savepoint problems

From: Linos <info(at)linos(dot)es>
To: pgsql-general(at)postgresql(dot)org
Subject: savepoint problems
Date: 2008-08-01 16:30:36
Message-ID: 48933A2C.8030806@linos.es
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Hello,
i have migrated from Maxdb to Postgresql recently and i am having a speed problem in
large transactions over slow links because of autorollback on error postgresql feature, i
create data in any tables with triggers in other tables and i do large inserts from the
data created in this tables to any other postgresql servers (replication purposes), for
this example maybe we can say 20000 rows, i want do this in a transaction to make rollback
on certain errors, but i use a fallback feature if a duplicated is found i relaunch the
last insert data in a update to the existing row, so i have to set savepoint and release
after the insert has been successful, so my traffic flow is anything like this.

client server
begin ------------------>
<----------------- ok
savepoint------------->
<----------------- ok
insert ------------------>
<----------------- ok
release savepoint--->
<----------------- ok
insert ------------------>
<----------------- error duplicated key
update ----------------->
<----------------- ok
release savepoint--->
<----------------- ok
20000 rows later....
commit ----------------->
<----------------- ok

obviously in a slow link this is slow as hell, i have posted this same email in spanish
pgsql-es-ayuda where Alvaro Herrera has replied my with some solutions (thanks Alvaro for
your great support in spanish mailing list!), mainly two:

1- create a function that uses EXCEPTION to save data traffic or the function like an
upsert that can be located in the example 38-1 at
http://www.postgresql.org/docs/8.3/static/plpgsql-control-structures.html but this have
the problem that i still have the savepoint overhead.

2- create a function that make a select locking the table before decide to do an insert or
an update.

Well i would like to know if every can help with any other idea or any notes on this
problem? Other question i have it is how i could create a function without be sure the
number of columns to insert/update. Thanks in advance.

Best Regards,
Miguel Angel.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Martijn van Oosterhout 2008-08-01 17:01:32 Re: savepoint problems
Previous Message David Wilson 2008-08-01 15:31:20 Re: use of column in COPY