Thread: [HACKERS] [Q] When should I use reg* types instead of oid in the system catalog?
[HACKERS] [Q] When should I use reg* types instead of oid in the system catalog?
From
"MauMau"
Date:
Hello, Both pg_aggregate.aggfnoid and pg_trigger.tgfoid references pg_proc.oid, but the data types of them are regproc and oid respectively. Is there any criterion on when to which in the system catalog? Is the regproc choice just for readability of the catalog query output? Should pg_trigger.tgfoid also have been changed to regproc? Regards MauMau
Re: [HACKERS] [Q] When should I use reg* types instead of oid in the system catalog?
From
Tom Lane
Date:
"MauMau" <maumau307@gmail.com> writes:
> Both pg_aggregate.aggfnoid and pg_trigger.tgfoid references
> pg_proc.oid, but the data types of them are regproc and oid
> respectively. Is there any criterion on when to which in the system
> catalog? Is the regproc choice just for readability of the catalog
> query output? Should pg_trigger.tgfoid also have been changed to
> regproc?
It's probably mostly historical accident :-(. There have been suggestions
before to convert more system catalog columns to regfoo types, but there's
serious stumbling blocks in the way:
* Almost certainly, such conversions would break client code that's
expecting to see simple numeric OIDs in those columns. pg_dump would
certainly be broken for example. It'd be within our power to fix pg_dump,
but there would be more pushback about it from places like pgAdmin and
the JDBC driver.
* It's not actually that helpful, at least not without some fresh ideas
about type resolution. For example, you'd think that if we changed
pg_attribute.attrelid to regclass then you could do something likeselect attname from pg_attribute where attrelid =
'mytable'
and it'd work. But it won't, as can be shown by trying it on one
of the existing regproc columns:
=# select * from pg_aggregate where aggfinalfn = 'interval_avg';ERROR: invalid input syntax for type oid:
"interval_avg"LINE1: select * from pg_aggregate where aggfinalfn = 'interval_avg'...
^
The reason for that is that the "=" operator is resolved as oideq,
there not being a separate set of operators for each OID-alias type.
Now that's pretty confusing considering that the printed values for
relevant entries in that column look exactly like 'interval_avg',
but there it is.
So, pending some ideas about resolving those issues, there hasn't
been much eagerness to change catalog columns that are currently
plain "oid".
regards, tom lane
Re: [HACKERS] [Q] When should I use reg* types instead of oid in the system catalog?
From
"MauMau"
Date:
From: Tom Lane It's probably mostly historical accident :-(. There have been suggestions before to convert more system catalog columns to regfoo types, but there's serious stumbling blocks in the way: Thank you so much for concise detailed explanation of the history and current situation. I understood that it would do more harm than good to change existing plain oid columns to reg* types for various clients, and it wouldn't very beneficial but also not so harmful to make new catalogs/columns reg* types. Regards MauMau