Re: Again, sorry, caching.

Greg Copeland wrote:
> At this point in time, I think we've both pretty well beat this topic
> up. Obviously there are two primary ways of viewing the situation. I
> don't think anyone is saying it's a bad idea...I think everyone is
> saying that it's easier to address elsewhere and that overall, the net
> returns may be at the expense of some other work loads. So, unless
> there are new pearls to be shared and gleaned, I think the topics been
> fairly well addressed. Does more need to said?

With a PREPARE/EXECUTE patch now out for approval, can I assume we will
go with that first and see how far it gets us, and then revisit the idea
of cached results. In this case, we are caching the query plan. The
query still executes again in the executor, so the data is always fresh.
In a sense, the buffer cache and disk are the caches, which don't need
separate invalidation if some data changes in the table.

The plan can get invalid if it calls a non-cachable function or the
schema changes, or the constants used to generate the plan in the
optimizer would generate a different plan from the constants used in a
later query, or the analyze statistics changed.

The MVCC ramifications of cached results and invalidation could be quite
complex. The commit of a transaction could change tuple visibility
rules even if the data modify statement was executed much earlier in the
transaction.

Also, on the NOTIFY/trigger idea, triggers are called on statement end,
not transaction end, so if an UPDATE query is in a multi-statement
transaction, another backend looking for the NOTIFY will receive it
before the transaction commits, meaning it will not see the update.
That seems like a problem. We do have deferrable constraints which will
only do checking on transaction end, but I am not sure if that can be
used for NOTIFY on transaction commit. Anyone?

--
Bruce Momjian | http://candle.pha.pa.us
pgman(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026