public inbox for [email protected]
help / color / mirror / Atom feedRe: RowDescription for a function does not include table OID
6+ messages / 3 participants
[nested] [flat]
* Re: RowDescription for a function does not include table OID
@ 2024-06-20 01:22 Maxwell Dreytser <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: Maxwell Dreytser @ 2024-06-20 01:22 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: [email protected] <[email protected]>
From: Tom Lane <[email protected]>:
>Hmm, I do not think that syntax means what you think it means ;-).
Its an interesting trick that I came across on DBA SE on a question named "How to use RETURNS TABLE with an existing table in PostgreSQL?".
>However, it seems to end up with prorettype =
>physical_table'::regtype anyway thanks to some special rules about
>single-column output tables, so as far as I can see you should get
>the table's composite type OID as the column type OID in the result
>descriptor for "SELECT my_function(...)". Or is that not the case
>you're concerned about?
The query I am running is "SELECT * FROM my_function()". According to Wireshark I can see that the returned RowDescription shows 0 for Table OID and Column index:
PostgreSQL
Type: Row description
Length: 219
Field count: 7
Column name: table_id
Table OID: 0
Column index: 0
Type OID: 20
Column length: 8
Type modifier: -1
Format: Binary (1)
<snipped>
>I'm confused about exactly what you're asking for, but (a) returning a
>type OID where a relation OID is expected is absolutely not OK ---
>there is no guarantee that those OID sets are distinct; (b) regardless
>of that, you seem to be asking for a silent semantic change in the
>wire protocol, which is going to be a very hard sell because it will
>probably break more applications than it makes happy. Why can't you
>get what you need from the composite type OID?
I would like the relation OID to be returned for the composite type that is returned from the function.
Maybe this can be simply considered a bug as it does seem like returning the relation OID that is clearly available would be the expected behavior.
Regards,
Maxwell.
^ permalink raw reply [nested|flat] 6+ messages in thread
* RowDescription for a function does not include table OID
@ 2024-06-21 14:41 Maxwell Dreytser <[email protected]>
parent: Maxwell Dreytser <[email protected]>
0 siblings, 2 replies; 6+ messages in thread
From: Maxwell Dreytser @ 2024-06-21 14:41 UTC (permalink / raw)
To: [email protected] <[email protected]>
Hello,
I am working on a meta-programming use-case where I need to scrape some detailed information about the results of a function that "RETURNS TABLE (LIKE physical_table)", which ends up with prorettype = 'physical_table'::regtype.
The problem is that for the query "SELECT * FROM my_function()" the RowDescription that is sent back shows 0 for Table OID and Column Index.
From Wireshark:
PostgreSQL
Type: Row description
Length: 219
Field count: 7
Column name: table_id
Table OID: 0
Column index: 0
Type OID: 20
Column length: 8
Type modifier: -1
Format: Binary (1)
<snipped>
I would expect that the Table OID contains the relation OID of this table, as it would do for a typical statement like "SELECT * FROM my_table". It would seem there is a bug here that is preventing PostgreSQL from connecting the dots.
Regards,
Maxwell.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RowDescription for a function does not include table OID
@ 2024-06-21 14:48 David G. Johnston <[email protected]>
parent: Maxwell Dreytser <[email protected]>
1 sibling, 0 replies; 6+ messages in thread
From: David G. Johnston @ 2024-06-21 14:48 UTC (permalink / raw)
To: Maxwell Dreytser <[email protected]>; +Cc: [email protected] <[email protected]>
On Fri, Jun 21, 2024 at 7:42 AM Maxwell Dreytser <
[email protected]> wrote:
> I am working on a meta-programming use-case where I need to scrape some
> detailed information about the results of a function that "RETURNS TABLE
> (LIKE physical_table)"
>
Yes, but the bug is yours. The definition you want is: RETURNS SETOF
physical_table (not tested though)
What you did was produce a one-column table whose column type is a
composite (and whose name is like - what with case-folding of unquoted
identifiers). Since that table doesn't exist anywhere in the catalogs it
has no TableOID.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RowDescription for a function does not include table OID
@ 2024-06-21 15:51 Tom Lane <[email protected]>
parent: Maxwell Dreytser <[email protected]>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2024-06-21 15:51 UTC (permalink / raw)
To: Maxwell Dreytser <[email protected]>; +Cc: [email protected] <[email protected]>
Maxwell Dreytser <[email protected]> writes:
> I am working on a meta-programming use-case where I need to scrape some detailed information about the results of a function that "RETURNS TABLE (LIKE physical_table)", which ends up with prorettype = 'physical_table'::regtype.
> The problem is that for the query "SELECT * FROM my_function()" the RowDescription that is sent back shows 0 for Table OID and Column Index.
Yes, that's expected. You're selecting from a function, not a table.
> I would expect that the Table OID contains the relation OID of this
> table, as it would do for a typical statement like "SELECT * FROM
> my_table".
The PG wire protocol specification [1] defines these fields thus:
If the field can be identified as a column of a specific
table, the object ID of the table; otherwise zero.
If the field can be identified as a column of a specific
table, the attribute number of the column; otherwise zero.
My reading of that is that we should populate these fields only for
the case of direct selection from a table. If you go further than
that, then first off you have a ton of definitional issues (should it
"look through" views, for example?), and second you probably break
applications that are expecting the existing, longstanding definition.
regards, tom lane
[1] https://www.postgresql.org/docs/current/protocol-message-formats.html
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RowDescription for a function does not include table OID
@ 2024-06-21 15:57 David G. Johnston <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2024-06-21 15:57 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Maxwell Dreytser <[email protected]>; [email protected] <[email protected]>
On Fri, Jun 21, 2024 at 8:51 AM Tom Lane <[email protected]> wrote:
>
> The PG wire protocol specification [1] defines these fields thus:
>
> If the field can be identified as a column of a specific
> table, the object ID of the table; otherwise zero.
>
> If the field can be identified as a column of a specific
> table, the attribute number of the column; otherwise zero.
>
> My reading of that is that we should populate these fields only for
> the case of direct selection from a table.
>
s/can be identified as/is/g ?
Experience shows people are inferring a lot from "can be identified" so we
should remove it. "is" maybe over-simplifies a bit but in the correct
direction.
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: RowDescription for a function does not include table OID
@ 2024-06-21 16:17 Tom Lane <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2024-06-21 16:17 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Maxwell Dreytser <[email protected]>; [email protected] <[email protected]>
"David G. Johnston" <[email protected]> writes:
> On Fri, Jun 21, 2024 at 8:51 AM Tom Lane <[email protected]> wrote:
>> The PG wire protocol specification [1] defines these fields thus:
>> If the field can be identified as a column of a specific
>> table, the object ID of the table; otherwise zero.
> s/can be identified as/is/g ?
> Experience shows people are inferring a lot from "can be identified" so we
> should remove it. "is" maybe over-simplifies a bit but in the correct
> direction.
I dunno, that seems to me to be just as open to argument if not
more so. Perhaps some phrasing like "can be directly identified"?
The real point IMV is that it's based purely on parse analysis,
without looking into the behavior of views or functions (which
could change between parsing and execution, anyway).
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2024-06-21 16:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-06-20 01:22 Re: RowDescription for a function does not include table OID Maxwell Dreytser <[email protected]>
2024-06-21 14:41 ` Maxwell Dreytser <[email protected]>
2024-06-21 14:48 ` David G. Johnston <[email protected]>
2024-06-21 15:51 ` Tom Lane <[email protected]>
2024-06-21 15:57 ` David G. Johnston <[email protected]>
2024-06-21 16:17 ` Tom Lane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox