[pgpool-general: 5499] Re: Backslash problem with pgpool

Sébastien Gay s.gay at ovea.com
Thu May 18 18:35:21 JST 2017


Yes, i’m using 
replication_mode = on

I don’t have time for the moment for the streaming replication
I need to have 3 or more servers with automatic failover and i need to have at least 2 servers in the pool. 
According to the documentation that I started to look at, I have to put automatically the slaves back on the new master if the master fail.
 
I will try to work on it next week if my client leaves me a maintenance range.

Cordialement, 

Sébastien Gay
L'équipe Support

-- 
OVEA
Tél. : 04 67 67 00 00
http://www.ovea.com

> Le 18 mai 2017 à 11:14, Tatsuo Ishii <ishii at sraoss.co.jp> a écrit :
> 
>> Hello
>> 
>> Thank you for your answers
>> I think their 2 bugs :
>> - The backslash problem
>> - A cache problem
>> 
>> I’m using Debian Jessie with postgres 9.5.6 and postgis 2.3
>> Pgpool 3.5.6
>> 
>> When i’m doing your test, no problem but if a date column is added, you can reproduce the problem :
>> 
>> Your test :
>> 
>> postgres=# create table t1(j json);
>> CREATE TABLE
>> postgres=# insert into t1  (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>> INSERT 0 1
>> postgres=# select * from t1 ;
>>                                j                                 
>> ------------------------------------------------------------------
>> {"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}
>> (1 ligne)
>> 
>> Now, i’m creating a new table with a date column 
>> 
>> 
>> postgres=# create table t2(j json, "date" timestamp(6) WITH TIME ZONE DEFAULT now());
>> CREATE TABLE
>> postgres=# insert into t2 (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>> INSERT 0 1
>> postgres=# select * from t2;
>>                                  j                                   |             date              
>> ----------------------------------------------------------------------+-------------------------------
>> {"image_url":"https:\\/\\/upload.domain.tld\\/get\\/img_0320-4.jpg"} | 2017-05-17 09:30:34.864892+02
>> (1 ligne)
>> 
>> We have the backslash problem
> 
> Did not reproduced here. Are you using native replication mode?
> (replication_mode = on). I am in streaming replication mode. If yes,
> that might be a bug with timestamp rewriting code in native
> replication mode.
> 
> test=# create table t3(j json, "date" timestamp(6) WITH TIME ZONE DEFAULT now());
> CREATE TABLE
> test=# insert into t3 (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
> INSERT 0 1
> test=# select * from t3;
>                                j                                 |             date              
> ------------------------------------------------------------------+-------------------------------
> {"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"} | 2017-05-18 18:08:16.728585+09
> (1 row)
> 
>> 
>> Now, I’m dropping the table and i create a new table without the date column 
>> 
>> 
>> postgres=# DROP TABLE IF EXISTS t2 ;
>> DROP TABLE
>> postgres=# create table t2(j json);
>> CREATE TABLE
>> postgres=# insert into t2  (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>> ERROR:  column "date" of relation "t2" does not exist
>> LIGNE 1 : insert into t2  (j) values('{"image_url":"https:\/\/upload.d...
>>                                ^
>> postgres=# select * from t2 ;
>> j 
>> ---
>> (0 ligne)
>> 
>> Their no date column, i’m think it’s the cache problem 
> 
> Yes, I think so too. See "relcache_expire" in pgpool.conf.
> 
>> Now i’m creating a new table without the date column 
>> 
>> No problem 
>> 
>> 
>> postgres=# create table t3(j json);
>> CREATE TABLE
>> postgres=# insert into t3  (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>> INSERT 0 1
>> postgres=# select * from t3 ;
>>                                j                                 
>> ------------------------------------------------------------------
>> {"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}
>> (1 ligne)
>> 
>> 
>> I’m dropping the table and i’m creating it with a date column 
>> 
>> 
>> postgres=# DROP TABLE IF EXISTS t3;
>> DROP TABLE
>> postgres=# create table t3(j json, "date" timestamp(6) WITH TIME ZONE DEFAULT now());
>> CREATE TABLE
>> postgres=# insert into t3 (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>> INSERT 0 1
>> postgres=# select * from t3;
>>                                j                                 |             date              
>> ------------------------------------------------------------------+-------------------------------
>> {"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"} | 2017-05-17 09:33:24.879886+02
>> (1 ligne)
>> 
>> 
>> In this case, no problem 
>> 
>> I’m closing the pgpool connection and i reopen it 
>> 
>> With the t1 table, the problem occurs 
>> 
>> postgres=# insert into t1 (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>> INSERT 0 1
>> postgres=# select * from t1 ;
>>                                  j                                   |             date              
>> ----------------------------------------------------------------------+-------------------------------
>> {"image_url":"https:\\/\\/upload.domain.tld\\/get\\/img_0320-4.jpg"} | 2017-05-17 09:34:12.233069+02
>> {"image_url":"https:\\/\\/upload.domain.tld\\/get\\/img_0320-4.jpg"} | 2017-05-17 09:42:19.329917+02
>> (2 lignes)
>> 
>> 
>> And with the t3 table, we have the problem 
>> 
>> postgres=# insert into t3 (j) values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>> INSERT 0 1
>> postgres=# select * from t3 ;
>>                                  j                                   |             date              
>> ----------------------------------------------------------------------+-------------------------------
>> {"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}     | 2017-05-17 09:33:24.879886+02
>> {"image_url":"https:\\/\\/upload.domain.tld\\/get\\/img_0320-4.jpg"} | 2017-05-17 09:42:33.932703+02
>> (2 lignes)
>> 
>> 
>> 
>> Regards, 
>> 
>> Sébastien Gay
>> L'équipe Support
>> 
>> -- 
>> OVEA
>> Tél. : 04 67 67 00 00
>> http://www.ovea.com
>> 
>>> Le 16 mai 2017 à 18:20, Tatsuo Ishii <ishii at sraoss.co.jp> a écrit :
>>> 
>>>> From: Tatsuo Ishii <ishii at sraoss.co.jp>
>>>>>>> But with pgpool :
>>>>>>> 
>>>>>>> https:\\/\\/upload.domain.tld\\/get\\/img_0320-4.jpg
>>>>>> 
>>>>>> Interesting. Since ordinary text types do not behave like that
>>>>>> (behaves same as PostgreSQL), I guess there's a problem with parsing
>>>>>> JSON syntax. I will dig into this.
>>>>> 
>>>>> Not reproduced here.
>>>>> 
>>>>> test=# create table t1(j json);
>>>> 
>>>>> CREATE TABLE
>>>>> test=# insert into t1 values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>>>>> INSERT 0 1
>>>>> test=# select * from t1;
>>>>>                               j                                
>>>>> ------------------------------------------------------------------
>>>>> {"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}
>>>>> (1 row)
>>>>> 
>>>> 
>>>>> This is Pgpool-II 3.5 stable head.
>>>> 
>>>> 
>>>> Is there a possibility the standard_conforming_strings setting of the postgresql instance has any affect here?
>>> 
>>> No, I don't think so. Because if it's off, above INSERT should warn
>>> and result will be different.
>>> 
>>> test=# insert into t1 values('{"image_url":"https:\/\/upload.domain.tld\/get\/img_0320-4.jpg"}');
>>> WARNING:  nonstandard use of escape in a string literal
>>> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
>>> WARNING:  nonstandard use of escape in a string literal
>>> LINE 1: insert into t1 values('{"image_url":"https:\/\/upload.domain...
>>>                             ^
>>> HINT:  Use the escape string syntax for escapes, e.g., E'\r\n'.
>>> INSERT 0 1
>>> test=# select * from t1;
>>>                               j                                 
>>> ------------------------------------------------------------------
>>> {"image_url":"https://upload.domain.tld/get/img_0320-4.jpg"}
>>> 
>>> 
>>> Best regards,
>>> --
>>> Tatsuo Ishii
>>> SRA OSS, Inc. Japan
>>> English: http://www.sraoss.co.jp/index_en.php
>>> Japanese:http://www.sraoss.co.jp

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.sraoss.jp/pipermail/pgpool-general/attachments/20170518/d790a729/attachment-0001.html>


More information about the pgpool-general mailing list