Re: BUG #3483: Dropped temporary tables filled up the disk - Mailing list pgsql-bugs

"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