Thread: Newbie questions relating to transactions
Hi, I'm new to pgsql (but not databases in general) and I've run into a roadblock that I'm having difficulty figuring out how to get around. Any help would be greatly appreciated! I'm using the database to store the results of calculations over data in some tables. There are a lot of data records (~13,000), with the calculations done over many (tens of thousands?) different subsets of those records, and results stored for each subset. (If a flag is set, each step of a subset calculation is saved as well, but that flag isn't set in this particular case.) I have written a PL/pgSQL function that performs these calculations by reading the needed data, calculating, and saving the results. When run over a smaller set of data, it works fine. But when I tried to run it over this larger set of data, I got the error message "ERROR: cannot have more than 2^32-1 commands in a transaction". I have looked into trying to control the transaction within my function, but apparently this results in nested transactions, which is not supported by pgsql 8.2 (my current version). I've done some googling, and found discussions mentioning savepoints, but they don't seem applicable. (Most discussions about savepoints are concerned with exceptions and rolling back to a savepoint. My issue is I need a commit in the middle of a transaction so that the transaction doesn't get too big.) If I just put commits within the function, I get either "ERROR: SPI_execute_plan failed executing query "commit": SPI_ERROR_TRANSACTION" (just "commit;") or "ERROR: cannot begin/end transactions in PL/pgSQL" (with "execute 'commit';"). Here are my questions: - Can I execute this logic without transaction control at all? I don't really need it in this case. In fact, I'd prefer if every database change were autocommitted when encountered (so I can watch progress; this takes a long time to run). If something fails, I can just re-run the routine. The only way that I have found (so far) to run the function is with a SELECT statement, which I assume is starting the transaction. - Is there such a thing as a "stored procedure" (as in DB2 or MS SQL) where I can control transactions better than in a function? - The message "HINT: Use a BEGIN block with an EXCEPTION clause instead." was displayed with the last error quoted above. I need to look into this suggestion further, as I was not able to readily find it in the manuals. Will this solve my issue? (I'm more than happy to research what I need to do, if this will work...) - Is there a way to commit a transaction mid way through it? (I know, this kinda defeats the purpose of a transaction, but thought I'd ask antway. :->) - Does version 8.3 support nested transactions, or something else that would resolve this issue. I don't have a problem with upgrading, if necessary. - Might you have some other way to get around this issue, short of changing the function to only process a subset of the calculations at a time? (This wouldn't be terribly difficult to do, but it introduces some "messiness" into the whole thing that I'd rather not introduce.) Thanks for the help, Carl
Carl Sopchak <carl.sopchak@cegis123.com> writes:
> I have written a PL/pgSQL function that performs these calculations by reading
> the needed data, calculating, and saving the results. When run over a
> smaller set of data, it works fine. But when I tried to run it over this
> larger set of data, I got the error message "ERROR: cannot have more than
> 2^32-1 commands in a transaction".
> I have looked into trying to control the transaction within my function, but
> apparently this results in nested transactions, which is not supported by
> pgsql 8.2 (my current version).
Try updating to 8.3 --- it only counts plpgsql statements as separate
"commands" if they actually modified something on-disk. The 2^32 limit
is still there but it's a lot harder to hit.
regards, tom lane
Well, the upgrade to 8.3 seemed to rid me of the command limit, but now I'm running out of memory. I have 2Gb physical and 8Gb swap (after adding 4Gb). Is there a way for me to run this outside of one huge transaction? This really shouldn't be using more than a few hundred megs of RAM (assuming cursor records are all stored in memory)... Thanks for the help, Carl On Saturday, March 07, 2009, Tom Lane wrote: > Carl Sopchak <carl.sopchak@cegis123.com> writes: > > I have written a PL/pgSQL function that performs these calculations by > > reading the needed data, calculating, and saving the results. When run > > over a smaller set of data, it works fine. But when I tried to run it > > over this larger set of data, I got the error message "ERROR: cannot > > have more than 2^32-1 commands in a transaction". > > > > I have looked into trying to control the transaction within my function, > > but apparently this results in nested transactions, which is not > > supported by pgsql 8.2 (my current version). > > Try updating to 8.3 --- it only counts plpgsql statements as separate > "commands" if they actually modified something on-disk. The 2^32 limit > is still there but it's a lot harder to hit. > > regards, tom lane
Carl Sopchak <carl.sopchak@cegis123.com> writes: > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now I'm > running out of memory. I have 2Gb physical and 8Gb swap (after adding 4Gb). What do you mean you're running out of memory? For most part of Postgres that's only a problem if you've configured it to use more memory than your system can handle -- such as setting work_mem or shared_buffers too large. One area that can cause problems is having too many trigger executions queued up. I don't know if that's what you're running into though. > Is there a way for me to run this outside of one huge transaction? This > really shouldn't be using more than a few hundred megs of RAM (assuming > cursor records are all stored in memory)... Personally I find it much more flexible to implement these types of jobs as external scripts connecting as a client. That lets you stop/start transactions freely. It also allows you to open multiple connections or run the client-side code on a separate machine which can have different resources available. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's Slony Replication support!
Carl Sopchak wrote: > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now I'm > running out of memory. I have 2Gb physical and 8Gb swap (after adding 4Gb). Do you have AFTER triggers on the involved tables? They are recorded on memory and we have no mechanism to spill to disk, so it's frequent that those cause out-of-memory. If that's the explanation, your workaround would be to get rid of them. > Is there a way for me to run this outside of one huge transaction? This > really shouldn't be using more than a few hundred megs of RAM (assuming > cursor records are all stored in memory)... Hmm, maybe you're holding too many cursors open and not closing them timely? Did you post your function for review? -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Sunday, March 08, 2009, Gregory Stark wrote: > Carl Sopchak <carl.sopchak@cegis123.com> writes: > > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now > > I'm running out of memory. I have 2Gb physical and 8Gb swap (after > > adding 4Gb). > > What do you mean you're running out of memory? For most part of Postgres > that's only a problem if you've configured it to use more memory than your > system can handle -- such as setting work_mem or shared_buffers too large. "ERROR: Out of Memory" is what I meant when I said I was running out of memory! :-) This is returned by psql, but it is the postmaster process that is hitting the wall. I haven't touched the configuration, so whatever the default in 8.3 is, is what these are set at. I'll look and bring the numbers down if necessary. Thanks for the pointer. > > One area that can cause problems is having too many trigger executions > queued up. I don't know if that's what you're running into though. There are no triggers on any of the tables in the database... > > > Is there a way for me to run this outside of one huge transaction? This > > really shouldn't be using more than a few hundred megs of RAM (assuming > > cursor records are all stored in memory)... > > Personally I find it much more flexible to implement these types of jobs as > external scripts connecting as a client. That lets you stop/start > transactions freely. It also allows you to open multiple connections or run > the client-side code on a separate machine which can have different > resources available. I suppose I could go that route... I was just trying to keep things simple, and all in the database. This was supposed to be a "quick and dirty" way to calculate, store and access these numbers... So much for "quick"... If there were stored procedures as in other databases that I've used, or a way to run a function outside an implicit transaction, then I'd be all set. Guess I'll have to add a layer to the setup. Got any suggestions as to a good, fast, language to code the external script in? Thanks for the help, Carl
Carl Sopchak <carl.sopchak@cegis123.com> writes:
> On Sunday, March 08, 2009, Gregory Stark wrote:
>> What do you mean you're running out of memory?
> "ERROR: Out of Memory" is what I meant when I said I was running out of
> memory! :-) This is returned by psql, but it is the postmaster process that
> is hitting the wall.
Oh? Postgres doesn't spell its out-of-memory errors that way. Please
quote the *exact* message you got, not some approximation.
Also, if this was a server-side out-of-memory problem, there should be a
memory usage dump appearing in the postmaster log, which would help
identify exactly what the problem is.
regards, tom lane
Carl Sopchak wrote on 08.03.2009 17:37: > or a way to run a function outside an implicit transaction No sensible DBMS will let you do _anything_ outside a transaction Thomas
On Sunday, March 08, 2009, Alvaro Herrera wrote:
> Carl Sopchak wrote:
> > Well, the upgrade to 8.3 seemed to rid me of the command limit, but now
> > I'm running out of memory. I have 2Gb physical and 8Gb swap (after
> > adding 4Gb).
>
> Do you have AFTER triggers on the involved tables? They are recorded on
> memory and we have no mechanism to spill to disk, so it's frequent that
> those cause out-of-memory. If that's the explanation, your workaround
> would be to get rid of them.
No triggers have been defined on any tables in the database...
>
> > Is there a way for me to run this outside of one huge transaction? This
> > really shouldn't be using more than a few hundred megs of RAM (assuming
> > cursor records are all stored in memory)...
>
> Hmm, maybe you're holding too many cursors open and not closing them
> timely? Did you post your function for review?
I am only using one cursor, which is opened and closed repeatedly. It pulls
the base data from the database for the calculations.
I have not posted the function for review yet because its function is
proprietary. However, I have stripped out the proprietary stuff, and include
the code below. I marked everything stripped out by placing a brief
description enclosed within {{ and }}. I left all of the places that the
database is accessed in the code. I changed some of the line wrapping to fit
a reasonable width (which I mention in case you see syntax type errors).
There is a few lines that save the calculation details based on a flag in the
trial_header table. This flag is set to N for the run that I am having
issues with, so these records are not being created. I left that code in
below for completeness...
I realize this isn't probably the cleanest code out there (I'm sure using
prepared statements would help speed), but it was really meant to be a "quick
and dirty" way to calculate the data I need. Any comments or suggestions on
improving the code is welcome.
create or replace function Run_Trial (tid integer) returns void as $proc$
declare
{{ declarations }}
begin
-- Set start time...
program_version := '1.16';
update trial_header set start_timestamp = clock_timestamp(),
run_version = program_version, end_timestamp = null
where trial_id = tid;
-- get rid of prior run, if any:
delete from trial_results where trial_id = tid;
delete from trial_calc_detail where trial_id = tid;
-- Get the trial parameters:
select * into trial_hdr from trial_header where trial_id = tid;
{{ Do some calculations }}
-- Create temp table of data. This simplifies the coding below A LOT.
{{ conditional calc }}
-- (I can't figure out how to do this with a dynamic select and
-- "insert into trial_data select" and I get an error
-- if I make the dynamic SQL a "select into temp table trial_data"...)
-- Do it by brute force, I guess...
drop table if exists trial_data;
create temp table trial_data(
{{ fields }}
);
for row in execute
'select {{ select statement }}' loop
execute 'insert into trial_data values(' || {{ fields }} || ')';
end loop;
create index trial_data_idx on trial_data (data_yyyymm, data_date);
create index trial_data_idx2 on trial_data (data_date);
-- Get date range for the data set we're using
for row in execute 'select min(data_date) as min_date,
max(data_date) as max_date from trial_data' loop
low_data_date := row.min_date;
high_data_date := row.max_date;
end loop;
-- Calculate maximum number of years that data covers
max_years = floor((high_data_date - low_data_date) / 365.25);
-- Loop through all possible "x year" periods
for cur_years in 1 .. max_years loop
-- start from the first period on file:
next_iteration_start := low_data_date;
num_periods := trial_hdr.periods_per_year * cur_years
+ trial_hdr.{{ field }};
for row in execute 'select count(*) as cnt from (
select data_date from trial_data where data_date >= ' ||
quote_literal(next_iteration_start) ||
' Limit ' || to_char(num_periods, '9999999999') || ') a' loop
data_periods := row.cnt;
end loop;
-- Do each "x year" period in data
while data_periods = num_periods loop
-- Initialize calculation
-- used to set sucessive values for next_iteration_start:
iteration_counter := 0;
{{ some calculations }}
for row in execute 'select max(data_date) as max_date' ||
' from ( select data_date from trial_data' ||
' where data_date >= ' ||
quote_literal(next_iteration_start) ||
' order by data_date' ||
' Limit ' || to_char(num_periods, '999999999') || ') a' loop
per_end_date := row.max_date;
end loop;
-- Get data for calculation
open data_cursor for execute 'select * from trial_data' ||
' where data_date >= ' || quote_literal(next_iteration_start) ||
' order by data_date ' ||
' Limit ' || to_char(num_periods, '999999999');
loop -- through periods for calculation
fetch data_cursor into data;
if not found then
exit;
end if;
-- determine next iteration start date:
iteration_counter := iteration_counter + 1;
if iteration_counter = 1 then
{{ calculations }}
end if;
if iteration_counter = 2 then
next_iteration_start := data.data_date;
end if;
{{ calculations based on row data }}
-- save details if requested:
if upper(trial_hdr.save_calc_details) = 'Y' then
insert into trial_calc_detail values( {{ fields }} );
end if;
{{ Calculation }}
end loop; -- through periods for calculation
-- Final calculations:
{{ calculations }}
-- save results:
insert into trial_results values( {{ fields }} );
close data_cursor;
for row in execute 'select count(*) as cnt from (
select data_date from trial_data where data_date >= ' ||
quote_literal(next_iteration_start) ||
' Limit ' || to_char(num_periods, '9999999999') || ') a'
loop
data_periods := row.cnt;
end loop;
end loop; -- Do each "x year" period in data
end loop; -- loop through all possible "x year" periods
-- mark finish time on trial:
update trial_header set end_timestamp = clock_timestamp()
where trial_id = tid;
-- clean up:
drop table trial_data;
end;
$proc$ language plpgsql;
On Sunday, March 08, 2009, Tom Lane wrote:
> Carl Sopchak <carl.sopchak@cegis123.com> writes:
> > On Sunday, March 08, 2009, Gregory Stark wrote:
> >> What do you mean you're running out of memory?
> >
> > "ERROR: Out of Memory" is what I meant when I said I was running out of
> > memory! :-) This is returned by psql, but it is the postmaster process
> > that is hitting the wall.
>
> Oh? Postgres doesn't spell its out-of-memory errors that way. Please
> quote the *exact* message you got, not some approximation.
Sorry. I didn't think capitalization would matter. It takes hours for this
thing to run, so I did type the message from memory. Here's a cut and
paste: "ERROR: out of memory" followed by "DETAIL: Failed on request of
size 14." (quotes mine).
>
> Also, if this was a server-side out-of-memory problem, there should be a
> memory usage dump appearing in the postmaster log, which would help
> identify exactly what the problem is.
Here's what's around the error message in the log:
TopMemoryContext: 1007952 total in 14 blocks; 8568 free (10 chunks); 999384
used
Local Buffer Lookup Table: 8192 total in 1 blocks; 744 free (0 chunks); 7448
used
Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392
used
Operator lookup cache: 24576 total in 2 blocks; 14072 free (6 chunks); 10504
used
PL/PgSQL function context: 122880 total in 4 blocks; 21040 free (45 chunks);
101840 used
Operator class cache: 8192 total in 1 blocks; 3848 free (0 chunks); 4344
used
CFuncHash: 8192 total in 1 blocks; 4936 free (0 chunks); 3256 used
Rendezvous variable hash: 8192 total in 1 blocks; 3848 free (0 chunks); 4344
used
PLpgSQL function cache: 24328 total in 2 blocks; 5904 free (0 chunks); 18424
used
TopTransactionContext: 8192 total in 1 blocks; 680 free (0 chunks); 7512
used
ExecutorState: 253952 total in 5 blocks; 111296 free (8 chunks); 142656
used
ExprContext: 8192 total in 1 blocks; 8112 free (0 chunks); 80 used
SPI Exec: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used
SPI Proc: 3154128080 total in 398 blocks; 13664 free (178 chunks);
3154114416 used
MessageContext: 8192 total in 1 blocks; 4616 free (1 chunks); 3576 used
smgr relation table: 8192 total in 1 blocks; 1776 free (0 chunks); 6416 used
TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16
used
Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
PortalMemory: 8192 total in 1 blocks; 7904 free (0 chunks); 288 used
PortalHeapMemory: 7168 total in 3 blocks; 2688 free (0 chunks); 4480 used
ExecutorState: 32832 total in 3 blocks; 15672 free (3 chunks); 17160
used
TIDBitmap: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
TupleSort: 319512 total in 6 blocks; 47720 free (4 chunks); 271792
used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
ExprContext: 0 total in 0 blocks; 0 free (0 chunks); 0 used
PortalHeapMemory: 1024 total in 1 blocks; 880 free (0 chunks); 144 used
ExecutorState: 8192 total in 1 blocks; 5984 free (4 chunks); 2208 used
ExprContext: 8192 total in 1 blocks; 8176 free (7 chunks); 16 used
Relcache by OID: 8192 total in 1 blocks; 2856 free (0 chunks); 5336 used
CacheMemoryContext: 667472 total in 20 blocks; 71416 free (0 chunks); 596056
used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
CachedPlanSource: 3072 total in 2 blocks; 1056 free (0 chunks); 2016 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 3072 total in 2 blocks; 520 free (0 chunks); 2552 used
CachedPlanSource: 1024 total in 1 blocks; 144 free (0 chunks); 880 used
SPI Plan: 1024 total in 1 blocks; 888 free (0 chunks); 136 used
CachedPlan: 1024 total in 1 blocks; 56 free (0 chunks); 968 used
CachedPlanSource: 3072 total in 2 blocks; 1696 free (0 chunks); 1376 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
CachedPlanSource: 3072 total in 2 blocks; 1872 free (2 chunks); 1200 used
SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
CachedPlan: 1024 total in 1 blocks; 232 free (0 chunks); 792 used
CachedPlanSource: 3072 total in 2 blocks; 1872 free (2 chunks); 1200 used
SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 328 free (0 chunks); 696 used
CachedPlanSource: 1024 total in 1 blocks; 24 free (0 chunks); 1000 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
CachedPlanSource: 3072 total in 2 blocks; 1984 free (2 chunks); 1088 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 152 free (0 chunks); 872 used
CachedPlanSource: 3072 total in 2 blocks; 1984 free (2 chunks); 1088 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 352 free (1 chunks); 672 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 232 free (0 chunks); 792 used
CachedPlanSource: 3072 total in 2 blocks; 1872 free (2 chunks); 1200 used
SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
CachedPlan: 1024 total in 1 blocks; 168 free (0 chunks); 856 used
CachedPlanSource: 3072 total in 2 blocks; 2000 free (3 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
CachedPlan: 1024 total in 1 blocks; 488 free (0 chunks); 536 used
CachedPlanSource: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 96 free (0 chunks); 928 used
CachedPlanSource: 3072 total in 2 blocks; 1744 free (0 chunks); 1328 used
SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 376 free (0 chunks); 648 used
CachedPlanSource: 1024 total in 1 blocks; 8 free (0 chunks); 1016 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 576 free (0 chunks); 448 used
CachedPlanSource: 1024 total in 1 blocks; 288 free (0 chunks); 736 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 488 free (0 chunks); 536 used
CachedPlanSource: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 3072 total in 2 blocks; 1608 free (0 chunks); 1464 used
CachedPlanSource: 3072 total in 2 blocks; 952 free (0 chunks); 2120 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 3072 total in 2 blocks; 1216 free (0 chunks); 1856 used
CachedPlanSource: 3072 total in 2 blocks; 560 free (0 chunks); 2512 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 352 free (1 chunks); 672 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 616 free (0 chunks); 408 used
CachedPlanSource: 1024 total in 1 blocks; 312 free (1 chunks); 712 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 352 free (1 chunks); 672 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 352 free (1 chunks); 672 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 352 free (1 chunks); 672 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 200 free (0 chunks); 824 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 3072 total in 2 blocks; 1712 free (0 chunks); 1360 used
CachedPlanSource: 3072 total in 2 blocks; 1056 free (0 chunks); 2016 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 368 free (0 chunks); 656 used
CachedPlanSource: 1024 total in 1 blocks; 64 free (0 chunks); 960 used
SPI Plan: 1024 total in 1 blocks; 904 free (0 chunks); 120 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 352 free (1 chunks); 672 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 192 free (0 chunks); 832 used
CachedPlanSource: 3072 total in 2 blocks; 1896 free (3 chunks); 1176 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 640 free (0 chunks); 384 used
CachedPlanSource: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 504 free (0 chunks); 520 used
CachedPlanSource: 1024 total in 1 blocks; 104 free (1 chunks); 920 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
trial_data_idx2: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
CachedPlan: 1024 total in 1 blocks; 648 free (0 chunks); 376 used
CachedPlanSource: 1024 total in 1 blocks; 528 free (0 chunks); 496 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
trial_data_idx: 1024 total in 1 blocks; 280 free (0 chunks); 744 used
CachedPlan: 1024 total in 1 blocks; 568 free (0 chunks); 456 used
CachedPlanSource: 1024 total in 1 blocks; 384 free (0 chunks); 640 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 7168 total in 3 blocks; 4008 free (0 chunks); 3160 used
CachedPlanSource: 7168 total in 3 blocks; 3024 free (0 chunks); 4144 used
SPI Plan: 1024 total in 1 blocks; 888 free (0 chunks); 136 used
xxx_data_idx: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
xxx_data_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
yyyy_weekly_series_idx: 1024 total in 1 blocks; 280 free (0 chunks); 744
used
yyyy_weekly_series_pkey: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
CachedPlan: 3072 total in 2 blocks; 1320 free (0 chunks); 1752 used
CachedPlanSource: 3072 total in 2 blocks; 896 free (0 chunks); 2176 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
pg_shdepend_depender_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_shdepend_reference_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_depend_depender_index: 1024 total in 1 blocks; 152 free (0 chunks); 872
used
pg_depend_reference_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
CachedPlan: 3072 total in 2 blocks; 776 free (0 chunks); 2296 used
CachedPlanSource: 3072 total in 2 blocks; 464 free (0 chunks); 2608 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 768 free (0 chunks); 256 used
CachedPlanSource: 1024 total in 1 blocks; 680 free (0 chunks); 344 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 600 free (0 chunks); 424 used
CachedPlanSource: 1024 total in 1 blocks; 264 free (0 chunks); 760 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 488 free (0 chunks); 536 used
CachedPlanSource: 1024 total in 1 blocks; 176 free (0 chunks); 848 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 264 free (0 chunks); 760 used
CachedPlanSource: 1024 total in 1 blocks; 32 free (0 chunks); 992 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 264 free (0 chunks); 760 used
CachedPlanSource: 1024 total in 1 blocks; 32 free (0 chunks); 992 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 1024 total in 1 blocks; 208 free (0 chunks); 816 used
CachedPlanSource: 1024 total in 1 blocks; 64 free (0 chunks); 960 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 7168 total in 3 blocks; 1336 free (0 chunks); 5832 used
CachedPlanSource: 9216 total in 2 blocks; 4216 free (1 chunks); 5000 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 3072 total in 2 blocks; 1640 free (0 chunks); 1432 used
CachedPlanSource: 1024 total in 1 blocks; 440 free (0 chunks); 584 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
trial_calc_detail_idx: 1024 total in 1 blocks; 128 free (0 chunks); 896
used
CachedPlan: 3072 total in 2 blocks; 1752 free (0 chunks); 1320 used
CachedPlanSource: 1024 total in 1 blocks; 456 free (0 chunks); 568 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
CachedPlan: 7168 total in 3 blocks; 912 free (0 chunks); 6256 used
CachedPlanSource: 3072 total in 2 blocks; 2000 free (2 chunks); 1072 used
SPI Plan: 1024 total in 1 blocks; 912 free (0 chunks); 112 used
trial_header_pkey: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_index_indrelid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
CachedPlan: 1024 total in 1 blocks; 624 free (0 chunks); 400 used
CachedPlanSource: 1024 total in 1 blocks; 336 free (0 chunks); 688 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
CachedPlan: 1024 total in 1 blocks; 648 free (0 chunks); 376 used
CachedPlanSource: 1024 total in 1 blocks; 168 free (0 chunks); 856 used
SPI Plan: 1024 total in 1 blocks; 928 free (0 chunks); 96 used
pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_toast_1255_index: 1024 total in 1 blocks; 240 free (0 chunks); 784 used
pg_ts_dict_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_aggregate_fnoid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_language_name_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_statistic_relid_att_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_ts_dict_dictname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_namespace_nspname_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_opfamily_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_opclass_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_ts_parser_prsname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_amop_fam_strat_index: 1024 total in 1 blocks; 88 free (0 chunks); 936
used
pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 152 free (0 chunks);
872 used
pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_cast_source_target_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_auth_members_role_member_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_ts_config_cfgname_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_authid_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_ts_config_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_conversion_default_index: 1024 total in 1 blocks; 128 free (0 chunks);
896 used
pg_language_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_enum_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680 used
pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 152 free (0
chunks); 872 used
pg_ts_parser_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_database_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_conversion_name_nsp_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_class_relname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 240 free (0
chunks); 784 used
pg_class_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_amproc_fam_proc_index: 1024 total in 1 blocks; 88 free (0 chunks); 936
used
pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 88 free (0
chunks); 936 used
pg_index_indexrelid_index: 1024 total in 1 blocks; 304 free (0 chunks);
720 used
pg_type_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_authid_rolname_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_auth_members_member_role_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_enum_typid_label_index: 1024 total in 1 blocks; 280 free (0 chunks);
744 used
pg_constraint_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_conversion_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
pg_ts_template_tmplname_index: 1024 total in 1 blocks; 280 free (0
chunks); 744 used
pg_ts_config_map_index: 1024 total in 1 blocks; 192 free (0 chunks); 832
used
pg_namespace_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_type_typname_nsp_index: 1024 total in 1 blocks; 240 free (0 chunks);
784 used
pg_operator_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720
used
pg_amop_opr_fam_index: 1024 total in 1 blocks; 240 free (0 chunks); 784
used
pg_proc_oid_index: 1024 total in 1 blocks; 304 free (0 chunks); 720 used
pg_opfamily_am_name_nsp_index: 1024 total in 1 blocks; 192 free (0
chunks); 832 used
pg_ts_template_oid_index: 1024 total in 1 blocks; 344 free (0 chunks); 680
used
MdSmgr: 8192 total in 1 blocks; 6592 free (0 chunks); 1600 used
LOCALLOCK hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used
Timezones: 48616 total in 2 blocks; 5968 free (0 chunks); 42648 used
ErrorContext: 8192 total in 1 blocks; 8176 free (5 chunks); 16 used
ERROR: out of memory
DETAIL: Failed on request of size 14.
>
> regards, tom lane
Carl Sopchak <carl.sopchak@cegis123.com> writes:
> Here's what's around the error message in the log:
> SPI Proc: 3154128080 total in 398 blocks; 13664 free (178 chunks);
> 3154114416 used
Hmm, so apparently some internal leak within the plpgsql engine. I'd be
willing to look into this if you can provide a self-contained test case.
(I don't wish to spend time trying to reverse engineer suitable tables
and data from the fragmentary function you posted, even assuming that it
would show the leak ...)
regards, tom lane
On Sunday, March 08, 2009, Tom Lane wrote: > Carl Sopchak <carl.sopchak@cegis123.com> writes: > > Here's what's around the error message in the log: > > > > SPI Proc: 3154128080 total in 398 blocks; 13664 free (178 chunks); > > 3154114416 used > > Hmm, so apparently some internal leak within the plpgsql engine. I'd be > willing to look into this if you can provide a self-contained test case. > (I don't wish to spend time trying to reverse engineer suitable tables > and data from the fragmentary function you posted, even assuming that it > would show the leak ...) > > regards, tom lane Yeah, those numbers looked pretty big to me, too... (Even though I didn't really know what they mean...) I'll try to put together a test case, but don't hold your breath. I'm kinda under a deadline on the project I'm working on, and this is a side track at best. There are other ways for me to work around this (client side script or changing the function to do partial runs at a time) that will get me to where I need to be. On the other hand, I like to help solve these types of things, so I should get around to it eventually... Thanks for your help. Carl