pg_restore - table restoration options - odd behaivors.

PostgreSQL 9.3.1 on amd64-portbld-freebsd9.2, compiled by cc (GCC) 4.2.1 20070831 patched [FreeBSD], 64-bit\q
pg_restore (PostgreSQL) 9.3.1

Summary:

I was exploring table restoration options of pg_restore to deal with issues of users configuring selected tables into a non-workable state and encountered 2 variations that I think are bugs. In both cases I am starting with a common archival file that was created by pg_dump.

e.g. pg_dump -Fc my_db -U <superuser> -f archvice_file

Case 1. pg_restore - n admin -c -v -1 -t tbla -t tblb -U user archive_file;

Recreates the table and data but does not re-create any constraints or triggers that are associated with tbla or tblb.
This may be the intended consequence of -c and -t combination but it is not clear from the documentation.
I note that if I restore the entire schema with only the -n and no -t options it restores all tables correctly.
According to the documentation -t restores the definition and/or data of the named table only. Perhaps I am incorrectly considering the Primary Keys and Foreign Keys part of the table definition ?

Example Case 1.

pg_restore -n admin -c -v -1 -t translator_sys -t translator_sys_mbr -U redcom /var/ace/pg_backups/1-acedb-alt_install.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.3.1
-- Dumped by pg_dump version 9.3.1
-- Started on 2014-01-14 16:59:34 EST

BEGIN;

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

pg_restore: dropping TABLE DATA translator_sys_mbr
SET search_path = admin, pg_catalog;

pg_restore: dropping TABLE DATA translator_sys
pg_restore: dropping TABLE translator_sys_mbr
DROP TABLE admin.translator_sys_mbr;
pg_restore: dropping TABLE translator_sys
DROP TABLE admin.translator_sys;
pg_restore: creating TABLE translator_sys
SET search_path = admin, pg_catalog;

SET default_tablespace = '';

SET default_with_oids = false;

--
-- TOC entry 352 (class 1259 OID 350256)
-- Dependencies: 200 6
-- Name: translator_sys; Type: TABLE; Schema: admin; Owner: redcom; Tablespace:
-- Data Pos: 0
--

CREATE TABLE translator_sys (
translator_id integer DEFAULT nextval('sys.translator_id_seq'::regclass) NOT NULL,
pattern_key character varying,
name character varying,
comments character varying,
owner_user_id integer,
mgcp_digit_map character varying
);

ALTER TABLE admin.translator_sys OWNER TO redcom;

pg_restore: creating TABLE translator_sys_mbr
--
-- TOC entry 353 (class 1259 OID 350263)
-- Dependencies: 1057 1057 6
-- Name: translator_sys_mbr; Type: TABLE; Schema: admin; Owner: redcom; Tablespace:
-- Data Pos: 0
--

CREATE TABLE translator_sys_mbr (
name character varying,
tu_id integer DEFAULT 0,
translator_id integer NOT NULL,
tid_seq integer NOT NULL,
ws_grp_seq integer DEFAULT 0,
obj_seq integer DEFAULT 1,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer DEFAULT 0 NOT NULL,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
callback character varying,
early_media character varying,
web_app_info character varying,
cause__mgcp integer,
cause__rfc4411 integer,
cause__sdp_warning integer,
cause__sip integer,
cause__comment character varying,
orig_identity_name character varying,
orig_identity_number character varying,
orig_identity_host character varying,
orig_identity_user character varying,
term_identity_name character varying,
term_identity_number character varying,
term_identity_host character varying,
term_identity_user character varying
);

ALTER TABLE admin.translator_sys_mbr OWNER TO redcom;

--
-- TOC entry 4224 (class 0 OID 350256)
-- Dependencies: 352
-- Data for Name: translator_sys; Type: TABLE DATA; Schema: admin; Owner: redcom
-- Data Pos: 841023
--

pg_restore: processing data for table "translator_sys"
COPY translator_sys (translator_id, pattern_key, name, comments, owner_user_id, mgcp_digit_map) FROM stdin;
1 $tran:user:address_ Standard Translator Normal starting translator \N \N
\.

--
-- TOC entry 4225 (class 0 OID 350263)
-- Dependencies: 353
-- Data for Name: translator_sys_mbr; Type: TABLE DATA; Schema: admin; Owner: redcom
-- Data Pos: 841116
--

pg_restore: processing data for table "translator_sys_mbr"
COPY translator_sys_mbr (name, tu_id, translator_id, tid_seq, ws_grp_seq, obj_seq, entry_type, pattern_key, pattern, pattern_match, screen_class, term_mode, port_id, tag_id, cause__q850, cause__redcom, new_translator_id, new_screen_class, duration, address, macro_type, owner_usr_id, owner_port_id, trans_result, sys_note, continue_translating, call_priority__level, user_message, label, label_reference, row_type, callback, early_media, web_app_info, cause__mgcp, cause__rfc4411, cause__sdp_warning, cause__sip, cause__comment, orig_identity_name, orig_identity_number, orig_identity_host, orig_identity_user, term_identity_name, term_identity_number, term_identity_host, term_identity_user) FROM stdin;
lines 0 1 1 0 1 7 \N \N 0 0 \N \N \N \N\N \N \N \N \N 1 \N \N 0 \N \N \N \N \N \Nreal \N \N \N \N \N \N \N \N \N \N \N \N \N \N\N \N
\.

pg_restore: setting owner and privileges for TABLE translator_sys
pg_restore: setting owner and privileges for TABLE translator_sys_mbr
pg_restore: setting owner and privileges for TABLE DATA translator_sys
pg_restore: setting owner and privileges for TABLE DATA translator_sys_mbr
COMMIT;

-- Completed on 2014-01-15 08:34:03 EST

--
-- PostgreSQL database dump complete
--

Case 2. pg_restore -c -a -v -1 -n admin -t tbla -t tblb -U superuser archive_file

In this -a, data only, case I was hopeful that the combined -c option would either DELETE or TRUNCATE the data prior to the COPY command.
There is an indication from pg_restore that it is attempting to do it but no commands are added to the output strean to cause that.
I wonder what the expected behavior here is ? The attempt ultimately fails because the COPY command fails because the existing
data and data to be inserted would break Primary Key constraints. ( Because the existing data is not dropped ? -c ? )

Example Case 2.

pg_restore -n admin -c -a -v -1 -t translator_sys -t translator_sys_mbr -U redcom /var/ace/pg_backups/1-acedb-alt_install.sql
--
-- PostgreSQL database dump
--

-- Dumped from database version 9.3.1
-- Dumped by pg_dump version 9.3.1
-- Started on 2014-01-14 16:59:34 EST

BEGIN;

SET statement_timeout = 0;
SET lock_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SET check_function_bodies = false;
SET client_min_messages = warning;

pg_restore: dropping TABLE DATA translator_sys_mbr
SET search_path = admin, pg_catalog;

pg_restore: dropping TABLE DATA translator_sys
SET search_path = admin, pg_catalog;

--
-- TOC entry 4224 (class 0 OID 350256)
-- Dependencies: 352
-- Data for Name: translator_sys; Type: TABLE DATA; Schema: admin; Owner: redcom
-- Data Pos: 841023
--

pg_restore: processing data for table "translator_sys"
COPY translator_sys (translator_id, pattern_key, name, comments, owner_user_id, mgcp_digit_map) FROM stdin;
1 $tran:user:address_ Standard Translator Normal starting translator \N \N
\.

--
-- TOC entry 4225 (class 0 OID 350263)
-- Dependencies: 353
-- Data for Name: translator_sys_mbr; Type: TABLE DATA; Schema: admin; Owner: redcom
-- Data Pos: 841116
--

pg_restore: processing data for table "translator_sys_mbr"
COPY translator_sys_mbr (name, tu_id, translator_id, tid_seq, ws_grp_seq, obj_seq, entry_type, pattern_key, pattern, pattern_match, screen_class, term_mode, port_id, tag_id, cause__q850, cause__redcom, new_translator_id, new_screen_class, duration, address, macro_type, owner_usr_id, owner_port_id, trans_result, sys_note, continue_translating, call_priority__level, user_message, label, label_reference, row_type, callback, early_media, web_app_info, cause__mgcp, cause__rfc4411, cause__sdp_warning, cause__sip, cause__comment, orig_identity_name, orig_identity_number, orig_identity_host, orig_identity_user, term_identity_name, term_identity_number, term_identity_host, term_identity_user) FROM stdin;
lines 0 1 1 0 1 7 \N \N 0 0 \N \N \N \N\N \N \N \N \N 1 \N \N 0 \N \N \N \N \N \Nreal \N \N \N \N \N \N \N \N \N \N \N \N \N \N\N \N
\.

pg_restore: setting owner and privileges for TABLE DATA translator_sys
pg_restore: setting owner and privileges for TABLE DATA translator_sys_mbr
COMMIT;

-- Completed on 2014-01-15 08:46:58 EST

--
-- PostgreSQL database dump complete
--

For reference purposes here is the definitions of my tbla and tblb.

CREATE TABLE admin.translator_sys
(
translator_id integer NOT NULL DEFAULT nextval('sys.translator_id_seq'::regclass),
pattern_key character varying,
name character varying,
comments character varying,
owner_user_id integer,
mgcp_digit_map character varying,
CONSTRAINT translator_sys_pkey PRIMARY KEY (translator_id),
CONSTRAINT translator_sys_owner_user_id_fkey FOREIGN KEY (owner_user_id)
REFERENCES admin."user" (id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY IMMEDIATE
)
WITH (
OIDS=FALSE
);
ALTER TABLE admin.translator_sys
OWNER TO redcom;
GRANT ALL ON TABLE admin.translator_sys TO ace_db_client;
GRANT ALL ON TABLE admin.translator_sys TO ace_web_portal;
GRANT ALL ON TABLE admin.translator_sys TO redcom;
COMMENT ON TABLE admin.translator_sys
IS 'Define the global aspects of a tranlsation set.';

-- Trigger: translator_sys_audit on admin.translator_sys

-- DROP TRIGGER translator_sys_audit ON admin.translator_sys;

CREATE TRIGGER translator_sys_audit
AFTER INSERT OR UPDATE OF pattern_key OR DELETE
ON admin.translator_sys
FOR EACH ROW
EXECUTE PROCEDURE sys.resource_post_change();

CREATE TABLE admin.translator_sys_mbr
(
name character varying,
tu_id integer DEFAULT 0,
translator_id integer NOT NULL,
tid_seq integer NOT NULL,
ws_grp_seq integer DEFAULT 0,
obj_seq integer DEFAULT 1,
entry_type integer DEFAULT 0,
pattern_key character varying,
pattern character varying,
pattern_match integer DEFAULT 0,
screen_class integer DEFAULT 0,
term_mode character varying,
port_id integer,
tag_id integer,
cause__q850 integer,
cause__redcom integer,
new_translator_id integer,
new_screen_class integer,
duration integer,
address character varying,
macro_type integer,
owner_usr_id integer,
owner_port_id integer,
trans_result integer NOT NULL DEFAULT 0,
sys_note character varying,
continue_translating character varying,
call_priority__level integer,
user_message character varying,
label character varying,
label_reference character varying,
row_type sys.rsrc_type_enum DEFAULT 'real'::sys.rsrc_type_enum,
callback character varying,
early_media character varying,
web_app_info character varying,
cause__mgcp integer,
cause__rfc4411 integer,
cause__sdp_warning integer,
cause__sip integer,
cause__comment character varying,
orig_identity_name character varying,
orig_identity_number character varying,
orig_identity_host character varying,
orig_identity_user character varying,
term_identity_name character varying,
term_identity_number character varying,
term_identity_host character varying,
term_identity_user character varying,
CONSTRAINT translator_sys_mbr_pkey PRIMARY KEY (translator_id, tid_seq),
CONSTRAINT tidfk FOREIGN KEY (translator_id)
REFERENCES admin.translator_sys (translator_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE DEFERRABLE INITIALLY DEFERRED,
CONSTRAINT translator_sys_call_priority_fkey FOREIGN KEY (call_priority__level)
REFERENCES admin.call_priority_level_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_entry_type_fkey FOREIGN KEY (entry_type)
REFERENCES admin.translator_entry_type_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__mgcp_fkey FOREIGN KEY (cause__mgcp)
REFERENCES admin.cause_mgcp (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__q850_fkey FOREIGN KEY (cause__q850)
REFERENCES admin.cause_q850 (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__redcom_fkey FOREIGN KEY (cause__redcom)
REFERENCES admin.cause_redcom (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__rfc4411_fkey FOREIGN KEY (cause__rfc4411)
REFERENCES admin.cause_rfc4411 (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__sdp_warning_fkey FOREIGN KEY (cause__sdp_warning)
REFERENCES admin.cause_sdp (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_mbr_cause__sip_fkey FOREIGN KEY (cause__sip)
REFERENCES admin.cause_sip (code) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_owner_port_id_fkey FOREIGN KEY (owner_port_id)
REFERENCES admin.port (port_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_pattern_match_fkey FOREIGN KEY (pattern_match)
REFERENCES admin.translator_pattern_match_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT translator_sys_trans_result_fkey FOREIGN KEY (trans_result)
REFERENCES admin.translator_result_enum (value) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
OIDS=FALSE
);
ALTER TABLE admin.translator_sys_mbr
OWNER TO redcom;
GRANT ALL ON TABLE admin.translator_sys_mbr TO ace_db_client;
GRANT ALL ON TABLE admin.translator_sys_mbr TO ace_web_portal;
GRANT ALL ON TABLE admin.translator_sys_mbr TO redcom;
COMMENT ON TABLE admin.translator_sys_mbr
IS 'This table defines the translation for a given translator id. Should match definition of translator_user_mbr.';

-- Trigger: translator_sys_mbr_audit on admin.translator_sys_mbr

-- DROP TRIGGER translator_sys_mbr_audit ON admin.translator_sys_mbr;

CREATE TRIGGER translator_sys_mbr_audit
AFTER INSERT OR UPDATE OR DELETE
ON admin.translator_sys_mbr
FOR EACH ROW
EXECUTE PROCEDURE sys.resource_post_change();

I suspect a very simple reference case of two tables with fewer columns in the same schema each with a PK definition, and a FK definition from one table would further confirm both case observations.
I would think that Case 2 would be a desirable selective restoral case that if not supported currently ought to be a wish list item for pg_restore in the future.

Respectfully,

Dave Day