Re: Implementing Incremental View Maintenance

On Mon, 26 Apr 2021 15:46:21 +0900 Yugo NAGATA wrote: > On Tue, 20 Apr 2021 09:51:34 +0900 > Yugo NAGATA wrote: > > > On Mon, 19 Apr 2021 17:40:31 -0400 > > Tom Lane wrote: > > > > > Andrew Dunstan writes: > > > > This patch (v22c) just crashed for me with an assertion failure on > > > > Fed

Re: Truncate in synchronous logical replication failed

On Mon, 26 Apr 2021 at 12:49, Amit Kapila wrote: > On Fri, Apr 23, 2021 at 7:18 PM osumi.takami...@fujitsu.com > wrote: >> > > The latest patch looks good to me. I have made a minor modification > and added a commit message in the attached. I would like to once again > ask whether anybody else

Re: [BUG] "FailedAssertion" reported when streaming in logical replication

On Mon, 26 Apr 2021 at 12:45 PM, tanghy.f...@fujitsu.com < tanghy.f...@fujitsu.com> wrote: > Hi > > I think I may found a bug when using streaming in logical replication. > Could anyone please take a look at this? > > Here's what I did to produce the problem. > I set logical_decoding_work_mem and

Re: [PATCH] add concurrent_abort callback for output plugin

On Mon, Apr 26, 2021 at 7:05 AM Peter Smith wrote: > > Hi, > > While testing another WIP patch [1] a clashing GID problem was found, > which gives us apply worker errors like: > > 2021-04-26 10:07:12.883 AEST [22055] ERROR: transaction identifier > "pg_gid_16403_608" is already in use > 2021-04-2

Re: [PATCH] Re: pg_identify_object_as_address() doesn't support pg_event_trigger oids

On Fri, Apr 23, 2021 at 09:33:36AM +0200, Joel Jacobson wrote: > Also, since this is a problem also in v13 maybe this should also be > back-ported? I think it's a bug since both > pg_identify_object_as_address() and event triggers exists in v13, so > the function should work there as well, otherwi

Re: Performance Evaluation of Result Cache by using TPC-DS

Hello David, Thank you for running experiments on your machine and I really appreciate your deep analysis. Your results are very interesting. In 5 queries, the result cache is cheaper but slower. Especially, in query 88, although the cost with result cache is cheaper, it has 34.23% degradation in

Re: Table refer leak in logical replication

On Mon, Apr 26, 2021 at 3:27 PM Michael Paquier wrote: > On Fri, Apr 23, 2021 at 09:38:01PM +0900, Amit Langote wrote: > > On Thu, Apr 22, 2021 at 1:45 PM Michael Paquier wrote: > >> On the other hand, the tests for partitions have much more value IMO, > >> but looking closely I think that we can

Re: Addition of authenticated ID to pg_stat_activity

On Sun, Apr 25, 2021 at 10:14:43PM -0500, Justin Pryzby wrote: > That part looks to be a copy+paste error. Sorry about that. I have fixed that on my own branch. >> + >> pg_stat_activity.authenticated_id >> field. >> + If this value is specified without units, it is taken as bytes.

Re: Parallel INSERT SELECT take 2

On Mon, Apr 26, 2021 at 7:00 AM houzj.f...@fujitsu.com wrote: > > > > Based on above, we plan to move forward with the apporache 2) (declarative > > idea). > > > > IIUC, the declarative behaviour idea attributes parallel > > safe/unsafe/restricted > > tags to each table with default being the uns

Re: Asynchronous Append on postgres_fdw nodes.

On 4/23/21 8:12 AM, Etsuro Fujita wrote: I have committed the patch. Small mistake i found. If no tuple was received from a foreign partition, explain shows that we never executed node. For example, if we have 0 tuples in f1 and 100 tuples in f2: Query: EXPLAIN (ANALYZE, VERBOSE, TIMING OFF, C

Re: [HACKERS] Cached plans and statement generalization

вс, 1 мар. 2020 г. в 22:26, Tom Lane : > > Konstantin Knizhnik writes: > > [ autoprepare-extended-4.patch ] > > The cfbot is showing that this doesn't apply anymore; there's > some doubtless-trivial conflict in prepare.c. > > However ... TBH I've been skeptical of this whole proposal from the > be

Re: PATCH: generate fractional cheapest paths in generate_orderedappend_path

Hi, thanks for looking into it! For some reason the patch doesn't apply at my end, could you repost one based at the master? > 1) If get_cheapest_fractional_path_for_pathkeys returns NULL, it's not > clear whether to default to cheapest_startup or cheapest_total. We might > also consider an

Re: [HACKERS] logical decoding of two-phase transactions

On Wed, Apr 21, 2021 at 12:13 PM Peter Smith wrote: > > On Tue, Apr 20, 2021 at 3:45 PM Peter Smith wrote: > > > > Please find attached the latest patch set v73`* > > > > Differences from v72* are: > > > > * Rebased to HEAD @ today (required because v72-0001 no longer applied > > cleanly) > > >

RE: Parallel INSERT SELECT take 2

> > The parallel attributes in table means the parallel safety when user does > > some > data-modification operations on it. > > So, It only limit the use of parallel plan when using INSERT/UPDATE/DELETE. > > In that case, isn't it better to use the terminology "PARALLEL DML > SAFE/UNSAFE/RESTRIC

Enhanced error message to include hint messages for redundant options error

Hi, While reviewing one of the logical replication patches, I found that we do not include hint messages to display the actual option which has been specified more than once in case of redundant option error. I felt including this will help in easily identifying the error, users will not have to s

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Hi Alvaro, On Sat, Apr 24, 2021 at 8:31 AM Alvaro Herrera wrote: > On 2021-Apr-23, Alvaro Herrera wrote: > > I think the patch I posted was too simple. I think a real fix requires > > us to keep track of exactly in what way the partdesc is outdated, so > > that we can compare to the current situ

Re: Dumping/restoring fails on inherited generated column

On 05.02.21 15:18, Peter Eisentraut wrote: Anyway, I figured out how to take account of generation expressions with different column orders.  I used the same approach that we use for check constraints.  The attached patch is good to go from my perspective. Dusting this off ... this patch shoul

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 5:29 PM vignesh C wrote: > > Hi, > > While reviewing one of the logical replication patches, I found that > we do not include hint messages to display the actual option which has > been specified more than once in case of redundant option error. I > felt including this will

Re: [BUG] "FailedAssertion" reported when streaming in logical replication

On Mon, Apr 26, 2021 at 1:26 PM Dilip Kumar wrote: > > On Mon, 26 Apr 2021 at 12:45 PM, tanghy.f...@fujitsu.com > wrote: >> >> Hi >> >> I think I may found a bug when using streaming in logical replication. Could >> anyone please take a look at this? >> >> Here's what I did to produce the probl

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Hello Amit, On 2021-Apr-26, Amit Langote wrote: > On Sat, Apr 24, 2021 at 8:31 AM Alvaro Herrera > wrote: > > I haven't added a mechanism to verify this; but with asserts on, this > > patch will crash if you have more than one. I think the behavior is not > > necessarily sane with asserts off

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 5:49 PM Bharath Rupireddy wrote: > > On Mon, Apr 26, 2021 at 5:29 PM vignesh C wrote: > > > > Hi, > > > > While reviewing one of the logical replication patches, I found that > > we do not include hint messages to display the actual option which has > > been specified more

Re: [BUG] "FailedAssertion" reported when streaming in logical replication

On Mon, Apr 26, 2021 at 5:55 PM Dilip Kumar wrote: > > On Mon, Apr 26, 2021 at 1:26 PM Dilip Kumar wrote: > > > > On Mon, 26 Apr 2021 at 12:45 PM, tanghy.f...@fujitsu.com > > wrote: > >> > >> Hi > >> > >> I think I may found a bug when using streaming in logical replication. > >> Could anyone

Re: Performance degradation of REFRESH MATERIALIZED VIEW

Hi, I took a look at this today, as I committed 39b66a91b back in January. I can reproduce the issue, with just 1M rows the before/after timings are roughly 480ms and 620ms on my hardware. Unfortunately, the v3 patch does not really fix the issue for me. The timing with it applied is ~610ms

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 5:49 PM Bharath Rupireddy wrote: > > On Mon, Apr 26, 2021 at 5:29 PM vignesh C wrote: > > > > Hi, > > > > While reviewing one of the logical replication patches, I found that > > we do not include hint messages to display the actual option which has > > been specified more

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 6:18 PM Dilip Kumar wrote: > > On Mon, Apr 26, 2021 at 5:49 PM Bharath Rupireddy > wrote: > > > > On Mon, Apr 26, 2021 at 5:29 PM vignesh C wrote: > > > > > > Hi, > > > > > > While reviewing one of the logical replication patches, I found that > > > we do not include hint

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 7:02 PM vignesh C wrote: > > On Mon, Apr 26, 2021 at 5:49 PM Bharath Rupireddy > wrote: > > > > On Mon, Apr 26, 2021 at 5:29 PM vignesh C wrote: > > > > > > Hi, > > > > > > While reviewing one of the logical replication patches, I found that > > > we do not include hint m

RE: Support tab completion for upper character inputs in psql

Hi I've updated the patch to V7 based on the following comments. On Friday, April 23, 2021 11:58 AM, Kyotaro Horiguchi wrote >All usages of pg_string_tolower don't need a copy. >So don't we change the function to in-place converter? Refer to your later discussion with Tom. Keep the code as i

RE: use pg_strncasecmp to replace strncmp when compare "pg_"

On Friday, April 23, 2021 2:06 PM, Tom Lane wrote >>Kyotaro Horiguchi writes: >> That doesn't matter at all for now since we match schema identifiers >> case-sensitively. Maybe it should be a part of the patch in [1]. > >Yeah --- maybe this'd make sense as part of a full patch to improve >tab-c

Re: How to test Postgres for any unaligned memory accesses?

On Fri, Apr 23, 2021 at 7:25 PM Tom Lane wrote: > > I'm not sure this is the right way. I would like to know whether there > > is a standard way of testing Postgres code for any unaligned memory > > accesses. Thanks. Any help would be appreciated. > > Per c.h, late-model compilers have options for

Re: Does rewriteTargetListIU still need to add UPDATE tlist entries?

Amit Langote writes: > On Mon, Apr 26, 2021 at 9:40 AM Tom Lane wrote: >> I would think that this is a totally straightforward improvement, >> but there's one thing in the comments for rewriteTargetListIU that >> gives me a little pause: it says >> >> * We must do items 1,2,3 before firing rewri

Re: [BUG] "FailedAssertion" reported when streaming in logical replication

On Mon, Apr 26, 2021 at 6:59 PM Amit Kapila wrote: > > On Mon, Apr 26, 2021 at 5:55 PM Dilip Kumar wrote: > > > > I am able to reproduce this and I think I have done the initial > > investigation. > > > > The cause of the issue is that, this transaction has only one change > > and that change is

Re: tab-complete for ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On Thu, Apr 22, 2021 at 04:40:35PM -0400, Alvaro Herrera wrote: > Would anyone oppose me pushing this for tab-completing the new keywords > of ALTER TABLE .. DETACH PARTITION? +1 to apply tab completion for v14 -- Justin

Re: Does rewriteTargetListIU still need to add UPDATE tlist entries?

On Mon, 26 Apr 2021 at 15:09, Tom Lane wrote: > > Thanks for looking at that. On reflection I think this must be so, > because those rewriter mechanisms were designed long before we had > trigger-updatable views, and rewriteTargetListIU has never added > tlist items like this for any other sort o

Re: Enhanced error message to include hint messages for redundant options error

On 2021-Apr-26, Bharath Rupireddy wrote: > Thanks! IMO, it is better to change the error message to "option > \"%s\" specified more than once" instead of adding an error detail. > Let's hear other hackers' opinions. Many other places have the message "conflicting or redundant options", and then p

Re: compute_query_id and pg_stat_statements

On 24.04.21 19:43, Tom Lane wrote: Bruce Momjian writes: That's a pretty weird API. I think we just need people to turn it on like they are doing when the configure pg_stat_statements anyway. pg_stat_statements already requires configuration anyway. Agreed. If pg_stat_statements were zero-c

Re: Does rewriteTargetListIU still need to add UPDATE tlist entries?

Dean Rasheed writes: > On Mon, 26 Apr 2021 at 15:09, Tom Lane wrote: >> Thanks for looking at that. On reflection I think this must be so, >> because those rewriter mechanisms were designed long before we had >> trigger-updatable views, and rewriteTargetListIU has never added >> tlist items like

Issue in recent pg_stat_statements?

-hackers, So in doing some recent work on pg_stat_statements, I notice that while the regression test still passes on HEAD, it appears that 4f0b096 (per git bisect) changed/broke how this works compared to historical versions. Essentially, when doing a fresh install of pg_stat_statements on a new

Re: Issue in recent pg_stat_statements?

On Mon, Apr 26, 2021 at 5:15 PM David Christensen wrote: > > -hackers, > > So in doing some recent work on pg_stat_statements, I notice that while the > regression test still passes on HEAD, it appears that 4f0b096 (per git > bisect) changed/broke how this works compared to historical versions.

Re: compute_query_id and pg_stat_statements

Re: Peter Eisentraut > > Agreed. If pg_stat_statements were zero-configuration today then > > this would be an annoying new burden, but it isn't. > > I think people can understand "add pg_stat_statements to > shared_preload_libraries" and "install the extension". You have to turn it > on somehow

Re: Issue in recent pg_stat_statements?

> > > Is this an expected change, or is this in fact broken? In previous > revisions, this was showing the INSERT and SELECT at the very least. I'm > unclear as to why the regression test is still passing, so want to verify > that I'm not doing something wrong in the testing. > > Yes, you want to

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 8:06 PM Alvaro Herrera wrote: > > On 2021-Apr-26, Bharath Rupireddy wrote: > > > Thanks! IMO, it is better to change the error message to "option > > \"%s\" specified more than once" instead of adding an error detail. > > Let's hear other hackers' opinions. > > Many other p

Re: Enhanced error message to include hint messages for redundant options error

On 2021-Apr-26, Bharath Rupireddy wrote: > I agree that we can just be clear about the problem. Looks like the > majority of the errors "conflicting or redundant options" are for > redundant options. So, wherever "conflicting or redundant options" > exists: 1) change the message to "option \"%s\"

Re: compute_query_id and pg_stat_statements

On Mon, Apr 26, 2021 at 05:34:30PM +0200, Christoph Berg wrote: > Re: Peter Eisentraut > > > Agreed. If pg_stat_statements were zero-configuration today then > > > this would be an annoying new burden, but it isn't. > > > > I think people can understand "add pg_stat_statements to > > shared_prelo

Re: compute_query_id and pg_stat_statements

Greetings, * Bruce Momjian (br...@momjian.us) wrote: > On Mon, Apr 26, 2021 at 05:34:30PM +0200, Christoph Berg wrote: > > Re: Peter Eisentraut > > > > Agreed. If pg_stat_statements were zero-configuration today then > > > > this would be an annoying new burden, but it isn't. > > > > > > I think

Re: Does rewriteTargetListIU still need to add UPDATE tlist entries?

On Mon, 26 Apr 2021 at 15:55, Tom Lane wrote: > > I checked into the commit history (how'd we ever survive without "git > blame"?) and found that my argument above is actually wrong in detail. > Before cab5dc5da of 2013-10-18, rewriteTargetListIU expanded non-updated > columns for all views not on

Re: compute_query_id and pg_stat_statements

Stephen Frost writes: > * Bruce Momjian (br...@momjian.us) wrote: >> Techically, pg_stat_statements can turn on compute_query_id when it is >> loaded, even if it is 'off' in postgresql.conf, right? And >> pg_stat_statements would know if an alternate hash method is being used, >> right? > +1 on

ERROR: relation "sql_features" does not exist

Hi I tried to write a query that does lateral join between information_schema.tables and pgstattuple function. select * from information_schema.tables, lateral(select * from pgstattuple(table_name::name)) s where table_type = 'BASE TABLE'; The query finished by strange error postgres=# select *

Re: compute_query_id and pg_stat_statements

On Mon, Apr 26, 2021 at 12:56:13PM -0400, Tom Lane wrote: > Stephen Frost writes: > > * Bruce Momjian (br...@momjian.us) wrote: > >> Techically, pg_stat_statements can turn on compute_query_id when it is > >> loaded, even if it is 'off' in postgresql.conf, right? And > >> pg_stat_statements would

Re: Synchronous commit behavior during network outage

Hello Andrey, I went through the thread for your patch and seems to me as an acceptable solution... > The only case patch does not handle is sudden backend crash - Postgres will recover without a restart. We also use a HA tool (Patroni). If the whole machine fails, it will find a new maste

Re: compute_query_id and pg_stat_statements

On Tue, Apr 27, 2021 at 12:56 AM Tom Lane wrote: > > Stephen Frost writes: > > * Bruce Momjian (br...@momjian.us) wrote: > >> Techically, pg_stat_statements can turn on compute_query_id when it is > >> loaded, even if it is 'off' in postgresql.conf, right? And > >> pg_stat_statements would know

Re: compute_query_id and pg_stat_statements

On Mon, Apr 26, 2021 at 6:56 PM Tom Lane wrote: > > Stephen Frost writes: > > * Bruce Momjian (br...@momjian.us) wrote: > >> Techically, pg_stat_statements can turn on compute_query_id when it is > >> loaded, even if it is 'off' in postgresql.conf, right? And > >> pg_stat_statements would know i

Re: compute_query_id and pg_stat_statements

On Mon, Apr 26, 2021 at 7:00 PM Bruce Momjian wrote: > > On Mon, Apr 26, 2021 at 12:56:13PM -0400, Tom Lane wrote: > > Stephen Frost writes: > > > * Bruce Momjian (br...@momjian.us) wrote: > > >> Techically, pg_stat_statements can turn on compute_query_id when it is > > >> loaded, even if it is '

Re: ERROR: relation "sql_features" does not exist

Pavel Stehule writes: > I tried to write a query that does lateral join between > information_schema.tables and pgstattuple function. > select * from information_schema.tables, lateral(select * from > pgstattuple(table_name::name)) s where table_type = 'BASE TABLE'; > The query finished by stran

Re: compute_query_id and pg_stat_statements

On Tue, Apr 27, 2021 at 1:04 AM Magnus Hagander wrote: > > Thatäs why I suggested the three value one. Default to a mode where > it's automatic, which is what the majority is going to want, but have > a way to explicitly turn it on. Agreed, that also sounds like a sensible default.

Re: ERROR: relation "sql_features" does not exist

po 26. 4. 2021 v 19:10 odesílatel Tom Lane napsal: > Pavel Stehule writes: > > I tried to write a query that does lateral join between > > information_schema.tables and pgstattuple function. > > > select * from information_schema.tables, lateral(select * from > > pgstattuple(table_name::name)) s

Re: Issue in recent pg_stat_statements?

On Mon, Apr 26, 2021 at 11:40 PM David Christensen wrote: >> >> > Is this an expected change, or is this in fact broken? In previous >> > revisions, this was showing the INSERT and SELECT at the very least. I'm >> > unclear as to why the regression test is still passing, so want to verify >>

Re: compute_query_id and pg_stat_statements

Greetings, * Magnus Hagander (mag...@hagander.net) wrote: > On Mon, Apr 26, 2021 at 6:56 PM Tom Lane wrote: > > Stephen Frost writes: > > > * Bruce Momjian (br...@momjian.us) wrote: > > >> Techically, pg_stat_statements can turn on compute_query_id when it is > > >> loaded, even if it is 'off' i

Re: compute_query_id and pg_stat_statements

Stephen Frost writes: > * Magnus Hagander (mag...@hagander.net) wrote: >> Thatäs why I suggested the three value one. Default to a mode where >> it's automatic, which is what the majority is going to want, but have >> a way to explicitly turn it on. > This is certainly fine with me too, though it

Re: compute_query_id and pg_stat_statements

Greetings, * Tom Lane (t...@sss.pgh.pa.us) wrote: > Stephen Frost writes: > > * Magnus Hagander (mag...@hagander.net) wrote: > >> Thatäs why I suggested the three value one. Default to a mode where > >> it's automatic, which is what the majority is going to want, but have > >> a way to explicitly

Re: compute_query_id and pg_stat_statements

On 2021-Apr-26, Tom Lane wrote: > Stephen Frost writes: > > * Magnus Hagander (mag...@hagander.net) wrote: > >> Thatäs why I suggested the three value one. Default to a mode where > >> it's automatic, which is what the majority is going to want, but have > >> a way to explicitly turn it on. > >

Re: compute_query_id and pg_stat_statements

Greetings, * Alvaro Herrera (alvhe...@alvh.no-ip.org) wrote: > On 2021-Apr-26, Tom Lane wrote: > > > Stephen Frost writes: > > > * Magnus Hagander (mag...@hagander.net) wrote: > > >> Thatäs why I suggested the three value one. Default to a mode where > > >> it's automatic, which is what the majo

Re: pg_amcheck contrib application

> On Apr 23, 2021, at 3:01 PM, Mark Dilger wrote: > > I'll try to post something that accomplishes the changes to the reports that > you are looking for. The attached patch changes amcheck corruption reports as discussed upthread. This patch is submitted for the v14 development cycle as a b

Re: Issue in recent pg_stat_statements?

On Mon, Apr 26, 2021 at 12:18 PM Julien Rouhaud wrote: > On Mon, Apr 26, 2021 at 11:40 PM David Christensen > wrote: > >> > >> > Is this an expected change, or is this in fact broken? In previous > revisions, this was showing the INSERT and SELECT at the very least. I'm > unclear as to why the

Re: Issue in recent pg_stat_statements?

On 2021-04-26 12:53:30 -0500, David Christensen wrote: > On Mon, Apr 26, 2021 at 12:18 PM Julien Rouhaud wrote: > > Using pg_stat_statements with a different query_id semantics without > > having to fork pg_stat_statements. > > > > I can see that argument for allowing alternatives, but the curren

Re: compute_query_id and pg_stat_statements

Hi, On 2021-04-26 13:43:31 -0400, Alvaro Herrera wrote: > I think it's straightforward, if we decouple the tri-valued enum used > for guc.c purposes from a separate boolean that actually enables the > feature. GUC sets the boolean to "off" initially when it sees the enum > as "auto", and then pg_

Re: compute_query_id and pg_stat_statements

Andres Freund writes: > I think that's the right direction. I wonder though if we shouldn't go a > bit further. Have one guc that determines the "query id provider" (NULL > or a shared library), and one GUC that configures whether query-id is > computed (never, on-demand/auto, always). For the pro

Re: compute_query_id and pg_stat_statements

Hi, On 2021-04-26 14:21:00 -0400, Tom Lane wrote: > Andres Freund writes: > That's sounding like a pretty sane design, actually. Not sure about > the shared-library-name-with-fixed-function-name detail, but certainly > it seems to be useful to separate "I need a query-id" from the details > of t

Re: compute_query_id and pg_stat_statements

On Mon, Apr 26, 2021 at 8:14 PM Andres Freund wrote: > > Hi, > > On 2021-04-26 13:43:31 -0400, Alvaro Herrera wrote: > > I think it's straightforward, if we decouple the tri-valued enum used > > for guc.c purposes from a separate boolean that actually enables the > > feature. GUC sets the boolean

Re: Use simplehash.h instead of dynahash in SMgr

Hi, On 2021-04-25 01:27:24 +0300, Yura Sokolov wrote: > It is quite interesting result. Simplehash being open-addressing with > linear probing is friendly for cpu cache. I'd recommend to define > SH_FILLFACTOR with value lower than default (0.9). I believe 0.75 is > suitable most for such kind of

Re: Addition of authenticated ID to pg_stat_activity

Hi, On 2021-04-26 11:34:16 +0900, Michael Paquier wrote: > 9afffcb has added the concept of authenticated identity to the > information provided in log_connections for audit purposes, with this > data stored in each backend's port. One extra thing that can be > really useful for monitoring is the

Re: Addition of authenticated ID to pg_stat_activity

Greetings, * Andres Freund (and...@anarazel.de) wrote: > On 2021-04-26 11:34:16 +0900, Michael Paquier wrote: > > 9afffcb has added the concept of authenticated identity to the > > information provided in log_connections for audit purposes, with this > > data stored in each backend's port. One ex

Re: Performance degradation of REFRESH MATERIALIZED VIEW

Hi, On 2021-04-26 15:31:02 +0200, Tomas Vondra wrote: > I'm not sure what to do about this :-( I don't have any ideas about how to > eliminate this overhead, so the only option I see is reverting the changes > in heap_insert. Unfortunately, that'd mean inserts into TOAST tables won't > be frozen .

multi-version capable PostgresNode.pm

Just to complete the circle on this topic, which I intend to take up again during the next dev cycle, I have captured the current state of my work in a public git repo at . This can be cloned and used without having to change the core Postgres code, as

Re: Use simplehash.h instead of dynahash in SMgr

Andres Freund писал 2021-04-26 21:46: Hi, On 2021-04-25 01:27:24 +0300, Yura Sokolov wrote: It is quite interesting result. Simplehash being open-addressing with linear probing is friendly for cpu cache. I'd recommend to define SH_FILLFACTOR with value lower than default (0.9). I believe 0.75 i

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On 2021-Apr-26, Alvaro Herrera wrote: > > Please allow me to study the patch a bit more closely and get back tomorrow. > > Sure, thanks! Here's a more polished version. After trying the version with the elog(ERROR) when two detached partitions are present, I decided against it; it is unhelpful

Re: Use simplehash.h instead of dynahash in SMgr

Hi, On 2021-04-26 22:44:13 +0300, Yura Sokolov wrote: > Even for Robin Hood hashing 0.9 fill factor is too high. It leads to too > much movements on insertion/deletion and longer average collision chain. That's true for modification heavy cases - but most hash tables in PG, including the smgr one

Re: tab-complete for ALTER TABLE .. DETACH PARTITION CONCURRENTLY

On 2021-Apr-26, Justin Pryzby wrote: > On Thu, Apr 22, 2021 at 04:40:35PM -0400, Alvaro Herrera wrote: > > Would anyone oppose me pushing this for tab-completing the new keywords > > of ALTER TABLE .. DETACH PARTITION? > > +1 to apply tab completion for v14 Pushed. -- Álvaro Herrera

Re: Performance degradation of REFRESH MATERIALIZED VIEW

On 4/26/21 9:27 PM, Andres Freund wrote: Hi, On 2021-04-26 15:31:02 +0200, Tomas Vondra wrote: I'm not sure what to do about this :-( I don't have any ideas about how to eliminate this overhead, so the only option I see is reverting the changes in heap_insert. Unfortunately, that'd mean inse

Re: Performance degradation of REFRESH MATERIALIZED VIEW

Hi, On 2021-04-26 23:59:17 +0200, Tomas Vondra wrote: > On 4/26/21 9:27 PM, Andres Freund wrote: > > On 2021-04-26 15:31:02 +0200, Tomas Vondra wrote: > > > I'm not sure what to do about this :-( I don't have any ideas about how to > > > eliminate this overhead, so the only option I see is reverti

Re: ALTER TABLE .. DETACH PARTITION CONCURRENTLY

Sorry, I forgot to update some comments in that version. Fixed here. -- Álvaro Herrera39°49'30"S 73°17'W >From cb6d9e026624656e826ea880716ee552b15203a8 Mon Sep 17 00:00:00 2001 From: Alvaro Herrera Date: Mon, 26 Apr 2021 14:53:04 -0400 Subject: [PATCH v2] Allow a par

Do we have a way to dynamically make a callback stub?

Hi, A lot of the APIs in PostgreSQL that accept a callback follow the familiar idiom of an extra void* argument allowing a single static callback address to be multiplexed. But not all of them do. For example, if I wanted to expose the possibility of defining GUCs from code written in my PL, I wou

Bug fix for tab completion of ALTER TABLE ... VALIDATE CONSTRAINT ...

Folks, I noticed that $subject completes with already valid constraints, please find attached a patch that fixes it. I noticed that there are other places constraints can be validated, but didn't check whether similar bugs exist there yet. Best, David. -- David Fetter http://fetter.org/ Phone:

Re: Parallel INSERT SELECT take 2

On Mon, Apr 26, 2021 at 4:56 PM houzj.f...@fujitsu.com wrote: > > > The parallel attributes in table means the parallel safety when user does > > > some > > data-modification operations on it. > > > So, It only limit the use of parallel plan when using > > > INSERT/UPDATE/DELETE. > > > > In that

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 9:24 PM Alvaro Herrera wrote: > > On 2021-Apr-26, Bharath Rupireddy wrote: > > > I agree that we can just be clear about the problem. Looks like the > > majority of the errors "conflicting or redundant options" are for > > redundant options. So, wherever "conflicting or red

Re: Enhanced error message to include hint messages for redundant options error

On Mon, Apr 26, 2021 at 9:10 PM Bharath Rupireddy wrote: > > I found another problem with collationcmds.c is that it doesn't error > out if some of the options are specified more than once, something > like below. I think the option checking "for loop" in DefineCollation > needs to be reworked. >

Re: Addition of authenticated ID to pg_stat_activity

On Mon, Apr 26, 2021 at 03:21:46PM -0400, Stephen Frost wrote: > * Andres Freund (and...@anarazel.de) wrote: >> I'm getting a bit worried about the incremental increase in >> pg_stat_activity width - it's probably by far the view that's most >> viewed interactively. I think we need to be careful no

Re: Transactions involving multiple postgres foreign servers, take 2

On 2021/03/17 12:03, Masahiko Sawada wrote: > I've attached the updated version patch set. Thanks for updating the patches! I'm now restarting to review of 2PC because I'd like to use this feature in PG15. I think the following logic of resolving and removing the fdwxact entries by the transa

Re: Addition of authenticated ID to pg_stat_activity

On Tue, Apr 27, 2021 at 09:59:18AM +0900, Michael Paquier wrote: > > I am wondering if we should take this as an occasion to move some data > out of pg_stat_activity into a separate biew, dedicated to the data > related to the connection that remains set to the same value for the > duration of a b

Re: Performance degradation of REFRESH MATERIALIZED VIEW

On Mon, Apr 26, 2021 at 10:31 PM Tomas Vondra wrote: > > Hi, > > I took a look at this today, as I committed 39b66a91b back in January. I > can reproduce the issue, with just 1M rows the before/after timings are > roughly 480ms and 620ms on my hardware. > > Unfortunately, the v3 patch does not rea

Re: Addition of authenticated ID to pg_stat_activity

On Tue, Apr 27, 2021 at 09:26:11AM +0800, Julien Rouhaud wrote: > -1. It's already annoying enough to have to type "WHERE pid != > pg_backend_pid()" to exclude my own backend, and I usually need it quite > often. > Unless we add some new view which integrate that, something like > pg_stat_activit

RE: Parallel INSERT SELECT take 2

> > Currently, index expression and predicate are stored in text format. > > We need to use stringToNode(expression/predicate) to parse it. > > Some committers think doing this twice does not look good, unless we > > found some ways to pass parsed info to the executor to avoid the second > parse. >

Re: Parallel INSERT SELECT take 2

On Tue, Apr 27, 2021 at 10:51 AM Bharath Rupireddy wrote: > > > I still feel that why we shouldn't limit the declarative approach to > only partitioned tables? And for normal tables, possibly with a > minimal cost(??), the server can do the safety checking. I know this > feels a little inconsisten

Re: Replication slot stats misgivings

On Mon, Apr 26, 2021 at 8:42 AM Amit Kapila wrote: > > On Mon, Apr 26, 2021 at 8:01 AM Masahiko Sawada wrote: > > > > On Fri, Apr 23, 2021 at 6:15 PM Amit Kapila wrote: > > > > > > On Mon, Apr 19, 2021 at 4:28 PM vignesh C wrote: > > > > > > > > I have made the changes to update the replication

Re: Parallel INSERT SELECT take 2

On Tue, Apr 27, 2021 at 7:45 AM Greg Nancarrow wrote: > > On Tue, Apr 27, 2021 at 10:51 AM Bharath Rupireddy > wrote: > > > > > > I still feel that why we shouldn't limit the declarative approach to > > only partitioned tables? And for normal tables, possibly with a > > minimal cost(??), the serv

Re: SQL-standard function body

On Thu, Apr 22, 2021 at 04:04:18PM -0400, Jeff Janes wrote: > This commit break line continuation prompts for unbalanced parentheses in > the psql binary. Skimming through this thread, I don't see that this is > intentional or has been noticed before. > > with psql -X > > Before: > > jjanes=# a

Re: Replication slot stats misgivings

On Tue, Apr 27, 2021 at 8:01 AM vignesh C wrote: > > On Mon, Apr 26, 2021 at 8:42 AM Amit Kapila wrote: > > > > On Mon, Apr 26, 2021 at 8:01 AM Masahiko Sawada > > wrote: > > > > > > On Fri, Apr 23, 2021 at 6:15 PM Amit Kapila > > > wrote: > > > > > > > > On Mon, Apr 19, 2021 at 4:28 PM vigne

Re: Replication slot stats misgivings

On Tue, Apr 27, 2021 at 11:45 AM Amit Kapila wrote: > > On Tue, Apr 27, 2021 at 8:01 AM vignesh C wrote: > > > > On Mon, Apr 26, 2021 at 8:42 AM Amit Kapila wrote: > > > > > > On Mon, Apr 26, 2021 at 8:01 AM Masahiko Sawada > > > wrote: > > > > > > > > On Fri, Apr 23, 2021 at 6:15 PM Amit Kapi

Re: Parallel INSERT SELECT take 2

On Tue, Apr 27, 2021 at 7:39 AM houzj.f...@fujitsu.com wrote: > > I'm thinking that when users say ALTER TABLE partioned_table SET PARALLEL > > TO 'safe';, we check all the partitions' and their associated objects' > > parallel > > safety? If all are parallel safe, then only we set partitioned_ta

Re: Parallel INSERT SELECT take 2

On Tue, Apr 27, 2021 at 7:45 AM Greg Nancarrow wrote: > > On Tue, Apr 27, 2021 at 10:51 AM Bharath Rupireddy > wrote: > > > > > > I still feel that why we shouldn't limit the declarative approach to > > only partitioned tables? And for normal tables, possibly with a > > minimal cost(??), the serv

Re: [HACKERS] logical decoding of two-phase transactions

On Wed, Apr 21, 2021 at 12:13 PM Peter Smith wrote: > > On Tue, Apr 20, 2021 at 3:45 PM Peter Smith wrote: > > > > Please find attached the latest patch set v73`* > > > > Differences from v72* are: > > > > * Rebased to HEAD @ today (required because v72-0001 no longer applied > > cleanly) > > >

  1   2   >