Use different SQL for trigger detection on Postgres 9.0

2 files changed, 38 insertions, 7 deletions

diff --git a/python/skytools/dbstruct.py b/python/skytools/dbstruct.py
index f0d2d436..b9723eff 100644
--- a/python/skytools/dbstruct.py
+++ b/python/skytools/dbstruct.py
@@ -73,6 +73,11 @@ class TElem(object):
         """Return SQL statement for dropping or None of not supported."""
         return None
 
+    @classmethod
+    def get_load_sql(cls, pgver):
+        """Return SQL statement for finding objects."""
+        return self.SQL
+
 class TConstraint(TElem):
     """Info about constraint."""
     type = T_CONSTRAINT
@@ -195,11 +200,7 @@ class TRule(TElem):
 class TTrigger(TElem):
     """Info about trigger."""
     type = T_TRIGGER
-    SQL = """
-        SELECT tgname as name, pg_get_triggerdef(oid) as def
-          FROM  pg_trigger
-         WHERE tgrelid = %(oid)s AND NOT tgisconstraint
-    """
+
     def __init__(self, table_name, row):
         self.table_name = table_name
         self.name = row['name']
@@ -218,6 +219,19 @@ class TTrigger(TElem):
     def get_drop_sql(self, curs):
         return 'DROP TRIGGER %s ON %s' % (quote_ident(self.name), quote_fqident(self.table_name))
 
+    @classmethod
+    def get_load_sql(cls, pg_vers):
+        """Return SQL statement for finding objects."""
+
+        sql = "SELECT tgname as name, pg_get_triggerdef(oid) as def "\
+              "  FROM  pg_trigger "\
+              "  WHERE tgrelid = %(oid)s AND "
+        if pg_vers >= 90000:
+            sql += "NOT tgisinternal"
+        else:
+            sql += "NOT tgisconstraint"
+        return sql
+
 class TParent(TElem):
     """Info about trigger."""
     type = T_PARENT
@@ -424,7 +438,8 @@ class BaseStruct(object):
         """Fetch element(s) from db."""
         elem_list = []
         #print "Loading %s, name=%s, args=%s" % (repr(eclass), repr(name), repr(args))
-        curs.execute(eclass.SQL % args)
+        sql = eclass.get_load_sql(curs.connection.server_version)
+        curs.execute(sql % args)
         for row in curs.dictfetchall():
             elem_list.append(eclass(name, row))
         return elem_list
diff --git a/sql/londiste/functions/londiste.local_add_table.sql b/sql/londiste/functions/londiste.local_add_table.sql
index d89a2fc8..9b42c6b0 100644
--- a/sql/londiste/functions/londiste.local_add_table.sql
+++ b/sql/londiste/functions/londiste.local_add_table.sql
@@ -144,7 +144,22 @@ begin
     -- Don't report all the trigger names, 8.3 does not have array_accum
     -- available
 
-   select tg.tgname into logtrg_previous
+    if pgversion >= 90000 then
+        select tg.tgname into logtrg_previous
+        from pg_class r, pg_trigger tg
+        where r.oid = londiste.find_table_oid(fq_table_name)
+          and not tg.tgisinternal
+          and tg.tgname < logtrg_name::name
+          -- per-row AFTER trigger
+          and (tg.tgtype & 3) = 1   -- bits: 0:ROW, 1:BEFORE
+          -- current londiste
+          and tg.tgfoid not in ('pgq.sqltriga'::regproc::oid, 'pgq.logutriga'::regproc::oid)
+          -- old londiste
+          and substring(tg.tgname from 1 for 10) != '_londiste_'
+          and substring(tg.tgname from char_length(tg.tgname) - 6) != '_logger'
+        order by 1 limit 1;
+    else
+        select tg.tgname into logtrg_previous
         from pg_class r, pg_trigger tg
         where r.oid = londiste.find_table_oid(fq_table_name)
           and not tg.tgisconstraint
@@ -157,6 +172,7 @@ begin
           and substring(tg.tgname from 1 for 10) != '_londiste_'
           and substring(tg.tgname from char_length(tg.tgname) - 6) != '_logger'
         order by 1 limit 1;
+    end if;
 
     if logtrg_previous is not null then
        select 301,