pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedStored procedure array limits
4+ messages / 2 participants
[nested] [flat]
* Stored procedure array limits
@ 2006-09-28 22:11 Paul B. Anderson <paul.a@pnlassociates.com>
2006-09-29 03:57 ` Re: Stored procedure array limits Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 4+ messages in thread
From: Paul B. Anderson @ 2006-09-28 22:11 UTC (permalink / raw)
To: pgsql-admin
I have a large stored procedure with 16 arrays, mostly varchar, each
with dimension 3000. The procedure works fine on a small number of rows
used from these arrays but gets the following error for large rowsets:
ERROR: invalid array subscripts
CONTEXT: PL/pgSQL function "name_search" line 64 at assignment
I have not exceeded the size of the arrays, index-wise.
I increased max_stack_depth to 32768 and work_mem to 32768 but without
effect.
If I comment out two particular arrays (that can have null values), it
also runs OK.
Anybody have any ideas?
Paul
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Stored procedure array limits
2006-09-28 22:11 Stored procedure array limits Paul B. Anderson <paul.a@pnlassociates.com>
@ 2006-09-29 03:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 15:00 ` Re: Stored procedure array limits Paul B. Anderson <paul.a@pnlassociates.com>
0 siblings, 1 reply; 4+ messages in thread
From: Tom Lane @ 2006-09-29 03:57 UTC (permalink / raw)
To: Paul B. Anderson <paul.a@pnlassociates.com>; +Cc: pgsql-admin
"Paul B. Anderson" <paul.a@pnlassociates.com> writes:
> I have a large stored procedure with 16 arrays, mostly varchar, each
> with dimension 3000. The procedure works fine on a small number of rows
> used from these arrays but gets the following error for large rowsets:
> ERROR: invalid array subscripts
Right offhand I only see that error being thrown for out-of-range array
subscripts. Are you maybe trying to fill the arrays in nonsequential
order?
> If I comment out two particular arrays (that can have null values), it
> also runs OK.
As already noted, we don't support nulls in arrays before 8.2, but
I'm not exactly sure how that omission leads to this error message.
regards, tom lane
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Stored procedure array limits
2006-09-28 22:11 Stored procedure array limits Paul B. Anderson <paul.a@pnlassociates.com>
2006-09-29 03:57 ` Re: Stored procedure array limits Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-29 15:00 ` Paul B. Anderson <paul.a@pnlassociates.com>
2006-09-29 15:12 ` Re: Stored procedure array limits Paul B. Anderson <paul.a@pnlassociates.com>
0 siblings, 1 reply; 4+ messages in thread
From: Paul B. Anderson @ 2006-09-29 15:00 UTC (permalink / raw)
To: pgsql-admin
I've checked my code and it does not seem to run past any limits. I am
filling the arrays in sequential order, extending each one in parallel
with the others.
The null problem fixed the code for some cases but I'm still having
problems in general and with the same error message but reported in
another place.
Paul
Tom Lane wrote:
> "Paul B. Anderson" <paul.a@pnlassociates.com> writes:
>
>> I have a large stored procedure with 16 arrays, mostly varchar, each
>> with dimension 3000. The procedure works fine on a small number of rows
>> used from these arrays but gets the following error for large rowsets:
>>
>
>
>> ERROR: invalid array subscripts
>>
>
> Right offhand I only see that error being thrown for out-of-range array
> subscripts. Are you maybe trying to fill the arrays in nonsequential
> order?
>
>
>> If I comment out two particular arrays (that can have null values), it
>> also runs OK.
>>
>
> As already noted, we don't support nulls in arrays before 8.2, but
> I'm not exactly sure how that omission leads to this error message.
>
> regards, tom lane
>
> .
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Stored procedure array limits
2006-09-28 22:11 Stored procedure array limits Paul B. Anderson <paul.a@pnlassociates.com>
2006-09-29 03:57 ` Re: Stored procedure array limits Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 15:00 ` Re: Stored procedure array limits Paul B. Anderson <paul.a@pnlassociates.com>
@ 2006-09-29 15:12 ` Paul B. Anderson <paul.a@pnlassociates.com>
0 siblings, 0 replies; 4+ messages in thread
From: Paul B. Anderson @ 2006-09-29 15:12 UTC (permalink / raw)
To: pgsql-admin
Actually, I was not filling all of the arrays in sequential order. I
added code to initialize them in order and the function seems to be
working now. Is that a known problem?
Paul
Paul B. Anderson wrote:
> I've checked my code and it does not seem to run past any limits. I
> am filling the arrays in sequential order, extending each one in
> parallel with the others.
>
> The null problem fixed the code for some cases but I'm still having
> problems in general and with the same error message but reported in
> another place.
>
> Paul
>
>
> Tom Lane wrote:
>> "Paul B. Anderson" <paul.a@pnlassociates.com> writes:
>>
>>> I have a large stored procedure with 16 arrays, mostly varchar, each
>>> with dimension 3000. The procedure works fine on a small number of rows
>>> used from these arrays but gets the following error for large rowsets:
>>>
>>
>>
>>> ERROR: invalid array subscripts
>>>
>>
>> Right offhand I only see that error being thrown for out-of-range array
>> subscripts. Are you maybe trying to fill the arrays in nonsequential
>> order?
>>
>>
>>> If I comment out two particular arrays (that can have null values), it
>>> also runs OK.
>>>
>>
>> As already noted, we don't support nulls in arrays before 8.2, but
>> I'm not exactly sure how that omission leads to this error message.
>>
>> regards, tom lane
>>
>> .
>>
>>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-09-29 15:12 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-28 22:11 Stored procedure array limits Paul B. Anderson <paul.a@pnlassociates.com>
2006-09-29 03:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 15:00 ` Paul B. Anderson <paul.a@pnlassociates.com>
2006-09-29 15:12 ` Paul B. Anderson <paul.a@pnlassociates.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox