pg_upgrade problem - Mailing list pgsql-hackers

From Oliver Elphick
Subject pg_upgrade problem
Date
Msg-id 199906072117.WAA07790@linda.lfix.co.uk
Whole thread Raw
Responses Re: [HACKERS] pg_upgrade problem
Re: [HACKERS] pg_upgrade problem
List pgsql-hackers
I am updating a database from 6.4 to 6.5.  This statement was produced
by 6.4.2's pg_dumpall but is not accepted by 6.5:
 CREATE RULE "rtest_v1_upd" AS ON UPDATE TO "rtest_v1"     DO INSTEAD UPDATE rtest_t1 SET "a" = new."a", "b" = new."b"
 WHERE "a" = current."a";
 
 ERROR:  current: Table does not exist.

This rule is part of the regression tests and it seems that `current' has
now been changed to `old'.  Apart from the error messages that occur
as the script flows past (too fast to read) pg_upgrade does not
offer any explanations; it merely says that psql failed to run the
script, which is contrary to what the user (me) thinks he has just seen.

This particular problem can be fixed by applying this sed command to
the script:
 sed -e '/^CREATE RULE /s/current\./old./g'

with some smallish risk of breaking rules where some table has a name
ending in `current'.


I'm currently trying to fix the automatic upgrade procedure for Debian's
postgresql package.  Are there any other problems like this that people
might meet?




I attach a patch for pg_upgrade.  This does two things:

1. check whether the program is being executed in $PGDATA/..  This is  necessary if the data tree is not in the
standardplace, as is the  case with the Debian distribution (because of Debian policy).
 

2. give a clearer error message if the dumped data structure fails to  be loaded.

===================== start patch =================
*** src/bin/pg_dump/pg_upgrade~    Mon Jun  7 22:13:00 1999
--- src/bin/pg_dump/pg_upgrade    Mon Jun  7 22:11:13 1999
***************
*** 29,36 ****  # check things 
! if [ ! -f "./lib/global1.bki.source" ]
! then    echo "$0 must be run from the top of the postgres directory tree." 1>&2     exit 1 fi 
--- 29,37 ----  # check things 
! if [ ! -f "./data/PG_VERSION" ]
! then    echo "`basename $0` must be run from the directory containing
! the database directory \`data' (`dirname $PGDATA`.)" 1>&2     exit 1 fi 
***************
*** 72,78 **** psql "template1" <"/tmp/$$"  if [ $? -ne 0 ]
! then    echo "psql failed to execute $INPUT script." 1>&2     exit 1 fi 
--- 73,80 ---- psql "template1" <"/tmp/$$"  if [ $? -ne 0 ]
! then    echo "There were errors in the input script $INPUT.
! $0 aborted." 1>&2     exit 1 fi 
===================== end patch =================

-- 
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver              PGP key from public servers; key
ID32B8FAA1                ========================================    "There is a way that seems right to a man, but in
the     end it leads to death."                                              Proverbs 16:25 
 




pgsql-hackers by date:

Previous
From: "Jackson, DeJuan"
Date:
Subject: RE: [HACKERS] inet type & select
Next
From: Kaare Rasmussen
Date:
Subject: Re: [HACKERS] PostgreSQL History(Parody)