Re: [GENERAL] How to drop column from interrelated views
On Sat, Jul 08, 2017 at 03:18:39PM -0700, Guyren Howe wrote: > I’ve a set of interrelated views. I want to drop a column from a table and > from all the views that cascade from it. > > I’ve gone to the leaf dependencies and removed the field from them. But I > can’t remove the field from the intermediate views because Postgres doesn’t > appear to be clever enough to see that the leafs no longer depend on the > column. Or did I just miss one? > > In general, this seems like a major weakness expressing a model in Postgres > (I get that any such weakness derives from SQL; that doesn’t stop me wanting > a solution). Not that it helps much with your immediate problem but this is typically the point where one realizes that database definitions should live under version control. That doesn't enable easy dropping of a column from interrelated views but does allow for more convenient ways of writing the required DDL change script. Regards, Karsten -- GPG key ID E4071346 @ eu.pool.sks-keyservers.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to drop column from interrelated views
I've got some functions on my own :)
If you prefer to do it from SQL level I've got two functions prepared which
first secure then restore all missing views definitions.
-- secure all views
DO $$
BEGIN
--drop schema migration cascade
CREATE SCHEMA migration;
CREATE TABLE migration.views AS
SELECT
table_schema,
table_name,
view_definition
FROM INFORMATION_SCHEMA.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
CREATE TABLE migration.view_count AS
SELECT
count(*),
'before' :: TEXT AS desc
FROM INFORMATION_SCHEMA.views
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
END;
$$;
/*
HERE DO YOUR EVIL DROP CASCADE
YOUR VIEWS DEFINITIONS ARE SAFE IN MIGRATION SCHEMA
REMEMBER YOU BACKED UP ONLY VIEWS DEFINITIONS - NO TABLES, RULES OR DATA
*/
-- restore all dropped views / only not existing views
DO $$
DECLARE
l_string TEXT;
BEGIN
FOR l_string IN SELECT 'CREATE VIEW ' || table_schema || '.' || table_name
|| ' AS '
|| view_definition
FROM migration.views
LOOP
BEGIN
EXECUTE l_string;
EXCEPTION WHEN OTHERS THEN
-- do nothing
END;
END LOOP;
IF ((SELECT count
FROM migration.view_count) = (SELECT count(*)
FROM INFORMATION_SCHEMA.views
WHERE table_schema NOT IN
('pg_catalog', 'information_schema')))
THEN
RAISE NOTICE 'Migration successful';
ELSE
RAISE NOTICE 'Something went wrong';
END IF;
END;
$$;
If migration was successful you can drop schema migration.
--
View this message in context:
http://www.postgresql-archive.org/How-to-drop-column-from-interrelated-views-tp5970484p5970518.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to drop column from interrelated views
On Sun, Jul 9, 2017 at 9:56 AM, Berend Tober wrote: > Guyren Howe wrote: > >> On Jul 8, 2017, at 16:11 , Berend Tober > bto...@computer.org>> >> wrote: >> >>> >>> Guyren Howe wrote: >>> I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the field from the intermediate views because Postgres doesn’t appear to be clever enough to see that the leafs no longer depend on the column. Or did I just miss one? In general, this seems like a major weakness expressing a model in Postgres (I get that any such weakness derives from SQL; that doesn’t stop me wanting a solution). Thoughts? Comments? >>> >>> This usually involves a pg_dump in the custom format, editing the list >>> file, creating a script >>> with pg_restore. >>> >>> I described a way I have had success with it at one point at >>> >>> >>> https://www.postgresql.org/message-id/55C3F0B4.5010600%40computer.org >>> >> >> I was wondering if I changed up all the things that interrelate in a >> transaction, whether that >> would bundle them up so they’re all correct afterward. I was hoping so. >> > > > Well, nothing beats empirical evidence ... set up a test case and try it! > > You definitely want to do it in a transaction anyway, so that if you get > it wrong the first few times and have to iterate, the data base rolls back > to where you started. > > Note the method suggested in the earlier link appears to have a error. > Step 4 should be > > > pg_restore -c -1 -L mydatabase.list mydatabase.dump > sql > > > The lower case "c" flag will include DROP statements for the views. The > "1" will wrap in a transaction, like you want. > > > BTW, please generally use the "reply-all" functionality of your email > client when interacting with this list ... the server puts the list alias > in the CC line, so you have to reply all to keep the conversation publicly > available for others to learn from. > > -- B > > > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > Another alternative is to simply extract all the view defs with the column name you want to drop, edit the viewdef to remove the columns and then use that to redefine the views BEFORE dropping the column from the table. IE: in the following query, replace with the name of the column to be dropped. 1. Make a SQL pg_dump of the database before proceeding 2. \o edit_views.sql SELECT 'CREATE OR REPLACE VIEW ' || n.nspname || '.' || c.relname || ' AS ' || pg_get_viewdef(c.oid, true) FROM pg_attribute a JOIN pg_class c ON c.oid = a.attrelid JOIN pg_namespace n ON (n.oid = c.relnamespace) WHERE c.relkind = 'v' AND n.nspname NOT LIKE 'pg_%' AND n.nspname NOT LIKE 'sql_%' AND n.nspname NOT LIKE 'information%' AND a.attname = '' ORDER BY 1; 3. Edit edit_views.sql to remove all occurrences of the column being dropped 4.psql < edit_views.sql 5. Then drop the column from the table. *Melvin Davidson* I reserve the right to fantasize. Whether or not you wish to share my fantasy is entirely up to you.
Re: [GENERAL] How to drop column from interrelated views
Guyren Howe wrote: On Jul 8, 2017, at 16:11 , Berend Tober mailto:bto...@computer.org>> wrote: Guyren Howe wrote: I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the field from the intermediate views because Postgres doesn’t appear to be clever enough to see that the leafs no longer depend on the column. Or did I just miss one? In general, this seems like a major weakness expressing a model in Postgres (I get that any such weakness derives from SQL; that doesn’t stop me wanting a solution). Thoughts? Comments? This usually involves a pg_dump in the custom format, editing the list file, creating a script with pg_restore. I described a way I have had success with it at one point at https://www.postgresql.org/message-id/55C3F0B4.5010600%40computer.org I was wondering if I changed up all the things that interrelate in a transaction, whether that would bundle them up so they’re all correct afterward. I was hoping so. Well, nothing beats empirical evidence ... set up a test case and try it! You definitely want to do it in a transaction anyway, so that if you get it wrong the first few times and have to iterate, the data base rolls back to where you started. Note the method suggested in the earlier link appears to have a error. Step 4 should be pg_restore -c -1 -L mydatabase.list mydatabase.dump > sql The lower case "c" flag will include DROP statements for the views. The "1" will wrap in a transaction, like you want. BTW, please generally use the "reply-all" functionality of your email client when interacting with this list ... the server puts the list alias in the CC line, so you have to reply all to keep the conversation publicly available for others to learn from. -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to drop column from interrelated views
Guyren Howe wrote: I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the field from the intermediate views because Postgres doesn’t appear to be clever enough to see that the leafs no longer depend on the column. Or did I just miss one? In general, this seems like a major weakness expressing a model in Postgres (I get that any such weakness derives from SQL; that doesn’t stop me wanting a solution). Thoughts? Comments? This usually involves a pg_dump in the custom format, editing the list file, creating a script with pg_restore. I described a way I have had success with it at one point at https://www.postgresql.org/message-id/55C3F0B4.5010600%40computer.org -- B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] How to drop column from interrelated views
I’ve a set of interrelated views. I want to drop a column from a table and from all the views that cascade from it. I’ve gone to the leaf dependencies and removed the field from them. But I can’t remove the field from the intermediate views because Postgres doesn’t appear to be clever enough to see that the leafs no longer depend on the column. Or did I just miss one? In general, this seems like a major weakness expressing a model in Postgres (I get that any such weakness derives from SQL; that doesn’t stop me wanting a solution). Thoughts? Comments? Thanks in advance.
