Guest User

Untitled

a guest
May 22nd, 2018
76
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.76 KB | None | 0 0
  1. create table email_stats (
  2. id bigserial primary key,
  3. mailing_list_id int not null,
  4. sended_date timestamp not null,
  5. emails_send bigint not null default 0,
  6. emails_clicks bigint not null default 0
  7. );
  8.  
  9. alter table email_stats
  10. add column emails_paid_clicks bigint not null default 0;
  11.  
  12. set statement_timeout to '1s';
  13. alter table email_stats
  14. add column emails_paid_clicks bigint not null default 0;
  15.  
  16. alter table email_stats
  17. add column emails_paid_clicks bigint not null default 0;
  18.  
  19. begin;
  20. set local statement_timeout = '1s';
  21. alter table email_stats
  22. add column emails_paid_clicks bigint default null;
  23. alter table email_stats
  24. alter column emails_paid_clicks set default 0;
  25. commit;
  26.  
  27. update email_stats
  28. set emails_paid_clicks = default
  29. where emails_paid_clicks is null
  30. and /* следующий кусочек данных */
  31.  
  32. $current_id = (select min(id) from email_stats)
  33. $maxid = (select max(id) from email_stats)
  34.  
  35. while ($current_id < $maxid) {
  36. $batch_to = $current_id + 10000 // максимальный размер пачки для одного обновления
  37. update email_stats
  38. set emails_paid_clicks = default
  39. where emails_paid_clicks is null
  40. and id >= $current_id and id < $batch_to
  41.  
  42. $current_id = $batch_to
  43. sleep(5) -- задержка между обновлениями чтобы меньше мешать сервису
  44. }
  45.  
  46. alter table email_stats alter column emails_paid_clicks set not null;
  47.  
  48. begin;
  49. set local statement_timeout = '1s';
  50. alter table email_stats
  51. add constraint emails_paid_clicks_not_null
  52. check (emails_paid_clicks is not null) not valid;
  53. commit;
  54.  
  55. alter table email_stats validate constraint emails_paid_clicks_not_null;
Add Comment
Please, Sign In to add comment