From: | "David Gardner" <dgardner(at)creatureshop(dot)com> |
---|---|
To: | pgsql-bugs(at)postgresql(dot)org |
Subject: | BUG #5240: Stable Functions that return a table type with a dropped column fail |
Date: | 2009-12-11 19:19:58 |
Message-ID: | 200912111919.nBBJJwDY033995@wwwmaster.postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged online:
Bug reference: 5240
Logged by: David Gardner
Email address: dgardner(at)creatureshop(dot)com
PostgreSQL version: 8.4.1
Operating system: Debian Linux, amd64 2.6.30
Description: Stable Functions that return a table type with a dropped
column fail
Details:
SELECT foo(); works while SELECT * FROM foo(); fails.
However redefining the function as volatile fixes the issue. Possibly
related to BUG #4907.
-----------------------
dgardner(at)cssun32 ~:$ psql -h localhost -d hdpsdb_baseline
Password:
psql (8.4.1)
Type "help" for help.
hdpsdb_baseline=# SELECT * FROM version();
version
----------------------------------------------------------------------------
--------------------------
PostgreSQL 8.4.1 on x86_64-pc-linux-gnu, compiled by GCC gcc-4.3.real
(Debian 4.3.4-2) 4.3.4, 64-bit
(1 row)
hdpsdb_baseline=# SELECT a.attnum, a.attname AS field,
hdpsdb_baseline-# a.attlen AS length, a.atttypmod AS length_var,
hdpsdb_baseline-# a.attnotnull AS not_null, a.atthasdef as
has_default
hdpsdb_baseline-# FROM pg_class c, pg_attribute a
hdpsdb_baseline-# WHERE c.relname = 'users'
hdpsdb_baseline-# AND a.attnum > 0
hdpsdb_baseline-# AND a.attrelid = c.oid
hdpsdb_baseline-# ORDER BY a.attnum;
attnum | field | length | length_var | not_null |
has_default
--------+------------------------------+--------+------------+----------+---
----------
1 | userid | -1 | 259 | t | f
2 | name | -1 | 259 | t | f
3 | email | -1 | -1 | f | f
4 | ........pg.dropped.4........ | 1 | -1 | f | f
5 | enabled | 1 | -1 | t | t
(5 rows)
hdpsdb_baseline=# CREATE OR REPLACE FUNCTION get_users()
hdpsdb_baseline-# RETURNS SETOF users AS
hdpsdb_baseline-# $BODY$
hdpsdb_baseline$# SELECT users.*
hdpsdb_baseline$# FROM
hdpsdb_baseline$# users
hdpsdb_baseline$# ORDER BY users.userid LIMIT 1;
hdpsdb_baseline$# $BODY$
hdpsdb_baseline-# LANGUAGE 'sql' STABLE;
CREATE FUNCTION
hdpsdb_baseline=# ALTER FUNCTION get_users() OWNER TO dgardner;
ALTER FUNCTION
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO public;
GRANT
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO dgardner;
GRANT
hdpsdb_baseline=#
hdpsdb_baseline=# SELECT * FROM get_users();
ERROR: invalid attribute number 5
hdpsdb_baseline=# SELECT get_users();
get_users
---------------------------------------
(aa_test_user,test,test(at)here(dot)there,t)
(1 row)
hdpsdb_baseline=# CREATE OR REPLACE FUNCTION get_users()
hdpsdb_baseline-# RETURNS SETOF users AS
hdpsdb_baseline-# $BODY$
hdpsdb_baseline$# SELECT users.*
hdpsdb_baseline$# FROM
hdpsdb_baseline$# users
hdpsdb_baseline$# ORDER BY users.userid LIMIT 1;
hdpsdb_baseline$# $BODY$
hdpsdb_baseline-# LANGUAGE 'sql' VOLATILE;
CREATE FUNCTION
hdpsdb_baseline=# ALTER FUNCTION get_users() OWNER TO dgardner;
ALTER FUNCTION
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO public;
GRANT
hdpsdb_baseline=# GRANT EXECUTE ON FUNCTION get_users() TO dgardner;
GRANT
hdpsdb_baseline=# SELECT * FROM get_users();
userid | name | email | enabled
--------------+------+-----------------+---------
aa_test_user | test | test(at)here(dot)there | t
(1 row)
hdpsdb_baseline=#
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Gierth | 2009-12-11 22:17:02 | Re: BUG #5240: Stable Functions that return a table type with a dropped column fail |
Previous Message | Craig Ringer | 2009-12-11 07:53:24 | Re: BUG #5238: frequent signal 11 segfaults |