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

Eugen Dueck wrote:
>
> 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.

> 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?

The tricky bits are (1) reliability, (2) payload size and (3)
transactional semantics. You might find the pg-memcache project of some
interest, since it's handling this for the memcached system.
http://pgfoundry.org/projects/pgmemcache/

--
Richard Huxton
Archonet Ltd