Skip site navigation (1) Skip section navigation (2)

Column rename in an extension update script

From: Philippe BEAUDOIN <phb(dot)emaj(at)free(dot)fr>
To: pgsql-general(at)postgresql(dot)org
Subject: Column rename in an extension update script
Date: 2017-05-01 06:54:56
Message-ID: 2cf7941e-4e41-7714-3de8-37b1a8f74dff@free.fr (view raw, whole thread or download thread mbox)
Thread:
Lists: pgsql-generalpgsql-hackers
Hi all,

I am coding an update script for an extension. And I am in trouble when 
trying to rename a column of an existing table.

Just after the ALTER TABLE statement, I want to access this table. But 
at this time, the altered column is not visible with its new name.

I wrote a simple test case to show this. Here is the shell script that 
can be easily adapted.

# issue in postgres extension when trying to access a column that has 
been renamed inside an extension update script
#
export EXTDIR="/tmp"
export PGDIR="/usr/local/pg962/share/postgresql/extension"
export PGHOST=localhost
export PGPORT=5496
export PGDATABASE='postgres'

echo "create files for the extension"
echo "------------------------------"
cat >$EXTDIR/myextension.control <<*END*
default_version        = '1'
directory            = '$EXTDIR'
*END*
sudo ln -s $EXTDIR/myextension.control $PGDIR/myextension.control

cat >$EXTDIR/myextension--1.sql <<*END*
CREATE TABLE mytable (col_old INT);
*END*

cat >$EXTDIR/myextension--1--2.sql <<*END*
ALTER TABLE mytable RENAME col_old TO col_new;
UPDATE mytable SET col_new = 0;
*END*

echo "psql: run the test ==> FAILS"
echo "----------------------------"
psql -a <<*END*
select version();
CREATE EXTENSION myextension VERSION '1';
ALTER EXTENSION myextension UPDATE TO '2';
DROP EXTENSION IF EXISTS myextension;
*END*

echo "psql: similar statements outside extension ==> WORKS"
echo "----------------------------------------------------"
psql -a <<*END*
CREATE TABLE mytable (col_old INT);
BEGIN;
   ALTER TABLE mytable RENAME col_old TO col_new;
   UPDATE mytable SET col_new = 0;
COMMIT;
DROP TABLE IF EXISTS mytable;
*END*

sudo rm $PGDIR/myextension.control
rm $EXTDIR/myextension*

And here is the result:

create files for the extension
------------------------------
psql: run the test ==> FAILS
----------------------------
select version();
version
-----------------------------------------------------------------------------------------------------------------
  PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit
(1 row)

CREATE EXTENSION myextension VERSION '1';
CREATE EXTENSION
ALTER EXTENSION myextension UPDATE TO '2';
ERROR:  column "col_new" of relation "mytable" does not exist
DROP EXTENSION IF EXISTS myextension;
DROP EXTENSION
psql: similar statements outside extension ==> WORKS
----------------------------------------------------
CREATE TABLE mytable (col_old INT);
CREATE TABLE
BEGIN;
BEGIN
   ALTER TABLE mytable RENAME col_old TO col_new;
ALTER TABLE
   UPDATE mytable SET col_new = 0;
UPDATE 0
COMMIT;
COMMIT
DROP TABLE IF EXISTS mytable;
DROP TABLE

As you can see:

- the error message is "ERROR:  column "col_new" of relation "mytable" 
does not exist", while the ALTER TABLE statement doesn't return any error,

- the same statements in a simple psql script works fine,

- I reproduce this with all supported postgres versions.

As a workaround, I perform the UPDATE statement before the ALTER TABLE 
operation, using of course the old column name.

I probably do something wrong. But I can't see what.

Thanks by advance for any piece of advise.

Best regards. Philippe Beaudoin.


Responses

pgsql-hackers by date

Next:From: Andres FreundDate: 2017-05-01 08:03:48
Subject: Re: snapbuild woes
Previous:From: Amit KapilaDate: 2017-05-01 05:30:10
Subject: Re: OK, so culicidae is *still* broken

pgsql-general by date

Next:From: Amitabh KantDate: 2017-05-01 09:57:39
Subject: Re: all serial type was changed to 1
Previous:From: Peter GeogheganDate: 2017-05-01 06:09:44
Subject: Re: all serial type was changed to 1

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group