From mboxrd@z Thu Jan 1 00:00:00 1970 X-Spam-Checker-Version: SpamAssassin 3.4.4 (2020-01-24) on polar.synack.me X-Spam-Level: X-Spam-Status: No, score=-1.9 required=5.0 tests=BAYES_00,FREEMAIL_FROM autolearn=unavailable autolearn_force=no version=3.4.4 X-Received: by 10.42.92.69 with SMTP id s5mr5236454icm.20.1399658527028; Fri, 09 May 2014 11:02:07 -0700 (PDT) X-Received: by 10.140.96.41 with SMTP id j38mr59258qge.8.1399658526996; Fri, 09 May 2014 11:02:06 -0700 (PDT) Path: border1.nntp.dca3.giganews.com!backlog3.nntp.dca3.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!c1no3763096igq.0!news-out.google.com!dz10ni36309qab.1!nntp.google.com!hw13no1856801qab.0!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail Newsgroups: comp.lang.ada Date: Fri, 9 May 2014 11:02:06 -0700 (PDT) In-Reply-To: <304f2721-64bc-4959-a7e8-0cc29cf5204f@googlegroups.com> Complaints-To: groups-abuse@google.com Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=46.195.148.91; posting-account=3_reEwoAAAC163IAIrx427KYmwahFuh9 NNTP-Posting-Host: 46.195.148.91 References: <304f2721-64bc-4959-a7e8-0cc29cf5204f@googlegroups.com> User-Agent: G2/1.0 MIME-Version: 1.0 Message-ID: <49354f93-75ef-4b1c-90a3-0fdedc7e36f3@googlegroups.com> Subject: Re: Ada and PL/pgSQL ? From: =?ISO-8859-1?Q?bj=F6rn_lundin?= Injection-Date: Fri, 09 May 2014 18:02:07 +0000 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Original-Bytes: 4849 Xref: number.nntp.dca.giganews.com comp.lang.ada:186337 Date: 2014-05-09T11:02:06-07:00 List-Id: Den fredagen den 9:e maj 2014 kl. 18:26:22 UTC+2 skrev sdale...@gmail.com: > After careful review the most frequent and essential logic functions of t= his > exchange application directly as a collection of stored procedures,=20 > functions and triggers in the Postgres database that will be backing it, = seems to make the most sense. I think not. I'm maintaining/developing a farliy bif Warhouse management sy= stem, build in Ada. It is _very_ database intensicew and I would not even think ofd putting the= logic=20 in a database. >=20 > The logic behind the decision is pretty clear cut.=20 > Transit time back and forth between the database becomes=20 > cumulative and burdensome when you consider just how many queries are inv= olved. Bad database design I'd say. > By way of example, we have a transactions table and an orders table. =20 > Before an order can be placed, the transactions table must reflect > a debit against the user account by the amount of their bid=20 > by way of a special "HELDFORORDER" flag that otherwise looks like=20 > a normal withdrawal transaction. Then once the order is settled,=20 > that flag must be changed to DEBIT. >=20 >=20 > Under the original system, each of these steps were at > least one round trip between application core logic=20 > ( a mix of PHP and C) and the DB (which was originally=20 > level and then moved to MySQL).=20 > The minimum execution time for an order was 30s. > However it was climbing as high as 5mins.=20 > About 20% was query and transit time.=20 > With a majority of the slow down occuring from table locks on db transact= ions. =20 And there is your problem. Mysql is not scaling weill (or did not at least)= for concurrent writes, since it lock the whole table. Bad choise of db. > Thus the decision to move that logic closer to the datastore, embedding i= t if at all possible.=20 You would still have lock table problems if moving logic to Mysql. It would just lock the tables faster, Postgres/oracle/sql-server does not (necessary) lock tables in the same manner as mysql. >=20 > While researching the PL/pgSQL language I noticed that it > bears a shocking similarity to Ada. Almost like it's Ada but tailored fo= r SQL. =20 To me it seems like it is heavily inspired by Oracle's PL/SQL, which=20 is 'inspired' by Ada83 - almost identical=20 =20 > Still I would like to maintain the safety and provability of Ada. > It appears that Postgres has the ability to import various languages=20 > such as C and execute those about as easily as it's own native language. > So the question I have here is, does anyone know of a > path to get Ada functions operational in Postgres. Or=20 > (assuming there is a pathway) is Postgres's PL/pgSQL going to=20 > give the same benefits of Ada? =20 >=20 No, but postgres is extensible. You can wrtie youre own Pl/Ada extension as there is pl/tcl, pl/java, pl/perl ... I really think you are on the wrong track. Keep your thought of using Ada. Make test programs and see if you get such bad timings as you describe.=20 read up on postgres maintenance and performance tuning . I really doubt that you have a more complex db schema than we do. I really doubt that you will have those transfer times if you are using=20 a somewhat modern/fast server. If you do, then database analysis come in play. Indicies/normalization to a sensible degree is everything in a db-intensive= application. Unless you are sending a massive amount of data to ther server, I think you are bitten by mysql performance. /Bj=F6rn