Thread: Bug in RI
Hi,
I encountered the following problem:
./configure --enable-debug --prefix=/opt/postgres
Edit config.h: BLCKSZ 32768
postgres=# select version(); version
--------------------------------------------------------------- PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc
2.95.2
(1 row)
I have the following table:
CREATE TABLE "folders" ( "nr" int4 NOT NULL, "parent" int4, "name" character varying(100) NOT
NULL, "lang" character varying(2) NOT NULL, "sort_order" int2 DEFAULT 0 NOT NULL, "stylesheet"
charactervarying(100), "introduction" character varying(1000), "template" character varying(100) NOT
NULL, "img_normal" character varying(50), "img_over" character varying(50), "img_active" character
varying(50), PRIMARY KEY ("nr")
);
CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER INSERT OR UPDATE ON
"folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins" ('fk_folders__parent', 'folders', 'folders',
'UNSPECIFIED', 'parent', 'nr');
CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER DELETE ON
"folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('fk_folders__parent', 'folders',
'folders', 'UNSPECIFIED', 'parent', 'nr');
CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER UPDATE ON
"folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('fk_folders__parent', 'folders',
'folders', 'UNSPECIFIED', 'parent', 'nr');
If I do the following query:
update folders set title='Sitemap' where nr=43;
I get the following error in the log:
Server process (pid 31566) exited with status 139 at Thu Sep 21 17:24:39 2000
Terminating any active server processes...
Server processes were terminated at Thu Sep 21 17:24:39 2000
Reinitializing shared memory and semaphores
The Data Base System is starting up
DEBUG: Data Base System is starting up at Thu Sep 21 17:24:39 2000
DEBUG: Data Base System was interrupted being in production at Thu Sep 21
17:24:25 2000
DEBUG: Data Base System is in production state at Thu Sep 21 17:24:39 2000
and the following error in psql:
pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally
before or while processing the request.
A backtrace says:
#0 ri_BuildQueryKeyFull (key=0xbfffe4c8, constr_id=21463, constr_queryno=0, fk_rel=0x0, pk_rel=0x8217c20, argc=6,
argv=0x821a9e0)at
ri_triggers.c:2951
2951 key->fk_relid = fk_rel->rd_id;
(gdb) bt
#0 ri_BuildQueryKeyFull (key=0xbfffe4c8, constr_id=21463, constr_queryno=0, fk_rel=0x0, pk_rel=0x8216780, argc=6,
argv=0x8219540)at
ri_triggers.c:2951
#1 0x813292e in RI_FKey_keyequal_upd () at ri_triggers.c:2853
#2 0x809cfe2 in DeferredTriggerSaveEvent (rel=0x8216780, event=2, oldtup=0x8227bb0, newtup=0x8227ac8) at
trigger.c:1904
#3 0x809c0ed in ExecARUpdateTriggers (estate=0x8225dc8, tupleid=0xbfffe668, newtuple=0x8227ac8) at trigger.c:915
#4 0x80a36a6 in ExecReplace (slot=0x82261e8, tupleid=0xbfffe668, estate=0x8225dc8) at execMain.c:1591
#5 0x80a3261 in ExecutePlan (estate=0x8225dc8, plan=0x8225cb8, operation=CMD_UPDATE, offsetTuples=0,
numberTuples=0, direction=ForwardScanDirection, destfunc=0x8227a60) at execMain.c:1213
#6 0x80a27be in ExecutorRun (queryDesc=0x8226048, estate=0x8225dc8, feature=3, limoffset=0x0, limcount=0x0) at
execMain.c:327
#7 0x8101f84 in ProcessQueryDesc (queryDesc=0x8226048, limoffset=0x0, limcount=0x0) at pquery.c:310
#8 0x8102017 in ProcessQuery (parsetree=0x820a840, plan=0x8225cb8, dest=Remote) at pquery.c:353
#9 0x8100839 in pg_exec_query_dest ( query_string=0x81bae28 "update folders set name='Sitemap' where nr=43;",
dest=Remote,aclOverride=0) at postgres.c:663
#10 0x81006fa in pg_exec_query ( query_string=0x81bae28 "update folders set name='Sitemap' where nr=43;") at
postgres.c:562
#11 0x81018c3 in PostgresMain (argc=4, argv=0xbfffed80, real_argc=5, real_argv=0xbffff734) at postgres.c:1590
#12 0x80e9727 in DoBackend (port=0x81c00d8) at postmaster.c:2009
#13 0x80e92da in BackendStartup (port=0x81c00d8) at postmaster.c:1776
#14 0x80e8499 in ServerLoop () at postmaster.c:1037
#15 0x80e7e5e in PostmasterMain (argc=5, argv=0xbffff734) at postmaster.c:725
#16 0x80b485b in main (argc=5, argv=0xbffff734) at main.c:93
Any ideas? If you need any additional info, please let me know.
Jeroen
Odd, it looks like it had trouble doing the heap_openr
on the relation, although I don't immediately see why...
What does select * from pg_trigger where tgconstrname='fk_folders__parent'
give you?
I wasn't able to duplicate with the table statements below
and dummy values. Do you have a subset of your data that
will cause the probably that you can send?
Stephan Szabo
sszabo@bigpanda.com
On Thu, 21 Sep 2000, Jeroen van Vianen wrote:
> Hi,
>
> I encountered the following problem:
>
> ./configure --enable-debug --prefix=/opt/postgres
> Edit config.h: BLCKSZ 32768
>
> postgres=# select version();
> version
> ---------------------------------------------------------------
> PostgreSQL 7.0.2 on i686-pc-linux-gnu, compiled by gcc 2.95.2
> (1 row)
>
> I have the following table:
>
> CREATE TABLE "folders" (
> "nr" int4 NOT NULL,
> "parent" int4,
> "name" character varying(100) NOT NULL,
> "lang" character varying(2) NOT NULL,
> "sort_order" int2 DEFAULT 0 NOT NULL,
> "stylesheet" character varying(100),
> "introduction" character varying(1000),
> "template" character varying(100) NOT NULL,
> "img_normal" character varying(50),
> "img_over" character varying(50),
> "img_active" character varying(50),
> PRIMARY KEY ("nr")
> );
>
> CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER INSERT OR UPDATE ON
> "folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_check_ins" ('fk_folders__parent', 'folders', 'folders',
> 'UNSPECIFIED', 'parent', 'nr');
>
> CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER DELETE ON
> "folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_noaction_del" ('fk_folders__parent', 'folders',
> 'folders', 'UNSPECIFIED', 'parent', 'nr');
>
> CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER UPDATE ON
> "folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_noaction_upd" ('fk_folders__parent', 'folders',
> 'folders', 'UNSPECIFIED', 'parent', 'nr');
At 10:18 21-9-00 -0700, Stephan Szabo wrote:
>Odd, it looks like it had trouble doing the heap_openr
>on the relation, although I don't immediately see why...
>
>What does
> select * from pg_trigger where
> tgconstrname='fk_folders__parent'
>give you?
First it didn't give me anything (0 rows). After I recreated the constraint
triggers:
CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER INSERT OR UPDATE ON
"folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_check_ins" ('fk_folders__parent', 'folders', 'folders',
'UNSPECIFIED', 'parent', 'nr');
CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER DELETE ON
"folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_del" ('fk_folders__parent', 'folders',
'folders', 'UNSPECIFIED', 'parent', 'nr');
CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER UPDATE ON
"folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
PROCEDURE "RI_FKey_noaction_upd" ('fk_folders__parent', 'folders',
'folders', 'UNSPECIFIED', 'parent', 'nr');
the above query returned three rows:
jeroenv=> select * from pg_trigger where tgconstrname='fk_folders__parent' ; tgrelid | tgname |
tgfoid| tgtype | tgenabled |
tgisconstr
aint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred |
tgna
rgs | tgattr | tgargs
---------+----------------------------+--------+--------+-----------+-----------
-----+--------------------+---------------+--------------+----------------+-----
----+--------+------------------------------------------------------------------
----------- 20152 | RI_ConstraintTrigger_21856 | 1644 | 21 | t | t | fk_folders__parent |
0 | f | f | 6 | |
fk_folders__parent\000folders\000folders\000UNSPECIFIED\000parent
\000nr\000 20152 | RI_ConstraintTrigger_21858 | 1654 | 9 | t | t | fk_folders__parent |
0 | f | f | 6 | |
fk_folders__parent\000folders\000folders\000UNSPECIFIED\000parent
\000nr\000 20152 | RI_ConstraintTrigger_21860 | 1655 | 17 | t | t | fk_folders__parent |
0 | f | f | 6 | |
fk_folders__parent\000folders\000folders\000UNSPECIFIED\000parent
\000nr\000
(3 rows)
But the same query (update folders set title='Sitemap' where nr=43) still
crashes the backend at exactly the same spot.
So, still no clue.
Thanks,
Jeroen
Did you compile from sources or install from a binaries package?
I think it would be handy to get a notice from where I think it's
failing to open the relation to make sure it's getting the correct
parameter there. (I don't have source in front of me to give you
a patch - I'll send one tonight)
Stephan Szabo
sszabo@bigpanda.com
On Fri, 22 Sep 2000, Jeroen van Vianen wrote:
> At 10:18 21-9-00 -0700, Stephan Szabo wrote:
> >Odd, it looks like it had trouble doing the heap_openr
> >on the relation, although I don't immediately see why...
> >
> >What does
> > select * from pg_trigger where
> > tgconstrname='fk_folders__parent'
> >give you?
>
> First it didn't give me anything (0 rows). After I recreated the constraint
> triggers:
>
> CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER INSERT OR UPDATE ON
> "folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_check_ins" ('fk_folders__parent', 'folders', 'folders',
> 'UNSPECIFIED', 'parent', 'nr');
>
> CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER DELETE ON
> "folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_noaction_del" ('fk_folders__parent', 'folders',
> 'folders', 'UNSPECIFIED', 'parent', 'nr');
>
> CREATE CONSTRAINT TRIGGER "fk_folders__parent" AFTER UPDATE ON
> "folders" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_noaction_upd" ('fk_folders__parent', 'folders',
> 'folders', 'UNSPECIFIED', 'parent', 'nr');
>
> the above query returned three rows:
>
> jeroenv=> select * from pg_trigger where tgconstrname='fk_folders__parent' ;
> tgrelid | tgname | tgfoid | tgtype | tgenabled |
> tgisconstr
> aint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred |
> tgna
> rgs | tgattr | tgargs
>
> ---------+----------------------------+--------+--------+-----------+-----------
> -----+--------------------+---------------+--------------+----------------+-----
> ----+--------+------------------------------------------------------------------
> -----------
> 20152 | RI_ConstraintTrigger_21856 | 1644 | 21 | t | t
> | fk_folders__parent | 0 | f | f |
> 6 | |
> fk_folders__parent\000folders\000folders\000UNSPECIFIED\000parent
> \000nr\000
> 20152 | RI_ConstraintTrigger_21858 | 1654 | 9 | t | t
> | fk_folders__parent | 0 | f | f |
> 6 | |
> fk_folders__parent\000folders\000folders\000UNSPECIFIED\000parent
> \000nr\000
> 20152 | RI_ConstraintTrigger_21860 | 1655 | 17 | t | t
> | fk_folders__parent | 0 | f | f |
> 6 | |
> fk_folders__parent\000folders\000folders\000UNSPECIFIED\000parent
> \000nr\000
> (3 rows)
>
> But the same query (update folders set title='Sitemap' where nr=43) still
> crashes the backend at exactly the same spot.
At 21:13 21-9-00 -0700, Stephan Szabo wrote:
>This is a one line patch that will throw a notice with
>what relation name it's trying to open and what it
>got back in RI_FKey_keyequal_upd. It should say
>the name of your table and a number, but I expect
>the number will be 0.
Yes, it is. So I also found the error: I did a rename table and the
constraint triggers were not updated with the new table name.
Maybe a little check should be built in to check for fkey == 0, like this
(from the top of my head, no actual checking):
fk_rel = heap_openr(tgargs[RI_FK_RELNAME_ARGNO], NoLock);
+ if (fk_rel == NULL) {
+ elog(ERROR, "In foreign key constraint, cannot open relname: %s",
+ tgargs[RI_FK_RELNAME_ARGNO]);
+ } pk_rel = trigdata->tg_relation; new_row = trigdata->tg_newtuple; old_row =
trigdata->tg_trigtuple;
Thanks for your help,
Jeroen
Actually, current sources already work better (well, elog rather
than crash).
Eventually, the triggers will reference things by OID rather
than name so renames will work. I'd also like to make the
dependencies known so we can make it work properly when
drop column gets implemented. No known eta at this point
though.
Stephan Szabo
sszabo@bigpanda.com
On Fri, 22 Sep 2000, Jeroen van Vianen wrote:
> At 21:13 21-9-00 -0700, Stephan Szabo wrote:
> >This is a one line patch that will throw a notice with
> >what relation name it's trying to open and what it
> >got back in RI_FKey_keyequal_upd. It should say
> >the name of your table and a number, but I expect
> >the number will be 0.
>
> Yes, it is. So I also found the error: I did a rename table and the
> constraint triggers were not updated with the new table name.
>
> Maybe a little check should be built in to check for fkey == 0, like this
> (from the top of my head, no actual checking):
>
> fk_rel = heap_openr(tgargs[RI_FK_RELNAME_ARGNO], NoLock);
> + if (fk_rel == NULL) {
> + elog(ERROR, "In foreign key constraint, cannot open relname: %s",
> + tgargs[RI_FK_RELNAME_ARGNO]);
> + }
> pk_rel = trigdata->tg_relation;
> new_row = trigdata->tg_newtuple;
> old_row = trigdata->tg_trigtuple;