I believe what you might be running into is the fact that zope is multi-threaded.  And I believe that each thread keeps it's own cache.  As far as I know the caches are not synchronized across the threads.  So, if you happen to get a thread that hasn't cached that query previously, it reruns the query on the database and gets the new result set without the deleted record.  The other threads will not reload their cache until the 5000 seconds have passed.  We have noticed similiar results with our own app.
 
If you don't want the delete to be cached, I think about the only thing to do is to add a flag on each record that marks it as deleted and then run a cron job nightly that clears out those records.  That way, even if the combo box reruns the sql statement, the records are still there.  If it is imperative that the caches always be the same, you could probably automatically restart zope to flush all the caches.  There are probably less draconian methods of invalidating the cache.  But this would still allow you to cache your results without the fear of getting threads out of sync.
 
This is why we don't use cacheing much on sql statements unless we know that the results change only rarely because we have to be sure that the same request always returns the most recent data.
------------------------------
Chris Kratz
chris.kratz@vistashare.com
----- Original Message -----
From: Raúl Pérez
To: zope@zope.org
Sent: Tuesday, October 30, 2001 12:55 PM
Subject: [Zope] ZSQLMethod Cache(unusual behavior)

Hi all, I am a newbie and I have a question,
 
I have a countries table with 'country_code' and 'name' fields, the table has
211 records and
the database is postgres and connection is made with product Z Psycopg (also I
have checked it with PoPy and the result is the same one I explain later).
 
I have declared a ZSQLMethod(getCountriesZSQL) with a simple query:
SELECT country_code,name FROM countries. At Advanced Tab's ZSQLMethod I have
managed the following parameters to cache the query:
 
Maximum rows to retrieve: 300
Maximum results to breaks: 300
Maximum Time (sec) to breaks: 5000
 
I have created a DMTL Method to show a combo-select of countries:
 
<SELECT name="comboCountries">
<dtml-in getCountriesZSQL>
<OPTION VALUE='<dtml-var country_code null="">'>
<dtml-var name null="">
</dtml-in>
</SELECT>
 
This is my question:
When I delete a record of countries table, the table changes and
I think that the combo-select shouldn´t change because is cached;
but, in fact, the combo-select changes soon, over he fifth or sixth page
refresh. The result changes showing 210 and 211 records randomly...
 
What is the reason of this behavior?
Does anyone know what is the solution?
 
Regards,
ZRaul.
Z8-)