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

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

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

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

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

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

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

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

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

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

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

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

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

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,

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Re: [GENERAL] 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 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

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

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

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

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

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

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

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

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

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

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?