Thread: bug found in the dump/restore process
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.
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.
>
>
Hello ,
The following is ok :
$ psql -h localhost -U postgres
$ psql -h 127.0.0.1 -U postgres
but this don't work :
$ psql -U postgres
psql: connectDBStart() -- connect() failed: Connection refused
Is the postmaster running at 'localhost'
and accepting connections on Unix socket '5432'?
The pg_hba.conf is the same since a long time :
local all
password passwdpgsql
host all 127.0.0.1 255.255.255.255 password passwdpgsql
The only thing that have change it's that ssh have been installed ... and ....
Do you see a exlplication ?
PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2 (on a Potatoe )
Thanks
Loïc TREGOUËT writes: > The following is ok : > $ psql -h localhost -U postgres > $ psql -h 127.0.0.1 -U postgres > > but this don't work : > $ psql -U postgres > psql: connectDBStart() -- connect() failed: Connection refused > Is the postmaster running at 'localhost' > and accepting connections on Unix socket '5432'? Check that there's a socket file /tmp/.s.PSQL.5432. Maybe it got deleted accidentally. Restart the postmaster to create a new one. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Hie,
Why the folowing is not accepted on PostgreSQL 7.0 but accepted on
PostgreSQL 6.5.3 ?
insert into tempo select id, name from topics order by name;
ERROR: ORDER BY is not allowed in INSERT/SELECT
Thanks
Hie,
First , thanks to you . It's exact that i've lost this socket
file .
On my other pc , this locate in /var/run/postgresql .
But , i've restarted postmaster and i have no refund my file
Any idea ? thanks
Loic
Peter Eisentraut wrote:
>
> Loïc TREGOUËT writes:
>
> > The following is ok :
> > $ psql -h localhost -U postgres
> > $ psql -h 127.0.0.1 -U postgres
> >
> > but this don't work :
> > $ psql -U postgres
> > psql: connectDBStart() -- connect() failed: Connection refused
> > Is the postmaster running at 'localhost'
> > and accepting connections on Unix socket '5432'?
>
> Check that there's a socket file /tmp/.s.PSQL.5432. Maybe it got deleted
> accidentally. Restart the postmaster to create a new one.
>
> --
> Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/
Loïc TREGOUËT writes: > insert into tempo select id, name from topics order by name; > ERROR: ORDER BY is not allowed in INSERT/SELECT What's the point of this command? The values in "tempo" are going to end up in random order anyway. -- Peter Eisentraut peter_e@gmx.net http://yi.org/peter-e/