Re: [HACKERS] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file

On Tue, Jun 9, 2015 at 3:29 PM, Amit Kapila wrote: > On Tue, Jun 9, 2015 at 10:56 AM, Fujii Masao wrote: >> >> On Tue, Jun 9, 2015 at 1:04 PM, Amit Kapila >> wrote: >> > On Tue, Jun 9, 2015 at 9:09 AM, Fujii Masao >> > wrote: >> >> >> >> On Tue, May 12, 2015 at 10:42 PM, Andrew Dunstan >> >> w

Re: [HACKERS] Typo fix loged vs logged.

On Wed, Jun 10, 2015 at 1:10 PM, David Rowley wrote: > The attached fixes a small typo in a comment. Pushed. Thanks! -- Fujii Masao -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] reaper should restart archiver even on standby

On Tue, Jun 9, 2015 at 5:21 AM, Alvaro Herrera wrote: > Fujii Masao wrote: >> Hi, >> >> When the archiver exits, currently reaper() restarts it only while >> the postmaster state is PM_RUN. This is OK in 9.4 or before because >> the archiver could be running on that state. But in 9.5, we can set >

Re: [HACKERS] Information of pg_stat_ssl visible to all users

On Tue, Jun 9, 2015 at 10:55 PM, Michael Paquier wrote: > On Tue, Jun 9, 2015 at 3:27 PM, Magnus Hagander > wrote: > > > > On Jun 9, 2015 6:00 AM, "Michael Paquier" > wrote: > >> > >> Hi all, > >> > >> I should have noticed that before, but it happens that pg_stat_ssl > >> leaks information abo

Re: [HACKERS] Restore-reliability mode

On Wed, Jun 03, 2015 at 04:18:37PM +0200, Andres Freund wrote: > On 2015-06-03 09:50:49 -0400, Noah Misch wrote: > > Second, I would define the subject matter as "bug fixes, testing and > > review", not "restructuring, testing and review." Different code > > structures are clearest to different ha

[HACKERS] Expending the use of xlog_internal.h's macros

Hi all, While looking at the code of pg_archivecleanup.c, I noticed that there is some code present to detect if a given string has the format of a WAL segment file name or of a backup file. The recent commit 179cdd09 has introduced in xlog_internal.h a set of macros to facilitate checks of pg_xlo

[HACKERS] [idea] table partition + hash join

Hello, It might be a corner case optimization, however, it looks to me worth to share the idea and have discussion. Table partition + Hash join pushdown Hash-join logic works most effectively when inner relation can be stored within a hash table. So, it is a m

Re: [HACKERS] The Future of Aggregation

On 6/9/15 9:52 AM, Kevin Grittner wrote: Yeah, I think we want to preserve the ability of count() to have a simple state, and implement dependent aggregates as discussed in the other thread -- where (as I understood it) having sum(x), count(x), and avg(x) in a query would avoid the row-by-row wor

[HACKERS] Typo fix loged vs logged.

The attached fixes a small typo in a comment. -- David Rowley http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services diff --git a/src/include/catalog/pg_class.h b/src/include/catalog/pg_class.h index fea99c7..e526cd9

Re: [HACKERS] "could not adopt C locale" failure at startup on Windows

On Tue, Jun 09, 2015 at 12:24:02PM -0400, Tom Lane wrote: > Andres Freund writes: > > The error seems odd. The only even remotely related change between 9.4.1 > > and .2 seems to be ca325941. Could also be a build environment change. > > Yeah, my first instinct was to blame ca325941 as well, but

Re: [HACKERS] pg_archivecleanup bug (invalid filename input)

On 06/09/2015 05:54 PM, Michael Paquier wrote: Looking at the documentation what is expected is not a path to a segment file, but only a segment file name: http://www.postgresql.org/docs/devel/static/pgarchivecleanup.html So the current behavior is correct, it is actually what SetWALFileNameFor

Re: [HACKERS] The Future of Aggregation

David Rowley wrote: > On 10 June 2015 at 03:25, Alvaro Herrera wrote: > > > Kevin Grittner wrote: > > > Alvaro Herrera wrote: > > > > > > Uh, this also requires serialization and deserialization of non- > > > > finalized transition state, no? > > > > > > For that sort of optimization to incremen

Re: [HACKERS] The Future of Aggregation

On 10 June 2015 at 03:25, Alvaro Herrera wrote: > Kevin Grittner wrote: > > Alvaro Herrera wrote: > > > > Uh, this also requires serialization and deserialization of non- > > > finalized transition state, no? > > > > For that sort of optimization to incremental maintenance of > > materialized vi

Re: [HACKERS] The Future of Aggregation

On 10 June 2015 at 02:52, Kevin Grittner wrote: > David Rowley wrote: > The idea I discussed in the link in item 5 above gets around this > > problem, but it's a perhaps more surprise filled implementation > > as it will mean "select avg(x),sum(x),count(x) from t" is > > actually faster than "s

Re: [HACKERS] Aggregate Supporting Functions

On 10 June 2015 at 01:53, Kevin Grittner wrote: > David Rowley wrote: > > > 3. Add logic in the planner to look for look for supporting > > cases. With logic something along the lines of: > > > > a. Does the query have any aggregates? If not -> return; > > b. Does the query have more than 1

Re: [HACKERS] [patch] PL/Python is too lossy with floats

Peter Eisentraut writes: > On 6/1/15 5:34 AM, Marko Kreen wrote: >> Please reconsider backporting. > It's been like this forever, so I don't think it's appropriate to > backpatch this. http://www.postgresql.org/docs/devel/static/plpython-data.html states in so many words that floats are converte

Re: [HACKERS] Run pgindent now?

On Tue, Jun 9, 2015 at 09:56:13PM -0400, Robert Haas wrote: > What I really don't want to do is apply the pgindent diff somewhat > blindly, without really knowing how many cases we're improving and how > many cases we're making worse. The number of times we've run pgindent > and then realized lat

Re: [HACKERS] [patch] PL/Python is too lossy with floats

On 6/1/15 5:34 AM, Marko Kreen wrote: > On Wed, Mar 11, 2015 at 9:49 PM, Peter Eisentraut wrote: >> On 3/3/15 9:32 AM, Marko Kreen wrote: >>> PL/Python uses str(v) to convert float data, but is lossy >>> by design. Only repr(v) is guaranteed to have enough >>> precision to make floats roundtrip p

Re: [HACKERS] Run pgindent now?

On Tue, May 26, 2015 at 2:55 PM, Robert Haas wrote: > This is kind of why I think that reindenting the back branches is > unlikely to be productive: it only helps if you can get pgindent to do > the same thing on all branches, and I bet that's going to be tough. ...but having said that and though

Re: [HACKERS] Checkpoints vs restartpoints

On Wed, Jun 10, 2015 at 9:33 AM, Bruce Momjian wrote: > Ah, so even thought standbys don't have to write WAL, they are fsyncing > shared buffers. Where is the restart point recorded, in pg_controldata? > c Yep. Latest checkpoint's REDO location, or ControlFile->checkPointCopy.redo. During recover

Re: [HACKERS] pg_archivecleanup bug (invalid filename input)

On Wed, Jun 10, 2015 at 7:27 AM, Joshua D. Drake wrote: > Trying to use pg_archivecleanup as a: > > "standalone archive cleaner" > > Results in an error of: > > pg_archivecleanup: invalid filename input > Try "pg_archivecleanup --help" for more information. > > /usr/pgsql-9.4/bin/pg_archivecleanup

Re: [HACKERS] Checkpoints vs restartpoints

On Tue, Jun 9, 2015 at 05:20:23PM -0700, Jeff Janes wrote: > On Tue, Jun 9, 2015 at 4:20 PM, Thomas Munro > wrote: > > Hi > > Why do standby servers not simply treat every checkpoint as a > restartpoint?  As I understand it, setting checkpoint_timeout and > checkpoint_segments h

Re: [HACKERS] Checkpoints vs restartpoints

On Tue, Jun 9, 2015 at 4:20 PM, Thomas Munro wrote: > Hi > > Why do standby servers not simply treat every checkpoint as a > restartpoint? As I understand it, setting checkpoint_timeout and > checkpoint_segments higher on a standby server effectively instruct > standby servers to skip some check

Re: [HACKERS] Draft release notes for 9.4.4 et al

On Wed, Jun 10, 2015 at 8:41 AM, Josh Berkus wrote: > On 06/09/2015 04:38 PM, Michael Paquier wrote: >> On Wed, Jun 10, 2015 at 8:31 AM, Josh Berkus wrote: >>> Tom, all: >>> >>> First draft of the release announcement. >>> >>> Please improve/edit/correct. Thanks! >> >> Some comments: >> s/expeci

Re: [HACKERS] Draft release notes for 9.4.4 et al

On 06/09/2015 04:38 PM, Michael Paquier wrote: > On Wed, Jun 10, 2015 at 8:31 AM, Josh Berkus wrote: >> Tom, all: >> >> First draft of the release announcement. >> >> Please improve/edit/correct. Thanks! > > Some comments: > s/expecially/especially. Thanks, fixed > > This bug fix is not menti

Re: [HACKERS] Draft release notes for 9.4.4 et al

On Wed, Jun 10, 2015 at 8:31 AM, Josh Berkus wrote: > Tom, all: > > First draft of the release announcement. > > Please improve/edit/correct. Thanks! Some comments: s/expecially/especially. This bug fix is not mentioned (worth it?): Avoid deadlock between incoming sessions and CREATE/DROP DATAB

Re: [HACKERS] Draft release notes for 9.4.4 et al

Tom, all: First draft of the release announcement. Please improve/edit/correct. Thanks! -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com 2015-06-12 Update Release = The PostgreSQL Global Development Group has released an update to all supported versions of o

[HACKERS] Checkpoints vs restartpoints

Hi Why do standby servers not simply treat every checkpoint as a restartpoint? As I understand it, setting checkpoint_timeout and checkpoint_segments higher on a standby server effectively instruct standby servers to skip some checkpoints. Even with the same settings on both servers, the server

[HACKERS] The purpose of the core team

There has been some confusion by old and new community members about the purpose of the core team, and this lack of understanding has caused some avoidable problems. Therefore, the core team has written a core charter and published it on our website: http://www.postgresql.org/developer/co

[HACKERS] pg_archivecleanup bug (invalid filename input)

Hello, Trying to use pg_archivecleanup as a: "standalone archive cleaner" Results in an error of: pg_archivecleanup: invalid filename input Try "pg_archivecleanup --help" for more information. /usr/pgsql-9.4/bin/pg_archivecleanup /backups/db1/archive 0001074800B1.00015838.backup

Re: [HACKERS] Why no jsonb_exists_path()?

On 06/09/2015 05:30 PM, Andrew Dunstan wrote: On 06/09/2015 02:40 PM, Tom Lane wrote: Josh Berkus writes: I'm noticing a feature gap for JSONB operators; we have no way to do this: jsonb_col ? ARRAY['key1','key2','key3'] ... that is, there is no way for us to check for key existence in an i

Re: [HACKERS] Why no jsonb_exists_path()?

On 06/09/2015 02:40 PM, Tom Lane wrote: Josh Berkus writes: I'm noticing a feature gap for JSONB operators; we have no way to do this: jsonb_col ? ARRAY['key1','key2','key3'] ... that is, there is no way for us to check for key existence in an indexable fashion. Given that @> already can chec

Re: [HACKERS] [COMMITTERS] pgsql: Add pg_audit, an auditing extension

David Steele writes: > My honest, albeit novice, opinion is that it was a mistake to pull > pg_audit from contrib. I know more than anyone that it had flaws, > mostly owing to its implementation as an extension, but it also provided > capability that simply does not exist right now. Recent conve

Re: [HACKERS] Information of pg_stat_ssl visible to all users

On Tue, Jun 9, 2015 at 3:27 PM, Magnus Hagander wrote: > > On Jun 9, 2015 6:00 AM, "Michael Paquier" wrote: >> >> Hi all, >> >> I should have noticed that before, but it happens that pg_stat_ssl >> leaks information about the SSL status of all the users connected to a >> server. Let's imagine for

Re: [HACKERS] [COMMITTERS] pgsql: Add pg_audit, an auditing extension

Hi Noah, On 6/8/15 10:13 AM, Noah Misch wrote: > My condemnation of the pg_audit commits probably hurt you as the feature's > authors. I am sorry for that. Your code was better than most "Ready for > Committer" code, and I hope you submit more patches in the future. I appreciate you saying this

Re: [HACKERS] Why no jsonb_exists_path()?

Josh Berkus writes: > I'm noticing a feature gap for JSONB operators; we have no way to do this: > jsonb_col ? ARRAY['key1','key2','key3'] > ... that is, there is no way for us to check for key existence in an > indexable fashion. Given that @> already can check the whole path > including the v

[HACKERS] Why no jsonb_exists_path()?

Dmitry, Alexander: I'm noticing a feature gap for JSONB operators; we have no way to do this: jsonb_col ? ARRAY['key1','key2','key3'] ... that is, there is no way for us to check for key existence in an indexable fashion. Given that @> already can check the whole path including the value, is th

Re: [HACKERS] Draft release notes for 9.4.4 et al

Andres Freund wrote: > Hi, > > On 2015-06-09 13:09:27 -0400, Tom Lane wrote: > > I've pushed up draft release notes for 9.4.4 at > > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=21187cfc7dfd82461db9119377a76366c00d27c3 > > Please review and comment ASAP, particularly if the in

Re: [HACKERS] Draft release notes for 9.4.4 et al

Andres Freund writes: > On 2015-06-09 13:09:27 -0400, Tom Lane wrote: >> I've pushed up draft release notes for 9.4.4 at >> http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=21187cfc7dfd82461db9119377a76366c00d27c3 >> Please review and comment ASAP, particularly if the instructions

Re: [HACKERS] Draft release notes for 9.4.4 et al

Hi, On 2015-06-09 13:09:27 -0400, Tom Lane wrote: > I've pushed up draft release notes for 9.4.4 at > http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=21187cfc7dfd82461db9119377a76366c00d27c3 > Please review and comment ASAP, particularly if the instructions regarding > avoiding e

[HACKERS] Draft release notes for 9.4.4 et al

I've pushed up draft release notes for 9.4.4 at http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=21187cfc7dfd82461db9119377a76366c00d27c3 Please review and comment ASAP, particularly if the instructions regarding avoiding emergency autovacuuming are not accurate.

Re: [HACKERS] "could not adopt C locale" failure at startup on Windows

Andres Freund writes: > On 2015-06-09 11:20:06 -0400, Tom Lane wrote: >> We've seen several reports of $SUBJECT lately. I have no idea what's >> going on, but it occurred to me that it could be informative to tweak >> init_locale() so that it reports which category failed to be set. >> Any object

Re: [HACKERS] The Future of Aggregation

On 06/09/15 17:27, Andres Freund wrote: On 2015-06-09 17:19:33 +0200, Tomas Vondra wrote: ... and yet another use case for 'aggregate state combine' that I just remembered about is grouping sets. What GROUPING SET (ROLLUP, ...) do currently is repeatedly sorting the input, once for each groupi

Re: [HACKERS] Aggregate Supporting Functions

Tom Lane wrote: > Kevin Grittner writes: >> David Rowley wrote: >>> [ avoid duplicate calculations for related aggregates ] >> From the information you have proposed storing, with cost factors >> associated with the functions, it seems technically possible to >> infer that you could run (for ex

Re: [HACKERS] "could not adopt C locale" failure at startup on Windows

On 2015-06-09 11:20:06 -0400, Tom Lane wrote: > We've seen several reports of $SUBJECT lately. I have no idea what's > going on, but it occurred to me that it could be informative to tweak > init_locale() so that it reports which category failed to be set. > Any objections to squeezing that into t

Re: [HACKERS] The Future of Aggregation

On 2015-06-09 17:19:33 +0200, Tomas Vondra wrote: > ... and yet another use case for 'aggregate state combine' that I just > remembered about is grouping sets. What GROUPING SET (ROLLUP, ...) do > currently is repeatedly sorting the input, once for each grouping. Actually, that's not really what h

Re: [HACKERS] The Future of Aggregation

Kevin Grittner wrote: > Alvaro Herrera wrote: > > Uh, this also requires serialization and deserialization of non- > > finalized transition state, no? > > For that sort of optimization to incremental maintenance of > materialized views (when we get there), yes. That will be one of > many issues

[HACKERS] "could not adopt C locale" failure at startup on Windows

We've seen several reports of $SUBJECT lately. I have no idea what's going on, but it occurred to me that it could be informative to tweak init_locale() so that it reports which category failed to be set. Any objections to squeezing that into today's releases? regards, tom

Re: [HACKERS] The Future of Aggregation

On 06/09/15 16:10, Tomas Vondra wrote: Hi, On 06/09/15 12:58, David Rowley wrote: ... Items 1-4 above I believe require support of "Aggregate State Combine Support" -> https://commitfest.postgresql.org/5/131/ which I believe will need to be modified to implement complex database types to b

Re: [HACKERS] The Future of Aggregation

Alvaro Herrera wrote: > Kevin Grittner wrote: >> David Rowley wrote: >>> 5. Dependant Aggregates >>> >>> Item 5 makes items 1-4 a bit more complex as with this item >>> there's opportunity for very good performance improvements by >>> allowing aggregates like AVG(x) also perform all the required

Re: [HACKERS] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file

On 06/08/2015 11:19 PM, Amit Kapila wrote: On Tue, Jun 9, 2015 at 12:27 AM, Andrew Dunstan > wrote: On 06/08/2015 11:16 AM, Amit Kapila wrote: I have to retry that operation, but for me unlink hasn't deleted the file on Windows, may be I am not

Re: [HACKERS] The Future of Aggregation

Kevin Grittner wrote: > David Rowley wrote: > > 5. Dependant Aggregates > > > Item 5 makes items 1-4 a bit more complex as with this item > > there's opportunity for very good performance improvements by > > allowing aggregates like AVG(x) also perform all the required > > work to allow SUM(x) a

Re: [HACKERS] The Future of Aggregation

David Rowley wrote: > It appears to me that there's quite a few new features and > optimisations on the not too distant horizon which will require > adding yet more fields into pg_aggregate. > > These are things along the lines of: > 3. Auto-updating Materialized views (ones which contain aggreg

Re: [HACKERS] Aggregate Supporting Functions

Kevin Grittner writes: > David Rowley wrote: >> [ avoid duplicate calculations for related aggregates ] > From the information you have proposed storing, with cost factors > associated with the functions, it seems technically possible to > infer that you could run (for example) the avg() aggrega

Re: [HACKERS] The Future of Aggregation

Hi, On 06/09/15 12:58, David Rowley wrote: These are things along the lines of: 1. Parallel Aggregation (computes each aggregate state in parallel worker processes and then merges these states in serial mode) 2. Aggregate push-down / Aggregate before join (requires passing partially computed a

Re: [HACKERS] Aggregate Supporting Functions

David Rowley wrote: > The idea is that we skip a major chunk of processing in > situations like: > > SELECT avg(x),sum(x),count(x) FROM bigtable; > > Because avg(x) already technically knows what the values of > sum(x) and count(x) are. That has occurred to me as a possible optimization, but I h

Re: [HACKERS] psql :: support for \ev viewname and \sv viewname

The following review has been posted through the commitfest application: make installcheck-world: tested, passed Implements feature: tested, passed Spec compliant: tested, passed Documentation:tested, passed Patch looks good to pass to committer. The new status of thi

Re: [HACKERS] psql :: support for \ev viewname and \sv viewname

Hi Patch looks excellent now. No issues. Found a typo which I have fixed in the attached patch. Thanks -- Jeevan B Chalke Principal Software Engineer, Product Development EnterpriseDB Corporation The Enterprise PostgreSQL Company diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/p

Re: [CORE] [HACKERS] back-branch multixact fixes & 9.5 alpha/beta: schedule

On Tue, Jun 9, 2015 at 3:04 AM, David Gould wrote: > On Mon, 8 Jun 2015 13:53:42 -0300 > Alvaro Herrera wrote: > >> * people with the wrong oldestMulti setting in pg_control (which would >> be due to a buggy pg_upgrade being used long ago) will be unable to >> start if they upgrade to 9.3.7 or 9.

Re: [HACKERS] bugfix: incomplete implementation of errhidecontext

On Mon, Jun 8, 2015 at 8:19 PM, Andres Freund wrote: > On 2015-06-08 14:44:53 +, Jeevan Chalke wrote: > > The following review has been posted through the commitfest application: > > make installcheck-world: tested, passed > > Implements feature: tested, passed > > Spec compliant:

[HACKERS] The Future of Aggregation

It appears to me that there's quite a few new features and optimisations on the not too distant horizon which will require adding yet more fields into pg_aggregate. These are things along the lines of: 1. Parallel Aggregation (computes each aggregate state in parallel worker processes and then me

[HACKERS] Dead code in Create/RenameRole() after RoleSpec changes related to CURRENT/SESSION_USER

Hi, I found some dead code in CREATE/RENAME ROLE code path. Attached patch to remove those. We have introduced RoleSpec and handled public and none role names in grammar itself. We do have these handling in CreateRole() and RenameRole() which is NO more valid now. Here is the related commit: com

Re: [HACKERS] Cancel race condition

Ah, OK - I wasn't aware that cancellation was actually delivered as a regular POSIX signal... You're right about the lack of guarantees then. In that case, I'm guessing not much could be do to guarantee sane cancellation behavior... I do understand the "best effort" idea around cancellations. Howe

Re: [HACKERS] CREATE POLICY and RETURNING

On 8 June 2015 at 16:53, Stephen Frost wrote: > I definitely don't like the idea of returning a portion of the data in a > RETURNING clause. Returning an error if the RETURNING clause ends up > not passing the SELECT policy is an interesting idea, but I do have > doubts about how often that will

Re: [CORE] [HACKERS] back-branch multixact fixes & 9.5 alpha/beta: schedule

On Mon, 8 Jun 2015 13:53:42 -0300 Alvaro Herrera wrote: > * people with the wrong oldestMulti setting in pg_control (which would > be due to a buggy pg_upgrade being used long ago) will be unable to > start if they upgrade to 9.3.7 or 9.3.8. A solution for them would be > to downgrade to 9.3.6.