Re: Drop or alter column under load give ERROR #42804 structure of query does not match function result type: - Mailing list pgsql-general

On 10/09/2015 08:30 PM, Victor Blomqvist wrote:
> Note that these errors most of the time only happens very briefly at the
> same time as the ALTER is run. When I did some experiments today the
> server in total had around 3k req/s with maybe 0.1% of them touching the
> table being updated, and the error then happens maybe 1-10% of the times
> I try this operation. If I do the operation on a table with more load
> the error will happen more frequently.

Out of curiosity more then any else, what happens if you ADD a column
instead of DROP a column in the experiment?

>
> Also, someone suggested me to try and recreate the functions returning
> the table as well inside a transaction, but that did not change anything:
> BEGIN;
> ALTER TABLE...
> CREATE OR UPDATE FUNCTION ...
> END;
>
> Thanks for your help so far!
> /Victor
>
> On Fri, Oct 9, 2015 at 10:49 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/09/2015 07:31 AM, Albe Laurenz wrote:
>
>         Adrian Klaver wrote:
>
>                     For the reason why this is happening see:
>
>                     http://www.postgresql.org/docs/9.4/interactive/plpgsql-implementation.html#PLPGSQL-PLAN-CACHING
>
>
>                 Yes, but the ALTER TABLE causes the plan to be recreated
>                 the next time.
>
>
>             But does it? From the link above:
>
>             "Because PL/pgSQL saves prepared statements and sometimes
>             execution
>             plans in this way, SQL commands that appear directly in a
>             PL/pgSQL
>             function must refer to the same tables and columns on every
>             execution;
>             that is, you cannot use a parameter as the name of a table
>             or column in
>             an SQL command. To get around this restriction, you can
>             construct
>             dynamic commands using the PL/pgSQL EXECUTE statement — at
>             the price of
>             performing new parse analysis and constructing a new
>             execution plan on
>             every execution."
>
>             I see '*' as a parameter. Or to put it another way '*' is
>             not referring
>             to the same thing on each execution when you change the
>             table definition
>             under the function.  Now if I can only get the brain to wake
>             up I could
>             find the post where Tom Lane explained this more coherently
>             then I can:)
>
>
>         Session 1:
>
>         test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar
>         NOT NULL, to_be_removed integer NOT NULL);
>         CREATE TABLE
>         test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF
>         users AS
>                  $$BEGIN RETURN QUERY SELECT * FROM users WHERE id =
>         id_; END;$$ LANGUAGE plpgsql;
>         CREATE FUNCTION
>
>         Session 2:
>
>         test=> SELECT id, name FROM select_users(18);
>            id | name
>         ----+------
>         (0 rows)
>
>         Ok, now the plan is cached.
>
>         Now in Session 1:
>
>         test=> ALTER TABLE users DROP COLUMN to_be_removed;
>         ALTER TABLE
>
>         Session2:
>
>         test=> SELECT id, name FROM select_users(18);
>            id | name
>         ----+------
>         (0 rows)
>
>         No error.  This is 9.4.4.
>
>
>     I stand corrected. I also tried on Postgres 9.3.7, which is a close
>     as I could get to OP's 9.3.5 and it worked. Will have to rethink my
>     assumptions.
>
>
>
>         Yours,
>         Laurenz Albe
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com