Message-ID: <545A1F65.6050705@gmail.com>
Date: Wed, 05 Nov 2014 14:00:21 +0100
From: Martin Edlman <martin.edlman@gmail.com>
User-Agent: Mozilla/5.0 (X11; Linux i686;
 rv:31.0) Gecko/20100101 Thunderbird/31.2.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: Bug or feature in AFTER INSERT trigger?
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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$
DECLARE
	sid  integer := 119;
	rec  record;
	vfrom date;
	vto date;
	cmnt text;
	cnt integer := 0;
BEGIN
	vfrom := date_trunc('month', now());
	vto := date_trunc('month', now() + interval '1 month') - interval '1 day';
	RAISE NOTICE '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 1
	LOOP
		RAISE NOTICE 'number of mails: %, mails: %', rec.unitage, rec.emails;
		cnt := cnt + 1;
		-- here is some code which inserts or updates
		-- 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$
BEGIN
	IF 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_to timestamp 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", domain COLLATE
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();


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql