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

Gurjeet Singh wrote:
> Plausible theory, and nice explanation....
>
> Try the following link (I had to wait for 50 sec for the link to appear, but
> I guess the trade-off of getting knowledge in return is worth it :) )
>
> http://www5.upload2.net/download/77fa86e16a02e52fd5439c76e148d231/47c7fdce/rfsLfnuVlYjEcCJ/basetables.tgz
>
> Not sending attachment in this mail; that may cause the mail to be not
> accepted by the list. I will try to send the attachment in the next mail, to
> retain it in the mailing list for historica purposes.

Thanks!

As I suspected, what's happening is that buffers for the dropped temp
table are not cleaned up from the temporary buffer cache as they should
be. The next time the buffers are needed, on the next COPY, we try to
write out the buffers make room for new pages, but that fails because
the file the buffers are related to doesn't exist anymore.

The patch I sent earlier fixes that, by tightening the check that you
can't operate on temporary tables on 2pc transactions.

If you had a real-world use case for that, sorry :-(. Perhaps we could
enhance that for 8.4 if there's demand, so that you could CREATE+DROP or
use ON COMMIT TRUNCATE temp tables in a transaction, though I haven't
personally planned to work on it.

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