Skip site navigation (1) Skip section navigation (2)

Closing down EvalPlanQual

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Vadim Mikheev <vadim4o(at)email(dot)com>
Cc: pgsql-hackers(at)postgreSQL(dot)org
Subject: Closing down EvalPlanQual
Date: 2000-04-07 01:07:26
Message-ID: 1666.955069646@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-hackers
I just repaired (I hope) a resource leakage problem in execMain.c.
When EvalPlanQual is used, any subplans it creates need to be shut down
at the end of the query to release their resources.  There was no code
to do that, so I created a new function EndEvalPlanQual to do it.

Vadim, this seems to be your code --- would you look to see if the
addition is correct or not?

If you need a test case, here is one starting from the regression
test database:

create table foo (unique1 int, instock int);

insert into foo select unique1, 1000 from tenk1;

create index fooi on foo(unique1);

Make sure you get a double indexscan nestloop plan from this:

explain
update foo set instock=instock-1 where
foo.unique1 = tenk1.unique1 and tenk1.unique2 = 4444;

NOTICE:  QUERY PLAN:

Nested Loop  (cost=0.00..62.83 rows=1 width=18)
  ->  Index Scan using tenk1_unique2 on tenk1  (cost=0.00..2.07 rows=1 width=4)
  ->  Index Scan using fooi on foo  (cost=0.00..59.52 rows=100 width=14)

EXPLAIN

Now, executing that query by itself works fine:

update foo set instock=instock-1 where
foo.unique1 = tenk1.unique1 and tenk1.unique2 = 4444;
UPDATE 1

Start up a second psql, and in it start a transaction with the same query:

begin;
update foo set instock=instock-1 where
foo.unique1 = tenk1.unique1 and tenk1.unique2 = 4444;

(don't commit yet).  Back in the first psql, do the same query:

update foo set instock=instock-1 where
foo.unique1 = tenk1.unique1 and tenk1.unique2 = 4444;

This hangs waiting to see if the other xact will commit or not.
Go back to the other psql and commit:

end;

In the first psql, if not patched, you get

NOTICE:  Buffer Leak: [008] (freeNext=-3, freePrev=-3, relname=tenk1_unique2, blockNum=12, flags=0x4, refcount=1 1)
NOTICE:  Buffer Leak: [058] (freeNext=-3, freePrev=-3, relname=tenk1, blockNum=103, flags=0x4, refcount=1 1)
UPDATE 1

because the indexscan opened by EvalPlanQual is never closed.

(Up till an hour ago, you actually got a coredump because of bogosity
in nodeIndexScan, but I fixed that.  I'm less sure of this fix however.)

			regards, tom lane

pgsql-hackers by date

Next:From: Brian HirtDate: 2000-04-07 01:09:35
Subject: Re: Unique Key Violation 7.0 vs. 6.5.3
Previous:From: Hiroshi InoueDate: 2000-04-07 01:00:17
Subject: RE: 7.0 like selectivity

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group