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

On Sat, Oct 10, 2015 at 10:00 PM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>