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