A thought on Index Organized Tables
in [PgSql]
|
Prev: [COMMITTERS] pgsql: Oops, don't forget to rewind the directory before scanning it to
Next: Time travel on the buildfarm
From: Heikki Linnakangas on 24 Feb 2010 03:40 Simon Riggs wrote: > I would add that both Heikki and Greg Stark have argued at length that > the visibility map cannot be relied upon in production systems. It cannot be relied on *in its current form*. There's a hole in crash recovery where it can be left in an inconsistent state. That obviously needs to be fixed before it is relied on for index-only-scans or similar purposes, but it's not an insurmountable problem. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Simon Riggs on 24 Feb 2010 03:46 On Wed, 2010-02-24 at 10:40 +0200, Heikki Linnakangas wrote: > Simon Riggs wrote: > > I would add that both Heikki and Greg Stark have argued at length that > > the visibility map cannot be relied upon in production systems. > > It cannot be relied on *in its current form*. There's a hole in crash > recovery where it can be left in an inconsistent state. That obviously > needs to be fixed before it is relied on for index-only-scans or similar > purposes, but it's not an insurmountable problem. I was referring to earlier discussions around the use of that information for use in partitioning. At that time it was argued the technique would be fragile and unusable in production systems, even assuming the information was accurate. Regrettably, I agree: even a light write workload is sufficient to render the technique useless and designing systems that relied upon that would be risky. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Gokulakannan Somasundaram on 24 Feb 2010 03:53 > > > The fragility there is not an issue in a mostly read-only application, > but it definitely would be a concern in other cases. > While we accept that visibility map is good for read only application, why can't we make it optional? Atleast if there is a way for a person to drop the visibility map for a table(if it gets created by default), the application need not incur the overhead for those tables, when it knows it is update intensive / with batch jobs. Again not to deviate from my initial question, can we make a decision regarding unstable/mutable functions / broken data types ? Thanks, Gokul.
From: Simon Riggs on 24 Feb 2010 04:24 On Wed, 2010-02-24 at 14:23 +0530, Gokulakannan Somasundaram wrote: > can we make a decision regarding unstable/mutable functions / broken > data types ? You need to take about 5 steps back. Diving straight into a particular technical detail is not the right approach. Nobody will confirm a decision on anything without first understanding the whole question and how it relates to something they care about. -- Simon Riggs www.2ndQuadrant.com -- Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
From: Karl Schnaitter on 24 Feb 2010 04:24
On Wed, Feb 24, 2010 at 12:53 AM, Gokulakannan Somasundaram < gokul007(a)gmail.com> wrote: > > Again not to deviate from my initial question, can we make a decision > regarding unstable/mutable functions / broken data types ? > > I second this question. A year or two ago, Gokul and I both proposed a feature that put visibility metadata into the index tuples and supported index-only scans, and the idea was dismissed because a user might choose incorrect ordering operators. I tried to ask for a clear explanation of the issue, but never got it. Incorrect operators and mutable functions will surely lead to incorrect query results, but that is already a possibility with any index. It's also possible that a heap tuple is deleted, but the deletion is not recorded in the index because the tuple wasn't found. This is okay because (1) the heap tuple will remain where it is until vacuuming, and (2) during vacuuming, the visibility metadata in the index should be ignored when determining whether an index tuple points to a dead heap tuple. This ensures that all references to a heap tuple are removed before wiping it out. The bottom line is that the visibility metadata is a good thing if you know when to trust it. It's fine to trust it when evaluating a SELECT. But not during a more dangerous operation like VACUUM. Karl |