public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v5 3/4] Allow granting VACUUM and ANALYZE privileges on relations.
27+ messages / 10 participants
[nested] [flat]
* [PATCH v5 3/4] Allow granting VACUUM and ANALYZE privileges on relations.
@ 2022-09-04 06:31 Nathan Bossart <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Nathan Bossart @ 2022-09-04 06:31 UTC (permalink / raw)
---
doc/src/sgml/ddl.sgml | 49 ++++++++---
doc/src/sgml/func.sgml | 3 +-
.../sgml/ref/alter_default_privileges.sgml | 4 +-
doc/src/sgml/ref/analyze.sgml | 3 +-
doc/src/sgml/ref/grant.sgml | 4 +-
doc/src/sgml/ref/revoke.sgml | 2 +-
doc/src/sgml/ref/vacuum.sgml | 3 +-
src/backend/catalog/aclchk.c | 8 ++
src/backend/commands/analyze.c | 2 +-
src/backend/commands/vacuum.c | 24 ++++--
src/backend/parser/gram.y | 7 ++
src/backend/utils/adt/acl.c | 16 ++++
src/bin/pg_dump/dumputils.c | 2 +
src/bin/pg_dump/t/002_pg_dump.pl | 2 +-
src/bin/psql/tab-complete.c | 4 +-
src/include/nodes/parsenodes.h | 4 +-
src/include/utils/acl.h | 6 +-
src/test/regress/expected/dependency.out | 22 ++---
src/test/regress/expected/privileges.out | 86 ++++++++++++++-----
src/test/regress/expected/rowsecurity.out | 34 ++++----
src/test/regress/expected/vacuum.out | 6 ++
src/test/regress/sql/dependency.sql | 2 +-
src/test/regress/sql/privileges.sql | 40 +++++++++
23 files changed, 249 insertions(+), 84 deletions(-)
diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml
index 03c0193709..ed034a6b1d 100644
--- a/doc/src/sgml/ddl.sgml
+++ b/doc/src/sgml/ddl.sgml
@@ -1691,8 +1691,9 @@ ALTER TABLE products RENAME TO items;
<literal>INSERT</literal>, <literal>UPDATE</literal>, <literal>DELETE</literal>,
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>, <literal>TRIGGER</literal>,
<literal>CREATE</literal>, <literal>CONNECT</literal>, <literal>TEMPORARY</literal>,
- <literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>
- and <literal>ALTER SYSTEM</literal>.
+ <literal>EXECUTE</literal>, <literal>USAGE</literal>, <literal>SET</literal>,
+ <literal>ALTER SYSTEM</literal>, <literal>VACUUM</literal>, and
+ <literal>ANALYZE</literal>.
The privileges applicable to a particular
object vary depending on the object's type (table, function, etc.).
More detail about the meanings of these privileges appears below.
@@ -1982,7 +1983,25 @@ REVOKE ALL ON accounts FROM PUBLIC;
</para>
</listitem>
</varlistentry>
- </variablelist>
+
+ <varlistentry>
+ <term><literal>VACUUM</literal></term>
+ <listitem>
+ <para>
+ Allows <command>VACUUM</command> on a relation.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>ANALYZE</literal></term>
+ <listitem>
+ <para>
+ Allows <command>ANALYZE</command> on a relation.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
The privileges required by other commands are listed on the
reference page of the respective command.
@@ -2131,6 +2150,16 @@ REVOKE ALL ON accounts FROM PUBLIC;
<entry><literal>A</literal></entry>
<entry><literal>PARAMETER</literal></entry>
</row>
+ <row>
+ <entry><literal>VACUUM</literal></entry>
+ <entry><literal>v</literal></entry>
+ <entry><literal>TABLE</literal></entry>
+ </row>
+ <row>
+ <entry><literal>ANALYZE</literal></entry>
+ <entry><literal>z</literal></entry>
+ <entry><literal>TABLE</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -2221,7 +2250,7 @@ REVOKE ALL ON accounts FROM PUBLIC;
</row>
<row>
<entry><literal>TABLE</literal> (and table-like objects)</entry>
- <entry><literal>arwdDxt</literal></entry>
+ <entry><literal>arwdDxtvz</literal></entry>
<entry>none</entry>
<entry><literal>\dp</literal></entry>
</row>
@@ -2279,12 +2308,12 @@ GRANT SELECT (col1), UPDATE (col1) ON mytable TO miriam_rw;
would show:
<programlisting>
=> \dp mytable
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------+-------+-----------------------+-----------------------+----------
- public | mytable | table | miriam=arwdDxt/miriam+| col1: +|
- | | | =r/miriam +| miriam_rw=rw/miriam |
- | | | admin=arw/miriam | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------+-------+-------------------------+-----------------------+----------
+ public | mytable | table | miriam=arwdDxtvz/miriam+| col1: +|
+ | | | =r/miriam +| miriam_rw=rw/miriam |
+ | | | admin=arw/miriam | |
(1 row)
</programlisting>
</para>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 67eb380632..8be800767d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -22962,7 +22962,8 @@ SELECT has_function_privilege('joeuser', 'myfunc(int, text)', 'execute');
are <literal>SELECT</literal>, <literal>INSERT</literal>,
<literal>UPDATE</literal>, <literal>DELETE</literal>,
<literal>TRUNCATE</literal>, <literal>REFERENCES</literal>,
- and <literal>TRIGGER</literal>.
+ <literal>TRIGGER</literal>, <literal>VACUUM</literal> and
+ <literal>ANALYZE</literal>.
</para></entry>
</row>
diff --git a/doc/src/sgml/ref/alter_default_privileges.sgml b/doc/src/sgml/ref/alter_default_privileges.sgml
index f1d54f5aa3..0da295daff 100644
--- a/doc/src/sgml/ref/alter_default_privileges.sgml
+++ b/doc/src/sgml/ref/alter_default_privileges.sgml
@@ -28,7 +28,7 @@ ALTER DEFAULT PRIVILEGES
<phrase>where <replaceable class="parameter">abbreviated_grant_or_revoke</replaceable> is one of:</phrase>
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
@@ -51,7 +51,7 @@ GRANT { USAGE | CREATE | ALL [ PRIVILEGES ] }
TO { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...] [ WITH GRANT OPTION ]
REVOKE [ GRANT OPTION FOR ]
- { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+ { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON TABLES
FROM { [ GROUP ] <replaceable class="parameter">role_name</replaceable> | PUBLIC } [, ...]
diff --git a/doc/src/sgml/ref/analyze.sgml b/doc/src/sgml/ref/analyze.sgml
index 2ba115d1ad..400ea30cd0 100644
--- a/doc/src/sgml/ref/analyze.sgml
+++ b/doc/src/sgml/ref/analyze.sgml
@@ -149,7 +149,8 @@ ANALYZE [ VERBOSE ] [ <replaceable class="parameter">table_and_columns</replacea
<para>
To analyze a table, one must ordinarily be the table's owner or a
- superuser. However, database owners are allowed to
+ superuser or have the <literal>ANALYZE</literal> privilege on the table.
+ However, database owners are allowed to
analyze all tables in their databases, except shared catalogs.
(The restriction for shared catalogs means that a true database-wide
<command>ANALYZE</command> can only be performed by a superuser.)
diff --git a/doc/src/sgml/ref/grant.sgml b/doc/src/sgml/ref/grant.sgml
index dea19cd348..f6234d975a 100644
--- a/doc/src/sgml/ref/grant.sgml
+++ b/doc/src/sgml/ref/grant.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
-GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+GRANT { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
| ALL TABLES IN SCHEMA <replaceable class="parameter">schema_name</replaceable> [, ...] }
@@ -193,6 +193,8 @@ GRANT <replaceable class="parameter">role_name</replaceable> [, ...] TO <replace
<term><literal>USAGE</literal></term>
<term><literal>SET</literal></term>
<term><literal>ALTER SYSTEM</literal></term>
+ <term><literal>VACUUM</literal></term>
+ <term><literal>ANALYZE</literal></term>
<listitem>
<para>
Specific types of privileges, as defined in <xref linkend="ddl-priv"/>.
diff --git a/doc/src/sgml/ref/revoke.sgml b/doc/src/sgml/ref/revoke.sgml
index 4fd4bfb3d7..ece1aa721f 100644
--- a/doc/src/sgml/ref/revoke.sgml
+++ b/doc/src/sgml/ref/revoke.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
REVOKE [ GRANT OPTION FOR ]
- { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER }
+ { { SELECT | INSERT | UPDATE | DELETE | TRUNCATE | REFERENCES | TRIGGER | VACUUM | ANALYZE }
[, ...] | ALL [ PRIVILEGES ] }
ON { [ TABLE ] <replaceable class="parameter">table_name</replaceable> [, ...]
| ALL TABLES IN SCHEMA <replaceable>schema_name</replaceable> [, ...] }
diff --git a/doc/src/sgml/ref/vacuum.sgml b/doc/src/sgml/ref/vacuum.sgml
index c582021d29..70c0d81346 100644
--- a/doc/src/sgml/ref/vacuum.sgml
+++ b/doc/src/sgml/ref/vacuum.sgml
@@ -357,7 +357,8 @@ VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ <replaceable class="paramet
<para>
To vacuum a table, one must ordinarily be the table's owner or a
- superuser. However, database owners are allowed to
+ superuser or have the <literal>VACUUM</literal> privilege on the table.
+ However, database owners are allowed to
vacuum all tables in their databases, except shared catalogs.
(The restriction for shared catalogs means that a true database-wide
<command>VACUUM</command> can only be performed by a superuser.)
diff --git a/src/backend/catalog/aclchk.c b/src/backend/catalog/aclchk.c
index 17ff617fba..20c018098f 100644
--- a/src/backend/catalog/aclchk.c
+++ b/src/backend/catalog/aclchk.c
@@ -3403,6 +3403,10 @@ string_to_privilege(const char *privname)
return ACL_SET;
if (strcmp(privname, "alter system") == 0)
return ACL_ALTER_SYSTEM;
+ if (strcmp(privname, "vacuum") == 0)
+ return ACL_VACUUM;
+ if (strcmp(privname, "analyze") == 0)
+ return ACL_ANALYZE;
if (strcmp(privname, "rule") == 0)
return 0; /* ignore old RULE privileges */
ereport(ERROR,
@@ -3444,6 +3448,10 @@ privilege_to_string(AclMode privilege)
return "SET";
case ACL_ALTER_SYSTEM:
return "ALTER SYSTEM";
+ case ACL_VACUUM:
+ return "VACUUM";
+ case ACL_ANALYZE:
+ return "ANALYZE";
default:
elog(ERROR, "unrecognized privilege: %d", (int) privilege);
}
diff --git a/src/backend/commands/analyze.c b/src/backend/commands/analyze.c
index a7966fff83..faa5b098df 100644
--- a/src/backend/commands/analyze.c
+++ b/src/backend/commands/analyze.c
@@ -168,7 +168,7 @@ analyze_rel(Oid relid, RangeVar *relation,
*/
if (!vacuum_is_relation_owner(RelationGetRelid(onerel),
onerel->rd_rel,
- params->options & VACOPT_ANALYZE))
+ VACOPT_ANALYZE))
{
relation_close(onerel, ShareUpdateExclusiveLock);
return;
diff --git a/src/backend/commands/vacuum.c b/src/backend/commands/vacuum.c
index 651e4a7556..15311e91d2 100644
--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -547,16 +547,16 @@ vacuum(List *relations, VacuumParams *params,
}
/*
- * Check if a given relation can be safely vacuumed or analyzed. If the
- * user is not the relation owner, issue a WARNING log message and return
- * false to let the caller decide what to do with this relation. This
- * routine is used to decide if a relation can be processed for VACUUM or
- * ANALYZE.
+ * Check if the current user has privileges to vacuum or analyze the relation.
+ * If not, issue a WARNING log message and return false to let the caller
+ * decide what to do with this relation. This routine is used to decide if a
+ * relation can be processed for VACUUM or ANALYZE.
*/
bool
vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options)
{
char *relname;
+ AclMode mode = 0;
Assert((options & (VACOPT_VACUUM | VACOPT_ANALYZE)) != 0);
@@ -566,13 +566,19 @@ vacuum_is_relation_owner(Oid relid, Form_pg_class reltuple, bits32 options)
* We allow the user to vacuum or analyze a table if he is superuser, the
* table owner, or the database owner (but in the latter case, only if
* it's not a shared relation). pg_class_ownercheck includes the
- * superuser case.
+ * superuser case. The user might also have been granted privileges to
+ * vacuum or analyze the table.
*
* Note we choose to treat permissions failure as a WARNING and keep
* trying to vacuum or analyze the rest of the DB --- is this appropriate?
*/
+ if (options & VACOPT_VACUUM)
+ mode |= ACL_VACUUM;
+ if (options & VACOPT_ANALYZE)
+ mode |= ACL_ANALYZE;
if (pg_class_ownercheck(relid, GetUserId()) ||
- (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !reltuple->relisshared))
+ (pg_database_ownercheck(MyDatabaseId, GetUserId()) && !reltuple->relisshared) ||
+ pg_class_aclcheck(relid, GetUserId(), mode) == ACLCHECK_OK)
return true;
relname = NameStr(reltuple->relname);
@@ -1894,12 +1900,12 @@ vacuum_rel(Oid relid, RangeVar *relation, VacuumParams *params)
*/
if (!vacuum_is_relation_owner(RelationGetRelid(rel),
rel->rd_rel,
- params->options & VACOPT_VACUUM))
+ VACOPT_VACUUM))
{
relation_close(rel, lmode);
PopActiveSnapshot();
CommitTransactionCommand();
- return false;
+ return true; /* user may have the ANALYZE privilege */
}
/*
diff --git a/src/backend/parser/gram.y b/src/backend/parser/gram.y
index 0492ff9a66..7b2426bc52 100644
--- a/src/backend/parser/gram.y
+++ b/src/backend/parser/gram.y
@@ -7485,6 +7485,13 @@ privilege: SELECT opt_column_list
n->cols = NIL;
$$ = n;
}
+ | analyze_keyword
+ {
+ AccessPriv *n = makeNode(AccessPriv);
+ n->priv_name = pstrdup("analyze");
+ n->cols = NIL;
+ $$ = n;
+ }
| ColId opt_column_list
{
AccessPriv *n = makeNode(AccessPriv);
diff --git a/src/backend/utils/adt/acl.c b/src/backend/utils/adt/acl.c
index fd71a9b13e..b4b4a5e6fa 100644
--- a/src/backend/utils/adt/acl.c
+++ b/src/backend/utils/adt/acl.c
@@ -314,6 +314,12 @@ aclparse(const char *s, AclItem *aip)
case ACL_ALTER_SYSTEM_CHR:
read = ACL_ALTER_SYSTEM;
break;
+ case ACL_VACUUM_CHR:
+ read = ACL_VACUUM;
+ break;
+ case ACL_ANALYZE_CHR:
+ read = ACL_ANALYZE;
+ break;
case 'R': /* ignore old RULE privileges */
read = 0;
break;
@@ -1588,6 +1594,8 @@ makeaclitem(PG_FUNCTION_ARGS)
{"CONNECT", ACL_CONNECT},
{"SET", ACL_SET},
{"ALTER SYSTEM", ACL_ALTER_SYSTEM},
+ {"VACUUM", ACL_VACUUM},
+ {"ANALYZE", ACL_ANALYZE},
{"RULE", 0}, /* ignore old RULE privileges */
{NULL, 0}
};
@@ -1696,6 +1704,10 @@ convert_aclright_to_string(int aclright)
return "SET";
case ACL_ALTER_SYSTEM:
return "ALTER SYSTEM";
+ case ACL_VACUUM:
+ return "VACUUM";
+ case ACL_ANALYZE:
+ return "ANALYZE";
default:
elog(ERROR, "unrecognized aclright: %d", aclright);
return NULL;
@@ -2005,6 +2017,10 @@ convert_table_priv_string(text *priv_type_text)
{"REFERENCES WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_REFERENCES)},
{"TRIGGER", ACL_TRIGGER},
{"TRIGGER WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_TRIGGER)},
+ {"VACUUM", ACL_VACUUM},
+ {"VACUUM WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_VACUUM)},
+ {"ANALYZE", ACL_ANALYZE},
+ {"ANALYZE WITH GRANT OPTION", ACL_GRANT_OPTION_FOR(ACL_ANALYZE)},
{"RULE", 0}, /* ignore old RULE privileges */
{"RULE WITH GRANT OPTION", 0},
{NULL, 0}
diff --git a/src/bin/pg_dump/dumputils.c b/src/bin/pg_dump/dumputils.c
index 6e501a5413..9311417f18 100644
--- a/src/bin/pg_dump/dumputils.c
+++ b/src/bin/pg_dump/dumputils.c
@@ -457,6 +457,8 @@ do { \
CONVERT_PRIV('d', "DELETE");
CONVERT_PRIV('t', "TRIGGER");
CONVERT_PRIV('D', "TRUNCATE");
+ CONVERT_PRIV('v', "VACUUM");
+ CONVERT_PRIV('z', "ANALYZE");
}
}
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 2873b662fb..f015fe2194 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -566,7 +566,7 @@ my %tests = (
\QREVOKE ALL ON TABLES FROM regress_dump_test_role;\E\n
\QALTER DEFAULT PRIVILEGES \E
\QFOR ROLE regress_dump_test_role \E
- \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,UPDATE ON TABLES TO regress_dump_test_role;\E
+ \QGRANT INSERT,REFERENCES,DELETE,TRIGGER,TRUNCATE,VACUUM,ANALYZE,UPDATE ON TABLES TO regress_dump_test_role;\E
/xm,
like => { %full_runs, section_post_data => 1, },
unlike => { no_privs => 1, },
diff --git a/src/bin/psql/tab-complete.c b/src/bin/psql/tab-complete.c
index a7eccc75d2..cca98ebb64 100644
--- a/src/bin/psql/tab-complete.c
+++ b/src/bin/psql/tab-complete.c
@@ -3742,7 +3742,7 @@ psql_completion(const char *text, int start, int end)
if (HeadMatches("ALTER", "DEFAULT", "PRIVILEGES"))
COMPLETE_WITH("SELECT", "INSERT", "UPDATE",
"DELETE", "TRUNCATE", "REFERENCES", "TRIGGER",
- "EXECUTE", "USAGE", "ALL");
+ "EXECUTE", "USAGE", "VACUUM", "ANALYZE", "ALL");
else
COMPLETE_WITH_QUERY_PLUS(Query_for_list_of_roles,
"GRANT",
@@ -3760,6 +3760,8 @@ psql_completion(const char *text, int start, int end)
"USAGE",
"SET",
"ALTER SYSTEM",
+ "VACUUM",
+ "ANALYZE",
"ALL");
}
diff --git a/src/include/nodes/parsenodes.h b/src/include/nodes/parsenodes.h
index 726529476c..6db572908d 100644
--- a/src/include/nodes/parsenodes.h
+++ b/src/include/nodes/parsenodes.h
@@ -94,7 +94,9 @@ typedef uint64 AclMode; /* a bitmask of privilege bits */
#define ACL_CONNECT (1<<11) /* for databases */
#define ACL_SET (1<<12) /* for configuration parameters */
#define ACL_ALTER_SYSTEM (1<<13) /* for configuration parameters */
-#define N_ACL_RIGHTS 14 /* 1 plus the last 1<<x */
+#define ACL_VACUUM (1<<14) /* for relations */
+#define ACL_ANALYZE (1<<15) /* for relations */
+#define N_ACL_RIGHTS 16 /* 1 plus the last 1<<x */
#define ACL_NO_RIGHTS 0
/* Currently, SELECT ... FOR [KEY] UPDATE/SHARE requires UPDATE privileges */
#define ACL_SELECT_FOR_UPDATE ACL_UPDATE
diff --git a/src/include/utils/acl.h b/src/include/utils/acl.h
index 8ac7696b7d..4dc6a06126 100644
--- a/src/include/utils/acl.h
+++ b/src/include/utils/acl.h
@@ -148,15 +148,17 @@ typedef struct ArrayType Acl;
#define ACL_CONNECT_CHR 'c'
#define ACL_SET_CHR 's'
#define ACL_ALTER_SYSTEM_CHR 'A'
+#define ACL_VACUUM_CHR 'v'
+#define ACL_ANALYZE_CHR 'z'
/* string holding all privilege code chars, in order by bitmask position */
-#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsA"
+#define ACL_ALL_RIGHTS_STR "arwdDxtXUCTcsAvz"
/*
* Bitmasks defining "all rights" for each supported object type
*/
#define ACL_ALL_RIGHTS_COLUMN (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_REFERENCES)
-#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER)
+#define ACL_ALL_RIGHTS_RELATION (ACL_INSERT|ACL_SELECT|ACL_UPDATE|ACL_DELETE|ACL_TRUNCATE|ACL_REFERENCES|ACL_TRIGGER|ACL_VACUUM|ACL_ANALYZE)
#define ACL_ALL_RIGHTS_SEQUENCE (ACL_USAGE|ACL_SELECT|ACL_UPDATE)
#define ACL_ALL_RIGHTS_DATABASE (ACL_CREATE|ACL_CREATE_TEMP|ACL_CONNECT)
#define ACL_ALL_RIGHTS_FDW (ACL_USAGE)
diff --git a/src/test/regress/expected/dependency.out b/src/test/regress/expected/dependency.out
index 8232795148..81d8376509 100644
--- a/src/test/regress/expected/dependency.out
+++ b/src/test/regress/expected/dependency.out
@@ -19,7 +19,7 @@ DETAIL: privileges for table deptest
REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user;
+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, VACUUM, ANALYZE ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
ERROR: role "regress_dep_user" cannot be dropped because some objects depend on it
DETAIL: privileges for table deptest
@@ -63,21 +63,21 @@ CREATE TABLE deptest (a serial primary key, b text);
GRANT ALL ON deptest1 TO regress_dep_user2;
RESET SESSION AUTHORIZATION;
\z deptest1
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+----------+-------+----------------------------------------------------+-------------------+----------
- public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 +| |
- | | | regress_dep_user1=a*r*w*d*D*x*t*/regress_dep_user0+| |
- | | | regress_dep_user2=arwdDxt/regress_dep_user1 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+----------+-------+--------------------------------------------------------+-------------------+----------
+ public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 +| |
+ | | | regress_dep_user1=a*r*w*d*D*x*t*v*z*/regress_dep_user0+| |
+ | | | regress_dep_user2=arwdDxtvz/regress_dep_user1 | |
(1 row)
DROP OWNED BY regress_dep_user1;
-- all grants revoked
\z deptest1
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+----------+-------+---------------------------------------------+-------------------+----------
- public | deptest1 | table | regress_dep_user0=arwdDxt/regress_dep_user0 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+----------+-------+-----------------------------------------------+-------------------+----------
+ public | deptest1 | table | regress_dep_user0=arwdDxtvz/regress_dep_user0 | |
(1 row)
-- table was dropped
diff --git a/src/test/regress/expected/privileges.out b/src/test/regress/expected/privileges.out
index bd3453ee91..023bf75161 100644
--- a/src/test/regress/expected/privileges.out
+++ b/src/test/regress/expected/privileges.out
@@ -2561,39 +2561,39 @@ grant select on dep_priv_test to regress_priv_user4 with grant option;
set session role regress_priv_user4;
grant select on dep_priv_test to regress_priv_user5;
\dp dep_priv_test
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-----------------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| |
- | | | regress_priv_user2=r*/regress_priv_user1 +| |
- | | | regress_priv_user3=r*/regress_priv_user1 +| |
- | | | regress_priv_user4=r*/regress_priv_user2 +| |
- | | | regress_priv_user4=r*/regress_priv_user3 +| |
- | | | regress_priv_user5=r/regress_priv_user4 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+-------------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| |
+ | | | regress_priv_user2=r*/regress_priv_user1 +| |
+ | | | regress_priv_user3=r*/regress_priv_user1 +| |
+ | | | regress_priv_user4=r*/regress_priv_user2 +| |
+ | | | regress_priv_user4=r*/regress_priv_user3 +| |
+ | | | regress_priv_user5=r/regress_priv_user4 | |
(1 row)
set session role regress_priv_user2;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_test
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-----------------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| |
- | | | regress_priv_user2=r*/regress_priv_user1 +| |
- | | | regress_priv_user3=r*/regress_priv_user1 +| |
- | | | regress_priv_user4=r*/regress_priv_user3 +| |
- | | | regress_priv_user5=r/regress_priv_user4 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+-------------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| |
+ | | | regress_priv_user2=r*/regress_priv_user1 +| |
+ | | | regress_priv_user3=r*/regress_priv_user1 +| |
+ | | | regress_priv_user4=r*/regress_priv_user3 +| |
+ | | | regress_priv_user5=r/regress_priv_user4 | |
(1 row)
set session role regress_priv_user3;
revoke select on dep_priv_test from regress_priv_user4 cascade;
\dp dep_priv_test
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------+---------------+-------+-----------------------------------------------+-------------------+----------
- public | dep_priv_test | table | regress_priv_user1=arwdDxt/regress_priv_user1+| |
- | | | regress_priv_user2=r*/regress_priv_user1 +| |
- | | | regress_priv_user3=r*/regress_priv_user1 | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------+---------------+-------+-------------------------------------------------+-------------------+----------
+ public | dep_priv_test | table | regress_priv_user1=arwdDxtvz/regress_priv_user1+| |
+ | | | regress_priv_user2=r*/regress_priv_user1 +| |
+ | | | regress_priv_user3=r*/regress_priv_user1 | |
(1 row)
set session role regress_priv_user1;
@@ -2809,3 +2809,43 @@ DROP ROLE regress_group;
DROP ROLE regress_group_direct_manager;
DROP ROLE regress_group_indirect_manager;
DROP ROLE regress_group_member;
+-- VACUUM and ANALYZE
+CREATE ROLE regress_no_priv;
+CREATE ROLE regress_only_vacuum;
+CREATE ROLE regress_only_analyze;
+CREATE ROLE regress_both;
+CREATE TABLE vacanalyze_test (a INT);
+GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;
+GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both;
+SET ROLE regress_no_priv;
+VACUUM vacanalyze_test;
+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
+ANALYZE vacanalyze_test;
+WARNING: permission denied to analyze "vacanalyze_test", skipping it
+VACUUM (ANALYZE) vacanalyze_test;
+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
+RESET ROLE;
+SET ROLE regress_only_vacuum;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+WARNING: permission denied to analyze "vacanalyze_test", skipping it
+VACUUM (ANALYZE) vacanalyze_test;
+WARNING: permission denied to analyze "vacanalyze_test", skipping it
+RESET ROLE;
+SET ROLE regress_only_analyze;
+VACUUM vacanalyze_test;
+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+WARNING: permission denied to vacuum "vacanalyze_test", skipping it
+RESET ROLE;
+SET ROLE regress_both;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+DROP TABLE vacanalyze_test;
+DROP ROLE regress_no_priv;
+DROP ROLE regress_only_vacuum;
+DROP ROLE regress_only_analyze;
+DROP ROLE regress_both;
diff --git a/src/test/regress/expected/rowsecurity.out b/src/test/regress/expected/rowsecurity.out
index b5f6eecba1..ac21a11330 100644
--- a/src/test/regress/expected/rowsecurity.out
+++ b/src/test/regress/expected/rowsecurity.out
@@ -93,23 +93,23 @@ CREATE POLICY p2r ON document AS RESTRICTIVE TO regress_rls_dave
CREATE POLICY p1r ON document AS RESTRICTIVE TO regress_rls_dave
USING (cid <> 44);
\dp
- Access privileges
- Schema | Name | Type | Access privileges | Column privileges | Policies
---------------------+----------+-------+---------------------------------------------+-------------------+--------------------------------------------
- regress_rls_schema | category | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
- | | | =arwdDxt/regress_rls_alice | |
- regress_rls_schema | document | table | regress_rls_alice=arwdDxt/regress_rls_alice+| | p1: +
- | | | =arwdDxt/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +
- | | | | | FROM uaccount +
- | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+
- | | | | | p2r (RESTRICTIVE): +
- | | | | | (u): ((cid <> 44) AND (cid < 50)) +
- | | | | | to: regress_rls_dave +
- | | | | | p1r (RESTRICTIVE): +
- | | | | | (u): (cid <> 44) +
- | | | | | to: regress_rls_dave
- regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxt/regress_rls_alice+| |
- | | | =r/regress_rls_alice | |
+ Access privileges
+ Schema | Name | Type | Access privileges | Column privileges | Policies
+--------------------+----------+-------+-----------------------------------------------+-------------------+--------------------------------------------
+ regress_rls_schema | category | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| |
+ | | | =arwdDxtvz/regress_rls_alice | |
+ regress_rls_schema | document | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| | p1: +
+ | | | =arwdDxtvz/regress_rls_alice | | (u): (dlevel <= ( SELECT uaccount.seclv +
+ | | | | | FROM uaccount +
+ | | | | | WHERE (uaccount.pguser = CURRENT_USER)))+
+ | | | | | p2r (RESTRICTIVE): +
+ | | | | | (u): ((cid <> 44) AND (cid < 50)) +
+ | | | | | to: regress_rls_dave +
+ | | | | | p1r (RESTRICTIVE): +
+ | | | | | (u): (cid <> 44) +
+ | | | | | to: regress_rls_dave
+ regress_rls_schema | uaccount | table | regress_rls_alice=arwdDxtvz/regress_rls_alice+| |
+ | | | =r/regress_rls_alice | |
(3 rows)
\d document
diff --git a/src/test/regress/expected/vacuum.out b/src/test/regress/expected/vacuum.out
index 0035d158b7..e0fb21b36e 100644
--- a/src/test/regress/expected/vacuum.out
+++ b/src/test/regress/expected/vacuum.out
@@ -336,7 +336,9 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_parted", skipping it
WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
@@ -358,6 +360,7 @@ ANALYZE vacowned_part2;
WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
@@ -380,6 +383,7 @@ WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_parted", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
VACUUM (ANALYZE) vacowned_part2;
WARNING: permission denied to vacuum "vacowned_part2", skipping it
@@ -404,7 +408,9 @@ ANALYZE vacowned_part2;
WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_parted;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
+WARNING: permission denied to analyze "vacowned_part1", skipping it
WARNING: permission denied to vacuum "vacowned_part2", skipping it
+WARNING: permission denied to analyze "vacowned_part2", skipping it
VACUUM (ANALYZE) vacowned_part1;
WARNING: permission denied to vacuum "vacowned_part1", skipping it
VACUUM (ANALYZE) vacowned_part2;
diff --git a/src/test/regress/sql/dependency.sql b/src/test/regress/sql/dependency.sql
index 2559c62d0b..99b905a938 100644
--- a/src/test/regress/sql/dependency.sql
+++ b/src/test/regress/sql/dependency.sql
@@ -21,7 +21,7 @@ REVOKE SELECT ON deptest FROM GROUP regress_dep_group;
DROP GROUP regress_dep_group;
-- can't drop the user if we revoke the privileges partially
-REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES ON deptest FROM regress_dep_user;
+REVOKE SELECT, INSERT, UPDATE, DELETE, TRUNCATE, REFERENCES, VACUUM, ANALYZE ON deptest FROM regress_dep_user;
DROP USER regress_dep_user;
-- now we are OK to drop him
diff --git a/src/test/regress/sql/privileges.sql b/src/test/regress/sql/privileges.sql
index 4ad366470d..a8ebcc8b85 100644
--- a/src/test/regress/sql/privileges.sql
+++ b/src/test/regress/sql/privileges.sql
@@ -1813,3 +1813,43 @@ DROP ROLE regress_group;
DROP ROLE regress_group_direct_manager;
DROP ROLE regress_group_indirect_manager;
DROP ROLE regress_group_member;
+
+-- VACUUM and ANALYZE
+CREATE ROLE regress_no_priv;
+CREATE ROLE regress_only_vacuum;
+CREATE ROLE regress_only_analyze;
+CREATE ROLE regress_both;
+
+CREATE TABLE vacanalyze_test (a INT);
+GRANT VACUUM ON vacanalyze_test TO regress_only_vacuum, regress_both;
+GRANT ANALYZE ON vacanalyze_test TO regress_only_analyze, regress_both;
+
+SET ROLE regress_no_priv;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+
+SET ROLE regress_only_vacuum;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+
+SET ROLE regress_only_analyze;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+
+SET ROLE regress_both;
+VACUUM vacanalyze_test;
+ANALYZE vacanalyze_test;
+VACUUM (ANALYZE) vacanalyze_test;
+RESET ROLE;
+
+DROP TABLE vacanalyze_test;
+DROP ROLE regress_no_priv;
+DROP ROLE regress_only_vacuum;
+DROP ROLE regress_only_analyze;
+DROP ROLE regress_both;
--
2.25.1
--PEIAKu/WMn1b1Hv9
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v5-0004-Add-pg_vacuum_all_tables-and-pg_analyze_all_table.patch"
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-04-27 14:06 Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2023-04-27 14:06 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Fri, Apr 21, 2023 at 12:02 PM John Naylor
<[email protected]> wrote:
>
> I agree having the new functions in the tree is useful. I also tried running the TAP tests in v2, but 001 and 002 fail to run:
>
> Odd number of elements in hash assignment at [...]/Cluster.pm line 2010.
> Can't locate object method "pump_nb" via package "PostgreSQL::Test::BackgroundPsql" at [...]
>
> It seems to be complaining about
>
> +my $in = '';
> +my $out = '';
> +my $timeout = IPC::Run::timer($PostgreSQL::Test::Utils::timeout_default);
> +my $background_psql = $node->background_psql('postgres', \$in, \$out, $timeout);
>
> ...that call to background_psql doesn't look like other ones that have "key => value". Is there something I'm missing?
Thanks for reporting. I think that the patch needs to be updated since
commit 664d757531e1 changed background psql TAP functions. I've
attached the updated patch.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] v3-0001-Add-tests-for-XID-wraparound.patch (22.5K, ../../CAD21AoBZ3t+fRtVamQTA+wBJaapFUY1dfP08-rxsQ+fouPvgKg@mail.gmail.com/2-v3-0001-Add-tests-for-XID-wraparound.patch)
download | inline diff:
From 3cc02492129e56ba719d11c75a55732b22c97258 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Fri, 3 Mar 2023 12:01:28 +0200
Subject: [PATCH v3] Add tests for XID wraparound.
The test module includes helper functions to quickly burn through lots
of XIDs. They are used in the tests, and are also handy for manually
testing XID wraparound.
Author: Masahiko Sawada, Heikki Linnakangas
Discussion: https://www.postgresql.org/message-id/CAD21AoDVhkXp8HjpFO-gp3TgL6tCKcZQNxn04m01VAtcSi-5sA%40mail.gmail.com
---
src/test/modules/Makefile | 1 +
src/test/modules/meson.build | 1 +
src/test/modules/xid_wraparound/.gitignore | 4 +
src/test/modules/xid_wraparound/Makefile | 28 +++
src/test/modules/xid_wraparound/README | 3 +
src/test/modules/xid_wraparound/meson.build | 43 ++++
.../xid_wraparound/t/001_emergency_vacuum.pl | 115 +++++++++
.../modules/xid_wraparound/t/002_limits.pl | 109 +++++++++
.../xid_wraparound/t/003_wraparounds.pl | 46 ++++
.../xid_wraparound/xid_wraparound--1.0.sql | 12 +
.../modules/xid_wraparound/xid_wraparound.c | 221 ++++++++++++++++++
.../xid_wraparound/xid_wraparound.control | 4 +
12 files changed, 587 insertions(+)
create mode 100644 src/test/modules/xid_wraparound/.gitignore
create mode 100644 src/test/modules/xid_wraparound/Makefile
create mode 100644 src/test/modules/xid_wraparound/README
create mode 100644 src/test/modules/xid_wraparound/meson.build
create mode 100644 src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
create mode 100644 src/test/modules/xid_wraparound/t/002_limits.pl
create mode 100644 src/test/modules/xid_wraparound/t/003_wraparounds.pl
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.c
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.control
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 79e3033ec2..2e782519e0 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -14,6 +14,7 @@ SUBDIRS = \
plsample \
snapshot_too_old \
spgist_name_ops \
+ xid_wraparound \
test_bloomfilter \
test_copy_callbacks \
test_custom_rmgrs \
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index dcb82ed68f..30f989b3ce 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -11,6 +11,7 @@ subdir('plsample')
subdir('snapshot_too_old')
subdir('spgist_name_ops')
subdir('ssl_passphrase_callback')
+subdir('xid_wraparound')
subdir('test_bloomfilter')
subdir('test_copy_callbacks')
subdir('test_custom_rmgrs')
diff --git a/src/test/modules/xid_wraparound/.gitignore b/src/test/modules/xid_wraparound/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/src/test/modules/xid_wraparound/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/xid_wraparound/Makefile b/src/test/modules/xid_wraparound/Makefile
new file mode 100644
index 0000000000..fc5ead6cc5
--- /dev/null
+++ b/src/test/modules/xid_wraparound/Makefile
@@ -0,0 +1,28 @@
+# src/test/modules/xid_wraparound/Makefile
+
+MODULE_big = xid_wraparound
+OBJS = \
+ $(WIN32RES) \
+ xid_wraparound.o
+PGFILEDESC = "xid_wraparound - tests for XID wraparound"
+
+EXTENSION = xid_wraparound
+DATA = xid_wraparound--1.0.sql
+
+# Disabled by default because these tests could take a long time,
+# which typical installcheck users cannot tolerate (e.g. buildfarm
+# clients).
+ifdef ENABLE_XID_WRAPAROUND_TESTS
+TAP_TESTS = 1
+endif
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/xid_wraparound
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/xid_wraparound/README b/src/test/modules/xid_wraparound/README
new file mode 100644
index 0000000000..3aab464dec
--- /dev/null
+++ b/src/test/modules/xid_wraparound/README
@@ -0,0 +1,3 @@
+This module contains tests for XID wraparound. The tests use two
+helper functions to quickly consume lots of XIDs, to reach XID
+wraparound faster.
diff --git a/src/test/modules/xid_wraparound/meson.build b/src/test/modules/xid_wraparound/meson.build
new file mode 100644
index 0000000000..bdd55f22c4
--- /dev/null
+++ b/src/test/modules/xid_wraparound/meson.build
@@ -0,0 +1,43 @@
+# Copyright (c) 2022-2023, PostgreSQL Global Development Group
+
+# FIXME: prevent install during main install, but not during test :/
+
+xid_wraparound_sources = files(
+ 'xid_wraparound.c',
+)
+
+if host_system == 'windows'
+ xid_wraparound_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'xid_wraparound',
+ '--FILEDESC', 'xid_wraparound - tests for XID wraparound',])
+endif
+
+xid_wraparound = shared_module('xid_wraparound',
+ xid_wraparound_sources,
+ kwargs: pg_mod_args,
+)
+testprep_targets += xid_wraparound
+
+install_data(
+ 'xid_wraparound.control',
+ 'xid_wraparound--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+# Disabled by default because these test could take a long time,
+# which typical installcheck users cannot tolerate (e.g. buildfarm
+# clients).
+if false
+ tests += {
+ 'name': 'xid_wraparound',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'tap': {
+ 'tests': [
+ 't/001_emergency_vacuum.pl',
+ 't/002_limits.pl',
+ 't/003_wraparounds.pl',
+ ],
+ },
+ }
+endif
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
new file mode 100644
index 0000000000..f443c0ab86
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -0,0 +1,115 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+# Test wraparound emergency autovacuum.
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('main');
+
+$node->init;
+$node->append_conf('postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create tables for a few different test scenarios
+$node->safe_psql('postgres', qq[
+CREATE TABLE large(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large(data) SELECT generate_series(1,30000);
+
+CREATE TABLE large_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large_trunc(data) SELECT generate_series(1,30000);
+
+CREATE TABLE small(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small(data) SELECT generate_series(1,15000);
+
+CREATE TABLE small_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small_trunc(data) SELECT generate_series(1,15000);
+
+CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (autovacuum_enabled=false);
+INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
+]);
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $timeout = IPC::Run::timer($PostgreSQL::Test::Utils::timeout_default);
+my $background_psql = $node->background_psql('postgres',
+ on_error_stop => 0);
+$background_psql->query_safe(q{
+ BEGIN;
+ DELETE FROM large WHERE id % 2 = 0;
+ DELETE FROM large_trunc WHERE id > 10000;
+ DELETE FROM small WHERE id % 2 = 0;
+ DELETE FROM small_trunc WHERE id > 1000;
+ DELETE FROM autovacuum_disabled WHERE id % 2 = 0;
+});
+
+# Consume 2 billion XIDs, to get us very close to wraparound
+$node->safe_psql('postgres', qq[SELECT consume_xids_until('2000000000'::bigint)]);
+
+# Make sure the latest completed XID is advanced
+$node->safe_psql('postgres', qq[INSERT INTO small(data) SELECT 1]);
+
+# Check that all databases became old enough to trigger failsafe.
+my $ret = $node->safe_psql('postgres',
+ qq[
+SELECT datname,
+ age(datfrozenxid) > current_setting('vacuum_failsafe_age')::int as old
+FROM pg_database ORDER BY 1
+]);
+is($ret, "postgres|t
+template0|t
+template1|t", "all tables became old");
+
+my $log_offset = -s $node->logfile;
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# Wait until autovacuum processed all tables and advanced the
+# system-wide oldest-XID.
+$node->poll_query_until('postgres',
+ qq[
+SELECT NOT EXISTS (
+ SELECT *
+ FROM pg_database
+ WHERE age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::int)
+]) or die "timeout waiting for all databases to be vacuumed";
+
+# Check if these tables are vacuumed.
+$ret = $node->safe_psql('postgres', qq[
+SELECT relname, age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
+FROM pg_class
+WHERE relname IN ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+ORDER BY 1
+]);
+
+is($ret, "autovacuum_disabled|f
+large|f
+large_trunc|f
+small|f
+small_trunc|f", "all tables are vacuumed");
+
+# Check if vacuum failsafe was triggered for each table.
+my $log_contents = slurp_file($node->logfile, $log_offset);
+foreach my $tablename ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+{
+ like(
+ $log_contents,
+ qr/bypassing nonessential maintenance of table "postgres.public.$tablename" as a failsafe after \d+ index scans/,
+ "failsafe vacuum triggered for $tablename");
+}
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/002_limits.pl b/src/test/modules/xid_wraparound/t/002_limits.pl
new file mode 100644
index 0000000000..6c41fe7919
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/002_limits.pl
@@ -0,0 +1,109 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Test XID wraparound limits.
+#
+# When you get close to XID wraparound, you start to get warnings, and
+# when you get even closer, the system refuses to assign any more XIDs
+# until the oldest databases have been vacuumed and datfrozenxid has
+# been advanced.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+my $ret;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf('postgresql.conf', qq[
+autovacuum = off # run autovacuum only to prevent wraparound
+autovacuum_naptime = 1s
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql('postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('start');
+]);
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $timeout = IPC::Run::timer($PostgreSQL::Test::Utils::timeout_default);
+my $background_psql = $node->background_psql('postgres',
+ on_error_stop => 0);
+$background_psql->query_safe(q{
+ BEGIN;
+ INSERT INTO wraparoundtest VALUES ('oldxact');
+});
+
+# Consume 2 billion transactions, to get close to wraparound
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('after 1 billion')]);
+
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('after 2 billion')]);
+
+# We are now just under 150 million XIDs away from wraparound.
+# Continue consuming XIDs, in batches of 10 million, until we get
+# the warning:
+#
+# WARNING: database "postgres" must be vacuumed within 3000024 transactions
+# HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
+# You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
+my $stderr;
+my $warn_limit = 0;
+for my $i (1 .. 15)
+{
+ $node->psql('postgres', qq[SELECT consume_xids(10000000)], stderr => \$stderr, on_error_die => 1);
+
+ if ($stderr =~ /WARNING: database "postgres" must be vacuumed within [0-9]+ transactions/)
+ {
+ # Reached the warn-limit
+ $warn_limit = 1;
+ last;
+ }
+}
+ok($warn_limit == 1, "warn-limit reached");
+
+# We can still INSERT, despite the warnings.
+$node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('reached warn-limit')]);
+
+# Keep going. We'll hit the hard "stop" limit.
+$ret = $node->psql('postgres', qq[SELECT consume_xids(100000000)], stderr => \$stderr);
+like($stderr, qr/ERROR: database is not accepting commands to avoid wraparound data loss/, "stop-limit");
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# VACUUM, to freeze the tables and advance datfrozenxid.
+#
+# Autovacuum does this for the other databases, and would do it for
+# 'postgres' too, but let's test manual VACUUM.
+#
+$node->safe_psql('postgres', 'VACUUM');
+
+# Wait until autovacuum has processed the other databases and advanced
+# the system-wide oldest-XID.
+$ret = $node->poll_query_until('postgres', qq[INSERT INTO wraparoundtest VALUES ('after VACUUM')], 'INSERT 0 1');
+
+# Check the table contents
+$ret = $node->safe_psql('postgres', qq[SELECT * from wraparoundtest]);
+is($ret, "start
+oldxact
+after 1 billion
+after 2 billion
+reached warn-limit
+after VACUUM");
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
new file mode 100644
index 0000000000..e79adbd12d
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -0,0 +1,46 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Consume a lot of XIDs, wrapping around a few times.
+#
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf('postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql('postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('beginning');
+]);
+
+# Burn through 10 billion transactions in total, in batches of 100 million.
+my $ret;
+for my $i (1 .. 100)
+{
+ $ret = $node->safe_psql('postgres', qq[SELECT consume_xids(100000000)]);
+ $ret = $node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('after $i batches')]);
+}
+
+$ret = $node->safe_psql('postgres', qq[SELECT COUNT(*) FROM wraparoundtest]);
+is($ret, "101");
+
+$node->stop;
+
+done_testing();
diff --git a/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
new file mode 100644
index 0000000000..f5577adfdb
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
@@ -0,0 +1,12 @@
+/* src/test/modules/xid_wraparound/xid_wraparound--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION xid_wraparound" to load this file. \quit
+
+CREATE FUNCTION consume_xids(bigint)
+RETURNS bigint IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
+
+CREATE FUNCTION consume_xids_until(bigint)
+RETURNS bigint IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.c b/src/test/modules/xid_wraparound/xid_wraparound.c
new file mode 100644
index 0000000000..c9d6034b55
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.c
@@ -0,0 +1,221 @@
+/*--------------------------------------------------------------------------
+ *
+ * xid_wraparound.c
+ * Utilities for testing XID wraparound
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/test/modules/xid_wraparound/xid_wraparound.c
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/transam.h"
+#include "access/xact.h"
+#include "fmgr.h"
+#include "miscadmin.h"
+#include "storage/lwlock.h"
+#include "storage/proc.h"
+
+PG_MODULE_MAGIC;
+
+static int64 consume_xids_shortcut(void);
+static FullTransactionId consume_xids_common(FullTransactionId untilxid, uint64 nxids);
+
+/*
+ * Consume the specified number of XIDs.
+ */
+PG_FUNCTION_INFO_V1(consume_xids);
+Datum
+consume_xids(PG_FUNCTION_ARGS)
+{
+ int64 nxids = PG_GETARG_INT64(0);
+ FullTransactionId lastxid;
+
+ if (nxids < 0)
+ elog(ERROR, "invalid nxids argument: %lld", (long long) nxids);
+
+ if (nxids == 0)
+ lastxid = ReadNextFullTransactionId();
+ else
+ lastxid = consume_xids_common(InvalidFullTransactionId, (uint64) nxids);
+
+ PG_RETURN_INT64((int64) U64FromFullTransactionId(lastxid));
+}
+
+/*
+ * Consume XIDs, up to the given XID.
+ */
+PG_FUNCTION_INFO_V1(consume_xids_until);
+Datum
+consume_xids_until(PG_FUNCTION_ARGS)
+{
+ FullTransactionId targetxid = FullTransactionIdFromU64((uint64) PG_GETARG_INT64(0));
+ FullTransactionId lastxid;
+
+ if (!FullTransactionIdIsNormal(targetxid))
+ elog(ERROR, "targetxid %llu is not normal", (unsigned long long) U64FromFullTransactionId(targetxid));
+
+ lastxid = consume_xids_common(targetxid, 0);
+
+ PG_RETURN_INT64((int64) U64FromFullTransactionId(lastxid));
+}
+
+/*
+ * Common functionality between the two public functions.
+ */
+static FullTransactionId
+consume_xids_common(FullTransactionId untilxid, uint64 nxids)
+{
+ FullTransactionId lastxid;
+ uint64 last_reported_at = 0;
+ uint64 consumed = 0;
+
+ /* Print a NOTICE every REPORT_INTERVAL xids */
+#define REPORT_INTERVAL (10*1000000)
+
+ /* initialize 'lastxid' with the system's current next XID */
+ lastxid = ReadNextFullTransactionId();
+
+ /*
+ * We consume XIDs by calling GetNewTransactionId(true), which marks the
+ * consumed XIDs as subtransactions of the current top-level transaction.
+ * For that to work, this transaction must have a top-level XID.
+ *
+ * GetNewTransactionId registers them in the subxid cache in PGPROC, until
+ * the cache overflows, but beyond that, we don't keep track of the
+ * consumed XIDs.
+ */
+ (void) GetTopTransactionId();
+
+ for (;;)
+ {
+ uint64 xids_left;
+
+ CHECK_FOR_INTERRUPTS();
+
+ /* How many XIDs do we have left to consume? */
+ if (nxids > 0)
+ {
+ if (consumed >= nxids)
+ break;
+ xids_left = nxids - consumed;
+ }
+ else
+ {
+ if (FullTransactionIdFollowsOrEquals(lastxid, untilxid))
+ break;
+ xids_left = U64FromFullTransactionId(untilxid) - U64FromFullTransactionId(lastxid);
+ }
+
+ /*
+ * If we still have plenty of XIDs to consume, try to take a shortcut
+ * and bump up the nextXid counter directly.
+ */
+ if (xids_left > 2000 &&
+ consumed - last_reported_at < REPORT_INTERVAL &&
+ MyProc->subxidStatus.overflowed)
+ {
+ int64 consumed_by_shortcut = consume_xids_shortcut();
+
+ if (consumed_by_shortcut > 0)
+ {
+ consumed += consumed_by_shortcut;
+ continue;
+ }
+ }
+
+ /* Slow path: Call GetNewTransactionId to allocate a new XID. */
+ lastxid = GetNewTransactionId(true);
+ consumed++;
+
+ /* Report progress */
+ if (consumed - last_reported_at >= REPORT_INTERVAL)
+ {
+ if (nxids > 0)
+ elog(NOTICE, "consumed %llu / %llu XIDs, latest %u:%u",
+ (unsigned long long) consumed, (unsigned long long) nxids,
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid));
+ else
+ elog(NOTICE, "consumed up to %u:%u / %u:%u",
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid),
+ EpochFromFullTransactionId(untilxid),
+ XidFromFullTransactionId(untilxid));
+ last_reported_at = consumed;
+ }
+ }
+
+ return lastxid;
+}
+
+
+/*
+ * These constants copied from .c files, because they're private.
+ */
+#define COMMIT_TS_XACTS_PER_PAGE (BLCKSZ / 10)
+#define SUBTRANS_XACTS_PER_PAGE (BLCKSZ / sizeof(TransactionId))
+#define CLOG_XACTS_PER_BYTE 4
+#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
+
+/*
+ * All the interesting action in GetNewTransactionId happens when we extend
+ * the SLRUs, or at the uint32 wraparound. If the nextXid counter is not close
+ * to any of those interesting values, take a shortcut and bump nextXID
+ * directly, close to the next "interesting" value.
+ */
+static inline uint32
+XidSkip(FullTransactionId fullxid)
+{
+ uint32 low = XidFromFullTransactionId(fullxid);
+ uint32 rem;
+ uint32 distance;
+
+ if (low < 5 || low >= UINT32_MAX - 5)
+ return 0;
+ distance = UINT32_MAX - 5 - low;
+
+ rem = low % COMMIT_TS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, COMMIT_TS_XACTS_PER_PAGE - rem);
+
+ rem = low % SUBTRANS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, SUBTRANS_XACTS_PER_PAGE - rem);
+
+ rem = low % CLOG_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, CLOG_XACTS_PER_PAGE - rem);
+
+ return distance;
+}
+
+static int64
+consume_xids_shortcut(void)
+{
+ FullTransactionId nextXid;
+ uint32 consumed;
+
+ LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
+ nextXid = ShmemVariableCache->nextXid;
+
+ /*
+ * Go slow near the "interesting values". The interesting zones include 5
+ * transactions before and after SLRU page switches.
+ */
+ consumed = XidSkip(nextXid);
+ if (consumed > 0)
+ ShmemVariableCache->nextXid.value += (uint64) consumed;
+
+ LWLockRelease(XidGenLock);
+
+ return consumed;
+}
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.control b/src/test/modules/xid_wraparound/xid_wraparound.control
new file mode 100644
index 0000000000..6c6964ed3d
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.control
@@ -0,0 +1,4 @@
+comment = 'Tests for XID wraparound'
+default_version = '1.0'
+module_pathname = '$libdir/xid_wraparound'
+relocatable = true
--
2.31.1
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-04-27 14:12 Daniel Gustafsson <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Daniel Gustafsson @ 2023-04-27 14:12 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
> On 27 Apr 2023, at 16:06, Masahiko Sawada <[email protected]> wrote:
> On Fri, Apr 21, 2023 at 12:02 PM John Naylor
> <[email protected]> wrote:
>> ...that call to background_psql doesn't look like other ones that have "key => value". Is there something I'm missing?
>
> Thanks for reporting. I think that the patch needs to be updated since
> commit 664d757531e1 changed background psql TAP functions. I've
> attached the updated patch.
Is there a risk that the background psql will time out on slow systems during
the consumption of 2B xid's? Since you mainly want to hold it open for the
duration of testing you might want to bump it to avoid false negatives on slow
test systems.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-04-28 03:21 John Naylor <[email protected]>
parent: Daniel Gustafsson <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: John Naylor @ 2023-04-28 03:21 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Thu, Apr 27, 2023 at 9:12 PM Daniel Gustafsson <[email protected]> wrote:
>
> > On 27 Apr 2023, at 16:06, Masahiko Sawada <[email protected]> wrote:
> > On Fri, Apr 21, 2023 at 12:02 PM John Naylor
> > <[email protected]> wrote:
>
> >> ...that call to background_psql doesn't look like other ones that have
"key => value". Is there something I'm missing?
> >
> > Thanks for reporting. I think that the patch needs to be updated since
> > commit 664d757531e1 changed background psql TAP functions. I've
> > attached the updated patch.
Thanks, it passes for me now.
> Is there a risk that the background psql will time out on slow systems
during
> the consumption of 2B xid's? Since you mainly want to hold it open for
the
> duration of testing you might want to bump it to avoid false negatives on
slow
> test systems.
If they're that slow, I'd worry more about generating 20GB of xact status
data. That's why the tests are disabled by default.
--
John Naylor
EDB: http://www.enterprisedb.com
On Thu, Apr 27, 2023 at 9:12 PM Daniel Gustafsson <[email protected]> wrote:
> > On 27 Apr 2023, at 16:06, Masahiko Sawada <[email protected]> wrote:
> > On Fri, Apr 21, 2023 at 12:02 PM John Naylor
> > <[email protected]> wrote:
>
> >> ...that call to background_psql doesn't look like other ones that have
> "key => value". Is there something I'm missing?
> >
> > Thanks for reporting. I think that the patch needs to be updated since
> > commit 664d757531e1 changed background psql TAP functions. I've
> > attached the updated patch.
>
> Is there a risk that the background psql will time out on slow systems
> during
> the consumption of 2B xid's? Since you mainly want to hold it open for the
> duration of testing you might want to bump it to avoid false negatives on
> slow
> test systems.
>
> --
> Daniel Gustafsson
>
>
--
John Naylor
EDB: http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-04-28 04:42 Tom Lane <[email protected]>
parent: John Naylor <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Tom Lane @ 2023-04-28 04:42 UTC (permalink / raw)
To: John Naylor <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Masahiko Sawada <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
John Naylor <[email protected]> writes:
> On Thu, Apr 27, 2023 at 9:12 PM Daniel Gustafsson <[email protected]> wrote:
>> Is there a risk that the background psql will time out on slow systems during
>> the consumption of 2B xid's? Since you mainly want to hold it open for the
>> duration of testing you might want to bump it to avoid false negatives on
>> slow test systems.
> If they're that slow, I'd worry more about generating 20GB of xact status
> data. That's why the tests are disabled by default.
There is exactly zero chance that anyone will accept the introduction
of such an expensive test into either check-world or the buildfarm
sequence.
regards, tom lane
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-07-12 07:52 Masahiko Sawada <[email protected]>
parent: Daniel Gustafsson <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2023-07-12 07:52 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Thu, Apr 27, 2023 at 11:12 PM Daniel Gustafsson <[email protected]> wrote:
>
> > On 27 Apr 2023, at 16:06, Masahiko Sawada <[email protected]> wrote:
> > On Fri, Apr 21, 2023 at 12:02 PM John Naylor
> > <[email protected]> wrote:
>
> >> ...that call to background_psql doesn't look like other ones that have "key => value". Is there something I'm missing?
> >
> > Thanks for reporting. I think that the patch needs to be updated since
> > commit 664d757531e1 changed background psql TAP functions. I've
> > attached the updated patch.
>
> Is there a risk that the background psql will time out on slow systems during
> the consumption of 2B xid's? Since you mainly want to hold it open for the
> duration of testing you might want to bump it to avoid false negatives on slow
> test systems.
Agreed. The timeout can be set by manually setting
PG_TEST_TIMEOUT_DEFAULT, but I bump it to 10 min by default. And it
now require setting PG_TET_EXTRA to run it.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] v4-0001-Add-tests-for-XID-wraparound.patch (23.2K, ../../CAD21AoCmwfbpmiLk3qtD06osv_NKjmJpeoGNhCN1C3-0HFPBfA@mail.gmail.com/2-v4-0001-Add-tests-for-XID-wraparound.patch)
download | inline diff:
From 10920f76b2a77f48e495f2541c749fa8bb970e78 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Fri, 3 Mar 2023 12:01:28 +0200
Subject: [PATCH v4] Add tests for XID wraparound.
The test module includes helper functions to quickly burn through lots
of XIDs. They are used in the tests, and are also handy for manually
testing XID wraparound.
Since these tests are very expensive the entire suite is disabled by
default. It requires to set PG_TET_EXTRA to run it.
Author: Masahiko Sawada, Heikki Linnakangas
Discussion: https://www.postgresql.org/message-id/CAD21AoDVhkXp8HjpFO-gp3TgL6tCKcZQNxn04m01VAtcSi-5sA%40mail.gmail.com
---
src/test/modules/Makefile | 1 +
src/test/modules/meson.build | 1 +
src/test/modules/xid_wraparound/.gitignore | 4 +
src/test/modules/xid_wraparound/Makefile | 23 ++
src/test/modules/xid_wraparound/README | 3 +
src/test/modules/xid_wraparound/meson.build | 43 ++++
.../xid_wraparound/t/001_emergency_vacuum.pl | 125 ++++++++++
.../modules/xid_wraparound/t/002_limits.pl | 118 ++++++++++
.../xid_wraparound/t/003_wraparounds.pl | 55 +++++
.../xid_wraparound/xid_wraparound--1.0.sql | 12 +
.../modules/xid_wraparound/xid_wraparound.c | 221 ++++++++++++++++++
.../xid_wraparound/xid_wraparound.control | 4 +
12 files changed, 610 insertions(+)
create mode 100644 src/test/modules/xid_wraparound/.gitignore
create mode 100644 src/test/modules/xid_wraparound/Makefile
create mode 100644 src/test/modules/xid_wraparound/README
create mode 100644 src/test/modules/xid_wraparound/meson.build
create mode 100644 src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
create mode 100644 src/test/modules/xid_wraparound/t/002_limits.pl
create mode 100644 src/test/modules/xid_wraparound/t/003_wraparounds.pl
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.c
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.control
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 6331c976dc..3c6eb33b38 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -14,6 +14,7 @@ SUBDIRS = \
plsample \
snapshot_too_old \
spgist_name_ops \
+ xid_wraparound \
test_bloomfilter \
test_copy_callbacks \
test_custom_rmgrs \
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index 17d369e378..cb63901e32 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -11,6 +11,7 @@ subdir('plsample')
subdir('snapshot_too_old')
subdir('spgist_name_ops')
subdir('ssl_passphrase_callback')
+subdir('xid_wraparound')
subdir('test_bloomfilter')
subdir('test_copy_callbacks')
subdir('test_custom_rmgrs')
diff --git a/src/test/modules/xid_wraparound/.gitignore b/src/test/modules/xid_wraparound/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/src/test/modules/xid_wraparound/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/xid_wraparound/Makefile b/src/test/modules/xid_wraparound/Makefile
new file mode 100644
index 0000000000..7a6e0f6676
--- /dev/null
+++ b/src/test/modules/xid_wraparound/Makefile
@@ -0,0 +1,23 @@
+# src/test/modules/xid_wraparound/Makefile
+
+MODULE_big = xid_wraparound
+OBJS = \
+ $(WIN32RES) \
+ xid_wraparound.o
+PGFILEDESC = "xid_wraparound - tests for XID wraparound"
+
+EXTENSION = xid_wraparound
+DATA = xid_wraparound--1.0.sql
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/xid_wraparound
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/xid_wraparound/README b/src/test/modules/xid_wraparound/README
new file mode 100644
index 0000000000..3aab464dec
--- /dev/null
+++ b/src/test/modules/xid_wraparound/README
@@ -0,0 +1,3 @@
+This module contains tests for XID wraparound. The tests use two
+helper functions to quickly consume lots of XIDs, to reach XID
+wraparound faster.
diff --git a/src/test/modules/xid_wraparound/meson.build b/src/test/modules/xid_wraparound/meson.build
new file mode 100644
index 0000000000..bdd55f22c4
--- /dev/null
+++ b/src/test/modules/xid_wraparound/meson.build
@@ -0,0 +1,43 @@
+# Copyright (c) 2022-2023, PostgreSQL Global Development Group
+
+# FIXME: prevent install during main install, but not during test :/
+
+xid_wraparound_sources = files(
+ 'xid_wraparound.c',
+)
+
+if host_system == 'windows'
+ xid_wraparound_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'xid_wraparound',
+ '--FILEDESC', 'xid_wraparound - tests for XID wraparound',])
+endif
+
+xid_wraparound = shared_module('xid_wraparound',
+ xid_wraparound_sources,
+ kwargs: pg_mod_args,
+)
+testprep_targets += xid_wraparound
+
+install_data(
+ 'xid_wraparound.control',
+ 'xid_wraparound--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+# Disabled by default because these test could take a long time,
+# which typical installcheck users cannot tolerate (e.g. buildfarm
+# clients).
+if false
+ tests += {
+ 'name': 'xid_wraparound',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'tap': {
+ 'tests': [
+ 't/001_emergency_vacuum.pl',
+ 't/002_limits.pl',
+ 't/003_wraparounds.pl',
+ ],
+ },
+ }
+endif
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
new file mode 100644
index 0000000000..ed3e722f67
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -0,0 +1,125 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+# Test wraparound emergency autovacuum.
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all =>
+ "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# bump the query timeout to avoid false negatives on slow test syetems.
+$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('main');
+
+$node->init;
+$node->append_conf('postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create tables for a few different test scenarios
+$node->safe_psql('postgres', qq[
+CREATE TABLE large(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large(data) SELECT generate_series(1,30000);
+
+CREATE TABLE large_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large_trunc(data) SELECT generate_series(1,30000);
+
+CREATE TABLE small(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small(data) SELECT generate_series(1,15000);
+
+CREATE TABLE small_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small_trunc(data) SELECT generate_series(1,15000);
+
+CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (autovacuum_enabled=false);
+INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
+]);
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $timeout = IPC::Run::timer($PostgreSQL::Test::Utils::timeout_default);
+my $background_psql = $node->background_psql('postgres',
+ on_error_stop => 0);
+$background_psql->set_query_timer_restart();
+$background_psql->query_safe(q{
+ BEGIN;
+ DELETE FROM large WHERE id % 2 = 0;
+ DELETE FROM large_trunc WHERE id > 10000;
+ DELETE FROM small WHERE id % 2 = 0;
+ DELETE FROM small_trunc WHERE id > 1000;
+ DELETE FROM autovacuum_disabled WHERE id % 2 = 0;
+});
+
+# Consume 2 billion XIDs, to get us very close to wraparound
+$node->safe_psql('postgres', qq[SELECT consume_xids_until('2000000000'::bigint)]);
+
+# Make sure the latest completed XID is advanced
+$node->safe_psql('postgres', qq[INSERT INTO small(data) SELECT 1]);
+
+# Check that all databases became old enough to trigger failsafe.
+my $ret = $node->safe_psql('postgres',
+ qq[
+SELECT datname,
+ age(datfrozenxid) > current_setting('vacuum_failsafe_age')::int as old
+FROM pg_database ORDER BY 1
+]);
+is($ret, "postgres|t
+template0|t
+template1|t", "all tables became old");
+
+my $log_offset = -s $node->logfile;
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# Wait until autovacuum processed all tables and advanced the
+# system-wide oldest-XID.
+$node->poll_query_until('postgres',
+ qq[
+SELECT NOT EXISTS (
+ SELECT *
+ FROM pg_database
+ WHERE age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::int)
+]) or die "timeout waiting for all databases to be vacuumed";
+
+# Check if these tables are vacuumed.
+$ret = $node->safe_psql('postgres', qq[
+SELECT relname, age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
+FROM pg_class
+WHERE relname IN ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+ORDER BY 1
+]);
+
+is($ret, "autovacuum_disabled|f
+large|f
+large_trunc|f
+small|f
+small_trunc|f", "all tables are vacuumed");
+
+# Check if vacuum failsafe was triggered for each table.
+my $log_contents = slurp_file($node->logfile, $log_offset);
+foreach my $tablename ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+{
+ like(
+ $log_contents,
+ qr/bypassing nonessential maintenance of table "postgres.public.$tablename" as a failsafe after \d+ index scans/,
+ "failsafe vacuum triggered for $tablename");
+}
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/002_limits.pl b/src/test/modules/xid_wraparound/t/002_limits.pl
new file mode 100644
index 0000000000..24cc5f272d
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/002_limits.pl
@@ -0,0 +1,118 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Test XID wraparound limits.
+#
+# When you get close to XID wraparound, you start to get warnings, and
+# when you get even closer, the system refuses to assign any more XIDs
+# until the oldest databases have been vacuumed and datfrozenxid has
+# been advanced.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all =>
+ "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# bump the query timeout to avoid false negatives on slow test syetems.
+$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
+
+my $ret;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf('postgresql.conf', qq[
+autovacuum = off # run autovacuum only to prevent wraparound
+autovacuum_naptime = 1s
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql('postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('start');
+]);
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $timeout = IPC::Run::timer($PostgreSQL::Test::Utils::timeout_default);
+my $background_psql = $node->background_psql('postgres',
+ on_error_stop => 0);
+$background_psql->query_safe(q{
+ BEGIN;
+ INSERT INTO wraparoundtest VALUES ('oldxact');
+});
+
+# Consume 2 billion transactions, to get close to wraparound
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('after 1 billion')]);
+
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('after 2 billion')]);
+
+# We are now just under 150 million XIDs away from wraparound.
+# Continue consuming XIDs, in batches of 10 million, until we get
+# the warning:
+#
+# WARNING: database "postgres" must be vacuumed within 3000024 transactions
+# HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
+# You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
+my $stderr;
+my $warn_limit = 0;
+for my $i (1 .. 15)
+{
+ $node->psql('postgres', qq[SELECT consume_xids(10000000)], stderr => \$stderr, on_error_die => 1);
+
+ if ($stderr =~ /WARNING: database "postgres" must be vacuumed within [0-9]+ transactions/)
+ {
+ # Reached the warn-limit
+ $warn_limit = 1;
+ last;
+ }
+}
+ok($warn_limit == 1, "warn-limit reached");
+
+# We can still INSERT, despite the warnings.
+$node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('reached warn-limit')]);
+
+# Keep going. We'll hit the hard "stop" limit.
+$ret = $node->psql('postgres', qq[SELECT consume_xids(100000000)], stderr => \$stderr);
+like($stderr, qr/ERROR: database is not accepting commands to avoid wraparound data loss/, "stop-limit");
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# VACUUM, to freeze the tables and advance datfrozenxid.
+#
+# Autovacuum does this for the other databases, and would do it for
+# 'postgres' too, but let's test manual VACUUM.
+#
+$node->safe_psql('postgres', 'VACUUM');
+
+# Wait until autovacuum has processed the other databases and advanced
+# the system-wide oldest-XID.
+$ret = $node->poll_query_until('postgres', qq[INSERT INTO wraparoundtest VALUES ('after VACUUM')], 'INSERT 0 1');
+
+# Check the table contents
+$ret = $node->safe_psql('postgres', qq[SELECT * from wraparoundtest]);
+is($ret, "start
+oldxact
+after 1 billion
+after 2 billion
+reached warn-limit
+after VACUUM");
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
new file mode 100644
index 0000000000..e018e6a433
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -0,0 +1,55 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Consume a lot of XIDs, wrapping around a few times.
+#
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all =>
+ "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# bump the query timeout to avoid false negatives on slow test syetems.
+$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf('postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql('postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('beginning');
+]);
+
+# Burn through 10 billion transactions in total, in batches of 100 million.
+my $ret;
+for my $i (1 .. 100)
+{
+ $ret = $node->safe_psql('postgres', qq[SELECT consume_xids(100000000)]);
+ $ret = $node->safe_psql('postgres', qq[INSERT INTO wraparoundtest VALUES ('after $i batches')]);
+}
+
+$ret = $node->safe_psql('postgres', qq[SELECT COUNT(*) FROM wraparoundtest]);
+is($ret, "101");
+
+$node->stop;
+
+done_testing();
diff --git a/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
new file mode 100644
index 0000000000..f5577adfdb
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
@@ -0,0 +1,12 @@
+/* src/test/modules/xid_wraparound/xid_wraparound--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION xid_wraparound" to load this file. \quit
+
+CREATE FUNCTION consume_xids(bigint)
+RETURNS bigint IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
+
+CREATE FUNCTION consume_xids_until(bigint)
+RETURNS bigint IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.c b/src/test/modules/xid_wraparound/xid_wraparound.c
new file mode 100644
index 0000000000..c9d6034b55
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.c
@@ -0,0 +1,221 @@
+/*--------------------------------------------------------------------------
+ *
+ * xid_wraparound.c
+ * Utilities for testing XID wraparound
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/test/modules/xid_wraparound/xid_wraparound.c
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/transam.h"
+#include "access/xact.h"
+#include "fmgr.h"
+#include "miscadmin.h"
+#include "storage/lwlock.h"
+#include "storage/proc.h"
+
+PG_MODULE_MAGIC;
+
+static int64 consume_xids_shortcut(void);
+static FullTransactionId consume_xids_common(FullTransactionId untilxid, uint64 nxids);
+
+/*
+ * Consume the specified number of XIDs.
+ */
+PG_FUNCTION_INFO_V1(consume_xids);
+Datum
+consume_xids(PG_FUNCTION_ARGS)
+{
+ int64 nxids = PG_GETARG_INT64(0);
+ FullTransactionId lastxid;
+
+ if (nxids < 0)
+ elog(ERROR, "invalid nxids argument: %lld", (long long) nxids);
+
+ if (nxids == 0)
+ lastxid = ReadNextFullTransactionId();
+ else
+ lastxid = consume_xids_common(InvalidFullTransactionId, (uint64) nxids);
+
+ PG_RETURN_INT64((int64) U64FromFullTransactionId(lastxid));
+}
+
+/*
+ * Consume XIDs, up to the given XID.
+ */
+PG_FUNCTION_INFO_V1(consume_xids_until);
+Datum
+consume_xids_until(PG_FUNCTION_ARGS)
+{
+ FullTransactionId targetxid = FullTransactionIdFromU64((uint64) PG_GETARG_INT64(0));
+ FullTransactionId lastxid;
+
+ if (!FullTransactionIdIsNormal(targetxid))
+ elog(ERROR, "targetxid %llu is not normal", (unsigned long long) U64FromFullTransactionId(targetxid));
+
+ lastxid = consume_xids_common(targetxid, 0);
+
+ PG_RETURN_INT64((int64) U64FromFullTransactionId(lastxid));
+}
+
+/*
+ * Common functionality between the two public functions.
+ */
+static FullTransactionId
+consume_xids_common(FullTransactionId untilxid, uint64 nxids)
+{
+ FullTransactionId lastxid;
+ uint64 last_reported_at = 0;
+ uint64 consumed = 0;
+
+ /* Print a NOTICE every REPORT_INTERVAL xids */
+#define REPORT_INTERVAL (10*1000000)
+
+ /* initialize 'lastxid' with the system's current next XID */
+ lastxid = ReadNextFullTransactionId();
+
+ /*
+ * We consume XIDs by calling GetNewTransactionId(true), which marks the
+ * consumed XIDs as subtransactions of the current top-level transaction.
+ * For that to work, this transaction must have a top-level XID.
+ *
+ * GetNewTransactionId registers them in the subxid cache in PGPROC, until
+ * the cache overflows, but beyond that, we don't keep track of the
+ * consumed XIDs.
+ */
+ (void) GetTopTransactionId();
+
+ for (;;)
+ {
+ uint64 xids_left;
+
+ CHECK_FOR_INTERRUPTS();
+
+ /* How many XIDs do we have left to consume? */
+ if (nxids > 0)
+ {
+ if (consumed >= nxids)
+ break;
+ xids_left = nxids - consumed;
+ }
+ else
+ {
+ if (FullTransactionIdFollowsOrEquals(lastxid, untilxid))
+ break;
+ xids_left = U64FromFullTransactionId(untilxid) - U64FromFullTransactionId(lastxid);
+ }
+
+ /*
+ * If we still have plenty of XIDs to consume, try to take a shortcut
+ * and bump up the nextXid counter directly.
+ */
+ if (xids_left > 2000 &&
+ consumed - last_reported_at < REPORT_INTERVAL &&
+ MyProc->subxidStatus.overflowed)
+ {
+ int64 consumed_by_shortcut = consume_xids_shortcut();
+
+ if (consumed_by_shortcut > 0)
+ {
+ consumed += consumed_by_shortcut;
+ continue;
+ }
+ }
+
+ /* Slow path: Call GetNewTransactionId to allocate a new XID. */
+ lastxid = GetNewTransactionId(true);
+ consumed++;
+
+ /* Report progress */
+ if (consumed - last_reported_at >= REPORT_INTERVAL)
+ {
+ if (nxids > 0)
+ elog(NOTICE, "consumed %llu / %llu XIDs, latest %u:%u",
+ (unsigned long long) consumed, (unsigned long long) nxids,
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid));
+ else
+ elog(NOTICE, "consumed up to %u:%u / %u:%u",
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid),
+ EpochFromFullTransactionId(untilxid),
+ XidFromFullTransactionId(untilxid));
+ last_reported_at = consumed;
+ }
+ }
+
+ return lastxid;
+}
+
+
+/*
+ * These constants copied from .c files, because they're private.
+ */
+#define COMMIT_TS_XACTS_PER_PAGE (BLCKSZ / 10)
+#define SUBTRANS_XACTS_PER_PAGE (BLCKSZ / sizeof(TransactionId))
+#define CLOG_XACTS_PER_BYTE 4
+#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
+
+/*
+ * All the interesting action in GetNewTransactionId happens when we extend
+ * the SLRUs, or at the uint32 wraparound. If the nextXid counter is not close
+ * to any of those interesting values, take a shortcut and bump nextXID
+ * directly, close to the next "interesting" value.
+ */
+static inline uint32
+XidSkip(FullTransactionId fullxid)
+{
+ uint32 low = XidFromFullTransactionId(fullxid);
+ uint32 rem;
+ uint32 distance;
+
+ if (low < 5 || low >= UINT32_MAX - 5)
+ return 0;
+ distance = UINT32_MAX - 5 - low;
+
+ rem = low % COMMIT_TS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, COMMIT_TS_XACTS_PER_PAGE - rem);
+
+ rem = low % SUBTRANS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, SUBTRANS_XACTS_PER_PAGE - rem);
+
+ rem = low % CLOG_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, CLOG_XACTS_PER_PAGE - rem);
+
+ return distance;
+}
+
+static int64
+consume_xids_shortcut(void)
+{
+ FullTransactionId nextXid;
+ uint32 consumed;
+
+ LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
+ nextXid = ShmemVariableCache->nextXid;
+
+ /*
+ * Go slow near the "interesting values". The interesting zones include 5
+ * transactions before and after SLRU page switches.
+ */
+ consumed = XidSkip(nextXid);
+ if (consumed > 0)
+ ShmemVariableCache->nextXid.value += (uint64) consumed;
+
+ LWLockRelease(XidGenLock);
+
+ return consumed;
+}
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.control b/src/test/modules/xid_wraparound/xid_wraparound.control
new file mode 100644
index 0000000000..6c6964ed3d
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.control
@@ -0,0 +1,4 @@
+comment = 'Tests for XID wraparound'
+default_version = '1.0'
+module_pathname = '$libdir/xid_wraparound'
+relocatable = true
--
2.31.1
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-07-12 11:47 Daniel Gustafsson <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Daniel Gustafsson @ 2023-07-12 11:47 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
> On 12 Jul 2023, at 09:52, Masahiko Sawada <[email protected]> wrote:
> Agreed. The timeout can be set by manually setting
> PG_TEST_TIMEOUT_DEFAULT, but I bump it to 10 min by default. And it
> now require setting PG_TET_EXTRA to run it.
+# bump the query timeout to avoid false negatives on slow test syetems.
typo: s/syetems/systems/
+# bump the query timeout to avoid false negatives on slow test syetems.
+$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
Does this actually work? Utils.pm read the environment variable at compile
time in the BEGIN block so this setting won't be seen? A quick testprogram
seems to confirm this but I might be missing something.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-08-22 05:49 Michael Paquier <[email protected]>
parent: Daniel Gustafsson <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: Michael Paquier @ 2023-08-22 05:49 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Wed, Jul 12, 2023 at 01:47:51PM +0200, Daniel Gustafsson wrote:
> +# bump the query timeout to avoid false negatives on slow test syetems.
> +$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
> Does this actually work? Utils.pm read the environment variable at compile
> time in the BEGIN block so this setting won't be seen? A quick testprogram
> seems to confirm this but I might be missing something.
I wish that this test were cheaper, without a need to depend on
PG_TEST_EXTRA.. Actually, note that you are forgetting to update the
documentation of PG_TEST_EXTRA with this new value of xid_wraparound.
--
Michael
Attachments:
[application/pgp-signature] signature.asc (833B, ../../[email protected]/2-signature.asc)
download
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-09-03 05:48 Noah Misch <[email protected]>
parent: Daniel Gustafsson <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Noah Misch @ 2023-09-03 05:48 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; Daniel Gustafsson <[email protected]>; +Cc: John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Wed, Jul 12, 2023 at 01:47:51PM +0200, Daniel Gustafsson wrote:
> > On 12 Jul 2023, at 09:52, Masahiko Sawada <[email protected]> wrote:
> > Agreed. The timeout can be set by manually setting
> > PG_TEST_TIMEOUT_DEFAULT, but I bump it to 10 min by default. And it
> > now require setting PG_TET_EXTRA to run it.
>
> +# bump the query timeout to avoid false negatives on slow test syetems.
> typo: s/syetems/systems/
>
>
> +# bump the query timeout to avoid false negatives on slow test syetems.
> +$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
> Does this actually work? Utils.pm read the environment variable at compile
> time in the BEGIN block so this setting won't be seen? A quick testprogram
> seems to confirm this but I might be missing something.
The correct way to get a longer timeout is "IPC::Run::timer(4 *
$PostgreSQL::Test::Utils::timeout_default);". Even if changing env worked,
that would be removing the ability for even-slower systems to set timeouts
greater than 10min.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-09-27 12:39 Masahiko Sawada <[email protected]>
parent: Noah Misch <[email protected]>
0 siblings, 2 replies; 27+ messages in thread
From: Masahiko Sawada @ 2023-09-27 12:39 UTC (permalink / raw)
To: Noah Misch <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
Sorry for the late reply.
On Sun, Sep 3, 2023 at 2:48 PM Noah Misch <[email protected]> wrote:
>
> On Wed, Jul 12, 2023 at 01:47:51PM +0200, Daniel Gustafsson wrote:
> > > On 12 Jul 2023, at 09:52, Masahiko Sawada <[email protected]> wrote:
> > > Agreed. The timeout can be set by manually setting
> > > PG_TEST_TIMEOUT_DEFAULT, but I bump it to 10 min by default. And it
> > > now require setting PG_TET_EXTRA to run it.
> >
> > +# bump the query timeout to avoid false negatives on slow test syetems.
> > typo: s/syetems/systems/
> >
> >
> > +# bump the query timeout to avoid false negatives on slow test syetems.
> > +$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
> > Does this actually work? Utils.pm read the environment variable at compile
> > time in the BEGIN block so this setting won't be seen? A quick testprogram
> > seems to confirm this but I might be missing something.
>
> The correct way to get a longer timeout is "IPC::Run::timer(4 *
> $PostgreSQL::Test::Utils::timeout_default);". Even if changing env worked,
> that would be removing the ability for even-slower systems to set timeouts
> greater than 10min.
Agreed.
I've attached new version patches. 0001 patch adds an option to
background_psql to specify the timeout seconds, and 0002 patch is the
main regression test patch.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] v5-0001-Add-option-to-specify-timeout-seconds-to-Backgrou.patch (2.6K, ../../CAD21AoBKBVkXyEwkApSUqN98CuOWw=YQdbkeE6gGJ0zH7z-TBw@mail.gmail.com/2-v5-0001-Add-option-to-specify-timeout-seconds-to-Backgrou.patch)
download | inline diff:
From 10240ff94fa62383eeedb8fc395254f980c0e8f0 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Wed, 30 Aug 2023 23:10:59 +0900
Subject: [PATCH v5 1/2] Add option to specify timeout seconds to
BackgroundPsql.pm.
Author: Masahiko Sawada
Reviewed-by:
Discussion: https://postgr.es/m/
---
src/test/perl/PostgreSQL/Test/BackgroundPsql.pm | 10 ++++++----
src/test/perl/PostgreSQL/Test/Cluster.pm | 4 +++-
2 files changed, 9 insertions(+), 5 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm b/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
index 924b57ab21..725c3555f9 100644
--- a/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
+++ b/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
@@ -68,7 +68,7 @@ use Test::More;
=over
-=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params)
+=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params, timeout)
Builds a new object of class C<PostgreSQL::Test::BackgroundPsql> for either
an interactive or background session and starts it. If C<interactive> is
@@ -81,7 +81,7 @@ string. For C<interactive> sessions, IO::Pty is required.
sub new
{
my $class = shift;
- my ($interactive, $psql_params) = @_;
+ my ($interactive, $psql_params, $timeout) = @_;
my $psql = {
'stdin' => '',
'stdout' => '',
@@ -96,8 +96,10 @@ sub new
"Forbidden caller of constructor: package: $package, file: $file:$line"
unless $package->isa('PostgreSQL::Test::Cluster');
- $psql->{timeout} =
- IPC::Run::timeout($PostgreSQL::Test::Utils::timeout_default);
+ $psql->{timeout} = IPC::Run::timeout(
+ defined($timeout)
+ ? $timeout
+ : $PostgreSQL::Test::Utils::timeout_default);
if ($interactive)
{
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index 2a478ba6ed..c34c3b8627 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -2054,6 +2054,7 @@ sub background_psql
local %ENV = $self->_get_env();
my $replication = $params{replication};
+ my $timeout = undef;
my @psql_params = (
$self->installed_command('psql'),
@@ -2065,12 +2066,13 @@ sub background_psql
'-');
$params{on_error_stop} = 1 unless defined $params{on_error_stop};
+ $timeout = $params{timeout} if defined $params{timeout};
push @psql_params, '-v', 'ON_ERROR_STOP=1' if $params{on_error_stop};
push @psql_params, @{ $params{extra_params} }
if defined $params{extra_params};
- return PostgreSQL::Test::BackgroundPsql->new(0, \@psql_params);
+ return PostgreSQL::Test::BackgroundPsql->new(0, \@psql_params, $timeout);
}
=pod
--
2.31.1
[application/octet-stream] v5-0002-Add-tests-for-XID-wraparound.patch (23.8K, ../../CAD21AoBKBVkXyEwkApSUqN98CuOWw=YQdbkeE6gGJ0zH7z-TBw@mail.gmail.com/3-v5-0002-Add-tests-for-XID-wraparound.patch)
download | inline diff:
From ffbe0c42ffcf90b288e9fabffc3a6599381bb608 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Fri, 3 Mar 2023 12:01:28 +0200
Subject: [PATCH v5 2/2] Add tests for XID wraparound.
The test module includes helper functions to quickly burn through lots
of XIDs. They are used in the tests, and are also handy for manually
testing XID wraparound.
Since these tests are very expensive the entire suite is disabled by
default. It requires to set PG_TET_EXTRA to run it.
Author: Heikki Linnakangas, Masahiko Sawada, Andres Freund
Discussion: https://www.postgresql.org/message-id/CAD21AoDVhkXp8HjpFO-gp3TgL6tCKcZQNxn04m01VAtcSi-5sA%40mail.gmail.com
---
doc/src/sgml/regress.sgml | 10 +
src/test/modules/Makefile | 1 +
src/test/modules/meson.build | 1 +
src/test/modules/xid_wraparound/.gitignore | 4 +
src/test/modules/xid_wraparound/Makefile | 23 ++
src/test/modules/xid_wraparound/README | 3 +
src/test/modules/xid_wraparound/meson.build | 36 +++
.../xid_wraparound/t/001_emergency_vacuum.pl | 133 +++++++++++
.../modules/xid_wraparound/t/002_limits.pl | 138 +++++++++++
.../xid_wraparound/t/003_wraparounds.pl | 60 +++++
.../xid_wraparound/xid_wraparound--1.0.sql | 12 +
.../modules/xid_wraparound/xid_wraparound.c | 221 ++++++++++++++++++
.../xid_wraparound/xid_wraparound.control | 4 +
13 files changed, 646 insertions(+)
create mode 100644 src/test/modules/xid_wraparound/.gitignore
create mode 100644 src/test/modules/xid_wraparound/Makefile
create mode 100644 src/test/modules/xid_wraparound/README
create mode 100644 src/test/modules/xid_wraparound/meson.build
create mode 100644 src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
create mode 100644 src/test/modules/xid_wraparound/t/002_limits.pl
create mode 100644 src/test/modules/xid_wraparound/t/003_wraparounds.pl
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.c
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.control
diff --git a/doc/src/sgml/regress.sgml b/doc/src/sgml/regress.sgml
index 675db86e4d..9aeef9ec1e 100644
--- a/doc/src/sgml/regress.sgml
+++ b/doc/src/sgml/regress.sgml
@@ -313,6 +313,16 @@ make check-world PG_TEST_EXTRA='kerberos ldap ssl load_balance'
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>xid_wraparound</literal></term>
+ <listitem>
+ <para>
+ Runs the test suite under <filename>src/test/module/xid_wrapround</filename>.
+ Not enabled by default it is resource intensive.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
Tests for features that are not supported by the current build
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index 6331c976dc..3c6eb33b38 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -14,6 +14,7 @@ SUBDIRS = \
plsample \
snapshot_too_old \
spgist_name_ops \
+ xid_wraparound \
test_bloomfilter \
test_copy_callbacks \
test_custom_rmgrs \
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index 17d369e378..cb63901e32 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -11,6 +11,7 @@ subdir('plsample')
subdir('snapshot_too_old')
subdir('spgist_name_ops')
subdir('ssl_passphrase_callback')
+subdir('xid_wraparound')
subdir('test_bloomfilter')
subdir('test_copy_callbacks')
subdir('test_custom_rmgrs')
diff --git a/src/test/modules/xid_wraparound/.gitignore b/src/test/modules/xid_wraparound/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/src/test/modules/xid_wraparound/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/xid_wraparound/Makefile b/src/test/modules/xid_wraparound/Makefile
new file mode 100644
index 0000000000..7a6e0f6676
--- /dev/null
+++ b/src/test/modules/xid_wraparound/Makefile
@@ -0,0 +1,23 @@
+# src/test/modules/xid_wraparound/Makefile
+
+MODULE_big = xid_wraparound
+OBJS = \
+ $(WIN32RES) \
+ xid_wraparound.o
+PGFILEDESC = "xid_wraparound - tests for XID wraparound"
+
+EXTENSION = xid_wraparound
+DATA = xid_wraparound--1.0.sql
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/xid_wraparound
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/xid_wraparound/README b/src/test/modules/xid_wraparound/README
new file mode 100644
index 0000000000..3aab464dec
--- /dev/null
+++ b/src/test/modules/xid_wraparound/README
@@ -0,0 +1,3 @@
+This module contains tests for XID wraparound. The tests use two
+helper functions to quickly consume lots of XIDs, to reach XID
+wraparound faster.
diff --git a/src/test/modules/xid_wraparound/meson.build b/src/test/modules/xid_wraparound/meson.build
new file mode 100644
index 0000000000..42f933525e
--- /dev/null
+++ b/src/test/modules/xid_wraparound/meson.build
@@ -0,0 +1,36 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+xid_wraparound_sources = files(
+ 'xid_wraparound.c',
+)
+
+if host_system == 'windows'
+ xid_wraparound_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'xid_wraparound',
+ '--FILEDESC', 'xid_wraparound - tests for XID wraparound',])
+endif
+
+xid_wraparound = shared_module('xid_wraparound',
+ xid_wraparound_sources,
+ kwargs: pg_mod_args,
+)
+testprep_targets += xid_wraparound
+
+install_data(
+ 'xid_wraparound.control',
+ 'xid_wraparound--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'xid_wraparound',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'tap': {
+ 'tests': [
+ 't/001_emergency_vacuum.pl',
+ 't/002_limits.pl',
+ 't/003_wraparounds.pl',
+ ],
+ },
+}
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
new file mode 100644
index 0000000000..dd75faaa91
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -0,0 +1,133 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+# Test wraparound emergency autovacuum.
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('main');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create tables for a few different test scenarios
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE large(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large(data) SELECT generate_series(1,30000);
+
+CREATE TABLE large_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large_trunc(data) SELECT generate_series(1,30000);
+
+CREATE TABLE small(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small(data) SELECT generate_series(1,15000);
+
+CREATE TABLE small_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small_trunc(data) SELECT generate_series(1,15000);
+
+CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (autovacuum_enabled=false);
+INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $background_psql = $node->background_psql(
+ 'postgres',
+ on_error_stop => 0,
+ timeout => $psql_timeout_secs);
+$background_psql->set_query_timer_restart();
+$background_psql->query_safe(
+ q{
+ BEGIN;
+ DELETE FROM large WHERE id % 2 = 0;
+ DELETE FROM large_trunc WHERE id > 10000;
+ DELETE FROM small WHERE id % 2 = 0;
+ DELETE FROM small_trunc WHERE id > 1000;
+ DELETE FROM autovacuum_disabled WHERE id % 2 = 0;
+});
+
+# Consume 2 billion XIDs, to get us very close to wraparound
+$node->safe_psql('postgres',
+ qq[SELECT consume_xids_until('2000000000'::bigint)]);
+
+# Make sure the latest completed XID is advanced
+$node->safe_psql('postgres', qq[INSERT INTO small(data) SELECT 1]);
+
+# Check that all databases became old enough to trigger failsafe.
+my $ret = $node->safe_psql(
+ 'postgres',
+ qq[
+SELECT datname,
+ age(datfrozenxid) > current_setting('vacuum_failsafe_age')::int as old
+FROM pg_database ORDER BY 1
+]);
+is( $ret, "postgres|t
+template0|t
+template1|t", "all tables became old");
+
+my $log_offset = -s $node->logfile;
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# Wait until autovacuum processed all tables and advanced the
+# system-wide oldest-XID.
+$node->poll_query_until(
+ 'postgres',
+ qq[
+SELECT NOT EXISTS (
+ SELECT *
+ FROM pg_database
+ WHERE age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::int)
+]) or die "timeout waiting for all databases to be vacuumed";
+
+# Check if these tables are vacuumed.
+$ret = $node->safe_psql(
+ 'postgres', qq[
+SELECT relname, age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
+FROM pg_class
+WHERE relname IN ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+ORDER BY 1
+]);
+
+is( $ret, "autovacuum_disabled|f
+large|f
+large_trunc|f
+small|f
+small_trunc|f", "all tables are vacuumed");
+
+# Check if vacuum failsafe was triggered for each table.
+my $log_contents = slurp_file($node->logfile, $log_offset);
+foreach my $tablename ('large', 'large_trunc', 'small', 'small_trunc',
+ 'autovacuum_disabled')
+{
+ like(
+ $log_contents,
+ qr/bypassing nonessential maintenance of table "postgres.public.$tablename" as a failsafe after \d+ index scans/,
+ "failsafe vacuum triggered for $tablename");
+}
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/002_limits.pl b/src/test/modules/xid_wraparound/t/002_limits.pl
new file mode 100644
index 0000000000..7e99e72613
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/002_limits.pl
@@ -0,0 +1,138 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Test XID wraparound limits.
+#
+# When you get close to XID wraparound, you start to get warnings, and
+# when you get even closer, the system refuses to assign any more XIDs
+# until the oldest databases have been vacuumed and datfrozenxid has
+# been advanced.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+my $ret;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only to prevent wraparound
+autovacuum_naptime = 1s
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('start');
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $background_psql = $node->background_psql(
+ 'postgres',
+ on_error_stop => 0,
+ timeout => $psql_timeout_secs);
+$background_psql->query_safe(
+ q{
+ BEGIN;
+ INSERT INTO wraparoundtest VALUES ('oldxact');
+});
+
+# Consume 2 billion transactions, to get close to wraparound
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after 1 billion')]);
+
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after 2 billion')]);
+
+# We are now just under 150 million XIDs away from wraparound.
+# Continue consuming XIDs, in batches of 10 million, until we get
+# the warning:
+#
+# WARNING: database "postgres" must be vacuumed within 3000024 transactions
+# HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
+# You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
+my $stderr;
+my $warn_limit = 0;
+for my $i (1 .. 15)
+{
+ $node->psql(
+ 'postgres', qq[SELECT consume_xids(10000000)],
+ stderr => \$stderr,
+ on_error_die => 1);
+
+ if ($stderr =~
+ /WARNING: database "postgres" must be vacuumed within [0-9]+ transactions/
+ )
+ {
+ # Reached the warn-limit
+ $warn_limit = 1;
+ last;
+ }
+}
+ok($warn_limit == 1, "warn-limit reached");
+
+# We can still INSERT, despite the warnings.
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('reached warn-limit')]);
+
+# Keep going. We'll hit the hard "stop" limit.
+$ret = $node->psql(
+ 'postgres',
+ qq[SELECT consume_xids(100000000)],
+ stderr => \$stderr);
+like(
+ $stderr,
+ qr/ERROR: database is not accepting commands to avoid wraparound data loss/,
+ "stop-limit");
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# VACUUM, to freeze the tables and advance datfrozenxid.
+#
+# Autovacuum does this for the other databases, and would do it for
+# 'postgres' too, but let's test manual VACUUM.
+#
+$node->safe_psql('postgres', 'VACUUM');
+
+# Wait until autovacuum has processed the other databases and advanced
+# the system-wide oldest-XID.
+$ret =
+ $node->poll_query_until('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after VACUUM')],
+ 'INSERT 0 1');
+
+# Check the table contents
+$ret = $node->safe_psql('postgres', qq[SELECT * from wraparoundtest]);
+is( $ret, "start
+oldxact
+after 1 billion
+after 2 billion
+reached warn-limit
+after VACUUM");
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
new file mode 100644
index 0000000000..be71b00a17
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -0,0 +1,60 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Consume a lot of XIDs, wrapping around a few times.
+#
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('beginning');
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Burn through 10 billion transactions in total, in batches of 100 million.
+my $ret;
+for my $i (1 .. 100)
+{
+ $ret = $node->safe_psql(
+ 'postgres',
+ qq[SELECT consume_xids(100000000)],
+ timeout => $psql_timeout_secs);
+ $ret = $node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after $i batches')]);
+}
+
+$ret = $node->safe_psql('postgres', qq[SELECT COUNT(*) FROM wraparoundtest]);
+is($ret, "101");
+
+$node->stop;
+
+done_testing();
diff --git a/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
new file mode 100644
index 0000000000..f5577adfdb
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
@@ -0,0 +1,12 @@
+/* src/test/modules/xid_wraparound/xid_wraparound--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION xid_wraparound" to load this file. \quit
+
+CREATE FUNCTION consume_xids(bigint)
+RETURNS bigint IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
+
+CREATE FUNCTION consume_xids_until(bigint)
+RETURNS bigint IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.c b/src/test/modules/xid_wraparound/xid_wraparound.c
new file mode 100644
index 0000000000..c9d6034b55
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.c
@@ -0,0 +1,221 @@
+/*--------------------------------------------------------------------------
+ *
+ * xid_wraparound.c
+ * Utilities for testing XID wraparound
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/test/modules/xid_wraparound/xid_wraparound.c
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/transam.h"
+#include "access/xact.h"
+#include "fmgr.h"
+#include "miscadmin.h"
+#include "storage/lwlock.h"
+#include "storage/proc.h"
+
+PG_MODULE_MAGIC;
+
+static int64 consume_xids_shortcut(void);
+static FullTransactionId consume_xids_common(FullTransactionId untilxid, uint64 nxids);
+
+/*
+ * Consume the specified number of XIDs.
+ */
+PG_FUNCTION_INFO_V1(consume_xids);
+Datum
+consume_xids(PG_FUNCTION_ARGS)
+{
+ int64 nxids = PG_GETARG_INT64(0);
+ FullTransactionId lastxid;
+
+ if (nxids < 0)
+ elog(ERROR, "invalid nxids argument: %lld", (long long) nxids);
+
+ if (nxids == 0)
+ lastxid = ReadNextFullTransactionId();
+ else
+ lastxid = consume_xids_common(InvalidFullTransactionId, (uint64) nxids);
+
+ PG_RETURN_INT64((int64) U64FromFullTransactionId(lastxid));
+}
+
+/*
+ * Consume XIDs, up to the given XID.
+ */
+PG_FUNCTION_INFO_V1(consume_xids_until);
+Datum
+consume_xids_until(PG_FUNCTION_ARGS)
+{
+ FullTransactionId targetxid = FullTransactionIdFromU64((uint64) PG_GETARG_INT64(0));
+ FullTransactionId lastxid;
+
+ if (!FullTransactionIdIsNormal(targetxid))
+ elog(ERROR, "targetxid %llu is not normal", (unsigned long long) U64FromFullTransactionId(targetxid));
+
+ lastxid = consume_xids_common(targetxid, 0);
+
+ PG_RETURN_INT64((int64) U64FromFullTransactionId(lastxid));
+}
+
+/*
+ * Common functionality between the two public functions.
+ */
+static FullTransactionId
+consume_xids_common(FullTransactionId untilxid, uint64 nxids)
+{
+ FullTransactionId lastxid;
+ uint64 last_reported_at = 0;
+ uint64 consumed = 0;
+
+ /* Print a NOTICE every REPORT_INTERVAL xids */
+#define REPORT_INTERVAL (10*1000000)
+
+ /* initialize 'lastxid' with the system's current next XID */
+ lastxid = ReadNextFullTransactionId();
+
+ /*
+ * We consume XIDs by calling GetNewTransactionId(true), which marks the
+ * consumed XIDs as subtransactions of the current top-level transaction.
+ * For that to work, this transaction must have a top-level XID.
+ *
+ * GetNewTransactionId registers them in the subxid cache in PGPROC, until
+ * the cache overflows, but beyond that, we don't keep track of the
+ * consumed XIDs.
+ */
+ (void) GetTopTransactionId();
+
+ for (;;)
+ {
+ uint64 xids_left;
+
+ CHECK_FOR_INTERRUPTS();
+
+ /* How many XIDs do we have left to consume? */
+ if (nxids > 0)
+ {
+ if (consumed >= nxids)
+ break;
+ xids_left = nxids - consumed;
+ }
+ else
+ {
+ if (FullTransactionIdFollowsOrEquals(lastxid, untilxid))
+ break;
+ xids_left = U64FromFullTransactionId(untilxid) - U64FromFullTransactionId(lastxid);
+ }
+
+ /*
+ * If we still have plenty of XIDs to consume, try to take a shortcut
+ * and bump up the nextXid counter directly.
+ */
+ if (xids_left > 2000 &&
+ consumed - last_reported_at < REPORT_INTERVAL &&
+ MyProc->subxidStatus.overflowed)
+ {
+ int64 consumed_by_shortcut = consume_xids_shortcut();
+
+ if (consumed_by_shortcut > 0)
+ {
+ consumed += consumed_by_shortcut;
+ continue;
+ }
+ }
+
+ /* Slow path: Call GetNewTransactionId to allocate a new XID. */
+ lastxid = GetNewTransactionId(true);
+ consumed++;
+
+ /* Report progress */
+ if (consumed - last_reported_at >= REPORT_INTERVAL)
+ {
+ if (nxids > 0)
+ elog(NOTICE, "consumed %llu / %llu XIDs, latest %u:%u",
+ (unsigned long long) consumed, (unsigned long long) nxids,
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid));
+ else
+ elog(NOTICE, "consumed up to %u:%u / %u:%u",
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid),
+ EpochFromFullTransactionId(untilxid),
+ XidFromFullTransactionId(untilxid));
+ last_reported_at = consumed;
+ }
+ }
+
+ return lastxid;
+}
+
+
+/*
+ * These constants copied from .c files, because they're private.
+ */
+#define COMMIT_TS_XACTS_PER_PAGE (BLCKSZ / 10)
+#define SUBTRANS_XACTS_PER_PAGE (BLCKSZ / sizeof(TransactionId))
+#define CLOG_XACTS_PER_BYTE 4
+#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
+
+/*
+ * All the interesting action in GetNewTransactionId happens when we extend
+ * the SLRUs, or at the uint32 wraparound. If the nextXid counter is not close
+ * to any of those interesting values, take a shortcut and bump nextXID
+ * directly, close to the next "interesting" value.
+ */
+static inline uint32
+XidSkip(FullTransactionId fullxid)
+{
+ uint32 low = XidFromFullTransactionId(fullxid);
+ uint32 rem;
+ uint32 distance;
+
+ if (low < 5 || low >= UINT32_MAX - 5)
+ return 0;
+ distance = UINT32_MAX - 5 - low;
+
+ rem = low % COMMIT_TS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, COMMIT_TS_XACTS_PER_PAGE - rem);
+
+ rem = low % SUBTRANS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, SUBTRANS_XACTS_PER_PAGE - rem);
+
+ rem = low % CLOG_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, CLOG_XACTS_PER_PAGE - rem);
+
+ return distance;
+}
+
+static int64
+consume_xids_shortcut(void)
+{
+ FullTransactionId nextXid;
+ uint32 consumed;
+
+ LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
+ nextXid = ShmemVariableCache->nextXid;
+
+ /*
+ * Go slow near the "interesting values". The interesting zones include 5
+ * transactions before and after SLRU page switches.
+ */
+ consumed = XidSkip(nextXid);
+ if (consumed > 0)
+ ShmemVariableCache->nextXid.value += (uint64) consumed;
+
+ LWLockRelease(XidGenLock);
+
+ return consumed;
+}
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.control b/src/test/modules/xid_wraparound/xid_wraparound.control
new file mode 100644
index 0000000000..6c6964ed3d
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.control
@@ -0,0 +1,4 @@
+comment = 'Tests for XID wraparound'
+default_version = '1.0'
+module_pathname = '$libdir/xid_wraparound'
+relocatable = true
--
2.31.1
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-09-29 10:17 Daniel Gustafsson <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 2 replies; 27+ messages in thread
From: Daniel Gustafsson @ 2023-09-29 10:17 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
> On 27 Sep 2023, at 14:39, Masahiko Sawada <[email protected]> wrote:
> I've attached new version patches. 0001 patch adds an option to
> background_psql to specify the timeout seconds, and 0002 patch is the
> main regression test patch.
-=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params)
+=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params, timeout)
Looking at this I notice that I made a typo in 664d757531e, the =item line
should have "@psql_params" and not "@params". Perhaps you can fix that minor
thing while in there?
+ $timeout = $params{timeout} if defined $params{timeout};
I think this should be documented in the background_psql POD docs.
+ Not enabled by default it is resource intensive.
This sentence is missing a "because", should read: "..by default *because* it
is.."
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
Should we bump the timeout like this for all systems? I interpreted Noah's
comment such that it should be possible for slower systems to override, not
that it should be extended everywhere, but I might have missed something.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-09-29 10:19 vignesh C <[email protected]>
parent: Masahiko Sawada <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: vignesh C @ 2023-09-29 10:19 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Noah Misch <[email protected]>; Daniel Gustafsson <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Thu, 28 Sept 2023 at 03:55, Masahiko Sawada <[email protected]> wrote:
>
> Sorry for the late reply.
>
> On Sun, Sep 3, 2023 at 2:48 PM Noah Misch <[email protected]> wrote:
> >
> > On Wed, Jul 12, 2023 at 01:47:51PM +0200, Daniel Gustafsson wrote:
> > > > On 12 Jul 2023, at 09:52, Masahiko Sawada <[email protected]> wrote:
> > > > Agreed. The timeout can be set by manually setting
> > > > PG_TEST_TIMEOUT_DEFAULT, but I bump it to 10 min by default. And it
> > > > now require setting PG_TET_EXTRA to run it.
> > >
> > > +# bump the query timeout to avoid false negatives on slow test syetems.
> > > typo: s/syetems/systems/
> > >
> > >
> > > +# bump the query timeout to avoid false negatives on slow test syetems.
> > > +$ENV{PG_TEST_TIMEOUT_DEFAULT} = 600;
> > > Does this actually work? Utils.pm read the environment variable at compile
> > > time in the BEGIN block so this setting won't be seen? A quick testprogram
> > > seems to confirm this but I might be missing something.
> >
> > The correct way to get a longer timeout is "IPC::Run::timer(4 *
> > $PostgreSQL::Test::Utils::timeout_default);". Even if changing env worked,
> > that would be removing the ability for even-slower systems to set timeouts
> > greater than 10min.
>
> Agreed.
>
> I've attached new version patches. 0001 patch adds an option to
> background_psql to specify the timeout seconds, and 0002 patch is the
> main regression test patch.
Few comments:
1) Should we have some validation for the inputs given:
+PG_FUNCTION_INFO_V1(consume_xids_until);
+Datum
+consume_xids_until(PG_FUNCTION_ARGS)
+{
+ FullTransactionId targetxid =
FullTransactionIdFromU64((uint64) PG_GETARG_INT64(0));
+ FullTransactionId lastxid;
+
+ if (!FullTransactionIdIsNormal(targetxid))
+ elog(ERROR, "targetxid %llu is not normal", (unsigned
long long) U64FromFullTransactionId(targetxid));
If not it will take inputs like -1 and 999999999999999.
Also the notice messages might confuse for the above values, as it
will show a different untilxid value like the below:
postgres=# SELECT consume_xids_until(999999999999999);
NOTICE: consumed up to 0:10000809 / 232830:2764472319
2) Should this be added after worker_spi as we generally add it in the
alphabetical order:
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index fcd643f6f1..4054bde84c 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -10,6 +10,7 @@ subdir('libpq_pipeline')
subdir('plsample')
subdir('spgist_name_ops')
subdir('ssl_passphrase_callback')
+subdir('xid_wraparound')
subdir('test_bloomfilter')
3) Similarly here too:
index e81873cb5a..a4c845ab4a 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -13,6 +13,7 @@ SUBDIRS = \
libpq_pipeline \
plsample \
spgist_name_ops \
+ xid_wraparound \
test_bloomfilter \
4) The following includes are not required transam.h, fmgr.h, lwlock.h
+ * src/test/modules/xid_wraparound/xid_wraparound.c
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/transam.h"
+#include "access/xact.h"
+#include "fmgr.h"
+#include "miscadmin.h"
+#include "storage/lwlock.h"
+#include "storage/proc.h"
Regards,
Vignesh
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-09-29 13:57 Noah Misch <[email protected]>
parent: Daniel Gustafsson <[email protected]>
1 sibling, 0 replies; 27+ messages in thread
From: Noah Misch @ 2023-09-29 13:57 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Masahiko Sawada <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Fri, Sep 29, 2023 at 12:17:04PM +0200, Daniel Gustafsson wrote:
> +# Bump the query timeout to avoid false negatives on slow test systems.
> +my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
>
> Should we bump the timeout like this for all systems? I interpreted Noah's
> comment such that it should be possible for slower systems to override, not
> that it should be extended everywhere, but I might have missed something.
This is the conventional way to do it. For an operation far slower than a
typical timeout_default situation, the patch can and should dilate the default
timeout like this. The patch version as of my last comment was extending the
timeout but also blocking users from extending it further via
PG_TEST_TIMEOUT_DEFAULT. The latest version restores PG_TEST_TIMEOUT_DEFAULT
reactivity, resolving my comment.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-11-27 13:06 Masahiko Sawada <[email protected]>
parent: Daniel Gustafsson <[email protected]>
1 sibling, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2023-11-27 13:06 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Fri, Sep 29, 2023 at 7:17 PM Daniel Gustafsson <[email protected]> wrote:
>
> > On 27 Sep 2023, at 14:39, Masahiko Sawada <[email protected]> wrote:
>
> > I've attached new version patches. 0001 patch adds an option to
> > background_psql to specify the timeout seconds, and 0002 patch is the
> > main regression test patch.
>
> -=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params)
> +=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params, timeout)
>
> Looking at this I notice that I made a typo in 664d757531e, the =item line
> should have "@psql_params" and not "@params". Perhaps you can fix that minor
> thing while in there?
>
>
> + $timeout = $params{timeout} if defined $params{timeout};
>
> I think this should be documented in the background_psql POD docs.
While updating the documentation, I found the following description:
=item $node->background_psql($dbname, %params) =>
PostgreSQL::Test::BackgroundPsql inst$
Invoke B<psql> on B<$dbname> and return a BackgroundPsql object.
A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
which can be modified later.
Is it true that we can modify the timeout after creating
BackgroundPsql object? If so, it seems we don't need to introduce the
new timeout argument. But how?
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-11-27 13:40 Daniel Gustafsson <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Daniel Gustafsson @ 2023-11-27 13:40 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
> On 27 Nov 2023, at 14:06, Masahiko Sawada <[email protected]> wrote:
> Is it true that we can modify the timeout after creating
> BackgroundPsql object? If so, it seems we don't need to introduce the
> new timeout argument. But how?
I can't remember if that's leftovers that incorrectly remains from an earlier
version of the BackgroundPsql work, or if it's a very bad explanation of
->set_query_timer_restart(). The timeout will use the timeout_default value
and that cannot be overridden, it can only be reset per query.
With your patch the timeout still cannot be changed, but at least set during
start which seems good enough until there are tests warranting more complexity.
The docs should be corrected to reflect this in your patch.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-11-28 02:00 Masahiko Sawada <[email protected]>
parent: Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2023-11-28 02:00 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Mon, Nov 27, 2023 at 10:40 PM Daniel Gustafsson <[email protected]> wrote:
>
> > On 27 Nov 2023, at 14:06, Masahiko Sawada <[email protected]> wrote:
>
> > Is it true that we can modify the timeout after creating
> > BackgroundPsql object? If so, it seems we don't need to introduce the
> > new timeout argument. But how?
>
> I can't remember if that's leftovers that incorrectly remains from an earlier
> version of the BackgroundPsql work, or if it's a very bad explanation of
> ->set_query_timer_restart(). The timeout will use the timeout_default value
> and that cannot be overridden, it can only be reset per query.
Thank you for confirming this. I see there is the same problem also in
interactive_psql(). So I've attached the 0001 patch to fix these
documentation issues. Which could be backpatched.
> With your patch the timeout still cannot be changed, but at least set during
> start which seems good enough until there are tests warranting more complexity.
> The docs should be corrected to reflect this in your patch.
I've incorporated the comments except for the following one and
attached updated version of the rest patches:
On Fri, Sep 29, 2023 at 7:20 PM vignesh C <[email protected]> wrote:
> Few comments:
> 1) Should we have some validation for the inputs given:
> +PG_FUNCTION_INFO_V1(consume_xids_until);
> +Datum
> +consume_xids_until(PG_FUNCTION_ARGS)
> +{
> + FullTransactionId targetxid =
> FullTransactionIdFromU64((uint64) PG_GETARG_INT64(0));
> + FullTransactionId lastxid;
> +
> + if (!FullTransactionIdIsNormal(targetxid))
> + elog(ERROR, "targetxid %llu is not normal", (unsigned
> long long) U64FromFullTransactionId(targetxid));
>
> If not it will take inputs like -1 and 999999999999999.
> Also the notice messages might confuse for the above values, as it
> will show a different untilxid value like the below:
> postgres=# SELECT consume_xids_until(999999999999999);
> NOTICE: consumed up to 0:10000809 / 232830:2764472319
The full transaction ids shown in the notice messages are separated
into epoch and xid so it's not a different value. This epoch-and-xid
style is used also in pg_controldata output and makes sense to me to
show the progress of xid consumption.
Once the new test gets committed, I'll prepare a new buildfarm animal
for that if possible.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] v6-0001-fix-wrong-description-of-BackgroundPsql-s-timeout.patch (1.5K, ../../CAD21AoBUgppMyu5ExLWOfZaBOySsv+v3LF7Mu836Bo=ACqHKJQ@mail.gmail.com/2-v6-0001-fix-wrong-description-of-BackgroundPsql-s-timeout.patch)
download | inline diff:
From ad61cb7fdfd0cb484314d7fd3db844669a583da6 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Tue, 28 Nov 2023 10:34:48 +0900
Subject: [PATCH v6 1/3] fix wrong description of BackgroundPsql's timeout.
---
src/test/perl/PostgreSQL/Test/Cluster.pm | 6 ++----
1 file changed, 2 insertions(+), 4 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index c3d46c7c70..4b7baa908f 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -2028,8 +2028,7 @@ sub psql
Invoke B<psql> on B<$dbname> and return a BackgroundPsql object.
-A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
-which can be modified later.
+A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
psql is invoked in tuples-only unaligned mode with reading of B<.psqlrc>
disabled. That may be overridden by passing extra psql parameters.
@@ -2095,8 +2094,7 @@ sub background_psql
Invoke B<psql> on B<$dbname> and return a BackgroundPsql object, which the
caller may use to send interactive input to B<psql>.
-A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
-which can be modified later.
+A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
psql is invoked in tuples-only unaligned mode with reading of B<.psqlrc>
disabled. That may be overridden by passing extra psql parameters.
--
2.31.1
[application/octet-stream] v6-0003-Add-tests-for-XID-wraparound.patch (23.7K, ../../CAD21AoBUgppMyu5ExLWOfZaBOySsv+v3LF7Mu836Bo=ACqHKJQ@mail.gmail.com/3-v6-0003-Add-tests-for-XID-wraparound.patch)
download | inline diff:
From aff49fdf5715c28f593d3b7c63d7332e7f3e840a Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Fri, 3 Mar 2023 12:01:28 +0200
Subject: [PATCH v6 3/3] Add tests for XID wraparound.
The test module includes helper functions to quickly burn through lots
of XIDs. They are used in the tests, and are also handy for manually
testing XID wraparound.
Since these tests are very expensive the entire suite is disabled by
default. It requires to set PG_TET_EXTRA to run it.
Reviewed-by: Daniel Gustafsson, John Naylor, Michael Paquier,
Reviewed-by: vignesh C
Author: Heikki Linnakangas, Masahiko Sawada, Andres Freund
Discussion: https://www.postgresql.org/message-id/CAD21AoDVhkXp8HjpFO-gp3TgL6tCKcZQNxn04m01VAtcSi-5sA%40mail.gmail.com
---
doc/src/sgml/regress.sgml | 10 +
src/test/modules/Makefile | 3 +-
src/test/modules/meson.build | 1 +
src/test/modules/xid_wraparound/.gitignore | 4 +
src/test/modules/xid_wraparound/Makefile | 23 ++
src/test/modules/xid_wraparound/README | 3 +
src/test/modules/xid_wraparound/meson.build | 36 +++
.../xid_wraparound/t/001_emergency_vacuum.pl | 133 +++++++++++
.../modules/xid_wraparound/t/002_limits.pl | 138 +++++++++++
.../xid_wraparound/t/003_wraparounds.pl | 60 +++++
.../xid_wraparound/xid_wraparound--1.0.sql | 12 +
.../modules/xid_wraparound/xid_wraparound.c | 219 ++++++++++++++++++
.../xid_wraparound/xid_wraparound.control | 4 +
13 files changed, 645 insertions(+), 1 deletion(-)
create mode 100644 src/test/modules/xid_wraparound/.gitignore
create mode 100644 src/test/modules/xid_wraparound/Makefile
create mode 100644 src/test/modules/xid_wraparound/README
create mode 100644 src/test/modules/xid_wraparound/meson.build
create mode 100644 src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
create mode 100644 src/test/modules/xid_wraparound/t/002_limits.pl
create mode 100644 src/test/modules/xid_wraparound/t/003_wraparounds.pl
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.c
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.control
diff --git a/doc/src/sgml/regress.sgml b/doc/src/sgml/regress.sgml
index 69f627d7f4..70d9bdefe1 100644
--- a/doc/src/sgml/regress.sgml
+++ b/doc/src/sgml/regress.sgml
@@ -314,6 +314,16 @@ make check-world PG_TEST_EXTRA='kerberos ldap ssl load_balance'
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>xid_wraparound</literal></term>
+ <listitem>
+ <para>
+ Runs the test suite under <filename>src/test/module/xid_wrapround</filename>.
+ Not enabled by default because it is resource intensive.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
Tests for features that are not supported by the current build
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index a18e4d28a0..5d33fa6a9a 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -34,7 +34,8 @@ SUBDIRS = \
test_shm_mq \
test_slru \
unsafe_tests \
- worker_spi
+ worker_spi \
+ xid_wraparound
ifeq ($(with_ssl),openssl)
SUBDIRS += ssl_passphrase_callback
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index 4e83c0f8d7..b76f588559 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -32,3 +32,4 @@ subdir('test_shm_mq')
subdir('test_slru')
subdir('unsafe_tests')
subdir('worker_spi')
+subdir('xid_wraparound')
diff --git a/src/test/modules/xid_wraparound/.gitignore b/src/test/modules/xid_wraparound/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/src/test/modules/xid_wraparound/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/xid_wraparound/Makefile b/src/test/modules/xid_wraparound/Makefile
new file mode 100644
index 0000000000..7a6e0f6676
--- /dev/null
+++ b/src/test/modules/xid_wraparound/Makefile
@@ -0,0 +1,23 @@
+# src/test/modules/xid_wraparound/Makefile
+
+MODULE_big = xid_wraparound
+OBJS = \
+ $(WIN32RES) \
+ xid_wraparound.o
+PGFILEDESC = "xid_wraparound - tests for XID wraparound"
+
+EXTENSION = xid_wraparound
+DATA = xid_wraparound--1.0.sql
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/xid_wraparound
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/xid_wraparound/README b/src/test/modules/xid_wraparound/README
new file mode 100644
index 0000000000..3aab464dec
--- /dev/null
+++ b/src/test/modules/xid_wraparound/README
@@ -0,0 +1,3 @@
+This module contains tests for XID wraparound. The tests use two
+helper functions to quickly consume lots of XIDs, to reach XID
+wraparound faster.
diff --git a/src/test/modules/xid_wraparound/meson.build b/src/test/modules/xid_wraparound/meson.build
new file mode 100644
index 0000000000..42f933525e
--- /dev/null
+++ b/src/test/modules/xid_wraparound/meson.build
@@ -0,0 +1,36 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+xid_wraparound_sources = files(
+ 'xid_wraparound.c',
+)
+
+if host_system == 'windows'
+ xid_wraparound_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'xid_wraparound',
+ '--FILEDESC', 'xid_wraparound - tests for XID wraparound',])
+endif
+
+xid_wraparound = shared_module('xid_wraparound',
+ xid_wraparound_sources,
+ kwargs: pg_mod_args,
+)
+testprep_targets += xid_wraparound
+
+install_data(
+ 'xid_wraparound.control',
+ 'xid_wraparound--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'xid_wraparound',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'tap': {
+ 'tests': [
+ 't/001_emergency_vacuum.pl',
+ 't/002_limits.pl',
+ 't/003_wraparounds.pl',
+ ],
+ },
+}
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
new file mode 100644
index 0000000000..2ae1667d58
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -0,0 +1,133 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+# Test wraparound emergency autovacuum.
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('main');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create tables for a few different test scenarios
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE large(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large(data) SELECT generate_series(1,30000);
+
+CREATE TABLE large_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large_trunc(data) SELECT generate_series(1,30000);
+
+CREATE TABLE small(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small(data) SELECT generate_series(1,15000);
+
+CREATE TABLE small_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small_trunc(data) SELECT generate_series(1,15000);
+
+CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (autovacuum_enabled=false);
+INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $background_psql = $node->background_psql(
+ 'postgres',
+ on_error_stop => 0,
+ timeout => $psql_timeout_secs);
+$background_psql->set_query_timer_restart();
+$background_psql->query_safe(
+ q{
+ BEGIN;
+ DELETE FROM large WHERE id % 2 = 0;
+ DELETE FROM large_trunc WHERE id > 10000;
+ DELETE FROM small WHERE id % 2 = 0;
+ DELETE FROM small_trunc WHERE id > 1000;
+ DELETE FROM autovacuum_disabled WHERE id % 2 = 0;
+});
+
+# Consume 2 billion XIDs, to get us very close to wraparound
+$node->safe_psql('postgres',
+ qq[SELECT consume_xids_until('2000000000'::xid8)]);
+
+# Make sure the latest completed XID is advanced
+$node->safe_psql('postgres', qq[INSERT INTO small(data) SELECT 1]);
+
+# Check that all databases became old enough to trigger failsafe.
+my $ret = $node->safe_psql(
+ 'postgres',
+ qq[
+SELECT datname,
+ age(datfrozenxid) > current_setting('vacuum_failsafe_age')::int as old
+FROM pg_database ORDER BY 1
+]);
+is( $ret, "postgres|t
+template0|t
+template1|t", "all tables became old");
+
+my $log_offset = -s $node->logfile;
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# Wait until autovacuum processed all tables and advanced the
+# system-wide oldest-XID.
+$node->poll_query_until(
+ 'postgres',
+ qq[
+SELECT NOT EXISTS (
+ SELECT *
+ FROM pg_database
+ WHERE age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::int)
+]) or die "timeout waiting for all databases to be vacuumed";
+
+# Check if these tables are vacuumed.
+$ret = $node->safe_psql(
+ 'postgres', qq[
+SELECT relname, age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
+FROM pg_class
+WHERE relname IN ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+ORDER BY 1
+]);
+
+is( $ret, "autovacuum_disabled|f
+large|f
+large_trunc|f
+small|f
+small_trunc|f", "all tables are vacuumed");
+
+# Check if vacuum failsafe was triggered for each table.
+my $log_contents = slurp_file($node->logfile, $log_offset);
+foreach my $tablename ('large', 'large_trunc', 'small', 'small_trunc',
+ 'autovacuum_disabled')
+{
+ like(
+ $log_contents,
+ qr/bypassing nonessential maintenance of table "postgres.public.$tablename" as a failsafe after \d+ index scans/,
+ "failsafe vacuum triggered for $tablename");
+}
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/002_limits.pl b/src/test/modules/xid_wraparound/t/002_limits.pl
new file mode 100644
index 0000000000..b35352f25b
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/002_limits.pl
@@ -0,0 +1,138 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Test XID wraparound limits.
+#
+# When you get close to XID wraparound, you start to get warnings, and
+# when you get even closer, the system refuses to assign any more XIDs
+# until the oldest databases have been vacuumed and datfrozenxid has
+# been advanced.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+my $ret;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only to prevent wraparound
+autovacuum_naptime = 1s
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('start');
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $background_psql = $node->background_psql(
+ 'postgres',
+ on_error_stop => 0,
+ timeout => $psql_timeout_secs);
+$background_psql->query_safe(
+ q{
+ BEGIN;
+ INSERT INTO wraparoundtest VALUES ('oldxact');
+});
+
+# Consume 2 billion transactions, to get close to wraparound
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after 1 billion')]);
+
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after 2 billion')]);
+
+# We are now just under 150 million XIDs away from wraparound.
+# Continue consuming XIDs, in batches of 10 million, until we get
+# the warning:
+#
+# WARNING: database "postgres" must be vacuumed within 3000024 transactions
+# HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
+# You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
+my $stderr;
+my $warn_limit = 0;
+for my $i (1 .. 15)
+{
+ $node->psql(
+ 'postgres', qq[SELECT consume_xids(10000000)],
+ stderr => \$stderr,
+ on_error_die => 1);
+
+ if ($stderr =~
+ /WARNING: database "postgres" must be vacuumed within [0-9]+ transactions/
+ )
+ {
+ # Reached the warn-limit
+ $warn_limit = 1;
+ last;
+ }
+}
+ok($warn_limit == 1, "warn-limit reached");
+
+# We can still INSERT, despite the warnings.
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('reached warn-limit')]);
+
+# Keep going. We'll hit the hard "stop" limit.
+$ret = $node->psql(
+ 'postgres',
+ qq[SELECT consume_xids(100000000)],
+ stderr => \$stderr);
+like(
+ $stderr,
+ qr/ERROR: database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "postgres"/,
+ "stop-limit");
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# VACUUM, to freeze the tables and advance datfrozenxid.
+#
+# Autovacuum does this for the other databases, and would do it for
+# 'postgres' too, but let's test manual VACUUM.
+#
+$node->safe_psql('postgres', 'VACUUM');
+
+# Wait until autovacuum has processed the other databases and advanced
+# the system-wide oldest-XID.
+$ret =
+ $node->poll_query_until('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after VACUUM')],
+ 'INSERT 0 1');
+
+# Check the table contents
+$ret = $node->safe_psql('postgres', qq[SELECT * from wraparoundtest]);
+is( $ret, "start
+oldxact
+after 1 billion
+after 2 billion
+reached warn-limit
+after VACUUM");
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
new file mode 100644
index 0000000000..be71b00a17
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -0,0 +1,60 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Consume a lot of XIDs, wrapping around a few times.
+#
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('beginning');
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Burn through 10 billion transactions in total, in batches of 100 million.
+my $ret;
+for my $i (1 .. 100)
+{
+ $ret = $node->safe_psql(
+ 'postgres',
+ qq[SELECT consume_xids(100000000)],
+ timeout => $psql_timeout_secs);
+ $ret = $node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after $i batches')]);
+}
+
+$ret = $node->safe_psql('postgres', qq[SELECT COUNT(*) FROM wraparoundtest]);
+is($ret, "101");
+
+$node->stop;
+
+done_testing();
diff --git a/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
new file mode 100644
index 0000000000..51d25fc4c6
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
@@ -0,0 +1,12 @@
+/* src/test/modules/xid_wraparound/xid_wraparound--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION xid_wraparound" to load this file. \quit
+
+CREATE FUNCTION consume_xids(nxids bigint)
+RETURNS xid8 IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
+
+CREATE FUNCTION consume_xids_until(targetxid xid8)
+RETURNS xid8 IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.c b/src/test/modules/xid_wraparound/xid_wraparound.c
new file mode 100644
index 0000000000..312eebbbc8
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.c
@@ -0,0 +1,219 @@
+/*--------------------------------------------------------------------------
+ *
+ * xid_wraparound.c
+ * Utilities for testing XID wraparound
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/test/modules/xid_wraparound/xid_wraparound.c
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/xact.h"
+#include "miscadmin.h"
+#include "storage/proc.h"
+#include "utils/xid8.h"
+
+PG_MODULE_MAGIC;
+
+static int64 consume_xids_shortcut(void);
+static FullTransactionId consume_xids_common(FullTransactionId untilxid, uint64 nxids);
+
+/*
+ * Consume the specified number of XIDs.
+ */
+PG_FUNCTION_INFO_V1(consume_xids);
+Datum
+consume_xids(PG_FUNCTION_ARGS)
+{
+ int64 nxids = PG_GETARG_INT64(0);
+ FullTransactionId lastxid;
+
+ if (nxids < 0)
+ elog(ERROR, "invalid nxids argument: %lld", (long long) nxids);
+
+ if (nxids == 0)
+ lastxid = ReadNextFullTransactionId();
+ else
+ lastxid = consume_xids_common(InvalidFullTransactionId, (uint64) nxids);
+
+ PG_RETURN_FULLTRANSACTIONID(lastxid);
+}
+
+/*
+ * Consume XIDs, up to the given XID.
+ */
+PG_FUNCTION_INFO_V1(consume_xids_until);
+Datum
+consume_xids_until(PG_FUNCTION_ARGS)
+{
+ FullTransactionId targetxid = PG_GETARG_FULLTRANSACTIONID(0);
+ FullTransactionId lastxid;
+
+ if (!FullTransactionIdIsNormal(targetxid))
+ elog(ERROR, "targetxid %llu is not normal",
+ (unsigned long long) U64FromFullTransactionId(targetxid));
+
+ lastxid = consume_xids_common(targetxid, 0);
+
+ PG_RETURN_FULLTRANSACTIONID(lastxid);
+}
+
+/*
+ * Common functionality between the two public functions.
+ */
+static FullTransactionId
+consume_xids_common(FullTransactionId untilxid, uint64 nxids)
+{
+ FullTransactionId lastxid;
+ uint64 last_reported_at = 0;
+ uint64 consumed = 0;
+
+ /* Print a NOTICE every REPORT_INTERVAL xids */
+#define REPORT_INTERVAL (10 * 1000000)
+
+ /* initialize 'lastxid' with the system's current next XID */
+ lastxid = ReadNextFullTransactionId();
+
+ /*
+ * We consume XIDs by calling GetNewTransactionId(true), which marks the
+ * consumed XIDs as subtransactions of the current top-level transaction.
+ * For that to work, this transaction must have a top-level XID.
+ *
+ * GetNewTransactionId registers them in the subxid cache in PGPROC, until
+ * the cache overflows, but beyond that, we don't keep track of the
+ * consumed XIDs.
+ */
+ (void) GetTopTransactionId();
+
+ for (;;)
+ {
+ uint64 xids_left;
+
+ CHECK_FOR_INTERRUPTS();
+
+ /* How many XIDs do we have left to consume? */
+ if (nxids > 0)
+ {
+ if (consumed >= nxids)
+ break;
+ xids_left = nxids - consumed;
+ }
+ else
+ {
+ if (FullTransactionIdFollowsOrEquals(lastxid, untilxid))
+ break;
+ xids_left = U64FromFullTransactionId(untilxid) - U64FromFullTransactionId(lastxid);
+ }
+
+ /*
+ * If we still have plenty of XIDs to consume, try to take a shortcut
+ * and bump up the nextXid counter directly.
+ */
+ if (xids_left > 2000 &&
+ consumed - last_reported_at < REPORT_INTERVAL &&
+ MyProc->subxidStatus.overflowed)
+ {
+ int64 consumed_by_shortcut = consume_xids_shortcut();
+
+ if (consumed_by_shortcut > 0)
+ {
+ consumed += consumed_by_shortcut;
+ continue;
+ }
+ }
+
+ /* Slow path: Call GetNewTransactionId to allocate a new XID. */
+ lastxid = GetNewTransactionId(true);
+ consumed++;
+
+ /* Report progress */
+ if (consumed - last_reported_at >= REPORT_INTERVAL)
+ {
+ if (nxids > 0)
+ elog(NOTICE, "consumed %llu / %llu XIDs, latest %u:%u",
+ (unsigned long long) consumed, (unsigned long long) nxids,
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid));
+ else
+ elog(NOTICE, "consumed up to %u:%u / %u:%u",
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid),
+ EpochFromFullTransactionId(untilxid),
+ XidFromFullTransactionId(untilxid));
+ last_reported_at = consumed;
+ }
+ }
+
+ return lastxid;
+}
+
+/*
+ * These constants copied from .c files, because they're private.
+ */
+#define COMMIT_TS_XACTS_PER_PAGE (BLCKSZ / 10)
+#define SUBTRANS_XACTS_PER_PAGE (BLCKSZ / sizeof(TransactionId))
+#define CLOG_XACTS_PER_BYTE 4
+#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
+
+/*
+ * All the interesting action in GetNewTransactionId happens when we extend
+ * the SLRUs, or at the uint32 wraparound. If the nextXid counter is not close
+ * to any of those interesting values, take a shortcut and bump nextXID
+ * directly, close to the next "interesting" value.
+ */
+static inline uint32
+XidSkip(FullTransactionId fullxid)
+{
+ uint32 low = XidFromFullTransactionId(fullxid);
+ uint32 rem;
+ uint32 distance;
+
+ if (low < 5 || low >= UINT32_MAX - 5)
+ return 0;
+ distance = UINT32_MAX - 5 - low;
+
+ rem = low % COMMIT_TS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, COMMIT_TS_XACTS_PER_PAGE - rem);
+
+ rem = low % SUBTRANS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, SUBTRANS_XACTS_PER_PAGE - rem);
+
+ rem = low % CLOG_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, CLOG_XACTS_PER_PAGE - rem);
+
+ return distance;
+}
+
+static int64
+consume_xids_shortcut(void)
+{
+ FullTransactionId nextXid;
+ uint32 consumed;
+
+ LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
+ nextXid = ShmemVariableCache->nextXid;
+
+ /*
+ * Go slow near the "interesting values". The interesting zones include 5
+ * transactions before and after SLRU page switches.
+ */
+ consumed = XidSkip(nextXid);
+ if (consumed > 0)
+ ShmemVariableCache->nextXid.value += (uint64) consumed;
+
+ LWLockRelease(XidGenLock);
+
+ return consumed;
+}
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.control b/src/test/modules/xid_wraparound/xid_wraparound.control
new file mode 100644
index 0000000000..6c6964ed3d
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.control
@@ -0,0 +1,4 @@
+comment = 'Tests for XID wraparound'
+default_version = '1.0'
+module_pathname = '$libdir/xid_wraparound'
+relocatable = true
--
2.31.1
[application/octet-stream] v6-0002-Add-option-to-specify-timeout-seconds-to-Backgrou.patch (3.5K, ../../CAD21AoBUgppMyu5ExLWOfZaBOySsv+v3LF7Mu836Bo=ACqHKJQ@mail.gmail.com/4-v6-0002-Add-option-to-specify-timeout-seconds-to-Backgrou.patch)
download | inline diff:
From b4dbb1cf1ba74e14a3c7383e9fa000593127dd97 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Wed, 30 Aug 2023 23:10:59 +0900
Subject: [PATCH v6 2/3] Add option to specify timeout seconds to
BackgroundPsql.pm.
Author: Masahiko Sawada
Reviewed-by: Daniel Gustafsson, Noah Misch
Discussion: https://postgr.es/m/C9CF2F76-0D81-4C9D-9832-202BE8517056%40yesql.se
---
src/test/perl/PostgreSQL/Test/BackgroundPsql.pm | 10 ++++++----
src/test/perl/PostgreSQL/Test/Cluster.pm | 11 ++++++++---
2 files changed, 14 insertions(+), 7 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm b/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
index 924b57ab21..58d393f5b8 100644
--- a/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
+++ b/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
@@ -68,7 +68,7 @@ use Test::More;
=over
-=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params)
+=item PostgreSQL::Test::BackgroundPsql->new(interactive, @psql_params, timeout)
Builds a new object of class C<PostgreSQL::Test::BackgroundPsql> for either
an interactive or background session and starts it. If C<interactive> is
@@ -81,7 +81,7 @@ string. For C<interactive> sessions, IO::Pty is required.
sub new
{
my $class = shift;
- my ($interactive, $psql_params) = @_;
+ my ($interactive, $psql_params, $timeout) = @_;
my $psql = {
'stdin' => '',
'stdout' => '',
@@ -96,8 +96,10 @@ sub new
"Forbidden caller of constructor: package: $package, file: $file:$line"
unless $package->isa('PostgreSQL::Test::Cluster');
- $psql->{timeout} =
- IPC::Run::timeout($PostgreSQL::Test::Utils::timeout_default);
+ $psql->{timeout} = IPC::Run::timeout(
+ defined($timeout)
+ ? $timeout
+ : $PostgreSQL::Test::Utils::timeout_default);
if ($interactive)
{
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index 4b7baa908f..f37907bcf7 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -2028,8 +2028,6 @@ sub psql
Invoke B<psql> on B<$dbname> and return a BackgroundPsql object.
-A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
-
psql is invoked in tuples-only unaligned mode with reading of B<.psqlrc>
disabled. That may be overridden by passing extra psql parameters.
@@ -2047,6 +2045,11 @@ By default, the B<psql> method invokes the B<psql> program with ON_ERROR_STOP=1
set, so SQL execution is stopped at the first error and exit code 3 is
returned. Set B<on_error_stop> to 0 to ignore errors instead.
+=item timeout => 'interval'
+
+Set a timeout for a background psql session. By default, timeout of
+$PostgreSQL::Test::Utils::timeout_default is set up.
+
=item replication => B<value>
If set, add B<replication=value> to the conninfo string.
@@ -2068,6 +2071,7 @@ sub background_psql
local %ENV = $self->_get_env();
my $replication = $params{replication};
+ my $timeout = undef;
my @psql_params = (
$self->installed_command('psql'),
@@ -2079,12 +2083,13 @@ sub background_psql
'-');
$params{on_error_stop} = 1 unless defined $params{on_error_stop};
+ $timeout = $params{timeout} if defined $params{timeout};
push @psql_params, '-v', 'ON_ERROR_STOP=1' if $params{on_error_stop};
push @psql_params, @{ $params{extra_params} }
if defined $params{extra_params};
- return PostgreSQL::Test::BackgroundPsql->new(0, \@psql_params);
+ return PostgreSQL::Test::BackgroundPsql->new(0, \@psql_params, $timeout);
}
=pod
--
2.31.1
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-11-28 10:16 Daniel Gustafsson <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Daniel Gustafsson @ 2023-11-28 10:16 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
> On 28 Nov 2023, at 03:00, Masahiko Sawada <[email protected]> wrote:
>
> On Mon, Nov 27, 2023 at 10:40 PM Daniel Gustafsson <[email protected]> wrote:
>>
>>> On 27 Nov 2023, at 14:06, Masahiko Sawada <[email protected]> wrote:
>>
>>> Is it true that we can modify the timeout after creating
>>> BackgroundPsql object? If so, it seems we don't need to introduce the
>>> new timeout argument. But how?
>>
>> I can't remember if that's leftovers that incorrectly remains from an earlier
>> version of the BackgroundPsql work, or if it's a very bad explanation of
>> ->set_query_timer_restart(). The timeout will use the timeout_default value
>> and that cannot be overridden, it can only be reset per query.
>
> Thank you for confirming this. I see there is the same problem also in
> interactive_psql(). So I've attached the 0001 patch to fix these
> documentation issues.
-A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
-which can be modified later.
+A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
Since it cannot be modified, I think we should just say "A timeout of .." and
call it a default timeout. This obviously only matters for the backpatch since
the sentence is removed in 0002.
> Which could be backpatched.
+1
>> With your patch the timeout still cannot be changed, but at least set during
>> start which seems good enough until there are tests warranting more complexity.
>> The docs should be corrected to reflect this in your patch.
>
> I've incorporated the comments except for the following one and
> attached updated version of the rest patches:
LGTM.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-11-28 20:27 Masahiko Sawada <[email protected]>
parent: Daniel Gustafsson <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2023-11-28 20:27 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Tue, Nov 28, 2023 at 7:16 PM Daniel Gustafsson <[email protected]> wrote:
>
> > On 28 Nov 2023, at 03:00, Masahiko Sawada <[email protected]> wrote:
> >
> > On Mon, Nov 27, 2023 at 10:40 PM Daniel Gustafsson <[email protected]> wrote:
> >>
> >>> On 27 Nov 2023, at 14:06, Masahiko Sawada <[email protected]> wrote:
> >>
> >>> Is it true that we can modify the timeout after creating
> >>> BackgroundPsql object? If so, it seems we don't need to introduce the
> >>> new timeout argument. But how?
> >>
> >> I can't remember if that's leftovers that incorrectly remains from an earlier
> >> version of the BackgroundPsql work, or if it's a very bad explanation of
> >> ->set_query_timer_restart(). The timeout will use the timeout_default value
> >> and that cannot be overridden, it can only be reset per query.
> >
> > Thank you for confirming this. I see there is the same problem also in
> > interactive_psql(). So I've attached the 0001 patch to fix these
> > documentation issues.
>
> -A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
> -which can be modified later.
> +A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
>
> Since it cannot be modified, I think we should just say "A timeout of .." and
> call it a default timeout. This obviously only matters for the backpatch since
> the sentence is removed in 0002.
Agreed.
I've attached new version patches (0002 and 0003 are unchanged except
for the commit message). I'll push them, barring any objections.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] v7-0001-Fix-wrong-description-of-BackgroundPsql-s-timeout.patch (1.6K, ../../CAD21AoD3dKEVVEmy7W1kBXmDevj3o_dQ8qnRPda6c4TfCzy7bA@mail.gmail.com/2-v7-0001-Fix-wrong-description-of-BackgroundPsql-s-timeout.patch)
download | inline diff:
From 00973920b19d741e4733793880c68f6d2c08351b Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Tue, 28 Nov 2023 10:34:48 +0900
Subject: [PATCH v7 1/3] Fix wrong description of BackgroundPsql's timeout.
Backpatch through 16 where this was introduced.
Reviewed-by: Daniel Gustafsson
Backpatch-through: 16
Discussion: http://postgr.es/m/CAD21AoBXMEqDBLoDuAWVWoTLYB4aNsxx4oYNmyJJbhfq_vGQBQ@mail.gmail.com
---
src/test/perl/PostgreSQL/Test/Cluster.pm | 6 ++----
1 file changed, 2 insertions(+), 4 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index c3d46c7c70..321b77d7ed 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -2028,8 +2028,7 @@ sub psql
Invoke B<psql> on B<$dbname> and return a BackgroundPsql object.
-A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
-which can be modified later.
+A timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
psql is invoked in tuples-only unaligned mode with reading of B<.psqlrc>
disabled. That may be overridden by passing extra psql parameters.
@@ -2095,8 +2094,7 @@ sub background_psql
Invoke B<psql> on B<$dbname> and return a BackgroundPsql object, which the
caller may use to send interactive input to B<psql>.
-A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
-which can be modified later.
+A timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
psql is invoked in tuples-only unaligned mode with reading of B<.psqlrc>
disabled. That may be overridden by passing extra psql parameters.
--
2.31.1
[application/octet-stream] v7-0003-Add-tests-for-XID-wraparound.patch (23.7K, ../../CAD21AoD3dKEVVEmy7W1kBXmDevj3o_dQ8qnRPda6c4TfCzy7bA@mail.gmail.com/3-v7-0003-Add-tests-for-XID-wraparound.patch)
download | inline diff:
From 8dbc82d5e867d085fac11fbef112ef1fc3401b54 Mon Sep 17 00:00:00 2001
From: Heikki Linnakangas <[email protected]>
Date: Fri, 3 Mar 2023 12:01:28 +0200
Subject: [PATCH v7 3/3] Add tests for XID wraparound.
The test module includes helper functions to quickly burn through lots
of XIDs. They are used in the tests, and are also handy for manually
testing XID wraparound.
Since these tests are very expensive the entire suite is disabled by
default. It requires to set PG_TET_EXTRA to run it.
Reviewed-by: Daniel Gustafsson, John Naylor, Michael Paquier
Reviewed-by: vignesh C
Author: Heikki Linnakangas, Masahiko Sawada, Andres Freund
Discussion: https://www.postgresql.org/message-id/CAD21AoDVhkXp8HjpFO-gp3TgL6tCKcZQNxn04m01VAtcSi-5sA%40mail.gmail.com
---
doc/src/sgml/regress.sgml | 10 +
src/test/modules/Makefile | 3 +-
src/test/modules/meson.build | 1 +
src/test/modules/xid_wraparound/.gitignore | 4 +
src/test/modules/xid_wraparound/Makefile | 23 ++
src/test/modules/xid_wraparound/README | 3 +
src/test/modules/xid_wraparound/meson.build | 36 +++
.../xid_wraparound/t/001_emergency_vacuum.pl | 133 +++++++++++
.../modules/xid_wraparound/t/002_limits.pl | 138 +++++++++++
.../xid_wraparound/t/003_wraparounds.pl | 60 +++++
.../xid_wraparound/xid_wraparound--1.0.sql | 12 +
.../modules/xid_wraparound/xid_wraparound.c | 219 ++++++++++++++++++
.../xid_wraparound/xid_wraparound.control | 4 +
13 files changed, 645 insertions(+), 1 deletion(-)
create mode 100644 src/test/modules/xid_wraparound/.gitignore
create mode 100644 src/test/modules/xid_wraparound/Makefile
create mode 100644 src/test/modules/xid_wraparound/README
create mode 100644 src/test/modules/xid_wraparound/meson.build
create mode 100644 src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
create mode 100644 src/test/modules/xid_wraparound/t/002_limits.pl
create mode 100644 src/test/modules/xid_wraparound/t/003_wraparounds.pl
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.c
create mode 100644 src/test/modules/xid_wraparound/xid_wraparound.control
diff --git a/doc/src/sgml/regress.sgml b/doc/src/sgml/regress.sgml
index 69f627d7f4..70d9bdefe1 100644
--- a/doc/src/sgml/regress.sgml
+++ b/doc/src/sgml/regress.sgml
@@ -314,6 +314,16 @@ make check-world PG_TEST_EXTRA='kerberos ldap ssl load_balance'
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>xid_wraparound</literal></term>
+ <listitem>
+ <para>
+ Runs the test suite under <filename>src/test/module/xid_wrapround</filename>.
+ Not enabled by default because it is resource intensive.
+ </para>
+ </listitem>
+ </varlistentry>
</variablelist>
Tests for features that are not supported by the current build
diff --git a/src/test/modules/Makefile b/src/test/modules/Makefile
index a18e4d28a0..5d33fa6a9a 100644
--- a/src/test/modules/Makefile
+++ b/src/test/modules/Makefile
@@ -34,7 +34,8 @@ SUBDIRS = \
test_shm_mq \
test_slru \
unsafe_tests \
- worker_spi
+ worker_spi \
+ xid_wraparound
ifeq ($(with_ssl),openssl)
SUBDIRS += ssl_passphrase_callback
diff --git a/src/test/modules/meson.build b/src/test/modules/meson.build
index 4e83c0f8d7..b76f588559 100644
--- a/src/test/modules/meson.build
+++ b/src/test/modules/meson.build
@@ -32,3 +32,4 @@ subdir('test_shm_mq')
subdir('test_slru')
subdir('unsafe_tests')
subdir('worker_spi')
+subdir('xid_wraparound')
diff --git a/src/test/modules/xid_wraparound/.gitignore b/src/test/modules/xid_wraparound/.gitignore
new file mode 100644
index 0000000000..5dcb3ff972
--- /dev/null
+++ b/src/test/modules/xid_wraparound/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/xid_wraparound/Makefile b/src/test/modules/xid_wraparound/Makefile
new file mode 100644
index 0000000000..7a6e0f6676
--- /dev/null
+++ b/src/test/modules/xid_wraparound/Makefile
@@ -0,0 +1,23 @@
+# src/test/modules/xid_wraparound/Makefile
+
+MODULE_big = xid_wraparound
+OBJS = \
+ $(WIN32RES) \
+ xid_wraparound.o
+PGFILEDESC = "xid_wraparound - tests for XID wraparound"
+
+EXTENSION = xid_wraparound
+DATA = xid_wraparound--1.0.sql
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/xid_wraparound
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/xid_wraparound/README b/src/test/modules/xid_wraparound/README
new file mode 100644
index 0000000000..3aab464dec
--- /dev/null
+++ b/src/test/modules/xid_wraparound/README
@@ -0,0 +1,3 @@
+This module contains tests for XID wraparound. The tests use two
+helper functions to quickly consume lots of XIDs, to reach XID
+wraparound faster.
diff --git a/src/test/modules/xid_wraparound/meson.build b/src/test/modules/xid_wraparound/meson.build
new file mode 100644
index 0000000000..42f933525e
--- /dev/null
+++ b/src/test/modules/xid_wraparound/meson.build
@@ -0,0 +1,36 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+xid_wraparound_sources = files(
+ 'xid_wraparound.c',
+)
+
+if host_system == 'windows'
+ xid_wraparound_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
+ '--NAME', 'xid_wraparound',
+ '--FILEDESC', 'xid_wraparound - tests for XID wraparound',])
+endif
+
+xid_wraparound = shared_module('xid_wraparound',
+ xid_wraparound_sources,
+ kwargs: pg_mod_args,
+)
+testprep_targets += xid_wraparound
+
+install_data(
+ 'xid_wraparound.control',
+ 'xid_wraparound--1.0.sql',
+ kwargs: contrib_data_args,
+)
+
+tests += {
+ 'name': 'xid_wraparound',
+ 'sd': meson.current_source_dir(),
+ 'bd': meson.current_build_dir(),
+ 'tap': {
+ 'tests': [
+ 't/001_emergency_vacuum.pl',
+ 't/002_limits.pl',
+ 't/003_wraparounds.pl',
+ ],
+ },
+}
diff --git a/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
new file mode 100644
index 0000000000..2ae1667d58
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/001_emergency_vacuum.pl
@@ -0,0 +1,133 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+
+# Test wraparound emergency autovacuum.
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('main');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create tables for a few different test scenarios
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE large(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large(data) SELECT generate_series(1,30000);
+
+CREATE TABLE large_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO large_trunc(data) SELECT generate_series(1,30000);
+
+CREATE TABLE small(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small(data) SELECT generate_series(1,15000);
+
+CREATE TABLE small_trunc(id serial primary key, data text, filler text default repeat(random()::text, 10));
+INSERT INTO small_trunc(data) SELECT generate_series(1,15000);
+
+CREATE TABLE autovacuum_disabled(id serial primary key, data text) WITH (autovacuum_enabled=false);
+INSERT INTO autovacuum_disabled(data) SELECT generate_series(1,1000);
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $background_psql = $node->background_psql(
+ 'postgres',
+ on_error_stop => 0,
+ timeout => $psql_timeout_secs);
+$background_psql->set_query_timer_restart();
+$background_psql->query_safe(
+ q{
+ BEGIN;
+ DELETE FROM large WHERE id % 2 = 0;
+ DELETE FROM large_trunc WHERE id > 10000;
+ DELETE FROM small WHERE id % 2 = 0;
+ DELETE FROM small_trunc WHERE id > 1000;
+ DELETE FROM autovacuum_disabled WHERE id % 2 = 0;
+});
+
+# Consume 2 billion XIDs, to get us very close to wraparound
+$node->safe_psql('postgres',
+ qq[SELECT consume_xids_until('2000000000'::xid8)]);
+
+# Make sure the latest completed XID is advanced
+$node->safe_psql('postgres', qq[INSERT INTO small(data) SELECT 1]);
+
+# Check that all databases became old enough to trigger failsafe.
+my $ret = $node->safe_psql(
+ 'postgres',
+ qq[
+SELECT datname,
+ age(datfrozenxid) > current_setting('vacuum_failsafe_age')::int as old
+FROM pg_database ORDER BY 1
+]);
+is( $ret, "postgres|t
+template0|t
+template1|t", "all tables became old");
+
+my $log_offset = -s $node->logfile;
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# Wait until autovacuum processed all tables and advanced the
+# system-wide oldest-XID.
+$node->poll_query_until(
+ 'postgres',
+ qq[
+SELECT NOT EXISTS (
+ SELECT *
+ FROM pg_database
+ WHERE age(datfrozenxid) > current_setting('autovacuum_freeze_max_age')::int)
+]) or die "timeout waiting for all databases to be vacuumed";
+
+# Check if these tables are vacuumed.
+$ret = $node->safe_psql(
+ 'postgres', qq[
+SELECT relname, age(relfrozenxid) > current_setting('autovacuum_freeze_max_age')::int
+FROM pg_class
+WHERE relname IN ('large', 'large_trunc', 'small', 'small_trunc', 'autovacuum_disabled')
+ORDER BY 1
+]);
+
+is( $ret, "autovacuum_disabled|f
+large|f
+large_trunc|f
+small|f
+small_trunc|f", "all tables are vacuumed");
+
+# Check if vacuum failsafe was triggered for each table.
+my $log_contents = slurp_file($node->logfile, $log_offset);
+foreach my $tablename ('large', 'large_trunc', 'small', 'small_trunc',
+ 'autovacuum_disabled')
+{
+ like(
+ $log_contents,
+ qr/bypassing nonessential maintenance of table "postgres.public.$tablename" as a failsafe after \d+ index scans/,
+ "failsafe vacuum triggered for $tablename");
+}
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/002_limits.pl b/src/test/modules/xid_wraparound/t/002_limits.pl
new file mode 100644
index 0000000000..b35352f25b
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/002_limits.pl
@@ -0,0 +1,138 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Test XID wraparound limits.
+#
+# When you get close to XID wraparound, you start to get warnings, and
+# when you get even closer, the system refuses to assign any more XIDs
+# until the oldest databases have been vacuumed and datfrozenxid has
+# been advanced.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+my $ret;
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only to prevent wraparound
+autovacuum_naptime = 1s
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('start');
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Start a background session, which holds a transaction open, preventing
+# autovacuum from advancing relfrozenxid and datfrozenxid.
+my $background_psql = $node->background_psql(
+ 'postgres',
+ on_error_stop => 0,
+ timeout => $psql_timeout_secs);
+$background_psql->query_safe(
+ q{
+ BEGIN;
+ INSERT INTO wraparoundtest VALUES ('oldxact');
+});
+
+# Consume 2 billion transactions, to get close to wraparound
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after 1 billion')]);
+
+$node->safe_psql('postgres', qq[SELECT consume_xids(1000000000)]);
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after 2 billion')]);
+
+# We are now just under 150 million XIDs away from wraparound.
+# Continue consuming XIDs, in batches of 10 million, until we get
+# the warning:
+#
+# WARNING: database "postgres" must be vacuumed within 3000024 transactions
+# HINT: To avoid a database shutdown, execute a database-wide VACUUM in that database.
+# You might also need to commit or roll back old prepared transactions, or drop stale replication slots.
+my $stderr;
+my $warn_limit = 0;
+for my $i (1 .. 15)
+{
+ $node->psql(
+ 'postgres', qq[SELECT consume_xids(10000000)],
+ stderr => \$stderr,
+ on_error_die => 1);
+
+ if ($stderr =~
+ /WARNING: database "postgres" must be vacuumed within [0-9]+ transactions/
+ )
+ {
+ # Reached the warn-limit
+ $warn_limit = 1;
+ last;
+ }
+}
+ok($warn_limit == 1, "warn-limit reached");
+
+# We can still INSERT, despite the warnings.
+$node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('reached warn-limit')]);
+
+# Keep going. We'll hit the hard "stop" limit.
+$ret = $node->psql(
+ 'postgres',
+ qq[SELECT consume_xids(100000000)],
+ stderr => \$stderr);
+like(
+ $stderr,
+ qr/ERROR: database is not accepting commands that assign new XIDs to avoid wraparound data loss in database "postgres"/,
+ "stop-limit");
+
+# Finish the old transaction, to allow vacuum freezing to advance
+# relfrozenxid and datfrozenxid again.
+$background_psql->query_safe(q{COMMIT;});
+$background_psql->quit;
+
+# VACUUM, to freeze the tables and advance datfrozenxid.
+#
+# Autovacuum does this for the other databases, and would do it for
+# 'postgres' too, but let's test manual VACUUM.
+#
+$node->safe_psql('postgres', 'VACUUM');
+
+# Wait until autovacuum has processed the other databases and advanced
+# the system-wide oldest-XID.
+$ret =
+ $node->poll_query_until('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after VACUUM')],
+ 'INSERT 0 1');
+
+# Check the table contents
+$ret = $node->safe_psql('postgres', qq[SELECT * from wraparoundtest]);
+is( $ret, "start
+oldxact
+after 1 billion
+after 2 billion
+reached warn-limit
+after VACUUM");
+
+$node->stop;
+done_testing();
diff --git a/src/test/modules/xid_wraparound/t/003_wraparounds.pl b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
new file mode 100644
index 0000000000..be71b00a17
--- /dev/null
+++ b/src/test/modules/xid_wraparound/t/003_wraparounds.pl
@@ -0,0 +1,60 @@
+# Copyright (c) 2023, PostgreSQL Global Development Group
+#
+# Consume a lot of XIDs, wrapping around a few times.
+#
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+use Time::HiRes qw(usleep);
+
+if ($ENV{PG_TEST_EXTRA} !~ /\bxid_wraparound\b/)
+{
+ plan skip_all => "test xid_wraparound not enabled in PG_TEST_EXTRA";
+}
+
+# Initialize node
+my $node = PostgreSQL::Test::Cluster->new('wraparound');
+
+$node->init;
+$node->append_conf(
+ 'postgresql.conf', qq[
+autovacuum = off # run autovacuum only when to anti wraparound
+autovacuum_naptime = 1s
+# so it's easier to verify the order of operations
+autovacuum_max_workers = 1
+log_autovacuum_min_duration = 0
+]);
+$node->start;
+$node->safe_psql('postgres', 'CREATE EXTENSION xid_wraparound');
+
+# Create a test table
+$node->safe_psql(
+ 'postgres', qq[
+CREATE TABLE wraparoundtest(t text);
+INSERT INTO wraparoundtest VALUES ('beginning');
+]);
+
+# Bump the query timeout to avoid false negatives on slow test systems.
+my $psql_timeout_secs = 4 * $PostgreSQL::Test::Utils::timeout_default;
+
+# Burn through 10 billion transactions in total, in batches of 100 million.
+my $ret;
+for my $i (1 .. 100)
+{
+ $ret = $node->safe_psql(
+ 'postgres',
+ qq[SELECT consume_xids(100000000)],
+ timeout => $psql_timeout_secs);
+ $ret = $node->safe_psql('postgres',
+ qq[INSERT INTO wraparoundtest VALUES ('after $i batches')]);
+}
+
+$ret = $node->safe_psql('postgres', qq[SELECT COUNT(*) FROM wraparoundtest]);
+is($ret, "101");
+
+$node->stop;
+
+done_testing();
diff --git a/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
new file mode 100644
index 0000000000..51d25fc4c6
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound--1.0.sql
@@ -0,0 +1,12 @@
+/* src/test/modules/xid_wraparound/xid_wraparound--1.0.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "CREATE EXTENSION xid_wraparound" to load this file. \quit
+
+CREATE FUNCTION consume_xids(nxids bigint)
+RETURNS xid8 IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
+
+CREATE FUNCTION consume_xids_until(targetxid xid8)
+RETURNS xid8 IMMUTABLE PARALLEL SAFE STRICT
+AS 'MODULE_PATHNAME' LANGUAGE C;
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.c b/src/test/modules/xid_wraparound/xid_wraparound.c
new file mode 100644
index 0000000000..312eebbbc8
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.c
@@ -0,0 +1,219 @@
+/*--------------------------------------------------------------------------
+ *
+ * xid_wraparound.c
+ * Utilities for testing XID wraparound
+ *
+ *
+ * Portions Copyright (c) 1996-2023, PostgreSQL Global Development Group
+ * Portions Copyright (c) 1994, Regents of the University of California
+ *
+ * IDENTIFICATION
+ * src/test/modules/xid_wraparound/xid_wraparound.c
+ *
+ * -------------------------------------------------------------------------
+ */
+#include "postgres.h"
+
+#include "access/xact.h"
+#include "miscadmin.h"
+#include "storage/proc.h"
+#include "utils/xid8.h"
+
+PG_MODULE_MAGIC;
+
+static int64 consume_xids_shortcut(void);
+static FullTransactionId consume_xids_common(FullTransactionId untilxid, uint64 nxids);
+
+/*
+ * Consume the specified number of XIDs.
+ */
+PG_FUNCTION_INFO_V1(consume_xids);
+Datum
+consume_xids(PG_FUNCTION_ARGS)
+{
+ int64 nxids = PG_GETARG_INT64(0);
+ FullTransactionId lastxid;
+
+ if (nxids < 0)
+ elog(ERROR, "invalid nxids argument: %lld", (long long) nxids);
+
+ if (nxids == 0)
+ lastxid = ReadNextFullTransactionId();
+ else
+ lastxid = consume_xids_common(InvalidFullTransactionId, (uint64) nxids);
+
+ PG_RETURN_FULLTRANSACTIONID(lastxid);
+}
+
+/*
+ * Consume XIDs, up to the given XID.
+ */
+PG_FUNCTION_INFO_V1(consume_xids_until);
+Datum
+consume_xids_until(PG_FUNCTION_ARGS)
+{
+ FullTransactionId targetxid = PG_GETARG_FULLTRANSACTIONID(0);
+ FullTransactionId lastxid;
+
+ if (!FullTransactionIdIsNormal(targetxid))
+ elog(ERROR, "targetxid %llu is not normal",
+ (unsigned long long) U64FromFullTransactionId(targetxid));
+
+ lastxid = consume_xids_common(targetxid, 0);
+
+ PG_RETURN_FULLTRANSACTIONID(lastxid);
+}
+
+/*
+ * Common functionality between the two public functions.
+ */
+static FullTransactionId
+consume_xids_common(FullTransactionId untilxid, uint64 nxids)
+{
+ FullTransactionId lastxid;
+ uint64 last_reported_at = 0;
+ uint64 consumed = 0;
+
+ /* Print a NOTICE every REPORT_INTERVAL xids */
+#define REPORT_INTERVAL (10 * 1000000)
+
+ /* initialize 'lastxid' with the system's current next XID */
+ lastxid = ReadNextFullTransactionId();
+
+ /*
+ * We consume XIDs by calling GetNewTransactionId(true), which marks the
+ * consumed XIDs as subtransactions of the current top-level transaction.
+ * For that to work, this transaction must have a top-level XID.
+ *
+ * GetNewTransactionId registers them in the subxid cache in PGPROC, until
+ * the cache overflows, but beyond that, we don't keep track of the
+ * consumed XIDs.
+ */
+ (void) GetTopTransactionId();
+
+ for (;;)
+ {
+ uint64 xids_left;
+
+ CHECK_FOR_INTERRUPTS();
+
+ /* How many XIDs do we have left to consume? */
+ if (nxids > 0)
+ {
+ if (consumed >= nxids)
+ break;
+ xids_left = nxids - consumed;
+ }
+ else
+ {
+ if (FullTransactionIdFollowsOrEquals(lastxid, untilxid))
+ break;
+ xids_left = U64FromFullTransactionId(untilxid) - U64FromFullTransactionId(lastxid);
+ }
+
+ /*
+ * If we still have plenty of XIDs to consume, try to take a shortcut
+ * and bump up the nextXid counter directly.
+ */
+ if (xids_left > 2000 &&
+ consumed - last_reported_at < REPORT_INTERVAL &&
+ MyProc->subxidStatus.overflowed)
+ {
+ int64 consumed_by_shortcut = consume_xids_shortcut();
+
+ if (consumed_by_shortcut > 0)
+ {
+ consumed += consumed_by_shortcut;
+ continue;
+ }
+ }
+
+ /* Slow path: Call GetNewTransactionId to allocate a new XID. */
+ lastxid = GetNewTransactionId(true);
+ consumed++;
+
+ /* Report progress */
+ if (consumed - last_reported_at >= REPORT_INTERVAL)
+ {
+ if (nxids > 0)
+ elog(NOTICE, "consumed %llu / %llu XIDs, latest %u:%u",
+ (unsigned long long) consumed, (unsigned long long) nxids,
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid));
+ else
+ elog(NOTICE, "consumed up to %u:%u / %u:%u",
+ EpochFromFullTransactionId(lastxid),
+ XidFromFullTransactionId(lastxid),
+ EpochFromFullTransactionId(untilxid),
+ XidFromFullTransactionId(untilxid));
+ last_reported_at = consumed;
+ }
+ }
+
+ return lastxid;
+}
+
+/*
+ * These constants copied from .c files, because they're private.
+ */
+#define COMMIT_TS_XACTS_PER_PAGE (BLCKSZ / 10)
+#define SUBTRANS_XACTS_PER_PAGE (BLCKSZ / sizeof(TransactionId))
+#define CLOG_XACTS_PER_BYTE 4
+#define CLOG_XACTS_PER_PAGE (BLCKSZ * CLOG_XACTS_PER_BYTE)
+
+/*
+ * All the interesting action in GetNewTransactionId happens when we extend
+ * the SLRUs, or at the uint32 wraparound. If the nextXid counter is not close
+ * to any of those interesting values, take a shortcut and bump nextXID
+ * directly, close to the next "interesting" value.
+ */
+static inline uint32
+XidSkip(FullTransactionId fullxid)
+{
+ uint32 low = XidFromFullTransactionId(fullxid);
+ uint32 rem;
+ uint32 distance;
+
+ if (low < 5 || low >= UINT32_MAX - 5)
+ return 0;
+ distance = UINT32_MAX - 5 - low;
+
+ rem = low % COMMIT_TS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, COMMIT_TS_XACTS_PER_PAGE - rem);
+
+ rem = low % SUBTRANS_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, SUBTRANS_XACTS_PER_PAGE - rem);
+
+ rem = low % CLOG_XACTS_PER_PAGE;
+ if (rem == 0)
+ return 0;
+ distance = Min(distance, CLOG_XACTS_PER_PAGE - rem);
+
+ return distance;
+}
+
+static int64
+consume_xids_shortcut(void)
+{
+ FullTransactionId nextXid;
+ uint32 consumed;
+
+ LWLockAcquire(XidGenLock, LW_EXCLUSIVE);
+ nextXid = ShmemVariableCache->nextXid;
+
+ /*
+ * Go slow near the "interesting values". The interesting zones include 5
+ * transactions before and after SLRU page switches.
+ */
+ consumed = XidSkip(nextXid);
+ if (consumed > 0)
+ ShmemVariableCache->nextXid.value += (uint64) consumed;
+
+ LWLockRelease(XidGenLock);
+
+ return consumed;
+}
diff --git a/src/test/modules/xid_wraparound/xid_wraparound.control b/src/test/modules/xid_wraparound/xid_wraparound.control
new file mode 100644
index 0000000000..6c6964ed3d
--- /dev/null
+++ b/src/test/modules/xid_wraparound/xid_wraparound.control
@@ -0,0 +1,4 @@
+comment = 'Tests for XID wraparound'
+default_version = '1.0'
+module_pathname = '$libdir/xid_wraparound'
+relocatable = true
--
2.31.1
[application/octet-stream] v7-0002-Add-option-to-specify-timeout-seconds-to-Backgrou.patch (3.6K, ../../CAD21AoD3dKEVVEmy7W1kBXmDevj3o_dQ8qnRPda6c4TfCzy7bA@mail.gmail.com/4-v7-0002-Add-option-to-specify-timeout-seconds-to-Backgrou.patch)
download | inline diff:
From 508cc515b6ac04487544d005b77d20b709fa11b2 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Wed, 30 Aug 2023 23:10:59 +0900
Subject: [PATCH v7 2/3] Add option to specify timeout seconds to
BackgroundPsql.pm.
Previously, a background psql session uses the default timeout and it
cannot be overridden. This change adds a new option to set the timeout
during start.
Reviewed-by: Daniel Gustafsson, Noah Misch
Discussion: https://postgr.es/m/C9CF2F76-0D81-4C9D-9832-202BE8517056%40yesql.se
---
src/test/perl/PostgreSQL/Test/BackgroundPsql.pm | 10 ++++++----
src/test/perl/PostgreSQL/Test/Cluster.pm | 11 ++++++++---
2 files changed, 14 insertions(+), 7 deletions(-)
diff --git a/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm b/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
index 924b57ab21..58d393f5b8 100644
--- a/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
+++ b/src/test/perl/PostgreSQL/Test/BackgroundPsql.pm
@@ -68,7 +68,7 @@ use Test::More;
=over
-=item PostgreSQL::Test::BackgroundPsql->new(interactive, @params)
+=item PostgreSQL::Test::BackgroundPsql->new(interactive, @psql_params, timeout)
Builds a new object of class C<PostgreSQL::Test::BackgroundPsql> for either
an interactive or background session and starts it. If C<interactive> is
@@ -81,7 +81,7 @@ string. For C<interactive> sessions, IO::Pty is required.
sub new
{
my $class = shift;
- my ($interactive, $psql_params) = @_;
+ my ($interactive, $psql_params, $timeout) = @_;
my $psql = {
'stdin' => '',
'stdout' => '',
@@ -96,8 +96,10 @@ sub new
"Forbidden caller of constructor: package: $package, file: $file:$line"
unless $package->isa('PostgreSQL::Test::Cluster');
- $psql->{timeout} =
- IPC::Run::timeout($PostgreSQL::Test::Utils::timeout_default);
+ $psql->{timeout} = IPC::Run::timeout(
+ defined($timeout)
+ ? $timeout
+ : $PostgreSQL::Test::Utils::timeout_default);
if ($interactive)
{
diff --git a/src/test/perl/PostgreSQL/Test/Cluster.pm b/src/test/perl/PostgreSQL/Test/Cluster.pm
index 321b77d7ed..a020377761 100644
--- a/src/test/perl/PostgreSQL/Test/Cluster.pm
+++ b/src/test/perl/PostgreSQL/Test/Cluster.pm
@@ -2028,8 +2028,6 @@ sub psql
Invoke B<psql> on B<$dbname> and return a BackgroundPsql object.
-A timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
-
psql is invoked in tuples-only unaligned mode with reading of B<.psqlrc>
disabled. That may be overridden by passing extra psql parameters.
@@ -2047,6 +2045,11 @@ By default, the B<psql> method invokes the B<psql> program with ON_ERROR_STOP=1
set, so SQL execution is stopped at the first error and exit code 3 is
returned. Set B<on_error_stop> to 0 to ignore errors instead.
+=item timeout => 'interval'
+
+Set a timeout for a background psql session. By default, timeout of
+$PostgreSQL::Test::Utils::timeout_default is set up.
+
=item replication => B<value>
If set, add B<replication=value> to the conninfo string.
@@ -2068,6 +2071,7 @@ sub background_psql
local %ENV = $self->_get_env();
my $replication = $params{replication};
+ my $timeout = undef;
my @psql_params = (
$self->installed_command('psql'),
@@ -2079,12 +2083,13 @@ sub background_psql
'-');
$params{on_error_stop} = 1 unless defined $params{on_error_stop};
+ $timeout = $params{timeout} if defined $params{timeout};
push @psql_params, '-v', 'ON_ERROR_STOP=1' if $params{on_error_stop};
push @psql_params, @{ $params{extra_params} }
if defined $params{extra_params};
- return PostgreSQL::Test::BackgroundPsql->new(0, \@psql_params);
+ return PostgreSQL::Test::BackgroundPsql->new(0, \@psql_params, $timeout);
}
=pod
--
2.31.1
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-11-30 07:35 Masahiko Sawada <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 3 replies; 27+ messages in thread
From: Masahiko Sawada @ 2023-11-30 07:35 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Wed, Nov 29, 2023 at 5:27 AM Masahiko Sawada <[email protected]> wrote:
>
> On Tue, Nov 28, 2023 at 7:16 PM Daniel Gustafsson <[email protected]> wrote:
> >
> > > On 28 Nov 2023, at 03:00, Masahiko Sawada <[email protected]> wrote:
> > >
> > > On Mon, Nov 27, 2023 at 10:40 PM Daniel Gustafsson <[email protected]> wrote:
> > >>
> > >>> On 27 Nov 2023, at 14:06, Masahiko Sawada <[email protected]> wrote:
> > >>
> > >>> Is it true that we can modify the timeout after creating
> > >>> BackgroundPsql object? If so, it seems we don't need to introduce the
> > >>> new timeout argument. But how?
> > >>
> > >> I can't remember if that's leftovers that incorrectly remains from an earlier
> > >> version of the BackgroundPsql work, or if it's a very bad explanation of
> > >> ->set_query_timer_restart(). The timeout will use the timeout_default value
> > >> and that cannot be overridden, it can only be reset per query.
> > >
> > > Thank you for confirming this. I see there is the same problem also in
> > > interactive_psql(). So I've attached the 0001 patch to fix these
> > > documentation issues.
> >
> > -A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
> > -which can be modified later.
> > +A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
> >
> > Since it cannot be modified, I think we should just say "A timeout of .." and
> > call it a default timeout. This obviously only matters for the backpatch since
> > the sentence is removed in 0002.
>
> Agreed.
>
> I've attached new version patches (0002 and 0003 are unchanged except
> for the commit message). I'll push them, barring any objections.
>
Pushed.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2023-12-01 02:14 Masahiko Sawada <[email protected]>
parent: Masahiko Sawada <[email protected]>
2 siblings, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2023-12-01 02:14 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Thu, Nov 30, 2023 at 4:35 PM Masahiko Sawada <[email protected]> wrote:
>
> On Wed, Nov 29, 2023 at 5:27 AM Masahiko Sawada <[email protected]> wrote:
> >
> > On Tue, Nov 28, 2023 at 7:16 PM Daniel Gustafsson <[email protected]> wrote:
> > >
> > > > On 28 Nov 2023, at 03:00, Masahiko Sawada <[email protected]> wrote:
> > > >
> > > > On Mon, Nov 27, 2023 at 10:40 PM Daniel Gustafsson <[email protected]> wrote:
> > > >>
> > > >>> On 27 Nov 2023, at 14:06, Masahiko Sawada <[email protected]> wrote:
> > > >>
> > > >>> Is it true that we can modify the timeout after creating
> > > >>> BackgroundPsql object? If so, it seems we don't need to introduce the
> > > >>> new timeout argument. But how?
> > > >>
> > > >> I can't remember if that's leftovers that incorrectly remains from an earlier
> > > >> version of the BackgroundPsql work, or if it's a very bad explanation of
> > > >> ->set_query_timer_restart(). The timeout will use the timeout_default value
> > > >> and that cannot be overridden, it can only be reset per query.
> > > >
> > > > Thank you for confirming this. I see there is the same problem also in
> > > > interactive_psql(). So I've attached the 0001 patch to fix these
> > > > documentation issues.
> > >
> > > -A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up,
> > > -which can be modified later.
> > > +A default timeout of $PostgreSQL::Test::Utils::timeout_default is set up.
> > >
> > > Since it cannot be modified, I think we should just say "A timeout of .." and
> > > call it a default timeout. This obviously only matters for the backpatch since
> > > the sentence is removed in 0002.
> >
> > Agreed.
> >
> > I've attached new version patches (0002 and 0003 are unchanged except
> > for the commit message). I'll push them, barring any objections.
> >
>
> Pushed.
FYI I've configured the buildfarm animal perentie to run regression
tests including xid_wraparound:
https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=perentie&br=HEAD
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2024-02-07 18:11 Peter Eisentraut <[email protected]>
parent: Masahiko Sawada <[email protected]>
2 siblings, 1 reply; 27+ messages in thread
From: Peter Eisentraut @ 2024-02-07 18:11 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; Daniel Gustafsson <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
The way src/test/modules/xid_wraparound/meson.build is written, it
installs the xid_wraparound.so module into production installations.
For test modules, a different installation code needs to be used. See
neighboring test modules such as
src/test/modules/test_rbtree/meson.build for examples.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2024-02-08 04:05 Masahiko Sawada <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Masahiko Sawada @ 2024-02-08 04:05 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Thu, Feb 8, 2024 at 3:11 AM Peter Eisentraut <[email protected]> wrote:
>
> The way src/test/modules/xid_wraparound/meson.build is written, it
> installs the xid_wraparound.so module into production installations.
> For test modules, a different installation code needs to be used. See
> neighboring test modules such as
> src/test/modules/test_rbtree/meson.build for examples.
>
Good catch, thanks.
I've attached the patch to fix it. Does it make sense?
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
Attachments:
[application/octet-stream] 0001-Prevent-installation-of-xid_wraparound-test-during-m.patch (1.1K, ../../CAD21AoDU3Z+CUQ2ewCeR_e7snCH2PeL2j+Yr+GnPvkBgspVPkg@mail.gmail.com/2-0001-Prevent-installation-of-xid_wraparound-test-during-m.patch)
download | inline diff:
From 188fa6e4e60106f655e45b4370c976a92ef5f3b6 Mon Sep 17 00:00:00 2001
From: Masahiko Sawada <[email protected]>
Date: Thu, 8 Feb 2024 13:01:36 +0900
Subject: [PATCH] Prevent installation of xid_wraparound test during main
install.
Oversight in e255b646a.
Reported-by: Peter Eisentraut
Discussion: https://postgr.es/m/84cd416a-0e37-4019-8380-1c8a3cdd8c5c%40eisentraut.org
---
src/test/modules/xid_wraparound/meson.build | 7 +++----
1 file changed, 3 insertions(+), 4 deletions(-)
diff --git a/src/test/modules/xid_wraparound/meson.build b/src/test/modules/xid_wraparound/meson.build
index 602172754a..2e5248131b 100644
--- a/src/test/modules/xid_wraparound/meson.build
+++ b/src/test/modules/xid_wraparound/meson.build
@@ -12,14 +12,13 @@ endif
xid_wraparound = shared_module('xid_wraparound',
xid_wraparound_sources,
- kwargs: pg_mod_args,
+ kwargs: pg_test_mod_args,
)
-testprep_targets += xid_wraparound
+test_install_libs += xid_wraparound
-install_data(
+test_install_data += files(
'xid_wraparound.control',
'xid_wraparound--1.0.sql',
- kwargs: contrib_data_args,
)
tests += {
--
2.39.3
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2024-02-08 07:06 Peter Eisentraut <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Peter Eisentraut @ 2024-02-08 07:06 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On 08.02.24 05:05, Masahiko Sawada wrote:
> On Thu, Feb 8, 2024 at 3:11 AM Peter Eisentraut <[email protected]> wrote:
>>
>> The way src/test/modules/xid_wraparound/meson.build is written, it
>> installs the xid_wraparound.so module into production installations.
>> For test modules, a different installation code needs to be used. See
>> neighboring test modules such as
>> src/test/modules/test_rbtree/meson.build for examples.
>>
>
> Good catch, thanks.
>
> I've attached the patch to fix it. Does it make sense?
Yes, that looks correct to me and produces the expected behavior.
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2024-02-08 08:06 Masahiko Sawada <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Masahiko Sawada @ 2024-02-08 08:06 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
On Thu, Feb 8, 2024 at 4:06 PM Peter Eisentraut <[email protected]> wrote:
>
> On 08.02.24 05:05, Masahiko Sawada wrote:
> > On Thu, Feb 8, 2024 at 3:11 AM Peter Eisentraut <[email protected]> wrote:
> >>
> >> The way src/test/modules/xid_wraparound/meson.build is written, it
> >> installs the xid_wraparound.so module into production installations.
> >> For test modules, a different installation code needs to be used. See
> >> neighboring test modules such as
> >> src/test/modules/test_rbtree/meson.build for examples.
> >>
> >
> > Good catch, thanks.
> >
> > I've attached the patch to fix it. Does it make sense?
>
> Yes, that looks correct to me and produces the expected behavior.
>
Thank you for the check. Pushed at 1aa67a5ea687.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2024-05-22 12:00 Alexander Lakhin <[email protected]>
parent: Masahiko Sawada <[email protected]>
2 siblings, 0 replies; 27+ messages in thread
From: Alexander Lakhin @ 2024-05-22 12:00 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; Daniel Gustafsson <[email protected]>; +Cc: Noah Misch <[email protected]>; John Naylor <[email protected]>; Heikki Linnakangas <[email protected]>; Ian Lawrence Barwick <[email protected]>; Andres Freund <[email protected]>; Anastasia Lubennikova <[email protected]>; pgsql-hackers; Peter Geoghegan <[email protected]>
Hello,
30.11.2023 10:35, Masahiko Sawada wrote:
>
>> I've attached new version patches (0002 and 0003 are unchanged except
>> for the commit message). I'll push them, barring any objections.
>>
> Pushed.
I've discovered that the test 001_emergency_vacuum.pl can fail due to a
race condition. I can't see the server log at [1], but I reproduced the
failure locally and with additional logging and log_min_messages = DEBUG3,
the log shows:
...
2024-05-22 11:46:28.125 UTC [21256:2853] DEBUG: SlruScanDirectory invoking callback on pg_xact/0690
2024-05-22 11:46:28.125 UTC [21256:2854] DEBUG: transaction ID wrap limit is 2147484396, limited by database with OID 5
2024-05-22 11:46:28.126 UTC [21256:2855] LOG: !!!SendPostmasterSignal| PMSIGNAL_START_AUTOVAC_LAUNCHER
2024-05-22 11:46:28.135 UTC [14871:20077] DEBUG: postmaster received pmsignal signal
2024-05-22 11:46:28.137 UTC [21257:1] DEBUG: autovacuum launcher started
2024-05-22 11:46:28.137 UTC [21257:2] DEBUG: InitPostgres
2024-05-22 11:46:28.138 UTC [21257:3] LOG: !!!AutoVacLauncherMain| !AutoVacuumingActive() && !ShutdownRequestPending;
before do_start_worker()
2024-05-22 11:46:28.138 UTC [21257:4] LOG: !!!do_start_worker| return quickly when there are no free workers
2024-05-22 11:46:28.138 UTC [21257:5] DEBUG: shmem_exit(0): 4 before_shmem_exit callbacks to make
2024-05-22 11:46:28.138 UTC [21257:6] DEBUG: shmem_exit(0): 6 on_shmem_exit callbacks to make
2024-05-22 11:46:28.138 UTC [21257:7] DEBUG: proc_exit(0): 1 callbacks to make
2024-05-22 11:46:28.138 UTC [21257:8] DEBUG: exit(0)
2024-05-22 11:46:28.138 UTC [21257:9] DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
2024-05-22 11:46:28.138 UTC [21257:10] DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
2024-05-22 11:46:28.138 UTC [21257:11] DEBUG: proc_exit(-1): 0 callbacks to make
2024-05-22 11:46:28.146 UTC [21256:2856] DEBUG: MultiXactId wrap limit is 2147483648, limited by database with OID 5
2024-05-22 11:46:28.146 UTC [21256:2857] DEBUG: MultiXact member stop limit is now 4294914944 based on MultiXact 1
2024-05-22 11:46:28.146 UTC [21256:2858] DEBUG: shmem_exit(0): 4 before_shmem_exit callbacks to make
2024-05-22 11:46:28.147 UTC [21256:2859] DEBUG: shmem_exit(0): 7 on_shmem_exit callbacks to make
2024-05-22 11:46:28.147 UTC [21256:2860] DEBUG: proc_exit(0): 1 callbacks to make
2024-05-22 11:46:28.147 UTC [21256:2861] DEBUG: exit(0)
2024-05-22 11:46:28.147 UTC [21256:2862] DEBUG: shmem_exit(-1): 0 before_shmem_exit callbacks to make
2024-05-22 11:46:28.147 UTC [21256:2863] DEBUG: shmem_exit(-1): 0 on_shmem_exit callbacks to make
2024-05-22 11:46:28.147 UTC [21256:2864] DEBUG: proc_exit(-1): 0 callbacks to make
2024-05-22 11:46:28.151 UTC [14871:20078] DEBUG: forked new backend, pid=21258 socket=8
2024-05-22 11:46:28.171 UTC [14871:20079] DEBUG: server process (PID 21256) exited with exit code 0
2024-05-22 11:46:28.152 UTC [21258:1] [unknown] LOG: connection received: host=[local]
2024-05-22 11:46:28.171 UTC [21258:2] [unknown] DEBUG: InitPostgres
2024-05-22 11:46:28.172 UTC [21258:3] [unknown] LOG: connection authenticated: user="vagrant" method=trust
(/pgtest/postgresql.git/src/test/modules/xid_wraparound/tmp_check/t_001_emergency_vacuum_main_data/pgdata/pg_hba.conf:117)
2024-05-22 11:46:28.172 UTC [21258:4] [unknown] LOG: connection authorized: user=vagrant database=postgres
application_name=001_emergency_vacuum.pl
2024-05-22 11:46:28.175 UTC [21258:5] 001_emergency_vacuum.pl LOG: statement: INSERT INTO small(data) SELECT 1
That is, autovacuum worker (21256) sent PMSIGNAL_START_AUTOVAC_LAUNCHER,
postmaster started autovacuum launcher, which could not start new
autovacuum worker due to the process 21256 not exited yet.
The failure can be reproduced easily with the sleep added inside
SetTransactionIdLimit():
if (TransactionIdFollowsOrEquals(curXid, xidVacLimit) &&
IsUnderPostmaster && !InRecovery)
SendPostmasterSignal(PMSIGNAL_START_AUTOVAC_LAUNCHER);
+pg_usleep(10000L);
By the way I also discovered that rather resource-intensive xid_wraparound
tests executed twice during the buildfarm "make" run (on dodo and perentie
(see [2]) animals), at stage module-xid_wraparound-check and then at stage
testmodules-install-check-C.
[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=dodo&dt=2024-05-19%2006%3A33%3A34
[2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=perentie&dt=2024-05-22%2000%3A02%3A19
Best regards,
Alexander
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2024-10-09 05:00 Alexander Lakhin <[email protected]>
parent: Masahiko Sawada <[email protected]>
0 siblings, 1 reply; 27+ messages in thread
From: Alexander Lakhin @ 2024-10-09 05:00 UTC (permalink / raw)
To: Masahiko Sawada <[email protected]>; +Cc: pgsql-hackers
Hello Masahiko-san,
01.12.2023 05:14, Masahiko Sawada wrote:
> FYI I've configured the buildfarm animal perentie to run regression
> tests including xid_wraparound:
>
> https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=perentie&br=HEAD
Please look at a failure produced by perentie recently: [1].
I've analyzed all the available detailed perentie logs (starting from
2024-04-04) and got the following durations of the pg_ctl stop operation
at the end of the 001_emergency_vacuum.pl (from the
module-xid_wraparound-check stage): see perentie-timings.txt and
perentie-timings.png attached. So it looks like perentie needs larger
PGCTLTIMEOUT for the test (maybe 180 seconds would work?).
Though it's not clear to me, why this test takes so long on that animal,
even when it succeeds. For example, [2] shows:
[09:28:23] t/001_emergency_vacuum.pl .. ok 225894 ms ( 0.00 usr 0.00 sys + 0.31 cusr 0.43 csys = 0.74 CPU)
[09:30:53] t/002_limits.pl ............ ok 150291 ms ( 0.00 usr 0.00 sys + 1.85 cusr 1.50 csys = 3.35 CPU)
[09:49:33] t/003_wraparounds.pl ....... ok 1119766 ms ( 0.00 usr 0.00 sys + 1.68 cusr 2.39 csys = 4.07 CPU)
While what I'm seeing locally on my Fedora 40 VM is:
PG_TEST_EXTRA="xid_wraparound" make -s check -C src/test/modules/xid_wraparound/ PROVE_FLAGS="--timer"
# +++ tap check in src/test/modules/xid_wraparound +++
[04:41:56] t/001_emergency_vacuum.pl .. ok 18852 ms ( 0.01 usr 0.00 sys + 0.14 cusr 0.28 csys = 0.43 CPU)
[04:42:15] t/002_limits.pl ............ ok 18539 ms ( 0.00 usr 0.00 sys + 0.72 cusr 0.88 csys = 1.60 CPU)
[04:42:34] t/003_wraparounds.pl ....... ok 74368 ms ( 0.00 usr 0.00 sys + 0.82 cusr 2.57 csys = 3.39 CPU)
Also, maybe it would make sense to run this test for REL_17_STABLE too, as
dodo is not with us since 2024-09-04, and I don't know if there are other
animals running these tests (having xid_wraparound in PG_TEST_EXTRA).
[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=perentie&dt=2024-10-05%2000%3A00%3A14
[2] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=perentie&dt=2024-10-06%2000%3A00%3A13
Best regards,
Alexander
dt=2024-04-04 00:02:21-waiting for server to shut down.......................................................... done
dt=2024-04-05 00:00:07-waiting for server to shut down............................ done
dt=2024-04-06 00:00:07-waiting for server to shut down................................................. done
dt=2024-04-07 00:00:05-waiting for server to shut down........................................................................................ done
dt=2024-04-08 00:02:47-waiting for server to shut down................................ done
dt=2024-04-09 00:02:43-waiting for server to shut down............................ done
dt=2024-04-10 00:00:05-waiting for server to shut down............................... done
dt=2024-04-11 00:00:06-waiting for server to shut down...... done
dt=2024-04-12 00:00:06-waiting for server to shut down.......................... done
dt=2024-04-13 00:00:07-waiting for server to shut down................................... done
dt=2024-04-14 00:00:06-waiting for server to shut down......................................... done
dt=2024-04-15 00:02:48-waiting for server to shut down............................. done
dt=2024-04-16 00:00:07-waiting for server to shut down................................... done
dt=2024-04-17 00:02:33-waiting for server to shut down....................................... done
dt=2024-04-18 00:00:06-waiting for server to shut down................................ done
dt=2024-04-19 00:00:05-waiting for server to shut down............................................................ done
dt=2024-04-20 00:00:06-waiting for server to shut down....................................................... done
dt=2024-04-21 00:00:05-waiting for server to shut down........................................................ done
dt=2024-04-22 00:00:05-waiting for server to shut down........................................... done
dt=2024-04-23 00:00:06-waiting for server to shut down.................................................... done
dt=2024-04-24 00:00:05-waiting for server to shut down........................................ done
dt=2024-04-25 00:02:47-waiting for server to shut down............................... done
dt=2024-04-26 00:00:06-waiting for server to shut down.............................. done
dt=2024-04-27 00:00:06-waiting for server to shut down....................................................................... done
dt=2024-04-28 00:00:05-waiting for server to shut down....................................... done
dt=2024-04-29 00:02:31-waiting for server to shut down............................. done
dt=2024-05-01 00:00:03-waiting for server to shut down............................... done
dt=2024-05-02 00:02:36-waiting for server to shut down......................................... done
dt=2024-05-03 00:00:05-waiting for server to shut down......................... done
dt=2024-05-04 00:00:06-waiting for server to shut down.................................. done
dt=2024-05-06 00:00:04-waiting for server to shut down.............................. done
dt=2024-05-07 00:00:05-waiting for server to shut down................................. done
dt=2024-05-08 00:00:06-waiting for server to shut down................................... done
dt=2024-05-10 00:00:04-waiting for server to shut down........................................ done
dt=2024-05-11 00:02:23-waiting for server to shut down...................................................... done
dt=2024-05-12 00:00:05-waiting for server to shut down....................................... done
dt=2024-05-13 00:00:06-waiting for server to shut down.......................................... done
dt=2024-05-14 00:02:27-waiting for server to shut down................................ done
dt=2024-05-15 00:00:05-waiting for server to shut down................................. done
dt=2024-05-16 00:00:06-waiting for server to shut down.................................................. done
dt=2024-05-17 00:00:06-waiting for server to shut down.............................................. done
dt=2024-05-18 00:02:33-waiting for server to shut down...................................... done
dt=2024-05-19 00:00:06-waiting for server to shut down....................................... done
dt=2024-05-21 00:00:04-waiting for server to shut down........................................ done
dt=2024-05-22 00:02:19-waiting for server to shut down....................................... done
dt=2024-05-23 00:00:07-waiting for server to shut down.................................. done
dt=2024-05-24 00:00:06-waiting for server to shut down.......................... done
dt=2024-05-25 00:00:05-waiting for server to shut down.................. done
dt=2024-05-27 00:00:04-waiting for server to shut down................................................................. done
dt=2024-05-28 00:00:06-waiting for server to shut down............................ done
dt=2024-06-04 00:00:04-waiting for server to shut down...................................... done
dt=2024-06-05 00:00:05-waiting for server to shut down.............................................. done
dt=2024-06-06 00:00:05-waiting for server to shut down................................. done
dt=2024-06-07 00:00:06-waiting for server to shut down....................... done
dt=2024-06-08 00:00:06-waiting for server to shut down........................ done
dt=2024-06-11 00:00:03-waiting for server to shut down....................................... done
dt=2024-06-12 00:00:06-waiting for server to shut down................................ done
dt=2024-06-13 00:00:06-waiting for server to shut down.................................. done
dt=2024-06-14 00:00:06-waiting for server to shut down......................................................... done
dt=2024-06-15 00:00:06-waiting for server to shut down.............................................. done
dt=2024-06-17 00:00:04-waiting for server to shut down............................ done
dt=2024-06-18 00:00:05-waiting for server to shut down.................................. done
dt=2024-06-19 00:00:08-waiting for server to shut down.............................. done
dt=2024-06-20 00:00:05-waiting for server to shut down................... done
dt=2024-06-21 00:00:05-waiting for server to shut down....................................... done
dt=2024-06-22 00:00:09-waiting for server to shut down.......................... done
dt=2024-06-23 00:00:05-waiting for server to shut down....................... done
dt=2024-06-25 00:00:05-waiting for server to shut down.................................. done
dt=2024-06-26 00:00:06-waiting for server to shut down........................................... done
dt=2024-06-27 00:00:06-waiting for server to shut down................................ done
dt=2024-06-28 00:00:06-waiting for server to shut down....................... done
dt=2024-06-29 00:00:06-waiting for server to shut down.......................................... done
dt=2024-07-01 00:00:04-waiting for server to shut down.............................................................................. done
dt=2024-07-01 00:00:04-waiting for server to shut down.............................................................................. done
dt=2024-07-02 00:00:06-waiting for server to shut down............................................... done
dt=2024-07-03 00:00:06-waiting for server to shut down.................................................................. done
dt=2024-07-04 00:00:06-waiting for server to shut down................................................. done
dt=2024-07-05 00:00:06-waiting for server to shut down......................... done
dt=2024-07-06 00:00:05-waiting for server to shut down.................................................................... done
dt=2024-07-07 00:00:06-waiting for server to shut down................................................... done
dt=2024-07-09 00:00:04-waiting for server to shut down............................... done
dt=2024-07-10 00:00:06-waiting for server to shut down.......................................... done
dt=2024-07-11 00:00:05-waiting for server to shut down........................................... done
dt=2024-07-12 00:00:06-waiting for server to shut down....................... done
dt=2024-07-13 00:00:05-waiting for server to shut down.............................. done
dt=2024-07-14 00:00:06-waiting for server to shut down.............................. done
dt=2024-07-15 00:00:05-waiting for server to shut down.............................................. done
dt=2024-07-16 00:00:05-waiting for server to shut down..................................... done
dt=2024-07-17 00:00:03-waiting for server to shut down.............................. done
dt=2024-07-18 00:00:04-waiting for server to shut down.............................. done
dt=2024-07-19 00:00:06-waiting for server to shut down.................................. done
dt=2024-07-20 00:00:06-waiting for server to shut down.............. done
dt=2024-07-21 00:00:06-waiting for server to shut down.......................... done
dt=2024-07-23 00:00:06-waiting for server to shut down..................................................................... done
dt=2024-07-24 00:00:06-waiting for server to shut down................................. done
dt=2024-07-25 00:00:11-waiting for server to shut down................................................................. done
dt=2024-07-26 00:00:05-waiting for server to shut down.............. done
dt=2024-07-27 00:00:03-waiting for server to shut down........................................... done
dt=2024-07-28 00:00:04-waiting for server to shut down............................................................ done
dt=2024-07-29 00:00:06-waiting for server to shut down........................................................................... done
dt=2024-07-30 00:00:07-waiting for server to shut down................................ done
dt=2024-07-31 00:00:06-waiting for server to shut down.................................... done
dt=2024-08-02 00:00:04-waiting for server to shut down.................................. done
dt=2024-08-03 00:00:05-waiting for server to shut down............................................................................... done
dt=2024-08-04 00:00:05-waiting for server to shut down............................. done
dt=2024-08-05 00:00:06-waiting for server to shut down.................................. done
dt=2024-08-06 00:00:05-waiting for server to shut down...................... done
dt=2024-08-07 00:00:05-waiting for server to shut down............................................................................... done
dt=2024-08-08 00:00:06-waiting for server to shut down........ done
dt=2024-08-09 00:00:04-waiting for server to shut down....................... done
dt=2024-08-10 00:00:03-waiting for server to shut down................................................................................. done
dt=2024-08-11 00:00:04-waiting for server to shut down.................................. done
dt=2024-08-12 00:00:07-waiting for server to shut down.................................... done
dt=2024-08-13 00:00:06-waiting for server to shut down...................................... done
dt=2024-08-14 00:00:05-waiting for server to shut down................................ done
dt=2024-08-15 00:00:07-waiting for server to shut down............................... done
dt=2024-08-16 00:00:07-waiting for server to shut down...................... done
dt=2024-08-17 00:00:06-waiting for server to shut down............................................ done
dt=2024-08-18 00:00:04-waiting for server to shut down............................. done
dt=2024-08-28 06:15:55-waiting for server to shut down................................................. done
dt=2024-09-03 00:00:14-waiting for server to shut down........................................................................ done
dt=2024-09-04 00:00:13-waiting for server to shut down...................... done
dt=2024-09-05 00:00:45-waiting for server to shut down........................... done
dt=2024-09-06 00:00:14-waiting for server to shut down............................... done
dt=2024-09-07 00:00:23-waiting for server to shut down...................................... done
dt=2024-09-08 00:01:08-waiting for server to shut down................................. done
dt=2024-09-09 00:00:15-waiting for server to shut down................................. done
dt=2024-09-10 00:00:21-waiting for server to shut down..................... done
dt=2024-09-11 00:00:13-waiting for server to shut down........................... done
dt=2024-09-12 00:00:14-waiting for server to shut down..................................... done
dt=2024-09-13 00:00:15-waiting for server to shut down....................................... done
dt=2024-09-14 00:00:12-waiting for server to shut down..................... done
dt=2024-09-15 00:00:10-waiting for server to shut down.................................... done
dt=2024-09-16 00:00:16-waiting for server to shut down.................................... done
dt=2024-09-17 00:00:15-waiting for server to shut down.................................... done
dt=2024-09-18 00:00:08-waiting for server to shut down........................................... done
dt=2024-09-19 00:00:14-waiting for server to shut down........................................ done
dt=2024-09-20 00:00:13-waiting for server to shut down................................. done
dt=2024-09-21 00:00:21-waiting for server to shut down...................................... done
dt=2024-09-22 00:00:13-waiting for server to shut down................................. done
dt=2024-09-24 00:00:14-waiting for server to shut down................................. done
dt=2024-09-25 00:00:11-waiting for server to shut down........................ done
dt=2024-09-26 00:00:15-waiting for server to shut down............................... done
dt=2024-09-27 00:00:16-waiting for server to shut down...................................... done
dt=2024-09-28 00:00:14-waiting for server to shut down...................... done
dt=2024-09-30 00:00:12-waiting for server to shut down........................................ done
dt=2024-10-01 00:00:14-waiting for server to shut down................................... done
dt=2024-10-02 00:00:13-waiting for server to shut down.................................... done
dt=2024-10-03 00:00:11-waiting for server to shut down...................................... done
dt=2024-10-04 00:00:12-waiting for server to shut down.......................... done
dt=2024-10-05 00:00:14-waiting for server to shut down........................................................................................................................... failed
dt=2024-10-06 00:00:13-waiting for server to shut down.......................................... done
dt=2024-10-07 00:00:14-waiting for server to shut down........................................ done
dt=2024-10-08 00:00:15-waiting for server to shut down............................ done
dt=2024-10-09 00:00:09-waiting for server to shut down............. done
Attachments:
[text/plain] perentie-timings.txt (14.9K, ../../[email protected]/2-perentie-timings.txt)
download | inline:
dt=2024-04-04 00:02:21-waiting for server to shut down.......................................................... done
dt=2024-04-05 00:00:07-waiting for server to shut down............................ done
dt=2024-04-06 00:00:07-waiting for server to shut down................................................. done
dt=2024-04-07 00:00:05-waiting for server to shut down........................................................................................ done
dt=2024-04-08 00:02:47-waiting for server to shut down................................ done
dt=2024-04-09 00:02:43-waiting for server to shut down............................ done
dt=2024-04-10 00:00:05-waiting for server to shut down............................... done
dt=2024-04-11 00:00:06-waiting for server to shut down...... done
dt=2024-04-12 00:00:06-waiting for server to shut down.......................... done
dt=2024-04-13 00:00:07-waiting for server to shut down................................... done
dt=2024-04-14 00:00:06-waiting for server to shut down......................................... done
dt=2024-04-15 00:02:48-waiting for server to shut down............................. done
dt=2024-04-16 00:00:07-waiting for server to shut down................................... done
dt=2024-04-17 00:02:33-waiting for server to shut down....................................... done
dt=2024-04-18 00:00:06-waiting for server to shut down................................ done
dt=2024-04-19 00:00:05-waiting for server to shut down............................................................ done
dt=2024-04-20 00:00:06-waiting for server to shut down....................................................... done
dt=2024-04-21 00:00:05-waiting for server to shut down........................................................ done
dt=2024-04-22 00:00:05-waiting for server to shut down........................................... done
dt=2024-04-23 00:00:06-waiting for server to shut down.................................................... done
dt=2024-04-24 00:00:05-waiting for server to shut down........................................ done
dt=2024-04-25 00:02:47-waiting for server to shut down............................... done
dt=2024-04-26 00:00:06-waiting for server to shut down.............................. done
dt=2024-04-27 00:00:06-waiting for server to shut down....................................................................... done
dt=2024-04-28 00:00:05-waiting for server to shut down....................................... done
dt=2024-04-29 00:02:31-waiting for server to shut down............................. done
dt=2024-05-01 00:00:03-waiting for server to shut down............................... done
dt=2024-05-02 00:02:36-waiting for server to shut down......................................... done
dt=2024-05-03 00:00:05-waiting for server to shut down......................... done
dt=2024-05-04 00:00:06-waiting for server to shut down.................................. done
dt=2024-05-06 00:00:04-waiting for server to shut down.............................. done
dt=2024-05-07 00:00:05-waiting for server to shut down................................. done
dt=2024-05-08 00:00:06-waiting for server to shut down................................... done
dt=2024-05-10 00:00:04-waiting for server to shut down........................................ done
dt=2024-05-11 00:02:23-waiting for server to shut down...................................................... done
dt=2024-05-12 00:00:05-waiting for server to shut down....................................... done
dt=2024-05-13 00:00:06-waiting for server to shut down.......................................... done
dt=2024-05-14 00:02:27-waiting for server to shut down................................ done
dt=2024-05-15 00:00:05-waiting for server to shut down................................. done
dt=2024-05-16 00:00:06-waiting for server to shut down.................................................. done
dt=2024-05-17 00:00:06-waiting for server to shut down.............................................. done
dt=2024-05-18 00:02:33-waiting for server to shut down...................................... done
dt=2024-05-19 00:00:06-waiting for server to shut down....................................... done
dt=2024-05-21 00:00:04-waiting for server to shut down........................................ done
dt=2024-05-22 00:02:19-waiting for server to shut down....................................... done
dt=2024-05-23 00:00:07-waiting for server to shut down.................................. done
dt=2024-05-24 00:00:06-waiting for server to shut down.......................... done
dt=2024-05-25 00:00:05-waiting for server to shut down.................. done
dt=2024-05-27 00:00:04-waiting for server to shut down................................................................. done
dt=2024-05-28 00:00:06-waiting for server to shut down............................ done
dt=2024-06-04 00:00:04-waiting for server to shut down...................................... done
dt=2024-06-05 00:00:05-waiting for server to shut down.............................................. done
dt=2024-06-06 00:00:05-waiting for server to shut down................................. done
dt=2024-06-07 00:00:06-waiting for server to shut down....................... done
dt=2024-06-08 00:00:06-waiting for server to shut down........................ done
dt=2024-06-11 00:00:03-waiting for server to shut down....................................... done
dt=2024-06-12 00:00:06-waiting for server to shut down................................ done
dt=2024-06-13 00:00:06-waiting for server to shut down.................................. done
dt=2024-06-14 00:00:06-waiting for server to shut down......................................................... done
dt=2024-06-15 00:00:06-waiting for server to shut down.............................................. done
dt=2024-06-17 00:00:04-waiting for server to shut down............................ done
dt=2024-06-18 00:00:05-waiting for server to shut down.................................. done
dt=2024-06-19 00:00:08-waiting for server to shut down.............................. done
dt=2024-06-20 00:00:05-waiting for server to shut down................... done
dt=2024-06-21 00:00:05-waiting for server to shut down....................................... done
dt=2024-06-22 00:00:09-waiting for server to shut down.......................... done
dt=2024-06-23 00:00:05-waiting for server to shut down....................... done
dt=2024-06-25 00:00:05-waiting for server to shut down.................................. done
dt=2024-06-26 00:00:06-waiting for server to shut down........................................... done
dt=2024-06-27 00:00:06-waiting for server to shut down................................ done
dt=2024-06-28 00:00:06-waiting for server to shut down....................... done
dt=2024-06-29 00:00:06-waiting for server to shut down.......................................... done
dt=2024-07-01 00:00:04-waiting for server to shut down.............................................................................. done
dt=2024-07-01 00:00:04-waiting for server to shut down.............................................................................. done
dt=2024-07-02 00:00:06-waiting for server to shut down............................................... done
dt=2024-07-03 00:00:06-waiting for server to shut down.................................................................. done
dt=2024-07-04 00:00:06-waiting for server to shut down................................................. done
dt=2024-07-05 00:00:06-waiting for server to shut down......................... done
dt=2024-07-06 00:00:05-waiting for server to shut down.................................................................... done
dt=2024-07-07 00:00:06-waiting for server to shut down................................................... done
dt=2024-07-09 00:00:04-waiting for server to shut down............................... done
dt=2024-07-10 00:00:06-waiting for server to shut down.......................................... done
dt=2024-07-11 00:00:05-waiting for server to shut down........................................... done
dt=2024-07-12 00:00:06-waiting for server to shut down....................... done
dt=2024-07-13 00:00:05-waiting for server to shut down.............................. done
dt=2024-07-14 00:00:06-waiting for server to shut down.............................. done
dt=2024-07-15 00:00:05-waiting for server to shut down.............................................. done
dt=2024-07-16 00:00:05-waiting for server to shut down..................................... done
dt=2024-07-17 00:00:03-waiting for server to shut down.............................. done
dt=2024-07-18 00:00:04-waiting for server to shut down.............................. done
dt=2024-07-19 00:00:06-waiting for server to shut down.................................. done
dt=2024-07-20 00:00:06-waiting for server to shut down.............. done
dt=2024-07-21 00:00:06-waiting for server to shut down.......................... done
dt=2024-07-23 00:00:06-waiting for server to shut down..................................................................... done
dt=2024-07-24 00:00:06-waiting for server to shut down................................. done
dt=2024-07-25 00:00:11-waiting for server to shut down................................................................. done
dt=2024-07-26 00:00:05-waiting for server to shut down.............. done
dt=2024-07-27 00:00:03-waiting for server to shut down........................................... done
dt=2024-07-28 00:00:04-waiting for server to shut down............................................................ done
dt=2024-07-29 00:00:06-waiting for server to shut down........................................................................... done
dt=2024-07-30 00:00:07-waiting for server to shut down................................ done
dt=2024-07-31 00:00:06-waiting for server to shut down.................................... done
dt=2024-08-02 00:00:04-waiting for server to shut down.................................. done
dt=2024-08-03 00:00:05-waiting for server to shut down............................................................................... done
dt=2024-08-04 00:00:05-waiting for server to shut down............................. done
dt=2024-08-05 00:00:06-waiting for server to shut down.................................. done
dt=2024-08-06 00:00:05-waiting for server to shut down...................... done
dt=2024-08-07 00:00:05-waiting for server to shut down............................................................................... done
dt=2024-08-08 00:00:06-waiting for server to shut down........ done
dt=2024-08-09 00:00:04-waiting for server to shut down....................... done
dt=2024-08-10 00:00:03-waiting for server to shut down................................................................................. done
dt=2024-08-11 00:00:04-waiting for server to shut down.................................. done
dt=2024-08-12 00:00:07-waiting for server to shut down.................................... done
dt=2024-08-13 00:00:06-waiting for server to shut down...................................... done
dt=2024-08-14 00:00:05-waiting for server to shut down................................ done
dt=2024-08-15 00:00:07-waiting for server to shut down............................... done
dt=2024-08-16 00:00:07-waiting for server to shut down...................... done
dt=2024-08-17 00:00:06-waiting for server to shut down............................................ done
dt=2024-08-18 00:00:04-waiting for server to shut down............................. done
dt=2024-08-28 06:15:55-waiting for server to shut down................................................. done
dt=2024-09-03 00:00:14-waiting for server to shut down........................................................................ done
dt=2024-09-04 00:00:13-waiting for server to shut down...................... done
dt=2024-09-05 00:00:45-waiting for server to shut down........................... done
dt=2024-09-06 00:00:14-waiting for server to shut down............................... done
dt=2024-09-07 00:00:23-waiting for server to shut down...................................... done
dt=2024-09-08 00:01:08-waiting for server to shut down................................. done
dt=2024-09-09 00:00:15-waiting for server to shut down................................. done
dt=2024-09-10 00:00:21-waiting for server to shut down..................... done
dt=2024-09-11 00:00:13-waiting for server to shut down........................... done
dt=2024-09-12 00:00:14-waiting for server to shut down..................................... done
dt=2024-09-13 00:00:15-waiting for server to shut down....................................... done
dt=2024-09-14 00:00:12-waiting for server to shut down..................... done
dt=2024-09-15 00:00:10-waiting for server to shut down.................................... done
dt=2024-09-16 00:00:16-waiting for server to shut down.................................... done
dt=2024-09-17 00:00:15-waiting for server to shut down.................................... done
dt=2024-09-18 00:00:08-waiting for server to shut down........................................... done
dt=2024-09-19 00:00:14-waiting for server to shut down........................................ done
dt=2024-09-20 00:00:13-waiting for server to shut down................................. done
dt=2024-09-21 00:00:21-waiting for server to shut down...................................... done
dt=2024-09-22 00:00:13-waiting for server to shut down................................. done
dt=2024-09-24 00:00:14-waiting for server to shut down................................. done
dt=2024-09-25 00:00:11-waiting for server to shut down........................ done
dt=2024-09-26 00:00:15-waiting for server to shut down............................... done
dt=2024-09-27 00:00:16-waiting for server to shut down...................................... done
dt=2024-09-28 00:00:14-waiting for server to shut down...................... done
dt=2024-09-30 00:00:12-waiting for server to shut down........................................ done
dt=2024-10-01 00:00:14-waiting for server to shut down................................... done
dt=2024-10-02 00:00:13-waiting for server to shut down.................................... done
dt=2024-10-03 00:00:11-waiting for server to shut down...................................... done
dt=2024-10-04 00:00:12-waiting for server to shut down.......................... done
dt=2024-10-05 00:00:14-waiting for server to shut down........................................................................................................................... failed
dt=2024-10-06 00:00:13-waiting for server to shut down.......................................... done
dt=2024-10-07 00:00:14-waiting for server to shut down........................................ done
dt=2024-10-08 00:00:15-waiting for server to shut down............................ done
dt=2024-10-09 00:00:09-waiting for server to shut down............. done
[application/gzip] perentie-timings.tar.gz (21.7K, ../../[email protected]/3-perentie-timings.tar.gz)
download
^ permalink raw reply [nested|flat] 27+ messages in thread
* Re: Testing autovacuum wraparound (including failsafe)
@ 2024-10-10 06:25 Masahiko Sawada <[email protected]>
parent: Alexander Lakhin <[email protected]>
0 siblings, 0 replies; 27+ messages in thread
From: Masahiko Sawada @ 2024-10-10 06:25 UTC (permalink / raw)
To: Alexander Lakhin <[email protected]>; +Cc: pgsql-hackers
Hi,
On Tue, Oct 8, 2024 at 10:00 PM Alexander Lakhin <[email protected]> wrote:
>
> Hello Masahiko-san,
>
> 01.12.2023 05:14, Masahiko Sawada wrote:
> > FYI I've configured the buildfarm animal perentie to run regression
> > tests including xid_wraparound:
> >
> > https://buildfarm.postgresql.org/cgi-bin/show_history.pl?nm=perentie&br=HEAD
>
> Please look at a failure produced by perentie recently: [1].
>
> I've analyzed all the available detailed perentie logs (starting from
> 2024-04-04) and got the following durations of the pg_ctl stop operation
> at the end of the 001_emergency_vacuum.pl (from the
> module-xid_wraparound-check stage): see perentie-timings.txt and
> perentie-timings.png attached. So it looks like perentie needs larger
> PGCTLTIMEOUT for the test (maybe 180 seconds would work?).
>
> Though it's not clear to me, why this test takes so long on that animal,
> even when it succeeds. For example, [2] shows:
> [09:28:23] t/001_emergency_vacuum.pl .. ok 225894 ms ( 0.00 usr 0.00 sys + 0.31 cusr 0.43 csys = 0.74 CPU)
> [09:30:53] t/002_limits.pl ............ ok 150291 ms ( 0.00 usr 0.00 sys + 1.85 cusr 1.50 csys = 3.35 CPU)
> [09:49:33] t/003_wraparounds.pl ....... ok 1119766 ms ( 0.00 usr 0.00 sys + 1.68 cusr 2.39 csys = 4.07 CPU)
>
> While what I'm seeing locally on my Fedora 40 VM is:
> PG_TEST_EXTRA="xid_wraparound" make -s check -C src/test/modules/xid_wraparound/ PROVE_FLAGS="--timer"
> # +++ tap check in src/test/modules/xid_wraparound +++
> [04:41:56] t/001_emergency_vacuum.pl .. ok 18852 ms ( 0.01 usr 0.00 sys + 0.14 cusr 0.28 csys = 0.43 CPU)
> [04:42:15] t/002_limits.pl ............ ok 18539 ms ( 0.00 usr 0.00 sys + 0.72 cusr 0.88 csys = 1.60 CPU)
> [04:42:34] t/003_wraparounds.pl ....... ok 74368 ms ( 0.00 usr 0.00 sys + 0.82 cusr 2.57 csys = 3.39 CPU)
Thank you for reporting it.
I've investigated the logs you shared and figured out that some other
(personal) tests were overlapped at that time (perentie is my
machine). So the failure should be ignored, sorry for making noise.
I'll make sure other tests won't be overlapped again when perentie is
executing the regression tests.
> Also, maybe it would make sense to run this test for REL_17_STABLE too, as
> dodo is not with us since 2024-09-04, and I don't know if there are other
> animals running these tests (having xid_wraparound in PG_TEST_EXTRA).
Good idea. I've configured perentie to do that.
Regards,
--
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com
^ permalink raw reply [nested|flat] 27+ messages in thread
end of thread, other threads:[~2024-10-10 06:25 UTC | newest]
Thread overview: 27+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-09-04 06:31 [PATCH v5 3/4] Allow granting VACUUM and ANALYZE privileges on relations. Nathan Bossart <[email protected]>
2023-04-27 14:06 Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2023-04-27 14:12 ` Re: Testing autovacuum wraparound (including failsafe) Daniel Gustafsson <[email protected]>
2023-04-28 03:21 ` Re: Testing autovacuum wraparound (including failsafe) John Naylor <[email protected]>
2023-04-28 04:42 ` Re: Testing autovacuum wraparound (including failsafe) Tom Lane <[email protected]>
2023-07-12 07:52 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2023-07-12 11:47 ` Re: Testing autovacuum wraparound (including failsafe) Daniel Gustafsson <[email protected]>
2023-08-22 05:49 ` Re: Testing autovacuum wraparound (including failsafe) Michael Paquier <[email protected]>
2023-09-03 05:48 ` Re: Testing autovacuum wraparound (including failsafe) Noah Misch <[email protected]>
2023-09-27 12:39 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2023-09-29 10:17 ` Re: Testing autovacuum wraparound (including failsafe) Daniel Gustafsson <[email protected]>
2023-09-29 13:57 ` Re: Testing autovacuum wraparound (including failsafe) Noah Misch <[email protected]>
2023-11-27 13:06 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2023-11-27 13:40 ` Re: Testing autovacuum wraparound (including failsafe) Daniel Gustafsson <[email protected]>
2023-11-28 02:00 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2023-11-28 10:16 ` Re: Testing autovacuum wraparound (including failsafe) Daniel Gustafsson <[email protected]>
2023-11-28 20:27 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2023-11-30 07:35 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2023-12-01 02:14 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2024-10-09 05:00 ` Re: Testing autovacuum wraparound (including failsafe) Alexander Lakhin <[email protected]>
2024-10-10 06:25 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2024-02-07 18:11 ` Re: Testing autovacuum wraparound (including failsafe) Peter Eisentraut <[email protected]>
2024-02-08 04:05 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2024-02-08 07:06 ` Re: Testing autovacuum wraparound (including failsafe) Peter Eisentraut <[email protected]>
2024-02-08 08:06 ` Re: Testing autovacuum wraparound (including failsafe) Masahiko Sawada <[email protected]>
2024-05-22 12:00 ` Re: Testing autovacuum wraparound (including failsafe) Alexander Lakhin <[email protected]>
2023-09-29 10:19 ` Re: Testing autovacuum wraparound (including failsafe) vignesh C <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox