Re: Autovacuum stuck for hours, blocking queries

On Fri, Feb 17, 2017 at 1:38 PM, Rakesh Kumar
<rakeshkumar464(at)outlook(dot)com> wrote:
> LOCK TABLE yourtable <a suitable LOCKMODE>;
> CREATE TEMPORARY TABLE keep AS SELECT * FROM yourtable WHERE <keep>;
> TRUNCATE yourtable;
> INSERT INTO yourtable SELECT * from keep;
> COMMIT;
> ===
> the above snippet assumes truncate in PG can be in a transaction. In other words, while truncate by itself
> is atomic, it can't be rolled back. So in the above case, if "INSERT INTO yourtable SELECT * from keep;" and
> we rollback, will it rollback yourtable.

Yes it can. Truncate has been rollbackable for a while now.

begin;
create table
insert into table
truncate old table
. something goes wrong .
rollback;

Unless I misunderstand your meaning.