[pgpool-general: 5480] Re: Backslash problem with pgpool
Sébastien Gay
s.gay at ovea.com
Wed May 17 16:49:21 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
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
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