Re: New strategies for freezing, advancing relfrozenxid early

On Wed, Dec 14, 2022 at 6:07 AM Peter Geoghegan wrote: > > At the suggestion of Jeff, I wrote a Wiki page that shows motivating > examples for the patch series: > > https://wiki.postgresql.org/wiki/Freezing/skipping_strategies_patch:_motivating_examples > > These are all cases where VACUUM

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

On Fri, Dec 16, 2022 at 12:49 AM Tom Lane wrote: > > Masahiko Sawada writes: > > I don't think we need additional PG_TRY() for that since exec_stmts() > > is already called in PG_TRY() if there is an exception block. I meant > > to call stmt_end() in PG_CATCH() in exec_stmt_block() (i.e. only

Re: Perform streaming logical transactions by background workers and parallel apply

On Thu, Dec 15, 2022 at 12:28 PM houzj.f...@fujitsu.com wrote: > > On Wednesday, December 14, 2022 2:49 PM Amit Kapila > wrote: > > > > > On Wed, Dec 14, 2022 at 9:50 AM houzj.f...@fujitsu.com > > wrote: > > > > > > On Tuesday, December 13, 2022 11:25 PM Masahiko Sawada > > wrote: > > > > > >

Re: Removing unneeded self joins

On 12/6/22 23:46, Andres Freund wrote: This doesn't pass the main regression tests due to a plan difference. https://cirrus-ci.com/task/5537518245380096 https://api.cirrus-ci.com/v1/artifact/task/5537518245380096/testrun/build/testrun/regress/regress/regression.diffs diff -U3

RE: Time delayed LR (WAS Re: logical replication restrictions)

Dear Amit, > I also don't see the need for this mechanism for logical replication, > and in fact, why do we need to even wait for sending the existing WAL? Is it meant that logicalrep walsenders do not have to track WalSndCaughtUp and any pending data in the output buffer? > I think the reason

Re: Typo macro name on FreeBSD?

On Fri, 16 Dec 2022 at 12:25, Thomas Munro wrote: > On Fri, Dec 16, 2022 at 4:44 PM Japin Li wrote: >> Recently, I compile PostgreSQL on FreeBSD, I find commit a2a8acd152 >> introduecs >> __freebsd__ macro, however, I cannot find this macro on FreeBSD 13. There >> only >> has __FreeBSD__

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, Dec 15, 2022 at 09:13:54PM -0800, Nathan Bossart wrote: > I do wonder whether this is something we really need to be concerned about. > In the worst case, you might be able to VACUUM a table with a concurrent > owner change. I suppose we might want to avoid running the index functions as

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, Dec 15, 2022 at 08:35:53PM -0800, Jeff Davis wrote: > But why make CLUSTER more like VACUUM? Shouldn't we make > VACUUM/CLUSTER/ANALYZE more like INSERT/SELECT/REINDEX? Hm. Since VACUUM may happen across multiple transactions, it is careful to re-check the privileges for each relation.

Re: Exclusion constraints on partitioned tables

On 12/15/22 16:12, Tom Lane wrote: This patch also requires the matching constraint columns to use equality comparisons (`(foo WITH =)`), so it is really equivalent to the existing b-tree rule. That's not quite good enough: you'd better enforce that it's the same equality operator (and same

Re: fix and document CLUSTER privileges

Here is a new version of the patch. I've moved the privilege checks to a new function, and I added a note in the docs about clustering partitioned tables in a transaction block (it's not allowed). -- Nathan Bossart Amazon Web Services: https://aws.amazon.com diff --git

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, 2022-12-15 at 17:19 -0800, Nathan Bossart wrote: > The attached patch adds WARNING messages, but we're still far from > consistency with VACUUM/ANALYZE. But why make CLUSTER more like VACUUM? Shouldn't we make VACUUM/CLUSTER/ANALYZE more like INSERT/SELECT/REINDEX? As far as I can tell,

Re: Typo macro name on FreeBSD?

On Fri, Dec 16, 2022 at 4:44 PM Japin Li wrote: > Recently, I compile PostgreSQL on FreeBSD, I find commit a2a8acd152 introduecs > __freebsd__ macro, however, I cannot find this macro on FreeBSD 13. There only > has __FreeBSD__ macro. Is this a typo? Yeah, that seems to be my fault. Will fix.

Re: Time delayed LR (WAS Re: logical replication restrictions)

On Thu, Dec 15, 2022 at 11:22 AM Kyotaro Horiguchi wrote: > > At Thu, 15 Dec 2022 10:29:17 +0530, Amit Kapila > wrote in > > On Thu, Dec 15, 2022 at 10:11 AM Kyotaro Horiguchi > > wrote: > > > > > > At Thu, 15 Dec 2022 09:18:55 +0530, Amit Kapila > > > wrote in > > > > On Thu, Dec 15, 2022

Re: New strategies for freezing, advancing relfrozenxid early

On Thu, Dec 15, 2022 at 11:11 AM Justin Pryzby wrote: > The patches (003 and 005) are missing a word > should use to decide whether to its eager freezing strategy. I mangled this during rebasing for v9, which reordered the commits. Will be fixed in v10. > On the wiki, missing a word: > builds

Re: Time delayed LR (WAS Re: logical replication restrictions)

On Thu, Dec 15, 2022 at 1:42 PM Hayato Kuroda (Fujitsu) wrote: > > Dear Horiguchi-san, Amit, > > > > Yes, that would be ideal. But do you know why that is a must? > > > > I believe a graceful shutdown (fast and smart) of a replication set is > > expected to > > be in sync. Of course we can

Typo macro name on FreeBSD?

Hi, hackers Recently, I compile PostgreSQL on FreeBSD, I find commit a2a8acd152 introduecs __freebsd__ macro, however, I cannot find this macro on FreeBSD 13. There only has __FreeBSD__ macro. Is this a typo? root@freebsd:~ # uname -a FreeBSD freebsd 13.1-RELEASE-p3 FreeBSD

Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication

On Thu, Dec 15, 2022 at 5:33 PM Melih Mutlu wrote: > > Also here are some numbers with 10 tables loaded with some data : > > | 10 MB | 100 MB > -- > master | 2868.524 ms | 14281.711 ms >

Re: generic plans and "initial" pruning

On Wed, Dec 14, 2022 at 5:35 PM Amit Langote wrote: > I have moved the original functionality of GetCachedPlan() to > GetCachedPlanInternal(), turning the former into a sort of controller > as described shortly. The latter's CheckCachedPlan() part now only > locks the "minimal" set of,

Re: Add proper planner support for ORDER BY / DISTINCT aggregates

On Fri, 16 Dec 2022 at 00:10, David Rowley wrote: > v3-0002 removes the 1.5 x cost pessimism from incremental sort and > also rewrites how we make incremental sort paths. I've now gone > through the remaining places where we create an incremental sort path > to give all those the same treatment

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, Dec 15, 2022 at 01:48:13PM -0600, Justin Pryzby wrote: > vacuum initially calls vacuum_is_permitted_for_relation() only for the > partitioned table, and *later* locks the partition and then checks its > permissions, which is when the message is output. > > Since v15, cluster calls

Re: Exclusion constraints on partitioned tables

Paul Jungwirth writes: > It lets you create exclusion constraints on partitioned tables, similar > to today's rules for b-tree primary keys & unique constraints: > just as we permit a PK on a partitioned table when the PK's columns are > a superset of the partition keys, so we could also allow

Add enable_presorted_aggregate GUC

Over on [1] Pavel reports that PG16's performance is slower than PG14's for his ORDER BY aggregate query. This seems to be mainly down to how the costing works for Incremental Sort. Now, ever since 1349d279, the planner will make a plan that's ordered by the GROUP BY clause and add on the path

Re: Infinite Interval

On Mon, Dec 12, 2022 at 8:05 AM Ashutosh Bapat wrote: > > Hi Joseph, > I stumbled upon this requirement a few times. So I started working on > this support in my spare time as a hobby project to understand > horology code in PostgreSQL. This was sitting in my repositories for > more than an year.

Exclusion constraints on partitioned tables

Hello Hackers, I'm trying to get things going again on my temporal tables work, and here is a small patch to move that forward. It lets you create exclusion constraints on partitioned tables, similar to today's rules for b-tree primary keys & unique constraints: just as we permit a PK on a

Re: [PATCH] Teach pg_waldump to extract FPIs from the WAL

On Thu, Dec 15, 2022 at 12:36 AM Michael Paquier wrote: > > On Wed, Dec 14, 2022 at 04:44:34PM -0600, David Christensen wrote: > > I can get one sent in tomorrow. This v10 should incorporate your feedback as well as Bharath's. > -XLogRecordHasFPW(XLogReaderState *record) >

Re: wake up logical workers after ALTER SUBSCRIPTION

I tried setting wal_retrieve_retry_interval to 1ms for all TAP tests (similar to what was done in 2710ccd), and I noticed that the recovery tests consistently took much longer. Upon further inspection, it looks like the same (or a very similar) race condition described in e5d494d's commit message

Re: The drop-index-concurrently-1 isolation test no longer tests what it was meant to

On Thu, 15 Dec 2022 at 18:26, David Rowley wrote: > I propose the attached which gets rid of the not-so-great casting > method that was originally added to this test to try and force the seq > scan. It seems a little dangerous to put in hacks like that to force > a particular plan when the

RE: Partial aggregates pushdown

Hi Mr.Freund. > cfbot complains about some compiler warnings when building with clang: > https://cirrus-ci.com/task/6606268580757504 > > deparse.c:3459:22: error: equality comparison with extraneous parentheses > [-Werror,-Wparentheses-equality] > if ((node->aggsplit == AGGSPLIT_SIMPLE))

Re: Error-safe user functions

Robert Haas writes: > Tom, I just want to extend huge thanks to you for working on this > infrastructure. Thanks. I agree it's an important bit of work. I'm going to step back from this for now and get on with other work, but before that I thought there was one more input function I should

Re: Speedup generation of command completion tags

On Mon, 12 Dec 2022 at 14:48, David Rowley wrote: > > On Sun, 11 Dec 2022 at 11:05, Andres Freund wrote: > > What about moving make_completion_tag() to cmdtag.c? Then we could just get > > the entire CommandTagBehaviour struct at once. It's not super pretty to pass > > QueryCompletion to a

Re: [PATCH] random_normal function

> On Dec 14, 2022, at 9:17 PM, Michael Paquier wrote: > > On Tue, Dec 13, 2022 at 03:51:11PM -0800, Paul Ramsey wrote: >> Clearing up one CI failure. > > +-- normal values converge on stddev == 2.0 > +SELECT round(stddev(random_normal(2, 2))) > + FROM generate_series(1, 1); > > I am not

Re: Pluggable toaster

Hi hackers! I want to bump this thread and remind the community about Pluggable TOAST. Overall Pluggable TOAST was revised to work without altering PG_ATTRIBUTE table - no atttoaster column, allow to drop unused Toasters and some other improvements. Sorry for the big delay, but it was a big piece

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, Dec 15, 2022 at 10:10:43AM -0800, Jeff Davis wrote: > On Thu, 2022-12-15 at 12:31 +0300, Pavel Luzanov wrote: > > I think the approach that Nathan implemented [1] for TOAST tables > > in the latest version can be used for partitioned tables as well. > > Skipping the privilege check for

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, Dec 15, 2022 at 10:42:15AM -0800, Jeff Davis wrote: > Right now, targetting the toast table directly requires the USAGE > privilege on the toast schema, and you have to look up the name first, > right? As it is, that's not a great UI. > > How about if we add a VACUUM option like

Re: New strategies for freezing, advancing relfrozenxid early

The patches (003 and 005) are missing a word should use to decide whether to its eager freezing strategy. On the wiki, missing a word: builds on related added

Re: New strategies for freezing, advancing relfrozenxid early

On Thu, Dec 15, 2022 at 6:50 AM Matthias van de Meent wrote: > This first run of (auto)vacuum after the 8GB threshold seems > to appear as a significant IO event (both in WAL and relation > read/write traffic) with 50% of the table updated and WAL-logged. I > think this should be limited to some

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Wed, 2022-12-14 at 16:27 -0800, Nathan Bossart wrote: > I don't know if this is good enough.  It seems like ideally you > should be > able to VACUUM a TOAST table directly if you have MAINTAIN on its > main > relation. Right now, targetting the toast table directly requires the USAGE privilege

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On Thu, 2022-12-15 at 12:31 +0300, Pavel Luzanov wrote: > I think the approach that Nathan implemented [1] for TOAST tables > in the latest version can be used for partitioned tables as well. > Skipping the privilege check for partitions while working with > a partitioned table. In that case we

Re: postgres_fdw: using TABLESAMPLE to collect remote sample

James Finnerty writes: > This patch looks good to me. I have two very minor nits: The inflation > of the sample size by 10% is arbitrary but it doesn't seem unreasonable > or concerning. It just makes me curious if there are any known cases > that motivated adding this logic. I wondered why,

Re: postgres_fdw: using TABLESAMPLE to collect remote sample

This patch looks good to me. I have two very minor nits: The inflation of the sample size by 10% is arbitrary but it doesn't seem unreasonable or concerning. It just makes me curious if there are any known cases that motivated adding this logic. Secondly, if the earliest non-deprecated

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

On Thu, Dec 15, 2022 at 8:49 AM Tom Lane wrote: > Masahiko Sawada writes: > > I don't think we need additional PG_TRY() for that since exec_stmts() > > is already called in PG_TRY() if there is an exception block. I meant > > to call stmt_end() in PG_CATCH() in exec_stmt_block() (i.e. only when

Re: [PATCH] psql: \dn+ to show size of each schema (and \dA+ for AMs)

Rebased on c727f511b. This patch record was "closed for lack of interest", but I think what's actually needed is committer review of which approach to take. - add backend functions but do not modify psql ? - add to psql slash-plus commnds ? - introduce psql double-plus commands for new

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

Masahiko Sawada writes: > I don't think we need additional PG_TRY() for that since exec_stmts() > is already called in PG_TRY() if there is an exception block. I meant > to call stmt_end() in PG_CATCH() in exec_stmt_block() (i.e. only when > an error is caught by the exception block). Currently,

Re: New strategies for freezing, advancing relfrozenxid early

On Wed, 14 Dec 2022 at 00:07, Peter Geoghegan wrote: > > On Tue, Dec 13, 2022 at 9:16 AM Peter Geoghegan wrote: > > That's not the only thing we care about, though. And to the extent we > > care about it, we mostly care about the consequences of either > > freezing or not freezing eagerly.

Re: Error-safe user functions

On Wed, Dec 14, 2022 at 6:24 PM Tom Lane wrote: > I've gone through these now and revised/pushed most of them. Tom, I just want to extend huge thanks to you for working on this infrastructure. jsonpath aside, I think this is going to pay dividends in many ways for many years to come. It's

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

čt 15. 12. 2022 v 12:51 odesílatel Masahiko Sawada napsal: > On Thu, Dec 15, 2022 at 4:53 PM Kyotaro Horiguchi > wrote: > > > > At Thu, 15 Dec 2022 08:41:21 +0100, Pavel Stehule < > pavel.steh...@gmail.com> wrote in > > > čt 15. 12. 2022 v 8:25 odesílatel Masahiko Sawada < >

Re: Temporary tables versus wraparound... again

On Wed, Dec 14, 2022 at 4:44 PM Greg Stark wrote: > > You do have to lock a table in order to update its pg_class row, > > though, whether the table is temporary or not. Otherwise, another > > session could drop it while you're doing something with it, after > > which bad things would happen. > >

RE: Ability to reference other extensions by schema in extension scripts

> On Tue, Nov 22, 2022 at 11:24:19PM -0500, Regina Obe wrote: > > > Here is first version of my patch using the @extschema:extensionname@ > > syntax you proposed. > > > > This patch includes: > > 1) Changes to replace references of @extschema:extensionname@ with the > > schema of the required

Re: Perform streaming logical transactions by background workers and parallel apply

On Thu, Dec 15, 2022 at 8:58 AM houzj.f...@fujitsu.com wrote: > Few minor comments: = 1. + for (i = list_length(subxactlist) - 1; i >= 0; i--) + { + TransactionId xid_tmp = lfirst_xid(list_nth_cell(subxactlist, i)); + + if (xid_tmp == subxid) + { + RollbackToSavepoint(spname); +

Re: [PATCH] Reuse Workers and Replication Slots during Logical Replication

Hi, Attached new versions of the patch with some changes/fixes. Here also some numbers to compare the performance of log. rep. with this patch against the current master branch. My method of benchmarking is the same with what I did earlier in this thread. (on a different environment, so not

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

On Thu, Dec 15, 2022 at 4:53 PM Kyotaro Horiguchi wrote: > > At Thu, 15 Dec 2022 08:41:21 +0100, Pavel Stehule > wrote in > > čt 15. 12. 2022 v 8:25 odesílatel Masahiko Sawada > > napsal: > > > Is this a bug in plpgsql? > > > > > > > I think it is by design. There is not any callback that is

cirrus scripts could use make -k

I would find it useful if the cirrus scripts used make -k (and ninja -k) to keep building everything in the presence of errors. For example, I once had some issue in code that caused a bunch of compiler warnings on Windows. The cirrus build would only show me the first one, then I had to

Re: Add proper planner support for ORDER BY / DISTINCT aggregates

On Tue, 13 Dec 2022 at 20:53, David Rowley wrote: > I think what we need to do is: Do our best to give incremental sort > the most realistic costs we can and accept that it might choose a > worse plan in some cases. Users can turn it off if they really have no > other means to convince the

Re: Raising the SCRAM iteration count

> On 15 Dec 2022, at 00:52, Michael Paquier wrote: >conn->in_hot_standby = PG_BOOL_UNKNOWN; > + conn->scram_iterations = SCRAM_DEFAULT_ITERATIONS; > > s/SCRAM_DEFAULT_ITERATIONS/SCRAM_SHA_256_DEFAULT_ITERATIONS/ and > s/scram_iterations/scram_sha_256_interations/ perhaps? Distinct

Re: Raising the SCRAM iteration count

> On 14 Dec 2022, at 19:59, Jonathan S. Katz wrote: > On 12/14/22 6:25 AM, Daniel Gustafsson wrote: >>> On 14 Dec 2022, at 02:00, Michael Paquier wrote: >>> So, you mean that the GUC should be named like password_iterations, >>> taking a grammar with a list like 'scram-sha-256=4096,algo2=5000'?

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

čt 15. 12. 2022 v 9:34 odesílatel Kyotaro Horiguchi napsal: > At Thu, 15 Dec 2022 09:03:12 +0100, Pavel Stehule > wrote in > > I found some solution based by using fmgr hook > > > > > https://github.com/okbob/plpgsql_check/commit/9a17e97354a48913de5219048ee3be6f8460bae9 > > Oh! Thanks for the

Re: Minimal logical decoding on standbys

Hi, On 12/14/22 4:55 PM, Robert Haas wrote: On Wed, Dec 14, 2022 at 8:06 AM Drouvot, Bertrand> Other comments: +if RelationIsAccessibleInLogicalDecoding(rel) +xlrec.flags |= VISIBILITYMAP_ON_CATALOG_ACCESSIBLE_IN_LOGICAL_DECODING; This is a few parentheses short of where it

Re: Minimal logical decoding on standbys

Hi, On 12/14/22 6:48 PM, Robert Haas wrote: On Wed, Dec 14, 2022 at 12:35 PM Andres Freund wrote: typedef struct xl_heap_prune I think this is unsafe on alignment-picky machines. I think it will cause the offset numbers to be aligned at an odd address. heap_xlog_prune() doesn't copy the

Re: Inconsistency in reporting checkpointer stats

> /* If part of a checkpoint, count this as a buffer written. */ >if (fdata) >CheckpointStats.ckpt_bufs_written++; > + PendingCheckpointerStats.buf_written_checkpoints++; > Also, the proposed patch would touch PendingCheckpointerStats even > when there is no fdata, aka outside

Re: Inconsistency in reporting checkpointer stats

> Indeed PendingCheckpointerStats.buf_written_checkpoints needs to count > buffer writes in SlruInternalWritePage(). However, does it need to be > done immediately there? The stats will not be visible to the users > until the next pgstat_report_checkpointer(). Incrementing >

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On 15.12.2022 03:27, Nathan Bossart wrote: Another option I'm looking at is skipping the privilege checks when VACUUM recurses to a TOAST table. This won't allow you to VACUUM the TOAST table directly, but it would at least address the originally-reported issue This approach can be

Re: static assert cleanup

On 09.12.22 11:01, John Naylor wrote: On Fri, Dec 9, 2022 at 2:47 PM Peter Eisentraut > wrote: > > 0003-Move-some-static-assertions-to-better-places.patch > > This moves some that I thought were suboptimally placed but it could be > debated or

Re: allow granting CLUSTER, REFRESH MATERIALIZED VIEW, and REINDEX

On 15.12.2022 03:18, Jeff Davis wrote: Right, that's what I had in mind: a user is only granted operations on the partitioned table, not the partitions. It's all clear now. There's definitely a problem with this patch and partitioning, because REINDEX affects the partitions, CLUSTER is a

Re: Ability to reference other extensions by schema in extension scripts

On Tue, Nov 22, 2022 at 11:24:19PM -0500, Regina Obe wrote: > Here is first version of my patch using the @extschema:extensionname@ syntax > you proposed. > > This patch includes: > 1) Changes to replace references of @extschema:extensionname@ with the > schema of the required extension > 2)

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

At Thu, 15 Dec 2022 09:03:12 +0100, Pavel Stehule wrote in > I found some solution based by using fmgr hook > > https://github.com/okbob/plpgsql_check/commit/9a17e97354a48913de5219048ee3be6f8460bae9 Oh! Thanks for the pointer, will look into that. By the way, It seems to me that the tool is

Re: pg_upgrade: Make testing different transfer modes easier

> On 15 Dec 2022, at 01:56, Kyotaro Horiguchi wrote: > > At Wed, 14 Dec 2022 10:40:45 +0100, Daniel Gustafsson wrote > in >>> On 14 Dec 2022, at 08:04, Peter Eisentraut >>> wrote: >>> >>> On 07.12.22 17:33, Peter Eisentraut wrote: I think if we want to make this configurable on the

RE: Time delayed LR (WAS Re: logical replication restrictions)

Dear Horiguchi-san, Amit, > > Yes, that would be ideal. But do you know why that is a must? > > I believe a graceful shutdown (fast and smart) of a replication set is > expected to > be in sync. Of course we can change the policy to allow walsnder to stop > before > confirming all WAL have

Re: plpgsq_plugin's stmt_end() is not called when an error is caught

čt 15. 12. 2022 v 8:53 odesílatel Kyotaro Horiguchi napsal: > At Thu, 15 Dec 2022 08:41:21 +0100, Pavel Stehule > wrote in > > čt 15. 12. 2022 v 8:25 odesílatel Masahiko Sawada > > > napsal: > > > Is this a bug in plpgsql? > > > > > > > I think it is by design. There is not any callback that