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/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).

--
Craig Ringer