Ada and PL/pgSQL ?

Ada and PL/pgSQL ?

[sdalemorrey]

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.

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.

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.

Thusly even a simple market order consists of several round trips to the database, unless the logic can be consolidated into a collection of functions that are stored in the DB.

Overall an order looks like this..

Step 1:  Check user balance and ensure it is sufficient to cover the order
Step 2:  Hold funds for order
Step 3:  Create Order
Step 4:  If market order or FOK then trigger the order matching engine.
Step 5:  Attempt to fill orders by comparing bid/ask and matching them.
Step 6:  Convert transaction hold flag to debit and link in the orderID.

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.

Thus the decision to move that logic closer to the datastore, embedding it if at all possible. 

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.

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?  

The languages are to my mind so similar that I'm having real difficulty seeing a major difference between the two.

Thanks for any info!

Re: Ada and PL/pgSQL ?

[björn lundin]

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

Re: Ada and PL/pgSQL ?

[sdalemorrey]

> Unless you are sending a massive amount of data to ther server,
> 
> I think you are bitten by mysql performance.
> 
> /Björn

No there are similar results when exporting the schema from MySQL to Postgres.  The fact is there is only so much you can logically do in a single query.  You  can index and join and filter and sort etc.  But when push comes to shove you need to implement business logic somewhere.

The tables are simple and not at all complex.  There are two tables involved a transactions table and an order table.

The transactions table reflect credits/debits against accounts, whereas the orders table represents a literal "filled out order form".

It can't really be simplified much more than that.

Re: Ada and PL/pgSQL ?

[björn lundin]

Den fredagen den 9:e maj 2014 kl. 21:59:30 UTC+2 skrev sdale...@gmail.com:
> 
>> I think you are bitten by mysql performance.
> 
> No there are similar results when exporting the schema from MySQL to >Postgres.  The fact is there is only so much you can logically do in a single >query.  You  can index and join and filter and sort etc.  But when push comes >to shove you need to implement business logic somewhere.

Yes. And for me, the correct place is in a separate layer - in Ada.
That also makes things a lot easier if you want to change db-engine again.
We are into the 5th db since the late 80's
(Mimer, Oracle RDB, Oracle, Sql-server, Postgresql)
 
> The tables are simple and not at all complex.  There are two tables involved > a transactions table and an order table.

Given that and 
> The minimum execution time for an order was 30s.
> However it was climbing as high as 5mins. 

makes me wonder about the number of records you have in the db.
With these timings, it must be in the tens or hundreds of million records?

explain analyze of the queries after a vacuumdb --analyze would be interesting to see.
But that is for Postgres performance mail list, not for this one.

Executing logic will take place in a postgres process.
Having your logic i c or ada executing locally on that server -
does that make a difference in the figures?
 
> The transactions table reflect credits/debits against accounts, whereas the orders table represents a literal "filled out order form".
> 
> It can't really be simplified much more than that.

No. And that is why I am curios about the amount of data.
The timings just seems very strange.

/Björn

Re: Ada and PL/pgSQL ?

[Dirk Heinrichs]

sdalemorrey@gmail.com wrote:

> 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.
> 
> 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?

While I mostly agree with what Björn has already written, I would 
nevertheless like to tell you how it could be done.

In PostgreSQL, you can not only write Stored Procedures in 
PL/yourFavoriteLanguage (I prefer Perl, btw.), but you can also import 
procedures written in a compiled language such as C[1]. The documentation 
also states "or a language that can be made compatible with C, such as C++", 
which could as well include Ada.

And then, there's also the SPI[2] (Server Programming Interface). With an 
Ada binding to the SPI (which to my knowlegde does not exist, yet), you 
would be able to write PostgreSQL functions in Ada.

However, as Björn wrote, that would make your project depend on PostgreSQL 
in a way you might not want. If you want to stay independent of a specific 
RDBMS, you should look into something like APQ[3], SOCI[4] or GnatColl's 
database interface[5]

[1]: http://www.postgresql.org/docs/9.3/static/xfunc-c.html
[2]: http://www.postgresql.org/docs/9.3/static/spi.html
[3]: http://sourceforge.net/projects/apq/
[4]: http://soci.sourceforge.net/doc/3.2/languages/ada/index.html
[5]: http://docs.adacore.com/gnatcoll-docs/sql.html

Bye...

	Dirk
-- 
Dirk Heinrichs <dirk.heinrichs@altum.de>
Tel: +49 (0)2471 209385 | Mobil: +49 (0)176 34473913
GPG Public Key CB614542 | Jabber: dirk.heinrichs@altum.de
Sichere Internetkommunikation: http://www.retroshare.org

Re: Ada and PL/pgSQL ?

[Mike H]

Please forgive the ramblings of this 80-year-old who is stuck in an 
Ada95 time warp but as I understand it, the steps in a transaction boil 
down to

A: An enquiry concerning a proposed transaction (in outline form)
B1: A detailed check that the transaction is compliant with trading 
rules, etc.
B2: Confirmation that funds are available (and, if so, locking them)
C: Completion or abandonment (or time-out?) of the transaction

Could B1 and B2 be concurrent tasks that on completion, rendezvous with 
C?
-- 
The thing I like best about the Internet is that no one
knows that, in reality, I am just another old dog!
Mike

Re: Ada and PL/pgSQL ?

[björn lundin]

Den fredagen den 9:e maj 2014 kl. 18:26:22 UTC+2 skrev sdale...@gmail.com:

>With a majority of the slow down occuring from table locks on db transactions.

Thinking a bit of this.
If you have the same figures with Postgres, as you state,
then I wonder if your code is explicitly locking the tables?

As in issuing a statement like 'LOCK TABLE XYZ' or
'SELECT XYZ for UPDATE'

That is killing performance in any db.

If so, and you migrate that principle, you will always have performance issues.
If so, you'll need to use other mechanisms to ensure your data is untampered with, as in having versions/timestamps on each row, and check that the row-version you first read, is the same at write (essentially using a where-clause having 'where version=originally_read_version' in it. 

And if rows affected is 0, rollback and start-over, given you wanted to update 1 row). Scoop of transactions becomes very important here

Basically, what my posts are trying to say is that
the timings you describe will likely not get better by putting your logic in the database. Putting you logic in Ada, will however likely make your maintenance burden a lot easier

/Björn