01:43 < donny> so i'm checking out the postgres source because i'm exploring the possibility of byte-compiling instructions given to the server to run on a common VM 01:43 < donny> i see that you guys already support using other programming languages on the server 01:43 < donny> but i doubt you are actually compiling those languages to a common run-time environment 01:44 < peerce> bytecompiling SQL queries ?!? 01:44 < donny> the reason i'm saying this is because i'm interested in how SQL instructions are executed now -- are they byte-compiled? 01:44 < donny> apparently not ;) 01:45 < donny> the purpose is not really to byte-compile SQL queries 01:45 -!- ltbarcly [n=ltbarcly@ip72-192-249-57.dc.dc.cox.net] has joined #postgresql 01:45 < peerce> the vast majority of the work in SQL isn't parsing or interpreting, its calculating an optimal execution plan, then executing it. 01:45 < donny> the purpose is to allow functionality written in other langauges to run natively within the postgres server without extending the server at all 01:45 -!- ltbarcly_ [n=ltbarcly@ip72-192-249-57.dc.dc.cox.net] has quit [Read error: 104 (Connection reset by peer)] 01:45 < AlexB> donny: Looked into the various procedural languages? 01:45 < donny> the other reason is best explained by plpythonu 01:45 -!- HSquirrel [n=alexeyp@60-240-121-160.static.tpgi.com.au] has joined #postgresql 01:45 -!- burnin [n=burnin@204-228-142-194.ip.xmission.com] has quit ["Leaving"] 01:46 < metatrontech> donny, there are a couple of options. 01:46 < donny> peerce: i understand that. i wouldn't even *touch* that part of the code. probably far too complex for me. 01:46 < metatrontech> plpgsql statements (aside from EXECUTE statements) are cached as a query plan. 01:46 < metatrontech> So in a way they are sort of byte compiled. 01:46 < donny> AlexB: i'm looking at them a bit now, yes. mostly at pl/python because i have a lot of experience embedding and extending python 01:47 < metatrontech> server-side prepares also do something similar. 01:47 -!- scampbell [n=scampbel@mail.scampbell.net] has quit ["Konversation terminated!"] 01:47 < donny> metatrontech: well it's not really important to me at this stage that i implement a plpgsql compiler for whatever VM i try to pop into postgres 01:47 < metatrontech> But the cost is that the initial plan doesn't isn't always optimal for second run. 01:47 -!- sleepcat [n=chatzill@71-34-74-52.ptld.qwest.net] has quit [Remote closed the connection] 01:47 < metatrontech> ? 01:47 < metatrontech> The "VM" is the Postgresql executor. 01:48 < peerce> i'd be inclined to suspect the plpython module uses the core of the regular python compiler/executor/vm/whatever it is. 01:48 < donny> the real advantage of byte-compiling all procedural languages to a common runtime bytecode is that things like the security issue that are present in plpythonu would become a non-issue 01:48 < donny> metatrontech: executor, huh? *looks into the source* 01:48 < metatrontech> ok. what I mean is--- 01:49 < metatrontech> the query plan is essentially the equivalent of a byte-compiled SQL Query. 01:49 < metatrontech> it runs inside the Pgsql backend. 01:49 -!- ketema [n=ketema@rrcs-24-227-61-54.se.biz.rr.com] has quit [] 01:49 < donny> metatrontech: i believe i understand you. i suspected as much to begin with. 01:50 < donny> metatrontech: however all i'm trying to clarify is that i wouldn't be immediately interested in providing a new compiler target for ordinary SQL queries, only for alternate procedural languages 01:51 -!- tdi [n=tdi@gvf90.internetdsl.tpnet.pl] has quit [Read error: 110 (Connection timed out)] 01:51 < donny> the other big advantage to this approach is that it gives postgres the opportunity to become very intimate with the execution of code written in an alternate procedural language 01:51 < peerce> you mean, they might have babies? 01:51 < peerce> :D 01:52 < donny> that in turn allows programmers to use native constructs which make good data access abstractions to refer to tables 01:52 * metatrontech wonders if there are other options for caching query plans for other PL handlers. 01:52 < peerce> the performance of the pl/**** code is rarely that important, its consumed almost entirely by the SQL queriees. 01:52 < donny> for instance you could write code like "for each in accounts: if each.username == someusername and each.password == passhash: login()" 01:52 < peerce> most pl/*** functions are almost pure query, with just a bit of procedural glue 01:53 -!- sleepcat [n=chatzill@71-34-74-52.ptld.qwest.net] has joined #postgresql 01:53 -!- mordaunt [n=mordaunt@unaffiliated/mordaunt] has quit [Remote closed the connection] 01:53 < donny> and then postgres would be able to translate this into what you'd have gotten with the sql statement "select count(*) from accounts where username=someusername and password=passhash; ...return this somehow not using SQL obviosuly" 01:54 -!- pOxbOx [n=pzolivie@bas6-montreal02-1096660622.dsl.bell.ca] has quit [] 01:54 < donny> peerce: i'm not really concerned with increasing the performance of currently available good programming practices 01:55 < donny> peerce: i'm interested in making it possible to program in new ways without sacrificing performance 01:55 -!- Girr [n=Girr@70-100-188-177.dsl1-erie.roc.ny.frontiernet.net] has quit [Remote closed the connection] 01:55 < sleepcat> donny: you are a genius 01:56 < donny> peerce: for instance now, in order to use my python pseudocode example, data from the database would have to be passed from the postgres core to the python vm continuously, resulting in a ridiculous amount of copying -- this would be prohibitively expensive in many many cases, especailly as it means you can't take advantage of indexing 01:56 < donny> sleepcat: thank you! 01:56 < Gurthg> . 01:56 * donny wonders if sleepcat is being sarcastic 01:57 < peerce> so why would you use python to do something like that when its a simple SELECT statement ? 01:57 < donny> peerce: well that's a poor example, obviously ;) nderance 02:00 < sleepcat> donny: or they can spend a week to learn sql 02:00 < donny> also, program flow analysis is important to optimizing compilers 02:01 -!- jwp [n=jwp@216.4.146.131] has quit ["mmm, is that cake?"] 02:01 < metatrontech> donny-- you are trying to build in-db orms? 02:01 -!- jstephan [n=jstephan@p54A5D25B.dip.t-dialin.net] has joined #postgresql 02:01 < donny> sleepcat: lol, well my interest in the subject may be partially academic in nature (as opposed to practical) 02:01 < peerce> but query plan optimization is very much a runtime thing, as the optimizations are based on the CONTENT of the database tables involved. 02:01 < donny> metatrontech: yes, basically 02:01 < donny> in the future, in fact 02:01 < metatrontech> donny, in LSMB we do something like that with PLPGSQL. 02:01 < donny> metatrontech: what is LSMB? 02:02 < metatrontech> LedgerSMB, an accounting app. 02:02 < donny> ooh, i've been looking for a good one of those ;) 02:02 < donny> (probably not what i'm looknig for though) 02:02 * donny goes to the bathroom 02:02 < metatrontech> Basically, we create procedures in PLPGSQL which are then mapped to object methods. 02:02 < metatrontech> Unfortunately there are some limitations until we can depend on 8.3. 02:03 * metatrontech would like to be able to define objects in PLPGSQL and then access them in any other language automatically) 02:03 -!- roverr [i=kvirc@mx1.hesgdv.de] has joined #postgresql 02:04 < donny> metatrontech: what changes in 8.3? 02:04 -!- muvy [n=ivan@geologia5.agora.ing.unipg.it] has joined #postgresql 02:04 < metatrontech> The big limitation at the moment is in rich data types being passed to procedures. 02:04 < metatrontech> In 8.3, one can have arrays of tuples. 02:04 < metatrontech> Which allows for stricter typing than arrays of arrays. 02:05 < metatrontech> Basically we have some glue code so the Perl (or possiby eventually PL/Perl too) can just invoke function names and let the app do the mapping for them. 02:05 < metatrontech> This way you only have to have the SQL interface written once, and nobody has to worry about how the call actually happens. 02:06 < donny> :) 02:06 < donny> sounds good 02:06 < metatrontech> In short, no more mixing SQL and whatever language is used (except for one file which does the mapping) 02:07 < metatrontech> (and there it is pretty minimal) 02:08 < metatrontech> I am, however, running into a few odd and unexpected performance issues. Hopefully these can be fixed eventually but they are not that major since we can have workaround. 02:08 < metatrontech> (workarounds) 02:08 < peerce> except, thats the opposite end of what donny is talking about. 02:08 < donny> one time when i was high, i had the crazy thought to implement something similar to what i've been discussing here, except that the VM was much more capable, including the ability to utilize TCP/IP. i had the crazy idea that you could upload entire byte-compiled game servers and connect directly to the database server with your game client. 02:09 < statim> ok, i have a boolean field default => false, not null in a pgsql db... if i set field = false, field is nil in the object, causing the constraint to fail... any run into this? 02:09 < peerce> donny wants to do away with sql entirely in queries and implement them via python extensions to directly manipulate the RDBMS innards 02:09 < metatrontech> Pretty much-- same problem, nearly opposite-solution vector. 02:09 < statim> er sorry.. that was a rails question wrong channel 02:09 < donny> peerce: i think my language of choice these days might be ECMAScript ;) Python was only an example 02:09 < statim> but by all means if anyone in here uses pg in their rails app feel free :) 02:09 < peerce> javascript? blah. 02:09 < peerce> horrid language 02:09 < donny> ! 02:09 < donny> false! 02:10 < donny> it has some rough edges 02:10 < sleepcat> donny: why not just use stored procedures? 02:10 < peerce> statim; don't know the first hting about ruby, but I suppose you could have a trigger procedure inside of pgsql that converts any NULL fields to '' on the tables of interest 02:10 < donny> and i hate developing anything that's supposed to work on MSIE 02:10 < donny> but i'll be damned if i don't get a lot done in a short period of time doing anything else in JS 02:11 < donny> sleepcat: stored procedures alleviate performance concerns, however scroll up to see why it isn't the same solution when i said "not all programmers know Python" I meant to say "not all programmers know SQL"