Re: [GENERAL] XID wraparound with huge pg_largeobject

On 11/30/2015 9:58 AM, David Kensiski wrote: I am working with a client who has a 9.1 database rapidly approaching XID wraparound. They also have an exceedingly large pg_largeobject table (4217 GB) that has never been vacuumed. An attempt to vacuum this on a replica has run for days and

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 13:13:27 -0500, Tom Lane wrote: > "Peter J. Holzer" writes: > > Postgres worker processes are single-threaded, are they? Is there > > something else which could interact badly with a moderately complex > > multithreaded I/O library used from a stored procedure? >

Re: [GENERAL] Replication with 9.4

On Tue, Oct 6, 2015 at 12:27 PM, Thomas Munro wrote: > On Sun, Oct 4, 2015 at 11:47 PM, Michael Paquier > wrote: > > (Seems like you forgot to push the Reply-all button) > > > > On Sun, Oct 4, 2015 at 7:01 PM, Madovsky wrote: > >> On

Re: [GENERAL] XID wraparound with huge pg_largeobject

On Mon, Nov 30, 2015 at 9:58 AM, David Kensiski wrote: > I am working with a client who has a 9.1 database rapidly approaching XID > wraparound. The hard limit at 2 billion, or the soft limit at autovacuum_freeze_max_age? > They also have an exceedingly large pg_largeobject

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

"Peter J. Holzer" writes: > But there is something else which may be relevant: ØMQ uses threads > internally, and I don't actually know whether zmq_msg_recv returning > means that the read(2) call (or whatever) on the socket terminates. ... ugh ... > Postgres worker processes

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 12/01/2015 09:58 AM, Peter J. Holzer wrote: On 2015-12-01 07:16:04 -0800, Adrian Klaver wrote: On 12/01/2015 06:51 AM, Peter J. Holzer wrote: A rather weird observation from the log files of our server (9.5 beta1): 2015-12-01 09:23:37 CET [26265]: [42-1] user=fragner,db=wds,pid=26265

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 07:16:04 -0800, Adrian Klaver wrote: > On 12/01/2015 06:51 AM, Peter J. Holzer wrote: > >A rather weird observation from the log files of our server (9.5 beta1): > > > >2015-12-01 09:23:37 CET [26265]: [42-1] user=fragner,db=wds,pid=26265 > >WARNING: Use of uninitialized value

Re: [GENERAL] 2 questions

On 12/01/2015 09:16 AM, anj patnaik wrote: Thanks for the info Scott. Can I setup a primary server called A that uses the Postgres installation on a nfs mounted filesystem and then A does a daily backup of database A and restores to database B on same filesystem. Then I have server B acting as

[GENERAL] json indexing and data types

Hi As json essentially only has three basic data types, string, int, and boolean, I wonder how much of this - to index, search, and sort on unstructured data - is possible. I guess part of the answer would be 'jsquery and vodka', but let me describe the problem first. The basics is, that I

Re: [GENERAL] Exclusively locking parent tables while disinheriting children.

On Wed, Sep 16, 2015 at 12:10 AM, Thom Brown wrote: > On 7 August 2015 at 12:34, Thom Brown wrote: >> On 30 July 2015 at 13:35, Rowan Collins wrote: >>> So what I want to understand is what the risk of adding this lock are - >>> under

[GENERAL] how to import "where exists（subquery）" EXISTS CONDITION performance?

I had saw this sentence: SQL statements that use the EXISTS condition in PostgreSQL are very inefficient since the sub-query is RE-RUN for EVERY row in the outer query's table. There are more efficient ways to write most queries, that do not use the EXISTS condition. So,I want to know how

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 10:20:09 -0800, Adrian Klaver wrote: > On 12/01/2015 09:58 AM, Peter J. Holzer wrote: > >On 2015-12-01 07:16:04 -0800, Adrian Klaver wrote: > >>On 12/01/2015 06:51 AM, Peter J. Holzer wrote: > >>>A rather weird observation from the log files of our server (9.5 beta1): > >>> >

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 2015-12-01 18:58:31 +0100, Peter J. Holzer wrote: > I suspect such an interaction because I cannot reproduce the problem > outside of a stored procedure. A standalone Perl script doing the same > requests doesn't get a timeout. > > I guess Alvaro is right: I should strace the postgres worker

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

> Before I start in to implement a DELETE / AUTOVACUUM / VACUUM approach, to > recycling disk space used for a session management table, I would like to > propose, for consideration by this forum, an idea for a different approach. > > A row in a session management table, represents a significant

[GENERAL] Pgbasebackup help

Hi All, I need some help in postgresql base backup. We are currently using multiple DBMS in our project and postgresql is one of them. Our private DBMS keeps the online data and postgresql keeps online as well as historical data. At present, we are doing the backup/restore process for our

Re: [GENERAL] 2 questions

1) directory listing: /opt/nfsDir/postgres/9.4/ /bin /data /etc /pgAdmin3 2) The way I am using PG now is that I

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On 12/01/2015 06:51 AM, Peter J. Holzer wrote: A rather weird observation from the log files of our server (9.5 beta1): 2015-12-01 09:23:37 CET [26265]: [42-1] user=fragner,db=wds,pid=26265 WARNING: Use of uninitialized value $success in concatenation (.) or string at

[GENERAL] FATAL: unable to read data from DB node 0

Hi everybody I've the next architecture with pgpool (streaming replication mode): 4 nodes 1 Master Node 2 Standbys Node 1 pgpool Node I've disabled the load balancing, because some clients report me problems with the load balancer, they told me the load balancer some times send querys to

Re: [GENERAL] 2 questions

Hello, Yes, postgres is currently installed on a nfs mounted file system. So when graphical installer runs, there is a form which asks location for installation. I specified this path /opt/nfsDir/Postgres where nfsDir is a nfs mount. So currently this is where PG 9.4 lives. My question is when I

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

Peter J. Holzer wrote: > Two messages from the same line of the same plperlu stored procedure, 68 > seconds apart. So what is this line 36? > > confess "impossible result '$success' (payload=$payload)"; > > What? The first message clearly comes from interpolating $success > (which is undef

Re: [GENERAL] plperlu stored procedure seems to freeze for a minute

On Tue, Dec 1, 2015 at 8:51 AM, Peter J. Holzer wrote: > What could cause a plperlu procedure to freeze for 68 > seconds between the call to confess and its output? > Another weird thing: $success is undef because a ØMQ rpc call[1] timed > out (after 60 seconds, which is also

Re: [GENERAL] using a postgres table as a multi-writer multi-updater queue

On Mon, 30 Nov 2015 23:07:36 -0500, "Steve Petrie, P.Eng." wrote: >Instead of using a DELETE command to destroy the row (and a >resource-intensive AUTOVACUUM / VACUUM process to reclaim the storage >space), why not instead, simply mark that session management row as "free"

[GENERAL] plperlu stored procedure seems to freeze for a minute

A rather weird observation from the log files of our server (9.5 beta1): 2015-12-01 09:23:37 CET [26265]: [42-1] user=fragner,db=wds,pid=26265 WARNING: Use of uninitialized value $success in concatenation (.) or string at /usr/local/share/perl/5.20.2/WDS/Macrobond/Utils.pm line 36. 2015-12-01

Re: [GENERAL] 2 questions

> On Nov 30, 2015, at 12:54, anj patnaik wrote: > > 1) directory listing: > > /opt/nfsDir/postgres/9.4/ > /bin > /data > /etc >