Re: [GENERAL] Does PG Support Unicode on Windows?

2005-11-17 Thread Magnus Hagander
> Is there any truth to what this guy is saying? Yes, some. But not much. > > > On the other hand, Postgresql claims that "Windows > does not support > > > Unicode" and you can't have Unicode fields on > postgresql on Windows. > > > This is a big mistake. See: > > > > http://pginstaller.project

Re: [GENERAL] locked backend

2005-11-17 Thread Csaba Nagy
On Wed, 2005-11-16 at 19:41, Tom Lane wrote: > Csaba Nagy <[EMAIL PROTECTED]> writes: > > The situation (diagnosed via pg_stat_activity): one table was locked by > > an update, a VACUUM ANALYZE was running for the same table (triggered by > > autovacuum), and a handful of inserts were waiting on th

Re: [GENERAL] PG 8.1 on Dual XEON with FreeBSD 6.0

2005-11-17 Thread Daniel Kalchev
This is how multiprocessor systems work. You will benefit from the second processor when you run another query in paralell. There is no way as far as I am aware to run single task on both processors (except maybe by using threads?) By the way, Intel processor's Hyperthreading provides not two CP

Re: [GENERAL] Incomplete Startup Packet

2005-11-17 Thread Markus Wollny
Title: [GENERAL] Incomplete Startup Packet ? Hi!   We're getting "incomplete startup packet" messages in our logfiles due to some sort of system probe run by our service provider which checks if PG is still running. In our case they're harmless of course. Are you sure that you're not runni

Re: [GENERAL] Rebranding PostgreSQL

2005-11-17 Thread john.bender
On Wed, 16 Nov 2005 12:50:37 -0800 Chris Browne <[EMAIL PROTECTED]> >I would imagine that if you simply stow components where you >choose to >stow them, and say, "this is part of what we always install for >all >our customers," and never bring OSS up as an issue, they probably >won't notice they

Re: [GENERAL] Rebranding PostgreSQL

2005-11-17 Thread Magnus Hagander
> The silent capabilities of the installer will do nicely in > this regard...I can wrap it into my current NSIS script and > simply call out to the installer. However, I need to create a > empty database and initial user after install is complete. Is > there any way to "hook" custom scripts int

Re: [GENERAL] Wrong rows selected with view

2005-11-17 Thread Bill Moseley
On Wed, Nov 16, 2005 at 11:34:21PM -0500, Tom Lane wrote: > No, I'm saying that the underlying data (the join result before applying > DISTINCT ON) looks like this: > > bill=# select "class".id, person.id AS person_id > bill-#FROM "class", "location", region, person, instructors > bill-# WH

Re: [GENERAL] PREPARE TRANSACTION and webapps

2005-11-17 Thread Lincoln Yeoh
At 06:04 PM 11/16/2005 +0100, Martijn van Oosterhout wrote: On Thu, Nov 17, 2005 at 12:29:25AM +0800, Lincoln Yeoh wrote: > My assumption is that pending transactions (e.g. locks and other metainfo) > will take much less memory than database backends. They make take less memory but they take ma

[GENERAL] strange behavior on 8.1

2005-11-17 Thread Csaba Nagy
Hi all, I have observed a strange behavior on 8.1 of an insert statement into a temporary table done from a delete trigger. I've attached a test case. Observe that the NOTICE saying the rows were inserted occurs all 5 times for the 8.0.3 server and only for the first 2 times for the 8.1 installati

Re: [GENERAL] Performance of a view

2005-11-17 Thread Thomas F. O'Connell
On Nov 14, 2005, at 7:40 PM, John McCawley wrote: I have a view which is defined as follows: //- SELECT tbl_claim.claim_id, count(tbl_invoice.invoice_id) AS count, min(tbl_invoice.invoicedate) AS invoicedate FROM tbl_claim LEFT JOIN tbl_invoice ON tbl_claim.claim

[GENERAL] Very slow queries on 8.1

2005-11-17 Thread David Rysdam
I'm porting an application from Sybase and I've noticed that similar application functions take 2 to 3 times longer on postgres than they used to on the same machine running under Sybase. I've tried changing various "performance tuning" parameters, such as shared_buffers, effective_cache_size,

Re: [GENERAL] strange behavior on 8.1

2005-11-17 Thread Csaba Nagy
Ok, I found a fix which works for me: don't use ON COMMIT DELETE ROWS on the temporary tables, but explicitly delete the rows once processed. However, I think it should work with ON COMMIT DELETE ROWS too, and it works fine indeed in 8.0.3. Cheers, Csaba. On Thu, 2005-11-17 at 16:22, Csaba Nagy w

Re: [GENERAL] Most significant digit number formatting

2005-11-17 Thread codeWarrior
If it is a numeric data column -- you probably want to use the "round" function: SELECT round(1200.01, 3); SELECT round(12.009, 2); "Berend Tober" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Say I want to format calculated numeric output to uniformly have a > specific number

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread codeWarrior
What is the data type for "signum" ??? "David Rysdam" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > I'm porting an application from Sybase and I've noticed that similar > application functions take 2 to 3 times longer on postgres than they used > to on the same machine running

Re: [GENERAL] Performance of a view

2005-11-17 Thread John McCawley
I'd be curious to see what would happen if you added claimnum as a field in your view. I don't have a complete understanding of the postgres internals in terms of how it is able to push outer clauses down in to its views, but I think it might be able to optimize in that fashion if it is a

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread Tom Lane
David Rysdam <[EMAIL PROTECTED]> writes: > Right now, I'm working on a test case that involves a table with ~360k > rows called "nb.sigs". My sample query is: > select * from nb.sigs where signum > 25 > With no index, explain says this query costs 11341. After CREATE INDEX > on the signum f

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread David Rysdam
int4, not null and the index is unique. I even tried clustering on it to no avail. codeWarrior wrote: What is the data type for "signum" ??? "David Rysdam" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] I'm porting an application from Sybase and I've noticed that similar

Re: [GENERAL] PREPARE TRANSACTION and webapps

2005-11-17 Thread Greg Stark
Lincoln Yeoh writes: > At 06:04 PM 11/16/2005 +0100, Martijn van Oosterhout wrote: > > >On Thu, Nov 17, 2005 at 12:29:25AM +0800, Lincoln Yeoh wrote: > > > My assumption is that pending transactions (e.g. locks and other metainfo) > > > will take much less memory than database backends. > > > >T

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread David Rysdam
Tom Lane wrote: David Rysdam <[EMAIL PROTECTED]> writes: Right now, I'm working on a test case that involves a table with ~360k rows called "nb.sigs". My sample query is: select * from nb.sigs where signum > 25 With no index, explain says this query costs 11341. After CREATE INDEX on

Re: [GENERAL] Most significant digit number formatting

2005-11-17 Thread Berend Tober
codeWarrior wrote: If it is a numeric data column -- you probably want to use the "round" function: SELECT round(1200.01, 3); SELECT round(12.009, 2); Interesting. I had tried that. After your message I tried again and encountered this interesting anomaly: while the ROUND function used i

Re: [GENERAL] strange behavior on 8.1

2005-11-17 Thread Tom Lane
Csaba Nagy <[EMAIL PROTECTED]> writes: > I have observed a strange behavior on 8.1 of an insert statement into a > temporary table done from a delete trigger. In an assert-enabled build this dumps core, so I'd say you've found a bug ... regards, tom lane -

Re: [GENERAL] Incomplete Startup Packet

2005-11-17 Thread Mott Leroy
Markus Wollny wrote: We're getting "incomplete startup packet" messages in our logfiles due to some sort of system probe run by our service provider which checks if PG is still running. In our case they're harmless of course. Are you sure that you're not running something along those lines, to

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread Tom Lane
David Rysdam <[EMAIL PROTECTED]> writes: > Right, it's about 100k rows and it is through libpq (pgadmin in this > case, but my app uses libpq from pgtcl). Is there a way to tell libpq > to not do what it "likes" and do what I need instead? The only way ATM is to declare a cursor on the query an

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread Bruno Wolff III
On Thu, Nov 17, 2005 at 11:31:27 -0500, David Rysdam <[EMAIL PROTECTED]> wrote: > Right, it's about 100k rows and it is through libpq (pgadmin in this > case, but my app uses libpq from pgtcl). Is there a way to tell libpq > to not do what it "likes" and do what I need instead? I didn't see

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread Martijn van Oosterhout
On Thu, Nov 17, 2005 at 11:31:27AM -0500, David Rysdam wrote: > Right, it's about 100k rows and it is through libpq (pgadmin in this > case, but my app uses libpq from pgtcl). Is there a way to tell libpq > to not do what it "likes" and do what I need instead? I didn't see > anything in the do

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread David Rysdam
Bruno Wolff III wrote: On Thu, Nov 17, 2005 at 11:31:27 -0500, David Rysdam <[EMAIL PROTECTED]> wrote: Right, it's about 100k rows and it is through libpq (pgadmin in this case, but my app uses libpq from pgtcl). Is there a way to tell libpq to not do what it "likes" and do what I need in

[GENERAL] Moving from MySQL to PostgreSQL with Ruby on Rails.

2005-11-17 Thread Peter Michaux
Hi, I'm just new to the PostgreSQL world. I've been using MySQL but I want to develop a Ruby on Rails application that can be installed on either MySQL or PostgreSQL. I don't know how much the DDL dialects vary between them. At the moment I am interested in the options on a table like UTF-8. In My

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread David Rysdam
Martijn van Oosterhout wrote: On Thu, Nov 17, 2005 at 11:31:27AM -0500, David Rysdam wrote: Right, it's about 100k rows and it is through libpq (pgadmin in this case, but my app uses libpq from pgtcl). Is there a way to tell libpq to not do what it "likes" and do what I need instead? I di

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread Michael Fuhr
On Thu, Nov 17, 2005 at 10:38:50AM -0500, David Rysdam wrote: > I'm porting an application from Sybase and I've noticed that similar > application functions take 2 to 3 times longer on postgres than they > used to on the same machine running under Sybase. I've tried changing > various "performa

Re: [GENERAL] Moving from MySQL to PostgreSQL with Ruby on Rails.

2005-11-17 Thread Robby Russell
On Thu, 2005-11-17 at 08:48 -0800, Peter Michaux wrote: > Hi, > > I'm just new to the PostgreSQL world. I've been using MySQL but I want > to develop a Ruby on Rails application that can be installed on either > MySQL or PostgreSQL. I don't know how much the DDL dialects vary > between them. At th

Re: [GENERAL] Very slow queries on 8.1

2005-11-17 Thread David Rysdam
Michael Fuhr wrote: On Thu, Nov 17, 2005 at 10:38:50AM -0500, David Rysdam wrote: I'm porting an application from Sybase and I've noticed that similar application functions take 2 to 3 times longer on postgres than they used to on the same machine running under Sybase. I've tried changing

Re: [GENERAL] strange behavior on 8.1

2005-11-17 Thread Tom Lane
Csaba Nagy <[EMAIL PROTECTED]> writes: > Ok, I found a fix which works for me: don't use ON COMMIT DELETE ROWS on > the temporary tables, but explicitly delete the rows once processed. > However, I think it should work with ON COMMIT DELETE ROWS too, and it > works fine indeed in 8.0.3. I found th

[GENERAL] unsubscribe pgsql-general

2005-11-17 Thread Peter Atkins
unsubscribe pgsql-general

Re: [GENERAL] Rebranding PostgreSQL

2005-11-17 Thread Steve Atkins
On Wed, Nov 16, 2005 at 02:19:28PM -0500, Vivek Khera wrote: > On Nov 16, 2005, at 1:09 PM, <[EMAIL PROTECTED]> > <[EMAIL PROTECTED]> wrote: > > >There are a few obstinate anti-open source customers though, that > >prevent my plan from moving forward. They've bought into whatever > >hype they've

[GENERAL] Partial foreign keys, check constraints and inheritance

2005-11-17 Thread Eric E
Hi all, In my database application, I've repeatedly encountered a particular issue, and I'm not sure I'm addressing it well, so I'd like suggestions on how to deal with it. The problem is that I need something like a partial foreign key - a foreign key where, based on field1, in some rows

Re: [GENERAL] Partial foreign keys, check constraints and inheritance

2005-11-17 Thread Jaime Casanova
On 11/17/05, Eric E <[EMAIL PROTECTED]> wrote: > Hi all, >In my database application, I've repeatedly encountered a particular > issue, and I'm not sure I'm addressing it well, so I'd like suggestions > on how to deal with it. The problem is that I need something like a > partial foreign key -

Re: [GENERAL] Most significant digit number formatting

2005-11-17 Thread codeWarrior
Got it now Delphi is interfering with the numeric formatting -- obviously... this is considered normal for numeric data types that trailing zeroes are removed... they are insignificant anyway To solve your issue: I guess the thing to do is to cast the result as text to preserve the f

Re: [GENERAL] Partial foreign keys, check constraints and inheritance

2005-11-17 Thread Eric E
maybe you can solve it adding a new col and allow both to contain null values. if these are not mutually exclusive you can avoid a check if they are check that if one has a non-null value other has null... I did think about that, but I disliked the idea of two fields of nulls for every one fu

Re: [GENERAL] Partial foreign keys, check constraints and inheritance

2005-11-17 Thread Eric E
Eric E wrote: maybe you can solve it adding a new col and allow both to contain null values. if these are not mutually exclusive you can avoid a check if they are check that if one has a non-null value other has null... I did think about that, but I disliked the idea of two fields of nulls

Re: [GENERAL] Moving from MySQL to PostgreSQL with Ruby on Rails.

2005-11-17 Thread David Fetter
On Thu, Nov 17, 2005 at 08:48:45AM -0800, Peter Michaux wrote: > Hi, > > I'm just new to the PostgreSQL world. I've been using MySQL but I want to > develop a Ruby on Rails application that can be installed on either MySQL or > PostgreSQL. I don't know how much the DDL dialects vary between them.

[GENERAL] unsubscribe

2005-11-17 Thread Josel Malixi
unsubscribe __ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Partial foreign keys, check constraints and

2005-11-17 Thread Scott Marlowe
On Thu, 2005-11-17 at 13:36, Eric E wrote: > Eric E wrote: > > >> maybe you can solve it adding a new col and allow both to contain > >> null values. > >> > >> if these are not mutually exclusive you can avoid a check if they are > >> check that if one has a non-null value other has null... > > >

Re: [GENERAL] Moving from MySQL to PostgreSQL with Ruby on Rails.

2005-11-17 Thread Bruno Wolff III
On Thu, Nov 17, 2005 at 09:23:51 -0800, Robby Russell <[EMAIL PROTECTED]> wrote: > > CREATE TABLE product ( > id SERIAL PRIMARY KEY, > name VARCHAR(255) NOT NULL DEFAULT '', > ); And depending on why you chose VARCHAR(255), you may really want to use TEXT instead. -

[GENERAL] [Fwd: Sun backs open-source database PostgreSQL | Topic: "everything" | ZDNet News Alerts]

2005-11-17 Thread Reid Thompson
Original Message Subject:Sun backs open-source database PostgreSQL | Topic: "everything" | ZDNet News Alerts Date: Thu, 17 Nov 2005 13:10:34 -0800 (PST) From: ZDNet News Alerts<[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: [EMAIL PROTECTED] ==

[GENERAL] Strange slow database

2005-11-17 Thread David Mitchell
Hi all, Recently our databases started to experience a significant slowdown. Queries that were taking 500ms now take up to 20 seconds. An insert can take 150ms or more. This is strange since we are still hitting indexes and we vacuum regularly. Here is the description of our system: A single

Re: [GENERAL] Rebranding PostgreSQL

2005-11-17 Thread Andrew Sullivan
On Wed, Nov 16, 2005 at 03:26:19PM -0800, Joshua D. Drake wrote: > That's easy. The same reason people used to buy Mammoth PostgreSQL (not > the replicator version). Well, yeah-no. Mammoth was coming from someone who was explicitly in the business of selling support for it, and was selling to pe

Re: [GENERAL] Moving from MySQL to PostgreSQL with Ruby on Rails.

2005-11-17 Thread Robby Russell
On Thu, 2005-11-17 at 15:10 -0600, Bruno Wolff III wrote: > On Thu, Nov 17, 2005 at 09:23:51 -0800, > Robby Russell <[EMAIL PROTECTED]> wrote: > > > > CREATE TABLE product ( > > id SERIAL PRIMARY KEY, > > name VARCHAR(255) NOT NULL DEFAULT '', > > ); > > And depending on why you chose VARCH

Re: [GENERAL] Rebranding PostgreSQL

2005-11-17 Thread Robert Treat
On Wed, 2005-11-16 at 17:37, Greg Sabino Mullane wrote: > > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > > > I appreciate what you're trying to do. At the same time, why do you > > think your customers will be more willing to go for John's Database > > than some community product called

Re: [GENERAL] Partial foreign keys, check constraints and inheritance

2005-11-17 Thread Eric E
Scott Marlowe wrote: On Thu, 2005-11-17 at 13:36, Eric E wrote: Eric E wrote: maybe you can solve it adding a new col and allow both to contain null values. if these are not mutually exclusive you can avoid a check if they are check that if one has a non-null value other has null...

Re: [GENERAL] [Fwd: Sun backs open-source database PostgreSQL | Topic: "everything" | ZDNet News Alerts]

2005-11-17 Thread Aaron Glenn
> > *Sun backs open-source database PostgreSQL* > This is going to make PostgreSQL a much easier sell to PHB's (at least, in my experience) aaron.glenn ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] Strange slow database

2005-11-17 Thread Andrew Sullivan
On Fri, Nov 18, 2005 at 10:05:47AM +1300, David Mitchell wrote: > Strangely, this slowdown is on all 12 servers. Perhaps this is a > configuration issue? If anyone has any ideas we'd love to hear them. It sounds like a use-pattern issue. Did something in your use change? Any time you get repea

Re: [GENERAL] Strange slow database

2005-11-17 Thread Tom Lane
David Mitchell <[EMAIL PROTECTED]> writes: > Recently our databases started to experience a significant slowdown. > Queries that were taking 500ms now take up to 20 seconds. An insert can > take 150ms or more. This is strange since we are still hitting indexes > and we vacuum regularly. Here is

Re: [GENERAL] unsubscribe pgsql-general

2005-11-17 Thread felix
On Thu, Nov 17, 2005 at 09:53:26AM -0800, Peter Atkins wrote: > unsubscribe pgsql-general O dear ... I haven't posted this in a while :-) Here's how to unsubscribe: First, ask your Internet Provider to mail you an Unsubscribing Kit. Then follow these directions. The kit will most likely be the

Re: [GENERAL] [Fwd: Sun backs open-source database PostgreSQL | Topic:

2005-11-17 Thread Bruce Momjian
Aaron Glenn wrote: > > > > *Sun backs open-source database PostgreSQL* > > > > This is going to make PostgreSQL a much easier sell to PHB's (at > least, in my experience) Agreed. We now have Sun's blessing, and access to Sun resources. -- Bruce Momjian| http://candle

[GENERAL] Mambo/Joomla (CMS) on PostgreSQL ?

2005-11-17 Thread Leif Jensen
Hi All, I have been using PostgreSQL ever since version 6.3 and are very happy about it. Many of our company corporate registration systems are based on PostgreSQL and are web based using PHP. Unfortunately someone has decided that our (external) homepage(s) are gonna use the Mambo CMS sy

Re: [GENERAL] unsubscribe pgsql-general

2005-11-17 Thread Terry Lee Tucker
:oD On Thursday 17 November 2005 02:45 pm, [EMAIL PROTECTED] saith: > On Thu, Nov 17, 2005 at 09:53:26AM -0800, Peter Atkins wrote: > > unsubscribe pgsql-general > > O dear ... I haven't posted this in a while :-) > > Here's how to unsubscribe: > > First, ask your Internet Provider to mail you an

Re: [GENERAL] Strange slow database

2005-11-17 Thread David Mitchell
Tom Lane wrote: Have you checked to see whether you are encountering table or index bloat? Your vacuuming policy doesn't sound unreasonable on its face, but there's no substitute for actually looking at physical file sizes when you aren't sure what's wrong. Try doing a pg_dump and reload into

Re: [GENERAL] Strange slow database

2005-11-17 Thread Tom Lane
David Mitchell <[EMAIL PROTECTED]> writes: > I dumped and loaded then wrote a script to compare the sizes of tables. > I noticed that a few of the indexes, especially the indexes on the > 15million table shrunk a lot (~50% reduction - ~400Mb). Also, the > pg_attribute_relid_attnam_index index sh

Re: [GENERAL] Strange slow database

2005-11-17 Thread David Mitchell
Tom Lane wrote: Also, the pg_attribute_relid_attnam_index index shrank loads, to 1% of its original size. Hm, better check whether your vacuuming policy is taking care of the system catalogs ... Yes, fair point. So I did: 18/11/2005 03:02:29 INFO: vacuuming "pg_catalog.pg_attribute" 18/11/

Re: [GENERAL] Strange slow database

2005-11-17 Thread Tom Lane
David Mitchell <[EMAIL PROTECTED]> writes: > 18/11/2005 03:02:29 INFO: index "pg_attribute_relid_attnam_index" now > contains 2861 row versions in 11900 pages > 18/11/2005 03:02:29 DETAIL: 11834 index pages have been deleted, 11834 > are currently reusable. > 18/11/2005 03:02:30 INFO: index "

Re: [GENERAL] Partial foreign keys, check constraints and inheritance

2005-11-17 Thread Greg Stark
> On 11/17/05, Eric E <[EMAIL PROTECTED]> wrote: > > > > What's the best way to do this? My immediate reaction is that I want a > > partial foreign key, but perhaps this is not a good way to go about such > > a design. Normally I just have multiple columns with all but one NULL. Alternatively

Re: [GENERAL] Rebranding PostgreSQL

2005-11-17 Thread Christopher Browne
> On Wed, Nov 16, 2005 at 02:19:28PM -0500, Vivek Khera wrote: >> On Nov 16, 2005, at 1:09 PM, <[EMAIL PROTECTED]> >> <[EMAIL PROTECTED]> wrote: >> >> >There are a few obstinate anti-open source customers though, that >> >prevent my plan from moving forward. They've bought into whatever >> >hype

Re: [GENERAL] Rebranding PostgreSQL

2005-11-17 Thread Christopher Browne
>> Maybe he is going to call it "Orakle"? :) > > I was thinking he could call it "my-sql"... Call it "Your SQL" :-) -- "cbbrowne","@","gmail.com" http://cbbrowne.com/info/slony.html "...Roxanne falls in love with Christian, a chevalier in Cyrano's regiment who hasn't got the brains God gave an ec

Re: [GENERAL] Strange slow database

2005-11-17 Thread David Mitchell
Tom Lane wrote: I speculate that you did a VACUUM FULL on it recently. You speculate right, we do a vacuum full every sunday night as a safety net. So the vacuum full was a week old. The condition of the indexes suggests strongly that you've not been vacuuming pg_attribute often enough (and

Re: [GENERAL] Trouble with recursive trigger

2005-11-17 Thread Tom Lane
Justin Hawkins <[EMAIL PROTECTED]> writes: > I'm having trouble with the DELETE. When deleting a row three things > need to happen: > o recursively DELETE all children posts to preserve data integrity > o decrement the number of replies of the parent post (if it exists) > o delete itself This has