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

<div dir="ltr"><div class="gmail_extra">On Thu, Mar 6, 2014 at 5:04 PM, Robert Haas <<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br />><br />> On Thu, Mar 6, 2014 at 2:52
PM,Fabrízio de Royes Mello<br /> > <<a href="mailto:fabriziomello@gmail.com">fabriziomello@gmail.com</a>>
wrote:<br/>> > On Thu, Mar 6, 2014 at 4:42 PM, Robert Haas <<a
href="mailto:robertmhaas@gmail.com">robertmhaas@gmail.com</a>>wrote:<br /> > >> I think this isn't a good
design. Per the discussion between Andres<br />> >> and I, I think that I think you should do is make ALTER
TABLE.. SET<br />> >> LOGGED work just like VACUUM FULL, with the exception that it will set<br /> >
>>a different relpersistence for the new relfilenode.  If you do it that<br />> >> way, this will be
lessefficient, but much simpler, and you might<br />> >> actually finish it in one summer.<br /> >
>><br/>> ><br />> > Do it like 'VACUUM FULL' for any wal_level?<br />><br />> Yep.  Anything
elseappears to be a research problem.<br />><br /><br />Updated proposal:<br /><br /><proposal><br /><br />**
Addto PostgreSQL the capacity to making an “Unlogged” table “Logged” **<br /><br />Introduction<br /><br />This project
willallow to change an “unlogged” table (that doesn’t create transaction logs - WAL files) and it’s dependencies to a
“logged”table, in other words, a regular table that create WAL files. To make this happen we'll introduce a new SQL
syntax:<br/><br />ALTER TABLE name SET LOGGED;<br /><br /><br />Benefits to the PostgreSQL Community<br /><br />The
 “unlogged”tables feature was introduced by 9.1 version, and provide better write performance than regular tables
(logged),but are not crash-safe. Their contents are automatically discarded (cleared) in a case of a server crash, and
theircontents do not propagate to replication slaves, either.<br /> With the capacity of turning an “unlogged” table in
alogged table will allow us have the better of two features, in other words, we can use an "unlogged" table to run a
bulkload a thousands of lines (ETL scripts) and get better performance, and then change it to a "logged" table to get
durabilityof loaded data.<br /><br /><br />Deliverables<br /><br />This project has just one deliverable at the end.
Thedeliverable will be the implementation of the routines that transform an “unlogged” table to “logged”, using the
samealgorithm of the “vacuum full”, with the exception that it will set a different “relpersistence” for the new
“relfilenode”.<br/><br /><br />Project Schedule<br /><br />until May 19:<br />* create a website to the project (<a
href="http://wiki.postgresql.org">wiki.postgresql.org</a>)<br/>* create a public repository to the project (<a
href="http://github.com/fabriziomello">github.com/fabriziomello</a>)<br/> * read what has already been discussed by the
communityabout the project (<a href="http://wiki.postgresql.org/wiki/Todo">http://wiki.postgresql.org/wiki/Todo</a>)<br
/>*as already discussed in pgsql-hackers mailing list this feature will be implemented similar to “vacuum full”, with
theexception that it will set a differente “relpersistence” for the new “relfilenode”<br /> * learn about some
PostgreSQLinternals:<br />  . grammar (src/backend/parser/gram.y)<br />  . vacuum full (src/backend/commands/[vacuum.c
|cluster.c])<br /><br />May 19 - June 23<br />* implementation of the first prototype:<br />  . change the grammar of
PostgreSQLto support “ALTER TABLE … SET LOGGED”<br />   . implement and/or adapt the routines to change an "unlogged"
tableto "logged" (similar to “vacuum full”)<br />* write documentation and the test cases<br />* submit this first
prototypeto the commitfest 2014/06 (<a
href="https://commitfest.postgresql.org/action/commitfest_view?id=22">https://commitfest.postgresql.org/action/commitfest_view?id=22</a>)<br
/><br/>June 23 - June 27<br />* review with the Mentor of the work done until now<br /><br />June 27 - August 18<br />*
dothe adjustments based on the community feedback during the commitfest 2014/06<br />* submit to the commitfest 2014/09
forfinal evaluation and maybe will be committed to 9.5 version (webpage don’t created yet)<br /><br />August 18 -
August22<br />* final review with the Mentor of all work done.<br /><br /><br />About the proponent<br /><br />Fabrízio
deRoyes Mello<br />e-mail: <a href="mailto:fabriziomello@gmail.com">fabriziomello@gmail.com</a><br /> twitter:
@fabriziomello<br/>github: <a href="http://github.com/fabriziomello">http://github.com/fabriziomello</a><br />linkedin:
<ahref="http://linkedin.com/in/fabriziomello">http://linkedin.com/in/fabriziomello</a><br /><br /> Currently I help
peopleand teams to take the full potential of relational databases, especially PostgreSQL, helping teams to design the
structureof the database (modeling), build physical architecture (database schema), programming (procedural languages),
SQL(usage, tuning, best practices), optimization and orchestration of instances in production too. I perform a
volunteerwork for Brazilian Community of PostgreSQL (<a href="http://www.postgresql.org.br">www.postgresql.org.br</a>),
supportingmailing lists, organizing events (<a href="http://pgbr.postgresql.org.br">pgbr.postgresql.org.br</a>) and
someadmin tasks. And also I help a little the PostgreSQL Global Development Group (PGDG) in the implementation of some
featuresand review of patches (<a href="http://git.postgresql.org">git.postgresql.org</a>).<br /><br
/></proposal><br/><br />--<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>