Re: [GENERAL] Graphical representation of query plans

2009-09-22 Thread Guillaume Lelarge 
Le mardi 22 septembre 2009 à 22:48:57, Emi Lu a écrit :
> Grzegorz Jaśkiewicz wrote:
> > pgadmin does it pretty nicely:
> > http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
> 
> As shown in the mackintosh version, it is a very nice and helpful feature!
> 
> 
> I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu,
> compiled by GCC gcc (GCC) 3.3.2.
> 
> I did not see this "explain" in my version. I only have "Data Output,
> Message, and History".
> 
> May I know in which pgadmin version the "query plan visualization" was
> added please?
> 

At least, 1.4 has it.

> Did not see it from pgadmin online doc either?
> 

http://www.pgadmin.org/docs/dev/query.html


-- 
Guillaume.
 http://www.postgresqlfr.org
 http://dalibo.com

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

[GENERAL] Help! Database restored with disabled triggers

2009-09-22 Thread Joe Kramer 
I have database backup schema+data in text (non-compressed) format.
Backup is created using "pg_dump -i  -h ... -U ... -f dump.sql".
I run it with "psql http://www.postgresql.org/mailpref/pgsql-general

Re: Re[GENERAL] sources for learning PostgreSQL

2009-09-22 Thread Scott Bailey 



The PostgreSQL website has excellent documentation (second only to
php.net). If you already know MySQL, then you're 2/3 of the way there.
You just need to get used to the quirks of PostgreSQL. 


A more accurate statement would be "You just need to learn where the 
quirks you've picked up by using MySQL are."  You should be able to jump 
right in and start using Postgres. But you'll probably need the use the 
manual at first. The good news is that once you learn Postgres, your 
skills will be a lot more transferable to other databases.


This article is a bit dated but still good.

http://www.xach.com/aolserver/mysql-to-postgresql.html

Scott Bailey

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

Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work

2009-09-22 Thread Scott Marlowe 
On Tue, Sep 22, 2009 at 3:55 PM, Gurjeet Singh  wrote:
> Sometimes, your current postgresql.conf might not be in sync with server
> settings, for various reasons. I'd suggest looking at the output of:
>
> select name, setting, source from pg_settings where name like E'log\\_%';

Or

psql dbname
show log_statement;

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

Re: [GENERAL] VMWare file system / database corruption

2009-09-22 Thread Greg Smith 

On Mon, 21 Sep 2009, Tom Duffey wrote:

Does anyone with a better understanding of PostgreSQL and VMWare know if this 
is an unreliable setup for PostgreSQL?  I see things like "NFS" and "VMWare" 
and start to get worried.


PostgreSQL requires one simple guarantee:  that when the database writes 
something and then calls the OS fsync call, that call will not return 
success until that write is on physical disk.  In your case, this 
requires:


1) VMWare recognizes fsync and passes that request to the network storage 
device

2) The NFS software passes fsync to the SAN
3) The SAN waits until the physical disk write is complete (and not just 
in the hard drive's write caches) before returning from the fsync that the 
operation is complete.


In theory, there's no reason this can't be made reliable (albeit slow). 
But when you have so many layers of stuff in the middle it's hard to prove 
that things are working correctly or find the problem part that's causing 
corruption.  You'll need to audit everything from your VM down to the SAN 
configuration to make sure there are no non-battery backed write-back 
caches being used (and, no, a UPS doesn't count), and that none of the 
software involved has turned off fsync support as a performance 
optimization.


There's a bunch of additional trivia in this area at 
http://www.postgresql.org/docs/current/static/wal-reliability.html and my 
article at 
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm


--
* Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD

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

Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work

2009-09-22 Thread Gurjeet Singh 
Sometimes, your current postgresql.conf might not be in sync with server
settings, for various reasons. I'd suggest looking at the output of:

select name, setting, source from pg_settings where name like E'log\\_%';

Best regards,

On Wed, Sep 23, 2009 at 3:13 AM, Chris Barnes <
compuguruchrisbar...@hotmail.com> wrote:

>  Hello,
>
> I looked for log_statement and it appears to be off? Strange.
>
> #log_statement = 'none'
> #log_statement_stats = off
>
> > To: compuguruchrisbar...@hotmail.com
> > CC: pgsql-general@postgresql.org
> > Subject: Re: [GENERAL] Logging statements longer than 1000ms doesn't
> appear to work
> > Date: Tue, 22 Sep 2009 15:08:39 -0400
> > From: t...@sss.pgh.pa.us
> >
> > Chris Barnes  writes:
> > > I've have set the parameter in my postgresql.conf file and have
> restarted postgres.
> >
> > > When reviewing the log file I am finding that all of the statements are
> being logged (0.108 ms)?
> >
> > Perhaps you also set log_statement = all, or some other reason that
> > would cause them to be logged?
> >
> > regards, tom lane
>
> --
> We are your photos. Share us now with Windows Live 
> Photos.
>



-- 
Lets call it Postgres

EnterpriseDB  http://www.enterprisedb.com

gurjeet[.sin...@enterprisedb.com

singh.gurj...@{ gmail | hotmail | indiatimes | yahoo }.com
Twitter: singh_gurjeet
Skype: singh_gurjeet

Mail sent from my BlackLaptop device

Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work

2009-09-22 Thread Chris Barnes 

Hello,

 

I looked for log_statement and it appears to be off? Strange.

 

#log_statement = 'none' 

#log_statement_stats = off
 
> To: compuguruchrisbar...@hotmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Logging statements longer than 1000ms doesn't appear 
> to work 
> Date: Tue, 22 Sep 2009 15:08:39 -0400
> From: t...@sss.pgh.pa.us
> 
> Chris Barnes  writes:
> > I've have set the parameter in my postgresql.conf file and have restarted 
> > postgres.
> 
> > When reviewing the log file I am finding that all of the statements are 
> > being logged (0.108 ms)?
> 
> Perhaps you also set log_statement = all, or some other reason that
> would cause them to be logged?
> 
> regards, tom lane
  
_
We are your photos. Share us now with Windows Live Photos.
http://go.microsoft.com/?linkid=9666047

Re: [GENERAL] Graphical representation of query plans

2009-09-22 Thread Raymond O'Donnell 
On 22/09/2009 21:48, Emi Lu wrote:
> Grzegorz Jaśkiewicz wrote:
>> pgadmin does it pretty nicely:
>> http://pgadmin.org/images/screenshots/pgadmin3_macosx.png
> 
> 
> As shown in the mackintosh version, it is a very nice and helpful feature!
> 
> 
> I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu,
> compiled by GCC gcc (GCC) 3.3.2.

That's a *really* old version - pgAdmin is up to 1.10 now, and it's well
worth your while upgrading. 8.0 is a really old version of PG too

Ray.

--
Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland
r...@iol.ie
Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals
--

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

Re: [GENERAL] Graphical representation of query plans

2009-09-22 Thread Emi Lu 

Grzegorz Jaśkiewicz wrote:

pgadmin does it pretty nicely:
http://pgadmin.org/images/screenshots/pgadmin3_macosx.png



As shown in the mackintosh version, it is a very nice and helpful feature!


I have pgadmin 1.2.0 for PostgreSQL 8.0.15 on i686-pc-linux-gnu, 
compiled by GCC gcc (GCC) 3.3.2.


I did not see this "explain" in my version. I only have "Data Output, 
Message, and History".


May I know in which pgadmin version the "query plan visualization" was 
added please?


Did not see it from pgadmin online doc either?


Thanks a lot!

--
Ying Lu





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

Re: [GENERAL] PLJava not installed for PostgreSQL 8.4 on Solaris 10 x86 64

2009-09-22 Thread Joshua D. Drake 
On Tue, 2009-09-22 at 13:35 -0700, John R Pierce wrote:
> Arnold, Sandra wrote:
> > Is the pljava.so file included in the PostgreSQL 8.4 binary file for 
> > Solaris 10 x86 64-bit?  If not, where can I go to download this 
> > library file?  If so, what would cause it to not install during the 
> > installation process?
> 
> 
> pljava is a mess.   you either use GCJ which is only-sorta-java, or you 
> drag the whole JVM in with every instance of postgres, ugh!

If you check the archives there were very specific reasons for that.

Joshua D. Drake

-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


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

Re: [GENERAL] PLJava not installed for PostgreSQL 8.4 on Solaris 10 x86 64

2009-09-22 Thread John R Pierce 

Arnold, Sandra wrote:
Is the pljava.so file included in the PostgreSQL 8.4 binary file for 
Solaris 10 x86 64-bit?  If not, where can I go to download this 
library file?  If so, what would cause it to not install during the 
installation process?



pljava is a mess.   you either use GCJ which is only-sorta-java, or you 
drag the whole JVM in with every instance of postgres, ugh!




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

Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work

2009-09-22 Thread Chris Barnes 

I checked and this is the only refererences. Were usng 8.3.3.

 

#log_statement = 'none' # none, ddl, mod, all
#log_statement_stats = off

 
> To: compuguruchrisbar...@hotmail.com
> CC: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Logging statements longer than 1000ms doesn't appear 
> to work 
> Date: Tue, 22 Sep 2009 15:08:39 -0400
> From: t...@sss.pgh.pa.us
> 
> Chris Barnes  writes:
> > I've have set the parameter in my postgresql.conf file and have restarted 
> > postgres.
> 
> > When reviewing the log file I am finding that all of the statements are 
> > being logged (0.108 ms)?
> 
> Perhaps you also set log_statement = all, or some other reason that
> would cause them to be logged?
> 
> regards, tom lane
> 
> -- 
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
  
_
Create a cool, new character for your Windows Live™ Messenger. 
http://go.microsoft.com/?linkid=9656621

Re: [GENERAL] Logging statements longer than 1000ms doesn't appear to work

2009-09-22 Thread Tom Lane 
Chris Barnes  writes:
> I've have set the parameter in my postgresql.conf file and have restarted 
> postgres.

> When reviewing the log file I am finding that all of the statements are being 
> logged (0.108 ms)?

Perhaps you also set log_statement = all, or some other reason that
would cause them to be logged?

regards, tom lane

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

Re: [GENERAL] citext like query and index usage

2009-09-22 Thread Tom Lane 
Tore Halvorsen  writes:
> On Tue, Sep 22, 2009 at 8:49 PM, Alvaro Herrera
>  wrote:
>> Tore Halvorsen escribió:
>>> Is it possible to use an index for like queries on a citext column?

>> Hmm, I think this needs one of the *_pattern_ops indexes.  I'm not sure
>> if you can use the builtin ones with citext though.

> Looks like I'm stuck with lower(text_col) for now...

Afraid so --- there are special cases in the planner for LIKE, and that
code only knows about the built-in types.  Sometime we should figure out
how to push that logic out to datatype-specific code so it can be
more extensible.

regards, tom lane

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

[GENERAL] Logging statements longer than 1000ms doesn't appear to work

2009-09-22 Thread Chris Barnes 

I've have set the parameter in my postgresql.conf file and have restarted 
postgres.

When reviewing the log file I am finding that all of the statements are being 
logged (0.108 ms)?

 

Is there some other parameter that I have missed?

 

 

log_min_duration_statement = 1000   # -1 is disabled, 0 logs all statements

 

Chris
  
_
Windows Live helps you keep up with all your friends, in one place.
http://go.microsoft.com/?linkid=9660826

Re: [GENERAL] citext like query and index usage

2009-09-22 Thread Tore Halvorsen 
On Tue, Sep 22, 2009 at 8:49 PM, Alvaro Herrera
 wrote:
> Tore Halvorsen escribió:
>> Hi,
>>
>> Is it possible to use an index for like queries on a citext column?
>> I'm using pg 8.4.1 on windows - with no changes to the default configuration.
>>
>> For example:
>>
>> CREATE TABLE test ( citext citext NOT NULL );
>> INSERT INTO test select md5(random()::text) FROM generate_series(0, 100, 
>> 1);
>> CREATE INDEX test_citext_idx ON test USING btree(citext);
>
> Hmm, I think this needs one of the *_pattern_ops indexes.  I'm not sure
> if you can use the builtin ones with citext though.

Yeah, I started looking at something like that - sadly after I sent my mail.

Looks like I'm stuck with lower(text_col) for now...

Thanks anyway :)

-- 
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
 2009 Tore Halvorsen || +052 0553034554

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

Re: [GENERAL] PLJava not installed for PostgreSQL 8.4 on Solaris 10 x86 64

2009-09-22 Thread Joshua D. Drake 
On Tue, 2009-09-22 at 09:10 -0400, Arnold, Sandra wrote:
> Is the pljava.so file included in the PostgreSQL 8.4 binary file for
> Solaris 10 x86 64-bit?  If not, where can I go to download this
> library file?  If so, what would cause it to not install during the
> installation process?

pljava is not part of the core distribution.

http://pgfoundry.org/projects/pljava/?downloads


Joshua D. Drake


>  
> Thanks,
> 
> Sandra Arnold
> Sr. DBA
> DOE/OSTI
> Oak Ridge, TN
-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564
Consulting, Training, Support, Custom Development, Engineering
If the world pushes look it in the eye and GRR. Then push back harder. - 
Salamander


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

Re: [GENERAL] citext like query and index usage

2009-09-22 Thread Alvaro Herrera 
Tore Halvorsen escribió:
> Hi,
> 
> Is it possible to use an index for like queries on a citext column?
> I'm using pg 8.4.1 on windows - with no changes to the default configuration.
> 
> For example:
> 
> CREATE TABLE test ( citext citext NOT NULL );
> INSERT INTO test select md5(random()::text) FROM generate_series(0, 100, 
> 1);
> CREATE INDEX test_citext_idx ON test USING btree(citext);

Hmm, I think this needs one of the *_pattern_ops indexes.  I'm not sure
if you can use the builtin ones with citext though.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

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

Re: Re[GENERAL] sources for learning PostgreSQL

2009-09-22 Thread Paul M Foster 
On Tue, Sep 22, 2009 at 10:43:09AM -0700, dan06 wrote:

> 
> Hi,
> 
> I've used mysql for sometime now, and I'd like to broaden my db
> knowledge/experience to include postgresql. Can anyone recommend any books
> or other resources that could help me along? Thanks.

The PostgreSQL website has excellent documentation (second only to
php.net). If you already know MySQL, then you're 2/3 of the way there.
You just need to get used to the quirks of PostgreSQL. For books, I have
two:

Beginning Databases with PostgreSQL 2/e by Matthew & Stones

PostgreSQL 2/e by Douglas & Douglas

These are better than the original PostgreSQL: Introduction and Concepts
by Momjian (sorry, Bruce). That book struck me as mostly a transcription
of the existing documentation at the time. It was very helpful when
there were no other PostgreSQL books out there.

But you may not even need the books, if you peruse the website.

Paul

-- 
Paul M. Foster

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

Re[GENERAL] sources for learning PostgreSQL

2009-09-22 Thread dan06 

Hi,

I've used mysql for sometime now, and I'd like to broaden my db
knowledge/experience to include postgresql. Can anyone recommend any books
or other resources that could help me along? Thanks.

-Dan
-- 
View this message in context: 
http://www.nabble.com/Resources-for-learning-PostgreSQL-tp25530840p25530840.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] Storage of Foreign Keys

2009-09-22 Thread Lew 

Scott Marlowe wrote:

On Mon, Sep 21, 2009 at 11:09 AM, Alan Hodgson  wrote:

On Monday 21 September 2009, Christian Koetschan
 wrote:

Is everything I insert into mycolA and mycolB stored twice, or
is there something like a pointer/reference from mycolA to the things
stored in mycolB?


It's stored twice and for performance you need to index it in both tables.
If you put ON UPDATE CASCADE on it, an update to the master will hit both
tables though.


Slight correction.  You might need to index it on both ends for
performance.  There are some instances where the referencing table
only ever has a few rows in it, and in those cases, you likely don't
need an index.


And it is likely that the referenced table has an index since foreign keys 
reference a primary key or unique-constrained (combination of) column(s).


--
Lew

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

Re: [GENERAL] citext like query and index usage

2009-09-22 Thread Emanuel Calvo Franco 
> Is it possible to use an index for like queries on a citext column?
> I'm using pg 8.4.1 on windows - with no changes to the default configuration.
>
> For example:
>
> CREATE TABLE test ( citext citext NOT NULL );
> INSERT INTO test select md5(random()::text) FROM generate_series(0, 100, 
> 1);
> CREATE INDEX test_citext_idx ON test USING btree(citext);
> vacuum analyze test;
>
> explain analyze select * from test where citext like '%'
> ...
> Seq Scan on test  (cost=0.00..20834.03 rows=5000 width=33)
>                  (actual time=45.916..3691.540 rows=16 loops=1)
>  Filter: (citext ~~ '%'::citext)
> Total runtime: 3691.676 ms
>
> set enable_seqscan = off;
> explain analyze select * from test where citext like '%'
> ...
> Seq Scan on test  (cost=100.00..1020834.03 rows=5000 width=33)
>                       (actual time=45.578..3761.687 rows=16 loops=1)
>  Filter: (citext ~~ '%'::citext)
> Total runtime: 3761.860 ms
>
>
> With equal I'm getting an index scan
> explain analyze select * from test where citext =
> '9cb65689f035766eb69ed615afd4'
> Index Scan using test_citext_idx on test  (cost=0.00..8.56 rows=1 width=33)
>                       (actual time=0.452..0.462 rows=1 loops=1)
>  Index Cond: (citext = '9cb65689f035766eb69ed615afd4'::citext)
> Total runtime: 0.558 ms
>
> So, is there any way to get the like queries to use the index?
>
>

I don't know if it is a good practice (in this case), but you can
create an index per value
(expressional indexes).

CREATE INDEX xx ON table (citext_column ) WHERE citext_column ~~ '%';

But IMHO if you are interest only in the firsts values (example 4)
you can create an index using hash_text function:

CREATE INDEX xx ON table (hashtext(substring(citext_col,1,4)));
--disable seqscan for little tables
explain select * from pp where (hashtext(substring(i,1,4))) = hashtext('0.06');

Without hashtext:
CREATE INDEX xx ON table (substring(citext_col,1,4));
explain select * from pp where substring(i,1,4) = '0.06';

The entire field to search:
CREATE INDEX xx ON table (hashtext(citext_col));
explain select * from pp where hashtext(i) = hashtext('all the field here');

It is useful?

-- 
  Emanuel Calvo Franco
 DBA at:  www.siu.edu.ar
www.emanuelcalvofranco.com.ar

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

[GENERAL] How to have ant's task insert special chars appropriately?

2009-09-22 Thread agostonbejo 


Hi!

What I'm trying to do is to insert some data from a sql file into a postgres
DB by calling the  ant task. My problem is that I can't get special
characters (even if they can be represented by the standard ASCII charset,
such as ä, ö, ü, é, etc.) to be inserted correctly. 

When I check the DB with a DB browser such as Squirrel or pgAdmin, after
running the  task they can only see question marks where these
characters are supposed to be. So does the actual web application using the
DB. (The web app can store and read such special characters seamlessly,
though. It's also OK if I enter some of them by hand with the DB browsers
mentioned above.)

This all suggests that the problem is with the way I call the  task.
I've tried several versions: inserting from a sql file with iso-8859-1
encoding, then from one with utf-8 encoding. (In pgAdmin I can see the
default encoding of the DB is utf-8.)

Something like this:



(This works perfectly as long as there are no special characters.)

I tried changing the "encoding" attribute above to UTF-8, adding "SET
client_encoding = 'UTF8';" to the UTF-8 file, then to the iso-8859-1 file;
"SET client_encoding = 'LATIN1';" to the iso-8859-1 file (this latter in
addition produced the error "The server's client_encoding parameter was
changed to LATIN1. The JDBC driver requires client_encoding to be UNICODE
for correct operation.")

Does anyone have any idea how to get those special characters loaded into
the DB from a pre-written sql file (possibly with the ant  task)?


Thanks,
Agoston
-- 
View this message in context: 
http://www.nabble.com/How-to-have-ant%27s-%3Csql%3E-task-insert-special-chars-appropriately--tp25530663p25530663.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


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

Re: [GENERAL] VMWare file system / database corruption

2009-09-22 Thread Bill Moran 
In response to Merlin Moncure :

> On Mon, Sep 21, 2009 at 4:03 PM, Scot Kreienkamp  wrote:
> > On the contrary, we've been running PG in production for years now under 
> > VMWare.  Same with MSSQL.  We've never had any problems.  Less so than an 
> > actual physical machine actually since we can move the server to different 
> > physical hardware on demand.  Also makes disaster recovery MUCH easier.
> >
> > However, VMWare does have its places.  A high usage database is not one of 
> > them, IMHO.  A moderately or less used one, depending on requirements and 
> > the hardware backing it, is often a good fit.  And I agree with Scott about 
> > the snapshots.  They do tend to cause temporary communication issues with a 
> > running virtual machine occasionally, regardless of OS or DB type.  (The 
> > benefits outweigh the risks 99% of the time though, with backups being that 
> > 1%.)  In my experience the level of interference from snapshotting a 
> > virtual machine also depends on the type and speed of your physical disks 
> > backing the VMWare host and the size of the virtual machine and any 
> > existing snapshot.  I've been told that in VSPhere (VMWare 4.0) this will 
> > be significantly improved.
> 
> I agree with pretty much everything you've said. I would never put a
> high load system on vmware, but testing, workstation, development,
> legacy etc is all good.  I've never had any type of filesystem
> corruption.

There was a bug in the disk driver for VMWare ESXi not long ago that would
neglect to flush the disk buffers on certain hardware on shutdown.  This,
naturally, would lead to horrendous filesystem corruption if you rebooted.
This bit us pretty hard (we use ESXi for our testing/development systems).

The good news is that VMWare has since issued a patch that fixes the
problem.  The point being that if you're experiencing filesystem corruption
on VMWare, check your version against their updates and see if that's
the cause.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [GENERAL] VMWare file system / database corruption

2009-09-22 Thread Merlin Moncure 
On Mon, Sep 21, 2009 at 4:03 PM, Scot Kreienkamp  wrote:
> On the contrary, we've been running PG in production for years now under 
> VMWare.  Same with MSSQL.  We've never had any problems.  Less so than an 
> actual physical machine actually since we can move the server to different 
> physical hardware on demand.  Also makes disaster recovery MUCH easier.
>
> However, VMWare does have its places.  A high usage database is not one of 
> them, IMHO.  A moderately or less used one, depending on requirements and the 
> hardware backing it, is often a good fit.  And I agree with Scott about the 
> snapshots.  They do tend to cause temporary communication issues with a 
> running virtual machine occasionally, regardless of OS or DB type.  (The 
> benefits outweigh the risks 99% of the time though, with backups being that 
> 1%.)  In my experience the level of interference from snapshotting a virtual 
> machine also depends on the type and speed of your physical disks backing the 
> VMWare host and the size of the virtual machine and any existing snapshot.  
> I've been told that in VSPhere (VMWare 4.0) this will be significantly 
> improved.

I agree with pretty much everything you've said. I would never put a
high load system on vmware, but testing, workstation, development,
legacy etc is all good.  I've never had any type of filesystem
corruption.  I'm guessing the OP's issues are either coming from NFS
or hardware problems on the SAN (IMO not iikely).  I would however
check all software versions, etc. and make sure it's all up to date.

Personally, I avoid NFS like the plague for anything other than basic
file serving.  Running a database through a NAS gateway to a SAN is
crazy...even if it works performance is going to suck.  If it was me
and this was a critical database, I'd dedicate a LUN on the san and
run a fiber cable direct to the vmware box, and mount the storage
directly.

merlin

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

[GENERAL] PLJava not installed for PostgreSQL 8.4 on Solaris 10 x86 64

2009-09-22 Thread Arnold, Sandra 
Is the pljava.so file included in the PostgreSQL 8.4 binary file for Solaris 10 
x86 64-bit?  If not, where can I go to download this library file?  If so, what 
would cause it to not install during the installation process?

Thanks,

Sandra Arnold
Sr. DBA
DOE/OSTI
Oak Ridge, TN

[GENERAL] citext like query and index usage

2009-09-22 Thread Tore Halvorsen 
Hi,

Is it possible to use an index for like queries on a citext column?
I'm using pg 8.4.1 on windows - with no changes to the default configuration.

For example:

CREATE TABLE test ( citext citext NOT NULL );
INSERT INTO test select md5(random()::text) FROM generate_series(0, 100, 1);
CREATE INDEX test_citext_idx ON test USING btree(citext);
vacuum analyze test;

explain analyze select * from test where citext like '%'
...
Seq Scan on test  (cost=0.00..20834.03 rows=5000 width=33)
  (actual time=45.916..3691.540 rows=16 loops=1)
  Filter: (citext ~~ '%'::citext)
Total runtime: 3691.676 ms

set enable_seqscan = off;
explain analyze select * from test where citext like '%'
...
Seq Scan on test  (cost=100.00..1020834.03 rows=5000 width=33)
   (actual time=45.578..3761.687 rows=16 loops=1)
  Filter: (citext ~~ '%'::citext)
Total runtime: 3761.860 ms


With equal I'm getting an index scan
explain analyze select * from test where citext =
'9cb65689f035766eb69ed615afd4'
Index Scan using test_citext_idx on test  (cost=0.00..8.56 rows=1 width=33)
   (actual time=0.452..0.462 rows=1 loops=1)
  Index Cond: (citext = '9cb65689f035766eb69ed615afd4'::citext)
Total runtime: 0.558 ms

So, is there any way to get the like queries to use the index?


-- 
Eld på åren og sol på eng gjer mannen fegen og fjåg. [Jøtul]
 2009 Tore Halvorsen || +052 0553034554

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

Re: [GENERAL] Insert unique fails, still increments ID in a lookup table

2009-09-22 Thread Chris 



The problem is that the uniqueness check apparently increases the serial 
counter and hence I burn through the bigint IDs much faster. It's a waste for 
100m+ records...


It's a little hidden but

http://www.postgresql.org/docs/current/static/functions-sequence.html

Per the last sentence, updates to the sequence are never rolled back - 
the insert is always going to increment the id regardless of whether it 
hits the exception or not.


--
Postgresql & php tutorials
http://www.designmagick.com/


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