Обсуждение: [DBmirror Issue] recordchange() dies in packageData unless there is aPRIMARY KEY on DELETE and UPDATEs.
[DBmirror Issue] recordchange() dies in packageData unless there is aPRIMARY KEY on DELETE and UPDATEs.
От
"Rajesh Kumar Mallah."
Дата:
Hi Is anyone using dbmirror in production? I Intend to replicate a set of tables between local and remote servers. and plan to use dbmirror. I observe that unless a table has a primary key the trigger recordchange() which is supposed to be attached to a replicated table does not works properly and causes postmaster to crash. INSERT works but not update and deletes. below is a debug outputing from pending.c I am using 7.2.1 and DBmirror: $Id: pending.c,v 1.1 2002/06/23 21:58:08 momjian Exp $ Regds Mallah. tradein_clients=# CREATE TABLE test (a integer , b integer); CREATE tradein_clients=# tradein_clients=# tradein_clients=# CREATE TRIGGER "test_trig" AFTER INSERT OR DELETE OR UPDATE ON "test" FOR EACH ROW EXECUTE PROCEDURE "recordchange"(); ]CREATE tradein_clients=# tradein_clients=# tradein_clients=# INSERT INTO test values(1,1); NOTICE: row successfully stored in pending table NOTICE: a NOTICE: 1 NOTICE: Starting format loop NOTICE: "a"='1' NOTICE: b NOTICE: 1 NOTICE: Starting format loop NOTICE: "a"='1' "b"='1' NOTICE: Returning NOTICE: INSERT SUCCESFULL NOTICE: DOne storing keyinfo NOTICE: Returning on success INSERT 183571162 1 tradein_clients=# tradein_clients=# tradein_clients=# INSERT INTO test values(1,3); NOTICE: row successfully stored in pending table NOTICE: a NOTICE: 1 NOTICE: Starting format loop NOTICE: "a"='1' NOTICE: b NOTICE: 3 NOTICE: Starting format loop NOTICE: "a"='1' "b"='3' NOTICE: Returning NOTICE: INSERT SUCCESFULL NOTICE: DOne storing keyinfo NOTICE: Returning on success INSERT 183571165 1 tradein_clients=# DELETE from test; NOTICE: row successfully stored in pending table NOTICE: Executing: SELECT indkey FROM pg_index WHERE indisprimary='t' AND indrelid=183571159 NOTICE: Could not select primary index key server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !# -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
Hi Steven , thanks for the reply and letting me know that a primary key is essential for the replicated tables. For your information pending.c used to crash postmaster even when it was not in debug mode. (it was only then i defined DEBUG_OUTPUT to see whats going on). Thanks for the patch. But what will it say when there is no primary key in the table? anyway i will apply the patch and let u know. I am using dbmirror for asynchronous replication so speen of replication is not important. Regards Mallah. > On Mon, 23 Sep 2002, Rajesh Kumar Mallah. wrote: > >> Hi >> >> Is anyone using dbmirror in production? > > We've been using it in production for sometime now and haven't been having any problems with > it. > >> >> I Intend to replicate a set of tables between local and remote servers. and plan to use >> dbmirror. >> > > The speed of replicating edits over a WAN isn't that great. > If you perform a lot of edits in a short period of time there could be a backlog. > >> I observe that unless a table has a primary key the trigger recordchange() which is supposed >> to be attached to a replicated table does not works properly and causes postmaster to crash. >> > > dbmirror requires that all tables that you replicate have primary keys (This should be said > more explictly in the README) > > However it still should not crash(In defence it will only crash the postmaster when you > compile dbmirror in debug mode). > > The attatched patch to pending.c should prevent it from crashing the postmaster. Let me know > if it fixes the problem and I'll submit it to pgpatches. > > > *** pending.c Mon Sep 23 20:24:04 2002 > --- /tmp/pending.c Mon Sep 23 20:23:43 2002 > *************** > *** 226,232 **** > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! elog(NOTICE, cpKeyData); > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > --- 226,235 ---- > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! if(cpKeyData != NULL ) > ! { > ! elog(NOTICE, cpKeyData); > ! } > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > -- > Steven Singer ssinger@navtechinc.com Aircraft Performance > Systems Phone: 519-747-1170 ext 282 Navtech Systems Support Inc. > AFTN: CYYZXNSX SITA: YYZNSCR Waterloo, Ontario ARINC: YKFNSCR ----------------------------------------- Get your free web based email at trade-india.com. "India's Leading B2B eMarketplace.!" http://www.trade-india.com/
Re: [DBmirror Issue] recordchange() dies in packageData unless there is a PRIMARY KEY on DELETE and UPDATEs.
От
"Rajesh Kumar Mallah."
Дата:
Hi can you help me with applying the patch i dunno much abt patch # patch -p0 < patch patch: **** malformed patch at line 7: PRIMARY); regds mallah. On Tuesday 24 September 2002 02:18, Steven Singer wrote: > On Mon, 23 Sep 2002, Rajesh Kumar Mallah. wrote: > > Hi > > > > Is anyone using dbmirror in production? > > We've been using it in production for sometime now and haven't been having > any problems with it. > > > I Intend to replicate a set of tables between local and remote servers. > > and plan to use dbmirror. > > The speed of replicating edits over a WAN isn't that great. > If you perform a lot of edits in a short period of time there could be a > backlog. > > > I observe that unless a table has a primary key the trigger > > recordchange() which is supposed to be attached to a replicated table > > does not works properly and causes postmaster to crash. > > dbmirror requires that all tables that you replicate have primary > keys(This should be said more explictly in the README) > > However it still should not crash(In defence it will only crash the > postmaster when you compile dbmirror in debug mode). > > The attatched patch to pending.c should prevent it from crashing the > postmaster. Let me know if it fixes the problem and I'll submit it to > pgpatches. > > > *** pending.c Mon Sep 23 20:24:04 2002 > --- /tmp/pending.c Mon Sep 23 20:23:43 2002 > *************** > *** 226,232 **** > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! elog(NOTICE, cpKeyData); > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > --- 226,235 ---- > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! if(cpKeyData != NULL ) > ! { > ! elog(NOTICE, cpKeyData); > ! } > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
[Rajesh Kumar Mallah] > Hi can you help me with applying the patch > i dunno much abt patch > > # patch -p0 < patch > patch: **** malformed patch at line 7: PRIMARY); It looks like a context patch. patch --help should tell you how to apply a context patch. It will also tell you the basic syntax. Suppose I had a patch in a file called foobar.patch. And the file I wanted to patch was called foobar. I'd apply it like this: patch foobar foobar.patch Cheers, // mark -
"Rajesh Kumar Mallah." <mallah@trade-india.com> writes: > # patch -p0 < patch > patch: **** malformed patch at line 7: PRIMARY); I think the patch got mucked up by line-wrapping, either in your mail program or Steve's ... regards, tom lane
Re: [DBmirror Issue] recordchange() dies in packageData unless there is a PRIMARY KEY on DELETE and UPDATEs.
От
"Rajesh Kumar Mallah."
Дата:
Hi Steven, Your most recent version pending.c does not crashes postmaster anymore when PRIMARY KEYS are missing. Instead it raises notices and errors: tradein_clients=> BEGIN work; UPDATE iid_listing set city='DELHI testing' where list_id=23312; BEGIN NOTICE: Could not select primary index key NOTICE: Error obtaining primary key values ERROR: Operation could not be mirrored Regds Mallah. On Tuesday 24 September 2002 02:18, Steven Singer wrote: > On Mon, 23 Sep 2002, Rajesh Kumar Mallah. wrote: > > Hi > > > > Is anyone using dbmirror in production? > > We've been using it in production for sometime now and haven't been having > any problems with it. > > > I Intend to replicate a set of tables between local and remote servers. > > and plan to use dbmirror. > > The speed of replicating edits over a WAN isn't that great. > If you perform a lot of edits in a short period of time there could be a > backlog. > > > I observe that unless a table has a primary key the trigger > > recordchange() which is supposed to be attached to a replicated table > > does not works properly and causes postmaster to crash. > > dbmirror requires that all tables that you replicate have primary > keys(This should be said more explictly in the README) > > However it still should not crash(In defence it will only crash the > postmaster when you compile dbmirror in debug mode). > > The attatched patch to pending.c should prevent it from crashing the > postmaster. Let me know if it fixes the problem and I'll submit it to > pgpatches. > > > *** pending.c Mon Sep 23 20:24:04 2002 > --- /tmp/pending.c Mon Sep 23 20:23:43 2002 > *************** > *** 226,232 **** > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! elog(NOTICE, cpKeyData); > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > --- 226,235 ---- > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! if(cpKeyData != NULL ) > ! { > ! elog(NOTICE, cpKeyData); > ! } > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); -- Rajesh Kumar Mallah, Project Manager (Development) Infocom Network Limited, New Delhi phone: +91(11)6152172 (221) (L) ,9811255597 (M) Visit http://www.trade-india.com , India's Leading B2B eMarketplace.
On Mon, 23 Sep 2002, Rajesh Kumar Mallah. wrote: > Hi > > Is anyone using dbmirror in production? We've been using it in production for sometime now and haven't been having any problems with it. > > I Intend to replicate a set of tables between local and remote servers. > and plan to use dbmirror. > The speed of replicating edits over a WAN isn't that great. If you perform a lot of edits in a short period of time there could be a backlog. > I observe that unless a table has a primary key the trigger recordchange() > which is supposed to be attached to a replicated table does not works properly > and causes postmaster to crash. > dbmirror requires that all tables that you replicate have primary keys(This should be said more explictly in the README) However it still should not crash(In defence it will only crash the postmaster when you compile dbmirror in debug mode). The attatched patch to pending.c should prevent it from crashing the postmaster. Let me know if it fixes the problem and I'll submit it to pgpatches. *** pending.c Mon Sep 23 20:24:04 2002 --- /tmp/pending.c Mon Sep 23 20:23:43 2002 *************** *** 226,232 **** /* pplan = SPI_saveplan(pplan); */ cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, PRIMARY); #if defined DEBUG_OUTPUT ! elog(NOTICE, cpKeyData); #endif saPlanData[0] = PointerGetDatum(cpKeyData); --- 226,235 ---- /* pplan = SPI_saveplan(pplan); */ cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, PRIMARY); #if defined DEBUG_OUTPUT ! if(cpKeyData != NULL ) ! { ! elog(NOTICE, cpKeyData); ! } #endif saPlanData[0] = PointerGetDatum(cpKeyData); -- Steven Singer ssinger@navtechinc.com Aircraft Performance Systems Phone: 519-747-1170 ext 282 Navtech Systems Support Inc. AFTN: CYYZXNSX SITA: YYZNSCR Waterloo, Ontario ARINC: YKFNSCR
Your patch has been added to the PostgreSQL unapplied patches list at: http://candle.pha.pa.us/cgi-bin/pgpatches I will try to apply it within the next 48 hours. --------------------------------------------------------------------------- Steven Singer wrote: > On Mon, 23 Sep 2002, Rajesh Kumar Mallah. wrote: > > > Hi > > > > Is anyone using dbmirror in production? > > We've been using it in production for sometime now and haven't been having > any problems with it. > > > > > I Intend to replicate a set of tables between local and remote servers. > > and plan to use dbmirror. > > > > The speed of replicating edits over a WAN isn't that great. > If you perform a lot of edits in a short period of time there could be a > backlog. > > > I observe that unless a table has a primary key the trigger recordchange() > > which is supposed to be attached to a replicated table does not works properly > > and causes postmaster to crash. > > > > dbmirror requires that all tables that you replicate have primary > keys(This should be said more explictly in the README) > > However it still should not crash(In defence it will only crash the > postmaster when you compile dbmirror in debug mode). > > The attatched patch to pending.c should prevent it from crashing the > postmaster. Let me know if it fixes the problem and I'll submit it to > pgpatches. > > > *** pending.c Mon Sep 23 20:24:04 2002 > --- /tmp/pending.c Mon Sep 23 20:23:43 2002 > *************** > *** 226,232 **** > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! elog(NOTICE, cpKeyData); > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > --- 226,235 ---- > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! if(cpKeyData != NULL ) > ! { > ! elog(NOTICE, cpKeyData); > ! } > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > > > > > -- > Steven Singer ssinger@navtechinc.com > Aircraft Performance Systems Phone: 519-747-1170 ext 282 > Navtech Systems Support Inc. AFTN: CYYZXNSX SITA: YYZNSCR > Waterloo, Ontario ARINC: YKFNSCR > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073
Patch withdrawn by author. Newer version already applied. --------------------------------------------------------------------------- Steven Singer wrote: > On Mon, 23 Sep 2002, Rajesh Kumar Mallah. wrote: > > > Hi > > > > Is anyone using dbmirror in production? > > We've been using it in production for sometime now and haven't been having > any problems with it. > > > > > I Intend to replicate a set of tables between local and remote servers. > > and plan to use dbmirror. > > > > The speed of replicating edits over a WAN isn't that great. > If you perform a lot of edits in a short period of time there could be a > backlog. > > > I observe that unless a table has a primary key the trigger recordchange() > > which is supposed to be attached to a replicated table does not works properly > > and causes postmaster to crash. > > > > dbmirror requires that all tables that you replicate have primary > keys(This should be said more explictly in the README) > > However it still should not crash(In defence it will only crash the > postmaster when you compile dbmirror in debug mode). > > The attatched patch to pending.c should prevent it from crashing the > postmaster. Let me know if it fixes the problem and I'll submit it to > pgpatches. > > > *** pending.c Mon Sep 23 20:24:04 2002 > --- /tmp/pending.c Mon Sep 23 20:23:43 2002 > *************** > *** 226,232 **** > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! elog(NOTICE, cpKeyData); > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > --- 226,235 ---- > /* pplan = SPI_saveplan(pplan); */ > cpKeyData = packageData(tTupleData, tTupleDesc, tpTrigData, > PRIMARY); > #if defined DEBUG_OUTPUT > ! if(cpKeyData != NULL ) > ! { > ! elog(NOTICE, cpKeyData); > ! } > #endif > saPlanData[0] = PointerGetDatum(cpKeyData); > > > > > > -- > Steven Singer ssinger@navtechinc.com > Aircraft Performance Systems Phone: 519-747-1170 ext 282 > Navtech Systems Support Inc. AFTN: CYYZXNSX SITA: YYZNSCR > Waterloo, Ontario ARINC: YKFNSCR > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup. | Newtown Square, Pennsylvania 19073