How to reliably detect if it's a promoting standby

Ok, it seems impossible to do that by using any public interfaces
currently available in PostgreSQL 9.0. I will create a custom C
function to be distributed along with pgpool-II.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Thu, Oct 14, 2010 at 11:10 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:

Ok, it seems impossible to do that by using any public interfaces
currently available in PostgreSQL 9.0. I will create a custom C
function to be distributed along with pgpool-II.

Could you submit the function for 9.1? Thanks :-)

--
Itagaki Takahiro

On Thu, Oct 14, 2010 at 11:10 AM, Tatsuo Ishii <ishii@postgresql.org> wrote:

Ok, it seems impossible to do that by using any public interfaces
currently available in PostgreSQL 9.0. I will create a custom C
function to be distributed along with pgpool-II.

Could you submit the function for 9.1? Thanks :-)

I can't wait till 9.1 since pgpool-II needs it now. Of course I could
propose a patch against HEAD when I have enough time...

BTW, I have checked xlog.c etc. and probably the only way to know if
the standby is promoting is, to see if ShutdownWalRcv() is called. If
it's called, global variable WalRcvData->walRcvsState is set to other
than WALRCV_RUNNING, I think.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

What new public interfaces do you think are needed for 9.1 in this
regard?

Cheers,
David.
On Thu, Oct 14, 2010 at 11:10:14AM +0900, Tatsuo Ishii wrote:

Ok, it seems impossible to do that by using any public interfaces
currently available in PostgreSQL 9.0. I will create a custom C
function to be distributed along with pgpool-II.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

Hi,

I'm looking for a way to reliably detect if it's a promoting
standby. This is neccessary for pgpool-II manage streaming replication
clusters. When primary goes down, standby *could* start promoting to
primary. The only way to find it is calling
pg_is_in_recovery(). Problem is, it returns true even if standby is
promoting. If pgpool-II waits for cetain period, it may finishes
promotion, thus it returns false. But how long should we wait?

Any idea?
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

--
David Fetter <david@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter@gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

What new public interfaces do you think are needed for 9.1 in this
regard?

At this point I'm thinking of modifying existing pg_is_in_recovery(),
thus 0 new public interface. The heart of the function is
RecoveryInProgress(). It simply returns LocalRecoveryInProgress. In
addition to that, checking WalRcvData->walRcvsState being set to other
than WALRCV_RUNNING seems to solve the problem, though I have not
tested yet. Let me see if it's working...
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On Thu, Oct 14, 2010 at 7:35 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:

What new public interfaces do you think are needed for 9.1 in this
regard?

At this point I'm thinking of modifying existing pg_is_in_recovery(),
thus 0 new public interface.

pg_is_in_recovery() returns a bool, are you proposing to change that?

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL

On Thu, Oct 14, 2010 at 7:35 PM, Tatsuo Ishii <ishii@postgresql.org> wrote:

What new public interfaces do you think are needed for 9.1 in this
regard?

At this point I'm thinking of modifying existing pg_is_in_recovery(),
thus 0 new public interface.

pg_is_in_recovery() returns a bool, are you proposing to change that?

No. I just thought about adding more condition when it returns true.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

pg_is_in_recovery() returns a bool, are you proposing to change that?

No. I just thought about adding more condition when it returns true.

Here is the patch. Comments are welcome!

*** a/src/backend/access/transam/xlog.c
--- b/src/backend/access/transam/xlog.c
***************
*** 5604,5610 **** GetLatestXTime(void)
  Datum
  pg_is_in_recovery(PG_FUNCTION_ARGS)
  {
! 	PG_RETURN_BOOL(RecoveryInProgress());
  }

  /*
--- 5604,5613 ----
  Datum
  pg_is_in_recovery(PG_FUNCTION_ARGS)
  {
! 	/* use volatile pointer to prevent code rearrangement */
! 	volatile WalRcvData *walrcv = WalRcv;
! 
! 	PG_RETURN_BOOL(RecoveryInProgress() && walrcv->walRcvState == WALRCV_RUNNING);
  }

/*

On 20.10.2010 17:32, Tatsuo Ishii wrote:

pg_is_in_recovery() returns a bool, are you proposing to change that?

No. I just thought about adding more condition when it returns true.

Here is the patch. Comments are welcome!
...
Datum
pg_is_in_recovery(PG_FUNCTION_ARGS)
{
! /* use volatile pointer to prevent code rearrangement */
! volatile WalRcvData *walrcv = WalRcv;
!
! PG_RETURN_BOOL(RecoveryInProgress()&& walrcv->walRcvState == WALRCV_RUNNING);
}

This returns 'false' if you're in hot standby mode running against an
archive. That seems wrong, I don't think the walreceiver state should
play any role in this.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

On Wed, Oct 20, 2010 at 10:35 AM, Heikki Linnakangas
<heikki.linnakangas@enterprisedb.com> wrote:

On 20.10.2010 17:32, Tatsuo Ishii wrote:

pg_is_in_recovery() returns a bool, are you proposing to change that?

No. I just thought about adding more condition when it returns true.

Here is the patch. Comments are welcome!
...
  Datum
  pg_is_in_recovery(PG_FUNCTION_ARGS)
  {
!       /* use volatile pointer to prevent code rearrangement */
!       volatile WalRcvData *walrcv = WalRcv;
!
!       PG_RETURN_BOOL(RecoveryInProgress()&&  walrcv->walRcvState ==
WALRCV_RUNNING);
  }

This returns 'false' if you're in hot standby mode running against an
archive. That seems wrong, I don't think the walreceiver state should play
any role in this.

I think what we need is a second function, not a change to the
definition of this one.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

This returns 'false' if you're in hot standby mode running against an
archive. That seems wrong, I don't think the walreceiver state should
play any role in this.

Apart this, I wonder why walsender/walreceiver do not transfer archive
logs as well.
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese: http://www.sraoss.co.jp

On 20.10.2010 18:06, Tatsuo Ishii wrote:

This returns 'false' if you're in hot standby mode running against an
archive. That seems wrong, I don't think the walreceiver state should
play any role in this.

Apart this, I wonder why walsender/walreceiver do not transfer archive
logs as well.

What do you mean?

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com

Heikki Linnakangas <heikki.linnakangas@enterprisedb.com> writes:

On 20.10.2010 18:06, Tatsuo Ishii wrote:

Apart this, I wonder why walsender/walreceiver do not transfer archive
logs as well.

What do you mean?

I'd be pleased if Tatsuo idea have anything to do with this mail:

http://archives.postgresql.org/message-id/m239s1f2oi.fsf@2ndQuadrant.fr

Regards,
--
Dimitri Fontaine
http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support