Re: "could not open relation 1663/16384/16584: No such file or directory" in a specific combination of transactions with temp tables

John Smith wrote:
> BTW, I found a easier way of reproducing this (see attached 2pc.sql).
> It might help with debugging or verifying a fix/regression.

Thanks.

> [1] The data file is reported missing in the second transaction only
> if the first transaction was ended using PREPARE TRANSACTION. The
> error does not show up if a direct COMMIT is performed (commit.sql)
> instead of PREPARE TRANSACTION + COMMIT PREPARED. Why is that so?

On normal COMMIT, all buffers related to the table are flushed from the
buffer cache, and the file is deleted. On PREPARE TRANSACTION, the
buffers and the file are not immediately dropped, but the relfilenode (=
filename) of the file is stored in the two-phase state file. On COMMIT
PREPARED, the state file is read, the buffers are dropped and the file
is deleted.

Temporary tables don't use the shared buffer cache, but a backend-local
buffer cache. In PREPARE TRANSACTION, we don't make any note of which
tables are temporary, because there shouldn't be any, because we
should've aborted if you have operated on temporary tables. But as we
found out, that check in the lock manager isn't working. Therefore when
COMMIT PREPARED is run, we delete the file, but don't flush the buffers
from the backend-local temporary buffer cache. The leftover buffers
cause the "relation not found" error later on, when we try to flush them
to disk to make room in the cache for other pages.

> [2] From all of the discussion here since my first post, I understand
> that there are complications for session-level TEMP tables. But is it
> easier to support PREPARE TRANSACTION for transactions that create and
> drop their TEMP tables, i.e., so that the tables are not session-level
> but just transaction-level?

Yes, if the table is created and dropped in the same transaction, that
avoids many of the problems. I think we could get away with dropping the
buffers, deleting the file, and releasing locks on it immediately at
PREPARE TRANSACTION in that case. It wouldn't behave exactly the same as
a normal transaction, though. The lock would be released early, which
would allow another transaction to create a table with the same name
before the transaction has been committed/rolled back.

ON COMMIT DELETE ROWS could be handled like that as well.

> [3] I am not certain how widespread they might be, but I think there
> may be some backward compatibility concerns with the patch you are
> proposing. On the one hand, the documentation says, "It is not
> currently allowed to PREPARE a transaction that has executed any
> operations involving temporary tables or created any cursors WITH
> HOLD." But temporary tables that are created ON COMMIT DROP are more
> like cursors that do not have WITH HOLD specified. So it does not seem
> clear from the documentation that PREPARE TRANSACTION is not
> supported, and indeed due to the lack of a check in Postgres today, it
> seems as though it is supported. Do you think there is a risk in
> breaking applications?

Well, the current behavior is certainly broken, so an application
relying on it is in trouble anyway :-(. Even if we came up with a patch
for 8.4 to relax the limitation, I doubt it would be safe enough to
backport to stable branches.

Is your application relying on this? As a workaround, you could use
non-temporary tables instead.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com