Hi!
I have a followup to this thread.
George, thanks for all the help. I spend some time now investigating
various approaches here and I am reporting here some findings, so that
they might help others as well.
First, I have tried the approach with MATERIALIZED VIEWs to hold the
cached contents o
Hi!
I started a patch to address this limitation. [1]
[1]
https://www.postgresql.org/message-id/CAKLmikMN%2B0W79A7Wg4krK55cTLxb%3DTARdz2b%3Dvqp19ao6AAZjw%40mail.gmail.com
Mitar
On Mon, Dec 24, 2018 at 1:38 AM Mitar wrote:
>
> Hi!
>
> On Mon, Dec 24, 2018 at 1:31 AM George Neuner wrote:
> >
Hi!
On Mon, Dec 24, 2018 at 1:31 AM George Neuner wrote:
> Did you execute the view or simply define it? I think the table is
> created the 1st time the view is executed.
View is populated with data and I can select from it. Still, I cannot
find any table which would look like it in pg_tables?
On Mon, 24 Dec 2018 01:16:53 -0800, Mitar wrote:
>
>I am unable to find cache table's name. :-(
>
Did you execute the view or simply define it? I think the table is
created the 1st time the view is executed.
George
Hi!
I am unable to find cache table's name. :-(
Mitar
On Mon, Dec 24, 2018 at 1:01 AM Mitar wrote:
>
> Hi!
>
> On Sun, Dec 23, 2018 at 11:59 PM George Neuner wrote:
> > IIRC the cache table's name is generated. I don't know the proper
> > incantations to get it from the catalogs, but an easy
Hi!
On Sun, Dec 23, 2018 at 11:59 PM George Neuner wrote:
> IIRC the cache table's name is generated. I don't know the proper
> incantations to get it from the catalogs, but an easy way to find it
> is to put the materialized view into its own tablespace, then search
> pg_tables for objects in t
Hi!
On Mon, Dec 24, 2018 at 12:20 AM George Neuner wrote:
> Also remember that you may have to deal with DELETEd rows. If you
> can't send row data by NOTIFY, then the client HAS to cache the whole
> view anyway to see what's been deleted.
Client-side (web browser) is caching the view (because i
On Sun, 23 Dec 2018 10:10:50 -0800, Mitar wrote:
>I can then wire triggers on underlying tables to REFRESH materialized
>views automatically. Is there some easy way to debounce those
>refreshes? If I just blindly trigger REFRESH in every row trigger,
>this could do a lot of work. I would prefer t
On Sun, 23 Dec 2018 23:06:51 -0800, Mitar wrote:
>On Fri, Dec 21, 2018 at 11:10 PM George Neuner wrote:
>> A materialized view IS exactly such a deliberate cache of results from
>> applying a view. It is a real table that can be monitored for changes
>> using INSERT, UPDATE and/or DELETE trigge
Hi!
On Fri, Dec 21, 2018 at 11:10 PM George Neuner wrote:
> A materialized view IS exactly such a deliberate cache of results from
> applying a view. It is a real table that can be monitored for changes
> using INSERT, UPDATE and/or DELETE triggers.
Are you sure one can use triggers on a materi
Hi!
On Sun, Dec 23, 2018 at 1:00 AM George Neuner wrote:
> A spreadsheet is an order of magnitude simpler to implement than a
> DBMS, but the combination would be an order of magnitude (or more)
> harder. Even object graph databases don't offer spreadsheet
> functionality, and it would be a lot
On Fri, 21 Dec 2018 23:41:16 -0800, Mitar wrote:
>Hi!
>
>On Fri, Dec 21, 2018 at 11:10 PM George Neuner wrote:
>> A materialized view IS exactly such a deliberate cache of results from
>> applying a view. It is a real table that can be monitored for changes
>> using INSERT, UPDATE and/or DELETE
22, 2018 1:21:49 AM
> *To:* Kevin Brannen
> *Cc:* pgsql-general@lists.postgresql.org
> *Subject:* Re: Watching for view changes
>
> Hi!
>
> On Thu, Dec 20, 2018 at 12:54 PM Kevin Brannen wrote:
>
>> Hmm, I guess I could see that as long as the DB wasn't too [write] busy
wrote:
>
> Hi, perhaps you can use triggers for some tables.
> Regards.
>
> Obtener Outlook para Android <https://aka.ms/ghei36>
> From: Mitar
> Sent: Saturday, December 22, 2018 1:21:49 AM
> To: Kevin Brannen
> Cc: pgsql-general@lists.postgresql.org
> Subje
Hi, perhaps you can use triggers for some tables.
Regards.
Obtener Outlook para Android<https://aka.ms/ghei36>
From: Mitar
Sent: Saturday, December 22, 2018 1:21:49 AM
To: Kevin Brannen
Cc: pgsql-general@lists.postgresql.org
Subject: Re: Watching fo
Hi!
On Fri, Dec 21, 2018 at 11:10 PM George Neuner wrote:
> A materialized view IS exactly such a deliberate cache of results from
> applying a view. It is a real table that can be monitored for changes
> using INSERT, UPDATE and/or DELETE triggers.
Caching is needed if you want to compute a di
On Fri, 21 Dec 2018 20:49:23 -0800, Mitar wrote:
>On Thu, Dec 20, 2018 at 6:28 PM George Neuner wrote:
>
>> The difficulty is that views are not guaranteed to be updateable.
>
>What you mean by that? I mean, just to be clear. I care only about
>read-only views. The changes to views I care about
Hi!
Thanks for more responses.
On Thu, Dec 20, 2018 at 6:28 PM George Neuner wrote:
> >You mean that for every SQL query I am making to a database, I would
> >manually determine which tables are involved and then setup triggers
> >with NOTIFY that the SELECT might have changed?
>
> You can just
Hi!
On Thu, Dec 20, 2018 at 12:54 PM Kevin Brannen wrote:
> Hmm, I guess I could see that as long as the DB wasn't too [write] busy,
> else you'd be flooded with notifications.
>
Sure. But this could be resolved by allowing notifications to be batched
together. Debounce them. So could maybe con
On Thu, 20 Dec 2018 12:12:14 -0800, Mitar wrote:
>On Thu, Dec 20, 2018 at 8:11 AM Mike Rylander wrote:
>> If, as I assume, the FROM clause of the SELECT is essentially static,
>> just with varying WHERE conditions, you could just use an AFTER
>> INSERT/UPDATE/DELETE trigger on each of the table
On 12/20/18 2:20 PM, Mitar wrote:
Hi!
On Thu, Dec 20, 2018 at 12:07 PM Rob Sargent wrote:
Are you hoping to see the difference in the returned values for successive
calls to the same query?
i would like to in as close to real-time as possible get notification
when results of a query would ha
From: Mitar
> On Thu, Dec 20, 2018 at 12:07 PM Rob Sargent wrote:
> > Are you hoping to see the difference in the returned values for successive
> > calls to the same query?
>
> i would like to in as close to real-time as possible get notification when
> results of a query would have changed i
Hi!
On Thu, Dec 20, 2018 at 12:07 PM Rob Sargent wrote:
> Are you hoping to see the difference in the returned values for successive
> calls to the same query?
i would like to in as close to real-time as possible get notification
when results of a query would have changed if I would do the same
Hi!
On Thu, Dec 20, 2018 at 8:11 AM Mike Rylander wrote:
> If, as I assume, the FROM clause of the SELECT is essentially static,
> just with varying WHERE conditions, you could just use an AFTER
> INSERT/UPDATE/DELETE trigger on each of the tables involved to send a
> NOTIFY whenever data that is
> On Dec 20, 2018, at 1:04 PM, Mitar wrote:
>
> Hi!
>
> Ah, that was a thread about table changes. I am interested in data
> changes (results from a query).
>
> Thanks.
>
Are you hoping to see the difference in the returned values for successive
calls to the same query?
Hi!
Ah, that was a thread about table changes. I am interested in data
changes (results from a query).
Thanks.
Mitar
On Thu, Dec 20, 2018 at 8:01 AM Adrian Klaver wrote:
>
> On 12/20/18 7:54 AM, Mitar wrote:
> > Hi!
> >
> > Thanks. Care for a link or at least thread subject?
>
> https://www.p
On Thu, Dec 20, 2018 at 4:17 AM Mitar wrote:
>
> Hi!
>
> I am trying to see how I could use NOTIFY/LISTEN to watch changes of a
> complicated SELECT query which spans multiple tables. Ideally, I would
> like to leave to PostgreSQL to determine when some data (and which
> data) in the result of the
On 12/20/18 7:54 AM, Mitar wrote:
Hi!
Thanks. Care for a link or at least thread subject?
https://www.postgresql.org/message-id/CA%2BFnnTxqScf6mDw_7HLMfu7YTagPrsYUw-bc%3DOKsBQ0TqprvEA%40mail.gmail.com
Also, Googling around this seems a pretty popular request. I am quite
surprised that there
Hi!
Thanks. Care for a link or at least thread subject?
Also, Googling around this seems a pretty popular request. I am quite
surprised that there is not something out-of-box available for this,
efficient and scalable.
Mitar
On Thu, Dec 20, 2018 at 7:33 AM Ron wrote:
>
> On 12/20/18 3:17 AM,
On 12/20/18 3:17 AM, Mitar wrote:
Hi!
I am trying to see how I could use NOTIFY/LISTEN to watch changes of a
complicated SELECT query which spans multiple tables. Ideally, I would
like to leave to PostgreSQL to determine when some data (and which
data) in the result of the SELECT query has chang
Hi!
I am trying to see how I could use NOTIFY/LISTEN to watch changes of a
complicated SELECT query which spans multiple tables. Ideally, I would
like to leave to PostgreSQL to determine when some data (and which
data) in the result of the SELECT query has changed. So I am thinking
that creating a
31 matches
Mail list logo
