Thread: strange array insertion

Hello,

This is directly out of PostgreSQL User's Guide.

regression=# CREATE TABLE sal_emp (
regression(#     name            text,
regression(#     pay_by_quarter  int4[],
regression(#     schedule        text[][]
regression(# );
CREATE

regression=# INSERT INTO sal_emp
regression-#     VALUES ('Bill',
regression(#     '{10000, 10000, 10000, 10000}',
regression(#     '{{"meeting", "lunch"}, {}}');
INSERT 48043 1

regression=# select * from sal_emp ;
name |      pay_by_quarter       |      schedule
------+---------------------------+--------------------
Bill | {10000,10000,10000,10000} | {{"meeting"},{""}}
(1 row)

regression=# INSERT INTO sal_emp
regression-#     VALUES ('Carol',
regression(#     '{20000, 25000, 25000, 25000}',
regression(#     '{{"talk", "consult"}, {"meeting"}}');
INSERT 48044 1

regression=# select * from sal_emp ;
name  |      pay_by_quarter       |        schedule
-------+---------------------------+------------------------
Bill  | {10000,10000,10000,10000} | {{"meeting"},{""}}
Carol | {20000,25000,25000,25000} | {{"talk"},{"meeting"}}
(2 rows)

************
Strange enough, it seems like the 2-d text array size depends on the last
element size.

regression=# INSERT INTO sal_emp
regression-#     VALUES ('Carol',
regression(#     '{20000, 25000, 25000, 25000}',
regression(#     '{{"talk", "consult"}, {"meeting",""}}');
INSERT 48045 1
regression=# select * from sal_emp ;
name  |      pay_by_quarter       |              schedule
-------+---------------------------+-------------------------------------
Bill  | {10000,10000,10000,10000} | {{"meeting"},{""}}
Carol | {20000,25000,25000,25000} | {{"talk"},{"meeting"}}
Carol | {20000,25000,25000,25000} | {{"talk","consult"},{"meeting",""}}
(3 rows)

regression=# INSERT INTO sal_emp
regression-#     VALUES ('Carol',
regression(#     '{20000, 25000, 25000, 25000}',
regression(#     '{{"talk", "consult"}, {"meeting","",""}}');
INSERT 48046 1
regression=# select * from sal_emp ;
name  |      pay_by_quarter       |                 schedule
-------+---------------------------+-------------------------------------------
Bill  | {10000,10000,10000,10000} | {{"meeting"},{""}}
Carol | {20000,25000,25000,25000} | {{"talk"},{"meeting"}}
Carol | {20000,25000,25000,25000} | {{"talk","consult"},{"meeting",""}}
Carol | {20000,25000,25000,25000} |
{{"talk","consult",""},{"meeting","",""}}
(4 rows)

*******
See. So, am I doing anything wrong or is it a bug?  Btw, I was informed that
rollback is not possible if array is involved (it auto-commits before you
issue "commit" or "rollback" comands).
eg; "begin;", "update tb1 set arr[1]='something';", "rollback;"

This will not be able to rollback.  Any suggestions? coz I am using array A
LOT!  =)

Thanks very much.


- Bernie
_________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at
http://profiles.msn.com.

"Bernie Huang" <bernieyth@hotmail.com> writes:
> Strange enough, it seems like the 2-d text array size depends on the last
> element size.

Looks like a bug to me too ... probably silliness in the array_in
routine that determines dimensions of an unspecified-dimensions input.
Want to look at it?

> Btw, I was informed that
> rollback is not possible if array is involved (it auto-commits before you
> issue "commit" or "rollback" comands).
> eg; "begin;", "update tb1 set arr[1]='something';", "rollback;"

Array update had this strange idea that it's OK to scribble on the
source tuple :-(.  This is fixed for 7.1.

            regards, tom lane