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