WIP: fix SET WITHOUT OIDS, add SET WITH OIDS
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
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
Andrew Dunstan <andrew@dunslane.net> 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
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
Zdenek Kotala <Zdenek.Kotala@Sun.COM> 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
On Sun, Feb 8, 2009 at 11:51 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Andrew Dunstan <andrew@dunslane.net> 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.phpThe 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
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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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 <david@fetter.org> wrote:
Show quoted text
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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.comRemember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
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
Greg Stark <greg.stark@enterprisedb.com> 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
On 2/9/09, Andrew Dunstan <andrew@dunslane.net> 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
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 <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
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
On 2/9/09, Martijn van Oosterhout <kleptog@svana.org> 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
On 2/9/09, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> 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
On 2/9/09, Martijn van Oosterhout <kleptog@svana.org> 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
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 <kleptog@svana.org> http://svana.org/kleptog/
Show quoted text
Please line up in a tree and maintain the heap invariant while
boarding. Thank you for flying nlogn airlines.
On Feb 9, 2009, at 7:47 AM, Marko Kreen <markokr@gmail.com> wrote:
On 2/9/09, Andrew Dunstan <andrew@dunslane.net> 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
On 2/9/09, Robert Haas <robertmhaas@gmail.com> wrote:
On Feb 9, 2009, at 7:47 AM, Marko Kreen <markokr@gmail.com> wrote:
On 2/9/09, Andrew Dunstan <andrew@dunslane.net> 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
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
On 2/9/09, Andrew Dunstan <andrew@dunslane.net> 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 bigannoyance.
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
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 <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
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
On 2/9/09, David Fetter <david@fetter.org> 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 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
Marko Kreen <markokr@gmail.com> 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
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
Andrew Dunstan <andrew@dunslane.net> 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
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
On 2/9/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Marko Kreen <markokr@gmail.com> 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
Tom Lane wrote:
Andrew Dunstan <andrew@dunslane.net> 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
On 2/9/09, Robert Haas <robertmhaas@gmail.com> 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
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 <markokr@gmail.com> writes:
On 2/9/09, Tom Lane <tgl@sss.pgh.pa.us> 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
On Mon, Feb 9, 2009 at 11:36 AM, Marko Kreen <markokr@gmail.com> wrote:
On 2/9/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Marko Kreen <markokr@gmail.com> 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
On 2/9/09, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> 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
On 2/9/09, Robert Haas <robertmhaas@gmail.com> wrote:
On Mon, Feb 9, 2009 at 11:36 AM, Marko Kreen <markokr@gmail.com> wrote:
On 2/9/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Marko Kreen <markokr@gmail.com> 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
Marko Kreen <markokr@gmail.com> writes:
On 2/9/09, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> 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
On 2/9/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Marko Kreen <markokr@gmail.com> writes:
On 2/9/09, Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> 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
Marko Kreen <markokr@gmail.com> 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
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
Andrew Dunstan <andrew@dunslane.net> 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
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 <tgl@sss.pgh.pa.us> wrote:
Greg Stark <greg.stark@enterprisedb.com> 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
Greg Stark <stark@enterprisedb.com> 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
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
Simon Riggs <simon@2ndQuadrant.com> 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
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 <tgl@sss.pgh.pa.us> wrote:
Show quoted text
Greg Stark <stark@enterprisedb.com> 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