Re: GSoC proposal - "make an unlogged table logged"

On Tue, Mar 4, 2014 at 3:31 AM, Andres Freund <andres(at)2ndquadrant(dot)com>
wrote:
>
> On 2014-03-04 01:10:50 -0300, Fabrízio de Royes Mello wrote:
> > Today I do something like that:
> >
> > 1) create unlogged table tmp_foo ...
> > 2) populate 'tmp_foo' table (ETL scripts or whatever)
> > 3) start transaction
> > 4) lock table tmp_foo in access exclusive mode
> > 5) update pg_class set relpersistence = 'p' where oid =
'tmp_foo':regclass
> > 6) drop table foo; -- the old foo table
> > 7) alter table tmp_foo rename to foo;
> > 8) end transaction
> > 9) run pg_repack in table 'foo'
> >
> > I know it's very ugly, but works... and works for standbys too... :-)
>
> No, it doesn't work. It just may happen to not fail loudly/visibly in
> some cases. You're absolutely risking corruption of this *and* other
> relations when doing so.
>

Well this already works for some time, but you are correct, exists the risk
of corruption!

But in my case if all run without any interrupt the relation is switched to
logged. I do some checks before and after, and if something happens with
this process we cleanup everything and start from the beginning.

Maybe I must run CLUSTER inside the transaction block after update pg_class
and execute DROP and RENAME after, in a second phase. Maybe this way is
more secure. Is it?

If some crash occurs and PostgreSQL restart I check if the unlogged table
'tmp_foo' exists and then I drop it.

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog sobre TI: http://fabriziomello.blogspot.com
>> Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello