Re: [GENERAL] Justifying a PG over MySQL approach to a project

Quick question about the following statement: "but no multi-master is on the horizion" >From what I understand, there's several multi-master solutions such as Bucardo, rubyrep, PgPool and PgPool II, PgCluster and Sequoia. Also Postgres-R, which is still in development. Perhaps you just meant th

Re: [GENERAL] Fw: password authentication failed

yes. I have an entry in pg_hba.conf file as well I have made entry in .pgpass also. Thanks Sam Jas --- On Thu, 17/12/09, A. Kretschmer wrote: From: A. Kretschmer Subject: Re: [GENERAL] Fw: password authentication failed To: pgsql-general@postgresql.org Date: Thursday, 17 December, 2009, 6:1

Re: [GENERAL] Justifying a PG over MySQL approach to a project

On 17/12/2009 5:02 AM, Gauthier, Dave wrote: Hi Everyone: Tomorrow, I will need to present to a group of managers (who know nothing about DBs) why I chose to use PG over MySQL in a project, MySQL being the more popular DB choice with other engineers, and managers fearing things that are “differe

Re: [GENERAL] Fw: password authentication failed

In response to Sam Jas : > > I am newbee for Bucardo. > > Whenever i am trying to add database or try to look into show all command. > It shows me following error. > > Connecting to database 'bucardo' as user 'bucardo' > DBI connect('dbname=bucardo;port=5433','bucardo',...) f

[GENERAL] Fw: password authentication failed

I am newbee for Bucardo. Whenever i am trying to add database or try to look into show all command. It shows me following error. Connecting to database 'bucardo' as user 'bucardo' DBI connect('dbname=bucardo;port=5433','bucardo',...) failed: FATAL:  password authentication failed for user "bu

Re: [GENERAL] How to get text for a plpgsql variable from a file.

2009/12/17 Erwin Brandstetter : > On Dec 16, 10:47 pm, pavel.steh...@gmail.com (Pavel Stehule) wrote: >> hello >> >> look on orafce from pgfoundry. There modul utl_file >> >> http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#UTL_FILE > > > Thanks Pavel, that should do the trick. > > I

Re: [GENERAL] Automatic truncation of character values & casting to the type of a column type

Justin Bailey writes: > I have tried using the PL/PGSQL > feature where types can be "copied" in a declaration: > DECLARE > myVal Short.shortCol%TYPE; > But I can still put values which are too long into that variable, so > it doesn't help me. Really? Works for me, in everything back to

Re: [GENERAL] Slow select

If I search for something which is not in db like 'dfsgsdfgsdfgdsfg' it always work fast. I suspect that speed depends on number of rows retruned, but I don't know exactly... -- View this message in context: http://old.nabble.com/Slow-select-tp26810673p26821859.html Sent from the PostgreSQL - ge

Re: [GENERAL] Slow select

Sam Mason wrote: > > Wouldn't this be "lower(value) = lower(?)" ? > Yes, I use it as "lower(value) = lower(?)", I typed inaccurate example. Sam Mason wrote: > > So each query is taking approx 300ms? How much data does each one > return? > No more than 1000 rows. Sam Mason wrote: > > H

Re: [GENERAL] Server Requirements

On 17/12/2009 7:21 AM, Christine Penner wrote: Hi, If we have clients that are going to buy new computers or upgrade current ones, what we can recommend to them for optimal system performance to run Postgres. These can be servers or desktop PCs. We can have from 1-10 users in at a time. At this

[GENERAL] Automatic truncation of character values & casting to the type of a column type

Greetings! I am trying to avoid the old problem of inserting a 40 character string into a 20 character field. However, I'd like to avoid hard-coding the acceptable length (20). Is there a way to say "cast to the same type as a given column"? E.g., if I have tables Long and Short:  CREATE TABLE Lon

[GENERAL] Automatic truncation of character values & casting to the type of a column type

Greetings! I am trying to avoid the old problem of inserting a 40 character string into a 20 character field. However, I'd like to avoid hard-coding the acceptable length (20). Is there a way to say "cast to the same type as a given column"? E.g., if I have tables Long and Short: CREATE TABLE Lo

[GENERAL] Server Requirements

Hi, If we have clients that are going to buy new computers or upgrade current ones, what we can recommend to them for optimal system performance to run Postgres. These can be servers or desktop PCs. We can have from 1-10 users in at a time. At this point all of our database's are small but th

Re: [GENERAL] Justifying a PG over MySQL approach to a project

EnterpriseDB wrote a white paper called "PostgreSQL vs. MySQL: A Comparison of Enterprise Suitability", which is fairly accessible: http://downloads.enterprisedb.com/whitepapers/White_Paper_PostgreSQL_MySQL.pdf Regards, Peter Geoghegan -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] How to get text for a plpgsql variable from a file.

On Dec 16, 10:47 pm, pavel.steh...@gmail.com (Pavel Stehule) wrote: > hello > > look on orafce from pgfoundry. There modul utl_file > > http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#UTL_FILE Thanks Pavel, that should do the trick. I assume then, there is no easier built-in way i

[GENERAL] A kludge for updateable views and Hibernate

My schema uses table inheritance. I was presenting records to the UI for insert/update by a defined view CREATE VIEW monster AS SELECT * FROM parent_table NATURAL LEFT JOIN child1 NATURAL LEFT JOIN. Inserts and updates from the UI were to monster, which then used RULEs to redirect the query t

Re: [GENERAL] Justifying a PG over MySQL approach to a project

On Dec 16, 2009, at 3:05 PM, Scott Marlowe wrote: > On Wed, Dec 16, 2009 at 2:44 PM, Greg Smith wrote: >> You've probably already found >> http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 >> which was my long treatment of this topic (and ov

Re: [GENERAL] Justifying a PG over MySQL approach to a project

On Wed, Dec 16, 2009 at 2:44 PM, Greg Smith wrote: > You've probably already found > http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 > which was my long treatment of this topic (and overdue for an update). > > The main thing I intended to pu

Re: [GENERAL] Dependency tracking tool

On mån, 2009-12-14 at 16:58 +0100, Philippe Lang wrote: > My idea was to parse the functions definitions in order to build > dependencies between the functions. I'm not sure how difficult it is, > especially with overloaded functions, which require more than a simple > pattern search inside the fun

Re: [GENERAL] Need some advice on a difficult query

Thanks! The queries I wrote in my email were just an example, my actual implementation specifies all column names required and also uses full text search. I just didn't want to paste in that much cruft :) I'll do some tests with your technique below and see which works better.. Mike On Wed, De

Re: [GENERAL] getaddrinfo.c error

On mån, 2009-12-14 at 09:27 -0500, Tom Lane wrote: > Alvaro Herrera writes: > > Tom Lane wrote: > >> Hm, I wonder whether the reason the OP ran into trouble was that he > >> followed that guide :-(. Relying on manual invocation of configure > >> is a sure recipe for hitting weird breakage anytime

Re: [GENERAL] Justifying a PG over MySQL approach to a project

Greg Smith writes: > They do have a regression test suite: > http://dev.mysql.com/doc/refman/5.0/en/mysql-test-suite.html > But it's not really clear that they run it on every platform, i.e. > http://ourdelta.org/hidden-tests-of-the-mysql-testsuite They definitely don't run it on every combin

Re: [GENERAL] PlPerl scope issue

On 12/16/2009 3:15 PM, Peter wrote: Hello Tim, Thanks for the reply! I'm still not sure why it's bad to have named subroutines. At any rate I cant use anon subs since we have a complicated reporting subsystem that relies on Perl formulas being eval-ed at runtime, and these refer to various subro

Re: [GENERAL] make check fails on OS X 10.6.2

On Dec 16, 2009, at 3:16 PM, Tom Lane wrote: > Were you running a live PG instance on the box at the same time? OS X's > default SHMMAX is small enough that PG will eat all of it by default, > meaning that attempting to start a second postmaster on the box will > fail. Yeah - I was. I stopped m

Re: [GENERAL] PlPerl scope issue

On Wed, Dec 16, 2009 at 03:15:21PM -0600, Peter wrote: > Hello Tim, > > Thanks for the reply! I'm still not sure why it's bad to have named > subroutines. At any rate I cant use anon subs since we have a complicated > reporting subsystem that relies on Perl formulas being eval-ed at runtime, > and

Re: [GENERAL] Justifying a PG over MySQL approach to a project

Thomas Kellerer wrote: Greg Smith wrote on 16.12.2009 22:44: You've probably already found http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 which was my long treatment of this topic (and overdue for an update). There is an update: htt

Re: [GENERAL] Justifying a PG over MySQL approach to a project

Greg Smith wrote on 16.12.2009 22:44: You've probably already found http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 which was my long treatment of this topic (and overdue for an update). There is an update: http://wiki.postgresql.org/wik

Re: [GENERAL] How to get text for a plpgsql variable from a file.

hello look on orafce from pgfoundry. There modul utl_file http://www.postgres.cz/index.php/Oracle_functionality_%28en%29#UTL_FILE Regards Pavel Stehule 2009/12/16 Erwin Brandstetter : > Hello, > >        I need a long text form from a file in my plpgsql variable. > Can anyone think of a more st

Re: [GENERAL] Justifying a PG over MySQL approach to a project

You've probably already found http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007 which was my long treatment of this topic (and overdue for an update). The main thing I intended to put into such an update when I get to it is talking about t

Re: [GENERAL] Justifying a PG over MySQL approach to a project

Gauthier, Dave wrote on 16.12.2009 22:02: Hi Everyone: Tomorrow, I will need to present to a group of managers (who know nothing about DBs) why I chose to use PG over MySQL in a project, What kind of project is that? If you are developing something that you are selling to other people, MySQL'

Re: [GENERAL] Justifying a PG over MySQL approach to a project

Managers want support, they can't live without. Every piece of software has its flaws and needs patches. PostgreSQL is supported for 5 years, the latest version (8.4) will be supported at least until 2014. In total there are 6 supported version as we speak, 7.4 - 8.4. MySQL has active suppo

Re: [GENERAL] make check fails on OS X 10.6.2

Tim Hart writes: > creating template1 database in > /Users/thart/projects/pgsql/src/test/regress/./tmp_check/data/base/1 ... > FATAL: could not create shared memory segment: Cannot allocate memory > DETAIL: Failed system call was shmget(key=1, size=1613824, 03600). > HINT: This error usually

Re: [GENERAL] PlPerl scope issue

Hello Tim, Thanks for the reply! I'm still not sure why it's bad to have named subroutines. At any rate I cant use anon subs since we have a complicated reporting subsystem that relies on Perl formulas being eval-ed at runtime, and these refer to various subroutines. I have since resolved the is

[GENERAL] Justifying a PG over MySQL approach to a project

Hi Everyone: Tomorrow, I will need to present to a group of managers (who know nothing about DBs) why I chose to use PG over MySQL in a project, MySQL being the more popular DB choice with other engineers, and managers fearing things that are "different" (risk). I have a few hard tecnical reas

Re: [GENERAL] replication dbs

Bucardo is released under the BSD license (see htt://bucardo.org for more details). It's actively developed, and we at End Point have found it very useful and reliable. But we wrote it :) -- Joshua Tolley / eggyknap End Point Corporation http://www.endpoint.com On Tue, Dec 15, 2009 at 03:05:34PM

[GENERAL] make check fails on OS X 10.6.2

All, I've been installing postgres from source on os x for years, but I haven't generally run 'make check' before I install. I don't feel that running 'make check' is terribly necessary in this case - it's a personal sandbox on my laptop, with no production value. Regardless, I realize that it'

[GENERAL] How to get text for a plpgsql variable from a file.

Hello, I need a long text form from a file in my plpgsql variable. Can anyone think of a more straightforward way to read the file than the following: CREATE FUNCTION test() RETURNS void AS $BODY$ DECLARE mytxt text; BEGIN CREATE TEMP TABLE x (x text); COPY x from '/path/to/myfi

Re: [GENERAL] Possible causes for database corruption and solutions

On Wed, Dec 16, 2009 at 2:05 PM, Greg Smith wrote: > Michael Clark wrote: > > On Wed, Dec 16, 2009 at 11:25 AM, Greg Smith wrote: > >> Florian Weimer wrote: >> >>> I hope that Mac OS X turns off write caches on low battery. >>> >>> >> >> I've never heard of such a thing. The best you can do i

Re: [GENERAL] Possible causes for database corruption and solutions

Michael Clark wrote: On Wed, Dec 16, 2009 at 11:25 AM, Greg Smith > wrote: Florian Weimer wrote: I hope that Mac OS X turns off write caches on low battery. I've never heard of such a thing. The best you can do is try to push the sy

Re: [GENERAL] Counts and percentages and such

> jackassplus : > I'm just using squirrel to Sure! But it's bad. -- Architecte Informatique chez Blueline/Gulfsat: Administration Systeme, Recherche & Developpement +261 34 29 155 34 / +261 33 11 207 36 -- Sent via pgsql-general mailing list (pgsql-general@postgres

Re: [GENERAL] Possible causes for database corruption and solutions

On Wed, Dec 16, 2009 at 11:25 AM, Greg Smith wrote: > Florian Weimer wrote: > >> I hope that Mac OS X turns off write caches on low battery. >> >> > > I've never heard of such a thing. The best you can do is try to push the > system into hibernation instead of going down hard. That *should* cle

Re: [GENERAL] Slow select

On Wed, Dec 16, 2009 at 04:56:16AM -0800, yuliada wrote: > I have a table with column of character varying(100). There are about > 150.000.000 rows in a table. Index was created as > > CREATE INDEX idx_stringv > ON bn_stringvalue > USING btree > (lower(value::text)); > > I'm trying to execu

Re: [GENERAL] Slow select

show us explain select * -- GJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Slow select

I have a table with column of character varying(100). There are about 150.000.000 rows in a table. Index was created as CREATE INDEX idx_stringv ON bn_stringvalue USING btree (lower(value::text)); I'm trying to execute queries like 'select * from stringvalue where value=lower(?)'. Making 1

Re: [GENERAL] Cause of error message?

I have a very similar case, I am using erlang with postgres. I began a transaction then make an insertion and then I got the error "ERROR: current transaction is aborted, commands ignored until end of transaction block" when i try any other query. When i added the rollback line, It gave me anothe

[GENERAL] Training and open source

Hello, Thank you all for your advice. It has been helpful to read you. I think we will be looking into open source data integration as it seems to be the most flexible option in regards to our business. A question: what are the assistance and training capabilities open source software gives t

Re: [GENERAL] Cause of error message?

I have a very similar case, I am using erlang with postgres. I began a transaction then make an insertion and then I got the error "ERROR: current transaction is aborted, commands ignored until end of transaction block" when i try any other query. When i added the rollback line, It gave me anothe

Re: [GENERAL] C functions, dll, server fall down

>hm. I wonder if this is confusion between value/reference datums. Can >you do a quick check to see exactly where it's crashing (either before >you get into the function, at the getarg, or in the return)? > >you can elog(WARNING, msg) to print out debug info from inside the function. > >merlin

Re: [GENERAL] How to remove non-UTF values from a table?

"Raymond O'Donnell" writes: > On 16/12/2009 15:01, Richard Broersma wrote: >> It looks like the future 8.5 release will be able to >> preform an in-place upgrade on 8.4. > Really? That would be *wonderful*. I know it's impossible to be > definitive, but how likely would you say this is? It's wis

Re: [GENERAL] Possible causes for database corruption and solutions

Florian Weimer wrote: I hope that Mac OS X turns off write caches on low battery. I've never heard of such a thing. The best you can do is try to push the system into hibernation instead of going down hard. That *should* clear any disk caches as part of the graceful shutdown. But you're

Re: [GENERAL] How to remove non-UTF values from a table?

On 16/12/2009 15:01, Richard Broersma wrote: > It looks like the future 8.5 release will be able to > preform an in-place upgrade on 8.4. Really? That would be *wonderful*. I know it's impossible to be definitive, but how likely would you say this is? Ray. -- Raymond O'Donnell :: Galway :: Ir

Re: [GENERAL] Objects / Procedure creation date or modified date

Thanks.. Will try that one Regards On Wed, Dec 16, 2009 at 9:29 AM, Brian Modra wrote: > 2009/12/16 akp geek : > > Dear all - > >How can we check when a procedure last modified or > last > > DDL change happened to a table in postgres? Which Pg_* do we need to > query > > to

Re: [GENERAL] Need some advice on a difficult query

On Wed, Dec 16, 2009 at 12:47:36AM -0800, Mike Christensen wrote: > When the user searches for a new pasta dish, the UI would generate a query > something like this: > > SELECT * FROM Recipes where RecipeTitle ilike '%pasta%'; > > I only need the data from the recipes table since I display a summ

Re: [GENERAL] How to remove non-UTF values from a table?

On Wed, Dec 16, 2009 at 6:05 AM, Howard Cole wrote: > Better still, just fix using Iconv then import the clean data into > 8.3 or 8.4 FWIW, If I was going to make a choice between 8.3 and 8.4, I would choose 8.4. It looks like the future 8.5 release will be able to preform an in-place upgrade

Re: [GENERAL] Objects / Procedure creation date or modified date

2009/12/16 akp geek : > Dear all - >                    How can we check when a procedure last modified or last > DDL change happened to a table  in postgres? Which Pg_* do we need to query > to get the details > Regards Maybe follow this line of thinking: http://stackoverflow.com/questions/899203

Re: [GENERAL] Possible causes for database corruption and solutions

On Wed, Dec 16, 2009 at 5:41 AM, Florian Weimer wrote: > * Craig Ringer: > > > On 16/12/2009 3:54 PM, Florian Weimer wrote: > >> * Michael Clark: > >>> and with no power protection, then I expect it does. Add laptop > > users with ageing/flakey batteries, laptops let go flat after they > > go int

[GENERAL] Objects / Procedure creation date or modified date

Dear all - How can we check when a procedure last modified or last DDL change happened to a table in postgres? Which Pg_* do we need to query to get the details Regards

Re: [GENERAL] Possible causes for database corruption and solutions

Hi Greg, thanks for the reply! On Tue, Dec 15, 2009 at 10:52 PM, Greg Smith wrote: > Michael Clark wrote: > >> >> Secondly, I ask about an alternative solution to the corruption problem >> because with preliminary testing we have seen a significant degradation in >> performance. So far the two

Re: [GENERAL] How to remove non-UTF values from a table?

Phoenix Kiula wrote: On Tue, Dec 15, 2009 at 9:26 PM, Howard Cole wrote: Phoenix Kiula wrote: An easy question for some I hope. I have a DB from 8.2 days that when I now dump and try to take into the 8.3.7, it gives me errors about utf-8 stuff. I tried searching this list's archives

Re: [GENERAL] Interesting Benchmark Article

Greg Smith wrote: Howard Cole wrote: Postgres comes out on top for most of the benchmarks against MySQL and SQL Server, in fact in the authors original article he goes as far as recommending using Postgres. More interestingly, the article seems to indicate that W2K8 server is faster for postgr

Re: [GENERAL] Possible causes for database corruption and solutions

Hi Scott and Craig, On Tue, Dec 15, 2009 at 9:45 PM, Craig Ringer wrote: > On 16/12/2009 9:07 AM, Scott Marlowe wrote: > > I'd also recommend moving off of OSX as you're using a minority OS as >> far as databases are concerned, and you won't have a very large >> community to help out when things

Re: [GENERAL] Possible causes for database corruption and solutions

Hello Craig - thanks for the reply. I will reply below. On Tue, Dec 15, 2009 at 9:34 PM, Craig Ringer wrote: > On 16/12/2009 6:39 AM, Michael Clark wrote: > >> Hello all, >> >> Over the past 6 months or so I have posted to the list a couple times >> looking for information regarding recovering d

Re: [GENERAL] Possible causes for database corruption and solutions

On 16/12/2009 6:41 PM, Florian Weimer wrote: * Craig Ringer: When you're dealing with end users who have machines running god-knows-what kinds of awful hardware drivers Even Mac OS X? There should be less variety. Of disk and other critical drivers, sure. There is, however, a huge variety

Re: [GENERAL] Column privileges and Hibernate

On 16/12/2009 5:06 PM, Willy-Bas Loos wrote: Do you mean that you use the postgresql role system as authentication and authorization mechanism in your app through hibernate? Correct. Actually I make a plain 'ol JDBC connection with the user-supplied credentials to test the user's auth and do

Re: [GENERAL] Possible causes for database corruption and solutions

* Craig Ringer: > On 16/12/2009 3:54 PM, Florian Weimer wrote: >> * Michael Clark: >> >>> The solution to the problem seemed to be to change the value for the >>> wal_sync_method setting to fsync_writethrough from the default of fsync. >>> I was curious if there were perhaps any other reasons that

Re: [GENERAL] Possible causes for database corruption and solutions

On 16/12/2009 3:54 PM, Florian Weimer wrote: * Michael Clark: The solution to the problem seemed to be to change the value for the wal_sync_method setting to fsync_writethrough from the default of fsync. I was curious if there were perhaps any other reasons that we should look at? Or if there

Re: [GENERAL] Need some advice on a difficult query

Hi Mike, here is an untested "weird nested query" for your problem: SELECT * FROM Recipes r where lower(RecipeTitle) like lower('%pasta%') and not exists (select 1 from ingredients inner join blacklist using (IngredientId) where RecipeId = r.RecipeId and blacklist.UserId = 123 limit 1);

Re: [GENERAL] Need some advice on a difficult query

Awesome, I'll give this a shot.. Blacklist.UserId will be indexed and all the recipe links are of course already indexed, but I'll run it under the query analyzer to see if there's any other fine tuning needed. I appreciate your help! Mike On Wed, Dec 16, 2009 at 1:27 AM, Timo Klecker wrote:

Re: [GENERAL] pl/pgsql string combining

Dear Mr. Stehule Thankyou for your super prompt (came to my mailbox less then 2 minutes since my post) enlightment. I'll try it Sincerely -bino- Pavel Stehule wrote: Hello NULL and any is NULL. So you have to use "coalesce" function. like NEW.prefix = ctrcode || coalesce(NEW.code, '');

Re: [GENERAL] pl/pgsql string combining

2009/12/16 Bino Oetomo : > Dear All > > I have 2 table : > 1. hotel_pbx_country > 2. hotel_pbx_area > > Country is one2many to area > Area have a field called "prefx" > > The "prefx" field is auto filled by country.code and area.code > and for that purpose, i created trigger and function >

[GENERAL] pl/pgsql string combining

Dear All I have 2 table : 1. hotel_pbx_country 2. hotel_pbx_area Country is one2many to area Area have a field called "prefx" The "prefx" field is auto filled by country.code and area.code and for that purpose, i created trigger and function Trigger- CREATE TRIGGER prefx_xtr

[GENERAL] Need some advice on a difficult query

Hi all - I'd like some advice on how to write a rather complicated (for me, anyway) query and if there's any nifty Postgres features I can take advantage of in this situation. Imagine a database, if you will, used to store recipes. I have a recipes table: RecipeId RecipeTitle RecipeRating And a

Re: [GENERAL] Column privileges and Hibernate

Hi Craig, Do you mean that you use the postgresql role system as authentication and authorization mechanism in your app through hibernate? I don't understand how that should work. How do you authenticate? As far as i know, the way to achieve what you want is through an authorization layer on top