Re: [GENERAL] Catalog bloat (again)

On 1/27/2016 2:54 PM, Ivan Voras wrote: For blocking reasons, we'd like to avoid vacuum fulls on these tables (as it seems like touching them will lock up everything else). vacuum full isn't nearly as nasty in 9.x than it was in much older releases, so a lot of the caveats no longer apply.

[GENERAL] A contradiction in 13.2.1

Hello, I'm trying to understand concurrency in PostgreSQL so I'm slowly reading through chapter 13 of the fine manual and I believe I've found a contradiction in section 13.2.1. ​My understanding of the second sentence of the first paragraph is that read committed mode never sees "changes

Re: [GENERAL] Catalog bloat (again)

On Wed, Jan 27, 2016 at 11:54:37PM +0100, Ivan Voras wrote: > So, question #1: WTF? How could this happen, on a regularly vacuumed > system? Shouldn't the space be reused, at least after a VACUUM? The issue > here is not the absolute existence of the bloat space, it's that it's > constantly

Re: [GENERAL] A contradiction in 13.2.1

On Wed, Jan 27, 2016 at 5:59 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster wrote: > >> Hello, >> >> I'm trying to understand concurrency in PostgreSQL so I'm slowly reading >> through chapter 13 of the fine manual

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On Wed, Jan 27, 2016 at 6:24 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > On Wed, Jan 27, 2016 at 4:03 PM, Don Parris wrote: > > > ​Parameter passing and variables are client-side considerations. You > haven't told us how you plan to execute the SQL. > >

[GENERAL] Catalog bloat (again)

Hi, I've done my Googling, and it looks like this is a fairly common problem. In my case, there's a collection of hundreds of databases (10 GB+) with apps which are pretty much designed (a long time ago) with heavy use of temp tables - so a non-trivial system. The databases are vacuumed

Re: [GENERAL] A contradiction in 13.2.1

Dane, > So the mental model I've built based on the first four sentences of > the first paragraph is that when a transaction starts in read > committed mode a snapshot is taken of the (database) universe as it > exists at the moment of its creation and that it's only updated by > changes made

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On Wed, Jan 27, 2016 at 4:03 PM, Don Parris wrote: > On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus wrote: > >> >> >> > Am I on the right track, or is there some better way to set this up? My >> > understanding is that views really aren't meant for

Re: [GENERAL] A motion

Adrian Klaver wrote: Motion: The Coc discussion be moved to its own list where those who care can argue to their hearts content and leave the rest of us to deal with technical questions. Upon a decision on said list the result be posted to the Postgres web site for consideration. Been

Re: [GENERAL] A contradiction in 13.2.1

On Wed, Jan 27, 2016 at 3:23 PM, Dane Foster wrote: > Hello, > > I'm trying to understand concurrency in PostgreSQL so I'm slowly reading > through chapter 13 of the fine manual and I believe I've found a > contradiction in section 13.2.1. > > ​My understanding of the second

Re: [GENERAL] Catalog bloat (again)

On Wed, 27 Jan 2016 23:54:37 +0100 Ivan Voras wrote: > > I've done my Googling, and it looks like this is a fairly common problem. > In my case, there's a collection of hundreds of databases (10 GB+) with > apps which are pretty much designed (a long time ago) with heavy use of

Re: [GENERAL] Catalog bloat (again)

Ivan Voras writes: > On 28 January 2016 at 00:13, Bill Moran wrote: > > On Wed, 27 Jan 2016 23:54:37 +0100 > Ivan Voras wrote: > > > So, question #1: WTF? How could this happen, on a regularly vacuumed > >

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Wed, Jan 27, 2016 at 9:13 PM, Michael Paquier wrote: > On Thu, Jan 28, 2016 at 9:34 AM, David G. Johnston > wrote: > > So how about: > > > > + snprintf(title, strlen(myopt.title) + 50, > > + _("Watch every %lds\t%s\t%s"), > > + sleep,

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

Don Parris wrote: I have several tables... and want db users to be able to add or update ... ... in one step, and get all the information into the correct tables. I think I am ok with setting the privileges on the tables and columns as appropriate to allow each group to select, insert and

Re: [GENERAL] Catalog bloat (again)

On 01/27/2016 03:37 PM, Ivan Voras wrote: On 28 January 2016 at 00:13, Bill Moran > wrote: On Wed, 27 Jan 2016 23:54:37 +0100 Ivan Voras > wrote: > So, question #1: WTF? How

Re: [GENERAL] Catalog bloat (again)

On Thu, 28 Jan 2016 00:37:54 +0100 Ivan Voras wrote: > On 28 January 2016 at 00:13, Bill Moran wrote: > > > On Wed, 27 Jan 2016 23:54:37 +0100 > > Ivan Voras wrote: > > > > > So, question #1: WTF? How could this happen, on a

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Thu, Jan 28, 2016 at 9:34 AM, David G. Johnston wrote: > So how about: > > + snprintf(title, strlen(myopt.title) + 50, > + _("Watch every %lds\t%s\t%s"), > + sleep, head_title, asctime(localtime())); I would just keep the timestamp and the title separated so what

Re: [GENERAL] Performance options for CPU bound multi-SUM query

On 28 January 2016 at 08:41, Matt wrote: > Moving from NUMERIC to FLOAT(8) did indeed lower query times by about 20%. > > I will try fixeddecimal and agg() as time permits. That's surprisingly little gain. Please note that you'll not gain any further improvements from the

Re: [GENERAL] Catalog bloat (again)

On 01/27/2016 03:37 PM, Ivan Voras wrote: On 28 January 2016 at 00:13, Bill Moran > wrote: On Wed, 27 Jan 2016 23:54:37 +0100 Ivan Voras > wrote: > So, question #1: WTF? How

Re: [GENERAL] Catalog bloat (again)

-- Scott Mead via mobile IPhone : +1-607-765-1395 Skype : scottm.openscg Gtalk: sco...@openscg.com > On Jan 27, 2016, at 22:11, Joshua D. Drake wrote: > >> On 01/27/2016 03:37 PM, Ivan Voras wrote: >> >> >> On 28 January 2016 at 00:13, Bill Moran

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus wrote: > > > > Am I on the right track, or is there some better way to set this up? My > > understanding is that views really aren't meant for insert/update > > operations, and I have seen on the web that using views to

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus wrote: > > > > Am I on the right track, or is there some better way to set this up? My > > understanding is that views really aren't meant for insert/update > > operations, and I have seen on the web that using views to

Re: [GENERAL] Catalog bloat (again)

On 28 January 2016 at 00:13, Bill Moran wrote: > On Wed, 27 Jan 2016 23:54:37 +0100 > Ivan Voras wrote: > > > So, question #1: WTF? How could this happen, on a regularly vacuumed > > system? Shouldn't the space be reused, at least after a VACUUM? The

Re: [GENERAL] Request - repeat value of \pset title during \watch interations

On Mon, Jan 11, 2016 at 9:55 PM, Michael Paquier wrote: > On Tue, Jan 12, 2016 at 1:15 AM, David G. Johnston > wrote: > > On Mon, Jan 11, 2016 at 9:03 AM, Tom Lane wrote: > >> > >> "David G. Johnston"

[GENERAL] pgpool II, streaming replication and HA

Hi. I'd like to setup HA PostgreSQL streaming replication cluster with two nodes. The main issue which I'd like to protect from is HW node failure or reboot. I supposed to use repmgr to control PostgreSQL cluster and two pgpool instances as load balancers/DB proxies. The issue which I still

Re: [GENERAL] Performance options for CPU bound multi-SUM query

Moving from NUMERIC to FLOAT(8) did indeed lower query times by about 20%. I will try fixeddecimal and agg() as time permits. On 25 Jan 2016, at 4:44, David Rowley wrote: On 25 January 2016 at 15:45, Matt wrote: I have a warehousing case where data is bucketed by a key of

[GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

I have several tables related to people and their contact information, and want db users to be able to add or update a given person and their respective contact information in one step, and get all the information into the correct tables. I think I am ok with setting the privileges on the tables

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On this track (possibly unrelated)...can a view be used as part of the multi table update Just curious On Wed, 2016-01-27 at 14:48 -0500, Don Parris wrote: > I have several tables related to people and their contact information, > and want db users to be able to add or update a given person

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On Wed, Jan 27, 2016 at 1:28 PM, Bret Stern < bret_st...@machinemanagement.com> wrote: > On this track (possibly unrelated)...can a view be used as part of the > multi table update > Just curious > ​Never done so myself but - I believe so though doing so over a one-to-many relationship can be

Re: [GENERAL] Multi-Table Insert/Update Strategy - Use Functions/Procedures?

> Am I on the right track, or is there some better way to set this up? My > understanding is that views really aren't meant for insert/update > operations, and I have seen on the web that using views to insert/update is > a bit tricky - and still requires a procedure with a rule on the view.

Re: [GENERAL] pgpool II, streaming replication and HA

On 01/27/2016 11:53, Tatsuo Ishii wrote: There's a file called "pool_status" which records the previous status of the DB servers. So the file records that "A is down, B is up". In your case, when A reboots, the old primary server on A may start as a primary, but from the file pgpool knows that

Re: [GENERAL] pgpool II, streaming replication and HA

I don't know anything about repmgr. I neglect the questions to repmgr. > I'd like to setup HA PostgreSQL streaming replication cluster with two > nodes. The main issue which I'd like to protect from is HW node > failure or reboot. I supposed to use repmgr to control PostgreSQL > cluster and two