[HACKERS] Re: How to reproduce serialization failure for a read only transaction.
Regarding this: >> So what does this have to do with relying on the results of read-only transactions that abort? Well, what if you had instead had R ROLLBACK instead of COMMIT -- maybe because you expected ROLLBACK and COMMIT to be equivalent for transactions that don't modify the database, or maybe because something else caused the transaction to abort? When W1 does its update, it will be checked for serialization failures, but aborted transactions are (intentionally) not included in those checks. W1 is therefore allowed to commit; the apparent serial order of execution is W1 followed by W2, and the results of the aborted transaction R aren't consistent with that. << So if I am reading the data and then commit, I should be always fine, correct? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569p5785757.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: How to reproduce serialization failure for a read only transaction.
This worked for me - thank you so much! The SELECT did fail. Also I cannot reproduce a scenario when "applications must not depend on results read during a transaction that later aborted;". In this example the SELECT itself has failed. Can you show an example where a SELECT completes, but the COMMIT blows up? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569p5785618.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: How to reproduce serialization failure for a read only transaction.
Hi Florian,
can you explain why do you state that "default isolation level is assumed to
be
serializable, of course", when you explicitly specify isolation level for
every session - why should he default matter at all?
When I am trying to reproduce the scenario which you have posted, I am
observing different results. Here is my full scenario:
Session 1. Setting up:
CREATE TABLE cars(
license_plate VARCHAR NOT NULL,
reserved_by VARCHAR NULL
);
INSERT INTO cars(license_plate)
VALUES ('SUPRUSR'),('MIDLYPH');
Session 2: W1
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE cars SET reserved_by = 'Julia'
WHERE license_plate = 'SUPRUSR'
AND reserved_by IS NULL;
SELECT * FROM Cars
WHERE license_plate IN('SUPRUSR','MIDLYPH');
Session 3: W2
BEGIN ISOLATION LEVEL SERIALIZABLE;
UPDATE cars SET reserved_by = 'Ryan'
WHERE license_plate = 'MIDLYPH'
AND reserved_by IS NULL;
COMMIT;
Session 4: R
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY;
SELECT * FROM Cars
WHERE license_plate IN('SUPRUSR','MIDLYPH');
Session 2: W1
COMMIT;
ERROR: could not serialize access due to read/write dependencies among
transactions
What am I doing wrong?
Thank you for your help!
--
View this message in context:
http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569p5785597.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] How to reproduce serialization failure for a read only transaction.
If two transactions both read and write, I can easily reproduce the following: "could not serialize access due to read/write dependencies among transactions". However, the 9.3 documentation says that "When relying on Serializable transactions to prevent anomalies, it is important that any data read from a permanent user table not be considered valid until the transaction which read it has successfully committed. This is true even for read-only transactions". I cannot have a read-only transaction fail because of serialization anomalies. Can someone show me a working example please? -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-reproduce-serialization-failure-for-a-read-only-transaction-tp5785569.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let us fix the documentation
Oops, did not notice that - thank you! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Let-us-fix-the-documentation-tp5782999p5783002.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Let us fix the documentation
The following url seems to be slightly incorrect: http://www.postgresql.org/docs/9.3/static/sql-prepare.html PREPARE usrrptplan (int) AS SELECT * FROM users u, logs l WHERE u.usrid=$1 AND u.usrid=l.usrid AND l.date = $2; EXECUTE usrrptplan(1, current_date); I guess the first line of the example should be: PREPARE usrrptplan (int, date) AS What do you think? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Let-us-fix-the-documentation-tp5782999.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should we improve documentation on isolation levels?
I concur - the documentation is not incorrect, it is just incomplete. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Should-we-improve-documentation-on-isolation-levels-tp5780629p5780636.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Should we improve documentation on isolation levels?
I am not sure if i am posting in the right place - correct me if I am wrong. The following is not precise: 13.2.1. Read Committed Isolation Level "Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit changes during execution of the first SELECT." I think it should be re-worded as follows "Also note that two successive SELECT commands can see different data, even though they are within a single transaction, if other transactions commit after the first SELECT starts, and before the second SELECT starts." The reason: there could be other DML running between these two SELECTs. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Should-we-improve-documentation-on-isolation-levels-tp5780629.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] why semicolon after begin is not allowed in postgresql?
Kevin, I do see your logic now, but this thing is a common mistake - it means that this seems counter-intuitive to some people. What would happen if we applied Occam's razor and just removed this rule? All existing code would continue to work as is, and we would have one less rule to memorize. That would make PostgreSql a slightly better product, right? -- View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905p5780216.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why is UPDATE with column-list syntax not implemented
Claudio, Unfortunately, this UPDATE...FROM approach does not detect ambiguities, unless we go for tricks. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5780215.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] why semicolon after begin is not allowed in postgresql?
I am reading the following in the documentation: "Tip: A common mistake is to write a semicolon immediately after BEGIN. This is incorrect and will result in a syntax error." So, "common mistake" means semicolons after BEGIN seem consistent to many people - it seems consistent to me as well. If PostgreSql allowed them, we would have one less rule to memorize, shorter documentation, less mistakes and so on. In other words, without this limitation PostgreSql would be slightly more useful, right? What am I missing? Why do we need this rule? How is it making PostgreSql better? -- View this message in context: http://postgresql.1045698.n5.nabble.com/why-semicolon-after-begin-is-not-allowed-in-postgresql-tp5779905.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why is UPDATE with column-list syntax not implemented
Thank you, Tom! -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5779899.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why is UPDATE with column-list syntax not implemented
Claudio, Can you elaborate how rules can help? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600p5779896.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Why is UPDATE with column-list syntax not implemented
9.3 documentation says: According to the standard, the column-list syntax should allow a list of columns to be assigned from a single row-valued expression, such as a sub-select: UPDATE accounts SET (contact_last_name, contact_first_name) = (SELECT last_name, first_name FROM salesmen WHERE salesmen.id = accounts.sales_id); This is not currently implemented — the source must be a list of independent expressions. Why is this not implemented? Is it considered inconvenient to use, or difficult to implement. or not important enough, or some other reason? -- View this message in context: http://postgresql.1045698.n5.nabble.com/Why-is-UPDATE-with-column-list-syntax-not-implemented-tp5779600.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
