How are foreign key constraints built? In loading my database into
PostgreSQL 8.0, on the command:
ALTER TABLE ONLY TABLEA
ADD CONSTRAINT "$1" FOREIGN KEY (mkey) REFERENCES tableb(mkey) ON DELETE
CASCADE;
I ended up with the following in pg_tmp as it is adding the constraint:
-rw------- 1 postgres admin 1073741824 23 Jan 06:09 pgsql_tmp2559.25
-rw------- 1 postgres admin 1073741824 23 Jan 06:24 pgsql_tmp2559.26
-rw------- 1 postgres admin 1073741824 23 Jan 06:39 pgsql_tmp2559.27
-rw------- 1 postgres admin 636526592 23 Jan 06:48 pgsql_tmp2559.28
-rw------- 1 postgres admin 1073741824 23 Jan 11:51 pgsql_tmp2559.29
-rw------- 1 postgres admin 1073741824 23 Jan 11:34 pgsql_tmp2559.30
-rw------- 1 postgres admin 1073741824 23 Jan 11:36 pgsql_tmp2559.31
-rw------- 1 postgres admin 1073741824 23 Jan 11:37 pgsql_tmp2559.32
-rw------- 1 postgres admin 1073741824 23 Jan 11:38 pgsql_tmp2559.33
-rw------- 1 postgres admin 1073741824 23 Jan 11:39 pgsql_tmp2559.34
-rw------- 1 postgres admin 1073741824 23 Jan 11:41 pgsql_tmp2559.35
-rw------- 1 postgres admin 1073741824 23 Jan 11:42 pgsql_tmp2559.36
-rw------- 1 postgres admin 1073741824 23 Jan 11:43 pgsql_tmp2559.37
-rw------- 1 postgres admin 1073741824 23 Jan 11:45 pgsql_tmp2559.38
-rw------- 1 postgres admin 1073741824 23 Jan 11:46 pgsql_tmp2559.39
-rw------- 1 postgres admin 1073741824 23 Jan 11:47 pgsql_tmp2559.40
-rw------- 1 postgres admin 1073741824 23 Jan 11:49 pgsql_tmp2559.41
-rw------- 1 postgres admin 1073741824 23 Jan 11:50 pgsql_tmp2559.42
-rw------- 1 postgres admin 603136000 23 Jan 11:51 pgsql_tmp2559.43
I believe files 25-28 are about the size of one of the indexes, but 29-43 is
about 35% larger than the tmp files when building the other index (the other
index is about 10GB).
There's no problem here, I'd just like to understand what it is doing. I
expected adding the foreign key constraint would just use the existing
indexes to verify the database is currently consistent. Is this just
working space to more efficiently build the initial constraint, or does it
actually write this to the database?
Wes
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
