Re: Bug or feature in AFTER INSERT trigger?

>
> 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(at)gmail(dot)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(at)gmail(dot)com');

Then the valid_from gets value from default expression.

depesz