mirror of
https://github.com/LemmyNet/lemmy.git
synced 2025-09-03 19:53:48 +00:00
644a448aa9
* [0.19] Fixing active counts slow queries. (#5907) * Fixing active counts slow queries. * Simplify back to str tuple * Batch site and community updates * Using update from temp table * Making aggs temp table use interval name. * Make dev setup use optimized postgres. * Addressing PR comments. * Use ref * Removing system custom info from customPostgresql.conf * Forgot to remove old scheduled tasks. * Making sure migrations aren't missing anything from release/v0.19 Checked using git diff --diff-filter D --stat release/v0.19 migrations * Rename all migrations to come after release/v0.19 migrations * Add liked_at is not null check.
46 lines
1.2 KiB
SQL
46 lines
1.2 KiB
SQL
ALTER TABLE local_image
|
|
ADD COLUMN local_user_id int REFERENCES local_user (id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
UPDATE
|
|
local_image AS li
|
|
SET
|
|
local_user_id = lu.id
|
|
FROM
|
|
local_user AS lu
|
|
WHERE
|
|
li.person_id = lu.person_id;
|
|
|
|
-- You need to have the exact correct column order, so this needs to be re-created
|
|
--
|
|
-- Rename the table
|
|
ALTER TABLE local_image RENAME TO local_image_old;
|
|
|
|
-- Rename a few constraints
|
|
ALTER TABLE local_image_old RENAME CONSTRAINT image_upload_pkey TO image_upload_pkey_old;
|
|
|
|
-- Create the old one again
|
|
CREATE TABLE local_image (
|
|
local_user_id integer,
|
|
pictrs_alias text NOT NULL,
|
|
published timestamp with time zone DEFAULT now() NOT NULL
|
|
);
|
|
|
|
ALTER TABLE ONLY local_image
|
|
ADD CONSTRAINT image_upload_pkey PRIMARY KEY (pictrs_alias);
|
|
|
|
CREATE INDEX idx_image_upload_local_user_id ON local_image USING btree (local_user_id);
|
|
|
|
ALTER TABLE ONLY local_image
|
|
ADD CONSTRAINT image_upload_local_user_id_fkey FOREIGN KEY (local_user_id) REFERENCES local_user (id) ON UPDATE CASCADE ON DELETE CASCADE;
|
|
|
|
-- Insert the data again
|
|
INSERT INTO local_image (local_user_id, pictrs_alias, published)
|
|
SELECT
|
|
local_user_id,
|
|
pictrs_alias,
|
|
published
|
|
FROM
|
|
local_image_old;
|
|
|
|
DROP TABLE local_image_old;
|
|
|