Re: BUG #6042: unlogged table with Streaming Replication

On Fri, May 27, 2011 at 12:26 AM, Tomonari Katsumata
<katsumata(dot)tomonari(at)po(dot)ntts(dot)co(dot)jp> wrote:
>
>
> I've checked "unlogged table" and "Streaming Replication".
> I'm thinking about using unlogged tables as work-tables on Primary.
>
> 1) construct Streaming Replication Environment.
>  Primary and Standby are same server with different database cluster and
> port number.
>
> 2) create unlogged table on Primary.
>  =# CREATE UNLOGGED TABLE tbl1(i int);
>  This table is available on primary only.
>

because streaming replication works shipping WAL records (the records
of the transactional log) to the standby but because UNLOGGED tables
are not logged to WAL i guess those always will be empty on standby,
but the table should appear on the standby, i guess

> 4) create unlogged table on Primary again.
>  =# CREATE UNLOGGED TABLE tbl2(i int);
>
> when I executed 4), any response is not back to my psql. and I canceled the
> query, I got messages bellow.
> ---
> Cancel request sent
> WARNING:  canceling wait for synchronous replication due to user request
> DETAIL:  The transaction has already committed locally, but may not have
> been replicated to the standby.
> CREATE TABLE
> ---
> and the table has been created.
>
> I think It's strange behavior(a canceled table has been created).
>

actually, you're not cancelling the creation... the table has been
created and the wal is being sent to the standby (because the standby
is a synchronous standby, it keeps waiting until the standby aknlowdge
to have received the wal), so what you are cancelling now is the
primary waiting for the standby...

btw, i guess we should be defaulting to asynchronous standbys (ie:
synchronous_commit=local)

--
Jaime Casanova         www.2ndQuadrant.com
Professional PostgreSQL: Soporte y capacitación de PostgreSQL