pg-schema-diff
pg-schema-diff copied to clipboard
stripe
Add support for stored procedures
I did try to add a STORED PROCEDURE but the plan didn't generate a diff with the new file
It should. Do you have the SQL for reference, pg dump of your schema, and the command you are running to generate the diff?
-- ------------ Write DROP-PROCEDURE-stage scripts -----------
-- DROP ROUTINE IF EXISTS dbo."Usp_Archive"(IN INTEGER, IN INTEGER, INOUT INT, INOUT REFCURSOR);
-- ------------ Write CREATE-DATABASE-stage scripts -----------
--CREATE SCHEMA IF NOT EXISTS dbo;
-- ------------ Write CREATE-PROCEDURE-stage scripts -----------
CREATE OR REPLACE PROCEDURE dbo."Usp_Archive"(IN par_count INTEGER, IN par_numdays_to_keep INTEGER, INOUT return_code INT DEFAULT 0, INOUT p_refcur REFCURSOR DEFAULT NULL)
AS
$BODY$
DECLARE
var_cutoff TIMESTAMP WITHOUT TIME ZONE DEFAULT timezone('UTC', CURRENT_TIMESTAMP(6)) - (par_numdays_to_keep::NUMERIC || ' days')::INTERVAL;
BEGIN
CREATE TEMPORARY TABLE "VISITKEYS_USP_ARCHIVE"
("VISIT_KEY" BIGINT);
CREATE TEMPORARY TABLE "REQUESTKEYS_USP_ARCHIVE"
("REQUEST_KEY" BIGINT);
RAISE NOTICE 'selecting Visits';
INSERT INTO "VISITKEYS_USP_ARCHIVE"
SELECT
"VISIT_KEY"
FROM dbo."Visit"
WHERE "DATE_ENTERED_UTC" < var_cutoff
LIMIT 250;
RAISE NOTICE 'selecting Requests';
INSERT INTO "REQUESTKEYS_USP_ARCHIVE"
SELECT
"REQUEST_KEY"
FROM dbo."Request"
WHERE "VISIT_KEY" IN (SELECT "VISIT_KEY" FROM "VISITKEYS_USP_ARCHIVE");
RAISE NOTICE 'inserting into Visit';
INSERT INTO webanalyticsarchivecandev_dbo."Visit" ("VISIT_KEY", "CHANNEL", "DATE_ENTERED_UTC", "DATE_SESSION_END_UTC", "DESCRIPTION", "KEYWORDS", "LINK_TYPE", "SEARCH_QUERY", "SUB_CHANNEL", "USER_AGENT", "VISITOR_KEY", "LOAN_APPLICATION_KEY")
SELECT
"VISIT_KEY", "CHANNEL", "DATE_ENTERED_UTC", "DATE_SESSION_END_UTC", "DESCRIPTION", "KEYWORDS", "LINK_TYPE", "SEARCH_QUERY", "SUB_CHANNEL", "USER_AGENT", "VISITOR_KEY", "LOAN_APPLICATION_KEY"
FROM dbo."Visit"
WHERE "VISIT_KEY" IN (SELECT "VISIT_KEY" FROM "VISITKEYS_USP_ARCHIVE");
RAISE NOTICE 'inserting into Request';
INSERT INTO webanalyticsarchivecandev_dbo."Request" ("REQUEST_KEY", "VISIT_KEY", "URL_KEY", "REQUEST_TYPE", "IP_ADDRESS", "DATE_ENTERED_UTC", "NOTE", "DEVICE_KEY", "SERVER_NAME")
SELECT
"REQUEST_KEY", "VISIT_KEY", "URL_KEY", "REQUEST_TYPE", "IP_ADDRESS", "DATE_ENTERED_UTC", "NOTE", "DEVICE_KEY", "SERVER_NAME"
FROM dbo."Request"
WHERE "VISIT_KEY" IN (SELECT "VISIT_KEY" FROM "VISITKEYS_USP_ARCHIVE");
RAISE NOTICE 'inserting into RequestDetail';
INSERT INTO webanalyticsarchivecandev_dbo."RequestDetail" ("REQUEST_DETAIL_KEY", "REQUEST_KEY", "NAME", "VALUE")
SELECT
"REQUEST_DETAIL_KEY", "REQUEST_KEY", "NAME", "VALUE"
FROM dbo."RequestDetail"
WHERE "REQUEST_KEY" IN (SELECT "REQUEST_KEY" FROM "REQUESTKEYS_USP_ARCHIVE");
RAISE NOTICE 'deleting from LastFocusedField';
DELETE FROM dbo."LastFocusedField"
WHERE "REQUEST_KEY" IN (SELECT "REQUEST_KEY" FROM "REQUESTKEYS_USP_ARCHIVE");
RAISE NOTICE 'deleting from RequestDetail';
DELETE FROM dbo."RequestDetail"
WHERE "REQUEST_KEY" IN (SELECT "REQUEST_KEY" FROM "REQUESTKEYS_USP_ARCHIVE");
RAISE NOTICE 'deleting from ValidationError';
DELETE FROM dbo."ValidationError"
WHERE "REQUEST_KEY" IN (SELECT "REQUEST_KEY" FROM "REQUESTKEYS_USP_ARCHIVE");
RAISE NOTICE 'deleting from Request';
DELETE FROM dbo."Request"
WHERE "REQUEST_KEY" IN (SELECT "REQUEST_KEY" FROM "REQUESTKEYS_USP_ARCHIVE");
RAISE NOTICE 'deleting from Visit';
DELETE FROM dbo."Visit"
WHERE "VISIT_KEY" IN (SELECT "VISIT_KEY" FROM "VISITKEYS_USP_ARCHIVE");
OPEN p_refcur FOR
SELECT
COUNT(1)
FROM "VISITKEYS_USP_ARCHIVE";
return_code := 0;
DROP TABLE IF EXISTS "VISITKEYS_USP_ARCHIVE";
DROP TABLE IF EXISTS "REQUESTKEYS_USP_ARCHIVE";
RETURN;
END;
$BODY$
LANGUAGE plpgsql;
here is the stored proc
command used
➜ WebAnalytics git:(Add_postgres_db_migrations) ✗ pg-schema-diff apply --dsn "postgresql://postgres:mysecretpassword@localhost:5432/webanalytics" --schema-dir WebAnalytics.Database.Postgres/db/Scripts/Create --allow-hazards HAS_UNTRACKABLE_DEPENDENCIES --skip-confirm-prompt
Schema matches expected. No plan generated
if i use two folders i run into this issue
WebAnalytics git:(Add_postgres_db_migrations) ✗ pg-schema-diff apply --dsn "postgresql://postgres:mysecretpassword@localhost:5432/webanalytics" --schema-dir WebAnalytics.Database.Postgres/db/Scripts/Create --allow-hazards HAS_UNTRACKABLE_DEPENDENCIES --skip-confirm-prompt --schema-dir WebAnalytics.Database.Postgres/db/Procedures
Error: generating plan: getting new schema: running DDL: ERROR: schema "dbo" does not exist (SQLSTATE 3F000)
if i keep them in one folder then it doesn't recognize the stored proc
Okay, I was mistaken. We do not support stored procedures. I was under the impression that function support == stored procedure support (and that stored procedures are just syntactic sugar for functions). This is only partially true. In the interim, you could structure your migrations to always create or replace your stored procedures at the end.
In the interim, you could structure your migrations to always create or replace your stored procedures at the end.
Can you elaborate on what you mean by that?
Can you elaborate on what you mean by that? You can plan your migration with
--insert-statementthat create or replace your stored procedures every time. Insert statement lets you inject migration statements into your migration plan.
Unfortunately, the offset is in terms of index from the start instead of the end. I might add support for "negative indexes" in the future, such that the offset can also be in terms of a relative position from the end of the migration statements.
So for now i can't do stored procedures ? You can do them using the workaround I suggested, but otherwise, the tooling does not support them yet. I can look into adding support for them.
So for now i can't do stored procedures ?
You can do them using the workaround I suggested, but otherwise, the tooling does not support them yet. I can look into adding support for them.
But isn't the workaround to run them from start rather than at the end ?
But isn't the workaround to run them from start rather than at the end ?
It's a bit situation dependent, but generally makes sense to run at the end after any adds/alters that may occur.
I can try to knock it out soon-ish. Stored procedures shouldn't be too bad to implement. They're definitely a bit on the trickier end, however.
Notably, we can't track the dependencies of pg/plsql functions. Postgres doesn't even track those dependencies.
I was looking into this today! Yes, I can try to get it done this week. I have a prototype for a continuing refactor I'm also working on, but I can try to prioritize this first.
Are you planning to make the changes so that the stored proc runs everytime or similar to any changes made to SQL file in general ?
Are you planning to make the changes so that the stored proc runs everytime or similar to any changes made to SQL file in general ?
Stored procedures don't have dependency tracking in Postgres except at initilization. So it will probably look like
...Delete old stored procedures...
...
...
All other migration statements
...
...
...
...
Create new/alter existing stored procedures
Hi @Navbryce, just checking in—are there any updates on this? Apologies for the follow-up!
@Navbryce hope you had a great Thanksgiving - checking back on this, any new updates ?
Just merged in some basic support! Let me know if it works okay. Notably, postgres has no dependency tracking of procedures except at creation time. In other words, it will allow you to totally delete the dependencies of a procedure and not complain.