Re: Re: OID wraparound: summary and proposal

Tom Lane wrote:
>
> Hannu Krosing <hannu(at)tm(dot)ee> writes:
> >> 9. To continue to support COMMENT ON COLUMN when columns have no OIDs,
> >> pg_description will be modified so that its primary key is (object type,
> >> object OID, column number) --- this also solves the problem that comments
> >> break if there are duplicate OIDs in different system tables.
>
> > Hm.. To me this sounds like allowing duplicates in an unique index in
> > case there happen to be duplicate keys there ;)
>
> Unless you want to implement a global unique index

Or insert/update trigger

> that can enforce uniqueness of OIDs across all the system tables, I don't
> think that approach is tenable.

As I wrote in another mail to this list, AFAIK OID is supposed to be
Object Identifier - something that can be used to identify any object
in a unique fashion.

When (and if ;) we will implement SQL3's UNDER, we should, IMHO, make a
primary
key inherited and *unique* over all tables created UNDER the main table,
meaning
that we will need a way to have uniqueness constraint spanning multiple
tables.

( At least logically multiple tables, as IMHO UNDER with its single
inheritance is
best implemented in a single table with a bit more flexible column
structure. )

At that time we could theoretically inherit all system tables that have
OID
column from table "pg_system(oid oid primary key);"

> pg_description is broken as it stands. Bruce
> doesn't like the "column number" part of my proposal --- I suppose he'd
> rather see the pg_description key as just <object type, object OID> with
> object type referring to pg_attribute if it's a comment on column.
> That would work too as far as fixing the lack of uniqueness goes, but it
> still leaves us with pg_attribute as a significant consumer of OIDs.

That would probably be a problem with 4-byte OIDs, there is an ample
supply of 8-byte ones

I do like dropping OID from pg_listener, as it is a mostly empty and
really
rapidly changing table, but I see little value in dropping oid from
pg_attribute.

BTW, don't indexes, triggers or saved plans use OIDs from pg_attribute ?

> Since the major point of this exercise (in my mind) is cutting the rate
> of consumption of OIDs to postpone wraparound, I want to suppress OIDs
> in pg_attribute, and to do that I have to add the column number to
> pg_description.

I still think that going to 8-byte OIDs would be the best use of your
time ;)

If you can make the size of oid's a compile time option, then even
better.

Postponing the wraparound by the means you describe may be a fools
errand anyway,
as there are other ways to quickly consume oids that are very likely as
common as
those involving pg_listener, pg_largeobject, and pg_attribute.

Also computers still get faster, and disks still get bigger at the rate
I doubt
you will be able to match by finding ways to postpone the wraparound.

So here I'd like to contradict Vadim's claim that the time of simple
solutions is
over for PostgreSQL - making OID bigger is at least conceptually simple,
it's just
"a small matter of programming" ;)

--------------
Hannu