Referential Integrity functions for 2 different users - Mailing list pgsql-general

Q: why do 2 different users have 2 different versions of
referential-integrity checks ?

Background: Ive done several different builds of 6.5.1 and 6.5.2
(from tar) on
linux (RH6.0), and ran refint.sql, and had some trouble with
regression tests.
Due to these probs (which showed as trigger-function errs), I
tried to
implement the Foreign Key checks as rules.

Bad idea perhaps, but my install didnt know about
check_primary_key(),
which is apparently needed to pass the regression tests.

FWIW, perhaps contrib should be under src (ie src/contrib) then
the
src/Makefile could automatically build it.


Anyway, the function-definitions stored w/in postgres for the 2
users
differ.  For the bad one 'jimc', refint.example causes immediate

disconnection from the backend, its a broken configuration.


jimc=> select * from pg_proc where proname like 'check%';
proname
|proowner|prolang|proisinh|proistrusted|proiscachable|pronargs|proretset|prorettype|
proargtypes|probyte_pct|properbyte_cpu|propercall_cpu|prooutin_ratio|prosrc
|probin

-----------------+--------+-------+--------+------------+-------------+--------+---------+----------+------------------+-----------+--------------+--------------+--------------+---------------------+------

check_foreign_key|     500|     14|f       |t
|f            |       1|f        |        16|  23 0 0 0 0 0 0
0|        100|             0|             0|
100|select true          |-
check_primary_key|     500|     14|f       |t
|f            |       3|f        |        16|23 23 23 0 0 0 0
0|        100|             0|             0|
100|select true as result|-
(2 rows)


postgres=> select * from pg_proc where proname like 'check%';
proname
|proowner|prolang|proisinh|proistrusted|proiscachable|pronargs|proretset|prorettype|
proargtypes|probyte_pct|properbyte_cpu|propercall_cpu|prooutin_ratio|prosrc|probin


-----------------+--------+-------+--------+------------+-------------+--------+---------+----------+---------------+-----------+--------------+--------------+--------------+------+-----------------------------------------------------

check_foreign_key|     501|     13|f       |t
|t            |       0|f        |         0|0 0 0 0 0 0 0
0|        100|             0|             0|           100|-
|/home/postgres/postgresql-6.5.1/contrib/spi/refint.so
check_primary_key|     501|     13|f       |t
|t            |       0|f        |         0|0 0 0 0 0 0 0
0|        100|             0|             0|           100|-
|/home/postgres/postgresql-6.5.1/contrib/spi/refint.so
(2 rows)


Note the lack of a .so for jimc,

I tried to drop function, which reported 'does not exist' for
both
functions, and and drop rule, which reported 'Rule or view $X
not
found'

Q2: So how do I delete these erroneous entries ?  Theyre
apparently
associated with user 'jimc', possibly the database instance
itself.
Is it necessary to drop the jimc database ?

Q3: (rephrasing Q1) given that these functions are 6.5.1
specific (at
least in terms of where they reside in a standard build),
shouldnt
they be system-wide functions (and not over-ridden by
user-specific
settings ?

Q4: It seems that the pg_* tables are database specific, and not

site-wide.  However, it doesnt jive with 'jimc's inability to do

'\i $HOME/v651/contrib/spi/refint.sql', which results in a lack
of
permission.  If theyre database-specific (ie \connect jimc) why
cant I
delete them.