Re: [HACKERS] GSoC 2015 proposal: Improve the performance of “ALTER TABLE .. SET LOGGED / UNLOGGED” statement

On Thu, Apr 2, 2015 at 3:24 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>
> On Wed, Mar 25, 2015 at 9:46 PM, Fabrízio de Royes Mello
> <fabriziomello(at)gmail(dot)com> wrote:
> >
http://www.postgresql.org/message-id/CA+TgmoZM+-0R7h0eDPzZjbokVVQ+gAVKChmno4fypVEccW-EqA@mail.gmail.com
>
> I like the idea of the feature a lot, but the proposal to which you
> refer here mentions some problems, which I'm curious how you think you
> might solve. (I don't have any good ideas myself, beyond what I
> mentioned there.)
>

You're right, and we have another design (steps 1 and 2 was implemented
last year):

*** ALTER TABLE changes

1) ATController
1.1) Acquire an AccessExclusiveLock (src/backend/commands/tablecmds.c -
AlterTableGetLockLevel:3023)

2) Prepare to change relpersistence (src/backend/commands/tablecmds.c -
ATPrepCmd:3249-3270)
• check temp table (src/backend/commands/tablecmds.c -
ATPrepChangePersistence:11074)
• check foreign key constraints (src/backend/commands/tablecmds.c -
ATPrepChangePersistence:11102)

3) FlushRelationBuffers, DropRelFileNodeBuffers and smgrimmedsync
(MAIN_FORKNUM, FSM_FORKNUM, VISIBILITYMAP_FORKNUM and INIT_FORKNUM if
exists)

4) Create a new fork called "TRANSIENT INIT FORK":

• from Unlogged to Logged (create _initl fork) (INIT_TO_LOGGED_FORKNUM)
∘ new forkName (src/common/relpath.c) called "_initl"
∘ insert XLog record to drop it if transaction abort

• from Logged to Unlogged (create _initu fork) (INIT_TO_UNLOGGED_FORKUM)
∘ new forkName (src/common/relpath.c) called "_initu"
∘ insert XLog record to drop it if transaction abort

5) Change the relpersistence in catalog (pg_class->relpersistence) (heap,
toast, indexes)

6) Remove/Create INIT_FORK

• from Unlogged to Logged
∘ remove the INIT_FORK and INIT_TO_LOGGED_FORK adding to the
pendingDeletes queue
• from Logged to Unlogged
∘ remove the INIT_TO_UNLOGGED_FORK adding to the pendingDeletes queue
∘ create the INIT_FORK using "heap_create_init_fork"
∘ insert XLog record to drop init fork if the transaction abort

*** CRASH RECOVERY changes

1) During crash recovery
(src/backend/access/transam/xlog.c:6507:ResetUnloggedRelations)

• if the transient fork "_initl" exists then
∘ drop the transient fork "_initl"
∘ if the init fork doesn't exist then create it
∘ reset relation
• if the transient fork "_initu" exists then
∘ drop the transient fork "_initl"
∘ if the init fork exists then drop it
∘ don't reset the relation

Regards,

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello