Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit0d18852

Browse files
committed
Disallow CREATE INDEX if table is already in use in current session.
If we allow this, whatever outer command has the table open will not knowabout the new index and may fail to update it as needed, as shown in areport from Laurenz Albe. We already had such a prohibition in place forALTER TABLE, but the CREATE INDEX syntax missed the check.Fixing it requires an API change for DefineIndex(), which conceivablywould break third-party extensions if we were to back-patch it. Givenhow long this problem has existed without being noticed, fixing it inthe back branches doesn't seem worth that risk.Discussion:https://postgr.es/m/A737B7A37273E048B164557ADEF4A58B53A4DC9A@ntex2010i.host.magwien.gv.at
1 parent55a70a0 commit0d18852

File tree

7 files changed

+78
-0
lines changed

7 files changed

+78
-0
lines changed

‎src/backend/bootstrap/bootparse.y

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -323,6 +323,7 @@ Boot_DeclareIndexStmt:
323323
$4,
324324
false,
325325
false,
326+
false,
326327
true,/* skip_build*/
327328
false);
328329
do_end();
@@ -366,6 +367,7 @@ Boot_DeclareUniqueIndexStmt:
366367
$5,
367368
false,
368369
false,
370+
false,
369371
true,/* skip_build*/
370372
false);
371373
do_end();

‎src/backend/commands/indexcmds.c

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -295,6 +295,9 @@ CheckIndexCompatible(Oid oldId,
295295
* 'is_alter_table': this is due to an ALTER rather than a CREATE operation.
296296
* 'check_rights': check for CREATE rights in namespace and tablespace. (This
297297
*should be true except when ALTER is deleting/recreating an index.)
298+
* 'check_not_in_use': check for table not already in use in current session.
299+
*This should be true unless caller is holding the table open, in which
300+
*case the caller had better have checked it earlier.
298301
* 'skip_build': make the catalog entries but leave the index file empty;
299302
*it will be filled later.
300303
* 'quiet': suppress the NOTICE chatter ordinarily provided for constraints.
@@ -307,6 +310,7 @@ DefineIndex(Oid relationId,
307310
OidindexRelationId,
308311
boolis_alter_table,
309312
boolcheck_rights,
313+
boolcheck_not_in_use,
310314
boolskip_build,
311315
boolquiet)
312316
{
@@ -404,6 +408,15 @@ DefineIndex(Oid relationId,
404408
(errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
405409
errmsg("cannot create indexes on temporary tables of other sessions")));
406410

411+
/*
412+
* Unless our caller vouches for having checked this already, insist that
413+
* the table not be in use by our own session, either. Otherwise we might
414+
* fail to make entries in the new index (for instance, if an INSERT or
415+
* UPDATE is in progress and has already made its list of target indexes).
416+
*/
417+
if (check_not_in_use)
418+
CheckTableNotInUse(rel,"CREATE INDEX");
419+
407420
/*
408421
* Verify we (still) have CREATE rights in the rel's namespace.
409422
* (Presumably we did when the rel was created, but maybe not anymore.)

‎src/backend/commands/tablecmds.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6679,6 +6679,7 @@ ATExecAddIndex(AlteredTableInfo *tab, Relation rel,
66796679
InvalidOid,/* no predefined OID */
66806680
true,/* is_alter_table */
66816681
check_rights,
6682+
false,/* check_not_in_use - we did it already */
66826683
skip_build,
66836684
quiet);
66846685

‎src/backend/tcop/utility.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1329,6 +1329,7 @@ ProcessUtilitySlow(ParseState *pstate,
13291329
InvalidOid,/* no predefined OID */
13301330
false,/* is_alter_table */
13311331
true,/* check_rights */
1332+
true,/* check_not_in_use */
13321333
false,/* skip_build */
13331334
false);/* quiet */
13341335

‎src/include/commands/defrem.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -27,6 +27,7 @@ extern ObjectAddress DefineIndex(Oid relationId,
2727
OidindexRelationId,
2828
boolis_alter_table,
2929
boolcheck_rights,
30+
boolcheck_not_in_use,
3031
boolskip_build,
3132
boolquiet);
3233
externOidReindexIndex(RangeVar*indexRelation,intoptions);

‎src/test/regress/expected/triggers.out

Lines changed: 29 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1674,6 +1674,35 @@ drop table self_ref_trigger;
16741674
drop function self_ref_trigger_ins_func();
16751675
drop function self_ref_trigger_del_func();
16761676
--
1677+
-- Check that index creation (or DDL in general) is prohibited in a trigger
1678+
--
1679+
create table trigger_ddl_table (
1680+
col1 integer,
1681+
col2 integer
1682+
);
1683+
create function trigger_ddl_func() returns trigger as $$
1684+
begin
1685+
alter table trigger_ddl_table add primary key (col1);
1686+
return new;
1687+
end$$ language plpgsql;
1688+
create trigger trigger_ddl_func before insert on trigger_ddl_table for each row
1689+
execute procedure trigger_ddl_func();
1690+
insert into trigger_ddl_table values (1, 42); -- fail
1691+
ERROR: cannot ALTER TABLE "trigger_ddl_table" because it is being used by active queries in this session
1692+
CONTEXT: SQL statement "alter table trigger_ddl_table add primary key (col1)"
1693+
PL/pgSQL function trigger_ddl_func() line 3 at SQL statement
1694+
create or replace function trigger_ddl_func() returns trigger as $$
1695+
begin
1696+
create index on trigger_ddl_table (col2);
1697+
return new;
1698+
end$$ language plpgsql;
1699+
insert into trigger_ddl_table values (1, 42); -- fail
1700+
ERROR: cannot CREATE INDEX "trigger_ddl_table" because it is being used by active queries in this session
1701+
CONTEXT: SQL statement "create index on trigger_ddl_table (col2)"
1702+
PL/pgSQL function trigger_ddl_func() line 3 at SQL statement
1703+
drop table trigger_ddl_table;
1704+
drop function trigger_ddl_func();
1705+
--
16771706
-- Verify behavior of before and after triggers with INSERT...ON CONFLICT
16781707
-- DO UPDATE
16791708
--

‎src/test/regress/sql/triggers.sql

Lines changed: 31 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1183,6 +1183,37 @@ drop table self_ref_trigger;
11831183
dropfunction self_ref_trigger_ins_func();
11841184
dropfunction self_ref_trigger_del_func();
11851185

1186+
--
1187+
-- Check that index creation (or DDL in general) is prohibited in a trigger
1188+
--
1189+
1190+
createtabletrigger_ddl_table (
1191+
col1integer,
1192+
col2integer
1193+
);
1194+
1195+
createfunctiontrigger_ddl_func() returns triggeras $$
1196+
begin
1197+
altertable trigger_ddl_table addprimary key (col1);
1198+
return new;
1199+
end$$ language plpgsql;
1200+
1201+
createtriggertrigger_ddl_func before inserton trigger_ddl_table for each row
1202+
execute procedure trigger_ddl_func();
1203+
1204+
insert into trigger_ddl_tablevalues (1,42);-- fail
1205+
1206+
create or replacefunctiontrigger_ddl_func() returns triggeras $$
1207+
begin
1208+
createindexon trigger_ddl_table (col2);
1209+
return new;
1210+
end$$ language plpgsql;
1211+
1212+
insert into trigger_ddl_tablevalues (1,42);-- fail
1213+
1214+
droptable trigger_ddl_table;
1215+
dropfunction trigger_ddl_func();
1216+
11861217
--
11871218
-- Verify behavior of before and after triggers with INSERT...ON CONFLICT
11881219
-- DO UPDATE

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp