Interesting debate. 2011/9/13 Marti Raudsepp <marti@xxxxxxxxx>: > Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a > SAVEPOINT it can roll back to in case of an error. Are you sure? In theory I always understood that there are no "subtransactions". In fact when looking at the docs there is chapter 39.6.6. saying "By default, any error occurring in a PL/pgSQL function aborts execution of the function, and indeed of the surrounding transaction as well. You can trap errors and recover from them by using a BEGIN block with an EXCEPTION clause." (http://www.postgresql.org/docs/current/interactive/plpgsql-control-structures.html ) So the doc isn't totally explicit about this. But whatever: What would be the the function of a subtransaction? To give the possibility to recover and continue within the surrounding transaction? Stefan 2011/9/13 Marti Raudsepp <marti@xxxxxxxxx>: > On Tue, Sep 13, 2011 at 19:34, Robert Klemme <shortcutter@xxxxxxxxxxxxxx> wrote: >> I don't think so. You only need to catch the error (see attachment). >> Or does this create a sub transaction? > > Yes, every BEGIN/EXCEPTION block creates a subtransaction -- like a > SAVEPOINT it can roll back to in case of an error. > >> Yes, I mentioned the speed issue. But regardless of the solution for >> MySQL's "INSERT..ON DUPLICATE KEY UPDATE" which Igor mentioned you >> will have the locking problem anyhow if you plan to insert >> concurrently into the same table and be robust. > > In a mass-loading application you can often divide the work between > threads in a manner that doesn't cause conflicts. > > For example, if the unique key is foobar_id and you have 4 threads, > thread 0 will handle rows where (foobar_id%4)=0, thread 1 takes > (foobar_id%4)=1 etc. Or potentially hash foobar_id before dividing the > work. > > I already suggested this in my original post. > > Regards, > Marti > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance > -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
