0

The latest days, Postgres has started to log very frequently (about 100 logs per second!!!!) this kind of traces:

autovacuum: found orphan temp table "pg_temp_5"."p14528" in database "ma_prc_lim_absa_z5"

Is this a real problem? If it isn't, how can I filter them?

2

It's a temporary table that wasn't removed automatically when it should have.

The fix from the point of view of a DBA is to simply drop the table manually.

There are several references to this problem on mailing lists. See for instance
[bug fix] Suppress "autovacuum: found orphan temp table" message for a discussion on this subject.

1

Thanks for the answer. Here is a link that helped me in that case: ​https://community.synopsys.com/s/article/Postgresql-log-LOG-autovacuum-found-orphan-temp-table-pg-temp-xxx-loblist-in-database-cim

Detailed answer copied from this Source at community.synopsys.com: Possible Reasons from PostgreSQL Community

  1. The applications were using temporary tables. The rows for temporary tables were created in pg_namespace (one row for pg_temp_n) and pg_class. Those rows were replicated to the standby. The data files for the temporary tables were not replicated.
  2. The server crashed the standby was promoted to the primary.
  3. The new primary performed recovery, but the rows for temporary tables in the system catalog were left.
  4. The applications resumed processing. However, the workload got lighter, so the zombie pg_temp_n entries were not recycled.
  5. autovacuum workers found the zombie temporary table entries in the system catalog, repeatedly emitting lots of messages.

And the very helpful SQL-Query that they provided at community.synopsys.com:

Query to find those schemas :

select relname,nspname from pg_class 
join pg_namespace on (relnamespace=pg_namespace.oid) 
where pg_is_other_temp_schema(relnamespace);

On finding you can drop those schemas, if you want to get rid of the messages, just do:

 DROP SCHEMA pg_temp_NNN CASCADE;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.