agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedBug or feature in AFTER INSERT trigger?
2+ messages / 2 participants
[nested] [flat]
* Bug or feature in AFTER INSERT trigger?
@ 2014-11-05 13:00 Martin Edlman <martin.edlman@gmail.com>
2014-11-05 14:06 ` Re: Bug or feature in AFTER INSERT trigger? hubert depesz lubaczewski <depesz@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Martin Edlman @ 2014-11-05 13:00 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Bug or feature in AFTER INSERT trigger?
2014-11-05 13:00 Bug or feature in AFTER INSERT trigger? Martin Edlman <martin.edlman@gmail.com>
@ 2014-11-05 14:06 ` hubert depesz lubaczewski <depesz@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: hubert depesz lubaczewski @ 2014-11-05 14:06 UTC (permalink / raw)
To: Martin Edlman <martin.edlman@gmail.com>; +Cc: pgsql-sql
>
> 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.
>
The problem is with your function, not Pg logic.
Namely you have this condition:
AND coalesce(ma.valid_from, '-infinity') < now()
AND coalesce(ma.valid_to, 'infinity') > now()
Let's assume you didn't fill in values for valid_from/valid_to. Valid_from,
due to "default" becomes now(). and valid_to null.
The thing is now() doesn't change within transaction.
So the value of now() that your where compares is *exactly* the same as the
one inserted into row.
So, the condition: coalesce(ma.valid_from, '-infinity) <now() returns
false, because it is = now(), and not < now().
If you'd insert literal NULL value, for example by doing:
INSERT INTO tmp.mail_account(contract_id, username, domain, email,
valid_from, valid_to) VALUES (123, 'depesz', 'depesz.com', 'depesz@gmail.com',
NULL, NULL);
Then, the column would be null, and coalesce() would return '-infinity',
which would give true when comparing with now().
But if you insert data like:
INSERT INTO tmp.mail_account(contract_id, username, domain, email) VALUES
(123, 'depesz', 'depesz.com', 'depesz@gmail.com');
Then the valid_from gets value from default expression.
depesz
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2014-11-05 14:06 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-11-05 13:00 Bug or feature in AFTER INSERT trigger? Martin Edlman <martin.edlman@gmail.com>
2014-11-05 14:06 ` hubert depesz lubaczewski <depesz@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox