[pgpool-general: 2068] Re: trouble with recovery of a downed node

[Sean Hogan]

On 13-08-22 08:59 AM, Sean Hogan wrote:
> On 13-08-21 08:23 PM, Tatsuo Ishii wrote:
>>> Ah, I believe I understand the problem now: the sequence values are 
>>> different (off by one) between the original and recovered databases. 
>>> The sample pgpool_sample_pitr script has: SELECT setval(oid, 
>>> nextval(oid)) FROM pg_class WHERE relkind = 'S' When I change that 
>>> to SELECT setval(oid, 1*currval(oid)) FROM pg_class WHERE relkind = 
>>> 'S' I get identical databases and the problem has not reoccurred! 
>>> (The "1*" is to defeat optimization; not sure if it is needed.) 
>>> Thanks for your excellent advice. 
>> Thanks for the report but your changes seems a little bit strange to
>> me. According to the PostgreSQL manual, calling currval() without
>> prior calling nextval() will raise an error. Don't you get any error?
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English:http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>
> That's interesting, I was unaware of that restriction on currval. 
> Indeed if I run the script manually I do get an error.  Now I'm quite 
> confused, because the overall effect is what I need.  Based on the 
> manual page at 
> http://www.postgresql.org/docs/9.2/static/functions-sequence.html if 
> appears the following will work:
>
> SELECT setval(oid, nextval(oid), false) FROM pg_class WHERE relkind = 'S'
>
> and my initial experiments suggest it does.
>
> Sean

But later experiments show I'm back where I started.  :-(  Is there any 
way to get the current sequence value out into the WAL without 
disturbing it?

Or am I looking at the wrong thing?  Is there some other logical reason 
why the sequence values were all off by one?

Thanks,
Sean

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20130822/a7e4c1d5/attachment.htm>
-------------- next part --------------
A non-text attachment was scrubbed...
Name: sean.vcf
Type: text/x-vcard
Size: 288 bytes
Desc: not available
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20130822/a7e4c1d5/attachment.vcf>