Killing OIDs

On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote:

I'm about to deal with an upgrade of a server running 7.4. I have
checked with the developers and they are not using OIDs so I'd like to
remove them so they aren't carried forward to 8.3.

My plan is to do the OID removal on 7.4. I can get a script with:
SELECT
'ALTER TABLE ' || relname || ' SET WITHOUT OIDS;'
FROM
pg_class
WHERE
relkind='r' and
relowner != 1 and
relhasoids;

Before I pull the trigger, I figured I'd post and find out if anyone
sees any feet in the way.

That won't drop the OID columns.

Joshua D. Drake

Cheers,
Steve

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

----- "Joshua D. Drake" <jd@commandprompt.com> wrote:

On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote:

I'm about to deal with an upgrade of a server running 7.4. I have
checked with the developers and they are not using OIDs so I'd like

to

remove them so they aren't carried forward to 8.3.

My plan is to do the OID removal on 7.4. I can get a script with:
SELECT
'ALTER TABLE ' || relname || ' SET WITHOUT OIDS;'
FROM
pg_class
WHERE
relkind='r' and
relowner != 1 and
relhasoids;

Before I pull the trigger, I figured I'd post and find out if anyone

sees any feet in the way.

That won't drop the OID columns.

Joshua D. Drake

Now I am confused. From the docs I get:

SET WITHOUT OIDS

This form removes the oid column from the table. Removing OIDs from a table does not occur immediately. The space that the OID uses will be reclaimed when the row is updated. Without updating the row, both the space and the value of the OID are kept indefinitely. This is semantically similar to the DROP COLUMN process.

I remember from past posts, that to get rid of the OIDS you can do a 'fake' update on the whole table to reclaim the space. The case the OP is dealing with he does not want the OID setting to propagate via the dump/restore cycle. The above statement would do that or am I mistaken?

Thanks,
Adrian Klaver
aklaver@comcast.net

"Joshua D. Drake" <jd@commandprompt.com> writes:

On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote:

My plan is to do the OID removal on 7.4. I can get a script with:
SELECT
'ALTER TABLE ' || relname || ' SET WITHOUT OIDS;'

That won't drop the OID columns.

Sure it will. I'd be a little worried about whether he shouldn't
be using quote_identifier and/or schema-qualifying the names, but
SET WITHOUT OIDS is the right command to be issuing.

regards, tom lane

On Wed, 2009-02-11 at 18:01 +0000, Adrian Klaver wrote:

----- "Joshua D. Drake" <jd@commandprompt.com> wrote:

Now I am confused. From the docs I get:

My bad. The docs are obviously correct. I think I was thinking about the
postgresql.conf option.

Joshua D. Drae

SET WITHOUT OIDS

This form removes the oid column from the table. Removing OIDs from a table does not occur immediately. The space that the OID uses will be reclaimed when the row is updated. Without updating the row, both the space and the value of the OID are kept indefinitely. This is semantically similar to the DROP COLUMN process.

I remember from past posts, that to get rid of the OIDS you can do a 'fake' update on the whole table to reclaim the space. The case the OP is dealing with he does not want the OID setting to propagate via the dump/restore cycle. The above statement would do that or am I mistaken?

Thanks,
Adrian Klaver
aklaver@comcast.net

--
PostgreSQL - XMPP: jdrake@jabber.postgresql.org
Consulting, Development, Support, Training
503-667-4564 - http://www.commandprompt.com/
The PostgreSQL Company, serving since 1997

Joshua D. Drake wrote:

On Wed, 2009-02-11 at 09:41 -0800, Steve Crawford wrote:

I'm about to deal with an upgrade of a server running 7.4. I have
checked with the developers and they are not using OIDs so I'd like to
remove them so they aren't carried forward to 8.3.

My plan is to do the OID removal on 7.4. I can get a script with:
SELECT
'ALTER TABLE ' || relname || ' SET WITHOUT OIDS;'
FROM
pg_class
WHERE
relkind='r' and
relowner != 1 and
relhasoids;
.....

That won't drop the OID columns.

So what am I missing, here?:

steve=> create table foo (bar text);
CREATE TABLE
steve=> alter table foo drop column OID;
ERROR: cannot drop system column "oid"
steve=> alter table foo set without OIDs;
ALTER TABLE
steve=> alter table foo drop column OID;
ERROR: column "oid" of relation "foo" does not exist

Although I assume a cluster would reclaim space, I don't actually care
if the space used by the OIDs is reclaimed in the 7.4 database as long
as the OIDs are not created when the data is restored in 8.3.

Cheers,
Steve

Tom Lane wrote:

...
I'd be a little worried about whether he shouldn't
be using quote_identifier and/or schema-qualifying the names, but
SET WITHOUT OIDS is the right command to be issuing.

It may not make any difference in this case, but for completeness and
correctness:

SELECT
'ALTER TABLE ' || quote_ident(n.nspname) || '.' ||
quote_ident(c.relname) || ' SET WITHOUT OIDS;'
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
AND pg_catalog.pg_table_is_visible(c.oid)
AND c.relhasoids
;

Cheers,
Steve

I wrote:

Sure it will. I'd be a little worried about whether he shouldn't
be using quote_identifier and/or schema-qualifying the names, but
SET WITHOUT OIDS is the right command to be issuing.

BTW, the lazy man's way to deal with both of those issues is to cast
the OID to regclass, ie the best way to handle this is

SELECT
'ALTER TABLE ' || oid::regclass || ' SET WITHOUT OIDS;'
FROM pg_class WHERE ...

Observe the following example:

regression=# create schema s1 create table "Foo"(f1 int);
CREATE SCHEMA
regression=# select max(oid)::regclass from pg_class;
max
----------
s1."Foo"
(1 row)

You can similarly use regprocedure, regoperator, etc to get
safely qualified names for functions, operators etc.

regards, tom lane

On Wed, Feb 11, 2009 at 10:41 AM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

I'm about to deal with an upgrade of a server running 7.4. I have checked
with the developers and they are not using OIDs so I'd like to remove them
so they aren't carried forward to 8.3.

My plan is to do the OID removal on 7.4. I can get a script with:
SELECT
'ALTER TABLE ' || relname || ' SET WITHOUT OIDS;'
FROM
pg_class
WHERE
relkind='r' and
relowner != 1 and
relhasoids;

Before I pull the trigger, I figured I'd post and find out if anyone sees
any feet in the way.

Since 8.3 creates tables WITHOUT OIDS by default, I'd think just
dumping from 7.4 to 8.3 with 8.3's pg_dump should do the trick. Or am
I missing something?

On 11/02/2009 19:40, Scott Marlowe wrote:

Since 8.3 creates tables WITHOUT OIDS by default, I'd think just
dumping from 7.4 to 8.3 with 8.3's pg_dump should do the trick. Or am
I missing something?

I could be wrong, but I think 8.3's pg_dump will append WITH (OIDS=TRUE)
if it finds tables with OIDs.

Ray.

------------------------------------------------------------------
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
rod@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
------------------------------------------------------------------

Raymond O'Donnell wrote:

Since 8.3 creates tables WITHOUT OIDS by default, I'd think just
dumping from 7.4 to 8.3 with 8.3's pg_dump should do the trick. Or am
I missing something?

I could be wrong, but I think 8.3's pg_dump will append WITH (OIDS=TRUE)
if it finds tables with OIDs.

Close. It actually does a "SET default_with_oids = true;" (or false)
prior to the CREATE TABLE statement. In any case, it does preserve the
OID setting of the source database.

Cheers,
Steve

Steve Crawford wrote:

Although I assume a cluster would reclaim space, I don't actually

care

if the space used by the OIDs is reclaimed in the 7.4 database as

long

as the OIDs are not created when the data is restored in 8.3.

I seems to me that pg_dump does it for you anyway, unless you ask
otherwise. See the -o option.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Daniel Verite wrote:

I seems to me that pg_dump does it for you anyway, unless you ask
otherwise. See the -o option.

No, as I understand it this does not influence whether or not the table
is recreated with OIDs, it determines whether the _values_ of the OIDs
are included in the dumped data. Without this option, new OIDs are
created on restore - not good if you expect them to remain unchanged
(foreign key or whatever).

Cheers,
Steve

On Wed, Feb 11, 2009 at 12:59 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

Raymond O'Donnell wrote:

Since 8.3 creates tables WITHOUT OIDS by default, I'd think just
dumping from 7.4 to 8.3 with 8.3's pg_dump should do the trick. Or am
I missing something?

I could be wrong, but I think 8.3's pg_dump will append WITH (OIDS=TRUE)
if it finds tables with OIDs.

Close. It actually does a "SET default_with_oids = true;" (or false) prior
to the CREATE TABLE statement. In any case, it does preserve the OID setting
of the source database.

So, I'd think it would be easier to just edit the backup than to muck
around in the 7.4 database.

Scott Marlowe wrote:

Close. It actually does a "SET default_with_oids = true;" (or false) prior
to the CREATE TABLE statement. In any case, it does preserve the OID setting
of the source database.

So, I'd think it would be easier to just edit the backup than to muck
around in the 7.4 database.

The query to generate the drop-OID script runs in a fraction of a second
and the drop-OID script itself takes a second or two and I'm done. The
alternative requires running many gigs through sed (multiple times since
I have to run preliminary tests).

Cheers,
Steve

On Thu, Feb 12, 2009 at 10:02 AM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:

Scott Marlowe wrote:

Close. It actually does a "SET default_with_oids = true;" (or false)
prior
to the CREATE TABLE statement. In any case, it does preserve the OID
setting
of the source database.

So, I'd think it would be easier to just edit the backup than to muck
around in the 7.4 database.

The query to generate the drop-OID script runs in a fraction of a second and
the drop-OID script itself takes a second or two and I'm done. The
alternative requires running many gigs through sed (multiple times since I
have to run preliminary tests).

I always dump schema and data separately when doing a migration like
this, so I don't have to do silly things like run many gigs through
sed to change one or two DDL lines. That way if something in my data
matches a change I'm making to my DDL, it won't get stomped on, or
vice versa. Also lets me work out schema issues separately and all
that.

OTOH, there are some issues with data that's got things like circular
references that can mess up a data / schema separate dump / restore.
There's lots of ways to skin this cat.