Обсуждение: [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