Re: [HACKERS] BUG #14245: Segfault on weird to_tsquery

On Tue, Jul 12, 2016 at 05:11:32PM -0400, Tom Lane wrote: > David Kellum writes: > > On Tue, Jul 12, 2016 at 12:42 PM, Tom Lane wrote: > >> Note that while crashing is certainly not good, the pre-9.6 behavior > >> can hardly be called correct either. What happened to 'a'? > > > 'a' is a stopwor

[HACKERS] Re: Document that vacuum can't truncate if old_snapshot_threshold >= 0

On Wed, Jul 13, 2016 at 02:14:06PM -0700, Andres Freund wrote: > That appears to not be mentioned in a comment, the commit message or the > the docs. I think this definitely needs to be prominently documented. [Action required within 72 hours. This is a generic notification.] The above-described

Re: [HACKERS] dumping database privileges broken in 9.6

On Sat, Jul 09, 2016 at 12:55:33AM -0400, Stephen Frost wrote: > * Noah Misch (n...@leadboat.com) wrote: > > This PostgreSQL 9.6 open item is past due for your status update. Kindly > > send > > a status update within 24 hours, and include a date for your subsequent > > status > > update. Refer

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 happen > is > > that

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 > > become available because of changed condition. I woul

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 ; \int

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 physical/logical correlation perform

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 d

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 ANALYZE SELECT SUM(l_

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 (l_shipdate > >= '1995-01-01'

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 >> really the only answ

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] 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 mechanism to let us know that t

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 relevan

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 of toggle to enable parallel aggregate/join feature >> > ? >> > >> >> I am able

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 >>> unmatched argument OID. > >> Note that Michael P

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. > If we do it we should also ensure it's

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 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 the server just cons one up on demand

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 old "snake oil c

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 would that be? > > >> You mean, aside from the fact that it breaks every single installat

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. Automatical

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 qui

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 these reports. > >> > >> On 2016-06-13 00

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 recor

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 >/tmp/pg9?_perf_report.txt >

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 any reasons > to wa

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 in_progr

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] 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 in another > thread:

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 th

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. Constr

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 in places like pg_relat

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 first as it doesn't make things worse

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 updating pg_proc, the plan changes (perhaps) to a safe

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 reasons > to want to d

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 copy-to-program or copy-to-c

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 -f with no end positi

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 plan, The parallel plan may be costly > in your query co

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 RPM provided here for Cent OS. >> > >> > Based

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", and is there any other

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 not selecting the parallel p

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 *max

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 installed postgres beta 2 u

[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 sequential

[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

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 > heap_xlog_lock(XLogReader

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 uses_user_mapping, that indicates

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 pl

[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 tha

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 wheth

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 finish the >> split if it finds split-in-progres

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 ago. People who nee

[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 /lib64/libc.so.

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 proposal that addresses all three of these interrelated points: >>>

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 the format match * what should a non-space, n

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 >> to solve it there.

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 like a good idea to me to bypass those cache >> loo

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 relations. Now I think that you ne

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 a safe one in > this context: >

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 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 w

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. -pg_get_indexdef

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: (t

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 y

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 will fall in same equival

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 serv

[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 261