Обсуждение: Bug involving plus sign before newline in text field being duplicatedor stripped

Hello,

This report contains either one or two distinct bugs, if they are two they appear related.

First I noticed than when using the python pyscopg2-binary library, a plus sign immediately proceeding a newline at the end of a value was being stripped.

When investigating further, I noticed that when directly using the psql command line interface, when inserting a text value ending with a plus sign and then a newline, the plus sign is duplicated.

This behavior exists on the following three versions I have tested:

PostgreSQL 11.2 (Ubuntu 11.2-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit

and

PostgreSQL 10.7 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-23), 64-bit

and

PostgreSQL 10.6 (Ubuntu 10.6-0ubuntu0.18.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-16ubuntu3) 7.3.0, 64-bit

I've attached very small example SQL and python files that reproduce the bug(s). The SQL file demonstrates the duplication bug - run it first. The python file demonstrates the stripping bug - run it after the table is created.

Here is an example of the duplication bug (ran on a fresh 11.2 installation from the postgresql repo on Ubuntu 18.04, no configuration changes or start up options):

wedell@manowar:~$ psql -U postgres
psql (11.2 (Ubuntu 11.2-1.pgdg18.04+1))
Type "help" for help.

postgres=# create table test (inchi text);
CREATE TABLE
postgres=# insert into test values ('test+
postgres'# ');
INSERT 0 1
postgres=# select * from test;
 inchi
-------
 test++
 
(1 row)

As you can see, only one plus sign was inserted, but two are returned. (The expected return value was 'test+\n' but the actual value was 'test++\n'.)

For reference, the second line as entered was

insert into test values ('test+
');

The second bug is that pyscopg2-binary is stripping a plus sign at the end of the value out. The following example was ran immediately after the SQL above:

wedell@manowar:~$ python
Python 2.7.15rc1 (default, Nov 12 2018, 14:31:15)
Type "copyright", "credits" or "license" for more information.

IPython 5.5.0 -- An enhanced Interactive Python.
?         -> Introduction and overview of IPython's features.
%quickref -> Quick reference.
help      -> Python's own help system.
object?   -> Details about 'object', use 'object??' for extra details.

In [1]: import psycopg2
In [2]: conn = psycopg2.connect(user='postgres')
In [3]: cur = conn.cursor()
In [4]: cur.execute('select * from test');
In [5]: print cur.fetchall()
[('test+\n',)]

Based on the psql response above, the expected value was 'test++\n' but the actual value was 'test+\n'.

It is true that the original insert had one plus sign, but if psql is to be believed the value in the database now has two. This was how I originally noticed the problem; in a table I had a value which psql reports having just one plus sign, but psycopg2 strips it and returns a value with none.

I'm happy to provide any other information necessary.

Best Regards,

Jon Wedell

On 2019-Mar-12, Jon Wedell wrote:

> postgres=# select * from test;
>  inchi
> -------
>  test++
>  

This is just psql showing a literal "+" as continuation character,
indicating that the field contains a newline.  Try changing "\pset
format" to something different.

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Jon Wedell <wedell@bmrb.wisc.edu> writes:
> postgres=# create table test (inchi text);
> CREATE TABLE
> postgres=# insert into test values ('test+
> postgres'# ');
> INSERT 0 1
> postgres=# select * from test;
>  inchi
> -------
>  test++
>  
> (1 row)

Well, that one is not a bug.  The character value you inserted is
"t e s t + newline", and when psql renders a value including a
newline, by default it puts a plus at the end of the preceding line.
You can alter that behavior with psql's various \pset options, I believe.

> The second bug is that pyscopg2-binary is stripping a plus sign at the
> end of the value out.

I don't use pyscopg2, but I suspect that you're confusing
the decorative "+" shown by psql with actual data.

            regards, tom lane

🤦

Thanks guys, this was the source of my confusion.

Best,
Jon

> On 2019-Mar-12, Jon Wedell wrote:
>
>> postgres=# select * from test;
>>  inchi
>> -------
>>  test++
>>  
> This is just psql showing a literal "+" as continuation character,
> indicating that the field contains a newline.  Try changing "\pset
> format" to something different.
>