Re: [GENERAL] Postgresql replication

It provides pseudo relief if all your servers are in the same building. Having a front-end pgpool connector pointing to servers across the world is not workable -- performance ends up being completely decrepit due to the high latency. Which is the problem we face. Great, you've got multiple se

Re: [GENERAL] Help with a subselect inside a view

On Wed, Aug 24, 2005 at 11:12:17PM -0700, Bill Moseley wrote: > I need a little SQL help: > > I'm trying to get a subselect working inside a view. > > I have a table "class" that has related tables (a class has a > location, a location has an address with columns city, state, zip). > I want to us

Re: [GENERAL] Postgresql replication

I would have a slight offtopic question, this is issue only of pgsql or there are some other db solutions which have good performance when doing this kind of replication across the world. Regards, Bohdan On Thu, Aug 25, 2005 at 09:01:49AM +0200, William Yu wrote: > It provides pseudo relief if

Re: [GENERAL] Postgresql replication

Bohdan Linda schrieb: I would have a slight offtopic question, this is issue only of pgsql or there are some other db solutions which have good performance when doing this kind of replication across the world. it depends entirely on your application. There is no "one size fits all" For example

Re: [GENERAL] Postgresql replication

As far as I know, nobody has a generic solution for multi-master replication where servers are not in close proximity. Single master replication? Doable. Application specific conflict resolution? Doable. Off the shelf package that somehow knows financial transactions on a server shouldn't be du

Re: [GENERAL] Postgresql replication

Another tidbit I'd like to add. What has helped a lot in implementing high-latency master-master replication writing our software with a business process model in mind where data is not posted directly to the final tables. Instead, users are generally allowed to enter anything -- could be incor

Re: [GENERAL] Postgresql replication

Tino Wildenhain wrote: Which is the problem we face. Great, you've got multiple servers for failover. Too bad it doesn't do much good if your building gets hit by fire/earthquake/hurricane/etc. This would remove the application using that data too, or not? ;) Yes and no. If your DB is an in

Re: [GENERAL] plpgsql return setof integer?

On Wed, 24 Aug 2005, Alvaro Herrera wrote: On Wed, Nov 12, 2003 at 11:42:56PM -0500, Christopher Murtagh wrote: Thanks once again. You've really helped a lot on this. I especially liked your 'return qq/{"/ . (join qq/","/, @_) . qq/"}/;' code. If you were in Montreal, I would owe you a dinner o

Re: [GENERAL] Help with a subselect inside a view

On Wed, Aug 24, 2005 at 23:12:17 -0700, Bill Moseley <[EMAIL PROTECTED]> wrote: > I need a little SQL help: > > I'm trying to get a subselect working inside a view. Unfortunately you didn't show us what you tried. My guess would be that you didn't enclose the subselect in parenthesis. The dist

Re: [GENERAL] Postgresql replication

On Thursday 25 August 2005 13:03, William Yu wrote: > As far as I know, nobody has a generic solution for multi-master > replication where servers are not in close proximity. Single master > replication? Doable. Application specific conflict resolution? Doable. > Off the shelf package that somehow

Re: [GENERAL] Query results caching?

Jim C. Nasby wrote: On Tue, Aug 23, 2005 at 12:27:39AM +0200, Ben-Nes Yonatan wrote: Jim C. Nasby wrote: Emptying the cache will not show real-life results. You are always going to have some stuff cached, even if you get a query for something new. In this case (since you'll obviously w

Re: [GENERAL] Query results caching?

Dann Corbit wrote: -Original Message- From: Ben-Nes Yonatan [mailto:[EMAIL PROTECTED] Sent: Monday, August 22, 2005 3:28 PM To: Jim C. Nasby; Sean Davis; Dann Corbit Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] Query results caching? On Mon, Aug 22, 2005 at 10:13:49PM +

[GENERAL] Problem upgrading from 8.0.1 to 8.0.3

I was running 8.0.1 and PHP in FC3 with no problems. Then i upgraded to 8.0.3 using yum and yum installed postgresql-libs.i386 0:7.4.8-1.FC3.1. (Is it Ok?) Now i am receving this message from PHP: PHP Fatal error: Call to undefined function: pg_pconnect() php-pgsql was already installed. I rem

[GENERAL] getting last day of month

Hi all. I need to find out the last day of current month. Currently I do the trick with code below, but that's rather ugly way to do it IMHO. Could anybody suggest me a better way ? The following is my testing procedure : CREATE or REPLACE FUNCTION test_findout_dates() RETURNS integer AS $$ D

Re: [GENERAL] getting last day of month

Sergey, Try this one: CREATE OR REPLACE FUNCTION public.lastdayofmonth(date) RETURNS date AS ' select ((date_trunc(\'month\', $1) + interval \'1 month\') - interval \'1 day\')::date; ' LANGUAGE 'sql' VOLATILE; Sergey Pariev wrote: Hi all. I need to find out the last day of current mon

Re: [GENERAL] getting last day of month

Sergey Pariev schrieb: Hi all. I need to find out the last day of current month. Currently I do the trick with code below, but that's rather ugly way to do it IMHO. Could anybody suggest me a better way ? The following is my testing procedure : CREATE or REPLACE FUNCTION test_findout_dates()

Re: [GENERAL] getting last day of month

You could try : SELECT int4(EXTRACT( DAYS FROM CURRENT_DATE + '1 month'::interval - CURRENT_DATE )); --- Patrick Fiche email : [EMAIL PROTECTED] tel : 01 69 29 36 18 --

Re: [GENERAL] getting last day of month

Sergey Pariev wrote: > Hi all. > I need to find out the last day of current month. Currently I do the > trick with code below, but that's rather ugly way to do it IMHO. Could > anybody suggest me a better way ? select '2005-09-01'::date-'1 day'::interval does the trick :) -- Wbr, Sergey Moiseev -

Re: [GENERAL] Postgresql replication

Chris Travers wrote: Carlos Henrique Reimer wrote: I read some documents about replication and realized that if you plan on using asynchronous replication, your application should be designed from the outset with that in mind because asynchronous replication is not something that can be easi

Re: [GENERAL] Postgresql replication

Bohdan Linda wrote: I would have a slight offtopic question, this is issue only of pgsql or there are some other db solutions which have good performance when doing this kind of replication across the world. It all depends on the quality of the connection Node A to Node B. If connectivity

Re: [GENERAL] Help with a subselect inside a view

Hi David, On Thu, Aug 25, 2005 at 01:22:02AM -0700, David Fetter wrote: > This sounds like a case for PostgreSQL's nifty DISTINCT ON functionality. > > http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT The DISTINCT ON expression(s) must match the leftmost ORDER BY

Re: [GENERAL] Help with a subselect inside a view

On Thu, Aug 25, 2005 at 08:05:36AM -0500, Bruno Wolff III wrote: > On Wed, Aug 24, 2005 at 23:12:17 -0700, > Bill Moseley <[EMAIL PROTECTED]> wrote: > > I need a little SQL help: > > > > I'm trying to get a subselect working inside a view. > > Unfortunately you didn't show us what you tried. My

[GENERAL] SOCKET Conection on Windwos 2003 vs PostgreSQL 8.0.1

 Hi,    I´m Julio and a have a situation running W2003 and DBExpress.    I connect to the W2003 with socket (:5432) and, when i save the package on my table, a new postgres.exe process start. Ok, no problems until here. I disconnect from the W2003 and the new proccess just die    The pr

Re: [GENERAL] SOCKET Conection on Windwos 2003 vs PostgreSQL 8.0.1

> Hi, > > I´m Julio and a have a situation running W2003 and DBExpress. > > I connect to the W2003 with socket (:5432) and, when i save > the package on my table, a new postgres.exe process start. > Ok, no problems until here. I disconnect from the W2003 and > the new proccess just die...

Re: [GENERAL] Query results caching?

Dann Corbit wrote: If I have a 4 million row table, with long rows and big varchar columns and I run a query on a column like this: SELECT * FROM inventory WHERE product LIKE '%Table%' It isn't going to be fast on any system with any database. Hypothetically it seems one could theore

Re: [GENERAL] Help with a subselect inside a view

And about being efficient: On Thu, Aug 25, 2005 at 08:01:26AM -0700, Bill Moseley wrote: > DROP VIEW cl; > CREATE VIEW cl (id, class_time, instructor) > AS > SELECT DISTINCT ON(class.id) >class.id, class.class_time, person.first_name >

Re: [GENERAL] Help with a subselect inside a view

Bill Moseley <[EMAIL PROTECTED]> writes: >> http://www.postgresql.org/docs/current/static/sql-select.html#SQL-DISTINCT >The DISTINCT ON expression(s) must match the leftmost ORDER BY >expression(s). The ORDER BY clause will normally contain additional >expression(s) that determine the

Re: [GENERAL] Help with a subselect inside a view

On Thu, Aug 25, 2005 at 12:14:31PM -0400, Tom Lane wrote: > > CREATE VIEW cl (id, class_time, instructor) > > AS > > SELECT DISTINCT ON(class.id) > >class.id, class.class_time, person.first_name > > FROM class, instructors, person > >

Re: [GENERAL] Postgresql replication

William Yu schrieb: Tino Wildenhain wrote: Which is the problem we face. Great, you've got multiple servers for failover. Too bad it doesn't do much good if your building gets hit by fire/earthquake/hurricane/etc. This would remove the application using that data too, or not? ;) Yes and

Re: [GENERAL] Postgresql replication

[EMAIL PROTECTED] (Aly Dharshi) writes: > I know I am wadding into this discussion as an beginner compared to > the rest who have answered this thread, but doesn't something like > pgpool provide relief for pseudo-multimaster replication, and what > about software like sqlrelay wouldn't these suite

Re: [GENERAL] Postgresql replication

[EMAIL PROTECTED] (David Goodenough) writes: > On Thursday 25 August 2005 13:03, William Yu wrote: >> As far as I know, nobody has a generic solution for multi-master >> replication where servers are not in close proximity. Single master >> replication? Doable. Application specific conflict resolut

Re: [GENERAL] Help with a subselect inside a view

Bill Moseley <[EMAIL PROTECTED]> writes: > On Thu, Aug 25, 2005 at 12:14:31PM -0400, Tom Lane wrote: >> It's fairly pointless though, because as the manual notes, you can't get >> any well-defined behavior without additional ORDER BY columns to >> prioritize the rows within class.id groups. As is,

Re: [GENERAL] Postgresql replication

[EMAIL PROTECTED] (Bohdan Linda) writes: > I would have a slight offtopic question, this is issue only of pgsql or > there are some other db solutions which have good performance when doing > this kind of replication across the world. Asynchronous multimaster replication is pretty much a generally

Re: [GENERAL] Is there such a thing as a 'background database job'?

On Aug 22, 2005, at 10:53 PM, Mike Nolan wrote: In a recent discussion with an academician friend of mine regarding how to improve performance on a system, he came up with the idea of taking what is now a monthly purge/cleanup job that takes about 24 hours (and growing) and splitting it u

Re: [GENERAL] Postgresql replication

Jim C. Nasby wrote: > Or, for something far easier, try > http://pgfoundry.org/projects/pgcluster/ which provides syncronous > multi-master clustering. > He specifically said that pgcluster did not work for him because the databases would be at physically seperate locations. PGCluster requires th

Re: [GENERAL] ctid access is slow

On Aug 23, 2005, at 10:02 AM, Ilja Golshtein wrote: The only thing I am curios is ctid good for anything from user point of view? I have a very specific use for it -- to bypass the index on an update. Something like this: select ctid,user_id from users where ... ... do stuff based on us

Re: [GENERAL] Postgresql replication

Tino Wildenhain wrote: If your app is used by external customers who are all across the country, they want to continue to still use your software even though you and data center #1 are 6 feet under due to an 8.0 earthquake. They want auto-failover to data center #2 which is in close proximity t

Re: [GENERAL] Postgresql replication

ExactlyJeff Davis <[EMAIL PROTECTED]> escreveu: Jim C. Nasby wrote:> Or, for something far easier, try> http://pgfoundry.org/projects/pgcluster/ which provides syncronous> multi-master clustering.> He specifically said that pgcluster did not work for him because thedatabases would be at physica

Re: [GENERAL] Postgresql replication

> > http://pgfoundry.org/projects/pgcluster/ which provides syncronous > > multi-master clustering. > > He specifically said that pgcluster did not work for him > because ...PGCluster requires that there be a load balancer and a > replicator centrally located managing the cluster. If a network > pr

Re: [GENERAL] Postgresql replication

On Thu, Aug 25, 2005 at 12:20:52PM -0400, Chris Browne wrote: > [EMAIL PROTECTED] (Bohdan Linda) writes: > > I would have a slight offtopic question, this is issue only of pgsql or > > there are some other db solutions which have good performance when doing > > this kind of replication across the

Re: [GENERAL] Help with a subselect inside a view

On Thu, Aug 25, 2005 at 08:19:25 -0700, Bill Moseley <[EMAIL PROTECTED]> wrote: > > DROP VIEW cl; > CREATE VIEW cl (id, instructor) > AS > SELECT class.id, person.first_name > FROM class, instructors, person > WHERE instructors.person = person.id >AND

[GENERAL] help

We used nullif('$value','') on inserts in mssql. We moved to postgres but the nullif() function doesn't match empty strings to each other to return null. MS SQL2000 nullif('1','') would insert 1 as integer even though wrapped in '' (aka string). Also nullif('','') would evaluate NULL (both equal

Re: [GENERAL] Postgresql replication

David Goodenough wrote: The most obvious one that does exactly this (generic multi-master replication) is Lotus Domino. It is not a relational DB, but not sufficiently far off to stop the analogy. Domino marks each document with a binary value which identifies the server (built from a hash of t

Re: [GENERAL] Postgresql replication

Carlos Henrique Reimer wrote: > Exactly > Was there something lacking in my suggested solution at: It's a little complicated to administer, but it seems well-suited to a company that has several locations that want to share

[GENERAL] Odd Problems

Hi, I have about 15 postgres databases, recently I have noticed on a few of them odd errors. Basically I have seen two different manifestations of the same problem (which I think is index coruption). In case 1, updates and or inserts fail due to a constraint on the table, however they shou

Re: [GENERAL] drop table before create

I have not been able to work out how to do this is Postgres 8 (pseudo-code) if exists table foo drop table foo; end create table foo; If I go with drop table foo; create table foo; then it barfs on an empty db. The assumption here is that the SQL is coming in on a script via the

Re: [GENERAL] Postgresql replication

[EMAIL PROTECTED] (Brad Nicholson) writes: > Bohdan Linda wrote: > >>I would have a slight offtopic question, this is issue only of pgsql or >>there are some other db solutions which have good performance when doing >>this kind of replication across the world. >> >> >> > It all depends on the quali

Re: [GENERAL] getting last day of month

[EMAIL PROTECTED] (Sergey Pariev) writes: > I need to find out the last day of current month. Currently I do the > trick with code below, but that's rather ugly way to do it IMHO. Could > anybody suggest me a better way ? log_analysis=# select date_trunc('months', (date_trunc('months', now()) + '

Re: [GENERAL] Postgresql replication

[EMAIL PROTECTED] (Steve Atkins) writes: > On Thu, Aug 25, 2005 at 12:20:52PM -0400, Chris Browne wrote: >> [EMAIL PROTECTED] (Bohdan Linda) writes: >> > I would have a slight offtopic question, this is issue only of pgsql or >> > there are some other db solutions which have good performance when d

Re: [GENERAL] Postgresql replication

William Yu <[EMAIL PROTECTED]> writes: > David Goodenough wrote: >> The most obvious one that does exactly this (generic multi-master >> replication) is Lotus Domino. It is not a relational DB, but not >> sufficiently >> far off to stop the analogy. >> Domino marks each document with a binary val

Re: [GENERAL] help

Very odd, what are you trying? kleptog=# select nullif('','') is null; ?column? -- t (1 row) kleptog=# select nullif('1','') is null; ?column? -- f (1 row) Works for me. Have a nice day, On Thu, Aug 25, 2005 at 01:44:52PM -0700, Matt A. wrote: > We used nullif('$value',''

[GENERAL] POSS. FEATURE REQ: "Dynamic" Views

For lack of a better term, but I was curious if there is/was any thought about making PG's views automatically "see" changes in underlying tables, as opposed to currently having to drop/create all corresponding views if a table's structure (add/delete fields, etc.) is changed. CSN

Re: [GENERAL] help

Please reply to the list also in the future, I'm going to sleep now. Anyway, it still works: kleptog=# create temp table x (a text); CREATE TABLE kleptog=# insert into x values (nullif('','')); INSERT 114760 1 kleptog=# insert into x values (nullif('1','')); INSERT 114761 1 kleptog=# select a,a i

Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views

On Thu, Aug 25, 2005 at 03:03:30PM -0700, CSN wrote: > For lack of a better term, but I was curious if there > is/was any thought about making PG's views > automatically "see" changes in underlying tables, as > opposed to currently having to drop/create all > corresponding views if a table's struct

Re: [GENERAL] help

Sorry to have replied directly to you. I assumed the reply address was that of the pgsql email.A thousand apologies. Anyway, I am new to postgres and looking to casts. I have wrote this question several times before and on this version I forgot to add the exact rules of why it's breaking and what

Re: [GENERAL] Postgresql replication

William Yu wrote: Another tidbit I'd like to add. What has helped a lot in implementing high-latency master-master replication writing our software with a business process model in mind where data is not posted directly to the final tables. Instead, users are generally allowed to enter anythi

[GENERAL] Its Beta Time Again ... 8.1 Beta 1 Now Available

Back on the 1st of July, after almost 6 months of development since 8.0 was released, development on 8.1 was frozen. Now, after spending the past few weeks processing through outstanding patches applicable to 8.1, we now enter our beta testing period, where we need help from the community at

Re: [GENERAL] Postgresql replication

Matt Miller wrote: >>>http://pgfoundry.org/projects/pgcluster/ which provides syncronous >>>multi-master clustering. >> >>He specifically said that pgcluster did not work for him >>because ...PGCluster requires that there be a load balancer and a >>replicator centrally located managing the cluster.

[GENERAL] Altering built-in functions cast

Anyone know how I could alter the cast of the nullif() function directly to return INT? We use NULLIF() for adding [1|0|null] according to the evalution of nullif('x','') into integer columns. Where x is an integer and sometimes a empty string, which if it's an empty string (x='') then we add NULL

Re: [GENERAL] Its Beta Time Again ... 8.1 Beta 1 Now Available

Hi Marc, Could you post the location of the beta documentation? I seem to have lost the link :-( Is there info on the new role system and in/out params in the beta docs? Thanks, Tony PostgreSQL 8.1 will bring in alot of fairly large features, including: - Improve concurrent access to

Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views

Brand X simulates this in their GUI diagrammer by tracking dependencies and dropping and recreating dependent views on schema changes. This might be a better job for one of the GUI tools for us too, rather than trying to put it in the back end. Brand X doesn't do it in their backend either. On

Re: [GENERAL] POSS. FEATURE REQ: "Dynamic" Views

"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > ... since currently CREATE VIEW v AS SELECT * FROM t > actually expands the * out at creation time. I believe that that behavior is required by the SQL spec. For instance, SQL92's description of ALTER TABLE ADD COLUMN sez: Note: The additi

Re: [GENERAL] unsubscribe

On Thu, Aug 25, 2005 at 07:30:07AM +0800, Richard Sydney-Smith wrote: > unsubscribe 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 standard no-fault type. Depending on requirements, Syst

Re: [GENERAL] help

On Thu, Aug 25, 2005 at 04:10:27PM -0700, Matt A. wrote: > Anyway, I am new to postgres and looking to casts. I > have wrote this question several times before and on > this version I forgot to add the exact rules of why > it's breaking and what i need it to do. I will try to > be more clear... >