Re: Indexes on temporary tables

Path: emory!swrinde!mips!dimacs.rutgers.edu!rutgers!pyrnj!pyramid!infmx!aland From: aland@informix.com (Colonel Panic) Newsgroups: comp.databases.informix Summary: 4.0 RDS-specific bugs Message-ID: <1991Oct16.052008.27205@informix.com> Date: 16 Oct 91 05:20:08 GMT References: <515@rand.mel.cocam.oz.au> Sender: news@informix.com (Usenet News) Organization: International Brotherhood of Geeks, Local 619 In article <515@rand.mel.cocam.oz.au> shaneb@auzodt3.mel.cocam.oz.au (Shane Booth) writes: > >Came across an interesting one the other day. When we try to run the following >code (with 64 insert statements), the program crashes on the CREATE INDEX with >error -257, system limit on cursors exceeded... But an INSERT statement isn't >a cursor declaration! Also, placing a single SELECT statement before the >CREATE INDEX seems to make the whole thing work fine! Why should it make>any difference? We run the Rapid Development System v4.00.UC1 on SCO Unix. > >DATABASE test > >MAIN > CREATE TEMP TABLE t1 (f1 INTEGER, f2 INTEGER) WITH NO LOG > INSERT INTO tab001(field001) VALUES ("xxx") > { repeat above INSERT 63 more times } > CREATE INDEX temp1 ON t1(f1) >END MAIN> > >Is it OK to create indexes on temporary tables? >Also, are there any known bugs involving temporary tables? I'd be interested >in any other traps that may be lurking just around the corner... Actually, this doesn't seem to depend on it being a temp table, nor does it seem to depend on the database having logging. It was similar, though not exactly like, another known bug that was fixed in 4.00.UC1. It fails only if you created the table in that same program, AND you are using RDS (compiled 4GL works OK), AND you have >= 64 independent INSERT statements (e.g. having 1 statement in a FOR loop run 64 times works OK), AND you follow it up with a CREATE INDEX. It's generic; it fails on 4.00.UH1 on Sun also. It works OK (even in RDS) in 4.10. I reported it as a bug (#11439). Also, until/unless you upgrade to 4.10, I'd suggest not creating temp tables WITH NO LOG in RDS programs -- there's another problem that can cause -206/-111 errors when trying to select from them later. This too is RDS-specific and is fixed in 4.10. >Shane Booth: shaneb@auzodt3.mel.cocam.oz.au -- Alan Denney "If that honey would come back aland@informix.com we would throw such a party {pyramid|uunet}!infmx!aland Drink, and cook the prodigal son Disclaimer: I am a Toon. Fondue forks for everybody." - TMBG