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

Sébastien Gay s.gay at ovea.com
Mon May 29 16:19:23 JST 2017


Hello

I have tried new test in streaming replication and i don't have the problem
Thank you for your help


Best regards, 

Sébastien Gay
L'équipe Support

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

> Le 18 mai 2017 à 11:35, Sébastien Gay <s.gay at ovea.com> a écrit :
> 
> 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 <http://www.ovea.com/>
> 
>> Le 18 mai 2017 à 11:14, Tatsuo Ishii <ishii at sraoss.co.jp <mailto: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 <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/20170529/f396c9a7/attachment-0001.html>


More information about the pgpool-general mailing list