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.



Fri, 19 Sep 2003 14:49:49 -0700
"Joshua D. Drake" <jd@commandprompt.com>
PostgreSQL not ACID compliant?
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.  


Fri, 19 Sep 2003 17:10:07 -0600 (MDT)
"scott.marlowe" <scott.marlowe@ihs.com>
Re: PostgreSQL not ACID compliant?
"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

Fri, 19 Sep 2003 19:28:34 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL not ACID compliant? 
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.



Fri, 19 Sep 2003 17:17:06 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: PostgreSQL not ACID compliant?


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 ...


Fri, 19 Sep 2003 21:27:04 -0300 (ADT)
"Marc G. Fournier" <scrappy@postgresql.org>
Re: PostgreSQL not ACID compliant?
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.



Fri, 19 Sep 2003 17:48:26 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: PostgreSQL not ACID compliant?
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.


Sat, 20 Sep 2003 11:05:58 +1000 (EST)
Philip Yarra <philip@utiba.com>
Re: PostgreSQL not ACID compliant?
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.



Fri, 19 Sep 2003 18:11:09 -0700
"Joshua D. Drake" <jd@commandprompt.com>
Re: PostgreSQL not ACID compliant?
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/>

Fri, 19 Sep 2003 21:13:09 -0400
Christopher Browne <cbbrowne@acm.org>
Re: PostgreSQL not ACID compliant?
Joshua D. Drake wrote:
> I need a hug.

*HUG*


Sat, 20 Sep 2003 13:12:04 +0200
Gaetano Mendola <mendola@bigfoot.com>
Re: PostgreSQL not ACID compliant?
"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?

Sat, 20 Sep 2003 22:33:11 +0200
Florian Weimer <fw@deneb.enyo.de>
Re: PostgreSQL not ACID compliant?
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

Sat, 20 Sep 2003 17:48:47 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL not ACID compliant? 
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 that is 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 better described 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 ITEMS when
    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."

Sat, 20 Sep 2003 19:19:36 -0400
Christopher Browne <cbbrowne@acm.org>
Re: PostgreSQL not ACID compliant?
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

Sat, 20 Sep 2003 20:50:25 -0400
Tom Lane <tgl@sss.pgh.pa.us>
Re: PostgreSQL not ACID compliant? 
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, Pennsylvania 19073

Wed, 24 Sep 2003 15:10:42 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostgreSQL not ACID compliant?
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


Wed, 24 Sep 2003 23:40:36 +0000
Zak Greant <zak@mysql.com>
Re: PostgreSQL not ACID compliant?
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, Pennsylvania 19073

Wed, 24 Sep 2003 23:04:03 -0400 (EDT)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: PostgreSQL not ACID compliant?
>>>>> "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're talking
    Tom> about is to take a non-sharable write lock on the table you
    Tom> want to modify, so that only one transaction 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



26 Sep 2003 03:09:39 -0700
Sailesh Krishnamurthy <sailesh@cs.berkeley.edu>
Re: PostgreSQL not ACID compliant?