Hello
there is fix for bug Re: [BUGS] BUG #4907: stored procedures and changed tables
regards
Pavel Stehule
2009/7/10 Sergey Burladyan <[email protected]>:
> Sergey Burladyan <[email protected]> writes:
>
>> Alvaro Herrera <[email protected]> writes:
>>
>> > Michael Tenenbaum wrote:
>> >
>> > > If I have a stored procedure that returns a set of records of a table, I
>> > > get
>> > > an error message that the procedure's record is the wrong type after I
>> > > change some columns in the table.
>> > >
>> > > Deleting the procedure then rewriting the procedure does not help. The
>> > > only
>> > > thing that works is deleting both the stored procedure and the table and
>> > > starting over again.
>> >
>> > Does it work if you disconnect and connect again?
>>
>> No, example:
>
> More simple:
>
> PostgreSQL 8.4.0 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
> 4.3.3-13) 4.3.3, 32-bit
>
> create table t (i int);
> alter table t add v text; alter table t drop i;
> create function foo() returns setof t language plpgsql as $$begin return
> query select * from t; end$$;
> select foo();
> ERROR: 42804: structure of query does not match function result type
> ПОДРОБНО: Number of returned columns (1) does not match expected column
> count (2).
> КОНТЕКСТ: PL/pgSQL function "foo" line 1 at RETURN QUERY
> РАСПОЛОЖЕНИЕ: validate_tupdesc_compat, pl_exec.c:5143
>
> So, function with RETURNS SETOF tbl does not work if it created after ALTER
> TABLE
>
> 8.3.7 too:
>
> PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian
> 4.3.3-5) 4.3.3
>
> create table t (i int);
> alter table t add v text; alter table t drop i;
> create function foo() returns setof t language plpgsql as $$begin return
> query select * from t; end$$;
> select * from foo();
> ERROR: 42804: structure of query does not match function result type
> КОНТЕКСТ: PL/pgSQL function "foo" line 1 at RETURN QUERY
> РАСПОЛОЖЕНИЕ: exec_stmt_return_query, pl_exec.c:2173
>
>
> --
> Sergey Burladyan
>
> --
> Sent via pgsql-bugs mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-bugs
>
*** ./src/pl/plpgsql/src/pl_exec.c.orig 2009-07-12 17:22:57.268901328 +0200
--- ./src/pl/plpgsql/src/pl_exec.c 2009-07-12 16:57:37.037896969 +0200
***************
*** 2284,2289 ****
--- 2284,2294 ----
{
Portal portal;
uint32 processed = 0;
+ int i;
+ bool dropped_columns = false;
+ Datum *dvalues;
+ bool *nulls;
+ int natts;
if (!estate->retisset)
ereport(ERROR,
***************
*** 2308,2318 ****
validate_tupdesc_compat(estate->rettupdesc, portal->tupDesc,
"structure of query does not match function result type");
while (true)
{
MemoryContext old_cxt;
- int i;
SPI_cursor_fetch(portal, true, 50);
if (SPI_processed == 0)
--- 2313,2330 ----
validate_tupdesc_compat(estate->rettupdesc, portal->tupDesc,
"structure of query does not match function result type");
+ natts = estate->rettupdesc->natts;
+
+ if (natts > portal->tupDesc->natts)
+ {
+ dropped_columns = true;
+ dvalues = (Datum *) palloc0(natts * sizeof(Datum));
+ nulls = (bool *) palloc(natts * sizeof(bool));
+ }
while (true)
{
MemoryContext old_cxt;
SPI_cursor_fetch(portal, true, 50);
if (SPI_processed == 0)
***************
*** 2323,2335 ****
{
HeapTuple tuple = SPI_tuptable->vals[i];
! tuplestore_puttuple(estate->tuple_store, tuple);
processed++;
}
MemoryContextSwitchTo(old_cxt);
SPI_freetuptable(SPI_tuptable);
}
SPI_freetuptable(SPI_tuptable);
SPI_cursor_close(portal);
--- 2335,2374 ----
{
HeapTuple tuple = SPI_tuptable->vals[i];
! if (!dropped_columns)
! tuplestore_puttuple(estate->tuple_store, tuple);
! else
! {
! int anum;
! int j = 0;
! bool isnull;
!
! for (anum = 0; anum < natts; anum++)
! {
! if (estate->rettupdesc->attrs[anum]->attisdropped)
! nulls[anum] = true;
! else
! {
! dvalues[anum] = SPI_getbinval(tuple, portal->tupDesc,
! ++j, &isnull);
! nulls[anum] = isnull;
! }
! }
! tuple = heap_form_tuple(estate->rettupdesc, dvalues, nulls);
! tuplestore_puttuple(estate->tuple_store, tuple);
! }
processed++;
}
MemoryContextSwitchTo(old_cxt);
SPI_freetuptable(SPI_tuptable);
}
+
+ if (dropped_columns)
+ {
+ pfree(dvalues);
+ pfree(nulls);
+ }
SPI_freetuptable(SPI_tuptable);
SPI_cursor_close(portal);
***************
*** 5127,5132 ****
--- 5166,5172 ----
validate_tupdesc_compat(TupleDesc expected, TupleDesc returned, const char *msg)
{
int i;
+ int j = 0;
const char *dropped_column_type = gettext_noop("N/A (dropped column)");
if (!expected || !returned)
***************
*** 5134,5153 ****
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("%s", _(msg))));
- if (expected->natts != returned->natts)
- ereport(ERROR,
- (errcode(ERRCODE_DATATYPE_MISMATCH),
- errmsg("%s", _(msg)),
- errdetail("Number of returned columns (%d) does not match "
- "expected column count (%d).",
- returned->natts, expected->natts)));
-
for (i = 0; i < expected->natts; i++)
! if (expected->attrs[i]->atttypid != returned->attrs[i]->atttypid)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("%s", _(msg)),
! errdetail("Returned type %s does not match expected type "
"%s in column \"%s\".",
OidIsValid(returned->attrs[i]->atttypid) ?
format_type_be(returned->attrs[i]->atttypid) :
--- 5174,5186 ----
(errcode(ERRCODE_DATATYPE_MISMATCH),
errmsg("%s", _(msg))));
for (i = 0; i < expected->natts; i++)
! if (!expected->attrs[i]->attisdropped)
! if (expected->attrs[i]->atttypid != returned->attrs[j++]->atttypid)
! ereport(ERROR,
! (errcode(ERRCODE_DATATYPE_MISMATCH),
! errmsg("%s", _(msg)),
! errdetail("Returned type %s does not match expected type "
"%s in column \"%s\".",
OidIsValid(returned->attrs[i]->atttypid) ?
format_type_be(returned->attrs[i]->atttypid) :
*** ./src/test/regress/expected/plpgsql.out.orig 2009-07-12 17:19:13.471901827 +0200
--- ./src/test/regress/expected/plpgsql.out 2009-07-12 17:20:34.917899093 +0200
***************
*** 3285,3290 ****
--- 3285,3342 ----
(4 rows)
drop function return_dquery();
+ -- fix return query and dropped columns bug
+ create table tabwithcols(a int, b int, c int, d int);
+ insert into tabwithcols values(10,20,30,40),(50,60,70,80);
+ create or replace function returnqueryf()
+ returns setof tabwithcols as $$
+ begin
+ return query select * from tabwithcols;
+ return query execute 'select * from tabwithcols';
+ end;
+ $$ language plpgsql;
+ select * from returnqueryf();
+ a | b | c | d
+ ----+----+----+----
+ 10 | 20 | 30 | 40
+ 50 | 60 | 70 | 80
+ 10 | 20 | 30 | 40
+ 50 | 60 | 70 | 80
+ (4 rows)
+
+ alter table tabwithcols drop column b;
+ alter table tabwithcols drop column c;
+ select * from returnqueryf();
+ a | d
+ ----+----
+ 10 | 40
+ 50 | 80
+ 10 | 40
+ 50 | 80
+ (4 rows)
+
+ alter table tabwithcols drop column d;
+ select * from returnqueryf();
+ a
+ ----
+ 10
+ 50
+ 10
+ 50
+ (4 rows)
+
+ alter table tabwithcols add column d int;
+ select * from returnqueryf();
+ a | d
+ ----+---
+ 10 |
+ 50 |
+ 10 |
+ 50 |
+ (4 rows)
+
+ drop function returnqueryf();
+ drop table tabwithcols;
-- Tests for 8.4's new RAISE features
create or replace function raise_test() returns void as $$
begin
*** ./src/test/regress/sql/plpgsql.sql.orig 2009-07-12 17:10:26.186902373 +0200
--- ./src/test/regress/sql/plpgsql.sql 2009-07-12 17:19:00.836898296 +0200
***************
*** 2684,2689 ****
--- 2684,2719 ----
drop function return_dquery();
+ -- fix return query and dropped columns bug
+ create table tabwithcols(a int, b int, c int, d int);
+ insert into tabwithcols values(10,20,30,40),(50,60,70,80);
+
+ create or replace function returnqueryf()
+ returns setof tabwithcols as $$
+ begin
+ return query select * from tabwithcols;
+ return query execute 'select * from tabwithcols';
+ end;
+ $$ language plpgsql;
+
+ select * from returnqueryf();
+
+ alter table tabwithcols drop column b;
+ alter table tabwithcols drop column c;
+
+ select * from returnqueryf();
+
+ alter table tabwithcols drop column d;
+
+ select * from returnqueryf();
+
+ alter table tabwithcols add column d int;
+
+ select * from returnqueryf();
+
+ drop function returnqueryf();
+ drop table tabwithcols;
+
-- Tests for 8.4's new RAISE features
create or replace function raise_test() returns void as $$
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
