Thread: Bug with rename bigserial column

I just ran into a dump/restore problem with a bigserial column
on a renamed table.

BIGSERIAL columns when created will automagically create
the sequence also.  The sequence name is derived from
the table name and column name.

I had a table named 'audio_usage' and defined a column like this:

    ausage_id BIGSERIAL

who's default value was

    NEXTVAL('public.audio_usage_ausage_id_seq')

I then renamed my table to 'audio_file_usage'.  But the column
in the table remained unchanged. (that ok?)

Well, then I did a pg_dump and a pg_restore to migrate my dev
environment to qa.  Well, sure enough, the dump was fine,
but when the restore happened, the newly renamed table combined
with the same bigserial column automagically created it's
sequence, but the sequence created is different from the
default NEXTVAL which still refers to the original table_column_seq
name.

I've corrected the problem manually, but it does seem like a bug
somewhere.

Dante

On Saturday 10 January 2004 21:31, D. Dante Lorenso wrote:
> I just ran into a dump/restore problem with a bigserial column
> on a renamed table.
[snip]
> I've corrected the problem manually, but it does seem like a bug
> somewhere.

Sounds like a bug. You might want to have a look and see if it's restoring the
value of the old or new sequence.

There's a bug form on the website or a -bugs mailing list if you prefer that.
A short example showing the sequence of commands will almost certainly help
too, as will the version of PG you're running.

--
  Richard Huxton
  Archonet Ltd

On Sun, 11 Jan 2004, Richard Huxton wrote:

> On Saturday 10 January 2004 21:31, D. Dante Lorenso wrote:
> > I just ran into a dump/restore problem with a bigserial column
> > on a renamed table.
> [snip]
> > I've corrected the problem manually, but it does seem like a bug
> > somewhere.
>
> Sounds like a bug. You might want to have a look and see if it's restoring the
> value of the old or new sequence.
>
> There's a bug form on the website or a -bugs mailing list if you prefer that.
> A short example showing the sequence of commands will almost certainly help
> too, as will the version of PG you're running.

I think previous discussions on this couldn't decide between not renaming the
underlying sequence and the one where the sequence is also renamed when the
table is.

Of course in this instance it's slightly different in that it does sound like a
bug of the dump/restore process with the not renaming sequence behaviour
employed.

I think this applies to 7.4.x as well as 7.3.x but could swear to it.


--
Nigel J. Andrews

On Sunday 11 January 2004 13:14, Nigel J. Andrews wrote:
> On Sun, 11 Jan 2004, Richard Huxton wrote:
> > On Saturday 10 January 2004 21:31, D. Dante Lorenso wrote:
> > > I just ran into a dump/restore problem with a bigserial column
> > > on a renamed table.
> >
> > [snip]
> >
> > > I've corrected the problem manually, but it does seem like a bug
> > > somewhere.
> >
> > Sounds like a bug. You might want to have a look and see if it's
> > restoring the value of the old or new sequence.

> I think previous discussions on this couldn't decide between not renaming
> the underlying sequence and the one where the sequence is also renamed when
> the table is.
>
> Of course in this instance it's slightly different in that it does sound
> like a bug of the dump/restore process with the not renaming sequence
> behaviour employed.

Yep - it sounds like the column definition is doing one thing, while the
sequence definition is doing the other.

--
  Richard Huxton
  Archonet Ltd