Re: User Interface for WAL usage data

2020-04-01 Thread Kyotaro Horiguchi
At Thu, 2 Apr 2020 00:41:20 -0500, Justin Pryzby wrote in > On Thu, Apr 02, 2020 at 10:13:18AM +0530, Amit Kapila wrote: > > In thread [1], we are discussing to expose WAL usage data for each > > statement in a way quite similar to how we expose BufferUsage data. > > The way it exposes seems

Re: Allow continuations in "pg_hba.conf" files

2020-04-01 Thread Justin Pryzby
On Thu, Apr 02, 2020 at 07:25:36AM +0200, Fabien COELHO wrote: > > Hello, > > > FWIW, I don't think so. Generally a trailing backspace is an escape > > character for the following newline. And '\ ' is a escaped space, > > which is usualy menas a space itself. > > > > In this case escape

Re: WAL usage calculation patch

2020-04-01 Thread Amit Kapila
On Thu, Apr 2, 2020 at 11:07 AM Amit Kapila wrote: > > On Wed, Apr 1, 2020 at 8:00 PM Julien Rouhaud wrote: > Also, I forgot to mention that let's not base this on buffer usage patch for create index (v10-0002-Allow-parallel-index-creation-to-accumulate-buff) because as per recent discussion I

Re: User Interface for WAL usage data

2020-04-01 Thread Justin Pryzby
On Thu, Apr 02, 2020 at 10:13:18AM +0530, Amit Kapila wrote: > In thread [1], we are discussing to expose WAL usage data for each > statement in a way quite similar to how we expose BufferUsage data. > The way it exposes seems reasonable to me and no one else raises any > objection. It could be

Re: WAL usage calculation patch

2020-04-01 Thread Amit Kapila
On Wed, Apr 1, 2020 at 8:00 PM Julien Rouhaud wrote: > > On Wed, Apr 01, 2020 at 04:29:16PM +0530, Amit Kapila wrote: > > 3. Doing some testing with and without parallelism to ensure WAL usage > > data is correct would be great and if possible, share the results? > > > I just saw that Dilip did

Re: Allow continuations in "pg_hba.conf" files

2020-04-01 Thread Fabien COELHO
Hello, FWIW, I don't think so. Generally a trailing backspace is an escape character for the following newline. And '\ ' is a escaped space, which is usualy menas a space itself. In this case escape character doesn't work generally and I think it is natural that a backslash in the middle

Re: Some problems of recovery conflict wait events

2020-04-01 Thread Masahiko Sawada
On Wed, 1 Apr 2020 at 22:32, Fujii Masao wrote: > > > > On 2020/03/30 20:10, Masahiko Sawada wrote: > > On Fri, 27 Mar 2020 at 17:54, Fujii Masao > > wrote: > >> > >> > >> > >> On 2020/03/04 14:31, Masahiko Sawada wrote: > >>> On Wed, 4 Mar 2020 at 13:48, Fujii Masao > >>> wrote: > >

Re: Autovacuum vs vac_update_datfrozenxid() vs ?

2020-04-01 Thread Noah Misch
On Mon, Mar 23, 2020 at 04:50:36PM -0700, Andres Freund wrote: > I think there's also another (even larger?) race in > vac_update_datfrozenxid(): Unless I miss something, two backends can > concurrently run through the scan in vac_update_datfrozenxid() for two > different tables in the same

Re: NOT IN subquery optimization

2020-04-01 Thread Andrey Lepikhov
You should do small rebase (conflict with 911e7020770) and pgindent of the patch to repair problems with long lines and backspaces. I am reviewing your patch in small steps. Questions: 1. In the find_innerjoined_rels() routine you stop descending on JOIN_FULL node type. I think it is wrong

User Interface for WAL usage data

2020-04-01 Thread Amit Kapila
Hi, In thread [1], we are discussing to expose WAL usage data for each statement in a way quite similar to how we expose BufferUsage data. The way it exposes seems reasonable to me and no one else raises any objection. It could be that it appears fine to others who have reviewed the patch but I

Re: pg_stat_statements issue with parallel maintenance (Was Re: WAL usage calculation patch)

2020-04-01 Thread Dilip Kumar
On Thu, Apr 2, 2020 at 9:13 AM Dilip Kumar wrote: > > On Thu, Apr 2, 2020 at 8:34 AM Peter Geoghegan wrote: > > > > On Wed, Apr 1, 2020 at 7:52 PM Amit Kapila wrote: > > > Peter, Is this behavior expected? > > > > > > Let me summarize the situation so that it would be easier for Peter to > > >

Re: Allow continuations in "pg_hba.conf" files

2020-04-01 Thread Kyotaro Horiguchi
At Thu, 02 Apr 2020 00:20:12 +, David Zhang wrote in > Hi Fabien, > Should we consider the case "\ ", i.e. one or more spaces after the backslash? > For example, if I replace a user map > "mymap /^(.*)@mydomain\.com$ \1" with > "mymap /^(.*)@mydomain\.com$ \ " > "\1" > by

Re: Proposal: Expose oldest xmin as SQL function for monitoring

2020-04-01 Thread Craig Ringer
On Thu, 2 Apr 2020 at 07:57, Tom Lane wrote: > Alvaro Herrera writes: > > On 2020-Apr-01, Tom Lane wrote: > >> The fact that I had to use max(age(...)) in that sample query > >> hints at one reason: it's really hard to do arithmetic correctly > >> on raw XIDs. Dealing with wraparound is a

Re: BUG #16109: Postgres planning time is high across version (Expose buffer usage during planning in EXPLAIN)

2020-04-01 Thread Fujii Masao
On 2020/04/02 3:47, Julien Rouhaud wrote: On Wed, Apr 1, 2020 at 7:51 PM Fujii Masao wrote: On 2020/03/31 10:31, Justin Pryzby wrote: On Wed, Jan 29, 2020 at 12:15:59PM +0100, Julien Rouhaud wrote: Rebase due to conflict with 3ec20c7091e97. This is failing to apply probably since

Re: [HACKERS] WAL logging problem in 9.4.3?

2020-04-01 Thread Noah Misch
On Mon, Mar 30, 2020 at 11:37:57PM -0700, Andres Freund wrote: > On 2020-03-30 23:28:54 -0700, Noah Misch wrote: > > On Mon, Mar 30, 2020 at 04:43:00PM +0900, Michael Paquier wrote: > > > On Sun, Mar 29, 2020 at 09:41:01PM -0700, Noah Misch wrote: > > > > I think attached v41nm is ready for

Re: Add A Glossary

2020-04-01 Thread Corey Huinker
> > I propose we define "planner" and make "optimizer" a entry. > I have no objection to more entries, or edits to entries, but am concerned that the process leads to someone having to manually merge several start-from-scratch patches, with no clear sense of when we'll be done. I may make sense

Re: pg_stat_statements issue with parallel maintenance (Was Re: WAL usage calculation patch)

2020-04-01 Thread Dilip Kumar
On Thu, Apr 2, 2020 at 8:34 AM Peter Geoghegan wrote: > > On Wed, Apr 1, 2020 at 7:52 PM Amit Kapila wrote: > > Peter, Is this behavior expected? > > > > Let me summarize the situation so that it would be easier for Peter to > > comment. Julien has noticed that parallel vacuum and parallel

Re: Add A Glossary

2020-04-01 Thread Corey Huinker
> > 2. I found out that "see xyz" and "see also" have bespoke markup in > Docbook -- and . I changed some glossentries > to use those, removing some glossdefs and changing a couple of paras to > glossseealsos. I also removed all "id" properties from glossentries > that are just , because I

Re: Should we add xid_current() or a int8->xid cast?

2020-04-01 Thread Thomas Munro
On Sat, Mar 21, 2020 at 11:14 AM Thomas Munro wrote: > * updated OIDs to avoid collisions > * added btequalimage to btree/xid8_ops Here's the version I'm planning to commit tomorrow, if no one objects. Changes: * txid.c renamed to xid8funcs.c * remaining traces of "txid" replaced various

Re: Berserk Autovacuum (let's save next Mandrill)

2020-04-01 Thread Tom Lane
Dean Rasheed writes: > Yeah, that makes sense. I still can't see what might be causing those > failures. The tests that were doing an ALTER COLUMN and then expecting > to see the results of a non-analysed table ought to be fixed by > 0936d1b6f, but that doesn't match the buildfarm failures.

Re: [HACKERS] advanced partition matching algorithm for partition-wise join

2020-04-01 Thread Etsuro Fujita
Hi Tomas and Ashutosh, On Thu, Apr 2, 2020 at 1:51 AM Ashutosh Bapat wrote: > On Thu, 26 Mar 2020 at 05:47, Tomas Vondra > wrote: >> three more comments after eye-balling the code for a bit longer. >> >> 1) The patch probably needs to tweak config.sgml which says this about >> the

Re: pg_stat_statements issue with parallel maintenance (Was Re: WAL usage calculation patch)

2020-04-01 Thread Peter Geoghegan
On Wed, Apr 1, 2020 at 7:52 PM Amit Kapila wrote: > Peter, Is this behavior expected? > > Let me summarize the situation so that it would be easier for Peter to > comment. Julien has noticed that parallel vacuum and parallel create > index doesn't seem to report correct values for buffer usage

Re: pg_stat_statements issue with parallel maintenance (Was Re: WAL usage calculation patch)

2020-04-01 Thread Amit Kapila
Adding Peter G. On Wed, Apr 1, 2020 at 12:41 PM Dilip Kumar wrote: > > I have done some testing for the parallel "create index". > > postgres[99536]=# show maintenance_work_mem ; > maintenance_work_mem > -- > 1MB > (1 row) > > CREATE TABLE test (a int, b int); > INSERT INTO

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

2020-04-01 Thread Tomas Vondra
On Wed, Apr 01, 2020 at 10:09:20PM -0400, James Coleman wrote: On Wed, Apr 1, 2020 at 5:42 PM Tomas Vondra wrote: ... I've realized the way get_useful_pathkeys_for_relation() is coded kinda works against the fastpath we added when comparing pathkeys. That depends on comparing pointers to the

Re: Planning counters in pg_stat_statements (using pgss_store)

2020-04-01 Thread Fujii Masao
On 2020/04/01 18:19, Fujii Masao wrote: On 2020/04/01 3:42, Julien Rouhaud wrote: On Wed, Apr 01, 2020 at 02:43:10AM +0900, Fujii Masao wrote: On 2020/03/31 16:33, Julien Rouhaud wrote: v12 attached! Thanks for updating the patch! The patch looks good to me. I applied minor and

RE: SLRU statistics

2020-04-01 Thread Shinoda, Noriyoshi (PN Japan A Delivery)
Hi, Thank you for developing great features. The attached patch is a small fix to the committed documentation for the data type name of blks_hit column. Best regards, Noriyoshi Shinoda -Original Message- From: Tomas Vondra [mailto:tomas.von...@2ndquadrant.com] Sent: Thursday, April

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 17:54:06 -0700, Andres Freund wrote: > * Check whether the given snapshot is too old to have safely read the given > * page from the given table. If so, throw a "snapshot too old" error. > * > * This test generally needs to be performed after every BufferGetPage() call > *

Re: recovery_target_action=pause with confusing hint

2020-04-01 Thread movead...@highgo.ca
>So I'd like to propose the attached patch. The patch changes the message >logged when a promotion is requested, based on whether the recovery is >in paused state or not. It is a compromise, we should notice it in document I think. Regards, Highgo Software (Canada/China/Pakistan) URL :

Re: Add A Glossary

2020-04-01 Thread Alvaro Herrera
On 2020-Apr-01, Justin Pryzby wrote: > planner/optimizer: ... I propose we define "planner" and make "optimizer" a entry. I further propose not to define the term "normalized", at least not for now. That seems a very deep rabbit hole. -- Álvaro Herrera

Re: control max length of parameter values logged

2020-04-01 Thread Justin Pryzby
Thanks for updating the patch. On Thu, Apr 02, 2020 at 01:29:04AM +0100, Alexey Bashtanov wrote: > +If greater than zero, bind parameter values reported in non-error > +statement-logging messages are trimmed to no more than this many > bytes. > +If this value is specified

Re: Add A Glossary

2020-04-01 Thread Justin Pryzby
On Tue, Mar 31, 2020 at 03:26:02PM -0400, Corey Huinker wrote: > Just so I can prioritize my work, which of these things, along with your > suggestions in previous emails, would you say is a barrier to considering > this ready for a committer? To answer your off-list inquiry, I'm not likely to

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Peter Geoghegan
On Wed, Apr 1, 2020 at 5:54 PM Andres Freund wrote: > As far as I can tell there's not sufficient in-tree explanation of when > code needs to test for an old snapshot. There's just the following > comment above TestForOldSnapshot(): > * Check whether the given snapshot is too old to have safely

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 16:59:51 -0700, Andres Freund wrote: > The primary issue here is that there is no TestForOldSnapshot() in > heap_hot_search_buffer(). Therefore index fetches will never even try to > detect that tuples it needs actually have already been pruned away. bitmap heap scan doesn't

Re: SLRU statistics

2020-04-01 Thread Tomas Vondra
Hi, I've pushed this after some minor cleanup and improvements. regards -- Tomas Vondra http://www.2ndQuadrant.com PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: proposal \gcsv

2020-04-01 Thread Vik Fearing
On 4/1/20 6:29 PM, Tom Lane wrote: > I'd modify my first proposal so far as to make it > > \g ( pset-option pset-value ... ) filename-or-pipe > > That is, require spaces around the parens I think requiring spaces inside the parentheses is a severe POLA violation and I vote strongly

Re: control max length of parameter values logged

2020-04-01 Thread Alexey Bashtanov
Hi, The privilege argument seems irrelevant to me. We already decided that the plan is (a) SUSET for non-error statement logging purposes and (b) USERSET for logging caused by errors, and that would have to apply to length limits as well as enable/disable ability. Otherwise a user could pretty

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Peter Geoghegan
On Wed, Apr 1, 2020 at 4:59 PM Andres Freund wrote: > Thanks, that's super helpful. Glad I could help. > I got a bit confused here - you seemed to have switched session 1 and 2 > around? Doesn't seem to matter much though, I was able to reproduce this. Yeah, I switched the session numbers

Re: Allow continuations in "pg_hba.conf" files

2020-04-01 Thread David Zhang
Hi Fabien, Should we consider the case "\ ", i.e. one or more spaces after the backslash? For example, if I replace a user map "mymap /^(.*)@mydomain\.com$ \1" with "mymap /^(.*)@mydomain\.com$ \ " "\1" by adding one extra space after the backslash, then I got the pg_role="\\" but

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 16:59:51 -0700, Andres Freund wrote: > The primary issue here is that there is no TestForOldSnapshot() in > heap_hot_search_buffer(). Therefore index fetches will never even try to > detect that tuples it needs actually have already been pruned away. FWIW, with autovacuum=off

Re: Add A Glossary

2020-04-01 Thread Alvaro Herrera
On 2020-Apr-01, Jürgen Purtz wrote: > > On 31.03.20 19:58, Justin Pryzby wrote: > > On Tue, Mar 31, 2020 at 04:13:00PM +0200, Jürgen Purtz wrote: > > > Please find some minor suggestions in the attachment. They are based on > > > Corey's last patch 0001-glossary-v4.patch. > > > @@ -220,7 +220,7

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 15:30:39 -0700, Peter Geoghegan wrote: > On Wed, Apr 1, 2020 at 3:00 PM Peter Geoghegan wrote: > > I like that idea. I think that I've spotted what may be an independent > > bug, but I have to wait around for a minute or two to reproduce it > > each time. Makes it hard to get

Re: Proposal: Expose oldest xmin as SQL function for monitoring

2020-04-01 Thread Tom Lane
Alvaro Herrera writes: > On 2020-Apr-01, Tom Lane wrote: >> The fact that I had to use max(age(...)) in that sample query >> hints at one reason: it's really hard to do arithmetic correctly >> on raw XIDs. Dealing with wraparound is a problem, and knowing >> what's past or future is even harder.

Re: Ltree syntax improvement

2020-04-01 Thread Tom Lane
Nikita Glukhov writes: > [ latest version of ltree syntax extension ] This is going to need another rebase after all the other ltree hacking that just got done. However, I did include 0001 (use a switch) in the commit I just pushed, so you don't need to worry about that.

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 14:11:11 -0700, Andres Freund wrote: > As far as I can tell, with a large old_snapshot_threshold, it can take a > very long time to get to a head_timestamp that's old enough for > TransactionIdLimitedForOldSnapshots() to do anything. Look at this > trace of a pgbench run with

Re: Proposal: Expose oldest xmin as SQL function for monitoring

2020-04-01 Thread James Coleman
On Wed, Apr 1, 2020 at 5:58 PM Alvaro Herrera wrote: > > On 2020-Apr-01, Tom Lane wrote: > > > James Coleman writes: > > > To my knowledge the current oldest xmin (GetOldestXmin() if I'm not > > > mistaken) isn't exposed directly in any view or function by Postgres. > > > > You could do

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Peter Geoghegan
On Wed, Apr 1, 2020 at 3:00 PM Peter Geoghegan wrote: > I like that idea. I think that I've spotted what may be an independent > bug, but I have to wait around for a minute or two to reproduce it > each time. Makes it hard to get to a minimal test case. I now have simple steps to reproduce a bug

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Peter Geoghegan
On Wed, Apr 1, 2020 at 1:25 PM Robert Haas wrote: > Maybe that contrib module could even have some functions to simulate > aging without the passage of any real time. Like, say you have a > function or procedure old_snapshot_pretend_time_has_passed(integer), > and it moves

Re: Proposal: Expose oldest xmin as SQL function for monitoring

2020-04-01 Thread Alvaro Herrera
On 2020-Apr-01, Tom Lane wrote: > James Coleman writes: > > To my knowledge the current oldest xmin (GetOldestXmin() if I'm not > > mistaken) isn't exposed directly in any view or function by Postgres. > > You could do something like > > select max(age(backend_xmin)) from pg_stat_activity; >

Re: [PATCH] ltree, lquery, and ltxtquery binary protocol support

2020-04-01 Thread Nino Floris
Hi Tom, Thanks a lot for pushing this through. In complete agreement on fixing mbstrlen, it would clearly have lead to cut off string sends, or worse (does the binary protocol use null terminated strings, or are they length prefixed?). Apologies anyways, it's been a while so I don't know how it

Re: Proposal: Expose oldest xmin as SQL function for monitoring

2020-04-01 Thread Tom Lane
James Coleman writes: > To my knowledge the current oldest xmin (GetOldestXmin() if I'm not > mistaken) isn't exposed directly in any view or function by Postgres. You could do something like select max(age(backend_xmin)) from pg_stat_activity; though I'm not sure whether that accounts for

Re: [PATCH] Incremental sort (was: PoC: Partial sort)

2020-04-01 Thread Tomas Vondra
On Wed, Apr 01, 2020 at 09:05:27AM -0400, James Coleman wrote: On Tue, Mar 31, 2020 at 11:07 PM James Coleman wrote: On Tue, Mar 31, 2020 at 10:44 PM Tomas Vondra wrote: > > On Tue, Mar 31, 2020 at 10:12:29PM -0400, James Coleman wrote: > >On Tue, Mar 31, 2020 at 9:59 PM Tomas Vondra > >

Re: [PATCH] ltree, lquery, and ltxtquery binary protocol support

2020-04-01 Thread Tom Lane
I wrote: > Fortunately for the odds of getting this patch accepted, we just > pushed an ALTER TYPE improvement that will solve your problem [1]. > Please replace ltree--1.2.sql with an upgrade script that uses > that, and resubmit. I decided it would be a shame for this to miss v13, seeing that

Re: backup manifests

2020-04-01 Thread David Steele
On 3/31/20 7:57 AM, Robert Haas wrote: On Mon, Mar 30, 2020 at 7:24 PM David Steele wrote: I'm confused as to why you're not seeing that. What's the exact sequence of steps? $ pg_basebackup -D test/backup5 --manifest-checksums=SHA256 $ vi test/backup5/backup_manifest * Add 'X' to the

Proposal: Expose oldest xmin as SQL function for monitoring

2020-04-01 Thread James Coleman
Currently there's no good way that I'm aware of for monitoring software to check what the xmin horizon is being blocked at. You can check pg_stat_replication and pg_replication_slots and txid_snapshot_xmin(txid_current_snapshot()) and so on, but that list can grow, and it means monitoring setups

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 15:11:52 -0500, Kevin Grittner wrote: > On Wed, Apr 1, 2020 at 2:43 PM Andres Freund wrote: > > > The thing that makes me really worried is that the contents of the time > > mapping seem very wrong. I've reproduced query results in a REPEATABLE > > READ transaction changing

Re: backup manifests

2020-04-01 Thread Andres Freund
Hi, On 2020-03-31 14:56:07 +0530, Amit Kapila wrote: > On Tue, Mar 31, 2020 at 11:10 AM Noah Misch wrote: > > On Mon, Mar 30, 2020 at 12:16:31PM -0700, Andres Freund wrote: > > > On 2020-03-30 15:04:55 -0400, Robert Haas wrote: > > > I'm mildly inclined to name it pg_validate, pg_validate_dbdir

Re: backup manifests

2020-04-01 Thread Andres Freund
Hi, On 2020-03-31 22:15:04 -0700, Noah Misch wrote: > On Tue, Mar 31, 2020 at 03:50:34PM -0700, Andres Freund wrote: > > On 2020-03-31 14:10:34 -0400, Robert Haas wrote: > > > +/* > > > + * Attempt to parse the WAL files required to restore from backup using > > > + * pg_waldump. > > > + */ > > >

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Robert Haas
On Wed, Apr 1, 2020 at 3:43 PM Andres Freund wrote: > The thing that makes me really worried is that the contents of the time > mapping seem very wrong. I've reproduced query results in a REPEATABLE > READ transaction changing (pruned without triggering an error). And I've > reproduced rows not

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Kevin Grittner
On Wed, Apr 1, 2020 at 2:43 PM Andres Freund wrote: > The thing that makes me really worried is that the contents of the time > mapping seem very wrong. I've reproduced query results in a REPEATABLE > READ transaction changing (pruned without triggering an error). That is a very big problem.

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, Nice to have you back for a bit! Even if the circumstances aren't great... It's very understandable that the lists are past your limits, I barely keep up these days. Without any health issues. On 2020-04-01 14:10:09 -0500, Kevin Grittner wrote: > Perhaps the lack of evidence for usage in

Re: error context for vacuum to include block number

2020-04-01 Thread Alvaro Herrera
On 2020-Apr-01, Andres Freund wrote: > On 2020-04-01 07:54:45 +0530, Amit Kapila wrote: > > Pushed. I think we are done here. The patch is marked as committed in > > CF. Thank you! > > Awesome! Thanks for all your work on this, all. This'll make it a lot > easier to debug errors during

Re: error context for vacuum to include block number

2020-04-01 Thread Andres Freund
On 2020-04-01 07:54:45 +0530, Amit Kapila wrote: > Pushed. I think we are done here. The patch is marked as committed in > CF. Thank you! Awesome! Thanks for all your work on this, all. This'll make it a lot easier to debug errors during autovacuum.

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Kevin Grittner
On Wed, Apr 1, 2020 at 10:09 AM Andres Freund wrote: First off, many thanks to Andres for investigating this, and apologies for the bugs. Also thanks to Michael for making sure I saw the thread. I must also apologize that for not being able to track the community lists consistently due to

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Robert Haas
On Wed, Apr 1, 2020 at 2:37 PM Andres Freund wrote: > Just continuing is easier said than done. Especially with the background > of knowing that several users had hit the bug that allowed all of the > above to be hit, and that advancing relfrozenxid further would make it > worse. Fair point, but

Re: tweaking perfect hash multipliers

2020-04-01 Thread John Naylor
On Tue, Mar 31, 2020 at 4:05 PM John Naylor wrote: > > On Tue, Mar 31, 2020 at 2:31 AM Andres Freund wrote: > > I think the form of lea generated here is among the ones that can only > > be executed on port 1. Whereas e.g. an register+register/immediate add > > can be executed on four different

Re: [PATCH] Check operator when creating unique index on partition table

2020-04-01 Thread Tom Lane
Guancheng Luo writes: > On Mar 26, 2020, at 01:00, Tom Lane wrote: >> This would reject, for example, a hash index associated with a btree-based >> partition constraint, but I'm not sure we're losing anything much thereby. > There is cases when a BTREE index associated with a HASH partition

Re: BUG #16109: Postgres planning time is high across version (Expose buffer usage during planning in EXPLAIN)

2020-04-01 Thread Julien Rouhaud
On Wed, Apr 1, 2020 at 7:51 PM Fujii Masao wrote: > > > On 2020/03/31 10:31, Justin Pryzby wrote: > > On Wed, Jan 29, 2020 at 12:15:59PM +0100, Julien Rouhaud wrote: > >> Rebase due to conflict with 3ec20c7091e97. > > > > This is failing to apply probably since > >

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 13:27:56 -0400, Robert Haas wrote: > Perhaps "irresponsible" is the wrong word, but it's certainly caused > problems for multiple EnterpriseDB customers, and in my view, those > problems weren't necessary. Either a WARNING or an ERROR would have > shown up in the log, but an

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 11:04:43 -0700, Peter Geoghegan wrote: > On Wed, Apr 1, 2020 at 10:28 AM Robert Haas wrote: > > Is there any chance that you're planning to look into the details? > > That would certainly be welcome from my perspective. +1 This definitely needs more eyes. I am not even close

Re: proposal \gcsv

2020-04-01 Thread Alvaro Herrera
On 2020-Apr-01, Pavel Stehule wrote: > It can work, but it is not user friendly - my proposal was motivated by > using some quick csv exports to gplot's pipe. Can we fix that by adding some syntax to allow command aliases? So you could add to your .psqlrc something like \alias \gcsv \pset push

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Peter Geoghegan
On Wed, Apr 1, 2020 at 10:28 AM Robert Haas wrote: > Sure, but not all levels of risk are equal. Jumping out of a plane > carries some risk of death whether or not you have a parachute, but > that does not mean that we shouldn't worry about whether you have one > or not before you jump. > > In

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 12:02:18 -0400, Robert Haas wrote: > On Wed, Apr 1, 2020 at 11:09 AM Andres Freund wrote: > > There's really no reason at all to have bins of one minute. As it's a > > PGC_POSTMASTER GUC, it should just have didided time into bins of > > (old_snapshot_threshold * USEC_PER_SEC)

Re: BUG #16109: Postgres planning time is high across version (Expose buffer usage during planning in EXPLAIN)

2020-04-01 Thread Fujii Masao
On 2020/03/31 10:31, Justin Pryzby wrote: On Wed, Jan 29, 2020 at 12:15:59PM +0100, Julien Rouhaud wrote: Rebase due to conflict with 3ec20c7091e97. This is failing to apply probably since 4a539a25ebfc48329fd656a95f3c1eb2cda38af3. Could you rebase? (Also, not sure if this can be set as

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Robert Haas
On Wed, Apr 1, 2020 at 1:03 PM Peter Geoghegan wrote: > I don't think that it's fair to characterize Andres' actions in that > situation as in any way irresponsible. We had an extremely complicated > data corruption bug that he went to great lengths to fix, following > two other incorrect fixes.

Re: [HACKERS] advanced partition matching algorithm for partition-wise join

2020-04-01 Thread Ashutosh Bapat
On Thu, 26 Mar 2020 at 00:35, Tomas Vondra wrote: > Hi, > > I've started reviewing the patch a couple days ago. I haven't done any > extensive testing, but I do have a bunch of initial comments that I can > share now. > > 1) I wonder if this needs to update src/backend/optimizer/README, which >

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Peter Geoghegan
On Wed, Apr 1, 2020 at 9:02 AM Robert Haas wrote: > I complained > when you added those error checks to vacuum in back-branches, and > since that release went out people are regularly tripping those checks > and taking prolonged outages for a problem that wasn't making them > unhappy before. I

Re: proposal \gcsv

2020-04-01 Thread Tom Lane
Pavel Stehule writes: > It can work, but it is not user friendly - my proposal was motivated by > using some quick csv exports to gplot's pipe. I kind of liked the stack idea, myself. It's simpler than what I was suggesting and it covers probably 90% of the use-case. However, if we prefer

Re: wraparound dangers in snapshot too old

2020-04-01 Thread Andres Freund
Hi, On 2020-03-31 21:53:04 -0700, Andres Freund wrote: > I am trying to change the snapshot too old infrastructure so it > cooperates with my snapshot scalability patch. While trying to > understand the code sufficiently, I think I found a fairly serious > issue: I accidentally sent this email,

Re: A bug when use get_bit() function for a long bytea string

2020-04-01 Thread Tom Lane
"movead...@highgo.ca" writes: > [ long_bytea_string_bug_fix_ver5.patch ] I don't think this has really solved the overflow hazards. For example, in binary_encode we've got resultlen = enc->encode_len(VARDATA_ANY(data), datalen); result = palloc(VARHDRSZ + resultlen); and all

Re: proposal \gcsv

2020-04-01 Thread Pavel Stehule
st 1. 4. 2020 v 17:52 odesílatel Daniel Verite napsal: > Tom Lane wrote: > > > I could see having a command to copy the current primary formatting > > parameters to the alternate area, too. > > We could have a stack to store parameters before temporary > changes, for instance if you

Re: pgbench - add pseudo-random permutation function

2020-04-01 Thread David Steele
Hi Fabien, On 2/1/20 5:12 AM, Fabien COELHO wrote: Attached is an attempt at improving things. I have added a explicit note and hijacked an existing example to better illustrate the purpose of the function. This patch does not build on Linux due to some unused functions and variables:

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Robert Haas
On Wed, Apr 1, 2020 at 11:09 AM Andres Freund wrote: > That doesn't exist in all the back branches. Think it'd be easier to add > code to explicitly prune it during MaintainOldSnapshotTimeMapping(). That's reasonable. > There's really no reason at all to have bins of one minute. As it's a >

Re: proposal \gcsv

2020-04-01 Thread Isaac Morland
On Wed, 1 Apr 2020 at 11:52, Daniel Verite wrote: > Tom Lane wrote: > > > I could see having a command to copy the current primary formatting > > parameters to the alternate area, too. > > We could have a stack to store parameters before temporary > changes, for instance if you want to

Re: proposal \gcsv

2020-04-01 Thread Daniel Verite
Tom Lane wrote: > I could see having a command to copy the current primary formatting > parameters to the alternate area, too. We could have a stack to store parameters before temporary changes, for instance if you want to do one csv export and come back to normal without assuming what

Re: BufFileRead() error signalling

2020-04-01 Thread David Steele
Hi Thomas, On 11/29/19 9:46 PM, Thomas Munro wrote: Ok. Here is a first attempt at that. It's been a few CFs since this patch received an update, though there has been plenty of discussion. Perhaps it would be best to mark it RwF until you have a chance to produce an update patch?

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 11:15:14 -0400, Robert Haas wrote: > On Wed, Apr 1, 2020 at 2:40 AM Andres Freund wrote: > > I added some debug output to print the mapping before/after changes by > > MaintainOldSnapshotTimeMapping() (note that I used timestamps relative > > to the server start in

Re: Removing unneeded self joins

2020-04-01 Thread David Steele
On 1/27/20 11:10 PM, Andrey Lepikhov wrote: Rebased version v.22. - Added enable_self_join_removal GUC (true is default) - The joinquals of the relation that is being removed, redistributed in accordance with the remove_rel_from_query () machinery. This patch no longer applies cleanly on

Re: Online checksums patch - once again

2020-04-01 Thread David Steele
On 1/18/20 6:18 PM, Daniel Gustafsson wrote: Attached is a v16 rebased on top of current master which addresses the above commented points, and which I am basing the concurrency work on. This patch no longer applies cleanly: http://cfbot.cputube.org/patch_27_2260.log The CF entry has been

Re: proposal \gcsv

2020-04-01 Thread Tom Lane
Vik Fearing writes: > On 4/1/20 1:53 AM, Tom Lane wrote: >> Consider some syntax along the lines of >> \gpset (pset-option-name [pset-option-value]) ... filename > If parens are going to be required, why don't we just add them to \g? > TABLE blah \g (format csv) filename Yeah, if we're willing

Re: Verify true root on replicas with amcheck

2020-04-01 Thread David Steele
On 1/9/20 3:55 AM, godjan • wrote: Hi, we have trouble to detect true root corruptions on replicas. I made a patch for resolving it with the locking meta page and potential root page. I heard that amcheck has an invariant about locking no more than 1 page at a moment for avoiding deadlocks.

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Robert Haas
On Wed, Apr 1, 2020 at 2:40 AM Andres Freund wrote: > I added some debug output to print the mapping before/after changes by > MaintainOldSnapshotTimeMapping() (note that I used timestamps relative > to the server start in minutes/seconds to make it easier to interpret). > > And the output turns

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-04-01 10:01:07 -0400, Robert Haas wrote: > On Wed, Apr 1, 2020 at 2:40 AM Andres Freund wrote: > > The problem is that there's no protection again the xids in the > > ringbuffer getting old enough to wrap around. Given that practical uses > > of old_snapshot_threshold are likely to

Re: control max length of parameter values logged

2020-04-01 Thread Tom Lane
Justin Pryzby writes: > On Wed, Apr 01, 2020 at 10:10:55AM +0100, Alexey Bashtanov wrote: >>> Could you make zero a normal value and -1 the "special" value to disable >>> trimming ? >> I can, but then for the sake of consistency I'll have to do the same for >> log_parameter_max_length. >> Then

Re: Less-silly selectivity for JSONB matching operators

2020-04-01 Thread Tom Lane
Alexey Bashtanov writes: > On 31/03/2020 18:53, Tom Lane wrote: >> Renamed "matchsel" to "matchingsel" etc, added DEFAULT_MATCHING_SEL, >> rebased over commit 911e70207. Since that commit already created >> new versions of the relevant contrib modules, I think we can just >> redefine what those

Re: WAL usage calculation patch

2020-04-01 Thread Julien Rouhaud
Hi, I'm replying here to all reviews that have been sent, thanks a lot! On Wed, Apr 01, 2020 at 04:29:16PM +0530, Amit Kapila wrote: > On Wed, Apr 1, 2020 at 1:32 PM Julien Rouhaud wrote: > > > > So here's a v9, rebased on top of the latest versions of Sawada-san's bug > > fixes > > (Amit's v6

Re: potential stuck lock in SaveSlotToPath()

2020-04-01 Thread Peter Eisentraut
On 2020-03-27 08:48, Michael Paquier wrote: On Thu, Mar 26, 2020 at 02:16:05PM +0100, Peter Eisentraut wrote: committed and backpatched The patch committed does that in three places: /* rename to permanent file, fsync file and directory */ if (rename(tmppath, path) != 0) { +

Re: Commitfest 2020-03 Now in Progress

2020-04-01 Thread David Steele
On 3/17/20 8:10 AM, David Steele wrote: On 3/1/20 4:10 PM, David Steele wrote: The last Commitfest for v13 is now in progress! Current stats for the Commitfest are: Needs review: 192 Waiting on Author: 19 Ready for Committer: 4 Total: 215 Halfway through, here's where we stand.  Note that a

Re: allow online change primary_conninfo

2020-04-01 Thread Peter Eisentraut
On 2020-03-28 11:49, Sergei Kornilov wrote: I attached updated patch: walreceiver will use configured primary_slot_name as temporary slot name if wal_receiver_create_temp_slot is enabled. The original setup worked consistently with pg_basebackup. In pg_basebackup, if you specify -S/--slot,

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Andres Freund
Hi, On 2020-03-31 23:40:08 -0700, Andres Freund wrote: > I added some debug output to print the mapping before/after changes by > MaintainOldSnapshotTimeMapping() (note that I used timestamps relative > to the server start in minutes/seconds to make it easier to interpret). Now attached.

Re: snapshot too old issues, first around wraparound and then more.

2020-04-01 Thread Robert Haas
On Wed, Apr 1, 2020 at 2:40 AM Andres Freund wrote: > The problem is that there's no protection again the xids in the > ringbuffer getting old enough to wrap around. Given that practical uses > of old_snapshot_threshold are likely to be several hours to several > days, that's not particularly

  1   2   >