Re: [GENERAL] Poor Performance with Distinct Subqueries with EXISTS and EXCEPT

Interestingly, I tried the new version with and without enable_seqscan on, and the version without indexes performs better because, I think, it returns more rows than an index lookup would enhance. Thanks again for your help. This is certainly an improvement over my original version. -tfo -- T

[SOLVED] Re: [GENERAL] initdb error: "could not identify current directory" (or, what have i done now?)

hi tom, i think i got it ... keeping in mind your question re: read perms, & reading this thread: and noting

Re: [GENERAL] initdb error: "could not identify current directory" (or,

hi tom, per this thread, i've been googl'ing on 'getcwd initdb permissions parent' and find lots of refs ... issues with folks having 'permission denied' errors if parent directories don't have correct permissions, etc. this 'smells' pretty much like the issue at hand here ... tho' i dunno (yet)

[GENERAL] rc1 fedora core rpms

As promised, I've posted 8.0.0rc1 rpms here: http://www.joeconway.com/postgresql-8.0.0rc/ Again note that these are not "official" PGDG rpms, just my own home brew. In addition to the change of Postgres itself from beta5 to rc1, I updated jdbc to latest beta (pg80b1.308*). Joe

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

On Fri, 3 Dec 2004, Christopher Browne wrote: [EMAIL PROTECTED] (Jan Wieck) wrote: Since the mailing list to comp.databases.postgresql.general gating was stopped over a week ago, there has been zero communication on that newsgroup. I guess, that currently all of the developers and advanced users ar

Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was:

On Fri, 3 Dec 2004, Mike Cox wrote: If you want more news servers to carry pgsql.*, consider emailing [EMAIL PROTECTED], and request them to carry pgsql.*. I've done one better ... I email'd and arranged a direct peerage between our servers, so that the groups are there, and all articles available

Re: [GENERAL] PostgreSQL 8.0.0 Release Candidate 1

Fixed, thanks ... On Fri, 3 Dec 2004, Michael Fuhr wrote: On Sat, Dec 04, 2004 at 12:18:03AM -0400, Marc G. Fournier wrote: For a complete list of changes/improvement since Beta 5 was released, please see: ftp://ftp.postgresql.org/pub/source/v8.0.0beta/ChangeLog-Beta5-to-RC1 No such file. A li

Re: [GENERAL] PostgreSQL 8.0.0 Release Candidate 1

On Sat, Dec 04, 2004 at 12:18:03AM -0400, Marc G. Fournier wrote: > For a complete list of changes/improvement since Beta 5 was released, > please see: > >ftp://ftp.postgresql.org/pub/source/v8.0.0beta/ChangeLog-Beta5-to-RC1 No such file. A link that works, but that may itself be a typo, i

Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)

Mike Cox wrote: > Jan Wieck wrote: > >> On 12/3/2004 3:32 PM, Woodchuck Bill wrote: >> >>> [EMAIL PROTECTED] ("Joshua D. Drake") wrote in >>> news:[EMAIL PROTECTED]: >>> > So the current state of affairs is that we have the gated, official > pgsql.* newsgroups, and the comp.* stuf

Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)

Jan Wieck wrote: > On 12/3/2004 3:32 PM, Woodchuck Bill wrote: > >> [EMAIL PROTECTED] ("Joshua D. Drake") wrote in >> news:[EMAIL PROTECTED]: >> >>> So the current state of affairs is that we have the gated, official pgsql.* newsgroups, and the comp.* stuff is not gated in either

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

[EMAIL PROTECTED] (Jan Wieck) wrote: > Since the mailing list to comp.databases.postgresql.general gating was > stopped over a week ago, there has been zero communication on that > newsgroup. I guess, that currently all of the developers and advanced > users are either on the mailing list or using

[GENERAL] PostgreSQL 8.0.0 Release Candidate 1

After 3 months of BETA testing, The PostgreSQL Global Development Group is pleased to announce that we've locked down the source code and have now released our first Release Candidate. Our goal is to formally release 8.0.0 around the 15th of December, and to ensure that we are as bug free as po

Re: [GENERAL] initdb error: "could not identify current directory" (or,

hi tom, % ls -ald /Volumes/data/pgsql drwxrwxr-x 12 testuser testuser 408 Nov 25 09:12 /Volumes/data/pgsql Huh? Why didn't the rm -rf remove both of those? Or are you skipping some steps? oops, sorry, copy-n-paste-itis: % rm -rf /var/data/pgsql/* /Volumes/data/pgsql/* % cd /var/data/pg

Re: [GENERAL] [ANNOUNCE] pgsql.* USENET hierarchy has hit Google ...

Marc G. Fournier wrote: > > Just so that everyone knows, they are now carrying 11 of the groups: > > http://groups.google.ca/groups?hl=en&lr=&group=pgsql > > For those that were using Google to read them in the past ... Excellent, but now Google just changed the way it displays Groups and

Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK

hi tom, After eyeballing that, I think I have no hope of reproducing your test conditions unless you show me the exact script and property list files you used. certainly easy enuf. thought *i'm* not certain *i* have hope of reproducing much after today's shenanigans ... jeeesh! fyi, the latest v

Re: [GENERAL] initdb error: "could not identify current directory" (or, what have i done now?)

OpenMacNews <[EMAIL PROTECTED]> writes: > then, i stumbled across the following behavior.: > % rm -rf /var/data/pgsql /Volumes/data/pgsql > % ls -ald /var/data/pgsql > lrwxr-xr-x 1 root wheel 27 Nov 22 20:26 /var/data/pgsql -> > /Volumes/data/pgsql > % ls -ald /Volumes/data/pgsql >

Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK

OpenMacNews <[EMAIL PROTECTED]> writes: > fyi: here's an O'Reilly blurb with way more info than you want to know ... > After eyeballing that, I think I have no hope of reproducing your test conditions unless you show me the exact scr

Re: [GENERAL] initdb error: "could not identify current directory" (or,

hi there, % /usr/local/pgsql/bin/initdb --pgdata=/var/data/pgsql --username=testuser could not identify current directory: Permission denied What directory are you in when you give this command? (AFAIR su doesn't change directory ... you might be better off with su - testuser ...) h, that'

Re: [GENERAL] Dont let those int8's drive you mad!!

update transactions set state='O' where trans_id=int8(14332) Sped it up from 4 milliseconds to .07 milliseconds (and obviously now did an index scan) This HAS bitten me before. Questions: If postgres knows the field is an int8, why do I have to cast it in my query? That is a long answer. An

Re: [GENERAL] initdb error: "could not identify current directory" (or, what have i done now?)

OpenMacNews <[EMAIL PROTECTED]> writes: >> su testuser > % /usr/local/pgsql/bin/initdb --pgdata=/var/data/pgsql --username=testuser > could not identify current directory: Permission denied What directory are you in when you give this command? (AFAIR su doesn't change directory ... you might

[GENERAL] initdb error: "could not identify current directory" (or, what have i done now?)

hi all, i just clean-rebuilt pgsql8b5 os OSX 10.3.6 the build went fine as before -- no errors reported. per instruction, i've created an unpriv'd user: testuser. now, however, when i try to 'initdb', creating in '/var/data/pgsql': su testuser % /usr/local/pgsql/bin/initdb --pgdata=/var/data/pgsql

Re: [GENERAL] Poor Performance with Distinct Subqueries with EXISTS and EXCEPT

Pierre, Your re-write makes a lot of sense. Thanks! It's not using indexes for some reason, and discovering why will be my next challenge. -tfo -- Thomas F. O'Connell Co-Founder, Information Architect Sitening, LLC http://www.sitening.com/ 110 30th Avenue North, Suite 6 Nashville, TN 37203-6320 6

Re: [GENERAL] Dont let those int8's drive you mad!!

Outstanding thanks all Tom Lane wrote: Jeff Amiel <[EMAIL PROTECTED]> writes: If postgres knows the field is an int8, why do I have to cast it in my query? As of 8.0 you won't have to anymore. You don't really want to know why it took us six years to find a workable solution... suff

Re: [GENERAL] Dont let those int8's drive you mad!!

Jeff Amiel <[EMAIL PROTECTED]> writes: > If postgres knows the field is an int8, why do I have to cast it in my query? As of 8.0 you won't have to anymore. You don't really want to know why it took us six years to find a workable solution... suffice it to say that it was harder than you might thi

[GENERAL] RFDs, Lists & Usenet: One Non-Advanced, Non-Developer User's Perspective

hi all, after a fairly long hiatus from pgsql, i've recently (re)joined the community, and have dropped into the middle of the what-do-we-do-abt-newsgroups thread. i thought i'd add my lil' ol' voice w/ some 'objective, unbiased' (yeah, right ...) comments. (1) in general, i honestly don't care

Re: [GENERAL] Dont let those int8's drive you mad!!

Jeff Amiel <[EMAIL PROTECTED]> writes: > If postgres knows the field is an int8, why do I have to cast it in my query? This is fixed in 8.0. -Doug ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[GENERAL] Dont let those int8's drive you mad!!

I was stuck for 3 hours today trying to figure out why postgres was doing a seq scan on a primary key/unique index column. the statement was innocuous enough update transactions set state='O' where trans_id=14332 trans_id was the primary key and also had a unique btree index on it. No matter w

Re: [GENERAL] table inheritance and DB design

We are working on a PostgresSQL KB. Soon we are going to move the FAQ and Information as such to the KB -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Alec Swan Sent: Friday, December 03, 2004 7:02 PM To: Tom Lane Cc: [EMAIL PROTECTED] Subject: Re: [G

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

On 12/3/2004 3:32 PM, Woodchuck Bill wrote: [EMAIL PROTECTED] ("Joshua D. Drake") wrote in news:[EMAIL PROTECTED]: So the current state of affairs is that we have the gated, official pgsql.* newsgroups, and the comp.* stuff is not gated in either direction? Yes. If that's the case, there shoul

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

Woodchuck Bill wrote: David Harmon <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]: On Fri, 3 Dec 2004 00:29:40 + (UTC) in news.groups, Marc G. Fournier From: <[EMAIL PROTECTED]> wrote, The pgsql.* hierarchy is a not a private one, it is a public one carried by several of the large use

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

If that's the case, there should be a weekly/monthly reminder posting on the comp.* side set up, pointing out that these are not official groups and that real PostgreSQL questions are better asked somewhere else, if the intention is to reach real developers and get real help. I don't want to see p

Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was: comp.databases.postgresql.*)

David Harmon <[EMAIL PROTECTED]> wrote in news:[EMAIL PROTECTED]: > On Fri, 3 Dec 2004 00:29:40 + (UTC) in news.groups, Marc G. > Fournier From: <[EMAIL PROTECTED]> wrote, >>The pgsql.* hierarchy is a not a private one, it is a public one >>carried by several of the large usenet servers. >

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

[EMAIL PROTECTED] ("Joshua D. Drake") wrote in news:[EMAIL PROTECTED]: > >> So the current state of affairs is that we have the gated, official >> pgsql.* newsgroups, and the comp.* stuff is not gated in either >> direction? Yes. >> If that's the case, there should be a weekly/monthly remind

Re: [GENERAL] Older Windows versions

Leonardo Mateo wrote: > Hi, I need an older version of PostgreSQL for windows since I, > unfortunatelly, have to develope a Win32 application with Qt-Non > Commercial, and the available drivers for Postgre are for Versions > 6.x and 7.x. Any driver that works with 7.x should also work with 8.0. >

[GENERAL] How to get point in the path?

select seq_id,npoints(latlon_path) as path_points,(latlon_path[0])[0] as path_pt1_x,(latlon_path[0])[1] as path_pt1_y,(latlon_path[1])[0] as path_pt2_x,(latlon_path[1])[1] as path_pt2_y,(latlon_path[2])[0] as path_pt3_x,(latlon_path[2])[1] as path_pt3_y,(latlon_path[3])[0] as path_pt4

[GENERAL] Older Windows versions

Hi, I need an older version of PostgreSQL for windows since I, unfortunatelly, have to develope a Win32 application with Qt-Non Commercial, and the available drivers for Postgre are for Versions 6.x and 7.x. I went to the downloads section on postgresql.org but I couldn't find anything but the 8.x

Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK

hi tom, however, if i place an identical startup string in my OSX's StartupItem for pgsql & reboot, pgsql does not start on boot. I was trying to reproduce this on my own machine, but couldn't get out of the starting gate. I put an executable shell script into "/System Folder/Startup Items", but I

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

So the current state of affairs is that we have the gated, official pgsql.* newsgroups, and the comp.* stuff is not gated in either direction? If that's the case, there should be a weekly/monthly reminder posting on the comp.* side set up, pointing out that these are not official groups and th

[GENERAL] Calling for translation finalization

Leading up to the release of PostgreSQL 8.0, the development group has agreed to freeze the message strings, so now is the right time to send in message translations for the 8.0 release. See http://developer.postgresql.org/~petere/nlsinfo/ for information on how to contribute. If there ar

Re: [GENERAL] 3rd RFD: comp.databases.postgresql

On 12/3/2004 1:59 PM, Marc G. Fournier wrote: On Fri, 3 Dec 2004, Net Virtual Mailing Lists wrote: My only suggestion: I don't care what you do with the newsgroups, just don't screw with the mailing lists. If the mailing lists go away, I will be *EXTREMELY* disappointed! this RFD in no way affects

[GENERAL] pgsql.* USENET hierarchy has hit Google ...

Just so that everyone knows, they are now carrying 11 of the groups: http://groups.google.ca/groups?hl=en&lr=&group=pgsql For those that were using Google to read them in the past ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED]

Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was:

On Fri, 3 Dec 2004, Net Virtual Mailing Lists wrote: My only suggestion: I don't care what you do with the newsgroups, just don't screw with the mailing lists. If the mailing lists go away, I will be *EXTREMELY* disappointed! this RFD in no way affects the mailing lists, and is in no way an 'offi

Re: [GENERAL] pgFoundary?

On Fri, 3 Dec 2004, Jim C. Nasby wrote: I've seen references to pgFoundary on the mailling lists, but I can't seem to find it anywhere. Does it actually exist? Is it the predecessor to gborg? Speaking of gborg, how come it isn't accepting projects right now? because pgfoundry is meant to replace gb

Re: [GENERAL] table inheritance and DB design

Berend, thanks for posting a part of your schema. In OO terms you used incapsulation by providing a reference from customer table to organization table instead of inheritance. This makes sense. But again, in order to find a supplier represented by an organizaion record, your business layer would

Re: [GENERAL] table inheritance and DB design

On Fri, Dec 03, 2004 at 10:02:07AM -0800, Alec Swan wrote: > On the TODO page it says: "A hyphen, "-", marks > changes that will appear in the upcoming 8.1 > release.", but none of the items are marked with a > hyphen. I guess this TODO page needs to be updated. Development for 8.1 hasn't started

Re: [GENERAL] table inheritance and DB design

Alec Swan <[EMAIL PROTECTED]> writes: > On the TODO page it says: "A hyphen, "-", marks > changes that will appear in the upcoming 8.1 > release.", but none of the items are marked with a > hyphen. I guess this TODO page needs to be updated. It's perfectly up to date ... nothing's been done for 8.

Re: [GENERAL] table inheritance and DB design

I understand your bang theory perfectly :) I just wanted to know if there were plans to fix this. I just looked at the TODO list at http://developer.postgresql.org/todo.php and found the section with inherited ref. constraints in the Indexes section. On the TODO page it says: "A hyphen, "-", mar

Re: [GENERAL] Indexes?

> >> based on the index values because 2004-12-02 is between the from and end >> date. However, b and d would be excluded immediately because the the >> from >> date is greater than 2004-12-02 and would save the optimizer from even >> reading the table for these index entries because the fromDate i

Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK

i've a new install of pgsql8b5 running on OSX 10.3.6. ... however, if i place an identical startup string in my OSX's StartupItem for pgsql & reboot, pgsql does not start on boot. I was trying to reproduce this on my own machine, but couldn't get out of the starting gate. I put an executable shel

Re: [GENERAL] pgsql8b5 not launching on OSX system start; otherwise OK

OpenMacNews <[EMAIL PROTECTED]> writes: > i've a new install of pgsql8b5 running on OSX 10.3.6. > ... > however, if i place an identical startup string in my OSX's StartupItem for > pgsql & reboot, pgsql does not start on boot. I was trying to reproduce this on my own machine, but couldn't get ou

Re: [GENERAL] Indexes?

On Thu, Dec 02, 2004 at 22:37:38 -0800, Vincent Hikida <[EMAIL PROTECTED]> wrote: > 2004-12-01 2003-02-22 > 2005-03-04 2003-02-22 (a) > 2005-03-05 2004-12-15 (b) > 2005-03-05 2004-06-18 (c) > 2007-04-12 2005-06-18 (d) > > Let's say that there are a millio

Re: [GENERAL] Is there a way to view a rewritten query?

Jonathan Knopp <[EMAIL PROTECTED]> writes: > I'm trying to set up some complex rules and having a hard time figuring > out why they won't work. Being able to see the query after all the rules > have been applied would be a huge help, but I can't seem to find a way > to do this. log_statement only s

Re: [GENERAL] reclaiming diskspace bloat w/near-zero downtime

On Friday December 3 2004 9:09, Tom Lane wrote: > "Ed L." <[EMAIL PROTECTED]> writes: > > [SIDEBAR: Vacuum + fsm is not working as > > I expected; it is clearly not reclaiming space resulting from the > > UPDATEs. If I UPDATE 1 rows and then run vacuum, I was > > expecting/hoping that a subseq

Re: [GENERAL] table inheritance and DB design

Alvaro Herrera <[EMAIL PROTECTED]> writes: > On Fri, Dec 03, 2004 at 08:24:38AM -0500, Berend Tober wrote: >> My personal take-away was that the behavior was not going to be fixed >> in the near term > The problem is that no developer is interested in fixing it (it's "low > on everyone's to-do lis

Re: [GENERAL] table inheritance and DB design

> ...have a Resource table and a Car table > and a ResCar many-to-many relation. I don't think you need the ResCar table. The Car table defines a many-to-many relation with Appointment. As does the Resource table. The Car table contains a subset of rows from the Resource table. > ...it's not exte

[GENERAL] Overhead of dynamic query in trigger

I am curious as to how much overhead building a dynamic query in a trigger adds to the process. The example: Have a list of subcontractors, each of which gets unique pricing. There is a total of roughly 100,000 items available and some 100 subcontractors. The 2 design choices would be 100 tables (

Re: [GENERAL] reclaiming diskspace bloat w/near-zero downtime

"Ed L." <[EMAIL PROTECTED]> writes: > [SIDEBAR: Vacuum + fsm is not working as > I expected; it is clearly not reclaiming space resulting from the UPDATEs. > If I UPDATE 1 rows and then run vacuum, I was expecting/hoping that a > subsequent UPDATE of 9000 rows would largely reuse the space

Re: [GENERAL] general questions on Postgresql and deployment on

Calvin Wood <[EMAIL PROTECTED]> writes: > symbolic link. But on win32, there is no equivalent. However, even under > *nix system, I believe symbolic link can only be created for directories on > the same hard drive. This seems less than optimal. Typically, one would > place database files on RA

Re: [GENERAL] reclaiming diskspace bloat w/near-zero downtime

On Friday December 3 2004 1:54, Martijn van Oosterhout wrote: > > For this, check the FSM settings. It can only keep track of a limited > number of pages. So if your tuples are large it may not be able to > track it all... > > The FSM should be set big enough to cover all the space that might be >

Re: [GENERAL] table inheritance and DB design

Alec Swan wrote: Berend, I understand your alternative design. So, you are suggesting to have a Resource table and a Car table and a ResCar many-to-many relation. This will work, but it's not extensible. Suppose, my application needs to find the resource that is assigned to an appointment. My progr

Re: [GENERAL] table inheritance and DB design

Berend, I understand your alternative design. So, you are suggesting to have a Resource table and a Car table and a ResCar many-to-many relation. This will work, but it's not extensible. Suppose, my application needs to find the resource that is assigned to an appointment. My program will have to

Re: [GENERAL] table inheritance and DB design

On Fri, Dec 03, 2004 at 08:24:38AM -0500, Berend Tober wrote: > > On Thu, Dec 02, 2004 at 10:53:37PM -0500, Berend Tober wrote: > > > >> I learned that the unusual behavior (or at least the behavior that > >> seems weird to me) regarding relational integrity and uniquness > >> constraints as been a

Re: [GENERAL] reclaiming diskspace bloat w/near-zero downtime

Ed L. wrote: I need to reclaim the diskspace from a heavily updated 7.3.4 table which has grown 95% bloated to 20gb of disk (indices and toast included), and I need to reclaim it while the table continues to get updates, and without interrupting/delaying access more than a few seconds. This is

Re: [GENERAL] relation does not exist error

The answer is in the question! It's the difference between Customers and customers. PostgreSQL folds identifiers to lower case if not double quoted. The best solution is to use lower case consistently. The alternative is to use double quotes consistently. Try SELECT * FROM "Customers"; and you

Re: [GENERAL] general questions on Postgresql and deployment on win32 platform

> I have gone through the documentation that come with version > 8 beta 4 and I have a number of questions. > > (1) backup/restore > I notice that in the documentation, it seems to suggest that > an online backup, made via pg_start_backup() and > pg_stop_backup() functions would back up all dat

Re: [GENERAL] table inheritance and DB design

> On Thu, Dec 02, 2004 at 10:53:37PM -0500, Berend Tober wrote: > >> I learned that the unusual behavior (or at least the behavior that >> seems weird to me) regarding relational integrity and uniquness >> constraints as been around for a while, and some people actually think >> is is SUPPOSED to w

Re: [GENERAL] 3rd RFD: comp.databases.postgresql (was:

My only suggestion: I don't care what you do with the newsgroups, just don't screw with the mailing lists. If the mailing lists go away, I will be *EXTREMELY* disappointed! - Greg >Hopefully someone like Russ will tell us the correct term for domains like >microsoft.* and gnu.*. Those on the m

Re: [GENERAL] general questions on Postgresql and deployment on win32

Calvin Wood wrote: Does it also mean that I must back up and restore all the databases (or database cluster in Postgresql Speak) even if I am only interested in 1 database? You can use pg_dump to backup individual databases (or tables etc). A file-level backup does require all databases in one "

Re: [GENERAL] Is there a way to view a rewritten query?

On Thu, Dec 02, 2004 at 09:32:35PM -0800, Jonathan Knopp wrote: > I'm trying to set up some complex rules and having a hard time figuring > out why they won't work. Being able to see the query after all the rules > have been applied would be a huge help, but I can't seem to find a way > to do this.

Re: [GENERAL] reclaiming diskspace bloat w/near-zero downtime

I can't help you with your general enquiry, but some comments: On Thu, Dec 02, 2004 at 11:06:08PM -0700, Ed L. wrote: > If I UPDATE 1 rows and then run vacuum, I was expecting/hoping that a > subsequent UPDATE of 9000 rows would largely reuse the space reclaimed from > the 1-row UPDATE.]