plpgsql, rowtype and dropped columns

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