Thread: foreign key constraint not working when index tablespace is not default.
I have two tablespaces: fis (default) and fis_index. The users are required to
create the tables in fis and the indexes if fis_index.
If the indexes are created in fis_index, the foreign key constraints in the
user table are ignored on insert and update.
The constraints work properly if the indexes are created in the default
tablespace.
Joel
----------
fis=> SELECT version();
version
------------------------------------------------------------------------
PostgreSQL 8.0.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.2
(1 row)
-- Department Mappings
-- -------------------
-- Mapping between the department code and the full department name.
CREATE TABLE dept_map (
code VARCHAR(2),
description VARCHAR(64),
PRIMARY KEY( code ) USING INDEX TABLESPACE fis_index
-- Card Access category mappings.
-- ------------------------------
-- Different access level to the card access application.
CREATE TABLE card_category_map (
code VARCHAR(16),
description VARCHAR(64),
PRIMARY KEY( code ) USING INDEX TABLESPACE fis_index
);
-- Fis Category Mappings
-- ---------------------
-- Mapping between categories in FIS and access levels in card access.
CREATE TABLE fis_title_map (
fis_title VARCHAR(64),
card_category VARCHAR(64),
PRIMARY KEY( fis_title ) USING INDEX TABLESPACE fis_index,
FOREIGN KEY( card_category ) REFERENCES card_category_map( code )
);
-- Users
-- --------------------
-- Table of users with valid accounts to the application.
--
CREATE TABLE users (
username VARCHAR(8),
name VARCHAR(64),
email VARCHAR(128),
department VARCHAR(2),
title VARCHAR(64),
PRIMARY KEY( name, department, title ) USING INDEX TABLESPACE fis_index,
FOREIGN KEY( department ) REFERENCES dept_map( code ),
FOREIGN KEY( title ) REFERENCES fis_title_map( fis_title )
);
--
| 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. |
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
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. |
Joel Krajden <joelk@cs.concordia.ca> writes:
> 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.
This is even harder to believe than the first report. Could we see a
complete, self-contained test case? A SQL script that demonstrates the
problem from a standing start in an empty database is what I have in mind.
(What I suspect is that you have multiple similarly-named tables in
different schemas and are getting confused by that...)
regards, tom lane
I don't think I am crazy and the user who showed me the problem exists - but damn it neither he or I cannot duplicate the problem. The only thing that changed is each of us has logged out of our session and logged back in. The only thing that could have happened is that sometime in the past the schema search path changed after the user logged in. There was no other table with a duplicate name in another schema. Thanks for doing the sanity check. Joel Tom Lane wrote: > Joel Krajden <joelk@cs.concordia.ca> writes: > >>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. > > > This is even harder to believe than the first report. Could we see a > complete, self-contained test case? A SQL script that demonstrates the > problem from a standing start in an empty database is what I have in mind. > > (What I suspect is that you have multiple similarly-named tables in > different schemas and are getting confused by that...) > > 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. |