pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCommit every processed record
4+ messages / 4 participants
[nested] [flat]
* Commit every processed record
@ 2008-04-07 12:46 luke.78@libero.it <luke.78@libero.it>
2008-04-07 13:01 ` Re: Commit every processed record A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-04-07 13:38 ` Re: Commit every processed record Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 2 replies; 4+ messages in thread
From: luke\.78\@libero\.it @ 2008-04-07 12:46 UTC (permalink / raw)
To: pgsql-sql
Hi,
I have to execute commit for evey record that i processed during a cursor fetch in a function.
There is a way to do it?
Thanks in advance.
Luke.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Commit every processed record
2008-04-07 12:46 Commit every processed record luke.78@libero.it <luke.78@libero.it>
@ 2008-04-07 13:01 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-04-07 14:22 ` Re: Commit every processed record Bart Degryse <Bart.Degryse@indicator.be>
1 sibling, 1 reply; 4+ messages in thread
From: A. Kretschmer @ 2008-04-07 13:01 UTC (permalink / raw)
To: pgsql-sql
am Mon, dem 07.04.2008, um 14:46:50 +0200 mailte luke.78@libero.it folgendes:
> Hi,
> I have to execute commit for evey record that i processed during a cursor fetch in a function.
> There is a way to do it?
No.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Commit every processed record
2008-04-07 12:46 Commit every processed record luke.78@libero.it <luke.78@libero.it>
2008-04-07 13:01 ` Re: Commit every processed record A. Kretschmer <andreas.kretschmer@schollglas.com>
@ 2008-04-07 14:22 ` Bart Degryse <Bart.Degryse@indicator.be>
0 siblings, 0 replies; 4+ messages in thread
From: Bart Degryse @ 2008-04-07 14:22 UTC (permalink / raw)
To: pgsql-sql; A. Kretschmer <andreas.kretschmer@schollglas.com>
Well, actually there is.
Do the processing in a plperlu function which uses it's own connection to the db. Then every instance of the function will have it's own transaction.
Try to start that perl connection outside the function or your performance will drop too much.
I use this technique to fetch, process and store records from an oracle db (erp) in a postgresql db (datawarehousing/reporting/external data).
It gets me some 500,000 records in little over 50 minutes.
Probably real perl addicts would have a lot to say about my coding, but being a perl illiterate (does one write it like this?) I must say that I'm quite happy with the result.
All I used was the perl chapter in the postgresql manual (big hurray for the manual), perl express for testing and debugging of a lot of very
basic things like hashes (yes, illiterate !-) and EMS SQL Manager.
Good luck
>>> "A. Kretschmer" <andreas.kretschmer@schollglas.com> 2008-04-07 15:01 >>>
am Mon, dem 07.04.2008, um 14:46:50 +0200 mailte luke.78@libero.it folgendes:
> Hi,
> I have to execute commit for evey record that i processed during a cursor fetch in a function.
> There is a way to do it?
No.
Regards, Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ( http://wwwkeys.de.pgp.net/ )
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Commit every processed record
2008-04-07 12:46 Commit every processed record luke.78@libero.it <luke.78@libero.it>
@ 2008-04-07 13:38 ` Craig Ringer <craig@postnewspapers.com.au>
1 sibling, 0 replies; 4+ messages in thread
From: Craig Ringer @ 2008-04-07 13:38 UTC (permalink / raw)
To: luke.78@libero.it <luke.78@libero.it>; +Cc: pgsql-sql
luke.78@libero.it wrote:
> Hi,
> I have to execute commit for evey record that i processed during a cursor fetch in a function.
> There is a way to do it?
>
>
Sure. Do it outside PL/PgSQL, using a database client API like those
available for Python, Java, Perl, etc.
If you really need to do it in PL/PgSQL, is there any reason you can't
do all the work in one transaction and commit when the function finishes?
--
Craig Ringer
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2008-04-07 14:22 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2008-04-07 12:46 Commit every processed record luke.78@libero.it <luke.78@libero.it>
2008-04-07 13:01 ` A. Kretschmer <andreas.kretschmer@schollglas.com>
2008-04-07 14:22 ` Bart Degryse <Bart.Degryse@indicator.be>
2008-04-07 13:38 ` Craig Ringer <craig@postnewspapers.com.au>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox