What is the relevance of ‘SET default_with_oids = true’ in a Postgresql dump?

Posted on

Question :

I have an SQL backup from an old database which may have been a PostgreSQL 7 or 8 database I am importing which contains a SET default_with_oids - true statement, into a an existing 8.4 database and wonder what the relevance is and how it might affect the existing database.

I don’t think the OIDs were actually used in the original database. Can I simply ignore it?

Answer :

The documentation states the following:

default_with_oids (boolean)

This controls whether CREATE TABLE and CREATE TABLE AS include an OID column in newly-created tables, if neither WITH OIDS nor WITHOUT
OIDS is specified. It also determines whether OIDs will be included in
tables created by SELECT INTO. The parameter is off by default; in
PostgreSQL 8.0 and earlier, it was on by default.

The use of OIDs in user tables is considered deprecated, so most installations should leave this variable disabled. Applications that
require OIDs for a particular table should specify WITH OIDS when
creating the table. This variable can be enabled for compatibility
with old applications that do not follow this behavior.

Note that the feature was deprecated with the coming of 8.0.

This means that if you plan to use the DB as it is, you can safely remove this line before restoring (you have to be really sure that they are not referred to). It will be left set to the default false.

If you have a legacy application working on this DB, you will want to check it, too, if it uses the OIDs in any way. (At the moment I cannot really think of such a use case, but it may be the weakness of my imagination.)

A side note: you may experience some other issues restoring the dump – I remember some problems with changed implicit cast behaviour.

Leave a Reply

Your email address will not be published. Required fields are marked *