Re: Temporary tables versus wraparound... again

Hi,

On 2022-03-28 16:11:55 -0400, Greg Stark wrote:
> From 4515075b644d1e38920eb5bdaaa898e1698510a8 Mon Sep 17 00:00:00 2001
> From: Greg Stark <stark(at)mit(dot)edu>
> Date: Tue, 22 Mar 2022 15:51:32 -0400
> Subject: [PATCH v4 1/2] Update relfrozenxmin when truncating temp tables
>
> Make ON COMMIT DELETE ROWS reset relfrozenxmin and other table stats
> like normal truncate. Otherwise even typical short-lived transactions
> using temporary tables can easily cause them to reach relfrozenxid.

Might be worth mentioning that ON COMMIT DELETE is implemented as truncating
tables. If we actually implemented it as deleting rows, it'd not at all be
correct to reset relfrozenxmin.

> Also add warnings when old temporary tables are found to still be in
> use during autovacuum. Long lived sessions using temporary tables are
> required to vacuum them themselves.

I'd do that in a separate patch.

> +/*
> + * Reset the relfrozenxid and other stats to the same values used when
> + * creating tables. This is used after non-transactional truncation.
> + *
> + * This reduces the need for long-running programs to vacuum their own
> + * temporary tables (since they're not covered by autovacuum) at least in the
> + * case where they're ON COMMIT DELETE ROWS.
> + *
> + * see also src/backend/commands/vacuum.c vac_update_relstats()
> + * also see AddNewRelationTuple() above
> + */
> +
> +static void
> +ResetVacStats(Relation rel)
> +{
> + HeapTuple ctup;
> + Form_pg_class pgcform;
> + Relation classRel;
> +
> + /* Fetch a copy of the tuple to scribble on */
> + classRel = table_open(RelationRelationId, RowExclusiveLock);
> + ctup = SearchSysCacheCopy1(RELOID, ObjectIdGetDatum(RelationGetRelid(rel)));
>
> + if (!HeapTupleIsValid(ctup))
> + elog(ERROR, "pg_class entry for relid %u vanished during truncation",
> + RelationGetRelid(rel));
> + pgcform = (Form_pg_class) GETSTRUCT(ctup);
> +
> + /*
> + * Update relfrozenxid
> + */
> +
> + pgcform->relpages = 0;
> + pgcform->reltuples = -1;
> + pgcform->relallvisible = 0;
> + pgcform->relfrozenxid = RecentXmin;

Hm. Is RecentXmin guaranteed to be valid at this point?

> + pgcform->relminmxid = GetOldestMultiXactId();

Ugh. That's pretty expensive for something now done at a much higher rate than
before.

> @@ -2113,20 +2126,31 @@ do_autovacuum(void)
> * Remember it so we can try to delete it later.
> */
> orphan_oids = lappend_oid(orphan_oids, relid);
> + } else if (temp_status == TEMP_NAMESPACE_NOT_TEMP) {
> + elog(LOG, "autovacuum: found temporary table \"%s.%s.%s\" in non-temporary namespace",
> + get_database_name(MyDatabaseId),
> + get_namespace_name(classForm->relnamespace),
> + NameStr(classForm->relname));
> + } else if (temp_status == TEMP_NAMESPACE_IN_USE && wraparound) {

we put else if on a separate line from }. And { also is always on a separate
line.

Greetings,

Andres Freund