Re: [HACKERS] Sampling profiler updated
Hi, Le 21 juil. 09 à 07:57, Itagaki Takahiro a écrit : Oops, I must fix it. I didn't test well the default stack depth (10). I'd better not have limitation of condition stack. I'm glad to hear it's possible to implement without arbitrary limits :) BTW, I hope I have enough feedbacks from reviewers if the patch is Returned with Feedback. Are there any issues I need to fix or improve by the next commitfest? I feel we don't have enough discussion about the feature, like: * Is is useful enough? or are there any idea to be more useful? It looks very useful but I didn't have time to play around enough with it (stopped at warnings, which were very early in testing). It seems not possible to reset the profiles then launch a query and see stats for only this query run? (all backends will be profiled together). Knowing what sort of workload (very detailed here, which is good) is running is good though, I think I'm going to use it when available :) * Is it ok we have two versions of profiling? (this and dtrace probes) Can't comment on this, never used dtrace before, don't have a version of it on my production systems. * Is the quality of the patch enough in terms of implmentation? I've raised some questions related on performance impact of function calls when profiling is disabled and the code changes related to how to take some locks, I didn't have more comments than that (didn't spot other comments to get done). Regards, -- dim -- 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] visibility maps and heap_prune
On Tue, Jul 21, 2009 at 10:38 AM, Robert Haasrobertmh...@gmail.com wrote: Pavan, are you planning to respond to Alex's comments and/or update this patch? Yes, I will. Hopefully by end of this week. Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Robert Haas wrote: 2009/7/20 KaiGai Kohei kai...@ak.jp.nec.com: Robert Haas wrote: - row-level security - complex DDL permissions Is the complex DDL permissions mean something like db_xxx:{create}, db_xxx:{relabelfrom relabelto} and others? If so, I can agree to implement these checks at the later patch. However, please note that the initial patch cannot achieve actual security in this case, because it means anyone can change security label of objects. I'm not qualified to answer this question, and that's exactly why we need more documentation of what this patch tries to do and why (i.e. a spec). Agreed, What I was specifically referring to is things like db_column:{drop}, which are much more specific than anything PostgreSQL currently offers to control DDL. I think we should make an attempt to get objects labelled in a reasonable way (I did not like the approach your latest patch took of just assigning everything a default label), which might include relabelling also, but certainly doesn't include things like distinguishing between different kinds of DDL operations. I'm not really up on SELinux terminology (this is another thing that needs to be covered in the documentation, and isn't) but maybe something like db_table:{owner}. It also should be described in the documentation again... The current design reflects a fundamental principle in SELinux. It requires how objects are labeled and what permission should be checked on the creation/deletion time. It seems to me you misunderstand the default security context is uniform for all the clients, but it's incorrect. The default security context depends on client's security context and parent object's security context (if exists). For example, when a user with classified security context creates a table under the regular schema, the new table will be also labeled as classified to prevent accesses from unclassified users. All the object classes that we can create or delete (such as files, sockets, ipc objects, x-windows, ...) have their permissions to be checked on creation or deletion time. These permissions are defined on the relationship between user's security context and object's security context. If only database objects ignore the design, it breaks consistency in the security policy. Again, the fact I needed to introduce such a fundamental design in SELinux shows the current documents are poor for native English users and database experts but not for security. I also agree with Peter's contention that a spec would be useful. If you could read a clear description of what the patch was going to do, then you could separate the problem of figuring out whether that was the right thing from the question of whether the patch actually did it. I can also agree with the suggestion. The specifications (from viewpoint of the developer) will introduces the fundamental principles to be implemented, and it will figure out what implementation is better. As I noted before, I've been flexible about how SE-PgSQL is implemented as far as it can perform SELinux's security model correctly. Please for a several days. I'll describe it. I really, really think you need to find someone to help you with the documentation. As I've said before, your English is a lot better than my Japanese, but the current documentation is just hard to read. More than that, writing good documentation is HARD, and there are not a ton of people who can do it well. It seems to me that the documentation for this project could require as much work as the actual code. Agreed, If possible, I would like to know what is the easy-understandable documentation from the viewpoint of non-security-experts also. What I want for documentation of this feature is something like the Database Roles and Privileges chapter of the current documentation. You can read this chapter from beginning to end and have a pretty good idea how to manage permissions in PostgreSQL. SE-Linux is complex enough that you might have to refer to other sources of information for certain topics (e.g. policy writing), because a full overview of those topics might exceed the scope of our documentation. But it should still be possible to start knowing nothing, read the chapter, and have a pretty good idea how all the pieces fit together, at least as far as PostgreSQL is concerned. Indeed, I've learned before it is a good composition of documentations to compare similar two different features (one is well-known, the other is new) to introduce a new concept. Here is one idea. I'll upload the draft of the documentation on the wikipage shorter than the current one. Is somebody available to check it from the viewpoint of native English user or database users? 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:
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
KaiGai Kohei asked: ... Here is one idea. I'll upload the draft of the documentation on the wikipage shorter than the current one. Is somebody available to check it from the viewpoint of native English user or database users? I'll give a shot ... native english speaker, some experience with documentation, and I'm fond of postgres, but not really deserving of being on this alias (not a hacker in the useful sense of the word). Greg Williamson -- 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] SE-PgSQL/tiny rev.2193
Greg Williamson wrote: KaiGai Kohei asked: ... Here is one idea. I'll upload the draft of the documentation on the wikipage shorter than the current one. Is somebody available to check it from the viewpoint of native English user or database users? I'll give a shot ... native english speaker, some experience with documentation, and I'm fond of postgres, but not really deserving of being on this alias (not a hacker in the useful sense of the word). Greg Williamson Thanks for your help. I'm now under writing the initial draft, seeing the Database Roles and Privileges chapter. Please wait for a few days. -- 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] Sampling profiler updated
On Tuesday 21 July 2009 09:09:54 Dimitri Fontaine wrote: * Is it ok we have two versions of profiling? (this and dtrace probes) Can't comment on this, never used dtrace before, don't have a version of it on my production systems. Well, the objection remains: We already have dtrace support, and dtrace or dtrace-like systems are spreading to many operating systems, so one wonders whether it is useful to clutter the code with another probing system instead of putting some resources, say, into getting systemtap up to speed. -- 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] git revisited
On Monday 20 July 2009 23:17:30 Andrew Dunstan wrote: I'm not sure where we got to with doing some surgery on the CVS repo so that we can replicate all the tags and branches properly. Has someone fully identified what needs to be fixed so we can have all the tags? I think this depends on which tool we would end up using to do the final conversion. Each tool has its own quirks. -- 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] SE-PgSQL/tiny rev.2193
On Tue, Jul 21, 2009 at 5:51 AM, Robert Haasrobertmh...@gmail.com wrote: I really, really think you need to find someone to help you with the documentation. As I've said before, your English is a lot better than my Japanese, but the current documentation is just hard to read. In general we're very generous with English writing quality. I wouldn't worry too much about the language barrier as far as writing documentation. However the snippets Robert posted of the documentation had a more fundamental problem unrelated to language. The problem is that it explained what an option did in terms of what that option did -- ie, the documentation only made sense if you already knew what it was trying to say. That's not an unusual trap to get into when writing documentation regardless of what language you're writing in. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Duplicate key value error
Em Tue, 21 Jul 2009 02:07:47 -0300, Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp escreveu: I modified the format logic to use StringInfo and don't cut off the message in 512 bytes. Key names and values will be never into '...'. I changed both both report_unique_violation() and ri_ReportViolation(). Hi Takahiro! Hum, for key names ok, but for values, wouldn't this worse the output when it is greater than 512 bytes? -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br http://www.rnp.br/keyserver/pks/lookup?search=0x8F3E3C06D428D10A -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore --clean vs. large object
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: Jaime Casanova jcasa...@systemguards.com.ec wrote: i think this one could be applied, just as is... there is no need for docs, because the issue being fixed is not documented... maybe that should be in doc of older releases? Sure, it was an undocumented behavior. Should we need to add details of this patch to documentation? The release note entry will be sufficient I think. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Sorry for jumping late into this discussion, but I've been out sailing for a couple of weeks and stayed away from email :-) On Mon, Jun 29, 2009 at 21:01, Stephen Frostsfr...@snowman.net wrote: * Lars Kanis (ka...@comcard.de) wrote: The problem I have, is that I want to use an ordinary windows application, which connects to an arbitrary ODBC data source. This application stores a fixed username und password for the connection within it's own binary data file. It doesn't know anything about TLS-connection nor smartcard based authentication. All this is done in the libpg.dll. To be honest, I think the problem as described above makes it a no-go. What you're asking for is essentially I want the server to ignore the username passed in which just doesn't make sense. It's unfortunate that this Windows application using ODBC doesn't allow for the username to be changed, but that's not something PG is going to solve for you. I would recommend you work on fixing the application. I think it's a reasonable expectation for there to be a way to pick up the username from an external authentication system, such as Kerberos, SSPI or certificate. Other databases that support them do - at least MSSQL. That said, if there is a username specified it should not be ignored. But if there is none specified, it should work. This works reasonably well today, in that we pick the username up from the environment. But I can see cases where it would be a lot more useful to have it instead pick up the username from the authentication system, since they may differ. But in no case should this override what the user put in to the system - but it could change the default. It works fine so far, as long as I want to work with the sigle role given by the fixed username. I could map any cn-contents to this one user by writing: # MAPNAME SYSTEM-USERNAME PG-USERNAME ssl-user /.* fixed_user What you really want is: ssl-user user1_cn user1 ssl-user user2_cn user2 ssl-user user3_cn user3 or so.. Well, you'd write that too with a regexp map with a back reference. That's the original reason I implemented regexps for pg_ident :-) And then have your application accept and use a username provided to it when connecting to ODBC. Having your application pass some hard-coded username to PG all the time just isn't going to work. I think the issue is provided to it. There should be no need for the user to know his username since he's already logged in. I agree that the hard-coded-username method is broken. -- 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] [PATCH] user mapping extension to pg_ident.conf
Magnus Hagander mag...@hagander.net writes: That said, if there is a username specified it should not be ignored. But if there is none specified, it should work. This works reasonably well today, in that we pick the username up from the environment. But I can see cases where it would be a lot more useful to have it instead pick up the username from the authentication system, since they may differ. Are you not describing a behavior that you yourself removed in 8.4, ie the libpq code that looked aside at Kerberos for a username? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
On Tue, Jul 21, 2009 at 15:58, Tom Lanet...@sss.pgh.pa.us wrote: Magnus Hagander mag...@hagander.net writes: That said, if there is a username specified it should not be ignored. But if there is none specified, it should work. This works reasonably well today, in that we pick the username up from the environment. But I can see cases where it would be a lot more useful to have it instead pick up the username from the authentication system, since they may differ. Are you not describing a behavior that you yourself removed in 8.4, ie the libpq code that looked aside at Kerberos for a username? Yes, partially I am :-) But it was not documented, and done in a fairly hackish way. If we want it, it should work the same for *all* external authentication methods (where it would be possible). Doing it on the client presents a certain challenge when it comes to certificates for example - or really in any case where you need to map the username to something else. It would be quite convenient to have that ability controlled from the server side. We'd have to have some way to communicate down that the username specified was the default one and not a user-specified one (or we're back at overriding), but if the actual mapping could be controlled server-side it would be a lot more convenient. -- 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] [PATCH v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Jeremy Kerr j...@ozlabs.org writes: Thanks for the benchmark app, thought I'd pitch in with some ppc results: It looks to me like we should go with the lookup table approach, as being the best tradeoff of speed improvement vs platform and compiler independence. The float hack is right out ;-) I wonder whether it is worth fooling with alternatives for the data type of the lookup table entries. unsigned char might be a tad faster (avoid useless sign-extension work). int might be faster yet, but it would enlarge the table, creating a distributed overhead that the microbenchmark would completely fail to show. Or we could buy that back by reducing the table to cover only 6 bits, knowing that 12 is more than we need. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] user mapping extension to pg_ident.conf
Magnus Hagander mag...@hagander.net writes: On Tue, Jul 21, 2009 at 15:58, Tom Lanet...@sss.pgh.pa.us wrote: Are you not describing a behavior that you yourself removed in 8.4, ie the libpq code that looked aside at Kerberos for a username? Yes, partially I am :-) But it was not documented, and done in a fairly hackish way. If we want it, it should work the same for *all* external authentication methods (where it would be possible). Well, the problem with it of course was that it happened even when the selected auth method was not Kerberos. Doing it on the client presents a certain challenge Yup, you would need a protocol change that would allow the client to change its mind about what the username was after it got the auth challenge. And then what effects does that have on username-sensitive pg_hba.conf decisions? We go back and change our minds about the challenge type, perhaps? The whole thing seems like a nonstarter 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] [PATCH] SE-PgSQL/tiny rev.2193
Greg Stark wrote: On Mon, Jul 20, 2009 at 8:44 PM, Joshua Brindlemet...@manicmethod.com wrote: I am capable of speaking for Tresys in this matter. We are very interested in this work and our US DoD customers need the capabilities that this project adds (assuming row level access controls are a possibility). I'm kind of curious about how these features get used. What specific problems do they solve? Backing up from KaiGai's description a bit, basically what this is needed for is storing multilevel data in a single db instance. For example, you have people logging in from different classifications (unclass, secret, top secret, etc) and the data they put in is marked (labeled) with their classification label. Then for queries the policy is used to determine who can see what. An unclass user will only be able to see unclassified data. A top secret user, however, can see all the data from top secret, secret and unclassified. This is why the view model doesn't work, we need 'read down' support. This also implies that key constraints are held between data of differing levels. This has some information leak side effects (eg., if an unclass user inserts data then waits 5 minutes and inserts again and sees the key incremented greatly he knows lots of classified data is going into the database) but this is a small information leak compared to the gain in functionality of a read-down system. The alternative to having this built in to the system is having many instances of postgres (or something else) running and having to query the right one to get the data (or all of them) and manually collating and associating rows. Very non-ideal. I have seen this method used en luau of having a multilevel database. Another scenerio is that all the data being put in is of some classification (eg., radar data coming from a piece of equipment is all marked secret) but some of the data is top secret. For example a column that has precise coordinates of enemy soldiers is higher classification than the rest of the data. A secret user may be allowed to query the data with some 'fuzz', through a trusted stored procedure. So the secret user wouldn't have direct access to the coordinates but could call a stored procedure to get the information with precision removed. This is why column level, row level and stored procedure access controls are all really required for the applications we are looking at. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] errcontext support in PL/Perl
Hi, While trying to build a custom error reporting function for one of our clients we came across the fact that PL/Perl doesn't set errcontext that we relied on to get the traceback of running functions. Exactly the same problem with PL/Python was fixed recently by Peter Eisentraut (http://archives.postgresql.org/pgsql-committers/2009-07/msg00168.php). Attached is a patch (HEAD) that sets errcontext with PL/Perl function name, making a distinction between compilation and execution stages, fixes error messages where function name was already included in the message itself and updates regression tests. I'll appreciate any suggestions on how to improve it. plperl_error_callback.diff Description: Binary data -- Alexey Klyukin http://www.CommandPrompt.com The PostgreSQL Company - Command Prompt, Inc. -- 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] Sampling profiler updated
Peter Eisentraut pete...@gmx.net writes: Well, the objection remains: We already have dtrace support, and dtrace or dtrace-like systems are spreading to many operating systems, so one wonders whether it is useful to clutter the code with another probing system instead of putting some resources, say, into getting systemtap up to speed. For the record, I think this patch is a waste of manpower and we should rely on dtrace/systemtap. However, if we are going to make our own homegrown substitute for those facilities, a minimum requirement should be that it uses the dtrace macros already put into the sources, rather than expecting that it gets to clutter the code some more with its own set of tracing markers. 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] Sampling profiler updated
Hi, Tom Lane t...@sss.pgh.pa.us writes: For the record, I think this patch is a waste of manpower and we should rely on dtrace/systemtap. However, if we are going to make our own homegrown substitute for those facilities, a minimum requirement should be that it uses the dtrace macros already put into the sources, rather than expecting that it gets to clutter the code some more with its own set of tracing markers. I think going from this form of the patch to reusing dtrace macros means providing another entirely different patch to solve the issue (even if the system view and its support function could remain about the same), so I've updated the patch commit fest status to rejected. Regards, -- dim -- 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] could not reattach to shared memory on Windows
On Wed, Jul 15, 2009 at 11:20, Tsutomu Yamadatsut...@sraoss.co.jp wrote: Hello, Alvaro Herrera alvhe...@commandprompt.com wrote: Tsutomu Yamada wrote: This patch using VirtualAlloc()/VirtualFree() to avoid failing in reattach to shared memory. Can this be added to CommitFest ? Since this fixes a very annoying bug present in older versions, I think this should be backpatched all the way back to 8.2. Some notes about the patch itself: - please use ereport() instead of elog() for error messages - Are you really putting the pgwin32_ReserveSharedMemory declaration inside a function? Please move that into the appropriate header file. - Failure to reserve memory in pgwin32_ReserveSharedMemory should be a FATAL error I think, not simply LOG. In this case, the parent process operates child's memory by using VirtualAlloc(). If VirtualAlloc failed and be a FATAL error, master process will be stopped. I think that is not preferable. So, when VirtualAlloc failed, parent reports error and terminates child. Revised patch - move function declaration to include/port/win32.h - add error check. when VirtualAlloc failed, parent will terminate child process. This patch looks a lot like one I've had sitting in my tree since before I left for three weeks of vacation, based on the same suggestion on the list. I will check if we have any actual functional differences, and merge yours with mine. The one I had worked fine in my testing. Once that is done, I propose the following: * Apply to HEAD. That will give us buildfarm coverage. * Produce a modified 8.4.0 *and* 8.3.7 binary for this, and ask people to test this. Both people with and without the problem. * Assuming it works for all users, backpatch to 8.2, 8.3 and 8.4. -- 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] [PATCH v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Normally I'd try a small lookup table (1-byte index to 1-byte value) in this case. But if the bitscan instruction were even close in performance, it'd be preferable, due to its more-reliable caching behavior; it should be possible to capture this at code-configuration time (aligned so as to produce an optimal result for each test case; see below). The specific code for large-versus-small testing would be useful; did I overlook it? Note that instruction alignment with respect to words is not the only potential instruction-alignment issue. In the past, when optimizing code to an extreme, I've run into cache-line issues where a small change that should've produced a small improvement resulted in a largish performance loss, without further work. Lookup tables can have an analogous issue; this could, in a simplistic test, explain an anomalous large-better-than-small result, if part of the large lookup table remains cached. (Do any modern CPUs attempt to address this??) This is difficult to tune in a multiplatform code base, so the numbers in a particular benchmark do not tell the whole tale; you'd need to make a judgment call, and perhaps to allow a code-configuration override. David Hudson
Re: [HACKERS] navigation menu for documents
Peter Eisentraut wrote: I would like to combine all these elements in header and footer, so they both say: Prev Fast Backward Home Fast Forward Next TitleP TitleFBTitleFFTitleN I think this would be much more usable than just the tooltips. +1 BTW I was left wondering why the fast forward link led to chapter 19 while standing on section 19.4, http://www.postgresql.org/docs/8.4/static/client-authentication-problems.html -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 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] [PATCH] SE-PgSQL/tiny rev.2193
On Tue, Jul 21, 2009 at 3:20 PM, Joshua Brindlemet...@manicmethod.com wrote: Backing up from KaiGai's description a bit, basically what this is needed for is storing multilevel data in a single db instance. For example, you have people logging in from different classifications (unclass, secret, top secret, etc) and the data they put in is marked (labeled) with their classification label. I'm beginning to wonder if we haven't gone about this all wrong. Every time someone asks my question about use cases the only answers that come back are about row-level security. Perhaps that's the only case that really matters here. If we provide a way to control access to database objects through SELinux policies -- ie, one which is functionally equivalent to what we have today but just allows administrators to control it in the same place they control other SELinux system privileges, is that useful? Is that something SE administrators want? Or are they happy to use Postgres roles and grants and just want the finer row-level data access controls? -- greg http://mit.edu/~gsstark/resume.pdf -- 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] Sampling profiler updated
On Tue, Jul 21, 2009 at 10:36 AM, Tom Lanet...@sss.pgh.pa.us wrote: Peter Eisentraut pete...@gmx.net writes: Well, the objection remains: We already have dtrace support, and dtrace or dtrace-like systems are spreading to many operating systems, so one wonders whether it is useful to clutter the code with another probing system instead of putting some resources, say, into getting systemtap up to speed. For the record, I think this patch is a waste of manpower and we should rely on dtrace/systemtap. However, if we are going to make our own homegrown substitute for those facilities, a minimum requirement should be that it uses the dtrace macros already put into the sources, rather than expecting that it gets to clutter the code some more with its own set of tracing markers. dtrace/systemtap doesn't work on every OS someone might care about, but I definitely agree that we should try to reuse the same tracing markers. ...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] [PATCH] SE-PgSQL/tiny rev.2193
Greg Stark wrote: On Tue, Jul 21, 2009 at 3:20 PM, Joshua Brindlemet...@manicmethod.com wrote: Backing up from KaiGai's description a bit, basically what this is needed for is storing multilevel data in a single db instance. For example, you have people logging in from different classifications (unclass, secret, top secret, etc) and the data they put in is marked (labeled) with their classification label. I'm beginning to wonder if we haven't gone about this all wrong. Every time someone asks my question about use cases the only answers that come back are about row-level security. Perhaps that's the only case that really matters here. If we provide a way to control access to database objects through SELinux policies -- ie, one which is functionally equivalent to what we have today but just allows administrators to control it in the same place they control other SELinux system privileges, is that useful? Is that something SE administrators want? Or are they happy to use Postgres roles and grants and just want the finer row-level data access controls? No, for multiple reasons. First a single person (role) could be logging in at different levels (eg., running the same application as the same linux user with the same credentials) and would need to see different things from the database. The SELinux contexts would provide the differentiation in this case and the SELinux policy would enforce the multilevel policy. I also don't think your roles and grants could enforce a multilevel policy but that is something I'd have to look into deeper to know for sure. Also the objects need to be labeled based on how they were inserted, and 're-grading' applications need to be able to relabel them. You still need the 'read-down' behavior I talked about above. You also snipped the other scenario I had where row based access control isn't required but column level and stored procedure level are. I understand you already have column level access controls but it still goes back to how the user is accessing the data, as a top secret user who can read the column with full precision or as a secret user with precision removed via a trusted stored procedure. The SELinux policy would have to give the stored procedure the ability to read the column and trust it to remove the necessary amount of precision. -- 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] user mapping extension to pg_ident.conf
Am Dienstag, 21. Juli 2009 16:01:01 schrieben Sie: Doing it on the client presents a certain challenge when it comes to certificates for example - or really in any case where you need to map the username to something else. It would be quite convenient to have that ability controlled from the server side. We'd have to have some way to communicate down that the username specified was the default one and not a user-specified one (or we're back at overriding), but if the actual mapping could be controlled server-side it would be a lot more convenient. I thought about doing it on the client side too, but server side mapping seemed to me more flexible. In fact one could do a client side mapping (without knowledge of the auth method), by greping the username of the external system out of the error text from the server and doing a second auth with it. Just I don't like this ugly hack. There was another mail, where I described the use of the mapping patch: http://archives.postgresql.org/pgsql-hackers/2009-06/msg01496.php Please have a look on it. One can give different mappings for combinations of internal and external username. This way you could easy use different roles within the database with differnent applications, although the external auth system gives the same username. The dummy-user of the first mail was not the best example. I'm not the expert with PGs internals. So if you have a better way to get an usermapping based on the external auth, I could do a bit of research in this area, because I need something like this. regards Lars Kanis signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] errcontext support in PL/Perl
Alexey Klyukin wrote: Attached is a patch (HEAD) that sets errcontext with PL/Perl function name, making a distinction between compilation and execution stages, fixes error messages where function name was already included in the message itself and updates regression tests. I'll appreciate any suggestions on how to improve it. Hmm, in plperl_exec_callback(), does the global variable work if you call one plperl function from another? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
p...@thetdh.com writes: Normally I'd try a small lookup table (1-byte index to 1-byte value) in this case. But if the bitscan instruction were even close in performance, it'd be preferable, due to its more-reliable caching behavior; Well, the problem with the bitscan instruction is we won't have it at all on a lot of configurations. I don't think this problem is so important as to justify *two* hacked-up code paths. The specific code for large-versus-small testing would be useful; did I overlook it? Sorry, I should have provided that. What I've tested is #define LT(n) n, n, n, n, n, n, n, n, n, n, n, n, n, n, n, n static const char LogTable256[256] = { 0, 1, 2, 2, 3, 3, 3, 3, 4, 4, 4, 4, 4, 4, 4, 4, LT(5), LT(6), LT(6), LT(7), LT(7), LT(7), LT(7), LT(8), LT(8), LT(8), LT(8), LT(8), LT(8), LT(8), LT(8) }; int AllocSetFreeIndex_lt(size_t size) { int idx = 0; if (size (1 ALLOC_MINBITS)) { unsigned int t, tt; // temporaries size = (size - 1) ALLOC_MINBITS; if ((tt = (size 16))) { idx = (t = tt 8) ? 24 + LogTable256[t] : 16 + LogTable256[tt]; } else { idx = (t = size 8) ? 8 + LogTable256[t] : LogTable256[size]; } } return idx; } int AllocSetFreeIndex_lts(size_t size) { int idx = 0; if (size (1 ALLOC_MINBITS)) { unsigned int t; // temporaries size = (size - 1) ALLOC_MINBITS; t = size 8; idx = t ? 8 + LogTable256[t] : LogTable256[(unsigned int) size]; } return idx; } plus the obvious additions to the other file for an additional test type. Note that instruction alignment with respect to words is not the only potential instruction-alignment issue. In the past, when optimizing code to an extreme, I've run into cache-line issues where a small change that should've produced a small improvement resulted in a largish performance loss, without further work. Lookup tables can have an analogous issue; this could, in a simplistic test, explain an anomalous large-better-than-small result, if part of the large lookup table remains cached. (Do any modern CPUs attempt to address this??) Yeah, I've seen similar things when trying to micro-optimize code. That's why it's so critical to get results from multiple platforms/machines before trusting the answers too much. This is difficult to tune in a multiplatform code base, so the numbers in a particular benchmark do not tell the whole tale; you'd need to make a judgment call, and perhaps to allow a code-configuration override. Well, my judgment call is that we should go with the lookup table. So far, the only machine where that doesn't seem to provide a nice win is my old Mac G4 laptop, which is hardly a platform that people are likely to be stressing about database performance on. Jeremy's PPC machines like it a lot more, so there's not something fundamentally wrong with the approach for that architecture. The other alternatives all have significantly greater downsides. 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] errcontext support in PL/Perl
On Jul 21, 2009, at 6:39 PM, Alvaro Herrera wrote: Alexey Klyukin wrote: Attached is a patch (HEAD) that sets errcontext with PL/Perl function name, making a distinction between compilation and execution stages, fixes error messages where function name was already included in the message itself and updates regression tests. I'll appreciate any suggestions on how to improve it. Hmm, in plperl_exec_callback(), does the global variable work if you call one plperl function from another? PL/Perl functions can't call each other directly. I don't see any problems with SPI calls: test=# create function perl_log1() returns void language plperl as $$ test$# elog(NOTICE, Test from function one); test$# $$ test-# ; CREATE FUNCTION test=# create function perl_log2() returns void language plperl as $ $ elog (NOTICE, Test from function two); my $rv = spi_exec_query('SELECT * FROM perl_log1()'); $$; CREATE FUNCTION test=# select perl_log2(); NOTICE: Test from function two CONTEXT: PL/Perl function perl_log2 NOTICE: Test from function one CONTEXT: PL/Perl function perl_log1 SQL statement SELECT * FROM perl_log1() PL/Perl function perl_log1 perl_log2 --- (1 row) -- Alexey Klyukin http://www.CommandPrompt.com The PostgreSQL Company - Command Prompt, Inc. -- 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] git revisited
Peter Eisentraut wrote: On Monday 20 July 2009 23:17:30 Andrew Dunstan wrote: I'm not sure where we got to with doing some surgery on the CVS repo so that we can replicate all the tags and branches properly. Has someone fully identified what needs to be fixed so we can have all the tags? I think this depends on which tool we would end up using to do the final conversion. Each tool has its own quirks. Didn't someone report not long ago that there were files missing in a checkout of some not-so-old tag that made it impossible to compile such a tree? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] errcontext support in PL/Perl
Alexey Klyukin wrote: NOTICE: Test from function one CONTEXT: PL/Perl function perl_log1 SQL statement SELECT * FROM perl_log1() PL/Perl function perl_log1 Shouldn't the second PL/Perl function line say perl_log2 instead? -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] errcontext support in PL/Perl
Alexey Klyukin al...@commandprompt.com writes: On Jul 21, 2009, at 6:39 PM, Alvaro Herrera wrote: Hmm, in plperl_exec_callback(), does the global variable work if you call one plperl function from another? PL/Perl functions can't call each other directly. Still, it's poor style to rely on the global variable when you don't have to. Use the error_context.arg field to pass it. 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] full join qualifications on 8.3.1 vs. 8.3.6
Given: select * from test1 full join test2 using (a,b) where b=1; 8.3.1 and CVS HEAD produce a plan in which the b=1 condition is pushed down into each side of the join, but 8.3.6 and 8.3.7 do not. Is this intentional? I wasn't able to identify anything applicable in the release notes. testcase used: create table test1 (a integer, b integer, c text); create table test2 (a integer, b integer, c text); insert into test1 select i,i,i::text from generate_series(1,1) i; insert into test2 select i,i,i::text from generate_series(1,1) i; create index test1_idx on test1 (b); create index test2_idx on test2 (b); explain select * from test1 full join test2 using (a,b) where b = 1; -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] errcontext support in PL/Perl
On Jul 21, 2009, at 7:20 PM, Alvaro Herrera wrote: Alexey Klyukin wrote: NOTICE: Test from function one CONTEXT: PL/Perl function perl_log1 SQL statement SELECT * FROM perl_log1() PL/Perl function perl_log1 Shouldn't the second PL/Perl function line say perl_log2 instead? Hm, yeah, seems to be a problem. I'll change the callback to avoid using global data. -- Alexey Klyukin http://www.CommandPrompt.com The PostgreSQL Company - Command Prompt, Inc. -- 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] full join qualifications on 8.3.1 vs. 8.3.6
Andrew Gierth and...@tao11.riddles.org.uk writes: select * from test1 full join test2 using (a,b) where b=1; 8.3.1 and CVS HEAD produce a plan in which the b=1 condition is pushed down into each side of the join, but 8.3.6 and 8.3.7 do not. It does what you're expecting in 8.3 branch tip. http://archives.postgresql.org/pgsql-committers/2009-04/msg00204.php We're really overdue for a set of back-branch update releases :-( 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] Index-only-scans, indexam API changes
On Mon, Jul 13, 2009 at 11:32 AM, Heikki Linnakangasheikki.linnakan...@enterprisedb.com wrote: Tom Lane wrote: One thought here is that an AM call isn't really free, and doing two of them instead of one mightn't be such a good idea. I would suggest either having a separate AM entry point to get both bits of data (amgettupledata?) or adding an optional parameter to amgettuple. I'm thinking of adding a new flag to IndexScanDesc, needIndexTuple. When that is set, amgettuple stores a pointer to the IndexTuple in another new field in IndexScanDesc, xs_itup. (In case of b-tree at least, the IndexTuple is a palloc'd copy of the tuple on disk) [ 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? Well, the way I have it implemented is that the am is not required to return the index tuple, even if requested. I implemented the B-tree changes similar to how we implement kill_prior_tuple: in btgettuple, lock the index page and see if the tuple is still at the same position that we remembered in the scan opaque struct. If not (because of concurrent changes to the page), we give up and don't return the index tuple. The executor will then perform a heap fetch as before. Alternatively, we could copy all the matching index tuples to private memory when we step to a new index page, but that seems pretty expensive if we're only going to use the first few matching tuples (LIMIT), and I fear the memory management gets complicated. But in any case, the GiST issue would still be there. Since we're discussing it, I'm attaching the prototype patch I have for returning tuples from b-tree and using them to filter rows before heap fetches. I was going to post it in the morning along with description about the planner and executor changes, but here goes. It applies on top of the indexam API patch I started this thread with. I'm not sure where we are on this patch for reviewing purposes. Heikki, are you planning to provide an updated patch? Or what should we be doing here from an RRR standpoint? ...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] Non-blocking communication between a frontend and a backend (pqcomm)
On Fri, Jul 17, 2009 at 5:26 PM, Martin Pihlakmartin.pih...@gmail.com wrote: Fujii Masao wrote: http://archives.postgresql.org/pgsql-hackers/2009-07/msg00191.php In line with Robert's suggestion, I submit non-blocking pqcomm patch as a self-contained one. Here's my initial review of the non-blocking pqcomm patch. The patch applies cleanly and passes regression. Generally looks nice and clean. Couple of remarks from the department of nitpicking: * In secure_poll() the handling of timeouts is different depending whether poll(), select() or SSL_pending() is used. The latter doesn't use the timeout value at all, and for select() it is impossible to specify indefinite timeout. * occasional blank lines consisting of a single tab character -- maybe a left-over from editor auto-indent. Not sure of how much a problem this is, given that the blanks will be removed by pg_indent. * Comment on pq_wait() seems to have a typo: -1 if an error directly. I have done limited testing on Linux i686 (HAVE_POLL only) -- the non-blocking functions behave as expected. Fujii Masao, Are you planning to update this patch based on Martin's review? ...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
On Mon, 2009-07-20 at 17:24 +0100, Dean Rasheed wrote: The same argument could be applied to ruleutils.c, trigfuncs.c and perhaps one or two others. And if not there, where then? I'm moving the patch status back to waiting on author until Alvaro's concerns are addressed. I don't have an opinion about what should happen to the location of the file, but some kind of decision should be made. 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] errcontext support in PL/Perl
On Jul 21, 2009, at 7:47 PM, Alexey Klyukin wrote: On Jul 21, 2009, at 7:20 PM, Alvaro Herrera wrote: Alexey Klyukin wrote: NOTICE: Test from function one CONTEXT: PL/Perl function perl_log1 SQL statement SELECT * FROM perl_log1() PL/Perl function perl_log1 Shouldn't the second PL/Perl function line say perl_log2 instead? Hm, yeah, seems to be a problem. I'll change the callback to avoid using global data. Attached is the updated version of the patch (the original description is here: http://archives.postgresql.org/pgsql-hackers/2009-07/msg01332.php) that doesn't use global variables. It also shows the last line of the context in the example above correctly: psql (8.5devel) Type help for help. test=# select perl_log2(); NOTICE: Test from function two CONTEXT: PL/Perl function perl_log2 NOTICE: Test from function one CONTEXT: PL/Perl function perl_log1 SQL statement SELECT * FROM perl_log1() PL/Perl function perl_log2 perl_log2 --- (1 row) plperl_error_callback_v2.diff Description: Binary data -- Alexey Klyukin http://www.CommandPrompt.com The PostgreSQL Company - Command Prompt, Inc. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] psql \du and \dg is the same - pg 8.4
Hi, actually I discovered that using \du and \dg in psql is providing the same result: book=# \du * QUERY ** SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof FROM pg_catalog.pg_roles r ORDER BY 1; ** List of roles Role name | Attributes | Member of ---+--+--- postgres | Superuser| {} : Create role : Create DB ps_buch_group | | {} psbuch| | {} psbuch_role | Cannot login | {} roundcube | | {} book=# \dg * QUERY ** SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof FROM pg_catalog.pg_roles r ORDER BY 1; ** List of roles Role name | Attributes | Member of ---+--+--- postgres | Superuser| {} : Create role : Create DB ps_buch_group | | {} psbuch| | {} psbuch_role | Cannot login | {} roundcube | | {} Commonly a grouprole is defined as a role with no right to login. As of this, the following statement should list all grouproles: SELECT r.rolname, r.rolsuper, r.rolinherit, r.rolcreaterole, r.rolcreatedb, r.rolcanlogin, r.rolconnlimit, ARRAY(SELECT b.rolname FROM pg_catalog.pg_auth_members m JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid) WHERE m.member = r.oid) as memberof FROM pg_catalog.pg_roles r WHERE r.rolcanlogin = 'f' ORDER BY 1; rolname|rolsuper|rolinherit|rolcreaterole|rolcreatedb|rolcanlogin| rolconnlimit|memberof ---++--+-+---+---+ ---+- psbuch_role| f | t| f | f | f | -1 | {} (1 row) On the other hand a group role can also have the login privilege as of all roles can have members or not with the privilege login or not. I am wondering why there is \dg at all. I am not sure what the intention is to have it. And also I am not sure if the definition of a group role (having no login privilege) is really correct. Any ideas on this? If there is a clear solution and the implementation of \dg is wanted but not correctly implemented, I could try to provide a patch. By the way. It's also possible to use \dg+ . This is missing in the psql help (\dg[+]) in the same way as for \du (see my small patch). If I missed something please lend me a hand to the right way. Cheers Andy -- 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 patch for TODO Item: Add prompt escape to display the client and server versions
On Friday 17 July 2009 23:24:16 Dickson S. Guedes wrote: An use case that i can figure out is an user that connects in multiples instances in a lot of remote sites (like home-officer for example) and needs this information in the prompt to don't lost the context of your work. Is this valid? Is this and other similar cases quite enough to justify this patch? If yes I can change the patch to satisfy the Peter's suggestions, if no we can just ignore the patch and remove the item from TODO. I think there is enough support for the patch. So please adjust it to report the server version correctly. -- 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 patch for TODO Item: Add prompt escape to display the client and server versions
Em Tue, 21 Jul 2009 16:52:48 -0300, Peter Eisentraut pete...@gmx.net escreveu: On Friday 17 July 2009 23:24:16 Dickson S. Guedes wrote: An use case that i can figure out is an user that connects in multiples instances in a lot of remote sites (like home-officer for example) and needs this information in the prompt to don't lost the context of your work. Is this valid? Is this and other similar cases quite enough to justify this patch? If yes I can change the patch to satisfy the Peter's suggestions, if no we can just ignore the patch and remove the item from TODO. I think there is enough support for the patch. So please adjust it to report the server version correctly. Thanks Peter, I'll adjust the patch and post a new version ASAP. Regards. -- Dickson S. Guedes mail/xmpp: gue...@guedesoft.net - skype: guediz http://guedesoft.net - http://www.postgresql.org.br http://www.rnp.br/keyserver/pks/lookup?search=0x8F3E3C06D428D10A -- 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 v4] Avoid manual shift-and-test logic in AllocSetFreeIndex
Jeremy Kerr j...@ozlabs.org writes: Rather than testing single bits in a loop, change AllocSetFreeIndex to use the __builtin_clz() function to calculate the chunk index. Per subsequent discussion and testing, I've committed a patch that uses a lookup table instead of depending on __builtin_clz(). http://archives.postgresql.org/message-id/20090721195312.207ca753...@cvs.postgresql.org 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] psql \du and \dg is the same - pg 8.4
ANdreas Wenk a.w...@netzmeister-st-pauli.de writes: actually I discovered that using \du and \dg in psql is providing the same result: Yup. The psql documentation says as much. I am wondering why there is \dg at all. Users and groups used to be distinct kinds of objects. They aren't anymore, but people might still be used to the old commands. 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] psql \du and \dg is the same - pg 8.4
Tom Lane wrote: ANdreas Wenk a.w...@netzmeister-st-pauli.de writes: actually I discovered that using \du and \dg in psql is providing the same result: Yup. The psql documentation says as much. ok - got it ;-) Should have read the psql docu ... I am wondering why there is \dg at all. Users and groups used to be distinct kinds of objects. They aren't anymore, but people might still be used to the old commands. Yeah I know - but I was wondering if it would make sense to have \dg with a different result because here http://www.postgresql.org/docs/8.4/interactive/role-membership.html the people still read about group roles. I am not sure what is confusing here - in psql or in the docu. regards, tom lane Cheers Andy Cheers Andy -- 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] bytea vs. pg_dump
--On Samstag, Juli 11, 2009 13:40:44 +0300 Peter Eisentraut pete...@gmx.net wrote: OK, here is an updated patch. It has the setting as enum, completed documentation, and libpq support. I'll add it to the commit fest in the hope that someone else can look it over in detail. I've started looking at this and did some profiling with large bytea data again. For those interested, here are the numbers: Dumping with bytea_output=hex (COPY to file): real20m38.699s user0m11.265s sys 1m0.560s Dumping with bytea_output=escape (COPY to file): real39m52.399s user0m22.085s sys 1m50.131s So the time needed dropped about 50%. The dump file dropped from around 48 GB to 28 GB with the new format. I have some profiler data for this, but the restore seems much more interesting: the time to restore for both formats is quite the same: Restore bytea_output=hex real32m11.028s user0m0.000s sys 0m0.008s Restore bytea_output=escape real31m35.378s user0m0.000s sys 0m0.000s The profile for restoring the hex format looks like this: % cumulative self self total time seconds secondscalls s/call s/call name 34.54156.79 156.7997836 0.00 0.00 pglz_compress 18.64241.3884.59 141374 0.00 0.00 CopyReadLine 12.83299.6258.24 3604740854 0.00 0.00 get_hex 8.44337.9538.33 14257432 0.00 0.00 XLogInsert 7.39371.4833.53 141373 0.00 0.00 hex_decode 7.23404.3132.83132.83 436.67 DoCopy 3.48420.1215.81 esc_enc_len 0.61422.89 2.77 134943749 0.00 0.00 _bt_compare 0.54425.36 2.47 33682172 0.00 0.00 ReadBuffer_common 0.54427.83 2.47 52166324 0.00 0.00 hash_search_with_hash_value 0.45429.89 2.06 104798203 0.00 0.00 LWLockAcquire 0.36431.53 1.64 105234314 0.00 0.00 LWLockRelease I've attached a slightly edited patch which fixes a compiler warning in encode.c, too. -- Thanks Bernddiff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml index 99d25d7..9a543bc 100644 *** a/doc/src/sgml/config.sgml --- b/doc/src/sgml/config.sgml *** COPY postgres_log FROM '/full/path/to/lo *** 3735,3740 --- 3735,3757 titleStatement Behavior/title variablelist + varlistentry id=guc-bytea-output xreflabel=bytea_output + termvarnamebytea_output/varname (typeenum/type)/term + indexterm +primaryvarnamebytea_output/ configuration parameter/primary + /indexterm + listitem +para + Sets the output format for values of type typebytea/type. + Valid values are literalhex/literal (the default) + and literalescape/literal (the traditional PostgreSQL + format). The xref linkend=datatype-binary for more + information. Note that the typebytea/type type always + accepts both formats on input. +/para + /listitem + /varlistentry + varlistentry id=guc-search-path xreflabel=search_path termvarnamesearch_path/varname (typestring/type)/term indexterm diff --git a/doc/src/sgml/datatype.sgml b/doc/src/sgml/datatype.sgml index f7ee8e9..8576419 100644 *** a/doc/src/sgml/datatype.sgml --- b/doc/src/sgml/datatype.sgml *** *** 1,4 ! !-- $PostgreSQL$ -- chapter id=datatype title id=datatype-titleData Types/title --- 1,4 ! !-- $PostgreSQL: pgsql/doc/src/sgml/datatype.sgml,v 1.237 2009/04/27 16:27:35 momjian Exp $ -- chapter id=datatype title id=datatype-titleData Types/title *** SELECT b, char_length(b) FROM test2; *** 1191,1196 --- 1191,1256 /para para + The typebytea/type type supports two external formats for + input and output: the quoteescape/quote format that is + particular to PostgreSQL, and the quotehex/quote format. Both + of these are always accepted on input. The output format depends + on the configuration parameter xref linkend=guc-bytea-output; + the default is hex. (Note that the hex format was introduced in + PostgreSQL 8.5; so earlier version and some tools don't understand + it.) +/para + +para + The acronymSQL/acronym standard defines a different binary + string type, called typeBLOB/type or typeBINARY LARGE + OBJECT/type. The input format is different from + typebytea/type, but the provided functions and operators are + mostly the same. +/para + + sect2 +titleThe Hex Format/title + +para + The hex format encodes the binary data as 2 hexadecimal digits per + byte, highest significant nibble first. The entire string ist + preceded by the sequence literal\x/literal (to distinguish it + from the bytea format). In SQL literals, the backslash may need + to be escaped, but it is one logical
Re: [HACKERS] pg_listener attribute number #defines
On Wednesday 24 June 2009 08:09:57 Robert Haas wrote: It appears that, for no particularly good reason, pg_listener.h deviates from the usual convention for declaring attribute number constants. Normally, it's #define Anum_{catalog-name}_{column-name} {attribute-number} pg_listener.h, however substitutes a different string that is similar, but not the same as, the column name. Suggested patch attached. Fixed. -- 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] bytea vs. pg_dump
Bernd Helmle wrote: --On Samstag, Juli 11, 2009 13:40:44 +0300 Peter Eisentraut pete...@gmx.net wrote: OK, here is an updated patch. It has the setting as enum, completed documentation, and libpq support. I'll add it to the commit fest in the hope that someone else can look it over in detail. I've started looking at this and did some profiling with large bytea data again. For those interested, here are the numbers: Dumping with bytea_output=hex (COPY to file): real20m38.699s user0m11.265s sys 1m0.560s Dumping with bytea_output=escape (COPY to file): real39m52.399s user0m22.085s sys 1m50.131s So the time needed dropped about 50%. The dump file dropped from around 48 GB to 28 GB with the new format. You just tested COPY, not pg_dump, right? Some pg_dump numbers would be interesting, both for text and custom formats. 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] WIP: Deferrable unique constraints
2009/7/21 Jeff Davis pg...@j-davis.com: On Mon, 2009-07-20 at 17:24 +0100, Dean Rasheed wrote: The same argument could be applied to ruleutils.c, trigfuncs.c and perhaps one or two others. And if not there, where then? I'm moving the patch status back to waiting on author until Alvaro's concerns are addressed. I don't have an opinion about what should happen to the location of the file, but some kind of decision should be made. Regards, Jeff Davis OK, I'll try to post an updated patch soon. I'm not seeing an obvious alternative location to utils/adt. Any advice would be appreciated. - 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] WIP: Deferrable unique constraints
On Tue, 2009-07-21 at 22:01 +0100, Dean Rasheed wrote: OK, I'll try to post an updated patch soon. I'm not seeing an obvious alternative location to utils/adt. Any advice would be appreciated. My first reaction is utils/misc. 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] bytea vs. pg_dump
--On Dienstag, Juli 21, 2009 16:49:45 -0400 Andrew Dunstan and...@dunslane.net wrote: You just tested COPY, not pg_dump, right? Some pg_dump numbers would be interesting, both for text and custom formats. Plain COPY, yes. I planned testing pg_dump for this round of my review but ran out of time unfortunately. The restore might be limited by xlog (didn't realize that the profile shows XLogInsert in the top four). I'll try to get some additional numbers soon, but this won't happen before thursday. -- Thanks Bernd -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_restore --clean vs. large object
Itagaki Takahiro itagaki.takah...@oss.ntt.co.jp writes: The attached is a patch to execute lo_unlink() before lo_create() in pg_restore. Applied with corrections --- you had failed to ensure that pg_dump and pg_restore produce the same output. I also took the opportunity to schema-qualify the calls of lo_xxx functions, just to be on the safe side. (The code already sets search_path, but why not be sure ...) 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-21 at 22:01 +0100, Dean Rasheed wrote: I'm not seeing an obvious alternative location to utils/adt. Any advice would be appreciated. You could make a fair case for either backend/catalog or backend/commands. Maybe the latter since that's where the core trigger support is. My first reaction is utils/misc. I always hated that directory. *Double* failure to classify. 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] bytea vs. pg_dump
Bernd Helmle wrote: --On Dienstag, Juli 21, 2009 16:49:45 -0400 Andrew Dunstan and...@dunslane.net wrote: You just tested COPY, not pg_dump, right? Some pg_dump numbers would be interesting, both for text and custom formats. Plain COPY, yes. I planned testing pg_dump for this round of my review but ran out of time unfortunately. The restore might be limited by xlog (didn't realize that the profile shows XLogInsert in the top four). I'll try to get some additional numbers soon, but this won't happen before thursday. If the table is created by the restore job, either use parallel pg_restore (-j nn) or use the --single-transaction flag - both will ensure that the WAL log is avoided. For plain COPY, get the same effect using: begin; truncat foo; copy foo ... ; commit; All this assumes that archive_mode is off. 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] bytea vs. pg_dump
Peter Eisentraut pete...@gmx.net writes: OK, here is an updated patch. It has the setting as enum, completed documentation, and libpq support. I'll add it to the commit fest in the hope that someone else can look it over in detail. I found that there is another issue that should be addressed, maybe not by this patch but by a follow-on. While looking at Itagaki-san's patch for making pg_dump --clean drop large objects, I noticed that pg_dump is still relying on the deprecated function PQescapeBytea to dump the contents of large objects when it is creating text output. This manages not to fail for common cases, but there is at least one case we overlooked: if you pg_dump with standard_conforming_strings turned on into a custom (or probably tar) archive, and then use pg_restore to generate a SQL script from that, the strings will be improperly escaped. It strikes me that the best solution for this is to emit hex-coded bytea instead of escaped bytea. While we could just hardcode that into pg_dump, it would probably be better if libpq provided a function along the lines of PQescapeByteaHex. In some far future, maybe PQescapeBytea could be rescued from the depths of deprecation by having it emit hex-coded output; but of course that would fail against pre-8.5 servers, so it's a long way off. In the nearer future, it would be possible and perhaps wise for PQescapeByteaConn to adopt hex coding when it sees the connection is to a server = 8.5. It didn't look to me like the patch addressed this either. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
On Tue, Jul 21, 2009 at 4:24 PM, Joshua Brindlemet...@manicmethod.com wrote: You also snipped the other scenario I had where row based access control isn't required but column level and stored procedure level are. Well we already have column level and stored procedure privileges. I understand you already have column level access controls but it still goes back to how the user is accessing the data, as a top secret user who can read the column with full precision or as a secret user with precision removed via a trusted stored procedure. Sure, and people do this every day already with postgres roles and privileges. The SELinux policy would have to give the stored procedure the ability to read the column and trust it to remove the necessary amount of precision. Well the question is: Is the important feature of SEPostgres the unification of the privilege model with every other piece of the system in the SELinux policy? Or is that not the main thing and only the row-level access security is interesting. None of the use cases seem to put any emphasis on the unification of the security policy. -- greg http://mit.edu/~gsstark/resume.pdf -- 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] revised hstore patch
Andrew Gierth and...@tao11.riddles.org.uk writes: Revision to previous hstore patch to fix (and add tests for) some edge case bugs with nulls or empty arrays. I took a quick look at this, and have a couple of beefs associated with upgrade risks. 1. The patch arbitrarily changes the C-code names of several existing SQL functions. DO NOT DO THIS. If somebody dumps an 8.4 database containing hstore, and loads it into 8.5, the result would be crashes and perhaps even exploitable security holes. The C name is part of the function's ABI and you can't just change it. It's okay if, after such a dump and reload scenario, there is functionality that's inaccessible because the necessary SQL function definitions are missing. It's not so okay if there are security holes. 2. The patch changes the on-disk representation of hstore. That is clearly necessary to achieve the goal of allowing keys/values longer than 64K, but it breaks on-disk compatibility from 8.4 to 8.5. I'm not sure what our threshold is for allowing compatibility breaks, but I think it's higher than this. The demand for longer values inside an hstore has not been very great. Perhaps an appropriate thing to do is separate out the representation change from the other new features, and apply just the latter for now. Or maybe we should think about having two versions of hstore. This is all tied up in the problem of having a decent module infrastructure (which I hope somebody is working on for 8.5). I don't know where we're going to end up for 8.5, but I'm disinclined to let a fairly minor contrib feature improvement break upgrade-compatibility before we've even really started the cycle. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Greg Stark wrote: On Tue, Jul 21, 2009 at 4:24 PM, Joshua Brindlemet...@manicmethod.com wrote: You also snipped the other scenario I had where row based access control isn't required but column level and stored procedure level are. Well we already have column level and stored procedure privileges. I understand you already have column level access controls but it still goes back to how the user is accessing the data, as a top secret user who can read the column with full precision or as a secret user with precision removed via a trusted stored procedure. Sure, and people do this every day already with postgres roles and privileges. The SELinux policy would have to give the stored procedure the ability to read the column and trust it to remove the necessary amount of precision. Well the question is: Is the important feature of SEPostgres the unification of the privilege model with every other piece of the system in the SELinux policy? Or is that not the main thing and only the row-level access security is interesting. None of the use cases seem to put any emphasis on the unification of the security policy. It is the most significant feature of SE-PostgreSQL to enforce the centralized security policy, as if SELinux enforces its policy to the accesses on objects manged by operating system. Indeed, SELinux can control accesses on user's data stored within filesystems (operating system feature) based on its security policy. However, nowadays, filesystem is not only piece to store user's data with variable credentials (ie; secret, unclassified, ...). Needless to say, database is a significant pieces to store variable user's data, but it is not invisible from the operating system how does it controled, because all the database objects are managed in userspace. It means SELinux cannot control data flows via databases, although it occupies an important place in our information system. Granularity in access controls is an independent issue. Integration of the access control policy is an issue how to decide whether the required accesses to be allowed, or not. On the other hand, table/column/row level access control is an issue what objects to be controled. 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] [PATCH] SE-PgSQL/tiny rev.2193
Greg Stark wrote: On Tue, Jul 21, 2009 at 4:24 PM, Joshua Brindlemet...@manicmethod.com wrote: You also snipped the other scenario I had where row based access control isn't required but column level and stored procedure level are. Well we already have column level and stored procedure privileges. I understand you already have column level access controls but it still goes back to how the user is accessing the data, as a top secret user who can read the column with full precision or as a secret user with precision removed via a trusted stored procedure. Sure, and people do this every day already with postgres roles and privileges. The SELinux policy would have to give the stored procedure the ability to read the column and trust it to remove the necessary amount of precision. Well the question is: Is the important feature of SEPostgres the unification of the privilege model with every other piece of the system in the SELinux policy? Or is that not the main thing and only the row-level access security is interesting. None of the use cases seem to put any emphasis on the unification of the security policy. I don't understand how you came to this conclusion. Quoting from my prior email: No, for multiple reasons. First a single person (role) could be logging in at different levels (eg., running the same application as the same linux user with the same credentials) and would need to see different things from the database. The SELinux contexts would provide the differentiation in this case and the SELinux policy would enforce the multilevel policy. In this case the selinux context (which specifies their MLS level) says the user is running at unclass and there should be no possible credentials or role or anything that gives him access to data above unclass in the database. This _is_ a unification of the policy because whether the unclass user is accessing files or rows in a mixed-level database or entire databases that are classified as such the SELinux policy is governing that. Its the same with selinux aware X. If a user is running 2 database clients, one at unclass and one at secret then each application would how the appropriate results. The policy also disallows that user from copying from the secret database client into the unclass client (in this case there are SELinux rules enforcing behavior of X apps, of the database clients and of the filesystem), unified indeed.
Re: [HACKERS] [PATCH] SE-PgSQL/tiny rev.2193
Greg Stark wrote: On Tue, Jul 21, 2009 at 5:51 AM, Robert Haasrobertmh...@gmail.com wrote: I really, really think you need to find someone to help you with the documentation. As I've said before, your English is a lot better than my Japanese, but the current documentation is just hard to read. In general we're very generous with English writing quality. I wouldn't worry too much about the language barrier as far as writing documentation. However the snippets Robert posted of the documentation had a more fundamental problem unrelated to language. The problem is that it explained what an option did in terms of what that option did -- ie, the documentation only made sense if you already knew what it was trying to say. That's not an unusual trap to get into when writing documentation regardless of what language you're writing in. Indeed, language quality is a causality, but not all. The matter is we've not been able to share what does it try to achieve correctly, what is identicals or differences between database privileges and SELinux's security model, and so on. 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] generic explain options v3
Robert Haas robertmh...@gmail.com writes: Here is an updated version of my generic options for explain patch. What is the rationale for essentially duplicating defGetBoolean()? Also, I'd suggest changing the ExplainStmt struct to have a list of DefElem options instead of hard-wiring the option set at that level. 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] Commitfest Code Sprint with PUGs
So if the general commitfest begins on Sept 1, I recommend that we hold our sprint the weekend following (saturday 5, say 10am to 4pm Pacific Standard?). Thoughts? If we set a date, then people can converge on it. Pardon me if I am replying without enough context -- I have enough compelling time sinks in my life without the various PostgreSQL lists these days. Gabrielle -- have you started a wiki page/ topic somewhere? w -- 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] revised hstore patch
Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Andrew Gierth and...@tao11.riddles.org.uk writes: Revision to previous hstore patch to fix (and add tests for) some edge case bugs with nulls or empty arrays. Tom I took a quick look at this, and have a couple of beefs Tom associated with upgrade risks. Tom 1. The patch arbitrarily changes the C-code names of several Tom existing SQL functions. (a) As written, it provides all of the old names too. (b) many of the old names are significant collision risks. (This was all discussed previously. I specifically said that compatibility was being maintained on this point; you obviously missed that.) Tom 2. The patch changes the on-disk representation of hstore. That Tom is clearly necessary to achieve the goal of allowing keys/values Tom longer than 64K, but it breaks on-disk compatibility from 8.4 to Tom 8.5. I'm not sure what our threshold is for allowing Tom compatibility breaks, but I think it's higher than this. The Tom demand for longer values inside an hstore has not been very Tom great. The intention is that hstore(record) should work for all practically useful record sizes. While it's possible for records to be much larger than 1GB, in practice you're going to run into issues long before then. Conversely, text fields over 64k are much more common. The code already has users who are using it for audit-trail stuff (easily computing the changes between old and new records and storing only the differences). Perhaps one of the existing users could express an opinion on this point. Certainly when developing this I had _SIGNIFICANT_ encouragement, some of it from YOU, for increasing the limit. (see for example http://archives.postgresql.org/pgsql-hackers/2009-03/msg00577.php or http://archives.postgresql.org/pgsql-hackers/2009-03/msg00607.php in which alternative limits are discussed; I only noticed later that it was possible to increase the limit to 1GB for both keys and values without using extra space.) Tom Perhaps an appropriate thing to do is separate out the Tom representation change from the other new features, and apply Tom just the latter for now. Or maybe we should think about having Tom two versions of hstore. Both of those options suck (and I don't believe either would suit users of the code). I'm prepared to give slightly more consideration to option #3: make the new code read the old format as well as the new one. I believe (though I have not yet tested) that it is possible to reliably distinguish the two with relatively low overhead, though the overhead would be nonzero, and do an in-core format conversion (which would result in writing out the new format if anything changed). -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] revised hstore patch
Andrew Gierth and...@tao11.riddles.org.uk writes: (b) many of the old names are significant collision risks. What collision risks? PG does not allow loadable libraries to export their symbols, so I don't see the problem. I recommend just keeping those things named as they were. Certainly when developing this I had _SIGNIFICANT_ encouragement, some of it from YOU, for increasing the limit. Yes, but that was before the interest in in-place upgrade went up. This patch is the first place where we have to decide whether we meant it when we said we were going to pay more attention to that. I'm prepared to give slightly more consideration to option #3: make the new code read the old format as well as the new one. If you think you can make that work, it would solve the problem. 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] revised hstore patch
On Wed, 2009-07-22 at 01:06 +0100, Andrew Gierth wrote: I'm prepared to give slightly more consideration to option #3: make the new code read the old format as well as the new one. I believe (though I have not yet tested) that it is possible to reliably distinguish the two with relatively low overhead, though the overhead would be nonzero, and do an in-core format conversion (which would result in writing out the new format if anything changed). It might be good to have a way to ensure that all values have been upgraded to the new format. Otherwise you have to permanently maintain the old format even across multiple upgrades. Maybe that's not a big deal, but I thought I'd bring it up. 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] Patch for contains/overlap of polygons
Teodor Sigaev teo...@sigaev.ru writes: http://www.sigaev.ru/misc/polygon-0.2.gz Patch fixes wrong algorithms of contains and overlap operations over polygons. Regression tests contain an examples of such polygons. Looks sane in a quick once-over. Please commit. 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] revised hstore patch
Tom == Tom Lane t...@sss.pgh.pa.us writes: (b) many of the old names are significant collision risks. Tom What collision risks? PG does not allow loadable libraries to Tom export their symbols, so I don't see the problem. I recommend Tom just keeping those things named as they were. You've never tested this, I can tell. I specifically checked this point, back when working on the original proposal (and when debugging the uuid code on freebsd, where uuid-ossp crashes due to a symbol collision). If a loaded module compiled from multiple source files defines some symbol, and another similar loaded module tries to define the same symbol, then whichever one gets loaded second will end up referring to the one from the first, obviously causing hilarity to ensue. I have a test case that demonstrates this and everything: % bin/psql -c 'select foo()' postgres NOTICE: mod1a foo() called NOTICE: mod1b bar() called foo - (1 row) % bin/psql -c 'select baz()' postgres NOTICE: mod2a baz() called NOTICE: mod2b bar() called baz - (1 row) % bin/psql -c 'select baz(),foo()' postgres NOTICE: mod2a baz() called NOTICE: mod2b bar() called NOTICE: mod1a foo() called NOTICE: mod2b bar() called baz | foo -+- | (1 row) % bin/psql -c 'select foo(),baz()' postgres NOTICE: mod1a foo() called NOTICE: mod1b bar() called NOTICE: mod2a baz() called NOTICE: mod1b bar() called foo | baz -+- | (1 row) Notice that in the third case, foo() called the bar() function in mod2b, not the one in mod1b which it called in the first case. All modules are compiled with pgxs and no special options. Certainly when developing this I had _SIGNIFICANT_ encouragement, some of it from YOU, for increasing the limit. Tom Yes, but that was before the interest in in-place upgrade went up. Tom This patch is the first place where we have to decide whether we meant Tom it when we said we were going to pay more attention to that. I'm prepared to give slightly more consideration to option #3: make the new code read the old format as well as the new one. Tom If you think you can make that work, it would solve the problem. OK. Should I produce an additional patch, or re-do the original one? -- 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] revised hstore patch
Jeff == Jeff Davis pg...@j-davis.com writes: I'm prepared to give slightly more consideration to option #3: make the new code read the old format as well as the new one. I believe (though I have not yet tested) that it is possible to reliably distinguish the two with relatively low overhead, though the overhead would be nonzero, and do an in-core format conversion (which would result in writing out the new format if anything changed). Jeff It might be good to have a way to ensure that all values have Jeff been upgraded to the new format. Otherwise you have to Jeff permanently maintain the old format even across multiple Jeff upgrades. Maybe that's not a big deal, but I thought I'd bring Jeff it up. Running ALTER TABLE foo ALTER COLUMN bar TYPE hstore USING bar || ''; on all of your hstore columns would suffice to ensure that, I believe. (Subject to testing once I actually have code for it, of course.) -- Andrew (irc:RhodiumToad) -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] revised hstore patch
* Andrew Gierth (and...@tao11.riddles.org.uk) wrote: Running ALTER TABLE foo ALTER COLUMN bar TYPE hstore USING bar || ''; on all of your hstore columns would suffice to ensure that, I believe. (Subject to testing once I actually have code for it, of course.) This could/would be included in pg_migrator then, I would think.. Stephen signature.asc Description: Digital signature
Re: [HACKERS] revised hstore patch
On Tue, Jul 21, 2009 at 7:25 PM, Tom Lanet...@sss.pgh.pa.us wrote: Or maybe we should think about having two versions of hstore. This is all tied up in the problem of having a decent module infrastructure (which I hope somebody is working on for 8.5). A decent module infrastructure is probably not going to fix this problem unless it links with -ldwiw. There are really only two options here: - Keep the old version around for compatibility and add a new version that isn't compatible, plus provide a migration path from the old version to the new version. - Make the new version read the format written by the old version. (I am also not aware that anyone is working on the module infrastructure problem, though of course that doesn't mean that no-one is; but the point is that's neither here nor there as respects the present problem. The module infrastructure is just a management layer around the same underlying issues.) ...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] Duplicate key value error
Dickson S. Guedes lis...@guedesoft.net wrote: Hum, for key names ok, but for values, wouldn't this worse the output when it is greater than 512 bytes? Why do we need to cut values in 512 bytes? It might be generate larger logs before, but we don't have any limits for length of log messages. ex) ERROR: ... STATEMENT: (very long query without length-limit) Also, we cannot use so long keys because length of key must be less then BLCKSZ. Error messages is 256kB at longest (INDEX_MAX_KEYS=32 * BLKCSZ=8kB), so I think it is not so dangerous. Regards, --- ITAGAKI Takahiro 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] revised hstore patch
Andrew Gierth wrote: The code already has users who are using it for audit-trail stuff (easily computing the changes between old and new records and storing only the differences). Perhaps one of the existing users could express an opinion on this point. I use it for exactly that purpose (and it works extremely well). I'm not sure we have any values 64k, though, and certainly our keys are tiny - they are all column names. OTOH, that could well be an annoying limitation, and would be easily breached if the changed field were some binary object like an image or a PDF. I rather like your idea of doing a convert-on-write, if you can reliably detect that the data is in the old or new format. 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] generic explain options v3
On Tue, Jul 21, 2009 at 7:47 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: Here is an updated version of my generic options for explain patch. What is the rationale for essentially duplicating defGetBoolean()? I just didn't realize we already had something along those lines. Updated patch attached, to which I've also applied Andres Freund's parser changes, suggested here: http://archives.postgresql.org/pgsql-hackers/2009-07/msg01213.php Also, I'd suggest changing the ExplainStmt struct to have a list of DefElem options instead of hard-wiring the option set at that level. What is the advantage of that? ...Robert *** a/contrib/auto_explain/auto_explain.c --- b/contrib/auto_explain/auto_explain.c *** *** 14,19 --- 14,20 #include commands/explain.h #include executor/instrument.h + #include nodes/makefuncs.h #include utils/guc.h PG_MODULE_MAGIC; *** *** 196,207 explain_ExecutorEnd(QueryDesc *queryDesc) msec = queryDesc-totaltime-total * 1000.0; if (msec = auto_explain_log_min_duration) { StringInfoData buf; initStringInfo(buf); ! ExplainPrintPlan(buf, queryDesc, ! queryDesc-doInstrument auto_explain_log_analyze, ! auto_explain_log_verbose); /* Remove last line break */ if (buf.len 0 buf.data[buf.len - 1] == '\n') --- 197,210 msec = queryDesc-totaltime-total * 1000.0; if (msec = auto_explain_log_min_duration) { + ExplainStmt *stmt = makeExplain(NIL, NULL); StringInfoData buf; initStringInfo(buf); ! stmt-analyze = ! (queryDesc-doInstrument auto_explain_log_analyze); ! stmt-verbose = auto_explain_log_verbose; ! ExplainPrintPlan(buf, queryDesc, stmt); /* Remove last line break */ if (buf.len 0 buf.data[buf.len - 1] == '\n') *** a/doc/src/sgml/ref/explain.sgml --- b/doc/src/sgml/ref/explain.sgml *** *** 31,36 PostgreSQL documentation --- 31,37 refsynopsisdiv synopsis + EXPLAIN [ ( [ { ANALYZE | VERBOSE | COSTS } [ replaceable class=parameterboolean_value/replaceable ] ] [, ...] ) ] replaceable class=parameterstatement/replaceable EXPLAIN [ ANALYZE ] [ VERBOSE ] replaceable class=parameterstatement/replaceable /synopsis /refsynopsisdiv *** *** 70,75 EXPLAIN [ ANALYZE ] [ VERBOSE ] replaceable class=parameterstatement/replac --- 71,86 are close to reality. /para + para +Only the literalANALYZE/literal and literalVERBOSE/literal options +can be specified, and only in the order, without surrounding the option list +in parentheses. Prior to productnamePostgreSQL/productname 8.5, the +unparenthesized syntax was the only one supported. It is expected that +all new options will be supported only when using the parenthesized syntax, +which also allows a value to be specified for each option +(e.g. literalTRUE/literal or literalFALSE/literal). + /para + important para Keep in mind that the statement is actually executed when *** *** 99,105 ROLLBACK; termliteralANALYZE/literal/term listitem para ! Carry out the command and show the actual run times. /para /listitem /varlistentry --- 110,117 termliteralANALYZE/literal/term listitem para ! Carry out the command and show the actual run times. This ! parameter defaults to commandFALSE/command. /para /listitem /varlistentry *** *** 108,114 ROLLBACK; termliteralVERBOSE/literal/term listitem para ! Include the output column list for each node in the plan tree. /para /listitem /varlistentry --- 120,151 termliteralVERBOSE/literal/term listitem para ! Include the output column list for each node in the plan tree. This ! parameter defaults to commandFALSE/command. ! /para ! /listitem !/varlistentry ! !varlistentry ! termliteralCOSTS/literal/term ! listitem ! para ! Include information on the estimated startup and total cost of each ! plan node, as well as the estimated number of rows and the estimated ! width of each row. This parameter defaults to commandTRUE/command. ! /para ! /listitem !/varlistentry ! !varlistentry ! termreplaceable class=parameter /boolean_value/replaceable/term ! listitem ! para ! Specifies whether the named parameter should be turned on or off. You ! can use the values literalTRUE/literal or literal1/literal to ! request the stated option, and literalFALSE/literal ! or literal0/literal. If the Boolean value is omitted, it defaults ! to literalTRUE/literal. /para /listitem /varlistentry *** *** 202,207 EXPLAIN SELECT * FROM foo WHERE i = 4; --- 239,258 /para
Re: [HACKERS] Modifying TOAST_TUPLE_THRESHOLD and TOAST_TUPLE_TARGET?
Kevin Grittner kevin.gritt...@wicourts.gov writes: I wrote: Proposed patch attached. That first version was of the minimally invasive variety, to stress how little I was changing and minimize the chance that I would make some dumb error; however, it involved copy/paste of a few lines which were already in a source file twice. Attached is what I hope is a functionally identical patch, with minor refactoring. I think it results in more readable code. Applied with some editorialization on the comments. I also tweaked the user-facing TOAST documentation thusly: *** doc/src/sgml/storage.sgml 17 Jun 2009 21:58:49 - 1.29 --- doc/src/sgml/storage.sgml 22 Jul 2009 01:17:23 - *** *** 347,353 literalMAIN/literal allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other ! way to make the row small enough.) /para /listitem /itemizedlist --- 347,353 literalMAIN/literal allows compression but not out-of-line storage. (Actually, out-of-line storage will still be performed for such columns, but only as a last resort when there is no other ! way to make the row small enough to fit on a page.) /para /listitem /itemizedlist 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] Sampling profiler updated
Tom Lane t...@sss.pgh.pa.us wrote: For the record, I think this patch is a waste of manpower and we should rely on dtrace/systemtap. However, if we are going to make our own homegrown substitute for those facilities, a minimum requirement should be that it uses the dtrace macros already put into the sources, rather than expecting that it gets to clutter the code some more with its own set of tracing markers. How about export dtrace functions as hook function pointers? For example: void (*LWLOCK_WAIT_START_hook)(int, int); #define TRACE_POSTGRESQL_LWLOCK_WAIT_START(INT1, INT2) \ if (LWLOCK_WAIT_START_hook == NULL); else \ LWLOCK_WAIT_START_hook(INT1, INT2) #define TRACE_POSTGRESQL_LWLOCK_WAIT_START_ENABLED() \ (LWLOCK_WAIT_START_hook != NULL) If there were such hooks, my profiler could be implemented as a loadable module on top of the hooks. It might be good to initialize LWLOCK_WAIT_START_hook with lwlock__wait__start(). If do so, dtrace probes still work and we can avoid if-null checks for each call. If acceptable, I'll also suggest new probe functions like SLEEP, SEND, RECV, SPINLOCK_FAILURE and so on. Regards, --- ITAGAKI Takahiro 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] Higher TOAST compression.
Kevin Grittner kevin.gritt...@wicourts.gov writes: It seems like it might be reasonable to have a separate threshold for compression from that for out-of-line storage. Since I've been in that code recently, I would be pretty comfortable doing something about that; but, as is so often the case, the problem will probably be getting agreement on what would be a good change. Ignoring for a moment the fact that low hanging fruit in the form of *very* large values can be handled first, the options would seem to be: (1) Just hard-code a lower default threshold for compression than for out-of-line storage. (2) Add a GUC or two to control thresholds. (3) Allow override of the thresholds for individual columns. I'm not clear how this would work. The toast code is designed around hitting a target for the overall tuple size; how would it make sense to treat compression and out-of-lining differently? And especially, how could you have per-column targets? I could see having a reloption that allowed per-table adjustment of the target tuple width... 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] generic explain options v3
Robert Haas robertmh...@gmail.com writes: On Tue, Jul 21, 2009 at 7:47 PM, Tom Lanet...@sss.pgh.pa.us wrote: Also, I'd suggest changing the ExplainStmt struct to have a list of DefElem options instead of hard-wiring the option set at that level. What is the advantage of that? Fewer places to change when you add a new option; in particular, not copyfuncs or equalfuncs. Also, the way you are doing it is gratuitously unlike every other command that has similar issues to deal with. Everybody else parses their DefElem list at execution time. I think you should have the legacy ANALYZE and VERBOSE syntax elements generate DefElem list members that get examined at execution. BTW, I see that your explain refactoring patch is marked ready for committer, but is it actually sane to apply it before the other two? 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] generic explain options v3
On Tue, Jul 21, 2009 at 10:05 PM, Tom Lanet...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Jul 21, 2009 at 7:47 PM, Tom Lanet...@sss.pgh.pa.us wrote: Also, I'd suggest changing the ExplainStmt struct to have a list of DefElem options instead of hard-wiring the option set at that level. What is the advantage of that? Fewer places to change when you add a new option; in particular, not copyfuncs or equalfuncs. Also, the way you are doing it is gratuitously unlike every other command that has similar issues to deal with. Everybody else parses their DefElem list at execution time. I think you should have the legacy ANALYZE and VERBOSE syntax elements generate DefElem list members that get examined at execution. Not having to touch copyfuncs or equalfuncs for future options is a definite plus, so I'll rework along these lines. BTW, I see that your explain refactoring patch is marked ready for committer, but is it actually sane to apply it before the other two? I think so. It's all code cleanup, with no behavioral changes, and is intended to contain only the stuff that seemed to me as thought it would still be worth doing even if the rest of the patch set were rejected. ...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] CommitFest 2009-07 - End of Week 1
A brief update on our progress so far. We started with 71 patches of which 12 have been committed, 9 returned with feedback, 2 rejected, and 1 bumped to the next CommitFest. In other words, we've closed out just over a third of the total number of patches in the first week, which is great progress. Many of the other patches are in the process of being reviewed - either a review has already been posted and the patch author is in the process of reworking it, or the reviewer has begun reviewing but has not yet completed it. In order to make it easier to get this status information, I've added a quick summary line to the top of the CommitFest status page. https://commitfest.postgresql.org/action/commitfest_view/inprogress There are a few patches that have some level of committer endorsement from previous discussions, and it's not clear whether any round-robin review is required. These are: Fix memory leak in win32 security functions (Magnus) do_tup_output_datum v2 (Tom) These might be worth a quick look by the respective committers to see whether these can just go in. If not, we'll assign round-robin reviewers. A couple of other patches that seem to be problem cases are: - Security Enhanced PostgreSQL. I don't think that there is any way that this is going to be committable for this CommitFest, but I'm willing to review it one more time this CommitFest before moving on. At a minimum, I hope the discussion has made it clear that getting this patch where it needs to me is going to take a lot of work, and the fact that KaiGai was until yesterday the only one willing to do any of that work is a significant problem. - Indexam API changes, Index-only quals. These seem likely really important patches, but it appears that they're not really done, and Heikki is apparently on vacation (I'm not sure until when). It may be necessary to bump these to the next CommitFest. Thanks to all who have been helping with the review process. Please remember to add reviews to the commitfest app and update the status of the patch as well. Thanks, ...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] Hadoop backend?
Paul Sheer wrote: Hadoop backend for PostGreSQL Resurrecting an old thread, it seems some guys at Yale implemented something very similar to what this thread was discussing. http://dbmsmusings.blogspot.com/2009/07/announcing-release-of-hadoopdb-longer.html It's an open source stack that includes PostgreSQL Hadoop, and Hive, along with some glue between PostgreSQL and Hadoop, a catalog, a data loader, and an interface that accepts queries in MapReduce or SQL and generates query plans that are processed partly in Hadoop and partly in different PostgreSQL instances spread across many nodes in a shared-nothing cluster of machines. Their detailed paper is here: http://db.cs.yale.edu/hadoopdb/hadoopdb.pdf According to the paper, it scales very well. A problem that my client has, and one that I come across often, is that a database seems to always be associated with a particular physical machine, a physical machine that has to be upgraded, replaced, or otherwise maintained. Even if the database is replicated, it just means there are two or more machines. Replication is also a difficult thing to properly manage. With a distributed data store, the data would become a logical object - no adding or removal of machines would affect the data. This is an ideal that would remove a tremendous maintenance burden from many sites well, at least the one's I have worked at as far as I can see. Does anyone know of plans to implement PostGreSQL over Hadoop? Yahoo seems to be doing this: http://glinden.blogspot.com/2008/05/yahoo-builds-two-petabyte-postgresql.html But they store tables column-ways for their performance situation. If one is doing a lot of inserts I don't think this is most efficient - ? Has Yahoo put the source code for their work online? Many thanks for any pointers. -paul -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CommitFest 2009-07 - End of Week 1
Robert Haas robertmh...@gmail.com writes: There are a few patches that have some level of committer endorsement from previous discussions, and it's not clear whether any round-robin review is required. These are: Fix memory leak in win32 security functions (Magnus) do_tup_output_datum v2 (Tom) These might be worth a quick look by the respective committers to see whether these can just go in. If not, we'll assign round-robin reviewers. Roger, I'll look at do_tup_output_datum in the morning, but I think it's noncontroversial. Magnus just got back from sailing and presumably will catch up on his commitments in due course ... 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] pg_dump Add dumping of comments on index columns
2009/7/14 Jaime Casanova jcasa...@systemguards.com.ec: On Thu, Mar 26, 2009 at 2:39 AM, higeponhige...@gmail.com wrote: Here is a patch for pg_dump Commenting on a composite-type column. This patch is for Todo item named Add dumping of comments on index columns and composite type columns. this one looks good to me, the only adjust i made to the patch is change the name for the function that dump the comments from the composite types columns for: dumpCompositeTypeColsComment that seems more clearer to me... the patch works just fine... Oops. I picked this patch up from the commitfest queue because it was still marked as Needs Review, not realising that Jaime had already done an initial review of the patch. Seems like this one should have been Ready for Committer, but no matter. I've also done an initial review of the patch. Everything looks sane and the patch works as advertised. I made a couple of minor tweaks for code-style and comment consistency, and my version 3 is attached. I'm marking this patch Ready for Committer. I've also added a TODO item forbid COMMENT on columns of an index, per Tom's comments upthread. Cheers, BJ pg_dump_composite_type_v3.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers