pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
PQftype(copy_rset) returns zero OIDs???
3+ messages / 2 participants
[nested] [flat]

* PQftype(copy_rset) returns zero OIDs???
@ 2024-02-27 12:12  Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Dominique Devienne @ 2024-02-27 12:12 UTC (permalink / raw)
  To: pgsql-general

Hi.

For the first time, I'm checking the OIDs returned
(via the result set's PQftype API), by "regular" SELECT:

`select ... from tab`

and by a COPY TO BINARY:

`COPY (
 select ...
   from tab
) TO STDOUT WITH (FORMAT BINARY)`

And to my surprise, they are not!
The ones from the COPY are all zeros.

Is that normal?

The BINARY record format encodes the byte-size, not the OIDs of the
(scalar) fields.
How is one supposed to "infer" the OIDs then?

Also, given that the select-clause can use expressions I think,
including casts, instead of just naming columns, "describing" the table
doesn't help.

But even for a "plain" COPY, having to describe explicitly is one more
round-trip,
and seems asymmetrical compared to the result-set of a regular select.

I'm both surprised and confused by this behavior.
And would appreciate some clarifications. Thanks, --DD

PS: libpq v16.1. v12 server.

^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PQftype(copy_rset) returns zero OIDs???
@ 2024-02-27 17:11  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Dominique Devienne <ddevienne@gmail.com>
  0 siblings, 1 reply; 3+ messages in thread

From: Tom Lane @ 2024-02-27 17:11 UTC (permalink / raw)
  To: Dominique Devienne <ddevienne@gmail.com>; +Cc: pgsql-general

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

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

			regards, tom lane





^ permalink  raw  reply  [nested|flat] 3+ messages in thread

* Re: PQftype(copy_rset) returns zero OIDs???
@ 2024-02-27 17:57  Dominique Devienne <ddevienne@gmail.com>
  parent: Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 3+ messages in thread

From: Dominique Devienne @ 2024-02-27 17:57 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: 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

^ permalink  raw  reply  [nested|flat] 3+ messages in thread


end of thread, other threads:[~2024-02-27 17:57 UTC | newest]

Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-02-27 12:12 PQftype(copy_rset) returns zero OIDs??? Dominique Devienne <ddevienne@gmail.com>
2024-02-27 17:11 ` Tom Lane <tgl@sss.pgh.pa.us>
2024-02-27 17:57   ` Dominique Devienne <ddevienne@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox