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