|
On 07/06/2012 07:29 PM, Craig Ringer
wrote:
On 07/03/2012 11:22 PM, Stanislaw Pankevich wrote:
I cannot! use transactions.
Everything in PostgreSQL uses transactions, they are not optional.
I'm assuming you mean you can't use explicit transaction
demarcation, ie BEGIN and COMMIT.
need the fastest cleaning strategy for such case working on
PostgreSQL both 8 and 9.
Just so you know, there isn't really any "PostgreSQL 8" or
"PostgreSQL 9". Major versions are x.y, eg 8.4, 9.0, 9.1 and 9.2
are all distinct major versions. This is different to most
software and IMO pretty damn annoying, but that's how it is.
1) Truncate each table. It is too slow, I think, especially for
empty tables.
Really?!? TRUNCATE should be extremely fast, especially on empty
tables.
You're aware that you can TRUNCATE many tables in one run, right?
TRUNCATE TABLE a, b, c, d, e, f, g;
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.
You can examine the value of SELECT last_value FROM the_sequence ;
that's the equivalent of the MySQL hack you're using. To set it,
use 'setval(...)'.
http://www.postgresql.org/docs/9.1/static/functions-sequence.html
I use Ruby code to iterate through all tables
If you want to be fast, get rid of iteration. Do it all in one
query or a couple of simple queries. Minimize the number of
round-trips and queries.
I'll be truly stunned if the fastest way isn't to just TRUNCATE
all the target tables in a single statement (not iteratively one
by one with separate TRUNCATEs).
Oh, also, you can setval(...) a bunch of sequences at once:
SELECT
setval('first_seq', 0),
setval('second_seq', 0),
setval('third_seq', 0),
setval('fouth_seq', 0);
... etc. You should only need two statements, fast ones, to reset
your DB to the default state.
--
Craig Ringer
|
