Re: Bug or feature in AFTER INSERT trigger?

<div dir="ltr"><div class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" style="margin:0px 0px
0px0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"> There is a table and an AFTER INSERT trigger which
calla function which<br /> counts a number of records in the same table. But the newly inserted<br /> record is not
selectedand counted.<br /></blockquote></div><br /></div><div class="gmail_extra">The problem is with your function,
notPg logic.<br /><br /></div><div class="gmail_extra">Namely you have this condition:<br /><br />        AND
coalesce(ma.valid_from,'-infinity') < now()<br />        AND coalesce(ma.valid_to, 'infinity') > now()<br /><br
/></div><divclass="gmail_extra">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.<br /><br /></div><div class="gmail_extra">The thing is now() doesn't change
withintransaction.<br /><br /></div><div class="gmail_extra">So the value of now() that your where compares is
*exactly*the same as the one inserted into row.<br /><br />So, the condition: coalesce(ma.valid_from, '-infinity)
<now()returns false, because it is = now(), and not < now().<br /><br /></div><div class="gmail_extra">If you'd
insertliteral NULL value, for example by doing:<br /><br />INSERT INTO tmp.mail_account(contract_id, username, domain,
email,valid_from, valid_to) VALUES (123, 'depesz', '<a href="http://depesz.com">depesz.com</a>', '<a
href="mailto:depesz@gmail.com">depesz@gmail.com</a>',NULL, NULL);<br /><br /></div><div class="gmail_extra">Then, the
columnwould be null, and coalesce() would return '-infinity', which would give true when comparing with now().<br /><br
/>Butif you insert data like:<br /><br />INSERT INTO tmp.mail_account(contract_id, username, domain, email) VALUES
(123,'depesz', '<a href="http://depesz.com">depesz.com</a>', '<a
href="mailto:depesz@gmail.com">depesz@gmail.com</a>');<br/><br /></div><div class="gmail_extra">Then the valid_from
getsvalue from default expression.<br /><br /></div><div class="gmail_extra">depesz<br /></div></div>