Bug in RETURN QUERY

Hello all SQL BUG CODE:
BEGIN;
SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by
GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );

CREATE TABLE "bug_table" (
"id" BIGINT NOT NULL,
"buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
NULL,
CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
) WITHOUT OIDS;

CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
AS $$
BEGIN
-- @todo hide password
RETURN QUERY (
SELECT *
FROM bug_table
);
END;
$$
LANGUAGE plpgsql STRICT SECURITY DEFINER;

SELECT * FROM buggy_procedure(); -- All Okey
DROP TYPE buggy_enum_first CASCADE;
CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
SELECT * FROM buggy_procedure(); -- Bug
ROLLBACK;
/*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
"test_table_pkey" for table "bug_table"

NOTICE: drop cascades to default for table bug_table column
buggy_enum_fieldNOTICE: drop cascades to table bug_table column
buggy_enum_field
ERROR: structure of query does not match function result type
CONTEXT: PL/pgSQL function "buggy_procedure" line 3 at RETURN QUERY*/

Hello

2008/9/1 Oleg Serov <serovov(at)gmail(dot)com>:
> Hello all SQL BUG CODE:
> BEGIN;
> SELECT version(); -- "PostgreSQL 8.3.3 on i686-redhat-linux-gnu, compiled by
> GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14)"
> CREATE TYPE "buggy_enum_first" AS ENUM ( 'bug1', 'bug2', 'bug3' );
>
> CREATE TABLE "bug_table" (
> "id" BIGINT NOT NULL,
> "buggy_enum_field" "buggy_enum_first" DEFAULT 'bug1'::buggy_enum_first NOT
> NULL,
> CONSTRAINT "test_table_pkey" PRIMARY KEY("id")
> ) WITHOUT OIDS;
>
>
> CREATE FUNCTION buggy_procedure() RETURNS SETOF bug_table
> AS $$
> BEGIN
> -- @todo hide password
> RETURN QUERY (
> SELECT *
> FROM bug_table
> );
> END;
> $$
> LANGUAGE plpgsql STRICT SECURITY DEFINER;
>
> SELECT * FROM buggy_procedure(); -- All Okey
> DROP TYPE buggy_enum_first CASCADE;
> CREATE TYPE "buggy_enum_second" AS ENUM ( 'bug1', 'bug2', 'bug3' );
> ALTER TABLE bug_table ADD COLUMN buggy_enum_field buggy_enum_second;
> SELECT * FROM buggy_procedure(); -- Bug
> ROLLBACK;
> /*NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "test_table_pkey" for table "bug_table"
>
> NOTICE: drop cascades to default for table bug_table column
> buggy_enum_fieldNOTICE: drop cascades to table bug_table column
> buggy_enum_field
> 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. Informations stored in execution plan
are broken, when some references to removed objects are stored in plan
- objects aren't stored by name, but by object id. So, when you drop
any object, then you have to finish session. Note: actually only drop
of table emits plan cache invalidation signal.

Regards
Pavel Stehule

"Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> 2008/9/1 Oleg Serov <serovov(at)gmail(dot)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

Tom Lane wrote:

> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)com> writes:
> > 2008/9/1 Oleg Serov <serovov(at)gmail(dot)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;