Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

On Tue, Nov 22, 2011 at 10:51 PM, Robert Treat wrote: > On Tue, Nov 22, 2011 at 11:00 PM, Lonni J Friedman wrote: >> On Tue, Nov 22, 2011 at 7:49 PM, Tom Lane wrote: >>> Lonni J Friedman writes: I suspect you're right.  I just ran strace against that PID again, and now all the lseek &

Re: [GENERAL] wal archiving on a hot-standby server

On Mon, Nov 21, 2011 at 5:58 AM, Enrico Sirola wrote: > Hello, > is it possible to archive the WAL files received by a hot-standby server? In > noticed nothing about this on the pgsql docs. The idea > is to archive logs in two locations, at the primary site and at the replica > site (over a wan)

Re: [GENERAL] Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

On Tue, Nov 22, 2011 at 10:21 AM, Scott Marlowe wrote: > On Tue, Nov 22, 2011 at 7:50 AM, Reid Thompson wrote: >> Note that I man­u­ally added the 4 to the desired size of 35..again, for >> some legacy rea­sons inside PG. Done. That's it. Should we check? >> >> d TABLE1 >> >> TABLE "public.TABLE1

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

On Tue, Nov 22, 2011 at 11:00 PM, Lonni J Friedman wrote: > On Tue, Nov 22, 2011 at 7:49 PM, Tom Lane wrote: >> Lonni J Friedman writes: >>> I suspect you're right.  I just ran strace against that PID again, and >>> now all the lseek & read FD's are referrring to a different number >>> (115), so

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

On Tue, Nov 22, 2011 at 7:49 PM, Tom Lane wrote: > Lonni J Friedman writes: >> I suspect you're right.  I just ran strace against that PID again, and >> now all the lseek & read FD's are referrring to a different number >> (115), so that means its moved onto something new since I looked a few >>

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

Lonni J Friedman writes: > I suspect you're right. I just ran strace against that PID again, and > now all the lseek & read FD's are referrring to a different number > (115), so that means its moved onto something new since I looked a few > hours ago? > Anyway, I think this is what you were refe

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

On Tue, Nov 22, 2011 at 7:19 PM, Tom Lane wrote: > Lonni J Friedman writes: >> Thanks for your prompt reply.  I was pretty sure that I was using the >> default, but just to confirm, I just ran: >> 'SHOW vacuum_cost_delay;' > > What about autovacuum_vacuum_cost_delay?  The selects seem to be > del

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

Hi, On 23 November 2011 13:20, Lonni J Friedman wrote: >  I investigated, and found that for the past ~18 hours, > there's one autovacuum process that has been running, and not making > any obvious progress: snip... > I'm using the defaults for all the *vacuum* options in > postgresql.conf, exc

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

Lonni J Friedman writes: > Thanks for your prompt reply. I was pretty sure that I was using the > default, but just to confirm, I just ran: > 'SHOW vacuum_cost_delay;' What about autovacuum_vacuum_cost_delay? The selects seem to be delaying for 32msec, which is not the default for anything. >

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

On Tue, Nov 22, 2011 at 6:57 PM, Tom Lane wrote: > Lonni J Friedman writes: >> When I strace PID 30188, I see tons of this scrolling past quickly, >> but I'm not really sure what it means beyond a 'Timeout' not looking >> good: >> select(0, NULL, NULL, NULL, {0, 32000}) = 0 (Timeout) >> lseek(95,

Re: [GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

Lonni J Friedman writes: > When I strace PID 30188, I see tons of this scrolling past quickly, > but I'm not really sure what it means beyond a 'Timeout' not looking > good: > select(0, NULL, NULL, NULL, {0, 32000}) = 0 (Timeout) > lseek(95, 753901568, SEEK_SET) = 753901568 > read(95, "\2

Re: [GENERAL] synchronous replication + fsync=off?

Tomas Vondra wrote: > On 22 Listopad 2011, 18:16, Bruce Momjian wrote: > > Tomas Vondra wrote: > >> While I don't recommend it, fsync=off definitely is an option, > >> especially > >> with sync replication. The synchronous_commit is not a 1:1 replacement. > >> > >> Imagine for example a master with

[GENERAL] autovacuum stuck on a table for 18+ hours, consuming lots of CPU time

Greetings, I'm running PostgreSQL-9.0.4 on a Linux-x86_64 cluster with 1 master, and two streaming replication slaves. Since late yesterday, the load on the server has been noticably higher (5.00+) than normal (generally under 1.00). I investigated, and found that for the past ~18 hours, there's

Re: [GENERAL] Biztalk adapter for postgresql 9

On 11/22/2011 08:29 PM, Jenish Vyas wrote: Hi All, I need to integrate Biztalk with Postgresql , But not able to find the adapter for the same. All database adapter is available on www including MySql, But Postgresql adapter is missing. Can anyone guide me where to find it? Unless someone's

Re: [GENERAL] PostgreSQL uninstall fails

On 11/23/2011 06:47 AM, J.V. wrote: So if there is only one versin of postgeSQL installed and no other products with the name "postgres", which I am guessing is very typical in most instances, can I change the uninstaller to delete/remove everything that this installer installed? The installer

Re: [GENERAL] PostgreSQL uninstall fails

On 11/22/11 2:47 PM, J.V. wrote: So if there is only one versin of postgeSQL installed and no other products with the name "postgres", which I am guessing is very typical in most instances, can I change the uninstaller to delete/remove everything that this installer installed? If there is ano

Re: [GENERAL] synchronous replication + fsync=off?

On 22 Listopad 2011, 18:16, Bruce Momjian wrote: > Tomas Vondra wrote: >> While I don't recommend it, fsync=off definitely is an option, >> especially >> with sync replication. The synchronous_commit is not a 1:1 replacement. >> >> Imagine for example a master with lot of I/O, and a sync standby. B

Re: [GENERAL] synchronous replication + fsync=off?

On Tue, Nov 22, 2011 at 12:16 PM, Bruce Momjian wrote: > Tomas Vondra wrote: >> On 17 Listopad 2011, 17:07, Jaime Casanova wrote: >> > On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg >> > wrote: >> >> Hello, >> >> >> >> I have two servers with battery backed power supply (USV). So it is >> >> un

Re: [GENERAL] PostgreSQL uninstall fails

So if there is only one versin of postgeSQL installed and no other products with the name "postgres", which I am guessing is very typical in most instances, can I change the uninstaller to delete/remove everything that this installer installed? If there is another instance, it would leave the

Re: [GENERAL] pg_standby for postgresql8.2

On Tue, Nov 22, 2011 at 4:09 AM, khizer wrote: > Hi, > >    May i know how to install pg_standby for postgresql8.2 in ubuntu 10.10 >  OS > I copied the pg_standby folder for compilation which has the files > pg_standby.c, Makefile > > initially i tried with make, make install inside contrip/pg_sta

Re: [GENERAL] Incremental backup with RSYNC or something?

On 11/22/2011 3:28 PM, Merlin Moncure wrote: On Sun, Nov 13, 2011 at 5:38 AM, Phoenix Kiula wrote: Hi. I currently have a cronjob to do a full pgdump of the database every day. And then gzip it for saving to my backup drive. However, my db is now 60GB in size, so this daily operation is makin

Re: [GENERAL] Incremental backup with RSYNC or something?

On Sun, Nov 13, 2011 at 5:38 AM, Phoenix Kiula wrote: > Hi. > > I currently have a cronjob to do a full pgdump of the database every > day. And then gzip it for saving to my backup drive. > > However, my db is now 60GB in size, so this daily operation is making > less and less sense. (Some of you

Re: [GENERAL] possible race condition in trigger functions on insert operations?

On Tue, Nov 22, 2011 at 3:52 PM, Merlin Moncure wrote: > On Tue, Nov 22, 2011 at 2:43 PM, David Johnston wrote: >> Just create a single sequence for each year and then call the proper one >> on-the-fly.  You can create multiple sequences in advance and possible even >> auto-create the sequence th

Re: [GENERAL] possible race condition in trigger functions on insert operations?

Pre-created sequences per year. Not a bad idea at all. I had not been worrying about gaps but I checked with the user and they definitely do not want gaps. But as long as we do not delete, is there still a risk of gaps? I am leaning towards a serial column maintained by postgres, using row_number

Re: [GENERAL] possible race condition in trigger functions on insert operations?

On Tue, Nov 22, 2011 at 2:43 PM, David Johnston wrote: > Just create a single sequence for each year and then call the proper one > on-the-fly.  You can create multiple sequences in advance and possible even > auto-create the sequence the first time one is attempted to be used in a > given year.  

Re: [GENERAL] possible race condition in trigger functions on insert operations?

-Original Message- From: Kenneth Tilton [mailto:ktil...@mcna.net] Sent: Tuesday, November 22, 2011 1:52 PM To: David Johnston Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] possible race condition in trigger functions on insert operations? On Tue, Nov 22, 2011 at 12:48 PM, David

Re: [GENERAL] are there any methods to disable updating index before inserting large number tuples?

On 11/22/11 10:53 AM, Andres Freund wrote: 20M rows inserted inside one transaction doesn't cause*that* many writes. indeed, I just ran a test on a fairly beefy server, a 2U HP DL180G6 with dual Sandy Bridge E5660 CPUs (12 cores, 24 threads), 48GB, and 20 x 15k SAS RAID10 on a 1GB SAS2 raid

Re: [GENERAL] possible race condition in trigger functions on insert operations?

On Tue, Nov 22, 2011 at 2:05 PM, Andreas Kretschmer wrote: > Kenneth Tilton wrote: > >> On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer >> wrote: >> > Kenneth Tilton wrote: >> > >> >> Bit of a trigger NOOB Q: >> >> >> >> I am trying to use a trigger function to automatically populate new >

Re: [GENERAL] possible race condition in trigger functions on insert operations?

Kenneth Tilton wrote: > On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer > wrote: > > Kenneth Tilton wrote: > > > >> Bit of a trigger NOOB Q: > >> > >> I am trying to use a trigger function to automatically populate new > >> rows in a table with  a public ID of the form -NNN such that t

Re: [GENERAL] possible race condition in trigger functions on insert operations?

Thanks, that's perfect. -kenneth On Tue, Nov 22, 2011 at 12:53 PM, Merlin Moncure wrote: > On Tue, Nov 22, 2011 at 11:25 AM, Kenneth Tilton wrote: >> Bit of a trigger NOOB Q: >> >> I am trying to use a trigger function to automatically populate new >> rows in a table with  a public ID of the fo

Re: [GENERAL] possible race condition in trigger functions on insert operations?

On Tue, Nov 22, 2011 at 12:53 PM, Andreas Kretschmer wrote: > Kenneth Tilton wrote: > >> Bit of a trigger NOOB Q: >> >> I am trying to use a trigger function to automatically populate new >> rows in a table with  a public ID of the form -NNN such that the >> 42nd row created in 2011 would get

Re: [GENERAL] are there any methods to disable updating index before inserting large number tuples?

Hi, On Tuesday 22 Nov 2011 19:01:02 John R Pierce wrote: > On 11/22/11 7:52 AM, Andrew Sullivan wrote: > > But I think performance on that table is going to be pretty bad. I > > suspect that COPY is going to be your friend here. > > indeed. 20M rows/hour is 5500 rows/second. you'd better have

Re: [GENERAL] possible race condition in trigger functions on insert operations?

On Tue, Nov 22, 2011 at 12:48 PM, David Johnston wrote: > -Original Message- > From: pgsql-general-ow...@postgresql.org > [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Kenneth Tilton > Sent: Tuesday, November 22, 2011 12:26 PM > To: pgsql-general@postgresql.org > Subject: [GENER

Re: [GENERAL] are there any methods to disable updating index before inserting large number tuples?

On 11/22/11 7:52 AM, Andrew Sullivan wrote: But I think performance on that table is going to be pretty bad. I suspect that COPY is going to be your friend here. indeed. 20M rows/hour is 5500 rows/second. you'd better have a seriously fast disk system, say, 20 15k RPM SAS drives in a RAID10

Re: [GENERAL] possible race condition in trigger functions on insert operations?

On Tue, Nov 22, 2011 at 11:25 AM, Kenneth Tilton wrote: > Bit of a trigger NOOB Q: > > I am trying to use a trigger function to automatically populate new > rows in a table with  a public ID of the form -NNN such that the > 42nd row created in 2011 would get the ID "2011-042". Each row is > as

Re: [GENERAL] possible race condition in trigger functions on insert operations?

Kenneth Tilton wrote: > Bit of a trigger NOOB Q: > > I am trying to use a trigger function to automatically populate new > rows in a table with a public ID of the form -NNN such that the > 42nd row created in 2011 would get the ID "2011-042". Each row is > associated via an iasid column wit

Re: [GENERAL] possible race condition in trigger functions on insert operations?

-Original Message- From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Kenneth Tilton Sent: Tuesday, November 22, 2011 12:26 PM To: pgsql-general@postgresql.org Subject: [GENERAL] possible race condition in trigger functions on insert operations

[GENERAL] possible race condition in trigger functions on insert operations?

Bit of a trigger NOOB Q: I am trying to use a trigger function to automatically populate new rows in a table with a public ID of the form -NNN such that the 42nd row created in 2011 would get the ID "2011-042". Each row is associated via an iasid column with a row in an audit table that has a

Re: [GENERAL] synchronous replication + fsync=off?

Tomas Vondra wrote: > On 17 Listopad 2011, 17:07, Jaime Casanova wrote: > > On Thu, Nov 17, 2011 at 7:52 AM, Schubert, Joerg > > wrote: > >> Hello, > >> > >> I have two servers with battery backed power supply (USV). So it is > >> unlikely, that both will crash at the same time. > >> > >> Will syn

[GENERAL] pg_standby for postgresql8.2

Hi, May i know how to install pg_standby for postgresql8.2 in ubuntu 10.10 OS I copied the pg_standby folder for compilation which has the files pg_standby.c, Makefile initially i tried with make, make install inside contrip/pg_standby folder but i got an err Makefile.global no such fil

Re: [GENERAL] Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

for the future it is better to just use text type, and: check length(field) < 35; -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] are there any methods to disable updating index before inserting large number tuples?

On Tue, Nov 22, 2011 at 11:47:15PM +0800, sunpeng wrote: > >disable index update > >insert into A //here will insert 20 millions tuples > > enable index update > BEGIN; DROP INDEX; INSERT INTO A; CREATE INDEX; But I think performance on that table is going to be pretty bad. I suspect

[GENERAL] are there any methods to disable updating index before inserting large number tuples?

hi, friends, Now each hour I want to insert about 20 millions tuples into table A, which has a btree index. How to disable index update before inserting those 20 millions tuples, and then enable it after those tuples having being inserted? Just as the followings: >disable index update >ins

Re: [GENERAL] Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

On Tue, Nov 22, 2011 at 7:50 AM, Reid Thompson wrote: > Note that I man­u­ally added the 4 to the desired size of 35..again, for > some legacy rea­sons inside PG. Done. That's it. Should we check? > > d TABLE1 > > TABLE "public.TABLE1" > COLUMN  |  TYPE                 | Modifiers > +-

[GENERAL] Is this safe to perform on PostgreSQL 8.3.7 -> Resize a column in a PostgreSQL table without changing data

reporting=# select version(); version -- PostgreSQL 8.3.7 on x86_64-redhat-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20071124 (Red Hat 4.1.2-4

Re: [GENERAL] Why CASCADE constraint takes more time when table is loaded with huge records?

On Nov 22, 2011, at 4:32, Siva Palanisamy wrote: > Hi ya, > > > > As I had raised a question here at ‘Please recommend the best bulk-delete > option’ > (http://stackoverflow.com/questions/8172101/please-recommend-the-best-bulk-delete-option), > CASCADE constraint is the one that prevents m

Re: [GENERAL] Installing postgresql on windows 7

Will this works ... http://postgresql.1045698.n5.nabble.com/Unable-to-write-inside-TEMP-environment-variable-path-td3315027.html --- Regards, Raghavendra EnterpriseDB Corporation Blog: http://raghavt.blogspot.com/ On Tue, Nov 22, 2011 at 6:13 PM, Twaha Daudi wrote: > Ashesh, > Yes, I have r

Re: [GENERAL] Installing postgresql on windows 7

Ashesh, Yes, I have read and write permission.Please an idea? Twaha On Tue, Nov 22, 2011 at 9:37 AM, Ashesh Vashi wrote: > On Tue, Nov 22, 2011 at 1:05 PM, Twaha Daudi wrote: > >> Hello Ashesh, >> here is the output of the command: >> C:\>echo %TEMP% >> C:\Users\User\AppData\Local\Temp >> >> It

[GENERAL] Biztalk adapter for postgresql 9

Hi All, I need to integrate Biztalk with Postgresql , But not able to find the adapter for the same. All database adapter is available on www including MySql, But Postgresql adapter is missing. Can anyone guide me where to find it? Thanks & regards, JENISH VYAS

Re: [GENERAL] wal archiving on a hot-standby server

Hello John, Il giorno 22/nov/2011, alle ore 11.04, John R Pierce ha scritto: > I don't believe that function is immutable, since it depends on the value of > pg_current_xlog_location() which will change over time. oops, I'm afraid you are right! e. -- Sent via pgsql-general mailing list (pgs

Re: [GENERAL] wal archiving on a hot-standby server

On 11/22/11 1:51 AM, Enrico Sirola wrote: create or replace function current_xlog_bytes() returns int8 as $$ select cast(cast( 'x' || lpad(split_part( pg_current_xlog_location(), '/', 1), 8, '0') as bit(3

Re: [GENERAL] wal archiving on a hot-standby server

This message has been digitally signed by the sender. Re___GENERAL__wal_archiving_on_a_hot_standby_server.eml Description: Binary data - Hi-Tech Gears Ltd, Gurgaon, India -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

Re: [GENERAL] wal archiving on a hot-standby server

Hello Simon, Il giorno 21/nov/2011, alle ore 15.47, Simon Riggs ha scritto: > On Mon, Nov 21, 2011 at 10:58 AM, Enrico Sirola > wrote: > >> is it possible to archive the WAL files received by a hot-standby server? In >> noticed nothing about this on the pgsql docs. The idea is to archive logs

[GENERAL] Why CASCADE constraint takes more time when table is loaded with huge records?

Hi ya, As I had raised a question here at 'Please recommend the best bulk-delete option' (http://stackoverflow.com/questions/8172101/please-recommend-the-best-bulk-delete-option), CASCADE constraint is the

Re: [GENERAL] VACUUM touching file but not updating relation

On Fri, Nov 18, 2011 at 3:18 PM, Tom Lane wrote: >> So the correct number of WAL records is emitted and I see no bug there. > > What Thom's complaining about is that the buffer may be marked dirty > unnecessarily, ie when there has been no actual data change. Based upon both your feedback, I mad

Re: [GENERAL] [general] rsync'd database requires reindex - why ?

On Tue, Nov 22, 2011 at 7:32 AM, marcin kowalski wrote: > i'm simply stopping postgresql If you do do pg_ctl stop -m immediate then the copy will be corrupt. You need to do a correct shutdown for it to work. --  Simon Riggs   http://www.2ndQuadrant.com/  PostgreSQL Development

Re: [GENERAL] Using KNN for objects that have more than 2 dimensions?

On 11/22/11 12:11 AM, Bèrto ëd Sèra wrote: Hi >Whats KNN ? http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1#K-Nearest-Neighbor_Indexing At least this is my understanding of the question.

Fwd: [GENERAL] Installing postgresql on windows 7

-- Forwarded message -- From: Twaha Daudi Date: Tue, Nov 22, 2011 at 9:35 AM Subject: Re: [GENERAL] Installing postgresql on windows 7 To: Ashesh Vashi Hello Ashesh, here is the output of the command: C:\>echo %TEMP% C:\Users\User\AppData\Local\Temp It looks like the variable i

Re: [GENERAL] Using KNN for objects that have more than 2 dimensions?

Hi >Whats KNN ? http://wiki.postgresql.org/wiki/What's_new_in_PostgreSQL_9.1#K-Nearest-Neighbor_Indexing At least this is my understanding of the question. I'm unable to check if it will work on an array (it should, as far as my understanding goes). Bèrto -- == If Pa