PostgreSQL function with duplicate parameters

Question

I stumbled upon a curious function signature in pg_catalog.pg_stat_get_activity:

CREATE OR REPLACE FUNCTION pg_stat_get_activity(
    IN pid integer, 
    OUT datid oid,
    OUT pid integer, 
    -- more parameters...)
  RETURNS SETOF record AS 'pg_stat_get_activity'
  LANGUAGE internal STABLE
  COST 1
  ROWS 100;

This function declares the same parameter name twice, which is also reported from the information_schema.

select 
  parameter_mode,
  parameter_name
from information_schema.parameters
where specific_schema = 'pg_catalog'
and specific_name like 'pg_stat_get_activity%'
order by ordinal_position

The above yields (see also this SQLFiddle):

+--------------+----------------+
|parameter_mode|parameter_name  |
+--------------+----------------+
|IN            |pid             |
|OUT           |datid           |
|OUT           |pid             |
|...           |...             |
+--------------+----------------+

Naively, I tried creating a similar function, without avail:

CREATE FUNCTION f_2647(p1 IN int, p1 OUT int)
AS $$
BEGIN
    p1 := p1;
END;
$$ LANGUAGE plpgsql;

My questions:

Why does the internal pg_stat_get_activity function redeclare the same parameter name twice? What's the purpose of this? E.g. why not just use an INOUT parameter?
What is different between the internal pg_stat_get_activity function and mine? Why can't I use this syntax?

I know these are rather academic questions, but I need to correctly understand this to fix an issue in the jOOQ codegenerator.

You will probably get better answers if you post this on the Postgres mailing list. — user330315
– user330315, Commented Sep 20, 2013 at 16:36
@a_horse_with_no_name: Usually, Stack Overflow is not so bad with these kinds of questions. But you're right, I might cross-post this eventually, and keep this question up to date if applicable. — Lukas Eder
– Lukas Eder, Commented Sep 20, 2013 at 16:49
I thought that maybe INOUT parameters might be split up like that in the system catalog, but that's not it. I have no explanation .. — Erwin Brandstetter
– Erwin Brandstetter, Commented Sep 20, 2013 at 17:38
@ErwinBrandstetter: That was my thought at first. But the method signature in text form, as can be seen in pgAdmin III for instance, really lists the same parameter name twice... — Lukas Eder
– Lukas Eder, Commented Sep 20, 2013 at 18:52

Daniel Vérité · Accepted Answer · 2013-09-20 20:30:16Z

I notice that it appeared in 9.2. In version 9.1, the out field was named procpid:

 parameter_mode |  parameter_name  
----------------+------------------
 IN             | pid
 OUT            | datid
 OUT            | procpid
 OUT            | usesysid
 ...

Looking for the change in postgres git history leads to this commit:

commit 4f42b546fd87a80be30c53a0f2c897acb826ad52
Author: Magnus Hagander 
Date:   Thu Jan 19 14:19:20 2012 +0100

    Separate state from query string in pg_stat_activity

    This separates the state (running/idle/idleintransaction etc) into
    it's own field ("state"), and leaves the query field containing just
    query text.

    The query text will now mean "current query" when a query is running
    and "last query" in other states. Accordingly,the field has been
    renamed from current_query to query.

    Since backwards compatibility was broken anyway to make that, the procpid
    field has also been renamed to pid - along with the same field in
    pg_stat_replication for consistency.

    Scott Mead and Magnus Hagander, review work from Greg Smith

Among the lines that changed, here is the one of interest:

-DATA(insert OID = 2022 (  pg_stat_get_activity         PGNSP PGUID 12 1 100 0 0 f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,16,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,procpid,usesysid,application_name,current_query,waiting,xact_start,query_start,backend_start,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));
+DATA(insert OID = 2022 (  pg_stat_get_activity         PGNSP PGUID 12 1 100 0 0 f f f f t s 1 0 2249 "23" "{23,26,23,26,25,25,25,16,1184,1184,1184,1184,869,25,23}" "{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o}" "{pid,datid,pid,usesysid,application_name,state,query,waiting,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port}" _null_ pg_stat_get_activity _null_ _null_ _null_ ));

In this pre-digested form, it's plausible that the authors didn't notice the double-use of pid, or else they didn't care since it's harmless in practice.

It is let through because these internal functions are created by initdb in a fast path that skips the create checks of normal user functions.

Interesting, it hadn't occurred to me to check older versions of PostgreSQL. Thanks for looking that up.

Collectives™ on Stack Overflow

PostgreSQL function with duplicate parameters

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related