The following bug has been logged on the website:
Bug reference: 19402
Logged by: AmirT
Email address: amir.i.m.blade@gmail.com
PostgreSQL version: 16.8
Operating system: Operating system: AWS Lambda (Amazon Linux 2)
Description:
PostgreSQL version: Aurora PostgreSQL (compatible with PostgreSQL 13+)
Description:
When two concurrent sessions execute CREATE TABLE IF NOT EXISTS for the same
partition (child table) of a partitioned table, one session receives:
psycopg.errors.DuplicateTable: relation "accounts _<tenant_id >" already
exists
despite using IF NOT EXISTS.
Steps to reproduce:
1. Create a partitioned table:
CREATE TABLE accounts (
id UUID, tenant_id UUID
) PARTITION BY LIST (tenant_id);
2. From two concurrent sessions, simultaneously execute:
CREATE TABLE IF NOT EXISTS accounts_<same_tenant_id>
PARTITION OF accounts FOR VALUES IN ('<same_tenant_id>');
3. One session succeeds, the other raises DuplicateTable error.
Expected behavior:
IF NOT EXISTS should suppress the error when the table is being concurrently
created, and to be "thread safe" and solving this seamlessly and gurantee
atomicity .
Actual behavior:
DuplicateTable exception is raised, and the transaction enters
INERROR state, making all subsequent SQL in that transaction fail.
Our code having a loop for 5-6 partitions, so even if we catch the
DuplicateTable to supress it and continuing to the next partition creation,
the connection's active transaction is marked as aborted, so any following
operation will fail because of that.
Impact:
In serverless architectures (AWS Lambda), concurrent invocations for the
same
tenant trigger this race condition during partition creation. which fails
the lambda main flow .
Workarounds attempted:
- SAVEPOINT wrapping each CREATE TABLE IF NOT EXISTS (should work but
non-standard, and increasing complexity and will be more tough to maintain.
- Advisory locks (pg_advisory_xact_lock) to serialize access - didn't try
that yet but adds complexity, not easy to maintain.
Please note -
Our concern that if the SAVEPOINT\Locks are ONLY solution - is that it is
not documented anywhere to be official best practice solution, and we do
think that it should be handled in the rds level,
and as mentioned before - those solutions are not easy to maintain and add
code complexity, for instance - since we have a loop for 6 partitions
creation, we'll need to use internal transaction with block (in python) and
outer one, to guarantee that outer scope will create active transaction ->
otherwise, each iteration won't create savepoint but a new transaction that
will be committed each operation but this is against our requirement where
we need all the partitions to be considered as one operation, so if one
fails we want to fail entire loop operation, unless - it is 'already exists'
error.
To conclude -
eventually we aim for a seamless solution that RDS level will handle it and
not in the application level, as this should be atomic operation and
'concurrent safe'
