pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Array assignment behavior (was Re: Stored procedure array limits)
7+ messages / 5 participants
[nested] [flat]

* Array assignment behavior (was Re: Stored procedure array limits)
@ 2006-09-29 16:14 Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-29 16:35 ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) Casey Duncan <casey@pandora.com>
  2006-09-29 17:08 ` Re: Array assignment behavior (was Re: Stored procedure array Paul B. Anderson <paul.a@pnlassociates.com>
  0 siblings, 2 replies; 7+ messages in thread

From: Tom Lane @ 2006-09-29 16:14 UTC (permalink / raw)
  To: Paul B. Anderson <paul.a@pnlassociates.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org; pgsql-hackers@postgresql.org

[ 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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits)
  2006-09-29 16:14 Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-29 16:35 ` Casey Duncan <casey@pandora.com>
  2006-09-29 16:40   ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) John D. Burger <john@mitre.org>
  1 sibling, 1 reply; 7+ messages in thread

From: Casey Duncan @ 2006-09-29 16:35 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Paul B. Anderson <paul.a@pnlassociates.com>; pgsql-admin; pgsql-general@postgresql.org; pgsql-hackers@postgresql.org

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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits)
  2006-09-29 16:14 Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-29 16:35 ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) Casey Duncan <casey@pandora.com>
@ 2006-09-29 16:40   ` John D. Burger <john@mitre.org>
  2006-09-29 17:59     ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 7+ messages in thread

From: John D. Burger @ 2006-09-29 16:40 UTC (permalink / raw)
  To: Postgres General <pgsql-general@postgresql.org>; PostgreSQL-development <pgsql-hackers@postgresql.org>; +Cc: pgsql-admin

>> 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




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits)
  2006-09-29 16:14 Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-29 16:35 ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) Casey Duncan <casey@pandora.com>
  2006-09-29 16:40   ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) John D. Burger <john@mitre.org>
@ 2006-09-29 17:59     ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-29 18:51       ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure Erik Jones <erik@myemma.com>
  0 siblings, 1 reply; 7+ messages in thread

From: Tom Lane @ 2006-09-29 17:59 UTC (permalink / raw)
  To: John D. Burger <john@mitre.org>; +Cc: Postgres General <pgsql-general@postgresql.org>; PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-admin

"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



^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Array assignment behavior (was Re: Stored procedure
  2006-09-29 16:14 Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-29 16:35 ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) Casey Duncan <casey@pandora.com>
  2006-09-29 16:40   ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) John D. Burger <john@mitre.org>
  2006-09-29 17:59     ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-29 18:51       ` Erik Jones <erik@myemma.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Erik Jones @ 2006-09-29 18:51 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Postgres General <pgsql-general@postgresql.org>; PostgreSQL-development <pgsql-hackers@postgresql.org>; pgsql-admin

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)




^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: Array assignment behavior (was Re: Stored procedure array
  2006-09-29 16:14 Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-09-29 17:08 ` Paul B. Anderson <paul.a@pnlassociates.com>
  2006-09-29 17:37   ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure Erik Jones <erik@myemma.com>
  1 sibling, 1 reply; 7+ messages in thread

From: Paul B. Anderson @ 2006-09-29 17:08 UTC (permalink / raw)
  To: pgsql-admin; pgsql-general@postgresql.org; pgsql-hackers@postgresql.org

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
>
> .
>
>   

^ permalink  raw  reply  [nested|flat] 7+ messages in thread

* Re: [GENERAL] Array assignment behavior (was Re: Stored procedure
  2006-09-29 16:14 Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
  2006-09-29 17:08 ` Re: Array assignment behavior (was Re: Stored procedure array Paul B. Anderson <paul.a@pnlassociates.com>
@ 2006-09-29 17:37   ` Erik Jones <erik@myemma.com>
  0 siblings, 0 replies; 7+ messages in thread

From: Erik Jones @ 2006-09-29 17:37 UTC (permalink / raw)
  To: Paul B. Anderson <paul.a@pnlassociates.com>; +Cc: pgsql-admin; pgsql-general@postgresql.org; pgsql-hackers@postgresql.org

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)




^ permalink  raw  reply  [nested|flat] 7+ messages in thread


end of thread, other threads:[~2006-09-29 18:51 UTC | newest]

Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-29 16:14 Array assignment behavior (was Re: Stored procedure array limits) Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 16:35 ` Casey Duncan <casey@pandora.com>
2006-09-29 16:40   ` John D. Burger <john@mitre.org>
2006-09-29 17:59     ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-29 18:51       ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure Erik Jones <erik@myemma.com>
2006-09-29 17:08 ` Re: Array assignment behavior (was Re: Stored procedure array Paul B. Anderson <paul.a@pnlassociates.com>
2006-09-29 17:37   ` Re: [GENERAL] Array assignment behavior (was Re: Stored procedure Erik Jones <erik@myemma.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