[GENERAL] Problem with UPDATE and UNIQUE

Hi all I have a problem, which I suspect stems from bad design. If I explain what I am doing, perhaps someone can suggest a better approach. I want to store data in a 'tree' form, with a fixed number of levels, so that each level has a defined role. I have the following (simplified) table - CR

Re: [GENERAL] Converting non-null unique idx to pkey

"Ed L." <[EMAIL PROTECTED]> writes: > Are there > are any known or obvious gotchas associated with transforming a > unique index on a non null column into a primary key via this > sql? > update pg_index > set indisprimary = 't' > where indexrelid = The problem with that is there won't be an

Re: [GENERAL] history table

On 8/21/07, Guy Rouillier <[EMAIL PROTECTED]> wrote: > Well, I can't find a way to set a variable associated with a connection, > so probably the easiest thing to do is to add an "updated_by" column to > your regular table (i.e., the non-history version.) Then just include > the userid from your w

[GENERAL] Pgcluster 1.7 Fail safe !!!

Hi All , I have successfully installed pgcluster 1.7 rc7 and it is working fine . But i have got a doubt on " Single Point Failure " . I need a suggest from u all on this . For an Application which demands High Availability and no Single Point Failure and built Multi-Master Replication syst

[GENERAL] raw data into table process

I am trying to record the following entries into a table. I'm curious to know if there's an efficient/effective way of doing this? This entries come from an ancient datalogger (note: separated by space and uses YY/MM/DD format to record date) Plain file sample.dat 3665 OK BS 07/08/1

Re: [GENERAL] Converting non-null unique idx to pkey

On 8/21/07, Ed L. <[EMAIL PROTECTED]> wrote: > On Tuesday 21 August 2007 1:45 pm, Scott Marlowe wrote: > > If you have a large db in 7.4.6, you should do two things. > > > > 1: Update to 7.4.19 or whatever the latest flavor of 7.4 is, > > right now. There are a few known data eating bugs in 7.4.6.

Re: [GENERAL] Pgcluster 1.7 Fail safe !!!

On 8/21/07, Trinath Somanchi <[EMAIL PROTECTED]> wrote: > > Hi All , > > I have successfully installed pgcluster 1.7 rc7 and it is working fine . But > i have got a doubt on " Single Point Failure " . > > I need a suggest from u all on this . we don't see to many pgcluster questions here...you

Re: [GENERAL] Converting non-null unique idx to pkey

On Tuesday 21 August 2007 1:45 pm, Scott Marlowe wrote: > If you have a large db in 7.4.6, you should do two things. > > 1: Update to 7.4.19 or whatever the latest flavor of 7.4 is, > right now. There are a few known data eating bugs in 7.4.6. Sounds like good advice from a strictly technical vie

Re: [GENERAL] PG Seg Faults Performing a Query

On 8/22/07, Bill Thoen <[EMAIL PROTECTED]> wrote: > How would you suggest I try to track down this problem? > Any suggestions? postgres version? Operating system? Anything in the log(s)? -- Please don't top post, and don't use HTML e-Mail :} Make your quotes concise. http://www.american.edu/ec

Re: [GENERAL] PG Seg Faults Performing a Query

On 8/21/07, Bill Thoen <[EMAIL PROTECTED]> wrote: > How would you suggest I try to track down this problem? > I run the following query: > > SELECT a.* FROM compliance_2006 a, ers_regions b > WHERE a.fips_st_cd=b.fips_st > AND a.fips_cnty_cd=b.fips_cou AND b.region =1 > AND a.fips_st_cd='

Re: [GENERAL] history table

Robin Helgelin wrote: On 8/21/07, Guy Rouillier <[EMAIL PROTECTED]> wrote: Well, you haven't told us much about your webapp. Are you using connection pooling? If so, then you'll need to provide the webapp userid as an additional parameter to your database updates. If you are not using connect

Re: [GENERAL] Auto-partitioning?

-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Steve Wampler wrote: > Steve Wampler wrote: >> ... Specifically, I'm wondering if it's >> possible to add a default rule that would create a new partition >> (with indices, etc.) and would add a new rule for this partition >> to match the similar ones

Re: [GENERAL] Auto-partitioning?

Steve Wampler wrote: ... Specifically, I'm wondering if it's possible to add a default rule that would create a new partition (with indices, etc.) and would add a new rule for this partition to match the similar ones above (and, of course, then move the INSERT into the new partition). I think I

[GENERAL] PG Seg Faults Performing a Query

How would you suggest I try to track down this problem? I run the following query: SELECT a.* FROM compliance_2006 a, ers_regions b WHERE a.fips_st_cd=b.fips_st AND a.fips_cnty_cd=b.fips_cou AND b.region =1 AND a.fips_st_cd='17' AND a.fips_cnty_cd='003'; and it works. But when I try this:

Re: [GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

Dmitry Koterov wrote: One difference in SQL syntax is that FireBird could join stored procedures like this: SELECT b.* FROM get_ids() a LEFT JOIN get_data(a.ID ) ON 1=1 (where a.ID parameter is passed from the previous set as a next procedure parameter), but Pos

Re: [GENERAL] Converting non-null unique idx to pkey

On 8/21/07, Ed L. <[EMAIL PROTECTED]> wrote: > > I'm preparing a fairly large 7.4.6 DB for trigger-based > replication. I'm looking for ways to minimize my impact on the > existing schema & data and uptime. This replication solution > requires every table to have a primary key. Rather than addin

Re: [GENERAL] history table

On Tuesday 21 August 2007 1:42 pm, Ed L. wrote: > Then you could > store the user ID in an update_session_id column and tablelog > would help track of the history. s/user ID/session ID/g; Ed ---(end of broadcast)--- TIP 6: explain analyze is your

Re: [GENERAL] Array with Subselect / ANY - cast?

On Tue, 21 Aug 2007 21:36:00 +0200 "Pavel Stehule" <[EMAIL PROTECTED]> wrote: > it works? no. > > pavel=# select 1 from (values(10)) a(i) where i = any((select * > from foo)::int[]); > ERROR: cannot cast type integer to integer[] > LINE 1: ...values(10)) a(i) where i = any((select * from > foo

Re: [GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

One difference in SQL syntax is that FireBird could join stored procedures like this: SELECT b.* FROM get_ids() a LEFT JOIN get_data(a.ID) ON 1=1 (where a.ID parameter is passed from the previous set as a next procedure parameter), but Postgres cannot. On 8/21/07, Tony Caduto <[EMAIL PROTE

Re: [GENERAL] history table

On Tuesday 21 August 2007 1:22 pm, Robin Helgelin wrote: > > Yes, this is where I'm too new to postgresql, how do I tell > the database which user is logged in to the webapp? A session > parameter? There will be connection pooling, but if I know how > to solve the previous question I don't think it

Re: [GENERAL] Array with Subselect / ANY - cast?

2007/8/21, Josh Trutwin <[EMAIL PROTECTED]>: > On Tue, 21 Aug 2007 20:15:59 +0200 > "Pavel Stehule" <[EMAIL PROTECTED]> wrote: > > > SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123) > > > > or > > > > SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb > > WHERE id=12

Re: [GENERAL] Array with Subselect / ANY - cast?

On Tue, 21 Aug 2007 14:19:03 -0500 Michael Glaesemann <[EMAIL PROTECTED]> wrote: > Out of curiosity, what led to the schema design of storing these > pb_id values in an array rather than in a many-to-many table? > You're working against the database server here. The usual way to > define this re

[GENERAL] Converting non-null unique idx to pkey

I'm preparing a fairly large 7.4.6 DB for trigger-based replication. I'm looking for ways to minimize my impact on the existing schema & data and uptime. This replication solution requires every table to have a primary key. Rather than adding a new key column and index for the pkey, it's ap

Re: [GENERAL] history table

On 8/21/07, Guy Rouillier <[EMAIL PROTECTED]> wrote: > Well, you haven't told us much about your webapp. Are you using > connection pooling? If so, then you'll need to provide the webapp > userid as an additional parameter to your database updates. If you are > not using connection pooling, such

Re: [GENERAL] Array with Subselect / ANY - cast?

On Aug 21, 2007, at 12:49 , Josh Trutwin wrote: SELECT pb_ids FROM pb WHERE id = 123: pb_id --- {196,213,215,229,409} These numbers map to a productid in tblproducts so I figured I could do this: SELECT * FROM tblproducts WHERE productid = ANY ( SELECT

Re: [GENERAL] history table

Robin Helgelin wrote: Hi, I want to save history for a few tables using triggers on update and creation. What's the best approach to do this in a webapp environment where I want to save which webapp user that is doing the change, not the postgresql user? Well, you haven't told us much about y

Re: [GENERAL] history table

am Tue, dem 21.08.2007, um 20:20:38 +0200 mailte Robin Helgelin folgendes: > Hi, > > I want to save history for a few tables using triggers on update and > creation. What's the best approach to do this in a webapp environment > where I want to save which webapp user that is doing the change, not

Re: [GENERAL] Array with Subselect / ANY - cast?

On Tue, 21 Aug 2007 20:15:59 +0200 "Pavel Stehule" <[EMAIL PROTECTED]> wrote: > SELECT * FROM ... WHERE 1000 IN (SELECT pb_ids FROM pb WHERE id=123) > > or > > SELECT * FROM ... WHERE 1000 = ANY(ARRAY(SELECT pb_ids FROM pb > WHERE id=123)) Thanks - another way: SELECT * FROM ... WHERE 1000 = A

[GENERAL] history table

Hi, I want to save history for a few tables using triggers on update and creation. What's the best approach to do this in a webapp environment where I want to save which webapp user that is doing the change, not the postgresql user? -- regards, Robin ---(

Re: [GENERAL] Array with Subselect / ANY - cast?

Hello 2007/8/21, Josh Trutwin <[EMAIL PROTECTED]>: > Hi - I have the following array field: > > SELECT pb_ids FROM pb WHERE id = 123: > > pb_id > --- > {196,213,215,229,409} > > These numbers map to a productid in tblproducts so I figured I could > do this: > > SELECT

[GENERAL] Array with Subselect / ANY - cast?

Hi - I have the following array field: SELECT pb_ids FROM pb WHERE id = 123: pb_id --- {196,213,215,229,409} These numbers map to a productid in tblproducts so I figured I could do this: SELECT * FROM tblproducts WHERE productid = ANY ( SELECT pb_ids

Re: [GENERAL] Question on INDEX and SQL - stalling my database?

Rishi, I looked up that thread 1st:) p.s. I am using PostgreSQL 7.4.17 Any reason for that? Actual version is 8.2.4; or at least 8.1.9 2nd) your query is: SELECT DISTINCT c.* FROM customer c LEFT OUTER JOIN weborders w ON c.username = w.usern

[GENERAL] Need help doing a PostgreSQL vs Firebird feature comparison

Hi, I was just wondering if anyone could help me out by taking a look to see if I missed any important features. http://www.amsoftwaredesign.com/pg_vs_fb This comparison is going to be for the benefit of Delphi users. The Delphi community is heavily biased to Firebird. Please post any com

Re: [GENERAL] Question on INDEX and SQL - stalling my database?

Rishi Daryanani wrote: I'm having problems with a query that's just "stalling" my database. If someone could help me out - I posted a forum topic on http://forums.devshed.com/postgresql-help-21/postgresql-new-index-pros-and-cons-467120.html Did you get any advice from that forum? Was it helpfu

Re: [GENERAL] SELECT question

Michelle Konzack wrote: > Am 2007-08-17 12:53:41, schrieb Michael Glaesemann: > > > > On Aug 17, 2007, at 7:27 , Michelle Konzack wrote: > > > > >* > > >* Do not Cc: me, because I am on THIS list, if I write here.* > > >

Re: [GENERAL] SELECT question

On 8/18/07, Michelle Konzack <[EMAIL PROTECTED]> wrote: > Am 2007-08-17 12:53:41, schrieb Michael Glaesemann: > > (Is is just me or have there been a lot of queries that can be solved > > using DISTINCT ON recently?) > > I do not know... Since when does "DISTINCT ON" exist? I have been lurking o

Re: [GENERAL] SELECT question

Am 2007-08-17 12:53:41, schrieb Michael Glaesemann: > > On Aug 17, 2007, at 7:27 , Michelle Konzack wrote: > > >* > >* Do not Cc: me, because I am on THIS list, if I write here.* > > You might want to consider changing y

Re: [GENERAL] Audit-trail engine inner-workings

--- Marcelo de Moraes Serpa <[EMAIL PROTECTED]> wrote: > Hello list, > [snip] > > * Each http request gets an instance of a session_id > ? Or is it > per-connection ? It depends. There is no necessary correlation between a session as defined within a web application and a session as defined i

[GENERAL] Audit-trail engine inner-workings

Hello list, Taking this discussion as a base: http://archives.postgresql.org/pgsql-general/2007-04/msg01034.php Manuel helped me to develop an audit-trail engine, and even though it works ok, I need to know better its inner workings: >From what I understand, the flow is something like this:

Re: [GENERAL] Using oid as pkey

On Mon, Aug 20, 2007 at 07:00:32PM -0500, D. Dante Lorenso wrote: > Exactly what I was looking for. Looks like I need to make moves to get > from 8.1 onto 8.2 ;-) in any pg you should simply use select currval('sequence_name'); and be happy with it. depesz -- quicksil1er: "postgres is excelle

Re: [GENERAL] do you have an easy example of postgis and mapserver?

On Tue, Aug 21, 2007 at 12:44:49PM +0200, Ottavio Campana wrote: > I'm sorry this mail is not very in topic, but I hope you can help me. Just so you know, perhaps a better list to contact with this is the MapServer mailing list at http://lists.umn.edu/archives/mapserver-users.html, or maybe the Po

Re: [GENERAL] Solution to Bus error(coredump) from postgres binary

Jayaprakash, Sowmiya Lakshmi (STSD) wrote: > Hi, > > I'm in the process of building Postgres 7.4.2 on a HP-UX PA Machine. Why are you trying 7.4.2 and not 7.4.17? Or even 8.2.4, really. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Comm

[GENERAL] do you have an easy example of postgis and mapserver?

Hi, I'm sorry this mail is not very in topic, but I hope you can help me. I'm trying to learn how postgis and mapserver work together, but I cannot understand nearly anything. I mean, I read the documentation of postgis and I think I understood it, but I cannot do anything useful with it. What I

[GENERAL] Pgcluster 1.7 Fail safe !!!

Hi All , I have successfully installed pgcluster 1.7 rc7 and it is working fine . But i have got a doubt on " Single Point Failure " . I need a suggest from u all on this . For an Application which demands High Availability and  no Single Point Failure and built Multi-Master Replication sys