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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your Subscription: http://mail.postgresql.org/mj/mj_wwwusr?domain=postgresql.org&extra=pgsql-hackers
