Weird behaviour with binary copy, arrays and column count - Mailing list pgsql-hackers

From James Vanns
Subject Weird behaviour with binary copy, arrays and column count
Date
Msg-id CAH7vdhNpUDMtXMEmLmoEgiNJTP68mgwHTRzPApbkU3GzgOqzdw@mail.gmail.com
Whole thread Raw
Responses Re: Weird behaviour with binary copy, arrays and column count
List pgsql-hackers
Hi all, apologies if this is the wrong list to use, but I figured this is a low-level enough problem that it might be the best to gain some understanding.

In PGDB 13.4 I have a simple (obscured) table;

CREATE SEQUENCE tbl_id_seq START 1;
CREATE TABLE tbl (
   a BIGINT UNIQUE NOT NULL DEFAULT nextval('tbl_id_seq'),
   b BIGINT NOT NULL,
   c TIMESTAMP WITHOUT TIME ZONE NOT NULL,
   d INT NOT NULL,
   e INT NULL DEFAULT 0,
   f INT NULL DEFAULT 1,
   g INT NULL DEFAULT 0,
   h BIGINT ARRAY,
   PRIMARY KEY (a)
);

Prior to introducing the ARRAY as field h, everything was working fine using a binary mode COPY via libpq;
COPY tbl (b,c,d,e,f,g,h) FROM STDIN WITH (FORMAT binary, FREEZE ON)
PQputCopyData()
PQputCopyEnd()
etc.

Now this is where the problem becomes peculiar. I read all the Interwebs has to offer on the efforts required to encode an array
in binary mode and I've achieved that just fine... but it only works *if* I remove column g from the COPY statement and data (it can remain in table definition and be filled in with a default). It's most odd. I've selectively gone through the table adding/removing fields until I get to this. It doesn't appear to be the array copy itself - it succeeds with 6 columns (b .. f plus h) but fails with the full complement of 7 (noting that 'a' is a generative sequence). The error in the PG logs is this;

ERROR:  syntax error at end of input at character 255

It does seem to smell of an alignment, padding, buffer overrun, parsing kind of error. I tried reintroducing column g as a larger integer or smaller field and the problem persists (and curiously the input character error remains at 255).

Also, if I remove the array from the COPY or replace it with a simple (series of) int, then the problem also goes away. The size of the array appears to have no relevance - whether its just a single item or 10, for example, the same problem remains and the same parse error at character 255. Finally, the definition order of the columns/fields also makes no difference - I can sandwich the array in the middle of the table and the COPY listing and the upload still succeeds so long as I keep the column count down at 6, essentially omitting 'g' again in this case.

I've read the array_send/recv functions in arrayfuncs.c and pretty sure I got that right (otherwise the array copy wouldn't work at all, right!?) ... its this odd combination of array+field lengths I can't figure!? I couldn't find the protocol receive code where array_recv is called - that might provide a clue.

Anyway, I appreciate I've sent this off without code or an MRE - I'll work on getting something isolated. Until then I wanted to get the ball rolling, in case anyone has any clues or can suggest what I'm either doing wrong or where the problem might be in PG!? In the meantime, to confirm the PG array format in binary its (inc overall field size for wire transfer);

htobe32(total_array_bytes_inc_header);
/* begin header */
htobe32(1); // single dimension
htobe32(0); // flags
htobe32(20); // array of bigint (it's OID)
htobe32(2); // 2 items, as an example
htobe32(1); // offset to first dimension
/* end header */
for (int i = 0 ; i < 2 ; ++i) {
    htobe32(sizeof(int8));
    htobe64(some_int8_val + i);
}

Cheers,

Jim

--
Jim Vanns
Principal Production Engineer
Industrial Light & Magic, London

pgsql-hackers by date:

Previous
From: David Steele
Date:
Subject: Re: remove more archiving overhead
Next
From: Robert Haas
Date:
Subject: Re: pg15b2: large objects lost on upgrade