Issue #10794 has been reported by Jonathon Anderson.
----------------------------------------
Feature #10794: foreign and unique key constraints in storeconfigs
https://projects.puppetlabs.com/issues/10794
Author: Jonathon Anderson
Status: Unreviewed
Priority: Normal
Assignee:
Category: stored configuration
Target version:
Affected Puppet version:
Keywords:
Branch:
We use PostgreSQL for our storeconfigs db. For a variety of reasons (some not
fully understood) our db has gotten into an invalid state a number of times,
requiring manual intervention. We've minimized this in our environment by
adding foreign and unique key constraints to the database.
Some of these duplicate the existing *indexes*, but we've just duplicated them
for the time being. That said, we haven't had any problems with these
constraints in place, and it has appreciably helped our management of the
storeconfigs db.
I recommend that these (and/or similar) constraints be included in the official
storeconfigs schema for databases that support them.
# foreign key constraints
ALTER TABLE fact_values ADD CONSTRAINT fact_names_fk FOREIGN KEY (
fact_name_id ) REFERENCES fact_names ( id ) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE hosts ADD CONSTRAINT source_files_fk FOREIGN KEY (
source_file_id ) REFERENCES source_files ( id ) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE param_values ADD CONSTRAINT param_names_fk FOREIGN KEY (
param_name_id ) REFERENCES param_names ( id ) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE param_values ADD CONSTRAINT resources_fk FOREIGN KEY (
resource_id ) REFERENCES resources ( id ) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE resource_tags ADD CONSTRAINT resources_fk FOREIGN KEY (
resource_id ) REFERENCES resources ( id ) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE resource_tags ADD CONSTRAINT puppet_tags_fk FOREIGN KEY (
puppet_tag_id ) REFERENCES puppet_tags ( id ) ON DELETE CASCADE ON UPDATE
CASCADE;
ALTER TABLE resources ADD CONSTRAINT hosts_fk FOREIGN KEY ( host_id )
REFERENCES hosts ( id ) ON DELETE CASCADE ON UPDATE CASCADE;
ALTER TABLE resources ADD CONSTRAINT source_files_fk FOREIGN KEY (
source_file_id ) REFERENCES source_files ( id ) ON DELETE CASCADE ON UPDATE
CASCADE;
# unique constraints
ALTER TABLE fact_names ADD CONSTRAINT unique_fact_names_on_name UNIQUE
(name);
ALTER TABLE hosts ADD CONSTRAINT unique_hosts_on_name UNIQUE (name);
ALTER TABLE param_names ADD CONSTRAINT unique_param_names_on_name UNIQUE
(name);
ALTER TABLE puppet_tags ADD CONSTRAINT unique_puppet_tags_on_name UNIQUE
(name);
ALTER TABLE resources ADD CONSTRAINT unique_resources_on_title_and_restype
UNIQUE (title, restype);
ALTER TABLE source_files ADD CONSTRAINT unique_source_files_on_filename
UNIQUE (filename);
ALTER TABLE source_files ADD CONSTRAINT unique_source_files_on_path UNIQUE
(path);
--
You have received this notification because you have either subscribed to it,
or are involved in it.
To change your notification preferences, please click here:
http://projects.puppetlabs.com/my/account
--
You received this message because you are subscribed to the Google Groups
"Puppet Bugs" group.
To post to this group, send email to [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/puppet-bugs?hl=en.
