[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.&nbsp; And I believe that each =
thread keeps=20
it's own cache.&nbsp; As far as I know the caches are not synchronized =
across=20
the threads.&nbsp; 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.&nbsp; The other threads will not reload =
their=20
cache until the 5000 seconds have passed.&nbsp; We have noticed similiar =
results=20
with our own app.</FONT></DIV>
<DIV><FONT face=3D"Courier New" size=3D2></FONT>&nbsp;</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.&nbsp;=20
That way, even if the combo box reruns the sql statement, the records =
are still=20
there.&nbsp; If it is imperative that the caches always be the same, you =
could=20
probably automatically restart zope to flush all the caches.&nbsp; There =
are=20
probably less draconian methods of invalidating the cache.&nbsp; 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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>I have created a DMTL Method to show =
a=20
  combo-select of countries: </FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2>&lt;SELECT=20
  name=3D"comboCountries"&gt;<BR>&lt;dtml-in =
getCountriesZSQL&gt;<BR>&lt;OPTION=20
  VALUE=3D'&lt;dtml-var country_code null=3D""&gt;'&gt;<BR>&lt;dtml-var =
name=20
  null=3D""&gt;<BR>&lt;/dtml-in&gt;<BR>&lt;/SELECT&gt; </FONT></DIV>
  <DIV>&nbsp;</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>&nbsp;</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>&nbsp;</DIV>
  <DIV><FONT face=3DArial =
size=3D2>Regards,<BR>ZRaul.<BR>Z8-)</FONT></DIV>
  <DIV>&nbsp;</DIV>
  <DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
  <DIV><FONT face=3DArial =
size=3D2></FONT>&nbsp;</DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_00B4_01C16144.33433F00--