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.
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 |