Call of macro returning-effected-rows in send-query is hanging indefinitely

[JosefMachytkaNetApp]

Hello, I have encountered another problem with cl-postgres package in pgloader. Code is trying to migrate table which contains bytea column. It issues SELECT from the table which casts all columns as text and in this moment it suddenly hangs indefinitely.

I traced it and cl-postgres:protocol:send-query starts returning-effected-rows - https://github.com/marijnh/Postmodern/blob/805f033974dcd5313091bb99e73f7a98b5892c56/cl-postgres/protocol.lisp#L523 and here it hangs on running row-reader part - see details below. It looks like returning-effected-rows never starts, because it hangs on preparing data for it.

Values in the table are quite small. There is one row with value approx 350 KB big, but as far as I can tell, code already successfully migrated much bigger values. The only difference seems to be that bytea column, which is not used in other tables. But select casts all columns as text so it should not be a problem.

I tried different ways to debug what is happening here but so far without success. Would you be able to give me some hints what could be the problem here? Thank you very much.

---

UPDATE: I added some additional debug messages so I got this info about content of variables:

cl-postgres:protocol:send-query socket: #<FD-STREAM for "socket, peer: /var/run/postgresql/.s.PGSQL.5432" {10071CCF23}>
cl-postgres:protocol:send-query row-reader: #<FUNCTION (LAMBDA
                                                           (#:G39
                                                            PGLOADER.SOURCES:FIELDS)
                                                         :IN
                                                         PGLOADER.SOURCES:MAP-ROWS) {10071CB89B}>
cl-postgres:protocol:send-query row-description: #(#<FIELD-DESCRIPTION {1007233E13}>
                                                   #<FIELD-DESCRIPTION {1007233FC3}>
                                                   #<FIELD-DESCRIPTION {1007234173}>
                                                   #<FIELD-DESCRIPTION {1007234323}>
                                                   #<FIELD-DESCRIPTION {10072344D3}>)
cl-postgres:protocol:send-query returning-effected-rows
cl-postgres:protocol:send-query Using row-reader

[sabracrolleton]

Hello,

Just looking briefly at the code, I am probably thinking of things you have already tried:

1. Looking at the sequence of calls in the debugging messages, it looks like it is hanging on line 525 of the protocol.lisp file where it is trying to call a row reader and passing it a socket and a row description. The row description is that array shown in the third function call of your additional debug messages. So, what is in the field descriptions in that array?

2. I would inspect the field-description objects in the row description array (in the debug messages you show in the update above). If you cannot inspect the objects in the array, you could instrument the function

   (read-field-descriptions socket)

   which starts on line 356 of the protocol.lisp file.

3. I would try to read that table with and without conversion into text and see if that gives off any useful information.
4. I would look at the cast to text and compare that against the row descriptions to make sure it makes sense in context.

I have never had to convert bytea to text, but my understanding is that the normal postgresql conversion code (for example shown in https://www.heatware.net/postgresql/postgres-bytea-to-string-conversion/) looks like:

   SELECT encode(bytea_column, 'escape') FROM your_table;

At the same time, to quote Daniel Vérité, https://dba.stackexchange.com/questions/273044/how-to-convert-a-bytea-column-to-text,

"Bytes have 256 possible values, when there are only about 95 visualizable ASCII characters, (the range [32:126]), so the mapping between ASCII text and binary representation cannot be one-to-one.

The format produced by encode(metadata::bytea, 'escape') is just one possible representation, where some bytes are represented by sequences of characters. Others common representations are hexadecimal (outputs each byte as 2 characters) and base64 (outputs each 3 bytes as 4 characters)."

I do not know if any of this helps. I have not seen this issue before.