Re: 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.

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