Обсуждение: 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/