Referential Integrity functions for 2 different users
От | Jim Cromie |
---|---|
Тема | Referential Integrity functions for 2 different users |
Дата | |
Msg-id | 37F12EE9.E3E51252@bwn.net обсуждение исходный текст |
Список | 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.
В списке pgsql-general по дате отправления: