Re: Win2K Questions

but how do you handle the case where two people have two different
connections, and one starts a serializable transaction and adds n rows to
the table.  For that transaction, there are x+n rows in the table, while
for the transaction started before his, there are only x rows.  which is
the "right" answer?

On Fri, 8 Nov 2002, Jean-Luc Lachance wrote:

> Here is a suggestion.
>
> When a count(*) is computed (for all records) store that value and
> unvalidate it if there is a later insert or delete on the table. Next
> improvement would be to maintain a count per active transaction.
>
> Bruce Momjian wrote:
> >
> > Charles H. Woloszynski wrote:
> > >
> > >
> > > Richard Huxton wrote:
> > >
> > > >Depends on usage patterns and how you build your application. There are a
> > > >couple of oddities with workarounds: count() and max() aren't very optimised
> > > >for example.
> > > >
> > > You can 'fix' the max() SNAFU with a new query of the form
> > > "select field from tbl limit 1 order by field  desc" (not precise
> > > syntax, but the idea is correct)
> > >
> > > I call it a SNAFU since it I hate to have to change queries from
> > > something obvious to a more obscure format just to work around
> > > an optimizer issue.
> > >
> > > Not sure if there is an equivalent query to make count() work
> > > faster
> >
> > The problem with optimizing COUNT() is that different backends have
> > different tuple views, meaning the count from one backend could be
> > different than from another backend.  I can't see how to optimize that.
> > Does oracle do it?  Maybe by looking their redo segements.  We don't
> > have those because redo is stored in the main table.
> >
> > --
> >   Bruce Momjian                        |  http://candle.pha.pa.us
> >   pgman@candle.pha.pa.us               |  (610) 359-1001
> >   +  If your life is a hard drive,     |  13 Roberts Road
> >   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>