Thread: SET TRANSACTION * proposal

Continuing the thread about checkpointing. While I was browsing through
SQL standard documents I noticed that there are clauses like:

SET TRANSACTION {READ UNCOMMITED|READ COMMITED|READ
REPEATABLE|SERIALIZABLE}

I was wondering... If we have non-overwriting feature of postgres, we
would accomplish no-lock reads for at least first two isolation levels.

Also, by adding 'checkpointed' flag to each record we would allow for:
SET TRANSACTION READ CHECKPOINTED
(it's out of SQL Standard, but I couldn't find the right command)

During CHECKPOINT we would mark all the current records with
'checkpointed' flag. Also checkpointing would do VACUUM, so it would be
guaranteed that each checkpointed record would be the first in it's
modification chain.

Then for READ CHECKPOINTED transaction mode we would accomplish no-lock
reading which is especially usefull when you have to do a very long
statistical query on your data being constantly updated. Also, it would
be guaranteed that checkpointed data are consistent.

Is this hard to accomplish?

Mike

--
WWW: http://www.lodz.pdi.net/~mimo  tel: Int. Acc. Code + 48 42 148340
add: Michal Mosiewicz  *  Bugaj 66 m.54 *  95-200 Pabianice  *  POLAND

Michal Mosiewicz wrote:
>
> Continuing the thread about checkpointing. While I was browsing through
> SQL standard documents I noticed that there are clauses like:
>
> SET TRANSACTION {READ UNCOMMITED|READ COMMITED|READ
> REPEATABLE|SERIALIZABLE}
>
> I was wondering... If we have non-overwriting feature of postgres, we
> would accomplish no-lock reads for at least first two isolation levels.
>
> Also, by adding 'checkpointed' flag to each record we would allow for:
> SET TRANSACTION READ CHECKPOINTED
> (it's out of SQL Standard, but I couldn't find the right command)
>
> During CHECKPOINT we would mark all the current records with
> 'checkpointed' flag. Also checkpointing would do VACUUM, so it would be
> guaranteed that each checkpointed record would be the first in it's
> modification chain.
>
> Then for READ CHECKPOINTED transaction mode we would accomplish no-lock
> reading which is especially usefull when you have to do a very long
> statistical query on your data being constantly updated. Also, it would
> be guaranteed that checkpointed data are consistent.

For that matter, if we could work this into the vacuum code, that
would be sweet.  We could update the statistics without locking out
writers.

Ocie

> SET TRANSACTION {READ UNCOMMITED|READ COMMITED|READ
> REPEATABLE|SERIALIZABLE}
<snip>
> Is this hard to accomplish?

This is one of Vadim's favorite projects. We diverted him for v6.3 to
implement subselects, but I think he plans to pick it up again soon (for
v6.4 or v6.5; he has other things on his list too :).

                      - Tom