Re: unlogged sequences

On Thu, Mar 31, 2022 at 12:36 PM Tomas Vondra wrote: > On 3/31/22 19:35, David G. Johnston wrote: > > On Thu, Mar 31, 2022 at 9:28 AM Andres Freund > <mailto:and...@anarazel.de>> wrote: > > > > I agree it makes sense to have logged sequences with unlogged

Re: unlogged sequences

On Thu, Mar 31, 2022 at 9:28 AM Andres Freund wrote: > I agree it makes sense to have logged sequences with unlogged tables. We > should call out the behavioural change somewhere prominent in the release > notes. > > We can/do already support that unlikely use case by allowing one to remove the O

Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Wed, Mar 30, 2022 at 1:39 PM Andres Freund wrote: > Hi, > > On 2022-03-30 12:29:51 -0700, David G. Johnston wrote: > > On Wednesday, March 30, 2022, Andres Freund wrote: > > > My current proposal is to just have two reset times. One for the > contents > >

Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Wednesday, March 30, 2022, Andres Freund wrote: > > My current proposal is to just have two reset times. One for the contents > of > pg_stat_database (i.e. not affected by pg_stat_reset_single_*_counters()), > and > one for stats within the entire database. > > What IS it affected by? And doe

Re: Returning multiple rows in materialized mode inside the extension

On Wed, Mar 30, 2022 at 9:01 AM Piotr Styczyński wrote: > I don’t know if this mailing list is a good place to ask this question, > but if it’s not, just correct me. > pgsql-general is probably better > *The problem:* > > We currently have a one-to-many function (an operation that produces > mu

Re: Granting SET and ALTER SYSTE privileges for GUCs

On Wed, Mar 30, 2022 at 8:46 AM Tom Lane wrote: > I don't want to do that with > a blunderbuss, but perhaps there's an argument to do it for specific > cases (search_path comes to mind, though the performance cost could be > significant, since I think setting that in function SET clauses is > com

Re: Granting SET and ALTER SYSTE privileges for GUCs

On Wed, Mar 30, 2022 at 8:12 AM Andrew Dunstan wrote: > > On 3/30/22 09:26, Tom Lane wrote: > > > > > What this loses is the ability to revoke public SET permissions > > on USERSET GUCs. I claim that that is not so valuable as to > > justify all the complication needed to deal with it. Agreed,

Re: Granting SET and ALTER SYSTE privileges for GUCs

On Tue, Mar 29, 2022 at 9:00 PM Mark Dilger wrote: > A grant or revoke on an unrecognized custom parameter will create a SUSET > placeholder, [...] > which cleans up the problem, with one exception: if the user executes a > "revoke set on parameter some.such from public" prior to loading the

Re: [PATCH] Full support for index LP_DEAD hint bits on standby

On Tue, Mar 22, 2022 at 6:52 AM Michail Nikolaev wrote: > Hello, Andres. > > > Fails to apply at the moment: http://cfbot.cputube.org/patch_37_2947.log > > Thanks for notifying me. BTW, some kind of automatic email in case of > status change could be very helpful. > > > Marked as waiting for auth

Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Tue, Mar 29, 2022 at 5:56 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tue, Mar 29, 2022 at 5:50 PM Andres Freund wrote: > >> Hi, >> >> On 2022-03-29 17:06:24 -0700, David G. Johnston wrote: >> > On Tue, Mar 29, 2022 at 4:43 PM A

Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Tue, Mar 29, 2022 at 5:50 PM Andres Freund wrote: > Hi, > > On 2022-03-29 17:06:24 -0700, David G. Johnston wrote: > > On Tue, Mar 29, 2022 at 4:43 PM Andres Freund > wrote: > > > But more importantly, a > > > per-relation/function reset field wouldn'

Re: [PATCH] Full support for index LP_DEAD hint bits on standby

On Tue, Mar 29, 2022 at 5:20 PM Peter Geoghegan wrote: > On Tue, Mar 29, 2022 at 4:55 AM Michail Nikolaev > wrote: > > I think that you could do a better job of explaining and promoting the > problem that you're trying to solve here. Emphasis on the problem, not > so much the solution. As a sp

Re: Temporary tables versus wraparound... again

On Tue, Mar 29, 2022 at 4:52 PM Greg Stark wrote: > On Mon, 28 Mar 2022 at 16:30, Andres Freund wrote: > > > > > Make ON COMMIT DELETE ROWS reset relfrozenxmin and other table > stats > > > like normal truncate. Otherwise even typical short-lived > transactions > > > using temporary

Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Tue, Mar 29, 2022 at 4:43 PM Andres Freund wrote: > But more importantly, a > per-relation/function reset field wouldn't address Tomas's concern: He > wants a > single thing to check to see if any stats have been reset - and that's imo > a > quite reasonable desire. > Per the original email:

Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Tue, Mar 29, 2022 at 1:37 PM Andres Freund wrote: > > Secondly, to do anything really meaningful you need to calculate deltas, > > and be able to detect if some of the stats were reset for the particular > > interval. And the stat_reset timestamp was designed to be a simple way > > to detect t

Re: Document atthasmissing default optimization avoids verification table scan

On Sun, Mar 27, 2022 at 11:17 AM James Coleman wrote: > Hmm, I didn't realize that was project policy, Guideline/Rule of Thumb is probably a better concept. > but I'm a bit > surprised given that the sentence which 0001 replaces seems like a > direct violation of that also: "In neither case

Re: Document atthasmissing default optimization avoids verification table scan

On Sun, Mar 27, 2022 at 10:00 AM James Coleman wrote: > As shown above, table scans (and specifically table scans used to > validate constraints, which is what this patch is about) are clearly > documented (more than once!) in the ALTER TABLE documentation. In fact > it's documented specifically

Re: Document atthasmissing default optimization avoids verification table scan

On Sat, Mar 26, 2022 at 4:36 PM Tom Lane wrote: > "David G. Johnston" writes: > > Or, we can leave it where things are and make sure the reader understands > > there are two paths to having a NOT NULL constraint on the newly added > > column. Something like: >

Re: Document atthasmissing default optimization avoids verification table scan

On Sat, Mar 26, 2022 at 4:14 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > > I would suggest rewriting 0001 to target ALTER COLUMN instead of in the > generic notes section (in the paragraph beginning "Adding a column with a > volatile DEFAULT") for the d

Re: Document atthasmissing default optimization avoids verification table scan

On Sat, Mar 26, 2022 at 3:25 PM James Coleman wrote: > On Fri, Mar 25, 2022 at 4:40 PM Robert Haas wrote: > > > > On Tue, Jan 25, 2022 at 8:49 AM James Coleman wrote: > > > Here's a version that looks like that. I'm not convinced it's an > > > improvement over the previous version: again, I exp

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Sat, Mar 26, 2022 at 1:53 AM Laetitia Avrot wrote: > Hello all, > > Le sam. 26 mars 2022 à 01:13, Michael Paquier a > écrit : > >> On Fri, Mar 25, 2022 at 10:09:33PM +0100, Daniel Gustafsson wrote: >> > Agreed. In this case it seems that adding --exclude-extension would >> make sense >> > to

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Fri, Mar 25, 2022 at 2:55 PM Tom Lane wrote: > Daniel Gustafsson writes: > >> On 25 Mar 2022, at 19:37, Tom Lane wrote: > >> I'd vote for changing the behavior of --table rather than trying to > >> be bug-compatible with this decision. > > > Agreed. Question is what to do for "-t pg_class",

Re: Document atthasmissing default optimization avoids verification table scan

On Fri, Mar 25, 2022 at 1:40 PM Robert Haas wrote: > On Tue, Jan 25, 2022 at 8:49 AM James Coleman wrote: > > Here's a version that looks like that. I'm not convinced it's an > > improvement over the previous version: again, I expect more advanced > > users to already understand this concept, an

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Friday, March 25, 2022, Tom Lane wrote: > "David G. Johnston" writes: > > On Fri, Mar 25, 2022 at 10:57 AM Tom Lane wrote: > >> pg_dump never dumps system objects, so I don't see a need for > >> a switch to tell it not to. > > > I consider

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Fri, Mar 25, 2022 at 10:57 AM Tom Lane wrote: > "David G. Johnston" writes: > > > Except succinctly > > omitting system objects which should get its own general option. > pg_dump never dumps system objects, so I don't see a need for > a switch to tell

Re: Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Fri, Mar 25, 2022 at 9:44 AM Laetitia Avrot wrote: > > Actually, I thought of it after the --schema-only flag (which is kind of > confusing, because it won't export only schema creation DDL). > --schema-only is talking about the different sections of the dump file, not namespace schema object

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Thu, Mar 24, 2022 at 5:40 PM Tom Lane wrote: > "David G. Johnston" writes: > > The extension object type does not seem to have gotten the > > --exclude-extension capability that it would need to conform to the > general > > design exemplified by --table

Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Mon, Jan 24, 2022 at 10:49 PM Michael Paquier wrote: > What about patterns? Switches like --table or > --extension are able to digest a psql-like pattern to decide which > objects to dump. > The extension object type does not seem to have gotten the --exclude-extension capability that it wou

Re: Re: pg_dump new feature: exporting functions only. Bad or good idea ?

On Thu, Mar 24, 2022 at 4:42 PM Chapman Flack wrote: > On 03/27/21 08:57, Andrew Dunstan wrote: > > We can bikeshed the name of the flag at some stage. --procedures-only > > might also make sense > > Any takers for --routines-only ? > > "Routine" is the genuine, ISO SQL umbrella term for a functi

Re: pg_stat_reset_single_*_counters vs pg_stat_database.stats_reset

On Wed, Mar 23, 2022 at 5:55 PM Andres Freund wrote: > > Starting with the below commit, pg_stat_reset_single_function_counters, > pg_stat_reset_single_table_counters don't just reset the stats for the > individual function, but also set pg_stat_database.stats_reset. > > commit 4c468b37a281941afd

Re: Window Function "Run Conditions"

On Tue, Mar 22, 2022 at 3:39 PM David Rowley wrote: > On Thu, 17 Mar 2022 at 17:04, Corey Huinker > wrote: > > It seems like this effort would aid in implementing what some other > databases implement via the QUALIFY clause, which is to window functions > what HAVING is to aggregate functions. >

Re: pg14 psql broke \d datname.nspname.relname

On Tue, Mar 15, 2022 at 12:31 PM Mark Dilger wrote: > > > On Mar 15, 2022, at 12:27 PM, Robert Haas wrote: > > > > - Justin Pryzby, who originally discovered the problem, prefers the > > same behavior that I prefer long-term, but thinks Tom's behavior is > > better than doing nothing. > > - Mark

Re: role self-revocation

On Fri, Mar 11, 2022 at 8:32 AM Stephen Frost wrote: > > Such scripts as will break will still > break in a pretty clear way with a clear answer as to how to fix them > and I don't think there's some kind of data corruption or something that > would happen. > > I largely agree and am perfectly fi

Re: role self-revocation

On Fri, Mar 11, 2022 at 6:55 AM Robert Haas wrote: > On Thu, Mar 10, 2022 at 5:14 PM Tom Lane wrote: > > This seems reasonable in isolation, but > > > > (1) it implies a persistent relationship between creating and created > > roles. Whether you want to call that ownership or not, it sure walks

Re: role self-revocation

On Thu, Mar 10, 2022 at 3:01 PM Robert Haas wrote: > On Thu, Mar 10, 2022 at 4:00 PM David G. Johnston > wrote: > > I dislike changing the documented behavior of CREATEROLE to the degree > suggested here. However, there are three choices here, only one of which > can be

Re: role self-revocation

On Thu, Mar 10, 2022 at 12:58 PM Stephen Frost wrote: > I don't think we're that far from having all of these though. To start > with, we remove from CREATEROLE the random things that it does which go > beyond what folks tend to expect- remove the whole 'grant any role to > any other' stuff, rem

Re: role self-revocation

On Thu, Mar 10, 2022 at 12:45 PM Stephen Frost wrote: > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > On Thu, Mar 10, 2022 at 11:05 AM Stephen Frost > wrote: > Why not just look at the admin_option field of pg_auth_members...? I > don't get why that

Re: role self-revocation

On Thu, Mar 10, 2022 at 11:05 AM Stephen Frost wrote: > Greetings, > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > On Thu, Mar 10, 2022 at 9:19 AM Stephen Frost > wrote: > > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > > >

Re: role self-revocation

On Thu, Mar 10, 2022 at 9:19 AM Stephen Frost wrote: > Greetings, > > * David G. Johnston (david.g.johns...@gmail.com) wrote: > > On Thu, Mar 10, 2022 at 7:46 AM Robert Haas > wrote: > > > On Wed, Mar 9, 2022 at 4:31 PM Tom Lane wrote: > > > > I don'

Re: role self-revocation

On Thu, Mar 10, 2022 at 7:46 AM Robert Haas wrote: > On Wed, Mar 9, 2022 at 4:31 PM Tom Lane wrote: > > I don't think we need syntax to describe it. As I just said in my > > other reply, we have a perfectly good precedent for this already > > in ordinary object permissions. That is: an object

Re: role self-revocation

On Wed, Mar 9, 2022 at 2:31 PM Tom Lane wrote: > Robert Haas writes: > > Well, the problem is that as far as I can see, the admin option is an > > optional feature of membership. You can grant someone membership > > without admin option, or with admin option, but you can't grant them > > the adm

Re: Naming of the different stats systems / "stats collector"

On Tue, Mar 8, 2022 at 7:32 PM Andres Freund wrote: > we need a descriptive term / shorthand that > describes the type of statistics we currently send to the stats collector. > > "cumulative stats subsystem"? > > I'm growing fond of "cumulative". It is more precise (and restrictive) than "metric

Re: Naming of the different stats systems / "stats collector"

On Tue, Mar 8, 2022 at 6:50 PM Andres Freund wrote: > On 2022-03-08 15:55:04 -0700, David G. Johnston wrote: > > On Tue, Mar 8, 2022 at 1:54 PM Andres Freund wrote: > > > One thing I'm not yet happy around the shared memory stats patch is > > > naming. Curren

Re: Naming of the different stats systems / "stats collector"

On Tue, Mar 8, 2022 at 1:54 PM Andres Freund wrote: > > One thing I'm not yet happy around the shared memory stats patch is > naming. Currently a lot of comments say things like: > > * [...] We convert to > * microseconds in PgStat_Counter format when transmitting to the > collector. > > or >

Re: Doc about how to set max_wal_senders when setting minimal wal_level

On Fri, Mar 4, 2022 at 2:49 AM Japin Li wrote: > Thanks for your review. Modified. > Works for me. I have some additional sparks of ideas but nothing that need hold this up. David J.

Re: Doc about how to set max_wal_senders when setting minimal wal_level

On Thu, Mar 3, 2022 at 11:05 PM Kyotaro Horiguchi wrote: > But, > anyway, IMHO, it is mere a performance tips that is not necessarily > required in this section, or even in this documentaiotn. Addtion to > that, if we write this for max_wal_senders, archive_mode will deserve > the similar tips b

Re: role self-revocation

On Mon, Mar 7, 2022 at 1:16 PM Tom Lane wrote: > Based on Robert's archaeological dig, it now seems that the fact that > we have any such behavior at all was just a mistake. What would be > lost if we drop it? > Probably nothing that couldn't be replaced, and with a better model, but I do have

Re: role self-revocation

On Mon, Mar 7, 2022 at 11:18 AM Robert Haas wrote: > In terms of how > things work today, see Joshua Brindle's email about the use of groups > in pg_hba.conf. That is an excellent example of how removing oneself > from a group could enable one to bypass security restrictions intended > by the DBA

Re: role self-revocation

On Mon, Mar 7, 2022 at 11:18 AM Robert Haas wrote: > On Sun, Mar 6, 2022 at 11:01 PM David G. Johnston > wrote: > > The example, which you moved here, then attempts to demonstrate this > "fact" but gets it wrong. Boss became a member of peon so if you want to > dem

Re: role self-revocation

On Mon, Mar 7, 2022 at 9:04 AM Tom Lane wrote: > Just looking at it now, without having done any historical research, > I wonder why it is that we don't attach significance to WITH ADMIN > OPTION being granted to the role itself. It seems like the second > part of that sentence is effectively sa

Re: role self-revocation

On Mon, Mar 7, 2022 at 8:37 AM Robert Haas wrote: > A role is not considered to hold WITH > ADMIN OPTION on itself, but it may grant or revoke membership in > itself from a database session where the session user matches the > role." > > Is there some use case for the behavior described in that l

Re: role self-revocation

On Sun, Mar 6, 2022 at 8:19 AM Robert Haas wrote: > The choice of names in my example wasn't accidental. If the granted > role is a login role, then the superuser's intention was to vest the > privileges of that role in some other role, and it is surely not right > for that role to be able to dec

Re: role self-revocation

On Sun, Mar 6, 2022 at 9:53 AM Tom Lane wrote: > Robert Haas writes: > > ... Suppose the superuser grants "admin" to both "joe" and "sally". > > Now "joe" can SET ROLE to "admin" and revoke it from "sally", and the > > superuser has no tool to prevent this. > > Really? > > regression=# grant adm

Re: role self-revocation

On Fri, Mar 4, 2022 at 1:50 PM Robert Haas wrote: > On Mon, Feb 28, 2022 at 2:09 PM Stephen Frost wrote: > > The ability of a role to revoke itself from some other role is just > > something we need to accept as being a change that needs to be made, and > > I do believe that such a change is sup

Re: Doc about how to set max_wal_senders when setting minimal wal_level

On Wed, Mar 2, 2022 at 7:44 PM Japin Li wrote: > > Hi, hackers > > When I try to change wal_level to minimal and restart the database, it > complains > max_wal_senders > 0. > > 2022-03-03 10:10:16.938 CST [6389] FATAL: WAL streaming (max_wal_senders > > 0) requires wal_level "replica" or "logica

Re: Document ordering guarantees on INSERT/UPDATE RETURNING clause

On Sat, Feb 26, 2022 at 5:42 AM Shay Rojansky wrote: > FWIW I've received feedback from a SQL Server engineer that one definitely > should *not* depend on such ordering there, and that future optimizations > (e.g. parallel insertion of many rows) could result in row ordering which > differs from

Re: Design of pg_stat_subscription_workers vs pgstats

On Sun, Feb 20, 2022 at 10:10 PM Amit Kapila wrote: > On Sat, Feb 19, 2022 at 10:35 PM David G. Johnston > wrote: > > > > On Sat, Feb 19, 2022 at 9:37 AM Andres Freund > wrote: > >> > >> IMO the type of information you'd want for apply failures i

Re: Design of pg_stat_subscription_workers vs pgstats

On Mon, Feb 21, 2022 at 2:19 AM Amit Kapila wrote: > On Mon, Feb 21, 2022 at 1:18 PM Andres Freund wrote: > > > > The view name could be pg_stat_subscription_lrep, > > > pg_stat_logical_replication, or something on those lines. > > > > pg_stat_subscription_stats :) > > > > Having *stat* two time

Re: Design of pg_stat_subscription_workers vs pgstats

On Sat, Feb 19, 2022 at 9:37 AM Andres Freund wrote: > IMO the type of information you'd want for apply failures is substantially > different enough from worker failures that I don't really see the temptation > to put them in the same table. > > It's an error message and a transaction LSN in both

Re: Design of pg_stat_subscription_workers vs pgstats

On Sat, Feb 19, 2022 at 9:02 AM Andres Freund wrote: > > Even leaving everything else aside, a key of (dboid, subid, subrelid), > where > subrelid can be NULL, but where (dboid, subid) is *not* unique, imo is poor > relational design. What is the justification for mixing relation specific > and

Re: Design of pg_stat_subscription_workers vs pgstats

On Saturday, February 19, 2022, Amit Kapila wrote: > On Sat, Feb 19, 2022 at 1:17 AM David G. Johnston > wrote: > > > > On Fri, Feb 18, 2022 at 1:26 AM Masahiko Sawada > wrote: > >> > >> > >> Here is the summary of the discussion, changes, an

Re: Design of pg_stat_subscription_workers vs pgstats

On Fri, Feb 18, 2022 at 1:26 AM Masahiko Sawada wrote: > > Here is the summary of the discussion, changes, and plan. > > 1. Move some error information such as the error message to a new > system catalog, pg_subscription_error. The pg_subscription_error table > would have the following columns: >

Re: Storage for multiple variable-length attributes in a single row

On Mon, Feb 7, 2022 at 9:58 AM Esteban Zimanyi wrote: > > As suggested by David, this goes beyond the "traditional" usage of > PostgreSQL. Therefore my questions are > * What is the suggested strategy to splitting these 2K attributes into > vertically partitioned tables where the tables are linke

Re: Storage for multiple variable-length attributes in a single row

On Mon, Feb 7, 2022 at 8:44 AM Esteban Zimanyi wrote: > May I kindly ask your insight about a question I posted 1 month ago and > for which I never received any answer ? > -hackers really isn't the correct place for usage questions like this - even if you are creating a custom type (why you are

Re: Design of pg_stat_subscription_workers vs pgstats

On Wednesday, February 2, 2022, Masahiko Sawada wrote: > and have other error > information in pg_stat_subscription_workers view. > What benefit is there to keeping the existing collector-based pg_stat_subscripiton_workers view? If we re-write it using shmem IPC then we might as well put everyt

Re: Unclear problem reports

On Wed, Feb 2, 2022 at 5:35 PM Bruce Momjian wrote: > I consider these as problems that need digging to find the cause, and > users are usually unable to do sufficient digging, and we don't have > time to give them instructions, so they never get a reply. > > Is there something we can do to impro

Re: warn if GUC set to an invalid shared library

On Tue, Feb 1, 2022 at 11:06 PM Maciek Sakrejda wrote: > I tried running ALTER SYSTEM and got the warnings as expected: > > postgres=# alter system set shared_preload_libraries = > no_such_library,not_this_one_either; > WARNING: could not access file "$libdir/plugins/no_such_library" > WARNING:

Re: Design of pg_stat_subscription_workers vs pgstats

On Wed, Feb 2, 2022 at 5:08 AM Amit Kapila wrote: > On Wed, Feb 2, 2022 at 1:06 PM David G. Johnston > wrote: > > ... > > > > I already explained that the concept of err_cnt is not useful. The fact > that you include it here makes me think you are still thinking

Re: Design of pg_stat_subscription_workers vs pgstats

On Tue, Feb 1, 2022 at 11:55 PM Amit Kapila wrote: > On Wed, Feb 2, 2022 at 9:41 AM David G. Johnston > wrote: > > > > On Tue, Feb 1, 2022 at 8:07 PM Amit Kapila > wrote: > >> > >> On Tue, Feb 1, 2022 at 11:47 AM Masahiko Sawada > wrote: > >

Re: Design of pg_stat_subscription_workers vs pgstats

On Tue, Feb 1, 2022 at 8:07 PM Amit Kapila wrote: > On Tue, Feb 1, 2022 at 11:47 AM Masahiko Sawada > wrote: > > > > > I see that it's better to use a better IPC for ALTER SUBSCRIPTION SKIP > > feature to pass error-XID or error-LSN information to the worker > > whereas I'm also not sure of the

Re: substring odd behavior

On Thu, Jan 27, 2022 at 7:22 PM Regina Obe wrote: > Is this intentional behavior? > > -- I can do this > SELECT substring('3.2.0' from '[0-9]*\.([0-9]*)\.'); > > -- But can't do this gives error syntax error at or near "from" > SELECT pg_catalog.substring('3.2.0' from '[0-9]*\.([0-9]*)\.'); > > s

Re: Design of pg_stat_subscription_workers vs pgstats

On Thu, Jan 27, 2022 at 2:15 PM Andres Freund wrote: > Another related thing is that using a 32bit xid for allowing skipping is a > bad > idea anyway - we shouldn't adding new interfaces with xid wraparound > dangers - > it's getting more and more common to have multiple wraparounds a day. An >

Re: Design of pg_stat_subscription_workers vs pgstats

On Thu, Jan 27, 2022 at 5:08 AM Amit Kapila wrote: > On Thu, Jan 27, 2022 at 11:16 AM Andres Freund wrote: > > > > On 2022-01-25 20:27:07 +0900, Masahiko Sawada wrote: > > > > > There will be some challenges in a case where updating > pg_subscription_rel > > > also failed too (what to report to

Re: Output clause for Upsert aka INSERT...ON CONFLICT

On Thursday, January 27, 2022, Anand Sowmithiran wrote: > > However, the MS SQL server MERGE command also does 'delete' using the > 'when not matched' clause, is there an equivalent ? > PostgreSQL does not have a merge command feature. Just the subset of behavior that is INSERT…on conflict Davi

Re: Output clause for Upsert aka INSERT...ON CONFLICT

On Wednesday, January 26, 2022, Anand Sowmithiran wrote: > The INSERT...ON CONFLICT is used for doing upserts in one of our app. > Our app works with both MS SQL and Postgresql, based on customer needs. > > Unlike the MS SQL MERGE command's OUTPUT clause that gives the $action >

Re: Skipping logical replication transactions on subscriber side

On Tue, Jan 25, 2022 at 9:16 PM Amit Kapila wrote: > On Wed, Jan 26, 2022 at 9:36 AM Masahiko Sawada > wrote: > > On Wed, Jan 26, 2022 at 12:54 PM Amit Kapila > wrote: > > > > > > > > > Probably, we also need to consider the case where the tablesync > worker > > > > entered an error loop and th

Re: Skipping logical replication transactions on subscriber side

On Mon, Jan 24, 2022 at 12:59 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > > > 5(out). wait for the user to manually restart the replication stream >> >> Do you mean that there always is user intervention after error so the >> replication str

Re: JSONB docs patch

On Tue, Jan 25, 2022 at 3:38 PM Mikhail Dobrinin wrote: > Hello, > > I have come across some missing documentation that I think could benefit > the community. > > Several functions like `jsonb_exists`, `jsonb_exists_any`, > `jsonb_exists_all` have existed for many PG versions but were not > docum

Re: Skipping logical replication transactions on subscriber side

On Tue, Jan 25, 2022 at 8:33 AM Masahiko Sawada wrote: > Given that we cannot use rely on the pg_stat_subscription_workers view > for this purpose, we would need either a new sub-system that tracks > each logical replication status so the system can set the error XID to > subskipxid, or to wait f

Re: Skipping logical replication transactions on subscriber side

On Tue, Jan 25, 2022 at 8:09 AM Masahiko Sawada wrote: > On Tue, Jan 25, 2022 at 11:58 PM David G. Johnston > wrote: > > > > On Tue, Jan 25, 2022 at 7:47 AM Masahiko Sawada > wrote: > >> > >> Yeah, I think it's a good idea to clear the subskipxid af

Re: Skipping logical replication transactions on subscriber side

On Tue, Jan 25, 2022 at 7:47 AM Masahiko Sawada wrote: > Yeah, I think it's a good idea to clear the subskipxid after the first > transaction regardless of whether the worker skipped it. > > So basically instead of stopping the worker with an error you suggest having the worker continue applying

Re: Skipping logical replication transactions on subscriber side

On Tue, Jan 25, 2022 at 5:52 AM Peter Eisentraut < peter.eisentr...@enterprisedb.com> wrote: > On 25.01.22 06:18, Amit Kapila wrote: > > I think to avoid this we can send a message to clear this (at least to > > clear XID in the view) after skipping the xact but there is no > > guarantee that it w

Re: Skipping logical replication transactions on subscriber side

On Monday, January 24, 2022, Amit Kapila wrote: > On Mon, Jan 24, 2022 at 1:30 PM David G. Johnston > wrote: > > > > That said, at present my two dislikes: > > > > 1) ALTER SYSTEM SKIP accepts any xid value (I need to consider further > the timing of when thi

Re: Skipping logical replication transactions on subscriber side

On Sun, Jan 23, 2022 at 11:55 PM Masahiko Sawada wrote: > On Mon, Jan 24, 2022 at 1:49 PM David G. Johnston > wrote: > > > > On Sun, Jan 23, 2022 at 8:35 PM Amit Kapila > wrote: > >> > >> > I really dislike the user experience this provides, and gi

Re: Skipping logical replication transactions on subscriber side

On Sun, Jan 23, 2022 at 8:35 PM Amit Kapila wrote: > > I really dislike the user experience this provides, and given it is new > in v15 (and right now this table seems to exist solely to support this > feature) changing this seems within the realm of possibility. I have to > imagine these workers

Re: Bogus duplicate command issued in pg_dump

On Sun, Jan 23, 2022 at 7:25 PM Michael Paquier wrote: > On Sun, Jan 23, 2022 at 01:31:03PM -0500, Tom Lane wrote: > > We could consider a more global change to get rid of using > > appendPQExpBuffer where it's not absolutely necessary, so that > > there are fewer bad examples to copy. Another i

Re: Bogus duplicate command issued in pg_dump

On Sun, Jan 23, 2022 at 11:31 AM Tom Lane wrote: > > res = ExecuteSqlQueryForSingleRow(fout, upgrade_query->data); > ... > appendPQExpBuffer(upgrade_query, > "SELECT t.oid, t.typarray " > ... > res = ExecuteSqlQueryForSingleRow(fou

Re: Skipping logical replication transactions on subscriber side

On Sat, Jan 22, 2022 at 9:21 AM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Sat, Jan 22, 2022 at 2:41 AM Amit Kapila > wrote: > >> >> > Additionally, the description for pg_stat_subscription_workers should >> describe what happens

Re: Skipping logical replication transactions on subscriber side

On Sat, Jan 22, 2022 at 2:41 AM Amit Kapila wrote: > On Sat, Jan 22, 2022 at 12:41 PM David G. Johnston > wrote: > > > > On Fri, Jan 21, 2022 at 10:30 PM Amit Kapila > wrote: > >> > >> On Fri, Jan 21, 2022 at 10:00 PM David G. Johnston > >> wrot

Re: Document atthasmissing default optimization avoids verification table scan

On Saturday, January 22, 2022, James Coleman wrote: > On Sat, Jan 22, 2022 at 12:35 AM David G. Johnston > wrote: > > > > On Fri, Jan 21, 2022 at 5:14 PM James Coleman wrote: > >> > >> > >> > Really? That's horrid, because that's direc

Re: Skipping logical replication transactions on subscriber side

On Fri, Jan 21, 2022 at 10:30 PM Amit Kapila wrote: > On Fri, Jan 21, 2022 at 10:00 PM David G. Johnston > wrote: > > > > On Fri, Jan 21, 2022 at 4:55 AM Amit Kapila > wrote: > >> > >> Apart from this, I have changed a few comments and ran pgindent. Do

Re: Document atthasmissing default optimization avoids verification table scan

On Fri, Jan 21, 2022 at 5:14 PM James Coleman wrote: > > > Really? That's horrid, because that's directly useful advice. > > Remedied, but rewritten a bit to better fit with the new style/goal of > that tip). > > Version 3 is attached. > > Coming back to this after a respite I think the tip need

Re: Proposal: allow database-specific role memberships

On Fri, Jan 21, 2022 at 3:12 PM Kenaniah Cerny wrote: > The latest rebased version of the patch is attached. > As I was just reminded, we tend to avoid specifying specific PostgreSQL versions in our documentation. We just say what the current version does. Here, the note sentences at lines 62 a

Re: Document atthasmissing default optimization avoids verification table scan

On Fri, Jan 21, 2022 at 2:50 PM Tom Lane wrote: > "David G. Johnston" writes: > > On Fri, Jan 21, 2022 at 2:08 PM Andrew Dunstan > wrote: > >> I know what it's replacing refers to release 11, but let's stop doing > >> that. How about some

Re: Document atthasmissing default optimization avoids verification table scan

On Fri, Jan 21, 2022 at 2:08 PM Andrew Dunstan wrote: > On 1/21/22 13:55, James Coleman wrote: > > + Before PostgreSQL 11, adding a new > column to a > + table required rewriting that table, making it a very slow operation. > + More recent versions can sometimes optimize away this rew

Re: Document atthasmissing default optimization avoids verification table scan

On Fri, Jan 21, 2022 at 11:55 AM James Coleman wrote: > On Thu, Jan 20, 2022 at 3:43 PM James Coleman wrote: > > > > As noted earlier I expect to be posting an updated patch soon. > > Here's the updated series. In 0001 I've moved the documentation tweak > into the ALTER TABLE notes section. In 0

Re: Skipping logical replication transactions on subscriber side

On Fri, Jan 21, 2022 at 4:55 AM Amit Kapila wrote: > Apart from this, I have changed a few comments and ran pgindent. Do > let me know what you think of the changes? > The paragraph describing ALTER SUBSCRIPTION SKIP seems unnecessarily repetitive. Consider: """ Skips applying all changes of th

Re: Document atthasmissing default optimization avoids verification table scan

On Wed, Jan 19, 2022 at 6:14 PM James Coleman wrote: > I'm open to the idea of wordsmithing here, of course, but I strongly > disagree that this is irrelevant data. Ok, but wording aside, only changing a tip in the DDL - Add Table section doesn't seem like a complete fix. The notes in alter ta

Re: Document atthasmissing default optimization avoids verification table scan

On Wed, Jan 19, 2022 at 5:08 PM Bossart, Nathan wrote: > On 9/24/21, 7:30 AM, "James Coleman" wrote: > > When PG11 added the ability for ALTER TABLE ADD COLUMN to set a constant > > default value without rewriting the table the doc changes did not note > > how the new feature interplayed with AD

Re: Refactoring of compression options in pg_basebackup

On Mon, Jan 17, 2022 at 8:41 AM Alvaro Herrera wrote: > On 2022-Jan-17, Robert Haas wrote: > > > Of the two > > alternatives that you propose, I prefer --compress=["server-"]METHOD > > and --compression-level=NUMBER to having both > > --client-compression-level and --server-compression-level. To

<    2   3   4   5   6   7   8   9   10   11   >