Re: bug found in the dump/restore process

Hey Cristvo,
I've met this bug also. I wrote a python script (included below) to cleanup
the pg_dump files. Please read the 'Usage Notes' below. I dump my schema
and data separately, so I run this script against the schema file. It adds any
missing double quotes to the CREATE TABLE statements.
Good Luck,
David Huttleston Jr

#!/usr/bin/env python
# nextval_bugfix.py
# This fixes the sql scripts dumped by pg_dump.

# The Bug this script fixes:
# pg_dump forgets to double-quote the names of sequences
# Since postgresql identifiers are case-sensitive, this
# causes fatal errors when the sql script is run.

# David Huttleston Jr
# mailto: dhjr(at)hddesign(dot)com
# licence: copy away

# Usage:
# python nextval_bugfix "sql_file(s)"

# Usage Notes:
# Wildcards are fine, but if you use them surround the
# parameter in double-quotes. This forces Bash to
# let python do the unglobbing.
# Do NOT try to use this on large pg_dump files, because
# the whole file is read into memory.
# I dump my structure separate from my data, so this
# was written to run against the small structure files.

# Outline:
# 1) search for nextval(
# 2) use string.split() on single quotes to check for lack
# of inner double quotes
# 3) if the bug is found
# a) rename the dump file
# b) dump the modified to the output file
# c) show changes using a diff command

import string, sys, os, os.path, glob

def fixfile(infile):
fscan = open(infile)
isFixed = 0
fixedLines = []

r = fscan.readline()
#r = string.rstrip( fscan.readline() )
while r:
d = r
if string.find(r,filter) != -1:
s = string.split(r, "'")
if s[1][0]!='"':
s[1] = '"' + s[1] + '"'
d = string.join(s, "'")
isFixed = 1
fixedLines.append( d )
r = fscan.readline()

fscan.close()
if isFixed:
return fixedLines
else:
return None

def show_usage():
print 'usage: python ' + sys.argv[0] + ' "sql_file(s)"'
sys.exit()

#------------------#
# start of program #
#------------------#
if len( sys.argv ) <> 2:
show_usage()

files = glob.glob( sys.argv[1] )

#------------#
# main logic #
#------------#

filter = 'nextval('
nfilter = len( filter )

for filename in files:

fixed = fixfile(filename)
if fixed:
bkfilename = filename + '.bak'
print "\nMoving " + filename + " to " + bkfilename + "...",
os.rename(filename, bkfilename)
print "Done."
fout = open(filename, 'w')
fout.writelines( fixed )
fout.close()
# show changes using a diff command
print filename + " is now built with pg_dump bug fixed"
os.system( 'diff ' + bkfilename + ' ' + filename )

On Wed, 4 Oct 2000 21:39:12 -0300, you wrote:
> Here's the story:
>
> * I created a table with a SERIAL type
> * I decided i'd rather insert the ids manually, so I dropped the sequence
> * when trying to restore from backup, psql fails because there's no
> sequence, and pg_dump created the table definition as if the sequence was
> still there
> * I removed the "default nextval ('xxx_seq'::text)" from the table
> definition, now restore works correctly.
>
> Here's a snippet from the dumpfile:
>
> CREATE TABLE "product" (
> "id" int4 DEFAULT nextval('product_id_seq'::text) NOT NULL,
> "name" text,
> PRIMARY KEY ("id")
> );
>
> The sequence 'product_id_seq' is the one which was dropped. The restore
> fails with "Invalid command: \N" when it reaches the first COPY FROM, which
> refers to another table.
>
> BTW, the bugtool page in the postgres website has blanks where it should be
> telling us where to post bug reports.
>
>