bomb = lock up most other users. some can still get in and view things. seems like only rarely does it lock up direct access through psql. ps ax shows some update and other selects waiting. you can kill those and it doesn't help. in fact, you can kill each process (or user) in postgres and it still won't free up. you have to restart posgresql, then get into the database and vacuum, and all is fine for another several hours. some days it will lock every hour or so. other days it will run all through the day (peak activity) and then only lock up once at night (only 2-3 users). using ps ax i can see the user name and then SELECT or UPDATE or INSERT, but then the other words to the right of those statements are cut off by the screen and i don't know how to make them "wrap" so i can see. with a short user name i can see SELECT waitin| (| represents right side of screen), so i assume that process is WAITING on another. but like i said, you can kill all processes and it still won't free up. checkpoints??? i have only a faint, sketchy idea of what you are talking about. and i must say that when i designed the database i didn't PURPOSEFULLY create or install or ? ANY checkpoints. . . filehandles?? not enough?? ditto previous paragraph. . . i did do a fairly descent job at normalization. ok, ok, so the city and state ARE in the same table as a zip code, but that is all too common. otherwise we are at the 3rd degree. our large "NOTES" field (internal notes we keep, or make, on each service call -- each service record in the database) are a series of varchar(255)'s. they are in a database: create table dispnotes (autoinv int4 not null, dispnote varchar(255), insertdt datetime not null default text 'now', insertby not null default current_user, constraint dispnotes pk primary key (autoinv, insertdt, insertby)); as someone types in "notes" on a service record, once they fill up one varchar(255) field, they just start another. when you pull up notes for a particular service record (autoinv), it pulls: select * from dispnotes where ssms.autoinv=dispnotes.autoinv and ssms.autoinv = 999999 order by insertdt; now our notes fields are unlimited in size -- or at least only limited by the HD size and not some tuple limitation (which was the case when the "notes" field was defined as TEXT), and the system doesn't have to juggle around large TEXT fields -- only much smaller and more efficient varchar(255)'s! no triggers, no rules, no functions defined in the database. only tables, primary key indexes (none created by create index), and a few sequences. even if postgres is only a "one at a time" sql server, doesn't the postmaster hold / queue requests?? so the net effect, although a little slower, is that all requests are eventually (and with a quick machine, quickly) met?? well, with all this info, any ideas on the locking problem? the file handles and checkpoints sound suspicious. we start postgres with '/usr/bin/postmaster -B 256 -S -D /ssms -i' -- if i started with (added) -d 9, where would that put the "mucho debug" info?? the company is now nearly wholly dependent on this database. even though we are able to recover quickly, and seemingly don't loose anything when we recover, "mesa not comfortable"! i'd like it to be as stable as our linux box that runs the internet connectivity / email / file sharing stuff! actually, the server os (rh 5.2 / mandrake) is very stable. i'm sure the postgres locking problem is some kind of rookie overlook (most unlike those we have here in chattanooga) that i have made. . . thanks for any help you can provide! jt +++++++++++++++++++++ Date: Fri, 25 Jun 1999 21:38:24 -0400 (EDT) From: Quark Physics <[EMAIL PROTECTED]> Subject: Re: update queries > We are running a new database on Postgres 6.4.2 with an Access front end. > We have maybe 10 - 15 people connected at any one time and when we try to > do any kind of update query, it bombs. It seems to be stable otherwise. > What could we be overlooking here? What do we need to be looking for? Define Bomb? Watch the processes on the Linux machine.. Run Postgres with -d 9 for mucho debug info... Most common SQL server problem: Not checkpointing often enough, not enough file handles... tables with unrealistic table structures. (you are storing notes fields as WHAT?) Bad normalization... Also remember Postgress is a 'one at a time' SQL server and is NOT multi-threading/multi-tasking system. You may have a client waiting for something in error and the server is not hung, it's just waiting to complete it's cycle. Sybase/Oracle and such allow you to define a query/connection activity timout error so that a single client does not hang and stop the system. Postgres is an excellent product and worth every penny they charge you for an more. Remember that ;) - --Mike--
