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