Re: [GENERAL] Importance of re-index

2006-08-09 Thread John Sidney-Woollett

Disagree.

We only apply reindex on tables that see lots of updates...

With our 7.4.x databases we vacuum each day, but we see real performance 
gains after re-indexing too - we see lower load averages and no decrease 
in responsiveness over time. Plus we have the benefit of reduced disk 
space usage.


I think that the two things go hand in hand, although vacuum is the most 
important.


John

Jim C. Nasby wrote:

And if you're vacuuming frequently enough, there shouldn't be that much
need to reindex.


---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] Connection string

2006-08-09 Thread Michael Fuhr
On Thu, Aug 10, 2006 at 12:02:24AM -0400, Harpreet Dhaliwal wrote:
> I already read that documentation.
> 
> My ECPG code for connecting to the DB server is:
> 
> EXEC SQL CONNECT TO 192.168.1.100:/xyz

That format isn't shown in the documentation; the ecpg preprocessor
should fail with a syntax error if you try using it.

> i also tried
> 
> tcp:postgresql://192.168.1.100[:*port*][/*dbname*][?*options*]

The above is probably what you need, but without seeing the exact
code you tried it's hard to say why it's not working.

> unix:postgresql://*192.168.1.100*[:*port*][/*dbname*][?*options*]

The ecpg preprocessor shouldn't allow this -- it should fail with
an error like "unix domain sockets only work on 'localhost' but not
on '192.168.1.100'".

> but unfortunately it say DB doesn't exist.

Are you sure the database exists?  Can you connect to it with
psql?

> I don't know the right way to use IP addresses while connecting to a
> postgres DB using ECPG.

If you have a server on 192.168.1.100 listening on the default port
(5432, or whatever PGPORT is set to) and you want to connect to a
database named "mydb" on that server, then the following should
work:

EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100/mydb;

If the database is listening on another port, say 12345, then
this should work:

EXEC SQL CONNECT TO tcp:postgresql://192.168.1.100:12345/mydb;

If you're getting 'database "mydb" does not exist' errors then try
connecting with psql and make sure the database really does exist.
If you still have trouble then please post a minimal but complete
program so we can see everything you're doing.

-- 
Michael Fuhr

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [GENERAL] Accessing Database Data from C Function

2006-08-09 Thread Steve Atkins


On Aug 8, 2006, at 8:33 PM, Jeffrey Bigham wrote:




Do you have a reason to use C instead of a higher-level language?


Not really.  Basically I know C decently enough and would have to
learn PL/pgSQL (although it looks pretty easy).  Perl sounds quite
attractive for the added benefit of text-processing as you mention.  I
also have some vague understanding that C might be faster.  I hope I'm
not opening a can-of-worms, but is C actually faster?


It depends on what you're doing. I've found plpgsql to be a better
match for a lot of purposes, as it has a much better "impedance
match" with the database. I'm a happy C hacker, and use C for
PG functions where it seems appropriate, but use plpgsql for
>90% of my in database work. Performance is probably marginally
slower than C, but development time for day-to-day triggers and
functions is a lot faster. Much easier to maintain, too.

Cheers,
  Steve


---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [GENERAL] Too many open files from postgres.

2006-08-09 Thread Tom Lane
"Mavinakuli, Prasanna (STSD)" <[EMAIL PROTECTED]> writes:
> We have observed that postgres opens many files in data directory 
> And won't close till next vacuum (I experience it)
> When it hits max limit, we won't able to open any other files.

> So what's the solution ..?

Reduce PG's max_files_per_process setting, or increase your kernel's
max-number-of-open-files limit.

regards, tom lane

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [GENERAL] Connection string

2006-08-09 Thread Harpreet Dhaliwal
Hi
I already read that documentation.
 
My ECPG code for connecting to the DB server is:
 
EXEC SQL CONNECT TO 192.168.1.100:/xyz
 
i also tried 
 

tcp:postgresql://192.168.1.100[:port][/dbname][
?options] 

unix:postgresql://192.168.1.100[:port][
/dbname][?options] 
but unfortunately it say DB doesn't exist.
 
I don't know the right way to use IP addresses while connecting to a postgres DB using ECPG. 
On 8/9/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:
On Wed, Aug 09, 2006 at 11:02:00AM -0400, Harpreet Dhaliwal wrote:> I'm trying to connect to postgres database in  a distributed environment,
> say from machine X to Machine Y (Machine Y has postgres DB)> How should my connection string look like in a program in Machine X.>> EXEC SQL CONNECT TO --??? (Do i need to specify the IP of DB server?)
> I tried a few options but nothing works.This appears to be ECPG so see "Connecting to the Database Server"in the ECGP chapter of the documentation:
http://www.postgresql.org/docs/8.1/interactive/ecpg-connect.htmlIf the documentation and examples don't help then please post exactlywhat you've tried and what happened (complete error message, etc.).
--Michael Fuhr


Re: [GENERAL] Accessing Database Data from C Function

2006-08-09 Thread Tom Lane
"Jeffrey Bigham" <[EMAIL PROTECTED]> writes:
> I also have some vague understanding that C might be faster.  I hope I'm
> not opening a can-of-worms, but is C actually faster?

C should theoretically be faster than any of the alternatives you
mention, all else being equal (eg, you are implementing the identical
algorithm in each language).  Whether the difference is enough to notice
is another question --- for example, if effectively all the runtime is
spent inside SQL queries, shaving a few microseconds off the time it
takes you to issue the queries isn't going to improve your life.
You also have to consider the larger effort you'll need to put into
coding in C ... it's a pretty low-level language by any modern standard.
That effort might more profitably be spent elsewhere, eg improving the
SQL queries themselves.

My advice is don't code in C until it's clear you have to.  If you can
prototype in plpgsql or plperl or pl-your-favorite-language, do that
first and get it working; and then recode in C if the performance is
so bad you can't stand it (and you can prove that the cycles are
actually spent in your PL code and not somewhere else like the SQL
engine).  About the only time I'd not do it that way is if my problem
involves data structures too complex to express nicely in
my-favorite-pl or if I need access to low-level database details that
aren't exposed by my-favorite-pl.

regards, tom lane

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] Connection string

2006-08-09 Thread Michael Fuhr
On Wed, Aug 09, 2006 at 11:02:00AM -0400, Harpreet Dhaliwal wrote:
> I'm trying to connect to postgres database in  a distributed environment,
> say from machine X to Machine Y (Machine Y has postgres DB)
> How should my connection string look like in a program in Machine X.
> 
> EXEC SQL CONNECT TO --??? (Do i need to specify the IP of DB server?)
> I tried a few options but nothing works.

This appears to be ECPG so see "Connecting to the Database Server"
in the ECGP chapter of the documentation:

http://www.postgresql.org/docs/8.1/interactive/ecpg-connect.html

If the documentation and examples don't help then please post exactly
what you've tried and what happened (complete error message, etc.).

-- 
Michael Fuhr

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [GENERAL] PITR Questions

2006-08-09 Thread Matthew T. O'Connor

Jim C. Nasby wrote:

Take a look at http://pgfoundry.org/projects/pgpitrha/


I had already seen this however it says that this project has yet to 
release any files, so I thought it was a dead project.  Am I missing 
something?



Also, note that in 8.1, you have to manually archive the last WAL file
after pg_stop_backup(), or you backup is useless until that WAL file
fills up on its own and is archived.


Right, I was hoping to find someone who had well written and tested bash 
script or something that did this.




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


[GENERAL] Connection string

2006-08-09 Thread Harpreet Dhaliwal
Hi,I'm trying to connect to postgres database in  a distributed environment, say from machine X to Machine Y (Machine Y has postgres DB)How should my connection string look like in a program in Machine X.EXEC SQL CONNECT TO --??? (Do i need to specify the IP of DB server?)
I tried a few options but nothing works.Can someone please write this connection string for me?Thanks in advance,~Harpreet


[GENERAL] Too many open files from postgres.

2006-08-09 Thread Mavinakuli, Prasanna (STSD)
Hello All,

We are using postgres with HP-UX.
Usually We will have many records,and will do vacuum once in 2 days.

We have observed that postgres opens many files in data directory 
And won't close till next vacuum (I experience it)
When it hits max limit, we won't able to open any other files.

So what's the solution ..?

1)Is it really postgres issue..doesn't it close opened files immediately
after completion of writing..?
If so how do we handle the scenario of hitting max fd within next
immediate vaccuming..?

2)ideally what's the size of each file..?when it opens second
file..?what's criterion..?
I can observe files of different sizes in the same directories..?


Thx in advance,
Prasanna.
 
*lsof* gives following output.

postmaste 22481 sfmdb9u   REG 64,0x8 16384   6562
/var/opt/sfmdb/pgsql/base/17142/16635
postmaste 22481 sfmdb   10u   REG 64,0x8  8192   4373
/var/opt/sfmdb/pgsql/base/17142/16595
postmaste 22481 sfmdb   11u   REG 64,0x8 16384   6563
/var/opt/sfmdb/pgsql/base/17142/16636
postmaste 22481 sfmdb   12u   REG 64,0x8180224   6510
/var/opt/sfmdb/pgsql/base/17142/16614
postmaste 22481 sfmdb   13u   REG 64,0x8204800   4341
/var/opt/sfmdb/pgsql/base/17142/1259
postmaste 22481 sfmdb   14u   REG 64,0x8 49152   6506
/var/opt/sfmdb/pgsql/base/17142/16613
postmaste 22481 sfmdb   15u   REG 64,0x8 98304   6475
/var/opt/sfmdb/pgsql/base/17142/16610
postmaste 22481 sfmdb   16u   REG 64,0x8360448   4340
/var/opt/sfmdb/pgsql/base/17142/1249
postmaste 22481 sfmdb   17u   REG 64,0x8106496   6569
/var/opt/sfmdb/pgsql/base/17142/16640
postmaste 22481 sfmdb   18u   REG 64,0x8106496   4358
/var/opt/sfmdb/pgsql/base/17142/16392
postmaste 22481 sfmdb   19u   REG 64,0x8 16384   3673
/var/opt/sfmdb/pgsql/base/17142/16652
postmaste 22481 sfmdb   20u   REG 64,0x8 57344   4337
/var/opt/sfmdb/pgsql/base/17142/1247
postmaste 22481 sfmdb   21u   REG 64,0x8 16384   6500
/var/opt/sfmdb/pgsql/base/17142/16612
postmaste 22481 sfmdb   22u   REG 64,0x8 16384   4372
/var/opt/sfmdb/pgsql/base/17142/16418
postmaste 22481 sfmdb   23u   REG 64,0x8 32768   6568
/var/opt/sfmdb/pgsql/base/17142/16639
postmaste 22481 sfmdb   24u   REG 64,0x8122880   6570
/var/opt/sfmdb/pgsql/base/17142/16641
postmaste 22481 sfmdb   25u   REG 64,0x8540672   4224
/var/opt/sfmdb/pgsql/base/17142/1255
postmaste 22481 sfmdb   26u   REG 64,0x8 16384   6554
/var/opt/sfmdb/pgsql/base/17142/16630
postmaste 22481 sfmdb   27u   REG 64,0x8 40960   4356
/var/opt/sfmdb/pgsql/base/17142/16390
postmaste 22481 sfmdb   28u   REG 64,0x8  8192   4360
/var/opt/sfmdb/pgsql/base/17142/16396
postmaste 22481 sfmdb   29u   REG 64,0x8 16384   6109
/var/opt/sfmdb/pgsql/base/17142/16605
postmaste 22481 sfmdb   30u   REG 64,0x8 16384   4361
/var/opt/sfmdb/pgsql/base/17142/16398
postmaste 22481 sfmdb   31u   REG 64,0x8 16384   6460
/var/opt/sfmdb/pgsql/base/17142/16606
postmaste 22481 sfmdb   32u   REG 64,0x8  8192   4362
/var/opt/sfmdb/pgsql/base/17142/16400
postmaste 22481 sfmdb   33u   REG 64,0x8 16384   6553
/var/opt/sfmdb/pgsql/base/17142/16629
postmaste 22481 sfmdb   34u   REG 64,0x8 16384   6574
/var/opt/sfmdb/pgsql/base/17142/16647
postmaste 22481 sfmdb   35u   REG 64,0x8 16384   6634
/var/opt/sfmdb/pgsql/base/17142/17175
postmaste 22481 sfmdb   36u   REG 64,0x8 16384   6461
/var/opt/sfmdb/pgsql/base/17142/16607
postmaste 22481 sfmdb   37u   REG 64,0x8  8192   4342
/var/opt/sfmdb/pgsql/base/17142/16384
postmaste 22481 sfmdb   38u   REG 64,0x8 16384   6516
/var/opt/sfmdb/pgsql/base/17142/16616
postmaste 22481 sfmdb   39u   REG 64,0x8 24576   4344
/var/opt/sfmdb/pgsql/base/17142/16386
postmaste 22481 sfmdb   40u   REG 64,0x8 16384   6565
/var/opt/sfmdb/pgsql/base/17142/16637
postmaste 22481 sfmdb   41u   REG 64,0x8 16384   4359
/var/opt/sfmdb/pgsql/base/17142/16394
postmaste 22481 sfmdb   42u   REG 64,0x8 16384   5566
/var/opt/sfmdb/pgsql/base/17142/16604
postmaste 22481 sfmdb   43u   REG 64,0x8  8192   6752
/var/opt/sfmdb/pgsql/base/17142/17244
postmaste 22481 sfmdb   44u   REG 64,0x8  8192   6755
/var/opt/sfmdb/pgsql/base/17142/17249
postmaste 22481 sfmdb   45u   REG 64,0x8  8192   6746
/var/opt/sfmdb/pgsql/base/17142/17234
postmaste 22481 sfmdb   46u   REG 64,0x8  8192   6743
/var/opt/sfmdb/pgsql/base/17142/17229
postmaste 22481 sfmdb   47u   REG 64,0x8   1228800   6759
/var/opt/sfmdb/pgsql/base/17142/17256
postmaste 22481 sfmdb   48u   REG 64,0x8  

Re: [GENERAL] Too many open files from postgres.

2006-08-09 Thread Mavinakuli, Prasanna (STSD)
Also (obvsly) we aren't able to get a new connection after hitting max
fd limit.. 

Thx,
Prasanna.
-Original Message-
From: Mavinakuli, Prasanna (STSD) 
Sent: Wednesday, August 09, 2006 10:10 AM
To: pgsql-general@postgresql.org
Cc: Mavinakuli, Prasanna (STSD)
Subject: Too many open files from postgres.

Hello All,

We are using postgres with HP-UX.
Usually We will have many records,and will do vacuum once in 2 days.

We have observed that postgres opens many files in data directory And
won't close till next vacuum (I experience it) When it hits max limit,
we won't able to open any other files.

So what's the solution ..?

1)Is it really postgres issue..doesn't it close opened files immediately
after completion of writing..?
If so how do we handle the scenario of hitting max fd within next
immediate vaccuming..?

2)ideally what's the size of each file..?when it opens second
file..?what's criterion..?
I can observe files of different sizes in the same directories..?


Thx in advance,
Prasanna.
 
*lsof* gives following output.

postmaste 22481 sfmdb9u   REG 64,0x8 16384   6562
/var/opt/sfmdb/pgsql/base/17142/16635
postmaste 22481 sfmdb   10u   REG 64,0x8  8192   4373
/var/opt/sfmdb/pgsql/base/17142/16595
postmaste 22481 sfmdb   11u   REG 64,0x8 16384   6563
/var/opt/sfmdb/pgsql/base/17142/16636
postmaste 22481 sfmdb   12u   REG 64,0x8180224   6510
/var/opt/sfmdb/pgsql/base/17142/16614
postmaste 22481 sfmdb   13u   REG 64,0x8204800   4341
/var/opt/sfmdb/pgsql/base/17142/1259
postmaste 22481 sfmdb   14u   REG 64,0x8 49152   6506
/var/opt/sfmdb/pgsql/base/17142/16613
postmaste 22481 sfmdb   15u   REG 64,0x8 98304   6475
/var/opt/sfmdb/pgsql/base/17142/16610
postmaste 22481 sfmdb   16u   REG 64,0x8360448   4340
/var/opt/sfmdb/pgsql/base/17142/1249
postmaste 22481 sfmdb   17u   REG 64,0x8106496   6569
/var/opt/sfmdb/pgsql/base/17142/16640
postmaste 22481 sfmdb   18u   REG 64,0x8106496   4358
/var/opt/sfmdb/pgsql/base/17142/16392
postmaste 22481 sfmdb   19u   REG 64,0x8 16384   3673
/var/opt/sfmdb/pgsql/base/17142/16652
postmaste 22481 sfmdb   20u   REG 64,0x8 57344   4337
/var/opt/sfmdb/pgsql/base/17142/1247
postmaste 22481 sfmdb   21u   REG 64,0x8 16384   6500
/var/opt/sfmdb/pgsql/base/17142/16612
postmaste 22481 sfmdb   22u   REG 64,0x8 16384   4372
/var/opt/sfmdb/pgsql/base/17142/16418
postmaste 22481 sfmdb   23u   REG 64,0x8 32768   6568
/var/opt/sfmdb/pgsql/base/17142/16639
postmaste 22481 sfmdb   24u   REG 64,0x8122880   6570
/var/opt/sfmdb/pgsql/base/17142/16641
postmaste 22481 sfmdb   25u   REG 64,0x8540672   4224
/var/opt/sfmdb/pgsql/base/17142/1255
postmaste 22481 sfmdb   26u   REG 64,0x8 16384   6554
/var/opt/sfmdb/pgsql/base/17142/16630
postmaste 22481 sfmdb   27u   REG 64,0x8 40960   4356
/var/opt/sfmdb/pgsql/base/17142/16390
postmaste 22481 sfmdb   28u   REG 64,0x8  8192   4360
/var/opt/sfmdb/pgsql/base/17142/16396
postmaste 22481 sfmdb   29u   REG 64,0x8 16384   6109
/var/opt/sfmdb/pgsql/base/17142/16605
postmaste 22481 sfmdb   30u   REG 64,0x8 16384   4361
/var/opt/sfmdb/pgsql/base/17142/16398
postmaste 22481 sfmdb   31u   REG 64,0x8 16384   6460
/var/opt/sfmdb/pgsql/base/17142/16606
postmaste 22481 sfmdb   32u   REG 64,0x8  8192   4362
/var/opt/sfmdb/pgsql/base/17142/16400
postmaste 22481 sfmdb   33u   REG 64,0x8 16384   6553
/var/opt/sfmdb/pgsql/base/17142/16629
postmaste 22481 sfmdb   34u   REG 64,0x8 16384   6574
/var/opt/sfmdb/pgsql/base/17142/16647
postmaste 22481 sfmdb   35u   REG 64,0x8 16384   6634
/var/opt/sfmdb/pgsql/base/17142/17175
postmaste 22481 sfmdb   36u   REG 64,0x8 16384   6461
/var/opt/sfmdb/pgsql/base/17142/16607
postmaste 22481 sfmdb   37u   REG 64,0x8  8192   4342
/var/opt/sfmdb/pgsql/base/17142/16384
postmaste 22481 sfmdb   38u   REG 64,0x8 16384   6516
/var/opt/sfmdb/pgsql/base/17142/16616
postmaste 22481 sfmdb   39u   REG 64,0x8 24576   4344
/var/opt/sfmdb/pgsql/base/17142/16386
postmaste 22481 sfmdb   40u   REG 64,0x8 16384   6565
/var/opt/sfmdb/pgsql/base/17142/16637
postmaste 22481 sfmdb   41u   REG 64,0x8 16384   4359
/var/opt/sfmdb/pgsql/base/17142/16394
postmaste 22481 sfmdb   42u   REG 64,0x8 16384   5566
/var/opt/sfmdb/pgsql/base/17142/16604
postmaste 22481 sfmdb   43u   REG 64,0x8  8192   6752
/var/opt/sfmdb/pgsql/base/17142/17244
postmaste 22481 sfmdb   44u   REG 64,0x8  8192   6755
/var/opt/sfmdb/pgsql/base/17142/17249
postmaste 22481 sfmdb   45u   REG 64,0x8  8192   6

[GENERAL] Syslog v/s filename in pg_ctl command

2006-08-09 Thread Mavinakuli, Prasanna (STSD)
Hello All,

When we pass file name in pg_ctl (with option -l) what has to be syslog
value..
Should it be enabled..?if so what cud be the value for syslog..?

Though I have sent approprite file name with -l option ,and if I use
default value for syslog (0) then 
I am getting the messages in console..

So how can I override getting messages in console...?


Thx in advance,
Prasanna.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] Accessing Database Data from C Function

2006-08-09 Thread Jeffrey Bigham

On 8/8/06, Michael Fuhr <[EMAIL PROTECTED]> wrote:

On Tue, Aug 08, 2006 at 12:16:18PM -0700, [EMAIL PROTECTED] wrote:
> I'd like to write a C Function for Postgresql that can access data in
> the database, use that to conduct additional queries to find more
> information in the database and eventually return a result.  I
> currently have the functionality I want implemented as php/C programs
> that first connect to the database, do the processing and then
> disconnect, but I want to make it what I think is called a "stored
> procedure" in other databases.  It seems odd that I would write a C
> program to be included in the database that connects to  the localhost
> - is that what I should do?  Is that somehow more efficient than
> running it as a separate process?

Server-side functions written in C can use the Server Programming
Interface (SPI) to query the database.

http://www.postgresql.org/docs/8.1/interactive/spi.html


Thanks, that's exactly what I was looking for.


Do you have a reason to use C instead of a higher-level language?


Not really.  Basically I know C decently enough and would have to
learn PL/pgSQL (although it looks pretty easy).  Perl sounds quite
attractive for the added benefit of text-processing as you mention.  I
also have some vague understanding that C might be faster.  I hope I'm
not opening a can-of-worms, but is C actually faster?


Functions that are mostly queries are probably best done in SQL or
PL/pgSQL, while text processing and some OS-level access (e.g.,
reading and writing files) can be done with PL/Perl, PL/Tcl,
PL/Python, PL/Ruby, PL/R, etc.  There's even a third-party PL/php
if that's your preferred language:

http://projects.commandprompt.com/public/plphp


Thanks!
Jeff


--
Michael Fuhr



---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly


Re: [GENERAL] Restoring database from old DATA folder

2006-08-09 Thread Patrick TJ McPhee
In article <[EMAIL PROTECTED]>,
RPK <[EMAIL PROTECTED]> wrote:

% I tried to modify the parameters of the .CONF file when the pgsql-8.1
% service was running. The next time I started, it displayed error: "The
% service did not listen to local host". (something like this).
% 
% So I copied the old DATA folder and un-installed PostgreSQL. I again
% re-installed it and replace the new DATA folder with the old one. Both
% previous and new are same versions and same settings were used during
% installation.

I think what you need to do is to fix whatever's wrong with your
config file. When you made a copy of the old data folder, you copied
the config file with it. When you restored the copy, you restored the
config file. Do you have a copy of the version that was used the last
time the database started?

>From what you've said so far, I suggest you search for listen_addresses
and set it to '*'. If the database doesn't start, I strongly suggest
you look in the log for the error messages and report them verbatim
to the list.
-- 

Patrick TJ McPhee
North York  Canada
[EMAIL PROTECTED]

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [GENERAL] Importance of re-index

2006-08-09 Thread Ron Johnson
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Jim C. Nasby wrote:
> On Thu, Aug 03, 2006 at 05:46:44PM -0500, Scott Marlowe wrote:
>> On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote:
[snip]
> And if you're vacuuming frequently enough, there shouldn't be
> that much need to reindex.

How aggressively does PostgreSQL keep b-trees in balance?

Inserting the range [1..1000] should result in a right-
unbalanced tree.

- --
Ron Johnson, Jr.
Jefferson LA  USA

Is "common sense" really valid?
For example, it is "common sense" to white-power racists that
whites are superior to blacks, and that those with brown skins
are mud people.
However, that "common sense" is obviously wrong.
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.3 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org

iD8DBQFE2nZQS9HxQb37XmcRAh4jAJ0bCnk4GOxIt9gUZh9hujDBi/PjwwCfUndS
EB9GruGxVJ9Ja0avpurTKwA=
=g+1r
-END PGP SIGNATURE-

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] PITR timeline question

2006-08-09 Thread Tom Lane
Jim Nasby <[EMAIL PROTECTED]> writes:
> On Aug 3, 2006, at 3:01 PM, Tom Lane wrote:
>> The only way to "roll time backwards" is to replace the data directory
>> with a backup, so if I understand your question correctly, the answer
>> is no.

> Well, specifically I was thinking of...

> Roll PITR forward and start database
> Do a bunch of stuff
> Got more WAL files... start database back in recovery and roll  
> forward to end of new WAL files (throwing away everything that was  
> done previously).

No, that definitely won't work, since any data pages touched by the
"bunch of stuff" and not by the new WAL files will not have been
reverted to their prior states --- while any pages touched in common
*will* have been overwritten.  Net result: inconsistent database.

There's been some speculation about allowing a standby server to execute
purely read-only operations, but it's just speculation so far.

regards, tom lane

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [GENERAL] PITR timeline question

2006-08-09 Thread Jim Nasby

On Aug 3, 2006, at 3:01 PM, Tom Lane wrote:

Jim Nasby <[EMAIL PROTECTED]> writes:

If you're using multiple timelines with PITR, do you have to re-copy
all the data files into the cluster every time you start a new
timeline? Or can you copy the data files out of the backup once, and
then perform multiple recoveries, each to different timelines?


The only way to "roll time backwards" is to replace the data directory
with a backup, so if I understand your question correctly, the answer
is no.


Well, specifically I was thinking of...

Roll PITR forward and start database
Do a bunch of stuff
Got more WAL files... start database back in recovery and roll  
forward to end of new WAL files (throwing away everything that was  
done previously).

--
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Dann Corbit
> -Original Message-
> From: [EMAIL PROTECTED] [mailto:pgsql-general-
> [EMAIL PROTECTED] On Behalf Of Bruce Momjian
> Sent: Wednesday, August 09, 2006 2:19 PM
> To: Shoaib Mir
> Cc: DEV; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] WIN32 Build?
> 
> Shoaib Mir wrote:
> > Dev,
> >
> > You are doing it a little wrong for Debug version, in order to build
the
> > Debug version use the following:
> >
> > nmake /f win32.mak DEBUG=1
> >
> > Bruce,
> >
> > If you remove "inline" the build process goes fine and if you dont,
it
> first
> > gives a few warning and in the end quits the build process with a
fatal
> > error.
> 
> OK, good to know.  If we ever find a symbol that is defined for that
> compiler that we can test, we can fix this.

#ifdef _MSC_VER
#define inline __inline
#endif

An alternative would be to do this:

#ifdef _MSC_VER
#define inline 
#endif

And let the compiler inline with /Ob2 (which generally makes better
decisions than the programmer anyway).

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [GENERAL] Not so much load balancing as load limits

2006-08-09 Thread Jim C. Nasby
On Wed, Aug 09, 2006 at 10:22:20AM -0400, Kenneth Downs wrote:
> I'm having some trouble getting a google hit on this topic, and the docs 
> aren't doing much for me.
> 
> What I'm wondering is, how do I limit any particular postgres operation, 
> in particular a long-running batch operation with lots of inserts, from 
> bogging down a server?
> 
> This is not so much a question of how to code the SQL, as it is how to 
> keep a selfish process from causing a self-inflicted DoS situation. 
> 
> Can anybody get me pointed in the right direction with a link or two?  
> Thanks.

Right now, you basically don't. :( Unless you manually break your
operation up into multiple steps.

There is a lot of discussion on bizgres-general right now about
statement queuing, which migth help in your case.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] Importance of re-index

2006-08-09 Thread Jim C. Nasby
On Thu, Aug 03, 2006 at 05:46:44PM -0500, Scott Marlowe wrote:
> On Thu, 2006-08-03 at 17:44, John Sidney-Woollett wrote:
> > In addition to making sure databases are vacuumed regularly, it is worth 
> > running REINDEX on tables that see a lot of updates (or insert/deletes).
> > 
> > Running REINDEX on a regular basis will keep the indexes compacted and 
> > can noticeably improve the database performance.
> > 
> > The other benefit is that the disk space taken by your database can be 
> > significantly reduced.
> > 
> > This is barely mentioned in the 7.4 docs, and alluded to in the 8.1 docs.
> > 
> > FWIW, in my experience it is DEFINITELY worth reindexing regularly.
> 
> But note that reindex is one of those "invasive" commands that may cause
> problems for certain types of 24/7 operations, while vacuum is meant to
> run concurrently almost any time of day.  Reindex was originally
> designed to fix broken indexes, and, at least in earlier encarnations,
> should something stop it in the middle of reindexing I believe it is
> possible to be left with no index.
> 
> It's got its uses, but it's got its issues as well.

And if you're vacuuming frequently enough, there shouldn't be that much
need to reindex.
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [GENERAL] PITR Questions

2006-08-09 Thread Jim C. Nasby
On Fri, Aug 04, 2006 at 03:46:09PM -0400, Matthew T. O'Connor wrote:
> Chander Ganesan wrote:
> >Matthew T. O'Connor wrote:
> >>I have done some googling for real world archive_command examples and 
> >>haven't really found anything.  The example in the PGSQL Docs are 
> >>qualified by (This is an example, not a recommendation, and may not 
> >>work on all platforms.)
> >>
> >>I have it set as follows:
> >>archive_command = 'rsync -a %p backup_server:/pgsql_pitr/%f'
> >It doesn't look to be a *bad* choice.  I'd definitely recommend 
> >keeping a copy off of the current system - which you do here.  You 
> >might also consider keeping a local copy (so you don't have to copy 
> >them back if you have to do a local recovery).
> 
> I know this can, but what I'm looking for is if someone has written some 
> scripts that I can crib from that offer some additional features such as 
> protection from overwriting an existing file, notification of the admin 
> in case of failure etc..
 
Take a look at http://pgfoundry.org/projects/pgpitrha/

Also, note that in 8.1, you have to manually archive the last WAL file
after pg_stop_backup(), or you backup is useless until that WAL file
fills up on its own and is archived.

There's a bunch of new functions in 8.2 that will make a lot of this
stuff easier, btw.

> >>Also, I'm concerned that this clients website has extended periods of 
> >>time where it's very low traffic, which will result in the same WAL 
> >>file being used for long periods of time and not getting archived.  
> >>Does anyone have a tested script available for grabbing the most 
> >>recent WAL file?  I can write one myself, but it seems this is 
> >>information that should be posted somewhere.
> >The checkpoint_timeout value should help with this - its default is 
> >300 seconds, so you should checkpoint at least once every 5 minutes.
> 
> I don't see how checkpoint_timeout is relevant.  Just because we 
> checkpoint doesn't mean the WAL file will get archived.  I have to have 
> 16M of WAL traffic before a file gets archived regardless of 
> check-pointing, or am I missing something?
 
You're not.

> >You could setup a 'hot standby' system that uses a tool like cron to 
> >periodically sync your pg_xlog directory to your backup server (or 
> >just sync it so you have it..)   - which might be useful if you go for 
> >long periods of time between checkpoints.  A common scenario is to 
> >place one server into a "constant recovery" mode by using a 
> >restore_command that waits for new logs to be available before copying 
> >them.  Periodically sync your pg_xlog directory in this case to ensure 
> >that when you need to recover you'll have most of what you need...but 
> >perhaps not all. 
> 
> I say the "hot standby" is a common scenario, yet I'm not sure it's even 
> possible since the docs only mention it in passing, and I wasn't able to 
> find anyone example script that implements a restore_command that does 
> this.  Am I missing something that is obvious?

See above pgfoundry link. :)
-- 
Jim C. Nasby, Sr. Engineering Consultant  [EMAIL PROTECTED]
Pervasive Software  http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf   cell: 512-569-9461

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Reece Hart




On Wed, 2006-08-09 at 16:54 -0400, Tom Laudeman wrote:

Great suggestion. I've read about CLUSTER, but never had a chance to use it. The only problem is that this table with 9 million records has 5 or 6 indexes. It is hard to pick the most used, but I'll bet CLUSTER will make at least one of the queries run very fast, especially for an index with a small number of distinct values.


Tom-

I found multi-column indexes and clustering to be extremely beneficial in 7.4. I still use them in 8.1, but I haven't compared them extensively with equivalent queries that use bitmap index scans. The obvious downside of having more indexes is the additional time and space overhead during insert, update, or delete.

The approach I took to design multi-column indexes was to run explain on representative queries and look for seq scans. The seq scans indicate which columns /might/ be well-served by indexes. In 7.4, the order of indexed columns was important. (I saw something in the 8.1 release notes that made me think that this was no longer true, but I haven't verified that.) 

-Reece





-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0








Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Shoaib Mir
As a workaround if you dont want to make the change and delete "inline" from wchar.c then you can do the following change in the win32.mak file for libpq:You can see at line 121 in win32.mak file as:
"WIN32" /D "_WINDOWS" /Fp"$(INTDIR)\libpq.pch" /YX\Change it to"WIN32" /D "_WINDOWS" /D "inline=__inline" /Fp"$(INTDIR)\libpq.pch" /YX\
This will help get rid of the libpq build problem without needing to remove "inline" from wchar.c file.Thanks,-- Shoaib MirEnterpriseDB (www.enterprisedb.com
)On 8/10/06, Bruce Momjian <[EMAIL PROTECTED]> wrote:
Shoaib Mir wrote:> Dev,>> You are doing it a little wrong for Debug version, in order to build the> Debug version use the following:>> nmake /f win32.mak DEBUG=1>> Bruce,
>> If you remove "inline" the build process goes fine and if you dont, it first> gives a few warning and in the end quits the build process with a fatal> error.OK, good to know.  If we ever find a symbol that is defined for that
compiler that we can test, we can fix this.--->> Log shows:>> ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> ';' be> fore '{'> ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'> undefined; a> ssuming extern returning int> ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'
> undefined;> assuming extern returning int> NMAKE : fatal error U1077: 'cl.exe' : return code '0x2'> Stop.>> Thanks,> --> Shoaib Mir> EnterpriseDB (
www.enterprisedb.com)>> On 8/10/06, DEV <[EMAIL PROTECTED]> wrote:> >> >  Okay I get a build and I get the release of the libpq built but it does
> > not build the Debug version of that.  I have tried going to the directory> > and running nmake /f win32.mak DEBUG but it errors with:> >> > Building the Win32 static library...
> >> >> >> > NMAKE : fatal error U1073: don't know how to make 'DEBUG'> >> > Stop.> >> >> >> > So what am I missing.  I see in the mak file for debug but I can not see
> > how to enable that?> >> >> >  --> >> > *From:* [EMAIL PROTECTED]
 [mailto:> > [EMAIL PROTECTED]] *On Behalf Of *Shoaib Mir> > *Sent:* Wednesday, August 09, 2006 3:30 PM> > *To:* DEV
> > *Cc:* pgsql-general@postgresql.org> > *Subject:* Re: [GENERAL] WIN32 Build?> >> >> >> > I too faced same kind of a problem building libpq on Windows and as a
> > solution to it removed "inline" from the file "\backend\utils\mb\wchar.c"> >> > Did the following ... changed "static inline int" to "static int" instead
> > at a couple of places in the wchar.c file> >> > Run "nmake" after doing this specific change and hopefully it will solve> > the problem.> >> > Thanks,
> > --> > Shoaib Mir> > EnterpriseDB ( www.enterprisedb.com)> >> >  On 8/9/06, *DEV* <[EMAIL PROTECTED]
> wrote:> >> > Hello all.> >> >   I am trying to build postgres from the source on a WINXP system. I> > am using MSVC++.Net and I use the link with it to get to the command
> > prompt.  After I do that I run vcvars32.bat and the nmake /f win32.mak and> > I get:> >> >> >> > C:\Dev\postgresql-8.1.4\src>nmake /f win32.mak> >
> >> >> > Microsoft (R) Program Maintenance Utility Version 7.10.3077> >> > Copyright (C) Microsoft Corporation.  All rights reserved.> >> >> >
> > cd include> >> > if not exist pg_config.h copy pg_config.h.win32 pg_config.h> >> > cd ..> >> > cd interfaces\libpq
> >> > nmake /f win32.mak> >> >> >> > Microsoft (R) Program Maintenance Utility Version 7.10.3077> >> > Copyright (C) Microsoft Corporation.  All rights reserved.
> >> >> >> > Building the Win32 static library...> >> >> >> > cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp> >> > cl : Command line warning D4029 : optimization is not available in the
> > standard> >> > edition compiler> >> > wchar.c> >> > ..\..\backend\utils\mb\wchar.c(100) : error C2054: expected '(' to follow> > 'inlin
> >> > e'> >> > ..\..\backend\utils\mb\wchar.c(101) : error C2085: 'pg_euc_mblen' : not in> > forma> >> > l parameter list> >> > ..\..\backend\utils\mb\wchar.c(101) : error C2143: syntax error : missing
> > ';' be> >> > fore '{'> >> > ..\..\backend\utils\mb\wchar.c(116) : error C2054: expected '(' to follow> > 'inlin> >> > e'> >
> > ..\..\backend\utils\mb\wchar.c(117) : error C2085: 'pg_euc_dsplen' : not> > in form> >> > al parameter list> >> > ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> > ';' be> >> > fore '{'> >> > ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'> > undefined; a> >> > ssuming extern returning int
> >> > ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'> > undefined;> >> > assuming extern returning int> >> > NMAKE : fatal error U1077: '
cl.exe' : return code '0x2'> >> > Stop.> >> > NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio .NET> > 2003\> >> > VC7\BIN\nmake.exe"' : return code '0x2'
> >> > Stop.> >> >> >> > What do you I need to change to mak

Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Bruce Momjian
Shoaib Mir wrote:
> Dev,
> 
> You are doing it a little wrong for Debug version, in order to build the
> Debug version use the following:
> 
> nmake /f win32.mak DEBUG=1
> 
> Bruce,
> 
> If you remove "inline" the build process goes fine and if you dont, it first
> gives a few warning and in the end quits the build process with a fatal
> error.

OK, good to know.  If we ever find a symbol that is defined for that
compiler that we can test, we can fix this.

---


> 
> Log shows:
> 
> ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> ';' be
> fore '{'
> ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'
> undefined; a
> ssuming extern returning int
> ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'
> undefined;
> assuming extern returning int
> NMAKE : fatal error U1077: 'cl.exe' : return code '0x2'
> Stop.
> 
> Thanks,
> -- 
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)
> 
> On 8/10/06, DEV <[EMAIL PROTECTED]> wrote:
> >
> >  Okay I get a build and I get the release of the libpq built but it does
> > not build the Debug version of that.  I have tried going to the directory
> > and running nmake /f win32.mak DEBUG but it errors with:
> >
> > Building the Win32 static library...
> >
> >
> >
> > NMAKE : fatal error U1073: don't know how to make 'DEBUG'
> >
> > Stop.
> >
> >
> >
> > So what am I missing.  I see in the mak file for debug but I can not see
> > how to enable that?
> >
> >
> >  --
> >
> > *From:* [EMAIL PROTECTED] [mailto:
> > [EMAIL PROTECTED] *On Behalf Of *Shoaib Mir
> > *Sent:* Wednesday, August 09, 2006 3:30 PM
> > *To:* DEV
> > *Cc:* pgsql-general@postgresql.org
> > *Subject:* Re: [GENERAL] WIN32 Build?
> >
> >
> >
> > I too faced same kind of a problem building libpq on Windows and as a
> > solution to it removed "inline" from the file "\backend\utils\mb\wchar.c"
> >
> > Did the following ... changed "static inline int" to "static int" instead
> > at a couple of places in the wchar.c file
> >
> > Run "nmake" after doing this specific change and hopefully it will solve
> > the problem.
> >
> > Thanks,
> > --
> > Shoaib Mir
> > EnterpriseDB ( www.enterprisedb.com)
> >
> >  On 8/9/06, *DEV* <[EMAIL PROTECTED]> wrote:
> >
> > Hello all.
> >
> >   I am trying to build postgres from the source on a WINXP system. I
> > am using MSVC++.Net and I use the link with it to get to the command
> > prompt.  After I do that I run vcvars32.bat and the nmake /f win32.mak and
> > I get:
> >
> >
> >
> > C:\Dev\postgresql-8.1.4\src>nmake /f win32.mak
> >
> >
> >
> > Microsoft (R) Program Maintenance Utility Version 7.10.3077
> >
> > Copyright (C) Microsoft Corporation.  All rights reserved.
> >
> >
> >
> > cd include
> >
> > if not exist pg_config.h copy pg_config.h.win32 pg_config.h
> >
> > cd ..
> >
> > cd interfaces\libpq
> >
> > nmake /f win32.mak
> >
> >
> >
> > Microsoft (R) Program Maintenance Utility Version 7.10.3077
> >
> > Copyright (C) Microsoft Corporation.  All rights reserved.
> >
> >
> >
> > Building the Win32 static library...
> >
> >
> >
> > cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp
> >
> > cl : Command line warning D4029 : optimization is not available in the
> > standard
> >
> > edition compiler
> >
> > wchar.c
> >
> > ..\..\backend\utils\mb\wchar.c(100) : error C2054: expected '(' to follow
> > 'inlin
> >
> > e'
> >
> > ..\..\backend\utils\mb\wchar.c(101) : error C2085: 'pg_euc_mblen' : not in
> > forma
> >
> > l parameter list
> >
> > ..\..\backend\utils\mb\wchar.c(101) : error C2143: syntax error : missing
> > ';' be
> >
> > fore '{'
> >
> > ..\..\backend\utils\mb\wchar.c(116) : error C2054: expected '(' to follow
> > 'inlin
> >
> > e'
> >
> > ..\..\backend\utils\mb\wchar.c(117) : error C2085: 'pg_euc_dsplen' : not
> > in form
> >
> > al parameter list
> >
> > ..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing
> > ';' be
> >
> > fore '{'
> >
> > ..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen'
> > undefined; a
> >
> > ssuming extern returning int
> >
> > ..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen'
> > undefined;
> >
> > assuming extern returning int
> >
> > NMAKE : fatal error U1077: 'cl.exe' : return code '0x2'
> >
> > Stop.
> >
> > NMAKE : fatal error U1077: '"C:\Program Files\Microsoft Visual Studio .NET
> > 2003\
> >
> > VC7\BIN\nmake.exe"' : return code '0x2'
> >
> > Stop.
> >
> >
> >
> > What do you I need to change to make this work?
> >
> >
> >
> > Brian Doyle
> >
> >
> >
> >
> >
> >
> >
> >
> 
> 
> -- 
> Shoaib Mir
> EnterpriseDB (www.enterprisedb.com)

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 9: In versio

Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Shoaib Mir
Dev,You are doing it a little wrong for Debug version, in order to build the Debug version use the following:nmake /f win32.mak DEBUG=1Bruce,If you remove "inline" the build process goes fine and if you dont, it first gives a few warning and in the end quits the build process with a fatal error.
Log shows:..\..\backend\utils\mb\wchar.c(117) : error C2143: syntax error : missing ';' before '{'..\..\backend\utils\mb\wchar.c(143) : warning C4013: 'pg_euc_mblen' undefined; assuming extern returning int
..\..\backend\utils\mb\wchar.c(180) : warning C4013: 'pg_euc_dsplen' undefined;assuming extern returning intNMAKE : fatal error U1077: 'cl.exe' : return code '0x2'Stop.Thanks,-- Shoaib Mir
EnterpriseDB (www.enterprisedb.com)On 8/10/06, DEV <[EMAIL PROTECTED]> wrote:














Okay I get a
build and I get the release of the libpq built but it does not build the Debug
version of that.  I have tried going to the directory and running nmake /f
win32.mak DEBUG but it errors with:

Building the
Win32 static library...

 

NMAKE : fatal
error U1073: don't know how to make 'DEBUG'

Stop.

 

So what am I
missing.  I see in the mak file for debug but I can not see how to enable that?

 









From:
[EMAIL PROTECTED] [mailto:
[EMAIL PROTECTED]] On Behalf Of Shoaib Mir
Sent: Wednesday, August 09, 2006
3:30 PM
To: DEV
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] WIN32
Build?



 

I too faced same kind of a
problem building libpq on Windows and as a solution to it removed
"inline" from the file "\backend\utils\mb\wchar.c"

Did the following ... changed "static inline int" to "static
int" instead at a couple of places in the wchar.c file 

Run "nmake" after doing this specific change and hopefully it will
solve the problem.

Thanks,
-- 
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)





On 8/9/06, DEV
<[EMAIL PROTECTED]> wrote:







Hello all. 

  I am trying
to build postgres from the source on a WINXP system. I am using MSVC++.Net and
I use the link with it to get to the command prompt.  After I do that I
run vcvars32.bat and the nmake /f win32.mak and I get:

 

C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak

 

Microsoft (R) Program Maintenance
Utility Version 7.10.3077

Copyright (C) Microsoft
Corporation.  All rights reserved.

 

   
cd include

   
if not exist pg_config.h copy pg_config.h.win32 pg_config.h

   
cd ..

   
cd interfaces\libpq

   
nmake /f win32.mak

 

Microsoft (R) Program Maintenance
Utility Version 7.10.3077

Copyright (C) Microsoft
Corporation.  All rights reserved.

 

Building the Win32 static library...

 

   
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp

cl : Command line warning D4029 :
optimization is not available in the standard

edition compiler

wchar.c

..\..\backend\utils\mb\wchar.c(100) :
error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(101) :
error C2085: 'pg_euc_mblen' : not in forma

l parameter list

..\..\backend\utils\mb\wchar.c(101) :
error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(116) :
error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(117) :
error C2085: 'pg_euc_dsplen' : not in form

al parameter list

..\..\backend\utils\mb\wchar.c(117) :
error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(143) :
warning C4013: 'pg_euc_mblen' undefined; a

ssuming extern returning int

..\..\backend\utils\mb\wchar.c(180) :
warning C4013: 'pg_euc_dsplen' undefined;

assuming extern returning int

NMAKE : fatal error U1077: 'cl.exe' :
return code '0x2'

Stop.

NMAKE : fatal error U1077:
'"C:\Program Files\Microsoft Visual Studio .NET 2003\

VC7\BIN\nmake.exe"' : return
code '0x2'

Stop.

 

What do you I need to change to make
this work?

 

Brian Doyle

 

 



















-- Shoaib MirEnterpriseDB (www.enterprisedb.com)


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Michael Fuhr
On Wed, Aug 09, 2006 at 04:54:00PM -0400, Tom Laudeman wrote:
> I'll try CLUSTER (I'm looking forward to that test), but if we really 
> need speed, it will probably be necessary to create copies of the table, 
> or copy portions of the table elsewhere (essentially creating 
> materialized views, I suppose). I'm still trying to get my science 
> compatriot here to tell me which index he most wants to improve, then 
> I'll CLUSTER the table on that index.

If you enable statistics collection then you could use those
statistics to see which indexes are used the most.  Those indexes
might be good candidates for clustering.

http://www.postgresql.org/docs/8.1/interactive/monitoring-stats.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Tom Laudeman




Michael,
Great suggestion. I've read about CLUSTER, but never had a chance to
use it. The only problem is that this table with 9 million records has
5 or 6 indexes. It is hard to pick the most used, but I'll bet CLUSTER
will make at least one of the queries run very fast, especially for an
index with a small number of distinct values.

The speed of the query is (as Michael implies) limited to the rate at
which the disk can seek and read.  I have done experiments with views
and cursors; there was no improvement in speed. I've also tried only
pulling back  primary keys in the hope that a smaller amount of data
would more quickly be read into memory. No speed increase. I have also
raised all the usual memory limits, with the expected results (slight
speed improvements). 

I'll try CLUSTER (I'm looking forward to that test), but if we really
need speed, it will probably be necessary to create copies of the
table, or copy portions of the table elsewhere (essentially creating
materialized views, I suppose). I'm still trying to get my science
compatriot here to tell me which index he most wants to improve, then
I'll CLUSTER the table on that index.

Thanks!
Tom

Michael Fuhr wrote:

  On Wed, Aug 09, 2006 at 03:46:38PM +0200, Martijn van Oosterhout wrote:
  
  
On Wed, Aug 09, 2006 at 09:19:31AM -0400, Tom Laudeman wrote:


  Is there a tuning parameter I can change to increase speed of selects? 
Clearly, there's already some buffering going on since selecting an 
indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 
seconds on the second try (from pgsql).
  

Your OS is probably buffering, 1GB of RAM holds a lot of data. You can
try increasing the shared_buffers parameter, but if the delay is
getting data from the disk, that won't really help you.

  
  
If most of your queries use the same index then clustering on that
index might speed up initial (i.e., not-cached) queries by reducing
the number of disk pages that need to be read.  See the documentation
for more information.

http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html

  


-- 
Tom Laudeman
[EMAIL PROTECTED]
(434) 924-2456
http://www.people.virginia.edu/~twl8n/
http://laudeman.com/





Re: [GENERAL] WIN32 Build?

2006-08-09 Thread DEV








Okay I get a
build and I get the release of the libpq built but it does not build the Debug
version of that.  I have tried going to the directory and running nmake /f
win32.mak DEBUG but it errors with:

Building the
Win32 static library...

 

NMAKE : fatal
error U1073: don't know how to make 'DEBUG'

Stop.

 

So what am I
missing.  I see in the mak file for debug but I can not see how to enable that?

 









From:
[EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Shoaib Mir
Sent: Wednesday, August 09, 2006
3:30 PM
To: DEV
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] WIN32
Build?



 

I too faced same kind of a
problem building libpq on Windows and as a solution to it removed
"inline" from the file "\backend\utils\mb\wchar.c"

Did the following ... changed "static inline int" to "static
int" instead at a couple of places in the wchar.c file 

Run "nmake" after doing this specific change and hopefully it will
solve the problem.

Thanks,
-- 
Shoaib Mir
EnterpriseDB ( www.enterprisedb.com)





On 8/9/06, DEV
<[EMAIL PROTECTED]> wrote:







Hello all. 

  I am trying
to build postgres from the source on a WINXP system. I am using MSVC++.Net and
I use the link with it to get to the command prompt.  After I do that I
run vcvars32.bat and the nmake /f win32.mak and I get:

 

C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak

 

Microsoft (R) Program Maintenance
Utility Version 7.10.3077

Copyright (C) Microsoft
Corporation.  All rights reserved.

 

   
cd include

   
if not exist pg_config.h copy pg_config.h.win32 pg_config.h

   
cd ..

   
cd interfaces\libpq

   
nmake /f win32.mak

 

Microsoft (R) Program Maintenance
Utility Version 7.10.3077

Copyright (C) Microsoft
Corporation.  All rights reserved.

 

Building the Win32 static library...

 

   
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp

cl : Command line warning D4029 :
optimization is not available in the standard

edition compiler

wchar.c

..\..\backend\utils\mb\wchar.c(100) :
error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(101) :
error C2085: 'pg_euc_mblen' : not in forma

l parameter list

..\..\backend\utils\mb\wchar.c(101) :
error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(116) :
error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(117) :
error C2085: 'pg_euc_dsplen' : not in form

al parameter list

..\..\backend\utils\mb\wchar.c(117) :
error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(143) :
warning C4013: 'pg_euc_mblen' undefined; a

ssuming extern returning int

..\..\backend\utils\mb\wchar.c(180) :
warning C4013: 'pg_euc_dsplen' undefined;

assuming extern returning int

NMAKE : fatal error U1077: 'cl.exe' :
return code '0x2'

Stop.

NMAKE : fatal error U1077:
'"C:\Program Files\Microsoft Visual Studio .NET 2003\

VC7\BIN\nmake.exe"' : return
code '0x2'

Stop.

 

What do you I need to change to make
this work?

 

Brian Doyle

 

 




















Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Scott Marlowe
Title: Re: [GENERAL] Tuning to speed select






On Wed, 2006-08-09 at 14:58, louis gonzales wrote:
> I'm not so sure about that, when you create a view on a table - at least
> with Oracle - which is a subset(the trivial or 'proper' subset is the
> entire table view) of the information on a table, when a select is
> issued against a table, Oracle at least, determines if there is a view
> already on a the table which potentially has a smaller amount of
> information to process - as long as the view contains the proper
> constraints that meet your 'select' criteria, the RDBMS engine will have
> fewer records to process - which I'd say, certainly constitutes a time
> benefit, in terms of 'performance gain.'
>
> Hence my reasoning behind determining IF there is a subset of the 'big
> table' that is frequented, I'd create a view on this, assuming
> postgresql does this too?  Maybe somebody else can answer that for the
> pgsql-general's general information?
>
> query-speed itself is going to be as fast/slow as your system is
> configured for, however my point was to shave some time off of a 1M+
> record table, but implementing views of 'frequently' visisted/hit
> records meeting the same specifications.

There are basically two ways to do views.  The simple way, is to have a
view represent a query that gets run everytime you call it.  The more
complex way is to "materialize" the view data, and put it into a new
table, and then update that table whenever the source table changes.

PostgreSQL has native support for the first type.  They're cheap and
easy, and work for most of the things people need views for (i.e. hiding
complexity).

PostgreSQL is extensible, and therefore you can institute the second
type (i.e. materialized views) on your own.  Thanksfully, someone else
has already done most of the work for us, by the name of Jonathan
Gardner, and you can find his nifty guide by typing "materialized views
postgresql" into google.

Gardner's materialized views support several update methods depending on
what you need from your mat views.  It's also a danged fine tutorial on
how to write some simple plpgsql functions.






Re: [GENERAL] PL/pgSQL Problem

2006-08-09 Thread Ron St-Pierre

Michael Fuhr wrote:

On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote:
  

ERROR:  duplicate key violates unique constraint "systemcounts_pkey"
CONTEXT:  SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( 
$1 ::date)"

PL/pgSQL function "updatesystemcounts" line 8 at SQL statement



Is the table's primary key a serial column?  If so then the sequence
might be out of sync with the values in the table, in which case
you'll need to adjust the sequence's value with ALTER SEQUENCE or
setval().

  

Aha, that was exactly what the problem was.

Thanks!

Ron

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Bruce Momjian
Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Interesting.  It seem the key line is here:
> 
> > cl : Command line warning D4029 : optimization is not available in the
> > standard edition compiler
> 
> > So the "standard" version doesn't support inline functions.
> 
> Doesn't it simply ignore the inline keyword then?  Count on Microsoft
> for poorly designed tools, I suppose.

You would think so.  Can someone confirm that the warning generates
errors later on?

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Tom Lane
Bruce Momjian <[EMAIL PROTECTED]> writes:
> Interesting.  It seem the key line is here:

> cl : Command line warning D4029 : optimization is not available in the
> standard edition compiler

> So the "standard" version doesn't support inline functions.

Doesn't it simply ignore the inline keyword then?  Count on Microsoft
for poorly designed tools, I suppose.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread louis gonzales
I'm not so sure about that, when you create a view on a table - at least 
with Oracle - which is a subset(the trivial or 'proper' subset is the 
entire table view) of the information on a table, when a select is 
issued against a table, Oracle at least, determines if there is a view 
already on a the table which potentially has a smaller amount of 
information to process - as long as the view contains the proper 
constraints that meet your 'select' criteria, the RDBMS engine will have 
fewer records to process - which I'd say, certainly constitutes a time 
benefit, in terms of 'performance gain.'


Hence my reasoning behind determining IF there is a subset of the 'big 
table' that is frequented, I'd create a view on this, assuming 
postgresql does this too?  Maybe somebody else can answer that for the 
pgsql-general's general information?


query-speed itself is going to be as fast/slow as your system is 
configured for, however my point was to shave some time off of a 1M+ 
record table, but implementing views of 'frequently' visisted/hit 
records meeting the same specifications.


Harald Armin Massa wrote:


Louis,

Views certainly help in managing complexity. They do nothing to 
improve query-speed.


Querying a view gets rewritten to queries to the underlying tables on 
the fly.
(as long as there are no materialized views, which are still on a the 
TODO list)


--
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Reinsburgstraße 202b
70197 Stuttgart
0173/9409607
-
Let's set so double the killer delete select all. 




---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Bruce Momjian
DEV wrote:
> Shoaib,
>   That looks like it worked thank you!
> 
> Bruce,
>   I am using the following compliler:
> C:\Dev\postgresql-8.1.4\src\interfaces\libpq\Release>nmake --help
> 
> Microsoft (R) Program Maintenance Utility Version 7.10.3077
> Copyright (C) Microsoft Corporation.  All rights reserved.
> 
> Which came with 
> Microsoft Visual C++ .NET  Version 7.1.3088
> 
> Don't know if that helps or not!

Not really.  I need to see all the defines predefined by the compiler. 
Anyone know how to do that on MSVC?

-- 
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match


Re: [GENERAL] WIN32 Build?

2006-08-09 Thread DEV
Shoaib,
That looks like it worked thank you!

Bruce,
I am using the following compliler:
C:\Dev\postgresql-8.1.4\src\interfaces\libpq\Release>nmake --help

Microsoft (R) Program Maintenance Utility Version 7.10.3077
Copyright (C) Microsoft Corporation.  All rights reserved.

Which came with 
Microsoft Visual C++ .NET  Version 7.1.3088

Don't know if that helps or not!

-Original Message-
From: Bruce Momjian [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, August 09, 2006 3:41 PM
To: Shoaib Mir
Cc: DEV; pgsql-general@postgresql.org
Subject: Re: [GENERAL] WIN32 Build?

Shoaib Mir wrote:
> I too faced same kind of a problem building libpq on Windows and as a
> solution to it removed "inline" from the file
> "\backend\utils\mb\wchar.c"
> 
> Did the following ... changed "static inline int" to "static int"
> instead at a couple of places in the wchar.c file
> 
> Run "nmake" after doing this specific change and hopefully it will
> solve the problem.

Interesting.  It seem the key line is here:

> > cl : Command line warning D4029 : optimization is not available in the
> > standard edition compiler

So the "standard" version doesn't support inline functions.  To fix
that, I think we need some compiler define test to tell us if it is a
"standard" edition compiler.

--
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +




---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Richard Broersma Jr
> Views certainly help in managing complexity. They do nothing to improve
> query-speed.
> 
> Querying a view gets rewritten to queries to the underlying tables on the
> fly.
> (as long as there are no materialized views, which are still on a the TODO
> list)

Would partial indexs on the most queried regions of the table help in query 
speed?

Regards,

Richard Broersma Jr.

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Bruce Momjian
Shoaib Mir wrote:
> I too faced same kind of a problem building libpq on Windows and as a
> solution to it removed "inline" from the file
> "\backend\utils\mb\wchar.c"
> 
> Did the following ... changed "static inline int" to "static int"
> instead at a couple of places in the wchar.c file
> 
> Run "nmake" after doing this specific change and hopefully it will
> solve the problem.

Interesting.  It seem the key line is here:

> > cl : Command line warning D4029 : optimization is not available in the
> > standard edition compiler

So the "standard" version doesn't support inline functions.  To fix
that, I think we need some compiler define test to tell us if it is a
"standard" edition compiler.

--
  Bruce Momjian   [EMAIL PROTECTED]
  EnterpriseDBhttp://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Harald Armin Massa
Louis,Views certainly help in managing complexity. They do nothing to improve query-speed. Querying a view gets rewritten to queries to the underlying tables on the fly.(as long as there are no materialized views, which are still on a the TODO list)
-- GHUM Harald Massapersuadere et programmareHarald Armin MassaReinsburgstraße 202b70197 Stuttgart0173/9409607-Let's set so double the killer delete select all.


Re: [GENERAL] WIN32 Build?

2006-08-09 Thread Shoaib Mir
I too faced same kind of a problem building libpq on Windows and as a solution to it removed "inline" from the file 
"\backend\utils\mb\wchar.c"Did the following ... changed "static inline int" to "static int" instead at a couple of places in the wchar.c file
Run "nmake" after doing this specific change and hopefully it will solve the problem.Thanks,-- Shoaib MirEnterpriseDB (
www.enterprisedb.com)
On 8/9/06, DEV <[EMAIL PROTECTED]> wrote:













Hello all. 

  I am
trying to build postgres from the source on a WINXP system. I am using MSVC++.Net
and I use the link with it to get to the command prompt.  After I do that I run
vcvars32.bat and the nmake /f win32.mak and I get:

 

C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak

 

Microsoft (R)
Program Maintenance Utility Version 7.10.3077

Copyright (C)
Microsoft Corporation.  All rights reserved.

 

    cd
include

    if
not exist pg_config.h copy pg_config.h.win32 pg_config.h

    cd ..

    cd
interfaces\libpq

    nmake
/f win32.mak

 

Microsoft (R)
Program Maintenance Utility Version 7.10.3077

Copyright (C)
Microsoft Corporation.  All rights reserved.

 

Building the
Win32 static library...

 

   
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp

cl : Command
line warning D4029 : optimization is not available in the standard

edition
compiler

wchar.c

..\..\backend\utils\mb\wchar.c(100)
: error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(101)
: error C2085: 'pg_euc_mblen' : not in forma

l parameter
list

..\..\backend\utils\mb\wchar.c(101)
: error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(116)
: error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(117)
: error C2085: 'pg_euc_dsplen' : not in form

al parameter
list

..\..\backend\utils\mb\wchar.c(117)
: error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(143)
: warning C4013: 'pg_euc_mblen' undefined; a

ssuming
extern returning int

..\..\backend\utils\mb\wchar.c(180)
: warning C4013: 'pg_euc_dsplen' undefined;

assuming
extern returning int

NMAKE : fatal
error U1077: 'cl.exe' : return code '0x2'

Stop.

NMAKE : fatal
error U1077: '"C:\Program Files\Microsoft Visual Studio .NET 2003\

VC7\BIN\nmake.exe"'
: return code '0x2'

Stop.

 

What do you I
need to change to make this work?

 

Brian Doyle

 

 










Re: [GENERAL] Tuning to speed select

2006-08-09 Thread louis gonzales
What about creating views on areas of the table that are queried often?  
I don't know if you have access or the ability to find what type of 
trends the table has, in terms of queries, but if you create some views 
on frequently visited information, this could also help.


Tom Laudeman wrote:


Hi,

I'm running PostgreSQL version 8 on a dual 2.4GHz Xeon with 1GB of RAM 
and an IDE hard drive. My big table has around 9 million records.


Is there a tuning parameter I can change to increase speed of selects? 
Clearly, there's already some buffering going on since selecting an 
indexed ~50,000 records takes 17 seconds on the first try, and only 
0.5 seconds on the second try (from pgsql).


cowpea=> explain analyze select bs_fk from blast_result where 
si_fk=11843254;
QUERY 
PLAN 
 

Index Scan using si_fk_index on blast_result  (cost=0.00..22874.87 
rows=58118 width=4) (actual time=112.249..17472.935 rows=50283 loops=1)

  Index Cond: (si_fk = 11843254)
Total runtime: 17642.522 ms
(3 rows)

cowpea=>  explain analyze select bs_fk from blast_result where 
si_fk=11843254;
  QUERY 
PLAN   
 

Index Scan using si_fk_index on blast_result  (cost=0.00..22874.87 
rows=58118 width=4) (actual time=0.178..341.643 rows=50283 loops=1)

  Index Cond: (si_fk = 11843254)
Total runtime: 505.011 ms
(3 rows)

cowpea=> 



Thanks,
Tom





---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [GENERAL] PITR Questions

2006-08-09 Thread Scott Ribe
> I don't see how checkpoint_timeout is relevant.  Just because we
> checkpoint doesn't mean the WAL file will get archived.  I have to have
> 16M of WAL traffic before a file gets archived regardless of
> check-pointing, or am I missing something?

Right, I think ;-) If you want finer-grained backup, you have to do
something like rsync the current WAL file frequently.

-- 
Scott Ribe
[EMAIL PROTECTED]
http://www.killerbytes.com/
(303) 722-0567 voice



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [GENERAL] PL/pgSQL Problem

2006-08-09 Thread Merlin Moncure

On 8/9/06, Ron St-Pierre <[EMAIL PROTECTED]> wrote:

Hi, I'm having a problem with one of my functions, where I delete all
rows containing a particular date and then re-insert a row with that
same date. When I try this I get a constraint error. This just started
recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL).

here's the function:
CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS '
DECLARE
compDate DATE;
currCount INT;
BEGIN
compDate := current_date::date;
LOOP
DELETE FROM dm.systemCounts WHERE updateDate::date =
compDate::date;
INSERT INTO dm.systemCounts (updateDate) VALUES
(compDate::date);
.

and here's the error:
ERROR:  duplicate key violates unique constraint "systemcounts_pkey"
CONTEXT:  SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 
::date)"
PL/pgSQL function "updatesystemcounts" line 8 at SQL statement

The only explanation I can think of is that maybe the newer version of
postgres needs a COMMIT inside the function.


commit inside function is impossible (by definition), however you can
do subtransaction inside the function and catch the error.

what is the primary key for dm.systemCounts. does it have a default?

merlin

---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org


[GENERAL] LinuxWorld West

2006-08-09 Thread Joshua D. Drake

Hello,

I will be basically unavailable from this Saturday until the 21st of 
August. I will be spending a long week in SF at LinuxWorld West.


Please use email to contact me if it is important.

Sincerely,

Joshua D. Drake

--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [GENERAL] PL/pgSQL Problem

2006-08-09 Thread Michael Fuhr
On Wed, Aug 09, 2006 at 10:33:02AM -0700, Ron St-Pierre wrote:
> ERROR:  duplicate key violates unique constraint "systemcounts_pkey"
> CONTEXT:  SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( 
> $1 ::date)"
> PL/pgSQL function "updatesystemcounts" line 8 at SQL statement

Is the table's primary key a serial column?  If so then the sequence
might be out of sync with the values in the table, in which case
you'll need to adjust the sequence's value with ALTER SEQUENCE or
setval().

-- 
Michael Fuhr

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


[GENERAL] PL/pgSQL Problem

2006-08-09 Thread Ron St-Pierre
Hi, I'm having a problem with one of my functions, where I delete all 
rows containing a particular date and then re-insert a row with that 
same date. When I try this I get a constraint error. This just started 
recently, after upgrading our database from 7.4 to 8.1.4 (now on RH EL).


here's the function:
CREATE OR REPLACE FUNCTION updatesystemCounts() RETURNS void AS '
   DECLARE
   compDate DATE;
   currCount INT;
   BEGIN
   compDate := current_date::date;
   LOOP
   DELETE FROM dm.systemCounts WHERE updateDate::date = 
compDate::date;
   INSERT INTO dm.systemCounts (updateDate) VALUES 
(compDate::date);

   .

and here's the error:
ERROR:  duplicate key violates unique constraint "systemcounts_pkey"
CONTEXT:  SQL statement "INSERT INTO dm.systemCounts (updateDate) VALUES ( $1 
::date)"
PL/pgSQL function "updatesystemcounts" line 8 at SQL statement

The only explanation I can think of is that maybe the newer version of 
postgres needs a COMMIT inside the function.


Any ideas?

Thanks

Ron St.Pierre

---(end of broadcast)---
TIP 6: explain analyze is your friend


Re: [GENERAL] psql/readline clears screen

2006-08-09 Thread Merlin Moncure

On 8/9/06, Reece Hart <[EMAIL PROTECTED]> wrote:

 On Wed, 2006-08-09 at 09:02 -0400, Merlin Moncure wrote:

 is there a way to configure psql/readline so that it doesn't clear the
 screen after browsing a query with 'q'?
 I assume you're using the less text pager on a Unix box. I think what you're 
seeing is less clearing the screen after displaying a query (i.e., it's not 
psql or readline).

 I like the following invocation: less -iMSx4 -FX (see less manpage for 
explanation).  This is particularly useful with ' \pset pager always' (in 
.psqlrc). The upshot is that less will decide whether the contents fit on one 
screen both horizontally and vertically; if so it'll display and quit without 
any interaction, and if not it will permit scrolling in one or both directions.



actually, i do my editing about 60% windows, 40% linux until i finish
migrating my development platform to os x. on windows, i tried out
your less options using the cygwin compiled psql and like them very
much.

merlin

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


[GENERAL] WIN32 Build?

2006-08-09 Thread DEV








Hello all. 

  I am
trying to build postgres from the source on a WINXP system. I am using MSVC++.Net
and I use the link with it to get to the command prompt.  After I do that I run
vcvars32.bat and the nmake /f win32.mak and I get:

 

C:\Dev\postgresql-8.1.4\src>nmake
/f win32.mak

 

Microsoft (R)
Program Maintenance Utility Version 7.10.3077

Copyright (C)
Microsoft Corporation.  All rights reserved.

 

    cd
include

    if
not exist pg_config.h copy pg_config.h.win32 pg_config.h

    cd ..

    cd
interfaces\libpq

    nmake
/f win32.mak

 

Microsoft (R)
Program Maintenance Utility Version 7.10.3077

Copyright (C)
Microsoft Corporation.  All rights reserved.

 

Building the
Win32 static library...

 

   
cl.exe @C:\DOCUME~1\doyleb\LOCALS~1\Temp\nm313.tmp

cl : Command
line warning D4029 : optimization is not available in the standard

edition
compiler

wchar.c

..\..\backend\utils\mb\wchar.c(100)
: error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(101)
: error C2085: 'pg_euc_mblen' : not in forma

l parameter
list

..\..\backend\utils\mb\wchar.c(101)
: error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(116)
: error C2054: expected '(' to follow 'inlin

e'

..\..\backend\utils\mb\wchar.c(117)
: error C2085: 'pg_euc_dsplen' : not in form

al parameter
list

..\..\backend\utils\mb\wchar.c(117)
: error C2143: syntax error : missing ';' be

fore '{'

..\..\backend\utils\mb\wchar.c(143)
: warning C4013: 'pg_euc_mblen' undefined; a

ssuming
extern returning int

..\..\backend\utils\mb\wchar.c(180)
: warning C4013: 'pg_euc_dsplen' undefined;

assuming
extern returning int

NMAKE : fatal
error U1077: 'cl.exe' : return code '0x2'

Stop.

NMAKE : fatal
error U1077: '"C:\Program Files\Microsoft Visual Studio .NET 2003\

VC7\BIN\nmake.exe"'
: return code '0x2'

Stop.

 

What do you I
need to change to make this work?

 

Brian Doyle

 

 








Re: [GENERAL] psql/readline clears screen

2006-08-09 Thread Reece Hart




On Wed, 2006-08-09 at 09:02 -0400, Merlin Moncure wrote:

is there a way to configure psql/readline so that it doesn't clear the
screen after browsing a query with 'q'? 


I assume you're using the less text pager on a Unix box. I think what you're seeing is less clearing the screen after displaying a query (i.e., it's not psql or readline).

I like the following invocation: less -iMSx4 -FX (see less manpage for explanation).  This is particularly useful with ' \pset pager always' (in .psqlrc). The upshot is that less will decide whether the contents fit on one screen both horizontally and vertically; if so it'll display and quit without any interaction, and if not it will permit scrolling in one or both directions.

There are two ways you might enable this: 
- set and export the LESS environment variable in your shell setup, like this in .bashrc:
export LESS='-iMSx4 -FX'
-  prefix your psql invocation with the LESS environment variable setting, like this:
$ LESS='iMSx4 -FX'   psql 


-Reece





-- 
Reece Hart, http://harts.net/reece/, GPG:0x25EC91A0








Re: [GENERAL] read only transaction, temporary tables

2006-08-09 Thread Tom Lane
Richard Huxton  writes:
> Carl R. Brune wrote:
>> I should have added that I want to make further use of the temporary
>> table after the COMMIT -- the rollback approach you propose makes it
>> go away.

> In which case the transaction isn't READONLY.

It does seem a bit inconsistent that we allow you to write into a temp
table during a "READONLY" transaction, but not to create/drop one.
I'm not excited about changing it though, as the tests to see if
the command is allowed would become vastly more complex.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend


[GENERAL] Not so much load balancing as load limits

2006-08-09 Thread Kenneth Downs
I'm having some trouble getting a google hit on this topic, and the docs 
aren't doing much for me.


What I'm wondering is, how do I limit any particular postgres operation, 
in particular a long-running batch operation with lots of inserts, from 
bogging down a server?


This is not so much a question of how to code the SQL, as it is how to 
keep a selfish process from causing a self-inflicted DoS situation. 

Can anybody get me pointed in the right direction with a link or two?  
Thanks.
begin:vcard
fn:Kenneth  Downs
n:Downs;Kenneth 
email;internet:[EMAIL PROTECTED]
tel;work:631-689-7200
tel;fax:631-689-0527
tel;cell:631-379-0010
x-mozilla-html:FALSE
version:2.1
end:vcard


---(end of broadcast)---
TIP 4: Have you searched our list archives?

   http://archives.postgresql.org


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Michael Fuhr
On Wed, Aug 09, 2006 at 03:46:38PM +0200, Martijn van Oosterhout wrote:
> On Wed, Aug 09, 2006 at 09:19:31AM -0400, Tom Laudeman wrote:
> > Is there a tuning parameter I can change to increase speed of selects? 
> > Clearly, there's already some buffering going on since selecting an 
> > indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 
> > seconds on the second try (from pgsql).
> 
> Your OS is probably buffering, 1GB of RAM holds a lot of data. You can
> try increasing the shared_buffers parameter, but if the delay is
> getting data from the disk, that won't really help you.

If most of your queries use the same index then clustering on that
index might speed up initial (i.e., not-cached) queries by reducing
the number of disk pages that need to be read.  See the documentation
for more information.

http://www.postgresql.org/docs/8.1/interactive/sql-cluster.html

-- 
Michael Fuhr

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [GENERAL] Tuning to speed select

2006-08-09 Thread Martijn van Oosterhout
On Wed, Aug 09, 2006 at 09:19:31AM -0400, Tom Laudeman wrote:
> Hi,
> 
> I'm running PostgreSQL version 8 on a dual 2.4GHz Xeon with 1GB of RAM 
> and an IDE hard drive. My big table has around 9 million records.
> 
> Is there a tuning parameter I can change to increase speed of selects? 
> Clearly, there's already some buffering going on since selecting an 
> indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 
> seconds on the second try (from pgsql).

Your OS is probably buffering, 1GB of RAM holds a lot of data. You can
try increasing the shared_buffers parameter, but if the delay is
getting data from the disk, that won't really help you.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature


Re: [GENERAL] Data warehouse & OLAP

2006-08-09 Thread Tomi NA

On 8/9/06, Stefano B. <[EMAIL PROTECTED]> wrote:



hi,
I'm working in the implementation of a datawarehouse on Postgres.

For analisys of aggregated data I'd like to use some OLAP tools like for
example, Mondrian, OR use the meterialized view (if better).

My questions:
is there any documentation about warehouse (with OLAP or materialized view)
on Postgres?
Which is better? OLAP tools (I think) or materialized view?

Any information is welcome!
Thanks


You'll obviously have to identify all possible changes to your
normalized data that affect the data warehouse consistency.
As far as I know, pgsql doesn't directly support materialized views,
so you'll write one or more update_warehouse functions and do one of
the following:
1.) set up a collection of triggers to keep track of all changes since
the last synchronization. Set up a periodic task (probably using
pgAgent) which will invoke the update_warehouse functions which will
than update the needed records.
2.) set up a collection of triggers directly calling your update_functions

The first option is what probably 99% users need because of the
implicit nature of the queries run against warehouse data. An hour,
day or even week of the latest data very often makes no difference
when analyzing an OLAP cube, but the exact tolerance level obviously
depends on the exact queries analysts really run, the resources
available/needed to refresh the warehouse, the nature of the data etc.
The second option is nice in that it keeps the relational data in sync
with the warehouse, but this can only be implemented in specific
systems where the update load is tolerable, changes limited in scope
and update triggers highly focused on the scope of the change. I can
imagine circumstances when you'd need such a setup, but most of the
time it's just a theoretical possibility.

I have implemented the second approach using the very good Mondrian
OLAP server and stunning JRubik analysis interface in one of my
systems (small database, several dozen MB, less than a milion records,
total) at the price of a ~2 sec delay when updating a row - an action
that happens about 50-60 times per day. The reason was very
non-technical: we needed to be able to say "you can analyze data in
real time".

Hope this helps.
Cheers,
t.n.a.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


[GENERAL] Tuning to speed select

2006-08-09 Thread Tom Laudeman

Hi,

I'm running PostgreSQL version 8 on a dual 2.4GHz Xeon with 1GB of RAM 
and an IDE hard drive. My big table has around 9 million records.


Is there a tuning parameter I can change to increase speed of selects? 
Clearly, there's already some buffering going on since selecting an 
indexed ~50,000 records takes 17 seconds on the first try, and only 0.5 
seconds on the second try (from pgsql).


cowpea=> explain analyze select bs_fk from blast_result where 
si_fk=11843254;
QUERY 
PLAN 

Index Scan using si_fk_index on blast_result  (cost=0.00..22874.87 
rows=58118 width=4) (actual time=112.249..17472.935 rows=50283 loops=1)

  Index Cond: (si_fk = 11843254)
Total runtime: 17642.522 ms
(3 rows)

cowpea=>  explain analyze select bs_fk from blast_result where 
si_fk=11843254;
  QUERY 
PLAN   

Index Scan using si_fk_index on blast_result  (cost=0.00..22874.87 
rows=58118 width=4) (actual time=0.178..341.643 rows=50283 loops=1)

  Index Cond: (si_fk = 11843254)
Total runtime: 505.011 ms
(3 rows)

cowpea=>  




Thanks,
Tom


--
Tom Laudeman
[EMAIL PROTECTED]
(434) 924-2456
http://www.people.virginia.edu/~twl8n/
http://laudeman.com/


---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq


Re: [GENERAL] psql/readline clears screen

2006-08-09 Thread AgentM


On Aug 9, 2006, at 9:02 , Merlin Moncure wrote:


is there a way to configure psql/readline so that it doesn't clear the
screen after browsing a query with 'q'?


Assuming you are using less as your PAGER, then see:
http://www.greenwoodsoftware.com/less/#tite

One can add the -X option to your LESS or PAGER environment variables.

-M


---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [GENERAL] psql/readline clears screen

2006-08-09 Thread Martijn van Oosterhout
On Wed, Aug 09, 2006 at 09:02:55AM -0400, Merlin Moncure wrote:
> is there a way to configure psql/readline so that it doesn't clear the
> screen after browsing a query with 'q'?

You don't say the OS, but you may be seeing the effects of less working
on a terminal with a seperate interactive screen (for example xterm).
What's happening is that when less exits, it switches back to the main
screen, thus hiding what you were just looking at.

From reading the manpage, setting the pager to "less -X" should solve
this problem.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature


[GENERAL] psql/readline clears screen

2006-08-09 Thread Merlin Moncure

is there a way to configure psql/readline so that it doesn't clear the
screen after browsing a query with 'q'?

regards,
merlin

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings


Re: [GENERAL] DB connectivity from a client machine

2006-08-09 Thread Joshua D. Drake

Jasbinder Bali wrote:

In my database server, I'm using packaged version of postgres.
I installed it using: "yum install postgres" and this inturn searches for
the rmp.
So, its a packaged version of postgresql.

Now as far as my client is concerned, that has the middle tier component
written in C, i don't have any postgres there.
Do you mean to say that I need to install the client version of postgres in
the m/c hosting the middle tier component and trying to connect to the
postgres server?


No but you may need to install the libs.

Joshua D. Drake


--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] sizing of a server

2006-08-09 Thread Joshua D. Drake


The best way of finding out is to do some testing - use a standard 
desktop machine (or laptop) and see how that performs. Make sure you 
have a realistic mix and amount of data though.


If I had to guess, I'd buy a cheap server with some SATA disks (at least 
2 so you can mirror them) and as much RAM as was left in my budget.


Yeah, this sounds like a dualcore amd64 with a areca 4 drive sata raid 
10 array. Put 2/4 gig of ram in it, and you will think it is just flying.


Joshua D. Drake





--

   === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
   Providing the most comprehensive  PostgreSQL solutions since 1997
 http://www.commandprompt.com/



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match


[GENERAL] Data warehouse & OLAP

2006-08-09 Thread Stefano B.



hi,
I'm working in the implementation of a 
datawarehouse on Postgres.
 
For analisys of aggregated data I'd like to use 
some OLAP tools like for example, Mondrian, OR use the meterialized view (if 
better).
 
My questions:
is there any documentation about 
warehouse (with OLAP or materialized view) on Postgres?
Which is better? OLAP tools (I think) or 
materialized view?
 
Any information is welcome!
Thanks
 
Stefano


Re: [GENERAL] DB connectivity from a client machine

2006-08-09 Thread Michael Meskes
On Tue, Aug 08, 2006 at 05:25:12PM -0400, Jasbinder Bali wrote:
> Why I'm asking this is (though generally m/c hosting the middle tier
> component can connect to the m/c hosting the Db without any DB client
> installed) because i tried to run ecpg in the middle tier m/c and it doesn't
> work. Well it would definitely not work coz ecpg comes with postgres.

This is about the third time you claim ecpg doesn't work. Would you
please be so kind to explain what doesn't work? Of course ecpg can be
run on a machine without a PostgreSQL server. And programs compiled with
ecpg also do not need a server on the same machine, why should they?

This is why Debian for instance has a seperate ecpg package.

Michael
-- 
Michael Meskes
Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org)
ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faq


Re: [GENERAL] Need help with quote escaping in exim for postgresql

2006-08-09 Thread Marc Haber
On Fri, Jul 07, 2006 at 03:48:00PM +0200, Marc Haber wrote:
> I am the maintainer of Debian's packages for exim4, a powerful and
> versatile Mail Transfer Agent developed in Cambridge and in wide use
> throughout the Free Software Community (http://www.exim.org/).
> 
> One of our daemon flavours has PostgreSQL support. Our security guys
> have found a flaw in exim regarding quote escaping for PostgreSQL. The
> bug is filed in Debian's BTS as http://bugs.debian.org/369351 and was
> transferred to exim's Bugzilla installation as
> http://www.exim.org/bugzilla/show_bug.cgi?id=107.

I would like to thank anybody who commented in this thread. Special
thanks go to Florian Weimer, who has done the task of communicating
with Philip Hazel, the exim author, and working out a fix which has
since been released as part of exim 4.63.

Greetings
Marc

-- 
-
Marc Haber | "I don't trust Computers. They | Mailadresse im Header
Mannheim, Germany  |  lose things."Winona Ryder | Fon: *49 621 72739834
Nordisch by Nature |  How to make an American Quilt | Fax: *49 621 72739835

---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
   subscribe-nomail command to [EMAIL PROTECTED] so that your
   message can get through to the mailing list cleanly


Re: [GENERAL] sizing of a server

2006-08-09 Thread Richard Huxton

[EMAIL PROTECTED] wrote:

I would like to create a database server with postgres web application.

My DB size will be 20GB.
I will write 1 a day with until 10 to 15 simultanous write.
How could I size my server (memory, CPU, ...) ?


The only reliable way is to do some testing. Now 10,000 rows a day isn't 
many and I doubt it will be 10-15 simultaneous writes (although it might 
be 10-15 simultaneous connections).


The key questions will be how much of that 20GB gets used regularly, and 
what level of performance you'll need.


The best way of finding out is to do some testing - use a standard 
desktop machine (or laptop) and see how that performs. Make sure you 
have a realistic mix and amount of data though.


If I had to guess, I'd buy a cheap server with some SATA disks (at least 
2 so you can mirror them) and as much RAM as was left in my budget.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster


Re: [GENERAL] read only transaction, temporary tables

2006-08-09 Thread Richard Huxton

Carl R. Brune wrote:

I should have added that I want to make further use of the temporary
table after the COMMIT -- the rollback approach you propose makes it
go away.


In which case the transaction isn't READONLY. You have two options:

CREATE TEMPORARY TABLE ... AS SELECT ...
BEGIN READONLY;
...
COMMIT;

Or, create a user with only-read permissions on your database and 
connect as that user.


--
  Richard Huxton
  Archonet Ltd

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings