pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
DDL 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