Per-Table Transaction Isolation Level?
От | Florian G. Pflug |
---|---|
Тема | Per-Table Transaction Isolation Level? |
Дата | |
Msg-id | 41903AB8.7080104@phlo.org обсуждение исходный текст |
Ответы |
Re: Per-Table Transaction Isolation Level?
Re: Per-Table Transaction Isolation Level? Re: Per-Table Transaction Isolation Level? |
Список | 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
В списке pgsql-general по дате отправления: