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

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,

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

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) > >

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 ...

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

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); >

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

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

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.

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,

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

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

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

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:

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

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

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: 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 >

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

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

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: 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

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

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

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

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

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:

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

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

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

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

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

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

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

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: 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

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

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

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

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

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: 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

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

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

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

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: 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

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: 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: 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

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

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

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

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: 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, _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: 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

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

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

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