0s autopkgtest [03:04:23]: starting date and time: 2025-01-13 03:04:23+0000 0s autopkgtest [03:04:23]: git checkout: 325255d2 Merge branch 'pin-any-arch' into 'ubuntu/production' 0s autopkgtest [03:04:23]: host juju-7f2275-prod-proposed-migration-environment-15; command line: /home/ubuntu/autopkgtest/runner/autopkgtest --output-dir /tmp/autopkgtest-work.z855lmf2/out --timeout-copy=6000 --setup-commands /home/ubuntu/autopkgtest-cloud/worker-config-production/setup-canonical.sh --apt-pocket=proposed=src:postgresql-16,src:postgresql-17 --apt-upgrade slony1-2 --timeout-short=300 --timeout-copy=20000 --timeout-build=20000 '--env=ADT_TEST_TRIGGERS=postgresql-16/16.4-3build2 postgresql-17/17.2-1build2' -- ssh -s /home/ubuntu/autopkgtest/ssh-setup/nova -- --flavor builder-cpu2-ram4-disk20 --security-groups autopkgtest-juju-7f2275-prod-proposed-migration-environment-15@bos03-20.secgroup --name adt-plucky-amd64-slony1-2-20250113-015417-juju-7f2275-prod-proposed-migration-environment-15-cc742134-0d27-478a-92fb-ef0b0b4f3d79 --image adt/ubuntu-plucky-amd64-server --keyname testbed-juju-7f2275-prod-proposed-migration-environment-15 --net-id=net_prod-proposed-migration-amd64 -e TERM=linux -e ''"'"'http_proxy=http://squid.internal:3128'"'"'' -e ''"'"'https_proxy=http://squid.internal:3128'"'"'' -e ''"'"'no_proxy=127.0.0.1,127.0.1.1,login.ubuntu.com,localhost,localdomain,novalocal,internal,archive.ubuntu.com,ports.ubuntu.com,security.ubuntu.com,ddebs.ubuntu.com,changelogs.ubuntu.com,keyserver.ubuntu.com,launchpadlibrarian.net,launchpadcontent.net,launchpad.net,10.24.0.0/24,keystone.ps5.canonical.com,objectstorage.prodstack5.canonical.com,radosgw.ps5.canonical.com'"'"'' --mirror=http://ftpmaster.internal/ubuntu/ 169s autopkgtest [03:07:12]: testbed dpkg architecture: amd64 169s autopkgtest [03:07:12]: testbed apt version: 2.9.18 169s autopkgtest [03:07:12]: @@@@@@@@@@@@@@@@@@@@ test bed setup 170s autopkgtest [03:07:13]: testbed release detected to be: None 170s autopkgtest [03:07:13]: updating testbed package index (apt update) 171s Get:1 http://ftpmaster.internal/ubuntu plucky-proposed InRelease [73.9 kB] 171s Hit:2 http://ftpmaster.internal/ubuntu plucky InRelease 171s Hit:3 http://ftpmaster.internal/ubuntu plucky-updates InRelease 171s Hit:4 http://ftpmaster.internal/ubuntu plucky-security InRelease 171s Get:5 http://ftpmaster.internal/ubuntu plucky-proposed/restricted Sources [9708 B] 171s Get:6 http://ftpmaster.internal/ubuntu plucky-proposed/universe Sources [815 kB] 171s Get:7 http://ftpmaster.internal/ubuntu plucky-proposed/multiverse Sources [13.7 kB] 171s Get:8 http://ftpmaster.internal/ubuntu plucky-proposed/main Sources [128 kB] 171s Get:9 http://ftpmaster.internal/ubuntu plucky-proposed/main amd64 Packages [315 kB] 171s Get:10 http://ftpmaster.internal/ubuntu plucky-proposed/main i386 Packages [178 kB] 171s Get:11 http://ftpmaster.internal/ubuntu plucky-proposed/restricted i386 Packages [2408 B] 171s Get:12 http://ftpmaster.internal/ubuntu plucky-proposed/restricted amd64 Packages [40.1 kB] 171s Get:13 http://ftpmaster.internal/ubuntu plucky-proposed/universe i386 Packages [394 kB] 171s Get:14 http://ftpmaster.internal/ubuntu plucky-proposed/universe amd64 Packages [970 kB] 172s Get:15 http://ftpmaster.internal/ubuntu plucky-proposed/multiverse i386 Packages [3516 B] 172s Get:16 http://ftpmaster.internal/ubuntu plucky-proposed/multiverse amd64 Packages [13.0 kB] 172s Fetched 2958 kB in 1s (3058 kB/s) 172s Reading package lists... 173s Reading package lists... 173s Building dependency tree... 173s Reading state information... 173s Calculating upgrade... 174s The following package was automatically installed and is no longer required: 174s libicu74 174s Use 'sudo apt autoremove' to remove it. 174s The following packages will be upgraded: 174s bash-completion libxml2 174s 2 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. 174s Need to get 985 kB of archives. 174s After this operation, 4096 B disk space will be freed. 174s Get:1 http://ftpmaster.internal/ubuntu plucky/main amd64 libxml2 amd64 2.12.7+dfsg+really2.9.14-0.2ubuntu2 [771 kB] 174s Get:2 http://ftpmaster.internal/ubuntu plucky/main amd64 bash-completion all 1:2.16.0-3 [214 kB] 174s Fetched 985 kB in 1s (1719 kB/s) 175s (Reading database ... (Reading database ... 5% (Reading database ... 10% (Reading database ... 15% (Reading database ... 20% (Reading database ... 25% (Reading database ... 30% (Reading database ... 35% (Reading database ... 40% (Reading database ... 45% (Reading database ... 50% (Reading database ... 55% (Reading database ... 60% (Reading database ... 65% (Reading database ... 70% (Reading database ... 75% (Reading database ... 80% (Reading database ... 85% (Reading database ... 90% (Reading database ... 95% (Reading database ... 100% (Reading database ... 75824 files and directories currently installed.) 175s Preparing to unpack .../libxml2_2.12.7+dfsg+really2.9.14-0.2ubuntu2_amd64.deb ... 175s Unpacking libxml2:amd64 (2.12.7+dfsg+really2.9.14-0.2ubuntu2) over (2.12.7+dfsg+really2.9.14-0.2build1) ... 175s Preparing to unpack .../bash-completion_1%3a2.16.0-3_all.deb ... 175s Unpacking bash-completion (1:2.16.0-3) over (1:2.16.0-1) ... 175s Setting up bash-completion (1:2.16.0-3) ... 175s Setting up libxml2:amd64 (2.12.7+dfsg+really2.9.14-0.2ubuntu2) ... 175s Processing triggers for man-db (2.13.0-1) ... 175s Processing triggers for libc-bin (2.40-4ubuntu1) ... 176s Reading package lists... 176s Building dependency tree... 176s Reading state information... 176s The following packages will be REMOVED: 176s libicu74* 176s 0 upgraded, 0 newly installed, 1 to remove and 0 not upgraded. 176s After this operation, 36.8 MB disk space will be freed. 176s (Reading database ... (Reading database ... 5% (Reading database ... 10% (Reading database ... 15% (Reading database ... 20% (Reading database ... 25% (Reading database ... 30% (Reading database ... 35% (Reading database ... 40% (Reading database ... 45% (Reading database ... 50% (Reading database ... 55% (Reading database ... 60% (Reading database ... 65% (Reading database ... 70% (Reading database ... 75% (Reading database ... 80% (Reading database ... 85% (Reading database ... 90% (Reading database ... 95% (Reading database ... 100% (Reading database ... 75824 files and directories currently installed.) 176s Removing libicu74:amd64 (74.2-1ubuntu4) ... 176s Processing triggers for libc-bin (2.40-4ubuntu1) ... 177s autopkgtest [03:07:20]: upgrading testbed (apt dist-upgrade and autopurge) 177s Reading package lists... 177s Building dependency tree... 177s Reading state information... 177s Calculating upgrade...Starting pkgProblemResolver with broken count: 0 177s Starting 2 pkgProblemResolver with broken count: 0 177s Done 178s Entering ResolveByKeep 178s 178s 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. 178s Reading package lists... 179s Building dependency tree... 179s Reading state information... 179s Starting pkgProblemResolver with broken count: 0 179s Starting 2 pkgProblemResolver with broken count: 0 179s Done 179s 0 upgraded, 0 newly installed, 0 to remove and 0 not upgraded. 182s autopkgtest [03:07:25]: testbed running kernel: Linux 6.11.0-8-generic #8-Ubuntu SMP PREEMPT_DYNAMIC Mon Sep 16 13:41:20 UTC 2024 182s autopkgtest [03:07:25]: @@@@@@@@@@@@@@@@@@@@ apt-source slony1-2 185s Get:1 http://ftpmaster.internal/ubuntu plucky/universe slony1-2 2.2.11-4 (dsc) [2413 B] 185s Get:2 http://ftpmaster.internal/ubuntu plucky/universe slony1-2 2.2.11-4 (tar) [1465 kB] 185s Get:3 http://ftpmaster.internal/ubuntu plucky/universe slony1-2 2.2.11-4 (diff) [17.1 kB] 185s gpgv: Signature made Sun Jul 28 15:46:54 2024 UTC 185s gpgv: using RSA key 5C48FE6157F49179597087C64C5A6BAB12D2A7AE 185s gpgv: Can't check signature: No public key 185s dpkg-source: warning: cannot verify inline signature for ./slony1-2_2.2.11-4.dsc: no acceptable signature found 185s autopkgtest [03:07:28]: testing package slony1-2 version 2.2.11-4 186s autopkgtest [03:07:29]: build not needed 189s autopkgtest [03:07:32]: test load-functions: preparing testbed 189s Reading package lists... 189s Building dependency tree... 189s Reading state information... 189s Starting pkgProblemResolver with broken count: 0 189s Starting 2 pkgProblemResolver with broken count: 0 189s Done 190s The following NEW packages will be installed: 190s libio-pty-perl libipc-run-perl libjson-perl libpq5 libxslt1.1 postgresql-16 190s postgresql-16-slony1-2 postgresql-client-16 postgresql-client-common 190s postgresql-common slony1-2-bin slony1-2-doc ssl-cert 190s 0 upgraded, 13 newly installed, 0 to remove and 0 not upgraded. 190s Need to get 18.7 MB of archives. 190s After this operation, 56.7 MB of additional disk space will be used. 190s Get:1 http://ftpmaster.internal/ubuntu plucky/main amd64 libjson-perl all 4.10000-1 [81.9 kB] 190s Get:2 http://ftpmaster.internal/ubuntu plucky/main amd64 libio-pty-perl amd64 1:1.20-1build3 [31.4 kB] 190s Get:3 http://ftpmaster.internal/ubuntu plucky/main amd64 libipc-run-perl all 20231003.0-2 [91.5 kB] 190s Get:4 http://ftpmaster.internal/ubuntu plucky/main amd64 postgresql-client-common all 267 [37.1 kB] 190s Get:5 http://ftpmaster.internal/ubuntu plucky/main amd64 ssl-cert all 1.1.3ubuntu1 [18.7 kB] 190s Get:6 http://ftpmaster.internal/ubuntu plucky/main amd64 postgresql-common all 267 [162 kB] 190s Get:7 http://ftpmaster.internal/ubuntu plucky-proposed/main amd64 libpq5 amd64 17.2-1build2 [153 kB] 190s Get:8 http://ftpmaster.internal/ubuntu plucky/main amd64 libxslt1.1 amd64 1.1.39-0exp1ubuntu2 [175 kB] 190s Get:9 http://ftpmaster.internal/ubuntu plucky-proposed/main amd64 postgresql-client-16 amd64 16.4-3build2 [1347 kB] 190s Get:10 http://ftpmaster.internal/ubuntu plucky-proposed/main amd64 postgresql-16 amd64 16.4-3build2 [16.1 MB] 193s Get:11 http://ftpmaster.internal/ubuntu plucky/universe amd64 postgresql-16-slony1-2 amd64 2.2.11-4 [20.8 kB] 193s Get:12 http://ftpmaster.internal/ubuntu plucky/universe amd64 slony1-2-bin amd64 2.2.11-4 [226 kB] 193s Get:13 http://ftpmaster.internal/ubuntu plucky/universe amd64 slony1-2-doc all 2.2.11-4 [328 kB] 193s Preconfiguring packages ... 193s Fetched 18.7 MB in 3s (6448 kB/s) 193s Selecting previously unselected package libjson-perl. 193s (Reading database ... (Reading database ... 5% (Reading database ... 10% (Reading database ... 15% (Reading database ... 20% (Reading database ... 25% (Reading database ... 30% (Reading database ... 35% (Reading database ... 40% (Reading database ... 45% (Reading database ... 50% (Reading database ... 55% (Reading database ... 60% (Reading database ... 65% (Reading database ... 70% (Reading database ... 75% (Reading database ... 80% (Reading database ... 85% (Reading database ... 90% (Reading database ... 95% (Reading database ... 100% (Reading database ... 75808 files and directories currently installed.) 193s Preparing to unpack .../00-libjson-perl_4.10000-1_all.deb ... 193s Unpacking libjson-perl (4.10000-1) ... 193s Selecting previously unselected package libio-pty-perl. 193s Preparing to unpack .../01-libio-pty-perl_1%3a1.20-1build3_amd64.deb ... 193s Unpacking libio-pty-perl (1:1.20-1build3) ... 193s Selecting previously unselected package libipc-run-perl. 193s Preparing to unpack .../02-libipc-run-perl_20231003.0-2_all.deb ... 193s Unpacking libipc-run-perl (20231003.0-2) ... 193s Selecting previously unselected package postgresql-client-common. 193s Preparing to unpack .../03-postgresql-client-common_267_all.deb ... 193s Unpacking postgresql-client-common (267) ... 193s Selecting previously unselected package ssl-cert. 193s Preparing to unpack .../04-ssl-cert_1.1.3ubuntu1_all.deb ... 193s Unpacking ssl-cert (1.1.3ubuntu1) ... 193s Selecting previously unselected package postgresql-common. 193s Preparing to unpack .../05-postgresql-common_267_all.deb ... 193s Adding 'diversion of /usr/bin/pg_config to /usr/bin/pg_config.libpq-dev by postgresql-common' 193s Unpacking postgresql-common (267) ... 193s Selecting previously unselected package libpq5:amd64. 193s Preparing to unpack .../06-libpq5_17.2-1build2_amd64.deb ... 193s Unpacking libpq5:amd64 (17.2-1build2) ... 193s Selecting previously unselected package libxslt1.1:amd64. 193s Preparing to unpack .../07-libxslt1.1_1.1.39-0exp1ubuntu2_amd64.deb ... 193s Unpacking libxslt1.1:amd64 (1.1.39-0exp1ubuntu2) ... 193s Selecting previously unselected package postgresql-client-16. 193s Preparing to unpack .../08-postgresql-client-16_16.4-3build2_amd64.deb ... 193s Unpacking postgresql-client-16 (16.4-3build2) ... 193s Selecting previously unselected package postgresql-16. 193s Preparing to unpack .../09-postgresql-16_16.4-3build2_amd64.deb ... 193s Unpacking postgresql-16 (16.4-3build2) ... 194s Selecting previously unselected package postgresql-16-slony1-2. 194s Preparing to unpack .../10-postgresql-16-slony1-2_2.2.11-4_amd64.deb ... 194s Unpacking postgresql-16-slony1-2 (2.2.11-4) ... 194s Selecting previously unselected package slony1-2-bin. 194s Preparing to unpack .../11-slony1-2-bin_2.2.11-4_amd64.deb ... 194s Unpacking slony1-2-bin (2.2.11-4) ... 194s Selecting previously unselected package slony1-2-doc. 194s Preparing to unpack .../12-slony1-2-doc_2.2.11-4_all.deb ... 194s Unpacking slony1-2-doc (2.2.11-4) ... 194s Setting up libio-pty-perl (1:1.20-1build3) ... 194s Setting up libpq5:amd64 (17.2-1build2) ... 194s Setting up ssl-cert (1.1.3ubuntu1) ... 194s Created symlink '/etc/systemd/system/multi-user.target.wants/ssl-cert.service' → '/usr/lib/systemd/system/ssl-cert.service'. 194s Setting up libipc-run-perl (20231003.0-2) ... 194s Setting up libjson-perl (4.10000-1) ... 194s Setting up libxslt1.1:amd64 (1.1.39-0exp1ubuntu2) ... 194s Setting up slony1-2-doc (2.2.11-4) ... 194s Setting up postgresql-client-common (267) ... 194s Setting up postgresql-client-16 (16.4-3build2) ... 195s update-alternatives: using /usr/share/postgresql/16/man/man1/psql.1.gz to provide /usr/share/man/man1/psql.1.gz (psql.1.gz) in auto mode 195s Setting up postgresql-common (267) ... 195s Configuring postgresql-common 195s ----------------------------- 195s 195s Obsolete major version 16 195s 195s The PostgreSQL version 16 is obsolete, but the server or client packages are 195s still installed. Please install the latest packages (postgresql-17 and 195s postgresql-client-17) and upgrade the existing clusters with 195s pg_upgradecluster (see manpage). 195s 195s Please be aware that the installation of postgresql-17 will automatically 195s create a default cluster 17/main. If you want to upgrade the 16/main 195s cluster, you need to remove the already existing 17 cluster (pg_dropcluster 195s --stop 17 main, see manpage for details). 195s 195s The old server and client packages are no longer supported. After the 195s existing clusters are upgraded, the postgresql-16 and postgresql-client-16 195s packages should be removed. 195s 195s Please see /usr/share/doc/postgresql-common/README.Debian.gz for details. 195s 195s Creating config file /etc/postgresql-common/createcluster.conf with new version 195s Building PostgreSQL dictionaries from installed myspell/hunspell packages... 195s Removing obsolete dictionary files: 196s Created symlink '/etc/systemd/system/multi-user.target.wants/postgresql.service' → '/usr/lib/systemd/system/postgresql.service'. 196s Setting up slony1-2-bin (2.2.11-4) ... 197s Setting up postgresql-16 (16.4-3build2) ... 197s Creating new PostgreSQL cluster 16/main ... 197s /usr/lib/postgresql/16/bin/initdb -D /var/lib/postgresql/16/main --auth-local peer --auth-host scram-sha-256 --no-instructions 197s The files belonging to this database system will be owned by user "postgres". 197s This user must also own the server process. 197s 197s The database cluster will be initialized with locale "C.UTF-8". 197s The default database encoding has accordingly been set to "UTF8". 197s The default text search configuration will be set to "english". 197s 197s Data page checksums are disabled. 197s 197s fixing permissions on existing directory /var/lib/postgresql/16/main ... ok 197s creating subdirectories ... ok 197s selecting dynamic shared memory implementation ... posix 197s selecting default max_connections ... 100 197s selecting default shared_buffers ... 128MB 197s selecting default time zone ... Etc/UTC 197s creating configuration files ... ok 197s running bootstrap script ... ok 198s performing post-bootstrap initialization ... ok 198s syncing data to disk ... ok 201s Setting up postgresql-16-slony1-2 (2.2.11-4) ... 201s Processing triggers for libc-bin (2.40-4ubuntu1) ... 201s Processing triggers for man-db (2.13.0-1) ... 203s autopkgtest [03:07:46]: test load-functions: [----------------------- 204s ### PostgreSQL 16 psql ### 204s Creating new PostgreSQL cluster 16/regress ... 206s create table public.sl_node ( 206s no_id int4, 206s no_active bool, 206s no_comment text, 206s no_failed bool, 206s CONSTRAINT "sl_node-pkey" 206s PRIMARY KEY (no_id) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_node is 'Holds the list of nodes associated with this namespace.'; 206s COMMENT 206s comment on column public.sl_node.no_id is 'The unique ID number for the node'; 206s COMMENT 206s comment on column public.sl_node.no_active is 'Is the node active in replication yet?'; 206s COMMENT 206s comment on column public.sl_node.no_comment is 'A human-oriented description of the node'; 206s COMMENT 206s create table public.sl_nodelock ( 206s nl_nodeid int4, 206s nl_conncnt serial, 206s nl_backendpid int4, 206s CONSTRAINT "sl_nodelock-pkey" 206s PRIMARY KEY (nl_nodeid, nl_conncnt) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_nodelock is 'Used to prevent multiple slon instances and to identify the backends to kill in terminateNodeConnections().'; 206s COMMENT 206s comment on column public.sl_nodelock.nl_nodeid is 'Clients node_id'; 206s COMMENT 206s comment on column public.sl_nodelock.nl_conncnt is 'Clients connection number'; 206s COMMENT 206s comment on column public.sl_nodelock.nl_backendpid is 'PID of database backend owning this lock'; 206s COMMENT 206s create table public.sl_set ( 206s set_id int4, 206s set_origin int4, 206s set_locked bigint, 206s set_comment text, 206s CONSTRAINT "sl_set-pkey" 206s PRIMARY KEY (set_id), 206s CONSTRAINT "set_origin-no_id-ref" 206s FOREIGN KEY (set_origin) 206s REFERENCES public.sl_node (no_id) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_set is 'Holds definitions of replication sets.'; 206s COMMENT 206s comment on column public.sl_set.set_id is 'A unique ID number for the set.'; 206s COMMENT 206s comment on column public.sl_set.set_origin is 206s 'The ID number of the source node for the replication set.'; 206s COMMENT 206s comment on column public.sl_set.set_locked is 'Transaction ID where the set was locked.'; 206s COMMENT 206s comment on column public.sl_set.set_comment is 'A human-oriented description of the set.'; 206s COMMENT 206s create table public.sl_setsync ( 206s ssy_setid int4, 206s ssy_origin int4, 206s ssy_seqno int8, 206s ssy_snapshot "pg_catalog".txid_snapshot, 206s ssy_action_list text, 206s CONSTRAINT "sl_setsync-pkey" 206s PRIMARY KEY (ssy_setid), 206s CONSTRAINT "ssy_setid-set_id-ref" 206s FOREIGN KEY (ssy_setid) 206s REFERENCES public.sl_set (set_id), 206s CONSTRAINT "ssy_origin-no_id-ref" 206s FOREIGN KEY (ssy_origin) 206s REFERENCES public.sl_node (no_id) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_setsync is 'SYNC information'; 206s COMMENT 206s comment on column public.sl_setsync.ssy_setid is 'ID number of the replication set'; 206s COMMENT 206s comment on column public.sl_setsync.ssy_origin is 'ID number of the node'; 206s COMMENT 206s comment on column public.sl_setsync.ssy_seqno is 'Slony-I sequence number'; 206s COMMENT 206s comment on column public.sl_setsync.ssy_snapshot is 'TXID in provider system seen by the event'; 206s COMMENT 206s comment on column public.sl_setsync.ssy_action_list is 'action list used during the subscription process. At the time a subscriber copies over data from the origin, it sees all tables in a state somewhere between two SYNC events. Therefore this list must contains all log_actionseqs that are visible at that time, whose operations have therefore already been included in the data copied at the time the initial data copy is done. Those actions may therefore be filtered out of the first SYNC done after subscribing.'; 206s COMMENT 206s create table public.sl_table ( 206s tab_id int4, 206s tab_reloid oid UNIQUE NOT NULL, 206s tab_relname name NOT NULL, 206s tab_nspname name NOT NULL, 206s tab_set int4, 206s tab_idxname name NOT NULL, 206s tab_altered boolean NOT NULL, 206s tab_comment text, 206s CONSTRAINT "sl_table-pkey" 206s PRIMARY KEY (tab_id), 206s CONSTRAINT "tab_set-set_id-ref" 206s FOREIGN KEY (tab_set) 206s REFERENCES public.sl_set (set_id) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_table is 'Holds information about the tables being replicated.'; 206s COMMENT 206s comment on column public.sl_table.tab_id is 'Unique key for Slony-I to use to identify the table'; 206s COMMENT 206s comment on column public.sl_table.tab_reloid is 'The OID of the table in pg_catalog.pg_class.oid'; 206s COMMENT 206s comment on column public.sl_table.tab_relname is 'The name of the table in pg_catalog.pg_class.relname used to recover from a dump/restore cycle'; 206s COMMENT 206s comment on column public.sl_table.tab_nspname is 'The name of the schema in pg_catalog.pg_namespace.nspname used to recover from a dump/restore cycle'; 206s COMMENT 206s comment on column public.sl_table.tab_set is 'ID of the replication set the table is in'; 206s COMMENT 206s comment on column public.sl_table.tab_idxname is 'The name of the primary index of the table'; 206s COMMENT 206s comment on column public.sl_table.tab_altered is 'Has the table been modified for replication?'; 206s COMMENT 206s comment on column public.sl_table.tab_comment is 'Human-oriented description of the table'; 206s COMMENT 206s create table public.sl_sequence ( 206s seq_id int4, 206s seq_reloid oid UNIQUE NOT NULL, 206s seq_relname name NOT NULL, 206s seq_nspname name NOT NULL, 206s seq_set int4, 206s seq_comment text, 206s CONSTRAINT "sl_sequence-pkey" 206s PRIMARY KEY (seq_id), 206s CONSTRAINT "seq_set-set_id-ref" 206s FOREIGN KEY (seq_set) 206s REFERENCES public.sl_set (set_id) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_sequence is 'Similar to sl_table, each entry identifies a sequence being replicated.'; 206s COMMENT 206s comment on column public.sl_sequence.seq_id is 'An internally-used ID for Slony-I to use in its sequencing of updates'; 206s COMMENT 206s comment on column public.sl_sequence.seq_reloid is 'The OID of the sequence object'; 206s COMMENT 206s comment on column public.sl_sequence.seq_relname is 'The name of the sequence in pg_catalog.pg_class.relname used to recover from a dump/restore cycle'; 206s COMMENT 206s comment on column public.sl_sequence.seq_nspname is 'The name of the schema in pg_catalog.pg_namespace.nspname used to recover from a dump/restore cycle'; 206s COMMENT 206s comment on column public.sl_sequence.seq_set is 'Indicates which replication set the object is in'; 206s COMMENT 206s comment on column public.sl_sequence.seq_comment is 'A human-oriented comment'; 206s COMMENT 206s create table public.sl_path ( 206s pa_server int4, 206s pa_client int4, 206s pa_conninfo text NOT NULL, 206s pa_connretry int4, 206s CONSTRAINT "sl_path-pkey" 206s PRIMARY KEY (pa_server, pa_client), 206s CONSTRAINT "pa_server-no_id-ref" 206s FOREIGN KEY (pa_server) 206s REFERENCES public.sl_node (no_id), 206s CONSTRAINT "pa_client-no_id-ref" 206s FOREIGN KEY (pa_client) 206s REFERENCES public.sl_node (no_id) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_path is 'Holds connection information for the paths between nodes, and the synchronisation delay'; 206s COMMENT 206s comment on column public.sl_path.pa_server is 'The Node ID # (from sl_node.no_id) of the data source'; 206s COMMENT 206s comment on column public.sl_path.pa_client is 'The Node ID # (from sl_node.no_id) of the data target'; 206s COMMENT 206s comment on column public.sl_path.pa_conninfo is 'The PostgreSQL connection string used to connect to the source node.'; 206s COMMENT 206s comment on column public.sl_path.pa_connretry is 'The synchronisation delay, in seconds'; 206s COMMENT 206s create table public.sl_listen ( 206s li_origin int4, 206s li_provider int4, 206s li_receiver int4, 206s CONSTRAINT "sl_listen-pkey" 206s PRIMARY KEY (li_origin, li_provider, li_receiver), 206s CONSTRAINT "li_origin-no_id-ref" 206s FOREIGN KEY (li_origin) 206s REFERENCES public.sl_node (no_id), 206s CONSTRAINT "sl_listen-sl_path-ref" 206s FOREIGN KEY (li_provider, li_receiver) 206s REFERENCES public.sl_path (pa_server, pa_client) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_listen is 'Indicates how nodes listen to events from other nodes in the Slony-I network.'; 206s COMMENT 206s comment on column public.sl_listen.li_origin is 'The ID # (from sl_node.no_id) of the node this listener is operating on'; 206s COMMENT 206s comment on column public.sl_listen.li_provider is 'The ID # (from sl_node.no_id) of the source node for this listening event'; 206s COMMENT 206s comment on column public.sl_listen.li_receiver is 'The ID # (from sl_node.no_id) of the target node for this listening event'; 206s COMMENT 206s create table public.sl_subscribe ( 206s sub_set int4, 206s sub_provider int4, 206s sub_receiver int4, 206s sub_forward bool, 206s sub_active bool, 206s CONSTRAINT "sl_subscribe-pkey" 206s PRIMARY KEY (sub_receiver, sub_set), 206s CONSTRAINT "sl_subscribe-sl_path-ref" 206s FOREIGN KEY (sub_provider, sub_receiver) 206s REFERENCES public.sl_path (pa_server, pa_client), 206s CONSTRAINT "sub_set-set_id-ref" 206s FOREIGN KEY (sub_set) 206s REFERENCES public.sl_set (set_id) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_subscribe is 'Holds a list of subscriptions on sets'; 206s COMMENT 206s comment on column public.sl_subscribe.sub_set is 'ID # (from sl_set) of the set being subscribed to'; 206s COMMENT 206s comment on column public.sl_subscribe.sub_provider is 'ID# (from sl_node) of the node providing data'; 206s COMMENT 206s comment on column public.sl_subscribe.sub_receiver is 'ID# (from sl_node) of the node receiving data from the provider'; 206s COMMENT 206s comment on column public.sl_subscribe.sub_forward is 'Does this provider keep data in sl_log_1/sl_log_2 to allow it to be a provider for other nodes?'; 206s COMMENT 206s comment on column public.sl_subscribe.sub_active is 'Has this subscription been activated? This is not set on the subscriber until AFTER the subscriber has received COPY data from the provider'; 206s COMMENT 206s create table public.sl_event ( 206s ev_origin int4, 206s ev_seqno int8, 206s ev_timestamp timestamptz, 206s ev_snapshot "pg_catalog".txid_snapshot, 206s ev_type text, 206s ev_data1 text, 206s ev_data2 text, 206s ev_data3 text, 206s ev_data4 text, 206s ev_data5 text, 206s ev_data6 text, 206s ev_data7 text, 206s ev_data8 text, 206s CONSTRAINT "sl_event-pkey" 206s PRIMARY KEY (ev_origin, ev_seqno) 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_event is 'Holds information about replication events. After a period of time, Slony removes old confirmed events from both this table and the sl_confirm table.'; 206s COMMENT 206s comment on column public.sl_event.ev_origin is 'The ID # (from sl_node.no_id) of the source node for this event'; 206s COMMENT 206s comment on column public.sl_event.ev_seqno is 'The ID # for the event'; 206s COMMENT 206s comment on column public.sl_event.ev_timestamp is 'When this event record was created'; 206s COMMENT 206s comment on column public.sl_event.ev_snapshot is 'TXID snapshot on provider node for this event'; 206s COMMENT 206s comment on column public.sl_event.ev_seqno is 'The ID # for the event'; 206s COMMENT 206s comment on column public.sl_event.ev_type is 'The type of event this record is for. 206s SYNC = Synchronise 206s STORE_NODE = 206s ENABLE_NODE = 206s DROP_NODE = 206s STORE_PATH = 206s DROP_PATH = 206s STORE_LISTEN = 206s DROP_LISTEN = 206s STORE_SET = 206s DROP_SET = 206s MERGE_SET = 206s SET_ADD_TABLE = 206s SET_ADD_SEQUENCE = 206s STORE_TRIGGER = 206s DROP_TRIGGER = 206s MOVE_SET = 206s ACCEPT_SET = 206s SET_DROP_TABLE = 206s SET_DROP_SEQUENCE = 206s SET_MOVE_TABLE = 206s SET_MOVE_SEQUENCE = 206s FAILOVER_SET = 206s SUBSCRIBE_SET = 206s ENABLE_SUBSCRIPTION = 206s UNSUBSCRIBE_SET = 206s DDL_SCRIPT = 206s ADJUST_SEQ = 206s RESET_CONFIG = 206s '; 206s COMMENT 206s comment on column public.sl_event.ev_data1 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s comment on column public.sl_event.ev_data2 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s comment on column public.sl_event.ev_data3 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s comment on column public.sl_event.ev_data4 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s comment on column public.sl_event.ev_data5 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s comment on column public.sl_event.ev_data6 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s comment on column public.sl_event.ev_data7 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s comment on column public.sl_event.ev_data8 is 'Data field containing an argument needed to process the event'; 206s COMMENT 206s create table public.sl_confirm ( 206s con_origin int4, 206s con_received int4, 206s con_seqno int8, 206s con_timestamp timestamptz DEFAULT timeofday()::timestamptz 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_confirm is 'Holds confirmation of replication events. After a period of time, Slony removes old confirmed events from both this table and the sl_event table.'; 206s COMMENT 206s comment on column public.sl_confirm.con_origin is 'The ID # (from sl_node.no_id) of the source node for this event'; 206s COMMENT 206s comment on column public.sl_confirm.con_seqno is 'The ID # for the event'; 206s COMMENT 206s comment on column public.sl_confirm.con_timestamp is 'When this event was confirmed'; 206s COMMENT 206s create index sl_confirm_idx1 on public.sl_confirm 206s (con_origin, con_received, con_seqno); 206s CREATE INDEX 206s create index sl_confirm_idx2 on public.sl_confirm 206s (con_received, con_seqno); 206s CREATE INDEX 206s create table public.sl_seqlog ( 206s seql_seqid int4, 206s seql_origin int4, 206s seql_ev_seqno int8, 206s seql_last_value int8 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_seqlog is 'Log of Sequence updates'; 206s COMMENT 206s comment on column public.sl_seqlog.seql_seqid is 'Sequence ID'; 206s COMMENT 206s comment on column public.sl_seqlog.seql_origin is 'Publisher node at which the sequence originates'; 206s COMMENT 206s comment on column public.sl_seqlog.seql_ev_seqno is 'Slony-I Event with which this sequence update is associated'; 206s COMMENT 206s comment on column public.sl_seqlog.seql_last_value is 'Last value published for this sequence'; 206s COMMENT 206s create index sl_seqlog_idx on public.sl_seqlog 206s (seql_origin, seql_ev_seqno, seql_seqid); 206s CREATE INDEX 206s create function public.sequenceLastValue(p_seqname text) returns int8 206s as $$ 206s declare 206s v_seq_row record; 206s begin 206s for v_seq_row in execute 'select last_value from ' || public.slon_quote_input(p_seqname) 206s loop 206s return v_seq_row.last_value; 206s end loop; 206s 206s -- not reached 206s end; 206s $$ language plpgsql; 206s CREATE FUNCTION 206s comment on function public.sequenceLastValue(p_seqname text) is 206s 'sequenceLastValue(p_seqname) 206s 206s Utility function used in sl_seqlastvalue view to compactly get the 206s last value from the requested sequence.'; 206s COMMENT 206s create table public.sl_log_1 ( 206s log_origin int4, 206s log_txid bigint, 206s log_tableid int4, 206s log_actionseq int8, 206s log_tablenspname text, 206s log_tablerelname text, 206s log_cmdtype "char", 206s log_cmdupdncols int4, 206s log_cmdargs text[] 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s create index sl_log_1_idx1 on public.sl_log_1 206s (log_origin, log_txid, log_actionseq); 206s CREATE INDEX 206s comment on table public.sl_log_1 is 'Stores each change to be propagated to subscriber nodes'; 206s COMMENT 206s comment on column public.sl_log_1.log_origin is 'Origin node from which the change came'; 206s COMMENT 206s comment on column public.sl_log_1.log_txid is 'Transaction ID on the origin node'; 206s COMMENT 206s comment on column public.sl_log_1.log_tableid is 'The table ID (from sl_table.tab_id) that this log entry is to affect'; 206s COMMENT 206s comment on column public.sl_log_1.log_actionseq is 'The sequence number in which actions will be applied on replicas'; 206s COMMENT 206s comment on column public.sl_log_1.log_tablenspname is 'The schema name of the table affected'; 206s COMMENT 206s comment on column public.sl_log_1.log_tablerelname is 'The table name of the table affected'; 206s COMMENT 206s comment on column public.sl_log_1.log_cmdtype is 'Replication action to take. U = Update, I = Insert, D = DELETE, T = TRUNCATE'; 206s COMMENT 206s comment on column public.sl_log_1.log_cmdupdncols is 'For cmdtype=U the number of updated columns in cmdargs'; 206s COMMENT 206s comment on column public.sl_log_1.log_cmdargs is 'The data needed to perform the log action on the replica'; 206s COMMENT 206s create table public.sl_log_2 ( 206s log_origin int4, 206s log_txid bigint, 206s log_tableid int4, 206s log_actionseq int8, 206s log_tablenspname text, 206s log_tablerelname text, 206s log_cmdtype "char", 206s log_cmdupdncols int4, 206s log_cmdargs text[] 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s create index sl_log_2_idx1 on public.sl_log_2 206s (log_origin, log_txid, log_actionseq); 206s CREATE INDEX 206s comment on table public.sl_log_2 is 'Stores each change to be propagated to subscriber nodes'; 206s COMMENT 206s comment on column public.sl_log_2.log_origin is 'Origin node from which the change came'; 206s COMMENT 206s comment on column public.sl_log_2.log_txid is 'Transaction ID on the origin node'; 206s COMMENT 206s comment on column public.sl_log_2.log_tableid is 'The table ID (from sl_table.tab_id) that this log entry is to affect'; 206s COMMENT 206s comment on column public.sl_log_2.log_actionseq is 'The sequence number in which actions will be applied on replicas'; 206s COMMENT 206s comment on column public.sl_log_2.log_tablenspname is 'The schema name of the table affected'; 206s COMMENT 206s comment on column public.sl_log_2.log_tablerelname is 'The table name of the table affected'; 206s COMMENT 206s comment on column public.sl_log_2.log_cmdtype is 'Replication action to take. U = Update, I = Insert, D = DELETE, T = TRUNCATE'; 206s COMMENT 206s comment on column public.sl_log_2.log_cmdupdncols is 'For cmdtype=U the number of updated columns in cmdargs'; 206s COMMENT 206s comment on column public.sl_log_2.log_cmdargs is 'The data needed to perform the log action on the replica'; 206s COMMENT 206s create table public.sl_log_script ( 206s log_origin int4, 206s log_txid bigint, 206s log_actionseq int8, 206s log_cmdtype "char", 206s log_cmdargs text[] 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s create index sl_log_script_idx1 on public.sl_log_script 206s (log_origin, log_txid, log_actionseq); 206s CREATE INDEX 206s comment on table public.sl_log_script is 'Captures SQL script queries to be propagated to subscriber nodes'; 206s COMMENT 206s comment on column public.sl_log_script.log_origin is 'Origin name from which the change came'; 206s COMMENT 206s comment on column public.sl_log_script.log_txid is 'Transaction ID on the origin node'; 206s COMMENT 206s comment on column public.sl_log_script.log_actionseq is 'The sequence number in which actions will be applied on replicas'; 206s COMMENT 206s comment on column public.sl_log_2.log_cmdtype is 'Replication action to take. S = Script statement, s = Script complete'; 206s COMMENT 206s comment on column public.sl_log_script.log_cmdargs is 'The DDL statement, optionally followed by selected nodes to execute it on.'; 206s COMMENT 206s create table public.sl_registry ( 206s reg_key text primary key, 206s reg_int4 int4, 206s reg_text text, 206s reg_timestamp timestamptz 206s ) WITHOUT OIDS; 206s CREATE TABLE 206s comment on table public.sl_registry is 'Stores miscellaneous runtime data'; 206s COMMENT 206s comment on column public.sl_registry.reg_key is 'Unique key of the runtime option'; 206s COMMENT 206s comment on column public.sl_registry.reg_int4 is 'Option value if type int4'; 206s COMMENT 206s comment on column public.sl_registry.reg_text is 'Option value if type text'; 206s COMMENT 206s comment on column public.sl_registry.reg_timestamp is 'Option value if type timestamp'; 206s COMMENT 206s create table public.sl_apply_stats ( 206s as_origin int4, 206s as_num_insert int8, 206s as_num_update int8, 206s as_num_delete int8, 206s as_num_truncate int8, 206s as_num_script int8, 206s as_num_total int8, 206s as_duration interval, 206s as_apply_first timestamptz, 206s as_apply_last timestamptz, 206s as_cache_prepare int8, 206s as_cache_hit int8, 206s as_cache_evict int8, 206s as_cache_prepare_max int8 206s ) WITHOUT OIDS; 207s CREATE TABLE 207s create index sl_apply_stats_idx1 on public.sl_apply_stats 207s (as_origin); 207s CREATE INDEX 207s comment on table public.sl_apply_stats is 'Local SYNC apply statistics (running totals)'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_origin is 'Origin of the SYNCs'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_num_insert is 'Number of INSERT operations performed'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_num_update is 'Number of UPDATE operations performed'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_num_delete is 'Number of DELETE operations performed'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_num_truncate is 'Number of TRUNCATE operations performed'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_num_script is 'Number of DDL operations performed'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_num_total is 'Total number of operations'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_duration is 'Processing time'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_apply_first is 'Timestamp of first recorded SYNC'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_apply_last is 'Timestamp of most recent recorded SYNC'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_cache_evict is 'Number of apply query cache evict operations'; 207s COMMENT 207s comment on column public.sl_apply_stats.as_cache_prepare_max is 'Maximum number of apply queries prepared in one SYNC group'; 207s COMMENT 207s create view public.sl_seqlastvalue as 207s select SQ.seq_id, SQ.seq_set, SQ.seq_reloid, 207s S.set_origin as seq_origin, 207s public.sequenceLastValue( 207s "pg_catalog".quote_ident(PGN.nspname) || '.' || 207s "pg_catalog".quote_ident(PGC.relname)) as seq_last_value 207s from public.sl_sequence SQ, public.sl_set S, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where S.set_id = SQ.seq_set 207s and PGC.oid = SQ.seq_reloid and PGN.oid = PGC.relnamespace; 207s CREATE VIEW 207s create view public.sl_failover_targets as 207s select set_id, 207s set_origin as set_origin, 207s sub1.sub_receiver as backup_id 207s FROM 207s public.sl_subscribe sub1 207s ,public.sl_set set1 207s where 207s sub1.sub_set=set_id 207s and sub1.sub_forward=true 207s --exclude candidates where the set_origin 207s --has a path a node but the failover 207s --candidate has no path to that node 207s and sub1.sub_receiver not in 207s (select p1.pa_client from 207s public.sl_path p1 207s left outer join public.sl_path p2 on 207s (p2.pa_client=p1.pa_client 207s and p2.pa_server=sub1.sub_receiver) 207s where p2.pa_client is null 207s and p1.pa_server=set_origin 207s and p1.pa_client<>sub1.sub_receiver 207s ) 207s and sub1.sub_provider=set_origin 207s --exclude any subscribers that are not 207s --direct subscribers of all sets on the 207s --origin 207s and sub1.sub_receiver not in 207s (select direct_recv.sub_receiver 207s from 207s 207s (--all direct receivers of the first set 207s select subs2.sub_receiver 207s from public.sl_subscribe subs2 207s where subs2.sub_provider=set1.set_origin 207s and subs2.sub_set=set1.set_id) as 207s direct_recv 207s inner join 207s (--all other sets from the origin 207s select set_id from public.sl_set set2 207s where set2.set_origin=set1.set_origin 207s and set2.set_id<>sub1.sub_set) 207s as othersets on(true) 207s left outer join public.sl_subscribe subs3 207s on(subs3.sub_set=othersets.set_id 207s and subs3.sub_forward=true 207s and subs3.sub_provider=set1.set_origin 207s and direct_recv.sub_receiver=subs3.sub_receiver) 207s where subs3.sub_receiver is null 207s ); 207s CREATE VIEW 207s create sequence public.sl_local_node_id 207s MINVALUE -1; 207s CREATE SEQUENCE 207s SELECT setval('public.sl_local_node_id', -1); 207s setval 207s -------- 207s -1 207s (1 row) 207s 207s comment on sequence public.sl_local_node_id is 'The local node ID is initialized to -1, meaning that this node is not initialized yet.'; 207s COMMENT 207s create sequence public.sl_event_seq; 207s CREATE SEQUENCE 207s comment on sequence public.sl_event_seq is 'The sequence for numbering events originating from this node.'; 207s COMMENT 207s select setval('public.sl_event_seq', 5000000000); 207s setval 207s ------------ 207s 5000000000 207s (1 row) 207s 207s create sequence public.sl_action_seq; 207s CREATE SEQUENCE 207s comment on sequence public.sl_action_seq is 'The sequence to number statements in the transaction logs, so that the replication engines can figure out the "agreeable" order of statements.'; 207s COMMENT 207s create sequence public.sl_log_status 207s MINVALUE 0 MAXVALUE 3; 207s CREATE SEQUENCE 207s SELECT setval('public.sl_log_status', 0); 207s NOTICE: checked validity of cluster main namespace - OK! 207s setval 207s -------- 207s 0 207s (1 row) 207s 207s comment on sequence public.sl_log_status is ' 207s Bit 0x01 determines the currently active log table 207s Bit 0x02 tells if the engine needs to read both logs 207s after switching until the old log is clean and truncated. 207s 207s Possible values: 207s 0 sl_log_1 active, sl_log_2 clean 207s 1 sl_log_2 active, sl_log_1 clean 207s 2 sl_log_1 active, sl_log_2 unknown - cleanup 207s 3 sl_log_2 active, sl_log_1 unknown - cleanup 207s 207s This is not yet in use. 207s '; 207s COMMENT 207s create table public.sl_config_lock ( 207s dummy integer 207s ); 207s CREATE TABLE 207s comment on table public.sl_config_lock is 'This table exists solely to prevent overlapping execution of configuration change procedures and the resulting possible deadlocks. 207s '; 207s COMMENT 207s comment on column public.sl_config_lock.dummy is 'No data ever goes in this table so the contents never matter. Indeed, this column does not really need to exist.'; 207s COMMENT 207s create table public.sl_event_lock ( 207s dummy integer 207s ); 207s CREATE TABLE 207s comment on table public.sl_event_lock is 'This table exists solely to prevent multiple connections from concurrently creating new events and perhaps getting them out of order.'; 207s COMMENT 207s comment on column public.sl_event_lock.dummy is 'No data ever goes in this table so the contents never matter. Indeed, this column does not really need to exist.'; 207s COMMENT 207s create table public.sl_archive_counter ( 207s ac_num bigint, 207s ac_timestamp timestamptz 207s ) without oids; 207s CREATE TABLE 207s comment on table public.sl_archive_counter is 'Table used to generate the log shipping archive number. 207s '; 207s COMMENT 207s comment on column public.sl_archive_counter.ac_num is 'Counter of SYNC ID used in log shipping as the archive number'; 207s COMMENT 207s comment on column public.sl_archive_counter.ac_timestamp is 'Time at which the archive log was generated on the subscriber'; 207s COMMENT 207s insert into public.sl_archive_counter (ac_num, ac_timestamp) 207s values (0, 'epoch'::timestamptz); 207s INSERT 0 1 207s create table public.sl_components ( 207s co_actor text not null primary key, 207s co_pid integer not null, 207s co_node integer not null, 207s co_connection_pid integer not null, 207s co_activity text, 207s co_starttime timestamptz not null, 207s co_event bigint, 207s co_eventtype text 207s ) without oids; 207s CREATE TABLE 207s comment on table public.sl_components is 'Table used to monitor what various slon/slonik components are doing'; 207s COMMENT 207s comment on column public.sl_components.co_actor is 'which component am I?'; 207s COMMENT 207s comment on column public.sl_components.co_pid is 'my process/thread PID on node where slon runs'; 207s COMMENT 207s comment on column public.sl_components.co_node is 'which node am I servicing?'; 207s COMMENT 207s comment on column public.sl_components.co_connection_pid is 'PID of database connection being used on database server'; 207s COMMENT 207s comment on column public.sl_components.co_activity is 'activity that I am up to'; 207s COMMENT 207s comment on column public.sl_components.co_starttime is 'when did my activity begin? (timestamp reported as per slon process on server running slon)'; 207s COMMENT 207s comment on column public.sl_components.co_eventtype is 'what kind of event am I processing? (commonly n/a for event loop main threads)'; 207s COMMENT 207s comment on column public.sl_components.co_event is 'which event have I started processing?'; 207s COMMENT 207s CREATE OR replace function public.agg_text_sum(txt_before TEXT, txt_new TEXT) RETURNS TEXT AS 207s $BODY$ 207s DECLARE 207s c_delim text; 207s BEGIN 207s c_delim = ','; 207s IF (txt_before IS NULL or txt_before='') THEN 207s RETURN txt_new; 207s END IF; 207s RETURN txt_before || c_delim || txt_new; 207s END; 207s $BODY$ 207s LANGUAGE plpgsql; 207s CREATE FUNCTION 207s comment on function public.agg_text_sum(text,text) is 207s 'An accumulator function used by the slony string_agg function to 207s aggregate rows into a string'; 207s COMMENT 207s CREATE AGGREGATE public.string_agg(text) ( 207s SFUNC=public.agg_text_sum, 207s STYPE=text, 207s INITCOND='' 207s ); 207s CREATE AGGREGATE 207s grant usage on schema public to public; 207s GRANT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text, ev_data6 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text, ev_data6 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text, ev_data6 text, ev_data7 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text, ev_data6 text, ev_data7 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text, ev_data6 text, ev_data7 text, ev_data8 text) 207s returns bigint 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__createEvent' 207s language C 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.createEvent (p_cluster_name name, p_event_type text, ev_data1 text, ev_data2 text, ev_data3 text, ev_data4 text, ev_data5 text, ev_data6 text, ev_data7 text, ev_data8 text) is 207s 'FUNCTION createEvent (cluster_name, ev_type [, ev_data [...]]) 207s 207s Create an sl_event entry'; 207s COMMENT 207s create or replace function public.denyAccess () 207s returns trigger 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__denyAccess' 207s language C 207s security definer; 207s CREATE FUNCTION 207s comment on function public.denyAccess () is 207s 'Trigger function to prevent modifications to a table on a subscriber'; 207s COMMENT 207s grant execute on function public.denyAccess () to public; 207s GRANT 207s create or replace function public.lockedSet () 207s returns trigger 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__lockedSet' 207s language C; 207s CREATE FUNCTION 207s comment on function public.lockedSet () is 207s 'Trigger function to prevent modifications to a table before and after a moveSet()'; 207s COMMENT 207s create or replace function public.getLocalNodeId (p_cluster name) returns int4 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__getLocalNodeId' 207s language C 207s security definer; 207s CREATE FUNCTION 207s grant execute on function public.getLocalNodeId (p_cluster name) to public; 207s GRANT 207s comment on function public.getLocalNodeId (p_cluster name) is 207s 'Returns the node ID of the node being serviced on the local database'; 207s COMMENT 207s create or replace function public.getModuleVersion () returns text 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__getModuleVersion' 207s language C 207s security definer; 207s CREATE FUNCTION 207s grant execute on function public.getModuleVersion () to public; 207s GRANT 207s comment on function public.getModuleVersion () is 207s 'Returns the compiled-in version number of the Slony-I shared object'; 207s COMMENT 207s create or replace function public.resetSession() returns text 207s as '$libdir/slony1_funcs.2.2.11','_Slony_I_2_2_11__resetSession' 207s language C; 207s CREATE FUNCTION 207s create or replace function public.logApply () returns trigger 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__logApply' 207s language C 207s security definer; 207s CREATE FUNCTION 207s create or replace function public.logApplySetCacheSize (p_size int4) 207s returns int4 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__logApplySetCacheSize' 207s language C; 207s CREATE FUNCTION 207s create or replace function public.logApplySaveStats (p_cluster name, p_origin int4, p_duration interval) 207s returns int4 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__logApplySaveStats' 207s language C; 207s CREATE FUNCTION 207s create or replace function public.checkmoduleversion () returns text as $$ 207s declare 207s moduleversion text; 207s begin 207s select into moduleversion public.getModuleVersion(); 207s if moduleversion <> '2.2.11' then 207s raise exception 'Slonik version: 2.2.11 != Slony-I version in PG build %', 207s moduleversion; 207s end if; 207s return null; 207s end;$$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.checkmoduleversion () is 207s 'Inline test function that verifies that slonik request for STORE 207s NODE/INIT CLUSTER is being run against a conformant set of 207s schema/functions.'; 207s COMMENT 207s select public.checkmoduleversion(); 207s checkmoduleversion 207s -------------------- 207s 207s (1 row) 207s 207s create or replace function public.decode_tgargs(bytea) returns text[] as 207s '$libdir/slony1_funcs.2.2.11','_Slony_I_2_2_11__slon_decode_tgargs' language C security definer; 207s CREATE FUNCTION 207s comment on function public.decode_tgargs(bytea) is 207s 'Translates the contents of pg_trigger.tgargs to an array of text arguments'; 207s COMMENT 207s grant execute on function public.decode_tgargs(bytea) to public; 207s GRANT 207s create or replace function public.check_namespace_validity () returns boolean as $$ 207s declare 207s c_cluster text; 207s begin 207s c_cluster := 'main'; 207s if c_cluster !~ E'^[[:alpha:]_][[:alnum:]_\$]{0,62}$' then 207s raise exception 'Cluster name % is not a valid SQL symbol!', c_cluster; 207s else 207s raise notice 'checked validity of cluster % namespace - OK!', c_cluster; 207s end if; 207s return 't'; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s select public.check_namespace_validity(); 207s check_namespace_validity 207s -------------------------- 207s t 207s (1 row) 207s 207s drop function public.check_namespace_validity(); 207s DROP FUNCTION 207s create or replace function public.logTrigger () returns trigger 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__logTrigger' 207s language C 207s security definer; 207s CREATE FUNCTION 207s comment on function public.logTrigger () is 207s 'This is the trigger that is executed on the origin node that causes 207s updates to be recorded in sl_log_1/sl_log_2.'; 207s COMMENT 207s grant execute on function public.logTrigger () to public; 207s GRANT 207s create or replace function public.terminateNodeConnections (p_failed_node int4) returns int4 207s as $$ 207s declare 207s v_row record; 207s begin 207s for v_row in select nl_nodeid, nl_conncnt, 207s nl_backendpid from public.sl_nodelock 207s where nl_nodeid = p_failed_node for update 207s loop 207s perform public.killBackend(v_row.nl_backendpid, 'TERM'); 207s delete from public.sl_nodelock 207s where nl_nodeid = v_row.nl_nodeid 207s and nl_conncnt = v_row.nl_conncnt; 207s end loop; 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.terminateNodeConnections (p_failed_node int4) is 207s 'terminates all backends that have registered to be from the given node'; 207s COMMENT 207s create or replace function public.killBackend (p_pid int4, p_signame text) returns int4 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__killBackend' 207s language C; 207s CREATE FUNCTION 207s comment on function public.killBackend(p_pid int4, p_signame text) is 207s 'Send a signal to a postgres process. Requires superuser rights'; 207s COMMENT 207s create or replace function public.seqtrack (p_seqid int4, p_seqval int8) returns int8 207s as '$libdir/slony1_funcs.2.2.11', '_Slony_I_2_2_11__seqtrack' 207s strict language C; 207s CREATE FUNCTION 207s comment on function public.seqtrack(p_seqid int4, p_seqval int8) is 207s 'Returns NULL if seqval has not changed since the last call for seqid'; 207s COMMENT 207s create or replace function public.slon_quote_brute(p_tab_fqname text) returns text 207s as $$ 207s declare 207s v_fqname text default ''; 207s begin 207s v_fqname := '"' || replace(p_tab_fqname,'"','""') || '"'; 207s return v_fqname; 207s end; 207s $$ language plpgsql immutable; 207s CREATE FUNCTION 207s comment on function public.slon_quote_brute(p_tab_fqname text) is 207s 'Brutally quote the given text'; 207s COMMENT 207s create or replace function public.slon_quote_input(p_tab_fqname text) returns text as $$ 207s declare 207s v_nsp_name text; 207s v_tab_name text; 207s v_i integer; 207s v_l integer; 207s v_pq2 integer; 207s begin 207s v_l := length(p_tab_fqname); 207s 207s -- Let us search for the dot 207s if p_tab_fqname like '"%' then 207s -- if the first part of the ident starts with a double quote, search 207s -- for the closing double quote, skipping over double double quotes. 207s v_i := 2; 207s while v_i <= v_l loop 207s if substr(p_tab_fqname, v_i, 1) != '"' then 207s v_i := v_i + 1; 207s else 207s v_i := v_i + 1; 207s if substr(p_tab_fqname, v_i, 1) != '"' then 207s exit; 207s end if; 207s v_i := v_i + 1; 207s end if; 207s end loop; 207s else 207s -- first part of ident is not quoted, search for the dot directly 207s v_i := 1; 207s while v_i <= v_l loop 207s if substr(p_tab_fqname, v_i, 1) = '.' then 207s exit; 207s end if; 207s v_i := v_i + 1; 207s end loop; 207s end if; 207s 207s -- v_i now points at the dot or behind the string. 207s 207s if substr(p_tab_fqname, v_i, 1) = '.' then 207s -- There is a dot now, so split the ident into its namespace 207s -- and objname parts and make sure each is quoted 207s v_nsp_name := substr(p_tab_fqname, 1, v_i - 1); 207s v_tab_name := substr(p_tab_fqname, v_i + 1); 207s if v_nsp_name not like '"%' then 207s v_nsp_name := '"' || replace(v_nsp_name, '"', '""') || 207s '"'; 207s end if; 207s if v_tab_name not like '"%' then 207s v_tab_name := '"' || replace(v_tab_name, '"', '""') || 207s '"'; 207s end if; 207s 207s return v_nsp_name || '.' || v_tab_name; 207s else 207s -- No dot ... must be just an ident without schema 207s if p_tab_fqname like '"%' then 207s return p_tab_fqname; 207s else 207s return '"' || replace(p_tab_fqname, '"', '""') || '"'; 207s end if; 207s end if; 207s 207s end;$$ language plpgsql immutable; 207s CREATE FUNCTION 207s comment on function public.slon_quote_input(p_text text) is 207s 'quote all words that aren''t quoted yet'; 207s COMMENT 207s create or replace function public.slonyVersionMajor() 207s returns int4 207s as $$ 207s begin 207s return 2; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.slonyVersionMajor () is 207s 'Returns the major version number of the slony schema'; 207s COMMENT 207s create or replace function public.slonyVersionMinor() 207s returns int4 207s as $$ 207s begin 207s return 2; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.slonyVersionMinor () is 207s 'Returns the minor version number of the slony schema'; 207s COMMENT 207s create or replace function public.slonyVersionPatchlevel() 207s returns int4 207s as $$ 207s begin 207s return 11; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.slonyVersionPatchlevel () is 207s 'Returns the version patch level of the slony schema'; 207s COMMENT 207s create or replace function public.slonyVersion() 207s returns text 207s as $$ 207s begin 207s return public.slonyVersionMajor()::text || '.' || 207s public.slonyVersionMinor()::text || '.' || 207s public.slonyVersionPatchlevel()::text ; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.slonyVersion() is 207s 'Returns the version number of the slony schema'; 207s COMMENT 207s create or replace function public.registry_set_int4(p_key text, p_value int4) 207s returns int4 as $$ 207s BEGIN 207s if p_value is null then 207s delete from public.sl_registry 207s where reg_key = p_key; 207s else 207s lock table public.sl_registry; 207s update public.sl_registry 207s set reg_int4 = p_value 207s where reg_key = p_key; 207s if not found then 207s insert into public.sl_registry (reg_key, reg_int4) 207s values (p_key, p_value); 207s end if; 207s end if; 207s return p_value; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.registry_set_int4(p_key text, p_value int4) is 207s 'registry_set_int4(key, value) 207s 207s Set or delete a registry value'; 207s COMMENT 207s create or replace function public.registry_get_int4(p_key text, p_default int4) 207s returns int4 as $$ 207s DECLARE 207s v_value int4; 207s BEGIN 207s select reg_int4 into v_value from public.sl_registry 207s where reg_key = p_key; 207s if not found then 207s v_value = p_default; 207s if p_default notnull then 207s perform public.registry_set_int4(p_key, p_default); 207s end if; 207s else 207s if v_value is null then 207s raise exception 'Slony-I: registry key % is not an int4 value', 207s p_key; 207s end if; 207s end if; 207s return v_value; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.registry_get_int4(p_key text, p_default int4) is 207s 'registry_get_int4(key, value) 207s 207s Get a registry value. If not present, set and return the default.'; 207s COMMENT 207s create or replace function public.registry_set_text(p_key text, p_value text) 207s returns text as $$ 207s BEGIN 207s if p_value is null then 207s delete from public.sl_registry 207s where reg_key = p_key; 207s else 207s lock table public.sl_registry; 207s update public.sl_registry 207s set reg_text = p_value 207s where reg_key = p_key; 207s if not found then 207s insert into public.sl_registry (reg_key, reg_text) 207s values (p_key, p_value); 207s end if; 207s end if; 207s return p_value; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.registry_set_text(text, text) is 207s 'registry_set_text(key, value) 207s 207s Set or delete a registry value'; 207s COMMENT 207s create or replace function public.registry_get_text(p_key text, p_default text) 207s returns text as $$ 207s DECLARE 207s v_value text; 207s BEGIN 207s select reg_text into v_value from public.sl_registry 207s where reg_key = p_key; 207s if not found then 207s v_value = p_default; 207s if p_default notnull then 207s perform public.registry_set_text(p_key, p_default); 207s end if; 207s else 207s if v_value is null then 207s raise exception 'Slony-I: registry key % is not a text value', 207s p_key; 207s end if; 207s end if; 207s return v_value; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.registry_get_text(p_key text, p_default text) is 207s 'registry_get_text(key, value) 207s 207s Get a registry value. If not present, set and return the default.'; 207s COMMENT 207s create or replace function public.registry_set_timestamp(p_key text, p_value timestamptz) 207s returns timestamp as $$ 207s BEGIN 207s if p_value is null then 207s delete from public.sl_registry 207s where reg_key = p_key; 207s else 207s lock table public.sl_registry; 207s update public.sl_registry 207s set reg_timestamp = p_value 207s where reg_key = p_key; 207s if not found then 207s insert into public.sl_registry (reg_key, reg_timestamp) 207s values (p_key, p_value); 207s end if; 207s end if; 207s return p_value; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.registry_set_timestamp(p_key text, p_value timestamptz) is 207s 'registry_set_timestamp(key, value) 207s 207s Set or delete a registry value'; 207s COMMENT 207s create or replace function public.registry_get_timestamp(p_key text, p_default timestamptz) 207s returns timestamp as $$ 207s DECLARE 207s v_value timestamp; 207s BEGIN 207s select reg_timestamp into v_value from public.sl_registry 207s where reg_key = p_key; 207s if not found then 207s v_value = p_default; 207s if p_default notnull then 207s perform public.registry_set_timestamp(p_key, p_default); 207s end if; 207s else 207s if v_value is null then 207s raise exception 'Slony-I: registry key % is not an timestamp value', 207s p_key; 207s end if; 207s end if; 207s return v_value; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.registry_get_timestamp(p_key text, p_default timestamptz) is 207s 'registry_get_timestamp(key, value) 207s 207s Get a registry value. If not present, set and return the default.'; 207s COMMENT 207s create or replace function public.cleanupNodelock () 207s returns int4 207s as $$ 207s declare 207s v_row record; 207s begin 207s for v_row in select nl_nodeid, nl_conncnt, nl_backendpid 207s from public.sl_nodelock 207s for update 207s loop 207s if public.killBackend(v_row.nl_backendpid, 'NULL') < 0 then 207s raise notice 'Slony-I: cleanup stale sl_nodelock entry for pid=%', 207s v_row.nl_backendpid; 207s delete from public.sl_nodelock where 207s nl_nodeid = v_row.nl_nodeid and 207s nl_conncnt = v_row.nl_conncnt; 207s end if; 207s end loop; 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.cleanupNodelock() is 207s 'Clean up stale entries when restarting slon'; 207s COMMENT 207s create or replace function public.registerNodeConnection (p_nodeid int4) 207s returns int4 207s as $$ 207s begin 207s insert into public.sl_nodelock 207s (nl_nodeid, nl_backendpid) 207s values 207s (p_nodeid, pg_backend_pid()); 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.registerNodeConnection (p_nodeid int4) is 207s 'Register (uniquely) the node connection so that only one slon can service the node'; 207s COMMENT 207s create or replace function public.initializeLocalNode (p_local_node_id int4, p_comment text) 207s returns int4 207s as $$ 207s declare 207s v_old_node_id int4; 207s v_first_log_no int4; 207s v_event_seq int8; 207s begin 207s -- ---- 207s -- Make sure this node is uninitialized or got reset 207s -- ---- 207s select last_value::int4 into v_old_node_id from public.sl_local_node_id; 207s if v_old_node_id != -1 then 207s raise exception 'Slony-I: This node is already initialized'; 207s end if; 207s 207s -- ---- 207s -- Set sl_local_node_id to the requested value and add our 207s -- own system to sl_node. 207s -- ---- 207s perform setval('public.sl_local_node_id', p_local_node_id); 207s perform public.storeNode_int (p_local_node_id, p_comment); 207s 207s if (pg_catalog.current_setting('max_identifier_length')::integer - pg_catalog.length('public')) < 5 then 207s raise notice 'Slony-I: Cluster name length [%] versus system max_identifier_length [%] ', pg_catalog.length('public'), pg_catalog.current_setting('max_identifier_length'); 207s raise notice 'leaves narrow/no room for some Slony-I-generated objects (such as indexes).'; 207s raise notice 'You may run into problems later!'; 207s end if; 207s 207s -- 207s -- Put the apply trigger onto sl_log_1 and sl_log_2 207s -- 207s create trigger apply_trigger 207s before INSERT on public.sl_log_1 207s for each row execute procedure public.logApply('_main'); 207s alter table public.sl_log_1 207s enable replica trigger apply_trigger; 207s create trigger apply_trigger 207s before INSERT on public.sl_log_2 207s for each row execute procedure public.logApply('_main'); 207s alter table public.sl_log_2 207s enable replica trigger apply_trigger; 207s 207s return p_local_node_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.initializeLocalNode (p_local_node_id int4, p_comment text) is 207s 'no_id - Node ID # 207s no_comment - Human-oriented comment 207s 207s Initializes the new node, no_id'; 207s COMMENT 207s create or replace function public.storeNode (p_no_id int4, p_no_comment text) 207s returns bigint 207s as $$ 207s begin 207s perform public.storeNode_int (p_no_id, p_no_comment); 207s return public.createEvent('_main', 'STORE_NODE', 207s p_no_id::text, p_no_comment::text); 207s end; 207s $$ language plpgsql 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.storeNode(p_no_id int4, p_no_comment text) is 207s 'no_id - Node ID # 207s no_comment - Human-oriented comment 207s 207s Generate the STORE_NODE event for node no_id'; 207s COMMENT 207s create or replace function public.storeNode_int (p_no_id int4, p_no_comment text) 207s returns int4 207s as $$ 207s declare 207s v_old_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check if the node exists 207s -- ---- 207s select * into v_old_row 207s from public.sl_node 207s where no_id = p_no_id 207s for update; 207s if found then 207s -- ---- 207s -- Node exists, update the existing row. 207s -- ---- 207s update public.sl_node 207s set no_comment = p_no_comment 207s where no_id = p_no_id; 207s else 207s -- ---- 207s -- New node, insert the sl_node row 207s -- ---- 207s insert into public.sl_node 207s (no_id, no_active, no_comment,no_failed) values 207s (p_no_id, 'f', p_no_comment,false); 207s end if; 207s 207s return p_no_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.storeNode_int(p_no_id int4, p_no_comment text) is 207s 'no_id - Node ID # 207s no_comment - Human-oriented comment 207s 207s Internal function to process the STORE_NODE event for node no_id'; 207s COMMENT 207s create or replace function public.enableNode (p_no_id int4) 207s returns bigint 207s as $$ 207s declare 207s v_local_node_id int4; 207s v_node_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that we are the node to activate and that we are 207s -- currently disabled. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select * into v_node_row 207s from public.sl_node 207s where no_id = p_no_id 207s for update; 207s if not found then 207s raise exception 'Slony-I: node % not found', p_no_id; 207s end if; 207s if v_node_row.no_active then 207s raise exception 'Slony-I: node % is already active', p_no_id; 207s end if; 207s 207s -- ---- 207s -- Activate this node and generate the ENABLE_NODE event 207s -- ---- 207s perform public.enableNode_int (p_no_id); 207s return public.createEvent('_main', 'ENABLE_NODE', 207s p_no_id::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.enableNode(p_no_id int4) is 207s 'no_id - Node ID # 207s 207s Generate the ENABLE_NODE event for node no_id'; 207s COMMENT 207s create or replace function public.enableNode_int (p_no_id int4) 207s returns int4 207s as $$ 207s declare 207s v_local_node_id int4; 207s v_node_row record; 207s v_sub_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that the node is inactive 207s -- ---- 207s select * into v_node_row 207s from public.sl_node 207s where no_id = p_no_id 207s for update; 207s if not found then 207s raise exception 'Slony-I: node % not found', p_no_id; 207s end if; 207s if v_node_row.no_active then 207s return p_no_id; 207s end if; 207s 207s -- ---- 207s -- Activate the node and generate sl_confirm status rows for it. 207s -- ---- 207s update public.sl_node 207s set no_active = 't' 207s where no_id = p_no_id; 207s insert into public.sl_confirm 207s (con_origin, con_received, con_seqno) 207s select no_id, p_no_id, 0 from public.sl_node 207s where no_id != p_no_id 207s and no_active; 207s insert into public.sl_confirm 207s (con_origin, con_received, con_seqno) 207s select p_no_id, no_id, 0 from public.sl_node 207s where no_id != p_no_id 207s and no_active; 207s 207s -- ---- 207s -- Generate ENABLE_SUBSCRIPTION events for all sets that 207s -- origin here and are subscribed by the just enabled node. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s for v_sub_row in select SUB.sub_set, SUB.sub_provider from 207s public.sl_set S, 207s public.sl_subscribe SUB 207s where S.set_origin = v_local_node_id 207s and S.set_id = SUB.sub_set 207s and SUB.sub_receiver = p_no_id 207s for update of S 207s loop 207s perform public.enableSubscription (v_sub_row.sub_set, 207s v_sub_row.sub_provider, p_no_id); 207s end loop; 207s 207s return p_no_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.enableNode_int(p_no_id int4) is 207s 'no_id - Node ID # 207s 207s Internal function to process the ENABLE_NODE event for node no_id'; 207s COMMENT 207s create or replace function public.disableNode (p_no_id int4) 207s returns bigint 207s as $$ 207s begin 207s -- **** TODO **** 207s raise exception 'Slony-I: disableNode() not implemented'; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.disableNode(p_no_id int4) is 207s 'generate DISABLE_NODE event for node no_id'; 207s COMMENT 207s create or replace function public.disableNode_int (p_no_id int4) 207s returns int4 207s as $$ 207s begin 207s -- **** TODO **** 207s raise exception 'Slony-I: disableNode_int() not implemented'; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.disableNode(p_no_id int4) is 207s 'process DISABLE_NODE event for node no_id 207s 207s NOTE: This is not yet implemented!'; 207s COMMENT 207s create or replace function public.dropNode (p_no_ids int4[]) 207s returns bigint 207s as $$ 207s declare 207s v_node_row record; 207s v_idx integer; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that this got called on a different node 207s -- ---- 207s if public.getLocalNodeId('_main') = ANY (p_no_ids) then 207s raise exception 'Slony-I: DROP_NODE cannot initiate on the dropped node'; 207s end if; 207s 207s -- 207s -- if any of the deleted nodes are receivers we drop the sl_subscribe line 207s -- 207s delete from public.sl_subscribe where sub_receiver = ANY (p_no_ids); 207s 207s v_idx:=1; 207s LOOP 207s EXIT WHEN v_idx>array_upper(p_no_ids,1) ; 207s select * into v_node_row from public.sl_node 207s where no_id = p_no_ids[v_idx] 207s for update; 207s if not found then 207s raise exception 'Slony-I: unknown node ID % %', p_no_ids[v_idx],v_idx; 207s end if; 207s -- ---- 207s -- Make sure we do not break other nodes subscriptions with this 207s -- ---- 207s if exists (select true from public.sl_subscribe 207s where sub_provider = p_no_ids[v_idx]) 207s then 207s raise exception 'Slony-I: Node % is still configured as a data provider', 207s p_no_ids[v_idx]; 207s end if; 207s 207s -- ---- 207s -- Make sure no set originates there any more 207s -- ---- 207s if exists (select true from public.sl_set 207s where set_origin = p_no_ids[v_idx]) 207s then 207s raise exception 'Slony-I: Node % is still origin of one or more sets', 207s p_no_ids[v_idx]; 207s end if; 207s 207s -- ---- 207s -- Call the internal drop functionality and generate the event 207s -- ---- 207s perform public.dropNode_int(p_no_ids[v_idx]); 207s v_idx:=v_idx+1; 207s END LOOP; 207s return public.createEvent('_main', 'DROP_NODE', 207s array_to_string(p_no_ids,',')); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropNode(p_no_ids int4[]) is 207s 'generate DROP_NODE event to drop node node_id from replication'; 207s COMMENT 207s create or replace function public.dropNode_int (p_no_id int4) 207s returns int4 207s as $$ 207s declare 207s v_tab_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- If the dropped node is a remote node, clean the configuration 207s -- from all traces for it. 207s -- ---- 207s if p_no_id <> public.getLocalNodeId('_main') then 207s delete from public.sl_subscribe 207s where sub_receiver = p_no_id; 207s delete from public.sl_listen 207s where li_origin = p_no_id 207s or li_provider = p_no_id 207s or li_receiver = p_no_id; 207s delete from public.sl_path 207s where pa_server = p_no_id 207s or pa_client = p_no_id; 207s delete from public.sl_confirm 207s where con_origin = p_no_id 207s or con_received = p_no_id; 207s delete from public.sl_event 207s where ev_origin = p_no_id; 207s delete from public.sl_node 207s where no_id = p_no_id; 207s 207s return p_no_id; 207s end if; 207s 207s -- ---- 207s -- This is us ... deactivate the node for now, the daemon 207s -- will call uninstallNode() in a separate transaction. 207s -- ---- 207s update public.sl_node 207s set no_active = false 207s where no_id = p_no_id; 207s 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s return p_no_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropNode_int(p_no_id int4) is 207s 'internal function to process DROP_NODE event to drop node node_id from replication'; 207s COMMENT 207s create or replace function public.preFailover(p_failed_node int4,p_is_candidate boolean) 207s returns int4 207s as $$ 207s declare 207s v_row record; 207s v_row2 record; 207s v_n int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- All consistency checks first 207s 207s if p_is_candidate then 207s -- ---- 207s -- Check all sets originating on the failed node 207s -- ---- 207s for v_row in select set_id 207s from public.sl_set 207s where set_origin = p_failed_node 207s loop 207s -- ---- 207s -- Check that the backup node is subscribed to all sets 207s -- that originate on the failed node 207s -- ---- 207s select into v_row2 sub_forward, sub_active 207s from public.sl_subscribe 207s where sub_set = v_row.set_id 207s and sub_receiver = public.getLocalNodeId('_main'); 207s if not found then 207s raise exception 'Slony-I: cannot failover - node % is not subscribed to set %', 207s public.getLocalNodeId('_main'), v_row.set_id; 207s end if; 207s 207s -- ---- 207s -- Check that the subscription is active 207s -- ---- 207s if not v_row2.sub_active then 207s raise exception 'Slony-I: cannot failover - subscription for set % is not active', 207s v_row.set_id; 207s end if; 207s 207s -- ---- 207s -- If there are other subscribers, the backup node needs to 207s -- be a forwarder too. 207s -- ---- 207s select into v_n count(*) 207s from public.sl_subscribe 207s where sub_set = v_row.set_id 207s and sub_receiver <> public.getLocalNodeId('_main'); 207s if v_n > 0 and not v_row2.sub_forward then 207s raise exception 'Slony-I: cannot failover - node % is not a forwarder of set %', 207s public.getLocalNodeId('_main'), v_row.set_id; 207s end if; 207s end loop; 207s end if; 207s 207s -- ---- 207s -- Terminate all connections of the failed node the hard way 207s -- ---- 207s perform public.terminateNodeConnections(p_failed_node); 207s 207s update public.sl_path set pa_conninfo='' WHERE 207s pa_server=p_failed_node; 207s notify "_main_Restart"; 207s -- ---- 207s -- That is it - so far. 207s -- ---- 207s return p_failed_node; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.preFailover(p_failed_node int4,is_failover_candidate boolean) is 207s 'Prepare for a failover. This function is called on all candidate nodes. 207s It blanks the paths to the failed node 207s and then restart of all node daemons.'; 207s COMMENT 207s create or replace function public.failedNode(p_failed_node int4, p_backup_node int4,p_failed_nodes integer[]) 207s returns int4 207s as $$ 207s declare 207s v_row record; 207s v_row2 record; 207s v_failed boolean; 207s v_restart_required boolean; 207s begin 207s 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s v_restart_required:=false; 207s -- 207s -- any nodes other than the backup receiving 207s -- ANY subscription from a failed node 207s -- will now get that data from the backup node. 207s update public.sl_subscribe set 207s sub_provider=p_backup_node 207s where sub_provider=p_failed_node 207s and sub_receiver<>p_backup_node 207s and sub_receiver <> ALL (p_failed_nodes); 207s if found then 207s v_restart_required:=true; 207s end if; 207s -- 207s -- if this node is receiving a subscription from the backup node 207s -- with a failed node as the provider we need to fix this. 207s update public.sl_subscribe set 207s sub_provider=p_backup_node 207s from public.sl_set 207s where set_id = sub_set 207s and set_origin=p_failed_node 207s and sub_provider = ANY(p_failed_nodes) 207s and sub_receiver=public.getLocalNodeId('_main'); 207s 207s -- ---- 207s -- Terminate all connections of the failed node the hard way 207s -- ---- 207s perform public.terminateNodeConnections(p_failed_node); 207s 207s -- Clear out the paths for the failed node. 207s -- This ensures that *this* node won't be pulling data from 207s -- the failed node even if it *does* become accessible 207s 207s update public.sl_path set pa_conninfo='' WHERE 207s pa_server=p_failed_node 207s and pa_conninfo<>''; 207s 207s if found then 207s v_restart_required:=true; 207s end if; 207s 207s v_failed := exists (select 1 from public.sl_node 207s where no_failed=true and no_id=p_failed_node); 207s 207s if not v_failed then 207s 207s update public.sl_node set no_failed=true where no_id = ANY (p_failed_nodes) 207s and no_failed=false; 207s if found then 207s v_restart_required:=true; 207s end if; 207s end if; 207s 207s if v_restart_required then 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s -- ---- 207s -- Make sure the node daemon will restart 207s -- ---- 207s notify "_main_Restart"; 207s end if; 207s 207s 207s -- ---- 207s -- That is it - so far. 207s -- ---- 207s return p_failed_node; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.failedNode(p_failed_node int4, p_backup_node int4,p_failed_nodes integer[]) is 207s 'Initiate failover from failed_node to backup_node. This function must be called on all nodes, 207s and then waited for the restart of all node daemons.'; 207s COMMENT 207s create or replace function public.failedNode2 (p_failed_node int4, p_backup_node int4, p_ev_seqno int8, p_failed_nodes integer[]) 207s returns bigint 207s as $$ 207s declare 207s v_row record; 207s v_new_event bigint; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s select * into v_row 207s from public.sl_event 207s where ev_origin = p_failed_node 207s and ev_seqno = p_ev_seqno; 207s if not found then 207s raise exception 'Slony-I: event %,% not found', 207s p_failed_node, p_ev_seqno; 207s end if; 207s 207s update public.sl_node set no_failed=true where no_id = ANY 207s (p_failed_nodes) and no_failed=false; 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s -- ---- 207s -- Make sure the node daemon will restart 207s -- ---- 207s raise notice 'calling restart node %',p_failed_node; 207s 207s notify "_main_Restart"; 207s 207s select public.createEvent('_main','FAILOVER_NODE', 207s p_failed_node::text,p_ev_seqno::text, 207s array_to_string(p_failed_nodes,',')) 207s into v_new_event; 207s 207s 207s return v_new_event; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.failedNode2 (p_failed_node int4, p_backup_node int4, p_ev_seqno int8,p_failed_nodes integer[] ) is 207s 'FUNCTION failedNode2 (failed_node, backup_node, set_id, ev_seqno, ev_seqfake,p_failed_nodes) 207s 207s On the node that has the highest sequence number of the failed node, 207s fake the FAILOVER_SET event.'; 207s COMMENT 207s create or replace function public.failedNode3 (p_failed_node int4, p_backup_node int4,p_seq_no bigint) 207s returns int4 207s as $$ 207s declare 207s 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s perform public.failoverSet_int(p_failed_node, 207s p_backup_node,p_seq_no); 207s 207s notify "_main_Restart"; 207s return 0; 207s end; 207s $$ language plpgsql; 207s NOTICE: function public.clonenodeprepare(int4,int4,text) does not exist, skipping 207s CREATE FUNCTION 207s create or replace function public.failoverSet_int (p_failed_node int4, p_backup_node int4,p_last_seqno bigint) 207s returns int4 207s as $$ 207s declare 207s v_row record; 207s v_last_sync int8; 207s v_set int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s SELECT max(ev_seqno) into v_last_sync FROM public.sl_event where 207s ev_origin=p_failed_node; 207s if v_last_sync > p_last_seqno then 207s -- this node is ahead of the last sequence number from the 207s -- failed node that the backup node has. 207s -- this node must unsubscribe from all sets from the origin. 207s for v_set in select set_id from public.sl_set where 207s set_origin=p_failed_node 207s loop 207s raise warning 'Slony is dropping the subscription of set % found sync %s bigger than %s ' 207s , v_set, v_last_sync::text, p_last_seqno::text; 207s perform public.unsubscribeSet(v_set, 207s public.getLocalNodeId('_main'), 207s true); 207s end loop; 207s delete from public.sl_event where ev_origin=p_failed_node 207s and ev_seqno > p_last_seqno; 207s end if; 207s -- ---- 207s -- Change the origin of the set now to the backup node. 207s -- On the backup node this includes changing all the 207s -- trigger and protection stuff 207s for v_set in select set_id from public.sl_set where 207s set_origin=p_failed_node 207s loop 207s -- ---- 207s if p_backup_node = public.getLocalNodeId('_main') then 207s delete from public.sl_setsync 207s where ssy_setid = v_set; 207s delete from public.sl_subscribe 207s where sub_set = v_set 207s and sub_receiver = p_backup_node; 207s update public.sl_set 207s set set_origin = p_backup_node 207s where set_id = v_set; 207s update public.sl_subscribe 207s set sub_provider=p_backup_node 207s FROM public.sl_node receive_node 207s where sub_set = v_set 207s and sub_provider=p_failed_node 207s and sub_receiver=receive_node.no_id 207s and receive_node.no_failed=false; 207s 207s for v_row in select * from public.sl_table 207s where tab_set = v_set 207s order by tab_id 207s loop 207s perform public.alterTableConfigureTriggers(v_row.tab_id); 207s end loop; 207s else 207s raise notice 'deleting from sl_subscribe all rows with receiver %', 207s p_backup_node; 207s 207s delete from public.sl_subscribe 207s where sub_set = v_set 207s and sub_receiver = p_backup_node; 207s 207s update public.sl_subscribe 207s set sub_provider=p_backup_node 207s FROM public.sl_node receive_node 207s where sub_set = v_set 207s and sub_provider=p_failed_node 207s and sub_provider=p_failed_node 207s and sub_receiver=receive_node.no_id 207s and receive_node.no_failed=false; 207s update public.sl_set 207s set set_origin = p_backup_node 207s where set_id = v_set; 207s -- ---- 207s -- If we are a subscriber of the set ourself, change our 207s -- setsync status to reflect the new set origin. 207s -- ---- 207s if exists (select true from public.sl_subscribe 207s where sub_set = v_set 207s and sub_receiver = public.getLocalNodeId( 207s '_main')) 207s then 207s delete from public.sl_setsync 207s where ssy_setid = v_set; 207s 207s select coalesce(max(ev_seqno), 0) into v_last_sync 207s from public.sl_event 207s where ev_origin = p_backup_node 207s and ev_type = 'SYNC'; 207s if v_last_sync > 0 then 207s insert into public.sl_setsync 207s (ssy_setid, ssy_origin, ssy_seqno, 207s ssy_snapshot, ssy_action_list) 207s select v_set, p_backup_node, v_last_sync, 207s ev_snapshot, NULL 207s from public.sl_event 207s where ev_origin = p_backup_node 207s and ev_seqno = v_last_sync; 207s else 207s insert into public.sl_setsync 207s (ssy_setid, ssy_origin, ssy_seqno, 207s ssy_snapshot, ssy_action_list) 207s values (v_set, p_backup_node, '0', 207s '1:1:', NULL); 207s end if; 207s end if; 207s end if; 207s end loop; 207s 207s --If there are any subscriptions with 207s --the failed_node being the provider then 207s --we want to redirect those subscriptions 207s --to come from the backup node. 207s -- 207s -- The backup node should be a valid 207s -- provider for all subscriptions served 207s -- by the failed node. (otherwise it 207s -- wouldn't be a allowable backup node). 207s -- delete from public.sl_subscribe 207s -- where sub_receiver=p_backup_node; 207s 207s update public.sl_subscribe 207s set sub_provider=p_backup_node 207s from public.sl_node 207s where sub_provider=p_failed_node 207s and sl_node.no_id=sub_receiver 207s and sl_node.no_failed=false 207s and sub_receiver<>p_backup_node; 207s 207s update public.sl_subscribe 207s set sub_provider=(select set_origin from 207s public.sl_set where set_id= 207s sub_set) 207s where sub_provider=p_failed_node 207s and sub_receiver=p_backup_node; 207s 207s update public.sl_node 207s set no_active=false WHERE 207s no_id=p_failed_node; 207s 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s 207s return p_failed_node; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.failoverSet_int (p_failed_node int4, p_backup_node int4,p_seqno bigint) is 207s 'FUNCTION failoverSet_int (failed_node, backup_node, set_id, wait_seqno) 207s 207s Finish failover for one set.'; 207s COMMENT 207s create or replace function public.uninstallNode () 207s returns int4 207s as $$ 207s declare 207s v_tab_row record; 207s begin 207s raise notice 'Slony-I: Please drop schema "_main"'; 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.uninstallNode() is 207s 'Reset the whole database to standalone by removing the whole 207s replication system.'; 207s COMMENT 207s DROP FUNCTION IF EXISTS public.cloneNodePrepare(int4,int4,text); 207s DROP FUNCTION 207s create or replace function public.cloneNodePrepare (p_no_id int4, p_no_provider int4, p_no_comment text) 207s returns bigint 207s as $$ 207s begin 207s perform public.cloneNodePrepare_int (p_no_id, p_no_provider, p_no_comment); 207s return public.createEvent('_main', 'CLONE_NODE', 207s p_no_id::text, p_no_provider::text, 207s p_no_comment::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.cloneNodePrepare(p_no_id int4, p_no_provider int4, p_no_comment text) is 207s 'Prepare for cloning a node.'; 207s COMMENT 207s create or replace function public.cloneNodePrepare_int (p_no_id int4, p_no_provider int4, p_no_comment text) 207s returns int4 207s as $$ 207s declare 207s v_dummy int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s update public.sl_node set 207s no_active = np.no_active, 207s no_comment = np.no_comment, 207s no_failed = np.no_failed 207s from public.sl_node np 207s where np.no_id = p_no_provider 207s and sl_node.no_id = p_no_id; 207s if not found then 207s insert into public.sl_node 207s (no_id, no_active, no_comment,no_failed) 207s select p_no_id, no_active, p_no_comment, no_failed 207s from public.sl_node 207s where no_id = p_no_provider; 207s end if; 207s 207s insert into public.sl_path 207s (pa_server, pa_client, pa_conninfo, pa_connretry) 207s select pa_server, p_no_id, '', pa_connretry 207s from public.sl_path 207s where pa_client = p_no_provider 207s and (pa_server, p_no_id) not in (select pa_server, pa_client 207s from public.sl_path); 207s 207s insert into public.sl_path 207s (pa_server, pa_client, pa_conninfo, pa_connretry) 207s select p_no_id, pa_client, '', pa_connretry 207s from public.sl_path 207s where pa_server = p_no_provider 207s and (p_no_id, pa_client) not in (select pa_server, pa_client 207s from public.sl_path); 207s 207s insert into public.sl_subscribe 207s (sub_set, sub_provider, sub_receiver, sub_forward, sub_active) 207s select sub_set, sub_provider, p_no_id, sub_forward, sub_active 207s from public.sl_subscribe 207s where sub_receiver = p_no_provider; 207s 207s insert into public.sl_confirm 207s (con_origin, con_received, con_seqno, con_timestamp) 207s select con_origin, p_no_id, con_seqno, con_timestamp 207s from public.sl_confirm 207s where con_received = p_no_provider; 207s 207s perform public.RebuildListenEntries(); 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.cloneNodePrepare_int(p_no_id int4, p_no_provider int4, p_no_comment text) is 207s 'Internal part of cloneNodePrepare().'; 207s COMMENT 207s create or replace function public.cloneNodeFinish (p_no_id int4, p_no_provider int4) 207s returns int4 207s as $$ 207s declare 207s v_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s perform "pg_catalog".setval('public.sl_local_node_id', p_no_id); 207s perform public.resetSession(); 207s for v_row in select sub_set from public.sl_subscribe 207s where sub_receiver = p_no_id 207s loop 207s perform public.updateReloid(v_row.sub_set, p_no_id); 207s end loop; 207s 207s perform public.RebuildListenEntries(); 207s 207s delete from public.sl_confirm 207s where con_received = p_no_id; 207s insert into public.sl_confirm 207s (con_origin, con_received, con_seqno, con_timestamp) 207s select con_origin, p_no_id, con_seqno, con_timestamp 207s from public.sl_confirm 207s where con_received = p_no_provider; 207s insert into public.sl_confirm 207s (con_origin, con_received, con_seqno, con_timestamp) 207s select p_no_provider, p_no_id, 207s (select max(ev_seqno) from public.sl_event 207s where ev_origin = p_no_provider), current_timestamp; 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.cloneNodeFinish(p_no_id int4, p_no_provider int4) is 207s 'Internal part of cloneNodePrepare().'; 207s COMMENT 207s create or replace function public.storePath (p_pa_server int4, p_pa_client int4, p_pa_conninfo text, p_pa_connretry int4) 207s returns bigint 207s as $$ 207s begin 207s perform public.storePath_int(p_pa_server, p_pa_client, 207s p_pa_conninfo, p_pa_connretry); 207s return public.createEvent('_main', 'STORE_PATH', 207s p_pa_server::text, p_pa_client::text, 207s p_pa_conninfo::text, p_pa_connretry::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.storePath (p_pa_server int4, p_pa_client int4, p_pa_conninfo text, p_pa_connretry int4) is 207s 'FUNCTION storePath (pa_server, pa_client, pa_conninfo, pa_connretry) 207s 207s Generate the STORE_PATH event indicating that node pa_client can 207s access node pa_server using DSN pa_conninfo'; 207s COMMENT 207s create or replace function public.storePath_int (p_pa_server int4, p_pa_client int4, p_pa_conninfo text, p_pa_connretry int4) 207s returns int4 207s as $$ 207s declare 207s v_dummy int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check if the path already exists 207s -- ---- 207s select 1 into v_dummy 207s from public.sl_path 207s where pa_server = p_pa_server 207s and pa_client = p_pa_client 207s for update; 207s if found then 207s -- ---- 207s -- Path exists, update pa_conninfo 207s -- ---- 207s update public.sl_path 207s set pa_conninfo = p_pa_conninfo, 207s pa_connretry = p_pa_connretry 207s where pa_server = p_pa_server 207s and pa_client = p_pa_client; 207s else 207s -- ---- 207s -- New path 207s -- 207s -- In case we receive STORE_PATH events before we know 207s -- about the nodes involved in this, we generate those nodes 207s -- as pending. 207s -- ---- 207s if not exists (select 1 from public.sl_node 207s where no_id = p_pa_server) then 207s perform public.storeNode_int (p_pa_server, ''); 207s end if; 207s if not exists (select 1 from public.sl_node 207s where no_id = p_pa_client) then 207s perform public.storeNode_int (p_pa_client, ''); 207s end if; 207s insert into public.sl_path 207s (pa_server, pa_client, pa_conninfo, pa_connretry) values 207s (p_pa_server, p_pa_client, p_pa_conninfo, p_pa_connretry); 207s end if; 207s 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.storePath_int (p_pa_server int4, p_pa_client int4, p_pa_conninfo text, p_pa_connretry int4) is 207s 'FUNCTION storePath (pa_server, pa_client, pa_conninfo, pa_connretry) 207s 207s Process the STORE_PATH event indicating that node pa_client can 207s access node pa_server using DSN pa_conninfo'; 207s COMMENT 207s create or replace function public.dropPath (p_pa_server int4, p_pa_client int4) 207s returns bigint 207s as $$ 207s declare 207s v_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- There should be no existing subscriptions. Auto unsubscribing 207s -- is considered too dangerous. 207s -- ---- 207s for v_row in select sub_set, sub_provider, sub_receiver 207s from public.sl_subscribe 207s where sub_provider = p_pa_server 207s and sub_receiver = p_pa_client 207s loop 207s raise exception 207s 'Slony-I: Path cannot be dropped, subscription of set % needs it', 207s v_row.sub_set; 207s end loop; 207s 207s -- ---- 207s -- Drop all sl_listen entries that depend on this path 207s -- ---- 207s for v_row in select li_origin, li_provider, li_receiver 207s from public.sl_listen 207s where li_provider = p_pa_server 207s and li_receiver = p_pa_client 207s loop 207s perform public.dropListen( 207s v_row.li_origin, v_row.li_provider, v_row.li_receiver); 207s end loop; 207s 207s -- ---- 207s -- Now drop the path and create the event 207s -- ---- 207s perform public.dropPath_int(p_pa_server, p_pa_client); 207s 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s return public.createEvent ('_main', 'DROP_PATH', 207s p_pa_server::text, p_pa_client::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropPath (p_pa_server int4, p_pa_client int4) is 207s 'Generate DROP_PATH event to drop path from pa_server to pa_client'; 207s COMMENT 207s create or replace function public.dropPath_int (p_pa_server int4, p_pa_client int4) 207s returns int4 207s as $$ 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Remove any dangling sl_listen entries with the server 207s -- as provider and the client as receiver. This must have 207s -- been cleared out before, but obviously was not. 207s -- ---- 207s delete from public.sl_listen 207s where li_provider = p_pa_server 207s and li_receiver = p_pa_client; 207s 207s delete from public.sl_path 207s where pa_server = p_pa_server 207s and pa_client = p_pa_client; 207s 207s if found then 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s return 1; 207s else 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s return 0; 207s end if; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropPath_int (p_pa_server int4, p_pa_client int4) is 207s 'Process DROP_PATH event to drop path from pa_server to pa_client'; 207s COMMENT 207s create or replace function public.storeListen (p_origin int4, p_provider int4, p_receiver int4) 207s returns bigint 207s as $$ 207s begin 207s perform public.storeListen_int (p_origin, p_provider, p_receiver); 207s return public.createEvent ('_main', 'STORE_LISTEN', 207s p_origin::text, p_provider::text, p_receiver::text); 207s end; 207s $$ language plpgsql 207s called on null input; 207s CREATE FUNCTION 207s comment on function public.storeListen(p_origin int4, p_provider int4, p_receiver int4) is 207s 'FUNCTION storeListen (li_origin, li_provider, li_receiver) 207s 207s generate STORE_LISTEN event, indicating that receiver node li_receiver 207s listens to node li_provider in order to get messages coming from node 207s li_origin.'; 207s COMMENT 207s create or replace function public.storeListen_int (p_li_origin int4, p_li_provider int4, p_li_receiver int4) 207s returns int4 207s as $$ 207s declare 207s v_exists int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s select 1 into v_exists 207s from public.sl_listen 207s where li_origin = p_li_origin 207s and li_provider = p_li_provider 207s and li_receiver = p_li_receiver; 207s if not found then 207s -- ---- 207s -- In case we receive STORE_LISTEN events before we know 207s -- about the nodes involved in this, we generate those nodes 207s -- as pending. 207s -- ---- 207s if not exists (select 1 from public.sl_node 207s where no_id = p_li_origin) then 207s perform public.storeNode_int (p_li_origin, ''); 207s end if; 207s if not exists (select 1 from public.sl_node 207s where no_id = p_li_provider) then 207s perform public.storeNode_int (p_li_provider, ''); 207s end if; 207s if not exists (select 1 from public.sl_node 207s where no_id = p_li_receiver) then 207s perform public.storeNode_int (p_li_receiver, ''); 207s end if; 207s 207s insert into public.sl_listen 207s (li_origin, li_provider, li_receiver) values 207s (p_li_origin, p_li_provider, p_li_receiver); 207s end if; 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.storeListen_int(p_li_origin int4, p_li_provider int4, p_li_receiver int4) is 207s 'FUNCTION storeListen_int (li_origin, li_provider, li_receiver) 207s 207s Process STORE_LISTEN event, indicating that receiver node li_receiver 207s listens to node li_provider in order to get messages coming from node 207s li_origin.'; 207s COMMENT 207s create or replace function public.dropListen (p_li_origin int4, p_li_provider int4, p_li_receiver int4) 207s returns bigint 207s as $$ 207s begin 207s perform public.dropListen_int(p_li_origin, 207s p_li_provider, p_li_receiver); 207s 207s return public.createEvent ('_main', 'DROP_LISTEN', 207s p_li_origin::text, p_li_provider::text, p_li_receiver::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropListen(p_li_origin int4, p_li_provider int4, p_li_receiver int4) is 207s 'dropListen (li_origin, li_provider, li_receiver) 207s 207s Generate the DROP_LISTEN event.'; 207s COMMENT 207s create or replace function public.dropListen_int (p_li_origin int4, p_li_provider int4, p_li_receiver int4) 207s returns int4 207s as $$ 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s delete from public.sl_listen 207s where li_origin = p_li_origin 207s and li_provider = p_li_provider 207s and li_receiver = p_li_receiver; 207s if found then 207s return 1; 207s else 207s return 0; 207s end if; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropListen_int(p_li_origin int4, p_li_provider int4, p_li_receiver int4) is 207s 'dropListen (li_origin, li_provider, li_receiver) 207s 207s Process the DROP_LISTEN event, deleting the sl_listen entry for 207s the indicated (origin,provider,receiver) combination.'; 207s COMMENT 207s create or replace function public.storeSet (p_set_id int4, p_set_comment text) 207s returns bigint 207s as $$ 207s declare 207s v_local_node_id int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s v_local_node_id := public.getLocalNodeId('_main'); 207s 207s insert into public.sl_set 207s (set_id, set_origin, set_comment) values 207s (p_set_id, v_local_node_id, p_set_comment); 207s 207s return public.createEvent('_main', 'STORE_SET', 207s p_set_id::text, v_local_node_id::text, p_set_comment::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.storeSet(p_set_id int4, p_set_comment text) is 207s 'Generate STORE_SET event for set set_id with human readable comment set_comment'; 207s COMMENT 207s create or replace function public.storeSet_int (p_set_id int4, p_set_origin int4, p_set_comment text) 207s returns int4 207s as $$ 207s declare 207s v_dummy int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s select 1 into v_dummy 207s from public.sl_set 207s where set_id = p_set_id 207s for update; 207s if found then 207s update public.sl_set 207s set set_comment = p_set_comment 207s where set_id = p_set_id; 207s else 207s if not exists (select 1 from public.sl_node 207s where no_id = p_set_origin) then 207s perform public.storeNode_int (p_set_origin, ''); 207s end if; 207s insert into public.sl_set 207s (set_id, set_origin, set_comment) values 207s (p_set_id, p_set_origin, p_set_comment); 207s end if; 207s 207s -- Run addPartialLogIndices() to try to add indices to unused sl_log_? table 207s perform public.addPartialLogIndices(); 207s 207s return p_set_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.storeSet_int(p_set_id int4, p_set_origin int4, p_set_comment text) is 207s 'storeSet_int (set_id, set_origin, set_comment) 207s 207s Process the STORE_SET event, indicating the new set with given ID, 207s origin node, and human readable comment.'; 207s COMMENT 207s create or replace function public.lockSet (p_set_id int4) 207s returns int4 207s as $$ 207s declare 207s v_local_node_id int4; 207s v_set_row record; 207s v_tab_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that the set exists and that we are the origin 207s -- and that it is not already locked. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select * into v_set_row from public.sl_set 207s where set_id = p_set_id 207s for update; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_set_id; 207s end if; 207s if v_set_row.set_origin <> v_local_node_id then 207s raise exception 'Slony-I: set % does not originate on local node', 207s p_set_id; 207s end if; 207s if v_set_row.set_locked notnull then 207s raise exception 'Slony-I: set % is already locked', p_set_id; 207s end if; 207s 207s -- ---- 207s -- Place the lockedSet trigger on all tables in the set. 207s -- ---- 207s for v_tab_row in select T.tab_id, 207s public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) as tab_fqname 207s from public.sl_table T, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where T.tab_set = p_set_id 207s and T.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid 207s order by tab_id 207s loop 207s execute 'create trigger "_main_lockedset" ' || 207s 'before insert or update or delete on ' || 207s v_tab_row.tab_fqname || ' for each row execute procedure 207s public.lockedSet (''_main'');'; 207s end loop; 207s 207s -- ---- 207s -- Remember our snapshots xmax as for the set locking 207s -- ---- 207s update public.sl_set 207s set set_locked = "pg_catalog".txid_snapshot_xmax("pg_catalog".txid_current_snapshot()) 207s where set_id = p_set_id; 207s 207s return p_set_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.lockSet(p_set_id int4) is 207s 'lockSet(set_id) 207s 207s Add a special trigger to all tables of a set that disables access to 207s it.'; 207s COMMENT 207s create or replace function public.unlockSet (p_set_id int4) 207s returns int4 207s as $$ 207s declare 207s v_local_node_id int4; 207s v_set_row record; 207s v_tab_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that the set exists and that we are the origin 207s -- and that it is not already locked. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select * into v_set_row from public.sl_set 207s where set_id = p_set_id 207s for update; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_set_id; 207s end if; 207s if v_set_row.set_origin <> v_local_node_id then 207s raise exception 'Slony-I: set % does not originate on local node', 207s p_set_id; 207s end if; 207s if v_set_row.set_locked isnull then 207s raise exception 'Slony-I: set % is not locked', p_set_id; 207s end if; 207s 207s -- ---- 207s -- Drop the lockedSet trigger from all tables in the set. 207s -- ---- 207s for v_tab_row in select T.tab_id, 207s public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) as tab_fqname 207s from public.sl_table T, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where T.tab_set = p_set_id 207s and T.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid 207s order by tab_id 207s loop 207s execute 'drop trigger "_main_lockedset" ' || 207s 'on ' || v_tab_row.tab_fqname; 207s end loop; 207s 207s -- ---- 207s -- Clear out the set_locked field 207s -- ---- 207s update public.sl_set 207s set set_locked = NULL 207s where set_id = p_set_id; 207s 207s return p_set_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.unlockSet(p_set_id int4) is 207s 'Remove the special trigger from all tables of a set that disables access to it.'; 207s COMMENT 207s create or replace function public.moveSet (p_set_id int4, p_new_origin int4) 207s returns bigint 207s as $$ 207s declare 207s v_local_node_id int4; 207s v_set_row record; 207s v_sub_row record; 207s v_sync_seqno int8; 207s v_lv_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that the set is locked and that this locking 207s -- happened long enough ago. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select * into v_set_row from public.sl_set 207s where set_id = p_set_id 207s for update; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_set_id; 207s end if; 207s if v_set_row.set_origin <> v_local_node_id then 207s raise exception 'Slony-I: set % does not originate on local node', 207s p_set_id; 207s end if; 207s if v_set_row.set_locked isnull then 207s raise exception 'Slony-I: set % is not locked', p_set_id; 207s end if; 207s if v_set_row.set_locked > "pg_catalog".txid_snapshot_xmin("pg_catalog".txid_current_snapshot()) then 207s raise exception 'Slony-I: cannot move set % yet, transactions < % are still in progress', 207s p_set_id, v_set_row.set_locked; 207s end if; 207s 207s -- ---- 207s -- Unlock the set 207s -- ---- 207s perform public.unlockSet(p_set_id); 207s 207s -- ---- 207s -- Check that the new_origin is an active subscriber of the set 207s -- ---- 207s select * into v_sub_row from public.sl_subscribe 207s where sub_set = p_set_id 207s and sub_receiver = p_new_origin; 207s if not found then 207s raise exception 'Slony-I: set % is not subscribed by node %', 207s p_set_id, p_new_origin; 207s end if; 207s if not v_sub_row.sub_active then 207s raise exception 'Slony-I: subsctiption of node % for set % is inactive', 207s p_new_origin, p_set_id; 207s end if; 207s 207s -- ---- 207s -- Reconfigure everything 207s -- ---- 207s perform public.moveSet_int(p_set_id, v_local_node_id, 207s p_new_origin, 0); 207s 207s perform public.RebuildListenEntries(); 207s 207s -- ---- 207s -- At this time we hold access exclusive locks for every table 207s -- in the set. But we did move the set to the new origin, so the 207s -- createEvent() we are doing now will not record the sequences. 207s -- ---- 207s v_sync_seqno := public.createEvent('_main', 'SYNC'); 207s insert into public.sl_seqlog 207s (seql_seqid, seql_origin, seql_ev_seqno, seql_last_value) 207s select seq_id, v_local_node_id, v_sync_seqno, seq_last_value 207s from public.sl_seqlastvalue 207s where seq_set = p_set_id; 207s 207s -- ---- 207s -- Finally we generate the real event 207s -- ---- 207s return public.createEvent('_main', 'MOVE_SET', 207s p_set_id::text, v_local_node_id::text, p_new_origin::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.moveSet(p_set_id int4, p_new_origin int4) is 207s 'moveSet(set_id, new_origin) 207s 207s Generate MOVE_SET event to request that the origin for set set_id be moved to node new_origin'; 207s COMMENT 207s create or replace function public.moveSet_int (p_set_id int4, p_old_origin int4, p_new_origin int4, p_wait_seqno int8) 207s returns int4 207s as $$ 207s declare 207s v_local_node_id int4; 207s v_tab_row record; 207s v_sub_row record; 207s v_sub_node int4; 207s v_sub_last int4; 207s v_sub_next int4; 207s v_last_sync int8; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Get our local node ID 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s 207s -- On the new origin, raise an event - ACCEPT_SET 207s if v_local_node_id = p_new_origin then 207s -- Create a SYNC event as well so that the ACCEPT_SET has 207s -- the same snapshot as the last SYNC generated by the new 207s -- origin. This snapshot will be used by other nodes to 207s -- finalize the setsync status. 207s perform public.createEvent('_main', 'SYNC', NULL); 207s perform public.createEvent('_main', 'ACCEPT_SET', 207s p_set_id::text, p_old_origin::text, 207s p_new_origin::text, p_wait_seqno::text); 207s end if; 207s 207s -- ---- 207s -- Next we have to reverse the subscription path 207s -- ---- 207s v_sub_last = p_new_origin; 207s select sub_provider into v_sub_node 207s from public.sl_subscribe 207s where sub_set = p_set_id 207s and sub_receiver = p_new_origin; 207s if not found then 207s raise exception 'Slony-I: subscription path broken in moveSet_int'; 207s end if; 207s while v_sub_node <> p_old_origin loop 207s -- ---- 207s -- Tracing node by node, the old receiver is now in 207s -- v_sub_last and the old provider is in v_sub_node. 207s -- ---- 207s 207s -- ---- 207s -- Get the current provider of this node as next 207s -- and change the provider to the previous one in 207s -- the reverse chain. 207s -- ---- 207s select sub_provider into v_sub_next 207s from public.sl_subscribe 207s where sub_set = p_set_id 207s and sub_receiver = v_sub_node 207s for update; 207s if not found then 207s raise exception 'Slony-I: subscription path broken in moveSet_int'; 207s end if; 207s update public.sl_subscribe 207s set sub_provider = v_sub_last 207s where sub_set = p_set_id 207s and sub_receiver = v_sub_node 207s and sub_receiver <> v_sub_last; 207s 207s v_sub_last = v_sub_node; 207s v_sub_node = v_sub_next; 207s end loop; 207s 207s -- ---- 207s -- This includes creating a subscription for the old origin 207s -- ---- 207s insert into public.sl_subscribe 207s (sub_set, sub_provider, sub_receiver, 207s sub_forward, sub_active) 207s values (p_set_id, v_sub_last, p_old_origin, true, true); 207s if v_local_node_id = p_old_origin then 207s select coalesce(max(ev_seqno), 0) into v_last_sync 207s from public.sl_event 207s where ev_origin = p_new_origin 207s and ev_type = 'SYNC'; 207s if v_last_sync > 0 then 207s insert into public.sl_setsync 207s (ssy_setid, ssy_origin, ssy_seqno, 207s ssy_snapshot, ssy_action_list) 207s select p_set_id, p_new_origin, v_last_sync, 207s ev_snapshot, NULL 207s from public.sl_event 207s where ev_origin = p_new_origin 207s and ev_seqno = v_last_sync; 207s else 207s insert into public.sl_setsync 207s (ssy_setid, ssy_origin, ssy_seqno, 207s ssy_snapshot, ssy_action_list) 207s values (p_set_id, p_new_origin, '0', 207s '1:1:', NULL); 207s end if; 207s end if; 207s 207s -- ---- 207s -- Now change the ownership of the set. 207s -- ---- 207s update public.sl_set 207s set set_origin = p_new_origin 207s where set_id = p_set_id; 207s 207s -- ---- 207s -- On the new origin, delete the obsolete setsync information 207s -- and the subscription. 207s -- ---- 207s if v_local_node_id = p_new_origin then 207s delete from public.sl_setsync 207s where ssy_setid = p_set_id; 207s else 207s if v_local_node_id <> p_old_origin then 207s -- 207s -- On every other node, change the setsync so that it will 207s -- pick up from the new origins last known sync. 207s -- 207s delete from public.sl_setsync 207s where ssy_setid = p_set_id; 207s select coalesce(max(ev_seqno), 0) into v_last_sync 207s from public.sl_event 207s where ev_origin = p_new_origin 207s and ev_type = 'SYNC'; 207s if v_last_sync > 0 then 207s insert into public.sl_setsync 207s (ssy_setid, ssy_origin, ssy_seqno, 207s ssy_snapshot, ssy_action_list) 207s select p_set_id, p_new_origin, v_last_sync, 207s ev_snapshot, NULL 207s from public.sl_event 207s where ev_origin = p_new_origin 207s and ev_seqno = v_last_sync; 207s else 207s insert into public.sl_setsync 207s (ssy_setid, ssy_origin, ssy_seqno, 207s ssy_snapshot, ssy_action_list) 207s values (p_set_id, p_new_origin, 207s '0', '1:1:', NULL); 207s end if; 207s end if; 207s end if; 207s delete from public.sl_subscribe 207s where sub_set = p_set_id 207s and sub_receiver = p_new_origin; 207s 207s -- Regenerate sl_listen since we revised the subscriptions 207s perform public.RebuildListenEntries(); 207s 207s -- Run addPartialLogIndices() to try to add indices to unused sl_log_? table 207s perform public.addPartialLogIndices(); 207s 207s -- ---- 207s -- If we are the new or old origin, we have to 207s -- adjust the log and deny access trigger configuration. 207s -- ---- 207s if v_local_node_id = p_old_origin or v_local_node_id = p_new_origin then 207s for v_tab_row in select tab_id from public.sl_table 207s where tab_set = p_set_id 207s order by tab_id 207s loop 207s perform public.alterTableConfigureTriggers(v_tab_row.tab_id); 207s end loop; 207s end if; 207s 207s return p_set_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.moveSet_int(p_set_id int4, p_old_origin int4, p_new_origin int4, p_wait_seqno int8) is 207s 'moveSet(set_id, old_origin, new_origin, wait_seqno) 207s 207s Process MOVE_SET event to request that the origin for set set_id be 207s moved from old_origin to node new_origin'; 207s COMMENT 207s create or replace function public.dropSet (p_set_id int4) 207s returns bigint 207s as $$ 207s declare 207s v_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that the set exists and originates here 207s -- ---- 207s select set_origin into v_origin from public.sl_set 207s where set_id = p_set_id; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_set_id; 207s end if; 207s if v_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: set % does not originate on local node', 207s p_set_id; 207s end if; 207s 207s -- ---- 207s -- Call the internal drop set functionality and generate the event 207s -- ---- 207s perform public.dropSet_int(p_set_id); 207s return public.createEvent('_main', 'DROP_SET', 207s p_set_id::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropSet(p_set_id int4) is 207s 'Generate DROP_SET event to drop replication of set set_id'; 207s COMMENT 207s create or replace function public.dropSet_int (p_set_id int4) 207s returns int4 207s as $$ 207s declare 207s v_tab_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Restore all tables original triggers and rules and remove 207s -- our replication stuff. 207s -- ---- 207s for v_tab_row in select tab_id from public.sl_table 207s where tab_set = p_set_id 207s order by tab_id 207s loop 207s perform public.alterTableDropTriggers(v_tab_row.tab_id); 207s end loop; 207s 207s -- ---- 207s -- Remove all traces of the set configuration 207s -- ---- 207s delete from public.sl_sequence 207s where seq_set = p_set_id; 207s delete from public.sl_table 207s where tab_set = p_set_id; 207s delete from public.sl_subscribe 207s where sub_set = p_set_id; 207s delete from public.sl_setsync 207s where ssy_setid = p_set_id; 207s delete from public.sl_set 207s where set_id = p_set_id; 207s 207s -- Regenerate sl_listen since we revised the subscriptions 207s perform public.RebuildListenEntries(); 207s 207s -- Run addPartialLogIndices() to try to add indices to unused sl_log_? table 207s perform public.addPartialLogIndices(); 207s 207s return p_set_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.dropSet(p_set_id int4) is 207s 'Process DROP_SET event to drop replication of set set_id. This involves: 207s - Removing log and deny access triggers 207s - Removing all traces of the set configuration, including sequences, tables, subscribers, syncs, and the set itself'; 207s COMMENT 207s create or replace function public.mergeSet (p_set_id int4, p_add_id int4) 207s returns bigint 207s as $$ 207s declare 207s v_origin int4; 207s in_progress boolean; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that both sets exist and originate here 207s -- ---- 207s if p_set_id = p_add_id then 207s raise exception 'Slony-I: merged set ids cannot be identical'; 207s end if; 207s select set_origin into v_origin from public.sl_set 207s where set_id = p_set_id; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_set_id; 207s end if; 207s if v_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: set % does not originate on local node', 207s p_set_id; 207s end if; 207s 207s select set_origin into v_origin from public.sl_set 207s where set_id = p_add_id; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_add_id; 207s end if; 207s if v_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: set % does not originate on local node', 207s p_add_id; 207s end if; 207s 207s -- ---- 207s -- Check that both sets are subscribed by the same set of nodes 207s -- ---- 207s if exists (select true from public.sl_subscribe SUB1 207s where SUB1.sub_set = p_set_id 207s and SUB1.sub_receiver not in (select SUB2.sub_receiver 207s from public.sl_subscribe SUB2 207s where SUB2.sub_set = p_add_id)) 207s then 207s raise exception 'Slony-I: subscriber lists of set % and % are different', 207s p_set_id, p_add_id; 207s end if; 207s 207s if exists (select true from public.sl_subscribe SUB1 207s where SUB1.sub_set = p_add_id 207s and SUB1.sub_receiver not in (select SUB2.sub_receiver 207s from public.sl_subscribe SUB2 207s where SUB2.sub_set = p_set_id)) 207s then 207s raise exception 'Slony-I: subscriber lists of set % and % are different', 207s p_add_id, p_set_id; 207s end if; 207s 207s -- ---- 207s -- Check that all ENABLE_SUBSCRIPTION events for the set are confirmed 207s -- ---- 207s select public.isSubscriptionInProgress(p_add_id) into in_progress ; 207s 207s if in_progress then 207s raise exception 'Slony-I: set % has subscriptions in progress - cannot merge', 207s p_add_id; 207s end if; 207s 207s -- ---- 207s -- Create a SYNC event, merge the sets, create a MERGE_SET event 207s -- ---- 207s perform public.createEvent('_main', 'SYNC', NULL); 207s perform public.mergeSet_int(p_set_id, p_add_id); 207s return public.createEvent('_main', 'MERGE_SET', 207s p_set_id::text, p_add_id::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.mergeSet(p_set_id int4, p_add_id int4) is 207s 'Generate MERGE_SET event to request that sets be merged together. 207s 207s Both sets must exist, and originate on the same node. They must be 207s subscribed by the same set of nodes.'; 207s COMMENT 207s create or replace function public.isSubscriptionInProgress(p_add_id int4) 207s returns boolean 207s as $$ 207s begin 207s if exists (select true from public.sl_event 207s where ev_type = 'ENABLE_SUBSCRIPTION' 207s and ev_data1 = p_add_id::text 207s and ev_seqno > (select max(con_seqno) from public.sl_confirm 207s where con_origin = ev_origin 207s and con_received::text = ev_data3)) 207s then 207s return true; 207s else 207s return false; 207s end if; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.isSubscriptionInProgress(p_add_id int4) is 207s 'Checks to see if a subscription for the indicated set is in progress. 207s Returns true if a subscription is in progress. Otherwise false'; 207s COMMENT 207s create or replace function public.mergeSet_int (p_set_id int4, p_add_id int4) 207s returns int4 207s as $$ 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s update public.sl_sequence 207s set seq_set = p_set_id 207s where seq_set = p_add_id; 207s update public.sl_table 207s set tab_set = p_set_id 207s where tab_set = p_add_id; 207s delete from public.sl_subscribe 207s where sub_set = p_add_id; 207s delete from public.sl_setsync 207s where ssy_setid = p_add_id; 207s delete from public.sl_set 207s where set_id = p_add_id; 207s 207s return p_set_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.mergeSet_int(p_set_id int4, p_add_id int4) is 207s 'mergeSet_int(set_id, add_id) - Perform MERGE_SET event, merging all objects from 207s set add_id into set set_id.'; 207s COMMENT 207s create or replace function public.setAddTable(p_set_id int4, p_tab_id int4, p_fqname text, p_tab_idxname name, p_tab_comment text) 207s returns bigint 207s as $$ 207s declare 207s v_set_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that we are the origin of the set 207s -- ---- 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = p_set_id; 207s if not found then 207s raise exception 'Slony-I: setAddTable(): set % not found', p_set_id; 207s end if; 207s if v_set_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: setAddTable(): set % has remote origin', p_set_id; 207s end if; 207s 207s if exists (select true from public.sl_subscribe 207s where sub_set = p_set_id) 207s then 207s raise exception 'Slony-I: cannot add table to currently subscribed set % - must attach to an unsubscribed set', 207s p_set_id; 207s end if; 207s 207s -- ---- 207s -- Add the table to the set and generate the SET_ADD_TABLE event 207s -- ---- 207s perform public.setAddTable_int(p_set_id, p_tab_id, p_fqname, 207s p_tab_idxname, p_tab_comment); 207s return public.createEvent('_main', 'SET_ADD_TABLE', 207s p_set_id::text, p_tab_id::text, p_fqname::text, 207s p_tab_idxname::text, p_tab_comment::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setAddTable(p_set_id int4, p_tab_id int4, p_fqname text, p_tab_idxname name, p_tab_comment text) is 207s 'setAddTable (set_id, tab_id, tab_fqname, tab_idxname, tab_comment) 207s 207s Add table tab_fqname to replication set on origin node, and generate 207s SET_ADD_TABLE event to allow this to propagate to other nodes. 207s 207s Note that the table id, tab_id, must be unique ACROSS ALL SETS.'; 207s COMMENT 207s create or replace function public.setAddTable_int(p_set_id int4, p_tab_id int4, p_fqname text, p_tab_idxname name, p_tab_comment text) 207s returns int4 207s as $$ 207s declare 207s v_tab_relname name; 207s v_tab_nspname name; 207s v_local_node_id int4; 207s v_set_origin int4; 207s v_sub_provider int4; 207s v_relkind char; 207s v_tab_reloid oid; 207s v_pkcand_nn boolean; 207s v_prec record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- For sets with a remote origin, check that we are subscribed 207s -- to that set. Otherwise we ignore the table because it might 207s -- not even exist in our database. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = p_set_id; 207s if not found then 207s raise exception 'Slony-I: setAddTable_int(): set % not found', 207s p_set_id; 207s end if; 207s if v_set_origin != v_local_node_id then 207s select sub_provider into v_sub_provider 207s from public.sl_subscribe 207s where sub_set = p_set_id 207s and sub_receiver = public.getLocalNodeId('_main'); 207s if not found then 207s return 0; 207s end if; 207s end if; 207s 207s -- ---- 207s -- Get the tables OID and check that it is a real table 207s -- ---- 207s select PGC.oid, PGC.relkind, PGC.relname, PGN.nspname into v_tab_reloid, v_relkind, v_tab_relname, v_tab_nspname 207s from "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where PGC.relnamespace = PGN.oid 207s and public.slon_quote_input(p_fqname) = public.slon_quote_brute(PGN.nspname) || 207s '.' || public.slon_quote_brute(PGC.relname); 207s if not found then 207s raise exception 'Slony-I: setAddTable_int(): table % not found', 207s p_fqname; 207s end if; 207s if v_relkind != 'r' then 207s raise exception 'Slony-I: setAddTable_int(): % is not a regular table', 207s p_fqname; 207s end if; 207s 207s if not exists (select indexrelid 207s from "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGC 207s where PGX.indrelid = v_tab_reloid 207s and PGX.indexrelid = PGC.oid 207s and PGC.relname = p_tab_idxname) 207s then 207s raise exception 'Slony-I: setAddTable_int(): table % has no index %', 207s p_fqname, p_tab_idxname; 207s end if; 207s 207s -- ---- 207s -- Verify that the columns in the PK (or candidate) are not NULLABLE 207s -- ---- 207s 207s v_pkcand_nn := 'f'; 207s for v_prec in select attname from "pg_catalog".pg_attribute where attrelid = 207s (select oid from "pg_catalog".pg_class where oid = v_tab_reloid) 207s and attname in (select attname from "pg_catalog".pg_attribute where 207s attrelid = (select oid from "pg_catalog".pg_class PGC, 207s "pg_catalog".pg_index PGX where 207s PGC.relname = p_tab_idxname and PGX.indexrelid=PGC.oid and 207s PGX.indrelid = v_tab_reloid)) and attnotnull <> 't' 207s loop 207s raise notice 'Slony-I: setAddTable_int: table % PK column % nullable', p_fqname, v_prec.attname; 207s v_pkcand_nn := 't'; 207s end loop; 207s if v_pkcand_nn then 207s raise exception 'Slony-I: setAddTable_int: table % not replicable!', p_fqname; 207s end if; 207s 207s select * into v_prec from public.sl_table where tab_id = p_tab_id; 207s if not found then 207s v_pkcand_nn := 't'; -- No-op -- All is well 207s else 207s raise exception 'Slony-I: setAddTable_int: table id % has already been assigned!', p_tab_id; 207s end if; 207s 207s -- ---- 207s -- Add the table to sl_table and create the trigger on it. 207s -- ---- 207s insert into public.sl_table 207s (tab_id, tab_reloid, tab_relname, tab_nspname, 207s tab_set, tab_idxname, tab_altered, tab_comment) 207s values 207s (p_tab_id, v_tab_reloid, v_tab_relname, v_tab_nspname, 207s p_set_id, p_tab_idxname, false, p_tab_comment); 207s perform public.alterTableAddTriggers(p_tab_id); 207s 207s return p_tab_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setAddTable_int(p_set_id int4, p_tab_id int4, p_fqname text, p_tab_idxname name, p_tab_comment text) is 207s 'setAddTable_int (set_id, tab_id, tab_fqname, tab_idxname, tab_comment) 207s 207s This function processes the SET_ADD_TABLE event on remote nodes, 207s adding a table to replication if the remote node is subscribing to its 207s replication set.'; 207s COMMENT 207s create or replace function public.setDropTable(p_tab_id int4) 207s returns bigint 207s as $$ 207s declare 207s v_set_id int4; 207s v_set_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Determine the set_id 207s -- ---- 207s select tab_set into v_set_id from public.sl_table where tab_id = p_tab_id; 207s 207s -- ---- 207s -- Ensure table exists 207s -- ---- 207s if not found then 207s raise exception 'Slony-I: setDropTable_int(): table % not found', 207s p_tab_id; 207s end if; 207s 207s -- ---- 207s -- Check that we are the origin of the set 207s -- ---- 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = v_set_id; 207s if not found then 207s raise exception 'Slony-I: setDropTable(): set % not found', v_set_id; 207s end if; 207s if v_set_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: setDropTable(): set % has remote origin', v_set_id; 207s end if; 207s 207s -- ---- 207s -- Drop the table from the set and generate the SET_ADD_TABLE event 207s -- ---- 207s perform public.setDropTable_int(p_tab_id); 207s return public.createEvent('_main', 'SET_DROP_TABLE', 207s p_tab_id::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setDropTable(p_tab_id int4) is 207s 'setDropTable (tab_id) 207s 207s Drop table tab_id from set on origin node, and generate SET_DROP_TABLE 207s event to allow this to propagate to other nodes.'; 207s COMMENT 207s create or replace function public.setDropTable_int(p_tab_id int4) 207s returns int4 207s as $$ 207s declare 207s v_set_id int4; 207s v_local_node_id int4; 207s v_set_origin int4; 207s v_sub_provider int4; 207s v_tab_reloid oid; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Determine the set_id 207s -- ---- 207s select tab_set into v_set_id from public.sl_table where tab_id = p_tab_id; 207s 207s -- ---- 207s -- Ensure table exists 207s -- ---- 207s if not found then 207s return 0; 207s end if; 207s 207s -- ---- 207s -- For sets with a remote origin, check that we are subscribed 207s -- to that set. Otherwise we ignore the table because it might 207s -- not even exist in our database. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = v_set_id; 207s if not found then 207s raise exception 'Slony-I: setDropTable_int(): set % not found', 207s v_set_id; 207s end if; 207s if v_set_origin != v_local_node_id then 207s select sub_provider into v_sub_provider 207s from public.sl_subscribe 207s where sub_set = v_set_id 207s and sub_receiver = public.getLocalNodeId('_main'); 207s if not found then 207s return 0; 207s end if; 207s end if; 207s 207s -- ---- 207s -- Drop the table from sl_table and drop trigger from it. 207s -- ---- 207s perform public.alterTableDropTriggers(p_tab_id); 207s delete from public.sl_table where tab_id = p_tab_id; 207s return p_tab_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setDropTable_int(p_tab_id int4) is 207s 'setDropTable_int (tab_id) 207s 207s This function processes the SET_DROP_TABLE event on remote nodes, 207s dropping a table from replication if the remote node is subscribing to 207s its replication set.'; 207s COMMENT 207s create or replace function public.setAddSequence (p_set_id int4, p_seq_id int4, p_fqname text, p_seq_comment text) 207s returns bigint 207s as $$ 207s declare 207s v_set_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that we are the origin of the set 207s -- ---- 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = p_set_id; 207s if not found then 207s raise exception 'Slony-I: setAddSequence(): set % not found', p_set_id; 207s end if; 207s if v_set_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: setAddSequence(): set % has remote origin - submit to origin node', p_set_id; 207s end if; 207s 207s if exists (select true from public.sl_subscribe 207s where sub_set = p_set_id) 207s then 207s raise exception 'Slony-I: cannot add sequence to currently subscribed set %', 207s p_set_id; 207s end if; 207s 207s -- ---- 207s -- Add the sequence to the set and generate the SET_ADD_SEQUENCE event 207s -- ---- 207s perform public.setAddSequence_int(p_set_id, p_seq_id, p_fqname, 207s p_seq_comment); 207s return public.createEvent('_main', 'SET_ADD_SEQUENCE', 207s p_set_id::text, p_seq_id::text, 207s p_fqname::text, p_seq_comment::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setAddSequence (p_set_id int4, p_seq_id int4, p_fqname text, p_seq_comment text) is 207s 'setAddSequence (set_id, seq_id, seq_fqname, seq_comment) 207s 207s On the origin node for set set_id, add sequence seq_fqname to the 207s replication set, and raise SET_ADD_SEQUENCE to cause this to replicate 207s to subscriber nodes.'; 207s COMMENT 207s create or replace function public.setAddSequence_int(p_set_id int4, p_seq_id int4, p_fqname text, p_seq_comment text) 207s returns int4 207s as $$ 207s declare 207s v_local_node_id int4; 207s v_set_origin int4; 207s v_sub_provider int4; 207s v_relkind char; 207s v_seq_reloid oid; 207s v_seq_relname name; 207s v_seq_nspname name; 207s v_sync_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- For sets with a remote origin, check that we are subscribed 207s -- to that set. Otherwise we ignore the sequence because it might 207s -- not even exist in our database. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = p_set_id; 207s if not found then 207s raise exception 'Slony-I: setAddSequence_int(): set % not found', 207s p_set_id; 207s end if; 207s if v_set_origin != v_local_node_id then 207s select sub_provider into v_sub_provider 207s from public.sl_subscribe 207s where sub_set = p_set_id 207s and sub_receiver = public.getLocalNodeId('_main'); 207s if not found then 207s return 0; 207s end if; 207s end if; 207s 207s -- ---- 207s -- Get the sequences OID and check that it is a sequence 207s -- ---- 207s select PGC.oid, PGC.relkind, PGC.relname, PGN.nspname 207s into v_seq_reloid, v_relkind, v_seq_relname, v_seq_nspname 207s from "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where PGC.relnamespace = PGN.oid 207s and public.slon_quote_input(p_fqname) = public.slon_quote_brute(PGN.nspname) || 207s '.' || public.slon_quote_brute(PGC.relname); 207s if not found then 207s raise exception 'Slony-I: setAddSequence_int(): sequence % not found', 207s p_fqname; 207s end if; 207s if v_relkind != 'S' then 207s raise exception 'Slony-I: setAddSequence_int(): % is not a sequence', 207s p_fqname; 207s end if; 207s 207s select 1 into v_sync_row from public.sl_sequence where seq_id = p_seq_id; 207s if not found then 207s v_relkind := 'o'; -- all is OK 207s else 207s raise exception 'Slony-I: setAddSequence_int(): sequence ID % has already been assigned', p_seq_id; 207s end if; 207s 207s -- ---- 207s -- Add the sequence to sl_sequence 207s -- ---- 207s insert into public.sl_sequence 207s (seq_id, seq_reloid, seq_relname, seq_nspname, seq_set, seq_comment) 207s values 207s (p_seq_id, v_seq_reloid, v_seq_relname, v_seq_nspname, p_set_id, p_seq_comment); 207s 207s -- ---- 207s -- On the set origin, fake a sl_seqlog row for the last sync event 207s -- ---- 207s if v_set_origin = v_local_node_id then 207s for v_sync_row in select coalesce (max(ev_seqno), 0) as ev_seqno 207s from public.sl_event 207s where ev_origin = v_local_node_id 207s and ev_type = 'SYNC' 207s loop 207s insert into public.sl_seqlog 207s (seql_seqid, seql_origin, seql_ev_seqno, 207s seql_last_value) values 207s (p_seq_id, v_local_node_id, v_sync_row.ev_seqno, 207s public.sequenceLastValue(p_fqname)); 207s end loop; 207s end if; 207s 207s return p_seq_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setAddSequence_int(p_set_id int4, p_seq_id int4, p_fqname text, p_seq_comment text) is 207s 'setAddSequence_int (set_id, seq_id, seq_fqname, seq_comment) 207s 207s This processes the SET_ADD_SEQUENCE event. On remote nodes that 207s subscribe to set_id, add the sequence to the replication set.'; 207s COMMENT 207s create or replace function public.setDropSequence (p_seq_id int4) 207s returns bigint 207s as $$ 207s declare 207s v_set_id int4; 207s v_set_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Determine set id for this sequence 207s -- ---- 207s select seq_set into v_set_id from public.sl_sequence where seq_id = p_seq_id; 207s 207s -- ---- 207s -- Ensure sequence exists 207s -- ---- 207s if not found then 207s raise exception 'Slony-I: setDropSequence_int(): sequence % not found', 207s p_seq_id; 207s end if; 207s 207s -- ---- 207s -- Check that we are the origin of the set 207s -- ---- 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = v_set_id; 207s if not found then 207s raise exception 'Slony-I: setDropSequence(): set % not found', v_set_id; 207s end if; 207s if v_set_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: setDropSequence(): set % has origin at another node - submit this to that node', v_set_id; 207s end if; 207s 207s -- ---- 207s -- Add the sequence to the set and generate the SET_ADD_SEQUENCE event 207s -- ---- 207s perform public.setDropSequence_int(p_seq_id); 207s return public.createEvent('_main', 'SET_DROP_SEQUENCE', 207s p_seq_id::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setDropSequence (p_seq_id int4) is 207s 'setDropSequence (seq_id) 207s 207s On the origin node for the set, drop sequence seq_id from replication 207s set, and raise SET_DROP_SEQUENCE to cause this to replicate to 207s subscriber nodes.'; 207s COMMENT 207s create or replace function public.setDropSequence_int(p_seq_id int4) 207s returns int4 207s as $$ 207s declare 207s v_set_id int4; 207s v_local_node_id int4; 207s v_set_origin int4; 207s v_sub_provider int4; 207s v_relkind char; 207s v_sync_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Determine set id for this sequence 207s -- ---- 207s select seq_set into v_set_id from public.sl_sequence where seq_id = p_seq_id; 207s 207s -- ---- 207s -- Ensure sequence exists 207s -- ---- 207s if not found then 207s return 0; 207s end if; 207s 207s -- ---- 207s -- For sets with a remote origin, check that we are subscribed 207s -- to that set. Otherwise we ignore the sequence because it might 207s -- not even exist in our database. 207s -- ---- 207s v_local_node_id := public.getLocalNodeId('_main'); 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = v_set_id; 207s if not found then 207s raise exception 'Slony-I: setDropSequence_int(): set % not found', 207s v_set_id; 207s end if; 207s if v_set_origin != v_local_node_id then 207s select sub_provider into v_sub_provider 207s from public.sl_subscribe 207s where sub_set = v_set_id 207s and sub_receiver = public.getLocalNodeId('_main'); 207s if not found then 207s return 0; 207s end if; 207s end if; 207s 207s -- ---- 207s -- drop the sequence from sl_sequence, sl_seqlog 207s -- ---- 207s delete from public.sl_seqlog where seql_seqid = p_seq_id; 207s delete from public.sl_sequence where seq_id = p_seq_id; 207s 207s return p_seq_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setDropSequence_int(p_seq_id int4) is 207s 'setDropSequence_int (seq_id) 207s 207s This processes the SET_DROP_SEQUENCE event. On remote nodes that 207s subscribe to the set containing sequence seq_id, drop the sequence 207s from the replication set.'; 207s COMMENT 207s create or replace function public.setMoveTable (p_tab_id int4, p_new_set_id int4) 207s returns bigint 207s as $$ 207s declare 207s v_old_set_id int4; 207s v_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Get the tables current set 207s -- ---- 207s select tab_set into v_old_set_id from public.sl_table 207s where tab_id = p_tab_id; 207s if not found then 207s raise exception 'Slony-I: table %d not found', p_tab_id; 207s end if; 207s 207s -- ---- 207s -- Check that both sets exist and originate here 207s -- ---- 207s if p_new_set_id = v_old_set_id then 207s raise exception 'Slony-I: set ids cannot be identical'; 207s end if; 207s select set_origin into v_origin from public.sl_set 207s where set_id = p_new_set_id; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_new_set_id; 207s end if; 207s if v_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: set % does not originate on local node', 207s p_new_set_id; 207s end if; 207s 207s select set_origin into v_origin from public.sl_set 207s where set_id = v_old_set_id; 207s if not found then 207s raise exception 'Slony-I: set % not found', v_old_set_id; 207s end if; 207s if v_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: set % does not originate on local node', 207s v_old_set_id; 207s end if; 207s 207s -- ---- 207s -- Check that both sets are subscribed by the same set of nodes 207s -- ---- 207s if exists (select true from public.sl_subscribe SUB1 207s where SUB1.sub_set = p_new_set_id 207s and SUB1.sub_receiver not in (select SUB2.sub_receiver 207s from public.sl_subscribe SUB2 207s where SUB2.sub_set = v_old_set_id)) 207s then 207s raise exception 'Slony-I: subscriber lists of set % and % are different', 207s p_new_set_id, v_old_set_id; 207s end if; 207s 207s if exists (select true from public.sl_subscribe SUB1 207s where SUB1.sub_set = v_old_set_id 207s and SUB1.sub_receiver not in (select SUB2.sub_receiver 207s from public.sl_subscribe SUB2 207s where SUB2.sub_set = p_new_set_id)) 207s then 207s raise exception 'Slony-I: subscriber lists of set % and % are different', 207s v_old_set_id, p_new_set_id; 207s end if; 207s 207s -- ---- 207s -- Change the set the table belongs to 207s -- ---- 207s perform public.createEvent('_main', 'SYNC', NULL); 207s perform public.setMoveTable_int(p_tab_id, p_new_set_id); 207s return public.createEvent('_main', 'SET_MOVE_TABLE', 207s p_tab_id::text, p_new_set_id::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setMoveTable(p_tab_id int4, p_new_set_id int4) is 207s 'This generates the SET_MOVE_TABLE event. If the set that the table is 207s in is identically subscribed to the set that the table is to be moved 207s into, then the SET_MOVE_TABLE event is raised.'; 207s COMMENT 207s create or replace function public.setMoveTable_int (p_tab_id int4, p_new_set_id int4) 207s returns int4 207s as $$ 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Move the table to the new set 207s -- ---- 207s update public.sl_table 207s set tab_set = p_new_set_id 207s where tab_id = p_tab_id; 207s 207s return p_tab_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setMoveTable(p_tab_id int4, p_new_set_id int4) is 207s 'This processes the SET_MOVE_TABLE event. The table is moved 207s to the destination set.'; 207s COMMENT 207s create or replace function public.setMoveSequence (p_seq_id int4, p_new_set_id int4) 207s returns bigint 207s as $$ 207s declare 207s v_old_set_id int4; 207s v_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Get the sequences current set 207s -- ---- 207s select seq_set into v_old_set_id from public.sl_sequence 207s where seq_id = p_seq_id; 207s if not found then 207s raise exception 'Slony-I: setMoveSequence(): sequence %d not found', p_seq_id; 207s end if; 207s 207s -- ---- 207s -- Check that both sets exist and originate here 207s -- ---- 207s if p_new_set_id = v_old_set_id then 207s raise exception 'Slony-I: setMoveSequence(): set ids cannot be identical'; 207s end if; 207s select set_origin into v_origin from public.sl_set 207s where set_id = p_new_set_id; 207s if not found then 207s raise exception 'Slony-I: setMoveSequence(): set % not found', p_new_set_id; 207s end if; 207s if v_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: setMoveSequence(): set % does not originate on local node', 207s p_new_set_id; 207s end if; 207s 207s select set_origin into v_origin from public.sl_set 207s where set_id = v_old_set_id; 207s if not found then 207s raise exception 'Slony-I: set % not found', v_old_set_id; 207s end if; 207s if v_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: set % does not originate on local node', 207s v_old_set_id; 207s end if; 207s 207s -- ---- 207s -- Check that both sets are subscribed by the same set of nodes 207s -- ---- 207s if exists (select true from public.sl_subscribe SUB1 207s where SUB1.sub_set = p_new_set_id 207s and SUB1.sub_receiver not in (select SUB2.sub_receiver 207s from public.sl_subscribe SUB2 207s where SUB2.sub_set = v_old_set_id)) 207s then 207s raise exception 'Slony-I: subscriber lists of set % and % are different', 207s p_new_set_id, v_old_set_id; 207s end if; 207s 207s if exists (select true from public.sl_subscribe SUB1 207s where SUB1.sub_set = v_old_set_id 207s and SUB1.sub_receiver not in (select SUB2.sub_receiver 207s from public.sl_subscribe SUB2 207s where SUB2.sub_set = p_new_set_id)) 207s then 207s raise exception 'Slony-I: subscriber lists of set % and % are different', 207s v_old_set_id, p_new_set_id; 207s end if; 207s 207s -- ---- 207s -- Change the set the sequence belongs to 207s -- ---- 207s perform public.setMoveSequence_int(p_seq_id, p_new_set_id); 207s return public.createEvent('_main', 'SET_MOVE_SEQUENCE', 207s p_seq_id::text, p_new_set_id::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setMoveSequence (p_seq_id int4, p_new_set_id int4) is 207s 'setMoveSequence(p_seq_id, p_new_set_id) - This generates the 207s SET_MOVE_SEQUENCE event, after validation, notably that both sets 207s exist, are distinct, and have exactly the same subscription lists'; 207s COMMENT 207s create or replace function public.setMoveSequence_int (p_seq_id int4, p_new_set_id int4) 207s returns int4 207s as $$ 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Move the sequence to the new set 207s -- ---- 207s update public.sl_sequence 207s set seq_set = p_new_set_id 207s where seq_id = p_seq_id; 207s 207s return p_seq_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setMoveSequence_int (p_seq_id int4, p_new_set_id int4) is 207s 'setMoveSequence_int(p_seq_id, p_new_set_id) - processes the 207s SET_MOVE_SEQUENCE event, moving a sequence to another replication 207s set.'; 207s COMMENT 207s create or replace function public.sequenceSetValue(p_seq_id int4, p_seq_origin int4, p_ev_seqno int8, p_last_value int8,p_ignore_missing bool) returns int4 207s as $$ 207s declare 207s v_fqname text; 207s v_found integer; 207s begin 207s -- ---- 207s -- Get the sequences fully qualified name 207s -- ---- 207s select public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) into v_fqname 207s from public.sl_sequence SQ, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where SQ.seq_id = p_seq_id 207s and SQ.seq_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid; 207s if not found then 207s if p_ignore_missing then 207s return null; 207s end if; 207s raise exception 'Slony-I: sequenceSetValue(): sequence % not found', p_seq_id; 207s end if; 207s 207s -- ---- 207s -- Update it to the new value 207s -- ---- 207s execute 'select setval(''' || v_fqname || 207s ''', ' || p_last_value::text || ')'; 207s 207s if p_ev_seqno is not null then 207s insert into public.sl_seqlog 207s (seql_seqid, seql_origin, seql_ev_seqno, seql_last_value) 207s values (p_seq_id, p_seq_origin, p_ev_seqno, p_last_value); 207s end if; 207s return p_seq_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.sequenceSetValue(p_seq_id int4, p_seq_origin int4, p_ev_seqno int8, p_last_value int8,p_ignore_missing bool) is 207s 'sequenceSetValue (seq_id, seq_origin, ev_seqno, last_value,ignore_missing) 207s Set sequence seq_id to have new value last_value. 207s '; 207s COMMENT 207s drop function if exists public.ddlCapture (p_statement text, p_nodes text); 207s DROP FUNCTION 207s create or replace function public.ddlCapture (p_statement text, p_nodes text) 207s returns bigint 207s as $$ 207s declare 207s c_local_node integer; 207s c_found_origin boolean; 207s c_node text; 207s c_cmdargs text[]; 207s c_nodeargs text; 207s c_delim text; 207s begin 207s c_local_node := public.getLocalNodeId('_main'); 207s 207s c_cmdargs = array_append('{}'::text[], p_statement); 207s c_nodeargs = ''; 207s if p_nodes is not null then 207s c_found_origin := 'f'; 207s -- p_nodes list needs to consist of a list of nodes that exist 207s -- and that include the current node ID 207s for c_node in select trim(node) from 207s pg_catalog.regexp_split_to_table(p_nodes, ',') as node loop 207s if not exists 207s (select 1 from public.sl_node 207s where no_id = (c_node::integer)) then 207s raise exception 'ddlcapture(%,%) - node % does not exist!', 207s p_statement, p_nodes, c_node; 207s end if; 207s 207s if c_local_node = (c_node::integer) then 207s c_found_origin := 't'; 207s end if; 207s if length(c_nodeargs)>0 then 207s c_nodeargs = c_nodeargs ||','|| c_node; 207s else 207s c_nodeargs=c_node; 207s end if; 207s end loop; 207s 207s if not c_found_origin then 207s raise exception 207s 'ddlcapture(%,%) - origin node % not included in ONLY ON list!', 207s p_statement, p_nodes, c_local_node; 207s end if; 207s end if; 207s c_cmdargs = array_append(c_cmdargs,c_nodeargs); 207s c_delim=','; 207s c_cmdargs = array_append(c_cmdargs, 207s 207s (select public.string_agg( seq_id::text || c_delim 207s || c_local_node || 207s c_delim || seq_last_value) 207s FROM ( 207s select seq_id, 207s seq_last_value from public.sl_seqlastvalue 207s where seq_origin = c_local_node) as FOO 207s where NOT public.seqtrack(seq_id,seq_last_value) is NULL)); 207s insert into public.sl_log_script 207s (log_origin, log_txid, log_actionseq, log_cmdtype, log_cmdargs) 207s values 207s (c_local_node, pg_catalog.txid_current(), 207s nextval('public.sl_action_seq'), 'S', c_cmdargs); 207s execute p_statement; 207s return currval('public.sl_action_seq'); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.ddlCapture (p_statement text, p_nodes text) is 207s 'Capture an SQL statement (usually DDL) that is to be literally replayed on subscribers'; 207s COMMENT 207s drop function if exists public.ddlScript_complete (int4, text, int4); 207s DROP FUNCTION 207s create or replace function public.ddlScript_complete (p_nodes text) 207s returns bigint 207s as $$ 207s declare 207s c_local_node integer; 207s c_found_origin boolean; 207s c_node text; 207s c_cmdargs text[]; 207s begin 207s c_local_node := public.getLocalNodeId('_main'); 207s 207s c_cmdargs = '{}'::text[]; 207s if p_nodes is not null then 207s c_found_origin := 'f'; 207s -- p_nodes list needs to consist o a list of nodes that exist 207s -- and that include the current node ID 207s for c_node in select trim(node) from 207s pg_catalog.regexp_split_to_table(p_nodes, ',') as node loop 207s if not exists 207s (select 1 from public.sl_node 207s where no_id = (c_node::integer)) then 207s raise exception 'ddlcapture(%,%) - node % does not exist!', 207s p_statement, p_nodes, c_node; 207s end if; 207s 207s if c_local_node = (c_node::integer) then 207s c_found_origin := 't'; 207s end if; 207s 207s c_cmdargs = array_append(c_cmdargs, c_node); 207s end loop; 207s 207s if not c_found_origin then 207s raise exception 207s 'ddlScript_complete(%) - origin node % not included in ONLY ON list!', 207s p_nodes, c_local_node; 207s end if; 207s end if; 207s 207s perform public.ddlScript_complete_int(); 207s 207s insert into public.sl_log_script 207s (log_origin, log_txid, log_actionseq, log_cmdtype, log_cmdargs) 207s values 207s (c_local_node, pg_catalog.txid_current(), 207s nextval('public.sl_action_seq'), 's', c_cmdargs); 207s 207s return currval('public.sl_action_seq'); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.ddlScript_complete(p_nodes text) is 207s 'ddlScript_complete(set_id, script, only_on_node) 207s 207s After script has run on origin, this fixes up relnames and 207s log trigger arguments and inserts the "fire ddlScript_complete_int() 207s log row into sl_log_script.'; 207s COMMENT 207s drop function if exists public.ddlScript_complete_int(int4, int4); 207s DROP FUNCTION 207s create or replace function public.ddlScript_complete_int () 207s returns int4 207s as $$ 207s begin 207s perform public.updateRelname(); 207s perform public.repair_log_triggers(true); 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.ddlScript_complete_int() is 207s 'ddlScript_complete_int() 207s 207s Complete processing the DDL_SCRIPT event.'; 207s COMMENT 207s create or replace function public.alterTableAddTriggers (p_tab_id int4) 207s returns int4 207s as $$ 207s declare 207s v_no_id int4; 207s v_tab_row record; 207s v_tab_fqname text; 207s v_tab_attkind text; 207s v_n int4; 207s v_trec record; 207s v_tgbad boolean; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Get our local node ID 207s -- ---- 207s v_no_id := public.getLocalNodeId('_main'); 207s 207s -- ---- 207s -- Get the sl_table row and the current origin of the table. 207s -- ---- 207s select T.tab_reloid, T.tab_set, T.tab_idxname, 207s S.set_origin, PGX.indexrelid, 207s public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) as tab_fqname 207s into v_tab_row 207s from public.sl_table T, public.sl_set S, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN, 207s "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGXC 207s where T.tab_id = p_tab_id 207s and T.tab_set = S.set_id 207s and T.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid 207s and PGX.indrelid = T.tab_reloid 207s and PGX.indexrelid = PGXC.oid 207s and PGXC.relname = T.tab_idxname 207s for update; 207s if not found then 207s raise exception 'Slony-I: alterTableAddTriggers(): Table with id % not found', p_tab_id; 207s end if; 207s v_tab_fqname = v_tab_row.tab_fqname; 207s 207s v_tab_attkind := public.determineAttKindUnique(v_tab_row.tab_fqname, 207s v_tab_row.tab_idxname); 207s 207s execute 'lock table ' || v_tab_fqname || ' in access exclusive mode'; 207s 207s -- ---- 207s -- Create the log and the deny access triggers 207s -- ---- 207s execute 'create trigger "_main_logtrigger"' || 207s ' after insert or update or delete on ' || 207s v_tab_fqname || ' for each row execute procedure public.logTrigger (' || 207s pg_catalog.quote_literal('_main') || ',' || 207s pg_catalog.quote_literal(p_tab_id::text) || ',' || 207s pg_catalog.quote_literal(v_tab_attkind) || ');'; 207s 207s execute 'create trigger "_main_denyaccess" ' || 207s 'before insert or update or delete on ' || 207s v_tab_fqname || ' for each row execute procedure ' || 207s 'public.denyAccess (' || pg_catalog.quote_literal('_main') || ');'; 207s 207s perform public.alterTableAddTruncateTrigger(v_tab_fqname, p_tab_id); 207s 207s perform public.alterTableConfigureTriggers (p_tab_id); 207s return p_tab_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.alterTableAddTriggers(p_tab_id int4) is 207s 'alterTableAddTriggers(tab_id) 207s 207s Adds the log and deny access triggers to a replicated table.'; 207s COMMENT 207s create or replace function public.alterTableDropTriggers (p_tab_id int4) 207s returns int4 207s as $$ 207s declare 207s v_no_id int4; 207s v_tab_row record; 207s v_tab_fqname text; 207s v_n int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Get our local node ID 207s -- ---- 207s v_no_id := public.getLocalNodeId('_main'); 207s 207s -- ---- 207s -- Get the sl_table row and the current tables origin. 207s -- ---- 207s select T.tab_reloid, T.tab_set, 207s S.set_origin, PGX.indexrelid, 207s public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) as tab_fqname 207s into v_tab_row 207s from public.sl_table T, public.sl_set S, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN, 207s "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGXC 207s where T.tab_id = p_tab_id 207s and T.tab_set = S.set_id 207s and T.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid 207s and PGX.indrelid = T.tab_reloid 207s and PGX.indexrelid = PGXC.oid 207s and PGXC.relname = T.tab_idxname 207s for update; 207s if not found then 207s raise exception 'Slony-I: alterTableDropTriggers(): Table with id % not found', p_tab_id; 207s end if; 207s v_tab_fqname = v_tab_row.tab_fqname; 207s 207s execute 'lock table ' || v_tab_fqname || ' in access exclusive mode'; 207s 207s -- ---- 207s -- Drop both triggers 207s -- ---- 207s execute 'drop trigger "_main_logtrigger" on ' || 207s v_tab_fqname; 207s 207s execute 'drop trigger "_main_denyaccess" on ' || 207s v_tab_fqname; 207s 207s perform public.alterTableDropTruncateTrigger(v_tab_fqname, p_tab_id); 207s 207s return p_tab_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.alterTableDropTriggers (p_tab_id int4) is 207s 'alterTableDropTriggers (tab_id) 207s 207s Remove the log and deny access triggers from a table.'; 207s COMMENT 207s create or replace function public.alterTableConfigureTriggers (p_tab_id int4) 207s returns int4 207s as $$ 207s declare 207s v_no_id int4; 207s v_tab_row record; 207s v_tab_fqname text; 207s v_n int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Get our local node ID 207s -- ---- 207s v_no_id := public.getLocalNodeId('_main'); 207s 207s -- ---- 207s -- Get the sl_table row and the current tables origin. 207s -- ---- 207s select T.tab_reloid, T.tab_set, 207s S.set_origin, PGX.indexrelid, 207s public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) as tab_fqname 207s into v_tab_row 207s from public.sl_table T, public.sl_set S, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN, 207s "pg_catalog".pg_index PGX, "pg_catalog".pg_class PGXC 207s where T.tab_id = p_tab_id 207s and T.tab_set = S.set_id 207s and T.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid 207s and PGX.indrelid = T.tab_reloid 207s and PGX.indexrelid = PGXC.oid 207s and PGXC.relname = T.tab_idxname 207s for update; 207s if not found then 207s raise exception 'Slony-I: alterTableConfigureTriggers(): Table with id % not found', p_tab_id; 207s end if; 207s v_tab_fqname = v_tab_row.tab_fqname; 207s 207s -- ---- 207s -- Configuration depends on the origin of the table 207s -- ---- 207s if v_tab_row.set_origin = v_no_id then 207s -- ---- 207s -- On the origin the log trigger is configured like a default 207s -- user trigger and the deny access trigger is disabled. 207s -- ---- 207s execute 'alter table ' || v_tab_fqname || 207s ' enable trigger "_main_logtrigger"'; 207s execute 'alter table ' || v_tab_fqname || 207s ' disable trigger "_main_denyaccess"'; 207s perform public.alterTableConfigureTruncateTrigger(v_tab_fqname, 207s 'enable', 'disable'); 207s else 207s -- ---- 207s -- On a replica the log trigger is disabled and the 207s -- deny access trigger fires in origin session role. 207s -- ---- 207s execute 'alter table ' || v_tab_fqname || 207s ' disable trigger "_main_logtrigger"'; 207s execute 'alter table ' || v_tab_fqname || 207s ' enable trigger "_main_denyaccess"'; 207s perform public.alterTableConfigureTruncateTrigger(v_tab_fqname, 207s 'disable', 'enable'); 207s 207s end if; 207s 207s return p_tab_id; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.alterTableConfigureTriggers (p_tab_id int4) is 207s 'alterTableConfigureTriggers (tab_id) 207s 207s Set the enable/disable configuration for the replication triggers 207s according to the origin of the set.'; 207s COMMENT 207s create or replace function public.resubscribeNode (p_origin int4, 207s p_provider int4, p_receiver int4) 207s returns bigint 207s as $$ 207s declare 207s v_record record; 207s v_missing_sets text; 207s v_ev_seqno bigint; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- 207s -- Check that the receiver exists 207s -- 207s if not exists (select no_id from public.sl_node where no_id= 207s p_receiver) then 207s raise exception 'Slony-I: subscribeSet() receiver % does not exist' , p_receiver; 207s end if; 207s 207s -- 207s -- Check that the provider exists 207s -- 207s if not exists (select no_id from public.sl_node where no_id= 207s p_provider) then 207s raise exception 'Slony-I: subscribeSet() provider % does not exist' , p_provider; 207s end if; 207s 207s 207s -- ---- 207s -- Check that this is called on the origin node 207s -- ---- 207s if p_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: subscribeSet() must be called on origin'; 207s end if; 207s 207s -- --- 207s -- Verify that the provider is either the origin or an active subscriber 207s -- Bug report #1362 207s -- --- 207s if p_origin <> p_provider then 207s for v_record in select sub1.sub_set from 207s public.sl_subscribe sub1 207s left outer join (public.sl_subscribe sub2 207s inner join 207s public.sl_set on ( 207s sl_set.set_id=sub2.sub_set 207s and suNOTICE: function public.ddlcapture(text,text) does not exist, skipping 207s NOTICE: function public.ddlscript_complete(int4,text,int4) does not exist, skipping 207s NOTICE: function public.ddlscript_complete_int(int4,int4) does not exist, skipping 207s NOTICE: function public.subscribeset_int(int4,int4,int4,bool,bool) does not exist, skipping 207s NOTICE: function public.unsubscribeset(int4,int4,pg_catalog.bool) does not exist, skipping 207s b2.sub_set=p_origin) 207s ) 207s ON ( sub1.sub_set = sub2.sub_set and 207s sub1.sub_receiver = p_provider and 207s sub1.sub_forward and sub1.sub_active 207s and sub2.sub_receiver=p_receiver) 207s 207s where sub2.sub_set is null 207s loop 207s v_missing_sets=v_missing_sets || ' ' || v_record.sub_set; 207s end loop; 207s if v_missing_sets is not null then 207s raise exception 'Slony-I: subscribeSet(): provider % is not an active forwarding node for replication set %', p_sub_provider, v_missing_sets; 207s end if; 207s end if; 207s 207s for v_record in select * from 207s public.sl_subscribe, public.sl_set where 207s sub_set=set_id and 207s sub_receiver=p_receiver 207s and set_origin=p_origin 207s loop 207s -- ---- 207s -- Create the SUBSCRIBE_SET event 207s -- ---- 207s v_ev_seqno := public.createEvent('_main', 'SUBSCRIBE_SET', 207s v_record.sub_set::text, p_provider::text, p_receiver::text, 207s case v_record.sub_forward when true then 't' else 'f' end, 207s 'f' ); 207s 207s -- ---- 207s -- Call the internal procedure to store the subscription 207s -- ---- 207s perform public.subscribeSet_int(v_record.sub_set, 207s p_provider, 207s p_receiver, v_record.sub_forward, false); 207s end loop; 207s 207s return v_ev_seqno; 207s end; 207s $$ 207s language plpgsql; 207s CREATE FUNCTION 207s create or replace function public.subscribeSet (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4, p_sub_forward bool, p_omit_copy bool) 207s returns bigint 207s as $$ 207s declare 207s v_set_origin int4; 207s v_ev_seqno int8; 207s v_ev_seqno2 int8; 207s v_rec record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- 207s -- Check that the receiver exists 207s -- 207s if not exists (select no_id from public.sl_node where no_id= 207s p_sub_receiver) then 207s raise exception 'Slony-I: subscribeSet() receiver % does not exist' , p_sub_receiver; 207s end if; 207s 207s -- 207s -- Check that the provider exists 207s -- 207s if not exists (select no_id from public.sl_node where no_id= 207s p_sub_provider) then 207s raise exception 'Slony-I: subscribeSet() provider % does not exist' , p_sub_provider; 207s end if; 207s 207s -- ---- 207s -- Check that the origin and provider of the set are remote 207s -- ---- 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = p_sub_set; 207s if not found then 207s raise exception 'Slony-I: subscribeSet(): set % not found', p_sub_set; 207s end if; 207s if v_set_origin = p_sub_receiver then 207s raise exception 207s 'Slony-I: subscribeSet(): set origin and receiver cannot be identical'; 207s end if; 207s if p_sub_receiver = p_sub_provider then 207s raise exception 207s 'Slony-I: subscribeSet(): set provider and receiver cannot be identical'; 207s end if; 207s -- ---- 207s -- Check that this is called on the origin node 207s -- ---- 207s if v_set_origin != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: subscribeSet() must be called on origin'; 207s end if; 207s 207s -- --- 207s -- Verify that the provider is either the origin or an active subscriber 207s -- Bug report #1362 207s -- --- 207s if v_set_origin <> p_sub_provider then 207s if not exists (select 1 from public.sl_subscribe 207s where sub_set = p_sub_set and 207s sub_receiver = p_sub_provider and 207s sub_forward and sub_active) then 207s raise exception 'Slony-I: subscribeSet(): provider % is not an active forwarding node for replication set %', p_sub_provider, p_sub_set; 207s end if; 207s end if; 207s 207s -- --- 207s -- Enforce that all sets from one origin are subscribed 207s -- using the same data provider per receiver. 207s -- ---- 207s if not exists (select 1 from public.sl_subscribe 207s where sub_set = p_sub_set and sub_receiver = p_sub_receiver) then 207s -- 207s -- New subscription - error out if we have any other subscription 207s -- from that origin with a different data provider. 207s -- 207s for v_rec in select sub_provider from public.sl_subscribe 207s join public.sl_set on set_id = sub_set 207s where set_origin = v_set_origin and sub_receiver = p_sub_receiver 207s loop 207s if v_rec.sub_provider <> p_sub_provider then 207s raise exception 'Slony-I: subscribeSet(): wrong provider % - existing subscription from origin % users provider %', 207s p_sub_provider, v_set_origin, v_rec.sub_provider; 207s end if; 207s end loop; 207s else 207s -- 207s -- Existing subscription - in case the data provider changes and 207s -- there are other subscriptions, warn here. subscribeSet_int() 207s -- will currently change the data provider for those sets as well. 207s -- 207s for v_rec in select set_id, sub_provider from public.sl_subscribe 207s join public.sl_set on set_id = sub_set 207s where set_origin = v_set_origin and sub_receiver = p_sub_receiver 207s and set_id <> p_sub_set 207s loop 207s if v_rec.sub_provider <> p_sub_provider then 207s raise exception 'Slony-I: subscribeSet(): also data provider for set % use resubscribe instead', 207s v_rec.set_id; 207s end if; 207s end loop; 207s end if; 207s 207s -- ---- 207s -- Create the SUBSCRIBE_SET event 207s -- ---- 207s v_ev_seqno := public.createEvent('_main', 'SUBSCRIBE_SET', 207s p_sub_set::text, p_sub_provider::text, p_sub_receiver::text, 207s case p_sub_forward when true then 't' else 'f' end, 207s case p_omit_copy when true then 't' else 'f' end 207s ); 207s 207s -- ---- 207s -- Call the internal procedure to store the subscription 207s -- ---- 207s v_ev_seqno2:=public.subscribeSet_int(p_sub_set, p_sub_provider, 207s p_sub_receiver, p_sub_forward, p_omit_copy); 207s 207s if v_ev_seqno2 is not null then 207s v_ev_seqno:=v_ev_seqno2; 207s end if; 207s 207s return v_ev_seqno; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.subscribeSet (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4, p_sub_forward bool, p_omit_copy bool) is 207s 'subscribeSet (sub_set, sub_provider, sub_receiver, sub_forward, omit_copy) 207s 207s Makes sure that the receiver is not the provider, then stores the 207s subscription, and publishes the SUBSCRIBE_SET event to other nodes. 207s 207s If omit_copy is true, then no data copy will be done. 207s '; 207s COMMENT 207s DROP FUNCTION IF EXISTS public.subscribeSet_int(int4,int4,int4,bool,bool); 207s DROP FUNCTION 207s create or replace function public.subscribeSet_int (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4, p_sub_forward bool, p_omit_copy bool) 207s returns int4 207s as $$ 207s declare 207s v_set_origin int4; 207s v_sub_row record; 207s v_seq_id bigint; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Lookup the set origin 207s -- ---- 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = p_sub_set; 207s if not found then 207s raise exception 'Slony-I: subscribeSet_int(): set % not found', p_sub_set; 207s end if; 207s 207s -- ---- 207s -- Provider change is only allowed for active sets 207s -- ---- 207s if p_sub_receiver = public.getLocalNodeId('_main') then 207s select sub_active into v_sub_row from public.sl_subscribe 207s where sub_set = p_sub_set 207s and sub_receiver = p_sub_receiver; 207s if found then 207s if not v_sub_row.sub_active then 207s raise exception 'Slony-I: subscribeSet_int(): set % is not active, cannot change provider', 207s p_sub_set; 207s end if; 207s end if; 207s end if; 207s 207s -- ---- 207s -- Try to change provider and/or forward for an existing subscription 207s -- ---- 207s update public.sl_subscribe 207s set sub_provider = p_sub_provider, 207s sub_forward = p_sub_forward 207s where sub_set = p_sub_set 207s and sub_receiver = p_sub_receiver; 207s if found then 207s 207s -- ---- 207s -- This is changing a subscriptoin. Make sure all sets from 207s -- this origin are subscribed using the same data provider. 207s -- For this we first check that the requested data provider 207s -- is subscribed to all the sets, the receiver is subscribed to. 207s -- ---- 207s for v_sub_row in select set_id from public.sl_set 207s join public.sl_subscribe on set_id = sub_set 207s where set_origin = v_set_origin 207s and sub_receiver = p_sub_receiver 207s and sub_set <> p_sub_set 207s loop 207s if not exists (select 1 from public.sl_subscribe 207s where sub_set = v_sub_row.set_id 207s and sub_receiver = p_sub_provider 207s and sub_active and sub_forward) 207s and not exists (select 1 from public.sl_set 207s where set_id = v_sub_row.set_id 207s and set_origin = p_sub_provider) 207s then 207s raise exception 'Slony-I: subscribeSet_int(): node % is not a forwarding subscriber for set %', 207s p_sub_provider, v_sub_row.set_id; 207s end if; 207s 207s -- ---- 207s -- New data provider offers this set as well, change that 207s -- subscription too. 207s -- ---- 207s update public.sl_subscribe 207s set sub_provider = p_sub_provider 207s where sub_set = v_sub_row.set_id 207s and sub_receiver = p_sub_receiver; 207s end loop; 207s 207s -- ---- 207s -- Rewrite sl_listen table 207s -- ---- 207s perform public.RebuildListenEntries(); 207s 207s return p_sub_set; 207s end if; 207s 207s -- ---- 207s -- Not found, insert a new one 207s -- ---- 207s if not exists (select true from public.sl_path 207s where pa_server = p_sub_provider 207s and pa_client = p_sub_receiver) 207s then 207s insert into public.sl_path 207s (pa_server, pa_client, pa_conninfo, pa_connretry) 207s values 207s (p_sub_provider, p_sub_receiver, 207s '', 10); 207s end if; 207s insert into public.sl_subscribe 207s (sub_set, sub_provider, sub_receiver, sub_forward, sub_active) 207s values (p_sub_set, p_sub_provider, p_sub_receiver, 207s p_sub_forward, false); 207s 207s -- ---- 207s -- If the set origin is here, then enable the subscription 207s -- ---- 207s if v_set_origin = public.getLocalNodeId('_main') then 207s select public.createEvent('_main', 'ENABLE_SUBSCRIPTION', 207s p_sub_set::text, p_sub_provider::text, p_sub_receiver::text, 207s case p_sub_forward when true then 't' else 'f' end, 207s case p_omit_copy when true then 't' else 'f' end 207s ) into v_seq_id; 207s perform public.enableSubscription(p_sub_set, 207s p_sub_provider, p_sub_receiver); 207s end if; 207s 207s -- ---- 207s -- Rewrite sl_listen table 207s -- ---- 207s perform public.RebuildListenEntries(); 207s 207s return p_sub_set; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.subscribeSet_int (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4, p_sub_forward bool, p_omit_copy bool) is 207s 'subscribeSet_int (sub_set, sub_provider, sub_receiver, sub_forward, omit_copy) 207s 207s Internal actions for subscribing receiver sub_receiver to subscription 207s set sub_set.'; 207s COMMENT 207s drop function IF EXISTS public.unsubscribeSet(int4,int4,boolean); 207s DROP FUNCTION 207s create or replace function public.unsubscribeSet (p_sub_set int4, p_sub_receiver int4,p_force boolean) 207s returns bigint 207s as $$ 207s declare 207s v_tab_row record; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- Check that this is called on the receiver node 207s -- ---- 207s if p_sub_receiver != public.getLocalNodeId('_main') then 207s raise exception 'Slony-I: unsubscribeSet() must be called on receiver'; 207s end if; 207s 207s 207s 207s -- ---- 207s -- Check that this does not break any chains 207s -- ---- 207s if p_force=false and exists (select true from public.sl_subscribe 207s where sub_set = p_sub_set 207s and sub_provider = p_sub_receiver) 207s then 207s raise exception 'Slony-I: Cannot unsubscribe set % while being provider', 207s p_sub_set; 207s end if; 207s 207s if exists (select true from public.sl_subscribe 207s where sub_set = p_sub_set 207s and sub_provider = p_sub_receiver) 207s then 207s --delete the receivers of this provider. 207s --unsubscribeSet_int() will generate the event 207s --when it runs on the receiver. 207s delete from public.sl_subscribe 207s where sub_set=p_sub_set 207s and sub_provider=p_sub_receiver; 207s end if; 207s 207s -- ---- 207s -- Remove the replication triggers. 207s -- ---- 207s for v_tab_row in select tab_id from public.sl_table 207s where tab_set = p_sub_set 207s order by tab_id 207s loop 207s perform public.alterTableDropTriggers(v_tab_row.tab_id); 207s end loop; 207s 207s -- ---- 207s -- Remove the setsync status. This will also cause the 207s -- worker thread to ignore the set and stop replicating 207s -- right now. 207s -- ---- 207s delete from public.sl_setsync 207s where ssy_setid = p_sub_set; 207s 207s -- ---- 207s -- Remove all sl_table and sl_sequence entries for this set. 207s -- Should we ever subscribe again, the initial data 207s -- copy process will create new ones. 207s -- ---- 207s delete from public.sl_table 207s where tab_set = p_sub_set; 207s delete from public.sl_sequence 207s where seq_set = p_sub_set; 207s 207s -- ---- 207s -- Call the internal procedure to drop the subscription 207s -- ---- 207s perform public.unsubscribeSet_int(p_sub_set, p_sub_receiver); 207s 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s -- ---- 207s -- Create the UNSUBSCRIBE_SET event 207s -- ---- 207s return public.createEvent('_main', 'UNSUBSCRIBE_SET', 207s p_sub_set::text, p_sub_receiver::text); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.unsubscribeSet (p_sub_set int4, p_sub_receiver int4,force boolean) is 207s 'unsubscribeSet (sub_set, sub_receiver,force) 207s 207s Unsubscribe node sub_receiver from subscription set sub_set. This is 207s invoked on the receiver node. It verifies that this does not break 207s any chains (e.g. - where sub_receiver is a provider for another node), 207s then restores tables, drops Slony-specific keys, drops table entries 207s for the set, drops the subscription, and generates an UNSUBSCRIBE_SET 207s node to publish that the node is being dropped.'; 207s COMMENT 207s create or replace function public.unsubscribeSet_int (p_sub_set int4, p_sub_receiver int4) 207s returns int4 207s as $$ 207s declare 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- All the real work is done before event generation on the 207s -- subscriber. 207s -- ---- 207s 207s --if this event unsubscribes the provider of this node 207s --then this node should unsubscribe itself from the set as well. 207s 207s if exists (select true from 207s public.sl_subscribe where 207s sub_set=p_sub_set and sub_provider=p_sub_receiver 207s and sub_receiver=public.getLocalNodeId('_main')) 207s then 207s perform public.unsubscribeSet(p_sub_set,public.getLocalNodeId('_main'),true); 207s end if; 207s 207s 207s delete from public.sl_subscribe 207s where sub_set = p_sub_set 207s and sub_receiver = p_sub_receiver; 207s 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s return p_sub_set; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.unsubscribeSet_int (p_sub_set int4, p_sub_receiver int4) is 207s 'unsubscribeSet_int (sub_set, sub_receiver) 207s 207s All the REAL work of removing the subscriber is done before the event 207s is generated, so this function just has to drop the references to the 207s subscription in sl_subscribe.'; 207s COMMENT 207s create or replace function public.enableSubscription (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4) 207s returns int4 207s as $$ 207s begin 207s return public.enableSubscription_int (p_sub_set, 207s p_sub_provider, p_sub_receiver); 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.enableSubscription (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4) is 207s 'enableSubscription (sub_set, sub_provider, sub_receiver) 207s 207s Indicates that sub_receiver intends subscribing to set sub_set from 207s sub_provider. Work is all done by the internal function 207s enableSubscription_int (sub_set, sub_provider, sub_receiver).'; 207s COMMENT 207s create or replace function public.enableSubscription_int (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4) 207s returns int4 207s as $$ 207s declare 207s v_n int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- ---- 207s -- The real work is done in the replication engine. All 207s -- we have to do here is remembering that it happened. 207s -- ---- 207s 207s -- ---- 207s -- Well, not only ... we might be missing an important event here 207s -- ---- 207s if not exists (select true from public.sl_path 207s where pa_server = p_sub_provider 207s and pa_client = p_sub_receiver) 207s then 207s insert into public.sl_path 207s (pa_server, pa_client, pa_conninfo, pa_connretry) 207s values 207s (p_sub_provider, p_sub_receiver, 207s '', 10); 207s end if; 207s 207s update public.sl_subscribe 207s set sub_active = 't' 207s where sub_set = p_sub_set 207s and sub_receiver = p_sub_receiver; 207s get diagnostics v_n = row_count; 207s if v_n = 0 then 207s insert into public.sl_subscribe 207s (sub_set, sub_provider, sub_receiver, 207s sub_forward, sub_active) 207s values 207s (p_sub_set, p_sub_provider, p_sub_receiver, 207s false, true); 207s end if; 207s 207s -- Rewrite sl_listen table 207s perform public.RebuildListenEntries(); 207s 207s return p_sub_set; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.enableSubscription_int (p_sub_set int4, p_sub_provider int4, p_sub_receiver int4) is 207s 'enableSubscription_int (sub_set, sub_provider, sub_receiver) 207s 207s Internal function to enable subscription of node sub_receiver to set 207s sub_set via node sub_provider. 207s 207s slon does most of the work; all we need do here is to remember that it 207s happened. The function updates sl_subscribe, indicating that the 207s subscription has become active.'; 207s COMMENT 207s create or replace function public.forwardConfirm (p_con_origin int4, p_con_received int4, p_con_seqno int8, p_con_timestamp timestamp) 207s returns bigint 207s as $$ 207s declare 207s v_max_seqno bigint; 207s begin 207s select into v_max_seqno coalesce(max(con_seqno), 0) 207s from public.sl_confirm 207s where con_origin = p_con_origin 207s and con_received = p_con_received; 207s if v_max_seqno < p_con_seqno then 207s insert into public.sl_confirm 207s (con_origin, con_received, con_seqno, con_timestamp) 207s values (p_con_origin, p_con_received, p_con_seqno, 207s p_con_timestamp); 207s v_max_seqno = p_con_seqno; 207s end if; 207s 207s return v_max_seqno; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.forwardConfirm (p_con_origin int4, p_con_received int4, p_con_seqno int8, p_con_timestamp timestamp) is 207s 'forwardConfirm (p_con_origin, p_con_received, p_con_seqno, p_con_timestamp) 207s 207s Confirms (recorded in sl_confirm) that items from p_con_origin up to 207s p_con_seqno have been received by node p_con_received as of 207s p_con_timestamp, and raises an event to forward this confirmation.'; 207s COMMENT 207s create or replace function public.cleanupEvent (p_interval interval) 207s returns int4 207s as $$ 207s declare 207s v_max_row record; 207s v_min_row record; 207s v_max_sync int8; 207s v_origin int8; 207s v_seqno int8; 207s v_xmin bigint; 207s v_rc int8; 207s begin 207s -- ---- 207s -- First remove all confirmations where origin/receiver no longer exist 207s -- ---- 207s delete from public.sl_confirm 207s where con_origin not in (select no_id from public.sl_node); 207s delete from public.sl_confirm 207s where con_received not in (select no_id from public.sl_node); 207s -- ---- 207s -- Next remove all but the oldest confirm row per origin,receiver pair. 207s -- Ignore confirmations that are younger than 10 minutes. We currently 207s -- have an not confirmed suspicion that a possibly lost transaction due 207s -- to a server crash might have been visible to another session, and 207s -- that this led to log data that is needed again got removed. 207s -- ---- 207s for v_max_row in select con_origin, con_received, max(con_seqno) as con_seqno 207s from public.sl_confirm 207s where con_timestamp < (CURRENT_TIMESTAMP - p_interval) 207s group by con_origin, con_received 207s loop 207s delete from public.sl_confirm 207s where con_origin = v_max_row.con_origin 207s and con_received = v_max_row.con_received 207s and con_seqno < v_max_row.con_seqno; 207s end loop; 207s 207s -- ---- 207s -- Then remove all events that are confirmed by all nodes in the 207s -- whole cluster up to the last SYNC 207s -- ---- 207s for v_min_row in select con_origin, min(con_seqno) as con_seqno 207s from public.sl_confirm 207s group by con_origin 207s loop 207s select coalesce(max(ev_seqno), 0) into v_max_sync 207s from public.sl_event 207s where ev_origin = v_min_row.con_origin 207s and ev_seqno <= v_min_row.con_seqno 207s and ev_type = 'SYNC'; 207s if v_max_sync > 0 then 207s delete from public.sl_event 207s where ev_origin = v_min_row.con_origin 207s and ev_seqno < v_max_sync; 207s end if; 207s end loop; 207s 207s -- ---- 207s -- If cluster has only one node, then remove all events up to 207s -- the last SYNC - Bug #1538 207s -- http://gborg.postgresql.org/project/slony1/bugs/bugupdate.php?1538 207s -- ---- 207s 207s select * into v_min_row from public.sl_node where 207s no_id <> public.getLocalNodeId('_main') limit 1; 207s if not found then 207s select ev_origin, ev_seqno into v_min_row from public.sl_event 207s where ev_origin = public.getLocalNodeId('_main') 207s order by ev_origin desc, ev_seqno desc limit 1; 207s raise notice 'Slony-I: cleanupEvent(): Single node - deleting events < %', v_min_row.ev_seqno; 207s delete from public.sl_event 207s where 207s ev_origin = v_min_row.ev_origin and 207s ev_seqno < v_min_row.ev_seqno; 207s 207s end if; 207s 207s if exists (select * from "pg_catalog".pg_class c, "pg_catalog".pg_namespace n, "pg_catalog".pg_attribute a where c.relname = 'sl_seqlog' and n.oid = c.relnamespace and a.attrelid = c.oid and a.attname = 'oid') then 207s execute 'alter table public.sl_seqlog set without oids;'; 207s end if; 207s -- ---- 207s -- Also remove stale entries from the nodelock table. 207s -- ---- 207s perform public.cleanupNodelock(); 207s 207s -- ---- 207s -- Find the eldest event left, for each origin 207s -- ---- 207s for v_origin, v_seqno, v_xmin in 207s select ev_origin, ev_seqno, "pg_catalog".txid_snapshot_xmin(ev_snapshot) from public.sl_event 207s where (ev_origin, ev_seqno) in (select ev_origin, min(ev_seqno) from public.sl_event where ev_type = 'SYNC' group by ev_origin) 207s loop 207s delete from public.sl_seqlog where seql_origin = v_origin and seql_ev_seqno < v_seqno; 207s delete from public.sl_log_script where log_origin = v_origin and log_txid < v_xmin; 207s end loop; 207s 207s v_rc := public.logswitch_finish(); 207s if v_rc = 0 then -- no switch in progress 207s perform public.logswitch_start(); 207s end if; 207s 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.cleanupEvent (p_interval interval) is 207s 'cleaning old data out of sl_confirm, sl_event. Removes all but the 207s last sl_confirm row per (origin,receiver), and then removes all events 207s that are confirmed by all nodes in the whole cluster up to the last 207s SYNC.'; 207s COMMENT 207s create or replace function public.determineIdxnameUnique(p_tab_fqname text, p_idx_name name) returns name 207s as $$ 207s declare 207s v_tab_fqname_quoted text default ''; 207s v_idxrow record; 207s begin 207s v_tab_fqname_quoted := public.slon_quote_input(p_tab_fqname); 207s -- 207s -- Ensure that the table exists 207s -- 207s if (select PGC.relname 207s from "pg_catalog".pg_class PGC, 207s "pg_catalog".pg_namespace PGN 207s where public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) = v_tab_fqname_quoted 207s and PGN.oid = PGC.relnamespace) is null then 207s raise exception 'Slony-I: determineIdxnameUnique(): table % not found', v_tab_fqname_quoted; 207s end if; 207s 207s -- 207s -- Lookup the tables primary key or the specified unique index 207s -- 207s if p_idx_name isnull then 207s select PGXC.relname 207s into v_idxrow 207s from "pg_catalog".pg_class PGC, 207s "pg_catalog".pg_namespace PGN, 207s "pg_catalog".pg_index PGX, 207s "pg_catalog".pg_class PGXC 207s where public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) = v_tab_fqname_quoted 207s and PGN.oid = PGC.relnamespace 207s and PGX.indrelid = PGC.oid 207s and PGX.indexrelid = PGXC.oid 207s and PGX.indisprimary; 207s if not found then 207s raise exception 'Slony-I: table % has no primary key', 207s v_tab_fqname_quoted; 207s end if; 207s else 207s select PGXC.relname 207s into v_idxrow 207s from "pg_catalog".pg_class PGC, 207s "pg_catalog".pg_namespace PGN, 207s "pg_catalog".pg_index PGX, 207s "pg_catalog".pg_class PGXC 207s where public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) = v_tab_fqname_quoted 207s and PGN.oid = PGC.relnamespace 207s and PGX.indrelid = PGC.oid 207s and PGX.indexrelid = PGXC.oid 207s and PGX.indisunique 207s and public.slon_quote_brute(PGXC.relname) = public.slon_quote_input(p_idx_name); 207s if not found then 207s raise exception 'Slony-I: table % has no unique index %', 207s v_tab_fqname_quoted, p_idx_name; 207s end if; 207s end if; 207s 207s -- 207s -- Return the found index name 207s -- 207s return v_idxrow.relname; 207s end; 207s $$ language plpgsql called on null input; 207s CREATE FUNCTION 207s comment on function public.determineIdxnameUnique(p_tab_fqname text, p_idx_name name) is 207s 'FUNCTION determineIdxnameUnique (tab_fqname, indexname) 207s 207s Given a tablename, tab_fqname, check that the unique index, indexname, 207s exists or return the primary key index name for the table. If there 207s is no unique index, it raises an exception.'; 207s COMMENT 207s create or replace function public.determineAttkindUnique(p_tab_fqname text, p_idx_name name) returns text 207s as $$ 207s declare 207s v_tab_fqname_quoted text default ''; 207s v_idx_name_quoted text; 207s v_idxrow record; 207s v_attrow record; 207s v_i integer; 207s v_attno int2; 207s v_attkind text default ''; 207s v_attfound bool; 207s begin 207s v_tab_fqname_quoted := public.slon_quote_input(p_tab_fqname); 207s v_idx_name_quoted := public.slon_quote_brute(p_idx_name); 207s -- 207s -- Ensure that the table exists 207s -- 207s if (select PGC.relname 207s from "pg_catalog".pg_class PGC, 207s "pg_catalog".pg_namespace PGN 207s where public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) = v_tab_fqname_quoted 207s and PGN.oid = PGC.relnamespace) is null then 207s raise exception 'Slony-I: table % not found', v_tab_fqname_quoted; 207s end if; 207s 207s -- 207s -- Lookup the tables primary key or the specified unique index 207s -- 207s if p_idx_name isnull then 207s raise exception 'Slony-I: index name must be specified'; 207s else 207s select PGXC.relname, PGX.indexrelid, PGX.indkey 207s into v_idxrow 207s from "pg_catalog".pg_class PGC, 207s "pg_catalog".pg_namespace PGN, 207s "pg_catalog".pg_index PGX, 207s "pg_catalog".pg_class PGXC 207s where public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) = v_tab_fqname_quoted 207s and PGN.oid = PGC.relnamespace 207s and PGX.indrelid = PGC.oid 207s and PGX.indexrelid = PGXC.oid 207s and PGX.indisunique 207s and public.slon_quote_brute(PGXC.relname) = v_idx_name_quoted; 207s if not found then 207s raise exception 'Slony-I: table % has no unique index %', 207s v_tab_fqname_quoted, v_idx_name_quoted; 207s end if; 207s end if; 207s 207s -- 207s -- Loop over the tables attributes and check if they are 207s -- index attributes. If so, add a "k" to the return value, 207s -- otherwise add a "v". 207s -- 207s for v_attrow in select PGA.attnum, PGA.attname 207s from "pg_catalog".pg_class PGC, 207s "pg_catalog".pg_namespace PGN, 207s "pg_catalog".pg_attribute PGA 207s where public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) = v_tab_fqname_quoted 207s and PGN.oid = PGC.relnamespace 207s and PGA.attrelid = PGC.oid 207s and not PGA.attisdropped 207s and PGA.attnum > 0 207s order by attnum 207s loop 207s v_attfound = 'f'; 207s 207s v_i := 0; 207s loop 207s select indkey[v_i] into v_attno from "pg_catalog".pg_index 207s where indexrelid = v_idxrow.indexrelid; 207s if v_attno isnull or v_attno = 0 then 207s exit; 207s end if; 207s if v_attrow.attnum = v_attno then 207s v_attfound = 't'; 207s exit; 207s end if; 207s v_i := v_i + 1; 207s end loop; 207s 207s if v_attfound then 207s v_attkind := v_attkind || 'k'; 207s else 207s v_attkind := v_attkind || 'v'; 207s end if; 207s end loop; 207s 207s -- Strip off trailing v characters as they are not needed by the logtrigger 207s v_attkind := pg_catalog.rtrim(v_attkind, 'v'); 207s 207s -- 207s -- Return the resulting attkind 207s -- 207s return v_attkind; 207s end; 207s $$ language plpgsql called on null input; 207s CREATE FUNCTION 207s comment on function public.determineAttkindUnique(p_tab_fqname text, p_idx_name name) is 207s 'determineAttKindUnique (tab_fqname, indexname) 207s 207s Given a tablename, return the Slony-I specific attkind (used for the 207s log trigger) of the table. Use the specified unique index or the 207s primary key (if indexname is NULL).'; 207s COMMENT 207s create or replace function public.RebuildListenEntries() 207s returns int 207s as $$ 207s declare 207s v_row record; 207s v_cnt integer; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s -- First remove the entire configuration 207s delete from public.sl_listen; 207s 207s -- Second populate the sl_listen configuration with a full 207s -- network of all possible paths. 207s insert into public.sl_listen 207s (li_origin, li_provider, li_receiver) 207s select pa_server, pa_server, pa_client from public.sl_path; 207s while true loop 207s insert into public.sl_listen 207s (li_origin, li_provider, li_receiver) 207s select distinct li_origin, pa_server, pa_client 207s from public.sl_listen, public.sl_path 207s where li_receiver = pa_server 207s and li_origin <> pa_client 207s and pa_conninfo<>'' 207s except 207s select li_origin, li_provider, li_receiver 207s from public.sl_listen; 207s 207s if not found then 207s exit; 207s end if; 207s end loop; 207s 207s -- We now replace specific event-origin,receiver combinations 207s -- with a configuration that tries to avoid events arriving at 207s -- a node before the data provider actually has the data ready. 207s 207s -- Loop over every possible pair of receiver and event origin 207s for v_row in select N1.no_id as receiver, N2.no_id as origin, 207s N2.no_failed as failed 207s from public.sl_node as N1, public.sl_node as N2 207s where N1.no_id <> N2.no_id 207s loop 207s -- 1st choice: 207s -- If we use the event origin as a data provider for any 207s -- set that originates on that very node, we are a direct 207s -- subscriber to that origin and listen there only. 207s if exists (select true from public.sl_set, public.sl_subscribe , public.sl_node p 207s where set_origin = v_row.origin 207s and sub_set = set_id 207s and sub_provider = v_row.origin 207s and sub_receiver = v_row.receiver 207s and sub_active 207s and p.no_active 207s and p.no_id=sub_provider 207s ) 207s then 207s delete from public.sl_listen 207s where li_origin = v_row.origin 207s and li_receiver = v_row.receiver; 207s insert into public.sl_listen (li_origin, li_provider, li_receiver) 207s values (v_row.origin, v_row.origin, v_row.receiver); 207s 207s -- 2nd choice: 207s -- If we are subscribed to any set originating on this 207s -- event origin, we want to listen on all data providers 207s -- we use for this origin. We are a cascaded subscriber 207s -- for sets from this node. 207s else 207s if exists (select true from public.sl_set, public.sl_subscribe, 207s public.sl_node provider 207s where set_origin = v_row.origin 207s and sub_set = set_id 207s and sub_provider=provider.no_id 207s and provider.no_failed = false 207s and sub_receiver = v_row.receiver 207s and sub_active) 207s then 207s delete from public.sl_listen 207s where li_origin = v_row.origin 207s and li_receiver = v_row.receiver; 207s insert into public.sl_listen (li_origin, li_provider, li_receiver) 207s select distinct set_origin, sub_provider, v_row.receiver 207s from public.sl_set, public.sl_subscribe 207s where set_origin = v_row.origin 207s and sub_set = set_id 207s and sub_receiver = v_row.receiver 207s and sub_active; 207s end if; 207s end if; 207s 207s if v_row.failed then 207s 207s --for every failed node we delete all sl_listen entries 207s --except via providers (listed in sl_subscribe) 207s --or failover candidates (sl_failover_targets) 207s --we do this to prevent a non-failover candidate 207s --that is more ahead of the failover candidate from 207s --sending events to the failover candidate that 207s --are 'too far ahead' 207s 207s --if the failed node is not an origin for any 207s --node then we don't delete all listen paths 207s --for events from it. Instead we leave 207s --the listen network alone. 207s 207s select count(*) into v_cnt from public.sl_subscribe sub, 207s public.sl_set s 207s where s.set_origin=v_row.origin and s.set_id=sub.sub_set; 207s if v_cnt > 0 then 207s delete from public.sl_listen where 207s li_origin=v_row.origin and 207s li_receiver=v_row.receiver 207s and li_provider not in 207s (select sub_provider from 207s public.sl_subscribe, 207s public.sl_set where 207s sub_set=set_id 207s and set_origin=v_row.origin); 207s end if; 207s end if; 207s -- insert into public.sl_listen 207s -- (li_origin,li_provider,li_receiver) 207s -- SELECT v_row.origin, pa_server 207s -- ,v_row.receiver 207s -- FROM public.sl_path where 207s -- pa_client=v_row.receiver 207s -- and (v_row.origin,pa_server,v_row.receiver) not in 207s -- (select li_origin,li_provider,li_receiver 207s -- from public.sl_listen); 207s -- end if; 207s end loop ; 207s 207s return null ; 207s end ; 207s $$ language 'plpgsql'; 207s CREATE FUNCTION 207s comment on function public.RebuildListenEntries() is 207s 'RebuildListenEntries() 207s 207s Invoked by various subscription and path modifying functions, this 207s rewrites the sl_listen entries, adding in all the ones required to 207s allow communications between nodes in the Slony-I cluster.'; 207s COMMENT 207s create or replace function public.generate_sync_event(p_interval interval) 207s returns int4 207s as $$ 207s declare 207s v_node_row record; 207s 207s BEGIN 207s select 1 into v_node_row from public.sl_event 207s where ev_type = 'SYNC' and ev_origin = public.getLocalNodeId('_main') 207s and ev_timestamp > now() - p_interval limit 1; 207s if not found then 207s -- If there has been no SYNC in the last interval, then push one 207s perform public.createEvent('_main', 'SYNC', NULL); 207s return 1; 207s else 207s return 0; 207s end if; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.generate_sync_event(p_interval interval) is 207s 'Generate a sync event if there has not been one in the requested interval, and this is a provider node.'; 207s COMMENT 207s drop function if exists public.updateRelname(int4, int4); 207s DROP FUNCTION 207s create or replace function public.updateRelname () 207s returns int4 207s as $$ 207s declare 207s v_no_id int4; 207s v_set_origin int4; 207s begin 207s -- ---- 207s -- Grab the central configuration lock 207s -- ---- 207s lock table public.sl_config_lock; 207s 207s update public.sl_table set 207s tab_relname = PGC.relname, tab_nspname = PGN.nspname 207s from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN 207s where public.sl_table.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid and 207s (tab_relname <> PGC.relname or tab_nspname <> PGN.nspname); 207s update public.sl_sequence set 207s seq_relname = PGC.relname, seq_nspname = PGN.nspname 207s from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN 207s where public.sl_sequence.seq_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid and 207s (seq_relname <> PGC.relname or seq_nspname <> PGN.nspname); 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.updateRelname() is 207s 'updateRelname()'; 207s COMMENT 207s drop function if exists public.updateReloid (int4, int4); 207s DROP FUNCTION 207s NOTICE: function public.updaterelname(int4,int4) does not exist, skipping 207s create or replace function public.updateReloid (p_set_id int4, p_only_on_node int4) 207s returns bigint 207s as $$ 207s declare 207s v_no_id int4; 207s v_set_origin int4; 207s prec record; 207s begin 207s -- ---- 207s -- Check that we either are the set origin or a current 207s -- subscriber of the set. 207s -- ---- 207s v_no_id := public.getLocalNodeId('_main'); 207s select set_origin into v_set_origin 207s from public.sl_set 207s where set_id = p_set_id 207s for update; 207s if not found then 207s raise exception 'Slony-I: set % not found', p_set_id; 207s end if; 207s if v_set_origin <> v_no_id 207s and not exists (select 1 from public.sl_subscribe 207s where sub_set = p_set_id 207s and sub_receiver = v_no_id) 207s then 207s return 0; 207s end if; 207s 207s -- ---- 207s -- If execution on only one node is requested, check that 207s -- we are that node. 207s -- ---- 207s if p_only_on_node > 0 and p_only_on_node <> v_no_id then 207s return 0; 207s end if; 207s 207s -- Update OIDs for tables to values pulled from non-table objects in pg_class 207s -- This ensures that we won't have collisions when repairing the oids 207s for prec in select tab_id from public.sl_table loop 207s update public.sl_table set tab_reloid = (select oid from pg_class pc where relkind <> 'r' and not exists (select 1 from public.sl_table t2 where t2.tab_reloid = pc.oid) limit 1) 207s where tab_id = prec.tab_id; 207s end loop; 207s 207s for prec in select tab_id, tab_relname, tab_nspname from public.sl_table loop 207s update public.sl_table set 207s tab_reloid = (select PGC.oid 207s from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN 207s where public.slon_quote_brute(PGC.relname) = public.slon_quote_brute(prec.tab_relname) 207s and PGC.relnamespace = PGN.oid 207s and public.slon_quote_brute(PGN.nspname) = public.slon_quote_brute(prec.tab_nspname)) 207s where tab_id = prec.tab_id; 207s end loop; 207s 207s for prec in select seq_id from public.sl_sequence loop 207s update public.sl_sequence set seq_reloid = (select oid from pg_class pc where relkind <> 'S' and not exists (select 1 from public.sl_sequence t2 where t2.seq_reloid = pc.oid) limit 1) 207s where seq_id = prec.seq_id; 207s end loop; 207s 207s for prec in select seq_id, seq_relname, seq_nspname from public.sl_sequence loop 207s update public.sl_sequence set 207s seq_reloid = (select PGC.oid 207s from pg_catalog.pg_class PGC, pg_catalog.pg_namespace PGN 207s where public.slon_quote_brute(PGC.relname) = public.slon_quote_brute(prec.seq_relname) 207s and PGC.relnamespace = PGN.oid 207s and public.slon_quote_brute(PGN.nspname) = public.slon_quote_brute(prec.seq_nspname)) 207s where seq_id = prec.seq_id; 207s end loop; 207s 207s return 1; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.updateReloid(p_set_id int4, p_only_on_node int4) is 207s 'updateReloid(set_id, only_on_node) 207s 207s Updates the respective reloids in sl_table and sl_seqeunce based on 207s their respective FQN'; 207s COMMENT 207s create or replace function public.logswitch_start() 207s returns int4 as $$ 207s DECLARE 207s v_current_status int4; 207s BEGIN 207s -- ---- 207s -- Get the current log status. 207s -- ---- 207s select last_value into v_current_status from public.sl_log_status; 207s 207s -- ---- 207s -- status = 0: sl_log_1 active, sl_log_2 clean 207s -- Initiate a switch to sl_log_2. 207s -- ---- 207s if v_current_status = 0 then 207s perform "pg_catalog".setval('public.sl_log_status', 3); 207s perform public.registry_set_timestamp( 207s 'logswitch.laststart', now()); 207s raise notice 'Slony-I: Logswitch to sl_log_2 initiated'; 207s return 2; 207s end if; 207s 207s -- ---- 207s -- status = 1: sl_log_2 active, sl_log_1 clean 207s -- Initiate a switch to sl_log_1. 207s -- ---- 207s if v_current_status = 1 then 207s perform "pg_catalog".setval('public.sl_log_status', 2); 207s perform public.registry_set_timestamp( 207s 'logswitch.laststart', now()); 207s raise notice 'Slony-I: Logswitch to sl_log_1 initiated'; 207s return 1; 207s end if; 207s 207s raise exception 'Previous logswitch still in progress'; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.logswitch_start() is 207s 'logswitch_start() 207s 207s Initiate a log table switch if none is in progress'; 207s COMMENT 207s create or replace function public.logswitch_finish() 207s returns int4 as $$ 207s DECLARE 207s v_current_status int4; 207s v_dummy record; 207s v_origin int8; 207s v_seqno int8; 207s v_xmin bigint; 207s v_purgeable boolean; 207s BEGIN 207s -- ---- 207s -- Get the current log status. 207s -- ---- 207s select last_value into v_current_status from public.sl_log_status; 207s 207s -- ---- 207s -- status value 0 or 1 means that there is no log switch in progress 207s -- ---- 207s if v_current_status = 0 or v_current_status = 1 then 207s return 0; 207s end if; 207s 207s -- ---- 207s -- status = 2: sl_log_1 active, cleanup sl_log_2 207s -- ---- 207s if v_current_status = 2 then 207s v_purgeable := 'true'; 207s 207s -- ---- 207s -- Attempt to lock sl_log_2 in order to make sure there are no other transactions 207s -- currently writing to it. Exit if it is still in use. This prevents TRUNCATE from 207s -- blocking writers to sl_log_2 while it is waiting for a lock. It also prevents it 207s -- immediately truncating log data generated inside the transaction which was active 207s -- when logswitch_finish() was called (and was blocking TRUNCATE) as soon as that 207s -- transaction is committed. 207s -- ---- 207s begin 207s lock table public.sl_log_2 in access exclusive mode nowait; 207s exception when lock_not_available then 207s raise notice 'Slony-I: could not lock sl_log_2 - sl_log_2 not truncated'; 207s return -1; 207s end; 207s 207s -- ---- 207s -- The cleanup thread calls us after it did the delete and 207s -- vacuum of both log tables. If sl_log_2 is empty now, we 207s -- can truncate it and the log switch is done. 207s -- ---- 207s for v_origin, v_seqno, v_xmin in 207s select ev_origin, ev_seqno, "pg_catalog".txid_snapshot_xmin(ev_snapshot) from public.sl_event 207s where (ev_origin, ev_seqno) in (select ev_origin, min(ev_seqno) from public.sl_event where ev_type = 'SYNC' group by ev_origin) 207s loop 207s if exists (select 1 from public.sl_log_2 where log_origin = v_origin and log_txid >= v_xmin limit 1) then 207s v_purgeable := 'false'; 207s end if; 207s end loop; 207s if not v_purgeable then 207s -- ---- 207s -- Found a row ... log switch is still in progress. 207s -- ---- 207s raise notice 'Slony-I: log switch to sl_log_1 still in progress - sl_log_2 not truncated'; 207s return -1; 207s end if; 207s 207s raise notice 'Slony-I: log switch to sl_log_1 complete - truncate sl_log_2'; 207s truncate public.sl_log_2; 207s if exists (select * from "pg_catalog".pg_class c, "pg_catalog".pg_namespace n, "pg_catalog".pg_attribute a where c.relname = 'sl_log_2' and n.oid = c.relnamespace and a.attrelid = c.oid and a.attname = 'oid') then 207s execute 'alter table public.sl_log_2 set without oids;'; 207s end if; 207s perform "pg_catalog".setval('public.sl_log_status', 0); 207s -- Run addPartialLogIndices() to try to add indices to unused sl_log_? table 207s perform public.addPartialLogIndices(); 207s 207s return 1; 207s end if; 207s 207s -- ---- 207s -- status = 3: sl_log_2 active, cleanup sl_log_1 207s -- ---- 207s if v_current_status = 3 then 207s v_purgeable := 'true'; 207s 207s -- ---- 207s -- Attempt to lock sl_log_1 in order to make sure there are no other transactions 207s -- currently writing to it. Exit if it is still in use. This prevents TRUNCATE from 207s -- blocking writes to sl_log_1 while it is waiting for a lock. It also prevents it 207s -- immediately truncating log data generated inside the transaction which was active 207s -- when logswitch_finish() was called (and was blocking TRUNCATE) as soon as that 207s -- transaction is committed. 207s -- ---- 207s begin 207s lock table public.sl_log_1 in access exclusive mode nowait; 207s exception when lock_not_available then 207s raise notice 'Slony-I: could not lock sl_log_1 - sl_log_1 not truncated'; 207s return -1; 207s end; 207s 207s -- ---- 207s -- The cleanup thread calls us after it did the delete and 207s -- vacuum of both log tables. If sl_log_2 is empty now, we 207s -- can truncate it and the log switch is done. 207s -- ---- 207s for v_origin, v_seqno, v_xmin in 207s select ev_origin, ev_seqno, "pg_catalog".txid_snapshot_xmin(ev_snapshot) from public.sl_event 207s where (ev_origin, ev_seqno) in (select ev_origin, min(ev_seqno) from public.sl_event where ev_type = 'SYNC' group by ev_origin) 207s loop 207s if (exists (select 1 from public.sl_log_1 where log_origin = v_origin and log_txid >= v_xmin limit 1)) then 207s v_purgeable := 'false'; 207s end if; 207s end loop; 207s if not v_purgeable then 207s -- ---- 207s -- Found a row ... log switch is still in progress. 207s -- ---- 207s raise notice 'Slony-I: log switch to sl_log_2 still in progress - sl_log_1 not truncated'; 207s return -1; 207s end if; 207s 207s raise notice 'Slony-I: log switch to sl_log_2 complete - truncate sl_log_1'; 207s truncate public.sl_log_1; 207s if exists (select * from "pg_catalog".pg_class c, "pg_catalog".pg_namespace n, "pg_catalog".pg_attribute a where c.relname = 'sl_log_1' and n.oid = c.relnamespace and a.attrelid = c.oid and a.attname = 'oid') then 207s execute 'alter table public.sl_log_1 set without oids;'; 207s end if; 207s perform "pg_catalog".setval('public.sl_log_status', 1); 207s -- Run addPartialLogIndices() to try to add indices to unused sl_log_? table 207s perform public.addPartialLogIndices(); 207s return 2; 207s end if; 207s END; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.logswitch_finish() is 207s 'logswitch_finish() 207s 207s Attempt to finalize a log table switch in progress 207s return values: 207s -1 if switch in progress, but not complete 207s 0 if no switch in progress 207s 1 if performed truncate on sl_log_2 207s 2 if performed truncate on sl_log_1 207s '; 207s COMMENT 207s NOTICE: function public.updatereloid(int4,int4) does not exist, skipping 207s create or replace function public.addPartialLogIndices () returns integer as $$ 207s DECLARE 207s v_current_status int4; 207s v_log int4; 207s v_dummy record; 207s v_dummy2 record; 207s idef text; 207s v_count int4; 207s v_iname text; 207s v_ilen int4; 207s v_maxlen int4; 207s BEGIN 207s v_count := 0; 207s select last_value into v_current_status from public.sl_log_status; 207s 207s -- If status is 2 or 3 --> in process of cleanup --> unsafe to create indices 207s if v_current_status in (2, 3) then 207s return 0; 207s end if; 207s 207s if v_current_status = 0 then -- Which log should get indices? 207s v_log := 2; 207s else 207s v_log := 1; 207s end if; 207s -- PartInd_test_db_sl_log_2-node-1 207s -- Add missing indices... 207s for v_dummy in select distinct set_origin from public.sl_set loop 207s v_iname := 'PartInd_main_sl_log_' || v_log::text || '-node-' 207s || v_dummy.set_origin::text; 207s -- raise notice 'Consider adding partial index % on sl_log_%', v_iname, v_log; 207s -- raise notice 'schema: [_main] tablename:[sl_log_%]', v_log; 207s select * into v_dummy2 from pg_catalog.pg_indexes where tablename = 'sl_log_' || v_log::text and indexname = v_iname; 207s if not found then 207s -- raise notice 'index was not found - add it!'; 207s v_iname := 'PartInd_main_sl_log_' || v_log::text || '-node-' || v_dummy.set_origin::text; 207s v_ilen := pg_catalog.length(v_iname); 207s v_maxlen := pg_catalog.current_setting('max_identifier_length'::text)::int4; 207s if v_ilen > v_maxlen then 207s raise exception 'Length of proposed index name [%] > max_identifier_length [%] - cluster name probably too long', v_ilen, v_maxlen; 207s end if; 207s 207s idef := 'create index "' || v_iname || 207s '" on public.sl_log_' || v_log::text || ' USING btree(log_txid) where (log_origin = ' || v_dummy.set_origin::text || ');'; 207s execute idef; 207s v_count := v_count + 1; 207s else 207s -- raise notice 'Index % already present - skipping', v_iname; 207s end if; 207s end loop; 207s 207s -- Remove unneeded indices... 207s for v_dummy in select indexname from pg_catalog.pg_indexes i where i.tablename = 'sl_log_' || v_log::text and 207s i.indexname like ('PartInd_main_sl_log_' || v_log::text || '-node-%') and 207s not exists (select 1 from public.sl_set where 207s i.indexname = 'PartInd_main_sl_log_' || v_log::text || '-node-' || set_origin::text) 207s loop 207s -- raise notice 'Dropping obsolete index %d', v_dummy.indexname; 207s idef := 'drop index public."' || v_dummy.indexname || '";'; 207s execute idef; 207s v_count := v_count - 1; 207s end loop; 207s return v_count; 207s END 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.addPartialLogIndices () is 207s 'Add partial indexes, if possible, to the unused sl_log_? table for 207s all origin nodes, and drop any that are no longer needed. 207s 207s This function presently gets run any time set origins are manipulated 207s (FAILOVER, STORE SET, MOVE SET, DROP SET), as well as each time the 207s system switches between sl_log_1 and sl_log_2.'; 207s COMMENT 207s create or replace function public.check_table_field_exists (p_namespace text, p_table text, p_field text) 207s returns bool as $$ 207s BEGIN 207s return exists ( 207s select 1 from "information_schema".columns 207s where table_schema = p_namespace 207s and table_name = p_table 207s and column_name = p_field 207s ); 207s END;$$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.check_table_field_exists (p_namespace text, p_table text, p_field text) 207s is 'Check if a table has a specific attribute'; 207s COMMENT 207s create or replace function public.add_missing_table_field (p_namespace text, p_table text, p_field text, p_type text) 207s returns bool as $$ 207s DECLARE 207s v_row record; 207s v_query text; 207s BEGIN 207s if not public.check_table_field_exists(p_namespace, p_table, p_field) then 207s raise notice 'Upgrade table %.% - add field %', p_namespace, p_table, p_field; 207s v_query := 'alter table ' || p_namespace || '.' || p_table || ' add column '; 207s v_query := v_query || p_field || ' ' || p_type || ';'; 207s execute v_query; 207s return 't'; 207s else 207s return 'f'; 207s end if; 207s END;$$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.add_missing_table_field (p_namespace text, p_table text, p_field text, p_type text) 207s is 'Add a column of a given type to a table if it is missing'; 207s COMMENT 207s create or replace function public.upgradeSchema(p_old text) 207s returns text as $$ 207s declare 207s v_tab_row record; 207s v_query text; 207s v_keepstatus text; 207s begin 207s -- If old version is pre-2.0, then we require a special upgrade process 207s if p_old like '1.%' then 207s raise exception 'Upgrading to Slony-I 2.x requires running slony_upgrade_20'; 207s end if; 207s 207s perform public.upgradeSchemaAddTruncateTriggers(); 207s 207s -- Change all Slony-I-defined columns that are "timestamp without time zone" to "timestamp *WITH* time zone" 207s if exists (select 1 from information_schema.columns c 207s where table_schema = '_main' and data_type = 'timestamp without time zone' 207s and exists (select 1 from information_schema.tables t where t.table_schema = c.table_schema and t.table_name = c.table_name and t.table_type = 'BASE TABLE') 207s and (c.table_name, c.column_name) in (('sl_confirm', 'con_timestamp'), ('sl_event', 'ev_timestamp'), ('sl_registry', 'reg_timestamp'),('sl_archive_counter', 'ac_timestamp'))) 207s then 207s 207s -- Preserve sl_status 207s select pg_get_viewdef('public.sl_status') into v_keepstatus; 207s execute 'drop view sl_status'; 207s for v_tab_row in select table_schema, table_name, column_name from information_schema.columns c 207s where table_schema = '_main' and data_type = 'timestamp without time zone' 207s and exists (select 1 from information_schema.tables t where t.table_schema = c.table_schema and t.table_name = c.table_name and t.table_type = 'BASE TABLE') 207s and (table_name, column_name) in (('sl_confirm', 'con_timestamp'), ('sl_event', 'ev_timestamp'), ('sl_registry', 'reg_timestamp'),('sl_archive_counter', 'ac_timestamp')) 207s loop 207s raise notice 'Changing Slony-I column [%.%] to timestamp WITH time zone', v_tab_row.table_name, v_tab_row.column_name; 207s v_query := 'alter table ' || public.slon_quote_brute(v_tab_row.table_schema) || 207s '.' || v_tab_row.table_name || ' alter column ' || v_tab_row.column_name || 207s ' type timestamp with time zone;'; 207s execute v_query; 207s end loop; 207s -- restore sl_status 207s execute 'create view sl_status as ' || v_keepstatus; 207s end if; 207s 207s if not exists (select 1 from information_schema.tables where table_schema = '_main' and table_name = 'sl_components') then 207s v_query := ' 207s create table public.sl_components ( 207s co_actor text not null primary key, 207s co_pid integer not null, 207s co_node integer not null, 207s co_connection_pid integer not null, 207s co_activity text, 207s co_starttime timestamptz not null, 207s co_event bigint, 207s co_eventtype text 207s ) without oids; 207s '; 207s execute v_query; 207s end if; 207s 207s 207s 207s 207s 207s if not exists (select 1 from information_schema.tables t where table_schema = '_main' and table_name = 'sl_event_lock') then 207s v_query := 'create table public.sl_event_lock (dummy integer);'; 207s execute v_query; 207s end if; 207s 207s if not exists (select 1 from information_schema.tables t 207s where table_schema = '_main' 207s and table_name = 'sl_apply_stats') then 207s v_query := ' 207s create table public.sl_apply_stats ( 207s as_origin int4, 207s as_num_insert int8, 207s as_num_update int8, 207s as_num_delete int8, 207s as_num_truncate int8, 207s as_num_script int8, 207s as_num_total int8, 207s as_duration interval, 207s as_apply_first timestamptz, 207s as_apply_last timestamptz, 207s as_cache_prepare int8, 207s as_cache_hit int8, 207s as_cache_evict int8, 207s as_cache_prepare_max int8 207s ) WITHOUT OIDS;'; 207s execute v_query; 207s end if; 207s 207s -- 207s -- On the upgrade to 2.2, we change the layout of sl_log_N by 207s -- adding columns log_tablenspname, log_tablerelname, and 207s -- log_cmdupdncols as well as changing log_cmddata into 207s -- log_cmdargs, which is a text array. 207s -- 207s if not public.check_table_field_exists('_main', 'sl_log_1', 'log_cmdargs') then 207s -- 207s -- Check that the cluster is completely caught up 207s -- 207s if public.check_unconfirmed_log() then 207s raise EXCEPTION 'cannot upgrade to new sl_log_N format due to existing unreplicated data'; 207s end if; 207s 207s -- 207s -- Drop tables sl_log_1 and sl_log_2 207s -- 207s drop table public.sl_log_1; 207s drop table public.sl_log_2; 207s 207s -- 207s -- Create the new sl_log_1 207s -- 207s create table public.sl_log_1 ( 207s log_origin int4, 207s log_txid bigint, 207s log_tableid int4, 207s log_actionseq int8, 207s log_tablenspname text, 207s log_tablerelname text, 207s log_cmdtype "char", 207s log_cmdupdncols int4, 207s log_cmdargs text[] 207s ) without oids; 207s create index sl_log_1_idx1 on public.sl_log_1 207s (log_origin, log_txid, log_actionseq); 207s 207s comment on table public.sl_log_1 is 'Stores each change to be propagated to subscriber nodes'; 207s comment on column public.sl_log_1.log_origin is 'Origin node from which the change came'; 207s comment on column public.sl_log_1.log_txid is 'Transaction ID on the origin node'; 207s comment on column public.sl_log_1.log_tableid is 'The table ID (from sl_table.tab_id) that this log entry is to affect'; 207s comment on column public.sl_log_1.log_actionseq is 'The sequence number in which actions will be applied on replicas'; 207s comment on column public.sl_log_1.log_tablenspname is 'The schema name of the table affected'; 207s comment on column public.sl_log_1.log_tablerelname is 'The table name of the table affected'; 207s comment on column public.sl_log_1.log_cmdtype is 'Replication action to take. U = Update, I = Insert, D = DELETE, T = TRUNCATE'; 207s comment on column public.sl_log_1.log_cmdupdncols is 'For cmdtype=U the number of updated columns in cmdargs'; 207s comment on column public.sl_log_1.log_cmdargs is 'The data needed to perform the log action on the replica'; 207s 207s -- 207s -- Create the new sl_log_2 207s -- 207s create table public.sl_log_2 ( 207s log_origin int4, 207s log_txid bigint, 207s log_tableid int4, 207s log_actionseq int8, 207s log_tablenspname text, 207s log_tablerelname text, 207s log_cmdtype "char", 207s log_cmdupdncols int4, 207s log_cmdargs text[] 207s ) without oids; 207s create index sl_log_2_idx1 on public.sl_log_2 207s (log_origin, log_txid, log_actionseq); 207s 207s comment on table public.sl_log_2 is 'Stores each change to be propagated to subscriber nodes'; 207s comment on column public.sl_log_2.log_origin is 'Origin node from which the change came'; 207s comment on column public.sl_log_2.log_txid is 'Transaction ID on the origin node'; 207s comment on column public.sl_log_2.log_tableid is 'The table ID (from sl_table.tab_id) that this log entry is to affect'; 207s comment on column public.sl_log_2.log_actionseq is 'The sequence number in which actions will be applied on replicas'; 207s comment on column public.sl_log_2.log_tablenspname is 'The schema name of the table affected'; 207s comment on column public.sl_log_2.log_tablerelname is 'The table name of the table affected'; 207s comment on column public.sl_log_2.log_cmdtype is 'Replication action to take. U = Update, I = Insert, D = DELETE, T = TRUNCATE'; 207s comment on column public.sl_log_2.log_cmdupdncols is 'For cmdtype=U the number of updated columns in cmdargs'; 207s comment on column public.sl_log_2.log_cmdargs is 'The data needed to perform the log action on the replica'; 207s 207s create table public.sl_log_script ( 207s log_origin int4, 207s log_txid bigint, 207s log_actionseq int8, 207s log_cmdtype "char", 207s log_cmdargs text[] 207s ) WITHOUT OIDS; 207s create index sl_log_script_idx1 on public.sl_log_script 207s (log_origin, log_txid, log_actionseq); 207s 207s comment on table public.sl_log_script is 'Captures SQL script queries to be propagated to subscriber nodes'; 207s comment on column public.sl_log_script.log_origin is 'Origin name from which the change came'; 207s comment on column public.sl_log_script.log_txid is 'Transaction ID on the origin node'; 207s comment on column public.sl_log_script.log_actionseq is 'The sequence number in which actions will be applied on replicas'; 207s comment on column public.sl_log_2.log_cmdtype is 'Replication action to take. S = Script statement, s = Script complete'; 207s comment on column public.sl_log_script.log_cmdargs is 'The DDL statement, optionally followed by selected nodes to execute it on.'; 207s 207s -- 207s -- Put the log apply triggers back onto sl_log_1/2 207s -- 207s create trigger apply_trigger 207s before INSERT on public.sl_log_1 207s for each row execute procedure public.logApply('_main'); 207s alter table public.sl_log_1 207s enable replica trigger apply_trigger; 207s create trigger apply_trigger 207s before INSERT on public.sl_log_2 207s for each row execute procedure public.logApply('_main'); 207s alter table public.sl_log_2 207s enable replica trigger apply_trigger; 207s end if; 207s if not exists (select 1 from information_schema.routines where routine_schema = '_main' and routine_name = 'string_agg') then 207s CREATE AGGREGATE public.string_agg(text) ( 207s SFUNC=public.agg_text_sum, 207s STYPE=text, 207s INITCOND='' 207s ); 207s end if; 207s if not exists (select 1 from information_schema.views where table_schema='_main' and table_name='sl_failover_targets') then 207s create view public.sl_failover_targets as 207s select set_id, 207s set_origin as set_origin, 207s sub1.sub_receiver as backup_id 207s 207s FROM 207s public.sl_subscribe sub1 207s ,public.sl_set set1 207s where 207s sub1.sub_set=set_id 207s and sub1.sub_forward=true 207s --exclude candidates where the set_origin 207s --has a path a node but the failover 207s --candidate has no path to that node 207s and sub1.sub_receiver not in 207s (select p1.pa_client from 207s public.sl_path p1 207s left outer join public.sl_path p2 on 207s (p2.pa_client=p1.pa_client 207s and p2.pa_server=sub1.sub_receiver) 207s where p2.pa_client is null 207s and p1.pa_server=set_origin 207s and p1.pa_client<>sub1.sub_receiver 207s ) 207s and sub1.sub_provider=set_origin 207s --exclude any subscribers that are not 207s --direct subscribers of all sets on the 207s --origin 207s and sub1.sub_receiver not in 207s (select direct_recv.sub_receiver 207s from 207s 207s (--all direct receivers of the first set 207s select subs2.sub_receiver 207s from public.sl_subscribe subs2 207s where subs2.sub_provider=set1.set_origin 207s and subs2.sub_set=set1.set_id) as 207s direct_recv 207s inner join 207s (--all other sets from the origin 207s select set_id from public.sl_set set2 207s where set2.set_origin=set1.set_origin 207s and set2.set_id<>sub1.sub_set) 207s as othersets on(true) 207s left outer join public.sl_subscribe subs3 207s on(subs3.sub_set=othersets.set_id 207s and subs3.sub_forward=true 207s and subs3.sub_provider=set1.set_origin 207s and direct_recv.sub_receiver=subs3.sub_receiver) 207s where subs3.sub_receiver is null 207s ); 207s end if; 207s 207s if not public.check_table_field_exists('_main', 'sl_node', 'no_failed') then 207s alter table public.sl_node add column no_failed bool; 207s update public.sl_node set no_failed=false; 207s end if; 207s return p_old; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s create or replace function public.check_unconfirmed_log () 207s returns bool as $$ 207s declare 207s v_rc bool = false; 207s v_error bool = false; 207s v_origin integer; 207s v_allconf bigint; 207s v_allsnap txid_snapshot; 207s v_count bigint; 207s begin 207s -- 207s -- Loop over all nodes that are the origin of at least one set 207s -- 207s for v_origin in select distinct set_origin as no_id 207s from public.sl_set loop 207s -- 207s -- Per origin determine which is the highest event seqno 207s -- that is confirmed by all subscribers to any of the 207s -- origins sets. 207s -- 207s select into v_allconf min(max_seqno) from ( 207s select con_received, max(con_seqno) as max_seqno 207s from public.sl_confirm 207s where con_origin = v_origin 207s and con_received in ( 207s select distinct sub_receiver 207s from public.sl_set as SET, 207s public.sl_subscribe as SUB 207s where SET.set_id = SUB.sub_set 207s and SET.set_origin = v_origin 207s ) 207s group by con_received 207s ) as maxconfirmed; 207s if not found then 207s raise NOTICE 'check_unconfirmed_log(): cannot determine highest ev_seqno for node % confirmed by all subscribers', v_origin; 207s v_error = true; 207s continue; 207s end if; 207s 207s -- 207s -- Get the txid snapshot that corresponds with that event 207s -- 207s select into v_allsnap ev_snapshot 207s from public.sl_event 207s where ev_origin = v_origin 207s and ev_seqno = v_allconf; 207s if not found then 207s raise NOTICE 'check_unconfirmed_log(): cannot find event %,% in sl_event', v_origin, v_allconf; 207s v_error = true; 207s continue; 207s end if; 207s 207s -- 207s -- Count the number of log rows that appeard after that event. 207s -- 207s select into v_count count(*) from ( 207s select 1 from public.sl_log_1 207s where log_origin = v_origin 207s and log_txid >= "pg_catalog".txid_snapshot_xmax(v_allsnap) 207s union all 207s select 1 from public.sl_log_1 207s where log_origin = v_origin 207s and log_txid in ( 207s select * from "pg_catalog".txid_snapshot_xip(v_allsnap) 207s ) 207s union all 207s select 1 from public.sl_log_2 207s where log_origin = v_origin 207s and log_txid >= "pg_catalog".txid_snapshot_xmax(v_allsnap) 207s union all 207s select 1 from public.sl_log_2 207s where log_origin = v_origin 207s and log_txid in ( 207s select * from "pg_catalog".txid_snapshot_xip(v_allsnap) 207s ) 207s ) as cnt; 207s 207s if v_count > 0 then 207s raise NOTICE 'check_unconfirmed_log(): origin % has % log rows that have not propagated to all subscribers yet', v_origin, v_count; 207s v_rc = true; 207s end if; 207s end loop; 207s 207s if v_error then 207s raise EXCEPTION 'check_unconfirmed_log(): aborting due to previous inconsistency'; 207s end if; 207s 207s return v_rc; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s set search_path to public 207s ; 207s SET 207s comment on function public.upgradeSchema(p_old text) is 207s 'Called during "update functions" by slonik to perform schema changes'; 207s COMMENT 207s create or replace view public.sl_status as select 207s E.ev_origin as st_origin, 207s C.con_received as st_received, 207s E.ev_seqno as st_last_event, 207s E.ev_timestamp as st_last_event_ts, 207s C.con_seqno as st_last_received, 207s C.con_timestamp as st_last_received_ts, 207s CE.ev_timestamp as st_last_received_event_ts, 207s E.ev_seqno - C.con_seqno as st_lag_num_events, 207s current_timestamp - CE.ev_timestamp as st_lag_time 207s from public.sl_event E, public.sl_confirm C, 207s public.sl_event CE 207s where E.ev_origin = C.con_origin 207s and CE.ev_origin = E.ev_origin 207s and CE.ev_seqno = C.con_seqno 207s and (E.ev_origin, E.ev_seqno) in 207s (select ev_origin, max(ev_seqno) 207s from public.sl_event 207s where ev_origin = public.getLocalNodeId('_main') 207s group by 1 207s ) 207s and (C.con_origin, C.con_received, C.con_seqno) in 207s (select con_origin, con_received, max(con_seqno) 207s from public.sl_confirm 207s where con_origin = public.getLocalNodeId('_main') 207s group by 1, 2 207s ); 207s CREATE VIEW 207s comment on view public.sl_status is 'View showing how far behind remote nodes are.'; 207s COMMENT 207s create or replace function public.copyFields(p_tab_id integer) 207s returns text 207s as $$ 207s declare 207s result text; 207s prefix text; 207s prec record; 207s begin 207s result := ''; 207s prefix := '('; -- Initially, prefix is the opening paren 207s 207s for prec in select public.slon_quote_input(a.attname) as column from public.sl_table t, pg_catalog.pg_attribute a where t.tab_id = p_tab_id and t.tab_reloid = a.attrelid and a.attnum > 0 and a.attisdropped = false order by attnum 207s loop 207s result := result || prefix || prec.column; 207s prefix := ','; -- Subsequently, prepend columns with commas 207s end loop; 207s result := result || ')'; 207s return result; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.copyFields(p_tab_id integer) is 207s 'Return a string consisting of what should be appended to a COPY statement 207s to specify fields for the passed-in tab_id. 207s 207s In PG versions > 7.3, this looks like (field1,field2,...fieldn)'; 207s COMMENT 207s create or replace function public.prepareTableForCopy(p_tab_id int4) 207s returns int4 207s as $$ 207s declare 207s v_tab_oid oid; 207s v_tab_fqname text; 207s begin 207s -- ---- 207s -- Get the OID and fully qualified name for the table 207s -- --- 207s select PGC.oid, 207s public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) as tab_fqname 207s into v_tab_oid, v_tab_fqname 207s from public.sl_table T, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where T.tab_id = p_tab_id 207s and T.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid; 207s if not found then 207s raise exception 'Table with ID % not found in sl_table', p_tab_id; 207s end if; 207s 207s -- ---- 207s -- Try using truncate to empty the table and fallback to 207s -- delete on error. 207s -- ---- 207s perform public.TruncateOnlyTable(v_tab_fqname); 207s raise notice 'truncate of % succeeded', v_tab_fqname; 207s 207s -- suppress index activity 207s perform public.disable_indexes_on_table(v_tab_oid); 207s 207s return 1; 207s exception when others then 207s raise notice 'truncate of % failed - doing delete', v_tab_fqname; 207s perform public.disable_indexes_on_table(v_tab_oid); 207s execute 'delete from only ' || public.slon_quote_input(v_tab_fqname); 207s return 0; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.prepareTableForCopy(p_tab_id int4) is 207s 'Delete all data and suppress index maintenance'; 207s COMMENT 207s create or replace function public.finishTableAfterCopy(p_tab_id int4) 207s returns int4 207s as $$ 207s declare 207s v_tab_oid oid; 207s v_tab_fqname text; 207s begin 207s -- ---- 207s -- Get the tables OID and fully qualified name 207s -- --- 207s select PGC.oid, 207s public.slon_quote_brute(PGN.nspname) || '.' || 207s public.slon_quote_brute(PGC.relname) as tab_fqname 207s into v_tab_oid, v_tab_fqname 207s from public.sl_table T, 207s "pg_catalog".pg_class PGC, "pg_catalog".pg_namespace PGN 207s where T.tab_id = p_tab_id 207s and T.tab_reloid = PGC.oid 207s and PGC.relnamespace = PGN.oid; 207s if not found then 207s raise exception 'Table with ID % not found in sl_table', p_tab_id; 207s end if; 207s 207s -- ---- 207s -- Reenable indexes and reindex the table. 207s -- ---- 207s perform public.enable_indexes_on_table(v_tab_oid); 207s execute 'reindex table ' || public.slon_quote_input(v_tab_fqname); 207s 207s return 1; 207s end; 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.finishTableAfterCopy(p_tab_id int4) is 207s 'Reenable index maintenance and reindex the table'; 207s COMMENT 207s create or replace function public.setup_vactables_type () returns integer as $$ 207s begin 207s if not exists (select 1 from pg_catalog.pg_type t, pg_catalog.pg_namespace n 207s where n.nspname = '_main' and t.typnamespace = n.oid and 207s t.typname = 'vactables') then 207s execute 'create type public.vactables as (nspname name, relname name);'; 207s end if; 207s return 1; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.setup_vactables_type () is 207s 'Function to be run as part of loading slony1_funcs.sql that creates the vactables type if it is missing'; 207s COMMENT 207s select public.setup_vactables_type(); 207s setup_vactables_type 207s ---------------------- 207s 1 207s (1 row) 207s 207s drop function public.setup_vactables_type (); 207s DROP FUNCTION 207s create or replace function public.TablesToVacuum () returns setof public.vactables as $$ 207s declare 207s prec public.vactables%rowtype; 207s begin 207s prec.nspname := '_main'; 207s prec.relname := 'sl_event'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := '_main'; 207s prec.relname := 'sl_confirm'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := '_main'; 207s prec.relname := 'sl_setsync'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := '_main'; 207s prec.relname := 'sl_seqlog'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := '_main'; 207s prec.relname := 'sl_archive_counter'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := '_main'; 207s prec.relname := 'sl_components'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := '_main'; 207s prec.relname := 'sl_log_script'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := 'pg_catalog'; 207s prec.relname := 'pg_listener'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s prec.nspname := 'pg_catalog'; 207s prec.relname := 'pg_statistic'; 207s if public.ShouldSlonyVacuumTable(prec.nspname, prec.relname) then 207s return next prec; 207s end if; 207s 207s return; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.TablesToVacuum () is 207s 'Return a list of tables that require frequent vacuuming. The 207s function is used so that the list is not hardcoded into C code.'; 207s COMMENT 207s create or replace function public.add_empty_table_to_replication(p_set_id int4, p_tab_id int4, p_nspname text, p_tabname text, p_idxname text, p_comment text) returns bigint as $$ 207s declare 207s 207s prec record; 207s v_origin int4; 207s v_isorigin boolean; 207s v_fqname text; 207s v_query text; 207s v_rows integer; 207s v_idxname text; 207s 207s begin 207s -- Need to validate that the set exists; the set will tell us if this is the origin 207s select set_origin into v_origin from public.sl_set where set_id = p_set_id; 207s if not found then 207s raise exception 'add_empty_table_to_replication: set % not found!', p_set_id; 207s end if; 207s 207s -- Need to be aware of whether or not this node is origin for the set 207s v_isorigin := ( v_origin = public.getLocalNodeId('_main') ); 207s 207s v_fqname := '"' || p_nspname || '"."' || p_tabname || '"'; 207s -- Take out a lock on the table 207s v_query := 'lock ' || v_fqname || ';'; 207s execute v_query; 207s 207s if v_isorigin then 207s -- On the origin, verify that the table is empty, failing if it has any tuples 207s v_query := 'select 1 as tuple from ' || v_fqname || ' limit 1;'; 207s execute v_query into prec; 207s GET DIAGNOSTICS v_rows = ROW_COUNT; 207s if v_rows = 0 then 207s raise notice 'add_empty_table_to_replication: table % empty on origin - OK', v_fqname; 207s else 207s raise exception 'add_empty_table_to_replication: table % contained tuples on origin node %', v_fqname, v_origin; 207s end if; 207s else 207s -- On other nodes, TRUNCATE the table 207s v_query := 'truncate ' || v_fqname || ';'; 207s execute v_query; 207s end if; 207s -- If p_idxname is NULL, then look up the PK index, and RAISE EXCEPTION if one does not exist 207s if p_idxname is NULL then 207s select c2.relname into prec from pg_catalog.pg_index i, pg_catalog.pg_class c1, pg_catalog.pg_class c2, pg_catalog.pg_namespace n where i.indrelid = c1.oid and i.indexrelid = c2.oid and c1.relname = p_tabname and i.indisprimary and n.nspname = p_nspname and n.oid = c1.relnamespace; 207s if not found then 207s raise exception 'add_empty_table_to_replication: table % has no primary key and no candidate specified!', v_fqname; 207s else 207s v_idxname := prec.relname; 207s end if; 207s else 207s v_idxname := p_idxname; 207s end if; 207s return public.setAddTable_int(p_set_id, p_tab_id, v_fqname, v_idxname, p_comment); 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.add_empty_table_to_replication(p_set_id int4, p_tab_id int4, p_nspname text, p_tabname text, p_idxname text, p_comment text) is 207s 'Verify that a table is empty, and add it to replication. 207s tab_idxname is optional - if NULL, then we use the primary key. 207s 207s Note that this function is to be run within an EXECUTE SCRIPT script, 207s so it runs at the right place in the transaction stream on all 207s nodes.'; 207s COMMENT 207s create or replace function public.replicate_partition(p_tab_id int4, p_nspname text, p_tabname text, p_idxname text, p_comment text) returns bigint as $$ 207s declare 207s prec record; 207s prec2 record; 207s v_set_id int4; 207s 207s begin 207s -- Look up the parent table; fail if it does not exist 207s select c1.oid into prec from pg_catalog.pg_class c1, pg_catalog.pg_class c2, pg_catalog.pg_inherits i, pg_catalog.pg_namespace n where c1.oid = i.inhparent and c2.oid = i.inhrelid and n.oid = c2.relnamespace and n.nspname = p_nspname and c2.relname = p_tabname; 207s if not found then 207s raise exception 'replicate_partition: No parent table found for %.%!', p_nspname, p_tabname; 207s end if; 207s 207s -- The parent table tells us what replication set to use 207s select tab_set into prec2 from public.sl_table where tab_reloid = prec.oid; 207s if not found then 207s raise exception 'replicate_partition: Parent table % for new partition %.% is not replicated!', prec.oid, p_nspname, p_tabname; 207s end if; 207s 207s v_set_id := prec2.tab_set; 207s 207s -- Now, we have all the parameters necessary to run add_empty_table_to_replication... 207s return public.add_empty_table_to_replication(v_set_id, p_tab_id, p_nspname, p_tabname, p_idxname, p_comment); 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.replicate_partition(p_tab_id int4, p_nspname text, p_tabname text, p_idxname text, p_comment text) is 207s 'Add a partition table to replication. 207s tab_idxname is optional - if NULL, then we use the primary key. 207s This function looks up replication configuration via the parent table. 207s 207s Note that this function is to be run within an EXECUTE SCRIPT script, 207s so it runs at the right place in the transaction stream on all 207s nodes.'; 207s COMMENT 207s create or replace function public.disable_indexes_on_table (i_oid oid) 207s returns integer as $$ 207s begin 207s -- Setting pg_class.relhasindex to false will cause copy not to 207s -- maintain any indexes. At the end of the copy we will reenable 207s -- them and reindex the table. This bulk creating of indexes is 207s -- faster. 207s 207s update pg_catalog.pg_class set relhasindex ='f' where oid = i_oid; 207s return 1; 207s end $$ 207s language plpgsql; 207s CREATE FUNCTION 207s comment on function public.disable_indexes_on_table(i_oid oid) is 207s 'disable indexes on the specified table. 207s Used during subscription process to suppress indexes, which allows 207s COPY to go much faster. 207s 207s This may be set as a SECURITY DEFINER in order to eliminate the need 207s for superuser access by Slony-I. 207s '; 207s COMMENT 207s create or replace function public.enable_indexes_on_table (i_oid oid) 207s returns integer as $$ 207s begin 207s update pg_catalog.pg_class set relhasindex ='t' where oid = i_oid; 207s return 1; 207s end $$ 207s language plpgsql 207s security definer; 207s CREATE FUNCTION 207s comment on function public.enable_indexes_on_table(i_oid oid) is 207s 're-enable indexes on the specified table. 207s 207s This may be set as a SECURITY DEFINER in order to eliminate the need 207s for superuser access by Slony-I. 207s '; 207s COMMENT 207s drop function if exists public.reshapeSubscription(int4,int4,int4); 207s DROP FUNCTION 207s create or replace function public.reshapeSubscription (p_sub_origin int4, p_sub_provider int4, p_sub_receiver int4) returns int4 as $$ 207s begin 207s update public.sl_subscribe 207s set sub_provider=p_sub_provider 207s from public.sl_set 207s WHERE sub_set=sl_set.set_id 207s and sl_set.set_origin=p_sub_origin and sub_receiver=p_sub_receiver; 207s if found then 207s perform public.RebuildListenEntries(); 207s notify "_main_Restart"; 207s end if; 207s return 0; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.reshapeSubscription(p_sub_origin int4, p_sub_provider int4, p_sub_receiver int4) is 207s 'Run on a receiver/subscriber node when the provider for that 207s subscription is being changed. Slonik will invoke this method 207s before the SUBSCRIBE_SET event propogates to the receiver 207s so listen paths can be updated.'; 207s COMMENT 207s create or replace function public.slon_node_health_check() returns boolean as $$ 207s declare 207s prec record; 207s all_ok boolean; 207s begin 207s all_ok := 't'::boolean; 207s -- validate that all tables in sl_table have: 207s -- sl_table agreeing with pg_class 207s for prec in select tab_id, tab_relname, tab_nspname from 207s public.sl_table t where not exists (select 1 from pg_catalog.pg_class c, pg_catalog.pg_namespace n 207s where c.oid = t.tab_reloid and c.relname = t.tab_relname and c.relnamespace = n.oid and n.nspname = t.tab_nspname) loop 207s all_ok := 'f'::boolean; 207s raise warning 'table [id,nsp,name]=[%,%,%] - sl_table does not match pg_class/pg_namespace', prec.tab_id, prec.tab_relname, prec.tab_nspname; 207s end loop; 207s if not all_ok then 207s raise warning 'Mismatch found between sl_table and pg_class. Slonik command REPAIR CONFIG may be useful to rectify this.'; 207s end if; 207s return all_ok; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.slon_node_health_check() is 'called when slon starts up to validate that there are not problems with node configuration. Returns t if all is OK, f if there is a problem.'; 207s COMMENT 207s create or replace function public.log_truncate () returns trigger as 207s $$ 207s declare 207s r_role text; 207s c_nspname text; 207s c_relname text; 207s c_log integer; 207s c_node integer; 207s c_tabid integer; 207s begin 207s -- Ignore this call if session_replication_role = 'local' 207s select into r_role setting 207s from pg_catalog.pg_settings where name = 'session_replication_role'; 207s if r_role = 'local' then 207s return NULL; 207s end if; 207s 207s c_tabid := tg_argv[0]; 207s c_node := public.getLocalNodeId('_main'); 207s select tab_nspname, tab_relname into c_nspname, c_relname 207s from public.sl_table where tab_id = c_tabid; 207s select last_value into c_log from public.sl_log_status; 207s if c_log in (0, 2) then 207s insert into public.sl_log_1 ( 207s log_origin, log_txid, log_tableid, 207s log_actionseq, log_tablenspname, 207s log_tablerelname, log_cmdtype, 207s log_cmdupdncols, log_cmdargs 207s ) values ( 207s c_node, pg_catalog.txid_current(), c_tabid, 207s nextval('public.sl_action_seq'), c_nspname, 207s c_relname, 'T', 0, '{}'::text[]); 207s else -- (1, 3) 207s insert into public.sl_log_2 ( 207s log_origin, log_txid, log_tableid, 207s log_actionseq, log_tablenspname, 207s log_tablerelname, log_cmdtype, 207s log_cmdupdncols, log_cmdargs 207s ) values ( 207s c_node, pg_catalog.txid_current(), c_tabid, 207s nextval('public.sl_action_seq'), c_nspname, 207s c_relname, 'T', 0, '{}'::text[]); 207s end if; 207s return NULL; 207s end 207s $$ language plpgsql 207s security definer; 207s CREATE FUNCTION 207s comment on function public.log_truncate () 207s is 'trigger function run when a replicated table receives a TRUNCATE request'; 207s COMMENT 207s create or replace function public.deny_truncate () returns trigger as 207s $$ 207s declare 207s r_role text; 207s begin 207s -- Ignore this call if session_replication_role = 'local' 207s select into r_role setting 207s from pg_catalog.pg_settings where name = 'session_replication_role'; 207s if r_role = 'local' then 207s return NULL; 207s end if; 207s 207s raise exception 'truncation of replicated table forbidden on subscriber node'; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.deny_truncate () 207s is 'trigger function run when a replicated table receives a TRUNCATE request'; 207s COMMENT 207s create or replace function public.store_application_name (i_name text) returns text as $$ 207s declare 207s p_command text; 207s begin 207s if exists (select 1 from pg_catalog.pg_settings where name = 'application_name') then 207s p_command := 'set application_name to '''|| i_name || ''';'; 207s execute p_command; 207s return i_name; 207s end if; 207s return NULL::text; 207s end $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.store_application_name (i_name text) is 207s 'Set application_name GUC, if possible. Returns NULL if it fails to work.'; 207s COMMENT 207s create or replace function public.is_node_reachable(origin_node_id integer, 207s receiver_node_id integer) returns boolean as $$ 207s declare 207s listen_row record; 207s reachable boolean; 207s begin 207s reachable:=false; 207s select * into listen_row from public.sl_listen where 207s li_origin=origin_node_id and li_receiver=receiver_node_id; 207s if found then 207s reachable:=true; 207s end if; 207s return reachable; 207s end $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.is_node_reachable(origin_node_id integer, receiver_node_id integer) 207s is 'Is the receiver node reachable from the origin, via any of the listen paths?'; 207s COMMENT 207s create or replace function public.component_state (i_actor text, i_pid integer, i_node integer, i_conn_pid integer, i_activity text, i_starttime timestamptz, i_event bigint, i_eventtype text) returns integer as $$ 207s begin 207s -- Trim out old state for this component 207s if not exists (select 1 from public.sl_components where co_actor = i_actor) then 207s insert into public.sl_components 207s (co_actor, co_pid, co_node, co_connection_pid, co_activity, co_starttime, co_event, co_eventtype) 207s values 207s (i_actor, i_pid, i_node, i_conn_pid, i_activity, i_starttime, i_event, i_eventtype); 207s else 207s update public.sl_components 207s set 207s co_connection_pid = i_conn_pid, co_activity = i_activity, co_starttime = i_starttime, co_event = i_event, 207s co_eventtype = i_eventtype 207s where co_actor = i_actor 207s and co_starttime < i_starttime; 207s end if; 207s return 1; 207s end $$ 207s language plpgsql; 207s CREATE FUNCTION 207s comment on function public.component_state (i_actor text, i_pid integer, i_node integer, i_conn_pid integer, i_activity text, i_starttime timestamptz, i_event bigint, i_eventtype text) is 207s 'Store state of a Slony component. Useful for monitoring'; 207s NOTICE: function public.reshapesubscription(int4,int4,int4) does not exist, skipping 207s COMMENT 207s create or replace function public.recreate_log_trigger(p_fq_table_name text, 207s p_tab_id oid, p_tab_attkind text) returns integer as $$ 207s begin 207s execute 'drop trigger "_main_logtrigger" on ' || 207s p_fq_table_name ; 207s -- ---- 207s execute 'create trigger "_main_logtrigger"' || 207s ' after insert or update or delete on ' || 207s p_fq_table_name 207s || ' for each row execute procedure public.logTrigger (' || 207s pg_catalog.quote_literal('_main') || ',' || 207s pg_catalog.quote_literal(p_tab_id::text) || ',' || 207s pg_catalog.quote_literal(p_tab_attkind) || ');'; 207s return 0; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s comment on function public.recreate_log_trigger(p_fq_table_name text, 207s p_tab_id oid, p_tab_attkind text) is 207s 'A function that drops and recreates the log trigger on the specified table. 207s It is intended to be used after the primary_key/unique index has changed.'; 207s COMMENT 207s create or replace function public.repair_log_triggers(only_locked boolean) 207s returns integer as $$ 207s declare 207s retval integer; 207s table_row record; 207s begin 207s retval=0; 207s for table_row in 207s select tab_nspname,tab_relname, 207s tab_idxname, tab_id, mode, 207s public.determineAttKindUnique(tab_nspname|| 207s '.'||tab_relname,tab_idxname) as attkind 207s from 207s public.sl_table 207s left join 207s pg_locks on (relation=tab_reloid and pid=pg_backend_pid() 207s and mode='AccessExclusiveLock') 207s ,pg_trigger 207s where tab_reloid=tgrelid and 207s public.determineAttKindUnique(tab_nspname||'.' 207s ||tab_relname,tab_idxname) 207s !=(public.decode_tgargs(tgargs))[2] 207s and tgname = '_main' 207s || '_logtrigger' 207s LOOP 207s if (only_locked=false) or table_row.mode='AccessExclusiveLock' then 207s perform public.recreate_log_trigger 207s (table_row.tab_nspname||'.'||table_row.tab_relname, 207s table_row.tab_id,table_row.attkind); 207s retval=retval+1; 207s else 207s raise notice '%.% has an invalid configuration on the log trigger. This was not corrected because only_lock is true and the table is not locked.', 207s table_row.tab_nspname,table_row.tab_relname; 207s 207s end if; 207s end loop; 207s return retval; 207s end 207s $$ 207s language plpgsql; 207s CREATE FUNCTION 207s comment on function public.repair_log_triggers(only_locked boolean) 207s is ' 207s repair the log triggers as required. If only_locked is true then only 207s tables that are already exclusively locked by the current transaction are 207s repaired. Otherwise all replicated tables with outdated trigger arguments 207s are recreated.'; 207s COMMENT 207s create or replace function public.unsubscribe_abandoned_sets(p_failed_node int4) returns bigint 207s as $$ 207s declare 207s v_row record; 207s v_seq_id bigint; 207s v_local_node int4; 207s begin 207s 207s select public.getLocalNodeId('_main') into 207s v_local_node; 207s 207s if found then 207s --abandon all subscriptions from this origin. 207s for v_row in select sub_set,sub_receiver from 207s public.sl_subscribe, public.sl_set 207s where sub_set=set_id and set_origin=p_failed_node 207s and sub_receiver=v_local_node 207s loop 207s raise notice 'Slony-I: failover_abandon_set() is abandoning subscription to set % on node % because it is too far ahead', v_row.sub_set, 207s v_local_node; 207s --If this node is a provider for the set 207s --then the receiver needs to be unsubscribed. 207s -- 207s select public.unsubscribeSet(v_row.sub_set, 207s v_local_node,true) 207s into v_seq_id; 207s end loop; 207s end if; 207s 207s return v_seq_id; 207s end 207s $$ language plpgsql; 207s CREATE FUNCTION 207s CREATE OR replace function public.agg_text_sum(txt_before TEXT, txt_new TEXT) RETURNS TEXT AS 207s $BODY$ 207s DECLARE 207s c_delim text; 207s BEGIN 207s c_delim = ','; 207s IF (txt_before IS NULL or txt_before='') THEN 207s RETURN txt_new; 207s END IF; 207s RETURN txt_before || c_delim || txt_new; 207s END; 207s $BODY$ 207s LANGUAGE plpgsql; 207s CREATE FUNCTION 207s comment on function public.agg_text_sum(text,text) is 207s 'An accumulator function used by the slony string_agg function to 207s aggregate rows into a string'; 207s COMMENT 207s Dropping cluster 16/regress ... 207s ### End 16 psql ### 207s autopkgtest [03:07:50]: test load-functions: -----------------------] 208s load-functions PASS 208s autopkgtest [03:07:51]: test load-functions: - - - - - - - - - - results - - - - - - - - - - 209s autopkgtest [03:07:52]: @@@@@@@@@@@@@@@@@@@@ summary 209s load-functions PASS 508s nova [W] Skipping flock for amd64 508s Creating nova instance adt-plucky-amd64-slony1-2-20250113-015417-juju-7f2275-prod-proposed-migration-environment-15-cc742134-0d27-478a-92fb-ef0b0b4f3d79 from image adt/ubuntu-plucky-amd64-server-20250112.img (UUID 664c508a-f422-495d-9f39-68c9cbfd21ed)...