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

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