You seem to be misinterpreting a part of my advice to your previous question:
so I can not Truncate and rename the temp_tables since the system
should be on all the time.
There was no renaming involved. After TRUNCATE
you run an INSERT
. The only blocking operation is the TRUNCATE
. I quote the manual:
TRUNCATE
acquires an ACCESS EXCLUSIVE
lock on each table it
operates on, which blocks all other concurrent operations on the
table. (...) If concurrent access to a table is required, then the
DELETE
command should be used instead.
INSERT
may still work, if it is not time-critical and allowed to wait until the lock is released. TRUNCATE
is usually very fast, if you run it in a separate transaction, it should only block for a couple of milliseconds. Note: separate transaction, not separate session! Your temporary table lives and dies with the session.
The drawback of a separate transaction: if you lose your session before you are able to re-insert from the temporary table, you loose data. To be sure, you could use a plain table instead. (Which would offer the alternative path to drop the old and rename the new table.)
Either way, your updated question makes clear that you want to run this repeatedly, accumulating old rows in the same table. In this case, TRUNCATE
is not a good option anyway. You can always just use a plain DELETE
. Considerably slower with big tables, but concurrent INSERT
is not blocked at all. The autovacuuming daemon will have to do some more work, too.
It's mostly a trade-off between speed and security.
SQL
The DELETE
command for the slow and sure method could look like this:
DELETE FROM TABLE tbl t
USING (
SELECT created_at FROM tbl WHERE created_at < now() - (interval '3 month')
) d
LEFT JOIN (
SELECT min(created_at) AS created_at
FROM tbl
GROUP BY date_trunc('minute', created_at)
WHERE created_at < now() - (interval '3 month')
) x USING (created_at)
WHERE x.created_at IS NULL
AND d.created_at = t.created_at;
Whenever you dump a mysql table that has an AUTO_INCREMENT column, the next value is always attached to the definition of the table. You should see something like:
) ENGINE=InnoDB AUTO_INCREMENT=<some-number> ...
You may want to consider one of three(3) things
SUGGESTION #1 : mysqldump the database with table structure
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database > my_database_data.sql
That way, the auto_increment is attached to the table definition
SUGGESTION #2 : mysqldump the data and table structure separately
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-create-info > my_database_data.sql
imac2011:Desktop allendar$ /Applications/MAMP/Library/bin/mysqldump -u root -p my_database --no-data > my_database_schema.sql
You would load my_database_schema.sql
first, then my_database_data.sql
SUGGESTION #3 : Read the next auto_increment of every table into a script
You can use a query like this to make the SQL script:
SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,' AUTO_INCREMENT=',autoinc,';')
FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc
FROM information_schema.tables WHERE table_schema='my_database') A;
In the OS, you can do it like this:
SQLSTMT="SELECT CONCAT('TRUNCATE TABLE ',db,'.',tb,'; ALTER TABLE ',db,'.',tb,'AUTO_INCREMENT=',autoinc,';')"
SQLSTMT="${SQLSTMT} FROM (SELECT table_schema db,table_name tb ,auto_increment autoinc"
SQLSTMT="${SQLSTMT} FROM information_schema.tables WHERE table_schema='my_database') A;"
mysql -uroot -p -Ane"${SQLSTMT}" > autoinc.sql
Simply run autoinc.sql
before my_database_data.sql
Give it a Try !!!
Best Answer
Dropping and then recreating all of the foreign keys shouldn't be a problem because you should have them all in your source control system. If that's not the case then you have much bigger problems.
You can generate DROP/CREATE scripts through SSMS. If you right-click on your database then you can go to Tasks->Generate Scripts... and select the tables that you need through the wizard. You'll have to do a lot of cut and paste to get just the FKs. Alternatively, you can dig down to the FKs in the object tree, right-click each one, and generate a script from that menu.
In either case, it sounds like your deploy process for your production system is in some bad shape.