Re: [HACKERS] Review: support for multiplexing SIGUSR1
Hi, On Tue, Jul 28, 2009 at 12:09 AM, Tom Lanet...@sss.pgh.pa.us wrote: I think you're making things more complicated when they should be getting simpler. It strikes me that the current API of pass the BackendId if known or InvalidBackendId if not still works for processes without a BackendId, as long as you can tolerate a bit of extra search overhead for them. (You could reduce the search overhead by searching the array back to front.) So a new process index may be overkill. Yeah, this is very simple. I'll change the patch according to your suggestion. Such extra search wouldn't be problem. Because an auxiliary process doesn't need a catchup and notify interrupt which are intended uses. Also, because there are few opportunities to send a multiplexed signal to an auxiliary process in Synch Rep. Do we have a clear idea of what the future applications are? Yes, I'm planning to use this mechanism for communication between a process which can generate the WAL records and a WAL sender process, in Synch Rep. Since not only a backend but also some auxiliary processes (e.g., bgwriter) can generate the WAL records, processes which don't have a ProcState need to receive the multiplexed signal, too. Huh? Wouldn't the bgwriter be *sending* the signal not receiving it? The bgwriter sends and receives a signal to/from the walsender process. The walsender is new introduced process in Synch Rep, and there are some free signals which aren't assigned yet. So, the signal sent from a backend or an auxiliary process to the walsender doesn't need to be multiplexed. On the other hand, the signal sent to a backend which has no free signal must be multiplexed. Since I'd like to treat a backend and an auxiliary process as unified destination of a signal, I'd like to multiplex also a signal sent to an auxiliary process. Of course, though we can change the method of signaling according to the destination (e.g., call a native kill instead of newly-introduced SendProcSignal when the destination is the bgwriter), it seems to be awkward. Umm... the patch should cover a notify interrupt which currently uses SIGUSR2? Getting rid of the separate SIGUSR2 handler would definitely be a good proof of concept that the mechanism works for more than one use. OK. I'll change the patch as above. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PostgreSQL Specifications
I revised the SE-PostgreSQL Specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft - Put several external link to introduce something too detail for PostgreSQL documentations. - Paid attention not to use undefined terminology, such as security context, security policy and mandatory access controls. - Revised whole of the composition in the Brief overview section. - Put an example of security policy rule. - SECURITY_LABEL option was replaced by SECURITY_CONTEXT to avoid meaningless confusion. I believe it become better than previous revision. Thanks, KaiGai Kohei wrote: Sam Mason wrote: On Sun, Jul 26, 2009 at 12:27:12PM +0900, KaiGai Kohei wrote: Indeed, the draft used the term of security context with minimum introductions, but not enough friendliness for database folks. The purpose of security context is an identifier of any subject and object to describe them in the security policy. Because the security policy is common for operating system, databases, x-window and others, any managed database objects needs its security context. Anyway, I need to introduce them in the security model section. I'm coming to the conclusion that you really need to link to external material here; there must be good (and canonical) definitions of these things outside and because SE-PG isn't self contained I really think you need to link to them. This will be somewhat of a break from normal PG documentation because so far everything has been self contained, it's chosen its own interpretation of the SQL standard and it needs to document that. SE-PG will be interacting with much more code from outside and showing which parts of these are PG specific vs. which parts are common to all SELinux seems important. If you try to document *everything* you're going to be writing for years and give the impression that everything is implemented in SE-PG. A dividing line needs to be drawn between what is PG specific and what is SELinux (why not SEL?). It also seems to me reasonable suggestion. However, a reasonable amount (which should be adjusted under discussions) of description should be self-contained. For example, security context is a formatted short string is not enough to understand why it is necessary and what is the purpose. As Robert suggested, a few example and definition of technical terms will help database folks to understand what it is, even if self-contained explanation is not comprehensive from viewpoint of security folks. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PostgreSQL Specifications
On Sunday 26 July 2009 14:35:41 Sam Mason wrote: I'm coming to the conclusion that you really need to link to external material here; there must be good (and canonical) definitions of these things outside and because SE-PG isn't self contained I really think you need to link to them. This is not supposed to be user documentation. It's supposed to be a feature specification that an implementation can be validated against. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] SE-PostgreSQL Specifications
Thanks for the updates. I might suggest a couple of small changes: a) a section that explains comments like This is not supported in the initial version -- do you mean in the first Beta release of SE-PostgreSQL, or not in the initial release(s) for commitfests ? If it is not supported why mention it ? If experienced users of SELinux expect it, they might look for an explanation as to why it is missing and when it might appear. I'm not sure if postgres DB hackers would care if is it is not to be included. How much do these compromise the design, and if so, are there specific plans for implementing them ? b) something which explains the differences between SELinux and SEPostgreSQL on the one hand (for SE fans). You've done a good job of outlining the differences and similarities with the more standard ACL methods and that needs to be kept prominent so people with DB experience can see the differences. I am all in favor of external links if you can find good explanation of concepts elsewhere. This is a very high level outline and so I'd be tempted to move all implementation details to another page -- basically everything from Installation on, with the exception of the overview of the dump issues, is (to my eye) a detail that doesn't need exposing (yet). I'll send mail when I have a few paragraphs done so you can check it and see if you approve. Apologies for top-posting -- lame mailer. Greg W. - Original Message From: KaiGai Kohei kai...@ak.jp.nec.com To: KaiGai Kohei kai...@kaigai.gr.jp Cc: Sam Mason s...@samason.me.uk; pgsql-hackers@postgresql.org Sent: Monday, July 27, 2009 11:57:32 PM Subject: Re: [HACKERS] SE-PostgreSQL Specifications I revised the SE-PostgreSQL Specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft - Put several external link to introduce something too detail for PostgreSQL documentations. - Paid attention not to use undefined terminology, such as security context, security policy and mandatory access controls. - Revised whole of the composition in the Brief overview section. - Put an example of security policy rule. - SECURITY_LABEL option was replaced by SECURITY_CONTEXT to avoid meaningless confusion. I believe it become better than previous revision. Thanks, KaiGai Kohei wrote: Sam Mason wrote: On Sun, Jul 26, 2009 at 12:27:12PM +0900, KaiGai Kohei wrote: Indeed, the draft used the term of security context with minimum introductions, but not enough friendliness for database folks. The purpose of security context is an identifier of any subject and object to describe them in the security policy. Because the security policy is common for operating system, databases, x-window and others, any managed database objects needs its security context. Anyway, I need to introduce them in the security model section. I'm coming to the conclusion that you really need to link to external material here; there must be good (and canonical) definitions of these things outside and because SE-PG isn't self contained I really think you need to link to them. This will be somewhat of a break from normal PG documentation because so far everything has been self contained, it's chosen its own interpretation of the SQL standard and it needs to document that. SE-PG will be interacting with much more code from outside and showing which parts of these are PG specific vs. which parts are common to all SELinux seems important. If you try to document *everything* you're going to be writing for years and give the impression that everything is implemented in SE-PG. A dividing line needs to be drawn between what is PG specific and what is SELinux (why not SEL?). It also seems to me reasonable suggestion. However, a reasonable amount (which should be adjusted under discussions) of description should be self-contained. For example, security context is a formatted short string is not enough to understand why it is necessary and what is the purpose. As Robert suggested, a few example and definition of technical terms will help database folks to understand what it is, even if self-contained explanation is not comprehensive from viewpoint of security folks. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Higher TOAST compression.
On Thu, Jul 23, 2009 at 4:45 PM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: Laurent Laborde kerdez...@gmail.com wrote: (iostat show a 5~25MB/s bandwidth at 100%util instead of 2~5MB/s at 100%util). Any numbers for overall benefit at the application level? So... now i'm not sure anymore about lowering the tuple per page om 4 to 8. Doing that would mean more data in TOAST table ... Yeah, I've been skeptical that it would be a good thing for your situation unless the lower target only applied to more aggressive compression, not out-of-line storage. I tested to change the TUPLES PER PAGE (EXTERNAL) to 8. As expected, it very badly impact the IO performance as many tuple (always read) are now stored external. With some extremly high IOwait peak because of the additional disk seek required to get the toasted data (the average IO bandwidth dropped) : Cpu0 : 5.3%us, 3.0%sy, 0.0%ni, 7.0%id, 83.4%wa, 0.7%hi, 0.7%si, 0.0%st Cpu1 : 4.3%us, 1.3%sy, 0.0%ni, 5.7%id, 88.7%wa, 0.0%hi, 0.0%si, 0.0%st Cpu2 : 3.3%us, 0.7%sy, 0.0%ni, 8.0%id, 88.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu3 : 3.7%us, 0.7%sy, 0.0%ni, 4.7%id, 91.0%wa, 0.0%hi, 0.0%si, 0.0%st Cpu4 : 4.0%us, 1.3%sy, 0.0%ni, 8.0%id, 86.7%wa, 0.0%hi, 0.0%si, 0.0%st Cpu5 : 3.7%us, 0.3%sy, 0.0%ni, 5.7%id, 90.3%wa, 0.0%hi, 0.0%si, 0.0%st Cpu6 : 3.0%us, 0.7%sy, 0.0%ni, 6.7%id, 89.7%wa, 0.0%hi, 0.0%si, 0.0%st Cpu7 : 2.7%us, 0.7%sy, 0.0%ni, 7.7%id, 89.0%wa, 0.0%hi, 0.0%si, 0.0%st If you can wait for a week or two, I can give you a proof of concept patch to use separate targets for compression and out-of-line storage. It would be interesting to see how much that helps when combined with your more aggressive compression configuration. Of course, of course, i can wait. All my patchs and testing are released on a public github.com : http://github.com/ker2x/AkaneSQL/tree/master I'll continue to patch postgresql/AkaneSQL, for fun and learning purpose :) Hoping to be good enough, someday, to submit patch here. -- Laurent Laborde Sysadmin @ http://www.over-blog.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] SE-PostgreSQL Specifications
On Mon, Jul 27, 2009 at 01:53:07PM -0400, Chris Browne wrote: s...@samason.me.uk (Sam Mason) writes: On Sun, Jul 26, 2009 at 01:42:32PM +0900, KaiGai Kohei wrote: Robert Haas wrote: In some cases, the clearance of infoamtion may be changed. We often have dome more complex requirements also. OK, so there is some other trusted entity that has unfettered access to both databases and its job is to manage these requirements. No, that's not what this implies. What this implies is along the following lines... If a user at the more secret level updates some data that had been classified at a lower level, then that data gets reclassified at the higher level. I still think it does; but maybe there are other ways of arranging things. The problem seems to be that if each user only has write access to their own level then the is no bound as to how far the two databases will get out of sync with each other. Some way has to be made of declassifying data and so bound the amount of difference between the two. This declassification can not done by a normal user as they can only write in their own level. This trusted entity has to exist to punch a hole in the security to do something that wouldn't otherwise be allowed to happen, information normally only flows up the hierarchy. -- Sam http://samason.me.uk/ -- 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] SE-PostgreSQL Specifications
[wretched top-posting -- begs forgiveness!] KaiGai -- I have edited the first three sections (up to but not including Architecture), mostly cleaning up language but I did run into a few places where I am not sure if I got the proper meaning -- I flagged those in square brackets (e.g.[...]) and with your name. Feel free to change them, accept them, or confer back with me about them. In particular, in the Security policy section you had: TE rules use the third field in the security context. It is called type or domain (for processes). allow httpd_t sepgsql_ro_table_t : db_table {getattr select lock}; I thought that colons were used to split these fields, so the above line would have only 2 ? Perhaps after the httpd_t ? I'd like to get some feedback from you (and any other readers) before I do more ... I need to go deal an invasion of the kitchen -- it is garbage collection morning (early) and I just had a pretty young skunk and two raccoon kits in rapid order and I have to clean up and secure the premises. Regards! G - Original Message From: Greg Williamson gwilliamso...@yahoo.com To: KaiGai Kohei kai...@ak.jp.nec.com; KaiGai Kohei kai...@kaigai.gr.jp Cc: Sam Mason s...@samason.me.uk; pgsql-hackers@postgresql.org Sent: Tuesday, July 28, 2009 1:20:29 AM Subject: Re: [HACKERS] SE-PostgreSQL Specifications Thanks for the updates. I might suggest a couple of small changes: a) a section that explains comments like This is not supported in the initial version -- do you mean in the first Beta release of SE-PostgreSQL, or not in the initial release(s) for commitfests ? If it is not supported why mention it ? If experienced users of SELinux expect it, they might look for an explanation as to why it is missing and when it might appear. I'm not sure if postgres DB hackers would care if is it is not to be included. How much do these compromise the design, and if so, are there specific plans for implementing them ? b) something which explains the differences between SELinux and SEPostgreSQL on the one hand (for SE fans). You've done a good job of outlining the differences and similarities with the more standard ACL methods and that needs to be kept prominent so people with DB experience can see the differences. I am all in favor of external links if you can find good explanation of concepts elsewhere. This is a very high level outline and so I'd be tempted to move all implementation details to another page -- basically everything from Installation on, with the exception of the overview of the dump issues, is (to my eye) a detail that doesn't need exposing (yet). I'll send mail when I have a few paragraphs done so you can check it and see if you approve. Apologies for top-posting -- lame mailer. Greg W. - Original Message From: KaiGai Kohei kai...@ak.jp.nec.com To: KaiGai Kohei kai...@kaigai.gr.jp Cc: Sam Mason s...@samason.me.uk; pgsql-hackers@postgresql.org Sent: Monday, July 27, 2009 11:57:32 PM Subject: Re: [HACKERS] SE-PostgreSQL Specifications I revised the SE-PostgreSQL Specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft - Put several external link to introduce something too detail for PostgreSQL documentations. - Paid attention not to use undefined terminology, such as security context, security policy and mandatory access controls. - Revised whole of the composition in the Brief overview section. - Put an example of security policy rule. - SECURITY_LABEL option was replaced by SECURITY_CONTEXT to avoid meaningless confusion. I believe it become better than previous revision. Thanks, KaiGai Kohei wrote: Sam Mason wrote: On Sun, Jul 26, 2009 at 12:27:12PM +0900, KaiGai Kohei wrote: Indeed, the draft used the term of security context with minimum introductions, but not enough friendliness for database folks. The purpose of security context is an identifier of any subject and object to describe them in the security policy. Because the security policy is common for operating system, databases, x-window and others, any managed database objects needs its security context. Anyway, I need to introduce them in the security model section. I'm coming to the conclusion that you really need to link to external material here; there must be good (and canonical) definitions of these things outside and because SE-PG isn't self contained I really think you need to link to them. This will be somewhat of a break from normal PG documentation because so far everything has been self contained, it's chosen its own interpretation of the SQL standard and it needs to document that. SE-PG will be interacting with much more code from outside and showing which parts of these are PG specific vs. which parts are common to all SELinux seems important. If you try to document *everything* you're going to be writing for years and give the impression that everything is implemented in SE-PG. A dividing line needs to be
Re: [HACKERS] [RFC] new digest datatypes, or generic fixed-len hex types?
On Monday 27 July 2009 14:50:30 Alvaro Herrera wrote: We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA1 and the various SHA2 types). These types have minimal overhead and are quite complete, including btree and hash opclasses. We're wondering about proposing them for inclusion in pgcrypto. I asked Marko Kreen but he is not sure about it; according to him it would be better to have general fixed-length hex types. (I guess it would be possible to implement the digest types as domains over those.) I think equipping bytea with a length restriction would be a very natural, simple, and useful addition. If we ever want to move the bytea type closer to the SQL standard blob type, this will need to happen anyway. The case for separate fixed-length data types seems very dubious, unless you can show very impressive performance numbers. For one thing, they would make the whole type system more complicated, or in the alternative, would have little function and operator support. -- 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] display previous query string of idle-in-transaction
On Friday 24 July 2009 18:15:00 Tom Lane wrote: Another question is that this proposal effectively redefines the current_query column as not the current query, but something that might be better be described as latest_query. Should we change the name? We'd probably break some client code if we did, but on the other hand the semantics change might break such code anyway. Intentional breakage might not be such a bad thing if it forces people to take a fresh look at their code. That breakage could be pretty widespread, though. Maybe have current_query and last_query. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Psql List Languages
On Wed, Jul 22, 2009 at 9:23 PM, Robert Haasrobertmh...@gmail.com wrote: On Sun, Jul 19, 2009 at 4:00 AM, Peter Eisentrautpete...@gmx.net wrote: Please submit an updated patch. If you would like to have this change committed during this CommitFest, please submit an updated patch ASAP. Otherwise, you can resubmit for the next CommitFest in September. As this patch has not been updated, I am moving it to Returned with Feedback. ...Robert -- 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] SE-PostgreSQL Specifications
Peter Eisentraut wrote: On Sunday 26 July 2009 14:35:41 Sam Mason wrote: I'm coming to the conclusion that you really need to link to external material here; there must be good (and canonical) definitions of these things outside and because SE-PG isn't self contained I really think you need to link to them. This is not supposed to be user documentation. It's supposed to be a feature specification that an implementation can be validated against. Hmm... What kind of descriptions are necessary for a feature specifications? Currently, I guess the specification describes when/where the security hook should be invoked, what permission should be checked and what result should be returned for each security hooks. Is it correct? Or, do you expect any other stuffs? Apart from that, user documentation is also necessary. If the specification should be described from completely different viewpoint, I'll provide it. Thanks, -- KaiGai Kohei kai...@kaigai.gr.jp -- 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] Higher TOAST compression.
I'm currently rewriting the whole toaster stuff to simply define : - a compression threshold (size limit to compress, in Nth of page) - an external threshold (size limit to externalize compressed data, in Nth of page) i keep the TOAST_INDEX_TARGET and EXTERN_TUPLES_PER_PAGE. I expect a lot of trial and error as it will my first real patch. http://github.com/ker2x/AkaneSQL/tree/master as usual. -- F4FQM Kerunix Flan Laurent Laborde -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on
On Mon, Jul 27, 2009 at 16:14, Tom Lanet...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: To fix that we'd just have to turn those functions all into returning boolean and log with LOG instead. AFAIK, we've had zero reports of this actually happening, so I'm not sure it's worth redesigning. Thoughts? I'm not really insisting on a redesign. I'm talking about the places where the code author appears not to have understood that ERROR means FATAL, because the code keeps plugging on after it anyway. As far as I can see, using ERROR at lines 3630, 3657, 3674, and 3693 is just plain bogus, and changing to LOG there requires no other fixing. 3630: can't happen, because we already elog(ERROR) deep in the function, which is what I tried to outline above. That's the one requiring a redesign - because the errors *inside* the function it calls can certainly happen. 3657: is one of those should never happen issues - which we haven't had any reports of. 3674: see above 3693 is a comment, I assume you mean 3683, which is also one of those can't happen. But. I'll look into cleaning those up for HEAD anyway, but due to lack of reports I think we should skip backpatch. Reasonable? -- Magnus Hagander Self: 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] SE-PostgreSQL Specifications
Greg Williamson wrote: [wretched top-posting -- begs forgiveness!] KaiGai -- I have edited the first three sections (up to but not including Architecture), mostly cleaning up language but I did run into a few places where I am not sure if I got the proper meaning -- I flagged those in square brackets (e.g.[...]) and with your name. Feel free to change them, accept them, or confer back with me about them. Thanks for your efforts so much. I'll confirm it tomorrow (in JST). In particular, in the Security policy section you had: TE rules use the third field in the security context. It is called type or domain (for processes). allow httpd_t sepgsql_ro_table_t : db_table {getattr select lock}; I thought that colons were used to split these fields, so the above line would have only 2 ? Perhaps after the httpd_t ? It says the third field in the security policy, not a rule in the security policy. Sorry, it might be introduced more carefully. In the default security policy, web server process performs labeled as system_u:system_r:httpd_t:s0. ^^^ This rule is checked when web server process tries to access a table labeled as system_u:object_r:sepgsql_ro_table_t:s0, for example. ^^ The TE rule is defined between the pair of third field (which is called type or domain) of security contexts. # BTW, basically, the second field is used for RBAC rules, the fourth field # is used for MLS rules. The first field is used to record who create the # object. Thanks, I'd like to get some feedback from you (and any other readers) before I do more ... I need to go deal an invasion of the kitchen -- it is garbage collection morning (early) and I just had a pretty young skunk and two raccoon kits in rapid order and I have to clean up and secure the premises. Regards! G - Original Message From: Greg Williamson gwilliamso...@yahoo.com To: KaiGai Kohei kai...@ak.jp.nec.com; KaiGai Kohei kai...@kaigai.gr.jp Cc: Sam Mason s...@samason.me.uk; pgsql-hackers@postgresql.org Sent: Tuesday, July 28, 2009 1:20:29 AM Subject: Re: [HACKERS] SE-PostgreSQL Specifications Thanks for the updates. I might suggest a couple of small changes: a) a section that explains comments like This is not supported in the initial version -- do you mean in the first Beta release of SE-PostgreSQL, or not in the initial release(s) for commitfests ? If it is not supported why mention it ? If experienced users of SELinux expect it, they might look for an explanation as to why it is missing and when it might appear. I'm not sure if postgres DB hackers would care if is it is not to be included. How much do these compromise the design, and if so, are there specific plans for implementing them ? b) something which explains the differences between SELinux and SEPostgreSQL on the one hand (for SE fans). You've done a good job of outlining the differences and similarities with the more standard ACL methods and that needs to be kept prominent so people with DB experience can see the differences. I am all in favor of external links if you can find good explanation of concepts elsewhere. This is a very high level outline and so I'd be tempted to move all implementation details to another page -- basically everything from Installation on, with the exception of the overview of the dump issues, is (to my eye) a detail that doesn't need exposing (yet). I'll send mail when I have a few paragraphs done so you can check it and see if you approve. Apologies for top-posting -- lame mailer. Greg W. - Original Message From: KaiGai Kohei kai...@ak.jp.nec.com To: KaiGai Kohei kai...@kaigai.gr.jp Cc: Sam Mason s...@samason.me.uk; pgsql-hackers@postgresql.org Sent: Monday, July 27, 2009 11:57:32 PM Subject: Re: [HACKERS] SE-PostgreSQL Specifications I revised the SE-PostgreSQL Specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft - Put several external link to introduce something too detail for PostgreSQL documentations. - Paid attention not to use undefined terminology, such as security context, security policy and mandatory access controls. - Revised whole of the composition in the Brief overview section. - Put an example of security policy rule. - SECURITY_LABEL option was replaced by SECURITY_CONTEXT to avoid meaningless confusion. I believe it become better than previous revision. Thanks, KaiGai Kohei wrote: Sam Mason wrote: On Sun, Jul 26, 2009 at 12:27:12PM +0900, KaiGai Kohei wrote: Indeed, the draft used the term of security context with minimum introductions, but not enough friendliness for database folks. The purpose of security context is an identifier of any subject and object to describe them in the security policy. Because the security policy is common for operating system, databases, x-window and others, any managed database objects needs its security context. Anyway, I need to introduce them in the
Re: [HACKERS] SE-PostgreSQL Specifications
On Tuesday 28 July 2009 15:36:29 KaiGai Kohei wrote: Peter Eisentraut wrote: On Sunday 26 July 2009 14:35:41 Sam Mason wrote: I'm coming to the conclusion that you really need to link to external material here; there must be good (and canonical) definitions of these things outside and because SE-PG isn't self contained I really think you need to link to them. This is not supposed to be user documentation. It's supposed to be a feature specification that an implementation can be validated against. Hmm... What kind of descriptions are necessary for a feature specifications? It describes what the feature does and why. Currently, I guess the specification describes when/where the security hook should be invoked, what permission should be checked and what result should be returned for each security hooks. Is it correct? Or, do you expect any other stuffs? That is a pretty good start, but it's drifting into implementation details. Apart from that, user documentation is also necessary. If the specification should be described from completely different viewpoint, I'll provide it. Yes, user documentation will eventually also be necessary, and the original specification may be usable as a source for that. I'm just reacting to those who commented whether or not references should be added and what reference style the documentation uses. We're not there yet. The purpose of this document is to explain what the feature does, not to teach users to use the feature. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Reserve the shared memory region during backend startup on
Magnus Hagander mag...@hagander.net writes: On Mon, Jul 27, 2009 at 16:14, Tom Lanet...@sss.pgh.pa.us wrote: I'm not really insisting on a redesign. I'm talking about the places where the code author appears not to have understood that ERROR means FATAL, because the code keeps plugging on after it anyway. As far as I can see, using ERROR at lines 3630, 3657, 3674, and 3693 is just plain bogus, and changing to LOG there requires no other fixing. But. I'll look into cleaning those up for HEAD anyway, but due to lack of reports I think we should skip backpatch. Reasonable? Fair enough. 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] question about the _SPI_save_plan() and plan cache
Hi, It seems postgres cache the plan under CacheMemoryContext during the plpgsql executing. If there is a function with lots of variables and every one of them got a default value, postgres will allocate lots of memory for caching the default value plan(we have to run the function at least once). Once we DROP the function, the memory consumed by the plan will be leak. The same thing for the compiled function structure(PLpgSQL_function). Is this an oldest known question? Thanks, Tao Ma -- 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: Revise parallel pg_restore's scheduling heuristic
I wrote: So far, all tests have shown no difference in performance based on the patch; My testing to that point had been on a big machine with 16 CPUs and 128 GB RAM and dozens of spindles. Last night I tried with a dual core machine with 4 GB RAM and 5 spindles in RAID 5. Still no difference with the patch. Any suggestions besides the foreign keys? Should 488 FKs be enough to matter here? (Barring better suggestions, I'll try the small machine again tonight with the default configuration, rather than the optimized one.) -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [RFC] new digest datatypes, or generic fixed-len hex types?
On Tue, Jul 28, 2009 at 7:15 AM, Peter Eisentrautpete...@gmx.net wrote: On Monday 27 July 2009 14:50:30 Alvaro Herrera wrote: We've developed some code to implement fixed-length datatypes for well known digest function output (MD5, SHA1 and the various SHA2 types). These types have minimal overhead and are quite complete, including btree and hash opclasses. I think equipping bytea with a length restriction would be a very natural, simple, and useful addition. If we ever want to move the bytea type closer to the SQL standard blob type, this will need to happen anyway. +1 merlin -- 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] question about the _SPI_save_plan() and plan cache
Tao Ma feng_e...@163.com writes: Once we DROP the function, the memory consumed by the plan will be leak. I'm pretty unconcerned about DROP FUNCTION. The case that seems worth worrying about is CREATE OR REPLACE FUNCTION, and in that case we'll reclaim the storage on the next call of the function. 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] Filtering dictionaries support and unaccent dictionary
I'm curious about the pg_regress change ... is it really necessary? To test unaccent dictionary it's needed to input accented characters, not all encodings allow that. UTF8 allows that, but it doesn't compatible with a lot of locales. So, --no-locale should be propagated to CREATE DATABASE command as it's done for encoding. AFAICS the changes to the core code are very small; I wonder if you should commit it separately i.e. without the contrib module, and add the that one in another commit. Split patch to two parts: filter_dictionary-0.1.gz - core changes, including pg_regress changes unaccent-0.5.gz - contrib module Also, I added some comments into code and did cosmetic changes in docs. As for the contrib module, I think it could use a lot more function header comments! Also, it would be great if it could be used separately from tsearch, i.e. that it provided a function unaccent(text) returns text that unaccented arbitrary strings (I guess it would use the default tsconfig). Umm? Module provides unaccent(text) and unaccent(regdictionary, text) functions. -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ unaccent-0.5.gz Description: Unix tar archive filter_dictionary-0.1.gz Description: Unix tar archive -- 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] Higher TOAST compression.
On Tue, Jul 28, 2009 at 2:36 PM, Laurent Labordekerdez...@gmail.com wrote: I'm currently rewriting the whole toaster stuff to simply define : - a compression threshold (size limit to compress, in Nth of page) - an external threshold (size limit to externalize compressed data, in Nth of page) i keep the TOAST_INDEX_TARGET and EXTERN_TUPLES_PER_PAGE. I expect a lot of trial and error as it will my first real patch. http://github.com/ker2x/AkaneSQL/tree/master as usual. Rewritten. All 114 tests passed. I'm testing it on our plateform (currently replicating ~80GB of data via slony, heavy read test will come later). If it works, and if you're interested, i may try to write a patch for 8.5. *hugs* -- Laurent ker2x Laborde Sysadmin @ http://www.over-blog.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] Index-only-scans, indexam API changes
[ thinks a bit ... ] At least for GIST, it is possible that whether data can be regurgitated will vary depending on the selected opclass. Some opclasses use the STORAGE modifier and some don't. I am not sure how hard we want to work to support flexibility there. Would it be sufficient to hard-code the check as pgam says the AM can do it, and the opclass does not have a STORAGE property? Or do we need additional intelligence about GIST opclasses? GiST: btree_gist uses STORAGE option, although original value is accessible from index's tuple. GIN doesn't require setting of STORAGE option even if it's impossible to reconstruct original heap value from indexed value. Right now, only btree_gin's opclasses could be used for index only scans (and only for single-column index scan!). So, STORAGE option could not indicate reconstruct-ability original heap value :(. It seems to me, opclass definition could contain boolean field about that, but with STORAGE option specified it's needed to have separate reconstructing interface method. IMHO, it's too complex for now and it doesn't give significant benefits. Although index-only scan over GIN/GiST could be useful for some aggregates queries like count(*). -- Teodor Sigaev E-mail: teo...@sigaev.ru WWW: http://www.sigaev.ru/ -- 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] multi-threaded pgbench
On Tue, 28 Jul 2009, Josh Williams wrote: Maybe pgbench itself is less of a bottleneck in this environment, relatively speaking? On UNIXish systems, you know you've reached the conditions under which the threaded pgbench would be helpful if the pgbench client program itself is taking up a large percentage of a CPY just by itself. If your test system is still setup, it might be interesting to try the 64 and 128 client cases with Task Manager open, to see what percentage of the CPU the pgbench driver program is using. If the pgbench client isn't already pegged at a full CPU, I wouldn't necessarily threading it to help--it would just add overhead that doesn't buy you anything, which seems to be what you're measuring. All the Linux tests suggest that limit tends up show up at over 20,000 TPS nowawadys, so maybe your Window system is bottlenecking somewhere completely different before it reaches saturation on the client. In any case, Josh's review is exactly what I wanted to see here--the code does compile and run successfully for someone besides its author under Windows. Making it *effective* on that platform might end up being outside the scope of what we want to chew on right now. I'll have updated performance results to submit later this week against the updated patch. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
... btw, where in the SQL spec do you read that PRIMARY KEY constraints can't be deferred? I don't see that. 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] Higher TOAST compression.
Laurent Laborde kerdez...@gmail.com wrote: If it works, and if you're interested, i may try to write a patch for 8.5. Cool! I can help with it if you wish. If you haven't already done so, be sure to read this carefully: http://wiki.postgresql.org/wiki/Developer_FAQ Also, be sure you are taking into account the comments of others on this thread. In particular, there was feedback regarding the level at which to apply this (hard-coded, global but configurable, or column-specific); what feedback we had was in favor of making it configurable by column. If you're not comfortable with doing that part of it, and you can get proof-of-concept benchmarks with a hard-coded global change, that would help convince people that it is worth adding code to support such capabilities at whatever level(s) people agree is best. I've worked in the parser portion before, so that part would be familiar to me if you want help there. Finally, you should probably consider volunteering to review a patch or two for the next commitfest. :-) To ensure timely review of submitted patches, while still allowing the reviewers some guarantee of unencumbered time to write their own patches, there is a development cycle which alternates between coding and reviewing. -Kevin -- 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] question about the _SPI_save_plan() and plan cache
I knew that the delete_function() will reclaim the memory context allocated for the function. But I did not find any code for removing the plan(SPI plan memory context), saved by calling _SPI_save_plan. Is the plan memory context freed when someone issued CREATE OR REPLACE FUNCTION? Thanks. Tom Lane t...@sss.pgh.pa.us wrotes:25310.1248791...@sss.pgh.pa.us... Tao Ma feng_e...@163.com writes: Once we DROP the function, the memory consumed by the plan will be leak. I'm pretty unconcerned about DROP FUNCTION. The case that seems worth worrying about is CREATE OR REPLACE FUNCTION, and in that case we'll reclaim the storage on the next call of the function. 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: to_char, support for EEEE format
On Sat, Jul 25, 2009 at 2:08 AM, Brendan Jurddire...@gmail.com wrote: 2009/7/24 Euler Taveira de Oliveira eu...@timbira.com: Here is my review. The patch applied without problems. The docs and regression tests are included. Both of them worked as expected. Also, you included a fix in RN format, do it in another patch. Well, I updated an error message for RN to keep it consistent with the change I made to the nearby error message. Neither RN or is supported for input, and the error messages were vague on this point (they just said not supported). I understand that separate improvements should be submitted as separate patches, but this is really part of the one improvement. Implementing required improving the error messages, and consistency required that we improve the RN error message also. The behavior is not the same as Oracle. Oracle accepts an invalid scientific notation '999.9'. Will we support it too? I think so. euler=# SELECT to_char(1234.56789, '999.9'); ERRO: invalid format for scientific notation DETALHE: requires exactly one digit before the decimal point. DICA: For example, 9.999 is a valid format. TO_CHAR(1234.56789,'999.9') --- 1.2E+03 *shakes fist at Oracle* yes, I suppose we had better follow suit. The '9.999' format error message is misleading. euler=# select to_char(123, '9.999'); ERRO: cannot use twice Ah, thanks for picking up on this. This was a bug in the original patch. Looks like we forgot to update the formatting keyword for lowercase e. You could include an example in manual too. You could add the two failing cases above in regression tests too. I had already added an example to the manual. Please find attached version 4 of the patch, and incremental diff from version 3. It fixes the bug ( is now accepted as a valid form of ), and lifts the restriction on only having one digit before the decimal point. Euler, Are you going to review this again? We need to know if it is Ready for Committer. ...Robert -- 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] FW: PostGres developer Contact
You sent this message to the list. What you want to do is go and subscribe yourself here: http://mail.postgresql.org/mj/mj_wwwusr/domain=postgresql.org?func=lists-long-fullextra=pgsql-hackers ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Request about pg_terminate_backend()
Hello, Version 8.4 has brought a very useful function : pg_terminate_backend() There have been many reports since years about idle processes remaining on the server while clients are no longer connected. While this may be due to poor application code not closing connections correctly, it does happen that a regular call to close an open connection fails SILENTLY for whatever reason (poor network I/O, router buffers full, p2p on the network). This can impair server resources seriously without being noticed. (I have seen same situation with Oracle too). pg_terminate_backend() allows to explicitly kill a process through a pl/pgsql function in a client application. The following does it somehow rightly : create or replace function suicide() returns void as $$ declare res boolean; p integer; begin select pg_backend_pid() into p; set log_min_error_statement = PANIC; set log_min_messages = PANIC; select pg_terminate_backend(p) into res; set log_min_messages = WARNING; set log_min_error_statement = ERROR; end; $$ language plpgsql security definer; My request is as follows : that superuser privileges be unnecessary to call the function, that when called by an unprivileged user, it does not raise an error situation and that no error is logged. After all, a user is allowed to close his connection. Please consider. Thank you. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Higher TOAST compression.
On Tue, Jul 28, 2009 at 12:44 PM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: Finally, you should probably consider volunteering to review a patch or two for the next commitfest. :-) To ensure timely review of submitted patches, while still allowing the reviewers some guarantee of unencumbered time to write their own patches, there is a development cycle which alternates between coding and reviewing. Yep, I don't want to throw stones at anyone in particular, and particularly not at first-time patch submitters, but there are always more people writing patches than volunteering to review them. This is partially offset by the fact that it usually takes less time to review a patch than it does to write one - however, I know that I have had a difficult time managing this CommitFest, reviewing 2 patches, and keeping on top of the changes requested for the 10 patches I submitted. As a result, one or more of my patches may fall out of this CommitFest, whereas if I had not volunteered to manage to CommitFest, or had not reviewed any patches myself, it would have been much easier. I'm not bent out of shape about it, but it would definitely be nice if everyone could volunteer to take a turn reviewing, especially regular contributors. ...Robert -- 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] Request about pg_terminate_backend()
On Tue, Jul 28, 2009 at 1:19 PM, Saleem EDAH-TALLYnm...@netcourrier.com wrote: Hello, Version 8.4 has brought a very useful function : pg_terminate_backend() There have been many reports since years about idle processes remaining on the server while clients are no longer connected. While this may be due to poor application code not closing connections correctly, it does happen that a regular call to close an open connection fails SILENTLY for whatever reason (poor network I/O, router buffers full, p2p on the network). This can impair server resources seriously without being noticed. (I have seen same situation with Oracle too). pg_terminate_backend() allows to explicitly kill a process through a pl/pgsql function in a client application. The following does it somehow rightly : create or replace function suicide() returns void as $$ declare res boolean; p integer; begin select pg_backend_pid() into p; set log_min_error_statement = PANIC; set log_min_messages = PANIC; select pg_terminate_backend(p) into res; set log_min_messages = WARNING; set log_min_error_statement = ERROR; end; $$ language plpgsql security definer; My request is as follows : that superuser privileges be unnecessary to call the function, that when called by an unprivileged user, it does not raise an error situation and that no error is logged. After all, a user is allowed to close his connection. Well, if someone wants that, they can always write a security definer function, just as you did. But if we made that available to everyone, then we'd get complaints from people who DON'T want it that way, and those people wouldn't have an out. ...Robert -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
... speaking of adding catalog columns, I just discovered that the patch adds searches of pg_depend and pg_constraint to BuildIndexInfo. This seems utterly unacceptable on two grounds: * It's sheer luck that it gets through bootstrap without crashing. Those aren't part of the core set of catalogs that we expect to be accessed by primitive catalog searches. I wouldn't be too surprised if it gets the wrong answer, and the only reason there's not a visible bug is none of the core catalogs have deferrable indexes (so there's no pg_depend entry to be found). * It increases the runtime of BuildIndexInfo by what seems likely to be orders of magnitude (note that get_index_constraint is not a syscacheable operation). Did anyone do any performance checks on this patch, like seeing if pgbench got slower? I think we had better add the deferrability state to pg_index to avoid this. I tried to see if we could dispense with storing the flags in IndexInfo at all, so as not to have to do that. It looks to me like the only place where it's really needed is in ExecInsertIndexTuples: if (is_vacuum || !relationDescs[i]-rd_index-indisunique) uniqueCheck = UNIQUE_CHECK_NO; == else if (indexInfo-ii_Deferrable) uniqueCheck = UNIQUE_CHECK_PARTIAL; else uniqueCheck = UNIQUE_CHECK_YES; Since this code has its hands on the pg_index row already, it definitely doesn't need a copy in IndexInfo if the state is in pg_index. However, I also notice that it doesn't particularly care about the deferrability state in the sense that the triggers use (ie, whether to check at end of statement or end of transaction). What we want to know here is whether to force an old-school immediate uniqueness check in the index AM. So I'm thinking that we only need one bool added to pg_index, not two. And I'm further thinking about intentionally calling it something other than deferred, to emphasize that the semantics aren't quite like regular constraint deferral. Maybe invert the sense and call it immediate. Comments? 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] system timezone regression failure
Building 8.4 on a client's system, I get a regression failure apparently due to some difference between the system's timezone DB and what out regression tests expect, as shown below. I'm wondering if we should not disable the timestamptz regression test when we configure with the system timezone db. I guess i could patch the SRPM by providing an alternative result file - not sure if it's worth it though. cheers andrew *** /usr/local/src/rpms/BUILD/postgresql-8.4.0/src/test/regress/expected/timestamptz.out 2009-03-21 21:12:32.0 -0400 --- /usr/local/src/rpms/BUILD/postgresql-8.4.0/src/test/regress/results/timestamptz.out 2009-07-28 12:33:15.0 -0400 *** *** 153,159 SELECT '20500710 173201 Europe/Helsinki'::timestamptz; -- DST timestamptz -- ! Sun Jul 10 07:32:01 2050 PDT (1 row) SELECT '20500110 173201 Europe/Helsinki'::timestamptz; -- non-DST --- 153,159 SELECT '20500710 173201 Europe/Helsinki'::timestamptz; -- DST timestamptz -- ! Sun Jul 10 07:32:01 2050 PST (1 row) SELECT '20500110 173201 Europe/Helsinki'::timestamptz; -- non-DST *** *** 165,171 SELECT '205000-07-10 17:32:01 Europe/Helsinki'::timestamptz; -- DST timestamptz ! Thu Jul 10 07:32:01 205000 PDT (1 row) SELECT '205000-01-10 17:32:01 Europe/Helsinki'::timestamptz; -- non-DST --- 165,171 SELECT '205000-07-10 17:32:01 Europe/Helsinki'::timestamptz; -- DST timestamptz ! Thu Jul 10 07:32:01 205000 PST (1 row) SELECT '205000-01-10 17:32:01 Europe/Helsinki'::timestamptz; -- non-DST == -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
On Tue, 2009-07-28 at 13:41 -0400, Tom Lane wrote: * It's sheer luck that it gets through bootstrap without crashing. Those aren't part of the core set of catalogs that we expect to be accessed by primitive catalog searches. I wouldn't be too surprised if it gets the wrong answer, and the only reason there's not a visible bug is none of the core catalogs have deferrable indexes (so there's no pg_depend entry to be found). * It increases the runtime of BuildIndexInfo by what seems likely to be orders of magnitude (note that get_index_constraint is not a syscacheable operation). Did anyone do any performance checks on this patch, like seeing if pgbench got slower? Not I. I didn't notice anything that made me think it would slow down in the default case, but I'll be more careful in the future. This also impacts my patch. After moving the constraint information from pg_index (where my patch currently has it) to pg_constraint, I will also need access to the index and the constraint at the same time. Perhaps this can all be handled more like CHECK constraints, storing this information in the ResultRelInfo? I think we had better add the deferrability state to pg_index to avoid this. This might make it difficult to allow multiple constraints to use the same index. We might have to make some kind of rules about when two constraints can share an index, and when they can't. I also notice that it doesn't particularly care about the deferrability state in the sense that the triggers use (ie, whether to check at end of statement or end of transaction). What we want to know here is whether to force an old-school immediate uniqueness check in the index AM. [...] And I'm further thinking about intentionally calling it something other than deferred, to emphasize that the semantics aren't quite like regular constraint deferral. Maybe invert the sense and call it immediate. Comments? I'm trying to figure out how this fits with the generalized index constraints idea. We may want the generalized index constraints to have the same immediate behavior, but that doesn't have much to do with the index. Regards, Jeff Davis -- 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] system timezone regression failure
Andrew Dunstan and...@dunslane.net writes: Building 8.4 on a client's system, I get a regression failure apparently due to some difference between the system's timezone DB and what out regression tests expect, as shown below. Those regression tests were *intentionally* set up to fail if someone's TZ support is not Y2038 clean. This is not a bug. Advise your client to get some less-obsolete timezone data files; or don't depend on the system TZ database. (The only reason why you should do so is if it's being kept up to date, eh?) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
Jeff Davis pg...@j-davis.com writes: On Tue, 2009-07-28 at 13:41 -0400, Tom Lane wrote: I think we had better add the deferrability state to pg_index to avoid this. This might make it difficult to allow multiple constraints to use the same index. Huh? That hardly seems possible anyway, if some of them want deferred checks and others do not. I'm trying to figure out how this fits with the generalized index constraints idea. We may want the generalized index constraints to have the same immediate behavior, but that doesn't have much to do with the index. Sure it does. Whether the check is immediate must be considered a property of the index itself. Any checking you do later could be per-constraint, but the index is either going to fail at insert or not. 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] When is a record NULL?
On Jul 27, 2009, at 5:19 PM, David E. Wheeler wrote: Yep, that's just what I needed, thanks. I think I'll send a patch for the Cursors section of the PL/pgSQL documentation that mentions this. Would have saved me a bunch of hassle. So would have reading two more sentences of the docs, which state, to my surprise (that I missed it!), “As with `SELECT INTO`, the special variable codeFOUND/code can be checked to see whether a row was obtained or not.” D’oh! So now I've got things working the way I need, and I've blogged about these SQL pathologies so that I don't forget. http://www.justatheory.com/computers/databases/postgresql/neither-null-nor-not-null.html Thanks for the help, everyone. 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] system timezone regression failure
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Building 8.4 on a client's system, I get a regression failure apparently due to some difference between the system's timezone DB and what out regression tests expect, as shown below. Those regression tests were *intentionally* set up to fail if someone's TZ support is not Y2038 clean. This is not a bug. Advise your client to get some less-obsolete timezone data files; or don't depend on the system TZ database. (The only reason why you should do so is if it's being kept up to date, eh?) Oh, you're right, I misread the diffs. The client is getting the machines updated. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] xpath not a good replacement for xpath_string
Sorry to bring this up, I know you've been fighting about XML for a while. Currently, I am using XML2 functionality and have tried to get the newer XPath function to work similarly, but can't quite seem to do it. I think the current xpath function is too limited. (The docs said to post problems to hackers if I have an issue.) For instance, we have a web application that uses java with an XML class serializer/deserializer Xstream. It creates XML that looks like this: com.company.local.myclass uuidb5212259-a91f-4dca-a547-4fe89cf2f32c/uuid emailj...@somedomain.com/email /com.company.local.myclass My current strategy is to use xml2 as: select xpath_string(datum, E'/com\.company\.local\.myclass/uuid) as uuid from table; Which produces a usable: b5212259-a91f-4dca-a547-4fe89cf2f32c I have been trying to use xpath select xpath(E'/com\.company\.local\.myclass/uuid', XMLPARSE(CONTENT datum)) as uuid from table; Which produces an unusable: {uuidb5212259-a91f-4dca-a547-4fe89cf2f32c/uuid} -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
On Tue, 2009-07-28 at 15:15 -0400, Tom Lane wrote: This might make it difficult to allow multiple constraints to use the same index. Huh? That hardly seems possible anyway, if some of them want deferred checks and others do not. I don't see why it's completely impossible. You could have: * non-overlapping, deferred * not completely contained in, fail-early behavior Probably not worth supporting, though. Sure it does. Whether the check is immediate must be considered a property of the index itself. Any checking you do later could be per-constraint, but the index is either going to fail at insert or not. My point is that the immediate behavior does not require the index itself to fail early. My original patch for generalized index constraints has the same behavior as UNIQUE currently does (including the fail early behavior), but can be used over indexes that know nothing about UNIQUE (list GiST). Regards, Jeff Davis -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
Jeff Davis pg...@j-davis.com writes: On Tue, 2009-07-28 at 15:15 -0400, Tom Lane wrote: Sure it does. Whether the check is immediate must be considered a property of the index itself. Any checking you do later could be per-constraint, but the index is either going to fail at insert or not. My point is that the immediate behavior does not require the index itself to fail early. My original patch for generalized index constraints has the same behavior as UNIQUE currently does (including the fail early behavior), but can be used over indexes that know nothing about UNIQUE (list GiST). Fail-early still sounds like a property of the index. Whether the property is implemented inside or outside the index AM isn't very relevant. Partial and functional index support are outside the AM, for example, but we have no problem representing those features in pg_index. In any case, this can be redesigned as needed when and if your other patch gets to the point of being ready for consideration. 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] xpath not a good replacement for xpath_string
On Tue, Jul 28, 2009 at 3:21 PM, pg...@mohawksoft.com wrote: Sorry to bring this up, I know you've been fighting about XML for a while. Currently, I am using XML2 functionality and have tried to get the newer XPath function to work similarly, but can't quite seem to do it. I think the current xpath function is too limited. (The docs said to post problems to hackers if I have an issue.) For instance, we have a web application that uses java with an XML class serializer/deserializer Xstream. It creates XML that looks like this: com.company.local.myclass uuidb5212259-a91f-4dca-a547-4fe89cf2f32c/uuid emailj...@somedomain.com/email /com.company.local.myclass My current strategy is to use xml2 as: select xpath_string(datum, E'/com\.company\.local\.myclass/uuid) as uuid from table; Which produces a usable: b5212259-a91f-4dca-a547-4fe89cf2f32c I have been trying to use xpath select xpath(E'/com\.company\.local\.myclass/uuid', XMLPARSE(CONTENT datum)) as uuid from table; Which produces an unusable: {uuidb5212259-a91f-4dca-a547-4fe89cf2f32c/uuid} How about: SELECT (xpath(E'/com\.company\.local\.myclass/uuid/text()', XMLPARSE(CONTENT datum)))[1] as uuid from table; Not as clean, but it produces the same result as xpath_string(). Combined with array_to_string() could can collapse the array instead of just grabbing the first element (in cases other than uuid, of course). -- Mike Rylander | VP, Research and Design | Equinox Software, Inc. / The Evergreen Experts | phone: 1-877-OPEN-ILS (673-6457) | email: mi...@esilibrary.com | web: http://www.esilibrary.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] xpath not a good replacement for xpath_string
pg...@mohawksoft.com wrote: Sorry to bring this up, I know you've been fighting about XML for a while. Currently, I am using XML2 functionality and have tried to get the newer XPath function to work similarly, but can't quite seem to do it. I think the current xpath function is too limited. (The docs said to post problems to hackers if I have an issue.) For instance, we have a web application that uses java with an XML class serializer/deserializer Xstream. It creates XML that looks like this: com.company.local.myclass uuidb5212259-a91f-4dca-a547-4fe89cf2f32c/uuid emailj...@somedomain.com/email /com.company.local.myclass My current strategy is to use xml2 as: select xpath_string(datum, E'/com\.company\.local\.myclass/uuid) as uuid from table; Which produces a usable: b5212259-a91f-4dca-a547-4fe89cf2f32c I have been trying to use xpath select xpath(E'/com\.company\.local\.myclass/uuid', XMLPARSE(CONTENT datum)) as uuid from table; Which produces an unusable: {uuidb5212259-a91f-4dca-a547-4fe89cf2f32c/uuid} This is really a usage question, which doesn't belong on -hackers. However, here is how to do what you want: andrew=# select (xpath($$/com.company.local.myclass/uuid/text()$$,$$com.company.local.myclass uuidb5212259-a91f-4dca-a547-4fe89cf2f32c/uuid emailj...@somedomain.com/email /com.company.local.myclass $$::xml))[1]::text as uuid; uuid -- b5212259-a91f-4dca-a547-4fe89cf2f32c (1 row) cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xpath not a good replacement for xpath_string
Andrew Dunstan and...@dunslane.net wrote: This is really a usage question, which doesn't belong on -hackers. Perhaps this sentence in the 8.4.0 docs should be amended or removed?: If you find that some of the functionality of this module is not available in an adequate form with the newer API, please explain your issue to pgsql-hackers@postgresql.org so that the deficiency can be addressed. http://www.postgresql.org/docs/8.4/interactive/xml2.html#AEN115231 -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Deferred uniqueness versus foreign keys
[sigh, forgot to cc hackers the first time ] Foreign key behavior is only sane if the referenced column(s) are unique. With the proposed patch, it is possible that the uniqueness check on the referenced columns is deferred, which means it might not occur till after an FK check does. Discuss. 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] xpath not a good replacement for xpath_string
Kevin Grittner wrote: Andrew Dunstan and...@dunslane.net wrote: This is really a usage question, which doesn't belong on -hackers. Perhaps this sentence in the 8.4.0 docs should be amended or removed?: If you find that some of the functionality of this module is not available in an adequate form with the newer API, please explain your issue to pgsql-hackers@postgresql.org so that the deficiency can be addressed. http://www.postgresql.org/docs/8.4/interactive/xml2.html#AEN115231 Well, yes, maybe I should withdraw my comment, although in fact the desired functionality is present, as both Mike Rylander and I noted. You just need to use the text() function to get the contents of the node, and an array subscript to pull it out of the result array. The really annoying thing we are missing is not xpath functionality, but XSLT processing. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] xpath not a good replacement for xpath_string
Andrew Dunstan and...@dunslane.net wrote: in fact the desired functionality is present [...] You just need to use the text() function to get the contents of the node, and an array subscript to pull it out of the result array. I just took a quick look, and that didn't jump out at me from the documentation. Perhaps there should be an example or two of how to get the equivalent functionality through the newer standard API, for those looking to migrate? Would it make sense to supply convenience SQL functions which map some of the old API to the new? -Kevin -- 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] xpath not a good replacement for xpath_string
Andrew Dunstan and...@dunslane.net wrote: in fact the desired functionality is present [...] You just need to use the text() function to get the contents of the node, and an array subscript to pull it out of the result array. I just took a quick look, and that didn't jump out at me from the documentation. Perhaps there should be an example or two of how to get the equivalent functionality through the newer standard API, for those looking to migrate? Would it make sense to supply convenience SQL functions which map some of the old API to the new? The thing that perplexed me was that it was not obvious from the docs how, exactly, to get the functionality that was simple and straight forward in XML2. Another thing that is troubling is that more exotic types do not seem to be supported at all. For instance, in my example I used uuid, and if one substitutes uuid() for text() that doesn't work. The API is less intuitive than the previous incarnation and is, indeed, more difficult to use. -Kevin -- 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
[HACKERS] plpgsql: support identif%TYPE[], (from ToDo)
Hello I would to solve some points from ToDo. I began with TYPE [] support. I thing, so this should be relative simple, but there are one issue. There are syntax for declare array from scalar type - create or replace function x(a int) returns ... as $$ declare f a%type[] -- begin ... but there are not syntax for inversion - scalar from array. Theoretically we could to define variable with same type (array) and everywhere work with first element. Or we should to define some syntax: My first idea is using word element: create or replace function x(a int[]) ... declare f a%element; begin ... any ideas? 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] WIP: to_char, support for EEEE format
Brendan Jurd escreveu: Please find attached version 4 of the patch, and incremental diff from version 3. It fixes the bug ( is now accepted as a valid form of ), and lifts the restriction on only having one digit before the decimal point. Looks better but I did some tests and caught some strange behaviors. SQL SELECT to_char(1234.56789, '8.999') FROM DUAL; SELECT to_char(1234.56789, '8.999') FROM DUAL ERROR at line 1: ORA-01481: invalid number format model SQL SELECT to_char(1234.56789, '9.080') FROM DUAL; SELECT to_char(1234.56789, '9.080') FROM DUAL ERROR at line 1: ORA-01481: invalid number format model This is not a problem with your patch but something that needs to be fixed in PostgreSQL to match Oracle behavior. The following example should emit an error. IMHO, filling the string with # is very strange. TODO? euler=# SELECT to_char(1234.56789, '9.080'); to_char - #.#8# (1 row) Couldn't the following code be put inside switch clause? If not, you should add a comment why the validation is outside switch. + if (IS_(num) n-key-id != NUM_E) + { + NUM_cache_remove(last_NUMCacheEntry); + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), +errmsg(\\ must be the last pattern used))); + } + switch (n-key-id) { case NUM_9: Oracle has a diferent overflow limit [1] but I think we could stay with the PostgreSQL one. But the #. is not the intended behavior. IIRC you're limited to 99 exponent. SQL SELECT to_char(1.234567E+308, '9.999'); SELECT to_char(1.234567E+308, '9.999') ERROR at line 1: ORA-01426: numeric overflow euler=# SELECT to_char(1.234567E+308, '9.999'); to_char --- #.### (1 row) The problem is in numeric_to_char() and float8_to_char(). You could fix it with the following code. Besides that I think you should comment why '5' or '6' in the other *_to_char() functions. + /* 6 means '.' (decimal point), 'E', '+', and 3 exponent digits */ + if (isnan(value) || is_infinite(value) || + len Num.pre + Num.post + 6) + { + numstr = (char *) palloc(Num.pre + Num.post + 7); + fill_str(numstr, '#', Num.pre + Num.post + 6); + *(numstr + Num.pre) = '.'; + } I can't see more problems in your patch. When you fix it, it'll be ready for a committer. [1] http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#sthref80 -- Euler Taveira de Oliveira http://www.timbira.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] Deferred uniqueness versus foreign keys
2009/7/28 Tom Lane t...@sss.pgh.pa.us: [sigh, forgot to cc hackers the first time ] Foreign key behavior is only sane if the referenced column(s) are unique. With the proposed patch, it is possible that the uniqueness check on the referenced columns is deferred, which means it might not occur till after an FK check does. Discuss. regards, tom lane Hmm, yes, looking in the SQL spec, I've just noticed this under 11.8, referential constraint definition: The table constraint descriptor describing the unique constraint definition whose unique column list identifies the referenced columns shall indicate that the unique constraint is not deferrable. which seems like a sensible policy now that I think about it. - Dean -- 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] plpgsql: support identif%TYPE[], (from ToDo)
On Tue, Jul 28, 2009 at 10:53:08PM +0200, Pavel Stehule wrote: Hello I would to solve some points from ToDo. I began with TYPE [] support. I thing, so this should be relative simple, but there are one issue. snip My first idea is using word element: create or replace function x(a int[]) ... declare f a%element; begin ... I would have thought: declare f a[0]%type; as in: the type of a[0]. Perhaps a[]%type but that seems less natural. Have a nice day, -- Martijn van Oosterhout klep...@svana.org http://svana.org/kleptog/ Please line up in a tree and maintain the heap invariant while boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [HACKERS] Deferred uniqueness versus foreign keys
On Tue, 2009-07-28 at 22:10 +0100, Dean Rasheed wrote: Hmm, yes, looking in the SQL spec, I've just noticed this under 11.8, referential constraint definition: The table constraint descriptor describing the unique constraint definition whose unique column list identifies the referenced columns shall indicate that the unique constraint is not deferrable. which seems like a sensible policy now that I think about it. Is it a problem to allow unique constraints to be deferrable until the end of the command though? To meet the spec, SET i = i + 1 should work even if i is referenced by a FK. Regards, Jeff Davis -- 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] Deferred uniqueness versus foreign keys
Jeff Davis pg...@j-davis.com writes: Is it a problem to allow unique constraints to be deferrable until the end of the command though? Yes. If you do have a case where this matters, the command updating the referenced table is most likely different from the one updating the referencing table, and so the command boundaries aren't the same. I think what we can do for now is insist that the FK's unique constraint be immediate-mode. Trying to relax that looks like a research project to me. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Deferrable unique constraints
Another thought on the index AM API issues: after poking through the code I realized that there is *nobody* paying any attention to the existing bool result of aminsert() (ie, did we insert anything or not). So I think that instead of adding a bool* parameter, we should repurpose the function result, along the lines of this spec: para The method's boolean result value is significant only when literalcheckUnique/ is literalUNIQUE_CHECK_PARTIAL/. In this case a TRUE result means the new entry is known unique, whereas FALSE means it might be non-unique (and a deferred uniqueness check must be scheduled). For other cases a constant FALSE result is recommended. /para para For non-unique indexes, it is not required that functionaminsert/ do anything; it might for instance refuse to index NULLs. /para The bool* parameter is fairly ugly in a couple of ways: it's not clear when it's okay to pass a NULL pointer, and the compiler doesn't give you a lot of help in being sure you've set the result in all code paths. So I'd rather not use one if we don't have to. 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] xpath not a good replacement for xpath_string
On Tuesday, July 28, 2009, pg...@mohawksoft.com wrote: Andrew Dunstan and...@dunslane.net wrote: in fact the desired functionality is present [...] You just need to use the text() function to get the contents of the node, and an array subscript to pull it out of the result array. I just took a quick look, and that didn't jump out at me from the documentation. Perhaps there should be an example or two of how to get the equivalent functionality through the newer standard API, for those looking to migrate? Would it make sense to supply convenience SQL functions which map some of the old API to the new? The thing that perplexed me was that it was not obvious from the docs how, exactly, to get the functionality that was simple and straight forward in XML2. Another thing that is troubling is that more exotic types do not seem to be supported at all. For instance, in my example I used uuid, and if one substitutes uuid() for text() that doesn't work. text() is an XPath function used to extract the text content of a node, in this case the uuid element. The API is less intuitive than the previous incarnation and is, indeed, more difficult to use. It may be easier to use for those not familiar with more advanced XPath, but it also has non-standard default actions. That being said, I'd love to see wrapper functions that provide the older api but leverage the core code. --miker -- Mike Rylander | VP, Research and Design | Equinox Software, Inc. / The Evergreen Experts | phone: 1-877-OPEN-ILS (673-6457) | email: mi...@esilibrary.com | web: http://www.esilibrary.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] SE-PostgreSQL Specifications
Greg Williamson wrote: Thanks for the updates. I might suggest a couple of small changes: a) a section that explains comments like This is not supported in the initial version -- do you mean in the first Beta release of SE-PostgreSQL, or not in the initial release(s) for commitfests ? If it is not supported why mention it ? If experienced users of SELinux expect it, they might look for an explanation as to why it is missing and when it might appear. I'm not sure if postgres DB hackers would care if is it is not to be included. How much do these compromise the design, and if so, are there specific plans for implementing them ? The This is not supported ... comment means this feature is not included within the initial patch submitted to the next commit fest, so the corresponding section is not also included within the documentation patch at that time. However, I expect all the features and corresponding user documents should be included within the stable v8.5 release. So, I also described these sections with notations. b) something which explains the differences between SELinux and SEPostgreSQL on the one hand (for SE fans). You've done a good job of outlining the differences and similarities with the more standard ACL methods and that needs to be kept prominent so people with DB experience can see the differences. I am all in favor of external links if you can find good explanation of concepts elsewhere. This is a very high level outline and so I'd be tempted to move all implementation details to another page -- basically everything from Installation on, with the exception of the overview of the dump issues, is (to my eye) a detail that doesn't need exposing (yet). At first, what should be included within the PostgreSQL official documentation? I don't think here is any opposition to include - Steps to installations - Enhancement in SQL statement - External links something like: It provides mandatory access controls. See the http://. for details. Needs to be discussed - Securtiy model overview, such as security context, security policy and so on. - Feature overview, such as example of access controls. - References to object classes and permissions. (Basically, it is not necessary for end users.) If we prepare a comprehensive external documentation, one idea is to revise the series of wikipages at: http://wiki.postgresql.org/wiki/SEPostgreSQL Thanks, I'll send mail when I have a few paragraphs done so you can check it and see if you approve. Apologies for top-posting -- lame mailer. Greg W. - Original Message From: KaiGai Kohei kai...@ak.jp.nec.com To: KaiGai Kohei kai...@kaigai.gr.jp Cc: Sam Mason s...@samason.me.uk; pgsql-hackers@postgresql.org Sent: Monday, July 27, 2009 11:57:32 PM Subject: Re: [HACKERS] SE-PostgreSQL Specifications I revised the SE-PostgreSQL Specifications: http://wiki.postgresql.org/wiki/SEPostgreSQL_Draft - Put several external link to introduce something too detail for PostgreSQL documentations. - Paid attention not to use undefined terminology, such as security context, security policy and mandatory access controls. - Revised whole of the composition in the Brief overview section. - Put an example of security policy rule. - SECURITY_LABEL option was replaced by SECURITY_CONTEXT to avoid meaningless confusion. I believe it become better than previous revision. Thanks, KaiGai Kohei wrote: Sam Mason wrote: On Sun, Jul 26, 2009 at 12:27:12PM +0900, KaiGai Kohei wrote: Indeed, the draft used the term of security context with minimum introductions, but not enough friendliness for database folks. The purpose of security context is an identifier of any subject and object to describe them in the security policy. Because the security policy is common for operating system, databases, x-window and others, any managed database objects needs its security context. Anyway, I need to introduce them in the security model section. I'm coming to the conclusion that you really need to link to external material here; there must be good (and canonical) definitions of these things outside and because SE-PG isn't self contained I really think you need to link to them. This will be somewhat of a break from normal PG documentation because so far everything has been self contained, it's chosen its own interpretation of the SQL standard and it needs to document that. SE-PG will be interacting with much more code from outside and showing which parts of these are PG specific vs. which parts are common to all SELinux seems important. If you try to document *everything* you're going to be writing for years and give the impression that everything is implemented in SE-PG. A dividing line needs to be drawn between what is PG specific and what is SELinux (why not SEL?). It also seems to me reasonable suggestion. However, a reasonable amount (which
Re: [HACKERS] Review: Revise parallel pg_restore's scheduling heuristic
On Tue, Jul 28, 2009 at 10:28 AM, Kevin Grittnerkevin.gritt...@wicourts.gov wrote: I wrote: So far, all tests have shown no difference in performance based on the patch; My testing to that point had been on a big machine with 16 CPUs and 128 GB RAM and dozens of spindles. Last night I tried with a dual core machine with 4 GB RAM and 5 spindles in RAID 5. Still no difference with the patch. Any suggestions besides the foreign keys? Should 488 FKs be enough to matter here? (Barring better suggestions, I'll try the small machine again tonight with the default configuration, rather than the optimized one.) The other possibility here is that this just doesn't work. :-) ...Robert -- 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: Revise parallel pg_restore's scheduling heuristic
Robert Haas robertmh...@gmail.com writes: The other possibility here is that this just doesn't work. :-) That's why we wanted to test it ;-). I don't have time to look right now, but ISTM the original discussion that led to making that patch had ideas about scenarios where it would be faster. It'd be worth digging that up and seeing if the current tests covered the case or not. 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] xpath not a good replacement for xpath_string
pg...@mohawksoft.com wrote: Another thing that is troubling is that more exotic types do not seem to be supported at all. For instance, in my example I used uuid, and if one substitutes uuid() for text() that doesn't work. text() is an XPath function, with well defined semantics that have no relation at all to types in a PostgreSQL sense. uuid() of course has no meaning in an XPath expression. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] multi-threaded pgbench
On Tue, 2009-07-28 at 12:10 -0400, Greg Smith wrote: If your test system is still setup, it might be interesting to try the 64 and 128 client cases with Task Manager open, to see what percentage of the CPU the pgbench driver program is using. If the pgbench client isn't already pegged at a full CPU, I wouldn't necessarily threading it to help--it would just add overhead that doesn't buy you anything, which seems to be what you're measuring. That's a really good point, I do recall seeing pgbench taking only a fraction of the CPU... Running it again, it hovers around 6 or 7 percent in both cases, so it's only using up around half a core. Huh, running the patched version on a single thread with 128 clients just got it to crash. Actually consistently, three times now. Will try the same thing on the development box tomorrow morning to get some better debugging information. All the Linux tests suggest that limit tends up show up at over 20,000 TPS nowawadys, so maybe your Window system is bottlenecking somewhere completely different before it reaches saturation on the client. I figured it was just indicating a limitation of the environment, where Windows has some kind of inefficiency either in the PG port or just something inherent in how the OS works. It does make me wonder where exactly all that CPU time is going, though. OProfile, how I miss thee. But that's a different discussion entirely. - Josh Williams -- 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] plpgsql: support identif%TYPE[], (from ToDo)
Pavel Stehule pavel.steh...@gmail.com writes: I would to solve some points from ToDo. I began with TYPE [] support. plpgsql's %type support is a crock that's going to have to be rewritten from the ground up as soon as we consolidate the lexer with the core. I wouldn't suggest spending any time in that area now. 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] plpgsql: support identif%TYPE[], (from ToDo)
2009/7/29 Tom Lane t...@sss.pgh.pa.us: Pavel Stehule pavel.steh...@gmail.com writes: I would to solve some points from ToDo. I began with TYPE [] support. plpgsql's %type support is a crock that's going to have to be rewritten from the ground up as soon as we consolidate the lexer with the core. I wouldn't suggest spending any time in that area now. ook Pavel 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