Re: newbie: renaming sequences task

Hi -

I was perusing the todo list to see some easy items that I might help out on (and get up to speed on postgres hacking)... one of them (with %) seems to lead to another:

o %Have ALTER TABLE RENAME rename SERIAL sequence names
o Have ALTER SEQUENCE RENAME rename the sequence name stored in the sequence table

and perhaps this one as well:

Consider placing all sequences in a single table, or create a system view

I read through the mailing list links (they seem to culminate with these two):

http://archives.postgresql.org/pgsql-bugs/2007-09/msg00141.php
http://archives.postgresql.org/pgsql-bugs/2007-10/msg00038.php

But I'm left a bit confused on what, if anything, can or should be done. Maybe this isn't the best item to start with? If I had some more direction, it might be straightforward enough.

One thing I did notice: sequence names are stored in both pg_type.typname and pg_class.relkind. I presume both tables would need to be updated, unless we remove the redundancy? Why can they not be updated within a single transaction (easily)? What sort of restructuring would be needed to separate out the transactional vs non-transactional aspects?

thanks!
--craig

____________________________________________________________________________________
Never miss a thing. Make Yahoo your home page.
http://www.yahoo.com/r/hs

craigp <craigp98072(at)yahoo(dot)com> writes:
> I was perusing the todo list to see some easy items that I might help out on (and get up to speed on postgres hacking)... one of them (with %) seems to lead to another:
> ...
> But I'm left a bit confused on what, if anything, can or should be done. Maybe this isn't the best item to start with? If I had some more direction, it might be straightforward enough.

No, the reason those are still on the TODO list is that it's not
straightforward.

The first one is really not related to the others --- it just proposes
that when renaming a table or individual column, we should look for
sequences "owned by" that column or columns, and rename them so that
they still look like "table_column_seq". This is about 50%
straightforward searching of pg_depend, and about 50% dealing with
collisions --- if there's already something of that name, you'd need
to go through the same type of fallback name selection that's already
done when a serial column is first made.

(Thinking about it, I kinda wonder whether we even *want* such behavior
anymore. In the presence of ALTER SEQUENCE ... OWNED BY, it's entirely
possible that an owned sequence has a name that's got nothing to do with
table_column_seq, and which the user wouldn't really want us to forcibly
rename. Maybe this TODO has been overtaken by events?)

The second one is not about that, but about wishing that the sequence
name that (for purely historical reasons) is stored in the sequence's
data row would track ALTER SEQUENCE RENAME. The problem here is that
ordinary sequence operations like nextval() are nontransactional, and
it's hard to mix transactional and nontransactional updates of the same
row. There's been talk of rearranging the representation of sequences
to fix this, but nothing concrete. The only concrete solution offered
has been to remove that copy of the name, which would be simple but not
backwards compatible.

The third one is a semi-independent feature wish, namely for a single
system table or view in which all sequences' parameters could be seen.
(An example of the usefulness of that is that it's currently extremely
hard to get psql's \dS to show sequence parameters, because we can't
join dynamically to individual sequences.)

The tie between #2 and #3 is mostly just not wanting to repeatedly
whack around the representation or user-visible properties of sequences.
If we're going to do both things they should happen in the same update.

I'm not sure that #2 or #3 is a suitable first hacking project. The
real task underlying both of them is "redesign the representation of
sequences in a way that has the right combination of transactional and
nontransactional behaviors, and try to make sure that it'll scale to
lots and lots of sequences".

regards, tom lane

--- Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> The first one is really not related to the others --- it just proposes
> that when renaming a table or individual column, we should look for
> sequences "owned by" that column or columns, and rename them so that
> they still look like "table_column_seq". This is about 50%
> straightforward searching of pg_depend, and about 50% dealing with
> collisions --- if there's already something of that name, you'd need
> to go through the same type of fallback name selection that's already
> done when a serial column is first made.
>
> (Thinking about it, I kinda wonder whether we even *want* such behavior
> anymore. In the presence of ALTER SEQUENCE ... OWNED BY, it's entirely
> possible that an owned sequence has a name that's got nothing to do with
> table_column_seq, and which the user wouldn't really want us to forcibly
> rename. Maybe this TODO has been overtaken by events?)

Well, if we just look at the first one, I wonder (as well) whether or not it is
useful (and as you said, perhaps surprising in some cases).

I did a quick test, and neither sequences nor primary keys are renamed [1].
Maybe this should be the default behaviour, unless we give provide some other
option to ALTER TABLE RENAME? Another possibility might be to only rename
sequences and/or primary keys which had been created implicitly during table
creation (assuming that information is tracked)?

[1]
create table t1 (id serial8 primary key not null)

alter table t1 rename to t2

pk still named t1_pkey
sequence still named t1_id_seq

____________________________________________________________________________________
Looking for last minute shopping deals?
Find them fast with Yahoo! Search. http://tools.search.yahoo.com/newsearch/category.php?category=shopping