Re: why partition pruning doesn't work?

On 8 June 2018 at 15:22, Tom Lane wrote: > David Rowley writes: >> On 8 June 2018 at 03:43, Tom Lane wrote: >>> Maybe there's something I'm missing here, but I sort of hoped that this >>> patch would nuke all the special-case code for Params in this area. >>> Why is there any need to distinguish

Re: hot_standby_feedback vs excludeVacuum and snapshots

On Fri, Jun 8, 2018 at 2:55 AM, Andres Freund wrote: > > On 2018-06-07 14:19:18 -0700, Andres Freund wrote: > > Hi, > > > > On 2018-03-29 12:17:24 +0100, Greg Stark wrote: > > > I'm poking around to see debug a vacuuming problem and wondering if > > > I've found something more serious. > > > > > >

Re: hot_standby_feedback vs excludeVacuum and snapshots

On 7 June 2018 at 22:19, Andres Freund wrote: > Wonder if the right thing here wouldn't be to instead transiently > acquire an AEL lock during replay when truncating a relation? The way AELs are replayed in generic, all AEL requests are handled that way. So yes, you could invent a special case

Re: why partition pruning doesn't work?

On Fri, Jun 8, 2018 at 8:52 AM, Tom Lane wrote: > David Rowley writes: >> On 8 June 2018 at 03:43, Tom Lane wrote: >>> Maybe there's something I'm missing here, but I sort of hoped that this >>> patch would nuke all the special-case code for Params in this area. >>> Why is there any need to dist

Re: Performance regression with PostgreSQL 11 and partitioning

David Rowley writes: > On 8 June 2018 at 08:22, Tom Lane wrote: >> I'm still of the opinion that find_appinfos_by_relids() needs to be >> nuked from orbit. > Yeah, I agree it's not nice that it pallocs an array then pfrees it > again. adjust_appendrel_attrs and adjust_child_relids could probably

Re: assert in nested SQL procedure call in current HEAD

> "Andrew" == Andrew Gierth writes: > "Joe" == Joe Conway writes: Joe> My colleague Yogesh Sharma discovered an assert in nested SQL Joe> procedure calls after ROLLBACK is used. Minimal test case and Joe> backtrace below. I have not yet tried to figure out exactly what Joe> is going

Re: config.{guess,sub} updates

Peter Eisentraut writes: > We didn't update those for beta1, as we usually do. Should we do it for > beta2? Yes, if there are updates to be made. Better late than never. regards, tom lane

Re: Bug in either collation docs or code

"David G. Johnston" writes: > On Thu, Jun 7, 2018 at 4:37 PM, Melanie Plageman > wrote: >> I thought this would error out because the subquery's result is considered >> implicit and, in this case, it seems you now have conflicting implicit >> collations. However, this does not produce an error. W

Re: Add RANGE with values and exclusions clauses to the Window Functions

Peter Eisentraut writes: > On 2/4/18 13:10, Tom Lane wrote: >> + 22013EERRCODE_INVALID_PRECEDING_FOLLOWING_SIZE >> invalid_preceding_following_size > I was checking the new error codes in PostgreSQL 11 and came across > this. The original name in the SQL standard i

Re: why partition pruning doesn't work?

David Rowley writes: > On 8 June 2018 at 03:43, Tom Lane wrote: >> Maybe there's something I'm missing here, but I sort of hoped that this >> patch would nuke all the special-case code for Params in this area. >> Why is there any need to distinguish them from other stable expressions? > We need

Re: Loaded footgun open_datasync on Windows

On Fri, Jun 8, 2018 at 7:48 AM, Laurenz Albe wrote: > Amit Kapila wrote: > > On Wed, Jun 6, 2018 at 3:06 PM, Kuntal Ghosh > wrote: > > > It seems the "#ifndef FRONTEND" restriction was added around > > > pgwin32_open() for building libpq with Visual C++ or Borland C++. The > > > restriction was

Re: Add RANGE with values and exclusions clauses to the Window Functions

On 2/4/18 13:10, Tom Lane wrote: > diff --git a/src/backend/utils/errcodes.txt b/src/backend/utils/errcodes.txt > index 1475bfe..9871d1e 100644 > *** a/src/backend/utils/errcodes.txt > --- b/src/backend/utils/errcodes.txt > *** Section: Class 22 - Data Exception > *** 177,182 > ---

Re: Loaded footgun open_datasync on Windows

Amit Kapila wrote: > On Wed, Jun 6, 2018 at 3:06 PM, Kuntal Ghosh > wrote: > > It seems the "#ifndef FRONTEND" restriction was added around > > pgwin32_open() for building libpq with Visual C++ or Borland C++. The > > restriction was added in commit 422d4819 to build libpq with VC++[1]. > > Late

config.{guess,sub} updates

We didn't update those for beta1, as we usually do. Should we do it for beta2? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: Column store in Greenplum

On Fri, Jun 8, 2018 at 7:34 AM, Asim R P wrote: > Hi, > > In the pluggable storage unconference session in Ottawa, > column oriented storage was a key point of discussion. We would like > to share an overview of Greenplum's column store. We hope that this > will aid a discussion to carve out a

Re: Performance regression with PostgreSQL 11 and partitioning

On 8 June 2018 at 08:22, Tom Lane wrote: > So that's basically what David's patch does, and it seems fine as far > as it goes, although I disapprove of shoving the responsibility into > setup_simple_rel_arrays() without so much as a comment change. > I'd make a separate function for that, I think.

Re: Bug in either collation docs or code

On Thu, Jun 7, 2018 at 4:37 PM, Melanie Plageman wrote: > CREATE TABLE test1 ( > a text COLLATE "de_DE", > b text COLLATE "es_ES", > ... > ); > > My thought was to add the following example: > > SELECT a < (select 'foo' COLLATE "fr_FR") FROM test1; > > I thought this would error out b

Re: Bug in either collation docs or code

You could mark the subquery's result with a collation like this: > > postgres=# SELECT 'c' COLLATE "de_DE" > (SELECT 'ç') COLLATE "es_ES"; > ERROR: collation mismatch between explicit collations "de_DE" and "es_ES" > > I'm not sure if this behavior is considered a bug, but I also can't imagine >>

Re: Spilling hashed SetOps and aggregates to disk

As Serge mentioned, we’ve implemented spill-to-disk for SetOps and Aggregates at Salesforce. We were hitting OOMs often enough that this became a high priority for us. However, our current spill implementation is based on dynahash from 9.6, and we’re not happy with its performance (it was primar

Re: [PATCH v16] GSSAPI encryption support

On Fri, Jun 08, 2018 at 10:11:52AM +1200, Thomas Munro wrote: > On Fri, Jun 8, 2018 at 9:00 AM, Nico Williams wrote: > > Cool! Is there any reason that your patch for Travis and AppVeyor > > integration is not just committed to master? > > I think that's a good idea and I know that some others a

Re: why partition pruning doesn't work?

On 8 June 2018 at 03:43, Tom Lane wrote: > Maybe there's something I'm missing here, but I sort of hoped that this > patch would nuke all the special-case code for Params in this area. > Why is there any need to distinguish them from other stable expressions? > > IOW, I was hoping for the code to

Re: [PATCH v16] GSSAPI encryption support

On Fri, Jun 8, 2018 at 9:00 AM, Nico Williams wrote: > On Tue, Jun 05, 2018 at 12:16:31PM +1200, Thomas Munro wrote: >> On Sat, May 26, 2018 at 6:58 AM, Robbie Harwood wrote: >> > Me and the bot are having an argument. This should green Linux but I >> > dunno about Windows. >> >> BTW if you're l

Column store in Greenplum

Hi, In the pluggable storage unconference session in Ottawa, column oriented storage was a key point of discussion. We would like to share an overview of Greenplum's column store. We hope that this will aid a discussion to carve out a pluggable storage interface. This is just an overview, source

Re: hot_standby_feedback vs excludeVacuum and snapshots

On 2018-06-07 14:19:18 -0700, Andres Freund wrote: > Hi, > > On 2018-03-29 12:17:24 +0100, Greg Stark wrote: > > I'm poking around to see debug a vacuuming problem and wondering if > > I've found something more serious. > > > > As far as I can tell the snapshots on HOT standby are built using a >

Re: SHOW ALL does not honor pg_read_all_settings membership

On 2018-May-31, Michael Paquier wrote: > On Thu, May 31, 2018 at 07:05:58PM +0100, Simon Riggs wrote: > > Any objections to backpatch to v10? > > A backpatch is acceptable in my opinion. Agreed on backpatching. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Developmen

Re: hot_standby_feedback vs excludeVacuum and snapshots

Hi, On 2018-03-29 12:17:24 +0100, Greg Stark wrote: > I'm poking around to see debug a vacuuming problem and wondering if > I've found something more serious. > > As far as I can tell the snapshots on HOT standby are built using a > list of running xids that the primary builds and puts in the WAL

Re: [PATCH v16] GSSAPI encryption support

On Tue, Jun 05, 2018 at 12:16:31PM +1200, Thomas Munro wrote: > On Sat, May 26, 2018 at 6:58 AM, Robbie Harwood wrote: > > Me and the bot are having an argument. This should green Linux but I > > dunno about Windows. > > BTW if you're looking for a way to try stuff out on Windows exactly > the w

Code of Conduct committee: call for volunteers

The proposed Postgres Code of Conduct [1] calls for an investigation and enforcement committee, which is to be appointed by and ultimately answerable to the core team, though no core members may sit on it. The core team is pleased to announce that Stacey Haysler has agreed to serve as the first Ch

Re: Performance regression with PostgreSQL 11 and partitioning

Ashutosh Bapat writes: > On Wed, Jun 6, 2018 at 11:27 AM, David Rowley > wrote: >> I was trying to be realistic for something we can do to fix v11. It's >> probably better to minimise the risky surgery on this code while in >> beta. What I proposed was intended to fix a performance regression new

Re: computing completion tag is expensive for pgbench -S -M prepared

On 2018-06-07 20:34:39 +0100, Simon Riggs wrote: > On 7 June 2018 at 20:27, Tom Lane wrote: > > Simon Riggs writes: > >> If we're going to compress the protocol, it seems sensible to remove > >> extraneous information first. > > > > Breaking the wire protocol was nowhere in this thread. > > No,

cursors with prepared statements

I have developed a patch that allows declaring cursors over prepared statements: DECLARE cursor_name CURSOR FOR prepared_statement_name [ USING param, param, ... ] This is an SQL standard feature. ECPG already supports it (with different internals). Intern

Re: computing completion tag is expensive for pgbench -S -M prepared

On 7 June 2018 at 20:27, Tom Lane wrote: > Simon Riggs writes: >> If we're going to compress the protocol, it seems sensible to remove >> extraneous information first. > > Breaking the wire protocol was nowhere in this thread. No, it wasn't. But there is another thread on the subject of compress

Re: computing completion tag is expensive for pgbench -S -M prepared

Simon Riggs writes: > If we're going to compress the protocol, it seems sensible to remove > extraneous information first. Breaking the wire protocol was nowhere in this thread. regards, tom lane

Re: computing completion tag is expensive for pgbench -S -M prepared

On 7 June 2018 at 19:20, Andres Freund wrote: > On 2018-06-07 11:40:48 +0100, Simon Riggs wrote: >> On 7 June 2018 at 11:29, Pavel Stehule wrote: >> >> >> Do we actually need the completion tag at all? In most cases?? >> > >> > >> > affected rows is taken from this value on protocol level >> >> I

Re: computing completion tag is expensive for pgbench -S -M prepared

On 2018-06-07 17:01:47 +1200, David Rowley wrote: > On 7 June 2018 at 16:13, Andres Freund wrote: > > in PortalRun(). That's actually fairly trivial to optimize - we don't > > need the full blown snprintf machinery here. A quick benchmark > > replacing it with: > > > >mem

Re: computing completion tag is expensive for pgbench -S -M prepared

On 2018-06-07 10:30:14 -0400, Tom Lane wrote: > Andres Freund writes: > > ... That's actually fairly trivial to optimize - we don't > > need the full blown snprintf machinery here. A quick benchmark > > replacing it with: > > >memcpy(completionTag, "SELECT ", sizeof("SEL

Re: computing completion tag is expensive for pgbench -S -M prepared

On 2018-06-07 11:40:48 +0100, Simon Riggs wrote: > On 7 June 2018 at 11:29, Pavel Stehule wrote: > > >> Do we actually need the completion tag at all? In most cases?? > > > > > > affected rows is taken from this value on protocol level > > I didn't mean we should remove the number of rows. Many

Re: Transform for pl/perl

ilm...@ilmari.org (Dagfinn Ilmari =?utf-8?Q?Manns=C3=A5ker?=) writes: > Peter Eisentraut writes: >> The way I understand it, it's only how things are passed around >> internally. Nothing is noticeable externally, and so there is no >> backward compatibility issue. >> >> At least that's how I und

Re: POC: GROUP BY optimization

Yes. But again, this description is a bit short. First it works after first patch and might get some preordered leading pathkeys. Second, it tries to match ORDER BY clause order if there is no preordered leading pathkeys from first patch (it was introduced in v7). And third, if there is a tail of

Re: why partition pruning doesn't work?

David Rowley writes: > On 6 June 2018 at 18:05, Amit Langote wrote: >> I wonder why we need to create those Bitmapsets in the planner? Why not >> in ExecSetupPartitionPruneState()? For example, like how >> context->exprstates is initialized. > That seems like a good idea. Certainly much bette

Re: POC: GROUP BY optimization

Again agree. If we have fixed order of columns (ORDER BY) then we should not try to reorder it. Current patch follows that if I didn't a mistake. This part seems to be more a misunderstanding between me and Claudio. I believe Claudio was referring to the column order in a GROUP BY, not ORDER

Re: POC: GROUP BY optimization

On 06/07/2018 03:41 PM, Teodor Sigaev wrote: >> ... snip ... >> Priorization of the user-provided order can be as simple as giving that comparison_cost a small handicap. I see no point in doing that, and I don't recall a single place in the planner where we do that. If the user specified ORDE

Re: processSQLNamePattern() analog

Thanks for the answer. On Ср, 2018-06-06 at 13:06 -0400, Tom Lane wrote: > Sergey Cherkashin writes: > > > > The command "\dA" (as well as several commands that I write) accept > > the access method name template. The resulting template is > > processed by the processSQLNamePattern () function,

Re: computing completion tag is expensive for pgbench -S -M prepared

Andres Freund writes: > ... That's actually fairly trivial to optimize - we don't > need the full blown snprintf machinery here. A quick benchmark > replacing it with: >memcpy(completionTag, "SELECT ", sizeof("SELECT ")); >pg_lltoa(nprocessed, com

Re: Transform for pl/perl

Peter Eisentraut writes: > On 6/6/18 12:14, Alvaro Herrera wrote: >> On 2018-May-17, Peter Eisentraut wrote: >> >>> The items that are still open from the original email are: >>> >>> 2) jsonb scalar values are passed to the plperl function wrapped in not >>>one, but _two_ layers of reference

Re: POC: GROUP BY optimization

I don't see why not to generate all possible orderings (keeping only the cheapest path for each pathkey variant) and let the optimizer to sort it out. I'm assuming costing the full N! possible orderings would be prohibitively expensive. That's true, but for the first step we need to improve co

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

On Tue, Jun 5, 2018 at 3:40 PM, Kyotaro HORIGUCHI wrote: > Hello. > > At Mon, 04 Jun 2018 20:58:28 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI > wrote in > <20180604.205828.208262556.horiguchi.kyot...@lab.ntt.co.jp> >> It fails on some join-pushdown cases since it doesn't add tid >> columns

Re: POC: GROUP BY optimization

So the costing was fairly trivial, we simply do something like comparison_cost = 2.0 * cpu_operator_cost; sort_cost = comparison_cost * tuples * LOG2(tuples); which essentially ignores that there might be multiple columns, or that the columns may have sort operator with different cost

Re: Remove mention in docs that foreign keys on partitioned tables are not supported

On Thu, Jun 7, 2018 at 10:58 AM, Amit Langote wrote: > On 2018/06/07 14:17, Ashutosh Bapat wrote: >>> that is, users can find out about that feature by themselves by >>> trying it out? >> >> I didn't understand that part. >> >> Probably we just say that BEFORE ROW triggers are not supported on a >

Re: POC: GROUP BY optimization

OK, I haven't looked at v7 yet, but if I understand correctly it tries to maintain the ordering as much as possible? Does that actually help? I mean, the incremental sort patch allows the sorting to happen by pieces, but here we still need to sort all the data, right? Can you give an example

Re: commitfest 2018-07

On Thu, Jun 7, 2018 at 8:38 AM, Jonathan S. Katz wrote: > >> On Jun 6, 2018, at 8:14 PM, Michael Paquier wrote: >> >> On Wed, Jun 06, 2018 at 12:40:40PM -0400, Andrew Dunstan wrote: >>> I'll volunteer for CFM, which seems appropriate since I was one of the >>> supporters of having an extra CF. >>

Re: Typo in planner README

On Thu, Jun 7, 2018 at 12:28 PM, Daniel Gustafsson wrote: > The README didn’t get the memo when set_base_rel_pathlist was renamed in > 6543d81d659f417. Use the right name, set_base_rel_pathlists, as per the > attached. > Applied, thanks. -- Magnus Hagander Me: https://www.hagander.net/

Re: PANIC during crash recovery of a recently promoted standby

Hello. At Thu, 24 May 2018 16:57:07 +0900, Michael Paquier wrote in <20180524075707.ge15...@paquier.xyz> > On Mon, May 14, 2018 at 01:14:22PM +0530, Pavan Deolasee wrote: > > Looks like I didn't understand Alvaro's comment when he mentioned it to me > > off-list. But I now see what Michael and A

Re: Expression errors with "FOR UPDATE" and postgres_fdw with partition wise join enabled.

On Wed, Jun 6, 2018 at 5:00 PM, Etsuro Fujita wrote: > (2018/05/18 16:33), Etsuro Fujita wrote: >> >> Other than pull_var_clause things, >> the updated version looks good to me, so I'll mark this as Ready for >> Committer. > > > Since I'm not 100% sure that that is the right way to go, I've been >

Re: computing completion tag is expensive for pgbench -S -M prepared

On 7 June 2018 at 11:29, Pavel Stehule wrote: >> Do we actually need the completion tag at all? In most cases?? > > > affected rows is taken from this value on protocol level I didn't mean we should remove the number of rows. Many things rely on that. -- Simon Riggshttp://www.2

Re: computing completion tag is expensive for pgbench -S -M prepared

2018-06-07 12:01 GMT+02:00 Simon Riggs : > On 7 June 2018 at 06:01, David Rowley > wrote: > > On 7 June 2018 at 16:13, Andres Freund wrote: > >> in PortalRun(). That's actually fairly trivial to optimize - we don't > >> need the full blown snprintf machinery here. A quick benchmark > >> replac

Typo in planner README

The README didn’t get the memo when set_base_rel_pathlist was renamed in 6543d81d659f417. Use the right name, set_base_rel_pathlists, as per the attached. cheers ./daniel typo-planner_README.patch Description: Binary data

Re: computing completion tag is expensive for pgbench -S -M prepared

On 7 June 2018 at 06:01, David Rowley wrote: > On 7 June 2018 at 16:13, Andres Freund wrote: >> in PortalRun(). That's actually fairly trivial to optimize - we don't >> need the full blown snprintf machinery here. A quick benchmark >> replacing it with: >> >>memcpy(compl

Re: Needless additional partition check in INSERT?

On 2018/06/07 15:02, David Rowley wrote: > On 7 June 2018 at 17:45, Amit Langote wrote: >> On 2018/06/07 13:10, David Rowley wrote: >>> On 7 June 2018 at 16:05, Amit Langote wrote: Or we could just not have a separate function and put the logic that determines whether or not to check th

Re: Concurrency bug in UPDATE of partition-key

On 7 June 2018 at 11:44, Amit Kapila wrote: > On Tue, Jun 5, 2018 at 8:03 PM, Amit Khandekar > wrote: >> >> Attached is a rebased patch version. Also included it in the upcoming >> commitfest : >> https://commitfest.postgresql.org/18/1660/ >> > > Doesn't this belong to PostgreSQL 11 Open Items [1

Re: Spilling hashed SetOps and aggregates to disk

On 06/07/2018 02:18 AM, Andres Freund wrote: On 2018-06-06 17:17:52 -0700, Andres Freund wrote: On 2018-06-07 12:11:37 +1200, David Rowley wrote: On 7 June 2018 at 08:11, Tomas Vondra wrote: On 06/06/2018 04:11 PM, Andres Freund wrote: Consider e.g. a scheme where we'd switch from hashed agg

Re: Possible bug in logical replication.

On 6 June 2018 at 17:22, Alvaro Herrera wrote: > This thread seems to have died down without any fix being proposed. > Simon, you own this open item. Thanks, will look. -- Simon Riggshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Service

Re: Fix obsolete comment

On 2018/06/07 16:03, Heikki Linnakangas wrote: > On 07/06/18 09:43, Amit Langote wrote: >> A comment in ExecUpdate refers to an argument of ExecConstraints that no >> longer exists.  Attached fixes that, but I'm about over a year too late >> [1] in sending this patch. > > Applied, thanks! Thanks

Re: Fix obsolete comment

On 07/06/18 09:43, Amit Langote wrote: A comment in ExecUpdate refers to an argument of ExecConstraints that no longer exists. Attached fixes that, but I'm about over a year too late [1] in sending this patch. Applied, thanks! - Heikki