Forum OpenACS Development: Re: on_lob_ref trigger error on PG 9.x

Collapse
Posted by Dave Bauer on
This error occurs when updating the lob column to either NULL or to a new lob_id value.
Collapse
Posted by Dave Bauer on
I tracked this down. The on_lob_ref function needs to be updated so that the function does not update the lob row refcount before ultimately deleting the row because the refcount is 0. If the refcount will end up as 0, just delete the lob row.

The change in postgresql was introduced in version 9.3.

CREATE OR REPLACE FUNCTION on_lob_ref() RETURNS trigger AS $$
declare rc integer;
BEGIN
	if TG_OP = 'UPDATE' then
		if new.lob = old.lob then
			return new;
		end if;
	end if;

	if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then
		if new.lob is not null then
			insert into lobs select new.lob, 0
				where 0 = (select count(*) from lobs where lob_id = new.lob);
			update lobs set refcount = refcount + 1 where lob_id = new.lob;
		end if;
	end if;

	if TG_OP <> 'INSERT' then
		if old.lob is not null then
		    select refcount into rc from lobs where lob_id = old.lob;
		    if rc > 1 then
		        update lobs set refcount = refcount - 1 where lob_id = old.lob;		   else
			delete from lobs where lob_id = old.lob and refcount = 0;		   end if;
		end if;
	end if;

	if TG_OP = 'INSERT' or TG_OP = 'UPDATE' then return new;
	else return old;
	end if;

END;
$$ LANGUAGE plpgsql;