Re: [HACKERS] Comments from a Firebird user via Borland Newsgroups.
Tom Lane wrote: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-transaction.html http://candle.pha.pa.us/main/writings/pgsql/sgml/transaction-iso.html It's a bit amusing that this person is dissing us for not having REPEATABLE READ, when what he actually seems to want is SERIALIZABLE (which we've had since 1999). Certainly REPEATABLE READ does *not* guarantee a stable view of data during one transaction --- see the discussion of phantom reads in the second link given above. regards, tom lane Tom, This is what the firebird guy said: Serializable is stricter and somehwat unusable in a multi-user, loaded database, because only one transaction can run at any time. Let's say you would have one long running serializable transaction encapsulating a reporting query, this will cause other transactions to wait. There is a pretty good paper on discussing why it was a somewhat bad idea to describe transaction isolation levels in terms of phenomena in the SQL standard. This paper also describes transaction isolation levels for MVCC databases. The paper is from 1995. http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf SNAPSHOT in Firebird isn't a SQL standard compliant REPEATBLE READ either. SNAPSHOT in Firebird is between REPEATABLE READ and SERIALIZABLE, but without blocking other transactions. Is this true? will SERIALIZABLE block all transactions on the whole server, or just on that one connection? Thanks, Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Comments from a Firebird user via Borland Newsgroups.
On Thu, 10 Nov 2005, Tony Caduto wrote: Serializable is stricter and somehwat unusable in a multi-user, loaded database, because only one transaction can run at any time. Let's say you would have one long running serializable transaction encapsulating a reporting query, this will cause other transactions to wait. There is a pretty good paper on discussing why it was a somewhat bad idea to describe transaction isolation levels in terms of phenomena in the SQL standard. This paper also describes transaction isolation levels for MVCC databases. The paper is from 1995. http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf SNAPSHOT in Firebird isn't a SQL standard compliant REPEATBLE READ either. SNAPSHOT in Firebird is between REPEATABLE READ and SERIALIZABLE, but without blocking other transactions. Is this true? will SERIALIZABLE block all transactions on the whole server, or just on that one connection? I don't believe so ... my understanding was that MVCC took care of any blocking issues, since we are looking at a 'snapshot' or 'layer' of data, based on the time you started the transaction ... other transactions can still work on data while the SERIALIZABLE transaction is going on ... The way I've thought about it is akin to going to a cash register to pay for groceries ... you don't want prices to change part way through the cashier ringing up your bill, but you also don't want to have the office shut everyone off while they update the price list ... so the cash register would be running the 'bill tally' in a SERIALIZABLE transaction, so that the prices are based on when (s)he started to ring things up ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Comments from a Firebird user via Borland Newsgroups.
Tony Caduto [EMAIL PROTECTED] writes: Tom, This is what the firebird guy said: Serializable is stricter and somehwat unusable in a multi-user, loaded database, because only one transaction can run at any time. He's already demonstrated that he has no clue what he's talking about, so I think you can discount the rest ;-) Serializability means that the database has to *give the illusion* of one-at-a-time execution, not that it must actually do things that way. Certainly we don't do things that way. See the extensive discussion in the MVCC chapter of our docs. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Comments from a Firebird user via Borland
Hi Tony, As the referenced documentation states, the PostgreSQL SERIALIZABLE transaction isolation level complies with the ANSI/ISO requirements, but not with a mathematically pure interpretation of the term. (The only quibble I have with that documentation is that you have to be averting your eyes to not find several commercial products which do enforce the stricter interpretation.) As far as I can see, the difference is only significant if you need to have two concurrent transactions where one transaction is selecting from a set of data A to modify something within a set of data B at the same time that another transaction is selecting from B to modify something within A -- without any overlap between the rows updated by the transactions. In practice, this seems unlikely to be meaningful outside of some theoretical science; you don't normally want recursive redundancies in your database. So to address the original concern -- PostgreSQL absolutely gives you a stable view of the data during a SERIALIZABLE transaction. The only thing it doesn't give you is a guarantee that some other transaction hasn't made modifications which would change what the same SELECTs would show if you were to start a NEW transaction. -Kevin Tony Caduto [EMAIL PROTECTED] Tom Lane wrote: http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-transaction.html http://candle.pha.pa.us/main/writings/pgsql/sgml/transaction-iso.html It's a bit amusing that this person is dissing us for not having REPEATABLE READ, when what he actually seems to want is SERIALIZABLE (which we've had since 1999). Certainly REPEATABLE READ does *not* guarantee a stable view of data during one transaction --- see the discussion of phantom reads in the second link given above. regards, tom lane Tom, This is what the firebird guy said: Serializable is stricter and somehwat unusable in a multi-user, loaded database, because only one transaction can run at any time. Let's say you would have one long running serializable transaction encapsulating a reporting query, this will cause other transactions to wait. There is a pretty good paper on discussing why it was a somewhat bad idea to describe transaction isolation levels in terms of phenomena in the SQL standard. This paper also describes transaction isolation levels for MVCC databases. The paper is from 1995. http://www.cs.duke.edu/~junyang/courses/cps216-2003-spring/papers/berenson-etal-1995.pdf SNAPSHOT in Firebird isn't a SQL standard compliant REPEATBLE READ either. SNAPSHOT in Firebird is between REPEATABLE READ and SERIALIZABLE, but without blocking other transactions. Is this true? will SERIALIZABLE block all transactions on the whole server, or just on that one connection? Thanks, Tony ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Comments from a Firebird user via Borland
On Thu, Nov 10, 2005 at 12:00:12 -0600, Kevin Grittner [EMAIL PROTECTED] wrote: Hi Tony, As the referenced documentation states, the PostgreSQL SERIALIZABLE transaction isolation level complies with the ANSI/ISO requirements, but not with a mathematically pure interpretation of the term. (The only quibble I have with that documentation is that you have to be averting your eyes to not find several commercial products which do enforce the stricter interpretation.) For cases where you really need predicate locking, you can use full table locks. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Comments from a Firebird user via Borland Newsgroups.
We found PostgreSQL a mature product, but in two things Firebird was simply better than PostgreSQL: Two-Phase commit (ok, that is gone with PG 8.1), but the second is a SNAPSHOT / REPEATABLE READ transaction isolation. I can't live without that when it comes having a stable view of data during one transaction, or did that change with 8.1? Is there now a SNAPHOST / REPEATBLE READ transaction isolation level available as well? Just wondering what the PG take on this snapshot repeatable read stuff is. Tony ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Comments from a Firebird user via Borland Newsgroups.
On Wed, 2005-11-09 at 19:35 -0600, Tony Caduto wrote: We found PostgreSQL a mature product, but in two things Firebird was simply better than PostgreSQL: Two-Phase commit (ok, that is gone with PG 8.1), but the second is a SNAPSHOT / REPEATABLE READ transaction isolation. I can't live without that when it comes having a stable view of data during one transaction, or did that change with 8.1? Is there now a SNAPHOST / REPEATBLE READ transaction isolation level available as well? Just wondering what the PG take on this snapshot repeatable read stuff is. It has kinda been there for years and is what PostgreSQL uses to achieve a consistent snapshot with pg_dump. Of course, per spec the DB is allowed to upgrade the isolation level to SERIALIZABLE from what you specify you require as a minimum (REPEATABLE READ in this case). session1: begin isolation level repeatable read; session2: insert into junk values (1); session1: rbt=# select * from junk; col - 1 (1 row) session2: insert into junk values (2); session1: rbt=# select * from junk; col - 1 (1 row) -- ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Comments from a Firebird user via Borland Newsgroups.
On Wed, Nov 09, 2005 at 19:35:30 -0600, Tony Caduto [EMAIL PROTECTED] wrote: We found PostgreSQL a mature product, but in two things Firebird was simply better than PostgreSQL: Two-Phase commit (ok, that is gone with PG 8.1), but the second is a SNAPSHOT / REPEATABLE READ transaction isolation. I can't live without that when it comes having a stable view of data during one transaction, or did that change with 8.1? Is there now a SNAPHOST / REPEATBLE READ transaction isolation level available as well? Just wondering what the PG take on this snapshot repeatable read stuff is. http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-transaction.html http://candle.pha.pa.us/main/writings/pgsql/sgml/transaction-iso.html ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Comments from a Firebird user via Borland Newsgroups.
Bruno Wolff III [EMAIL PROTECTED] writes: On Wed, Nov 09, 2005 at 19:35:30 -0600, Tony Caduto [EMAIL PROTECTED] wrote: We found PostgreSQL a mature product, but in two things Firebird was simply better than PostgreSQL: Two-Phase commit (ok, that is gone with PG 8.1), but the second is a SNAPSHOT / REPEATABLE READ transaction isolation. I can't live without that when it comes having a stable view of data during one transaction, or did that change with 8.1? Is there now a SNAPHOST / REPEATBLE READ transaction isolation level available as well? Just wondering what the PG take on this snapshot repeatable read stuff is. http://candle.pha.pa.us/main/writings/pgsql/sgml/sql-set-transaction.html http://candle.pha.pa.us/main/writings/pgsql/sgml/transaction-iso.html It's a bit amusing that this person is dissing us for not having REPEATABLE READ, when what he actually seems to want is SERIALIZABLE (which we've had since 1999). Certainly REPEATABLE READ does *not* guarantee a stable view of data during one transaction --- see the discussion of phantom reads in the second link given above. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
