Re: foreign key constraint not working when index tablespace

Hi Tom,

Ok. You are right but I am not crazy (yet).

If I create the tables and indexes as user postgres it works like a charm.

But if I create the tables as a mortal user or create them as postgres but in
the schema of user joelk and grant all to user joelk, I can insert data
without the foreign key constraint being respected. Now if I drop the foreign
key constraint and recreate it with a schema prefix in the references section,
the constarint works fine.

alter table joelk.jk_users add FOREIGN KEY (department) REFERENCES
joelk.jk_map(code);

fis=> INSERT INTO joelk.jk_users VALUES( 'fancott', 'Fancott, T.',
'fancott(at)cs(dot)concordia(dot)ca', '08', 'Professor' );
ERROR: insert or update on table "jk_users" violates foreign key constraint
"jk_users_department_fkey"
DETAIL: Key (department)=(08) is not present in table "jk_map".

fis=> \d jk_users Table "joelk.jk_users"
Column | Type | Modifiers
------------+------------------------+-----------
username | character varying(8) |
name | character varying(64) | not null
email | character varying(128) |
department | character varying(2) | not null
title | character varying(64) | not null
Indexes:
"jk_users_pkey" PRIMARY KEY, btree (name, department, title)
Foreign-key constraints:
"jk_users_department_fkey" FOREIGN KEY (department) REFERENCES jk_map(code)

Everything also works fine if a mortal user creates the tables and indexes in
the appropriate tablespaces and uses the schema.table(column_name) in the
references section of the foreign key constraint.

Strange.

Not sure if this is relevant - in this database I dropped the public schema
and I had to grant all to the group public so that users could create indexes
in the fis_index tablespace.

fis=> \db+
List of tablespaces
Name | Owner | Location | Access
privileges
------------+----------+-----------------------------------+-----------------------------------
fis | postgres | /local/data/pgsql-8.0/fis |
fis_index | postgres | /local/data/pgsql-8.0/fis_index |
{postgres=C/postgres,=C/postgres}

Thanks for looking into it.

Regards

joel

Tom Lane wrote:
> Joel Krajden <joelk(at)cs(dot)concordia(dot)ca> writes:
>
>>If the indexes are created in fis_index, the foreign key constraints in the
>>user table are ignored on insert and update.
>
>
> Works for me...
>
> $ mkdir /tmp/fis
> $ mkdir /tmp/fis_index
> $ psql regression
> ...
> regression=# create tablespace fis location '/tmp/fis';
> CREATE TABLESPACE
> regression=# create tablespace fis_index location '/tmp/fis_index';
> CREATE TABLESPACE
> regression=# \i joel.sql
> psql:joel.sql:11: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "dept_map_pkey" for table "dept_map"
> CREATE TABLE
> psql:joel.sql:23: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "card_category_map_pkey" for table "card_category_map"
> CREATE TABLE
> psql:joel.sql:38: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "fis_title_map_pkey" for table "fis_title_map"
> CREATE TABLE
> psql:joel.sql:57: NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "users_pkey" for table "users"
> CREATE TABLE
> regression=# insert into users values('username','name','email','dp','title');
> ERROR: insert or update on table "users" violates foreign key constraint "users_department_fkey"
> DETAIL: Key (department)=(dp) is not present in table "dept_map".
> regression=# insert into dept_map values('dp','desc');
> INSERT 0 1
> regression=# insert into users values('username','name','email','dp','title');
> ERROR: insert or update on table "users" violates foreign key constraint "users_title_fkey"
> DETAIL: Key (title)=(title) is not present in table "fis_title_map".
> regression=# insert into fis_title_map values('title','cardcat');
> ERROR: insert or update on table "fis_title_map" violates foreign key constraint "fis_title_map_card_category_fkey"
> DETAIL: Key (card_category)=(cardcat) is not present in table "card_category_map".
> regression=# insert into card_category_map values('cardcat','desc');
> INSERT 0 1
> regression=# insert into fis_title_map values('title','cardcat');
> INSERT 0 1
> regression=# insert into users values('username','name','email','dp','title');
> INSERT 0 1
> regression=#
>
> regards, tom lane

--
| Joel Krajden | Rm: LB-915, Tel: 514 848-2424 3052 |
| | Fax: 514 848-2830 |
| Senior Systems Analyst | Email: joelk(at)cs(dot)concordia(dot)ca |
| Engineering & Computer Sc.| http://www.cs.concordia.ca/~staffcs/joelk |
| Concordia University | Remember it's a circus and the clowns |
| Montreal, Canada | are supposed to make you laugh, not cry. |