Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

[Martijn van Oosterhout]

On Tue, Oct 23, 2007 at 09:56:26AM +0400, Sergey Konoplev wrote:
> I took a look at TCP state with netstat:
> 
> pgdb:/base/PG-Data # netstat -pna |grep 8590
> tcp1  0 127.0.0.1:5432  127.0.0.1:35442
> CLOSE_WAIT  8590/postgres: kono

CLOSE_WAIT means that the client (in this case SSH) has shutdown() its
end of the connection and evidently postgresql hasn't noticed. However,
SSH has not closed its socket entirely, because then the write would
fail.

Can you strace the SSH daemon, my bet is that it's also stuck on a
write(), to the original client. It would also be interesting to know
what the original client is doing, since it's obviously still alive.
Looks like somewhere along the chain a program called shutdown() but is
no longer reading incoming data...

Hope this helps,
-- 
Martijn van Oosterhout   <[EMAIL PROTECTED]>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[GENERAL] alter database tablespace

[Rafael Martinez]

Hello

I am wondering if 8.4 is going to implement this:

ALTER DATABASE name SET TABLESPACE new_tablespace

I cannot find information in the todo list about the priority  or
release that would implement this feature.

regards
-- 
 Rafael Martinez, <[EMAIL PROTECTED]>
 Center for Information Technology Services
 University of Oslo, Norway

 PGP Public Key: http://folk.uio.no/rafael/

---(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

[GENERAL] How to transfer from place to plase without backup/restore

[Hristo Filipov]

Is it possible to transfer database from one place to another without making
backup from the source and restore at the target? 

I mean I want to copy the files as they are at the source to the target?
What else I must do, so I can attach them to the target PostgreSQL database?

Also I'm creating stored procedure and it is returning me recordset, but I
can't named it, it gets random names - Unnamed Recordset . Is there
a way to name give a specific name to this recordset? 

 

Hristo Filipov

[GENERAL] How to transfer from place to plase without backup/restore

[Hristo Filipov]

Is it possible to transfer database from one place to another without making
backup from the source and restore at the target? 

I mean I want to copy the files as they are at the source to the target?
What else I must do, so I can attach them to the target PostgreSQL database?

Also I'm creating stored procedure and it is returning me recordset, but I
can't named it, it gets random names - Unnamed Recordset . Is there
a way to name give a specific name to this recordset? 

 

Hristo Filipov

Re: [GENERAL] How to transfer from place to plase without backup/restore

[Shane Ambler]

Hristo Filipov wrote:

Is it possible to transfer database from one place to another without making
backup from the source and restore at the target? 


I mean I want to copy the files as they are at the source to the target?
What else I must do, so I can attach them to the target PostgreSQL database?


That is basically what you are doing when you transfer (unless you are 
moving the drive to a new machine) - how you do the transfer will depend 
on your needs and what hardware and software is at the source and 
destination.


If you are looking for a backup server to be ready to use as soon as 
your production server fails then you can look at PITR or 
synchronization such as slony-1


If you want to copy your database to a development machine occasionally, 
then if they have the same hardware and postgres version then you should 
be able to copy the data files across while the production server is 
stopped without problems. Some differences between CPU and postgres 
version can stop this from working.


You could also do the dump/restore directly from one machine to another 
if they are on the same network without having the dump file to transfer 
across and import which will give similar results.


eg. instead of -
pg_dumpall > mybackupfile
psql -h my.other.machine.address < mybackupfile

you can -

pg_dumpall | psql -h my.other.machine.address

If you have different cpu's (one Intel and one AMD64) or different 
postgres versions then you will have to do the dump/restore even if it 
is sent straight from one to another.


For more help specify what hardware, system and postgres version you 
have on each machine and what you wish to achieve in transferring.




--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

---(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] unicode searches failing that use % and LIKE operators

[Benjamin Weaver]

Thanks, Tom and Greg, for all your help.  I agree that Redhat 9 is a bit 
creaky and that we here should upgrade.  In the meantime I will put together a 
tiny test case that will reproduce the problem (at least on my platform).

Ben

In message <[EMAIL PROTECTED]> Tom Lane <[EMAIL PROTECTED]> 
writes:
> Benjamin Weaver <[EMAIL PROTECTED]> writes:
> > I AM in fact running the db on Linux.  Redhat 9.  Are the encoding 
parameters
> > wrong for Linux?
> 
> Hmm ... RH 9 is awfully old.  It's at least conceivable that you're
> getting bit by some glibc bug.  However, if these are just plain LIKE
> calls and not ILIKE then I don't think that theory is very viable
> --- AFAICS the regular LIKE code doesn't depend on anything from the
> platform.
> 
> A slightly more viable theory is that you're getting bit by a gcc bug.
> Did you build the Postgres executables in-house, and if not where did
> you get them from?  It might be helpful if you'd show us the output
> of "pg_config".
> 
> Again, if you could put together a self-contained test case (preferably
> a short psql script) it would be helpful so we could try the case on
> other machines.  It's not at all clear at this point whether the bug
> is Postgres' fault or something about the underlying platform.
> 
>   regards, tom lane

-- 
Benjamin Weaver
Faculty Research Associate, Imaging Papyri Projects, Herculaneum Society, 
Oxford
email:  [EMAIL PROTECTED]
phone:  (0)1865 610236


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

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

Re: [GENERAL] pg_cancel_backend() does not work with buzz queries

[Sergey Konoplev]

2007/10/23, Martijn van Oosterhout <[EMAIL PROTECTED]>:
> On Tue, Oct 23, 2007 at 09:56:26AM +0400, Sergey Konoplev wrote:
> > I took a look at TCP state with netstat:
> >
> > pgdb:/base/PG-Data # netstat -pna |grep 8590
> > tcp1  0 127.0.0.1:5432  127.0.0.1:35442
> > CLOSE_WAIT  8590/postgres: kono
>
> CLOSE_WAIT means that the client (in this case SSH) has shutdown() its
> end of the connection and evidently postgresql hasn't noticed. However,
> SSH has not closed its socket entirely, because then the write would
> fail.
>
> Can you strace the SSH daemon, my bet is that it's also stuck on a
> write(), to the original client. It would also be interesting to know
> what the original client is doing, since it's obviously still alive.
> Looks like somewhere along the chain a program called shutdown() but is
> no longer reading incoming data...
>
> Hope this helps,

I've done strace and noticed that this SHH daemon is repeating next output:

pid 10511 stopped, [SIGTRAP]
10.485688 [e410] rt_sigprocmask(SIG_BLOCK, [CHLD],
[wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
[], 8) = 0
 [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
 0.000578 [e410] rt_sigprocmask(SIG_SETMASK, [],  [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
NULL, 8) = 0
 [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
 0.000468 [e410] read(4,  [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
"\x0e\xd7\x62\xdb\xc8\x97\xbb\xbc\x52\xe6\xe1\xab\x6a\xcc"..., 16384) = 32
 [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
 0.000533 [e410] select(25, [4 5 19 21 22], [22], NULL, NULL
[wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
) = 1 (out [22])
 [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
 0.000705 [e410] rt_sigprocmask(SIG_BLOCK, [CHLD],
[wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
[], 8) = 0
 [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
 0.000481 [e410] rt_sigprocmask(SIG_SETMASK, [],  [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
NULL, 8) = 0
 [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
 0.000447 [e410] write(22,
"\x51\x00\x00\x00\x11\x53\x65\x6c\x65\x63\x74\x20\x27\x4f"..., 18
[wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
) = 18
 [wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
 0.000876 [e410] select(25, [4 5 19 21 22], [], NULL, NULL
[wait(0x57f) = 10511]
pid 10511 stopped, [SIGTRAP]
) = 1 (in [22])
 [wait(0x57f) = 10511]

Original client was interrupted by me before the backend has hung and
I've found neither its process (in task manager) nor corresponding
netstat output line.

By the way, I've also noticed that the SSH daemon's (which is in
FIN_WAIT2) timer is "off" and took a look at tcp_fin_timeout...

pgdb:~ # netstat -pnao |grep 37465
tcp1 131072 127.0.0.1:5432  127.0.0.1:37465
CLOSE_WAIT  24855/postgres: kon unkn-4 (41.30/0/0)
tcp73728  0 127.0.0.1:37465 127.0.0.1:5432
FIN_WAIT2   10511/sshd: dcsshcl off (0.00/0/0)
pgdb:~ # cat /proc/sys/net/ipv4/tcp_fin_timeout
60

...which is 60. I wonder are there another variables which affect on
FIN_WAIT2 timeout?

-- 
Regards,
Sergey Konoplev

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

   http://archives.postgresql.org/

[GENERAL] Reliability of WAL replication

[Marc Schablewski]

We had some corrupted data files in the past (missing clog, see
http://archives.postgresql.org/pgsql-bugs/2007-07/msg00124.php) and are
thinking about setting up a warm standby system using WAL replication.

Would an error like the one we had appear in WAL and would it be
replicated too? Or is there some kind of consistency check, that
prevents broken WAL from being restored?

I've already posted this question to the bugs list two weeks ago, but
didn't receive an answer so far. Maybe it was the wrong list for that
kind of question, so we'll give it another try here.

Our customer demands a final statement from us, so we would appreciate a
soon reply ( I know, it's always urgent, isn't it? ;) ).

Regards,

Marc Schablewski
click:ware Informationstechnik GmbH


---(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] Bitmap Heap scan 8.1/8.2

[Martin Marques]

Martin Marques escribió:

Pavel Stehule wrote:


try

set work_mem to '8MB';
and
explain analyze select ..


These things didn't help. What changed the plan completely was this:

seq_page_cost = 5.0 # measured on an arbitrary scale
cpu_tuple_cost = 0.05   # same scale as above


Can someone explain how this parameters are measured? What is 5.0 in 
this case for seq_page_cost?


---(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] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Rainer Bauer]

"Trevor Talbot" wrote:

>I wrote:
>
>[ desktop heap usage ]
>
>> It could be that there's a significant difference between XP and 2003
>> in how that's handled though.  I do have an XP SP2 machine here with
>> 512MB RAM, and I'll try tests on it as soon as I can free up what it's
>> currently occupied with.
>
>...yep, under XP I'm using about 3.1KB of the service heap per
>connection, which tears through it quite a bit faster.  Now to figure
>out exactly where it's coming from...

I can confirm this here (WinXP SP2).

I have restored the original postgresql.conf file that was created when the
cluster was initialized with Postgres 8.2.4-1 (the installed version now is
8.2.5-1). The only other change to this installation is that I have moved the
WAL directory pg_xlog to another drive using a junction link.

Here are my numbers from SysInternals System Information program:
Pages Limit:364544KB  [356MB]
Nonpaged Limit: 262144KB  [256MB]
These limits are never reached.

Using the Desktop Heap Monitor every new connection consumes 3232 bytes of the
total 512KB heap.

>It could be that there's a significant difference between XP and 2003
>in how that's handled though.  I do have an XP SP2 machine here with
>512MB RAM, and I'll try tests on it as soon as I can free up what it's
>currently occupied with.

Yeah, Win2003 behaves differently accoriding to this source:



Session paged pool allows session specific paged pool allocations.  Windows XP
uses regular paged pool, since the number of remote desktop connections is
limited.  On the other hand, Windows Server 2003 makes allocations from
session paged pool instead of regular paged pool if Terminal Services
(application server mode) is installed.


After increasing the session heap size in the registry from 512KB to 1024KB
the no. of connections was roughly doubled. So this might be a solution for
people running out of Desktop heap.

Alter the value of the following key


The numeric values following "SharedSection=" control the heap management:
On WinXP these are the default values: "SharedSection=1024,3072,512"
Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
non-interactive window stations to 1024KB.

Rainer

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

Re: [GENERAL] Reliability of WAL replication

[Csaba Nagy]

Marc,

On Tue, 2007-10-23 at 13:58 +0200, Marc Schablewski wrote:
> We had some corrupted data files in the past (missing clog, see
> http://archives.postgresql.org/pgsql-bugs/2007-07/msg00124.php) and are
> thinking about setting up a warm standby system using WAL replication.
> 
> Would an error like the one we had appear in WAL and would it be
> replicated too? Or is there some kind of consistency check, that
> prevents broken WAL from being restored?

Here we had WAL based replication in place some time ago, and the result
are somewhat mixed: in one case the corruption was replicated, other
times it was not... I guess it has to do with where the corruption
occurred, and I have a feeling the first case (corruption replicated)
was some postgres corner case reacting badly on kill -9 and alike, the
second case (corruption not replicated) was file system corruption. I
didn't run WAL based replication for a while, so I don't know what have
changed in it lately...

Cheers,
Csaba.



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

Re: [GENERAL] SQL spec/implementation question: UPDATE

[Nis Jørgensen]

Albe Laurenz skrev:
> Tom Lane wrote:
>>> I don't have handy a spec guide.  Does this mean that MySQL
>>> is indeed showing incorrect behavior?
>> I think this is really outside the spec.
> [...]
>> There is not anything I can see addressing whether an 
>> "update" should or should not be considered to occur if a
>> target column happens to not change as a result of a
>> commanded update.
> 
> Moreover, I can think of one argument why an UPDATE that
> does not change the value of the row should still occur:
> There may be an ON UPDATE trigger on the table that you
> expect to fire whether or not the UPDATE was "optimized
> away".

This case seems indeed to be "broken" in MySQL.

Nis


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

Re: [GENERAL] Bitmap Heap scan 8.1/8.2

[Pavel Stehule]

2007/10/23, Martin Marques <[EMAIL PROTECTED]>:
> Martin Marques escribió:
> > Pavel Stehule wrote:
> >>
> >> try
> >>
> >> set work_mem to '8MB';
> >> and
> >> explain analyze select ..
> >
> > These things didn't help. What changed the plan completely was this:
> >
> > seq_page_cost = 5.0 # measured on an arbitrary scale
> > cpu_tuple_cost = 0.05   # same scale as above
>
> Can someone explain how this parameters are measured? What is 5.0 in
> this case for seq_page_cost?
>

http://www.postgresql.org/docs/8.2/interactive/runtime-config-query.html

5.0 means so seq scan will be expensive for optimaliser, and
optimaliser will prefer index scan.

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

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

Re: [GENERAL] Reliability of WAL replication

[Simon Riggs]

On Tue, 2007-10-23 at 13:58 +0200, Marc Schablewski wrote:
> We had some corrupted data files in the past (missing clog, see
> http://archives.postgresql.org/pgsql-bugs/2007-07/msg00124.php) and are
> thinking about setting up a warm standby system using WAL replication.
> 
> Would an error like the one we had appear in WAL and would it be
> replicated too? Or is there some kind of consistency check, that
> prevents broken WAL from being restored?

Each WAL record is CRC checked, so it is quite unlikely that it could be
corrupt on its own. 

The contents of the WAL record may cause the system to do something
wrong on the second server, but if this occurs it usually causes some
form of error and we can see that this has happened, report the bug and
then restart replication. If that kind of error occurs it is because of
a problem in the PostgreSQL software, not a fault of the replication
technique. That means these incidents are very rare and we have quickly
fixed such bugs when they do occur. I think this has happened twice in
12-18 months.

-- 
  Simon Riggs
  2ndQuadrant  http://www.2ndQuadrant.com


---(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] unicode searches failing that use % and LIKE operators

[Benjamin Weaver]

Tom, Greg,

Please accept my considerable apologies.  The fault was my own program that 
loaded metadata into the text column, metadatafulltext.  My reason (not a 
sufficient excuse!) for overlooking the problem is that the Unicode is 
invisible in psql windows, therefore it was not a simple matter of displaying 
each row of metadatafulltext to examine its contents.  Some rows got loaded 
with Latin metadata, others with Greek, whereas each and every row needed both.

You both have been very helpful.  In the future I will be careful as I can to 
ensure any problems don't exist with my down read-write-create-selete software!

Thanks again for your help,

Ben 

In message <[EMAIL PROTECTED]> Tom Lane <[EMAIL PROTECTED]> 
writes:
> Benjamin Weaver <[EMAIL PROTECTED]> writes:
> > I AM in fact running the db on Linux.  Redhat 9.  Are the encoding 
parameters
> > wrong for Linux?
> 
> Hmm ... RH 9 is awfully old.  It's at least conceivable that you're
> getting bit by some glibc bug.  However, if these are just plain LIKE
> calls and not ILIKE then I don't think that theory is very viable
> --- AFAICS the regular LIKE code doesn't depend on anything from the
> platform.
> 
> A slightly more viable theory is that you're getting bit by a gcc bug.
> Did you build the Postgres executables in-house, and if not where did
> you get them from?  It might be helpful if you'd show us the output
> of "pg_config".
> 
> Again, if you could put together a self-contained test case (preferably
> a short psql script) it would be helpful so we could try the case on
> other machines.  It's not at all clear at this point whether the bug
> is Postgres' fault or something about the underlying platform.
> 
>   regards, tom lane

-- 
Benjamin Weaver
Faculty Research Associate, Imaging Papyri Projects, Herculaneum Society, 
Oxford
email:  [EMAIL PROTECTED]
phone:  (0)1865 610236


---(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

[GENERAL] Determine query run-time from pg_* tables

[Ow Mun Heng]

Hi,

Is there a method to obtain the query's runtime from any of the pg_*
tables?

Right now, I'm only seeing the backend_start_time (from
pg_stat_activity) (and I'm also not sure how to interpret this.


---(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] Bitmap Heap scan 8.1/8.2

[Alvaro Herrera]

Martin Marques escribió:
> Martin Marques escribió:
>> Pavel Stehule wrote:
>>>
>>> try
>>>
>>> set work_mem to '8MB';
>>> and
>>> explain analyze select ..
>> These things didn't help. What changed the plan completely was this:
>> seq_page_cost = 5.0 # measured on an arbitrary scale
>> cpu_tuple_cost = 0.05   # same scale as above
>
> Can someone explain how this parameters are measured? What is 5.0 in this 
> case for seq_page_cost?

It's an arbitrary number, based on which all the other numbers are
measured.

What people generally do around here is mess with random_page_cost, and
leave seq_page_cost alone.  Often, it's the ratio
seq_page_cost/random_page_cost what's most important to the cost
equations results.  (seq_page_cost wasn't tunable at all until
recently, say 8.1 or 8.2 AFAIR).

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

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

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

Re: [GENERAL] Determine query run-time from pg_* tables

[Alvaro Herrera]

Ow Mun Heng wrote:
> Hi,
> 
> Is there a method to obtain the query's runtime from any of the pg_*
> tables?

No.  You can use log_durations and log_statement for that.  We don't
store that info in tables.

> Right now, I'm only seeing the backend_start_time (from
> pg_stat_activity) (and I'm also not sure how to interpret this.

This is the time that the session was started.  Nothing to do with the
query itself.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] autovacuum and locks

[Alvaro Herrera]

Dietmar Maurer wrote:

> The pg_locks table shows the following:
> 
> > SELECT pg_class.relname AS table, transaction, pid, mode, granted FROM
> pg_locks, pg_class, pg_database WHERE pg_locks.relation = pg_class.oid
> AND pg_locks.database = pg_database.oid;
> 
>  pg_class3389057 | 26130 | AccessShareLock
> | t
>  pg_class_oid_index  3389057 | 26130 | AccessShareLock
> | t
>  pg_locks3389057 | 26130 | AccessShareLock
> | t
> 
> 
>  cgreylist   3368984 | 10979 |
> ShareUpdateExclusiveLock | t  
>  cgreylist_pkey  3368984 | 10979 |
> ShareUpdateExclusiveLock | t
>  cgreylist_extime_index  3368984 | 10979 |
> ShareUpdateExclusiveLock | t
>  cgreylist_instance_sender_index 3368984 | 10979 |
> ShareUpdateExclusiveLock | t
>  cgreylist_mtime_index   3368984 | 10979 |
> ShareUpdateExclusiveLock | t
> 
>  cgreylist   3368998 | 10980 | ExclusiveLock
> | f  
>  
>  cgreylist   3369000 | 10984 | AccessShareLock
> | t  
>  cgreylist   3369000 | 10984 | RowExclusiveLock
> | f  # WHY?
>  cgreylist_extime_index  3369000 | 10984 | AccessShareLock
> | t
> 
>  cgreylist   3388458 | 10023 | ExclusiveLock
> | f ?
>  cgreylist   3388420 | 10021 | ExclusiveLock
> | f ?
> 
> 
> Why cant postgres get the RowExclusiveLock in transaction 3369000? 

Probably because the ExclusiveLock'ers are waiting in front of
RowExclusiveLock.  Locks are granted in order.

It would help if you didn't mangle the pg_locks output so badly.

-- 
Alvaro Herrera  http://www.amazon.com/gp/registry/5ZYLFMCVHXC
"You're _really_ hosed if the person doing the hiring doesn't understand
relational systems: you end up with a whole raft of programmers, none of
whom has had a Date with the clue stick."  (Andrew Sullivan)

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

Re: [GENERAL] How to speedup intarray aggregate function?

[Alvaro Herrera]

Dmitry Koterov escribió:
> I have written in C all needed contrib functions: intarray.bidx() (binary
> search in sorted list) and intagg.int_agg_append_state (bufferized appending
> of one array to another without linear memory reallocation). The speed now
> is great: in one case with intersection of 10 and 15000 arrays it become
> 30ms instead of 1600 ms (50 times faster).
> 
> Few days later, after complex testing, I'll publish complete patches in
> pgsql-hackers maillist.

Did you already?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [GENERAL] Bitmap Heap scan 8.1/8.2

[Tom Lane]

Alvaro Herrera <[EMAIL PROTECTED]> writes:
> Martin Marques escribió:
>>> seq_page_cost = 5.0 # measured on an arbitrary scale

> What people generally do around here is mess with random_page_cost, and
> leave seq_page_cost alone.

It's also worth pointing out that having seq_page_cost >
random_page_cost is simply not sane, and is very likely to result in
performance problems in other queries.

regards, tom lane

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

Re: [GENERAL] Determine query run-time from pg_* tables

[Erik Jones]

On Oct 23, 2007, at 8:43 AM, Alvaro Herrera wrote:


Ow Mun Heng wrote:

Hi,

Is there a method to obtain the query's runtime from any of the pg_*
tables?


No.  You can use log_durations and log_statement for that.  We don't
store that info in tables.


Right now, I'm only seeing the backend_start_time (from
pg_stat_activity) (and I'm also not sure how to interpret this.


This is the time that the session was started.  Nothing to do with the
query itself.


query_start does, however, give you the time that the query started.   
I use something like


SELECT procpid, client_addr, to_char(now() - query_start, 'DD  
HH24:MI:SS') as query_time, current_query

FROM pg_stat_activity
ORDER BY query_time DESC;

To get a snapshot view of what's currently running in our databases.

Erik Jones

Software Developer | Emma®
[EMAIL PROTECTED]
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com



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

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

Re: [GENERAL] autovacuum and locks

[Alvaro Herrera]

Dietmar Maurer wrote:
> > > 
> > > Why cant postgres get the RowExclusiveLock in transaction 3369000? 
> > 
> > Probably because the ExclusiveLock'ers are waiting in front 
> > of RowExclusiveLock.  Locks are granted in order.
> > 
> > It would help if you didn't mangle the pg_locks output so badly.
> 
> Yes, sorry about that.
> 
> I was able to reproduce the problem, and the problem is that locks are
> granted in order (wonder why?).

Because doing otherwise would cause starvation for some lockers.

> Anyways, i am trying to avoid locks now, by using my own merge
> function to avoid update/insert race condition.
> 
> Or what is the suggested way to avoid the update/insert race condition?.

What update/insert race condition?  Maybe you are talking about the
subject of example 37-1 here:

http://www.postgresql.org/docs/current/static/plpgsql-control-structures.html#PLPGSQL-ERROR-TRAPPING

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(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] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Dave Page]

Rainer Bauer wrote:
>> ...yep, under XP I'm using about 3.1KB of the service heap per
>> connection, which tears through it quite a bit faster.  Now to figure
>> out exactly where it's coming from...
> 
> I can confirm this here (WinXP SP2).

It's coming from direct dependencies on user32.dll (from which we use
wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
and is allocated when ResumeThread() is called to kickstart the new
backend, but before the backend actually does anything (proven with a
while(1) loop in main() for the -forkbackend case with a breakpoint on
ResumeThread() in the postmaster).

I've submitted a patch against 8.3 that removes these dependencies
altogether. Unfortuntely, it seems we still have indirect dependencies
on user32.dll which I don't believe we can do anything about. In
testing, the patch reduces the per-connection desktop heap usage from
arount 9.7KB to 3.2KB which is back in line with 8.2.

Regards, Dave

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

Re: [GENERAL] autovacuum and locks

[Alvaro Herrera]

Dietmar Maurer wrote:
> > > Anyways, i am trying to avoid locks now, by using my own merge 
> > > function to avoid update/insert race condition.
> > > 
> > > Or what is the suggested way to avoid the update/insert 
> > race condition?.
> > 
> > What update/insert race condition?  Maybe you are talking 
> > about the subject of example 37-1 here:
> 
> Yes, i talk about Example 37-1

So are you having problems with it?

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Harald Armin Massa]

Dave,

> It's coming from direct dependencies on user32.dll (from which we use
> wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
> and is allocated when ResumeThread() is called to kickstart the new
> backend,

why does every backend need its own heap for user32.dll or
shell32.dll? Wasn't the point of shared dlls to be shared?

Harald

-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

---(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] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Gregory Stark]

"Harald Armin Massa" <[EMAIL PROTECTED]> writes:

> Dave,
>
>> It's coming from direct dependencies on user32.dll (from which we use
>> wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
>> and is allocated when ResumeThread() is called to kickstart the new
>> backend,
>
> why does every backend need its own heap for user32.dll or
> shell32.dll? Wasn't the point of shared dlls to be shared?

The Desktop Heap appears to be a place for processes belonging to the same
"desktop" to allocate shared objects such as GUI elements. These are allocated
in shared space so they can be manipulated by any process running in that
"desktop". 

Why Shell32 and User32 are allocating space in there just to initialize
themselves or handle these basic utility functions is a bit of a mystery.

-- 
  Gregory Stark
  EnterpriseDB  http://www.enterprisedb.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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Dave Page]

Harald Armin Massa wrote:
> Dave,
> 
>> It's coming from direct dependencies on user32.dll (from which we use
>> wsprintf()) and shell32.dll (from which we use SHGetSpecialFolderPath())
>> and is allocated when ResumeThread() is called to kickstart the new
>> backend,
> 
> why does every backend need its own heap for user32.dll or
> shell32.dll? Wasn't the point of shared dlls to be shared?

No idea, and I thought so.

It's quite easy to prove using the test program attached. Just monitor
the desktop heap with dheapmon (from Microsoft's website), and run the
program with a single command line argument to get it to spawn a 100
child processes. You can stop it loading various DLLs by commenting out
the dummy calls to functions in them and rebuilding.

Of course, none of this would be an issue if we made the backend
multithreaded. :-)

I'll get my coat...

/D

#include 
#include 
#include 
#define SECURITY_WIN32
#include 
#include 

int main(int argc, char *argv[])
{
// Dummy functions to force linking to specific libs

// user32.lib
IsCharAlpha('a');

// wsock32.lib
WSADATAwsaData;
WSAStartup(MAKEWORD(1, 1), &wsaData);

// secur32.lib
char un[30];
DWORD dwUNLen = 30;
GetUserNameExA(NameUserPrincipal, un, &dwUNLen); 

// advapi32.dll
char un2[30];
DWORD dwUN2Len = 30;
GetUserNameA(un2, &dwUN2Len); 

// shell32.dll
IsUserAnAdmin();

// Used by child processes
if (argc == 1)
{
while (1)
{
printf("Foo\n");
Sleep(2000);
}
}
else
{
for (int x=0; x<100; x++)
{
STARTUPINFOA si;
PROCESS_INFORMATION pi;

memset(&pi, 0, sizeof(pi));
memset(&si, 0, sizeof(si));
si.cb = sizeof(si);

/*
 * Create the subprocess in a suspended state. This will be 
resumed later,
 * once we have written out the parameter file.
 */
printf("Creating process %d...\n", x);
if (!CreateProcessA(NULL, argv[0], NULL, NULL, TRUE, 
CREATE_SUSPENDED,
   NULL, NULL, &si, &pi))
{
printf("CreateProcess call failed: %m (error code %d)", 
(int) GetLastError());
return -1;
}

printf("Resuming thread %d...\n", x);
if (ResumeThread(pi.hThread) == -1)
{
if (!TerminateProcess(pi.hProcess, 255))
{
printf("could not terminate unstartable 
process: error code %d", (int) GetLastError());
CloseHandle(pi.hProcess);
CloseHandle(pi.hThread);
return -1;
}
CloseHandle(pi.hProcess);
CloseHandle(pi.hThread);
printf("could not resume thread of unstarted process: 
error code %d", (int) GetLastError());
return -1;
}
}
}

return 0;
}


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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Harald Armin Massa]

> > why does every backend need its own heap for user32.dll or
> > shell32.dll? Wasn't the point of shared dlls to be shared?
>
> The Desktop Heap appears to be a place for processes belonging to the same
> "desktop" to allocate shared objects such as GUI elements. These are allocated
> in shared space so they can be manipulated by any process running in that
> "desktop".

Using this knowledge and Daves response, also looking back at "3,2kb
per backend", I stumbled upon that KB entry:

http://support.microsoft.com/?scid=kb%3Ben-us%3B184802&x=15&y=14

Please pay special attention to the following parts:

%SystemRoot%\system32\csrss.exe ObjectDirectory=\Windows
   SharedSection=1024,3072,512 Windows=On SubSystemType=Windows
   ServerDll=basesrv,1 ServerDll=winsrv:UserServerDllInitialization,3
   ServerDll=winsrv:ConServerDllInitialization,2 ProfileControl=Off
   MaxRequestThreads=16

"""The second SharedSection value (3072) is the size of the desktop
heap for each desktop that is associated with the "interactive" window
station WinSta0."""

and further down:

"""All services that are executed under the LocalSystem account with
the Allow Service to Interact with Desktop startup option selected
will use "Winsta0\Default". All these processes will share the same
desktop heap associated with the "Default" application desktop."""

Postgres is definitely NOT started as LocalSystem account; so using a
"logical not" on Microsofts Words that could indicate the reason why
our service-backends consume that memory? Add to this that MS SQL runs
as LocalSystem; and as much as I know also Oracle.

Is this a path of thinking to try?

Harald

-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Harald Armin Massa]

Replying to myself
> Postgres is definitely NOT started as LocalSystem account; so using a
> "logical not" on Microsofts Words that could indicate the reason why
> our service-backends consume that memory? Add to this that MS SQL runs
> as LocalSystem; and as much as I know also Oracle.

just some lines further down:

"""Every service process executed under a user account will receive a
new desktop in a noninteractive window station created by the Service
Control Manager (SCM). Thus, each service executed under a user
account will consume the number of kilobytes of desktop heap specified
in the third SharedSection value. All services executed under the
LocalSystem account with Allow Service to Interact with the Desktop
not selected share the desktop heap of the "Default" desktop in the
noninteractive service windows station (Service-0x0-3e7$)."""


it is exactly as suspected ... just starting the service allocates that heap

Harald


-- 
GHUM Harald Massa
persuadere et programmare
Harald Armin Massa
Spielberger Straße 49
70435 Stuttgart
0173/9409607
fx 01212-5-13695179
-
EuroPython 2008 will take place in Vilnius, Lithuania - Stay tuned!

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

   http://archives.postgresql.org/

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Dave Page]

Harald Armin Massa wrote:
> Replying to myself
>> Postgres is definitely NOT started as LocalSystem account; so using a
>> "logical not" on Microsofts Words that could indicate the reason why
>> our service-backends consume that memory? Add to this that MS SQL runs
>> as LocalSystem; and as much as I know also Oracle.
> 
> just some lines further down:
> 
> """Every service process executed under a user account will receive a
> new desktop in a noninteractive window station created by the Service
> Control Manager (SCM). Thus, each service executed under a user
> account will consume the number of kilobytes of desktop heap specified
> in the third SharedSection value. All services executed under the
> LocalSystem account with Allow Service to Interact with the Desktop
> not selected share the desktop heap of the "Default" desktop in the
> noninteractive service windows station (Service-0x0-3e7$)."""
> 
> 
> it is exactly as suspected ... just starting the service allocates that heap

You're missing the point I think. There's 48MB (iirc) on XP that is
reserved for desktop heaps. From that, it allocates 64KB for
WinSta0\Disconnect, 128KB for WinSta0\Winlogon and 3072KB for
WinSta0\Default (ie. the regular desktop). Each additional session
started by the SCM gets allocated the non-interactive default of 512KB.

It's not the 48MB we're running out of, it's the 512KB. That's why if
you look back in the thread, you'll see I found 8.3 was crashing with 46
connections when running as a service, but with much higher numbers of
connections when run from the logon session.

The reason why Microsoft services don't consume so much heap is that
they are multi-threaded, not multi-process so they don't init user32.dll
etc. for each individual connection like we do, but only once for the
whole server.

/D

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

   http://archives.postgresql.org/

[GENERAL] can't compile Pl/Java

[Roger Mason]

hello,

I need to install Pl/java in my 8.0.12 PostgreSQL.  According to the
documentation on the PL/java wiki I must compile from source.

When I run make the following errors occur:

make[1]: Entering directory `/usr/local/share/pljava-1.3.0/build/classes/pljava'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/usr/local/share/pljava-1.3.0/build/classes/pljava'
make[1]: Entering directory `/usr/local/share/pljava-1.3.0/build/classes/deploy'
make[1]: Nothing to be done for `all'.
make[1]: Leaving directory `/usr/local/share/pljava-1.3.0/build/classes/deploy'
make[1]: Entering directory `/usr/local/share/pljava-1.3.0/build/objs'
i686-pc-linux-gnu-gcc -c -O2 -march=pentium-m -pipe -Wall -Wmissing-prototypes 
-Wpointer-arith -Wdeclaration-after-statement -Wendif-labels 
-fno-strict-aliasing -fpic  -I. -I/usr/include/postgresql/pgsql/server 
-I/usr/include/postgresql/pgsql/internal -D_GNU_SOURCE  
-DPKGLIBDIR=\"/usr/lib/postgresql\" 
-I/usr/local/share/pljava-1.3.0/src/C/include 
-I/usr/local/share/pljava-1.3.0/build/jni -DPGSQL_MAJOR_VER=8 
-DPGSQL_MINOR_VER=0 -DPGSQL_PATCH_VER=12 
-I"/home/rmason/.gentoo/java-config-2/current-user-vm/include" 
-I"/home/rmason/.gentoo/java-config-2/current-user-vm/include/linux" 
/usr/local/share/pljava-1.3.0/src/C/pljava/type/JavaWrapper.c -o 
type/JavaWrapper.o
In file included from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/JNICalls.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/PgObject.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/PgObject_priv.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/type/Type_priv.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/pljava/type/JavaWrapper.c:10:
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:29:22: error: 
postgres.h: No such file or directory
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:30:28: error: 
lib/stringinfo.h: No such file or directory
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:31:18: error: 
fmgr.h: No such file or directory
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:32:25: error: 
mb/pg_wchar.h: No such file or directory
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:33:28: error: 
utils/syscache.h: No such file or directory
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:34:28: error: 
utils/memutils.h: No such file or directory
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:35:27: error: 
tcop/tcopprot.h: No such file or directory
In file included from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/JNICalls.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/PgObject.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/PgObject_priv.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/include/pljava/type/Type_priv.h:12,
 from 
/usr/local/share/pljava-1.3.0/src/C/pljava/type/JavaWrapper.c:10:
/usr/local/share/pljava-1.3.0/src/C/include/pljava/pljava.h:46: error: expected 
'=', ',', ';', 'asm' or '__attribute__' before 'pljavaEntryFence'

Then follow many other errors.

The first error (not finding postgres.h) is puzzling because gcc is
looking in the right place:

/usr/include/postgresql/server/postgres.h

Thanks for any help,

Roger Mason


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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Trevor Talbot]

On 10/23/07, Harald Armin Massa <[EMAIL PROTECTED]> wrote:

> > The Desktop Heap appears to be a place for processes belonging to the same
> > "desktop" to allocate shared objects such as GUI elements. These are 
> > allocated
> > in shared space so they can be manipulated by any process running in that
> > "desktop".
>
> Using this knowledge and Daves response, also looking back at "3,2kb
> per backend", I stumbled upon that KB entry:
>
> http://support.microsoft.com/?scid=kb%3Ben-us%3B184802&x=15&y=14

[...]

> Postgres is definitely NOT started as LocalSystem account; so using a
> "logical not" on Microsofts Words that could indicate the reason why
> our service-backends consume that memory? Add to this that MS SQL runs
> as LocalSystem; and as much as I know also Oracle.

It's not quite what you think.  The link Rainer posted upthread does a
decent job describing it, although there's still some room for
confusion: 
http://blogs.msdn.com/ntdebugging/archive/2007/01/04/desktop-heap-overview.aspx

The hierarchy of containers goes Session, Window Station, Desktop.
Everything relevant is under the same Session, so I'll ignore that for
now.

The console gets a Window Station; this is the interactive one since
the user sitting down works with it directly.  It normally contains
one Desktop of interest (Default), which is what the user actually
sees.  (It's possible to create multiple desktops as a framework for a
"virtual desktop" type of thing, but that's all third-party stuff.)

Each service registered with the Service Manager has a specific
account it logs in under.  For each account, the Service Manager
creates a Window Station to contain it, and all services using the
same account share the default Desktop inside it.  Most services run
under one of the 3 canned accounts, which is what that KB article is
talking about with the Local System bit.

Each Desktop created has a fixed-size chunk of memory allocated to it.
 Desktops created under the interactive Window Station get the larger
chunk of memory (3072KB) since they expect to contain lots of UI
stuff.  Desktops created under other Window Stations get the smaller
chunk of memory (512KB), since they aren't presenting a UI to the
user.

That fixed-size desktop heap is used to track objects handled by the
USER subsystem, which is mostly UI elements like windows and such.
Most of the API interaction for those resources go through user32.dll,
and apparently its initialization procedure grabs some of that heap
space for each process it's loaded into.

The PostgreSQL service is set to log in under its own account, so it
gets its own Window Station, and a default Desktop inside that.  This
is a non-interactive Window Station, so the Desktop gets the smaller
heap.  All postgres.exe processes run in that Desktop and share one
512KB heap.  As each process ends up carving out a chunk of that
space, it uses up all 512KB and fails to create more backends.

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Trevor Talbot]

On 10/23/07, Rainer Bauer <[EMAIL PROTECTED]> wrote:
> "Trevor Talbot" wrote:

> >It could be that there's a significant difference between XP and 2003
> >in how that's handled though.  I do have an XP SP2 machine here with
> >512MB RAM, and I'll try tests on it as soon as I can free up what it's
> >currently occupied with.
>
> Yeah, Win2003 behaves differently accoriding to this source:
> 
>
> 
> Session paged pool allows session specific paged pool allocations.  Windows XP
> uses regular paged pool, since the number of remote desktop connections is
> limited.  On the other hand, Windows Server 2003 makes allocations from
> session paged pool instead of regular paged pool if Terminal Services
> (application server mode) is installed.
> 

That's a little different.  There's a specific range of kernel VM
space dedicated to session-specific data, so each session references
the same addresses but it can be backed by different physical memory
(same concept as separate processes).  The session paged pool area of
that VM space is used to allocate the individual desktop heaps from.

It's saying that under XP, it's mapped to the main kernel paged pool,
while under 2003 TS it's mapped to session-specific memory, to avoid
depleting the main paged pool.  (Each Terminal Services connection
creates an entire Session.)  It doesn't change how desktop heap is
actually used though, which is the issue we're running into.

The system I'm testing on doesn't have Terminal Services running in
appserver mode.

> After increasing the session heap size in the registry from 512KB to 1024KB
> the no. of connections was roughly doubled. So this might be a solution for
> people running out of Desktop heap.
>
> Alter the value of the following key
> 
>
> The numeric values following "SharedSection=" control the heap management:
> On WinXP these are the default values: "SharedSection=1024,3072,512"
> Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
> non-interactive window stations to 1024KB.

It's probably safe to do on a typical XP box, but it's unfortunately
not something you want the installer to do, or even suggest as blanket
advice.  I also wondered about having postmaster create more desktops
on demand, but that has about the same amount of sanity (i.e. not
much).

I think it boils down to getting postgres to avoid using desktop heap
if at all possible, and if not, advising people to avoid XP for high
concurrency, except for suggesting the above change in specific
circumstances.

I suspect win2000 has the same issue, but I don't have a system to
test.  It'd be interesting to know if 2000 Professional behaves any
differently than Server.

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

   http://archives.postgresql.org/

Re: [GENERAL] Bitmap Heap scan 8.1/8.2

[Martin Marques]

Alvaro Herrera wrote:


It's an arbitrary number, based on which all the other numbers are
measured.


Now that I read more intensively he docs I see that all the cost 
parameters are related one with the other.



What people generally do around here is mess with random_page_cost, and
leave seq_page_cost alone.  Often, it's the ratio
seq_page_cost/random_page_cost what's most important to the cost
equations results.  (seq_page_cost wasn't tunable at all until
recently, say 8.1 or 8.2 AFAIR).


Ok, now the 8.1 server has a RAID1 hardware board with SCSI disks, and 
the 8.2 is just a PentiumD with SATA disks (it's my desktop PC where I 
do tests). Should I have a lower random_page_cost on a machine that is 
likely to have a lower disk IO speed?


--
 21:50:04 up 2 days,  9:07,  0 users,  load average: 0.92, 0.37, 0.18
-
Lic. Martín Marqués |   SELECT 'mmarques' ||
Centro de Telemática|   '@' || 'unl.edu.ar';
Universidad Nacional|   DBA, Programador,
del Litoral |   Administrador
-

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

Re: [GENERAL] 8.2.3: Server crashes on Windows using Eclipse/Junit

[Magnus Hagander]

Rainer Bauer wrote:
> After increasing the session heap size in the registry from 512KB to 1024KB
> the no. of connections was roughly doubled. So this might be a solution for
> people running out of Desktop heap.
> 
> Alter the value of the following key
> 
> 
> The numeric values following "SharedSection=" control the heap management:
> On WinXP these are the default values: "SharedSection=1024,3072,512"
> Altering this to "SharedSection=1024,3072,1024" will increase the heap for all
> non-interactive window stations to 1024KB.

This part should go in the FAQ, I think. It's valid for 8.2 as well,
from what I can tell, and it's valid for 8.3 both before and after the
patch I just applied.

Dave, you're listed as maintainer :-P

//Magnus

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

   http://archives.postgresql.org/

[GENERAL] Service pgsql-8.1 failed to start

[Josi Perez]

I'm trying install PostgreSQL-8.1.10 (binary format) in an external HD (USB
connection) under Windows XP SP2.

1) If I try to install postgres as  a service I receive the message
"Service failed to start; verify if you have sufficient privileges to start
system services"

I'm running the installation under an Administrative account.
I disabled AV program and Windows firewall.
In Control Panel/Administrative Tools/Local Security, postgres account has
permission to do logon as service.


2) When I tried didn't to install postgres as a service, the installation
finished and I could to run initdb and restore a small database. But, when I
tried start manually the pg_ctl
a) using the windows account
pg_ctl register -N "pgsql-8.1" -U postgres -P postgres -D E:\Data start
pg_ctl -D E:\Data start

   the message was "You can start in an administrative account". Yes, I'm in
an administrative account, but I registered the service telling the user to
use...

b) without using  the postgres windows account
pg_ctl -D E:\Data start
the message is "You can not start in an administrative account".
Yes, I'm in an administrative account...

I would like to keep an demonstration database in this external hard drive
and would like to attach this HD in different Windows PCs. Start manually
without change the user machine it is a better solution for me and
generally, the user is in an administrative account.

Any ideas?
Thank you.
Josi Perez

[GENERAL] newbie: update timestamp trigger?

[ahnf]

Whenever a row is updated in a table with a timestamp column. How do I write a 
trigger to set that timestamp column to now() or the current timestamp?

thanks



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

Re: [GENERAL] newbie: update timestamp trigger?

[Rodrigo De León]

On 10/23/07, ahnf <[EMAIL PROTECTED]> wrote:
> Whenever a row is updated in a table with a timestamp column. How do I write 
> a trigger to set that timestamp column to now() or the current timestamp?

Straight from the horse's mouth:
http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html#PLPGSQL-TRIGGER-EXAMPLE

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

Re: [GENERAL] newbie: update timestamp trigger?

[Scott Marlowe]

On 10/23/07, ahnf <[EMAIL PROTECTED]> wrote:
> Whenever a row is updated in a table with a timestamp column. How do I write 
> a trigger to set that timestamp column to now() or the current timestamp?

You should be able to take a virtual chainsaw to the example on this
page and do it:

http://www.postgresql.org/docs/8.2/static/plpgsql-trigger.html

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

   http://archives.postgresql.org/

[GENERAL] data statistic functions

[Kevin Hunter]

Hullo List,

What does Postgres offer in terms of statistics support (not the
statistics about the database, but functions to operate on the data).

I know there are simple things like AVG, COUNT, MAX, and MIN, but what else?

I'm not sure where to begin looking, other than a cursory inspection of
http://www.postgresql.org/docs/8.3/static/functions.html . . .

On a side note, I also noted that I couldn't immediately spot AVG, MAX,
or MIN in the output of \df.

Thanks,

Kevin

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

Re: [GENERAL] data statistic functions

[Joshua D. Drake]

On Tue, 23 Oct 2007 16:43:04 -0400
Kevin Hunter <[EMAIL PROTECTED]> wrote:

> Hullo List,
> 
> What does Postgres offer in terms of statistics support (not the
> statistics about the database, but functions to operate on the data).
> 
> I know there are simple things like AVG, COUNT, MAX, and MIN, but
> what else?
> 
> I'm not sure where to begin looking, other than a cursory inspection
> of http://www.postgresql.org/docs/8.3/static/functions.html . . .
> 
> On a side note, I also noted that I couldn't immediately spot AVG,
> MAX, or MIN in the output of \df.

http://www.postgresql.org/docs/8.2/static/functions-aggregate.html

Sincerely,

Joshua D. Drake



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


-- 

  === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564   24x7/Emergency: +1.800.492.2240
PostgreSQL solutions since 1997  http://www.commandprompt.com/
UNIQUE NOT NULL
Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate
PostgreSQL Replication: http://www.commandprompt.com/products/



signature.asc
Description: PGP signature

Re: [GENERAL] data statistic functions

[Steve Atkins]

On Oct 23, 2007, at 1:43 PM, Kevin Hunter wrote:


Hullo List,

What does Postgres offer in terms of statistics support (not the
statistics about the database, but functions to operate on the data).

I know there are simple things like AVG, COUNT, MAX, and MIN, but  
what else?


I'm not sure where to begin looking, other than a cursory  
inspection of

http://www.postgresql.org/docs/8.3/static/functions.html . . .


Take a look under "Aggregate Functions" ( http://www.postgresql.org/ 
docs/8.3/static/functions-aggregate.html ). There's probably some  
interesting stuff if you look at PL/R too ( http://pgfoundry.org/ 
projects/plr/ ).


Cheers,
  Steve




---(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] data statistic functions

[Kevin Hunter]

At 4:53p -0400 on 23 Oct 2007, Steve Atkins wrote:
> There's probably some interesting stuff if you look at PL/R too (
> http://pgfoundry.org/projects/plr/ ).

PL/R . . . that looks promising.  Thanks.

Kevin

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

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

Re: [GENERAL] Bitmap Heap scan 8.1/8.2

[Tom Lane]

Martin Marques <[EMAIL PROTECTED]> writes:
> Ok, now the 8.1 server has a RAID1 hardware board with SCSI disks, and 
> the 8.2 is just a PentiumD with SATA disks (it's my desktop PC where I 
> do tests). Should I have a lower random_page_cost on a machine that is 
> likely to have a lower disk IO speed?

I'd guess the other way round: cheaper disks are likely to have worse
seek speeds, which translates to random accesses being proportionally
slower.

regards, tom lane

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

Re: [GENERAL] data statistic functions

[Tom Lane]

Kevin Hunter <[EMAIL PROTECTED]> writes:
> On a side note, I also noted that I couldn't immediately spot AVG, MAX,
> or MIN in the output of \df.

\df explicitly excludes aggregate functions.  Try \da.

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

[GENERAL] Extracting Index Creation Script

[Paul Silveira]

Hello,

Does anyone have any good code to extract the metadata needed to create
indexes on a specific table?  The Client Tools (like pgadmin-III) presents
that code so I'm sure it's extractable but could not find it in my trace
that I ran while operating pgadmin...

Thanks in advance,

Paul

-- 
View this message in context: 
http://www.nabble.com/Extracting-Index-Creation-Script-tf4680848.html#a13375314
Sent from the PostgreSQL - general mailing list archive at Nabble.com.


---(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

[GENERAL] Start DB giving fatal message.(linux)

[Farhan Khan]

Because of some OS failure I have to reinstall linux and postgres and now
database start gives me following error message.  Any pointers will be
apprciated ...

[EMAIL PROTECTED]:postgresql-8.2.5$ /usr/local/pgsql/bin/postgres -D
/usr/local/pgsql/data
LOG:  database system was shut down at 2007-10-23 16:30:49 PDT
LOG:  checkpoint record is at 0/42C408
LOG:  redo record is at 0/42C408; undo record is at 0/0; shutdown TRUE
LOG:  next transaction ID: 0/593; next OID: 10820
LOG:  next MultiXactId: 1; next MultiXactOffset: 0
LOG:  database system is ready
FATAL:  database "db_test1" does not exist
FATAL:  database "db_test1" does not exist
FATAL:  database "db_test1" does not exist
FATAL:  database "db_test1" does not exist

"db_test1" was one the databases I was using before reinstalling everything.
Note during re-installation, I have formatted the harddrive which has erased
the /usr/local/pgsql/bin/postgres folder ... but there were some stuff that
persisted as it was on NFS (this includes the installer for postgres) ...

BTW, where does postgres saves all db related information which is used at
db start time. Is there a utility to edit this information??

Thanks .. Farhan

Re: [GENERAL] Start DB giving fatal message.(linux)

[Scott Marlowe]

On 10/23/07, Farhan Khan <[EMAIL PROTECTED]> wrote:
> Because of some OS failure I have to reinstall linux and postgres and now
> database start gives me following error message.  Any pointers will be
> apprciated ...
>
> [EMAIL PROTECTED]:postgresql-8.2.5$ /usr/local/pgsql/bin/postgres -D
> /usr/local/pgsql/data
> LOG:  database system was shut down at 2007-10-23 16:30:49 PDT
> LOG:  checkpoint record is at 0/42C408
> LOG:  redo record is at 0/42C408; undo record is at 0/0; shutdown TRUE
> LOG:  next transaction ID: 0/593; next OID: 10820
> LOG:  next MultiXactId: 1; next MultiXactOffset: 0
> LOG:  database system is ready
> FATAL:  database "db_test1" does not exist
>  FATAL:  database "db_test1" does not exist
>  FATAL:  database "db_test1" does not exist
>  FATAL:  database "db_test1" does not exist
>
> " db_test1" was one the databases I was using before reinstalling
> everything. Note during re-installation, I have formatted the harddrive
> which has erased the /usr/local/pgsql/bin/postgres folder ... but there were
> some stuff that persisted as it was on NFS (this includes the installer for
> postgres) ...
>
> BTW, where does postgres saves all db related information which is used at
> db start time. Is there a utility to edit this information??

If what you've written is correct, your old db, db_test1 is gone, lost
when you formatted your hard drive.  postgres stores it's data in
different places depending on the OS and how it was installed.  The
part after the -D in your startup up there is where it is storing its
data right now.  Where the old version stored it is anybody's guess.
OS / pg version and how pg was installed on the old machine would help
us guess where it might have been before formatting erased it.

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

[GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows

[Craig Hawkes]

Hi Everyone I have a question re using postgres as a "embedded" database on
Windows.

First a little background:
I have been using Postgres for a number of years on Linux, and it is great.
I am now working with a company which develops Windows software using
Delphi. This has been successful and they have a number of users (1000's).
This currently uses a Paradox database. For various reasons (some I am sure
you can guess!) we would like to move away from paradox, and Postgres is a
good option.

The main problem(s) I see with Postgres in this environment related to:
 - The fact that the users are very non computer savy - these are general
office workers or Farmers - most will have little interest in the computer
beyond getting a job done.
 - Installation (Yes there is a silent install)
 - running as a service
  What happens in the unlikely event that they already have a version of
Postgres installed?
 - I guess I can see that we could create a installation which works for
most users, but end up spending a lot of time getting the last few computers
updated, as we deal with some obscure problem between windows and services
and Postgres (I know welcome to Windows), and we would like to be able to
manage these updates remotely

I would like to hear feed back from others, has anyone else deployed
Postgres in this type of environment?
Are there options for running postgres, maybe as a process (maybe the wrong
term) within the main application - this could be started and stopped by the
application?

I am thinking some type of options similar to SQLLite would be great for a
single user install - but we also want to be able to A) use the full power
of Postgres, B) set-up as a full server in some other cases.


I look forward to hearing your views
Thanks
Craig

Re: [GENERAL] Using Postgres as a "embedded" / SQL Lite database on Windows

[Scott Marlowe]

On 10/23/07, Craig Hawkes <[EMAIL PROTECTED]> wrote:
> Hi Everyone I have a question re using postgres as a "embedded" database on
> Windows.

This question gets asked about once every three months.  Searching the
archives should turn up some previous discussions on the subject.

> First a little background:
> I have been using Postgres for a number of years on Linux, and it is great.
> I am now working with a company which develops Windows software using
> Delphi. This has been successful and they have a number of users (1000's).
> This currently uses a Paradox database. For various reasons (some I am sure
> you can guess!) we would like to move away from paradox, and Postgres is a
> good option.

> I would like to hear feed back from others, has anyone else deployed
> Postgres in this type of environment?
> Are there options for running postgres, maybe as a process (maybe the wrong
> term) within the main application - this could be started and stopped by the
> application?

No, and it's not likely to happen.  this would make postgresql subject
to silent corruption if the application crashed, and no one wants to
support that on top of the version we already have.

> I am thinking some type of options similar to SQLLite would be great for a
> single user install - but we also want to be able to A) use the full power
> of Postgres, B) set-up as a full server in some other cases.

Sadly, there ain't no such thing as a free lunch here.  Either you
install the real thing, and deal with the issues of administration and
such, or you go with sqllite or it's brethren.

I'd look into building an appliance computer for the customers.
Something in a 1U rack mount with a pair of SATA or SAS drives mounted
in a sw RAID-1 would do nicely for this kind of thing.  Think of it as
delivering the whole solution.  Deliver it with a super simple custom
gui on top to create accounts or phpmyadmin or something.  The further
away from a database on every desktop you get, generally the better
off you'll be.

If embedded fits this project better, then look elsewhere, it's really
not pgsql's strong suit.

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

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

[GENERAL] function result cache for pl/pgsql

[Peter Manchev]

I was thinking about pl/pgsql and comparing it with pl/sql function result 
cache,
featured in Oracle 11g - see

http://www.oracle.com/technology/oramag/oracle/07-sep/o57plsql.html

Is it possible to get pl/pgsql function result cache functionality into 
PostgreSQL?

I am not that familiar with the pgsql internals to hack this only by myself.

Best,
Pete

_
Help yourself to FREE treats served up daily at the Messenger Café. Stop by 
today.
http://www.cafemessenger.com/info/info_sweetstuff2.html?ocid=TXT_TAGLM_OctWLtagline
---(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

[GENERAL] (Never?) Kill Postmaster?

[Stefan Schwarzer]

Hi there,

I read dozens of times the "TIP 2: Don't 'kill -9' the postmaster"...

Now, what am I supposed to do if I launched a query which takes ages,  
and which I want to interrupt?


Thanks for any advice,

Stef

 

  Stefan Schwarzer

  Lean Back and Relax - Enjoy some Nature Photography:
  http://photoblog.la-famille-schwarzer.de

  Appetite for Global Data? UNEP GEO Data Portal:
  http://geodata.grid.unep.ch
  

Re: [GENERAL] (Never?) Kill Postmaster?

[Pavel Stehule]

2007/10/24, Stefan Schwarzer <[EMAIL PROTECTED]>:
> Hi there,
>
> I read dozens of times the "TIP 2: Don't 'kill -9' the postmaster"...
>
> Now, what am I supposed to do if I launched a query which takes ages, and
> which I want to interrupt?
>
> Thanks for any advice,
>
> Stef
>

Hello

you have to use more gently way

select pg_cancel_backend()
http://www.postgresql.org/docs/current/interactive/functions-admin.html

Pavel



> 
>
>
>   Stefan Schwarzer
>
>   Lean Back and Relax - Enjoy some Nature Photography:
>   http://photoblog.la-famille-schwarzer.de
>
>   Appetite for Global Data? UNEP GEO Data Portal:
>   http://geodata.grid.unep.ch
>
> 
>
>
>
>
>

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

Re: [GENERAL] (Never?) Kill Postmaster?

[Ow Mun Heng]

On Wed, 2007-10-24 at 07:57 +0200, Stefan Schwarzer wrote:
> Hi there,
> 
> 
> I read dozens of times the "TIP 2: Don't 'kill -9' the postmaster"...
> 
> 
> Now, what am I supposed to do if I launched a query which takes ages,
> and which I want to interrupt? 


look for the query's procpid and then issue a select
pg_cancel_backend('the_id')

> 

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

Re: [GENERAL] function result cache for pl/pgsql

[Pavel Stehule]

2007/10/24, Peter Manchev <[EMAIL PROTECTED]>:
>
> I was thinking about pl/pgsql and comparing it with pl/sql function result 
> cache,
> featured in Oracle 11g - see
>
> http://www.oracle.com/technology/oramag/oracle/07-sep/o57plsql.html
>
> Is it possible to get pl/pgsql function result cache functionality into 
> PostgreSQL?

plpgsql doesn't allow access to shared memory now. You can use plper
for caching some resuts. I am not sure if result cache is generally
accaptable technique, but I thing, I can write similar functionality
for orafce

if you need anything hurry, use plperl
http://www.postgresql.org/docs/8.2/interactive/plperl-global.html and
$_SHARED array.

Regards
Pavel Stehule

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

   http://archives.postgresql.org/

Re: [GENERAL] (Never?) Kill Postmaster?

[Tommy Gildseth]

Pavel Stehule wrote:

2007/10/24, Stefan Schwarzer <[EMAIL PROTECTED]>:
  

I read dozens of times the "TIP 2: Don't 'kill -9' the postmaster"...

Now, what am I supposed to do if I launched a query which takes ages, and
which I want to interrupt?

you have to use more gently way

select pg_cancel_backend()
http://www.postgresql.org/docs/current/interactive/functions-admin.html


The problem is that pg_cancel_backend() is somewhat unreliable at 
cancelling wayward queries. You can try other options for kill though, 
other than -9, which is kind of a last resort.


--
Tommy Gildseth


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

Re: [GENERAL] (Never?) Kill Postmaster?

[Christian Schröder]

Ow Mun Heng wrote:

look for the query's procpid and then issue a select
pg_cancel_backend('the_id')
  


Does it do any harm if I kill (either with signal 9 or signal 15) the 
single backend process (and not the postmaster)?


Regards,
   Christian

--
Deriva GmbH Tel.: +49 551 489500-42
Financial IT and Consulting Fax:  +49 551 489500-91
Hans-Böckler-Straße 2  http://www.deriva.de
D-37079 Göttingen

Deriva CA Certificate: http://www.deriva.de/deriva-ca.cer


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