Re: [HACKERS] PostgreSQL::PLPerl::Call - Simple interface for calling SQL functions from PostgreSQL PL/Perl
On Feb 12, 2010, at 3:10 PM, Tim Bunce wrote: I've appended the POD documentation and attached the (rough but working) test script. I plan to release the module to CPAN in the next week or so. I'd greatly appreciate any feedback. I like the idea overall, and anything that can simplify the interface is more than welcome. However: * I'd rather not have to specify a signature for a non-polymorphic function. * I'd like to be able to use Perl code to call the functions as discussed previously, something like: my $count_sql = SP-tl_activity_stats_sql( [ statistic = $stat, person_id = $pid ], $debug ); For a Polymorphic function, perhaps it could be something like: my $count = SP-call( tl_activity_stats_sql = [qw(text[] int)], [ statistic = $stat, person_id = $pid ], $debug ); The advantage here is that I'm not writing functions inside strings, and only provide the signature when I need to disambiguate between polymorphic variants. Anyway, That's just interface arguing. The overall idea is sound and very much appreciated. 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] Package namespace and Safe init cleanup for plperl [PATCH]
On Feb 13, 2010, at 6:32 AM, Andrew Dunstan wrote: My feeling is if we provide something we are responsible for it, documented or not. Undocumented features with security implications raise big red flags in my head. Maybe the difference in perspective comes from working on a database as opposed to working on a language. I'm confused. Doesn't on_plperl_init already give us this? Isn't any of the stuff loaded by that GUC then available from inside the PLPerl Safe compartment? 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] Package namespace and Safe init cleanup for plperl [PATCH]
On Feb 13, 2010, at 2:46 PM, Andrew Dunstan wrote: I'm confused. Doesn't on_plperl_init already give us this? Isn't any of the stuff loaded by that GUC then available from inside the PLPerl Safe compartment? No (and if it does it's a bug). Try it and see. Then what's the point of it? 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] Package namespace and Safe init cleanup for plperl [PATCH]
On Feb 13, 2010, at 3:35 PM, Andrew Dunstan wrote: To perform initialisation, such as setting a value in %_SHARED. Hrm. Well, as a DBA, I'd *really* like to be able to make some things available from within a Safe container, such as Devel::NYTProf::PgPLPerl or the PostgreSQL::PLPerl::Call module that Tim's working on. Right now I can do that by hacking warnings.pm directly or by the method you figured out a few weeks ago, which isn't really all that nasty. I'm not sure that Tim's interface is the best approach to giving DBAs the ability to do this from within PostgreSQL, but I'm hard-pressed to come up with a better interface. But I do think it should be allowed. 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] Pathological regexp match
On Feb 8, 2010, at 5:15 AM, Magnus Hagander wrote: The text is about 180Kb. PostgreSQL takes ~40 seconds without the patch, ~36 seconds with it, to extract the match from it. Perl takes 0.016 seconds. Obviously we need to support Perl regular expressions in core. Not PCRE, but Perl. ;-P 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] damage control mode
On Feb 8, 2010, at 9:34 AM, Josh Berkus wrote: Eh? Previously we allowed code to go in with documentation to be written after feature freeze. Is this no longer acceptable? My $0.0201115: I think you need to use a NUMERIC type there, as some calculation has lost precision in the float. 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] Hot standby documentation
On Feb 7, 2010, at 12:35 PM, Josh Berkus wrote: I've always thought this feature was misnamed and nothing has happened to change my mind, but it's not clear whether I'm in the majority. I'm afraid force of habit is more powerful than correctness on this one. It's going to be HS/SR whether that's perfectly correct or not. What would be correct? I thought HS/SR were pretty correct (as long as no one confuses SR with synchronous replication!). 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] PG 9.0 and standard_conforming_strings
On Feb 3, 2010, at 6:16 PM, Robert Haas wrote: Any web framework that interpolates user supplied values into SQL rather than using placeholders is broken from the get go, IMNSHO. I'm not saying that there aren't reasons to hold up moving to SCS, but this isn't one of them. That seems more than slightly harsh. I've certainly come across situations where interpolating values (with proper quoting of course) made more sense than using placeholders. YMMV, of course. Not if it leads to Little Bobby Tables's door when, you know, you use SQL conformant strings! Sounds like an app that needs its quoting function fixed. 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] Add on_trusted_init and on_untrusted_init to plperl UPDATED [PATCH]
On Feb 3, 2010, at 9:21 AM, Alex Hunsaker wrote: plperl.on_init - run on interpreter creation plperl.on_plperl_init - run when then specialized for plperl plperl.on_plperlu_init - run when then specialized for plperlu Hrm, I think I agree with Tom that we should not have a global on_init. And instead of two separate GUCs (we still end up with 3 gucs total). Im still thinking through it... I completely agree on using plperl and plperlu instead of trusted and untrusted in the GUC names. The latter are just too confusing (even Tom mixed them up in a post last week). They are among the worst names in the system, IMHO. 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] Add on_trusted_init and on_untrusted_init to plperl UPDATED [PATCH]
On Feb 3, 2010, at 11:04 AM, Tom Lane wrote: What I was actually wondering about, however, is the extent to which the semantics of Perl code could be changed from an on_init hook --- is there any equivalent of changing search_path or otherwise creating trojan-horse code that might be executed unexpectedly? Yes. And if so is there any point in trying to guard against it? No. This is Perl we're talking about. The DBA should vet code before putting it into on_perl_init. AIUI there isn't anything that can be done in on_init that couldn't be done in somebody else's function anyhow. Correct. 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] plpython3
On Feb 1, 2010, at 10:53 AM, Tom Lane wrote: The first thought that comes to mind is plpythonng, following a tradition established by the tcl client rewrite among others ... but that double n doesn't read very well. And without it, you have a thong. Who's going to wear that? 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] Review: listagg aggregate
On Jan 29, 2010, at 10:43 AM, Robert Haas wrote: I haven't even looked at this code - I sort of assumed Itagaki was handling this one. But it might be good to make sure that the docs have been read through by a native English speaker prior to commit... I did and revised them slightly. There isn't much, just a brief comment in the table of aggregate functions. The documentation for all the functions on that page could use a little love, frankly. 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] Review: listagg aggregate
On Jan 29, 2010, at 10:46 AM, Robert Haas wrote: I did and revised them slightly. There isn't much, just a brief comment in the table of aggregate functions. The documentation for all the functions on that page could use a little love, frankly. Want to take a short at it? ENOTUITS! /me is already sorely over-committed… 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] PG 9.0 and standard_conforming_strings
On Jan 29, 2010, at 11:51 AM, Bruce Momjian wrote: With the release of Postgres 9.0, should we consider changing the default for 'standard_conforming_strings'? +1 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] Review: listagg aggregate
On Jan 28, 2010, at 9:29 AM, Marko Tiikkaja wrote: Someone might have a perfectly good use case for using different delimiters. I don't think it's a good idea to be artificially limiting what you can and can't do. +1 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] Add on_trusted_init and on_untrusted_init to plperl [PATCH]
On Jan 28, 2010, at 12:01 PM, Tim Bunce wrote: Once the previous patch lands I'll post an update to this patch with those changes applied. Ds the Add on_perl_init and proper destruction to plperl patch the one you're waiting on, then? Best, David, who loses track of these things -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 9:08 AM, Tom Lane wrote: This is exactly the claim that I have zero confidence in. Quite frankly, the problem with Perl as an extension language is that Perl was never designed to be a subsystem: it feels free to mess around with the entire state of the process. We've been burnt multiple times by that even with the limited use we make of Perl now, and these proposed additions are going to make it a lot worse IMO. Can you provide an example? Such concerns are impossible to address without concrete examples. 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 10:08 AM, Tom Lane wrote: Two examples that I can find in a quick review of our CVS history: perl stomping on the process's setlocale state, and perl stomping on the stdio state (Windows only). Are there links to those commits? Thanks, 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 1:27 PM, Tim Bunce wrote: Okay. I could change the callback code to ignore calls if proc_exit_inprogress is false. So an abnormal shutdown via exit() wouldn't involve plperl at all. (Alternatively I could use use on_proc_exit() instead of atexit() to register the callback.) Given Tom's hesitace about atexit(), perhaps that would be best. Neither of those relate to the actions of perl source code. To address that, instead of calling perl_destruct() to perform a complete destruction I could just execute END blocks and object destructors. That would avoid executing any system-level actions. Does perl_destruct() execute system-level actions, then? If so, then it seems prudent to either audit such actions or, as you say, call destructors directly. Do you have any examples of how a user could write code in a plperl END block that would interact with the rest of the backend? I appreciate you taking the time to look at ways to address the issues Tom has raised, Tim. Good on you. There is so much benefit to this level of interaction, as shown by the success of mod_perl and other forking environments that support pre-loading code, that I think it'd be extremely valuable to get these features in 9.0. They really allow Perl to be a first-class PL in a way that it wasn't before. So if there is no way other than SPI for Perl code to interact with the backend, and system-level actions in Perl itself are disabled, it seems to me that the major issues are addressed. Am I wrong, Tom? 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 1:53 PM, Robert Haas wrote: What exactly do we mean by system-level actions? I mean, END blocks can execute arbitrary code Yeah. In Perl. What part of Perl can access the backend systems without SPI? And that it couldn't do at any other point in runtime? 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] make everything target
On Jan 27, 2010, at 2:38 PM, Tom Lane wrote: These proposals sound reasonable to me too, but is everything an appropriate target name, or is there some other/better convention? Oooh, more bike-shedding. make theworld make toutlemonde make myday make lovenotwar 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 3:11 PM, Tom Lane wrote: You still aren't letting go of the notion that Perl could only affect the rest of the backend via SPI. The point I'm trying to impress on you is that there are any number of other possible pathways, and that Perl's historical assumption that it owns all resources of the process make those pathways a nontrivial hazard. Anything that Perl does to libc state, open file handles, etc etc carries a high risk of breaking the backend. As could any other code that executes then, including C libraries installed from pgFoundry and loaded by a DBA. Now it is certainly true that any such hazards can be created just from use of plperlu (we hope only plperlu, and not plperl ...) today, without any use of the proposed additional features. What is bothering me about these features is that their entire reason for existence is to encourage people to use parts of Perl that have time-extended effects on the process state. Well, mainly it's to avoid the overhead of loading the code except at startup. That means that (a) the probability of problems goes up substantially, Why? Arbitrary code can already execute at start time. Is Perl special somehow? and (b) our ability to fix such problems goes down substantially. Why is it your problem? Right now, the canonical approach to trying to undo anything bad Perl does is to save/restore process state around a plperl call. If we're trying to support usages in which Perl has time-extended effects on process state, that solution goes out the window, and we have to think of some other way to coexist with Perl. (Where, I note, coexist means Perl does what it damn pleases and we have to pick up the pieces --- we're not likely to get any cooperation on limiting damage from that side. Nobody even suggested that we treat stomping on setlocale state as a Perl bug, for example, rather than a fact of life that we just had to work around however we could.) How is that different from any other code that gets loaded when the server starts, exactly? Do, however, feel free to report Perl bugs. Just run `perlbug`. So the real bottom line here is that I foresee this patch as being destabilizing and requiring us to put large amounts of time into figuring out workarounds for whatever creative things people decide to try to do with Perl. I'd feel better about it if I thought that we could get away with a policy of if it breaks it's your problem, but I do not think that will fly from a PR standpoint. It hasn't in the past. mod_perl has for many years. Provide lots of caveats in the documentation. Point users to it when they write in about a problem. Truth is, the vast majority of Perl modules are pretty well-behaved. I sincerely doubt you'd hear much complaint. Have the Apache guys had to take any special steps to protect httpd from mod_perl? 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 3:33 PM, Tom Lane wrote: I don't have to read any further than the place where it says doesn't work if you call both plperl and plperlu to realize that that's quite false. Maybe we have different definitions of what a software interaction is... I think that dates from when plperl and plperlu couldn't co-exists, which was fixed a few months ago, n'est pas? 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 4:10 PM, Tom Lane wrote: Absolutely. The difference here is in who is going to be expected to try to deal with any problems. When somebody says if I do this in plperlu, my database crashes! Postgres sux! it's not going to help to say that's a Perl bug, even if an independent observer might agree. It's going to be *our* problem, and I don't see any reason to expect a shred of help from the Perl side. Is that not the case with plperlu already? 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 4:15 PM, Tom Lane wrote: Is that not the case with plperlu already? Sure. Which is why I'm resisting expanding our exposure to it I don't understand how it's expanding core's exposure to it. 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] Add on_perl_init and proper destruction to plperl [PATCH]
On Jan 27, 2010, at 4:33 PM, Tom Lane wrote: [ shrug...] I see little point in repeating myself yet again. It's obvious that the people who want this are entirely willing to adopt a Pollyanna-ishly optimistic view about its potential to cause serious problems that we may or may not be able to fix. Well, no. The problems you raise already exist in plperlu. And I would argue that they're worse there, as the DBA can give others permission to create PL/PerlU functions, and those users can do all kinds of crazy shit with them. on_perl_init can be executed the DBA only. It's scope is far less. This is *safe* than PL/PerlU, while given more capability to PL/Perl. I don't really expect to be able to prevent something along this line from getting committed --- I'm merely hoping to circumscribe it as much as possible and get large WARNING items into the manual's description. Oh, absolutely. Your sober attention to security issues is greatly appreciated by us fanboys. Best, David PS: I'm a PostgreSQL fanboy, not a Tom Lane fanboy. ;-P -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Review: listagg aggregate
On Jan 27, 2010, at 7:58 AM, Pavel Stehule wrote: with actualised oids Thanks. Looks good, modulo my preference for concat_agg(). I'll mark it ready for committer. 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] Review: listagg aggregate
On Jan 27, 2010, at 6:47 PM, Takahiro Itagaki wrote:
* I think we cannot cache the delimiter at the first call.
For example,
SELECT string_agg(elem, delim)
FROM (VALUES('A', ','), ('B', '+'), ('C', '*')) t(elem, delim);
should return 'A+B*C' rather than 'A,B,C'.
Ooh, nice.
* Can we use StringInfo directly as the aggregate context instead of
StringAggState? For the first reason, we need to drop 'delimiter' field
from struct StringAggState. Now it has only StringInfo field.
Makes sense.
* We'd better avoiding to call text_to_cstring() for delimitors and elements
for performance reason. We can use appendBinaryStringInfo() here.
My proposal patch attached.
Also, I've not changed it yet, but it might be considerable:
* Do we need better names for string_agg1_transfn and string_agg2_transfn?
They are almost internal names, but we could have more
like string_agg_with_sep_transfn.
Yes please.
Comments?
Patch looks great, thank you!
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] Review: listagg aggregate
On Jan 25, 2010, at 23:14, Pavel Stehule pavel.steh...@gmail.com wrote: why is concat_agg better than listagg ? Because it's an aggregate that cocatenates values. It's not an aggregate that lists things. I also like concat_agg better than string_agg because it's not limited to acting on strings. 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] Review: listagg aggregate
On Jan 26, 2010, at 4:03, Peter Eisentraut pete...@gmx.net wrote: What else can it act on? Any data type, since they all can be converted to text. Integers would be a common choice. 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] Review: listagg aggregate
On Jan 26, 2010, at 9:36 AM, Tom Lane wrote: But what it *produces* is a string. For comparison, the SQL-standard-specified array_agg produces arrays, but what it acts on isn't an array. Meh. This is all just bike-shedding. I'm fine with string_agg(), though in truth none of the names has really been great. The inclusion of agg in the name is unfortunate. I'll have a look at Pavel's new patch now. 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] Review: listagg aggregate
On Jan 25, 2010, at 6:56 AM, Pavel Stehule wrote: actualised patch - the name is string_agg All looks fine except I'm getting this error during initdb: creating template1 database in /usr/local/pgsql-devel/data/base/1 ... FATAL: could not create unique index pg_proc_oid_index DETAIL: Key (oid)=(3031) is duplicated. child process exited with exit code 1 Would you mind re-submitting with unique OIDs? Thanks, 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] Review: listagg aggregate
On Jan 25, 2010, at 2:09 AM, Peter Eisentraut wrote: xmlagg - concatenates values to form xml datum array_agg - concatenates values to form array datum ??? - concatenates values to form string datum concat_agg(). 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] Review: listagg aggregate
On Jan 25, 2010, at 6:12 AM, Pavel Stehule wrote: I am not happy, I thing so we do bigger chaos then it is. But it hasn't progress. So I agree with name string_agg. In this case isn't a problem rename this function if somebody would. Could you not use CREATE AGGREGATE to create an alias named listagg if you needed it? Or are we limited to a single argument in that case? 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] Review: listagg aggregate
On Jan 24, 2010, at 1:19 AM, Pavel Stehule wrote: can I help with it, please. My English is terrible. Yes, I added a bit in the patch I submitted. array user functions are used more time in pg core. The complexity of array functions are much higher, so I don't think we need special file. Okay. Should have tried it in PL/pgSQL then, perhaps. I'll recheck it. I am sure so all parameters should be a text. Probably shouldn't go into varchar.c then, yes? We can, but it isn't good way. Processing of arrays is little bit more expensive then processing plain text. It is reason why listagg is faster, than your custom aggregate. More, the final function could be faster - the content is final. Understood. It normal for aggregate functions. We need more transfn function, because we need two two variant: listagg(col), listagg(col, sep). Our coding guidlines doesn't advice share C functions - but these functions are +/- wrapper for accumStringResult - so there is zero overhead. Ah, okay, it's because of the second argument. Now I understand. I don't think. When we have function, with same parameters, same behave like some Oracle function, then I am strongly prefer Oracle name. I don't see any benefit from different name. It can only confuse developers and add the trable to people who porting applications. Meh. If the name is terrible, we don't have to use it, and it's easy enough to create an alias in SQL for those who need it. 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] default_language
On Jan 24, 2010, at 2:04 PM, Tom Lane wrote: I don't see any strong argument for having a default for CREATE FUNCTION. The original argument for having a GUC for DO was that plpgsql wasn't built in; now that it is, I think a case could be made for dropping default_do_language in favor of a hardwired default. +1 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] Miscellaneous changes to plperl [PATCH]
On Jan 22, 2010, at 7:59 PM, Alex Hunsaker wrote: $name =~ s/::|'/_/g; # avoid package delimiters + $name =~ s/'/\'/g; Looks to me like ' is already handled in the line above the one you added, no? 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] 8.5 vs. 9.0, Postgres vs. PostgreSQL
On Jan 23, 2010, at 3:25 AM, Greg Stark wrote: Actually the original promounciation was mee-ess-cue-ell, My is monty's daughter's name and is pronounced like that. People generally pronounced it my though so they just made that the official pronounciation -- but they still don't approve of my-sequel. We could go with PrySQL, as in you can pry it from my cold dead fingers. Or if you're Finnish, you can think of it as coming before SQL. Or maybe TrySQL, to encourage you to try it and because you can make tress out of it. It's greener, too. Or perhaps OMGWTFSQL. No, wait, sorry, that's what I say when I'm working with MySQL. How about PugSQL? It's kind of butch, keeps the pg part, and we could have a dog logo. Or maybe we can determine that geeks are completely useless at branding and not touch this issue with a 10m pole. So, 10mPoleSQL it is. Or maybe KillThisFuckingThreadSQL. Rather suggestive, don't you think? 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] Miscellaneous changes to plperl [PATCH]
On Jan 23, 2010, at 11:20 AM, Alex Hunsaker wrote: Well no, i suppose we could fix that via: $name =~ s/[:|']/_/g; Im betting that was the intent. Doubtful. In Perl, the package separator is either `::` or `'` (for hysterical reasons). So the original code was replacing any package separator with a single underscore. Your regex would change This::Module to This__Module, which I'm certain was not the intent. 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] 8.5 vs. 9.0, Postgres vs. PostgreSQL
On Jan 23, 2010, at 1:22 PM, Magnus Hagander wrote: FYI, the figures for the past month are: 1.postgresql 45,579 10.91% 2.postgres16,225 3.88% 3.postgre 4,901 1.17% 4.postgresql download 4,590 1.10% 5.postgresql tutorial 2,408 0.58% 6.pg_dump 1,755 0.42% 7.psql1,360 0.33% 8.postgresql odbc 1,022 0.24% 9.postgre sql 964 0.23% 10. pg_restore 871 0.21% Huh. No pgsql. Interesting. David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Review: listagg aggregate
Pavel,
My review of your listagg patch.
Submission Review
-
* The diff is a context diff and applies cleanly to HEAD (with just two hunks
offset by 2 lines each).
* There is documentation, though I'm not sure it needs to be mentioned in the
string functions documentation. No harm in it, I guess.
I would like to see an example, though, and the documentation does not
currently explain what each of the parameters are for. In fact, it looks like
all the existing aggregates take only one parameter, so there was not
previously a need to explain it. But listagg() has an optional second param. I
think that the description should explain what it's for.
* There are tests and they look fine.
Usability Review
* The patch does in fact implement the aggregate function it describes, and OH
YES do we want it (I've written my own in SQL a few times).
* No, we don't already have it.
* Yes it follows community-agreed behavior. I'm assuming that there is no
special parsing of aggregate functions, so the simple use of commas to separate
the two parameters is appropriate, rather than using a keyword like MySQL's
SEPARATOR in the group_concat() aggregate.
* No need to have pg_dump support, no dangers that I can see, looks like all
the bases have been covered.
Feature Test
* Everything built cleanly, but I got an OID dupe error when I tried to init
the DB. Looks like 2997 and 2998 have been used for something else since you
created the patch. I changed them to 2995 and 2996 and then it worked.
* The feature appears to work. I didn't see any tests for encodings or other
data types, so I ran a few myself and they work fine:
postgres=# select listagg(a, U'-\0441\043B\043E\043D-') from
(values(''),(''),(''
listagg
--
-слон--слон-
(1 row)
postgres=# select listagg(a, U'\2014') from
(values(U'\0441\043B\043E\043D'),(U'd\0061t\+61'),(U'\0441\043B\043E\043D'))
AS g(a);
listagg
слон—data—слон
(1 row)
postgres=# select listagg(a::text) from (values(1),(2),(3)) AS g(a);
listagg
-
123
(1 row)
Performance Review
--
No performance issues, except that it should be faster than a custom aggregate
that does the same thing. To test, I created a quick custom aggregate (no
second argument, alas, so listagg() is more flexible) like so:
CREATE OR REPLACE FUNCTION a2s(ANYARRAY)
RETURNS TEXT LANGUAGE SQL AS $$
SELECT array_to_string($1, ',');
$$;
CREATE AGGREGATE string_accum (
SFUNC= array_accum,
BASETYPE = ANYELEMENT,
STYPE= ANYARRAY,
INITCOND = '{}',
FINALFUNC = a2s
);
Then I ran some simple tests (thanks for the clue, depesz):
postgres=# select count(*) from (select string_accum(a) from
(values(''),(''),('')) AS g(a), generate_series(1,1) i) AS x(i);
count
---
1
(1 row)
Time: 1365.382 ms
postgres=# select count(*) from (select listagg(a) from
(values(''),(''),('')) AS g(a), generate_series(1,1) i) AS x(i);
count
---
1
(1 row)
Time: 17.989 ms
So overall, it looks like listagg() is 1-2 orders of magnitude faster. YMMV,
and my system is built with --enable-cassert and --enable-debug. Still, good
job.
Coding Review
-
* Is varchar.c really the best place to put the ListAggState struct and the
listagg() function? I grepped the source for array_agg() and it's in
src/backend/utils/adt/array_userfuncs.c. Maybe there's an equivalent file for
string functions? Otherwise, the style of the C code looks fine to my untrained
eye.
Actually, shouldn't it return text rather than varchar?
* Does it really require four functions to do its work? Might there be some way
to use the array_agg() C functions and then just a different final function to
turn it into a string (using the internal array_to_string() function, perhaps)?
I'm not at all sure about it, but given how little code was required to create
the same basic functionality in SQL, I'm surprised that the C implementation
requires four functions (accumStringResult(), listagg1_transfn(),
listagg2_transfn(), and listagg_finalfn()). Maybe they're required to make it
fast and avoid the overhead of an array?
* No compiler warnings, I never made it crash, good comments, does what it says
on the tin. I doubt that there are any portability issues, as the code seems to
use standard PostgreSQL internal macros and functions.
Architecture Review
---
* No dependencies, things seem to make sense overall, notwithstanding my
questions in the Coding Review.
Review Review
-
The only thing I haven't covered so far is the name. I agree with Tom's
assertion that the name is awful. Sure there may be a precedent in Oracle, but
I hardly find that convincing (some of the big corporations seem to do a really
shitty job naming
Re: [HACKERS] 8.5 vs. 9.0, Postgres vs. PostgreSQL
On Jan 22, 2010, at 4:54 PM, Mark Mielke wrote: MS SQL, MySQL, SQLite - do they have advocacy problems due to the SQL in their name? I think it is the opposite. SQL in the name almost grants legitimacy to them as products. Dropping the SQL has the potential to increase confusion. What is a Postgres? :-) Something that comes after black, but before white. 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] 8.5 vs. 9.0, Postgres vs. PostgreSQL
On Jan 21, 2010, at 9:19 AM, Robert Haas wrote: As far as I can see, there is absolutely zero reason to care about whether the product is called Postgres or PostgreSQL. How about simply Post? Or just SQL? ;-P If it were called WeGrindUpTheBonesOfSmallChildrenSQL, maybe a change would be worth considering. And where do you think baby powder comes from? Sheesh. As it is, I submit that the product name is not on in the top 10,000 things we should be worried about fixing, even if there were a consensus that it were a good idea (which there isn't) and even if -core had not already made a decision on this point (which they have). What I think we SHOULD be worrying about right now is getting 9.0 out the door, and I am 100% opposed to letting ourselves getting sucked into this or any other discussion which is likely to make that take longer than it likely already will. +1 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] GUC failure on exception
On Jan 19, 2010, at 9:26 PM, Andrew Dunstan wrote: The first thing I think we need to do is move the GUC processing code out of _PG_init() and into plperl_init_interp(), protected by a flag to make sure it's only called successfully once. I'm trying to work out a neat way to put the value back if there is an exception, but it's a bit ugly. Will this solve the underlying bug in custom GUCs? I'm assuming there is such a bug. Won't this be an issue for other modules that have custom GUCs, potentially a security issue? The addition of new PL/Perl custom GUCs notwithstanding, it sure seems like a potential security vulnerability such as this should be addressed ASAP. Who knows this 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] Patch: regschema OID type
On Jan 21, 2010, at 9:46 AM, David Christensen wrote: It uses the same quoting mechanism as regclass, and I've tested against some odd schema names such as fooschema; I updated the docs as I was able, but am not familiar enough with the regression tests to add those yet. I hope to address that in a future revision. OOh, /me likey! This would save me a ton of code in pgTAP (about half its queries have to join to pg_namespace to get schema names). 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] Patch: regschema OID type
On Jan 21, 2010, at 9:57 AM, Tom Lane wrote: Schema names of what? It sounds to me like you're failing to use the existing regfoo types in appropriate places ... The names of schemas in which to find functions, tables, views, triggers, etc. etc. I have lots of stuff like this: SELECT true FROM pg_catalog.pg_namespace n JOIN pg_catalog.pg_class c ON n.oid = c.relnamespace WHERE c.relkind = $1 AND n.nspname = $2 AND c.relname = $3 I'd love to instead do something like: SELECT true FROM pg_catalog.pg_class c WHERE c.relkind = $1 AND c.relnamespace::regschema = $2 AND c.relname = $3 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] Patch: regschema OID type
On Jan 21, 2010, at 10:06 AM, Tom Lane wrote: Well, without a context that explains *why* you're doing that, it's hard to consider what a better solution would look like. Personally I usually prefer solutions involving WHERE oid = 'foo.bar'::regclass, because that scales easily to either providing or omitting the schema reference. It never occurred to me. And does `oid = bar::regclass` return true if bar is in a schema not in the search path? But yeah, I need to avoid errors, too. If you're trying to avoid throwing an error on bad schema name, a regschema type would not help you. Good point. 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: commit fests (was Re: [HACKERS] primary key error message)
On Jan 21, 2010, at 3:05 PM, Andrew Dunstan wrote: Well, we used to have the idea of a feature freeze ... is that going to apply at the end of the commitfest? I generally agree that we need to have a bit of wiggle room at this stage - small and non-controversial items can be allowed to creep in still. Seems to me we can have discussion anytime, though a given patch might get more or less discussion at various times. But clearly something like this would just be entered for the first 9.1 commitfest, no? 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] warn in plperl logs as... NOTICE??
On Jan 21, 2010, at 4:55 PM, Andrew Dunstan wrote: *shrug* I don't have a strong opinion about it, and it's pretty easy to change, if there's a consensus we should. I have certainly found over the years that perl warnings from some modules can be annoyingly verbose, which is probably why the original patch didn't make them have a higher level in Postgres. If this were a big issue we'd have surely heard about it before now - there are plenty of plperl users out there. Using elog(WARNING) certainly makes a lot more sense to me… 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] Helping the CommitFest re PL/Perl changes
On Jan 19, 2010, at 11:10 AM, Tim Bunce wrote: What can I do to help the CommitFest, especially in relation to the PL/Perl changes? Start reviewing other patches. An active/helpful patch submitter gets more love. 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] MySQL-ism help patch for psql
On Jan 19, 2010, at 12:58 PM, Stefan Kaltenbrunner wrote: well providing a hint that one should use different command will only lead to the path uhm why not make it work as well I don't think so. People know it's a different database. They'd be thrilled just to get the hint. I think it's a great idea (notwithstanding the caveats mentioned up-thread). 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] MySQL-ism help patch for psql
On Jan 19, 2010, at 1:39 PM, Tom Lane wrote: I thought Magnus had a really good point: covering these four cases will probably be enough to teach newbies to look at psql's backslash commands. And once they absorb that, we're over a big hump. +1 On Jan 19, 2010, at 1:57 PM, Devrim GÜNDÜZ wrote: I disagree. If they want to use PostgreSQL, they should learn our syntax. How can you make sure that this will be enough for them? What if they want more? Why would they want more? It's not MySQL, and they know that. If we give them some very minor helpful hints for the most common things they try to do, it would be a huge benefit to them. I know I've badly wanted the opposite when I've had to use MySQL, but I don't expect MySQL to implement \c for me. What if some other people will come up with the idea of adding similar functionality for their favorite database? The only exception will be Informix IMHO, because of historical reasons. I think it'd be helpful for other databases, too. Oracle comes to mind: What commands are finger-trained in Oracle DBAs? 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] is this a bug?
On Jan 17, 2010, at 3:47 PM, Tom Lane wrote:
create type y as (c char, n int);
select ('a', NULL)::y = ('a', NULL)::y; -- TRUE
select ('a', NULL) = ('a', NULL); -- NULL
I would expect those to evaluate to the same thing.
The latter gets simplified to ('a' = 'a') AND (NULL = NULL).
The former doesn't --- it goes through record_eq, which treats
two nulls as equal.
Shouldn't this go through record_eq, then?
try=# select row('a', NULL) = row('a', NULL);
?column?
--
[null]
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] Streaming replication, loose ends
On Jan 15, 2010, at 9:37 AM, Robert Haas wrote: But I'm still wondering why this isn't replication. I was wondering the same thing. ISTM that the docs could reference third-party replication solutions, too (or a wiki page listing them, since they'll change often). Anyway, I think Heikki's proposed chapter name covers it: High Availability, Load Balancing, and Replication Works for me. 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] Miscellaneous changes to plperl [PATCH]
On Jan 14, 2010, at 8:07 AM, Tim Bunce wrote: - Stored procedure subs are now given names. The names are not visible in ordinary use, but they make tools like Devel::NYTProf and Devel::Cover _much_ more useful. Wasn't this in the previous patch, too? 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] Feature patch 1 for plperl [PATCH]
On Jan 10, 2010, at 11:17 AM, Robert Haas wrote: It's nicer to write: plperl.on_perl_init='strict,warnings,LDAP,HTML::Parser,Archive::Zip' rather than: plperl.on_perl_init='use strict;use warnings;use LDAP;use HTML::Parser;use Archive::Zip;' Well, no, because sometimes I just want to load something and not have functions exported (into whatever namespaces ends up calling this). So I might have something like: plplerl.on_perl_init='use HTML::Entities ();' Other times I might want those functions exported. FWIW, Bricolage has a feature like this, and you can only put stuff on one line. It's been there since 2002 or so. No one has ever complained about it; I doubt anyone would complain about this, either. 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] RFC: PostgreSQL Add-On Network
On Jan 8, 2010, at 1:35 AM, Dave Page wrote: I am saying that if the design won't ever work without requiring painful dependency installation that users will likely not want to bother with, then it is fundamentally broken. Better to write one system that can _eventually_ work everywhere, than one that works for some, and then another that works elsewhere. This whole bit about Windows is a red herring. Perhaps I should not have phrased it the way I did WRT Windows. So I'm going to change it to: The PGAN client will make no other assumptions about how to build and install extensions, leaving such to the PostgreSQL core. To the extent that PGXS-powered `make` works on a given platform, the client will support it. Discussing it with Andrew, that means it should work if you have mingw, and we might have to tweak it a bit to work with `src/tools/msvc`. So the point is: the PGAN client (which is just one part of this project, after all), will *not* include a build system. It will use whatever build system is supported by the community. Right now that's PGXS. If core switches to something later, or provides binary builds for Windows, the client will be easily adapted to take advantage of it. No sweat. The upshot is this: PGAN does *not* ignore Windows or any other platform. Rather, it relies on others to create the appropriate community-supported installers for all platforms. The issue of build systems and installers is not within its domain. Thus, I've also changed the FAQ to: * '''What about Windows?''' The PGAN client will always follow the lead of the PostgreSQL core on installing extensions. If support for installing extensions on Windows improves such that a compiler is no longer required, the PGAN client will be modified as appropriate to take advantage of it. This applies not specifically to Windows, but to the ability of the core intaller (or any future community-supported installer) to work on ''any'' platform. Please let the Windows thread die now. PGAN doesn't ignore Windows; it ignores installer development. 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] damage control mode
On Jan 8, 2010, at 1:02 AM, Dimitri Fontaine wrote: Now, I'll second Greg Smith and Tom here, in that I think we need to run the last commitfest as usual, knowing that the outcome of the commitfest for any given patch is not it made it but we reviewed it. It's still right for the project to bump a patch on resources ground rather than on technical merit, at the end of the commitfest. +1 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] RFC: PostgreSQL Add-On Network
On Jan 8, 2010, at 9:24 AM, Dave Page wrote: If that is the goal of your project then I withdraw my previous comments, which were written on the belief that you were proposing a generic distribution/build/installation system for PostgreSQL users. It is a generic distribution and installation system, but it just uses installer approaches provided by others. This is not unlike CPAN.pm, which doesn't include an installer itself, but things through Module::Build or ExtUtils::MakeMaker as appropriate. Completely separate domain problem, as Greg notes. Sorry for the noise! Glad to have it cleared up! :-) 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] RFC: PostgreSQL Add-On Network
On Jan 8, 2010, at 9:38 AM, Magnus Hagander wrote: Is there a particular reason not to use the existing mirroring network to distribute the files? If not, then I suggest using them should be part of the design. No, as long as PAUS can drop uploaded distributions onto the master FTP server, or else the existing mirror system can rsync from PGAN's own master (I'll build all this on my own box to start with). It'll just be rsync, really, it won't where it's mirrored or where the master index lives. 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] RFC: PostgreSQL Add-On Network
On Jan 8, 2010, at 9:59 AM, Dave Page wrote: Either can be arranged. For StackBuilder, we created a pgFoundry project, so files can just be uploaded there by authorised users, from where they get replicated back onto the mirror network. Which leads us neatly back to the GForge URL thread :-) BTW, what's a PAUS? I missed that one... That's the very first part of PGAN: • PAUS: The PostgreSQL Authors Upload Server. Users will be able to create logins and upload extension distributions. Details in the wiki. http://wiki.postgresql.org/wiki/PGAN 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] RFC: PostgreSQL Add-On Network
On Jan 8, 2010, at 10:08 AM, Magnus Hagander wrote: That, or implement that send me to a random mirror feature. Or maybe the send me to a random close mirror if available, or a random global if not feature. :-) Either way, there's definitely room for some improvement there, but let's figure out what the exact needs are first :- I'll come back to this when I'm actually implementing it, for sure. 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] Feature patch 1 for plperl [PATCH]
On Jan 8, 2010, at 7:01 AM, Tim Bunce wrote: I didn't get any significant feedback from the earlier draft so here's the finished 'feature patch 1' for plperl. (This builds on my earlier plperl refactoring patch, and the follow-on ppport.h patch.) Significant changes from the first draft: - New GUC plperl.on_perl_init='...perl...' for admin use. - New GUC plperl.on_trusted_init='...perl...' for plperl user use. - New GUC plperl.on_untrusted_init='...perl...' for plperlu user use. - END blocks now run at backend exit (fixes bug #5066). - Stored procedure subs are now given names ($name__$oid). - More error checking and reporting. - Warnings no longer have an extra newline in the NOTICE text. - Various minor optimizations like pre-growing data structures. Additional changes from the second draft: - SPI functions aren't available during plperl.on_*_init execution. - Added utility functions: quote_literal, quote_nullable, quote_ident, encode_bytea, decode_bytea, looks_like_number, encode_array_literal, encode_array_constructor. - Enabled plperl to use/require safely by redirecting the require opcode to code that dies if module not already loaded. - Corresponding changes to the documentation. Additional changes in this version: - Added the missing ', arguments' to docs of spi_exec_prepared(). - Added Util.c to list of files for plperl make clean to delete. I'll add this to the commitfest. These changes all sound great to me, Tim, and if I can ever get my PL/Perl install working again, I'd be glad to find some tuits and review it. 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] Testing with concurrent sessions
On Jan 6, 2010, at 6:26 PM, Tom Lane wrote: We have not yet fully accepted the notion that you must have Perl to build (and, in fact, I am right now trying to verify that you don't). I don't think that requiring Perl to test is going to fly. I believe that the build farm already requires Perl, regardless of whether the PostgreSQL build itself requires it. 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] Testing with concurrent sessions
On Jan 6, 2010, at 6:31 PM, Kevin Grittner wrote:
As far as I've been able to determine so far, to call psql in a
relatively portable way would require something like this:
http://perldoc.perl.org/perlfork.html
Here's an example using IPC::Open3:
#!/usr/local/bin/perl -w
use strict;
use warnings;
use IPC::Open3;
use Symbol 'gensym';
use constant EOC = __DONE__\n;
my ($in1, $out1, $err1) = (gensym, gensym, gensym);
my ($in2, $out2, $err2) = (gensym, gensym, gensym);
my $pid1 = open3 $in1, $out1, $err1, 'bash';
my $pid2 = open3 $in2, $out2, $err2, 'bash';
print $in1 cd ~/dev/postgresql\n;
print $in1 ls doc\n;
print $in1 echo , EOC;
while ((my $line = $out1)) {
last if $line eq EOC;
print LS: $line;
}
print Finished file listing\n\n;
print $in2 cd ~/dev/postgresql\n;
print $in2 head -4 README\n;
print $in2 echo , EOC;
while (defined( my $line = $out2 )) {
last if $line eq EOC;
print HEAD: $line;
}
print Finished reading README\n\n;
print $in1 exit\n;
print $in2 exit\n;
waitpid $pid2, 0;
print All done!\n;
With that, I get:
LS: KNOWN_BUGS
LS: MISSING_FEATURES
LS: Makefile
LS: README.mb.big5
LS: README.mb.jp
LS: TODO
LS: bug.template
LS: src
Finished file listing
HEAD: PostgreSQL Database Management System
HEAD: =
HEAD:
HEAD: This directory contains the source code distribution of the
PostgreSQL
Finished reading README
All done!
I could easily write a very simple module to abstract all that stuff for you,
then you could just do something like:
my $psql1 = Shell::Pipe-new(qw(psql -U postgres));
my $psql2 = Shell::Pipe-new(qw(psql -U postgres));
$psql1-print('SELECT * from pg_class');
while (my $line = $psql1-readln) { print Output: $line\n }
$psql1-close;
All I'd need is some more reliable way than echo DONE__\n to be able to tell
when a particular command has finished outputting.
Thoughts?
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] Testing with concurrent sessions
On Jan 7, 2010, at 9:08 AM, Tom Lane wrote: Right, but to my mind building from a tarball needs to include the ability to run the regression tests on what you built. So injecting Perl into that is moving the goalposts on build requirements. In that case, there's nothing for it except concurrent psql. Or else some sort of shell environment that's available on all platforms. do we require bash on Windows? Oh, wait, the Windows build requires Perl… 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] Testing with concurrent sessions
On Jan 7, 2010, at 9:19 AM, Tom Lane wrote: In that case, there's nothing for it except concurrent psql. Unless we are prepared to define concurrency testing as something separate from the basic regression tests. Which is kind of annoying but perhaps less so than the alternatives. It certainly seems to me to be the kind of thing you wouldn't need to test in order to have confidence in a local build. I was rather assuming that was what we were talking about here, since we have in the past discussed testing things like dump and restore, which would require something like Perl to handle multiple processes, and wouldn't work very well for a regular release. I think if we have the ability to add tests that are not distributed, it gives us a lot more freedom and opportunity to test things that are not currently well-tested. 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
[HACKERS] RFC: PostgreSQL Add-On Network
Hackers, I've posted a [plan] to implement PGAN][], a CPAN for PostgreSQL extensions. I've tried to closely follow the [CPAN philosophy][] to come up with a plan that requires a minimum-work implementation that builds on the existing PostgreSQL tools and the examples of the [CPAN][] and [JSAN][]. My hope is that it's full of [JFDI][]! I would be very grateful for feedback and suggestions. [plan]: http://wiki.postgresql.org/wiki/PGAN [CPAN philosophy]: http://use.perl.org/article.pl?sid=02/11/12/1616209 [CPAN]: http://cpan.org/ [JSAN]: http://www.openjsan.org [JFDI]: http://acronyms.thefreedictionary.com/JFDI 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] Streaming replication and postmaster signaling
On Jan 7, 2010, at 12:10 PM, Heikki Linnakangas wrote: But FWIW I have dedicated today and tomorrow for SR, and plan to dedicate 2-3 days next week as well. Should we then await what you determine over the next week? 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] RFC: PostgreSQL Add-On Network
On Jan 7, 2010, at 12:36 PM, Dave Page wrote: Whilst the aim is a noble one, glossing over 'it won't work on Windows' which is by far our most popular platform these days seems incredibly short sighted, and liable to lead to an endless stream of 'why doesn't this work' questions. It also does the module authors no favours, by excluding 50%+ of their potential audience, and frankly, isn't the way this project generally works. That's orthogonal to the development of PGAN. The PGAN client will depend on PGXS to do the installing. If the core implements something that works better on Windows, the PGAN client will use it. We have discussed this sort of facility at previous developer meetings, and as I recall came to the conclusion that we need to have the ability to distribute pre-built binaries, not just source code as virtually no Windows users are ever going to have a build environment setup. Similarly, neither are Mac users, if they didn't install XCode. It's no hardship to install XCode. People do it for Perl/CPAN installs all the time. We also discussed extension management at the DBMS level, which I believe Dimitri was working on in his spare time. You should look at what he's been doing. He and I have discussed it. As I wrote in the proposal, when extensions get in, the PGAN client will be updated as appropriate to support them. Finally, don't write the client in Perl. Again, that's of no use to most Windows users. C will work on all platforms from the outset, with no dependencies required. Of course, the server side doesn't matter. I'll do it in Perl because: 1. It's what I know. 2. There's a lot to draw on already (CPAN.pm, JSAN::Client, CPANPLUS) 3. JFDI. The nice thing is that anyone will be able to write a client if they want it in some other language. 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] RFC: PostgreSQL Add-On Network
On Jan 7, 2010, at 1:31 PM, Dave Page wrote: No, I'm suggesting the mechanism needs to support source and binary distribution. For most *nix users, source will be fine. For Windows binaries are required. I would love to follow what Strawberry Perl has done to solve this problem. In 2.0. 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] RFC: PostgreSQL Add-On Network
On Jan 7, 2010, at 1:59 PM, Joshua D. Drake wrote: So +1 on Wheeler's idea. Thanks! 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] RFC: PostgreSQL Add-On Network
On Jan 7, 2010, at 2:11 PM, Peter Eisentraut wrote: You might want to clarify in your prose what an extension is. I suspect I know what you mean, but perhaps not everyone does. Good suggestion, thanks. How about this in the FAQ? * WTF is an extension? An extension is a piece of software that adds functionality to PostgreSQL itself. Examples are data types (CITEXT, PERIOD), utilities (newsysviews, pgTAP), and procedural languages (PL/Ruby, PL/R), among others. An extension is *not* a piece of software designed to run on top of PostgreSQL (Bricolage, Drupal). 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] RFC: PostgreSQL Add-On Network
On Jan 7, 2010, at 2:23 PM, Dimitri Fontaine wrote: Maybe with a link to: http://www.postgresql.org/docs/8.4/static/extend.html Good call, thanks. 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] Status of plperl inter-sp calling
On Jan 6, 2010, at 11:27 AM, Andrew Dunstan wrote: That's a case of out of date docco more than anything else, AFAIK. It's been there at least since 5.6.2 (which is the earliest source I have on hand). Which likely also means 5.6.1 and quite possibly 5.6.0. I don't recommend anything earlier than 5.6.2, though, frankly, and 5.8.9 is a better choice. 5.10.1 better still. Is there a documented required minimum version for PL/Perl? 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] Status of plperl inter-sp calling
On Jan 6, 2010, at 12:20 PM, Tom Lane wrote: One of the things on my to-do list for today is to make configure reject Perl versions less than $TBD. I thought we had agreed a week or so back that 5.8 was the lowest safe version because of utf8 and other considerations. +1, and 5.8.3 at a minimum for utf8 stuff, 5.8.8 much much better. 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] Testing with concurrent sessions
On Jan 6, 2010, at 1:52 PM, Kevin Grittner wrote: Last I heard, Andrew was willing to require Test::More for testing, so that a Perl script could handle multiple psql connections (perhaps forked) and output test results based on them. But he wasn't as interested in requiring DBI and DBD::Pg, neither of which are in the Perl core and are more of a PITA to install (not huge, but the barrier might as well stay low). OK, I've gotten familiar with Perl as a programming language and tinkered with Test::More. What's not clear to me yet is what would be considered good technique for launching several psql sessions from that environment, interleaving commands to each of them, and checking results from each of them as the test plan progresses. Any code snippets or URLs to help me understand that are welcome. (It seems clear enough with DBI, but I'm trying to avoid that per the above.) Probably the simplest way is to use the core IPC::Open3 module: http://search.cpan.org/perldoc?IPC::Open3 IPC::Run might be easier to use if it's available, but it's not in Perl core, so YMMV. Really it's up to andrew what modules he requires test servers to have. 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] Testing with concurrent sessions
On Jan 6, 2010, at 2:08 PM, Peter Eisentraut wrote: Then I don't see much of a point in using Perl. You might as well fire up a few psqls from a shell script If you're more comfortable with shell, then yes. Although then it won't run on Windows, will it? 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] Status of plperl inter-sp calling
On Jan 6, 2010, at 3:31 PM, Tim Bunce wrote: For 8.5 I don't think I'll even attempt direct inter-plperl-calls. I'll just do a nicely-sugared wrapper around spi_exec_prepared(). Either via import, as I outlined earlier, or Garick Hamlin's suggestion of attributes - which is certainly worth exploring. If it's just the sugar, then in addition to the export, which is a great idea, I'd still like to have the AUTOLOAD solution, since there may be a bunch of different functions and I might not want to import them all. 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] Status of plperl inter-sp calling
On Jan 6, 2010, at 5:46 PM, Tom Lane wrote: I went with 5.8 as the cutoff, for a couple of reasons: we're not in the business of telling people they ought to be up-to-date, but only of rejecting versions that demonstrably fail badly; and I found out that older versions of awk are not sufficiently competent with and || to code a more complex test properly :-(. A version check that doesn't actually do what it claims to is worse than useless, and old buggy awk is exactly what you'd expect to find on a box with old buggy perl. Yes, but even a buggy old Perl is quite competent with and ||. Why use awk to test the version of Perl when you have this other nice utility to do the job? (It's also worth noting that the perl version seen at configure time is not necessarily that seen at runtime, anyway, so there's not a lot of point in getting too finicky here.) Fair enough. 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] Status of plperl inter-sp calling
On Jan 5, 2010, at 12:59 PM, Tim Bunce wrote: So you're suggesting SP::foo(...) _always_ executes foo(...) via bunch of spi_* calls. Umm. I thought performance was a major driving factor. Sounds like you're more keen on syntactic sugar. I'm saying do both. Make the cached version the one that will be used most often, but make available a second version that doesn't cache so that you get the sugar and the polymorphic dispatch. Such would only have to be used in cases where there is more than one function that takes the same number of arguments. The rest of the time -- most of the time, that is -- one can use the cached version. 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] Testing with concurrent sessions
On Jan 4, 2010, at 3:29 PM, Peter Eisentraut wrote: If you're not deep into Perl, perhaps ignore the Test::More comment for the moment and just use DBI to connect to several database sessions, execute your queries and check if the results are what you want. Once you have gotten somewhere with that, wrapping a test harness around it is something others will be able to help with. Last I heard, Andrew was willing to require Test::More for testing, so that a Perl script could handle multiple psql connections (perhaps forked) and output test results based on them. But he wasn't as interested in requiring DBI and DBD::Pg, neither of which are in the Perl core and are more of a PITA to install (not huge, but the barrier might as well stay low). pgTAP isn't really going to help you here, as it runs with *one* database session, and its main functionality is to format the result of SQL functions into TAP output, which is not very much like what you ought to be doing. Right, exactly. 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] PATCH: Add hstore_to_json()
On Jan 4, 2010, at 8:18 PM, Robert Haas wrote: Is this something you are planning to work on for the 2010-01-15 CommitFest? If not, I think we should go ahead and mark the patch which was the original subject of this thread Returned with Feedback, as it does not seem to make sense to add it unless we add a json type first. Not me, too much on my plate, and not enough C knowledge to be efficient. Agreed on Returned with Feedback. 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] PATCH: Add hstore_to_json()
On Jan 3, 2010, at 8:00 AM, Andrew Dunstan wrote: I think the minimal functionality I'd want is: convert record to JSON convert JSON to record With caveats as to dealing with nested structures (can a record be an attribute of a record?). extract a value, or set of values, from JSON composition of JSON There's a lot of functionality in hstore that I'd like to see. It'd make sense to use the same operators for the same operations. I think I'd start with hstore as a basic spec. 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] Testing with concurrent sessions
On Jan 1, 2010, at 6:01 PM, Kevin Grittner wrote: It's very soon going to be critical that I be able to test particular interleavings of statements in particular concurrent transaction sets to be able to make meaningful progress on the serializable transaction work. It would be wonderful if some of these scripts could be integrated into the PostgreSQL 'make check' scripts, although that's not an absolute requirement. I'm not really concerned about performance tests for a while, just testing the behavior of particular interleavings of statements in multiple sessions. If psql isn't expected to support that soon, any suggestions? Is pgTAP suited to this? We've discussed it a bit in the past with regard to testing replication and such. I think the consensus was, failing support for concurrent sessions in psql, to use a Perl script to control multiple psql sessions and perhaps use Test::More to do the testing. Although pgTAP might make sense, too, if the tests ought to run in the database. 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] PATCH: Add hstore_to_json()
On Jan 3, 2010, at 11:40 AM, Andrew Dunstan wrote:
We allow composites as fields. The biggest mismatch in the type model is
probably w.r.t arrays. JSON arrays can be heterogenous and non-rectangular,
AIUI.
Cool, that sounds right.
OK, but hstores are flat, unlike JSON. We need some way to do the equivalent
of xpath along the child axis and without predicate tests. hstore has no real
equivalent because it has no nesting.
You mean so that you can fetch a nested value? Hrm. I agree that it's have to
be XPath like. But perhaps we can use a JavaScript-y syntax for it? There could
be an operator that returns records:
% SELECT '{foo:{bar:[a,b,c]}}' - '[foo]';
bar
-
({a,b,c})
% SELECT '{foo:{bar:[a,b,c]}}' - '[foo][1]';
1
-
(b)
And another that returns values where possible and JSON where there are data
structures.
% SELECT '{foo:{bar:[a,b,c]}}' = '[foo]';
?column?
--
{bar:{a,b,c}}
% SELECT '{foo:{bar:[a,b,c]}}' = '[foo][1]';
?column?
--
b
Not sure if the same function can return different values, or if it's even
appropriate. In addition to returning JSON and TEXT as above, we'd also need to
be able to return numbers:
% SELECT '{foo:{bar:[22,42]}}' = '[foo][1]';
?column?
--
42
Thoughts?
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] invalid UTF-8 via pl/perl
On Jan 3, 2010, at 11:54 AM, Andrew Dunstan wrote: There are two issues with this patch. First, how far if at all should it be backpatched? All the way, or 8.3, where we tightened the encoding rules, or not at all? 8.3 seems reasonable. Second, It produces errors like this: andrew=# select 'a' || invalid_utf_seq() || 'b'; ERROR: invalid byte sequence for encoding UTF8: 0xd0 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by client_encoding. CONTEXT: PL/Perl function invalid_utf_seq andrew=# That hint seems rather misleading. I'm not sure what we can do about it though. If we set the noError param on pg_verifymbstr() we would miss the error message that actually identified the bad data, so that doesn't seem like a good plan. I'm sure I'm just revealing my ignorance here, but how is the hint misleading? 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] PATCH: Add hstore_to_json()
On Jan 3, 2010, at 4:18 PM, Hitoshi Harada wrote:
That sounds good and seems possible, as far as operator returns JSON
always. Perhaps every JSON fetching returns JSON even if the result
would be a number. You can cast it.
% SELECT ('{foo:{bar:[a,b,c]}}' - '[foo][1]')::text;
1
-
b
No, because 'b' isn't valid JSON. So if we want an interface that returns
scalars, they can't be JSON.
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] PATCH: Add hstore_to_json()
On Dec 31, 2009, at 1:04 AM, Peter Eisentraut wrote: I think the primary use will be to load a JSON value into Perl or Python and process it there. So a json type that doesn't have any interesting operators doesn't sound useless to me. The features I would like to get out of it are input validation and encoding handling and smooth integration with said languages. What about access to various parts of a JSON data structure? Or is that just asking for too much trouble up-front? 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] Status of plperl inter-sp calling
On Dec 30, 2009, at 2:54 PM, Tim Bunce wrote:
That handles the arity of the calls and invokes the right SP, bypassing
SQL if the SP is already loaded.
Nice.
That much works currently. Behind the scenes, when a stored procedure is
loaded into plperl the code ref for the perl sub is stored in a cache.
Effectively just
$cache{$name}[$nargs] = $coderef;
An SP::AUTOLOAD sub intercepts any SP::* call and effectively does
lookup_sp($name, \...@_)-(@_);
For SPs that are already loaded lookup_sp returns $cache{$name}[$nargs]
so the overhead of the call is very small.
Definite benefit, there. How does it handle the difference between IMMUTABLE |
STABLE | VOLATILE, as well as STRICT functions? And what does it do if the
function called is not actually a Perl function?
For SPs that are not cached, lookup_sp returns a code ref of a closure
that will invoke $name with the args in @_ via
spi_exec_query(select * from $name($encoded_args));
The fallback-to-SQL behaviour neatly handles non-cached SPs (forcing
them to be loaded and thus cached), and inter-language calling (both
plperl-plperl and other PLs).
Is there a way for such a function to be cached? If not, I'm not sure where
cached functions come from.
Limitations:
* It's not meant to handle type polymorphism, only the number of args.
Well, spi_exec_query() handles the type polymorphism. So might it be possible
to call SP::function() and have it not use a cached query? That way, one gets
the benefit of polymorphism. Maybe there's a SP package that does caching, and
an SPI package that does not? (Better named, though.)
* When invoked via SQL, because the SP isn't cached, all non-ref args
are all expressed as strings via quote_nullable(). Any array refs
are encoded as ARRAY[...] via encode_array_constructor().
Hrm. Why not use spi_prepare() and let spi_exec_prepared() handle the quoting?
I don't see either of those as significant issues: If you need more
control for a particular SP then don't use SP::* to call that SP.
If there was a non-cached version that was essentially just sugar for the SPI
stuff, I think that would be more predicable, no? I'm not saying there
shouldn't be a cached interface, just that it should not be the first choice
when using polymorphic functions and non-PL/Perl functions.
Open issues:
* What should SP::foo(...) return? The plain as-if-called-by-perl
return value, or something closer to what spi_exec_query() returns?
The former.
* If the called SP::foo(...) calls return_next those rows are returned
directly to the client. That can be construed as a feature.
As a list?
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
[HACKERS] Group and Role Disagreement
Fellow Hackers,
Given this SQL:
BEGIN;
CREATE ROLE foo WITH NOLOGIN;
CREATE ROLE foo_bar WITH LOGIN PASSWORD '***' INHERIT IN ROLE foo;
CREATE ROLE foo_baz WITH LOGIN PASSWORD '***' INHERIT IN ROLE foo;
CREATE ROLE foo_yow WITH LOGIN PASSWORD '***' INHERIT
IN ROLE foo, foo_bar, foo_baz;
SELECT groname, array_agg(rolname)
FROM pg_group
JOIN pg_roles ON pg_roles.oid = ANY(grolist)
WHERE groname IN ('foo', 'foo_bar', 'foo_baz', 'foo_yow')
GROUP BY groname;
SELECT r.rolname,
ARRAY(SELECT b.rolname
FROM pg_catalog.pg_auth_members m
JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
WHERE m.member = r.oid) as memberof
FROM pg_catalog.pg_roles r
WHERE rolname IN ('foo', 'foo_bar', 'foo_baz', 'foo_yow');
ROLLBACK;
The output is:
BEGIN
CREATE ROLE
CREATE ROLE
CREATE ROLE
CREATE ROLE
groname | array_agg
-+---
foo | {foo_bar,foo_baz,foo_yow}
(1 row)
rolname | memberof
-+---
foo | {}
foo_bar | {foo}
foo_baz | {foo}
foo_yow | {foo,foo_bar,foo_baz}
(4 rows)
ROLLBACK
My question is: why is the group membership of the foo_bar, foo_baz, and
foo_yow roles not reflected in pg_group? Should it not have the same
associations as pg_roles? A quick query shows that the only record in pg_group
is for the foo group -- it doesn't even know that the foo_bar, foo_baz, and
foo_yow roles also act as groups. Should it?
Thanks,
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] Group and Role Disagreement
On Dec 31, 2009, at 3:41 PM, Tom Lane wrote: My question is: why is the group membership of the foo_bar, foo_baz, and foo_yow roles not reflected in pg_group? Per the fine manual: The view pg_group exists for backwards compatibility: it emulates a catalog that existed in PostgreSQL before version 8.1. It shows the names and members of all roles that are marked as not rolcanlogin, which is an approximation to the set of roles that are being used as groups. Ah, hadn't noticed that, thanks for the pointer to TFM. 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] PATCH: Add hstore_to_json()
On Dec 29, 2009, at 6:14 PM, Robert Haas wrote: I've been mulling this over and I think this is a pretty good idea. If we could get it done in time for 8.5, we could actually change the output type of EXPLAIN (FORMAT JSON) to the new type. If not, I'm inclined to say that we should postpone adding any more functions that generate json output until such time as we have a real type for it. I wouldn't feel too bad about changing the output type of EXPLAIN (FORMAT JSON) from text to json in 8.6, because it's relatively difficult to be depending on that for anything very important. It's much easier to be depending on something like this, and changing it later could easily break working applications. +1 Anyone have an interest in taking a crack at this? There are a bunch of C libraries listed on http://www.json.org/. Perhaps one has a suitable license and clean enough implementation to be used? 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] PATCH: Add hstore_to_json()
On Dec 30, 2009, at 9:53 AM, Robert Haas wrote: It looks like they are all very permissive, though I wonder what the legal effect of a license clause that the software be used for Good and not Evil might be. Yeah, that might be too restrictive, given that PostgreSQL is used by government agencies and porn sites. Not that a given gov or porn site is inherently evil, mind, but some are. ;-P I guess the question is whether we would slurp one of these into our code base, or whether we would add an analog of --with-libxml and provide only a stub implementation when the library is not present. Any opinions? Does anyone know whether any of these implementations are commonly packaged already? I doubt that they have similar interfaces, so we'd probably have to rely on one. I'd probably favor embedding, personally, it's less work for admins. 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] Status of plperl inter-sp calling
On Dec 30, 2009, at 4:17 PM, Robert Haas wrote:
That much works currently. Behind the scenes, when a stored procedure is
loaded into plperl the code ref for the perl sub is stored in a cache.
Effectively just
$cache{$name}[$nargs] = $coderef;
That doesn't seem like enough to guarantee that you've got the right
function. What if you have two functions with the same number of
arguments but different argument types? And what about optional
arguments, variable arguments, etc.?
As Tim said elsewhere:
I don't see either of those as significant issues: If you need more
control for a particular SP then don't use SP::* to call that SP.
Best,
Davdi
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] unicode questions
On Dec 24, 2009, at 4:14 PM, Andrew Dunstan wrote: 2) How far is normalization support in PG? When I checked a long time ago, there was no such support. Now that the SQL standard mandates a NORMALIZE function that may have changed. Any updates? Creating such a function shouldn't be terribly hard AIUI, if someone wants to submit a patch. It was raised about three months ago but nobody actually volunteered unless I missed that. I wrote a similar function in PL/Perl: http://justatheory.com/computers/databases/postgresql/unicode-normalization.html 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] Segfault from PL/Perl Returning vstring
On Dec 21, 2009, at 9:04 PM, Andrew Dunstan wrote: I cannot reproduce this. I tested with perl 5.10.1 which is the latest reported stable release at http://www.cpan.org/src/README.html, on an 8.4.2 UTF8 database, and with the same Safe and Encode module versions as above. I've replicated it all the way back to 8.0. I'd be happy to give you a login to my box. 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] alpha3 release schedule?
On Dec 22, 2009, at 11:02 AM, Simon Riggs wrote: I've clearly been working too hard and will retire for some rest (even though that is not listed as a task on the Wiki). Someone add it! 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] creating index names automatically?
On Dec 22, 2009, at 7:31 PM, Tom Lane wrote: Wait a minute. I must have been looking at the wrong keyword list --- ON already is reserved. The problem is exactly that it can't tell whether CREATE INDEX CONCURRENTLY ON ... means to default the index name or to create an index named CONCURRENTLY. So really the *only* way to fix this is to make CONCURRENTLY be at least type_func_name_keyword. +1 if it prevents indexes from being named CONCURRENTLY. 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] Minimum perl version supported
On Dec 21, 2009, at 7:18 AM, Tim Bunce wrote: Given the above three things it seems like we could define 5.8.1 as the minimum required version. I'd be delighted with that. +1 BTW Tim, have you tested with 5.11 yet? 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
