def discover_unreplicated(cur):
"""Inspect the database for tables and sequences in the public schema
that are not in a replication set.
:returns: (unreplicated_tables_set, unreplicated_sequences_set)
"""
all_tables = all_tables_in_schema(cur, 'public')
all_sequences = all_sequences_in_schema(cur, 'public')
# Ignore any tables and sequences starting with temp_. These are
# transient and not to be replicated per Bug #778338.
all_tables = set(table for table in all_tables
if not table.startswith('public.temp_'))
all_sequences = set(sequence for sequence in all_sequences
if not sequence.startswith('public.temp_'))
cur.execute("""
SELECT tab_nspname, tab_relname FROM %s
WHERE tab_nspname = 'public'
""" % fqn(CLUSTER_NAMESPACE, "sl_table"))
replicated_tables = set(fqn(*row) for row in cur.fetchall())
cur.execute("""
SELECT seq_nspname, seq_relname FROM %s
WHERE seq_nspname = 'public'
""" % fqn(CLUSTER_NAMESPACE, "sl_sequence"))
replicated_sequences = set(fqn(*row) for row in cur.fetchall())
return (all_tables - replicated_tables - IGNORED_TABLES,
all_sequences - replicated_sequences - IGNORED_SEQUENCES)
def discover_unreplicated(cur):
"""Inspect the database for tables and sequences in the public schema
that are not in a replication set.
:returns: (unreplicated_tables_set, unreplicated_sequences_set)
"""
all_tables = all_tables_in_schema(cur, 'public')
all_sequences = all_sequences_in_schema(cur, 'public')
# Ignore any tables and sequences starting with temp_. These are
# transient and not to be replicated per Bug #778338.
all_tables = set(
table for table in all_tables
if not table.startswith('public.temp_'))
all_sequences = set(
sequence for sequence in all_sequences
if not sequence.startswith('public.temp_'))
cur.execute("""
SELECT tab_nspname, tab_relname FROM %s
WHERE tab_nspname = 'public'
""" % fqn(CLUSTER_NAMESPACE, "sl_table"))
replicated_tables = set(fqn(*row) for row in cur.fetchall())
cur.execute("""
SELECT seq_nspname, seq_relname FROM %s
WHERE seq_nspname = 'public'
""" % fqn(CLUSTER_NAMESPACE, "sl_sequence"))
replicated_sequences = set(fqn(*row) for row in cur.fetchall())
return (
all_tables - replicated_tables - IGNORED_TABLES,
all_sequences - replicated_sequences - IGNORED_SEQUENCES)
def __init__(self, con, namespace, table_name):
cur = con.cursor()
cur.execute("""
SELECT tab_id, tab_set, set_origin
FROM %s.sl_table, %s.sl_set
WHERE tab_set = set_id
AND tab_nspname = %s
AND tab_relname = %s
""" % ((CLUSTER_NAMESPACE, CLUSTER_NAMESPACE) +
sqlvalues(namespace, table_name)))
row = cur.fetchone()
if row is None:
raise LookupError(fqn(namespace, table_name))
self.table_id, self.replication_set_id, self.master_node_id = row
def __init__(self, con, namespace, table_name):
cur = con.cursor()
cur.execute("""
SELECT tab_id, tab_set, set_origin
FROM %s.sl_table, %s.sl_set
WHERE tab_set = set_id
AND tab_nspname = %s
AND tab_relname = %s
""" % (
(CLUSTER_NAMESPACE, CLUSTER_NAMESPACE)
+ sqlvalues(namespace, table_name)))
row = cur.fetchone()
if row is None:
raise LookupError(fqn(namespace, table_name))
self.table_id, self.replication_set_id, self.master_node_id = row
def calculate_replication_set(cur, seeds):
"""Return the minimal set of tables and sequences needed in a
replication set containing the seed table.
A replication set must contain all tables linked by foreign key
reference to the given table, and sequences used to generate keys.
Tables and sequences can be added to the IGNORED_TABLES and
IGNORED_SEQUENCES lists for cases where we known can safely ignore
this restriction.
:param seeds: [(namespace, tablename), ...]
:returns: (tables, sequences)
"""
# Results
tables = set()
sequences = set()
# Our pending set to check
pending_tables = set(seeds)
# Generate the set of tables that reference the seed directly
# or indirectly via foreign key constraints, including the seed itself.
while pending_tables:
namespace, tablename = pending_tables.pop()
# Skip if the table doesn't exist - we might have seeds listed that
# have been removed or are yet to be created.
cur.execute("""
SELECT TRUE
FROM pg_class, pg_namespace
WHERE pg_class.relnamespace = pg_namespace.oid
AND pg_namespace.nspname = %s
AND pg_class.relname = %s
""" % sqlvalues(namespace, tablename))
if cur.fetchone() is None:
log.debug("Table %s.%s doesn't exist" % (namespace, tablename))
continue
tables.add((namespace, tablename))
# Find all tables that reference the current (seed) table
# and all tables that the seed table references.
cur.execute("""
SELECT ref_namespace.nspname, ref_class.relname
FROM
-- One of the seed tables
pg_class AS seed_class,
pg_namespace AS seed_namespace,
-- A table referencing the seed, or being referenced by
-- the seed.
pg_class AS ref_class,
pg_namespace AS ref_namespace,
pg_constraint
WHERE
seed_class.relnamespace = seed_namespace.oid
AND ref_class.relnamespace = ref_namespace.oid
AND seed_namespace.nspname = %s
AND seed_class.relname = %s
-- Foreign key constraints are all we care about.
AND pg_constraint.contype = 'f'
-- We want tables referenced by, or referred to, the
-- seed table.
AND ((pg_constraint.conrelid = ref_class.oid
AND pg_constraint.confrelid = seed_class.oid)
OR (pg_constraint.conrelid = seed_class.oid
AND pg_constraint.confrelid = ref_class.oid)
)
""" % sqlvalues(namespace, tablename))
for namespace, tablename in cur.fetchall():
key = (namespace, tablename)
if (key not in tables and key not in pending_tables and '%s.%s' %
(namespace, tablename) not in IGNORED_TABLES):
pending_tables.add(key)
# Generate the set of sequences that are linked to any of our set of
# tables. We assume these are all sequences created by creation of
# serial or bigserial columns, or other sequences OWNED BY a particular
# column.
for namespace, tablename in tables:
cur.execute("""
SELECT seq
FROM (
SELECT pg_get_serial_sequence(%s, attname) AS seq
FROM pg_namespace, pg_class, pg_attribute
WHERE pg_namespace.nspname = %s
AND pg_class.relnamespace = pg_namespace.oid
AND pg_class.relname = %s
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attisdropped IS FALSE
) AS whatever
WHERE seq IS NOT NULL;
""" % sqlvalues(fqn(namespace, tablename), namespace, tablename))
for sequence, in cur.fetchall():
if sequence not in IGNORED_SEQUENCES:
sequences.add(sequence)
# We can't easily convert the sequence name to (namespace, name) tuples,
# so we might as well convert the tables to dot notation for consistancy.
tables = set(fqn(namespace, tablename) for namespace, tablename in tables)
return tables, sequences
def calculate_replication_set(cur, seeds):
"""Return the minimal set of tables and sequences needed in a
replication set containing the seed table.
A replication set must contain all tables linked by foreign key
reference to the given table, and sequences used to generate keys.
Tables and sequences can be added to the IGNORED_TABLES and
IGNORED_SEQUENCES lists for cases where we known can safely ignore
this restriction.
:param seeds: [(namespace, tablename), ...]
:returns: (tables, sequences)
"""
# Results
tables = set()
sequences = set()
# Our pending set to check
pending_tables = set(seeds)
# Generate the set of tables that reference the seed directly
# or indirectly via foreign key constraints, including the seed itself.
while pending_tables:
namespace, tablename = pending_tables.pop()
# Skip if the table doesn't exist - we might have seeds listed that
# have been removed or are yet to be created.
cur.execute("""
SELECT TRUE
FROM pg_class, pg_namespace
WHERE pg_class.relnamespace = pg_namespace.oid
AND pg_namespace.nspname = %s
AND pg_class.relname = %s
""" % sqlvalues(namespace, tablename))
if cur.fetchone() is None:
log.debug("Table %s.%s doesn't exist" % (namespace, tablename))
continue
tables.add((namespace, tablename))
# Find all tables that reference the current (seed) table
# and all tables that the seed table references.
cur.execute("""
SELECT ref_namespace.nspname, ref_class.relname
FROM
-- One of the seed tables
pg_class AS seed_class,
pg_namespace AS seed_namespace,
-- A table referencing the seed, or being referenced by
-- the seed.
pg_class AS ref_class,
pg_namespace AS ref_namespace,
pg_constraint
WHERE
seed_class.relnamespace = seed_namespace.oid
AND ref_class.relnamespace = ref_namespace.oid
AND seed_namespace.nspname = %s
AND seed_class.relname = %s
-- Foreign key constraints are all we care about.
AND pg_constraint.contype = 'f'
-- We want tables referenced by, or referred to, the
-- seed table.
AND ((pg_constraint.conrelid = ref_class.oid
AND pg_constraint.confrelid = seed_class.oid)
OR (pg_constraint.conrelid = seed_class.oid
AND pg_constraint.confrelid = ref_class.oid)
)
""" % sqlvalues(namespace, tablename))
for namespace, tablename in cur.fetchall():
key = (namespace, tablename)
if (key not in tables and key not in pending_tables
and '%s.%s' % (namespace, tablename) not in IGNORED_TABLES):
pending_tables.add(key)
# Generate the set of sequences that are linked to any of our set of
# tables. We assume these are all sequences created by creation of
# serial or bigserial columns, or other sequences OWNED BY a particular
# column.
for namespace, tablename in tables:
cur.execute("""
SELECT seq
FROM (
SELECT pg_get_serial_sequence(%s, attname) AS seq
FROM pg_namespace, pg_class, pg_attribute
WHERE pg_namespace.nspname = %s
AND pg_class.relnamespace = pg_namespace.oid
AND pg_class.relname = %s
AND pg_attribute.attrelid = pg_class.oid
AND pg_attribute.attisdropped IS FALSE
) AS whatever
WHERE seq IS NOT NULL;
""" % sqlvalues(fqn(namespace, tablename), namespace, tablename))
for sequence, in cur.fetchall():
if sequence not in IGNORED_SEQUENCES:
sequences.add(sequence)
# We can't easily convert the sequence name to (namespace, name) tuples,
# so we might as well convert the tables to dot notation for consistancy.
tables = set(fqn(namespace, tablename) for namespace, tablename in tables)
return tables, sequences
