Re: Read only transactions - Commit or Rollback

Hi, Tom,

Tom Lane wrote:

>>Some time ago, I had some tests with large bulk insertions, and it
>>turned out that SERIALIZABLE seemed to be 30% faster, which surprised us.
>
> That surprises me too --- can you provide details on the test case so
> other people can reproduce it? AFAIR the only performance difference
> between SERIALIZABLE and READ COMMITTED is the frequency with which
> transaction status snapshots are taken; your report suggests you were
> spending 30% of the time in GetSnapshotData, which is a lot higher than
> I've ever seen in a profile.

It was in my previous Job two years ago, so I don't have access to the
exact code, and my memory is foggy. It was PostGIS 0.8 and PostgreSQL 7.4.

AFAIR, it was inserting into a table with about 6 columns and some
indices, some columns having database-provided values (now() and a
SERIAL column), where the other columns (a PostGIS Point, a long, a
foreign key into another table) were set via the aplication. We tried
different insertion methods (INSERT, prepared statements, a pgjdbc patch
to allow COPY support), different bunch sizes and different number of
parallel connections to get the highest overall insert speed. However,
the project never went productive the way it was designed initially.

As you write about transaction snapshots: It may be that the PostgreSQL
config was not optimized well enough, and the hard disk was rather slow.

> As to the original question, a transaction that hasn't modified the
> database does not bother to write either a commit or abort record to
> pg_xlog. I think you'd be very hard pressed to measure any speed
> difference between saying COMMIT and saying ROLLBACK after a read-only
> transaction. It'd be worth your while to let transactions run longer
> to minimize their startup/shutdown overhead, but there's a point of
> diminishing returns --- you don't want client code leaving transactions
> open for hours, because of the negative side-effects of holding locks
> that long (eg, VACUUM can't reclaim dead rows).

Okay, so I'll stick with my current behaviour (Autocommit off and
ROLLBACK after each bunch of work).

Thanks,
Markus

--
Markus Schaber | Logical Tracking&Tracing International AG
Dipl. Inf. | Software Development GIS

Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org