[BUGS] distance operator dont work
Dear all, I have a table containing polygon and I try a distance operator which fails. (select * from mytable order by point(10,10) <-> mycol) Postgres responds that the operator <-> between a point and a polygon is unknown. I look in the pg_operator.h (in src/include/catalog) where I think all builtin operators are defined and found these definitions DATA(insert OID = 517 ( "<->" PGNSP PGUID b f 600 600 701 517 0 0 0 0 0 point_distance - - )); DATA(insert OID = 613 ( "<->" PGNSP PGUID b f 600 628 701 0 0 0 0 0 0 dist_pl - - )); DATA(insert OID = 614 ( "<->" PGNSP PGUID b f 600 601 701 0 0 0 0 0 0 dist_ps - - )); DATA(insert OID = 615 ( "<->" PGNSP PGUID b f 600 603 701 0 0 0 0 0 0 dist_pb - - )); DATA(insert OID = 616 ( "<->" PGNSP PGUID b f 601 628 701 0 0 0 0 0 0 dist_sl - - )); DATA(insert OID = 617 ( "<->" PGNSP PGUID b f 601 603 701 0 0 0 0 0 0 dist_sb - - )); DATA(insert OID = 618 ( "<->" PGNSP PGUID b f 600 602 701 0 0 0 0 0 0 dist_ppath - - )); DATA(insert OID = 706 ( "<->" PGNSP PGUID b f 603 603 701 706 0 0 0 0 0 box_distance - - )); DATA(insert OID = 707 ( "<->" PGNSP PGUID b f 602 602 701 707 0 0 0 0 0 path_distance - - )); DATA(insert OID = 708 ( "<->" PGNSP PGUID b f 628 628 701 708 0 0 0 0 0 line_distance - - )); DATA(insert OID = 709 ( "<->" PGNSP PGUID b f 601 601 701 709 0 0 0 0 0 lseg_distance - - )); DATA(insert OID = 712 ( "<->" PGNSP PGUID b f 604 604 701 712 0 0 0 0 0 poly_distance - - )); DATA(insert OID = 1522 ( "<->" PGNSP PGUID b f 600 718 701 0 0 0 0 0 0 dist_pc - - )); DATA(insert OID = 1523 ( "<->" PGNSP PGUID b f 718 604 701 0 0 0 0 0 0 dist_cpoly - - )); DATA(insert OID = 1520 ( "<->" PGNSP PGUID b f 718 718 701 1520 0 0 0 0 0 circle_distance - - )); DATA(insert OID = 1524 ( "<->" PGNSP PGUID b f 628 603 701 0 0 0 0 0 0 dist_lb - - )); and there is none between a point and a polygon. (600 604) (furthermore the 628 type is defined in ptypes.h as not implemented. I did the following test, I patch the line DATA(insert OID = 613 ( "<->" PGNSP PGUID b f 600 628 701 0 0 0 0 0 0 dist_pl - - )); replacing 628 by 604 , compiling and reinstalling everything including the database and It works (I didnt verify the result) So I am wondering , if I miss something, do I have to convert my point in a degenerated circle or polygon) or do you forget one definition ? I have checked the source code in versions 7.4.2,7.4.3 , 8.1.0 & 8.1.4. The version I patched is 8.1.4 and the lines above were extracted from 7.4.2 Thank you Bernard SNYERS Chief Architect Ionic Software [EMAIL PROTECTED]
[BUGS] BUG #2572: ALTER TABLE ADD COLUMN
The following bug has been logged online: Bug reference: 2572 Logged by: Emil J. Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.4 Operating system: Windows XP Description:ALTER TABLE ADD COLUMN Details: I create some function (PLPGSQL): . BEGIN ALTER TABLE sch.table ADD COLUMN abc INTEGER; RETURN NULL; END; After i called it, nothing happend. No Error, No Exception, No effect - no column added. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2572: ALTER TABLE ADD COLUMN
On Sat, Aug 12, 2006 at 11:53:08AM +, Emil J. wrote: > I create some function (PLPGSQL): > > . > BEGIN > ALTER TABLE sch.table ADD COLUMN abc INTEGER; > RETURN NULL; > END; > > After i called it, nothing happend. No Error, No Exception, No effect - no > column added. Works here. Is it possible that the calling transaction rolled back or that it hadn't committed yet and you looked at the table in another transaction? Can you provide a complete test case? Example: test=> CREATE TABLE foo (id integer); CREATE TABLE test=> CREATE FUNCTION test() RETURNS void AS $$ test$> BEGIN test$> ALTER TABLE foo ADD COLUMN newcol integer; test$> END; test$> $$ LANGUAGE plpgsql; CREATE FUNCTION test=> \d foo Table "public.foo" Column | Type | Modifiers +-+--- id | integer | test=> SELECT test(); test -- (1 row) test=> \d foo Table "public.foo" Column | Type | Modifiers +-+--- id | integer | newcol | integer | -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [BUGS] distance operator dont work
On Sat, Aug 12, 2006 at 08:52:48AM +0200, bernard snyers wrote: > I have a table containing polygon and I try a distance operator which > fails. (select * from mytable order by point(10,10) <-> mycol) > > Postgres responds that the operator <-> between a point and a polygon is > unknown. > > I look in the pg_operator.h (in src/include/catalog) where I think all > builtin operators are defined and found these definitions Are you aware that you can query the system catalogs with SQL instead of digging into header files? See also psql's \do command. http://www.postgresql.org/docs/8.1/interactive/catalogs.html http://www.postgresql.org/docs/8.1/interactive/catalog-pg-operator.html http://www.postgresql.org/docs/8.1/interactive/app-psql.html > and there is none between a point and a polygon. (600 604) (furthermore > the 628 type is defined in ptypes.h as not implemented. > > I did the following test, > I patch the line > DATA(insert OID = 613 ( "<->" PGNSP PGUID b f 600 628 701 > 0 00 0 0 0 dist_pl - - )); > > replacing 628 by 604 , > compiling and reinstalling everything including the database The dist_pl function expects a line argument; calling it with a different type could cause unexpected results or even a server crash. And are you aware that you can use CREATE OPERATOR instead of hacking the source code and recompiling? http://www.postgresql.org/docs/8.1/interactive/sql-createoperator.html > and It works (I didnt verify the result) In what sense does it work if you didn't verify the result? > So I am wondering , if I miss something, do I have to convert my point > in a degenerated circle or polygon) or do you forget one definition ? I'd guess that certain features remain unimplemented due to lack of interest; maybe one of the developers can comment on the history of support for geometry operations. A nice thing about PostgreSQL is that it's extensible without having to hack the source code: you can write your own functions in PL/pgSQL or C or some other language and create operators that call those functions. Such customizations survive migrations via database dumps so you don't have to remember to patch the code every time you upgrade. If you're working with spatial data then you might want to check out PostGIS: http://www.postgis.org/ -- Michael Fuhr ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [BUGS] distance operator dont work
bernard snyers <[EMAIL PROTECTED]> writes: > I patch the line > DATA(insert OID = 613 ( "<->" PGNSP PGUID b f 600 628 701 > 0 00 0 0 0 dist_pl - - )); > replacing 628 by 604 , > compiling and reinstalling everything including the database > and It works (I didnt verify the result) Obviously not. line and polygon don't have the same internal representation, so there's no way that's going to work. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
