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

On Tue, Mar 4, 2014 at 5:00 PM, Fabrízio de Royes Mello <
fabriziomello(at)gmail(dot)com> wrote:
>
>
> On Tue, Mar 4, 2014 at 3:29 PM, Andres Freund <andres(at)2ndquadrant(dot)com>
wrote:
> >
> > On 2014-03-04 12:54:02 -0500, Robert Haas wrote:
> > > On Tue, Mar 4, 2014 at 9:50 AM, Andres Freund <andres(at)2ndquadrant(dot)com>
wrote:
> > > > On 2014-03-04 09:47:08 -0500, Robert Haas wrote:
> > > > Can't that be solved by just creating the permanent relation in a
new
> > > > relfilenode? That's equivalent to a rewrite, yes, but we need to do
that
> > > > for anything but wal_level=minimal anyway.
> > >
> > > Yes, that would work. I've tended to view optimizing away the
> > > relfilenode copy as an indispensable part of this work, but that might
> > > be wrongheaded. It would certainly be a lot easier to make this
> > > happen if we didn't insist on that.
> >
> > I think it'd already much better than today's situation, and it's a
> > required codepath for wal_level > logical anyway. So even if somebody
> > wants to make this work without the full copy for minimal, it'd still be
> > a required codepath. So I am perfectly ok with a patch just adding that.
> >
>
> Then is this a good idea for a GSoC project ?
>
> I don't know very well this internals, but I am willing to learn and I
think the GSoC is a good opportunity.
>
> Any of you are willing to mentoring this project?
>

Hi all,

I written the proposal to this feature, so I would like to know if someone
can review.

<proposal>

** Add to PostgreSQL the capacity to making an "Unlogged" table "Logged" **

Introduction

This project will allow 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:

ALTER TABLE name SET LOGGED;

Benefits to the PostgreSQL Community

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 their contents do not propagate to replication
slaves, either.
With the capacity of turning an "unlogged" table in a logged table will
allow us have the better of two features, in other words, we can use an
"unlogged" table to run a bulk load a thousands of lines (ETL scripts) and
get better performance, and then change it to a "logged" table to get
durability of loaded data.

Deliverables

This project will be splitted into 2 (two) deliverables:
1) Allow change an "unlogged" table to "logged" when "wal_level = minimal"
(without propagate their contents to replication slaves)
2) Allow change an "unlogged" table to "logged" when "wal_level != minimal"
(propagating their contents to replication slaves)

Project Schedule

until May 19:
* create a website to the project (wiki.postgresql.org)
* create a public repository to the project (github.com/fabriziomello)
* read what has already been discussed by the community about the project (
http://wiki.postgresql.org/wiki/Todo)
* discuss with the community the best "design" to the feature
* learn about some PostgreSQL internals:
. physical storage for relations (src/backend/catalog/storage.c)
. transaction system (src/backend/access/transam/xact.c)
. two-phase commit (src/backend/access/transam/twophase.c)
. table commands (src/backend/commands/tablecmds.c)
. grammar (src/backend/parser/gram.y)

May 19 - June 23
* evaluate with the mentor and community if is a good start point use the
already sent patch (
http://www.postgresql.org/message-id/263033.9223.qm@web29013.mail.ird.yahoo.com
)
* implementation of the first deliverable:
. change the grammar of PostgreSQL to support "ALTER TABLE ... SET LOGGED"
. implement the routines to change an "unlogged" table to "logged" when
"wal_level = minimal"
* write documentation and the test cases
* submit this first deliverable to the commitfest 2014/06 (
https://commitfest.postgresql.org/action/commitfest_view?id=22)

June 23 - June 27
* review with the Mentor of the work done until now

June 27 - August 18
* implementation of the second deliverable (wal_level != minimal)
* write documentation and the test cases
* submit this second deliverable to the commitfest 2014/09 (webpage don't
created yet)

August 18 - August 22
* final review with the Mentor of all work done.

About the proponent

Fabrízio de Royes Mello
e-mail: fabriziomello(at)gmail(dot)com
twitter: @fabriziomello
github: http://github.com/fabriziomello
linkedin: http://linkedin.com/in/fabriziomello

Currently I help people and teams to take the full potential of relational
databases, especially PostgreSQL, helping teams to design the structure of
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
volunteer work for Brazilian Community of PostgreSQL (www.postgresql.org.br),
supporting mailing lists, organizing events (pgbr.postgresql.org.br) and
some admin tasks. And also I help a little the PostgreSQL Global
Development Group (PGDG) in the implementation of some features and review
of patches (git.postgresql.org).

</proposal>

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