Re: [Patch] ALTER SYSTEM READ ONLY

On Fri, Jul 24, 2020 at 6:28 AM Soumyadeep Chakraborty < soumyadeep2...@gmail.com> wrote: > > On Thu, Jun 18, 2020 at 7:54 AM Robert Haas wrote: > > I think we'd want the FIRST write operation to be the end-of-recovery > > checkpoint, before the system is fully read-write. And then after that > >

Re: heap_abort_speculative() sets xmin to Invalid* without HEAP_XMIN_INVALID

On Thu, Jul 23, 2020 at 2:49 PM Alvaro Herrera wrote: > On 2020-Jul-23, Andres Freund wrote: > > > I think we should change heap_abort_speculative() to set > > HEAP_XMIN_INVALID in master. > > +1 +1 > +1 for doing it as an additional fix, with a fat comment somewhere > explaining where such tupl

Re: Making CASE error handling less surprising

Andres Freund writes: > I'm a bit worried about a case like: > CREATE FUNCTION yell(int, int) > RETURNS int > IMMUTABLE > LANGUAGE SQL AS $$ >SELECT CASE WHEN $1 != 0 THEN 17 / $2 ELSE NULL END > $$; > EXPLAIN SELECT yell(g.i, 0) FROM generate_series(1, 10) g(i); > I don't think the paramet

Re: Default setting for enable_hashagg_disk

On Thu, Jul 23, 2020 at 6:22 PM Tomas Vondra wrote: > So let me share some fresh I/O statistics collected on the current code > using iosnoop. I've done the tests on two different machines using the > "aggregate part" of TPC-H Q17, i.e. essentially this: > > SELECT * FROM ( > SELECT >

Re: [Patch] ALTER SYSTEM READ ONLY

Hi, > From f0188a48723b1ae7372bcc6a344ed7868fdc40fb Mon Sep 17 00:00:00 2001 > From: Amul Sul > Date: Fri, 27 Mar 2020 05:05:38 -0400 > Subject: [PATCH v3 2/6] Add alter system read only/write syntax > > Note that syntax doesn't have any implementation. > --- > src/backend/nodes/copyfuncs.c

Re: [PATCH] audo-detect and use -moutline-atomics compilation flag for aarch64

Hi, On 2020-07-01 15:40:38 +, Zidenberg, Tsahi wrote: > Outline-atomics is a gcc compilation flag that adds runtime detection > of weather or not the cpu supports atomic instructions. CPUs that > don't support atomic instructions will use the old > load-exclusive/store-exclusive instructions.

Re: [Patch] ALTER SYSTEM READ ONLY

On Thu, Jun 18, 2020 at 7:54 AM Robert Haas wrote: > I think we'd want the FIRST write operation to be the end-of-recovery > checkpoint, before the system is fully read-write. And then after that > completes you could do other things. I can't see why this is necessary from a correctness or perfor

Re: [Patch] ALTER SYSTEM READ ONLY

On Thu, Jul 23, 2020 at 3:57 AM Amul Sul wrote: > Well, once we've initiated the change to a read-only state, we probably want > to > always either finish that change or go back to read-write, even if the process > that initiated the change is interrupted. Leaving the system in a > half-way-in-b

Re: Default setting for enable_hashagg_disk

On Fri, Jul 17, 2020 at 5:13 PM Jeff Davis wrote: > The patch itself looks reasonable to me. I don't see a lot of obvious > dangers, but perhaps someone would like to take a closer look at the > planner changes as you suggest. Attached is v3 of the hash_mem_multiplier patch series, which now has

Re: heap_abort_speculative() sets xmin to Invalid* without HEAP_XMIN_INVALID

On 2020-Jul-23, Andres Freund wrote: > I think we should change heap_abort_speculative() to set > HEAP_XMIN_INVALID in master. +1 > But we can't really do anything about > existing tuples without it - therefore we will have to forever take care > about encountering that combination :(. > > Perh

Re: Making CASE error handling less surprising

Hi, On 2020-07-23 16:56:44 -0400, Tom Lane wrote: > Andres Freund writes: > > Hm. Would SQL function inlining be a problem? It looks like that just > > substitutes parameters. Before calling > > eval_const_expressions_mutator(). So we'd not know not to evaluate such > > "pseudo constants". And t

Re: Making CASE error handling less surprising

Andres Freund writes: > Hm. Would SQL function inlining be a problem? It looks like that just > substitutes parameters. Before calling > eval_const_expressions_mutator(). So we'd not know not to evaluate such > "pseudo constants". And that'd probably be confusing, especially > because it's not ex

Re: Making CASE error handling less surprising

Hi, On 2020-07-23 13:42:08 -0700, Andres Freund wrote: > On 2020-07-23 16:34:25 -0400, Tom Lane wrote: > > Andres Freund writes: > > > It doesn't seem like it'd be too hard to implement that, but that it'd > > > probably be fairly bulky because we'd need to track more state across > > > recursive

Re: Making CASE error handling less surprising

Hi, On 2020-07-23 16:34:25 -0400, Tom Lane wrote: > Andres Freund writes: > > Is there an argument to continue simplifying expressions within case > > when only involving "true" constants even with not leakproof functions, > > but only simplify "pseudo" constants like parameters with leakproof >

Re: Making CASE error handling less surprising

Andres Freund writes: > Is there an argument to continue simplifying expressions within case > when only involving "true" constants even with not leakproof functions, > but only simplify "pseudo" constants like parameters with leakproof > functions? I.e CASE WHEN ... THEN 1 / 0 would still raise

Re: Making CASE error handling less surprising

Andres Freund writes: > Is there any concern about having to do additional lookups for > leakproofness? It doesn't seem likely to me since we already need to do > lookups for the FmgrInfo? No, we could easily fix it so that one syscache lookup gets both the provolatile and proleakproof markings.

Re: Making CASE error handling less surprising

Hi, On 2020-07-23 21:56:26 +0200, Pavel Stehule wrote: > I am afraid of a performance impact. > > lot of people expects constant folding everywhere now and I can imagine > query like > > SELECT CASE col1 WHEN 1 THEN upper('hello') ELSE upper('bye') END FROM ... > > Now, it is optimized well, b

Re: Making CASE error handling less surprising

čt 23. 7. 2020 v 21:56 odesílatel Pavel Stehule napsal: > > > čt 23. 7. 2020 v 21:43 odesílatel Tom Lane napsal: > >> Andres Freund writes: >> > On 2020-07-23 18:50:32 +0100, Dagfinn Ilmari Mannsåker wrote: >> >> Would it be feasible to set up an exception handler when constant- >> >> folding c

Re: Making CASE error handling less surprising

Hi, On 2020-07-23 15:43:44 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2020-07-23 18:50:32 +0100, Dagfinn Ilmari Mannsåker wrote: > >> Would it be feasible to set up an exception handler when constant- > >> folding cases that might not be reached, and leave the expression > >> unfolded

Re: Making CASE error handling less surprising

čt 23. 7. 2020 v 21:43 odesílatel Tom Lane napsal: > Andres Freund writes: > > On 2020-07-23 18:50:32 +0100, Dagfinn Ilmari Mannsåker wrote: > >> Would it be feasible to set up an exception handler when constant- > >> folding cases that might not be reached, and leave the expression > >> unfolde

Re: Making CASE error handling less surprising

Andres Freund writes: > On 2020-07-23 18:50:32 +0100, Dagfinn Ilmari Mannsåker wrote: >> Would it be feasible to set up an exception handler when constant- >> folding cases that might not be reached, and leave the expression >> unfolded only if an error was thrown, or does that have too much >> ov

heap_abort_speculative() sets xmin to Invalid* without HEAP_XMIN_INVALID

Hi, After adding a few assertions to validate the connection scalability patch I saw failures that also apply to master: I added an assertion to TransactionIdIsCurrentTransactionId(), *IsInProgress(), ... ensuring that the xid is within an expected range. Which promptly failed in isolation tests.

Re: Making CASE error handling less surprising

Hi, On 2020-07-23 18:50:32 +0100, Dagfinn Ilmari Mannsåker wrote: > Tom Lane writes: > > > Every so often we get a complaint like [1] about how a CASE should have > > prevented a run-time error and didn't, because constant-folding tried > > to evaluate a subexpression that would not have been en

HOT vs freezing issue causing "cannot freeze committed xmax"

Hi, In a development branch of mine Thomas / the CF bot found a relatively rare regression failures. That turned out to be because there was an edge case in which heap_page_prune() was a bit more pessimistic than lazy_scan_heap(). But I wonder if this isn't an issue more broadly: The issue I am c

Re: 'with' regression tests fails rarely (and spuriously)

Hi, On 2020-07-23 13:05:32 -0400, Tom Lane wrote: > Andres Freund writes: > > I've twice seen the below failure when running tests in a loop (to > > verify another rare issue in a patch is fixed): > > Weird. It sort of looks like autovacuum came along and changed the > stats for those tables, b

Re: Making CASE error handling less surprising

Tom Lane writes: > Every so often we get a complaint like [1] about how a CASE should have > prevented a run-time error and didn't, because constant-folding tried > to evaluate a subexpression that would not have been entered at run-time. > > It struck me that it would not be hard to improve this

[BUG] Error in BRIN summarization

One of our clients caught an error "failed to find parent tuple for heap-only tuple at (50661,130) in table "tbl'" in PostgreSQL v12. Steps to reproduce (REL_12_STABLE): 1) Create table with primary key, create brin index, fill table with some initial data: create table tbl (id int primary k

Re: 'with' regression tests fails rarely (and spuriously)

Andres Freund writes: > I've twice seen the below failure when running tests in a loop (to > verify another rare issue in a patch is fixed): Weird. It sort of looks like autovacuum came along and changed the stats for those tables, but I didn't think they were big enough to draw autovac's attent

Re: Loaded footgun open_datasync on Windows

On Fri, Sep 14, 2018 at 3:32 AM Michael Paquier wrote: > On Fri, Sep 14, 2018 at 08:43:18AM +0200, Laurenz Albe wrote: > > > If it turns out not to break anything, would you consider backpatching? > > On the one hand it fixes a bug, on the other hand it affects all > > frontend executables... > >

Making CASE error handling less surprising

Every so often we get a complaint like [1] about how a CASE should have prevented a run-time error and didn't, because constant-folding tried to evaluate a subexpression that would not have been entered at run-time. It struck me that it would not be hard to improve this situation a great deal. If

'with' regression tests fails rarely (and spuriously)

Hi, I've twice seen the below failure when running tests in a loop (to verify another rare issue in a patch is fixed): diff -du10 /home/andres/src/postgresql/src/test/regress/expected/with.out /home/andres/build/postgres/dev-assert/vpath/src/test/regress/results/with.out --- /home/andres/src/pos

Re: [Patch] ALTER SYSTEM READ ONLY

On Thu, Jul 23, 2020 at 3:42 AM Amul Sul wrote: > The aim of this feature is preventing new WAL records from being generated, > not > preventing them from being flushed to disk, or streamed to standbys, or > anything > else. The rest should happen as normal. > > If you can't flush WAL, then you

Re: Building 12.3 from source on Mac

I'd like to add that MacPorts installs everything to /opt/ and /opt/local unless someone configures other path. You can also easily check is something from homebrew installation by running 'brew config' and looking at HOMEBREW_PREFIX entry. Regards, Pavel чт, 23 июл. 2020 г. в 19:05, Paul Förster

Re: Building 12.3 from source on Mac

Hi Tom, > On 23. Jul, 2020, at 16:50, Tom Lane wrote: > > You should see what "file" reports these as, but there's a good > bet that these are 32-bit code and won't even run on Catalina. yes, they seem pretty old: paul@meerkat:/usr/local/lib$ file libintl.* libintl.3.4.3.dylib: Mach-

Re: Building 12.3 from source on Mac

=?utf-8?Q?Paul_F=C3=B6rster?= writes: > there is not a single symlink in /usr/local/lib: Not only that, but look at the file dates: > -rw-r--r--+ 1 root wheel 253512 Sep 23 2006 libintl.3.4.3.dylib > -rw-r--r--+ 1 root wheel 286284 Sep 23 2006 libintl.a > -rw-r--r--+ 1 root wheel 8

Re: Building 12.3 from source on Mac

Hi Tom, > On 23. Jul, 2020, at 16:03, Tom Lane wrote: > > Looks like what you lack is a symlink libintl.dylib -> libintl.3.4.3.dylib > in /usr/local/lib. It's not real clear to me why you'd have .a and .la > files and no versionless symlink, because all of those files would > just be used for l

Re: Open Item: Should non-text EXPLAIN always show properties?

On Thu, Jun 25, 2020 at 08:41:43AM -0400, James Coleman wrote: > On Thu, Jun 25, 2020 at 5:15 AM David Rowley wrote: > > Over on [1] Justin mentions that the non-text EXPLAIN ANALYZE should > > always show the "Disk Usage" and "HashAgg Batches" properties. I > > agree with this. show_wal_usage()

Re: Building 12.3 from source on Mac

=?utf-8?Q?Paul_F=C3=B6rster?= writes: >> On 23. Jul, 2020, at 15:42, Tom Lane wrote: >> However, Apple's toolchain doesn't search /usr/local by default, >> I believe. You'll need to add something along the line of >> --with-includes=/usr/local/include --with-libs=/usr/local/lib >> to your config

Re: Building 12.3 from source on Mac

Hi Tom, > On 23. Jul, 2020, at 15:42, Tom Lane wrote: > > Kind of looks like you *did* install gettext as Daniel suggested > (macOS proper would never put anything under /usr/local). Maybe > you did not ask for that specifically, but installed some package > that requires it? > > However, Appl

Re: Transactions involving multiple postgres foreign servers, take 2

On Wed, Jul 22, 2020 at 12:42 PM Masahiko Sawada < masahiko.saw...@2ndquadrant.com> wrote: > On Sat, 18 Jul 2020 at 01:55, Fujii Masao > wrote: > > > > > > > > On 2020/07/16 14:47, Masahiko Sawada wrote: > > > On Tue, 14 Jul 2020 at 11:19, Fujii Masao > wrote: > > >> > > >> > > >> > > >> On 2020

Re: Building 12.3 from source on Mac

=?utf-8?Q?Paul_F=C3=B6rster?= writes: >> On 23. Jul, 2020, at 12:37, Daniel Gustafsson wrote: >> gettext is not shipped by default with macOS, you will have to install it >> separately via your favourite package manager or by building from source. > But I am still wondering: mdfind spits out lib

Re: Building 12.3 from source on Mac

Hi Daniel, > On 23. Jul, 2020, at 12:37, Daniel Gustafsson wrote: > gettext is not shipped by default with macOS, you will have to install it > separately via your favourite package manager or by building from source. To > verify you can always search your system for the required header file: >

Re: Add session statistics to pg_stat_database

On Wed, Jul 8, 2020 at 4:17 PM Laurenz Albe wrote: > Here is a patch that adds the following to pg_stat_database: > - number of connections > Is it expected behaviour to not count idle connections? The connection is included after it is aborted but not while it was idle. > - number of sessions

Re: Parallel copy

On Thu, Jul 23, 2020 at 9:22 AM Amit Kapila wrote: > >> >> I ran tests for partitioned use cases - results are similar to that of non partitioned cases[1]. > > > I could see the gain up to 10-11 times for non-partitioned cases [1], can we use similar test case here as well (with one of the indexes

Re: Why it is not possible to create custom AM which behaves similar to btree?

On 23.07.2020 03:11, Tom Lane wrote: Konstantin Knizhnik writes: But then I get error for btfloat48cmp and btfloat84cmp functions: ERROR: associated data types must be specified for index support function You need to specify the amproclefttype and amprocrighttype types you want the functio

Re: Compatible defaults for LEAD/LAG

čt 23. 7. 2020 v 13:29 odesílatel Daniel Gustafsson napsal: > > On 13 Jul 2020, at 19:23, Pavel Stehule wrote: > > > ne 31. 5. 2020 v 22:02 odesílatel Vik Fearing > napsal: > > On 5/31/20 9:53 PM, Tom Lane wrote: > > > Vik Fearing mailto:v...@postgresfriends.org

RE: Index Skip Scan (new UniqueKeys)

> > One UniqueKey can have multiple corresponding expressions, which gives us > also possibility of having one unique key with (t1.a, t2.a) and it looks now > similar to EquivalenceClass. > I believe the current definition of a unique key with two expressions (t1.a, t2.a) means that it's unique

Re: explain HashAggregate to report bucket and memory stats

> On 12 Jul 2020, at 21:52, Daniel Gustafsson wrote: > This thread has stalled and the patch has been Waiting on Author since March, > and skimming the thread there seems to be questions raised over the value > proposition. Is there progress happening behind the scenes or should we close > this

Re: Compatible defaults for LEAD/LAG

> On 13 Jul 2020, at 19:23, Pavel Stehule wrote: > ne 31. 5. 2020 v 22:02 odesílatel Vik Fearing > napsal: > On 5/31/20 9:53 PM, Tom Lane wrote: > > Vik Fearing mailto:v...@postgresfriends.org>> > > writes: > >> postgres=# SELECT LAG(n, 1, -99) OVER (ORDER BY

Re: Parallel Seq Scan vs kernel read ahead

On Wed, Jul 22, 2020 at 10:03 AM Thomas Munro wrote: > > On Wed, Jul 22, 2020 at 3:57 PM Amit Kapila wrote: > > Yeah, that is true but every time before the test the same amount of > > data should be present in shared buffers (or OS cache) if any which > > will help in getting consistent results.

Re: [Patch] ALTER SYSTEM READ ONLY

On Thu, Jul 23, 2020 at 6:08 AM Soumyadeep Chakraborty wrote: > > Hi Amul, > Thanks, Soumyadeep for looking and putting your thoughts on the patch. > On Tue, Jun 16, 2020 at 6:56 AM amul sul wrote: > > The proposed feature is built atop of super barrier mechanism commit[1] to > > coordinate > >

Re: Dumping/restoring fails on inherited generated column

On Thu, 16 Jul 2020 at 04:29, Tom Lane wrote: > > Peter Eisentraut writes: > >> Right, there were a number of combinations that were not properly > >> handled. The attached patch should fix them all. It's made against > >> PG12 but also works on master. See contained commit message and > >> do

Re: [Patch] ALTER SYSTEM READ ONLY

On Thu, Jul 23, 2020 at 4:34 AM SATYANARAYANA NARLAPURAM wrote: > > +1 to this feature and I have been thinking about it for sometime. There are > several use cases with marking database read only (no transaction log > generation). Some of the examples in a hosted service scenario are 1/ when >

Re: [Patch] ALTER SYSTEM READ ONLY

On Thu, Jul 23, 2020 at 3:33 AM Soumyadeep Chakraborty wrote: > > Hello, > > I think we should really term this feature, as it stands, as a means to > solely stop WAL writes from happening. > True. > The feature doesn't truly make the system read-only (e.g. dirty buffer > flushes may succeed the

Re: Building 12.3 from source on Mac

> On 23 Jul 2020, at 12:01, Paul Förster wrote: > If I leave out --enable-nls then building works fine and I get everything > without error. But why is there a problem with gettext? gettext is not shipped by default with macOS, you will have to install it separately via your favourite package m

Building 12.3 from source on Mac

Hi, I'm not sure this is the right list, but I have a problem concerning building PostgreSQL 12.3 from source on a Mac. I do: ./configure \ --prefix=${pgTargetDir} \ --enable-nls \ --with-perl \ --with-python \ --with-libxml \ --with-tclconfig=/usr/lib64 \ PG_SYSROOT

Re: Index Skip Scan (new UniqueKeys)

> On Tue, Jul 14, 2020 at 06:18:50PM +, Floris Van Nee wrote: > > Due to the other changes I made in > create_distinct_paths/query_has_uniquekeys_for, it will choose a correct plan > now, even without the EC_MUST_BE_REDUNDANT check though, so it's difficult to > give an actual failing test c

Re: Transactions involving multiple postgres foreign servers, take 2

On Fri, Jul 17, 2020 at 9:56 PM Fujii Masao wrote: > > > On 2020/07/16 14:47, Masahiko Sawada wrote: > > On Tue, 14 Jul 2020 at 11:19, Fujii Masao > wrote: > >> > >> > >> > >> On 2020/07/14 9:08, Masahiro Ikeda wrote: > I've attached the latest version patches. I've incorporated the review

Re: Parallel worker hangs while handling errors.

Thanks for reviewing and adding your thoughts, My comments are inline. On Fri, Jul 17, 2020 at 1:21 PM Bharath Rupireddy wrote: > > The same hang issue can occur(though I'm not able to back it up with a > use case), in the cases from wherever the EmitErrorReport() is called > from "if (sigsetjmp(