Re: [GENERAL] BDR, wal sender, high system cpu, mutex_lock_common

On 12 October 2017 at 11:03, Andres Freund wrote: > On 2017-10-12 10:25:43 +0800, Craig Ringer wrote: >> On 4 October 2017 at 00:21, milist ujang wrote: >> > On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer wrote: >> >> >> >> >> >>

Re: [GENERAL] BDR, wal sender, high system cpu, mutex_lock_common

On 2017-10-12 10:25:43 +0800, Craig Ringer wrote: > On 4 October 2017 at 00:21, milist ujang wrote: > > On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer wrote: > >> > >> > >> Can you get stacks please? > >> > >> Use -g > > > > > > # Events: 2K cpu-clock

Re: [GENERAL] BDR, wal sender, high system cpu, mutex_lock_common

On 4 October 2017 at 00:21, milist ujang wrote: > On Tue, Oct 3, 2017 at 8:49 PM, Craig Ringer wrote: >> >> >> Can you get stacks please? >> >> Use -g > > > # Events: 2K cpu-clock > # > # Overhead Command Shared Object

Re: [GENERAL] Equivalence Classes when using IN

On 12 October 2017 at 10:15, Kim Rose Carlsen wrote: > Why don't I see that predicate (customer_id) pushed into the outer nested > loop so we don't have to sort the whole table on each loop. > > (See original post and follow up for definitions) >

Re: [GENERAL] Equivalence Classes when using IN

> On 11 Oct 2017, at 21.46, David Rowley wrote: > >> On 12 October 2017 at 08:37, Kim Rose Carlsen wrote: >> >>> Yeah. The ORDER BY creates a partial optimization fence, preventing >>> any such plan from being considered. >> >> I can see in

Re: [GENERAL] Equivalence Classes when using IN

On 12 October 2017 at 08:37, Kim Rose Carlsen wrote: > >> Yeah. The ORDER BY creates a partial optimization fence, preventing >> any such plan from being considered. >>> > > I can see in the general case it semanticly means different things If you > allow the WHERE to pass

Re: [GENERAL] Equivalence Classes when using IN

> Yeah. The ORDER BY creates a partial optimization fence, preventing > any such plan from being considered. >> I can see in the general case it semanticly means different things If you allow the WHERE to pass through ORDER BY. A special case can be allowed for WHERE to pass the ORDER BY if

Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication

You mean at the user permissions level? Yes, I could, but would mean doing so table by table, which is not our current structure. I guess there is nothing at the database level. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing

Re: [GENERAL] Making subscribers read only in Postgres 10 logical replication

On 10/11/2017 11:18 AM, rverghese wrote: Hi I'm testing out logical replication on PostgreSQL 10. Is there a setting to make subscribers read-only slaves like with Slony. Currently I can insert into the Publisher and the Subscriber. If there is a conflict, i.e. same record exists in both, then

[GENERAL] Making subscribers read only in Postgres 10 logical replication

Hi I'm testing out logical replication on PostgreSQL 10. Is there a setting to make subscribers read-only slaves like with Slony. Currently I can insert into the Publisher and the Subscriber. If there is a conflict, i.e. same record exists in both, then all replication gets backed up (even to

Re: [GENERAL] Why does increasing the precision of a numeric column rewrites the table?

Thomas Kellerer writes: > I don't understand why going from numeric(12,2) to numeric(15,3) would > require a table rewrite. The comment for numeric_transform explains this: * Flatten calls to numeric's length coercion function that solely represent * increases in

Re: [GENERAL] startup process stuck in recovery

Simon Riggs writes: > On 11 October 2017 at 08:09, Christophe Pettus wrote: >> While it's certainly true that this was an extreme case, it was a real-life >> production situation. The concern here is that in the actual production >> situation, the

Re: [GENERAL] Determine size of table before it's committed?

On Wed, Oct 11, 2017 at 9:43 AM, Seamus Abshere wrote: > hi, > > I've had an `INSERT INTO x SELECT FROM [...]` query running for more > then 2 days. > > Is there a way to see how big x has gotten? Even a very rough estimate > (off by a gigabyte) would be fine. > > Best, >

Re: [GENERAL] Determine size of table before it's committed?

On Wed, Oct 11, 2017 at 10:43:26AM -0300, Seamus Abshere wrote: > I've had an `INSERT INTO x SELECT FROM [...]` query running for more > then 2 days. > > Is there a way to see how big x has gotten? Even a very rough estimate > (off by a gigabyte) would be fine. On linux: Run ps -fu postgres (or

[GENERAL] Determine size of table before it's committed?

hi, I've had an `INSERT INTO x SELECT FROM [...]` query running for more then 2 days. Is there a way to see how big x has gotten? Even a very rough estimate (off by a gigabyte) would be fine. Best, Seamus -- Seamus Abshere, SCEA https://www.faraday.io https://github.com/seamusabshere

Re: [GENERAL] startup process stuck in recovery

On 11 October 2017 at 08:09, Christophe Pettus wrote: > >> On Oct 10, 2017, at 23:54, Simon Riggs wrote: >> >> The use case described seems incredibly >> unreal and certainly amenable to being rewritten. > > While it's certainly true that this was an

[GENERAL] Why does increasing the precision of a numeric column rewrites the table?

When increasing the length constraint on a varchar column, Postgres is smart enough to not rewrite the table. I expected the same thing to be true when increasing the size of a numeric column. However this does not seem to be the case: Consider the following table: create table foo

Re: [GENERAL] startup process stuck in recovery

> On Oct 10, 2017, at 23:54, Simon Riggs wrote: > > The use case described seems incredibly > unreal and certainly amenable to being rewritten. While it's certainly true that this was an extreme case, it was a real-life production situation. The concern here is that in

Re: [GENERAL] startup process stuck in recovery

On 10 October 2017 at 21:23, Tom Lane wrote: > What I see is that, given this particular test case, the backend > process on the master never holds more than a few locks at a time. > Each time we abort a subtransaction, the AE lock it was holding > on the temp table it