anse1 / emacs-libpq Goto Github PK

In contrast to handing ERRORs, Implementing NOTICEs/WARNINGs is not trivial due to their asynchronous nature.

Currently, a dead but formerly-live connection sometimes yields an error by the emacs-module wrapper "peculiar error", instead of a properly reported one.

This needs fixing.

Hello,

I am trying to load this file test.el however, on (require 'pq) I get the error as in subject of this message. I am using latest GNU Emacs from git.

Please help me run this database connection, as pg.el stopped working with PostgreSQL version 10.1

I would like to know how others are using this module, why not open discussion or forum?

You could apply on GNU to make mailing list for emacs-libpq

Here is video of high level interface how I am using it:
https://gnu.support/images/2022/09/2022-09-10/2022-09-10-11:02:50.ogv

If I use (pq:escapeLiteral "'" "d") -- I did not know how to use it, but Emacs segfaults on this.

I pushed to the scratch/pq-compile branch of elpa.git my proposed patch which should let Emacs auomatically compile pq-core.c for the user when the package is installed/compiled or when it's used. Feel free to make it a pull request here if you prefer, of course.

I would like to provide parameter NULL to:

UPDATE table SET column = $1

And currently providing "NULL" as string does not work. Is there any existing solution?

Since PostgreSQL version 10.1 the protocol version 1.0 has been removed, and such is used by pg.el and each package, just as this one.

See the change here:
https://www.postgresql.org/docs/current/static/release-10.html

As protocol 1.0 has been dropped, the pg.el on which this package depends does not work any more with the PostgreSQL latest version 10.1

If you can do anything to handle this problem, please do, as of now, all PostgreSQL programs with Emacs are broken on my side.

In case you wanted to improve emacs-libpq further what features would you add?

I am not that familiar with libpq and I would like to know what features you consider adding if you wanted to improve it.

For someone who wants to use emacs-libpq a lot, what features do you think they would miss the most?

Could you please submit your module for inclusion into GNU Emacs?

It works well, so why not submit it directly to inclusion into GNU Emacs?

https://lists.gnu.org/archive/html/help-gnu-emacs/

If I have column like variable_boolean and I want to know if it is FALSE, the module returns nil, which I understand, however, it does not say if column is with the value FALSE or it is empty. There is particular situation where it is important. I hope you understand me.

In some contexts, SQL errors are still not properly propagated and yield a generic "peculiar error" by the module interface instead. I've not found a recipe to reproduce these yet.

Is there a way to know the names and types of columns a query returns?

I'm not acquainted with using libpq at such a low level and I wonder if there is an API for it.

At first I have made libraries that connect and fetch and handle was &optional. If there was no handle standard *db* handle was used. Programs could access one database.

Then I have developed separate Emacs package that uses the same database but due to its nature could also use separate database and other databases outside of my local computer.

As then I had 2 programs connecting to local database and complexities arrive in future when using multiple databases in the same time, I am of opinion that each call to function that uses database should also transfer the handle, so that handle is not just global variable.

Do you agree on this?

Handle was here optional and it worked well until I started making two connections to database:

(defun rcd-sql (sql &optional handle)
  "Sends SQL queries to PostgreSQL database and returns results"
  (let ((handle (if handle handle *cf*)))
    (condition-case err
	(pq:query handle sql)
      (error
       (if (string-match "^ERROR:  syntax error" (cdr err))
	   (progn
	     (if (fboundp 'speak) (speak (cdr err)))
	     (message (cdr err)))
	 ;; re-throw
	 (signal (car err) (cdr err)))))))

(defun rcd-sql-list (sql &optional handle)
  "Returns list of lists instead of vectors"
  (let ((list '()))
    (dolist (i (apply 'rcd-sql (list sql handle)) (reverse list))
      (cond ((eq (type-of i) 'vector) (push (append i '()) list))
	    (t (push i list))))))

(defun rcd-sql-first (sql &optional handle)
  "Returns first entry from SQL query"
  (car (apply 'rcd-sql (list sql handle))))

My opinion is that I should not make it optional but require handle each time. Is that what you also recommend?

I have noticed that queries that take long time are not interruptible by Emacs. Maybe something can be done on that?

I had to stop server to interrupt it.

Maybe there is some function that can be implemented to send some signal to interrupt it?

I am expecting that

(pq:query db "SELECT $1" 100) ⇒ ("100")

returns integer instead of a string. I suspect bug here.

Only way it would work is:

(pq:query db "SELECT $1::integer" 100) ⇒ (100)

which makes everything cumbersome. It forces me to use function format which is then unsafe for users' input. I would rather like to switch to using pq:query parameters, but this problem is here.

I should be able to get the database erorr programmatically and not just enter debugger on something like

(pq:query *rcd-pg* "SEjnjn")

so how do I do it?

I cannot find description for these optional parameters:

pq:query is a module function.

(pq:query ARG1 ARG2 &optional ARG3 ARG4 ARG5 ARG6
ARG7 ARG8 ARG9 ARG10 ARG11 ARG12 ARG13 ARG14)

Execute QUERY on CONNECTION with optional PARAMETERS.

I was thinking there is some error handling option, I cannot find it. Please help. Imagine editing a table field and it should be unique, and database is raising error. I think I should be able to capture, understand error and do further programming based on that.

Table:

admin=# \d sal_emp
                   Table "public.sal_emp"
     Column     |   Type    | Collation | Nullable | Default 
----------------+-----------+-----------+----------+---------
 name           | text      |           |          | 
 pay_by_quarter | integer[] |           |          | 
 schedule       | text[]    |           |          | 

Result:

(rcd-sql "SELECT schedule from sal_emp;" *admin*)
("{{meeting,lunch},{training,presentation}}" "{{meeting,lunch},{training,presentation},{Assigned,Jean}}")

In my opinion that is poor for Emacs Lisp to receive just textual representation of PostgreSQL arrays. I was expecting to receive some better structure.

Is it feasible to support PostgreSQL arrays so that Emacs Lisp receives better structure out of it?

Does escaping really needs conn-db and why? Should not escaping be independent of connection?

Finally escaping function could be used to generate SQL without having a database connection.

I do expect that library automatically escapes whatever is necessary when it comes to providing parameters. Here below I get error as "+" is considered quantifier. Am I supposed to escape this before providing it as parameter?

(pq:query cf-db "SELECT commlines_people FROM commlines WHERE commlines_value ~ $1 AND (SELECT addresses_people FROM addresses WHERE addresses_countries = $2) != commlines_people" "+256" 224)

progn: SQL error: "ERROR:  invalid regular expression: quantifier operand invalid

(defun rcd-sql (sql)
  (pq:query *rcd-pg* sql))

(rcd-sql "SELECT a.attname, pg_catalog.format_type(a.atttypid, a.atttypmod), (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid) for 128) FROM pg_catalog.pg_attrdef d WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef), a.attnotnull, a.attnum, (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation, a.attidentity, NULL AS indexdef, NULL AS attfdwoptions, a.attstorage, CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget, pg_catalog.col_description(a.attrelid, a.attnum) FROM pg_catalog.pg_attribute a WHERE a.attrelid = '33748' AND a.attnum > 0 AND NOT a.attisdropped ORDER BY a.attnum")

(["tlds_id" "integer" "nextval('tlds_tlds_id_seq'::regclass)" "" 1 #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "p" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "ID"] ["tlds_tld" "text" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "" 2 #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "x" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "TLD"] ["tlds_nic" "integer" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> 3 #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "p" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "NIC Company"] ["tlds_description" "text" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> 4 #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "x" #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug> "Description"] . #<EMACS BUG: INVALID DATATYPE (0x01) Save your buffers immediately and please report this bug>)

     attname      | format_type |               substring               | attnotnull | attnum | attcollation | attidentity | indexdef | attfdwoptions | attstorage | attstattarget | col_description 
------------------+-------------+---------------------------------------+------------+--------+--------------+-------------+----------+---------------+------------+---------------+-----------------
 tlds_id          | integer     | nextval('tlds_tlds_id_seq'::regclass) | t          |      1 |              |             |          |               | p          |               | ID
 tlds_tld         | text        |                                       | t          |      2 |              |             |          |               | x          |               | TLD
 tlds_nic         | integer     |                                       | f          |      3 |              |             |          |               | p          |               | NIC Company
 tlds_description | text        |                                       | f          |      4 |              |             |          |               | x          |               | Description
(4 rows)


Basically, I have git upgraded to newest development Emacs version. Recompiled your module which worked so well over last months. And now I cannot run it, as I am crashing whole emacs by the above SQL query.

Do you know maybe, do you have some clue, how to avoid the crash?