Guest User

Untitled

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