Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Fri, Sep 22, 2023 at 11:59 AM Bharath Rupireddy wrote: > > On Thu, Sep 21, 2023 at 6:54 PM Hayato Kuroda (Fujitsu) > wrote: > > > > 1. > +/* > + * Use max_slot_wal_keep_size as -1 to prevent the WAL removal by the > + * checkpointer process. If WALs required by logical replication

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Fri, Sep 22, 2023 at 10:57 AM Bharath Rupireddy wrote: > > On Thu, Sep 21, 2023 at 5:45 PM Amit Kapila wrote: > > > 3. > > > +/* Quick exit if the given lsn is larger than current one */ > > > +if (start_lsn >= GetFlushRecPtr(NULL)) > > > +PG_RETURN_BOOL(false); > > > + > > > >

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Thu, Sep 21, 2023 at 6:54 PM Hayato Kuroda (Fujitsu) wrote: > > > 6. > > +if (PQntuples(res) != 1) > > +pg_fatal("could not count the number of logical replication > > slots"); > > + > > > > Not existing a single logical replication slot an error? I think it > > must be if (PQntupl

Re: New WAL record to detect the checkpoint redo location

On Thu, Sep 21, 2023 at 9:06 PM Robert Haas wrote: > > On Thu, Sep 21, 2023 at 4:22 AM Amit Kapila wrote: > > After the 0003 patch, do we need acquire exclusive lock via > > WALInsertLockAcquireExclusive() for non-shutdown checkpoints. Even the > > comment "We must block concurrent insertions whi

Re: Remove MSVC scripts from the tree

On 22.09.23 03:12, Michael Paquier wrote: It has been mentioned a few times now that, as Meson has been integrated in the tree, the next step would be to get rid of the custom scripts in src/tools/msvc/ and moving forward only support Meson when building with VS compilers. First we need to fix

Re: information_schema and not-null constraints

On 19.09.23 09:01, Peter Eisentraut wrote: While testing this, I noticed that the way the check_clause of regular check constraints is computed appears to be suboptimal.  It currently does CAST(substring(pg_get_constraintdef(con.oid) from 7) AS character_data) which ends up with an extra set o

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Thu, Sep 21, 2023 at 5:45 PM Amit Kapila wrote: > > > Thanks for the patch. I have some comments on v42: > > Probably trying to keep it similar with > binary_upgrade_create_empty_extension(). I think it depends what > behaviour we expect for NULL input. confirmed_flush_lsn for a logical slot c

Re: Bug fix in vacuumdb --buffer-usage-limit xxx -Z

On Thu, Sep 21, 2023 at 06:56:29PM +1200, David Rowley wrote: > I deem it pretty unlikely that someone will accidentally remove the > code that I just committed and a test to test that vacuumdb -Z > --buffer-usage-limit ... passes the BUFFER_USAGE_LIMIT option would > likely just forever mark that

Re: Row pattern recognition

> Attached is the fix against v6 patch. I will include this in upcoming v7 > patch. Attached is the v7 patch. It includes the fix mentioned above. Also this time the pattern matching engine is enhanced: previously it recursed to row direction, which means if the number of rows in a frame is larg

Doesn't pgstat_report_wal() handle the argument "force" incorrectly

Hi, pgstat_report_wal() calls pgstat_flush_wal() and pgstat_flush_io(). When calling them, pgstat_report_wal() specifies its argument "force" as the argument of them, as follows. But according to the code of pgstat_flush_wal() and pgstat_flush_io(), their argument is "nowait" and its meaning

Doesn't pgstat_report_wal() handle the argument "force" incorrectly

Hi, pgstat_report_wal() calls pgstat_flush_wal() and pgstat_flush_io(). When calling them, pgstat_report_wal() specifies its argument "force" as the argument of them, as follows. But according to the code of pgstat_flush_wal() and pgstat_flush_io(), their argument is "nowait" and its meaning

Re: [HACKERS] Should logtape.c blocks be of type long?

On Thu, Sep 21, 2023 at 9:46 PM Michael Paquier wrote: > So, attached is a patch to change these longs to int64. Any thoughts? No new thoughts. I'm still all in favor of this. Thanks for picking it up. At some point we should completely ban the use of "long". -- Peter Geoghegan

Try adding type cast with tablespace

Hi all, Good day! I am a newbee to PostgreSQL and recently came across an idea about type-casting tablespace OID. The motibation is that when I have to upgrade a PostgreSQL database, we need to join other tables to track tablespace name. I have just created a simple patch to resolve this. H

Re: [HACKERS] Should logtape.c blocks be of type long?

On Sun, Feb 26, 2017 at 10:04:20AM -0800, Peter Geoghegan wrote: > I think it's worth being consistent about a restriction like this, as > Robert said. Given that fixing this issue will not affect the machine > code generated by compilers for the majority of platforms we support, > doing so seems e

Re: New WAL record to detect the checkpoint redo location

On Thu, Sep 21, 2023 at 1:50 AM Robert Haas wrote: > > On Mon, Sep 18, 2023 at 2:57 PM Robert Haas wrote: > > I've been brainstorming about this today, trying to figure out some > > ideas to make it work. > > Here are some patches. > > 0001 refactors XLogInsertRecord to unify a couple of separate

Re: [PoC] Reducing planning time when tables have many partitions

On Wed, Sep 20, 2023, at 5:04 PM, Yuya Watari wrote: > On Tue, Sep 19, 2023 at 5:21 PM Andrey Lepikhov > wrote: >> Working on self-join removal in the thread [1] nearby, I stuck into the >> problem, which made an additional argument to work in this new direction >> than a couple of previous ones.

Re: pg_upgrade --check fails to warn about abstime

Bruce Momjian writes: > On Wed, Sep 20, 2023 at 06:54:24PM +0200, Álvaro Herrera wrote: >> I got a complaint that pg_upgrade --check fails to raise red flags when >> the source database contains type abstime when upgrading from pg11. The >> type (along with reltime and tinterval) was removed by p

Re: Questioning an errcode and message in jsonb.c

Hi Chap, > As to how to redesign the error message is a bit confusing to > me, it would be good to see the proposal code as well. > > The only concern from me is that the new error from newer > version is not compatible with the older versions, which may matters > matters or doesn't match, I don'

Re: Report planning memory in EXPLAIN ANALYZE

Hi Ashutosh, Thanks for the patch! I think the reason why others are not excited about this is because the benefit is not clear enough to them after the first glance and plus the chosen wolds "Planning Memory: used " is still confusing for different people. I admit it is clear to you absolutely,

RE: CI: Unfamiliar error while testing macOS

Dear Daniel, Thank you for confirmation! > > That looks like a transient infrastructure error and not something related to > your patch, it will most likely go away for the next run. > I checked next run and found that tests were passed on all platforms. I'm still not sure the root cause, but n

Re: pg_upgrade --check fails to warn about abstime

On Wed, Sep 20, 2023 at 06:54:24PM +0200, Álvaro Herrera wrote: > I got a complaint that pg_upgrade --check fails to raise red flags when > the source database contains type abstime when upgrading from pg11. The > type (along with reltime and tinterval) was removed by pg12. Wow, I never added cod

Re: Questioning an errcode and message in jsonb.c

On 2023-09-21 20:38, Andy Fan wrote: insert into tb select '{"a": "foo", "b": 1}'; ... select cast(a->'a' as numeric) from tb; ERRCODE_INVALID_PARAMETER_VALUE cannot cast jsonb string to type numeric ... select cast(a->'b' as int2) from tb; NUMERIC_VALUE_OUT_OF_RANGE smallint out of ra

Re: Questioning an errcode and message in jsonb.c

Hi Peter, On Wed, Sep 20, 2023 at 4:51 PM Peter Eisentraut wrote: > On 18.09.23 18:55, Chapman Flack wrote: > > It would make me happy if the message could be changed, and maybe > > ERRCODE_INVALID_PARAMETER_VALUE also changed, perhaps to one of > > the JSON-specific ones in the 2203x range. > >

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Thu, Sep 21, 2023 at 01:50:28PM +0530, Amit Kapila wrote: > We have discussed this point. Normally, we don't have such options in > upgrade, so we were hesitent to add a new one for this but there is a > discussion to add an --exclude-logical-slots option. We are planning > to add that as a sepa

Re: Disabling Heap-Only Tuples

Hi, On 2023-09-19 20:20:06 +0200, Matthias van de Meent wrote: > Mostly agreed, but I think there's a pitfall here. You seem to assume > we have a perfect oracle that knows the optimal data size, but we > already know that our estimates can be significantly off. I don't > quite trust the statistic

Re: pg_upgrade and logical replication

On Thu, Sep 21, 2023 at 02:35:55PM +0530, Amit Kapila wrote: > It is because after upgrade of both publisher and subscriber, the > subscriptions won't work. Both publisher and subscriber should work, > otherwise, the logical replication set up won't work. I think we can > probably do this, if we ca

Re: Disabling Heap-Only Tuples

Hi, On 2023-09-19 14:50:13 -0400, Robert Haas wrote: > On Tue, Sep 19, 2023 at 12:56 PM Andres Freund wrote: > > Yea, a setting like what's discussed here seems, uh, not particularly useful > > for achieving the goal of compacting tables. I don't think guiding this > > through SQL makes a lot of

Re: GenBKI emits useless open;close for catalogs without rows

Hi, On 2023-09-19 21:05:41 +0300, Heikki Linnakangas wrote: > On 18/09/2023 17:50, Matthias van de Meent wrote: > > (initdb takes about 73ms locally with syncing disabled) > > That's impressive. It takes about 600 ms on my laptop. Of which about 140 ms > goes into processing the BKI file. And that

Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }

On Tue, 2023-09-19 at 20:23 -0700, Maciek Sakrejda wrote: > On Tue, Sep 19, 2023 at 5:56 PM Jeff Davis wrote: > > ... > > On Tue, 2023-09-19 at 11:41 -0400, Robert Haas wrote: > > > That leads to a second idea, which is having it continue > > > to be a GUC but only affect directly-entered SQL, wit

Re: Index range search optimization

On Thu, Sep 21, 2023 at 5:11 AM Pavel Borisov wrote: > I looked at the patch code and I agree with this optimization. > Implementation also looks good to me except change : > + if (key->sk_flags & (SK_BT_REQFWD | SK_BT_REQBKWD) && > + !(key->sk_flags & SK_ROW_HEADER)) > + requiredDir = true; > ...

Re: Eliminate redundant tuple visibility check in vacuum

On Thu, Sep 21, 2023 at 3:53 PM Robert Haas wrote: > > > static int heap_prune_chain(Buffer buffer, > > >OffsetNumber > > > rootoffnum, > > > + int8 *htsv, > > >

Re: Eliminate redundant tuple visibility check in vacuum

[ sorry for the delay getting back to this ] On Wed, Sep 13, 2023 at 1:29 PM Andres Freund wrote: > I think it might be worth making the names a bit less ambiguous than they > were. It's a bit odd that one has "new" in the name, the other doesn't, > despite both being about newly marked things. A

Re: how to do profile for pg?

Hi, This talk from Andres seems to have some relevant information for you: https://www.youtube.com/watch?v=HghP4D72Noc -- David Geier (ServiceNow)

Re: Questions about the new subscription parameter: password_required

On Thu, Sep 21, 2023 at 8:03 AM Benoit Lobréau wrote: > I am confused about the new subscription parameter: password_required. > > I have two instances. The publisher's pg_hba is configured too allow > connections without authentication. On the subscriber, I have an > unprivileged user with pg_cre

Re: CREATE FUNCTION ... SEARCH { DEFAULT | SYSTEM | SESSION }

On Tue, Sep 19, 2023 at 7:56 PM Jeff Davis wrote: > Good documentation includes some guidance. Sure, it should describe the > system behavior, but without anchoring it to some kind of expected use > case, it can be equally frustrating. Fair. > I don't mean to set some major new standard in the d

Re: how to do profile for pg?

This talk by D. Dolgov https://www.postgresql.eu/events/pgconfeu2022/sessions/session/3861/slides/325/Dmitrii_Dolgov_PGConf_EU_2022.pdf might be insightful. Or not, because you need to fill in a lot of blanks. Maybe you can find a recording of that talk somewhere, if you're lucky. -- Álvaro He

Re: how to do profile for pg?

Hi, > but I need a quick demo to see the memory profiling or CPU profiling. I hope > a blog or a video which is better for me. Thanks Well, then I guess you better hurry with reading these books :) There is no shortcut I'm afraid. One of the first things that Brendan explains is how to do benc

Re: New WAL record to detect the checkpoint redo location

On Thu, Sep 21, 2023 at 4:22 AM Amit Kapila wrote: > After the 0003 patch, do we need acquire exclusive lock via > WALInsertLockAcquireExclusive() for non-shutdown checkpoints. Even the > comment "We must block concurrent insertions while examining insert > state to determine the checkpoint REDO p

Re: how to do profile for pg?

but I need a quick demo to see the memory profiling or CPU profiling.  I hope a blog or a video which is better for me. Thanks. Replied Message From mailto:aleksan...@timescale.com"; >Aleksander Alekseev

Re: how to do profile for pg?

Hi jacktby, PostgreSQL is literally a large and complicated program in C. Thus it can be profiled as such. E.g. you can use `perf` and build flamegraphs using `perf record`. Often pgbench is an adequate tool to compare before and after results.There are many other tools available depending on what

Re: Infinite Interval

On Wed, Sep 20, 2023 at 8:23 PM Dean Rasheed wrote: > > On Wed, 20 Sept 2023 at 15:00, Ashutosh Bapat > wrote: > > > > 0005 - Refactored Jian's code fixing window functions. Does not > > contain the changes for serialization and deserialization. Jian, > > please let me know if I have missed anyth

RE: [PoC] pg_upgrade: allow to upgrade publisher node

Dear Bharath, Thank you for reviewing! Before addressing them, I would like to reply some comments. > 6. > +if (PQntuples(res) != 1) > +pg_fatal("could not count the number of logical replication slots"); > + > > Not existing a single logical replication slot an error? I think it >

how to do profile for pg?

Re: Allow specifying a dbname in pg_basebackup connection string

> On 18 Sep 2023, at 14:11, Daniel Gustafsson wrote: > Unless something sticks out in a second pass over it I will go ahead and apply > it. And applied, closing the CF entry. -- Daniel Gustafsson

Re: Add 'worker_type' to pg_stat_subscription

On Thu, Sep 21, 2023 at 5:36 AM Peter Smith wrote: > > On Thu, Sep 21, 2023 at 9:34 AM Nathan Bossart > wrote: > > > > On Thu, Sep 21, 2023 at 08:14:23AM +0900, Michael Paquier wrote: > > > On Thu, Sep 21, 2023 at 09:01:01AM +1000, Peter Smith wrote: > > >> One question -- the patch comment stil

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Thu, Sep 21, 2023 at 4:57 PM Bharath Rupireddy wrote: > > On Wed, Sep 20, 2023 at 7:20 PM Hayato Kuroda (Fujitsu) > wrote: > > > > Good catch, I could not notice because it worked well in my RHEL. Here is > > the > > updated version. > > Thanks for the patch. I have some comments on v42: > >

Re: Index range search optimization

On Thu, 21 Sept 2023 at 15:17, Alexander Korotkov wrote: > > On Wed, Sep 20, 2023 at 5:07 PM Alexander Korotkov > wrote: > > On Tue, Sep 19, 2023 at 1:48 AM Peter Geoghegan wrote: > > This also makes sense. I've rephrased the comment. > > The revised patch is attached. It contains better comm

Re: pg_stat_statements and "IN" conditions

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: not tested Documentation:tested, passed I've tested the patched on 17devel/master and it is my feeling -

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Wed, Sep 20, 2023 at 7:20 PM Hayato Kuroda (Fujitsu) wrote: > > Good catch, I could not notice because it worked well in my RHEL. Here is the > updated version. Thanks for the patch. I have some comments on v42: 1. +{ oid => '8046', descr => 'for use by pg_upgrade', + proname => 'binary_upgr

Re: Comment about set_join_pathlist_hook()

Hi, On Wed, Sep 20, 2023 at 7:49 PM David Rowley wrote: > On Wed, 20 Sept 2023 at 22:06, Etsuro Fujita wrote: > > So I would like to propose to extend the comment to explain what they > > can do, as in the comment about set_rel_pathlist_hook() in allpaths.c. > > Attached is a patch for that. > >

RE: [PoC] pg_upgrade: allow to upgrade publisher node

Dear Hackers, > Good catch, I could not notice because it worked well in my RHEL. Here is the > updated version. I did some cosmetic changes for the patch, the functionality was not changed. E.g., a macro function was replaced to an inline. Note that cfbot got angry to old patch, but it seemed t

Re: Add 'worker_type' to pg_stat_subscription

On Thu, Sep 21, 2023 at 1:00 AM Nathan Bossart wrote: > > On Tue, Sep 19, 2023 at 08:36:35AM +0530, Amit Kapila wrote: > > I am of the opinion that worker_type should be 'apply' instead of > > 'leader apply' because even when it is a leader for parallel apply > > worker, it could perform individua

Questions about the new subscription parameter: password_required

Hi, I am confused about the new subscription parameter: password_required. I have two instances. The publisher's pg_hba is configured too allow connections without authentication. On the subscriber, I have an unprivileged user with pg_create_subscription and CREATE on the database. I tried u

Re: Guiding principle for dropping LLVM versions?

> On 21 Sep 2023, at 07:28, Tom Lane wrote: > > Thomas Munro writes: >> I wonder if there is a good way to make this sort of thing more >> systematic. If we could agree on a guiding principle vaguely like the >> above, then perhaps we just need a wiki page that lists relevant >> distributions,

Re: Index range search optimization

On Wed, Sep 20, 2023 at 5:07 PM Alexander Korotkov wrote: > On Tue, Sep 19, 2023 at 1:48 AM Peter Geoghegan wrote: > This also makes sense. I've rephrased the comment. The revised patch is attached. It contains better comments and the commit message. Peter, could you please check if you're OK

Re: pg_upgrade and logical replication

On Thu, Sep 21, 2023 at 4:39 AM Michael Paquier wrote: > > On Wed, Sep 20, 2023 at 04:54:36PM +0530, Amit Kapila wrote: > > Also, the patch seems to be allowing subscription relations from PG > > >=10 to be migrated but how will that work if the corresponding > > publisher is also upgraded without

Re: pg_upgrade and logical replication

On Thu, Sep 21, 2023 at 11:37 AM Michael Paquier wrote: > > On Wed, Sep 20, 2023 at 04:54:36PM +0530, Amit Kapila wrote: > > Is the check to ensure remote_lsn is valid correct in function > > check_for_subscription_state()? How about the case where the apply > > worker didn't receive any change bu

Re: New WAL record to detect the checkpoint redo location

On Thu, Sep 21, 2023 at 7:05 AM Robert Haas wrote: > > On Mon, Sep 18, 2023 at 2:57 PM Robert Haas wrote: > > I've been brainstorming about this today, trying to figure out some > > ideas to make it work. > > Here are some patches. > > 0001 refactors XLogInsertRecord to unify a couple of separate

Re: remaining sql/json patches

I keep looking at 0001, and in the struct definition I think putting the escontext at the bottom is not great, because there's a comment a few lines above that says "XXX: following fields only needed during "compilation"), could be thrown away afterwards". This comment is not strictly true, becaus

Re: should frontend tools use syncfs() ?

On Wed, 20 Sept 2023 at 22:08, Nathan Bossart wrote: > I think we should also consider renaming things like SYNC_METHOD_FSYNC to > WAL_SYNC_METHOD_FSYNC, and sync_method_options to wal_sync_method_options. > I've already rename sync_method_options in previous patch. 34 @@ -171,7 +171,7 @@ stati

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Thu, Sep 21, 2023 at 1:10 PM Michael Paquier wrote: > > On Wed, Sep 20, 2023 at 11:28:33AM +, Hayato Kuroda (Fujitsu) wrote: > > Good catch, I could not notice because it worked well in my RHEL. Here is > > the > > updated version. > > I am getting slowly up to date with this patch.. But

Re: Array initialisation notation in syscache.c

On 31.03.23 04:16, Thomas Munro wrote: From the light relief department, here is some more variadic macrology: - tp = SearchSysCache1(TSPARSEROID, ObjectIdGetDatum(prsId)); + tp = SearchSysCache(TSPARSEROID, ObjectIdGetDatum(prsId)); I'm worried that if we are removing the variant

Re: Bug fix in vacuumdb --buffer-usage-limit xxx -Z

On 2023-09-21 14:50, David Rowley wrote: I've pushed your patch plus some additional code to escape the text specified in --buffer-usage-limit before passing it to the server in commit 5cfba1ad6 Thanks again for the report. Thank you for the commit. I didn't notice about the escaping and it s

Re: CI: Unfamiliar error while testing macOS

> On 21 Sep 2023, at 05:25, Hayato Kuroda (Fujitsu) > wrote: > > Dear hackers, > > While developing my patch, I found that the CI for macOS failed with unknown > error [1]. > Do you know the reason why it happened? Please tell me if you have > workarounds... > > It failed the test at "Upload

Re: bug fix and documentation improvement about vacuumdb

> On 21 Sep 2023, at 03:53, Kuwamura Masaki > wrote: > When sending an update, please include the previous patch as well with your > new > tests as a 0002 patch in a patchset. The CFBot can only apply and build/test > patches when the entire patchset is attached to the email. The below > test

Re: Make --help output fit within 80 columns per line

On 31.08.23 09:47, torikoshia wrote: BTW, psql --help outputs the content of PGHOST, which caused a failure in the test: ``` -h, --host=HOSTNAME  database server host or socket directory (default: "/var/folders/m7/9snkd5b54cx_b4lxkl9ljlccgn/T/LobrmSUf7t") ```

Re: [PoC] pg_upgrade: allow to upgrade publisher node

On Wed, Sep 20, 2023 at 11:28:33AM +, Hayato Kuroda (Fujitsu) wrote: > Good catch, I could not notice because it worked well in my RHEL. Here is the > updated version. I am getting slowly up to date with this patch.. But before going in depth with more review, there is something that I got to