public inbox for [email protected]help / color / mirror / Atom feed
Re: RowDescription for a function does not include table OID 5+ messages / 3 participants [nested] [flat]
* Re: RowDescription for a function does not include table OID @ 2024-06-21 15:03 Maxwell Dreytser <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Maxwell Dreytser @ 2024-06-21 15:03 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]> On Friday, June 21, 2024 10:48 AM David G. Johnston <[email protected]>wrote: >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. SETOF also does not return correct RowDescription data. Table OID and column number are still both 0. Both versions have the exact same pg_proc.prorettype. If I join this onto pg_type, the pg_type.typrelid = 'physical_table'::regclass. Regards, Maxwell ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RowDescription for a function does not include table OID @ 2024-06-21 15:28 David G. Johnston <[email protected]> parent: Maxwell Dreytser <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: David G. Johnston @ 2024-06-21 15:28 UTC (permalink / raw) To: Maxwell Dreytser <[email protected]>; +Cc: [email protected] <[email protected]> On Fri, Jun 21, 2024 at 8:04 AM Maxwell Dreytser < [email protected]> wrote: > On Friday, June 21, 2024 10:48 AM David G. Johnston < > [email protected]>wrote: > > >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. > > SETOF also does not return correct RowDescription data. Table OID and > column number are still both 0. > Both versions have the exact same pg_proc.prorettype. If I join this onto > pg_type, the pg_type.typrelid = 'physical_table'::regclass. > > Interesting, then I suppose it is semantics. There is no table involved - you are referencing the type of that name, not the table - so no TableOID. There is no guarantee the row you are holding came from a table - and I'd interpret the current behavior as conveying that fact. Though the current wording: "If the field can be identified as a column of a specific table, the object ID of the table; otherwise zero."; and the observation that at least a human "can identify" a related column, leads one to reasonably infer the system should be able to make such an identification as well. I would expect you'd be able to find the pg_type.oid value somewhere in the RowDescription given those specifications, but not the pg_type.typrelid value. But since the header has no allowance for a row type oid this information does seem to be missing. In short, the system doesn't generate the information you need, where you need it, to tie these pieces together. Modifying existing elements of the backend protocol is not presently in the cards. David J. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RowDescription for a function does not include table OID @ 2024-06-21 15:41 Maxwell Dreytser <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Maxwell Dreytser @ 2024-06-21 15:41 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: [email protected] <[email protected]> On Friday, June 21, 2024 11:28 AM David G. Johnston <[email protected]> wrote: > Interesting, then I suppose it is semantics. There is no table involved - you are referencing the type of that name, not the table - so no TableOID. There is no guarantee the row you are holding came from a table - and I'd interpret the current behavior as conveying that fact. Though the current wording: "If the field can be identified as a column of a specific table, the object ID of the table; otherwise zero."; and the observation that at least a human "can identify" a related column, leads one to reasonably infer the system should be able to make such an identification as well. This is exactly my point. If the return type of the function is strongly linked (directly in the function schema) to the table according to pg_catalog, the field can obviously be tied to a specific column of that specific table. The RowDescription not having that value filled in is a violation of that promise. > In short, the system doesn't generate the information you need, where you need it, to tie these pieces together. Modifying existing elements of the backend protocol is not presently in the cards. From my perspective this is clearly a bug as there is no way to define a function in a way that provides enough data to the reader. Regards, Maxwell. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RowDescription for a function does not include table OID @ 2024-06-21 15:52 David G. Johnston <[email protected]> parent: Maxwell Dreytser <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: David G. Johnston @ 2024-06-21 15:52 UTC (permalink / raw) To: Maxwell Dreytser <[email protected]>; +Cc: [email protected] <[email protected]> On Fri, Jun 21, 2024 at 8:41 AM Maxwell Dreytser < [email protected]> wrote: > On Friday, June 21, 2024 11:28 AM David G. Johnston < > [email protected]> wrote: > > > In short, the system doesn't generate the information you need, where > you need it, to tie these pieces together. Modifying existing elements of > the backend protocol is not presently in the cards. > > From my perspective this is clearly a bug as there is no way to define a > function in a way that provides enough data to the reader. > Quick search turned up this prior thread: https://www.postgresql.org/message-id/19323.1245720832%40sss.pgh.pa.us Based upon that unargued point the only bug here is in the documentation, leaving the reader to assume that some effort will be made to chain together a function returns clause to a physical table through that table's automatically-generated composite type. We don't and never will modify the existing protocol message semantics in that respect. David J. ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: RowDescription for a function does not include table OID @ 2024-06-21 16:12 Tom Lane <[email protected]> parent: David G. Johnston <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Tom Lane @ 2024-06-21 16:12 UTC (permalink / raw) To: David G. Johnston <[email protected]>; +Cc: Maxwell Dreytser <[email protected]>; [email protected] <[email protected]> "David G. Johnston" <[email protected]> writes: > Based upon that unargued point the only bug here is in the documentation, > leaving the reader to assume that some effort will be made to chain > together a function returns clause to a physical table through that table's > automatically-generated composite type. Hmm, I read the documentation as making minimal promises about how much effort will be expended, not maximal ones. But in any case, I repeat the point that you can't open this can of worms without having a lot of definitional slipperiness wriggle out. Here is an example: regression=# create table foo(a int, b int); CREATE TABLE regression=# create table bar(x int, y int, z int); CREATE TABLE regression=# create function f(int) returns setof foo stable begin atomic select y, z from bar where x = $1; end; CREATE FUNCTION What labeling would you expect for "select * from f(...)", and on what grounds? It is by no stretch of the imagination a select from table foo. Moreover, the system has fully enough information to perceive the query as a select from bar after inlining the function call: regression=# explain verbose select * from f(42); QUERY PLAN ------------------------------------------------------------ Seq Scan on public.bar (cost=0.00..35.50 rows=10 width=8) Output: bar.y, bar.z Filter: (bar.x = 42) (3 rows) In fact, if we implemented this labeling at the tail end of planning rather than early in parsing, it'd be fairly hard to avoid labeling the output columns as bar.* rather than foo.*. But we don't, and I'm not seeing an upside to redefining how that works. I've long forgotten the alleged JDBC connection that David mentions, but it's surely just the tip of the iceberg of client-side code that we could break if we change how this works. regards, tom lane ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-06-21 16:12 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-06-21 15:03 Re: RowDescription for a function does not include table OID Maxwell Dreytser <[email protected]> 2024-06-21 15:28 ` David G. Johnston <[email protected]> 2024-06-21 15:41 ` Maxwell Dreytser <[email protected]> 2024-06-21 15:52 ` David G. Johnston <[email protected]> 2024-06-21 16:12 ` 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