Re: Isolated transactions?

Hi,

I have a number of large tables in a schema all of which are related by
foreign keys.

Now, what I would like to be able to do is run some long-running queries
(mostly selects, but also some updates and inserts into the tables), to
generate some reports which will eventually be rolled-back.

Now, while this works just fine, I would like to be able to avoid
holding other transactions back while this is happening.

Is there a way to tell postgresql that a transaction is eventually going
to be rolled back, so that changes there do not delay other transactions?

Basically, the changes made in the transaction used for generating the
reports will only ever be visible in that transaction, which will get
rolled back.

Sorry for the weird description, I hope you understand what I mean.

Thanks,
LL

Lexington Luthor <lexington(dot)luthor(at)gmail(dot)com> writes:

> Hi,
>
> I have a number of large tables in a schema all of which are related
> by foreign keys.
>
> Now, what I would like to be able to do is run some long-running
> queries (mostly selects, but also some updates and inserts into the
> tables), to generate some reports which will eventually be
> rolled-back.
>
> Now, while this works just fine, I would like to be able to avoid
> holding other transactions back while this is happening.

In general, you won't block other transactions, unless you do explicit
locking with SELECT FOR UPDATE or LOCK TABLE. The rows you change
will simply never be seen by other transactions and will be removed by
the next VACUUM after your transaction rolls back.

-Doug

On Tue, Oct 04, 2005 at 04:46:04PM -0400, Douglas McNaught wrote:
> Lexington Luthor <lexington(dot)luthor(at)gmail(dot)com> writes:
> > I have a number of large tables in a schema all of which are related
> > by foreign keys.
> >
> > Now, what I would like to be able to do is run some long-running
> > queries (mostly selects, but also some updates and inserts into the
> > tables), to generate some reports which will eventually be
> > rolled-back.
> >
> > Now, while this works just fine, I would like to be able to avoid
> > holding other transactions back while this is happening.
>
> In general, you won't block other transactions, unless you do explicit
> locking with SELECT FOR UPDATE or LOCK TABLE. The rows you change
> will simply never be seen by other transactions and will be removed by
> the next VACUUM after your transaction rolls back.

Updates will block other update attempts on the same rows. Also,
inserts or updates on records with foreign keys acquire locks on
the referred-to keys that can block other operations that you might
not expect. For example:

CREATE TABLE foo (
id integer PRIMARY KEY
);

CREATE TABLE bar (
id integer PRIMARY KEY,
fooid integer NOT NULL REFERENCES foo
);

INSERT INTO foo (id) VALUES (1);

If Transaction A does this:

BEGIN;
INSERT INTO bar (id, fooid) VALUES (1, 1);

and then Transaction B does this:

BEGIN;
INSERT INTO bar (id, fooid) VALUES (2, 1);

then Transaction B will block even though there's no apparent
conflict. This is a gotcha that can cause deadlock errors (8.1
will acquire a weaker lock, which should reduce the likelihood of
deadlock).

--
Michael Fuhr