Re: BUG #3483: Dropped temporary tables filled up the disk

"Tom Lane" <tgl ( at ) sss ( dot ) pgh ( dot ) pa ( dot ) us> writes:

> for Thread B that's a bit out of the ordinary. Can you show us anything
> of that code, or more accurately the SQL it's using?
The company that I work for is very paranoid about sharing specific
information about what we do with the outside world. Therefore, I have
changed the table and column names to protect the innocent.

The table definition that session B is using is:

Column | Type | Modifiers
--------------+-----------------------+--------------------
col1 | character varying(64) | not null
col2 | integer | not null
col3 | character varying(64) |
col4 | bigint | not null default 0
col5 | integer | not null default 0
col6 | integer | not null
col7 | bytea |
col8 | date |
col9 | integer | not null default 0
col10 | bigint | not null default 0
col11 | bytea |
Indexes:
"foo_pkey" PRIMARY KEY, btree (col1)
"foo_col9ix" btree (col9)
"foo_col10ix" btree (col10)
"foo_col5ix" btree (col5)
Rules:
delrule_foo20070716 AS
ON DELETE TO foo
WHERE old.col8 < '2007-07-17'::date DO INSTEAD DELETE FROM foo20070716
WHERE foo20070716.col1::text = old.col1::text
delrule_foo20070717 AS
ON DELETE TO foo
WHERE old.col8 >= '2007-07-17'::date AND old.col8 < '2007-07-18'::date DO
INSTEAD DELETE FROM foo20070717
WHERE foo20070717.col1::text = old.col1::text
delrule_foo20070718 AS
ON DELETE TO foo
WHERE old.col8 >= '2007-07-18'::date DO INSTEAD DELETE FROM foo20070718
WHERE foo20070718.col1::text = old.col1::text
insrule_foo20070716 AS
ON INSERT TO foo
WHERE new.col8 < '2007-07-17'::date DO INSTEAD INSERT INTO foo20070716
(col1, col2, col3, col4, col5, "location", col7, col8, col9, col10, col11)
VALUES (new.col1, new.col2, new.col3, new.col4, new.col5, new."col6",
new.col7, new.col8, new.col9, new.col10, new.col11)
insrule_foo20070717 AS
ON INSERT TO foo
WHERE new.col8 >= '2007-07-17'::date AND new.col8 < '2007-07-18'::date DO
INSTEAD INSERT INTO foo20070717 (col1, col2, charset, col4, col5, "col6",
col7, col8, col9, col10, col11)
VALUES (new.col1, new.col2, new.col3, new.col4, new.col5 , new."col6",
new.col7, new.col8, new.col9, new.col10, new.col11)
insrule_foo20070718 AS
ON INSERT TO foo
WHERE new.col8 >= '2007-07-18'::date DO INSTEAD INSERT INTO foo20070718
(col1, col2, col3, col4, col5, "location", col7, col8, col9, col10, col11)
VALUES (new.col1, new.col2, new.col3, new.col4, new.col5, new."col6",
new.col7, new.col8, new.col9, new.col10, new.col11)
updrule_foo20070716 AS
ON UPDATE TO foo
WHERE old.col8 < '2007-07-17'::date DO INSTEAD UPDATE foo20070716 SET
col1 = new.col1, col2 = new.col2, col3 = new.charset, col4 = new.col4, col5
= new.col5, "col6" = new."col6", col7 = new.col7, col8 = new.col8, col9 =
new.indexed, col10 = new.col10, col11 = new.col11
WHERE foo20070716.col1::text = old.col1::text
updrule_foo20070717 AS
ON UPDATE TO foo
WHERE old.col8 >= '2007-07-17'::date AND old.col8 < '2007-07-18'::date DO
INSTEAD UPDATE foo20070717 SET col1 = new.col1, datatype = new.col2, col3 =
new.col3, col4 = new.col4, col5 = new.col5, "col6" = new."col6", col7 =
new.col7, partition = new.col8, col9 = new.col9, col10 = new.col10, col11 =
new.col11
WHERE foo20070717.col1::text = old.col1::text
updrule_foo20070718 AS
ON UPDATE TO foo
WHERE old.col8 >= '2007-07-18'::date DO INSTEAD UPDATE foo20070718 SET
col1 = new.col1, col2 = new.col2, col3 = new.charset, col4 = new.col4, col5
= new.col5, "col6" = new."col6", col7 = new.col7, col8 = new.col8, col9 =
new.indexed, col10 = new.col10, col11 = new.col11
WHERE foo20070718.col1::text = old.col1::text

Session B loops on the following SQL statements with a one minute delay
between each loop:

BEGIN;
SELECT col1,col2,col3,col4,col5,col6,col8,col9 FROM foo WHERE col5 = $1 AND
col8 >= '20070716' LIMIT 100;
-- The following is repeated as many times as there are rows from the above
select that need to be updated
UPDATE foo SET col4 = $1, col5 = $2 WHERE col1 = $3 AND col8 = $4;
COMMIT;

The following is a snippet from the postgres log showing the loop session B
was in as we were trying to figure out how we got into this situation:

2007-07-23 15:46:06 MDT [30061]LOG: duration: 0.005 ms statement: EXECUTE
<unnamed> [PREPARE: BEGIN]
2007-07-23 15:46:06 MDT [30061]LOG: duration: 2.417 ms statement: EXECUTE
<unnamed> [PREPARE: SELECT col1,col2,col3,col4,col5,col6,col8,col9 FROM
foo WHERE col5 = $1 AND col8 >= '20070716' LIMIT 100]
2007-07-23 15:46:06 MDT [30061]LOG: duration: 0.055 ms statement: EXECUTE
<unnamed> [PREPARE: COMMIT]
2007-07-23 15:47:06 MDT [30061]LOG: duration: 0.007 ms statement: EXECUTE
<unnamed> [PREPARE: BEGIN]
2007-07-23 15:47:06 MDT [30061]LOG: duration: 2.605 ms statement: EXECUTE
<unnamed> [PREPARE: SELECT col1,col2,col3,col4,col5,col6,col8,col9 FROM
foo WHERE col5 = $1 AND col8 >= '20070716' LIMIT 100]
2007-07-23 15:47:06 MDT [30061]LOG: duration: 0.032 ms statement: EXECUTE
<unnamed> [PREPARE: COMMIT]
2007-07-23 15:47:06 MDT [30061]LOG: duration: 0.020 ms statement: EXECUTE
<unnamed> [PREPARE: BEGIN]
2007-07-23 15:47:06 MDT [30061]LOG: duration: 2.325 ms statement: EXECUTE
<unnamed> [PREPARE: SELECT col1,col2,col3,col4,col5,col6,col8,col9 FROM
foo WHERE col5 = $1 AND col8 >= '20070716' LIMIT 100]
2007-07-23 15:47:06 MDT [30061]LOG: duration: 0.041 ms statement: EXECUTE
<unnamed> [PREPARE: COMMIT]
2007-07-23 15:48:06 MDT [30061]LOG: duration: 0.008 ms statement: EXECUTE
<unnamed> [PREPARE: BEGIN]
2007-07-23 15:48:06 MDT [30061]LOG: duration: 2.537 ms statement: EXECUTE
<unnamed> [PREPARE: SELECT col1,col2,col3,col4,col5,col6,col8,col9 FROM
foo WHERE col5 = $1 AND col8 >= '20070716' LIMIT 100]
2007-07-23 15:48:06 MDT [30061]LOG: duration: 0.032 ms statement: EXECUTE
<unnamed> [PREPARE: COMMIT]
2007-07-23 15:48:06 MDT [30061]LOG: duration: 0.021 ms statement: EXECUTE
<unnamed> [PREPARE: BEGIN]
2007-07-23 15:48:06 MDT [30061]LOG: duration: 2.551 ms statement: EXECUTE
<unnamed> [PREPARE: SELECT col1,col2,col3,col4,col5,col6,col8,col9 FROM
foo WHERE col5 = $1 AND col8 >= '20070716' LIMIT 100]
2007-07-23 15:48:06 MDT [30061]LOG: duration: 0.043 ms statement: EXECUTE
<unnamed> [PREPARE: COMMIT]

However, I was able to get the same results by following the steps I had
listed in the bug report. Were you not able to duplicate this by following
those steps?

Thanks for you help.

Brent