Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

On 29 April 2017 at 11:37, David G. Johnston wrote: >> > Perhaps there are reasons why this optimization is not safe that I >> > haven't >> > thought about? >> >> Yeah, I think so. What happens if an A row cannot find a match in B or >> C? This version of the query

Re: [GENERAL] [PERFORM] Questionaire: Common WAL write rates on busy servers.

On 04/28/2017 01:34 AM, Andres Freund wrote: On 2017-04-28 01:29:14 +0200, Tomas Vondra wrote: I can confirm this observation. I bought the Intel 750 NVMe SSD last year, the device has 1GB DDR3 cache on it (power-loss protected), can do ~1GB/s of sustained O_DIRECT sequential writes. But when

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

On Fri, Apr 28, 2017 at 3:24 PM, David Rowley wrote: > On 29 April 2017 at 07:59, Dave Vitek wrote: > > Is what I want in the query planner's vocabulary? It would need to > exploit > > the fact that the _id columns are not nullable, and

[GENERAL] Reset privileges to builtin defaults

Hi, I've been charged with the administration of a couple of big databases in an old 8.1 cluster. The databases have many administrative issues, privileges being the most prominent for my job. All objects have privileges for specific personal end-user roles (dozens and dozens of privileges each

Re: [GENERAL] query planner placement of sort/limit w.r.t. joins

On 29 April 2017 at 07:59, Dave Vitek wrote: > Is what I want in the query planner's vocabulary? It would need to exploit > the fact that the _id columns are not nullable, and either exploit the > uniqueness of the id columns or do an extra LIMIT step after the join. I >

[GENERAL] query planner placement of sort/limit w.r.t. joins

Hi all, I have a query I'd like to speed up. I am wondering whether the query planner is capable of coming up with a certain kind of plan for this query, and if I might tickle it into doing so, or if I have to explicitly use subqueries to get what I want. Imagine we have tables A, B, and C

Re: [GENERAL] Error in PostgreSQL Log

Sachin Srivastava writes: > Dear Team, > > Please suggest why we are getting the below error in our PostgreSQL > DB log. > > We are using postgresql version = 9.2 > > > ERROR:  prepared transaction with identifier "131077_AP// I dont have such an ancient

Re: [GENERAL] Error in PostgreSQL Log

On Friday, April 28, 2017, Sachin Srivastava wrote: > > Please suggest why we are getting the below error in our PostgreSQL DB log. > > ERROR: prepared transaction with identifier "131077_AP// >

Re: [GENERAL] Error in PostgreSQL Log

I have already tuned below parameters as max_prepared_transactions = 500 max_connection = 2000 Regards, SSR On Sat, Apr 29, 2017 at 12:15 AM, Sachin Srivastava wrote: > Dear Team, > > Please suggest why we are getting the below error in our PostgreSQL DB log. >

[GENERAL] Error in PostgreSQL Log

Dear Team, Please suggest why we are getting the below error in our PostgreSQL DB log. We are using postgresql version = 9.2 ERROR: prepared transaction with identifier "131077_AP//CqqfIep65ipZAmdpAAIcgjE=_AP//CqqfIep65ipZAmdpAAIciwAA" does not exist STATEMENT:

Re: [GENERAL] PostgreSQL Required Monitoring

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andrew Kerber Sent: Friday, April 28, 2017 12:58 PM To: Scott Marlowe Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL Required Monitoring Attention:

Re: [GENERAL] PostgreSQL Required Monitoring

Yes, that was the first item on my list (disk space)... On Fri, Apr 28, 2017 at 11:56 AM, Scott Marlowe wrote: > On Fri, Apr 28, 2017 at 8:39 AM, Andrew Kerber > wrote: > > I am a fairly experienced Oracle DBA, and we are starting to move in to

Re: [GENERAL] PostgreSQL Required Monitoring

On Fri, Apr 28, 2017 at 8:39 AM, Andrew Kerber wrote: > I am a fairly experienced Oracle DBA, and we are starting to move in to the > PostgreSQL world. I would expect the standard monitoring items are required > for mission critical postgres apps, Ie, disk space, wal log

Re: [GENERAL] PostgreSQL Required Monitoring

From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Andrew Kerber Sent: Friday, April 28, 2017 12:22 PM To: John R Pierce Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] PostgreSQL Required Monitoring Attention: This

Re: [GENERAL] PostgreSQL Required Monitoring

yes, replication monitoring is high on the oracle list also, just forgot to mention it. I ran into a similar transaction issue in Oracle when they were running queries across database links and not committing. Its a little known fact that any oracle query that runs across a database link starts

Re: [GENERAL] PostgreSQL Required Monitoring

On 4/28/2017 7:39 AM, Andrew Kerber wrote: I am a fairly experienced Oracle DBA, and we are starting to move in to the PostgreSQL world. I would expect the standard monitoring items are required for mission critical postgres apps, Ie, disk space, wal log space, log monitoring, process

Re: [GENERAL] PostgreSQL Required Monitoring

On Fri, Apr 28, 2017 at 4:39 PM, Andrew Kerber wrote: > I am a fairly experienced Oracle DBA, and we are starting to move in to > the PostgreSQL world. I would expect the standard monitoring items are > required for mission critical postgres apps, Ie, disk space, wal

Re: [GENERAL] PostgreSQL Required Monitoring

On Fri, Apr 28, 2017 at 10:46 AM, Joshua D. Drake wrote: > On 04/28/2017 07:39 AM, Andrew Kerber wrote: > >> I am a fairly experienced Oracle DBA, and we are starting to move in to >> the PostgreSQL world. I would expect the standard monitoring items are >> required for

Re: [GENERAL] PostgreSQL Required Monitoring

On 04/28/2017 07:39 AM, Andrew Kerber wrote: I am a fairly experienced Oracle DBA, and we are starting to move in to the PostgreSQL world. I would expect the standard monitoring items are required for mission critical postgres apps, Ie, disk space, wal log space, log monitoring, process

[GENERAL] PostgreSQL Required Monitoring

I am a fairly experienced Oracle DBA, and we are starting to move in to the PostgreSQL world. I would expect the standard monitoring items are required for mission critical postgres apps, Ie, disk space, wal log space, log monitoring, process counts,software running, connection available on the

Re: [GENERAL] GDAL OUT-DB RASTER SUPPORT

On 04/28/2017 07:09 AM, Osahon Oduware wrote: Hi All, I am getting a black image when I try to display PostGIS out-db rasters. I discovered that it has to do with GDAL support for out-db rasters as stated in the link below for a similar issue (over 1 year back):

Re: [GENERAL] Postgres 9.6.2 and pg_log - solved

De : pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow...@postgresql.org] De la part de Mark Watson Envoyé : Monday, April 24, 2017 3:39 PM À : David G. Johnston Cc : (pgsql-general@postgresql.org) Objet : Re: [GENERAL] Postgres 9.6.2 and pg_log De : David G. Johnston

[GENERAL] clarification about async streaming replication and replication slots

Hi, I need a clarification about async streaming replication and replication slots. I have a master and a standby both with server process running but with network disconnected between them. In my primary, I see: pg_current_xlog_location = B/48DFF790 and, through pg_replication_slots, that

Re: [GENERAL] LIMIT clause slowing down query in some cases, accelerating in others

On 04/26/2017 08:11 PM, Klaus P. Pieper wrote: > > Running PostgreSQL 9.6 on a Windows Server. > > Table “t” is kind of a materialized view with > 100 columns and 2.24 > Mio rows. Queries are generated by an ORM framework – fairly difficult > to modify. > > Vacuum analyze was carried out – no