No subject
Fri Jan 30 20:15:43 JST 2015
# First, read and update data - invalidates cache - this works fine.
psql -U ruser -d mydb -h 127.0.0.1 -p 9999
Password for user ruser:
psql (9.4.1)
Type "help" for help.
mydb=3D> select comments from users where id=3D1;
comments
----------------
boot me please
(1 row)
mydb=3D> update users set comments=3D'hello' where id=3D1;
UPDATE 1
mydb=3D> select comments from users where id=3D1;
comments
----------
hello
(1 row)
mydb=3D>
Second pgpool:
# Connects via second deamon, reads stale data, i/e updates from this
pgpool instance never invalidate cache.
=CE=BB ~/ psql -U ruser -d mydb -h 127.0.0.1 -p 9988
Password for user ruser:
psql (9.4.1)
Type "help" for help.
mydb=3D> select comments from users where id=3D1;
comments
----------
hello
(1 row)
mydb=3D> update users set comments=3D'hello world' where id=3D1;
UPDATE 1
mydb=3D> select comments from users where id=3D1;
comments
----------
hello
(1 row)
mydb=3D>
The configs and full logs are attached in the link, They both are same
except for the directory paths and ports - not sure whats going wrong here.
https://gist.github.com/dbalan/c5a93458d5c091c28c71
*DHANANJAY *| Infrastructure Engineer
Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
Web: www.plivo.com | Twitter: @plivo <http://twitter.com/plivo>,
@notmycommit <https://twitter.com/notmycommit>
Free Incoming SMS for All US Short Codes =E2=80=93 Get One Today!
<https://www.plivo.com/sms-short-code/?utm=3Demailsig>=EF=BB=BF
On Thu, Apr 23, 2015 at 5:11 PM, Muhammad Usama <m.usama at gmail.com> wrote:
> Hi
>
> I have found the problem, It was because of the buffer for storing table
> oids was created in wrong memory context.
> I have pushed the fix for it in master and 3_4 stable branches.
>
> Thanks
> Best regards
> Muhammad Usama
>
>
> On Wed, Apr 22, 2015 at 2:09 PM, Tatsuo Ishii <ishii at postgresql.org>
> wrote:
>
>> Thanks for the report.
>>
>> I confirm this with pgpool-II 3.4.1. I also found that this does not
>> happen with 3.3.5 (I tested with shmem).
>>
>> Here is the test script.
>> -------------------------------------
>> drop table t1;
>> create table t1(i int, j text);
>> insert into t1 values(1, 'aaa');
>> select * from t1;
>> select * from t1;
>> update t1 set j =3D 'bbb' where i =3D 1;
>> select * from t1;
>> -------------------------------------
>>
>> Will look into this...
>>
>> Best regards,
>> --
>> Tatsuo Ishii
>> SRA OSS, Inc. Japan
>> English: http://www.sraoss.co.jp/index_en.php
>> Japanese:http://www.sraoss.co.jp
>>
>> > Looks like only part of this email ended up in the mailing list , not
>> > sure how. Resending again - hope it works this time.
>> >
>> > pgpool doesn't seem to be invalidating caches properly. See the
>> > example test case below
>> >
>> > psql -U ruser -d mydb -h 127.0.0.1 -p 9999
>> > Password for user ruser:
>> > psql (9.4.1)
>> > Type "help" for help.
>> >
>> > mydb=3D> select comments from users where id=3D1;
>> > comments
>> > ----------
>> > boooo
>> > (1 row)
>> >
>> > # comes from cache
>> > mydb=3D> select comments from users where id=3D1;
>> > comments
>> > ----------
>> > boooo
>> > (1 row)
>> >
>> > # works
>> > mydb=3D> update users set comments=3D'hello' where id=3D1;
>> > UPDATE 1
>> > mydb=3D> select comments from users where id=3D1;
>> > comments
>> > ----------
>> > hello
>> > (1 row)
>> >
>> > # stale
>> > mydb=3D> update users set comments=3D'hello world' where id=3D1;
>> > UPDATE 1
>> > mydb=3D> select comments from users where id=3D1;
>> > comments
>> > ----------
>> > hello
>> > (1 row)
>> >
>> > # also stale
>> > mydb=3D> update users set comments=3D'booo' where id=3D1;
>> > UPDATE 1
>> > mydb=3D> select comments from users where id=3D1;
>> > comments
>> > ----------
>> > hello
>> > (1 row)
>> >
>> >
>> > From the second write onwards, the cache is not invalidated and stale
>> > value is being served. Is it a bug or am I doing something wrong? This
>> > happens with both cache backends (memcache and shmem but test logs are
>> > from memcache backend)
>> >
>> > Test setup is postgresql-9.4 and memcached 1.4.21 and pgpool-3.4.2. my
>> > configs and logs from test are here
>> >
>> > https://gist.github.com/dbalan/d635993a3e532a083751
>> >
>> > Regards,
>> > --------
>> > DHANANJAY | Infrastructure Engineer
>> > Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
>> > Web: www.plivo.com | Twitter: @plivo, @notmycommit
>> > DHANANJAY | Infrastructure Engineer
>> > Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
>> > Web: www.plivo.com | Twitter: @plivo, @notmycommit
>> >
>> > Free Incoming SMS for All US Short Codes =E2=80=93 Get One Today!=EF=
=BB=BF
>> >
>> >
>> >
>> >
>> > On Tue, Apr 21, 2015 at 12:33 PM, Dhananjay Balan <dhananjay at plivo.com=
>
>> wrote:
>> >> pgpool doesn't seem to be invalidating caches properly. See the
>> >> example test case below
>> >>
>> >> psql -U ruser -d mydb -h 127.0.0.1 -p 9999
>> >> Password for user ruser:
>> >> psql (9.4.1)
>> >> Type "help" for help.
>> >>
>> >> mydb=3D> select comments from users where id=3D1;
>> >> comments
>> >> ----------
>> >> boooo
>> >> (1 row)
>> >>
>> >> # comes from cache
>> >> mydb=3D> select comments from users where id=3D1;
>> >> comments
>> >> ----------
>> >> boooo
>> >> (1 row)
>> >>
>> >> # works
>> >> mydb=3D> update users set comments=3D'hello' where id=3D1;
>> >> UPDATE 1
>> >> mydb=3D> select comments from users where id=3D1;
>> >> comments
>> >> ----------
>> >> hello
>> >> (1 row)
>> >>
>> >> # stale
>> >> mydb=3D> update users set comments=3D'hello world' where id=3D1;
>> >> UPDATE 1
>> >> mydb=3D> select comments from users where id=3D1;
>> >> comments
>> >> ----------
>> >> hello
>> >> (1 row)
>> >>
>> >> # also stale
>> >> mydb=3D> update users set comments=3D'booo' where id=3D1;
>> >> UPDATE 1
>> >> mydb=3D> select comments from users where id=3D1;
>> >> comments
>> >> ----------
>> >> hello
>> >> (1 row)
>> >>
>> >>
>> >> From the second write onwards, the cache is not invalidated and stale
>> >> value is being served. Is it a bug or am I doing something wrong? Thi=
s
>> >> happens with both cache backends (memcache and shmem but test logs ar=
e
>> >> from memcache backend)
>> >>
>> >> Test setup is postgresql-9.4 and memcached 1.4.21 and pgpool-3.4.2. m=
y
>> >> configs and logs from test are here
>> >>
>> >> https://gist.github.com/dbalan/d635993a3e532a083751
>> >>
>> >> Regards,
>> >> --------
>> >> DHANANJAY | Infrastructure Engineer
>> >> Plivo, Inc. 340 Pine St, San Francisco - 94104, USA
>> >> Web: www.plivo.com | Twitter: @plivo, @notmycommit
>> > _______________________________________________
>> > pgpool-general mailing list
>> > pgpool-general at pgpool.net
>> > http://www.pgpool.net/mailman/listinfo/pgpool-general
>> _______________________________________________
>> pgpool-general mailing list
>> pgpool-general at pgpool.net
>> http://www.pgpool.net/mailman/listinfo/pgpool-general
>>
>
>
--20cf3071cec0f853aa0514b3782b
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
<div dir=3D"ltr"><div><div><div><div>Hi,<br><br></div>This commit seems to =
have fixed the issue. However cache invalidation when multiple pgpools are =
used (connecting to same memcache server) is (?) broken even with 3.3.5. He=
re are the logs from 3.3.5 <br><br><a href=3D"https://gist.github.com/dbala=
n/c5a93458d5c091c28c71">https://gist.github.com/dbalan/c5a93458d5c091c28c71=
</a><br><br></div>My test setup is two instances of 3.3.5 running on 9999 a=
nd 9988 of same machine with memcache running in localhost.<br><br></div>Fr=
om first pgpool,<br></div><br># First, read and update data - invalidates c=
ache - this works fine.<br>psql -U ruser -d mydb -h 127.0.0.1 -p 9999<br>Pa=
ssword for user ruser:<br>psql (9.4.1)<br>Type "help" for help.<b=
r>=C2=A0<br>mydb=3D> select comments from users where id=3D1;<br>=C2=A0=
=C2=A0=C2=A0 comments<br>----------------<br>=C2=A0boot me please<br>(1 row=
)<br>=C2=A0<br>mydb=3D> update users set comments=3D'hello' wher=
e id=3D1;<br>UPDATE 1<br>mydb=3D> select comments from users where id=3D=
1;<br>=C2=A0comments<br>----------<br>=C2=A0hello<br>(1 row)<br>=C2=A0<br>m=
ydb=3D><br><div><div><br><br></div><div>Second pgpool:<br># Connects via=
second deamon, reads stale data, i/e updates from this pgpool instance nev=
er invalidate cache.<br>=CE=BB ~/ psql -U ruser -d mydb -h 127.0.0.1 -p 998=
8<br>Password for user ruser:<br>psql (9.4.1)<br>Type "help" for =
help.<br>=C2=A0<br>mydb=3D> select comments from users where id=3D1;<br>=
=C2=A0comments<br>----------<br>=C2=A0hello<br>(1 row)<br>=C2=A0<br>mydb=3D=
> update users set comments=3D'hello world' where id=3D1;<br>UPD=
ATE 1<br>mydb=3D> select comments from users where id=3D1;<br>=C2=A0comm=
ents<br>----------<br>=C2=A0hello<br>(1 row)<br>=C2=A0<br>mydb=3D><br><b=
r><br></div><div>The configs and full logs are attached in the link, They b=
oth are same except for the directory paths and ports - not sure whats goin=
g wrong here.<br></div><div><br><a href=3D"https://gist.github.com/dbalan/c=
5a93458d5c091c28c71">https://gist.github.com/dbalan/c5a93458d5c091c28c71<br=
></a><br><br></div><div><div><div><br></div></div></div></div></div><div cl=
ass=3D"gmail_extra"><br clear=3D"all"><div><div class=3D"gmail_signature"><=
div dir=3D"ltr"><div><div dir=3D"ltr"><div><b style=3D"font-size:small;font=
-family:tahoma,sans-serif">DHANANJAY=C2=A0</b><font style=3D"font-size:smal=
l;font-family:tahoma,sans-serif" color=3D"#666666">| Infrastructure Enginee=
r</font><br></div><div dir=3D"ltr"><div style=3D"font-size:small;font-famil=
y:arial"><div><span style=3D"color:rgb(102,102,102);font-family:tahoma,sans=
-serif;font-size:13px;line-height:16.1200008392334px">Plivo, Inc. 340 Pine =
St, San Francisco - 94104, USA</span><font face=3D"tahoma, sans-serif"><fon=
t color=3D"#666666"><br></font></font></div><div><font face=3D"tahoma, sans=
-serif"><font color=3D"#666666">Web:</font>=C2=A0<a href=3D"http://www.pliv=
o.com/" style=3D"color:rgb(17,85,204)" target=3D"_blank">www.plivo.com</a>=
=C2=A0<font color=3D"#666666">| Twitter:</font>=C2=A0<a href=3D"http://twit=
ter.com/plivo" style=3D"color:rgb(17,85,204)" target=3D"_blank">@plivo</a>,=
<a href=3D"https://twitter.com/notmycommit" target=3D"_blank">@notmycommit=
</a></font></div><div><br><a href=3D"https://www.plivo.com/sms-short-code/?=
utm=3Demailsig" target=3D"_blank">Free Incoming SMS for All US Short Codes =
=E2=80=93 Get One Today!</a>=EF=BB=BF<br></div><div><br></div></div></div><=
div><br></div></div></div></div></div></div>
<br><div class=3D"gmail_quote">On Thu, Apr 23, 2015 at 5:11 PM, Muhammad Us=
ama <span dir=3D"ltr"><<a href=3D"mailto:m.usama at gmail.com" target=3D"_b=
lank">m.usama at gmail.com</a>></span> wrote:<br><blockquote class=3D"gmail=
_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:=
1ex"><div dir=3D"ltr">Hi<div><br></div><div>I have found the problem, It wa=
s because of the buffer for storing table oids=C2=A0was created in wrong me=
mory context.</div><div>I have pushed the fix for it in master and 3_4 stab=
le branches.</div><div><br></div><div>Thanks</div><div>Best regards</div><s=
pan class=3D"HOEnZb"><font color=3D"#888888"><div>Muhammad Usama</div></fon=
t></span><div><div class=3D"h5"><div><br></div><div class=3D"gmail_extra"><=
br><div class=3D"gmail_quote">On Wed, Apr 22, 2015 at 2:09 PM, Tatsuo Ishii=
<span dir=3D"ltr"><<a href=3D"mailto:ishii at postgresql.org" target=3D"_b=
lank">ishii at postgresql.org</a>></span> wrote:<br><blockquote class=3D"gm=
ail_quote" style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-le=
ft:1ex">Thanks for the report.<br>
<br>
I confirm this with pgpool-II 3.4.1. I also found that this does not<br>
happen with 3.3.5 (I tested with shmem).<br>
<br>
Here is the test script.<br>
-------------------------------------<br>
drop table t1;<br>
create table t1(i int, j text);<br>
insert into t1 values(1, 'aaa');<br>
select * from t1;<br>
select * from t1;<br>
update t1 set j =3D 'bbb' where i =3D 1;<br>
select * from t1;<br>
-------------------------------------<br>
<br>
Will look into this...<br>
<br>
Best regards,<br>
--<br>
Tatsuo Ishii<br>
SRA OSS, Inc. Japan<br>
English: <a href=3D"http://www.sraoss.co.jp/index_en.php" target=3D"_blank"=
>http://www.sraoss.co.jp/index_en.php</a><br>
Japanese:<a href=3D"http://www.sraoss.co.jp" target=3D"_blank">http://www.s=
raoss.co.jp</a><br>
<div><div><br>
> Looks like only part of this email ended up in the mailing list , not<=
br>
> sure how. Resending again - hope it works this time.<br>
><br>
> pgpool doesn't seem to be invalidating caches properly. See the<br=
>
> example test case below<br>
><br>
> psql -U ruser -d mydb -h 127.0.0.1 -p 9999<br>
> Password for user ruser:<br>
> psql (9.4.1)<br>
> Type "help" for help.<br>
><br>
> mydb=3D> select comments from users where id=3D1;<br>
>=C2=A0 comments<br>
> ----------<br>
>=C2=A0 boooo<br>
> (1 row)<br>
><br>
> # comes from cache<br>
> mydb=3D> select comments from users where id=3D1;<br>
>=C2=A0 comments<br>
> ----------<br>
>=C2=A0 boooo<br>
> (1 row)<br>
><br>
> # works<br>
> mydb=3D> update users set comments=3D'hello' where id=3D1;<=
br>
> UPDATE 1<br>
> mydb=3D> select comments from users where id=3D1;<br>
>=C2=A0 comments<br>
> ----------<br>
>=C2=A0 hello<br>
> (1 row)<br>
><br>
> # stale<br>
> mydb=3D> update users set comments=3D'hello world' where id=
=3D1;<br>
> UPDATE 1<br>
> mydb=3D> select comments from users where id=3D1;<br>
>=C2=A0 comments<br>
> ----------<br>
>=C2=A0 hello<br>
> (1 row)<br>
><br>
> # also stale<br>
> mydb=3D> update users set comments=3D'booo' where id=3D1;<b=
r>
> UPDATE 1<br>
> mydb=3D> select comments from users where id=3D1;<br>
>=C2=A0 comments<br>
> ----------<br>
>=C2=A0 hello<br>
> (1 row)<br>
><br>
><br>
> From the second write onwards, the cache is not invalidated and stale<=
br>
> value is being served. Is it a bug or am I doing something wrong? This=
<br>
> happens with both cache backends (memcache and shmem but test logs are=
<br>
> from memcache backend)<br>
><br>
> Test setup is postgresql-9.4 and memcached 1.4.21 and pgpool-3.4.2. my=
<br>
> configs and logs from test are here<br>
><br>
> <a href=3D"https://gist.github.com/dbalan/d635993a3e532a083751" target=
=3D"_blank">https://gist.github.com/dbalan/d635993a3e532a083751</a><br>
><br>
> Regards,<br>
> --------<br>
> DHANANJAY | Infrastructure Engineer<br>
> Plivo, Inc. 340 Pine St, San Francisco - 94104, USA<br>
> Web: <a href=3D"http://www.plivo.com" target=3D"_blank">www.plivo.com<=
/a> | Twitter: @plivo, @notmycommit<br>
> DHANANJAY | Infrastructure Engineer<br>
> Plivo, Inc. 340 Pine St, San Francisco - 94104, USA<br>
> Web: <a href=3D"http://www.plivo.com" target=3D"_blank">www.plivo.com<=
/a> | Twitter: @plivo, @notmycommit<br>
><br>
> Free Incoming SMS for All US Short Codes =E2=80=93 Get One Today!=EF=
=BB=BF<br>
><br>
><br>
><br>
><br>
> On Tue, Apr 21, 2015 at 12:33 PM, Dhananjay Balan <<a href=3D"mailt=
o:dhananjay at plivo.com" target=3D"_blank">dhananjay at plivo.com</a>> wrote:=
<br>
>> pgpool doesn't seem to be invalidating caches properly. See th=
e<br>
>> example test case below<br>
>><br>
>> psql -U ruser -d mydb -h 127.0.0.1 -p 9999<br>
>> Password for user ruser:<br>
>> psql (9.4.1)<br>
>> Type "help" for help.<br>
>><br>
>> mydb=3D> select comments from users where id=3D1;<br>
>>=C2=A0 comments<br>
>> ----------<br>
>>=C2=A0 boooo<br>
>> (1 row)<br>
>><br>
>> # comes from cache<br>
>> mydb=3D> select comments from users where id=3D1;<br>
>>=C2=A0 comments<br>
>> ----------<br>
>>=C2=A0 boooo<br>
>> (1 row)<br>
>><br>
>> # works<br>
>> mydb=3D> update users set comments=3D'hello' where id=
=3D1;<br>
>> UPDATE 1<br>
>> mydb=3D> select comments from users where id=3D1;<br>
>>=C2=A0 comments<br>
>> ----------<br>
>>=C2=A0 hello<br>
>> (1 row)<br>
>><br>
>> # stale<br>
>> mydb=3D> update users set comments=3D'hello world' wher=
e id=3D1;<br>
>> UPDATE 1<br>
>> mydb=3D> select comments from users where id=3D1;<br>
>>=C2=A0 comments<br>
>> ----------<br>
>>=C2=A0 hello<br>
>> (1 row)<br>
>><br>
>> # also stale<br>
>> mydb=3D> update users set comments=3D'booo' where id=3D=
1;<br>
>> UPDATE 1<br>
>> mydb=3D> select comments from users where id=3D1;<br>
>>=C2=A0 comments<br>
>> ----------<br>
>>=C2=A0 hello<br>
>> (1 row)<br>
>><br>
>><br>
>> From the second write onwards, the cache is not invalidated and st=
ale<br>
>> value is being served. Is it a bug or am I doing something wrong? =
This<br>
>> happens with both cache backends (memcache and shmem but test logs=
are<br>
>> from memcache backend)<br>
>><br>
>> Test setup is postgresql-9.4 and memcached 1.4.21 and pgpool-3.4.2=
. my<br>
>> configs and logs from test are here<br>
>><br>
>> <a href=3D"https://gist.github.com/dbalan/d635993a3e532a083751" ta=
rget=3D"_blank">https://gist.github.com/dbalan/d635993a3e532a083751</a><br>
>><br>
>> Regards,<br>
>> --------<br>
>> DHANANJAY | Infrastructure Engineer<br>
>> Plivo, Inc. 340 Pine St, San Francisco - 94104, USA<br>
>> Web: <a href=3D"http://www.plivo.com" target=3D"_blank">www.plivo.=
com</a> | Twitter: @plivo, @notmycommit<br>
> _______________________________________________<br>
> pgpool-general mailing list<br>
> <a href=3D"mailto:pgpool-general at pgpool.net" target=3D"_blank">pgpool-=
general at pgpool.net</a><br>
> <a href=3D"http://www.pgpool.net/mailman/listinfo/pgpool-general" targ=
et=3D"_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
_______________________________________________<br>
pgpool-general mailing list<br>
<a href=3D"mailto:pgpool-general at pgpool.net" target=3D"_blank">pgpool-gener=
al at pgpool.net</a><br>
<a href=3D"http://www.pgpool.net/mailman/listinfo/pgpool-general" target=3D=
"_blank">http://www.pgpool.net/mailman/listinfo/pgpool-general</a><br>
</div></div></blockquote></div><br></div></div></div></div>
</blockquote></div><br></div>
--20cf3071cec0f853aa0514b3782b--
More information about the pgpool-general
mailing list