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

On 10/12/2015 05:29 AM, Andres Freund wrote:
> Hi,
>
> On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote:
>> 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).
>
> My guess is that the problem here is that table level locking prevents
> modification of the "users" type when the table is used, but there's no
> locking preventing the columns to be dropped while the function is
> used. So what happens is that 1) the function is parsed & planned 2)
> DROP COLUMN is executed 3) the contained statement is executed 4) a
> mismatch between the contained statement and the function definition is
> detected.

Except per Albe's[1] example, the above sequence of events does not
fail. It fails in Victors's case when the server is under load, so it
seems there is another factor in play.


[1]
http://www.postgresql.org/message-id/A737B7A37273E048B164557ADEF4A58B50FB756E@ntex2010i.host.magwien.gv.at
>
> Greetings,
>
> Andres Freund
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com