From: PG Bug reporting form <noreply@postgresql.org>
To: pgsql-bugs@lists.postgresql.org
Cc: thierrym@gmail.com
Subject: BUG #17902: export/import tenant not possible due to PG internal id on jsonB fields.
Date: Mon, 17 Apr 2023 11:34:48 +0000
Message-ID: <17902-e75daa72de96a626@postgresql.org> (raw)
The following bug has been logged on the website:
Bug reference: 17902
Logged by: thierry melkebeke
Email address: thierrym@gmail.com
PostgreSQL version: 14.6
Operating system: no matter
Description:
the context:
We have multitenant databases. Each tenant is one of our customers.
To ensure a clear separation between the tenants, each table contains a
field "tenantid" and we apply some RLS config based on tenantid.
From time to time, we want to export one tenant from production db to
acceptance or training db
we never export/import the whole db, because it is not fine for most of the
customers, so we export/import one particular tenant.
The procedure is the following
- pg_dump tenant X from production
- reset tenant X in acceptance
- import tenant X in acceptance
Of course a record with id=123456 in production can be rejected because the
same id was already used by another tenant in acceptance. To avoid such id
clashes, we replaced all the id (sequences) by uuid on all tables.
This way the identifiers are always unique and there is no problem
This is working fine!! But now we have some tables with a jsonB field
Those large objects fields are stored separately by postgresl and an
(internal) id is used to link the large object and the records in our
tables.
When we export tenant X from production, the import in acceptance can fail
if the internal id on the jsonB is already used.
To solved this, I think the internal tables to store large objects should
use uuid instead of classical sequences
wdyt?
thierry melkebeke
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-bugs@postgresql.org
Cc: noreply@postgresql.org, pgsql-bugs@lists.postgresql.org, thierrym@gmail.com
Subject: Re: BUG #17902: export/import tenant not possible due to PG internal id on jsonB fields.
In-Reply-To: <17902-e75daa72de96a626@postgresql.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox