Re: Should we add GUCs to allow partition pruning to be disabled?

On 20 April 2018 at 14:07, Amit Langote wrote: > To clarify: if we're going to add a new parameter *for partitioned tables* > to configure whether or not pruning occurs, even if UPDATE and DELETE now > rely on constraint exclusion for pruning, we should ignore the

Re: [sqlsmith] Unpinning error in parallel worker

On Wed, Apr 18, 2018 at 11:43 AM, Jonathan Rudenberg wrote: > On Tue, Apr 17, 2018, at 19:31, Thomas Munro wrote: >> On Wed, Apr 18, 2018 at 11:01 AM, Jonathan Rudenberg >> wrote: >> > Yep, I think I know approximately what it looked like, I've

Re: Re: Is there a memory leak in commit 8561e48?

On Fri, Apr 20, 2018 at 10:00:38AM +0800, jian.l...@i-soft.com.cn wrote: > what about just free _SPI_stack in AtEOXact_SPI? if the transaction > end was initiated by SPI , AtEOXact_SPI will do nothing. For example: > @@ -283,6 +295,8 @@ AtEOXact_SPI(bool isCommit) >

Re: Should we add GUCs to allow partition pruning to be disabled?

On 20 April 2018 at 14:33, Amit Langote wrote: > On 2018/04/20 11:18, David Rowley wrote: >> 4. Replace test doing (constraint_exclusion == >> CONSTRAINT_EXCLUSION_PARTITION) with (enable_partition_pruning). >> 5. Get rid of CONSTRAINT_EXCLUSION_PARTITION. > > About

Re: Oddity in tuple routing for foreign partitions

(2018/04/20 9:48), Amit Langote wrote: On 2018/04/19 21:42, Etsuro Fujita wrote: (2018/04/19 16:43), Amit Langote wrote: Would it be a good idea to explain *why* we need to replace the RTE in the first place? Afaics, it's for deparseColumnRef() to find the correct relation when it uses

Re: Should we add GUCs to allow partition pruning to be disabled?

Hi. On 2018/04/20 11:18, David Rowley wrote: > On 20 April 2018 at 14:07, Amit Langote wrote: >> To clarify: if we're going to add a new parameter *for partitioned tables* >> to configure whether or not pruning occurs, even if UPDATE and DELETE now >> rely on

Re: Should we add GUCs to allow partition pruning to be disabled?

On 20 April 2018 at 14:07, Amit Langote wrote: > To clarify: if we're going to add a new parameter *for partitioned tables* > to configure whether or not pruning occurs, even if UPDATE and DELETE now > rely on constraint exclusion for pruning, we should ignore the

Re: Should we add GUCs to allow partition pruning to be disabled?

On 2018/04/19 21:50, Ashutosh Bapat wrote: > On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote >> I can imagine having a enable_partition_pruning which defaults to true, if >> only to avoid the performance overhead of pruning code when a user knows >> for sure that it won't help for some queries.

Re: Re: Is there a memory leak in commit 8561e48?

what about just free _SPI_stack in AtEOXact_SPI? if the transaction end was initiated by SPI , AtEOXact_SPI will do nothing. for example: @@ -283,6 +295,8 @@ AtEOXact_SPI(bool isCommit) errmsg("transaction left non-empty SPI stack"),

Re: Corrupted btree index on HEAD because of covering indexes

On Thu, Apr 19, 2018 at 10:47:02PM +0300, Teodor Sigaev wrote: > I tried to minimize Michael's test case and add it to patch. I cannot comment on the actual fix as I lack background in the area, but having a test case and even more having pg_upgrade do some work on those pages are good things.

Re: Corrupted btree index on HEAD because of covering indexes

On Thu, Apr 19, 2018 at 11:59 AM, Teodor Sigaev wrote: > Agree. Hope, nobody found a way how to use amcheck module in production to > serve user requests. But, it should be implemented before BETA stage, in > opposite case we will get a lot of objections. It shouldn't take that

Re: ON CONFLICT DO UPDATE for partitioned tables

On 2018/04/20 4:40, Alvaro Herrera wrote: > Alvaro Herrera wrote: >> Amit Langote wrote: >> >>> Yeah, I too have wondered in the past what it would take to make >>> equalTupDescs() return true for parent and partitions. Maybe we can make >>> it work by looking a bit harder than I did then. >> >>

Re: Problem while setting the fpw with SIGHUP

On Thu, Apr 19, 2018 at 07:11:43PM +0530, Amit Kapila wrote: > On Thu, Apr 19, 2018 at 7:19 AM, Michael Paquier wrote: >> And, er, actually, I was thinking again about the case where a user >> wants to disable full_page_writes temporarily to do some bulk load and >> then

Re: Corrupted btree index on HEAD because of covering indexes

On Thu, Apr 19, 2018 at 2:00 PM, Alexander Korotkov wrote: > - if (ItemPointerIsValid(leafhikey)) > + if (ItemPointerGetBlockNumberNoCheck(leafhikey) != InvalidBlockNumber) > > Should we use BTreeInnerTupleGetDownLink() as soon as we use > BTreeInnerTupleSetDownLink()

Re: Oddity in tuple routing for foreign partitions

Fujita-san, Thanks for the updated patch. On 2018/04/19 21:42, Etsuro Fujita wrote: > (2018/04/19 16:43), Amit Langote wrote: >> Would it be a good idea to explain *why* we need to replace the RTE in the >> first place?  Afaics, it's for deparseColumnRef() to find the correct >> relation when it

Re: Is there a memory leak in commit 8561e48?

On Thu, Apr 19, 2018 at 03:10:42PM +0900, Michael Paquier wrote: > You are right. I can easily see the leak if I use for example a > background worker which connects to a database, and launches many > transactions in a row. The laziest reproducer I have is to patch one of > my bgworkers to

Re: Is a modern build system acceptable for older platforms

> > My gut reaction to Catalin's list is that requiring C+11 is a pretty > darn high bar to clear for older platforms. > It's only for latest version and we can support version 3.9 with C++98 I think at least 5 years. 3.9.6 was realease in November 10, 2017 . That's a pretty big shift from the

Re: Built-in connection pooling

> On Fri, Apr 20, 2018 at 07:58:00AM +0900, Tatsuo Ishii wrote: >> Yeah. Since SCRAM auth is implemented, some connection poolers >> including Pgpool-II are struggling to adopt it. > > Er, well. pgpool is also taking advantage of MD5 weaknesses... While > SCRAM fixes this class of problems, and

Re: initdb fails to initialize data directory

On Thu, Apr 19, 2018 at 04:59:44PM -0300, Alvaro Herrera wrote: > Heikki Linnakangas wrote: >> Nitpick: using MAXPGPATH seems for the buffer size seems to wrong to me. >> We're not storing a path here. MAXPGPATH is 1024 by default, which seems >> fine, but I would've spelled it out directly as

Re: Built-in connection pooling

On Fri, Apr 20, 2018 at 07:58:00AM +0900, Tatsuo Ishii wrote: > Yeah. Since SCRAM auth is implemented, some connection poolers > including Pgpool-II are struggling to adopt it. Er, well. pgpool is also taking advantage of MD5 weaknesses... While SCRAM fixes this class of problems, and channel

Re: Double-writes, take two?

On Thu, Apr 19, 2018 at 06:28:01PM -0400, Robert Haas wrote: > On Wed, Apr 18, 2018 at 2:22 AM, Michael Paquier wrote: >> I was thinking about this problem, and it looks that one approach for >> double-writes would be to introduce it as a secondary WAL stream >> independent

Re: Double-writes, take two?

On Wed, Apr 18, 2018 at 11:40:51AM +0200, Fabien COELHO wrote: >> - double-write buffers use a pre-decided numbers of pages (32 for the >> checkpointer, 128 divided into 4 buckets for the backends), which are >> synced into disk once each batch is full. > >> - The double-write file of the

Re: Built-in connection pooling

> I think there's plenty things that don't really make sense solving > outside of postgres: > - additional added hop / context switches due to external pooler This is only applied to external process type pooler (like Pgpool-II). > - temporary tables > - prepared statements > - GUCs and other

Re: Double-writes, take two?

On Wed, Apr 18, 2018 at 2:22 AM, Michael Paquier wrote: > I was thinking about this problem, and it looks that one approach for > double-writes would be to introduce it as a secondary WAL stream > independent from the main one: > - Once a buffer is evicted from shared buffers

Re: Event trigger bugs (was Re: Repeated crashes in GENERATED ... AS IDENTITY tests)

Tom Lane wrote: > Hence, two questions: > > * Should EventTriggerTableRewrite do > > if (!currentEventTriggerState || > currentEventTriggerState->commandCollectionInhibited) > return; > > like most of the other functions, or should it just check for null >

Re: Repeated crashes in GENERATED ... AS IDENTITY tests

> "Alvaro" == Alvaro Herrera writes: Alvaro> I can't look further into this now -- maybe next week if nobody Alvaro> has beaten me into it. My guess is that the identity stuff is Alvaro> not setting state like event triggers expect. I think this is unrelated to

Event trigger bugs (was Re: Repeated crashes in GENERATED ... AS IDENTITY tests)

I wrote: > I'm inclined to say that whether or not there's a bug here (and there > well may be, it doesn't seem like a crash is a good thing), this is > bad test design and we need to change it. So my suspicion was aroused by the fact that, unlike almost every other function in event_trigger.c,

Re: Repeated crashes in GENERATED ... AS IDENTITY tests

Alvaro Herrera writes: > Of the machines you listed, conchuela has gdb installed, which gives us > a nice backtrace of the crash, pasted below, which seems to blame event > triggers. Of the tests in the same parallel group as identity, the test > fast_default seems to be

Re: Documentation for bootstrap data conversion

John Naylor writes: > I've attached a patch that mostly touches boilerplate comments in the > headers and data files. I couldn't resist editing some comments for > clarity and consistency. Pushed, along with a little bit of extra tweaking to fix random discrepancies in the

Re: Built-in connection pooling

On 2018-04-19 15:01:24 -0400, Tom Lane wrote: > Only after you can say "there's nothing wrong with this that isn't > directly connected to its not being in-core" does it make sense to try > to push the logic into core. I think there's plenty things that don't really make sense solving outside of

Re: Repeated crashes in GENERATED ... AS IDENTITY tests

Tom Lane wrote: > The test case that's failing, in identity.sql, has been there since > early February; the lack of any crashes till more recently suggests > that something committed in mid-to-late March broke it. > > I have no idea what's going on there, but I think this is clearly > something

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

Andres Freund wrote: > On 2018-04-19 16:56:59 -0300, Alvaro Herrera wrote: > > Michael Paquier wrote: > > > > > Then, let's consider the beginning of the first commit fest of v12 as > > > judgement. Implementing radix tree for shared buffers is a long-term > > > project, which has no guarantee

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

On 2018-04-19 16:56:59 -0300, Alvaro Herrera wrote: > Michael Paquier wrote: > > > Then, let's consider the beginning of the first commit fest of v12 as > > judgement. Implementing radix tree for shared buffers is a long-term > > project, which has no guarantee to get merged, while a

Re: reloption to prevent VACUUM from truncating empty pages at the end of relation

2018-04-19 21:56 GMT+02:00 Alvaro Herrera : > Michael Paquier wrote: > > > Then, let's consider the beginning of the first commit fest of v12 as > > judgement. Implementing radix tree for shared buffers is a long-term > > project, which has no guarantee to get merged,

Re: initdb fails to initialize data directory

Heikki Linnakangas wrote: > Nitpick: using MAXPGPATH seems for the buffer size seems to wrong to me. > We're not storing a path here. MAXPGPATH is 1024 by default, which seems > fine, but I would've spelled it out directly as "line[1000]". Hmm ... yeah, kinda. Do you care about it strongly

Re: Corrupted btree index on HEAD because of covering indexes

I also think that we could have better conventional regression test coverage here. I tried to minimize Michael's test case and add it to patch. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ diff --git

Re: ON CONFLICT DO UPDATE for partitioned tables

Alvaro Herrera wrote: > Amit Langote wrote: > > > Yeah, I too have wondered in the past what it would take to make > > equalTupDescs() return true for parent and partitions. Maybe we can make > > it work by looking a bit harder than I did then. > > How about simply relaxing the tdtypeid test

Re: ON CONFLICT DO UPDATE for partitioned tables

On Thu, Apr 19, 2018 at 12:00 PM, Robert Haas wrote: > On Thu, Apr 19, 2018 at 1:20 PM, Alvaro Herrera > wrote: >> How about simply relaxing the tdtypeid test from equalTupleDescs? I >> haven't looked deeply but I think just checking whether or

Re: Built-in connection pooling

Stephen Frost writes: > Greetings, > * Andres Freund (and...@anarazel.de) wrote: >> On 2018-04-18 06:36:38 -0400, Heikki Linnakangas wrote: >>> However, I suspect that dealing with *all* of the issues is going to be hard >>> and tedious. And if there are any significant gaps,

Re: ON CONFLICT DO UPDATE for partitioned tables

On Thu, Apr 19, 2018 at 1:20 PM, Alvaro Herrera wrote: > Amit Langote wrote: >> Yeah, I too have wondered in the past what it would take to make >> equalTupDescs() return true for parent and partitions. Maybe we can make >> it work by looking a bit harder than I did

Re: Corrupted btree index on HEAD because of covering indexes

I would like to go and implement this check now, and if all goes well I may propose that you commit it to the master branch for v11. I don't see this as a new feature. I see it as essential testing infrastructure. What do you think about adding this new check soon? Agree. Hope, nobody found a way

Re: VM map freeze corruption

On Wed, Apr 18, 2018 at 7:06 PM, Alvaro Herrera wrote: > > > IMO the cause is the totally_frozen variable, which starts life in a > bogus state (true) and the different code paths can set it to the right > state, or by inaction end up deciding that the initial bogus

Re: Corrupted btree index on HEAD because of covering indexes

On Thu, Apr 19, 2018 at 9:42 AM, Teodor Sigaev wrote: > Interesting, contrib/amcheck doesn't find any error in index. Seems, it's > subject for further improvement. I think that you're right that this should be detectable by bt_index_parent_check(). I have an idea about how we

Re: Built-in connection pooling

Greetings, * Andres Freund (and...@anarazel.de) wrote: > On 2018-04-18 06:36:38 -0400, Heikki Linnakangas wrote: > > On 18/04/18 06:10, Konstantin Knizhnik wrote: > > > But there are still use cases which can not be covered y external > > > connection pooler. > > > > Can you name some? I

Re: Built-in connection pooling

On 2018-04-18 06:36:38 -0400, Heikki Linnakangas wrote: > On 18/04/18 06:10, Konstantin Knizhnik wrote: > > But there are still use cases which can not be covered y external > > connection pooler. > > Can you name some? I understand that the existing external connection > poolers all have their

Re: ON CONFLICT DO UPDATE for partitioned tables

Amit Langote wrote: > Yeah, I too have wondered in the past what it would take to make > equalTupDescs() return true for parent and partitions. Maybe we can make > it work by looking a bit harder than I did then. How about simply relaxing the tdtypeid test from equalTupleDescs? I haven't

Re: Corrupted btree index on HEAD because of covering indexes

I'll take a look tomorrow. Interesting, contrib/amcheck doesn't find any error in index. Seems, it's subject for further improvement. Nevertheless, seems, I found. In _bt_mark_page_halfdead() we use truncated high key IndexTuple as a storage of blocknumber of top parent to remove. And sets

Re: pruning disabled for array, enum, record, range type partition keys

Amit Langote wrote: > On 2018/04/19 6:45, Alvaro Herrera wrote: > > Please give this version another look. I also rewrote a couple of > > comments. > > Thanks, your rewritten version looks much better. Thanks! Pushed now. -- Álvaro Herrerahttps://www.2ndQuadrant.com/

Re: Built-in connection pooling

On Thu, Apr 19, 2018, 9:24 AM Konstantin Knizhnik, < k.knizh...@postgrespro.ru> wrote: > > > On 19.04.2018 07:46, Tsunakawa, Takayuki wrote: > > From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru] > > Oracle, for example, you can create dedicated and non-dedicated backends. > >> I wonder

Re: Problem while setting the fpw with SIGHUP

On Thu, Apr 19, 2018 at 7:19 AM, Michael Paquier wrote: > On Wed, Apr 18, 2018 at 10:52:51AM -0400, Robert Haas wrote: >> I would just document the risks. If the documentation says that you >> can't rely on the value until after the next checkpoint, or whatever >> the rule

Re: initdb fails to initialize data directory

Nikhil Sontakke wrote: > Intrigued, on digging down further, this is happening because we are > not using a long enough buffer to accept the output of "postgres -V" > in the find_other_exec() function. In our case, we had used > --with-extra-version option with configure which caused the output

Re: Built-in connection pooling

On 19.04.2018 07:46, Tsunakawa, Takayuki wrote: From: Konstantin Knizhnik [mailto:k.knizh...@postgrespro.ru] Oracle, for example, you can create dedicated and non-dedicated backends. I wonder why we do not want to have something similar in Postgres. Yes, I want it, too. In addition to

Re: Should we add GUCs to allow partition pruning to be disabled?

On Thu, Apr 19, 2018 at 5:02 PM, Amit Langote wrote: > On 2018/04/19 13:32, Ashutosh Bapat wrote: >> On Thu, Apr 19, 2018 at 2:54 AM, David Rowley >>> The more I think about this the more undecided I am as to whether we >>> need to add a GUC for this at all, so I'm

Re: Oddity in tuple routing for foreign partitions

(2018/04/19 19:03), Kyotaro HORIGUCHI wrote: At Tue, 17 Apr 2018 16:41:31 +0900, Etsuro Fujita wrote in<5ad5a52b.7050...@lab.ntt.co.jp> (2018/04/17 16:10), Amit Langote wrote: On 2018/04/17 11:13, Kyotaro HORIGUCHI wrote: If I'm reading this correctly,

Re: Oddity in tuple routing for foreign partitions

(2018/04/19 16:43), Amit Langote wrote: On 2018/04/18 21:55, Etsuro Fujita wrote: (2018/04/18 14:44), Amit Langote wrote: That the resultRelInfo received by BeginForeignInsert (when called by ExecInitRoutingInfo) could be a reused UPDATE result relation. 2. This is UPDATE and the

Re: make installcheck-world in a clean environment

06.04.2018 09:19, Alexander Lakhin wrote: To avoid overheating of this pretty hot discussion, I would like just to propose "a more elaborated fix" (for REL_10_STABLE and master). In fact, when we perform "make installcheck" it not only requires us to build ecpg, but it also rebuilds

Re: Boolean partitions syntax

Thanks for reviewing. At Wed, 18 Apr 2018 19:27:16 +0900, Amit Langote wrote in <7ac6b44e-4638-3320-1512-f6c03a28d...@lab.ntt.co.jp> > Horiguchi-san, > > Thank you for updating the patch. > > On 2018/04/16 16:17, Kyotaro HORIGUCHI wrote: > > the attached v6

Toast issues with OldestXmin going backwards

Various comments in GetOldestXmin mention the possibility of the oldest xmin going backward, and assert that this is actually safe. It's not. Consider: A table has a toastable column. A row is updated in a way that changes the toasted value. There are now two row versions pointing to different

Re: pruning disabled for array, enum, record, range type partition keys

Hi. On 2018/04/19 6:45, Alvaro Herrera wrote: > Amit Langote wrote: >> On Thu, Apr 19, 2018 at 12:01 AM, Alvaro Herrera >> wrote: > >>> Makes sense. Still, I was expecting that pruning of hash partitioning >>> would also work for pseudotypes, yet it doesn't. >> >> It

Re: Oddity in tuple routing for foreign partitions

At Tue, 17 Apr 2018 16:41:31 +0900, Etsuro Fujita wrote in <5ad5a52b.7050...@lab.ntt.co.jp> > (2018/04/17 16:10), Amit Langote wrote: > > On 2018/04/17 11:13, Kyotaro HORIGUCHI wrote: > >> If I'm reading this correctly, ExecInitParititionInfo calls > >>

Re: partitioning code reorganization

On 2018/04/18 5:18, Alvaro Herrera wrote: > Amit Langote wrote: > >> 0001-Make-copying-of-cached-partitioning-info-more-con.patch >> 0002-Cache-all-partitioning-info-under-one-memory-cont.patch >> 0003-Cache-partsupfunc-separately-from-PartitionKey.patch > > I'd rather not do these patches now,

Re: Documentation for bootstrap data conversion

On 4/18/18, Tom Lane wrote: > John Naylor writes: >> and dug through a bit to find cases where 'catalog' is clearly a >> better term. Most of these are in the pg_*.h/.dat file boilerplate >> comments, which would be easy enough to change with a script. If

Re: Is a modern build system acceptable for older platforms

> > The above is all about getting the build system to work at all. If that > isn't a showstopper there's a subsequent discussion to be had about older > platforms where one could get the build system to work but convenient > packages are missing. For example not even RHEL7 has any Python3

Re: Boolean partitions syntax

On 2018/04/18 19:27, Amit Langote wrote: > Please find attached an updated version of your patch. I think we'll need > to make some documentation changes and think about a way to back-patch > this to PG10. Added documentation changes. Also, noticed that there was no need to change the

Re: WIP: Covering + unique indexes.

Thank you, pushed Peter Geoghegan wrote: On Wed, Apr 18, 2018 at 10:47 PM, Teodor Sigaev wrote: Thank you, pushed. Thanks. I saw another preexisting issue, this time one that has been around since 2007. Commit bc292937 forgot to remove a comment above _bt_insertonpg()

Re: Is a modern build system acceptable for older platforms

About CMake: We can use 3.9 version very well, it has all features for us, at least for my postgres_cmake branch and I have the experience to introduce features to cmake special for postgres build. Also, cmake very easily build even for Solaris or AIX (on my webpage I have examples to build

Re: Excessive PostmasterIsAlive calls slow down WAL redo

On Thu, Apr 19, 2018 at 6:20 PM, Andres Freund wrote: > On April 18, 2018 8:05:50 PM PDT, Thomas Munro > wrote: >>By the way, these patches only use the death signal to make >>PostmasterIsAlive() fast, for use by busy loops like recovery. The

Re: Corrupted btree index on HEAD because of covering indexes

On Wed, Apr 18, 2018 at 10:29 PM, Teodor Sigaev wrote: > Will see... I'll take a look tomorrow. -- Peter Geoghegan

Re: Excessive PostmasterIsAlive calls slow down WAL redo

On April 18, 2018 8:05:50 PM PDT, Thomas Munro wrote: >On Wed, Apr 18, 2018 at 5:04 PM, Thomas Munro > wrote: >> On Wed, Apr 11, 2018 at 10:22 PM, Heikki Linnakangas > wrote: On Tue, Apr 10, 2018 at 12:53 PM,

Re: WIP: Covering + unique indexes.

On Wed, Apr 18, 2018 at 10:47 PM, Teodor Sigaev wrote: > Thank you, pushed. Thanks. I saw another preexisting issue, this time one that has been around since 2007. Commit bc292937 forgot to remove a comment above _bt_insertonpg() (the 'afteritem' stuff ended up being moved to

Re: Is there a memory leak in commit 8561e48?

On Thu, Apr 19, 2018 at 11:38:09AM +0800, jian.l...@i-soft.com.cn wrote: > in commit 8561e48, _SPI_stack alloc from TopMemoryContext. But > AtEOXact_SPI just set _SPI_stack = NULL. Is this a memory leak? You are right. I can easily see the leak if I use for example a background worker which

Re: Problem while updating a foreign table pointing to a partitioned table on foreign server

At Wed, 18 Apr 2018 13:23:06 +0530, Ashutosh Bapat wrote in > On Wed, Apr 18, 2018 at 9:43 AM, Kyotaro HORIGUCHI > wrote: > > > > Anyway I think we should warn