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 standard place, 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 ID 32B8FAA1
                 ========================================
     "There is a way that seems right to a man, but in the 
      end it leads to death."       
                                        Proverbs 16:25 



Mon, 07 Jun 1999 22:17:42 +0100
"Oliver Elphick" <olly@lfix.co.uk>
pg_upgrade problem
> 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.

Yikes.  I forgot to disable pg_upgrade in this release.  I don't think
it is going to work because the on-disk tuple structures has changed for
MVCC?  Vadim, an I correct?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 7 Jun 1999 18:56:33 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_upgrade problem
Applied.


> 
> 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 standard place, 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 ID 32B8FAA1
>                  ========================================
>      "There is a way that seems right to a man, but in the 
>       end it leads to death."       
>                                         Proverbs 16:25 
> 
> 
> 
> 


-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@candle.pha.pa.us            |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Mon, 7 Jun 1999 18:59:24 -0400 (EDT)
Bruce Momjian <maillist@candle.pha.pa.us>
Re: [HACKERS] pg_upgrade problem
Bruce Momjian wrote:
> 
> Yikes.  I forgot to disable pg_upgrade in this release.  I don't think
> it is going to work because the on-disk tuple structures has changed for
> MVCC?  Vadim, an I correct?

Not for MVCC, but we get rid t_len from tuple header.

Vadim

Tue, 08 Jun 1999 09:54:48 +0800
Vadim Mikheev <vadim@krs.ru>
Re: [HACKERS] pg_upgrade problem