Re: PG rules! (RULES being the word ;->) - Mailing list pgsql-general

At the very least you should use the SQL 92 features
that PostgreSQL has.  There is no reason not to use
Foreign keys and constraints, for example.  Any
commercial database that is an "upgrade" to PostgreSQL
has these features (generally implemented in a very
similar fashion), and since PostgreSQL is free, there
is no reason to be downwards compatible.

If you design your middleware so that it is compatible
with something like MySQL then you really are creating
too much work for yourself.  Maintaining referential
integrity is hard, and PostgreSQL gives you all the
necessary tools to do it simply and inexpensively.
Writing this type of functionality in your own
application is a recipe for trouble.  There is little
chance that you are going to do as good a job as the
PostgreSQL team, and implementing this type of stuff
takes away time from implementing the stuff that
actually matters.

Besides, eventually your users are going to want to
circumvent your middleware for one reason or another.
PostgreSQL's constraints and referential integrity
makes this sort of access safe.

Personally, I am like Dr. Evil.  The databases that I
could afford to port my application to would all be
either a step backwards, or at best a lateral move to
a database with similar functionality.  I can't afford
Oracle, and don't really need or want the features
that it has above and beyond PostgreSQL.  Because of
this once PostgreSQL gains the ability to return
result sets from functions I will probably move *more*
of my applications functionality directly into
PostgreSQL.  That way I could use this logic not only
from my primary application, but also from psql,
one-off Python scripts, MS Access front-ends, etc.

Jason

--- "Dr. Evil" <drevil@sidereal.kz> wrote:
> > But watch out the discussion "Application Design
> and PostgreSQL"
> > these Days in the list. I thought your way.
> Thought it is pretty cool
> > to have all logic inside the database, but it
> seems it isnt!
>
> Don't listen to them!  Every case is different.
>
> > How do you do your error checking?
> > You won't be vendor independent!
>
> I throw myself to the mercy of Postgres!
>
> > How do you check if your php sends a '1' instead
> of 'true' to the
> > database. I think you can only catch this error
> with a midllerware.
> > switching the databse is impossible.
>
> I am married to Postgres!
>
> > im just thinking about the same problem at this
> time and i am
> > convinced by others to put the business logic in
> the middleware and
> > just use SQL to store simple tables.
>
> I am familiar with that argument.  It's a good point
> in some
> instances, but it isn't gospel by any means.
>
> The question is, how much portability do you really
> need?  In my case,
> I know that I'm working on a domain name registry.
> Even being
> optimistic, let's say we get 100k domains
> registered.  A 100k row
> table is well within PG's capabilities on
> appropriate hardware/OS
> (1.2mhz AMD, 2gig RAM, FreeBSD should do it).  At
> the same time, I
> know that an Oracle license is completely out of our
> budget for the
> foreseeable future, so me worrying about being able
> to run my
> application on Oracle is like me worrying about
> which color Rolls
> Royce looks best.
>
> So, can you really say that this particular
> application needs to be
> portable at all?
>
> What kind of application really needs to be
> portable?  If you think
> it's going to be small (in terms of data set size
> and transaction
> volume), then go with PG and commit to it.  If you
> think it's going to
> be big, then why not get the budget and do it on
> Oracle or DB2 to
> begin with?
>
> I'm not saying that portability has no value, but
> you should always do
> a cost/benefit analysis of portability (and all the
> other major design
> requirements), and try to make some intelligent
> assumptions about the
> future, to see whether it's worth investing the
> substantial extra time
> and money in writing middleware.  Perhaps some kind
> of accounting
> database at a rapidly growing company would be a
> good candidate for a
> highly portable design, because you can't afford
> Oracle right now, but
> you are pretty sure you'll need the extra power
> later.
>
> I've decided that my paricular application doesn't
> need portability,
> so I'm going to make full use of all the great
> features of PG to
> ensure data integrity and the highest possible
> stability.  If our
> marketing projections are wrong, and we get 1mil
> domains registered in
> the first month, then certainly we will need to make
> some changes, but
> we will also have a lot more budget power.  We can
> always get by by
> upgrading hardware and other tweaks.
>
> Now of course if I were a Java consultant, getting
> paid $100/hour to
> write Java middleware, I might feel differently
> about all this...
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html


__________________________________________________
Do You Yahoo!?
Get personalized email addresses from Yahoo! Mail
http://personal.mail.yahoo.com/