Sfrgttk

How to install press fit bottom bracket into new frame

How to write the following sign?

How to react to hostile behavior from a senior developer?

A term for a woman complaining about things/begging in a cute/childish way

What does it mean that physics no longer uses mechanical models to describe phenomena?

How to compare two different files line by line in unix?

Why is it faster to reheat something than it is to cook it?

Do I really need to have a message in a novel to appeal to readers?

When a candle burns, why does the top of wick glow if bottom of flame is hottest?

Is it possible for SQL statements to execute concurrently within a single session in SQL Server?

Why is Nikon 1.4g better when Nikon 1.8g is sharper?

What is the font for "b" letter?

How to tell that you are a giant?

Is there a kind of relay only consumes power when switching?

Trademark violation for app?

What is the appropriate index architecture when forced to implement IsDeleted (soft deletes)?

Converted a Scalar function to a TVF function for parallel execution-Still running in Serial mode

An adverb for when you're not exaggerating

Denied boarding although I have proper visa and documentation. To whom should I make a complaint?

NumericArray versus PackedArray in MMA12

Project Euler #1 in C++

SF book about people trapped in a series of worlds they imagine

Performance gap between vector<bool> and array

How fail-safe is nr as stop bytes?

Postgres serializable transactions not enforcing isolation when joins are involved

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

The Postgres manual says that transactions in SERIALIZABLE mode behave as though they were executed serially. However, this example seems to refute that. Am I missing something?

Suppose the application using transaction 1 wants to insert a row into B referring to the row in A with id 1, but only if no other rows in B already refer to that row in A. (I know you can do this properly with just UPDATE ... WHERE, but in more complicated applications, that's hard to deal with.)

% setup

CREATE TABLE a (id serial primary key);

CREATE TABLE b (a int REFERENCES a(id), val text);

INSERT INTO a(id) VALUES (1);





% transaction 1:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;

% Leaves transaction open...



% transaction 2:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT INTO b(a, val) VALUES (1, 'foo');  % Shouldn't succeed but does.

COMMIT;



% transaction 1:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 0 rows. Ok, let's insert...

INSERT INTO b(a, val) VALUES (1, 'foo');

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 1 rows. As expected.

COMMIT;



% no longer in a transaction:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 2 rows?!

If I instead do in transaction 1 SELECT... FOR UPDATE, I get the expected behavior. Session 2 blocks when it tries to insert. But strangely, this doesn't happen if I leave out the FK in table B (REFERENCES a(id)), which is bad because I can imagine cases where there'd be no FK.

postgresql isolation-level

share

asked 2 mins ago

sudontsudont

1

New contributor

sudont is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

0

The Postgres manual says that transactions in SERIALIZABLE mode behave as though they were executed serially. However, this example seems to refute that. Am I missing something?

Suppose the application using transaction 1 wants to insert a row into B referring to the row in A with id 1, but only if no other rows in B already refer to that row in A. (I know you can do this properly with just UPDATE ... WHERE, but in more complicated applications, that's hard to deal with.)

% setup

CREATE TABLE a (id serial primary key);

CREATE TABLE b (a int REFERENCES a(id), val text);

INSERT INTO a(id) VALUES (1);





% transaction 1:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;

% Leaves transaction open...



% transaction 2:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT INTO b(a, val) VALUES (1, 'foo');  % Shouldn't succeed but does.

COMMIT;



% transaction 1:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 0 rows. Ok, let's insert...

INSERT INTO b(a, val) VALUES (1, 'foo');

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 1 rows. As expected.

COMMIT;



% no longer in a transaction:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 2 rows?!

If I instead do in transaction 1 SELECT... FOR UPDATE, I get the expected behavior. Session 2 blocks when it tries to insert. But strangely, this doesn't happen if I leave out the FK in table B (REFERENCES a(id)), which is bad because I can imagine cases where there'd be no FK.

postgresql isolation-level

share

asked 2 mins ago

sudontsudont

1

New contributor

sudont is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

add a comment | 

The Postgres manual says that transactions in SERIALIZABLE mode behave as though they were executed serially. However, this example seems to refute that. Am I missing something?

Suppose the application using transaction 1 wants to insert a row into B referring to the row in A with id 1, but only if no other rows in B already refer to that row in A. (I know you can do this properly with just UPDATE ... WHERE, but in more complicated applications, that's hard to deal with.)

% setup

CREATE TABLE a (id serial primary key);

CREATE TABLE b (a int REFERENCES a(id), val text);

INSERT INTO a(id) VALUES (1);





% transaction 1:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;

% Leaves transaction open...



% transaction 2:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT INTO b(a, val) VALUES (1, 'foo');  % Shouldn't succeed but does.

COMMIT;



% transaction 1:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 0 rows. Ok, let's insert...

INSERT INTO b(a, val) VALUES (1, 'foo');

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 1 rows. As expected.

COMMIT;



% no longer in a transaction:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 2 rows?!

If I instead do in transaction 1 SELECT... FOR UPDATE, I get the expected behavior. Session 2 blocks when it tries to insert. But strangely, this doesn't happen if I leave out the FK in table B (REFERENCES a(id)), which is bad because I can imagine cases where there'd be no FK.

postgresql isolation-level

share

asked 2 mins ago

sudontsudont

1

New contributor

sudont is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

% setup

CREATE TABLE a (id serial primary key);

CREATE TABLE b (a int REFERENCES a(id), val text);

INSERT INTO a(id) VALUES (1);





% transaction 1:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;

% Leaves transaction open...



% transaction 2:

BEGIN;

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

INSERT INTO b(a, val) VALUES (1, 'foo');  % Shouldn't succeed but does.

COMMIT;



% transaction 1:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 0 rows. Ok, let's insert...

INSERT INTO b(a, val) VALUES (1, 'foo');

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 1 rows. As expected.

COMMIT;



% no longer in a transaction:

SELECT a.* FROM a, b WHERE b.a = a.id AND a.id = 1;  % 2 rows?!

share

asked 2 mins ago

sudontsudont

1

New contributor

sudont is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

share

asked 2 mins ago

sudontsudont

1

New contributor

sudont is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

sudontsudont

1

New contributor

sudont is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

asked 2 mins ago

sudontsudont

1