Open ACS Porting Oracle Applications to Postgre SQL

Open. ACS: Porting Oracle Applications to Postgre. SQL Ben Adida ben@openforce. net

Using an Enterprise-Ready Open-Source RDBMS Ben Adida ben@openforce. net

Context • Building a multi-user, networked application • Open-Source hackers have ignored the RDBMS for too long! • The Next Open-Source Challenge Can Postgre. SQL replace Oracle?

Outline • Open-Source and RDBMSs • Oracle & Postgre. SQL comparison – SQL support – Maintenance – Advanced Features • Building a DB-independent App • Open. ACS

Data in Networked Apps • Multiple users accessing data – Read & Write – Contention! • Multiple means of access – Web server – Command line – Automated batch scripts

Data Abstraction • Assurances made to application • Understanding Failure: – Sometimes, you must fail – If you fail, admit it and fail gracefully • Understanding Relevancy: – Sometimes certain data is irrelevant

ACID Abstraction for reliable data storage: • Atomicity • Consistency • Isolation • Durability

Atomicity • All or nothing • Much more than locking!

Consistency • Invariants - Consistent States • Complex Data Invariants – Audit Tables – Aggregate Checks • Logic in the DB • Logic triggered within transactional control

Isolation • Partial work invisible to others • Levels of Isolation – Dirty Reads – Read Committed – Read Repeatable – Serializable

Durability • Once committed, a transaction is permanent -- no more rollbacks! • Point-In-Time Recovery • Impact on Backups

ACID in the DB • locking, rollback in the application layer? Abstraction! • Guarantees must be made no matter how data is accessed • Isolation mandates implementation at low-level

ACID is not Enough! • Naïve Implementation – single-threaded – global lock – full write to disk before commit. • Get Fired • Optimistic behavior

Does this Work? begin transaction; v_balance: = select balance from accounts where account_id=1; update accounts set balance= v_balance+20 where account_id=1; commit transaction;

This Does! begin transaction; v_balance: = select balance from accounts where account_id=1 for update; update accounts set balance= v_balance+20 where account_id=1; commit transaction;

Oracle - an Overview • ACID-compliant • Highly optimized for OLTP • Highly configurable • Very complex administration • OPS: the Promised Land

Postgre. SQL - an Overview • ACId-compliant • Highly optimized for OLTP • Not so configurable • Very simple administration • Replication in the works

SQL support • SQL 92 - the “standard” • No specifications for stored procedures or triggers! • Ambiguities • Oracle’isms – Connect By – Dates – SQL 92 alternatives

SQL support • Aggregates • Joins, outer joins • Subselects • Views • On-the-fly views

Stored Procedures Oracle • PL/SQL, Java • Triggers (statement and rowlevel) Postgre. SQL • PL/pg. SQL, PL/Perl, PL/Tcl, PL/C • Triggers (rowlevel only)

Transaction Engine • • • Oracle Overwrite Rollback Segment Transaction Log (Redo Log) Row-Level Locking Optimistic • • • Postgre. SQL No-overwrite Versioning Transaction Log (as of v 7. 1) Row-Level Locking Optimistic

Backup & Recovery Oracle Postgre. SQL • Logical Dump (not • Logical Dump consistent!) (consistent) • Incremental • No incremental dumps using dumps physical backup • No point-in-time • Point-in-time recovery yet recovery

Oracle Architecture Tablespace Rollback Segments

Postgre. SQL Architecture Field: price Field: name Field: price Val: 120 Val: 110 Val: “Tim” Val: 120 Version # 1659 1660 1661 ……

Parallelism Oracle • OPS • Very few users • However, a huge advantage in large installations Postgre. SQL • Asynchronous replication • Synchronous in the works • No complete solution yet

SQL Applications • Interface via ODBC/JDBC, DBI, etc. . • SQL statements – Many static, w/ bind variables Select * from users where user_id = : user_id – Some dynamic Select * from users where $date_predicate

Summary • Postgre. SQL can do 90% of what Oracle can do • You probably need 70% of what Oracle can do • Postgre. SQL is ready for many enterprise systems.

Open. ACS - History • 1995 - AOLserver/Tcl/Illustra • 1997 - AOLserver/Tcl/Oracle • 1999 - AOLserver/Tcl/Postgre. SQL • 2001 - v 4. x, multi-DB capable, modular

Open. ACS 4. x Architecture • Postgre. SQL data model and stored procedures • Queries stored in XML • Templates • AOLserver/Tcl logic

Custom Code Open. ACS Stack Open. ACS Presentation App Server RDBMS O/S All Free Software

Open. ACS 4. x Features • A common user table - collaboration • Registration/Login/Permissions • Discussion Forums • Ecommerce • Calendaring • Education! (MIT Sloan)

Open. ACS 4. x Demo

References • http: //postgresql. org • http: //openacs. org/doc/openacs/? ?