Re: [HACKERS] [GENERAL] Updating column on row update
On Sun, 2009-11-22 at 18:51 -0500, Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: I do think this comes up often enough that a built-in trigger update named column with result of expression on insert trigger might be desirable. There's something of the sort in contrib already, I believe, though it's so old it still uses abstime :-( What's wrong with abstime ? it is valid for timestamps up to 2038-01-19 and it's on-disk size smaller than other timestamp options -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
2009/11/24 Hannu Krosing ha...@2ndquadrant.com On Sun, 2009-11-22 at 18:51 -0500, Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: I do think this comes up often enough that a built-in trigger update named column with result of expression on insert trigger might be desirable. There's something of the sort in contrib already, I believe, though it's so old it still uses abstime :-( What's wrong with abstime ? it is valid for timestamps up to 2038-01-19 and it's on-disk size smaller than other timestamp options But it's very very deprecated and could be removed at any time. It's been so for years now, and I wouldn't want to *start* using something which is deprecated. Thom
Re: [HACKERS] [GENERAL] Updating column on row update
On Tue, 2009-11-24 at 09:46 +, Thom Brown wrote: 2009/11/24 Hannu Krosing ha...@2ndquadrant.com On Sun, 2009-11-22 at 18:51 -0500, Tom Lane wrote: Craig Ringer cr...@postnewspapers.com.au writes: I do think this comes up often enough that a built-in trigger update named column with result of expression on insert trigger might be desirable. There's something of the sort in contrib already, I believe, though it's so old it still uses abstime :-( What's wrong with abstime ? it is valid for timestamps up to 2038-01-19 and it's on-disk size smaller than other timestamp options But it's very very deprecated and could be removed at any time. It's been so for years now, and I wouldn't want to *start* using something which is deprecated. Thom I'd expect it to have an afterlife as a separately maintained type somewhere for those who care about data sizes, similar other space savers like ip4 type. -- Hannu Krosing http://www.2ndQuadrant.com PostgreSQL Scalability and Availability Services, Consulting and Training -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
Andrew Dunstan and...@dunslane.net wrote: Part of the motivation for allowing inline blocks was to allow for conditional logic. So you can do things like: DO $$ begin if not exists (select 1 from pg_tables where schemaname = 'foo' and tablename = 'bar') then create table foo.bar (x int, y text); end if; end; $$; It's a bit more verbose (maybe someone can streamline it) but it does give you CINE (for whatever flavor of CINE you want), as well as lots more complex possibilities than we can conceivably build into SQL. So we're conceding that this is a valid need and people will now have a way to meet it. Is the argument against having CINE syntax that it would be more prone to error than the above, or that the code would be so large and complex as to create a maintenance burden? (Is there some other reason I'm missing?) -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] [GENERAL] Updating column on row update
Kevin Grittner kevin.gritt...@wicourts.gov writes: So we're conceding that this is a valid need and people will now have a way to meet it. Is the argument against having CINE syntax that it would be more prone to error than the above, or that the code would be so large and complex as to create a maintenance burden? The argument against CINE is that it's unsafe. The fragment proposed by Andrew is no safer, of course, but it could be made safe by adding additional checks that the properties of the existing object are what the script expects. So in principle that's an acceptable approach, whereas CINE will never be safe. But actually I thought we had more or less concluded that CREATE OR REPLACE LANGUAGE would be acceptable (perhaps only if it's given without any extra args?). Or for that matter there seems to be enough opinion on the side of just installing plpgsql by default. CINE is a markedly inferior alternative to either of those. 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] [GENERAL] Updating column on row update
Tom Lane t...@sss.pgh.pa.us wrote: The argument against CINE is that it's unsafe. By no means rhetorically, is that based on the assumption that the statement would not validate that the existing object (if any) matches the supplied definition? The fragment proposed by Andrew is no safer, of course, but it could be made safe by adding additional checks that the properties of the existing object are what the script expects. Again, not rhetorically, is that assuming an error-free mapping of the CREATE statement to all the related system tables -- each time it is written by every user, individually? So in principle that's an acceptable approach, whereas CINE will never be safe. Only with the most simplistic implementation of CINE. I really don't see how that assertion holds up if there is checking of the supplied definition against the existing object. Even the most simplistic definition is arguably safer than CREATE OR REPLACE, since that can destroy existing data. An implementation which does the checking that you suggest, reviewed by this community to confirm that it is correct, would seem to beat out most people's home-grown attempts to write what you suggest. But actually I thought we had more or less concluded that CREATE OR REPLACE LANGUAGE would be acceptable (perhaps only if it's given without any extra args?). Or for that matter there seems to be enough opinion on the side of just installing plpgsql by default. CINE is a markedly inferior alternative to either of those. It sounded pretty much like a consensus on installing by default to me; however, that doesn't seem like it has anything to do with Andrew's example or my reply to it. -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] [GENERAL] Updating column on row update
On Tue, Nov 24, 2009 at 12:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: So we're conceding that this is a valid need and people will now have a way to meet it. Is the argument against having CINE syntax that it would be more prone to error than the above, or that the code would be so large and complex as to create a maintenance burden? The argument against CINE is that it's unsafe. The fragment proposed by Andrew is no safer, of course, but it could be made safe by adding additional checks that the properties of the existing object are what the script expects. So in principle that's an acceptable approach, whereas CINE will never be safe. Well, there can be methods extrinsic to the system for controlling this sort of thing. For example, I can provide a script, using CINE, that will either install version 2 of my app into some database or that will upgrade an existing version 1 installation to version 2. It's true that if someone has taken the version-1 schema and made manual modifications to it, then things might blow up. But, I can tell people that they shouldn't do that, or the upgrade script might break. If they do and it does then they get to keep both pieces. Even if I do the whole thing in PL/pgsql, I'm still not going to check for every stupid thing someone might have done to break the schema... I think the cat is already out of the bag on this one, and it's just a matter of whether we're willing to provide some convenient syntax or leave people to hand-code it. But actually I thought we had more or less concluded that CREATE OR REPLACE LANGUAGE would be acceptable (perhaps only if it's given without any extra args?). I'm not sure there's any value in that restriction - seems more confusing than helpful. Or for that matter there seems to be enough opinion on the side of just installing plpgsql by default. CINE is a markedly inferior alternative to either of those. For languages, yes. ...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] [GENERAL] Updating column on row update
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: The argument against CINE is that it's unsafe. By no means rhetorically, is that based on the assumption that the statement would not validate that the existing object (if any) matches the supplied definition? If it did so, that would be outside the apparent meaning of the command, which is to do nothing if an object of that name exists. That's why we've gone with CREATE OR REPLACE instead. The fragment proposed by Andrew is no safer, of course, but it could be made safe by adding additional checks that the properties of the existing object are what the script expects. Again, not rhetorically, is that assuming an error-free mapping of the CREATE statement to all the related system tables -- each time it is written by every user, individually? Yes, I'd expect the user to custom-code it, because it's not clear exactly which properties the script would be depending on and which ones it's okay to allow to vary. To take just one example, is it okay if the object ownership is different from current user? That might be fine, or it might be catastrophic (suppose the script is going to issue GRANT commands that presuppose particular ownership; if it's different you could be left with security holes). Only with the most simplistic implementation of CINE. I really don't see how that assertion holds up if there is checking of the supplied definition against the existing object. Even the most simplistic definition is arguably safer than CREATE OR REPLACE, since that can destroy existing data. How exactly would it do that? You seem to be postulating non-obvious or not-as-currently-implemented semantics for both variants of the command, so you had better explain exactly what you think they'd be. (I agree that CREATE OR REPLACE on a table might be expected to destroy existing data, but we don't have such a command and there is no proposal to make one.) 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] [GENERAL] Updating column on row update
Robert Haas robertmh...@gmail.com writes: On Tue, Nov 24, 2009 at 12:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: But actually I thought we had more or less concluded that CREATE OR REPLACE LANGUAGE would be acceptable (perhaps only if it's given without any extra args?). I'm not sure there's any value in that restriction - seems more confusing than helpful. The point would be to reduce the risk that you're changing the language definition in a surprising way. Extra args would imply that you're trying to install a non-default definition of the language. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
On Tue, Nov 24, 2009 at 11:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Tue, Nov 24, 2009 at 12:28 PM, Tom Lane t...@sss.pgh.pa.us wrote: But actually I thought we had more or less concluded that CREATE OR REPLACE LANGUAGE would be acceptable (perhaps only if it's given without any extra args?). I'm not sure there's any value in that restriction - seems more confusing than helpful. The point would be to reduce the risk that you're changing the language definition in a surprising way. Extra args would imply that you're trying to install a non-default definition of the language. But if you'd installed it that way before, wouldn't you then need the arguments this time to have them match? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
Tom Lane t...@sss.pgh.pa.us wrote: If it did so, that would be outside the apparent meaning of the command, which is to do nothing if an object of that name exists. That's why we've gone with CREATE OR REPLACE instead. I think that fail on existence of an object conflicting with given definition is behavior which could be documented and rates fairly low on my astonishment scale. (I can't speak for anyone else.) I am skeptical that, in the absence of built-in support for checking the existing object against the supplied definition, people would generally go any further than Andrew's example. When they did, I'm skeptical about how often they would get the details exactly right. Yes, I'd expect the user to custom-code it, because it's not clear exactly which properties the script would be depending on and which ones it's okay to allow to vary. To take just one example, is it okay if the object ownership is different from current user? That might be fine, or it might be catastrophic (suppose the script is going to issue GRANT commands that presuppose particular ownership; if it's different you could be left with security holes). Yeah, that's an area which I figured would require some discussion. The best behavior isn't immediately clear to me in that regard. I didn't figure that arriving at some decision on that was necessarily an insurmountable obstacle. Similar issue with indexes, although the answer there seems clearer (at least to me). (I agree that CREATE OR REPLACE on a table might be expected to destroy existing data, but we don't have such a command and there is no proposal to make one.) There was, up-thread, discussion by multiple people of the desire to have CINE for tables. Andrew's example was specifically about an alternative way of spelling that. This branch of the thread has been all about exactly that. (Well, at least in my head.) You asserted that CREATE OR REPLACE was superior to CINE; I took it to be in response to the discussion of CINE for tables, but I guess it was just in the scope of languages. Sorry for misinterpreting. -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] [GENERAL] Updating column on row update
Scott Marlowe scott.marl...@gmail.com writes: On Tue, Nov 24, 2009 at 11:34 AM, Tom Lane t...@sss.pgh.pa.us wrote: The point would be to reduce the risk that you're changing the language definition in a surprising way. Extra args would imply that you're trying to install a non-default definition of the language. But if you'd installed it that way before, wouldn't you then need the arguments this time to have them match? If you knew you'd installed it that way before, you wouldn't be executing this command at all. The use-case for commands like this IMO is scripts that don't know exactly what the database state is. The use-case for a script that is installing non-default language parameters into somebody else's database seems pretty darn thin. I'm not dead set on this by any means. But it seems like it would help reduce the risk of bad consequences from CREATE OR REPLACE LANGUAGE. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
Kevin Grittner kevin.gritt...@wicourts.gov writes: Tom Lane t...@sss.pgh.pa.us wrote: Yes, I'd expect the user to custom-code it, because it's not clear exactly which properties the script would be depending on and which ones it's okay to allow to vary. To take just one example, is it okay if the object ownership is different from current user? Yeah, that's an area which I figured would require some discussion. The best behavior isn't immediately clear to me in that regard. I didn't figure that arriving at some decision on that was necessarily an insurmountable obstacle. The reason a script-driven solution seems attractive is exactly that there doesn't seem to be a good one-size-fits-all behavior for complex objects. There was, up-thread, discussion by multiple people of the desire to have CINE for tables. Andrew's example was specifically about an alternative way of spelling that. This branch of the thread has been all about exactly that. (Well, at least in my head.) I thought the thread was about CREATE LANGUAGE. If you want to discuss CINE in general it would probably be appropriate to start a different thread about 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] [GENERAL] Updating column on row update
On Tue, Nov 24, 2009 at 2:07 PM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: If it did so, that would be outside the apparent meaning of the command, which is to do nothing if an object of that name exists. That's why we've gone with CREATE OR REPLACE instead. I think that fail on existence of an object conflicting with given definition is behavior which could be documented and rates fairly low on my astonishment scale. (I can't speak for anyone else.) I think CINE should create the object if it does not exist and otherwise do nothing. It might be useful to have some kind of consistency-checking behavior, but it would probably be more useful if decoupled from CINE, and in any case, that's not what CREATE IF NOT EXISTS means to me. I am skeptical that, in the absence of built-in support for checking the existing object against the supplied definition, people would generally go any further than Andrew's example. When they did, I'm skeptical about how often they would get the details exactly right. Bingo. ...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] [GENERAL] Updating column on row update
2009/11/23 Tom Lane t...@sss.pgh.pa.us CREATE OR REPLACE has got far safer semantics from the viewpoint of a script that wants to bull through without having any actual error handling (which is more or less the scenario we are arguing here, no?) After successful execution of the command you know exactly what properties the object has got. Whether it would be sensible to have CREATE OR REPLACE semantics for a language is something I'm not very clear on. It seems like changing any of the properties of a pg_language entry could be rather fatal from the viewpoint of an existing function using the language. [ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among creation commands in that the common cases have no parameters, at least not since we added pg_pltemplate. So you could imagine defining CINE for a language as disallowing any parameters and having these semantics: * language not present - create from template * language present, matches template - OK, do nothing * language present, does not match template - report error This would meet the objection of not being sure what the state is after successful execution of the command. It doesn't scale to any other object type, but is it worth doing for this one type? regards, tom lane Actually, I prefer CREATE OR REPLACE over CINE, at least for the majority of the creations, especially since it would be more consistent with what we have for functions. If there must be an exception for languages, it would make sense from what you describe above. As for having plpgsql installed by default, are there any security implications? If not, I can only see it as an advantage. At the moment we're having to resort to a bit of a hack using a CASE statement in a plain SQL function as mentioned earlier in this thread. Thom
Re: [HACKERS] [GENERAL] Updating column on row update
On Sun, Nov 22, 2009 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Robert Haas robertmh...@gmail.com writes: On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: CREATE IF NOT EXISTS has been proposed and rejected before, more than once. Please see the archives. Search for CINE to find the discussions. This is a good place to start: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00252.php Despite Tom's assertions to the contrary, I am unable to find a clear consensus against this feature in the archives, I think you didn't look back far enough --- that issue was settled years ago. IIRC the killer argument is that after CINE you do not know the state of the object: it exists, yes, but what properties has it got? If it already existed then it's still got its old definition, which might or might not be what you're expecting. CREATE OR REPLACE has got far safer semantics from the viewpoint of a script that wants to bull through without having any actual error handling (which is more or less the scenario we are arguing here, no?) After successful execution of the command you know exactly what properties the object has got. Sure. I think that CINE only makes sense for objects for which COR can't be implemented - things that have internal substructure, like tables or tablespaces. I agree that there are pitfalls for the unwary but I still think it's better than nothing. I understand that you disagree. Whether it would be sensible to have CREATE OR REPLACE semantics for a language is something I'm not very clear on. It seems like changing any of the properties of a pg_language entry could be rather fatal from the viewpoint of an existing function using the language. [ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among creation commands in that the common cases have no parameters, at least not since we added pg_pltemplate. So you could imagine defining CINE for a language as disallowing any parameters and having these semantics: * language not present - create from template * language present, matches template - OK, do nothing * language present, does not match template - report error This would meet the objection of not being sure what the state is after successful execution of the command. It doesn't scale to any other object type, but is it worth doing for this one type? CREATE OR REPLACE seems like a better fit in this case. For example, it seems plausible that someone might want to add an inline handler to a procedural language that didn't have one without dropping and recreating the language. Even changing the call handler seems like it could be potentially useful in an upgrade scenario. ...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] [GENERAL] Updating column on row update
Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Well, that's pretty much exactly the question --- are there? It would certainly make it easier for someone to exploit any other security weakness they might find. I believe plain SQL plus SQL functions is Turing-complete, but that doesn't mean it's easy or fast to write loops etc in 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] [GENERAL] Updating column on row update
2009/11/23 Tom Lane t...@sss.pgh.pa.us Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Well, that's pretty much exactly the question --- are there? It would certainly make it easier for someone to exploit any other security weakness they might find. I believe plain SQL plus SQL functions is Turing-complete, but that doesn't mean it's easy or fast to write loops etc in it. regards, tom lane I personally find it more important to gracefully add plpgsql if it doesn't already exist than to rely on it already being there. In a way it wouldn't solve this problem as someone could have still removed it. Other procedural languages could benefit from some sort of check too. Thom
Re: [HACKERS] [GENERAL] Updating column on row update
Tom == Tom Lane t...@sss.pgh.pa.us writes: Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Tom Well, that's pretty much exactly the question --- are there? It Tom would certainly make it easier for someone to exploit any other Tom security weakness they might find. I believe plain SQL plus SQL Tom functions is Turing-complete, but that doesn't mean it's easy or Tom fast to write loops etc in it. Now that we have recursive CTEs, plain SQL is turing-complete without requiring functions. (Yes, I did actually prove this a while back, by implementing one of the known-Turing-complete tag system automata as a single recursive query. This proof is pretty boring, though, because you wouldn't actually _use_ that approach in practice.) Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. -- 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] [GENERAL] Updating column on row update
Tom Lane wrote: Thom Brown thombr...@gmail.com writes: As for having plpgsql installed by default, are there any security implications? Well, that's pretty much exactly the question --- are there? It would certainly make it easier for someone to exploit any other security weakness they might find. I believe plain SQL plus SQL functions is Turing-complete, but that doesn't mean it's easy or fast to write loops etc in it. That's a bit harder argument to sustain now we have recursive queries, ISTM. 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] [GENERAL] Updating column on row update
Andrew Gierth and...@tao11.riddles.org.uk writes: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Well, that's pretty much exactly the question --- are there? It Tom would certainly make it easier for someone to exploit any other Tom security weakness they might find. Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. The fact remains though that the looping performance of anything you can cons up in straight SQL will be an order of magnitude worse than in plpgsql; and it's a notation the average script kiddie will find pretty unfamiliar. So I think this still does represent some barrier. Whether it's enough of a barrier to justify keeping plpgsql out of the default install is certainly debatable. 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] [GENERAL] Updating column on row update
Tom == Tom Lane t...@sss.pgh.pa.us writes: Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. Tom The fact remains though that the looping performance of anything Tom you can cons up in straight SQL will be an order of magnitude Tom worse than in plpgsql; Well, let's see. How about generating all possible strings of 6 characters from A-Z? We'll just count the results for now: select count(chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26) ||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26)) from generate_series(0,17575) i, generate_series(0,17575) j; count --- 308915776 (1 row) Time: 462570.563 ms create function foo() returns bigint language plpgsql as $f$ declare c bigint := 0; s text; begin for i in 0..17575 loop for j in 0..17575 loop s := chr(65+(i/676))||chr(65+(i/26)%26)||chr(65+i%26) ||chr(65+(j/676))||chr(65+(j/26)%26)||chr(65+j%26); c := c + 1; end loop; end loop; return c; end; $f$; select foo(); foo --- 308915776 (1 row) Time: 624809.671 ms plpgsql comes out 35% _slower_, not an order of magnitude worse. -- 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] [GENERAL] Updating column on row update
On 23/11/2009 11:35 PM, Tom Lane wrote: Andrew Gierth and...@tao11.riddles.org.uk writes: Tom == Tom Lane t...@sss.pgh.pa.us writes: Tom Well, that's pretty much exactly the question --- are there? It Tom would certainly make it easier for someone to exploit any other Tom security weakness they might find. Loops in plain SQL are no problem: see generate_series. The last time we discussed this I demonstrated reasonably straightforward SQL examples of how to do things like password-cracking (and that was long before we had CTEs, so it would be even easier now); my challenge to anyone to produce examples of malicious plpgsql code that couldn't be reproduced in plain SQL went unanswered. The fact remains though that the looping performance of anything you can cons up in straight SQL will be an order of magnitude worse than in plpgsql; and it's a notation the average script kiddie will find pretty unfamiliar. So I think this still does represent some barrier. Whether it's enough of a barrier to justify keeping plpgsql out of the default install is certainly debatable. the average script kiddie doesn't write their own exploits. They tend to use proofs of concept created by very, very smart people involved in active security research (be it malicious or not) that've been wrapped up in easy-to-use click to exploit tools. They'll no more learn SQL to explot Pg than they learn x86 asm to write their payload injectors, or learn details about the Linux kernel to use a local root exploit. Just making it a bit harder doesn't stop determined attackers, such as security researchers, criminals seeking confidential information (credit card databases etc) or commercially-motivated black hats seeking to build botnets. Once the determined attackers find a way, the script kiddies and the botnet builders tend to follow. Any attack relying on the presence of PL/PgSQL will have to be an attack by an already-authenticated user* with an established connection. Mass botnet- or worm- style exploits are out. That said, PL/PgSQL does undeniably increase the available attack surface for a rogue authorized user, simply by being more code that has to be free of security issues. An authenticated user might seek to escalate to DB superuser priveleges, gain access to other DBs, or execute code as the postgres user to trigger a local root exploit on the hosting machine. So there is some concern, since not all authenticated users are necessarily fully trusted. It's for that reason that I proposed it being made available by default only to superusers and to the owner of the current database. If either of those are executing malicious code, you're already well and truly screwed. Thinking about it some more, though, there's nothing that'll let the DB owner (unlike the superuser) execute supplied code as the postgres user or break into other DBs, so an exploit against PL/PgSQL might still give them a way to escalate priveleges. I don't see making PL/PgSQL available by default only to a superuser as particularly useful. Anything else does increase the attack surface available for potential exploit. So the question becomes is that increase a sufficient worry to block the installation of PL/PgSQL by default ? Personally, I think not. Default installing PL/PgSQL doesn't increase the risk of a worm, which would be my main worry about enabling a feature by default. PL/PgSQL is so widely used that any security issue with it is already about as critical as it can be. Those few with significant databases who do NOT use it can drop it if they are concerned, but I sincerely doubt there are many significant production DBs out there without it installed and in use, as it's effectively a requirement for the use of triggers. So - I say go ahead and install it by default, available to all users. It's meant to be a trusted language, it's basically required for triggers, it's nearly universal anyway, and it's a pain not having it installed. If it's not to be installed by default, then a cleaner way to ensure it's installed it would be strongly desirable. (I'm also honestly not sure what relevance performance has here. If it takes an attacker 10 minutes to exploit a server rather than 1 minute, is it any less cracked? Performance is only an issue if it renders an attack impossible due to memory/storage requirements, or non-linear computation time growth. Anyway, I frequently seek to avoid PL/PgSQL, using pure SQL loops etc instead, because it's *faster* that way.) * It could, I guess, be a hijacked connection, but if you have connection hijacking going on you've already lost and have bigger things to worry about. Otherwise it's going to be a stolen username/password, or an authorized user gone rogue. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
On 23/11/2009 4:15 AM, Scott Marlowe wrote: On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown thombr...@gmail.com wrote: Hi, This should be simple, but for some reason I'm not quite sure what the solution is. I want to be able to update the value of a column for rows that have been updated. More specifically, if a row is updated, I want it's modified_date column to be populated with the current time stamp. I've looked at triggers and rules, and it looks like I'd need to create a function just to achieve this which seems incredibly clumsy and unnecessary. Could someone enlighten me? Well, you DO have to create a function, but it's not all that clumsy really. Also it's quite flexible so you can do lots of complex stuff and hide it away in a trigger function. I do think this comes up often enough that a built-in trigger update named column with result of expression on insert trigger might be desirable. Especially if implemented in C to avoid the need for PL/PgSQL and to reduce the CPU cost a smidge. Hmm. CC'iing -hackers; there was a discussion earlier on it being desirable to have more [EASY] TODO items, and this might be a good one for the job. So might CREATE LANGUAGE ... IF NOT EXISTS. Maybe even CREATE ROLE ... IF NOT EXISTS and CREATE USER ... IF NOT EXISTS - I know I'd find them really handy. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
2009/11/22 Craig Ringer cr...@postnewspapers.com.au On 23/11/2009 4:15 AM, Scott Marlowe wrote: On Sun, Nov 22, 2009 at 12:50 PM, Thom Brown thombr...@gmail.com wrote: Hi, This should be simple, but for some reason I'm not quite sure what the solution is. I want to be able to update the value of a column for rows that have been updated. More specifically, if a row is updated, I want it's modified_date column to be populated with the current time stamp. I've looked at triggers and rules, and it looks like I'd need to create a function just to achieve this which seems incredibly clumsy and unnecessary. Could someone enlighten me? Well, you DO have to create a function, but it's not all that clumsy really. Also it's quite flexible so you can do lots of complex stuff and hide it away in a trigger function. I do think this comes up often enough that a built-in trigger update named column with result of expression on insert trigger might be desirable. Especially if implemented in C to avoid the need for PL/PgSQL and to reduce the CPU cost a smidge. Hmm. CC'iing -hackers; there was a discussion earlier on it being desirable to have more [EASY] TODO items, and this might be a good one for the job. So might CREATE LANGUAGE ... IF NOT EXISTS. Maybe even CREATE ROLE ... IF NOT EXISTS and CREATE USER ... IF NOT EXISTS - I know I'd find them really handy. -- Craig Ringer I would have thought the IF NOT EXISTS syntax could be handy on every CREATE command and I wouldn't object to such a thing being implemented in future. But my reason for the column updating on row update was due to me converting a MySQL script to PostgreSQL. MySQL had the following syntax available: `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP That's effectively what I'm emulating. I think that syntax is actually quite useful. Another thing I found useful was having COMMENT available on the same line as the column declaration too. An example of this is: `parent_id` integer unsigned NOT NULL default '0' COMMENT 'The parent menu item in the menu tree.', I really can't think of any other syntactic sugar I'd want from MySQL though. Thom
Re: [HACKERS] [GENERAL] Updating column on row update
Craig Ringer cr...@postnewspapers.com.au writes: I do think this comes up often enough that a built-in trigger update named column with result of expression on insert trigger might be desirable. There's something of the sort in contrib already, I believe, though it's so old it still uses abstime :-( So might CREATE LANGUAGE ... IF NOT EXISTS. Maybe even CREATE ROLE ... IF NOT EXISTS and CREATE USER ... IF NOT EXISTS - I know I'd find them really handy. CREATE IF NOT EXISTS has been proposed and rejected before, more than once. Please see the archives. 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] [GENERAL] Updating column on row update
MySQL had the following syntax available: `updated_date` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP I wonder supporting this syntax would speed things up a little bit. Here's a simple benchmark about the situation we are discussing here: There are 2 tables: CREATE TABLE t1 (n integer not null, mtime timestamp with time zone not null); CREATE TABLE t2 (n integer not null, mtime timestamp with time zone not null); and a trigger for the second one: CREATE LANGUAGE plpgsql; CREATE FUNCTION touch() RETURNS trigger AS $$ BEGIN new.mtime := now(); RETURN new; END; $$ LANGUAGE 'plpgsql'; CREATE TRIGGER ttt_mtime BEFORE UPDATE or INSERT ON t2 FOR EACH ROW EXECUTE PROCEDURE touch(); and here's the actual test: test= INSERT INTO t1(n,mtime) SELECT *, now() FROM generate_series(1,100); INSERT 0 100 Time: 7382.313 ms test= INSERT INTO t2(n) SELECT * FROM generate_series(1,100); INSERT 0 100 Time: 24541.088 ms So, updating the column explicitly is 3.5 times faster than the trigger. My guess is that in real life applications where tables have bigger rows (more columns, data types other than integer), the overhead of the trigger will be even smaller. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: Craig Ringer cr...@postnewspapers.com.au writes: I do think this comes up often enough that a built-in trigger update named column with result of expression on insert trigger might be desirable. There's something of the sort in contrib already, I believe, though it's so old it still uses abstime :-( So might CREATE LANGUAGE ... IF NOT EXISTS. Maybe even CREATE ROLE ... IF NOT EXISTS and CREATE USER ... IF NOT EXISTS - I know I'd find them really handy. CREATE IF NOT EXISTS has been proposed and rejected before, more than once. Please see the archives. Search for CINE to find the discussions. This is a good place to start: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00252.php Despite Tom's assertions to the contrary, I am unable to find a clear consensus against this feature in the archives, and still think it would be useful. MySQL and SQLite both support it, at least in the specific case of CREATE TABLE IF NOT EXISTS. But I've exhausted my quota of beating my head against a brick wall on this issue. ...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] [GENERAL] Updating column on row update
Robert Haas robertmh...@gmail.com writes: On Sun, Nov 22, 2009 at 6:51 PM, Tom Lane t...@sss.pgh.pa.us wrote: CREATE IF NOT EXISTS has been proposed and rejected before, more than once. Please see the archives. Search for CINE to find the discussions. This is a good place to start: http://archives.postgresql.org/pgsql-hackers/2009-05/msg00252.php Despite Tom's assertions to the contrary, I am unable to find a clear consensus against this feature in the archives, I think you didn't look back far enough --- that issue was settled years ago. IIRC the killer argument is that after CINE you do not know the state of the object: it exists, yes, but what properties has it got? If it already existed then it's still got its old definition, which might or might not be what you're expecting. CREATE OR REPLACE has got far safer semantics from the viewpoint of a script that wants to bull through without having any actual error handling (which is more or less the scenario we are arguing here, no?) After successful execution of the command you know exactly what properties the object has got. Whether it would be sensible to have CREATE OR REPLACE semantics for a language is something I'm not very clear on. It seems like changing any of the properties of a pg_language entry could be rather fatal from the viewpoint of an existing function using the language. [ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among creation commands in that the common cases have no parameters, at least not since we added pg_pltemplate. So you could imagine defining CINE for a language as disallowing any parameters and having these semantics: * language not present - create from template * language present, matches template - OK, do nothing * language present, does not match template - report error This would meet the objection of not being sure what the state is after successful execution of the command. It doesn't scale to any other object type, but is it worth doing for this one type? 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] [GENERAL] Updating column on row update
Tom Lane wrote: [ thinks for awhile... ] Actually, CREATE LANGUAGE is unique among creation commands in that the common cases have no parameters, at least not since we added pg_pltemplate. So you could imagine defining CINE for a language as disallowing any parameters and having these semantics: * language not present - create from template * language present, matches template - OK, do nothing * language present, does not match template - report error This would meet the objection of not being sure what the state is after successful execution of the command. It doesn't scale to any other object type, but is it worth doing for this one type? I seriously doubt it. The only reason I could see for such a thing would be to make it orthogonal with other CINE commands. Part of the motivation for allowing inline blocks was to allow for conditional logic. So you can do things like: DO $$ begin if not exists (select 1 from pg_tables where schemaname = 'foo' and tablename = 'bar') then create table foo.bar (x int, y text); end if; end; $$; It's a bit more verbose (maybe someone can streamline it) but it does give you CINE (for whatever flavor of CINE you want), as well as lots more complex possibilities than we can conceivably build into SQL. 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] [GENERAL] Updating column on row update
Andrew Dunstan and...@dunslane.net writes: Part of the motivation for allowing inline blocks was to allow for conditional logic. I don't think that argument really applies to this case, because the complaint was about not being sure if plpgsql is installed. If it isn't, you can hardly use a plpgsql DO block to fix it. (Is anyone up for revisiting the perennial topic of whether to install plpgsql by default? Andrew's argument does suggest that DO might offer a new consideration in that tradeoff.) 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] [GENERAL] Updating column on row update
On Sun, Nov 22, 2009 at 10:19 PM, Tom Lane t...@sss.pgh.pa.us wrote: Andrew Dunstan and...@dunslane.net writes: Part of the motivation for allowing inline blocks was to allow for conditional logic. I don't think that argument really applies to this case, because the complaint was about not being sure if plpgsql is installed. If it isn't, you can hardly use a plpgsql DO block to fix it. (Is anyone up for revisiting the perennial topic of whether to install plpgsql by default? Andrew's argument does suggest that DO might offer a new consideration in that tradeoff.) One non-coding vote for yes. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Part of the motivation for allowing inline blocks was to allow for conditional logic. I don't think that argument really applies to this case, because the complaint was about not being sure if plpgsql is installed. If it isn't, you can hardly use a plpgsql DO block to fix it. (Is anyone up for revisiting the perennial topic of whether to install plpgsql by default? Andrew's argument does suggest that DO might offer a new consideration in that tradeoff.) It'd be a HUGE benefit in deployment and update scripts to have PL/PgSQL installed and available by default, at least to the superuser and to the DB owner. One issue I run into with DB deployment is that a schema often requires several roles. If the schema has been imported into another (possibly since-dropped) database in the cluster before, global changes such as role creations will fail since they've already been done by a prior run. This makes it necessary to split the schema into global and database-specific parts or to ignore errors that arise as the SQL is processed. Neither option lets me reasonably apply a schema update transactionally. Having PL/PgSQL available right from an initial connection to `template1' as superuser for a 'CREATE DATABASE' would be great, as I could use appropriate logic to avoid or handle errors, and could run schema create/update scripts with ON_ERROR_ROLLBACK . If CREATE LANGUAGE silently succeeded if the language was already installed with the same params, perhaps PL/PgSQL could be made available by default with no impact on existing scripts and apps? Is there any harm in making it succeed if it need take no action to achieve the requested state? After all, the end result is as the user requested. Do they really care whether CREATE LANGUAGE had to modify the catalogs? As for CREATE [USER|ROLE] ... IF NOT EXISTS; I was concerned about just that issue, which is why I was unsure whether it was sane for users and roles. Being able to easily test for the presence of a user (say, within a DO block with default-installed PL/PgSQL) would be nicer and safer than having ... IF EXISTS for users/roles. -- Craig Ringer -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [GENERAL] Updating column on row update
On Sun, Nov 22, 2009 at 10:41 PM, Craig Ringer cr...@postnewspapers.com.au wrote: Tom Lane wrote: It'd be a HUGE benefit in deployment and update scripts to have PL/PgSQL installed and available by default, at least to the superuser and to the DB owner. Are there any known security problems with plpgsql? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
