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 and best regards, -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37"N, 78° 30' 59.76"E - Hyderabad 18° 32' 57.25"N, 73° 56' 25.42"E - Pune * 37° 47' 19.72"N, 122° 24' 1.69" W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device On Fri, Feb 29, 2008 at 3:32 PM, Heikki Linnakangas <[EMAIL PROTECTED]> wrote: > John Smith wrote: > > Architecture: Intel Core 2 Duo > > OS: linux-2.6.20-gentoo-r8 > > Filesystem: ext3 > > Postgres v8.2.3 compiled with gcc 4.1.1-r3 > > RAM - 2GB > > Shared buffers - 24MB > > [All other Postgres configuration parameters are default values] > > > > Problem description: > > COPY into temp table fails using a specific combination of > > create/insert on temp tables, prepare/commit in subsequent > > transactions. The "could not open relation" error occurs reliably. > > > > Steps to reproduce: > > > > Existing schema (scripts to create and populate these tables are > > uploaded to > http://upload2.net/page/download/rfsLfnuVlYjEcCJ/basetables.tgz.html > > ): > > I can't get that link to work. Can you please email me the files > offlist? Or upload somewhere else if they're too big for email. > > > Observations: > > 1. The size of the data seems to matters. If the amount of data being > > inserted is dropped to just one or two records per table, the error > > doesn't happen. > > 2. The order of columns for the select into temp2 matters. Changing > > the order can cause the error to go away. > > 3. If the prepare/commit is replaced with a "commit;" the error goes > away. > > 4. Removing "temp3" or "temp4" from the transaction causes one run of > > the above statements to succeed, but if the sequence is issued in the > > same PSQL session, the second one will fail. > > 5. Given the current dataset, the error always occurs on line 926 of > > the COPY (even if the values at line 926 are changed). > > 6. <tablespace>/<database>/<oid> typically always corresponds to that > > of temp2 on my system. > > I think I see what's happening here. We have restricted two-phase commit > so that you're not supposed to be able to PREPARE TRANSACTION if the > transaction has touched any temporary tables. That's because the 2nd > phase commit can be performed from another backend, and another backend > can't mess with another backend's temporary tables. > > However in this case, where you CREATE and DROP the temporary table in > the same transaction, we don't detect that, and let the PREPARE > TRANSACTION to finish. The detection relies on the lock manager, but > we're not holding any locks on the dropped relation. > > I think we could in fact allow CREATE+DROP in same transaction, and > remove the table immediately at PREPARE TRANSACTION, but what happens > right now is that we store the relfilenode of the temp table to the > two-phase state file in pg_twophase, for deletion at COMMIT/ROLLBACK > PREPARED. But we don't store the fact that it's a temporary table, and > therefore we try to unlink it like a normal table, and fail to purge the > temp buffers of that table which causes problems later. > > Attached is a simple patch to fix that by disallowing > CREATE+DROP+PREPARE TRANSACTION more reliably. It'd still be nice to > debug the full test case of yours to verify that that's what's > happening, though. > > -- > Heikki Linnakangas > EnterpriseDB http://www.enterprisedb.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > >