Lists: | pgsql-hackers |
---|
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Tilmann Singer <tils(at)tils(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] pg_migrator not setting values of sequences? |
Date: | 2009-07-14 13:49:59 |
Message-ID: | 200907141349.n6EDnxi16734@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
bruce wrote:
> Tom Lane wrote:
> > Bruce Momjian <bruce(at)momjian(dot)us> writes:
> > > Tilmann Singer wrote:
> > >> However, all of the sequences were at the initial values and not
> > >> bumped up to the last used value as I would have expected. The first
> > >> nextval call on any sequence in the migrated 8.4 database always
> > >> returned 1.
> >
> > > Wow, that is also surprising. I am going to have to run some tests to
> > > find the cause, but it certainly is not intended.
> >
> > Looks like pg_migrator neglects to include relkind 'S' in the set of
> > tables that it needs to physically migrate.
>
> Thanks, I have fixed pg_migrator with the attached patch. Once we find
> the cause of the lovacuum problem, I will make a new pg_migrator release.
The patch I posted definately fixes a bug. Not sure how I missed the
regression changes caused by not migrating sequences; I thought it was
an issue with cached sequence values, not an actual bug.
However, I with the patch, I am now seeing another difference; a
database with:
SELECT pg_catalog.setval('clstr_tst_a_seq', 33, true);
becomes:
SELECT pg_catalog.setval('clstr_tst_a_seq', 33, false);
and the is_called column of the migrated sequences is NULL:
regression=> \d check_seq
Sequence "public.check_seq"
Column | Type | Value
---------------+---------+---------------------
sequence_name | name | check_seq
last_value | bigint | 1
start_value | bigint | 1
increment_by | bigint | 9223372036854775807
max_value | bigint | 1
min_value | bigint | 1
cache_value | bigint | 1
log_cnt | bigint | 25387551686656
is_cycled | boolean | f
is_called | boolean |
regression=> select * from check_seq where is_called is null;
sequence_name | last_value | start_value | increment_by |
max_value | min_value | cache_value | log_cnt | is_cycled |
is_called
---------------+------------+-------------+---------------------+-----------+-----------+-------------+----------------+-----------+-----------
check_seq | 1 | 1 | 9223372036854775807 |
1 | 1 | 1 | 25387551686656 | f |
(1 row)
Something is certainly wrong. Did we change sequence table format from
8.3 to 8.4?
--
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. +
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Tilmann Singer <tils(at)tils(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] pg_migrator not setting values of sequences? |
Date: | 2009-07-14 13:55:23 |
Message-ID: | 16088.1247579723@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
Bruce Momjian <bruce(at)momjian(dot)us> writes:
> Did we change sequence table format from
> 8.3 to 8.4?
Oh, yes we did: we added a start_value column. So this is going to take
more work than that :-(
regards, tom lane
From: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tilmann Singer <tils(at)tils(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] pg_migrator not setting values of sequences? |
Date: | 2009-07-14 13:56:47 |
Message-ID: | 20090714135647.GC4799@alvh.no-ip.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Alvaro Herrera <alvherre(at)commandprompt(dot)com> |
Cc: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tilmann Singer <tils(at)tils(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] pg_migrator not setting values of sequences? |
Date: | 2009-07-18 15:58:27 |
Message-ID: | 200907181558.n6IFwRC10882@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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?
--
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. +
From: | Bruce Momjian <bruce(at)momjian(dot)us> |
---|---|
To: | Bruce Momjian <bruce(at)momjian(dot)us> |
Cc: | Alvaro Herrera <alvherre(at)commandprompt(dot)com>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Tilmann Singer <tils(at)tils(dot)net>, PostgreSQL-development <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: [GENERAL] pg_migrator not setting values of sequences? |
Date: | 2009-07-20 19:01:36 |
Message-ID: | 200907201901.n6KJ1aZ20865@momjian.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-hackers |
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. +
Attachment | Content-Type | Size |
---|---|---|
/rtmp/diff | text/x-diff | 9.5 KB |