Hello,
today I encountered strange behaviour in PgSQL 9.0 (tried in 9.3 with
same effect).
There is a table and an AFTER INSERT trigger which call a function which
counts a number of records in the same table. But the newly inserted
record is not selected and counted.
When I delete a record and the very same AFTER trigger calls the very
same function, selected and counted records are already without the
deleted one.
I supposed that after insert the record is already in the database,
isn't it true?!
The documentation doesn't mention this. (or I didn't find it).
Can someone confirm it as a bug or explain why it works this way.
Regards,
Martin Edlman
EXAMPLE:
-- FUNCTION
CREATE OR REPLACE FUNCTION tmp.email_service(contrid integer) RETURNS integer AS
$BODY$
DECLAREsid integer := 119;rec record;vfrom date;vto date;cmnt text;cnt integer := 0;
BEGINvfrom := date_trunc('month', now());vto := date_trunc('month', now() + interval '1 month') - interval '1
day';RAISENOTICE 'sid %, from %, to %', sid, vfrom, vto;FOR rec IN SELECT ma.contract_id, count(ma.*) as unitage,
string_agg(ma.email,',
' order by ma.email) as emails FROM tmp.mail_account as ma WHERE contract_id = contrid AND
coalesce(ma.valid_from,'-infinity') < now() AND coalesce(ma.valid_to, 'infinity') > now() GROUP BY 1LOOP RAISE
NOTICE'number of mails: %, mails: %', rec.unitage, rec.emails; cnt := cnt + 1; -- here is some code which inserts
orupdates -- services ...END LOOP;RETURN cnt;
END
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION tmp.email_service(integer) OWNER TO postgres;
GRANT EXECUTE ON FUNCTION tmp.email_service(integer) TO public;
GRANT EXECUTE ON FUNCTION tmp.email_service(integer) TO postgres;
-- TRIGGER FUNCTION
CREATE OR REPLACE FUNCTION tmp.email_service() RETURNS trigger AS
$BODY$
BEGINIF TG_OP = 'INSERT' THEN RAISE NOTICE '% % email %@% inserted, setting services for id %',
TG_WHEN, TG_OP, NEW.username, NEW.domain, NEW.contract_id; -- call a function PERFORM
tmp.email_service(NEW.contract_id); RETURN NEW;END IF;
IF TG_OP = 'UPDATE' THEN -- RETURN NEW;END IF;
IF TG_OP = 'DELETE' THEN PERFORM tmp.email_service(OLD.contract_id); RETURN OLD;END IF;
RETURN NULL;
END
$BODY$ LANGUAGE plpgsql VOLATILE COST 100;
ALTER FUNCTION tmp.email_service() OWNER TO edlman;
GRANT EXECUTE ON FUNCTION tmp.email_service() TO public;
GRANT EXECUTE ON FUNCTION tmp.email_service() TO edlman;
-- TABLE
CREATE TABLE tmp.mail_account
( id serial NOT NULL, contract_id integer NOT NULL, username character varying(50) NOT NULL, domain character
varying(100)NOT NULL, email character varying(255) NOT NULL, valid_from timestamp without time zone DEFAULT now(),
valid_totimestamp without time zone, CONSTRAINT mail_account_pkey PRIMARY KEY (id)
)
WITH ( OIDS=FALSE
);
ALTER TABLE tmp.mail_account OWNER TO postgres;
CREATE UNIQUE INDEX mail_account_email ON tmp.mail_account USING btree (email COLLATE pg_catalog."default");
CREATE UNIQUE INDEX mail_account_email_idx ON tmp.mail_account USING btree (username COLLATE pg_catalog."default",
domainCOLLATE
pg_catalog."default");
CREATE INDEX mail_account_username_idx ON tmp.mail_account USING btree (username COLLATE pg_catalog."default");
CREATE TRIGGER email_service AFTER INSERT OR UPDATE OR DELETE ON tmp.mail_account FOR EACH ROW EXECUTE PROCEDURE
tmp.email_service();