Re: Re: OID wraparound: summary and proposal

Tom Lane wrote:
>
> Given Hiroshi's objections, and the likelihood of compatibility problems
> for existing applications, I am now thinking that it's not a good idea to
> turn off OID generation by default.  (At least not for 7.2 --- maybe in
> some future release we could change the default.)
>
> Based on the discussion so far, here is an attempt to flesh out the
> details of what to do with OIDs for 7.2:

Also OIDS should be promoted to 8-byte integers at some future time.

>
> 1. Add an optional clause "WITH OIDS" or "WITHOUT OIDS" to CREATE TABLE.
> The default behavior will be WITH OIDS.
>
> Note: there was some discussion of a GUC variable to control the default.
> I'm leaning against this, mainly because having one would mean that
> pg_dump *must* write WITH OIDS or WITHOUT OIDS in every CREATE TABLE;
> else it couldn't be sure that the database schema would be correctly
> reconstructed.  That would create dump-script portability problems and
> negate some of the point of having a GUC variable in the first place.
> So I'm thinking a fixed default is better.
>
> Note: an alternative syntax possibility is to make it look like the "with"
> option clauses for functions and indexes: "WITH (oids)" or "WITH (noOids)".
> This is uglier today, but would start to look more attractive if we invent
> additional CREATE TABLE options in the future --- there'd be a place to
> put 'em.  Comments?
>
> 2. A child table will be forced to have OIDs if any of its parents do,
> even if WITHOUT OIDS is specified in the child's CREATE command.  This is
> on the theory that the OID ought to act like an inherited column.
>
> 3. For a table without OIDs, no entry will be made in pg_attribute for
> the OID column, so an attempt to reference the OID column will draw a
> "no such column" error.  (An alternative is to allow OID to read as nulls,
> but it seemed that people preferred the error to be raised.)
>
> 4. When inserting into an OID-less table, the INSERT result string will
> always show 0 for the OID.
>
> 5. A "relhasoids" boolean column will be added to pg_class to signal
> whether a table has OIDs or not.
>
> 6. COPY out WITH OIDS will ignore the "WITH OIDS" specification if the
> table has no OIDs.  (Alternative possibility: raise an error --- is that
> better?)  COPY in WITH OIDS will silently drop the incoming OID values.
>
> 7. Physical tuple headers won't change.  If no OIDs are assigned for a
> particular table, the OID field in the header will be left zero.
>
> 8. OID generation will be disabled for those system tables that don't need
> it --- pg_listener, pg_largeobject, and pg_attribute being some major
> offenders that consume lots of OIDs.

1-8 sounds good

> 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 ;)

IMHO duplicate OID's in system tables should be treated as bug's - if
they 
are there they are meant to break stuff.

> The object
> type is the OID of the system catalog in which the object OID appears.
> The column number field will be zero for all object types except columns.
> For a column comment, the object type and OID fields will refer to the
> parent table, and column number will be nonzero.

What happens to columns added to inherited tables ?

> 10. pg_dump will be modified to do the appropriate things with OIDs.  Are
> there any other application programs that need to change?
>
> We had also talked about adding an INSERT ... RETURNING feature to allow
> applications to eliminate their dependence on looking at the OID returned
> by an INSERT command.  I think this is a good idea, but there are still
> a number of unsolved issues about how it should interact with rules.
> Accordingly, I'm not going to try to include it in this batch of work.
>