Re: Spilling hashed SetOps and aggregates to disk

On Tue, 2018-06-05 at 07:04 +0200, Tomas Vondra wrote: > I expect the eviction strategy to be the primary design challenge of > this patch. The other bits will be mostly determined by this one > piece. Not sure I agree that this is the primary challenge. The cases that benefit from eviction are

Re: libpq compression

On Thu, May 17, 2018 at 3:54 AM, Konstantin Knizhnik wrote: > Thank you for this notice. > Updated and rebased patch is attached. Hi Konstantin, Seems very useful. +1. + rc = inflate(>rx, Z_SYNC_FLUSH); + if (rc != Z_OK) + { + return ZPQ_DECOMPRESS_ERROR; + } Does this actually guarantee

Re: Spilling hashed SetOps and aggregates to disk

On Mon, 2018-06-04 at 11:52 -0700, Andres Freund wrote: > I wonder whether, at least for aggregates, the better fix wouldn't be > to > switch to feeding the tuples into tuplesort upon memory exhaustion > and > doing a sort based aggregate.  We have most of the infrastructure to > do That's an

Re: Spilling hashed SetOps and aggregates to disk

On 06/05/2018 04:56 AM, David Rowley wrote: > On 5 June 2018 at 06:52, Andres Freund wrote: >> That part has gotten a bit easier since, because we have serialize / >> deserialize operations for aggregates these days. > > True. Although not all built in aggregates have those defined. Not sure

Re: pg_replication_slot_advance to return NULL instead of 0/0 if slot not advanced

On Mon, Jun 04, 2018 at 11:51:35AM +0200, Petr Jelinek wrote: > On 01/06/18 21:13, Michael Paquier wrote: >> -startlsn = MyReplicationSlot->data.confirmed_flush; >> +if (OidIsValid(MyReplicationSlot->data.database)) >> +startlsn = MyReplicationSlot->data.confirmed_flush; >> +

Bug in either collation docs or code

Hi, I noticed what seems like a bug in collation. This query errors out: SELECT (SELECT 'c' COLLATE "de_DE") > (SELECT 'ç' COLLATE "es_ES"); -- error While this query does not: SELECT 'c' COLLATE "de_DE" > (SELECT 'ç' COLLATE "es_ES"); -- no error It seems like this is in conflict with what

Re: Performance regression with PostgreSQL 11 and partitioning

On Tue, Jun 5, 2018 at 5:50 AM, David Rowley wrote: > On 5 June 2018 at 01:35, Ashutosh Bapat > wrote: >> I was wondering if we can get rid of append_rel_list altogether. In >> your patch, you have saved AppendRelInfos by child_relid. So all the >> slots indexed by parent relid are empty. We

Re: pg_replication_slot_advance to return NULL instead of 0/0 if slot not advanced

On Mon, Jun 04, 2018 at 11:51:35AM +0200, Petr Jelinek wrote: > On 01/06/18 21:13, Michael Paquier wrote: >> -startlsn =3D MyReplicationSlot->data.confirmed_flush; >> +if (OidIsValid(MyReplicationSlot->data.database)) >> +startlsn =3D MyReplicationSlot->data.confirmed_flush; >>

Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

On Mon, Jun 4, 2018 at 10:47 PM, Konstantin Knizhnik wrote: > > > On 26.04.2018 09:10, Masahiko Sawada wrote: >> >> On Thu, Apr 26, 2018 at 3:30 AM, Robert Haas >> wrote: >>> >>> On Tue, Apr 10, 2018 at 9:08 PM, Masahiko Sawada >>> wrote: Never mind. There was a lot of items

Re: commitfest 2018-07

Michael Paquier writes: > On Mon, Jun 04, 2018 at 07:16:33PM -0400, Peter Eisentraut wrote: >> There were some discussions about renaming the existing 2018-09 entry >> versus inserting a new one at -07 and requiring patches to be moved back >> explicitly. > I would do that to reduce unnecessary

Re: commitfest 2018-07

David Rowley writes: > On 5 June 2018 at 11:16, Peter Eisentraut > wrote: >> Per decision from the developer meeting, there will be a commitfest >> 2018-07 (unless there are concerns from the RMT). > In absence of concerns from the RMT, does this mean v12 will be a 5 'fest > cycle? Yes,

Re: commitfest 2018-07

On 5 June 2018 at 11:16, Peter Eisentraut wrote: > Per decision from the developer meeting, there will be a commitfest > 2018-07 (unless there are concerns from the RMT). In absence of concerns from the RMT, does this mean v12 will be a 5 'fest cycle? -- David Rowley

Re: Spilling hashed SetOps and aggregates to disk

On 5 June 2018 at 06:52, Andres Freund wrote: > That part has gotten a bit easier since, because we have serialize / > deserialize operations for aggregates these days. True. Although not all built in aggregates have those defined. > I wonder whether, at least for aggregates, the better fix

Re: Make deparsing of column defaults faster

On 6/4/18 20:55, Jeff Janes wrote: > The user docs say about column defaults: "The value is any variable-free > expression (subqueries and cross-references to other columns in the > current table are not allowed)" > > And also say about pg_get_expr "If the expression might contain Vars, > specify

Make deparsing of column defaults faster

The user docs say about column defaults: "The value is any variable-free expression (subqueries and cross-references to other columns in the current table are not allowed)" And also say about pg_get_expr "If the expression might contain Vars, specify the OID of the relation they refer to as the

Re: commitfest 2018-07

On Mon, Jun 04, 2018 at 07:16:33PM -0400, Peter Eisentraut wrote: > Per decision from the developer meeting, there will be a commitfest > 2018-07 (unless there are concerns from the RMT). Thanks for raising the thread. > Could we set up the commitfest app appropriately? Sure. I have admin

Re: Performance regression with PostgreSQL 11 and partitioning

On 5 June 2018 at 01:35, Ashutosh Bapat wrote: > I was wondering if we can get rid of append_rel_list altogether. In > your patch, you have saved AppendRelInfos by child_relid. So all the > slots indexed by parent relid are empty. We could place AppendRelInfos > by parent relid. Thus a given

Re: [PATCH v16] GSSAPI encryption support

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 way cfbot does it without posting a new patch to the mailing list, I put

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

On Mon, Jun 4, 2018 at 2:40 PM, Tom Lane wrote: > > I think, in general, that we should try to pick semantics that make a > > partitioned table behave like an unpartitioned table, provided that > > all triggers are defined on the partitioned table itself. > > Well, then we lose the property

commitfest 2018-07

Per decision from the developer meeting, there will be a commitfest 2018-07 (unless there are concerns from the RMT). Could we set up the commitfest app appropriately? There were some discussions about renaming the existing 2018-09 entry versus inserting a new one at -07 and requiring patches to

Re: plans for PostgreSQL 12

Hi, On 2018-06-04 07:35:23 +0100, Simon Riggs wrote: > On 4 June 2018 at 06:08, Pavel Stehule wrote: > > > 4. optimization expression without necessity to create snapshots - > > experiments > > > > @4 There are lot of not database expressions in PLpgSQL - like var1 := var1 > > + var2 or var1 :=

Re: Spilling hashed SetOps and aggregates to disk

Hi, On 2018-06-04 10:32:47 +0200, Heikki Linnakangas wrote: > Hash Aggs and SetOps are currently not spilled to disk. If the planner's > estimate on the number of entries is badly off, you might run out of memory > at execution time, if all the entries don't fit in memory. > > For HashAggs, this

Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

Hi, On 2018-06-04 16:47:29 +0300, Konstantin Knizhnik wrote: > We in PostgresProc were faced with lock extension contention problem at two > more customers and tried to use this patch (v13) to address this issue. > Unfortunately replacing heavy lock with lwlock couldn't completely eliminate >

Re: Code of Conduct plan

> Two years ago, there was considerable discussion about creating a > Code of Conduct for the Postgres community, as a result of which > the core team announced a plan to create an exploration committee > to draft a CoC [1]. That process has taken far longer than expected, > but the committee has

Re: [HACKERS] GnuTLS support

On 6/2/18 16:50, Heikki Linnakangas wrote: > On 08/03/18 14:13, Peter Eisentraut wrote: >> There are two failures in the SSL tests that I cannot explain. The >> tests are for some rather obscure configurations, so the changed >> behaviors are not obviously wrong, perhaps legitimate implementation

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

Robert Haas writes: > On Mon, Jun 4, 2018 at 4:50 PM, Tom Lane wrote: >> Perhaps, but I'm having a hard time wrapping my mind around what the >> semantics ought to be. If a trigger on partition A changes the keys >> so that the row shouldn't have gone into A at all, what then? That >> trigger

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

On 6/4/18 16:50, Tom Lane wrote: > Perhaps, but I'm having a hard time wrapping my mind around what the > semantics ought to be. If a trigger on partition A changes the keys > so that the row shouldn't have gone into A at all, what then? That > trigger should never have fired, eh? The insert

Re: Loaded footgun open_datasync on Windows

On Fri, Jun 1, 2018 at 2:56 PM, Michael Paquier wrote: > When things come to VMs or containers, developers and users tend to be > sloppy regarding the hardware being used, and they are not usually aware > that the database running within it is sensitive to such details. Many > folks use

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

On Mon, Jun 4, 2018 at 1:42 PM, Tom Lane wrote: > Could we solve it by saying that triggers on partitioned tables aren't > allowed to change the partitioning values? (Or at least, not allowed > to change them in a way that changes the target partition.) That seems like a somewhat-unfortunate

Re: New committers announced at PGCon 2018

пн, 4 июн. 2018 г., 21:37 Finnerty, Jim : > Congratulations, everyone! (I wonder if we have any female PG > committers?) > > On 6/4/18, 9:14 AM, "Jesper Pedersen" wrote: > > On 06/01/2018 05:05 PM, Tom Lane wrote: > > The core team is pleased to announce the appointment of seven >

Re: plans for PostgreSQL 12

2018-06-04 21:53 GMT+02:00 Tom Lane : > Pavel Stehule writes: > > 2018-06-04 9:59 GMT+02:00 Vik Fearing : > >> On 04/06/18 09:37, Pavel Stehule wrote: > >>> Yes, it is incorrect mark. Unfortunately - this is often workaround for > >>> wrong estimations - so I afraid, in this case, your proposed

Re: plans for PostgreSQL 12

Pavel Stehule writes: > 2018-06-04 9:59 GMT+02:00 Vik Fearing : >> On 04/06/18 09:37, Pavel Stehule wrote: >>> Yes, it is incorrect mark. Unfortunately - this is often workaround for >>> wrong estimations - so I afraid, in this case, your proposed fix breaks >>> lot of applications. >> I would

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

On 2018-Jun-04, Tom Lane wrote: > Alvaro Herrera writes: > > On 2018-Jun-04, Tom Lane wrote: > >> ... why doesn't the same problem apply to AFTER triggers that are attached > >> to the inheritance parent? > > > With a BEFORE trigger, running the trigger might change the target > > partition,

Re: New committers announced at PGCon 2018

"Finnerty, Jim" writes: > Congratulations, everyone! (I wonder if we have any female PG committers?) We do not :-(. One of the reasons for the code-of-conduct effort is to try to make the community feel more welcoming to women. Eventually that might lead to some female committers, and I hope

Re: New committers announced at PGCon 2018

Congratulations, everyone! (I wonder if we have any female PG committers?) On 6/4/18, 9:14 AM, "Jesper Pedersen" wrote: On 06/01/2018 05:05 PM, Tom Lane wrote: > The core team is pleased to announce the appointment of seven > new Postgres committers: > > Etsuro Fujita

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

Alvaro Herrera writes: > On 2018-Jun-04, Tom Lane wrote: >> ... why doesn't the same problem apply to AFTER triggers that are attached >> to the inheritance parent? > With a BEFORE trigger, running the trigger might change the target > partition, which has the potential for all kinds of trouble.

Re: \d t: ERROR: XX000: cache lookup failed for relation

On Mon, Jun 04, 2018 at 08:01:41PM +0300, Teodor Sigaev wrote: > >Also note that my "INSERT" was run in a separate loop, concurrent with the > >VACUUM and ALTER, but yours is running consecutively. > > both loops run in backgound. I tried to run two scripts - and got a lot of > deadlocks but not

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

On 2018-Jun-04, Tom Lane wrote: > Alvaro Herrera writes: > > This kind of inconsistency is what I wanted to avoid. One of the > > guiding principles here was that a partitioned table behaves just like a > > regular table does; in particular, inserting directly into a partition > > is an

Re: [MASSMAIL]Re: Code of Conduct plan

El 2018-06-04 12:52, Joshua D. Drake escribió: On 06/03/2018 11:29 AM, Tom Lane wrote: https://wiki.postgresql.org/wiki/Code_of_Conduct We are now asking for a final round of community comments. Please send any public comments to the pgsql-general list (only). If you wish to make a private

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

Alvaro Herrera writes: > This kind of inconsistency is what I wanted to avoid. One of the > guiding principles here was that a partitioned table behaves just like a > regular table does; in particular, inserting directly into a partition > is an application-level optimization that must behave

Re: Code of Conduct plan

On 06/03/2018 04:08 PM, Gavin Flower wrote: My comments: 1) Reiterate my contention that this is a solution is search of problem. Still it looks like it is going forward, so see below. 2) "... engaging in behavior that may bring the PostgreSQL project into disrepute, ..." This to me is

Re: Code of Conduct plan

On 06/03/2018 11:29 AM, Tom Lane wrote: https://wiki.postgresql.org/wiki/Code_of_Conduct We are now asking for a final round of community comments. Please send any public comments to the pgsql-general list (only). If you wish to make a private comment, you may send it to c...@postgresql.org.

Re: \d t: ERROR: XX000: cache lookup failed for relation

On Mon, Jun 04, 2018 at 07:12:53PM +0300, Teodor Sigaev wrote: > > >Is that considered an actionable problem? > > > I think so. but I'm not able to reproduce that, I wrote a script to simplify The failure is triggered by running "\d t" in (yet) another session - sorry if that was unclear. It

Re: \d t: ERROR: XX000: cache lookup failed for relation

Is that considered an actionable problem? I think so. but I'm not able to reproduce that, I wrote a script to simplify but it doesn't reproduce too. And how long to wait to reproduce? I waited for one hour -- Teodor Sigaev E-mail: teo...@sigaev.ru

Re: [HACKERS] Moving relation extension locks out of heavyweight lock manager

On 26.04.2018 09:10, Masahiko Sawada wrote: On Thu, Apr 26, 2018 at 3:30 AM, Robert Haas wrote: On Tue, Apr 10, 2018 at 9:08 PM, Masahiko Sawada wrote: Never mind. There was a lot of items especially at the last CommitFest. In terms of moving forward, I'd still like to hear what Andres

Re: why partition pruning doesn't work?

> On 3 June 2018 at 19:11, Tom Lane wrote: > Dmitry Dolgov <9erthali...@gmail.com> writes: >> Just to clarify for myself, for evaluating any stable function here would it >> be >> enough to handle all function-like expressions (FuncExpr / OpExpr / >> DistinctExpr / NullIfExpr) and check a

Re: New committers announced at PGCon 2018

On 06/01/2018 05:05 PM, Tom Lane wrote: The core team is pleased to announce the appointment of seven new Postgres committers: Etsuro Fujita Peter Geoghegan Amit Kapila Alexander Korotkov Thomas Munro Michael Paquier Tomas Vondra Congratulations to all! Congratulations to all - well

Re: [PATCH] Improve geometric types

> But now I'm wondering what does this mean for existing indexes? Doesn't this > effectively mean those are unlikely to give meaningful responses (in the old > or new semantics)? The patches shouldn't cause any change to the indexable operators. The fixes are mostly around the lines and the line

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

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> > Hello. > > At Fri, 1 Jun 2018 10:21:39 -0400, Ashutosh Bapat > wrote in > > > I am not suggesting to commit 0003 in my patch set, but just 0001

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

Hello. At Fri, 1 Jun 2018 10:21:39 -0400, Ashutosh Bapat wrote in > I am not suggesting to commit 0003 in my patch set, but just 0001 and > 0002 which just raise an error when multiple rows get updated when > only one row is expected to be updated. I reconsidered Tom's suggestion and found a

Re: New committers announced at PGCon 2018

Congrats  to all ! regards, On 06/04/2018 11:14 AM, Rajkumar Raghuwanshi wrote: Congratulations Everyone :) Thanks & Regards, Rajkumar Raghuwanshi QMG, EnterpriseDB Corporation On Sun, Jun 3, 2018 at 10:55 PM, MauMau > wrote: From: Tom Lane The core team

Re: pg_replication_slot_advance to return NULL instead of 0/0 if slot not advanced

Hi, On 01/06/18 21:13, Michael Paquier wrote: > - startlsn = MyReplicationSlot->data.confirmed_flush; > + if (OidIsValid(MyReplicationSlot->data.database)) > + startlsn = MyReplicationSlot->data.confirmed_flush; > + else > + startlsn =

Re: plans for PostgreSQL 12

2018-06-04 9:59 GMT+02:00 Vik Fearing : > On 04/06/18 09:37, Pavel Stehule wrote: > > > > > > 2018-06-04 9:24 GMT+02:00 Heikki Linnakangas > >: > > > > On 04/06/18 09:12, Pavel Stehule wrote: > > > > It requires introduction of new "safe" functions (&

Spilling hashed SetOps and aggregates to disk

Hi, Hash Aggs and SetOps are currently not spilled to disk. If the planner's estimate on the number of entries is badly off, you might run out of memory at execution time, if all the entries don't fit in memory. For HashAggs, this was discussed in depth a couple of years ago at [1]. SetOps

Re: plans for PostgreSQL 12

On 04/06/18 09:37, Pavel Stehule wrote: > > > 2018-06-04 9:24 GMT+02:00 Heikki Linnakangas >: > > On 04/06/18 09:12, Pavel Stehule wrote: > > It requires introduction of new "safe" functions (& operators). > Immutable > functions are not

Re: plans for PostgreSQL 12

2018-06-04 9:24 GMT+02:00 Heikki Linnakangas : > On 04/06/18 09:12, Pavel Stehule wrote: > >> 2018-06-04 8:35 GMT+02:00 Simon Riggs : >> >>> >>> Sounds good. I think this would need to be restricted by operator and >>> datatype, since in general you won't know if the datatype functions >>> need a

Re: plans for PostgreSQL 12

On 04/06/18 09:12, Pavel Stehule wrote: 2018-06-04 8:35 GMT+02:00 Simon Riggs : Sounds good. I think this would need to be restricted by operator and datatype, since in general you won't know if the datatype functions need a snapshot or not. Immutable functions for the operators ought to do

Re: plans for PostgreSQL 12

On 4 June 2018 at 06:08, Pavel Stehule wrote: > 4. optimization expression without necessity to create snapshots - > experiments > > @4 There are lot of not database expressions in PLpgSQL - like var1 := var1 > + var2 or var1 := var1 + konst. Own calculation needs about 1% of time of > total