This is in no way a complete answer, but I'll do what I can. Also note
that I haven't used MySQL in awhile and endorse PostgreSQL whenever I
can.
On Wed, Oct 09, 2002 at 01:42:28AM +0800, Michael Peligro wrote:
> 3. Can a MySQL or PostgreSQL database grow from 500kb to gigabytes
> without any administrative conversion and maintenance? Progress
> database is a deploy-once-then-forget type of database. It grows to
> any size without needing any administrative tweaking at all.
I do not yet manage very large databases, but have noticed that
PostgreSQL on Debian does daily runs of a maintenance script that
analyzes all indices and optimizes them as necessary to keep indexed
queries running at optimum.
> 4. Does MySQL and PostgreSQL support database triggers? Stored
> procedures?
PostgreSQL supports both. Stored procedures may either be in PL/pgSQL,
PL/Tcl, PL/Perl or PL/Python.
> 5. Does MySQL and PostgreSQL enable before-imaging and after-imaging?
> Are there any equivalent technologies that ensures
> transaction/database integrity in these databases?
>From the PostgreSQL documentation:
Unlike most other database systems which use locks for concurrency
control, PostgreSQL maintains data consistency by using a
multiversion model. This means that while querying a database each
transaction sees a snapshot of data (a database version) as it was
some time ago, regardless of the current state of the underlying
data. This protects the transaction from viewing inconsistent data
that could be caused by (other) concurrent transaction updates on
the same data rows, providing transaction isolation for each
database session.
The main difference between multiversion and lock models is that in
MVCC locks acquired for querying (reading) data don't conflict with
locks acquired for writing data and so reading never blocks writing
and writing never blocks reading.
> This is also a very important feature. In Progress, if the machine
> accidentally shuts down and the client has open transactions, Progress
> will automatically roll-back the uncommitted transactions upon startup
> through before-imaging. In this way, database write/update integrity
> is taken cared off automatically without scripting lengthy resolution
> commands.
PostgreSQL is supposed to do this, but have not personally tested
killing our server while in the middle of a transaction. From the
documentation:
Write Ahead Logging (WAL) is a standard approach to transaction
logging. Its detailed description may be found in most (if not all)
books about transaction processing. Briefly, WAL's central concept
is that changes to data files (where tables and indexes reside)
must be written only after those changes have been logged - that
is, when log records have been flushed to permanent storage. When
we follow this procedure, we do not need to flush data pages to
disk on every transaction commit, because we know that in the event
of a crash we will be able to recover the database using the log:
any changes that have not been applied to the data pages will first
be redone from the log records (this is roll-forward recovery, also
known as REDO) and then changes made by uncommitted transactions
will be removed from the data pages (roll-backward recovery -
UNDO).
> 7. Does MySQL and PostgreSQL support multiple database connections?
A resounding yes for both.
> 8. Can MySQL and PostgreSQL store BLOBS (binary, large objects)?
A resounding yes for both. With PostgreSQL you can encode your data
using something like base64 and then store it in a TEXT field, which
allows for varying lengths with no hard upper limit. The actual limit is
a byproduct of the current version's row size limit, which I think is
2GB at the moment. Alternatively there's the BYTEA type, which is
similar to the TEXT type but is null-safe. There's a third solution
which I run across while seeing how to store files using the database
with Perl. It created a file descriptor, wrote to it, then stored the
new OID in a table (an OID is a long integer I believe).
--> Jijo
--
Federico Sevilla III : http://jijo.free.net.ph
Network Administrator : The Leather Collection, Inc.
GnuPG Key ID : 0x93B746BE
_
Philippine Linux Users Group. Web site and archives at http://plug.linux.org.ph
To leave: send "unsubscribe" in the body to [EMAIL PROTECTED]
Fully Searchable Archives With Friendly Web Interface at http://marc.free.net.ph
To subscribe to the Linux Newbies' List: send "subscribe" in the body to
[EMAIL PROTECTED]
