Re: password_encryption default

On Wed, Jun 10, 2020 at 10:51:22AM -0400, Jonathan S. Katz wrote: > On 6/10/20 10:47 AM, Peter Eisentraut wrote: >> committed > > Yay!!! Thank you! Thanks, all. -- Michael signature.asc Description: PGP signature

Re: Speedup usages of pg_*toa() functions

> "David" == David Rowley writes: David> Pending any objections, I'd like to push both of these patches David> in the next few days to master. For the second patch, can we take the opportunity to remove the extraneous blank line at the top of pg_ltoa, and add the two missing "extern"s in b

Re: [Patch] pg_rewind: options to use restore_command from recovery.conf or command line

On Wed, Jun 10, 2020 at 01:23:37PM +0900, Michael Paquier wrote: > Okay. After sleeping on it, it looks like would be better to move > this new fe_archive.c to src/fe_utils/. I'll try to do that tomorrow, > and added an open item so as we don't forget about it. Applied this part now as of a3b2bf

Re: Is it useful to record whether plans are generic or custom?

On 2020-06-10 18:00, Kyotaro Horiguchi wrote: + TupleDescInitEntry(tupdesc, (AttrNumber) 8, "last_plan", This could be a problem if we showed the last plan in this view. I think "last_plan_type" would be better. + if (prep_stmt->plansource->last_plan_type == PLAN_CACHE_TYPE_CUSTOM)

Add tap test for --extra-float-digits option

Hi hackers! I added tap test code for pg_dump --extra-float-digits option because it hadn't tested it. There was no problem when writing test code and running TAP tests. 0001-Add-tap-test-for-extra-float-digits-option.patch Description: Binary data

Re: Parallel Seq Scan vs kernel read ahead

On Thu, 11 Jun 2020 at 16:03, Amit Kapila wrote: > I think something on these lines would be a good idea especially > keeping step-size proportional to relation size. However, I am not > completely sure if doubling the step-size with equal increase in > relation size (ex. what is happening betwee

Re: Parallel Seq Scan vs kernel read ahead

On Thu, Jun 11, 2020 at 8:35 AM David Rowley wrote: > > On Thu, 11 Jun 2020 at 14:09, Amit Kapila wrote: > > > > On Thu, Jun 11, 2020 at 7:18 AM David Rowley wrote: > > > > > > On Thu, 11 Jun 2020 at 01:24, Amit Kapila wrote: > > > > Can we try the same test with 4, 8, 16 workers as well? I do

Re: Speedup usages of pg_*toa() functions

On Wed, 10 Jun 2020 at 11:57, David Rowley wrote: > > On Tue, 9 Jun 2020 at 22:08, Andrew Gierth > wrote: > > > > > "David" == David Rowley writes: > > > > David> This allows us to speed up a few cases. int2vectorout() should > > David> be faster and int8out() becomes a bit faster if we g

Re: Resetting spilled txn statistics in pg_stat_replication

On Thu, Jun 11, 2020 at 12:35 AM Magnus Hagander wrote: > > On Wed, Jun 10, 2020 at 9:01 AM Masahiko Sawada > wrote: >> > >> If we move these values to replication slots, I think we can change >> the code so that these statistics are managed by replication slots >> (e.g. ReplicationSlot struct).

Re: Recording test runtimes with the buildfarm

On Thu, 11 Jun 2020 at 10:02, Tom Lane wrote: > > Thomas Munro writes: > > I've been doing that in a little database that pulls down the results > > and analyses them with primitive regexes. First I wanted to know the > > pass/fail history for each individual regression, isolation and TAP > > sc

Re: Parallel Seq Scan vs kernel read ahead

On Thu, 11 Jun 2020 at 14:09, Amit Kapila wrote: > > On Thu, Jun 11, 2020 at 7:18 AM David Rowley wrote: > > > > On Thu, 11 Jun 2020 at 01:24, Amit Kapila wrote: > > > Can we try the same test with 4, 8, 16 workers as well? I don't > > > foresee any problem with a higher number of workers but i

Re: [Proposal] Global temporary tables

> 2020年6月9日 下午8:15，Prabhat Sahu 写道： > > > > On Wed, Apr 29, 2020 at 8:52 AM 曾文旌 > wrote: >> 2020年4月27日 下午9:48，Prabhat Sahu > > 写道： >> >> Thanks Wenjing, for the fix patch for previous issues. >> I have verified the iss

Re: Parallel Seq Scan vs kernel read ahead

On Thu, Jun 11, 2020 at 7:18 AM David Rowley wrote: > > On Thu, 11 Jun 2020 at 01:24, Amit Kapila wrote: > > Can we try the same test with 4, 8, 16 workers as well? I don't > > foresee any problem with a higher number of workers but it might be > > better to once check that if it is not too much

Re: Parallel Seq Scan vs kernel read ahead

On Thu, 11 Jun 2020 at 01:24, Amit Kapila wrote: > Can we try the same test with 4, 8, 16 workers as well? I don't > foresee any problem with a higher number of workers but it might be > better to once check that if it is not too much additional work. I ran the tests again with up to 7 workers.

Re: hashagg slowdown due to spill changes

On Fri, 2020-06-05 at 21:11 -0700, Andres Freund wrote: > Hi, > > While preparing my pgcon talk I noticed that our hash-agg performance > degraded noticeably. Looks to me like it's due to the spilling- > hashagg > changes. Attached a proposed fix. (Might require some minor cleanup). The only awk

Re: Default setting for enable_hashagg_disk

On Wed, Jun 10, 2020 at 10:39 AM Jeff Davis wrote: > On Tue, 2020-06-09 at 18:20 -0700, Melanie Plageman wrote: > > So, I was catching up on email and noticed the last email in this > > thread. > > > > I think I am not fully understanding what > > enable_groupingsets_hash_disk > > does. Is it onl

Re: Recording test runtimes with the buildfarm

Thomas Munro writes: > I've been doing that in a little database that pulls down the results > and analyses them with primitive regexes. First I wanted to know the > pass/fail history for each individual regression, isolation and TAP > script, then I wanted to build something that could identify

Re: Recording test runtimes with the buildfarm

Hi, On June 10, 2020 2:13:51 PM PDT, David Rowley wrote: >On Thu, 11 Jun 2020 at 02:13, Tom Lane wrote: >> I have in the past scraped the latter results and tried to make sense >of >> them. They are *mighty* noisy, even when considering just one animal >> that I know to be running on a machine

Re: Recording test runtimes with the buildfarm

On Thu, Jun 11, 2020 at 9:43 AM Thomas Munro wrote: > On Thu, Jun 11, 2020 at 2:13 AM Tom Lane wrote: > > I have in the past scraped the latter results and tried to make sense of > > them. They are *mighty* noisy, even when considering just one animal > > that I know to be running on a machine w

Re: Recording test runtimes with the buildfarm

David Rowley writes: > On Thu, 11 Jun 2020 at 02:13, Tom Lane wrote: >> I have in the past scraped the latter results and tried to make sense of >> them. They are *mighty* noisy, even when considering just one animal >> that I know to be running on a machine with little else to do. > Do you rec

Re: Recording test runtimes with the buildfarm

On Thu, Jun 11, 2020 at 2:13 AM Tom Lane wrote: > I have in the past scraped the latter results and tried to make sense of > them. They are *mighty* noisy, even when considering just one animal > that I know to be running on a machine with little else to do. Maybe > averaging across the whole bu

Re: Recording test runtimes with the buildfarm

On Thu, 11 Jun 2020 at 02:13, Tom Lane wrote: > I have in the past scraped the latter results and tried to make sense of > them. They are *mighty* noisy, even when considering just one animal > that I know to be running on a machine with little else to do. Do you recall if you looked at the para

Re: Resetting spilled txn statistics in pg_stat_replication

On Wed, Jun 10, 2020 at 9:01 AM Masahiko Sawada < masahiko.saw...@2ndquadrant.com> wrote: > On Tue, 9 Jun 2020 at 17:24, Magnus Hagander wrote: > > > > > > > > On Tue, Jun 9, 2020 at 9:12 AM Masahiko Sawada < > masahiko.saw...@2ndquadrant.com> wrote: > >> > >> On Tue, 2 Jun 2020 at 18:34, Amit Ka

Re: Default setting for enable_hashagg_disk

On Tue, 2020-04-07 at 11:20 -0700, Jeff Davis wrote: > Now that we have Disk-based Hash Aggregation, there are a lot more > situations where the planner can choose HashAgg. The > enable_hashagg_disk GUC, if set to true, chooses HashAgg based on > costing. If false, it only generates a HashAgg path

Re: Default setting for enable_hashagg_disk

On Tue, 2020-06-09 at 21:15 -0500, Justin Pryzby wrote: > The behavior of the GUC is inconsistent with the other GUCs, which is > confusing. See also Robert's comments in this thread. > https://www.postgresql.org/message-id/20200407223900.GT2228%40telsasoft.com enable_* GUCs are planner GUCs, so

Re: Fast DSM segments

On Tue, Jun 9, 2020 at 6:03 PM Thomas Munro wrote: > That all makes sense. Now I'm wondering if I should use exactly that > word in the GUC... dynamic_shared_memory_preallocate? I tend to prefer verb-object rather than object-verb word ordering, because that's how English normally works, but I r

Re: Command statistics system (cmdstats)

On Wed, Mar 4, 2020 at 10:43 PM Mark Dilger wrote: > The two main differences are that > > (1) This implementation is based on commandtags as enums, not strings and > (2) This implementation uses techniques to reduce lock contention > > I think (2) is the more important part. My spidey sense is t

Re: Default setting for enable_hashagg_disk

On Tue, 2020-06-09 at 18:20 -0700, Melanie Plageman wrote: > So, I was catching up on email and noticed the last email in this > thread. > > I think I am not fully understanding what > enable_groupingsets_hash_disk > does. Is it only for testing? It's mostly for testing. I could imagine cases whe

Re: Internal key management system

On Fri, Jun 5, 2020 at 03:34:54PM +0200, Fabien COELHO wrote: > Obviously it requires some more thinking and design, but my point is that > postgres should not hold a KEK, ever, nor presume how DEK are to be managed > by a DMS, and that is not very difficult to achieve by putting it outside of > p

Re: global barrier & atomics in signal handlers (Re: Atomic operations within spinlocks)

On Tue, Jun 9, 2020 at 6:54 PM Andres Freund wrote: > What do you think about my idea of having a BEGIN/END_SIGNAL_HANDLER? > That'd make it much easier to write assertions forbidding palloc, 64bit > atomics, ... I must have missed the previous place where you suggested this, but I think it's a g

Re: Physical replication slot advance is not persistent

On 2020-06-10 11:38, Kyotaro Horiguchi wrote: At Wed, 10 Jun 2020 15:53:53 +0900, Michael Paquier wrote in > > I find it really depressing how much obviously untested stuff gets > > added in this area. > > Prior to this patch pg_replication_slot_advance was not being tested > at all. > Unfortun

COPY, bytea streaming and memory footprint

Hello, I am trying to understand/optimize how a COPY operation behaves when transfering a bytea from the database to a client. For simplicity, I'll consider that I have only one bytea _image_ in the _images_ table. Starting with COPY (SELECT image FROM images) TO STDOUT BINARY I understand that

Re: Default setting for enable_hashagg_disk

On Tue, Jun 9, 2020 at 7:15 PM Justin Pryzby wrote: > On Tue, Jun 09, 2020 at 06:20:13PM -0700, Melanie Plageman wrote: > > On Thu, Apr 9, 2020 at 1:02 PM Jeff Davis wrote: > > > > > 2. enable_groupingsets_hash_disk (default false): > > > > > > This is about how we choose which grouping sets to

Re: Recording test runtimes with the buildfarm

Greetings, * Andrew Dunstan (andrew.duns...@2ndquadrant.com) wrote: > On 6/10/20 10:13 AM, Tom Lane wrote: > > Andrew Dunstan writes: > >> Alternatively, people with access to the database could extract the logs > >> and post-process them using perl or python. That would involve no work > >> on m

Re: Recording test runtimes with the buildfarm

On 6/10/20 10:13 AM, Tom Lane wrote: > Andrew Dunstan writes: >> Alternatively, people with access to the database could extract the logs >> and post-process them using perl or python. That would involve no work >> on my part :-) But it would not be automated. > Yeah, we could easily extract per

Re: password_encryption default

On 6/10/20 10:47 AM, Peter Eisentraut wrote: > On 2020-05-28 15:28, Jonathan S. Katz wrote: >> On 5/28/20 8:10 AM, Peter Eisentraut wrote: >>> On 2020-05-27 15:25, Jonathan S. Katz wrote: $ initdb -D data --auth-local=scram-sha-256 --auth-host=md5 Got an error message: "ini

Re: password_encryption default

On 2020-05-28 15:28, Jonathan S. Katz wrote: On 5/28/20 8:10 AM, Peter Eisentraut wrote: On 2020-05-27 15:25, Jonathan S. Katz wrote: $ initdb -D data --auth-local=scram-sha-256 --auth-host=md5 Got an error message: "initdb: error: must specify a password for the superuser to enable md5 authe

Re: elog(DEBUG2 in SpinLocked section.

On Tue, Jun 9, 2020 at 8:12 PM Andres Freund wrote: > I don't think the size is worth of concern in this case, and I'm not > sure there's any current case where it's really worth spending effort > reducing size. But if there is: It seems possible to reduce the size. Yeah, I don't think it's very

Re: Terminate the idle sessions

> > > Why not implement it in the core of Postgres? Are there any disadvantages of > implementing it in the core of Postgres? I was surprised this wasn't a feature when I looked into it a couple years ago. I'd use it if it were built in, but I am not installing something extra just for this. > I’m

Re: Recording test runtimes with the buildfarm

Andrew Dunstan writes: > Alternatively, people with access to the database could extract the logs > and post-process them using perl or python. That would involve no work > on my part :-) But it would not be automated. Yeah, we could easily extract per-test-script runtimes, since pg_regress start

pg_upgrade fails if vacuum_defer_cleanup_age > 0

A customer's upgrade failed, and it took me a while to figure out that the problem was that they had set "vacuum_defer_cleanup_age=1" on the new cluster. The consequence was that the "vacuumdb --freeze" that takes place before copying commit log files failed to freeze "pg_database". That cause

Re: Recording test runtimes with the buildfarm

On 6/10/20 8:58 AM, David Rowley wrote: > Hi, > > I've just had some thoughts about the possible usefulness of having > the buildfarm record the run-time of each regression test to allow us > to have some sort of ability to track the run-time history of each > test. > > I thought the usefulness m

Re: Terminate the idle sessions

On Jun 10, 2020, at 4:25 PM, Michael Paquier mailto:mich...@paquier.xyz>> wrote: Idle sessions staying around can be a problem in the long run as they impact snapshot building. You could for example use a background worker to do this work, like that: https://github.com/michaelpq/pg_plugins/tre

Re: Atomic operations within spinlocks

Robert Haas writes: > On Fri, Jun 5, 2020 at 8:19 PM Andres Freund wrote: >> Randomly noticed while looking at the code: >> uint64 flagbit = UINT64CONST(1) << (uint64) type; >> >> that shouldn't be 64bit, right? > I'm going to admit ignorance here. What's the proper coding rule?

Re: Parallel Seq Scan vs kernel read ahead

On Wed, Jun 10, 2020 at 6:04 PM David Rowley wrote: > > On Wed, 10 Jun 2020 at 17:39, David Rowley wrote: > > > > On Wed, 10 Jun 2020 at 17:21, Thomas Munro wrote: > > > I also heard from Andres that he likes this patch with his AIO > > > prototype, because of the way request merging works. So

Re: Recording test runtimes with the buildfarm

Greetings, * David Rowley (dgrowle...@gmail.com) wrote: > 1. We could quickly identify when someone adds some overly complex > test and slows down the regression tests too much. Sure, makes sense to me. We do track the individual 'stage_duration' but we don't track things down to a per-regressio

Recording test runtimes with the buildfarm

Hi, I've just had some thoughts about the possible usefulness of having the buildfarm record the run-time of each regression test to allow us to have some sort of ability to track the run-time history of each test. I thought the usefulness might be two-fold: 1. We could quickly identify when som

Re: [PATCH] Add support for choosing huge page size

Thanks again Thomas, > Oh, so maybe we need a configure test for them? And if you don't have > it, a runtime error if you try to set the page size to something other > than 0 (like we do for effective_io_concurrency if you don't have a > posix_fadvise() function). Ahh, yes, that sounds reasonabl

Re: Parallel Seq Scan vs kernel read ahead

On Wed, 10 Jun 2020 at 17:39, David Rowley wrote: > > On Wed, 10 Jun 2020 at 17:21, Thomas Munro wrote: > > I also heard from Andres that he likes this patch with his AIO > > prototype, because of the way request merging works. So it seems like > > there are several reasons to want it. > > > > B

Re: Auto-vectorization speeds up multiplication of large-precision numerics

On Wed, 10 Jun 2020 at 04:20, Peter Eisentraut wrote: > > On 2020-06-09 13:50, Amit Khandekar wrote: > > Also, the regress/sql/numeric_big test itself speeds up by 80% > > That's nice. I can confirm the speedup: > > -O3 without the patch: > > numeric ... ok 737

walkdir does not honor elevel because of call to AllocateDir, possibly causing issues in abort handler

walkdir is used indirectly in the abort handler of SharedFileSetOnDetach, which has the following comment: /* * Callback function that will be invoked when this backend detaches from a * DSM segment holding a SharedFileSet that it has created or attached to. If * we are the last to detach, then t

Re: calling procedures is slow and consumes extra much memory against calling function

st 10. 6. 2020 v 12:26 odesílatel Amit Khandekar napsal: > On Sat, 16 May 2020 at 00:07, Pavel Stehule > wrote: > > > > Hi > > > >>> > >>> The problem is in plpgsql implementation of CALL statement > >>> > >>> In non atomic case - case of using procedures from DO block, the > expression plan is

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Wed, Jun 10, 2020 at 5:02 PM Dilip Kumar wrote: > > On Wed, Jun 10, 2020 at 4:00 PM Amit Kapila wrote: > > > > > 2. Files should not be closed at the end of the transaction: > > > Currently, files opened with BufFileCreateShared/BufFileOpenShared are > > > registered to be closed on EOXACT. B

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Wed, Jun 10, 2020 at 4:00 PM Amit Kapila wrote: > > On Wed, Jun 10, 2020 at 2:30 PM Dilip Kumar wrote: > > > > > > Currently, I am done with a working prototype of using the BufFile > > infrastructure for the tempfile. Meanwhile, I want to discuss a few > > interface changes required for the

Re: Atomic operations within spinlocks

On Fri, Jun 5, 2020 at 8:19 PM Andres Freund wrote: > Randomly noticed while looking at the code: > uint64 flagbit = UINT64CONST(1) << (uint64) type; > > that shouldn't be 64bit, right? I'm going to admit ignorance here. What's the proper coding rule? -- Robert Haas EnterpriseD

Re: FailedAssertion at ReorderBufferCheckMemoryLimit()

On Wed, Jun 10, 2020 at 9:15 AM Fujii Masao wrote: > > On 2020/06/10 12:00, Amit Kapila wrote: > > On Tue, Jun 9, 2020 at 2:28 PM Amit Kapila wrote: > >> > >> On Tue, Jun 9, 2020 at 1:56 PM Fujii Masao > >> wrote: > >>> > >> > >>> I wonder if we may > >>> need to keep evicting the transactions

Re: Inlining of couple of functions in pl_exec.c improves performance

On Tue, 9 Jun 2020 at 21:49, Pavel Stehule wrote: > Is your patch in commitfest in commitfest application? Thanks for reminding me. Just added. https://commitfest.postgresql.org/28/2590/

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Wed, Jun 10, 2020 at 2:30 PM Dilip Kumar wrote: > > > Currently, I am done with a working prototype of using the BufFile > infrastructure for the tempfile. Meanwhile, I want to discuss a few > interface changes required for the BufFIle infrastructure. > > 1. Support read-write mode for "BufFil

Re: calling procedures is slow and consumes extra much memory against calling function

On Sat, 16 May 2020 at 00:07, Pavel Stehule wrote: > > Hi > >>> >>> The problem is in plpgsql implementation of CALL statement >>> >>> In non atomic case - case of using procedures from DO block, the >>> expression plan is not cached, and plan is generating any time. This is >>> reason why it i

Re: Relation wide 'LIKE' clause

‐‐‐ Original Message ‐‐‐ On Wednesday, June 10, 2020 12:08 PM, Peter Eisentraut wrote: > On 2020-06-10 11:42, Georgios wrote: > > > Postgres create table statement supports `LIKE source_table [like_option... > > ]` > > to specify `a table from which the new table automatically copies

Re: Relation wide 'LIKE' clause

On 2020-06-10 11:42, Georgios wrote: Postgres create table statement supports `LIKE source_table [like_option... ]` to specify `a table from which the new table automatically copies all column names, their data types, and their not-null constraints.` according to documentation [1]. I am wonderin

Re: typos in comments referring to macros

On Wed, Jun 10, 2020 at 2:47 PM John Naylor wrote: > > It should be BLCKSZ and LOBLKSIZE, as in the attached. > LGTM on first look. I'll push either later today or tomorrow. -- With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com

Relation wide 'LIKE' clause

Hi, Postgres create table statement supports `LIKE source_table [like_option... ]` to specify `a table from which the new table automatically copies all column names, their data types, and their not-null constraints.` according to documentation [1]. I am wondering if a similar clause would make s

typos in comments referring to macros

It should be BLCKSZ and LOBLKSIZE, as in the attached. -- John Naylorhttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services block-size-comment.patch Description: Binary data

Re: PATCH: logical_work_mem and logical streaming of large in-progress transactions

On Sun, Jun 7, 2020 at 5:06 PM Dilip Kumar wrote: > > On Thu, Jun 4, 2020 at 2:05 PM Dilip Kumar wrote: > > > > On Wed, Jun 3, 2020 at 2:43 PM Amit Kapila wrote: > > > > > > On Tue, Jun 2, 2020 at 7:53 PM Dilip Kumar wrote: > > > > > > > > On Tue, Jun 2, 2020 at 4:56 PM Amit Kapila > > > > wr

Re: Is it useful to record whether plans are generic or custom?

At Wed, 10 Jun 2020 10:50:58 +0900, torikoshia wrote in > On 2020-06-08 20:45, Masahiro Ikeda wrote: > > > BTW, I found that the dependency between function's comments and > > the modified code is broken at latest patch. Before this is > > committed, please fix it. > > ``` > > diff --git a/src/

Re: Physical replication slot advance is not persistent

At Wed, 10 Jun 2020 15:53:53 +0900, Michael Paquier wrote in > On Tue, Jun 09, 2020 at 09:01:13PM +0300, Alexey Kondratov wrote: > > Yes, there was a ReplicationSlotsComputeRequiredLSN() call inside > > pg_physical_replication_slot_advance() in the v5 of the patch: > > > > But later it has been

Re: Terminate the idle sessions

On Wed, Jun 10, 2020 at 05:20:36AM +, Li Japin wrote: > I agree with you. But we can also give the user to control the idle > sessions lifetime. Idle sessions staying around can be a problem in the long run as they impact snapshot building. You could for example use a background worker to do

Re: Speedup usages of pg_*toa() functions

> "Ranier" == Ranier Vilela writes: Ranier> Sorry, my mistake. Ranier> uvalue = (uint64) 0 - value; This doesn't gain anything over the original, and it has the downside of hiding an int64 to uint64 conversion that is actually quite sensitive. For example, it might tempt someone to rewrit

Re: Resetting spilled txn statistics in pg_stat_replication

On Tue, 9 Jun 2020 at 17:24, Magnus Hagander wrote: > > > > On Tue, Jun 9, 2020 at 9:12 AM Masahiko Sawada > wrote: >> >> On Tue, 2 Jun 2020 at 18:34, Amit Kapila wrote: >> > >> > On Tue, Jun 2, 2020 at 11:48 AM Masahiko Sawada >> > wrote: >> > > >> > > Hi all, >> > > >> > > Tracking of spille