Обсуждение: pgsql functions and transactions?

I read the posts on pgsql functions and transactions from the Novice list, and I think they are saying "PGSQL functions
donot support transactions". Is this correct? Or is the idea that functions are automatically in a transaction? 

My functions are processing a lot of data, and I'm getting the following error after 2 hours of processing on my
developmentbox: 

WARNING:  ShmemAlloc: out of memory
WARNING:  Error occurred while executing PL/pgSQL function get_facility_percentiles
WARNING:  line 37 at execute statement
ERROR:  LockAcquire: lock table 1 is out of memory

I thought if I put begin transaction/commit transaction around pieces of the functions, the database could let go of
somelocks and I could process all the way through, as well as preserve some of the results. As it is now, I believe all
theresults are rolledback after the memory error. However, I cannot seem to get the begin transaction/commit in the
correctplaces. 

Any insight and/or assistance with how to solve this memory problem/rollback issue would be greatly appreciated.

Best Regards,

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

Can anyone recommend a solid resource book for learning/using php in
conjunction with postgresql.

Thank you for your input.

Mike

Betsy Barker <betsy.barker@supportservicesinc.com> writes:
> WARNING:  ShmemAlloc: out of memory
> WARNING:  Error occurred while executing PL/pgSQL function get_facility_percentiles
> WARNING:  line 37 at execute statement
> ERROR:  LockAcquire: lock table 1 is out of memory

Hmm, are you touching a whole lot of different tables in one
transaction?  If so you may need to raise the max_locks_per_transaction
parameter.

If that doesn't help, we need more details about what you're doing.

            regards, tom lane

Betsy,

> I read the posts on pgsql functions and transactions from the Novice list,
> and I think they are saying "PGSQL functions do not support transactions".
> Is this correct? Or is the idea that functions are automatically in a
> transaction?

That's right.   Soon (8.0 or 8.1) functions will support *sub-transactions*,
or savepoints, but that still won't do a checkpoint and synch, which is what
you need.

In my experience, there is a limit to the amount of processing you can
reasonably do in a single function in Postgres because of the need to stop
and synch (and possibly VACUUM).    I often have "series" of functions (in
one case, about 18) which are executed in succession by a Perl script.

We've been discussing PROCEDURES on -hackers which are non-transactional (and
thus can contain several transactions).  But nobody is coding this yet.

--
Josh Berkus
Aglio Database Solutions
San Francisco

I'm not touching a lot of different tables per se, but I have about 10 functions that each process one or more cursor
thatall combined end up creating about 45,000 records. The functions cascade. In otherwords the first function gets the
associations,then for each association a function gets all the facilities, then for each facility I do one set of
calculations,then for each of those calculations I do another set of calculations. That continues for about 500
facilities.Then I go back through and combine facilities into different groupings and again do all the calculations.
So,there is a lot of processing going on. 

And like I said, I get the error on my development box with 512 M of RAM. Production has 3 G of RAM. Maybe I won't run
intothis issue on production, but I'm trying to solve it on development so that I don't have to worry about it in
production.Note: I'm trying to replace some functionality that is currently running in J2EE java objects and takes 12
hourswith this set of stored procedure functions, and this is extremely important as I'm sure you understand. 

Tom,
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?Where is it? Does it start when I am at the top of the first function and end when I complete that main
function?Or do I have separate transactions at the beginning and end of each subfunction? 

Thank you,

Betsy Barker

On Thu, 26 Aug 2004 23:09:12 -0400
Tom Lane <tgl@sss.pgh.pa.us> wrote:

> Betsy Barker <betsy.barker@supportservicesinc.com> writes:
> > WARNING:  ShmemAlloc: out of memory
> > WARNING:  Error occurred while executing PL/pgSQL function get_facility_percentiles
> > WARNING:  line 37 at execute statement
> > ERROR:  LockAcquire: lock table 1 is out of memory
>
> Hmm, are you touching a whole lot of different tables in one
> transaction?  If so you may need to raise the max_locks_per_transaction
> parameter.
>
> If that doesn't help, we need more details about what you're doing.
>
>             regards, tom lane
>


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

Betsy,

> 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? Where is it? Does it
> start when I am at the top of the first function and end when I complete
> that main function? Or do I have separate transactions at the beginning and
> end of each subfunction?

The whole thing ... the entire function chain ... is one big transaction.

--
Josh Berkus
Aglio Database Solutions
San Francisco

Betsy Barker <betsy.barker@supportservicesinc.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

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@sss.pgh.pa.us> wrote:

> Betsy Barker <betsy.barker@supportservicesinc.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

Can anyone recommend a solid resource book for learning/using php in
conjunction with postgresql.

Thank you for your input.

Mike

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Hi Mr. Engineer,

I've found some very good php books floating around on Shareaza.  I'm
starting with Sam's Teach yourself PHP in 24 hours.  I know many folks
don't like these books, but for me they are a good way to start --
real basic, real simple.  :-)

Mike


----- Original Message -----
From: operationsengineer1@yahoo.com <operationsengineer1@yahoo.com>
Date: Sat, 28 Aug 2004 13:27:34 -0700 (PDT)
Subject: Re: [NOVICE] pgsql functions and transactions?
To: Mike <1100100@gmail.com>, pgsql-novice@postgresql.org


Mike,

I'm trying to do just this.  I'm not a Linux guru and, frankly, can
hardly qualify for Linux Schmoe...

So, this has been tough for me.

My toolset consists of...

1. This mailing list - lots of generous and folks here with lots of
knowledge and experience.
2. Beginning Databases with PostgreSQL by Wrox (this may not even be
available.  if it is, contact apress.com for more information since
they bought the rights to this title).  The problem here is that it is
somewhat outdated and this is the kiss of death when it comes to
installation - very little help was provided, especially for WinXP.
They have one chapter on interacting with PGSQL via PHP.  Not much,
but something.  Even so, there is a lot of good PGSQL information once
you get past installation.
3. PHP4 Databases by Wrox - This is mostly for learning PHP database
concepts with limited direct PGSQL information.
4. Beginning PHP Programming by Wrox - Probably not necessary if you
are already a PHP pro.
5. Wrox's SQL book - not necessary if no SQL.
6. Tutorials and newsgroups on the net.  Lot of valuable information
out there - although it is often confusing and, believe it or not, I
couldn't find one tutorial in hours of looking that, faollowed step by
step, actually installed and configured PGSQL on WinXP.  One got
close, but no cigar.
7. Last, but not least, check http://www.postgresql.org/ .

How will all this work?  I don't know yet.  I'm in the beginning
stages, but I hope to learn a lot.

PS - I'm unaware of any PostgreSQL focused PHP books - and I've
searched.  Almost all are MySQL related.




Mike <1100100@gmail.com> wrote:
Can anyone recommend a solid resource book for learning/using php in
conjunction with postgresql.

Thank you for your input.

Mike

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend


 ________________________________
Do you Yahoo!?
 Take Yahoo! Mail with you! Get it on your mobile phone.

Mike,

> 2. Beginning Databases with PostgreSQL by Wrox
> (this may not even be available.  if it is, contact apress.com for more
> information since they bought the rights to this title).

I reviewed this book for Wrox Press, shortly before they went out of business.
It was not a favorable review; the authors are primarily MS SQL Server
engineers and cross-over some information which is not accurate.   Also, many
of the technical examples in the book contain typos and mistakes.  So I do
NOT recommend this book currently.   Buy Kerry Douglas' book instead.
http://www.powells.com/cgi-bin/biblio?inkey=4-0735712573-2
Somewhat outdated (versionn 7.2) but everything in it is accurate.

> Can anyone recommend a solid resource book for learning/using php in
> conjunction with postgresql.

Ewald has such a book:
http://www.powells.com/cgi-bin/biblio?inkey=4-0672323826-3

--
--Josh

Josh Berkus
Aglio Database Solutions
San Francisco

Hi Josh,

Thanks for your response.
I'm taking your advice.
I have sampled the Douglas book and liked what I saw, so I just ordered it.
I'm thinking I should see if I'm even able to learn how to build and
use a database, then if I'm successful with postgresql, I'll worry
about building a web interface for it.

Thanks again for the input and guidance.

Mike

On Sat, 28 Aug 2004 15:00:52 -0700, Josh Berkus <josh@agliodbs.com> wrote:
> Mike,
>
> > 2. Beginning Databases with PostgreSQL by Wrox
> > (this may not even be available.  if it is, contact apress.com for more
> > information since they bought the rights to this title).
>
> I reviewed this book for Wrox Press, shortly before they went out of business.
> It was not a favorable review; the authors are primarily MS SQL Server
> engineers and cross-over some information which is not accurate.   Also, many
> of the technical examples in the book contain typos and mistakes.  So I do
> NOT recommend this book currently.   Buy Kerry Douglas' book instead.
> http://www.powells.com/cgi-bin/biblio?inkey=4-0735712573-2
> Somewhat outdated (versionn 7.2) but everything in it is accurate.
>
> > Can anyone recommend a solid resource book for learning/using php in
> > conjunction with postgresql.
>
> Ewald has such a book:
> http://www.powells.com/cgi-bin/biblio?inkey=4-0672323826-3
>
> --
> --Josh
>
>
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

Hi Josh,

Thanks for your response.
I'm taking your advice.
I have sampled the Douglas book and liked what I saw, so I just ordered it.
I'm thinking I should see if I'm even able to learn how to build and
use a database, then if I'm successful with postgresql, I'll worry
about building a web interface for it.

Thanks again for the input and guidance.

Mike

On Sat, 28 Aug 2004 15:00:52 -0700, Josh Berkus wrote:
> Mike,
>
> > 2. Beginning Databases with PostgreSQL by Wrox
> > (this may not even be available. if it is, contact apress.com for more
> > information since they bought the rights to this title).
>
> I reviewed this book for Wrox Press, shortly before they went out of business.
> It was not a favorable review; the authors are primarily MS SQL Server
& gt; engineers and cross-over some information which is not accurate. Also, many
> of the technical examples in the book contain typos and mistakes. So I do
> NOT recommend this book currently. Buy Kerry Douglas' book instead.
> http://www.powells.com/cgi-bin/biblio?inkey=4-0735712573-2
> Somewhat outdated (versionn 7.2) but everything in it is accurate.
>
> > Can anyone recommend a solid resource book for learning/using php in
> > conjunction with postgresql.
>
> Ewald has such a book:
> http://www.powells.com/cgi-bin/biblio?inkey=4-0672323826-3
>
> --
> --Josh
>
>
>
> Josh Berkus
> Aglio Database Solutions
> San Francisco
>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend