Re: [Q] When should I use reg* types instead of oid in the system catalog?

"MauMau" <maumau307(at)gmail(dot)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 like
select 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"
LINE 1: 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