plpgsql, rowtype and dropped columns - Mailing list pgsql-general

As usual, the process of constructing a complete explanation
for an email has provided the final bit of inspiration for me
to produce a temporary work around but I'm still posting this
because I can't believe it's only me this has hit...


This is driving me nuts now. Either I'm being very very stupid
or there is something odd going on.

My apologies for the length of this. If you want the overview
of the problem I hit it's pretty close to the end and in the
form of a question.

I'll skip the standard \d table definition and go straight for
the definition from pg_attribute:

select at.attnum
         , at.attname
         , t.typname
         , case when at.attisdropped is true
             then null::text
             else '''' || at.attnum || '''::' || t.typname
            end
    from pg_attribute at
          , pg_class c
          , pg_type t
    where
          c.relname = 'article_content'
        and
          pg_table_is_visible(c.oid) is true
        and
          at.attrelid = c.oid
        and
          at.atttypid = t.oid
    order by at.attnum;

 attnum |            attname            |   typname   |       case
--------+-------------------------------+-------------+-------------------
     -7 | tableoid                      | oid         | '-7'::oid
     -6 | cmax                          | cid         | '-6'::cid
     -5 | xmax                          | xid         | '-5'::xid
     -4 | cmin                          | cid         | '-4'::cid
     -3 | xmin                          | xid         | '-3'::xid
     -2 | oid                           | oid         | '-2'::oid
     -1 | ctid                          | tid         | '-1'::tid
      1 | id                            | int4        | '1'::int4
      2 | name                          | text        | '2'::text
      3 | article_id                    | int4        | '3'::int4
      4 | status_id                     | int4        | '4'::int4
      5 | copyright                     | text        | '5'::text
      6 | summary                       | text        | '6'::text
      7 | comment                       | text        | '7'::text
      8 | lang_id                       | int4        | '8'::int4
      9 | active_date                   | timestamptz | '9'::timestamptz
     10 | expire_date                   | timestamptz | '10'::timestamptz
     11 | revision_date                 | timestamptz | '11'::timestamptz
     12 | revision_days                 | interval    | '12'::interval
     13 | content_type_id               | int4        | '13'::int4
     14 | contribution_receipt_date     | timestamptz | '14'::timestamptz
     15 | content                       | text        | '15'::text
     16 | version                       | int4        | '16'::int4
     17 | article_type_id               | int4        | '17'::int4
     18 | override_template_id          | int4        | '18'::int4
     19 | modified_user_id              | int4        | '19'::int4
     20 | modified_dt                   | timestamptz | '20'::timestamptz
     21 | ........pg.dropped.21........ | int4        |
     22 | ........pg.dropped.22........ | timestamptz |
     23 | active                        | bool        | '23'::bool
     24 | menu_appear                   | bool        | '24'::bool
     25 | keywords                      | text        | '25'::text
(32 rows)

(I'd change the column names but I really can't be bothered what
with the time I've wasted trying to get this working)

The last column was just so I could compare against the place I'm
getting the problem more easily.


I have also defined a test function, since the real one wasn't
working and a test one made sense to keep things simple and try
and determine the problem. That is defined as:

CREATE OR REPLACE FUNCTION do_my_test_thing ( integer, integer )
    RETURNS article_content
    AS '
    DECLARE
        myrec    article_content%ROWTYPE;
    BEGIN
        SELECT INTO myrec
                *
            FROM
                article_content ac
            WHERE
                    ac.article_id = $2
                AND
                    ac.status_id = 5;
        RETURN myrec;
    END;
    '
    LANGUAGE 'plpgsql';


Running it (and yes there is a single tuple matching the conditions) gives:

ttaweb=> select do_my_test_thing(2, 47);
WARNING:  Error occurred while executing PL/pgSQL function get_live_article_content
WARNING:  line 4 at select into variables
ERROR:  pg_atoi: error in "t": can't parse "t"
ttaweb=>


Listing all the fields explicitly:

CREATE OR REPLACE FUNCTION do_my_test_thing ( integer, integer )
    RETURNS article_content
    AS '
    DECLARE
        myrec    article_content%ROWTYPE;
    BEGIN
        SELECT INTO myrec
                1::int4
                ,''2''::text
                ,3::int4
                ,4::int4
                ,''5''::text
                ,''6''::text
                ,''7''::text
                ,8::int4
                ,current_timestamp + ''9 minutes''::interval
                ,current_timestamp + ''10 minutes''::interval
                ,current_timestamp + ''11 minutes''::interval
                ,''12 days''::interval
                ,13::int4
                ,current_timestamp + ''14 minutes''::interval
                ,''15''::text
                ,16::int4
                ,17::int4
                ,18::int4
                ,19::int4
                ,current_timestamp + ''20 minutes''::interval
                ,true::bool
                ,false::bool
                ,''23''::text
            FROM
                article_content ac
            WHERE
                    ac.article_id = $2
                AND
                    ac.status_id = 5;
        RETURN myrec;
    END;
    '
    LANGUAGE 'plpgsql';


Running it (and yes there is a single tuple matching the conditions):

ttaweb=> select get_live_article_content(2, 47);
WARNING:  Error occurred while executing PL/pgSQL function get_live_article_content
WARNING:  line 4 at select into variables
ERROR:  pg_atoi: error in "t": can't parse "t"
ttaweb=>


Noticing that "t" could be the representation of a boolean value and
that there are two dropped columns immediately before two boolean
ones we turn desparate and change the function to:

CREATE OR REPLACE FUNCTION do_my_test_thing ( integer, integer )
    RETURNS article_content
    AS '
    DECLARE
        myrec    article_content%ROWTYPE;
    BEGIN
        SELECT INTO myrec
                1::int4
                ,''2''::text
                ,3::int4
                ,4::int4
                ,''5''::text
                ,''6''::text
                ,''7''::text
                ,8::int4
                ,current_timestamp + ''9 minutes''::interval
                ,current_timestamp + ''10 minutes''::interval
                ,current_timestamp + ''11 minutes''::interval
                ,''12 days''::interval
                ,13::int4
                ,current_timestamp + ''14 minutes''::interval
                ,''15''::text
                ,16::int4
                ,17::int4
                ,18::int4
                ,19::int4
                ,current_timestamp + ''20 minutes''::interval
                ,11111
                ,22222
                ,true::bool
                ,false::bool
                ,''23''::text
            FROM
                article_content ac
            WHERE
                    ac.article_id = $2
                AND
                    ac.status_id = 5;
        RETURN myrec;
    END;
    '
    LANGUAGE 'plpgsql';


and running gives:

ttaweb=> select do_my_test_thing(2, 47);
WARNING:  Error occurred while executing PL/pgSQL function get_live_article_content
WARNING:  line 4 at select into variables
ERROR:  Bad timestamp external representation '2222'
ttaweb=>


Now I'm completely lost and don't know what else to try short of
attaching gdb to the server and trying to trace this, which I really
don't want to get into. Nothing in it makes sense. Is the first dropped
column trying to take the true boolean value? It would seem so given
that the final version has problems converting 22222 into a timestamp,
which of course is the type of the second dropped column.

Doing the quick additional tests of changing that 22222 to a proper
timestamp representation:

ttaweb=> select do_my_test_thing(2, 47);
ERROR:  Cannot display a value of type RECORD
ttaweb=>

Which I don't care about since that's not how I'm using the function
and just to show that it does work:


ttaweb=> select * from do_my_test_thing(2, 47);
 id | name | article_id | status_id | copyright | summary | comment | lang_id |      active_date       |
expire_date      |     revision_date      | revision_days | content_type_id | contribution_receipt_date | content |
version| article_type_id | override_template_id | modified_user_id |      modified_dt       | active | menu_appear |
keywords 

----+------+------------+-----------+-----------+---------+---------+---------+------------------------+------------------------+------------------------+---------------+-----------------+---------------------------+---------+---------+-----------------+----------------------+------------------+------------------------+--------+-------------+----------
  1 | 2    |          3 |         4 | 5         | 6       | 7       |       8 | 2003-06-11 17:16:16+01 | 2003-06-11
17:17:16+01| 2003-06-11 17:18:16+01 | 12 days       |              13 | 2003-06-11 17:21:16+01    | 15      |      16 |
            17 |                   18 |               19 | 2003-06-11 17:27:16+01 | t      | f           | 23 
(1 row)

ttaweb=>


Can it be that I'm the only one to have hit this problem that dropped
columns are still included in rowtypes?

The server has been stopped and restarted and the function reloaded
afterwards. The only thing I didn't do is drop and recreate the
language, which I can't see making any difference. The server
restart was a long shot. Using RECORD instead of ROWTYPE would
seem to be the way to make it work but apart from that requiring
the column definition list in the using query I haven't managed
to get past the

psql:/tmp/aa:25: ERROR:  Query-specified return tuple and actual function return tuple do not match

error and I really can't be bothered to spend more time fu^H^Hmessing
around trying to get that to work so it's back to listing all the
columns, including dropped ones, in the real function :(


--
Nigel Andrews