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');


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


Thu, 21 Sep 2000 17:41:10 +0200
Jeroen van Vianen <jeroen.van.vianen@satama.com>
Bug in RI

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');


Thu, 21 Sep 2000 10:18:27 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Bug in RI
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


Fri, 22 Sep 2000 00:39:45 +0200
Jeroen van Vianen <jeroen.van.vianen@satama.com>
Re: Bug in RI

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.


Thu, 21 Sep 2000 16:36:38 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Bug in RI
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



Fri, 22 Sep 2000 10:08:12 +0200
Jeroen van Vianen <jeroen.van.vianen@satama.com>
Re: Bug in RI

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;




Fri, 22 Sep 2000 10:03:16 -0700 (PDT)
Stephan Szabo <sszabo@megazone23.bigpanda.com>
Re: Bug in RI