Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Lincoln Yeoh
At 01:40 PM 12/16/2005 -0500, Tom Lane wrote: Nobody's said anything about giving up locale-sensitive sorting. The question is about locale-sensitive equality: does it really make sense that 'tty' = 'tyty'? Would your answer change in the context '/dev/tty' = '/dev/tyty'? Are you willing to *

Re: [GENERAL] client_encoding values

2005-12-16 Thread Bruce Momjian
wrote: > Hello! > > Please, help. I need to get complete list of 'client_encoding' > values supported by the PostgreSQL Server. Can I somehow get these > values using select query or function call? Uh, we have a pg_conversion table, but I don't know how to tell which are av

Re: [GENERAL] Negative offsets

2005-12-16 Thread Jim C. Nasby
On Fri, Dec 16, 2005 at 07:14:42PM -0500, Bruce Momjian wrote: > Actually we allow negative offsets and limits. Seems we should disallow > them. Yes, it would be interesting if they did offsets relative to the > end of the result set. Either interesting or a great way to introduce bugs... AFAIK

Re: [GENERAL] Negative offsets

2005-12-16 Thread Bruce Momjian
CSN wrote: > I was playing around with negative offsets: > > select * from table1 order by col1 offset -5 limit 25; > select * from table1 order by col1 offset -25 limit > 25; > select * from table1 order by col1 offset -250 limit > 25; > > They all return the same resultset (offset 0). Is > ther

Re: [GENERAL] Versioning Schema/Stored Procedures

2005-12-16 Thread Jim C. Nasby
The way I handle this is to version the entire schema and have scripts that know how to upgrade from one version to another. If you think about it, you really want/need everything in the database to be designed to run together anyway. I've yet to find a case where I'd want some of the stuff in the

Re: [GENERAL] How to store the time zone with a timestamp

2005-12-16 Thread Jim C. Nasby
On Fri, Dec 16, 2005 at 11:25:22PM +0100, Martijn van Oosterhout wrote: > On Fri, Dec 16, 2005 at 03:18:04PM -0600, Jim C. Nasby wrote: > > BTW, if you wanted a more integrated solution, you could build a custom > > type that would store the timestamp info. There's a good chance such > > code would

[GENERAL] Versioning Schema/Stored Procedures

2005-12-16 Thread vishal saberwal
hi all, We installed a first version (1.0.0.1) of our schema. then came a few patches we had for a few stored procedures and tables (1.0.0.2). Then even more (1.0.0.3) (1.0.0.4). Some chose to upgrade to version 1.0.0.3 and stick to it, while some others chose to upgrade to 1.0.0.4. Now when i ha

Re: [GENERAL] How to store the time zone with a timestamp

2005-12-16 Thread Martijn van Oosterhout
On Fri, Dec 16, 2005 at 03:18:04PM -0600, Jim C. Nasby wrote: > BTW, if you wanted a more integrated solution, you could build a custom > type that would store the timestamp info. There's a good chance such > code would end up being what's used in the server as the official type, > too. Well, I wr

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Chris Browne
[EMAIL PROTECTED] (Madison Kelly) writes: >In this case I can't predict what a given install's postgresql > will be used for (outside of my program) because it is meant for > general distribution (it's a backup program). This obviously makes > things a lot more complicated. :p No, it oughtn't.

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Greg Stark
Tom Lane <[EMAIL PROTECTED]> writes: > The other approach we could take is to define text comparison as > yielding equality only for bitwise-equal strings. If strcoll() returns > zero then ask strcmp() for a second opinion. Fwiw this has come up before on this list and it was discovered this

Re: [GENERAL] 8.1 build on Solaris has LATIN9?

2005-12-16 Thread Jerry Sievers
Please forgive the empty reply preceeding this note. Tom Lane <[EMAIL PROTECTED]> writes: > Jerry Sievers <[EMAIL PROTECTED]> writes: > > Hi. I am curious how/why the Pg 8.1.1 build on Solaris > > SunOS $hostname 5.9 Generic sun4u sparc SUNW,Ultra-250 > > chose LATIN9 charset as default. >

Re: [GENERAL] Toolkit for creating editable grid

2005-12-16 Thread Chris Browne
"Andrus" <[EMAIL PROTECTED]> writes: >> Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's >> a bit immature, but it might do what you want. The dreaded MS >> Access can do what you describe in about 4 minutes... > > Postgres lacks easy GUI frontend with report generation capabili

Re: [GENERAL] Fetch statements

2005-12-16 Thread Michael Fuhr
On Fri, Dec 16, 2005 at 08:49:32PM +, Carlos Benkendorf wrote: > I would like to say "take longer than" with "bad" statements... > but I´m only logging statements that take longer than 700 ms > and DECLARE statements are not logged of course. > > I think the only way in my case is logging

Re: [GENERAL] How to store the time zone with a timestamp

2005-12-16 Thread Jim C. Nasby
BTW, if you wanted a more integrated solution, you could build a custom type that would store the timestamp info. There's a good chance such code would end up being what's used in the server as the official type, too. On Fri, Dec 16, 2005 at 08:57:04AM -0800, Andrew B. Young wrote: > I just realiz

FW: [GENERAL] PL/pgSQL Function Help

2005-12-16 Thread Niblett, David A
Well that kills two birds. I completely didn't understand the difference between SETOF and just RECORD return types. That fixed it up, and for the record here is what the function looks like now. CREATE TYPE myrec AS ( id int ); CREATE OR REPLACE FUNCTION test(x int) RETURNS SETOF myrec

Re: [GENERAL] Transacciones Anidadas

2005-12-16 Thread Karsten Hilbert
On Fri, Dec 16, 2005 at 03:23:07PM -0400, Juan Garcés Bustamante wrote: > Hola Guten Abend ! > Estoy trabajando con Postgres 8.0.3 en Ubuntu. Hm, ich benutze Debian, mit PostgreSQL 7.4 in einem Cluster. Wie das bei Ubuntu so funktioniert, weiß ich nicht so richtig. > Necesito realizar transaccio

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Madison Kelly
Tom Lane wrote: Madison Kelly <[EMAIL PROTECTED]> writes: Oh shoot, I really wasn't very verbose, was I? Sorry about that. [ default pg_hba.conf with only "ident" lines ] Ah, that explains your question about whether passwords were good for anything at all. With this pg_hba.conf they aren't

Re: [GENERAL] Transacciones Anidadas

2005-12-16 Thread Florian G. Pflug
Juan Garcés Bustamante wrote: Hola Estoy trabajando con Postgres 8.0.3 en Ubuntu. Necesito realizar transacciones anidadas, pero no logro que se aborten transacciones intermedias al abortarse una superior. The main language spoken here is english. If you ask your questions in english, you'll h

Re: [GENERAL] Fetch statements

2005-12-16 Thread Carlos Benkendorf
Sorry,   I would like to say "take longer than" with "bad" statements... but I´m only logging statements that take longer than 700 ms and DECLARE statements are not logged of course.   I think the only way in my case is logging all statements... Am I right? Or is there another way?   Thanks

Re: [GENERAL] Transacciones Anidadas

2005-12-16 Thread Michael Fuhr
On Fri, Dec 16, 2005 at 03:23:07PM -0400, Juan Garcs Bustamante wrote: > Estoy trabajando con Postgres 8.0.3 en Ubuntu. Jaime Casanova has already mentioned this list is in English and that questions in Spanish should go to pgsql-es-ayuda. > Necesito realizar transacciones anidadas, pero no logro

Re: [GENERAL] PL/pgSQL Function Help

2005-12-16 Thread Michael Fuhr
On Fri, Dec 16, 2005 at 03:30:01PM -0500, Tom Lane wrote: > "Niblett, David A" <[EMAIL PROTECTED]> writes: > > Is there no way in Postgres that I can simply not return > > anything so I show zero rows? > > Make the function return SETOF myrec not just myrec. Then you > can return zero or one (or

Re: [GENERAL] PL/pgSQL Function Help

2005-12-16 Thread Tom Lane
"Niblett, David A" <[EMAIL PROTECTED]> writes: > Is there no way in Postgres that I can simply not return > anything so I show zero rows? Make the function return SETOF myrec not just myrec. Then you can return zero or one (or more) myrec's. regards, tom lane ---

Re: [GENERAL] 8.1 build on Solaris has LATIN9?

2005-12-16 Thread Tom Lane
Jerry Sievers <[EMAIL PROTECTED]> writes: > Hi. I am curious how/why the Pg 8.1.1 build on Solaris > SunOS $hostname 5.9 Generic sun4u sparc SUNW,Ultra-250 > chose LATIN9 charset as default. It probably deduced that from the locale setting (LANG or LC_ALL) that initdb was run in. Try "sho

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Tom Lane
Madison Kelly <[EMAIL PROTECTED]> writes: > Oh shoot, I really wasn't very verbose, was I? Sorry about that. > [ default pg_hba.conf with only "ident" lines ] Ah, that explains your question about whether passwords were good for anything at all. With this pg_hba.conf they aren't --- the server wi

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Madison Kelly
Tom Lane wrote: Madison Kelly <[EMAIL PROTECTED]> writes: May I ask then? What *is* considered "best practices" for securing a database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's default values, is there any real point to having a password on a postgresql user account?

[GENERAL] PL/pgSQL Function Help

2005-12-16 Thread Niblett, David A
I've got an issue with a plpgsql function and I have not been able to find any reference to this situation in searching. I hope that someone can point me in the correct direction. I'm running v8.0.3 and assuming a test set up of: - CREATE TYPE myrec AS ( id int );

Re: [GENERAL] Migration tool from MS SQL server 7.0

2005-12-16 Thread Qingqing Zhou
""dfx"" <[EMAIL PROTECTED]> wrote > Dear Sirs, > it exist some tool to convert a SQL script generated from MS SQL 7 to the > syntax > compatible with Postgresql v. 8.x.x? In general, I bet no - since some functionalities in SQL Server is different from ours, say TableSpace. Regards, Qingqing

[GENERAL] 8.1 build on Solaris has LATIN9?

2005-12-16 Thread Jerry Sievers
Hi. I am curious how/why the Pg 8.1.1 build on Solaris SunOS $hostname 5.9 Generic sun4u sparc SUNW,Ultra-250 chose LATIN9 charset as default. Of course we can override this and might if there's a good reason move to LATIN9 as default. Afraid though of having anything go flakey when

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Madison Kelly
Martijn van Oosterhout wrote: On Fri, Dec 16, 2005 at 02:09:52PM -0500, Madison Kelly wrote: May I ask then? What *is* considered "best practices" for securing a database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's default values, is there any real point to having a password

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Tom Lane
Madison Kelly <[EMAIL PROTECTED]> writes: > May I ask then? What *is* considered "best practices" for securing a > database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's > default values, is there any real point to having a password on a > postgresql user account? Well, if ther

Re: [GENERAL] Toolkit for creating editable grid

2005-12-16 Thread Andrus
> Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit > immature, but it might do what you want. The dreaded MS Access > can do what you describe in about 4 minutes... Postgres lacks easy GUI frontend with report generation capabilities like Access. To fix this pgAdmin shou

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Martijn van Oosterhout
On Fri, Dec 16, 2005 at 02:09:52PM -0500, Madison Kelly wrote: > May I ask then? What *is* considered "best practices" for securing a > database in PostgreSQL? Assuming I leave the 'pg_hba.conf' file at it's > default values, is there any real point to having a password on a > postgresql user ac

Re: [GENERAL] Toolkit for creating editable grid

2005-12-16 Thread Scott Marlowe
On Fri, 2005-12-16 at 13:11, Florian G. Pflug wrote: > Andrus wrote: > > Florian, > > > > your reply is not is not in news.postgresql.org server. > > How to receive all messages using news.postgresql.org server ? It is tedious > > to check the newsgroup archives using web interface also. > > Bad,

Re: [GENERAL] Toolkit for creating editable grid

2005-12-16 Thread Andrus
Florian, >> your reply is not is not in news.postgresql.org server. > > Bad, but I guess there is nothing I can do about that... I'm subscriber > of the mailinglist, not of the newsgroup... Sorry.. Your messages are digitally signed. Maybe this prevents them from distributing using news protocol

[GENERAL] Transacciones Anidadas

2005-12-16 Thread Juan Garcés Bustamante
Hola Estoy trabajando con Postgres 8.0.3 en Ubuntu. Necesito realizar transacciones anidadas, pero no logro que se aborten transacciones intermedias al abortarse una superior. Ejemplo: BEGIN WORK; INSERT INTO mitabla VALUES (1); BEGIN TRANSACTION; INSERT INTO mitabla VALUES (2); I

Re: [GENERAL] Toolkit for creating editable grid

2005-12-16 Thread Florian G. Pflug
Andrus wrote: Florian, your reply is not is not in news.postgresql.org server. How to receive all messages using news.postgresql.org server ? It is tedious to check the newsgroup archives using web interface also. Bad, but I guess there is nothing I can do about that... I'm subscriber of the m

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf,

2005-12-16 Thread Madison Kelly
Tom Lane wrote: Madison Kelly <[EMAIL PROTECTED]> writes: I want to find a way to let the user set the password on the new database and have postgres actually ask for it without editing the default 'pg_hba.conf' file, if at all possible. There is no such animal as a "database password" in

Re: [GENERAL] Getting a DB password to work without editing pg_hba.conf, possible?

2005-12-16 Thread Tom Lane
Madison Kelly <[EMAIL PROTECTED]> writes: > I want to find a way to let the user set the password on the new > database and have postgres actually ask for it without editing the > default 'pg_hba.conf' file, if at all possible. There is no such animal as a "database password" in PG. There are

Re: [GENERAL] Fetch statements

2005-12-16 Thread Michael Fuhr
On Fri, Dec 16, 2005 at 06:28:03PM +, Carlos Benkendorf wrote: > I´m trying to discover some bad SQL statements in our environment > but the logs are not helping too much... they say we´ve some very > bad fetch statements but I don´t know how to discover the > corresponding SELECT statement...

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Tom Lane
Martijn van Oosterhout writes: > On Fri, Dec 16, 2005 at 01:06:58PM -0500, Tom Lane wrote: >> Ah. So we could redefine hashtext() to return the hash of the strxfrm >> value. Slow, but a lot better than giving up hash join and hash >> aggregation altogether... > Not to put too fine a point on it

[GENERAL] Getting a DB password to work without editing pg_hba.conf, possible?

2005-12-16 Thread Madison Kelly
Hi all, I am working on an installer for my program that creates a postgres database and user (the installer is written in perl and runs as 'root'). I want to find a way to let the user set the password on the new database and have postgres actually ask for it without editing the default '

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Tom Lane
Martijn van Oosterhout writes: > FWIW, here's some links to Microsoft and MySQL dealing with the same > issue, so we're not alone here. Hungarian seems to be a complex > language to sort, but it seems that glibc is right in this case. The mysql bug link has a fairly detailed description, but it d

[GENERAL] Fetch statements

2005-12-16 Thread Carlos Benkendorf
Hi,   I´m trying to discover some bad SQL statements in our environment but the logs are not helping too much... they say we´ve some very bad fetch statements but I don´t know how to discover the corresponding SELECT statement...  How can I find them?   Some example: <10.0.1.122(3821) 5072 200

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Russ Brown
On Fri, 16 Dec 2005 11:59:48 -0600, Csaba Nagy <[EMAIL PROTECTED]> wrote: On Fri, 2005-12-16 at 18:52, Tom Lane wrote: Csaba Nagy <[EMAIL PROTECTED]> writes: > ... So "tyty" and "tty" could be arguably both taken as double "ty", > except that the official form is "tty"... but from a pronunciati

Re: [GENERAL] Debug user lib for user define data type

2005-12-16 Thread Ale Raza
OK, thanks meneer! Ale -Original Message- From: Martijn van Oosterhout [mailto:[EMAIL PROTECTED] Sent: Thursday, December 15, 2005 11:35 AM To: Ale Raza Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Debug user lib for user define data type On Wed, Dec 14, 2005 at 11:52:19AM -0

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Martijn van Oosterhout
On Fri, Dec 16, 2005 at 01:06:58PM -0500, Tom Lane wrote: > Ah. So we could redefine hashtext() to return the hash of the strxfrm > value. Slow, but a lot better than giving up hash join and hash > aggregation altogether... Not to put too fine a point on it, but either you want locale-sensetive

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Martijn van Oosterhout
On Fri, Dec 16, 2005 at 06:54:15PM +0100, Martijn van Oosterhout wrote: > That's true, in the sense that unconverted strings are not hashable. > This is what strxfrm was created for, to return the sorting key for a > string. A quick C program demonstrates that indeed in that locale these > two stri

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Tom Lane
Martijn van Oosterhout writes: > On Fri, Dec 16, 2005 at 12:12:08PM -0500, Tom Lane wrote: >> Perhaps the fast-path check is a bad idea, but fixing this is not just >> a matter of removing that. If we subscribe to strcoll's worldview then >> we have to conclude that *text strings are not hashable

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Csaba Nagy
On Fri, 2005-12-16 at 18:52, Tom Lane wrote: > Csaba Nagy <[EMAIL PROTECTED]> writes: > > ... So "tyty" and "tty" could be arguably both taken as double "ty", > > except that the official form is "tty"... but from a pronunciation point > > of view they ARE equivalent in hungarian. > > That's fair

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Martijn van Oosterhout
On Fri, Dec 16, 2005 at 12:12:08PM -0500, Tom Lane wrote: > Perhaps the fast-path check is a bad idea, but fixing this is not just > a matter of removing that. If we subscribe to strcoll's worldview then > we have to conclude that *text strings are not hashable*, because > strings that should be "

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Tom Lane
Csaba Nagy <[EMAIL PROTECTED]> writes: > ... So "tyty" and "tty" could be arguably both taken as double "ty", > except that the official form is "tty"... but from a pronunciation point > of view they ARE equivalent in hungarian. That's fair enough, but the question is should they be taken as equiv

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Tom Lane
Michael Fuhr <[EMAIL PROTECTED]> writes: > I don't know if this is related or not, but did the following issue > from a year ago ever get resolved? We were wondering then if the > Hungarian locale on some platforms might be causing problems. AFAIR we never did figure that one out. I wasn't able

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Csaba Nagy
On Fri, 2005-12-16 at 18:12, Tom Lane wrote: [snip] > I don't know anything about hu_HU comparison rules, but it appears that > strcoll() believes that these two strings should be considered equal. > Is that sane? It is sane in a way, as the "ty" combination is pronounced together as a single con

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Michael Fuhr
On Fri, Dec 16, 2005 at 12:12:08PM -0500, Tom Lane wrote: > Thanks. After digging through it, I can exhibit the problem: in hu_HU > locale and LATIN2 encoding, on a Linux box (Fedora Core 4, but probably > all glibc installations will do this), I get I don't know if this is related or not, but di

Re: [GENERAL] is this a bug or I am blind?

2005-12-16 Thread Tom Lane
Mage <[EMAIL PROTECTED]> writes: > Tom, I can send you the data I dumped and you can try it out with same > settings. I trust you. Thanks. After digging through it, I can exhibit the problem: in hu_HU locale and LATIN2 encoding, on a Linux box (Fedora Core 4, but probably all glibc installations

[GENERAL] How to store the time zone with a timestamp

2005-12-16 Thread Andrew B. Young
I just realized that the type "timestamp with time zone" does NOT store the time zone! (And I just found this support list to look for help.) I am developing an application that stores power (watts) readings from meters located around the world (abridged)-- power_meter_id integer NOT NULL,

Re: [GENERAL] Toolkit for creating editable grid

2005-12-16 Thread Eric E
Has anyone used OpenOffice Base? Just a thought. Or Rekall - it's a bit immature, but it might do what you want. The dreaded MS Access can do what you describe in about 4 minutes... EE > I'm in progress of selecting development platform. pgAdmin3 is written in C++, using the cross-plattform

Re: [GENERAL] Toolkit for creating editable grid

2005-12-16 Thread Andrus
Florian, your reply is not is not in news.postgresql.org server. How to receive all messages using news.postgresql.org server ? It is tedious to check the newsgroup archives using web interface also. > > I'm in progress of selecting development platform. >pgAdmin3 is written in C++, using the cr

[GENERAL] Migration tool from MS SQL server 7.0

2005-12-16 Thread dfx
Dear Sirs,   it exist some tool to convert a SQL script generated from MS SQL 7 to the syntax compatible with Postgresql v. 8.x.x?   Best regards.   Domenico