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  wrote:

> Betsy Barker  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