Обсуждение: problem with array of boxes
Hello! I have run into a problem with the array of boxes datatype. Here is a simple example: testdb=# CREATE TABLE boxarray_test (col1 BOX[2]); CREATE testdb=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}'); INSERT 32957 1 testdb=# SELECT * FROM boxarray_test; col1 --------------- {(4,4),(2,2)} (1 row) Instead of the above, I expected the result of the SELECT to be: {"(3,3),(1,1)","(4,4),(2,2)"} Arrays of other geometric types worked like I expected them to do. Is this a bug? I'm running PostgreSQL 7.2 on Mac OS X 10.1.3 (powerpc-apple-darwin5.3), compiled by GCC 2.95.2. I ran the regression tests against my installation and all tests were completed successfully. (The tests don't seem to cover arrays of geometric types, though.) -Andre -- Andre Radke + mailto:lists@spicynoodles.net + http://www.spicynoodles.net/
Andre Radke <lists@spicynoodles.net> writes: > I have run into a problem with the array of boxes datatype. After a little poking at this, it seems that some parts of the array support code may be failing to pay attention to "typdelim". Type box has typdelim set to ';' (it's the only standard datatype whose typdelim is not ','). Changing that to ',' made the behavior less unexpected. Haven't dug into the code yet for a proper fix. This does beg the question of whether box's typdelim should be ';' rather than the standard ','. I can see why that was done: box likes to use commas in its text representation. But I really wonder how much client code will be prepared to cope with arrays represented with ';' not ',' between items ... regards, tom lane
Andre Radke <lists@spicynoodles.net> writes: > testdb=# CREATE TABLE boxarray_test (col1 BOX[2]); > testdb=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}'); > testdb=# SELECT * FROM boxarray_test; > col1 > --------------- > {(4,4),(2,2)} > (1 row) I've finished looking into this, and the short answer is that your input is not syntactically correct. Because type box has typdelim = ';', the correct input would have been INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}'); (btw, you could omit the double-quote marks here.) There is indeed a bug here: since the array parser didn't think the comma was an item delimiter, IMHO it should have considered the array to contain one item (3,3),(1,1),(4,4),(2,2) which would have provoked an error when handed to the box-datatype input parser. Instead the array parser messed up and passed only the second double-quoted substring to the box input routine. I have fixed this for 7.3: with the just-committed code, I get boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}'); ERROR: Bad box external representation '(3,3),(1,1),(4,4),(2,2)' boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}'); INSERT 533436 1 boxes=# INSERT INTO boxarray_test VALUES ('{(3,3),(1,1);(4,4),(2,2)}'); INSERT 533437 1 boxes=# select * from boxarray_test; col1 --------------------------- {(3,3),(1,1);(4,4),(2,2)} {(3,3),(1,1);(4,4),(2,2)} (2 rows) This still leaves us with the question of whether it's really a good idea that type box has typdelim ';' and not ',' like everything else uses. Anyone have a strong feeling about changing it or not? If we change it, we'd instead get this behavior: boxes=# update pg_type set typdelim = ',' where typname = 'box'; UPDATE 1 boxes=# select * from boxarray_test; col1 ------------------------------- {"(3,3),(1,1)","(4,4),(2,2)"} {"(3,3),(1,1)","(4,4),(2,2)"} (2 rows) boxes=# INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)","(4,4),(2,2)"}'); INSERT 533438 1 and the double quotes would be required. One argument against changing is that it'd break pg_dump output for existing tables containing arrays of boxes ... if any there be. Given that this hasn't come up before, I wonder if anyone's using 'em. regards, tom lane
At 18:01h -0500 16.03.2002, Tom Lane wrote: >I've finished looking into this, and the short answer is that your input >is not syntactically correct. Because type box has typdelim = ';', the >correct input would have been > >INSERT INTO boxarray_test VALUES ('{"(3,3),(1,1)";"(4,4),(2,2)"}'); Thanks! I changed my code to use a semi-colon instead of a comma as delimiter and that indeed solved my problem. >This still leaves us with the question of whether it's really a good >idea that type box has typdelim ';' and not ',' like everything else >uses. Anyone have a strong feeling about changing it or not? I'm relatively new to PostgreSQL, so I don't have a qualified opinion on this. -Andre -- Andre Radke + mailto:lists@spicynoodles.net + http://www.spicynoodles.net/