Обсуждение: Bad dumps...

Hi all,

I'm stuck with a problem,about which I couldn't find
much info. I'm sure somebody must have encountered this before.

I've got a "NOT NULL" column of type text.
It happens that freehand data gets inserted into this table, and
it typically contains over 1000 rows with the value '\N' , and
about another 3400 rows that has an occurrence of '\N'

The database dumps fine, but when I import it again, I get
"cannot insert null value into not null column"-type errors,
and if I don't pay close attention,  I end up with an empty table
on the database. There is no way for me to tell how many columns
could be affected in future, but I need to stabilize the backups.

I'm using postgres version 7.3.4

Any idea how to get around this problem?

Kind Regards
Stefan

This is very similar to my problem with the ascii code 13s when I dumped and couldn't load the dumped data.  What I did
wasto write a short script replaced the offending characters with an empty string which is different to NULL.  The
otheralternative is to have 

myfield text DEFAULT '' NOT NULL

in your schema. (The '' bit is two single quotes ie the empty string)  This means anyone failing to insert a value for
thiscolumn will cause the db to insert an empty string rather than leaving it as NULL.) 

Hilary

At 09:57 09/07/2004 +0200, Stef wrote:

>Hi all,
>
>I'm stuck with a problem,about which I couldn't find
>much info. I'm sure somebody must have encountered this before.
>
>I've got a "NOT NULL" column of type text.
>It happens that freehand data gets inserted into this table, and
>it typically contains over 1000 rows with the value '\N' , and
>about another 3400 rows that has an occurrence of '\N'
>
>The database dumps fine, but when I import it again, I get
>"cannot insert null value into not null column"-type errors,
>and if I don't pay close attention,  I end up with an empty table
>on the database. There is no way for me to tell how many columns
>could be affected in future, but I need to stabilize the backups.
>
>I'm using postgres version 7.3.4
>
>Any idea how to get around this problem?
>
>Kind Regards
>Stefan

Hilary Forbes
The DMR Information and Technology Group  (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************

Can we go back to the beginning here?!  If you are doing updates to remove the \N, why are you allowing them to get
intothe database in the first place?  Why not get rid of them in your UPDATE statement using the replace statement in
thefirst place (or dealing with them in your source application before invoking postgres). 

Maybe I'm missing the point here!

Hilary

At 13:03 09/07/2004 +0200, you wrote:

>Hilary Forbes mentioned :
>=> This is very similar to my problem with the ascii code 13s when I dumped and couldn't load the dumped data.  What I
didwas to write a short script replaced the offending characters with an empty string which is different to NULL.  The
otheralternative is to have 
>
>This sounds like a possible solution, but
>people actually insert the two characters : '\' and 'N'
>as '\N' into this column, which is not null, but is seen
>as null by the COPY statement during import. I'm
>just not happy about the fact that literal and valid '\N'
>values become null next time I import the database.
>
>At the moment I'm doing this :
>update person set per_id_no = '' where per_id_no like '%\N%';
>
>I can also clean  the data for this specific column before insertion,
>but not for all other columns in the database where this could possibly happen.
>
>Can I change pg_dump behaviour to dump nulls as a series of characters
>of my choice (as in the COPY statement) and then pg_restore to interpret this
>correctly when importing?
>
>TIA
>Stefan

Hilary Forbes
The DMR Information and Technology Group  (www.dmr.co.uk)
Direct tel 01689 889950 Fax 01689 860330
DMR is a UK registered trade mark of DMR Limited
**********************************************************

Oops, my <Reply all> button doesn't work...

Hilary Forbes mentioned :
=> Can we go back to the beginning here?!  If you are doing updates to remove the \N, why are you allowing them to get
intothe database in the first place?  Why not get rid of them in your UPDATE statement using the replace statement in
thefirst place (or dealing with them in your source application before invoking postgres). 

Well, my point exactly : why can I have these values physically sitting in
the database, and export successfully, but the import cannot import a
successfully exported database.

I have already found two other text columns where the intention
was to have a value of '\N'  (It is an ID code, not the null '\N'), but
the values magically become null when you export and re-import the database.
Also I have no control over the data in these free-hand type text columns.
Users actually decided to put '\N' in there from an application, which I
guess, they should feel free to do, if they want to. But it breaks backups.

Kind Regards
Stefan

That could be a bug.  How are you dumping the data?  pg_dump?  Select
query?  How are you restoring the data?  psql?
On Fri, 2004-07-09 at 09:16, Stef wrote:
> Oops, my <Reply all> button doesn't work...
>
> Hilary Forbes mentioned :
> => Can we go back to the beginning here?!  If you are doing updates to remove the \N, why are you allowing them to
getinto the database in the first place?  Why not get rid of them in your UPDATE statement using the replace statement
inthe first place (or dealing with them in your source application before invoking postgres). 
>
> Well, my point exactly : why can I have these values physically sitting in
> the database, and export successfully, but the import cannot import a
> successfully exported database.
>
> I have already found two other text columns where the intention
> was to have a value of '\N'  (It is an ID code, not the null '\N'), but
> the values magically become null when you export and re-import the database.
> Also I have no control over the data in these free-hand type text columns.
> Users actually decided to put '\N' in there from an application, which I
> guess, they should feel free to do, if they want to. But it breaks backups.
>
> Kind Regards
> Stefan

mike g mentioned :
=> That could be a bug.  How are you dumping the data?  pg_dump?  Select
=> query?  How are you restoring the data?  psql?

Dumping:
pg_dump -Ft | gzip > dump.tgz

Restoring:
zcat dump.tgz | pg_restore -Ft |psql
OR
tar xvfz dump.tgz
perl -pi -e 's/\$\$PATH\$\$/$ENV{PWD}/g' restore.sql
psql -f restore.sql

Both these methods, produce the same result.

I found that it was actually a '\\N' value only that causes
dumps to dump successfully, but fail on import (When using COPY),
because both '\N' and '\\N' are seen as null by the COPY statement.
I just happened to have '\\N' values in my NOT NULL text field.
I now manually use this dump command :
pg_dump  | sed 's:\\\\N:¬:g' | gzip > dump.gz

and do the reverse sed to restore.

Stef mentioned :
=> mike g mentioned :
=> => That could be a bug.  How are you dumping the data?  pg_dump?  Select
=> => query?  How are you restoring the data?  psql?
=>
=> Dumping:
=> pg_dump -Ft | gzip > dump.tgz
=>
=> Restoring:
=> zcat dump.tgz | pg_restore -Ft |psql
=> OR
=> tar xvfz dump.tgz
=> perl -pi -e 's/\$\$PATH\$\$/$ENV{PWD}/g' restore.sql
=> psql -f restore.sql
=>
=> Both these methods, produce the same result.
=>

Stef <svb@ucs.co.za> writes:
> I found that it was actually a '\\N' value only that causes
> dumps to dump successfully, but fail on import (When using COPY),
> because both '\N' and '\\N' are seen as null by the COPY statement.

This is demonstrably not so.  You might have trouble with data coming
from somewhere else, if the source doesn't understand the quoting rules
for COPY data.  But I can dump and restore a table containing '\N' and
variants of that without any trouble.

            regards, tom lane

Tom Lane mentioned :
=> This is demonstrably not so.  You might have trouble with data coming
=> from somewhere else, if the source doesn't understand the quoting rules
=> for COPY data.  But I can dump and restore a table containing '\N' and
=> variants of that without any trouble.

Here's what I did to recreate the problem :
=# create table text_test ( id text NOT NULL);
CREATE TABLE
=# INSERT INTO text_test values ('\\N');
INSERT 37302671 1
=# \q
[root@p0 postgres]# pg_dump p0 -U postgres -t text_test > text_test.sql
[root@p0 postgres]# psql p0 -U postgres
Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

=# drop table text_test ;
DROP TABLE
=# \q
[root@p0 postgres]# cat text_test.sql | psql p0 -U postgres
You are now connected as new user postgres.
SET
CREATE TABLE
ERROR:  copy: line 1, CopyFrom: Fail to add null value in not null attribute id
lost synchronization with server, resetting connection
[root@p0 postgres]#

Stef <svb@ucs.co.za> writes:
> Here's what I did to recreate the problem :
> ...
> Welcome to psql 7.3.4, the PostgreSQL interactive terminal.

Ah.  I was checking it in 7.4.  I thought we'd fixed this issue further
back than 7.4, but some digging in the CVS logs shows not:

2003-10-05 22:38  tgl

    * doc/src/sgml/ref/copy.sgml, src/backend/commands/copy.c: Modify
    COPY FROM to match the null-value string against the column value
    before it is de-backslashed, not after.  This allows the null
    string \N to be reliably distinguished from the data value \N
    (which must be represented as \\N).  Per bug report from Manfred
    Koizar ... but it's amazing this hasn't been reported before ...
    Also, be consistent about encoding conversion for null string: the
    form specified in the command is in the server encoding, but what
    is sent to/from client must be in client encoding.  This never
    worked quite right before either.

            regards, tom lane