Re: PQftype(copy_rset) returns zero OIDs??? - Mailing list pgsql-general

From Dominique Devienne
Subject Re: PQftype(copy_rset) returns zero OIDs???
Date
Msg-id CAFCRh--0V+iGCGU9OBvTHe849zRQERuHLOjcDd7hfuCSZT6rEQ@mail.gmail.com
Whole thread Raw
In response to Re: PQftype(copy_rset) returns zero OIDs???  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-general
On Tue, Feb 27, 2024 at 6:11 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Dominique Devienne <ddevienne@gmail.com> writes:
> For the first time, I'm checking the OIDs returned
> (via the result set's PQftype API), by "regular" SELECT:
> ...
> And to my surprise, they are not!
> The ones from the COPY are all zeros.

> Is that normal?

The CopyInResponse and CopyOutResponse messages don't carry any
column name or type indications, so yes it's normal.

https://www.postgresql.org/docs/current/protocol-message-formats.html

Thanks Tom.

Darn, if it's not there at the protocol level, I'll never get it :(

OTOH, why about the flags which are part of the COPY BINARY format?

On COPY FROM STDIN, I have this WITH OIDS commented out flag in my code.
And there's also that extended header. Can't OIDs be in there, instead of the result-set
that initiate the COPY FROM? (my Copies helper only does COPY FROM STDIN).

I'd love an option SQL option for COPY that sets a FLAG on COPY TO STDOUT,
which then puts the OIDs in the extender header part?
The field count is after the extended header, but since one knows the extended header size,
one can always skip ahead to know how many OIDs to expect for example.

So is there something like this?
If no, could there be something like this?
That would be easier than a protocol change, no?

void Copier::put_begin() {
...
    // header
    bytes.append(
        copy_binary_header_signature.data(),
        copy_binary_header_signature.size()
    );

    int32_t flags = 0;
    //flags |= (1 << 16); // means WITH OIDS
    bytes.append(as_big_endian_chars(flags));

    // extended header
    int32_t hdr_ext_len = 0;
    bytes.append(as_big_endian_chars(hdr_ext_len));
...
}

 
Maybe we missed a bet there, but AFAIR you are the first person to
ask for this in twenty-plus years 

Well, maybe COPY TO STDOUT WITH (FORMAT BINARY) is not used by many...
I tend to think outside the box and not follow the mainstream. I use COPY extensively.
I'll work-around this. But my OP points remains IMHO. I.e. at least the OIDs, and probably
the NAMEs too (you're right), should have been included in CopyOutResponse.

How is one supposed to decode a COPY TO BINARY, w/o the OIDs?

Thanks, --DD

pgsql-general by date:

Previous
From: Thiemo Kellner
Date:
Subject: Me again with an insert trigger problem
Next
From: Ron Johnson
Date:
Subject: Re: PostgreSQL Guard