Re: WIP -- renaming implicit sequences

On Sat, Jan 14, 2012 at 5:51 PM, Thomas Munro <munro(at)ip9(dot)org> wrote:
> On 12 January 2012 00:58, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> Hmm ... this seems a bit inconsistent with the fact that we got rid of
>> automatic renaming of indexes a year or three back.  Won't renaming of
>> serials have all the same problems that caused us to give up on renaming
>> indexes?
>
> I was sort of planning to do something similar for constraints (once
> the patch to support renaming constraints lands) and indexes (I didn't
> know they'd previously been automatically renamed and that had been
> dropped).
>
> Would you say that I should abandon this, no chance of being accepted?
>  Is there a technical problem I'm missing, other than the gap between
> unique name generation and execution of the implicit ALTERs?
>
> Maybe I should look into writing a 'tidy rename' procedure for tables
> and columns instead, rather than modifying the behaviour of core ALTER
> TABLE.

+1 for that approach. I think this is the kind of thing that seems
cooler on paper than it is in real life. For example, consider this:

rhaas=# create table foo (a serial);
NOTICE: CREATE TABLE will create implicit sequence "foo_a_seq" for
serial column "foo.a"
CREATE TABLE
rhaas=# alter sequence foo_a_seq rename to bob;
ALTER SEQUENCE

If somebody renames the table or the column at this point, it's a good
bet that they *don't* want bob renamed.

Also, some application code I've had in the past had sequence names
hardcoded into it - it did things like SELECT nextval(...) followed by
INSERT ..., for lack of INSERT .. RETURNING, which didn't exist at the
time. So it's not implausible that renaming a sequence could be
unwanted, though in practice the likelihood is fairly low: had I
renamed a table, I probably would have renamed the sequence as well.

Another, admittedly minor consideration is that this can introduce
some subtle concurrency bugs that we don't have today. For example,
suppose we choose a new name for the sequence which isn't in use, but
then between the time when we pick the name and the time we do the
insert the name becomes used, due to some concurrent transaction. Now
we'll fail with a rather baffling error message. That isn't
necessarily a huge problem - we have lots of code that is prone to
such race conditions - but it's not wonderful either. Someday it
would be nice to figure out a cleaner solution to these kinds of
issues... maybe allowing the btree AM to return normally with an
indication that there's a unique constraint violation, rather than
throwing an ERROR.

I think we should remove this from the TODO list, or at least document
that there are a number of reasons why it might be a deeper hole than
it appears to be at first glance.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company