Обсуждение: [GENERAL] Column rename in an extension update script
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.
On 04/30/2017 11:54 PM, Philippe BEAUDOIN wrote: > 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. > > From the error it looks to me like the statements are each run in a separate session and the UPDATE is not seeing the ALTER TABLE. A quick search of the source indicates this is handled in extension.c: https://git.postgresql.org/gitweb/?p=postgresql.git;a=blob;f=src/backend/commands/extension.c;h=33b0de0a7657298729ad5c3b185dc2f4aab0bb73;hb=6a18e4bc2d13d077c52cf90a4c6ec68343808ba7 In particular execute_sql_string line 684. I do not understand C well enough to figure out if the above is actually creating separate sessions or not. Maybe you understand it or someone else can chime in. > > # 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. > > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 04/30/2017 11:54 PM, Philippe BEAUDOIN wrote: >> 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. > From the error it looks to me like the statements are each run in a > separate session and the UPDATE is not seeing the ALTER TABLE. No, it's in the same session; the problem is the lack of a CommandCounterIncrement call between the ALTER's update and the parsing of the next statement. That means the update isn't visible yet, even in its own session. See the fix here: https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9209e07605afe0349660447f20d83ef165cdd0ae regards, tom lane
Le 03/05/2017 à 19:29, Tom Lane a écrit : > Adrian Klaver <adrian.klaver@aklaver.com> writes: >> On 04/30/2017 11:54 PM, Philippe BEAUDOIN wrote: >>> 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. >> From the error it looks to me like the statements are each run in a >> separate session and the UPDATE is not seeing the ALTER TABLE. > No, it's in the same session; the problem is the lack of a > CommandCounterIncrement call between the ALTER's update and the parsing > of the next statement. That means the update isn't visible yet, > even in its own session. See the fix here: > > https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=9209e07605afe0349660447f20d83ef165cdd0ae > > regards, tom lane Thanks Tom for the fix. And thanks to Julien and Adrian too, for the time spent on this issue. Regards.