Re: Problems with "pg.dropped" column after upgrade 9.5 to 9.6

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
>