Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 10:53 AM, Alvaro Herrera wrote: > Robert Haas wrote: >> On Wed, Jul 6, 2016 at 6:06 PM, Andres Freund wrote: >> > Hm. We can't easily do that in the back-patched version; because a >> > standby won't know to check for the flag

Re: [HACKERS] EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

2016-07-07 Thread Amit Kapila
On Thu, Jul 7, 2016 at 7:37 PM, Tom Lane wrote: > Amit Kapila writes: >> On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao wrote: >>> I found $SUBJECT while trying to test parallel queries. Is this a bug? > > Presumably the

Re: [HACKERS] EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

2016-07-07 Thread Tom Lane
Amit Kapila writes: > On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao wrote: >> I found $SUBJECT while trying to test parallel queries. Is this a bug? Presumably the instrumentation data needed for that is not getting returned from the worker to the

Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Robert Haas
On Wed, Jul 6, 2016 at 6:06 PM, Andres Freund wrote: > Hm. We can't easily do that in the back-patched version; because a > standby won't know to check for the flag . That's kinda ok, since we > don't yet need to clear all-visible yet at that point of > heap_update. But that

Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Alvaro Herrera
Robert Haas wrote: > On Wed, Jul 6, 2016 at 6:06 PM, Andres Freund wrote: > > Hm. We can't easily do that in the back-patched version; because a > > standby won't know to check for the flag . That's kinda ok, since we > > don't yet need to clear all-visible yet at that point

Re: [HACKERS] EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 10:07 AM, Tom Lane wrote: > Amit Kapila writes: >> On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao wrote: >>> I found $SUBJECT while trying to test parallel queries. Is this a bug? > > Presumably the

[HACKERS] Disable WAL completely - Performance and Persistency research

2016-07-07 Thread Netanel Katzburg
Hi All, As part of my masters at TAU, I'm currently conducting some research regarding new persistent memory technology. I'm using PG for this research and would like to better understand some of the performance bottlenecks. For this reason I'm trying to disable the WAL completely, using some

Re: [HACKERS] Disable WAL completely - Performance and Persistency research

2016-07-07 Thread Michael Paquier
On Thu, Jul 7, 2016 at 5:01 PM, Netanel Katzburg wrote: > 1. Disable the WAL by not writing anything to the xlog directory. I don't > care about recovery/fault tolerance or PITR/ replication etc at the moment. > I'm aware that the WAL and checkpoint are bind in many ways and

Re: [HACKERS] Header and comments describing routines in incorrect shape in visibilitymap.c

2016-07-07 Thread Alvaro Herrera
Michael Paquier wrote: > Hi all, > > I just bumped into a couple of things in visibilitymap.c: > - visibilitymap_clear clears all bits of a visibility map, its header > comment mentions the contrary > - The header of visibilitymap.c mentions correctly "a bit" when > referring to setting them, but

Re: [HACKERS] Parallel query and temp_file_limit

2016-07-07 Thread Robert Haas
On Tue, Jul 5, 2016 at 3:59 PM, Peter Geoghegan wrote: > On Tue, Jul 5, 2016 at 12:58 PM, Tom Lane wrote: >> Perhaps we could change the wording of temp_file_limit's description >> from "space that a session can use" to "space that a process can use" >> to

Re: [HACKERS] EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

2016-07-07 Thread Fujii Masao
On Fri, Jul 8, 2016 at 12:55 AM, Tom Lane wrote: > Robert Haas writes: >> On Thu, Jul 7, 2016 at 10:07 AM, Tom Lane wrote: >>> Presumably the instrumentation data needed for that is not getting >>> returned from the worker to the

Re: [HACKERS] EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

2016-07-07 Thread Tom Lane
Robert Haas writes: > On Thu, Jul 7, 2016 at 10:07 AM, Tom Lane wrote: >> Presumably the instrumentation data needed for that is not getting >> returned from the worker to the leader. > Yes. > ... > I'm not sure about the rest of you, but I'd kind of

Re: [HACKERS] Header and comments describing routines in incorrect shape in visibilitymap.c

2016-07-07 Thread Tom Lane
Alvaro Herrera writes: > Regarding the first hunk, I don't like these INTERFACE sections too > much; they get seriously outdated over the time and aren't all that > helpful anyway. See the one on heapam.c for example. I'd rather get > rid of that one instead of

Re: [HACKERS] PARALLEL SAFE/UNSAFE question

2016-07-07 Thread Tom Lane
Satoshi Nagayasu writes: > I was trying writing my own parallel aggregation functions on 9.6beta2. > During that, we found a bit confusing behavior with SAFE/UNSAFE options. > Once a PARALLEL UNSAFE function f1_unsafe() is wrapped by > a PARALLEL SAFE function f1_safe_unsafe(),

Re: [HACKERS] Cluster on NAS and data center.

2016-07-07 Thread Robert Haas
On Tue, Jul 5, 2016 at 7:46 AM, Krzysztof Kaczkowski wrote: > Thanks to emails, we have achieved what we wanted. This is what we’ve done: > > Compilation (for 32 bit version, initdb with locale=C): > > CFLAGS="-mx32 -fexcess-precision=standard -O2" > CXXFLAGS="-mx32" > >

Re: [HACKERS] can we optimize STACK_DEPTH_SLOP

2016-07-07 Thread Tom Lane
I found out that pmap can give much more fine-grained results than I was getting before, if you give it the -x flag and then pay attention to the "dirty" column rather than the "nominal size" column. That gives a reliable indication of how much stack space the process ever actually touched, with

Re: [HACKERS] Don't include MMAP DSM's transient files in basebackup

2016-07-07 Thread Andres Freund
On 2016-07-07 08:49:18 +0100, Magnus Hagander wrote: > On Thursday, July 7, 2016, Andres Freund wrote: > > > On 2016-07-06 16:46:53 +0300, Oskari Saarenmaa wrote: > > > The files are not useful when restoring a backup and would be > > automatically > > > deleted on startup

Re: [HACKERS] Bug in batch tuplesort memory CLUSTER case (9.6 only)

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 3:34 AM, Noah Misch wrote: >> Are you satisfied that I have adequately described steps to reproduce? > > I can confirm that (after 62 minutes) your test procedure reached SIGSEGV > today and then completed successfully with your patch. Thanks for

Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Andres Freund
On 2016-07-07 14:01:05 -0400, Robert Haas wrote: > On Thu, Jul 7, 2016 at 1:58 PM, Andres Freund wrote: > > On 2016-07-07 10:37:15 -0400, Robert Haas wrote: > >> On Wed, Jul 6, 2016 at 6:06 PM, Andres Freund wrote: > >> > Hm. We can't easily do that in the

Re: [HACKERS] Bug in batch tuplesort memory CLUSTER case (9.6 only)

2016-07-07 Thread Peter Geoghegan
On Thu, Jul 7, 2016 at 10:51 AM, Robert Haas wrote: > Thanks for testing. I've committed this patch, breaking off one > unrelated bit of into a separate commit. Thank you. To be clear, I still intend to follow up with a CLUSTER external sort test case, as outlined to

[HACKERS] Issue with bgworker, SPI and pgstat_report_stat

2016-07-07 Thread Julien Rouhaud
Hello, While investigating on a bloat issue with a colleague, we found that if a bgworker executes some queries with SPI, the statistic changes will never be reported, since pgstat_report_stat() is only called in regular backends. In our case, the bgworker is the only process inserting and

Re: [HACKERS] asynchronous and vectorized execution

2016-07-07 Thread Robert Haas
On Wed, Jul 6, 2016 at 3:29 AM, Kyotaro HORIGUCHI wrote: > This seems to be a good opportunity to show this patch. The > attched patch set does async execution of foreignscan > (postgres_fdw) on the Robert's first infrastructure, with some > modification. Cool.

Re: [HACKERS] Issue with bgworker, SPI and pgstat_report_stat

2016-07-07 Thread Andres Freund
On 2016-07-07 14:04:36 -0400, Robert Haas wrote: > On Thu, Jul 7, 2016 at 1:52 PM, Julien Rouhaud > wrote: > > Should a bgworker modifing data have to call pgstat_report_stat() to > > avoid this problem? I don't find any documentation suggesting it, and it > > seems

Re: [HACKERS] Partition-wise join for join between (declaratively) partitioned tables

2016-07-07 Thread Robert Haas
On Wed, Jun 15, 2016 at 3:25 AM, Ashutosh Bapat wrote: > Amit Langote is working on supporting declarative partitioning in PostgreSQL > [1]. I have started working on supporting partition-wise join. This mail > describes very high level design and some insight

Re: [HACKERS] MVCC overheads

2016-07-07 Thread Simon Riggs
On 7 July 2016 at 17:45, Pete Stevenson wrote: > Hi postgresql hackers - > > I would like to find some analysis (published work, blog posts) on the > overheads affiliated with the guarantees provided by MVCC isolation. More > specifically, assuming the current workload

Re: [HACKERS] gettimeofday is at the end of its usefulness?

2016-07-07 Thread Andres Freund
On 2016-07-07 14:43:31 -0400, Robert Haas wrote: > On Tue, Jun 14, 2016 at 4:27 PM, Jim Nasby wrote: > > Semi-related: someone (Robert I think) recently mentioned investigating > > "vectorized" executor nodes, where multiple tuples would be processed in one > > shot. If

[HACKERS] MVCC overheads

2016-07-07 Thread Pete Stevenson
Hi postgresql hackers - I would like to find some analysis (published work, blog posts) on the overheads affiliated with the guarantees provided by MVCC isolation. More specifically, assuming the current workload is CPU bound (as opposed to IO) what is the CPU overhead of generating the WAL,

Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 1:58 PM, Andres Freund wrote: > On 2016-07-07 10:37:15 -0400, Robert Haas wrote: >> On Wed, Jul 6, 2016 at 6:06 PM, Andres Freund wrote: >> > Hm. We can't easily do that in the back-patched version; because a >> > standby won't know

Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 2:04 PM, Andres Freund wrote: >> Yeah, that's true, but I'm having a bit of trouble imagining exactly >> we end up with corruption that actually matters. I guess a torn page >> could do it. > > I think Noah pointed out a bad scenario: If we crash after

Re: [HACKERS] gettimeofday is at the end of its usefulness?

2016-07-07 Thread Robert Haas
On Tue, Jun 14, 2016 at 4:27 PM, Jim Nasby wrote: > Semi-related: someone (Robert I think) recently mentioned investigating > "vectorized" executor nodes, where multiple tuples would be processed in one > shot. If we had that presumably the explain penalty would be a

Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Andres Freund
On 2016-07-07 10:37:15 -0400, Robert Haas wrote: > On Wed, Jul 6, 2016 at 6:06 PM, Andres Freund wrote: > > Hm. We can't easily do that in the back-patched version; because a > > standby won't know to check for the flag . That's kinda ok, since we > > don't yet need to clear

Re: [HACKERS] primary_conninfo missing from pg_stat_wal_receiver

2016-07-07 Thread Robert Haas
On Thu, Jun 30, 2016 at 10:24 AM, Alvaro Herrera wrote: > Also, actually, I see no reason for the conninfo to be shown differently > regardless of a connection being already established. If we show the > conninfo that the server is trying to use, it might be easier to >

Re: [HACKERS] [BUGS] BUG #14230: Wrong timeline returned by pg_stop_backup on a standby

2016-07-07 Thread Michael Paquier
On Thu, Jul 7, 2016 at 12:57 AM, Marco Nenciarini wrote: > After further analysis, the issue is that we retrieve the starttli from > the ControlFile structure, but it was using ThisTimeLineID when writing > the backup label. > > I've attached a very simple patch

Re: [HACKERS] Bug in batch tuplesort memory CLUSTER case (9.6 only)

2016-07-07 Thread Peter Geoghegan
On Thu, Jul 7, 2016 at 12:34 AM, Noah Misch wrote: >> How do you feel about adding testing to tuplesort.c not limited to >> hitting this bug (when Valgrind memcheck is used)? > > Sounds great, but again, not in the patch fixing this bug. I'll work on a minimal CLUSTER testcase

Re: [HACKERS] Don't include MMAP DSM's transient files in basebackup

2016-07-07 Thread Magnus Hagander
On Thursday, July 7, 2016, Andres Freund wrote: > On 2016-07-06 16:46:53 +0300, Oskari Saarenmaa wrote: > > The files are not useful when restoring a backup and would be > automatically > > deleted on startup anyway. Also deleted an out-of-date comment in dsm.c. > > No

Re: [HACKERS] Header and comments describing routines in incorrect shape in visibilitymap.c

2016-07-07 Thread Michael Paquier
On Thu, Jul 7, 2016 at 4:41 PM, Kyotaro HORIGUCHI wrote: > So, the 'pinned' is not necessary here or should be added also to > _clear. I think the former is preferable since it is already > written in the comments for the functions and seems to be a bit > too

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Kyotaro HORIGUCHI
At Thu, 7 Jul 2016 16:48:57 +0900, Amit Langote wrote in <3649f1e4-ac74-841e-6ae5-cfe0022aa...@lab.ntt.co.jp> > On 2016/07/07 16:26, Michael Paquier wrote: > > On Thu, Jul 7, 2016 at 3:59 PM, Amit Langote > > wrote: > >> While

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Kyotaro HORIGUCHI
I wrote a bit wrong thing. At Thu, 07 Jul 2016 17:20:52 +0900 (Tokyo Standard Time), Kyotaro HORIGUCHI wrote in <20160707.172052.213690356.horiguchi.kyot...@lab.ntt.co.jp> > > > To be honest, I have always found that this restriction was hard to > > > justify

Re: [HACKERS] Reviewing freeze map code

2016-07-07 Thread Masahiko Sawada
Than you for reviewing! On Thu, Jul 7, 2016 at 7:06 AM, Andres Freund wrote: > On 2016-07-05 23:37:59 +0900, Masahiko Sawada wrote: >> diff --git a/src/backend/access/heap/heapam.c >> b/src/backend/access/heap/heapam.c >> index 57da57a..fd66527 100644 >> ---

Re: [HACKERS] Header and comments describing routines in incorrect shape in visibilitymap.c

2016-07-07 Thread Kyotaro HORIGUCHI
At Thu, 7 Jul 2016 16:48:00 +0900, Michael Paquier wrote in > On Thu, Jul 7, 2016 at 4:41 PM, Kyotaro HORIGUCHI > wrote: > > So, the 'pinned' is not necessary here or

Re: [HACKERS] Header and comments describing routines in incorrect shape in visibilitymap.c

2016-07-07 Thread Kyotaro HORIGUCHI
Hello, At Wed, 6 Jul 2016 11:28:19 +0900, Michael Paquier wrote in > I just bumped into a couple of things in visibilitymap.c: > - visibilitymap_clear clears all bits of a visibility map, its header

[HACKERS] EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

2016-07-07 Thread Fujii Masao
Hi, I found $SUBJECT while trying to test parallel queries. Is this a bug? In not parallel mode, EXPLAIN ANALYZE reports the information about Sort Method as follows. =# EXPLAIN ANALYZE SELECT * FROM pgbench_accounts ORDER BY bid;

Re: [HACKERS] [PATCH] Set sgml-basic-offset to 1 in .dir-locals.el

2016-07-07 Thread Dagfinn Ilmari Mannsåker
Peter Eisentraut writes: > On 7/6/16 4:52 AM, Dagfinn Ilmari Mannsåker wrote: >> This keeps the indentation consistent when editing the documentation >> using Emacs. > > Unfortunately, sgml-basic-offset is not a "safe" variable, so if we put > it into

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Amit Langote
On 2016/05/19 17:34, Amit Langote wrote: > Currently in HEAD and 9.6, one can issue a non-exclusive backup on > standby, so this is OK: > > select pg_is_in_recovery(); > pg_is_in_recovery > --- > t > (1 row) > > select pg_start_backup('sby-bkp-test', 'f', 'f'); >

Re: [HACKERS] Don't include MMAP DSM's transient files in basebackup

2016-07-07 Thread Michael Paquier
On Wed, Jul 6, 2016 at 10:46 PM, Oskari Saarenmaa wrote: > The files are not useful when restoring a backup and would be automatically > deleted on startup anyway. The same could be said about pg_snapshots. -- Michael -- Sent via pgsql-hackers mailing list

Re: [HACKERS] Don't include MMAP DSM's transient files in basebackup

2016-07-07 Thread Andres Freund
Hi, On 2016-07-06 16:46:53 +0300, Oskari Saarenmaa wrote: > The files are not useful when restoring a backup and would be automatically > deleted on startup anyway. Also deleted an out-of-date comment in dsm.c. No arguments against the change. But I am wondering how you ran into this - the mmap

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Michael Paquier
On Thu, Jul 7, 2016 at 3:59 PM, Amit Langote wrote: > While reading the thread "BUG #14230: Wrong timeline returned by > pg_stop_backup on a standby", I came to know that ThisTimelineId is > invalid on standby. And because pg_xlogfile_name_offset() uses the same >

Re: [HACKERS] Bug in batch tuplesort memory CLUSTER case (9.6 only)

2016-07-07 Thread Noah Misch
This PostgreSQL 9.6 open item is past due for a status update. On Sat, Jul 02, 2016 at 08:47:20PM -0700, Peter Geoghegan wrote: > On Sat, Jul 2, 2016 at 3:17 PM, Robert Haas wrote: > > In the interest of clarity, I was not intending to say that there > > should be a

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Amit Langote
On 2016/07/07 16:26, Michael Paquier wrote: > On Thu, Jul 7, 2016 at 3:59 PM, Amit Langote > wrote: >> While reading the thread "BUG #14230: Wrong timeline returned by >> pg_stop_backup on a standby", I came to know that ThisTimelineId is >> invalid on standby. And

Re: [HACKERS] EXPLAIN ANALYZE for parallel query doesn't report the SortMethod information.

2016-07-07 Thread Amit Kapila
On Thu, Jul 7, 2016 at 1:23 PM, Fujii Masao wrote: > Hi, > > I found $SUBJECT while trying to test parallel queries. Is this a bug? > > > In not parallel mode, EXPLAIN ANALYZE reports the information about > Sort Method as follows. > > =# EXPLAIN ANALYZE SELECT * FROM

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Kyotaro HORIGUCHI
Hello, At Thu, 07 Jul 2016 17:26:15 +0900, Kyotaro HORIGUCHI wrote in <20160707.172615.232806225.horiguchi.kyot...@lab.ntt.co.jp> > > As the result, this relaxation adds more good than bad and I > > agree to this. > > > > Addition to that, I'd like to propose

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Simon Riggs
On 7 July 2016 at 08:26, Michael Paquier wrote: > On Thu, Jul 7, 2016 at 3:59 PM, Amit Langote > wrote: > > While reading the thread "BUG #14230: Wrong timeline returned by > > pg_stop_backup on a standby", I came to know that

Re: [HACKERS] strange explain in upstream - subplan 1 twice - is it bug?

2016-07-07 Thread Robert Haas
On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule wrote: > Hi > > When I tested some queries, I found strange plan > > postgres=# explain analyze select s.nazev, o.nazev, o.pocet_obyvatel from > (select nazev, array(select id from obce_pocet_obyvatel where okresy.id = >

Re: [HACKERS] Comment typo in _readExtensibleNode()

2016-07-07 Thread Robert Haas
On Fri, May 6, 2016 at 3:26 AM, Amit Langote wrote: > Attached fixes a minor typo comment in _readExtensibleNode(). > > s/skip: extnodename/skip :extnodename/g > > I was confused at first as to why there is a skip extnodename on one line > and get extnodename right

Re: [HACKERS] strange explain in upstream - subplan 1 twice - is it bug?

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 4:13 PM, Pavel Stehule wrote: > 2016-07-07 21:57 GMT+02:00 Robert Haas : >> On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule >> wrote: >> > Hi >> > >> > When I tested some queries, I found strange plan

[HACKERS] SELECT DISTINCT never uses an index?

2016-07-07 Thread Bill Moran
Take the following table as an example: CREATE TABLE grue ( id SERIAL PRIMARY KEY, size VARCHAR(255) ); CREATE INDEX grue_size ON grue(size); Now insert approximately eleventy jillion rows, but ensure that there are only about 20 distinct values for size. SELECT DISTINCT size FROM grue;

Re: [HACKERS] MVCC overheads

2016-07-07 Thread Pete Stevenson
Hi Simon - Thanks for the note. I think it's fair to say that I didn't provide enough context, so let me try and elaborate on my question. I agree, MVCC is a benefit. The research angle here is about enabling MVCC with hardware offload. Since I didn't explicitly mention it, the offload I refer

Re: [HACKERS] strange explain in upstream - subplan 1 twice - is it bug?

2016-07-07 Thread Pavel Stehule
2016-07-07 21:57 GMT+02:00 Robert Haas : > On Wed, Jun 1, 2016 at 7:29 AM, Pavel Stehule > wrote: > > Hi > > > > When I tested some queries, I found strange plan > > > > postgres=# explain analyze select s.nazev, o.nazev, o.pocet_obyvatel from > >

Re: [HACKERS] Floating point comparison inconsistencies of the geometric types

2016-07-07 Thread Robert Haas
On Wed, Jun 1, 2016 at 10:27 AM, Tom Lane wrote: > As I understand it, the key problem is that tests like "is point on line" > would basically never succeed except in the most trivial cases, because of > roundoff error. That's not very nice, and it might cascade to larger >

[HACKERS] \timing interval

2016-07-07 Thread Corey Huinker
\timing is great. \timing is helpful. \timing has made me really good at mentally estimating numbers modulo 360. Wouldn't it be great if we had a way of printing timing in more human friendly formats? Attached is a patch that allows the following (new/interesting bits in bold): # \timing

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Robert Haas
On Mon, May 16, 2016 at 8:52 PM, David Fetter wrote: > In light of the above, it is perfectly reasonable to require, at least > temporarily, setting up duplicate storage, or another node. > > I am aware that some cases exist where this is not possible, but I > don't think we

Re: [HACKERS] SELECT DISTINCT never uses an index?

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 4:56 PM, Bill Moran wrote: > Take the following table as an example: > > CREATE TABLE grue ( > id SERIAL PRIMARY KEY, > size VARCHAR(255) > ); > CREATE INDEX grue_size ON grue(size); > > Now insert approximately eleventy jillion rows, but ensure

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Robert Haas
On Mon, May 16, 2016 at 9:22 PM, Alvaro Herrera wrote: > David Fetter wrote: >> As a relatively (to our users) minor course correction, I would like >> to propose the following: > >> - Develop a logical upgrade path as a part of the (Yay! Sexy!) logical >> replication

Re: [HACKERS] SELECT DISTINCT never uses an index?

2016-07-07 Thread Tom Lane
Robert Haas writes: > The alternative worth considering is presumably something like: > GroupAggregate > -> Index Only Scan on grue_size > Scanning an entire index in order is pretty expensive, but it seems > possible that this could be faster than the Seq Scan,

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Simon Riggs
On 7 July 2016 at 21:01, Robert Haas wrote: > On Mon, May 16, 2016 at 9:22 PM, Alvaro Herrera > wrote: > > David Fetter wrote: > >> As a relatively (to our users) minor course correction, I would like > >> to propose the following: > > > >> -

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Joshua D. Drake
On 07/07/2016 01:10 PM, Robert Haas wrote: On Mon, May 16, 2016 at 8:52 PM, David Fetter wrote: In light of the above, it is perfectly reasonable to require, at least temporarily, setting up duplicate storage, or another node. pg_upgrade does that, kinda. I'd like to have

Re: [HACKERS] delta relations in AFTER triggers

2016-07-07 Thread Robert Haas
On Fri, May 13, 2016 at 2:37 PM, Kevin Grittner wrote: > On Fri, May 13, 2016 at 1:02 PM, David Fetter wrote: >> On Thu, Jan 22, 2015 at 02:41:42PM +, Kevin Grittner wrote: > >>> [ideas on how to pass around references to ephemeral relations] >> >>

Re: [HACKERS] SELECT DISTINCT never uses an index?

2016-07-07 Thread Thomas Munro
On Fri, Jul 8, 2016 at 9:49 AM, Robert Haas wrote: > On Thu, Jul 7, 2016 at 4:56 PM, Bill Moran wrote: >> SELECT DISTINCT size FROM grue; >> >> Always does a seq scan on Postgres 9.5.2. (Yes, I know we're >> a patch behind, the upgrade is on the

Re: [HACKERS] \timing interval

2016-07-07 Thread Peter Geoghegan
On Thu, Jul 7, 2016 at 2:52 PM, Corey Huinker wrote: > Wouldn't it be great if we had a way of printing timing in more human > friendly formats? Yes, it would. I've thought about doing this myself. So, +1 to the idea from me. -- Peter Geoghegan -- Sent via

Re: [HACKERS] Issue with bgworker, SPI and pgstat_report_stat

2016-07-07 Thread Michael Paquier
On Fri, Jul 8, 2016 at 3:06 AM, Andres Freund wrote: > On 2016-07-07 14:04:36 -0400, Robert Haas wrote: >> On Thu, Jul 7, 2016 at 1:52 PM, Julien Rouhaud >> wrote: >> > Should a bgworker modifing data have to call pgstat_report_stat() to >> > avoid

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Simon Riggs
On 7 July 2016 at 21:10, Robert Haas wrote: > pg_upgrade does that, kinda. I'd like to have something better, but > in the absence of that, I think it's quite wrong to think about > deprecating it, even if we had logical replication fully integrated > into core today.

Re: [HACKERS] SELECT DISTINCT never uses an index?

2016-07-07 Thread David Rowley
On 8 July 2016 at 09:49, Robert Haas wrote: > We're probably missing a few tricks on queries of this type. If the > index-traversal machinery had a mechanism to skip quickly to the next > distinct value, that could be used here: walk up the btree until you > find a page

Re: [HACKERS] MVCC overheads

2016-07-07 Thread Simon Riggs
On 7 July 2016 at 20:50, Pete Stevenson wrote: > Hi Simon - > > Thanks for the note. I think it's fair to say that I didn't provide enough > context, so let me try and elaborate on my question. > > I agree, MVCC is a benefit. The research angle here is about enabling

Re: [HACKERS] [PATCH] add option to pg_dumpall to exclude tables from the dump

2016-07-07 Thread Robert Haas
On Fri, Apr 22, 2016 at 6:42 AM, Juergen Hannappel wrote: > A new option -T --exlude-table for pg_dumpall. This option is then > passed through to the pg_dump which really does the work. > This feature can be used to exclude large tables that are known not > to

Re: [HACKERS] Header and comments describing routines in incorrect shape in visibilitymap.c

2016-07-07 Thread Michael Paquier
On Fri, Jul 8, 2016 at 1:18 AM, Tom Lane wrote: > Alvaro Herrera writes: >> Regarding the first hunk, I don't like these INTERFACE sections too >> much; they get seriously outdated over the time and aren't all that >> helpful anyway. See the one on

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Michael Paquier
On Thu, Jul 7, 2016 at 6:26 PM, Simon Riggs wrote: > The behaviour of that function is defined in backbranches, so I suggest we > should not alter that now. Hm.. > What we can do is have another function that makes it clearer that the > answer is variable. >

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Simon Riggs
On 8 July 2016 at 00:48, Joshua D. Drake wrote: > On 07/07/2016 01:10 PM, Robert Haas wrote: > >> On Mon, May 16, 2016 at 8:52 PM, David Fetter wrote: >> >>> In light of the above, it is perfectly reasonable to require, at least >>> temporarily, setting

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Joshua D. Drake
On 07/07/2016 05:14 PM, Simon Riggs wrote: I would much rather see more brain power put into pg_upgrade or in place upgrades than logical replication (as a upgrade solution). Why is that? First, let me state that I don't have a problem with logical replication as an upgrade

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Simon Riggs
On 8 July 2016 at 01:47, Joshua D. Drake wrote: > * Long running transaction > And of course you can't run any transactions at all during pg_upgrade, not just long running ones. -- Simon Riggshttp://www.2ndQuadrant.com/

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 7:15 PM, Simon Riggs wrote: > Yes, I ran the unconference session. It was a shame you weren't able to stay > for the whole discussion. I thought I sat through, at least, most of it, but you barely gave anyone else a chance to talk, which kind of

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Simon Riggs
On 8 July 2016 at 02:41, Robert Haas wrote: > On Thu, Jul 7, 2016 at 7:15 PM, Simon Riggs wrote: > > Yes, I ran the unconference session. It was a shame you weren't able to > stay > > for the whole discussion. > > I thought I sat through, at least,

Re: [HACKERS] can we optimize STACK_DEPTH_SLOP

2016-07-07 Thread Tom Lane
I wrote: > Based on these numbers, I'd have no fear of reducing STACK_DEPTH_SLOP > to 256KB on x86_64. It would sure be good to check things on some > other architectures, though ... I went to the work of doing the same test on a PPC Mac: 182 Stack [ 8192K/ 40K] 25

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Simon Riggs
On 8 July 2016 at 01:47, Joshua D. Drake wrote: > It is true that something like pg_logical doesn't suffer from those three > things but it does suffer from others: > > * No DDL - Agreed, not "required" but certainly a very nice > feature. > > * Lack of

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Simon Riggs
On 8 July 2016 at 02:41, Robert Haas wrote: > > Personally, I'm in the group of people that don't see the need for DDL. > > There are already many successful features that don't utilize DDL, such > as > > backup, advisory locks and some features that use DDL that don't

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Tom Lane
Simon Riggs writes: > On 8 July 2016 at 02:41, Robert Haas 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 >> any means other than DDL. > pg_am

Re: [HACKERS] A Modest Upgrade Proposal

2016-07-07 Thread Alvaro Herrera
Tom Lane wrote: > Simon Riggs writes: > > pg_am has existed for decades without supporting DDL > > That argument has been obsoleted by events ;-) ... and in any case, the > reason we went without CREATE ACCESS METHOD for so long was not that we > encouraged "INSERT INTO

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 5:26 AM, Simon Riggs wrote: > The behaviour of that function is defined in backbranches, so I suggest we > should not alter that now. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via

Re: [HACKERS] \timing interval

2016-07-07 Thread Pavel Stehule
2016-07-08 0:13 GMT+02:00 Peter Geoghegan : > On Thu, Jul 7, 2016 at 2:52 PM, Corey Huinker > wrote: > > Wouldn't it be great if we had a way of printing timing in more human > > friendly formats? > > Yes, it would. I've thought about doing this myself.

Re: [HACKERS] pg_xlogfile_name_offset() et al and recovery

2016-07-07 Thread Simon Riggs
On 8 July 2016 at 00:43, Michael Paquier wrote: > On Thu, Jul 7, 2016 at 6:26 PM, Simon Riggs wrote: > > The behaviour of that function is defined in backbranches, so I suggest > we > > should not alter that now. > > Hm.. > > > What we can do is

Re: [HACKERS] Re: GiST optimizing memmoves in gistplacetopage for fixed-size updates [PoC]

2016-07-07 Thread Andrew Borodin
One more thing came to my mind: WAL records done by the GiST before patch will corrupt GiST after patch if replayed. Is it a problem? May be it's prevented on some higher level? Best regards, Andrey Borodin, Octonica & Ural Federal University. 2016-07-06 22:11 GMT+05:00 Andrew Borodin

Re: [HACKERS] Re: GiST optimizing memmoves in gistplacetopage for fixed-size updates [PoC]

2016-07-07 Thread Michael Paquier
On Fri, Jul 8, 2016 at 2:00 PM, Andrew Borodin wrote: (Please top-post on threads, this is annoying) > One more thing came to my mind: > WAL records done by the GiST before patch will corrupt GiST after > patch if replayed. > Is it a problem? May be it's prevented on some

Re: [HACKERS] Issue with bgworker, SPI and pgstat_report_stat

2016-07-07 Thread Robert Haas
On Thu, Jul 7, 2016 at 1:52 PM, Julien Rouhaud wrote: > While investigating on a bloat issue with a colleague, we found that if > a bgworker executes some queries with SPI, the statistic changes will > never be reported, since pgstat_report_stat() is only called in