Re: [GENERAL] Dumb question about count()

2005-07-21 Thread Michael Fuhr
On Thu, Jul 21, 2005 at 10:09:07PM -0700, Benjamin Smith wrote: > select classroom.title, > count(students.id) AS students, > count(seats.id) AS seats > from classrooms, students, seats > where classrooms.id=students.classrooms_id > and classrooms.id=seats.id > > Except that it coun

Re: [GENERAL] Strange input/cast semantics for inet

2005-07-21 Thread Bruce Momjian
Greg Stark wrote: > > This looks very strange to me: > > staging=> select '1.10'::cidr; > cidr > - > 1.10.0.0/16 > (1 row) > > > The normal way to read "1.10" would be as synonymous with "1.0.0.10". This is > even mandated by the POSIX spec for getaddrinfo and company. q

[GENERAL] Dumb question about count()

2005-07-21 Thread Benjamin Smith
I'm sure I've done this before, but for some reason, my main noodle is drawing a blank. Assume we have three tables defined thusly: create table classrooms ( id serial unique not null, name varchar ); create table seats ( classrooms_id integer not null references classrooms(id), position var

Re: [GENERAL] Strange input/cast semantics for inet

2005-07-21 Thread Michael Fuhr
On Thu, Jul 21, 2005 at 10:14:42PM -0400, Greg Stark wrote: > Michael Fuhr <[EMAIL PROTECTED]> writes: > > > I don't know if it's ever been blessed by a formal standard > > It's blessed by POSIX: > > http://www.opengroup.org/onlinepubs/009695399/functions/inet_addr.html Yep, that's lifted almost

Re: [GENERAL] Strange input/cast semantics for inet

2005-07-21 Thread Greg Stark
Michael Fuhr <[EMAIL PROTECTED]> writes: > On Thu, Jul 21, 2005 at 06:38:01PM -0400, Tom Lane wrote: > > Greg Stark <[EMAIL PROTECTED]> writes: > > > The normal way to read "1.10" would be as synonymous with "1.0.0.10". > > > > That might be the case for IPv6, but it's never been a standard > >

[GENERAL] Connection error

2005-07-21 Thread Richard Sydney-Smith
An associate is using Postgresql 8 on a windows xp system. The installation has been operating for several months and he has recently tried installing some software and postgresql has begun refusing connection " could not receive server response to SSL negotiation packet". Unfortunately he is

Re: [GENERAL] Strange input/cast semantics for inet

2005-07-21 Thread Michael Fuhr
On Thu, Jul 21, 2005 at 06:38:01PM -0400, Tom Lane wrote: > Greg Stark <[EMAIL PROTECTED]> writes: > > The normal way to read "1.10" would be as synonymous with "1.0.0.10". > > That might be the case for IPv6, but it's never been a standard > convention for IPv4; and even for IPv6 it doesn't make

Re: [GENERAL] IN subquery not using a hash

2005-07-21 Thread Paul Tillotson
Tom Lane wrote: Paul Tillotson <[EMAIL PROTECTED]> writes: Tom Lane wrote: Hardly likely, considering it's estimating only 296 rows in the subquery output. My bet is that you've chosen a datatype whose comparisons are not hashable (like char(n)). What is the datatype of parentid in t

[GENERAL] unsubscribe

2005-07-21 Thread wayne schlemitz
__ 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

Re: [GENERAL] Strange input/cast semantics for inet

2005-07-21 Thread Tom Lane
Greg Stark <[EMAIL PROTECTED]> writes: > The normal way to read "1.10" would be as synonymous with "1.0.0.10". That might be the case for IPv6, but it's never been a standard convention for IPv4; and even for IPv6 it doesn't make any sense for a network (as opposed to host) number.

[GENERAL] Going to OSCON? We need your help!

2005-07-21 Thread Josh Berkus
Folks, No doubt several/many of you are planning on attending OSCON 2005. The PostgreSQL community will have a large, commercial booth in the exhibit hall at OSCON this year, sponsored by SRA and CommandPrompt. And we need you to help staff it. Based on last year, I expect that many of us wi

Re: [GENERAL] Insert into ... Select ... From ... too intelligent transaction

2005-07-21 Thread Roman Neuhauser
# [EMAIL PROTECTED] / 2005-07-21 19:11:04 +0200: > I use some updateable views to handle my data (which are amazingly > slow), which gives me ultimate flexibility to handle my data. > > there are some insert rules which use currval() to get the last > sequence id for my data which I have to inser

[GENERAL] ROW SHARE vs. ACCESS EXCLUSIVE; UPDATE vs. SELECT ... FOR UPDATE

2005-07-21 Thread Thomas F. O'Connell
I encountered an issue today in a system I'm monitoring where a DDL statement that occurred during a data import caused a pileup. I just want to see if there's anything to do other than increase available resources and wait it out or break referential integrity.Here's the basic setup:CREATE TABLE r

Re: [GENERAL] Can't connect after restart

2005-07-21 Thread Audrey Bergeron-Morin
--- "Matthew T. O'Connor" wrote: > Audrey Bergeron-Morin wrote: > > >We've been having trouble with a pgSQL 8.0.3 > install > >on a WXP machine. Install goes fine, DB works until > we > >restart the machine, then we can't connect. First > time > >we thought something was corrupted because we h

Re: [GENERAL] Can't connect after restart

2005-07-21 Thread Matthew T. O'Connor
Audrey Bergeron-Morin wrote: We've been having trouble with a pgSQL 8.0.3 install on a WXP machine. Install goes fine, DB works until we restart the machine, then we can't connect. First time we thought something was corrupted because we had a power outage, we uninstalled/reinstalled and it was

[GENERAL] Strange input/cast semantics for inet

2005-07-21 Thread Greg Stark
This looks very strange to me: staging=> select '1.10'::cidr; cidr - 1.10.0.0/16 (1 row) The normal way to read "1.10" would be as synonymous with "1.0.0.10". This is even mandated by the POSIX spec for getaddrinfo and company. q -- greg ---(end

[GENERAL] Can't connect after restart

2005-07-21 Thread Audrey Bergeron-Morin
Hi, We've been having trouble with a pgSQL 8.0.3 install on a WXP machine. Install goes fine, DB works until we restart the machine, then we can't connect. First time we thought something was corrupted because we had a power outage, we uninstalled/reinstalled and it was fine until, again, we decid

Re: [GENERAL] Converting MySQL tinyint to PostgreSQL

2005-07-21 Thread Scott Marlowe
On Thu, 2005-07-21 at 09:23, Dawid Kuroczko wrote: > On 7/19/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > > CREATE TABLE sample1 ( > > > a boolean, > > > b int, > > > c boolean > > > ); > > > > > > ...it will take more storage than: > > > > > > CREATE TABLE sample2 ( > > > b int,

Re: [GENERAL] dynamic loading of c-functions

2005-07-21 Thread Martijn van Oosterhout
On Thu, Jul 21, 2005 at 10:40:21AM -0700, TJ O'Donnell wrote: > It is clear from the manual that c-functions can cause the dynamic loading > of .so's and that subsequent usage in the same database session will use > the already loaded function. But, when the session is done, will the loaded > func

[GENERAL] dynamic loading of c-functions

2005-07-21 Thread TJ O'Donnell
It is clear from the manual that c-functions can cause the dynamic loading of .so's and that subsequent usage in the same database session will use the already loaded function. But, when the session is done, will the loaded function remain in the server for subsequent sessions? When/how does a dyn

[GENERAL] Insert into ... Select ... From ... too intelligent transaction

2005-07-21 Thread Rose, Juergen
Hi all, I'm still working on my updateable views, which work fine now even I have a trigger to use because my delete rules wont work, but thats not my current, more important problem. I use some updateable views to handle my data (which are amazingly slow), which gives me ultimate flexibility

Re: [GENERAL] Query planner refuses to use index

2005-07-21 Thread Janning Vygen
Am Donnerstag, 21. Juli 2005 17:45 schrieb Kilian Hagemann: > Hi there, > > I know this subject has come up before many times, but I'm struggling for > hours with the following problem and none of the posts seem to have a > solution. I have a table with a good 13 million entries with > > station_da

Re: [GENERAL] Query planner refuses to use index

2005-07-21 Thread Martijn van Oosterhout
On Thu, Jul 21, 2005 at 05:45:55PM +0200, Kilian Hagemann wrote: > Hi there, > > I know this subject has come up before many times, but I'm struggling for > hours with the following problem and none of the posts seem to have a > solution. I have a table with a good 13 million entries with Firs

Re: [GENERAL] Wishlist?

2005-07-21 Thread Martijn van Oosterhout
Hmm, true. So I guess the real solution (once temp tables work nicely) would be something like CREATE LOCAL TEMP TABLE where the pl/pgsql parser invents the unusual name for you. Ofcourse, then you run into the issue where subcalls (like using EXECUTE) won't be able to see that table either. What

Re: [GENERAL] RAMFS with Postgres

2005-07-21 Thread Scott Marlowe
On Thu, 2005-07-21 at 02:43, vinita bansal wrote: > Hi, > > My application is database intensive. I am using 4 processes since I have 4 > processeors on my box. There are times when all the 4 processes write to the > database at the same time and times when all of them will read all at once. >

[GENERAL] Query planner refuses to use index

2005-07-21 Thread Kilian Hagemann
Hi there, I know this subject has come up before many times, but I'm struggling for hours with the following problem and none of the posts seem to have a solution. I have a table with a good 13 million entries with station_data=# \d speed Table "public.speed" Column | Type | Mo

Re: [GENERAL] Wishlist?

2005-07-21 Thread Stephan Szabo
On Thu, 21 Jul 2005, Martijn van Oosterhout wrote: > On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote: > > * Create a ROWSET variable type in plpgsql that would function like a > > temporary but in-memory table. Support for it would have to be added for > > plpgsql's SELECT, UPDATE

Re: [GENERAL] Wishlist?

2005-07-21 Thread Martijn van Oosterhout
On Thu, Jul 21, 2005 at 07:10:03PM +1000, Ezequiel Tolnay wrote: > * Stored procedures: Although similar to functions, SP always return an > execution status rather than a result, but provide the caller with the > equivalent of running several sql instructions, as if running a script. > Unlike f

Re: [GENERAL] Converting MySQL tinyint to PostgreSQL

2005-07-21 Thread Dawid Kuroczko
On 7/19/05, Jim C. Nasby <[EMAIL PROTECTED]> wrote: > > CREATE TABLE sample1 ( > > a boolean, > > b int, > > c boolean > > ); > > > > ...it will take more storage than: > > > > CREATE TABLE sample2 ( > > b int, > > a boolean, > > c boolean > > ); > > > Actually, I believe th

Re: [GENERAL] TRUNCATE locking problem

2005-07-21 Thread Joe Maldonado
Tom Lane wrote: Joe Maldonado <[EMAIL PROTECTED]> writes: While researching this locking issue I got some of the logs and found that in one of the cases there was a SELECT running for a long time, about 2 hours. This select statement does not usually take more than a few seconds though, i

Re: [GENERAL] Wishlist?

2005-07-21 Thread Tom Lane
Bruno Wolff III <[EMAIL PROTECTED]> writes: > On Thu, Jul 21, 2005 at 19:10:03 +1000, >> * Allow to optionally prevent overloading in functions, thus allowing >> the CREATE OR REPLACE command replace the function despite having >> different parameters > What happens if there is more than one exi

Re: [GENERAL] TRUNCATE locking problem

2005-07-21 Thread Tom Lane
Joe Maldonado <[EMAIL PROTECTED]> writes: > While researching this locking issue I got some of the logs and found > that in one of the cases there was a SELECT running for a long time, > about 2 hours. This select statement does not usually take more than a > few seconds though, it appeared tha

Re: [GENERAL] Wishlist?

2005-07-21 Thread Bruno Wolff III
On Thu, Jul 21, 2005 at 19:10:03 +1000, Ezequiel Tolnay <[EMAIL PROTECTED]> wrote: > > * Stored procedures: Although similar to functions, SP always return an > execution status rather than a result, but provide the caller with the > equivalent of running several sql instructions, as if runnin

Re: [GENERAL] Converting MySQL tinyint to PostgreSQL

2005-07-21 Thread Bruce Momjian
Added to TODO: * Research storing disk pages with no alignment/padding --- Lincoln Yeoh wrote: > I believe that one should leave such on-the-fly disk compression to the > O/S. Postgresql already does compression fo

[GENERAL] ANN: Access2PostrgreSQL Pro v1.0.0 release

2005-07-21 Thread dmsoft
DMSoft Technologies is glad to announce Access2PostrgreSQL Pro v1.0.0 release. It converts Microsoft Access databases to PostgreSQL and vice versa supporting Unicode and allowing user to convert data to PostgreSQL Text file and PHP script. Conversion of Primary keys and Indexes is supported. Here

Re: [GENERAL] Wishlist?

2005-07-21 Thread Ezequiel Tolnay
Jim C. Nasby wrote: http://www.postgresql.org/developer/roadmap Note especially the part about developers scratching their own itch. You can request all you want, but unless a number of developers agree it's a good idea it probably won't make it to the TODO. And stuff can get pulled from the TOD

Re: [GENERAL] TRUNCATE locking problem

2005-07-21 Thread Joe Maldonado
Hello all again... While researching this locking issue I got some of the logs and found that in one of the cases there was a SELECT running for a long time, about 2 hours. This select statement does not usually take more than a few seconds though, it appeared that TRUNCATE was waiting on it

Re: [GENERAL] [HACKERS] No user being created during initdb for OS X

2005-07-21 Thread Michael Fuhr
On Thu, Jul 21, 2005 at 02:38:51PM +1000, Jamie Deppeler wrote: > I am doing it right > /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data How are you determining that "no user is being created"? What are you doing and what's the exact error message? -- Michael Fuhr http://www.fuhr.org/~mfuh

Re: [GENERAL] [HACKERS] No user being created during initdb for OS X

2005-07-21 Thread Dave Cramer
I guess the next obvious question is why do you think there isn't a user? What is the error message ? Dave On 21-Jul-05, at 12:38 AM, Jamie Deppeler wrote: I am doing it right /usr/local/pgsql/bin/initdb -D /usr/local/pgsql/data Thomas F. O'Connell wrote: Do you already have a postgres us

Re: [GENERAL] function execution problem - plpgsql

2005-07-21 Thread Roman Neuhauser
# [EMAIL PROTECTED] / 2005-07-21 10:18:05 +0200: > I have two functions: funcA() drops a row from table A, funcB() drops a > row from table B that references table A. funcA() calls funcB() and > issues a delete command after returning from funcB(). I get an error, > stating that it can not be done,

Re: [GENERAL] RAMFS with Postgres

2005-07-21 Thread Marco Colombo
On Thu, 2005-07-21 at 07:43 +, vinita bansal wrote: > Hi, > > My application is database intensive. I am using 4 processes since I have 4 > processeors on my box. There are times when all the 4 processes write to the > database at the same time and times when all of them will read all at onc

[GENERAL] function execution problem - plpgsql

2005-07-21 Thread Zoltan Bartko
Hello folks I would like to ask the following thing: I have two functions: funcA() drops a row from table A, funcB() drops a row from table B that references table A. funcA() calls funcB() and issues a delete command after returning from funcB(). I get an error, stating that it can not be done,

[GENERAL] Postgres terminates on first connecton

2005-07-21 Thread Andrus
I have Postgres 8 on XP Trying to recover from crash I replaced data subdirectory from backup (no postgres backup avaliable) and re-started Postgres service. After trying to connect to it, Postgres service terminates. How I can recover data from this data directory ? The following information

Re: [GENERAL] RAMFS with Postgres

2005-07-21 Thread vinita bansal
Hi, My application is database intensive. I am using 4 processes since I have 4 processeors on my box. There are times when all the 4 processes write to the database at the same time and times when all of them will read all at once. The database is definitely not read only. Out of the entire d

Re: [GENERAL] Cannot start DB system

2005-07-21 Thread Magnus Hagander
> All, > > I have postgreSQL 8.0 on winXP. This morning at 7:59am, the > db system could not start. > I looked at the pg_log. Started from yesterday 1:30pm, all > log files have this line: > > database system was not properly shut down; automatic > recovery in progress > > Then, I restarted t