Re: [HACKERS] FDW join pushdown and scanclauses

On Thu, Jan 14, 2016 at 9:31 AM, Etsuro Fujita wrote: > On 2016/01/08 22:05, Ashutosh Bapat wrote: > >> In add_paths_to_joinrel(), the FDW specific hook GetForeignJoinPaths() >> is called. This hook if implemented should add ForeignPaths for pushed >> down joins. create_plan_recurse() calls creat

[HACKERS] About get_relation_constraints and include_notnull

Why does the argument include_notnull argument exist if get_relation_constraints() is being called from only one place? Perhaps we could remove it and add the IS NOT NULL test expression unconditionally if there are any NOT NULL columns. Thanks, Amit -- Sent via pgsql-hackers mailing list (pg

[HACKERS] Removing service-related code in pg_ctl for Cygwin

Hi all, Beginning a new thread seems more adapted regarding $subject but that's mentioned here as well: http://www.postgresql.org/message-id/CAB7nPqQXghm_SdB5iniupz1atzMxk=95gv9a8ocdo83sxcn...@mail.gmail.com It happens based on some investigation from Andrew that cygwin does not need to use the s

Re: [HACKERS] extend pgbench expressions with functions

On Wed, Jan 13, 2016 at 10:28 PM, Fabien COELHO wrote: > > Hello Michaël, > >>> ISTM that if pgbench is to be stopped, the simplest option is just to >>> abort >>> with a nicer error message from the get*Rand function, there is no need >>> to >>> change the function signature and transfer the erro

Re: [HACKERS] Python 3.x versus PG 9.1 branch

On Thu, Jan 14, 2016 at 12:37 PM, Noah Misch wrote: > On Wed, Jan 13, 2016 at 11:46:07AM -0500, Tom Lane wrote: >> [...] we've repeatedly not bothered >> to back-port regression test fixes for newer Pythons into that branch. >> I could just omit Python 3 coverage for that branch in the critter's >

Re: [HACKERS] FDW join pushdown and scanclauses

On 2016/01/08 22:05, Ashutosh Bapat wrote: In add_paths_to_joinrel(), the FDW specific hook GetForeignJoinPaths() is called. This hook if implemented should add ForeignPaths for pushed down joins. create_plan_recurse() calls create_scan_plan() on seeing these paths. create_scan_plan() generate

Re: [HACKERS] Python 3.x versus PG 9.1 branch

On Wed, Jan 13, 2016 at 11:46:07AM -0500, Tom Lane wrote: > [...] we've repeatedly not bothered > to back-port regression test fixes for newer Pythons into that branch. > I could just omit Python 3 coverage for that branch in the critter's > configuration, but I wonder exactly why things are that w

Re: [HACKERS] Truncation of identifiers

On 14/01/16 13:05, Tom Lane wrote: Thomas Munro writes: Wouldn't it be better to raise an error when identifiers are too long, rather than accepting but truncating them? I wouldn't think so. I'm not aware of any other database that does this. It's standard practice in most programming langu

Re: [HACKERS] Removing Functionally Dependent GROUP BY Columns

Many thanks for the thorough review! On 12 January 2016 at 23:37, Julien Rouhaud wrote: > > In identify_key_vars() > > + /* Only PK constraints are of interest for now, see comment above > */ > + if (con->contype != CONSTRAINT_PRIMARY) > + continue; > > I think the comment

Re: [HACKERS] Truncation of identifiers

Thomas Munro writes: > Wouldn't it be better to raise an error when identifiers are too long, > rather than accepting but truncating them? I wouldn't think so. > I'm not aware of any other database that does this. It's standard practice in most programming languages AFAIK. And SQL is surely a

[HACKERS] Truncation of identifiers

Hi hackers Wouldn't it be better to raise an error when identifiers are too long, rather than accepting but truncating them? I'm not aware of any other database that does this. If you're using oversized identifiers you could finish up using more than one way to refer to the same database object,

[HACKERS] PgConf.US Hackers Track CFP

The organization committee for PGConf.US invites all Pg Hackers to the PGConf.US 2016 hackers track. We want to showcase your talents to the general PostgreSQL community, potential new hackers plus provide a forum for feedback and deep discussion by having you present! We have also brought bac

Re: [HACKERS] Weird behavior during CREATE EXTENSION

Jim Nasby writes: > On 1/12/16 5:00 PM, Tom Lane wrote: >> Also, there's some technology in CREATE FUNCTION that deals with the fact >> that we may be calling the parser on a string different from the original >> user command, which might be worth borrowing here --- at least part of >> the confusi

Re: [HACKERS] Fuzzy substring searching with the pg_trgm extension

Jeff Janes writes: > In the meantime, I had a question about bumping the version to 1.3. > Version 1.2 of pg_trgm has never been included in a community release > (because it didn't make the 9.5 cutoff). So should we really bump the > version to 1.3, or just merge the changes here directly into

Re: [HACKERS] pgindent-polluted commits

On Wed, Jan 13, 2016 at 9:13 AM, Tom Lane wrote: > I'm willing to go with the "separate commit to reindent individual files" > approach if there's a consensus that that makes for a cleaner git history. > But I'm not 100% convinced it matters. I recently changed the configuration of my text editor

Re: [HACKERS]WIP: Covering + unique indexes.

On 14 January 2016 at 02:58, Anastasia Lubennikova < a.lubennik...@postgrespro.ru> wrote: > 13.01.2016 04:27, David Rowley: > >> I've also done some testing: >> >> create table ab (a int, b int); >> insert into ab select a,b from generate_Series(1,10) a(a), >> generate_series(1,1) b(b); >> set

Re: [HACKERS] Weird behavior during CREATE EXTENSION

On 1/12/16 5:00 PM, Tom Lane wrote: There's certainly room to improve error reporting for extension scripts, but I think you are heading into a dead end in the name of saving a little time coding. I'd suggest looking into an errcontext callback, instead. Also, there's some technology in CREATE

Re: [HACKERS] Fuzzy substring searching with the pg_trgm extension

On Sat, Dec 26, 2015 at 9:12 PM, Jeff Janes wrote: > On Fri, Dec 18, 2015 at 11:43 AM, Artur Zakirov > wrote: >> Hello. >> >> PostgreSQL has a contrib module named pg_trgm. It is used to the fuzzy text >> search. It provides some functions and operators for determining the >> similarity of the gi

Re: [HACKERS] pgindent-polluted commits

> On Jan 13, 2016, at 9:13 AM, Tom Lane wrote: > > Simon Riggs writes: >> On 13 January 2016 at 14:48, Noah Misch wrote: >>> I've noticed commits, from a few of you, carrying pgindent changes to lines >>> the patch would not otherwise change. > >> Could we review again why this matters? > >

Re: [HACKERS] proposal: PL/Pythonu - function ereport

On 1/12/16 11:25 AM, Catalin Iacob wrote: >The differentiation between Error and SPIError is wrong, because there isn't >any difference in reality. They're similar but not really the same thing. raise Error and plpy.error are both ways to call ereport(ERROR, ...) while SPIError is raised when co

Re: [HACKERS] pgindent-polluted commits

Simon Riggs writes: > On 13 January 2016 at 14:48, Noah Misch wrote: >> I've noticed commits, from a few of you, carrying pgindent changes to lines >> the patch would not otherwise change. > Could we review again why this matters? Basically this is trading off convenience of the committer (all

Re: [HACKERS] pgindent-polluted commits

On 13 January 2016 at 14:48, Noah Misch wrote: > I've noticed commits, from a few of you, carrying pgindent changes to lines > the patch would not otherwise change. Could we review again why this matters? -- Simon Riggshttp://www.2ndQuadrant.com/

[HACKERS] Python 3.x versus PG 9.1 branch

In view of our rather embarrassing failure to cover the back branches with Python 3.5-related regression test adjustments, I think there is a clear need for a buildfarm critter that's testing with Python 3.5, and I've been working on setting one up. It's passing at the moment for 9.2 and up, but n

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 17:12 GMT+01:00 Vladimir Sitnikov : > >If plan is ok for one value parameters, then can be pretty bad for > following parameters. > > Happy statements are all alike; every unhappy statement is unhappy in > its own way (see [1]). > If user is sending different kinds of parameters, he is

Re: [HACKERS] pg_dump and premature optimizations for objects not to be dumped

I wrote: > Attached is a draft patch that does things this way. BTW, the bulk of the changes in this patch are just there to pass down the DumpOptions struct to where it's needed, following some pre-existing work that passed that around as a separate pointer. I wonder why things were done that wa

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

>If plan is ok for one value parameters, then can be pretty bad for following >parameters. Happy statements are all alike; every unhappy statement is unhappy in its own way (see [1]). If user is sending different kinds of parameters, he is shooting in the foot. >Albe's proposal can be good enoug

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

>(1) the generic plan is falsely optimistic That is my case. Application is sending most common value on every execution while backend is optimistic and it things that the app would stop sending MCVs. Costs for the plans are OK. However, there is a data skew, so it is hard to tell what is the "tr

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

Vladimir Sitnikov writes: > Note: I state that mixing "kinds" of bind values is a bad application > design anyway. In other words, application developer should understand > if a query is DWH-like (requires replans) or OLTP-like (does not > require replans). Agreed? No, not agreed. As was already

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 16:22 GMT+01:00 Vladimir Sitnikov : > >A value of -1 could disable generic plans > > I do not like the idea. > > I've seen dramatic performance improvements from using cached plans. > The numbers are like "20ms to plan vs 1ms to execute" for an often > used OLTP query. Query text is inv

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 16:18 GMT+01:00 Albe Laurenz : > Pavel Stehule wrote: > > I like a strategy based on risks. Probably there are situation, when the > generic plan is great every > > time - INSERTs, UPDATEs via PK, simple SELECTs via PK. generic plan can > be well if almost all data has > > similar proba

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

>A value of -1 could disable generic plans I do not like the idea. I've seen dramatic performance improvements from using cached plans. The numbers are like "20ms to plan vs 1ms to execute" for an often used OLTP query. Query text is involved (~5-10KiB). Vladimir -- Sent via pgsql-hackers mai

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

Pavel Stehule wrote: > I like a strategy based on risks. Probably there are situation, when the > generic plan is great every > time - INSERTs, UPDATEs via PK, simple SELECTs via PK. generic plan can be > well if almost all data has > similar probability. Elsewhere on bigger data, the probability

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

>Basically you're arguing to fix one specific edge case which bugs you >personally, by creating a lot of others, which don't bug you. Not >convincing. It bugs me. It bugs clients of pgjdbc (e.g. Thomas who started the thread). Note: support of prepared statements for java applications has just la

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

> Generally using a very small sample That is another issue. Inventing some other algorithm instead of current "cache after 5 executions" is another effort. However, I suggest to "learn" from what client is sending. You suggest to completely ignore that and just prepare for the case he/she will s

[HACKERS] pgindent-polluted commits

I've noticed commits, from a few of you, carrying pgindent changes to lines the patch would not otherwise change. (That is to say, the next pgindent run would have made the same changes anyway.) From https://wiki.postgresql.org/wiki/Submitting_a_Patch#Reasons_your_patch_might_be_returned: The

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

>so you don't get to (or want to) have any control over the underlying prepared >statement. That is pl/pgsql's problem, isn't it? In the mean time, user can use different query texts (e.g. by adding offset 0, offset 0*1, offset 0*2, etc kind of stuff they typically use to tune queries) to convinc

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On 2016-01-13 17:38:22 +0300, Vladimir Sitnikov wrote: > >so you don't get to (or want to) have any control over the underlying > >prepared statement. > > That is pl/pgsql's problem, isn't it? > In the mean time, user can use different query texts (e.g. by adding > offset 0, offset 0*1, offset 0*

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On 13/01/16 15:34, Vladimir Sitnikov wrote: This completely screws over PL/PgSQL, among other things. Can you elaborate a bit? You just write a query like this: SELECT * FROM foo WHERE bar = _Variable; so you don't get to (or want to) have any control over the underlying prepared stateme

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On 2016-01-13 17:26:43 +0300, Vladimir Sitnikov wrote: > > consider e.g a table with one somewhat common and otherwise just unique > > values.> > So what? > If I understand you properly, you mean: "if client sends unique binds > first 5-6 executions and bad non-unique afterwards, then cached plan

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

>This completely screws over PL/PgSQL, among other things. Can you elaborate a bit? Vladimir -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On 13/01/16 15:26, Vladimir Sitnikov wrote: 2) It is likely to be more performant. We just need to explain users that "if different plans required, just use different statements". This completely screws over PL/PgSQL, among other things. .m -- Sent via pgsql-hackers mailing list (pgsql-hack

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

> for one custom plans can be much better than the generic plan, independent of > cardinalities So what? I do not suggest dropping custom plans entirely. I perfectly understand there are cases when better replan every time. > consider e.g a table with one somewhat common and otherwise just uniqu

[HACKERS] Re: Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

Maybe more work, but: Could a custom plan be cached /along with the values of the parameters for which it was planned/? Then it's kind of a no-brainer to say "if the parameters are the same this time, I'm probably looking at the best plan already." Pretty simpleminded, but at least it would catc

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On January 13, 2016 3:02:27 PM GMT+01:00, Vladimir Sitnikov wrote: >> The custom plan is *more expensive*; > >You compare costs of custom vs generic plans. >I suggest: do not compare costs *at all*. > >>I don't know, it's your proposal :-) But it looks like I >misunderstood. > >It is not. > >My

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

> The custom plan is *more expensive*; You compare costs of custom vs generic plans. I suggest: do not compare costs *at all*. >I don't know, it's your proposal :-) But it looks like I misunderstood. It is not. My suggestion is: build a generic plan (that is the plan that will return proper re

Re: [HACKERS]WIP: Covering + unique indexes.

13.01.2016 04:27, David Rowley: I've also done some testing: create table ab (a int, b int); insert into ab select a,b from generate_Series(1,10) a(a), generate_series(1,1) b(b); set enable_bitmapscan=off; set enable_indexscan=off; select * from ab where a = 1 and b=1; a | b ---+--- 1 |

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On 13/01/16 14:36, Vladimir Sitnikov wrote: Say you already have a plan which looks like this: Now the plan gets invoked with $1 = 5. What exactly in your mind would happen here? A sequential scan with $1=5 condition. What else could be there? I don't know, it's your proposal :-) But it l

Re: [HACKERS] pgbench stats per script & other stuff

Hello Alvaro, Here is a two part v12, which: part a (refactoring of scripts and their stats): - fix option checks (-i alone) - s/repleacable/replaceable/ in doc - keep small description in doc and help for -S & -N - fix 2 comments for pg style - show builtin list if not found I'm lookin

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

Note: I do not suggest changing already cached plans yet. I suggest looking into "6th bind values" when building a cached plan. In other words, "if first 5 execution do not reveal dependence on bind values, then cache the generated plan". >Say you already have a plan which looks like this: >Now th

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On 13/01/16 14:27, Vladimir Sitnikov wrote: TL;DR: I suggest to create "generic plan" with regard to current bind values. What's wrong with that approach? I don't understand how this is supposed to work. Say you already have a plan which looks like this: Seq Scan on foo (cost=0.00..100.00

Re: [HACKERS] extend pgbench expressions with functions

Hello Michaël, ISTM that if pgbench is to be stopped, the simplest option is just to abort with a nicer error message from the get*Rand function, there is no need to change the function signature and transfer the error management upwards. That's fine to me, as long as the solution is elegant.

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

On 13/01/16 14:12, Pavel Stehule wrote: so the strategy - if cost of generic plan is less than some MAGIC CONSTANT (can be specified by GUC), then use generic plan. Elsewhere use a custom plan everytime. It allow to controll the plan reusing. When MAGIC CONSTANT = 0 .. use custom plan everytime,

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

Please, read my suggestion again. TL;DR: I suggest to create "generic plan" with regard to current bind values. What's wrong with that approach? Vladimir -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpr

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

2016-01-13 11:44 GMT+01:00 Vladimir Sitnikov : > >the implementation is simply - but it hard to design some really general > - it is task for UI > > Can you please rephrase? > Sorry - It is task for artifical inteligency > > Current design is "if the cost of a generic plan is less than the one >

Re: [HACKERS]WIP: Covering + unique indexes.

13.01.2016 04:47, David Rowley : On 13 January 2016 at 06:47, Jeff Janes > wrote: Why is omit_opclass a separate patch? If the included columns now never participate in the index ordering, shouldn't it be an inherent property of the main patch that yo

Re: [HACKERS] Fwd: [JDBC] Re: 9.4-1207 behaves differently with server side prepared statements compared to 9.2-1102

>the implementation is simply - but it hard to design some really general - it >is task for UI Can you please rephrase? Current design is "if the cost of a generic plan is less than the one of a custom plan+replan, prefer generic". I think that is wrong. "Generic plan" misunderestimates a cost

Re: [HACKERS] ExecGather() + nworkers

On Mon, Jan 11, 2016 at 9:16 AM, Amit Kapila wrote: > On Mon, Jan 11, 2016 at 3:14 AM, Peter Geoghegan wrote: > > > > On Sun, Jan 10, 2016 at 9:13 AM, Robert Haas wrote: > > >> I'm not sure why the test for nworkers following the > > >> LaunchParallelWorkers() call doesn't look like this, though