[BUGS] "ERROR: Query-specified return tuple and actual function return tuple do not match" after dropping column

[Andrzej Kosmala]

PostgreSQL 7.3 on Linux
After dropping column functions return error message: "ERROR:
Query-specified return tuple and actual function return tuple do not match"

template1=# create table test(id integer, cdate timestamp);
CREATE TABLE
template1=# INSERT INTO test VALUES (1,now());
INSERT 17515 1
template1=# INSERT INTO test VALUES (2,now());
INSERT 17516 1
template1=# INSERT INTO test VALUES (3,now());
INSERT 17517 1
template1=# create function ftest() returns setof test as 'select * from
test' language 'sql';
CREATE FUNCTION
template1=# select * from ftest();
 id |   cdate
+
  1 | 2003-01-12 22:17:19.814273
  2 | 2003-01-12 22:17:22.899388
  3 | 2003-01-12 22:17:25.246219
(3 rows)

template1=# alter table test drop column cdate;
ALTER TABLE
template1=# select * from ftest();
ERROR:  Query-specified return tuple and actual function return tuple do not
match
template1=# alter table test add column cdate timestamp;
ALTER TABLE
template1=# select * from ftest();
ERROR:  Query-specified return tuple and actual function return tuple do not
match
template1=# drop function ftest();
DROP FUNCTION
template1=# create function ftest() returns setof test as 'select * from
test' language 'sql';
CREATE FUNCTION
template1=# select * from ftest();
ERROR:  Query-specified return tuple and actual function return tuple do not
match
template1=# create function ftest2() returns setof test as 'select * from
test' language 'sql';
CREATE FUNCTION
template1=# select * from ftest2();
ERROR:  Query-specified return tuple and actual function return tuple do not
match

Yours sincerely,
Andrzej Kosmala


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [BUGS] "ERROR: Query-specified return tuple and actual function

[Andrzej Kosmala]

Tom Lane <[EMAIL PROTECTED]> writes:

> Joe Conway <[EMAIL PROTECTED]> writes:
> > Andrzej Kosmala wrote:
> >> PostgreSQL 7.3 on Linux
> >> After dropping column functions return error message: "ERROR:
> >> Query-specified return tuple and actual function return tuple do not
match"
>
> > Hmmm, looks like nodeFunctionscan.c:tupledesc_mismatch needs to be
> > taught about attisdropped. I'll submit a patch this evening if no one
> > else gets to it first.
>
> Actually, I believe I deliberately left it like that because I was
> concerned about what would happen in this scenario.  I don't think that
> changing tupledesc_mismatch to ignore attisdropped columns will make
> things work nicely.  If a function is expecting to return (a,b,c) and
> then you drop b from the table that defines its return type, is the
> function likely to magically return (a,c)?  I doubt it.  Letting the
> code get past the mismatch check is likely to result in core dumps.
>
> I had toyed with the notion of forbidding column drop (and maybe column
> add too, not sure) whenever a table's rowtype appears as a function
> argument or result type; but I'm not sure that's sufficient to protect
> against problems ...
>

OK, but after dropping column, it is impossible to create _any_ proper
working function. Only drop/create table solves this problem.

template1=# create table test(id integer, name text);
CREATE TABLE
template1=# INSERT INTO test VALUES (1,'a');
INSERT 25332 1
template1=# create function ftest() returns setof test as 'select * from
test' language 'sql';
CREATE FUNCTION
template1=# select * from ftest();
 id | name
+--
  1 | a
(1 row)

template1=# DROP FUNCTION ftest();
DROP FUNCTION
template1=# alter table test drop column name;
ALTER TABLE
template1=# create function ftest() returns setof test as 'select * from
test' language 'sql';
CREATE FUNCTION
template1=# select * from ftest();
ERROR:  Query-specified return tuple and actual function return tuple do not
match

Andrzej Kosmala


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]