View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000058 | Pgpool-II | Bug | public | 2013-05-08 08:51 | 2013-06-12 20:01 |
| Reporter | wms | Assigned To | t-ishii | ||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| Platform | x86_64 | OS | Oracle Linux | OS Version | 6.3 |
| Summary | 0000058: query cache invalidation does not fire for multiple DML in transaction | ||||
| Description | I have the memory cache turned on with auto invalidation. It looks like I'm seeing a bug along the lines of: SELECT x.* FROM x; # returns data from backend, stores in cache BEGIN INSERT INTO x (a, b, c) VALUES (1,2,3); DELETE FROM y WHERE z = 0; COMMIT SELECT x.* FROM x; # returns cached data I would have expected the INSERT in the transaction to invalidate the query cache. Note that the DELETE is for another table. | ||||
| Additional Information | pgpool-II 3.2.3 and 3.2.4 with PostgreSQL 9.2 | ||||
| Tags | No tags attached. | ||||
|
|
I was able to work around it by performing the DELETE after the COMMIT, which is not ideal for my application, but it should be good enough for now. |
|
|
Did not reproduce here. CREATE TABLE t1(i INTEGER); CREATE TABLE t2(i INTEGER); SELECT * FROM t1; BEGIN; INSERT INTO t1(i) VALUES(1); DELETE FROM t2 WHERE i = 0; COMMIT; SELECT * FROM t1; The last SELECT correctly returns i = 1; |
|
|
|
|
|
Looks like I over-simplified what I was seeing in our logs. I have attached logs that seem to show the following query get cached and then not actually discarded even though the logs say it was. SELECT "tB".* FROM "tB" WHERE "tB"."id" = 2231 ORDER BY tB.created_at DESC LIMIT 1 |
|
|
Can you please show a self contained test case? I have had hard times to reproduce the problem. |
|
|
I am also having a hard time coming up with a reduced test case. Do you at least agree that the logs seem to show that query being pulled from the cache even though earlier in the log the cache was supposed to be discarded? I just can't figure out the exact pattern our app follows to trigger the behavior. |
|
|
Yes. Although from your log I cannot see when the query cache for "SELECT "tB".* FROM "tB" WHERE "tB"."id" = 2231..." was created but I see UPDATE for tB was committed, thus the cache should have been discarded anyway. However after the commit "SELECT "tB".* FROM "tB" WHERE "tB"."id" = 2231..." returns the result from cache, and this should not happen. |
|
|
Here are the queries to reproduce: DROP TABLE IF EXISTS t1; DROP TABLE IF EXISTS t2; CREATE TABLE t1 (id integer NOT NULL, state integer); CREATE SEQUENCE t1_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE ONLY t1 ALTER COLUMN id SET DEFAULT nextval('t1_seq'::regclass); ALTER TABLE ONLY t1 ADD CONSTRAINT t1_pkey PRIMARY KEY (id); CREATE TABLE t2 (id integer NOT NULL, a_id integer NOT NULL); CREATE SEQUENCE t2_seq START WITH 1 INCREMENT BY 1 NO MINVALUE NO MAXVALUE CACHE 1; ALTER TABLE ONLY t2 ALTER COLUMN id SET DEFAULT nextval('t2_seq'::regclass); ALTER TABLE ONLY t2 ADD CONSTRAINT t2_pkey PRIMARY KEY (id); ALTER TABLE ONLY t2 ADD CONSTRAINT t2_a_id_fkey FOREIGN KEY (a_id) REFERENCES t1(id); BEGIN; INSERT INTO "t1" ("state") VALUES (0); UPDATE "t1" SET "state" = 4 WHERE ("t1"."id" = 1); INSERT INTO "t2" ("a_id") VALUES (1) RETURNING "id"; COMMIT; BEGIN; SELECT "t1".* FROM "t1" WHERE "t1"."id" = 1; DELETE FROM "t2" WHERE ("t2"."id" > 0); COMMIT; BEGIN; INSERT INTO "t2" ("a_id") VALUES (1) RETURNING "id"; SELECT "t2".* FROM "t2" WHERE ("t2".a_id = 1); UPDATE "t1" SET "state" = 25 WHERE ("t1"."id" = 1); COMMIT; BEGIN; SELECT "t1".* FROM "t1" WHERE "t1"."id" = 1; COMMIT; The last select should show the record having state=25, but it shows state=4. I can see state=25 if I connect directly to the backend. |
|
|
Thanks for the test case. That helps a lot! It turned out that it's a pgpool's bug: BEGIN; INSERT INTO "t2" ("a_id") VALUES (1) RETURNING "id"; SELECT "t2".* FROM "t2" WHERE ("t2".a_id = 1); UPDATE "t1" SET "state" = 25 WHERE ("t1"."id" = 1); COMMIT; At commit, pgpool tries to remove cache for t2 but it fails because in the same transaction the last statement was DML(UPDATE "t1"...). (This sounds strange because the last DML was for t1, not t2. Yes, I know there's room to enhance but that is another story...) After the failure pgpool mistakenly stops to invalidate cache for t1. I have attached a fix. Can you please try it out? |
|
|
|
|
|
All of our tests pass now. Thanks for the quick fix. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2013-05-08 08:51 | wms | New Issue | |
| 2013-05-08 08:53 | wms | Note Added: 0000268 | |
| 2013-05-11 12:30 | t-ishii | Note Added: 0000269 | |
| 2013-05-11 12:30 | t-ishii | Assigned To | => t-ishii |
| 2013-05-11 12:30 | t-ishii | Status | new => assigned |
| 2013-05-14 03:13 | wms | File Added: pgpool_bug_58_logs.txt | |
| 2013-05-14 03:16 | wms | Note Added: 0000275 | |
| 2013-05-14 11:46 | t-ishii | Note Added: 0000276 | |
| 2013-05-14 11:57 | t-ishii | Status | assigned => feedback |
| 2013-05-15 09:14 | wms | Note Added: 0000280 | |
| 2013-05-15 09:14 | wms | Status | feedback => assigned |
| 2013-05-15 09:25 | t-ishii | Note Added: 0000281 | |
| 2013-05-17 04:48 | wms | Note Added: 0000284 | |
| 2013-05-17 09:04 | t-ishii | Note Added: 0000285 | |
| 2013-05-17 09:08 | t-ishii | File Added: pool_memqcache.c.patch | |
| 2013-05-17 17:22 | t-ishii | Status | assigned => feedback |
| 2013-05-18 02:48 | wms | Note Added: 0000289 | |
| 2013-05-18 02:48 | wms | Status | feedback => assigned |
| 2013-05-18 06:51 | t-ishii | Status | assigned => resolved |
| 2013-06-12 20:01 | t-ishii | Changeset attached | => pgpool2 master 2b4b8370 |
