Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

After examining the benchmark design - I see we are probably not being helped by the repeated insertion of keys all of form 'userxxx' leading to some page splitting. However your index rebuild gets you from 5 to 3 GB - does that really help performance significantly? regards Mark On

[HACKERS] [PATCH] COPY vs \copy HINT

Hi all I see this sort of question quite a bit: http://stackoverflow.com/q/38903811/398670 where the user wonders why COPY gemeenten FROM 'D:\CBS_woningcijfers_2014.csv' DELIMITER ';' CSV fails with ERROR: could not open file "D:\CBS_woningcijfers_2014.csv" for reading: No such file or

Re: [HACKERS] [PATCH] COPY vs \copy HINT

On 12 August 2016 at 16:34, Christoph Berg wrote: > > postgres=# COPY x TO '/root/nopermissions'; > > ERROR: could not open file "/root/nopermissions" for writing: Permission > > denied > > HINT: Paths for COPY are on the PostgreSQL server, not the client. You > may > > want

Re: [HACKERS] new autovacuum criterion for visible pages

On Fri, Aug 12, 2016 at 9:01 AM, Tom Lane wrote: > Michael Paquier writes: >> In short, autovacuum will need to scan by itself the VM of each >> relation and decide based on that. > > That seems like a worthwhile approach to pursue. The VM is

Re: [HACKERS] [PATCH] COPY vs \copy HINT

Re: Craig Ringer 2016-08-12 > I think we should emit a HINT here, something like: > > ERROR: could not open file "D:\CBS_woningcijfers_2014.csv" for reading: No > such file or directory' > HINT: Paths for COPY are on the

Re: [HACKERS] [Patch] New psql prompt substitution %r (m = master, r = replica)

Thank you everyone for your replies! I did some research and apparently the is no need in any patch. As David pointed out what I did could be easily done using \gset: ``` $ cat ~/.psqlrc select (case when pg_is_in_recovery() then 'r' else 'm' end) as mor \gset \set PROMPT1 '%p (%:mor:) =# '

[HACKERS] Fwd: [BUG] Print timing statistics of trigger execution under turned off timing option of EXPLAIN ANALYZE

Hello, hackers! At this moment EXPLAIN ANALYZE with turned off timing option after execution query that expires trigger prints time of total execution of trigger function: postgres=# EXPLAIN (ANALYZE, timing false) insert INTO foo values(101, ''); QUERY

[HACKERS] Relocation of tuple between release and re-acquire of tuple lock

Hackers, I'm now exploring code working with heap tuples. The following code in heap_update() catch my eyes. if (DoesMultiXactIdConflict((MultiXactId) xwait, infomask, > *lockmode)) > { > LockBuffer(buffer, BUFFER_LOCK_UNLOCK); > /* acquire tuple lock, if necessary */ >

Re: [HACKERS] Relocation of tuple between release and re-acquire of tuple lock

On Fri, Aug 12, 2016 at 3:15 PM, Alexander Korotkov < a.korot...@postgrespro.ru> wrote: > I'm now exploring code working with heap tuples. The following code > in heap_update() catch my eyes. > > if (DoesMultiXactIdConflict((MultiXactId) xwait, infomask, >> *lockmode)) >> { >> LockBuffer(buffer,

[HACKERS] pg_dump with tables created in schemas created by extensions

Hi, About a month or two ago I reported a pg_dump bug regarding tables (and other objects) created inside a schema from an extension. Objects created by the extensions are not dumped, as they will be created once again with the CREATE EXTENSION call, but and other objects which might live inside

Re: [HACKERS] Add hint for function named "is"

Greg Stark writes: > On Fri, Aug 12, 2016 at 7:40 PM, Tom Lane wrote: >> pointing out that "SELECT 42 ISNULL" is now ambiguous. Since ISNULL >> is nonstandard, maybe dropping support for it would be feasible. > Isn't ISNULL one of the lexical tricks we used

[HACKERS] Is tuplesort_heap_siftup() a misnomer?

Doesn't tuplesort_heap_siftup() actually shift-down? The Wikipedia article on heaps [1] lists "shift-down" (never "sift down", FWIW) as a common operation on a heap: "shift-down: move a node down in the tree, similar to shift-up; used to restore heap condition after deletion or replacement."

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: Tom> But we need to be clear in the documentation about what this Tom> property actually means. My objection to having it answer at the Tom> index or column level is basically that that encourages confusion Tom> as to what it means. OK.

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

On Sat, Aug 13, 2016 at 1:18 AM, Andrew Gierth wrote: > > Hmm? The code in _bt_findsplitloc and _bt_checksplitloc doesn't seem to > agree with this. > > (Inserting on the high leaf page is a special case, which is where the > fillfactor logic kicks in; that's why

[HACKERS] Pluggable storage

Many have expressed their interest in this topic, but I haven't seen any design of how it should work. Here's my attempt; I've been playing with this for some time now and I think what I propose here is a good initial plan. This will allow us to write permanent table storage that works

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

On Fri, Aug 12, 2016 at 8:13 PM, Andrew Gierth wrote: > No, because as the pages split, they fill more slowly (because there are > now more pages). So on average in a large randomly filled index, pages > spend more time nearer 50% full than 100% full. This is easy to

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

> "Greg" == Greg Stark writes: >> No, because as the pages split, they fill more slowly (because there >> are now more pages). So on average in a large randomly filled index, >> pages spend more time nearer 50% full than 100% full. This is easy >> to demonstrate by

Re: [HACKERS] Server crash due to SIGBUS(Bus Error) when trying to access the memory created using dsm_create().

On Sat, Aug 13, 2016 at 8:26 AM, Thomas Munro wrote: > On Sat, Aug 13, 2016 at 2:08 AM, Tom Lane wrote: >> amul sul writes: >>> When I am calling dsm_create on Linux using the POSIX DSM implementation >>> can succeed, but

Re: [HACKERS] Add hint for function named "is"

On Fri, Aug 12, 2016 at 7:40 PM, Tom Lane wrote: > pointing out that "SELECT 42 ISNULL" is now ambiguous. Since ISNULL > is nonstandard, maybe dropping support for it would be feasible. Isn't ISNULL one of the lexical tricks we used to effectively give bison two token

Re: [HACKERS] new autovacuum criterion for visible pages

On 12/08/16 15:15, Peter Eisentraut wrote: > On 8/11/16 11:59 AM, Jeff Janes wrote: >> Insertions and HOT-updates clear vm bits but don't increment the >> counters that those existing parameters are compared to. >> >> Also, the relationship between number of updated/deleted rows and the >> number

[HACKERS] Server crash due to SIGBUS(Bus Error) when trying to access the memory created using dsm_create().

Hi All, When I am calling dsm_create on Linux using the POSIX DSM implementation can succeed, but result in SIGBUS when later try to access the memory.  This happens because of my system does not have enough shm space &  current allocation in dsm_impl_posix does not allocate disk blocks[1]. I

Re: [HACKERS] new autovacuum criterion for visible pages

On 8/11/16 11:59 AM, Jeff Janes wrote: > Insertions and HOT-updates clear vm bits but don't increment the > counters that those existing parameters are compared to. > > Also, the relationship between number of updated/deleted rows and the > number of hint-bits cleared can be hard to predict due

Re: [HACKERS] Server crash due to SIGBUS(Bus Error) when trying to access the memory created using dsm_create().

amul sul writes: > When I am calling dsm_create on Linux using the POSIX DSM implementation can > succeed, but result in SIGBUS when later try to access the memory.  This > happens because of my system does not have enough shm space &  current > allocation in

Re: [HACKERS] Add hint for function named "is"

On Thu, Aug 11, 2016 at 10:54 PM, Tom Lane wrote: > I think what is happening > in the trouble case is that since IS has lower precedence than Op, the > grammar decides it ought to resolve || as a postfix operator, and then > it effectively has > ('x' ||) IS ... >

Re: [HACKERS] Add hint for function named "is"

Jim Nasby writes: > Is there a place in the error reporting path where we'd still have > access to the 'is' token, and have enough control to look for a relevant > function? No. The grammar can't assume that it's being run inside a transaction (consider parsing START

[HACKERS] Why is box <-> point missing, and box <-> box not indexable?

point <-> point, circle <-> point and polygon <-> point all exist as orderable-by-operator operators (in fact they are the only ones by default). But there's no box <-> point operator at all, and no index support for box <-> box. Was this intentional, or just a strange oversight? -- Andrew

Re: [HACKERS] Why is box <-> point missing, and box <-> box not indexable?

Andrew Gierth writes: > point <-> point, circle <-> point and polygon <-> point all exist as > orderable-by-operator operators (in fact they are the only ones by > default). But there's no box <-> point operator at all, and no index > support for box <-> box. > Was

[HACKERS] [parallel query] random server crash while running tpc-h query on power2

Hi All, Recently while running tpc-h queries on postgresql master branch, I am noticed random server crash. Most of the time server crash coming while turn tpch query number 3 - (but its very random). Call Stack of server crash: (gdb) bt #0 0x102aa9ac in ExplainNode

Re: [HACKERS] Add hint for function named "is"

On Fri, Aug 12, 2016 at 9:40 AM, Greg Stark wrote: > On Thu, Aug 11, 2016 at 10:54 PM, Tom Lane wrote: > >> I think what is happening >> in the trouble case is that since IS has lower precedence than Op, the >> grammar decides it ought to resolve || as a

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

You're right. Reindex improves the performance of the benchmark workloads dramatically. I'm gathering results and will announce them. But I think we should notice that the results before Reindexing is poorer than MongoDB. It seems that this is because of Btree bloating (not exact expression). The

Re: [HACKERS] Server crash due to SIGBUS(Bus Error) when trying to access the memory created using dsm_create().

No segfault during dsm_create,  mmap returns  the memory address  which is inaccessible.  Let me see how can I disable kernel overcommit behaviour, but  IMHO,  we should prevent ourselves from crashing,  shouldn't we?  Regards,  

Re: [HACKERS] TODO item: Implement Boyer-Moore searching in LIKE queries

> Having said that, I've had a bee in my bonnet for a long time about > removing per-row setup cost for repetitive regex matches, and > whatever infrastructure that needs would work for this too. What are the per-row setup costs for regex matches? I looked at `regexp.c` and saw: ``` /* * We

Re: [HACKERS] Add hint for function named "is"

Robert Haas writes: > On Fri, Aug 12, 2016 at 9:40 AM, Greg Stark wrote: >> I wonder whether it's really worth keeping postfix operators. They >> seem to keep causing these kinds of headaches and I wonder how much >> the grammar tables would be simplified by

Re: [HACKERS] Server crash due to SIGBUS(Bus Error) when trying to access the memory created using dsm_create().

On Fri, Aug 12, 2016 at 1:55 PM, amul sul wrote: > No segfault during dsm_create, mmap returns the memory address which is > inaccessible. > > Let me see how can I disable kernel overcommit behaviour, but IMHO, we > should prevent ourselves from crashing, shouldn't we?

Re: [HACKERS] TODO item: Implement Boyer-Moore searching in LIKE queries

Karan Sikka writes: >> Having said that, I've had a bee in my bonnet for a long time about >> removing per-row setup cost for repetitive regex matches, and >> whatever infrastructure that needs would work for this too. > What are the per-row setup costs for regex matches?

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

On Fri, Aug 12, 2016 at 1:40 AM, Mark Kirkwood wrote: > After examining the benchmark design - I see we are probably not being > helped by the repeated insertion of keys all of form 'userxxx' leading > to some page splitting. But shouldn't that still leave us

Re: [HACKERS] Add hint for function named "is"

On Fri, Aug 12, 2016 at 12:57 PM, Tom Lane wrote: > Robert Haas writes: >> On Fri, Aug 12, 2016 at 9:40 AM, Greg Stark wrote: >>> I wonder whether it's really worth keeping postfix operators. They >>> seem to keep causing these kinds of

Re: [HACKERS] Add hint for function named "is"

Robert Haas writes: > Half a percent for two productions is not bad, but I think the real > win would be in removing ambiguity from the grammar. We get periodic > complaints about the fact that things like "SELECT 3 cache" don't work > because cache is an unreserved

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

So I'm tidying up and doing docs for the next version of this patch, but here for comment is the current functionality: select cap, pg_indexam_has_property(a.oid, cap) as "AM", pg_index_has_property('onek_hundred'::regclass, cap) as "Index",

Re: [HACKERS] Add hint for function named "is"

I wrote: > Robert Haas writes: >> I think I experimented with this a while ago and found that even after >> removing postfix operators there was at least one other grammar >> problem that prevented us from accepting ColLabel there. I gave up >> and didn't dig further, but

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > "Tom" == Tom Lane writes: > Tom> +1 mostly, but I'm a bit bemused by can_order and can_backward > Tom> having different scopes --- how come? > That's where they were in the previous list, a couple of messages up in > the

Re: [HACKERS] Server crash due to SIGBUS(Bus Error) when trying to access the memory created using dsm_create().

On Sat, Aug 13, 2016 at 2:08 AM, Tom Lane wrote: > amul sul writes: >> When I am calling dsm_create on Linux using the POSIX DSM implementation can >> succeed, but result in SIGBUS when later try to access the memory. This >> happens because of my

Re: [HACKERS] Fwd: [BUG] Print timing statistics of trigger execution under turned off timing option of EXPLAIN ANALYZE

maksim writes: > postgres=# EXPLAIN (ANALYZE, timing false) insert INTO foo > values(101, ''); > QUERY PLAN > -- > Insert on foo (cost=0.00..0.01 rows=1

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> distance_orderable now returns true/false depending on the opclass, >> not just on the amcanorderbyop field. In order to do this, I've >> added an optional amproperty function to the AM api, which if it >> exists, gets first dibs on all

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

> "Tom" == Tom Lane writes: >> This table shows what properties are exposed at the AM-wide level, >> the per-index level and the per-column level. Tom> +1 mostly, but I'm a bit bemused by can_order and can_backward Tom> having different scopes --- how come? That's

Re: [HACKERS] No longer possible to query catalogs for index capabilities?

Andrew Gierth writes: > This table shows what properties are exposed at the AM-wide level, the > per-index level and the per-column level. +1 mostly, but I'm a bit bemused by can_order and can_backward having different scopes --- how come? Also, not sure about

Re: [HACKERS] Parallel tuplesort, partitioning, merging, and the future

On Wed, Aug 10, 2016 at 4:54 PM, Peter Geoghegan wrote: > On Wed, Aug 10, 2016 at 11:59 AM, Robert Haas wrote: >> My view on this - currently anyway - is that we shouldn't conflate the >> tuplesort with the subsequent index generation, but that we should

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?

Robert Haas writes: > Let's introduce a new variant of SET that only affects the lexical > scope of the function to which it is attached, and then do what you > said. That would be full of win, because actually I think in nearly > every case that's the behavior people

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?

On Fri, Aug 12, 2016 at 3:22 PM, Tom Lane wrote: > Robert Haas writes: >> Let's introduce a new variant of SET that only affects the lexical >> scope of the function to which it is attached, and then do what you >> said. That would be full of win,

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

On Fri, Aug 12, 2016 at 3:03 PM, Robert Haas wrote: > On Thu, Aug 11, 2016 at 8:34 AM, David G. Johnston > wrote: > > I don't have a fundamental issue with saying "when turning auto-commit on > > you are also requesting that the open

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?

On Thu, Mar 10, 2016 at 11:48 AM, Tom Lane wrote: > Robert Haas writes: >> Hmm. The meaning of funcs.inline depends on the search_path, not just >> during dump restoration but all the time. So anything uses it under a >> different search_path setting

Re: [HACKERS] Surprising behaviour of \set AUTOCOMMIT ON

On Thu, Aug 11, 2016 at 8:34 AM, David G. Johnston wrote: > I don't have a fundamental issue with saying "when turning auto-commit on > you are also requesting that the open transaction, if there is one, is > committed immediately." I'm more inclined to think an error

Re: [HACKERS] Btree Index on PostgreSQL and Wiredtiger (MongoDB3.2)

> "Jeff" == Jeff Janes writes: Jeff> But shouldn't that still leave us with a 75% full index, rather Jeff> than slightly over 50% full? Average is usually about 67%-70%. (For capacity estimation I always assume 66% for a non-sequentially-filled btree.) Jeff> The

Re: [HACKERS] Is there a way around function search_path killing SQL function inlining?

Robert Haas writes: > On Fri, Aug 12, 2016 at 3:22 PM, Tom Lane wrote: >> Hm. I think that sounds a lot easier than it actually is. As an example, >> this would mean that we'd want such a search_path setting to apply during >> parse analysis of a