[GENERAL] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?

Hi, currently, ALTER TABLE VALIDATE CONSTRAINT for foreign key constraints acquires an AccessExclusiveLock on the referencing table. Why? If the constraint is in place but not validated (ADD CONSTRAINT ... NOT VALID) it already prevents new modifications from violating the constraint. The code

Re: [GENERAL] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?

On 04/13/2014 12:58 PM, Torsten Förtsch wrote: > Hi, > > currently, ALTER TABLE VALIDATE CONSTRAINT for foreign key constraints > acquires an AccessExclusiveLock on the referencing table. > > Why? > > If the constraint is in place but not validated (ADD CONSTRAINT ... NOT > VALID) it already preven

Re: [GENERAL] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?

On 13/04/14 13:34, Vik Fearing wrote: > Actually, it is implemented yet. > > http://www.postgresql.org/message-id/e1wwovd-0004ts...@gemulon.postgresql.org > > It'll be in 9.4. That's good news. So, I could validate a FK constraint this way: UPDATE pg_constraint SET convalidated = NOT EXISTS

Re: [GENERAL] Database Design: Maintain Audit Trail of Changes

On Sat, Apr 12, 2014 at 2:57 AM, Tony Theodore wrote: > > I know this is a terribly old thread, but if you are still looking for >> software to provide an audit trail of changes in the database, please see >> Cyan Audit at http://pgxn.org/dist/cyanaudit. I think it will do just >> what you're loo

[GENERAL] User defined operator fails to work in EXCLUDE constraint

I tried to define my own circle operator to use in an EXCLUDE constraint but it fails to detect insertion of rows that should not be simultaneously be allowed in the table.  The operator compares two circles' radii and works for a simple SELECT.  What am I doing wrong? Here is the code to repro

Re: [GENERAL] User defined operator fails to work in EXCLUDE constraint

Paul Jones writes: > I tried to define my own circle operator to use in an EXCLUDE constraint but > it fails to detect > insertion of rows that should not be simultaneously be allowed in the table.  > The operator > compares two circles' radii and works for a simple SELECT.  What am I doing > w

[GENERAL] streaming replication + wal shipping

Hi All, I would like to setup a 2 servers with streaming replication, one master and another hot standby. I want to use the standby for read-only queries. So I want the replication lag to be as small as possible. So I choose streaming replication over WAL shipping. When the master fails, I want

Re: [GENERAL] User defined operator fails to work in EXCLUDE constraint

- Original Message - > From: Tom Lane > To: Paul Jones > Cc: "pgsql-general@postgresql.org" > Sent: Sunday, April 13, 2014 4:25 PM > Subject: Re: [GENERAL] User defined operator fails to work in EXCLUDE > constraint > > Paul Jones writes: >> I tried to define my own circle operat

[GENERAL] restore_command ignored in recovery.conf on standby

- Base backup taken with 9.2.6 (via pg_basebackup command) - binaries updated to 9.2.8 - set up the base backup to replicate from the master and archives, and started - the restore_command option is ignored, with the following message: 2014-04-13 21:07:21.386 CDT,,,22055,,534b42d7.5627,4,,2014-04-

Re: [GENERAL] restore_command ignored in recovery.conf on standby

Rene, * Rene Romero Benavides (rene.romer...@gmail.com) wrote: > restore_command='/bin/tar -xzf /db/wal_archives/%f.tar.gz -C %p' [...] > I tested the restore_command replacing variables and it works. Any ideas on > why it isn't being executed? Are you sure that it isn't being executed and just i

Re: [GENERAL] restore_command ignored in recovery.conf on standby

Yep, I checked: [postgres@uxmal standby_node]$ /bin/tar -xzf /db/wal_archives/000101ED00FB.tar.gz -C /db/standby_node/pg_xlog/ [postgres@uxmal standby_node]$ echo $? 0 [postgres@uxmal standby_node]$ ls /db/standby_node/pg_xlog/ | grep 000101ED00FB 000101ED00FB

Re: [GENERAL] restore_command ignored in recovery.conf on standby

* Rene Romero Benavides (rene.romer...@gmail.com) wrote: > Yep, I checked: > > [postgres@uxmal standby_node]$ /bin/tar -xzf > /db/wal_archives/000101ED00FB.tar.gz -C > /db/standby_node/pg_xlog/ > [postgres@uxmal standby_node]$ echo $? > 0 Err, sure, but that isn't actually what is bei

Re: [GENERAL] restore_command ignored in recovery.conf on standby

What I did (I bet there's a better way) is this: restore_command='/db/standby_node/scripts/wal_restore.sh %f %p' # wal_restore.sh #!/bin/bash /bin/tar -xzf /db/wal_archives/$1.tar.gz -C /tmp cp /tmp/$1 $2 rm /tmp/$1 My best regards to Stephen Frost. 2014-04-13 21:58 GMT-05:00 Stephen Frost :

Re: [GENERAL] restore_command ignored in recovery.conf on standby

Rene, * Rene Romero Benavides (rene.romer...@gmail.com) wrote: > What I did (I bet there's a better way) is this: > restore_command='/db/standby_node/scripts/wal_restore.sh %f %p' > > # wal_restore.sh > #!/bin/bash > /bin/tar -xzf /db/wal_archives/$1.tar.gz -C /tmp > cp /tmp/$1 $2 > rm /tmp/$1 Y

Re: [GENERAL] restore_command ignored in recovery.conf on standby

On Apr 13, 2014 7:30 PM, "Rene Romero Benavides" wrote: > > - Base backup taken with 9.2.6 (via pg_basebackup command) > - binaries updated to 9.2.8 > - set up the base backup to replicate from the master and archives, and started > - the restore_command option is ignored, with the following messa

Re: [GENERAL] streaming replication and recovery

On Sat, Apr 12, 2014 at 3:12 PM, Anupama Ramaswamy wrote: > Lets suppose at this point there is 0 delivery lag but bytes of replay > lag. > All your answers are here: http://www.postgresql.org/docs/devel/static/warm-standby.html "Standby mode is exited and the server switches to normal operat