Re: [HACKERS] Issue with circular references in VIEW

Gilles Darold writes: > There is an issue with version prior to 10 when dumping views with circular > references. I know that these views are now exported as views in 10 but they > are still exported as TABLE + RULE in prior versions. This conduct to the > following

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

On 7/23/17 11:48 PM, Masahiko Sawada wrote: On Sat, Jul 22, 2017 at 8:04 AM, Stephen Frost wrote: I started discussing this with David off-list and he'd like a chance to review it in a bit more detail (he's just returning from being gone for a few weeks). That review will

Re: [HACKERS] why not parallel seq scan for slow functions

On Sat, Jul 22, 2017 at 8:53 PM, Amit Kapila wrote: > On Thu, Jul 13, 2017 at 7:38 AM, Amit Kapila > wrote: > > On Wed, Jul 12, 2017 at 11:20 PM, Jeff Janes > wrote: > >> > >> > >> > >> Setting parallel_workers to 8

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Mon, Jul 24, 2017 at 11:40 AM, David Steele wrote: > > Before reviewing the patch, I would note that it looks like this issue was > > introduced in b6a323a8c before the 9.6 release. The documentation states > > that

Re: [HACKERS] cache lookup failed error for partition key with custom opclass

Rushabh Lathia writes: > PFA patch, where added elog() to add the error message same as all other > places. Some looking around says that this *isn't* the only place that just blithely assumes that it will find an opfamily entry. But I agree that not checking for that

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

On Mon, Jul 24, 2017 at 11:40 AM, David Steele wrote: > Before reviewing the patch, I would note that it looks like this issue was > introduced in b6a323a8c before the 9.6 release. The documentation states > that the pg_stop_backup() function will wait for all required WAL

Re: [HACKERS] Increase Vacuum ring buffer.

On Mon, Jul 24, 2017 at 6:37 AM, Sokolov Yura wrote: > Good day, Claudio > > > On 2017-07-22 00:27, Claudio Freire wrote: >> >> On Fri, Jul 21, 2017 at 2:41 PM, Sokolov Yura >> wrote: >>> >>> >>> My friend noticed, that I didn't said why

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

David, * David Steele (da...@pgmasters.net) wrote: > On 7/23/17 11:48 PM, Masahiko Sawada wrote: > >On Sat, Jul 22, 2017 at 8:04 AM, Stephen Frost wrote: > >> > >>I started discussing this with David off-list and he'd like a chance to > >>review it in a bit more detail (he's

[HACKERS] Issue with circular references in VIEW

Hi, There is an issue with version prior to 10 when dumping views with circular references. I know that these views are now exported as views in 10 but they are still exported as TABLE + RULE in prior versions. This conduct to the following error when columns of sub-queries doesn't have the same

Re: [HACKERS] Syncing sql extension versions with shared library versions

On Sat, Jul 22, 2017 at 10:50 AM, Robert Haas wrote: > On Fri, Jul 21, 2017 at 4:17 PM, Mat Arye wrote: > > (I > > want to avoid having to keep backwards-compatibility for all functions in > > future shared-libraries). > > Are you sure that's a good

Re: [HACKERS] segfault in HEAD when too many nested functions call

Hi, On 2017-07-21 20:17:54 -0400, Tom Lane wrote: > > I dislike having the miscadmin.h include in executor.h - but I don't > > quite see a better alternative. > > I seriously, seriously, seriously dislike that. You practically might as > well put miscadmin.h into postgres.h. Instead, what do

Re: [HACKERS] Issue with circular references in VIEW

Gilles Darold writes: > Le 24/07/2017 à 19:19, Tom Lane a écrit : >> ... I'm inclined to think in terms of fixing it at that level >> rather than in pg_dump. It doesn't look like it would be hard to fix: >> both functions ultimately call get_query_def(), it's just that

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

On 7/24/17 12:28 PM, Stephen Frost wrote: * David Steele (da...@pgmasters.net) wrote: While this patch brings pg_stop_backup() in line with the documentation, it also introduces a behavioral change compared to 9.6. Currently, the default 9.6 behavior on a standby is to return immediately

Re: [HACKERS] Buildfarm failure and dubious coding in predicate.c

On Tue, Jul 25, 2017 at 7:24 AM, Tom Lane wrote: > Thomas Munro writes: >> Ahh, I think I see it. This is an EXEC_BACKEND build farm animal. >> Theory: After the backend we see had removed the scratch entry and >> before it had restored it,

Re: [HACKERS] Buildfarm failure and dubious coding in predicate.c

Thomas Munro writes: > Ahh, I think I see it. This is an EXEC_BACKEND build farm animal. > Theory: After the backend we see had removed the scratch entry and > before it had restored it, another backend started up and ran > InitPredicateLocks(), which inserted a

Re: [HACKERS] Change in "policy" on dump ordering?

Stephen Frost writes: > * Peter Eisentraut (peter.eisentr...@2ndquadrant.com) wrote: >> On 3/6/17 03:33, Michael Banck wrote: >>> Would this be a candidate for backpatching, or is the behaviour change >>> in pg_dump trumping the issues it solves? >> Unless someone literally

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

On Mon, Jul 24, 2017 at 12:31 PM, Stephen Frost wrote: > Those backup scripts might very well be, today, producing invalid > backups though, which would be much less good.. True. However, I suspect that depends on what procedure is actually being followed. If *everyone* who

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

On Mon, Jul 24, 2017 at 6:45 PM, Stephen Frost wrote: > What the change would do is make the pg_stop_backup() caller block until > the last WAL is archvied, and perhaps that ends up taking hours, and > then the connection is dropped for whatever reason and the backup fails >

Re: [HACKERS] Issue with circular references in VIEW

Le 24/07/2017 à 21:18, Tom Lane a écrit : > Gilles Darold writes: >> Le 24/07/2017 à 19:19, Tom Lane a écrit : >>> ... I'm inclined to think in terms of fixing it at that level >>> rather than in pg_dump. It doesn't look like it would be hard to fix: >>> both functions

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

On 7/24/17 3:28 PM, David Steele wrote: Yes, and this is another behavioral change to consider -- one that is more likely to impact users than the change to pg_stop_backup(). If pg_basebackup is not currently waiting for WAL on a standby (but does on a primary) then that's pretty serious.

Re: [HACKERS] pl/perl extension fails on Windows

On Wed, Jul 19, 2017 at 05:01:31PM -0400, Tom Lane wrote: > Ashutosh Sharma writes: > > Here are the list of macros and variables from 'intrpvar.h' file that > > are just defined in perl module but not in plperl on Windows, > > > #ifdef PERL_USES_PL_PIDSTATUS > >

Re: [HACKERS] segfault in HEAD when too many nested functions call

On Tue, Jul 18, 2017 at 01:04:10PM -0700, Andres Freund wrote: > Ok, I'll flesh out the patch till Thursday. But I do think we're going > to have to do something about the back branches, too. This PostgreSQL 10 open item is past due for your status update. Kindly send a status update within 24

Re: [HACKERS] Oddity in error handling of constraint violation in ExecConstraints for partitioned tables

Hi Amit, On 2017/07/24 14:09, Amit Khandekar wrote: >>> On 2017/07/10 14:15, Etsuro Fujita wrote: >>> Another thing I noticed is the error handling in ExecWithCheckOptions; it >>> doesn't take any care for partition tables, so the column description in >>> the error message for WCO_VIEW_CHECK is

Fwd: [HACKERS] Syncing sql extension versions with shared library versions

(adding -hackers back into thread, got dropped by my email client, sorry) On Mon, Jul 24, 2017 at 1:38 PM, Tom Lane wrote: > Mat Arye writes: > > I tried looking in the contrib modules and didn't find many with lots of > > planner hook usage. > > I'm

Re: [HACKERS] Oddity in error handling of constraint violation in ExecConstraints for partitioned tables

On Mon, Jul 24, 2017 at 6:21 AM, Amit Langote wrote: > Yes, we need that there too. > > Done that in the attached v3 (including the test where > ExecPartitionCheck() would have crashed without the patch). Committed. Thanks to all of you. -- Robert Haas

Re: [HACKERS] VACUUM and ANALYZE disagreeing on what reltuples means

Tomas Vondra writes: > It seems to me that VACUUM and ANALYZE somewhat disagree on what exactly > reltuples means. VACUUM seems to be thinking that > reltuples = live + dead > while ANALYZE apparently believes that > reltuples = live > The question is -

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On 2017-07-24 23:31, Mark Rofail wrote: On Mon, Jul 24, 2017 at 11:25 PM, Erik Rijkers wrote: This patch doesn't apply to HEAD at the moment ( e2c8100e6072936 ). My bad, I should have mentioned that the patch is dependant on the original patch. Here is a *unified* patch

Re: [HACKERS] Mishandling of WCO constraints in direct foreign table modification

On Fri, Jul 21, 2017 at 6:21 AM, Etsuro Fujita wrote: > I mean constraints derived from WITH CHECK OPTIONs specified for parent > views. We use the words "WITH CHECK OPTION constraints" in comments in > nodeModifyTable.c, so the expression "CHECK OPTION constrains"

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

It certainly is, thank you for the heads up. I included a note to encourage the user to index the referencing column instead. On Sun, Jul 23, 2017 at 4:41 AM, Robert Haas wrote: > > This is a jumbo king-sized can of worms, and even a very experienced > contributor would

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

On 2017-07-24 23:08, Mark Rofail wrote: Here is the new Patch with the bug fixes and the New Patch with the Index in place performance results. I just want to point this out because I still can't believe the numbers. In reference to the old patch: The new patch without the index suffers a

Re: [HACKERS] [PATCH] Pageinspect - add functions on GIN and GiST indexes from gevel

On Fri, Jul 21, 2017 at 8:05 AM, Alexey Chernyshov wrote: > the following patch transfers functionality from gevel module > (http://www.sai.msu.su/~megera/wiki/Gevel) which provides functions for > analyzing GIN and GiST indexes to pageinspect. Gevel was originally >

Re: [HACKERS] GSoC 2017: Foreign Key Arrays

> > However, there is a bug that prevented me from testing the third scenario, > I assume there's an issue of incompatible types problem since the right > operand type is anyelement and the supporting procedures expect anyarray. > I am working on debugging it right now. > I have also solved the

Re: Fwd: [HACKERS] Syncing sql extension versions with shared library versions

Mat Arye writes: > On Mon, Jul 24, 2017 at 1:38 PM, Tom Lane wrote: >> I'm not really sure why planner hooks would have anything to do with your >> exposed SQL API? > Sorry what I meant was i'd like to package different versions of my > extension --

[HACKERS] VACUUM and ANALYZE disagreeing on what reltuples means

Hi, It seems to me that VACUUM and ANALYZE somewhat disagree on what exactly reltuples means. VACUUM seems to be thinking that reltuples = live + dead while ANALYZE apparently believes that reltuples = live This causes somewhat bizarre changes in the value, depending on which of

Re: [HACKERS] Increase Vacuum ring buffer.

On Thu, Jul 20, 2017 at 12:51 PM, Tom Lane wrote: > Robert Haas writes: > > I think that's a valid point. There are also other concerns here - > > e.g. whether instead of adopting the patch as proposed we ought to (a) > > use some smaller size, or (b)

Re: [HACKERS] Increase Vacuum ring buffer.

On Mon, Jul 24, 2017 at 2:20 PM, Claudio Freire wrote: > On Mon, Jul 24, 2017 at 2:10 PM, Sokolov Yura > wrote: >> On 2017-07-24 19:11, Claudio Freire wrote: >>> I was mostly thinking about something like the attached patch. >>> >>> Simple,

Re: [HACKERS] segfault in HEAD when too many nested functions call

Andres Freund writes: > On 2017-07-21 20:17:54 -0400, Tom Lane wrote: >>> I dislike having the miscadmin.h include in executor.h - but I don't >>> quite see a better alternative. >> I seriously, seriously, seriously dislike that. You practically might as >> well put

Re: [HACKERS] segfault in HEAD when too many nested functions call

On 2017-07-24 13:27:58 -0400, Tom Lane wrote: > Andres Freund writes: > >> I seriously, seriously, seriously dislike that. You practically might as > >> well put miscadmin.h into postgres.h. Instead, what do you think of > >> requiring the individual ExecProcNode functions

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On Mon, Jul 24, 2017 at 10:50 AM, Joshua D. Drake wrote: > Does this suggest that we don't have a cleanup problem but a fragmentation > problem (or both at least for the index)? Having an index that is almost > twice the uncleaned up size isn't that uncommon. As Tom

Re: [HACKERS] Increase Vacuum ring buffer.

On 2017-07-24 19:11, Claudio Freire wrote: On Mon, Jul 24, 2017 at 6:37 AM, Sokolov Yura wrote: Good day, Claudio On 2017-07-22 00:27, Claudio Freire wrote: On Fri, Jul 21, 2017 at 2:41 PM, Sokolov Yura wrote: My friend noticed,

Re: [HACKERS] Increase Vacuum ring buffer.

On Mon, Jul 24, 2017 at 2:10 PM, Sokolov Yura wrote: > On 2017-07-24 19:11, Claudio Freire wrote: >> >> On Mon, Jul 24, 2017 at 6:37 AM, Sokolov Yura >> wrote: >>> >>> Good day, Claudio >>> >>> >>> On 2017-07-22 00:27, Claudio Freire

Re: [HACKERS] autovacuum can't keep up, bloat just continues to rise

On 07/23/2017 12:03 PM, Joshua D. Drake wrote: As you can see even with aggressive vacuuming, over a period of 36 hours life gets increasingly miserable. The largest table is: postgres=# select pg_size_pretty(pg_total_relation_size('bmsql_order_line')); pg_size_pretty

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Mon, Jul 24, 2017 at 2:43 PM, Peter Geoghegan wrote: > On Mon, Jul 24, 2017 at 10:13 AM, Claudio Freire > wrote: >> Vacuum *might* be able to redistribute tuples too, while holding locks >> to all 3 pages (the two children and the parent page), since it

Re: [HACKERS] Issue with circular references in VIEW

Le 24/07/2017 à 19:19, Tom Lane a écrit : > Gilles Darold writes: >> There is an issue with version prior to 10 when dumping views with circular >> references. I know that these views are now exported as views in 10 but they >> are still exported as TABLE + RULE in prior

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Wed, Nov 23, 2016 at 12:27 AM, Peter Geoghegan wrote: > On Mon, Nov 21, 2016 at 5:15 PM, Claudio Freire > wrote: >>> There are a couple >>> of tricky issues with that that you'd have to look out for, like >>> making sure that the high key continues to

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Mon, Jul 24, 2017 at 2:02 PM, Peter Geoghegan wrote: > On Mon, Jul 24, 2017 at 9:51 AM, Claudio Freire > wrote: >> My point was that the TID doesn't have to point to an actual tuple. >> >> It's more of a keyspace thing, so it doesn't need to match real

Re: [HACKERS] Definitional questions for pg_sequences view

On 7/20/17 16:36, Tom Lane wrote: > What exactly is the point of the new pg_sequences view? It is analogous to pg_tables, pg_matviews, pg_indexes, and other such system views that are sort of half-way between system catalogs and information schema. -- Peter Eisentraut

Re: [HACKERS] Buildfarm failure and dubious coding in predicate.c

On Mon, Jul 24, 2017 at 11:51 AM, Thomas Munro wrote: > On Sun, Jul 23, 2017 at 8:32 AM, Tom Lane wrote: >> Meanwhile, it's still pretty unclear what happened yesterday on >> culicidae. > > That failure is indeed baffling. The only code that

Re: [HACKERS] pg_stop_backup(wait_for_archive := true) on standby server

Robert, * Robert Haas (robertmh...@gmail.com) wrote: > On Mon, Jul 24, 2017 at 12:31 PM, Stephen Frost wrote: > > Those backup scripts might very well be, today, producing invalid > > backups though, which would be much less good.. > > True. However, I suspect that depends

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Mon, Jul 24, 2017 at 9:51 AM, Claudio Freire wrote: > My point was that the TID doesn't have to point to an actual tuple. > > It's more of a keyspace thing, so it doesn't need to match real > tuples, it can just divide the keyspace with an arbitrary cutoff, and > should

Re: [HACKERS] [WIP] [B-Tree] Keep indexes sorted by heap physical location

On Mon, Jul 24, 2017 at 10:13 AM, Claudio Freire wrote: > In most cases, the TID itself can be omitted when the key itself > differs already. > > When a split happens, a TID will be added referring to a real tid from > a child page iff necessary. > > This gives a lot of

Re: [HACKERS] why not parallel seq scan for slow functions

On Mon, Jul 24, 2017 at 9:21 PM, Jeff Janes wrote: > On Sat, Jul 22, 2017 at 8:53 PM, Amit Kapila > wrote: >> >> On Thu, Jul 13, 2017 at 7:38 AM, Amit Kapila >> wrote: >> > On Wed, Jul 12, 2017 at 11:20 PM, Jeff Janes

Re: [HACKERS] Testlib.pm vs msys

I wrote: > Andrew Dunstan writes: >> The problem is command_like's use of redirection to strings. Why this >> should be a problem for this particular use is a matter of speculation. > I looked at jacana's two recent pg_ctlCheck failures, and they both > seem to

Re: [HACKERS] More race conditions in logical replication

Alvaro Herrera wrote: > I'm back at looking into this again, after a rather exhausting week. I > think I have a better grasp of what is going on in this code now, Here's an updated patch, which I intend to push tomorrow. > and it > appears to me that we should change the locking so that

Re: [HACKERS] cache lookup failed error for partition key with custom opclass

On Mon, Jul 24, 2017 at 7:23 PM, Tom Lane wrote: > Rushabh Lathia writes: > > PFA patch, where added elog() to add the error message same as all other > > places. > > Some looking around says that this *isn't* the only place that just > blithely

Re: [HACKERS] Increase Vacuum ring buffer.

Good day, Claudio On 2017-07-22 00:27, Claudio Freire wrote: On Fri, Jul 21, 2017 at 2:41 PM, Sokolov Yura wrote: My friend noticed, that I didn't said why I bother with autovacuum. Our customers suffers from table bloating. I've made synthetic bloating test, and

Re: [HACKERS] Increase Vacuum ring buffer.

On 2017-07-21 20:41, Sokolov Yura wrote: On 2017-07-21 19:32, Robert Haas wrote: On Fri, Jul 21, 2017 at 4:19 AM, Sokolov Yura wrote: Probably with increased ring buffer there is no need in raising vacuum_cost_limit. Will you admit it? No, I definitely won't

Re: [HACKERS] segfault in HEAD when too many nested functions call

On July 24, 2017 7:10:19 AM GMT+01:00, Noah Misch wrote: >On Tue, Jul 18, 2017 at 01:04:10PM -0700, Andres Freund wrote: >> Ok, I'll flesh out the patch till Thursday. But I do think we're >going >> to have to do something about the back branches, too. > >This PostgreSQL 10

Re: [HACKERS] Oddity in error handling of constraint violation in ExecConstraints for partitioned tables

On 2017/07/24 17:30, Etsuro Fujita wrote: > On 2017/07/24 16:16, Amit Khandekar wrote: >> On 24 July 2017 at 12:11, Amit Langote >> wrote: >>> Attached is the updated version of your patch. > > Good catch, Amit K. and Amit L.! > >> Now that this is done, any

Re: [HACKERS] Oddity in error handling of constraint violation in ExecConstraints for partitioned tables

On 2017/07/24 16:16, Amit Khandekar wrote: On 24 July 2017 at 12:11, Amit Langote wrote: Attached is the updated version of your patch. Good catch, Amit K. and Amit L.! Now that this is done, any particular reason it is not done in ExecPartitionCheck() ? I

Re: [HACKERS] Oddity in error handling of constraint violation in ExecConstraints for partitioned tables

On 24 July 2017 at 12:11, Amit Langote wrote: > Hi Amit, > > On 2017/07/24 14:09, Amit Khandekar wrote: On 2017/07/10 14:15, Etsuro Fujita wrote: Another thing I noticed is the error handling in ExecWithCheckOptions; it doesn't take any care for

[HACKERS] cache lookup failed error for partition key with custom opclass

Hi, Consider the following test: CREATE OR REPLACE FUNCTION dummy_binaryint4(a int4, b int4) RETURNS int4 AS $$ BEGIN RETURN a; END; $$ LANGUAGE 'plpgsql' IMMUTABLE; CREATE OPERATOR CLASS custom_opclass2 FOR TYPE int2 USING BTREE AS OPERATOR 1 = , FUNCTION 1 dummy_binaryint4(int4, int4); t=#