Re: [HACKERS] ERROR: GIN indexes do not support whole-index scans
Ah - you mentioning index definitions has suddenly made it clearer just what that error message might mean. The source_lang_code and target_lang_code columns didn't yet each have an index. If I create an index for either one of them, the error then goes away, I'm guessing because the query processor can use one index or other to filter table rows before applying the full-text filter, rather than applying the full-text filter first then applying the ='code' filters to the results - which must be what the error means you can't do. Strange, though - if I change the ='code' terms to use LIKE, it works ... so it obviously can be done without adding another index. Still, those two columns both needed an index anyway, and everything then works just fine, so I shan't worry about that :) Thank you very much. Kevin. -Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 20 May 2010 22:15 To: Kevin Flanagan Cc: pgsql-hackers@postgresql.org Subject: Re: [HACKERS] ERROR: GIN indexes do not support whole-index scans Kevin Flanagan kevi...@linkprior.com writes: Why would adding target_lang_code='en' cause this error? Hard to tell without seeing the index definitions for this table. Also could we see the EXPLAIN plans for both queries? (If possible ... I'm not sure whether you'd get this error just from EXPLAINing the problem query.) Environment: PostgreSQL 8.4 on Windows (installed with one-click installer), 8.4.what-exactly? 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] Row-level Locks SERIALIZABLE transactions, postgres vs. Oracle
On May 21, 2010, at 4:20 , Florian Pflug wrote: On May 19, 2010, at 2:15 , Florian Pflug wrote: On May 17, 2010, at 3:30 , Robert Haas wrote: On Sun, May 16, 2010 at 9:07 PM, Florian Pflug f...@phlo.org wrote: On May 14, 2010, at 22:54 , Robert Haas wrote: On Thu, May 13, 2010 at 5:39 PM, Tom Lane t...@sss.pgh.pa.us wrote: Florian Pflug f...@phlo.org writes: All in all, I believe that SHARE and UPDATE row-level locks should be changed to cause concurrent UPDATEs to fail with a serialization error. I don't see an argument for doing that for FOR SHARE locks, and it already happens for FOR UPDATE (at least if the row actually gets updated). AFAICS this proposal mainly breaks things, in pursuit of an unnecessary and probably-impossible-anyway goal of making FK locking work with only user-level snapshots. After giving this considerable thought and testing the behavior at some length, I think the OP has it right. One thing I sometimes need to do is denormalize a copy of a field, e.g. snipped example I've whipped up a quick and still rather dirty patch that implements the behavior I proposed, at least for the case of conflicts between FOR UPDATE locks and updates. With the patch, any attempt to UPDATE or FOR UPDATE lock a row that has concurrently been FOR UPDATE locked will cause a serialization error. (The same for an actually updated row of course, but that happened before too). While this part of the patch was fairly straight forward, make FOR SHARE conflict too seems to be much harder. The assumption that a lock becomes irrelevant after the transaction(s) that held it completely is built deeply into the multi xact machinery that powers SHARE locks. That machinery therefore assumes that once all members of a multi xact have completed the multi xact is dead also. But my proposal depends on a SERIALIZABLE transaction being able to find if any of the lockers of a row are invisible under it's snapshot - for which it'd need any multi xact containing invisible xids to outlive its snapshot. Thanks for putting this together. I suggest adding it to the open CommitFest - even if we decide to go forward with this, I don't imagine anyone is going to be excited about changing it during beta. https://commitfest.postgresql.org/action/commitfest_view/open Will do. Thanks for the link. Here is an updated version that works for SHARE locks too. Forgetting to run make check before sending a patch is bad, as I just proved :-( For the archives' and the commitfest app's sake, here is a version that actually passes the regression tests. To make up for it, I also did some testing with a custom pgbench script schema and proved the effectiveness of this patch. I ran this with pgbench -s 10 -j 10 -c 10 -t 1000 -n -f fkbench.pgbench on both HEAD and HEAD+patch. The former errors out quickly with database inconsistent while the later completes the pgbench run without errors. The patch still needs more work, at least on the comments documentation side of things, but I'm going to let this rest now while we're in beta. Patch, pgbench script and schema attached. Great, now my mail client decided to send encode those attachments with MacBinary instead of sending them as plain text :-( Not sure if MUAs other than Mail.app can open those, so I'm resending this. Really sorry for the noise, guys best regards, Florian Pflug serializable_lock_consistency.patch Description: Binary data fkbench.init.sql Description: Binary data fkbench.pgbench 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
[HACKERS] Specification for Trusted PLs?
Folks, I feel dumb. I have been looking for a document which specifies what trusted and untrusted PLs must do and forbid, so far without result. Where do we document this, and if we don't where *should* we document this? Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Specification for Trusted PLs?
* David Fetter (da...@fetter.org) wrote: I have been looking for a document which specifies what trusted and untrusted PLs must do and forbid, so far without result. I think you might have been missing the tree for the forest in this case.. :) I'm sure you've seen this, but perhaps you weren't thinking about how broad it really is: http://www.postgresql.org/docs/9.0/static/sql-createlanguage.html TRUSTED TRUSTED specifies that the language is safe, that is, it does not offer an unprivileged user any functionality to bypass access restrictions. If this key word is omitted when registering the language, only users with the PostgreSQL superuser privilege can use this language to create new functions. That's about it- a language is TRUSTED if there's no way for a user to be able to write a function which will give them access to things they're not supposed to have. Practically, this includes things like any kind of direct I/O (files, network, etc). Where do we document this, and if we don't where *should* we document this? I'd be hesitant about trying to document exactly what a PL must do to be trusted at a more granular level than what's above- mostly because, if we change some functionality, we would end up having to document that change in the place which is appropriate for it and then also in the list of things trusted PLs shouldn't do/allow. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Specification for Trusted PLs?
That's about it- a language is TRUSTED if there's no way for a user to be able to write a function which will give them access to things they're not supposed to have. Practically, this includes things like any kind of direct I/O (files, network, etc). The fact that plpythonu used to be plpython back in 7.3 serves to illustrate that the distinction is not all that well defined. I guess that someone made an executive decision that the python restricted execution environment wasn't restricted enough. Regards, 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] Specification for Trusted PLs?
Peter Geoghegan peter.geoghega...@gmail.com writes: That's about it- a language is TRUSTED if there's no way for a user to be able to write a function which will give them access to things they're not supposed to have. Practically, this includes things like any kind of direct I/O (files, network, etc). The fact that plpythonu used to be plpython back in 7.3 serves to illustrate that the distinction is not all that well defined. I guess that someone made an executive decision that the python restricted execution environment wasn't restricted enough. Well, it was the upstream authors of python's restricted execution environment who decided it was unfixably insecure, not us. So the trusted version had to go away. (For awhile there last month, it was looking like plperl was going to suffer the same fate :-(. Fortunately Tim Bunce thought of a way to not have to rely on Safe.pm anymore.) 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] Snapshot Materialized Views - GSoC
2010/5/20 Pavel baro...@seznam.cz: For this summer I have plan to make patch inplementing snapshot materialized views (MV). I believe it will not be end of effort to implement more of MV. But I / we need discuss MV syntax and exact behaviour so I have some questions about that for all of you: a) relkind for materialized view in pg_class? - I'm voting for char 'm' quite obvious why, but not sure about alias: 1 - RELKIND_MVIEW 2 - RELKIND_MATVIEW or any other ideas? I think the prior question is whether we need to create a new relkind at all. I'm prepared to believe that the answer is yes, but I'd like to see a clear justification of why we can't use either 'v' or 'r'. It seems to me that a materialized view is a lot like a regular old table with a special rewrite rule attached to it somewhere. b) create MV syntax? - CREATE MATERIALIZED VIEW mvname AS ..., I think it is quite obvious to do so, but I had to ask I think that's OK. c) refresh command syntax? 1 - ALTER MATERIALIZED VIEW mvname REFRESH or 2 - REFRESH MATERIALIZED VIEW mvname 1. d) what to do when someone use INSERT, UPDATE or DELETE against MV? 1 - raise error? - I prefer this option 2 - let commands change MV? (no chance to let changes propagate to source tables, not for this summer :) if pg lets user to DML against MV, I expect that triggers should work too 1. e) what to do when someone drop table or column? - it behave like it was a classic view. Fire error and hint - CASCADE option will remove MV Agree. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Snapshot Materialized Views - GSoC
Pavel, b) create MV syntax? - CREATE MATERIALIZED VIEW mvname AS ..., I think it is quite obvious to do so, but I had to ask please do not fortget the: create or replace MATERIALIZED VIEW option. And also the DROP if exists for the drop-command Best wishes Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - Using PostgreSQL is mostly about sleeping well at night.
Re: [HACKERS] Snapshot Materialized Views - GSoC
Massa, Harald Armin c...@ghum.de writes: please do not fortget the: create or replace MATERIALIZED VIEW option. Please do. For something as complex as a table or view, CREATE OR REPLACE is a lot more complicated than it is for simple objects like functions. (See flamewar just a couple weeks ago about C.O.R. vs CREATE IF NOT EXISTS for tables.) Putting this on the to-do list for the GSOC project will just about guarantee failure. It's most likely too large a task for a GSOC project already... 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] changed source files.
Hello: How does one find out what source files were modified between two releases? For example, what .c and .h changed between 8.4.3 and 8.4.4? The Notes do not mention specific source files. Thanks, MMK.
Re: [HACKERS] changed source files.
MMK, * MMK (bom...@yahoo.com) wrote: How does one find out what source files were modified between two releases? For example, what .c and .h changed between 8.4.3 and 8.4.4? The Notes do not mention specific source files. You can pull down the CVS tags for each and then run a recursive diff.. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] unnailing shared relations (was Re: global temporary tables)
On Sat, Apr 24, 2010 at 6:53 PM, Robert Haas robertmh...@gmail.com wrote (in reply to Tom Lane): If we create, e.g. pg_shared_class and pg_shared_attribute, then we can un-nail the catalogs you just nailed to make the authentication process able to work without selecting a database. Actually, there's another way we could do this. Instead of creating pg_shared_class and pg_shared_attribute and moving all of the catalog entries for the shared relations into those tables, we could consider leaving the catalog entries in the unshared copies of pg_class, pg_attribute, etc. and DUPLICATING them in a shared catalog which would only be used prior to selecting a database. Once we selected a database we'd switch to using the database-specific pg_class et al. Obviously that's a little grotty but it might (?) be easier, and possibly a step along the way. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Snapshot Materialized Views - GSoC
On May 21, 2010, at 15:59 , Robert Haas wrote: 2010/5/20 Pavel baro...@seznam.cz: For this summer I have plan to make patch inplementing snapshot materialized views (MV). I believe it will not be end of effort to implement more of MV. But I / we need discuss MV syntax and exact behaviour so I have some questions about that for all of you: a) relkind for materialized view in pg_class? - I'm voting for char 'm' quite obvious why, but not sure about alias: 1 - RELKIND_MVIEW 2 - RELKIND_MATVIEW or any other ideas? I think the prior question is whether we need to create a new relkind at all. I'm prepared to believe that the answer is yes, but I'd like to see a clear justification of why we can't use either 'v' or 'r'. It seems to me that a materialized view is a lot like a regular old table with a special rewrite rule attached to it somewhere. I guess the justification is that with the same argument you could argue that a view should have relkind 'r', since it's just an empty table with a rewrite rule attached. I think relkind is mostly there to make pg_dump's and the information schema's job easier - without it, distinguishing tables with ON SELECT rules from views seem rather AI-complete. The same holds for materialized views vs. tables and materialized views vs. views. best regards, Florian Pflug -- 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] unnailing shared relations (was Re: global temporary tables)
2010/5/21 Robert Haas robertmh...@gmail.com: On Sat, Apr 24, 2010 at 6:53 PM, Robert Haas robertmh...@gmail.com wrote (in reply to Tom Lane): If we create, e.g. pg_shared_class and pg_shared_attribute, then we can un-nail the catalogs you just nailed to make the authentication process able to work without selecting a database. Actually, there's another way we could do this. Instead of creating pg_shared_class and pg_shared_attribute and moving all of the catalog entries for the shared relations into those tables, we could consider leaving the catalog entries in the unshared copies of pg_class, pg_attribute, etc. and DUPLICATING them in a shared catalog which would only be used prior to selecting a database. Once we selected a database we'd switch to using the database-specific pg_class et al. Obviously that's a little grotty but it might (?) be easier, and possibly a step along the way. I did it - just on syscache level - but there are problem with refresh. I though about some special pseudo persistent data pages attached to possible any table with temp data. Then you don't need modify any on higher level, you don't need new catalog entries, etc .. Regards Pavel Stehule -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres Company -- 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] Specification for Trusted PLs?
So, here's a working definition: 1) cannot directly read or write files on the server. 2) cannot bind network ports 3) uses only the SPI interface to interact with postgresql tables etc. 4) does any logging only using elog to the postgres log Questions: a) it seems like there should be some kind of restriction on access to memory, but I'm not clear on how that would be defined. b) where are we with the whole trusted module thing? Like for CPAN modules etc. -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 11:55 AM, Josh Berkus j...@agliodbs.com wrote: So, here's a working definition: 1) cannot directly read or write files on the server. 2) cannot bind network ports To make that more covering, don't yu really need something like cannot communicate with outside processes? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Specification for Trusted PLs?
On 05/21/2010 11:57 AM, Magnus Hagander wrote: On Fri, May 21, 2010 at 11:55 AM, Josh Berkusj...@agliodbs.com wrote: So, here's a working definition: 1) cannot directly read or write files on the server. 2) cannot bind network ports To make that more covering, don't yu really need something like cannot communicate with outside processes? So, no interprocess communication except through the SPI interface? How do module GUCs and things like %_SHARED fit into this? -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 11:57:33AM -0400, Magnus Hagander wrote: On Fri, May 21, 2010 at 11:55 AM, Josh Berkus j...@agliodbs.com wrote: So, here's a working definition: 1) cannot directly read or write files on the server. 2) cannot bind network ports To make that more covering, don't yu really need something like cannot communicate with outside processes? These need to be testable conditions, and new tests need to get added any time we find that we've missed something. Making this concept fuzzier is exactly the wrong direction to go. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Specification for Trusted PLs?
* David Fetter (da...@fetter.org) wrote: These need to be testable conditions, and new tests need to get added any time we find that we've missed something. Making this concept fuzzier is exactly the wrong direction to go. I'm really not sure that we want to be in the business of writing a ton of regression tests to see if languages which claim to be trusted really are.. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Specification for Trusted PLs?
On Fri, May 21, 2010 at 12:22 PM, David Fetter da...@fetter.org wrote: On Fri, May 21, 2010 at 11:57:33AM -0400, Magnus Hagander wrote: On Fri, May 21, 2010 at 11:55 AM, Josh Berkus j...@agliodbs.com wrote: So, here's a working definition: 1) cannot directly read or write files on the server. 2) cannot bind network ports To make that more covering, don't yu really need something like cannot communicate with outside processes? These need to be testable conditions, and new tests need to get added any time we find that we've missed something. Making this concept fuzzier is exactly the wrong direction to go. Well, the best way to define what a trusted language can do is to define a *whitelist* of what it can do, not a blacklist of what it can't do. That's the only way to get a complete definition. It's then up to the implementation step to figure out how to represent that in the form of tests. -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 12:26:24PM -0400, Stephen Frost wrote: * David Fetter (da...@fetter.org) wrote: These need to be testable conditions, and new tests need to get added any time we find that we've missed something. Making this concept fuzzier is exactly the wrong direction to go. I'm really not sure that we want to be in the business of writing a ton of regression tests to see if languages which claim to be trusted really are.. That is *precisely* the business we need to be in, at least for the languages we ship, and it would behoove us to test languages we don't ship so we can warn people when they don't pass. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Specification for Trusted PLs?
* David Fetter (da...@fetter.org) wrote: That is *precisely* the business we need to be in, at least for the languages we ship, and it would behoove us to test languages we don't ship so we can warn people when they don't pass. k, let's start with something simpler first tho- I'm sure we can pull in the glibc regression tests and run them too. You know, just in case there's a bug there, somewhere. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Specification for Trusted PLs?
On Fri, May 21, 2010 at 01:45:45PM -0400, Stephen Frost wrote: * David Fetter (da...@fetter.org) wrote: That is *precisely* the business we need to be in, at least for the languages we ship, and it would behoove us to test languages we don't ship so we can warn people when they don't pass. k, let's start with something simpler first tho- I'm sure we can pull in the glibc regression tests and run them too. You know, just in case there's a bug there, somewhere. That's pretty pure straw man argument. I expect much higher quality trolling. D-. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Specification for Trusted PLs?
On May 21, 2010, at 18:26 , Stephen Frost wrote: * David Fetter (da...@fetter.org) wrote: These need to be testable conditions, and new tests need to get added any time we find that we've missed something. Making this concept fuzzier is exactly the wrong direction to go. I'm really not sure that we want to be in the business of writing a ton of regression tests to see if languages which claim to be trusted really are.. Well, testing software security via regression tests certainly is sounds intriguing. But unfortunately, it's impossible also AFAICS - it'd amount to testing for the *absence* of features, which seems hard... I suggest the following definition of trusted PL. While potentially preventing excruciating pain, saving tons of sweat and allowing code reuse, actually adds nothing in terms of features over pl/pgsql. best regards, Florian Pflug -- 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] Specification for Trusted PLs?
* David Fetter (da...@fetter.org) wrote: On Fri, May 21, 2010 at 01:45:45PM -0400, Stephen Frost wrote: k, let's start with something simpler first tho- I'm sure we can pull in the glibc regression tests and run them too. You know, just in case there's a bug there, somewhere. That's pretty pure straw man argument. I expect much higher quality trolling. D-. Sorry, but seriously, at some point we have to expect that the tools we use will behave according to their claims and their documentation, at least until proven otherwise. I don't like that it means we may end up having to issue CVE's when there are issues in things we use, but I don't think that means we shouldn't use other libraries or we should spend alot of time working on validating those tools. Presumably, they have communities who do that. As an example, consider the zlib issue that happened not too long ago and the subsequent many CVE's that came of it. We could have reviewed zlib better and possibly found that bug, but I don't know that it would be the best use of our rather limited resources. Additionally, trying to go into other code bases like that to do that kind of detailed review would necessairly be much more difficult for those who are not familiar with it. etc, etc... Stephen signature.asc Description: Digital signature
Re: [HACKERS] Specification for Trusted PLs?
David Fetter da...@fetter.org writes: On Fri, May 21, 2010 at 12:26:24PM -0400, Stephen Frost wrote: I'm really not sure that we want to be in the business of writing a ton of regression tests to see if languages which claim to be trusted really are.. That is *precisely* the business we need to be in, at least for the languages we ship, and it would behoove us to test languages we don't ship so we can warn people when they don't pass. I can't see us writing an AI-complete set of tests for each language we ship, let alone ones we don't. Testing can prove the presence of bugs, not their absence --- and that applies in spades to security holes. 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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 1:58 PM, David Fetter da...@fetter.org wrote: On Fri, May 21, 2010 at 01:45:45PM -0400, Stephen Frost wrote: * David Fetter (da...@fetter.org) wrote: That is *precisely* the business we need to be in, at least for the languages we ship, and it would behoove us to test languages we don't ship so we can warn people when they don't pass. k, let's start with something simpler first tho- I'm sure we can pull in the glibc regression tests and run them too. You know, just in case there's a bug there, somewhere. That's pretty pure straw man argument. I expect much higher quality trolling. D-. I'm sorely tempted to try to provide some higher-quality trolling, but in all seriousness I think that (1) we could certainly use much better regression tests in many areas of which this is one and (2) it will never be possible to catch all security bugs - in particular - via regression testing because they typically stem from cases people didn't consider. So... can we get back to coming up with a reasonable definition, and if somebody wants to write some regression tests, all the better? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Specification for Trusted PLs?
Robert Haas robertmh...@gmail.com writes: So... can we get back to coming up with a reasonable definition, (1) no access to system calls (including file and network I/O) (2) no access to process memory, other than variables defined within the PL. What else? 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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 2:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So... can we get back to coming up with a reasonable definition, (1) no access to system calls (including file and network I/O) (2) no access to process memory, other than variables defined within the PL. What else? Doesn't subvert the general PostgreSQL security mechanisms? Not sure how to formulate that. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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] Specification for Trusted PLs?
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Well, the best way to define what a trusted language can do is to define a *whitelist* of what it can do, not a blacklist of what it can't do. That's the only way to get a complete definition. It's then up to the implementation step to figure out how to represent that in the form of tests. No, that's exactly backwards. We can't define all the things a language can do, but we can certainly lay out the things that it is not supposed to. - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005211452 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkv21oIACgkQvJuQZxSWSsg8lQCdFKNXO5XWD5bJ0lQAx3prFYGW 5CYAnjHiuwKVAxvwjl/clyiwCtXCVvr0 =5tSD -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Specification for Trusted PLs?
* Robert Haas (robertmh...@gmail.com) wrote: So... can we get back to coming up with a reasonable definition, and Guess I'm wondering if we could steal such a definition from one of the languages we allow as trusted already.. Just a thought. I certainly think we should make sure that we document how untrusted languages are handled from the PG point of view (eg: can't change ownership). if somebody wants to write some regression tests, all the better? I certainly am fine with that to the extent that they want to work on that instead of hacking PG.. Guess I just don't think it should be a priority for us to come up with a signifigant regression suite for pieces that are supposedly being externally managed. Thanks, Stephen signature.asc Description: Digital signature
[HACKERS] small exclusion constraints patch
Currently, the check for exclusion constraints performs a sanity check that's slightly too strict -- it assumes that a tuple will conflict with itself. That is not always the case: the operator might be , in which case it's perfectly valid for the search for conflicts to not find itself. This patch simply removes that sanity check, and leaves a comment in place. Regards, Jeff Davis *** a/src/backend/executor/execUtils.c --- b/src/backend/executor/execUtils.c *** *** 1309,1323 retry: index_endscan(index_scan); /* ! * We should have found our tuple in the index, unless we exited the loop ! * early because of conflict. Complain if not. */ - if (!found_self !conflict) - ereport(ERROR, - (errcode(ERRCODE_INTERNAL_ERROR), - errmsg(failed to re-find tuple within index \%s\, - RelationGetRelationName(index)), - errhint(This may be because of a non-immutable index expression.))); econtext-ecxt_scantuple = save_scantuple; --- 1309,1320 index_endscan(index_scan); /* ! * Ordinarily, at this point the search should have found the ! * inserted tuple if there was no conflict. However, there are ! * some cases where a tuple may not conflict with itself, and ! * therefore would _not_ have found itself in this search -- for ! * instance, if the operator is . */ econtext-ecxt_scantuple = save_scantuple; -- 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] Specification for Trusted PLs?
Greg Sabino Mullane g...@turnstep.com writes: Well, the best way to define what a trusted language can do is to define a *whitelist* of what it can do, not a blacklist of what it can't do. No, that's exactly backwards. We can't define all the things a language can do, but we can certainly lay out the things that it is not supposed to. Yeah. The whole point of allowing multiple PLs is that some of them make it possible/easy to do things you can't (easily) do in others. So I'm not sure that a whitelist is going to be especially useful. 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] Specification for Trusted PLs?
Robert Haas robertmh...@gmail.com writes: On Fri, May 21, 2010 at 2:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: (1) no access to system calls (including file and network I/O) (2) no access to process memory, other than variables defined within the PL. What else? Doesn't subvert the general PostgreSQL security mechanisms? Not sure how to formulate that. As long as you can't do database access except via SPI, that should be covered. So I guess the next item on the list is no, or at least restricted, access to functions outside the PL's own language. 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] changed source files.
-BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 How does one find out what source files were modified between two releases? For example, what .c and .h changed between 8.4.3 and 8.4.4? The Notes do not mention specific source files. You can pull down the CVS tags for each and then run a recursive diff.. Recursive? My CVS-foo is fading fast (hello git!) but it should be just: $ cvs diff -rREL8_4_3 -rREL8_4_4 \ | grep 'RCS file' \ | grep \.[ch] \ | cut -d/ -f5- \ | cut -d, -f1 \ | sort The list is so short I'm just going to post it inline: contrib/intarray/_int_gin.c contrib/pgstattuple/pgstattuple.c src/backend/access/heap/heapam.c src/backend/access/transam/xlog.c src/backend/catalog/pg_proc.c src/backend/commands/dbcommands.c src/backend/commands/user.c src/backend/executor/functions.c src/backend/libpq/hba.c src/backend/nodes/outfuncs.c src/backend/optimizer/plan/planner.c src/backend/optimizer/util/clauses.c src/backend/optimizer/util/plancat.c src/backend/parser/scansup.c src/backend/port/sysv_shmem.c src/backend/postmaster/pgarch.c src/backend/postmaster/syslogger.c src/backend/storage/ipc/ipc.c src/backend/utils/cache/relcache.c src/backend/utils/error/elog.c src/backend/utils/misc/guc.c src/bin/psql/copy.c src/bin/psql/print.c src/include/nodes/relation.h src/include/pg_config.h.win32 src/include/utils/guc.h src/interfaces/ecpg/ecpglib/connect.c src/interfaces/libpq/fe-misc.c src/pl/plperl/plperl.c src/pl/plperl/ppport.h src/pl/plpgsql/src/pl_exec.c src/pl/plpython/plpython.c src/pl/tcl/pltcl.c src/timezone/pgtz.c - -- Greg Sabino Mullane g...@turnstep.com End Point Corporation http://www.endpoint.com/ PGP Key: 0x14964AC8 201005211520 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iEYEAREDAAYFAkv23TEACgkQvJuQZxSWSsi0BwCg1Q8jnMdzpUYLU7LgsWkINyIE WzsAnA8xTTB3KSAJES34sIB19DyHK/2O =vAit -END PGP SIGNATURE- -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] small exclusion constraints patch
Jeff Davis pg...@j-davis.com writes: Currently, the check for exclusion constraints performs a sanity check that's slightly too strict -- it assumes that a tuple will conflict with itself. That is not always the case: the operator might be , in which case it's perfectly valid for the search for conflicts to not find itself. This patch simply removes that sanity check, and leaves a comment in place. I'm a bit uncomfortable with removing the sanity check; it seems like a good thing to have, especially since this code hasn't even made it out of beta yet. AFAIK the case is purely hypothetical, because we have no index opclasses supporting such an operator, no? How about just documenting that we'd need to remove the sanity check if we ever did add support for such a case? 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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 03:15:27PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, May 21, 2010 at 2:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: (1) no access to system calls (including file and network I/O) (2) no access to process memory, other than variables defined within the PL. What else? Doesn't subvert the general PostgreSQL security mechanisms? Not sure how to formulate that. As long as you can't do database access except via SPI, that should be covered. So I guess the next item on the list is no, or at least restricted, access to functions outside the PL's own language. No access seems pretty draconian. How about limiting such access to functions of equal or lower trustedness? Surely an untrusted function shouldn't be restricted from calling other untrusted functions based on the language they're written in. Cheers, David (who is not, at this point, going to suggest that a trusted boolean may inadequately reflect users' needs) -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 12:36:50PM -0700, David Fetter wrote: On Fri, May 21, 2010 at 03:15:27PM -0400, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Fri, May 21, 2010 at 2:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: (1) no access to system calls (including file and network I/O) (2) no access to process memory, other than variables defined within the PL. What else? Doesn't subvert the general PostgreSQL security mechanisms? Not sure how to formulate that. As long as you can't do database access except via SPI, that should be covered. So I guess the next item on the list is no, or at least restricted, access to functions outside the PL's own language. No access seems pretty draconian. How about limiting such access to functions of equal or lower trustedness? I see that's confusing. What I meant was that functions in trusted languages should be able to call other functions in trusted languages, while functions in untrusted languages shouldn't be restricted as to what other functions they can call. Cheers, David. -- David Fetter da...@fetter.org http://fetter.org/ Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter Skype: davidfetter XMPP: david.fet...@gmail.com iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics Remember to vote! Consider donating to Postgres: http://www.postgresql.org/about/donate -- 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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 1:36 PM, David Fetter da...@fetter.org wrote: On Fri, May 21, 2010 at 03:15:27PM -0400, Tom Lane wrote: As long as you can't do database access except via SPI, that should be covered. So I guess the next item on the list is no, or at least restricted, access to functions outside the PL's own language. No access seems pretty draconian. How about limiting such access to functions of equal or lower trustedness? Surely an untrusted function shouldn't be restricted from calling other untrusted functions based on the language they're written in. Agreed. As long as a trusted language can do things outside the database only by going through a database and calling some function to which the user has rights, in an untrusted language, that seems decent to me. A user with permissions to launch_missiles() would have a function in an untrusted language to do it, but there's no reason an untrusted language shouldn't be able to say SELECT launch_missiles(). -- Joshua Tolley / eggyknap End Point Corporation -- 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] Specification for Trusted PLs?
Joshua Tolley eggyk...@gmail.com writes: Agreed. As long as a trusted language can do things outside the database only by going through a database and calling some function to which the user has rights, in an untrusted language, that seems decent to me. A user with permissions to launch_missiles() would have a function in an untrusted language to do it, but there's no reason an untrusted language shouldn't be able to say SELECT s/untrusted/trusted/ here, right? launch_missiles(). To me, as long as they go back into the database via SPI, anything they can get to from there is OK. What I meant to highlight upthread is that we don't want trusted functions being able to access other functions directly without going through SQL. As an example, a PL that has FFI capability sufficient to allow direct access to heap_insert() would have to be considered untrusted. 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] Specification for Trusted PLs?
Howdy, On Fri, May 21, 2010 at 11:21 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: So... can we get back to coming up with a reasonable definition, (1) no access to system calls (including file and network I/O) (2) no access to process memory, other than variables defined within the PL. What else? I ran across this comment in PL/Perl while implementing PL/Parrot, and I think it should be taken into consideration for the definition of trusted/untrusted: /* * plperl.on_plperl_init is currently PGC_SUSET to avoid issues whereby a * user who doesn't have USAGE privileges on the plperl language could * possibly use SET plperl.on_plperl_init='...' to influence the behaviour * of any existing plperl function that they can EXECUTE (which may be * security definer). Set * http://archives.postgresql.org/pgsql-hackers/2010-02/msg00281.php and * the overall thread. */ Duke -- Jonathan Duke Leto jonat...@leto.net http://leto.net -- 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] Specification for Trusted PLs?
On Fri, May 21, 2010 at 2:04 PM, Tom Lane t...@sss.pgh.pa.us wrote: Joshua Tolley eggyk...@gmail.com writes: Agreed. As long as a trusted language can do things outside the database only by going through a database and calling some function to which the user has rights, in an untrusted language, that seems decent to me. A user with permissions to launch_missiles() would have a function in an untrusted language to do it, but there's no reason an untrusted language shouldn't be able to say SELECT s/untrusted/trusted/ here, right? Er, right. Sorry. launch_missiles(). To me, as long as they go back into the database via SPI, anything they can get to from there is OK. What I meant to highlight upthread is that we don't want trusted functions being able to access other functions directly without going through SQL. As an example, a PL that has FFI capability sufficient to allow direct access to heap_insert() would have to be considered untrusted. That I can definitely agree with. -- Joshua Tolley / eggyknap End Point Corporation -- 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] Specification for Trusted PLs?
The original idea was that a trusted language does not allow an unprivileged user to gain access to any object or data, he does not have access to without that language. This does not include data transformation functionality, like string processing or the like. As long as the user had legitimate access to the input datum, then every derived form thereof is OK. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- 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] unnailing shared relations (was Re: global temporary tables)
On Fri, May 21, 2010 at 11:10 AM, Pavel Stehule pavel.steh...@gmail.com wrote: 2010/5/21 Robert Haas robertmh...@gmail.com: On Sat, Apr 24, 2010 at 6:53 PM, Robert Haas robertmh...@gmail.com wrote (in reply to Tom Lane): If we create, e.g. pg_shared_class and pg_shared_attribute, then we can un-nail the catalogs you just nailed to make the authentication process able to work without selecting a database. Actually, there's another way we could do this. Instead of creating pg_shared_class and pg_shared_attribute and moving all of the catalog entries for the shared relations into those tables, we could consider leaving the catalog entries in the unshared copies of pg_class, pg_attribute, etc. and DUPLICATING them in a shared catalog which would only be used prior to selecting a database. Once we selected a database we'd switch to using the database-specific pg_class et al. Obviously that's a little grotty but it might (?) be easier, and possibly a step along the way. I did it - just on syscache level - but there are problem with refresh. I though about some special pseudo persistent data pages attached to possible any table with temp data. Then you don't need modify any on higher level, you don't need new catalog entries, etc .. I don't think we're talking about the same thing. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise Postgres 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) btree_gist support for searching on not equals
This patch adds support to btree_gist for searching on (not equals). This allows an interesting use of exclusion constraints: Say you have a table: create table zoo ( cage int, animal text, exclude using gist (cage with =, animal with ) ); That will permit you to add as many zebras as you want to a given cage, and as many lions as you want to another cage, but will not allow you to mix zebras and lions in the same cage. It also allows you to enforce the constraint that only one tuple exists in a table by doing something like: create table a ( i int, exclude using gist (i with ), unique (i) ); Regards, Jeff Davis *** a/contrib/btree_gist/btree_gist.h --- b/contrib/btree_gist/btree_gist.h *** *** 9,14 --- 9,16 #include access/itup.h #include access/nbtree.h + #define BTNotEqualStrategyNumber 6 + /* indexed types */ enum gbtree_type *** a/contrib/btree_gist/btree_gist.sql.in --- b/contrib/btree_gist/btree_gist.sql.in *** *** 143,148 AS --- 143,149 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_oid_consistent (internal, oid, int2, oid, internal), FUNCTION 2 gbt_oid_union (bytea, internal), FUNCTION 3 gbt_oid_compress (internal), *** *** 200,205 AS --- 201,207 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_int2_consistent (internal, int2, int2, oid, internal), FUNCTION 2 gbt_int2_union (bytea, internal), FUNCTION 3 gbt_int2_compress (internal), *** *** 256,261 AS --- 258,264 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_int4_consistent (internal, int4, int2, oid, internal), FUNCTION 2 gbt_int4_union (bytea, internal), FUNCTION 3 gbt_int4_compress (internal), *** *** 312,317 AS --- 315,321 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_int8_consistent (internal, int8, int2, oid, internal), FUNCTION 2 gbt_int8_union (bytea, internal), FUNCTION 3 gbt_int8_compress (internal), *** *** 369,374 AS --- 373,379 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_float4_consistent (internal, float4, int2, oid, internal), FUNCTION 2 gbt_float4_union (bytea, internal), FUNCTION 3 gbt_float4_compress (internal), *** *** 428,433 AS --- 433,439 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_float8_consistent (internal, float8, int2, oid, internal), FUNCTION 2 gbt_float8_union (bytea, internal), FUNCTION 3 gbt_float8_compress (internal), *** *** 495,500 AS --- 501,507 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_ts_consistent (internal, timestamp, int2, oid, internal), FUNCTION 2 gbt_ts_union (bytea, internal), FUNCTION 3 gbt_ts_compress (internal), *** *** 514,519 AS --- 521,527 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_tstz_consistent (internal, timestamptz, int2, oid, internal), FUNCTION 2 gbt_ts_union (bytea, internal), FUNCTION 3 gbt_tstz_compress (internal), *** *** 581,586 AS --- 589,595 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_time_consistent (internal, time, int2, oid, internal), FUNCTION 2 gbt_time_union (bytea, internal), FUNCTION 3 gbt_time_compress (internal), *** *** 598,603 AS --- 607,613 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_timetz_consistent (internal, timetz, int2, oid, internal), FUNCTION 2 gbt_time_union (bytea, internal), FUNCTION 3 gbt_timetz_compress (internal), *** *** 655,660 AS --- 665,671 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_date_consistent (internal, date, int2, oid, internal), FUNCTION 2 gbt_date_union (bytea, internal), FUNCTION 3 gbt_date_compress (internal), *** *** 717,722 AS --- 728,734 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_intv_consistent (internal, interval, int2, oid, internal), FUNCTION 2 gbt_intv_union (bytea, internal), FUNCTION 3 gbt_intv_compress (internal), *** *** 773,778 AS --- 785,791 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_cash_consistent (internal, money, int2, oid, internal), FUNCTION 2 gbt_cash_union (bytea, internal), FUNCTION 3 gbt_cash_compress (internal), *** *** 829,834 AS --- 842,848 OPERATOR 3 = , OPERATOR 4 = , OPERATOR 5 , + OPERATOR 6 , FUNCTION 1 gbt_macad_consistent (internal, macaddr, int2,
[HACKERS] Idea for getting rid of VACUUM FREEZE on cold pages
From a discussion at dinner at pgcon, I wanted to send this to the list for people to poke holes in it: Problem: currently, if your database has a large amount of cold data, such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer needs to touch it thanks to the visibility map. However, every freeze_age transactions, very old pages need to be sucked into memory and rewritten just in order to freeze those pages. This can have a huge impact on system performance, and seems unjustified because the pages are not actually being used. Suggested resolution: we would add a 4-byte field to the *page* header which would track the XID wraparound count. Any page whose wraparound count was not equal to the current one would be considered to have all frozen tuples. This would remove the necessity to read and write old pages just to freeze them, a humongous gain for databases with long data retention horizons, let alone data warehouses. All xids on the page would, necessarily, need to belong to the same wraparound; if a page gets updated and its wraparound count (hereafter WCID) is lower than current, all tuples on the page would be frozen before any data is written to it. XIDs which were before the max_freeze horizon on a page which was being written anyway would be frozen as they are now. Obvious issues: (1) In a case of rows written close to the wraparound point, this would cause a set of tuples to be frozen sooner than they would be in the current system. (2) It's not clear what to do with a page where there are XIDs which are just before wraparound (like XID # 2.4b) which are still visible and receives a write with a new cycle xid (#1). (3) This will require changing the page structure, with all that entails. So it should probably be done when we're making another change (like adding CRCs). -- -- Josh Berkus PostgreSQL Experts Inc. http://www.pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] (9.1) btree_gist support for searching on not equals
On 5/21/10 11:47 PM +0300, Jeff Davis wrote: It also allows you to enforce the constraint that only one tuple exists in a table by doing something like: create table a ( i int, exclude using gist (i with), unique (i) ); FWIW, this is achievable a lot more easily: CREATE UNIQUE INDEX a_single_row ON a ((1)); Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] beta testing - planner bug - ERROR: XX000: failed to build any 2-way joins
Hello there are maybe planner bug. test case: CREATE TABLE person ( id SERIAL PRIMARY KEY, name VARCHAR(64) ) WITHOUT OIDS; CREATE TABLE person_data ( id SERIAL PRIMARY KEY ) WITHOUT OIDS; SELECT name FROM person WHERE name IN ( SELECT name FROM person p LEFT JOIN person_data ON p.id = person_data.id ) postgres=# explain SELECT name FROM person WHERE name IN ( SELECT name FROM person p LEFT JOIN person_data ON p.id = person_data.id); ERROR: failed to build any 2-way joins it works on 8.3 report by Vaclav Novotny Regards Pavel -- 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] Idea for getting rid of VACUUM FREEZE on cold pages
On May 21, 2010, at 23:57 , Josh Berkus wrote: From a discussion at dinner at pgcon, I wanted to send this to the list for people to poke holes in it: Problem: currently, if your database has a large amount of cold data, such as 350GB of 3-year-old sales transactions, in 8.4 vacuum no longer needs to touch it thanks to the visibility map. However, every freeze_age transactions, very old pages need to be sucked into memory and rewritten just in order to freeze those pages. This can have a huge impact on system performance, and seems unjustified because the pages are not actually being used. Suggested resolution: we would add a 4-byte field to the *page* header which would track the XID wraparound count. Any page whose wraparound count was not equal to the current one would be considered to have all frozen tuples. This would remove the necessity to read and write old pages just to freeze them, a humongous gain for databases with long data retention horizons, let alone data warehouses. If I understand this correctly, VACUUM usually only frees old tuples, but never increases the oldest xid in the pg_class record. Once that value becomes older than freeze_age, VACUUM needs to scan the whole relation to freeze old tuples. That results in most of the pages being marked dirty and subsequently being written out, causing an IO storm. If, OTOH, the wraparound count was stored in the page header, VACUUM would still need to read those pages, but wouldn't need to write them out. Alternatively, VACUUM could freeze a few pages on each run, even if the xids are below freeze_age. It could pick those pages randomly, or maybe even prefer pages whose tuples have older xmin/xmas values. That would spread the load out more evenly, much like we try to spread checkpoints out over the whole checkpoint interval. best regards, Florian Pflugi -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] beta testing - pg_upgrade bug fix - double free
Hello it fixes bug pg_upgrade(13359) malloc: *** error for object 0x801600: non-page-aligned, non-allocated pointer being freed *** set a breakpoint in malloc_error_break to debug arget 03:31 /usr/local/src/postgresql/contrib/pg_upgrade git diff . diff --git a/contrib/pg_upgrade/check.c b/contrib/pg_upgrade/check.c index 31f12fb..f989229 100644 --- a/contrib/pg_upgrade/check.c +++ b/contrib/pg_upgrade/check.c @@ -154,7 +154,6 @@ issue_warnings(migratorContext *ctx, char *sequence_script_file_name) ctx-new.bindir, ctx-new.port, sequence_script_file_name, ctx-logfile); unlink(sequence_script_file_name); - pg_free(sequence_script_file_name); check_ok(ctx); } by Jan Matousek Regards Pavel -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
