Re: CTID issues and a soc student in need of help

From: Tzahi Fadida <tzahi(dot)ml(at)gmail(dot)com>
To: Martijn van Oosterhout <kleptog(at)svana(dot)org>
Cc: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: CTID issues and a soc student in need of help
Date: 2006-06-01 15:17:44
Message-ID: 1149175064.4871.18.camel@llord
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

I am using CTID for the concept of a tuple set.
For example, the set of t1 from relation1, t1 from relation2, t10 from
relation3 will be represented in my function as a list
of (TableID:CTID) pairs.
For example {(1:1),(2:1),(3:10))
I then save these in bytea arrays in a tuplestore.
This is essential for the full disjunction algorithm because
you do not want to recompute joins for every such set using the actual
attribute.

>From the documentation i see that
Dirty Read, Nonrepeatable Read and Phantom Read
are all unacceptable.
Moreover, when i said long time i meant full disjunctions
can run for hours on an extremely difficult input
(not that most people will want to do that, but for
the general case) so it is not
realistic to lock the tables for that period.

So i have 2 follow up questions:
1) If i execute the function in a serializable isolation level
and the function is read only to the tables, is it possible
for the function to fail or other updating transactions to
either fail or wait for hours/starvation?

2) Inside the function i open and reopen cursors and portals to
relations, how can i set once, for all those opening within
the function, to have a "serializable" isolation level.
I suspect that because of command boundaries, just running
SET TRANSACTION SERIALIZABLE using SPI at the start should be enough.

On Thu, 2006-06-01 at 15:30 +0200, Martijn van Oosterhout wrote:
>
> The CTID is the location on disk of the tuple, so no, it doesn't change
> while you are running.
>
> However, if you're running in isolation mode "read committed", then
> someone else could update the tuple while you're looking at it. In this
> case the tuple will appear to vanish and the updated version will
> appear elsewhere with a new CTID. Whether this is a problem or not
> depends on what you're using it for.
>
> Hope this helps,

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2006-06-01 15:28:09 Re: CTID issues and a soc student in need of help
Previous Message Zdenek Kotala 2006-06-01 14:56:10 Re: Allow commenting of variables in postgresql.conf to -