CREATE OR REPLACE FUNCTION cargotel_common.copy_orders_to_dev() RETURNS trigger LANGUAGE plpython3u SECURITY DEFINER AS $function$
    import psycopg2
    import psycopg2.extras
    from psycopg2 import sql
    from inspect import currentframe
    import io
    import syslog
    import sys
    import time
    import datetime
    database = "cargotel"
    dev_database = "cargotel_dev"
            
    def get_linenumber():
        cf = currentframe()
        return cf.f_back.f_lineno + 1
        
    def logging(comment):
        global database
        process_name = "copy_orders_to_dev"
        plpy.notice(comment)        
        syslog.openlog(process_name,logoption=syslog.LOG_PID, facility=syslog.LOG_LOCAL0)
        #syslog.syslog("%s,%s,%s,%s" % (datetime.datetime.fromtimestamp(time.clock_gettime(0)),database,process_name,comment))
        syslog.syslog("%s,%s,%s,%s" % (plpy.execute("select clock_timestamp()")[0]["clock_timestamp"],(plpy.execute("select current_database()")[0]["current_database"]),process_name,comment))
        syslog.closelog()
        return
    
    def get_fk_tables(schema,table_name):
        fk_query = """
            truncate cargotel_common.foreign_keys;
            insert into cargotel_common.foreign_keys (
                referencing_schema,
                referencing_table,
                referencing_column,
                referenced_schema,
                referenced_table,
                referenced_column
            ) select
                schema1,
                table1,
                column1,
                schema2,
                table2,
                column2
            from cargotel_common.show_foreign_keys('%s'::text,'%s'::text) 
            order by schema1,table1
            on conflict do nothing
        """ % (schema,table_name)
        logging(fk_query)
        prod_fks_cursor.execute(fk_query)
        extras_query = """
            insert into cargotel_common.foreign_keys (
                referencing_schema,
                referencing_table,
                referencing_column,
                referenced_schema,
                referenced_table,
                referenced_column
            ) values (
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s'
            ) on conflict do nothing
        """ % (schema,'load_flags','load_id',schema,'load','id')
        logging(extras_query)
        prod_fks_cursor.execute(extras_query)
        extras_query = """
            insert into cargotel_common.foreign_keys (
                referencing_schema,
                referencing_table,
                referencing_column,
                referenced_schema,
                referenced_table,
                referenced_column
            ) values (
                '%s',
                '%s',
                '%s',
                '%s',
                '%s',
                '%s'
            ) on conflict do nothing
        """ % (schema,'load_flags2','load_id',schema,'load','id')
        logging(extras_query)
        prod_fks_cursor.execute(extras_query)
        prod_database_connection.commit()
        return prod_fks_cursor.rowcount
    
    def get_referenced_fk_tables(schema,table_name):
            fk_query = """
                truncate cargotel_common.referenced_foreign_keys;
                insert into cargotel_common.referenced_foreign_keys (
                    referencing_schema,
                    referencing_table,
                    referencing_column,
                    referenced_schema,
                    referenced_table,
                    referenced_column
                ) select
                    schema1,
                    table1,
                    column1,
                    schema2,
                    table2,
                    column2
                from cargotel_common.show_referenced_foreign_keys('%s'::text,'%s'::text)
                order by schema1,table1
                on conflict do nothing
            """ % (schema,table_name )
            logging(fk_query)
            prod_fks_cursor.execute(fk_query)
            prod_database_connection.commit()
            return prod_fks_cursor.rowcount
    
       
    def copy_orders_to_dev():
        tables_with_errors = []
        return_string = ''
        #get all rows that are ready to copy
        schemas_query = "select id, schema,order_id from cargotel_common.copy_orders_to_dev where database = '%s' and copy_completed = 'f' order by schema" % (database)
        logging("%s, %s" % (get_linenumber(),schemas_query))
        cursor = plpy.cursor(schemas_query)
        while True:
            schema_rows = cursor.fetch(100)
            if not schema_rows:
                break
            for schemas in schema_rows:
                row_id = schemas["id"]
                schema = schemas["schema"]
                order_id = schemas["order_id"]
                logging("%s, %s" % (get_linenumber(),schema))
                #fill the foreign key tables
                num_fks = get_fk_tables(schema,"load")
                logging("%s,Number of rows in foreign_keys table: %s" % (get_linenumber(),num_fks))
                num_rfks = get_referenced_fk_tables(schema,"load")
                logging("%s,Number of rows in referenced_foreign_keys table: %s" % (get_linenumber(),num_rfks))
                prod_orders_first_query = """
                    select array_to_string(array(
                    select id from %s.load where id in (%s) 
                    union
                    select id from %s.load where parent_id in (%s) and parent_id is not null
                    union
                    select id from %s.load where split_parent_id in (%s) and split_parent_id is not null
                    union
                    select id from %s.load where bundle_parent_id in (%s) and bundle_parent_id is not null
                    order by id),',')
                """ % (schema,order_id,schema,order_id,schema,order_id,schema,order_id)
                logging(prod_orders_first_query)
                #prod_orders_cursor.execute(prod_orders_query)
                #order_ids = prod_orders_cursor.fetchall()
                first_order_ids = plpy.execute(prod_orders_first_query)[0]["array_to_string"]
                logging(first_order_ids)
                prod_orders_second_query = """
                    select id from %s.load where id in (%s) 
                    union
                    select parent_id from %s.load where id in (%s) and parent_id is not null
                    union
                    select split_parent_id from %s.load where id in (%s) and split_parent_id is not null
                    union
                    select bundle_parent_id from %s.load where id in (%s) and bundle_parent_id is not null
                    order by id
                """ % (schema,first_order_ids,schema,first_order_ids,schema,first_order_ids,schema,first_order_ids)
                logging(prod_orders_second_query)
                #prod_orders_cursor.execute(prod_orders_query)
                #order_ids = prod_orders_cursor.fetchall()
                order_ids = plpy.execute(prod_orders_second_query)
                #logging("%s, %s" % (get_linenumber(),order_ids))
                if not order_ids:
                    break
                for orders in order_ids:
                    order_id = int(orders["id"])
                    logging("%s, %s" % (get_linenumber(),order_id))
                    #copy the orders from load table to dev
                    table_name = "load"
                    column_name = "id"
                    csv_buf = io.StringIO()
                    size = size = 3**20
                    logging("%s,Starting copy of load table..." % (get_linenumber()))
                    #check for identical columns between prod and dev
                    column_count_query = "select count(column_name) from information_schema.columns where table_schema = '%s' and table_name = '%s'" % (schema,table_name)
                    prod_count_cursor.execute(column_count_query)
                    prod_count = prod_count_cursor.fetchone()[0]
                    dev_count_cursor.execute(column_count_query)
                    dev_count = dev_count_cursor.fetchone()[0]
                    if (prod_count == dev_count) or (prod_count < dev_count):
                        use_cols = "prod"
                    else:
                        use_cols = "dev"
                    insert_column_string_query = "select * from cargotel_common.column_string('%s','%s','')" % (schema,table_name)
                    update_column_string_query = "select * from cargotel_common.column_string('%s','%s','EXCLUDED.')" % (schema,table_name)
                    if use_cols == "prod":
                        prod_column_cursor.execute(insert_column_string_query)
                        insert_column_string = prod_column_cursor.fetchone()[0]
                        prod_column_cursor.execute(update_column_string_query)
                        update_column_string = prod_column_cursor.fetchone()[0]
                    elif use_cols == 'dev':
                        dev_column_cursor.execute(insert_column_string_query)
                        insert_column_string = dev_column_cursor.fetchone()[0]
                        dev_column_cursor.execute(update_column_string_query)
                        update_column_string = dev_column_cursor.fetchone()[0]
                    insert_columns = insert_column_string.split(",")
                    update_columns = update_column_string.split(",")
                    logging("%s, %s" % (get_linenumber(),insert_columns))
                    logging("%s, %s" % (get_linenumber(),update_columns))
                    pkey_query = "select * from cargotel_common.get_pkey('%s','%s')" % (schema,table_name)
                    dev_pkey_cursor.execute(pkey_query)
                    pkey_column = dev_pkey_cursor.fetchone()[0]
    
                    prod_copy_query = "copy (select %s from %s.%s where %s in (%s)) to stdout with csv" % (insert_column_string,schema,table_name,column_name,order_id)
                    dev_copy_query = "copy %s_temp (%s) from stdin with csv;" % (table_name,insert_column_string)
                    temp_table_query = "create temp table if not exists %s_temp (like %s.%s including all) on commit drop; truncate %s_temp;" % (table_name, schema,table_name, table_name)
                    logging("%s, %s" % (get_linenumber(),temp_table_query))
                    prod_copy_cursor = prod_database_connection.cursor()
                    dev_copy_cursor = dev_database_connection.cursor()
                    dev_copy_cursor.execute("set session_replication_role to 'replica';")
                    dev_copy_cursor.execute(temp_table_query)
                    update_columns_string = ''
                    for d,e in zip(insert_columns,update_columns):
                        #logging("%s, d = %s, e = %s" % (get_linenumber(),d,e))
                        update_columns_string = update_columns_string + d + " = " + e + ","
    
                    dev_insert_query = """
                        insert into %s.%s
                            (%s)
                        select
                            %s
                        from %s_temp
                        on conflict (%s) do update set
                            %s
                        where %s.id in (%s)
                    """ % (schema,table_name,insert_column_string,insert_column_string,table_name,pkey_column,update_columns_string[:-1],table_name,order_id)
                    prod_copy_cursor.copy_expert(prod_copy_query,csv_buf,size)
                    csv_buf.seek(0)
                    dev_copy_cursor.copy_expert(dev_copy_query,csv_buf)
                    #upsert the rows from the temp table into the real table
                    logging("%s, %s" % (get_linenumber(),dev_insert_query))
                    dev_copy_cursor.execute(dev_insert_query)
                    prod_copy_cursor.close()
                    dev_copy_cursor.close()
                    dev_database_connection.commit()
                    csv_buf.close()
                    fk_tables_query = "select referencing_table, referencing_column from cargotel_common.foreign_keys where referenced_schema = '%s' and referencing_table != 'load_user_role' order by referencing_table, referencing_column" % (schema)
                    try:
                        prod_table_cursor.execute(fk_tables_query)
                    except Exception as e:
                        logging("%s, Got error %s" %  (get_linenumber(),e))
                        sys.exit()
                    while True:
                        table_rows = prod_table_cursor.fetchall()
                        if not table_rows:
                            break
                        for t in table_rows:
                            table_name = t[0]
                            column_name = t[1]
                            logging("%s,starting copy of %s.%s" % (get_linenumber(),schema,table_name))
                            insert_count = 0
                            update_count = 0    
                            insert_count_query = "select count(*) from %s.%s where %s in (%s)" % (schema, table_name, column_name,order_id)
                            logging("%s, %s" % (get_linenumber(),insert_count_query))
                            prod_count_cursor.execute(insert_count_query)
                            prod_insert_count = prod_count_cursor.fetchone()[0]
                            dev_count_cursor.execute(insert_count_query)
                            dev_insert_count = dev_count_cursor.fetchone()[0]
                            logging("%s,Rows from prod to insert: %s, Rows in dev with same ids: %s" % (get_linenumber(),prod_insert_count, dev_insert_count))
                            if prod_insert_count > 0:
                                csv_buf = io.StringIO()
                                size = size = 3**20
                                logging("%s,Inserting %s rows..." % (get_linenumber(),insert_count))
                                #check for identical columns between prod and dev
                                column_count_query = "select count(column_name) from information_schema.columns where table_schema = '%s' and table_name = '%s'" % (schema,table_name)
                                prod_count_cursor.execute(column_count_query)
                                prod_count = prod_count_cursor.fetchone()[0]
                                dev_count_cursor.execute(column_count_query)
                                dev_count = dev_count_cursor.fetchone()[0]
                                if (prod_count == dev_count) or (prod_count < dev_count):
                                    use_cols = "prod"
                                else:
                                    use_cols = "dev"
                                logging(use_cols)
                                insert_column_string_query = "select * from cargotel_common.column_string('%s','%s','')" % (schema,table_name)
                                update_column_string_query = "select * from cargotel_common.column_string('%s','%s','EXCLUDED.')" % (schema,table_name)
                                logging("%s, %s" % (get_linenumber(),insert_column_string_query))
                                logging("%s, %s" % (get_linenumber(),update_column_string_query))
                                if use_cols == "prod":
                                    prod_column_cursor.execute(insert_column_string_query)
                                    insert_column_string = prod_column_cursor.fetchone()[0]
                                    prod_column_cursor.execute(update_column_string_query)
                                    update_column_string = prod_column_cursor.fetchone()[0]
                                elif use_cols == 'dev':
                                    dev_column_cursor.execute(insert_column_string_query)
                                    insert_column_string = dev_column_cursor.fetchone()[0]
                                    dev_column_cursor.execute(update_column_string_query)
                                    update_column_string = dev_column_cursor.fetchone()[0]
                                insert_columns = insert_column_string.split(",")
                                update_columns = update_column_string.split(",")
                                pkey_query = "select * from cargotel_common.get_pkey('%s','%s')" % (schema,table_name)
                                dev_pkey_cursor.execute(pkey_query)
                                pkey_column = dev_pkey_cursor.fetchone()[0]
                                #get all unique_indexes
                                conflict_columns_query = """
                                    select
                                        array_to_string(
                                            array(
                                                select
                                                    case
                                                        when strpos(indexdef,',') > 0 then split_part(split_part(indexdef,'(',2),')',1)
                                                        else split_part(split_part(indexdef,'(',2),')',1)
                                                    end                            
                                                from pg_indexes
                                                where schemaname = '%s' and
                                                tablename = '%s' and
                                                indexdef like 'CREATE UNIQUE%%'
                                                and indexdef not like '%%(%s)%%'
                                            ),
                                        ','
                                    )
                                """ % (schema,table_name,pkey_column)
                                logging("%s, %s" % (get_linenumber(),conflict_columns_query))
                                prod_conflict_columns_cursor = prod_database_connection.cursor()
                                prod_conflict_columns_cursor.execute(conflict_columns_query)
                                conflict_columns = prod_conflict_columns_cursor.fetchone()[0]
                                logging("%s, %s" % (get_linenumber(),conflict_columns))
                                prod_copy_query = "copy (select %s from %s.%s where %s in (%s)) to stdout with csv" % (insert_column_string,schema,table_name,column_name,order_id)
                                dev_copy_query = "copy %s_temp (%s) from stdin with csv;" % (table_name,insert_column_string)
                                logging("%s, %s" % (get_linenumber(),prod_copy_query))
                                logging("%s, %s" % (get_linenumber(),dev_copy_query))
                                update_columns_string = ''
                                for d,e in zip(insert_columns,update_columns):
                                    update_columns_string = update_columns_string + d + " = " + e + ","
                                prod_copy_cursor = prod_database_connection.cursor()
                                dev_copy_cursor = dev_database_connection.cursor()
                                dev_copy_cursor.execute("set session_replication_role to 'replica';")
                                try:
                                    temp_table_query = "create temp table if not exists %s_temp (like %s.%s including all) on commit drop; truncate %s_temp;" % (table_name, schema,table_name,table_name)
                                    logging("%s, %s" % (get_linenumber(),temp_table_query))
                                    dev_copy_cursor.execute(temp_table_query)
                                except Exception as e:
                                    logging("%s, %s" % (get_linenumber(),e))
                                    tables_with_errors.append(table_name)
                                if len(conflict_columns):
                                    dev_insert_query = """
                                        insert into %s.%s
                                            (%s)
                                        OVERRIDING SYSTEM VALUE
                                        select
                                            %s
                                        from %s_temp
                                        on conflict (%s) do update set
                                            %s
                                        where %s.%s in (%s)
                                    """ % (schema,table_name,insert_column_string,insert_column_string,table_name,conflict_columns,update_columns_string[:-1],table_name,column_name,order_id)
                                else:
                                    dev_insert_query = """
                                        insert into %s.%s
                                            (%s)
                                        OVERRIDING SYSTEM VALUE
                                        select
                                            %s
                                        from %s_temp
                                        on conflict (%s) do update set
                                            %s
                                        where %s.%s in (%s)
                                    """ % (schema,table_name,insert_column_string,insert_column_string,table_name,pkey_column,update_columns_string[:-1],table_name,column_name,order_id)
    
                                logging("%s, %s" % (get_linenumber(),dev_insert_query))
                                prod_copy_cursor.copy_expert(prod_copy_query,csv_buf,size)
                                csv_buf.seek(0)
                                dev_copy_cursor.copy_expert(dev_copy_query,csv_buf)
                                #upsert the rows from the temp table into the real table
                                dev_copy_cursor.execute(dev_insert_query)
                            prod_copy_cursor.close()
                            dev_copy_cursor.close()
                            dev_database_connection.commit()
                            csv_buf.close()
                        #get the referenced_foreign_keys
                        rfk_tables_query = "select referencing_table, referencing_column,referenced_table, referenced_column from cargotel_common.referenced_foreign_keys where referenced_schema = '%s' and referenced_table != 'load_user_role' order by referenced_table, referenced_column" % (schema)
                        logging("%s, %s" % (get_linenumber(),rfk_tables_query))
                        try:
                            prod_table_cursor.execute(rfk_tables_query)
                        except Exception as e:
                            logging("%s, Got error %s" %  (get_linenumber(),e))
                            sys.exit()
                        while True:
                            table_rows = prod_table_cursor.fetchall()
                            if not table_rows:
                                break
                            for t in table_rows:
                                referencing_table_name = t[0]
                                referencing_column_name = t[1]
                                referenced_table_name = t[2] 
                                referenced_column_name = t[3]
                                if referenced_table_name != 'users':
                                    logging("Line number: %s, referencing_table_name = %s, referencing_column_name = %s, referenced_table_name = %s, referenced_column_name = %s" % (get_linenumber(),referencing_table_name,referencing_column_name,referenced_table_name,referenced_column_name))
                                    logging("Line number: %s, starting copy of %s.%s" % (get_linenumber(),schema,referenced_table_name))
                                    insert_count = 0
                                    update_count = 0    
                                    insert_count_query = "select count(*) from %s.%s where %s in (select %s from %s.%s where %s in (%s))" % (schema, referenced_table_name, referenced_column_name, referencing_column_name, schema,referencing_table_name, referenced_column_name,order_id)
                                    logging("%s, %s" % (get_linenumber(),insert_count_query))
                                    prod_count_cursor.execute(insert_count_query)
                                    prod_insert_count = prod_count_cursor.fetchone()[0]
                                    logging("%s, %s" % (get_linenumber(),prod_insert_count))
                                    dev_count_cursor.execute(insert_count_query)
                                    dev_insert_count = dev_count_cursor.fetchone()[0]
                                    logging("%s,Rows from prod to insert: %s, Rows in dev with same ids: %s" % (get_linenumber(),prod_insert_count, dev_insert_count))
                                    if prod_insert_count > 0:
                                        csv_buf = io.StringIO()
                                        size = size = 3**20
                                        logging("%s,Inserting %s rows..." % (get_linenumber(),insert_count))
                                        #check for identical columns between prod and dev
                                        column_count_query = "select count(column_name) from information_schema.columns where table_schema = '%s' and table_name = '%s'" % (schema,referenced_table_name)
                                        prod_count_cursor.execute(column_count_query)
                                        prod_count = prod_count_cursor.fetchone()[0]
                                        dev_count_cursor.execute(column_count_query)
                                        dev_count = dev_count_cursor.fetchone()[0]
                                        if (prod_count == dev_count) or (prod_count < dev_count):
                                            use_cols = "prod"
                                        else:
                                            use_cols = "dev"
                                        insert_column_string_query = "select * from cargotel_common.column_string('%s','%s','')" % (schema,referenced_table_name)
                                        update_column_string_query = "select * from cargotel_common.column_string('%s','%s','EXCLUDED.')" % (schema,referenced_table_name)
                                        logging("%s, %s" % (get_linenumber(),insert_column_string_query))
                                        logging("%s, %s" % (get_linenumber(),update_column_string_query))
                                        if use_cols == "prod":
                                            prod_column_cursor.execute(insert_column_string_query)
                                            insert_column_string = prod_column_cursor.fetchone()[0]
                                            prod_column_cursor.execute(update_column_string_query)
                                            update_column_string = prod_column_cursor.fetchone()[0]
                                        elif use_cols == 'dev':
                                            dev_column_cursor.execute(insert_column_string_query)
                                            insert_column_string = dev_column_cursor.fetchone()[0]
                                            dev_column_cursor.execute(update_column_string_query)
                                            update_column_string = dev_column_cursor.fetchone()[0]
                                        insert_columns = insert_column_string.split(",")
                                        update_columns = update_column_string.split(",")
                                        pkey_query = "select * from cargotel_common.get_pkey('%s','%s')" % (schema,referenced_table_name)
                                        dev_pkey_cursor.execute(pkey_query)
                                        pkey_column = dev_pkey_cursor.fetchone()[0]
            
                                        logging("Line number: %s, referencing_table_name = %s, referencing_column_name = %s, referenced_table_name = %s, referenced_column_name = %s" % (get_linenumber(),referencing_table_name,referencing_column_name,referenced_table_name,referenced_column_name))
                                        prod_copy_query = "copy (select %s from %s.%s where %s in (select %s from %s.%s where %s in (%s))) to stdout with csv" % (insert_column_string,schema,referenced_table_name,referenced_column_name,referencing_column_name,schema,referencing_table_name,referenced_column_name,order_id)
                                        logging("%s, %s" % (get_linenumber(),prod_copy_query))
                                        dev_copy_query = "copy %s_temp (%s) from stdin with csv;" % (referenced_table_name,insert_column_string)
                                        logging("%s, %s" % (get_linenumber(),dev_copy_query))
                                        temp_table_query = "create temp table if not exists %s_temp (like %s.%s including all) on commit drop; truncate %s_temp;" % (referenced_table_name, schema,referenced_table_name,referenced_table_name)
                                        logging("%s, %s" % (get_linenumber(),temp_table_query))
            
                                        prod_copy_cursor = prod_database_connection.cursor()
                                        dev_copy_cursor = dev_database_connection.cursor()
                                        dev_copy_cursor.execute("set session_replication_role to 'replica';")
                                        try:
                                            dev_copy_cursor.execute(temp_table_query)
                                        except Exception as e:
                                            logging("%s,insert error! %s" % (get_linenumber(),e))
                                            tables_with_errors.append(table_name)
                                        update_columns_string = ''
                                        for d,e in zip(insert_columns,update_columns):
                                            update_columns_string = update_columns_string + d + " = " + e + ","
                                        dev_insert_query = """
                                            insert into %s.%s
                                                (%s)
                                            OVERRIDING SYSTEM VALUE
                                            select
                                                %s
                                            from %s_temp
                                            on conflict (%s) do update set
                                                %s
                                            where %s.%s in (select %s from %s.%s where %s in (%s))
                                        """ % (schema,referenced_table_name,insert_column_string,insert_column_string,referenced_table_name,pkey_column,update_columns_string[:-1],referenced_table_name,referenced_column_name,referencing_column_name,schema, referencing_table_name,pkey_column,order_id)
                                        logging("%s, %s" % (get_linenumber(),dev_insert_query))
                                        prod_copy_cursor.copy_expert(prod_copy_query,csv_buf,size)
                                        csv_buf.seek(0)
                                        dev_copy_cursor.copy_expert(dev_copy_query,csv_buf)
                                        #upsert the rows from the temp table into the real table
                                        dev_copy_cursor.execute(dev_insert_query)
                                        prod_copy_cursor.close()
                                        dev_copy_cursor.close()
                                        dev_database_connection.commit()
                                        csv_buf.close()
                                else:
                                    #users if there is a conflict with ids and usernames we'll need to update the table on dev to match the username instead of updating the id
                                    #get the ids from prod and dev
                                    #referencing_table_name = load, referencing_column_name = last_upd_user, referenced_table_name = users, referenced_column_name = id
                                    prod_username_query = "select username from %s.users where %s = (select %s from %s.%s where %s = %s)" % (schema,referenced_column_name,referencing_column_name,schema,referencing_table_name, pkey_column,order_id)
                                    logging("%s, %s" % (get_linenumber(),prod_username_query))
                                    prod_query_cursor.execute(prod_username_query) 
                                    prod_username_check = prod_query_cursor.fetchone()
                                    logging("%s, prod_username_check = %s" % (get_linenumber(),prod_username_check))
                                    if prod_username_check:
                                        prod_username = prod_username_check[0]                                        
                                        #is this user in dev?
                                        dev_username_query = "select %s from %s.%s where username = '%s'" % (referenced_column_name, schema,referenced_table_name,prod_username)
                                        logging("%s, %s" % (get_linenumber(),dev_username_query))
                                        dev_query_cursor.execute(dev_username_query)
                                        dev_username_id_check = dev_query_cursor.fetchone()
                                        logging("%s, %s" % (get_linenumber(),dev_username_id_check))
                                        if dev_username_id_check != None:
                                            dev_username_id = dev_username_id_check[0]
                                            #user exists, update the referencing table column instead
                                            pkey_query = "select * from cargotel_common.get_pkey('%s','%s')" % (schema,referencing_table_name)
                                            dev_pkey_cursor.execute(pkey_query)
                                            pkey_column = dev_pkey_cursor.fetchone()[0]
                                            dev_update_query = "update %s.%s set %s = %s where %s = %s" % (schema,referencing_table_name,referencing_column_name,dev_username_id,pkey_column,order_id)
                                            logging("%s, %s" % (get_linenumber(),dev_update_query))
                                            dev_query_cursor.execute(dev_update_query)
                                        else:
                                            #user doesn't exist in dev, go ahead and insert
                                            logging("Line number: %s, referencing_table_name = %s, referencing_column_name = %s, referenced_table_name = %s, referenced_column_name = %s" % (get_linenumber(),referencing_table_name,referencing_column_name,referenced_table_name,referenced_column_name))
                                            logging("Line number: %s, starting copy of %s.%s" % (get_linenumber(),schema,referenced_table_name))
                                            insert_count = 0
                                            update_count = 0    
                                            insert_count_query = "select count(*) from %s.%s where %s in (select %s from %s.%s where %s in (%s))" % (schema, referenced_table_name, referenced_column_name, referencing_column_name, schema,referencing_table_name, referenced_column_name,order_id)
                                            logging("%s, %s" % (get_linenumber(),insert_count_query))
                                            prod_count_cursor.execute(insert_count_query)
                                            prod_insert_count = prod_count_cursor.fetchone()[0]
                                            logging("%s, %s" % (get_linenumber(),prod_insert_count))
                                            dev_count_cursor.execute(insert_count_query)
                                            dev_insert_count = dev_count_cursor.fetchone()[0]
                                            logging("%s,Rows from prod to insert: %s, Rows in dev with same ids: %s" % (get_linenumber(),prod_insert_count, dev_insert_count))
                                            if prod_insert_count > 0:
                                                csv_buf = io.StringIO()
                                                size = size = 3**20
                                                logging("%s,Inserting %s rows..." % (get_linenumber(),insert_count))
                                                #check for identical columns between prod and dev
                                                column_count_query = "select count(column_name) from information_schema.columns where table_schema = '%s' and table_name = '%s'" % (schema,referenced_table_name)
                                                prod_count_cursor.execute(column_count_query)
                                                prod_count = prod_count_cursor.fetchone()[0]
                                                dev_count_cursor.execute(column_count_query)
                                                dev_count = dev_count_cursor.fetchone()[0]
                                                if (prod_count == dev_count) or (prod_count < dev_count):
                                                    use_cols = "prod"
                                                else:
                                                    use_cols = "dev"
                                                insert_column_string_query = "select * from cargotel_common.column_string('%s','%s','')" % (schema,referenced_table_name)
                                                update_column_string_query = "select * from cargotel_common.column_string('%s','%s','EXCLUDED.')" % (schema,referenced_table_name)
                                                logging("%s, %s" % (get_linenumber(),insert_column_string_query))
                                                logging("%s, %s" % (get_linenumber(),update_column_string_query))
                                                if use_cols == "prod":
                                                    prod_column_cursor.execute(insert_column_string_query)
                                                    insert_column_string = prod_column_cursor.fetchone()[0]
                                                    prod_column_cursor.execute(update_column_string_query)
                                                    update_column_string = prod_column_cursor.fetchone()[0]
                                                elif use_cols == 'dev':
                                                    dev_column_cursor.execute(insert_column_string_query)
                                                    insert_column_string = dev_column_cursor.fetchone()[0]
                                                    dev_column_cursor.execute(update_column_string_query)
                                                    update_column_string = dev_column_cursor.fetchone()[0]
                                                insert_columns = insert_column_string.split(",")
                                                update_columns = update_column_string.split(",")
                                                pkey_query = "select * from cargotel_common.get_pkey('%s','%s')" % (schema,referenced_table_name)
                                                dev_pkey_cursor.execute(pkey_query)
                                                pkey_column = dev_pkey_cursor.fetchone()[0]
                    
                                                logging("Line number: %s, referencing_table_name = %s, referencing_column_name = %s, referenced_table_name = %s, referenced_column_name = %s" % (get_linenumber(),referencing_table_name,referencing_column_name,referenced_table_name,referenced_column_name))
                                                prod_copy_query = "copy (select %s from %s.%s where %s in (select %s from %s.%s where %s in (%s))) to stdout with csv" % (insert_column_string,schema,referenced_table_name,referenced_column_name,referencing_column_name,schema,referencing_table_name,referenced_column_name,order_id)
                                                logging("%s, %s" % (get_linenumber(),prod_copy_query))
                                                dev_copy_query = "copy %s_temp (%s) from stdin with csv;" % (referenced_table_name,insert_column_string)
                                                logging("%s, %s" % (get_linenumber(),dev_copy_query))
                                                temp_table_query = "create temp table if not exists %s_temp (like %s.%s including all) on commit drop; truncate %s_temp;" % (referenced_table_name, schema,referenced_table_name,referenced_table_name)
                                                logging("%s, %s" % (get_linenumber(),temp_table_query))
                    
                                                prod_copy_cursor = prod_database_connection.cursor()
                                                dev_copy_cursor = dev_database_connection.cursor()
                                                dev_copy_cursor.execute("set session_replication_role to 'replica';")
                                                try:
                                                    dev_copy_cursor.execute(temp_table_query)
                                                except Exception as e:
                                                    logging("%s,insert error! %s" % (get_linenumber(),e))
                                                    tables_with_errors.append(table_name)
                                                update_columns_string = ''
                                                for d,e in zip(insert_columns,update_columns):
                                                    update_columns_string = update_columns_string + d + " = " + e + ","
                                                dev_insert_query = """
                                                    insert into %s.%s
                                                        (%s)
                                                    OVERRIDING SYSTEM VALUE
                                                    select
                                                        %s
                                                    from %s_temp
                                                    on conflict (%s) do update set
                                                        %s
                                                    where %s.%s in (select %s from %s.%s where %s in (%s))
                                                """ % (schema,referenced_table_name,insert_column_string,insert_column_string,referenced_table_name,pkey_column,update_columns_string[:-1],referenced_table_name,referenced_column_name,referencing_column_name,schema, referencing_table_name,pkey_column,order_id)
                                                logging("%s, %s" % (get_linenumber(),dev_insert_query))
                                                prod_copy_cursor.copy_expert(prod_copy_query,csv_buf,size)
                                                csv_buf.seek(0)
                                                dev_copy_cursor.copy_expert(dev_copy_query,csv_buf)
                                                #upsert the rows from the temp table into the real table
                                                dev_copy_cursor.execute(dev_insert_query)
                                                prod_copy_cursor.close()
                                                dev_copy_cursor.close()
                                                dev_database_connection.commit()
                                                csv_buf.close()
    
    
                    #reset sequences so we don't have problems in dev
                    dev_sequence_cursor = dev_database_connection.cursor()
                    dev_sequence_cursor.execute("select * from cargotel_common.set_sequences('%s')" % (schema))
                    dev_database_connection.commit()
                    #mark our orders processed
                    done_query = "update cargotel_common.copy_orders_to_dev set copy_completed = 't', copy_completed_timestamp = clock_timestamp() where id = %s" % (row_id)    
                    logging("%s, %s" % (get_linenumber(),done_query))
                    logging(done_query)
                    plpy.execute(done_query)
                    prod_database_connection.commit()
                    dev_database_connection.commit()
                
    process_name = "copy_orders_to_dev"
    debug = True
    
    prod_database_connection_string = "host='pgbouncer' dbname='%s' application_name = '%s'" % (database,process_name)
    logging("%s,Connecting to prod_database: %s" % (get_linenumber(),prod_database_connection_string))
    try:
        prod_database_connection = psycopg2.connect(prod_database_connection_string)
    except:
        logging("%s,Can't connect to %s" % (get_linenumber(),prod_database_connection_string))
        exit()
    #connected
     
    #prod_database_connection.set_session(autocommit=True)
    prod_schemas_cursor = prod_database_connection.cursor()
    prod_orders_cursor = prod_database_connection.cursor()
    prod_copy_cursor = prod_database_connection.cursor()
    prod_fks_cursor = prod_database_connection.cursor()
    prod_table_cursor = prod_database_connection.cursor()
    prod_count_cursor = prod_database_connection.cursor()
    prod_column_cursor = prod_database_connection.cursor()
    prod_update_cursor = prod_database_connection.cursor()
    prod_query_cursor = prod_database_connection.cursor()
    prod_pkey_cursor = prod_database_connection.cursor()
    prod_schema_cursor = prod_database_connection.cursor()
    prod_done_cursor = prod_database_connection.cursor()
    prod_query_cursor = prod_database_connection.cursor()
    
    dev_database_connection_string = "host='pgbouncer' dbname='%s' application_name = '%s'" % (dev_database,process_name)
    logging("%s,Connecting to dev_database: %s" % (get_linenumber(),dev_database_connection_string))
    try:
        dev_database_connection = psycopg2.connect(dev_database_connection_string)
    except:
        logging("%s,Can't connect to %s" % (get_linenumber(),dev_database_connection_string))
        exit()
    #connected
     
    #dev_database_connection.set_session(autocommit=True)
    dev_schema_cursor = dev_database_connection.cursor()
    dev_count_cursor = dev_database_connection.cursor()    
    dev_copy_cursor = dev_database_connection.cursor()    
    dev_column_cursor = dev_database_connection.cursor()
    dev_pkey_cursor = dev_database_connection.cursor()
    dev_query_cursor = dev_database_connection.cursor()
    
    logging("%s,%s" % (get_linenumber(),TD))
    copy_orders_to_dev()
$function$