Principal Peril at Postgres High

Question

In Postgres High (PSD 9.6), the nerds and the glee club are in a strange co-dependent war. The nerds plot secret plans against the glee club. The glee club responds with pleasant songs.

The glee club would never respond if nerds wimp out. Similarly, the nerds are only trying to get a rile out of the glee club, so if the glee club fails to respond, the nerds won't actually invoke their secret plan.

And as Principal Programmer, it's my job to ensure that only one possible nerd rebellion attempt happens at once. I must also ensure that both the nerds succeed and the glee club succeeds or neither succeed. But those troublesome nerds, always trying sql injection attacks. Gotta prevent that too. And as all good leaders of Postgres High do, we write our lesson plans in SQL.

Lesson Plan:

begin;
-- stop race conditions with a hall monitor
select from semaphore where name = 'hall_monitor' for update;

prepare nerd_rebellion (
  text, -- secret_mission
  date -- when_we_strike
) as
  update ultimate_plan
    set secret_mission = $1
    where when_we_strike = $2;

prepare glee_club_counterstrike (
  text, -- happy_song
  boolean -- kill_them_with_love
) as
  insert into song_therapy (
    happy_song,
    kill_them_with_love
  ) values (
    $1,
    $2  
  )

execute nerd_rebellion(
  'Nerds do stuff like this ; drop table song_therapy --f you glee club',
  '2017-01-01'
);
execute glee_club_counterstrike(
  'god_bless_america',
  true
);
-- let us never speak of this again ...
deallocate nerd_rebellion
deallocate glee_club_counterstrike

-- all done. Release the hall monitor
commit;

Gosh this is a lot for a principal to do. Our class sessions are so short, we have all this preparing and deallocating. Ugh. Really cuts into my coffee and doughnut time. But I need to wrap multiple statements in a single transaction AND serialize with a semaphore. Can't compromise on SQL injection either. I don't see a more simple solution. Do you?

People don't get it, this will probably be closed soon. I like the question, though. — Erwin Brandstetter, Jan 05 '17 at 02:19

score 2 · Accepted Answer · edited May 23 '17 at 12:16

You did not make sure the nerds succeed. The nerds might strike out (the UPDATE might find no row), the glee club would sing anyway.

You could chain the two commands with a data-modifying CTE or in a plpgsql function where you can make the insert depend on success of the update.

Here is a tool the principal might use:

CREATE OR REPLACE FUNCTION nerd_strike(
      _secret_mission      text
    , _when_we_strike      date 
    , _happy_song          text
    , _kill_them_with_love boolean
   ) RETURNS void AS
$func$
BEGIN
   SELECT FROM semaphore WHERE name = 'hall_monitor' FOR UPDATE;

   UPDATE ultimate_plan
   SET    secret_mission = _secret_mission
   WHERE  when_we_strike = _when_we_strike;

   IF FOUND THEN  --  only if update actually succeeded
      INSERT INTO song_therapy (happy_song, kill_them_with_love)
      VALUES (_happy_song, _kill_them_with_love);
   END IF;
END
$func$  LANGUAGE plpgsql;

Call:

SELECT nerd_strike('Nerds do stuff like this ; drop table song_therapy --f you glee club'
                 , '2017-01-01'
                 , 'god_bless_america'
                 , true);

Functions are atomic, so it all happens or it never happened.
Parameters are passed as values (much like with prepared statements), so no chance for SQL injection.

Run SQL statements in PL/pgSQL only if a row doesn't exist

Controlling atomicity at the function-level in such a circumstance is simply brilliant. Thank you. I’ll hire a VP, call him `Burt_the_Nerd_Striker` and delegate responsibility as a Principal should. Coffee and doughnuts to my heart’s content. You’re welcome to join me, Erwin. I owe you one. — bbuckley123, Jan 05 '17 at 06:43

Principal Peril at Postgres High

1 Answers1