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

Re: [GENERAL] Best practices for aggregate table design

2015-10-09 Thread John R Pierce
On 10/9/2015 6:07 PM, Roxanne Reid-Bennett wrote: On 10/9/2015 3:16 PM, droberts wrote: Thanks for everyone's help. Can anyone recommend a good book on database modeling, around these 'cube' or aggregate concepts in particular? I'm using Postgres but shouldn't matter too much I assume. Given

Re: [GENERAL] Best practices for aggregate table design

2015-10-09 Thread Roxanne Reid-Bennett
On 10/9/2015 3:16 PM, droberts wrote: Thanks for everyone's help. Can anyone recommend a good book on database modeling, around these 'cube' or aggregate concepts in particular? I'm using Postgres but shouldn't matter too much I assume. Given the shift towards NoSQL for BI, and the age of the

Re: [GENERAL] Best practices for aggregate table design

2015-10-09 Thread droberts
David G Johnston wrote > Nabble has a "quote" feature - please use it. > > On Thu, Oct 8, 2015 at 5:00 PM, droberts < > david.roberts@ > > wrote: > >> I haven't but wouldn't it be better to wait and just add new columns >> if/when >> I need to? >> > > ​Its worth keeping in the back of your min

Re: [GENERAL] Merge join vs merge semi join against primary key

2015-10-09 Thread Sean Rhea
It does the merge (not-semi) join: production=> explain analyze SELECT ac.* FROM balances ac JOIN customers o ON (o.id = ac.customer_id AND o.group_id = 45); QUERY PLAN --

[GENERAL] Merge join vs merge semi join against primary key

2015-10-09 Thread Sean Rhea
Hello, I'm seeing some inexplicable (to me) behavior in PostgreSQL 9.2. I checked the archives, but I still can't explain it. Apologies if I missed something. 1. When I join two tables with "WHERE id IN (...)" versus with an explicit join, and the join column for the inner table is a primary key,

Re: [GENERAL] BDR: no free replication state could be found

2015-10-09 Thread Selim Tuvi
node: deliver_sing (the problem node): postgres=# SELECT * FROM pg_catalog.pg_replication_identifier; riident | riname -+ 1 | bdr_6197393155020108291_1_47458_16385_ 2 | bdr_6199712740068695651_1_16385_16385_ 3 |

Re: [GENERAL] Best way to sync table DML between databases

2015-10-09 Thread Francisco Reyes
On 10/05/2015 09:46 AM, jimbosworth wrote: Im not in a position to change the database setup on server A. Can you have the owners/maintainers do the needed changes to setup replication? Or that is 100% out of the question? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

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

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

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 foll

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

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/

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.

Re: [GENERAL] Version management for extensions

2015-10-09 Thread Albe Laurenz
Jeff Janes wrote: > I am facing a scenario where I have different version of an extension, say > 1.0 and 2.0, which have > some different functionality between them (so not merely a bug fix), so > people might want to continue > to use 1.0. > > But changes to the PostgreSQL software between majo

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

2015-10-09 Thread Oleksii Kliukin
> On 08 Oct 2015, at 19:54, Alvaro Herrera wrote: > > Oleksii Kliukin wrote: > >> Thank you, now it’s clear. I have to say there is no guarantee that >> the computation would be useless. Someone might be calling a function >> that updates/deletes rows in the SELECT INTO block, being forced to >

Re: [GENERAL] dubious optimization of the function in SELECT INTO target list

2015-10-09 Thread Oleksii Kliukin
> On 08 Oct 2015, at 16:00, Adrian Klaver wrote: > > On 10/08/2015 01:57 AM, Oleksii Kliukin wrote: >> >>> On 06 Oct 2015, at 23:31, Tom Lane >> >>> >> wrote: >>> >>> Oleksii Kliukin mailto:al...@hintbits.com> >