Stored procedure array limits

"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

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:

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:

[ expanding this thread, as it now needs wider discussion ]

"Paul B. Anderson" <paul.a@pnlassociates.com> writes:

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?

Well, it's a documented behavior: section 8.10.4 saith

A stored array value can be enlarged by assigning to an element
adjacent to those already present, or by assigning to a slice
that is adjacent to or overlaps the data already present.

Up to 8.2 we didn't have a lot of choice about this, because without any
ability to have nulls embedded in arrays, there wasn't any sane thing to
do with the intermediate positions if you assigned to an element not
adjacent to the existing range. As of 8.2 we could allow assignment to
arbitrary positions by filling the intermediate positions with nulls.
The code hasn't actually been changed to allow that, but it's something
we could consider doing now.

Comments?

regards, tom lane

On Sep 29, 2006, at 9:14 AM, Tom Lane wrote:

[ expanding this thread, as it now needs wider discussion ]

"Paul B. Anderson" <paul.a@pnlassociates.com> writes:

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?

Well, it's a documented behavior: section 8.10.4 saith

A stored array value can be enlarged by assigning to an element
adjacent to those already present, or by assigning to a slice
that is adjacent to or overlaps the data already present.

Up to 8.2 we didn't have a lot of choice about this, because
without any
ability to have nulls embedded in arrays, there wasn't any sane
thing to
do with the intermediate positions if you assigned to an element not
adjacent to the existing range. As of 8.2 we could allow
assignment to
arbitrary positions by filling the intermediate positions with nulls.
The code hasn't actually been changed to allow that, but it's
something
we could consider doing now.

Comments?

At first blush, this strikes me as a bit too magical/implicit. Are
there other languages where sequences behave similarly? The best
analogy that comes to mind is sparse files, but in that case there is
an implicit contract that the intervening empty regions do not
actually occupy physical space, doesn't sound like that's true here.

I think the result of this change would be more difficult debugging
of off-by-one errors and their ilk, rather than actually being a real
benefit.

OTOH, perhaps there is a real use-case I am missing here. I don't see
the rest of this thread on GENERAL and I couldn't find it searching
the archives, where did it come from?

-Casey

As of 8.2 we could allow assignment to
arbitrary positions by filling the intermediate positions with nulls.
The code hasn't actually been changed to allow that, but it's
something
we could consider doing now.

At first blush, this strikes me as a bit too magical/implicit. Are
there other languages where sequences behave similarly?

perl -e '@A = (1, 2, 3); print "@A\n"; $A[10] = 10; print "@A\n";'

1 2 3
1 2 3 10

- John D. Burger
MITRE

It seems that the suggestion to fill intermediate positions with NULLs
would be preferable to the current behavior.

I know of no requirement to populate arrays in sequence in any other
language so I think other programmers would be surprised too by the
current behavior.

Paul

Tom Lane wrote:

Yep, that definitely threw me the first time I encountered it.

Paul B. Anderson wrote:

It seems that the suggestion to fill intermediate positions with NULLs
would be preferable to the current behavior.

I know of no requirement to populate arrays in sequence in any other
language so I think other programmers would be surprised too by the
current behavior.

Paul

Tom Lane wrote:

[ expanding this thread, as it now needs wider discussion ]

"Paul B. Anderson" <paul.a@pnlassociates.com> writes:

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?

Well, it's a documented behavior: section 8.10.4 saith

A stored array value can be enlarged by assigning to an element
adjacent to those already present, or by assigning to a slice
that is adjacent to or overlaps the data already present.

Up to 8.2 we didn't have a lot of choice about this, because without any
ability to have nulls embedded in arrays, there wasn't any sane thing to
do with the intermediate positions if you assigned to an element not
adjacent to the existing range. As of 8.2 we could allow assignment to
arbitrary positions by filling the intermediate positions with nulls.
The code hasn't actually been changed to allow that, but it's something
we could consider doing now.

Comments?

regards, tom lane

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

.

--
erik jones <erik@myemma.com>
software development
emma(r)

"John D. Burger" <john@mitre.org> writes:

As of 8.2 we could allow assignment to
arbitrary positions by filling the intermediate positions with nulls.
The code hasn't actually been changed to allow that, but it's
something we could consider doing now.

At first blush, this strikes me as a bit too magical/implicit. Are
there other languages where sequences behave similarly?

perl -e '@A = (1, 2, 3); print "@A\n"; $A[10] = 10; print "@A\n";'

1 2 3
1 2 3 10

Actually, now that I look closely, I think the SQL spec demands exactly
this. Recall that SQL99 only allows one-dimensional, lower-bound-one
arrays. The specification for UPDATE ... SET C[I] = SV ... reads

Case:

i) If the value of C is null, then an exception condition is
raised: data exception - null value in array target.

ii) Otherwise:

1) Let N be the maximum cardinality of C.

2) Let M be the cardinality of the value of C.

3) Let I be the value of the <simple value specification>
immediately contained in <update target>.

4) Let EDT be the element type of C.

5) Case:

A) If I is greater than zero and less than or equal to
M, then the value of C is replaced by an array A
with element type EDT and cardinality M derived as
follows:

I) For j varying from 1 (one) to I-1 and from I+1 to
M, the j-th element in A is the value of the j-th
element in C.

II) The I-th element of A is set to the specified
update value, denoted by SV, by applying the
General Rules of Subclause 9.2, "Store assignment",
to the I-th element of A and SV as TARGET and
VALUE, respectively.

B) If I is greater than M and less than or equal to
N, then the value of C is replaced by an array A
with element type EDT and cardinality I derived as
follows:

I) For j varying from 1 (one) to M, the j-th element
in A is the value of the j-th element in C.

II) For j varying from M+1 to I-1, the j-th element in
A is the null value.

III) The I-th element of A is set to the specified
update value, denoted by SV, by applying the
General Rules of Subclause 9.2, "Store assignment",
to the I-th element of A and SV as TARGET and
VALUE, respectively.

C) Otherwise, an exception condition is raised: data
exception - array element error.

We currently violate case i by allowing the null array value to be
replaced by a single-element array. I'm disinclined to change that,
as I think our behavior is more useful than the spec's. But case ii.5.B
pretty clearly describes null-fill, so I think we'd better do that, now
that we can.

regards, tom lane

Ok, just so I can be sure I understand what I just read: i. says that
you can assign to an array that has not been initialized. ii. states
that the index of an insertion into an array should not be limited by
the current range of index values of the array and requires any gaps in
the index range to be set with values of null. I really don't have
anything to add to discussion other than that I agree with Tom's
assessment, but rather want clarify what might be a slightly hazy
interpretation of the specs listed below.

Tom Lane wrote:

Actually, now that I look closely, I think the SQL spec demands exactly
this. Recall that SQL99 only allows one-dimensional, lower-bound-one
arrays. The specification for UPDATE ... SET C[I] = SV ... reads

Case:

i) If the value of C is null, then an exception condition is
raised: data exception - null value in array target.

ii) Otherwise:

1) Let N be the maximum cardinality of C.

2) Let M be the cardinality of the value of C.

3) Let I be the value of the <simple value specification>
immediately contained in <update target>.

4) Let EDT be the element type of C.

5) Case:

A) If I is greater than zero and less than or equal to
M, then the value of C is replaced by an array A
with element type EDT and cardinality M derived as
follows:

I) For j varying from 1 (one) to I-1 and from I+1 to
M, the j-th element in A is the value of the j-th
element in C.

II) The I-th element of A is set to the specified
update value, denoted by SV, by applying the
General Rules of Subclause 9.2, "Store assignment",
to the I-th element of A and SV as TARGET and
VALUE, respectively.

B) If I is greater than M and less than or equal to
N, then the value of C is replaced by an array A
with element type EDT and cardinality I derived as
follows:

I) For j varying from 1 (one) to M, the j-th element
in A is the value of the j-th element in C.

II) For j varying from M+1 to I-1, the j-th element in
A is the null value.

III) The I-th element of A is set to the specified
update value, denoted by SV, by applying the
General Rules of Subclause 9.2, "Store assignment",
to the I-th element of A and SV as TARGET and
VALUE, respectively.

C) Otherwise, an exception condition is raised: data
exception - array element error.

We currently violate case i by allowing the null array value to be
replaced by a single-element array. I'm disinclined to change that,
as I think our behavior is more useful than the spec's. But case ii.5.B
pretty clearly describes null-fill, so I think we'd better do that, now
that we can.

regards, tom lane

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

--
erik jones <erik@myemma.com>
software development
emma(r)