Stanislaw Pankevich wrote: > ==== PostgreSQL db, 30 tables with number of rows < 100 (not huge) - the fastest way to clean each > non-empty table and reset unique identifier column of empty ones ==== > > I wonder, what is the fastest way to accomplish this kind of task in PostgreSQL. I am interested in > the fastest solutions ever possible. > I have following assumptions: > > I have 30-100 tables. Let them be 30. > > Half of the tables are empty. > > Each non-empty table has, say, no more than 100 rows. By this I mean, tables are NOT large. > > I need an optional possibility to exclude 2 or 5 or N tables from this procedure. > > I cannot! use transactions. Why? That would definitely speed up everything. > I need the fastest cleaning strategy for such case working on PostgreSQL both 8 and 9. > > I see the following approaches: > > 1) Truncate each table. It is too slow, I think, especially for empty tables. Did you actually try it? That's the king's way to performance questions! Truncating a single table is done in a matter of microseconds, particularly if it is not big. Do you have tens of thousands of tables? > 2) Check each table for emptiness by more faster method, and then if it is empty reset its unique > identifier column (analog of AUTO_INCREMENT in MySQL) to initial state (1), i.e to restore its > last_value from sequence (the same AUTO_INCREMENT analog) back to 1, otherwise run truncate on it. That seems fragile an won't work everywhere. What if the table has no primary key with a DEFAULT that uses a sequence? What if it has such a key, but the DEFAULT was not used for an INSERT? What if somebody manually reset the sequence? Besides, how do you find out what the sequence for a table's primary key is? With a SELECT, I guess. That SELECT is probably not faster than a simple TRUNCATE. > Also my guess was that EXISTS(SELECT something FROM TABLE) could somehow be used to work good as one > of the "check procedure" units, cleaning procedure should consist of, but haven't accomplished it too. You could of course run a SELECT 1 FROM table LIMIT 1, but again I don't think that this will be considerably faster than just truncating the table. > I would appreciate any hints on how this procedure could be accomplished in PostgreSQL native way. > > Thanks! > > UPDATE: > > I need all this to run unit and integration tests for Ruby or Ruby on Rails projects. Each test should > have a clean DB before it runs, or to do a cleanup after itself (so called teardown). Transactions are > very good, but they become unusable when running tests against particular webdrivers, in my case the > switch to truncation strategy is needed. Once I updated that with reference to RoR, please do not post > here the answers about "Obviously, you need DatabaseCleaner for PG" and so on and so on. I completely fail to understand what you talk about here. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
