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.
