Grouper Users - Open Discussion List

["Redman, Chad" <>]

Ah, this seems harder to do in postgres procedural code than it is in Oracle. 
Here is a python script that may work. If the issue is with the large 
rollback size, this may be faster. If it's just that updating individual rows 
is slow, it probably won't improve performance.


######################################
#!/usr/bin/env python

# Based on:
# incremental commits
# 2008 kcg
# http://www.kennygorman.com/incremental-commits-in-postgresql

# If not installed: `pip install psycopg2'
import psycopg2
import time

INCREMENTAL_COMMIT_SIZE = 1000 # number of rows
THROTTLE_TIME = 0 # seconds

connectstr = "host=192.168.99.100 dbname=grouper user=grouper port=5432 
password=mysecretpassword"
handle = psycopg2.connect(connectstr)
cursor = handle.cursor()
cursor2 = handle.cursor()
sql = "select id from GROUPER_AUDIT_ENTRY where act_as_member_id is null and 
logged_in_member_id is not null"
cursor.execute(sql)

num_updated = 0
while 1:
    output = cursor.fetchmany(INCREMENTAL_COMMIT_SIZE)
    
    if not output:
        break
    for row in output:
    
        # update table
        sql = "update grouper_audit_entry set act_as_member_id = 
logged_in_member_id where id = %s"
        cursor2.execute(sql, ([row[0]]))

        num_updated += 1
    
    # commit, invoked every incremental commit size
    print "Committing -- cumulative rows updated: %d" % num_updated
    handle.commit()
    time.sleep(THROTTLE_TIME)

handle.commit()
######################################



Alternatively, you could copy the whole table with the transformation into a 
temp table, truncate the audit entry table, then copy it back. I'm not that 
familiar with postgres, but this may be faster than individual row updates.

--------------------------------
select count(*) from grouper_audit_entry where act_as_member_id is null and 
logged_in_member_id is not null;

create table tmp as select
  coalesce(act_as_member_id, logged_in_member_id) AS act_as_member_id,
  audit_type_id,
  context_id,
  created_on,
  description,
  env_name,
  grouper_engine,
  grouper_version,
  hibernate_version_number,
  id,
  int01,
  int02,
  int03,
  int04,
  int05,
  last_updated,
  logged_in_member_id,
  server_host,
  string01,
  string02,
  string03,
  string04,
  string05,
  string06,
  string07,
  string08,
  user_ip_address,
  duration_microseconds,
  query_count,
  server_user_name
from grouper_audit_entry;

select 'should be zero', count(*) from tmp where act_as_member_id is null and 
logged_in_member_id is not null;

truncate table grouper_audit_entry;

insert into grouper_audit_entry
  select * from tmp;
--------------------------------



But definitely archive the existing grouper_audit_entry before trying 
anything.

-Chad





-----Original Message-----
From: Jim Fox 
[mailto:]
 
Sent: Monday, July 16, 2018 2:01 PM
To: Hyzer, Chris 
<>
Cc: Redman, Chad 
<>;
 

Subject: RE: [grouper-users] upgrade db from 28 to 29 seems to hang


> 
> Weve switched the grouper built in daemon queries to select all the data 
> that needs updating, then do the updates for ~200 rows at a time (since 200 
> is max bind var limit in some DBs).  We haven't done this for the DDL 
> upgrades but maybe we should discuss it.  So you could use a procedural 
> block to do this, do you need our help to code this?
>

I can delete the update from the ddl script, but if you could point me to 
the '200 row at a time' solution that's be great.

Jim