Thread: Killing OIDs

Killing OIDs

From
Steve Crawford
Date:
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.

Cheers,
Steve


Re: Killing OIDs

From
"Joshua D. Drake"
Date:
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


Re: Killing OIDs

From
Adrian Klaver
Date:
----- "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
thatthe OID uses will be reclaimed when the row is updated. Without updating the row, both the space and the value of
theOID 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
abovestatement would do that or am I mistaken? 


Thanks,
Adrian Klaver
aklaver@comcast.net


Re: Killing OIDs

From
Tom Lane
Date:
"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

Re: Killing OIDs

From
"Joshua D. Drake"
Date:
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
thatthe OID uses will be reclaimed when the row is updated. Without updating the row, both the space and the value of
theOID 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
abovestatement 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


Re: Killing OIDs

From
Steve Crawford
Date:
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


Re: Killing OIDs

From
Steve Crawford
Date:
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


Re: Killing OIDs

From
Tom Lane
Date:
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

Re: Killing OIDs

From
Scott Marlowe
Date:
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?

Re: Killing OIDs

From
Raymond O'Donnell
Date:
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
------------------------------------------------------------------

Re: Killing OIDs

From
Steve Crawford
Date:
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


Re: Killing OIDs

From
"Daniel Verite"
Date:
    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

Re: Killing OIDs

From
Steve Crawford
Date:
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


Re: Killing OIDs

From
Scott Marlowe
Date:
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.

Re: Killing OIDs

From
Steve Crawford
Date:
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


Re: Killing OIDs

From
Scott Marlowe
Date:
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.