pgsql functions and transactions?

#1

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

#2

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?

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

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 some locks and I could process all the way through, as well as preserve some of the results. As it is now, I believe all the results are rolledback after the memory error. However, I cannot seem to get the begin transaction/commit in the correct places.

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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo (AT) postgresql (DOT) org

#3

Betsy Barker <betsy.barker (AT) supportservicesinc (DOT) com> writes:

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

#4

Betsy,

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

#5

Betsy,

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

#6

Betsy Barker <betsy.barker (AT) supportservicesinc (DOT) com> writes:

Quote:

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.

Quote:

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.

Quote:

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

#7

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. 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 into this 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 hours with 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 (AT) sss (DOT) pgh.pa.us> wrote:

Quote:

Betsy Barker <betsy.barker (AT) supportservicesinc (DOT) 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

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo (AT) postgresql (DOT) org)

#8

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

Quote:

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

Quote:

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

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

#9

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 (AT) gmail (DOT) 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.

#10

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 (AT) yahoo (DOT) com <operationsengineer1 (AT) yahoo (DOT) com>
Date: Sat, 28 Aug 2004 13:27:34 -0700 (PDT)
Subject: Re: [NOVICE] pgsql functions and transactions?
To: Mike <1100100 (AT) gmail (DOT) com>, pgsql-novice (AT) postgresql (DOT) 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 (AT) gmail (DOT) 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.

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