insert and limit

kimi

Hi there...

I have more than 5 pc to run a hostel registration. Each room is limited to 4 people only.

if ($total <= $max_room ) {
  $insert = "INSERT INTO hostel VALUES ('$level','$room')";
  $result = pg_exec($insert);
}

My problem : it will occasionally insert 5 peoples into 1 room. How can i limit each room only contains 4 peoples?

Please help...
Thanks.

hermand

You cant with MySQL AFAIK, you'd need to do it all with PHP. What do the variables stand for in that example snippet?

Sxooter

Aren't you glad you don't use MySQL??? Because you CAN do this with Postgresql.

Just use a transaction:


$query = "Your query goes here";

pg_exec($conn,"begin");
pg_exec($conn,"set TRANSACTION ISOLATION LEVEL serializable");
$res = pg_query($conn,"select count(*)as count from hostel where room=2 and level=3");
$val = pg_fetch_result($res,0,'count');
if ($val<4){
  pg_query($conn,$query);
  pg_query($conn,"commit");
} else {
  pg_query($conn,"rollback");
  print "huh, looks like we have a bug somewhere.  Please try again <A href=...";
}

Sxooter

Oh, and I'm reasonably certain there's a data constraint somewhere we could throw at this too to make sure that doesn't happen. Or a rule or trigger that would fire when you tried to do that and not let you.

Oh, the reason for the set serializable...

I thought I'd throw some more info at ya.

If you set the transaction serializable, you have to be careful about blocking. This programming example assumes that in general, you won't often be putting two people into the same room at the same time by two different people. IF YOU DO, it's still safe in Postgresql, but VERY dangerous in MySQL with code only, unless you wanna lock the whole table or individual rows. which makes it slower.

Say there were 2 people in the room, and three clerks clicked the button at the same time to add someone to that room.

With serializable mode, the SELECT statements of the second and subsequent transactions will WAIT until the previous transaction either commits or rollsback. either way the next transaction will then start, and they'll be forced to go in order.

For an infinite number of clerks, with large rooms, (think ticket sales to coliseums) this situation is unusable. But for what you're doing it's exactly the right fit.

A row locking database would work that way automatically, but you kinda have to force Postgresql to act like one when you need that behaviour.

the good news is that non-serializable select queries won't even know you're doing all this.