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

Commitceb850d

Browse files
committed
Fix "ANALYZE t, t" inside a transaction block.
This failed with either "tuple already updated by self" or "duplicatekey value violates unique constraint", depending on whether the tablehad previously been analyzed or not. The reason is that ANALYZE triedto insert or update the same pg_statistic rows twice, and there was noCommandCounterIncrement between. So add one. The same case works fineoutside a transaction block, because then there's a whole transactionboundary between, as a consequence of the way VACUUM works.This issue has been latent all along, but the problem was unreachablebefore commit11d8d72 added the ability to specify multiple tablesin ANALYZE. We could, perhaps, alternatively fix it by adding code tode-duplicate the list of VacuumRelations --- but that would add alot of overhead to work around dumb commands, so it's not attractive.Per bug #15946 from Yaroslav Schekin. Back-patch to v11.(Note: in v11 I also back-patched the test added by commit2322456;otherwise the problem doesn't manifest in the test I added, because"vactst" is empty when the tests for multiple ANALYZE targets arereached. That seems like not a very good thing anyway, so I did thisrather than rethinking the choice of test case.)Discussion:https://postgr.es/m/15946-5c7570a2884a26cf@postgresql.org
1 parent2f729d8 commitceb850d

File tree

3 files changed

+42
-0
lines changed

3 files changed

+42
-0
lines changed

‎src/backend/commands/vacuum.c

Lines changed: 9 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -362,6 +362,15 @@ vacuum(int options, List *relations, VacuumParams *params,
362362
PopActiveSnapshot();
363363
CommitTransactionCommand();
364364
}
365+
else
366+
{
367+
/*
368+
* If we're not using separate xacts, better separate the
369+
* ANALYZE actions with CCIs. This avoids trouble if user
370+
* says "ANALYZE t, t".
371+
*/
372+
CommandCounterIncrement();
373+
}
365374
}
366375
}
367376
}

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

Lines changed: 16 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -71,6 +71,18 @@ ANALYZE vaccluster;
7171
ERROR: ANALYZE cannot be executed from VACUUM or ANALYZE
7272
CONTEXT: SQL function "do_analyze" statement 1
7373
SQL function "wrap_do_analyze" statement 1
74+
-- Test ANALYZE in transaction, where the transaction surrounding
75+
-- analyze performed modifications. This tests for the bug at
76+
-- https://postgr.es/m/c7988239-d42c-ddc4-41db-171b23b35e4f%40ssinger.info
77+
-- (which hopefully is unlikely to be reintroduced), but also seems
78+
-- independently worthwhile to cover.
79+
INSERT INTO vactst SELECT generate_series(1, 300);
80+
DELETE FROM vactst WHERE i % 7 = 0; -- delete a few rows outside
81+
BEGIN;
82+
INSERT INTO vactst SELECT generate_series(301, 400);
83+
DELETE FROM vactst WHERE i % 5 <> 0; -- delete a few rows inside
84+
ANALYZE vactst;
85+
COMMIT;
7486
VACUUM FULL pg_am;
7587
VACUUM FULL pg_class;
7688
VACUUM FULL pg_database;
@@ -112,6 +124,10 @@ ANALYZE vactst, does_not_exist, vacparted;
112124
ERROR: relation "does_not_exist" does not exist
113125
ANALYZE vactst (i), vacparted (does_not_exist);
114126
ERROR: column "does_not_exist" of relation "vacparted" does not exist
127+
ANALYZE vactst, vactst;
128+
BEGIN; -- ANALYZE behaves differently inside a transaction block
129+
ANALYZE vactst, vactst;
130+
COMMIT;
115131
-- parenthesized syntax for ANALYZE
116132
ANALYZE (VERBOSE) does_not_exist;
117133
ERROR: relation "does_not_exist" does not exist

‎src/test/regress/sql/vacuum.sql

Lines changed: 17 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -54,6 +54,19 @@ CREATE INDEX ON vaccluster(wrap_do_analyze(i));
5454
INSERT INTO vacclusterVALUES (1), (2);
5555
ANALYZE vaccluster;
5656

57+
-- Test ANALYZE in transaction, where the transaction surrounding
58+
-- analyze performed modifications. This tests for the bug at
59+
-- https://postgr.es/m/c7988239-d42c-ddc4-41db-171b23b35e4f%40ssinger.info
60+
-- (which hopefully is unlikely to be reintroduced), but also seems
61+
-- independently worthwhile to cover.
62+
INSERT INTO vactstSELECT generate_series(1,300);
63+
DELETEFROM vactstWHERE i %7=0;-- delete a few rows outside
64+
BEGIN;
65+
INSERT INTO vactstSELECT generate_series(301,400);
66+
DELETEFROM vactstWHERE i %5<>0;-- delete a few rows inside
67+
ANALYZE vactst;
68+
COMMIT;
69+
5770
VACUUM FULL pg_am;
5871
VACUUM FULL pg_class;
5972
VACUUM FULL pg_database;
@@ -88,6 +101,10 @@ ANALYZE vactst, vacparted;
88101
ANALYZE vacparted (b), vactst;
89102
ANALYZE vactst, does_not_exist, vacparted;
90103
ANALYZE vactst (i), vacparted (does_not_exist);
104+
ANALYZE vactst, vactst;
105+
BEGIN;-- ANALYZE behaves differently inside a transaction block
106+
ANALYZE vactst, vactst;
107+
COMMIT;
91108

92109
-- parenthesized syntax for ANALYZE
93110
ANALYZE (VERBOSE) does_not_exist;

0 commit comments

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