Re: Win2K Questions

Scott,

You answered the question yourself. The operative keyword her is
*before* the transaction started.
You store the global count before the transaction. While in a
transaction, you save the number of inserted and deleted records. When
*all* parallel transactions are commited, you update the global count
with the total of of updated and deleted records. If a connection start
a new transaction before the other transactions have been
commited you take the global count plus the adjustment from the previous
transaction.

JLL

"scott.marlowe" wrote:
>
> 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(at)candle(dot)pha(dot)pa(dot)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(at)postgresql(dot)org so that your
> > message can get through to the mailing list cleanly
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org