Thread: some questions about PostgreSQL in general

hello everybody,

i'm posting this on the novice group, because after about a week of
lurking, pgsql-general looks to me more like a pgsql-deep-magic :).
anyway, any reactions / comments / tips are welcome!

my company uses at the moment a Clipper application with foxpro tables,
homemade, it's about 5 years old and has constantly been in development
(innovation, extension, bugfixes). the db is rather small (500mb), but
the functionality is complex (in terms of relations, also it's client-
server with one central database + read-write replica's to several
notebooks)

we have decided to go for a complete new platform and after looking at
Oracle (too expensive) and some existing solutions (not flexible
enough) we want to go for a *nix (Redhat, Debian or OpenBSD, testing
will be done on Redhat 7.2, maybe when Woody awakens i'll switch) and a
postgreSQL db. the front-end isn't decided yet, but we have decided to
drop the replication and let the notebooks make a dial-in connection,
so PHP or JDBC would seem the obvious choice. the database should also
be able to either hold a lot of office documents as BLOBs or contain
links to the files themselves, which are located on a netware 4.11
server. the client machines are win95 workstations, but HTTP access
should also be possible.

the db is the core of our business so it should be very stable and also
very secure, because we work with medical data.

my guess would be that postgreSQL is ready for the job, is this
correct? also i would be much obliged for any info about which version
i should choose (the stable version or the developer version) and
generally about which pitfalls i'm likely to encounter, which frontends
are "mature", etc.?

would anyone like to share experiences?

thanks and a happy new year to you all!

--
Jules Alberts

> hello everybody,

Hello, Jules.

> my company uses at the moment a Clipper application with foxpro
> tables,
> homemade, it's about 5 years old and has constantly been in
> development
> (innovation, extension, bugfixes). the db is rather small (500mb),
> but
> the functionality is complex (in terms of relations, also it's
> client-
> server with one central database + read-write replica's to several
> notebooks)

On Clipper/Foxpro?  Must be a full-time job to keep running ...

> we have decided to go for a complete new platform and after looking
> at
> Oracle (too expensive) and some existing solutions (not flexible
> enough) we want to go for a *nix (Redhat, Debian or OpenBSD, testing
> will be done on Redhat 7.2, maybe when Woody awakens i'll switch) and
> a
> postgreSQL db. the front-end isn't decided yet, but we have decided
> to
> drop the replication and let the notebooks make a dial-in connection,
> so PHP or JDBC would seem the obvious choice. the database should
> also
> be able to either hold a lot of office documents as BLOBs or contain

I'd reccommend links to files for data integrity reasons.  Postgres is
capable of either.

> links to the files themselves, which are located on a netware 4.11
> server. the client machines are win95 workstations, but HTTP access
> should also be possible.

Darn!  Too bad you're not in my area.  This is exactly the kind of work
my company does ...

> the db is the core of our business so it should be very stable and
> also
> very secure, because we work with medical data.

Keep in mind that DB-level (through SQL) security is only about 5% of
overall security.  Network security and user management play a much
lareger role.

> my guess would be that postgreSQL is ready for the job, is this
> correct?

Yes.

> also i would be much obliged for any info about which
> version
> i should choose (the stable version or the developer version)

Start playing with 7.1.3 now, but plan your application for 7.2.  It
will be ready before you finish your application.  And, frankly, if you
are used to working with MS-DOS/Windows apps, the stability you get out
of a Postgres "beta" version (such as 7.2 RC1) is comparable to most
MS/Windows software "release" versions.

> and
> generally about which pitfalls i'm likely to encounter,

DB Application Design tip #1:  Never, ever, ever, skimp on the
application planning stage.  As a rule, every hour you cut from
necessary planning meetings and papers will equal 3-6 hours spent
debugging and/or adding "enhancements".

For more application design thoughts, Steve McConnell's Software Project
Survival Guide is good.  Also see the book reviews at:
http://techdocs.postgresql.org/bookreviews.php

As for Postgres-specific pitfalls, I can't think of any.  we have a
pretty robust RDBMS platform at this point.

> which
> frontends
> are "mature", etc.?

Based on your requirements above, I would reccomend Java/J2EE rather
than PHP.  PHP is not designed for the same depth/complexity of security
and distributed access as Java or C++.  However, if your development
budget is unreasonably constrained, you may have to choose rapid
development (PHP, Kylix/Delphi, or Python/Zope) over robustness (Java or
C++ plus XML).  Postges works with all of these and a few more (C#,
anyone?) but as always, you must trade off your "wish list" vs. your
budget.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco

On 2 Jan 2002 at 10:59, Josh Berkus wrote:

Hi Josh,

<lots of snips>

> On Clipper/Foxpro?  Must be a full-time job to keep running ...

well, once it was up and running, most of the work was innovation. i
managed to get as much support out of the way as i could. i actually
enjoyed writing the app, although i had to invent lots of wheels to
workaround the no-trigger no-constraint no-relation foxpro tables.

of course with postgresql having these things, life will become much
easier on this level. on another level (security, learning a new db +
language) there is enough to keep me busy for a year or two :)

> I'd reccommend links to files for data integrity reasons.  Postgres is
> capable of either.

OK. Frank Bax said the same, i guess we'll go for links.

> Darn!  Too bad you're not in my area.  This is exactly the kind of work
> my company does ...

yes, that's too bad indeed. support is hard to find over here, we're
located in the southern part of the netherlands.

> > the db is the core of our business so it should be very stable and
> > also very secure, because we work with medical data.
>
> Keep in mind that DB-level (through SQL) security is only about 5% of
> overall security.  Network security and user management play a much
> lareger role.

yes, i know. i don't know much about firewalls, SSL, etc. (my network
background is mostly NDS, netware, IPX), the best decision is IMO to
consult an external company for the setup and have another one audit
for security. that gives me more time to focus on the central issues
like programming, db design etc.

> Yes.

great!

> Start playing with 7.1.3 now, but plan your application for 7.2.  It
> will be ready before you finish your application.  And, frankly, if you
> are used to working with MS-DOS/Windows apps, the stability you get out
> of a Postgres "beta" version (such as 7.2 RC1) is comparable to most
> MS/Windows software "release" versions.

well, i guess i'm lucky: we don't use windows apps for business
critical apps. the main app was in clipper 5.3b which is very stable.
the reason we dump it, is that it's language oriented, not db oriented,
and it's no longer developed.

> DB Application Design tip #1:  Never, ever, ever, skimp on the
> application planning stage.  As a rule, every hour you cut from
> necessary planning meetings and papers will equal 3-6 hours spent
> debugging and/or adding "enhancements".

OK, i have (and already had) that etched into my mind. we had a 2 year
pre-build stadium in which we (management team + management) evaluated
our company and what we expect from our systems.

> For more application design thoughts, Steve McConnell's Software Project
> Survival Guide is good.  Also see the book reviews at:
> http://techdocs.postgresql.org/bookreviews.php

thanks, i'll have a look.

> As for Postgres-specific pitfalls, I can't think of any.  we have a
> pretty robust RDBMS platform at this point.

after some lurking on the postgresql mailinglists there seem to be a
few items in postgresql which should be avoided, like the array
datatype and BLOBs. i guess it would be good to keep the design of the
db as straightforward and elegant as possible and avoid more exotic
features...

> > which
> > frontends
> > are "mature", etc.?
>
> Based on your requirements above, I would reccomend Java/J2EE rather
> than PHP.  PHP is not designed for the same depth/complexity of security
> and distributed access as Java or C++.  However, if your development
> budget is unreasonably constrained, you may have to choose rapid
> development (PHP, Kylix/Delphi, or Python/Zope) over robustness (Java or
> C++ plus XML).  Postges works with all of these and a few more (C#,
> anyone?) but as always, you must trade off your "wish list" vs. your
> budget.

budgest is of course always an issue, but not in terms of buying
software. we have decided to go "free-beer-plus-free-speech" all the
way. so not only for the dbms, but also for the development
environment.

as for the development time, maybe it's a good idea to build a more
sophisticated front-end for the power users in Java, and a quick-and-
dirty HTML frontend with PHP. no offence meant, maybe in the end it
will be PHP only.

--
Jules Alberts

On 2 Jan 2002 at 21:06, Frank Bax wrote:
<snip>
> I've got a little script which you can use to create psql tables
> directly from the foxpro files (see below).  Just ftp the files to *nix
> server and run script.  Requires DBI abstraction package.

thanks, but i think the import will be a clipper applet which will
export all data to text, and a COPY to import the lot into postgresql.
i can't avoid a step like this because the data in the current tables
are encrypted.

> >we have decided to go for a complete new platform and after looking
>
> I use OpenBSD & PHP; no complaints.  OpenBSD packages system makes
> php/pgsql install very easy.

good to hear that openbsd is an option, i hear great things about it's
security. OTOH linux is more mainstream, so it should be easier to get
support. this choice may become a difficult one...

> >let the notebooks make a dial-in connection
>
> You mean dial-up to internet, right?  And come into *nix server via it's
> internet connection.

yes. and i know, there's _lots_ of security issues involved

> >the database should also be able to either hold a lot of office
> >documents.
>
> I'd go with links to files.  I once read something to the effect "if you
> don't index or search on it, don't include it in db".  You could install
> SAMBA on *nix box allowing you to "map" drives to SAMBA shares.  SAMBA
> implements the protocol behind Windows file-sharing.  If you need to
> search content of these documents, you might consider extracting
> readable text out of document and only put that in table.

OK, links it is.

another question i didn't think about in my original posting: how
flexible is postgresql? at the moment it's quite easy to change the
structure of the tables. i guess that in postgresql this may be a
little problematic.

thanks for reacting and HAND

--
Jules Alberts

At 11:45 AM 1/3/02 +0100, Jules Alberts wrote:
>another question i didn't think about in my original posting: how
>flexible is postgresql? at the moment it's quite easy to change the
>structure of the tables. i guess that in postgresql this may be a
>little problematic.

Making changes to pg tables isn't quite as easy as with DBU (I've also done
Clipper in the past), but it's not bad.  Last I heard there was no support
for DROP COLUMN,
    http://www2.ca.postgresql.org/docs/faq-english.html#4.5
Follow a similar process when making changes (like datatype) of a column.

Frank

At 11:45 AM 1/3/02 +0100, Jules Alberts wrote:
>another question i didn't think about in my original posting: how
>flexible is postgresql? at the moment it's quite easy to change the
>structure of the tables. i guess that in postgresql this may be a
>little problematic.

Making changes to pg tables isn't quite as easy as with DBU (I've also done
Clipper in the past), but it's not bad.  Last I heard there was no support
for DROP COLUMN,
    http://www2.ca.postgresql.org/docs/faq-english.html#4.5
Follow a similar process when making changes (like datatype) of a column.

Frank

Jules,

> > Darn!  Too bad you're not in my area.  This is exactly the kind of
> work
> > my company does ...
>
> yes, that's too bad indeed. support is hard to find over here, we're
> located in the southern part of the netherlands.

Well, if you get desperate, we could always set up some kind of
online-only support thing.  Possibly in coordination with PostgreSQL
Inc.  I'm not sure how their experience with Netware/Firewalls rates.

> yes, i know. i don't know much about firewalls, SSL, etc. (my network
> background is mostly NDS, netware, IPX), the best decision is IMO to
> consult an external company for the setup and have another one audit
> for security. that gives me more time to focus on the central issues
> like programming, db design etc.

Yes.  And you're more likely to find a network security company in the
Netherlands than a PostgreSQL or PHP one.

> well, i guess i'm lucky: we don't use windows apps for business
> critical apps. the main app was in clipper 5.3b which is very stable.
> the reason we dump it, is that it's language oriented, not db
> oriented,
> and it's no longer developed.

And there's that pesky Y2K bug.  :-)

> OK, i have (and already had) that etched into my mind. we had a 2
> year
> pre-build stadium in which we (management team + management)
> evaluated
> our company and what we expect from our systems.

This sort of ties in with your question about table re-design.  There
are some limitations to altering existing tables ... for example,
dropping a column requires a table re-build  (adding or re-naming a
column, and adding constraints, are permitted).    However, in my
experience, significant changes to the main data tables in a running
system usually require redesign of a number of components, so the table
re-build is less arduous than, say, checking all of your scripts for
references to the dropped column.

Overall, it's better to get the data design nailed down to the 90% level
before you write any SQL.  And this is true regardless of DB system.

> after some lurking on the postgresql mailinglists there seem to be a
> few items in postgresql which should be avoided, like the array
> datatype and BLOBs. i guess it would be good to keep the design of
> the
> db as straightforward and elegant as possible and avoid more exotic
> features...

Yes, there's chronic problems throughout the industry with the
"advanced" data types (MS SQL Server has much worse problems with BLOBs
than Postgres).  Partly it's that these types were not in the SQL 92
standard, and some (BLOBs) depend heavily on the host filesystem, so
that implementation is RDBMS- and OS-specific.  Some DB theorists,
notably C.J. Date and F. Pascal, are opposed to the use of non-atomic
data types in any form and their arguments have significant merit.

> as for the development time, maybe it's a good idea to build a more
> sophisticated front-end for the power users in Java, and a quick-and-
> dirty HTML frontend with PHP. no offence meant, maybe in the end it
> will be PHP only.

Ah.  I did not make myself clear.  In the PHP-vs-Java comparison, you
have two different layer structures:

Web Server
     |
  PHP + HTML
     |
Postgres Functions, Views and Triggers
     |
Data tables

or:

Web Server
          |
  JSP Engine + HTML
          |
J2EE or Java Middleware
          |
Postgres Functions, Views, and Triggers (optional)
          |
Data Tables

The difference here, between PHP and Java, is that Java includes an
extra, robust middleware layer capable of sophisticated, modular
business logic and data management across several servers, if desired.
Also, display/interface programming (JSP) is seperated from business
logic (J2EE or java libraries) making maintenence easier.  The
PHP/Postgres Function structure, while quite economical for rapid
development of small database applications, lacks this robustness and
flexibility.  It's a development cost vs. long-term value argument, and
I've done projects on both sides of the argument.

BTW, you can do the same things using Perl::DBI as you can with Java.
I'd also love to hear from a Python fan, if we have any on the list.

-Josh Berkus

______AGLIO DATABASE SOLUTIONS___________________________
                                       Josh Berkus
  Complete information technology      josh@agliodbs.com
   and data management solutions       (415) 565-7293
  for law firms, small businesses        fax 621-2533
    and non-profit organizations.      San Francisco