Re: [HACKERS] Additional role attributes superuser review

2015-03-16 Thread Stephen Frost
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: ... Lastly, there is the question of pg_cancel_backend and pg_terminate_backend. My thinking on this is to create a new 'pg_signal_backend' which admins could grant access to and leave the existing functions

Re: [HACKERS] [PATCH] Add transforms feature

2015-03-16 Thread Peter Eisentraut
On 3/12/15 8:12 AM, Pavel Stehule wrote: 1. fix missing semicolon pg_proc.h Oid protrftypes[1]; /* types for which to apply transforms */ Darn, I thought I had fixed that. 2. strange load lib by in sql scripts: DO '' LANGUAGE plperl; SELECT NULL::hstore; use load

Re: [HACKERS] OOM-killer issue when updating a inheritance table which has large number of child tables

2015-03-16 Thread chenhj
At the moment, partitioning into thousands of tables is not supported. Thank you for your reply. And thanks Tom Lane and Stephen Frost! The following(with createsql.sql and update.sql as attachment) is my complete test case. And i reproduced this problem in PostgreSQL 9.4.1 . 1)create

Re: [HACKERS] ranlib bleating about dirmod.o being empty

2015-03-16 Thread Peter Eisentraut
On 3/14/15 12:58 PM, Tom Lane wrote: I'm getting rather tired of reading these warning messages in OS X builds: /Applications/Xcode.app/Contents/Developer/Toolchains/XcodeDefault.xctoolchain/usr/bin/ranlib: file: libpgport.a(dirmod.o) has no symbols

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Alvaro Herrera
I pushed a fix for this. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription:

[HACKERS] Future directions for inheritance-hierarchy statistics

2015-03-16 Thread Tom Lane
A few days ago I posted a very-much-WIP patch for making the planner dynamically combine statistics for each member of an appendrel: http://www.postgresql.org/message-id/22598.1425686...@sss.pgh.pa.us That patch was only intended to handle the case of an appendrel generated by a UNION ALL

Re: [HACKERS] Additional role attributes superuser review

2015-03-16 Thread Stephen Frost
All, * Stephen Frost (sfr...@snowman.net) wrote: Alright, I've got an initial patch to do this for pg_start/stop_backup, pg_switch_xlog, and pg_create_restore_point. The actual backend changes are quite small, as expected. I'll add in the changes for the other functions being discussed and

Re: [HACKERS] pg_xlog_replay_resume() considered armed and dangerous

2015-03-16 Thread Jim Nasby
On 3/12/15 10:08 AM, Andres Freund wrote: I think this, at the very least, needs a big caveat in the documentation of the resume function. But a different API would probably be better. I'd actually argue that for now pg_xlog_replay_resume() should refuse to work if paused due to a recovery

Re: [HACKERS] One question about security label command

2015-03-16 Thread Alvaro Herrera
Kohei KaiGai wrote: This regression test fail come from the base security policy of selinux. In the recent selinux-policy package, unconfined domain was changed to have unrestricted permission as literal. So, this test case relies multi- category policy restricts unconfined domain, but its

Re: [HACKERS] One question about security label command

2015-03-16 Thread Stephen Frost
Alvaro, KaiGai, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Kohei KaiGai wrote: This regression test fail come from the base security policy of selinux. In the recent selinux-policy package, unconfined domain was changed to have unrestricted permission as literal. So, this test

[HACKERS] Question about TEMP tables

2015-03-16 Thread Dmitry Voronin
Hello, all. We can create temp namespaces and temp objects that contains it. So, for example, temp table will be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1 namespaces with OIDs 11333 and 11334. Those namespaces are visible from any cluster

Re: [HACKERS] Reduce pinning in btree indexes

2015-03-16 Thread Kevin Grittner
Kyotaro HORIGUCHI horiguchi.kyot...@lab.ntt.co.jp wrote: Thank you for rewriting. Thank *you* for pointing out where more work was needed in the comments and README! I attached the your latest patch to this mail as bt-nopin-v4.patch for now. Please check that there's no problem in it. I

Re: [HACKERS] Reduce pinning in btree indexes

2015-03-16 Thread Simon Riggs
On 16 March 2015 at 12:48, Kevin Grittner kgri...@ymail.com wrote: Simon Riggs si...@2ndquadrant.com wrote: On 13 March 2015 at 15:41, Kevin Grittner kgri...@ymail.com wrote: The feedback was generally fairly positive except for the fact that snapshot age (for purposes of being too old) was

Re: [HACKERS] Using 128-bit integers for sum, avg and statistics aggregates

2015-03-16 Thread Petr Jelinek
On 16/03/15 00:37, Andreas Karlsson wrote: On 03/15/2015 09:47 PM, Petr Jelinek wrote: It's almost the same thing as you wrote but the 128 bit and 64 bit optimizations are put on the same level of optimized routines. But this is nitpicking at this point, I am happy with the patch as it stands

Re: [HACKERS] One question about security label command

2015-03-16 Thread Stephen Frost
Tom, * Tom Lane (t...@sss.pgh.pa.us) wrote: The idea of making the regression test entirely independent of the system's policy would presumably solve this problem, so I'd kind of like to see progress on that front. Apologies, I guess it wasn't clear, but that's what I was intending to

Re: [HACKERS] One question about security label command

2015-03-16 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Kohei KaiGai wrote: The attached patch fixes the policy module of regression test. Is this something we would backpatch? As it's just a change to the regression tests, it seems like it'd be a good

Re: [HACKERS] recovery_target_action = pause hot_standby = off

2015-03-16 Thread Andres Freund
On 2015-03-16 07:52:20 +, Simon Riggs wrote: On 15 March 2015 at 22:38, Andres Freund and...@2ndquadrant.com wrote: Sorry, I don't buy this. If I have recovery_target_action = 'pause' in the config file, I want it to pause. You want it to enter a state where you cannot perform any

Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)

2015-03-16 Thread Robert Haas
On Sat, Mar 14, 2015 at 10:37 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: From the standpoint of extension development, I'm uncertain whether we can easily reproduce information needed to compute alternative paths on the hook at standard_join_search(), like a hook at add_paths_to_joinrel().

Re: [HACKERS] Join push-down support for foreign tables

2015-03-16 Thread Robert Haas
On Wed, Mar 4, 2015 at 4:26 AM, Shigeru Hanada shigeru.han...@gmail.com wrote: Here is v4 patch of Join push-down support for foreign tables. This patch requires Custom/Foreign join patch v7 posted by Kaigai-san. Hi, I just want to point out to the folks on this thread that the action in this

Re: [HACKERS] Reduce pinning in btree indexes

2015-03-16 Thread Robert Haas
On Mon, Mar 16, 2015 at 8:48 AM, Kevin Grittner kgri...@ymail.com wrote: Given that there seems to be disagreement on what is the more useful metric, do we want to consider allowing more than one? If so, would it be when *all* conditions are met or when *any* conditions are met? Oh, gosh.

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Stephen Frost
* Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Actually, there was a bug in the changes of the rule for ALTER EXTENSION ADD OPERATOR CLASS. I noticed by chance only, and upon testing it manually I realized I had made a mistake. I then remembered I made the same bug previously, fixed by

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-16 Thread Alvaro Herrera
Robert Haas wrote: On Wed, Mar 11, 2015 at 1:32 PM, Greg Stark st...@mit.edu wrote: I think what we have here is already a good semantic representation. It doesn't handle all the corner cases but those corner cases are a) very unlikely and b) easy to check for. A tool can check for any

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Stephen Frost
* Andres Freund (and...@2ndquadrant.com) wrote: On 2015-03-16 12:50:13 -0300, Alvaro Herrera wrote: Well, we already have make targets for gcov and friends; you get some HTML charts and marked-up source lines with coverage counts, etc. I don't think we've made any use of that. It'd be

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Alvaro Herrera
Stephen Frost wrote: Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Well, we already have make targets for gcov and friends; you get some HTML charts and marked-up source lines with coverage counts, etc. I don't think we've made any use of that. It'd be neat to have

Re: [HACKERS] Allow snapshot too old error, to prevent bloat

2015-03-16 Thread Andrew Dunstan
On Mon, Mar 16, 2015 at 11:46 AM, Robert Haas robertmh...@gmail.com wrote: On Sun, Mar 15, 2015 at 8:04 PM, Rui DeSousa r...@crazybean.net wrote: Would a parameter to auto terminate long running transactions be a better solution? Terminating the long running transaction would allow for the

[HACKERS] How to create shared_ptr for PGconn?

2015-03-16 Thread Chengyu Fan
Hi, Does anyone know how to create the shared_ptr for PGconn? I always get compile errors ... Below is my code:---const char * dbInfo = xxx;PGconn *conn = PGconnectdb(dbInfo);if (PGstatus(conn) != CONNECTION_OK) {std::cout PQerrorMessage(conn) std::endl;return 1;}

Re: [HACKERS] Reduce pinning in btree indexes

2015-03-16 Thread Robert Haas
On Mon, Mar 16, 2015 at 12:07 PM, Simon Riggs si...@2ndquadrant.com wrote: What Simon actually proposed was by *bloat*; that is, if the overall amount of bloat in the system exceeds some metric, start whacking old queries in order to control it. The appeal of that is obvious, Agreed but I

Re: [HACKERS] Reduce pinning in btree indexes

2015-03-16 Thread Simon Riggs
On 16 March 2015 at 13:02, Robert Haas robertmh...@gmail.com wrote: I'm not sure what the value of basing this on WAL volume is, and I think we should talk about that a bit more first. The value of doing this by rate of XID consumption is easy to understand: it's simple to implement. The

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Andres Freund
On 2015-03-16 12:50:13 -0300, Alvaro Herrera wrote: Well, we already have make targets for gcov and friends; you get some HTML charts and marked-up source lines with coverage counts, etc. I don't think we've made any use of that. It'd be neat to have something similar to our doxygen service,

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Stephen Frost
Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Stephen Frost wrote: I don't think we've made use of it either. If the targets/code are already there to make it happen and it's just a matter of having a system running which is generating the website then I can probably get

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-16 Thread Greg Stark
On Mon, Mar 16, 2015 at 4:29 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: +1 what Robert said. I think the additional keyword columns are a good solution to the issue. Huh. Well I disagree but obviously I'm in the minority. I'll put fix it up accordingly today and post the resulting

Re: [HACKERS] INSERT ... ON CONFLICT IGNORE (and UPDATE) 3.0

2015-03-16 Thread Dean Rasheed
On 5 March 2015 at 23:44, Peter Geoghegan p...@heroku.com wrote: On Wed, Mar 4, 2015 at 5:18 PM, Peter Geoghegan p...@heroku.com wrote: Attached patch series forms what I'm calling V3.0 of the INSERT ... ON CONFLICT IGNORE/UPDATE feature. (Sorry about all the threads. I feel this development

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Stephen Frost
Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Stephen Frost wrote: It'd certainly be great to have more testing in general, but we're not going to be able to include complete code coverage tests in the normal set of regression tests which are run.. What I've been thinking for

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-16 Thread Robert Haas
On Wed, Mar 11, 2015 at 1:32 PM, Greg Stark st...@mit.edu wrote: I think what we have here is already a good semantic representation. It doesn't handle all the corner cases but those corner cases are a) very unlikely and b) easy to check for. A tool can check for any users starting with + or

Re: [HACKERS] Allow snapshot too old error, to prevent bloat

2015-03-16 Thread Robert Haas
On Sun, Mar 15, 2015 at 8:04 PM, Rui DeSousa r...@crazybean.net wrote: Would a parameter to auto terminate long running transactions be a better solution? Terminating the long running transaction would allow for the normal vacuuming process to cleanup the deleted records thus avoiding

Re: [HACKERS] CATUPDATE confusion?

2015-03-16 Thread Stephen Frost
* Robert Haas (robertmh...@gmail.com) wrote: On Fri, Mar 13, 2015 at 9:52 AM, Stephen Frost sfr...@snowman.net wrote: * Stephen Frost (sfr...@snowman.net) wrote: I should have been more specific. I don't believe they've moved to using pg_roles completely (which was created specifically to

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Alvaro Herrera
Stephen Frost wrote: Alvaro, * Alvaro Herrera (alvhe...@2ndquadrant.com) wrote: Stephen Frost wrote: I thought the rest of it looked alright. I agree it's a bit odd how the opfamily is handled but I agree with your assessment that there's not much better we can do with this object

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Alvaro Herrera
Stephen Frost wrote: It'd certainly be great to have more testing in general, but we're not going to be able to include complete code coverage tests in the normal set of regression tests which are run.. What I've been thinking for a while (probably influenced by other discussion) is that we

Re: [HACKERS] CATUPDATE confusion?

2015-03-16 Thread Robert Haas
On Fri, Mar 13, 2015 at 9:52 AM, Stephen Frost sfr...@snowman.net wrote: * Stephen Frost (sfr...@snowman.net) wrote: I should have been more specific. I don't believe they've moved to using pg_roles completely (which was created specifically to address the issue that regular users can't

Re: [HACKERS] CATUPDATE confusion?

2015-03-16 Thread Adam Brightwell
All, Sure, if we never deprecate anything then tool authors would never need to change their existing code. I don't think that's actually a viable solution though; the reason we're discussing the removal of these particular views is that they aren't really being maintained and, when they

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-16 Thread David G. Johnston
On Mon, Mar 16, 2015 at 9:29 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Robert Haas wrote: On Wed, Mar 11, 2015 at 1:32 PM, Greg Stark st...@mit.edu wrote: I think what we have here is already a good semantic representation. It doesn't handle all the corner cases but those corner

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-16 Thread Robert Haas
On Mon, Mar 16, 2015 at 1:46 PM, David G. Johnston david.g.johns...@gmail.com wrote: Why not just leave the double-quoting requirements intact. An unquoted any or sameuser (etc) would represent the special keyword while the quoted version would mean that the name is used literally. That would

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-16 Thread Greg Stark
On Mon, Mar 16, 2015 at 5:46 PM, David G. Johnston david.g.johns...@gmail.com wrote: ​Why not just leave the double-quoting requirements intact. An unquoted any or sameuser (etc) would represent the special keyword while the quoted version would mean that the name is used literally. For

Re: [HACKERS] One question about security label command

2015-03-16 Thread Kouhei Kaigai
* Tom Lane (t...@sss.pgh.pa.us) wrote: The idea of making the regression test entirely independent of the system's policy would presumably solve this problem, so I'd kind of like to see progress on that front. Apologies, I guess it wasn't clear, but that's what I was intending to

Re: [HACKERS] Question about TEMP tables

2015-03-16 Thread Craig Ringer
On 16 March 2015 at 16:31, Dmitry Voronin carriingfat...@yandex.ru wrote: Hello, all. We can create temp namespaces and temp objects that contains it. So, for example, temp table will be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1

Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)

2015-03-16 Thread Shigeru Hanada
2015-03-14 7:18 GMT+09:00 Robert Haas robertmh...@gmail.com: I think the foreign data wrapper join pushdown case, which also aims to substitute a scan for a join, is interesting to think about, even though it's likely to be handled by a new FDW method instead of via the hook. Where should the

Re: [HACKERS] Parallel Seq Scan

2015-03-16 Thread Amit Kapila
On Fri, Mar 13, 2015 at 7:00 PM, Robert Haas robertmh...@gmail.com wrote: On Fri, Mar 13, 2015 at 8:59 AM, Amit Kapila amit.kapil...@gmail.com wrote: We can't directly call DestroyParallelContext() to terminate workers as it can so happen that by that time some of the workers are still not

Re: [HACKERS] [PATCH] Add transforms feature

2015-03-16 Thread Pavel Stehule
2015-03-17 2:51 GMT+01:00 Peter Eisentraut pete...@gmx.net: On 3/12/15 8:12 AM, Pavel Stehule wrote: 1. fix missing semicolon pg_proc.h Oid protrftypes[1]; /* types for which to apply transforms */ Darn, I thought I had fixed that. 2. strange load lib by in sql

[HACKERS] Resetting crash time of background worker

2015-03-16 Thread Amit Khandekar
When the postmaster recovers from a backend or worker crash, it resets bg worker's crash time (rw-rw_crashed_at) so that the bgworker will immediately restart (ResetBackgroundWorkerCrashTimes). But resetting rw-rw_crashed_at to 0 means that we have lost the information that the bgworker had

Re: [HACKERS] Improving RLS qual pushdown

2015-03-16 Thread Dean Rasheed
I took another look at this and came up with some alternate comment rewording. I also added a couple of additional comments that should hopefully clarify the code a bit. Regards, Dean diff --git a/src/backend/optimizer/path/allpaths.c b/src/backend/optimizer/path/allpaths.c new file mode 100644

Re: [HACKERS] get_object_address support for additional object types

2015-03-16 Thread Dean Rasheed
On 16 March 2015 at 15:11, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Actually, on second thought I revisited this and changed the representation for opfamilies and opclasses: instead of putting the AM name in objargs, we can put it as the first element of objname instead. That way,

Re: [HACKERS] Additional role attributes superuser review

2015-03-16 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: ... Lastly, there is the question of pg_cancel_backend and pg_terminate_backend. My thinking on this is to create a new 'pg_signal_backend' which admins could grant access to and leave the existing functions alone (modulo the change for

Re: [HACKERS] Additional role attributes superuser review

2015-03-16 Thread Tom Lane
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: That seems fairly ugly. Why would we need a new, duplicative function here? (Apologies if the reasoning was spelled out upthread, I've not been paying much attention.) Currently, those functions allow users to

Re: [HACKERS] assessing parallel-safety

2015-03-16 Thread Robert Haas
On Mon, Mar 16, 2015 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Is there a reason not to make a rule that opclass members must be parallel-safe? I ask because I think it's important that the process of planning a query be categorically

Re: [HACKERS] How to create shared_ptr for PGconn?

2015-03-16 Thread Dmitry Igrishin
2015-03-16 19:32 GMT+03:00 Chengyu Fan chengyu@hotmail.com: Hi, Does anyone know how to create the shared_ptr for PGconn? I always get compile errors ... Below is my code: --- const char * dbInfo = xxx; PGconn *conn = PGconnectdb(dbInfo); if (PGstatus(conn) !=

Re: [HACKERS] assessing parallel-safety

2015-03-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: On Mon, Mar 16, 2015 at 2:55 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Is there a reason not to make a rule that opclass members must be parallel-safe? I ask because I think it's important that the process of

Re: [HACKERS] assessing parallel-safety

2015-03-16 Thread Robert Haas
On Sun, Mar 15, 2015 at 2:39 AM, Noah Misch n...@leadboat.com wrote: On Thu, Mar 12, 2015 at 11:21:37AM -0400, Robert Haas wrote: On Thu, Feb 19, 2015 at 1:19 AM, Noah Misch n...@leadboat.com wrote: Rereading my previous message, I failed to make the bottom line clear: I recommend marking

Re: [HACKERS] assessing parallel-safety

2015-03-16 Thread Tom Lane
Robert Haas robertmh...@gmail.com writes: Is there a reason not to make a rule that opclass members must be parallel-safe? I ask because I think it's important that the process of planning a query be categorically parallel-safe. I'm not seeing the connection between those two statements. The

Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission

2015-03-16 Thread David G. Johnston
On Mon, Mar 16, 2015 at 11:11 AM, Greg Stark st...@mit.edu wrote: On Mon, Mar 16, 2015 at 5:46 PM, David G. Johnston david.g.johns...@gmail.com wrote: ​Why not just leave the double-quoting requirements intact. An unquoted any or sameuser (etc) would represent the special keyword while

Re: [HACKERS] Removing INNER JOINs

2015-03-16 Thread Simon Riggs
On 16 March 2015 at 09:55, David Rowley dgrowle...@gmail.com wrote: I think it's probably possible to do this, but I think it would require calling make_one_rel() with every combination of each possibly removable relations included and not included in the join list. I'm thinking this could

Re: [HACKERS] Reduce pinning in btree indexes

2015-03-16 Thread Kevin Grittner
Simon Riggs si...@2ndquadrant.com wrote: On 13 March 2015 at 15:41, Kevin Grittner kgri...@ymail.com wrote: The feedback was generally fairly positive except for the fact that snapshot age (for purposes of being too old) was measured in transaction IDs assigned. There seemed to be a pretty

Re: [HACKERS] Re: Install shared libs in lib/ and bin/ with MSVC (Was: install libpq.dll in bin directory on Windows / Cygwin)

2015-03-16 Thread Michael Paquier
On Mon, Mar 16, 2015 at 4:56 PM, Asif Naeem anaeem...@gmail.com wrote: The following review has been posted through the commitfest application: make installcheck-world: not tested Implements feature: tested, failed Spec compliant: not tested Documentation:not

Re: [HACKERS] Performance improvement for joins where outer side is unique

2015-03-16 Thread Kyotaro HORIGUCHI
Hello, I don't have enough time for now but made some considerations on this. It might be a way that marking unique join peer at bottom and propagate up, not searching from top of join list. Around create_join_clause might be a candidate for it. I'll investigate that later. regards, At Sat, 14

[HACKERS] Question about TEMP tables

2015-03-16 Thread Dmitry Voronin
Hello, all.We can create temp namespaces and temp objects that contains it. So, for example, temp table will be create at pg_temp_N (N - backendID). But afrer cluster init we have pg_temp_1 and pg_toast_temp_1 namespaces with OIDs 11333 and 11334. Those namespaces are visible from any cluster