[GENERAL] Help with storing spatial (map coordinates) data?
Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating an X-Talk front end called Revolution for the GUI development and have only some general experience with SQL. Thanks in advance John Tregea ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help with storing spatial (map coordinates) data?
John Tregea schrieb: Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I'd suggest starting w/ the contrib package and its cube datatype. This datatype maintains 3d-coordinates and has long/lat input and output. In theory if its just storing you could also just store the longitude, latitude in numeric fields. It really depends on what you really want to do with that data in the database. (e.g. what kind of searches you want to do) cube datatype for example is indexable which really helps in search queries. Regards Tino Wildenhain ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] delete seems to be getting blocked
hi, I am using postgresql 8.0.0. i have four tables in my database TAB1, has one primary key T1 TAB2 , has 2 fields, one is the primary ley T2 and the other one is the foreign key T1(from TAB1) TAB3 also has 2 fields, one is the primary ley T3 and the other is the foreign key T2(from TAB2) TAB4 has 2 fields again, primary key T4 and a foreign key T3(from TAB3) the disk is 100% full. i open psql datbase_name and do delete from TAB1 nothing seems to be happening for a long time, although when i do top, it shows postgres taking 99%. what is happening, cant delete be quickened? thanks, regards Surabhi
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Hi Tino, Thanks, I had just found the contrib directory and the spatial_ref_sys file as well. The database is to manage security assessments in supply chains and will store locations of buildings as well as points that define transportation routes. So the data will not be searched on but will be used to put risk assessment matrices into the correct order along a supply chain. I will try as you suggest and look at the cube datatype Thanks for your fast reply. Regards John T Tino Wildenhain wrote: John Tregea schrieb: Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I'd suggest starting w/ the contrib package and its cube datatype. This datatype maintains 3d-coordinates and has long/lat input and output. In theory if its just storing you could also just store the longitude, latitude in numeric fields. It really depends on what you really want to do with that data in the database. (e.g. what kind of searches you want to do) cube datatype for example is indexable which really helps in search queries. Regards Tino Wildenhain ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] test
[GENERAL] Best security practices for installing pgSQL with my software
My software package will install PostGreSQL on the server, and clients will connect to it with a windows smart client application. What would be the best way to keep the PostGreSQL usernames and passwords secure? I will be doing a silent install of the database, and obviously this will require a service username and password. Should I hardcode the service username and password? Or should I let the person installing the software enter their own username and password for the server? Also, what should I do when it comes to the actual user for the database? How should I handle these details? This software will be installed on many different Pcs by many different people. Can I use OpenSSL to secure communications between the clients and the server? Thanks
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing
David Fetter wrote: On Fri, Jun 09, 2006 at 03:55:04PM +0200, Aaron Bingham wrote: [EMAIL PROTECTED] wrote: I'm reading, and enjoying immensely, Fabial Pascal's book Practical Issues in Database Management. If you're interested in the theory of RDBMSs, you can start with the papers on Leonid Libkin's page and the books and papers they reference. :) http://www.cs.toronto.edu/~libkin/publ.html Thanks for the pointer, I will look into it later. I also found this book very useful when I first started doing serious database work. For a more thorough treatment of many of these issues, see An Introduction to Database Systems by Chris Date. The latter book is so full of detail that it is sometimes hard to follow, It certainly has an elaborate and well-thought-out system of ideas. As an empiricist, I find it crucially important that despite decades of whining about it, no one has come up with an actual *computer* system which implements this /gedankenexperiment/. Whether or not there will ever by an implementation of their definition of the RM, their ideas provide useful guidance in designing real-world databases today. The lack of an implementation of this RM is unfortunate, but there are many possible explanations for this lack other than faults in the theory itself. The theory _could_ be flawed, of course, but I have not seen sufficient evidence to support that conclusion. but it's worth the effort. Why? There are much more entertaining cranks out there if crank study is your thing. I'm not at all into crank study. I'm interested in basing design decisions on a solid foundation, grounded in logic. An Introduction to Database Systems is not a thorough exposition of relational theory, as you seem to imply, but an _introduction_ to database fundamentals and the application thereof to SQL-DBMSs. Were you thinking of The Third Manifesto by Date and Darwen? Though I've just gotten started with the book, he seems to be saying that modern RDBMSs aren't as faithful to relational theory as they ought to be, and that this has many *practical* consequences, e.g. lack of functionality. Given that PostgreSQL is open source, it seems a more likely candidate for addressing Pascal's concerns. At least the potential is there. Although some DBMSs have invented new ways to break the relational model, the fundamental problems are in SQL. Um, no. As I'll demonstrate below, it's the model that's broken and SQL that got it right. I'm unconvinced. See below. No DBMS based on SQL is going to be able to support RM correctly. Aha! I spy, with my little eye, a fanboy. I may have come across as over-enthusiastic late on Friday afternoon; I was attempting to counter over-enthusiastic claims in the other direction. I find your choice of words insulting, but I won't hold it against you. I appreciate Date and Pascal's work because it is well reasoned and grounded in mathematics and logic. I do not consider their work final in any way. They themselves are quick to acknowledge gaps in their understanding. I have not seen a better conceptual framework for thinking about databases. If I were presented with one, I would not cling to Date's or Pascal's views. You have to be a bit of a theory wonk to call Date's stuff RM. You seem to be implying here that Date's RM is somehow more desirable than what SQL actually provides. To be more desirable, I don't think it's unreasonable to say that it should be more powerful in some essential way. We were discussing Fabian Pascal's book. His book is based on his definition of RM, which is largely similar to (though not identical with) Date's. My above claim applies to RM as defined by Date or Pascal, not some other RM. If you could point me to alternate definitions of RM which are not in conflict with SQL, I would be curious to see them.; a better framework for thinking about SQL databases would be invaluable. In SQL, you can do this (this example condensed from Libkin's Expressive Power of SQL on the page above): SELECT (SELECT count(*) FROM table_1) (SELECT count(*) FROM table_2) AS Can't compare cardinalities in first order logic; Note the name of the output column. It's important and true, as you can verify if you care to do your homework on foundations of mathematics. Relational algebra is a subset of first-order logic http://en.wikipedia.org/wiki/Relational_algebra, and as a direct consequence, you can't do this simple but interesting thing with it. I must be missing something important. What aspect of the above query is supposedly impossible in relational algebra and/or relational calculus? What say we just stop right there and call Date's Relational Model what it is: a silly edifice built atop wrong premises. I'm unwilling to accept that claim without better evidence. Regards, --
Re: [GENERAL] Help with storing spatial (map coordinates) data?
We have had good success with postGIS for storing various spatial data sets (polygons, points and lines). They can be found at http://postgis.refractions.net/. We store our data in lat/long but postGIS has many different spatial reference systems defined and I would suspect that minutes/seconds exists. You may want to subscribe to and post your question on the postGIS mailing list. There are windows-ready compiled versions which seem to work well, although I've only played with them for prototypes (our real database servers are all linux so I can't be of any help on the Windoze front). In general support for this extension of postgres is quite helpful, so I would suggest asking on their general list. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of John Tregea Sent: Sun 6/11/2006 11:18 PM To: pgsql-general@postgresql.org Cc: Subject:[GENERAL] Help with storing spatial (map coordinates) data? Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating an X-Talk front end called Revolution for the GUI development and have only some general experience with SQL. Thanks in advance John Tregea ---(end of broadcast)--- TIP 6: explain analyze is your friend !DSPAM:448d0905111031804284693! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully implementing
Aaron Bingham wrote:
David Fetter wrote:
In SQL, you can do this (this example condensed from Libkin's
Expressive Power of SQL on the page above):
SELECT
(SELECT count(*) FROM table_1)
(SELECT count(*) FROM table_2)
AS Can't compare cardinalities in first order logic;
Note the name of the output column. It's important and true, as you
can verify if you care to do your homework on foundations of
mathematics. Relational algebra is a subset of first-order logic
http://en.wikipedia.org/wiki/Relational_algebra, and as a direct
consequence, you can't do this simple but interesting thing with it.
I must be missing something important. What aspect of the above query
is supposedly impossible in relational algebra and/or relational
calculus?
Having looked at this again, I now see that your statement above is
strictly correct, but misleading. Relational algebra consists of a
limited number of operators on relations. As such, relational algebra
says nothing about aggregate functions such as COUNT, or how to build a
relation from scaler values. Relational algebra is, however, only part
of the relational model as defined by Date, and Tutorial D includes all
the previsions we need to re-write the above query. The above query
could be expressed in Tutorial D more-or-less as follows (I'm not sure
if arbitrary strings are allowed as column names in Tutorial D, but
that's beside the point):
RELATION { TUPLE { Can't compare cardinalities in first order logic
(COUNT(table_1) COUNT(table_2)) } }
Placing the result of the comparison in a relation seems unnecessary,
but I have done so for equivalence to your example. Or did I miss the
point?
Regards,
--
Aaron Bingham
Senior Software Engineer
Cenix BioScience GmbH
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Best security practices for installing pgSQL with my software
Hi Harald, The program will have a database on the local PC, and be able to connect to the server database as well. Eventually they will synchronise the email, contacts etc. I need the database on the local PC so the user can take their PC/laptop home and still work. Do you think a direct connection to the database port using SSL will be suitable for this kind of scenario? From: Harald Armin Massa [mailto:[EMAIL PROTECTED] Sent: 12 June 2006 11:24 AM To: Greg Subject: Re: [GENERAL] Best security practices for installing pgSQL with my software Hello Greg! I will be doing a silent install of the database, and obviously this will require a service username and password. Should I hardcode the service username and password? Or should I let the person installing the software enter their own username and password for the server? Are you sure you want to do a silent install of a database on a server? That is, do you have THAT many servers that it would be justified? You are aware that you do NOT need to install PostgreSQL on a client to access the server? Only a very very very tiny library is enough for that (those three very are after installing Oracle Instant Client with 35MB) Can I use OpenSSL to secure communications between the clients and the server? Yes. Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Reinsburgstraße 202b 70197 Stuttgart 0173/9409607 - on different matter: did you ever dream of visiting CERN? The place where the antimatter for exploding Vatican is created? To eat in cantinas with the worlds highest propability to stand in queue with future or past Nobel Prize Winners? To talk about Web 2.5 at the place where Web 0.1 up to Web 1.0 were developed? register at www.europython.org!
Re: [GENERAL] Best security practices for installing pgSQL with my software
Greg, The program will have a database on the local PC, and be able to connect to the server database as well. Eventually they will synchronise the email, contacts etc.aaah. Like Lotus Notes. I need the database on the local PC so the user can take their PC/laptop home and still work.Yes, now it is clear. Do you think a direct connection to the database port using SSL will be suitable for this kind of scenario?I guess the connection between client and server should be the least of your concerns in this scenario :) Of course you can connect via SSL, that is explicitly supported by PostgreSQL. The challenges begin with the key infrastructure, the synchronization, the network, the ports, the installation of Databases on a lot of client computers in an automated fashion etc., the access rights et. all It took me around a year to get that working :); so better start now.Best wishes and good luck,Harald-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b 70197 Stuttgart0173/9409607-on different matter: did you ever dream of visiting CERN? The place where the antimatter for exploding Vatican is created? To eat in cantinaswith the worlds highest propability to stand in queue with future or past Nobel Prize Winners? To talk about Web 2.5 at the place where Web 0.1 up to Web 1.0 were developed? register at www.europython.org!
Re: [GENERAL] TOAST not working
On Sat, Jun 10, 2006 at 10:43:02PM -0400, Angus Berry wrote: Ahhh... thank you. This clarifies TOAST for me. I see how TOAST applies to individual columns that exceed the 8k page size. Postgres specs state it's possible to have 2GB rows and up to 1,600 columns. Can you tell me what data type would get to fill this spec. I wouldn't normally push to this limit, but I am expecting to have to defend Postgres specs. to a 3rd party. Any variable length datatype might be able to go to 2GB. Things like text, char, varchar and bytea are the obvious ones. Arrays too IIRC. However, as pointed out, even a toasted field takes about 20 bytes, which means you're limited to maybe 400 toasted fields. If you use integers you can get to 1600. Normally however, in cases where you need to store a lot of columns, what you really want is an array. You could easily store an array with a few million numerics in a single field... Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] are there static variables in pgsql?
In article [EMAIL PROTECTED], Yavuz Kavus [EMAIL PROTECTED] wrote: % i am writing a recursive procedure in pl/pgsql. % i need to check whether a condition is true in any step of recursive calls. [...] % i think i may achieve this with a static variable(shared among all calls). I'm not sure a static variable is the right way to achieve this, but you could use a custom_variable_class for this. Add this to your postgresql.conf: custom_variable_classes='global' Then you can set and show variables prefixed by global.: set global.success = 'true'; -- Patrick TJ McPhee North York Canada [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] delete seems to be getting blocked
Em Segunda 12 Junho 2006 04:24, surabhi.ahuja escreveu: hi, I am using postgresql 8.0.0. i have four tables in my database TAB1, has one primary key T1 TAB2 , has 2 fields, one is the primary ley T2 and the other one is the foreign key T1(from TAB1) TAB3 also has 2 fields, one is the primary ley T3 and the other is the foreign key T2(from TAB2) TAB4 has 2 fields again, primary key T4 and a foreign key T3(from TAB3) the disk is 100% full. i open psql datbase_name and do delete from TAB1 nothing seems to be happening for a long time, although when i do top, it shows postgres taking 99%. what is happening, cant delete be quickened? Good question. It can be made better if you have all correct indices. If you're missing some of them, and if you have a huge amount of data, then it might take a long time since for each data on your TAB1 it will have to search *all* data on the other tables to see if the value can be removed or not. To know exactly what is happening, try using strace / ltrace. You'll be able to see where PostgreSQL is spending time and what it is doing. -- Jorge Godoy [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] delete seems to be getting blocked
On Mon, Jun 12, 2006 at 12:54:15PM +0530, surabhi.ahuja wrote: i have four tables in my database TAB1, has one primary key T1 TAB2 , has 2 fields, one is the primary ley T2 and the other one is the foreign key T1(from TAB1) TAB3 also has 2 fields, one is the primary ley T3 and the other is the foreign key T2(from TAB2) TAB4 has 2 fields again, primary key T4 and a foreign key T3(from TAB3) Do the foreign key columns in TAB2, TAB3, and TAB4 have indexes? How many rows do the tables have? the disk is 100% full. i open psql datbase_name and do delete from TAB1 nothing seems to be happening for a long time, although when i do top, it shows postgres taking 99%. For each record you delete in TAB1 the database must search TAB2 to check for referential integrity violations or cascading operations (ON DELETE CASCADE, ON DELETE SET NULL, etc.). If the foreign key column in TAB2 doesn't have an index then each row deleted from TAB1 will result in a sequential scan on TAB2; likewise with TAB3 if you modify TAB2 and with TAB4 if you modify TAB3. If the tables are large then make sure you have indexes on the foreign key columns. If you create indexes then you might need to start a new session due to plan caching. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
RES: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully
What is real data for birthday - no birthday, n/a in date datatype representation ? For mysql is -00-00 and invalid date. For me is simple null, you have no data to put in the field. For me null is good in some situation, and bad in ohters. Just you have to think if you permit or not this value. Alejandro Michelin Salomon Porto Alegre Brasil ---Mensagem original- --De: [EMAIL PROTECTED] --[mailto:[EMAIL PROTECTED] Em nome de A.M. --Enviada em: sexta-feira, 9 de junho de 2006 13:01 --Para: Postgres general mailing list --Assunto: Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully -- -- --On Fri, June 9, 2006 11:45 am, David Fetter wrote: -- On Fri, Jun 09, 2006 at 05:20:46PM +0200, Martijn van Oosterhout -- wrote: -- -- On Fri, Jun 09, 2006 at 07:09:12AM -0400, Agent M wrote: -- -- Well, the Date argument against NULLs (and he never --endorsed them, -- or so he claims) is that they are not data- they represent the -- absence of data- so why put non-data in a _data_base. -- -- At this point you could start a whole philosophical --discussion about -- whether knowing you don't know something is a fact worth storing. -- -- And to me, the answer is an unqualified yes. A state of --ignorance -- is an important piece of information by itself. -- -- For example, that I don't know someone's birthdate is --important. When -- I'm trying to figure out when to send a birthday card, --knowing that I -- don't know this piece of information means that I take a different -- action decide whether to try to find out what the --birthdate is. from -- the action I would take if I didn't know that I don't know the -- birthdate, which is rummage through all my records trying --to find the -- birthdate. -- --So you should normalize and add relations to represent the --state adequately. NULL doesn't give you enough information --anyway- does NULL in a birthday header mean no birthday, --n/a (a business doesn't have a birthday), not born yet, --etc... Using real data, you can represent any of these states. -- -- -(end of --broadcast)--- --TIP 4: Have you searched our list archives? -- -- http://archives.postgresql.org -- -- --No virus found in this incoming message. --Checked by AVG Free Edition. --Version: 7.1.394 / Virus Database: 268.8.3/359 - Release --Date: 8/6/2006 -- -- --No virus found in this incoming message. --Checked by AVG Free Edition. --Version: 7.1.394 / Virus Database: 268.8.3/359 - Release --Date: 8/6/2006 -- -- -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.8.3/360 - Release Date: 9/6/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.8.3/360 - Release Date: 9/6/2006 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] are there static variables in pgsql?
Patrick TJ McPhee wrote: I'm not sure a static variable is the right way to achieve this, but you could use a custom_variable_class for this. Add this to your postgresql.conf: custom_variable_classes='global' Then you can set and show variables prefixed by global.: set global.success = 'true'; This is very intriguing, but I'd like to make sure it is doing what I think it is doing. Is it tracking variables in a connection across SQL commands? If so, shouldn't this work (Assume the existence of table detail with a column called val2). create or replace function SetVal2() returns void as $$ begin update detail set val2=global.val2; end; $$ language plpgsql set global.val2=5; select SetVal2(); I'm getting the following error which I don't understand. Seems I don't know how to access the variable once it is set. ERROR: missing FROM-clause entry for table global CONTEXT: SQL statement update detail set val2=global.val2 PL/pgSQL function setval2 line 2 at SQL statement begin:vcard fn:Kenneth Downs n:Downs;Kenneth email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE version:2.1 end:vcard ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Disk corruption detection
At 07:42 PM 6/11/2006 +0200, Florian Weimer wrote: We recently had a partially failed disk in a RAID-1 configuration which did not perform a write operation as requested. Consequently, What RAID1 config/hardware/software was this? Could be good to know... Regards, Link. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] are there static variables in pgsql?
Kenneth Downs wrote:
OK, cancel the question, the answer is
SELECT current_setting('global.val2');
This is very intriguing, but I'd like to make sure it is doing what I
think it is doing. Is it tracking variables in a connection across
SQL commands? If so, shouldn't this work (Assume the existence of
table detail with a column called val2).
create or replace function SetVal2() returns void as
$$
begin
update detail set val2=global.val2;
end;
$$
language plpgsql
set global.val2=5;
select SetVal2();
I'm getting the following error which I don't understand. Seems I
don't know how to access the variable once it is set.
ERROR: missing FROM-clause entry for table global
CONTEXT: SQL statement update detail set val2=global.val2
PL/pgSQL function setval2 line 2 at SQL statement
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
begin:vcard
fn:Kenneth Downs
n:Downs;Kenneth
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
On Mon, Jun 12, 2006 at 10:01:43AM +0800, Qingqing Zhou wrote: John Sidney-Woollett [EMAIL PROTECTED] wrote It looks like the db is using them at the rate of 1.5 million per day. At what value will I hit a wraparound, and what options do I have to identify/fix the (impending) problem. The Oid will wraparound when it reaches the 32bits unsigned integer limit. If you don't use the oid explicitely in your application, then you don't worry about it. Except IIRC the OP is running 7.4 which doesn't have checks in DDL code to deal with OID collisions. :( -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(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: [GENERAL] Disk corruption detection
On Sun, Jun 11, 2006 at 07:42:55PM +0200, Florian Weimer wrote: We recently had a partially failed disk in a RAID-1 configuration which did not perform a write operation as requested. Consequently, the mirrored disks had different contents, and the file which contained the block switched randomly between two copies, depending on which disk had been read. (In theory, it is possible to read always from both disks, but this is not what RAID-1 configurations normally do.) Actually, every RAID1 I've ever used will read from both to try and balance out the load. Anyway, how would be the chances for PostgreSQL to detect such a corruption on a heap or index data file? It's typically hard to detect this at the application level, so I don't expect wonders. I'm just curious if using PostgreSQL would have helped to catch this sooner. I know that WAL pages are (or at least were) CRC'd, because there was extensive discussion around 32 bit vs 64 bit CRCs. There is no such check for data pages, although PostgreSQL has other ways to detect errors. But in a nutshell, if you care about your data, buy hardware you can trust. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
Jim C. Nasby wrote: Except IIRC the OP is running 7.4 which doesn't have checks in DDL code to deal with OID collisions. :( This is not good news! :( What about other long runing 7.4.x DBs? Do you really have to dump, init and restore every once in a while? Also, do you know what is actually using the OIDs - transactions? John ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
On Mon, Jun 12, 2006 at 05:02:09PM +0100, John Sidney-Woollett wrote: Jim C. Nasby wrote: Except IIRC the OP is running 7.4 which doesn't have checks in DDL code to deal with OID collisions. :( This is not good news! :( What about other long runing 7.4.x DBs? Do you really have to dump, init and restore every once in a while? Well, you have to be using a lot of OIDs for this to be an issue. At your stated rate of 1.5 million OIDs per day it will take just under eight years before you wraparound. That's a lot of OIDs and most databases don't get anywhere near that many, which is why it's not a big deal for most people... Also, do you know what is actually using the OIDs - transactions? Inserting new rows into a table somewhere that has OIDs. Just using transactions won't do it. Note, some system catalogs use oids, so some DDL statements can do it. This gives you a list of tables that use OIDs. Maybe it can help you track down the problem. select attrelid::regclass from pg_attribute inner join pg_class on (attrelid = oid) where attname = 'oid' and relkind = 'r'; Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
On Mon, Jun 12, 2006 at 05:02:09PM +0100, John Sidney-Woollett wrote: Jim C. Nasby wrote: Except IIRC the OP is running 7.4 which doesn't have checks in DDL code to deal with OID collisions. :( This is not good news! :( What about other long runing 7.4.x DBs? Do you really have to dump, init and restore every once in a while? Also, do you know what is actually using the OIDs - transactions? Since you're running Slony, I suspect it's using them somehow. Or maybe it doesn't create it's tables WITHOUT OIDs. Also note that any time you create an object you burn through an OID. Probably your best bet is to just upgrade to 8.1, which will gracefully handle OID collisions. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
Martijn van Oosterhout kleptog@svana.org writes: Well, you have to be using a lot of OIDs for this to be an issue. At your stated rate of 1.5 million OIDs per day it will take just under eight years before you wraparound. That's a lot of OIDs and most databases don't get anywhere near that many, which is why it's not a big deal for most people... It should also be pointed out that OID wraparound is not a fatal condition. Pre-8.1 you might get occasional query failures due to trying to insert duplicate OIDs, but that's about it. This gives you a list of tables that use OIDs. Maybe it can help you track down the problem. Look at pg_class.relhasoids --- easier, and more reliable. regards, tom lane ---(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: [GENERAL] Disk corruption detection
* Lincoln Yeoh: At 07:42 PM 6/11/2006 +0200, Florian Weimer wrote: We recently had a partially failed disk in a RAID-1 configuration which did not perform a write operation as requested. Consequently, What RAID1 config/hardware/software was this? I would expect that any RAID-1 controller works in this mode by default. It's an analogy to RAID-5: In that case, you clearly can't verify the parity bits on read for performance reasons. So why do it for RAID-1? (If there is a controller which offers compare-on-read for RAID-1, I would like to know it's name. 8-) ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Fabian Pascal and RDBMS deficiencies in fully
* Roy Souther: In what way could a database like PostgreSQL not be faithful to relational theory? Does he give any explanation as to what that means? My guess: In SQL (and in PostgreSQL as a result), relations aren't sets, aren't first-class, and the underlying logic is not Boolean. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Disk corruption detection
* Jim C. Nasby: Anyway, how would be the chances for PostgreSQL to detect such a corruption on a heap or index data file? It's typically hard to detect this at the application level, so I don't expect wonders. I'm just curious if using PostgreSQL would have helped to catch this sooner. I know that WAL pages are (or at least were) CRC'd, because there was extensive discussion around 32 bit vs 64 bit CRCs. CRCs wouldn't help because the out-of-date copy has got a correct CRC. That's why it's so hard to detect this problem at the application level. Putting redundancy into rows doesn't help, for instance. There is no such check for data pages, although PostgreSQL has other ways to detect errors. But in a nutshell, if you care about your data, buy hardware you can trust. All hardware can fail. 8-/ AFAIK, compare-on-read is the recommend measure to compensate for this kind of failure. (The traditional recommendation also includes three disks, so that you've got a tie-breaker.) It seems to me that PostgreSQL's MVCC-related don't directly overwrite data rows policy might help to expose this sooner than with direct B-tree updates. In this particular case, we would have avoided the failure if we properly monitored the disk subsystem (the failure was gradual). Fortunately, it was just a test system, but it got me woried a bit. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
We'll probably upgrade to 8.1.x before we hit the wraparound problem! :) Hmm, looks like slony uses OIDs... And I found a couple of my own tables which were incorrectly created with OIDs. select relname, relnamespace, reltype from pg_catalog.pg_class where relhasoids=true; relname | relnamespace | reltype +--+-- pg_attrdef | 11 |16385 pg_constraint | 11 |16387 pg_database| 11 | 88 pg_proc| 11 | 81 pg_rewrite | 11 |16411 pg_type| 11 | 71 pg_class | 11 | 83 pg_operator| 11 |16393 pg_opclass | 11 |16395 pg_am | 11 |16397 pg_language| 11 |16403 pg_trigger | 11 |16413 pg_cast| 11 |16419 pg_namespace | 11 |16596 pg_conversion | 11 |16598 sturllog |18161 |18519 -- MINE stsession |18161 |18504 sl_trigger | 82061042 | 82061126 -- SLONY sl_table | 82061042 | 82061113 sl_nodelock| 82061042 | 82061082 sl_setsync | 82061042 | 82061098 sl_sequence| 82061042 | 82061134 sl_node| 82061042 | 82061073 sl_listen | 82061042 | 82061162 sl_path| 82061042 | 82061147 sl_subscribe | 82061042 | 82061174 sl_set | 82061042 | 82061087 sl_event | 82061042 | 82061186 sl_confirm | 82061042 | 82061193 sl_seqlog | 82061042 | 82061198 sl_log_1 | 82061042 | 82061202 sl_log_2 | 82061042 | 82061209 sl_config_lock | 82061042 | 82061229 Thanks John Tom Lane wrote: Martijn van Oosterhout kleptog@svana.org writes: Well, you have to be using a lot of OIDs for this to be an issue. At your stated rate of 1.5 million OIDs per day it will take just under eight years before you wraparound. That's a lot of OIDs and most databases don't get anywhere near that many, which is why it's not a big deal for most people... It should also be pointed out that OID wraparound is not a fatal condition. Pre-8.1 you might get occasional query failures due to trying to insert duplicate OIDs, but that's about it. This gives you a list of tables that use OIDs. Maybe it can help you track down the problem. Look at pg_class.relhasoids --- easier, and more reliable. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
Just a quick thought - I know that I don't fully understand tables with oids, and table without oids, is there a link to some more information about why you need oids, or why you don't that I could reference as I'm a bit lost on the subject of oids Alex.On 6/12/06, Tom Lane [EMAIL PROTECTED] wrote: Martijn van Oosterhout kleptog@svana.org writes: Well, you have to be using a lot of OIDs for this to be an issue. At your stated rate of 1.5 million OIDs per day it will take just under eight years before you wraparound. That's a lot of OIDs and most databases don't get anywhere near that many, which is why it's not a big deal for most people...It should also be pointed out that OID wraparound is not a fatal condition.Pre-8.1 you might get occasional query failures due totrying to insert duplicate OIDs, but that's about it. This gives you a list of tables that use OIDs. Maybe it can help you track down the problem. Look at pg_class.relhasoids --- easier, and more reliable.regards, tom lane---(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: [GENERAL] Ever increasing OIDs - gonna run out soon?
Slony does appear to use OIDs. John Jim C. Nasby wrote: On Mon, Jun 12, 2006 at 05:02:09PM +0100, John Sidney-Woollett wrote: Jim C. Nasby wrote: Except IIRC the OP is running 7.4 which doesn't have checks in DDL code to deal with OID collisions. :( This is not good news! :( What about other long runing 7.4.x DBs? Do you really have to dump, init and restore every once in a while? Also, do you know what is actually using the OIDs - transactions? Since you're running Slony, I suspect it's using them somehow. Or maybe it doesn't create it's tables WITHOUT OIDs. Also note that any time you create an object you burn through an OID. Probably your best bet is to just upgrade to 8.1, which will gracefully handle OID collisions. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
On 6/12/06, Alex Turner [EMAIL PROTECTED] wrote: Just a quick thought - I know that I don't fully understand tables with oids, and table without oids, is there a link to some more information about why you need oids, or why you don't that I could reference as I'm a bit lost on the subject of oids dont get lost, just forget you ever heard about them :). oid is a 'free' userland autoincrement counter which has some problems. It was a hidden column that in older versions of postgresql was implicitly added to your tables. newer versions of pg assume you dont want OIDs on your table. (system tables still use them, tho). for purposes of a global counter or table level ID generator, sequences are basically better in every way. use them. some middleware such as the odbc driver used to work better/easier if you had a column but afaik this is not the case anymore. Merlin ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Ever increasing OIDs - gonna run out soon?
On Mon, Jun 12, 2006 at 03:26:56PM -0400, Alex Turner wrote: Just a quick thought - I know that I don't fully understand tables with oids, and table without oids, is there a link to some more information about why you need oids, or why you don't that I could reference as I'm a bit lost on the subject of oids http://www.postgresql.org/docs/faqs.FAQ.html#item4.12 Basically, it's best if you just don't use them. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Disk corruption detection
On Mon, Jun 12, 2006 at 07:55:22PM +0200, Florian Weimer wrote: * Jim C. Nasby: Anyway, how would be the chances for PostgreSQL to detect such a corruption on a heap or index data file? It's typically hard to detect this at the application level, so I don't expect wonders. I'm just curious if using PostgreSQL would have helped to catch this sooner. I know that WAL pages are (or at least were) CRC'd, because there was extensive discussion around 32 bit vs 64 bit CRCs. CRCs wouldn't help because the out-of-date copy has got a correct CRC. That's why it's so hard to detect this problem at the application level. Putting redundancy into rows doesn't help, for instance. There is no such check for data pages, although PostgreSQL has other ways to detect errors. But in a nutshell, if you care about your data, buy hardware you can trust. All hardware can fail. 8-/ I'd argue that any raid controller that carries on without degrading the array even though it's getting write errors isn't worth the fiberglass the components are soldered to. Same thing if it's a HD that can't write something and doesn't throw an error back up the chain. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Partitioning...
Hi listers, I am trying to learn PG partioning (constaraint exclustion). I have created pretty simple table (all the code is below), but when I try to populate The table with data, the RULE system is not working as expected (e.g. as I have expected). The code: --- CREATE TABLE part ( id1int not null, id2int not null, filler varchar(200) ); create table part_id1_0_10 ( CHECK ( id1= 0 and id1=10) ) INHERITS (part); create table part_id1_11_20 ( CHECK ( id1=11 and id1=20) ) INHERITS (part); CREATE INDEX idx_part_id1_0_10 ON part_id1_0_10(id1); CREATE INDEX idx_part_id1_11_20 ON part_id1_11_20(id1); CREATE RULE part_id1_0_10_insert AS ON INSERT TO part WHERE ( id1= 0 and id1=10 ) DO INSTEAD INSERT INTO part_id1_0_10 VALUES ( NEW.id1, NEW.id2, NEW.filler); CREATE RULE part_id1_11_20_insert AS ON INSERT TO part WHERE ( id1=11 and id1=20 ) DO INSTEAD INSERT INTO part_id1_11_20 VALUES ( NEW.id1, NEW.id2, NEW.filler); analyze part_id1_0_10 ; analyze part_id1_11_20 ; CREATE VIEW part_all AS select * from part_id1_0_10 UNION ALL select * from part_id1_11_20 ; postgres=# \d+ part Table public.part Column | Type | Modifiers | Description ++---+- id1| integer| not null | id2| integer| not null | filler | character varying(200) | | Rules: part_id1_0_10_insert AS ON INSERT TO part WHERE new.id1 = 0 AND new.id1 = 10 DO INSTEAD INSERT INTO part_id1_0_10 (id1, id2, filler) VALUES (new.id1, new.id2, new.filler) part_id1_11_20_insert AS ON INSERT TO part WHERE new.id1 = 11 AND new.id1 = 20 DO INSTEAD INSERT INTO part_id1_11_20 (id1, id2, filler) VALUES (new.id1, new.id2, new.filler) Has OIDs: no --- When I try : insert into part(id1, id2, filler) select round( (random()*10)::bigint,0) as id1, round( (random()*20)::bigint,0) as id2, 'TTTESTZZZ' from generate_series(0,10); All the data is redirected to part_id1_0_10 (as expected). But When I issue: insert into part(id1, id2, filler) select round( (random()*20)::bigint,0) as id1, ---!!! Note that both partitions should be populated! round( (random()*20)::bigint,0) as id2, 'TTTESTZZZ' from generate_series(0,10); I am getting : ERROR: new row for relation part_id1_0_10 violates check constraint part_id1_0_10_id1_check How to fix the problem ? I thought that the rules were enough to redirect to records to The right partions. Should I use triggers instead. The documentation is saying that can use either Rules OR triggers: http://www.enterprisedb.com/documentation/ddl-partitioning.html Point 5 in 4.10.2. Implementing Partitioning. Any suggestions ? Many thanks in advance Milen ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Partitioning...
Milen Kulev [EMAIL PROTECTED] writes: But When I issue: insert into part(id1, id2, filler) select round( (random()*20)::bigint,0) as id1, ---!!! Note that both partitions should be populated! round( (random()*20)::bigint,0) as id2, 'TTTESTZZZ' from generate_series(0,10); ERROR: new row for relation part_id1_0_10 violates check constraint part_id1_0_10_id1_check Don't use random() in your test case. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Partitioning...
Thanks for the prompt reply Tom, What is wrong with random() ? The following snipped is working ... insert into part(id1, id2, filler) select 11 + round( (random()*9)::bigint,0) as id1, --- 11-20 range for id1 , as of definition round( (random()*20)::bigint,0) as id2, 'TTTESTZZZ' from generate_series(0,10); Regards. Milen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, June 13, 2006 12:18 AM To: Milen Kulev Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Partitioning... Milen Kulev [EMAIL PROTECTED] writes: But When I issue: insert into part(id1, id2, filler) select round( (random()*20)::bigint,0) as id1, ---!!! Note that both partitions should be populated! round( (random()*20)::bigint,0) as id2, 'TTTESTZZZ' from generate_series(0,10); ERROR: new row for relation part_id1_0_10 violates check constraint part_id1_0_10_id1_check Don't use random() in your test case. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Partitioning...
BTW , I can not see the planner to prune/isolate the right table/partion ? (constraint_exclusion=on, Version= 8.1.3 on RHEL4) postgres=# explain analyze select count(id1) from part_all where id1 =12 ; QUERY PLAN --- Aggregate (cost=5951.74..5951.75 rows=1 width=4) (actual time=111.687..111.688 rows=1 loops=1) - Append (cost=164.60..5463.19 rows=39084 width=49) (actual time=4.764..91.488 rows=13203 loops=1) - Bitmap Heap Scan on part_id1_0_10 (cost=164.60..3577.60 rows=25600 width=49) (actual time=0.123..0.123 rows=0 loops=1) Recheck Cond: (id1 = 12) - Bitmap Index Scan on idx_part_id1_0_10 (cost=0.00..164.60 rows=25600 width=0) (actual time=0.119..0.119 rows=0 loops=1) Index Cond: (id1 = 12) - Bitmap Heap Scan on part_id1_11_20 (cost=88.19..1494.74 rows=13484 width=49) (actual time=4.635..55.140 rows=13203 loops=1) Recheck Cond: (id1 = 12) - Bitmap Index Scan on idx_part_id1_11_20 (cost=0.00..88.19 rows=13484 width=0) (actual time=4.067..4.067 rows=13205 loops=1) Index Cond: (id1 = 12) Total runtime: 111.812 ms (11 rows) Time: 114.779 ms Regards. Milen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, June 13, 2006 12:18 AM To: Milen Kulev Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Partitioning... Milen Kulev [EMAIL PROTECTED] writes: But When I issue: insert into part(id1, id2, filler) select round( (random()*20)::bigint,0) as id1, ---!!! Note that both partitions should be populated! round( (random()*20)::bigint,0) as id2, 'TTTESTZZZ' from generate_series(0,10); ERROR: new row for relation part_id1_0_10 violates check constraint part_id1_0_10_id1_check Don't use random() in your test case. regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Partitioning...
Milen Kulev [EMAIL PROTECTED] writes: What is wrong with random() ? Not guaranteed to be stable across the multiple evaluations that the rule will perform ... remember a rule is a macro and has the usual multiple-evaluation gotchas in the face of volatile arguments. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Partitioning...
Aha ! Obviosly that is the reason for working sometimes properly ans sometimes not ... Thanks ! Regards. Milen -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Tom Lane Sent: Tuesday, June 13, 2006 12:58 AM To: Milen Kulev Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Partitioning... Milen Kulev [EMAIL PROTECTED] writes: What is wrong with random() ? Not guaranteed to be stable across the multiple evaluations that the rule will perform ... remember a rule is a macro and has the usual multiple-evaluation gotchas in the face of volatile arguments. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] How can I retrieve a function result?
Hi pgsql community,
I'm using libpq C. I'm trying to execute a FUNCTION called myFunction,
*
PGResult *res;
res = PQexecParams ( conn, select myFunction($1,$2,$3) , 3, NULL, paramValues, paraLenghts, paramFormats, resultFormat);
if ( PQresultStatus (res) != PGRES_TUPLES_OK)
{
//error
}else{
//ok
}
*
It's works fine, however I dont know how can I retrieve the result that return the FUNCTIONmyFunction. myFunction is a postgres FUNCTION which returns a INT.
Any advices??
thanks in advance.
Luis.
-- paz, amor y comprensión(1967-1994)
Re: [GENERAL] How can I retrieve a function result?
Luis, On Mon, Jun 12, 2006 at 06:24:24PM -0500, Luis Alberto Pérez Paz wrote: res = PQexecParams ( conn, select myFunction($1,$2,$3) , 3, NULL, paramValues, paraLenghts, paramFormats, resultFormat); It's works fine, however I dont know how can I retrieve the result that return the FUNCTION myFunction. myFunction is a postgres FUNCTION which returns a INT. libpq functions for retrieving query results are described here: http://www.postgresql.org/docs/8.1/static/libpq-exec.html#LIBPQ-EXEC-SELECT-INFO Have you tried them and do you have a special problem with one of those? If so, please give more detail. Getting the result of a function does not differ from getting the result of a regular select of a table or view. Joachim ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Hi Brent, I will look at postGIS today. I will try and keep the whole GIS functionality as a separate schema to avoid confusing myself, so a postGIS may be exactly what I am looking for. Thanks and regards John Brent Wood wrote: On Mon, 12 Jun 2006, John Tregea wrote: Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating an X-Talk front end called Revolution for the GUI development and have only some general experience with SQL. I stongly suggest you do not use tne native Postgres geometry capability, but install PostGIS and use this instead. See www.postgis.org Brent Wood ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Thanks Greg, I don't know in practice if I will need the minutes and seconds, as you say degrees with decimal information is probably more accurate. If I store degrees in decimal I will need to convert back and forth though as people will use GPS to enter lat and long into the system. I need to be able to work out route lengths for various transport modes as well as integrating (in the future) with GIS mapping software such as ESRi or MapInfo. I have subscribed to the Open Geospatial Consortium and downloaded their common architecture which includes an SQL model... But it is a lot to take in, so any guidance is appreciated. Thanks and regards John Gregory S. Williamson wrote: We have had good success with postGIS for storing various spatial data sets (polygons, points and lines). They can be found at http://postgis.refractions.net/. We store our data in lat/long but postGIS has many different spatial reference systems defined and I would suspect that minutes/seconds exists. You may want to subscribe to and post your question on the postGIS mailing list. There are windows-ready compiled versions which seem to work well, although I've only played with them for prototypes (our real database servers are all linux so I can't be of any help on the Windoze front). In general support for this extension of postgres is quite helpful, so I would suggest asking on their general list. HTH, Greg Williamson DBA GlobeXplorer LLC -Original Message- From: [EMAIL PROTECTED] on behalf of John Tregea Sent: Sun 6/11/2006 11:18 PM To: pgsql-general@postgresql.org Cc: Subject:[GENERAL] Help with storing spatial (map coordinates) data? Hi, I have recently switched to PostgreSQL and had no problem bringing our existing (my)SQL databases and data into the environment. I am now extending the functionality of our databases and want to start storing spatial information. The information is made up of latitude and longitude coordinates that define a point or location on the earth's surface. e.g. degrees, minutes and seconds north/south and degrees, minutes and seconds east/west. I have read up on custom data types (with input and output functions) in the docs but am not sure if that is the best way to go. Can anyone point me to a simple, workable implementation of storing and managing this type of data or advise me on how to structure a series of fields that could combine to the required string? I am running postgreSQL 8.1.4 under WinXP Pro and currently evaluating an X-Talk front end called Revolution for the GUI development and have only some general experience with SQL. Thanks in advance John Tregea ---(end of broadcast)--- TIP 6: explain analyze is your friend !DSPAM:448d0905111031804284693! ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Aggregate functions not allowed in WHERE clause
I have two tables, Claims and Logs, and I need to fish in for the id of any claim who have into the logs anything into the fields invoices or payments I think the best way to do this is by mean of: SELECT claim_id FROM logs WHERE ( sum(logs.invoices) 0 OR sum(logs.payments) 0 ) GROUP BY claim_id But Postgres claims Aggregate functions not allowed in WHERE clause Anyone could help me to figure out this task please Cordially -- @..@ Ricardo Naranjo FacciniTel: (1) 257-9832 () Ingeniero CivilCalle 95 #30-61 int 8 ( __ )M.Sc. Ing. de Sistemas y Comp. Barrio La Castellana ^^ ~~ ^^[EMAIL PROTECTED] Bogotá D.C. SKINAColombia, S.A. IT Solutions http://www.skinait.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Aggregate functions not allowed in WHERE clause
On Mon, Jun 12, 2006 at 09:00:33PM -0500, Ricardo Naranjo Faccini wrote: SELECT claim_id FROM logs WHERE ( sum(logs.invoices) 0 OR sum(logs.payments) 0 ) GROUP BY claim_id But Postgres claims Aggregate functions not allowed in WHERE clause I think you're looking for HAVING. Does the following do what you want? SELECT claim_id FROM logs GROUP BY claim_id HAVING sum(invoices) 0 OR sum(payments) 0; -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Aggregate functions not allowed in WHERE clause
Quoth [EMAIL PROTECTED] (Ricardo Naranjo Faccini): I have two tables, Claims and Logs, and I need to fish in for the id of any claim who have into the logs anything into the fields invoices or payments I think the best way to do this is by mean of: SELECT claim_id FROM logs WHERE ( sum(logs.invoices) 0 OR sum(logs.payments) 0 ) GROUP BY claim_id But Postgres claims Aggregate functions not allowed in WHERE clause Anyone could help me to figure out this task please You might consider using a HAVING clause to add those constraints at the grouping level... select claim_id from logs group by claim_id having sum(logs.invoices) 0 or sum(logs.payments) 0; You might need to have those sums in the outer select... -- (reverse (concatenate 'string ofni.secnanifxunil @ enworbbc)) http://linuxfinances.info/info/finances.html Microsoft has world class quality control -- Arthur Norman ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Thanks Brent, I will be cautious in my approach. The public schema is the place that I wanted to use to store the geometry attributes, so from your points, that sounds like the best place. The other schemas contain controlled (security) information in proprietary data structures so I that was my reluctance to modify those tables with the necessary geometry functions, types etc. Regards John Brent Wood wrote: On Tue, 13 Jun 2006, John Tregea wrote: Hi Brent, I will look at postGIS today. I will try and keep the whole GIS functionality as a separate schema to avoid confusing myself, so a postGIS may be exactly what I am looking for. Ummm... one caution: The lovely side effect, apart from all the SQL functions to query analyse spatial data in Postgres, is that any table with a properly created geometry attribute is automatically available as a GIS layer in a GIS map window, using GIS applications like QGIS, mezoGIS, JUMP uDIG (even ArcInfo via the PostGIS SDE), or to a less well integrated extent, GRASS. It can also be a layer in a web map server application using something like UMN mapserver. However, not all of these support the concept of schema's, so only tables in the public schema may be able to be plotted/mapped. Also, from a data modelling perspective, a geometry attribute is not inherently different to a numeric, int, varchar or text attribute, so unless there is some other reason to divide entities with geometries into a separate schema frpom those without, I'm not sure it is good practice. Cheers, Brent ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ECPG and Curors.
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
I'm looking at the documentation for Postgresql in Chapter 30 and I'm
checking out how to use FETCH INTO and CURSORs to loop through multiple
results from a table.
In the documentation they show something like
EXEC SQL DECLARE foo CURSOR for select a,b,c from test_table
do {
EXEC SQL FETCH NEXT FROM foo INTO
} while ( ... );
what I don't see is how to detect that I've fetched the last row from a
query. Is there more complete doco on this process somewhere?
- --
Peter L. Berghold [EMAIL PROTECTED]
Those who fail to learn from history are condemned to repeat it.
AIM: redcowdawgYahoo IM: blue_cowdawg ICQ: 11455958
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.6 (GNU/Linux)
Comment: Using GnuPG with Red Hat - http://enigmail.mozdev.org
iD8DBQFEji2QUM9/01RIhaARAqV6AJ43/F6y5sKbvY837dVwNL8ZPz0MxgCeIJlL
5Fo3FyR3e5Aup53s/z0UrxY=
=RLxy
-END PGP SIGNATURE-
begin:vcard
fn:Peter L. Berghold
n:Berghold;Peter
org:IBM;GSD
email;internet:[EMAIL PROTECTED]
title:Unix Specialist
tel;work:(732) 918-1487
tel;fax:(732) 982-1632
x-mozilla-html:FALSE
url:http://www.berghold.net
version:2.1
end:vcard
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
Re: [GENERAL] Help with storing spatial (map coordinates) data?
Hi Brent, Excellent advice, thanks for taking the time with what must be a fairly newbie question in GIS terms. I appreciate your help. Cheers John Brent Wood wrote: On Tue, 13 Jun 2006, John Tregea wrote: Thanks Brent, I will be cautious in my approach. The public schema is the place that I wanted to use to store the geometry attributes, so from your points, that sounds like the best place. The other schemas contain controlled (security) information in proprietary data structures so I that was my reluctance to modify those tables with the necessary geometry functions, types etc. Sounds eminently sensible :-) One point you might note, the AddGeometryColumn() function does two things. It adds a geometry column of the appropriate projection type to the specified table. It also writes a metadata record to the geometry_columns table. This is where many application look to find tables with geometries. If you create a view on a table with a geometry column, or create a table with a geometry column without using the AddGeometryColumn() function (eg: create table foo1 as select * from foo0;), then some applications will not recognise the table or view as a GIS table. If you are adding geometries to tables via views, which it sounds like you may be doing, you may need to manually insert the appropriate data into the geometry_columns table to be fully compliant with the OGC specs PostGIS implementation. If you create such a geometry table or view the GIS package you are using fails to make it available as a data source, this is almost certainly why :-) Cheers, Brent ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Aggregate functions not allowed in WHERE clause
i think this query can be rewritten as SELECT claim_id,sum(invoices),sum(payments) FROM logs GROUP BY claim_id HAVING sum(invoices) 0 OR sum(payments) 0; having clause can be used with aggregate functions but those functions should be the part of column list/expression list in the SELECT statement. pradeep --- Michael Fuhr [EMAIL PROTECTED] wrote: On Mon, Jun 12, 2006 at 09:00:33PM -0500, Ricardo Naranjo Faccini wrote: SELECT claim_id FROM logs WHERE ( sum(logs.invoices) 0 OR sum(logs.payments) 0 ) GROUP BY claim_id But Postgres claims Aggregate functions not allowed in WHERE clause I think you're looking for HAVING. Does the following do what you want? SELECT claim_id FROM logs GROUP BY claim_id HAVING sum(invoices) 0 OR sum(payments) 0; -- Michael Fuhr ---(end of broadcast)--- TIP 6: explain analyze is your friend __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
