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@Yahoo.com #