Skip site navigation (1) Skip section navigation (2)

BUG #5240: Stable Functions that return a table type with a dropped column fail

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 (view raw or flat)
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=#

Responses

pgsql-bugs by date

Next:From: Andrew GierthDate: 2009-12-11 22:17:02
Subject: Re: BUG #5240: Stable Functions that return a table type with a dropped column fail
Previous:From: Craig RingerDate: 2009-12-11 07:53:24
Subject: Re: BUG #5238: frequent signal 11 segfaults

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group