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

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

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.

Thanks for the report.

Joe

Joe Conway <mail@joeconway.com> 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 ...

            regards, tom lane

Tom Lane wrote:
 > Joe Conway <mail@joeconway.com> writes:
 >> 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.

Assuming tupledesc_mismatch ignored attisdropped attributes, and the
function still output the dropped attribute, the error would be
triggered due to a mismatch. But then you could adjust your function and
be on your merry way.

In the case where the function is returning a whole table tuple (e.g.
select * from...), everthing should work correctly (and as expected).

Without this change, any table that has had a column dropped would not
be able to be used as a function's return type at all.

 > Letting the code get past the mismatch check is likely to result in
 > core dumps.

I don't see how. The code would still ensure that the returned tuple and
defined tuple match, wouldn't it?

start with table test, attributes (a,b,c)
drop attribute b from test
tupledesc_mismatch checks that function's output is (a,c)
if function actually outputs (a,c), no problem

Am I just being thick-headed?

Joe

Joe Conway <mail@joeconway.com> writes:
> Without this change, any table that has had a column dropped would not
> be able to be used as a function's return type at all.

Yup, that was the idea ;-)

> start with table test, attributes (a,b,c)
> drop attribute b from test
> tupledesc_mismatch checks that function's output is (a,c)
> if function actually outputs (a,c), no problem

But there *is* a problem: tuples conforming to test's tupledescriptor
must actually still have three columns.  If the function only outputs
two columns, it won't work.

You might possibly be able to make it work by restructuring the tuple to
have a null column in the middle, but I'm not sure about all the
consequences.

In the meantime, tupledesc_mismatch *is correct as it stands* --- if the
test it embodies is not satisfied, you will get core dumps.  What you
would need to do if you do not like the present behavior is to be
willing to restructure the function's result tuple.

I believe that there are comparable problems in the other direction:
it's possible that the tuple actually returned by the function might
contain attisdropped columns that you'd need to ignore to make it match
up to the expected result type.  I'm not quite sure about how to put
together mapping logic that handles all these cases, but it could
probably be done.

However, all this is just the tip of the iceberg.  Suppose I have
defined a view

    CREATE VIEW v AS SELECT a, b, c FROM myfunction()

where myfunction returns a rowtype containing a,b,c.  What happens if
someone drops column b from the table defining the rowtype?

            regards, tom lane

On Mon, Jan 13, 2003 at 02:52:25PM -0500, Tom Lane wrote:
> Joe Conway <mail@joeconway.com> writes:
> > 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.

Notice, please, that the bug does not only affect existing functions
returning table rows, it also prevents new functions from being
created.  I think this should not take place.  I can agree that one
should not be able to drop columns when there are some functions using
the table as an argument or return type, but it should be possible to
drop these functions, then drop some columns and create completely new
functions.  And now it ends with an error. :(

Best regards,

--
Adam Buraczewski <adamb@polbox.pl> * Linux registered user #165585
GCS/TW d- s-:+>+:- a- C+++(++++) UL++++$ P++ L++++ E++ W+ N++ o? K? w--
O M- V- PS+ !PE Y PGP+ t+ 5 X+ R tv- b+ DI? D G++ e+++>++++ h r+>++ y?

Tom Lane <tgl@sss.pgh.pa.us> writes:

> Joe Conway <mail@joeconway.com> 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