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

2015-11-04 Thread Victor Blomqvist 
On Wed, Nov 4, 2015 at 1:31 AM, Tom Lane  wrote:

> Victor Blomqvist  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
>

Ok, then I dont hope for a fix in a future Postgres version.

Given this problem it seems like its generally a bad idea to ever ALTER
anything that is returned from a function, unless you want to add a lock
around the function call (which get a bit unpractical if you have many
functions, especially if they are nested). I wonder if it might be good to
mention this in the docs about the different RETURNs? On the other hand
maybe it only affects a very limited amount of users..

Thanks for the input so far!
/Victor

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

2015-11-03 Thread Tom Lane 
Victor Blomqvist  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-11-02 Thread Victor Blomqvist 
On Mon, Oct 12, 2015 at 10:15 PM, Adrian Klaver 
wrote:

> On 10/12/2015 06:53 AM, Tom Lane wrote:
>
>> Andres Freund  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.kla...@aklaver.com
>


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.

1. Create table and function
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;

2. Create loop_alter.sql with this content
#!/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 loop_select.sql with this content
#!/usr/bin/env bash
for i in {0..10} do
echo "select * from select_a() limit 1;"
done;

4. Run the files from 2 and 3 simultaneous with psql:
In one terminal: ./loop_alter.sql | psql
In another: ./loop_select.sql | psql

(Note that you need to drop and recreate the table after each run since it
reaches the column limit otherwise)

/Victor

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

2015-10-13 Thread Adrian Klaver 

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  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
> wrote:

On 10/12/2015 06:53 AM, Tom Lane wrote:

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





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing

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

2015-10-12 Thread Albe Laurenz 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-12 Thread 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-12 Thread Adrian Klaver 

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/a737b7a37273e048b164557adef4a58b50fb7...@ntex2010i.host.magwien.gv.at


Greetings,

Andres Freund





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-12 Thread Andres Freund 
On 2015-10-12 08:07:54 +, 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(, relid);

res = LockAcquire(, 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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-12 Thread Andres Freund 
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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-12 Thread Tom Lane 
Andres Freund  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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-12 Thread Adrian Klaver 

On 10/12/2015 06:53 AM, Tom Lane wrote:

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-12 Thread Victor Blomqvist 
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  also without select * in the functions.

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 
wrote:

> On 10/12/2015 06:53 AM, Tom Lane wrote:
>
>> Andres Freund  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.kla...@aklaver.com
>

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

2015-10-11 Thread Victor Blomqvist 
On Sat, Oct 10, 2015 at 10:00 PM, Adrian Klaver 
wrote:

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

The same behaviour. (Actually its more annoying than when it happens with
DROPs since we do ADDs much more often)


>
>
>> 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
>> > 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.kla...@aklaver.com 
>>
>>
>>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>

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

2015-10-10 Thread Adrian Klaver 

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





--
Adrian Klaver
adrian.kla...@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-09 Thread Victor Blomqvist 
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 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-22fdcf369...@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

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

2015-10-09 Thread Victor Blomqvist 
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.

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

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

2015-10-09 Thread Bill Moran 
On Fri, 9 Oct 2015 14:32:44 +0800
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.

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-22fdcf369...@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


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-09 Thread Albe Laurenz 
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

-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-09 Thread Albe Laurenz 
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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-09 Thread Adrian Klaver 

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-09 Thread Adrian Klaver 

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

2015-10-09 Thread Adrian Klaver 

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


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general