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

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

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:       

To solved this, I think the internal tables to store large objects should
use uuid instead of classical sequences]

On Mon, 17 Apr 2023 at 10:49, David G. Johnston
<david.g.johnston@gmail.com> wrote:
>
> This isn’t a bug and changing OIDs from integers to UUIDs isn’t going to happen.

Hm. Changing OIDs to UUIDs on catalog tables isn't going to happen.
But large objects are kind of a leaky abstraction in that the internal
ID becomes a user-visible identifier that is the only way users have
of referencing these objects.

I could see it being pretty useful to have an user-controlled
identifier instead of forcing users to use OIDs. Like, the only good
solution for this case that I see is to have a mapping table so they
would load the new LOBs and get new OIDs and then run a program to
pick up the UUIDs from within the JSON and store new mappings. That's
a pain and I don't see any reason for that mapping to be in a separate
table instead of in the LOB table itself.

But the bad news is that I don't think anyone's really excited about
working on the LOB facility. It's more than a decade old and I don't
think there's been any new features or optimization work done on it in
at least that long. I think most users who would drive any new work on
it end up deciding to use some external-to-the-database object store
and store references in the database. (which has issues with backups
and failovers getting out of sync but on the flip side makes backups
and replication way easier to manage resources for)

--
greg

Greg Stark <stark@mit.edu> writes:
> But large objects are kind of a leaky abstraction in that the internal
> ID becomes a user-visible identifier that is the only way users have
> of referencing these objects.

> I could see it being pretty useful to have an user-controlled
> identifier instead of forcing users to use OIDs.

You already can create LOBs with any identifier you want, as long
as it's an int4.  The OP seems to think he can mix manual allocation
with automatic allocation of those IDs and it should just work.

> But the bad news is that I don't think anyone's really excited about
> working on the LOB facility.

Yeah.  There's been some discussion of widening the identifiers to
int8, but I can't see going further than that.  UUIDs would be a
disaster for a number of reasons, both performance and compatibility
related.

            regards, tom lane