SHARE
TWEET

Untitled

a guest Feb 17th, 2019 69 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- Autogenerated by drop_old_column_promotion.erb
  2. --   author: siyer@prosperworks.com
  3. --   incept: 2018-09-19
  4.  
  5. -- Drops the old_property_owner_id column of the typed_properties table
  6. -- Locks that table for only O(1) with small K.
  7.  
  8. -- Schema migration parameters:
  9. --   Table:       typed_properties
  10. --   Column:      old_property_owner_id
  11.  
  12. -- Pattern for use with Heroku Postgres:
  13. --
  14. -- $ psql -d `heroku config:get --app ali-staging DATABASE_URL` -f drop_old_property_owner_id_on_typed_properties.sql
  15.  
  16. --
  17. -- Set up the connection.
  18. --
  19.  
  20. \set ECHO none
  21. \pset pager off
  22. \timing on
  23. \set ON_ERROR_STOP on
  24. SET maintenance_work_mem = '8GB';
  25. SET citus.multi_shard_commit_protocol = '2pc';
  26. \set ECHO queries
  27.  
  28. --
  29. -- Validate the parameters of the migration.
  30.  
  31. DO $$
  32. DECLARE
  33.   tbl text        := quote_ident('typed_properties');
  34.   current_col text := quote_ident('property_owner_id');
  35.   int_col text    := quote_ident('old_property_owner_id');
  36.  
  37. BEGIN
  38.   -- If the bigint column does not exist, fail.
  39.  
  40.   IF NOT EXISTS (
  41.     SELECT 1 FROM information_schema.columns
  42.       WHERE table_name = 'typed_properties'
  43.         AND column_name = 'property_owner_id'
  44.     ) THEN
  45.     RAISE EXCEPTION 'The bigint column % does not exist', current_col;
  46.   END IF;
  47.  
  48.   -- If the integer column does not exist, fail.
  49.  
  50.   IF NOT EXISTS (
  51.     SELECT 1 FROM information_schema.columns
  52.       WHERE table_name = 'typed_properties'
  53.         AND column_name = 'old_property_owner_id'
  54.     ) THEN
  55.     RAISE EXCEPTION 'The old integer column % does not exist', int_col;
  56.   END IF;
  57.  
  58.   -- If the integer column exists, but is not of type integer, fail.
  59.  
  60.   IF NOT EXISTS (
  61.     SELECT 1 FROM information_schema.columns
  62.       WHERE table_name = 'typed_properties'
  63.         AND column_name = 'old_property_owner_id'
  64.         AND data_type = 'integer'
  65.     ) THEN
  66.     RAISE EXCEPTION 'The old column % must have type integer', int_col;
  67.   END IF;
  68.  
  69.  -- If the column in question exists,but is not of type bigint, fail.
  70.  
  71.   IF NOT EXISTS (
  72.     SELECT 1 FROM information_schema.columns
  73.       WHERE table_name = 'typed_properties'
  74.         AND column_name = 'property_owner_id'
  75.         AND data_type = 'bigint'
  76.     ) THEN
  77.     RAISE EXCEPTION 'The expected bigint column % does not have type bigint', current_col;
  78.   END IF;
  79.  
  80.   -- Check if the integer and bigint columns have the same values.Raise exception if not
  81.  
  82.   IF EXISTS (
  83.     SELECT 1
  84.     FROM run_command_on_shards(
  85.       'typed_properties',
  86.       $cmd$
  87.       SELECT count(*)
  88.       FROM   %s
  89.       WHERE property_owner_id != old_property_owner_id
  90.         OR (property_owner_id IS NULL AND old_property_owner_id IS NOT NULL)
  91.         OR (property_owner_id IS NOT NULL AND old_property_owner_id IS NULL)
  92.       $cmd$
  93.     )
  94.     WHERE result::integer != 0
  95.   ) THEN
  96.     RAISE EXCEPTION 'Values of old and new column do not match';
  97.   END IF;
  98. END
  99. $$;
  100.  
  101. --
  102. -- Transaction to roll back.
  103. --
  104.  
  105. -- These are the function and trigger created after the column promotion to bigint. These need to be dropped. Refer Step_3 SQL file during column promotion
  106.  
  107. BEGIN;
  108.  
  109.   SET LOCAL lock_timeout = '10s';
  110.  
  111.   --    
  112.   -- Remove the fill function btwn bigint and int column.Get these from Last few lines of Step_3 SQL file.
  113.   --  
  114.  
  115.   SELECT run_command_on_shards('typed_properties',$cmd$ DROP TRIGGER IF EXISTS fill_property_owner_id_to_old_property_owner_id_on_typed_proper ON %s $cmd$);
  116.  
  117.   SELECT run_command_on_workers($cmd$ DROP FUNCTION IF EXISTS copy_property_owner_id_to_old_property_owner_id_on_typed_proper CASCADE; $cmd$);
  118.  
  119.   --
  120.   -- DROP THE INTEGER COLUMN
  121.   --
  122.   ALTER TABLE typed_properties DROP COLUMN old_property_owner_id;  
  123.  
  124. END;    
  125.  
  126. \echo Done!
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top