PostgreSQL: Add or drop column under load give wrong number of columns errors

ddlpostgresqlpostgresql-9.3

I have a heavily used PostgreSQL database. Sometimes I need to add/remove columns, preferably without any service interruptions.

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 we have user defined functions for all operations, which are run in the following manner:

Table and function definitions:

create table a(
    id serial primary key,
    x integer
);

create or replace function select_a() returns setof a AS
$$
begin
    return query
    select a.* from a;
end;
$$ language plpgsql;

Then the actual queries are run by our application as

SELECT id FROM select_a();

However, if I add a column with ALTER TABLE users ADD COLUMN y text; while the system is under load i sometimes (more frequently and persistent the more load the system is experiencing) 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).

Can this be avoided somehow, or do I need to take the system offline during these kind of changes?

To recreate this follow these steps:

create the above table and function as give above

Create one file loop_alter.sql

#!/usr/bin/env bash
for i in {0..1000}; do
echo "alter table a add column y text; alter table a drop column y;"
done;

Create one file loop_select.sql

#!/usr/bin/env bash
for i in {0..100000} do
echo "select * from select_a() limit 1;"
done;

Run the two files simultaneously with psql

In one terminal: ./loop_alter.sql | psql

In another: ./loop_select.sql | psql

Best Answer

If your function truly uses SELECT * then I would suggest switching to using an explicit list of fields and then separate out the amendment of the table and the function.

ADDING COLUMNS: Alter the table first, then change the function once the table is complete.

DELETING COLUMNS: Change the function first and then the table. If the table is large and under heavy load I'd still expect problems.

Related Solutions

Postgresql – Concatenation of setof type or setof record

The approach you're using is unnecessarily complex - and very inefficient. Instead of the first function use:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
SELECT $1, generate_series(0,$2);
$$                          
language sql;

or better, get rid of it entirely and write the whole operation like this:

-- Sample data creation:
CREATE TABLE my_obj(id bigint, obj_value integer);
insert into my_obj(id,obj_value) VALUES (1712437,2),(17000,5);

-- and the query:
SELECT id, generate_series(0,obj_value) FROM my_obj;

Resulting in:

regress=> SELECT id, generate_series(0,obj_value) FROM my_obj;
   id    | generate_series 
---------+-----------------
 1712437 |               0
 1712437 |               1
 1712437 |               2
   17000 |               0
   17000 |               1
   17000 |               2
   17000 |               3
   17000 |               4
   17000 |               5
(9 rows)

This exploits PostgreSQL's behaviour with set-returning functions called in the SELECT list. Once PostgreSQL 9.3 comes out it can be replaced with a standards-compliant LATERAL query.

Since it turns out your question was a simplified version of the real problem, let's tackle that. I'll work with the simplified compute_pair_id_value above to avoid the hassle of plpython3. Here's how to do what you want:

SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;

Result:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

but again, be warned that compute_pair_id_value will be called more than once. This is a limitation of PostgreSQL's query executor that can be avoided in 9.3 with LATERAL support, but as far as I know you're stuck with it in 9.2 and below. Observe:

create or replace function compute_pair_id_value(id bigint, value integer)
    returns setof pair_id_value
as $$
BEGIN
  RAISE NOTICE 'compute_pair_id_value(%,%)',id,value;
  RETURN QUERY SELECT $1, generate_series(0,$2);
END;
$$             
language plpgsql;

output:

regress=> SELECT (compute_pair_id_value(id,obj_value)).* FROM my_obj;
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

See how compute_pair_id_value is called once per output column?

There is a workaround: Another layer of subquery to unpack the composite type result. See:

regress=> SELECT (val).* FROM (SELECT compute_pair_id_value(id,obj_value) FROM my_obj) x(val);
NOTICE:  compute_pair_id_value(1712437,2)
NOTICE:  compute_pair_id_value(17000,5)
   id    | value 
---------+-------
 1712437 |     0
 1712437 |     1
 1712437 |     2
   17000 |     0
   17000 |     1
   17000 |     2
   17000 |     3
   17000 |     4
   17000 |     5
(9 rows)

You can use the same technique in your code if you really must LOOP over the results (it's slow to do that, so avoid it if you can).

Postgresql – Are there other ways to select a dynamic list of columns

Another way, similar to what I proposed to your previous question: Return a set of well known type. Since your column list is dynamic, create a temporary table for the purpose. This announces the type to the system. As a side-effect you get a temp table to keep results for the duration of the session - like you needed in your last question.

CREATE OR REPLACE FUNCTION select_prices(_tbl anyelement, _cols text)
 RETURNS SETOF anyelement AS
$func$
BEGIN

RETURN QUERY EXECUTE
'SELECT ' || colList || '
 FROM   prices
 WHERE  ...
 ORDER  BY ...';

END
$func$ LANGUAGE plpgsql;

Call:

CREATE TEMP TABLE t (col1, int, col2, date);
SELECT * FROM select_prices(NULL::t, 'col1, col2')

Or, to keep results in the temp table:

INSERT INTO t
SELECT * FROM select_prices(NULL::t, 'col1, col2')

If you need multiple tables in the same sessions, employ a sequence to get unique names. Related answer on SO:
Create a temporary table from a selection or insert if table already exist

However, this method (just like the other two in your question) are susceptible to SQL injection. You need to make sure it can't be abused.
SQL injection in Postgres functions vs prepared queries

Again, I would try to use this simple statement instead:

CREATE TEMP TABLE t AS
SELECT col1, col2 FROM prices;

Best Answer

Related Solutions

Postgresql – Concatenation of setof type or setof record

Postgresql – Are there other ways to select a dynamic list of columns

Related Question