change in LOCK behavior

On Wed, Oct 10, 2012 at 10:21:51PM +0200, Tomas Vondra wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and 9.2,
and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it sees
only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

regards
Tomas

Hi Tomas,

9.2 is doing it right. Per the documentation on explicit locking:

http://www.postgresql.org/docs/9.2/static/explicit-locking.html

Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

Regards,
Ken

On 10.10.2012 22:37, ktm@rice.edu wrote:

On Wed, Oct 10, 2012 at 10:21:51PM +0200, Tomas Vondra wrote:

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

regards
Tomas

Hi Tomas,

9.2 is doing it right. Per the documentation on explicit locking:

http://www.postgresql.org/docs/9.2/static/explicit-locking.html

Tip: Only an ACCESS EXCLUSIVE lock blocks a SELECT (without FOR UPDATE/SHARE) statement.

That is not the problem. We do expect it to block (that's why we do this
kind of lock in the first place), and that does happen both on 9.1 and 9.2.

The difference is that 9.1 does see the changes performed in the other
session (that held the lock and released it on commit), while 9.2 does not.

Tomas

On Wednesday, October 10, 2012 10:21:51 PM Tomas Vondra wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and 9.2,
and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it sees
only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

That sounds like youre using different isolation levels in 9.1 and 9.2. Is that
possible? I.e. your 9.1 test uses read committed, and 9.2 uses repeatable read
or serializable.

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and 9.2,
and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it sees
only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da

http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php
--
Thom

On 10.10.2012 22:42, Andres Freund wrote:

On Wednesday, October 10, 2012 10:21:51 PM Tomas Vondra wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and 9.2,
and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it sees
only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

That sounds like youre using different isolation levels in 9.1 and 9.2. Is that
possible? I.e. your 9.1 test uses read committed, and 9.2 uses repeatable read
or serializable.

Nope, it's 'read commited' on both. I haven't touched this, but I've
verified it to be sure.

============ 9.1 ============

$ psql testdb
psql (9.1.6)
Type "help" for help.

testdb=# show server_version;
server_version
----------------
9.1.6
(1 row)

testdb=# show transaction_isolation ;
transaction_isolation
-----------------------
read committed
(1 row)

============ 9.2 ============

$ psql testdb
psql (9.2.0)
Type "help" for help.

testdb=# show server_version;
server_version
----------------
9.2.0
(1 row)

testdb=# show transaction_isolation
testdb-# ;
transaction_isolation
-----------------------
read committed
(1 row)

On 10.10.2012 22:43, Thom Brown wrote:

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da

http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php

Maybe, the description suggests it might be related. I'm still not sure
whether this is a bug or expected behavior, although the commit clearly
states that the change shouldn't be user-visible.

Tomas

On Wednesday, October 10, 2012 10:43:57 PM Thom Brown wrote:

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and 9.2,
and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it sees
only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da

http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php

Very likely, yes. In fact you get the same beaviour in 9.1 if you modify the
example slightly:

B: PREPARE foo AS SELECT * FROM x;
A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: EXECUTE foo;
A: COMMIT;

If you think about it for a second its not that surprising anymore. We start to
execute a query, acquire a snapshot for that, and then wait for the locks on
the target relations. We continue executing in the same snapshot for the
duration of the statement and thus cannot see any of the new rows which
committed *after* we assembled our snapshot.

The easy workaround is acquiring a AccessShareLock in the B transaction
separately.

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 10.10.2012 23:05, Andres Freund wrote:

On Wednesday, October 10, 2012 10:43:57 PM Thom Brown wrote:

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and 9.2,
and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it sees
only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da

http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php

Very likely, yes. In fact you get the same beaviour in 9.1 if you modify the
example slightly:

B: PREPARE foo AS SELECT * FROM x;
A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: EXECUTE foo;
A: COMMIT;

If you think about it for a second its not that surprising anymore. We start to
execute a query, acquire a snapshot for that, and then wait for the locks on
the target relations. We continue executing in the same snapshot for the
duration of the statement and thus cannot see any of the new rows which
committed *after* we assembled our snapshot.

Yes, that was my guess too (that the snapshot is acquired before asking
for the lock and not re-acquired after getting the lock).

The easy workaround is acquiring a AccessShareLock in the B transaction
separately.

I know - I've mentioned explicit locking as a possible solution in my
first message, although it would make the whole process more complex.

The question is whether that should be necessary or whether the 9.2
should behave the same as 9.1.

Tomas

On Wednesday, October 10, 2012 11:23:10 PM Tomas Vondra wrote:

On 10.10.2012 23:05, Andres Freund wrote:

On Wednesday, October 10, 2012 10:43:57 PM Thom Brown wrote:

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and
9.2, and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it
sees only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da

http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php

Very likely, yes. In fact you get the same beaviour in 9.1 if you modify
the example slightly:

B: PREPARE foo AS SELECT * FROM x;
A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: EXECUTE foo;
A: COMMIT;

If you think about it for a second its not that surprising anymore. We
start to execute a query, acquire a snapshot for that, and then wait for
the locks on the target relations. We continue executing in the same
snapshot for the duration of the statement and thus cannot see any of
the new rows which committed *after* we assembled our snapshot.

Yes, that was my guess too (that the snapshot is acquired before asking
for the lock and not re-acquired after getting the lock).

The easy workaround is acquiring a AccessShareLock in the B transaction
separately.

I know - I've mentioned explicit locking as a possible solution in my
first message, although it would make the whole process more complex.

I read your original statement as if you would want to use a separate lock
(advisory?) which you don't need.

The question is whether that should be necessary or whether the 9.2
should behave the same as 9.1.

Given that 9.1 behaves the same as 9.2 with prepared statements I don't really
see a convincing argument for changing this from the status quo.

You can hit the same/similar behaviour in 9.1 even if youre not using PREPARE
although the window isn't too big and you need DML + only an EXCLUSIVE (not
access exlusive) lock for it.

Greetings,

Andres

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 10.10.2012 23:31, Andres Freund wrote:

On Wednesday, October 10, 2012 11:23:10 PM Tomas Vondra wrote:

On 10.10.2012 23:05, Andres Freund wrote:

On Wednesday, October 10, 2012 10:43:57 PM Thom Brown wrote:

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and
9.2, and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some operations
on "x" and needs to protect them with an "ACCESS EXCLUSIVE" lock (e.g.
it might be a pg_bulkload that acquires such locks, and we need to do
that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits. On
9.1 it sees the commited data (which is what we need) but on 9.2 it
sees only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da

http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php

Very likely, yes. In fact you get the same beaviour in 9.1 if you modify
the example slightly:

B: PREPARE foo AS SELECT * FROM x;
A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: EXECUTE foo;
A: COMMIT;

If you think about it for a second its not that surprising anymore. We
start to execute a query, acquire a snapshot for that, and then wait for
the locks on the target relations. We continue executing in the same
snapshot for the duration of the statement and thus cannot see any of
the new rows which committed *after* we assembled our snapshot.

Yes, that was my guess too (that the snapshot is acquired before asking
for the lock and not re-acquired after getting the lock).

The easy workaround is acquiring a AccessShareLock in the B transaction
separately.

I know - I've mentioned explicit locking as a possible solution in my
first message, although it would make the whole process more complex.

I read your original statement as if you would want to use a separate lock
(advisory?) which you don't need.

Oh yeah, right. Any lock would work - advisory or not.

The question is whether that should be necessary or whether the 9.2
should behave the same as 9.1.

Given that 9.1 behaves the same as 9.2 with prepared statements I don't really
see a convincing argument for changing this from the status quo.

Well, equally it's not an argument for the 9.2 behavior, I guess. I'm
not convinced this is a bug (partly because I haven't found any explicit
statement regarding this in the docs), that's why I started this thread
instead of spamming pgsql-bugs.

For us (our app) this means we'll need to make it a bit more complex,
add some more explicit locking that we did not need in 9.1. Acquiring an
Access Share lock explicitly feels a bit strange, because that's the
lock acquired by SELECT statement anyway.

The only difference seems to be that the snapshot is not reacquired
after obtaining the lock. Which may or may not be the right thing,
depending on the definition of when the query was executed (when asking
for the lock or after obtaining it?)

Anyway, this seems to me like a behavior change that might bite many
others, unknowingly depending on the 9.1-like behavior and I believe
it's worth mentioning somewhere - not sure where.

You can hit the same/similar behaviour in 9.1 even if youre not using PREPARE
although the window isn't too big and you need DML + only an EXCLUSIVE (not
access exlusive) lock for it.

Probably yes, but we're not doing that so I haven't noticed that.

Tomas

On Wednesday, October 10, 2012 11:45:41 PM Tomas Vondra wrote:

On 10.10.2012 23:31, Andres Freund wrote:

On Wednesday, October 10, 2012 11:23:10 PM Tomas Vondra wrote:

On 10.10.2012 23:05, Andres Freund wrote:

On Wednesday, October 10, 2012 10:43:57 PM Thom Brown wrote:

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

Hi,

I've just noticed a change of LOCK command behavior between 9.1 and
9.2, and I'm not sure whether this is expected or not.

Let's use a very simple table

CREATE TABLE x (id INT);

Say there are two sessions - A and B, where A performs some
operations on "x" and needs to protect them with an "ACCESS
EXCLUSIVE" lock (e.g. it might be a pg_bulkload that acquires such
locks, and we need to do that explicitly on one or two places).

Session B is attempting to read the data, but is blocked and waits.
On 9.1 it sees the commited data (which is what we need) but on 9.2
it sees only data commited at the time of the lock attemt.

Example:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table
itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da

http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php

Very likely, yes. In fact you get the same beaviour in 9.1 if you
modify the example slightly:

B: PREPARE foo AS SELECT * FROM x;
A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: EXECUTE foo;
A: COMMIT;

If you think about it for a second its not that surprising anymore. We
start to execute a query, acquire a snapshot for that, and then wait
for the locks on the target relations. We continue executing in the
same snapshot for the duration of the statement and thus cannot see
any of the new rows which committed *after* we assembled our snapshot.

Yes, that was my guess too (that the snapshot is acquired before asking
for the lock and not re-acquired after getting the lock).

The easy workaround is acquiring a AccessShareLock in the B transaction
separately.

I know - I've mentioned explicit locking as a possible solution in my
first message, although it would make the whole process more complex.

I read your original statement as if you would want to use a separate
lock (advisory?) which you don't need.

Oh yeah, right. Any lock would work - advisory or not.

Well, it needs to be a lock youre conflicting on, not any lock ;)

The question is whether that should be necessary or whether the 9.2
should behave the same as 9.1.

Given that 9.1 behaves the same as 9.2 with prepared statements I don't
really see a convincing argument for changing this from the status quo.

Well, equally it's not an argument for the 9.2 behavior, I guess. I'm
not convinced this is a bug (partly because I haven't found any explicit
statement regarding this in the docs), that's why I started this thread
instead of spamming pgsql-bugs.

For us (our app) this means we'll need to make it a bit more complex,
add some more explicit locking that we did not need in 9.1. Acquiring an
Access Share lock explicitly feels a bit strange, because that's the
lock acquired by SELECT statement anyway.

Yea, but its acquired *after* the snapshot is taken. And again, thats what
happened in 9.1 as well. Just that *another* snapshot was just for planning the
query which by also needs to lock the table in share mode. So after the lock
was taken for planning a new snapshot was acquired for execution... Thats not
the case anymore in simpler cases.

The only difference seems to be that the snapshot is not reacquired
after obtaining the lock. Which may or may not be the right thing,
depending on the definition of when the query was executed (when asking
for the lock or after obtaining it?)

You can't generally reacquire snapshots after waiting for a lock. For one it
would be noticeably expensive and for another it would actually result in very
strange behaviour in queries with multiple tables.

Anyway, this seems to me like a behavior change that might bite many
others, unknowingly depending on the 9.1-like behavior and I believe
it's worth mentioning somewhere - not sure where.

"Locking is not as simple as you (and most of us) thought!" ;)

You can hit the same/similar behaviour in 9.1 even if youre not using
PREPARE although the window isn't too big and you need DML + only an
EXCLUSIVE (not access exlusive) lock for it.

Probably yes, but we're not doing that so I haven't noticed that.

Btw, unrelated to this problem, but why are you access exlusive locking that
table? Shouldn't an exlusive lock be enough?

Greetings,

Andres
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 10.10.2012 23:57, Andres Freund wrote:

On Wednesday, October 10, 2012 11:45:41 PM Tomas Vondra wrote:

Oh yeah, right. Any lock would work - advisory or not.

Well, it needs to be a lock youre conflicting on, not any lock ;)

Oh, yeah, right. I was thinking about acquiring the same advisory lock
in both sessions, but somehow I forgot to mention that.

For us (our app) this means we'll need to make it a bit more complex,
add some more explicit locking that we did not need in 9.1. Acquiring an
Access Share lock explicitly feels a bit strange, because that's the
lock acquired by SELECT statement anyway.

Yea, but its acquired *after* the snapshot is taken. And again, thats what
happened in 9.1 as well. Just that *another* snapshot was just for planning the
query which by also needs to lock the table in share mode. So after the lock
was taken for planning a new snapshot was acquired for execution... Thats not
the case anymore in simpler cases.

Yes, exactly what I was suspecting.

The only difference seems to be that the snapshot is not reacquired
after obtaining the lock. Which may or may not be the right thing,
depending on the definition of when the query was executed (when asking
for the lock or after obtaining it?)

You can't generally reacquire snapshots after waiting for a lock. For one it
would be noticeably expensive and for another it would actually result in very
strange behaviour in queries with multiple tables.

Not sure what strange behaviour would that cause (given that 9.1 did
that if I understand that correctly). More expensive - no doubt about
it, and the commit message mentions that it's an optimization.

Anyway, this seems to me like a behavior change that might bite many
others, unknowingly depending on the 9.1-like behavior and I believe
it's worth mentioning somewhere - not sure where.

"Locking is not as simple as you (and most of us) thought!" ;)

I've never thought it's simple, there's a lot of things to deal with.
But I was somehow surprised that something that worked fine for quite
long time, suddenly broke on 9.2. Yes, it might be a dependency on
something that was not really guaranteed.

You can hit the same/similar behaviour in 9.1 even if youre not using
PREPARE although the window isn't too big and you need DML + only an
EXCLUSIVE (not access exlusive) lock for it.

Probably yes, but we're not doing that so I haven't noticed that.

Btw, unrelated to this problem, but why are you access exlusive locking that
table? Shouldn't an exlusive lock be enough?

Long story short, we don't, pg_bulkload does. And it's also the reason
why we're so unhappy about the change, because the other session is
waiting for the load to complete and then it is served with an old snapshot.

Tomas

Tomas Vondra <tv@fuzzy.cz> writes:

On 10.10.2012 22:43, Thom Brown wrote:

On 10 October 2012 21:21, Tomas Vondra <tv@fuzzy.cz> wrote:

A: BEGIN;
A: LOCK x IN ACCESS EXCLUSIVE MODE;
A: INSERT INTO x VALUES (100);
B: SELECT * FROM x;
A: COMMIT;

Now on 9.1, B receives the value "100" while on 9.2 it gets no rows.

Is this expected? I suspect the snapshot is read at different time or
something, but I've checked release notes but I haven't seen anything
relevant.

Without getting the commited version of data, the locking is somehow
pointless for us (unless using a different lock, not the table itself).

I suspect it's this commit: d573e239f03506920938bf0be56c868d9c3416da
http://archives.postgresql.org/pgsql-committers/2011-12/msg00167.php

Maybe, the description suggests it might be related. I'm still not sure
whether this is a bug or expected behavior, although the commit clearly
states that the change shouldn't be user-visible.

Yeah, I think that last is the key point: this patch was sold on the
grounds that it wouldn't cause any interesting user-visible behavioral
change, but your example blows that claim into tiny little pieces.

I'm inclined to think we need to revert this. The performance gain is
not worth the prospect of breaking a lot of applications that used to
work reliably. Robert?

regards, tom lane

On Wed, Oct 10, 2012 at 7:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah, I think that last is the key point: this patch was sold on the
grounds that it wouldn't cause any interesting user-visible behavioral
change, but your example blows that claim into tiny little pieces.

I'm inclined to think we need to revert this. The performance gain is
not worth the prospect of breaking a lot of applications that used to
work reliably. Robert?

Yeah, I have to admit that I didn't consider the possibility that a
lock wait might intervene between the first and second snapshots. But
something about this isn't making sense to me. The whole idea of that
patch is that we reuse the parse/plan snapshot at execution time. If
it's wrong to use a snapshot that might be stale at execution time,
then it's equally wrong to use it for parse/plan. Surely, parse/plan
also requires a lock before doing anything interesting with the table,
so the problem here must be that we're taking the parse/plan snapshot,
then getting the lock (after waiting), then doing something with the
parse/plan snapshot that fails to notice that the snapshot is stale.
However, in this example, the parse/plan stuff we do with the stale
snapshot fails to be noticeable, but if we continue on to execution
then it is noticeable.

But that sounds more like a happy accident than anything else.
Andres' example upthread shows that a slightly different test case
breaks on 9.1 and 9.2, and I suspect it's possible to construct other
examples that behave in surprising ways on 9.1 but *not* on 9.2, by
looking for a scenario where the fact that parse/plan uses a different
snapshot from execution manifests itself as a user-visible
inconsistency. Reverting the patch has the advantage of being simple,
not requiring a lot of thought, and restoring the historical behavior,
but I'm not that excited about it otherwise. It seems to me that the
root of the issue here is that people is not that people expect two
snapshots -- indeed, a number of people strongly supported getting rid
of that behavior at the time -- but rather that they expect the
snapshot to be taken after locks are acquired.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

Robert Haas <robertmhaas@gmail.com> writes:

On Wed, Oct 10, 2012 at 7:10 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Yeah, I think that last is the key point: this patch was sold on the
grounds that it wouldn't cause any interesting user-visible behavioral
change, but your example blows that claim into tiny little pieces.

I'm inclined to think we need to revert this. The performance gain is
not worth the prospect of breaking a lot of applications that used to
work reliably. Robert?

Yeah, I have to admit that I didn't consider the possibility that a
lock wait might intervene between the first and second snapshots. But
something about this isn't making sense to me.

It's not hard to see what's happening. The old code was:

* take parse/plan snapshot
* parse & plan query (includes taking AccessShare lock on tables)
* take execution snapshot
* run query

Taking the AccessShare lock blocks until the transaction with exclusive
lock commits; therefore, the execution snapshot will see that
transaction's effects.

In the new code, we re-use the parse/plan snapshot for execution, so it
predates the other transaction's commit, so we don't see its effects.

The whole idea of that
patch is that we reuse the parse/plan snapshot at execution time. If
it's wrong to use a snapshot that might be stale at execution time,
then it's equally wrong to use it for parse/plan.

Nope, because the parse/plan snapshot is only relevant to planner
estimation purposes. It doesn't have to be exactly right. (What does
have to be exactly right is our idea of the table structure, but that's
okay because any catalog consultation we do is with SnapshotNow, and
hence will see any DDL the other transaction might have done.)

But that sounds more like a happy accident than anything else.

[ shrug... ] Maybe it's a happy accident and maybe somebody designed it
with malice aforethought many years ago. But the point is that this
type of thing worked, with 100% reliability, before 9.2. Now it does
not. I don't think we can accept that.

It seems to me that the
root of the issue here is that people is not that people expect two
snapshots -- indeed, a number of people strongly supported getting rid
of that behavior at the time -- but rather that they expect the
snapshot to be taken after locks are acquired.

Sure. Maybe we can rejigger things in a way that does that, although
I think the stumbling block is going to be parse-time calls to
user-defined I/O functions for constants --- which might need a
snapshot. It might be possible to redesign things so that all tables
are locked before we do anything that requires a non-SnapshotNow
snapshot, and then take a single "planning/execution" snapshot. But
that is not this patch, and would be a lot more invasive than this
patch, and would certainly not be back-patchable to 9.2.

I think we have to revert and go back to the drawing board on this.

regards, tom lane

On Wed, Oct 10, 2012 at 08:43:34PM -0400, Tom Lane wrote:

It seems to me that the
root of the issue here is that people is not that people expect two
snapshots -- indeed, a number of people strongly supported getting rid
of that behavior at the time -- but rather that they expect the
snapshot to be taken after locks are acquired.

Sure. Maybe we can rejigger things in a way that does that, although
I think the stumbling block is going to be parse-time calls to
user-defined I/O functions for constants --- which might need a
snapshot. It might be possible to redesign things so that all tables
are locked before we do anything that requires a non-SnapshotNow
snapshot, and then take a single "planning/execution" snapshot. But
that is not this patch, and would be a lot more invasive than this
patch, and would certainly not be back-patchable to 9.2.

I think we have to revert and go back to the drawing board on this.

Is reverting going to adversely affect users who are already using the
9.2 behavior?

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Oct 10, 2012 at 08:43:34PM -0400, Tom Lane wrote:

I think we have to revert and go back to the drawing board on this.

Is reverting going to adversely affect users who are already using the
9.2 behavior?

In what way would somebody be relying on the 9.2 behavior?

regards, tom lane

On Wed, Oct 10, 2012 at 09:29:16PM -0400, Tom Lane wrote:

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Oct 10, 2012 at 08:43:34PM -0400, Tom Lane wrote:

I think we have to revert and go back to the drawing board on this.

Is reverting going to adversely affect users who are already using the
9.2 behavior?

In what way would somebody be relying on the 9.2 behavior?

I don't know. I am just asking if an application could be relying on
the 9.2 behavior.

--
Bruce Momjian <bruce@momjian.us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

Bruce Momjian <bruce@momjian.us> writes:

On Wed, Oct 10, 2012 at 09:29:16PM -0400, Tom Lane wrote:

In what way would somebody be relying on the 9.2 behavior?

I don't know. I am just asking if an application could be relying on
the 9.2 behavior.

I don't think so. Robert suggested in the original discussion that
there could be cases where users would notice if the plan snapshot was
different from the execution snapshot, but on reflection I consider that
argument bogus. We have these categories of snapshot-dependent things
that happen before execution starts:

* System examination of table DDL. This is all done with SnapshotNow
and after acquiring a lock on the table, so it's secure.

* Evaluation of the input functions for user-defined types and domains
(the latter of which can invoke nearly arbitrary code via CHECK
constraints). In principle you could imagine that one of these
datatypes or domain check constraints involves looking at the tables
that the surrounding query is going to touch, but I don't think anybody
would consider that good programming style, much less expect that it
would necessarily see exactly the same table state as query execution
does.

* Evaluation of IMMUTABLE functions at plan time. If such a function
actually cares exactly which snapshot it runs with, then it's not very
immutable, and I feel no compunction about breaking it.

* Evaluation of STABLE functions at plan time for estimation purposes.
Such a function might well get different answers depending on which
snapshot it uses --- but the result is only used for estimation
purposes. The worst possible consequence is an inferior plan; there is
no correctness issue.

So it seems to me to be pretty difficult to credit that any of these
things would care at all whether they saw the exact same snapshot that
query execution does. It's even less plausible that somebody would have
created such a dependency in the short time 9.2 has been out, when such
code could not have worked in any prior release.

regards, tom lane