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@xxxxxxxxxxxxxxxxxxxxxxxxxxxx
Greetings, Andres Freund
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
