Re: insert cursor

Heiko Giesselmann wrote: > Jonathan Leffler wrote: > > Paul Tilles wrote: > > > I currently have an application which attempts to insert one row at a > > > time into a table and then checks the return code. If the return code = > > > -239 or -268, then I do an update of one field in the record. > > > I would like to speed up the insert process by using the buffering > > > capabilities of an insert cursor. > > > > > > When writing records to the buffer, does Informix check the "integrity" > > > of records already in the buffer for such situations as duplicate > > > records? Also, does Informix do any "integrity" checking against > > > records already written to the table? > > > > No. And No. > > Yes. And I don't know. > > When insert cursors are used the engine performs the same integrity checks that > it performs for single row inserts. Of course, but the question was about what's in the buffer, not what happens down in the engine. When you do the PUT into the buffer, there is no validity checking done until the buffer is sent to the engine. Similarly, when you PUT a record into the buffer, there is no checking against other records already written to the database. Hence my "No. And No." answer. If you change the question to "when the data is flushed to the engine, does Informix check the integrity of records ..." then Heiko's Yes answer is correct. And of course the server checks the new records against those already inserted. > Let's take an example: You perform 10 PUT > operations and then a FLUSH. The row you try to insert with the 5th PUT is > actually a duplicate of a row already existing in the database (as defined by > some unique index or primary constraint). The FLUSH will return with sqlcode > -239 and sqlca.sqlerrd[2] contains the number of successfully inserted rows (4 > in the example). > > Now the application could repeat PUT operations 6 to 10 to try to insert the > remaining rows (which would involve some - not too complicated) buffering > scheme. Not too complicated unless you are dealing with gigabyte sized blobs :-) You also have the row that failed to deal with. Note that unless you know which rows are flushed with the flush, knowing that 4 rows succeeded won't help too much. With the numbers you give, I don't think I can devise a failure pattern, but a simple change works. Supposing that you do 10 PUT operations and then FLUSH, and the error information reports that the 3rd row failed. Now, if there was actually an implicit flush on the 6th PUT (with no error), then the row that actually failed is the 9th of the 10, not the 3rd. So it is critical to know when the data is actually flushed! Or, more succinctly, if an implicit flush occurs, you will have serious problems keeping track of which data was OK and which was not OK. Long live violations tables! [...double checking my answer -- if the implicit flush occurred on the 6th row of your example and was successful, then the explicit flush failed on the 4th row of the final 4 rows, so it was the 10th row that caused the trouble, not the 4th. It is critical to know when the implicit flushes occur!...] > For completeness it should be noted that not only a FLUSH but also PUT > operations can return above error codes (that is when the PUT operation needs to > flush buffers to the database to free memory space for the next PUT operation). > Again, sqlcode would indicate success of the overall operation and sqlerrd[2] > indicates how many rows have been successfully processed. > > As for integrity checking, I don't know what you are actually referring to. > Again, the database backend handles inserts through insert cursors not any > different than singleton inserts. Any integrity checks performed for a single > row insert would be performed for rows inserted as part of a insert cursor as > well. > > > > I am running Version 7.30UC2. > > > > Since you are expecting errors from your inserts, I don't think that > > using the INSERT cursor is a good idea. The only way to manage it would > > be to do keep tabs on all the records that have not yet been flushed so > > that when the flush occurs and detects an error, your code can deal > > with: > > > > * Some records were inserted OK. > > * One record failed and so needs to be updated. > > * The subsequent records were flushed but not processed > > and so need to be PUT again and flushed again. > > You are indicating here that integrity checks are actually performed. So your > "No" above is a little bit misleading. It depends on the interpretation of the question. I took a very literal, narrow minded interpretation, and I've now justified my literal, narrow minded answer. Your broader interpretation of the question covers what happens after my answer stopped being applicable. In other words, we're actually pretty much in agreement over what happens, but we were answering different questions. > Anyway, hope this helps, Heiko > > > You probably want to avoid implicit flushes and ensure that the flushes > > occur strictly under program control. > > > > Speaking personally, I think that's sufficiently much harder than using > > the singleton inserts that I would have to be convinced that it really > > was worth spending the time coding and debugging the non-trivial code. -- Yours, Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN "I don't suffer from insanity; I enjoy every minute of it!"