Re: Bug involving plus sign before newline in text field being duplicated or 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(at)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(at)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(at)bmrb(dot)wisc(dot)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.
>