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. 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
