Re: 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 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.
--
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(at)momjian(dot)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(at)gmx(dot)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 executing such 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(at)gmx(dot)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 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.

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:

> 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?

--
Jesper

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(at)momjian(dot)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

Jesper Krogh wrote:
> 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.

[ Moved to the www list.]

Good idea. I can't seem to exit that page. Who can? It is at the
bottom of the main wiki page.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

On Thu, Mar 10, 2011 at 19:45, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> Jesper Krogh wrote:
>> 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.
>
> [ Moved to the www list.]
>
> Good idea.  I can't seem to exit that page.  Who can?  It is at the

I assume you mean edit, not exit?
In that case, I have no idea if it even *can* be edited, if you're
talking about the login page.

> bottom of the main wiki page.

You mean the main page already has the information? Yes, but I thought
this was about getting it on the actual login page?

--
 Magnus Hagander
 Me: http://www.hagander.net/
 Work: http://www.redpill-linpro.com/

Magnus Hagander wrote:
> On Thu, Mar 10, 2011 at 19:45, Bruce Momjian <bruce(at)momjian(dot)us> wrote:
> > Jesper Krogh wrote:
> >> 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.
> >
> > [ Moved to the www list.]
> >
> > Good idea. ?I can't seem to exit that page. ?Who can? ?It is at the
>
> I assume you mean edit, not exit?
> In that case, I have no idea if it even *can* be edited, if you're
> talking about the login page.

Right.

> > bottom of the main wiki page.
>
> You mean the main page already has the information? Yes, but I thought
> this was about getting it on the actual login page?

Yes, the actual login page.

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +

2011/3/10 Jesper Krogh <jesper(at)krogh(dot)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