On 04/11/14 09:07, Craig Ringer wrote:
On 11/04/2014 07:31 AM, Álvaro Hernández Tortosa wrote:
Thank you for your comment, Tom. However I think this behavior, as
seen from a user perspective, it's not the expected one.
That may be the case, but I think it's the SQL-standard behaviour, so we
can't really mess with it.
The spec requires SET TRANSACTION ISOLATION, and you can't implement
that if you take a snapshot at BEGIN.
It's true that the standard mandates SET TRANSACTION rather than
setting the isolation level with the BEGIN statement, and in any case
you can raise/lower the isolation level with SET regardless of what the
session or the begin command said. However, is it really a problem
taking a snapshot at BEGIN time --only if the tx is started with BEGIN
... (REPEATABLE READ | SERIALIZABLE)? AFAIK, and I may be missing some
internal details here, the worst that can happen is that you took one
extra, unnecessary snapshot. I don't see that as a huge problem.
The standard (92) says that transaction is initiated when a
transaction-initiating SQL-statement is executed. To be fair, that
sounds to me more of a "SELECT" rather than a "BEGIN", but I may be wrong.
If it is still the intended behavior, I think it should be clearly
documented as such, and a recommendation similar to "issue a 'SELECT 1'
right after BEGIN to freeze the data before any own query" or similar
comment should be added. Again, as I said in my email, the documentation
clearly says that "only sees data committed before the transaction
began". And this is clearly not the real behavior.
It's more of a difference in when the transaction "begins".
Arguably, "BEGIN" says "I intend to begin a new transaction with the
next query" rather than "immediately begin executing a new transaction".
This concept could be clearer in the docs.
If this is really how it should behave, I'd +1000 to make it
clearer in the docs, and to explicitly suggest the user to perform a
query discarding the results early after BEGIN if the user wants the
state freezed if there may span time between BEGIN and the real queries
to be executed (like doing a SELECT 1).
Sure, there are, that was the link I pointed out, but I found no
explicit mention to the fact that I'm raising here.
I'm sure it's documented *somewhere*, in that I remember reading about
this detail in the docs, but I can't find _where_ in the docs.
It doesn't seem to be in:
http://www.postgresql.org/docs/current/static/transaction-iso.html
where I'd expect.
Yepp, there's no mention there.
In any case, we simply cannot take the snapshot at BEGIN time, because
it's permitted to:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
in a DB that has default serializable isolation or has a SET SESSION
CHARACTERISTICS isolation mode of serializable. Note that SET
TRANSACTION is SQL-standard.
As I said, AFAIK it shouldn't matter a lot to take the snapshot at
BEGIN. The worst that can happen is that you end up in read committed
and you need to take more snapshots, one per query.
AFAIK deferring the snapshot that's consistent with other RDBMSes that
use snapshots, too.
I tried Oracle and SQL Server. SQL Server seems to behave as
PostgreSQL, but just because it locks the table if accessed in a
serializable transaction, so it definitely waits until select to lock
it. However, Oracle behaved as I expected: data is frozen at BEGIN time.
I haven't tested others.
The docs of that command allude to, but doesn't explicitly state, the
behaviour you mention.
http://www.postgresql.org/docs/current/static/sql-set-transaction.html
Should we improve then the docs stating this more clearly? Any
objection to do this?
Regards,
Álvaro
--
Álvaro Hernández Tortosa
-----------
8Kdata
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
