Thread: Bug #666: vacuum dies when called from plpgsql after large delete
Bug #666: vacuum dies when called from plpgsql after large delete
From
pgsql-bugs@postgresql.org
Date:
Philip Warner (pjw@rhyme.com.au) reports a bug with a severity of 2 The lower the number the more severe it is. Short Description vacuum dies when called from plpgsql after large delete Long Description Large table (> 13M rows), nightly job deletes approx 0.5M rows, then does a vacuum. It looks like the vacuum part is failing.Subsequently going into psql and issuing a vaccum command works. The following is the log from the time of the crash: NOTICE: Vacuuming at 2002-05-11 04:39:01.240766+10 DEBUG: --Relation pg_type-- DEBUG: Pages 3: Changed 1, reaped 1, Empty 0, New 0; Tup 165: Vac 0, Keep/VTL 0/0, UnUsed 4, MinLen 106, MaxLen 106; Re-using:Free /Avail. Space 6020/6020; EndEmpty/Avail. Pages 0/3. CPU 0.00s/0.00u sec elapsed 0.03 sec. DEBUG: Index pg_type_oid_index: Pages 2; Tuples 165: Deleted 0. CPU 0.00s/0.00u sec elapsed 0.01 sec. DEBUG: Index pg_type_typname_index: Pages 2; Tuples 165: Deleted 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. DEBUG: Rel pg_type: Pages: 3 --> 3; Tuple(s) moved: 0. CPU 0.00s/0.00u sec elapsed 0.00 sec. NOTICE: Analyzing pg_type DEBUG: server process (pid 63200) was terminated by signal 11 OS is FreeBSD 4.2, PG Version 7.2.1. Backtrace is: #0 0x80e5734 in length () #1 0x80c3ce7 in analyze_rel () #2 0x80bd91d in vacuum () #3 0x81255a1 in ProcessUtility () #4 0x80e0161 in _SPI_execute_plan () #5 0x80dec06 in SPI_execp () #6 0x2835db2f in plpgsql_exec_trigger () from /usr/local/pgsql-7.2.1/lib/plpgsql.so #7 0x2835c8f2 in plpgsql_exec_trigger () from /usr/local/pgsql-7.2.1/lib/plpgsql.so #8 0x2835c756 in plpgsql_exec_trigger () from /usr/local/pgsql-7.2.1/lib/plpgsql.so #9 0x2835c6b0 in plpgsql_exec_trigger () from /usr/local/pgsql-7.2.1/lib/plpgsql.so #10 0x2835bcc5 in plpgsql_exec_function () from /usr/local/pgsql-7.2.1/lib/plpgsql.so #11 0x28359c8d in plpgsql_call_handler () from /usr/local/pgsql-7.2.1/lib/plpgsql.so #12 0x80d50c6 in ExecMakeFunctionResult () #13 0x80d5186 in ExecEvalFunc () #14 0x80d5791 in ExecEvalExpr () #15 0x80d5a69 in ExecTargetList () #16 0x80d5cf3 in ExecProject () #17 0x80db907 in ExecResult () #18 0x80d3ef5 in ExecProcNode () #19 0x80d2cd2 in ExecutePlan () #20 0x80d2256 in ExecutorRun () #21 0x8124b22 in ProcessQuery () #22 0x81233b0 in pg_exec_query_string () #23 0x81243fe in PostgresMain () #24 0x81083b8 in DoBackend () #25 0x8107cd9 in BackendStartup () #26 0x8106ef7 in ServerLoop () #27 0x8106a6d in PostmasterMain () #28 0x80e5349 in main () #29 0x806652f in _start () Any suggestions as to how to track this down further would be appreciated. Sample Code No file was uploaded with this report
pgsql-bugs@postgresql.org writes: > vacuum dies when called from plpgsql after large delete VACUUM from plpgsql doesn't work period, because of transaction/memory management issues. I believe we recently accepted a patch to reject attempts to do it. regards, tom lane