Обсуждение: DDL commands don't handle OID wrap
Haven't seen this in previous discussions of OID wrap in the archives. The issue is that DDL statements don't make any attempt to skip past used ranges of OIDs. duplicate key violates unique constraint "pg_attrdef_oid_index" duplicate key violates unique constraint "pg_type_oid_index" etc... While part of the solution is obviously not to define tables WITH OIDS, I would argue that this is still an issue because temporary objects use OIDs. I'm wondering if anyone has run into this problem and isn't using tables with OIDs. Probably the easiest way to fix this would be to trap oid unique constraint violations on catalog tables and find the next available number to use (probably want to bump the OID counter up to that at the same time). Of course a better method would be to drop OIDs from the catalog tables completely, but given the amount of work involved in that... -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"Jim C. Nasby" <jnasby@pervasive.com> writes:
> Haven't seen this in previous discussions of OID wrap in the archives.
> The issue is that DDL statements don't make any attempt to skip past
> used ranges of OIDs.
> duplicate key violates unique constraint "pg_attrdef_oid_index"
If you can make that happen in 8.1, I'd be fascinated to look at the
test case.
2005-08-11 21:35 tgl
* doc/src/sgml/ddl.sgml, src/backend/access/heap/heapam.c,
src/backend/access/heap/tuptoaster.c,
src/backend/access/transam/varsup.c, src/backend/catalog/catalog.c,
src/backend/catalog/heap.c, src/backend/catalog/index.c,
src/backend/catalog/pg_type.c, src/backend/commands/dbcommands.c,
src/backend/commands/trigger.c, src/backend/commands/typecmds.c,
src/backend/storage/large_object/inv_api.c,
src/backend/utils/cache/relcache.c,
src/bin/pg_dump/pg_backup_archiver.c, src/bin/pg_dump/pg_dump.c,
src/include/access/transam.h, src/include/catalog/catalog.h,
src/include/catalog/pg_type.h, src/include/utils/rel.h,
src/include/utils/relcache.h: Solve the problem of OID collisions
by probing for duplicate OIDs whenever we generate a new OID. This
prevents occasional duplicate-OID errors that can otherwise occur
once the OID counter has wrapped around. Duplicate relfilenode
values are also checked for when creating new physical files. Per
my recent proposal.
regards, tom lane
On Apr 17, 2006, at 2:46 PM, Tom Lane wrote:
> "Jim C. Nasby" <jnasby@pervasive.com> writes:
>> Haven't seen this in previous discussions of OID wrap in the
>> archives.
>> The issue is that DDL statements don't make any attempt to skip past
>> used ranges of OIDs.
>> duplicate key violates unique constraint "pg_attrdef_oid_index"
>
> If you can make that happen in 8.1, I'd be fascinated to look at the
> test case.
Yet another reason for this customer to upgrade... :)
Also, in 8.1, here's a list of tables in pg_catalog that don't appear
to have unique constraints on oid... do these all look safe?
decibel=# select table_name from information_schema.tables where
table_schema='pg_catalog' and table_type='BASE TABLE' except select
tablename from pg_indexes where schemaname='pg_catalog' and indexname
like '%oid%';
table_name
-----------------
pg_amop
pg_amproc
pg_attribute
pg_auth_members
pg_autovacuum
pg_depend
pg_description
pg_index
pg_inherits
pg_listener
pg_pltemplate
pg_shdepend
pg_statistic
--
Jim C. Nasby, Database Architect decibel@decibel.org
Give your computer some brain candy! www.distributed.net Team #1828
Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"
Jim Nasby <jnasby@pervasive.com> writes:
> Also, in 8.1, here's a list of tables in pg_catalog that don't appear
> to have unique constraints on oid... do these all look safe?
I'd be a bit surprised to find such a constraint on any of these,
seeing that they don't have oids.
regards, tom lane