Re: Unanswered questions about Postgre

Joe Kislo wrote:
> Yes. There are certainly times when a transaction needs to be
> ABORTed. However, there are many reasons why the database should not
> abort a transaction if it does not need to. There is obviously no
> reason why a transaction needs to be aborted for syntax errors. There
> is obviously no reason why a transaction needs to be aborted for say,
> trying to insert a duplicate primary key. The -insert- can fail, report
> it as such, and the application can determine if a rollback is
> nessasary. If you don't believe me, here's two fully SQL-92 compliant
> databases, Oracle and interbase, which do not exhibit this behavior:

You're right.

But it'd be (up to now) impossible to implement in Postgres.
Postgres doesn't record any undo information during the
execution of a transaction (like Oracle for example does in
the rollback segments). The way Postgres works is not to
overwrite existing tuples, but to stamp them outdated and
insert new ones. In the case of a ROLLBACK, just the stamps
made are flagged invalid (in pg_log).

If you do a

INSERT INTO t1 SELECT * FROM t2;

there could occur a duplicate key error. But if it happens in
the middle of all the rows inserted, the first half of rows
is already in t1, with the stamp of this transaction to come
alive. The only way to not let them show up is to invalidate
the entire transaction.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #