Re: [COMMITTERS] pgsql-server: Rearrange pg_subtrans handling

Christopher Kings-Lynne <chriskl(at)familyhealth(dot)com(dot)au> writes:

> > OK, TODO updated:
> > * Implement dirty reads or shared row locks and use them in RI triggers
>
> Can someone explain to me what a dirty read is and how it relates to RI
> triggers?

A dirty read is a read that includes data that hasn't been committed yet. Or
as the SQL 92 standard puts it:

1) P1 ("Dirty read"): SQL-transaction T1 modifies a row. SQL-
transaction T2 then reads that row before T1 performs a COMMIT.
If T1 then performs a ROLLBACK, T2 will have read a row that was
never committed and that may thus be considered to have never
existed.

It's only allowed when the transaction is in READ UNCOMMITTED isolation level.
Something Postgres doesn't currently support. In fact I'm not aware of any SQL
database that supports it, though I'm sure there's one somewhere.

You wouldn't normally want to use such a thing, but it could be useful for,
for example, seeing what progress a transaction has made for a UI progress
meter.

It could also be useful for referential integrity checks since, for example,
it would let you see if someone has deleted the referenced record but not
committed the delete yet.

But that alone wouldn't let you avoid locking the record, TODO items are
mostly just pointers to old threads on the mailing lists. They don't contain
the complete story. You could maybe find more information searching the
pgsql-hackers archive on the web site.

--
greg