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

[Sébastien Gay]

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