Skip site navigation (1) Skip section navigation (2)

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 (view raw, whole thread or download thread mbox)
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

>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


pgsql-hackers by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group