Обсуждение: Referential Integrity functions for 2 different users

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.

-- RI inherits right ?
-- no ! A has primary key, A1 doesnt
-- but A1 should have all columns of A
-- and I assumed properties on them

-- apparently 2 stage drop is needed
--  drop derived 1st, then base
drop table A1;
drop table A;

create table A ( -- common elements,
 name  varchar(20) not null,  -- key field
 primary key ( name )
);

create table A1 (
 -- name,
 rank  text
 -- primary key ( name ) -- err if uncommented
) inherits ( A );

insert into A values ('yo');
insert into A values ('hey');
-- insert into a values ('hey'); -- disallowed

select * from A;

insert into A1 values ('yo');
insert into A1 values ('hey');
insert into A1 values ('hey');  -- allowed

select * from A1;

-- seems last should be disallowed