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.