Re: Comparing primary/HS standby in tests

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