pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedColumn rename in an extension update script
6+ messages / 4 participants
[nested] [flat]
Commits from this thread:
2017-05-02 d56b8b41b3 d0d3a57bfa c9d6c564f4 c6b3d07061 062824edd9 9209e07605 Ensure commands in extension scripts see the results of preceding DDL.
* Column rename in an extension update script
@ 2017-05-01 06:54 Philippe BEAUDOIN <phb.emaj@free.fr>
2017-05-02 19:45 ` Re: [GENERAL] Column rename in an extension update script Julien Rouhaud <julien.rouhaud@dalibo.com>
2017-05-03 17:10 ` Re: Column rename in an extension update script Adrian Klaver <adrian.klaver@aklaver.com>
0 siblings, 2 replies; 6+ messages in thread
From: Philippe BEAUDOIN @ 2017-05-01 06:54 UTC (permalink / raw)
To: pgsql-general
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Column rename in an extension update script
2017-05-01 06:54 Column rename in an extension update script Philippe BEAUDOIN <phb.emaj@free.fr>
@ 2017-05-02 19:45 ` Julien Rouhaud <julien.rouhaud@dalibo.com>
2017-05-02 21:23 ` Re: [GENERAL] Column rename in an extension update script Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Julien Rouhaud @ 2017-05-02 19:45 UTC (permalink / raw)
To: Philippe BEAUDOIN <phb.emaj@free.fr>; pgsql-hackers <pgsql-hackers@postgresql.org>
moving this thread to -hackers, since this looks like a bug.
On 01/05/2017 08:54, 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.
>
I can reproduce this issue.
It looks like this is due to missing cache invalidation between the
ALTER TABLE execution and next query planning (failure happens during
pg_analyze_and_rewrite()).
AFAICT, CommandCounterIncrement() is responsible for handling
invalidation messages, but in execute_sql_string() this function is
called before executing a Stmt instead of after. Moving the
CommandCounterIncrement() just before the PopActiveSnapshot() call (and
removing the final one) fixes this issue for me, but I have no idea if
this is the right fix.
--
Julien Rouhaud
http://dalibo.com - http://dalibo.org
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: [GENERAL] Column rename in an extension update script
2017-05-01 06:54 Column rename in an extension update script Philippe BEAUDOIN <phb.emaj@free.fr>
2017-05-02 19:45 ` Re: [GENERAL] Column rename in an extension update script Julien Rouhaud <julien.rouhaud@dalibo.com>
@ 2017-05-02 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2017-05-02 21:23 UTC (permalink / raw)
To: Julien Rouhaud <julien.rouhaud@dalibo.com>; +Cc: Philippe BEAUDOIN <phb.emaj@free.fr>; pgsql-hackers <pgsql-hackers@postgresql.org>
Julien Rouhaud <julien.rouhaud@dalibo.com> writes:
> moving this thread to -hackers, since this looks like a bug.
> On 01/05/2017 08:54, Philippe BEAUDOIN wrote:
>> 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 can reproduce this issue.
> It looks like this is due to missing cache invalidation between the
> ALTER TABLE execution and next query planning (failure happens during
> pg_analyze_and_rewrite()).
Yes. Kind of surprising nobody noticed this before --- you'd certainly
think that extension scripts would have lots of cases of statements
depending on DDL done just before them. I think it must have been masked
by the fact that many DDL commands *end* with CommandCounterIncrement,
or at least have one pretty close to the end. But not renameatt().
> AFAICT, CommandCounterIncrement() is responsible for handling
> invalidation messages, but in execute_sql_string() this function is
> called before executing a Stmt instead of after. Moving the
> CommandCounterIncrement() just before the PopActiveSnapshot() call (and
> removing the final one) fixes this issue for me, but I have no idea if
> this is the right fix.
I'm inclined to add one before the parse step, rather than removing any.
The sequence of bump-the-command-counter-then-capture-a-snapshot is
pretty well established in places like spi.c, so I don't want to change
that usage. Also, I think part of the point here was to ensure that
any DDL done *before* reaching execute_sql_string() is visible to the
first command. Also note the assumption in ApplyExtensionUpdates that
execute_sql_string will do at least one CommandCounterIncrement, even
if the script is empty. A CCI that has nothing to do is quite cheap,
and we're not that worried about performance here anyway IMO, so I'd
just as soon err on the side of having more than enough CCIs.
regards, tom lane
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Column rename in an extension update script
2017-05-01 06:54 Column rename in an extension update script Philippe BEAUDOIN <phb.emaj@free.fr>
@ 2017-05-03 17:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-03 17:29 ` Re: Column rename in an extension update script Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 6+ messages in thread
From: Adrian Klaver @ 2017-05-03 17:10 UTC (permalink / raw)
To: Philippe BEAUDOIN <phb.emaj@free.fr>; pgsql-general
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=33b0...
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Column rename in an extension update script
2017-05-01 06:54 Column rename in an extension update script Philippe BEAUDOIN <phb.emaj@free.fr>
2017-05-03 17:10 ` Re: Column rename in an extension update script Adrian Klaver <adrian.klaver@aklaver.com>
@ 2017-05-03 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-03 17:36 ` Re: Column rename in an extension update script Philippe BEAUDOIN <phb.emaj@free.fr>
0 siblings, 1 reply; 6+ messages in thread
From: Tom Lane @ 2017-05-03 17:29 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Philippe BEAUDOIN <phb.emaj@free.fr>; pgsql-general
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=9209e07605afe0349660447f20d83ef16...
regards, tom lane
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Column rename in an extension update script
2017-05-01 06:54 Column rename in an extension update script Philippe BEAUDOIN <phb.emaj@free.fr>
2017-05-03 17:10 ` Re: Column rename in an extension update script Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-03 17:29 ` Re: Column rename in an extension update script Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-05-03 17:36 ` Philippe BEAUDOIN <phb.emaj@free.fr>
0 siblings, 0 replies; 6+ messages in thread
From: Philippe BEAUDOIN @ 2017-05-03 17:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: pgsql-general
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=9209e07605afe0349660447f20d83ef16...
>
> regards, tom lane
Thanks Tom for the fix. And thanks to Julien and Adrian too, for the
time spent on this issue.
Regards.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2017-05-03 17:36 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-01 06:54 Column rename in an extension update script Philippe BEAUDOIN <phb.emaj@free.fr>
2017-05-02 19:45 ` Julien Rouhaud <julien.rouhaud@dalibo.com>
2017-05-02 21:23 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-03 17:10 ` Adrian Klaver <adrian.klaver@aklaver.com>
2017-05-03 17:29 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-03 17:36 ` Philippe BEAUDOIN <phb.emaj@free.fr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox