Re: [GENERAL] Question about antijoin

Thanks Tom and David That's very useful. My interest for Andl is to be able to emit SQL that Postgres will reliably interpret as an anti-join, in the absence of an explicit form in SQL. But your reference to "anti-semijoin" is interesting -- what is that? Is it just another name for anti-join,

Re: [GENERAL] PostgreSQL FDW + Trigger on Remote DB = WARNING: there is no transaction in progress

Peter Olivier writes: > I create a foreign table F1 on Database local which points to Remote.R1 > When updating F1 the trigger on Remote.R1 fires but gives the following > warning: > WARNING: there is no transaction in progress Seems odd, but I think you'll have to

Re: [GENERAL] PostgreSQL FDW + Trigger on Remote DB = WARNING: there is no transaction in progress

On 07/12/2016 02:39 AM, Peter Olivier wrote: Hi, I have the following setup: Database Local has a table L1 Database Remote has a table R1 and a table R2. Table Remote.R1 has a trigger. This trigger updates Remote.R2 I create a foreign table F1 on Database local which points to

Re: [GENERAL] Upsert with a partial unique index constraint violation

Correct, there was no typo there. All of the psql examples I included were copy-pasted out of a clean psql 9.5 session on a clean psql 9.5 database (64 bit linux). $ createdb tmp $ psql --quiet tmp tmp=# select version(); version

[GENERAL] PostgreSQL FDW + Trigger on Remote DB = WARNING: there is no transaction in progress

Hi, I have the following setup: Database Local has a table L1 Database Remote has a table R1 and a table R2. Table Remote.R1 has a trigger. This trigger updates Remote.R2 I create a foreign table F1 on Database local which points to Remote.R1 When updating F1 the trigger on Remote.R1

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > This because I have the impression that it is during index creation, > where I think client role would be minimal. Hard to believe really, given the spelling of the message. But anyway, be sure you do the run with log_statement = all so

Re: [GENERAL] pg_restore out of memory

A Ter, 12-07-2016 às 11:58 -0400, Tom Lane escreveu: >  > Anyway, it would be useful to try running the restore with a more > modern > version of pg_restore, to see if that helps. > > regards, tom lane > > I have the scheduled restart tonight. So, I will do the other test

[GENERAL] using CRL(s) with multiple CA(s) issued by different root CA(s)

Hi all, I am unsure of how CRL works in PostgreSQL 9.3. I have a setup with multiple CA's issued by different root CA's that each may or may not issue a CRL. I am aware that in the postgresql.conf there is ssl_cert_file, and ssl_key_file which contains the unique (server) certificate and key.

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > Às 15:40 de 12-07-2016, Tom Lane escreveu: >> Unless you're running pg_restore under a really small ulimit, this would >> seem to suggest some kind of memory leak in pg_restore itself. I wonder >> how many objects in your dump (how long is

Re: [GENERAL] pg_restore out of memory

Às 16:23 de 12-07-2016, Miguel Ramos escreveu: It looks to me like this error is pg_restore itself running out of memory, not reporting a server-side OOM condition. You could verify that by looking in the server log to see whether any out-of-memory error appeared there. But assuming that I'm

Re: [GENERAL] pg_restore out of memory

Às 15:40 de 12-07-2016, Tom Lane escreveu: Miguel Ramos writes: We have backed up a database and now when trying to restore it to the same server we get this: # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump pg_restore: [custom archiver]

Re: [GENERAL] Upsert with a partial unique index constraint violation

Peter Geoghegan writes: > On Mon, Jul 11, 2016 at 12:06 AM, Tim Dawborn wrote: >> tmp=# INSERT INTO foo (a, b, c, d) VALUES (1, 2, 'four', true) >> tmp-# ON CONFLICT (a, b) WHERE d = true >> tmp-# DO UPDATE SET c = 'four' WHERE foo.a = 1 AND foo.b = 2 AND

Re: [GENERAL] pg_restore out of memory

Miguel Ramos writes: > We have backed up a database and now when trying to restore it to the > same server we get this: >>> # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump >>> pg_restore: [custom archiver] out of memory It looks to me like

Re: [GENERAL] Replication with non-read-only standby.

Thanks. I ended up using pglogical, since I don't really need Bi-directional replication and docs for UDR suggest using pglogical instead. Although I ran into a problem there, but pglogical seems to be the answer. Regards, Nick. - Original Message - From: "Sylvain Marechal"

[GENERAL] pglogical cascading replication (chaining replication)

I apologize if this is wrong place to ask the question. A quote from pglogical FAQ: > Q. Does pglogical support cascaded replication? > Subscribers can be configured as publishers as well thus cascaded replication > can be achieved > by forwarding/chaining (again no failover though). The only

Re: [GENERAL] Question about antijoin

"dandl" writes: > This got my interest! It's of great interest to me to know how and when > Postgres performs an anti-join (this being a significant omission from SQL). > Is this a reliable trigger: (NOT EXISTS )? That's one case; see convert_EXISTS_sublink_to_join() for the

Re: [GENERAL] Slow SQL?

=?UTF-8?B?QmrDuHJu?= T Johansen writes: > But when I try to run the 3 queries separately, then they are very quick, 2 > barely measurable and the third takes about 1,5 seconds. The union query > takes a little over 9 seconds, so I guess the union part is the bottleneck? No;

Re: [GENERAL] pg_restore out of memory

On Tue, 12 Jul 2016, 7:25 p.m. Miguel Ramos, < org.postgre...@miguel.ramos.name> wrote: > > Hi, > > We have backed up a database and now when trying to restore it to the > same server we get this: > > > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > > pg_restore: [custom

Re: [GENERAL] Slow SQL?

Bjørn T Johansen wrote: > Thx for your suggestions. Tried to use NOT EXISTS and the query was about > half a second quicker so not > much difference... > But when I try to run the 3 queries separately, then they are very quick, 2 > barely measurable and the > third takes about 1,5 seconds. The

Re: [GENERAL] Slow SQL?

On Tue, Jul 12, 2016 at 10:23:24AM +0200, Bjørn T Johansen wrote: > I am trying to move a small system from Oracle to PostgreSQL and > I have come upon a sql that runs really slow compared to on the Oracle > database and I am not able to interpret why this is slow. I loaded your explain analyze

Re: [GENERAL] pg_restore out of memory

2016-07-12 8:54 GMT-03:00 Miguel Ramos : > > Às 12:32 de 12-07-2016, Felipe Santos escreveu: > >> I would try lowering max_connections to 50 and then set work_mem to 128MB. >> >> After that restart your server and retry the restore. >> > > Ok, I will try

Re: [GENERAL] pg_restore out of memory

Às 12:32 de 12-07-2016, Felipe Santos escreveu: I would try lowering max_connections to 50 and then set work_mem to 128MB. After that restart your server and retry the restore. Ok, I will try restarting tonight. work_mem is the parameter I was most afraid of. I'll post some news in 24h...

Re: [GENERAL] Question about antijoin

On 12 July 2016 at 12:41, dandl wrote: >>NOT EXISTS (SELECT NULL FROM dyr_pause_mot WHERE avlsnr = a.avlsnr) >> >> This can be executed as anti-join and is often more efficient. > > This got my interest! It's of great interest to me to know how and when > Postgres performs an

Re: [GENERAL] pg_restore out of memory

2016-07-12 8:25 GMT-03:00 Miguel Ramos : > > Hi, > > We have backed up a database and now when trying to restore it to the same > server we get this: > > > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > > pg_restore: [custom archiver] out of

[GENERAL] pg_restore out of memory

Hi, We have backed up a database and now when trying to restore it to the same server we get this: > # pg_restore -d recovery /mnt/paysdeloire2013_convertida2.1.dump > pg_restore: [custom archiver] out of memory > 12:09:56.58 9446.593u+1218.508s 24.3% 167+2589k 6+0io 0pf+0sw 6968822cs

Re: [GENERAL] Slow SQL?

On Tue, 12 Jul 2016 09:12:23 + Albe Laurenz wrote: > haman...@t-online.de wrote: > Bjørn T Johansen wrote: > >> I am trying to move a small system from Oracle to PostgreSQL and I have > >> come upon a sql that runs > >> really slow compared to on the Oracle database

[GENERAL] Question about antijoin

>NOT EXISTS (SELECT NULL FROM dyr_pause_mot WHERE avlsnr = a.avlsnr) > > This can be executed as anti-join and is often more efficient. This got my interest! It's of great interest to me to know how and when Postgres performs an anti-join (this being a significant omission from SQL). Is

Re: [GENERAL] Slow SQL?

haman...@t-online.de wrote: Bjørn T Johansen wrote: >> I am trying to move a small system from Oracle to PostgreSQL and I have come >> upon a sql that runs >> really slow compared to on the Oracle database and I am not able to >> interpret why this is slow. > I have experienced that some

Re: [GENERAL] Slow SQL?

Hi Bjorn, I have experienced that some subqueries can be quite slow, and would suspect the NOT IN clause. I occasionally rewrite NOT IN (select key from unwanted_candidates) as IN (select key from possible_candidates except select key from unwanted_candidates) Admittedly, I am not running

[GENERAL] Slow SQL?

I am trying to move a small system from Oracle to PostgreSQL and I have come upon a sql that runs really slow compared to on the Oracle database and I am not able to interpret why this is slow. The SQL looks like this: Select a.status, a.plass, a.navn, a.avlsnr,

[GENERAL] BDR - Ignore already exists error during DDL replay

Hello, In my 2-node BDR setup if I make changes in db schema I am seeing below error or after few reboots I get into below inconsistent state during DDL replay. Is there any way to ignore ItemAlreadyExists error during DDL replay ? global lock of DDL replication is switched off in configuration.