Thread: Drop or alter column under load give ERROR #42804 structure of query does not match function result type:

On Fri, 9 Oct 2015 14:32:44 +0800
Victor Blomqvist <vb@viblo.se> 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.

I seriously doubt if Paul did enough research to be sure that "safe" is an
absolute term for that list.

> 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.

Sure it is ... the function does SELECT *, which absolutely includes the
to_be_removed column. The fact that you ignore that column in a
subsequent superselect doesn't mean that the query in the function knows
to do so.

> 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).

This is a bit surprising to me. I would expect Postgres to have one or the
other definition of that row within a single transaction, but what seems to
be happening is that the ALTER causes the row definition to be changed in
the middle of the transaction, thus the the function may return 3 columns,
but when the outer query checks the type, it sees that it should only
have 2.

> 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 reference, there was a similar but not same issue posted to psql-bugs a
> long time ago:
> http://www.postgresql.org/message-id/8254631e-61a5-4c03-899f-22fdcf369a46@e23g2000vbe.googlegroups.com
>
> I posted this same question at dba.stackexchange and got the advice to
> repost here:
> http://dba.stackexchange.com/questions/117511/postgresql-drop-column-under-load-give-wrong-number-of-columns-errors

This has a lot to do with internals. You should wait a bit to see if you
get a good answer, but if not you might need to post to the hackers list
so the developers can chime in. My opinion is that this is a bug, but it's
an obscure enough bug that it's not surprising that it's gone unfixed for
a while.

--
Bill Moran

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


>
> For reference, there was a similar but not same issue posted to
> psql-bugs a long time ago:
> http://www.postgresql.org/message-id/8254631e-61a5-4c03-899f-22fdcf369a46@e23g2000vbe.googlegroups.com
>
> I posted this same question at dba.stackexchange and got the advice to
> repost here:
> http://dba.stackexchange.com/questions/117511/postgresql-drop-column-under-load-give-wrong-number-of-columns-errors
>
> Thanks!
> Victor


--
Adrian Klaver
adrian.klaver@aklaver.com

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

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@aklaver.com

Adrian Klaver wrote:
>>> 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:)

Session 1:

test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT NULL, to_be_removed integer NOT NULL);
CREATE TABLE
test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
       $$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_; END;$$ LANGUAGE plpgsql;
CREATE FUNCTION

Session 2:

test=> SELECT id, name FROM select_users(18);
 id | name
----+------
(0 rows)

Ok, now the plan is cached.

Now in Session 1:

test=> ALTER TABLE users DROP COLUMN to_be_removed;
ALTER TABLE

Session2:

test=> SELECT id, name FROM select_users(18);
 id | name
----+------
(0 rows)

No error.  This is 9.4.4.

Yours,
Laurenz Albe

On 10/09/2015 07:31 AM, Albe Laurenz wrote:
> Adrian Klaver wrote:
>>>> 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:)
>
> Session 1:
>
> test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT NULL, to_be_removed integer NOT NULL);
> CREATE TABLE
> test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
>         $$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_; END;$$ LANGUAGE plpgsql;
> CREATE FUNCTION
>
> Session 2:
>
> test=> SELECT id, name FROM select_users(18);
>   id | name
> ----+------
> (0 rows)
>
> Ok, now the plan is cached.
>
> Now in Session 1:
>
> test=> ALTER TABLE users DROP COLUMN to_be_removed;
> ALTER TABLE
>
> Session2:
>
> test=> SELECT id, name FROM select_users(18);
>   id | name
> ----+------
> (0 rows)
>
> No error.  This is 9.4.4.

I stand corrected. I also tried on Postgres 9.3.7, which is a close as I
could get to OP's 9.3.5 and it worked. Will have to rethink my assumptions.

>
> Yours,
> Laurenz Albe
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/09/2015 08:30 PM, Victor Blomqvist wrote:
> Note that these errors most of the time only happens very briefly at the
> same time as the ALTER is run. When I did some experiments today the
> server in total had around 3k req/s with maybe 0.1% of them touching the
> table being updated, and the error then happens maybe 1-10% of the times
> I try this operation. If I do the operation on a table with more load
> the error will happen more frequently.

Out of curiosity more then any else, what happens if you ADD a column
instead of DROP a column in the experiment?

>
> Also, someone suggested me to try and recreate the functions returning
> the table as well inside a transaction, but that did not change anything:
> BEGIN;
> ALTER TABLE...
> CREATE OR UPDATE FUNCTION ...
> END;
>
> Thanks for your help so far!
> /Victor
>
> On Fri, Oct 9, 2015 at 10:49 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/09/2015 07:31 AM, Albe Laurenz wrote:
>
>         Adrian Klaver wrote:
>
>                     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:)
>
>
>         Session 1:
>
>         test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar
>         NOT NULL, to_be_removed integer NOT NULL);
>         CREATE TABLE
>         test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF
>         users AS
>                  $$BEGIN RETURN QUERY SELECT * FROM users WHERE id =
>         id_; END;$$ LANGUAGE plpgsql;
>         CREATE FUNCTION
>
>         Session 2:
>
>         test=> SELECT id, name FROM select_users(18);
>            id | name
>         ----+------
>         (0 rows)
>
>         Ok, now the plan is cached.
>
>         Now in Session 1:
>
>         test=> ALTER TABLE users DROP COLUMN to_be_removed;
>         ALTER TABLE
>
>         Session2:
>
>         test=> SELECT id, name FROM select_users(18);
>            id | name
>         ----+------
>         (0 rows)
>
>         No error.  This is 9.4.4.
>
>
>     I stand corrected. I also tried on Postgres 9.3.7, which is a close
>     as I could get to OP's 9.3.5 and it worked. Will have to rethink my
>     assumptions.
>
>
>
>         Yours,
>         Laurenz Albe
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Victor Blomqvist wrote:
[race condition causes errors due to stale plans immediately after ALTER TABLE DROP]
> Note that these errors most of the time only happens very briefly at the same time as the ALTER is
> run. When I did some experiments today the server in total had around 3k req/s with maybe 0.1% of them
> touching the table being updated, and the error then happens maybe 1-10% of the times I try this
> operation. If I do the operation on a table with more load the error will happen more frequently.

As far as I gleaned from reading the source, plan cache invalidation happens by signals
sent to the other backends, so I can see why there can be small delays.
I wonder if there is any good way to improve this.

Yours,
Laurenz Albe

On 2015-10-12 08:07:54 +0000, Albe Laurenz wrote:
> Victor Blomqvist wrote:
> [race condition causes errors due to stale plans immediately after ALTER TABLE DROP]
> > Note that these errors most of the time only happens very briefly at the same time as the ALTER is
> > run. When I did some experiments today the server in total had around 3k req/s with maybe 0.1% of them
> > touching the table being updated, and the error then happens maybe 1-10% of the times I try this
> > operation. If I do the operation on a table with more load the error will happen more frequently.
>
> As far as I gleaned from reading the source, plan cache invalidation happens by signals
> sent to the other backends, so I can see why there can be small delays.
> I wonder if there is any good way to improve this.

The signal based part is only relevant for idle backends, to wake them
up to process pending invalidations. The aim is to shrink the size of
the invalidation queue.

Normal invalidations are performed whenever a relation is locked:
void
LockRelationOid(Oid relid, LOCKMODE lockmode)
{
    LOCKTAG        tag;
    LockAcquireResult res;

    SetLocktagRelationOid(&tag, relid);

    res = LockAcquire(&tag, lockmode, false, false);

    /*
     * Now that we have the lock, check for invalidation messages, so that we
     * will update or flush any stale relcache entry before we try to use it.
     * RangeVarGetRelid() specifically relies on us for this.  We can skip
     * this in the not-uncommon case that we already had the same type of lock
     * being requested, since then no one else could have modified the
     * relcache entry in an undesirable way.  (In the case where our own xact
     * modifies the rel, the relcache update happens via
     * CommandCounterIncrement, not here.)
     */
    if (res != LOCKACQUIRE_ALREADY_HELD)
        AcceptInvalidationMessages();
}

I've not investigated what the OP's problem is.

Greetings,

Andres Freund

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.

Greetings,

Andres Freund

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@ntex2010i.host.magwien.gv.at
>
> Greetings,
>
> Andres Freund
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 2015-10-12 06:42:52 -0700, Adrian Klaver wrote:
> >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.

The above sequence is only problematic if 2) happens exactly between 1)
and 3), which is not particularly likely given that 1) is a very quick
operation.

Greetings,

Andres Freund

Andres Freund <andres@anarazel.de> writes:
> On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote:
>> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
>> $$
>> BEGIN
>> RETURN QUERY SELECT * FROM users WHERE id = id_;
>> END;
>> $$ LANGUAGE plpgsql;

> 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.

The query plan as such does get refreshed, I believe.  The problem is that
plpgsql has no provision for the definition of a named composite type to
change after a function's been parsed.  This applies to variables of named
composite types for sure, and based on this example I think it must apply
to the function result type as well, though I'm too lazy to go check the
code right now.

We have had past discussions about fixing this.  I believe it would
require getting rid of use of plpgsql's "row" infrastructure for named
composites, at least in most cases, and going over to the "record"
infrastructure instead.  In the past the conversations have stalled as
soon as somebody complained that that would probably make some operations
slower.  I don't entirely understand that objection, since (a) some other
operations would probably get faster, and (b) performance does not trump
correctness.  But that's where the discussion stands at the moment.

            regards, tom lane

On 10/12/2015 06:53 AM, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
>> On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote:
>>> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
>>> $$
>>> BEGIN
>>> RETURN QUERY SELECT * FROM users WHERE id = id_;
>>> END;
>>> $$ LANGUAGE plpgsql;
>
>> 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.
>
> The query plan as such does get refreshed, I believe.  The problem is that
> plpgsql has no provision for the definition of a named composite type to
> change after a function's been parsed.  This applies to variables of named
> composite types for sure, and based on this example I think it must apply
> to the function result type as well, though I'm too lazy to go check the
> code right now.

That makes sense. The problem is that I cannot square that with Albe's
example, which I tested also:

"
Session 1:

test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT
NULL, to_be_removed integer NOT NULL);
CREATE TABLE
test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
        $$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_; END;$$
LANGUAGE plpgsql;
CREATE FUNCTION

Session 2:

test=> SELECT id, name FROM select_users(18);
  id | name
----+------
(0 rows)

Ok, now the plan is cached.

Now in Session 1:

test=> ALTER TABLE users DROP COLUMN to_be_removed;
ALTER TABLE

Session2:

test=> SELECT id, name FROM select_users(18);
  id | name
----+------
(0 rows)

No error.  This is 9.4.4.
"

>
> We have had past discussions about fixing this.  I believe it would
> require getting rid of use of plpgsql's "row" infrastructure for named
> composites, at least in most cases, and going over to the "record"
> infrastructure instead.  In the past the conversations have stalled as
> soon as somebody complained that that would probably make some operations
> slower.  I don't entirely understand that objection, since (a) some other
> operations would probably get faster, and (b) performance does not trump
> correctness.  But that's where the discussion stands at the moment.
>
>             regards, tom lane
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On 10/12/2015 07:53 PM, Victor Blomqvist wrote:
> Do you have some advice how to design my functions to work around this
> problem?
>
> If I understand your conversation correct the problem is returning the
> rowtype users from the function. If so, I can think of two workarounds
> (both quite inconvenient and complex):
>
> 1. Use RETURNS TABLE(...) together with not selecting * in the functions.
> 2. Use RETURNS <custom type> also without select * in the functions.

Might want to investigate the record return type:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-overview.html#PLPGSQL-ARGS-RESULTS

40.1.2. Supported Argument and Result Data Types

"It is also possible to declare a PL/pgSQL function as returning record,
which means that the result is a row type whose columns are determined
by specification in the calling query, as discussed in Section 7.2.1.4."

The section that explains difference between declared type record and
returned type record:

http://www.postgresql.org/docs/9.4/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS


How to use a returned record in query:

http://www.postgresql.org/docs/9.4/interactive/queries-table-expressions.html#QUERIES-TABLEFUNCTIONS

See bottom of section.


Basically all the above leaves it up to the calling query to 'shape' the
output. Not sure if that will work for you.

>
> What do other people do in this situation? For our system the lowest
> load is in the late night, 04 - 06, which might have sufficiently low
> load to avoid the issue, but I would much prefer to run schema changes
> when there are people in the office.
>
> /Victor
>
> On Mon, Oct 12, 2015 at 10:15 PM, Adrian Klaver
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
>
>     On 10/12/2015 06:53 AM, Tom Lane wrote:
>
>         Andres Freund <andres@anarazel.de <mailto:andres@anarazel.de>>
>         writes:
>
>             On 2015-10-09 14:32:44 +0800, Victor Blomqvist wrote:
>
>                 CREATE FUNCTION select_users(id_ integer) RETURNS SETOF
>                 users AS
>                 $$
>                 BEGIN
>                 RETURN QUERY SELECT * FROM users WHERE id = id_;
>                 END;
>                 $$ LANGUAGE plpgsql;
>
>
>             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.
>
>
>         The query plan as such does get refreshed, I believe.  The
>         problem is that
>         plpgsql has no provision for the definition of a named composite
>         type to
>         change after a function's been parsed.  This applies to
>         variables of named
>         composite types for sure, and based on this example I think it
>         must apply
>         to the function result type as well, though I'm too lazy to go
>         check the
>         code right now.
>
>
>     That makes sense. The problem is that I cannot square that with
>     Albe's example, which I tested also:
>
>     "
>     Session 1:
>
>     test=> CREATE TABLE users (id integer PRIMARY KEY, name varchar NOT
>     NULL, to_be_removed integer NOT NULL);
>     CREATE TABLE
>     test=> CREATE FUNCTION select_users(id_ integer) RETURNS SETOF users AS
>             $$BEGIN RETURN QUERY SELECT * FROM users WHERE id = id_;
>     END;$$ LANGUAGE plpgsql;
>     CREATE FUNCTION
>
>     Session 2:
>
>     test=> SELECT id, name FROM select_users(18);
>       id | name
>     ----+------
>     (0 rows)
>
>     Ok, now the plan is cached.
>
>     Now in Session 1:
>
>     test=> ALTER TABLE users DROP COLUMN to_be_removed;
>     ALTER TABLE
>
>     Session2:
>
>     test=> SELECT id, name FROM select_users(18);
>       id | name
>     ----+------
>     (0 rows)
>
>     No error.  This is 9.4.4.
>     "
>
>
>         We have had past discussions about fixing this.  I believe it would
>         require getting rid of use of plpgsql's "row" infrastructure for
>         named
>         composites, at least in most cases, and going over to the "record"
>         infrastructure instead.  In the past the conversations have
>         stalled as
>         soon as somebody complained that that would probably make some
>         operations
>         slower.  I don't entirely understand that objection, since (a)
>         some other
>         operations would probably get faster, and (b) performance does
>         not trump
>         correctness.  But that's where the discussion stands at the moment.
>
>                                  regards, tom lane
>
>
>
>
>     --
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

Victor Blomqvist <vb@viblo.se> writes:
> In case any of you are interested of recreating this problem, I today had
> the time to create a short example that reproduce the error every time I
> try.

Hmm.  If you just do that serially:

regression=# select * from select_a() ;
 id | x
----+---
(0 rows)

regression=# alter table a add column y text;
ALTER TABLE
regression=# select * from select_a() ;
 id | x | y
----+---+---
(0 rows)

regression=# alter table a drop column y;
ALTER TABLE
regression=# select * from select_a() ;
 id | x
----+---
(0 rows)

So actually, we *are* tracking the change of table rowtype, both at the
level of the query inside the function and at the level of the function
result.  The problem is that the instant at which the result rowtype of
the function is determined (while parsing the outer query) is different
from the instant at which the inner query's result rowtype is determined.

I'm not really sure that there's anything we can, or even should, try
to do about this.  There would be a whole lot of action-at-a-distance
involved and it would be likely to make some other use-cases worse.

A possible user-level answer if you need to make an application robust
against this sort of thing is to take out a low-grade lock on the
table that's determining the function's result type:

begin;
lock table a in access share mode;
select * from select_a();
commit;

Holding the table lock will prevent any other transactions from altering
the table's rowtype while this transaction runs.

            regards, tom lane