On Wed, Aug 5, 2015 at 9:31 AM, Robert Haas <robertmh...@gmail.com> wrote:
>
> On Tue, Aug 4, 2015 at 1:15 PM, Alvaro Herrera <alvhe...@2ndquadrant.com>
wrote:
> > That opens up for lock escalation and deadlocks, doesn't it? You are
> > probably thinking that it's okay to ignore those but I don't necessarily
> > agree with that.
>
> Agreed. I think we're making a mountain out of a molehill here. As
> long as the locks that are actually used are monotonic, just use > and
> stick a comment in there explaining that it could need adjustment if
> we use other lock levels in the future. I presume all the lock-levels
> used for DDL are, and will always be, self-exclusive, so why all this
> hand-wringing?
>
New version attached with suggested changes.
Regards,
--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello
diff --git a/doc/src/sgml/ref/alter_table.sgml b/doc/src/sgml/ref/alter_table.sgml
index 1c1c181..ad985cd 100644
--- a/doc/src/sgml/ref/alter_table.sgml
+++ b/doc/src/sgml/ref/alter_table.sgml
@@ -543,6 +543,10 @@ ALTER TABLE ALL IN TABLESPACE <replaceable class="PARAMETER">name</replaceable>
of <command>ALTER TABLE</> that forces a table rewrite.
</para>
+ <para>
+ Changing autovacuum storage parameters acquires a <literal>SHARE UPDATE EXCLUSIVE</literal> lock.
+ </para>
+
<note>
<para>
While <command>CREATE TABLE</> allows <literal>OIDS</> to be specified
diff --git a/src/backend/access/common/reloptions.c b/src/backend/access/common/reloptions.c
index 180f529..c39b878 100644
--- a/src/backend/access/common/reloptions.c
+++ b/src/backend/access/common/reloptions.c
@@ -57,7 +57,8 @@ static relopt_bool boolRelOpts[] =
{
"autovacuum_enabled",
"Enables autovacuum in this relation",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
true
},
@@ -65,7 +66,8 @@ static relopt_bool boolRelOpts[] =
{
"user_catalog_table",
"Declare a table as an additional catalog table, e.g. for the purpose of logical replication",
- RELOPT_KIND_HEAP
+ RELOPT_KIND_HEAP,
+ AccessExclusiveLock
},
false
},
@@ -73,7 +75,8 @@ static relopt_bool boolRelOpts[] =
{
"fastupdate",
"Enables \"fast update\" feature for this GIN index",
- RELOPT_KIND_GIN
+ RELOPT_KIND_GIN,
+ AccessExclusiveLock
},
true
},
@@ -81,7 +84,8 @@ static relopt_bool boolRelOpts[] =
{
"security_barrier",
"View acts as a row security barrier",
- RELOPT_KIND_VIEW
+ RELOPT_KIND_VIEW,
+ AccessExclusiveLock
},
false
},
@@ -95,7 +99,8 @@ static relopt_int intRelOpts[] =
{
"fillfactor",
"Packs table pages only to this percentage",
- RELOPT_KIND_HEAP
+ RELOPT_KIND_HEAP,
+ AccessExclusiveLock
},
HEAP_DEFAULT_FILLFACTOR, HEAP_MIN_FILLFACTOR, 100
},
@@ -103,7 +108,8 @@ static relopt_int intRelOpts[] =
{
"fillfactor",
"Packs btree index pages only to this percentage",
- RELOPT_KIND_BTREE
+ RELOPT_KIND_BTREE,
+ AccessExclusiveLock
},
BTREE_DEFAULT_FILLFACTOR, BTREE_MIN_FILLFACTOR, 100
},
@@ -111,7 +117,8 @@ static relopt_int intRelOpts[] =
{
"fillfactor",
"Packs hash index pages only to this percentage",
- RELOPT_KIND_HASH
+ RELOPT_KIND_HASH,
+ AccessExclusiveLock
},
HASH_DEFAULT_FILLFACTOR, HASH_MIN_FILLFACTOR, 100
},
@@ -119,7 +126,8 @@ static relopt_int intRelOpts[] =
{
"fillfactor",
"Packs gist index pages only to this percentage",
- RELOPT_KIND_GIST
+ RELOPT_KIND_GIST,
+ AccessExclusiveLock
},
GIST_DEFAULT_FILLFACTOR, GIST_MIN_FILLFACTOR, 100
},
@@ -127,7 +135,8 @@ static relopt_int intRelOpts[] =
{
"fillfactor",
"Packs spgist index pages only to this percentage",
- RELOPT_KIND_SPGIST
+ RELOPT_KIND_SPGIST,
+ AccessExclusiveLock
},
SPGIST_DEFAULT_FILLFACTOR, SPGIST_MIN_FILLFACTOR, 100
},
@@ -135,7 +144,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_vacuum_threshold",
"Minimum number of tuple updates or deletes prior to vacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 0, INT_MAX
},
@@ -143,7 +153,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_analyze_threshold",
"Minimum number of tuple inserts, updates or deletes prior to analyze",
- RELOPT_KIND_HEAP
+ RELOPT_KIND_HEAP,
+ ShareUpdateExclusiveLock
},
-1, 0, INT_MAX
},
@@ -151,7 +162,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_vacuum_cost_delay",
"Vacuum cost delay in milliseconds, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 0, 100
},
@@ -159,7 +171,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_vacuum_cost_limit",
"Vacuum cost amount available before napping, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 1, 10000
},
@@ -167,7 +180,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_freeze_min_age",
"Minimum age at which VACUUM should freeze a table row, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 0, 1000000000
},
@@ -175,7 +189,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_multixact_freeze_min_age",
"Minimum multixact age at which VACUUM should freeze a row multixact's, for autovacuum",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 0, 1000000000
},
@@ -183,7 +198,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_freeze_max_age",
"Age at which to autovacuum a table to prevent transaction ID wraparound",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 100000000, 2000000000
},
@@ -191,7 +207,8 @@ static relopt_int intRelOpts[] =
{
"autovacuum_multixact_freeze_max_age",
"Multixact age at which to autovacuum a table to prevent multixact wraparound",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 100000000, 2000000000
},
@@ -199,21 +216,24 @@ static relopt_int intRelOpts[] =
{
"autovacuum_freeze_table_age",
"Age at which VACUUM should perform a full table sweep to freeze row versions",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
}, -1, 0, 2000000000
},
{
{
"autovacuum_multixact_freeze_table_age",
"Age of multixact at which VACUUM should perform a full table sweep to freeze row versions",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
}, -1, 0, 2000000000
},
{
{
"log_autovacuum_min_duration",
"Sets the minimum execution time above which autovacuum actions will be logged",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, -1, INT_MAX
},
@@ -221,14 +241,16 @@ static relopt_int intRelOpts[] =
{
"pages_per_range",
"Number of pages that each page range covers in a BRIN index",
- RELOPT_KIND_BRIN
+ RELOPT_KIND_BRIN,
+ AccessExclusiveLock
}, 128, 1, 131072
},
{
{
"gin_pending_list_limit",
"Maximum size of the pending list for this GIN index, in kilobytes.",
- RELOPT_KIND_GIN
+ RELOPT_KIND_GIN,
+ AccessExclusiveLock
},
-1, 64, MAX_KILOBYTES
},
@@ -243,7 +265,8 @@ static relopt_real realRelOpts[] =
{
"autovacuum_vacuum_scale_factor",
"Number of tuple updates or deletes prior to vacuum as a fraction of reltuples",
- RELOPT_KIND_HEAP | RELOPT_KIND_TOAST
+ RELOPT_KIND_HEAP | RELOPT_KIND_TOAST,
+ ShareUpdateExclusiveLock
},
-1, 0.0, 100.0
},
@@ -251,7 +274,8 @@ static relopt_real realRelOpts[] =
{
"autovacuum_analyze_scale_factor",
"Number of tuple inserts, updates or deletes prior to analyze as a fraction of reltuples",
- RELOPT_KIND_HEAP
+ RELOPT_KIND_HEAP,
+ ShareUpdateExclusiveLock
},
-1, 0.0, 100.0
},
@@ -259,7 +283,8 @@ static relopt_real realRelOpts[] =
{
"seq_page_cost",
"Sets the planner's estimate of the cost of a sequentially fetched disk page.",
- RELOPT_KIND_TABLESPACE
+ RELOPT_KIND_TABLESPACE,
+ AccessExclusiveLock
},
-1, 0.0, DBL_MAX
},
@@ -267,7 +292,8 @@ static relopt_real realRelOpts[] =
{
"random_page_cost",
"Sets the planner's estimate of the cost of a nonsequentially fetched disk page.",
- RELOPT_KIND_TABLESPACE
+ RELOPT_KIND_TABLESPACE,
+ AccessExclusiveLock
},
-1, 0.0, DBL_MAX
},
@@ -275,7 +301,8 @@ static relopt_real realRelOpts[] =
{
"n_distinct",
"Sets the planner's estimate of the number of distinct values appearing in a column (excluding child relations).",
- RELOPT_KIND_ATTRIBUTE
+ RELOPT_KIND_ATTRIBUTE,
+ AccessExclusiveLock
},
0, -1.0, DBL_MAX
},
@@ -283,7 +310,8 @@ static relopt_real realRelOpts[] =
{
"n_distinct_inherited",
"Sets the planner's estimate of the number of distinct values appearing in a column (including child relations).",
- RELOPT_KIND_ATTRIBUTE
+ RELOPT_KIND_ATTRIBUTE,
+ AccessExclusiveLock
},
0, -1.0, DBL_MAX
},
@@ -297,7 +325,8 @@ static relopt_string stringRelOpts[] =
{
"buffering",
"Enables buffering build for this GiST index",
- RELOPT_KIND_GIST
+ RELOPT_KIND_GIST,
+ AccessExclusiveLock
},
4,
false,
@@ -308,7 +337,8 @@ static relopt_string stringRelOpts[] =
{
"check_option",
"View has WITH CHECK OPTION defined (local or cascaded).",
- RELOPT_KIND_VIEW
+ RELOPT_KIND_VIEW,
+ AccessExclusiveLock
},
0,
true,
@@ -344,13 +374,29 @@ initialize_reloptions(void)
j = 0;
for (i = 0; boolRelOpts[i].gen.name; i++)
+ {
+ Assert(DoLockModesConflict(boolRelOpts[i].gen.lockmode,
+ boolRelOpts[i].gen.lockmode));
j++;
+ }
for (i = 0; intRelOpts[i].gen.name; i++)
+ {
+ Assert(DoLockModesConflict(intRelOpts[i].gen.lockmode,
+ intRelOpts[i].gen.lockmode));
j++;
+ }
for (i = 0; realRelOpts[i].gen.name; i++)
+ {
+ Assert(DoLockModesConflict(realRelOpts[i].gen.lockmode,
+ realRelOpts[i].gen.lockmode));
j++;
+ }
for (i = 0; stringRelOpts[i].gen.name; i++)
+ {
+ Assert(DoLockModesConflict(stringRelOpts[i].gen.lockmode,
+ stringRelOpts[i].gen.lockmode));
j++;
+ }
j += num_custom_options;
if (relOpts)
@@ -1411,3 +1457,38 @@ tablespace_reloptions(Datum reloptions, bool validate)
return (bytea *) tsopts;
}
+
+/*
+ * Determine the required LOCKMODE from an option list
+ */
+LOCKMODE
+GetRelOptionsLockLevel(List *defList)
+{
+ LOCKMODE lockmode = NoLock;
+ ListCell *cell;
+
+ if (defList == NIL)
+ return AccessExclusiveLock;
+
+ if (need_initialization)
+ initialize_reloptions();
+
+ foreach(cell, defList)
+ {
+ DefElem *def = (DefElem *) lfirst(cell);
+ int i;
+
+ for (i = 0; relOpts[i]; i++)
+ {
+ if (pg_strncasecmp(relOpts[i]->name,
+ def->defname,
+ relOpts[i]->namelen + 1) == 0)
+ {
+ if (lockmode < relOpts[i]->lockmode)
+ lockmode = relOpts[i]->lockmode;
+ }
+ }
+ }
+
+ return lockmode;
+}
diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c
index 970abd4..6e63ea2 100644
--- a/src/backend/commands/tablecmds.c
+++ b/src/backend/commands/tablecmds.c
@@ -3038,16 +3038,12 @@ AlterTableGetLockLevel(List *cmds)
* are set here for tables, views and indexes; for historical
* reasons these can all be used with ALTER TABLE, so we can't
* decide between them using the basic grammar.
- *
- * XXX Look in detail at each option to determine lock level,
- * e.g. cmd_lockmode = GetRelOptionsLockLevel((List *)
- * cmd->def);
*/
case AT_SetRelOptions: /* Uses MVCC in getIndexes() and
* getTables() */
case AT_ResetRelOptions: /* Uses MVCC in getIndexes() and
* getTables() */
- cmd_lockmode = AccessExclusiveLock;
+ cmd_lockmode = GetRelOptionsLockLevel((List *) cmd->def);
break;
default: /* oops */
diff --git a/src/include/access/reloptions.h b/src/include/access/reloptions.h
index e7b6bb5..c444c71 100644
--- a/src/include/access/reloptions.h
+++ b/src/include/access/reloptions.h
@@ -22,6 +22,7 @@
#include "access/htup.h"
#include "access/tupdesc.h"
#include "nodes/pg_list.h"
+#include "storage/lock.h"
/* types supported by reloptions */
typedef enum relopt_type
@@ -62,6 +63,7 @@ typedef struct relopt_gen
* marker) */
const char *desc;
bits32 kinds;
+ LOCKMODE lockmode;
int namelen;
relopt_type type;
} relopt_gen;
@@ -274,5 +276,6 @@ extern bytea *index_reloptions(RegProcedure amoptions, Datum reloptions,
bool validate);
extern bytea *attribute_reloptions(Datum reloptions, bool validate);
extern bytea *tablespace_reloptions(Datum reloptions, bool validate);
+extern LOCKMODE GetRelOptionsLockLevel(List *defList);
#endif /* RELOPTIONS_H */
diff --git a/src/test/isolation/expected/alter-table-4.out b/src/test/isolation/expected/alter-table-4.out
new file mode 100644
index 0000000..72d610e
--- /dev/null
+++ b/src/test/isolation/expected/alter-table-4.out
@@ -0,0 +1,131 @@
+Parsed test spec with 2 sessions
+
+starting permutation: b1 b2 at11 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at11: ALTER TABLE a SET (fillfactor=10);
+step wx1: UPDATE a SET id = id + 10000; <waiting ...>
+step c1: COMMIT;
+step wx1: <... completed>
+step c2: COMMIT;
+
+starting permutation: b1 b2 at12 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at12: ALTER TABLE a SET (user_catalog_table=true);
+step wx1: UPDATE a SET id = id + 10000; <waiting ...>
+step c1: COMMIT;
+step wx1: <... completed>
+step c2: COMMIT;
+
+starting permutation: b1 b2 at13 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at13: ALTER TABLE a SET (autovacuum_enabled=true);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at14 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at14: ALTER TABLE a SET (autovacuum_vacuum_threshold=10);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at15 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at15: ALTER TABLE a SET (autovacuum_analyze_threshold=5);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at16 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at16: ALTER TABLE a SET (autovacuum_vacuum_cost_delay=2);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at17 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at17: ALTER TABLE a SET (autovacuum_vacuum_cost_limit=1);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at18 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at18: ALTER TABLE a SET (autovacuum_freeze_min_age=0);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at19 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at19: ALTER TABLE a SET (autovacuum_multixact_freeze_min_age=500000000);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at20 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at20: ALTER TABLE a SET (autovacuum_freeze_max_age=1000000000);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at21 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at21: ALTER TABLE a SET (autovacuum_multixact_freeze_max_age=1000000000);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at22 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at22: ALTER TABLE a SET (autovacuum_freeze_table_age=1500000000);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at23 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at23: ALTER TABLE a SET (autovacuum_multixact_freeze_table_age=1000000000);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at24 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at24: ALTER TABLE a SET (autovacuum_vacuum_scale_factor=10);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at25 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at25: ALTER TABLE a SET (autovacuum_analyze_scale_factor=5);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
+
+starting permutation: b1 b2 at26 wx1 c1 c2
+step b1: BEGIN;
+step b2: BEGIN;
+step at26: ALTER TABLE a SET (log_autovacuum_min_duration=100);
+step wx1: UPDATE a SET id = id + 10000;
+step c1: COMMIT;
+step c2: COMMIT;
diff --git a/src/test/isolation/isolation_schedule b/src/test/isolation/isolation_schedule
index c0ed637..c6234bc 100644
--- a/src/test/isolation/isolation_schedule
+++ b/src/test/isolation/isolation_schedule
@@ -40,5 +40,6 @@ test: drop-index-concurrently-1
test: alter-table-1
test: alter-table-2
test: alter-table-3
+test: alter-table-4
test: create-trigger
test: timeouts
diff --git a/src/test/isolation/specs/alter-table-4.spec b/src/test/isolation/specs/alter-table-4.spec
new file mode 100644
index 0000000..4a9313e
--- /dev/null
+++ b/src/test/isolation/specs/alter-table-4.spec
@@ -0,0 +1,57 @@
+# ALTER TABLE - Enable and disable some autovacuum options
+#
+# SET autovacuum_* options needs a ShareUpdateExclusiveLock
+# so we mix reads with it to see what works or waits
+
+setup
+{
+ CREATE TABLE a (id int PRIMARY KEY);
+ INSERT INTO a SELECT generate_series(1,100);
+}
+
+teardown
+{
+ DROP TABLE a;
+}
+
+session "s1"
+step "b1" { BEGIN; }
+step "at11" { ALTER TABLE a SET (fillfactor=10); }
+step "at12" { ALTER TABLE a SET (user_catalog_table=true); }
+step "at13" { ALTER TABLE a SET (autovacuum_enabled=true); }
+step "at14" { ALTER TABLE a SET (autovacuum_vacuum_threshold=10); }
+step "at15" { ALTER TABLE a SET (autovacuum_analyze_threshold=5); }
+step "at16" { ALTER TABLE a SET (autovacuum_vacuum_cost_delay=2); }
+step "at17" { ALTER TABLE a SET (autovacuum_vacuum_cost_limit=1); }
+step "at18" { ALTER TABLE a SET (autovacuum_freeze_min_age=0); }
+step "at19" { ALTER TABLE a SET (autovacuum_multixact_freeze_min_age=500000000); }
+step "at20" { ALTER TABLE a SET (autovacuum_freeze_max_age=1000000000); }
+step "at21" { ALTER TABLE a SET (autovacuum_multixact_freeze_max_age=1000000000); }
+step "at22" { ALTER TABLE a SET (autovacuum_freeze_table_age=1500000000); }
+step "at23" { ALTER TABLE a SET (autovacuum_multixact_freeze_table_age=1000000000); }
+step "at24" { ALTER TABLE a SET (autovacuum_vacuum_scale_factor=10); }
+step "at25" { ALTER TABLE a SET (autovacuum_analyze_scale_factor=5); }
+step "at26" { ALTER TABLE a SET (log_autovacuum_min_duration=100); }
+step "c1" { COMMIT; }
+
+session "s2"
+step "b2" { BEGIN; }
+step "wx1" { UPDATE a SET id = id + 10000; }
+step "c2" { COMMIT; }
+
+permutation "b1" "b2" "at11" "wx1" "c1" "c2"
+permutation "b1" "b2" "at12" "wx1" "c1" "c2"
+permutation "b1" "b2" "at13" "wx1" "c1" "c2"
+permutation "b1" "b2" "at14" "wx1" "c1" "c2"
+permutation "b1" "b2" "at15" "wx1" "c1" "c2"
+permutation "b1" "b2" "at16" "wx1" "c1" "c2"
+permutation "b1" "b2" "at17" "wx1" "c1" "c2"
+permutation "b1" "b2" "at18" "wx1" "c1" "c2"
+permutation "b1" "b2" "at19" "wx1" "c1" "c2"
+permutation "b1" "b2" "at20" "wx1" "c1" "c2"
+permutation "b1" "b2" "at21" "wx1" "c1" "c2"
+permutation "b1" "b2" "at22" "wx1" "c1" "c2"
+permutation "b1" "b2" "at23" "wx1" "c1" "c2"
+permutation "b1" "b2" "at24" "wx1" "c1" "c2"
+permutation "b1" "b2" "at25" "wx1" "c1" "c2"
+permutation "b1" "b2" "at26" "wx1" "c1" "c2"
diff --git a/src/test/regress/expected/alter_table.out b/src/test/regress/expected/alter_table.out
index 028d6ed..fefabac 100644
--- a/src/test/regress/expected/alter_table.out
+++ b/src/test/regress/expected/alter_table.out
@@ -1913,19 +1913,19 @@ select * from my_locks order by 1;
commit;
begin; alter table alterlock set (toast.autovacuum_enabled = off);
select * from my_locks order by 1;
- relname | max_lockmode
------------+---------------------
- alterlock | AccessExclusiveLock
- pg_toast | AccessExclusiveLock
+ relname | max_lockmode
+-----------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+ pg_toast | ShareUpdateExclusiveLock
(2 rows)
commit;
begin; alter table alterlock set (autovacuum_enabled = off);
select * from my_locks order by 1;
- relname | max_lockmode
------------+---------------------
- alterlock | AccessExclusiveLock
- pg_toast | AccessExclusiveLock
+ relname | max_lockmode
+-----------+--------------------------
+ alterlock | ShareUpdateExclusiveLock
+ pg_toast | ShareUpdateExclusiveLock
(2 rows)
commit;
@@ -2006,6 +2006,44 @@ select * from my_locks order by 1;
(4 rows)
rollback;
+create or replace view my_locks as
+select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
+from pg_locks l join pg_class c on l.relation = c.oid
+where virtualtransaction = (
+ select virtualtransaction
+ from pg_locks
+ where transactionid = txid_current()::integer)
+and locktype = 'relation'
+and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
+and c.relname = 'my_locks'
+group by c.relname;
+-- raise exception
+alter table my_locks set (autovacuum_enabled = false);
+ERROR: unrecognized parameter "autovacuum_enabled"
+alter view my_locks set (autovacuum_enabled = false);
+ERROR: unrecognized parameter "autovacuum_enabled"
+alter table my_locks reset (autovacuum_enabled);
+alter view my_locks reset (autovacuum_enabled);
+begin;
+alter view my_locks set (security_barrier=off);
+select * from my_locks order by 1;
+ relname | max_lockmode
+----------+---------------------
+ my_locks | AccessExclusiveLock
+(1 row)
+
+alter view my_locks reset (security_barrier);
+rollback;
+begin;
+alter table my_locks set (security_barrier=off);
+select * from my_locks order by 1;
+ relname | max_lockmode
+----------+---------------------
+ my_locks | AccessExclusiveLock
+(1 row)
+
+alter table my_locks reset (security_barrier);
+rollback;
-- cleanup
drop table alterlock2;
drop table alterlock;
diff --git a/src/test/regress/sql/alter_table.sql b/src/test/regress/sql/alter_table.sql
index aefb5c9..684efea 100644
--- a/src/test/regress/sql/alter_table.sql
+++ b/src/test/regress/sql/alter_table.sql
@@ -1364,6 +1364,36 @@ alter table alterlock2 validate constraint alterlock2nv;
select * from my_locks order by 1;
rollback;
+create or replace view my_locks as
+select case when c.relname like 'pg_toast%' then 'pg_toast' else c.relname end, max(mode::lockmodes) as max_lockmode
+from pg_locks l join pg_class c on l.relation = c.oid
+where virtualtransaction = (
+ select virtualtransaction
+ from pg_locks
+ where transactionid = txid_current()::integer)
+and locktype = 'relation'
+and relnamespace != (select oid from pg_namespace where nspname = 'pg_catalog')
+and c.relname = 'my_locks'
+group by c.relname;
+
+-- raise exception
+alter table my_locks set (autovacuum_enabled = false);
+alter view my_locks set (autovacuum_enabled = false);
+alter table my_locks reset (autovacuum_enabled);
+alter view my_locks reset (autovacuum_enabled);
+
+begin;
+alter view my_locks set (security_barrier=off);
+select * from my_locks order by 1;
+alter view my_locks reset (security_barrier);
+rollback;
+
+begin;
+alter table my_locks set (security_barrier=off);
+select * from my_locks order by 1;
+alter table my_locks reset (security_barrier);
+rollback;
+
-- cleanup
drop table alterlock2;
drop table alterlock;
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
