Re: [HACKERS] Prepping to break every past release...
Simon Riggs wrote: The most consistent negative feedback I receive about Postgres is that we make minor changes from release to release that make it extremely difficult to upgrade without re-testing the applications. So we write great software, then make it difficult for people to upgrade to it. Then I would maintain that part of that makes the software great is that we have the ability to make incompatible changes once in a while, avoiding the accumulation of cruft. We do maintain old releases for 5 years as compensation. I did propose a deprecation policy that would address your concern to some degree by issuing warnings in release N-1, so the testing after upgrade can be taken care of for the most part by hunting down these warnings while running the previous release. That didn't receive universal support, but I think we should still look for a compromise in that area. The argument against was that this would slow down PostgreSQL development too much. And note that the one-year major release cycle of PostgreSQL is already pretty much the shortest one of any software of this complexity. So everyone has different expectations, it seems. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gcc: why optimize for size flag is not the default
Hi, I was wondering why -Os is not used in place of -O2 while compiling the Postgres sources with gcc. I prepared 2 install directories by respectively using -Os and -O2 flags and in the former case it seems to reduce the install footprint by about 1MB or so. Agreed this is not significant for normal systems. But I was wondering if there is a performance reason too for not using -Os. Regards, Nikhils -- http://www.enterprisedb.com
Re: [HACKERS] gcc: why optimize for size flag is not the default
Nikhil Sontakke wrote: I was wondering why -Os is not used in place of -O2 while compiling the Postgres sources with gcc. I prepared 2 install directories by respectively using -Os and -O2 flags and in the former case it seems to reduce the install footprint by about 1MB or so. Agreed this is not significant for normal systems. But I was wondering if there is a performance reason too for not using -Os. -Os disables optimizations that make the code run faster, like loop unrolling. There's no free lunch. -- Heikki Linnakangas 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] Prepping to break every past release...
On Wed, 2009-03-11 at 08:33 +0200, Peter Eisentraut wrote: Simon Riggs wrote: The most consistent negative feedback I receive about Postgres is that we make minor changes from release to release that make it extremely difficult to upgrade without re-testing the applications. So we write great software, then make it difficult for people to upgrade to it. Then I would maintain that part of that makes the software great is that we have the ability to make incompatible changes once in a while, avoiding the accumulation of cruft. We do maintain old releases for 5 years as compensation. Please remember I'm just the messenger, passing on client feedback. It hasn't ever been my way to act this way, but the reality is that difficult upgrades make for more consulting income. The cost to the client is much higher because of re-test costs, difficulty in supporting applications across different sites running different PG releases and general delay. We're getting very good at doing upgrades now... I did propose a deprecation policy that would address your concern to some degree by issuing warnings in release N-1, so the testing after upgrade can be taken care of for the most part by hunting down these warnings while running the previous release. That didn't receive universal support, but I think we should still look for a compromise in that area. I agree with the need for a deprecation policy or approach to this issue. I think that particular deprecation policy was too strong, but where possible, it would be good to have a way to avoid niggly changes of behaviour. We have done that sometimes, e.g. sort_mem is now a synonym for work_mem, just not consistently. An example solution might be a parameter that allowed us to act like the previous release in some aspects. A parameter for every behaviour change would be bad because that's just another minefield to cross. The first step is to record incompatibilities as they occur and record them somewhere, so that people can say that'll break my app. Often the first people hear about these things is when we compile the release notes, which is far too late either to complain or to fix. The argument against was that this would slow down PostgreSQL development too much. And note that the one-year major release cycle of PostgreSQL is already pretty much the shortest one of any software of this complexity. You know I would not agree to that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
KaiGai Kohei wrote: * ACL_SELECT_FOR_UPDATE has same value with ACL_UPDATE, so SE-PostgreSQL checks db_table:{update} permission on SELECT ... FOR SHARE OF, instead of db_table:{lock} permission. This again falls into the category of trying to have more fine-grained permissions than vanilla PostgreSQL has. Just give up on the lock permission, and let it check update permission instead. Yes, it can be annoying that you need update-permission to do SELECT FOR SHARE, but that's an existing problem and not in scope for this patch. -- Heikki Linnakangas 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
Heikki Linnakangas wrote: KaiGai Kohei wrote: * ACL_SELECT_FOR_UPDATE has same value with ACL_UPDATE, so SE-PostgreSQL checks db_table:{update} permission on SELECT ... FOR SHARE OF, instead of db_table:{lock} permission. This again falls into the category of trying to have more fine-grained permissions than vanilla PostgreSQL has. Just give up on the lock permission, and let it check update permission instead. Yes, it can be annoying that you need update-permission to do SELECT FOR SHARE, but that's an existing problem and not in scope for this patch. Can I consider the term of problem means it can be resolved in the future (v8.5, if possible) version? -- KaiGai Kohei kai...@kaigai.gr.jp -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1710)
KaiGai Kohei wrote: Heikki Linnakangas wrote: KaiGai Kohei wrote: * ACL_SELECT_FOR_UPDATE has same value with ACL_UPDATE, so SE-PostgreSQL checks db_table:{update} permission on SELECT ... FOR SHARE OF, instead of db_table:{lock} permission. This again falls into the category of trying to have more fine-grained permissions than vanilla PostgreSQL has. Just give up on the lock permission, and let it check update permission instead. Yes, it can be annoying that you need update-permission to do SELECT FOR SHARE, but that's an existing problem and not in scope for this patch. Can I consider the term of problem means it can be resolved in the future (v8.5, if possible) version? Sure, a patch to address that in 8.5 would be welcome. I don't know why it's like that. Maybe no-one has just bothered. Or maybe it's because of backwards-compatibility or SQL standard compliance. In any case, it would seem useful to separate them in the future. -- Heikki Linnakangas 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
[HACKERS] idea, proposal: only preloadable libraries (conditional load)
Hello I am searching way to extensible parser (actually not bison, only transformations). I propose parserHook (transformation part). One Tom's objection is difference between heooked and unhooked parser. It serious problem. I have one idea - only preloadable libraries. These libs have to be specified in local_preload_libraries variable, and cannot be initialised by LOAD statement. Statement LOAD should by used only for reload library. Because pre loaded library is initialised before first SQL statement should be executed, then hook is installed on time and an behave is consistent. One technique is an calling library's function canload before initialising. When canload returns false, then dfmgr unloads lib. I thing so this behave complements current functions PG_init and PG_finit. What I can understand, PG_init, cannot throw exception or signalise any problems with initialisation. Ideas, objections? regards Pavel Stehule -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: KaiGai Kohei wrote: * ACL_SELECT_FOR_UPDATE has same value with ACL_UPDATE, so SE-PostgreSQL checks db_table:{update} permission on SELECT ... FOR SHARE OF, instead of db_table:{lock} permission. This again falls into the category of trying to have more fine-grained permissions than vanilla PostgreSQL has. Just give up on the lock permission, and let it check update permission instead. Yes, it can be annoying that you need update-permission to do SELECT FOR SHARE, but that's an existing problem and not in scope for this patch. Would it make sense to instead of removing and deferring pieces bit by bit to instead work the other way around? Extract just the part of the patch that maps SELinux capabilities to Postgres privileges as a first patch? Then discuss any other parts individually at a later date? That might relieve critics of the sneaking suspicion that there may be some semantic change that hasn't been identified and discussed and snuck through? Some of them are probably good ideas but if they are they're probably good ideas even for non-SE semantics too. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS support! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Has anybody think about changing BLCKSZ to an option of initdb?
After all, re-initdb is much easier than re-build the whole package. And there seems nothing diffcult to implement this. Is that true? -- 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] Has anybody think about changing BLCKSZ to an option of initdb?
Jacky Leng lengjianq...@163.com writes: And there seems nothing diffcult to implement this. Is that true? No. 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] gcc: why optimize for size flag is not the default
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Nikhil Sontakke wrote: I was wondering why -Os is not used in place of -O2 while compiling the Postgres sources with gcc. There's no free lunch. In any case, this sort of choice is generally something that ought to be applied at a distro level. If, say, Fedora or Debian chose to use -Os uniformly across all their packages, then there might be a meaningful amount of space saved in the aggregate. As far as I know, though, -Os is not the preferred choice in any distro, which ought to tell you something ... 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] idea, proposal: only preloadable libraries (conditional load)
On Wed, 2009-03-11 at 11:09 +0100, Pavel Stehule wrote: I am searching way to extensible parser (actually not bison, only transformations). I propose parserHook (transformation part). One Tom's objection is difference between heooked and unhooked parser. It serious problem. Do you mean hooking the whole parser? That sounds more useful and less hassle than trying to hook parts of it. That would be just one check to see if the hook exists per statement, rather than potentially thousands of times per statement. I'd go for an implementation that uses pg_language to store new languages, just with lanispl = false. We can then have a new parameter session_language (TEXT) with 'internal' as default. session_language cannot be reset while connected. That would allow us to have multiple session languages in use at one time and to add new ones (or modify existing ones) without changing core behaviour. In the longer term it will be very useful to have the ability to support multiple language variants, including older PostgreSQL syntax to allow legacy systems to work with Postgres at the same time as allowing new development to continue. I have one idea - only preloadable libraries. These libs have to be specified in local_preload_libraries variable, and cannot be initialised by LOAD statement. Statement LOAD should by used only for reload library. Because pre loaded library is initialised before first SQL statement should be executed, then hook is installed on time and an behave is consistent. One technique is an calling library's function canload before initialising. When canload returns false, then dfmgr unloads lib. I thing so this behave complements current functions PG_init and PG_finit. What I can understand, PG_init, cannot throw exception or signalise any problems with initialisation. I remember I had some differences between the way loading occurs at session start and as a result of a LOAD command. I think there's probably already a way of doing this - probably by checking for something that would only be there *after* having read reloadable libraries but before main session starts. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Has anybody think about changing BLCKSZ to an option of initdb?
On Wed, Mar 11, 2009 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: And there seems nothing diffcult to implement this. Is that true? No. Eh? There's nothing difficult in implementing it. But there are a lot of other constants dependant on this value which are currently compile-time constants. The only downside I'm aware of is that with this change they become dynamically calculated values which might have a cpu cost since they'll be recalculated quite often. -- greg -- 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] gcc: why optimize for size flag is not the default
On Wed, Mar 11, 2009 at 12:42 PM, Tom Lane t...@sss.pgh.pa.us wrote: As far as I know, though, -Os is not the preferred choice in any distro, which ought to tell you something ... Unless of course you include distributions like ucLinux or emDebian which only proves the point. -- greg -- 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] Has anybody think about changing BLCKSZ to an option of initdb?
Greg Stark st...@enterprisedb.com writes: On Wed, Mar 11, 2009 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: And there seems nothing diffcult to implement this. Is that true? No. Eh? There's nothing difficult in implementing it. But there are a lot of other constants dependant on this value which are currently compile-time constants. Exactly, and we rely on them being constants, eg to size arrays. There's no free lunch, and in this particular case there is no evidence whatsoever that it'd be worth the trouble to support run-time-variable BLCKSZ. 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] idea, proposal: only preloadable libraries (conditional load)
On Wed, Mar 11, 2009 at 12:56 PM, Simon Riggs si...@2ndquadrant.com wrote: In the longer term it will be very useful to have the ability to support multiple language variants, including older PostgreSQL syntax to allow legacy systems to work with Postgres at the same time as allowing new development to continue. So I think having multiple parsers for different versions of Pg backwards compatibility is an awful idea. It would be a huge maintenance headache since every time we change a structure that the parser works someone would have to maintain all those compatibility parsers. And it's very rare that we make non-backwards compatible changes to the grammar with the exception of adding new reserved keywords. However that last thought led me to an interesting idea. We could fairly easily support SQL which used keywords which we later reserved. We could do this by marking each keyword in keywords.c with a version number that introduced it. Then have a guc which tells the lexer which version to target -- any keywords introduced after the desired version can just be passed up as regular urecognized identifiers. That would allow us to add new keywords more freely -- I think still not liberally since we would rather people not be forced to decide between new features and a working application. Hm, actually I see a fly in the ointment -- we often upgrade keywords from one kind of reservedness to another. That would mean we wouldn't be able to handle something like WITH which was previously some flavour of unreserved keyword and later became reserved. -- greg -- 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] Has anybody think about changing BLCKSZ to an option of initdb?
On Wed, Mar 11, 2009 at 1:13 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@enterprisedb.com writes: On Wed, Mar 11, 2009 at 12:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: And there seems nothing diffcult to implement this. Is that true? No. Eh? There's nothing difficult in implementing it. But there are a lot of other constants dependant on this value which are currently compile-time constants. Exactly, and we rely on them being constants, eg to size arrays. There's no free lunch, and in this particular case there is no evidence whatsoever that it'd be worth the trouble to support run-time-variable BLCKSZ. The main advantage would be for circumstances such as the Windows installer where users are installing precompiled binaries. They don't get an opportunity to choose the block size at all. (Similarly for users of binary-only commercial products such as EDB's but the Windows installer makes a pretty good argument on its own). I think the question hinges on whether there's any real benefit to block size at all. The current situation is that the facility is available for people to test and demonstrate that it's helpful. But there are so many variables -- filesystem type, filesystem block size, raid array stripe size, OS readahead, database work-load -- that nobody's done that kind of testing extensively enough to separate the effects of block size from other effects. If we had a solid use case for adjusting block size at all I think we would also need to make it adjustable at initdb time for those binary-only installs. Until we do leaving the compile-time configuration in for people to experiment with is sufficient. -- greg -- 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] gcc: why optimize for size flag is not the default
On 3/11/09, Tom Lane t...@sss.pgh.pa.us wrote: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: Nikhil Sontakke wrote: I was wondering why -Os is not used in place of -O2 while compiling the Postgres sources with gcc. There's no free lunch. In any case, this sort of choice is generally something that ought to be applied at a distro level. If, say, Fedora or Debian chose to use -Os uniformly across all their packages, then there might be a meaningful amount of space saved in the aggregate. As far as I know, though, -Os is not the preferred choice in any distro, which ought to tell you something ... Linux kernel is moving to use -Os everywhere. AFAIK their argument is that kernel code should not be doing anything CPU-intensive, thus minimal cache usage is more important than unrolled loops. This also seems to hint that -Os is not really appropriate to Postgres. Although it would be good fit for eg. PgBouncer. -- marko -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
Gregory Stark escribió: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: KaiGai Kohei wrote: * ACL_SELECT_FOR_UPDATE has same value with ACL_UPDATE, so SE-PostgreSQL checks db_table:{update} permission on SELECT ... FOR SHARE OF, instead of db_table:{lock} permission. This again falls into the category of trying to have more fine-grained permissions than vanilla PostgreSQL has. Just give up on the lock permission, and let it check update permission instead. Yes, it can be annoying that you need update-permission to do SELECT FOR SHARE, but that's an existing problem and not in scope for this patch. Would it make sense to instead of removing and deferring pieces bit by bit to instead work the other way around? Extract just the part of the patch that maps SELinux capabilities to Postgres privileges as a first patch? Then discuss any other parts individually at a later date? I think that makes sense. Implement just a very basic core in a first patch, and start adding checks slowly, one patch each. We have talked about incremental patches in the past. We wouldn't get unbreakable PostgreSQL in a single commit, but we would at least start moving. The good thing about having started in the opposite direction is that by now we know that the foundation APIs are good enough to build the complete feature. -- 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] idea, proposal: only preloadable libraries (conditional load)
On Wed, 2009-03-11 at 13:14 +, Greg Stark wrote: On Wed, Mar 11, 2009 at 12:56 PM, Simon Riggs si...@2ndquadrant.com wrote: In the longer term it will be very useful to have the ability to support multiple language variants, including older PostgreSQL syntax to allow legacy systems to work with Postgres at the same time as allowing new development to continue. So I think having multiple parsers for different versions of Pg backwards compatibility is an awful idea. There are a number of people interested in producing open source compatibility layers. I realise you may not be one of them, but that's no reason to stop the idea from taking root. It would be a huge maintenance headache since every time we change a structure that the parser works someone would have to maintain all those compatibility parsers. If it's a plugin that someone isn't any concern of ours. External projects can keep up with releases, or specific customer implementations may simply choose to standardise on one release and go with that. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] idea, proposal: only preloadable libraries (conditional load)
2009/3/11 Simon Riggs si...@2ndquadrant.com: On Wed, 2009-03-11 at 11:09 +0100, Pavel Stehule wrote: I am searching way to extensible parser (actually not bison, only transformations). I propose parserHook (transformation part). One Tom's objection is difference between heooked and unhooked parser. It serious problem. Do you mean hooking the whole parser? That sounds more useful and less hassle than trying to hook parts of it. That would be just one check to see if the hook exists per statement, rather than potentially thousands of times per statement. No, now I want to add hook only to current parser - concretely to transformations, although I can imagine any hook over whole parser. It could help with modules, that adds non sql statements like show statistic, show ... , explain and others - all service statements, some extensions ... Nearest goal is support for some smart functions like decode, greatest, xmlelement, ... I'd go for an implementation that uses pg_language to store new languages, just with lanispl = false. We can then have a new parameter session_language (TEXT) with 'internal' as default. session_language cannot be reset while connected. That would allow us to have multiple session languages in use at one time and to add new ones (or modify existing ones) without changing core behaviour. In the longer term it will be very useful to have the ability to support multiple language variants, including older PostgreSQL syntax to allow legacy systems to work with Postgres at the same time as allowing new development to continue. I have one idea - only preloadable libraries. These libs have to be specified in local_preload_libraries variable, and cannot be initialised by LOAD statement. Statement LOAD should by used only for reload library. Because pre loaded library is initialised before first SQL statement should be executed, then hook is installed on time and an behave is consistent. One technique is an calling library's function canload before initialising. When canload returns false, then dfmgr unloads lib. I thing so this behave complements current functions PG_init and PG_finit. What I can understand, PG_init, cannot throw exception or signalise any problems with initialisation. I remember I had some differences between the way loading occurs at session start and as a result of a LOAD command. I think there's probably already a way of doing this - probably by checking for something that would only be there *after* having read reloadable libraries but before main session starts. I can test debug_query_string, but main problem is impossibility throw exception inside PG_init. regards Pavel Stehule -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Prepping to break every past release...
On Wed, 2009-03-11 at 08:41 +, Simon Riggs wrote: On Wed, 2009-03-11 at 08:33 +0200, Peter Eisentraut wrote: The first step is to record incompatibilities as they occur and record them somewhere, so that people can say that'll break my app. Often the first people hear about these things is when we compile the release notes, which is far too late either to complain or to fix. That is a simple modification of the release notes and something that really should be done regardless of a deprecation policy. Joshua D. Drake -- PostgreSQL - XMPP: jdr...@jabber.postgresql.org Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea, proposal: only preloadable libraries (conditional load)
On Wed, Mar 11, 2009 at 2:18 PM, Simon Riggs si...@2ndquadrant.com wrote: It would be a huge maintenance headache since every time we change a structure that the parser works someone would have to maintain all those compatibility parsers. If it's a plugin that someone isn't any concern of ours. External projects can keep up with releases, or specific customer implementations may simply choose to standardise on one release and go with that. That's not what I mean. I mean, for example, if someone adds a field to any of the structss in parsenodes.h to implement a new feature. The old parser would have to know how to initialize that field correctly to avoid triggering that new feature or trigger it in a manner compatible with the old version's implicit behaviour. The last few commits to that file include Tom's commit to handle ALTER TABLE SET WITHOUT OIDS, Alvaro's commit to handle reloptions with qualifiers, Stephen Frost's patch to support column-level privileges, Heikki's commit to handle vacuum_freeze_table_age, etc. Every one of these commits would have had to adjust every single old parser to generate the correct data for the changed nodes. The parser isn't a separable module interacting with the rest of the system through a static interface. It's closely in bed with the rest of the system implementing the syntax it's parsing. Every feature the parser can parse has to be communicated to the backend code implementing the feature so it has to have a corresponding knob in the interface between the parser and the rest of the system. -- greg -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea, proposal: only preloadable libraries (conditional load)
On Wed, 2009-03-11 at 14:45 +, Greg Stark wrote: On Wed, Mar 11, 2009 at 2:18 PM, Simon Riggs si...@2ndquadrant.com wrote: It would be a huge maintenance headache since every time we change a structure that the parser works someone would have to maintain all those compatibility parsers. If it's a plugin that someone isn't any concern of ours. External projects can keep up with releases, or specific customer implementations may simply choose to standardise on one release and go with that. That's not what I mean. I mean, for example, if someone adds a field to any of the structss in parsenodes.h to implement a new feature. The old parser would have to know how to initialize that field correctly to avoid triggering that new feature or trigger it in a manner compatible with the old version's implicit behaviour. The last few commits to that file include Tom's commit to handle ALTER TABLE SET WITHOUT OIDS, Alvaro's commit to handle reloptions with qualifiers, Stephen Frost's patch to support column-level privileges, Heikki's commit to handle vacuum_freeze_table_age, etc. Every one of these commits would have had to adjust every single old parser to generate the correct data for the changed nodes. The parser isn't a separable module interacting with the rest of the system through a static interface. It's closely in bed with the rest of the system implementing the syntax it's parsing. Every feature the parser can parse has to be communicated to the backend code implementing the feature so it has to have a corresponding knob in the interface between the parser and the rest of the system. It would be a matter for a plugin designer how they did that. If the new parser involved just some changes in specific areas, then presumably you would design it as a drop through parser: handle any special cases or drop through to normal Postgres parser. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] parallel restore fixes
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: OK, here 'tis. Looks fairly reasonable to me, but of course I haven't tested it. Well, I have to do a blitz of parallel restores next week, so hopefully that will hit any soft spots. --Josh -- 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] Out parameters handling
On 3/7/09, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sat, Mar 7, 2009 at 9:08 AM, Rod Taylor rod.tay...@gmail.com wrote: It wouldn't be so bad if you could assign internal and external column names. This is a good point. Uglifying the parameter names is sort of OK for input parameters, but is much more annoying for output parameters. How much of this pain would go away if we changed over to the arguably correct (as in Or*cle does it that way) scoping for names, wherein the parser first tries to match a name against column names of tables of the current SQL statement, and only failing that looks to see if they are plpgsql variables? It would decrease the frequency of problems, but the problems that will stay will be more obscure than before - currently you can guess how the query will be parsed by just looking at function code, but with oracle style parsing you need to know the table definitions also. So my vote would go to some sort of alias or record variable that contains either all arguments (so we can get rid of i_ prefixes) or only out/inout variables. This should be optional and user-chooseable at function start, so it can be tied with local coding style. This seems to fit better to SQL style of using table or column aliases to make name resolution clear. I don't have clear idea of syntax for that, some variants: DECLARE args ALIAS FOR IN|OUT|INOUT ARGS; args PREFIX FOR ARGS|OUTARGS|INARGS; ARGALIAS ret; RENAME funcname TO foo; The PREFIX seems best of those as it also hints that the args will not be available in plain form. Not sure what is the good way to specify the IN|OUT|INOUT. I'm not against the Oracle-style parsing, if the prefix solution is voted down, it will be the next best thing. But my problem with it is that it will make me actually less confident than current solution that I really understand what a piece of SQL will actually end up doing. Also it will introduce new ways to silent breakdowns: what if someone adds new column to table with same name as function argument? Btw - the prefix and the Oracle-style parsing are actually orthogonal to each other so we could also have both. -- marko -- 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] parallel restore fixes
Josh Berkus wrote: Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: OK, here 'tis. Looks fairly reasonable to me, but of course I haven't tested it. Well, I have to do a blitz of parallel restores next week, so hopefully that will hit any soft spots. I have a known outstanding bug to do with deadlock from FKs that cross (i.e. A has an FK that references B, and B has an FK that references A). The solution to this could be mildly complex, but I have an outline of it in my head. Workaround: recreate the failed FK at the end of the restore. The only other reported problem is the one on Unixware to do with closing the archive. I haven't been able to reproduce it on Linux or Windows, the two platforms I test on, although it might be fixed by the patch I just applied. 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
Alvaro Herrera wrote: Gregory Stark escribió: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: KaiGai Kohei wrote: * [..feature description..] This again falls into the category of trying to have more fine-grained permissions than vanilla PostgreSQL has Would it make sense to instead of removing and deferring pieces bit by bit to instead work the other way around? Extract just the part of the patch that maps SELinux capabilities to Postgres privileges as a first patch? Then discuss any other parts individually at a later date? I think that makes sense. Implement just a very basic core in a first patch, and start adding checks slowly, one patch each. We have talked about incremental patches in the past. +1 from an end-user's point of view too. I'm quite aware of the postgres privileges, and if there were a MAC system of enforcing those I'd be reasonably likely to enable them right away. On the other hand, if SEPostgres initially comes with a different set of privileges that don't map to what I'm already using, I'm much less likely to spend the time to figure out the two different systems. And I do see row-level restrictions (and the other access restrictions mentioned in this thread) as quite orthogonal to MAC vs DAC. Would it be cool to have row-level permissions in postgres? Sure, as an abstract concept. Do I have any use for them? Seeing that I'm getting by without them, the answer must be not now. We wouldn't get unbreakable PostgreSQL in a single commit, but we would at least start moving. The good thing about having started in the opposite direction is that by now we know that the foundation APIs are good enough to build the complete feature. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Updates of SE-PostgreSQL 8.4devel patches (r1710)
Alvaro Herrera alvhe...@commandprompt.com writes: Gregory Stark escribió: Would it make sense to instead of removing and deferring pieces bit by bit to instead work the other way around? Extract just the part of the patch that maps SELinux capabilities to Postgres privileges as a first patch? Then discuss any other parts individually at a later date? I think that makes sense. That's pretty much the advice we gave KaiGai-san in January ... which I gather he hasn't taken. 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] idea, proposal: only preloadable libraries (conditional load)
Pavel Stehule pavel.steh...@gmail.com writes: 2009/3/11 Simon Riggs si...@2ndquadrant.com: I remember I had some differences between the way loading occurs at session start and as a result of a LOAD command. I think there's probably already a way of doing this - probably by checking for something that would only be there *after* having read reloadable libraries but before main session starts. I can test debug_query_string, but main problem is impossibility throw exception inside PG_init. If we can't support throwing an error there, I think we need to fix that. There's no way a precheck function can completely guarantee that no error will happen in the real do it function; at least not for interesting values of do 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
Re: [HACKERS] gcc: why optimize for size flag is not the default
On 11 Mar 2009, at 13:51, Marko Kreen wrote: Linux kernel is moving to use -Os everywhere. AFAIK their argument is that kernel code should not be doing anything CPU-intensive, thus minimal cache usage is more important than unrolled loops. This also seems to hint that -Os is not really appropriate to Postgres. Although it would be good fit for eg. PgBouncer. while it might be right in case of linux kernel (which I won't agree totally with personally), I don't see any reason to compare it with postgresql. Kernel is extensively use by everything in system, hence their reasoning. Postgresql is an application. -- 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] gcc: why optimize for size flag is not the default
On Mar 11, 2009, at 3:18 PM, Grzegorz Jaskiewicz wrote: On 11 Mar 2009, at 13:51, Marko Kreen wrote: Linux kernel is moving to use -Os everywhere. AFAIK their argument is that kernel code should not be doing anything CPU-intensive, thus minimal cache usage is more important than unrolled loops. This also seems to hint that -Os is not really appropriate to Postgres. Although it would be good fit for eg. PgBouncer. while it might be right in case of linux kernel (which I won't agree totally with personally), I don't see any reason to compare it with postgresql. Kernel is extensively use by everything in system, hence their reasoning. Postgresql is an application. MacOS X defaults to and recommends -Os with the rationales that smaller code causes less paging and less CPU instruction cache thrashing. http://developer.apple.com/ReleaseNotes/DeveloperTools/RN-GCC3/index.html For deployment builds, the recommended setting is -Os, which produces the smallest possible binary size. Generally, a binary that's smaller is also faster. That's because a large application spends much of its time paging its binary code in and out of memory. The smaller the binary, the less the application needs to page. For example, say a binary uses aggressive function inlining. That binary saves time with fewer function calls, but it could easily spend far more time paging the binary code containing those inlined functions in and out of memory. -Os Optimize for size. -Os enables all -O2 optimizations that do not typically increase code size. It also performs further optimizations designed to reduce code size. -Os is still optimizing but using a slightly different heuristic as to what optimization means. That said, if postgresql is paging out, the DBA probably has postgresql or the server misconfigured. Cheers, M -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Broken stuff in new dtrace probes
I notice that we have in md.c TRACE_POSTGRESQL_SMGR_MD_READ_DONE(forknum, blocknum, reln-smgr_rnode.spcNode, reln-smgr_rnode.dbNode, reln-smgr_rnode.relNode, relpath(reln-smgr_rnode, forknum), nbytes, BLCKSZ); TRACE_POSTGRESQL_SMGR_MD_WRITE_DONE(forknum, blocknum, reln-smgr_rnode.spcNode, reln-smgr_rnode.dbNode, reln-smgr_rnode.relNode, relpath(reln-smgr_rnode, forknum), nbytes, BLCKSZ); relpath() returns a palloc'd string, which will not get freed, which means that any serious use of these probe points will shortly blow out the backend's memory. So far as I can see the path argument is redundant with the other probe arguments and we might as well just remove it --- objections? There's another problem in tuplesort.c: TRACE_POSTGRESQL_SORT_DONE(state-tapeset, (state-tapeset ? LogicalTapeSetBlocks(state-tapeset) : (state-allowedMem - state-availMem + 1023) / 1024)); This is called after state-tapeset has been freed, which means that the LogicalTapeSetBlocks call will very likely give a wrong answer, especially in assert-enabled builds but maybe even in a regular one. Seems that we need to have been quality-checking the dtrace patches a bit more carefully. 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] gcc: why optimize for size flag is not the default
A.M. wrote: That said, if postgresql is paging out, the DBA probably has postgresql or the server misconfigured. Keep in mind that paging in in this context also means moving stuff from plain RAM into cache. -- 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
[HACKERS] Should SET ROLE inherit config params?
All, I was just noticing that doing SET ROLE changes the current session's priviledges, but not any runtime configuration parameters (like work_mem or statement_timeout) associated with the new role. This is as documented (although I want to add a line to SET ROLE docs) but is it the behavior we want? I for one would like SET ROLE to change runtime configs. --Josh -- 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] Should SET ROLE inherit config params?
On Wed, 2009-03-11 at 14:27 -0700, Josh Berkus wrote: I was just noticing that doing SET ROLE changes the current session's priviledges, but not any runtime configuration parameters (like work_mem or statement_timeout) associated with the new role. This is as documented (although I want to add a line to SET ROLE docs) but is it the behavior we want? I for one would like SET ROLE to change runtime configs. Sounds good to me, but you may want to explore what problems that might cause so we can avoid screwing up. Perhaps it could be an option? -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and 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] Should SET ROLE inherit config params?
On Wed, Mar 11, 2009 at 9:45 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-03-11 at 14:27 -0700, Josh Berkus wrote: This is as documented (although I want to add a line to SET ROLE docs) but is it the behavior we want? I for one would like SET ROLE to change runtime configs. Sounds good to me, but you may want to explore what problems that might cause so we can avoid screwing up. Perhaps it could be an option? Well for one thing pg_dump uses SET ROLE extensively and it sets parameters assuming they'll stay set -- greg -- 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] Should SET ROLE inherit config params?
--On Mittwoch, März 11, 2009 21:45:00 + Simon Riggs si...@2ndquadrant.com wrote: This is as documented (although I want to add a line to SET ROLE docs) but is it the behavior we want? I for one would like SET ROLE to change runtime configs. Sounds good to me, but you may want to explore what problems that might cause so we can avoid screwing up. Perhaps it could be an option? I had exactly the same intention yesterday. Maybe something along the line of su - is what we want, thus expanding such a functionality with an optional argument to SET ROLE. -- 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] Broken stuff in new dtrace probes
I wrote: Seems that we need to have been quality-checking the dtrace patches a bit more carefully. I went over all the existing dtrace probe calls and fixed what seemed clearly bogus, but there was one issue that seems like a bit of a judgement call. In ReadBuffer_common() we have isExtend = (blockNum == P_NEW); /* Substitute proper block number if caller asked for P_NEW */ if (isExtend) blockNum = smgrnblocks(smgr, forkNum); TRACE_POSTGRESQL_BUFFER_READ_START(forkNum, blockNum, smgr-smgr_rnode.spcNode, smgr-smgr_rnode.dbNode, smgr-smgr_rnode.relNode, isLocalBuf); if (isLocalBuf) ... etc etc ... What's bothering me about this is that in the isExtend case, the potentially significant cost of the smgrnblocks() call is not going to get charged as part of the buffer read time. An easy answer is to move the TRACE call in front of that, which would mean that in the isExtend case the trace would see blockNum == P_NEW rather than the actual block number. You could argue it either way as to whether that's good or bad, perhaps -- it would make it a tad harder to match up read_start and read_done calls, but it would also make it clearer which calls are for file extension and which are ordinary reads. Furthermore, an isExtend call doesn't actually do a read(), so lumping them together with regular reads doesn't seem like quite the right thing for performance measurement purposes anyway. Maybe we actually ought to have different probes for isExtend and regular cases. Comments? regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken stuff in new dtrace probes
On Wed, Mar 11, 2009 at 11:50 PM, Tom Lane t...@sss.pgh.pa.us wrote: Furthermore, an isExtend call doesn't actually do a read(), so lumping them together with regular reads doesn't seem like quite the right thing for performance measurement purposes anyway. Maybe we actually ought to have different probes for isExtend and regular cases. i like the idea of just have a separate pair of probes for table extension. I bet there are people who would actually like to see that alone sometimes too. I'm sure these probes will be refined over time as we get more experience analyzing with them. They don't have to be perfect right away... -- greg -- 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
Ron Mayer wrote: Alvaro Herrera wrote: Gregory Stark escribió: Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: KaiGai Kohei wrote: * [..feature description..] This again falls into the category of trying to have more fine-grained permissions than vanilla PostgreSQL has Would it make sense to instead of removing and deferring pieces bit by bit to instead work the other way around? Extract just the part of the patch that maps SELinux capabilities to Postgres privileges as a first patch? Then discuss any other parts individually at a later date? I think that makes sense. Implement just a very basic core in a first patch, and start adding checks slowly, one patch each. We have talked about incremental patches in the past. +1 from an end-user's point of view too. I'm quite aware of the postgres privileges, and if there were a MAC system of enforcing those I'd be reasonably likely to enable them right away. On the other hand, if SEPostgres initially comes with a different set of privileges that don't map to what I'm already using, I'm much less likely to spend the time to figure out the two different systems. I cannot update whole of the wikipage yet, but updated some of descriptions in object classes and permission. http://wiki.postgresql.org/wiki/SEPostgreSQL#Object_classes_and_permission Some of permissions are mapped to the vanilla PostgreSQL privileges, and some of them are not so. * ACL_INSERT The db_table:{insert} and db_column:{insert} for all the target columns are checked. The table-level permission does not override column-level permission, so the client need to have privileges for both of objects. It is same as other permissions. * ACL_SELECT The db_table:{select} and db_column:{select} for all the target columns are checked. But it applies db_table:{lock} on LockTableCommand(). * ACL_UPDATE The db_table:{update} and db_column:{update} for all the target columns are checked. * ACL_DELETE The db_table:{delete} is also checked. No column-level checks here. * ACL_TRUNCATE The db_table:{delete} is also checked. SE-PostgreSQL does not discriminate between TRUNCATE and DELETE. * ACL_REFERENCES * ACL_TRIGGER SE-PostgreSQL does not care about these privileges. But, it checks db_procedure:{execute} on trigger invocation time, and it also checks db_table:{select} on checks of FK constraint within its secondary SQL execution. * ACL_EXECUTE The db_procedure:{execute} is also checked. This check is embedded within pg_proc_ackcheck(). * ACL_USAGE * ACL_CREATE * ACL_CREATE_TEMP SE-PostgreSQL does not care about there privileges. * ACL_CONNECT The db_database:{access} is also checked. This check is embedded within pg_database_aclcheck(). * ACL_SELECT_FOR_UPDATE The db_table:{lock} should be also checked, but ... * database superuser privilege The db_database:{superuser} newly added should be also checked. In addition, SE-PostgreSQL defines and users some of new privileges. * db_xxx:{relabelfrom} and db_xxx:{relabelto} It is checked when the security context of database objects are changed. * db_xxx:{create} It is typically checked when CREATE TABLE and others. SE-PostgreSQL assigns a default security context on the table and columns newly created, if user does not give any security context explicitly. Then, it checks whether the user have db_xxx:{create} privileges on the tables/columns/etc labeled as the security context, or not. * db_xxx:{setattr} * db_xxx:{drop} It is typically cheched when ALTER/DROP TABLE and others. The vanilla PostgreSQL checks user's privileges based on the ownership, but SE-PostgreSQL does not consider the concept of owner due to its MAC policy. These permission are checked based on the security context assigned to the target objects. * db_procedure:{entrypoint} SE-PostgreSQL allows client to change its privilege during execution of certain procedures (called as trusted procedure). It checks this permission when user tries to invoke trusted procedure. The vanilla PostgreSQL does not have similar ACL, but it concept it similar to security definer or setuid on operating system. And I do see row-level restrictions (and the other access restrictions mentioned in this thread) as quite orthogonal to MAC vs DAC. Would it be cool to have row-level permissions in postgres? Sure, as an abstract concept. Do I have any use for them? Seeing that I'm getting by without them, the answer must be not now. We defined six permissions for row-level, but not used (included) now. * db_tuple:{relabelfrom} * db_tuple:{relabelto} * db_tuple:{select} * db_tuple:{update} * db_tuple:{insert} * db_tuple:{delete} As SE-PostgreSQL doing on any other database object, it (can) assigns a default security context on the tuple newly inserted, if user does not given any security context explicitly. Then, it checks db_tuple:{insert} permission on them. Do you need explanation for any other permissions on db_tuple
Re: [HACKERS] benchmarking the query planner
On Fri, Dec 12, 2008 at 3:59 PM, Tom Lane t...@sss.pgh.pa.us wrote: Simon Riggs si...@2ndquadrant.com writes: On Fri, 2008-12-12 at 13:20 -0500, Tom Lane wrote: Simon Riggs si...@2ndquadrant.com writes: Solutions can also include * manual mechanism for setting ndistinct that doesn't keep getting overwritten by subsequent ANALYZEs Hmm, that might actually be the most practical answer for large, reasonably-static tables. Especially if we expose the negative stadistinct feature to let people specify it as a fraction of table size. Works for me. Especially if you want to think more about ANALYZE before changing that. Well, it's something that would be sane to contemplate adding in 8.4. It's way too late for any of this other stuff to happen in this release. I'm thinking about trying to implement this, unless someone else is already planning to do it. I'm not sure it's practical to think about getting this into 8.4 at this point, but it's worth doing whether it does or not. The main problem I see here is that I don't know what the mechanism should be. My first thought was to use a reloption, but that won't work because reloptions are per-table and this setting is per-column. So my second thought is to add a column to pg_attribute and add a new DDL commands to modify it, maybe something like: ALTER TABLE name ALTER [COLUMN] column SET NDISTINCT 1672; ALTER TABLE name ALTER [COLUMN] column DROP NDISTINCT; Another option would be to invent a reloption-like syntax for columns: ALTER TABLE name ALTER [COLUMN] column SET (ndistinct = 1672); ALTER TABLE name ALTER [COLUMN] column RESET (ndistinct); This sort of seems cleaner and I can imagine it being useful for other purposes, but I'm not sure if I want to go to the trouble of building a completely general column-level reloptions mechanism ATM. It's also not entirely clear to me whether this option, wherever we put it, should be directly examined by the selectivity functions before consulting the statistics tuple, or whether it should merely override the value that ANALYZE puts into the statistics tuple. The latter seems simpler and more performant to me, but does lead to the possibly surprising result that changes don't take effect until the next ANALYZE. Thoughts? ...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] Should SET ROLE inherit config params?
Greg Stark st...@enterprisedb.com writes: On Wed, Mar 11, 2009 at 9:45 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-03-11 at 14:27 -0700, Josh Berkus wrote: This is as documented (although I want to add a line to SET ROLE docs) but is it the behavior we want? I for one would like SET ROLE to change runtime configs. Well for one thing pg_dump uses SET ROLE extensively and it sets parameters assuming they'll stay set I think this is going to make the already-tricky semantics of GUC variables completely impossible. Per-user settings normally establish the session's RESET values of the variables and can be overridden (for the session or just for a transaction) by explicit SET. If the latter remains true it'd fix Greg's concern about pg_dump, but it's just mind-bending to think about what RESET means if we try to put this in. Assume we've done ALTER ROLE SET foo = something for our login role and ALTER ROLE x SET foo = somethingelse: start psql -- foo = something, presumably SET foo = other; SET ROLE x; -- foo still = other, presumably RESET foo; -- now what is foo? (if your answer is somethingelse, justify this in terms of the documented behavior of RESET: restore to the session-start value.) RESET ROLE; -- now what is foo? (ie, does this action in itself change foo, and if so why?) Also, with all the whining I've seen in the past few days about not making application-breaking incompatible changes, it would seem appropriate to have a GUC to control whether we have this behavior or the old one. Discuss the implications of changing such a GUC partway through this sequence. For extra credit, explain what would happen if it were set via ALTER ROLE SET for one role or the other. In short: -1 from 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
* ACL_INSERT The db_table:{insert} and db_column:{insert} for all the target columns are checked. The table-level permission does not override column-level permission, so the client need to have privileges for both of objects. It is same as other permissions. * ACL_SELECT The db_table:{select} and db_column:{select} for all the target columns are checked. But it applies db_table:{lock} on LockTableCommand(). * ACL_UPDATE The db_table:{update} and db_column:{update} for all the target columns are checked. * ACL_DELETE The db_table:{delete} is also checked. No column-level checks here. I'm more or less with you up to this point. * ACL_TRUNCATE The db_table:{delete} is also checked. SE-PostgreSQL does not discriminate between TRUNCATE and DELETE. But this seems wrong. * ACL_REFERENCES * ACL_TRIGGER SE-PostgreSQL does not care about these privileges. But, it checks db_procedure:{execute} on trigger invocation time, and it also checks db_table:{select} on checks of FK constraint within its secondary SQL execution. And so do these. Why should there be any asymmetry with regular PostgreSQL here? * ACL_EXECUTE The db_procedure:{execute} is also checked. This check is embedded within pg_proc_ackcheck(). Good... * ACL_USAGE * ACL_CREATE * ACL_CREATE_TEMP SE-PostgreSQL does not care about there privileges. Again, there doesn't seem to be any reason for this asymmetry. I think you should change it. ...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] parallel restore item dependencies
OK, I've worked out why I am seeing deadlocks etc. from parallel restore on FK items. In my original patch, I looked at all the dependencies of a candidate item ansd compared them with the dependencies of the running items to see if there was a potential locking clash. However, Tom in his admirable reworking of my patch, restricted the list of potential clashing items (lockDeps) to TABLE items, if any. This would probably have been ok if we hadn't just beforehand transferred all TABLE dependencies in POST_DATA items to the corresponding TABLE DATA item. The result is that we get empty lockDeps lists on all items - I'm surprised we haven't had more complaints about deadlock or failing locks. A simple fix that would probably work would be to adjust the filter to include TABLE DATA items, so the relevant statement would read: if (tocsByDumpId[depid - 1] (strcmp(tocsByDumpId[depid - 1]-desc, TABLE) == 0 || strcmp(tocsByDumpId[depid - 1]-desc, TABLE DATA) == 0)) lockids[nlockids++] = depid; Perhaps a better fix would move the code that sets up the lockDeps so that it runs before we adjust the dependencies. I'm moderately confident that either of these fixes will work, but I think this demonstrates the need for lots of testing, especially with complex data sets that have lots of dependencies and potentially deadlocking items. thoughts? 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] Should SET ROLE inherit config params?
On Wed, Mar 11, 2009 at 9:21 PM, Tom Lane t...@sss.pgh.pa.us wrote: Greg Stark st...@enterprisedb.com writes: On Wed, Mar 11, 2009 at 9:45 PM, Simon Riggs si...@2ndquadrant.com wrote: On Wed, 2009-03-11 at 14:27 -0700, Josh Berkus wrote: This is as documented (although I want to add a line to SET ROLE docs) but is it the behavior we want? I for one would like SET ROLE to change runtime configs. Well for one thing pg_dump uses SET ROLE extensively and it sets parameters assuming they'll stay set I think this is going to make the already-tricky semantics of GUC variables completely impossible. Per-user settings normally establish the session's RESET values of the variables and can be overridden (for the session or just for a transaction) by explicit SET. If the latter remains true it'd fix Greg's concern about pg_dump, but it's just mind-bending to think about what RESET means if we try to put this in. Assume we've done ALTER ROLE SET foo = something for our login role and ALTER ROLE x SET foo = somethingelse: start psql -- foo = something, presumably SET foo = other; SET ROLE x; -- foo still = other, presumably RESET foo; -- now what is foo? (if your answer is somethingelse, justify this in terms of the documented behavior of RESET: restore to the session-start value.) RESET ROLE; -- now what is foo? (ie, does this action in itself change foo, and if so why?) Also, with all the whining I've seen in the past few days about not making application-breaking incompatible changes, it would seem appropriate to have a GUC to control whether we have this behavior or the old one. Discuss the implications of changing such a GUC partway through this sequence. For extra credit, explain what would happen if it were set via ALTER ROLE SET for one role or the other. In short: -1 from me. Maybe it would make more sense to have some option to SET ROLE or some separate command that resets all configuration parameters to the values that they would have had, if you had only logged in as that other user originally. I thought RESET ALL might do this, but it seems not. ...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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
Robert Haas wrote: * ACL_INSERT The db_table:{insert} and db_column:{insert} for all the target columns are checked. The table-level permission does not override column-level permission, so the client need to have privileges for both of objects. It is same as other permissions. * ACL_SELECT The db_table:{select} and db_column:{select} for all the target columns are checked. But it applies db_table:{lock} on LockTableCommand(). * ACL_UPDATE The db_table:{update} and db_column:{update} for all the target columns are checked. * ACL_DELETE The db_table:{delete} is also checked. No column-level checks here. I'm more or less with you up to this point. * ACL_TRUNCATE The db_table:{delete} is also checked. SE-PostgreSQL does not discriminate between TRUNCATE and DELETE. But this seems wrong. We consider these differences are just only the way to remove all the tuples, not the target of the tables and its result. * ACL_REFERENCES * ACL_TRIGGER SE-PostgreSQL does not care about these privileges. But, it checks db_procedure:{execute} on trigger invocation time, and it also checks db_table:{select} on checks of FK constraint within its secondary SQL execution. And so do these. Why should there be any asymmetry with regular PostgreSQL here? The ACL_REFERENCES is checked when we set up FK constraint, then ri_PerformCheck() invokes another query to check constraint with privileges of the table owner. It assumes the owner can access on the table owned. However, SE-PostgreSQL works orthogonally to the ownership. We don't assume the client can access on the FK constrainted table, and it apply appropriate checks on the secondary query also, so it does not need to check on FK creation time. The ACL_TRIGGER is also checked when CREATE TRIGGER. If someone set a trigger function which is not allowed to execute from other persons, the other person can invoke this function via trigger mechanism. I wonder why the vanilla PostgreSQL does not put pg_proc_aclcheck() on the ExecCallTriggerFunc(). * ACL_EXECUTE The db_procedure:{execute} is also checked. This check is embedded within pg_proc_ackcheck(). Good... * ACL_USAGE * ACL_CREATE * ACL_CREATE_TEMP SE-PostgreSQL does not care about there privileges. Again, there doesn't seem to be any reason for this asymmetry. I think you should change it. The ACL_CREATE and ACL_CREATE_TEMP are checked on namespace in which the object newly created belongs. And the ACL_USAGE is checked on various kind of database objects, but they don't have its security context. Funfamentally, SELinux requires to check user's privileges on the object newly created. The object is labeled as a default security context (if user does not specify anything) on its creation. So, if we implement it now, a facility is necessary to store a security context of namespace and others. 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] parallel restore item dependencies
Andrew Dunstan and...@dunslane.net writes: OK, I've worked out why I am seeing deadlocks etc. from parallel restore on FK items. In my original patch, I looked at all the dependencies of a candidate item ansd compared them with the dependencies of the running items to see if there was a potential locking clash. However, Tom in his admirable reworking of my patch, restricted the list of potential clashing items (lockDeps) to TABLE items, if any. This would probably have been ok if we hadn't just beforehand transferred all TABLE dependencies in POST_DATA items to the corresponding TABLE DATA item. The result is that we get empty lockDeps lists on all items - I'm surprised we haven't had more complaints about deadlock or failing locks. [ scratches head... ] I coulda sworn I tested that when I was hacking it. I'm running low on steam tonight but will think more about this tomorrow. 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] Updates of SE-PostgreSQL 8.4devel patches (r1710)
KaiGai Kohei wrote: I wonder why the vanilla PostgreSQL does not put pg_proc_aclcheck() on the ExecCallTriggerFunc(). I don't think we can assume any trigger functions are trusted, because normal users with ACL_TRIGGER privilege can set their procedures on the allowed tables. It also means someone without ACL_EXECUTE to invoke the functions, but I cannot believe ACL_TRIGGER implicitly contains such a meaning. Indeed, I put a hook to check db_procedure:{execute} permission in SELinux, but putting pg_proc_aclcheck() here is meaningful not only SE-PostgreSQL users. I found another matter related to triggers. I'll report it on another messages. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei kai...@ak.jp.nec.com *** src/backend/commands/trigger.c (revision 1704) --- src/backend/commands/trigger.c (working copy) *** *** 1560,1566 --- 1560,1576 * call. */ if (finfo-fn_oid == InvalidOid) + { + AclResult aclresult; + + aclresult = pg_proc_aclcheck(trigdata-tg_trigger-tgfoid, + GetUserId(), ACL_EXECUTE); + if (aclresult != ACLCHECK_OK) + aclcheck_error(aclresult, ACL_KIND_PROC, + get_func_name(trigdata-tg_trigger-tgfoid)); + fmgr_info(trigdata-tg_trigger-tgfoid, finfo); + } Assert(finfo-fn_oid == trigdata-tg_trigger-tgfoid); -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Row-Trigger implicitly allows users ACL_SELECT
* Row-Update/Delete trigger mechanism allows user defined triggers to refer the older tuple updated/deleted. * The ACL_TRIGGER privilege allows normal users to set up triggers on the relation allowed. It means someone with ACL_TRIGGER can set up a trigger which write out the given older tuple into somewhere. In logically, it also means users with ACL_TRIGGER and either of ACL_UPDATE or ACL_DELETE are allowed to read the table without ACL_SELECT permission. It has been my concern. Basically, I don't think it is a good design a permission implicitly contains different meanings. See the following steps. A normal user 'ymj' allows 'tak' ACL_UPDATE and ACL_TRIGGER on the table 't1', but ACL_SELECT is not allowed 'tak' tries to define his function which write out the OLD tuple into his table, and he also set up this function as a trigger of 't1'. Then, his invoke unconditional UPDATE on 't1' and can read whole of the table. (1) Create normal users: 'ymj' and 'tak' postgres=# CREATE USER ymj; CREATE ROLE postgres=# CREATE USER tak; CREATE ROLE postgres=# \q (2) 'ymj' create a table and grant UPDATE and TRIGGER to 'tak' [kai...@saba ~]$ psql postgres -U ymj psql (8.4devel) Type help for help. postgres= CREATE TABLE t1 (a int, b text); CREATE TABLE postgres= INSERT INTO t1 VALUES (1,'aaa'), (2,'bbb'), (3,'ccc'); INSERT 0 3 postgres= GRANT UPDATE ON t1 TO tak; GRANT postgres= GRANT TRIGGER ON t1 TO tak; GRANT postgres= \q (3) 'tak' create a function and set a trigger postgres= CREATE TABLE t2 (x text); CREATE TABLE postgres= CREATE OR REPLACE FUNCTION f1() RETURNS trigger postgres- language 'plpgsql' as' postgres' BEGIN postgres' INSERT INTO t2 VALUES(OLD::text); postgres' RETURN NEW; postgres' END'; CREATE FUNCTION postgres= CREATE TRIGGER tg1 BEFORE UPDATE ON t1 postgres- FOR ROW EXECUTE PROCEDURE f1(); CREATE TRIGGER (4) 'tak' update the table t1. He can also read 't1' without ACL_SELECT postgres= BEGIN; BEGIN postgres= UPDATE t1 SET a = 1; UPDATE 3 postgres= SELECT * FROM t2; x - (1,aaa) (2,bbb) (3,ccc) (3 rows) postgres= ABORT; ROLLBACK In a practical sense, we seldom assign users writer-permission without reader-permission, because they cannot specify what tuple should be updated/delete. But it does not mean we can overlook the access control facility does not work well. I think we should assign ACL_SELECT on rte-requiredPerms and set a bit for attno=0 on rte-selectedCols, when the target relation has user defined Row-Update/Delete triggers and CmdType matches them. (But the triggers to check FK constraints can be an exception because these are built-in, so obviously it is not malicious.) 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
[HACKERS] View running statements
Is there any way to inspect current running statements (optionally full stack of these statements)? I've found there is error_context_stack variable in each backend, but it seems there is no way to get this variable from another backend. It will be great if Postgres have such kind of mechanism, for instance a system table that consists statements running longer than certain time (5-10 seconds). -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers