[pgpool-general: 5498] Re: Backslash problem with pgpool
Tatsuo Ishii
ishii at sraoss.co.jp
Thu May 18 18:14:03 JST 2017
> 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
>
More information about the pgpool-general
mailing list