summaryrefslogtreecommitdiff
path: root/SQL/postgres/2013061000.sql
diff options
context:
space:
mode:
authorAleksander Machniak <alec@alec.pl>2013-06-09 11:07:46 +0200
committerAleksander Machniak <alec@alec.pl>2013-06-09 11:07:46 +0200
commit60b6d7c3894f61eb9c8bc40efe5528e91386bf94 (patch)
tree6f5572bc0722b6026e16ce3c3fc913f3757260ae /SQL/postgres/2013061000.sql
parentd186405c0090772d1c26788dad9ea973f0421390 (diff)
Fix database cache expunge issues (#1489149) - added 'expires' column
Diffstat (limited to 'SQL/postgres/2013061000.sql')
-rw-r--r--SQL/postgres/2013061000.sql24
1 files changed, 24 insertions, 0 deletions
diff --git a/SQL/postgres/2013061000.sql b/SQL/postgres/2013061000.sql
new file mode 100644
index 000000000..9db0ebcd7
--- /dev/null
+++ b/SQL/postgres/2013061000.sql
@@ -0,0 +1,24 @@
+ALTER TABLE "cache" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_shared" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_index" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_thread" ADD expires timestamp with time zone DEFAULT NULL;
+ALTER TABLE "cache_messages" ADD expires timestamp with time zone DEFAULT NULL;
+
+-- initialize expires column with created/changed date + 7days
+UPDATE "cache" SET expires = created + interval '604800 seconds';
+UPDATE "cache_shared" SET expires = created + interval '604800 seconds';
+UPDATE "cache_index" SET expires = changed + interval '604800 seconds';
+UPDATE "cache_thread" SET expires = changed + interval '604800 seconds';
+UPDATE "cache_messages" SET expires = changed + interval '604800 seconds';
+
+DROP INDEX cache_created_idx;
+DROP INDEX cache_shared_created_idx;
+ALTER TABLE "cache_index" DROP "changed";
+ALTER TABLE "cache_thread" DROP "changed";
+ALTER TABLE "cache_messages" DROP "changed";
+
+CREATE INDEX cache_expires_idx ON "cache" (expires);
+CREATE INDEX cache_shared_expires_idx ON "cache_shared" (expires);
+CREATE INDEX cache_index_expires_idx ON "cache_index" (expires);
+CREATE INDEX cache_thread_expires_idx ON "cache_thread" (expires);
+CREATE INDEX cache_messages_expires_idx ON "cache_messages" (expires);