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 the actual production situation, the only symptom was that the startup process just stopped. There were no log messages or any other indication of what was going wrong. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 ( some_number numeric(12,2) ); The following statement returns "immediately", regardless of the number of rows in the table alter table foo alter column some_number numeric(15,2); However, when running (on the original table definition) alter table foo alter column some_number numeric(15,3); it takes quite a while (depending on the number of rows) which indicates a table rewrite is taking place. I don't understand why going from numeric(12,2) to numeric(15,3) would require a table rewrite. Thomas -- 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] 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 extreme case, it was a real-life > production situation. The concern here is that in the actual production > situation, the only symptom was that the startup process just stopped. There > were no log messages or any other indication of what was going wrong. Which indicates it was making progress, just slowly. Tom says "This is pretty easy to diagnose though because it spews "out of shared memory" WARNING messages to the postmaster log at an astonishing rate" These don't seem to match. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 https://linkedin.com/in/seamusabshere -- 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] 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 SELECT pid, query FROM pg_stat_activity) and look at: ls -l /proc/PID/fd writing to XX.22 means it's written ~22GB. You can also SELECT relfilenode FROM pg_class WHERE oid='x'::regclass (or relname='x'). Or try using strace (but beware I've seen its interruption to syscalls change the behavior of the program being straced). Justin -- 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] 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, > Seamus > > > -- > Seamus Abshere, SCEA > https://www.faraday.io > https://github.com/seamusabshere > https://linkedin.com/in/seamusabshere > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > >Is there a way to see how big x has gotten?... Try: SELECT n.nspname as schema, c.relname as table, a.rolname as owner, c.relfilenode as filename, c.reltuples::bigint, pg_size_pretty(pg_relation_size(n.nspname|| '.' || c.relname)) as size, pg_size_pretty(pg_total_relation_size(n.nspname|| '.' || c.relname)) as total_size, pg_relation_size(n.nspname|| '.' || c.relname) as size_bytes, pg_total_relation_size(n.nspname|| '.' || c.relname) as total_size_bytes, CASE WHEN c.reltablespace = 0 THEN 'pg_default' ELSE (SELECT t.spcname FROM pg_tablespace t WHERE (t.oid = c.reltablespace) ) END as tablespace FROM pg_class c JOIN pg_namespace n ON (n.oid = c.relnamespace) JOIN pg_authid a ON ( a.oid = c.relowner ) WHERE relname = 'x' ORDER BY total_size_bytes DESC, 1, 2; -- *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
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 only symptom was that the startup process just stopped. >> There were no log messages or any other indication of what was going wrong. > Which indicates it was making progress, just slowly. > Tom says "This is pretty easy to diagnose though > because it spews "out of shared memory" WARNING messages to the > postmaster log at an astonishing rate" > These don't seem to match. Yeah. I'm still suspicious that Christophe saw some other misbehavior than the one I found. We know his server was dealing with < 10K locks, which doesn't seem like enough to cause any obvious problem from a mere O(N^2) behavior. regards, tom lane -- 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] 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 allowable precision. Scale changes mutate every datum, so * they are unoptimizable. Some values, e.g. 1E-1001, can only fit into an * unconstrained numeric, so a change from an unconstrained numeric to any * constrained numeric is also unoptimizable. The issue is basically that changing '1.00' to '1.000' requires a change in the actually-stored value. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[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 other tables) till that one record is resolved. Thanks RV -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- 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] 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 all replication gets backed up (even to other tables) till that one record is resolved. GRANT? JD -- Command Prompt, Inc. || http://the.postgres.company/ || @cmdpromptinc PostgreSQL Centered full stack support, consulting and development. Advocate: @amplifypostgres || Learn: https://pgconf.us * Unless otherwise stated, opinions are my own. * -- 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] 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 list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
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 the column is part of DISTINCT ON. -- 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] 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 through ORDER BY. > > A special case can be allowed for WHERE to pass the ORDER BY if the column is > part of DISTINCT ON. Yeah, we do allow predicates to be pushed down in that case. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] 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 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 the column >> is part of DISTINCT ON. > > Yeah, we do allow predicates to be pushed down in that case. > Let's ignore that it's not a very useful query I have written. 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) QUERY PLAN - Nested Loop Left Join (cost=139.00..10392.96 rows=668 width=16) (actual time=0.528..35.120 rows=200 loops=1) Join Filter: (c.customer_id = product.customer_id) Rows Removed by Join Filter: 199900 -> Nested Loop (cost=0.28..199.21 rows=334 width=12) (actual time=0.075..1.146 rows=100 loops=1) -> Seq Scan on customer (cost=0.00..21.51 rows=334 width=8) (actual time=0.067..0.282 rows=100 loops=1) Filter: (age < 20) Rows Removed by Filter: 901 -> Index Only Scan using customer_pkey on customer c (cost=0.28..0.53 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100) Index Cond: (customer_id = customer.customer_id) Heap Fetches: 100 -> Materialize (cost=138.73..173.75 rows=2001 width=8) (actual time=0.005..0.130 rows=2001 loops=100) -> Sort (cost=138.73..143.73 rows=2001 width=8) (actual time=0.448..0.588 rows=2001 loops=1) Sort Key: product.customer_id, product.product_id Sort Method: quicksort Memory: 142kB -> Seq Scan on product (cost=0.00..29.01 rows=2001 width=8) (actual time=0.006..0.215 rows=2001 loops=1) Planning time: 0.214 ms Execution time: 35.284 ms -- 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] 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) > QUERY PLAN > - > Nested Loop Left Join (cost=139.00..10392.96 rows=668 width=16) (actual > time=0.528..35.120 rows=200 loops=1) > Join Filter: (c.customer_id = product.customer_id) > Rows Removed by Join Filter: 199900 > -> Nested Loop (cost=0.28..199.21 rows=334 width=12) (actual > time=0.075..1.146 rows=100 loops=1) > -> Seq Scan on customer (cost=0.00..21.51 rows=334 width=8) (actual > time=0.067..0.282 rows=100 loops=1) > Filter: (age < 20) > Rows Removed by Filter: 901 > -> Index Only Scan using customer_pkey on customer c > (cost=0.28..0.53 rows=1 width=4) (actual time=0.006..0.006 rows=1 loops=100) > Index Cond: (customer_id = customer.customer_id) > Heap Fetches: 100 > -> Materialize (cost=138.73..173.75 rows=2001 width=8) (actual > time=0.005..0.130 rows=2001 loops=100) > -> Sort (cost=138.73..143.73 rows=2001 width=8) (actual > time=0.448..0.588 rows=2001 loops=1) > Sort Key: product.customer_id, product.product_id > Sort Method: quicksort Memory: 142kB > -> Seq Scan on product (cost=0.00..29.01 rows=2001 width=8) > (actual time=0.006..0.215 rows=2001 loops=1) > Planning time: 0.214 ms > Execution time: 35.284 ms I don't really see any blockers that would mean we couldn't support this, it's just that we don't currently support it. The predicates that we do pushdown are just ones we deem as safe to pushdown of the ones that appear in the query, or ones that can be derived through equivalence. (e.g. ab.a = ab.b and ab.b = 1 --> ab.a = 1) For example, consider the difference between the following: create table ab(a int, b int); insert into ab select x,x from generate_series(1,100)x; create index on ab(a); create index on ab(b); postgres=# explain select * from (select distinct on (a) a,b from ab order by a,b) ab where ab.b < 10; QUERY PLAN --- Subquery Scan on ab (cost=127757.34..145257.34 rows=33 width=8) Filter: (ab.b < 10) -> Unique (cost=127757.34..132757.34 rows=100 width=8) -> Sort (cost=127757.34..130257.34 rows=100 width=8) Sort Key: ab_1.a, ab_1.b -> Seq Scan on ab ab_1 (cost=0.00..14425.00 rows=100 width=8) (6 rows) postgres=# explain select * from (select distinct on (a) a,b from ab order by a,b) ab where ab.a < 10; QUERY PLAN --- Unique (cost=8.73..8.77 rows=9 width=8) -> Sort (cost=8.73..8.75 rows=9 width=8) Sort Key: ab.a, ab.b -> Index Scan using ab_a_idx on ab (cost=0.42..8.58 rows=9 width=8) Index Cond: (a < 10) (5 rows) The "a < 10" was pushed down as we're distinct on (a), but pushing down "ab.b < 10" would be invalid and could cause wrong results. The predicate you'd like to see pushed down is actually a parameter in a parameterized Path and we don't currently generate any parameterized paths outside of each query level. Likely there's no good reason for this other than it's not been done yet, but it's really only been since 9.6 that the query planner has been flexible enough to possibly allow something like this to be done at all. The reason the planner may appear to push down the predicate when there's no DISTINCT ON clause is that the planner was able to pull the subquery (or view) up a level. When the planner is able to do this it's much more flexible to the types of plans it can generate. It's just that we don't ever pull up subqueries with DISTINCT ON, plus a bunch of other reasons. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] 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 ObjectSymbol > # . > # > 86.96% postgres [kernel.kallsyms] [k] __mutex_lock_common.isra.5 > | > --- __mutex_lock_common.isra.5 > read Unfortunately it looks like you're using a postgres built with -fomit-frame-pointers (the default) on x64, with an older perf not built with libunwind. This produces useless stacks. You may need to recompile with -fno-omit-frame-pointer -- 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] 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 > > # > > # Overhead Command Shared ObjectSymbol > > # . > > # > > 86.96% postgres [kernel.kallsyms] [k] __mutex_lock_common.isra.5 > > | > > --- __mutex_lock_common.isra.5 > > read > > > Unfortunately it looks like you're using a postgres built with > -fomit-frame-pointers (the default) on x64, with an older perf not > built with libunwind. This produces useless stacks. Just read this mail, but for libunwind to work you'd have to specify "--call-graph dwarf", no? - Andres -- 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] 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: >> >> >> >> >> >> Can you get stacks please? >> >> >> >> Use -g >> > >> > >> > # Events: 2K cpu-clock >> > # >> > # Overhead Command Shared ObjectSymbol >> > # . >> > # >> > 86.96% postgres [kernel.kallsyms] [k] __mutex_lock_common.isra.5 >> > | >> > --- __mutex_lock_common.isra.5 >> > read >> >> >> Unfortunately it looks like you're using a postgres built with >> -fomit-frame-pointers (the default) on x64, with an older perf not >> built with libunwind. This produces useless stacks. > > Just read this mail, but for libunwind to work you'd have to specify > "--call-graph dwarf", no? I think you're right. But only on a version of perf where it's available and used. I haven't recently checked if perf has finally grown the ability to load external debug symbols either. It never used to. -- Craig Ringer http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
