LISTEN ON table WHERE attr1 LIKE '%abc%';

Hi,

there are already some items in the TODO list regarding LISTEN/NOTIFY, and I
saw the discussion in the thread "Feature request: NOTIFY enhancement" on Jan
2008.

Now I was hoping for a feature that goes even further than the ones discussed
and allows clients to listen on changes in the database that satisfy real
WHERE clauses, if used on tables, but I could imagine that you can listen for
all changes caused by any DDL or DML statement, including CREATE TABLE and
DROP TABLE.

The motivation behind this request is, that I see lots of processes, connected
to databases, that provide features like the one requested (they can only
report changes that are done by clients through these processes, they don't
notice changes done to the database directly), on the abstraction level
of "business objects". These processes are essentially caches, used by
multiple clients.
That all works more or less, but I'd like to see this feature in the database,
so that you can work on the database directly, and processes connected will
notice. And sometimes the sole purpose of these processes is to give that
listen/notify feature to application, sometimes in addition to "make the
database faster". In any case, if multiple processes/clients are directly
connected to the database, consistency of those processes suffers.

The notification message listeners receive should ideally contain complete
information about the changes they registered for, so that if such a cache
process receives it, it doesn't need to query the database again to update
its internal cache.

On a side note, there are a lot of "object caches" springing up, trying to get
rid of databases altogether, like GemStone GemFire, but I consider them, at
least the ones I know, flawed, because apparently only after their conception
and inception, developers seem to realize that they need indexes, ways to
properly query the cache and so forth and they end up developing database
features on top of something that is not a real database. In other words, I
think they are lacking a solid foundation, especially if that foundation
is "collections of arbitrary Java objects". (Of course these products offer
other nice features like high availability and so forth.)

So it would be nice to see the one feature I like about these caches added to
Postgres, without having to resort to tricks that force me out of the (SQL)
language, like rules/triggers.

One problem might be that a couple of connection methods (like I think JDBC)
don't allow for asynchronous communication from database to clients, which is
why clients (when I checked out the LISTEN feature in Postgres) have to poll
for changes, but I think it should be possible and maybe there are already
ways to connect to Postgres that allow this?

Does this all make sense?

Eugen