От: Tom Lane
Тема: Re: Suspending SELECTs
Дата: ,
Msg-id: 21016.1137529171@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: Suspending SELECTs  (Alessandro Baretta)
Список: pgsql-performance

Скрыть дерево обсуждения

Suspending SELECTs  (Alessandro Baretta, )
 Re: Suspending SELECTs  (Tom Lane, )
  Re: Suspending SELECTs  (Alvaro Herrera <-ip.org>, )
   Re: Suspending SELECTs  (Tom Lane, )
  Re: Suspending SELECTs  ("Craig A. James", )
   Re: Suspending SELECTs  (Alessandro Baretta, )
    Re: Suspending SELECTs  ("Jim C. Nasby", )
    Re: Suspending SELECTs  (Mark Lewis, )
    Re: Suspending SELECTs  ("Craig A. James", )
  Re: Suspending SELECTs  (Alessandro Baretta, )
   Re: Suspending SELECTs  (Michael Stone, )
   Re: Suspending SELECTs  (Tom Lane, )
   Re: Suspending SELECTs  (, )
    Re: Suspending SELECTs  (Frank Wiles, )
    Re: Suspending SELECTs  (Mark Kirkwood, )
     Re: Suspending SELECTs  (Tom Lane, )
      Re: Suspending SELECTs  (Mark Kirkwood, )
    Re: Suspending SELECTs  (Alessandro Baretta, )
     Re: Suspending SELECTs  (Tino Wildenhain, )
     Re: Suspending SELECTs  (, )
      Re: Suspending SELECTs  (Alessandro Baretta, )
       Re: Suspending SELECTs  (Harry Jackson, )
        Re: Suspending SELECTs  (, )
   Re: Suspending SELECTs  (Josh Berkus, )
    Re: Suspending SELECTs  (Josh Berkus, )
     Re: Suspending SELECTs  (Alessandro Baretta, )
      Re: Suspending SELECTs  (August Zajonc, )
       Re: Suspending SELECTs  (Alessandro Baretta, )
 Re: Suspending SELECTs  (Mark Lewis, )

Alessandro Baretta <> writes:
> * When the cursor state is pushed back to the backend, no new
> transaction is instantiated, but the XID of the original transaction
> is reused. In the MVCC system, this allows us to achieve a perfectly
> consistent view of the database at the instant the original
> transaction started, unless a VACUUM command has been executed in the
> meantime, in which case I would lose track of tuples which would have
> been live in the context of the original transaction, but have been
> updated or deleted and later vacuumed; however, this does not bother
> me at all.

> Is this not a viable solution?

No.  I'm not interested in "solutions" that can be translated as "you
may or may not get the right answer, and there's no way even to know
whether you did or not".  That might be acceptable for your particular
application but you certainly can't argue that it's of general
usefulness.

Also, I can't accept the concept of pushing the entire execution engine
state out to the client and then back again; that state is large enough
that doing so for every few dozen rows would yield incredibly bad
performance.  (In most scenarios I think it'd be just as efficient for
the client to pull the whole cursor output at the start and page through
it for itself.)  Worse yet: this would represent a security hole large
enough to wheel West Virginia through.  We'd have no reasonable way to
validate the data the client sends back.

Lastly, you underestimate the problems associated with not holding the
locks the cursor is using.  As an example, it's likely that a btree
indexscan wouldn't successfully restart at all, because it couldn't find
where it had been if the index page had been split or deleted meanwhile.
So not running VACUUM is not enough to guarantee the query will still
work.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: Mark Kirkwood
Дата:
Сообщение: Re: Suspending SELECTs
От: Hari Warrier
Дата:
Сообщение: Getting pg to use index on an inherited table (8.1.1)