Thread: Read uncommitted ever possible?
Hi, if you want to do dirty counts or sums or any aggreate stuff, you will always have to visit the table. For many applicationsnobody cares about 0,01% inaccuracy. If you could keep the data that has to be aggregated in the index you could approximate values really fast. But because "Read uncommitted" is not implemented you will always have to visit the table. This is one reason why peoplehave to still buy oracle. I don't know the postgres code, but I don't thing it is a big deal, not to care about consistancy. The code for executingsuch a query should be quite basic, because no MVCC-Stuff has to be done. Will this feature come any time soon? Even if "Read uncommitted" is a "could read all sorts of old and dirty stuff" it isstill better than nothing. -- NEU: FreePhone - kostenlos mobil telefonieren und surfen! Jetzt informieren: http://www.gmx.net/de/go/freephone
hans wulf wrote: > Hi, > > if you want to do dirty counts or sums or any aggreate stuff, you will > always have to visit the table. For many applications nobody cares > about 0,01% inaccuracy. > > If you could keep the data that has to be aggregated in the index you > could approximate values really fast. > > But because "Read uncommitted" is not implemented you will always have > to visit the table. This is one reason why people have to still buy > oracle. > > I don't know the postgres code, but I don't thing it is a big deal, > not to care about consistancy. The code for executing such a query > should be quite basic, because no MVCC-Stuff has to be done. > > Will this feature come any time soon? Even if "Read uncommitted" is a > "could read all sorts of old and dirty stuff" it is still better than > nothing. Dirty reads are unlikely to be implemented. We do have a TODO item and wiki page about how to allow index scans without heap access: http://wiki.postgresql.org/wiki/Index-only_scans -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On Thu, Mar 10, 2011 at 12:31 PM, hans wulf <lotu1@gmx.net> wrote: > I don't know the postgres code, but I don't thing it is a big deal, not to care about consistancy. The code for executingsuch a query should be quite basic, because no MVCC-Stuff has to be done. I remember I used to think this would be simple -- just return all rows regardless of visibility. However I later learned things were more complex than that. You probably want to return the latest version of any row regardless of whether it's committed but *not* return two or more copies of the same row which would really make the results entirely meaningless. That alone would make it prohibitively hard to do. I think I remember issues with potentially running into old rows that don't even match the current definition of the table. That would potentially cause you to crash or output garbage. However offhand I don't see how that would be possible so perhaps I'm misremembering this issue. -- greg
On Thu, Mar 10, 2011 at 6:31 AM, hans wulf <lotu1@gmx.net> wrote: > Hi, > > if you want to do dirty counts or sums or any aggreate stuff, you will always have to visit the table. For many applicationsnobody cares about 0,01% inaccuracy. > > If you could keep the data that has to be aggregated in the index you could approximate values really fast. > > But because "Read uncommitted" is not implemented you will always have to visit the table. This is one reason why peoplehave to still buy oracle. > > I don't know the postgres code, but I don't thing it is a big deal, not to care about consistancy. The code for executingsuch a query should be quite basic, because no MVCC-Stuff has to be done. > > Will this feature come any time soon? Even if "Read uncommitted" is a "could read all sorts of old and dirty stuff" itis still better than nothing. Oracle has a different mvcc implementation than postgres. We keep a lot more records of questionable visibility around in the heap so in most real world cases your 0.01% could be 50% inaccuracy or worse. As Bruce noted the direction the postgres project has taken has been to limit the downsides of our mvcc implementation. A lot of the work in the 8.x cycle (HOT, visibility map, etc) has been laying the groundwork for the performance benefits you want without cheating...and covering index scans (such that they are possible) are on the radar. merlin
On 2011-03-10 18:00, Bruce Momjian wrote:<br /><br /><span style="white-space: pre;">> Dirty reads are unlikely to beimplemented. We do have a TODO item<br /> > and wiki page about how to allow index scans without heap access:<br />> <br /> > <a class="moz-txt-link-freetext" href="http://wiki.postgresql.org/wiki/Index-only_scans">http://wiki.postgresql.org/wiki/Index-only_scans</a></span><br /><br/> I think we (the company I work for) would help co-sponsor such<br /> a feature. Would it be ok to add a section onthe wiki with<br /> a list of potential sponsors that might in total be able to sponsor<br /> development of such a feature?Then perhaps a developer would<br /> drop by.<br /><br /> ... it would be easier if there was a feeling about howmuch actually<br /> is required.<br /><br /> ... can anyone create wiki accounts?<br /><br /> -- <br /> Jesper<br /><br/>
Jesper Krogh wrote: > On 2011-03-10 18:00, Bruce Momjian wrote: > > > Dirty reads are unlikely to be implemented. We do have a TODO item > > and wiki page about how to allow index scans without heap access: > > > > http://wiki.postgresql.org/wiki/Index-only_scans > > I think we (the company I work for) would help co-sponsor such > a feature. Would it be ok to add a section on the wiki with > a list of potential sponsors that might in total be able to sponsor > development of such a feature? Then perhaps a developer would > drop by. > > ... it would be easier if there was a feeling about how much actually > is required. > > ... can anyone create wiki accounts? Sure, anyone can add text to that wiki; you create a community account here: http://www.postgresql.org/community/signup -- Bruce Momjian <bruce@momjian.us> http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. +
On 2011-03-10 19:25, Bruce Momjian wrote: > > Sure, anyone can add text to that wiki; you create a community account > here: > > http://www.postgresql.org/community/signup Suggestion: Add this url to the login box on the wiki. -- Jesper
2011/3/10 Jesper Krogh <jesper@krogh.cc>: > On 2011-03-10 19:25, Bruce Momjian wrote: > >> Sure, anyone can add text to that wiki; you create a community account >> here: >> >> http://www.postgresql.org/community/signup > > Suggestion: Add this url to the login box on the wiki. +1, Adrian von Bidder had the same problem just two days ago. Nicolas