Re: [HACKERS] Application name patch - v2
2009/10/20 Bruce Momjian : > Robert Haas wrote: >> > I do agree with Peter's concerns about limiting the character set of the >> > name string, and maybe there should be some sort of length limit too. >> >> I don't have a strong feeling about this. If limiting this to 7-bit >> characters solves some nasty encoding problems or something, then >> fine, but otherwise I think we can just escape what we emit into the >> log and say that users who log this information should have a >> sufficiently sophisticated log parser to cope with it. > > Once problem I can imagine is someone with a long log_line prefix, like > '%t %a|', and assuming that the pipe is the end of the log_prefix > arguments. If someone adds a pipe to the application name, log parsing > code will assume the %a pipe ends the log_line_prefix, and we have no > system of escaping things like pipes in log_line_prefix. > > Effectively, if you use %a, there is no good way to terminate > log_line_prefix with a known unique character. If you're going to parse your logfile, you should probably be using CSV format logs, which I believe would not have this issue... -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
Bruce Momjian wrote: > > 1. Invent a GUC that has the settings backwards-compatible, > > oracle-compatible, throw-error (exact spellings TBD). Factory default, > > at least for a few releases, will be throw-error. Make it SUSET so that > > unprivileged users can't break things by twiddling it; but it's still > > possible for the DBA to set it per-database or per-user. > > > > 2. Also invent a #option syntax that allows the GUC to be overridden > > per-function. (Since the main GUC is SUSET, we can't just use a > > per-function SET to override it. There are other ways we could do this > > but none seem less ugly than #option...) > > I don't see the logic to making the setting SUSET. The user wrote the > function; what logic is there to say the resolution rules are not under > their control? > > Also, I think to GUC that throws an error or not is a lot safer than one > that changes resolution semantics. Changing resolution semantics sounds > like the autocommit GUC to me. :-O > > Also, I am not really keen on the "keep it for a few releases" --- we > often don't come back to finally change it, so maybe just error/no error > and using Oracle semantics is the way to go, with 'error' as the > default. Our change in casting for 8.3 seemed much more major than > this. Oh, two more things. First, with the Oracle resolution rules, I think it is possible to change the behavior of a function by adding or renaming a column that wasn't referenced in the function because a new/renamed column might mask a function variable --- that is not nice. Second, I can see the value of having the GUC be SUSET if changing the setting could possible break the function or cause insecure behavior, but I wasn't clear that was possible. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] UTF8 with BOM support in psql
Bruce Momjian wrote: > Itagaki Takahiro wrote: > > When psql opens a file with -f or \i, it checks first 3 bytes of the > > file. If they are BOM, discard the 3 bytes and change client encoding > > to UTF8 automatically. > > Seems there is community support for accepting BOM: > http://archives.postgresql.org/pgsql-hackers/2009-09/msg01625.php Thank yor for information. I read the thread that we discussed about BOM handling in *data types*. I agree the decision in the thead that we should not skip BOM characters, but we can handle BOM in a different way in the head of *files* for psql and COPY input. 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] UTF8 with BOM support in psql
Itagaki Takahiro wrote: > UTF8 encoding text files with BOM (Byte Order Mark) are commonly > used in Windows, though BOM was designed for UTF16 text originally. > However, psql cannot read such format even if we set client encoding > to UTF8. Is it worth supporting those format in psql? > > When psql opens a file with -f or \i, it checks first 3 bytes of the > file. If they are BOM, discard the 3 bytes and change client encoding > to UTF8 automatically. > > Is this change reasonable? Comments welcome. Seems there is community support for accepting BOM: http://archives.postgresql.org/pgsql-hackers/2009-09/msg01625.php Should I add this as a TODO item? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Could postgres be much cleaner if a future release skipped backward compatibility?
On Mon, 2009-10-19 at 14:08 -0700, Ron Mayer wrote: > Tom Lane wrote: > > What are the probabilities that the OpenACSes of the world will just > > set the value to "backward compatible" instead of touching their code? > > Would postgres get considerably cleaner if a hypothetical 9.0 release > skipped backward compatibility and removed anything that's only > maintained for historical reasons? Probably not. Most of the examples you cite of documented deprecated or historical behavior would be one-line changes to get rid of. -- 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] Controlling changes in plpgsql variable resolution
Tom Lane wrote: > Andrew Dunstan writes: > > Tom Lane wrote: > >> (a) Nobody but me is afraid of the consequences of treating this as > >> a GUC. (I still think you're all wrong, but so be it.) > > > I can't say I'm happy about it. For one thing, the granularity seems all > > wrong. I'd rather be able to keep backwards compatibility on a function > > by function basis. Or would the value of the GUC at the time the > > function was created stick? > > Again, I can't see making a GUC that works fundamentally differently > from the rest of them. > > Given this round of feedback, I make the following proposal: > > 1. Invent a GUC that has the settings backwards-compatible, > oracle-compatible, throw-error (exact spellings TBD). Factory default, > at least for a few releases, will be throw-error. Make it SUSET so that > unprivileged users can't break things by twiddling it; but it's still > possible for the DBA to set it per-database or per-user. > > 2. Also invent a #option syntax that allows the GUC to be overridden > per-function. (Since the main GUC is SUSET, we can't just use a > per-function SET to override it. There are other ways we could do this > but none seem less ugly than #option...) I don't see the logic to making the setting SUSET. The user wrote the function; what logic is there to say the resolution rules are not under their control? Also, I think to GUC that throws an error or not is a lot safer than one that changes resolution semantics. Changing resolution semantics sounds like the autocommit GUC to me. :-O Also, I am not really keen on the "keep it for a few releases" --- we often don't come back to finally change it, so maybe just error/no error and using Oracle semantics is the way to go, with 'error' as the default. Our change in casting for 8.3 seemed much more major than this. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] UTF8 with BOM support in psql
UTF8 encoding text files with BOM (Byte Order Mark) are commonly used in Windows, though BOM was designed for UTF16 text originally. However, psql cannot read such format even if we set client encoding to UTF8. Is it worth supporting those format in psql? When psql opens a file with -f or \i, it checks first 3 bytes of the file. If they are BOM, discard the 3 bytes and change client encoding to UTF8 automatically. Is this change reasonable? Comments welcome. 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] Application name patch - v2
Robert Haas wrote: > > I do agree with Peter's concerns about limiting the character set of the > > name string, and maybe there should be some sort of length limit too. > > I don't have a strong feeling about this. If limiting this to 7-bit > characters solves some nasty encoding problems or something, then > fine, but otherwise I think we can just escape what we emit into the > log and say that users who log this information should have a > sufficiently sophisticated log parser to cope with it. Once problem I can imagine is someone with a long log_line prefix, like '%t %a|', and assuming that the pipe is the end of the log_prefix arguments. If someone adds a pipe to the application name, log parsing code will assume the %a pipe ends the log_line_prefix, and we have no system of escaping things like pipes in log_line_prefix. Effectively, if you use %a, there is no good way to terminate log_line_prefix with a known unique character. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] per table random-page-cost?
On Mon, 19 Oct 2009, Jeff Davis wrote: On Mon, 2009-10-19 at 21:22 -0500, Kevin Grittner wrote: I'd bet accounts receivable applications often hit that. (Most payments on recent billings; a sprinkling on older ones.) I'm sure there are others. You worded the examples in terms of writes (I think), and we're talking about read caching, so I still don't entirely understand. No, that part was fair. The unfortunate reality of accounts receivable is that reports run to list people who owe one money happen much more often than posting payments into the system does. Also, the example sounds like you'd like to optimize across queries. There's no mechanism for the planner to remember some query executed a while ago, and match it up to some new query that it's trying to plan. Some of the use-cases here involve situations where you know most of a relation is likely to be in cache just because there's not much going on that might evict it. In any case, something that attempts to model some average percentage you can expect a relation to be in cache is in effect serving as a memory of past queries. I'm not clear on the scenario that we're trying to improve. Duh, that would be the situation where someone wants optimizer hints but can't call them that because then the idea would be reflexively rejected! Looks like I should dust off the much more complicated proposal for tracking and using in-cache hit percentages I keep not having time to finish writing up. Allowing a user-set value for that is a lot more reasonable if the system computes a reasonable one itself under normal circumstances. That's what I think people really want, even if it's not what they're asking for. -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rejecting weak passwords
Tom Lane wrote: > "Albe Laurenz" writes: > > Bruce Momjian wrote: > >> Password checks might include password complexity or non-reuse of > >> passwords. This facility will require the client to send the password to > >> the server in plain-text, so SSL and 'password' authentication is > >> necessary to use this features. > > > So in my opinion that should be: > > This facility will require to send new and changed password to > > the server in plain-text, so it will require SSL, and the use > > of encrypted passwords in CREATE/ALTER ROLE will have to be > > disabled. > > Actually, not one word of *either* version should be in TODO. All of > that is speculation about policies that a particular add-on module > might or might not choose to enforce. Agreed, updated: |Allow server-side enforcement of password policies |Password checks might include password complexity or non-reuse of passwords. This facility will require the client to send password creation/changes to the server in plain-text, not MD5. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] per table random-page-cost?
On Mon, 2009-10-19 at 21:22 -0500, Kevin Grittner wrote: > I'd bet accounts receivable applications often hit that. > (Most payments on recent billings; a sprinkling on older ones.) > I'm sure there are others. You worded the examples in terms of writes (I think), and we're talking about read caching, so I still don't entirely understand. Also, the example sounds like you'd like to optimize across queries. There's no mechanism for the planner to remember some query executed a while ago, and match it up to some new query that it's trying to plan. Maybe there should be, but that's an entirely different feature. I'm not clear on the scenario that we're trying to improve. 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] per table random-page-cost?
Jeff Davis wrote: > what kind of scenario > would involve a stable 90% cache hit ratio for a table? I'd bet accounts receivable applications often hit that. (Most payments on recent billings; a sprinkling on older ones.) I'm sure there are others. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per table random-page-cost?
On Mon, Oct 19, 2009 at 4:29 PM, Greg Stark wrote: > On Mon, Oct 19, 2009 at 4:21 PM, Tom Lane wrote: >> marcin mank writes: This proposal is just "hints by the back door", ISTM. As Tom says, there is a justification for having it on tablespaces but not on individual tables. >> >>> If the parameter is defined as "the chance that a page is in cache" >>> there is very real physical meaning to it. >> >> We have no such parameter... > > > And we want our parameters to be things the DBA has a chance of being > able to estimate. Do the current parameters meet that standard? When setting seq_page_cost now, don't people have a lot of "Well, we're about this likely to find it in the cache anyway" built into their settings? Jeff -- 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] Could postgres be much cleaner if a future release skipped backward compatibility?
"Marc G. Fournier" writes: > Just curious, but with that thought in mind, are we doing any code > cleanups as far as EOL releases? Ie. is there any code in our tree right > now that is for 'backward compatibility' for 7.3.x versions that could be > cleaned out? Well, we were just trying to pull out add_missing_from, and look how far that's gotten ... Something that has been suggested is to rip out pg_dump's support for pre-7.3 servers, which would probably be easier to get through because it wouldn't create any user-visible feature loss. It wouldn't be a monstrous savings but it'd certainly simplify matters somewhat. 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] Could postgres be much cleaner if a future release skipped backward compatibility?
On Mon, 19 Oct 2009, Tom Lane wrote: Ron Mayer writes: Would postgres get considerably cleaner if a hypothetical 9.0 release skipped backward compatibility and removed anything that's only maintained for historical reasons? Yeah, and our user community would get a lot smaller too :-( Actually, I think any attempt to do that would result in a fork, and a consequent splintering of the community. We can get away with occasionally cleaning up individual problematic behaviors (example: implicit casts to text), but any sort of all-at-once breakage would result in a lot of people Just Saying No. Just curious, but with that thought in mind, are we doing any code cleanups as far as EOL releases? Ie. is there any code in our tree right now that is for 'backward compatibility' for 7.3.x versions that could be cleaned out? I realize that this might not make a huge difference, but it would be easier to do a 'gradual clean up', then an 'all-at-once' scenario, no? Marc G. FournierHub.Org Hosting Solutions S.A. scra...@hub.org http://www.hub.org Yahoo:yscrappySkype: hub.orgICQ:7615664MSN:scra...@hub.org -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Add a pgstat config column to pg_database, so this, entire thing can be enabled/disabled on a per db basis
Hi, Looking at the code, it seems the $SUBJECT (comment) is obsolete. Indeed, we can set it with 'ALTER DATABASE foo SET track_*'. One thing that bothers me is the fact that if i turn it off, do a lot of stuff and then turn it on my counters will be wrong. :( Maybe should we call pgstat_reset_counters() in a new-function (assign_track_counts(?) that will be in assign_hook for guc 'track_counts')? -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] SE-PgSQL developer documentation (Re: Reworks for Access Control facilities (r2363))
After the long trial-and-errors, we learned a few approaches which use common entry points for both of DAC and MAC were rocky-path more than what we initially imagined. So, we came back to the original design. It deploys MAC hooks on the strategic points of core routines. On the other hand, people complained about this approach without clear documentation, because most of people are not familiar to both of SELinux and PgSQL. Heikki suggested that a clear developer documentation should be provided to understand pgsql-hackers this new concept. (And, Peter has also suggested before a developer documentation will be a good source of user documentations.) I plan to submit SE-PgSQL/lite patch with developer documentations on the next commit-fest. I can understand what I want to develop and the purpose of codes. However, it may not match with what you want to know. So, I'd like to ask what should be included within the developer documentation at first prior to making a documentation. I plans the developer documentation should be put as a REAME file, not a SGML documentation or a certain wiki page. And I think it should contain the following items. * overview - general overview of SE-PgSQL - introduction of SELinux specific terms (such as "security context") * internal architecture - the purpose of sub-components (such as management of security context, caches of access control decision and so on) - differences from similar permissions in DAC * object classes and permissions defined in SELinux model - list of them and when/where they should be checked. * specification of SE-PgSQL hooks (It should be put on the source code comments for easy maintenance.) - what this hook does, what arguments are required, what result will be returned. * code examples - a few examples to add MAC checks within 3rd party modules. Do you have any comments? What should be added to? or removed from? Thanks, KaiGai Kohei wrote: > Heikki Linnakangas wrote: >> KaiGai Kohei wrote: >>> When we create a new object, we can provide an explicit security context >>> to be assigned on the new object, instead of the default one. >> To get started, do we really need that feature? It would make for a >> significantly smaller patch if there was no explicit security labels on >> objects. > > The importance of the feature is relatively minor than MAC itself. > So, I can agree to omit code corresponding to statement support > from the first patch. (IIRC, about 300-400 lines can be reduced.) > But it will be necessary feature at the next step, because DBA cannot > create a special purpose table without statement support. > > For example, if security policy allows DBA to create read-writable > table (in default) and read-only table. He cannot set up read-only > table without explicit security label support. > > On the other hand, the default PG model allows to bypass checks on > certain objects. For example, column-level privileges are only checked > when a user does not have enough permissions on the target table. > If "SELECT a,b FROM t" is given, pg_attribute_aclcheck() may not invoked > when user has needed privileges on the table t. Hmm, I see. Yes, it does seem like we'd need to change such permission checks to accommodate both models. >>> I'm not clear why we need to rework the permission checks here. >>> DAC and MAC perform orthogonally and independently. >>> DAC allows to override column-level privileges by table-level privileges >>> according to the default PG's model. It seems to me fine. >>> On the other hand, MAC checks both of permissions. It is also fine. >> I meant we need to refactor the code doing the permission checks. The >> existing checks are doing the right thing for DAC, but as you point out, >> if the MAC checks are within pg_*_aclcheck() functions, >> pg_attribute_aclcheck() needs to be called even if you have privilege on >> the table. > > I think we already learned refactoring DAC checks need widespread code > changes and pushes a burden to reviewers. > > In this case, I think the point just after invocation of ExecCheckRTEPerms() > in ExecCheckRTPerms() is the best point to put SE-PgSQL's checks. > Needless to say, its specification should be clearly documented. > > Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei -- 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] Could postgres be much cleaner if a future release skipped backward compatibility?
* Tom Lane [091019 18:45]: > Ron Mayer writes: > > Would postgres get considerably cleaner if a hypothetical 9.0 release > > skipped backward compatibility and removed anything that's only > > maintained for historical reasons? > > Yeah, and our user community would get a lot smaller too :-( > > Actually, I think any attempt to do that would result in a fork, > and a consequent splintering of the community. We can get away > with occasionally cleaning up individual problematic behaviors > (example: implicit casts to text), but any sort of all-at-once > breakage would result in a lot of people Just Saying No. I don't know... What if this hypothetical "baggage-free" version came with configurable syncrhonous master-slave replication, writable CTEs, and everything ;-) Couple it with a libpq/protocol increase that allows fixing of the various warts of the current connection (like encoding, etc), and you still have a *very* attractive platform... And then just do the rename official to Postgres, and people can support both PostgreSQL, warts and all, or Postgres, the super-duper database-to-rule-them-all... ;-) /me crawls back into his hole a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. signature.asc Description: Digital signature
Re: [HACKERS] per table random-page-cost?
On Mon, 2009-10-19 at 16:39 -0700, Greg Stark wrote: > But the long-term strategy here I think is to actually have some way > to measure the real cache hit rate on a per-table basis. Whether it's > by timing i/o operations, programmatic access to dtrace, or some other > kind of os interface, if we could know the real cache hit rate it > would be very helpful. Maybe it would be simpler to just get the high-order bit: is this table likely to be completely in cache (shared buffers or os buffer cache), or not? The lower cache hit ratios are uninteresting: the performance difference between 1% and 50% is only a factor of two. The higher cache hit ratios that are lower than "almost 100%" seem unlikely: what kind of scenario would involve a stable 90% cache hit ratio for a table? 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] per table random-page-cost?
On Mon, Oct 19, 2009 at 5:54 PM, Kevin Grittner wrote: > Robert Haas wrote: > >> I've been wondering if it might make sense to have a >> "random_page_cost" and "seq_page_cost" setting for each TABLESPACE, >> to compensate for the fact that different media might be faster or >> slower, and a percent-cached setting for each table over top of >> that. > > [after recovering from the initial cringing reaction...] > > How about calculating an effective percentage based on other > information. effective_cache_size, along with relation and database > size, come to mind. How about the particular index being considered > for the plan? Of course, you might have to be careful about working > in TOAST table size for a particular query, based on the columns > retrieved. I think that a per-tablespace page cost should be set by the DBA, same as we do with global page-costs now. OTOH, I think that a per-relation percent-in-cache should be automatically calculated by the database (somehow) and the DBA should have an option to override in case the database does the wrong thing. I gave a lightning talk on this topic at PGcon. > I have no doubt that there would be some major performance regressions > in the first cut of anything like this, for at least *some* queries. > The toughest part of this might be to get adequate testing to tune it > for a wide enough variety of real-life situations. Agreed. ...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] per table random-page-cost?
On Mon, Oct 19, 2009 at 2:54 PM, Kevin Grittner wrote: > How about calculating an effective percentage based on other > information. effective_cache_size, along with relation and database > size, come to mind. I think previous proposals for this have fallen down when you actually try to work out a formula for this. The problem is that you could have a table which is much smaller than effective_cache_size but is never in cache due to it being one of many such tables. I think it would still be good to have some naive kind of heuristic here as long as it's fairly predictable for DBAs. But the long-term strategy here I think is to actually have some way to measure the real cache hit rate on a per-table basis. Whether it's by timing i/o operations, programmatic access to dtrace, or some other kind of os interface, if we could know the real cache hit rate it would be very helpful. Perhaps we could extrapolate from the shared buffer cache percentage. If there's a moderately high percentage in shared buffers then it seems like a reasonable supposition to assume the filesystem cache would have a similar distribution. -- 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] per table random-page-cost?
On Mon, Oct 19, 2009 at 4:21 PM, Tom Lane wrote: > marcin mank writes: >>> This proposal is just "hints by the back door", ISTM. As Tom says, there is >>> a justification for having it on tablespaces but not on individual tables. > >> If the parameter is defined as "the chance that a page is in cache" >> there is very real physical meaning to it. > > We have no such parameter... And we want our parameters to be things the DBA has a chance of being able to estimate. How would you come up with sensible figures for this hypothetical parameter? -- 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] per table random-page-cost?
marcin mank writes: >> This proposal is just "hints by the back door", ISTM. As Tom says, there is >> a justification for having it on tablespaces but not on individual tables. > If the parameter is defined as "the chance that a page is in cache" > there is very real physical meaning to it. We have no such parameter... 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] per table random-page-cost?
> This proposal is just "hints by the back door", ISTM. As Tom says, there is > a justification for having it on tablespaces but not on individual tables. If the parameter is defined as "the chance that a page is in cache" there is very real physical meaning to it. And this is per-table, not per-tablespace. A "users" table will likely be fetched from cache all the time, while a "billing_records" table will be fetched mostly from disk. Greetings Marcin -- 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] per table random-page-cost?
marcin mank wrote: I've been thinking about this a bit, too. I've been wondering if it might make sense to have a "random_page_cost" and "seq_page_cost" setting for each TABLESPACE, to compensate for the fact that different media might be faster or slower, and a percent-cached setting for each table over top of that. I thought about making it per-table, but realistically I think most people don`t use tablespaces now. I would not want to be telling people "to be able to hint the planner to (not) index-scan the table, You must move it to a separate tablespace". This is just plain wrong, in my experience. *Every* large installation I deal with uses tablespaces. This proposal is just "hints by the back door", ISTM. As Tom says, there is a justification for having it on tablespaces but not on individual tables. If you want to argue for full blown planner hints, that's a whole other story. Have you read the previous debates on the subject? cheers -- 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] Could postgres be much cleaner if a future release skipped backward compatibility?
Ron Mayer writes: > Would postgres get considerably cleaner if a hypothetical 9.0 release > skipped backward compatibility and removed anything that's only > maintained for historical reasons? Yeah, and our user community would get a lot smaller too :-( Actually, I think any attempt to do that would result in a fork, and a consequent splintering of the community. We can get away with occasionally cleaning up individual problematic behaviors (example: implicit casts to text), but any sort of all-at-once breakage would result in a lot of people Just Saying 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] LATERAL
> "Greg" == Greg Stark writes: >> Why not? As Andrew pointed out, what we're really trying to >> accomplish here is consider sub-join plans that are parameterized >> by a value obtained from an outer relation. I think we shouldn't >> artificially limit what we consider. Greg> Am I understanding you right that a typical case of this might Greg> be something like Greg> nested loop Greg> index scan expecting 1 record Greg> merge join Greg> index scan on partial index where col = outer.foo and col2 Greg> between a and b Greg> some other scan no, because you could never pick the partial index at plan time. Greg> or Greg> nested loop Greg> index scan expecting 1 record Greg> merge join Greg> index scan on where col1 = outer.foo and col2 Greg> between a and b Greg> some other scan Greg> Ie, where the nested loop is a degenerate nested loop which Greg> only expects a single value and provides a parameter which Greg> allows some partial index to work or allows for some other Greg> index scan by providing a higher order key element? The nested loop does NOT have to be degenerate. Consider queries of this form: select * from small left join (big1 join big2 on (big1.id=big2.id)) on (small.id=big1.id); Right now, the only way pg can plan this is to do a hashjoin or mergejoin of the _entire content of big1 and big2_ and join the result against "small" (again in a hashjoin or mergejoin plan). This becomes excessively slow compared to the "ideal" plan: nested loop seqscan on small nested loop indexscan on big1 where id=small.id indexscan on big2 where id=small.id (or big1.id which is equiv) (The same argument applies if "small" is not actually small but has restriction clauses) -- 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] per table random-page-cost?
marcin mank writes: > I thought about making it per-table, but realistically I think most > people don`t use tablespaces now. I would not want to be telling > people "to be able to hint the planner to (not) index-scan the table, > You must move it to a separate tablespace". Per-table is not physically sensible. Per-tablespace has some rationale to 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] LATERAL
Greg Stark writes: > nested loop > index scan expecting 1 record > merge join > index scan on where col1 = outer.foo and col2 > between a and b > some other scan > Ie, where the nested loop is a degenerate nested loop which only > expects a single value and provides a parameter which allows some > partial index to work or allows for some other index scan by providing > a higher order key element? Right. I don't see any particular reason to assume the inner path is iterated only once, either. If the key value coming from the outer path is sufficiently useful, this could be a win even with multiple iterations, as compared to having to scan the whole of some large relation or other ... 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] per table random-page-cost?
> I thought about making it per-table***space***, but realistically I -- 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] per table random-page-cost?
> I've been thinking about this a bit, too. I've been wondering if it > might make sense to have a "random_page_cost" and "seq_page_cost" > setting for each TABLESPACE, to compensate for the fact that different > media might be faster or slower, and a percent-cached setting for each > table over top of that. > I thought about making it per-table, but realistically I think most people don`t use tablespaces now. I would not want to be telling people "to be able to hint the planner to (not) index-scan the table, You must move it to a separate tablespace". A global default, a per-tablespace default overriding it, and a per-table value overriding them both seems like over-engineering to me. Greetings Marcin -- 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] per table random-page-cost?
Robert Haas wrote: > I've been wondering if it might make sense to have a > "random_page_cost" and "seq_page_cost" setting for each TABLESPACE, > to compensate for the fact that different media might be faster or > slower, and a percent-cached setting for each table over top of > that. [after recovering from the initial cringing reaction...] How about calculating an effective percentage based on other information. effective_cache_size, along with relation and database size, come to mind. How about the particular index being considered for the plan? Of course, you might have to be careful about working in TOAST table size for a particular query, based on the columns retrieved. I have no doubt that there would be some major performance regressions in the first cut of anything like this, for at least *some* queries. The toughest part of this might be to get adequate testing to tune it for a wide enough variety of real-life situations. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] per table random-page-cost?
On Mon, Oct 19, 2009 at 2:08 PM, marcin mank wrote: > Currently random_page_cost is a GUC. I propose that this could be set > per-table. Or per-tablespace. Yes, I think there are a class of GUCs which describe the physical attributes of the storage system which should be per-table or per-tablespace. random_page_cost, sequential_page_cost, effective_io_concurrency come to mind. While this isn't a simple flag to change it does seem like a bit of a SMOP. The GUC infrastructure stores these values in global variables which the planner and other systems consult directly. They would instead have to be made storage parameters which the planner and other systems check on the appropriate table and default to the global GUC if they're not 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] per table random-page-cost?
On Mon, Oct 19, 2009 at 5:08 PM, marcin mank wrote: > Currently random_page_cost is a GUC. I propose that this could be set > per-table. > > I think this is a good idea for widely-wanted planner hints. This way > You can say "I do NOT want this table to be index-scanned, because I > know it is not cached" by setting it`s random_page_cost to a large > value (an obviously You can do the other way around, when setting the > random_page_cost to 1 You say "I don`t care how You fetch the pages, > they are all in cache") > > The value for the per-table setting could be inferred from > pg_stat(io)?.*tables . We could have a tool to suggest appropriate > values. > > We could call it something like cached_percentage (and have the cost > of a random tuple fetch be inferred from the global random_page_cost, > seq_tuple_cost and the per-table cached_percentage). Then we could set > the global random_page_cost to a sane value like 200. Now one can > wonder why the planner works while having such blantantly unrealistic > values for random_page_cost :) > > What do You think? I've been thinking about this a bit, too. I've been wondering if it might make sense to have a "random_page_cost" and "seq_page_cost" setting for each TABLESPACE, to compensate for the fact that different media might be faster or slower, and a percent-cached setting for each table over top of that. ...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] per table random-page-cost?
Currently random_page_cost is a GUC. I propose that this could be set per-table. I think this is a good idea for widely-wanted planner hints. This way You can say "I do NOT want this table to be index-scanned, because I know it is not cached" by setting it`s random_page_cost to a large value (an obviously You can do the other way around, when setting the random_page_cost to 1 You say "I don`t care how You fetch the pages, they are all in cache") The value for the per-table setting could be inferred from pg_stat(io)?.*tables . We could have a tool to suggest appropriate values. We could call it something like cached_percentage (and have the cost of a random tuple fetch be inferred from the global random_page_cost, seq_tuple_cost and the per-table cached_percentage). Then we could set the global random_page_cost to a sane value like 200. Now one can wonder why the planner works while having such blantantly unrealistic values for random_page_cost :) What do You think? Greetings Marcin Mank -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Could postgres be much cleaner if a future release skipped backward compatibility?
Tom Lane wrote: > What are the probabilities that the OpenACSes of the world will just > set the value to "backward compatible" instead of touching their code? Would postgres get considerably cleaner if a hypothetical 9.0 release skipped backward compatibility and removed anything that's only maintained for historical reasons? I notice the docs are filled with passages like the quotes below - which suggest that there's a fair amount of stuff that might be done differently if it weren't for backward compatibility. "For historical reasons (i.e., this is clearly wrong but it's far too late to change it), subscripting of fixed-length array types starts from zero, rather than from one as for variable-length arrays. " "Most of the alternative names listed in the "Aliases" column are the names used internally by PostgreSQL for historical reasons. In addition, some internally used or deprecated types are available, but are not listed here. " "Note: The name "oid2name" is historical, and is actually rather misleading" "Note: Native Kerberos authentication has been deprecated and should be used only for backward compatibility." "Old-style functions are now deprecated because of portability problems and lack of functionality, but they are still supported for compatibility reasons. " "Although they still work, they are deprecated due to poor error handling, inconvenient methods of detecting end-of-data, and lack of support for binary or nonblocking transfers." "The PostgreSQL usage of SELECT INTO to represent table creation is historical. It is best to use CREATE TABLE AS for this purpose in new code. " "regular expression metasyntax ... option...m: historical synonym for n" "Such comments are more a historical artifact than a useful facility, and their use is deprecated; use the expanded syntax instead." "The CAST syntax conforms to SQL; the syntax with :: is historical PostgreSQL usage." "timeofday() is a historical PostgreSQL function." "(This does not match non-slice behavior and is done for historical reasons.)" "The SQL standard requires the use of the ISO 8601 format. The name of the "SQL" output format is a historical accident." "attribute ... The historical way to specify optional pieces of information about the function. " Caution "Caution: If the configuration parameter standard_conforming_strings is off, then PostgreSQL recognizes backslash escapes in both regular and escape string constants. This is for backward compatibility with the historical" "historical alias for stddev_samp ... historical alias for var_samp" "For historical reasons, this variable contains two independent components" "For historical reasons, the same function doesn't just return a boolean result; instead it has to store the flag at the location indicated by the third argument. " "For historical reasons, there are two levels of notice handling," "Note that subscripting is 1-based, whereas for historical reasons proargtypes is subscripted from 0 " "The term attribute is equivalent to column and is used for historical reasons. " "For historical reasons, ALTER TABLE can be used with sequences too; but the only variants of ALTER TABLE that are allowed with sequences are" "While this still works, it is deprecated and the special meaning of \. can be expected to be removed in a future release." "Use of this parameter is deprecated as of PostgreSQL 8.3;" -- 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 3:46 PM, Tom Lane wrote: Sorry if this is obvious to everyone else, but *when* will the error throw? Whenever we do semantic analysis of the particular query or expression. That's what I figured. During CREATE FUNCTION or during runtime? I'm secretly hoping that it'll throw during CREATE FUNCTION. Be careful what you ask for, you might get it ;-) Yeah, and we've got at least one function that does the CREATE TEMP TABLE foo (...) pattern. So I understand. We want to our schema to keep pace with whatever the default settings are for stuff like this, so it'd be great if we could find and resolve the issues sooner rather than later. We implemented better coding practices later on in the project to help us disambiguate between variables and columns, but there's still a bunch of legacy stuff that's going to be broken. eric -- 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] LATERAL
On Sun, Oct 18, 2009 at 12:57 PM, Tom Lane wrote: > Robert Haas writes: >> You could probably convince me that a merge join is not going to be >> too useful (how often can you want a merge join on the inner side of a >> nested loop? > > Why not? As Andrew pointed out, what we're really trying to accomplish > here is consider sub-join plans that are parameterized by a value > obtained from an outer relation. I think we shouldn't artificially > limit what we consider. Am I understanding you right that a typical case of this might be something like nested loop index scan expecting 1 record merge join index scan on partial index where col = outer.foo and col2 between a and b some other scan or nested loop index scan expecting 1 record merge join index scan on where col1 = outer.foo and col2 between a and b some other scan Ie, where the nested loop is a degenerate nested loop which only expects a single value and provides a parameter which allows some partial index to work or allows for some other index scan by providing a higher order key element? -- 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] Application name patch - v2
2009/10/19 Dave Page : > On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin wrote: >> >> Would'nt this also make sense for PostgreSQL? That is, when no environment >> is set, and no SET-command is issued, that the application name becomes the >> default? > > That needs to be set by the application. As discussed previously, > there's no way for libpq to get at argv[0]. Um, that has to be platform dependent, no? On windows for example, you can use GetCommandLine(). -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 12:23 PM, Tom Lane wrote: That is, the specification of options is made outside of the language in question. I don't think I particularly care for this. It's inventing a global mechanism to cover a problem that we currently have one instance of in one PL. That's a mighty thin justification. Also, I tend to think that you should have several instances of a problem before you venture to design a global solution --- else your one-size-fits-all solution might turn out to be a lot less general than you thought. Sure, just an idea to keep in mind for when you do have a second and a third option to add… Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
"Eric B. Ridge" writes: > On Oct 19, 2009, at 2:47 PM, Tom Lane wrote: >> 1. Invent a GUC that has the settings backwards-compatible, >> oracle-compatible, throw-error (exact spellings TBD). Factory >> default, >> at least for a few releases, will be throw-error. > Sorry if this is obvious to everyone else, but *when* will the error > throw? Whenever we do semantic analysis of the particular query or expression. > During CREATE FUNCTION or during runtime? I'm secretly hoping > that it'll throw during CREATE FUNCTION. Be careful what you ask for, you might get it ;-) The problem with doing more than minimal syntax checking during CREATE FUNCTION, or even at the start of function execution, is that people are far too accustomed to being able to do things like CREATE TEMP TABLE foo ( ... ); INSERT INTO foo ... ; and of course the second command will fail outright if foo doesn't exist --- or even if we made that not fail, how will we do any meaningful semantic checking of later SELECTs against foo? Another example is a fairly common pattern in trigger functions: if tg_op = 'insert' then ... do something with new.* ... else if tg_op = 'delete' then ... do something with old.* ... ... etc ... where semantic checking on the non-executed parts of the function would certainly throw error. I would love to offer an option that "fully" checks plpgsql functions but I think it would break so much code that no one could really use it. In any case this is pretty much unrelated to the current patch... 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] Controlling changes in plpgsql variable resolution
"David E. Wheeler" writes: > On Oct 19, 2009, at 12:05 PM, Tom Lane wrote: >> Where exactly would you put the modifier, and why is that better than >> the existing #option convention? > CREATE OR REPLACE FUNCTION foo() > RETURNS BOOLEAN > LANGUAGE plpgsql WITH opt1, opt2 > AS $$...$$; > That is, the specification of options is made outside of the language > in question. I don't think I particularly care for this. It's inventing a global mechanism to cover a problem that we currently have one instance of in one PL. That's a mighty thin justification. Also, I tend to think that you should have several instances of a problem before you venture to design a global solution --- else your one-size-fits-all solution might turn out to be a lot less general than you thought. 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] Controlling changes in plpgsql variable resolution
I wrote: > Where exactly would you put the modifier, and why is that better than > the existing #option convention? BTW, it occurs to me that since that's undocumented, not everyone might know what I'm talking about. There's some code in plpgsql that allows you to write #option dump at the very beginning of a plpgsql function body, and get a dump of the plpgsql syntax tree. Since this was never intended for anything except low-level debugging, it never got documented --- but the obvious intention was that other sorts of options might come along later. Now we have a case where a per-function option seems like just the ticket. 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 12:05 PM, Tom Lane wrote: What about adopting the modifier syntax you're adding to COPY? Where exactly would you put the modifier, and why is that better than the existing #option convention? CREATE OR REPLACE FUNCTION foo() RETURNS BOOLEAN LANGUAGE plpgsql WITH opt1, opt2 AS $$...$$; That is, the specification of options is made outside of the language in question. It might only effect a particular language (plpgsql in this case) and be ignored otherwise (or trigger an exception), but it's clean and very much like what you have elsewhere. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 2:47 PM, Tom Lane wrote: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Sorry if this is obvious to everyone else, but *when* will the error throw? During CREATE FUNCTION or during runtime? I'm secretly hoping that it'll throw during CREATE FUNCTION. I'd rather have my entire schema creation transaction abort so I can fix the problems up-front, rather than at "random" while the application is running. eric -- 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] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
Andrew Chernow píše v po 19. 10. 2009 v 14:14 -0400: > > # ./pg_ctl > > ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file > > /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value > > 0xfd7fff1cf210 does not fit > > Killed > > > > "symbol (unknown)". Can you turn on debugging symbols? Knowing the > symbol may point to a library that was not compiled properly. Also you can try to run LD_DEBUG=basic ./pg_ctl and also elfdump | grep R_AMD64_32 it should show when symbols came from. By theway what S10 version do you use? ld -V and uname -a also helps. > > So I run 'ldd pg_ctl' to see if everything is linking ok. > > > > And I'm wondering if there is a problem with libpq.so.5 as mentioned in > > the original error > > > > # file /usr/local/postgres64/lib/libpq.so.5 > > > > > > /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64 > > Version 1 [SSE CMOV], dynamically linked, not stripped > > > > Ok. So looking good. Maybe there is a library or header libpq needs > > that I'm missing in 64 bit? > > > > # ldd /usr/local/postgres64/lib/libpq.so.5 > > Are you sure that all pg_ctl referenced libraries and all libpq.so > referenced libraries were built as 64-bit using PIC? Are you linking > with any static library that may contain 32-bit objects? That error is > most commonly PIC or arch-mismatch. > Agree, I went through linker bugs and missing PIC is often root cause of this problem. See http://bugs.opensolaris.org/bugdatabase/view_bug.do?bug_id=6261066 Problem was that ./configure badly setup PIC switch on amd64 platform. Please, could you compile pure postgreSQL without other own libraries like readline and openssl? It should help to find which library is culprit. Zdenek -- 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] Controlling changes in plpgsql variable resolution
"David E. Wheeler" writes: > On Oct 19, 2009, at 11:47 AM, Tom Lane wrote: >> 2. Also invent a #option syntax that allows the GUC to be overridden >> per-function. (Since the main GUC is SUSET, we can't just use a >> per-function SET to override it. There are other ways we could do >> this but none seem less ugly than #option...) > What about adopting the modifier syntax you're adding to COPY? Where exactly would you put the modifier, and why is that better than the existing #option convention? 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 11:47 AM, Tom Lane wrote: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Make it SUSET so that unprivileged users can't break things by twiddling it; but it's still possible for the DBA to set it per-database or per-user. 2. Also invent a #option syntax that allows the GUC to be overridden per-function. (Since the main GUC is SUSET, we can't just use a per-function SET to override it. There are other ways we could do this but none seem less ugly than #option...) What about adopting the modifier syntax you're adding to COPY? David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
Andrew Dunstan writes: > Tom Lane wrote: >> (a) Nobody but me is afraid of the consequences of treating this as >> a GUC. (I still think you're all wrong, but so be it.) > I can't say I'm happy about it. For one thing, the granularity seems all > wrong. I'd rather be able to keep backwards compatibility on a function > by function basis. Or would the value of the GUC at the time the > function was created stick? Again, I can't see making a GUC that works fundamentally differently from the rest of them. Given this round of feedback, I make the following proposal: 1. Invent a GUC that has the settings backwards-compatible, oracle-compatible, throw-error (exact spellings TBD). Factory default, at least for a few releases, will be throw-error. Make it SUSET so that unprivileged users can't break things by twiddling it; but it's still possible for the DBA to set it per-database or per-user. 2. Also invent a #option syntax that allows the GUC to be overridden per-function. (Since the main GUC is SUSET, we can't just use a per-function SET to override it. There are other ways we could do this but none seem less ugly than #option...) Given that the global default will be throw-error, I don't feel a need to kluge up pg_dump to insert #option in old function definitions; that's ugly and there are too many cases it would not cover. But that could be added to this proposal if folks feel strongly enough. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
Merlin Moncure writes: > Maybe invent a new language handler? plpgsql2 or shorten to pgsql? > Now you can mess around all you want (and maybe fix some other > compatibility warts at the same time). Well, pl/psm is out there, and might even make it into core someday. I don't find a lot of attraction in inventing a new language type that's only marginally different from plpgsql --- that approach doesn't scale up to handling multiple compatibility issues, at least not unless you fix them all at the same time. 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] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
# ./pg_ctl ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 0xfd7fff1cf210 does not fit Killed "symbol (unknown)". Can you turn on debugging symbols? Knowing the symbol may point to a library that was not compiled properly. So I run 'ldd pg_ctl' to see if everything is linking ok. And I'm wondering if there is a problem with libpq.so.5 as mentioned in the original error # file /usr/local/postgres64/lib/libpq.so.5 /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64 Version 1 [SSE CMOV], dynamically linked, not stripped Ok. So looking good. Maybe there is a library or header libpq needs that I'm missing in 64 bit? # ldd /usr/local/postgres64/lib/libpq.so.5 Are you sure that all pg_ctl referenced libraries and all libpq.so referenced libraries were built as 64-bit using PIC? Are you linking with any static library that may contain 32-bit objects? That error is most commonly PIC or arch-mismatch. -- Andrew Chernow eSilo, LLC every bit counts http://www.esilo.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] Controlling changes in plpgsql variable resolution
Tom Lane wrote: (a) Nobody but me is afraid of the consequences of treating this as a GUC. (I still think you're all wrong, but so be it.) I can't say I'm happy about it. For one thing, the granularity seems all wrong. I'd rather be able to keep backwards compatibility on a function by function basis. Or would the value of the GUC at the time the function was created stick? What are the probabilities that the OpenACSes of the world will just set the value to "backward compatible" instead of touching their code? Quite high, I should say. 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] Controlling changes in plpgsql variable resolution
Tom Lane wrote: > (a) Nobody but me is afraid of the consequences of treating this as > a GUC. Well, it seems dangerous to me, but I'm confident we can cover this within our shop, so I'm reluctant to take a position on it. I guess the main question is whether we want to allow an Oracle-compatibility mode, knowing it's a foot-gun. Without it we'd likely make extra work for someone converting from Oracle to PostgreSQL, although they would be likely to fix bugs during the cleanup work. Based on previous decisions I've seen here, I would have expected people to just go with an error, period; especially since it would simplify the code. > (b) Everybody agrees that a "throw error" setting would be helpful. That's the only setting I would use on any of our databases, if it were a GUC. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
Robert Haas writes: > On Mon, Oct 19, 2009 at 1:50 PM, Tom Lane wrote: >> (a) Nobody but me is afraid of the consequences of treating this as >> a GUC. (I still think you're all wrong, but so be it.) > I'm afraid of it, I'm just not sure I have a better idea. It wouldn't > bother me a bit if we made the only available behavior "throw an > error", but I'm afraid it will bother someone else. > Is there a chance we could make this a GUC, but only allow it to be > changed at the function level, with no way to override the server > default? It seems to me that the chances of blowing up the world > would be a lot lower that way, though possibly still not low enough. I don't particularly care to invent a new GUC class just for this, but if we think the issue is important enough, we could (a) make the GUC superuser-only (b) invent a #option or similar syntax to override the GUC per-function. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 1:50 PM, Tom Lane wrote: > Pavel Stehule writes: >> ambiguous identifiers is probably the top reason of some plpgsql's >> mysterious errors. More times I found wrong code - sometime really >> important (some security checks). I never found good code with >> ambiguous identifiers - so for me, exception is good. But - there will >> be lot of working applications that contains this hidden bug - and >> works "well". So it could be a problem. GUC should be a solution. > > So the conclusions so far are: > > (a) Nobody but me is afraid of the consequences of treating this as > a GUC. (I still think you're all wrong, but so be it.) I'm afraid of it, I'm just not sure I have a better idea. It wouldn't bother me a bit if we made the only available behavior "throw an error", but I'm afraid it will bother someone else. Is there a chance we could make this a GUC, but only allow it to be changed at the function level, with no way to override the server default? It seems to me that the chances of blowing up the world would be a lot lower that way, though possibly still not low enough. > (b) Everybody agrees that a "throw error" setting would be helpful. > > I am not sure there's any consensus on what the default setting should > be, though. Can we get away with making the default be "throw error"? > What are the probabilities that the OpenACSes of the world will just > set the value to "backward compatible" instead of touching their code? > Do we need/want a hack in pg_dump to attach a SET to functions dumped > from old DBs? I've already commented on most of these (recap: yes, very high, yes) so I'll refrain from beating a dead horse. ...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] Controlling changes in plpgsql variable resolution
Pavel Stehule writes: > ambiguous identifiers is probably the top reason of some plpgsql's > mysterious errors. More times I found wrong code - sometime really > important (some security checks). I never found good code with > ambiguous identifiers - so for me, exception is good. But - there will > be lot of working applications that contains this hidden bug - and > works "well". So it could be a problem. GUC should be a solution. So the conclusions so far are: (a) Nobody but me is afraid of the consequences of treating this as a GUC. (I still think you're all wrong, but so be it.) (b) Everybody agrees that a "throw error" setting would be helpful. I am not sure there's any consensus on what the default setting should be, though. Can we get away with making the default be "throw error"? What are the probabilities that the OpenACSes of the world will just set the value to "backward compatible" instead of touching their code? Do we need/want a hack in pg_dump to attach a SET to functions dumped from old DBs? 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] postgres 8.3.8 and Solaris 10_x86 64 bit problems?
Zdenek Kotala wrote: I can point on this article: http://tweakers.net/reviews/649/all/database-test-sun-ultrasparc-t1-vs-punt-amd-opteron.html Zdenek Ok so I'm checking everything in my environment. The system actually builds postgres with openssl98k. Comes back and says it's ready to install. I run 'make install' and try to run something like pg_ctl again. Seem to be seeing the same results. # file pg_ctl pg_ctl: ELF 64-bit LSB executable AMD64 Version 1 [SSE FXSR CMOV FPU], dynamically linked, not stripped # ./pg_ctl ld.so.1: pg_ctl: fatal: relocation error: R_AMD64_32: file /usr/local/postgres64/lib/libpq.so.5: symbol (unknown): value 0xfd7fff1cf210 does not fit Killed So I run 'ldd pg_ctl' to see if everything is linking ok. libpq.so.5 =>/usr/local/postgres64/lib/libpq.so.5 libm.so.2 => /usr/lib/64/libm.so.2 libxml2.so.2 => /usr/lib/64/libxml2.so.2 libz.so.1 => /usr/lib/64/libz.so.1 libreadline.so.6 => /usr/local/lib/libreadline.so.6 libcurses.so.1 =>/usr/lib/64/libcurses.so.1 librt.so.1 =>/usr/lib/64/librt.so.1 libsocket.so.1 =>/usr/lib/64/libsocket.so.1 libc.so.1 => /usr/lib/64/libc.so.1 libpthread.so.1 => /usr/lib/64/libpthread.so.1 libnsl.so.1 => /lib/64/libnsl.so.1 libgcc_s.so.1 => /usr/sfw/lib/amd64/libgcc_s.so.1 libaio.so.1 => /lib/64/libaio.so.1 libmd.so.1 =>/lib/64/libmd.so.1 libmp.so.2 =>/lib/64/libmp.so.2 libscf.so.1 => /lib/64/libscf.so.1 libdoor.so.1 => /lib/64/libdoor.so.1 libuutil.so.1 => /lib/64/libuutil.so.1 libgen.so.1 => /lib/64/libgen.so.1 And I'm wondering if there is a problem with libpq.so.5 as mentioned in the original error # file /usr/local/postgres64/lib/libpq.so.5 /usr/local/postgres64/lib/libpq.so.5: ELF 64-bit LSB dynamic lib AMD64 Version 1 [SSE CMOV], dynamically linked, not stripped Ok. So looking good. Maybe there is a library or header libpq needs that I'm missing in 64 bit? # ldd /usr/local/postgres64/lib/libpq.so.5 libsocket.so.1 =>/usr/lib/64/libsocket.so.1 libpthread.so.1 => /usr/lib/64/libpthread.so.1 libc.so.1 => /usr/lib/64/libc.so.1 libnsl.so.1 => /lib/64/libnsl.so.1 libmp.so.2 =>/lib/64/libmp.so.2 libmd.so.1 =>/lib/64/libmd.so.1 libscf.so.1 => /lib/64/libscf.so.1 libdoor.so.1 => /lib/64/libdoor.so.1 libuutil.so.1 => /lib/64/libuutil.so.1 libgen.so.1 => /lib/64/libgen.so.1 libm.so.2 => /lib/64/libm.so.2 Looks good. I'm not sure where to go from here. I have everything else I need built in 64 bit except for Postgres with ssl :/ I've spent the last few weeks googling and talking to people about it. Not sure what I'm missing here. Thanks! -- 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] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 12:49 PM, Tom Lane wrote: > "David E. Wheeler" writes: >> I'd sure love $, as it's like shell, Perl, and other stuff. > > This discussion has gotten utterly off track. The problem I am trying > to solve is a non-Oracle-compatible behavior in plpgsql. I have got > substantially less than zero interest in proposals that "solve" the > problem by introducing notations that don't even pretend to be > compatible. OK. In that case, it seems like we should offer options #2 and #3 with a GUC or #option to switch between them. Nobody has made an argument in favor of keeping #1 around. I'm still strongly of the opinion that #3 (error) should be the default behavior to avoid silent failures. ...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] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 12:49 PM, Tom Lane wrote: > "David E. Wheeler" writes: >> I'd sure love $, as it's like shell, Perl, and other stuff. > > This discussion has gotten utterly off track. The problem I am trying > to solve is a non-Oracle-compatible behavior in plpgsql. I have got > substantially less than zero interest in proposals that "solve" the > problem by introducing notations that don't even pretend to be > compatible. Personally, I'd vote against a GUC option. I just plain don't like the idea that a function could do different things depending on server configuration. TBH, I'm not very happy with #option either. That said, I agree that Oracle method is far better. Maybe invent a new language handler? plpgsql2 or shorten to pgsql? Now you can mess around all you want (and maybe fix some other compatibility warts at the same time). merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
2009/10/19 Kevin Grittner : > "David E. Wheeler" wrote: > >> I'd be in favor of a GUC that I could turn on to throw an error >> when there's an ambiguity. > > I would consider hiding one definition with another very bad form, so > I would prefer to have plpgsql throw an error when that happens. I > don't particularly care whether that is the only supported behavior or > whether there's a GUC to control it, or what its default is, if > present. > ambiguous identifiers is probably the top reason of some plpgsql's mysterious errors. More times I found wrong code - sometime really important (some security checks). I never found good code with ambiguous identifiers - so for me, exception is good. But - there will be lot of working applications that contains this hidden bug - and works "well". So it could be a problem. GUC should be a solution. Pavel > -Kevin > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rejecting weak passwords
Andrew Dunstan writes: > Alvaro Herrera wrote: >> We do, if you have you server grabbing passwords from LDAP or whatever >> external auth service you use. That would be more secure than anything >> mentioned in this thread, because the password enforcement could work on >> unencrypted passwords without adverse consequences. > We don't have it today for passwords that postgres manages. Unless we're > going to rely on an external auth source completely, I think there's a > good case for the hooks, but not for any of the other "adjustments" that > people have suggested. Yeah. Installing LDAP or Kerberos or whatever is sensible if you have a need for a central auth server anyway. If you are just trying to run a database, it's a major additional investment of effort, and I can't quibble at all with people who think that it's unreasonable to have to do that just to have some modicum of a password policy. I also am of the opinion that it's reasonable to provide a hook or two for this purpose, but not to go further than that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
2009/10/19 Robert Haas : > On Mon, Oct 19, 2009 at 12:48 PM, Dean Rasheed > wrote: >> This is a WIP patch to replace the after-trigger queues with TID bitmaps >> to prevent them from using excessive amounts of memory. Each round of >> trigger executions is a modified bitmap heap scan. > > If the bitmap becomes lossy, how do you preserve the correct semantics? > > ...Robert > The idea is that it filters by the transaction ID and command ID of modified rows to see what's been updated in the command(s) the trigger is for... - 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] Controlling changes in plpgsql variable resolution
"David E. Wheeler" wrote: > I'd be in favor of a GUC that I could turn on to throw an error > when there's an ambiguity. I would consider hiding one definition with another very bad form, so I would prefer to have plpgsql throw an error when that happens. I don't particularly care whether that is the only supported behavior or whether there's a GUC to control it, or what its default is, if present. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Scaling up deferred unique checks and the after trigger queue
On Mon, Oct 19, 2009 at 12:48 PM, Dean Rasheed wrote: > This is a WIP patch to replace the after-trigger queues with TID bitmaps > to prevent them from using excessive amounts of memory. Each round of > trigger executions is a modified bitmap heap scan. If the bitmap becomes lossy, how do you preserve the correct semantics? ...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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 9:49 AM, Tom Lane wrote: I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that "solve" the problem by introducing notations that don't even pretend to be compatible. Party pooper. I'd be in favor of a GUC that I could turn on to throw an error when there's an ambiguity. As for which way it should go, I have no dog in that pony hunt. Or something. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
"David E. Wheeler" writes: > I'd sure love $, as it's like shell, Perl, and other stuff. This discussion has gotten utterly off track. The problem I am trying to solve is a non-Oracle-compatible behavior in plpgsql. I have got substantially less than zero interest in proposals that "solve" the problem by introducing notations that don't even pretend to be compatible. 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] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 9:29 AM, Stephen Frost wrote: Uh, what dollar quoting? $_$ is what I typically use, so I wouldn't expect a $ prefix to cause a problem. I think it'd be more of an issue because pl/pgsql still uses $1 and whatnot internally (doesn't it?). Yes, but that's no more an issue than it is in Perl, where the same $n variables are globals. The issue with dollar quoting is that you can put anything between the dollar signs. So if you have two $variables, they can get in the way. Potentially. But perhaps the lexer and/or Parser won't be confused by that, Tom? I'd sure love $, as it's like shell, Perl, and other stuff. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Application name patch - v2
2009/10/19 Stephen Frost : > * Pavel Stehule (pavel.steh...@gmail.com) wrote: >> 2009/10/19 Stephen Frost : >> > * Pavel Stehule (pavel.steh...@gmail.com) wrote: >> >> Superuser permission could not be a problem. Simple security definer >> >> function can do it. >> > >> > Then you've defeated the point of making it superuser-only. >> >> no. Because when I write security definer function, then I explicitly >> allow an writing for some roles. When I don't write this function, >> then GUC is secure. > > And what happens when those 'some roles' are used by broken > applications? You don't get to say "make it superuser only" and then > turn around and tell people to hack around the fact that it's superuser > only to be able to use it. That's not a solution. You don't understand me. When I would to have a secure environment, then I don't write this function. So there will not be a posibility to change application name from session. Pavel > > Stephen > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkrchUYACgkQrzgMPqB3kij8nACfUrF/wkpsORpXiN0QgbXvONdi > ghYAn19MpPNnRrf9BxmIOVBRR212JU6c > =c5tL > -END PGP SIGNATURE- > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rejecting weak passwords
I wrote: > A server-side plugin can provide a guarantee that there are no bad > passwords (for some value of bad, and with some possible adverse > consequences). We don't have that today. BTW, it strikes me that ALTER USER RENAME introduces an interesting hazard for such a plugin. Consider CREATE USER joe; ALTER USER joe PASSWORD joe; -- presumably, plugin will reject this ALTER USER joe PASSWORD mumblefrotz; -- assume this is considered OK ALTER USER joe RENAME TO mumblefrotz; Now we have a user with name equal to password, which no sane security policy will think is a good thing, but the plugin had no chance to prevent it. In the case where the password is stored MD5-crypted, we clear it on RENAME because of the fact that the username is part of the hash. (We had always thought that was a bug^Wimplementation restriction, but now it looks like a feature.) So in normal practice the above hazard doesn't exist; but it would for cleartext passwords. One thing we could do is *always* clear the password on RENAME. Another is to keep the cleartext password, but pass the new name and password through the plugin before allowing the RENAME to succeed. Since the PW is cleartext, presumably the plugin won't have any problem checking it. The latter however seems like we are getting a security-critical behavior out of a chance combination of implementation artifacts, which doesn't make me feel comfortable. Thoughts? 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] Rejecting weak passwords
Alvaro Herrera wrote: Except that your first statement is false. It is not possible currently for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. A server-side plugin can provide a guarantee that there are no bad passwords (for some value of bad, and with some possible adverse consequences). We don't have that today. We do, if you have you server grabbing passwords from LDAP or whatever external auth service you use. That would be more secure than anything mentioned in this thread, because the password enforcement could work on unencrypted passwords without adverse consequences. We don't have it today for passwords that postgres manages. Unless we're going to rely on an external auth source completely, I think there's a good case for the hooks, but not for any of the other "adjustments" that people have suggested. 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] Controlling changes in plpgsql variable resolution
* David E. Wheeler (da...@kineticode.com) wrote: > On Oct 19, 2009, at 8:36 AM, Robert Haas wrote: > >> I think warnings are too easy to miss, but I agree your other >> suggestion. I know you can write function_name.variable_name, but >> that's often massively long-winded. We either need a short, fixed >> prefix, or some kind of sigil. I previously suggested ? to parallel >> ECPG, but Tom didn't like it. I still do. :-) > > I suppose that $ would interfere with dollar quoting. What about @ or @@ > (sorry, I did mess with MSSQL back in the 90s). Uh, what dollar quoting? $_$ is what I typically use, so I wouldn't expect a $ prefix to cause a problem. I think it'd be more of an issue because pl/pgsql still uses $1 and whatnot internally (doesn't it?). Stephen signature.asc Description: Digital signature
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 8:36 AM, Robert Haas wrote:
I think warnings are too easy to miss, but I agree your other
suggestion. I know you can write function_name.variable_name, but
that's often massively long-winded. We either need a short, fixed
prefix, or some kind of sigil. I previously suggested ? to parallel
ECPG, but Tom didn't like it. I still do. :-)
I suppose that $ would interfere with dollar quoting. What about @ or
@@ (sorry, I did mess with MSSQL back in the 90s).
Hrm…PostgreSQL is starting to have the same problem as Perl: running
out of characters because they're used for operators. :var would be
perfect, if it wasn't for psql. ?var is okay, I guess, if a bit…
questionable. Are {braces} used for anything?
Best,
David
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Oct 19, 2009, at 7:54 AM, Stephen Frost wrote: 4. Resolve ambiguous names as query column, but throw warning #4 would be my vote, followed by #3. To be perfectly honest, I'd be a whole lot happier with a pl/pgsql that let me prefix variable names with a '$' or similar to get away from this whole nonsense. I've been very tempted to tell everyone I work with to start prefixing their variables names with '_' except that it ends up looking just plain ugly. +1, just what I was thinking. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Rejecting weak passwords
Tom Lane escribió: > Peter Eisentraut writes: > > On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: > >> I guess I misunderstood something there, but I had assumed that the > >> checkbox item read something like: "Does the product offer password > >> policy enforcement?" (to quote Dave Page). > > > The answer to that is currently "Yes, with external tools". Using the > > plugin approach, the answer will remain "Yes, with external tools". So > > we wouldn't gain much. > > Except that your first statement is false. It is not possible currently > for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. > A server-side plugin can provide a guarantee that there are no bad > passwords (for some value of bad, and with some possible adverse > consequences). We don't have that today. We do, if you have you server grabbing passwords from LDAP or whatever external auth service you use. That would be more secure than anything mentioned in this thread, because the password enforcement could work on unencrypted passwords without adverse consequences. -- 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] Rejecting weak passwords
Peter Eisentraut writes: > On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: >> I guess I misunderstood something there, but I had assumed that the >> checkbox item read something like: "Does the product offer password >> policy enforcement?" (to quote Dave Page). > The answer to that is currently "Yes, with external tools". Using the > plugin approach, the answer will remain "Yes, with external tools". So > we wouldn't gain much. Except that your first statement is false. It is not possible currently for any tool to prevent someone from doing ALTER USER joe PASSWORD joe. A server-side plugin can provide a guarantee that there are no bad passwords (for some value of bad, and with some possible adverse consequences). We don't have that today. 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] Application name patch - v2
Robert Haas wrote: > Tom Lane wrote: >> I think Pavel's entire line of argument is utter nonsense. > +1. I can't even understand why we're still arguing about this. Agreed. One premise of the whole concept was "don't even think of using it for security"[1]. That's not it's purpose; so any criticisms on that basis are irrelevant. Claims that it opens new security holes if you *don't* try to use it for this purpose don't seem to have any merit that I can see; I don't think Pavel has even attempted to put such an argument forward. -Kevin [1] http://archives.postgresql.org/message-id/407d949e0907161237r76ebd92av6836c6563d8a2...@mail.gmail.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] Rejecting weak passwords
On Mon, 2009-10-19 at 14:54 +0200, Albe Laurenz wrote: > Peter Eisentraut wrote: > > Note that this solution will still not satisfy the original checkbox > > requirement. > > I guess I misunderstood something there, but I had assumed that the > checkbox item read something like: "Does the product offer password > policy enforcement?" (to quote Dave Page). The answer to that is currently "Yes, with external tools". Using the plugin approach, the answer will remain "Yes, with external tools". So we wouldn't gain much. -- 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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
On Mon, 2009-10-19 at 12:56 -0300, Alvaro Herrera wrote: > Simon Riggs wrote: > > > > On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: > > > Tom Lane wrote: > > > > Peter Eisentraut writes: > > > > > Is there a good reason for $subject, other than that the code is > > > > > entangled > > > > > with other ALTER TABLE code? > > > > > > > > I think it could be lower, but it would take nontrivial restructuring of > > > > the ALTER TABLE support. In particular, consider what happens when you > > > > have a list of subcommands that don't all require the same lock level. > > > > I think you'd need to scan the list and find the highest required lock > > > > level before starting ... > > > > > > IIRC there was a patch from Simon to address this issue, but it had some > > > holes which he didn't have time to close, so it sank. Maybe this can be > > > resurrected and fixed. > > > > I was intending to finish that patch in this release cycle. > > Since you're busy with Hot Standby, any chance you could pass it on? If you'd like. It's mostly finished, just one last thing to finish: atomic changes to pg_class via an already agreed API. -- Simon Riggs www.2ndQuadrant.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] ALTER TABLE SET STATISTICS requires AccessExclusiveLock
Simon Riggs wrote: > > On Fri, 2009-08-07 at 15:58 -0400, Alvaro Herrera wrote: > > Tom Lane wrote: > > > Peter Eisentraut writes: > > > > Is there a good reason for $subject, other than that the code is > > > > entangled > > > > with other ALTER TABLE code? > > > > > > I think it could be lower, but it would take nontrivial restructuring of > > > the ALTER TABLE support. In particular, consider what happens when you > > > have a list of subcommands that don't all require the same lock level. > > > I think you'd need to scan the list and find the highest required lock > > > level before starting ... > > > > IIRC there was a patch from Simon to address this issue, but it had some > > holes which he didn't have time to close, so it sank. Maybe this can be > > resurrected and fixed. > > I was intending to finish that patch in this release cycle. Since you're busy with Hot Standby, any chance you could pass it on? -- 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] Application name patch - v2
David Fetter wrote: > Could you point to a reference for this? It could help the rest of us > see what you're aiming for even better :) Sybase Adaptive Server Enterprise (ASE) clientapplname varchar(30) column in sysprocesses table: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.tables/html/tables/tables50.htm It can be set (for example) in the LOGINREC structure with DBSETLAPP: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc32600.1500/html/dblib/dblib18.htm SET clientapplname command to set on the fly by running SQL on the connection: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.commands/html/commands/commands85.htm -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Writeable CTEs and side effects
Marko Tiikkaja writes: > I'm looking at this, and if I understood correctly, you're suggesting > we'd add a WithClause to InsertStmt. Would we also allow this? Yeah, we could eventually do all that. I think supporting it in SELECT would be plenty to start with, though. 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] Application name patch - v2
On Mon, Oct 19, 2009 at 10:36 AM, Tom Lane wrote: > Dave Page writes: >> On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule >> wrote: >>> I thing, so change of original name should generate warning. > >> Well, if other people think that's necessary, it's certainly possible. > > I think Pavel's entire line of argument is utter nonsense. He's setting > up a straw man that has nothing to do with any actually likely use of > the variable. +1. I can't even understand why we're still arguing about this. Other than Pavel, everyone thinks this is a complete non-problem, and Pavel's hypothesis basically boils down to "someone might use this feature in a stupid and naive way". Well, sure. They might. So what? > I do agree with Peter's concerns about limiting the character set of the > name string, and maybe there should be some sort of length limit too. I don't have a strong feeling about this. If limiting this to 7-bit characters solves some nasty encoding problems or something, then fine, but otherwise I think we can just escape what we emit into the log and say that users who log this information should have a sufficiently sophisticated log parser to cope with 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
Re: [HACKERS] Controlling changes in plpgsql variable resolution
On Mon, Oct 19, 2009 at 10:54 AM, Stephen Frost wrote: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> I think there are basically three behaviors that we could offer: >> >> 1. Resolve ambiguous names as plpgsql (historical PG behavior) >> 2. Resolve ambiguous names as query column (Oracle behavior) >> 3. Throw error if name is ambiguous (useful for finding problems) > > 4. Resolve ambiguous names as query column, but throw warning > > #4 would be my vote, followed by #3. To be perfectly honest, I'd be a > whole lot happier with a pl/pgsql that let me prefix variable names with > a '$' or similar to get away from this whole nonsense. I've been very > tempted to tell everyone I work with to start prefixing their variables > names with '_' except that it ends up looking just plain ugly. I think warnings are too easy to miss, but I agree your other suggestion. I know you can write function_name.variable_name, but that's often massively long-winded. We either need a short, fixed prefix, or some kind of sigil. I previously suggested ? to parallel ECPG, but Tom didn't like it. I still do. :-) ...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] COPY enhancements
On Mon, Oct 19, 2009 at 11:21 AM, Alvaro Herrera wrote: > Gokulakannan Somasundaram escribió: > >> Actually this problem is present even in today's transaction id scenario and >> the only way we avoid is by using freezing. Can we use a similar approach? >> This freezing should mean that we are freezing the sub-transaction in order >> to avoid the sub-transaction wrap around failure. > > This would mean we would have to go over the data inserted by the > subtransaction and mark it as "subxact frozen". Some sort of sub-vacuum > if you will (because it obviously needs to work inside a transaction). > Doesn't sound real workable to me. Especially because the XID consumed by the sub-transaction would still be consumed, advancing the global XID counter. Reclaiming the XIDs after the fact doesn't fix anything as far as I can see. ...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] Application name patch - v2
* Pavel Stehule (pavel.steh...@gmail.com) wrote: > 2009/10/19 Stephen Frost : > > * Pavel Stehule (pavel.steh...@gmail.com) wrote: > >> Superuser permission could not be a problem. Simple security definer > >> function can do it. > > > > Then you've defeated the point of making it superuser-only. > > no. Because when I write security definer function, then I explicitly > allow an writing for some roles. When I don't write this function, > then GUC is secure. And what happens when those 'some roles' are used by broken applications? You don't get to say "make it superuser only" and then turn around and tell people to hack around the fact that it's superuser only to be able to use it. That's not a solution. Stephen signature.asc Description: Digital signature
Re: [HACKERS] COPY enhancements
Gokulakannan Somasundaram escribió: > Actually this problem is present even in today's transaction id scenario and > the only way we avoid is by using freezing. Can we use a similar approach? > This freezing should mean that we are freezing the sub-transaction in order > to avoid the sub-transaction wrap around failure. This would mean we would have to go over the data inserted by the subtransaction and mark it as "subxact frozen". Some sort of sub-vacuum if you will (because it obviously needs to work inside a transaction). Doesn't sound real workable to me. -- 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] Application name patch - v2
2009/10/19 Stephen Frost : > * Pavel Stehule (pavel.steh...@gmail.com) wrote: >> Superuser permission could not be a problem. Simple security definer >> function can do it. > > Then you've defeated the point of making it superuser-only. no. Because when I write security definer function, then I explicitly allow an writing for some roles. When I don't write this function, then GUC is secure. Pavel > > I don't think that changing the app name deserves a warning, to be > perfectly honest. Notice should be sufficient. > > Thanks, > > Stephen > > -BEGIN PGP SIGNATURE- > Version: GnuPG v1.4.9 (GNU/Linux) > > iEYEARECAAYFAkrceMsACgkQrzgMPqB3kihrpwCePXXJLxXIpvfHF0fMXbO6Pn94 > uJcAn2cnT97QNqeRW2coKRDZfWVKaXxz > =xvXs > -END PGP SIGNATURE- > > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Application name patch - v2
On Mon, Oct 19, 2009 at 3:49 PM, Tom Lane wrote: > Stephen Frost writes: >> * Tom Lane (t...@sss.pgh.pa.us) wrote: >>> I do agree with Peter's concerns about limiting the character set of the >>> name string, and maybe there should be some sort of length limit too. > >> I was thinking we might just declare it of type 'name'.. > > 'name' wouldn't help, since it's pretty character-set-agnostic. > Anyway this is a GUC not a table column. As a sidenote, in the stats part of this patch I did limit the length to NAMEDATALEN for the purposes of sizing the shared memory allocation, however it's otherwise unlimited in length. Practically that just means that like the current query, the application name may be truncated when viewed through pg_stat_get_activity(). > Thinking about it more, it should be sufficient if we can ensure that > the value is in the database encoding; logging of statements will > already cause pretty much any legal DB-encoded string to be written to > the log, so if you have a problem with that then you've already got > a problem to fix. Right - that's what I was saying to Peter earlier. That can of course be turned off though > This is no issue for an ordinary SET, but AFAIR we do not have a good > story for handling non-ASCII stuff arriving within the initial > connection request packet. It might be time to try to do something > about that. Or we could just restrict those values to ASCII. It would seem sensible to apply the same rule to the connection string and SET, if only for consistency (at least as far as application_name is concerned). I know that use of Japanese/Chinese characters in database names is not uncommon however, so restricting connection strings to ASCII in general might not go down well. -- Dave Page EnterpriseDB UK: 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] Controlling changes in plpgsql variable resolution
* Tom Lane (t...@sss.pgh.pa.us) wrote: > I think there are basically three behaviors that we could offer: > > 1. Resolve ambiguous names as plpgsql (historical PG behavior) > 2. Resolve ambiguous names as query column (Oracle behavior) > 3. Throw error if name is ambiguous (useful for finding problems) 4. Resolve ambiguous names as query column, but throw warning #4 would be my vote, followed by #3. To be perfectly honest, I'd be a whole lot happier with a pl/pgsql that let me prefix variable names with a '$' or similar to get away from this whole nonsense. I've been very tempted to tell everyone I work with to start prefixing their variables names with '_' except that it ends up looking just plain ugly. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v2
Stephen Frost writes: > * Tom Lane (t...@sss.pgh.pa.us) wrote: >> I do agree with Peter's concerns about limiting the character set of the >> name string, and maybe there should be some sort of length limit too. > I was thinking we might just declare it of type 'name'.. 'name' wouldn't help, since it's pretty character-set-agnostic. Anyway this is a GUC not a table column. Thinking about it more, it should be sufficient if we can ensure that the value is in the database encoding; logging of statements will already cause pretty much any legal DB-encoded string to be written to the log, so if you have a problem with that then you've already got a problem to fix. This is no issue for an ordinary SET, but AFAIR we do not have a good story for handling non-ASCII stuff arriving within the initial connection request packet. It might be time to try to do something about that. Or we could just restrict those values to ASCII. 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] Application name patch - v2
On Mon, Oct 19, 2009 at 3:42 PM, Massa, Harald Armin wrote: > > Would'nt this also make sense for PostgreSQL? That is, when no environment > is set, and no SET-command is issued, that the application name becomes the > default? That needs to be set by the application. As discussed previously, there's no way for libpq to get at argv[0]. -- Dave Page EnterpriseDB UK: 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] Application name patch - v2
> Sure. Here's a nice example from SQL Server as well as related doc links: > > http://blog.benhall.me.uk/2007/10/sql-connection-application-name.html > http://msdn.microsoft.com/en-us/library/ms189770.aspx > > http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.85%29.aspx > that looks as if the entry defaults to the application name (argv[0]) > Similar features are available in Oracle: > > http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_appin2.htm and I definitely know from Oracle, that the application name is the default without the progammer / user issuing any SET command. Would'nt this also make sense for PostgreSQL? That is, when no environment is set, and no SET-command is issued, that the application name becomes the default? Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality
Re: [HACKERS] Application name patch - v2
* Tom Lane (t...@sss.pgh.pa.us) wrote: > Dave Page writes: > > Well, if other people think that's necessary, it's certainly possible. > > I think Pavel's entire line of argument is utter nonsense. He's setting > up a straw man that has nothing to do with any actually likely use of > the variable. > > I do agree with Peter's concerns about limiting the character set of the > name string, and maybe there should be some sort of length limit too. I was thinking we might just declare it of type 'name'.. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v2
David Fetter writes: > On Mon, Oct 19, 2009 at 11:39:58AM +0100, Dave Page wrote: >> Please bear in mind that this feature is based on similar features in >> other DBMSs (and in fact, a feature in the JDBC spec) > Could you point to a reference for this? It could help the rest of us > see what you're aiming for even better :) Yeah. One thing I would like to see is a confirmation that this feature can actually be used by the JDBC driver to implement the relevant feature(s) of the JDBC spec. 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] Application name patch - v2
On Mon, Oct 19, 2009 at 3:17 PM, David Fetter wrote: > Could you point to a reference for this? It could help the rest of us > see what you're aiming for even better :) Sure. Here's a nice example from SQL Server as well as related doc links: http://blog.benhall.me.uk/2007/10/sql-connection-application-name.html http://msdn.microsoft.com/en-us/library/ms189770.aspx http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection.connectionstring%28VS.85%29.aspx Kris pointed out the JDBC usage of this (and some related properties): http://archives.postgresql.org/pgsql-hackers/2009-10/msg00726.php Similar features are available in Oracle: http://download.oracle.com/docs/cd/B10501_01/appdev.920/a96612/d_appin2.htm http://www.dba-oracle.com/security/the_client_identifier.htm (in this case, it is considered a security/auditing feature) -- Dave Page EnterpriseDB UK: 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] Application name patch - v2
* Pavel Stehule (pavel.steh...@gmail.com) wrote: > 2009/10/19 Heikki Linnakangas : > > Or are you saying that it should not be possible for the client to > > change the value after connecting? That limits the usefulness with > > connection pools. > > What I know, connections from connection pool without reset are shared > by one application. But I am not against some possibility to change > this value from application. I am against to possibility an change by > normal user. When we allow it, then this value has not any wight, > because any broken appliaction (via SQL injection) can change it. Broken applications have much bigger problems than this. Predicating what we would/should do on the assumption of an application that's broken just doesn't make sense to me. Stephen signature.asc Description: Digital signature
Re: [HACKERS] Application name patch - v2
Dave Page writes: > On Mon, Oct 19, 2009 at 12:57 PM, Pavel Stehule > wrote: >> I thing, so change of original name should generate warning. > Well, if other people think that's necessary, it's certainly possible. I think Pavel's entire line of argument is utter nonsense. He's setting up a straw man that has nothing to do with any actually likely use of the variable. I do agree with Peter's concerns about limiting the character set of the name string, and maybe there should be some sort of length limit too. 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] Application name patch - v2
* Pavel Stehule (pavel.steh...@gmail.com) wrote: > Superuser permission could not be a problem. Simple security definer > function can do it. Then you've defeated the point of making it superuser-only. I don't think that changing the app name deserves a warning, to be perfectly honest. Notice should be sufficient. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] Rejecting weak passwords
"Albe Laurenz" writes: > Bruce Momjian wrote: >> Password checks might include password complexity or non-reuse of >> passwords. This facility will require the client to send the password to >> the server in plain-text, so SSL and 'password' authentication is >> necessary to use this features. > So in my opinion that should be: > This facility will require to send new and changed password to > the server in plain-text, so it will require SSL, and the use > of encrypted passwords in CREATE/ALTER ROLE will have to be > disabled. Actually, not one word of *either* version should be in TODO. All of that is speculation about policies that a particular add-on module might or might not choose to enforce. 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] Writeable CTEs and side effects
Tom Lane wrote: Merlin Moncure writes: Is the above form: with x as (delete .. returning *) insert into y select * from x going to be allowed? I was informed on irc that it wasn't...it would have to be written as: insert into y with x as (delete .. returning *) select * from x I would think that we would require the former and forbid the latter. One of the basic limitations of the feature is going to be that you can only have WITH (something RETURNING) at the top level, and the latter syntax doesn't look like that to me. I'm looking at this, and if I understood correctly, you're suggesting we'd add a WithClause to InsertStmt. Would we also allow this? WITH t1 AS (DELETE FROM foo RETURNING *) INSERT INTO bar WITH t2 AS (VALUES(0)) SELECT * FROM t1 UNION ALL SELECT * FROM t2; I could also see use for adding this for UDPATE and DELETE too, i.e. WITH t AS (DELETE FROM foo RETURNING id) UPDATE bar SET foo_id = NULL FROM t WHERE t.id = bar.foo_id; Did I misunderstand something here? Regards, Marko Tiikkaja -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
