Re: [HACKERS] Unable to test parallel aggregate/joins in Postgres beta 2

On Thu, Jul 14, 2016 at 7:09 PM, Haribabu Kommi wrote: > On Thu, Jul 14, 2016 at 11:16 PM, Madusudanan.B.N > wrote: > > > > 3) Is there any kind of toggle to enable parallel aggregate/join feature > ? > > > > I am able to generate parallel

Re: [HACKERS] pg_xlogdump bad error msg?

On Mon, Jul 11, 2016 at 5:20 PM, Andres Freund wrote: > On 2016-07-11 13:36:37 +0200, Magnus Hagander wrote: > > When you don't specify a start segment to pg_xlogdump, you get: > > > > pg_xlogdump: no start log position given in range mode. > > > > > > What is "range mode",

Re: [HACKERS] Unable to test parallel aggregate/joins in Postgres beta 2

On Thu, Jul 14, 2016 at 11:16 PM, Madusudanan.B.N wrote: > > 3) Is there any kind of toggle to enable parallel aggregate/join feature ? > I am able to generate parallel plan, The parallel plan may be costly in your query compared to other scans, because of which it is

Re: [HACKERS] Hang issue when COPY to/from an unopened FIFO

Kenan Yao writes: > -- mkfifo /tmp/test.dat # bash > copy pg_class to '/tmp/test.dat'; > -- try pg_cancel_backend or pg_terminate_backend from other sessions This does not seem like a supported case to me. I see few if any reasons to want to do that rather than doing

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

Dilip Kumar writes: > On Thu, Jul 14, 2016 at 1:37 PM, Amit Langote > wrote: >> Can we say that pg_get_indexdef() has "side-effects" because it can error >> like this? Shouldn't such a function be marked *volatile*? Because if I >> do so by

Re: [HACKERS] Unable to test parallel aggregate/joins in Postgres beta 2

Hi, On Thu, 2016-07-14 at 18:46 +0530, Madusudanan.B.N wrote: > I installed postgres beta 2 using the RPM provided here for Cent OS. > > Based on this article I was trying to set *max_parallel_degree*, but > postgres complained that there was no such config variable. But I was able > to set

Re: [HACKERS] pg_xlogdump follow into the future

On 14 July 2016 at 07:46, Magnus Hagander wrote: > Currently, if you run pg_xlogdump with -f, you have to specify an end > position in an existing file, or if you don't it will only follow until the > end of the current file. > > That seems like an oversight - if you specify

Re: [HACKERS] Unable to test parallel aggregate/joins in Postgres beta 2

++ Pg hackers On Thu, Jul 14, 2016 at 7:18 PM, Madusudanan.B.N wrote: > > > On Thu, Jul 14, 2016 at 6:59 PM, Devrim Gündüz wrote: > >> >> Hi, >> >> On Thu, 2016-07-14 at 18:46 +0530, Madusudanan.B.N wrote: >> > I installed postgres beta 2 using the

Re: [HACKERS] Hang issue when COPY to/from an unopened FIFO

* Tom Lane (t...@sss.pgh.pa.us) wrote: > Kenan Yao writes: > > -- mkfifo /tmp/test.dat # bash > > copy pg_class to '/tmp/test.dat'; > > -- try pg_cancel_backend or pg_terminate_backend from other sessions > > This does not seem like a supported case to me. I see few if any

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

Ashutosh Bapat writes: > Exactly, for a rare scenario, should we be penalizing large number of plans > or just continue to use a previously prepared plan when an optimal plan has > become available because of changed condition. I would choose second over > the

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

Tom Lane writes: > Dilip Kumar writes: >> So I think changing the view definition and calling this function on >> indexrelid will remove the error. So I think >> correct fix is to change view definition, as I proposed in above patch. [...] > We've dealt with similar issues

Re: [HACKERS] Improving executor performance

Andres Freund writes: > Having expression evaluation and slot deforming as a series of simple > sequential steps, instead of complex recursive calls, would also make it > fairly straightforward to optionally just-in-time compile those. I don't think that JIT becomes easier by this change.

Re: [HACKERS] Improving executor performance

On 2016-07-14 20:04:03 +0200, Andreas Seltenreich wrote: > Andres Freund writes: > > > Having expression evaluation and slot deforming as a series of simple > > sequential steps, instead of complex recursive calls, would also make it > > fairly straightforward to optionally just-in-time compile

Re: [HACKERS] pg_xlogdump follow into the future

On 2016-07-14 13:46:23 +0200, Magnus Hagander wrote: > Currently, if you run pg_xlogdump with -f, you have to specify an end > position in an existing file, or if you don't it will only follow until the > end of the current file. That's because specifying a file explicitly says that you only want

Re: [HACKERS] Reviewing freeze map code

On 2016-07-14 18:12:42 +0530, Amit Kapila wrote: > Just thinking out loud. If we set HEAP_XMAX_LOCK_ONLY during update, > then won't it impact the return value of > HeapTupleHeaderIsOnlyLocked(). It will start returning true whereas > otherwise I think it would have returned false due to

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

I wrote: > I concur with Etsuro-san's dislike for hasForeignJoin; that flag is > underspecified and doesn't convey nearly enough information. I do not > think a uses_user_mapping flag is much better. ISTM what should happen is > that any time we decide to push down a foreign join, we should

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

Andreas Seltenreich writes: > Tom Lane writes: >> We've dealt with similar issues in places like pg_relation_size() by >> making the functions return NULL instead of throwing an error for an >> unmatched argument OID. > Note that Michael Paquier sent a patch implementing this

Re: [HACKERS] Improving executor performance

Andres Freund writes: > The problem is that the previous form has a lot of ad-hoc analysis > strewn in. The interesting part is getting rid of all that. That's what > the new ExecInitExpr2() does. The target form can be both evaluated more > efficiently in the dispatch manner in the patch, and

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

On Thu, Jul 14, 2016 at 5:10 PM, Etsuro Fujita wrote: > On 2016/07/13 18:00, Ashutosh Bapat wrote: > >> To fix the first, I'd like to propose (1) replacing the existing >> has_foreign_join flag in the CachedPlan data structure with a new >> flag, say

[HACKERS] Re[2]: [HACKERS] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

>>The results above are not really fair, pgbouncer.ini was a bit different on >>Ubuntu host (application_name_add_host was disabled). Here are the right >>results with exactly the same configuration: >> >>OS PostgreSQL version TPS Avg. latency >>RHEL 6 9.4 44898 1.425 ms >>RHEL 6 9.5 26199 2.443

[HACKERS] Unable to test parallel aggregate/joins in Postgres beta 2

Hi, I installed postgres beta 2 using the RPM provided here for Cent OS. Based on this article I was trying to set *max_parallel_degree*, but postgres complained that there was no such config variable. But I was able to set *max_parallel_workers_per_gather* and test things for parallel

Re: [HACKERS] Unable to test parallel aggregate/joins in Postgres beta 2

Oops, the hyper links got removed. RPM repo - http://yum.postgresql.org/repopackages.php#pg96 Blog article followed for parallel aggregation testing - http://blog.2ndquadrant.com/parallel-aggregate/ On Thu, Jul 14, 2016 at 6:46 PM, Madusudanan.B.N wrote: > Hi, > > I

Re: [HACKERS] Reviewing freeze map code

On Thu, Jul 14, 2016 at 11:36 AM, Andres Freund wrote: > Hi, > > Master does > /* temporarily make it look not-updated */ > oldtup.t_data->t_ctid = oldtup.t_self; > here, and as is the wal record won't reflect that, because: > static void >

Re: [HACKERS] sslmode=require fallback

On Thu, Jul 14, 2016 at 11:27 PM, Tom Lane wrote: > Greg Stark writes: > > Well what's required to "configure SSL" anyways? If you don't have > > verify-ca set or a root canal cert present then the server just needs a > > certificate -- any certificate. Can

Re: [HACKERS] Improving executor performance

Andres Freund writes: > On 2016-07-14 23:03:10 +0200, Andreas Seltenreich wrote: >> That's the plan, yes. I'm sorry there's no publishable code yet on the >> the postgres side of things. Using libFirm[1], the plan is to. > > Why libfirm? - It has a more modern IR than LLVM (they're catching up

Re: [HACKERS] sslmode=require fallback

On 13 Jul 2016 9:28 pm, "Tom Lane" wrote: > > Robert Haas writes: > > On Wed, Jul 13, 2016 at 3:16 PM, Tom Lane wrote: > >> Robert Haas writes: > >>> Suppose we changed the default to "require". How crazy

Re: [HACKERS] sslmode=require fallback

Greg Stark writes: > Well what's required to "configure SSL" anyways? If you don't have > verify-ca set or a root canal cert present then the server just needs a > certificate -- any certificate. Can the server just cons one up on demand > (or server startup or initdb)? Hmm, good

Re: [HACKERS] sslmode=require fallback

Magnus Hagander writes: > On Thu, Jul 14, 2016 at 11:27 PM, Tom Lane wrote: >> Also, we could offer a switch to turn it off if necessary, with the >> understanding that non-Unix-socket connections can be expected to fail >> if user doesn't install a cert.

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On Fri, Jul 15, 2016 at 3:20 AM, Tom Lane wrote: > Andreas Seltenreich writes: >> Tom Lane writes: >>> We've dealt with similar issues in places like pg_relation_size() by >>> making the functions return NULL instead of throwing an error for an >>>

Re: [HACKERS] One process per session lack of sharing

Hi > It's mostly working, but there are workloads where it > fails badly - and competing database products survive a number of > scenarios where we just fall on our face. > So, I actually think it would be a good idea to think about this. Just to think. http://www.tiobe.com/tiobe_index The

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

On 2016/07/13 18:00, Ashutosh Bapat wrote: To fix the first, I'd like to propose (1) replacing the existing has_foreign_join flag in the CachedPlan data structure with a new flag, say uses_user_mapping, that indicates whether a cached plan uses any user mapping regardless of

[HACKERS] pg_xlogdump follow into the future

Currently, if you run pg_xlogdump with -f, you have to specify an end position in an existing file, or if you don't it will only follow until the end of the current file. That seems like an oversight - if you specify -f with no end position, it should follow "into the future" for any new files

Re: [HACKERS] Hash Indexes

On Wed, Jun 22, 2016 at 8:48 PM, Robert Haas wrote: > On Wed, Jun 22, 2016 at 5:14 AM, Amit Kapila wrote: >> We can do it in the way as you are suggesting, but there is another thing >> which we need to consider here. As of now, the patch tries to

Re: [HACKERS] One process per session lack of sharing

Craig>That moves work further away from the DB, which has its own costs, and isn't something you're likely to be happy with if you're looking at things like optimising PL/PgSQL with a bytecode compiler. But it's the best we have right now. What if JVM was started within a background worker? Then

Re: [HACKERS] A Modest Upgrade Proposal

On 14 July 2016 at 03:06, Robert Haas wrote: > Physical replication has > the same issue. Users don't want to configure archive_command and > wal_keep_segments and max_wal_senders and wal_level and set up an > archive and create recovery.conf on the standby. They want

Re: [HACKERS] Reviewing freeze map code

Hi, So I'm generally happy with 0001, baring some relatively minor adjustments. I am however wondering about one thing: On 2016-07-11 23:51:05 +0900, Masahiko Sawada wrote: > diff --git a/src/backend/access/heap/heapam.c > b/src/backend/access/heap/heapam.c > index 57da57a..e7cb8ca 100644 > ---

Re: [HACKERS] A Modest Upgrade Proposal

On 14 July 2016 at 03:26, Robert Haas wrote: > On Fri, Jul 8, 2016 at 5:47 AM, Craig Ringer > wrote: > >> DDL is our standard way of getting things into the system catalogs. > >> We have no system catalog metadata that is intended to be populated by

Re: [HACKERS] One process per session lack of sharing

On 14 July 2016 at 14:28, Vladimir Sitnikov wrote: > Craig>That moves work further away from the DB, which has its own costs, > and isn't something you're likely to be happy with if you're looking at > things like optimising PL/PgSQL with a bytecode compiler. But

Re: [HACKERS] pgbench - allow to store select results into variables

Hello Tom, SELECT 1, 2 \; SELECT 3; \into one two three Yeah, that's seriously nasty action-at-a-distance in my view. I'd be okay with SELECT 1, 2 \into one two SELECT 3 \into three ISTM that is not the same, because then you would have two queries (over the network) instead of one, so

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On 2016/07/14 16:08, Dilip Kumar wrote: > Query: > postgres=# select * from pg_catalog.pg_indexes where indexdef is not null; > ERROR: cache lookup failed for index 2619 > > If we see the plan for the same: > --- > Nested Loop Left Join >Join Filter:

[HACKERS] Issue in pg_catalog.pg_indexes view definition

While running sqlsmith tool, I saw some cache lookup failure issues reported, While investigating those issues, I found one strange reason, and I feel It's a bug in pg code. Query: postgres=# select * from pg_catalog.pg_indexes where indexdef is not null; ERROR: cache lookup failed for index

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On 2016/07/14 17:07, Amit Langote wrote: > Can we say that pg_get_indexdef() has "side-effects" because it can error > like this? Shouldn't such a function be marked *volatile*? Because if I > do so by updating pg_proc, the plan changes (perhaps) to a safe one in > this context: Didn't mean to

Re: [HACKERS] unexpected psql "feature"

Consider a throttled read-only load composed of 3 random selects, several rounds (so.sql) vs one (so2.sql): > pgbench -h server -T 100 -P 1 --rate 200 -c 2 -f so.sql -f so2.sql SQL script 1: so.sql - latency average = 1.878 ms SQL script 2: so2.sql - latency average = 1.089 ms There is 0.8

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On Thu, Jul 14, 2016 at 1:37 PM, Amit Langote wrote: > Can we say that pg_get_indexdef() has "side-effects" because it can error > like this? Shouldn't such a function be marked *volatile*? Because if I > do so by updating pg_proc, the plan changes (perhaps) to

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On Thu, Jul 14, 2016 at 5:29 PM, Dilip Kumar wrote: > > On Thu, Jul 14, 2016 at 1:40 PM, Michael Paquier > wrote: >> >> -pg_get_indexdef(I.oid) AS indexdef >> +pg_get_indexdef(X.indexrelid) AS indexdef >> Fixing it this way looks

Re: [HACKERS] unexpected psql "feature"

Hello David, Why would you choose to execute "SELECT 1 \; SELECT 2;" instead of "SELECT 1; SELECT 2;"​ in a setup where the behavior of both strings is identical? Or, rather, how would they differ? The answer is that at the protocol level the first one is one network round trip with the

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On Thu, Jul 14, 2016 at 12:38 PM, Dilip Kumar wrote: > I am not sure what should be the correct fix for this problem. > > I think even if we try to call this function on index oid > *pg_get_indexdef(*x.indexrelid*) *AS indexdef, problem will not be > solved, because both

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On Thu, Jul 14, 2016 at 4:59 PM, Dilip Kumar wrote: > I was wrong, Actually If we change the view and call function on > x.indexrelid, It will fix the issue, because pg_get_indexdef(x.indexrelid) > is non equal clause and of course will not fall in same equivalence class.

Re: [HACKERS] Issue in pg_catalog.pg_indexes view definition

On Thu, Jul 14, 2016 at 1:40 PM, Michael Paquier wrote: > -pg_get_indexdef(I.oid) AS indexdef > +pg_get_indexdef(X.indexrelid) AS indexdef > Fixing it this way looks like a good idea to me to bypass those cache > lookup errors caused by non-index

[HACKERS] Hang issue when COPY to/from an unopened FIFO

Hi devs, I came across a hang issue when COPY to a FIFO file, because the FIFO is not opened for read on the other end. The backtrace from master branch is like: #0 0x00332ccc6c30 in __open_nocancel () from /lib64/libc.so.6 #1 0x00332cc6b693 in __GI__IO_file_open () from

Re: [HACKERS] unexpected psql "feature"

On 14 July 2016 at 00:12, Tom Lane wrote: > I wonder > whether promoting \; to a recognized and documented behavior would > allow us to get away with converting -c strings to normal parsing > behavior, as was discussed and then rejected on compatibility grounds > not too long

Re: [HACKERS] One process per session lack of sharing

Hi > On Tue, Jul 12, 2016 at 9:18 AM, Tom Lane wrote: >> amatv...@bitec.ru writes: >>> Is there any plan to implement "session per thread" or "shared >>> sessions between thread"? >>... >> so >> there's not that much motivation to do a ton of work inside the database

Re: [HACKERS] Bug in to_timestamp().

2016-07-14 11:05 GMT+02:00 Artur Zakirov : > On 23.06.2016 21:02, Tom Lane wrote: > >> Robert Haas writes: >> >>> On Thu, Jun 23, 2016 at 1:40 PM, Tom Lane wrote: >>> At the very least I'd want to see a thought-through

Re: [HACKERS] Bug in to_timestamp().

On 23.06.2016 21:02, Tom Lane wrote: Robert Haas writes: On Thu, Jun 23, 2016 at 1:40 PM, Tom Lane wrote: At the very least I'd want to see a thought-through proposal that addresses all three of these interrelated points: * what should a space in

Re: [HACKERS] Improving executor performance

On 2016-07-14 23:03:10 +0200, Andreas Seltenreich wrote: > That's the plan, yes. I'm sorry there's no publishable code yet on the > the postgres side of things. Using libFirm[1], the plan is to. Why libfirm? It seems to only have x86 and sparc backends, and no windows support? > 1.

Re: [HACKERS] Hang issue when COPY to/from an unopened FIFO

On 2016-07-14 10:20:42 -0400, Tom Lane wrote: > Kenan Yao writes: > > -- mkfifo /tmp/test.dat # bash > > copy pg_class to '/tmp/test.dat'; > > -- try pg_cancel_backend or pg_terminate_backend from other sessions > > This does not seem like a supported case to me. I see few if

Re: [HACKERS] [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

On 2016-06-13 21:58:30 +0300, Vladimir Borodin wrote: > > > 13 июня 2016 г., в 0:51, Andres Freund написал(а): > > > > Hi Vladimir, > > > > Thanks for these reports. > > > > On 2016-06-13 00:42:19 +0300, Vladimir Borodin wrote: > >> perf report -g -i pg9?_all.data

Re: [HACKERS] [PERFORM] 9.4 -> 9.5 regression with queries through pgbouncer on RHEL 6

On 2016-07-04 16:30:51 +0300, Vladimir Borodin wrote: > > > 13 июня 2016 г., в 21:58, Vladimir Borodin написал(а): > > > >> > >> 13 июня 2016 г., в 0:51, Andres Freund >> > написал(а): > >> > >> Hi Vladimir, > >> > >> Thanks for

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

On 2016/07/15 3:49, Tom Lane wrote: On reflection, it seems to me that we've gone wrong by tying planning to equality of user mappings at all, and the best way to get out of this is to not do that. Instead, let's insist that a join can be pushed down only if the checkAsUser fields of the

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

Etsuro Fujita writes: > One thing I'm not sure about is: should we insist that a join can be > pushed down only if the checkAsUser fields of the relevant RTEs are > equal in the case where user mappings are meaningless to the FDW, like > file_fdw? If we add a

Re: [HACKERS] trivia: cancel{,l}{ed,ing,ation}

On 5/16/16 12:37 PM, Robert Haas wrote: > I never know how to spell the various derived forms of "cancel" that > come up from time to time. My spell checker actually flags the non-US forms as "wrong", and since I regularly spell check error messages and documentation, the only deviations are in C

Re: [HACKERS] Improving executor performance - tidbitmap

On Wed, Jul 13, 2016 at 8:06 PM, Andres Freund wrote: > I've quickly hacked up an alternative linear addressing hashtable > implementation. And the improvements are quite remarkable. > > Example Query: > EXPLAIN ANALYZE SELECT SUM(l_extendedprice) FROM lineitem WHERE

Re: [HACKERS] Reviewing freeze map code

On 2016-07-13 23:06:07 -0700, Andres Freund wrote: > won't enter the branch, because HEAP_XMAX_LOCK_ONLY won't be set. Which > will leave t_ctid and HEAP_HOT_UPDATED set differently on the master and > standby / after crash recovery. I'm failing to see any harmful > consequences right now, but

Re: [HACKERS] pgbench - allow to store select results into variables

Hello Tom, Yeah, that's seriously nasty action-at-a-distance in my view. I'd be okay with SELECT 1, 2 \into one two SELECT 3 \into three After giving it some thoughts, it could work on compound commands if \into does not close the current sql command. Something like: SELECT 1, 2 ;

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

On Fri, Jul 15, 2016 at 12:19 AM, Tom Lane wrote: > I wrote: > > I concur with Etsuro-san's dislike for hasForeignJoin; that flag is > > underspecified and doesn't convey nearly enough information. I do not > > think a uses_user_mapping flag is much better. ISTM what should

Re: [HACKERS] Unable to test parallel aggregate/joins in Postgres beta 2

On Thu, Jul 14, 2016 at 7:22 PM, Madusudanan.B.N wrote: > > > On Thu, Jul 14, 2016 at 7:09 PM, Haribabu Kommi > wrote: >> >> On Thu, Jul 14, 2016 at 11:16 PM, Madusudanan.B.N >> wrote: >> > >> > 3) Is there any kind

Re: [HACKERS] sslmode=require fallback

On 7/13/16 4:11 PM, Robert Haas wrote: > On Thu, Jun 16, 2016 at 3:42 AM, Magnus Hagander wrote: >> You would think so. >> >> The default mode of "prefer" is ridiculous in a lot of ways. If you are >> using SSL in any shape or form you should simply not use "prefer". That's

Re: [HACKERS] Oddity in handling of cached plans for FDW queries

On Thu, Jul 14, 2016 at 10:02 PM, Tom Lane wrote: > Ashutosh Bapat writes: > > Exactly, for a rare scenario, should we be penalizing large number of > plans > > or just continue to use a previously prepared plan when an optimal plan > has > >

Re: [HACKERS] Improving executor performance - tidbitmap

On 2016-07-14 20:41:21 -0700, Peter Geoghegan wrote: > On Wed, Jul 13, 2016 at 8:06 PM, Andres Freund wrote: > > I've quickly hacked up an alternative linear addressing hashtable > > implementation. And the improvements are quite remarkable. > > > > Example Query: > > EXPLAIN

Re: [HACKERS] Improving executor performance - tidbitmap

On Thu, Jul 14, 2016 at 8:45 PM, Andres Freund wrote: > Brin indexes IIRC always end up using tidbitmap.c, so the benefits > should be there as well ;) Right. Might the improvement be even more pronounced, though? I'm not sure how a BRIN index with a suitable