[INTERFACES] ForeignKey trigger problems - Mailing list pgsql-interfaces

Hello,

  I have modified the 'refint.c' and 'refint.example' in 'contrib/spi'
to do cascaded updates. I need to have both cascaded updates and
cascede deletes on several tables to accomodate for the build-in
facilities of M$-Access. The original 'refint.c' trigger was doing
cascaded deletes, but it was deleting the foreign key rows in stead
of updating the values as I need.

  My mod's works fine the very first time that run a program, e.g.
psql. I have made the trigger fire *after* the update, (to get
the changes in the 'primary key' table visible when doing updates
on the 'foreign key' tables), but it only works the first time I
do the update. If I exit and reenter the program, I can again do
*one* update correctly.

  I have just downloaded the latest version: 6.4.2 and installed
everything from scratch (no left-overs from previous versions).
On the system where I have implemented PostgreSQL, I am running
RedHat 5.x kernel 2.0.32 with glibc2.

  What am I doing wrong?

 Leif



  This is an extract from my psql session (I have included the
corresponding log file below. Please forgive me for not
gzipping/attaching/.... ):

----- psql session: -----------------------------------

  [snip]

CREATE TRIGGER ordrestatus_fkey AFTER DELETE OR UPDATE ON ordrestatus FOR EACH ROW
EXECUTE PROCEDURE
check_foreign_key (1, 'cascade', 'ordrestatus', 'ordre', 'ordrestatus');
CREATE
EOF

  [snip]

ut32_be_i=> update ordrestatus set ordrestatus = 5 where ordrestatus = 3;
ERROR:  ordre_t2: tuple references non-existing key in ordrestatus
ut32_be_i=> \q
[dba@ljserv psql]$ psql ut32_be_i
Welcome to the POSTGRESQL interactive sql monitor:
  Please read the file COPYRIGHT for copyright terms of POSTGRESQL

   type \? for help on slash commands
   type \q to quit
   type \g or terminate with semicolon to execute query
 You are currently connected to the database: ut32_be_i

ut32_be_i=> update ordrestatus set ordrestatus = 5 where ordrestatus = 3;
NOTICE:  Cascade, Update(0), new=5
UPDATE 1
ut32_be_i=> \q

-----------------------------------

   The main change I made to the 'refint.c' was to duplicate the 'settonull'
case making an update on the 'foreign key' table. (Ask for it and you'll
get it ;-). In the log you'll find references to ordre_t2, which is a
'foreign key' check:

CREATE TRIGGER ordre_t2 BEFORE INSERT OR UPDATE ON ordre FOR EACH ROW
EXECUTE PROCEDURE
check_primary_key ('ordrestatus', 'ordrestatus', 'ordrestatus');

  where 'check_primary_key' is the unmodified trigger function from
'refint.c'.


----- pgsql.log: -----------------------------------

FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
binding ShmemCreate(key=52e2c1, size=831176)
/usr/local/pgsql/bin/postmaster: ServerLoop:        handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:        handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:        handling writing 5
/usr/local/pgsql/bin/postmaster: BackendStartup: pid 10483 user dba db ut32_be_i socket 5
/usr/local/pgsql/bin/postmaster child[10483]: starting with (/usr/local/pgsql/bin/postgres, -p, -d2, -P5, -v131072,
ut32_be_i,) 
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
debug info:
    User         = dba
    RemoteHost   = localhost
    RemotePort   = 0
    DatabaseName = ut32_be_i
    Verbose      = 2
    Noversion    = f
    timings      = f
    dates        = Normal
    bufsize      = 64
    sortmem      = 512
    query echo   = f
InitPostgres
StartTransactionCommand
query: select * from ordrestatus;
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: select * from ordre;
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: update ordrestatus set ordrestatus = 3 where ordrestatus = 5;
ProcessQuery
NOTICE:  Cascade, Update(0), new=3
query: update ordre set ordrestatus = '3' where ordrestatus = $1
query: select 1 from ordrestatus where ordrestatus = $1
query: select 1 from ordretype where ordretype = $1
CommitTransactionCommand
StartTransactionCommand
query: select * from ordrestatus;
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: select * from ordre;
ProcessQuery
CommitTransactionCommand
StartTransactionCommand
query: update ordrestatus set ordrestatus = 5 where ordrestatus = 3;
ProcessQuery
ERROR:  ordre_t2: tuple references non-existing key in ordrestatus
AbortCurrentTransaction
StartTransactionCommand
query: DROP TRIGGER ordretype_fkey ON ordretype;
ProcessUtility: DROP TRIGGER ordretype_fkey ON ordretype;
CommitTransactionCommand
StartTransactionCommand
query: CREATE TRIGGER ordretype_fkey AFTER DELETE OR UPDATE ON ordretype FOR EACH ROW EXECUTE PROCEDURE
check_foreign_key(1, 'cascade', 'ordretype', 'ordre', 'ordretype'); 
ProcessUtility: CREATE TRIGGER ordretype_fkey AFTER DELETE OR UPDATE ON ordretype FOR EACH ROW
EXECUTE PROCEDURE
check_foreign_key (1, 'cascade', 'ordretype', 'ordre', 'ordretype');
CommitTransactionCommand
StartTransactionCommand
query: DROP TRIGGER ordrestatus_fkey ON ordrestatus;
ProcessUtility: DROP TRIGGER ordrestatus_fkey ON ordrestatus;
CommitTransactionCommand
StartTransactionCommand
query: CREATE TRIGGER ordrestatus_fkey AFTER DELETE OR UPDATE ON ordrestatus FOR EACH ROW EXECUTE PROCEDURE
check_foreign_key(1, 'cascade', 'ordrestatus', 'ordre', 'ordrestatus'); 
ProcessUtility: CREATE TRIGGER ordrestatus_fkey AFTER DELETE OR UPDATE ON ordrestatus FOR EACH ROW
EXECUTE PROCEDURE
check_foreign_key (1, 'cascade', 'ordrestatus', 'ordre', 'ordrestatus');
CommitTransactionCommand
StartTransactionCommand
query: update ordrestatus set ordrestatus = 5 where ordrestatus = 3;
ProcessQuery
ERROR:  ordre_t2: tuple references non-existing key in ordrestatus
AbortCurrentTransaction
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 10483 exited with status 0
/usr/local/pgsql/bin/postmaster: ServerLoop:        handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:        handling reading 5
/usr/local/pgsql/bin/postmaster: ServerLoop:        handling writing 5
/usr/local/pgsql/bin/postmaster: BackendStartup: pid 10495 user dba db ut32_be_i socket 5
/usr/local/pgsql/bin/postmaster child[10495]: starting with (/usr/local/pgsql/bin/postgres, -p, -d2, -P5, -v131072,
ut32_be_i,) 
FindExec: found "/usr/local/pgsql/bin/postgres" using argv[0]
debug info:
    User         = dba
    RemoteHost   = localhost
    RemotePort   = 0
    DatabaseName = ut32_be_i
    Verbose      = 2
    Noversion    = f
    timings      = f
    dates        = Normal
    bufsize      = 64
    sortmem      = 512
    query echo   = f
InitPostgres
StartTransactionCommand
query: update ordrestatus set ordrestatus = 5 where ordrestatus = 3;
ProcessQuery
NOTICE:  Cascade, Update(0), new=5
query: update ordre set ordrestatus = '5' where ordrestatus = $1
query: select 1 from ordrestatus where ordrestatus = $1
query: select 1 from ordretype where ordretype = $1
CommitTransactionCommand
proc_exit(0) [#0]
shmem_exit(0) [#0]
exit(0)
/usr/local/pgsql/bin/postmaster: reaping dead processes...
/usr/local/pgsql/bin/postmaster: CleanupProc: pid 10495 exited with status 0

------------------------------