Merge lp:~allenap/maas/non-django-database-test into lp:~maas-committers/maas/trunk

[Gavin Panella]

Run database tests without needing Django's test cases, mostly.

Previously Django's test cases did set-up and tear-down of the test database. In migrations mode this was teeth-grindingly slow; in legacy mode it was much quicker, but the database was not the same as would have been produced by migrations. Now a template database is constructed using migrations (with some cheats to make it much quicker) which is then used to prepare the database that tests use. In other words it combines the correctness of migrations with the speed of Django's legacy approach.

Fear not! Most of this diff is an SQL dump that's used to speed up the application of migrations:

$ bzr diff -r submit: | diffstat
Using submit branch /home/gavin/Launchpad/maas/trunk
 Makefile                                                        |   14
 buildout.cfg                                                    |   58
 src/maasserver/__main__.py                                      |    9
 src/maasserver/api/tests/test_discoveries.py                    |    7
 src/maasserver/api/tests/test_events.py                         |    1
 src/maasserver/api/tests/test_interfaces.py                     |    2
 src/maasserver/api/tests/test_ipaddresses.py                    |   10
 src/maasserver/api/tests/test_machine.py                        |   11
 src/maasserver/clusterrpc/tests/test_boot_images.py             |    3
 src/maasserver/dns/tests/test_config.py                         |    6
 src/maasserver/dns/tests/test_zonegenerator.py                  |   11
 src/maasserver/management/commands/dbupgrade.py                 |    7
 src/maasserver/models/signals/tests/test_nodes.py               |    7
 src/maasserver/models/tests/test_config.py                      |   33
 src/maasserver/models/tests/test_discovery.py                   |    5
 src/maasserver/models/tests/test_dnspublication.py              |    7
 src/maasserver/models/tests/test_interface.py                   |   14
 src/maasserver/models/tests/test_managers.py                    |    6
 src/maasserver/models/tests/test_node.py                        |   15
 src/maasserver/models/tests/test_staticipaddress.py             |    7
 src/maasserver/models/tests/test_subnet.py                      |   18
 src/maasserver/models/tests/test_timestampedmodel.py            |    6
 src/maasserver/regiondservices/tests/test_ntp.py                |    6
 src/maasserver/static/js/angular/factories/events.js            |    2
 src/maasserver/static/js/angular/factories/tests/test_events.js |    2
 src/maasserver/testing/api.py                                   |    1
 src/maasserver/testing/initial.maas_test.sql                    |13757 ++++++++++
 src/maasserver/testing/resources.py                             |  261
 src/maasserver/testing/testcase.py                              |  119
 src/maasserver/testing/tests/test_sampledata.py                 |    5
 src/maasserver/tests/test_auth.py                               |    5
 src/maasserver/tests/test_commands_dbupgrade.py                 |    4
 src/maasserver/tests/test_dbviews.py                            |    5
 src/maasserver/tests/test_eventloop.py                          |    3
 src/maasserver/tests/test_fields.py                             |   17
 src/maasserver/tests/test_forms_helpers.py                      |    4
 src/maasserver/tests/test_forms_interface_link.py               |   46
 src/maasserver/tests/test_ntp.py                                |   13
 src/maasserver/tests/test_preseed.py                            |   13
 src/maasserver/tests/test_routablepairs.py                      |    5
 src/maasserver/triggers/system.py                               |    3
 src/maasserver/triggers/tests/test_system_listener.py           |   17
 src/maasserver/utils/tests/test_signals.py                      |    4
 src/maasserver/utils/tests/test_threads.py                      |    5
 src/maasserver/views/tests/test_account.py                      |   11
 src/maasserver/views/tests/test_general.py                      |    4
 src/maasserver/websockets/handlers/tests/test_device.py         |    6
 src/maasserver/websockets/handlers/tests/test_discovery.py      |    5
 src/maastesting/djangotestcase.py                               |   16
 src/maastesting/testcase.py                                     |   29
 src/maastesting/twisted.py                                      |   19
 src/metadataserver/nodeinituser.py                              |   17
 src/metadataserver/tests/test_fields.py                         |    4
 src/metadataserver/tests/test_vendor_data.py                    |    2
 src/metadataserver/user_data/disk_erasing.py                    |    2
 src/metadataserver/user_data/tests/test_disk_erasing.py         |    2
 56 files changed, 14375 insertions(+), 296 deletions(-)

[Blake Rouse (blake-rouse)]

Looked through the code and I am still some what confused? What is legacy tests? I assume those must run as well when doing "make test"?

Looking at the code it was hard to see the difference between a legacy test and a none legacy test. Also no documentation on when legacy should be used over a standard test type. Is this something where we need to do work on tests that use legacy to remove this new "test.region-legacy"?

review: Needs Information

[Gavin Panella (allenap)]

> Looked through the code and I am still some what confused? What is
> legacy tests? I assume those must run as well when doing "make test"?
>
> Looking at the code it was hard to see the difference between a legacy
> test and a none legacy test. Also no documentation on when legacy
> should be used over a standard test type. Is this something where we
> need to do work on tests that use legacy to remove this new
> "test.region-legacy"?

I've done some work to whittle down the set of remaining legacy tests,
and it now stands at 71 tests. A legacy test is one that still needs to
run with the old Django test case and django_nose. 69 of those 71 need
it because they define `apps`, e.g.:

  class TestMAASModelForm(MAASLegacyTransactionServerTestCase):

      apps = ['maasserver.tests']

There will be a way around this, but I haven't figured it out yet.

The other 2 tests that I haven't figured out yet are:

  test_sends_message_for_config_windows_kms_host_insert
  test_sends_message_for_config_windows_kms_host_update

I haven't tried much to fix them, just put them aside for now.

The goal is definitely to eliminate these tests. For now, `make test`
will run bin/test.region.legacy so we won't be able to land broken
legacy tests.

[Blake Rouse (blake-rouse)]

Okay thanks for the information.

review: Approve

=== modified file 'Makefile'
--- Makefile	2016-12-07 12:46:14 +0000
+++ Makefile	2017-01-05 16:20:57 +0000
@@ -63,6 +63,7 @@
   bin/test.rack \
   bin/test.config \
   bin/test.region \
+  bin/test.region.legacy \
   bin/test.testing \
   bin/test.js \
   bin/test.e2e \
@@ -122,6 +123,11 @@
 	$(buildout) install region-test
 	@touch --no-create $@
 
+bin/test.region.legacy: \
+    bin/buildout buildout.cfg versions.cfg setup.py $(js_enums)
+	$(buildout) install region-test-legacy
+	@touch --no-create $@
+
 bin/maas: bin/buildout buildout.cfg versions.cfg setup.py
 	$(buildout) install cli
 	@touch --no-create $@
@@ -213,6 +219,7 @@
   bin/test.rack
   bin/test.config
   bin/test.region
+  bin/test.region.legacy
   bin/test.testing
   bin/test.js
 endef
@@ -239,6 +246,11 @@
 clean-failed:
 	$(RM) .noseids
 
+src/maasserver/testing/initial.maas_test.sql: bin/database syncdb
+	bin/database --preserve run -- pg_dump maas --format=plain > $@
+
+test-initial-data: src/maasserver/testing/initial.maas_test.sql
+
 define test-template
 $(test) --with-xunit --xunit-file=xunit.$(notdir $(test)).xml || touch .failed
 endef
@@ -475,7 +487,7 @@
   test+coverage
   test+lxd
   test-failed
-  test-migrations
+  test-initial-data
 endef
 
 #
 
=== modified file 'buildout.cfg'
--- buildout.cfg	2016-12-07 12:46:14 +0000
+++ buildout.cfg	2017-01-05 16:20:57 +0000
@@ -9,6 +9,7 @@
   rack-test
   region
   region-test
+  region-test-legacy
   repl
   sphinx
   testing-test
@@ -115,8 +116,54 @@
 recipe = zc.recipe.egg
 eggs =
   ${region:eggs}
-entry-points =
-  test.region=maasserver:execute_from_command_line
+  ${common:test-eggs}
+entry-points =
+  test.region=maastesting.noseplug:main
+initialization =
+  ${region:initialization}
+  options = [
+    "--with-crochet",
+    "--with-resources",
+    "--with-scenarios",
+    "--with-select",
+    "--select-dir=src/maasserver",
+    "--select-dir=src/metadataserver",
+    "--cover-package=maas,maasserver,metadataserver",
+    "--cover-branches",
+    # Reduce the logging level to INFO here as
+    # DebuggingLoggerMiddleware logs the content of all the
+    # requests at DEBUG level: we don't want this in the
+    # tests as it's too verbose.
+    "--logging-level=INFO",
+    "--logging-clear-handlers",
+    # Do not run tests tagged "legacy".
+    "-a", "!legacy",
+  ]
+  ${common:inject-test-options}
+  # Configure logging. TODO: Do this in a plugin.
+  from provisioningserver import logger
+  logger.configure(mode=logger.LoggingMode.COMMAND)
+  # Limit concurrency in all thread-pools to ONE.
+  from maasserver.utils import threads
+  threads.install_default_pool(maxthreads=1)
+  threads.install_database_unpool(maxthreads=1)
+  # Disable all database connections in the reactor.
+  from maasserver.utils import orm
+  from twisted.internet import reactor
+  assert not reactor.running, "The reactor has been started too early."
+  reactor.callFromThread(orm.disable_all_database_connections)
+  # Last and least, configure Django.
+  import django; django.setup()
+scripts = test.region
+extra-paths =
+  ${region:extra-paths}
+
+[region-test-legacy]
+recipe = zc.recipe.egg
+eggs =
+  ${region:eggs}
+entry-points =
+  test.region.legacy=maasserver:execute_from_command_line
 initialization =
   ${region:initialization}
   environ.setdefault("MAAS_PREVENT_MIGRATIONS", "1")
@@ -124,9 +171,6 @@
     "test",
     "--noinput",
     "--with-crochet",
-    # Disable resources in the region for now because of suspected bad
-    # interactions with django_nose.
-    # "--with-resources",
     "--with-scenarios",
     "--with-select",
     "--select-dir=src/maasserver",
@@ -139,9 +183,11 @@
     # tests as it's too verbose.
     "--logging-level=INFO",
     "--logging-clear-handlers",
+    # Run only tests tagged "legacy".
+    "-a", "legacy",
   ]
   ${common:inject-test-options}
-scripts = test.region
+scripts = test.region.legacy
 extra-paths =
   ${region:extra-paths}
 
 
=== added file 'src/maasserver/__main__.py'
--- src/maasserver/__main__.py	1970-01-01 00:00:00 +0000
+++ src/maasserver/__main__.py	2017-01-05 16:20:57 +0000
@@ -0,0 +1,9 @@
+# Copyright 2016 Canonical Ltd.  This software is licensed under the
+# GNU Affero General Public License version 3 (see the file LICENSE).
+
+"""Execute MAAS's Django subsystem."""
+
+from maasserver import execute_from_command_line
+
+
+raise SystemExit(execute_from_command_line())
 
=== modified file 'src/maasserver/api/tests/test_discoveries.py'
--- src/maasserver/api/tests/test_discoveries.py	2016-09-29 16:14:53 +0000
+++ src/maasserver/api/tests/test_discoveries.py	2017-01-05 16:20:57 +0000
@@ -21,7 +21,6 @@
     user_friendly_scan_results,
 )
 from maasserver.clusterrpc.utils import RPCResults
-from maasserver.dbviews import register_view
 from maasserver.models import Subnet
 from maasserver.testing.api import APITestCase
 from maasserver.testing.factory import factory
@@ -80,7 +79,6 @@
 
     def setUp(self):
         super().setUp()
-        register_view("maasserver_discovery")
         # Patch to ensure an actual scan is not attempted.
         scan_all_rack_networks_mock = self.patch(
             discoveries_module.scan_all_rack_networks)
@@ -182,7 +180,6 @@
 
     def setUp(self):
         super().setUp()
-        register_view("maasserver_discovery")
         # Patch to ensure an actual scan is not attempted.
         self.scan_all_rack_networks_mock = self.patch(
             discoveries_module.scan_all_rack_networks)
@@ -316,10 +313,6 @@
 
 class TestDiscoveryAPI(APITestCase.ForUser):
 
-    def setUp(self):
-        super().setUp()
-        register_view("maasserver_discovery")
-
     def test_handler_path(self):
         discovery = factory.make_Discovery()
         self.assertEqual(
 
=== modified file 'src/maasserver/api/tests/test_events.py'
--- src/maasserver/api/tests/test_events.py	2016-07-30 01:17:54 +0000
+++ src/maasserver/api/tests/test_events.py	2017-01-05 16:20:57 +0000
@@ -157,7 +157,6 @@
             [event.id for event in reversed(events)],
             extract_event_ids(parsed_result))
         self.assertEqual(len(events), parsed_result['count'])
-        self.assertNumQueries(1)
 
     def test_GET_query_with_some_matching_ids_returns_matching_nodes(self):
         # If some nodes match the requested ids and some don't, only the
 
=== modified file 'src/maasserver/api/tests/test_interfaces.py'
--- src/maasserver/api/tests/test_interfaces.py	2016-12-08 16:59:19 +0000
+++ src/maasserver/api/tests/test_interfaces.py	2017-01-05 16:20:57 +0000
@@ -9,7 +9,6 @@
 import random
 
 from django.core.urlresolvers import reverse
-from maasserver.dbviews import register_view
 from maasserver.enum import (
     INTERFACE_LINK_TYPE,
     INTERFACE_TYPE,
@@ -1074,7 +1073,6 @@
                     }))
 
     def test_link_subnet_creates_link_on_device(self):
-        register_view("maasserver_discovery")
         parent = factory.make_Node()
         device = factory.make_Device(
             owner=self.user, parent=parent)
 
=== modified file 'src/maasserver/api/tests/test_ipaddresses.py'
--- src/maasserver/api/tests/test_ipaddresses.py	2016-10-18 16:31:31 +0000
+++ src/maasserver/api/tests/test_ipaddresses.py	2017-01-05 16:20:57 +0000
@@ -9,7 +9,6 @@
 
 from django.conf import settings
 from django.core.urlresolvers import reverse
-from maasserver.dbviews import register_view
 from maasserver.enum import (
     INTERFACE_LINK_TYPE,
     INTERFACE_TYPE,
@@ -221,11 +220,6 @@
         ("with_force", {"force": True}),
     )
 
-    @transactional
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     @property
     def force_should_work(self):
         # The 'force' parameter should only work if (1) the user-under-test
@@ -372,10 +366,6 @@
         ("with_ip_address_param", {"ip_param": "ip_address"}),
     )
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def post_reservation_request(
             self, subnet=None, ip_address=None, network=None, mac=None,
             hostname=None):
 
=== modified file 'src/maasserver/api/tests/test_machine.py'
--- src/maasserver/api/tests/test_machine.py	2016-10-28 08:43:09 +0000
+++ src/maasserver/api/tests/test_machine.py	2017-01-05 16:20:57 +0000
@@ -15,7 +15,6 @@
 from django.db import transaction
 from maasserver import forms
 from maasserver.api import machines as machines_module
-from maasserver.dbviews import register_view
 from maasserver.enum import (
     FILESYSTEM_FORMAT_TYPE_CHOICES,
     FILESYSTEM_TYPE,
@@ -49,8 +48,10 @@
 )
 from maasserver.testing.architecture import make_usable_architecture
 from maasserver.testing.factory import factory
+from maasserver.testing.matchers import HasStatusCode
 from maasserver.testing.orm import reload_objects
 from maasserver.testing.osystems import make_usable_osystem
+from maasserver.testing.testcase import MAASServerTestCase
 from maasserver.testing.testclient import MAASSensibleOAuthClient
 from maasserver.utils.converters import json_load_bytes
 from maasserver.utils.orm import (
@@ -81,13 +82,13 @@
 import yaml
 
 
-class MachineAnonAPITest(APITestCase.ForAnonymous):
+class MachineAnonAPITest(MAASServerTestCase):
 
     def test_machine_init_user_cannot_access(self):
         token = NodeKey.objects.get_token_for_node(factory.make_Node())
         client = MAASSensibleOAuthClient(get_node_init_user(), token)
         response = client.get(reverse('machines_handler'))
-        self.assertEqual(http.client.FORBIDDEN, response.status_code)
+        self.assertThat(response, HasStatusCode(http.client.FORBIDDEN))
 
 
 class MachinesAPILoggedInTest(APITestCase.ForUserAndAdmin):
@@ -1349,10 +1350,6 @@
 class TestMachineAPITransactional(APITransactionTestCase.ForUser):
     """The following TestMachineAPI tests require APITransactionTestCase."""
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test_POST_start_returns_error_when_static_ips_exhausted(self):
         self.patch(node_module, 'power_driver_check')
         network = IPNetwork("10.0.0.0/30")
 
=== modified file 'src/maasserver/clusterrpc/tests/test_boot_images.py'
--- src/maasserver/clusterrpc/tests/test_boot_images.py	2016-09-12 21:33:05 +0000
+++ src/maasserver/clusterrpc/tests/test_boot_images.py	2017-01-05 16:20:57 +0000
@@ -48,7 +48,6 @@
     MockCallsMatch,
     MockNotCalled,
 )
-from maastesting.testcase import MAASTestCase
 from maastesting.twisted import TwistedLoggerFixture
 from provisioningserver.boot.tests import test_tftppath
 from provisioningserver.boot.tftppath import compose_image_path
@@ -401,7 +400,7 @@
                 param['release']))
 
 
-class TestRackControllersImporter(MAASTestCase):
+class TestRackControllersImporter(MAASServerTestCase):
     """Tests for `RackControllersImporter`."""
 
     def test__init_with_single_system_id(self):
 
=== modified file 'src/maasserver/dns/tests/test_config.py'
--- src/maasserver/dns/tests/test_config.py	2016-09-22 02:53:33 +0000
+++ src/maasserver/dns/tests/test_config.py	2017-01-05 16:20:57 +0000
@@ -75,9 +75,9 @@
         # A 'sys_dns' signal is also sent, but that is a side-effect of
         # inserting into the DNS publications table, and is tested as part of
         # the system triggers code.
-        self.assertRaises(
-            DNSPublication.DoesNotExist,
-            DNSPublication.objects.get_most_recent)
+        self.assertThat(
+            DNSPublication.objects.get_most_recent(),
+            MatchesStructure.byEquality(source="Initial publication"))
         dns_force_reload()
         self.assertThat(
             DNSPublication.objects.get_most_recent(),
 
=== modified file 'src/maasserver/dns/tests/test_zonegenerator.py'
--- src/maasserver/dns/tests/test_zonegenerator.py	2016-10-18 16:21:26 +0000
+++ src/maasserver/dns/tests/test_zonegenerator.py	2017-01-05 16:20:57 +0000
@@ -16,7 +16,6 @@
 from urllib.parse import urlparse
 
 from maasserver import server_address
-from maasserver.dbviews import register_view
 from maasserver.dns import zonegenerator
 from maasserver.dns.zonegenerator import (
     get_dns_search_paths,
@@ -130,10 +129,10 @@
 class TestGetDNSSearchPaths(MAASServerTestCase):
 
     def test__returns_all_authoritative_domains(self):
-        domain_names = [
+        domain_names = get_dns_search_paths()
+        domain_names.update(
             factory.make_Domain(authoritative=True).name
-            for _ in range(3)
-        ]
+            for _ in range(3))
         for _ in range(3):
             factory.make_Domain(authoritative=False)
         self.assertItemsEqual(
@@ -507,10 +506,6 @@
 class TestZoneGeneratorTTL(MAASTransactionServerTestCase):
     """Tests for TTL in :class:ZoneGenerator`."""
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     @transactional
     def test_domain_ttl_overrides_global(self):
         global_ttl = random.randint(100, 199)
 
=== modified file 'src/maasserver/management/commands/dbupgrade.py'
--- src/maasserver/management/commands/dbupgrade.py	2016-12-07 12:46:14 +0000
+++ src/maasserver/management/commands/dbupgrade.py	2017-01-05 16:20:57 +0000
@@ -288,11 +288,12 @@
     @classmethod
     def _perform_django_migrations(cls, database):
         """Perform the django migrations."""
+        env = dict(os.environ, PYTHONPATH=os.pathsep.join(sys.path))
         cmd = [
-            sys.argv[0], "dbupgrade", "--database", database,
-            "--django",
+            sys.executable, "-m", "maasserver", "dbupgrade",
+            "--database", database, "--django",
         ]
-        process = subprocess.Popen(cmd)
+        process = subprocess.Popen(cmd, env=env)
         return process.wait()
 
     @classmethod
 
=== modified file 'src/maasserver/models/tests/test_config.py'
--- src/maasserver/models/tests/test_config.py	2016-09-17 12:03:06 +0000
+++ src/maasserver/models/tests/test_config.py	2017-01-05 16:20:57 +0000
@@ -9,11 +9,15 @@
 
 from django.db import IntegrityError
 from fixtures import TestWithFixtures
-from maasserver.models import Config
+from maasserver.models import (
+    Config,
+    signals,
+)
 import maasserver.models.config
 from maasserver.models.config import get_default_config
 from maasserver.testing.factory import factory
 from maasserver.testing.testcase import MAASServerTestCase
+from testtools.matchers import Is
 
 
 class ConfigDefaultTest(MAASServerTestCase, TestWithFixtures):
@@ -162,3 +166,30 @@
         Config.objects.set_config(name, value)
 
         self.assertEqual([], recorder.calls)
+
+
+class SettingConfigTest(MAASServerTestCase):
+    """Testing of the :class:`Config` model and setting each option."""
+
+    scenarios = tuple(
+        (name, {"name": name})
+        for name in get_default_config()
+    )
+
+    def setUp(self):
+        super(SettingConfigTest, self).setUp()
+        # Some of these setting we have to be careful about.
+        if self.name in {"enable_http_proxy", "http_proxy"}:
+            manager = signals.bootsources.signals
+            self.addCleanup(manager.enable)
+            manager.disable()
+
+    def test_can_be_initialised_to_None_without_crashing(self):
+        Config.objects.set_config(self.name, None)
+        self.assertThat(Config.objects.get_config(self.name), Is(None))
+
+    def test_can_be_modified_from_None_without_crashing(self):
+        Config.objects.set_config(self.name, None)
+        something = [factory.make_name("value")]
+        Config.objects.set_config(self.name, something)
+        self.assertEqual(something, Config.objects.get_config(self.name))
 
=== modified file 'src/maasserver/models/tests/test_discovery.py'
--- src/maasserver/models/tests/test_discovery.py	2016-12-07 12:46:14 +0000
+++ src/maasserver/models/tests/test_discovery.py	2017-01-05 16:20:57 +0000
@@ -5,7 +5,6 @@
 
 __all__ = []
 
-from maasserver.dbviews import register_view
 from maasserver.models import (
     Discovery,
     discovery as discovery_module,
@@ -29,10 +28,6 @@
 
 class TestDiscoveryModel(MAASServerTestCase):
 
-    def setUp(self):
-        super().setUp()
-        register_view("maasserver_discovery")
-
     def test_mac_organization(self):
         discovery = factory.make_Discovery(mac_address="48:51:b7:00:00:00")
         self.assertThat(discovery.mac_organization, IsNonEmptyString)
 
=== modified file 'src/maasserver/models/tests/test_dnspublication.py'
--- src/maasserver/models/tests/test_dnspublication.py	2016-04-28 20:13:46 +0000
+++ src/maasserver/models/tests/test_dnspublication.py	2017-01-05 16:20:57 +0000
@@ -86,6 +86,11 @@
 class TestDNSPublicationManager(MAASServerTestCase):
     """Test `DNSPublicationManager`."""
 
+    def setUp(self):
+        super(TestDNSPublicationManager, self).setUp()
+        # These tests expect the DNSPublication table to be empty.
+        DNSPublication.objects.all().delete()
+
     def test_get_most_recent_returns_record_with_highest_id(self):
         DNSPublication(serial=3).save()
         DNSPublication(serial=30).save()
 
=== modified file 'src/maasserver/models/tests/test_interface.py'
--- src/maasserver/models/tests/test_interface.py	2016-12-20 06:52:13 +0000
+++ src/maasserver/models/tests/test_interface.py	2017-01-05 16:20:57 +0000
@@ -16,7 +16,6 @@
 from django.db import transaction
 from django.http import Http404
 from fixtures import FakeLogger
-from maasserver.dbviews import register_view
 from maasserver.enum import (
     INTERFACE_LINK_TYPE,
     INTERFACE_TYPE,
@@ -2031,10 +2030,6 @@
 class TestLinkSubnet(MAASTransactionServerTestCase):
     """Tests for `Interface.link_subnet`."""
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test__AUTO_creates_link_to_AUTO_with_subnet(self):
         interface = factory.make_Interface(INTERFACE_TYPE.PHYSICAL)
         auto_subnet = factory.make_Subnet(vlan=interface.vlan)
@@ -2328,11 +2323,6 @@
 class TestUpdateIPAddress(MAASTransactionServerTestCase):
     """Tests for `Interface.update_ip_address`."""
 
-    @transactional
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test__switch_dhcp_to_auto(self):
         interface = factory.make_Interface(INTERFACE_TYPE.PHYSICAL)
         subnet = factory.make_Subnet(vlan=interface.vlan)
@@ -2659,10 +2649,6 @@
 class TestClaimAutoIPs(MAASTransactionServerTestCase):
     """Tests for `Interface.claim_auto_ips`."""
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test__claims_all_auto_ip_addresses(self):
         with transaction.atomic():
             interface = factory.make_Interface(INTERFACE_TYPE.PHYSICAL)
 
=== modified file 'src/maasserver/models/tests/test_managers.py'
--- src/maasserver/models/tests/test_managers.py	2015-12-01 18:12:59 +0000
+++ src/maasserver/models/tests/test_managers.py	2017-01-05 16:20:57 +0000
@@ -1,18 +1,18 @@
-# Copyright 2012-2015 Canonical Ltd.  This software is licensed under the
+# Copyright 2012-2016 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """Test maasserver model managers."""
 
 __all__ = []
 
-from maasserver.testing.testcase import MAASTransactionServerTestCase
+from maasserver.testing.testcase import MAASLegacyTransactionServerTestCase
 from maasserver.tests.models import (
     BulkManagerParentTestModel,
     BulkManagerTestModel,
 )
 
 
-class BulkManagerTest(MAASTransactionServerTestCase):
+class BulkManagerTest(MAASLegacyTransactionServerTestCase):
 
     apps = ['maasserver.tests']
 
 
=== modified file 'src/maasserver/models/tests/test_node.py'
--- src/maasserver/models/tests/test_node.py	2017-01-04 17:45:30 +0000
+++ src/maasserver/models/tests/test_node.py	2017-01-05 16:20:57 +0000
@@ -36,7 +36,6 @@
     power_query,
 )
 from maasserver.clusterrpc.testing.boot_images import make_rpc_boot_image
-from maasserver.dbviews import register_view
 from maasserver.enum import (
     FILESYSTEM_GROUP_TYPE,
     FILESYSTEM_TYPE,
@@ -4259,10 +4258,6 @@
 class TestNodeNetworking(MAASTransactionServerTestCase):
     """Tests for methods on the `Node` related to networking."""
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test__create_acquired_bridges_doesnt_call_on_bridge(self):
         mock_create_acquired_bridge = self.patch(
             Interface, "create_acquired_bridge")
@@ -4839,10 +4834,6 @@
 class TestGetDefaultDNSServers(MAASServerTestCase):
     """Tests for `Node.get_default_dns_servers`."""
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def make_Node_with_RackController(
             self, ipv4=True, ipv6=True, ipv4_gateway=True, ipv6_gateway=True,
             ipv4_subnet_dns=None, ipv6_subnet_dns=None):
@@ -5016,7 +5007,6 @@
     def setUp(self):
         super(TestNode_Start, self).setUp()
         self.patch_autospec(node_module, 'power_driver_check')
-        register_view("maasserver_discovery")
 
     def make_acquired_node_with_interface(
             self, user, bmc_connected_to=None, power_type="virsh",
 
=== modified file 'src/maasserver/models/tests/test_staticipaddress.py'
--- src/maasserver/models/tests/test_staticipaddress.py	2016-12-07 12:46:14 +0000
+++ src/maasserver/models/tests/test_staticipaddress.py	2017-01-05 16:20:57 +0000
@@ -16,7 +16,6 @@
 from django.core.exceptions import ValidationError
 from django.db import IntegrityError
 from maasserver import locks
-from maasserver.dbviews import register_view
 from maasserver.enum import (
     INTERFACE_LINK_TYPE,
     INTERFACE_TYPE,
@@ -67,10 +66,6 @@
 
 class TestStaticIPAddressManager(MAASServerTestCase):
 
-    def setUp(self):
-        super(TestStaticIPAddressManager, self).setUp()
-        register_view("maasserver_discovery")
-
     def test_filter_by_ip_family_ipv4(self):
         network_v4 = factory.make_ipv4_network()
         subnet_v4 = factory.make_Subnet(cidr=str(network_v4.cidr))
@@ -328,8 +323,6 @@
     )
 
     def test_allocate_new_works_under_extreme_concurrency(self):
-        register_view("maasserver_discovery")
-
         ipv6 = (self.ip_version == 6)
         subnet = factory.make_managed_Subnet(ipv6=ipv6)
         count = 20  # Allocate this number of IP addresses.
 
=== modified file 'src/maasserver/models/tests/test_subnet.py'
--- src/maasserver/models/tests/test_subnet.py	2016-12-20 06:52:13 +0000
+++ src/maasserver/models/tests/test_subnet.py	2017-01-05 16:20:57 +0000
@@ -19,7 +19,6 @@
 from fixtures import FakeLogger
 from hypothesis import given
 from hypothesis.strategies import integers
-from maasserver.dbviews import register_view
 from maasserver.enum import (
     IPADDRESS_TYPE,
     IPRANGE_TYPE,
@@ -752,7 +751,6 @@
         self.assertThat(s, Contains(gateway_ip_2))
 
     def get__get_iprange_usage_includes_neighbours_on_request(self):
-        register_view("maasserver_discovery")
         subnet = factory.make_Subnet(
             cidr="10.0.0.0/30", gateway_ip=None, dns_servers=None)
         rackif = factory.make_Interface(vlan=subnet.vlan)
@@ -762,7 +760,6 @@
             MAASIPRange("10.0.0.1", purpose="neighbour")))
 
     def get__get_iprange_usage_excludes_neighbours_by_default(self):
-        register_view("maasserver_discovery")
         subnet = factory.make_Subnet(
             cidr="10.0.0.0/30", gateway_ip=None, dns_servers=None)
         rackif = factory.make_Interface(vlan=subnet.vlan)
@@ -853,10 +850,6 @@
 
 class TestSubnetGetMAASIPSetForNeighbours(MAASServerTestCase):
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test__returns_observed_neighbours(self):
         subnet = factory.make_Subnet(
             cidr="10.0.0.0/30", gateway_ip=None, dns_servers=None)
@@ -879,10 +872,6 @@
 
 class TestSubnetGetLeastRecentlySeenUnknownNeighbour(MAASServerTestCase):
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test__returns_least_recently_seen_neighbour(self):
         # Note: 10.0.0.0/30 --> 10.0.0.1 and 10.0.0.0.2 are usable.
         subnet = factory.make_Subnet(
@@ -931,10 +920,6 @@
             subnet = reload_object(subnet)
         return subnet
 
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
-
     def test__raises_if_no_free_addresses(self):
         # Note: 10.0.0.0/30 --> 10.0.0.1 and 10.0.0.0.2 are usable.
         subnet = self.make_Subnet(
@@ -1017,9 +1002,6 @@
 
 
 class TestUnmanagedSubnets(MAASServerTestCase):
-    def setUp(self):
-        register_view("maasserver_discovery")
-        return super().setUp()
 
     def test__allocation_uses_reserved_range(self):
         # Note: 10.0.0.0/29 --> 10.0.0.1 through 10.0.0.0.6 are usable.
 
=== modified file 'src/maasserver/models/tests/test_timestampedmodel.py'
--- src/maasserver/models/tests/test_timestampedmodel.py	2016-08-25 17:06:55 +0000
+++ src/maasserver/models/tests/test_timestampedmodel.py	2017-01-05 16:20:57 +0000
@@ -1,4 +1,4 @@
-# Copyright 2012-2015 Canonical Ltd.  This software is licensed under the
+# Copyright 2012-2016 Canonical Ltd.  This software is licensed under the
 # GNU Affero General Public License version 3 (see the file LICENSE).
 
 """:class:`TimestampedModel` tests."""
@@ -14,8 +14,8 @@
 from django.db import transaction
 from maasserver.models.timestampedmodel import now
 from maasserver.testing.testcase import (
+    MAASLegacyTransactionServerTestCase,
     MAASServerTestCase,
-    MAASTransactionServerTestCase,
 )
 from maasserver.tests.models import TimestampedModelTestModel
 
@@ -25,7 +25,7 @@
     return datetime.now() - many_seconds_ago
 
 
-class TimestampedModelTest(MAASTransactionServerTestCase):
+class TimestampedModelTest(MAASLegacyTransactionServerTestCase):
     """Testing for the class `TimestampedModel`."""
 
     apps = ['maasserver.tests']
 
=== modified file 'src/maasserver/regiondservices/tests/test_ntp.py'
--- src/maasserver/regiondservices/tests/test_ntp.py	2016-10-03 20:14:48 +0000
+++ src/maasserver/regiondservices/tests/test_ntp.py	2017-01-05 16:20:57 +0000
@@ -8,7 +8,6 @@
 from os.path import join
 
 from crochet import wait_for
-from maasserver.dbviews import register_view
 from maasserver.models.config import Config
 from maasserver.regiondservices import ntp
 from maasserver.service_monitor import service_monitor
@@ -68,7 +67,6 @@
 
     def setUp(self):
         super(TestRegionNetworkTimeProtocolService, self).setUp()
-        register_view("maasserver_routable_pairs")
         self.useFixture(MAASRootFixture())
 
     @transactional
@@ -145,10 +143,6 @@
 class TestRegionNetworkTimeProtocolService_Database(MAASServerTestCase):
     """Database tests for `RegionNetworkTimeProtocolService`."""
 
-    def setUp(self):
-        super(TestRegionNetworkTimeProtocolService_Database, self).setUp()
-        register_view("maasserver_routable_pairs")
-
     def test__getConfiguration_returns_configuration_object(self):
         service = ntp.RegionNetworkTimeProtocolService(reactor)
 
 
=== modified file 'src/maasserver/static/js/angular/factories/events.js'
--- src/maasserver/static/js/angular/factories/events.js	2016-07-15 02:05:06 +0000
+++ src/maasserver/static/js/angular/factories/events.js	2017-01-05 16:20:57 +0000
@@ -1,4 +1,4 @@
-/* Copyright 2015 Canonical Ltd.  This software is licensed under the
+/* Copyright 2015-2016 Canonical Ltd.  This software is licensed under the
  * GNU Affero General Public License version 3 (see the file LICENSE).
  *
  * MAAS Event Manager Factory
 
=== modified file 'src/maasserver/static/js/angular/factories/tests/test_events.js'
--- src/maasserver/static/js/angular/factories/tests/test_events.js	2016-07-15 02:05:06 +0000
+++ src/maasserver/static/js/angular/factories/tests/test_events.js	2017-01-05 16:20:57 +0000
@@ -1,4 +1,4 @@
-/* Copyright 2015 Canonical Ltd.  This software is licensed under the
+/* Copyright 2015-2016 Canonical Ltd.  This software is licensed under the
  * GNU Affero General Public License version 3 (see the file LICENSE).
  *
  * Unit tests for EventsManagerFactory.
 
=== modified file 'src/maasserver/testing/api.py'
--- src/maasserver/testing/api.py	2016-08-31 13:52:59 +0000
+++ src/maasserver/testing/api.py	2017-01-05 16:20:57 +0000
@@ -148,7 +148,6 @@
         self.scenarios = merge_scenarios(
             scenarios_users, scenarios_clients, self.scenarios)
 
-    @transactional
     def setUp(self):
         if not callable(self.userfactory):
             raise AssertionError(
 
=== added file 'src/maasserver/testing/initial.maas_test.sql'
--- src/maasserver/testing/initial.maas_test.sql	1970-01-01 00:00:00 +0000
+++ src/maasserver/testing/initial.maas_test.sql	2017-01-05 16:20:57 +0000
@@ -0,0 +1,13837 @@
+--
+-- PostgreSQL database dump
+--
+
+-- Dumped from database version 9.5.5
+-- Dumped by pg_dump version 9.5.5
+
+SET statement_timeout = 0;
+SET lock_timeout = 0;
+SET client_encoding = 'UTF8';
+SET standard_conforming_strings = on;
+SET check_function_bodies = false;
+SET client_min_messages = warning;
+SET row_security = off;
+
+--
+-- Name: plpgsql; Type: EXTENSION; Schema: -; Owner: 
+--
+
+CREATE EXTENSION IF NOT EXISTS plpgsql WITH SCHEMA pg_catalog;
+
+
+--
+-- Name: EXTENSION plpgsql; Type: COMMENT; Schema: -; Owner: 
+--
+
+COMMENT ON EXTENSION plpgsql IS 'PL/pgSQL procedural language';
+
+
+SET search_path = public, pg_catalog;
+
+--
+-- Name: config_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION config_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('config_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.config_create_notify() OWNER TO gavin;
+
+--
+-- Name: config_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION config_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('config_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.config_delete_notify() OWNER TO gavin;
+
+--
+-- Name: config_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION config_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('config_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.config_update_notify() OWNER TO gavin;
+
+--
+-- Name: device_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION device_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  pnode RECORD;
+BEGIN
+  IF NEW.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = NEW.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_create',CAST(NEW.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.device_create_notify() OWNER TO gavin;
+
+--
+-- Name: device_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION device_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  pnode RECORD;
+BEGIN
+  IF OLD.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = OLD.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_delete',CAST(OLD.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.device_delete_notify() OWNER TO gavin;
+
+--
+-- Name: device_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION device_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  pnode RECORD;
+BEGIN
+  IF NEW.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = NEW.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(NEW.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.device_update_notify() OWNER TO gavin;
+
+--
+-- Name: dhcpsnippet_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dhcpsnippet_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('dhcpsnippet_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dhcpsnippet_create_notify() OWNER TO gavin;
+
+--
+-- Name: dhcpsnippet_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dhcpsnippet_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('dhcpsnippet_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dhcpsnippet_delete_notify() OWNER TO gavin;
+
+--
+-- Name: dhcpsnippet_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dhcpsnippet_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('dhcpsnippet_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dhcpsnippet_update_notify() OWNER TO gavin;
+
+--
+-- Name: dnsdata_domain_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dnsdata_domain_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    dom RECORD;
+BEGIN
+  SELECT DISTINCT ON (domain_id) domain_id INTO dom
+  FROM maasserver_dnsresource AS dnsresource
+  WHERE dnsresource.id = OLD.dnsresource_id;
+  PERFORM pg_notify('domain_update',CAST(dom.domain_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dnsdata_domain_delete_notify() OWNER TO gavin;
+
+--
+-- Name: dnsdata_domain_insert_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dnsdata_domain_insert_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    dom RECORD;
+BEGIN
+  SELECT DISTINCT ON (domain_id) domain_id INTO dom
+  FROM maasserver_dnsresource AS dnsresource
+  WHERE dnsresource.id = NEW.dnsresource_id;
+  PERFORM pg_notify('domain_update',CAST(dom.domain_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dnsdata_domain_insert_notify() OWNER TO gavin;
+
+--
+-- Name: dnsdata_domain_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dnsdata_domain_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    dom RECORD;
+BEGIN
+  SELECT DISTINCT ON (domain_id) domain_id INTO dom
+  FROM maasserver_dnsresource AS dnsresource
+  WHERE dnsresource.id = OLD.dnsresource_id OR dnsresource.id = NEW.dnsresource_id;
+  PERFORM pg_notify('domain_update',CAST(dom.domain_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dnsdata_domain_update_notify() OWNER TO gavin;
+
+--
+-- Name: dnsresource_domain_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dnsresource_domain_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    domain RECORD;
+BEGIN
+  PERFORM pg_notify('domain_update',CAST(OLD.domain_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dnsresource_domain_delete_notify() OWNER TO gavin;
+
+--
+-- Name: dnsresource_domain_insert_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dnsresource_domain_insert_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    domain RECORD;
+BEGIN
+  PERFORM pg_notify('domain_update',CAST(NEW.domain_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dnsresource_domain_insert_notify() OWNER TO gavin;
+
+--
+-- Name: dnsresource_domain_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION dnsresource_domain_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    domain RECORD;
+BEGIN
+  PERFORM pg_notify('domain_update',CAST(OLD.domain_id AS text));
+  IF OLD.domain_id != NEW.domain_id THEN
+    PERFORM pg_notify('domain_update',CAST(NEW.domain_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.dnsresource_domain_update_notify() OWNER TO gavin;
+
+--
+-- Name: domain_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION domain_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('domain_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.domain_create_notify() OWNER TO gavin;
+
+--
+-- Name: domain_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION domain_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('domain_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.domain_delete_notify() OWNER TO gavin;
+
+--
+-- Name: domain_node_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION domain_node_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  IF OLD.name != NEW.name THEN
+    SELECT system_id, node_type, parent_id INTO node
+    FROM maasserver_node
+    WHERE maasserver_node.domain_id = NEW.id;
+
+    IF node.system_id IS NOT NULL THEN
+      IF node.node_type = 0 THEN
+        PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+      ELSIF node.node_type IN (2, 3, 4) THEN
+        PERFORM pg_notify(
+          'controller_update',CAST(node.system_id AS text));
+      ELSIF node.parent_id IS NOT NULL THEN
+        SELECT system_id INTO pnode
+        FROM maasserver_node
+        WHERE id = node.parent_id;
+        PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+      ELSE
+        PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+      END IF;
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.domain_node_update_notify() OWNER TO gavin;
+
+--
+-- Name: domain_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION domain_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('domain_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.domain_update_notify() OWNER TO gavin;
+
+--
+-- Name: event_create_machine_device_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION event_create_machine_device_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = NEW.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.event_create_machine_device_notify() OWNER TO gavin;
+
+--
+-- Name: event_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION event_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('event_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.event_create_notify() OWNER TO gavin;
+
+--
+-- Name: fabric_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION fabric_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('fabric_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.fabric_create_notify() OWNER TO gavin;
+
+--
+-- Name: fabric_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION fabric_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('fabric_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.fabric_delete_notify() OWNER TO gavin;
+
+--
+-- Name: fabric_machine_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION fabric_machine_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    node RECORD;
+    pnode RECORD;
+BEGIN
+  FOR node IN (
+    SELECT DISTINCT ON (maasserver_node.id)
+      system_id, node_type, parent_id
+    FROM
+      maasserver_node,
+      maasserver_fabric,
+      maasserver_interface,
+      maasserver_vlan
+    WHERE maasserver_fabric.id = NEW.id
+    AND maasserver_vlan.fabric_id = maasserver_fabric.id
+    AND maasserver_node.id = maasserver_interface.node_id
+    AND maasserver_vlan.id = maasserver_interface.vlan_id)
+  LOOP
+    IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+    ELSIF node.node_type IN (2, 3, 4) THEN
+      PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+    ELSIF node.parent_id IS NOT NULL THEN
+      SELECT system_id INTO pnode
+      FROM maasserver_node
+      WHERE id = node.parent_id;
+      PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+    ELSE
+      PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+    END IF;
+  END LOOP;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.fabric_machine_update_notify() OWNER TO gavin;
+
+--
+-- Name: fabric_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION fabric_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('fabric_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.fabric_update_notify() OWNER TO gavin;
+
+--
+-- Name: ipaddress_domain_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION ipaddress_domain_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  dom RECORD;
+BEGIN
+  FOR dom IN (
+    SELECT DISTINCT ON (domain.id)
+      domain.id
+    FROM maasserver_staticipaddress AS staticipaddress
+    LEFT JOIN (
+      maasserver_interface_ip_addresses AS iia
+      JOIN maasserver_interface AS interface ON
+        iia.interface_id = interface.id
+      JOIN maasserver_node AS node ON
+        node.id = interface.node_id) ON
+      iia.staticipaddress_id = staticipaddress.id
+    LEFT JOIN (
+      maasserver_dnsresource_ip_addresses AS dia
+      JOIN maasserver_dnsresource AS dnsresource ON
+        dia.dnsresource_id = dnsresource.id) ON
+      dia.staticipaddress_id = staticipaddress.id
+    JOIN maasserver_domain AS domain ON
+      domain.id = node.domain_id OR domain.id = dnsresource.domain_id
+    WHERE staticipaddress.id = OLD.id)
+  LOOP
+    PERFORM pg_notify('domain_update',CAST(dom.id AS text));
+  END LOOP;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.ipaddress_domain_delete_notify() OWNER TO gavin;
+
+--
+-- Name: ipaddress_domain_insert_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION ipaddress_domain_insert_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  dom RECORD;
+BEGIN
+  FOR dom IN (
+    SELECT DISTINCT ON (domain.id)
+      domain.id
+    FROM maasserver_staticipaddress AS staticipaddress
+    LEFT JOIN (
+      maasserver_interface_ip_addresses AS iia
+      JOIN maasserver_interface AS interface ON
+        iia.interface_id = interface.id
+      JOIN maasserver_node AS node ON
+        node.id = interface.node_id) ON
+      iia.staticipaddress_id = staticipaddress.id
+    LEFT JOIN (
+      maasserver_dnsresource_ip_addresses AS dia
+      JOIN maasserver_dnsresource AS dnsresource ON
+        dia.dnsresource_id = dnsresource.id) ON
+      dia.staticipaddress_id = staticipaddress.id
+    JOIN maasserver_domain AS domain ON
+      domain.id = node.domain_id OR domain.id = dnsresource.domain_id
+    WHERE staticipaddress.id = NEW.id)
+  LOOP
+    PERFORM pg_notify('domain_update',CAST(dom.id AS text));
+  END LOOP;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.ipaddress_domain_insert_notify() OWNER TO gavin;
+
+--
+-- Name: ipaddress_domain_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION ipaddress_domain_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  dom RECORD;
+BEGIN
+  IF ((OLD.ip IS NULL and NEW.ip IS NOT NULL) OR
+        (OLD.ip IS NOT NULL and NEW.ip IS NULL) OR
+        OLD.ip != NEW.ip) THEN
+    FOR dom IN (
+      SELECT DISTINCT ON (domain.id)
+        domain.id
+      FROM maasserver_staticipaddress AS staticipaddress
+      LEFT JOIN (
+        maasserver_interface_ip_addresses AS iia
+        JOIN maasserver_interface AS interface ON
+          iia.interface_id = interface.id
+        JOIN maasserver_node AS node ON
+          node.id = interface.node_id) ON
+        iia.staticipaddress_id = staticipaddress.id
+      LEFT JOIN (
+        maasserver_dnsresource_ip_addresses AS dia
+        JOIN maasserver_dnsresource AS dnsresource ON
+          dia.dnsresource_id = dnsresource.id) ON
+        dia.staticipaddress_id = staticipaddress.id
+      JOIN maasserver_domain AS domain ON
+        domain.id = node.domain_id OR domain.id = dnsresource.domain_id
+      WHERE staticipaddress.id = OLD.id OR staticipaddress.id = NEW.id)
+    LOOP
+      PERFORM pg_notify('domain_update',CAST(dom.id AS text));
+    END LOOP;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.ipaddress_domain_update_notify() OWNER TO gavin;
+
+--
+-- Name: ipaddress_machine_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION ipaddress_machine_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    node RECORD;
+    pnode RECORD;
+BEGIN
+  FOR node IN (
+    SELECT DISTINCT ON (maasserver_node.id)
+      system_id, node_type, parent_id
+    FROM
+      maasserver_node,
+      maasserver_interface,
+      maasserver_interface_ip_addresses AS ip_link
+    WHERE ip_link.staticipaddress_id = NEW.id
+    AND ip_link.interface_id = maasserver_interface.id
+    AND maasserver_node.id = maasserver_interface.node_id)
+  LOOP
+    IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+    ELSIF node.node_type IN (2, 3, 4) THEN
+      PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+    ELSIF node.parent_id IS NOT NULL THEN
+      SELECT system_id INTO pnode
+      FROM maasserver_node
+      WHERE id = node.parent_id;
+      PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+    ELSE
+      PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+    END IF;
+  END LOOP;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.ipaddress_machine_update_notify() OWNER TO gavin;
+
+--
+-- Name: ipaddress_subnet_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION ipaddress_subnet_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF OLD.subnet_id != NEW.subnet_id THEN
+    IF OLD.subnet_id IS NOT NULL THEN
+      PERFORM pg_notify('subnet_update',CAST(OLD.subnet_id AS text));
+    END IF;
+  END IF;
+  IF NEW.subnet_id IS NOT NULL THEN
+    PERFORM pg_notify('subnet_update',CAST(NEW.subnet_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.ipaddress_subnet_update_notify() OWNER TO gavin;
+
+--
+-- Name: iprange_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION iprange_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('iprange_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.iprange_create_notify() OWNER TO gavin;
+
+--
+-- Name: iprange_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION iprange_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('iprange_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.iprange_delete_notify() OWNER TO gavin;
+
+--
+-- Name: iprange_subnet_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION iprange_subnet_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF OLD.subnet_id IS NOT NULL THEN
+    PERFORM pg_notify('subnet_update',CAST(OLD.subnet_id AS text));
+  END IF;
+  RETURN OLD;
+END;
+$$;
+
+
+ALTER FUNCTION public.iprange_subnet_delete_notify() OWNER TO gavin;
+
+--
+-- Name: iprange_subnet_insert_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION iprange_subnet_insert_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF NEW.subnet_id IS NOT NULL THEN
+    PERFORM pg_notify('subnet_update',CAST(NEW.subnet_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.iprange_subnet_insert_notify() OWNER TO gavin;
+
+--
+-- Name: iprange_subnet_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION iprange_subnet_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF OLD.subnet_id != NEW.subnet_id THEN
+    IF OLD.subnet_id IS NOT NULL THEN
+      PERFORM pg_notify('subnet_update',CAST(OLD.subnet_id AS text));
+    END IF;
+  END IF;
+  IF NEW.subnet_id IS NOT NULL THEN
+    PERFORM pg_notify('subnet_update',CAST(NEW.subnet_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.iprange_subnet_update_notify() OWNER TO gavin;
+
+--
+-- Name: iprange_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION iprange_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('iprange_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.iprange_update_notify() OWNER TO gavin;
+
+--
+-- Name: machine_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION machine_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('machine_create',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.machine_create_notify() OWNER TO gavin;
+
+--
+-- Name: machine_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION machine_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('machine_delete',CAST(OLD.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.machine_delete_notify() OWNER TO gavin;
+
+--
+-- Name: machine_device_tag_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION machine_device_tag_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = NEW.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.machine_device_tag_link_notify() OWNER TO gavin;
+
+--
+-- Name: machine_device_tag_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION machine_device_tag_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = OLD.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.machine_device_tag_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: machine_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION machine_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('machine_update',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.machine_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_blockdevice_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_blockdevice_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = NEW.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(
+      node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_blockdevice_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_blockdevice_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_blockdevice_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = OLD.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(
+      node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_blockdevice_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_blockdevice_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_blockdevice_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = NEW.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(
+      node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_blockdevice_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_cacheset_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_cacheset_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partition,
+       maasserver_partitiontable,
+       maasserver_filesystem
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id =
+      maasserver_partition.partition_table_id
+  AND maasserver_partition.id = maasserver_filesystem.partition_id
+  AND maasserver_filesystem.cache_set_id = NEW.id;
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_cacheset_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_cacheset_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_cacheset_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partition,
+       maasserver_partitiontable,
+       maasserver_filesystem
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id =
+      maasserver_partition.partition_table_id
+  AND maasserver_partition.id = maasserver_filesystem.partition_id
+  AND maasserver_filesystem.cache_set_id = OLD.id;
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_cacheset_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_cacheset_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_cacheset_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partition,
+       maasserver_partitiontable,
+       maasserver_filesystem
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id =
+      maasserver_partition.partition_table_id
+  AND maasserver_partition.id = maasserver_filesystem.partition_id
+  AND maasserver_filesystem.cache_set_id = NEW.id;
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_cacheset_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_filesystem_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_filesystem_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  IF NEW.block_device_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node,
+           maasserver_blockdevice
+     WHERE maasserver_node.id = maasserver_blockdevice.node_id
+       AND maasserver_blockdevice.id = NEW.block_device_id;
+  ELSIF NEW.partition_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node,
+           maasserver_blockdevice,
+           maasserver_partition,
+           maasserver_partitiontable
+     WHERE maasserver_node.id = maasserver_blockdevice.node_id
+       AND maasserver_blockdevice.id =
+           maasserver_partitiontable.block_device_id
+       AND maasserver_partitiontable.id =
+           maasserver_partition.partition_table_id
+       AND maasserver_partition.id = NEW.partition_id;
+  ELSIF NEW.node_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node
+     WHERE maasserver_node.id = NEW.node_id;
+  END IF;
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update', CAST(node.system_id AS text));
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_filesystem_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_filesystem_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_filesystem_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  IF OLD.block_device_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node,
+           maasserver_blockdevice
+     WHERE maasserver_node.id = maasserver_blockdevice.node_id
+       AND maasserver_blockdevice.id = OLD.block_device_id;
+  ELSIF OLD.partition_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node,
+           maasserver_blockdevice,
+           maasserver_partition,
+           maasserver_partitiontable
+     WHERE maasserver_node.id = maasserver_blockdevice.node_id
+       AND maasserver_blockdevice.id =
+           maasserver_partitiontable.block_device_id
+       AND maasserver_partitiontable.id =
+           maasserver_partition.partition_table_id
+       AND maasserver_partition.id = OLD.partition_id;
+  ELSIF OLD.node_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node
+     WHERE maasserver_node.id = OLD.node_id;
+  END IF;
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update', CAST(node.system_id AS text));
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_filesystem_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_filesystem_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_filesystem_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  IF NEW.block_device_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node,
+           maasserver_blockdevice
+     WHERE maasserver_node.id = maasserver_blockdevice.node_id
+       AND maasserver_blockdevice.id = NEW.block_device_id;
+  ELSIF NEW.partition_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node,
+           maasserver_blockdevice,
+           maasserver_partition,
+           maasserver_partitiontable
+     WHERE maasserver_node.id = maasserver_blockdevice.node_id
+       AND maasserver_blockdevice.id =
+           maasserver_partitiontable.block_device_id
+       AND maasserver_partitiontable.id =
+           maasserver_partition.partition_table_id
+       AND maasserver_partition.id = NEW.partition_id;
+  ELSIF NEW.node_id IS NOT NULL
+  THEN
+    SELECT system_id, node_type INTO node
+      FROM maasserver_node
+     WHERE maasserver_node.id = NEW.node_id;
+  END IF;
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update', CAST(node.system_id AS text));
+  END IF;
+
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_filesystem_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_filesystemgroup_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_filesystemgroup_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partition,
+       maasserver_partitiontable,
+       maasserver_filesystem
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id =
+      maasserver_partition.partition_table_id
+  AND maasserver_partition.id = maasserver_filesystem.partition_id
+  AND (maasserver_filesystem.filesystem_group_id = NEW.id
+      OR maasserver_filesystem.cache_set_id = NEW.cache_set_id);
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_filesystemgroup_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_filesystemgroup_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_filesystemgroup_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partition,
+       maasserver_partitiontable,
+       maasserver_filesystem
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id =
+      maasserver_partition.partition_table_id
+  AND maasserver_partition.id = maasserver_filesystem.partition_id
+  AND (maasserver_filesystem.filesystem_group_id = OLD.id
+      OR maasserver_filesystem.cache_set_id = OLD.cache_set_id);
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_filesystemgroup_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_filesystemgroup_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_filesystemgroup_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partition,
+       maasserver_partitiontable,
+       maasserver_filesystem
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id =
+      maasserver_partition.partition_table_id
+  AND maasserver_partition.id = maasserver_filesystem.partition_id
+  AND (maasserver_filesystem.filesystem_group_id = NEW.id
+      OR maasserver_filesystem.cache_set_id = NEW.cache_set_id);
+
+  IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_filesystemgroup_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_interface_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_interface_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = NEW.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(
+      node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_interface_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_interface_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_interface_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = OLD.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(
+      node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_interface_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_interface_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_interface_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  IF OLD.node_id != NEW.node_id THEN
+    SELECT system_id, node_type, parent_id INTO node
+    FROM maasserver_node
+    WHERE id = OLD.node_id;
+
+    IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+    ELSIF node.node_type IN (2, 3, 4) THEN
+      PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+    ELSIF node.parent_id IS NOT NULL THEN
+      SELECT system_id INTO pnode
+      FROM maasserver_node
+      WHERE id = node.parent_id;
+      PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+    ELSE
+      PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+    END IF;
+  END IF;
+
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = NEW.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_interface_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_noderesult_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_noderesult_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = NEW.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(
+      node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_noderesult_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_noderesult_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_noderesult_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node
+  WHERE id = OLD.node_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(
+      node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_noderesult_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_partition_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_partition_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partitiontable
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id = NEW.partition_table_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_partition_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_partition_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_partition_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partitiontable
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id = OLD.partition_table_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_partition_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_partition_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_partition_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node,
+       maasserver_blockdevice,
+       maasserver_partitiontable
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = maasserver_partitiontable.block_device_id
+  AND maasserver_partitiontable.id = NEW.partition_table_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_partition_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_partitiontable_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_partitiontable_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node, maasserver_blockdevice
+    WHERE maasserver_node.id = maasserver_blockdevice.node_id
+    AND maasserver_blockdevice.id = NEW.block_device_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_partitiontable_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_partitiontable_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_partitiontable_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node, maasserver_blockdevice
+    WHERE maasserver_node.id = maasserver_blockdevice.node_id
+    AND maasserver_blockdevice.id = OLD.block_device_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_partitiontable_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_partitiontable_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_partitiontable_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node, maasserver_blockdevice
+    WHERE maasserver_node.id = maasserver_blockdevice.node_id
+    AND maasserver_blockdevice.id = NEW.block_device_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_partitiontable_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_physblockdevice_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_physblockdevice_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node, maasserver_blockdevice
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = NEW.blockdevice_ptr_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_physblockdevice_update_notify() OWNER TO gavin;
+
+--
+-- Name: nd_sipaddress_dns_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_sipaddress_dns_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  domain RECORD;
+BEGIN
+  SELECT maasserver_domain.id INTO domain
+  FROM maasserver_node, maasserver_interface, maasserver_domain
+  WHERE maasserver_node.id = maasserver_interface.node_id
+  AND maasserver_domain.id = maasserver_node.domain_id
+  AND maasserver_interface.id = NEW.interface_id;
+
+  IF domain.id IS NOT NULL THEN
+    PERFORM pg_notify('domain_update',CAST(domain.id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_sipaddress_dns_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_sipaddress_dns_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_sipaddress_dns_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  domain RECORD;
+BEGIN
+  SELECT maasserver_domain.id INTO domain
+  FROM maasserver_node, maasserver_interface, maasserver_domain
+  WHERE maasserver_node.id = maasserver_interface.node_id
+  AND maasserver_domain.id = maasserver_node.domain_id
+  AND maasserver_interface.id = OLD.interface_id;
+
+  IF domain.id IS NOT NULL THEN
+    PERFORM pg_notify('domain_update',CAST(domain.id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_sipaddress_dns_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_sipaddress_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_sipaddress_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node, maasserver_interface
+  WHERE maasserver_node.id = maasserver_interface.node_id
+  AND maasserver_interface.id = NEW.interface_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_sipaddress_link_notify() OWNER TO gavin;
+
+--
+-- Name: nd_sipaddress_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_sipaddress_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+  pnode RECORD;
+BEGIN
+  SELECT system_id, node_type, parent_id INTO node
+  FROM maasserver_node, maasserver_interface
+  WHERE maasserver_node.id = maasserver_interface.node_id
+  AND maasserver_interface.id = OLD.interface_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  ELSIF node.node_type IN (2, 3, 4) THEN
+    PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+  ELSIF node.parent_id IS NOT NULL THEN
+    SELECT system_id INTO pnode
+    FROM maasserver_node
+    WHERE id = node.parent_id;
+    PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+  ELSE
+    PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_sipaddress_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: nd_virtblockdevice_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION nd_virtblockdevice_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  node RECORD;
+BEGIN
+  SELECT system_id, node_type INTO node
+  FROM maasserver_node, maasserver_blockdevice
+  WHERE maasserver_node.id = maasserver_blockdevice.node_id
+  AND maasserver_blockdevice.id = NEW.blockdevice_ptr_id;
+
+  IF node.node_type = 0 THEN
+    PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.nd_virtblockdevice_update_notify() OWNER TO gavin;
+
+--
+-- Name: neighbour_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION neighbour_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('neighbour_create',CAST(NEW.ip AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.neighbour_create_notify() OWNER TO gavin;
+
+--
+-- Name: neighbour_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION neighbour_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('neighbour_delete',CAST(OLD.ip AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.neighbour_delete_notify() OWNER TO gavin;
+
+--
+-- Name: neighbour_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION neighbour_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('neighbour_update',CAST(NEW.ip AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.neighbour_update_notify() OWNER TO gavin;
+
+--
+-- Name: node_type_change_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION node_type_change_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF (OLD.node_type != NEW.node_type AND NOT (
+      (
+        OLD.node_type = 2 OR
+        OLD.node_type = 3 OR
+        OLD.node_type = 4
+      ) AND (
+        NEW.node_type = 2 OR
+        NEW.node_type = 3 OR
+        NEW.node_type = 4
+      ))) THEN
+    CASE OLD.node_type
+      WHEN 0 THEN
+        PERFORM pg_notify('machine_delete',CAST(
+          OLD.system_id AS TEXT));
+      WHEN 1 THEN
+        PERFORM pg_notify('device_delete',CAST(
+          OLD.system_id AS TEXT));
+      WHEN 2 THEN
+        PERFORM pg_notify('controller_delete',CAST(
+          OLD.system_id AS TEXT));
+      WHEN 3 THEN
+        PERFORM pg_notify('controller_delete',CAST(
+          OLD.system_id AS TEXT));
+      WHEN 4 THEN
+        PERFORM pg_notify('controller_delete',CAST(
+          OLD.system_id AS TEXT));
+    END CASE;
+    CASE NEW.node_type
+      WHEN 0 THEN
+        PERFORM pg_notify('machine_create',CAST(
+          NEW.system_id AS TEXT));
+      WHEN 1 THEN
+        PERFORM pg_notify('device_create',CAST(
+          NEW.system_id AS TEXT));
+      WHEN 2 THEN
+        PERFORM pg_notify('controller_create',CAST(
+          NEW.system_id AS TEXT));
+      WHEN 3 THEN
+        PERFORM pg_notify('controller_create',CAST(
+          NEW.system_id AS TEXT));
+      WHEN 4 THEN
+        PERFORM pg_notify('controller_create',CAST(
+          NEW.system_id AS TEXT));
+    END CASE;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.node_type_change_notify() OWNER TO gavin;
+
+--
+-- Name: packagerepository_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION packagerepository_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('packagerepository_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.packagerepository_create_notify() OWNER TO gavin;
+
+--
+-- Name: packagerepository_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION packagerepository_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('packagerepository_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.packagerepository_delete_notify() OWNER TO gavin;
+
+--
+-- Name: packagerepository_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION packagerepository_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('packagerepository_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.packagerepository_update_notify() OWNER TO gavin;
+
+--
+-- Name: rack_controller_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION rack_controller_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_create',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.rack_controller_create_notify() OWNER TO gavin;
+
+--
+-- Name: rack_controller_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION rack_controller_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_delete',CAST(OLD.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.rack_controller_delete_notify() OWNER TO gavin;
+
+--
+-- Name: rack_controller_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION rack_controller_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_update',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.rack_controller_update_notify() OWNER TO gavin;
+
+--
+-- Name: region_and_rack_controller_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION region_and_rack_controller_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_create',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.region_and_rack_controller_create_notify() OWNER TO gavin;
+
+--
+-- Name: region_and_rack_controller_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION region_and_rack_controller_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_delete',CAST(OLD.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.region_and_rack_controller_delete_notify() OWNER TO gavin;
+
+--
+-- Name: region_and_rack_controller_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION region_and_rack_controller_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_update',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.region_and_rack_controller_update_notify() OWNER TO gavin;
+
+--
+-- Name: region_controller_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION region_controller_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_create',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.region_controller_create_notify() OWNER TO gavin;
+
+--
+-- Name: region_controller_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION region_controller_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_delete',CAST(OLD.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.region_controller_delete_notify() OWNER TO gavin;
+
+--
+-- Name: region_controller_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION region_controller_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('controller_update',CAST(NEW.system_id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.region_controller_update_notify() OWNER TO gavin;
+
+--
+-- Name: rrset_sipaddress_link_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION rrset_sipaddress_link_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  domain RECORD;
+BEGIN
+  SELECT maasserver_domain.id INTO domain
+  FROM maasserver_dnsresource, maasserver_domain
+  WHERE maasserver_domain.id = maasserver_dnsresource.domain_id
+  AND maasserver_dnsresource.id = NEW.dnsresource_id;
+
+  IF domain.id IS NOT NULL THEN
+    PERFORM pg_notify('domain_update',CAST(domain.id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.rrset_sipaddress_link_notify() OWNER TO gavin;
+
+--
+-- Name: rrset_sipaddress_unlink_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION rrset_sipaddress_unlink_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  domain RECORD;
+BEGIN
+  SELECT maasserver_domain.id INTO domain
+  FROM maasserver_dnsresource, maasserver_domain
+  WHERE maasserver_domain.id = maasserver_dnsresource.domain_id
+  AND maasserver_dnsresource.id = OLD.dnsresource_id;
+
+  IF domain.id IS NOT NULL THEN
+    PERFORM pg_notify('domain_update',CAST(domain.id AS text));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.rrset_sipaddress_unlink_notify() OWNER TO gavin;
+
+--
+-- Name: service_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION service_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('service_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.service_create_notify() OWNER TO gavin;
+
+--
+-- Name: service_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION service_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('service_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.service_delete_notify() OWNER TO gavin;
+
+--
+-- Name: service_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION service_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('service_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.service_update_notify() OWNER TO gavin;
+
+--
+-- Name: space_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION space_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('space_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.space_create_notify() OWNER TO gavin;
+
+--
+-- Name: space_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION space_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('space_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.space_delete_notify() OWNER TO gavin;
+
+--
+-- Name: space_machine_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION space_machine_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    node RECORD;
+    pnode RECORD;
+BEGIN
+  FOR node IN (
+    SELECT DISTINCT ON (maasserver_node.id)
+      system_id, node_type, parent_id
+    FROM
+      maasserver_node,
+      maasserver_space,
+      maasserver_subnet,
+      maasserver_vlan,
+      maasserver_interface,
+      maasserver_interface_ip_addresses AS ip_link,
+      maasserver_staticipaddress
+    WHERE maasserver_space.id = NEW.id
+    AND maasserver_subnet.vlan_id = maasserver_vlan.id
+    AND maasserver_vlan.space_id IS NOT DISTINCT FROM maasserver_space.id
+    AND maasserver_staticipaddress.subnet_id = maasserver_subnet.id
+    AND ip_link.staticipaddress_id = maasserver_staticipaddress.id
+    AND ip_link.interface_id = maasserver_interface.id
+    AND maasserver_node.id = maasserver_interface.node_id)
+  LOOP
+    IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+    ELSIF node.node_type IN (2, 3, 4) THEN
+      PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+    ELSIF node.parent_id IS NOT NULL THEN
+      SELECT system_id INTO pnode
+      FROM maasserver_node
+      WHERE id = node.parent_id;
+      PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+    ELSE
+      PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+    END IF;
+  END LOOP;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.space_machine_update_notify() OWNER TO gavin;
+
+--
+-- Name: space_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION space_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('space_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.space_update_notify() OWNER TO gavin;
+
+--
+-- Name: sshkey_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sshkey_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('sshkey_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sshkey_create_notify() OWNER TO gavin;
+
+--
+-- Name: sshkey_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sshkey_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('sshkey_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sshkey_delete_notify() OWNER TO gavin;
+
+--
+-- Name: sshkey_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sshkey_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('sshkey_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sshkey_update_notify() OWNER TO gavin;
+
+--
+-- Name: staticroute_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION staticroute_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('staticroute_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.staticroute_create_notify() OWNER TO gavin;
+
+--
+-- Name: staticroute_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION staticroute_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('staticroute_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.staticroute_delete_notify() OWNER TO gavin;
+
+--
+-- Name: staticroute_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION staticroute_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('staticroute_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.staticroute_update_notify() OWNER TO gavin;
+
+--
+-- Name: subnet_create_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION subnet_create_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('subnet_create',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.subnet_create_notify() OWNER TO gavin;
+
+--
+-- Name: subnet_delete_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION subnet_delete_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('subnet_delete',CAST(OLD.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.subnet_delete_notify() OWNER TO gavin;
+
+--
+-- Name: subnet_machine_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION subnet_machine_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+    node RECORD;
+    pnode RECORD;
+BEGIN
+  FOR node IN (
+    SELECT DISTINCT ON (maasserver_node.id)
+      system_id, node_type, parent_id
+    FROM
+      maasserver_node,
+      maasserver_subnet,
+      maasserver_interface,
+      maasserver_interface_ip_addresses AS ip_link,
+      maasserver_staticipaddress
+    WHERE maasserver_subnet.id = NEW.id
+    AND maasserver_staticipaddress.subnet_id = maasserver_subnet.id
+    AND ip_link.staticipaddress_id = maasserver_staticipaddress.id
+    AND ip_link.interface_id = maasserver_interface.id
+    AND maasserver_node.id = maasserver_interface.node_id)
+  LOOP
+    IF node.node_type = 0 THEN
+      PERFORM pg_notify('machine_update',CAST(node.system_id AS text));
+    ELSIF node.node_type IN (2, 3, 4) THEN
+      PERFORM pg_notify('controller_update',CAST(node.system_id AS text));
+    ELSIF node.parent_id IS NOT NULL THEN
+      SELECT system_id INTO pnode
+      FROM maasserver_node
+      WHERE id = node.parent_id;
+      PERFORM pg_notify('machine_update',CAST(pnode.system_id AS text));
+    ELSE
+      PERFORM pg_notify('device_update',CAST(node.system_id AS text));
+    END IF;
+  END LOOP;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.subnet_machine_update_notify() OWNER TO gavin;
+
+--
+-- Name: subnet_update_notify(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION subnet_update_notify() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+BEGIN
+  PERFORM pg_notify('subnet_update',CAST(NEW.id AS text));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.subnet_update_notify() OWNER TO gavin;
+
+SET default_tablespace = '';
+
+SET default_with_oids = false;
+
+--
+-- Name: maasserver_regioncontrollerprocess; Type: TABLE; Schema: public; Owner: gavin
+--
+
+CREATE TABLE maasserver_regioncontrollerprocess (
+    id integer NOT NULL,
+    created timestamp with time zone NOT NULL,
+    updated timestamp with time zone NOT NULL,
+    pid integer NOT NULL,
+    region_id integer NOT NULL
+);
+
+
+ALTER TABLE maasserver_regioncontrollerprocess OWNER TO gavin;
+
+--
+-- Name: sys_core_get_managing_count(maasserver_regioncontrollerprocess); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_core_get_managing_count(process maasserver_regioncontrollerprocess) RETURNS integer
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  RETURN (SELECT count(*)
+    FROM maasserver_node
+    WHERE maasserver_node.managing_process_id = process.id);
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_core_get_managing_count(process maasserver_regioncontrollerprocess) OWNER TO gavin;
+
+--
+-- Name: maasserver_node; Type: TABLE; Schema: public; Owner: gavin
+--
+
+CREATE TABLE maasserver_node (
+    id integer NOT NULL,
+    created timestamp with time zone NOT NULL,
+    updated timestamp with time zone NOT NULL,
+    system_id character varying(41) NOT NULL,
+    hostname character varying(255) NOT NULL,
+    status integer NOT NULL,
+    bios_boot_method character varying(31),
+    osystem character varying(255) NOT NULL,
+    distro_series character varying(255) NOT NULL,
+    architecture character varying(31),
+    min_hwe_kernel character varying(31),
+    hwe_kernel character varying(31),
+    agent_name character varying(255),
+    error_description text NOT NULL,
+    cpu_count integer NOT NULL,
+    memory integer NOT NULL,
+    swap_size bigint,
+    instance_power_parameters text NOT NULL,
+    power_state character varying(10) NOT NULL,
+    power_state_updated timestamp with time zone,
+    error character varying(255) NOT NULL,
+    netboot boolean NOT NULL,
+    license_key character varying(30),
+    boot_cluster_ip inet,
+    enable_ssh boolean NOT NULL,
+    skip_networking boolean NOT NULL,
+    skip_storage boolean NOT NULL,
+    boot_interface_id integer,
+    gateway_link_ipv4_id integer,
+    gateway_link_ipv6_id integer,
+    owner_id integer,
+    parent_id integer,
+    token_id integer,
+    zone_id integer NOT NULL,
+    boot_disk_id integer,
+    node_type integer NOT NULL,
+    domain_id integer,
+    dns_process_id integer,
+    bmc_id integer,
+    address_ttl integer,
+    status_expires timestamp with time zone,
+    power_state_queried timestamp with time zone,
+    url character varying(255) NOT NULL,
+    managing_process_id integer,
+    last_image_sync timestamp with time zone,
+    previous_status integer NOT NULL,
+    default_user character varying(32) NOT NULL,
+    cpu_speed integer NOT NULL,
+    dynamic boolean NOT NULL,
+    CONSTRAINT maasserver_node_address_ttl_check CHECK ((address_ttl >= 0))
+);
+
+
+ALTER TABLE maasserver_node OWNER TO gavin;
+
+--
+-- Name: sys_core_get_num_conn(maasserver_node); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_core_get_num_conn(rack maasserver_node) RETURNS integer
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  RETURN (
+    SELECT count(*)
+    FROM
+      maasserver_regionrackrpcconnection AS connection
+    WHERE connection.rack_controller_id = rack.id);
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_core_get_num_conn(rack maasserver_node) OWNER TO gavin;
+
+--
+-- Name: sys_core_get_num_processes(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_core_get_num_processes() RETURNS integer
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  RETURN (
+    SELECT count(*) FROM maasserver_regioncontrollerprocess);
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_core_get_num_processes() OWNER TO gavin;
+
+--
+-- Name: sys_core_pick_new_region(maasserver_node); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_core_pick_new_region(rack maasserver_node) RETURNS maasserver_regioncontrollerprocess
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  selected_managing integer;
+  number_managing integer;
+  selected_process maasserver_regioncontrollerprocess;
+  process maasserver_regioncontrollerprocess;
+BEGIN
+  -- Get best region controller that can manage this rack controller.
+  -- This is identified by picking a region controller process that
+  -- at least has a connection to the rack controller and managing the
+  -- least number of rack controllers.
+  FOR process IN (
+    SELECT DISTINCT ON (maasserver_regioncontrollerprocess.id)
+      maasserver_regioncontrollerprocess.*
+    FROM
+      maasserver_regioncontrollerprocess,
+      maasserver_regioncontrollerprocessendpoint,
+      maasserver_regionrackrpcconnection
+    WHERE maasserver_regionrackrpcconnection.rack_controller_id = rack.id
+      AND maasserver_regionrackrpcconnection.endpoint_id =
+        maasserver_regioncontrollerprocessendpoint.id
+      AND maasserver_regioncontrollerprocessendpoint.process_id =
+        maasserver_regioncontrollerprocess.id)
+  LOOP
+    IF selected_process IS NULL THEN
+      -- First time through the loop so set the default.
+      selected_process = process;
+      selected_managing = sys_core_get_managing_count(process);
+    ELSE
+      -- See if the current process is managing less then the currently
+      -- selected process.
+      number_managing = sys_core_get_managing_count(process);
+      IF number_managing = 0 THEN
+        -- This process is managing zero so its the best, so we exit the
+        -- loop now to return the selected.
+        selected_process = process;
+        EXIT;
+      ELSIF number_managing < selected_managing THEN
+        -- Managing less than the currently selected; select this process
+        -- instead.
+        selected_process = process;
+        selected_managing = number_managing;
+      END IF;
+    END IF;
+  END LOOP;
+  RETURN selected_process;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_core_pick_new_region(rack maasserver_node) OWNER TO gavin;
+
+--
+-- Name: sys_core_rpc_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_core_rpc_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  rack_controller maasserver_node;
+  region_process maasserver_regioncontrollerprocess;
+BEGIN
+  -- Connection from region <-> rack, has been removed. If that region
+  -- process was managing that rack controller then a new one needs to
+  -- be selected.
+  SELECT maasserver_node.* INTO rack_controller
+  FROM maasserver_node
+  WHERE maasserver_node.id = OLD.rack_controller_id;
+
+  -- Get the region process from the endpoint.
+  SELECT
+    process.* INTO region_process
+  FROM
+    maasserver_regioncontrollerprocess AS process,
+    maasserver_regioncontrollerprocessendpoint AS endpoint
+  WHERE process.id = endpoint.process_id
+    AND endpoint.id = OLD.endpoint_id;
+
+  -- Only perform an action if processes equal.
+  IF rack_controller.managing_process_id = region_process.id THEN
+    -- Region process was managing this rack controller. Tell it to stop
+    -- watching the rack controller.
+    PERFORM pg_notify(
+      CONCAT('sys_core_', region_process.id),
+      CONCAT('unwatch_', CAST(rack_controller.id AS text)));
+
+    -- Pick a new region process for this rack controller.
+    region_process = sys_core_pick_new_region(rack_controller);
+
+    -- Update the rack controller and inform the new process.
+    UPDATE maasserver_node
+    SET managing_process_id = region_process.id
+    WHERE maasserver_node.id = rack_controller.id;
+    IF region_process.id IS NOT NULL THEN
+      PERFORM pg_notify(
+        CONCAT('sys_core_', region_process.id),
+        CONCAT('watch_', CAST(rack_controller.id AS text)));
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_core_rpc_delete() OWNER TO gavin;
+
+--
+-- Name: sys_core_rpc_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_core_rpc_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  rack_controller maasserver_node;
+  region_process maasserver_regioncontrollerprocess;
+BEGIN
+  -- New connection from region <-> rack, check that the rack controller
+  -- has a managing region controller.
+  SELECT maasserver_node.* INTO rack_controller
+  FROM maasserver_node
+  WHERE maasserver_node.id = NEW.rack_controller_id;
+
+  IF rack_controller.managing_process_id IS NULL THEN
+    -- No managing region process for this rack controller.
+    PERFORM sys_core_set_new_region(rack_controller);
+  ELSE
+    -- Currently managed check that the managing process is not dead.
+    SELECT maasserver_regioncontrollerprocess.* INTO region_process
+    FROM maasserver_regioncontrollerprocess
+    WHERE maasserver_regioncontrollerprocess.id =
+      rack_controller.managing_process_id;
+    IF EXTRACT(EPOCH FROM region_process.updated) -
+      EXTRACT(EPOCH FROM now()) > 90 THEN
+      -- Region controller process is dead. A new region process needs to
+      -- be selected for this rack controller.
+      UPDATE maasserver_node SET managing_process_id = NULL
+      WHERE maasserver_node.id = NEW.rack_controller_id;
+      NEW.rack_controller_id = NULL;
+      PERFORM sys_core_set_new_region(rack_controller);
+    ELSE
+      -- Currently being managed but lets see if we can re-balance the
+      -- managing processes better. We only do the re-balance once the
+      -- rack controller is connected to more than half of the running
+      -- processes.
+      IF sys_core_get_num_conn(rack_controller) /
+        sys_core_get_num_processes() > 0.5 THEN
+        -- Pick a new region process for this rack controller. Only update
+        -- and perform the notification if the selection is different.
+        region_process = sys_core_pick_new_region(rack_controller);
+        IF region_process.id != rack_controller.managing_process_id THEN
+          -- Alter the old process that its no longer responsable for
+          -- this rack controller.
+          PERFORM pg_notify(
+            CONCAT('sys_core_', rack_controller.managing_process_id),
+            CONCAT('unwatch_', CAST(rack_controller.id AS text)));
+          -- Update the rack controller and alert the region controller.
+          UPDATE maasserver_node
+          SET managing_process_id = region_process.id
+          WHERE maasserver_node.id = rack_controller.id;
+          PERFORM pg_notify(
+            CONCAT('sys_core_', region_process.id),
+            CONCAT('watch_', CAST(rack_controller.id AS text)));
+        END IF;
+      END IF;
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_core_rpc_insert() OWNER TO gavin;
+
+--
+-- Name: sys_core_set_new_region(maasserver_node); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_core_set_new_region(rack maasserver_node) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  region_process maasserver_regioncontrollerprocess;
+BEGIN
+  -- Pick the new region process to manage this rack controller.
+  region_process = sys_core_pick_new_region(rack);
+
+  -- Update the rack controller and alert the region controller.
+  UPDATE maasserver_node SET managing_process_id = region_process.id
+  WHERE maasserver_node.id = rack.id;
+  PERFORM pg_notify(
+    CONCAT('sys_core_', region_process.id),
+    CONCAT('watch_', CAST(rack.id AS text)));
+  RETURN;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_core_set_new_region(rack maasserver_node) OWNER TO gavin;
+
+--
+-- Name: maasserver_vlan; Type: TABLE; Schema: public; Owner: gavin
+--
+
+CREATE TABLE maasserver_vlan (
+    id integer NOT NULL,
+    created timestamp with time zone NOT NULL,
+    updated timestamp with time zone NOT NULL,
+    name character varying(256),
+    vid integer NOT NULL,
+    mtu integer NOT NULL,
+    fabric_id integer NOT NULL,
+    dhcp_on boolean NOT NULL,
+    primary_rack_id integer,
+    secondary_rack_id integer,
+    external_dhcp inet,
+    description text NOT NULL,
+    relay_vlan_id integer,
+    space_id integer
+);
+
+
+ALTER TABLE maasserver_vlan OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_alert(maasserver_vlan); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_alert(vlan maasserver_vlan) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  PERFORM pg_notify(CONCAT('sys_dhcp_', vlan.primary_rack_id), '');
+  IF vlan.secondary_rack_id IS NOT NULL THEN
+    PERFORM pg_notify(CONCAT('sys_dhcp_', vlan.secondary_rack_id), '');
+  END IF;
+  RETURN;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_alert(vlan maasserver_vlan) OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_config_ntp_servers_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_config_ntp_servers_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF OLD.name IN ('ntp_servers', 'ntp_external_only') THEN
+    PERFORM sys_dhcp_update_all_vlans();
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_config_ntp_servers_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_config_ntp_servers_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_config_ntp_servers_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF NEW.name = 'ntp_servers' THEN
+    PERFORM sys_dhcp_update_all_vlans();
+  ELSIF NEW.name = 'ntp_external_only' THEN
+    PERFORM sys_dhcp_update_all_vlans();
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_config_ntp_servers_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_config_ntp_servers_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_config_ntp_servers_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF OLD.name IN ('ntp_servers', 'ntp_external_only')
+  OR NEW.name IN ('ntp_servers', 'ntp_external_only') THEN
+    IF OLD.value != NEW.value THEN
+      PERFORM sys_dhcp_update_all_vlans();
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_config_ntp_servers_update() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_interface_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_interface_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if DHCP is enabled and the interface name or MAC
+  -- address has changed.
+  IF OLD.name != NEW.name OR OLD.mac_address != NEW.mac_address THEN
+    FOR vlan IN (
+      SELECT DISTINCT ON (maasserver_vlan.id)
+        maasserver_vlan.*
+      FROM
+        maasserver_vlan,
+        maasserver_subnet,
+        maasserver_staticipaddress,
+        maasserver_interface_ip_addresses AS ip_link
+      WHERE maasserver_staticipaddress.subnet_id = maasserver_subnet.id
+      AND ip_link.staticipaddress_id = maasserver_staticipaddress.id
+      AND ip_link.interface_id = NEW.id
+      AND maasserver_staticipaddress.alloc_type != 6
+      AND maasserver_staticipaddress.ip IS NOT NULL
+      AND host(maasserver_staticipaddress.ip) != ''
+      AND maasserver_vlan.id = maasserver_subnet.vlan_id
+      AND maasserver_vlan.dhcp_on)
+    LOOP
+      PERFORM sys_dhcp_alert(vlan);
+    END LOOP;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_interface_update() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_iprange_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_iprange_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if DHCP is enabled and was dynamic range.
+  IF OLD.type = 'dynamic' THEN
+    SELECT maasserver_vlan.* INTO vlan
+    FROM maasserver_vlan, maasserver_subnet
+    WHERE maasserver_subnet.id = OLD.subnet_id AND
+      maasserver_subnet.vlan_id = maasserver_vlan.id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_iprange_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_iprange_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_iprange_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if DHCP is enabled and a dynamic range.
+  IF NEW.type = 'dynamic' THEN
+    SELECT maasserver_vlan.* INTO vlan
+    FROM maasserver_vlan, maasserver_subnet
+    WHERE maasserver_subnet.id = NEW.subnet_id AND
+      maasserver_subnet.vlan_id = maasserver_vlan.id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_iprange_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_iprange_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_iprange_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if DHCP is enabled and was or is now a dynamic range.
+  IF OLD.type = 'dynamic' OR NEW.type = 'dynamic' THEN
+    SELECT maasserver_vlan.* INTO vlan
+    FROM maasserver_vlan, maasserver_subnet
+    WHERE maasserver_subnet.id = NEW.subnet_id AND
+      maasserver_subnet.vlan_id = maasserver_vlan.id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_iprange_update() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_node_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_node_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if on every interface on the node that is managed when
+  -- the node hostname is changed.
+  IF OLD.hostname != NEW.hostname THEN
+    FOR vlan IN (
+      SELECT DISTINCT ON (maasserver_vlan.id)
+        maasserver_vlan.*
+      FROM
+        maasserver_vlan,
+        maasserver_staticipaddress,
+        maasserver_subnet,
+        maasserver_interface,
+        maasserver_interface_ip_addresses AS ip_link
+      WHERE maasserver_staticipaddress.subnet_id = maasserver_subnet.id
+      AND ip_link.staticipaddress_id = maasserver_staticipaddress.id
+      AND ip_link.interface_id = maasserver_interface.id
+      AND maasserver_interface.node_id = NEW.id
+      AND maasserver_staticipaddress.alloc_type != 6
+      AND maasserver_staticipaddress.ip IS NOT NULL
+      AND host(maasserver_staticipaddress.ip) != ''
+      AND maasserver_vlan.id = maasserver_subnet.vlan_id
+      AND maasserver_vlan.dhcp_on)
+    LOOP
+      PERFORM sys_dhcp_alert(vlan);
+    END LOOP;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_node_update() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_snippet_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_snippet_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF OLD.enabled THEN
+    PERFORM sys_dhcp_snippet_update_value(OLD);
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_snippet_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_snippet_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_snippet_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF NEW.enabled THEN
+    PERFORM sys_dhcp_snippet_update_value(NEW);
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_snippet_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_snippet_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_snippet_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF OLD.enabled = NEW.enabled AND NEW.enabled IS FALSE THEN
+    -- If the DHCP snippet is disabled don't fire any alerts
+    RETURN NEW;
+  ELSIF ((OLD.value_id != NEW.value_id) OR
+      (OLD.enabled != NEW.enabled) OR
+      (OLD.description != NEW.description)) THEN
+    PERFORM sys_dhcp_snippet_update_value(NEW);
+  ELSIF ((OLD.subnet_id IS NULL AND NEW.subnet_id IS NOT NULL) OR
+      (OLD.subnet_id IS NOT NULL AND NEW.subnet_id IS NULL) OR
+      (OLD.subnet_id != NEW.subnet_id)) THEN
+    IF NEW.subnet_id IS NOT NULL THEN
+      PERFORM sys_dhcp_snippet_update_subnet(NEW.subnet_id);
+    END IF;
+    IF OLD.subnet_id IS NOT NULL THEN
+      PERFORM sys_dhcp_snippet_update_subnet(OLD.subnet_id);
+    END IF;
+  ELSIF ((OLD.node_id IS NULL AND NEW.node_id IS NOT NULL) OR
+      (OLD.node_id IS NOT NULL AND NEW.node_id IS NULL) OR
+      (OLD.node_id != NEW.node_id)) THEN
+    IF NEW.node_id IS NOT NULL THEN
+      PERFORM sys_dhcp_snippet_update_node(NEW.node_id);
+    END IF;
+    IF OLD.node_id IS NOT NULL THEN
+      PERFORM sys_dhcp_snippet_update_node(OLD.node_id);
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_snippet_update() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_snippet_update_node(integer); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_snippet_update_node(_node_id integer) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  rack INTEGER;
+BEGIN
+  FOR rack IN (
+    WITH racks AS (
+      SELECT primary_rack_id, secondary_rack_id
+      FROM maasserver_vlan, maasserver_interface
+      WHERE maasserver_interface.node_id = _node_id
+        AND maasserver_interface.vlan_id = maasserver_vlan.id
+        AND maasserver_vlan.dhcp_on = true
+    )
+    SELECT primary_rack_id FROM racks
+    WHERE primary_rack_id IS NOT NULL
+    UNION
+    SELECT secondary_rack_id FROM racks
+    WHERE secondary_rack_id IS NOT NULL)
+  LOOP
+    PERFORM pg_notify(CONCAT('sys_dhcp_', rack), '');
+  END LOOP;
+  RETURN;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_snippet_update_node(_node_id integer) OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_snippet_update_subnet(integer); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_snippet_update_subnet(_subnet_id integer) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  FOR vlan IN (
+    SELECT
+      maasserver_vlan.*
+    FROM
+      maasserver_vlan,
+      maasserver_subnet
+    WHERE maasserver_subnet.id = _subnet_id
+      AND maasserver_vlan.id = maasserver_subnet.vlan_id
+      AND maasserver_vlan.dhcp_on = true)
+    LOOP
+      PERFORM sys_dhcp_alert(vlan);
+    END LOOP;
+  RETURN;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_snippet_update_subnet(_subnet_id integer) OWNER TO gavin;
+
+--
+-- Name: maasserver_dhcpsnippet; Type: TABLE; Schema: public; Owner: gavin
+--
+
+CREATE TABLE maasserver_dhcpsnippet (
+    id integer NOT NULL,
+    created timestamp with time zone NOT NULL,
+    updated timestamp with time zone NOT NULL,
+    name character varying(255) NOT NULL,
+    description text NOT NULL,
+    enabled boolean NOT NULL,
+    node_id integer,
+    subnet_id integer,
+    value_id integer NOT NULL
+);
+
+
+ALTER TABLE maasserver_dhcpsnippet OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_snippet_update_value(maasserver_dhcpsnippet); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_snippet_update_value(_dhcp_snippet maasserver_dhcpsnippet) RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  IF _dhcp_snippet.subnet_id IS NOT NULL THEN
+    PERFORM sys_dhcp_snippet_update_subnet(_dhcp_snippet.subnet_id);
+  ELSIF _dhcp_snippet.node_id is NOT NULL THEN
+    PERFORM sys_dhcp_snippet_update_node(_dhcp_snippet.node_id);
+  ELSE
+    -- This is a global snippet, everyone has to update. This should only
+    -- be triggered when neither subnet_id or node_id are set. We verify
+    -- that only subnet_id xor node_id are set in DHCPSnippet.clean()
+    PERFORM sys_dhcp_update_all_vlans();
+  END IF;
+  RETURN;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_snippet_update_value(_dhcp_snippet maasserver_dhcpsnippet) OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_staticipaddress_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_staticipaddress_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if DHCP is enabled and has an IP address.
+  IF host(OLD.ip) != '' THEN
+    SELECT maasserver_vlan.* INTO vlan
+    FROM maasserver_vlan, maasserver_subnet
+    WHERE maasserver_subnet.id = OLD.subnet_id AND
+      maasserver_subnet.vlan_id = maasserver_vlan.id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_staticipaddress_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_staticipaddress_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_staticipaddress_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if DHCP is enabled, IP is set and not DISCOVERED.
+  IF NEW.alloc_type != 6 AND NEW.ip IS NOT NULL AND host(NEW.ip) != '' THEN
+    SELECT maasserver_vlan.* INTO vlan
+    FROM maasserver_vlan, maasserver_subnet
+    WHERE maasserver_subnet.id = NEW.subnet_id AND
+      maasserver_subnet.vlan_id = maasserver_vlan.id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_staticipaddress_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_staticipaddress_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_staticipaddress_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  old_vlan maasserver_vlan;
+  new_vlan maasserver_vlan;
+BEGIN
+  -- Ignore DISCOVERED IP addresses.
+  IF NEW.alloc_type != 6 THEN
+    IF OLD.subnet_id != NEW.subnet_id THEN
+      -- Subnet has changed; update each VLAN if different.
+      SELECT maasserver_vlan.* INTO old_vlan
+      FROM maasserver_vlan, maasserver_subnet
+      WHERE maasserver_subnet.id = OLD.subnet_id AND
+        maasserver_subnet.vlan_id = maasserver_vlan.id;
+      SELECT maasserver_vlan.* INTO new_vlan
+      FROM maasserver_vlan, maasserver_subnet
+      WHERE maasserver_subnet.id = NEW.subnet_id AND
+        maasserver_subnet.vlan_id = maasserver_vlan.id;
+      IF old_vlan.id != new_vlan.id THEN
+        -- Different VLAN's; update each if DHCP enabled.
+        IF old_vlan.dhcp_on THEN
+          PERFORM sys_dhcp_alert(old_vlan);
+        END IF;
+        IF new_vlan.dhcp_on THEN
+          PERFORM sys_dhcp_alert(new_vlan);
+        END IF;
+      ELSE
+        -- Same VLAN so only need to update once.
+        IF new_vlan.dhcp_on THEN
+          PERFORM sys_dhcp_alert(new_vlan);
+        END IF;
+      END IF;
+    ELSIF (OLD.ip IS NULL AND NEW.ip IS NOT NULL) OR
+      (OLD.ip IS NOT NULL and NEW.ip IS NULL) OR
+      (host(OLD.ip) != host(NEW.ip)) THEN
+      -- Assigned IP address has changed.
+      SELECT maasserver_vlan.* INTO new_vlan
+      FROM maasserver_vlan, maasserver_subnet
+      WHERE maasserver_subnet.id = NEW.subnet_id AND
+        maasserver_subnet.vlan_id = maasserver_vlan.id;
+      IF new_vlan.dhcp_on THEN
+        PERFORM sys_dhcp_alert(new_vlan);
+      END IF;
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_staticipaddress_update() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_subnet_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_subnet_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Update VLAN if DHCP is enabled.
+  SELECT * INTO vlan
+  FROM maasserver_vlan WHERE id = OLD.vlan_id;
+  IF vlan.dhcp_on THEN
+    PERFORM sys_dhcp_alert(vlan);
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_subnet_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_subnet_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_subnet_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  vlan maasserver_vlan;
+BEGIN
+  -- Subnet was moved to a new VLAN.
+  IF OLD.vlan_id != NEW.vlan_id THEN
+    -- Update old VLAN if DHCP is enabled.
+    SELECT * INTO vlan
+    FROM maasserver_vlan WHERE id = OLD.vlan_id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+    -- Update the new VLAN if DHCP is enabled.
+    SELECT * INTO vlan
+    FROM maasserver_vlan WHERE id = NEW.vlan_id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+  -- Related fields of subnet where changed.
+  ELSIF OLD.cidr != NEW.cidr OR
+    (OLD.gateway_ip IS NULL AND NEW.gateway_ip IS NOT NULL) OR
+    (OLD.gateway_ip IS NOT NULL AND NEW.gateway_ip IS NULL) OR
+    host(OLD.gateway_ip) != host(NEW.gateway_ip) OR
+    OLD.dns_servers != NEW.dns_servers THEN
+    -- Network has changed update alert DHCP if enabled.
+    SELECT * INTO vlan
+    FROM maasserver_vlan WHERE id = NEW.vlan_id;
+    IF vlan.dhcp_on THEN
+      PERFORM sys_dhcp_alert(vlan);
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_subnet_update() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_update_all_vlans(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_update_all_vlans() RETURNS void
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  rack INTEGER;
+BEGIN
+  FOR rack IN (
+    WITH racks AS (
+      SELECT primary_rack_id, secondary_rack_id FROM maasserver_vlan
+      WHERE maasserver_vlan.dhcp_on = true
+    )
+    SELECT primary_rack_id FROM racks
+    WHERE primary_rack_id IS NOT NULL
+    UNION
+    SELECT secondary_rack_id FROM racks
+    WHERE secondary_rack_id IS NOT NULL)
+  LOOP
+    PERFORM pg_notify(CONCAT('sys_dhcp_', rack), '');
+  END LOOP;
+  RETURN;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_update_all_vlans() OWNER TO gavin;
+
+--
+-- Name: sys_dhcp_vlan_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dhcp_vlan_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  relay_vlan maasserver_vlan;
+BEGIN
+  -- DHCP was turned off.
+  IF OLD.dhcp_on AND NOT NEW.dhcp_on THEN
+    PERFORM pg_notify(CONCAT('sys_dhcp_', OLD.primary_rack_id), '');
+    IF OLD.secondary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(CONCAT('sys_dhcp_', OLD.secondary_rack_id), '');
+    END IF;
+  -- DHCP was turned on.
+  ELSIF NOT OLD.dhcp_on AND NEW.dhcp_on THEN
+    PERFORM pg_notify(CONCAT('sys_dhcp_', NEW.primary_rack_id), '');
+    IF NEW.secondary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(CONCAT('sys_dhcp_', NEW.secondary_rack_id), '');
+    END IF;
+  -- DHCP state was not changed but the rack controllers might have been.
+  ELSIF NEW.dhcp_on AND (
+     OLD.primary_rack_id != NEW.primary_rack_id OR (
+       OLD.secondary_rack_id IS NULL AND
+       NEW.secondary_rack_id IS NOT NULL) OR (
+       OLD.secondary_rack_id IS NOT NULL AND
+       NEW.secondary_rack_id IS NULL) OR
+     OLD.secondary_rack_id != NEW.secondary_rack_id) THEN
+    -- Send the message to the old primary if no longer the primary.
+    IF OLD.primary_rack_id != NEW.primary_rack_id THEN
+      PERFORM pg_notify(CONCAT('sys_dhcp_', OLD.primary_rack_id), '');
+    END IF;
+    -- Always send the message to the primary as it has to be set.
+    PERFORM pg_notify(CONCAT('sys_dhcp_', NEW.primary_rack_id), '');
+    -- Send message to both old and new secondary rack controller if set.
+    IF OLD.secondary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(CONCAT('sys_dhcp_', OLD.secondary_rack_id), '');
+    END IF;
+    IF NEW.secondary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(CONCAT('sys_dhcp_', NEW.secondary_rack_id), '');
+    END IF;
+  END IF;
+
+  -- Relay VLAN was set when it was previously unset.
+  IF OLD.relay_vlan_id IS NULL AND NEW.relay_vlan_id IS NOT NULL THEN
+    SELECT maasserver_vlan.* INTO relay_vlan
+    FROM maasserver_vlan
+    WHERE maasserver_vlan.id = NEW.relay_vlan_id;
+    IF relay_vlan.primary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(
+        CONCAT('sys_dhcp_', relay_vlan.primary_rack_id), '');
+      IF relay_vlan.secondary_rack_id IS NOT NULL THEN
+        PERFORM pg_notify(
+          CONCAT('sys_dhcp_', relay_vlan.secondary_rack_id), '');
+      END IF;
+    END IF;
+  -- Relay VLAN was unset when it was previously set.
+  ELSIF OLD.relay_vlan_id IS NOT NULL AND NEW.relay_vlan_id IS NULL THEN
+    SELECT maasserver_vlan.* INTO relay_vlan
+    FROM maasserver_vlan
+    WHERE maasserver_vlan.id = OLD.relay_vlan_id;
+    IF relay_vlan.primary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(
+        CONCAT('sys_dhcp_', relay_vlan.primary_rack_id), '');
+      IF relay_vlan.secondary_rack_id IS NOT NULL THEN
+        PERFORM pg_notify(
+          CONCAT('sys_dhcp_', relay_vlan.secondary_rack_id), '');
+      END IF;
+    END IF;
+  -- Relay VLAN has changed on the VLAN.
+  ELSIF OLD.relay_vlan_id != NEW.relay_vlan_id THEN
+    -- Alert old VLAN if required.
+    SELECT maasserver_vlan.* INTO relay_vlan
+    FROM maasserver_vlan
+    WHERE maasserver_vlan.id = OLD.relay_vlan_id;
+    IF relay_vlan.primary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(
+        CONCAT('sys_dhcp_', relay_vlan.primary_rack_id), '');
+      IF relay_vlan.secondary_rack_id IS NOT NULL THEN
+        PERFORM pg_notify(
+          CONCAT('sys_dhcp_', relay_vlan.secondary_rack_id), '');
+      END IF;
+    END IF;
+    -- Alert new VLAN if required.
+    SELECT maasserver_vlan.* INTO relay_vlan
+    FROM maasserver_vlan
+    WHERE maasserver_vlan.id = NEW.relay_vlan_id;
+    IF relay_vlan.primary_rack_id IS NOT NULL THEN
+      PERFORM pg_notify(
+        CONCAT('sys_dhcp_', relay_vlan.primary_rack_id), '');
+      IF relay_vlan.secondary_rack_id IS NOT NULL THEN
+        PERFORM pg_notify(
+          CONCAT('sys_dhcp_', relay_vlan.secondary_rack_id), '');
+      END IF;
+    END IF;
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dhcp_vlan_update() OWNER TO gavin;
+
+--
+-- Name: sys_dns_config_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_config_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  -- Only care about the
+  IF (NEW.name = 'upstream_dns' OR
+      NEW.name = 'dnssec_validation' OR
+      NEW.name = 'default_dns_ttl' OR
+      NEW.name = 'windows_kms_host')
+  THEN
+    INSERT INTO maasserver_dnspublication
+      (serial, created, source)
+    VALUES
+      (nextval('maasserver_zone_serial_seq'), now(), substring(
+        ('Configuration ' || NEW.name || ' set to ' ||
+         COALESCE(NEW.value, 'NULL'))
+        FOR 255));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_config_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dns_config_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_config_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  -- Only care about the upstream_dns, default_dns_ttl, and
+  -- windows_kms_host.
+  IF (OLD.value != NEW.value AND (
+      NEW.name = 'upstream_dns' OR
+      NEW.name = 'dnssec_validation' OR
+      NEW.name = 'default_dns_ttl' OR
+      NEW.name = 'windows_kms_host'))
+  THEN
+    INSERT INTO maasserver_dnspublication
+      (serial, created, source)
+    VALUES
+      (nextval('maasserver_zone_serial_seq'), now(), substring(
+        ('Configuration ' || NEW.name || ' changed from ' ||
+         OLD.value || ' to ' || NEW.value)
+        FOR 255));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_config_update() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsdata_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsdata_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsdata_delete' FOR 255));
+  RETURN OLD;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsdata_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsdata_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsdata_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsdata_insert' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsdata_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsdata_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsdata_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsdata_update' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsdata_update() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsresource_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsresource_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsresource_delete' FOR 255));
+  RETURN OLD;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsresource_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsresource_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsresource_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsresource_insert' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsresource_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsresource_ip_link(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsresource_ip_link() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsresource_ip_link' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsresource_ip_link() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsresource_ip_unlink(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsresource_ip_unlink() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsresource_ip_unlink' FOR 255));
+  RETURN OLD;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsresource_ip_unlink() OWNER TO gavin;
+
+--
+-- Name: sys_dns_dnsresource_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_dnsresource_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_dnsresource_update' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_dnsresource_update() OWNER TO gavin;
+
+--
+-- Name: sys_dns_domain_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_domain_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_domain_delete' FOR 255));
+  RETURN OLD;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_domain_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dns_domain_insert(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_domain_insert() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_domain_insert' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_domain_insert() OWNER TO gavin;
+
+--
+-- Name: sys_dns_domain_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_domain_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_domain_update' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_domain_update() OWNER TO gavin;
+
+--
+-- Name: sys_dns_interface_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_interface_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  changes text[];
+BEGIN
+  IF OLD.name != NEW.name THEN
+    changes := changes || (
+      'renamed from ' || OLD.name || ' to ' || NEW.name);
+  END IF;
+  IF OLD.node_id IS NULL AND NEW.node_id IS NOT NULL THEN
+    changes := changes || 'node set'::text;
+  ELSIF OLD.node_id IS NOT NULL AND NEW.node_id IS NULL THEN
+    changes := changes || 'node unset'::text;
+  ELSIF OLD.node_id != NEW.node_id THEN
+    changes := changes || 'node changed'::text;
+  END IF;
+  IF array_length(changes, 1) != 0 THEN
+    INSERT INTO maasserver_dnspublication
+      (serial, created, source)
+    VALUES
+      (nextval('maasserver_zone_serial_seq'), now(),
+       substring(
+         ('Interface ' || NEW.name || ': ' ||
+          array_to_string(changes, ', '))
+         FOR 255));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_interface_update() OWNER TO gavin;
+
+--
+-- Name: sys_dns_nic_ip_link(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_nic_ip_link() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_nic_ip_link' FOR 255));
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_nic_ip_link() OWNER TO gavin;
+
+--
+-- Name: sys_dns_nic_ip_unlink(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_nic_ip_unlink() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_nic_ip_unlink' FOR 255));
+  RETURN OLD;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_nic_ip_unlink() OWNER TO gavin;
+
+--
+-- Name: sys_dns_node_delete(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_node_delete() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+BEGIN
+  INSERT INTO maasserver_dnspublication
+    (serial, created, source)
+  VALUES
+    (nextval('maasserver_zone_serial_seq'), now(),
+     substring('Call to sys_dns_node_delete' FOR 255));
+  RETURN OLD;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_node_delete() OWNER TO gavin;
+
+--
+-- Name: sys_dns_node_update(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_node_update() RETURNS trigger
+    LANGUAGE plpgsql
+    AS $$
+DECLARE
+  changes text[];
+BEGIN
+  IF OLD.hostname != NEW.hostname THEN
+    changes := changes || (
+      'hostname changed from ' || OLD.hostname || ' to ' || NEW.hostname);
+  END IF;
+  IF OLD.domain_id != NEW.domain_id THEN
+    changes := changes || 'domain changed'::text;
+  END IF;
+  IF array_length(changes, 1) != 0 THEN
+    INSERT INTO maasserver_dnspublication
+      (serial, created, source)
+    VALUES
+      (nextval('maasserver_zone_serial_seq'), now(),
+       substring(
+         ('Node ' || NEW.system_id || ': ' ||
+          array_to_string(changes, ', '))
+         FOR 255));
+  END IF;
+  RETURN NEW;
+END;
+$$;
+
+
+ALTER FUNCTION public.sys_dns_node_update() OWNER TO gavin;
+
+--
+-- Name: sys_dns_publish(); Type: FUNCTION; Schema: public; Owner: gavin
+--
+
+CREATE FUNCTION sys_dns_publish() RETURNS trigger
+    LANGUAGE plpgsql