Re: 7.0 FK trigger question

Started by Nonamealmost 26 years ago3 messages

wieck@debis.com

almost 26 years ago

Hi,

due to the limitations in alter table, I generate some SQL to
implement changes to tables. This works along the lines of

1. drop fk triggers on old table
2. rename serial sequences on old table
3. drop indexes on old table
4. rename old table
5. create new table
6. insert into new table select ... from old table
7. drop new sequences/rename old sequences
8. recreate fk triggers

9. drop old table

If you do 9. you can skip 1. because that's done
automatically.

[...]

This looks kind-of hairy to drop and recreate correctly.

I thought an alternative may be to change the oid's in pg_trigger. But I
saw that the oid's of the tables are part of the trigger name. I could
probably recreate the trigger names with different oid's but this looks
like asking for trouble.

Not exactly. The OIDs in the trigger names are just ones that
CREATE CONSTRAINT TRIGGER allocates itself to give any of
them a unique name. They aren't used anywhere else, so don't
care. And BTW: specifying a constraint really invokes these
commands internally.

So what is the best solution? It would be great if there would be some
way to drop foreign key triggers and re-instate them. This would also
help with loading data where there are circular dependencies of foreign
keys, as one could drop a trigger to break the loop, load the data, and
re-instate the triggers.

Ideally you would use correct ALTER TABLE ... ADD CONSTRAINT
commands, which are implemented in 7.0.

pg_dump actually does sort of this "disable RI triggers" for
data only dumps. You might want to setup a simple test
database and take a data only dump to see the mechanism.

So I guess my question really boils down to: is it possible to write a
function that drops a foreign key trigger or re-instates it? This should
really be ALTER TABLE table ALTER COLUMN column (DROP|CREATE)
CONSTRAINT.... or something along those lines.

There's still something missing in ALTER TABLE. DROP
CONSTRAINT is one of them, but since your sequencs with
renaming the old etc. is the safest possibility anyway, it's
not that high priority.

Jan

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

Import Notes

Reply to msg id not found: 38D9BEB2.217E3763@albourne.com

Adriaan Joubert

a.joubert@albourne.com

almost 26 years ago

In reply to: Noname (#1)

Thanks for the reply. For the time begin I've solved this by copying every
table in the database to a backup table without any constraints, recreating
the tables and copying the data back in. I have to be a bit careful with
doing it all in the right order, although I think I can solve this by doing
everything in a transaction as the constraints are only checked at the end of
transaction?

So I guess my question really boils down to: is it possible to write a
function that drops a foreign key trigger or re-instates it? This should
really be ALTER TABLE table ALTER COLUMN column (DROP|CREATE)
CONSTRAINT.... or something along those lines.

There's still something missing in ALTER TABLE. DROP
CONSTRAINT is one of them, but since your sequencs with
renaming the old etc. is the safest possibility anyway, it's
not that high priority.

OK, I'm definitely not being very bright here, but i cannot get my system to
accept the alter column commands. An example on the man pages ,ay help a lot
here! I tried

test=# create table t (i int4);
CREATE
test=# create table t1 (k int4);
CREATE
test=# alter table t1 alter column k add constraint references t(i);
ERROR: parser: parse error at or near "add"
test=# alter table t1 alter column k constraint references t(i);
ERROR: parser: parse error at or near "constraint"
test=# alter table t1 alter k constraint references t(i);
ERROR: parser: parse error at or near "constraint"
test=# alter table t1 alter column k create constraint references t(i);
ERROR: parser: parse error at or near "create"

So what am I doing wrong?

Thanks,

Adriaan

Noname

wieck@debis.com

almost 26 years ago

In reply to: Adriaan Joubert (#2)

Thanks for the reply. For the time begin I've solved this by copying every
table in the database to a backup table without any constraints, recreating
the tables and copying the data back in. I have to be a bit careful with
doing it all in the right order, although I think I can solve this by doing
everything in a transaction as the constraints are only checked at the end of
transaction?

By default, constraints are checked at end of statement.
Constraints can be specified DEFERRABLE, then you can do SET
CONSTRAINTS ... DEFERRED which will delay them until COMMIT.

OK, I'm definitely not being very bright here, but i cannot get my system to
accept the alter column commands. An example on the man pages ,ay help a lot
here! I tried

test=# create table t (i int4);
CREATE
test=# create table t1 (k int4);
CREATE
test=# alter table t1 alter column k add constraint references t(i);
ERROR: parser: parse error at or near "add"
test=# alter table t1 alter column k constraint references t(i);
ERROR: parser: parse error at or near "constraint"
test=# alter table t1 alter k constraint references t(i);
ERROR: parser: parse error at or near "constraint"
test=# alter table t1 alter column k create constraint references t(i);
ERROR: parser: parse error at or near "create"

So what am I doing wrong?

alter table t1 add constraint chk_k foreign key (k) references t (i);

The referenced column(s) (t.i in your case above) must not be
a primary key - any combination is accepted. SQL standard
requires that there is a unique index defined for the
referenced columns so it is guaranteed that FKs reference to
exactly ONE row. Actually Postgres doesn't check or force it,
so you have to take care yourself. For example:

create table t (i integer, j integer);
create unique index t_pk_idx_1 on t (i, j); -- DON'T FORGET THIS!
create table t1 (k integer, l integer,
foreign key (k, l) references t (i, j));

BTW: all existing data is checked at ALTER TABLE time.

And our implementation of FK is based on SQL3. So you can
specify match type FULL (PARTIAL will be in 7.1), and
referential actions (ON DELETE CASCADE etc.) too. It is nice
to define ON UPDATE CASCADE, because if you UPDATE a PK, all
referencing FKs will silently follow then.

Jan