[Zope] ZSQLMethod Cache(unusual behavior)
Chris Kratz
chris.kratz@vistashare.com
Tue, 30 Oct 2001 13:10:08 -0500
This is a multi-part message in MIME format.
------=_NextPart_000_00B4_01C16144.33433F00
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
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 -----=20
From: Ra=FAl P=E9rez=20
To: zope@zope.org=20
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,=20
I have a countries table with 'country_code' and 'name' fields, the =
table has=20
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).=20
I have declared a ZSQLMethod(getCountriesZSQL) with a simple query:=20
SELECT country_code,name FROM countries. At Advanced Tab's ZSQLMethod =
I have=20
managed the following parameters to cache the query:
Maximum rows to retrieve: 300=20
Maximum results to breaks: 300=20
Maximum Time (sec) to breaks: 5000=20
I have created a DMTL Method to show a combo-select of countries:=20
<SELECT name=3D"comboCountries">
<dtml-in getCountriesZSQL>
<OPTION VALUE=3D'<dtml-var country_code null=3D"">'>
<dtml-var name null=3D"">
</dtml-in>
</SELECT>=20
This is my question:
When I delete a record of countries table, the table changes and=20
I think that the combo-select shouldn=B4t change because is cached;=20
but, in fact, the combo-select changes soon, over he fifth or sixth =
page=20
refresh. The result changes showing 210 and 211 records randomly...=20
What is the reason of this behavior?
Does anyone know what is the solution?
Regards,
ZRaul.
Z8-)
------=_NextPart_000_00B4_01C16144.33433F00
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 6.00.2600.0" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3D"Courier New" size=3D2>I believe what you might be =
running into is=20
the fact that zope is multi-threaded. And I believe that each =
thread keeps=20
it's own cache. As far as I know the caches are not synchronized =
across=20
the threads. So, if you happen to get a thread that hasn't cached =
that=20
query previously, it reruns the query on the database and gets the new =
result=20
set without the deleted record. The other threads will not reload =
their=20
cache until the 5000 seconds have passed. We have noticed similiar =
results=20
with our own app.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>If you don't want the delete to =
be cached,=20
I think about the only thing to do is to add a flag on each record that =
marks it=20
as deleted and then run a cron job nightly that clears out those =
records. =20
That way, even if the combo box reruns the sql statement, the records =
are still=20
there. If it is imperative that the caches always be the same, you =
could=20
probably automatically restart zope to flush all the caches. There =
are=20
probably less draconian methods of invalidating the cache. But =
this would=20
still allow you to cache your results without the fear of getting =
threads out of=20
sync.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT> </DIV>
<DIV><FONT face=3D"Courier New" size=3D2>This is why we don't use =
cacheing much on=20
sql statements unless we know that the results change only rarely =
because we=20
have to be sure that the same request always returns the most recent=20
data.</FONT></DIV>
<DIV>------------------------------<BR>Chris Kratz<BR><A=20
href=3D"mailto:chris.kratz@vistashare.com">chris.kratz@vistashare.com</A>=
<BR></DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style=3D"FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV=20
style=3D"BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: =
black"><B>From:</B>=20
<A title=3Drperez@hola.com href=3D"mailto:rperez@hola.com">Ra=FAl =
P=E9rez</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>To:</B> <A title=3Dzope@zope.org=20
href=3D"mailto:zope@zope.org">zope@zope.org</A> </DIV>
<DIV style=3D"FONT: 10pt arial"><B>Sent:</B> Tuesday, October 30, 2001 =
12:55=20
PM</DIV>
<DIV style=3D"FONT: 10pt arial"><B>Subject:</B> [Zope] ZSQLMethod =
Cache(unusual=20
behavior)</DIV>
<DIV><BR></DIV>
<DIV><FONT face=3DArial size=3D2>Hi all, I am a newbie and I have a =
question,=20
</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>I have a countries table with =
'country_code' and=20
'name' fields, the table has <BR>211 records and<BR>the database is =
postgres=20
and connection is made with product Z Psycopg (also I<BR>have checked =
it with=20
PoPy and the result is the same one I explain later). </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>I have declared a =
ZSQLMethod(getCountriesZSQL)=20
with a simple query: <BR>SELECT country_code,name FROM countries. At =
Advanced=20
Tab's ZSQLMethod I have <BR>managed the following parameters to cache =
the=20
query:</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>Maximum rows to retrieve: 300 =
<BR>Maximum results=20
to breaks: 300 <BR>Maximum Time (sec) to breaks: 5000 </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>I have created a DMTL Method to show =
a=20
combo-select of countries: </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2><SELECT=20
name=3D"comboCountries"><BR><dtml-in =
getCountriesZSQL><BR><OPTION=20
VALUE=3D'<dtml-var country_code null=3D"">'><BR><dtml-var =
name=20
null=3D""><BR></dtml-in><BR></SELECT> </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>This is my question:<BR>When I delete =
a record of=20
countries table, the table changes and <BR>I think that the =
combo-select=20
shouldn=B4t change because is cached; <BR>but, in fact, the =
combo-select changes=20
soon, over he fifth or sixth page <BR>refresh. The result changes =
showing 210=20
and 211 records randomly... </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2>What is the reason of this =
behavior?<BR>Does=20
anyone know what is the solution?</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial =
size=3D2>Regards,<BR>ZRaul.<BR>Z8-)</FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=3DArial size=3D2></FONT> </DIV>
<DIV><FONT face=3DArial =
size=3D2></FONT> </DIV></BLOCKQUOTE></BODY></HTML>
------=_NextPart_000_00B4_01C16144.33433F00--