Обсуждение: Re: [GENERAL] Sequences/defaults and pg_dump
On Wed, Feb 08, 2006 at 10:57:20PM -0500, Bruce Momjian wrote: > > > > > TODO has: > > > > > * %Disallow changing default expression of a SERIAL column > > Sure, the "DROP SERIAL" I proposed would rather "change" the data type > > to int by dropping the default and would delete referring pg_depend entries. > > Read it more as a kind of "drop autoincrement functionality for this > > column". > > The problem I see (but you might see it differently) is that you can't drop > > this autoincrement stuff without also dropping the column once you forbid to > > change the default (yeah I know, changing the default is even worse and > > leaves you with incorrect dependencies). > I assume an ALTER COLUMN ... TYPE INTEGER would drop the SERIAL part. So far it doesn't because it doesn't know the difference between serial and int. What about this proposal for serial columns: - DROP DEFAULT drops serial and removes dependencies - SET DEFAULT forbidden, issues a hint to DROP DEFAULT first Is it also desired to convert an int column to a serial column? (moving to -hackers) Joachim -- Joachim Wieland joe@mcknight.de C/ Usandizaga 12 1°B ICQ: 37225940 20002 Donostia / San Sebastian (Spain) GPG key available
Joachim Wieland wrote: > On Wed, Feb 08, 2006 at 10:57:20PM -0500, Bruce Momjian wrote: > > > > > > TODO has: > > > > > > > * %Disallow changing default expression of a SERIAL column > > > > Sure, the "DROP SERIAL" I proposed would rather "change" the data type > > > to int by dropping the default and would delete referring pg_depend entries. > > > Read it more as a kind of "drop autoincrement functionality for this > > > column". > > > > The problem I see (but you might see it differently) is that you can't drop > > > this autoincrement stuff without also dropping the column once you forbid to > > > change the default (yeah I know, changing the default is even worse and > > > leaves you with incorrect dependencies). > > > I assume an ALTER COLUMN ... TYPE INTEGER would drop the SERIAL part. > > So far it doesn't because it doesn't know the difference between serial > and int. > > What about this proposal for serial columns: > > - DROP DEFAULT drops serial and removes dependencies > - SET DEFAULT forbidden, issues a hint to DROP DEFAULT first > > > Is it also desired to convert an int column to a serial column? I think the only sane solution is if a SERIAL column is changed to INTEGER, the default and dependencies are removed. Do you want a TODO for that? -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
>> Is it also desired to convert an int column to a serial column?
> I think the only sane solution is if a SERIAL column is changed to
> INTEGER, the default and dependencies are removed. Do you want a TODO
> for that?
If we are going to do something like that, I think we should take a hard
look at the idea I floated of putting SERIAL back to a pure
creation-time macro for type and default expression. This is getting to
have way too much hidden behavior, and what we are buying for it is very
little as of 8.1.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <pgman@candle.pha.pa.us> writes: > >> Is it also desired to convert an int column to a serial column? > > > I think the only sane solution is if a SERIAL column is changed to > > INTEGER, the default and dependencies are removed. Do you want a TODO > > for that? > > If we are going to do something like that, I think we should take a hard > look at the idea I floated of putting SERIAL back to a pure > creation-time macro for type and default expression. This is getting to > have way too much hidden behavior, and what we are buying for it is very > little as of 8.1. OK, but I was confused how 8.1 has improved the way SERIAL works. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> If we are going to do something like that, I think we should take a hard
>> look at the idea I floated of putting SERIAL back to a pure
>> creation-time macro for type and default expression. This is getting to
>> have way too much hidden behavior, and what we are buying for it is very
>> little as of 8.1.
> OK, but I was confused how 8.1 has improved the way SERIAL works.
I already said this up-thread, but: a plain old "DEFAULT nextval('foo')"
now has the properties that you can't drop sequence foo without dropping
the default expression, and renaming the sequence isn't a problem. That
takes care of the worst problems that we invented the SERIAL dependency
for. If we dropped the special sequence-to-column dependency that
SERIAL now adds, and got rid of the special pg_dump behavior for
serials, we'd have less code instead of more and it would work a lot
more transparently. The only thing we'd lose is that dropping a column
originally declared as serial wouldn't implicitly drop the sequence.
That's somewhat annoying but I'm not convinced that preserving that one
thing is worth the amount of infrastructure that's getting built (and
I hope you don't think that Joachim's proposal will be the end of it).
Basically we're sticking more and more band-aids on a design that wasn't
such a great idea to start with.
regards, tom lane
Tom Lane wrote: > The only thing we'd lose is that dropping a column > originally declared as serial wouldn't implicitly drop the sequence. Wasn't that the primary purpose that the main coder for dependencies did the work for? AFAIR the fact that the sequence wasn't dropped was a big gotcha. Everyone was annoyed any time they wanted to experiment with creating and dropping a table. -- Alvaro Herrera http://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc.
Alvaro Herrera <alvherre@commandprompt.com> writes:
> Tom Lane wrote:
>> The only thing we'd lose is that dropping a column
>> originally declared as serial wouldn't implicitly drop the sequence.
> Wasn't that the primary purpose that the main coder for dependencies did
> the work for?
My recollection is that the dependency for serials was added as an
afterthought without too much consideration of the long-term
implications. It was a cheap way of sort-of solving an immediate
problem using a mechanism that we were putting in place anyway.
But what we've got now is a misbegotten cross between the theory that
a SERIAL is a unitary object you mustn't muck with the innards of,
and the theory that SERIAL is just a macro that sets up an initial
state you can ALTER to your heart's content later.
IMHO we should make a choice between those plans and stick to it,
not add more and more infrastructure to let you ALTER things you
shouldn't be altering. Either a SERIAL is a black box or it isn't.
If it is not to be a black box, we need to reduce rather than increase
the amount of hidden semantics.
regards, tom lane