Bug involving SPs and PDQ
Evil, ugly bug alert! When using PDQ inside a stored procedure, the threads
and resources NEVER go away, even after SP execution completes, until the
SESSION dies. Run the same procedure several times in the same session, the
threads and memory will accumulate!
I'm looking to see if anyone can confirm this bug on platforms other than
Solaris. I have personally confirmed the bug in 7.30.UC3 and 7.30.UC6, and
the bug did NOT occur in 7.24.UC2. Informix Tech Support claims they have
confirmed the behavior in version 7.24.UC5, but I don't have that version,
so I can't confirm this.
Also, if anyone can think of any work arounds to the problem, I'm all ears.
Our processes are (obviously) a LOT more complex than the examples given
below, so redeveloping in ESQL/C is not an option that I'm excited about
(although it may come down to that).
Here's how to replicate:
Here is an easy-to-replicate test case of our problem using the stores7
database. You'll need two windows open at once.
Go into dbaccess against stores7, and run the following SQL:
SET PDQPRIORITY HIGH;
SELECT c.customer_num, c.lname, c.fname, o.order_num,
SUM(i.total_price) AS dollars
FROM customer AS c, orders AS o, items AS i
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
GROUP BY 1, 2, 3, 4
ORDER BY 1, 4;
SET PDQPRIORITY OFF;
SELECT *
FROM items;
It's important that all of this be set up as one script. Now hit <R> for
run. While the data from the first select is on your screen, go to your
second window and do an onstat -g ses on your session id. You'll see that
there are several scan and join threads running for your session, and a
memory pool is allocated.
Now, in your dbaccess window, hit <N>ext until the results of the second,
simple query come up. Again, go to your other window and do onstat -g ses
on your session id. You'll see that you're back down to one thread. Your
memory pool hasn't gone away, but it hasn't grown, either.
This is how I expect the engine to behave. On to the counterexample.
Create the following procedure in stores7, making sure PDQPRIORITY is OFF
when you run the CREATE PROCEDURE statement:
CREATE PROCEDURE sp_case829565()
SET PDQPRIORITY HIGH;
SELECT c.customer_num, c.lname, c.fname, o.order_num,
SUM(i.total_price) AS dollars
FROM customer AS c, orders AS o, items AS i
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
GROUP BY 1, 2, 3, 4
ORDER BY 1, 4
INTO TEMP testtemp1;
SET PDQPRIORITY OFF;
SELECT *
FROM items
INTO TEMP testtemp2;
END PROCEDURE;
You might want to exit dbaccess and restart it, just to be sure your session
has cleared itself up. Then execute:
EXECUTE PROCEDURE sp_case829565();
In your other window, do an onstat -g ses on your session id, and you'll see
that all of the threads are still hanging around, even though (A) the
procedure is turned PDQ off, and (B) the procedures execution has completed.
Now here is where it gets REALLY interesting. Create this procedure:
CREATE PROCEDURE sp_case829565_2()
SET PDQPRIORITY HIGH;
SELECT c.customer_num, c.lname, c.fname, o.order_num,
SUM(i.total_price) AS dollars
FROM customer AS c, orders AS o, items AS i
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
GROUP BY 1, 2, 3, 4
ORDER BY 1, 4
INTO TEMP testtemp1 WITH NO LOG;
SET PDQPRIORITY OFF;
SET PDQPRIORITY HIGH;
SELECT c.customer_num, c.lname, c.fname, o.order_num,
SUM(i.total_price) AS dollars
FROM customer AS c, orders AS o, items AS i
WHERE c.customer_num = o.customer_num
AND o.order_num = i.order_num
GROUP BY 1, 2, 3, 4
ORDER BY 1, 4
INTO TEMP testtemp1b WITH NO LOG;
SET PDQPRIORITY OFF;
SELECT *
FROM items
INTO TEMP testtemp2 WITH NO LOG;
END PROCEDURE;
The only difference is that the complicated query is executed twice this
time, into separate temp tables. Execute this procedure using the same
testing techniques as above, and you'll see that THIS procedure has TWICE as
many threads as the first. One set of threads for EACH query run with PDQ.