Re: Ada and PL/pgSQL ?

["björn lundin" <b.f.lundin@gmail.com>]

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 this
> exchange application directly as a collection of stored procedures, 
> 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 system, build in Ada.
It is _very_ database intensicew and I would not even think ofd putting the logic 
in a database.
> 
> The logic behind the decision is pretty clear cut. 
> Transit time back and forth between the database becomes 
> cumulative and burdensome when you consider just how many queries are involved.

Bad database design I'd say.

> By way of example, we have a transactions table and an orders table.  
> Before an order can be placed, the transactions table must reflect
>  a debit against the user account by the amount of their bid 
> by way of a special "HELDFORORDER" flag that otherwise looks like 
> a normal withdrawal transaction.  Then once the order is settled, 
> that flag must be changed to DEBIT.

</snip logic>

> 
> 
> Under the original system, each of these steps were at
> least one round trip between application core logic 
> ( a mix of PHP and C) and the DB (which was originally 
> level and then moved to MySQL). 
> The minimum execution time for an order was 30s.
> However it was climbing as high as 5mins. 
> About 20% was query and transit time. 
> With a majority of the slow down occuring from table locks on db transactions.
 
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 it if at all possible. 

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.
> 
> While researching the PL/pgSQL language I noticed that it
> bears a shocking similarity to Ada.  Almost like it's Ada but tailored for SQL.
 
To me it seems like it is heavily inspired by Oracle's PL/SQL, which 
is 'inspired' by Ada83 - almost identical 
  
> Still I would like to maintain the safety and provability of Ada.
> It appears that Postgres has the ability to import various languages 
> 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 
> (assuming there is a pathway) is Postgres's PL/pgSQL going to 
> give the same benefits of Ada?  
> 
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. 
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 
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örn