Thread: Damn bug!
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
Attachment
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
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 #
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
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
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