Thread
-
Damn bug!
Bernie Huang <bernie.huang@ec.gc.ca> — 2000-07-20T19:13:52Z
Hi, Sorry for the subject, but it is driving me crazy that I spend my entire morning searching for this little bug. =( Well, I found it. I have a field using array (eg; col1 text[]) in Postgres, and it's a list of attributes. (eg; {"hi","hello","whatever","Empty",...}) When I tried to update elements in the array via PHP script, $query = "update table set col1[1]='$var1', col1[2]='$var2', ... col1[4]='$var4'"; it worked alright; however, when it came to the word 'Empty', it just wouldn't update. So, after a lot of struggle... )xp ... I finally replaced the word 'Empty' with 'None' or something alike, and it worked! Although this is a rare case, I thought I would just share this with you so you won't get tripped over it. Don't know if it's a PHP bug or a Postgres bug! - Bernie -
Re: Damn bug!
Andrew McMillan <andrew@catalyst.net.nz> — 2000-07-20T20:51:45Z
Bernie Huang wrote: > > I have a field using array (eg; col1 text[]) in Postgres, and it's a > list of attributes. (eg; {"hi","hello","whatever","Empty",...}) > > When I tried to update elements in the array via PHP script, > > $query = "update table > set col1[1]='$var1', > col1[2]='$var2', > ... > col1[4]='$var4'"; > > it worked alright; however, when it came to the word 'Empty', it just > wouldn't update. So, after a lot of struggle... )xp ... I finally > replaced the word 'Empty' with 'None' or something alike, and it worked! > I think that there is something strange going on with assignment of multiple array subscripts in PostgreSQL there... It looks like a PostgreSQL limitation which is being detected on an 'insert' but is not being detected on an 'update' (and it probably should be). Look at the interesting log of various stuff from psql doing similar things: testing=# create table t1 ( c1 text[] ); CREATE testing=# insert into t1 (c1[1], c1[2], c1[3] ) values('not', 'actually', 'empty' ); ERROR: Attribute 'c1' specified more than once testing=# insert into t1 (c1[1], c1[2], c1[3] ) values('not', 'actually', 'empt' ); ERROR: Attribute 'c1' specified more than once testing=# insert into t1 (c1 ) values('{"not", "actually", "empt"}' ); INSERT 373577 1 testing=# select * from t1; c1 --------------------------- {"not","actually","empt"} (1 row) testing=# insert into t1 (c1 ) values('{"not", "actually", "empty"}' ); INSERT 373578 1 testing=# select * from t1; c1 ---------------------------- {"not","actually","empt"} {"not","actually","empty"} (2 rows) testing=# update t1 set c1[2] = 'empty'; UPDATE 2 testing=# select * from t1; c1 ------------------------- {"not","empty","empt"} {"not","empty","empty"} (2 rows) testing=# update t1 set c1[2] = 'empty', c1[3] = 'full'; UPDATE 2 testing=# select * from t1; c1 ------------------------- {"not","empty","full"} {"not","empty","empty"} (2 rows) ---------------------------------------------------- Now that was a bit strange, wasn't it? ---------------------------------------------------- testing=# update t1 set c1[2] = 'other', c1[3] = 'full'; UPDATE 2 testing=# select * from t1; c1 ------------------------- {"not","other","full"} {"not","other","empty"} (2 rows) testing=# update t1 set c1[3] = 'full'; UPDATE 2 testing=# select * from t1; c1 ------------------------ {"not","other","full"} {"not","other","full"} (2 rows) testing=# update t1 set c1[2] = 'strange', c1[3] = 'notfull'; UPDATE 2 testing=# select * from t1; c1 -------------------------- {"not","strange","full"} {"not","strange","full"} (2 rows) ---------------------------------------------------------- So it looks like UPDATE is silently ignoring second and subsequent references to the same array variable. In most cases... A good workaround muight be for you to use the '{"blah", "blah", "blah"}' syntax for updating the array, although it's a pretty messy syntax. Cheers, Andrew. -- _____________________________________________________________________ Andrew McMillan, e-mail: Andrew@cat-it.co.nz Catalyst IT Ltd, PO Box 10-225, Level 22, 105 The Terrace, Wellington Me: +64 (21) 635 694, Fax: +64 (4) 499 5596, Office: +64 (4) 499 2267 -
Re: Damn bug!
Jan Wieck <janwieck@t-online.de> — 2000-07-20T21:25:45Z
Bernie Huang wrote: [Charset iso-8859-15 unsupported, filtering to ASCII...] > Hi, > > Sorry for the subject, but it is driving me crazy that I spend my entire > morning searching for this little bug. =( > > Well, I found it. > > I have a field using array (eg; col1 text[]) in Postgres, and it's a > list of attributes. (eg; {"hi","hello","whatever","Empty",...}) > > When I tried to update elements in the array via PHP script, > > $query = "update table > set col1[1]='$var1', > col1[2]='$var2', > ... > col1[4]='$var4'"; > > it worked alright; however, when it came to the word 'Empty', it just > wouldn't update. So, after a lot of struggle... )xp ... I finally > replaced the word 'Empty' with 'None' or something alike, and it worked! > > Although this is a rare case, I thought I would just share this with you > so you won't get tripped over it. Don't know if it's a PHP bug or a > Postgres bug! A Postgres bug: pgsql=# create table t1 (a text[]); CREATE pgsql=# insert into t1 values ('{"foo","bar"}'); INSERT 18872 1 pgsql=# select * from t1; a --------------- {"foo","bar"} (1 row) pgsql=# update t1 set a[1] = 'new', a[2] = 'Empty'; UPDATE 1 pgsql=# select * from t1; a --------------- {"new","bar"} (1 row) pgsql=# update t1 set a[1] = 'next', a[2] = 'None'; UPDATE 1 pgsql=# select * from t1; a ----------------- {"next","None"} (1 row) Don't know where this might happen, but it's interesting that our ORDBMS treats some "text" literal special. Looks like a fogotten hack somewhere :-) Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # -
Re: Damn bug!
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-21T06:03:39Z
JanWieck@t-online.de (Jan Wieck) writes: > pgsql=# update t1 set a[1] = 'new', a[2] = 'Empty'; > UPDATE 1 > pgsql=# select * from t1; > a > --------------- > {"new","bar"} > (1 row) > Don't know where this might happen, but it's interesting that > our ORDBMS treats some "text" literal special. It's not dependent on what the text is, but it *is* dependent on the length of the text. regression=# select * from t1; a --------------- {"foo","bar"} (1 row) regression=# update t1 set a[1] = 'foo1', a[2] = 'barge'; UPDATE 1 regression=# select * from t1; a ---------------- {"foo1","bar"} (1 row) regression=# update t1 set a[1] = 'foo2', a[2] = 'barg'; UPDATE 1 regression=# select * from t1; a ----------------- {"foo2","barg"} (1 row) I was confused a few days ago when people claimed that it was possible to update different elements of an array with separate assignments in a single UPDATE. From what I know of the system, this *should not work* --- you should get only one of the updates applied, because each assignment will independently invoke array_set and construct a separate new value for what's really the same field. I think what we're seeing here is evidence that it really doesn't work. Possibly there's some hack in array_set that overwrites the source data (which it shouldn't be doing, in any case!) when the data length doesn't need to change. Needs more digging to understand in detail... regards, tom lane -
Re: Damn bug!
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-21T06:18:59Z
I wrote: > I think what we're seeing here is evidence that it really doesn't work. > Possibly there's some hack in array_set that overwrites the source > data (which it shouldn't be doing, in any case!) when the data length > doesn't need to change. Needs more digging to understand in detail... Indeed, it looks like that's exactly what's happening. Try this on for size: regression=# select * from t1; a ---------------- {"foo1","bar"} (1 row) regression=# begin; BEGIN regression=# update t1 set a[1] = 'foot'; UPDATE 1 regression=# abort; ROLLBACK regression=# select * from t1; a ---------------- {"foot","bar"} (1 row) <Dana Carvey> My, isn't *that* special ... </Dana Carvey> Evidently, array_set scribbles on its source object when the size of the object isn't changed by the element assignment. This is why it's possible for more than one element assignment to appear to work; when the later assignments are executed with the same old source datum, they see the other fields as updated. Too bad scribbling on disk buffers is verboten. I have to hack on the array support soon for TOAST anyway, and I'll make sure that this idiocy goes away then. That will mean that in fact you don't get more than one array update per UPDATE. Don't see any easy way around that, unless we want to hack on the parser to convert UPDATE table SET a[1] = foo, a[2] = bar into UPDATE table SET a = array_set(array_set(a, 1, foo), 2, bar) That might not be totally impractical but it looks ugly... Thomas, what do you think about it? regards, tom lane -
Re: Damn bug!
Tom Lane <tgl@sss.pgh.pa.us> — 2000-07-23T05:57:40Z
I believe I've fixed all these array problems as of current CVS sources, a/k/a Postgres 7.1-to-be. There's a lot more that could be done (eg, addressing the inability to have individual NULL elements in an array), but other issues beckon... regards, tom lane