Re: SELECT Query returns empty

Bright D.L. wrote:

> I would like to know why P1 can retrieve the data from the table while
> P2 can't.

At a guess: transactional visibility. P1 will have not yet committed its
transaction, so the data isn't visible to P2 yet. Remember, PostgreSQL
defaults to the READ COMMITTED isolation level and does not offer READ
UNCOMMITTED for those rare situations where you might want it.

By "send a trigger" I'm assuming you mean that you're using RAISE NOTICE
and LISTEN.

If P1 commits, then raises a notice to tell P2 about the changes, P2
should be able to see the changes immediately. Even if P2's transaction
began before it receives the notice it should still see the changes
(because of READ COMMITTED isolation). However, if a function or
statement is running in P2 when P1 commits, the changes made by P1 will
only become visible after that function or statement finishes.

If I'm mistaken in my interpretation and P1 does in fact commit before
notifying P2 of the changes, then there must be something else going on.
Perhaps a bit more detail might help.

--
Craig Ringer