newbie: renaming sequences task

Oops: meant pg_type.typname
and
pg_class.relname.

----- Original Message ----
From: craigp <craigp98072@yahoo.com>
To: pgsql-hackers@postgresql.org
Sent: Sunday, March 2, 2008 2:36:33 AM
Subject: 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

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

Oops: meant pg_type.typname
and
pg_class.relname.

----- Original Message ----
From: craigp <craigp98072@yahoo.com>
To: pgsql-hackers@postgresql.org
Sent: Sunday, March 2, 2008 2:36:33 AM
Subject: 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

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

craigp <craigp98072@yahoo.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@sss.pgh.pa.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]create table t1 (id serial8 primary key not null).
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)
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