Skip site navigation (1) Skip section navigation (2)

Use a rule or a transaction

From: "Madel, Kurt" <KMadel(at)USInspect(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Use a rule or a transaction
Date: 2000-08-15 17:57:17
Message-ID: C6F9B91B745CD4119F1500A0C9DD60C02E348E@exchhq01.usinspect.com (view raw or flat)
Thread:
Lists: pgsql-sql
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

Responses

pgsql-sql by date

Next:From: Stephan SzaboDate: 2000-08-15 18:00:40
Subject: Re: copy from
Previous:From: Poul L. ChristiansenDate: 2000-08-15 17:49:43
Subject: Re: 8K Limit, whats the best strategy?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group