Re: [HACKERS] Most Advanced

Lamar Owen wrote:
> 
> Bruce Momjian wrote:
> > > Until InterBase is released open source, it remains to be seen how
> > > advanced of an open source database it will be.
> 
> > Is Interbase any good?  I never heard of them much.  Sounds like it is a
> > PC database like dbase, right?  They don't scale very well.

IIRC it was missing shared cache between backends. 
> It seems to be an interesting system. To summarize its features (going
> quickly against the PDF referenced above):

I try putting a + or - based on weather PG has it (please correct me)

+> Client-server architecture;
+> SQL parser in server;
+> Server side triggers;
+> Stored Procedures;
+> User-defined functions;
+> Event alerters (that notify clients of database changes);

Actually or LISTEN/NOTIFY would could use some improvement, it would be 
much more powerful if it allowed even a single argument to be passed.

+> Declarative Referential Integrity with cascading operations;

Will be in 7.0 ?

-> Domains and contstraints extend SQL types;
?> Automatic two-phase commit to stabilize distributed mulit-database  transactions;
+> Cross-platform scalability and interoperability;
+> Small footprint (3MB disk for minimum, ~20MB for full install)

PG should be about the same size

+> Up to 150 concurrent clients;

What is the upper limit for PG ?

+> Y2K correct;
+> Implements entry level SQL-92, plus many intermediate level features and  selected features from the full level;
-> InterBase Corp has voting member status in the ANSI SQL standards  committee, X3H2;

Is this the bunch of guys we often fondly remember for their SQL3 standard ?

-> SQL Roles for group-level security;

+> SQL-92 syntax for inner and outer JOIN clauses;

Will be in 7.0 ?

+> Views on tables and joins;
-> Select procedures (that return not a value, but a result set);

This requires a rewrite of the pl function API

+> Full transactional operation;
+> MultiGenerational Architecture (basically the same as our MVCC);
?> Row-level locking;

How are we doing here ?

?> Multiple concurrent transactions on a per-client basis -- each client can have multiple concurrent transactions;

If client==connection, then we don't have it, if we opened a connection per
trx we do

-> Distributed transactions -- a single transaction can be open against multiple databases, with a two-phase commit;

Support for multi-db is generally weak in PG. A single connction can work only 
with one db at a time

+> BLOBs;

We have LOs, but the implementation is nut usable for more than a few on 
most UNIX filesystems (we have one LO per file, all in the same directory with 
everything else)

+> Arrays (implemented as structured BLOBs);

But nut implemented as structured BLOBS ;)

+> BLOB filter functions (such as a JPEG to PNG translator);

Could be done easily, but not included in distribution at least.

+> Cost-analysis query optimization;
+> On Unix systems, the InterBase security can be integrated with OS security;
+> Internationalization support, including UNICODE;

Do we have UNICODE (or just several other MB charsets)?

-> Integration with Borland JBuilder;

No intgration but can be used from it

+> ODBC client;
-> Automatic garbage collection -- no vacuum;

Implementing it to be _fully_ automatic would make it very hard to 
re-introduce time travel.

We have it semi-automatic using psql -c "vacuum;" in cron ;-p

+> No preallocation of disk space required -- files up to 4GB in size, with  expansion through the use of secondary
files(similar to our  segmentation);
 
+> Full ACID compliance.
> 
> That's the short version.
> 
> I don't see stuff like:
+> Ability to use Tcl and Perl in stored procedural functions;
-> Object Relational in nature;

Maybe we should rephrase it to "Object Relational by ancestry".
We have very few OR features currently in working order, and 
probably won't before 7.1. Chris Bitmead has patches for making 
inherited tables work right (for SELECT,DELETE,UPDATE), but 
they won't probably be included in 7.0 as they change the behaviour 
of the only statment (SELECT) that is currently working to some extent
and some of the core developers seem to be dependent on the old 
behaviour, i.e using inheritance as a shortcut for including the 
same set of columns in an unrelated table. 
Also people were set back by SQL3 standard which pg should (?) follow 
to some extent at least, but which is incomprehensible when read 
directly and which can only be understood through the works 
of apostles ;)

+> Endlessly extensible for types, languages, functions, etc.  (I especially like that one).

Otoh, they have implemented DOMAINS, which allow much of simpler types to 
be done at SQL level. They won't probably be indexable.

-------------------
Hannu