Thread: 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!
PostgreSQL is definitely not the problem.
Also, we did not have troubles with ODBC on PostgreSQL's side.
The problem is that Microsoft applications seem to be in favour of collapsing when sending more than just a few records over an ODBC connection.
I have heard that it works perfectly well for OpenOffice but we had real problems with MS products; especially when working with slow connections.
As I always say; the "core" of an MS application is a "dump" ...
Also, we did not have troubles with ODBC on PostgreSQL's side.
The problem is that Microsoft applications seem to be in favour of collapsing when sending more than just a few records over an ODBC connection.
I have heard that it works perfectly well for OpenOffice but we had real problems with MS products; especially when working with slow connections.
As I always say; the "core" of an MS application is a "dump" ...
Hans
-- Cybertec Geschwinde &. Schoenig Ludo-Hartmannplatz 1/14; A-1160 Wien Tel.: +43/1/913 68 09 oder +43/664/233 90 75 URL: www.cybertec.at oder http://postgres.cybertec.at
Hans, Thanks but I'm not sure you understood what I was saying. I'm considering switching to Postgresql from VB using Access database which is very unreliable. From what you're saying MS applications can cause problems that have nothing to do with Postgresql. In that event, I guess any SQL server with MS clients will have the same problem. Am I right? If an MS app crashes, that's one thing. I'm more worried about what happens to a Postgresql server when it does. Does it corrupt the database? You're saying MS sends bogus records. I think that would be an issue with the ODBC driver. Is that very stable? My architecture allows me to cut MS out out of the loop to a large extent. Thanks Hans-Juergen Schoenig <hs@cybertec.at> wrote in message news:<3C6CBDC6.7ACE2341@cybertec.at>... > PostgreSQL is definitely not the problem. > Also, we did not have troubles with ODBC on PostgreSQL's side. > The problem is that Microsoft applications seem to be in favour of > collapsing when sending more than just a few records over an ODBC > connection. > I have heard that it works perfectly well for OpenOffice but we had real > problems with MS products; especially when working with slow connections. > > As I always say; the "core" of an MS application is a "dump" ... > > Hans > > -- > Cybertec Geschwinde &. Schoenig > Ludo-Hartmannplatz 1/14; A-1160 Wien > Tel.: +43/1/913 68 09 oder +43/664/233 90 75 > URL: www.cybertec.at oder http://postgres.cybertec.at > > > > --
> Anybody care to comment on VB/odbc connection to Linux/Postresql? > Is it fast? ODBC is anything but fast.... steve
Any true database server with a well written application will be far more resistant to data corruption than MS Access. Thisis true as a matter of course due to the data storage format Access uses (whatever it is) and the fact that they don'tuse transactions. People smarter than me on this list can tell you exactly why, all I know is that I have never hada data corruption problem in PostgreSQL, and I had many in Access. Ian A. Harding Programmer/Analyst II Tacoma-Pierce County Health Department (253) 798-3549 mailto: iharding@tpchd.org >>> Eel <eel@javabox.com> 02/15/02 08:24AM >>> Hans, Thanks but I'm not sure you understood what I was saying. I'm considering switching to Postgresql from VB using Access database which is very unreliable. From what you're saying MS applications can cause problems that have nothing to do with Postgresql. In that event, I guess any SQL server with MS clients will have the same problem. Am I right? If an MS app crashes, that's one thing. I'm more worried about what happens to a Postgresql server when it does. Does it corrupt the database? You're saying MS sends bogus records. I think that would be an issue with the ODBC driver. Is that very stable? My architecture allows me to cut MS out out of the loop to a large extent. Thanks Hans-Juergen Schoenig <hs@cybertec.at> wrote in message news:<3C6CBDC6.7ACE2341@cybertec.at>... > PostgreSQL is definitely not the problem. > Also, we did not have troubles with ODBC on PostgreSQL's side. > The problem is that Microsoft applications seem to be in favour of > collapsing when sending more than just a few records over an ODBC > connection. > I have heard that it works perfectly well for OpenOffice but we had real > problems with MS products; especially when working with slow connections. > > As I always say; the "core" of an MS application is a "dump" ... > > Hans > > -- > Cybertec Geschwinde &. Schoenig > Ludo-Hartmannplatz 1/14; A-1160 Wien > Tel.: +43/1/913 68 09 oder +43/664/233 90 75 > URL: www.cybertec.at oder http://postgres.cybertec.at > > > > -- ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
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
> 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. Hi, I was in the same situation two years ago. PostgreSQL + ODBC is a very good choice. Maybe you should download pgAdmin2, the GUI interface of PostgreSQL from http://pgadmi.postgresql.org. It will be usefull. The only problem I encoutered with Access was when using $M Jet, which is not reliable. MS Access should be used with direct ODBC calls or better ADODB. There is a good tutorial called Using PostgreSQL Access howto. Search for it in Google. Cheers, Jean-Michel POURE