[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.pgpool.net/pipermail/pgpool-general/attachments/20170518/d790a729/attachment.htm>
More information about the pgpool-general
mailing list