Обсуждение: BUG #6042: unlogged table with Streaming Replication
The following bug has been logged online: Bug reference: 6042 Logged by: Tomonari Katsumata Email address: katsumata.tomonari@po.ntts.co.jp PostgreSQL version: 9.1beta1 Operating system: RHEL5.3 x86_64 Description: unlogged table with Streaming Replication Details: Hi, 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. 3) Stop Standby. [standby]$ pg_ctl stop 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). Is this the same problem with "BUG #6041"? regards, ---- Tomonari Katsumata
On Fri, May 27, 2011 at 12:26 AM, Tomonari Katsumata <katsumata.tomonari@po.ntts.co.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. > =A0Primary and Standby are same server with different database cluster and > port number. > > 2) create unlogged table on Primary. > =A0=3D# CREATE UNLOGGED TABLE tbl1(i int); > =A0This 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. > =A0=3D# CREATE UNLOGGED TABLE tbl2(i int); > > when I executed 4), any response is not back to my psql. and I canceled t= he > query, I got messages bellow. > --- > Cancel request sent > WARNING: =A0canceling wait for synchronous replication due to user request > DETAIL: =A0The 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=3Dlocal) --=20 Jaime Casanova=A0 =A0 =A0 =A0=A0 www.2ndQuadrant.com Professional PostgreSQL: Soporte y capacitaci=F3n de PostgreSQL
Hi, Jaime thank you for your answer. I understand it. I turned synchronous_commit to "local", I get desirable behavior. I've thought that if there are no standby, the primary would behave like stand-alone... sorry, this is my misunderstanding. regards, (2011/05/27 14:53), Jaime Casanova wrote: > On Fri, May 27, 2011 at 12:26 AM, Tomonari Katsumata > <katsumata.tomonari@po.ntts.co.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) >
On Fri, May 27, 2011 at 7:15 AM, Tomonari Katsumata <katsumata.tomonari@po.ntts.co.jp> wrote: > I've thought that if there are no standby, > the primary would behave like stand-alone... > sorry, this is my misunderstanding. It is a common misunderstanding. The programmed behaviour leads to the most trusted level of robustness. --=20 =A0Simon Riggs=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0=A0 http:/= /www.2ndQuadrant.com/ =A0PostgreSQL Development, 24x7 Support, Training & Services
On Fri, May 27, 2011 at 1:53 AM, Jaime Casanova <jaime@2ndquadrant.com> wrote: > btw, i guess we should be defaulting to asynchronous standbys (ie: > synchronous_commit=local) By default, synchronous_standby_names is empty, which makes synchronous_commit=on equivalent to synchronous_commit=local. So I think the current default is good, because it means you only need to change ONE parameter to switch from async rep to sync rep (namely, synchronous_standby_names). -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company