Re: bug found in the dump/restore process
От | David Huttleston Jr |
---|---|
Тема | Re: bug found in the dump/restore process |
Дата | |
Msg-id | 200010050421.XAA32457@proxy.hddesign.com обсуждение исходный текст |
Ответ на | bug found in the dump/restore process (Cristóvão Dalla Costa <cdallacosta@bigfoot.com>) |
Список | pgsql-admin |
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. > >
В списке pgsql-admin по дате отправления: