Documentation and code don't agree about partitioned table UPDATEs

The docs in PG11 and master both state: When an UPDATE causes a row to move from one partition to another, there is a chance that another concurrent UPDATE or DELETE misses this row. Suppose session 1 is performing an UPDATE on a partition key, and meanwhile a concurrent session 2 for which this r

Re: Performance issue in foreign-key-aware join estimation

On Sat, 22 Dec 2018 at 09:28, David Rowley wrote: > > On Fri, 21 Dec 2018 at 06:44, Tom Lane wrote: > > I was distressed to discover via perf that 69% of the runtime of this > > test now goes into match_eclasses_to_foreign_key_col(). That seems > > clearly unacceptable. > > Agreed. That's pretty

Re: Performance issue in foreign-key-aware join estimation

On Tue, 5 Feb 2019 at 22:43, David Rowley wrote: > So that this does not get lost, I've added an entry for the original > patch for the March commitfest. Attaching the original patch again so the commitfest bot gets off my back about the other one not applying. -- David Rowley

Re: WIP: Avoid creation of the free space map for small tables

On Tue, Feb 5, 2019 at 4:04 AM Amit Kapila wrote: > > On Mon, Feb 4, 2019 at 2:27 PM John Naylor > wrote: > > > > 1. Earlier, I had a test to ensure that free space towards the front > > of the relation was visible with no FSM. In [1], I rewrote it without > > using vacuum, so we can consider ad

Re: Tighten up a few overly lax regexes in pg_dump's tap tests

> On 5 Feb 2019, at 06:55, David G. Johnston wrote: > > On Monday, February 4, 2019, David Rowley > wrote: > On Tue, 5 Feb 2019 at 01:12, Daniel Gustafsson > wrote: > > We may also want to use the + metacharacter instead of * in a few

Re: Online verification of checksums

On 2/5/19 8:01 AM, Andres Freund wrote: > Hi, > > On 2019-02-05 06:57:06 +0100, Fabien COELHO wrote: > I'm wondering (possibly again) about the existing early exit if one block > cannot be read on retry: the command should count this as a kind of bad > block, proceed on checking oth

Re: What happens if checkpoint haven't completed until the next checkpoint interval or max_wal_size?

On Mon, Feb 4, 2019 at 2:50 PM Laurenz Albe wrote: > Mohammad Sherafat wrote: > > In the name of god! > > It is not considered good style to hurt people's religious feelings > by using the name of god in vain. I agree but... > What happens if checkpoint haven't completed until the next checkpo

Re: Online verification of checksums

Hi, Am Dienstag, den 05.02.2019, 11:30 +0100 schrieb Tomas Vondra: > On 2/5/19 8:01 AM, Andres Freund wrote: > > On 2019-02-05 06:57:06 +0100, Fabien COELHO wrote: > > > > > > I'm wondering (possibly again) about the existing early exit if one > > > > > > block > > > > > > cannot be read on retry

Re: Feature: temporary materialized views

Hi Andreas, On Tue, Feb 05, 2019 at 12:59:12PM +0900, Michael Paquier wrote: > Now... You have on this thread all the audience which already worked > on 874fe3a. And I am just looking at this patch, evaluating the > behavior change this is introducing. Still I would recommend a > separate threa

Re: What happens if checkpoint haven't completed until the next checkpoint interval or max_wal_size?

On Tue, Feb 05, 2019 at 04:11:55PM +0530, Robert Haas wrote: > ...not about this part. I think the next checkpoint just doesn't start > until the one already in progress completes. Yes, the requests are communicated from any backends to the checkpointer with shared memory (see ckpt_flags in Reques

Internal error while setting reloption on system catalogs.

Hello. The following command complains with an internal error. (allow_system_table_mods is on). alter table pg_attribute set (fillfactor = 90); > ERROR: AccessExclusiveLock required to add toast table. The same happens for pg_class. This is because ATRewriteCatalogs tries to add a toast table t

Re: What happens if checkpoint haven't completed until the next checkpoint interval or max_wal_size?

At Tue, 5 Feb 2019 20:42:59 +0900, Michael Paquier wrote in <20190205114259.gh1...@paquier.xyz> > On Tue, Feb 05, 2019 at 04:11:55PM +0530, Robert Haas wrote: > > ...not about this part. I think the next checkpoint just doesn't start > > until the one already in progress completes. > > Yes, the

Re: Ltree syntax improvement

В письме от вторник, 29 января 2019 г. 20:43:07 MSK пользователь Dmitry Belyavsky написал: > Please find attached the patch extending the sets of symbols allowed in > ltree labels. The patch introduces 2 variants of escaping symbols, via > backslashing separate symbols and via quoting the labels

Re: Memory contexts reset for trigger invocations

Andres Freund writes: > Wouldn't it be better to reset an appropriate context after each > invocation? Yes, that'd require some care to manage the lifetime of > tuples returned by triggers, but that seems OK? I'm not sure that we can change much here without effectively breaking the trigger-funct

Re: Memory contexts reset for trigger invocations

Hi, On February 5, 2019 7:55:28 PM GMT+05:30, Tom Lane wrote: >Andres Freund writes: >> Wouldn't it be better to reset an appropriate context after each >> invocation? Yes, that'd require some care to manage the lifetime of >> tuples returned by triggers, but that seems OK? > >I'm not sure that

Re: Internal error while setting reloption on system catalogs.

Kyotaro HORIGUCHI writes: > The following command complains with an internal > error. (allow_system_table_mods is on). > alter table pg_attribute set (fillfactor = 90); >> ERROR: AccessExclusiveLock required to add toast table. > The same happens for pg_class. Isn't this more or less the same

Re: fast defaults in heap_getattr vs heap_deform_tuple

On 2/1/19 5:49 PM, Andres Freund wrote: > Hi, > > On 2019-02-01 08:24:04 -0800, Andres Freund wrote: > > Andrew, I think it'd be good to do a ground up review of the fast > defaults patch. There's been a fair number of issues in it, and this is > a another pretty fundamental issue. > OK. Will t

Fix optimization of foreign-key on update actions

I came across an edge case in how our foreign key implementation works that I think is not SQL conforming. It has to do with how updates to values that "look" different but compare as equal are cascaded. A simple case involves float -0 vs. 0, but relevant cases also arise with citext and case-ins

Re: Fix optimization of foreign-key on update actions

> "Peter" == Peter Eisentraut writes: Peter> The SQL standard seems clear ha hahaha HAHAHAHAHAHA (since when has the SQL standard ever been clear?) SQL2016, 15.17 Execution of referential actions 10) If a non-null value of a referenced column RC in the referenced table is updated t

Re: Fix optimization of foreign-key on update actions

Andrew Gierth writes: > "Peter" == Peter Eisentraut writes: > Peter> The SQL standard seems clear > (since when has the SQL standard ever been clear?) Point to Andrew ;-). However, I kind of like Peter's idea anyway on the grounds that byte-wise comparison is probably faster than invoking the

Re: pg11.1: dsa_area could not attach to segment

I finally reproduced this with core.. For some reason I needed to write assert() rather than elog(PANIC), otherwise it failed with ERROR and no core.. @@ -1741,4 +1743,5 @@ get_segment_by_index(dsa_area *area, dsa_segment_index index) segment = dsm_attach(handle); +

Re: Commit Fest 2019-01 is now closed

On Mon, Feb 4, 2019 at 06:34:50AM +, Tsunakawa, Takayuki wrote: > From: Michael Paquier [mailto:mich...@paquier.xyz] > > As per $subject, CF 2019-01 is now closed for business. Here is the final > > score: > > Committed: 58. > > Moved to next CF: 113. > > Withdrawn: 4. > > Rejected: 3. > > Re

Re: Commit Fest 2019-01 is now closed

Bruce Momjian writes: > On Mon, Feb 4, 2019 at 06:34:50AM +, Tsunakawa, Takayuki wrote: >> Wow, thank you so much for your hard work. The last CF for PG 12 should be >> tough... > Agreed. I am somewhat concerned about this and am wondering what we can > do now to limit problems. There's

Re: fast defaults in heap_getattr vs heap_deform_tuple

Hi, On 2019-02-05 10:14:48 -0500, Andrew Dunstan wrote: > > On 2/1/19 5:49 PM, Andres Freund wrote: > > Hi, > > > > On 2019-02-01 08:24:04 -0800, Andres Freund wrote: > > > > Andrew, I think it'd be good to do a ground up review of the fast > > defaults patch. There's been a fair number of issues

Re: Commit Fest 2019-01 is now closed

On Tue, Feb 5, 2019 at 11:55:37AM -0500, Tom Lane wrote: > Bruce Momjian writes: > > On Mon, Feb 4, 2019 at 06:34:50AM +, Tsunakawa, Takayuki wrote: > >> Wow, thank you so much for your hard work. The last CF for PG 12 should > >> be tough... > > > Agreed. I am somewhat concerned about t

Re: Commit Fest 2019-01 is now closed

Bruce Momjian writes: > On Tue, Feb 5, 2019 at 11:55:37AM -0500, Tom Lane wrote: >> There's been talk periodically of having an aggressive triage effort >> to try to sort through the pending patches and decide which ones have >> no hope of making it to commit in the last CF. Then, if we just pus

Re: Commit Fest 2019-01 is now closed

On Tue, Feb 5, 2019 at 12:02:23PM -0500, Tom Lane wrote: > Bruce Momjian writes: > > On Tue, Feb 5, 2019 at 11:55:37AM -0500, Tom Lane wrote: > >> There's been talk periodically of having an aggressive triage effort > >> to try to sort through the pending patches and decide which ones have > >>

Re: Commit Fest 2019-01 is now closed

Hi, On 2019-02-05 11:55:37 -0500, Tom Lane wrote: > Bruce Momjian writes: > > On Mon, Feb 4, 2019 at 06:34:50AM +, Tsunakawa, Takayuki wrote: > >> Wow, thank you so much for your hard work. The last CF for PG 12 should > >> be tough... > > > Agreed. I am somewhat concerned about this and

Re: Commit Fest 2019-01 is now closed

Hi, On 2019-02-05 12:02:23 -0500, Tom Lane wrote: > Bruce Momjian writes: > > On Tue, Feb 5, 2019 at 11:55:37AM -0500, Tom Lane wrote: > >> There's been talk periodically of having an aggressive triage effort > >> to try to sort through the pending patches and decide which ones have > >> no hope

Re: Protect syscache from bloating with negative cache entries

Hi, I find it a bit surprising there are almost no results demonstrating the impact of the proposed changes on some typical workloads. It touches code (syscache, ...) that is quite sensitive performance-wise, and adding even just a little bit of overhead may hurt significantly. Even on systems tha

Re: Feature: temporary materialized views

On 2/5/19 12:36 PM, Michael Paquier wrote:> - skipData is visibly always false. > We may want to keep skipData to have an assertion at the beginning of > inforel_startup for sanity purposes though. This is not true in this version of the patch. The following two cases would crash if we add such

Re: Protect syscache from bloating with negative cache entries

On 1/21/19 9:56 PM, Bruce Momjian wrote: > On Fri, Jan 18, 2019 at 05:09:41PM -0800, Andres Freund wrote: >> Hi, >> >> On 2019-01-18 19:57:03 -0500, Robert Haas wrote: >>> On Fri, Jan 18, 2019 at 4:23 PM and...@anarazel.de >>> wrote: My proposal for this was to attach a 'generation' to cache

Re: pg11.1: dsa_area could not attach to segment

And here's the "dsa_allocate could not find %zu free pages" error with core. @@ -726,5 +728,5 @@ dsa_allocate_extended(dsa_area *area, size_t size, int flags) */ - if (!FreePageManagerGet(segment_map->fpm, npages, &first_page)) - elog(FATAL, -

Re: Commit Fest 2019-01 is now closed

On Tue, Feb 5, 2019 at 9:08 AM Andres Freund wrote: > What we'd talked about briefly at the Fosdem dev meeting was that a > field 'target release' or 'target branch' would be very useful to be > able to focus attention more. There's plenty stuff in the current CF > that is getting some attention,

Re: Changing SQL Inlining Behaviour (or...?)

> On Feb 3, 2019, at 3:47 PM, Tom Lane wrote: > > I wrote: >> I've posted some preliminary design ideas at >> https://www.postgresql.org/message-id/15193.1548028...@sss.pgh.pa.us >> and >> https://www.postgresql.org/message-id/15289.1548028...@sss.pgh.pa.us >> While there's a nontrivial amount

Re: patch to allow disable of WAL recycling

On Mon, Oct 1, 2018 at 7:16 PM Michael Paquier wrote: > On Thu, Sep 13, 2018 at 02:56:42PM -0600, Jerry Jelinek wrote: > > I'll take a look at that. I had been trying to keep the patch as minimal > as > > possible, but I'm happy to work through this. > > (Please be careful with top-posting) > > J

Re: Changing SQL Inlining Behaviour (or...?)

Paul Ramsey writes: > Hokay… I’ve read through the patch set, applied it and built it, all works. > Am starting to try a test implementation in PostGIS land. Robert’s comment > about “PostgreSQL magic” is ringing through my head ... Nodes and Ops and > Exprs, oh my! What ever doesn’t kill me on

Re: Changing SQL Inlining Behaviour (or...?)

> On Feb 5, 2019, at 11:07 AM, Tom Lane wrote: > > Paul Ramsey writes: >> Hokay… I’ve read through the patch set, applied it and built it, all works. >> Am starting to try a test implementation in PostGIS land. Robert’s comment >> about “PostgreSQL magic” is ringing through my head ... Node

Re: Changing SQL Inlining Behaviour (or...?)

Paul Ramsey writes: > So just a meta-comment, this is all very cool and I can see how it will > help out things like selectivity estimation and tuple return estimation > for unnest() and generate_series() and even how I could eventually do > some dynamic costing for some functions, but it’s gettin

Re: Fixing findDependentObjects()'s dependency on scan order (regressions in DROP diagnostic messages)

On 2019-Jan-29, Tom Lane wrote: > The closest I could get to solving it along the original lines > went like this: > > * In addition, we support INTERNAL_AUTO dependencies, which alter the > * rules for this. If the target has both INTERNAL and INTERNAL_AUTO > * dependencies, then

Re: Too rigorous assert in reorderbuffer.c

On 2019-Jan-31, Arseny Sher wrote: > My colleague Alexander Lakhin has noticed an assertion failure in > reorderbuffer.c:1330. Here is a simple snippet reproducing it: > > SELECT 'init' FROM pg_create_logical_replication_slot('regression_slot', > 'test_decoding'); > > create table t(k int); > b

Re: [HACKERS] Macros bundling RELKIND_* conditions

On 2018-Dec-19, Tom Lane wrote: > Alvaro Herrera writes: > > I support this idea. Here's a proof-of-concept patch that corresponds > > to one of the cases that Ashutosh was on about (specifically, the one > > that uses the RELKIND_CAN_HAVE_STORAGE macro I just added). If there > > are no objec

Bogus lateral-reference-propagation logic in create_lateral_join_info

While poking at bug #15613 (in which FDWs are failing to mark created Paths with correct outer-reference sets), I thought it'd be a good idea to add some asserts to Path creation saying that every Path should be parameterized by at least whatever the relation's required LATERAL references are. I d

Re: Protect syscache from bloating with negative cache entries

On 2019-Feb-05, Tomas Vondra wrote: > I don't think we need to remove the expired entries right away, if there > are only very few of them. The cleanup requires walking the hash table, > which means significant fixed cost. So if there are only few expired > entries (say, less than 25% of the cache

Re: Fixing findDependentObjects()'s dependency on scan order (regressions in DROP diagnostic messages)

Alvaro Herrera writes: > On 2019-Jan-29, Tom Lane wrote: >> It strikes me however that we can stick with the existing catalog contents >> by making this definition: of the INTERNAL_AUTO dependencies of a given >> object, the "true owner" to be reported in errors is the dependency >> that is of the

Re: Protect syscache from bloating with negative cache entries

On 2/5/19 11:05 PM, Alvaro Herrera wrote: > On 2019-Feb-05, Tomas Vondra wrote: > >> I don't think we need to remove the expired entries right away, if there >> are only very few of them. The cleanup requires walking the hash table, >> which means significant fixed cost. So if there are only few e

Re: Fixing findDependentObjects()'s dependency on scan order (regressions in DROP diagnostic messages)

On Tue, Feb 5, 2019 at 2:08 PM Tom Lane wrote: > I've got much of the code for it already (in the wreckage of my failed > attempts), so I'll go back and finish that up. I was just waiting to see > how loudly people would howl about using object type as a condition for > figuring out what a pg_dep

Re: Internal error while setting reloption on system catalogs.

At Tue, 05 Feb 2019 10:01:48 -0500, Tom Lane wrote in <20605.1549378...@sss.pgh.pa.us> > Kyotaro HORIGUCHI writes: > > The following command complains with an internal > > error. (allow_system_table_mods is on). > > > alter table pg_attribute set (fillfactor = 90); > >> ERROR: AccessExclusiveL

Re: Libpq support to connect to standby server as priority

On Mon, Jan 21, 2019 at 5:48 PM Tsunakawa, Takayuki < tsunakawa.ta...@jp.fujitsu.com> wrote: > From: Haribabu Kommi [mailto:kommi.harib...@gmail.com] > > Thanks for finding out the problem, how about the following way of > checking > > for prefer-read/prefer-standby. > > > > 1. (default_transactio

Re: Making all nbtree entries unique by having heap TIDs participate in comparisons

On Mon, Jan 28, 2019 at 1:41 PM Peter Geoghegan wrote: > Thanks for going to the trouble of implementing what you have in mind! > > I agree that the code that I wrote within nbtsplitloc.c is very > subtle, and I do think that I have further work to do to make its > design clearer. I think that thi

Re: Bogus lateral-reference-propagation logic in create_lateral_join_info

On Wed, 6 Feb 2019 at 10:57, Tom Lane wrote: > Also, I'd really like to know why I had to put in the exception seen > in the loop for AppendRelInfos that do not point to a valid parent. > It seems to me that that is almost certainly working around a bug in > the partitioning logic. (Without it, t

RE: speeding up planning with partitions

Imai-san, From: Amit Langote [mailto:langote_amit...@lab.ntt.co.jp] > On 2019/01/22 18:47, David Rowley wrote: > > On Tue, 22 Jan 2019 at 20:01, Imai, Yoshikazu > >> What I understand so far is about 10,000 while loops at total > (4098+4098+some extra) is needed in hash_seq_search() in EXECUTE que

Re: pg11.1: dsa_area could not attach to segment

I should have included query plan for the query which caused the "could not find free pages" error. This is a contrived query which I made up to try to exercise/stress bitmap scans based on Thomas's working hypothesis for this error/bug. This seems to be easier to hit than the other error ("could

Re: Feature: temporary materialized views

On 2/5/19 6:56 PM, Andreas Karlsson wrote: On 2/5/19 12:36 PM, Michael Paquier wrote:> - skipData is visibly always false. > We may want to keep skipData to have an assertion at the beginning of > inforel_startup for sanity purposes though. This is not true in this version of the patch. The fo

Add missing CREATE TABLE IF NOT EXISTS table_name AS EXECUTE query;

Hi, The first example below works while the second one is a syntax error despite that the documentation (https://www.postgresql.org/docs/11/sql-createtableas.html) makes it seem like both should be valid. I do not see any reason to not support CTAS with both IF NOT EXISTS and EXECUTE at the s

Re: Bogus lateral-reference-propagation logic in create_lateral_join_info

David Rowley writes: > On Wed, 6 Feb 2019 at 10:57, Tom Lane wrote: >> Also, I'd really like to know why I had to put in the exception seen >> in the loop for AppendRelInfos that do not point to a valid parent. >> It seems to me that that is almost certainly working around a bug in >> the partiti

Re: Documentation and code don't agree about partitioned table UPDATEs

On Tue, Feb 5, 2019 at 2:14 PM David Rowley wrote: > > The docs in PG11 and master both state: > > When an UPDATE causes a row to move from one partition to another, > there is a chance that another concurrent UPDATE or DELETE misses this > row. Suppose session 1 is performing an UPDATE on a parti

Re: Fixing findDependentObjects()'s dependency on scan order (regressions in DROP diagnostic messages)

Peter Geoghegan writes: > On Tue, Feb 5, 2019 at 2:08 PM Tom Lane wrote: >> I've got much of the code for it already (in the wreckage of my failed >> attempts), so I'll go back and finish that up. I was just waiting to see >> how loudly people would howl about using object type as a condition fo

Re: Non-deterministic IndexTuple toast compression from index_form_tuple() + amcheck false positives

On Fri, Feb 1, 2019 at 6:27 PM Peter Geoghegan wrote: > Attached draft patch fixes the bug by doing fairly simple > normalization. I think that TOAST compression of datums in indexes is > fairly rare in practice, so I'm not very worried about the fact that > this won't perform as well as it could

Re: PostgreSQL vs SQL/XML Standards

On 02/01/19 20:20, Michael Paquier wrote: > On Thu, Jan 31, 2019 at 04:26:31PM +0100, Pavel Stehule wrote: >> I'll mark this patch as ready for commiters. > > For now I have moved the patch to the next CF, with the same status. I wonder whether, given the move to next CF, it makes sense to change

Re: add_partial_path() may remove dominated path but still in use

Hello, Let me remind the thread again. I'm waiting for the fix getting committed for a month... 2019年1月22日(火) 20:50 Kohei KaiGai : > > Let me remind the thread. > If no more comments, objections, or better ideas, please commit this fix. > > Thanks, > > 2019年1月17日(木) 18:29 Kyotaro HORIGUCHI : > >

Re: pg11.1: dsa_area could not attach to segment

On Wed, Feb 6, 2019 at 1:10 PM Justin Pryzby wrote: > This is a contrived query which I made up to try to exercise/stress bitmap > scans based on Thomas's working hypothesis for this error/bug. This seems to > be easier to hit than the other error ("could not attach to segment") - a loop > around

RE: Cache relation sizes?

>From: Jamison, Kirk [mailto:k.jami...@jp.fujitsu.com] >On the other hand, the simplest method I thought that could also work is to >only cache >the file size (nblock) in shared memory, not in the backend process, since >both nblock >and relsize_change_counter are uint32 data type anyway. If >r

Re: Protect syscache from bloating with negative cache entries

At Tue, 5 Feb 2019 02:40:35 +, "Tsunakawa, Takayuki" wrote in <0A3221C70F24FB45833433255569204D1FB93A16@G01JPEXMBYT05> > From: br...@momjian.us [mailto:br...@momjian.us] > > On Mon, Feb 4, 2019 at 08:23:39AM +, Tsunakawa, Takayuki wrote: > > > Horiguchi-san, Bruce, all, So, why don't we

Re: Protect syscache from bloating with negative cache entries

At Wed, 06 Feb 2019 14:43:34 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20190206.144334.193118280.horiguchi.kyot...@lab.ntt.co.jp> > At Tue, 5 Feb 2019 02:40:35 +, "Tsunakawa, Takayuki" > wrote in > <0A3221C70F24FB45833433255569204D1FB93A16@G01JPEXMBYT05> > > From: br...@mom

RE: Cache relation sizes?

From: Jamison, Kirk [mailto:k.jami...@jp.fujitsu.com] > On the other hand, the simplest method I thought that could also work is > to only cache the file size (nblock) in shared memory, not in the backend > process, since both nblock and relsize_change_counter are uint32 data type > anyway. If rels

A separate table level option to control compression

Hello, Currently either the table level option `toast_tuple_target` or the compile time default `TOAST_TUPLE_TARGET` is used to decide whether a new tuple should be compressed or not. While this works reasonably well for most situations, at times the user may not want to pay the overhead of toasti