A tsvector can make database dumps unrestorable

[Stuart Bishop]

Binary package hint: postgresql-8.2

Attached is a script that after being run against a freshly created database, will cause dumps of that database to be unrestorable using pg_restore.

[Stuart Bishop (stub)]

Minimal example engineered from real world case.

[Christian Reis (kiko)]

I ran this on 8.2.5. When inserting I get this:

foo=# INSERT INTO Foo(bar) VALUES ('''0'':58A ''1'':92A,127A ''2'':5A ''e'':146A,151A ''n'':148A ''r'':147A ''10'':51A ''12'':50A ''45'':52A ''ad'':149A ''id'':32A ''ip'':86A ''nt'':68A ''200'':57A ''5.0'':69A ''5.5'':66A ''bug'':6A,12A ''e.g'':145A ''get'':54A ''one'':11A,17A ''ran'':9A ''tri'':95A ''use'':119A,138A ''255c'':61A ''8303'':38A,94A,133A ''blob'':35A,130A ''give'':106A ''hint'':137A ''hole'':75A ''line'':104A,126A ''msie'':65A ''null'':93A ''psql'':102A ''tool'':105A ''valu'':37A,132A ''warn'':116A,117A,167A ''zope'':4A,45A,81A ''+0200'':53A ''along'':111A ''error'':26A,28A,109A,163A ''escap'':121A,140A,144A ''extra'':115A ''found'':40A,135A ''liter'':125A ''secur'':78A ''start'':154A ''anonym'':48A ''compat'':64A ''execut'':97A ''follow'':42A,114A ''import'':7A ''insert'':20A,29A ''is/was'':84A ''logfil'':46A ''messag'':36A,110A,131A ''ncould'':71A ''postgr'':14A ''result'':158A ''server'':90A ''string'':124A,141A,157A ''syntax'':27A,142A,162A ''window'':67A ''address'':87A ''command'':103A ''content'':33A,128A ''generat'':22A ''sequenc'':34A,129A ''t312461'':70A ''webserv'':82A ''without'':165A ''/scripts'':60A ''complain'':15A ''http/1.1'':56A ''sqlobject'':24A ''statement'':21A,99A ''02/may/2002'':49A ''mozilla/4.0'':63A ''nonstandard'':118A ''213.46.5.117'':59A,83A ''messagechunk'':31A ''/p_/zopebutton'':55A ''programmingerror'':25A ''nystrom.no-ip.org'':91A ''np3ee224f1.dip.t-dialin.net'':47A ''255cwinnt/system32/cmd.exe?/c+dir+c:\\\\'':62A');
WARNING: nonstandard use of \\ in a string literal
LINE 1: INSERT INTO Foo(bar) VALUES ('''0'':58A ''1'':92A,127A ''2''...
                                     ^
HINT: Use the escape string syntax for backslashes, e.g., E'\\'.
INSERT 0 1
foo=#

The dump itself runs fine. When restoring, I get this:

kiko@whatever:~$ pg_restore -d foo dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 1791; 0 282910 TABLE DATA foo kiko
pg_restore: [archiver (db)] COPY failed: ERROR: syntax error
CONTEXT: COPY foo, line 1, column bar: "'0':58A '1':92A,127A '2':5A 'e':146A,151A 'n':148A 'r':147A '10':51A '12':50A '45':52A 'ad':149A 'id..."
WARNING: errors ignored on restore: 1

[Stuart Bishop (stub)]

Reported upstream:

http://archives.postgresql.org/pgsql-bugs/2007-09/msg00165.php

[Martin Pitt (pitti)]

This was fixed upstream in CVS head. 8.2.6 will be released soon.

[Martin Pitt (pitti)]

This is fixed in Hardy.

[Martin Pitt (pitti)]

BTW, this is also fixed in the 8.2 dapper backport that I provide in my PPA: https://launchpad.net/~pitti/+archive

{feisty,gutsy}-proposed also have 8.2.6 with the fix, it'll get to -security soon.