[pgpool-general: 5536] Re: Backslash problem with pgpool
Tatsuo Ishii
ishii at sraoss.co.jp
Mon May 29 17:40:48 JST 2017
Thank you for the report!
Best regards,
--
Tatsuo Ishii
SRA OSS, Inc. Japan
English: http://www.sraoss.co.jp/index_en.php
Japanese:http://www.sraoss.co.jp
> 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
>>
>
More information about the pgpool-general
mailing list