Thread: Design Q.:logic in app or db?
I have general design question about Postgres usage: How does one decide how much, and what parts of logic should go in DB rules, triggers, functions, constraints etc, versus what should go in the application? I see postings here from people who obviously have a lot of domain logic in the DB side. I currently have almost none. I plan to set up a bunch of RI constraints to keep things clean and consistant, but what about logic that implements frequent domain operations? Brief sketch of my project: 2 developers, 4k lines of python(gtk, pygres), 2 main GUI user apps and a few read-only scripts for web display, 50 concurrent users(all local), DB performance important but not currently a problem. The main thing not done yet is to facilitate ad-hoc queries (via odbc excel etc.) from db-naive users: maybe restructuring the db to make it simpler, maybe views and functions... The data is somewhat complex in structure. -- George -- I cannot think why the whole bed of the ocean isnot one solid mass of oysters, so prolific they seem. Ah,I am wandering!Strange how the brain controls the brain!-- Sherlock Holmes in "The Dying Detective"
On Wed, 26 Feb 2003, george young wrote: > I have general design question about Postgres usage: How does one decide > how much, and what parts of logic should go in DB rules, triggers, > functions, constraints etc, versus what should go in the application? > > I see postings here from people who obviously have a lot of domain > logic in the DB side. I currently have almost none. I plan to set up > a bunch of RI constraints to keep things clean and consistant, but what > about logic that implements frequent domain operations? > > Brief sketch of my project: 2 developers, 4k lines of python(gtk, pygres), > 2 main GUI user apps and a few read-only scripts for web display, > 50 concurrent users(all local), DB performance important but not currently > a problem. > > The main thing not done yet is to facilitate ad-hoc queries > (via odbc excel etc.) from db-naive users: maybe restructuring the > db to make it simpler, maybe views and functions... The data is > somewhat complex in structure. If your column names make sense you could expose table's structure using metadata of your favorite driver or direct sql calls to system tables. Also you could install a search engine on your site, and have all these "adhoc" queries predone and indexed. > > -- George > -- > I cannot think why the whole bed of the ocean is > not one solid mass of oysters, so prolific they seem. Ah, > I am wandering! Strange how the brain controls the brain! > -- Sherlock Holmes in "The Dying Detective" > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > ================================================================== Achilleus Mantzios S/W Engineer IT dept Dynacom Tankers Mngmt Nikis 4, Glyfada Athens 16610 Greece tel: +30-10-8981112 fax: +30-10-8981877 email: achill@matrix.gatewaynet.com mantzios@softlab.ece.ntua.gr
George, > I have general design question about Postgres usage: How does one decide > how much, and what parts of logic should go in DB rules, triggers, > functions, constraints etc, versus what should go in the application? Ideally, this should be done strictly on the basis of carefully planned design architecture, where each business rule is implemented at the application level where it is most effective, such as referential data integrity in the database and security in the middleware. Realistically, business logic tends to be implemented in the layer where you have the most programming expertise. The people on this list are often expert DBAs, so they implement as much business logic as possible in the database in the form of views, triggers, and rules. However, I"ve seen plenty of shops with multiple crackerjack Java programmers and a weak DBA implementing all of their business logic in the middleware. > The main thing not done yet is to facilitate ad-hoc queries > (via odbc excel etc.) from db-naive users: maybe restructuring the > db to make it simpler, maybe views and functions... The data is > somewhat complex in structure. This desired feature would argue strongly in favor of putting as much business logic as possibly in your database in the form of views and rules. If users can bypass the interface and middleware, you cannot rely on it to enforce data integrity and access control. -- Josh Berkus josh@agliodbs.com Aglio Database Solutions San Francisco
In contrib/btree_gist/ I see: CREATE FUNCTION gint4_union(bytea, internal) RETURNS int4 AS 'MODULE_PATHNAME' LANGUAGE 'C'; but gint4_union does this: INT4KEY *out = palloc(sizeof(INT4KEY)); [...] PG_RETURN_POINTER(out); Is the int4 return type declared above a bug? -- Itai Zukerman <http://www.math-hat.com/~zukerman/>
I am confused by your question. It seems it is declared as returning an int. --------------------------------------------------------------------------- Itai Zukerman wrote: > In contrib/btree_gist/ I see: > > CREATE FUNCTION gint4_union(bytea, internal) > RETURNS int4 > AS 'MODULE_PATHNAME' > LANGUAGE 'C'; > > but gint4_union does this: > > INT4KEY *out = palloc(sizeof(INT4KEY)); > [...] > PG_RETURN_POINTER(out); > > Is the int4 return type declared above a bug? > > -- > Itai Zukerman <http://www.math-hat.com/~zukerman/> > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001+ If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania19073
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> I am confused by your question. It seems it is declared as returning an
> int.
I suspect that all of the GiST union methods should be declared to
return 'internal', but Oleg or Teodor would probably know better.
regards, tom lane
> I am confused by your question. It seems it is declared as returning an > int. >> CREATE FUNCTION gint4_union(bytea, internal) >> RETURNS int4 >> AS 'MODULE_PATHNAME' >> LANGUAGE 'C'; >> >> but gint4_union does this: >> >> INT4KEY *out = palloc(sizeof(INT4KEY)); >> [...] >> PG_RETURN_POINTER(out); The function returns type INT4KEY, which is a struct of 2 int4's, but is declared as returning just int4. It should be declared to return type int4key, no? I don't think this actually has any negative consequences, though. -- Itai Zukerman <http://www.math-hat.com/~zukerman/>