Grokbase › Groups › PostgreSQL › pgsql-hackers › February 2009
FAQ
We have an open problem with CVS HEAD that ALTER TABLE SET WITHOUT OIDS
causes problems:
http://archives.postgresql.org/pgsql-hackers/2008-11/msg00332.php

I opined at the time that what we really have here is a table whose
tuples do not match its declared rowtype, and that the proper fix is
to make SET WITHOUT OIDS rewrite the table to physically get rid of
the OIDs. The attached patch (which lacks doc changes or regression
tests as yet) does that, and also adds the inverse SET WITH OIDS
operation to do what you'd expect, ie, add an OID column if it isn't
there already.

The major objection to this would probably be that SET WITHOUT OIDS has
historically been a "free" catalog-change operation, and now it will be
expensive on large tables. But given that we've deprecated OIDs in user
tables since 8.0, I think most people have been through that conversion
already, or have decided to keep their OIDs anyway. I don't think it's
worth taking a continuing risk of backend bugs in order to make life a
bit easier for any remaining laggards.

A different discussion is whether it's appropriate to put in SET WITH
OIDS now, when we're well past feature freeze. If we stripped that out
of this patch it'd save a few dozen lines of code, but I'm not really
seeing the point. The asymmetry of having SET WITHOUT and not SET WITH
has always been an implementation artifact anyway.

Comments?

regards, tom lane

Search Discussions

  • Andrew Dunstan at Feb 8, 2009 at 4:26 pm ⇧

    Tom Lane wrote:
    The attached patch (which lacks doc changes or regression
    tests as yet) does that, and also adds the inverse SET WITH OIDS
    operation to do what you'd expect, ie, add an OID column if it isn't
    there already.
    Why would we add an operation to implement a deprecated feature? That
    seems very strange.

    (I have no problem with making SET WITHOUT OIDS rewrite the table.)

    cheers

    andrew
  • Tom Lane at Feb 8, 2009 at 4:51 pm ⇧

    Andrew Dunstan writes:
    Tom Lane wrote:
    The attached patch (which lacks doc changes or regression
    tests as yet) does that, and also adds the inverse SET WITH OIDS
    operation to do what you'd expect, ie, add an OID column if it isn't
    there already.
    Why would we add an operation to implement a deprecated feature?
    Well, it was always intended to be that way:
    http://archives.postgresql.org/pgsql-patches/2002-12/msg00071.php

    The originally submitted patch didn't work very well
    http://archives.postgresql.org/pgsql-patches/2003-01/msg00011.php
    and what we ended up doing was applying just the SET WITHOUT OIDS half
    of it, but my feeling always was that that was for lack of round tuits
    rather than that it was a good place to be. Given the implementation
    at the time it would've taken a lot of extra code to do SET WITH OIDS,
    so nobody did get around to it. But subsequent changes in the ALTER
    code have made it possible to piggyback on ALTER ADD COLUMN easily ---
    which is what this patch is trying to demonstrate.

    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether, I'm not sure I could dispute it. But if we have the ability
    to do that ISTM we should offer the reverse too.

    regards, tom lane
  • Zdenek Kotala at Feb 8, 2009 at 7:10 pm ⇧

    Tom Lane píše v ne 08. 02. 2009 v 11:51 -0500:
    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether, I'm not sure I could dispute it. But if we have the
    ability to do that ISTM we should offer the reverse too.
    By my opinion TABLES with OIDs is obsolete feature. It make sense to
    have SET WITHOUT OIDS, because it is useful when people will migrate
    form 7.4 to 8.4. But opposite way does not make me sense, because I
    think we want to remove OID TABLES in the future. I personally prefer to
    say that 8.4 is last version which supports CREATE TABLE ... WITH OIDS.

    Zdenek
  • Tom Lane at Feb 8, 2009 at 7:27 pm ⇧

    Zdenek Kotala writes:
    Tom Lane píše v ne 08. 02. 2009 v 11:51 -0500:
    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether, I'm not sure I could dispute it. But if we have the
    ability to do that ISTM we should offer the reverse too.
    By my opinion TABLES with OIDs is obsolete feature. It make sense to
    have SET WITHOUT OIDS, because it is useful when people will migrate
    form 7.4 to 8.4. But opposite way does not make me sense, because I
    think we want to remove OID TABLES in the future. I personally prefer to
    say that 8.4 is last version which supports CREATE TABLE ... WITH OIDS.
    If we're going to do that we should do it *now*, not later, because
    right now is when we have a bug that we could actually save some effort
    on. In practice, since we have not ever suggested that we were actually
    going to remove the feature, I don't believe that we can do that. Not
    in 8.4, and not in 8.5 or any other near-future release either.

    The larger point though is that unless we restructure the system to the
    point of not using OIDs in system catalogs ... which ain't happening
    ... the amount of code we could save by removing OIDs for users is
    vanishingly small. Probably on the rough order of 100 lines, and about
    the same in documentation. (We couldn't, for instances, stop
    documenting that OIDs exist.) Doesn't really seem worth breaking
    applications for, even deprecated ones.

    regards, tom lane
  • Robert Haas at Feb 8, 2009 at 8:46 pm ⇧

    On Sun, Feb 8, 2009 at 11:51 AM, Tom Lane wrote:
    Andrew Dunstan <[email protected]> writes:
    Tom Lane wrote:
    The attached patch (which lacks doc changes or regression
    tests as yet) does that, and also adds the inverse SET WITH OIDS
    operation to do what you'd expect, ie, add an OID column if it isn't
    there already.
    Why would we add an operation to implement a deprecated feature?
    Well, it was always intended to be that way:
    http://archives.postgresql.org/pgsql-patches/2002-12/msg00071.php

    The originally submitted patch didn't work very well
    http://archives.postgresql.org/pgsql-patches/2003-01/msg00011.php
    and what we ended up doing was applying just the SET WITHOUT OIDS half
    of it, but my feeling always was that that was for lack of round tuits
    rather than that it was a good place to be. Given the implementation
    at the time it would've taken a lot of extra code to do SET WITH OIDS,
    so nobody did get around to it. But subsequent changes in the ALTER
    code have made it possible to piggyback on ALTER ADD COLUMN easily ---
    which is what this patch is trying to demonstrate.

    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether, I'm not sure I could dispute it. But if we have the ability
    to do that ISTM we should offer the reverse too.
    +1. I really hate it (in any application, not just PostgreSQL) when
    there's an option to add something but not delete it, delete it but
    not put it back, etc. Personally, *I* would not have spent the time
    to implement SET WITH OIDS, but since we now have the patch, I'm 100%
    in favor of applying it. Most likely, very few people will use it,
    but it doesn't cost us anything either, so I'm unclear why we would
    tell Tom to go back and rip that functionality back out of his patch.
    Sounds like bikeshedding to me.

    ...Robert
  • David Fetter at Feb 8, 2009 at 11:12 pm ⇧

    On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote:

    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether,
    +1 for removing it altogether. Row OIDs are and ugly wart :P

    Cheers,
    David.
    --
    David Fetter <[email protected]> http://fetter.org/
    Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
    Skype: davidfetter XMPP: [email protected]

    Remember to vote!
    Consider donating to Postgres: http://www.postgresql.org/about/donate
  • Greg Stark at Feb 9, 2009 at 12:02 am ⇧
    I don't understand what's wrong with the existing setup where DROP
    OIDS is a free operation. And the space is cleaned up later when the
    tuple is next written.

    It seems exactly equivalent to how we handle DROP COLUMN where the
    natt field of the tuple disagrees with the tuple descriptor and any
    additional columns are implicitly null.

    --
    Greg

    On 8 Feb 2009, at 23:12, David Fetter wrote:
    On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote:

    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether,
    +1 for removing it altogether. Row OIDs are and ugly wart :P

    Cheers,
    David.
    --
    David Fetter <[email protected]> http://fetter.org/
    Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
    Skype: davidfetter XMPP: [email protected]

    Remember to vote!
    Consider donating to Postgres: http://www.postgresql.org/about/donate

    --
    Sent via pgsql-hackers mailing list ([email protected])
    To make changes to your subscription:
    http://www.postgresql.org/mailpref/pgsql-hackers
  • Tom Lane at Feb 9, 2009 at 6:32 am ⇧

    Greg Stark writes:
    I don't understand what's wrong with the existing setup where DROP
    OIDS is a free operation.
    It breaks things, in particular
    http://archives.postgresql.org/pgsql-hackers/2008-11/msg00332.php
    We could kluge around that particular problem, but the objection
    I have to doing so is I'm 100% certain it won't be the last such bug.
    It seems exactly equivalent to how we handle DROP COLUMN
    It is just about exactly *unlike* DROP COLUMN, because in DROP COLUMN
    we retain a memory that there used to be a column there. A close
    emulation of DROP COLUMN would involve inventing some representation of
    "oidisdropped", and going through every one of the multitudinous places
    that special-case dropped columns in order to see if each one needs a
    similar special case for dropped OIDs. The bug mentioned above stems
    directly from not expecting a table to still contain OIDs after SET
    WITHOUT OIDS, so I don't think this parallel is mistaken.

    Note that I'm willing to lay a significant side bet that we still have
    bugs of omission with dropped columns, too. But we'll fix those as we
    come to them. I don't think it is worth making a similar open-ended
    commitment of resources just to keep SET WITHOUT OIDS fast.
    ... where the
    natt field of the tuple disagrees with the tuple descriptor and any
    additional columns are implicitly null.
    No, that's the mechanism that makes ADD COLUMN feasible (and indeed
    pretty easy). DROP COLUMN is the far newer and uglier mess around
    attisdropped.

    regards, tom lane
  • Greg Stark at Feb 9, 2009 at 10:39 pm ⇧
    Sorry, I was indeed thinking of newly added columns rather than
    dropped columns. We define the row representation such that one may
    have fewer rows than the tupledesc and how to interpret that in such a
    way as to make adding nullable columns a convenient operation.

    How is doing the same here and fixing a case where we weren't
    following the definition any more of a kludge than how we handle newly
    added columns? Which incidentally I don't think is at all kludgy.

    I think what you propose would be a mistake. We want to encourage
    people to move *away* from OIDS. - making drop kids prohibitively
    expensive and adding an operation to add kids which we hope nobody
    needs seems like heading in the wrong direction.

    Sorry for top posting - in this case i'm using google mail's mobile
    interface but it's no better about this and makes properly threading
    responses nigh impossible.


    On 2009-02-09, Tom Lane wrote:
    Greg Stark <[email protected]> writes:
    I don't understand what's wrong with the existing setup where DROP
    OIDS is a free operation.
    It breaks things, in particular
    http://archives.postgresql.org/pgsql-hackers/2008-11/msg00332.php
    We could kluge around that particular problem, but the objection
    I have to doing so is I'm 100% certain it won't be the last such bug.
    It seems exactly equivalent to how we handle DROP COLUMN
    It is just about exactly *unlike* DROP COLUMN, because in DROP COLUMN
    we retain a memory that there used to be a column there. A close
    emulation of DROP COLUMN would involve inventing some representation of
    "oidisdropped", and going through every one of the multitudinous places
    that special-case dropped columns in order to see if each one needs a
    similar special case for dropped OIDs. The bug mentioned above stems
    directly from not expecting a table to still contain OIDs after SET
    WITHOUT OIDS, so I don't think this parallel is mistaken.

    Note that I'm willing to lay a significant side bet that we still have
    bugs of omission with dropped columns, too. But we'll fix those as we
    come to them. I don't think it is worth making a similar open-ended
    commitment of resources just to keep SET WITHOUT OIDS fast.
    ... where the
    natt field of the tuple disagrees with the tuple descriptor and any
    additional columns are implicitly null.
    No, that's the mechanism that makes ADD COLUMN feasible (and indeed
    pretty easy). DROP COLUMN is the far newer and uglier mess around
    attisdropped.

    regards, tom lane

    --
    greg
  • Tom Lane at Feb 9, 2009 at 11:49 pm ⇧

    Greg Stark writes:
    I think what you propose would be a mistake. We want to encourage
    people to move *away* from OIDS.
    Why? I don't agree with that premise, and therefore not with any
    of the rest of your argument.

    regards, tom lane
  • Greg Stark at Feb 12, 2009 at 11:57 am ⇧
    Well for one thing because they don't scale well to billions of
    records. For another they're even less like the standard or anything
    any other database has.

    I agree with you that there's no reason to actively deprecate OIDs or
    hurt users who use them. But we should make it as easy as possible for
    users who want to move to a normal primary key, not put obstacles in
    their way like large full table rewrites.

    --
    Greg

    On 10 Feb 2009, at 01:49, Tom Lane wrote:

    Greg Stark <[email protected]> writes:
    I think what you propose would be a mistake. We want to encourage
    people to move *away* from OIDS.
    Why? I don't agree with that premise, and therefore not with any
    of the rest of your argument.

    regards, tom lane
  • Andrew Dunstan at Feb 9, 2009 at 2:32 am ⇧

    David Fetter wrote:
    On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote:

    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether,
    +1 for removing it altogether. Row OIDs are and ugly wart :P

    That might be true but I know of apps that use them. Having the ability
    to migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm).

    cheers

    andrew
  • Marko Kreen at Feb 9, 2009 at 12:47 pm ⇧

    On 2/9/09, Andrew Dunstan wrote:
    David Fetter wrote:
    On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote:
    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether,
    +1 for removing it altogether. Row OIDs are and ugly wart :P
    That might be true but I know of apps that use them. Having the ability to
    migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm).
    +1 for removal.

    Also, whether the removal happens or not, I would suggest a setting that
    makes Postgres accept, but ignore default_with_oids / WITH OIDS settings.

    The problem is how to migrate apps that definitely do not use oids,
    in a situation where you have hundred of databases.

    Scanning all dbs and doing ALTER table would be option, if it would
    work 100% and would not touch data. Otherwise is cannot be used.

    Trying to manually manipulate dump files which are filled with
    "SET default_with_oids" each time database is dumped/reloaded is also
    not an option.

    Currently the only sane path seems to patch Postgres to ignore the
    settings, but that does not seem very user-friendly approach...

    --
    marko
  • Martijn van Oosterhout at Feb 9, 2009 at 12:55 pm ⇧

    On Mon, Feb 09, 2009 at 02:47:21PM +0200, Marko Kreen wrote:
    That might be true but I know of apps that use them. Having the ability to
    migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm).
    +1 for removal.

    Also, whether the removal happens or not, I would suggest a setting that
    makes Postgres accept, but ignore default_with_oids / WITH OIDS settings.
    Err, you mean a setting that makes Postgres throw an error on the use
    of WITH OIDS. Just silently ignoring the option is a fantastic way to
    break applications silently.

    Making pg_dump not output the WITH OIDS option on tables may be an
    easier option.

    Have a nice day,
    --
    Martijn van Oosterhout <[email protected]> http://svana.org/kleptog/
    Please line up in a tree and maintain the heap invariant while
    boarding. Thank you for flying nlogn airlines.
  • Heikki Linnakangas at Feb 9, 2009 at 1:06 pm ⇧

    Martijn van Oosterhout wrote:
    Making pg_dump not output the WITH OIDS option on tables may be an
    easier option.
    Or just run ALTER TABLE WITHOUT OIDS for all the tables before dumping.

    --
    Heikki Linnakangas
    EnterpriseDB http://www.enterprisedb.com
  • Marko Kreen at Feb 9, 2009 at 1:21 pm ⇧

    On 2/9/09, Heikki Linnakangas wrote:
    Martijn van Oosterhout wrote:
    Making pg_dump not output the WITH OIDS option on tables may be an
    easier option.
    Or just run ALTER TABLE WITHOUT OIDS for all the tables before dumping.
    This does not work on dbs that are actually in use...

    --
    marko
  • Marko Kreen at Feb 9, 2009 at 1:20 pm ⇧

    On 2/9/09, Martijn van Oosterhout wrote:
    On Mon, Feb 09, 2009 at 02:47:21PM +0200, Marko Kreen wrote:
    That might be true but I know of apps that use them. Having the ability to
    migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm).
    +1 for removal.

    Also, whether the removal happens or not, I would suggest a setting that
    makes Postgres accept, but ignore default_with_oids / WITH OIDS settings.
    Err, you mean a setting that makes Postgres throw an error on the use
    of WITH OIDS. Just silently ignoring the option is a fantastic way to
    break applications silently.
    For me, ignoring is easier... But yeah, error would be better,
    if it does not affect reloading the dump.
    Making pg_dump not output the WITH OIDS option on tables may be an
    easier option.
    I don't like it - it would require more work from users, but does
    not seem to be any way safer. You usually do the check if db works
    on restore time, not dump time...

    From clarity standpoint, options that turns both default_with_oids
    and WITH OIDS to errors seems the best.

    --
    marko
  • Martijn van Oosterhout at Feb 9, 2009 at 1:38 pm ⇧

    On Mon, Feb 09, 2009 at 03:19:55PM +0200, Marko Kreen wrote:
    Making pg_dump not output the WITH OIDS option on tables may be an
    easier option.
    I don't like it - it would require more work from users, but does
    not seem to be any way safer. You usually do the check if db works
    on restore time, not dump time...
    Another idea, have WITH OIDS just append a column to the table called
    OID with SERIAL type. People see them, go "whoops" and drop them.

    Have a nice day,
    --
    Martijn van Oosterhout <[email protected]> http://svana.org/kleptog/
    Please line up in a tree and maintain the heap invariant while
    boarding. Thank you for flying nlogn airlines.
  • Marko Kreen at Feb 9, 2009 at 1:34 pm ⇧

    On 2/9/09, Martijn van Oosterhout wrote:
    Making pg_dump not output the WITH OIDS option on tables may be an
    easier option.
    OTOH, the pg_dump already has option --oids. If the option is not given,
    is there any point putting WITH OIDS / default_with_oids into dump?

    --
    marko
  • Robert Haas at Feb 9, 2009 at 2:09 pm ⇧

    On Feb 9, 2009, at 7:47 AM, Marko Kreen wrote:
    On 2/9/09, Andrew Dunstan wrote:
    David Fetter wrote:
    On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote:
    Now, if you want to argue that we should get rid of SET WITHOUT
    OIDS
    altogether,
    +1 for removing it altogether. Row OIDs are and ugly wart :P
    That might be true but I know of apps that use them. Having the
    ability to
    migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm).
    +1 for removal.
    Why? What benefit do we get out of denying users this option?
    Also, whether the removal happens or not, I would suggest a setting
    that
    makes Postgres accept, but ignore default_with_oids / WITH OIDS
    settings.

    The problem is how to migrate apps that definitely do not use oids,
    in a situation where you have hundred of databases.

    Scanning all dbs and doing ALTER table would be option, if it would
    work 100% and would not touch data. Otherwise is cannot be used.
    That might be true in your environment, but is certainly not true in
    general. We have many DDL commands that require full-table rewrites,
    and they are FAR from useless.
    Trying to manually manipulate dump files which are filled with
    "SET default_with_oids" each time database is dumped/reloaded is also
    not an option.

    Currently the only sane path seems to patch Postgres to ignore the
    settings, but that does not seem very
    ...Robert
  • Marko Kreen at Feb 9, 2009 at 2:41 pm ⇧

    On 2/9/09, Robert Haas wrote:
    On Feb 9, 2009, at 7:47 AM, Marko Kreen wrote:
    On 2/9/09, Andrew Dunstan wrote:
    David Fetter wrote:
    On Sun, Feb 08, 2009 at 11:51:22AM -0500, Tom Lane wrote:

    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether,
    +1 for removing it altogether. Row OIDs are and ugly wart :P
    That might be true but I know of apps that use them. Having the ability
    to
    migrate those slowly by using SET WITHOUT OIDS is a Good Thing (tm).
    +1 for removal.
    Why? What benefit do we get out of denying users this option?
    Why should we continue to support historical special case? It is not
    a feature that adds anything to user experience with Postgres.

    Anyway, that was my vote only. If there are developers interested
    in supporting oids feel free to do so.

    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently. Removing them completely is simplest path, but adding
    extra features to support it is another.

    If we are talking about adding a feature, then I like retargeting
    pg_dump --oids from data-only flag to apply to both data and schema.
    Yes, this is incompatible change, but the change affects feature we
    are discouraging anyway.

    If this does not work, then we need another postgresql.conf option.
    Also, whether the removal happens or not, I would suggest a setting that
    makes Postgres accept, but ignore default_with_oids / WITH OIDS settings.

    The problem is how to migrate apps that definitely do not use oids,
    in a situation where you have hundred of databases.

    Scanning all dbs and doing ALTER table would be option, if it would
    work 100% and would not touch data. Otherwise is cannot be used.
    That might be true in your environment, but is certainly not true in
    general. We have many DDL commands that require full-table rewrites, and
    they are FAR from useless.
    Compared to not having the DDL commands or having DDL commands that
    do not rewrite the tables? ;)

    --
    marko
  • Andrew Dunstan at Feb 9, 2009 at 3:44 pm ⇧

    Marko Kreen wrote:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently. Removing them completely is simplest path, but adding
    extra features to support it is another.

    If we are talking about adding a feature, then I like retargeting
    pg_dump --oids from data-only flag to apply to both data and schema.
    Yes, this is incompatible change, but the change affects feature we
    are discouraging anyway.
    How about a pg_dump flag that simply suppresses OIDs from the data and
    schema?

    cheers

    andrew
  • Marko Kreen at Feb 9, 2009 at 3:49 pm ⇧

    On 2/9/09, Andrew Dunstan wrote:
    Marko Kreen wrote:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big
    annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently. Removing them completely is simplest path, but adding
    extra features to support it is another.

    If we are talking about adding a feature, then I like retargeting
    pg_dump --oids from data-only flag to apply to both data and schema.
    Yes, this is incompatible change, but the change affects feature we
    are discouraging anyway.
    How about a pg_dump flag that simply suppresses OIDs from the data and
    schema?
    But we already have flag that is correlated to use of oids?

    I don't see why we should bother users who are not using oids with it.

    --
    marko
  • David Fetter at Feb 9, 2009 at 3:50 pm ⇧

    On Mon, Feb 09, 2009 at 10:44:17AM -0500, Andrew Dunstan wrote:
    Marko Kreen wrote:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes
    bugs or requires table rewrite, it turned from minor annoyance to
    big annoyance. So I'd like have a reasonable path for getting rid
    of them, which we don't have currently. Removing them completely
    is simplest path, but adding extra features to support it is
    another.

    If we are talking about adding a feature, then I like retargeting
    pg_dump --oids from data-only flag to apply to both data and
    schema. Yes, this is incompatible change, but the change affects
    feature we are discouraging anyway.
    How about a pg_dump flag that simply suppresses OIDs from the data
    and schema?
    Defaults matter. How about one that *preserves* the aforementioned
    OIDs and have the default, if it finds OIDs, error out with a message
    like this:

    You have explicit OIDs in this database, which have been
    deprecated since 8.1. If despite this, you would like to preserve
    them, use the --oids option for pg_dump.

    Cheers,
    David.
    --
    David Fetter <[email protected]> http://fetter.org/
    Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
    Skype: davidfetter XMPP: [email protected]

    Remember to vote!
    Consider donating to Postgres: http://www.postgresql.org/about/donate
  • Marko Kreen at Feb 9, 2009 at 3:55 pm ⇧

    On 2/9/09, David Fetter wrote:
    On Mon, Feb 09, 2009 at 10:44:17AM -0500, Andrew Dunstan wrote:
    Marko Kreen wrote:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes
    bugs or requires table rewrite, it turned from minor annoyance to
    big annoyance. So I'd like have a reasonable path for getting rid
    of them, which we don't have currently. Removing them completely
    is simplest path, but adding extra features to support it is
    another.

    If we are talking about adding a feature, then I like retargeting
    pg_dump --oids from data-only flag to apply to both data and
    schema. Yes, this is incompatible change, but the change affects
    feature we are discouraging anyway.
    How about a pg_dump flag that simply suppresses OIDs from the data
    and schema?

    Defaults matter. How about one that *preserves* the aforementioned
    OIDs and have the default, if it finds OIDs, error out with a message
    like this:

    You have explicit OIDs in this database, which have been
    deprecated since 8.1. If despite this, you would like to preserve
    them, use the --oids option for pg_dump.
    +1 for the warning.

    If --oids is not given, do the check. I would argue that the check
    should also see if there is index on the oid field, if not it's unusable
    anyway. So mosts users who have oid columns because of migration
    from older version, won't be bothered.

    Or can the oid column be usable without index?

    --
    marko
  • Heikki Linnakangas at Feb 9, 2009 at 3:55 pm ⇧

    Andrew Dunstan wrote:
    How about a pg_dump flag that simply suppresses OIDs from the data and
    schema?
    pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- &/'

    --
    Heikki Linnakangas
    EnterpriseDB http://www.enterprisedb.com
  • Andrew Dunstan at Feb 9, 2009 at 4:08 pm ⇧

    Heikki Linnakangas wrote:
    Andrew Dunstan wrote:
    How about a pg_dump flag that simply suppresses OIDs from the data
    and schema?
    pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- &/'
    No good for non-text dumps.

    cheers

    andrew
  • Heikki Linnakangas at Feb 9, 2009 at 4:26 pm ⇧

    Andrew Dunstan wrote:
    Heikki Linnakangas wrote:
    Andrew Dunstan wrote:
    How about a pg_dump flag that simply suppresses OIDs from the data
    and schema?
    pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- &/'
    No good for non-text dumps.
    *shrug*, create a text dump then.

    --
    Heikki Linnakangas
    EnterpriseDB http://www.enterprisedb.com
  • Tom Lane at Feb 9, 2009 at 4:32 pm ⇧

    Andrew Dunstan writes:
    Heikki Linnakangas wrote:
    Andrew Dunstan wrote:
    How about a pg_dump flag that simply suppresses OIDs from the data
    and schema?
    pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- &/'
    No good for non-text dumps.
    Also it would fail badly if the dump had in fact been made with -o.

    Currently there are two behaviors in pg_dump:

    1. With -o: preserve both the existence of oid columns and their
    exact contents

    2. Without -o: preserve the existence of oid columns, but don't
    worry about duplicating their contents (default).

    It might be worth extending the switch to provide a third option
    to get rid of oid columns altogether, but I'm really not convinced
    that this is better than suggesting that people run ALTER SET WITHOUT
    OIDS on all their tables.

    regards, tom lane
  • Heikki Linnakangas at Feb 9, 2009 at 4:41 pm ⇧

    Tom Lane wrote:
    Andrew Dunstan <[email protected]> writes:
    Heikki Linnakangas wrote:
    Andrew Dunstan wrote:
    How about a pg_dump flag that simply suppresses OIDs from the data
    and schema?
    pg_dump -s postgres | sed -e 's/SET default_with_oids = true;/-- &/'
    No good for non-text dumps.
    Also it would fail badly if the dump had in fact been made with -o.
    Don't do that then. We're not talking about filtering any old dump you
    have lying around. We're talking about adding a new flag to pg_dump. If
    you can run pg_dump with a new flag, surely you can run it without -o in
    text mode and use sed just as well.

    --
    Heikki Linnakangas
    EnterpriseDB http://www.enterprisedb.com
  • Tom Lane at Feb 9, 2009 at 4:13 pm ⇧

    Marko Kreen writes:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently.
    We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't
    applied it in all that time either does not care, or actually needs the
    OIDs and will be unhappy if we arbitrarily remove the feature.

    regards, tom lane
  • Marko Kreen at Feb 9, 2009 at 4:36 pm ⇧

    On 2/9/09, Tom Lane wrote:
    Marko Kreen <[email protected]> writes:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently.
    We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't
    applied it in all that time either does not care, or actually needs the
    OIDs and will be unhappy if we arbitrarily remove the feature.
    Sure I did not care. Because I thought I can get rid of them
    anytime I wanted. But it seems it's not the case...

    We've set default_with_oids = false, for quite a long time. But there
    are still tables remaining with oids. And this discussion showed it
    now easy to get rid of them.

    I can patch Postgres myself, but I was thinking maybe others want also
    some solution.

    --
    marko
  • Heikki Linnakangas at Feb 9, 2009 at 4:43 pm ⇧

    Marko Kreen wrote:
    We've set default_with_oids = false, for quite a long time. But there
    are still tables remaining with oids. And this discussion showed it
    now easy to get rid of them.
    Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before
    upgrading to 8.4, while it's still fast. If yes, you couldn't use the
    option to remove them at pg_dump anyway because you still need them
    after the upgrade.

    --
    Heikki Linnakangas
    EnterpriseDB http://www.enterprisedb.com
  • Marko Kreen at Feb 9, 2009 at 4:56 pm ⇧

    On 2/9/09, Heikki Linnakangas wrote:
    Marko Kreen wrote:
    We've set default_with_oids = false, for quite a long time. But there
    are still tables remaining with oids. And this discussion showed it
    now easy to get rid of them.
    Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before
    upgrading to 8.4, while it's still fast. If yes, you couldn't use the option
    to remove them at pg_dump anyway because you still need them after the
    upgrade.
    Indeed. I must apologize. I seems I read too fast and got the impression
    the bug applies also to older versions of Postgres. If this is not
    the case and ALTER still works fine on older versions, most of my comments
    do not apply, because indeed, we can clean it up on 8.3.

    There is still minor problem that it will be made expensive on 8.4, but
    as it is not released yet, it can be solved by advising users to clean
    up their tables on 8.3.

    --
    marko
  • Tom Lane at Feb 9, 2009 at 5:11 pm ⇧

    Marko Kreen writes:
    On 2/9/09, Heikki Linnakangas wrote:
    Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before
    upgrading to 8.4, while it's still fast. If yes, you couldn't use the option
    to remove them at pg_dump anyway because you still need them after the
    upgrade.
    Indeed. I must apologize. I seems I read too fast and got the impression
    the bug applies also to older versions of Postgres. If this is not
    the case and ALTER still works fine on older versions, most of my comments
    do not apply, because indeed, we can clean it up on 8.3.
    I think actually we are in violent agreement ;-). The argument for
    getting rid of userland OIDs, as far as I can see, is to eliminate
    future development effort and risk of bugs associated with them.
    Now if OIDs are staying in system tables ... which they are, for the
    foreseeable future ... then the only real cost or risk associated with
    userland OIDs is driven precisely by ALTER SET WITHOUT OIDS. Because
    that creates a situation with a table that used to have OIDs and no
    longer does, except there are still vestiges of its having OIDs, ie rows
    in the table that contain an OID. So the patch I'm proposing attacks
    that problem directly by making sure there is no intermediate status.
    Either a table has OIDS (and so do all its rows) or not (and none of
    its rows do either). I think this pretty much eliminates the risk of
    induced bugs, and it does it without taking away functionality that
    applications might depend on.

    Unless you want to argue that "SET WITHOUT OIDS is fast" is a property
    that apps are depending on, but that seems like a bit of a stretch.

    regards, tom lane
  • Marko Kreen at Feb 9, 2009 at 5:42 pm ⇧

    On 2/9/09, Tom Lane wrote:
    Marko Kreen <[email protected]> writes:
    On 2/9/09, Heikki Linnakangas wrote:
    Do you still need the oids? If not, run ALTER TABLE WITHOUT OIDS before
    upgrading to 8.4, while it's still fast. If yes, you couldn't use the option
    to remove them at pg_dump anyway because you still need them after the
    upgrade.
    Indeed. I must apologize. I seems I read too fast and got the impression
    the bug applies also to older versions of Postgres. If this is not
    the case and ALTER still works fine on older versions, most of my comments
    do not apply, because indeed, we can clean it up on 8.3.

    I think actually we are in violent agreement ;-). The argument for
    getting rid of userland OIDs, as far as I can see, is to eliminate
    future development effort and risk of bugs associated with them.
    Now if OIDs are staying in system tables ... which they are, for the
    foreseeable future ... then the only real cost or risk associated with
    userland OIDs is driven precisely by ALTER SET WITHOUT OIDS. Because
    that creates a situation with a table that used to have OIDs and no
    longer does, except there are still vestiges of its having OIDs, ie rows
    in the table that contain an OID. So the patch I'm proposing attacks
    that problem directly by making sure there is no intermediate status.
    Either a table has OIDS (and so do all its rows) or not (and none of
    its rows do either). I think this pretty much eliminates the risk of
    induced bugs, and it does it without taking away functionality that
    applications might depend on.
    Yes. I agree with the patch. And I'm all for robustness.
    Unless you want to argue that "SET WITHOUT OIDS is fast" is a property
    that apps are depending on, but that seems like a bit of a stretch.
    No. I'm not concerned with ALTER command, I'm concerned about reloading
    dumps from older versions. So my, uh, new argument is - starting with 8.4,
    it is very hard to get rid of oids on user tables because all the tools
    work against user.

    So either: the 8.4 will be a "flag day" and all users need to clean up
    their database on 8.3, or we give some option for them to lessen the pain.

    Considering that default_with_oids went false in 8.1 (?), affected are
    users who are reusing their dumps or postgresql.conf from 8.0 and below.

    Maybe there are not many of such users (?) so flag day approach it ok.

    --
    marko
  • Tom Lane at Feb 9, 2009 at 6:03 pm ⇧

    Marko Kreen writes:
    No. I'm not concerned with ALTER command, I'm concerned about reloading
    dumps from older versions. So my, uh, new argument is - starting with 8.4,
    it is very hard to get rid of oids on user tables because all the tools
    work against user.
    That's a pretty overstated claim. It's exactly the same tool as before,
    it's just slower.
    So either: the 8.4 will be a "flag day" and all users need to clean up
    their database on 8.3, or we give some option for them to lessen the pain.
    Considering that default_with_oids went false in 8.1 (?), affected are
    users who are reusing their dumps or postgresql.conf from 8.0 and below.
    Indeed. If they have not bothered to remove oids from their tables up
    to now, what are the odds that they're going to bother in the future?

    IMHO, the only way they'd care is if we try to force them to care
    (ie by removing oids as a user option), which I'm against. So I see
    no flag day here. They'll still have oids and they still won't care.

    regards, tom lane
  • Andrew Dunstan at Feb 9, 2009 at 6:28 pm ⇧

    Tom Lane wrote:
    Considering that default_with_oids went false in 8.1 (?), affected are
    users who are reusing their dumps or postgresql.conf from 8.0 and below.
    No, they have upgraded along the way. pg_dump carefully preserves the
    with/without oids property of the tables it is dumping. And rightly so.
    This has nothing to do with default_without_oids.
    Indeed. If they have not bothered to remove oids from their tables up
    to now, what are the odds that they're going to bother in the future?

    IMHO, the only way they'd care is if we try to force them to care
    (ie by removing oids as a user option), which I'm against. So I see
    no flag day here. They'll still have oids and they still won't care.


    I have clients I have not yet managed to ween off oids, because they
    have legacy apps, sometimes third party apps, that rely on them. I don't
    want to make it any harder to get them over the hurdle.

    cheers

    andrew
  • Tom Lane at Feb 9, 2009 at 7:00 pm ⇧

    Andrew Dunstan writes:
    I have clients I have not yet managed to ween off oids, because they
    have legacy apps, sometimes third party apps, that rely on them. I don't
    want to make it any harder to get them over the hurdle.
    Surely the major cost there is going to be fixing those apps; I think
    focusing on whether SET WITHOUT OIDS is zero-cost is worrying about
    entirely the wrong thing.

    Also, if they are using the oids (and presumably relying on them to be
    unique), the tables can't be as huge as all that --- they'd have to be
    under a billion or so rows, else the 32-bit width of oids would have
    forced a change a long time ago. So even a rewriting form of SET WITHOUT
    OIDS doesn't seem all that painful. Compared to an app migration that's
    still not happened after N years, I can't believe it's a problem.

    regards, tom lane
  • Tom Lane at Feb 9, 2009 at 4:44 pm ⇧

    Marko Kreen writes:
    On 2/9/09, Tom Lane wrote:
    We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't
    applied it in all that time either does not care, or actually needs the
    OIDs and will be unhappy if we arbitrarily remove the feature.
    Sure I did not care. Because I thought I can get rid of them
    anytime I wanted. But it seems it's not the case...
    Sure, you can still get rid of them, because SET WITHOUT OIDS isn't
    going away. It will be a bit more expensive than it used to be, but
    if you've not applied it before migrating to 8.4, that very strongly
    suggests that you don't care about getting rid of oids anyhow.

    The other half of this thread seems to be pointed in the direction
    of *forcing* users to get rid of oids, which is not happening as far
    as I'm concerned. It'd be breaking stuff to no purpose. I've been
    known to vote for breaking apps when there was a purpose to it
    (eg tightening implicit coercions) but removing the ability to have
    oids in user tables wouldn't buy us anything meaningful.

    regards, tom lane
  • Robert Haas at Feb 9, 2009 at 4:50 pm ⇧

    On Mon, Feb 9, 2009 at 11:36 AM, Marko Kreen wrote:
    On 2/9/09, Tom Lane wrote:
    Marko Kreen <[email protected]> writes:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently.
    We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't
    applied it in all that time either does not care, or actually needs the
    OIDs and will be unhappy if we arbitrarily remove the feature.
    Sure I did not care. Because I thought I can get rid of them
    anytime I wanted. But it seems it's not the case...

    We've set default_with_oids = false, for quite a long time. But there
    are still tables remaining with oids. And this discussion showed it
    now easy to get rid of them.

    I can patch Postgres myself, but I was thinking maybe others want also
    some solution.
    I must be missing something. Why would you need to patch PostgreSQL
    and how would it help you if you did?

    ...Robert
  • Marko Kreen at Feb 9, 2009 at 5:02 pm ⇧

    On 2/9/09, Robert Haas wrote:
    On Mon, Feb 9, 2009 at 11:36 AM, Marko Kreen wrote:
    On 2/9/09, Tom Lane wrote:
    Marko Kreen <[email protected]> writes:
    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently.
    We've had SET WITHOUT OIDS since 7.3 or thereabouts. Anybody who hasn't
    applied it in all that time either does not care, or actually needs the
    OIDs and will be unhappy if we arbitrarily remove the feature.
    Sure I did not care. Because I thought I can get rid of them
    anytime I wanted. But it seems it's not the case...

    We've set default_with_oids = false, for quite a long time. But there
    are still tables remaining with oids. And this discussion showed it
    now easy to get rid of them.

    I can patch Postgres myself, but I was thinking maybe others want also
    some solution.

    I must be missing something. Why would you need to patch PostgreSQL
    and how would it help you if you did?
    We use dumps to move db's around and they contain lot of
    SET default_with_oids that the pg_dump happily puts there.
    Remembering to filter them out each time a database is created
    does not work.

    So it would be good if we can use such dump, but receiving
    Postgres would ignore any requests to create tables with oids.

    --
    marko
  • Robert Haas at Feb 9, 2009 at 4:32 pm ⇧

    Why? What benefit do we get out of denying users this option?
    Why should we continue to support historical special case? It is not
    a feature that adds anything to user experience with Postgres.

    Anyway, that was my vote only. If there are developers interested
    in supporting oids feel free to do so.

    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently. Removing them completely is simplest path, but adding
    extra features to support it is another.
    Well, see Tom's point upthread: OIDs are extensively used for system
    tables, and are not going away. So this is a pipe dream. In the
    meantime, ALTER TABLE WITHOUT OIDS is (at least for some people) an
    easier migration path than dump+reload.
    That might be true in your environment, but is certainly not true in
    general. We have many DDL commands that require full-table rewrites, and
    they are FAR from useless.
    Compared to not having the DDL commands or having DDL commands that
    do not rewrite the tables? ;)
    Not having them, of course.

    If we remove ALTER TABLE WITHOUT OIDS, it's going to encourage people
    to do stuff like this:

    CREATE TABLE blah_without_oids AS SELECT * FROM blah;
    ALTER TABLE blah ...
    ALTER TABLE blah ...
    -- move foreign keys, constraints, etc.
    DROP TABLE blah;
    ALTER TABLE blah_without_oids RENAME TO blah;

    ...or else dump+reload. ISTM that if anything that's going to
    encourage people to keep the OIDs in there because it's too much work
    to get rid of them.

    ...Robert
  • Marko Kreen at Feb 9, 2009 at 4:43 pm ⇧

    On 2/9/09, Robert Haas wrote:
    Why? What benefit do we get out of denying users this option?
    Why should we continue to support historical special case? It is not
    a feature that adds anything to user experience with Postgres.

    Anyway, that was my vote only. If there are developers interested
    in supporting oids feel free to do so.

    But now that I learned that ALTER TABLE WITHOUT OIDS either causes bugs
    or requires table rewrite, it turned from minor annoyance to big annoyance.
    So I'd like have a reasonable path for getting rid of them, which we don't
    have currently. Removing them completely is simplest path, but adding
    extra features to support it is another.

    Well, see Tom's point upthread: OIDs are extensively used for system
    tables, and are not going away. So this is a pipe dream. In the
    meantime, ALTER TABLE WITHOUT OIDS is (at least for some people) an
    easier migration path than dump+reload.
    Sorry, I was talking only about oids in user tables.
    That might be true in your environment, but is certainly not true in
    general. We have many DDL commands that require full-table rewrites, and
    they are FAR from useless.
    Compared to not having the DDL commands or having DDL commands that
    do not rewrite the tables? ;)

    Not having them, of course.

    If we remove ALTER TABLE WITHOUT OIDS, it's going to encourage people
    to do stuff like this:

    CREATE TABLE blah_without_oids AS SELECT * FROM blah;
    ALTER TABLE blah ...
    ALTER TABLE blah ...
    -- move foreign keys, constraints, etc.
    DROP TABLE blah;
    ALTER TABLE blah_without_oids RENAME TO blah;

    ...or else dump+reload. ISTM that if anything that's going to
    encourage people to keep the OIDs in there because it's too much work
    to get rid of them.
    By "removing" I mean that in version 8.6 you simply cannot create user
    table with oids. Thus no need to get rid of them.

    If we keep the possibility to create tables with oids, obviously
    the ALTER, etc command must also be kept.

    --
    marko
  • Simon Riggs at Feb 10, 2009 at 10:56 am ⇧

    On Sun, 2009-02-08 at 11:51 -0500, Tom Lane wrote:
    Now, if you want to argue that we should get rid of SET WITHOUT OIDS
    altogether, I'm not sure I could dispute it. But if we have the
    ability
    to do that ISTM we should offer the reverse too.
    We should keep the ability to have OIDs. Some people use it, though not
    many.

    But the ability to turn this on/off is not an important one, since even
    the people who use OIDs seldom use this. They have CTAS; let them use
    it.

    So I say let's drop support now for ALTER TABLE SET WITHOUT OIDS and
    don't bother to implement SET WITH OIDS. Less weird corners in the
    software means fewer bugs.

    --
    Simon Riggs www.2ndQuadrant.com
    PostgreSQL Training, Services and Support
  • Tom Lane at Feb 10, 2009 at 3:18 pm ⇧

    Simon Riggs writes:
    But the ability to turn this on/off is not an important one, since even
    the people who use OIDs seldom use this. They have CTAS; let them use
    it.
    Well, CTAS is a vastly inferior solution because you'd have to manually
    move indexes, constraints, FKs, etc to the new table. Plus it's just as
    slow if not slower than the proposed rewriting code. I think that
    Andrew's complaint about not putting barriers in the way of removing
    OIDs would apply pretty strongly to that approach.

    regards, tom lane

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-hackers @
categoriespostgresql
postedFeb 8, '09 at 4:13p
activeFeb 12, '09 at 11:57a
posts47
users11
websitepostgresql.org...
irc#postgresql

11 users in discussion

Marko Kreen: 12 posts Tom Lane: 12 posts Heikki Linnakangas: 5 posts Andrew Dunstan: 5 posts Robert Haas: 4 posts Martijn van Oosterhout: 2 posts David Fetter: 2 posts Greg Stark: 2 posts Simon Riggs: 1 post Greg Stark: 1 post Zdenek Kotala: 1 post

Content

People

Support

Translate

site design / logo © 2023 Grokbase