BUG #17902: export/import tenant not possible due to PG internal id on jsonB fields.

From: PG Bug reporting form <noreply(at)postgresql(dot)org>
To: pgsql-bugs(at)lists(dot)postgresql(dot)org
Cc: thierrym(at)gmail(dot)com
Subject: BUG #17902: export/import tenant not possible due to PG internal id on jsonB fields.
Date: 2023-04-17 11:34:48
Message-ID: 17902-e75daa72de96a626@postgresql.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

The following bug has been logged on the website:

Bug reference: 17902
Logged by: thierry melkebeke
Email address: thierrym(at)gmail(dot)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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Ann In Dark 2023-04-17 14:04:04 BUGREPORT: Performance degradation in Postgres with old_snapshot_threshold parameter in version 12.*
Previous Message PG Bug reporting form 2023-04-17 11:33:28 BUG #17901: Unexpected 'permission denied' error depending on which template used to create database