Re: [GENERAL] pg_migrator not setting values of sequences?

Bruce Momjian wrote:
> Alvaro Herrera wrote:
> > Bruce Momjian wrote:
> >
> > > Something is certainly wrong. Did we change sequence table format from
> > > 8.3 to 8.4?
> >
> > 8.3 does not have start_value.
>
> Looking at an invalidly-migrated sequence's columns:
>
> regression=> \d serialtest_f2_foo
> Sequence "public.serialtest_f2_foo"
> Column | Type | Value
> ---------------+---------+---------------------
> sequence_name | name | serialtest_f2_foo
> last_value | bigint | 3
> start_value | bigint | 1
> increment_by | bigint | 9223372036854775807
> max_value | bigint | 1
> min_value | bigint | 1
> cache_value | bigint | 0
> log_cnt | bigint | 25387551686912
> is_cycled | boolean | f
> is_called | boolean |
>
> Should pg_migrator just pull the misaligned values and do an ALTER
> SEQUENCE/seval() to fix it, or create a script to do that?

I have applied the attached patch to pg_migrator that will properly
handle migrating sequences; it should apply cleanly to pg_migrator
8.4.1 alpha 1.

What I found during research is that pg_dump --schema-only already
creates the sequence:

CREATE SEQUENCE check_seq
START WITH 1
INCREMENT BY 1
NO MAXVALUE
NO MINVALUE
CACHE 1;

What it does not do is to call setval() to set the sequence value and
'is_called'. What I did was to _not_ migrate the sequence file, but
rather create a script from the old cluster that uses setval() to set
the sequence values. This can be safely run by pg_migrator
unconditionally because we are not migrating the sequence files, even in
link mode.

This solves the sequence migration problem, with no changes to pg_dump.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +