pg_catalog.pg_proc procedure has correct proargnames array, but proargtypes is empty
The following function's metadata is not correctly reported in the pg_proc
table (see picture below).
CREATE OR REPLACE FUNCTION scm.get_period_type_adjustment(period_type_id
integer)
RETURNS numeric
LANGUAGE plpgsql
IMMUTABLE
AS $function$
------
begin
return (case
when period_type_id in (1) then 1.0
when period_type_id in (2, 17, 4, 3) then 0.25
when period_type_id = 10 then 0.5
else 1
end)::numeric;
end;
$function$
;
[image: image.png]
--
Walker Philips
Data Engineering Consultant
Saguaro Capital Management
Attachments:
On Sat, 16 Jul 2022 at 12:14, Walker Philips <wphilips53@gmail.com> wrote:
The following function's metadata is not correctly reported in the pg_proc table (see picture below).
Seems quite unlikely this such a fundamental thing could be broken.
Can you share which version of PostgreSQL you see this on?
I see the proargtypes populated properly on current master.
David
Walker Philips <wphilips53@gmail.com> writes:
The following function's metadata is not correctly reported in the pg_proc
table (see picture below).
Looks fine to me:
regression=# CREATE OR REPLACE FUNCTION get_period_type_adjustment(period_type_id
integer)
RETURNS numeric
...
regression=# \x
Expanded display is on.
regression=# select * from pg_proc where proname = 'get_period_type_adjustment';
...
pronargs | 1
pronargdefaults | 0
prorettype | 1700
proargtypes | 23
proallargtypes |
proargmodes |
proargnames | {period_type_id}
proargdefaults |
...
One IN integer argument named period_type_id, result type numeric.
Note the comments in
https://www.postgresql.org/docs/devel/catalog-pg-proc.html
that proallargtypes etc. are generally set to NULL if they wouldn't
carry any additional data. This is a choice we made a long time
ago to prioritize storage space over simplicity of implementation.
I don't know whether we'd make the same choice in a green field
today ... but we're not going to change it now, because the ensuing
confusion would be pretty bad.
regards, tom lane
David Rowley <dgrowleyml@gmail.com> writes:
Can you share which version of PostgreSQL you see this on?
Hmm ... looking more closely, it's definitely not stock PG,
because proargtypes is oidvector which would not include any
curly braces in the output.
Hard to tell what's going on exactly; seems like it could be
corrupt catalog data or faulty code modifications. Or maybe
there's something wrong with whatever the viewing tool is?
regards, tom lane
PostgreSQL 14.4 (Ubuntu 14.4-1.pgdg22.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 11.2.0-19ubuntu1) 11.2.0, 64-bit
I'm using DBeaver as my IDE. The column does appear to be oidvector. Other
functions seem to come through ok weirdly enough. It potentially might be a
visual glitch with DBeaver based on the following screenshot. Still pretty
odd. I can submit to DBeaver if that might be the underlying issue.
[image: image.png]
[image: image.png]
On Fri, Jul 15, 2022 at 7:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
David Rowley <dgrowleyml@gmail.com> writes:
Can you share which version of PostgreSQL you see this on?
Hmm ... looking more closely, it's definitely not stock PG,
because proargtypes is oidvector which would not include any
curly braces in the output.Hard to tell what's going on exactly; seems like it could be
corrupt catalog data or faulty code modifications. Or maybe
there's something wrong with whatever the viewing tool is?regards, tom lane
--
Walker Philips
Data Engineering Consultant
Saguaro Capital Management
Walker Philips <wphilips53@gmail.com> writes:
I'm using DBeaver as my IDE. The column does appear to be oidvector. Other
functions seem to come through ok weirdly enough. It potentially might be a
visual glitch with DBeaver based on the following screenshot. Still pretty
odd. I can submit to DBeaver if that might be the underlying issue.
I'd try looking at the row in psql. If it looks normal there,
then it's a DBeaver problem.
regards, tom lane