3

Assuming PostgreSQL 10.3 and Read Committed: does a query over a partitioned table introduce a race condition in the case one of the partitions is modified by another process?

E.g. given this schema:

CREATE TABLE foo (
    id VARCHAR,
    state VARCHAR
) PARTITION BY LIST(state);
CREATE TABLE foo_pending PARTITION OF foo FOR VALUES IN ('pending');
CREATE TABLE foo_ready PARTITION OF foo FOR VALUES IN ('ready');

Let's say I have one process that queries:

SELECT * FROM foo WHERE id = 'something';

And another process:

BEGIN TRANSACTION;
DELETE FROM foo_ready WHERE id = 'something';
INSERT INTO foo_pending VALUES ('something', 'pending');
COMMIT;

Is it possible that the first process finds no matching rows in partition foo_pending (because it fetched before the other process committed) and also no matching rows in foo_ready (because it fetched after the other process committed)?

Do I need SERIALIZABLE (or REPEATABLE READ) to overcome this issue?

Davide R.
  • 860
  • 5
  • 24
  • I see from a couple sources that in Read Committed a single `SELECT` statement will read a single database snapshot, like with `UNION`. Still looking for confirmation that this is indeed the behavior with partitioned tables. – Davide R. Mar 21 '18 at 18:10

0 Answers0