Re: Temporary table has become problematically persistent

Ricky Ramirez <ricky@reddit.com> writes:

> Hello,
>
> We have run into a strange situation with our database. A temporary
> table was created some time ago and that session has since died.
> However, the temporary table stuck around. It's been around long
> enough that postgres stopped accepting writes to prevent
> transaction ID wraparound. Problem is, it's a temporary table and the
> session that it's associated with is gone, so we cannot vacuum it nor
> even drop the table. We even restarted postgres and the temporary
> table was still there!

What happened when you tried to drop the temp table?

>
> The table shows up in pg_class as a temporary table with zero tuples.
> The table replicated over to other hosts. We were able to promote a
> replica and then single user mode the replica and drop the offending
> table. I still have the original broken database available for
> debugging. Can I provide any additional debugging information?
>
> Thanks,
>
> Ricky
>
>

--
Jerry Sievers
Postgres DBA/Development Consulting
e: postgres.consulting@comcast.net