Postgres Pro
Downloads
Home   >   mailing lists

Thread: execute transaction / insert rows

execute transaction / insert rows

From
"Johnson, Shaunn"
Date:

Howdy:

Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7-10.

Are there any examples out there to insert / update
a table for every, say, 1000 records via SQL? I'm having
a hard time just inserting into the table ... it seems
that there isn't anything going on AND I need to commit
the transaction if the query gets killed somehow.

Thanks!

-X

Re: execute transaction / insert rows

From
"Johnson, Shaunn"
Date:

--thanks for the reply:

--i'm sorry i was not clear with my goals, but
--that is exactly what i'm trying to do - however,
--i was trying to figure out if there was a way to
--do this with SQL.

--like

[pseudo code]

begin
        update table
        set col = new
        from old_table
        where new = better
set transaction = 1000 records (* no clue on this part! *)
commit 1000;
end

[/code]

--any suggestions?

-X

-----Original Message-----
From: Andrew Sullivan [mailto:andrew@libertyrms.info]

On Tue, Oct 01, 2002 at 01:48:38PM -0400, Johnson, Shaunn wrote:
> Howdy:
>
> Running PostgreSQL 7.2.1 on RedHat Linux 7.2 kernel 2.4.7-10.
>
> Are there any examples out there to insert / update
> a table for every, say, 1000 records via SQL? I'm having
> a hard time just inserting into the table ... it seems
> that there isn't anything going on AND I need to commit
> the transaction if the query gets killed somehow.

I'm not sure I understand your question, but if I'm right, I've done
this with Perl.  Just loop through a counter to 1000, commit, do
another 1000, &c.  You can add a second counter for the remainder
(i.e. not divisible by 1000) bit, so you can add just that many the
last time.  You can also check the error status after each 1000, and
abort if you run into problems.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: execute transaction / insert rows

From
Andrew Sullivan
Date:
On Tue, Oct 01, 2002 at 02:46:54PM -0400, Johnson, Shaunn wrote:
> [pseudo code]
>
> begin
>     update table
>     set col = new
>     from old_table
>     where new = better
> set transaction = 1000 records (* no clue on this part! *)
> commit 1000;
> end
>
> [/code]
>
> --any suggestions?

Yes, but only the one I already made: use some kind of external
interface.  You can't do this directly in SQL in any reasonably-easy
way I can think of.  The problem is more or less that SQL is not
intended to be used for the sort of process control you're thinking
of.

A

--
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110