Re: Postgresql/linux speed + reliability - Mailing list pgsql-general

Friends,

    I wrote this before re-reading the original email, which this doesn't
really answer.  I'm going to send it anyway, as I don't like to feel that
I've totally wasted my time.  Hopefully it will be useful to someone.  To
address one issue in the email I'm responding to, I suspect that using the
recordset object from VB would have similar problems to what I've
experienced with Access, but I haven't confirmed it.

    I've had some bad experiences with connecting to PostgreSQL from Access via
ODBC, but not bad enough that I don't use it every day.
    We've seen a couple of problems that re-occur.  All these problems come up
only when altering data via a linked access table, or through records
selected in a query.  I suspect that using Access forms, or using the
recordset objects to alter or insert data would have the same problems, so
I've avoided them.  I've never had a query that I ran produce bad data or
put bad data into the database.

    The problems I've seen are:

    1)  Often when saving a record access will lose it and will replace the
record with #DELETED# values in it's fields.  The data will be stored in the
database correctly however and reloading the table or query takes care of
the problem.  This is a known bug in Access and apparently shows up when
Access isn't smart enough to reload the record that was changed.
    2)  Sometimes when editing or adding to a table or query the record will be
displayed as a duplicate of the one directly above it.  If you go back and
edit this record you may end up with changes to the correct record, or to
the record that is being shown as a duplicate.  I don't know what causes
this, but it can result in bad data in the database.  We get around it by
trying to not directly edit tables or queries through access, or by being
extremely careful when we do.
    3)  There are some changes to our data structure in PostgreSQL that we
needed to make to ensure that it worked well with Access.  This includes
only using Text types when it will contain things that we won't want to sort
or use in joins, as Access refuses to do that.  To get around this we use
varchar(255) types for fields we know will contain short data.  Access is
happiest when the tables that it's linking to contain serial id fields that
are set as primary keys.  This reduces the problems of 1 and 2 above, but
doesn't eliminate them.

    To conclude, in my experience if you just manipulate data using insert and
update queries and avoid using the spreadsheet like table/query interfaces
to change data everything should be rock solid, and even the spreadsheet
like interfaces can be used if you're careful and watch what it's doing.

Thanks,
Peter Darley

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Eel
Sent: Thursday, February 14, 2002 4:36 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Postgresql/linux speed + reliability


Hi,
  I was almost finished with a VB/MS-access app and went to install on
the client's site.  Started having horrible (once a day) database
corruption problems.  I'm recommending that we switch to
Linux/Postgresql.  It's been 100% reliable and fast for me on several
web sites that I manage.

  Anybody care to comment on VB/odbc connection to Linux/Postresql?
Is it fast?  Is it reliable?  I'm looking at about 8 users hitting the
database pretty hard for 2 shifts a day.  Also have a barcode reader
polling app that does updates about 3 times a second.  Is it going to
work?


Thanks!

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org