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

2016-11-01 22:56 GMT+01:00 Pavel Han=C3=A1k <hanak@is-it.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 =3D 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 =3D 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 positio=
n
> 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 =3D NULL
>        where False;
>   $$;
>
>   select _test_sql_update(False);
>
>   SQL=3D#  _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 =3D 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
>