OID wraparound: summary and proposal

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