Thread: ALTER TABLE ... TO ... to change related names
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I've always wanted to be a PoatgreSQL hacker, and I am going to try this change out first. Bruce said that it's kind of low on the priority list, so hopefully I won't be holding anyone up if I take a while to get it right. The bug is that when you craete a table with a "SERIAL" column, and/or a "PRIMARY KEY", and then change that table's name via "ALTER TABLE", the related sequence and primary key index do not change their names accordingly. I think the change is simple -- just update the names of the related sequences and indexes when the table name changes. Of course, the entire operation will have to be done in a transaction block. I'm playing with the CVS version of PostgreSQL right now -- compiling it and testing it. In the meantime, I am coming up with some unit tests to determine whether I succeed or not. Any comments about the project and its scope? - -- Jonathan Gardner jgardner@jonathangardner.net Live Free, Use Linux! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/UMOZWgwF3QvpWNwRAtFRAJ9imFuhSzneNCEL5PBmV2EtajCNWACgpKab tON4nCyOKhsIujtlAFhjEDU= =VZeU -----END PGP SIGNATURE-----
There is no guarantee that a given sequence is used only for one column in one table, as I understand it. So renaming it could screw you up badly. If we made 'serial-ness' first class, and hid the sequence completely from view, this would make more sense. Or am I smoking crack? andrew Jonathan Gardner wrote: >I've always wanted to be a PoatgreSQL hacker, and I am going to try this >change out first. Bruce said that it's kind of low on the priority list, so >hopefully I won't be holding anyone up if I take a while to get it right. > >The bug is that when you craete a table with a "SERIAL" column, and/or a >"PRIMARY KEY", and then change that table's name via "ALTER TABLE", the >related sequence and primary key index do not change their names >accordingly. > >I think the change is simple -- just update the names of the related >sequences and indexes when the table name changes. Of course, the entire >operation will have to be done in a transaction block. > >I'm playing with the CVS version of PostgreSQL right now -- compiling it and >testing it. In the meantime, I am coming up with some unit tests to >determine whether I succeed or not. > >Any comments about the project and its scope? > > > >
Andrew Dunstan <andrew@dunslane.net> writes:
> There is no guarantee that a given sequence is used only for one column
> in one table, as I understand it. So renaming it could screw you up badly.
Yeah, I would recommend having a discussion about the details of the
proposed behavior before you start coding, not after.
It'd probably be reasonable to rename only those sequences that are
connected to the target table/column by internal dependencies --- this
indicates that they were created by a SERIAL column definition and not
by manual operations. IIRC, pg_dump already uses this same cue to
decide whether to dump the sequence definition separately or say SERIAL.
It'd still be possible for someone's schema to break, if they made a
sequence via SERIAL and then referred to it by name in defaults for
other columns. But I think we could say they were being unreasonably
intimate with implementation details in that case.
Similar rules need to be agreed to about when to rename indexes.
regards, tom lane
On Sat, 30 Aug 2003, Tom Lane wrote:
> It'd probably be reasonable to rename only those sequences that are
> connected to the target table/column by internal dependencies --- this
> indicates that they were created by a SERIAL column definition and not
> by manual operations.
I don't understand why the serial columns sequence should be visible as
other sequences. As a user (and not thinking of how it would be
implemented) I think it's much more logical if the serial column sequence
is hidden in the namespace of the table in some way (there is no such
namespace now I guess). Anyway, so that you can use it like this:
create table foo (x serial);
select nextval('foo.x');
select * from foo.x;
This also solves the problem to know what the sequence name is which you
have to know what to use in currval() and such.
Renaming a sequence in this setting is the same as renaming the column. Of
someone tries to use nextval('foo.x') somewhere else and then rename the
column they would expect that the nextval above would not work any more.
Also, just to make it clear, I think the notation foo.x should only work
when it is a serial column. If someone has created the sequence explictly
(visible outside the table) and have given it a name, then that is the
name to use. The database should not try to figure out that sequence from
the default or anything like that.
--
/Dennis
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Saturday 30 August 2003 09:06, Andrew Dunstan wrote: > There is no guarantee that a given sequence is used only for one column > in one table, as I understand it. So renaming it could screw you up > badly. > > If we made 'serial-ness' first class, and hid the sequence completely > from view, this would make more sense. > I'm just trying to make the following novice mistake less confusing: CREATE TABLE test (id SERIAL PRIMARY KEY); ALTER TABLE test TO old_test; CREATE TABLE test (id SERIAL PRIMARY KEY); - -> ERROR: Already a sequence with that name, already an index with that name I don't think that hiding the sequence completely from the user is a good idea. They may want to manipulate the sequence later. I like Tom's idea of just telling people that this is what happens, and if they get too intimate with the system tables and the sequences and indexes that are automatically created, it's their own fault. - -- Jonathan Gardner jgardner@jonathangardner.net Live Free, Use Linux! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/UN5CWgwF3QvpWNwRAgr5AKCfEfqU4NqWO60+tc6x6zkrqY9EgwCgwD4X F3Sns/QFfneuijSG8fT85K4= =l5nJ -----END PGP SIGNATURE-----
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On Saturday 30 August 2003 09:38, Tom Lane wrote: > Andrew Dunstan <andrew@dunslane.net> writes: > > There is no guarantee that a given sequence is used only for one column > > in one table, as I understand it. So renaming it could screw you up > > badly. > > Yeah, I would recommend having a discussion about the details of the > proposed behavior before you start coding, not after. > > It'd probably be reasonable to rename only those sequences that are > connected to the target table/column by internal dependencies --- this > indicates that they were created by a SERIAL column definition and not > by manual operations. IIRC, pg_dump already uses this same cue to > decide whether to dump the sequence definition separately or say SERIAL. > Agreed. I definitely only want to manipulate the sequences created with SERIAL. > > Similar rules need to be agreed to about when to rename indexes. > The only indexes I see that are created during table construction are PRIMARY KEY and UNIQUE indexes. I think these should be renamed as if they were created for a table with the new name. The whole intent is to make PostgreSQL easier to use for novices. When they craete a table with SERIAL, PRIMARY KEY, UNIQUE, and then rename it, they expect to be able to create another table with the old name and not have those sequences and indexes collide. Experts can read the documentation and see how the indexes and sequences are created and renamed. - -- Jonathan Gardner jgardner@jonathangardner.net Live Free, Use Linux! -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/UN80WgwF3QvpWNwRAj1yAKDsEHdPNEIIZPMARVfgtvQ43TZeGACgzunp ssH+tMKhGPby/vBREQJ2Rsw= =eJwg -----END PGP SIGNATURE-----
Dennis Björklund wrote:
>On Sat, 30 Aug 2003, Tom Lane wrote:
>
>
>
>>It'd probably be reasonable to rename only those sequences that are
>>connected to the target table/column by internal dependencies --- this
>>indicates that they were created by a SERIAL column definition and not
>>by manual operations.
>>
>>
>
>I don't understand why the serial columns sequence should be visible as
>other sequences. As a user (and not thinking of how it would be
>implemented) I think it's much more logical if the serial column sequence
>is hidden in the namespace of the table in some way (there is no such
>namespace now I guess). Anyway, so that you can use it like this:
>
>create table foo (x serial);
>select nextval('foo.x');
>
... or using schema paths. Possibly you could add the ability to select
foo.x.nextval/foo.x.nextval( ). The path to that sequence would change
if the table name were changed, but you'd have to make sure that any
other table(s) referencing that sequence would be altered as well to
reflect the new location.
Using a method simlar to this would also prevent the sequence name
collision problem with long named tables and long named columns.
Could the dependency tracker handle this?
>select * from foo.x;
>
>This also solves the problem to know what the sequence name is which you
>have to know what to use in currval() and such.
>
>Renaming a sequence in this setting is the same as renaming the column. Of
>someone tries to use nextval('foo.x') somewhere else and then rename the
>column they would expect that the nextval above would not work any more.
>
>Also, just to make it clear, I think the notation foo.x should only work
>when it is a serial column. If someone has created the sequence explictly
>(visible outside the table) and have given it a name, then that is the
>name to use. The database should not try to figure out that sequence from
>the default or anything like that.
>
>
>
Dennis Björklund <db@zigo.dhs.org> writes:
> I don't understand why the serial columns sequence should be visible as
> other sequences.
Backwards compatibility, if nothing else. Are you prepared to break
every existing dump file that hasCREATE TABLE ser (f1 serial);SELECT pg_catalog.setval('ser_f1_seq', 1, false);
> create table foo (x serial);
> select nextval('foo.x');
This conflicts with the existing provisions for accessing sequences
using ordinary schema-qualified names ('schema.sequence').
The work I would actually like to see getting done in this area is
the existing TODO item about using Oracle-compatible syntax for nextval
et al, namely that you can writesequence.nextval
orschema.sequence.nextval
rather than nextval('sequence') or nextval('schema.sequence'). The
internal representation of such a thing could use the sequence OID to
refer to the sequence, and would thereby be inherently rename-proof
(not to mention visible to the dependency tracker, so's you couldn't
accidentally drop a sequence that's still mentioned in some default
expression). There is speculation in the archives about how we might
implement this and even arrange to auto-migrate existing schemas during
dump/reload.
regards, tom lane
Having just had to add serial columns to 3 columns, and found it
moderately painful, I have had these thoughts: . We need "alter table foo add column bar serial" - wasn't someone
working on that? . What about a rowid type? Wouldn't have to be in any order, just
unique. Could be an existing OID value, or something similar with no
wraparound? That would account for what I suspect is 90% of the cases
where serial fields are needed.
cheers
andrew
Tom Lane wrote:
>Dennis Björklund <db@zigo.dhs.org> writes:
>
>
>>I don't understand why the serial columns sequence should be visible as
>>other sequences.
>>
>>
>
>Backwards compatibility, if nothing else. Are you prepared to break
>every existing dump file that has
> CREATE TABLE ser (f1 serial);
> SELECT pg_catalog.setval('ser_f1_seq', 1, false);
>
>
>
>
>>create table foo (x serial);
>>select nextval('foo.x');
>>
>>
>
>This conflicts with the existing provisions for accessing sequences
>using ordinary schema-qualified names ('schema.sequence').
>
>The work I would actually like to see getting done in this area is
>the existing TODO item about using Oracle-compatible syntax for nextval
>et al, namely that you can write
> sequence.nextval
>or
> schema.sequence.nextval
>rather than nextval('sequence') or nextval('schema.sequence'). The
>internal representation of such a thing could use the sequence OID to
>refer to the sequence, and would thereby be inherently rename-proof
>(not to mention visible to the dependency tracker, so's you couldn't
>accidentally drop a sequence that's still mentioned in some default
>expression). There is speculation in the archives about how we might
>implement this and even arrange to auto-migrate existing schemas during
>dump/reload.
>
> regards, tom lane
>
>---------------------------(end of broadcast)---------------------------
>TIP 8: explain analyze is your friend
>
>
>
On Sat, 30 Aug 2003, Tom Lane wrote:
> > I don't understand why the serial columns sequence should be visible as
> > other sequences.
>
> Backwards compatibility, if nothing else. Are you prepared to break
> every existing dump file that has
> CREATE TABLE ser (f1 serial);
> SELECT pg_catalog.setval('ser_f1_seq', 1, false);
I think breaking backward compability not always bad. If
a) it the change is sound and good for the long term
b) there is a solution that can be included for a number of revisions and where you preferably can turn on/off the
featurewith a variable.
The question is if the change is important enough to warrent such a
breakage. Logically I think it's preferable.
> > create table foo (x serial);
> > select nextval('foo.x');
>
> This conflicts with the existing provisions for accessing sequences
> using ordinary schema-qualified names ('schema.sequence').
That can of course be fixed by using some other selector then . (dot)
like nextval('foo#x') or one can keep the dot and resolve it any way. But
I'm not trying to come up with the best syntax here. I was more interested
in the namespace change, to keep serial sequence names out of the "global"
namespace.
The exact syntax is the last problem. The backward compability is a much
bigger issue (with 7.3 since older dumps has no problem).
> The work I would actually like to see getting done in this area is
> the existing TODO item about using Oracle-compatible syntax for nextval
> et al, namely that you can write
> sequence.nextval
> or
> schema.sequence.nextval
> rather than nextval('sequence') or nextval('schema.sequence').
It's a separate issue that I also would welcome.
--
/Dennis
> > Backwards compatibility, if nothing else. Are you prepared to break
> > every existing dump file that has
> > CREATE TABLE ser (f1 serial);
> > SELECT pg_catalog.setval('ser_f1_seq', 1, false);
>
> I think breaking backward compability not always bad. If
Replacing SERIAL with a GENERATOR make sense as the definition of an
identity (including current count) is treated as an attribute of the
column for create table.
SQL2003 GENERATOR / IDENTITIES (based on DB2 and MSSQL, I believe) have
greater functionality than SERIAL.