Thread: Use a rule or a transaction
Hello, I am creating a web based (using php) class registration database with three tables directly effected by the registration process: 1) class 2) student 3) module The module table has a field for every module (or class period) for every student that registers for a given year. When a student registers for a given class, the class.id for that class is placed in the respective module field. However, if class.size is equal to class.maxsize, then I don't want to allow that class.id to be inserted into the module table. If the class.size is ok, then the class.id should be added to the given module field, module.a_q1 for example AND the class.size needs to be incremented by one (further, the class.size needs to be decremented if a student drops a class). My question is, would it be better (faster) to use rules or to use a transaction. Also, if I use a transaction, it would look something like this: >begin work; >insert into module (a_q1) values (1); >update class set class.size=class.size+1 where class.id=1 and class.size<class.maxsize; >commit work; >begin work; >insert into module (a_q2) values (3); >update class set class.size=class.size+1 where class.id=1 and class.size<class.maxsize; >commit work; ...and so on If I did it this way, I would have to do this for 4 quarters by 6 modules (24 times). I feel like I have to do it one by one, because a student should only not get registered for classes that are full. I don't want to lump all 24 modules into one transaction and then have the student start the registration process entirely over because one class was full. Is there a faster way to do this with rules or a better way to do it otherwise. TIF, Kurt
Hello Antoine, I thought that triggers only worked for one table at a time. In this case, I want the class.size incremented and checked for maxsize when I perform an update or insert on the module table. I was under the impression that a trigger would only work if I was performing an insert or update on the same table that I wanted the trigger to act on. Please let me know if I am way off in regards to triggers. thanks, Kurt -----Original Message----- From: Antoine Reid [mailto:antoiner@hansonpublications.com] Sent: Tuesday, August 15, 2000 2:43 PM To: Madel, Kurt Cc: pgsql-sql@postgresql.org Subject: Re: [SQL] Use a rule or a transaction On Tue, Aug 15, 2000 at 01:57:17PM -0400, Madel, Kurt wrote: > Hello, > > I am creating a web based (using php) class registration database with three > tables directly effected by the registration process: [snip] > My question is, would it be better (faster) to use rules or to use a > transaction. How about using triggers? I have used pl/pgsql triggers in the past to do similar things. I was not checking a maximum but definitely was keeping a reference count. What you want is a trigger that increments on insert, decrement on delete, and on update, IF the id changed, decrement the old one, increment the new one.. I suspect you could also have another trigger, on update on class, that would abort the transaction if class.size ever becomes bigger than maxsize.. I can't really comment on performance though..... > Is there a faster way to do this with rules or a better way to do it > otherwise. > > TIF, > Kurt Hope it helps Antoine -- o Antoine Reid o> Alcohol and calculus <o> <|> antoiner@hansonpublications.com <| don't mix. Never drink |>\ antoiner@edmarketing.com >\ and derive. /<
Hey Antoine,
I am using 7.0.2, and I would be in your debt if you created a working
example. Basically, the structure of the query I would like to do is as
such (there are six modules in the module table for each quarter:
a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.....). A student comes to register
and selects all of the classes that are available in a given module, and
then hits submit. Information will be inserted into the modules table as
follows (in PHP):
<?php
....
$conn=pg_connect(blah, blah, blah)or die('Unable to connect to database');
pg_exec($conn,"insert into module (student_id,year) values
('$student_id','$year')") or die('Unable to insert record, please go back to registration
page');
pg_exec($conn,"begin work");
pg_exec($conn,"update module set a_q1='$a_q1 where student_id=$student_id
and year=$year");
pg_exec($conn,"update class set class.size=class.size+1 where class.id=$a_q1
and class.size<class.maxsize");
pg_exec($conn,"commit work");
//and so on for the other 23 module/quarters
...
?>
If you could give me a working example of a trigger/function that would
simplify this, that would be fantastic.
Thanks,
Kurt
-----Original Message-----
From: Antoine Reid [mailto:antoiner@hansonpublications.com]
Sent: Tuesday, August 15, 2000 3:04 PM
To: Madel, Kurt
Cc: pgsql-sql@postgresql.org
Subject: Re: [SQL] Use a rule or a transaction
On Tue, Aug 15, 2000 at 02:52:24PM -0400, Madel, Kurt wrote:
> Hello Antoine,
>
> I thought that triggers only worked for one table at a time. In this
case,
> I want the class.size incremented and checked for maxsize when I perform
an
> update or insert on the module table. I was under the impression that a
> trigger would only work if I was performing an insert or update on the
same
> table that I wanted the trigger to act on.
>
> Please let me know if I am way off in regards to triggers.
>
> thanks,
> Kurt
>
Hi!
Well, the trigger, basically is a function that will be executed when you
act
on one table.
If I understand you correctly, you have a table that is a list of classes.
for each class, you want to have a 'reference count' (current usage) and a
maximum. Trying to take your own words, 'module' is a list of items that are
to be put member of 'classes'. classes is the table that has the maximum and
current usage numbers.
Assuming this is the case, what you do is following:
(pseudo code.. not actual sql statements!)
on insert on modules, update classes set current_usage=current_usage + 1
where
the class_id is equal to the id of the row you just added in modules.
on delete on modules, update classes set current_usage=current_usage - 1 ...
on update on modules, IF-and-only-if the class_id changed, increment the new
class,
and decrement the old class.
This all takes care of keeping the refcount in classes up to date.
Now, for the maximum.. since your triggers on modules will always do an
update
on classes, we can create triggers on update on classes to make sure the
maximum
is not over..
create another trigger;
create a trigger AFTER update on classes. abort the transaction with a
proper
message if class.current_usage > class.maximum.
abort the transaction with proper message if class.current_usage < 0 (this
shouldn't
happen!!!!!)
I have implemented ref counts in the past, I could probably come up with a
working
example really quick, if you want. I suspect the maximum check shouldn't be
too
hard either..
Oh BTW, this should work on 7.0.2, probably on 7.0; I really don't know
about 6.x.x..
hope this helps
antoine
-- o Antoine Reid o> Alcohol and calculus <o>
<|> antoiner@hansonpublications.com <| don't mix. Never drink |>\ antoiner@edmarketing.com >\ and
derive. /<
Hey Antoine,
That is awesome, you just whipped that out, and I think it will work
beautifully. I will let you know.
Also, I think I have the last one licked
- The trigger on INSERT on modules should return an error if the class_id
does not exist..
I created the 'module.class_id' as a foreign key of the class table, so that
won't let a class.id that doesn't exist, to be inserted into the module
table.
Thanks for all your help, I can't wait to get home and test it out,
Kurt
-----Original Message-----
From: Antoine Reid [mailto:antoiner@hansonpublications.com]
Sent: Tuesday, August 15, 2000 3:49 PM
To: Madel, Kurt
Cc: 'Antoine Reid'; pgsql-sql@postgresql.org
Subject: Re: [SQL] Use a rule or a transaction
On Tue, Aug 15, 2000 at 03:38:09PM -0400, Madel, Kurt wrote:
> Hey Antoine,
>
> I am using 7.0.2, and I would be in your debt if you created a working
> example. Basically, the structure of the query I would like to do is as
> such (there are six modules in the module table for each quarter:
> a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.....). A student comes to register
> and selects all of the classes that are available in a given module, and
> then hits submit. Information will be inserted into the modules table as
> follows (in PHP):
[snip]
I am not really familiar with PHP (installing/admining, but not programming
with it...).. At any rate, you will want to check the success/failure of
every insert/update/delete...
Here is a working example of such tables, including the reference count AND
limit check. This was tested with 7.0.2 on linux, i386. Make sure you have
plpgsql installed in that database....
> If you could give me a working example of a trigger/function that would
> simplify this, that would be fantastic.
here we go, broken in sections:
# Here, the tables:
# I made those simple for demonstration purposes.
CREATE TABLE "modules" ( "name" character varying(32) NOT NULL, "class_id" int4 NOT NULL, PRIMARY KEY
("name")
);
CREATE TABLE "classes" ( "class_id" int4 NOT NULL, "usage" int4 NOT NULL, "max" int4 NOT NULL,
PRIMARYKEY ("class_id")
);
# Now, the different plpgsql functions:
# this one will increment usage in a class.
CREATE FUNCTION "increment_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage + 1 where classes.class_id = NEW.class_id;
return NEW;
end;
' LANGUAGE 'plpgsql';
# this one will decrement usage in a class.
CREATE FUNCTION "decrement_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage - 1 where classes.class_id = OLD.class_id;
return OLD;
end;
' LANGUAGE 'plpgsql';
# this one will check if usage is under 0, or over the maximum.
CREATE FUNCTION "check_limit" ( ) RETURNS opaque AS '
begin
IF NEW.usage > NEW.max
then raise exception ''That class is full, sorry.'';
end if;
if NEW.usage < ''0''
then raise exception ''Class cannot have a negative value! Report to the DBA
now!!'';
end if;
return new;
end;
' LANGUAGE 'plpgsql';
# now, let's create triggers to call those functions on some events:
CREATE TRIGGER "insert_modules" AFTER INSERT ON "modules" FOR EACH ROW
EXECUTE PROCEDURE "increment_classes" ();
CREATE TRIGGER "delete_modules" BEFORE DELETE ON "modules" FOR EACH ROW
EXECUTE PROCEDURE "decrement_classes" ();
CREATE TRIGGER "update_classes" AFTER UPDATE ON "classes" FOR EACH ROW
EXECUTE PROCEDURE "check_limit" ();
So there you go.. please note the following:
- The classes table should have a trigger to make sure you don't insert a
row
with a usage != 0.
- There should be a trigger on UPDATE on modules that would decrement the
old
class, and increment the new one. The triggers on classes will already
check
that the new one is not already full..
- The trigger on INSERT on modules should return an error if the class_id
does not exist..
These are left as an exercise to the reader... (especially because i'm not
sure how to do that
last one... :-> )
> Thanks,
> Kurt
hope this helps
antoine
-- o Antoine Reid o> Alcohol and calculus <o>
<|> antoiner@hansonpublications.com <| don't mix. Never drink |>\ antoiner@edmarketing.com >\ and
derive. /<
Create a trigger on module before update or insert. The philosophy of postgres is to create a trigger based on a function previously created. Take a look at the docs, the part on procedural languages. The book from Momjian has one or two examples on triggers creation. -- Jesus Aneiros Sosa mailto:aneiros@jagua.cfg.sld.cu http://jagua.cfg.sld.cu/~aneiros On Tue, 15 Aug 2000, Madel, Kurt wrote: > Hello Antoine, > > I thought that triggers only worked for one table at a time. In this case, > I want the class.size incremented and checked for maxsize when I perform an > update or insert on the module table. I was under the impression that a > trigger would only work if I was performing an insert or update on the same > table that I wanted the trigger to act on. > > Please let me know if I am way off in regards to triggers. > > thanks, > Kurt > > -----Original Message----- > From: Antoine Reid [mailto:antoiner@hansonpublications.com] > Sent: Tuesday, August 15, 2000 2:43 PM > To: Madel, Kurt > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] Use a rule or a transaction > > > On Tue, Aug 15, 2000 at 01:57:17PM -0400, Madel, Kurt wrote: > > Hello, > > > > I am creating a web based (using php) class registration database with > three > > tables directly effected by the registration process: > > [snip] > > > My question is, would it be better (faster) to use rules or to use a > > transaction. > > How about using triggers? I have used pl/pgsql triggers in the past to do > similar things. I was not checking a maximum but definitely was keeping a > reference count. What you want is a trigger that increments on insert, > decrement on delete, and on update, IF the id changed, decrement the old > one, increment the new one.. I suspect you could also have another trigger, > on update on class, that would abort the transaction if class.size ever > becomes bigger than maxsize.. > > I can't really comment on performance though..... > > > Is there a faster way to do this with rules or a better way to do it > > otherwise. > > > > TIF, > > Kurt > > Hope it helps > Antoine > > -- > o Antoine Reid o> Alcohol and calculus <o> > <|> antoiner@hansonpublications.com <| don't mix. Never drink | > >\ antoiner@edmarketing.com >\ and derive. /< >
On Tue, Aug 15, 2000 at 03:38:09PM -0400, Madel, Kurt wrote:
> Hey Antoine,
>
> I am using 7.0.2, and I would be in your debt if you created a working
> example. Basically, the structure of the query I would like to do is as
> such (there are six modules in the module table for each quarter:
> a_q1,b_q1,c_q1,d_q1,e_q1,morn_q1,a_q2.....). A student comes to register
> and selects all of the classes that are available in a given module, and
> then hits submit. Information will be inserted into the modules table as
> follows (in PHP):
[snip]
I am not really familiar with PHP (installing/admining, but not programming
with it...).. At any rate, you will want to check the success/failure of
every insert/update/delete...
Here is a working example of such tables, including the reference count AND
limit check. This was tested with 7.0.2 on linux, i386. Make sure you have
plpgsql installed in that database....
> If you could give me a working example of a trigger/function that would
> simplify this, that would be fantastic.
here we go, broken in sections:
# Here, the tables:
# I made those simple for demonstration purposes.
CREATE TABLE "modules" ( "name" character varying(32) NOT NULL, "class_id" int4 NOT NULL, PRIMARY KEY
("name")
);
CREATE TABLE "classes" ( "class_id" int4 NOT NULL, "usage" int4 NOT NULL, "max" int4 NOT NULL,
PRIMARYKEY ("class_id")
);
# Now, the different plpgsql functions:
# this one will increment usage in a class.
CREATE FUNCTION "increment_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage + 1 where classes.class_id = NEW.class_id;
return NEW;
end;
' LANGUAGE 'plpgsql';
# this one will decrement usage in a class.
CREATE FUNCTION "decrement_classes" ( ) RETURNS opaque AS '
begin
update classes set usage = usage - 1 where classes.class_id = OLD.class_id;
return OLD;
end;
' LANGUAGE 'plpgsql';
# this one will check if usage is under 0, or over the maximum.
CREATE FUNCTION "check_limit" ( ) RETURNS opaque AS '
begin
IF NEW.usage > NEW.max
then raise exception ''That class is full, sorry.'';
end if;
if NEW.usage < ''0''
then raise exception ''Class cannot have a negative value! Report to the DBA now!!'';
end if;
return new;
end;
' LANGUAGE 'plpgsql';
# now, let's create triggers to call those functions on some events:
CREATE TRIGGER "insert_modules" AFTER INSERT ON "modules" FOR EACH ROW EXECUTE PROCEDURE "increment_classes" ();
CREATE TRIGGER "delete_modules" BEFORE DELETE ON "modules" FOR EACH ROW EXECUTE PROCEDURE "decrement_classes" ();
CREATE TRIGGER "update_classes" AFTER UPDATE ON "classes" FOR EACH ROW EXECUTE PROCEDURE "check_limit" ();
So there you go.. please note the following:
- The classes table should have a trigger to make sure you don't insert a row
with a usage != 0.
- There should be a trigger on UPDATE on modules that would decrement the old
class, and increment the new one. The triggers on classes will already check
that the new one is not already full..
- The trigger on INSERT on modules should return an error if the class_id
does not exist..
These are left as an exercise to the reader... (especially because i'm not sure how to do that
last one... :-> )
> Thanks,
> Kurt
hope this helps
antoine
-- o Antoine Reid o> Alcohol and calculus <o>
<|> antoiner@hansonpublications.com <| don't mix. Never drink |>\ antoiner@edmarketing.com >\ and
derive. /<
On Tue, Aug 15, 2000 at 02:52:24PM -0400, Madel, Kurt wrote: > Hello Antoine, > > I thought that triggers only worked for one table at a time. In this case, > I want the class.size incremented and checked for maxsize when I perform an > update or insert on the module table. I was under the impression that a > trigger would only work if I was performing an insert or update on the same > table that I wanted the trigger to act on. > > Please let me know if I am way off in regards to triggers. > > thanks, > Kurt > Hi! Well, the trigger, basically is a function that will be executed when you act on one table. If I understand you correctly, you have a table that is a list of classes. for each class, you want to have a 'reference count' (current usage) and a maximum. Trying to take your own words, 'module' is a list of items that are to be put member of 'classes'. classes is the table that has the maximum and current usage numbers. Assuming this is the case, what you do is following: (pseudo code.. not actual sql statements!) on insert on modules, update classes set current_usage=current_usage + 1 where the class_id is equal to the id of the row you just added in modules. on delete on modules, update classes set current_usage=current_usage - 1 ... on update on modules, IF-and-only-if the class_id changed, increment the new class, and decrement the old class. This all takes care of keeping the refcount in classes up to date. Now, for the maximum.. since your triggers on modules will always do an update on classes, we can create triggers on update on classes to make sure the maximum is not over.. create another trigger; create a trigger AFTER update on classes. abort the transaction with a proper message if class.current_usage > class.maximum. abort the transaction with proper message if class.current_usage < 0 (this shouldn't happen!!!!!) I have implemented ref counts in the past, I could probably come up with a working example really quick, if you want. I suspect the maximum check shouldn't be too hard either.. Oh BTW, this should work on 7.0.2, probably on 7.0; I really don't know about 6.x.x.. hope this helps antoine -- o Antoine Reid o> Alcohol and calculus <o> <|> antoiner@hansonpublications.com <| don't mix. Never drink |>\ antoiner@edmarketing.com >\ and derive. /<
On Tue, Aug 15, 2000 at 01:57:17PM -0400, Madel, Kurt wrote: > Hello, > > I am creating a web based (using php) class registration database with three > tables directly effected by the registration process: [snip] > My question is, would it be better (faster) to use rules or to use a > transaction. How about using triggers? I have used pl/pgsql triggers in the past to do similar things. I was not checking a maximum but definitely was keeping a reference count. What you want is a trigger that increments on insert, decrement on delete, and on update, IF the id changed, decrement the old one, increment the new one.. I suspect you could also have another trigger, on update on class, that would abort the transaction if class.size ever becomes bigger than maxsize.. I can't really comment on performance though..... > Is there a faster way to do this with rules or a better way to do it > otherwise. > > TIF, > Kurt Hope it helps Antoine -- o Antoine Reid o> Alcohol and calculus <o> <|> antoiner@hansonpublications.com <| don't mix. Never drink |>\ antoiner@edmarketing.com >\ and derive. /<