×
DVBA Home
Developer AMI
DVM

Solution to a PostgreSQL Error In the Developer Virtual Machine

Author: Mark Bykerk Kauffman
Categories: []
Tags: [‘postgresql’, ‘developer virtual machine’, ‘dvm’, ‘developer’]


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
    # ./ServiceController.sh 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”. BBLEARN=#


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 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 LANGUAGE plpgsql;


9. Quit Postgres

BBLEARN=#\q


10. Exit back to root.

$ exit ```

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