Thread: Killing OIDs
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
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
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
"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 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
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.