Re: [HACKERS] [Bug fix]If recovery.conf has target_session_attrs=read-write, the standby fails to start.

2017-05-19 Thread Higuchi, Daisuke 
From: Michael Paquier [mailto:michael.paqu...@gmail.com]
> if (!PQsendQuery(conn,
>- "show transaction_read_only"))
>+ "SHOW transaction_read_only"))
>Or perhaps the replication command parser could be made more flexible with 
>lower-case characters for replication commands?
By adding flex option '-i', replication command parser could be more flexible. 

# repl_scanner is compiled as part of repl_gram
 repl_gram.o: repl_scanner.c
+repl_scanner.c: FLEXFLAGS = -CF -p -i

This option is already used for syncrep_scanner.c, so it is not strange to add 
for repl_scanner.c too. 
Attached patch also fixes this problem. 

Regards, 
Daisuke, Higuchi 



flexible_replication_command_parser_v1.patch
Description: flexible_replication_command_parser_v1.patch

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

[HACKERS] [Bug fix]If recovery.conf has target_session_attrs=read-write, the standby fails to start.

2017-05-18 Thread Higuchi, Daisuke 
Hello, 

I found a problem with libpq connection failover. 
If primary_conninfo in recovery.conf has 'target_session_attrs=read-write', the 
standby fails to start. 

How to reproduce the bug: 
1. Prepare two standby (standby_1, standby_2) for one master.
   On standby_1, primary_conninfo in recovery.conf specifies master. 
   On standby_2, primary_conninfo in recovery.conf specifies master and 
standby_2 and target_session_attrs=read-write.
   For example, the standby_2's recovery.conf setting is as follows.
   ---
   standby_mode = 'on'
   primary_conninfo = 'host=localhost,localhost port=5432,5433 
target_session_attrs=read-write'
   recovery_target_timeline = 'latest'
   ---
2. Starts master, standby_1 and standby_2. When try to start standby_2, the 
following error is output. 
   ---
  Test "show transaction_read_only" failed on "localhost: 5432"
  ERROR: not connected to database
  Test "show transaction_read_only" failed on "localhost: 5433"
  ERROR: not connected to database
   ---
I wanted to test if standby_2 re-connects to the new master when master is 
stopped and standby_1 is promoted, but I failed at the start-up stage.

The reason of this problem is that sending 'show transaction_read_only' is 
failed. 
'show' must be in uppercase letters because the streaming replication protocol 
only accepts capital letters. 
In psql and libpq applications that do not use the streaming replication 
protocol, this error does not occur.

The attached patch fixes this. This patch changes 'show transaction_read_only' 
to 'SHOW transaction_read_only'.

Regards, 
Daisuke, Higuchi



target_session_attrs_in_recovery_conf_v1.patch
Description: target_session_attrs_in_recovery_conf_v1.patch

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

Re: [HACKERS] Re: new high availability feature for the system with both asynchronous and synchronous replication

2017-03-05 Thread Higuchi, Daisuke 
From: Robert Haas [mailto:robertmh...@gmail.com]
>> This patch enables walsender for async to wait until walsender for sync 
>> confirm
>> WAL is flashed to Disk. This feature is activated when GUC parameter
>> "async_walsender_delay" is set on.
> So this new option makes asynchronous replication synchronous?
No, this feature only delays the start of WAL transfer of asynchronous 
replication. 
Asynchronous replication on this feature does not wait for response from 
standby. 
(This behavior does not be changed, so it is the same as before. )

Regards, 
Daisuke Higuchi


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

[HACKERS] Re: new high availability feature for the system with both asynchronous and synchronous replication

2017-02-27 Thread Higuchi, Daisuke 
Hi all, 

I create POC patch for my proposal of new feature for high availability. 
I want to discuss about this feature. But this feature might be PG11 
because discussion is not enough. 

This patch enables walsender for async to wait until walsender for sync confirm 
WAL is flashed to Disk. This feature is activated when GUC parameter 
"async_walsender_delay" is set on. 

I write the case when this feature is useful (this is the same as I wrote 
before): 
1. Primary and synchronous standby are in the same center; called main center. 
2. Asynchronous standby is in the another center; called backup center. 
   (The backup center is located far away from the main center. If replication 
   mode is synchronous, performance will be deteriorated. So, this replication 
   must be Asynchronous. )
3. Asynchronous replication is performed in the backup center too. 
4. When primary in main center abnormally stops, standby in main center is 
   promoted, and the standby in backup center connects to the new primary.

[Main center]
||
| |--|  synchronous |--| |
| |  |replication   |  | |
| | primary  | <--> | standby1 | |
| |--|  |--| |
|||--|
 ||
 || asynchronous
 ||   replication
 ||
 ||[Backup center]
|||--|
| |--|  asynchronous|--| |
| |  |replication   |  | |
| | standby2 | <--> | standby3 | |
| |--|  |--| |
||

When the load in the main center becomes high, although WAL reaches standby in 
backup center, WAL may not reach synchronous standby in main center for various 
reasons. In other words, standby in the backup center may advance beyond 
synchronous standby in main center.

When the primary abnormally stops and standby in main center promotes, two 
standbys in backup center must be recovered by pg_rewind. However, it is 
necessary to stop new primary for pg_rewind. If pg_basebackup is used, 
recovery of backup center takes some times. This is not high availability.

Regards, 
Daisuke Higuchi 



async-waits-sync-walsender-v1.patch
Description: async-waits-sync-walsender-v1.patch

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

[HACKERS] new high availability feature for the system with both asynchronous and synchronous replication

2017-02-14 Thread Higuchi, Daisuke 
Hi all,

I propose a new feature for high availability. 

This configuration is effective for following configuration: 
1. Primary and synchronous standby are in the same center; called main center. 
2. Asynchronous standby is in the another center; called backup center. 
   (The backup center is located far away from the main center. If replication 
   mode is synchronous, performance will be deteriorated. So, this replication 
   must be Asynchronous. )
3. Asynchronous replication is performed in the backup center too. 
4. When primary in main center abnormally stops, standby in main center is 
   promoted, and the standby in backup center connects to the new primary.

This configuration is also shown in the figure below. 

[Main center]
||
| |--|  synchronous |--| |
| |  |replication   |  | |
| | primary  | <--> | standby1 | |
| |--|  |--| |
|||--|
 ||
 || asynchronous
 ||   replication
 ||
 ||[Backup center]
|||--|
| |--|  asynchronous|--| |
| |  |replication   |  | |
| | standby2 | <--> | standby3 | |
| |--|  |--| |
||

When the load in the main center becomes high, although WAL reaches standby in 
backup center, WAL may not reach synchronous standby in main center for various 
reasons. In other words, standby in the backup center may advance beyond 
synchronous standby in main center.

When the primary abnormally stops and standby in main center promotes, two 
standbys in backup center must be recovered by pg_rewind. However, it is 
necessary to stop new primary for pg_rewind. If pg_basebackup is used, 
recovery of backup center takes some times. This is not high availability. 

[Proposal Concept]
In this feature, just switch the connection destination and restart it. 
So, it is not necessary to stop new primary.There is no need for recovering 
by pg_rewind or pg_basebackup because standby in the backup center will not 
advance beyond the standby in the main center.

In my idea, this feature is enabled when the new GDU parameter is set. 
In the case that synchronous standby and asynchronous standby are connected 
to primary, walsender check if WAL is sent to synchronous standby before 
sending WAL to the asynchronous standby. After walsender confirm WAL has been 
sent to synchronous standby, it also sends the WAL to the asynchronous standby.

I would appreciate it if you give any comments for this feature. 

Regards, 
Daisuke Higuchi 



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

Re: [HACKERS] [Bug fix] PQsendQuery occurs error when target_session_attrs is set to read-write

2017-02-01 Thread Higuchi, Daisuke 
From: Michael Paquier [mailto:michael.paqu...@gmail.com] 
>This has not been added yet to the next CF. As Ashutosh mentioned
>things tend to be easily forgotten. So I have added it here:
>https://commitfest.postgresql.org/13/982/
Thank you for adding this problem to CF. 

> I have noticed this typo on the way => s/requisted/requested/:
>--- a/src/interfaces/libpq/fe-connect.c
>+++ b/src/interfaces/libpq/fe-connect.c
>@@ -2908,7 +2908,7 @@ keep_going:   /* We will
>come back to here until there is
>}
>/*
>-* If a read-write connection is requisted check for same.
>+* If a read-write connection is requested check for same.
> */
>if (conn->target_session_attrs != NULL &&
>strcmp(conn->target_session_attrs, "read-write") == 0)
I add this fix to the updated patch. 
This is based on the patch Ashutosh updated. 

Regards, 
Daisuke, Higuchi



PQsendQuery_for_target_session_attrs_v4.patch
Description: PQsendQuery_for_target_session_attrs_v4.patch

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

Re: [HACKERS] [Bug fix] PQsendQuery occurs error when target_session_attrs is set to read-write

2017-02-01 Thread Higuchi, Daisuke 
From: Ashutosh Bapat [mailto:ashutosh.ba...@enterprisedb.com] 
>Sorry, attached wrong patch. Here's the right one.
> The code expects that there will be two PQgetResult() calls required.
> One to fetch the result of SHOW command and the other to extract NULL.
> If we require more calls or unexpected results, we should throw and
> error. The patch just checks the first result and consumes the
> remaining without verifying them. Also, it looks like we can not clear
> result of PQgetResult() before using the values or copying them
> somewhere else [1]. Here's updated patch which tries to do that. 
> Please let me know if this looks good to you.
Oh, I had a basic misunderstanding. Thank you for correct me. 
There is no problem in the patch you attached. I agree with you. 

Regards, 
Daisuke, Higuchi


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

Re: [HACKERS] [Bug fix] PQsendQuery occurs error when target_session_attrs is set to read-write

2017-02-01 Thread Higuchi, Daisuke 
From: Ashutosh Bapat [mailto:ashutosh.ba...@enterprisedb.com] 
> Per the documentation [1], "PQgetResult must be called repeatedly
> until it returns a null pointer, indicating that the command is
> done.". The code in PQgetResult() under CONNECTION_CHECK_WRITABLE
> case, violates that. The patch fixes it. The patch however jumps to
> keep_going without changing conn->status, which means that it will end
> up again in the same case. While that's fine, may be we should use a
> local loop on PQgetResult() to keep the code readable.
Thank you for reviewing the patch. 
I created it with reference to pqSetenvPoll() in 
interfaces/libpq/fe-protocol2.c, 
but I certainly thought that readability is not good. 
I updated the patch, so I will add this to the next commitfest. 

Regards, 
Daisuke, Higuchi



PQsendQuery_for_target_session_attrs_v2.patch
Description: PQsendQuery_for_target_session_attrs_v2.patch

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

[HACKERS] [Bug fix] PQsendQuery occurs error when target_session_attrs is set to read-write

2017-01-31 Thread Higuchi, Daisuke 
Hello,

This this is my first posting to the mailing list.

I am interested in multiple hosts of libpq [1], then I found the bug in this 
feature. 
When I set "target_session_attrs" to "any" and call PQsendQuery, my application 
is succeeded.
However, when I set "target_session_attrs" to "read-write" and call 
PQsendQuery, "another command is already in progress" is occurred. 
I attached the test application to reproduce this problem. 

I think this is because PQgetResult is not called until PQgetResult has 
returned a null pointer. 
So, I attached the patch for fix this. 

[1] 
https://www.postgresql.org/message-id/flat/20150818041850.ga5...@wagner.pp.ru#20150818041850.ga5...@wagner.pp.ru

Regards, 
Daisuke Higuchi

/*
 * gcc multiple_hosts_test.c -lpq -I/home/postgres/work/pgsql10/include 
-L/home/postgres/work/pgsql10/lib
 * if target_session_attrs=read-write,  "another command is already in 
progress" is occured
 */

#include 
#include 
#include 
#ifdef WIN32
#include 
#endif
#include "libpq-fe.h"

int main( int argc, char *argv[])
{
int i=0,rtn;
PGconn *conn;
PGresult *r;
const char *keywords[5];
const char *values[5];

/*
** Connect to postgres
*/
keywords[0] = "dbname";
values[0] = "postgres";
keywords[1] = "port";
values[1] = (char *)getenv("PGPORT"); /* export PGPORT=5432,5433 */
keywords[1] = "host";
values[1] = (char *)getenv("PGHOST"); /* export 
PGHOST=localhost,localhost */
keywords[2] = "target_session_attrs";
values[2] = "read-write";
keywords[3] = NULL;
values[3] = NULL;
conn = PQconnectdbParams((const char * const*)&keywords, (const char * 
const*)&values, 1);
if (PQstatus(conn) != CONNECTION_OK)
{
printf("Connection to database failed: %s 
\n",PQerrorMessage(conn));
goto ERR;
}
printf("host=%s,port=%s\n",PQhost(conn),PQport(conn));

/*
** Asynchronous statement
*/
printf("-- SHOW transaction_read_only --\n");
rtn = PQsendQuery(conn, "SHOW transaction_read_only");
if (rtn != 1)
{
printf( "SHOW command failed: %s", PQerrorMessage(conn));
goto ERR;
}
r = PQgetResult(conn);
printf("SHOW transaction_read_only:%s\n",PQgetvalue(r, 0, 0));
r = PQgetResult(conn);
PQclear(r);

/*
** Disconnect
*/
PQfinish(conn);
printf("Success!\n");

return(0);

ERR:
PQfinish(conn);
printf("Failed!\n");

return(1);
}




PQsendQuery_for_target_session_attrs_v1.patch
Description: PQsendQuery_for_target_session_attrs_v1.patch

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