Thread

1. strange array insertion

   Bernie Huang <bernieyth@hotmail.com> — 2000-10-31T18:57:23Z

   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.
   
   
   

2. Re: strange array insertion

   Tom Lane <tgl@sss.pgh.pa.us> — 2000-11-02T17:08:18Z

   "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