Re: [HACKERS] 9.5 release notes
On Wed, Aug 26, 2015 at 10:10:01AM -0700, Peter Geoghegan wrote: On Wed, Aug 26, 2015 at 7:33 AM, Bruce Momjian br...@momjian.us wrote: I have applied the attached patch to document this in the data type docs. Thanks, but shouldn't varchar/text also be mentioned in the release notes, rather than character fields? Good point. I have applied the attached patch. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/release-9.5.sgml b/doc/src/sgml/release-9.5.sgml new file mode 100644 index 438c5ed..a535e22 *** a/doc/src/sgml/release-9.5.sgml --- b/doc/src/sgml/release-9.5.sgml *** Add GUC and storage parameter to set the *** 315,322 2015-05-13 [78efd5c] Robert..: Extend abbreviated key infrastructure to datum .. -- para ! Improve the speed of sorting character and typeNUMERIC/ fields ! (Peter Geoghegan, Andrew Gierth, Robert Haas) /para /listitem --- 315,323 2015-05-13 [78efd5c] Robert..: Extend abbreviated key infrastructure to datum .. -- para ! Improve the speed of sorting typeVARCHAR/, typeTEXT/, ! and typeNUMERIC/ fields (Peter Geoghegan, Andrew Gierth, ! Robert Haas) /para /listitem -- 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] RFC: replace pg_stat_activity.waiting with something more descriptive
On Mon, Jul 27, 2015 at 04:10:14PM -0500, Jim Nasby wrote: Sure, but I don't think this makes it impossible to figure out who's locking who. I think the only thing you need other than the data in pg_locks is the conflicts table, which is well documented. Oh, hmm, one thing missing is the ordering of the wait queue for each locked object. If process A holds RowExclusive on some object, process B wants ShareLock (stalled waiting) and process C wants AccessExclusive (also stalled waiting), who of B and C is woken up first after A releases the lock depends on order of arrival. Agreed - it would be nice to expose that somehow. +1. It's very common to want to know who's blocking who, and not at all easy to do that today. We should at minimum have a canonical example of how to do it, but something built in would be even better. Coming in late here, but have you looked at my locking presentation; I think there are examples in there: http://momjian.us/main/writings/pgsql/locking.pdf -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Is this a bug?
On Mon, Aug 25, 2014 at 6:07 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Aug 22, 2014 at 10:04:50PM -0400, Bruce Momjian wrote: On Fri, Aug 22, 2014 at 03:12:47PM -0400, Robert Haas wrote: On Fri, Aug 22, 2014 at 2:33 PM, Bruce Momjian br...@momjian.us wrote: Yes, you remember well. I will have to find a different way for pg_upgrade to call a no-op ALTER TABLE, which is fine. Looking at the ALTER TABLE options, I am going to put this check in a !IsBinaryUpgrade block so pg_upgrade can still use its trick. -1, that's really ugly. Maybe the right solution is to add a form of ALTER TABLE that is specifically defined to do only this check. This is an ongoing need, so that might not be out of line. Ah, seems ALTER TABLE ... DROP CONSTRAINT IF EXISTS also works --- I will use that. OK, attached patch applied, with pg_upgrade adjustments. I didn't think the original regression tests for this were necessary. Hi, Why this patch was reverted one day after applied [1]? I didn't see any discussion around it. Regards, [1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6cb74a67e26523eb2408f441bfc589c80f76c465 -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] Is this a bug?
On 26 August 2015 at 20:24, Fabrízio de Royes Mello fabriziome...@gmail.com wrote: On Mon, Aug 25, 2014 at 6:07 PM, Bruce Momjian br...@momjian.us wrote: On Fri, Aug 22, 2014 at 10:04:50PM -0400, Bruce Momjian wrote: On Fri, Aug 22, 2014 at 03:12:47PM -0400, Robert Haas wrote: On Fri, Aug 22, 2014 at 2:33 PM, Bruce Momjian br...@momjian.us wrote: Yes, you remember well. I will have to find a different way for pg_upgrade to call a no-op ALTER TABLE, which is fine. Looking at the ALTER TABLE options, I am going to put this check in a !IsBinaryUpgrade block so pg_upgrade can still use its trick. -1, that's really ugly. Maybe the right solution is to add a form of ALTER TABLE that is specifically defined to do only this check. This is an ongoing need, so that might not be out of line. Ah, seems ALTER TABLE ... DROP CONSTRAINT IF EXISTS also works --- I will use that. OK, attached patch applied, with pg_upgrade adjustments. I didn't think the original regression tests for this were necessary. Hi, Why this patch was reverted one day after applied [1]? I didn't see any discussion around it. Regards, [1] http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=6cb74a67e26523eb2408f441bfc589c80f76c465 The discussion was here: http://www.postgresql.org/message-id/20140826000757.ge14...@momjian.us Thom
Re: [HACKERS] 9.4 broken on alpha
On Tue, Aug 25, 2015 at 06:09:17PM -0400, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Well, strictly speaking, there were no uses of pg_read_barrier until 9.4. However, pg_write_barrier (which used wmb) was in use since 9.2; so unless you're claiming your assembler knows wmb but not rmb, the code's failed to compile for Alpha since 9.2. Actually according to this http://www.cs.cmu.edu/afs/cs.cmu.edu/academic/class/15213-f98/doc/alpha-asm.pdf there is a wmb instruction but there is no rmb. Exactly, as I had explained in the Debian bug report [1] over a year ago. Oh really? If rmb were a figment of someone's imagination, it would explain the build failure (although not why nobody's reported it till now). I reported the failure to build on Alpha, with an explanation and a patch to fix it, to the Debian package maintainers over a year ago, and within about of a month of version 9.4 being uploaded to Debian. My recollection is that prior versions (9.2 and 9.3) compiled on Alpha so the use of the wrong barrier, and the fix, was in fact reported in a timely fashion following the first reasonable chance to observe the problem. It has been built and running at Debian-Ports for over a year now as I uploaded the fixed version to the Alpha unreleased distribution. It'd be easy enough to s/rmb/mb/ in 9.4 ... but not sure it's worth the trouble, since we're desupporting Alpha as of 9.5 anyway. That is disappointing to hear. Why is that? It is still in use on Alpha. What is the maintenance load for keeping the Alpha arch specific code? If the effective desupport date is 9.4 instead, It's not. The fixed and built 9.4 version was uploaded to Debian-Ports Alpha (in the unreleased distribution) and has been in use for over a year. Regards, Michael. [1] https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=756368 -- 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] 9.4 broken on alpha
Andres Freund and...@anarazel.de writes: But I really strongly object to re-introducing alpha support. Having to care about data dependency barriers is a huge pita, and it complicates code for everyone. And we'd have to investigate a lot of code to actually make it work reliably. For what benefit? I hear you, but that's only an issue for multi-CPU machines no? If we just say we doubt this works on multi-CPU Alphas, if it breaks you get to keep both pieces, then we're basically at the same place we were before. To be clear: I don't want to do the work you're speaking of, either. But if we have people who were successfully using PG on Alphas before, the coherency issues must not have been a problem for them. Can't we just (continue to) ignore the issue? If we really were to re-introduce this we'd need an actual developer machine to run tests against. I would certainly expect that we'd insist on active support from the Alpha community; we're not going to continue to do this in an open-loop fashion. regards, tom lane -- 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] 9.5 release notes
On Wed, Aug 26, 2015 at 02:47:14PM -0400, Bruce Momjian wrote: On Wed, Aug 26, 2015 at 10:10:01AM -0700, Peter Geoghegan wrote: On Wed, Aug 26, 2015 at 7:33 AM, Bruce Momjian br...@momjian.us wrote: I have applied the attached patch to document this in the data type docs. Thanks, but shouldn't varchar/text also be mentioned in the release notes, rather than character fields? Good point. I have applied the attached patch. I am sorry it so long for me to address this. Peter brought it up in June, but I just wasn't around to address it cleanly before now. I am glad he reminded me. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- 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] Is this a bug?
Fabrízio de Royes Mello wrote: Why this patch was reverted one day after applied [1]? I didn't see any discussion around it. Contributors whose patches are getting committed should really subscribe to pgsql-committers. -- Á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: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Is this a bug?
On Wed, Aug 26, 2015 at 4:30 PM, Andres Freund and...@anarazel.de wrote: On 2015-08-26 16:24:31 -0300, Fabrízio de Royes Mello wrote: Why this patch was reverted one day after applied [1]? I didn't see any discussion around it. http://www.postgresql.org/message-id/23918.1409010...@sss.pgh.pa.us Thanks I'm not subscribed to pgsql-commiters so I didn't see it. -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] Is this a bug?
On 2015-08-26 16:24:31 -0300, Fabrízio de Royes Mello wrote: Why this patch was reverted one day after applied [1]? I didn't see any discussion around it. http://www.postgresql.org/message-id/23918.1409010...@sss.pgh.pa.us -- 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] Is this a bug?
On Wed, Aug 26, 2015 at 4:31 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Fabrízio de Royes Mello wrote: Why this patch was reverted one day after applied [1]? I didn't see any discussion around it. Contributors whose patches are getting committed should really subscribe to pgsql-committers. Ok. I sent a subscribe to pgsql-committers. Thanks, -- Fabrízio de Royes Mello Consultoria/Coaching PostgreSQL Timbira: http://www.timbira.com.br Blog: http://fabriziomello.github.io Linkedin: http://br.linkedin.com/in/fabriziomello Twitter: http://twitter.com/fabriziomello Github: http://github.com/fabriziomello
Re: [HACKERS] Foreign join pushdown vs EvalPlanQual
In addition, you may misunderstand the proposition of mine above. You can check RelOptInfo-fdw_private on top of the GetForeignJoinPaths, then, if it is second or later invocation, you can check cost of the alternative path kept in the ForeignPath node previously constructed. If cheapest_total_path at the moment of GetForeignJoinPaths invocation is cheaper than the saved alternative path, you can adjust the node to replace the alternative path node. To get the (probably unparameterized) cheapest_total_path, IIUC, we need to do set_cheapest during GetForeignJoinPaths in each subsequent invocation of that routine, don't we? And set_cheapest is expensive, isn't it? add_path() usually drop paths that are obviously lesser than others, so walk on join-pathlist shall have reasonable length. Even though it has hundreds items on the pathlist, you CAN implement EPQ fallback using alternative built-in logic. 2. Save the alternative Plan nodes on fdw_plans or lefttree/righttree somewhere you like at the GetForeignPlan() 3. Makes BeginForeignScan() to call ExecInitNode() towards the plan node saved at (2), then save the PlanState on fdw_ps, lefttree/righttree, or somewhere private area if not displayed on EXPLAIN. 4. Implement ForeignRecheck() routine. If scanrelid==0, it kicks the planstate node saved at (3) to generate tuple slot. Then, call the ExecQual() to check qualifiers being pushed down. 5. Makes EndForeignScab() to call ExecEndNode() towards the PlanState saved at (3). but the design that you proposed looks complicated beyond necessity. I think we should add an FDW API for doing something if FDWs have more knowledge about doing that than the core, but in your proposal, instead of the core, an FDW has to eventually do a lot of the core's work: ExecInitNode, ExecProcNode, ExecQual, ExecEndNode and so on. It is a trade-off problem between interface flexibility and code smallness of FDW extension if it fits scope of the core support. I stand on the viewpoint that gives highest priority on the flexibility, especially, in case when unpredictable type of modules are expected. Your proposition is comfortable to FDW on behalf of RDBMS, however, nobody can promise it is beneficial to FDW on behalf of columnar-store for example. Maybe I'm missing something, but why do we need such a flexiblity for the columnar-stores? We have various kind of FDW drivers, some of use cases were unpredictable preliminary. Our community knows 86 kind of FDW drivers in total, and only 15 of them are for RDBMS but 71 of them for other data source. https://wiki.postgresql.org/wiki/Foreign_data_wrappers Even if we enforce them a new interface specification comfortable to RDBMS, we cannot guarantee it is also comfortable to other type of FDW drivers. If module-X wants to implement the EPQ fallback routine by itself, without alternative plan, too rich interface design prevents what module-X really wants to do. If you stick on the code smallness of FDW on behalf of RDBMS, we can add utility functions on foreign.c or somewhere. It will be able to provide equivalent functionality, but FDW can determine whether it use the routines. That might be an idea, but I'd like to hear the opinions of others. -- NEC Business Creation Division / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.com -- 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] Foreign join pushdown vs EvalPlanQual
On 2015/08/26 13:49, Kouhei Kaigai wrote: On 2015/08/25 10:18, Kouhei Kaigai wrote: Likely, what you need to do are... 1. Save the alternative path on fdw_paths when foreign join push-down. GetForeignJoinPaths() may be called multiple times towards a particular joinrel according to the combination of innerrel/outerrel. RelOptInfo-fdw_private allows to avoid construction of same remote join path multiple times. On the second or later invocation, it may be a good tactics to reference cheapest_startup_path and replace the saved one if later invocation have cheaper one, prior to exit. I'm not sure that the tactics is a good one. I think you probably assume that GetForeignJoinPaths executes set_cheapest each time that gets called, but ISTM that that would be expensive. (That is one of the reason why I think it would be better to hook that routine in standard_join_search.) Here is two different problems. I'd like to identify whether the problem is must be solved or nice to have. Obviously, failure on EPQ check is a problem must be solved, however, hook location is nice to have. OK I'll focus on the must be solved problem at least on this thread. In addition, you may misunderstand the proposition of mine above. You can check RelOptInfo-fdw_private on top of the GetForeignJoinPaths, then, if it is second or later invocation, you can check cost of the alternative path kept in the ForeignPath node previously constructed. If cheapest_total_path at the moment of GetForeignJoinPaths invocation is cheaper than the saved alternative path, you can adjust the node to replace the alternative path node. To get the (probably unparameterized) cheapest_total_path, IIUC, we need to do set_cheapest during GetForeignJoinPaths in each subsequent invocation of that routine, don't we? And set_cheapest is expensive, isn't it? 2. Save the alternative Plan nodes on fdw_plans or lefttree/righttree somewhere you like at the GetForeignPlan() 3. Makes BeginForeignScan() to call ExecInitNode() towards the plan node saved at (2), then save the PlanState on fdw_ps, lefttree/righttree, or somewhere private area if not displayed on EXPLAIN. 4. Implement ForeignRecheck() routine. If scanrelid==0, it kicks the planstate node saved at (3) to generate tuple slot. Then, call the ExecQual() to check qualifiers being pushed down. 5. Makes EndForeignScab() to call ExecEndNode() towards the PlanState saved at (3). but the design that you proposed looks complicated beyond necessity. I think we should add an FDW API for doing something if FDWs have more knowledge about doing that than the core, but in your proposal, instead of the core, an FDW has to eventually do a lot of the core's work: ExecInitNode, ExecProcNode, ExecQual, ExecEndNode and so on. It is a trade-off problem between interface flexibility and code smallness of FDW extension if it fits scope of the core support. I stand on the viewpoint that gives highest priority on the flexibility, especially, in case when unpredictable type of modules are expected. Your proposition is comfortable to FDW on behalf of RDBMS, however, nobody can promise it is beneficial to FDW on behalf of columnar-store for example. Maybe I'm missing something, but why do we need such a flexiblity for the columnar-stores? If you stick on the code smallness of FDW on behalf of RDBMS, we can add utility functions on foreign.c or somewhere. It will be able to provide equivalent functionality, but FDW can determine whether it use the routines. That might be an idea, but I'd like to hear the opinions of others. Best regards, Etsuro Fujita -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Change TAP test framework to not rely on having a chmod executab
On Tue, Aug 25, 2015 at 11:02 PM, Peter Eisentraut pete...@gmx.net wrote: On 6/19/15 10:52 AM, Robert Haas wrote: Change TAP test framework to not rely on having a chmod executable. This might not work at all on Windows, and is not ever efficient. Michael Paquier I came across this on an unrelated mission and noticed it was unnecessarily complicated. How about this patch instead? I didn't know this one... Looking at the docs this would visibly work at least down to 5.8.8, the docs saying that a list of files can be passed to chmod: http://perldoc.perl.org/5.8.8/functions/chmod.html -- Michael -- 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] 9.4 broken on alpha
Michael Cree wrote: On Tue, Aug 25, 2015 at 06:09:17PM -0400, Tom Lane wrote: Oh really? If rmb were a figment of someone's imagination, it would explain the build failure (although not why nobody's reported it till now). I reported the failure to build on Alpha, with an explanation and a patch to fix it, to the Debian package maintainers over a year ago, and within about of a month of version 9.4 being uploaded to Debian. It's a pretty disappointing packaging process failure that the bug report wasn't sent to upstream immediately, rather than waiting for a whole year. That would have made a lot less likely that the removal of the port would have passed muster in the first place. Supposedly we were only removing stuff that was pretty clearly dead. It has been built and running at Debian-Ports for over a year now as I uploaded the fixed version to the Alpha unreleased distribution. Has this been battle-tested under high load in multi-core servers? Because based on other comments in this and other threads, it seems likely that the port is subtly broken. It'd be easy enough to s/rmb/mb/ in 9.4 ... but not sure it's worth the trouble, since we're desupporting Alpha as of 9.5 anyway. That is disappointing to hear. Why is that? It is still in use on Alpha. What is the maintenance load for keeping the Alpha arch specific code? The amount of code that was removed by the commit isn't all that much: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a6d488cb538c8761658f0f7edfc40cecc8c29f2d but there's been rather a lot of work after that to add support for atomic primitives as well as barriers, which would presumably not trivial to implement and test on Alpha. Someone would have to volunteer to writing, testing and maintaining that code. A buildfarm machine would be mandatory, too. I'm under the impression that Alpha machines are no longer being built, so I'm doubtful that this would be a good use of anybody's time. If the effective desupport date is 9.4 instead, It's not. The fixed and built 9.4 version was uploaded to Debian-Ports Alpha (in the unreleased distribution) and has been in use for over a year. I think we could apply the bugfix to 9.4, but this doesn't help with 9.5. -- Á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: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Rework access method interface
On Wed, Aug 26, 2015 at 6:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alexander Korotkov a.korot...@postgrespro.ru writes: OK. So, as we mentioned before, if we need to expose something of am parameters at SQL-level then we need to write special functions which would call amhandler and expose it. Did we come to the agreement on this solution? I think we were agreed that we should write functions to expose whatever needs to be visible at SQL level. I'm not sure that we had a consensus on exactly which things need to be visible. One thought here is that we might not want to just blindly duplicate the existing pg_am behavior anyway. For example, the main use of the amstrategies column was to allow validation of pg_amop.amopstrategy entries --- but in 4 out of the 6 existing AMs, knowledge of the AM alone isn't sufficient information to determine the valid set of strategy numbers anyway. So inventing a pg_amstrategies(am oid) function seems like it would be repeating a previous failure. Not quite sure what to do instead, though. We could imagine something like pg_amstrategies(am oid, opclass oid), but I don't see how to implement it without asking opclasses to provide a validation function, which maybe is a change we don't want to take on here. Could we add another function to access method interface which would validate opclass? Am could validate this way not only strategies, but also supporting functions. For instance, in GIN, we now require opclass to specify at least one of consistent and triconsistent. ISTM I would be nice to let the access method check such conditions. Also, we would be able to check opclass correction on its creation. Now one can create opclass with missing support functions which doesn't work. In the SQL-level we can create function which validates opclass using this new method. This function can be used in regression tests. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] 9.4 broken on alpha
On 2015-08-26 12:49:46 -0400, Tom Lane wrote: As far as that goes, we do have fallback atomics code that's supposed to work on anything (and not be unusably slow). So in principle, resurrecting the Alpha spinlock code ought to be enough to get back to the previous level of support. Coding Alpha atomic primitives would likely be worth doing, if there's somebody out there who's excited enough to take it on; but that could happen later, and incrementally. Actually, on linux and most other OSs it should just use the generic gcc based implementation and be pretty close to optimal. The only thing it'd need would be to define the memory barriers, so the fallback implementation of those isn't used. But I really strongly object to re-introducing alpha support. Having to care about data dependency barriers is a huge pita, and it complicates code for everyone. And we'd have to investigate a lot of code to actually make it work reliably. For what benefit? A buildfarm machine would be mandatory, too. That, however, is not negotiable. If we really were to re-introduce this we'd need an actual developer machine to run tests against. -- 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] psql - better support pipe line
Hi
2015-08-25 17:21 GMT+02:00 Tom Lane t...@sss.pgh.pa.us:
Jim Nasby jim.na...@bluetreble.com writes:
What I've had problems with is trying to correlate psql specified
connection attributes with things like DBI. It would be nice if there
was a way to get a fully formed connection URI for the current
connection.
Yeah, although I'd think the capability to create such a URI is libpq's
province not psql's. Maybe a PQgetConnectionURI(PGConn) function in
libpq, and some psql backslash command to access that? Or maybe a nicer
API would be that there's a magic psql variable containing the URI; not
sure.
proof concept of PQGetConnectionUri and \uri command.
missing:
connection options
uri encoding
regards, tom lane
diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c
new file mode 100644
index 6181a61..47e27cd
*** a/src/bin/psql/command.c
--- b/src/bin/psql/command.c
*** exec_command(const char *cmd,
*** 1505,1510
--- 1505,1530
free(opt);
}
+ /* \uri */
+ else if (strcmp(cmd, uri) == 0)
+ {
+ char *db = PQdb(pset.db);
+
+ if (db == NULL)
+ printf(_(You are currently not connected to a database.\n));
+ else
+ {
+ char *uri = PQgetConnectionUri(pset.db);
+ if (uri == NULL)
+ {
+ psql_error(out of memory\n);
+ exit(EXIT_FAILURE);
+ }
+ printf(%s\n, uri);
+ free(uri);
+ }
+ }
+
/* \w -- write query buffer to file */
else if (strcmp(cmd, w) == 0 || strcmp(cmd, write) == 0)
{
diff --git a/src/interfaces/libpq/exports.txt b/src/interfaces/libpq/exports.txt
new file mode 100644
index 4a21bf1..c1165c9
*** a/src/interfaces/libpq/exports.txt
--- b/src/interfaces/libpq/exports.txt
*** PQsslInUse166
*** 169,171
--- 169,172
PQsslStruct 167
PQsslAttributes 168
PQsslAttribute169
+ PQgetConnectionUri170
diff --git a/src/interfaces/libpq/fe-connect.c b/src/interfaces/libpq/fe-connect.c
new file mode 100644
index a45f4cb..c70e25e
*** a/src/interfaces/libpq/fe-connect.c
--- b/src/interfaces/libpq/fe-connect.c
*** PQport(const PGconn *conn)
*** 5361,5366
--- 5361,5402
return conn-pgport;
}
+ #define isdef(strptr) ((strptr) (strptr[0] != '\0'))
+
+ /*
+ * Returns string uri - returned string should be released
+ */
+ char *
+ PQgetConnectionUri(const PGconn *conn)
+ {
+ PQExpBufferData buf;
+ char *host;
+
+ if (!conn)
+ return NULL;
+
+ host = PQhost(conn);
+
+ initPQExpBuffer(buf);
+
+ appendPQExpBuffer(buf, postgresql://%s, conn-pguser);
+
+ if (isdef(conn-pgpass))
+ appendPQExpBuffer(buf, :%s, conn-pgpass);
+ appendPQExpBufferStr(buf, @);
+ if (isdef(host))
+ appendPQExpBufferStr(buf, host);
+ if (isdef(conn-pgport))
+ appendPQExpBuffer(buf, :%s, conn-pgport);
+ if (isdef(conn-dbName))
+ appendPQExpBuffer(buf, /%s, conn-dbName);
+
+ if (isdef(conn-pgoptions))
+ appendPQExpBuffer(buf, ?%s, conn-pgoptions);
+
+ return buf.data;
+ }
+
char *
PQtty(const PGconn *conn)
{
diff --git a/src/interfaces/libpq/libpq-fe.h b/src/interfaces/libpq/libpq-fe.h
new file mode 100644
index a73eae2..7c8a212
*** a/src/interfaces/libpq/libpq-fe.h
--- b/src/interfaces/libpq/libpq-fe.h
*** extern char *PQhost(const PGconn *conn);
*** 304,309
--- 304,310
extern char *PQport(const PGconn *conn);
extern char *PQtty(const PGconn *conn);
extern char *PQoptions(const PGconn *conn);
+ extern char *PQgetConnectionUri(const PGconn *conn);
extern ConnStatusType PQstatus(const PGconn *conn);
extern PGTransactionStatusType PQtransactionStatus(const PGconn *conn);
extern const char *PQparameterStatus(const PGconn *conn,
--
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] 9.4 broken on alpha
Michael Cree mc...@orcon.net.nz writes: On Tue, Aug 25, 2015 at 06:09:17PM -0400, Tom Lane wrote: It'd be easy enough to s/rmb/mb/ in 9.4 ... but not sure it's worth the trouble, since we're desupporting Alpha as of 9.5 anyway. That is disappointing to hear. Why is that? It is still in use on Alpha. What is the maintenance load for keeping the Alpha arch specific code? The core problem is that Alpha's unusually lax memory coherency model creates design and testing problems we face with no other architecture. We're not really excited about carrying that burden for a legacy architecture that isn't competitive in the modern world. Even if we were, it's completely impractical to maintain such an unusual port when there is no representative of the architecture in our buildfarm (http://buildfarm.postgresql.org/cgi-bin/show_status.pl). It's worth pointing out that had there been one, we would have noticed the rmb problem long before 9.4 ever shipped. I do not know anything about the prevalence of multi-CPU Alpha machines. If they're thin on the ground compared to single-CPU, maybe we could just document that we only support the latter, and not worry too much about the memory coherency issues. But in any case, without a commitment from somebody to maintain an Alpha buildfarm member, we will absolutely not consider reviving that port. regards, tom lane -- 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] WIP: Rework access method interface
Alexander Korotkov a.korot...@postgrespro.ru writes: OK. So, as we mentioned before, if we need to expose something of am parameters at SQL-level then we need to write special functions which would call amhandler and expose it. Did we come to the agreement on this solution? I think we were agreed that we should write functions to expose whatever needs to be visible at SQL level. I'm not sure that we had a consensus on exactly which things need to be visible. One thought here is that we might not want to just blindly duplicate the existing pg_am behavior anyway. For example, the main use of the amstrategies column was to allow validation of pg_amop.amopstrategy entries --- but in 4 out of the 6 existing AMs, knowledge of the AM alone isn't sufficient information to determine the valid set of strategy numbers anyway. So inventing a pg_amstrategies(am oid) function seems like it would be repeating a previous failure. Not quite sure what to do instead, though. We could imagine something like pg_amstrategies(am oid, opclass oid), but I don't see how to implement it without asking opclasses to provide a validation function, which maybe is a change we don't want to take on here. regards, tom lane -- 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] Freeze avoidance of very large table.
On Thu, Aug 20, 2015 at 11:46 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Jim Nasby wrote: I think things like pageinspect are very different; I really can't see any use for those beyond debugging (and debugging by an expert at that). I don't think that necessarily means it must continue to be in contrib. Quite the contrary, I think it is a tool critical enough that it should not be relegated to be a second-class citizen as it is now (let's face it, being in contrib *is* second-class citizenship). Attached patch is latest patch. The how to do the VM regression test is changed so that we do test without diagnostic functions. In current patch, we do VACUUM and VACUUM FREEZE table, and check its value of pg_class.relallvisible and relallfrozen. When doing first VACUUM in regression test, the table doesn't have VM. So VACUUM scans all pages and update exactly information about the number of all-visible bit. And when doing second VACUUM FREEZE, VACUUM FREEZE also scans all pages because every page is not marked as all-frozen. So VACUUM FREEZE can update exactly information about the number of all-frozen bit. In previous patch, we checked a bit of VM one by one using by diagnostic function, and compared between these result and pg_class.relallvisible(/frozen). So the essential check process is same as previous patch. We can ensure correctness by using such procedure. Regards, -- Masahiko Sawada 000_add_frozen_bit_into_visibilitymap_v12.patch Description: Binary data -- 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] 9.5 release notes
On Wed, Aug 26, 2015 at 7:33 AM, Bruce Momjian br...@momjian.us wrote: I have applied the attached patch to document this in the data type docs. Thanks, but shouldn't varchar/text also be mentioned in the release notes, rather than character fields? -- Peter Geoghegan -- 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] Function accepting array of complex type
On 08/25/2015 06:21 PM, Jim Nasby wrote: CREATE FUNCTION magsum( c c[] ) RETURNS float LANGUAGE sql AS $$ SELECT sum(sqrt(c.r^2 + c.i^2)) FROM unnest(c) c $$; SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)] ); SELECT magsum( array[row(2.1, 2.1), row(2.2,2.2)]::c[] ); cheers andrew -- 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] 9.4 broken on alpha
Alvaro Herrera alvhe...@2ndquadrant.com writes: Michael Cree wrote: That is disappointing to hear. Why is that? It is still in use on Alpha. What is the maintenance load for keeping the Alpha arch specific code? The amount of code that was removed by the commit isn't all that much: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=a6d488cb538c8761658f0f7edfc40cecc8c29f2d but there's been rather a lot of work after that to add support for atomic primitives as well as barriers, which would presumably not trivial to implement and test on Alpha. Someone would have to volunteer to writing, testing and maintaining that code. As far as that goes, we do have fallback atomics code that's supposed to work on anything (and not be unusably slow). So in principle, resurrecting the Alpha spinlock code ought to be enough to get back to the previous level of support. Coding Alpha atomic primitives would likely be worth doing, if there's somebody out there who's excited enough to take it on; but that could happen later, and incrementally. A buildfarm machine would be mandatory, too. That, however, is not negotiable. regards, tom lane -- 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] Custom Scans and private data
On 2015-08-25 19:22:04 -0400, Tom Lane wrote: Andres Freund and...@anarazel.de writes: I think it was a noticeable mistake in the fdw case, but we already released with that. We shouldn't make the same mistake twice. I don't agree that it was a mistake, and I do think there is value in consistency. Having to write ad-hoc serialization code in a different way in each fdw seems like a bad idea to me. The output of that serialization is rather hard to read since there's no names and such, and it's quite inefficient. I have a hard time seing the benefits. In the case at hand, it would not be too hard to provide some utility functions for some common cases; for instance, if you want to just store a struct, we could offer convenience functions to wrap that in a bytea constant and unwrap it again. Those could be useful for both FDWs and custom scans. Yes, that'd already be an improvement. Right now it seems the best way to achieve that is to either base64 the output into a Value node or use bytea inside a Const node. Alternatively you can make it a list of ints but that obviously isn't a grand idea. But imo that's not really good enough as soon as you have more than a single struct. For anything but that you'll have to write serialization/deserialization code again. Additionally you're in many cases forced to always use the serialized representation, even if there's no copyObject() inbetween, since you don't have a proper way to store non-serialized information! Check out serializePlanData in https://github.com/laurenz/oracle_fdw/blob/master/oracle_fdw.c - that's something we shouldn't put people through. Actually, *especially* if we at some point want to ship plans over the wire, that kind of serialization is a bad idea - it's very hard to have any sort of checks. out/readfuncs.c type serialization will have field names, but you can't do to the equivalent from these interfaces. Nor does shove it all into some callbacks mean that the callbacks will be easy to write.) Being able to only perform copying/serialization when necessary, storing the data in a non-serialized manner, seem pretty clear improvements. I think it's important to be able to easily store core data in fields, but that's just a copyObject() call away. Looking at postgres_fdw and the pg-strom example linked upthread imo pretty clearly shows how ugly this is. There's also the rather noticeable difference that we already have callbacks in the node for custom scans (used by outfuncs), making this rather trivial to add. I will manfully refrain from taking that bait. While that comment made me laugh, I'm not really sure why my examples are bait. One is the probably most used fdw, the other the only user of the custom scan interface I know of. Greetings, Andres Freund -- 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] 9.5 release notes
On Wed, Aug 26, 2015 at 11:58 AM, Bruce Momjian br...@momjian.us wrote: I am sorry it so long for me to address this. Peter brought it up in June, but I just wasn't around to address it cleanly before now. I am glad he reminded me. Well, you got around to it eventually. Thanks. -- Peter Geoghegan -- 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] Make HeapTupleSatisfiesMVCC more concurrent
Simon Riggs si...@2ndquadrant.com writes: On 25 August 2015 at 21:51, Tom Lane t...@sss.pgh.pa.us wrote: I don't mean to dismiss the potential for further optimization inside XidInMVCCSnapshot (for instance, the one-XID-cache idea sounds promising); but I think that's material for further research and a separate patch. Patch attached. Doesn't seem worth a separate thread. This doesn't seem right to me: it only caches the case where the XID is found to be present in the snapshot, whereas I think probably we should remember the result in both cases (present or not). Moreover, in the subxip-overflowed case where we replace an originally-inquired-of subxact xid with its parent, what you're caching is the parent xid, which will fail to match subsequent queries. Shouldn't we cache the originally inquired-of xid? The question of whether to cache false results perhaps requires some performance testing. regards, tom lane -- 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] Our trial to TPC-DS but optimizer made unreasonable plan
On Wed, Aug 19, 2015 at 10:32 AM, Qingqing Zhou
zhouqq.postg...@gmail.com wrote:
On Tue, Aug 18, 2015 at 5:59 PM, Kouhei Kaigai kai...@ak.jp.nec.com wrote:
BTW, did you register the patch on the upcoming commit-fest?
Not yet, it is in WIP status.
While I am working on the patch, I found some issues and resort help
here. Patch attached.
Here is an example:
postgres=# explain WITH q AS (
WITH p AS (SELECT * from a) SELECT p.* FROM p JOIN p p1 on p.i=p1.i)
SELECT * FROM q WHERE i = 5;
QUERY PLAN
--
Nested Loop (cost=0.58..5980.16 rows=13 width=8)
- Index Scan using ai on a (cost=0.29..8.36 rows=4 width=8)
Index Cond: (i = 5)
- Index Only Scan using ai on a a_1 (cost=0.29..1159.62
rows=3 width=4)
Index Cond: (i = a.i)
(5 rows)
So far so good. But if we add other references of the CTE q (m1-m,
m-q), we still have some extra CTE scans:
postgres=# explain WITH q AS (
WITH p AS (SELECT * from a) SELECT p.* FROM p JOIN p p1 on
p.i=p1.i), m as (select * from q), m1 as (select * from m)
SELECT * FROM m1 WHERE i = 5;
QUERY PLAN
-
CTE Scan on m (cost=158365985.66..233365985.65 rows=11 width=8)
Filter: (i = 5)
CTE q
- Nested Loop (cost=0.29..91699319.00 rows=33 width=8)
- Seq Scan on a (cost=0.00..1443.00 rows=10 width=8)
- Index Only Scan using ai on a a_1 (cost=0.29..583.65
rows=3 width=4)
Index Cond: (i = a.i)
CTE m
- CTE Scan on q (cost=0.00...66 rows=33 width=8)
(9 rows)
Above two queries essentially the same, but the second one is a
non-optimal plan. The reason is that how my patch works: it put a
substitution in front of SS_process_ctes():
/*
* If there is a WITH list, process each WITH query and build an initplan
! * SubPlan structure for it. Before we process ctes, try to subsitute with
! * subqueries to benefits from global optimization.
*/
if (parse-cteList)
+ {
+ substitute_ctes_with_subqueries(root);
SS_process_ctes(root);
+ }
AFAICS, the substitution only handles cteList within a query block, so
it does not go across the subquery boundary. I can see this is an
issue but can't see a nice way to fix it. Anybody has some recipe?
Regards,
Qingqing
ctes.patch
Description: Binary data
--
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] extend pgbench expressions with functions
On Sun, Jul 26, 2015 at 6:37 PM, Fabien COELHO coe...@cri.ensmp.fr wrote: Hello Heikki, As soon as we add more functions, the way they are documented needs to be reworked too; we'll need to add a table in the manual to list them. Here is a v8 with abs, min, max, random, gaussian et exponential. [...] There is no real doc, WIP... Here is a v9 with a doc. The implicit typing of expressions is improved. I also added two debug functions which allow to show intermediate integer (idebug) or double (ddebug). \set i idebug(random(1, 10)) will print the random value and assign it to i. I updated the defaut scripts, which seems to speed up meta command evaluations. The initial version does less than 2 million evals per second: sh cat before.sql \set naccounts 10 * :scale \setrandom aid 1 :naccounts sh ./pgbench -T 3 -P 1 -f before.sql [...] tps = 1899004.793098 (excluding connections establishing) The updated version does more than 3 million evals per second: sh cat after.sql \set aid random(1, 10 * :scale) sh ./pgbench -T 3 -P 1 -f after.sql [...] tps = 3143168.813690 (excluding connections establishing) Suggestion: A possibility would be to remove altogether the \setrandom stuff as the functionality is available through \set, maybe with an error message to advise about using \set with one of the random functions. That would remove about 200 ugly locs... Another option would be to translate the setrandom stuff into a \set expression, that would maybe save 100 locs for the eval, but keep and expand a little the manual parser part. I have moved this patch to the next CF. -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] src/test/ssl broken on HEAD
Hi all,
The following commit has broken the SSL test suite (embarrassing and
lonely moment):
commit 13d856e177e69083f543d6383eeda9e12ce3c55c
Author: Heikki Linnakangas heikki.linnakan...@iki.fi
Date: Wed Jul 29 19:17:02 2015 +0300
Make TAP tests work on Windows.
[...]
*Michael Paquier*, reviewed by Noah Misch, some additional tweaking by me.
What actually happens is that the SSL suite needs to use a host IP for
the tests, but listen_addresses has been reshuffled in the commit
above. Only HEAD is impacted, and attached is a patch to fix the
problem. I have refactored as well the code so as 127.0.0.1 is not
hardcoded around anymore, and the IP used for the tests is
centralized. The tests also used a log file, but this does not make
sense anymore as all the output from stderr and stdout is captured in
a log file.
Regards,
--
Michael
diff --git a/src/test/ssl/ServerSetup.pm b/src/test/ssl/ServerSetup.pm
index 8c1b517..79d948a 100644
--- a/src/test/ssl/ServerSetup.pm
+++ b/src/test/ssl/ServerSetup.pm
@@ -59,7 +59,8 @@ sub chmod_files
sub configure_test_server_for_ssl
{
- my $tempdir = $_[0];
+ my $tempdir= $_[0];
+ my $serverhost = $_[1];
# Create test users and databases
psql 'postgres', CREATE USER ssltestuser;
@@ -72,6 +73,7 @@ sub configure_test_server_for_ssl
print CONF fsync=off\n;
print CONF log_connections=on\n;
print CONF log_hostname=on\n;
+ print CONF listen_addresses='$serverhost'\n;
print CONF log_statement=all\n;
# enable SSL and set up server key
@@ -94,11 +96,11 @@ sub configure_test_server_for_ssl
print HBA
# TYPE DATABASEUSERADDRESS METHOD\n;
print HBA
-hostssl trustdb ssltestuser 127.0.0.1/32trust\n;
+hostssl trustdb ssltestuser $serverhost/32trust\n;
print HBA
hostssl trustdb ssltestuser ::1/128 trust\n;
print HBA
-hostssl certdb ssltestuser 127.0.0.1/32cert\n;
+hostssl certdb ssltestuser $serverhost/32cert\n;
print HBA
hostssl certdb ssltestuser ::1/128 cert\n;
close HBA;
@@ -121,10 +123,8 @@ sub switch_server_cert
print SSLCONF ssl_crl_file='root+client.crl'\n;
close SSLCONF;
- # Stop and restart server to reload the new config. We cannot use
- # restart_test_server() because that overrides listen_addresses to only all
- # Unix domain socket connections.
-
- system_or_bail 'pg_ctl', 'stop', '-D', $tempdir/pgdata;
- system_or_bail 'pg_ctl', 'start', '-D', $tempdir/pgdata, '-w';
+ # Stop and restart server to reload the new config. We cannot use
+ # restart_test_server() because that overrides listen_addresses to only all
+ # Unix domain socket connections.
+ restart_test_server();
}
diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl
index 5d24d8d..0d6f339 100644
--- a/src/test/ssl/t/001_ssltests.pl
+++ b/src/test/ssl/t/001_ssltests.pl
@@ -27,9 +27,6 @@ my $SERVERHOSTADDR = '127.0.0.1';
my $tempdir = TestLib::tempdir;
-#my $tempdir = tmp_check;
-
-
# Define a couple of helper functions to test connecting to the server.
my $common_connstr;
@@ -43,12 +40,7 @@ sub run_test_psql
'psql', '-A', '-t', '-c', SELECT 'connected with $connstr',
'-d', $connstr ];
- open CLIENTLOG, $tempdir/client-log
- or die Could not open client-log file;
- print CLIENTLOG \n# Running test: $connstr $logstring\n;
- close CLIENTLOG;
-
- my $result = run $cmd, '', $tempdir/client-log, '21';
+ my $result = run_log($cmd);
return $result;
}
@@ -84,7 +76,7 @@ chmod 0600, ssl/client.key;
diag setting up data directory in \$tempdir\...;
start_test_server($tempdir);
-configure_test_server_for_ssl($tempdir);
+configure_test_server_for_ssl($tempdir, $SERVERHOSTADDR);
switch_server_cert($tempdir, 'server-cn-only');
### Part 1. Run client-side tests.
--
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] checkpointer continuous flushing
On Mon, Aug 24, 2015 at 4:15 PM, Fabien COELHO coe...@cri.ensmp.fr wrote: [stuff] Moved to next CF 2015-09. -- Michael -- 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] PATCH: use foreign keys to improve join estimates v1
On Thu, Aug 20, 2015 at 11:25 AM, Tomas Vondra tomas.von...@2ndquadrant.com wrote: On 08/20/2015 03:49 AM, Tomas Vondra wrote: Then on current master, I get these estimates (showing just rows, because that's what matter): [...] Moved to next CF 2015-09. -- Michael -- 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] Make HeapTupleSatisfiesMVCC more concurrent
On 25 August 2015 at 21:51, Tom Lane t...@sss.pgh.pa.us wrote: I don't mean to dismiss the potential for further optimization inside XidInMVCCSnapshot (for instance, the one-XID-cache idea sounds promising); but I think that's material for further research and a separate patch. Patch attached. Doesn't seem worth a separate thread. It's not clear to me if anyone wanted to do further review/testing of this patch, or if I should go ahead and push it (after fixing whatever comments need to be fixed). Push, please. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services xidinmvccsnapshot_cache.v1.patch Description: Binary data -- 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] Foreign join pushdown vs EvalPlanQual
-Original Message-
From: Etsuro Fujita [mailto:fujita.ets...@lab.ntt.co.jp]
Sent: Wednesday, August 26, 2015 5:38 PM
To: Kaigai Kouhei(海外 浩平); Robert Haas
Cc: PostgreSQL-development; 花田茂
Subject: Re: [HACKERS] Foreign join pushdown vs EvalPlanQual
On 2015/08/26 17:05, Kouhei Kaigai wrote:
On 2015/08/26 16:07, Kouhei Kaigai wrote:
Even if we enforce them a new interface specification comfortable to
RDBMS,
we cannot guarantee it is also comfortable to other type of FDW drivers.
Specifically, what kind of points about the patch are specific to RDBMS?
TupleTableSlot *
ExecForeignScan(ForeignScanState *node)
{
+EState *estate = node-ss.ps.state;
+
+if (estate-es_epqTuple != NULL)
+{
+/*
+ * We are inside an EvalPlanQual recheck. If foreign
join, get
next
+ * tuple from subplan.
+ */
+Index scanrelid = ((Scan *)
node-ss.ps.plan)-scanrelid;
+
+if (scanrelid == 0)
+{
+PlanState *outerPlan = outerPlanState(node);
+
+return ExecProcNode(outerPlan);
+}
+}
It might not be specific to RDBMS, however, we cannot guarantee all the FDW
are
comfortable to run the alternative plan node on EPQ recheck.
This design does not allow FDW drivers to implement own EPQ recheck,
possibly
more efficient than built-in logic.
As I said below, EPQ testing is only execute a subplan for a *single*
set of component test tuples, so I think the performance gain by its own
EPQ testing implemented by an FDW would be probably negligible in
practice. No?
If module-X wants to implement the EPQ fallback routine by itself, without
alternative plan, too rich interface design prevents what module-X really
wants to do.
Sorry, I fail to see the need or advantage for module-X to do so, in
practice because I think EPQ testing is only execute a subplan for a
*single* set of component test tuples. Maybe I'm missing something,
though.
You may think execution of alternative plan is the best way for EPQ
rechecks,
however, other folks may think their own implementation is the best for EPQ
rechecks. I never argue which approach is better.
What I point out is freedom/flexibility of implementation choice.
No, I just want to know the need or advantage for that specifically.
I'm not interested in advantage / disadvantage of individual FDW driver's
implementation. It is matter of FDW drivers, not a matter of core PostgreSQL.
The only and significant point I repeatedly emphasized is, it is developer's
choice thus it is important to provide options for developers.
If they want, FDW developer can follow the manner of alternative plan
execution for EPQ rechecks. I never deny your idea, but should be one of
the options we can take.
Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei kai...@ak.jp.nec.com
--
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] Performance improvement for joins where outer side is unique
On Tue, Aug 25, 2015 at 2:25 PM, David Rowley david.row...@2ndquadrant.com wrote: On 24 August 2015 at 14:29, Tom Lane t...@sss.pgh.pa.us wrote: David Rowley david.row...@2ndquadrant.com writes: I have to admit I don't much like it either, originally I had this as an extra property that was only seen in EXPLAIN VERBOSE. Seems like a reasonable design from here. The attached patch has the format in this way. (Note that for non-text output, I'd say the field should come out unconditionally. We only care about abbreviating in text mode.) If that's the case then why do we not enable verbose for all of the non-text outputs? It seems strange to start making exceptions on a case-by-case basis. Moved to CF 2015-09. -- Michael -- 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] pg_dump quietly ignore missing tables - is it bug?
On Sun, Aug 23, 2015 at 10:47 PM, Pavel Stehule pavel.steh...@gmail.com wrote: [blah] fixed Moved to next CF 2015-09. -- Michael -- 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] Foreign join pushdown vs EvalPlanQual
On 2015/08/26 17:05, Kouhei Kaigai wrote:
On 2015/08/26 16:07, Kouhei Kaigai wrote:
Even if we enforce them a new interface specification comfortable to RDBMS,
we cannot guarantee it is also comfortable to other type of FDW drivers.
Specifically, what kind of points about the patch are specific to RDBMS?
TupleTableSlot *
ExecForeignScan(ForeignScanState *node)
{
+EState *estate = node-ss.ps.state;
+
+if (estate-es_epqTuple != NULL)
+{
+/*
+ * We are inside an EvalPlanQual recheck. If foreign join, get
next
+ * tuple from subplan.
+ */
+Index scanrelid = ((Scan *)
node-ss.ps.plan)-scanrelid;
+
+if (scanrelid == 0)
+{
+PlanState *outerPlan = outerPlanState(node);
+
+return ExecProcNode(outerPlan);
+}
+}
It might not be specific to RDBMS, however, we cannot guarantee all the FDW are
comfortable to run the alternative plan node on EPQ recheck.
This design does not allow FDW drivers to implement own EPQ recheck, possibly
more efficient than built-in logic.
As I said below, EPQ testing is only execute a subplan for a *single*
set of component test tuples, so I think the performance gain by its own
EPQ testing implemented by an FDW would be probably negligible in
practice. No?
If module-X wants to implement the EPQ fallback routine by itself, without
alternative plan, too rich interface design prevents what module-X really
wants to do.
Sorry, I fail to see the need or advantage for module-X to do so, in
practice because I think EPQ testing is only execute a subplan for a
*single* set of component test tuples. Maybe I'm missing something, though.
You may think execution of alternative plan is the best way for EPQ rechecks,
however, other folks may think their own implementation is the best for EPQ
rechecks. I never argue which approach is better.
What I point out is freedom/flexibility of implementation choice.
No, I just want to know the need or advantage for that specifically.
Best regards,
Etsuro Fujita
--
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] src/test/ssl broken on HEAD
On Wed, Aug 26, 2015 at 4:35 PM, Michael Paquier
michael.paqu...@gmail.com wrote:
Only HEAD is impacted, and attached is a patch to fix the problem.
Actually this version is better, I forgot to update a comment.
--
Michael
diff --git a/src/test/ssl/ServerSetup.pm b/src/test/ssl/ServerSetup.pm
index 8c1b517..79d948a 100644
--- a/src/test/ssl/ServerSetup.pm
+++ b/src/test/ssl/ServerSetup.pm
@@ -59,7 +59,8 @@ sub chmod_files
sub configure_test_server_for_ssl
{
- my $tempdir = $_[0];
+ my $tempdir= $_[0];
+ my $serverhost = $_[1];
# Create test users and databases
psql 'postgres', CREATE USER ssltestuser;
@@ -72,6 +73,7 @@ sub configure_test_server_for_ssl
print CONF fsync=off\n;
print CONF log_connections=on\n;
print CONF log_hostname=on\n;
+ print CONF listen_addresses='$serverhost'\n;
print CONF log_statement=all\n;
# enable SSL and set up server key
@@ -94,11 +96,11 @@ sub configure_test_server_for_ssl
print HBA
# TYPE DATABASEUSERADDRESS METHOD\n;
print HBA
-hostssl trustdb ssltestuser 127.0.0.1/32trust\n;
+hostssl trustdb ssltestuser $serverhost/32trust\n;
print HBA
hostssl trustdb ssltestuser ::1/128 trust\n;
print HBA
-hostssl certdb ssltestuser 127.0.0.1/32cert\n;
+hostssl certdb ssltestuser $serverhost/32cert\n;
print HBA
hostssl certdb ssltestuser ::1/128 cert\n;
close HBA;
@@ -121,10 +123,6 @@ sub switch_server_cert
print SSLCONF ssl_crl_file='root+client.crl'\n;
close SSLCONF;
- # Stop and restart server to reload the new config. We cannot use
- # restart_test_server() because that overrides listen_addresses to only all
- # Unix domain socket connections.
-
- system_or_bail 'pg_ctl', 'stop', '-D', $tempdir/pgdata;
- system_or_bail 'pg_ctl', 'start', '-D', $tempdir/pgdata, '-w';
+ # Stop and restart server to reload the new config.
+ restart_test_server();
}
diff --git a/src/test/ssl/t/001_ssltests.pl b/src/test/ssl/t/001_ssltests.pl
index 5d24d8d..0d6f339 100644
--- a/src/test/ssl/t/001_ssltests.pl
+++ b/src/test/ssl/t/001_ssltests.pl
@@ -27,9 +27,6 @@ my $SERVERHOSTADDR = '127.0.0.1';
my $tempdir = TestLib::tempdir;
-#my $tempdir = tmp_check;
-
-
# Define a couple of helper functions to test connecting to the server.
my $common_connstr;
@@ -43,12 +40,7 @@ sub run_test_psql
'psql', '-A', '-t', '-c', SELECT 'connected with $connstr',
'-d', $connstr ];
- open CLIENTLOG, $tempdir/client-log
- or die Could not open client-log file;
- print CLIENTLOG \n# Running test: $connstr $logstring\n;
- close CLIENTLOG;
-
- my $result = run $cmd, '', $tempdir/client-log, '21';
+ my $result = run_log($cmd);
return $result;
}
@@ -84,7 +76,7 @@ chmod 0600, ssl/client.key;
diag setting up data directory in \$tempdir\...;
start_test_server($tempdir);
-configure_test_server_for_ssl($tempdir);
+configure_test_server_for_ssl($tempdir, $SERVERHOSTADDR);
switch_server_cert($tempdir, 'server-cn-only');
### Part 1. Run client-side tests.
--
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] Foreign join pushdown vs EvalPlanQual
On 2015/08/26 16:07, Kouhei Kaigai wrote:
I wrote:
Maybe I'm missing something, but why do we need such a flexiblity for
the columnar-stores?
Even if we enforce them a new interface specification comfortable to RDBMS,
we cannot guarantee it is also comfortable to other type of FDW drivers.
Specifically, what kind of points about the patch are specific to RDBMS?
*** 88,93 ForeignRecheck(ForeignScanState *node, TupleTableSlot *slot)
--- 99,122
TupleTableSlot *
ExecForeignScan(ForeignScanState *node)
{
+ EState *estate = node-ss.ps.state;
+
+ if (estate-es_epqTuple != NULL)
+ {
+ /*
+ * We are inside an EvalPlanQual recheck. If foreign join, get
next
+ * tuple from subplan.
+ */
+ Index scanrelid = ((Scan *)
node-ss.ps.plan)-scanrelid;
+
+ if (scanrelid == 0)
+ {
+ PlanState *outerPlan = outerPlanState(node);
+
+ return ExecProcNode(outerPlan);
+ }
+ }
+
return ExecScan((ScanState *) node,
(ExecScanAccessMtd) ForeignNext,
(ExecScanRecheckMtd) ForeignRecheck);
It might not be specific to RDBMS, however, we cannot guarantee all the FDW are
comfortable to run the alternative plan node on EPQ recheck.
This design does not allow FDW drivers to implement own EPQ recheck, possibly
more efficient than built-in logic.
I never deny to run the alternative plan to implement EPQ recheck, according
to the decision by FDW driver, however, it is unacceptable pain to enforce all
the FDW driver to use alternative plan as a solution of EPQ check.
If module-X wants to implement the EPQ fallback routine by itself, without
alternative plan, too rich interface design prevents what module-X really
wants to do.
Sorry, I fail to see the need or advantage for module-X to do so, in
practice because I think EPQ testing is only execute a subplan for a
*single* set of component test tuples. Maybe I'm missing something, though.
You may think execution of alternative plan is the best way for EPQ rechecks,
however, other folks may think their own implementation is the best for EPQ
rechecks. I never argue which approach is better.
What I point out is freedom/flexibility of implementation choice.
Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei kai...@ak.jp.nec.com
--
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] Commitfest remaining Needs Review items
On Tue, Aug 25, 2015 at 4:39 PM, Michael Paquier michael.paqu...@gmail.com wrote: On Mon, Aug 10, 2015 at 4:34 PM, Heikki Linnakangas hlinn...@iki.fi wrote: Hello Hackers, There are a few Needs Review items remaining in the July commitfest. Reviewers, please take action - you are holding up the commitfest. In addition to these items, there are a bunch of items in Ready for Committer state. Committers: please help with those. At this stage, there are currently 26 patches in need of actions for the current CF: And now we are down to 2, the following ones: -- Backpatch Resource Owner reassign locks cache. Still not sure if Andres is planning a backpatch to 9.2. -- self-defined policy for sepgsql regression test. Joe is wrapping up that. The rest has been updated in the CF app. If you have any complaints or remarks, feel free to do so on the thread of the related patch or here that's fine. Regards, -- Michael -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] strange test in psql:startup.c
Hi
if (options.single_txn options.action != ACT_FILE
options.action == ACT_NOTHING)
{
fprintf(stderr, _(%s: -1 can only be used in
non-interactive mode\n), pset.progname);
exit(EXIT_FAILURE);
}
the expression should be probably only?
options.single_txn options.action == ACT_NOTHING)
Regards
Pavel
Re: [HACKERS] Foreign join pushdown vs EvalPlanQual
On 2015/08/26 18:01, Kouhei Kaigai wrote: You may think execution of alternative plan is the best way for EPQ rechecks, however, other folks may think their own implementation is the best for EPQ rechecks. I never argue which approach is better. What I point out is freedom/flexibility of implementation choice. Maybe my explanation was not accurate, but I just want to know use cases, to understand the need to provide the flexiblity. The only and significant point I repeatedly emphasized is, it is developer's choice thus it is important to provide options for developers. If they want, FDW developer can follow the manner of alternative plan execution for EPQ rechecks. I never deny your idea, but should be one of the options we can take. I don't object about your idea either, but I have a concern about that; it looks like that the more flexiblity we provide, the more the FDWs implementing their own EPQ would be subject to an internal change in the core. Best regards, Etsuro Fujita -- 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] Reducing ClogControlLock contention
On Tue, Aug 25, 2015 at 2:21 PM, Simon Riggs si...@2ndquadrant.com wrote: On 22 August 2015 at 15:14, Andres Freund and...@anarazel.de wrote: TransactionIdSetPageStatus() calls TransactionIdSetStatusBit(), which writes an 8 byte variable (the lsn). That's not safe. Agreed, thanks for spotting that. I see this as the biggest problem to overcome with this patch. How about using 64bit atomics or spinlock to protect this variable? With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] Reducing ClogControlLock contention
On 26 August 2015 at 11:40, Amit Kapila amit.kapil...@gmail.com wrote: On Tue, Aug 25, 2015 at 2:21 PM, Simon Riggs si...@2ndquadrant.com wrote: On 22 August 2015 at 15:14, Andres Freund and...@anarazel.de wrote: TransactionIdSetPageStatus() calls TransactionIdSetStatusBit(), which writes an 8 byte variable (the lsn). That's not safe. Agreed, thanks for spotting that. I see this as the biggest problem to overcome with this patch. How about using 64bit atomics or spinlock to protect this variable? Spinlock is out IMHO because this happens on every clog lookup. So it must be an atomic read. I'm wondering if its worth making this work on 32-bit systems at all. The contention problems only occur on higher end servers, so we can just disable this optimization if we aren't on a 64bit server. -- Simon Riggshttp://www.2ndQuadrant.com/ http://www.2ndquadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] proposal: multiple psql option -c
Hi 2015-07-29 21:05 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: Hi here is proof concept patch It should be cleaned, but it demonstrates a work well [pavel@localhost psql]$ ./psql -C 'select 10 x; select 20 y;' -C \l postgres x 10 (1 row) y 20 (1 row) List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) 2015-07-28 18:46 GMT+02:00 Andrew Dunstan and...@dunslane.net: On 07/28/2015 11:52 AM, Pavel Stehule wrote: 2015-07-28 15:16 GMT+02:00 Andrew Dunstan and...@dunslane.net mailto: and...@dunslane.net: On 07/28/2015 12:08 AM, Pavel Stehule wrote: 2015-07-28 5:24 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com: 2015-07-27 21:57 GMT+02:00 Andrew Dunstan and...@dunslane.net mailto:and...@dunslane.net mailto:and...@dunslane.net mailto:and...@dunslane.net: On 07/27/2015 02:53 PM, Pavel Stehule wrote: I am trying to run parallel execution psql -At -c select datname from pg_database postgres | xargs -n 1 -P 3 psql -c select current_database() I don't think it's going to be a hugely important feature, but I don't see a problem with creating a new option (-C seems fine) which would have the same effect as if the arguments were contatenated into a file which is then used with -f. IIRC -c has some special characteristics which means it's probably best not to try to extend it for this feature. ok, I'll try to write patch. I have a question. Can be -C option multiple? The SQL is without problem, but what about \x command? postgres=# \dt \dn select 10; No relations found. List of schemas ┌──┬───┐ │ Name │ Owner │ ╞══╪═══╡ └──┴───┘ (0 rows) \dn: extra argument 10; ignored I don't understand the question. You should include one sql or psql command per -C option, ISTM. e.g. psql -C '\dt' -C '\dn' -C 'select 10;' Isn't that what we're talking about with this whole proposal? I am searching some agreement, how to solve a current -c limits. I am 100% for psql -C '\dt' -C '\dn' -C 'select 10;' I think you're probably best off leaving -c alone. If there are issues to be solved for -c they should be handled separately from the feature we agree on. cheers andrew here is finished patch - cleaned, tested - the significant change is using -g --group-command instead -C [pavel@localhost psql]$ ./psql postgres -g select 10; select 20 -g select 30 ?column? -- 10 (1 row) ?column? -- 20 (1 row) ?column? -- 30 (1 row) Regards Pavel commit 9c71d3dbdc3b81f4f8535c93b69507fe5d9af897 Author: Pavel Stehule pavel.steh...@gooddata.com Date: Wed Aug 26 13:03:56 2015 +0200 inital diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index f996865..750c4ae 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -223,6 +223,20 @@ EOF /varlistentry varlistentry + termoption-g replaceable class=parametercommand(s)/replaceable//term + termoption--group-command=replaceable class=parametercommand(s)/replaceable//term + listitem + para + Specifies that applicationpsql/application is to execute one or + more command strings, replaceable class=parametercommands/replaceable, + and then exit. This is useful in shell scripts. Start-up files + (filenamepsqlrc/filename and filename~/.psqlrc/filename) are + ignored with this option. + /para + /listitem +/varlistentry + +varlistentry termoption-h replaceable class=parameterhostname/replaceable//term termoption--host=replaceable class=parameterhostname/replaceable//term listitem diff --git a/src/bin/psql/help.c b/src/bin/psql/help.c index d3e3114..55aa423 100644 --- a/src/bin/psql/help.c +++ b/src/bin/psql/help.c @@ -79,6 +79,8 @@ usage(unsigned short int pager) fprintf(output, _(
Re: [HACKERS] proposal: multiple psql option -c
other example related to using psql in pipeline [pavel@localhost psql]$ ./psql postgres -q -g vacuum analyze pg_attribute -g \echo :DBNAME postgres
Re: [HACKERS] exposing pg_controldata and pg_config as functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/26/2015 06:33 AM, Stephen Frost wrote: * Joe Conway (m...@joeconway.com) wrote: Issues needing comment: a.) Which items need hiding from non-superusers and should the value be redacted or the entire result set row be suppressed? I'm of the opinion that we need to at least redact it and that what we should do is simply suppress the entire result set until we provide a way for administrators to manage who can access it (eg: default roles, this one would fall under 'pg_monitor', imo). Whatever it is it would have to be available during initdb. And in any case I'm no closer to knowing which rows to hide/redact/suppress other than WAL position. Possibly the thing to do for now would be to revoke public from these? Joe - -- Crunchy Data - http://crunchydata.com PostgreSQL Support for Secure Enterprises Consulting, Training, Open Source Development -BEGIN PGP SIGNATURE- Version: GnuPG v2.0.22 (GNU/Linux) iQIcBAEBAgAGBQJV3c3nAAoJEDfy90M199hlMnYQAJliTc7bJTCndMQ0emN6xV55 DqODtxABxh3kqPmWcvSO08dSZ5yHpCKYgIm8OmRIpfDUwNID1uBXsO5pRd1XVzLr 42OmQ9QauAZ9+f/Rea668U/zgzhnIJXdVsFfAmum516UoR3W1rqW5ggPKgN5YDhC 9Z6ikL1fs6l6l1yrvaefbepS1FLx6wDplhctN+hbEdHw9gwAf67fv7ncaPZ4BRyc hogL4mXoz0fFQz7RDvnR2g0uu17k3imbwzqGiyJwH4+9cfnNLWrBXupKwC06ufWF t3cLh4lLTUhx/2amB0qKMQp1MgVs6r70f5ciFTWvaO0nro0wSGHnIsnqFDOfnv2X kctZreHs7gDAFXWM4Qp45oxTHy6Lfce75IvDfZGZ3y8NOhEHZDqJs6VIdOgCu4h0 RkJE/RrRz7ZtMAhyokxWMZvffYRutLPbXAUvg6TBeDVy1T7SKoQK81IBz/Nkd+Bm WkB/EFklUZw/B2HnDpXRV3tdjAzMAJw22bQi0Y7515K25w7NC2nquzX1eBMGmaqe yDf/gobFg601E9WMjaNoxMGy3Niigk46UsQLGT7RJ/ciojY1gGQh/qd4b1BeJpM0 kRmj0Jsyn0cO8hs6h7jBNBVJjlBhr9ijd4tWaZAk9XqLExPPmGunhcoOMf6ttmvy 533U1P2OKyGBZZissMd4 =dlGD -END PGP SIGNATURE- -- 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] 9.5 release notes
On Thu, Aug 20, 2015 at 04:07:36PM -0700, Peter Geoghegan wrote: On Sat, Jun 13, 2015 at 3:53 PM, Peter Geoghegan p...@heroku.com wrote: I think we should really address this. Attached patch adds a new release note item for it. It also adds to the documentation that explains why users should prefer varchar(n)/text to character(n); the lack of abbreviated key support now becomes a huge disadvantage for character(n), whereas in previous versions the disadvantages were fairly minor. In passing, I updated the existing sort item to reflect that only varchar(n), text, and numeric benefit from the abbreviation optimization (not character types more generally + numeric), and added a note on the effectiveness of the abbreviation optimization alone. A recent e-mail from Kaigai-san [1] reminded me of this item. I really think this limitation of char(n) needs to be documented along the lines I proposed here back in June. Benchmarks like TPC-H use char(n) extensively, since it's faster in other systems. However, PostgreSQL now has hugely inferior sort performance for that type as compared to text/varchar(n). This needs to be highlighted. [1] http://www.postgresql.org/message-id/flat/CAM3SWZRRCs6KAyN-bDsh0_pG=8xm3fvcf1x9dlsvd3wvbt1...@mail.gmail.com#CAM3SWZRRCs6KAyN-bDsh0_pG=8xm3fvcf1x9dlsvd3wvbt1...@mail.gmail.com I have applied the attached patch to document this in the data type docs. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml new file mode 100644 index 8113ddf..4d883ec *** a/doc/src/sgml/datatype.sgml --- b/doc/src/sgml/datatype.sgml *** SELECT '52093.89'::money::numeric::float *** 1139,1145 advantages in some other database systems, there is no such advantage in productnamePostgreSQL/productname; in fact typecharacter(replaceablen/)/type is usually the slowest of ! the three because of its additional storage costs. In most situations typetext/type or typecharacter varying/type should be used instead. /para --- 1139,1146 advantages in some other database systems, there is no such advantage in productnamePostgreSQL/productname; in fact typecharacter(replaceablen/)/type is usually the slowest of ! the three because of its additional storage costs and slower ! sorting. In most situations typetext/type or typecharacter varying/type should be used instead. /para -- 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] exposing pg_controldata and pg_config as functions
* Joe Conway (m...@joeconway.com) wrote: On 08/26/2015 06:33 AM, Stephen Frost wrote: * Joe Conway (m...@joeconway.com) wrote: Issues needing comment: a.) Which items need hiding from non-superusers and should the value be redacted or the entire result set row be suppressed? I'm of the opinion that we need to at least redact it and that what we should do is simply suppress the entire result set until we provide a way for administrators to manage who can access it (eg: default roles, this one would fall under 'pg_monitor', imo). Whatever it is it would have to be available during initdb. And in any case I'm no closer to knowing which rows to hide/redact/suppress other than WAL position. Possibly the thing to do for now would be to revoke public from these? That was my thinking- revoke public from them. The default roles, based on the last patch anyway, are available at initdb time and when system_views.sql is run. Thanks! Stehpen signature.asc Description: Digital signature
Re: [HACKERS] Declarative partitioning
On 18 August 2015 at 11:30, Amit Langote langote_amit...@lab.ntt.co.jp
wrote:
I would like propose $SUBJECT for this development cycle. Attached is a
WIP patch that implements most if not all of what's described below. Some
yet unaddressed parts are mentioned below, too. I'll add this to the
CF-SEP.
Thanks for working on this. It's a great start.
3. Multi-level partitioning
CREATE TABLE table_name
PARTITION OF partitioned_table_name
FOR VALUES values_spec
PARTITION BY {RANGE|LIST} ON (columns_list)
This variant implements a form of so called composite or sub-partitioning
with arbitrarily deep partitioning structure. A table created using this
form has both the relkind RELKIND_PARTITIONED_REL and
pg_class.relispartition set to true.
Multi-level partitioning is probably going to complicate things beyond
sanity.
One RELKIND_PARTITIONED_REL with lots of partitions sounds best to me. We
can still have N dimensions of partitioning (or partitioning and
subpartitioning, if you prefer that term)
The patch does not yet implement any planner changes for partitioned
tables, although I'm working on the same and post updates as soon as
possible. That means, it is not possible to run SELECT/UPDATE/DELETE
queries on partitioned tables without getting:
postgres=# SELECT * FROM persons;
ERROR: could not open file base/13244/106975: No such file or directory
Given that there would be more direct ways of performing partition pruning
decisions with the proposed, it would be nice to utilize them.
Specifically, I would like to avoid having to rely on constraint exclusion
for partition pruning whereby subquery_planner() builds append_rel_list
and the later steps exclude useless partitions.
This is really the heart of this patch/design. You can work for months on
all the rest of this, but you will live or die by how the optimization
works because that is the thing we really need to work well. Previous
attempts ignored this aspect and didn't get committed. It's hard, perhaps
even scary, but its critical. It's the 80/20 rule in reverse - 20% of the
code is 80% of the difficulty.
I suggest you write a partition query test script .sql and work towards
making this work. Not exhaustive and weird tests, but 5-10 key queries that
need to be optimized precisely and quickly. I'm sure that's been done
before.
Will include the following once we start reaching consensus on main parts
of the proposed design/implementation:
* New regression tests
* Documentation updates
* pg_dump, psql, etc.
For reference, some immediately previous discussions:
* On partitioning *
http://www.postgresql.org/message-id/20140829155607.gf7...@eldon.alvh.no-ip.org
* Partitioning WIP patch *
http://www.postgresql.org/message-id/54ec32b6.9070...@lab.ntt.co.jp
If you want to achieve consensus, please write either docs or README files
that explain how this works.
It took me a few seconds to notice deviations from Alvaro's original post.
I shouldn't have to read a full thread to see what the conclusions were,
you need to record them coherently.
Some great examples of such things are
src/backend/optimizer/README
src/backend/access/nbtree/README
Please imagine how far such code would have got without them, then look at
the code comments on the top of each of the functions in that area for
examples of the clarity of design this needs.
Comments welcome!
Yes, comments in code are indeed welcome, as well as the README/docs.
I couldn't see why you invented a new form of Alter Table recursion.
We will need to support multi-row batched COPY.
I'm pleased to see this patch and will stay with it to completion, perhaps
others also. We have 3 more CFs in this release, Nov, Jan, Mar - so this
has a great chance of making it into 9.6. The current patch implements a
bunch of stuff, but its hard to say what, how or why it does it and without
the planner stuff its all moot. My recommendation is we say Returned with
Feedback on this now, looking forward to next patch.
If you submit another patch before Nov, I will review it without waiting
for Nov 1.
There will be much discussion on syntax, but that is not the key point. DDL
Support routines are usually pretty straightforward too, so that can be
left for now.
--
Simon Riggshttp://www.2ndQuadrant.com/
http://www.2ndquadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training Services
Re: [HACKERS] Declarative partitioning
The following review has been posted through the commitfest application: make installcheck-world: not tested Implements feature: not tested Spec compliant: not tested Documentation:not tested Needs planner work and tests of that. ALTER TABLE etc can wait. The new status of this patch is: Waiting on Author -- 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] Join push-down support for foreign tables
On Mon, Mar 16, 2015 at 9:51 PM, Robert Haas robertmh...@gmail.com wrote: 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 area is happening on the other thread, about the custom/foreign join patch, and that Tom and I are suspecting that we do not have the right design here. Your input is needed. [...] Moved for now to the next CF as it was in state Need review. -- Michael -- 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] Foreign join pushdown vs EvalPlanQual
On 2015/08/26 16:07, Kouhei Kaigai wrote: I wrote: Maybe I'm missing something, but why do we need such a flexiblity for the columnar-stores? Even if we enforce them a new interface specification comfortable to RDBMS, we cannot guarantee it is also comfortable to other type of FDW drivers. Specifically, what kind of points about the patch are specific to RDBMS? If module-X wants to implement the EPQ fallback routine by itself, without alternative plan, too rich interface design prevents what module-X really wants to do. Sorry, I fail to see the need or advantage for module-X to do so, in practice because I think EPQ testing is only execute a subplan for a *single* set of component test tuples. Maybe I'm missing something, though. Best regards, Etsuro Fujita -- 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] exposing pg_controldata and pg_config as functions
* Joe Conway (m...@joeconway.com) wrote: Issues needing comment: a.) Which items need hiding from non-superusers and should the value be redacted or the entire result set row be suppressed? I'm of the opinion that we need to at least redact it and that what we should do is simply suppress the entire result set until we provide a way for administrators to manage who can access it (eg: default roles, this one would fall under 'pg_monitor', imo). b.) There is a difference in the formatting of timestamps between the pg_controldata binary and the builtin function. To see it do: diff -c (pg_controldata) \ (psql -qAt -c select rpad(name || ':', 38) || setting from pg_controldata) What I see is: pg_controldata ! pg_control last modified: Tue 25 Aug 2015 08:10:42 PM PDT pg_controldata() ! pg_control last modified: Tue Aug 25 20:10:42 2015 Does it matter? I don't think we can help that, can we? At the least, the pg_controldata() output should match what the GUCs and whatnot tell us to do, but the pg_controldata file needs to include things like the timezone. In the end, I don't believe we need to make them match and trying to would just make things ugly. c.) There is some common code between pg_controldata.c in bin and pg_controldata.c in backend/utils/misc. Specifically the string definitions in printf statements match those in ControlData[], and dbState() and wal_level_str() are in both places. Any opinions on consolidating them in src/common somewhere? Haven't got any great ideas about exactly how to consolidate them, but I do think it'd be good to do so.. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] proposal: multiple psql option -c
Hi 2015-08-26 13:12 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: Hi 2015-07-29 21:05 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com: Hi here is proof concept patch It should be cleaned, but it demonstrates a work well [pavel@localhost psql]$ ./psql -C 'select 10 x; select 20 y;' -C \l postgres x 10 (1 row) y 20 (1 row) List of databases Name| Owner | Encoding | Collate |Ctype| Access privileges ---+--+--+-+-+--- postgres | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | template0 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.UTF-8 | en_US.UTF-8 | =c/postgres + | | | | | postgres=CTc/postgres (3 rows) 2015-07-28 18:46 GMT+02:00 Andrew Dunstan and...@dunslane.net: On 07/28/2015 11:52 AM, Pavel Stehule wrote: 2015-07-28 15:16 GMT+02:00 Andrew Dunstan and...@dunslane.net mailto: and...@dunslane.net: On 07/28/2015 12:08 AM, Pavel Stehule wrote: 2015-07-28 5:24 GMT+02:00 Pavel Stehule pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com mailto:pavel.steh...@gmail.com: 2015-07-27 21:57 GMT+02:00 Andrew Dunstan and...@dunslane.net mailto:and...@dunslane.net mailto:and...@dunslane.net mailto:and...@dunslane.net: On 07/27/2015 02:53 PM, Pavel Stehule wrote: I am trying to run parallel execution psql -At -c select datname from pg_database postgres | xargs -n 1 -P 3 psql -c select current_database() I don't think it's going to be a hugely important feature, but I don't see a problem with creating a new option (-C seems fine) which would have the same effect as if the arguments were contatenated into a file which is then used with -f. IIRC -c has some special characteristics which means it's probably best not to try to extend it for this feature. ok, I'll try to write patch. I have a question. Can be -C option multiple? The SQL is without problem, but what about \x command? postgres=# \dt \dn select 10; No relations found. List of schemas ┌──┬───┐ │ Name │ Owner │ ╞══╪═══╡ └──┴───┘ (0 rows) \dn: extra argument 10; ignored I don't understand the question. You should include one sql or psql command per -C option, ISTM. e.g. psql -C '\dt' -C '\dn' -C 'select 10;' Isn't that what we're talking about with this whole proposal? I am searching some agreement, how to solve a current -c limits. I am 100% for psql -C '\dt' -C '\dn' -C 'select 10;' I think you're probably best off leaving -c alone. If there are issues to be solved for -c they should be handled separately from the feature we agree on. cheers andrew here is finished patch - cleaned, tested - the significant change is using -g --group-command instead -C [pavel@localhost psql]$ ./psql postgres -g select 10; select 20 -g select 30 ?column? -- 10 (1 row) ?column? -- 20 (1 row) ?column? -- 30 (1 row) Regards with -1 option support Pavel commit bef661b7c822f4fe9f004bf55645a3e47e514bc8 Author: Pavel Stehule pavel.steh...@gooddata.com Date: Wed Aug 26 13:03:56 2015 +0200 inital diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml index f996865..750c4ae 100644 --- a/doc/src/sgml/ref/psql-ref.sgml +++ b/doc/src/sgml/ref/psql-ref.sgml @@ -223,6 +223,20 @@ EOF /varlistentry varlistentry + termoption-g replaceable class=parametercommand(s)/replaceable//term + termoption--group-command=replaceable class=parametercommand(s)/replaceable//term + listitem + para + Specifies that applicationpsql/application is to execute one or + more command strings, replaceable class=parametercommands/replaceable, + and then exit. This is useful in shell scripts. Start-up files + (filenamepsqlrc/filename and filename~/.psqlrc/filename) are + ignored with this option. + /para + /listitem +/varlistentry + +varlistentry termoption-h replaceable class=parameterhostname/replaceable//term termoption--host=replaceable class=parameterhostname/replaceable//term listitem diff --git a/src/bin/psql/command.c b/src/bin/psql/command.c index 6181a61..277e980 100644
Re: [HACKERS] WIP: Rework access method interface
On Tue, Aug 25, 2015 at 7:20 PM, Tom Lane t...@sss.pgh.pa.us wrote: Jim Nasby jim.na...@bluetreble.com writes: On 8/25/15 10:56 AM, Tom Lane wrote: I'm good with this as long as all the things that get stored in pg_am are things that pg_class.relam can legitimately reference. If somebody proposed adding an access method kind that was not a relation access method, I'd probably push back on whether that should be in pg_am or someplace else. Would fields in pg_am be overloaded then? No, because the proposal was to reduce pg_am to just amname, amkind (which would be something like 'i' or 's'), and amhandler. Everything specific to a particular type of access method would be shoved down to the level of the C APIs. OK. So, as we mentioned before, if we need to expose something of am parameters at SQL-level then we need to write special functions which would call amhandler and expose it. Did we come to the agreement on this solution? From a SQL standpoint it'd be much nicer to have child tables, though that could potentially be faked with views. I've looked into having actual child tables in the system catalogs, and I'm afraid that the pain-to-reward ratio doesn't look very good. Agree. Teach syscache about inheritance would be overengeneering for this problem. -- Alexander Korotkov Postgres Professional: http://www.postgrespro.com The Russian Postgres Company
Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan
On Mon, Aug 17, 2015 at 6:40 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: I think SortSupport logic provides a reasonable way to solve this kind of problem. For example, btint4sortsupport() informs a function pointer of the fast version of comparator (btint4fastcmp) which takes two Datum argument without indirect memory reference. This mechanism will also make sense for HashAggregate logic, to reduce the cost of function invocations. Please comment on the idea I noticed here. Is this a 9.5-based system? If so, then you'd benefit from the memcmp() pre-check within varstr_cmp() by being on 9.5, since the pre-check is not limited to cases that use text/varchar SortSupport -- this could make a big difference here. If not, then it might be somewhat helpful to add a pre-check that considers total binary equality only before bcTruelen() is ever called. Not so sure about the latter idea, though. I'm not sure if it would help with hash aggregates to use something like SortSupport to avoid fmgr overhead. It might make enough of a difference to matter, but maybe the easier win would come from considering simple binary equality first, and only then using an equality operator (think HOT style checks). That would have the advantage of requiring no per-type/operator class support at all, since it's safe to assume that binary equality is a proxy for equivalence of sort order (or whatever we call the case where 5.00::numeric and 5.000::numeric are considered equal). -- Peter Geoghegan -- 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] [PATCH] Reload SSL certificates on SIGHUP
On 08/26/2015 03:57 AM, Tom Lane wrote: Is it unreasonable of me to ask for the Windows behavior to be fixed at the same time? I dunno. It's perhaps less broken than the Unix behavior, but that doesn't make it desirable. OTOH it might be a significantly larger patch, and I confess I'm not even too sure what we'd have to do. So I think the way to move this forward is to investigate how to hold the SSL config constant until SIGHUP in an EXEC_BACKEND build. If we find out that that's unreasonably difficult, maybe we'll decide that we can live without it; but I'd like to see the question investigated rather than ignored. I think this is a real concern and one that I will look into, to see if it can be fixed with a reasonable amount of work. Andreas -- 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] Our trial to TPC-DS but optimizer made unreasonable plan
Qingqing Zhou zhouqq.postg...@gmail.com writes:
Above two queries essentially the same, but the second one is a
non-optimal plan. The reason is that how my patch works: it put a
substitution in front of SS_process_ctes():
/*
* If there is a WITH list, process each WITH query and build an initplan
! * SubPlan structure for it. Before we process ctes, try to subsitute with
! * subqueries to benefits from global optimization.
*/
if (parse-cteList)
+ {
+ substitute_ctes_with_subqueries(root);
SS_process_ctes(root);
+ }
AFAICS, the substitution only handles cteList within a query block, so
it does not go across the subquery boundary. I can see this is an
issue but can't see a nice way to fix it. Anybody has some recipe?
It seems like you're doing this in fundamentally the wrong place.
What I had in mind in 38448.1430519...@sss.pgh.pa.us was to convert CTEs
into plain subqueries during the prepjointree phase, either just before
or as part of the pull_up_subqueries pass (since you'd want the converted
subquery to be flattened if possible). If you do it later than that,
then you'll have to reinvent a whole bunch of wheels to provide behavior
similar to regular subquery optimization.
regards, tom lane
--
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] Our trial to TPC-DS but optimizer made unreasonable plan
I wrote: What I had in mind in 38448.1430519...@sss.pgh.pa.us was to convert CTEs into plain subqueries during the prepjointree phase, either just before or as part of the pull_up_subqueries pass (since you'd want the converted subquery to be flattened if possible). After looking at the code a bit, IMO the most reasonable thing to do is to include this transformation in inline_set_returning_functions(), perhaps renaming it to something like inline_srfs_and_ctes(). You could invent a separate function instead, but that would require an extra pass over the rangetable, for no particular benefit that I can see; the separate function would have to be called from *exactly* the same set of places as inline_set_returning_functions(), anyway, or it would not work right for multiple levels of query optimization. regards, tom lane -- 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] Custom Scans and private data
On 2015-08-26 00:55:48 +, Kouhei Kaigai wrote:
As Tom pointed out, the primary reason why CustomScan required provider
to save its private data on custom_exprs/custom_private were awareness
of copyObject().
Well, a callback brings that with it as well. I do think it makes sense
to *allow* not to have a callback and rely on copyObject() to do the
work.
In addition, custom_exprs are expected to link expression node to be
processed in setrefs.c and subselect.c because core implementation
cannot know which type of data is managed in private.
Right.
Do you concern about custom_private only?
Yes, pretty much. There's very little chance you can expand the
expression tree with custom expressions and survive the experience. So
custom_exprs etc. makes sense.
I can understand your concern, however, I'm not certain which is the best
way to do.
Regarding of CustomPath and CustomScanState, we expect developer defines
its own data structure delivered from these types, like:
typedef struct {
CustomPath cpath;
List *host_quals; /* RestrictInfo run on host */
List *dev_quals; /* RestrictInfo run on device */
} GpuScanPath;
On the other hand, CustomScan node prohibit to have these manner because
of copyObject() awareness. If we can have similar design, it looks to me
harmonious design.
Here is my random thought to realize it, even though current specification
require CustomScan to follow existing copyObject() manner.
- _copyCustomScan() needs to allow to allocate larger than sizeof(CustomScan),
according to the requirement by custom-scan provider.
- We may need to have a utility function to copy the common part.
It is not preferable to implement by custom-scan provider itself.
- For upcoming readfuncs.c support, I don't want to have READ_XXX_FIELD()
macro on the extension side. Even though pg_strtok() is a public function,
_readBitmapset() is static function.
- Is custom_private deprecated? Also, do we force to have CopyObjectCustomScan()
and potentially TextReadCustomScan()?
I want to keep custom_private as-is, because of very simple custom-scan
provider which has at most several primitive values as private one.
Even if we have extra
callbacks like CopyObjectCustomScan() and TextReadCustomScan(),
how do we care about the situation when core implementation needs to
know the location of expression nodes? Is custom_exprs retained as is?
Yes.
In the earlier version of CustomScan interface had individual
callbacks on setrefs.c and subselect.c, however, its structure
depended on the current implementation too much, then we moved
to the implementation to have two individual private fields
rather than callbacks on outfuncs.c.
I agree with that choice.
On the other hands, I'm inclined to think TextOutCustomScan() might
be a misdesign to support serialize/deserialize via readfuncs.c.
http://www.postgresql.org/message-id/9A28C8860F777E439AA12E8AEA7694F80111D04
f...@bpxm15gp.gisp.nec.co.jp
I think it shall be deprecated rather then enhancement.
Well, right now there's no support for reading/writing plans at all. But
if we add it, TextOutCustomScan() seems like a small problem in
comparison to others. CustomScan contains pointers, that's definitely
not something you can ship over the wire and expect to work. We'll
probably have to store a soname + function name instead.
It may not be a long future. The ParallelAppend feature, I've discussed
in another thread, needs capability of plan tree serialization, and under
the development:
https://github.com/kaigai/sepgsql/blob/fappend/src/backend/nodes/readfuncs.c#L1506
I thought we may need to define a new DDL to associate a particular custom-
scan name with a set of callback pointer table, using persistent system
catalog. Your idea, soname + symbol name, may be a solution.
More generally I rather doubt that it'll always make sense to
serialize/deserialize in a generic manner between different backends. It
very well can make sense to refer to backend-local state in a plan - you
need to be able to take that into account upon
serialization/deserialization. Consider e.g. a connection id for an FDW
that uses pooling.
+1. I also prefer more generic mechanism to serialize/deserialize, rather
than custom-scan specific.
Thanks,
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei kai...@ak.jp.nec.com
--
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] 9.4 broken on alpha
On Wed, Aug 26, 2015 at 12:49:46PM -0400, Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: A buildfarm machine would be mandatory, too. That, however, is not negotiable. Right. I think the still-open question around PostgreSQL on Alpha is whether 9.1 through 9.4 are meaningfully supported there. Step one for anyone interested in Alpha support is to activate a buildfarm member covering that range of versions. Without that, the PostgreSQL community is just listening for bug fix contributions. On Wed, Aug 26, 2015 at 01:34:38PM -0400, Tom Lane wrote: Andres Freund and...@anarazel.de writes: But I really strongly object to re-introducing alpha support. Having to care about data dependency barriers is a huge pita, and it complicates code for everyone. And we'd have to investigate a lot of code to actually make it work reliably. For what benefit? I hear you, but that's only an issue for multi-CPU machines no? If we just say we doubt this works on multi-CPU Alphas, if it breaks you get to keep both pieces, then we're basically at the same place we were before. To be clear: I don't want to do the work you're speaking of, either. But if we have people who were successfully using PG on Alphas before, the coherency issues must not have been a problem for them. Can't we just (continue to) ignore the issue? The landscape changed with the 9.5 cycle's push to use more lock-free algorithms. Dropping Alpha support simplified review for those algorithms. True, we could ignore Alpha for review purposes and accept unstudied damage to reliability on Alpha. To some extent, that characterizes any platform whose test reports don't reach us. It's different when we know Alpha has special needs and we make changes in the area of those needs without even attempting to meet them. We made a decision to instead break compatibility explicitly, and I don't think this thread has impugned that decision. As it is, we've implicitly prepared to ship Alpha-supporting PostgreSQL 9.4 until 2019, by which time the newest Alpha hardware will be 15 years old. Computer museums would be our only audience for continued support. I do have a sentimental weakness for computer museums, but not at the price of drag on important performance work. nm -- 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] Custom Scans and private data
On 2015-08-26 00:55:48 +, Kouhei Kaigai wrote: As Tom pointed out, the primary reason why CustomScan required provider to save its private data on custom_exprs/custom_private were awareness of copyObject(). Well, a callback brings that with it as well. I do think it makes sense to *allow* not to have a callback and rely on copyObject() to do the work. In addition, custom_exprs are expected to link expression node to be processed in setrefs.c and subselect.c because core implementation cannot know which type of data is managed in private. Right. Do you concern about custom_private only? Yes, pretty much. There's very little chance you can expand the expression tree with custom expressions and survive the experience. So custom_exprs etc. makes sense. Even if we have extra callbacks like CopyObjectCustomScan() and TextReadCustomScan(), how do we care about the situation when core implementation needs to know the location of expression nodes? Is custom_exprs retained as is? Yes. In the earlier version of CustomScan interface had individual callbacks on setrefs.c and subselect.c, however, its structure depended on the current implementation too much, then we moved to the implementation to have two individual private fields rather than callbacks on outfuncs.c. I agree with that choice. On the other hands, I'm inclined to think TextOutCustomScan() might be a misdesign to support serialize/deserialize via readfuncs.c. http://www.postgresql.org/message-id/9a28c8860f777e439aa12e8aea7694f80111d...@bpxm15gp.gisp.nec.co.jp I think it shall be deprecated rather then enhancement. Well, right now there's no support for reading/writing plans at all. But if we add it, TextOutCustomScan() seems like a small problem in comparison to others. CustomScan contains pointers, that's definitely not something you can ship over the wire and expect to work. We'll probably have to store a soname + function name instead. More generally I rather doubt that it'll always make sense to serialize/deserialize in a generic manner between different backends. It very well can make sense to refer to backend-local state in a plan - you need to be able to take that into account upon serialization/deserialization. Consider e.g. a connection id for an FDW that uses pooling. Greetings, Andres Freund -- 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] Our trial to TPC-DS but optimizer made unreasonable plan
-Original Message- From: pgsql-hackers-ow...@postgresql.org [mailto:pgsql-hackers-ow...@postgresql.org] On Behalf Of Peter Geoghegan Sent: Thursday, August 27, 2015 8:31 AM To: Kaigai Kouhei(海外 浩平) Cc: Greg Stark; PostgreSQL-development Subject: Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable plan On Mon, Aug 17, 2015 at 6:40 AM, Kouhei Kaigai kai...@ak.jp.nec.com wrote: I think SortSupport logic provides a reasonable way to solve this kind of problem. For example, btint4sortsupport() informs a function pointer of the fast version of comparator (btint4fastcmp) which takes two Datum argument without indirect memory reference. This mechanism will also make sense for HashAggregate logic, to reduce the cost of function invocations. Please comment on the idea I noticed here. Is this a 9.5-based system? If so, then you'd benefit from the memcmp() pre-check within varstr_cmp() by being on 9.5, since the pre-check is not limited to cases that use text/varchar SortSupport -- this could make a big difference here. If not, then it might be somewhat helpful to add a pre-check that considers total binary equality only before bcTruelen() is ever called. Not so sure about the latter idea, though. My measurement is done on v9.5 based system. So, it also seems to me replacement of CHAR(n) by VARCHAR(n) will make sense. I'm not sure if it would help with hash aggregates to use something like SortSupport to avoid fmgr overhead. It might make enough of a difference to matter, but maybe the easier win would come from considering simple binary equality first, and only then using an equality operator (think HOT style checks). That would have the advantage of requiring no per-type/operator class support at all, since it's safe to assume that binary equality is a proxy for equivalence of sort order (or whatever we call the case where 5.00::numeric and 5.000::numeric are considered equal). My presumption was wrong, at least not major portion, according to the perf result. So, I don't think elimination of fmgr overhead has the first priority. However, shortcut pass of equality checks seems to me a great leap, to avoid strict equality checks implemented per data type; that often takes complicated logic. Probably, it is more intelligent to apply this binary equality proxy on only problematic data types, like bpchar(n). But less effective on simple data types, like int4. On the other hands, one other big portion of HashAggregate is calculation of hash-value by all the grouping key. It may be beneficial to have an option to reference the result attribute of underlying plan. It potentially allows co-processor to compute hash-value instead of CPU. Thanks, -- NEC Business Creation Division / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Allow replication roles to use file access functions
Hi all,
As of now, file access functions in genfile.c can only be used by
superusers. This proposal is to relax those functions so as
replication users can use them as well. Here are the functions aimed
by this patch:
- pg_stat_file
- pg_read_binary_file
- pg_read_file
- pg_ls_dir
The main argument for this change is that pg_rewind makes use of those
functions, forcing users to use a superuser role when rewinding a
node. And with this patch, we could allow replication roles to do the
same. Another argument in favor of this change is to allow replication
users to dump directly the contents of PGDATA via SQL, though I don't
believe that there are many people doing so these days.
Also, replication roles can already have an access to the contents of
PGDATA by taking a base backup for example, so this change looks
logical to me, even if we filter out some files in a base backup,
though I could not find any arguments to not let a replication user
have a look at them via those functions. A patch is attached, I am
adding it as well to the next CF.
Regards,
--
Michael
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index b3b78d2..d45a591 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -17877,7 +17877,8 @@ postgres=# SELECT * FROM pg_xlogfile_name_offset(pg_stop_backup());
database cluster directory and the varnamelog_directory/ can be
accessed. Use a relative path for files in the cluster directory,
and a path matching the varnamelog_directory/ configuration setting
-for log files. Use of these functions is restricted to superusers.
+for log files. Use of these functions is restricted to superusers and
+replication roles.
/para
table id=functions-admin-genfile-table
diff --git a/src/backend/utils/adt/genfile.c b/src/backend/utils/adt/genfile.c
index c4eb10d..046331d 100644
--- a/src/backend/utils/adt/genfile.c
+++ b/src/backend/utils/adt/genfile.c
@@ -194,10 +194,10 @@ pg_read_file(PG_FUNCTION_ARGS)
char *filename;
text *result;
- if (!superuser())
+ if (!superuser() !has_rolreplication(GetUserId()))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg(must be superuser to read files;
+ (errmsg(must be superuser or replication role to read files;
/* handle optional arguments */
if (PG_NARGS() = 3)
@@ -235,10 +235,10 @@ pg_read_binary_file(PG_FUNCTION_ARGS)
char *filename;
bytea *result;
- if (!superuser())
+ if (!superuser() !has_rolreplication(GetUserId()))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg(must be superuser to read files;
+ (errmsg(must be superuser or replication role to read files;
/* handle optional arguments */
if (PG_NARGS() = 3)
@@ -312,10 +312,10 @@ pg_stat_file(PG_FUNCTION_ARGS)
TupleDesc tupdesc;
bool missing_ok = false;
- if (!superuser())
+ if (!superuser() !has_rolreplication(GetUserId()))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg(must be superuser to get file information;
+ (errmsg(must be superuser or replication role to get file information;
/* check the optional argument */
if (PG_NARGS() == 2)
@@ -398,10 +398,10 @@ pg_ls_dir(PG_FUNCTION_ARGS)
directory_fctx *fctx;
MemoryContext oldcontext;
- if (!superuser())
+ if (!superuser() !has_rolreplication(GetUserId()))
ereport(ERROR,
(errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
- (errmsg(must be superuser to get directory listings;
+ (errmsg(must be superuser or replication role to get directory listings;
if (SRF_IS_FIRSTCALL())
{
--
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] Our trial to TPC-DS but optimizer made unreasonable plan
-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us]
Sent: Thursday, August 27, 2015 9:03 AM
To: Qingqing Zhou
Cc: Kaigai Kouhei(海外 浩平); Greg Stark; PostgreSQL-development
Subject: Re: [HACKERS] Our trial to TPC-DS but optimizer made unreasonable
plan
Qingqing Zhou zhouqq.postg...@gmail.com writes:
Above two queries essentially the same, but the second one is a
non-optimal plan. The reason is that how my patch works: it put a
substitution in front of SS_process_ctes():
/*
* If there is a WITH list, process each WITH query and build an initplan
! * SubPlan structure for it. Before we process ctes, try to subsitute with
! * subqueries to benefits from global optimization.
*/
if (parse-cteList)
+ {
+ substitute_ctes_with_subqueries(root);
SS_process_ctes(root);
+ }
AFAICS, the substitution only handles cteList within a query block, so
it does not go across the subquery boundary. I can see this is an
issue but can't see a nice way to fix it. Anybody has some recipe?
It seems like you're doing this in fundamentally the wrong place.
What I had in mind in 38448.1430519...@sss.pgh.pa.us was to convert CTEs
into plain subqueries during the prepjointree phase, either just before
or as part of the pull_up_subqueries pass (since you'd want the converted
subquery to be flattened if possible). If you do it later than that,
then you'll have to reinvent a whole bunch of wheels to provide behavior
similar to regular subquery optimization.
Hmm... My suggestion might not be reasonable. Sorry.
--
NEC Business Creation Division / PG-Strom Project
KaiGai Kohei kai...@ak.jp.nec.com
--
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] Foreign join pushdown vs EvalPlanQual
On 2015/08/26 18:01, Kouhei Kaigai wrote: You may think execution of alternative plan is the best way for EPQ rechecks, however, other folks may think their own implementation is the best for EPQ rechecks. I never argue which approach is better. What I point out is freedom/flexibility of implementation choice. Maybe my explanation was not accurate, but I just want to know use cases, to understand the need to provide the flexiblity. Let's assume the following situation: Someone wants to implement FPGA acceleration feature on top of FDW. (You may know the earliest PG-Strom was built on FDW interface) It enables to run SQL join workloads on FPGA device, but has equivalent fallback routines to be executed if FPGA returned an error. On EPQ check case, it is quite natural that he wants to re-use this fallback routine to validate EPQ tuple. Alternative plan may consume additional (at least not zero) memory and other system resource. As I have said repeatedly, it is software design decision by the author of extension. Even if it consumes 100 times larger memory and 1000 times slower, it is his decision and responsibility. Why he has to be forced to use a particular logic despite his intension? The only and significant point I repeatedly emphasized is, it is developer's choice thus it is important to provide options for developers. If they want, FDW developer can follow the manner of alternative plan execution for EPQ rechecks. I never deny your idea, but should be one of the options we can take. I don't object about your idea either, but I have a concern about that; it looks like that the more flexiblity we provide, the more the FDWs implementing their own EPQ would be subject to an internal change in the core. We never guarantee interface compatibility across major versions. All we can say is 'best efforts'. So, it is always role of extension owner, as long as he continue to maintain his module. Thanks, -- NEC Business Creation Division / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.com -- 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] Our trial to TPC-DS but optimizer made unreasonable plan
On 27/08/15 13:36, Kouhei Kaigai wrote: [...] My measurement is done on v9.5 based system. So, it also seems to me replacement of CHAR(n) by VARCHAR(n) will make sense. Is there any reason to not simply use text instead of CHAR(n) or VARCHAR(n)? [...] -Gavin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
