Обсуждение: Same question about PostgreSql
Hi group, We have a DSS system based on Informix IDS and number of OLTP systems based on Interbase. After we was running this system, we had have a unceasing serious problems with Interbase. 1) Massive inserts, updates, deletes is crashed database internal structure !!!. we have to make backup and restore every night ( backup and restore for 1G database is spent about 3 hours !!). 2) Garbage collection is crashed database internal structure !!!!!!!!!! 3) Backup sometimes can't process crashed database. 4) Restore frequently ( 50% of cases ) can't help ( because backup is not valid even if backup utility is finished without errors ( constraints violation and etc. ) !!!!) OLTP database has about 300 tables and 250 store procedures. Number of client connections 100 and less. Maximum database size is about 1.5 G - 3 G. Data from the OLTP systems periodically come into DSS database. Excuse me for long entering. Finally questions. How stable is PostgreSql ? I know, PostgreSql doesn't support 'prepare ' operation, is it successful to use one for OLTP databases ? Thanks for responds. Good luck. Sergey.
>How stable is PostgreSql ? >I know, PostgreSql doesn't support 'prepare ' operation, is it successful to >use one for OLTP databases ? Speaking from experience, I have personally stress tested Postgres under loads of over 512 persistent connections with our LXP application server with zero problems. J > > >Thanks for responds. > >Good luck. > >Sergey. > > > > > > > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > >http://www.postgresql.org/search.mpl > -- -- <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> <PROJECT>OpenDocs, LLC. - http://www.opendocs.org </PROJECT> <PROJECT>LinuxPorts - http://www.linuxports.com </PROJECT> <WEBMASTER>LDP - http://www.linuxdoc.org </WEBMASTER> -- Instead of asking why a piece of software is using "1970s technology," start asking why software is ignoring 30 years of accumulated wisdom. --
Sergey, > How stable is PostgreSql ? More than Interbase :-) More than MS SQL Server. Maybe less than Oracle or MySQL; it's open to debate. Futher, because everything in Postgresql is "open" -- source code, data files, system tables -- in the event of hardware or platform failure you can always retrieve your data with text and file editors. You may have trouble porting one thing, though ... at this time, PostgreSQL does not support full stored procedures. While the custom function feature can fulfill many stored procedure purposes, you will not be able to port your procedures over directly. Full stored procedures are planned for Postgres, but not for a couple of versions. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
> More than Interbase :-) More than MS SQL Server. Maybe less than > Oracle or MySQL; it's open to debate. Um. In my experience, PostgreSQL is more stable than MySQL. MySQL dies on me all the time, but I've never had it happen with PostgreSQL. (7.0.3, currently.) -- johs
Josh Berkus wrote: > Full stored procedures are planned for Postgres, but not for a couple of > versions. > I always thought that Pl/PgSQL functions where 'stored procedures'. Probably I'm wrong, but whats the difference between a Pl/PgSQL function and a stored procedure? (just curious). Regards, Nils Zonneveld
Nils, > I always thought that Pl/PgSQL functions where 'stored procedures'. > Probably I'm wrong, but whats the difference between a Pl/PgSQL > function > and a stored procedure? (just curious). Postgres functions have really expanded the concept of function, to the point of *almost* being stored procedures. However, there are still a few things missing: 1. Stored procedures must be able to return a rowset, possibly mutiple rowsets. 2. Stored procedures usually accept an indefinite number of parameters. 3. Stored procedures should be capable of logging their activites. 4. Stored procedures should have access to the full range of DB control language, includign cursors, transactions, and error handling. Of the 4. I'm only certain that 1. is part of the SQL92 spec. Any more authoritative opinions? -Josh ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Hello everyone
me again (apologies in advance :). I have been running a database
under 7.0.3 for some months now, and it was all fine. The tables all loaded
and it was working flawlessly. Then 7.1 came out and I noticed it had outer
joins (which are a big win in one of the main views i use).
So, i started loading in the schema into 7.1, but it seems to break.
Now, i have included the 3 tables below, but first i would like to tell some
of the design criteria behind this.
1) I need to have order_id as a primary key across the system (system key ?) so that i can pull out based on an
order_id.The same goes for history_id in the client.
2) I also need to have the client_id as a secondary key across the system, as another application frontend references
onclient_id. its icky but it works.
3) i have taken out some of the non-important fields, so please dont tell me that i have over-normalised my data ;p
for some reason though, under 7.1 when trying to get the tables i
get this error -> UNIQUE constraint matching given keys for referenced table
"client" not found. I know what it is saying, but i dont quite understand what
has changed between 7.0.3 and 7.1
CREATE TABLE action
( ORDER_ID integer PRIMARY KEY, ORDERTYPE integer NOT NULL,
client_id char(16) NOT NULL, priority integer DEFAULT 5 NOT NULL,
creation_id name default user, creation_date datetime default now(), close_id
name NULL, close_date datetime NULL, lock_id name
NULL, lock_date datetime NULL
) \g
CREATE TABLE client
( ORDER_ID integer REFERENCES action
(ORDER_ID) ON UPDATE CASCADE
INITIALLY DEFERRED, history_id SERIAL, active
boolean, client_id char(16) NOT NULL, change_id name
DEFAULT USER, change_date datetime DEFAULT NOW(), PRIMARY KEY
(ORDER_ID,history_id)
) \g
CREATE TABLE client_dates
( ORDER_ID integer REFERENCES action
(ORDER_ID) ON UPDATE CASCADE
INITIALLY DEFERRED, LOCATION_ID integer NOT NULL, history_id
integer REFERENCES client
(history_id) ON UPDATE CASCADE
INITIALLY DEFERRED, active boolean, client_id
char(16) REFERENCES client
(client_id) ON UPDATE CASCADE
INITIALLY DEFERRED, dte_action integer NULL, change_id
name DEFAULT USER, change_date datetime DEFAULT NOW(),
PRIMARYKEY (ORDER_ID,LOCATION_ID,history_id)
) \g
thank you, i know its something almost smackingly obvious but
i cant seem to understand why it was working and now isnt. i even went
through the changelog!
regards Stef
On Wed, 18 Apr 2001, Stef Telford wrote: > CREATE TABLE action > ( > ORDER_ID integer PRIMARY KEY, > ORDERTYPE integer NOT NULL, > client_id char(16) NOT NULL, > priority integer DEFAULT 5 NOT NULL, > creation_id name default user, > creation_date datetime default now(), > close_id name NULL, > close_date datetime NULL, > lock_id name NULL, > lock_date datetime NULL > ) \g > > CREATE TABLE client > ( > ORDER_ID integer REFERENCES action > (ORDER_ID) > ON UPDATE CASCADE > INITIALLY DEFERRED, > history_id SERIAL, > active boolean, > client_id char(16) NOT NULL, > change_id name DEFAULT USER, > change_date datetime DEFAULT NOW(), > PRIMARY KEY (ORDER_ID,history_id) > ) \g > > CREATE TABLE client_dates > ( > ORDER_ID integer REFERENCES action > (ORDER_ID) > ON UPDATE CASCADE > INITIALLY DEFERRED, > LOCATION_ID integer NOT NULL, > history_id integer REFERENCES client > (history_id) > ON UPDATE CASCADE > INITIALLY DEFERRED, > active boolean, > client_id char(16) REFERENCES client > (client_id) > ON UPDATE CASCADE > INITIALLY DEFERRED, > dte_action integer NULL, > change_id name DEFAULT USER, > change_date datetime DEFAULT NOW(), > PRIMARY KEY (ORDER_ID,LOCATION_ID,history_id) > ) \g > > > thank you, i know its something almost smackingly obvious but > i cant seem to understand why it was working and now isnt. i even went > through the changelog! Hmm, don't know why it's not in changelog, but the spec requires that the target fields of a foreign key constraint are themselves constrained by a unique or primary key constraint. 7.0 didn't actually check this, but 7.1 does. The reason for this is because while 7.0 would let you specify such a constraint, it wouldn't really work entirely properly if the field wasn't actually unique. You'll need a unique constraint on client.client_id.
Stephan Szabo wrote: > Hmm, don't know why it's not in changelog, but the spec requires that > the target fields of a foreign key constraint are themselves constrained > by a unique or primary key constraint. maybe its time for me to go and re-read the changelog with a fine tooth comb (it has been known for me to be blind to the obvious before and if this is the case then i more than apologise :) > 7.0 didn't actually check this, > but 7.1 does. The reason for this is because while 7.0 would let you > specify such a constraint, it wouldn't really work entirely properly > if the field wasn't actually unique. You'll need a unique constraint > on client.client_id. hhrrm. the only problem with -that- is that client_id by itself is not unique, but in conjunction with order_id it is. order_id is wholly unique. maybe i should jst drop the foreign key on client_id then, although i did want to use referential integrity on the client_id on an insert. although now i think about this, the criteria for having the changes on client_id cascading are totally gone and i could (read will) jst use a 'references' column. in short, thank you, i have jst figured out what an idiot i have been (again i hear you all say ;) many thanks and good work on postrgresql 7.1, it seems to be quite a bit quicker (and praise the lord for outer joins =) stefs