Hi Tom,
> Because the trigger function is plpgsql, this could happen only the
> first time the trigger is fired in a particular session (unless you are
> using EXECUTE to invoke the update command?). If the problem is related
> to the planner constant-folding environment, then the first-time-only
> limitation would help make it hard to reproduce.
It looks like your suspicion was right.... I've isolated one particulare case,
simply run the script attached. If you remove the comment before the extra
call of spawn_pol() at the bottom of the script, the error is avoided.
Hopefully, solving this will also solve the other cases of mem-alloc error
I've been seeing ;) I'm not using spawn_pol/sol() during the earlier
mentioned conversion, but my hope is that the error generated during
conversion will turn out to be related and caused by the constant-folding of
some other function. But I'll know soon enough if it isn't ;)
Looking forward to your assesment.
--
Best,
Frank.
-- ****************************************************************
-- ** DROP THE LOT
-- ****************************************************************
DROP TABLE "purchaseorder_line" CASCADE;
DROP SEQUENCE "purchaseorder_line_seq" CASCADE;
DROP TABLE "purchaseorder_line_status" CASCADE;
DROP TABLE "purchaseorder" CASCADE;
DROP SEQUENCE "purchaseorder_seq" CASCADE;
DROP TABLE "purchaseorder_status" CASCADE;
DROP FUNCTION po_stat(varchar) CASCADE;
DROP FUNCTION pol_stat(varchar) CASCADE;
DROP FUNCTION spawn_pol(int, int);
DROP TYPE spawned_pol_type;
DROP FUNCTION tr_purchaseorder_def() CASCADE;
DROP LANGUAGE 'plpgsql' CASCADE;
DROP FUNCTION plpgsql_call_handler() CASCADE;
CREATE FUNCTION plpgsql_call_handler() RETURNS LANGUAGE_HANDLER AS
'$libdir/plpgsql' LANGUAGE C;
-- ****************************************************************
-- ** CREATE THE LOT
-- ****************************************************************
CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql
HANDLER plpgsql_call_handler;
CREATE TABLE "purchaseorder_status" (
"id" int primary key check(id > 0),
"abbreviation" varchar(20) not null unique,
"descr" varchar(30) not null
);
COPY "purchaseorder_status" FROM stdin;
1 PO_ONE one
2 PO_TWO two
3 PO_THREE three
4 PO_FOUR four
\.
CREATE SEQUENCE purchaseorder_seq start 3;
CREATE TABLE "purchaseorder" (
"id" int primary key check(id > 0),
"status_id" int not null references purchaseorder_status(id),
"price_total" numeric not null check(price_total >= 0)
);
COPY "purchaseorder" FROM stdin;
1 1 0
2 1 0
\.
CREATE TABLE "purchaseorder_line_status" (
"id" int primary key check(id > 0),
"abbreviation" varchar(20) not null unique,
"descr" varchar(30) not null
);
COPY "purchaseorder_line_status" FROM stdin;
1 POL_ONE one
2 POL_TWO two
3 POL_THREE three
4 POL_FOUR four
\.
CREATE SEQUENCE purchaseorder_line_seq start 6;
CREATE TABLE "purchaseorder_line" (
"id" int primary key check(id > 0),
"purchaseorder_id" int not null references purchaseorder(id),
"status_id" int not null references purchaseorder_line_status(id),
"amount" int not null check(amount > 0),
"price" numeric check(price > 0)
);
COPY "purchaseorder_line" FROM stdin;
1 1 1 99 9.99
2 1 3 99 9.99
3 2 1 99 9.99
4 2 2 99 9.99
5 2 4 99 9.99
\.
CREATE OR REPLACE FUNCTION po_stat(varchar)
RETURNS int
LANGUAGE 'sql'
IMMUTABLE
STRICT
SECURITY INVOKER
AS ' SELECT id FROM purchaseorder_status WHERE abbreviation = $1';
CREATE OR REPLACE FUNCTION pol_stat(varchar)
RETURNS int
LANGUAGE 'sql'
IMMUTABLE
STRICT
SECURITY INVOKER
AS ' SELECT id FROM purchaseorder_line_status WHERE abbreviation =
$1';
CREATE TYPE spawned_pol_type AS (pol_id_orig int, pol_id_new int);
CREATE OR REPLACE FUNCTION spawn_pol(integer, integer)
RETURNS SETOF spawned_pol_type
LANGUAGE 'plpgsql'
VOLATILE
STRICT
SECURITY INVOKER
AS ' DECLARE
my_id ALIAS FOR $1;
spawned_amount ALIAS FOR $2;
result_size INTEGER;
pol RECORD;
result spawned_pol_type%ROWTYPE;
BEGIN
-- select the orderline to spawn
SELECT into pol * FROM purchaseorder_line WHERE id =
my_id FOR UPDATE;
result.pol_id_orig = my_id;
-- our amount must be high enough to be able to spawn
IF spawned_amount >= pol.amount THEN
RAISE EXCEPTION ''The original amount % in line
% is not high enough to be able to spawn amount %'', pol.amount,
spawned_amount, my_id;
RETURN;
END IF;
-- we have enough stock, insert the new orderline and
update the original one
INSERT INTO purchaseorder_line SELECT
nextval(''purchaseorder_line_seq''),
-- id
purchaseorder_id,
status_id,
spawned_amount,
-- amount,
price
FROM purchaseorder_line WHERE id = my_id;
result.pol_id_new = currval(''purchaseorder_line_seq'');
UPDATE purchaseorder_line SET amount = amount -
spawned_amount WHERE id = my_id;
-- finish
RETURN NEXT result;
RETURN;
END;';
CREATE or replace FUNCTION tr_purchaseorder_def()
RETURNS trigger
LANGUAGE 'plpgsql'
VOLATILE
STRICT
SECURITY INVOKER
AS ' DECLARE
my_purchaseorder_id INTEGER := 0;
BEGIN
-- how are we called?
IF TG_OP = ''INSERT'' THEN
my_purchaseorder_id = NEW.purchaseorder_id;
ELSIF TG_OP = ''UPDATE'' THEN
my_purchaseorder_id = NEW.purchaseorder_id;
ELSE -- TG_OP = ''DELETE''
my_purchaseorder_id = OLD.purchaseorder_id;
END IF;
-- why are we processing?
RAISE NOTICE ''tr_purchaseorder_def() was triggered by
the % on % for id %'', TG_OP, TG_RELNAME, my_purchaseorder_id;
-- update the purchaseorder
UPDATE purchaseorder SET
price_total = total,
status_id = CASE
WHEN count_lines = 0 OR count_pol_one >
0 THEN po_stat(''PO_ONE'')
WHEN count_pol_two > 0 THEN
po_stat(''PO_TWO'')
WHEN count_pol_three > 0 THEN
po_stat(''PO_THREE'')
WHEN count_pol_four = count_lines THEN
po_stat(''PO_FOUR'')
ELSE po_stat(''PO_FOUR'') END
FROM (
SELECT
purchaseorder_id,
coalesce(SUM(price * amount), 0) AS
total,
COUNT(purchaseorder_line.id) AS
count_lines,
COUNT(CASE WHEN status_id =
pol_stat(''POL_ONE'') THEN 1 ELSE null END) AS count_pol_one,
COUNT(CASE WHEN status_id =
pol_stat(''POL_TWO'') THEN 1 ELSE null END) AS count_pol_two,
COUNT(CASE WHEN status_id =
pol_stat(''POL_THREE'') THEN 1 ELSE null END) AS count_pol_three,
COUNT(CASE WHEN status_id =
pol_stat(''POL_FOUR'') THEN 1 ELSE null END) AS count_pol_four
FROM purchaseorder_line
WHERE purchaseorder_id = my_purchaseorder_id
GROUP BY purchaseorder_id
) AS foo
WHERE id = purchaseorder_id;
RETURN NULL;
END;';
CREATE CONSTRAINT TRIGGER purchaseorder_def AFTER INSERT OR UPDATE OR DELETE ON
purchaseorder_line DEFERRABLE INITIALLY DEFERRED FOR EACH ROW EXECUTE PROCEDURE
tr_purchaseorder_def();
-- ****************************************************************
-- ** DEFY THE LOT
-- ****************************************************************
-- calling spawn_pol() at least once will avoid the error
--select 1 from spawn_pol(1, 9);
-- this will fail when the line above is commented out
begin;
select 1 from spawn_pol(2, 9);
update purchaseorder_line set amount = amount where id =
currval('purchaseorder_line_seq');
commit;
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
