Re: bug found in the dump/restore process

Hey Crist�v�o,
    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@hddesign.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.
>
>