diff options
author | Dan Goodliffe <dan@randomdan.homeip.net> | 2016-12-30 17:36:57 +0000 |
---|---|---|
committer | Dan Goodliffe <dan@randomdan.homeip.net> | 2016-12-30 17:36:57 +0000 |
commit | 670340e60b28a274954b9c525448ff8f191d82c5 (patch) | |
tree | eab6908205c72a8c2c533633357ad57ee15186a2 /libpqpp | |
parent | Set tables in test database to be unlogged, faster (less disk IO) and we don'... (diff) | |
download | libdbpp-postgresql-670340e60b28a274954b9c525448ff8f191d82c5.tar.bz2 libdbpp-postgresql-670340e60b28a274954b9c525448ff8f191d82c5.tar.xz libdbpp-postgresql-670340e60b28a274954b9c525448ff8f191d82c5.zip |
More complex approach to setting tables to unlogged to handle foreign key dependencies between tables
Diffstat (limited to 'libpqpp')
-rw-r--r-- | libpqpp/pq-mock.cpp | 27 |
1 files 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<std::string, std::string>()) { - 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<std::string, std::string>()) { + c->execute("ALTER TABLE " + t.value<0>() + "." + t.value<1>() + " SET UNLOGGED"); + n += 1; + } + } while(n); } Mock::~Mock() |