Thread: PG rules!
I just want to say, that PG is an awesome thing. I'm finding new uses for constraints of various kinds to ensure data integrity in my DB. Constraints will really make the whole application more solid, because programming errors elsewhere still won't allow corrupt data to get into the DB.
Heya Dr. Evil,
Have you tried out RULES yet? (CREATE RULE)
They're even niftier. :-)
Let say you have a table people can add stuff to, but you need to put 3
entries in the table which can never be deleted, you use CREATE RULE.
i.e.
Lets create an example table :
foo=> CREATE TABLE gift_certificates (idnum serial unique not null,
person varchar(20), amount float4);
NOTICE: CREATE TABLE will create implicit sequence
'gift_certificates_idnum_seq' for SERIAL column
'gift_certificates.idnum'
NOTICE: CREATE TABLE/UNIQUE will create implicit index
'gift_certificates_idnum_key' for table 'gift_certificates'
CREATE
Lets give it some data :
foo=> insert into gift_certificates (person, amount) values ('Justin',
200);
INSERT 51564057 1
foo=> insert into gift_certificates (person, amount) values ('Tom',
200);
INSERT 51564059 1
foo=> insert into gift_certificates (person, amount) values ('Richard',
200);
INSERT 51564062 1
foo=> insert into gift_certificates (person, amount) values ('Peter',
200);
INSERT 51564065 1
foo=> insert into gift_certificates (person, amount) values ('Bruce',
200);
INSERT 51564066 1
foo=> insert into gift_certificates (person, amount) values ('Marc',
200);
INSERT 51564067 1
foo=> insert into gift_certificates (person, amount) values ('Vince',
200);
foo=> select * from gift_certificates;
idnum | person | amount
-------+---------+--------
1 | Justin | 200
2 | Tom | 200
3 | Richard | 200
4 | Peter | 200
5 | Bruce | 200
6 | Marc | 200
7 | Vince | 200
(7 rows)
Lets add two everyday useful example rules :
foo=> CREATE RULE prot_gc_upd AS ON UPDATE TO gift_certificates WHERE
old.idnum < 4 DO INSTEAD nothing;
CREATE
foo=> CREATE RULE prot_gc_del AS ON DELETE TO gift_certificates WHERE
old.idnum < 4 DO INSTEAD nothing;
CREATE
So here, all the normal SQL queries work except those which would
specifically update or delete any of the first 3 entries in this
gift_certificates table.
foo=> update gift_certificates set person = 'Justin2' where idnum = 1;
UPDATE 0
foo=> update gift_certificates set person = 'Justin2' where idnum = 2;
UPDATE 0
foo=> update gift_certificates set person = 'Justin2' where idnum = 3;
UPDATE 0
foo=> update gift_certificates set person = 'Justin2' where idnum = 4;
UPDATE 1
See, that last one worked because it wasn't protected by the rules?
foo=> select * from gift_certificates;
idnum | person | amount
-------+---------+--------
1 | Justin | 200
2 | Tom | 200
3 | Richard | 200
5 | Bruce | 200
6 | Marc | 200
7 | Vince | 200
4 | Justin2 | 200
(7 rows)
foo=>
And the delete rule from up above works as well :
foo=> delete from gift_certificates;
DELETE 4
foo=> select * from gift_certificates;
idnum | person | amount
-------+---------+--------
1 | Justin | 200
2 | Tom | 200
3 | Richard | 200
(3 rows)
foo=>
Cool eh?
Hope that's useful! (We should prolly put this in the PostgreSQL
tutorial somewhere....)
:-)
Regards and best wishes,
Justin Clift
"Dr. Evil" wrote:
>
> I just want to say, that PG is an awesome thing. I'm finding new uses
> for constraints of various kinds to ensure data integrity in my DB.
> Constraints will really make the whole application more solid, because
> programming errors elsewhere still won't allow corrupt data to get
> into the DB.
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
That's pretty cool. I may look into those. I just like being able to
define that the data stay in a self-consistent format. Other
programming languages would do well to follow this method. I'm
programming my front-end in PHP. I should be able to say
INT i CHECK (i > 0);
when I declare it in PHP, for instance, but this isn't possible; they
don't even have strong typing!
Anyway, I was just writing a table which holds credit card payment
data. I put in a constraint:
cardnumber VARCHAR(20) CHECK (luhn10(cardnumber)),
right in the table, so no matter how screwed up anything is, only
valid card numbers can go in the table. I put foreign key constraints
in so that none of the domain names can ever be without an owner (it's
a domain registry project). This is how programming should be.
Compare this to PHP or perl, where all variables are strings, or C
where there are memory leaks all over the place unless the programmer
is very skilled and careful.
Am Mittwoch, 18. Juli 2001 09:01 schrieb Dr. Evil: > That's pretty cool. I may look into those. I just like being able > to define that the data stay in a self-consistent format. Other > programming languages would do well to follow this method. I'm > programming my front-end in PHP. I should be able to say > > INT i CHECK (i > 0); > > when I declare it in PHP, for instance, but this isn't possible; > they don't even have strong typing! > > Anyway, I was just writing a table which holds credit card payment > data. I put in a constraint: > > cardnumber VARCHAR(20) CHECK (luhn10(cardnumber)), > > right in the table, so no matter how screwed up anything is, only > valid card numbers can go in the table. But watch out the discussion "Application Design and PostgreSQL" these Days in the list. I thought your way. Thought it is pretty cool to have all logic inside the database, but it seems it isnt! How do you do your error checking? You won't be vendor independent! How do you check if your php sends a '1' instead of 'true' to the database. I think you can only catch this error with a midllerware. switching the databse is impossible. im just thinking about the same problem at this time and i am convinced by others to put the business logic in the middleware and just use SQL to store simple tables. janning -- Planwerk 6 /websolutions Herzogstraße 86 40215 Düsseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
> But watch out the discussion "Application Design and PostgreSQL" > these Days in the list. I thought your way. Thought it is pretty cool > to have all logic inside the database, but it seems it isnt! Don't listen to them! Every case is different. > How do you do your error checking? > You won't be vendor independent! I throw myself to the mercy of Postgres! > How do you check if your php sends a '1' instead of 'true' to the > database. I think you can only catch this error with a midllerware. > switching the databse is impossible. I am married to Postgres! > im just thinking about the same problem at this time and i am > convinced by others to put the business logic in the middleware and > just use SQL to store simple tables. I am familiar with that argument. It's a good point in some instances, but it isn't gospel by any means. The question is, how much portability do you really need? In my case, I know that I'm working on a domain name registry. Even being optimistic, let's say we get 100k domains registered. A 100k row table is well within PG's capabilities on appropriate hardware/OS (1.2mhz AMD, 2gig RAM, FreeBSD should do it). At the same time, I know that an Oracle license is completely out of our budget for the foreseeable future, so me worrying about being able to run my application on Oracle is like me worrying about which color Rolls Royce looks best. So, can you really say that this particular application needs to be portable at all? What kind of application really needs to be portable? If you think it's going to be small (in terms of data set size and transaction volume), then go with PG and commit to it. If you think it's going to be big, then why not get the budget and do it on Oracle or DB2 to begin with? I'm not saying that portability has no value, but you should always do a cost/benefit analysis of portability (and all the other major design requirements), and try to make some intelligent assumptions about the future, to see whether it's worth investing the substantial extra time and money in writing middleware. Perhaps some kind of accounting database at a rapidly growing company would be a good candidate for a highly portable design, because you can't afford Oracle right now, but you are pretty sure you'll need the extra power later. I've decided that my paricular application doesn't need portability, so I'm going to make full use of all the great features of PG to ensure data integrity and the highest possible stability. If our marketing projections are wrong, and we get 1mil domains registered in the first month, then certainly we will need to make some changes, but we will also have a lot more budget power. We can always get by by upgrading hardware and other tweaks. Now of course if I were a Java consultant, getting paid $100/hour to write Java middleware, I might feel differently about all this...
Janning Vygen wrote: > > But watch out the discussion "Application Design and PostgreSQL" > these Days in the list. I thought your way. Thought it is pretty cool > to have all logic inside the database, but it seems it isnt! > > How do you do your error checking? > You won't be vendor independent! > How do you check if your php sends a '1' instead of 'true' to the > database. I think you can only catch this error with a midllerware. > switching the databse is impossible. > > im just thinking about the same problem at this time and i am > convinced by others to put the business logic in the middleware and > just use SQL to store simple tables. > Putting business logic in the database doesn't mean you don't have to check for bussiness rules in the middle tier. There are a few things that a database can do without interference from the middle tier. For instance I have a database where depending which client orders stuff (flowers) and depending on the quantity, different percentages are substracted from the price. All those percentages and conditions are listed in tables, so PostgreSQL can handle that itself, you don't need a middle tier for that. But integrity checks that raise an error need to be handled in the middle tier. On first sight that looks like more work to be done in the middle tier. On second sight though, its a guarantee that it doesn't matter if you have 23 different middle ware programs or just one large one, your data integrity will always be upheld by the database itself. That doesn't mean you don't have to handle those exceptions in the middle tier however. Regards, Nils Zonneveld -- Alles van waarde is weerloos Lucebert
Am Mittwoch, 18. Juli 2001 11:31 schrieb Nils Zonneveld: > Janning Vygen wrote: > > But watch out the discussion "Application Design and PostgreSQL" > > these Days in the list. I thought your way. Thought it is pretty > > cool to have all logic inside the database, but it seems it isnt! > > > > How do you do your error checking? > > You won't be vendor independent! > > How do you check if your php sends a '1' instead of 'true' to the > > database. I think you can only catch this error with a > > midllerware. switching the databse is impossible. > > > > im just thinking about the same problem at this time and i am > > convinced by others to put the business logic in the middleware > > and just use SQL to store simple tables. > > Putting business logic in the database doesn't mean you don't have > to check for bussiness rules in the middle tier. this way i have to implement my businees logic twice. inside the database and in the middleware. I dont think that this is very good. > There are a few > things that a database can do without interference from the middle > tier. For instance I have a database where depending which client > orders stuff (flowers) and depending on the quantity, different > percentages are substracted from the price. All those percentages > and conditions are listed in tables, so PostgreSQL can handle that > itself, you don't need a middle tier for that. but how can i access the percentage via the middle tier. If i have a object orientated middle tier, the percentage might be an attribute of the class customer. So if your data model has a percentage for each customer depending on other values, you have a non-persistent attribute inside a persistent object. > But integrity checks > that raise an error need to be handled in the middle tier. > On first sight that looks like more work to be done in the middle tier. On > second sight though, its a guarantee that it doesn't matter if you > have 23 different middle ware programs or just one large one, your > data integrity will always be upheld by the database itself. That > doesn't mean you don't have to handle those exceptions in the > middle tier however. To me it looks like that there are two good solutions. first: build _ALL_ business logic inside the database. then you wont need a middleware or just modules which are wrappers around the backend (ex. $person = new Person(name, surname, adress) will become insert into person values(name, surname, adress)) disadvantage: vendor specific, not easy to implement advantage: second: put all business logic inside the middleware. disadvantage: every access must use this middleware. advantage: no vendor specific code, easier to implement business logic in your favorite programming language than in plpgsql. most programmin languages have a lot more features and can interact with the system(like sending mails, bills etc) third: all logic inside database AND middleware disadvantages: maintainance is very difficult, everything have to be implemented twice. vendor-specific At first glance i thought that solution one would be fine when i first read about all postgresql features, but then i was convinced by others in this list, that it is not a good choice. further ideas very welcome :-) janning -- Planwerk 6 /websolutions Herzogstra�e 86 40215 D�sseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
Am Mittwoch, 18. Juli 2001 16:36 schrieb Thomas Lockhart: > > this way i have to implement my businees logic twice. inside the > > database and in the middleware. I dont think that this is very > > good. > > Hmm. Sounds like you are writing both the DB and the app. So > enforcing *your* business rules in the DB is not so important to > you because you are trusting yourself to do the same in the app. > But for cases where the project outgrows one programmer, or where > there are multiple apps touching the *same* DB, isolating the > business rules - into the one place in common between these apps - > becomes a more obvious choice. What do you mean with "isolating the business rules - into the one place in common between these apps" what is the place _between_ these apps? Do you mean the middleware? or do you mean, that businnes rules are at one place and are then parsed into middleware and database from the same source?? This would be a great tool. Do you know one? janning -- Planwerk 6 /websolutions Herzogstra�e 86 40215 D�sseldorf fon 0211-6015919 fax 0211-6015917 http://www.planwerk6.de
> this way i have to implement my businees logic twice. inside the
> database and in the middleware. I dont think that this is very good.
Hmm. Sounds like you are writing both the DB and the app. So enforcing
*your* business rules in the DB is not so important to you because you
are trusting yourself to do the same in the app. But for cases where the
project outgrows one programmer, or where there are multiple apps
touching the *same* DB, isolating the business rules - into the one
place in common between these apps - becomes a more obvious choice.
- Thomas
"Dr. Evil" <drevil@sidereal.kz> writes:
>> im just thinking about the same problem at this time and i am
>> convinced by others to put the business logic in the middleware and
>> just use SQL to store simple tables.
> I am familiar with that argument. It's a good point in some
> instances, but it isn't gospel by any means.
I think it's reasonable to expect the database to support standard
SQL mechanisms like constraints. There are some things like MySQL
that don't, but they're not really databases IMHO :-).
Obviously, if you care about vendor independence, you'll need to stay
away from nonstandard stuff like plpgsql functions. But that shouldn't
convince you to lobotomize your use of standard SQL features.
regards, tom lane
> this way i have to implement my businees logic twice. inside the > database and in the middleware. I dont think that this is very good. Sometimes implenting the business logic twice is the right thing to do. For instance, let's say we have a financial application, and we need to calculate account balances (let's say it's non-trivial to do this). It seems like it would be good to have the middleware calculate it, and have PG calculate it. That way, if there is a bug somewhere in the routine that does the calculation, it will be caught. It all depends on what you're doing.
At the very least you should use the SQL 92 features that PostgreSQL has. There is no reason not to use Foreign keys and constraints, for example. Any commercial database that is an "upgrade" to PostgreSQL has these features (generally implemented in a very similar fashion), and since PostgreSQL is free, there is no reason to be downwards compatible. If you design your middleware so that it is compatible with something like MySQL then you really are creating too much work for yourself. Maintaining referential integrity is hard, and PostgreSQL gives you all the necessary tools to do it simply and inexpensively. Writing this type of functionality in your own application is a recipe for trouble. There is little chance that you are going to do as good a job as the PostgreSQL team, and implementing this type of stuff takes away time from implementing the stuff that actually matters. Besides, eventually your users are going to want to circumvent your middleware for one reason or another. PostgreSQL's constraints and referential integrity makes this sort of access safe. Personally, I am like Dr. Evil. The databases that I could afford to port my application to would all be either a step backwards, or at best a lateral move to a database with similar functionality. I can't afford Oracle, and don't really need or want the features that it has above and beyond PostgreSQL. Because of this once PostgreSQL gains the ability to return result sets from functions I will probably move *more* of my applications functionality directly into PostgreSQL. That way I could use this logic not only from my primary application, but also from psql, one-off Python scripts, MS Access front-ends, etc. Jason --- "Dr. Evil" <drevil@sidereal.kz> wrote: > > But watch out the discussion "Application Design > and PostgreSQL" > > these Days in the list. I thought your way. > Thought it is pretty cool > > to have all logic inside the database, but it > seems it isnt! > > Don't listen to them! Every case is different. > > > How do you do your error checking? > > You won't be vendor independent! > > I throw myself to the mercy of Postgres! > > > How do you check if your php sends a '1' instead > of 'true' to the > > database. I think you can only catch this error > with a midllerware. > > switching the databse is impossible. > > I am married to Postgres! > > > im just thinking about the same problem at this > time and i am > > convinced by others to put the business logic in > the middleware and > > just use SQL to store simple tables. > > I am familiar with that argument. It's a good point > in some > instances, but it isn't gospel by any means. > > The question is, how much portability do you really > need? In my case, > I know that I'm working on a domain name registry. > Even being > optimistic, let's say we get 100k domains > registered. A 100k row > table is well within PG's capabilities on > appropriate hardware/OS > (1.2mhz AMD, 2gig RAM, FreeBSD should do it). At > the same time, I > know that an Oracle license is completely out of our > budget for the > foreseeable future, so me worrying about being able > to run my > application on Oracle is like me worrying about > which color Rolls > Royce looks best. > > So, can you really say that this particular > application needs to be > portable at all? > > What kind of application really needs to be > portable? If you think > it's going to be small (in terms of data set size > and transaction > volume), then go with PG and commit to it. If you > think it's going to > be big, then why not get the budget and do it on > Oracle or DB2 to > begin with? > > I'm not saying that portability has no value, but > you should always do > a cost/benefit analysis of portability (and all the > other major design > requirements), and try to make some intelligent > assumptions about the > future, to see whether it's worth investing the > substantial extra time > and money in writing middleware. Perhaps some kind > of accounting > database at a rapidly growing company would be a > good candidate for a > highly portable design, because you can't afford > Oracle right now, but > you are pretty sure you'll need the extra power > later. > > I've decided that my paricular application doesn't > need portability, > so I'm going to make full use of all the great > features of PG to > ensure data integrity and the highest possible > stability. If our > marketing projections are wrong, and we get 1mil > domains registered in > the first month, then certainly we will need to make > some changes, but > we will also have a lot more budget power. We can > always get by by > upgrading hardware and other tweaks. > > Now of course if I were a Java consultant, getting > paid $100/hour to > write Java middleware, I might feel differently > about all this... > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/users-lounge/docs/faq.html __________________________________________________ Do You Yahoo!? Get personalized email addresses from Yahoo! Mail http://personal.mail.yahoo.com/
Am Mittwoch, 18. Juli 2001 21:02 schrieb Jason Earl: > At the very least you should use the SQL 92 features > that PostgreSQL has. [...] ok, i can agree to this. > Besides, eventually your users are going to want to > circumvent your middleware for one reason or another. > PostgreSQL's constraints and referential integrity > makes this sort of access safe. but it is only safe if you put ALL businnes logic inside postgresql. if you dont you should never access your databse via pgaccess or psql. > Because of this once PostgreSQL gains the ability to return > result sets from functions I will probably move *more* > of my applications functionality directly into > PostgreSQL. That way I could use this logic not only > from my primary application, but also from psql, > one-off Python scripts, MS Access front-ends, etc. So you split your business logic. I cant believe that this is easy to achieve. I guess the main reason for putting businnes logic into the database is performance!? Like if you have 1.000.000 persons and you want to calculate the persons average age, you can instanciate all person objects and calculate it OR just ask postgresql which will do it much faster i guess. So my conclusion to this topic is to put business logic into postgresql when you benefit from it because of performance or because of data integrity. On the other hand it is very difficult or mostly impossible to put all businnes logic inside postgresql. janning
"Dr. Evil" wrote: > > I just want to say, that PG is an awesome thing. I'm finding new uses > for constraints of various kinds to ensure data integrity in my DB. > Constraints will really make the whole application more solid, because > programming errors elsewhere still won't allow corrupt data to get > into the DB. And I thought it was a question about rules :-) Speaking of which can someone tell me in which circumstances to use rules and in what circumstances to use triggers? Nils -- Alles van waarde is weerloos Lucebert
See my book chapter at:
http://www.postgresql.org/docs/awbook.html
It discusses when to use each. Basically triggers are for modifying the
row you just inserted/updated, while Rules are for referring to other
tables.
>
>
> "Dr. Evil" wrote:
> >
> > I just want to say, that PG is an awesome thing. I'm finding new uses
> > for constraints of various kinds to ensure data integrity in my DB.
> > Constraints will really make the whole application more solid, because
> > programming errors elsewhere still won't allow corrupt data to get
> > into the DB.
>
> And I thought it was a question about rules :-) Speaking of which can
> someone tell me in which circumstances to use rules and in what
> circumstances to use triggers?
>
> Nils
> --
> Alles van waarde is weerloos
> Lucebert
>
> ---------------------------(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
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Also, triggers are for when you want to be able to raise meaningful errors, right? I tend to use triggers a lot for
thingsI could use rules for, just to catch problems and return error messages that help figure out what happened. I
thoughtyou could not do that with a rule.
Ian A. Harding
Programmer/Analyst II
Tacoma-Pierce County Health Department
(253) 798-3549
mailto: ianh@tpchd.org
>>> Bruce Momjian <pgman@candle.pha.pa.us> 07/23/01 02:27PM >>>
See my book chapter at:
http://www.postgresql.org/docs/awbook.html
It discusses when to use each. Basically triggers are for modifying the
row you just inserted/updated, while Rules are for referring to other
tables.
>
>
> "Dr. Evil" wrote:
> >
> > I just want to say, that PG is an awesome thing. I'm finding new uses
> > for constraints of various kinds to ensure data integrity in my DB.
> > Constraints will really make the whole application more solid, because
> > programming errors elsewhere still won't allow corrupt data to get
> > into the DB.
>
> And I thought it was a question about rules :-) Speaking of which can
> someone tell me in which circumstances to use rules and in what
> circumstances to use triggers?
>
> Nils
> --
> Alles van waarde is weerloos
> Lucebert
>
> ---------------------------(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
>
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
> > And I thought it was a question about rules :-) Speaking of which can > someone tell me in which circumstances to use rules and in what > circumstances to use triggers? > > check here: http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/rules-triggers.html regards laser