5 ms·
There are more surprises around Postgres XID to be had: - Temp tables cannot be vacuumed by anyone else than connection that owns the table (no way around it)
by limaoscarjuliet 10y ago
There are more surprises around Postgres XID to be had:
- Temp tables cannot be vacuumed by anyone else than connection that owns the table (no way around it) - do not keep temp tables too long, nightly vacuum or auto vacuum will not clear these,
- Temp tables do not get automatically dropped on recovery if your DB cluster crashes. They get reused but if you have many connections before crash and few after some namespaces will linger - you may need to manually cascade drop temp namespaces if you see the warning,
- Full vacuum does not touch XIDs - use freeze or just normal vacuum,
- Check oldest XID per table:
SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind = 'r'
ORDER by age DESC
LIMIT %s
- Check oldest XID per db:
select datname db, age(datfrozenxid) FROM pg_database ORDER BY age DESC
- the_mitsuhiko 10y agoAre you sure the leftover temp table thing still exists? I have been using postgres for development on a project with temp tables and even though of being rough with postgres (killing, starting laptop in not so nice ways etc.) i can't find any leftovers.
- limaoscarjuliet 10y agoLeftovers will happen only when number of used temp namespaces before crash is larger than number used after crash. Then not all temp namespaces get reused and, as a result, reset after recovery. The leftovers then keep the XID around forever.
- anarazel 10y agoThat doesn't sound right: static void do_autovacuum(void) { ... if (classForm->relpersistence == RELPERSISTENCE_TEMP) { int backendID; backendID = GetTempNamespaceBackendId(classForm->relnamespace); /* We just ignore it if the owning backend is still active */ if (backendID == MyBackendId || BackendIdGetProc(backendID) == NULL) { /* * We found an orphan temp table (which was probably left * behind by a crashed backend). If it's so old as to need * vacuum for wraparound, forcibly drop it. Otherwise just * log a complaint. */ if (wraparound) { ObjectAddress object; ereport(LOG, (errmsg("autovacuum: dropping orphan temp table \"%s\".\"%s\" in database \"%s\"", get_namespace_name(classForm->relnamespace), NameStr(classForm->relname), get_database_name(MyDatabaseId)))); object.classId = RelationRelationId; object.objectId = relid; object.objectSubId = 0; performDeletion(&object, DROP_CASCADE, PERFORM_DELETION_INTERNAL); } else { ereport(LOG, (errmsg("autovacuum: found orphan temp table \"%s\".\"%s\" in database \"%s\"", get_namespace_name(classForm->relnamespace), NameStr(classForm->relname), get_database_name(MyDatabaseId)))); } }
- limaoscarjuliet 10y agoAye, my stuff was from 9.1, seen it with my own eyes. I'm glad it improved since!
- anarazel 10y agoIt's pretty much the same in 9.1.
- radiowave 10y agoAt a slight tangent, I don't think I've ever used a temporary table without living to regret it. - Queries that perform complex operations involving the creation of temp tables cannot be joined onto themselves[1]. - Operations that create temp tables can never be used as part of the definition of a materialized view[1]. This is a permissions issue, but not one that can be resolved by altering permissions. It's baked in. - Confusion over how to test for the existance of a temp table, "it's in the catalog but it's not in my session". [1] - Without resorting to dblink shenanigans, or such like.
- mjevans 10y agoHave you had better experiences when you modify the design to use an UNLOGGED table and update such a table within a transaction? (I assume most of the use cases are as a cache for data that actually exists elsewhere)
- radiowave 10y agoYes. That's what I tend to end up replacing the temp tables with. It can be a bit fiddly to design, but I haven't had any operational problems arising from it. In some of my cases, the temporary data is some kind of projection of future stock requirements, arising from a user doing a "what if" operation. Since multiple users could be doing this, the replacement unlogged non-temporary table needs to be "multi-tennant", which involves an extra column (usually called "invocation_id"), and a sequence, such that each invocation of the operation gets a new invocation_id value, which it uses in all of the rows it then generates. The extra complexity is ensuring the invocation_id gets passed around to all of the functions that are operating on the data, including the clean-up when we're done with it.
- anarazel 10y ago> - Full vacuum does not touch XIDs - use freeze or just normal vacuum, Don't think that's right. copy_heap_data(Oid OIDNewHeap, Oid OIDOldHeap, Oid OIDOldIndex, bool verbose, bool *pSwapToastByContent, TransactionId *pFreezeXid, MultiXactId *pCutoffMulti) ... /* * Compute xids used to freeze and weed out dead tuples and multixacts. * Since we're going to rewrite the whole table anyway, there's no reason * not to be aggressive about this. */ vacuum_set_xid_limits(OldHeap, 0, 0, 0, 0, &OldestXmin, &FreezeXid, NULL, &MultiXactCutoff, NULL); /* * FreezeXid will become the table's new relfrozenxid, and that mustn't go * backwards, so take the max. */ if (TransactionIdPrecedes(FreezeXid, OldHeap->rd_rel->relfrozenxid)) FreezeXid = OldHeap->rd_rel->relfrozenxid; i.e. the cutoff is computed in copy_heap_data(). And then in void rewrite_heap_tuple(RewriteState state, HeapTuple old_tuple, HeapTuple new_tuple) { ... /* * While we have our hands on the tuple, we may as well freeze any * eligible xmin or xmax, so that future VACUUM effort can be saved. */ heap_freeze_tuple(new_tuple->t_data, state->rs_freeze_xid, state->rs_cutoff_multi);
- limaoscarjuliet 10y agoI'm speaking from experience with 9.1, I perhaps this has changed since (5+ years of dev!). I ran an experiment to demonstrate no XID reset on vacuum full: I ran a test: XID age in callback 354015 (0% of max) task=# vacuum full callback; XID age in callback 354015 (0% of max) task=# vacuum freeze callback; XID age in callback 11 (0% of max)
- anarazel 10y agoYea, but that's not the same as not freezing at all - it freezes stuff older than autovacuum_freeze_min_age.