Re: [HACKERS] 10.0

On 15 June 2016 at 06:48, David G. Johnston wrote: > > ​We could stand to be more explicit here. The docs for version() > indicated the server_version_num should be used for "machine processing". > > The implied correct way to access the canonical server version is

Re: [HACKERS] New design for FK-based join selectivity estimation

Hi, On 06/16/2016 01:00 AM, Tom Lane wrote: Tomas Vondra writes: Attached is a reworked patch, mostly following the new design proposal from this thread. I've whacked this around quite a bit and am now reasonably happy with it. The issue of planning speed hit

Re: [HACKERS] MultiXactId error after upgrade to 9.3.4

Alvaro Herrera wrote: > Andrew Gierth wrote: > > Why is the correct rule not "check for and ignore pre-upgrade mxids > > before even trying to fetch members"? > > I propose something like the attached patch, which implements that idea. Hm, this doesn't apply cleanly to 9.4. I'll need to come

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

On Fri, Jun 17, 2016 at 3:20 AM, Robert Haas wrote: > > On Thu, Jun 16, 2016 at 12:16 PM, Robert Haas wrote: > > On Thu, Jun 16, 2016 at 8:00 AM, Amit Kapila wrote: > >>> 1. The case originally reported by Thomas Munro still

Re: [HACKERS] Reviewing freeze map code

On Wed, Jun 15, 2016 at 08:56:52AM -0400, Robert Haas wrote: > On Wed, Jun 15, 2016 at 2:41 AM, Thomas Munro > wrote: > > I spent some time chasing down the exact circumstances. I suspect > > that there may be an interlocking problem in heap_update. Using the > >

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

Amit Kapila writes: > On Thu, Jun 16, 2016 at 11:49 PM, Tom Lane wrote: >> But you need to include to use INT_MAX ... > I wonder why it has not given me any compilation error/warning. I have > tried it on both Windows and CentOS, before sending the

Re: [HACKERS] MultiXactId error after upgrade to 9.3.4

Andrew Gierth wrote: > But that leaves an obvious third issue: it's all very well to ignore the > pre-upgrade (pre-9.3) mxid if it's older than the cutoff or it's in the > future, but what if it's actually inside the currently valid range? > Looking it up as though it were a valid mxid in that

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

On Thu, Jun 16, 2016 at 11:49 PM, Tom Lane wrote: > > Amit Kapila writes: > > On Mon, Jun 13, 2016 at 10:36 PM, Tom Lane wrote: > >> min_parallel_relation_size, or min_parallelizable_relation_size, or > >> something like

Re: [HACKERS] [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116

On 2016/06/16 22:00, Robert Haas wrote: On Thu, Jun 16, 2016 at 7:05 AM, Etsuro Fujita wrote: ISTM that a robuster solution to this is to push down the ft1-ft2-ft3 join with the PHV by extending deparseExplicitTargetList() and/or something else so that we can send

Re: [HACKERS] Parallelized polymorphic aggs, and aggtype vs aggoutputtype

Robert Haas writes: > On Thu, Jun 16, 2016 at 6:40 PM, Tom Lane wrote: >> Meh. I think this is probably telling us that trying to repurpose Aggref >> as the representation of a partial aggregate may have been mistaken. > I don't mind if you feel the

Re: [HACKERS] Parallelized polymorphic aggs, and aggtype vs aggoutputtype

On Thu, Jun 16, 2016 at 6:40 PM, Tom Lane wrote: > Meh. I think this is probably telling us that trying to repurpose Aggref > as the representation of a partial aggregate may have been mistaken. > Or maybe just that fix_combine_agg_expr was a bad idea. It seems likely > to

Re: [HACKERS] 10.0

On 15 Jun 2016 2:59 pm, "David G. Johnston" wrote: > > IIRC the plan is to have the machine version behave as if the middle number is present and always zero. It would be (the?) one place that the historical behavior remains visible but it is impossible to have a

Re: [HACKERS] Parallelized polymorphic aggs, and aggtype vs aggoutputtype

Robert Haas writes: > On Thu, Jun 16, 2016 at 4:31 PM, Tom Lane wrote: >> However, before trying to fix any of that, I'd like to demand an >> explanation as to why aggoutputtype exists at all. It seems incredibly >> confusing to have both that and

[HACKERS] Re: [COMMITTERS] pgsql: Update pg_stat_statements extension for parallel query.

On Thu, Jun 16, 2016 at 4:51 PM, Vik Fearing wrote: > On 10/06/16 17:01, Robert Haas wrote: >> Update pg_stat_statements extension for parallel query. > > I couldn't readily find a review for this patch, and I am unsatisfied > with it. I think it's very strange that a 1.4

Re: [HACKERS] Parallelized polymorphic aggs, and aggtype vs aggoutputtype

On Thu, Jun 16, 2016 at 4:31 PM, Tom Lane wrote: > However, before trying to fix any of that, I'd like to demand an > explanation as to why aggoutputtype exists at all. It seems incredibly > confusing to have both that and aggtype, and certainly the code comments > give no

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

On Thu, Jun 16, 2016 at 12:16 PM, Robert Haas wrote: > On Thu, Jun 16, 2016 at 8:00 AM, Amit Kapila wrote: >>> 1. The case originally reported by Thomas Munro still fails. To fix >>> that, we probably need to apply scanjoin_target to each partial

Re: [HACKERS] proposal: integration bloat tables (indexes) to core

Tom Lane-2 wrote > The problem with an extension is: when we make a core change that breaks > one of these views, which we will, how can you pg_upgrade a database > with the extension installed? There's no provision for upgrading an > extension concurrently with the core upgrade. Maybe there

[HACKERS] Parallelized polymorphic aggs, and aggtype vs aggoutputtype

I believe I've narrowed down the cause of this failure [1]: regression=# explain SELECT min(col) FROM enumtest; ERROR: type matched to anyenum is not an enum type: anyenum The core reason for this seems to be that apply_partialaggref_adjustment fails to consider the possibility that it's

Re: [HACKERS] proposal: integration bloat tables (indexes) to core

On 16/06/16 20:31, Jim Nasby wrote: > On 6/13/16 12:16 PM, Tom Lane wrote: >> At the same time, I'm pretty suspicious of putting stuff like this in >> core, because the expectations for cross-version compatibility go up >> by orders of magnitude as soon as we do that. > > On a first go-round, I

Re: [HACKERS] proposal: integration bloat tables (indexes) to core

2016-06-16 20:31 GMT+02:00 Jim Nasby : > On 6/13/16 12:16 PM, Tom Lane wrote: > >> At the same time, I'm pretty suspicious of putting stuff like this in >> core, because the expectations for cross-version compatibility go up >> by orders of magnitude as soon as we do

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-06-16 11:57:48 -0700, Andres Freund wrote: > See > https://www.postgresql.org/message-id/20160616183207.wygoktoplycdz...@alap3.anar For posterity's sake, that was supposed to be https://www.postgresql.org/message-id/20160616183207.wygoktoplycdz...@alap3.anarazel.de -- Sent via

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-06-16 13:53:01 -0500, Kevin Grittner wrote: > On Thu, Jun 16, 2016 at 1:19 PM, Kevin Grittner wrote: > > On Thu, Jun 16, 2016 at 11:54 AM, Andres Freund wrote: > >> On 2016-06-16 12:43:34 -0400, Robert Haas wrote: > > >>> Maybe it would help if you

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Thu, Jun 16, 2016 at 1:32 PM, Andres Freund wrote: > With old_snapshot_threshold=1 I indeed can reproduce the issue. I > disabled autovacuum, to make the scheduling more predictable. But it > should "work" just as well with autovacuum. > > S1: CREATE TABLE toasted(largecol

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Thu, Jun 16, 2016 at 1:19 PM, Kevin Grittner wrote: > On Thu, Jun 16, 2016 at 11:54 AM, Andres Freund wrote: >> On 2016-06-16 12:43:34 -0400, Robert Haas wrote: >>> Maybe it would help if you lay out the whole sequence of events, like: >>> >>> S1: Does

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

Hi, On 2016-06-16 13:19:13 -0500, Kevin Grittner wrote: > On Thu, Jun 16, 2016 at 11:54 AM, Andres Freund wrote: > > On 2016-06-16 12:43:34 -0400, Robert Haas wrote: > > >> The root of my confusion is: if we prune a tuple, we'll bump the page > >> LSN, so any session that is

Re: [HACKERS] proposal: integration bloat tables (indexes) to core

On 6/13/16 12:16 PM, Tom Lane wrote: At the same time, I'm pretty suspicious of putting stuff like this in core, because the expectations for cross-version compatibility go up by orders of magnitude as soon as we do that. On a first go-round, I don't think we should add entire views, but

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-06-16 13:16:35 -0500, Kevin Grittner wrote: > On Thu, Jun 16, 2016 at 1:01 PM, Andres Freund wrote: > > > The relevant part is the HeapTupleSatisfiesMVCC check, we're using > > SatisfiesToast for toast lookups. > > > > FWIW, I just tried to reproduce this with

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

Amit Kapila writes: > On Mon, Jun 13, 2016 at 10:36 PM, Tom Lane wrote: >> min_parallel_relation_size, or min_parallelizable_relation_size, or >> something like that? > You are right that such a variable will make it simpler to write tests for >

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Thu, Jun 16, 2016 at 11:54 AM, Andres Freund wrote: > On 2016-06-16 12:43:34 -0400, Robert Haas wrote: >> The root of my confusion is: if we prune a tuple, we'll bump the page >> LSN, so any session that is still referencing that tuple will error >> out as soon as it

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Thu, Jun 16, 2016 at 1:01 PM, Andres Freund wrote: > The relevant part is the HeapTupleSatisfiesMVCC check, we're using > SatisfiesToast for toast lookups. > > FWIW, I just tried to reproduce this with old_snapshot_threshold = 0 - > but ran into the problem that I couldn't

[HACKERS] More parallel-query fun

As of HEAD you can exercise quite a lot of parallel query behavior by running the regression tests with these settings applied: force_parallel_mode = regress max_parallel_workers_per_gather = 2-- this is default at the moment min_parallel_relation_size = 0 parallel_setup_cost = 0

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-06-16 13:44:34 -0400, Robert Haas wrote: > On Thu, Jun 16, 2016 at 12:54 PM, Andres Freund wrote: > >> The root of my confusion is: if we prune a tuple, we'll bump the page > >> LSN, so any session that is still referencing that tuple will error > >> out as soon as it

Re: [HACKERS] [GENERAL] PgQ and pg_dump

Hi, 2016-06-16 9:48 GMT-03:00 Michael Paquier : > On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués > wrote: >> El 16/06/16 a las 00:08, Michael Paquier escribió: >>> On Wed, Jun 15, 2016 at 7:19 PM, Martín Marqués >>>

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Thu, Jun 16, 2016 at 12:54 PM, Andres Freund wrote: >> The root of my confusion is: if we prune a tuple, we'll bump the page >> LSN, so any session that is still referencing that tuple will error >> out as soon as it touches the page on which that tuple used to exist. > >

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

On Thu, Jun 16, 2016 at 12:50 PM, Tom Lane wrote: > Amit Kapila writes: >> On Thu, Jun 16, 2016 at 9:26 AM, Robert Haas wrote: >>> 3. In https://www.postgresql.org/message-id/25521.1465837...@sss.pgh.pa.us >>> Tom proposed

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-06-16 12:43:34 -0400, Robert Haas wrote: > On Thu, Jun 16, 2016 at 12:14 PM, Andres Freund wrote: > >> > The issue isn't there without the feature, because we (should) never > >> > access a tuple/detoast a column when it's invisible enough for the > >> > corresponding

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

Amit Kapila writes: > On Thu, Jun 16, 2016 at 9:26 AM, Robert Haas wrote: >> 3. In https://www.postgresql.org/message-id/25521.1465837...@sss.pgh.pa.us >> Tom proposed adding a GUC to set the minimum relation size required >> for consideration of

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Thu, Jun 16, 2016 at 12:14 PM, Andres Freund wrote: >> > The issue isn't there without the feature, because we (should) never >> > access a tuple/detoast a column when it's invisible enough for the >> > corresponding toast tuple to be vacuumed away. But with >> >

Re: [HACKERS] Should phraseto_tsquery('simple', 'blue blue') @@ to_tsvector('simple', 'blue') be true ?

Teodor Sigaev writes: >> So I think there's a reasonable case for decreeing that should only >> match lexemes *exactly* N apart. If we did that, we would no longer have >> the misbehavior that Jean-Pierre is complaining about, and we'd not need >> to argue about whether <0>

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

On Thu, Jun 16, 2016 at 8:00 AM, Amit Kapila wrote: >> 1. The case originally reported by Thomas Munro still fails. To fix >> that, we probably need to apply scanjoin_target to each partial path. >> But we can only do that if it's parallel-safe. It seems like what we >>

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-06-16 12:02:39 -0400, Robert Haas wrote: > On Thu, Jun 16, 2016 at 11:37 AM, Andres Freund wrote: > >> If that were really true, why would we not have the problem in > >> current production versions that the toast table could be vacuumed > >> before the heap, leading

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Thu, Jun 16, 2016 at 11:37 AM, Andres Freund wrote: >> If that were really true, why would we not have the problem in >> current production versions that the toast table could be vacuumed >> before the heap, leading to exactly the issue you are talking >> about? > > The

Re: [HACKERS] Prevent ALTER TABLE DROP NOT NULL on child tables if parent column has it

Michael Paquier writes: > On Wed, Jun 15, 2016 at 10:34 PM, Tom Lane wrote: >> My feeling is that we'd keep >> the pg_attribute.attnotnull field and continue to drive actual enforcement >> off that, but it would just reflect a summary of the

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On 2016-06-16 09:50:09 -0500, Kevin Grittner wrote: > On Wed, Jun 15, 2016 at 8:57 PM, Andres Freund wrote: > > > The simplest version of the scenario I'm concerned about is that a tuple > > in a tuple is *not* vacuumed, even though it's elegible to be removed > > due to STO.

Re: [HACKERS] forcing a rebuild of the visibility map

On Thu, Jun 16, 2016 at 10:03 PM, Robert Haas wrote: > On Thu, Jun 16, 2016 at 2:33 AM, Masahiko Sawada > wrote: >> Option name DISABLE_PAGE_SKIPPING is good to me. >> I'm still working on this, but here are some review comments. >> >> --- >>

Re: [HACKERS] Re: [COMMITTERS] pgsql: Avoid extra locks in GetSnapshotData if old_snapshot_threshold

On Wed, Jun 15, 2016 at 8:57 PM, Andres Freund wrote: > The simplest version of the scenario I'm concerned about is that a tuple > in a tuple is *not* vacuumed, even though it's elegible to be removed > due to STO. If that tuple has toasted columns, it could be the that the >

Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

On Thu, Jun 16, 2016 at 4:35 PM, Euler Taveira wrote: > On 16-06-2016 09:05, Magnus Hagander wrote: > > Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new > > cluster? Not talking about the post-analyze script, but when it runs > > vacuumdb to analyze

Re: [HACKERS] pg_upgrade vs vacuum_cost_delay

On 16-06-2016 09:05, Magnus Hagander wrote: > Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new > cluster? Not talking about the post-analyze script, but when it runs > vacuumdb to analyze and freeze before loading the new schema, in > prepare_new_cluster()? Those run during

Re: [HACKERS] Parallel safety tagging of extension functions

On 01-06-2016 20:52, Andreas Karlsson wrote: > I think at least three of the four aggregate functions are little used, > so I do not think many users would be affected. And only min(citext) and > max(citext) can make use of the parallel aggregation. > > The functions are: > > min(citext) >

Re: [HACKERS] Parallel safety tagging of extension functions

On 06/14/2016 09:55 PM, Robert Haas wrote: On Tue, Jun 14, 2016 at 1:51 PM, Robert Haas wrote: On Tue, Jun 14, 2016 at 6:37 AM, Andreas Karlsson wrote: I have rebased all my patches on the current master now (and skipped the extensions I previously

Re: [HACKERS] [GENERAL] PgQ and pg_dump

El 16/06/16 a las 09:48, Michael Paquier escribió: > On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués > wrote: > >> This problem came up due to a difference between pg_dump on 9.1.12 and >> 9.1.22 (I believe it was due to a patch on pg_dump that excluded the >> dependent

Re: [HACKERS] forcing a rebuild of the visibility map

On Thu, Jun 16, 2016 at 2:33 AM, Masahiko Sawada wrote: > Option name DISABLE_PAGE_SKIPPING is good to me. > I'm still working on this, but here are some review comments. > > --- > +CREATE FUNCTION pg_truncate_visibility_map(regclass) > +RETURNS void > +AS

Re: [HACKERS] [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116

On Thu, Jun 16, 2016 at 7:05 AM, Etsuro Fujita wrote: > ISTM that a robuster solution to this is to push down the ft1-ft2-ft3 join > with the PHV by extending deparseExplicitTargetList() and/or something else > so that we can send the remote query like: > > select

Re: [HACKERS] [GENERAL] PgQ and pg_dump

On Thu, Jun 16, 2016 at 8:37 PM, Martín Marqués wrote: > El 16/06/16 a las 00:08, Michael Paquier escribió: >> On Wed, Jun 15, 2016 at 7:19 PM, Martín Marqués >> wrote: >>> >>> How would the recovery process work? We expect the schema to be there

[HACKERS] pg_upgrade vs vacuum_cost_delay

Shouldn't pg_upgrade turn off vacuum cost delay when it vacuums the new cluster? Not talking about the post-analyze script, but when it runs vacuumdb to analyze and freeze before loading the new schema, in prepare_new_cluster()? Those run during downtime, so it seems like you'd want those to run

Re: [HACKERS] ERROR: ORDER/GROUP BY expression not found in targetlist

On Thu, Jun 16, 2016 at 9:26 AM, Robert Haas wrote: > > On Tue, Jun 14, 2016 at 12:18 PM, Tom Lane wrote: > > Amit Kapila writes: > >> On Mon, Jun 13, 2016 at 9:37 PM, Tom Lane wrote: > >>> ... I got a

Re: [HACKERS] [GENERAL] PgQ and pg_dump

El 16/06/16 a las 00:08, Michael Paquier escribió: > On Wed, Jun 15, 2016 at 7:19 PM, Martín Marqués > wrote: >> >> How would the recovery process work? We expect the schema to be there >> when restoring the tables? > > pg_dump creates the schema first via the CREATE

Re: [HACKERS] [sqlsmith] Failed assertion in postgres_fdw/deparse.c:1116

On 2016/06/15 9:13, Amit Langote wrote: On 2016/06/15 0:50, Robert Haas wrote: On Tue, Jun 14, 2016 at 4:06 AM, Amit Langote wrote: Attached new version of the patch with following changes: OK, I committed this version with some cosmetic changes. Thank you all for working on this! While

Re: [HACKERS] MultiXactId error after upgrade to 9.3.4

> "Alvaro" == Alvaro Herrera writes: Alvaro> I think that was a good choice in general so that Alvaro> possibly-data-eating bugs could be reported, but there's a Alvaro> problem in the specific case of tuples carried over by Alvaro> pg_upgrade whose Multixact is

Re: [HACKERS] sslmode=require fallback

On Thu, Jun 16, 2016 at 10:39 AM, Jakob Egger wrote: > Hi! > > I've looked at the way libpq handles TLS certificates and plaintext > fallback, and I am somewhat surprised. > > The default ssmode is prefer. According to the documentation, this will > make libpq use an SSL

[HACKERS] sslmode=require fallback

Hi! I've looked at the way libpq handles TLS certificates and plaintext fallback, and I am somewhat surprised. The default ssmode is prefer. According to the documentation, this will make libpq use an SSL connection if possible, but will use a plain text connection as a fallback. The

Re: [HACKERS] Hash Indexes

On Tue, May 10, 2016 at 5:39 PM, Amit Kapila wrote: > > > Incomplete Splits > -- > Incomplete splits can be completed either by vacuum or insert as both > needs exclusive lock on bucket. If vacuum finds split-in-progress flag on > a bucket then it

Re: [HACKERS] forcing a rebuild of the visibility map

On Thu, Jun 16, 2016 at 12:06 PM, Robert Haas wrote: > [ Changing subject line in the hopes of keeping separate issues in > separate threads. ] > > On Mon, Jun 6, 2016 at 11:00 AM, Tom Lane wrote: >> Robert Haas writes: >>> I'm