From: | Michael Paquier <michael(dot)paquier(at)gmail(dot)com> |
---|---|
To: | Andres Freund <andres(at)2ndquadrant(dot)com> |
Cc: | PostgreSQL mailing lists <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Comparing primary/HS standby in tests |
Date: | 2015-03-04 04:33:07 |
Message-ID: | CAB7nPqSmyhtgQNe4HPAqhq-W0W+V_zazAmKkO57K0duiQvW-1Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
On Wed, Mar 4, 2015 at 12:49 AM, Andres Freund <andres(at)2ndquadrant(dot)com> wrote:
> I every now and then run installcheck against a primary, verify that
> replay works without errors, and then compare pg_dumpall from both
> clusters. Unfortunately that currently requires hand inspection of
> dumps, there are differences like:
> -SELECT pg_catalog.setval('default_seq', 1, true);
> +SELECT pg_catalog.setval('default_seq', 33, true);
> Does anybody have a good idea how to get rid of that difference? One way
> to do that would be to log the value the standby is sure to have - but
> that's not entirely trivial.
SEQ_LOG_VALS has been added some time ago, so perhaps time have
changed and we could live without it:
commit: 741510521caea7e1ca12b4db0701bbc2db346a5f
author: Vadim B. Mikheev <vadim4o(at)yahoo(dot)com>
date: Thu, 30 Nov 2000 01:47:33 +0000
XLOG stuff for sequences.
CommitDelay in guc.c
However performance is really a problem, for example with the patch
attached and the following test case:
DO $$DECLARE count integer; count2 integer;
begin
for count in 1 .. 1000000
loop
select nextval('toto') into count2;
end loop;
END$$;
Patched, this takes 9.5ms and generates 191 MB of WAL on my laptop.
With master unpatched, this generates 6MB of WAL (records are divided
by 32) and takes 7.5s.
There are a couple of other possibilities we could consider as well:
1) Trick pg_dump such as it does not dump the current value of master
but one consistent with what a standby would expect. We would need
then something like nextval_standby() or similar.
2) Filter out lines with pg_catalog.setval in a home-made wrapper.
> I'd very much like to add a automated test like this to the tree, but I
> don't see a way to do that sanely without a comparison tool...
That's definitely worth having IMO.
Regards,
--
Michael
Attachment | Content-Type | Size |
---|---|---|
20150304_sequence_all_log.patch | text/x-patch | 23.0 KB |
test.sql | application/octet-stream | 449 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Asif Naeem | 2015-03-04 04:37:26 | Re: chkpass with RANDOMIZE_ALLOCATED_MEMORY |
Previous Message | Tom Lane | 2015-03-04 04:30:39 | Re: chkpass with RANDOMIZE_ALLOCATED_MEMORY |