Re: [RFC] Add an until-0 loop in psql - Mailing list pgsql-hackers

From Pavel Stehule
Subject Re: [RFC] Add an until-0 loop in psql
Date
Msg-id CAFj8pRAfzuSWH5x7tKMMbr9ay69CYH9e+itAjNsE=Aevm0Tycg@mail.gmail.com
Whole thread Raw
In response to [RFC] Add an until-0 loop in psql  (Pierre Ducroquet <pierre.ducroquet@people-doc.com>)
List pgsql-hackers
Hi

2018-04-24 9:58 GMT+02:00 Pierre Ducroquet <pierre.ducroquet@people-doc.com>:
Hi

When running database migrations with .sql files on a live database, it's not
uncommon to have to run a migration in a loop to prevent a big lock on a
table.
For instance if one want to delete some old datas from a big table one would
write :

DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE bad = true
LIMIT 1000);
VACUUM big_table;

Right now, doing this is quite inefficient. We either have to write a script
in another language, or run psql in a shell loop and wait for the migration to
stop altering rows.

The attached **proof of concept** patch (I insist, it's a 15 minutes hack
sprint with no previous knowledge of psql code) implements an 'until-0' loop
in psql.
The previous migration could be simply written as :

\until-0
BEGIN;
DELETE FROM big_table WHERE id IN (SELECT id FROM big_table WHERE bad = true
LIMIT 1000);
VACUUM big_table;
COMMIT;
\end-until

And psql will execute it until there is no row affected in the inner queries.

I am willing to write a proper patch for this (I hope the tell/seek is an
acceptable implementation…), but I prefer having some feedback first.

I like this idea, but it is really hack :)

In this case, the cycle should be \repeat ... and \end-repeat-until

The expression should be more generic maybe.

Regards

Pavel


Thanks

 Pierre

pgsql-hackers by date:

Previous
From: Konstantin Knizhnik
Date:
Subject: Re: Built-in connection pooling
Next
From: Devrim Gündüz
Date:
Subject: GCC 8 warnings