Re: why postgresql over other RDBMS

We're derailing the thread, but...

On 5/24/07, A.M. <agentm(at)themactionfaction(dot)com> wrote:
> 2PC requires that the modifications already be in concrete. What I
> suggest is a method for a new connection to insert itself into an
> existing (sub-)transaction SQL stream, make changes, and commit to
> the root or parent transaction.

The problem with long-running transactions is that they need to avoid
locking the resources they touch. Short-running transactions are bad
enough as they stand -- until fairly recently (8.1? 8.2?), merely
inserting or updating a row that had a foreign-key reference to
another table row would lock the referenced row until the end of the
transaction, by issuing an implicit "select ... for update".

Although a mechanism whereby multiple connections can share a single
session/transaction is probably easy to implement, using long-running
transactions to isolate DDL changes is not feasible at the moment
because PostgreSQL currently acquires an AccessExclusiveLock on the
modified table until the transaction ends, which means that concurrent
transactions would be blocked from even querying the table.

I don't know PostgreSQL's internals, so I can only postulate that this
locking occurs because PostgreSQL holds a single copy of the schema
and related bookeeping structures in memory.

Alexander.