Re: New feature: cached foreign keys

Reality is crude, seems than this idea is not as good as i think :(

Thanks for answer.

2011/7/9, Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>:
> On 9/07/2011 3:06 PM, pasman pasmański wrote:
>> Hi.
>>
>> Today i have an idea for increase performance of foreign keys. After
>> search parent record, store ctid in shared memory. Subsequent searches
>> look first to the record at stored ctid, and when it is deleted do
>> regular search using index.
>
> How many do you keep cached at a time?
>
> When do you evict the cached ctid from memory?
>
> What about if someone else deletes that parent tuple? Do you notify all
> backends whenever any tuple that could potentially be a foreign key
> target is deleted so they can check their caches and flush it if it's
> present?
>
> This is a *REALLY *HARD* problem unless you can narrow it to a specific
> case with clear and simple rules about what you cache, concurrency, how
> long something stays cached, etc. The cache you describe superficially
> seems like an easy and nice way to do things but in practice this sort
> of thing usually works out to be really, really, really complicated.
>
> "There are only two hard problems in Computer Science:
> cache invalidation and naming things."
>
> -- Phil Karlton
>
> To learn more about why it's so hard, see:
> http://en.wikipedia.org/wiki/Cache_invalidation
>
> --
> Craig Ringer
>
> POST Newspapers
> 276 Onslow Rd, Shenton Park
> Ph: 08 9381 3088 Fax: 08 9388 2258
> ABN: 50 008 917 717
> http://www.postnewspapers.com.au/
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
------------
pasman