Prev: Japanies translation breaks solaris build
Next: JSON manipulation functions
From: Florian Pflug on 14 May 2010 10:20
On May 14, 2010, at 15:54 , Kevin Grittner wrote:
> Florian Pflug <fgp(a)phlo.org> wrote:
>> On May 14, 2010, at 12:56 , Kevin Grittner wrote:
>> unless your patch completely removes support for snapshot
>> isolation (what is current called SERIALIZABLE)
>
> Both SERIALIZABLE and REPEATABLE READ currently map to snapshot
> isolation. We're leaving REPEATABLE READ alone.

Ah, yeah, that makes a lot of sense. I kinda had forgotten about REPEATABLE READ...

>> my proposal still eliminates the situation that user-level
>> constraints are correct in READ COMMITTED and (true) SERIALIZABLE
>> isolation but not in snapshot isolation.
>
> Agreed. If someone wants to enforce user-level constraints using
> SSI, they will somehow need to ensure that less strict isolation
> levels are never used to modify data. Your approach lifts that
> burden.
>
> By the way, if you can make this behave in a similar way to Oracle,
> especially if the syntax is compatible, I'm sure it will help
> promote PostgreSQL adoption. At PostgreSQL Conference U.S. East
> 2010, I talked briefly with a couple guys from an Oracle shop who
> were looking at converting to PostgreSQL, and were very concerned
> about not having what you describe. The techniques required to
> ensure integrity in PostgreSQL were not, to put it mildly, appealing
> to them. I suspect that they would be satisfied with *either* SSI
> or the change you describe.

My proposal would make SELECT ... FOR UPDATE behave like Oracle does with regard to serialization conflicts. SELECT ... FOR SHARE doesn't seem to exist on Oracle at all - at least I couldn't find a reference to it in the docs.

The syntax isn't 100% compatible because Oracle seems to expect a list of columns after the FOR UPDATE clause, while postgres expects a list of tables.

I must admit that I wasn't able to find an explicit reference to Oracle's behavior in their docs, so I had to resort to experiments. They do have examples showing how to do FK-like constraints with triggers, and those don't contain any warning whatsoever about problems in SERIALIZABLE mode, though. But still, if there is word on this from Oracle somewhere, I'd love to hear about it.

best regards,
Florian Pflug


--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: "Kevin Grittner" on 14 May 2010 10:32
Florian Pflug <fgp(a)phlo.org> wrote:

> I must admit that I wasn't able to find an explicit reference to
> Oracle's behavior in their docs, so I had to resort to
> experiments. They do have examples showing how to do FK-like
> constraints with triggers, and those don't contain any warning
> whatsoever about problems in SERIALIZABLE mode, though. But
> still, if there is word on this from Oracle somewhere, I'd love to
> hear about it.

I suspect that in trying to emulate Oracle on this, you may run into
an issue which posed challenges for the SSI implementation which
didn't come up in the Cahill prototype implementations: Oracle, and
all other MVCC databases I've read about outside of PostgreSQL, use
an "update in place with a rollback log" technique. Access to any
version of a given row or index entry goes through a single
location, with possible backtracking through the log after that,
which simplifies management of certain concurrency issues. Do they
perhaps use an in-RAM lock table, pointing to the "base" location of
the row for these SELECT FOR UPDATE locks? (Just guessing; I've
never used Oracle, myself.)

-Kevin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

 | 
Pages: 1
Prev: Japanies translation breaks solaris build
Next: JSON manipulation functions