Thread: Duplicated oids between tables - problem or not?
I noticed after a pg_upgrade on a system, that the same oid is used both for a database and a user (repeated many times for different combinations of databases and users). This is because pg_upgrade doesn't preserve the database oid, and it reused the oid of the row in pg_authid. The reason I noticed this was that it confuses the hell out of pgadmin. This is clearly a bug in a SQL query pgadmin uses, and I'm going to go fix that. But is this something that can cause us problems somewhere else in the system? ISTM the same thing could happen after oid wraparound, but that pg_upgrade makes it a lot more likely to happen. So I'm thinking it shouldn't be a problem since the oid's are in different tables, but are there any other parts of the system where this could cause an actual problem? --Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/
Magnus Hagander <magnus@hagander.net> writes:
> I noticed after a pg_upgrade on a system, that the same oid is used
> both for a database and a user (repeated many times for different
> combinations of databases and users). This is because pg_upgrade
> doesn't preserve the database oid, and it reused the oid of the row in
> pg_authid.
> The reason I noticed this was that it confuses the hell out of
> pgadmin. This is clearly a bug in a SQL query pgadmin uses, and I'm
> going to go fix that.
> But is this something that can cause us problems somewhere else in the
> system? ISTM the same thing could happen after oid wraparound, but
> that pg_upgrade makes it a lot more likely to happen. So I'm thinking
> it shouldn't be a problem since the oid's are in different tables, but
> are there any other parts of the system where this could cause an
> actual problem?
It should not. It's been understood for many years that uniqueness of
OIDs is only guaranteed within individual catalogs (by means of their
unique indexes on OID). That's why mechanisms like pg_depend and
pg_description use catalog OID + object OID to identify objects.
We do insist that hand-assigned OIDs be globally unique, but that's just
for maintainers' sanity not because the code depends on it.
regards, tom lane
On Mon, Nov 26, 2012 at 5:04 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > Magnus Hagander <magnus@hagander.net> writes: >> I noticed after a pg_upgrade on a system, that the same oid is used >> both for a database and a user (repeated many times for different >> combinations of databases and users). This is because pg_upgrade >> doesn't preserve the database oid, and it reused the oid of the row in >> pg_authid. > >> The reason I noticed this was that it confuses the hell out of >> pgadmin. This is clearly a bug in a SQL query pgadmin uses, and I'm >> going to go fix that. > >> But is this something that can cause us problems somewhere else in the >> system? ISTM the same thing could happen after oid wraparound, but >> that pg_upgrade makes it a lot more likely to happen. So I'm thinking >> it shouldn't be a problem since the oid's are in different tables, but >> are there any other parts of the system where this could cause an >> actual problem? > > It should not. It's been understood for many years that uniqueness of > OIDs is only guaranteed within individual catalogs (by means of their > unique indexes on OID). That's why mechanisms like pg_depend and > pg_description use catalog OID + object OID to identify objects. That's what I figured. The problem with pgadmin is it joins to pg_shdescription without restricting it on the catalog oid. > We do insist that hand-assigned OIDs be globally unique, but that's just > for maintainers' sanity not because the code depends on it. Gotcha. Thanks! --Magnus HaganderMe: http://www.hagander.net/Work: http://www.redpill-linpro.com/