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@gmail.com> wrote:
>
> On Wed, Mar 25, 2015 at 9:46 PM, Fabrízio de Royes Mello
> <fabriziomello@gmail.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

--
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