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