[GENERAL] array quotation problem

2006-12-26 Thread Sim Zacks
select version(); "PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC i686-pc-linux-gnu-gcc (GCC) 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)" In short: Does anyone have any idea of how to put non escaped quotes into a text array element in the same way that the system puts no

Re: [GENERAL] ORDER BY col is NULL in UNION causes error?

2006-12-26 Thread Tom Lane
Michael Glaesemann <[EMAIL PROTECTED]> writes: > On Dec 26, 2006, at 18:39 , Mike Benoit wrote: >> ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of >> the result columns > Even though state is a column in both tables, the order by is using > an expression, rather than a colu

Re: [GENERAL] ORDER BY col is NULL in UNION causes error?

2006-12-26 Thread Michael Glaesemann
On Dec 26, 2006, at 18:39 , Mike Benoit wrote: Fails - select * from income_tax_rate_us UNION select * from income_tax_rate_us order by state is null; ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns Even though state is a column

[GENERAL] ORDER BY col is NULL in UNION causes error?

2006-12-26 Thread Mike Benoit
I asked about this on IRC and the helpful people online at the time didn't seem to have an explanation. Works - select * from income_tax_rate_us order by state is null; Works - select * from income_tax_rate_us UNION select * from income_tax_rate_us order

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Marc Evans
On Tue, 26 Dec 2006, Ben wrote: On Dec 26, 2006, at 7:30 AM, Marc Evans wrote: What I have not been able to come up with a good semi-general purpose solution to is cross-data-center HA. Why does log shipping not work for you? Well, it may, but is short, I believe that this comes down to

Re: [GENERAL] NEED URGENT HELP....

2006-12-26 Thread Iannsp
Martijn van Oosterhout escreveu: On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado wrote: WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3) Could'nt he use an array in this 3rd parameter? I think so, if it's written: AND BOOK_NO = ANY($3) Have a nic

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Ben
On Dec 26, 2006, at 7:30 AM, Marc Evans wrote: What I have not been able to come up with a good semi-general purpose solution to is cross-data-center HA. Why does log shipping not work for you? ---(end of broadcast)--- TIP 1: if posting/readi

Re: [GENERAL] Problem with index in OR'd expression

2006-12-26 Thread Tom Lane
[EMAIL PROTECTED] writes: > I would submit that in that situation, it would be > reasonable for a user to expect my suggested syntax to still use the > indicated indexes. The only thing that will make that work is if "indexed_col IS NULL" were an indexable condition, which it isn't because the P

Re: [GENERAL] Problem with index in OR'd expression

2006-12-26 Thread postgresql . org
[EMAIL PROTECTED] wrote: Tom Lane wrote: you're still gonna lose because those are variables not constants ... Well, that *is* what I'm hoping to do. I understand how (0 IS NULL) is different from (variable IS NULL), but isn't it reasonable to expect that PG could evaluate that expression o

Re: [GENERAL] Optimization of unnecessary GROUP BY columns

2006-12-26 Thread Tom Lane
Martijn van Oosterhout writes: > On Tue, Dec 26, 2006 at 12:08:04PM -0500, Kevin Murphy wrote: >> When columns are required in a GROUP BY clause even though some of them >> are fully dependent on others, is there any plan for making it possible >> to do the GROUP BY only on the necessary columns?

[GENERAL] Functions to obtain group members- PostgreSQL prior to 8.1

2006-12-26 Thread Melvin Davidson
In PostgreSQL versions prior to 8.1, the functions to obtain group members is not present, so I wrote a couple of simple ones to illustrate how to do this. public.group_members(text) will return all members for a specific group. public.group_members() will return all members for all groups. F

Re: [GENERAL] Optimization of unnecessary GROUP BY columns

2006-12-26 Thread Martijn van Oosterhout
On Tue, Dec 26, 2006 at 12:08:04PM -0500, Kevin Murphy wrote: > When columns are required in a GROUP BY clause even though some of them > are fully dependent on others, is there any plan for making it possible > to do the GROUP BY only on the necessary columns? The 8.X documentation > made me c

[GENERAL] Optimization of unnecessary GROUP BY columns

2006-12-26 Thread Kevin Murphy
When columns are required in a GROUP BY clause even though some of them are fully dependent on others, is there any plan for making it possible to do the GROUP BY only on the necessary columns? The 8.X documentation made me curious: Section 7.2.3 in the 8.X documentation ("The GROUP BY and HA

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Marc Evans
On Tue, 26 Dec 2006, Shoaib Mir wrote: We are trying to achieve High Availability over load balancing, so basically we always try and have 2 databases in the same state while >>both are active, What problems do you see with Slony + Linux HA combo there? I think a Slony failover can do the sam

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Shoaib Mir
We are trying to achieve High Availability over load balancing, so basically we always try and have 2 databases in the same state while >>both are active, What problems do you see with Slony + Linux HA combo there? I think a Slony failover can do the same and promote a slave node to master in ca

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Devrim GUNDUZ
On Tue, 2006-12-26 at 15:11 +, Andy Dale wrote: > ... You guys please avoid top-posting. -- The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564 PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, plP

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Andy Dale
We are trying to achieve High Availability over load balancing, so basically we always try and have 2 databases in the same state while both are active, and if one goes down it should (hopefully) failover seemlessly. Thanks for the info on OpenSSI, as i had not heard of this before and will look

Re: [GENERAL] NEED URGENT HELP....

2006-12-26 Thread Gurjeet Singh
It works Martijn... but with a few changes will be required in your function Sandip; you will have to pass an ARRAY constructor and return a SETOF record. Here's a sample: postgres=> create table tab ( a int, b int ); CREATE TABLE postgres=> insert into tab values ( 1, 9 ); INSERT 0 1 postgres=>

Re: [GENERAL] Does pgsql's regex processor optimize Common-Prefix?

2006-12-26 Thread Tom Lane
Alvaro Herrera <[EMAIL PROTECTED]> writes: > Kurapica wrote: >> So I want to know whether pgsql's regex >> processor can optimize regexes such as: >> Nebraska|Nevada|North Carolina >> to >> N(e(braska|vada)|orth Carolina) > Compared to the use of indexes to skip whole table scanning, this > optimi

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Shoaib Mir
Why are you going for a multimaster case here? are you doing it for load balancing? if then you can also do it horizontally with a multi disk setup... Slony can be a real good candidate here as well with Linux HA in combination. For going on a vertical solution you can try OpenSSI and see if that

Re: [GENERAL] table locks

2006-12-26 Thread Tom Lane
"Ilja Golshtein" <[EMAIL PROTECTED]> writes: > I need table level locks for cooperative usage in my application. > LOCK TABLE table_name in EXCLUSIVE MODE > and > LOCK TABLE table_name in SHARED MODE > perfectly suit my needs. The question is *why* you feel you need that, ie what are you using th

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Andy Dale
The company i am working for has a trail/evaluation license for p/cluster, but unfortunately i cannot get it to function as a JBoss datasource. Cheers, Andy On 26/12/06, Shoaib Mir <[EMAIL PROTECTED]> wrote: Yes, that is true with pgpool. I did face the same as well. There is another as well

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Shoaib Mir
Yes, that is true with pgpool. I did face the same as well. There is another as well Uni-Cluster ( http://www.continuent.com/index.php?option=com_content&task=view&id=213&Itemid=170), haven't tried yet but it might help you there... --- Shoaib Mir EnterpriseDB (www.enterprisedb.com)

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Andy Dale
The issue i had with pgpool (1 or 2) was that (correct me if i am wrong) you had to start the pgpool cluster with both nodes in the same state. I thought this would mean that if you had a DB fail, before you could re-introduce it into the pgpool cluster you would have to manually sync it with the

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Shoaib Mir
pgpool-II might help you there too I guess... --- Shoaib Mir EnterpriseDB (www.enterprisedb.com) On 12/26/06, Andy Dale <[EMAIL PROTECTED]> wrote: Hi, I have just read the statement that Postgres does have (with end user assembly) multi-master replication system. Is this just PGC

Re: [GENERAL] Clustering & Load Balancing & Replication

2006-12-26 Thread Andy Dale
Hi, I have just read the statement that Postgres does have (with end user assembly) multi-master replication system. Is this just PGCluster or something else ? if it is not PGCluster, then how can this be achieved ? Cheers, Andy On 24/12/06, Shoaib Mir <[EMAIL PROTECTED]> wrote: I guess the

[GENERAL] pgAdmin crashes

2006-12-26 Thread Bobby Gontarski
I am experiencing pgAdmin (windows xp) crash whenever I try to connect to the postgresql server (type password and hit enter). It started after installing the new 8.2 version it didn't do it with the earlier versions. What do I have wrong? Thanks. ---(end of broadcast)--

Re: [GENERAL] Does pgsql's regex processor optimize Common-Prefix?

2006-12-26 Thread Oleg Bartunov
Kurapica, I'd use contrib/pg_trgm for your application. Олег On Tue, 26 Dec 2006, Alvaro Herrera wrote: Kurapica wrote: I am developing an application which searches for city names in a column. There is a lot of cities and I have to 'like' every name which is not effective enough. So I want

Re: [GENERAL] Does pgsql's regex processor optimize Common-Prefix?

2006-12-26 Thread Alvaro Herrera
Kurapica wrote: > I am developing an application which searches for city names in a > column. There is a lot of cities and I have to 'like' every name which > is not effective enough. So I want to know whether pgsql's regex > processor can optimize regexes such as: > > Nebraska|Nevada|North Carol

Re: [GENERAL] NEED URGENT HELP....

2006-12-26 Thread Martijn van Oosterhout
On Mon, Dec 25, 2006 at 08:52:52PM -0300, Henrique P Machado wrote: > WHERE (USER_ID = $1) AND (COMPANY_ID = $2) AND BOOK_NO IN ($3) > > Could'nt he use an array in this 3rd parameter? I think so, if it's written: AND BOOK_NO = ANY($3) Have a nice day, -- Martijn van Oosterhout

[GENERAL] DB problem

2006-12-26 Thread vinayakr
Hi, We are using PostgreSQL 7.3 version, installed in linux system. Only DB is getting shut down 3-4 times a day. From my application end I get "Sorry too many clients" error. Refering the FAQ I maximized the DB session limit to 200.  I think the logs are not enabled in my DB. So I set the followin