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@cs.concordia.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@cs.concordia.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@cs.concordia.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.  |