 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussion Groups Database Servers DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybaseDesktop Databases FileMakerFoxProMS AccessParadoxGeneral General DB TopicsDatabase TheoryRelated Topics Java Development.NET DevelopmentVB DevelopmentMore Topics ... |
Database Forum / Ingres Topics / February 2005global temporary table disappears??
HP-UX 11.11 Ingres 2.6/0305 I'm using a script that creates several global temporary tables. Among other temporary tables created, part of the process creates one table and then creates a second table from some of the data from the previous one. I then do an update to the second one from the first with some additional data; an exercise in getting a unique set of data :-(. The update to the second table complains with the "tuple at tid already deleted" error. And then the next statement that tries to update a different table from the second table complains that the second table doesn't exist or isn't owned by me. This is all happening in the same session so I can't figure out why the one table keeps disappearing. When it's first declared there is no complaint that it couldn't be created, and in fact returns the n rows message. I understand that global temporary tables will be written to disk if there isn't enough memory to hold them. But if that's what's happening here could there be a timing issue before the disk write completes and the next statement tries to access the data? The thing that led me to using session tables is that I don't need, or want, logging while moving the data. I suppose I could skip session tables and set nologging, and if something errors out it's easy enough to mark the dB consistent and go back to step one :-(. Thanks Dennis d _ roesler at agilent dot com >The update to the second table complains with the "tuple at tid already deleted" error. And then the next statement that tries to update a different table from the second table complains that the second table doesn't exist or isn't owned by me. I can't explain the "tuple at tid already deleted", that sounds pretty fishy. The bit about the temp table disappearing is, I'm afraid, a feature. A documented feature, no less. Since temp tables have no logging, if Ingres updates a temp table during a transaction, and that transaction is later rolled back, the temp table is *dropped*. I guess the theory is that there's no way to undo the updates that were made to the temp table, so there's no way to make it logically consistent. I have to admit that when I used to write code using the darn things, this feature was a real pain in the a.s. I'd love to have a variant of temp tables that says "keep me even if rollback occurs, I understand that the contents are indeterminate and I'll deal with it." Karl > >The update to the second table complains with the "tuple at tid already deleted" error. And then the next statement that tries to update a different table from the second table complains that the second table doesn't exist or isn't owned by me. > I can't explain the "tuple at tid already deleted", that sounds pretty > fishy. The bit about the temp table disappearing is, I'm afraid, [quoted text clipped - 9 lines] > rollback occurs, I understand that the contents are indeterminate > and I'll deal with it." That would be awfully close to being a Type 2 temporary table, which we discussed recently elsewhere, and I suggested at the time that I would quite like to have them too. I am sure there are good historical reasons why Ingres implemented Type 1 temporary tables first, but in this and several other ways Type 1 tables are rather goofy. Before you tell me where to find the source code, the answer is "I'm busy." Roy >> The update to the second table complains with the "tuple at tid >> already deleted" error. And then the next statement that tries to update [quoted text clipped - 4 lines] > fishy. The bit about the temp table disappearing is, I'm afraid, > a feature. A documented feature, no less. Ah, I wasn't aware of the "documented feature". I guess I thought 'on commit preserve rows' would keep it intact, but I guess the 'with norecovery' part overrides that and wasn't clear to me :-(. It's just there is no indication the declaration of the table failed to begin with. > I'd love to have a variant of temp tables that says "keep me even if > rollback occurs, I understand that the contents are indeterminate > and I'll deal with it." I agree :-( Migrating legacy data to a new schema is always fun :-). Cheers Dennis Dennis > The update to the second table complains with the "tuple at tid already > deleted" error. And then the next statement that tries to update a > different table from the second table complains that the second table > doesn't exist or isn't owned by me. > > This is all happening in the same session so I can't figure out why the > one table keeps disappearing. When it's first declared there is no > complaint that it couldn't be created, and in fact returns the n rows > message. Does it happen repeatably? We have two longstanding and intermitant problems that appear to be related to Temporary tables with VMS and Ingres 2.0 - Part of the problem is that generally re-running the query works as expected, so capturing information or testing suggested fixes always relies on a lot of waiting to see if it will go wrong again... The errors we see are either E_OP0004_RDF_GETDESC request for relation information failed - possibly because table was modified or deleted E_RD0113 consistency check - inconsistent keying info in attribute cache or Updates of Global Temporary Tables occasionally produce E_RD0060_DMT_SHOW errors. In the latter case we have (well, CA have) been able to determine the problem is caused by :- The session has set up memory to store X attributes, but the TCB (table control block) indicates that the table has >X attributes. but as of yet we are still trying to determine why and how to stop it... >>The update to the second table complains with the "tuple at tid > [quoted text clipped - 39 lines] > but as of yet we are still trying to determine why and how to stop > it... Hi Tim, I believe the issue is what was mentioned in previous posts by Paul and Karl. The real problem was an ambiguous replace, but this is not the error reported. That underlying error forced the session table to disappear and when the next statement tried to access it, the table doesn't exist error occurred. I've re-examined what should determine uniqueness and modified the scripts. Just need to wait ~3-4 hours to see the results :-(. Cheers Dennis d _ roesler at agilent dot com |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.