[HACKERS] broken documentation: BackgroundWorkerInitializeConnection(NULL, NULL);
Hi The documentation (or this feature) is broken still If dbname is NULL or dboid is InvalidOid, the session is not connected to any particular database, but shared catalogs can be accessed. If username is NULL or useroid is InvalidOid, the process will run as the superuser created during initdb. A background worker can only call one of these two functions, and only once. It is not possible to switch databases. But it fails with error: FATAL: database 0 does not exist I found some older thread related to this topic http://www.postgresql.org/message-id/13994.1376145...@sss.pgh.pa.us Regards Pavel
Re: [HACKERS] a few thoughts on the schedule
On Wed, May 13, 2015 at 8:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: I am already concerned about some of the commits that have gone in very recently, particularly these: There is going to need to be a mop-up period, and we ought to be willing to revert anything we feel wasn't really ready. I don't feel that those decisions need to be made in a hurry though. I'm envisioning taking about a month to look more closely at committed patches and see what needs to be cleaned up or undone altogether. I think doing post-commit review is really a good thing especially for the patches which have more impact. One way to achieve could be that we can identify all the patches that can have high impact (at least feature patches, it shouldn't be difficult to identify such patches) and some of the senior members like you can review them thoroughly after the feature freeze (at end of development cycle), ofcourse it is better if that can be done during development, but it seems that doesn't happen many of the times. So if we add a phase after feature freeze and before first release of the version, that can avoid some serious problems that the project faces during beta or post release. With Regards, Amit Kapila. EnterpriseDB: http://www.enterprisedb.com
Re: [HACKERS] PATCH: pgbench allow '=' in \set
Fabien COELHO coe...@cri.ensmp.fr writes: Another option, breaking backward compatibility, would be to decide that backslash commands have to be terminated by a semicolon token. I do not like it much, as it is inconsistent/incompatible with psql. [...] multi-line SQL queries. If we wanted to make that work, the best option might be to duplicate the backend lexer into pgbench just as we already do with psql. [...] I somewhat lean toward this second option, because I think it will be a lot more convenient in the long run. We'll probably get some complains about breaking people's pgbench scripts, but I'd personally be prepared to accept that as the price of progress. For an actual lexer: currently there is no real lexer for SQL commands in pgbench, the line is just taken as is, so that would mean adding another one, although probably a simplified one would do. Probably not; we'd have to borrow psql's, hook line and sinker. Even if you could come up with something creative that only failed occasionally, it would be better from a maintenance perspective if it were as much like the existing lexers as possible. (In this context it's worth pointing out that we already have trouble with keeping psql's and ecpg's lexers in step with the core code. Adding yet another one, not quite like any of the other ones, doesn't seem appetizing. If flex were better at factorizing .l files maybe this wouldn't be quite so painful, but it ain't :-() I tend to agree with the bottom line that that's just more complication than is justified. I sympathize with Robert's dislike for backslash continuations; but doing it the other way would be a huge amount of up-front work and a nontrivial amount of continuing maintenance, for which we would not get thanks from users but rather bitching about how we broke their scripts. Seems like a lose-lose situation. 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] Auditing extension for PostgreSQL (Take 2)
Robert, all, * Robert Haas (robertmh...@gmail.com) wrote: On Mon, May 11, 2015 at 9:07 PM, David Steele da...@pgmasters.net wrote: The attached v12 patch removes the code that became redundant with Alvaro committing the event trigger/deparse work. I've updated the regression tests to reflect the changes, which were fairly minor and add additional information to the output. There are no longer any #ifdef'd code blocks. This is not a full review, but just a few thoughts... Thanks for that. David and I worked through your suggestions, a number of my own, and some general cleanup and I've now pushed it. What happens if the server crashes? Presumably, audit records emitted just before the crash can be lost, possibly even if the transaction went on to commit. That's no worse than what is already the case for regular logging, of course, but it's maybe a bit more relevant here because of the intended use of this information. Right, if the server crashes then we may lose information- but there should be a log somewhere of the crash. I didn't do much in the way of changes to the documentation, but this is definitely an area where we should make it very clear what happens. Braces around single-statement blocks are not PostgreSQL style. Fixed those and a number of other things, like not having entire functions in if() blocks. I wonder if driving the auditing system off of the required permissions is really going to work well. That means that decisions we make about permissions enforcement will have knock-on effects on auditing. For example, the fact that we check permission on a view rather than on the underlying tables will (I think) flow through to how the auditing happens. The checks against the permissions are independent and don't go through our normal permission checking system, so I'm not too worried about this aspect. I agree that we need to be vigilant and consider the impact of changes to the permissions system, but there are also quite a few regression tests in pg_audit and those should catch a lot of potential issues. +stackItem-auditEvent.commandTag == T_DoStmt Use IsA(..., DoStmt) for this kind of test. There are many instances of this pattern in the patch; they should al be fixed. Unfortunately, that's not actually a Node, so we can't just use IsA. We considered making it one, but that would mean IsA() would return a T_DoStmt or similar for something that isn't actually a T_DoStmt (it's an auditEvent of a T_DoStmt). Still, I did go through and look at these cases and made changes to improve them and clean things up to be neater. The documentation and comments in this patch are, by my estimation, somewhat below our usual standard. For example: + DefineCustomBoolVariable(pg_audit.log_notice, +Raise a notice when logging, This was improved, but I'm sure more can be done. Suggestions welcome. Granted, there is a fuller explanation in the documentation, but that doesn't make that explanation particularly good. (One might also wonder why the log level isn't fully configurable instead of offering only two choices.) This was certainly a good point and we added support for choosing the log level to log it at. I don't want to focus too much on this particular example. The comments and documentation really deserve a bit more attention generally than they have gotten thus far. I am not saying they are bad. I am just saying that, IMHO, they are not as good as we typically try to make them. I've done quite a bit of rework of the comments and will be working on improving the documentation also. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] PATCH: pgbench allow '=' in \set
On Thu, May 14, 2015 at 3:20 AM, Fabien COELHO fabien.coe...@mines-paristech.fr wrote: I loathe violently the convention of using a backslash at the end of a line, because it's too easy to write backslash-space-newline or backslash-tab-newline when you meant to write backslash-newline. But maybe we should do it anyway. We certainly need some solution to that problem, because the status quo is monumentally annoying, and that might be the least bad solution available. I survive with that in bash/make/python... Yeah. Another option, breaking backward compatibility, would be to decide that backslash commands have to be terminated by a semicolon token. I do not like it much, as it is inconsistent/incompatible with psql. True, but anything will be, as far as backslash commands are concerned. psql doesn't support continuation lines in backslash commands at all. [...] multi-line SQL queries. If we wanted to make that work, the best option might be to duplicate the backend lexer into pgbench just as we already do with psql. [...] I somewhat lean toward this second option, because I think it will be a lot more convenient in the long run. We'll probably get some complains about breaking people's pgbench scripts, but I'd personally be prepared to accept that as the price of progress. For an actual lexer: currently there is no real lexer for SQL commands in pgbench, the line is just taken as is, so that would mean adding another one, although probably a simplified one would do. I think what we'd do is extend the expression lexer to cover everything in the file. To conclude, I'm rather for continuations, despite their ugliness, because (1) it is much easier (just a very small change in read_line_from_file) and (2) it is backward compatible, so no complaints handle. Those are certainly points to consider. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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: pgbench allow '=' in \set
Hello Robert, Also, having ; as a end of commands could also help by allowing multiline commands, but that would break compatibility. Maybe allowing continuations (\\\n) would be an acceptable compromise. I loathe violently the convention of using a backslash at the end of a line, because it's too easy to write backslash-space-newline or backslash-tab-newline when you meant to write backslash-newline. But maybe we should do it anyway. We certainly need some solution to that problem, because the status quo is monumentally annoying, and that might be the least bad solution available. I survive with that in bash/make/python... Another option, breaking backward compatibility, would be to decide that backslash commands have to be terminated by a semicolon token. I do not like it much, as it is inconsistent/incompatible with psql. [...] multi-line SQL queries. If we wanted to make that work, the best option might be to duplicate the backend lexer into pgbench just as we already do with psql. [...] I somewhat lean toward this second option, because I think it will be a lot more convenient in the long run. We'll probably get some complains about breaking people's pgbench scripts, but I'd personally be prepared to accept that as the price of progress. For an actual lexer: currently there is no real lexer for SQL commands in pgbench, the line is just taken as is, so that would mean adding another one, although probably a simplified one would do. To conclude, I'm rather for continuations, despite their ugliness, because (1) it is much easier (just a very small change in read_line_from_file) and (2) it is backward compatible, so no complaints handle. -- Fabien. -- 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] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file
On Thu, May 14, 2015 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, May 14, 2015 at 2:10 AM, Andrew Dunstan and...@dunslane.net wrote: How about if we simply abort if we find a non-symlink where we want the symlink to be, and only remove something that is actually a symlink (or a junction point, which is more or less the same thing)? We can do that way and for that I think we need to use rmdir instead of rmtree in the code being discussed (recovery path), OTOH we should try to minimize the errors raised during recovery. I'm not sure I understand this issue in detail, but why would using rmtree() on something you expect to be a symlink ever be a good idea? It seems like if things are the way you expect them to be, it has no benefit, but if they are different from what you expect, you might blow away a ton of important data. Maybe I am just confused. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [COMMITTERS] pgsql: Add pg_audit, an auditing extension
Stephen Frost sfr...@snowman.net writes: * Tom Lane (t...@sss.pgh.pa.us) wrote: At least on dromedary, this seems to be the problem: pg_audit.c: In function 'stack_pop': pg_audit.c:387: warning: format '%ld' expects type 'long int', but argument 3 has type 'int64' pg_audit.c: In function 'stack_valid': pg_audit.c:406: warning: format '%ld' expects type 'long int', but argument 3 has type 'int64' pg_audit.c:406: warning: format '%ld' expects type 'long int', but argument 4 has type 'int64' pg_audit.c: In function 'log_audit_event': pg_audit.c:676: warning: format '%ld' expects type 'long int', but argument 4 has type 'int64' pg_audit.c:676: warning: format '%ld' expects type 'long int', but argument 5 has type 'int64' Will push a fix shortly and we'll see what happens. Ah, ok. Pushed that, but some further notes: * The actual audit reports ought to be ereport() not elog(). I made them so but did not insert an errcode(). ISTM that it would likely be a good thing to assign a not-used-for-any-other-purpose errcode for this, but I'm not terribly sure what category to put it in. * The comments in the code betray utter ignorance of how logging actually works, in particular this: * Administrators can choose which log level the audit log is to be logged * at. The default level is LOG, which goes into the server log but does * not go to the client. Set to NOTICE in the regression tests. All the user has to do is change client_min_messages and he'll see all the reports, which means if you think that letting the user see the audit reports is a security problem then you have a hole a mile wide. (I assume BTW that we're not considering it a security problem that superusers can trivially escape auditing.) 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
[HACKERS] Changes to backup.sgml
-hackers, After my brain flatulence last week on backups, I decided to read the docs again. There are some improvements that I would like to make and wanted some feedback: 1. File System Level Backup The section should be a note within the larger document. It is largely a legacy section from before 8.3. 2. Rework the paragraph about consistent snapshots into its own section 3. Push the rsync paragraph (and edit where appropriate) within the continuous archiving section. 3a. Add information about robocopy (windows rsync) 4. Move continuous archiving up above everything except pg_dump. Perhaps change time to Online Backup. 4a. I want to do some general rewording, there are some places where the documentation is not clear. I can just do this and then let the reviewers have their say. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ 503-667-4564 PostgreSQL Centered full stack support, consulting and development. Announcing I'm offended is basically telling the world you can't control your own emotions, so everyone else should do it for you. -- 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] Final Patch for GROUPING SETS
On 2015-05-14 09:16:10 +0100, Andrew Gierth wrote: Andres A rough sketch of what I'm thinking of is: I'm not sure I'd do it quite like that. It was meant as a sketch, so there's lots of things it's probably missing ;) Rather, have a wrapper function get_outer_tuple that calls ExecProcNode and, if appropriate, writes the tuple to a tuplesort before returning it; use that in place of ExecProcNode in agg_retrieve_direct and when building the hash table. Hm. I'd considered that, but thought it might end up being more complex for hashing support. I'm not exactly sure why I thought that tho. -- 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] trust authentication behavior
Andrew Dunstan and...@dunslane.net writes: On 05/14/2015 11:59 AM, Robert Haas wrote: On Thu, May 14, 2015 at 5:16 AM, Denis Kirjanov k...@itsirius.su wrote: Is it possible to restrict the trust auth method to accept local connections only using the selinux policy? I would guess that it probably is, but I can't tell you how. I would have guessed not :-) Yeah. AFAIK selinux is strictly a kernel-level facility so it can only enforce things that are visible to the kernel. PG's choice of auth method isn't. 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] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file
On 05/14/2015 10:52 AM, Robert Haas wrote: On Thu, May 14, 2015 at 12:12 AM, Amit Kapila amit.kapil...@gmail.com wrote: On Thu, May 14, 2015 at 2:10 AM, Andrew Dunstan and...@dunslane.net wrote: How about if we simply abort if we find a non-symlink where we want the symlink to be, and only remove something that is actually a symlink (or a junction point, which is more or less the same thing)? We can do that way and for that I think we need to use rmdir instead of rmtree in the code being discussed (recovery path), OTOH we should try to minimize the errors raised during recovery. I'm not sure I understand this issue in detail, but why would using rmtree() on something you expect to be a symlink ever be a good idea? It seems like if things are the way you expect them to be, it has no benefit, but if they are different from what you expect, you might blow away a ton of important data. Maybe I am just confused. The suggestion is to get rid of using rmtree. Instead, if we find a non-symlink in pg_tblspc we'll make the user clean it up before we can continue. So your instinct is in tune with my suggestion. 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] proposal: contrib module - generic command scheduler
On 5/14/15 1:36 AM, Pavel Stehule wrote: I don't think we want to log statements, but we should be able to log when a job has run and whether it succeeded or not. (log in a table, not just a logfile). This isn't something that can be done at higher layers either; only the scheduler will know if the job failed to even start, or whether it tried to run the job. I don't agree - generic scheduler can run your procedure, and there you can log start, you can run other commands and you can log result (now there is no problem to catch any production nonfatal exception). And what happens when the job fails to even start? You get no logging. Personally I afraid about responsibility to maintain this log table - when and by who it should be cleaned, who can see results, ... This is job for top end scheduler. Only if the top-end scheduler has callbacks for everytime the bottom-end scheduler tries to start a job. Otherwise, the top has no clue what the bottom has actually attempted. To be clear, I don't think these need to be done in a first pass. I am concerned about not painting ourselves into a corner though. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] [COMMITTERS] pgsql: Add pg_audit, an auditing extension
* Tom Lane (t...@sss.pgh.pa.us) wrote: Stephen Frost sfr...@snowman.net writes: Ah, ok. Pushed that, but some further notes: Thanks! Looking much better. * The actual audit reports ought to be ereport() not elog(). I made them so but did not insert an errcode(). ISTM that it would likely be a good thing to assign a not-used-for-any-other-purpose errcode for this, but I'm not terribly sure what category to put it in. Right, I had seen that too and had intended to change it, but somehow missed it in the other changes I was doing. I'll take a look at the categories and try to figure out what makes sense. * The comments in the code betray utter ignorance of how logging actually works, in particular this: * Administrators can choose which log level the audit log is to be logged * at. The default level is LOG, which goes into the server log but does * not go to the client. Set to NOTICE in the regression tests. I had rewored that last night and will reword it again to be more clear. All the user has to do is change client_min_messages and he'll see all the reports, which means if you think that letting the user see the audit reports is a security problem then you have a hole a mile wide. There are certainly use-cases for this where that's not an issue and also ones where the user wouldn't be able to use pg_audit due to this. I'll update the docs to make the risk of this clear. At least for the use-cases we've been involved in, they've not been concerned about this. Still, any thoughts you have on this would certainly be welcome. I've been thinking about how we might re-route and tag messages in the backend for a number of years and I feel like this summer I'll have resources and time to spend working towards it. Providing a way to decide if a message should be sent only to the server log, or only to the client, or to an external system (syslog, pgQ, rabbitMQ, etc), or to some combination of those, is definitely one of the items on that list. (I assume BTW that we're not considering it a security problem that superusers can trivially escape auditing.) No, that's entirely understood and expected, which is one of the reasons it'd be good to reduce the number of superusers running around. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] upper planner path-ification
Robert Haas robertmh...@gmail.com writes: On Wed, May 13, 2015 at 10:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: For the reasons I mentioned, I'd like to get to a point where subquery_planner's output is Paths not Plans as soon as possible. But the idea of coarse representation of steps that we aren't trying to be smart about might be useful to save some labor in the short run. The zero-order version of that might be a single Path node type that represents do whatever grouping_planner would do, which we'd start to break down into multiple node types once we had the other APIs fixed. The problem I'm really interested in solving is gaining the ability to add additional aggregation strategies, such as letting an FDW do it remotely, or doing it in parallel. It seems to me that your proposed zero-order version of that wouldn't really get us terribly far toward that goal - it would be more oriented towards solving the other problems you mention, specifically adding more intelligence to setops and allowing parameterization of subqueries. Those things certainly have some value, but I think supporting alternate aggregation strategies is a lot more interesting. Clearly we'd like to get to both goals. I don't see the zero order design as something we'd ship or even have in the tree for more than a short time. But it might still be a useful refactorization step. In any case, the key question if we're to have Paths representing higher-level computations is what do we hang our lists of such Paths off of?. If we have say both GROUP BY and LIMIT, it's important to distinguish Paths that purport to do only the grouping step from those that do both the grouping and the limit. For the scan/join part of planning, we do this by attaching the Paths to RelOptInfos that denote various levels of joining ... but how does that translate to the higher level processing steps? Perhaps we could make dummy RelOptInfos that correspond to having completed different steps of processing; but I've not got a clear idea about how many such RelOptInfos we'd need, and in particular not about whether we need to cater for completing those steps in different orders. 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] trust authentication behavior
On Thu, May 14, 2015 at 5:16 AM, Denis Kirjanov k...@itsirius.su wrote: Is it possible to restrict the trust auth method to accept local connections only using the selinux policy? I would guess that it probably is, but I can't tell you how. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] upper planner path-ification
On Thu, May 14, 2015 at 10:54 AM, Tom Lane t...@sss.pgh.pa.us wrote: In any case, the key question if we're to have Paths representing higher-level computations is what do we hang our lists of such Paths off of?. Yeah, I was wondering about that, too. If we have say both GROUP BY and LIMIT, it's important to distinguish Paths that purport to do only the grouping step from those that do both the grouping and the limit. For the scan/join part of planning, we do this by attaching the Paths to RelOptInfos that denote various levels of joining ... but how does that translate to the higher level processing steps? Perhaps we could make dummy RelOptInfos that correspond to having completed different steps of processing; but I've not got a clear idea about how many such RelOptInfos we'd need, and in particular not about whether we need to cater for completing those steps in different orders. Well, I'm just shooting from the hip here, but it seems to me that the basic pipeline as it exists today is Join - Aggregate - SetOp - Limit - LockRows. I don't think Limit or LockRows can be moved any earlier. SetOps have a lot in common with Aggregates, though, and might be able to commute. For instance, if you had an EXCEPT that was going to knock out most of the rows and also a DISTINCT, you might want to postpone the DISTINCT until after you do the EXCEPT, or you might just want to do both at once: rhaas=# explain select distinct * from (values (1), (1), (2), (3)) x except select 3; QUERY PLAN - HashSetOp Except (cost=0.06..0.17 rows=4 width=3) - Append (cost=0.06..0.16 rows=5 width=3) - Subquery Scan on *SELECT* 1 (cost=0.06..0.14 rows=4 width=4) - HashAggregate (cost=0.06..0.10 rows=4 width=4) Group Key: *VALUES*.column1 - Values Scan on *VALUES* (cost=0.00..0.05 rows=4 width=4) - Subquery Scan on *SELECT* 2 (cost=0.00..0.02 rows=1 width=0) - Result (cost=0.00..0.01 rows=1 width=0) (8 rows) In an ideal world, we'd only hash once. And both set operations and aggregates can sometimes commute with joins, which seems like the stickiest wicket, because there can't be more than a couple of levels of grouping or aggregation in the same subquery (GROUP BY, DISTINCT, UNION?) but there can be lots of joins, and if a particular aggregate can be implemented in lots of places, things start to get complicated. Like, if you've got a SELECT DISTINCT ON (a.x) ... FROM ...lots...-type query, I think you can pretty much slap the DISTINCT on there at any point in the join nest, probably ideally at the point where the number of rows is the lowest it's going to get, or maybe when the sortorder is convenient. For a GROUP BY query with ungrouped dependent columns, you can do the aggregation before or after those joins, but you must do it after the joins that are needed to provide all the values needed for the aggregated columns. If you model this with RelOptInfos, you're basically going to need a RelOptInfo to say i include these relids and these aggregation or setop operations. So in the worst case each agg/setop doubles the number of RelOptInfos, although probably in reality it doesn't because you won't have infinite flexibility to reorder things. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] trust authentication behavior
On Thu, May 14, 2015 at 2:16 AM, Denis Kirjanov k...@itsirius.su wrote: Hello guys, Is it possible to restrict the trust auth method to accept local connections only using the selinux policy? You want selinux to prevent trust connections from non-local clients even if pg_hba.conf explicitly allows them? David J
Re: [HACKERS] [COMMITTERS] pgsql: Add pg_audit, an auditing extension
Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: I've pushed a change which should clean it up by simply loading the module after each reconnects is done, more-or-less simulating having it be in shared_preload_libraries. It also wasn't using the correct database for reconnecting. I'll keep an eye on it. Another thing that looks not amazingly well-thought-out about that regression test is that it creates a superuser role with a known name (and no password, not that adding a password would make it better). We create a lot of roles in other tests too; the foreign_data test is the only one that create a superuser role. While working on the tests for the DDL deparse thing, I had to create a script with a list of roles that all the tests use, and it's pretty amazing. I remember thinking at the time that it'd be better to initialize a number of standard roles in an initial step, and have them be used consistently in the tests that require them, rather than having create/drop everywhere. -- create roles used throughout the tests create role clstr_user; create role current_user; create role foreign_data_user; create role Public; create role regressgroup1; create role regressgroup2; create role regression_bob; create role regression_group; create role regression_user1; create role regression_user2; create role regression_user3; create role regression_user; create role regresslo; create role regress_rol_lock1; create role regress_test_indirect; create role regress_test_role; create role regress_test_role2; create role regress_test_role_super superuser; create role regressuser1; create role regressuser2; create role regressuser3; create role regressuser4; create role regressuser5; create role regtest_unpriv_user; create role regtest_addr_user; create role regtest_alter_user1; create role regtest_alter_user2; create role regtest_alter_user3; create role rls_regress_group1; create role rls_regress_group2; create role rls_regress_user0; create role rls_regress_user1; create role rls_regress_user2; create role rls_regress_exempt_user; create role schemauser2; create role seclabel_user1; create role seclabel_user2; create role selinto_user; create role testrol1; create role testrol2; create role testrolx; create role unprivileged_role; create role user; create role view_user2; -- Á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] trust authentication behavior
On 05/14/2015 11:59 AM, Robert Haas wrote: On Thu, May 14, 2015 at 5:16 AM, Denis Kirjanov k...@itsirius.su wrote: Is it possible to restrict the trust auth method to accept local connections only using the selinux policy? I would guess that it probably is, but I can't tell you how. I would have guessed not :-) 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] [COMMITTERS] pgsql: Add pg_audit, an auditing extension
Alvaro Herrera alvhe...@2ndquadrant.com writes: Tom Lane wrote: Another thing that looks not amazingly well-thought-out about that regression test is that it creates a superuser role with a known name (and no password, not that adding a password would make it better). We create a lot of roles in other tests too; the foreign_data test is the only one that create a superuser role. While working on the tests for the DDL deparse thing, I had to create a script with a list of roles that all the tests use, and it's pretty amazing. I remember thinking at the time that it'd be better to initialize a number of standard roles in an initial step, and have them be used consistently in the tests that require them, rather than having create/drop everywhere. It would definitely be better if the names were less randomly chosen and hence less likely to conflict with existing role names in an installation. I'm not sure why we don't insist that they should all start with regress or similar, for instance. But what I'm on about at the moment is that I think creating new superusers is a bad idea from a security standpoint. It seems quite unlikely that we *have* to do that for testing purposes. Also, I notice that the pg_audit test fails to drop the roles it created, even if it reaches the end successfully. That's just bad. 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
[HACKERS] pgbench - allow backslash-continuations in custom scripts
Add backslash continuations to pgbench custom scripts.
The benefit of this approach is that it is upward compatible, and it is
also pretty simple to implement. The downside is that backslash
continuation is not the best syntax ever invented, but then you do not
have to use it if you do not like it.
The alternative would be to make semi-colon a mandatory end-of-line
marker, which would introduce an incompatibility and requires more efforts
to implement, including some kind of SQL-compatible lexer.
IMHO this approach is the best compromise.
--
Fabien.diff --git a/doc/src/sgml/ref/pgbench.sgml b/doc/src/sgml/ref/pgbench.sgml
index a808546..f68acb2 100644
--- a/doc/src/sgml/ref/pgbench.sgml
+++ b/doc/src/sgml/ref/pgbench.sgml
@@ -697,11 +697,13 @@ pgbench optional replaceableoptions/ /optional replaceabledbname/
/para
para
- The format of a script file is one SQL command per line; multiline
- SQL commands are not supported. Empty lines and lines beginning with
- literal--/ are ignored. Script file lines can also be
- quotemeta commands/, which are interpreted by applicationpgbench/
- itself, as described below.
+ The format of a script file is composed of lines which are each either
+ one SQL command or one quotemeta command/ interpreted by
+ applicationpgbench/ itself, as described below.
+ Commands can be spread over multiple lines using backslash (literal\/)
+ continuations, in which case the set of continuated lines is considered
+ as just one line.
+ Empty lines and lines beginning with literal--/ are ignored.
/para
para
@@ -769,7 +771,8 @@ pgbench optional replaceableoptions/ /optional replaceabledbname/
Examples:
programlisting
\set ntellers 10 * :scale
-\set aid (1021 * :aid) % (10 * :scale) + 1
+\set aid \
+ (1021 * :aid) % (10 * :scale) + 1
/programlisting/para
/listitem
/varlistentry
diff --git a/src/bin/pgbench/pgbench.c b/src/bin/pgbench/pgbench.c
index 8b8b591..8991702 100644
--- a/src/bin/pgbench/pgbench.c
+++ b/src/bin/pgbench/pgbench.c
@@ -2437,7 +2437,7 @@ process_commands(char *buf, const char *source, const int lineno)
}
/*
- * Read a line from fd, and return it in a malloc'd buffer.
+ * Read a possibly \-continuated line from fd, and return it in a malloc'd buffer.
* Return NULL at EOF.
*
* The buffer will typically be larger than necessary, but we don't care
@@ -2462,9 +2462,25 @@ read_line_from_file(FILE *fd)
memcpy(buf + used, tmpbuf, thislen + 1);
used += thislen;
- /* Done if we collected a newline */
- if (thislen 0 tmpbuf[thislen - 1] == '\n')
- break;
+ /* If we collected a newline */
+ if (used 0 buf[used - 1] == '\n')
+ {
+ /* Handle simple \-continuations */
+ if (used = 2 buf[used - 2] == '\\')
+ {
+buf[used - 2] = '\0';
+used -= 2;
+ }
+ else if (used = 3 buf[used - 2] == '\r'
+ buf[used - 3] == '\\')
+ {
+buf[used - 3] = '\0';
+used -= 3;
+ }
+ else
+/* Else we are done */
+break;
+ }
/* Else, enlarge buf to ensure we can append next bufferload */
buflen += BUFSIZ;
--
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: pgbench allow '=' in \set
[...] I tend to agree with the bottom line that that's just more complication than is justified. I sympathize with Robert's dislike for backslash continuations; but doing it the other way would be a huge amount of up-front work and a nontrivial amount of continuing maintenance, for which we would not get thanks from users but rather bitching about how we broke their scripts. Seems like a lose-lose situation. I agree. It is a small matter that does not justify a large patch, a greater maintenance burden and breaking compatibility. I've posted a small patch for backslash-continuations as a new thread. -- Fabien. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] BackendPidGetProc doesn't return PGPROC for background worker?
Hi I am trying to start bgworker from bgworker and create communication between these process. I have a code based on test_shm_mq. This code fails because BackendPidGetProc doesn't find related bgworker process, although the registrant process is living registrant = BackendPidGetProc(MyBgworkerEntry-bgw_notify_pid) Is expected behave? Regards Pavel
Re: [HACKERS] trust authentication behavior
On Thu, May 14, 2015 at 12:22 PM, Denis Kirjanov k...@itsirius.su wrote: Yeah, but the idea is to do that without the pg_hba.conf You may want to try describing the problem and not just ask if the chosen solution is possible - of which I am doubtful but I have never used selinux or studied it in any depth. pg_hba.conf is the chosen tool for this kind of thing so pointing out why it cannot be used is a much more useful first step. David J.
[HACKERS] Query Deparsing Support
The DDL deparse support that just landed looks impressive, but I’ve needed query deparsing for some time now. Within pg_shard https://github.com/citusdata/pg_shard we took the fast-and-dirty approach of merging in a modified ruleutils.c, though I’d (obviously) like to get away from that. Many of ruleutils.c’s functions already have a public interface; would there be any objection to exposing get_query_def to provide a way to turn a Query back into an SQL string? A more structured approach analogous to the new DDL stuff would make my life easier, but simply having the Query-to-SQL function would be a huge improvement for now. I trawled through the archives but couldn’t find any discussion of why this function was kept static. -- Jason Petersen Software Engineer | Citus Data 303.736.9255 ja...@citusdata.com signature.asc Description: Message signed with OpenPGP using GPGMail
Re: [HACKERS] trust authentication behavior
Yeah, but the idea is to do that without the pg_hba.conf - Ursprüngliche Mail - Von: David G. Johnston david.g.johns...@gmail.com An: Denis Kirjanov k...@itsirius.su CC: pgsql-hackers@postgresql.org Gesendet: Donnerstag, 14. Mai 2015 18:22:45 Betreff: Re: [HACKERS] trust authentication behavior On Thu, May 14, 2015 at 2:16 AM, Denis Kirjanov k...@itsirius.su wrote: Hello guys, Is it possible to restrict the trust auth method to accept local connections only using the selinux policy? You want selinux to prevent trust connections from non-local clients even if pg_hba.conf explicitly allows them? David J -- 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] Triaging the remaining open commitfest items
On Thu, May 14, 2015 at 05:10:29PM -0400, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 05/13/2015 11:38 AM, Tom Lane wrote: * Grouping Sets I had originally promised to be committer for this one, and still want to go look at it, but Robert's nearby message about not committing stuff in haste definitely seems to apply. That makes me sad. I wish you would still try. FWIW, I did go look at this patch, and concluded it was not close enough to ready to try to squeeze it in now. (I think Andres isn't convinced of that yet, but time grows short, and I quite agree with Robert that committing almost-ready patches at this stage of the game is a bad idea.) The good news on this front is that Salesforce has recently taken an interest in having GROUPING SETS capability, so I should be able to find more time to work on this over the next month or two. What I am now hoping for is to work it over and have something ready to push as soon as the 9.6 branch opens. What I intend to spend my time on over the next day or so is fixing the GB18030 conversion problem (bug #12845), which looks like a small enough task to finish before feature freeze. So you claim the item on the commitfest in the Fall, which effectively prevents other committers from getting involved, then two days before the freeze you encourage others to work on it, and a day before the freeze you say it is too late to apply? And now, all of a sudden, you are interested in working on this because your employer is interested? How do I measure the amount of unfairness here? -- 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] Triaging the remaining open commitfest items
Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@momjian.us) wrote: I will call for a vote that the freeze deadline be changed if this patch is rejected to due to time. I might lose the vote, but I am going to try because if we lose our reputation for fairness, we have lost a lot more than a week/month of release time. I'm guessing the vote is core-only, but +1 from me in any case. I fully agree that this patch has had a serious measure of effort put behind it from the author and is absolutely a capability we desire and need to have in core. I should think we'd have learned by now what happens when we delay a release date to get in some extra feature. It hasn't worked well in the past and I see no reason to believe the results would be any more desirable this time. 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] upper planner path-ification
On Thu, May 14, 2015 at 12:19:44PM -0400, Robert Haas wrote: Well, I'm just shooting from the hip here, but it seems to me that the basic pipeline as it exists today is Join - Aggregate - SetOp - Limit - LockRows. I don't think Limit or LockRows can be moved any earlier. SetOps have a lot in common with Aggregates, though, and might be able to commute. For instance, if you had an EXCEPT that was going to knock out most of the rows and also a DISTINCT, you might want to postpone the DISTINCT until after you do the EXCEPT, or you might just want to do both at once: Also thinking a little from the side: an SQL query is a expression of some tree in relational algebra, and a Path is a representation of a way to acheive some sub-part of it. The planner attempts to try find alternative ways of generating path by reordering joins but AIUI doesn't do much about aggregations. What is essentially being discussed here is also allowing commuting aggregations and joins. DISTINCT and DISTINCT ON are just special kinds of aggregations so don't need to be considered especially. ISTM you should be able to for each aggregation note which joins it commutes with and which it doesn't, perhaps even with a simple bitmap. The backbone of the plan is the order of the aggregations which generally won't commute at all, and the joins which can float around as long as the dependancies (stuff that won't commute) are satisfied. And both set operations and aggregates can sometimes commute with joins, which seems like the stickiest wicket, because there can't be more than a couple of levels of grouping or aggregation in the same subquery (GROUP BY, DISTINCT, UNION?) but there can be lots of joins, and if a particular aggregate can be implemented in lots of places, things start to get complicated. I think this is basically the same idea. I'm not sure aggregates and set operations can commute in general, unless you could somehow (conceptually) describe them as a join/antijoin. UNION might be special here. Like, if you've got a SELECT DISTINCT ON (a.x) ... FROM ...lots...-type query, I think you can pretty much slap the DISTINCT on there at any point in the join nest, probably ideally at the point where the number of rows is the lowest it's going to get, or maybe when the sortorder is convenient. For a GROUP BY query with ungrouped dependent columns, you can do the aggregation before or after those joins, but you must do it after the joins that are needed to provide all the values needed for the aggregated columns. If you model this with RelOptInfos, you're basically going to need a RelOptInfo to say i include these relids and these aggregation or setop operations. So in the worst case each agg/setop doubles the number of RelOptInfos, although probably in reality it doesn't because you won't have infinite flexibility to reorder things. I think it's more like the number of possibilities doubles for each join that could commute with the aggregate. But as you say the number of actual possibilities doesn't actually grow that fast. I think it may be better to have each path track the relids and aggregates it covers, but then you need to have an efficient way to work out which rels/aggregates can be considered for each path. Either sounds it sounds like quite a planner overhaul. Hope this helps, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ He who writes carelessly confesses thereby at the very outset that he does not attach much importance to his own thoughts. -- Arthur Schopenhauer signature.asc Description: Digital signature
Re: [HACKERS] Triaging the remaining open commitfest items
On Thu, May 14, 2015 at 11:28:33PM +0200, Andres Freund wrote: On 2015-05-14 17:10:29 -0400, Tom Lane wrote: FWIW, I did go look at this patch, and concluded it was not close enough to ready to try to squeeze it in now. (I think Andres isn't convinced of that yet, but time grows short, and I quite agree with Robert that committing almost-ready patches at this stage of the game is a bad idea.) I think if there's any patch that deserves some leeway it's this one. It's been been forced into a limbo for nearly half a year; without leaving Andrew many options. I've removed the use of GroupedVars and Andrew is right now working on structural changes. I'm not ready at this point to make a judgement. I will call for a vote that the freeze deadline be changed if this patch is rejected to due to time. I might lose the vote, but I am going to try because if we lose our reputation for fairness, we have lost a lot more than a week/month of release time. -- 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] Triaging the remaining open commitfest items
* Bruce Momjian (br...@momjian.us) wrote: On Thu, May 14, 2015 at 11:28:33PM +0200, Andres Freund wrote: On 2015-05-14 17:10:29 -0400, Tom Lane wrote: FWIW, I did go look at this patch, and concluded it was not close enough to ready to try to squeeze it in now. (I think Andres isn't convinced of that yet, but time grows short, and I quite agree with Robert that committing almost-ready patches at this stage of the game is a bad idea.) I think if there's any patch that deserves some leeway it's this one. It's been been forced into a limbo for nearly half a year; without leaving Andrew many options. I've removed the use of GroupedVars and Andrew is right now working on structural changes. I'm not ready at this point to make a judgement. I will call for a vote that the freeze deadline be changed if this patch is rejected to due to time. I might lose the vote, but I am going to try because if we lose our reputation for fairness, we have lost a lot more than a week/month of release time. I'm guessing the vote is core-only, but +1 from me in any case. I fully agree that this patch has had a serious measure of effort put behind it from the author and is absolutely a capability we desire and need to have in core. I'd be happy to jump in and help tonight or tomorrow, but I don't think there's much I could really contribute right now beyond my support. Still, please ping me if there's something I can do. Thanks! Stephen signature.asc Description: Digital signature
Re: [HACKERS] Triaging the remaining open commitfest items
On Thu, May 14, 2015 at 6:03 PM, Peter Geoghegan p...@heroku.com wrote: On Thu, May 14, 2015 at 2:28 PM, Andres Freund and...@anarazel.de wrote: On 2015-05-14 17:10:29 -0400, Tom Lane wrote: FWIW, I did go look at this patch, and concluded it was not close enough to ready to try to squeeze it in now. (I think Andres isn't convinced of that yet, but time grows short, and I quite agree with Robert that committing almost-ready patches at this stage of the game is a bad idea.) I think if there's any patch that deserves some leeway it's this one. It's been been forced into a limbo for nearly half a year; without leaving Andrew many options. +1. If Andres is going to try and find a way of getting the patch into 9.5, then I think he deserves at least a few extra days. It wasn't as if Andrew wasn't all over this from early in the cycle. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Triaging the remaining open commitfest items
On Thu, May 14, 2015 at 5:54 PM, Bruce Momjian br...@momjian.us wrote: Life is unfair. True, but I have problems with leaders acting in a way that is unfair to those with less power. Have you considered how demoralizing it is to work in an unfair environment? Unfairness happens, but as leaders, we are supposed to try to avoid it, not cause it. +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Triaging the remaining open commitfest items
On Thu, May 14, 2015 at 06:57:24PM -0400, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@momjian.us) wrote: I will call for a vote that the freeze deadline be changed if this patch is rejected to due to time. I might lose the vote, but I am going to try because if we lose our reputation for fairness, we have lost a lot more than a week/month of release time. I'm guessing the vote is core-only, but +1 from me in any case. I fully agree that this patch has had a serious measure of effort put behind it from the author and is absolutely a capability we desire and need to have in core. I should think we'd have learned by now what happens when we delay a release date to get in some extra feature. It hasn't worked well in the past and I see no reason to believe the results would be any more desirable this time. Right, the importance of the feature is not a reason to delay the feature freeze. -- 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] Triaging the remaining open commitfest items
Andrew Dunstan and...@dunslane.net writes: On 05/13/2015 11:38 AM, Tom Lane wrote: * Grouping Sets I had originally promised to be committer for this one, and still want to go look at it, but Robert's nearby message about not committing stuff in haste definitely seems to apply. That makes me sad. I wish you would still try. FWIW, I did go look at this patch, and concluded it was not close enough to ready to try to squeeze it in now. (I think Andres isn't convinced of that yet, but time grows short, and I quite agree with Robert that committing almost-ready patches at this stage of the game is a bad idea.) The good news on this front is that Salesforce has recently taken an interest in having GROUPING SETS capability, so I should be able to find more time to work on this over the next month or two. What I am now hoping for is to work it over and have something ready to push as soon as the 9.6 branch opens. What I intend to spend my time on over the next day or so is fixing the GB18030 conversion problem (bug #12845), which looks like a small enough task to finish before feature freeze. 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] Triaging the remaining open commitfest items
Bruce Momjian br...@momjian.us writes: On Thu, May 14, 2015 at 05:10:29PM -0400, Tom Lane wrote: The good news on this front is that Salesforce has recently taken an interest in having GROUPING SETS capability, so I should be able to find more time to work on this over the next month or two. What I am now hoping for is to work it over and have something ready to push as soon as the 9.6 branch opens. So you claim the item on the commitfest in the Fall, which effectively prevents other committers from getting involved, then two days before the freeze you encourage others to work on it, and a day before the freeze you say it is too late to apply? And now, all of a sudden, you are interested in working on this because your employer is interested? [ shrug... ] Andrew had unilaterally removed me as committer from that patch back in January or so, so it dropped way down my priority list. I'm willing to move it back up now, but I could do without people expressing a sense of entitlement to my time. In any case, Andres is currently the committer of record, and if he decides to push it in the next 24 hours, I'm not doing anything more to stand in his way than Robert already did. How do I measure the amount of unfairness here? Life is unfair. 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] Triaging the remaining open commitfest items
On Thu, May 14, 2015 at 2:28 PM, Andres Freund and...@anarazel.de wrote: On 2015-05-14 17:10:29 -0400, Tom Lane wrote: FWIW, I did go look at this patch, and concluded it was not close enough to ready to try to squeeze it in now. (I think Andres isn't convinced of that yet, but time grows short, and I quite agree with Robert that committing almost-ready patches at this stage of the game is a bad idea.) I think if there's any patch that deserves some leeway it's this one. It's been been forced into a limbo for nearly half a year; without leaving Andrew many options. +1. If Andres is going to try and find a way of getting the patch into 9.5, then I think he deserves at least a few extra days. It wasn't as if Andrew wasn't all over this from early in the cycle. -- 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] Re: [COMMITTERS] pgsql: Map basebackup tablespaces using a tablespace_map file
On Thu, May 14, 2015 at 12:59 PM, Andrew Dunstan and...@dunslane.net wrote: I'm not sure I understand this issue in detail, but why would using rmtree() on something you expect to be a symlink ever be a good idea? It seems like if things are the way you expect them to be, it has no benefit, but if they are different from what you expect, you might blow away a ton of important data. Maybe I am just confused. The suggestion is to get rid of using rmtree. Instead, if we find a non-symlink in pg_tblspc we'll make the user clean it up before we can continue. So your instinct is in tune with my suggestion. Right. Maybe I should have just said +1. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] Triaging the remaining open commitfest items
On Thu, May 14, 2015 at 05:37:07PM -0400, Tom Lane wrote: Bruce Momjian br...@momjian.us writes: On Thu, May 14, 2015 at 05:10:29PM -0400, Tom Lane wrote: The good news on this front is that Salesforce has recently taken an interest in having GROUPING SETS capability, so I should be able to find more time to work on this over the next month or two. What I am now hoping for is to work it over and have something ready to push as soon as the 9.6 branch opens. So you claim the item on the commitfest in the Fall, which effectively prevents other committers from getting involved, then two days before the freeze you encourage others to work on it, and a day before the freeze you say it is too late to apply? And now, all of a sudden, you are interested in working on this because your employer is interested? [ shrug... ] Andrew had unilaterally removed me as committer from that patch back in January or so, so it dropped way down my priority list. I'm willing to move it back up now, but I could do without people expressing a sense of entitlement to my time. In any case, Andres is I am trying not to express any entitlement. I do have a problem with people claiming things that block others. In fact, the reason your name was taken off was because you were inactive on the patch. Unfortunately, even though your name was removed, people still thought of you as owning the patch, and your formidable reputation cemented that. I feel if you had not gotten involved, that patch would have been applied long ago, When someone's involvement _prevents_ a patch from being reviewed or applied, that is the opposite of what we want. I think this effect is indisputable in this case, which is why I am saying if we let this patch slip due to time, we are being unfair. currently the committer of record, and if he decides to push it in the next 24 hours, I'm not doing anything more to stand in his way than Robert already did. Uh, did Robert delay work on the patch in any way? How do I measure the amount of unfairness here? Life is unfair. True, but I have problems with leaders acting in a way that is unfair to those with less power. Have you considered how demoralizing it is to work in an unfair environment? Unfairness happens, but as leaders, we are supposed to try to avoid it, not cause it. -- 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] Triaging the remaining open commitfest items
Bruce Momjian br...@momjian.us writes: On Thu, May 14, 2015 at 05:37:07PM -0400, Tom Lane wrote: [ shrug... ] Andrew had unilaterally removed me as committer from that patch back in January or so, so it dropped way down my priority list. I'm willing to move it back up now, but I could do without people expressing a sense of entitlement to my time. In any case, Andres is I am trying not to express any entitlement. I do have a problem with people claiming things that block others. Well, I was and remain concerned that the patch would do a great deal of violence to basic system structure. If I'd had adequate time to work on it I would have attempted to fix it, but I have not had that kind of time. In the meantime, the patch was not claimed and I was not particularly blocking anyone else from claiming it. Plenty of stuff gets committed around here without my blessing. currently the committer of record, and if he decides to push it in the next 24 hours, I'm not doing anything more to stand in his way than Robert already did. Uh, did Robert delay work on the patch in any way? I was merely agreeing with the concerns Robert expressed in CA+TgmoZt0Q=Odx-pL+9Zc6Qyf1A5_2hMBWgaEUdoWgW=jve...@mail.gmail.com that we'd do well to avoid committing any more large-and-not-quite- fully-baked patches at this point. If Andres decides it's baked enough, that's his responsibility and prerogative as a committer. True, but I have problems with leaders acting in a way that is unfair to those with less power. Have you considered how demoralizing it is to work in an unfair environment? Unfairness happens, but as leaders, we are supposed to try to avoid it, not cause it. TBH, every time somebody beats me up about not having dropped everything else to spend a month on this patch, it just makes me want to back away further. I haven't had the time, and I really could do without accusations of that being unfair. 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] Patch for bug #12845 (GB18030 encoding)
I wrote: Robert Haas robertmh...@gmail.com writes: On Wed, May 6, 2015 at 11:13 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote: Maybe not, but at the very least we should consider getting it fixed in 9.5 rather than waiting a full development cycle. Same as in https://www.postgresql.org/message-id/20150428131549.ga25...@momjian.us I'm not saying we MUST include it in 9.5, but we should at least consider it. If we simply stash it in the open CF we guarantee that it will linger there for a year. Sure, if somebody has the time to put into it now, I'm fine with that. I'm afraid it won't be me, though: even if I had the time, I don't know enough about encodings. I concur that we should at least consider this patch for 9.5. I've added it to https://wiki.postgresql.org/wiki/PostgreSQL_9.5_Open_Items I looked at this patch a bit, and read up on GB18030 (thank you wikipedia). I concur we have a problem to fix. I do not like the way this patch went about it though, ie copying-and-pasting LocalToUtf and UtfToLocal and their supporting routines into utf8_and_gb18030.c. Aside from being duplicative, this means the improved mapping capability isn't available to use with anything except GB18030. (I do not know whether there are any linear mapping ranges in other encodings, but seeing that the Unicode crowd went to the trouble of defining a notation for it in http://www.unicode.org/reports/tr22/, I'm betting there are.) What I think would be a better solution, if slightly more invasive, is to extend LocalToUtf and UtfToLocal to add a callback function argument for a function of signature uint32 translate(uint32). This function, if provided, would be called after failing to find a mapping in the mapping table(s), and it could implement any translation that would be better handled by code than as a boatload of mapping-table entries. If it returns zero then it doesn't know a translation either, so throw error as before. An alternative definition that could be proposed would be to call the function before consulting the mapping tables, not after, on the grounds that the function can probably exit cheaply if the input's not in a range that it cares about. However, consulting the mapping table first wins if you have ranges that mostly work but contain a few exceptions: put the exceptions in the mapping table and then the function need not worry about handling them. Another alternative approach would be to try to define linear mapping ranges in a tabular fashion, for more consistency with what's there now. But that probably wouldn't work terribly well because the bytewise character representations used in this logic have to be converted into code points before you can do any sort of linear mapping. We could hard-wire that conversion for UTF8, but the conversion in the other code space would be encoding-specific. So we might as well just treat the whole linear mapping behavior as a black box function for each encoding. I'm also discounting the possibility that someone would want an algorithmic mapping for cases involving combined codes (ie pairs of UTF8 characters). Of the encodings we support, only EUC_JIS_2004 and SHIFT_JIS_2004 need such cases at all, and those have only a handful of cases; so it doesn't seem popular enough to justify the extra complexity. I also notice that pg_gb18030_verifier isn't even close to strict enough; it basically relies on pg_gb18030_mblen which contains no checks whatsoever on the third and fourth bytes. So that needs to be fixed. The verification tightening would definitely not be something to back-patch, and I'm inclined to think that the additional mapping capability shouldn't be either, in view of the facts that (a) we've had few if any field complaints yet, and (b) changing the signatures of LocalToUtf/UtfToLocal might possibly break third-party code. So I'm seeing this as a HEAD-only patch, but I do want to try to squeeze it into 9.5 rather than wait another year. Barring objections, I'll go make this happen. 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] Triaging the remaining open commitfest items
On 2015-05-14 17:10:29 -0400, Tom Lane wrote: FWIW, I did go look at this patch, and concluded it was not close enough to ready to try to squeeze it in now. (I think Andres isn't convinced of that yet, but time grows short, and I quite agree with Robert that committing almost-ready patches at this stage of the game is a bad idea.) I think if there's any patch that deserves some leeway it's this one. It's been been forced into a limbo for nearly half a year; without leaving Andrew many options. I've removed the use of GroupedVars and Andrew is right now working on structural changes. I'm not ready at this point to make a judgement. -- 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] KNN-GiST with recheck
On 05/15/2015 02:28 AM, Heikki Linnakangas wrote: I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. Forgot to attach the latest patch, here you go. - Heikki From df00d9c972a760e1ed777a7c9b1603dad1d3f134 Mon Sep 17 00:00:00 2001 From: Heikki Linnakangas heikki.linnakangas@iki.fi Date: Fri, 15 May 2015 00:56:27 +0300 Subject: [PATCH 1/1] Allow GiST distance function to return merely a lower-bound. The distance function can now set *recheck = false, like index quals. The executor will then re-check the ORDER BY expressions, and use a queue to reorder the results on the fly. This makes it possible to do kNN-searches on polygons and circles, which store a bounding box in the index, rather than the exact value. Alexander Korotkov and me --- doc/src/sgml/gist.sgml | 35 ++- src/backend/access/gist/gistget.c | 22 +- src/backend/access/gist/gistproc.c | 37 +++ src/backend/access/gist/gistscan.c | 5 + src/backend/executor/nodeIndexscan.c | 351 - src/backend/optimizer/plan/createplan.c| 69 -- src/backend/utils/adt/geo_ops.c| 27 +++ src/include/access/genam.h | 3 + src/include/access/relscan.h | 9 + src/include/catalog/catversion.h | 2 +- src/include/catalog/pg_amop.h | 2 + src/include/catalog/pg_amproc.h| 2 + src/include/catalog/pg_operator.h | 8 +- src/include/catalog/pg_proc.h | 4 + src/include/nodes/execnodes.h | 19 ++ src/include/nodes/plannodes.h | 12 +- src/include/utils/geo_decls.h | 3 + src/test/regress/expected/create_index.out | 78 +++ src/test/regress/sql/create_index.sql | 12 + 19 files changed, 663 insertions(+), 37 deletions(-) diff --git a/doc/src/sgml/gist.sgml b/doc/src/sgml/gist.sgml index e7d1ff9..1291f8d 100644 --- a/doc/src/sgml/gist.sgml +++ b/doc/src/sgml/gist.sgml @@ -105,6 +105,7 @@ literal~=/ /entry entry + literallt;-gt;/ /entry /row row @@ -163,6 +164,7 @@ literal~=/ /entry entry + literallt;-gt;/ /entry /row row @@ -207,6 +209,12 @@ /table para + Currently, ordering by the distance operator literallt;-gt;/ + is supported only with literalpoint/ by the operator classes + of the geometric types. + /para + + para For historical reasons, the literalinet_ops/ operator class is not the default class for types typeinet/ and typecidr/. To use it, mention the class name in commandCREATE INDEX/, @@ -780,6 +788,7 @@ my_distance(PG_FUNCTION_ARGS) data_type *query = PG_GETARG_DATA_TYPE_P(1); StrategyNumber strategy = (StrategyNumber) PG_GETARG_UINT16(2); /* Oid subtype = PG_GETARG_OID(3); */ +/* bool *recheck = (bool *) PG_GETARG_POINTER(4); */ data_type *key = DatumGetDataType(entry-gt;key); double retval; @@ -792,14 +801,24 @@ my_distance(PG_FUNCTION_ARGS) /programlisting The arguments to the functiondistance/ function are identical to - the arguments of the functionconsistent/ function, except that no - recheck flag is used. The distance to a leaf index entry must always - be determined exactly, since there is no way to re-order the tuples - once they are returned. Some approximation is allowed when determining - the distance to an internal tree node, so long as the result is never - greater than any child's actual distance. Thus, for example, distance - to a bounding box is usually sufficient in geometric applications. The - result value can be any finite typefloat8/ value. (Infinity and + the arguments of the functionconsistent/ function. + /para + + para + Some approximation is allowed when determining the distance, as long as + the result is never greater than the entry's actual distance. Thus, for + example, distance to a bounding box is usually sufficient in geometric + applications. For an internal tree node, the distance returned must not + be greater than the distance to any of the child nodes. If the returned + distance is not accurate, the function must set *recheck to false. (This + is not necessary for internal tree nodes; for them, the calculation is + always assumed to be inaccurate). The executor will calculate the + accurate distance after fetching the tuple from the heap, and reorder + the tuples if necessary. + /para + + para + The result value can be any finite typefloat8/ value. (Infinity and minus infinity are used internally to handle cases such as nulls, so it is not recommended that functiondistance/ functions return these values.) diff --git
Re: Custom/Foreign-Join-APIs (Re: [HACKERS] [v9.5] Custom Plan API)
A possible compromise that we could perhaps still wedge into 9.5 is to extend CustomPath with a List of child Paths, and CustomScan with a List of child Plans, which createplan.c would know to build from the Paths, and other modules would then also be aware of these children. I find that uglier than a separate join node type, but it would be tolerable I guess. The attached patch implements what you suggested as is. It allows custom-scan providers to have child Paths without exporting create_plan_recurse(), and enables to represent N-way join naturally. Please add any solution, even if we don't reach the consensus of how create_plan_recurse (and other useful static functions) are visible to extensions. Patch detail: It adds a List field (List *custom_children) to CustomPath structure to inform planner its child Path nodes, to be transformed to Plan node through the planner's job. CustomScan also have a new List field to have its child Plan nodes which shall be processed by setrefs.c and subselect.c. PlanCustomPath callback was extended to have a list of Plan nodes that were constructed on create_customscan_plan in core, it is a job of custom-scan provider to attach these Plan nodes onto lefttree, righttree or the custom_children list. CustomScanState also have an array to have PlanState nodes of the children. It is used for EXPLAIN command know the child nodes. Regarding of FDW, as Hanada-san mentioned, I'm uncertain whether similar feature is also needed because its join-pushdown feature scan on the result-set of remotely joined relations, thus no need to have local child Path nodes. So, I put this custom_children list on Custom structure only. It may need additional section in the documentation. Thanks, -- NEC Business Creation Division / PG-Strom Project KaiGai Kohei kai...@ak.jp.nec.com custom-join-problem-option-2.v1.patch Description: custom-join-problem-option-2.v1.patch -- 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] Fix token exceeding NAMELEN
On 2015-05-13 18:16, Christopher Browne wrote: On 13 May 2015 at 17:55, Tom Lane t...@sss.pgh.pa.us wrote: Aaron W. Swenson titanof...@gentoo.org writes: Trying to build HEAD and ran into this issue building the docs: openjade:logicaldecoding.sgml:575:62:Q: length of name token must not exceed NAMELEN (44) openjade:replication-origins.sgml:87:67:Q: length of name token must not exceed NAMELEN (44) Hmm ... that's odd. I don't see any such failure here, and the buildfarm members that build the docs aren't complaining either. What version of openjade are you using exactly? So, I've attached a patch that'll fix it. I have no particular objection to the patch as stated, but I'm just wondering if this is the tip of a tool compatibility iceberg we were not previously aware of. I recall us hitting this with Slony documentation. The NAMELEN limit lay in the SGML/DocBook configuration that was configured at the distribution level, so that it differed (crucially) betwen Debian and Red Hat. Red Hat used to have a lower name length limit, and while overriding it was technically possible, it required modifying configuration that the distribution thought was owned by one of the SGML packages, and hence the modification seemed pretty inadvisable. I thought that this restriction was alleviated years ago, so I'm a bit surprised to see this come up in 2015. (Or perhaps Gentoo hasn't yet opened up some limits??? :-) ) The restriction is alleviated (patched) by some distributions, and Gentoo isn't among those. It has been almost 4 years (the most recent Google has found) since the last time this happened with PostgreSQL's docs. http://www.postgresql.org/message-id/banlktiktw6srdygvfjrb4q+7dvwoqcc...@mail.gmail.com signature.asc Description: Digital signature
Re: [HACKERS] BackendPidGetProc doesn't return PGPROC for background worker?
On 2015-05-15 AM 05:01, Pavel Stehule wrote: I am trying to start bgworker from bgworker and create communication between these process. I have a code based on test_shm_mq. This code fails because BackendPidGetProc doesn't find related bgworker process, although the registrant process is living One reason for this may be that the worker was not started with the flag BGWORKER_SHMEM_ACCESS which is necessary to perform InitProcess() that would initialize a PGPROC entry for it. But if you'd used the same method for initializing workers as test_shm_mq_setup(), then it should have. Thanks, Amit -- 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] BackendPidGetProc doesn't return PGPROC for background worker?
On 2015-05-15 AM 10:39, Amit Langote wrote: On 2015-05-15 AM 05:01, Pavel Stehule wrote: I am trying to start bgworker from bgworker and create communication between these process. I have a code based on test_shm_mq. This code fails because BackendPidGetProc doesn't find related bgworker process, although the registrant process is living One reason for this may be that the worker was not started with the flag BGWORKER_SHMEM_ACCESS which is necessary to perform InitProcess() that would initialize a PGPROC entry for it. But if you'd used the same method for initializing workers as test_shm_mq_setup(), then it should have. It seems in addition, a BackgroundWorkerInitializeConnection() is also necessary for the PGPROC entry of the worker to be visible to others. I do not see that done anywhere in test_shm_mq(); so perhaps that's missing? Thanks, Amit -- 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] Triaging the remaining open commitfest items
On 15/05/15 10:58, Bruce Momjian wrote: On Thu, May 14, 2015 at 06:57:24PM -0400, Tom Lane wrote: Stephen Frost sfr...@snowman.net writes: * Bruce Momjian (br...@momjian.us) wrote: I will call for a vote that the freeze deadline be changed if this patch is rejected to due to time. I might lose the vote, but I am going to try because if we lose our reputation for fairness, we have lost a lot more than a week/month of release time. I'm guessing the vote is core-only, but +1 from me in any case. I fully agree that this patch has had a serious measure of effort put behind it from the author and is absolutely a capability we desire and need to have in core. I should think we'd have learned by now what happens when we delay a release date to get in some extra feature. It hasn't worked well in the past and I see no reason to believe the results would be any more desirable this time. Right, the importance of the feature is not a reason to delay the feature freeze. Following rules like this is very important, but so is making valid exceptions. Though I'm in no position to judge the importance of this patch, so I won't attempt to! Cheers, Gavin -- 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] best place for rtree strategy numbers
Alvaro Herrera alvhe...@2ndquadrant.com writes: In Emre Hasegeli's patch for range/inet/geometry opclasses for BRIN, he chose to move strategy numbers from gist.h to a more central place. He chose skey.h because that's where btree strategy numbers are defined, but I'm not sure I agree with that choice. Yeah, putting those in skey.h was probably not such a great idea. (IIRC, they didn't use to be there ... it's probably my fault that they're there now.) Therefore I would like to move those numbers elsewhere. Maybe we could have a new file which would be used only for strategy numbers, such as src/include/access/stratnum.h, and we would have something like this, and redefine the ones elsewhere to reference those. +1 I can, of course, just leave these files well enough alone and just rely on the numbers not changing (which surely they won't anyway) and remaining consistent with numbers used in new opclasses (which surely they will lest they be born inconsistent with existing ones). Yeah, we do have checks in opr_sanity which will complain if inconsistent strategy numbers are used for similarly-named operators. That's a pretty weak test though, and operator names aren't exactly the right thing to check anyway. I'd be good with pushing all of that stuff to a new central header. 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
[HACKERS] pg_upgrade cleanup
This patch makes pg_upgrade controldata checks more consistent, and adds
a missing check for float8_pass_by_value.
--
Bruce Momjian br...@momjian.ushttp://momjian.us
EnterpriseDB http://enterprisedb.com
+ Everyone has their own god. +
diff --git a/src/bin/pg_upgrade/controldata.c b/src/bin/pg_upgrade/controldata.c
new file mode 100644
index bf53db0..0608b64
*** a/src/bin/pg_upgrade/controldata.c
--- b/src/bin/pg_upgrade/controldata.c
*** get_control_data(ClusterInfo *cluster, b
*** 154,176
p++;/* remove ':' char */
cluster-controldata.cat_ver = str2uint(p);
}
- else if ((p = strstr(bufin, First log segment after reset:)) != NULL)
- {
- /* Skip the colon and any whitespace after it */
- p = strchr(p, ':');
- if (p == NULL || strlen(p) = 1)
- pg_fatal(%d: controldata retrieval problem\n, __LINE__);
- p = strpbrk(p, 01234567890ABCDEF);
- if (p == NULL || strlen(p) = 1)
- pg_fatal(%d: controldata retrieval problem\n, __LINE__);
-
- /* Make sure it looks like a valid WAL file name */
- if (strspn(p, 0123456789ABCDEF) != 24)
- pg_fatal(%d: controldata retrieval problem\n, __LINE__);
-
- strlcpy(cluster-controldata.nextxlogfile, p, 25);
- got_nextxlogfile = true;
- }
else if ((p = strstr(bufin, First log file ID after reset:)) != NULL)
{
p = strchr(p, ':');
--- 154,159
*** get_control_data(ClusterInfo *cluster, b
*** 201,207
pg_fatal(%d: controldata retrieval problem\n, __LINE__);
p++;/* remove ':' char */
! cluster-controldata.chkpnt_tli = str2uint(p);
got_tli = true;
}
else if ((p = strstr(bufin, Latest checkpoint's NextXID:)) != NULL)
--- 184,190
pg_fatal(%d: controldata retrieval problem\n, __LINE__);
p++;/* remove ':' char */
! tli = str2uint(p);
got_tli = true;
}
else if ((p = strstr(bufin, Latest checkpoint's NextXID:)) != NULL)
*** get_control_data(ClusterInfo *cluster, b
*** 266,271
--- 249,271
cluster-controldata.chkpnt_nxtmxoff = str2uint(p);
got_mxoff = true;
}
+ else if ((p = strstr(bufin, First log segment after reset:)) != NULL)
+ {
+ /* Skip the colon and any whitespace after it */
+ p = strchr(p, ':');
+ if (p == NULL || strlen(p) = 1)
+ pg_fatal(%d: controldata retrieval problem\n, __LINE__);
+ p = strpbrk(p, 01234567890ABCDEF);
+ if (p == NULL || strlen(p) = 1)
+ pg_fatal(%d: controldata retrieval problem\n, __LINE__);
+
+ /* Make sure it looks like a valid WAL file name */
+ if (strspn(p, 0123456789ABCDEF) != 24)
+ pg_fatal(%d: controldata retrieval problem\n, __LINE__);
+
+ strlcpy(cluster-controldata.nextxlogfile, p, 25);
+ got_nextxlogfile = true;
+ }
else if ((p = strstr(bufin, Maximum data alignment:)) != NULL)
{
p = strchr(p, ':');
*** get_control_data(ClusterInfo *cluster, b
*** 436,442
*/
if (GET_MAJOR_VERSION(cluster-major_version) = 902)
{
! if (got_log_id got_log_seg)
{
snprintf(cluster-controldata.nextxlogfile, 25, %08X%08X%08X,
tli, logid, segno);
--- 436,442
*/
if (GET_MAJOR_VERSION(cluster-major_version) = 902)
{
! if (got_tli got_log_id got_log_seg)
{
snprintf(cluster-controldata.nextxlogfile, 25, %08X%08X%08X,
tli, logid, segno);
*** get_control_data(ClusterInfo *cluster, b
*** 446,456
/* verify that we got all the mandatory pg_control data */
if (!got_xid || !got_oid ||
! !got_multi || !got_mxoff ||
(!got_oldestmulti
cluster-controldata.cat_ver = MULTIXACT_FORMATCHANGE_CAT_VER) ||
! (!live_check !got_nextxlogfile) ||
! !got_tli ||
!got_align || !got_blocksz || !got_largesz || !got_walsz ||
!got_walseg || !got_ident || !got_index || !got_toast ||
(!got_large_object
--- 446,455
/* verify that we got all the mandatory pg_control data */
if (!got_xid || !got_oid ||
! !got_multi ||
(!got_oldestmulti
cluster-controldata.cat_ver = MULTIXACT_FORMATCHANGE_CAT_VER) ||
! !got_mxoff || (!live_check !got_nextxlogfile) ||
!got_align || !got_blocksz || !got_largesz || !got_walsz ||
!got_walseg || !got_ident || !got_index || !got_toast ||
(!got_large_object
*** get_control_data(ClusterInfo *cluster, b
*** 470,488
if (!got_multi)
pg_log(PG_REPORT, latest checkpoint next MultiXactId\n);
- if (!got_mxoff)
- pg_log(PG_REPORT, latest checkpoint next MultiXactOffset\n);
-
if (!got_oldestmulti
cluster-controldata.cat_ver = MULTIXACT_FORMATCHANGE_CAT_VER)
pg_log(PG_REPORT, latest checkpoint oldest MultiXactId\n);
if (!live_check !got_nextxlogfile)
pg_log(PG_REPORT, first WAL segment after reset\n);
- if (!got_tli)
- pg_log(PG_REPORT, latest checkpoint timeline ID\n);
-
if (!got_align)
pg_log(PG_REPORT,
Re: [HACKERS] KNN-GiST with recheck
On 05/14/2015 01:43 PM, Alexander Korotkov wrote: On Wed, May 13, 2015 at 10:17 PM, Alexander Korotkov aekorot...@gmail.com wrote: One quick comment: It would be good to avoid the extra comparisons of the distances, when the index doesn't return any lossy items. As the patch stands, it adds one extra copyDistances() call and a cmp_distances() call for each tuple (in a knn-search), even if there are no lossy tuples. I will fix it until Friday. Attached patch is rebased against current master. Extra extra copyDistances() call and a cmp_distances() call for each tuple are avoided in the case of no lossy tuples. Thanks! I spent some time cleaning this up: * fixed a memory leak * fixed a silly bug in rechecking multi-column scans * I restructured the changes to IndexNext. I actually created a whole separate copy of IndexNext, called IndexNextWithReorder, that is used when there are ORDER BY expressions that might need to be rechecked. There is now some duplicated code between them, but I think they are both easier to understand this way. The IndexNext function is now as simple as before, and the IndexNextWithReorder doesn't need so many if()-checks on whether the reorder queue exists at all. * I renamed Distance to OrderByValues in the executor parts. We call the ORDER BY x - y construct an ORDER BY expression, so let's continue using that terminology. I think this is now ready for committing, but I'm pretty tired now so I'll read through this one more time in the morning, so that I won't wake up to a red buildfarm. - Heikki -- 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] Add pg_settings.pending_restart column
On 4/22/15 2:32 AM, Michael Paquier wrote: Attached is a rebased patch with previous comments addressed as I was looking at it. Switching this patch as Ready for committer. Committed, thanks. -- 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] BackendPidGetProc doesn't return PGPROC for background worker?
On 2015-05-15 AM 10:59, Amit Langote wrote: On 2015-05-15 AM 10:39, Amit Langote wrote: On 2015-05-15 AM 05:01, Pavel Stehule wrote: I am trying to start bgworker from bgworker and create communication between these process. I have a code based on test_shm_mq. This code fails because BackendPidGetProc doesn't find related bgworker process, although the registrant process is living One reason for this may be that the worker was not started with the flag BGWORKER_SHMEM_ACCESS which is necessary to perform InitProcess() that would initialize a PGPROC entry for it. But if you'd used the same method for initializing workers as test_shm_mq_setup(), then it should have. It seems in addition, a BackgroundWorkerInitializeConnection() is also necessary for the PGPROC entry of the worker to be visible to others. I do not see that done anywhere in test_shm_mq(); so perhaps that's missing? And these conditions apply to the bgworker that started another bgworker, that is the registrant bgworker. I think such a pattern does not exist in existing code. That is, normally all workers are started by a user backend that has a valid shared PGPROC entry. Thanks, Amit -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] best place for rtree strategy numbers
In Emre Hasegeli's patch for range/inet/geometry opclasses for BRIN, he chose to move strategy numbers from gist.h to a more central place. He chose skey.h because that's where btree strategy numbers are defined, but I'm not sure I agree with that choice. It's been clear for a while now that these numbers are not gist-only anyway; spgist and GIN also make use of them, and now brin inclusion opclass infrastructure also wants to use the same numbers. This is the reason for a central place, anyway. However, we find datatype specific definitions in places such as rangetypes.h: /* Operator strategy numbers used in the GiST and SP-GiST range opclasses */ /* Numbers are chosen to match up operator names with existing usages */ #define RANGESTRAT_BEFORE 1 #define RANGESTRAT_OVERLEFT 2 [ .. etc .. ] and network_gist.c: /* * Operator strategy numbers used in the GiST inet_ops opclass */ #define INETSTRAT_OVERLAPS 3 #define INETSTRAT_EQ18 #define INETSTRAT_NE19 [ .. etc .. ] Obviously, the expectation is that the numbers are not needed outside the opclass implementation itself. I think that's a lost cause, mainly because we want to have consistent operator names across datatypes. Therefore I would like to move those numbers elsewhere. Maybe we could have a new file which would be used only for strategy numbers, such as src/include/access/stratnum.h, and we would have something like this, and redefine the ones elsewhere to reference those. For instance, rangetypes.h could look like this: /* Operator strategy numbers used in the GiST and SP-GiST range opclasses */ /* Numbers are chosen to match up operator names with existing usages */ #define RANGESTRAT_BEFORE RTLeftStrategyNumber #define RANGESTRAT_OVERLEFT RTOverLeftStrategyNumber #define RANGESTRAT_OVERLAPS RTOverlapStrategyNumber and so on. (I am a bit hesitant about using the RT prefix in those symbols, since the set has grown quite a bit from R-Tree alone. But I don't have any ideas on what else to use either.) I can, of course, just leave these files well enough alone and just rely on the numbers not changing (which surely they won't anyway) and remaining consistent with numbers used in new opclasses (which surely they will lest they be born inconsistent with existing ones). Opinions? -- Á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] Final Patch for GROUPING SETS
On Thu, May 14, 2015 at 08:59:45AM +0200, Andres Freund wrote: On 2015-05-14 02:51:42 -0400, Noah Misch wrote: Covering hash aggregation might entail a large preparatory refactoring of nodeHash.c, but beyond development cost I can't malign that. You mean execGrouping.c? Afaics nodeHash.c isn't involved, and it doesn't look very interesting to make it so? That particular comment of mine was comprehensively wrong. -- 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_upgrade cleanup
On Thu, May 14, 2015 at 09:56:53PM -0400, Bruce Momjian wrote: This patch makes pg_upgrade controldata checks more consistent, and adds a missing check for float8_pass_by_value. Sorry, I should have mentioned I applied this patch to head. It isn't significant enough to backpatch. -- 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] upper planner path-ification
On Thu, May 14, 2015 at 7:09 AM, Robert Haas robertmh...@gmail.com wrote: Hi, I've been pulling over Tom's occasional remarks about redoing grouping_planner - and maybe further layers of the planner - to work with Paths instead of Plans. I've had difficulty locating all of the relevant threads. Here's everything I've found so far, which I'm pretty sure is not everything: http://www.postgresql.org/message-id/17400.1311716...@sss.pgh.pa.us http://www.postgresql.org/message-id/2614.1375730...@sss.pgh.pa.us http://www.postgresql.org/message-id/22721.1385048...@sss.pgh.pa.us http://www.postgresql.org/message-id/banlktindjjfhnozesg2j2u4gokqlu69...@mail.gmail.com http://www.postgresql.org/message-id/8479.1418420...@sss.pgh.pa.us I think there are two separate problems here. First, there's the problem that grouping_planner() is complicated. It's doing cost comparisons, but all in ad-hoc fashion rather than using a consistent mechanic the way add_path() does. Generally, we can plan an aggregate using either (1) a hashed aggregate, (2) a sorted aggregate, or (3) for min or max, an index scan that just grabs the highest or lowest value in lieu of a full table scan. Instead of generating a plan for each of these possibilities, we'd like to generate paths for each one, and then pick one to turn into a plan. AIUI, the hope is that this would simplify the cost calculations, and also make it easier to inject other paths, such as a path where an FDW performs the aggregation step on the remote server. Second, there's the problem that we might like to order aggregates with respect to joins. If we have something like SELECT DISTINCT ON (foo.x) foo.x, foo.y, bar.y FROM foo, bar WHERE foo.x = bar.x, then (a) if foo.x has many duplicates, it will be better to DISTINCT-ify foo and then join to bar afterwards but (b) if foo.x = bar.x is highly selective, it will be better to join to bar first and then DISTINCT-ify the result. Currently, aggregation is always last; that's not great. Hitoshi Harada's proposed strategy of essentially figuring out where the aggregation steps can go and then re-planning for each one is also not great, because each join problem will be a subtree of the one we use for the aggregate-last strategy, and thus we're wasting effort by planning the same subtrees multiple times. Instead, we might imagine letting grouping planner fish out the best paths for the joinrels that represent possible aggregation points, generate aggregation paths for each of those, and then work out what additional rels need to be joined afterwards. That sounds hard, but not as hard as doing something sensible with what we have today. Another futuristic avenue for optimization would be commuting Append and Join when joining two similarly partitioned tables. I'm inclined to think that it would be useful to solve the first problem even if we didn't solve the second one right away (but that might be wrong). As a preparatory step, I'm thinking it would be sensible to split grouping_planner() into an outer function that would handle the addition of Limit and LockRows nodes and maybe planning of set operations, and an inner function that would handle GROUP BY, DISTINCT, and possibly window function planning. Thoughts? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: [HACKERS] best place for rtree strategy numbers
Tom Lane wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: I can, of course, just leave these files well enough alone and just rely on the numbers not changing (which surely they won't anyway) and remaining consistent with numbers used in new opclasses (which surely they will lest they be born inconsistent with existing ones). Yeah, we do have checks in opr_sanity which will complain if inconsistent strategy numbers are used for similarly-named operators. That's a pretty weak test though, and operator names aren't exactly the right thing to check anyway. I'd be good with pushing all of that stuff to a new central header. So here's a patch for this. -- Álvaro Herrerahttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training Services commit 912acf2a0e622fec0837ff6773fc95621e216fa5 Author: Alvaro Herrera alvhe...@alvh.no-ip.org Date: Fri May 15 01:26:12 2015 -0300 Move strategy number definitions to their own file diff --git a/contrib/btree_gin/btree_gin.c b/contrib/btree_gin/btree_gin.c index 1a5bb3c..6e3bf17 100644 --- a/contrib/btree_gin/btree_gin.c +++ b/contrib/btree_gin/btree_gin.c @@ -5,7 +5,7 @@ #include limits.h -#include access/skey.h +#include access/stratnum.h #include utils/builtins.h #include utils/bytea.h #include utils/cash.h diff --git a/contrib/cube/cube.c b/contrib/cube/cube.c index b9ccad9..113c663 100644 --- a/contrib/cube/cube.c +++ b/contrib/cube/cube.c @@ -12,7 +12,7 @@ #include math.h #include access/gist.h -#include access/skey.h +#include access/stratnum.h #include utils/array.h #include utils/builtins.h diff --git a/contrib/hstore/hstore_gin.c b/contrib/hstore/hstore_gin.c index 68f9061..919181d 100644 --- a/contrib/hstore/hstore_gin.c +++ b/contrib/hstore/hstore_gin.c @@ -4,7 +4,7 @@ #include postgres.h #include access/gin.h -#include access/skey.h +#include access/stratnum.h #include catalog/pg_type.h #include hstore.h diff --git a/contrib/hstore/hstore_gist.c b/contrib/hstore/hstore_gist.c index 06f3c93..dde37fb 100644 --- a/contrib/hstore/hstore_gist.c +++ b/contrib/hstore/hstore_gist.c @@ -4,7 +4,7 @@ #include postgres.h #include access/gist.h -#include access/skey.h +#include access/stratnum.h #include catalog/pg_type.h #include utils/pg_crc.h diff --git a/contrib/intarray/_int_gin.c b/contrib/intarray/_int_gin.c index 58352ca..fb16b66 100644 --- a/contrib/intarray/_int_gin.c +++ b/contrib/intarray/_int_gin.c @@ -4,8 +4,7 @@ #include postgres.h #include access/gin.h -#include access/gist.h -#include access/skey.h +#include access/stratnum.h #include _int.h diff --git a/contrib/intarray/_intbig_gist.c b/contrib/intarray/_intbig_gist.c index 235db38..6dae7c91 100644 --- a/contrib/intarray/_intbig_gist.c +++ b/contrib/intarray/_intbig_gist.c @@ -4,7 +4,7 @@ #include postgres.h #include access/gist.h -#include access/skey.h +#include access/stratnum.h #include _int.h diff --git a/contrib/ltree/_ltree_gist.c b/contrib/ltree/_ltree_gist.c index 41be68d..37cd991 100644 --- a/contrib/ltree/_ltree_gist.c +++ b/contrib/ltree/_ltree_gist.c @@ -8,7 +8,7 @@ #include postgres.h #include access/gist.h -#include access/skey.h +#include access/stratnum.h #include crc32.h #include ltree.h diff --git a/contrib/ltree/ltree_gist.c b/contrib/ltree/ltree_gist.c index 2d89f1a..83da620 100644 --- a/contrib/ltree/ltree_gist.c +++ b/contrib/ltree/ltree_gist.c @@ -6,7 +6,7 @@ #include postgres.h #include access/gist.h -#include access/skey.h +#include access/stratnum.h #include crc32.h #include ltree.h diff --git a/contrib/pg_trgm/trgm_gin.c b/contrib/pg_trgm/trgm_gin.c index c59925c..d524cea 100644 --- a/contrib/pg_trgm/trgm_gin.c +++ b/contrib/pg_trgm/trgm_gin.c @@ -6,7 +6,8 @@ #include trgm.h #include access/gin.h -#include access/skey.h +#include access/stratnum.h +#include fmgr.h PG_FUNCTION_INFO_V1(gin_extract_trgm); diff --git a/contrib/pg_trgm/trgm_gist.c b/contrib/pg_trgm/trgm_gist.c index 69dc7f7..07d1dc3 100644 --- a/contrib/pg_trgm/trgm_gist.c +++ b/contrib/pg_trgm/trgm_gist.c @@ -5,7 +5,8 @@ #include trgm.h -#include access/skey.h +#include access/stratnum.h +#include fmgr.h typedef struct diff --git a/contrib/seg/seg.c b/contrib/seg/seg.c index 8e2d534..1e6c37d 100644 --- a/contrib/seg/seg.c +++ b/contrib/seg/seg.c @@ -12,7 +12,8 @@ #include float.h #include access/gist.h -#include access/skey.h +#include access/stratnum.h +#include fmgr.h #include segdata.h diff --git a/src/backend/access/gin/ginarrayproc.c b/src/backend/access/gin/ginarrayproc.c index 9c26e77..9220b5f 100644 --- a/src/backend/access/gin/ginarrayproc.c +++ b/src/backend/access/gin/ginarrayproc.c @@ -14,7 +14,7 @@ #include postgres.h #include access/gin.h -#include access/skey.h +#include access/stratnum.h #include utils/array.h #include utils/builtins.h #include utils/lsyscache.h diff --git a/src/backend/access/gist/gistproc.c
Re: [HACKERS] Providing catalog view to pg_hba.conf file - Patch submission
On Tue, May 5, 2015 at 6:48 AM, Peter Eisentraut pete...@gmx.net wrote: On 5/1/15 12:33 PM, Andres Freund wrote: On 2015-04-08 19:19:29 +0100, Greg Stark wrote: I'm not sure what the best way to handle the hand-off from patch contribution to reviewer/committer. If I start tweaking things then you send in a new version it's actually more work to resolve the conflicts. I think at this point it's easiest if I just take it from here. Are you intending to commit this? It still looks quite dubious to me. The more I test this, the more fond I grow of the idea of having this information available in SQL. But I'm also growing more perplexed by how this the file is mapped to a table. It just isn't a good match. For instance: What is keyword_databases? Why is it an array? Same for keyword_users. How can I know whether a given database or user matches a keyword? What is compare_method? (Should perhaps be keyword_address?) Why is compare method set to mask when a hostname is set? (Column order is also a bit confusing here.) I'd also like options to be jsonb instead of a text array. Thanks for your suggestion. I am not sure how to use jsonb here, i will study the same and provide a patch for the next version. Regards, Hari Babu Fujitsu Australia -- 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] BackendPidGetProc doesn't return PGPROC for background worker?
2015-05-15 3:39 GMT+02:00 Amit Langote langote_amit...@lab.ntt.co.jp: On 2015-05-15 AM 05:01, Pavel Stehule wrote: I am trying to start bgworker from bgworker and create communication between these process. I have a code based on test_shm_mq. This code fails because BackendPidGetProc doesn't find related bgworker process, although the registrant process is living One reason for this may be that the worker was not started with the flag BGWORKER_SHMEM_ACCESS which is necessary to perform InitProcess() that would initialize a PGPROC entry for it. But if you'd used the same method for initializing workers as test_shm_mq_setup(), then it should have. I have it Thanks, Amit
Re: [HACKERS] proposal: contrib module - generic command scheduler
2015-05-14 19:12 GMT+02:00 Jim Nasby jim.na...@bluetreble.com: On 5/14/15 1:36 AM, Pavel Stehule wrote: I don't think we want to log statements, but we should be able to log when a job has run and whether it succeeded or not. (log in a table, not just a logfile). This isn't something that can be done at higher layers either; only the scheduler will know if the job failed to even start, or whether it tried to run the job. I don't agree - generic scheduler can run your procedure, and there you can log start, you can run other commands and you can log result (now there is no problem to catch any production nonfatal exception). And what happens when the job fails to even start? You get no logging. Is only one case - when job is not started due missing worker. Else where is started topend executor, that can run in protected block. Personally I afraid about responsibility to maintain this log table - when and by who it should be cleaned, who can see results, ... This is job for top end scheduler. Only if the top-end scheduler has callbacks for everytime the bottom-end scheduler tries to start a job. Otherwise, the top has no clue what the bottom has actually attempted. sure. To be clear, I don't think these need to be done in a first pass. I am concerned about not painting ourselves into a corner though. I understand Regards Pavel -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
Re: [HACKERS] BackendPidGetProc doesn't return PGPROC for background worker?
On 2015-05-15 PM 02:13, Pavel Stehule wrote: 2015-05-15 3:39 GMT+02:00 Amit Langote langote_amit...@lab.ntt.co.jp: One reason for this may be that the worker was not started with the flag BGWORKER_SHMEM_ACCESS which is necessary to perform InitProcess() that would initialize a PGPROC entry for it. But if you'd used the same method for initializing workers as test_shm_mq_setup(), then it should have. I have it See my other (the last) email. You said you created a bgworker from bgworker, so if the parent bgworker did not perform BackgroundWorkerInitializeConnection, then it would not be found in ProcArray. Thanks, Amit -- 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] upper planner path-ification
On Thu, May 14, 2015 at 8:24 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Wed, May 13, 2015 at 10:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: For the reasons I mentioned, I'd like to get to a point where subquery_planner's output is Paths not Plans as soon as possible. But the idea of coarse representation of steps that we aren't trying to be smart about might be useful to save some labor in the short run. The zero-order version of that might be a single Path node type that represents do whatever grouping_planner would do, which we'd start to break down into multiple node types once we had the other APIs fixed. The problem I'm really interested in solving is gaining the ability to add additional aggregation strategies, such as letting an FDW do it remotely, or doing it in parallel. It seems to me that your proposed zero-order version of that wouldn't really get us terribly far toward that goal - it would be more oriented towards solving the other problems you mention, specifically adding more intelligence to setops and allowing parameterization of subqueries. Those things certainly have some value, but I think supporting alternate aggregation strategies is a lot more interesting. Clearly we'd like to get to both goals. I don't see the zero order design as something we'd ship or even have in the tree for more than a short time. But it might still be a useful refactorization step. In any case, the key question if we're to have Paths representing higher-level computations is what do we hang our lists of such Paths off of?. If we have say both GROUP BY and LIMIT, it's important to distinguish Paths that purport to do only the grouping step from those that do both the grouping and the limit. For the scan/join part of planning, we do this by attaching the Paths to RelOptInfos that denote various levels of joining ... but how does that translate to the higher level processing steps? Perhaps we could make dummy RelOptInfos that correspond to having completed different steps of processing; but I've not got a clear idea about how many such RelOptInfos we'd need, and in particular not about whether we need to cater for completing those steps in different orders. Given that the results of such computations are relations, having a RelOptInfo for each operation looks natural. Although, Sort/Order, which doesn't alter the result-set but just re-orders it, may be an exception here. It can be modeled as a property of some RelOptInfo rather than modelling as a RelOptInfo by itself. Same might be the case of row locking operation. As Robert has mentioned earlier, reordering does open opportunities for optimizations and hence can not be ignored. If we are restructuring the planner, we should restructure to enable such reordering. One possible solution would be to imitate make_one_rel(). make_one_rel() creates possible relations (and paths for each of them) which represent joining order for a given joinlist. Similarly, given a list of operations requested by user, a function would generate relations (and paths for each of them) which represent possible orders of those operations (operations also include JOINs). But that might explode the number of relations (and hence paths) we examine during planning. 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 -- Best Wishes, Ashutosh Bapat EnterpriseDB Corporation The Postgres Database Company
Re: [HACKERS] Final Patch for GROUPING SETS
On Thu, May 14, 2015 at 07:50:31AM +0200, Andres Freund wrote: I still believe that the general approach of chaining vs. a union or CTE is correct due to the efficiency arguments upthread. My problem is that, unless I very much misunderstand something, the current implementation can end up requiring roughly #sets * #input of additional space for the sidechannel tuplestore in some bad cases. That happens if you group by a couple clauses that each lead to a high number of groups. Correct. Andrew, is that a structure you could live with, or not? Others, what do you think? Andrew and I discussed that very structure upthread: http://www.postgresql.org/message-id/20141231085845.ga2148...@tornado.leadboat.com http://www.postgresql.org/message-id/87d26zd9k8@news-spur.riddles.org.uk http://www.postgresql.org/message-id/20141231210553.gb2159...@tornado.leadboat.com I still believe the words I wrote in my two messages cited. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CLUSTER on brin indexes
Hi Here's an idea for a small 9.6 feature that I don't see on the todo list already. I noticed that you can't currently CLUSTER on a brin index. As I understand it, brin indexes are most effective when blocks have non-overlapping value ranges, and less effective the more the ranges overlap, so it seems reasonable (essential?) to want to do this, and the machinery to do it is nearly all there already. Or am I missing some fundamental reason why this can't work? Poking around in the code, I see that you can only cluster on index types that have amclusterable set, which currently means btree and gist. I wonder if this could be split up into 3 separate concepts: 1. This index type supports CLUSTER (without saying how). True for btree and gist, ought to be true for brin. That's amclusterable. 2. This index type can do ordered index scans (and therefore scanning is an option when you run CLUSTER). True for btree and gist, false for brin. I think this probably amounts to amcanorder || amcanorderbyop. 3. This index type can describe the best clustering order in terms that tuplesort can handle. True for btree and false for gist (effectively, currently this is hardcoded), and ought to be true for brin. Maybe there could be a new attribute for this, amhasclusterorderby (insert better name here), and a new operation amclusterorderby (insert better name here) which could give tuplesort_begin_cluster the information that it currently extracts from btree indexes via a non-generic interface. Another approach could be to allow user-defined orderings, something like CLUSTER my_table ORDER BY ..., but I don't know if you'd ever want to do clustering that wasn't based on information that can be pulled out of an index. I thought about that first when trying to figure out how to use brin indexes effectively, before I realised that I really just wanted CLUSTER to understand brin indexes. Also, it occurred to me that if you have CLUSTERed a table by a brin index and then nobody has touched any tuples in the blocks you are interested in since then and somehow you know these facts, then you know something potentially useful about the physical order of the tuples within those blocks. (It may be extremely difficult to make use of that information, I have no idea, so maybe this is crazy talk.) -- Thomas Munro http://www.enterprisedb.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] Final Patch for GROUPING SETS
On Thu, May 14, 2015 at 08:38:07AM +0200, Andres Freund wrote: On 2015-05-14 02:32:04 -0400, Noah Misch wrote: On Thu, May 14, 2015 at 07:50:31AM +0200, Andres Freund wrote: Andrew, is that a structure you could live with, or not? Others, what do you think? Andrew and I discussed that very structure upthread: http://www.postgresql.org/message-id/87d26zd9k8@news-spur.riddles.org.uk I don't really believe that that'd necesarily be true. I think if done like I sketched it'll likely end up being simpler than the currently proposed code. I also don't see why this would make combining hashing and sorting any more complex than now. If anything the contrary. http://www.postgresql.org/message-id/20141231085845.ga2148...@tornado.leadboat.com http://www.postgresql.org/message-id/20141231210553.gb2159...@tornado.leadboat.com I still believe the words I wrote in my two messages cited. I.e. that you think it's a sane approach, despite the criticism? Yes. I won't warrant that it proves better, but it looks promising. Covering hash aggregation might entail a large preparatory refactoring of nodeHash.c, but beyond development cost I can't malign that. -- 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] proposal: contrib module - generic command scheduler
2015-05-14 8:01 GMT+02:00 Jim Nasby jim.na...@bluetreble.com: On 5/13/15 1:32 AM, Pavel Stehule wrote: 5. When command waits to free worker, write to log 6. When command was not be executed due missing workers (and max_workers 0), write to log Also unfortunate. We already don't provide enough monitoring capability and this just makes that worse. theoretically it can be supported some pg_stat_ view - but I would not to implement a some history table for commands. Again it is task for higher layers. I don't think we want to log statements, but we should be able to log when a job has run and whether it succeeded or not. (log in a table, not just a logfile). This isn't something that can be done at higher layers either; only the scheduler will know if the job failed to even start, or whether it tried to run the job. I don't agree - generic scheduler can run your procedure, and there you can log start, you can run other commands and you can log result (now there is no problem to catch any production nonfatal exception). Personally I afraid about responsibility to maintain this log table - when and by who it should be cleaned, who can see results, ... This is job for top end scheduler. Regards Pavel -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.com
Re: [HACKERS] pgAdmin4 Bug fix or my Fault ?
Bruce, Thank you for webpage trick, I send subscrible mail to pgadmin-hackers-request. Actually I want to developer for pgadmin4. I talk with Dave, He say, ... You should use git diff to create patches and then submit them to pgadmin-hackers ... So, If I want run pgadmin4 in Debian Jessie, need the patch code. I send git diff beacause, cant run pgadmin4-git-cloned version in jessie. There are really bugs? or is it my fault ? Sorry my English is not good, I hope you understood my problem. On Thu, May 14, 2015 at 2:32 AM, Bruce Momjian br...@momjian.us wrote: I think you want PGAdmin support: http://www.pgadmin.org/support/list.php Also, why isn't the non-subscribe email address listed on that webpage? --- On Thu, May 14, 2015 at 12:11:15AM +0300, Seçkin Alan wrote: Hi, I am using Debian Jessie and install pgAdmin4 Required modules. after I clone pgAdmin4 from http://git.postgresql.org/gitweb/?p=pgadmin4.git;a=summary , First of, ıf I want run setup.py, I must fix bug . after I want run pgadmin4.py, I must fix gravatar import line. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Seçkin ALAN http://sckn.org -- 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] Final Patch for GROUPING SETS
On 2015-05-14 02:51:42 -0400, Noah Misch wrote: Covering hash aggregation might entail a large preparatory refactoring of nodeHash.c, but beyond development cost I can't malign that. You mean execGrouping.c? Afaics nodeHash.c isn't involved, and it doesn't look very interesting to make it so? Isn't that just calling BuildTupleHashTable() for each to-be-hash-aggregated set, and then make agg_fill_hash_table() target multiple hashtables? This mostly seems to be adding a couple loops and parameters. 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] upper planner path-ification
Hello, this topic lured me on.. At Wed, 13 May 2015 23:43:57 -0400, Robert Haas robertmh...@gmail.com wrote in ca+tgmoaqa6bcasgcl8toxwmmoom-d7ebesadz4y58cb+tjq...@mail.gmail.com On Wed, May 13, 2015 at 10:27 PM, Tom Lane t...@sss.pgh.pa.us wrote: Both of those are problems all right, but there is more context here. Thanks for providing the context. I'm inclined to think that it would be useful to solve the first problem even if we didn't solve the second one right away (but that might be wrong). As a preparatory step, I'm thinking it would be sensible to split grouping_planner() into an outer function that would handle the addition of Limit and LockRows nodes and maybe planning of set operations, and an inner function that would handle GROUP BY, DISTINCT, and possibly window function planning. For the reasons I mentioned, I'd like to get to a point where subquery_planner's output is Paths not Plans as soon as possible. But the idea of coarse representation of steps that we aren't trying to be smart about might be useful to save some labor in the short run. The zero-order version of that might be a single Path node type that represents do whatever grouping_planner would do, which we'd start to break down into multiple node types once we had the other APIs fixed. The problem I'm really interested in solving is gaining the ability to add additional aggregation strategies, such as letting an FDW do it remotely, or doing it in parallel. It seems to me that your proposed zero-order version of that wouldn't really get us terribly far toward that goal - it would be more oriented towards solving the other problems you mention, specifically adding more intelligence to setops and allowing parameterization of subqueries. Those things certainly have some value, but I think supporting alternate aggregation strategies is a lot more interesting. I don't know which you are planning the interface between the split layers to be path or plan, I guess simply splitting grouping_planner results in the latter. Although Tom's path representation up to higher layer does not seem to clash with, or have anything to do directly with your layer splitting, complicating the existing complication should make it more difficult to refactor the planner to have more intelligence. For example, Limit could be pushed down into bottom of a path tree ideally and would be also effective for remote node greately like remote aggregations would be. The zero-order version would eliminate subqery scan (or frozen plan) path or such nodes (if any) which obstruct possible global optimization. But simple splitting of the current grouping_planner looks make it more difficult. It is so frustrating not to be able to express my thought well but IMHO, shortly, I wish the planner not to grow to be in such form. Certainly it seems not so easy to get over the zero-order version, but.. regards, -- Kyotaro Horiguchi NTT Open Source Software Center -- 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] proposal: contrib module - generic command scheduler
On 5/13/15 1:32 AM, Pavel Stehule wrote: 5. When command waits to free worker, write to log 6. When command was not be executed due missing workers (and max_workers 0), write to log Also unfortunate. We already don't provide enough monitoring capability and this just makes that worse. theoretically it can be supported some pg_stat_ view - but I would not to implement a some history table for commands. Again it is task for higher layers. I don't think we want to log statements, but we should be able to log when a job has run and whether it succeeded or not. (log in a table, not just a logfile). This isn't something that can be done at higher layers either; only the scheduler will know if the job failed to even start, or whether it tried to run the job. -- Jim Nasby, Data Architect, Blue Treble Consulting Data in Trouble? Get it in Treble! http://BlueTreble.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] Final Patch for GROUPING SETS
On 2015-05-14 02:32:04 -0400, Noah Misch wrote: On Thu, May 14, 2015 at 07:50:31AM +0200, Andres Freund wrote: Andrew, is that a structure you could live with, or not? Others, what do you think? Andrew and I discussed that very structure upthread: http://www.postgresql.org/message-id/87d26zd9k8@news-spur.riddles.org.uk I don't really believe that that'd necesarily be true. I think if done like I sketched it'll likely end up being simpler than the currently proposed code. I also don't see why this would make combining hashing and sorting any more complex than now. If anything the contrary. http://www.postgresql.org/message-id/20141231085845.ga2148...@tornado.leadboat.com http://www.postgresql.org/message-id/20141231210553.gb2159...@tornado.leadboat.com I still believe the words I wrote in my two messages cited. I.e. that you think it's a sane approach, despite the criticism? 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] PATCH: pgbench allow '=' in \set
Hello Robert, Sorry resent, wrong from Also, having ; as a end of commands could also help by allowing multiline commands, but that would break compatibility. Maybe allowing continuations (\\\n) would be an acceptable compromise. I loathe violently the convention of using a backslash at the end of a line, because it's too easy to write backslash-space-newline or backslash-tab-newline when you meant to write backslash-newline. But maybe we should do it anyway. We certainly need some solution to that problem, because the status quo is monumentally annoying, and that might be the least bad solution available. I survive with that in bash/make/python... Another option, breaking backward compatibility, would be to decide that backslash commands have to be terminated by a semicolon token. I do not like it much, as it is inconsistent/incompatible with psql. [...] multi-line SQL queries. If we wanted to make that work, the best option might be to duplicate the backend lexer into pgbench just as we already do with psql. [...] I somewhat lean toward this second option, because I think it will be a lot more convenient in the long run. We'll probably get some complains about breaking people's pgbench scripts, but I'd personally be prepared to accept that as the price of progress. For an actual lexer: currently there is no real lexer for SQL commands in pgbench, the line is just taken as is, so that would mean adding another one, although probably a simplified one would do. To conclude, I'm rather for continuations, despite their ugliness, because (1) it is much easier (just a very small change in read_line_from_file) and (2) it is backward compatible, so no complaints handle. -- Fabien. -- 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] Proposal : REINDEX xxx VERBOSE
On Thu, May 14, 2015 at 9:58 AM, Robert Haas robertmh...@gmail.com wrote:
On Wed, May 13, 2015 at 8:25 PM, Sawada Masahiko sawada.m...@gmail.com
wrote:
The v15 patch emits a line for each table when reindexing multiple
tables, and emits a line for each index when reindexing single table.
But v14 patch emits a line for each index, regardless of reindex target.
Should I change back to v14 patch?
Uh, maybe. What made you change it?
I thought that the users who want to reindex multiple tables are
interested in the time to reindex whole table takes.
But I think it seems sensible to emit a line for each index even when
reindex multiple tables.
The v16 patch is based on v14 and a few modified is attached.
Regards,
---
Sawada Masahiko
diff --git a/doc/src/sgml/ref/reindex.sgml b/doc/src/sgml/ref/reindex.sgml
index 998340c..703b760 100644
--- a/doc/src/sgml/ref/reindex.sgml
+++ b/doc/src/sgml/ref/reindex.sgml
@@ -21,7 +21,7 @@ PostgreSQL documentation
refsynopsisdiv
synopsis
-REINDEX { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } replaceable class=PARAMETERname/replaceable
+REINDEX [ ( { VERBOSE } [, ...] ) ] { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } replaceable class=PARAMETERname/replaceable
/synopsis
/refsynopsisdiv
@@ -150,6 +150,15 @@ REINDEX { INDEX | TABLE | SCHEMA | DATABASE | SYSTEM } replaceable class=PARAM
/para
/listitem
/varlistentry
+
+ varlistentry
+termliteralVERBOSE/literal/term
+listitem
+ para
+ Prints a progress report as each index is reindexed.
+ /para
+/listitem
+ /varlistentry
/variablelist
/refsect1
diff --git a/src/backend/catalog/index.c b/src/backend/catalog/index.c
index 8c8a9ea..bac9fbe 100644
--- a/src/backend/catalog/index.c
+++ b/src/backend/catalog/index.c
@@ -63,6 +63,7 @@
#include utils/inval.h
#include utils/lsyscache.h
#include utils/memutils.h
+#include utils/pg_rusage.h
#include utils/syscache.h
#include utils/tuplesort.h
#include utils/snapmgr.h
@@ -3184,13 +3185,17 @@ IndexGetRelation(Oid indexId, bool missing_ok)
* reindex_index - This routine is used to recreate a single index
*/
void
-reindex_index(Oid indexId, bool skip_constraint_checks, char persistence)
+reindex_index(Oid indexId, bool skip_constraint_checks, char persistence,
+int options)
{
Relation iRel,
heapRelation;
Oid heapId;
IndexInfo *indexInfo;
volatile bool skipped_constraint = false;
+ PGRUsage ru0;
+
+ pg_rusage_init(ru0);
/*
* Open and lock the parent heap relation. ShareLock is sufficient since
@@ -3334,6 +3339,14 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence)
heap_close(pg_index, RowExclusiveLock);
}
+ /* Log what we did */
+ if (options REINDEXOPT_VERBOSE)
+ ereport(INFO,
+(errmsg(index \%s\ was reindexed,
+ get_rel_name(indexId)),
+ errdetail(%s.,
+ pg_rusage_show(ru0;
+
/* Close rels, but keep locks */
index_close(iRel, NoLock);
heap_close(heapRelation, NoLock);
@@ -3375,7 +3388,7 @@ reindex_index(Oid indexId, bool skip_constraint_checks, char persistence)
* index rebuild.
*/
bool
-reindex_relation(Oid relid, int flags)
+reindex_relation(Oid relid, int flags, int options)
{
Relation rel;
Oid toast_relid;
@@ -3466,7 +3479,7 @@ reindex_relation(Oid relid, int flags)
RelationSetIndexList(rel, doneIndexes, InvalidOid);
reindex_index(indexOid, !(flags REINDEX_REL_CHECK_CONSTRAINTS),
- persistence);
+ persistence, options);
CommandCounterIncrement();
@@ -3501,7 +3514,7 @@ reindex_relation(Oid relid, int flags)
* still hold the lock on the master table.
*/
if ((flags REINDEX_REL_PROCESS_TOAST) OidIsValid(toast_relid))
- result |= reindex_relation(toast_relid, flags);
+ result |= reindex_relation(toast_relid, flags, options);
return result;
}
diff --git a/src/backend/commands/cluster.c b/src/backend/commands/cluster.c
index 3febdd5..7ab4874 100644
--- a/src/backend/commands/cluster.c
+++ b/src/backend/commands/cluster.c
@@ -1532,7 +1532,7 @@ finish_heap_swap(Oid OIDOldHeap, Oid OIDNewHeap,
else if (newrelpersistence == RELPERSISTENCE_PERMANENT)
reindex_flags |= REINDEX_REL_FORCE_INDEXES_PERMANENT;
- reindex_relation(OIDOldHeap, reindex_flags);
+ reindex_relation(OIDOldHeap, reindex_flags, 0);
/*
* If the relation being rebuild is pg_class, swap_relation_files()
diff --git a/src/backend/commands/indexcmds.c b/src/backend/commands/indexcmds.c
index 351d48e..7340a1f 100644
--- a/src/backend/commands/indexcmds.c
+++ b/src/backend/commands/indexcmds.c
@@ -1681,7 +1681,7 @@ ChooseIndexColumnNames(List *indexElems)
* Recreate a specific index.
*/
Oid
-ReindexIndex(RangeVar *indexRelation)
+ReindexIndex(RangeVar *indexRelation, int options)
{
Oid indOid;
Oid heapOid = InvalidOid;
@@ -1706,7 +1706,7 @@ ReindexIndex(RangeVar *indexRelation)
persistence = irel-rd_rel-relpersistence;
index_close(irel, NoLock);
-
Re: [HACKERS] psql :: support for \ev viewname and \sv viewname
This version contains one little change. In order to be consistent with “\d+ viewname” it uses pg_get_viewdef(oid, /* pretty */ true) to produce “pretty” output (without additional parentheses). psql-ev-sv-support-v2.diff Description: Binary data On 05 May 2015, at 16:42, Robert Haas robertmh...@gmail.com wrote: On Mon, May 4, 2015 at 5:21 AM, Petr Korobeinikov pkorobeini...@gmail.com wrote: I'm proposing to add two new subcommands in psql: 1. \ev viewname - edit view definition with external editor (like \ef for function) 2. \sv viewname - show view definition (like \sf for function, for consistency) Sounds nice. Make sure to add your patch to the open CommitFest so we don't forget about it. https://commitfest.postgresql.org/action/commitfest_view/open -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] pgAdmin4 Bug fix or my Fault ?
Hi, owww So sorry :( On Thu, May 14, 2015 at 11:46 AM, Dave Page dp...@pgadmin.org wrote: Hi On Thu, May 14, 2015 at 7:44 AM, Seçkin Alan seckina...@gmail.com wrote: Bruce, Thank you for webpage trick, I send subscrible mail to pgadmin-hackers-request. Actually I want to developer for pgadmin4. I talk with Dave, He say, ... You should use git diff to create patches and then submit them to pgadmin-hackers ... pgadmin-hack...@postgresql.org != pgsql-hackers@postgresql.org :-) So, If I want run pgadmin4 in Debian Jessie, need the patch code. I send git diff beacause, cant run pgadmin4-git-cloned version in jessie. There are really bugs? or is it my fault ? Sorry my English is not good, I hope you understood my problem. On Thu, May 14, 2015 at 2:32 AM, Bruce Momjian br...@momjian.us wrote: I think you want PGAdmin support: http://www.pgadmin.org/support/list.php Also, why isn't the non-subscribe email address listed on that webpage? --- On Thu, May 14, 2015 at 12:11:15AM +0300, Seçkin Alan wrote: Hi, I am using Debian Jessie and install pgAdmin4 Required modules. after I clone pgAdmin4 from http://git.postgresql.org/gitweb/?p=pgadmin4.git;a=summary , First of, ıf I want run setup.py, I must fix bug . after I want run pgadmin4.py, I must fix gravatar import line. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Seçkin ALAN http://sckn.org -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Seçkin ALAN http://sckn.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Minor improvement to create_foreign_table.sgml
Hi,
The attached patch adds missing NO INHERIT to the CHECK clause in the
synopsis section in the reference page on CREATE FOREIGN TABLE.
Best regards,
Etsuro Fujita
diff --git a/doc/src/sgml/ref/create_foreign_table.sgml b/doc/src/sgml/ref/create_foreign_table.sgml
index abadd83..413b033 100644
--- a/doc/src/sgml/ref/create_foreign_table.sgml
+++ b/doc/src/sgml/ref/create_foreign_table.sgml
@@ -32,13 +32,13 @@ CREATE FOREIGN TABLE [ IF NOT EXISTS ] replaceable class=PARAMETERtable_name
[ CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ]
{ NOT NULL |
NULL |
- CHECK ( replaceable class=PARAMETERexpression/replaceable ) |
+ CHECK ( replaceable class=PARAMETERexpression/replaceable ) [ NO INHERIT ] |
DEFAULT replaceabledefault_expr/replaceable }
phraseand replaceable class=PARAMETERtable_constraint/replaceable is:/phrase
[ CONSTRAINT replaceable class=PARAMETERconstraint_name/replaceable ]
-CHECK ( replaceable class=PARAMETERexpression/replaceable )
+CHECK ( replaceable class=PARAMETERexpression/replaceable ) [ NO INHERIT ]
/synopsis
/refsynopsisdiv
--
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] Final Patch for GROUPING SETS
Andres == Andres Freund and...@anarazel.de writes: Andres My problem is that, unless I very much misunderstand something, Andres the current implementation can end up requiring roughly #sets * Andres #input of additional space for the sidechannel tuplestore in Andres some bad cases. That happens if you group by a couple clauses Andres that each lead to a high number of groups. The actual upper bound for the tuplestore size is the size of the _result_ of the grouping, less one or two rows. You get that in cases like grouping sets (unique_col, rollup(constant_col)), which seems sufficiently pathological not to be worth worrying about greatly. In normal cases, the size of the tuplestore is the size of the result minus the rows processed directly by the top node. So the only way the size can be an issue is if the result set size itself is also an issue, and in that case I don't really think that this is going to be a matter of significant concern. Andres A rough sketch of what I'm thinking of is: I'm not sure I'd do it quite like that. Rather, have a wrapper function get_outer_tuple that calls ExecProcNode and, if appropriate, writes the tuple to a tuplesort before returning it; use that in place of ExecProcNode in agg_retrieve_direct and when building the hash table. The problem with trying to turn agg_retrieve_direct inside-out (to make it look more like agg_retrieve_chained) is that it potentially projects multiple output groups (not just multiple-result projections) from a single input tuple, so it has to have some control over whether a tuple is read or not. (agg_retrieve_chained avoids this problem because it can loop over the projections, since it's writing to the tuplestore rather than returning to the caller.) Andres I think this is quite doable and seems likely to actually end Andres up with easier to understand code. But unfortunately it seems Andres to be big enough of a change to make it unlikely to be done in Andres sufficient quality until the freeze. I'll nonetheless work a Andres couple hours on it tomorrow. Andres Andrew, is that a structure you could live with, or not? Well, I still think the opaque-blobless isn't nice, but I retract some of my previous concerns; I can see a way to do it that doesn't significantly impinge on the difficulty of adding hash support. It sounds like I have more time immediately available than you do. As discussed on IRC, I'll take the first shot, and we'll see how far I can get. -- Andrew (irc:RhodiumToad) -- 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] pgAdmin4 Bug fix or my Fault ?
Hi On Thu, May 14, 2015 at 7:44 AM, Seçkin Alan seckina...@gmail.com wrote: Bruce, Thank you for webpage trick, I send subscrible mail to pgadmin-hackers-request. Actually I want to developer for pgadmin4. I talk with Dave, He say, ... You should use git diff to create patches and then submit them to pgadmin-hackers ... pgadmin-hack...@postgresql.org != pgsql-hackers@postgresql.org :-) So, If I want run pgadmin4 in Debian Jessie, need the patch code. I send git diff beacause, cant run pgadmin4-git-cloned version in jessie. There are really bugs? or is it my fault ? Sorry my English is not good, I hope you understood my problem. On Thu, May 14, 2015 at 2:32 AM, Bruce Momjian br...@momjian.us wrote: I think you want PGAdmin support: http://www.pgadmin.org/support/list.php Also, why isn't the non-subscribe email address listed on that webpage? --- On Thu, May 14, 2015 at 12:11:15AM +0300, Seçkin Alan wrote: Hi, I am using Debian Jessie and install pgAdmin4 Required modules. after I clone pgAdmin4 from http://git.postgresql.org/gitweb/?p=pgadmin4.git;a=summary , First of, ıf I want run setup.py, I must fix bug . after I want run pgadmin4.py, I must fix gravatar import line. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + Everyone has their own god. + -- Seçkin ALAN http://sckn.org -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] trust authentication behavior
Hello guys, Is it possible to restrict the trust auth method to accept local connections only using the selinux policy? Thank you! -- 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] multivariate statistics / patch v6
On 05/13/15 10:31, Kyotaro HORIGUCHI wrote:
Hello, this might be somewhat out of place but strongly related
to this patch so I'll propose this here.
This is a proposal of new feature for this patch or asking for
your approval for my moving on this as a different (but very
close) project.
===
Attached is v6 of the multivariate stats, with a number of
improvements:
...
2) fix of pg_proc issues (reported by Jeff)
3) rebase to current master
Unfortunately, the v6 patch suffers some system oid conflicts
with recently added ones. And what more unfortunate for me is
that the code for functional dependencies looks undone:)
I'll fix the OID conflicts once the CF completes, which should be in a
few days I guess. Until then you can apply it on top of master from
about May 6 (that's when the v6 was created, and there should be no
conflicts).
Regarding the functional dependencies - you're right there's room for
improvement. For example it only works with dependencies between pairs
of columns, not multi-column dependencies. Is this what you mean by
incomplete?
I mention this because I recently had a issue from strong
correlation between two columns in dbt3 benchmark. Two columns in
some table are in strong correlation but not in functional
dependencies, there are too many values and the distribution of
them is very uniform so MCV is no use for the table (histogram
has nothing to do with equal conditions). As the result, planner
estimates the number of rows largely wrong as expected especially
for joins.
I think the other statistics types (esp. histograms) might be more
useful here, but I assume you haven't tried that because of the conflicts.
The current patch does not handle joins at all, though.
I, then, had a try calculating the ratio between the product of
distinctness of every column and the distinctness of the set of
the columns, call it multivariate coefficient here, and found
that it looks greately useful for the small storage space, less
calculation, and simple code.
So when you have two columns A and B, you compute this:
ndistinct(A) * ndistinct(B)
---
ndistinct(A,B)
where ndistinc(...) means number of distinct values in the column(s)?
The attached first is a script to generate problematic tables.
And the second is a patch to make use of the mv coef on current
master. The patch is a very primitive POC so no syntactical
interfaces involved.
For the case of your first example,
=# create table t (a int, b int, c int);
=# insert into t (select a/1, a/1, a/1
from generate_series(0, 99) a);
=# analyze t;
=# explain analyze select * from t where a = 1 and b = 1 and c = 1;
Seq Scan on t (cost=0.00..22906.00 rows=1 width=12)
(actual time=3.878..250.628 rows=1 loops=1)
Make use of mv coefficient.
=# insert into pg_mvcoefficient values ('t'::regclass, 1, 2, 3, 0);
=# analyze t;
=# explain analyze select * from t where a = 1 and b = 1 and c = 1;
Seq Scan on t (cost=0.00..22906.00 rows=9221 width=12)
(actual time=3.740..242.330 rows=1 loops=1)
Row number estimation was largely improved.
With my patch:
alter table t add statistics (mcv) on (a,b,c);
analyze t;
select * from pg_mv_stats;
tablename | attnums | mcvbytes | mcvinfo
---+-+--+
t | 1 2 3 | 2964 | nitems=100
explain (analyze,timing off)
select * from t where a = 1 and b = 1 and c = 1;
QUERY PLAN
Seq Scan on t (cost=0.00..22906.00 rows=9533 width=12)
(actual rows=1 loops=1)
Filter: ((a = 1) AND (b = 1) AND (c = 1))
Rows Removed by Filter: 99
Planning time: 0.233 ms
Execution time: 93.212 ms
(5 rows)
alter table t drop statistics all;
alter table t add statistics (histogram) on (a,b,c);
analyze t;
explain (analyze,timing off)
select * from t where a = 1 and b = 1 and c = 1;
QUERY PLAN
Seq Scan on t (cost=0.00..22906.00 rows=9667 width=12)
(actual rows=1 loops=1)
Filter: ((a = 1) AND (b = 1) AND (c = 1))
Rows Removed by Filter: 99
Planning time: 0.594 ms
Execution time: 109.917 ms
(5 rows)
So both the MCV list and histogram do quite a good work here, but there
are certainly cases when that does not work and the mvcoefficient works
better.
Well, my example,
$ perl gentbl.pl 1 | psql postgres
$ psql postgres
=# explain analyze select * from t1 where a = 1 and b = 2501;
Seq Scan on t1 (cost=0.00..6216.00 rows=1 width=8)
(actual time=0.030..66.005 rows=8 loops=1)
=# explain analyze select * from t1 join t2 on (t1.a = t2.a and t1.b = t2.b);
Hash Join (cost=1177.00..11393.76 rows=76 width=16)
(actual time=29.811..322.271 rows=32 loops=1)
Re: [HACKERS] Missing importing option of postgres_fdw
On 2015/04/30 2:10, Robert Haas wrote: On Mon, Apr 27, 2015 at 7:47 AM, Michael Paquier michael.paqu...@gmail.com wrote: Authorizing ALTER FOREIGN TABLE as query string that a FDW can use with IMPORT FOREIGN SCHEMA is a different feature than what is proposed in this patch, aka an option for postgres_fdw and meritates a discussion on its own because it impacts all the FDWs and not only postgres_fdw. Now, related to this patch, we could live without authorizing ALTER FOREIGN TABLE because CREATE FOREIGN TABLE does authorize the definition of CHECK constraints. I agree. I don't think there's a huge problem with allowing IMPORT FOREIGN SCHEMA to return ALTER FOREIGN TABLE statements, but it doesn't really seem to be necessary. I don't see why we can't just declare the CHECK constraints in the CREATE FOREIGN TABLE statement instead of adding more DDL. On second thought, I noticed that as for this option, we cannot live without allowing IMPORT FOREIGN SCHEMA to return ALTER FOREIGN TABLE statements because we cannot declare the convalidated information in the CREATE FOREIGN TABLE statement. So, I think we shoould also allow it to return ALTER FOREIGN TABLE statements. Am I right? Comments welcome. 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] KNN-GiST with recheck
On Wed, May 13, 2015 at 10:17 PM, Alexander Korotkov aekorot...@gmail.com wrote: One quick comment: It would be good to avoid the extra comparisons of the distances, when the index doesn't return any lossy items. As the patch stands, it adds one extra copyDistances() call and a cmp_distances() call for each tuple (in a knn-search), even if there are no lossy tuples. I will fix it until Friday. Attached patch is rebased against current master. Extra extra copyDistances() call and a cmp_distances() call for each tuple are avoided in the case of no lossy tuples. -- With best regards, Alexander Korotkov. knn-gist-recheck-9.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] Fix token exceeding NAMELEN
Aaron W. Swenson titanof...@gentoo.org writes: On 2015-05-13 18:16, Christopher Browne wrote: I thought that this restriction was alleviated years ago, so I'm a bit surprised to see this come up in 2015. (Or perhaps Gentoo hasn't yet opened up some limits??? :-) ) The restriction is alleviated (patched) by some distributions, and Gentoo isn't among those. It has been almost 4 years (the most recent Google has found) since the last time this happened with PostgreSQL's docs. http://www.postgresql.org/message-id/banlktiktw6srdygvfjrb4q+7dvwoqcc...@mail.gmail.com Ah, so we have hit it before and forgotten. Might as well stick to the previous decision then. Patch applied, thanks! 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
