0

We have a table with around 7M records with type ferrari and want to do a schema migration. We used this script

insert into new_car id, name, type, colorType
   select id, name, type, 'red' 
   from old_car 
   where type = 'ferrari' 
   order by id asc

The script took around 50 minutes to execute and after it got complete we realised that the new_car table have 2M less records than old_car table. While the script was executing the old_car table still got inserts, updates and etc concurrently.

Does this concurrency may cause some sort of problem? What are the possible cause of the lack of ~2M rows? (the old_car table didn't got 2M deletes while the query was running, maybe something like 100 or 200 deletes)

Nykolas Lima
  • 140
  • 1
  • 10
  • 1
    The statement inserted the rows that were in `old_car` at the moment the statement started. While the statement is running it does not see changes to the table. –  Apr 01 '16 at 20:47
  • welll you only insert `'ferrari'` cars, not all cars ... are you surprise you only insert 5 millions? I would say that is too much unless you are in Dubai. BTW if you query took 50 minutes you probably doesnt have one index for `type` show us `EXPLAIN ANALYZE` – Juan Carlos Oropeza Apr 01 '16 at 20:47
  • @JuanCarlosOropeza the table had 7M records with type `ferrari`, I already fixed it in the question. @a_horse_with_no_name the `old_car` table didn't had 2M deletes, just a few (100 or 200). My concern is not about the time took by the execution. I'm worried about the missing rows. – Nykolas Lima Apr 01 '16 at 20:59
  • You cant reference two user on the same comment, You should create seperated comment so each user get a notification. As @horse say at the moment of you query start, db create a snapshot of the table, so things happening on the db shouldnt affect the result. – Juan Carlos Oropeza Apr 01 '16 at 21:05
  • Try two things... `CREATE TABLE test_count as SELECT count(*) FROM old_cars where type= 'ferrari'` and then `CREATE TABLE test_create as SELECT * FROM old_cars where type= 'ferrari'` and let me know the results – Juan Carlos Oropeza Apr 01 '16 at 21:08
  • @JuanCarlosOropeza the problem is that only a set of the rows that match the where clause were migrated. There was no problem on table creation or from records deleted from old table. – Nykolas Lima Apr 01 '16 at 21:11
  • I know, but you are inserting on `new_car`. I want to be sure the target table isnt a factor, like a hidden trigger. Also validate the number of source and result rows. That is call eat the elephant. Try to eat small pieces. – Juan Carlos Oropeza Apr 01 '16 at 21:13
  • Also you can create a copy of old_car to remove the concurrence from the equation. `CREATE TABLE old_car_2 AS SELECT * FROM old_car` – Juan Carlos Oropeza Apr 01 '16 at 21:19
  • @JuanCarlosOropeza there is no triggers on new table. – Nykolas Lima Apr 01 '16 at 22:09
  • I just trying to help. But you need give us something else – Juan Carlos Oropeza Apr 01 '16 at 22:26

0 Answers0