6

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:

  1. create the above table and function as give above
  2. 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;
    
  3. Create one file loop_select.sql

    #!/usr/bin/env bash
    for i in {0..100000} do
    echo "select * from select_a() limit 1;"
    done;
    
  4. Run the two files simultaneously with psql

    In one terminal: ./loop_alter.sql | psql

    In another: ./loop_select.sql | psql

  • 4
    Relevant mailing list post: postgresql.org/message-id/10322.1241803607@sss.pgh.pa.us . It's old, not sure if any improvements were made since then. You might have more luck following up on pgsql-general or raising a bug on pgsql-bugs. – Craig Ringer Oct 9 '15 at 6:07
  • ALTER TABLE takes an ACCESS EXCLUSIVE lock on the table (at least it's supposed to). It may be hard to ensure that the row type to go with the table is not in use either, especially by prepared statements (and plpgsql functions, maybe others too) - or maybe it's an oversight, i.e. a bug ... – Erwin Brandstetter Oct 9 '15 at 14:53
  • I did a report on the pgsql-general mailing list, but so far no solution. – viblo Oct 10 '15 at 3:36
  • Discussion from pgsql-general mailing list: postgresql.org/message-id/flat/… – viblo Mar 9 '17 at 11:43
1

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.

  • There are some problems to this workaround. If there are 10 functions returning the same columns, then all of them have to list all columns instead of using the shared table type ("setof a" in my example). Also I think it wont be possible to do it without renaming the function each time its updated (since if you return record you must query out all returned columns in the select ... from myfunc query) – viblo Mar 9 '17 at 12:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.