[HACKERS] Support for cert auth in JDBC
Hello developers, My project had a requirement to use certificate authentication to the PG server. Our application uses Hibernate. We did just that and my boss has OKed a source release. Now, the current version of the code has dependencies on our internal libraries, so I'll need to spend a bit of time making this 'standard' Java code. Would you please tell me how you'd prefer for me to proceed to do that? Do I need write access to your CVS repo, or should I just send the code and test case by email? Is there a specific version of the JDBC code you want me to work from, should I just pick whatever is HEAD? Any package you'd like me to choose? Any specific crypto/ssl requirements to consider? Any specific dependencies to use instead of others? (e.g. I like SLF4J, but that's not everyone's choice...) -- Marc-André Laverdière Software Security Scientist Innovation Labs, Tata Consultancy Services Hyderabad, India -- 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] Support for cert auth in JDBC
Marc, Please just send a cvs context diff from HEAD to the JDBC list. Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca 2011/5/17 Marc-André Laverdière marc-an...@atc.tcs.com: Hello developers, My project had a requirement to use certificate authentication to the PG server. Our application uses Hibernate. We did just that and my boss has OKed a source release. Now, the current version of the code has dependencies on our internal libraries, so I'll need to spend a bit of time making this 'standard' Java code. Would you please tell me how you'd prefer for me to proceed to do that? Do I need write access to your CVS repo, or should I just send the code and test case by email? Is there a specific version of the JDBC code you want me to work from, should I just pick whatever is HEAD? Any package you'd like me to choose? Any specific crypto/ssl requirements to consider? Any specific dependencies to use instead of others? (e.g. I like SLF4J, but that's not everyone's choice...) -- Marc-André Laverdière Software Security Scientist Innovation Labs, Tata Consultancy Services Hyderabad, India -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Support for cert auth in JDBC
Marc-André, * Marc-André Laverdière (marc-an...@atc.tcs.com) wrote: Would you please tell me how you'd prefer for me to proceed to do that? Do I need write access to your CVS repo, or should I just send the code and test case by email? Ideally, you would submit the patch, as a context diff, to this mailing list and then add the patch to our 'CommitFest' system: http://commitfest.postgresql.org There is quite a bit of additional guideance on what a patch should look like, etc, here: http://wiki.postgresql.org/wiki/Submitting_a_Patch Is there a specific version of the JDBC code you want me to work from, should I just pick whatever is HEAD? I'm not too familiar with the JDBC parts, you might post this question to the JDBC mailing list. Any specific crypto/ssl requirements to consider? We currently use and pretty heavily depend on OpenSSL. I'm not sure how much that matters when it comes to JDBC. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Support for cert auth in JDBC
Stephen Frost sfr...@snowman.net writes: Marc-André, * Marc-André Laverdière (marc-an...@atc.tcs.com) wrote: Would you please tell me how you'd prefer for me to proceed to do that? Do I need write access to your CVS repo, or should I just send the code and test case by email? Ideally, you would submit the patch, as a context diff, to this mailing list and then add the patch to our 'CommitFest' system: http://commitfest.postgresql.org It sounded to me like this was a patch against the JDBC driver, not the core server, in which case the above advice would be incorrect. JDBC is developed by a separate project. You should join the pgsql-jdbc mailing list and send your patch there. 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] Extension Packaging
My apologies for wading in out of the blue here as a first time poster with big demands, but allow me to briefly state my hopes without trying to be too proscriptive about particular mechanisms. My hope here is that the extension model should eventually enable me to offer the ability for non-superuser databases to specify by some mechanism the extensions that they require in a reproducible fashion, enabling my users to recreate their local development conditions on a production cluster. My particular worry, and I apologize if I have misunderstood the thrust of this thread, is that extension version might not be tied to the extension revision, and so I will not be able to determine whether or not all existing extensions are already at a specific version. The precision of this process is very important to me. My intended use case for this feature is to allow users to specify the versions of extensions that they need in some kind of a control file or in a database migration script such that they can then install those extensions on various new systems in a reliable and reproducible way. David, if you do what you propose, haven't I already lost? --- Peter van Hardenberg Heroku On Wed, May 11, 2011 at 7:48 PM, David E. Wheeler da...@kineticode.comwrote: On May 11, 2011, at 2:47 PM, Robert Haas wrote: Okay, how we add a revision key to the control file and extrevision to the pg_extension catalog. Its type can be TEXT and is optional for use by extensions. This would allow extension authors to identify the base version of an extension but also the revision. And the core doesn't have to care how it works or if it's used, but it would allow users to know exactly what they have installed. Thoughts? How would pg_extension.extrevision be kept up to date? AFAICS, the whole point is that you might swap out the shared libraries without doing anything at the SQL level. Bah! Okay, I give up. I'll not worry about it right now, as I have only one C extension outside of core and it won't change much in the code. And I'll just keep using the full version string (x.y.z) for the upgrade scripts. What I won't do is change that version with every release, unless there is a code change to demand it. The distribution version can increment independently. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Peter van Hardenberg San Francisco, California Everything was beautiful, and nothing hurt. -- Kurt Vonnegut
Re: [HACKERS] Extension Packaging
On May 17, 2011, at 9:44 AM, Peter van Hardenberg wrote: My apologies for wading in out of the blue here as a first time poster with big demands, but allow me to briefly state my hopes without trying to be too proscriptive about particular mechanisms. You are not alone, I assure you. :-) My hope here is that the extension model should eventually enable me to offer the ability for non-superuser databases to specify by some mechanism the extensions that they require in a reproducible fashion, enabling my users to recreate their local development conditions on a production cluster. Yeah. Right now I believe this can only be done for extension that don't require a super user. And IIRC, all C-based extensions require a super user. My particular worry, and I apologize if I have misunderstood the thrust of this thread, is that extension version might not be tied to the extension revision, and so I will not be able to determine whether or not all existing extensions are already at a specific version. Well, nothing has happened in that regard. It's too late for 9.1, and there wasn't consensus, anyway. So right now, the installed extension version is the installed extension version. There is, however, no indication of any meaning or order to versions. They're just strings of text. The precision of this process is very important to me. My intended use case for this feature is to allow users to specify the versions of extensions that they need in some kind of a control file or in a database migration script such that they can then install those extensions on various new systems in a reliable and reproducible way. This is do-able. David, if you do what you propose, haven't I already lost? No. I was suggesting that there be some sort of function, pg_extension_version($ext_name), that would return the version and the revision. Combined they would equal the version you're interested in. I'm not thrilled with this approach, though, and it's not there, so for now we have the wild west of versions. So for now, what you want (modulo permissions issues) is what's there, IIUC. Best, David -- 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] use less space in xl_xact_commit patch
On Mon, May 16, 2011 at 11:20 AM, Leonardo Francalanci m_li...@yahoo.it wrote: following the conversation at http://postgresql.1045698.n5.nabble.com/switch-UNLOGGED-to-LOGGED-tp4290461p4382333.html I tried to remove some bytes from xl_xact_commit. The way I did it needs palloc+memcpy. I guess it could be done reusing the memory for smgrGetPendingDeletes. But I don't think it's that important. I guess there are other ways of doing it; let me know what you think. I don't think there's much point to the xl_xact_commit_opt structure; it doesn't really do anything. What I would do is end the xl_xact_commit structure with something like: int counts[1]; /* variable-length array of counts, xinfo flags define length of array and meaning of counts */ Then, I'd make macros like this: #define XactCommitNumberOfDroppedRelFileNodes(xlrec) \ ((xlref-xinfo XACT_COMMIT_DROPPED_RELFILENODES) ? xlrec-counts[0] : 0) #define XactCommitNumberOfCommittedSubXids(xlrec) \ ((xlref-xinfo XACT_COMMITED_SUBXDIDS) ? xlrec-counts[(xlrec-xinfo XACT_COMMIT_DROPPED_RELFILENODES) ? 1 : 0] : 0) ...etc... ...and a similar set of macros that will return a pointer to the beginning of the corresponding array, if it's present. I'd lay out the record like this: - main record - array of counts (might be zero-length) - array of dropped relfilnodes (if any) - array of committed subxids (if any) - array of sinval messages (if any) Also, it's important not to confuse xact completion with xact commit, as I think some of your naming does. Completion could perhaps be thought to include abort. -- 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] Review: psql include file using relative path
On Sat, May 14, 2011 at 5:03 PM, Josh Kupershmidt schmi...@gmail.com wrote: I had a chance to give this patch a look. This review is of the second patch posted by Gurjeet, at: http://archives.postgresql.org/message-id/AANLkTi=yjb_a+ggt_pxmrqhbhyid6aswwb8h-lw-k...@mail.gmail.com Cool. I see you (or someone) has added this to the entry for that patch on commitfest.postgresql.org as well, which is great. I have updated that entry to list you as the reviewer and changed the status of the patch to Waiting on Author pending resolution of the issues you observed. == General == The patch applies cleanly to HEAD. No regression tests are included, but I don't think they're needed here. I agree. == Documentation == The patch includes the standard psql help output description for the new \ir command. I think ./doc/src/sgml/ref/psql-ref.sgml needs to be patched as well, though. I agree with this too. Tangent: AFAICT we're not documenting the long form of psql commands, such as \print, anywhere. Following that precedent, this patch doesn't document \include_relative. Not sure if we want to document such options anywhere, but in any case a separate issue from this patch. And this. [...snip...] 5.) I tried the patch out on Linux and OS X; perhaps someone should give it a quick check on Windows as well -- I'm not sure if pathname manipulations like: last_slash = strrchr(pset.inputfile, '/'); work OK on Windows. Depends if canonicalize_path() has already been applied to that path. 6.) The indentation of these lines in tab-complete.c around line 2876 looks off: strcmp(prev_wd, \\i) == 0 || strcmp(prev_wd, \\include) == 0 || strcmp(prev_wd, \\ir) == 0 || strcmp(prev_wd, \\include_relative) == 0 || (I think the first of those lines was off before the patch, and the patch followed its example) pgindent likes to move things backward to make them fit within 80 columns. -- 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
[HACKERS] 9.1 support for hashing arrays
The algorithm for this was discussed in the original thread (http://archives.postgresql.org/pgsql-hackers/2010-10/msg02050.php) but I don't that think a satisfactory conclusion was really reached. In particular, it is way too easy to come up with pathological cases that defeat the hashing algorithm, for example: CREATE TABLE foo(a int[][]); INSERT INTO foo SELECT array_fill(i, ARRAY[8,8]) FROM generate_series(1,1) g(i); All 1 arrays are different, but they all have the same hash value (0), so if the query optimiser chooses to hash the arrays, the performance will be very poor. A few people on that thread (myself included - http://archives.postgresql.org/pgsql-hackers/2010-11/msg00123.php) suggested using the multiply-by-31 algorithm but I think I failed to properly make the case for it. Having given it some further thought, I think there are some very sound mathematical reasons why that algorithm performs well: The algorithm is to take the current hash total, multiply it by 31 and then add on the hash of the next element. The final result is a polynomial sum, where each element's hash value is multiplied by a different power of 31. Since this is all modulo 2^32 arithmetic, the powers of 31 will eventually start repeating, and at that point the hashing algorithm could be defeated by transpositions. However, the number 31 has the property that its powers don't repeat for a long time - the powers of 31 modulo 2^32 form a cyclic group with a multiplicative order of 2^27 (134217728). In other words 31^134217728 = 1 mod 2^32, and there are no smaller (strictly positive) powers of 31 for which this is the case. So the multiply-by-31 algorithm is only vulnerable to transpositions once the arrays reach 134217728 elements. For all smaller arrays, each array element's hash value is multiplied by a number different number from all the other elements, and since all the multipliers are odd numbers, *all* the individual bits from each element's hash value are distributed (differently) in the final value. Of course there are still going to be pathological cases, but they are very difficult to construct deliberately, and extremely unlikely to occur randomly. ISTM that this has all the properties of a good hashing algorithm (possibly the Java folks did a similar analysis and came to the same conclusion). Regards, Dean array-hashing.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] DOMAINs and CASTs
On Tue, May 17, 2011 at 12:19 PM, Robert Haas robertmh...@gmail.com wrote: The more controversial question is what to do if someone tries to create such a cast anyway. We could just ignore that as we do now, or we could throw a NOTICE, WARNING, or ERROR. IMHO, not being an error per se but an implementation limitation i would prefer to send a WARNING -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] cache estimates, cache access cost
On Sun, May 15, 2011 at 11:52 PM, Greg Smith g...@2ndquadrant.com wrote: Cédric Villemain wrote: http://git.postgresql.org/gitweb?p=users/c2main/postgres.git;a=shortlog;h=refs/heads/analyze_cache This rebases easily to make Cedric's changes move to the end; I just pushed a version with that change to https://github.com/greg2ndQuadrant/postgres/tree/analyze_cache if anyone wants a cleaner one to browse. I've attached a patch too if that's more your thing. Thank you. I don't much like sucking in other people's git repos - it tends to take a lot longer than just opening a patch file, and if I add the repo as a remote then my git repo ends up bloated. :-( The more important question is how to store the data collected and then use it for optimizing queries. Agreed, but unless I'm missing something, this patch does nothing about that. I think the first step needs to be to update all the formulas that are based on random_page_cost and seq_page_cost to properly take cache_page_cost into account - and in some cases it may be a bit debatable what the right mathematics are. For what it's worth, I don't believe for a minute that an analyze process that may run only run on a given table every six months has a chance of producing useful statistics about the likelihood that a table will be cached. The buffer cache can turn over completely in under a minute, and a minute is a lot less than a month. Now, if we measured this information periodically for a long period of time and averaged it, that might be a believable basis for setting an optimizer parameter. But I think we should take the approach recently discussed on performance: allow it to be manually set by the administrator on a per-relation basis, with some reasonable default (maybe based on the size of the relation relative to effective_cache_size) if the administrator doesn't intervene. I don't want to be excessively negative about the approach of examining the actual behavior of the system and using that to guide system behavior - indeed, I think there are quite a few places where we would do well to incorporate that approach to a greater degree than we do currently. But I think that it's going to take a lot of research, and a lot of work, and a lot of performance testing, to convince ourselves that we've come up with an appropriate feedback mechanism that will actually deliver better performance across a large variety of workloads. It would be much better, IMHO, to *first* get a cached_page_cost parameter added, even if the mechanism by which caching percentages are set is initially quite crude - that will give us a clear-cut benefit that people can begin enjoying immediately. -- 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] DOMAINs and CASTs
On Tue, May 17, 2011 at 12:29 AM, Jaime Casanova ja...@2ndquadrant.com wrote: On Sun, May 15, 2011 at 9:14 PM, Robert Haas robertmh...@gmail.com wrote: we should probably try to agree on which of the various options you mention makes most sense. well... my original patch only handle the simplest case, namely, try to make the cast that the user wants and if none is defined fall to the base types... anything else will complicate things as you shown... actually, things looks very simple until we start creating trees of domains... what options look sane to you? Well, clearly we should document. The more controversial question is what to do if someone tries to create such a cast anyway. We could just ignore that as we do now, or we could throw a NOTICE, WARNING, or ERROR. A NOTICE or WARNING has the disadvantage that the client might ignore it, and the user be unaware. An ERROR has the disadvantage that a dump-and-reload from an earlier version of PostgreSQL might fail - which also means that pg_upgrade will fail - after the point at which it's disabled the old cluster. I'm not sure how seriously to take that risk, but it's something to think about. -- 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
[HACKERS] deprecating contrib for PGXN
I have missed it if this was discussed before but ... Would now be a good time to start deprecating the contrib/ directory as a way to distribute Pg add-ons, with favor given to PGXN and the like instead? It would make sense to leave contrib/ alone for 9.1, but I believe that it should start slimming down as we move towards 9.2, with any content that can easily be migrated to PGXN/etc being taken out of contrib/ . Or, the policy would be to stop adding new things to contrib/ except in the odd case where that is surely the best place to put it, so only the legacy things are there, and for the legacy things, they are removed case-by-case as workable distributions for them first appear on PGXN/etc. An analogy for policy here would be Perl 5 and what Perl modules it bundles. The Perl modules that have the most business being bundled with Perl are those minimal ones whose function is to go out to CPAN and install other modules. Another analogy would be Parrot and languages implemented over it. Originally, various language compilers were bundled with Parrot, and they gradually migrated to their own distributions, Rakudo for example. If this general policy of deprecating contrib/ is agreed on, then at the very least the documentation shipped with 9.1 should mention it being deprecated and talk about migration strategies. Or 9.1 could include a CPAN-like program that makes it easier to install PGXN extensions, if that is applicable, so there is an overlap period where people could get the legacy add-ons either way. -- Darren Duncan -- 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] adding a new column in IDENTIFY_SYSTEM
On Mon, May 16, 2011 at 2:35 AM, Magnus Hagander mag...@hagander.net wrote: On Mon, May 16, 2011 at 01:03, Jaime Casanova ja...@2ndquadrant.com wrote: On Thu, May 5, 2011 at 10:59 AM, Tom Lane t...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: So even if people don't believe in the rationale behind the patch, would allowing it harm anything at this point? Adding it for the sake of upgrades seems very far fetched. Adding it for the sake of giving a better error message seems like a very good idea. But in that case, the client side code to actually give a better error message should be included from the start, IMHO. What's not apparent to me is how we'll even get to this check; if there's a mismatch, won't the database system identifier comparison fail first in most scenarios? that's why i didn't propose that to begin with... but thinking on that, we can use it to add a message in pg_basebackup, maybe just a warning if we are taking a basebackup from an incompatible system... but for that i will need to add xlog_internal.h and postgres.h to pg_basebackup and use the #define FRONTEND 1 hack we have in pg_resetxlog Well, pg_basebackup doesn't need it critically, since it never looks at the contents fo the files anyway. You could use a pg_basebackup for 9.1 to backup a 9.2 database - at least in theory. Granted, it wouldn't hurt to get the message from pg_basebackup *before* you took the backup, while you could, is also possible that you really think is the right version and that you will waste time until you found out you have the wrong version installed and that your backup won't work I think it'd be less of a kludge to move the definition of XLOG_PAGE_MAGIC somewhere that's visible already. agree, that also will allow us to avoid have that kludge in pg_resetxlog... Also, this error message: + fprintf(stderr, _(%s: could not identify system: XLOG pages are incompatible.\n), is clearly wrong - it *could* identify the system, it just didn't like what it saw... ah! yeah! we can, of course, put better messages! Anyway, the more useful point would be to have it in walreceiver, I believe. you mean a message like this in walreceiver? we can put it but probably it will never get to that... I'm also wondering why send WAL version number and not, say, catalog version number, if there's some idea that we need more tests than the system identifier comparison. well... catversion is not that informative, we change it for a lot of reasons, not only catalog estructure changes... so we can't swear that xlog records will be incompatible just because catversion changes... From the *replication* perspective we can be pretty certain it breaks. From the base backup perspective, it might well keep on working, since you get the new version of both the base backup and the logs. And what other reasons than catalog structure changes do we actually change catversion? see these commits: 76dd09bbec893c02376e3440a6a86a3b994d804c f5e524d92be609c709825be8995bf77f10880c3b 47082fa875179ae629edb26807ab3f38a775280b -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- 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] deprecating contrib for PGXN
On 05/17/2011 01:31 PM, Darren Duncan wrote: I have missed it if this was discussed before but ... Would now be a good time to start deprecating the contrib/ directory as a way to distribute Pg add-ons, with favor given to PGXN and the like instead? If PGXN moves into .Org infrastructure (which I believe is currently the plan) then yes, contrib should go away. JD -- Command Prompt, Inc. - http://www.commandprompt.com/ PostgreSQL Support, Training, Professional Services and Development The PostgreSQL Conference - http://www.postgresqlconference.org/ @cmdpromptinc - @postgresconf - 509-416-6579 -- 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] deprecating contrib for PGXN
On Tue, May 17, 2011 at 9:45 PM, Joshua D. Drake j...@commandprompt.com wrote: On 05/17/2011 01:31 PM, Darren Duncan wrote: I have missed it if this was discussed before but ... Would now be a good time to start deprecating the contrib/ directory as a way to distribute Pg add-ons, with favor given to PGXN and the like instead? If PGXN moves into .Org infrastructure (which I believe is currently the plan) then yes, contrib should go away. It'll need to be made to work properly on Windows first, which means solving issues around the lack of a compiler on 99.9% of Windows boxes, and consequently, how a binary distribution would work with PostgreSQL builds that may differ from machine to machine in important ways (think integer datetimes for example). -- 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
Re: [HACKERS] cache estimates, cache access cost
2011/5/17 Robert Haas robertmh...@gmail.com: On Sun, May 15, 2011 at 11:52 PM, Greg Smith g...@2ndquadrant.com wrote: Cédric Villemain wrote: http://git.postgresql.org/gitweb?p=users/c2main/postgres.git;a=shortlog;h=refs/heads/analyze_cache This rebases easily to make Cedric's changes move to the end; I just pushed a version with that change to https://github.com/greg2ndQuadrant/postgres/tree/analyze_cache if anyone wants a cleaner one to browse. I've attached a patch too if that's more your thing. Thank you. I don't much like sucking in other people's git repos - it tends to take a lot longer than just opening a patch file, and if I add the repo as a remote then my git repo ends up bloated. :-( The more important question is how to store the data collected and then use it for optimizing queries. Agreed, but unless I'm missing something, this patch does nothing about that. I think the first step needs to be to update all the formulas that are based on random_page_cost and seq_page_cost to properly take cache_page_cost into account - and in some cases it may be a bit debatable what the right mathematics are. Yes, I provide the branch only in case someone want to hack the costsize and to close the problem of getting stats. For what it's worth, I don't believe for a minute that an analyze process that may run only run on a given table every six months has a chance of producing useful statistics about the likelihood that a table will be cached. The buffer cache can turn over completely in under a minute, and a minute is a lot less than a month. Now, if we measured this information periodically for a long period of time and averaged it, that might be a believable basis for setting an optimizer The point is to get ratio in cache, not the distribution of the data in cache (pgfincore also allows you to see this information). I don't see how a stable (a server in production) system can have its ratio moving up and down so fast without known pattern. Maybe it is datawarehouse, so data move a lot, then just update your per-relation stats before starting your queries as suggested in other threads. Maybe it is just a matter of frequency of stats update or explicit request like we *use to do* (ANALYZE foo;) to handle those situations. parameter. But I think we should take the approach recently discussed on performance: allow it to be manually set by the administrator on a per-relation basis, with some reasonable default (maybe based on the size of the relation relative to effective_cache_size) if the administrator doesn't intervene. I don't want to be excessively negative about the approach of examining the actual behavior of the system and using that to guide system behavior - indeed, I think there are quite a few places where we would do well to incorporate that approach to a greater degree than we do currently. But I think that it's going to take a lot of research, and a lot of work, and a lot of performance testing, to convince ourselves that we've come up with an appropriate feedback mechanism that will actually deliver better performance across a large variety of workloads. It would be much better, IMHO, to *first* get a cached_page_cost parameter added, even if the mechanism by which caching percentages are set is initially quite crude - that will give us a clear-cut benefit that people can begin enjoying immediately. The plugin I provided is just to be able to do first analysis on how the os cache size move. You can either use pgfincore to monitor that per table or use the patch and monitor columns values for *cache. I took the Hooks approach because it allows to do what you want :) You can set up a hook where you set the values you want to see, it allows for example to fix cold start values, or permanent values set by DBA or ... do what you want here. The topic is do we need more parameters to increase the value of our planner ? 1/ cache_page_cost 2/ cache information, arbitrary set or not. Starting with 1/ is ok for me, I prefer to try both at once if possible to remove the pain to hack twice costsize.c Several items are to be discussed after that: formulas to handle 'small' tables, data distribution usage (this one hit an old topic about auto-partitionning as we are here), cold state, hot state, ... PS: there is very good blocker for the pg_class changes : what happens in a standby ? Maybe it just opens the door on how to unlock that or find another option to get the information per table but distinct per server. (or we don't care, at least for a first implementation, like for other parameters) -- Cédric Villemain 2ndQuadrant http://2ndQuadrant.fr/ PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] patch: Allow \dd to show constraint comments
Hi all,
Attached is a simple patch addressing the TODO item Allow \dd to show
constraint comments. If you have comments on various constraints
(column, foreign key, primary key, unique, exclusion), they should
show up via \dd now.
Some example SQL is attached to create two tables with a variety of
constraints and constraint comments. With the patch, \dd should then
produce something like this:
Object descriptions
Schema | Name | Object | Description
+--++--
public | bar_c_excl | constraint | exclusion constraint comment
public | bar_pkey | constraint | two column pkey comment
public | bar_uname_check | constraint | constraint for bar
public | bar_uname_fkey | constraint | fkey comment
public | uname_check_not_null | constraint | not null comment
public | uname_cons | constraint | sanity check for uname
public | uname_uniq_cons | constraint | unique constraint comment
(7 rows)
whereas without the patch, you should see nothing.
Josh
diff --git a/doc/src/sgml/ref/psql-ref.sgml b/doc/src/sgml/ref/psql-ref.sgml
index ac351d3..20dfd1d 100644
*** a/doc/src/sgml/ref/psql-ref.sgml
--- b/doc/src/sgml/ref/psql-ref.sgml
*** testdb=gt;
*** 991,997
objects.
quoteObject/quote covers aggregates, functions, operators,
types, relations (tables, views, indexes, sequences), large
! objects, rules, and triggers. For example:
programlisting
=gt; userinput\dd version/userinput
Object descriptions
--- 991,997
objects.
quoteObject/quote covers aggregates, functions, operators,
types, relations (tables, views, indexes, sequences), large
! objects, rules, triggers, and constraints. For example:
programlisting
=gt; userinput\dd version/userinput
Object descriptions
diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c
index e01fb7b..6da97e7 100644
*** a/src/bin/psql/describe.c
--- b/src/bin/psql/describe.c
*** objectDescription(const char *pattern, b
*** 998,1003
--- 998,1026
n.nspname, t.tgname, NULL,
pg_catalog.pg_table_is_visible(c.oid));
+ /* Constraint descriptions */
+ appendPQExpBuffer(buf,
+ UNION ALL\n
+ SELECT pgc.oid as oid, pgc.tableoid AS tableoid,\n
+ n.nspname as nspname,\n
+ CAST(pgc.conname AS pg_catalog.text) as name,
+ CAST('%s' AS pg_catalog.text) as object\n
+ FROM pg_catalog.pg_constraint pgc\n
+ JOIN pg_catalog.pg_class c
+ ON c.oid = pgc.conrelid\n
+ LEFT JOIN pg_catalog.pg_namespace n
+ ON n.oid = c.relnamespace\n,
+ gettext_noop(constraint));
+
+ if (!showSystem !pattern)
+ appendPQExpBuffer(buf, WHERE n.nspname 'pg_catalog'\n
+ AND n.nspname 'information_schema'\n);
+
+ /* XXX not sure what to do about visibility rule here? */
+ processSQLNamePattern(pset.db, buf, pattern, !showSystem !pattern, false,
+ n.nspname, pgc.conname, NULL,
+ pg_catalog.pg_table_is_visible(c.oid));
+
appendPQExpBuffer(buf,
) AS tt\n
JOIN pg_catalog.pg_description d ON (tt.oid = d.objoid AND tt.tableoid = d.classoid AND d.objsubid = 0)\n);
CREATE TABLE mytable (
uname text PRIMARY KEY,
CONSTRAINT uname_cons CHECK ((uname 'badname'::text))
);
COMMENT ON CONSTRAINT uname_cons ON mytable IS 'sanity check for uname';
CREATE TABLE bar (
uname text NOT NULL,
another_uname text NOT NULL,
c circle
CONSTRAINT bar_uname_check CHECK ((uname 'invalid'::text)),
CONSTRAINT uname_check_not_null CHECK ((uname IS NOT NULL)),
CONSTRAINT bar_pkey PRIMARY KEY (uname, another_uname),
CONSTRAINT uname_uniq_cons UNIQUE (uname),
CONSTRAINT bar_uname_fkey FOREIGN KEY (uname) REFERENCES mytable(uname),
EXCLUDE USING gist (c WITH )
);
COMMENT ON CONSTRAINT bar_uname_check ON bar IS 'constraint for bar';
COMMENT ON CONSTRAINT uname_check_not_null ON bar IS 'not null comment';
COMMENT ON CONSTRAINT bar_pkey ON bar IS 'two column pkey comment';
COMMENT ON CONSTRAINT uname_uniq_cons ON bar IS 'unique constraint comment';
COMMENT ON CONSTRAINT bar_uname_fkey ON bar IS 'fkey comment';
COMMENT ON CONSTRAINT bar_c_excl ON bar IS 'exclusion constraint comment';
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] LOCK DATABASE
One of the things that came out of the clustering session is a need for a LOCK DATABASE command. Primarily to be able to drop databases across nodes in a cluster, but later chats lead to ideas about upgrading databases' schemas and such operations that need to ensure that no one else is accessing the database. Some guys proposed that this could be implemented by changing some pg_hba.conf rules on the fly, but to me that seems a really ugly hack and not a real solution. (You could equally propose that all CONNECT privileges to a database should be granted via some role specifically dedicated to this task, and that this role would be revoked permission when you want to lock out connections. This seems really ugly as well.) Since DROP DATABASE requires to be called outside of a transaction, it is necessary to acquire a session-level lock, which wouldn't be released at the end of the locking transaction. The problem with this idea was that it'd need an UNLOCK DATABASE command to go with it -- which sucks and I didn't want to add to this proposal, but Andres didn't want to hear about that. So we would have a new command LOCK DATABASE [FOR SESSION] or something like that; the pooler software would call that and then kill other existing application connections (using pg_terminate_backend() perhaps), then drop the database. This LOCK DATABASE thingy would just be a simple function on top of LockSharedObject. Since establishing a new connection requires grabbing a lock on the database via LockSharedObject, things would Just Work (or at least so it seems to me). UNLOCK DATABASE would be needed to release a session-level lock acquired by LOCK DATABASE FOR SESSION for the cases where you want to lock a database to safely do schema upgrades and the like. (I was thinking that we already need a simple LockDatabase wrapper on top of LockSharedObject, but that's really a nice and small cleanup of existing code and not a new feature.) Thoughts? -- Álvaro Herrera alvhe...@alvh.no-ip.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] deprecating contrib for PGXN
On Tue, May 17, 2011 at 4:45 PM, Joshua D. Drake j...@commandprompt.com wrote: On 05/17/2011 01:31 PM, Darren Duncan wrote: I have missed it if this was discussed before but ... Would now be a good time to start deprecating the contrib/ directory as a way to distribute Pg add-ons, with favor given to PGXN and the like instead? If PGXN moves into .Org infrastructure (which I believe is currently the plan) then yes, contrib should go away. What is the benefit of getting rid of it? -- 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] deprecating contrib for PGXN
Robert Haas wrote: On Tue, May 17, 2011 at 4:45 PM, Joshua D. Drake j...@commandprompt.com wrote: On 05/17/2011 01:31 PM, Darren Duncan wrote: I have missed it if this was discussed before but ... Would now be a good time to start deprecating the contrib/ directory as a way to distribute Pg add-ons, with favor given to PGXN and the like instead? If PGXN moves into .Org infrastructure (which I believe is currently the plan) then yes, contrib should go away. What is the benefit of getting rid of it? Maybe something could be clarified for me first. Are the individual projects in contrib/ also distributed separately from Pg, on their own release schedules, so users can choose to upgrade them independently of upgrading Pg itself, or so their developers can have a lot of flexibility to make major changes without having to follow the same stability or deprecation timetables of Pg itself? If the only way to get a contrib/ project is bundled with Pg, then the project developers and users don't get the flexibility that they otherwise would have. That's the main answer, I think. -- Darren Duncan -- 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] deprecating contrib for PGXN
On Tue, 2011-05-17 at 13:45 -0700, Joshua D. Drake wrote: If PGXN moves into .Org infrastructure (which I believe is currently the plan) then yes, contrib should go away. Well, it is not an enough reason to kick contrib off. I am not aware that PGXN is a community driven project, and not aware that it has the same standards that contrib/ has. PGXN cannot replace contrib. It can only be an add-on to contrib. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] deprecating contrib for PGXN
On Tue, 2011-05-17 at 20:37 -0700, Darren Duncan wrote: Are the individual projects in contrib/ also distributed separately from Pg, on their own release schedules, No. If the only way to get a contrib/ project is bundled with Pg, then the project developers and users don't get the flexibility that they otherwise would have. These types of stuff goes under pgfoundry, for now. -- Devrim GÜNDÜZ Principal Systems Engineer @ EnterpriseDB: http://www.enterprisedb.com PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [HACKERS] LOCK DATABASE
On Tue, May 17, 2011 at 10:21 PM, Alvaro Herrera alvhe...@alvh.no-ip.org wrote: So we would have a new command LOCK DATABASE [FOR SESSION] or something like that; the pooler software would call that and then kill other existing application connections (using pg_terminate_backend() perhaps), then drop the database. This LOCK DATABASE thingy would just be a simple function on top of LockSharedObject. Since establishing a new connection requires grabbing a lock on the database via LockSharedObject, things would Just Work (or at least so it seems to me). UNLOCK DATABASE would be needed to release a session-level lock acquired by LOCK DATABASE FOR SESSION for the cases where you want to lock a database to safely do schema upgrades and the like. So we the lock will be released at end of the session or when the UNLOCK DATABASE command is invoked, right? A question: why will we beign so rude by killing other sessions instead of avoid new connections and wait until the current sessions disconnect? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitación de PostgreSQL -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Passing an array or record to a stored procedure in PostgreSQL
Hi Highly Respected Hackers! I have a task to pass arrays, records and in some cases array of records as a parameter to the stored procedures in PostgreSQL. I will use JDBC to work with PostgreSQL 9.0 At first I would like to learn how to pass arrays. Any ideas? p.s. Google and http://dba.stackexchange.com/ gave me no answer... Thank you in advance! Max -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
