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

<div dir="ltr"><div class="gmail_extra"><br />On Tue, Mar 4, 2014 at 3:31 AM, Andres Freund <<a
href="mailto:andres@2ndquadrant.com">andres@2ndquadrant.com</a>>wrote:<br />><br />> On 2014-03-04 01:10:50
-0300,Fabrízio de Royes Mello wrote:<br /> > > Today I do something like that:<br />> ><br />> > 1)
createunlogged table tmp_foo ...<br />> > 2) populate 'tmp_foo' table (ETL scripts or whatever)<br />> > 3)
starttransaction<br />> > 4) lock table tmp_foo in access exclusive mode<br /> > > 5) update pg_class set
relpersistence= 'p' where oid = 'tmp_foo':regclass<br />> > 6) drop table foo; -- the old foo table<br />>
>7) alter table tmp_foo rename to foo;<br />> > 8) end transaction<br /> > > 9) run pg_repack in table
'foo'<br/>> ><br />> > I know it's very ugly, but works... and works for standbys too... :-)<br />><br
/>>No, it doesn't work. It just may happen to not fail loudly/visibly in<br /> > some cases. You're absolutely
riskingcorruption of this *and* other<br />> relations when doing so.<br />><br /><br /></div><div
class="gmail_extra">Wellthis already works for some time, but you are correct, exists the risk of corruption!<br /><br
/>Butin my case if all run without any interrupt the relation is switched to logged. I do some checks before and after,
andif something happens with this process we cleanup everything and start from the beginning.<br /></div><div
class="gmail_extra"><br/></div><div class="gmail_extra">Maybe I must run CLUSTER inside the transaction block after
updatepg_class and execute DROP and RENAME after, in a second phase. Maybe this way is more secure. Is it?<br /><br
/>Ifsome crash occurs and PostgreSQL restart I check if the unlogged table 'tmp_foo' exists and then I drop it.<br
/></div><divclass="gmail_extra"><br /></div><div class="gmail_extra">Regards,<br /><br /></div><div
class="gmail_extra">--<br />Fabrízio de Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog sobre TI: <a
href="http://fabriziomello.blogspot.com">http://fabriziomello.blogspot.com</a><br/> >> Perfil Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a></div></div>