Thread: 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