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

[Torsten Förtsch]

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 that is called to validate the constraint, RI_Initial_Check,
contains this comment:

 * We expect that the caller has made provision to prevent any problems
 * caused by concurrent actions. This could be either by locking rel and
 * pkrel at ShareRowExclusiveLock or higher, or by otherwise ensuring
 * that triggers implementing the checks are already active.
 * Hence, we do not need to lock individual rows for the check.

Doesn't the presence of the NOT VALID constraint qualify as "otherwise
ensuring that triggers implementing the checks are already active"?

Is there any deeper reason? Or is it simply not implemented yet?

Thanks,
Torsten


-- 
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] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?

[Vik Fearing]

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 prevents new modifications from violating the constraint.
>
> The code that is called to validate the constraint, RI_Initial_Check,
> contains this comment:
>
>  * We expect that the caller has made provision to prevent any problems
>  * caused by concurrent actions. This could be either by locking rel and
>  * pkrel at ShareRowExclusiveLock or higher, or by otherwise ensuring
>  * that triggers implementing the checks are already active.
>  * Hence, we do not need to lock individual rows for the check.
>
> Doesn't the presence of the NOT VALID constraint qualify as "otherwise
> ensuring that triggers implementing the checks are already active"?
>
> Is there any deeper reason? Or is it simply not implemented yet?
>


Actually, it is implemented yet.

http://www.postgresql.org/message-id/e1wwovd-0004ts...@gemulon.postgresql.org

It'll be in 9.4.

-- 
Vik



-- 
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] Why do we need an AccessExclusiveLock to validate a FK constraint marked as NOT VALID?

[Torsten Förtsch]

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(
SELECT 1
  FROM ONLY fkrel a
  LEFT JOIN ONLY pkrel b
ON (a.fkcol1=b.pkcol1 AND ...)   -- all fk columns
 WHERE b.pkcol1 IS NULL  -- inner join failed
   AND (a.fkcol1 IS NOT NULL
OR/AND   -- MATCH SIMPLE: AND; FULL: OR
a.fkcol2 IS NOT NUL
...)
   )
 WHERE contype='f'
   AND ...

fkrel is confrelid::regclass and pkrel conrelid::regclass.

That's essentially what AT VALIDATE CONSTRAINT does.

Torsten


-- 
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] Database Design: Maintain Audit Trail of Changes

[Moshe Jacobson]

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 looking for.
>>
>

> Do you plan to support primary keys other than single column integers?
>

I may modify it to support bigints, but I don't have any plans to support
multi-column primary keys. It would require too much overhaul of the code.

Moshe Jacobson
Manager of Systems Engineering, Nead Werx Inc. 
2323 Cumberland Parkway · Suite 201 · Atlanta, GA 30339

"Quality is not an act, it is a habit." -- Aristotle

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

[Paul Jones]

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 reproduce.  The second insert at the end should fail 
because the two circles
have the same radius.

CREATE OR REPLACE FUNCTION circradcmp(aa CIRCLE, bb CIRCLE)
RETURNS BOOLEAN AS $$
DECLARE zz DOUBLE PRECISION;
BEGIN
    zz := abs(radius(aa) - radius(bb));
    IF (zz < 0.0005)
    THEN
    RETURN TRUE;
    ELSE
    RETURN FALSE;
    END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OPERATOR === (
    LEFTARG = CIRCLE,
    RIGHTARG = CIRCLE,
    PROCEDURE = circradcmp,
    COMMUTATOR = ===
);

ALTER OPERATOR FAMILY circle_ops USING gist ADD
    OPERATOR 15 === (circle, circle);

CREATE TABLE punky
(
    acirc   CIRCLE,
    EXCLUDE USING GIST (acirc circle_ops WITH ===)
);  

INSERT INTO punky VALUES ('(0,0),3)');
INSERT INTO punky VALUES ('(7,0),3)');

Paul Jones


-- 
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] User defined operator fails to work in EXCLUDE constraint

[Tom Lane]

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

This:

> ALTER OPERATOR FAMILY circle_ops USING gist ADD
>     OPERATOR 15 === (circle, circle);

You can't just add a new operator to a GIST opclass and have it work with
no coding, because what makes it work is teaching the opclass'
consistent() function about it.

What I'd have expected to happen when you did this was bleating about
an unrecognized operator strategy number.  The reason you didn't get that
was that rtree_internal_consistent doesn't throw an error in the default:
case in its switch, which seems pretty stupid now that I look at it.

In this particular application, circle_ops couldn't really help you even
if you were prepared to go and change the C code, because what it stores
in the index is bounding boxes for the circles.  I can't see any way for
bounding-box comparisons to exclude subtrees of the index when the query
is about whether the radii match; so you'd not be able to do better than
a full index scan, which will not be faster than a full table scan.

You could probably solve your problem with a different index
representation.  A brute-force way would be to make an expression index
on the range [radius(aa), radius(aa) + 0.005] and then look for
overlaps of those ranges.  There might be a better answer.

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

[GENERAL] streaming replication + wal shipping

[Anupama Ramaswamy]

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 the standby to take over as master. So I would 
like minimal data loss, if there is a streaming replication delay.

Is it possible to setup such a way that under normal conditions the standby by 
replicating using streaming replication and on failover, it uses the WAL 
archive for syncing up with the transactions. Of course the WAL will be 
available on a shared storage volume. If this is possible, what exactly do I 
need in my configuration files - postgresql.conf, recovery.conf ?

Thanks for your help
Anupama.

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

[Paul Jones]

- 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 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?
> 
> This:
> 
>>  ALTER OPERATOR FAMILY circle_ops USING gist ADD
>>      OPERATOR 15 === (circle, circle);
> 
> You can't just add a new operator to a GIST opclass and have it work with
> no coding, because what makes it work is teaching the opclass'
> consistent() function about it.
> 
> What I'd have expected to happen when you did this was bleating about
> an unrecognized operator strategy number.  The reason you didn't get that
> was that rtree_internal_consistent doesn't throw an error in the default:
> case in its switch, which seems pretty stupid now that I look at it.
> 
> In this particular application, circle_ops couldn't really help you even
> if you were prepared to go and change the C code, because what it stores
> in the index is bounding boxes for the circles.  I can't see any way for
> bounding-box comparisons to exclude subtrees of the index when the query
> is about whether the radii match; so you'd not be able to do better than
> a full index scan, which will not be faster than a full table scan.
> 
> You could probably solve your problem with a different index
> representation.  A brute-force way would be to make an expression index
> on the range [radius(aa), radius(aa) + 0.005] and then look for
> overlaps of those ranges.  There might be a better answer.
> 
>             regards, tom lane
> 

I was afraid it was something like this.  I see that I was way in over my head
on this one and I was mislead because it didn't complain about anything.

I do appreciate the lesson.

PJ

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


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

[GENERAL] restore_command ignored in recovery.conf on standby

[Rene Romero Benavides]

- 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-13 21:07:19
CDT,1/0,0,LOG,0,"consistent recovery state reached at
1E6/F9FFE880""CheckRecoveryConsistency, xlog.c:7371",""
2014-04-13 21:07:21.387 CDT,,,22053,,534b42d6.5625,1,,2014-04-13 21:07:18
CDT,,0,LOG,0,"database system is ready to accept read only
connections""sigusr1_handler, postmaster.c:4261",""

# recovery.conf

standby_mode=on
restore_command='/bin/tar -xzf /db/wal_archives/%f.tar.gz -C %p'

where the /db/wal_archives/ looks like this:
000101ED00F7.tar.gz
000101ED00F8.tar.gz
000101ED00F9.tar.gz

as you can see, the time line is far ahead from where the standby claims to
have reached a consistent recovery state

I tested the restore_command replacing variables and it works. Any ideas on
why it isn't being executed?


-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

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

[Stephen Frost]

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 immediately returning
'1' (meaning 'false'- aka, done with recovery)?

The -C option to tar is supposed to be "change directory" according to
the tar that I've got, and %p is the complete file name that PG wants
the WAL file to be copied to- it's not a directory (it's something like
pg_xlog/RECOVERY_WAL).

Thanks,

Stephen


signature.asc
Description: Digital signature

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

[Rene Romero Benavides]

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

I read somewhere that in order for the extracted file to be placed at a
custom location you had to use that option -C

I'll try rewriting the command and debug it. Thanks for your comment.



2014-04-13 21:39 GMT-05:00 Stephen Frost :

> 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 immediately returning
> '1' (meaning 'false'- aka, done with recovery)?
>
> The -C option to tar is supposed to be "change directory" according to
> the tar that I've got, and %p is the complete file name that PG wants
> the WAL file to be copied to- it's not a directory (it's something like
> pg_xlog/RECOVERY_WAL).
>
> Thanks,
>
> Stephen
>



-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

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

[Stephen Frost]

* 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 being passed via %p.  %p will
be something like 'pg_xlog/RECOVERY_WAL', as I said, which *won't* work
for your tar command, eg:

sfrost@tamriel:/home/sfrost> tar -xzf zz.tar.gz -C zz/zz
tar: zz/zz: Cannot open: No such file or directory
tar: Error is not recoverable: exiting now
sfrost@tamriel:/home/sfrost> echo $?
2

> [postgres@uxmal standby_node]$ ls /db/standby_node/pg_xlog/ | grep
> 000101ED00FB
> 000101ED00FB

N, PG tells you via %p the *specific* filename to use, do not just
overwrite files in pg_xlog willy-nilly with a tar command.

Thanks,

Stephen


signature.asc
Description: Digital signature

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

[Rene Romero Benavides]

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 :

> * 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 being passed via %p.  %p will
> be something like 'pg_xlog/RECOVERY_WAL', as I said, which *won't* work
> for your tar command, eg:
>
> sfrost@tamriel:/home/sfrost> tar -xzf zz.tar.gz -C zz/zz
> tar: zz/zz: Cannot open: No such file or directory
> tar: Error is not recoverable: exiting now
> sfrost@tamriel:/home/sfrost> echo $?
> 2
>
> > [postgres@uxmal standby_node]$ ls /db/standby_node/pg_xlog/ | grep
> > 000101ED00FB
> > 000101ED00FB
>
> N, PG tells you via %p the *specific* filename to use, do not just
> overwrite files in pg_xlog willy-nilly with a tar command.
>
> Thanks,
>
> Stephen
>



-- 
El genio es 1% inspiración y 99% transpiración.
Thomas Alva Edison
http://pglearn.blogspot.mx/

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

[Stephen Frost]

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

You'll probably want to be more careful here- this script could exit
with 'success' (meaning zero) even if some of the above commands fail.
When writing reliable shell scripts, you really need to check the exit
status of each command.  Note that you can return a high-value (>128,
iirc) from your shell script to indicate 'permanent' failure while
trying to do WAL recovery and PG will give up and stop trying.

Is there any particular reason you're tar'ing up the WAL files in the
first place..?  It'd surely be easier if you simply gzip'd them and then
used something like 'zcat /path/to/wal/archive/%f.gz > %p'.

The other option, if you really want to keep them tar'd, would be to use
tar's -O option, eg:

tar -O -zxf /db/wal_archives/%f.tar.gz %f > %p

There is also a --transform option that you could pass to tar to change
the filenames.

> My best regards to Stephen Frost.

Thanks!

Stephen


signature.asc
Description: Digital signature

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

[Jeff Janes]

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 message:
>
> 2014-04-13 21:07:21.386 CDT,,,22055,,534b42d7.5627,4,,2014-04-13 21:07:19
CDT,1/0,0,LOG,0,"consistent recovery state reached at
1E6/F9FFE880""CheckRecoveryConsistency, xlog.c:7371",""
> 2014-04-13 21:07:21.387 CDT,,,22053,,534b42d6.5625,1,,2014-04-13 21:07:18
CDT,,0,LOG,0,"database system is ready to accept read only
connections""sigusr1_handler, postmaster.c:4261",""
>

Are you sure there is actually a problem? "Ready to accept read-only
connections" doesn't mean recovery has ended.

Cheers,

Jeff

Re: [GENERAL] streaming replication and recovery

[Michael Paquier]

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 operation
when pg_ctl promote is run or a trigger file is found (trigger_file).
Before failover, any WAL immediately available in the archive or in
pg_xlog will be restored, but no attempt is made to connect to the
master."

> a) Will the replay complete before the standby stops replicating (because it
> sees the trigger file) ?
Any WAL available in the archives or pg_xlog will be replayed before
the failover.

> b) If I want to run this as new master and attach other secondaries to point
> to this master, can I do it soon after "touch /tmp/pgsql.failover" OR should
> I wait till the secondary has finished replaying all the delivered stream ?
You need to wait until all the WAL has been replayed, which is the
point where failover occurs.

> c) How do I know if the replay is over and it is ready for a standalone
> operation ?
"SELECT pg_is_in_recovery();" returns true if server is still
performing recovery operations.
-- 
Michael


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