Re: pgsql functions and transactions?

Thank you for the great information!

I'm using 10 tables and am creating and dropping one temporary table about 500 times.
I can send you the code or run tests if you would like. You seem curious as to why this happenned. As am I.

Also, I'm not using JDBC, I am running the stored procs from the psql command line.

Best Regards,
Betsy Barker

On Fri, 27 Aug 2004 15:34:13 -0400
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Betsy Barker <betsy(dot)barker(at)supportservicesinc(dot)com> writes:
> > I'm not touching a lot of different tables per se, but I have about 10
> > functions that each process one or more cursor that all combined end
> > up creating about 45,000 records. The functions cascade.
>
> That doesn't in itself seem like it would require locking a lot of
> different tables.
>
> > And like I said, I get the error on my development box with 512 M of
> > RAM. Production has 3 G of RAM.
>
> Available RAM has nothing to do with this --- you are overflowing the
> lock table in PG shared memory, which is sized according to
> max_locks_per_transaction (times max_connections). So kicking up that
> parameter should fix it. I'm just curious as to why you're overflowing
> the default setting --- we don't see that happen all that often.

>
> > Can I ask you what you mean by "are you touching a whole lot of
> > different tables in one transaction? " Do I have a transaction?
>
> Yes, you do --- if you're using JDBC then the driver's autocommit
> setting determines how long the transaction lasts, but in any case
> it will last at least as long as one SQL statement sent to the backend.
> So a pile of nested functions will necessarily all execute in one
> transaction. If that whole process involves accessing more than a
> few hundred tables, you'll need to do something with
> max_locks_per_transaction.
>
> But if you're only accessing a few tables (say tens) then there's
> something else going on here.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Betsy Barker
IT Manager
Support Services, Inc
(720)489-1630 X 38