current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 5 down vote favorite

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:

  1. 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?
  2. 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.

share|improve this question
    
You will probably get better answers if you post this on the Postgres mailing list. –  a_horse_with_no_name Sep 20 '13 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 Sep 20 '13 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 Sep 20 '13 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 Sep 20 '13 at 18:52

1 Answer 1

up vote 3 down vote

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.

share|improve this answer
    
Interesting, it hadn't occurred to me to check older versions of PostgreSQL. Thanks for looking that up. –  Lukas Eder Sep 21 '13 at 5:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.