Bad dumps...

[Stef]

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]

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 was to write a short script replaced the offending characters with an empty string which is different to NULL. The other alternative 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 this column will cause the db to insert an empty string rather than leaving it as NULL.)

Hilary

Post by Stef
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 [phone] Fax [phone]
DMR is a UK registered trade mark of DMR Limited
**********************************************************


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Hilary Forbes]

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

Maybe I'm missing the point here!

Hilary

=> 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 was to write a short script replaced the offending characters with an empty string which is different to NULL. The other alternative 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.
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 [phone] Fax [phone]
DMR is a UK registered trade mark of DMR Limited
**********************************************************


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

[Stef]

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 into the database in the first place? Why not get rid of them in your UPDATE statement using the replace statement in the 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]

That could be a bug. How are you dumping the data? pg_dump? Select
query? How are you restoring the data? psql?

Post by Stef
Oops, my <Reply all> button doesn't work...
=> Can we go back to the beginning here?! If you are doing updates to remove the \N, why are you allowing them to get into the database in the first place? Why not get rid of them in your UPDATE statement using the replace statement in the 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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stef]

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]

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.
=>

[Tom Lane]

Post by Stef
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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Stef]

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
[***@p0 postgres]# pg_dump p0 -U postgres -t text_test > text_test.sql
[***@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
[***@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
[***@p0 postgres]#

[Tom Lane]

Post by Stef
...
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org