[SQL] dbmirror - migration to 8.3 from 7.4
Hi, we have been running our own heavily modified/enhanced version of dbmirror, running on 7.4 for some years, and now it is the time to upgrade to 8.3. We have called our approach "Conditional row grained + FK dependency oriented lazy replication", that is, any FK dependencies of a row are tranfered only when needed, and each remote slave has a subset of the master DB. This is applied to a uucp network of postgresql installations that communicate over satelite dialup connections. That is why we cannot follow any officially supported replication platform. Now back to my issue, In the code, i do some SELECTs from the pg_catalog.pg_index, pg_catalog.pg_constraint c,pg_catalog.pg_class, pg_catalog.pg_attribute and i would like to have your opinion on wether some semantics have changed or added to the new pg_catalog tables. The way i find the primary key of a table is: SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=TABLEOID; i noticed that some columns have been added to pg_index : indisvalid, indcheckxmin, indisready,indoption Should i include any of them (e.g. indisvalid) in the where clause above? The way i find the FK of a table is: SELECT c.confrelid,c.conkey,c.confkey,f.relname FROM pg_catalog.pg_constraint c,pg_catalog.pg_class f WHERE c.contype = 'f' AND c.confrelid = f.oid AND c.conrelid= TABLEOID; I noticed that some columns have been added to pg_constraint: conpfeqop,conppeqop,conffeqop Should i change something to the above query? Finally, the way i find the name of a column is: SELECT attname FROM pg_attribute WHERE attrelid=TABLEOID and attnum=ATTNUM; Also, i had to change any int2vector code, since now int2vector are implemented like varlenas, and also i had to add PG_DETOAST_DATUM on any array Datum. The code seems to work however i'd like your comments if i miss something. Thanks a lot. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dbmirror - migration to 8.3 from 7.4
> we have been running our own heavily modified/enhanced version of dbmirror, > running on 7.4 for some years, > and now it is the time to upgrade to 8.3. > > The way i find the primary key of a table is: >SELECT indkey FROM pg_index WHERE indisprimary='t' AND > indrelid=TABLEOID; > i noticed that some columns have been added to pg_index : indisvalid, > indcheckxmin, indisready,indoption > Should i include any of them (e.g. indisvalid) in the where clause above? Do you use oid? We had an issue where old code relied on oid and when we tested 8.2 we had issues with oid being used but not available. The fix was easy and only required a id-column of type serial. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dbmirror - migration to 8.3 from 7.4
>> The way i find the primary key of a table is: >>SELECT indkey FROM pg_index WHERE indisprimary='t' AND >> indrelid=TABLEOID; >> i noticed that some columns have been added to pg_index : indisvalid, >> indcheckxmin, indisready,indoption >> Should i include any of them (e.g. indisvalid) in the where clause above? > > Do you use oid? We had an issue where old code relied on oid and when > we tested 8.2 we had issues with oid being used but not available. The > fix was easy and only required a id-column of type serial. ... with oid being used [in our webcode] but not available. -- regards Claus When lenity and cruelty play for a kingdom, the gentlest gamester is the soonest winner. Shakespeare -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dbmirror - migration to 8.3 from 7.4
Στις Wednesday 07 May 2008 14:49:31 ο/η Claus Guttesen έγραψε: > > we have been running our own heavily modified/enhanced version of dbmirror, > > running on 7.4 for some years, > > and now it is the time to upgrade to 8.3. > > > > The way i find the primary key of a table is: > >SELECT indkey FROM pg_index WHERE indisprimary='t' AND > > indrelid=TABLEOID; > > i noticed that some columns have been added to pg_index : indisvalid, > > indcheckxmin, indisready,indoption > > Should i include any of them (e.g. indisvalid) in the where clause above? > > Do you use oid? We had an issue where old code relied on oid and when > we tested 8.2 we had issues with oid being used but not available. The > fix was easy and only required a id-column of type serial. > No, we dont use OID as primary keys. -- Achilleas Mantzios -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] how to check if a point is contained in a polygon ?
Hello, I'm busy to work on an application where the user can select (with precision) an area on a map (for example the contours of a lake) and I have to retrieve all the data (specimen observations) within this area. I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3, long3), (..., ...)] which form a polygon. In my database every specimen has a pair of coordinates where it has been collected. Is there an SQL function to check if a point is contained in a polygon shape (before I start to write my own) ? I tried something like : rodentia=> select point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))'; ERROR: operator does not exist: point <@ polygon HINT: No operator matches the given name and argument type(s). You may need to add explicit type casts. ... but without success as you can see ;\ In advance thanks, Julien -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: [EMAIL PROTECTED] @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to check if a point is contained in a polygon ?
Julien Cigar wrote: Hello, I'm busy to work on an application where the user can select (with precision) an area on a map (for example the contours of a lake) and I have to retrieve all the data (specimen observations) within this area. I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3, long3), (..., ...)] which form a polygon. In my database every specimen has a pair of coordinates where it has been collected. Are you familiar with the PostGIS project - if you're doing a lot of this, it might be worthwhile looking into. http://www.postgis.org/ Is there an SQL function to check if a point is contained in a polygon shape (before I start to write my own) ? I tried something like : rodentia=> select point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))'; Works both ways around here: SELECT point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))'; SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)'; I'm running 8.3, but it seems to be in 8.2 too. Try \do '<@' from psql to see what operators are available. -- Richard Huxton Archonet Ltd -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Insert with pl/pgsql trigger
I am trying to create a table that is a log of another table in 8.3.1 using
a trigger. Both tables look like
Table "public.cpe_health_history"
Column|Type| Modifiers
--++---
cpe_healthid | integer| not null
mac | macaddr|
polldate | integer|
health | smallint |
rank | smallint |
data | integer[] |
alarm| smallint[] |
My trigger is :
CREATE OR REPLACE FUNCTION log_cpe_health() RETURNS trigger AS '
DECLARE
BEGIN
-- Update last outage before inserting
EXECUTE ''INSERT INTO cpe_health_history VALUES '' || NEW;
END;
' LANGUAGE plpgsql;
-- Create Trigger on outagelog table
CREATE TRIGGER cpe_health_log AFTER INSERT OR UPDATE ON dhct_health
FOR EACH ROW EXECUTE PROCEDURE log_cpe_health();
I am getting the following error:
health-test=> INSERT INTO dhct_health (mac, polldate, health, rank, data,
alarm) VALUES ('0001', 1210169492, 3, 9, '{2, 4,6}', '{3,3,3}');
ERROR: syntax error at or near ":"
LINE 1: INSERT INTO cpe_health_history VALUES (7,00:00:00:00:00:01,1...
^
QUERY: INSERT INTO cpe_health_history VALUES
(7,00:00:00:00:00:01,1210169492,3,9,"{2,4,6}","{3,3,3}")
CONTEXT: PL/pgSQL function "log_cpe_health" line 4 at EXECUTE statement
If I change the insert on the command line to (it works):
health-test=> INSERT INTO cpe_health_history VALUES
(7,'00:00:00:00:00:01',1210169492,3,9,'{2,4,6}','{3,3,3}');
INSERT 0 1
Am I using the "NEW" parameter wrong?
Thanks,
Woody
iGLASS Networks
3300 Green Level Rd. West
Cary NC 27519
(919) 387-3550 x813
www.iglass.net
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to check if a point is contained in a polygon ?
Julien Cigar <[EMAIL PROTECTED]> writes: > Is there an SQL function to check if a point is contained in a polygon > shape (before I start to write my own) ? > I tried something like : > rodentia=> select point '(-8,25)' <@ polygon > '((-3,10),(8,18),(-3,30),(-10,20))'; > ERROR: operator does not exist: point <@ polygon > HINT: No operator matches the given name and argument type(s). You may > need to add explicit type casts. > ... but without success as you can see ;\ I think you are reading recent documentation and trying to apply it to an old Postgres version. <@ had some other name before 8.2 ... check the docs for whatever you are running. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Insert with pl/pgsql trigger
"Woody Woodring" <[EMAIL PROTECTED]> writes: > My trigger is : > CREATE OR REPLACE FUNCTION log_cpe_health() RETURNS trigger AS ' >DECLARE >BEGIN > -- Update last outage before inserting > EXECUTE ''INSERT INTO cpe_health_history VALUES '' || NEW; >END; > ' LANGUAGE plpgsql; That's never going to work because of quoting issues, and it wouldn't be an efficient way if it did work (because of having to re-parse and re-plan the INSERT each time). And if it did act the way you are imagining, it still wouldn't be a good way because you typically want some additional columns in the log table, such as a timestamp. In recent releases you can do it like this: INSERT INTO cpe_health_history VALUES (NEW.*); which can be extended to, eg, INSERT INTO cpe_health_history VALUES (NEW.*, now()); regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] how to check if a point is contained in a polygon ?
Oops, you're right .. I'm still running 8.1 and I missed this note in the documentation : "Note: Before PostgreSQL 8.2, the containment operators @> and <@ were respectively called ~ and @. These names are still available, but are deprecated and will eventually be retired." thanks On Wed, 2008-05-07 at 15:38 +0100, Richard Huxton wrote: > Julien Cigar wrote: > > Hello, > > > > I'm busy to work on an application where the user can select (with > > precision) an area on a map (for example the contours of a lake) and I > > have to retrieve all the data (specimen observations) within this area. > > > > I have a list of coordinates pair [(lat1, long1), (lat2, long2), (lat3, > > long3), (..., ...)] which form a polygon. In my database every specimen > > has a pair of coordinates where it has been collected. > > Are you familiar with the PostGIS project - if you're doing a lot of > this, it might be worthwhile looking into. > > http://www.postgis.org/ > > > Is there an SQL function to check if a point is contained in a polygon > > shape (before I start to write my own) ? > > > > I tried something like : > > > > rodentia=> select point '(-8,25)' <@ polygon > > '((-3,10),(8,18),(-3,30),(-10,20))'; > > Works both ways around here: > > SELECT point '(-8,25)' <@ polygon '((-3,10),(8,18),(-3,30),(-10,20))'; > > SELECT polygon '((-3,10),(8,18),(-3,30),(-10,20))' @> point '(-8,25)'; > > I'm running 8.3, but it seems to be in 8.2 too. > > Try \do '<@' from psql to see what operators are available. > -- Julien Cigar Belgian Biodiversity Platform http://www.biodiversity.be Université Libre de Bruxelles (ULB) Campus de la Plaine CP 257 Bâtiment NO, Bureau 4 N4 115C (Niveau 4) Boulevard du Triomphe, entrée ULB 2 B-1050 Bruxelles Mail: [EMAIL PROTECTED] @biobel: http://biobel.biodiversity.be/person/show/471 Tel : 02 650 57 52 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] Joining with result of a plpgsql function
Hello, I have a pl/pgsql function, defined as: CREATE FUNCTION tms.get_tms_summary(id integer) RETURNS tms.tms_summary get_tms_summary returns a composite type, tms_summary, which is comprised of several numerics. What I would like to do is something like: select f.id, f.name, tms.get_tms_summary(f.id) from foo f; However this returns only three columns, the third of which is the entire complex data type in one column. I can do: select * from tms.get_tms_summary(99); But I would really like to be able to combine it with other data and get a result set that looked like: f.id, f.name, tms_summary.col1, tms_summary.col2 ... Any thoughts or suggestions? Thank you, Matthew O'Connor -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Joining with result of a plpgsql function
On Wed, 7 May 2008, Matthew T. O'Connor wrote: > I have a pl/pgsql function, defined as: > > CREATE FUNCTION tms.get_tms_summary(id integer) >RETURNS tms.tms_summary > > get_tms_summary returns a composite type, tms_summary, which is > comprised of several numerics. > > What I would like to do is something like: > > select f.id, f.name, tms.get_tms_summary(f.id) from foo f; > > However this returns only three columns, the third of which is the > entire complex data type in one column. > > I can do: select * from tms.get_tms_summary(99); > > But I would really like to be able to combine it with other data and get > a result set that looked like: > > f.id, f.name, tms_summary.col1, tms_summary.col2 ... Well I think select f.id, f.name, (tms.get_tms_summary(f.id)).* from foo f; would expand it out into separate columns, but I think that might also call it multiple times. You might have better luck combining that with a subquery like select id, name, (summary).col1, (summary).col2, ... from (select id, name, tms.get_tms_summary(f.id) as summary from foo) f; -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Joining with result of a plpgsql function
Stephan Szabo wrote: On Wed, 7 May 2008, Matthew T. O'Connor wrote: But I would really like to be able to combine it with other data and get a result set that looked like: f.id, f.name, tms_summary.col1, tms_summary.col2 ... Well I think select f.id, f.name, (tms.get_tms_summary(f.id)).* from foo f; would expand it out into separate columns, but I think that might also call it multiple times. You might have better luck combining that with a subquery like select id, name, (summary).col1, (summary).col2, ... from (select id, name, tms.get_tms_summary(f.id) as summary from foo) f; Ah, I knew there was an easy way to do it, I totally forgot / missed / didn't know about the (composite type).* syntax. Thank you! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
