Re: adding partitioned tables to publications

On Mon, Oct 7, 2019 at 9:55 AM Amit Langote wrote: > One cannot currently add partitioned tables to a publication. > > create table p (a int, b int) partition by hash (a); > create table p1 partition of p for values with (modulus 3, remainder 0); > create table p2 partition of p for values with

Re: maintenance_work_mem used by Vacuum

On Thu, Oct 10, 2019 at 9:58 AM Masahiko Sawada wrote: > > On Wed, Oct 9, 2019 at 7:12 PM Dilip Kumar wrote: > > > > I think the current situation is not good but if we try to cap it to > > maintenance_work_mem + gin_*_work_mem then also I don't think it will > > make the situation much better.

generating catcache control data

Hi, While digging through the archives, I found a thread from a couple years back about syscache performance. There was an idea [1] to generate the cache control data at compile time. That would to remove the need to perform database access to complete cache initialization, as well as the need to

Re: [HACKERS] Block level parallel vacuum

On Thu, Oct 10, 2019 at 2:19 PM Amit Kapila wrote: > > On Fri, Oct 4, 2019 at 4:18 PM Amit Kapila wrote: > > > > On Wed, Oct 2, 2019 at 7:29 PM Masahiko Sawada > > wrote: > >> > > Few more comments: Thank you for reviewing the patch! > - > 1. Caurrently

Re: [PATCH] Do not use StdRdOptions in Access Methods

Hello Nikolay, I read comments that Tomas left at: https://www.postgresql.org/message-id/20190727173841.7ypzo4xuzizvijge%40development I'd like to join Michael in reiterating one point from Tomas' review. I think the patch can go further in trying to make the code in this area more maintainable.

Re: pgbench - extend initialization phase control

Attached v2 is a rebase after ce8f9467. Here is rebase v3. -- Fabien.diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml index e3a0abb4c7..e9f43f3b26 100644 --- a/doc/src/sgml/ref/pgbench.sgml +++ b/doc/src/sgml/ref/pgbench.sgml @@ -167,7 +167,7 @@ pgbench options d

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Thu, Oct 3, 2019 at 1:18 PM Dilip Kumar wrote: > > I have attempted to test the performance of (Stream + Spill) vs > (Stream + BGW pool) and I can see the similar gain what Alexey had > shown[1]. > > In addition to this, I have rebased the latest patchset [2] without > the two-phase logical

Re: dropping column prevented due to inherited index

On Thu, Oct 10, 2019 at 4:53 PM Michael Paquier wrote: > On Thu, Oct 10, 2019 at 02:56:32PM +0900, Amit Langote wrote: > > /* Initialize addrs on the first invocation. */ > > I would add "recursive" here, to give: > /* Initialize addrs on the first recursive invocation. */ Actually, the code

Re: dropping column prevented due to inherited index

On Thu, Oct 10, 2019 at 02:56:32PM +0900, Amit Langote wrote: > /* > * Drops column 'colName' from relation 'rel' and returns the address of the > * dropped column. The column is also dropped (or marked as no longer > * inherited from relation) from the relation's inheritance children, if any.

Re: maintenance_work_mem used by Vacuum

On Thu, Oct 10, 2019 at 3:36 PM Amit Kapila wrote: > > On Thu, Oct 10, 2019 at 9:58 AM Masahiko Sawada wrote: > > > > On Wed, Oct 9, 2019 at 7:12 PM Dilip Kumar wrote: > > > > > > I think the current situation is not good but if we try to cap it to > > > maintenance_work_mem + gin_*_work_mem

Re: [PATCH] use separate PartitionedRelOptions structure to store partitioned table options

Hello, On Sun, Oct 6, 2019 at 9:48 PM Nikolay Shaplov wrote: > This message is follow up to the "Get rid of the StdRdOptions" patch thread: > https://www.postgresql.org/message-id/2620882.s52SJui4ql@x200m > > I've split patch into even smaller parts and commitfest want each patch in > separate

Re: maintenance_work_mem used by Vacuum

On Thu, Oct 10, 2019 at 2:10 PM Masahiko Sawada wrote: > > On Thu, Oct 10, 2019 at 3:36 PM Amit Kapila wrote: > > > > On Thu, Oct 10, 2019 at 9:58 AM Masahiko Sawada > > wrote: > > > > > > On Wed, Oct 9, 2019 at 7:12 PM Dilip Kumar wrote: > > > > > > > > I think the current situation is not

Re: use of the term "verifier" with SCRAM

On 2019-08-14 10:41, Heikki Linnakangas wrote: >> RFC 5803 is titled "Lightweight Directory Access Protocol (LDAP) Schema >> for Storing Salted Challenge Response Authentication Mechanism (SCRAM) >> Secrets". Following that, I think calling the contents of rolpassword a >> "secret" or a "stored

Re: use of the term "verifier" with SCRAM

On Thu, Oct 10, 2019 at 09:08:37AM +0200, Peter Eisentraut wrote: > Here is my proposed patch to adjust this. Looks fine to me reading through. I think that you are right to not change the descriptions in build_server_final_message(), as that's described similarly in RFC 5802. By renaming

Re: [HACKERS] proposal: schema variables

h rebase > > Regards > > Pavel > > >> Regards >> >> Pavel >> > schema_variables-20191010.patch.gz Description: application/gzip

Logical replication dead but synching

Hello, While giving assistance to some customer with their broker procedure, I found a scenario where the subscription is failing but the table are sync'ed anyway. Here is bash script to reproduce it with versions 10, 11 and 12 (make sure to set PATH correctly): # env PUB=/tmp/pub

Re: abort-time portal cleanup

On Wed, Oct 9, 2019 at 6:56 PM Robert Haas wrote: > > On Tue, Oct 8, 2019 at 2:10 PM Andres Freund wrote: > > On 2019-10-07 12:14:52 -0400, Robert Haas wrote: > > > > - if (portal->status == PORTAL_READY) > > > > - MarkPortalFailed(portal); > > > > > > > > Why it is safe to remove this check?

Compressed pluggable storage experiments

Hi hackers, I've been experimenting with pluggable storage API recently and just feel like I can share my first experience. First of all it's great to have this API and that now community has the opportunity to implement alternative storage engines. There are a few applications that come to mind

Re: adding partitioned tables to publications

On Thu, 10 Oct 2019 at 08:29, Amit Langote wrote: > On Mon, Oct 7, 2019 at 9:55 AM Amit Langote > wrote: > > One cannot currently add partitioned tables to a publication. > > > > create table p (a int, b int) partition by hash (a); > > create table p1 partition of p for values with (modulus 3,

Re: Remove size limitations of vacuums dead_tuples array

On Wed, Oct 09, 2019 at 03:58:11PM +0300, Ants Aasma wrote: When dealing with a case where a 2TB table had 3 billion dead tuples I discovered that vacuum currently can't make use of more than 1GB of maintenance_work_mem - 179M tuples. This caused excessive amounts of index scanning even though

Re: configure fails for perl check on CentOS8

On 10/10/19 1:46 AM, Kyotaro Horiguchi wrote: > Hello, While I'm moving to CentOS8 environment, I got stuck at > ./configure with the following error. > > configure: error: libperl library is requred for Perl > > It complains that it needs -fPIC. > > Configure uses only $Config{ccflags}, but it

Re: Transparent Data Encryption (TDE) and encrypted files

Greetings, * Craig Ringer (cr...@2ndquadrant.com) wrote: > On Wed, 9 Oct 2019 at 22:30, Stephen Frost wrote: > > - All decryption happens in a given backend when it's sending data to > > the client > > That is not what I think of as TDE. But upon review, it looks like I'm > wrong, and the

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

Hi, On 2019-10-09 16:29:07 -0400, Dave Cramer wrote: > I've added functionality into libpq to be able to set this STARTUP > parameter as well as changed it to _pq_.report. > Still need to document this and figure out how to test it. > From 85de9f48f80a3bfd9e8bdd4f1ba6b177b1ff9749 Mon Sep 17

Re: pg_dump compatibility level / use create view instead of create table/rule

Alex Williams writes: > [ gripes about pg_dump printing REPLICA IDENTITY NOTHING for a view ] I spent a little bit of time trying to reproduce this, and indeed I can, in versions before v10. regression=# create table mytab (f1 int primary key, f2 text); CREATE TABLE regression=# create view

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

Hi, On 2019-10-08 23:57:24 +0800, Craig Ringer wrote: > In other places I've (ab)used GUC_REPORT to push information back to the > client as a workaround for the lack of protocol extensibility / custom > messages. It's a little less ugly than abusing NOTICE messages. I'd prefer > a real way to

Re: configure fails for perl check on CentOS8

Andrew Dunstan writes: > On 10/10/19 1:46 AM, Kyotaro Horiguchi wrote: >> Hello, While I'm moving to CentOS8 environment, I got stuck at >> ./configure with the following error. >> configure: error: libperl library is requred for Perl >> It complains that it needs -fPIC. >> Configure uses only

Re: generating catcache control data

John Naylor writes: > While digging through the archives, I found a thread from a couple > years back about syscache performance. There was an idea [1] to > generate the cache control data at compile time. That would to remove > the need to perform database access to complete cache

Re: BTP_DELETED leaf still in tree

On Thu, Oct 10, 2019 at 12:48 PM Daniel Wood wrote: > Update query stuck in a loop. Looping in _bt_moveright(). You didn't say which PostgreSQL versions were involved, and if the database was ever upgraded using pg_upgrade. Those details could matter. >

Re: stress test for parallel workers

I wrote: >>> Yeah, I've been wondering whether pg_ctl could fork off a subprocess >>> that would fork the postmaster, wait for the postmaster to exit, and then >>> report the exit status. > [ pushed at 6a5084eed ] > Given wobbegong's recent failure rate, I don't think we'll have to wait > long.

Re: stress test for parallel workers

On Thu, Oct 10, 2019 at 05:34:51PM -0400, Tom Lane wrote: > A nearer-term solution would be to reproduce this manually and > dig into the core. Mark, are you in a position to give somebody > ssh access to wobbegong's host, or another similarly-configured VM? > > (While at it, it'd be nice to

Re: BRIN index which is much faster never chosen by planner

Since the optimizer is choosing a seq scan over index scan when it seems like it has good row estimates in both cases, to me that may mean costs of scanning index are expected to be high. Is this workload on SSD? Has the random_page_cost config been decreased from default 4 (compared with cost of

Re: BTP_DELETED leaf still in tree

> On October 10, 2019 at 1:18 PM Peter Geoghegan wrote: > > > On Thu, Oct 10, 2019 at 12:48 PM Daniel Wood wrote: > > Update query stuck in a loop. Looping in _bt_moveright(). > > You didn't say which PostgreSQL versions were involved, and if the > database was ever upgraded using

Re: BRIN index which is much faster never chosen by planner

On Fri, 11 Oct 2019 at 12:13, Tomas Vondra wrote: > The index scan is estimated to return 157328135 rows, i.e. about 50% of > the table (apparently it's ~10x more than the actual number). Don't pay too much attention to the actual row counts from bitmap index scans of brin indexes. The value is

BRIN index which is much faster never chosen by planner

Good Afternoon, I posted about this on another thread here , but the topic was not precisely planner issues, so I wanted to post it here. I am running Postgres 11.5. I have a table that is

Re: stress test for parallel workers

On 10/10/19 5:34 PM, Tom Lane wrote: > I wrote: Yeah, I've been wondering whether pg_ctl could fork off a subprocess that would fork the postmaster, wait for the postmaster to exit, and then report the exit status. >> [ pushed at 6a5084eed ] >> Given wobbegong's recent failure

Re: BRIN index which is much faster never chosen by planner

On Thu, Oct 10, 2019 at 04:58:11PM -0500, Jeremy Finzel wrote: ... Notice it chooses the smallest BRIN index with 1000 pages per range, and this is far faster than the seq scan. I do believe the estimate is actually way off. Just a plain EXPLAIN of the latter estimates 10x more rows than

Re: BTP_DELETED leaf still in tree

On Thu, Oct 10, 2019 at 1:18 PM Peter Geoghegan wrote: > You didn't say which PostgreSQL versions were involved, and if the > database was ever upgraded using pg_upgrade. Those details could > matter. In case you weren't aware, contrib/amcheck should make detected and diagnosing these kinds of

Re: pgsql: Remove pqsignal() from libpq's official exports list.

Stephen Frost writes: > Yes, this is absolutely the right answer, we shouldn't be removing > symbols without an SONAME bump. If we don't want to bump the SONAME, > then don't remove the symbol. OK, done. regards, tom lane

Re: generating catcache control data

... BTW, one other issue with changing this, at least if we want to precompute tupdescs for all system catalogs used in catcaches, is that that would put a very big crimp in doing runtime changes to catalogs. While we'll probably never support changes in the physical layouts of catalog rows, there

Re: let's make the list of reportable GUCs configurable (was Re: Add %r substitution for psql prompts to show recovery status)

On Thu, 10 Oct 2019 at 23:45, Andres Freund wrote: > > Unless schema qualified you can't rely on that even without search_path > changing. Consider an object in schema b existing, with a search_path of > a,b. Even without the search path changing, somebody could create a type > in a, "masking"

Re: BRIN index which is much faster never chosen by planner

On Thu, Oct 10, 2019 at 6:22 PM David Rowley wrote: > The planner might be able to get a better estimate on the number of > matching rows if the now() - interval '10 days' expression was > replaced with 'now'::timestamptz - interval '10 days'. However, care > would need to be taken to ensure the

Re: maintenance_work_mem used by Vacuum

On Thu, Oct 10, 2019 at 6:38 PM Amit Kapila wrote: > > On Thu, Oct 10, 2019 at 2:10 PM Masahiko Sawada wrote: > > > > On Thu, Oct 10, 2019 at 3:36 PM Amit Kapila wrote: > > > > > > On Thu, Oct 10, 2019 at 9:58 AM Masahiko Sawada > > > wrote: > > > > > > > > On Wed, Oct 9, 2019 at 7:12 PM