Solution to a PostgreSQL Error In the Developer Virtual Machine

Document created by mkauffman on Feb 24, 2016Last modified by mkauffman on Mar 25, 2016
Version 4Show Document
  • View in full screen mode

Recently I ran across an interesting issue while helping a Partner who uses the DVM exclusively for development. Changing some user's Institution role resulted in a screen indicating a database error. Looking through the logs/bb-sqlerror-log.txt file we saw entries like the following:

  2016-02-19 22:39:34 +0000 - { call user_roles_cr(users_pk1 := , institution_roles_pk1 := , row_status := , data_src_pk1 := , ) } failed. - org.postgresql.util.PSQLException: ERROR: cursor "
cursor0001" already in use
  Where: PL/pgSQL function layout_module_group_trg_ins_tf() line 27 at FOR over cursor


It turns out that the DVMs have several Postgres procedures and triggers that are using the same cursor names, causing the "already in use" contention. This issue impacts April 2014, October 2014, and Q2 2015 DVMS. You can correct for for this now with the following procedure.


1. Create a backup image/snapshot of the system you're going to work with. For example with Virtualbox I can click to create a snapshot that I can roll back to.

2. Open a terminal window that you can cut and paste code into. This is important for getting the SQL code provided below exactly right. On my Mac I opened a terminal.

3. From the terminal ssh into the DVM. However you get here doesn't matter - we're just after the cut/paste ability.

4. Switch to root.

$ sudo su -

5. Shutdown Learn.

# cd /usr/local/blackboard/tools/admin

# ./ services.stop

6. Tail the appropriate logs/tomcat/stdout-stderr file until the Learn app stops. Use ps -ef | grep java to ensure no Java code is running.

7. Switch to the postgres user. Start the psql command line interpreter. Connect to the BBLEARN database.

# sudo su - postgres

$ psql

postgres=# \connect BBLEARN

You are now connected to the database “BBLEARN” as user “postgres”.



8. Now run following SQL which should correct the issue.

First copy and paste the block of SQL below into the window you have open, right after the # that is displayed, then hit enter. Copy and paste everything up to and including the line $$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION layout_module_group_trg_ins_tf() RETURNS trigger AS $$


   temp_pk1 bigint;

   lmg_cur0 cursor for

      SELECT new.layout_pk1 layout_pk1, defl.pk1 default_layout_pk1

        FROM layout l, layout defl

       WHERE new.layout_pk1=l.pk1

         AND l.layout_family_pk1 = defl.layout_family_pk1

         AND defl.default_ind='Y' AND defl.pk1 <> new.layout_pk1;


FOR currow IN lmg_cur0



--Renamed alias to position1 as it is a reserved word

     lmg_cur1 cursor for

          SELECT mmg.module_pk1 module_pk1,

                 ml.column_number column_number, ml.position position1

            FROM module_module_group mmg,  module_layout ml

           WHERE mmg.module_group_pk1 = new.module_group_pk1

             AND mmg.module_pk1 = ml.module_pk1

             AND ml.layout_pk1 = currow.default_layout_pk1

             AND mmg.module_pk1 NOT IN (

                       SELECT module_pk1

                         FROM module_layout

                        WHERE layout_pk1=currow.layout_pk1);


   FOR currow1 in lmg_cur1


       temp_pk1 :=  module_layout_cr

                     (module_pk1 := currow1.module_pk1,

                      layout_pk1 := currow.layout_pk1,

                      column_number := currow1.column_number,

                      "position" := currow1.position1,

                      manually_added_ind := 'N',

                      minimized_ind := 'N'







$$ LANGUAGE plpgsql;


After you hit the Enter key, you should see CREATE FUNCTION displayed in the terminal window. You can check that you've actually changed the function with the following:

BBLEARN=# select proname,prosrc from pg_proc where proname='layout_module_group_trg_ins_tf';


Second copy and paste the block of SQL below into the window you have open, right after the #, then hit enter. Copy and paste everything up to and including the line $$ LANGUAGE plpgsql;


CREATE OR REPLACE FUNCTION users_portal_trg_upd_tf() RETURNS trigger AS $$


     lmg_pk1 bigint;

     v_old_ir_pk1 bigint;

     v_users_pk1 bigint;

     v_count int;

     cursor0001 cursor for

            SELECT l.pk1 layout_pk1, img.pk1 new_mg_pk1, dmg.pk1 old_mg_pk1, l.users_pk1 v_users_pk1 , old.institution_roles_pk1 v_old_ir_pk1

              FROM layout l, module_group img, module_group dmg

             WHERE l.users_pk1=new.pk1

               AND img.institution_roles_pk1=new.institution_roles_pk1

               AND dmg.institution_roles_pk1=old.institution_roles_pk1

               AND new.pk1=old.pk1

               AND new.institution_roles_pk1 <>old.institution_roles_pk1;


  FOR currow1 IN cursor0001


    v_count =0;

    SELECT count(*) INTO v_count FROM layout_module_group

              where layout_pk1=currow1.layout_pk1 and module_group_pk1=currow1.new_mg_pk1;


    IF(v_count=0) THEN

      lmg_pk1 := layout_module_group_cr

       (layout_pk1 := currow1.layout_pk1,

       module_group_pk1 := currow1.new_mg_pk1


    END IF;


   v_count =0;

    SELECT count(*) INTO v_count FROM user_roles

            where users_pk1=currow1.v_users_pk1 and institution_roles_pk1=currow1.v_old_ir_pk1;


    IF(v_count=0) THEN

       perform layout_module_group_rm

         (layout_pk1 := currow1.layout_pk1,

         module_group_pk1 := currow1.old_mg_pk1


    END IF;





$$ LANGUAGE plpgsql;


9. Quit Postgres



10. Exit back to root.

$ exit


11. Start Learn. Tail the appropriate logs/tomcat/stdout-stderr file to ensure Learn is really started, then test.