Re: PostgreSQL and ACID properties support

On Thu, Aug 07, 2003 at 21:28:52 -0700,
Neil Zanella <nzanella(at)cs(dot)mun(dot)ca> wrote:
> Hello,
>
> I would like to know to what extent PostgreSQL includes support for the ACID
> properties of transactions (Atomicity, Consistency, Isolation, and Durability).
> In particular, does PostgreSQL include constructs to guarantee that a sequence
> of SQL statements is executed atomically (i.e., either all execute to completion
> or none of them do). Also, with regards to isolation, what is the status of

Yes postgres is ACID compliant.

> PostgreSQL's implementation with respect to providing the option of concurrency
> of transactions as opposed to the operations taking place serially? Also, does

Postgres uses MVCC so that readers don't block writers and writers don't
block readers. Transactions proceed concurrently where there is no
contention.

> PostgreSQL fully support SQL99 CHECK constraints? And if the system crashes...
I don't know enough about the standard to answer this one fully, but it
does support check constraints. The syntax only allows references to columns
in the table the check is attached to. But you can call functions that
do queries versus other tables. So you should be able to do the tasks
you want, though you might not be able to use standard syntax.

> is the database guaranteed to remain in a consistent state?

If the underlying file system remains intact, then the database will have
all committed transactions saved. If the crash occurs in a way that
corrupts the disks (be careful of ide disks that report writes as complete
before the data is safe) then there are no guarentees.