Bug or feature in AFTER INSERT trigger?

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();