From 066d7e30a1cd6e491b9c56568f6e4520fb6a8b9f Mon Sep 17 00:00:00 2001 From: Dan Goodliffe Date: Fri, 30 Dec 2016 17:36:57 +0000 Subject: More complex approach to setting tables to unlogged to handle foreign key dependencies between tables --- libpqpp/pq-mock.cpp | 27 +++++++++++++++++++++++---- 1 file changed, 23 insertions(+), 4 deletions(-) diff --git a/libpqpp/pq-mock.cpp b/libpqpp/pq-mock.cpp index 30f14aa..c90f84c 100644 --- a/libpqpp/pq-mock.cpp +++ b/libpqpp/pq-mock.cpp @@ -24,12 +24,31 @@ void Mock::SetTablesToUnlogged() const { auto c = DB::ConnectionPtr(openConnection()); - auto s = c->select("SELECT schemaname, tablename FROM pg_catalog.pg_tables WHERE schemaname NOT IN (?, ?)"); + auto s = c->select(R"SQL( +SELECT n.nspname, c.relname +FROM pg_class c, pg_namespace n +WHERE c.relkind = 'r' +AND n.nspname not in (?, ?) +AND c.relpersistence = 'p' +AND NOT EXISTS ( + SELECT from pg_constraint fk, pg_class ck + WHERE fk.contype = 'f' + AND fk.confrelid = c.oid + AND fk.conrelid = ck.oid + AND ck.oid != c.oid + AND ck.relpersistence = 'p') +AND n.oid = c.relnamespace +ORDER BY 1, 2)SQL"); s->bindParamS(0, "pg_catalog"); s->bindParamS(1, "information_schema"); - for (const auto & t : s->as()) { - c->execute("ALTER TABLE " + t.value<0>() + "." + t.value<1>() + " SET UNLOGGED"); - } + unsigned int n = 0; + do { + n = 0; + for (const auto & t : s->as()) { + c->execute("ALTER TABLE " + t.value<0>() + "." + t.value<1>() + " SET UNLOGGED"); + n += 1; + } + } while(n); } Mock::~Mock() -- cgit v1.2.3