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

Plausible theory, and nice explanation....<br /><br />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 :) )<br /><br /><a
href="http://www5.upload2.net/download/77fa86e16a02e52fd5439c76e148d231/47c7fdce/rfsLfnuVlYjEcCJ/basetables.tgz">http://www5.upload2.net/download/77fa86e16a02e52fd5439c76e148d231/47c7fdce/rfsLfnuVlYjEcCJ/basetables.tgz</a><br
/><br/>Not sending attachment in this mail; that may cause the mail to be not accepted by the list. I will try to send
theattachment in the next mail, to retain it in the mailing list for historica purposes.<br /><br />Thanks and best
regards,<br/> -- <br />gurjeet[.singh]@EnterpriseDB.com<br />singh.gurjeet@{ gmail | hotmail | indiatimes | yahoo
}.com<br/><br />EnterpriseDB      <a href="http://www.enterprisedb.com">http://www.enterprisedb.com</a><br /><br />17°
29'34.37"N,  78° 30' 59.76"E - Hyderabad<br /> 18° 32' 57.25"N,  73° 56' 25.42"E - Pune *<br />37° 47' 19.72"N, 122°
24'1.69" W - San Francisco<br /><br /><a href="http://gurjeet.frihost.net">http://gurjeet.frihost.net</a><br /><br
/>Mailsent from my BlackLaptop device <br /><br /><div class="gmail_quote">On Fri, Feb 29, 2008 at 3:32 PM, Heikki
Linnakangas<<a href="mailto:heikki@enterprisedb.com">heikki@enterprisedb.com</a>> wrote:<br /><blockquote
class="gmail_quote"style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left:
1ex;"><divclass="Ih2E3d">John Smith wrote:<br /> > Architecture: Intel Core 2 Duo<br /> > OS:
linux-2.6.20-gentoo-r8<br/> > Filesystem: ext3<br /> > Postgres v8.2.3 compiled with gcc 4.1.1-r3<br /> > RAM
-2GB<br /> > Shared buffers - 24MB<br /> > [All other Postgres configuration parameters are default values]<br />
><br/> > Problem description:<br /> > COPY into temp table fails using a specific combination of<br /> >
create/inserton temp tables, prepare/commit in subsequent<br /> > transactions. The "could not open relation" error
occursreliably.<br /> ><br /> > Steps to reproduce:<br /> ><br /> > Existing schema (scripts to create and
populatethese tables are<br /> > uploaded to <a
href="http://upload2.net/page/download/rfsLfnuVlYjEcCJ/basetables.tgz.html"
target="_blank">http://upload2.net/page/download/rfsLfnuVlYjEcCJ/basetables.tgz.html</a><br/> > ):<br /><br
/></div>Ican't get that link to work. Can you please email me the files<br /> offlist? Or upload somewhere else if
they'retoo big for email.<br /><div class="Ih2E3d"><br /> > Observations:<br /> > 1. The size of the data seems
tomatters. If the amount of data being<br /> > inserted is dropped to just one or two records per table, the
error<br/> > doesn't happen.<br /> > 2. The order of columns for the select into temp2 matters. Changing<br />
>the order can cause the error to go away.<br /> > 3. If the prepare/commit is replaced with a "commit;" the
errorgoes away.<br /> > 4. Removing "temp3" or "temp4" from the transaction causes one run of<br /> > the above
statementsto succeed, but if the sequence is issued in the<br /> > same PSQL session, the second one will fail.<br
/>> 5. Given the current dataset, the error always occurs on line 926 of<br /> > the COPY (even if the values at
line926 are changed).<br /> > 6. <tablespace>/<database>/<oid> typically always corresponds to
that<br/> > of temp2 on my system.<br /><br /></div>I think I see what's happening here. We have restricted
two-phasecommit<br /> so that you're not supposed to be able to PREPARE TRANSACTION if the<br /> transaction has
touchedany temporary tables. That's because the 2nd<br /> phase commit can be performed from another backend, and
anotherbackend<br /> can't mess with another backend's temporary tables.<br /><br /> However in this case, where you
CREATEand DROP the temporary table in<br /> the same transaction, we don't detect that, and let the PREPARE<br />
TRANSACTIONto finish. The detection relies on the lock manager, but<br /> we're not holding any locks on the dropped
relation.<br/><br /> I think we could in fact allow CREATE+DROP in same transaction, and<br /> remove the table
immediatelyat PREPARE TRANSACTION, but what happens<br /> right now is that we store the relfilenode of the temp table
tothe<br /> two-phase state file in pg_twophase, for deletion at COMMIT/ROLLBACK<br /> PREPARED. But we don't store the
factthat it's a temporary table, and<br /> therefore we try to unlink it like a normal table, and fail to purge the<br
/>temp buffers of that table which causes problems later.<br /><br /> Attached is a simple patch to fix that by
disallowing<br/> CREATE+DROP+PREPARE TRANSACTION more reliably. It'd still be nice to<br /> debug the full test case of
yoursto verify that that's what's<br /> happening, though.<br /><font color="#888888"><br /> --<br />   Heikki
Linnakangas<br/>   EnterpriseDB   <a href="http://www.enterprisedb.com"
target="_blank">http://www.enterprisedb.com</a><br/></font><br /><br /> ---------------------------(end of
broadcast)---------------------------<br/> TIP 3: Have you checked our extensive FAQ?<br /><br />               <a
href="http://www.postgresql.org/docs/faq"target="_blank">http://www.postgresql.org/docs/faq</a><br /><br
/></blockquote></div><br/><br />