Re: [GENERAL] RE: postgresql and web transactions - Mailing list pgsql-general

At 10:10 AM 17-02-2000 +0100, Nemeth Miklos wrote:
>> And if this isn't too inflamatory a question, where does postgresql stand
>> in comparison to MySql and DB2? (both of which aren't free I believe).
>>
>MySQL does NOT support transaction, which is indispensable for a real
>DBMS used as the primary data storage system for reliable/consistent
>business transactions.
>However, MySQL is the fastest RDBMS ever seen, and perfect for data
>warehouses and DSS and EIS purposes, as well as for
>query databases for web sites.

MySQL is fast! As a data point for one of my webapps with MySQL- 30-50
hits/sec, with Postgres 7-8 hits/sec. Quite a significant difference. Heh
one of my colleagues PHP + MySQL pages does 50+ hits/sec too. On just a
workstation level machine...

However, Postgres has triggers, transactions, and it's free and Open
Source. MySQL is USD200 for commercial use and AFAIK not quite Open Source
(at least not the latest versions, the older versions were made Open Source
I think).

About transactions, I find that they're not that useful in a typical web
app, because
1) Many web apps are simple, and don't need transactions, or have simple
work arounds.
2) And when you do need transactions, HTTP is not very state friendly, so
unless you do something special your database level transaction is
typically going to last only one page, so if your real life transaction
spans across multiple pages, the database level transaction system isn't
going to help much.

Anyone have any ideas on how to get around that for Postgres? Coz if we
have to do transactions at the application level then it might actually be
better to just go MySQL, than to do transactions at both app and database
level. Hmm any ideas on how to do MVCC at app level? ;).

Session ID column and a committed boolean column? Or create a table for
each session (ouch!), is that what temp tables are for? Do temp tables
persist?

Or is writing a 'session' server the way to do it?
e.g.
webserver->cgi/app server->session server->database server

The session server is given a web session ID and SQL, and uses it to talk
to the database. It also times out connections.

Trouble is you don't know when users are leaving your site, so you could
have tons of simultaneous database connections. Probably only doable or
useful for niche applications- high concurrency limited user apps.

Any better ideas?

BTW: Somehow we seem to be going DB2 tho (dunno exactly why, but at least
it's tons cheaper than Oracle, but infinitely more expensive than Postgres
;) ). Don't know much about DB2..

Cheerio,

Link.