From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Pavel Hanák <hanak(at)is-it(dot)eu> |
Cc: | "pgsql-bugs(at)postgresql(dot)org" <pgsql-bugs(at)postgresql(dot)org> |
Subject: | Re: Problems with "pg.dropped" column after upgrade 9.5 to 9.6 |
Date: | 2016-11-02 04:43:32 |
Message-ID: | CAFj8pRDUSTNoX8zJSMLy=r45z_Tq-OUwd5S7_HGOwtxHZN-yAg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
2016-11-01 22:56 GMT+01:00 Pavel Hanák <hanak(at)is-it(dot)eu>:
> Hi,
>
> I've got very the similar problem as described in the message
> with the subject "got some errors after upgrade poestgresql from 9.5 to
> 9.6".
>
> I'll try to describe this very strange behaviour.
>
> I have a table A which has some "pg.dropped" attribute in
> pg_attribute table. It looks like:
>
> select attname, attnum
> from pg_attribute
> where attrelid = A::regclass and attisdropped;
>
> attname | attnum
> -------------------------------+--------
> ........pg.dropped.57........ | 57
> (1 row)
>
> Now, I create SQL function doing only update on this table
> when the boolean parameter of the function is True:
>
> CREATE OR REPLACE FUNCTION _test_sql_update(in do_update boolean)
> RETURNS VOID LANGUAGE sql VOLATILE AS $$
> update A
> set col = NULL
> where do_update;
> $$;
>
> Now running:
>
> select _test_sql_update(False);
>
> returns this error:
>
> ERROR: table row type and query-specified row type do not match
> DETAIL: Query provides a value for a dropped column at ordinal position
> 57.
> CONTEXT: SQL function "_test_sql_update" statement 1
>
This is runtime error - this check is evaluated, when function returns one
or more rows
>
> If I don't use the parameter in "where" and instead I use the constant
> False directly, everything works:
>
> CREATE OR REPLACE FUNCTION _test_sql_update(in do_update boolean)
> RETURNS VOID LANGUAGE sql VOLATILE AS $$
> update A
> set col = NULL
> where False;
> $$;
>
> select _test_sql_update(False);
>
> SQL=# _test_sql
> -----------
>
> (1 row)
>
in this case, the check is not evaluated because there is not any row on
result
>
> If I define the function as plpgsql, everything is also working:
>
> CREATE OR REPLACE FUNCTION _test_plpgsql_update(in do_update boolean)
> RETURNS VOID LANGUAGE plpgsql VOLATILE AS $$
> BEGIN
> update A
> set col = NULL
> where do_update;
> END;
> $$;
>
>
> My conclusion is:
>
> The problem occurs only under these circumstances:
>
> - Postgresql 9.6 (no problem in 9.5)
>
> - SQL function doing update
>
> - There is a boolean parameter of the fucntion used in the update command
> and the table which is updated has some attisdropped attributes
>
> Can anybody explain what is the problem?
>
The pipeline of SQL and PLpgSQL functions is pretty different - this is new
regression in 9.6 code.
Regards
Pavel
>
> Thanks
> Pavel
>
From | Date | Subject | |
---|---|---|---|
Next Message | Pavel Stehule | 2016-11-02 06:32:02 | Re: Problems with "pg.dropped" column after upgrade 9.5 to 9.6 |
Previous Message | Victor Colborn | 2016-11-02 01:37:25 | Re: BUG #14408: Schema not found error when 2 or more indices declared on temporary table |