pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedDDL commands don't handle OID wrap
4+ messages / 3 participants
[nested] [flat]
* DDL commands don't handle OID wrap
@ 2006-04-17 19:38 Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 4+ messages in thread
From: Jim C. Nasby @ 2006-04-17 19:38 UTC (permalink / raw)
To: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DDL commands don't handle OID wrap
@ 2006-04-17 19:46 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim C. Nasby <jnasby@pervasive.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-04-17 19:46 UTC (permalink / raw)
To: Jim C. Nasby <jnasby@pervasive.com>; +Cc: pgsql-admin
"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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DDL commands don't handle OID wrap
@ 2006-04-17 22:09 Jim Nasby <jnasby@pervasive.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Jim Nasby @ 2006-04-17 22:09 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
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?"
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: DDL commands don't handle OID wrap
@ 2006-04-18 03:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jim Nasby <jnasby@pervasive.com>
0 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2006-04-18 03:57 UTC (permalink / raw)
To: Jim Nasby <jnasby@pervasive.com>; +Cc: pgsql-admin
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-04-18 03:57 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-17 19:38 DDL commands don't handle OID wrap Jim C. Nasby <jnasby@pervasive.com>
2006-04-17 19:46 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-17 22:09 ` Jim Nasby <jnasby@pervasive.com>
2006-04-18 03:57 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox