diff options
| author | Aleksander Machniak <alec@alec.pl> | 2013-06-09 11:07:46 +0200 |
|---|---|---|
| committer | Aleksander Machniak <alec@alec.pl> | 2013-06-09 11:07:46 +0200 |
| commit | 60b6d7c3894f61eb9c8bc40efe5528e91386bf94 (patch) | |
| tree | 6f5572bc0722b6026e16ce3c3fc913f3757260ae /SQL/postgres/2013061000.sql | |
| parent | d186405c0090772d1c26788dad9ea973f0421390 (diff) | |
Fix database cache expunge issues (#1489149) - added 'expires' column
Diffstat (limited to 'SQL/postgres/2013061000.sql')
| -rw-r--r-- | SQL/postgres/2013061000.sql | 24 |
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); |