Per-Table Transaction Isolation Level?

From: "Florian G(dot) Pflug" <fgp(at)phlo(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Per-Table Transaction Isolation Level?
Date: 2004-11-09 03:34:16
Message-ID: 41903AB8.7080104@phlo.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hi

I'd like to know if there is a way to specify different transaction
isolation levels for different tables in the db. The reason i'm asking
this (rather bizarre sounding, i know ;-) ) question is the following:

I'm importing about 2 million records into my application each day (the
data is more or less fully replaced each day). My importer updates only
a few tables (about 5 - 10), but reads a lot of other tables (10 or so)
while importing. Those (read-only, meta-information) tables contains
information on how to
import the data, and what reports to calculate from the imported data.

My import sometimes crashed, becausse the meta-information tables are
changed while importing (e.h, I pass a id to a function, the function
does some calculations, than tries to select the row with the given id,
but fails, because the row was deleted in the meantime). I understand
that the standard approach to this problem is to set the transaction
isolation level to "serializeable", thus avoiding non-repeatable reads.

But since the import is a lenghty operation (a few hours), I don't want
to import in a searializeable transaction, since it would force me to
import "in a loop" until no serialization error occurs while importing.

But since it's only the meta-information tables for which I want to
avoid non-repeatable reads, and since those are read-only anyway (for my
importer), I wouldn't have to fear getting "serialization errors" when I
access only those tables in serializeable mode (since read-only
transaction never trigger serialization errors).

I know I could simulate something like that using dblink, but if
possible I'd prefer a simpler approach (Using dblink would meand that I
need to rewrite large parts of import, since it's mostly stored procedures).

greetings, Florian Pflug

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-11-09 03:41:55 Re: server auto-restarts and ipcs
Previous Message Ed L. 2004-11-09 02:28:57 Re: server auto-restarts and ipcs