Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 





On Sat, Oct 10, 2015 at 10:00 PM, Adrian Klaver <adrian.klaver@xxxxxxxxxxx> wrote:
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?

The same behaviour. (Actually its more annoying than when it happens with DROPs since we do ADDs much more often)
 


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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>




--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux