Re: Bug in RETURN QUERY - Mailing list pgsql-bugs
From | raf |
---|---|
Subject | Re: Bug in RETURN QUERY |
Date | |
Msg-id | 20081127042306.GA15683@raf.org Whole thread Raw |
In response to | Re: Bug in RETURN QUERY (Tom Lane <tgl@sss.pgh.pa.us>) |
List | pgsql-bugs |
Tom Lane wrote: > "Pavel Stehule" <pavel.stehule@gmail.com> writes: > > 2008/9/1 Oleg Serov <serovov@gmail.com>: > >> ERROR: structure of query does not match function result type > >> CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/ > > > this isn't bug, it's feature. > > No, it's a bug, and it's not related to plan caching at all --- even if > you start a fresh session the error persists. The problem is that > plpgsql isn't very good at dealing with rowtypes that contain dropped > columns. Unfortunately Oleg shouldn't hold his breath waiting for a > fix, because it's not trivial. In this example, the function would need > to return a three-column tuple (id, dropped-column, buggy_enum_field) > but the SELECT is only giving it two columns. There isn't anything in > plpgsql that has the ability to convert a tuple to add dropped columns > in the right places. I think we'd consider adding such functionality > as a new feature not a back-patchable bug fix. > > The best near-term workaround would be to handle changes like this by > means of ALTER COLUMN TYPE rather than dropping and re-adding columns. > > regards, tom lane hi tom, i've just come across this bug as well as soon as i dropped some columns (demonstration code below for those anyone who can't remember the bug). i hope i misunderstood your suggested fix. i strongly disagree that the fix is to make it possible for plpgsql to add dropped columns to queries (either automatically or via some explicit syntactic device). the dropped column was dropped after all. it should stay dropped. i don't think anyone would want to add dropped columns to any result sets. it's the automatic rowtype of the table that is wrong, because it has not been updated (and apparently needs to be) to reflect the current state of the table. i hope this does get fixed or dropping columns is a big mistake. i guess i won't be doing that again :) cheers, raf --- demonstrate: dropped column breaks rowtypes ------------------------- create table bug (a integer null, b integer null, c integer null); insert into bug (a, b, c) values (1, 2, 3); create or replace function bug1() returns setof bug stable language plpgsql as $$ begin return query select * from bug; end $$; select * from bug1(); -- Works alter table bug drop b; -- Ending/restarting session here makes no difference select * from bug1(); -- ERROR: structure of query does not match function result type -- Recreating the function explicitly makes no difference create or replace function bug1() returns setof bug stable language plpgsql as $$ begin return query select * from bug; end $$; -- Ending/restarting session here makes no difference select * from bug1(); -- ERROR: structure of query does not match function result type drop table if exists bug cascade;
pgsql-bugs by date: