Re: [GENERAL] ECPG and Curors.

On Mon, Jun 12, 2006 at 11:14:24PM -0400, Peter L. Berghold wrote: > 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? Just look for "exec sql whenever not found ..." Michael -- Michael Meskes Email: Michael

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

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 plac

[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

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

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 > WHER

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 look

[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 ) GR

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 t

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 rece

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 FUNCT

[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,

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

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.

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 --

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, 'TTTE

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, > 'TTTESTZZ

[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: ---

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

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 t

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 oi

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 o

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]> wrot

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

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 >> so

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. --

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 contro

Re: [GENERAL] Ever increasing OIDs - gonna run out soon?

Martijn van Oosterhout 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 wh

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

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

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 actua

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 r

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 (impendin

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 ex

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. --

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';

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 val

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

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

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

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

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

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 thi

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

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 . We store our data in lat/long but postGIS has many different spatial reference systems defined and I would suspect that minutes/secon

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 th

[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

[GENERAL] test

 

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 wi

[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)