Hi,
Not sure what is wrong the way we are doing migrations for trigger on a
large table ( table is about 600 GB, which we tried to modify the trigger ).
DDL for trigger is attached and the migration script is attached as well.
On a high level -- we do a create and replace of trigger for the upgrade
and downgrade script.
What is odd is that when we run the trigger directly on the DB [same
content of trigger creation present in the ddl.py], it modifies the trigger
within a few seconds - 2 or 3 seconds, but when I run this via the
migrations the whole DB just hangs and we have to kill the migrations.
We get a bunch of such errors, as shown below. We have a decent amount of
load on the DB hitting the dr table, but then the direct wirting of SQL
query works, where as running the migration hangs the system.
process 10775 still waiting for AccessShareLock on relation 16538 of
database 16385 after 1000.160 ms
PG DB - version 9.3 running on linux.
alembic==0.7.6
SQLAlchemy==1.0.6
I am starting to go over the documentation to see what is the best practice
to run trigger changes, or FK changes on large tables. If there is a better
approach than what I am using, would like to learn about it.
Thanks
Ashish
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy-alembic" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy-alembic+unsubscr...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
"""
Revision ID: 52aa1348e164
Revises: 23434d025508
Create Date: 2016-08-29 12:53:07.695000
"""
# revision identifiers, used by Alembic.
revision = '52aa1348e164'
down_revision = '23434d025508'
from alembic import op
import sqlalchemy as sa
from dataclass.dr_ddl import add_locations_mapping_trigger
def upgrade():
op.execute(add_locations_mapping_trigger.creater)
pass
def downgrade():
op.execute(add_locations_mapping_trigger.destroyer)
pass
#Below is dr_ddl.py
class TriggerFinger(object):
def __init__(self, creater, destroyer):
self.creater = creater
self.destroyer = destroyer
def upgrade(self):
return self.creater
def downgrade(self):
return self.destroyer
add_locations_mapping_trigger = TriggerFinger(
"""
CREATE OR REPLACE FUNCTION add_locations_mapping_trigger() RETURNS trigger LANGUAGE plpgsql
AS $function$
DECLARE
_record RECORD;
X_location_id TEXT;
Y_location_id TEXT;
_radius NUMERIC;
BEGIN
IF (TG_OP = 'INSERT') THEN
_record = NEW;
_radius = 200::NUMERIC;
/* get the locations id for start_geo_loaction*/
SELECT n.id INTO X_location_id FROM locations as n WHERE n.usr_id = _record.usr_id AND( ST_DWithin(n.location,_record.start_loc_gis,_radius/111000.0)) ORDER BY last_updated DESC LIMIT 1;
SELECT n.id INTO Y_location_id FROM locations as n WHERE n.usr_id = _record.usr_id AND( ST_DWithin(n.location,_record.end_loc_gis,_radius/111000.0)) ORDER BY last_updated DESC LIMIT 1;
UPDATE drive SET start_locations_id = X_location_id WHERE usr_id = _record.usr_id AND dr_id= _record.dr_id AND start_locations_id IS NULL;
UPDATE drive SET end_locations_id = Y_location_id WHERE usr_id = _record.usr_id AND dr_id= _record.dr_id AND end_locations_id IS NULL;
END IF;
return _record;
END;
$function$
""",
"""
CREATE OR REPLACE FUNCTION add_locations_mapping_trigger() RETURNS trigger LANGUAGE plpgsql
AS $function$
DECLARE
_record RECORD;
_X_location_id TEXT;
Y_location_id TEXT;
_height NUMERIC;
BEGIN
IF (TG_OP = 'INSERT') THEN
_record = NEW;
_height = 200::NUMERIC;
/* get the locations id for start_geo_loaction*/
SELECT n.id INTO X_location_id FROM locations as n WHERE n.usr_id = _record.usr_id AND( ST_DWithin(n.location,_record.start_loc_gis,_height/111000.0)) ORDER BY updated_at DESC LIMIT 1;
SELECT n.id INTO Y_location_id FROM locations as n WHERE n.usr_id = _record.usr_id AND( ST_DWithin(n.location,_record.end_loc_gis,_height/111000.0)) ORDER BY updated_at DESC LIMIT 1;
UPDATE drive SET start_locations_id = X_location_id WHERE usr_id = _record.usr_id AND dr_id= _record.dr_id AND start_locations_id IS NULL;
UPDATE drive SET end_locations_id = Y_location_id WHERE usr_id = _record.usr_id AND dr_id= _record.dr_id AND end_locations_id IS NULL;
END IF;
return _record;
END;
$function$
"""
)
