Re: OID wraparound: summary and proposal

I posted this question earlier, but it looks like it never made it on.

If you can control the OIDs on a per table basis, and some tables need not even
have any, why not let each table have its own OID range? Essentially, each
record will be numbered relative to 0 on its table?

That would really cut down the OID wrap around problem, and allow records to
have a notion of serialization.

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:
> 
> 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.
> 
> 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.  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.
> 
> 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.
> 
> Comments?
> 
>                         regards, tom lane
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

-- 
5-4-3-2-1 Thunderbirds are GO!
------------------------
http://www.mohawksoft.com