Thread: PostgreSQL not ACID compliant?
Hello, I just read a rather disturbing post.... PostgreSQL does not support read uncommited and repeatable read isolation levels? If that is so... then PostgreSQL is NOT ACID compliant? What is the real deal on this? Sincerley, Joshua Drake -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com The most reliable support for the most reliable Open Source database.
On Fri, 19 Sep 2003, Joshua D. Drake wrote: > Hello, > > I just read a rather disturbing post.... > > PostgreSQL does not support read uncommited and repeatable read > isolation levels? If that is so... then PostgreSQL is NOT ACID compliant? > > What is the real deal on this? Postgresql supports Serializable transactions, which are 100% ACID compliant. I'm pretty sure read committed mode is also ACID compliant since anything that would cause a failure of ACID compliance would result in a rollback, but I'm not sure.
"Joshua D. Drake" <jd@commandprompt.com> writes:
> I just read a rather disturbing post....
> PostgreSQL does not support read uncommited and repeatable read
> isolation levels? If that is so... then PostgreSQL is NOT ACID compliant?
Why do you find that disturbing? Read uncommitted is the very
definition of "not ACID".
> What is the real deal on this?
We don't support read uncommitted because it's not ACID, and we don't
support repeatable read because it doesn't map to any useful behavior in
an MVCC model. It is legal to omit support for these isolation levels
per spec. (I think the spec actually wants implementations to silently
treat them as the next higher isolation level rather than complaining,
but that seems to me like it'd just add confusion.)
Anyone who tries to paint this as a big deal is just trolling.
regards, tom lane
Hello, Sorry guys I made the unbelievable mistake of talking on the #mysql channel today. Can you believe that they actually feel that the fact that you can insert data outside the boundaries of the data type (int for example) and mySQL won't throw an exception is still valid ACID compliance. There argument is that the application (user) should not have tried to insert bad data. I was completely blown away. The C in ACID explictly states that internal (data type boundaries anyone) AND user defined constraints can not be violated. I am just flabbergasted. I am going to come back to my safe PostgreSQL world. I need a hug. J Tom Lane wrote: >"Joshua D. Drake" <jd@commandprompt.com> writes: > > >> I just read a rather disturbing post.... >> >> > > > >> PostgreSQL does not support read uncommited and repeatable read >>isolation levels? If that is so... then PostgreSQL is NOT ACID compliant? >> >> > >Why do you find that disturbing? Read uncommitted is the very >definition of "not ACID". > > > >> What is the real deal on this? >> >> > >We don't support read uncommitted because it's not ACID, and we don't >support repeatable read because it doesn't map to any useful behavior in >an MVCC model. It is legal to omit support for these isolation levels >per spec. (I think the spec actually wants implementations to silently >treat them as the next higher isolation level rather than complaining, >but that seems to me like it'd just add confusion.) > >Anyone who tries to paint this as a big deal is just trolling. > > regards, tom lane > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com The most reliable support for the most reliable Open Source database.
On Fri, 19 Sep 2003, Joshua D. Drake wrote: > Hello, > > Sorry guys I made the unbelievable mistake of talking on the #mysql > channel today. Can you believe that they > actually feel that the fact that you can insert data outside the > boundaries of the data type (int for example) and > mySQL won't throw an exception is still valid ACID compliance. > > There argument is that the application (user) should not have tried to > insert bad data. I was completely blown > away. The C in ACID explictly states that internal (data type boundaries > anyone) AND user defined constraints > can not be violated. > > I am just flabbergasted. I am going to come back to my safe PostgreSQL > world. Boy, I'm flabbergasted that you actually believed them ... tsk tsk ...
Hello, It wasn't so much that I believed them, as that I spent an hour digging through PDF's written by guys who look like RMS trying to find the answer to a yes or no question. Are you aware that these guys don't believe in a yes or no question? There is always "conditions". I am feeling much better now. Heh, Joshua Drake Marc G. Fournier wrote: >On Fri, 19 Sep 2003, Joshua D. Drake wrote: > > > >>Hello, >> >> Sorry guys I made the unbelievable mistake of talking on the #mysql >>channel today. Can you believe that they >>actually feel that the fact that you can insert data outside the >>boundaries of the data type (int for example) and >>mySQL won't throw an exception is still valid ACID compliance. >> >> There argument is that the application (user) should not have tried to >>insert bad data. I was completely blown >>away. The C in ACID explictly states that internal (data type boundaries >>anyone) AND user defined constraints >>can not be violated. >> >> I am just flabbergasted. I am going to come back to my safe PostgreSQL >>world. >> >> > >Boy, I'm flabbergasted that you actually believed them ... tsk tsk ... > > >---------------------------(end of broadcast)--------------------------- >TIP 7: don't forget to increase your free space map settings > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com The most reliable support for the most reliable Open Source database.
It's funny timing - I had to prepare a comparison between PostgreSQL and MySQL recently, explaining why we would prefer PostgreSQL. I know some people here have issues with the MySQL crashme test results, but I have to say I found it possibly one of the best postgreSQL advertisements available. A 4-way comparison between Sybase, Oracle, MySQL and PostgreSQL shows PostgreSQL in an extremely flattering light. Given the missing features in MySQL (column constraints and views, for god's sake!) I had to conclude that we couldn't implement most of our projects in MySQL, even if we wanted to. I have trouble believing MySQL was suggested as a viable alternative. I know I'm preaching to the choir here, but thought you might find it of interest. Regards, Philip Yarra.
Hello, Actually if you really want to scare them. 1. Use Innodb 2. Create 5000 inserts with at least 5k of data. The table needs to have a primary key. 3. Insert the 5000 records for 50 connections (250,000 inserts). 4. Watch the deadlocks fly. They didn't believe me. Then the guy tried it live on #mysql... and it crashed. He blamed it on the fact he only gave 8 meg to Innodb. Sincerely, Joshua Drake Philip Yarra wrote: >It's funny timing - I had to prepare a comparison between PostgreSQL and >MySQL recently, explaining why we would prefer PostgreSQL. I know some >people here have issues with the MySQL crashme test results, but I have to >say I found it possibly one of the best postgreSQL advertisements >available. A 4-way comparison between Sybase, Oracle, MySQL and PostgreSQL >shows PostgreSQL in an extremely flattering light. > >Given the missing features in MySQL (column constraints and views, for >god's sake!) I had to >conclude that we couldn't implement most of our projects in MySQL, even if >we wanted to. I have trouble believing MySQL was suggested as a viable >alternative. > >I know I'm preaching to the choir here, but thought you might find it of >interest. > >Regards, Philip Yarra. > > -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-222-2783 - jd@commandprompt.com - http://www.commandprompt.com The most reliable support for the most reliable Open Source database.
In an attempt to throw the authorities off his trail, jd@commandprompt.com ("Joshua D. Drake") transmitted:
> PostgreSQL does not support read uncommited and repeatable read
> isolation levels? If that is so... then PostgreSQL is NOT ACID
> compliant?
Are you certain that the source of the information was actually
credible?
If you need "repeatable read," then that means doing it in a
transaction scope with SET TRANSACTION ISOLATION LEVEL SERIALIZABLE.
On that side of things, the information source is presumably Downright
Wrong.
As for "read uncommitted," that's taboo to ACID.
After all, reading uncommitted data means depending on facts that
might well _evaporate_. You don't WANT "dirty reads."
In an Internet registry, for instance, that would mean that someone
might consult WHOIS and get address information for a half-baked
record that, due to a lack of funds on the part of a fly-by-night
registrar, never did get committed. That's WRONG.
Or that a payroll run might pick up phantom employees that someone
_considered_ hiring, but never finished the paperwork on. Again,
that's WRONG behaviour.
What kind of fool wants to get WRONG information that hasn't been
committed? (It's a given that they are a fool; the question is what
kind of fool...)
--
wm(X,Y):-write(X),write('@'),write(Y). wm('aa454','freenet.carleton.ca').
http://www.ntlug.org/~cbbrowne/lisp.html
Rules of the Evil Overlord #17. "When I employ people as advisors, I
will occasionally listen to their advice."
<http://www.eviloverlord.com/>
Joshua D. Drake wrote: > I need a hug. *HUG*
"scott.marlowe" <scott.marlowe@ihs.com> writes: > Postgresql supports Serializable transactions, which are 100% ACID > compliant. How can I activate it? 8-) Yes, I know about SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, please read on. Given the two tables: CREATE TABLE items (item INTEGER); CREATE TABLE counts (count INTEGER); And transactions following this pattern: number := <some number>; INSERT INTO items VALUES (number); nr := SELECT COUNT(*) FROM items; INSERT INTO counts VALUES(nr); COMMIT; If these transactions are executed serially, the following condition always holds once the tables are non-empty: (*) (SELECT COUNT(*) FROM items) = (SELECT MAX(count) FROM counts) Now look at the following history: Session 1 Session 2 number := <some number>; number := <some number>; INSERT INTO items VALUES (number); INSERT INTO items VALUES (number); nr := SELECT COUNT(*) FROM items; nr := SELECT COUNT(*) FROM items; INSERT INTO counts VALUES (nr); INSERT INTO counts VALUES (nr); COMMIT; COMMIT; If you enter these commands in two parallel psql sessions, in the order indicated, condition (*) no longer holds once both transactions are completed. Therefore, PostgreSQL must have generated a non-serializable history. Is this a bug, or is SQLxx serializability defined in different terms?
Florian Weimer <fw@deneb.enyo.de> writes: > Is this a bug, or is SQLxx serializability defined in different terms? Strictly speaking, we do not guarantee serializability because we do not do predicate locking. See for example http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php AFAIK, no commercial database does predicate locking either, so we all fall short of true serializability. The usual solution if you need the sort of behavior you're talking about is to take a non-sharable write lock on the table you want to modify, so that only one transaction can do the COUNT/INSERT at a time. regards, tom lane
Quoth tgl@sss.pgh.pa.us (Tom Lane): > Florian Weimer <fw@deneb.enyo.de> writes: >> Is this a bug, or is SQLxx serializability defined in different terms? > > Strictly speaking, we do not guarantee serializability because we do not > do predicate locking. See for example > http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php > > AFAIK, no commercial database does predicate locking either, so we all > fall short of true serializability. The usual solution if you need the > sort of behavior you're talking about is to take a non-sharable write > lock on the table you want to modify, so that only one transaction can > do the COUNT/INSERT at a time. It's worth elaborating on the answers here because the "count(*) is not stable" argument has been made by MySQL folks as, in effect, an argument that ACID is impossible, and thus conforming to it is pointless, and thus, anyone that claims to conform to it must be a big fat idiot/liar. This also begs two other questions. 1. What, _exactly_, is the aggregate select getting? The assumption made in Florian's article is that "SELECT COUNT(*) from items" is getting the dynamic thing thatis the number of rows in the table. _Reality_ is that what is actually returned by any database system we know of is something else, that would be betterdescribed as "How many rows did we find when we ran the query?" In the context of talking about "ACID," the answer, more precisely, is "How many committed tuples were there in ITEMSwhen we started running this query?" If two queries query this value at the same time, we would expect them to get the same answer. Which means that the condition Florian describes seems ill-framed. 2. Are aggregates actually something that should we should imagine applying predicate properties to? They aren't invertable, so I wouldn't think so. Would you expect to be able to do a query like: update transaction_table set count(*) = 897411; ??? Then why would you expect to be able to cast assertions based on aggregates? We aren't working in some pure form of ML or Haskell, where nothing ever gets reassigned, but rather the environment gets replaced with one containing new values. That kind of system does not cope well with concurrency; if ALL side-effects are forbidden, then that rules out having just about any kind of concurrency. -- wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','ntlug.org'). http://www.ntlug.org/~cbbrowne/nonrdbms.html Signs of a Klingon Programmer #5: "I have challenged the entire quality assurance team to a Bat-Leth contest. They will not concern us again."
Christopher Browne <cbbrowne@acm.org> writes:
> It's worth elaborating on the answers here
Agreed.
> This also begs two other questions.
> 1. What, _exactly_, is the aggregate select getting?
> The assumption made in Florian's article is that
> "SELECT COUNT(*) from items"
> is getting the dynamic thing that is the number of rows in the
> table.
What it's really getting is the static thing that was the number of
committed rows as of the relevant query snapshot instant (either start
of transaction or start of command, depending on your isolation mode).
This *is* reproducible (as long as you keep using the same snapshot of
course). Whether it is useful to solve any particular problem is quite
a different discussion.
> If two queries query this value at the same time, we would expect
> them to get the same answer.
They would be guaranteed to get the same answer as long as no
transaction (that affected the table) commits between the instants of
their two snapshots.
(Caveat: if you are thinking about queries inside transactions that
have themselves modified the table, then such queries see the effects
of prior commands in their own transaction, on top of the relevant
query snapshot of the outside world's effects.)
> 2. Are aggregates actually something that should we should imagine
> applying predicate properties to?
Actually, I think the standard academic discussion of this shows that
you can have serializability failures any time a row is added/deleted/
updated that would pass the WHERE clause of any concurrent query.
Whether that query is aggregating or not is not relevant.
regards, tom lane
Florian, if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB, then InnoDB uses next-key locking in every SELECT, and transactions really are serializable in the mathematical sense. I think the same holds for DB2 and MS SQL Server. PostgreSQL and Oracle use a loophole of SQL-1992 in defining serializability. In SQL-1992 serializability is defined as 'no phantom rows can appear if a read is repeated'. Oracle and PostgreSQL conform to this SQL-1992 definition, but their execution is really not serializable in the mathematical sense, like your example with COUNT(*) shows. Peter Gulutzan notes this in his paper: http://www.dbazine.com/gulutzan6.html. Another example of the flaw in the Oracle and PostgreSQL model is shown if you try to code a UNIQUE check manually. If your SELECT returns no conflicting rows, that does not guarantee there are none at the serialization point of the INSERT. ... Another note: Joshua Drake claimed that InnoDB deadlocks if you try to do inserts concurrently to a table with a primary key. I guess he refers to some old version, where InnoDB still used SELECT MAX(auto_inc_column) FROM table FOR UPDATE; to determine the next auto-inc key value. Because the execution has to be serializable :), it is not that easy to make this algorithm to avoid deadlocks if inserts are made to the end of the index. But the InnoDB algorithm was changed a long time ago. Nowadays innoDB uses an internal counter. I ran Joshua's test with MySQL-4.0.15 with the default my.cnf settings, and no deadlocks were generated. Best regards, Heikki .................. List: postgresql-general Subject: Re: [HACKERS] PostgreSQL not ACID compliant? From: Florian Weimer <fw () deneb ! enyo ! de> Date: 2003-09-20 20:33:11 [Download message RAW] "scott.marlowe" <scott.marlowe@ihs.com> writes: > Postgresql supports Serializable transactions, which are 100% ACID > compliant. How can I activate it? 8-) Yes, I know about SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, please read on. Given the two tables: CREATE TABLE items (item INTEGER); CREATE TABLE counts (count INTEGER); And transactions following this pattern: number := <some number>; INSERT INTO items VALUES (number); nr := SELECT COUNT(*) FROM items; INSERT INTO counts VALUES(nr); COMMIT; If these transactions are executed serially, the following condition always holds once the tables are non-empty: (*) (SELECT COUNT(*) FROM items) = (SELECT MAX(count) FROM counts) Now look at the following history: Session 1 Session 2 number := <some number>; number := <some number>; INSERT INTO items VALUES (number); INSERT INTO items VALUES (number); nr := SELECT COUNT(*) FROM items; nr := SELECT COUNT(*) FROM items; INSERT INTO counts VALUES (nr); INSERT INTO counts VALUES (nr); COMMIT; COMMIT; If you enter these commands in two parallel psql sessions, in the order indicated, condition (*) no longer holds once both transactions are completed. Therefore, PostgreSQL must have generated a non-serializable history. Is this a bug, or is SQLxx serializability defined in different terms?
"Heikki Tuuri" <Heikki.Tuuri@innodb.com> writes: > if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB, > then InnoDB uses next-key locking in every SELECT, and transactions really > are serializable in the mathematical sense. My understanding is that next-key locking only helps when all your predicates are point or range searches against an available b-tree index. While that might cover many practical cases, it can hardly be called a complete solution to the problem of serializability. Another serious problem with predicate locking in general is that it's prone to creating deadlocks. I gave an example here: http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php Since next-key locking is just an approximate form of predicate locking (approximate in the sense that it may map many different predicates into the same lock), I'd expect it to generate even more deadlocks than true predicate locking would. In short, next-key is not a perfect solution either. > Another note: Joshua Drake claimed that InnoDB deadlocks if you try to do > inserts concurrently to a table with a primary key. I guess he refers to > some old version, where InnoDB still used > SELECT MAX(auto_inc_column) FROM table FOR UPDATE; > to determine the next auto-inc key value. Because the execution has to be > serializable :), it is not that easy to make this algorithm to avoid > deadlocks if inserts are made to the end of the index. Yup, pretty much the same point I made above. Inserting at the end of the index requires a next-key lock on a (notional) infinite key. So all those inserts need the same exclusive lock. > But the InnoDB algorithm was changed a long time ago. Nowadays innoDB uses > an internal counter. I ran Joshua's test with MySQL-4.0.15 with the default > my.cnf settings, and no deadlocks were generated. Should I read that as saying that you fail to take out the required next-key lock when inserting an autoincremented value? Tsk tsk. The inserts may not conflict with each other, but I think you'll find that serializability is violated for concurrent selects looking at the table. regards, tom lane
Tom, ----- Original Message ----- From: "Tom Lane" <tgl@sss.pgh.pa.us> To: "Heikki Tuuri" <Heikki.Tuuri@innodb.com> Cc: <pgsql-hackers@postgresql.org> Sent: Sunday, September 21, 2003 10:32 AM Subject: Re: [HACKERS] PostgreSQL not ACID compliant? > "Heikki Tuuri" <Heikki.Tuuri@innodb.com> writes: > > if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB, > > then InnoDB uses next-key locking in every SELECT, and transactions really > > are serializable in the mathematical sense. > > My understanding is that next-key locking only helps when all your > predicates are point or range searches against an available b-tree > index. all SQL queries are performed through index searches. That is why next-key locking enforces serializability. IBM researcher C. Mohan has written papers about next-key locking. Next-key locking is an approximation of predicate locking. We simply lock more to make sure the 'predicates' themselves are locked. > While that might cover many practical cases, it can hardly > be called a complete solution to the problem of serializability. It is a complete solution. Another approximation of predicate locking is table level locking, a solution which Oracle used some 15 years ago, if you switched it on the SERIALIZABLE isolation level. > Another serious problem with predicate locking in general is that it's > prone to creating deadlocks. I gave an example here: > http://archives.postgresql.org/pgsql-general/2003-01/msg01581.php > Since next-key locking is just an approximate form of predicate locking > (approximate in the sense that it may map many different predicates into > the same lock), I'd expect it to generate even more deadlocks than true > predicate locking would. > > In short, next-key is not a perfect solution either. Of course, on the SERIALIZABLE isolation level we must lock more. Then deadlocks happen more often. ... > > But the InnoDB algorithm was changed a long time ago. Nowadays innoDB uses > > an internal counter. I ran Joshua's test with MySQL-4.0.15 with the default > > my.cnf settings, and no deadlocks were generated. > > Should I read that as saying that you fail to take out the required > next-key lock when inserting an autoincremented value? Tsk tsk. > The inserts may not conflict with each other, but I think you'll find > that serializability is violated for concurrent selects looking at > the table. No, because if you set the transaction isolation level SERIALIZABLE, and use a SELECT to read the MAX() of the auto-inc column, and try a concurrent insert from another connection, that insert will block. In the counter method, the auto-inc values assigned to newly inserted rows within the same transaction are not guaranteed to be sequential. That is why we can avoid the locking if we use the counter method. > regards, tom lane Best regards, Heikki
Florian, if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB, then InnoDB uses next-key locking in every SELECT, and transactions really are serializable in the mathematical sense. I think the same holds for DB2 and MS SQL Server. PostgreSQL and Oracle use a loophole of SQL-1992 in defining serializability. In SQL-1992 serializability is defined as 'no phantom rows can appear if a read is repeated'. Oracle and PostgreSQL conform to this SQL-1992 definition, but their execution is really not serializable in the mathematical sense, like your example with COUNT(*) shows. Peter Gulutzan notes this in his paper: http://www.dbazine.com/gulutzan6.html. Another example of the flaw in the Oracle and PostgreSQL model is shown if you try to code a UNIQUE check manually. If your SELECT returns no conflicting rows, that does not guarantee there are none at the serialization point of the INSERT. ... Another note: Joshua Drake claimed that InnoDB deadlocks if you try to do inserts concurrently to a table with a primary key. I guess he refers to some old version, where InnoDB still used SELECT MAX(auto_inc_column) FROM table FOR UPDATE; to determine the next auto-inc key value. Because the execution has to be serializable :), it is not that easy to make this algorithm to avoid deadlocks if inserts are made to the end of the index. But the InnoDB algorithm was changed a long time ago. Nowadays innoDB uses an internal counter. I ran Joshua's test with MySQL-4.0.15 with the default my.cnf settings, and no deadlocks were generated. Best regards, Heikki .................. List: postgresql-general Subject: Re: [HACKERS] PostgreSQL not ACID compliant? From: Florian Weimer <fw () deneb ! enyo ! de> Date: 2003-09-20 20:33:11 [Download message RAW] "scott.marlowe" <scott.marlowe@ihs.com> writes: > Postgresql supports Serializable transactions, which are 100% ACID > compliant. How can I activate it? 8-) Yes, I know about SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, please read on. Given the two tables: CREATE TABLE items (item INTEGER); CREATE TABLE counts (count INTEGER); And transactions following this pattern: number := <some number>; INSERT INTO items VALUES (number); nr := SELECT COUNT(*) FROM items; INSERT INTO counts VALUES(nr); COMMIT; If these transactions are executed serially, the following condition always holds once the tables are non-empty: (*) (SELECT COUNT(*) FROM items) = (SELECT MAX(count) FROM counts) Now look at the following history: Session 1 Session 2 number := <some number>; number := <some number>; INSERT INTO items VALUES (number); INSERT INTO items VALUES (number); nr := SELECT COUNT(*) FROM items; nr := SELECT COUNT(*) FROM items; INSERT INTO counts VALUES (nr); INSERT INTO counts VALUES (nr); COMMIT; COMMIT; If you enter these commands in two parallel psql sessions, in the order indicated, condition (*) no longer holds once both transactions are completed. Therefore, PostgreSQL must have generated a non-serializable history. Is this a bug, or is SQLxx serializability defined in different terms?
Heikki Tuuri kirjutas P, 21.09.2003 kell 12:51: > Tom, > > ----- Original Message ----- > From: "Tom Lane" <tgl@sss.pgh.pa.us> > To: "Heikki Tuuri" <Heikki.Tuuri@innodb.com> > Cc: <pgsql-hackers@postgresql.org> > Sent: Sunday, September 21, 2003 10:32 AM > Subject: Re: [HACKERS] PostgreSQL not ACID compliant? > > > > "Heikki Tuuri" <Heikki.Tuuri@innodb.com> writes: > > > if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB, > > > then InnoDB uses next-key locking in every SELECT, and transactions > really > > > are serializable in the mathematical sense. > > > > My understanding is that next-key locking only helps when all your > > predicates are point or range searches against an available b-tree > > index. > > all SQL queries are performed through index searches. Does that mean that an index is allways needed for predicate column, or is this an abstract "index" in some realational algebra sense ? > That is why next-key > locking enforces serializability. IBM researcher C. Mohan has written papers > about next-key locking. Next-key locking is an approximation of predicate > locking. We simply lock more to make sure the 'predicates' themselves are > locked. > > > While that might cover many practical cases, it can hardly > > be called a complete solution to the problem of serializability. > > It is a complete solution. Is this solution complete only for MAX() case, or is this a general solution woking for things line AVG or STDDEV and perhaps all user-defined aggregates as well ? > Another approximation of predicate locking is > table level locking, a solution which Oracle used some 15 years ago, if you > switched it on the SERIALIZABLE isolation level. Table level locking seems to be a complete solution indeed, just not concurrent at all. It may be that we have to forget concurrency to get complete and general concurrency ;( Or is "next key locking" something more than a solution for getting continuous nextval() 's ? ------------------ Hannu
Philip Yarra wrote: > It's funny timing - I had to prepare a comparison between PostgreSQL and > MySQL recently, explaining why we would prefer PostgreSQL. I know some > people here have issues with the MySQL crashme test results, but I have to > say I found it possibly one of the best postgreSQL advertisements > available. A 4-way comparison between Sybase, Oracle, MySQL and PostgreSQL > shows PostgreSQL in an extremely flattering light. They have been improving their fairness over at MySQL. I know Zak Greant (added as CC) removed all the inaccurate PostgreSQL stuff that was in the MySQL manuals. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Zak Greant wrote: > Thanks for the Cc: and for noticing the fixes! > > To be fair, Paul DuBois and Andrey Stroganov did the actual work - I > only did some pointing and grunting. I am not sure that we have removed > everything yet - I still need to do a full sweep of the docs. In any > case, this is a good start > > Also, I noticed the long GPL/MySQL related thread on [GENERAL]. It > looked like there are some good points in the discussion. Once I get > some other licensing-related issues taken care up, I will take a look > at cleaning up the licensing overview docs that were mentioned and > critiqued. Thanks again. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Hannu, ----- Original Message ----- From: "Hannu Krosing" <hannu@tm.ee> To: "Heikki Tuuri" <Heikki.Tuuri@innodb.com> Cc: <pgsql-hackers@postgresql.org> Sent: Monday, September 22, 2003 10:00 PM Subject: Re: [HACKERS] PostgreSQL not ACID compliant? > Heikki Tuuri kirjutas P, 21.09.2003 kell 12:51: > > Tom, > > > > ----- Original Message ----- > > From: "Tom Lane" <tgl@sss.pgh.pa.us> > > To: "Heikki Tuuri" <Heikki.Tuuri@innodb.com> > > Cc: <pgsql-hackers@postgresql.org> > > Sent: Sunday, September 21, 2003 10:32 AM > > Subject: Re: [HACKERS] PostgreSQL not ACID compliant? > > > > > > > "Heikki Tuuri" <Heikki.Tuuri@innodb.com> writes: > > > > if you set the transaction isolation level SERIALIZABLE in MySQL/InnoDB, > > > > then InnoDB uses next-key locking in every SELECT, and transactions > > really > > > > are serializable in the mathematical sense. > > > > > > My understanding is that next-key locking only helps when all your > > > predicates are point or range searches against an available b-tree > > > index. > > > > all SQL queries are performed through index searches. > > Does that mean that an index is allways needed for predicate column, or > is this an abstract "index" in some realational algebra sense ? in InnoDB, all tables have a clustered index, where rows are stored. Normally, it is the primary key of the table. All searches to the table go through indexes, even table scans. > > That is why next-key > > locking enforces serializability. IBM researcher C. Mohan has written papers > > about next-key locking. Next-key locking is an approximation of predicate > > locking. We simply lock more to make sure the 'predicates' themselves are > > locked. > > > > > While that might cover many practical cases, it can hardly > > > be called a complete solution to the problem of serializability. > > > > It is a complete solution. > > Is this solution complete only for MAX() case, or is this a general > solution woking for things line AVG or STDDEV and perhaps all > user-defined aggregates as well ? It works for all queries. An AVG(), for example, does a table scan through the clustered index, and consequently locks the whole table, if you use the SERIALIZABLE isolation level. > > Another approximation of predicate locking is > > table level locking, a solution which Oracle used some 15 years ago, if you > > switched it on the SERIALIZABLE isolation level. > > Table level locking seems to be a complete solution indeed, just not > concurrent at all. It may be that we have to forget concurrency to get > complete and general concurrency ;( No, not at all. If you want SERIALIZABLE execution, you can do the following: 1) Report transactions, like AVG(), are often read-only, and you can use the default multiversioning concurrency control method of InnoDB to perform them without setting any locks. The database simply reads a snapshot of the database, and that snapshot is the serialization point of the read-only transaction. 2) Update transactions are usually smaller, they will not scan whole tables. For them you can use the SERIALIZABLE isolation level and next key locks. The serialization point of the transaction is the COMMIT time of the transaction. > Or is "next key locking" something more than a solution for getting > continuous nextval() 's ? Yes. > ------------------ > Hannu Best regards, Heikki
On Wednesday, Sep 24, 2003, at 19:10 Atlantic/Reykjavik, Bruce Momjian wrote: > Philip Yarra wrote: >> It's funny timing - I had to prepare a comparison between PostgreSQL >> and >> MySQL recently, explaining why we would prefer PostgreSQL. I know some >> people here have issues with the MySQL crashme test results, but I >> have to >> say I found it possibly one of the best postgreSQL advertisements >> available. A 4-way comparison between Sybase, Oracle, MySQL and >> PostgreSQL >> shows PostgreSQL in an extremely flattering light. > > They have been improving their fairness over at MySQL. I know Zak > Greant (added as CC) removed all the inaccurate PostgreSQL stuff that > was in the MySQL manuals. Hi Bruce and all, Thanks for the Cc: and for noticing the fixes! To be fair, Paul DuBois and Andrey Stroganov did the actual work - I only did some pointing and grunting. I am not sure that we have removed everything yet - I still need to do a full sweep of the docs. In any case, this is a good start Also, I noticed the long GPL/MySQL related thread on [GENERAL]. It looked like there are some good points in the discussion. Once I get some other licensing-related issues taken care up, I will take a look at cleaning up the licensing overview docs that were mentioned and critiqued. Cheers! --zak
>>>>> "Tom" == Tom Lane <tgl@sss.pgh.pa.us> writes: Tom> AFAIK, no commercial database does predicate locking either, True .. Tom> so we all fall short of true serializability. The usual Tom> solution if you need the sort of behavior you'retalking Tom> about is to take a non-sharable write lock on the table you Tom> want to modify, so that only onetransaction can do the Not really. If you have B+-tree indexes on the table you can get by with key-value locking (as in ARIES/KVL) and achieve some of the effects of predicate locking to get true serializability without losing too much concurrency. While this falls short in the general case, it turns out to be pretty acceptable normally (when indexes are present). -- Pip-pip Sailesh http://www.cs.berkeley.edu/~sailesh
I'm afraid I'm confused about something very simple... but anyway I need to run a query on a varchar field containing a backslash. My first attempt looked like this: SELECT smth. FROM tbl WHERE situation LIKE '%\\%'; This did not returned any rows. I looked up for a reference, confirmed that "... double-backslash is required to represent a literal backslash." http://www.postgresql.org/docs/aw_pgsql_book/node139.html#copy_backslash_han dling But when I doubled the number of backslashes: SELECT smth. FROM tbl WHERE situation LIKE '%\\\\%'; - it actually worked fine. Same thing happens with using regex: situation ~ '\\'; Could someone shed some light on this, please. Mike.
Michael Brusser <michael@synchronicity.com> writes:
> But when I doubled the number of backslashes:
> SELECT smth. FROM tbl WHERE situation LIKE '%\\\\%';
> - it actually worked fine.
Backslash is special to both the string-literal parser and the LIKE code.
So when you write the above, the pattern value that arrives at the LIKE
processor has one less level of backslashing:%\\%
and the LIKE processor interprets this as percent, a literal backslash,
and another percent.
regards, tom lane
Tom Lane kirjutas R, 03.10.2003 kell 18:34: > Michael Brusser <michael@synchronicity.com> writes: > > But when I doubled the number of backslashes: > > SELECT smth. FROM tbl WHERE situation LIKE '%\\\\%'; > > - it actually worked fine. > > Backslash is special to both the string-literal parser and the LIKE code. > So when you write the above, the pattern value that arrives at the LIKE > processor has one less level of backslashing: > %\\% > and the LIKE processor interprets this as percent, a literal backslash, > and another percent. Regarding the dollar-quoting discussions - Will we be able to write the above query as SELECT smth. FROM tbl WHERE WHERE situation LIKE $$%\\%$$; in 7.4 or is \ still special there ? if it is then one \ in regex in plpython still needs to be entered as \\\\\\\\ which has some geek coolness but would not be what I'd prefer to do on a regular basis. ---------------- Hannu
----- Original Message ----- From: "Hannu Krosing" <hannu@tm.ee> > > Regarding the dollar-quoting discussions - > > Will we be able to write the above query as > > SELECT smth. FROM tbl WHERE WHERE situation LIKE $$%\\%$$; > Yes, as I understand it (as long as you remove the one of the WHEREs :-) ). If not we need some rethinking. Think of this as our equivalent of XML's CDATA quoting - you don't need to escape & or < or > inside a CDATA node. > in 7.4 or is \ still special there ? I don't believe so. cheers andrew
but this will be in 7.5, not 7.4, won't it? Andrew Dunstan wrote: >----- Original Message ----- >From: "Hannu Krosing" <hannu@tm.ee> > > >>Regarding the dollar-quoting discussions - >> >>Will we be able to write the above query as >> >>SELECT smth. FROM tbl WHERE WHERE situation LIKE $$%\\%$$; >> >> >> > >Yes, as I understand it (as long as you remove the one of the WHEREs :-) ). >If not we need some rethinking. Think of this as our equivalent of XML's >CDATA quoting - you don't need to escape & or < or > inside a CDATA node. > > > > >>in 7.4 or is \ still special there ? >> >> > >I don't believe so. > > > >
Hannu Krosing <hannu@tm.ee> writes:
> Regarding the dollar-quoting discussions -
> Will we be able to write the above query as
> SELECT smth. FROM tbl WHERE WHERE situation LIKE $$%\\%$$;
> in 7.4 or is \ still special there ?
We were discussing that for 7.5 not 7.4. But yeah, it would work that
way because \ won't be treated specially in dollar-quoted literals.
regards, tom lane