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

On 10/09/2015 06:25 AM, Albe Laurenz wrote:
> Adrian Klaver wrote:
>> On 10/08/2015 11:32 PM, Victor Blomqvist wrote:
>>> I have a heavily used PostgreSQL 9.3.5 database on CentOS 6. Sometimes I
>>> need to add/remove columns, preferably without any service
>>> interruptions, but I get temporary errors.
>>>
>>> I follow the safe operations list from
>>> https://www.braintreepayments.com/blog/safe-operations-for-high-volume-postgresql
>>> but many operations cause troubles anyway when the more busy tables are
>>> updated.
>>>
>>> Typically I have user defined functions for all operations, and my table
>>> and functions follow this pattern:
>>>
>>> CREATE TABLE users (
>>>     id integer PRIMARY KEY,
>>>     name varchar NOT NULL,
>>>     to_be_removed integer NOT NULL
>>> );
>>>
>>> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
>>> $$
>>> BEGIN
>>>     RETURN QUERY SELECT * FROM users WHERE id = id_;
>>> END;
>>> $$ LANGUAGE plpgsql;
>>>
>>> Then the actual queries are run by our application as
>>>
>>> SELECT id, name FROM select_users(18);
>>>
>>> As you can see the column to_be_removed is not selected. Then to remove
>>> the column I use:
>>>
>>> ALTER TABLE users DROP COLUMN to_be_removed;
>>>
>>> However, while the system is under load sometimes (more frequently and
>>> persistent the more load the system is experiencing) I get errors like
>>> these:
>>>
>>>       ERROR #42804 structure of query does not match function result
>>> type: Number of returned columns (2) does not match expected column
>>> count (3).
>>>
>>> The same error can happen when columns are added. Can this be avoided
>>> somehow, or do I need to take the system offline during these kind of
>>> changes?
>>
>> 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:)

>
> There must be a race condition that causes other sessions to continue using
> the old plan for a little while.  Don't know if that's as designed.
>
> Yours,
> Laurenz Albe
>


--
Adrian Klaver
adrian.klaver@aklaver.com