func setupDatabaseListener() { if !checkDatabaseHandleValid(db) { return } //create/replace function for notifyPhoneNumber() if _, err := db.Exec(`CREATE or REPLACE FUNCTION notifyPhoneNumber() RETURNS trigger AS $$ BEGIN IF TG_OP='DELETE' THEN EXECUTE FORMAT('NOTIFY notifyphonenumber, ''%s''', OLD.PhoneNumber); ELSE EXECUTE FORMAT('NOTIFY notifyphonenumber, ''%s''', NEW.PhoneNumber); END IF; RETURN NULL; END; $$ LANGUAGE plpgsql;`); err != nil { log.Println(err) } else { log.Println("Successfully create/replace function notifyPhoneNumber()") } //check for trigger existence var triggerExist bool if err := db.QueryRow(`SELECT EXISTS( SELECT 1 FROM pg_trigger WHERE tgname='inprogresschange')`).Scan(&triggerExist); err != nil { log.Println(err) } if !triggerExist { if _, err := db.Exec(`CREATE TRIGGER inprogresschange AFTER INSERT OR UPDATE OR DELETE ON inprogress FOR EACH ROW EXECUTE PROCEDURE notifyPhoneNumber();`); err != nil { log.Println(err) } else { log.Println("Successfully create trigger inprogresschange") } } else { log.Println("Trigger inprogresschange exists.") } //Create handler for logging listener errors reportProblem := func(ev pq.ListenerEventType, err error) { if err != nil { fmt.Println(err.Error()) } } //Listen for table updates var listenerObj *pq.Listener listenerObj = pq.NewListener(os.Getenv("DATABASE_URL"), 10*time.Second, time.Minute, reportProblem) err := listenerObj.Listen("notifyphonenumber") if err != nil { log.Println(err) } //Find our session PID so we can ignore notifications from ourselves var pid int fmt.Println("Getting session PID...") if rows, err := db.Query(`SELECT * FROM pg_stat_activity WHERE pid = pg_backend_pid();`); err != nil { log.Println(err) } else { var i interface{} //empty interface to read unneeded columns into for rows.Next() { if err := rows.Scan(&i, &i, &pid, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i, &i); err != nil { log.Println(err) } else { fmt.Println("Session PID:", pid) } } } //Monitor for noitification in background go func() { for { var notificationObj *pq.Notification notificationObj = <-listenerObj.Notify fmt.Printf("Backend PID %v\nChannel %v\nPayload %v\n", notificationObj.BePid, notificationObj.Channel, notificationObj.Extra) //Get updated row from database if the notifying PID is not this instance's PID if pid != notificationObj.BePid { if updatedRows := selectRowsFromTableByPhoneNumber("inprogress", notificationObj.Extra); updatedRows == nil { log.Println(err) } else { //We handle the possibility of deleted rows in laod pickups rows into memory since we can only enumerate over rows object once loadPickupRowsIntoMemory(&pickups, updatedRows, notificationObj) } } } }() }