From: Ben Morrow <ben@morrow.me.uk>
To: adam.mailinglists@gmail.com
To: pgsql-sql@postgresql.org
Subject: Re: Perform Function When The Rows Of A View Change
Date: Sun, 17 Feb 2013 10:50:59 +0000
Message-ID: <20130217105055.GA29128@anubis.morrow.me.uk> (raw)
In-Reply-To: <CABoSxBq2hsb38k+JZqtmhuE63=cibSVoO3DUvVaM=xOhA_db-Q@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
Quoth adam.mailinglists@gmail.com (Adam):
>
> I have a rather complicated view that is dependent upon multiple
> tables, consisting of several windowing and aggregate functions, as
> well as some time intervals. I would like to be able to perform a
> function, i.e. pg_notify(), whenever a row is added, changed, or
> removed from the view's result set.
>
> I think the kicker is the fact that the set of results returned by the
> view is dependent on the current time.
>
> Here's a simplified version of what's going on:
>
> CREATE VIEW view2 AS (
> SELECT view1.id, view1.ts
> FROM view1
> WHERE view1.ts > (now() - '1 day'::interval)
> );
>
> As such, even if there are no inserts, deletes, or updates performed
> on any of the tables that view1 depends on, the data contained in
> view2 will change as a function of time (i.e. rows will disappear
> from the view as time elapses). I have been unable to come up with a
> trigger or rule that can detect this situation and provide the
> notification I'm looking for.
>
> I could just query the view over and over again, and look for changes
> as they occur. But I'm hoping to find a more elegant (and less
> resource-intensive) solution. Any ideas?
Well, in principle you could calculate the next time the view will
change assuming the tables don't change first, and have a client sit
there sleeping until that time. For instance, the view you posted will
next change at
select min(t.ts)
from (
select view1.ts + '1 day'::interval "ts"
from view1
) t
where t.ts > now()
unless the tables view1 is based on change first.
Apart from the potential difficulty calculating that time, you would
need to be able to wake up that client early if one of the tables
changed. Setting triggers on the tables to send a notify to that client
(probably a different notify from the one that client then sends out to
other clients) should be sufficient, as long as that client uses
select(2) and PQconsumeInput to make sure it receives the notifications
in a timely fashion.
Ben
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: ben@morrow.me.uk, adam.mailinglists@gmail.com
Subject: Re: Perform Function When The Rows Of A View Change
In-Reply-To: <20130217105055.GA29128@anubis.morrow.me.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox