Postgres Pro
Downloads
Home   >   mailing lists

Thread: autovacuum: found orphan temp table

autovacuum: found orphan temp table

From
Nicolau Roca
Date:
Hi,
after a server crash the following messages appear in the log file every minute:

2013-06-25 15:02:15 CEST [::18264:1:] LOG:  autovacuum: found orphan temp table "pg_temp_47"."est_backup_ids_temp" in database "estudis1314"
2013-06-25 15:02:15 CEST [::18264:2:] LOG:  autovacuum: found orphan temp table "pg_temp_47"."est_backup_files_temp" in database "estudis1314"

I read a suggestion in the list pgsql-hackers (Message ID 48F4599D.7010601@enterprisedb.com) about just dropping the pg_temp_x schema. However, no such schema exists:

[postgres@postgresql1 ~]$ psql
psql (9.2.4)
Type "help" for help.

(postgres@[local]:5432) [postgres] > \dnS
        List of schemas
        Name        |  Owner
--------------------+----------
 information_schema | postgres
 pg_catalog         | postgres
 pg_temp_1          | postgres
 pg_toast           | postgres
 pg_toast_temp_1    | postgres
 pgpool_catalog     | postgres
 public             | postgres
(7 rows)

So what could be done in order to get rid of the message?

Regards,
Nicolau

Re: autovacuum: found orphan temp table

From
Bosco Rama
Date:
On 06/25/13 06:13, Nicolau Roca wrote:
>
> 2013-06-25 15:02:15 CEST [::18264:1:] LOG:  autovacuum: found orphan
> temp table "pg_temp_47"."est_backup_ids_temp" in database "estudis1314"
> 2013-06-25 15:02:15 CEST [::18264:2:] LOG:  autovacuum: found orphan
> temp table "pg_temp_47"."est_backup_files_temp" in database "estudis1314"
>
> I read a suggestion in the list pgsql-hackers (Message ID
> 48F4599D.7010601@enterprisedb.com
> <http://www.postgresql.org/message-id/48F4599D.7010601@enterprisedb.com>) about
> just dropping the pg_temp_x schema. However, no such schema exists:

Try looking in the "estudis1314" database:
  [postgres@postgresql1 ~]$ psql -d estudis1314


HTH,
Bosco.

Re: autovacuum: found orphan temp table

From
Raghavendra
Date:

On Tue, Jun 25, 2013 at 6:43 PM, Nicolau Roca <nicolau.roca@uib.cat> wrote:

Hi,
after a server crash the following messages appear in the log file every minute:
 
2013-06-25 15:02:15 CEST [::18264:1:] LOG:  autovacuum: found orphan temp table "pg_temp_47"."est_backup_ids_temp" in database "estudis1314"
2013-06-25 15:02:15 CEST [::18264:2:] LOG:  autovacuum: found orphan temp table "pg_temp_47"."est_backup_files_temp" in database "estudis1314"

I read a suggestion in the list pgsql-hackers (Message ID 48F4599D.7010601@enterprisedb.com) about just dropping the pg_temp_x schema. However, no such schema exists:

You can 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;

---
Regards,
Raghavendra
EnterpriseDB Corporation