Thread: Temporary table has become problematically persistent
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!
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
Ricky Ramirez <ricky@reddit.com> writes:
> 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!
Hm, what PG version is this? I thought we'd fixed that hazard quite some
while back.
regards, tom lane
Forgive me for forgetting the basics PG 11.2 on Ubuntu Bionic, amd64. No extensions enabled.
On Mon, May 4, 2020 at 4:54 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Ricky Ramirez <ricky@reddit.com> writes:
> 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!
Hm, what PG version is this? I thought we'd fixed that hazard quite some
while back.
regards, tom lane
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
We couldn't drop the table in the normal setup because of the wraparound protection measures. Specifically: "ERROR: database is not accepting commands to avoid wraparound data loss in database"
We also tried vacuuming the table. The vacuum didn't generate an error (the wraparound warning was printed), but nothing appeared to change from that. Vacuuming the full database would have taken prohibitively long so we never completed that operation.
On Mon, May 4, 2020 at 6:17 PM Jerry Sievers <gsievers19@comcast.net> wrote:
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