Обсуждение: A very puzzling backup/restore problem
I have a very confusing isse. I am trying to backup and restre a signle table . first I dump the table. Script started on 2019-10-24 06:29:12-0400 ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump Then I connect to the db, and verify that things are as expected. ]0;stan@smokey: ~stan@smokey:~$ psql psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) Type "help" for help. [1m[local] stan@stan=[0m> \dt List of relations Schema | Name | Type | Owner --------+--------------------------+-------+------- public | biz_constants | table | stan public | bom_item | table | stan public | costing_unit | table | stan public | customer | table | stan public | earthquake | table | stan public | employee | table | stan public | expense_report_instance | table | stan public | gl_code | table | stan public | mfg | table | stan public | mfg_part | table | stan public | mfg_vendor_relationship | table | stan public | permitted_work | table | stan public | phone_number_test | table | stan public | project | table | stan public | project_budget_component | table | stan public | project_cost_category | table | stan public | rate | table | stan public | salary | table | stan public | task_instance | table | stan public | vendor | table | stan public | work_type | table | stan (21 rows) [1m[local] stan@stan=[0m> \d task_instance [?1049h[22;0;0t[?1h= Table "public.task_instance" Column | Type | Collation | Nullable | Default ---------------+--------------------------+-----------+----------+-------------- --------------------------------- task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) project_key | integer | | not null | employee_key | integer | | not null | work_type_key | integer | | not null | hours | numeric(5,2) | | not null | work_start | timestamp with time zone | | not null | work_end | timestamp with time zone | | not null | modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP lock | boolean | | | true descrip | character varying | | | Indexes: "task_instance_pkey" PRIMARY KEY, btree (task_instance) "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) Foreign-key constraints: "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work [?1049h[22;0;0t[?1h= Table "public.permitted_work" Column | Type | Collation | Nullable | Default ---------------+--------------------------+-----------+----------+-------------- ----- employee_key | integer | | not null | work_type_key | integer | | not null | permit | boolean | | not null | false modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP Indexes: "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) Foreign-key constraints: "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETE RESTRICT "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETE RESTRICT Then I delete the rows from the table. [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; DELETE 31 Then I try to restore from the dump file. [1m[local] stan@stan=[0m> \i task_instance.dump SET SET SET SET SET set_config ------------ (1 row) SET SET SET SET psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist LINE 3: permitted_work ^ QUERY: SELECT permit FROM permitted_work WHERE NEW.employee_key = permitted_work.employee_key AND NEW.work_type_key = permitted_work.work_type_key CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." After that error, U cannot see any objects in the table [1m[local] stan@stan=[0m> \dt Did not find any relations. [1m[local] stan@stan=[0m> \q ]0;stan@smokey: ~stan@smokey:~$ exit Script done on 2019-10-24 06:30:48-0400 quiting psql and reconecting shows that the obkects ARE there, with the taks)instance table empty. What am I doing wrong? -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On Thu, Oct 24, 2019 at 07:57:14AM -0500, Ron wrote: > Why did you do "\i task_instance.dump" instead of "pg_restore"? Ah, that may be the root of my problem. I had in the back of my mind that the result of a pg_dump was a free standing SQL script, but that my be exactly what is going on. I will try to use pg_restore, and make certain that resolves my issue. In the meantime, I found that if I deleted all the SET lines from the dump I could restore with psql. But that is probably not the right way to do this. > > On 10/24/19 5:52 AM, stan wrote: > > > > I have a very confusing isse. I am trying to backup and restre a signle > > table . > > > > first I dump the table. > > > > > > Script started on 2019-10-24 06:29:12-0400 > > ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump > > > > Then I connect to the db, and verify that things are as expected. > > > > ]0;stan@smokey: ~stan@smokey:~$ psql > > psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) > > Type "help" for help. > > > > [1m[local] stan@stan=[0m> \dt > > List of relations > > Schema | Name | Type | Owner > > --------+--------------------------+-------+------- > > public | biz_constants | table | stan > > public | bom_item | table | stan > > public | costing_unit | table | stan > > public | customer | table | stan > > public | earthquake | table | stan > > public | employee | table | stan > > public | expense_report_instance | table | stan > > public | gl_code | table | stan > > public | mfg | table | stan > > public | mfg_part | table | stan > > public | mfg_vendor_relationship | table | stan > > public | permitted_work | table | stan > > public | phone_number_test | table | stan > > public | project | table | stan > > public | project_budget_component | table | stan > > public | project_cost_category | table | stan > > public | rate | table | stan > > public | salary | table | stan > > public | task_instance | table | stan > > public | vendor | table | stan > > public | work_type | table | stan > > (21 rows) > > > > [1m[local] stan@stan=[0m> \d task_instance > > [?1049h[22;0;0t[?1h= Table "public.task_instance" > > Column | Type | Collation | Nullable | Default > > ---------------+--------------------------+-----------+----------+-------------- --------------------------------- > > task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) > > project_key | integer | | not null | > > employee_key | integer | | not null | > > work_type_key | integer | | not null | > > hours | numeric(5,2) | | not null | > > work_start | timestamp with time zone | | not null | > > work_end | timestamp with time zone | | not null | > > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > > lock | boolean | | | true > > descrip | character varying | | | > > Indexes: > > "task_instance_pkey" PRIMARY KEY, btree (task_instance) > > "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) > > Foreign-key constraints: > > "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work > > [?1049h[22;0;0t[?1h= Table "public.permitted_work" > > Column | Type | Collation | Nullable | Default > > ---------------+--------------------------+-----------+----------+-------------- ----- > > employee_key | integer | | not null | > > work_type_key | integer | | not null | > > permit | boolean | | not null | false > > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > > Indexes: > > "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) > > Foreign-key constraints: > > "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETE RESTRICT > > "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETERESTRICT > > > > Then I delete the rows from the table. > > > > [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; > > DELETE 31 > > > > Then I try to restore from the dump file. > > > > [1m[local] stan@stan=[0m> \i task_instance.dump > > SET > > SET > > SET > > SET > > SET > > set_config > > ------------ > > (1 row) > > > > SET > > SET > > SET > > SET > > psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist > > LINE 3: permitted_work > > ^ > > QUERY: SELECT > > permit FROM > > permitted_work > > WHERE > > NEW.employee_key = permitted_work.employee_key > > AND > > NEW.work_type_key = permitted_work.work_type_key > > CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement > > COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." > > > > After that error, U cannot see any objects in the table > > > > [1m[local] stan@stan=[0m> \dt > > Did not find any relations. > > [1m[local] stan@stan=[0m> \q > > ]0;stan@smokey: ~stan@smokey:~$ exit > > > > Script done on 2019-10-24 06:30:48-0400 > > > > quiting psql and reconecting shows that the obkects ARE there, with the > > taks)instance table empty. > > > > What am I doing wrong? > > -- > Angular momentum makes the world go 'round. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On 10/24/19 3:52 AM, stan wrote: > > > I have a very confusing isse. I am trying to backup and restre a signle > table . > > first I dump the table. Actually you are just dumping the table data. More below. > > > Script started on 2019-10-24 06:29:12-0400 > ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump > > Then I connect to the db, and verify that things are as expected. > > ]0;stan@smokey: ~stan@smokey:~$ psql > psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) > Type "help" for help. > > [1m[local] stan@stan=[0m> \dt > List of relations > Schema | Name | Type | Owner > --------+--------------------------+-------+------- > public | biz_constants | table | stan > public | bom_item | table | stan > public | costing_unit | table | stan > public | customer | table | stan > public | earthquake | table | stan > public | employee | table | stan > public | expense_report_instance | table | stan > public | gl_code | table | stan > public | mfg | table | stan > public | mfg_part | table | stan > public | mfg_vendor_relationship | table | stan > public | permitted_work | table | stan > public | phone_number_test | table | stan > public | project | table | stan > public | project_budget_component | table | stan > public | project_cost_category | table | stan > public | rate | table | stan > public | salary | table | stan > public | task_instance | table | stan > public | vendor | table | stan > public | work_type | table | stan > (21 rows) > > [1m[local] stan@stan=[0m> \d task_instance > [?1049h[22;0;0t[?1h= Table "public.task_instance" > Column | Type | Collation | Nullable | Default > ---------------+--------------------------+-----------+----------+-------------- --------------------------------- > task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) > project_key | integer | | not null | > employee_key | integer | | not null | > work_type_key | integer | | not null | > hours | numeric(5,2) | | not null | > work_start | timestamp with time zone | | not null | > work_end | timestamp with time zone | | not null | > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > lock | boolean | | | true > descrip | character varying | | | > Indexes: > "task_instance_pkey" PRIMARY KEY, btree (task_instance) > "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) > Foreign-key constraints: > "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work > [?1049h[22;0;0t[?1h= Table "public.permitted_work" > Column | Type | Collation | Nullable | Default > ---------------+--------------------------+-----------+----------+-------------- ----- > employee_key | integer | | not null | > work_type_key | integer | | not null | > permit | boolean | | not null | false > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > Indexes: > "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) > Foreign-key constraints: > "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETE RESTRICT > "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETE RESTRICT > > Then I delete the rows from the table. > > [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; > DELETE 31 > > Then I try to restore from the dump file. > > [1m[local] stan@stan=[0m> \i task_instance.dump > SET > SET > SET > SET > SET > set_config > ------------ > > (1 row) > > SET > SET > SET > SET > psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist > LINE 3: permitted_work > ^ In your \d task_instance above I do not see a trigger that calls public.check_permission(). Does one exist or was it cut off the output you pasted? Also look in the dump file. Given that you are using 11.5 I'm going to assume it is resetting the search_path and that the unqualified schema name of permitted_work below is your issue. > QUERY: SELECT > permit FROM > permitted_work > WHERE > NEW.employee_key = permitted_work.employee_key > AND > NEW.work_type_key = permitted_work.work_type_key > CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement > COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." > > After that error, U cannot see any objects in the table > > [1m[local] stan@stan=[0m> \dt > Did not find any relations. > [1m[local] stan@stan=[0m> \q > ]0;stan@smokey: ~stan@smokey:~$ exit > > Script done on 2019-10-24 06:30:48-0400 > > quiting psql and reconecting shows that the obkects ARE there, with the > taks)instance table empty. > > What am I doing wrong? > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: > On 10/24/19 3:52 AM, stan wrote: > > > > > > I have a very confusing isse. I am trying to backup and restre a signle > > table . > > > > first I dump the table. > > Actually you are just dumping the table data. > > More below. > > > > > > Script started on 2019-10-24 06:29:12-0400 > > ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump > > > > Then I connect to the db, and verify that things are as expected. > > > > ]0;stan@smokey: ~stan@smokey:~$ psql > > psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) > > Type "help" for help. > > > > [1m[local] stan@stan=[0m> \dt > > List of relations > > Schema | Name | Type | Owner > > --------+--------------------------+-------+------- > > public | biz_constants | table | stan > > public | bom_item | table | stan > > public | costing_unit | table | stan > > public | customer | table | stan > > public | earthquake | table | stan > > public | employee | table | stan > > public | expense_report_instance | table | stan > > public | gl_code | table | stan > > public | mfg | table | stan > > public | mfg_part | table | stan > > public | mfg_vendor_relationship | table | stan > > public | permitted_work | table | stan > > public | phone_number_test | table | stan > > public | project | table | stan > > public | project_budget_component | table | stan > > public | project_cost_category | table | stan > > public | rate | table | stan > > public | salary | table | stan > > public | task_instance | table | stan > > public | vendor | table | stan > > public | work_type | table | stan > > (21 rows) > > > > [1m[local] stan@stan=[0m> \d task_instance > > [?1049h[22;0;0t[?1h= Table "public.task_instance" > > Column | Type | Collation | Nullable | Default > > ---------------+--------------------------+-----------+----------+-------------- --------------------------------- > > task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) > > project_key | integer | | not null | > > employee_key | integer | | not null | > > work_type_key | integer | | not null | > > hours | numeric(5,2) | | not null | > > work_start | timestamp with time zone | | not null | > > work_end | timestamp with time zone | | not null | > > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > > lock | boolean | | | true > > descrip | character varying | | | > > Indexes: > > "task_instance_pkey" PRIMARY KEY, btree (task_instance) > > "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) > > Foreign-key constraints: > > "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work > > [?1049h[22;0;0t[?1h= Table "public.permitted_work" > > Column | Type | Collation | Nullable | Default > > ---------------+--------------------------+-----------+----------+-------------- ----- > > employee_key | integer | | not null | > > work_type_key | integer | | not null | > > permit | boolean | | not null | false > > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > > Indexes: > > "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) > > Foreign-key constraints: > > "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETE RESTRICT > > "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETERESTRICT > > > > Then I delete the rows from the table. > > > > [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; > > DELETE 31 > > > > Then I try to restore from the dump file. > > > > [1m[local] stan@stan=[0m> \i task_instance.dump > > SET > > SET > > SET > > SET > > SET > > set_config > > ------------ > > (1 row) > > > > SET > > SET > > SET > > SET > > psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist > > LINE 3: permitted_work > > ^ > > In your \d task_instance above I do not see a trigger that calls > public.check_permission(). Does one exist or was it cut off the output you > pasted? It exists. Perhaps I am using a different /dt format? I am not accustomed to seeing the triggers when I do it. > > Also look in the dump file. Given that you are using 11.5 I'm going to > assume it is resetting the search_path and that the unqualified schema name > of permitted_work below is your issue. That makes sense. If I delete all the SET lines things do work. Is this a known bug on version 11.5? Or am I doing something incorrectly? BTW 11.5 is the latest package for Ubuntu, I believe. Should I start always specifying the schema? Seems to add confusion to me. Thanks for the education here. > > > QUERY: SELECT > > permit FROM > > permitted_work > > WHERE > > NEW.employee_key = permitted_work.employee_key > > AND > > NEW.work_type_key = permitted_work.work_type_key > > CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement > > COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." > > > > After that error, U cannot see any objects in the table > > > > [1m[local] stan@stan=[0m> \dt > > Did not find any relations. > > [1m[local] stan@stan=[0m> \q > > ]0;stan@smokey: ~stan@smokey:~$ exit > > > > Script done on 2019-10-24 06:30:48-0400 > > > > quiting psql and reconecting shows that the obkects ARE there, with the > > taks)instance table empty. > > > > What am I doing wrong? > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On 10/24/19 7:32 AM, stan wrote: > On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: >> On 10/24/19 3:52 AM, stan wrote: >>> >>> >>> I have a very confusing isse. I am trying to backup and restre a signle >>> table . >>> >>> first I dump the table. >> >> Actually you are just dumping the table data. >> >> More below. >>> >>> >>> Script started on 2019-10-24 06:29:12-0400 >>> ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump >>> >>> Then I connect to the db, and verify that things are as expected. >>> >>> ]0;stan@smokey: ~stan@smokey:~$ psql >>> psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) >>> Type "help" for help. >>> >>> [1m[local] stan@stan=[0m> \dt >>> List of relations >>> Schema | Name | Type | Owner >>> --------+--------------------------+-------+------- >>> public | biz_constants | table | stan >>> public | bom_item | table | stan >>> public | costing_unit | table | stan >>> public | customer | table | stan >>> public | earthquake | table | stan >>> public | employee | table | stan >>> public | expense_report_instance | table | stan >>> public | gl_code | table | stan >>> public | mfg | table | stan >>> public | mfg_part | table | stan >>> public | mfg_vendor_relationship | table | stan >>> public | permitted_work | table | stan >>> public | phone_number_test | table | stan >>> public | project | table | stan >>> public | project_budget_component | table | stan >>> public | project_cost_category | table | stan >>> public | rate | table | stan >>> public | salary | table | stan >>> public | task_instance | table | stan >>> public | vendor | table | stan >>> public | work_type | table | stan >>> (21 rows) >>> >>> [1m[local] stan@stan=[0m> \d task_instance >>> [?1049h[22;0;0t[?1h= Table "public.task_instance" >>> Column | Type | Collation | Nullable | Default >>> ---------------+--------------------------+-----------+----------+-------------- --------------------------------- >>> task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) >>> project_key | integer | | not null | >>> employee_key | integer | | not null | >>> work_type_key | integer | | not null | >>> hours | numeric(5,2) | | not null | >>> work_start | timestamp with time zone | | not null | >>> work_end | timestamp with time zone | | not null | >>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>> lock | boolean | | | true >>> descrip | character varying | | | >>> Indexes: >>> "task_instance_pkey" PRIMARY KEY, btree (task_instance) >>> "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) >>> Foreign-key constraints: >>> "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work >>> [?1049h[22;0;0t[?1h= Table "public.permitted_work" >>> Column | Type | Collation | Nullable | Default >>> ---------------+--------------------------+-----------+----------+-------------- ----- >>> employee_key | integer | | not null | >>> work_type_key | integer | | not null | >>> permit | boolean | | not null | false >>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>> Indexes: >>> "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) >>> Foreign-key constraints: >>> "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETE RESTRICT >>> "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETERESTRICT >>> >>> Then I delete the rows from the table. >>> >>> [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; >>> DELETE 31 >>> >>> Then I try to restore from the dump file. >>> >>> [1m[local] stan@stan=[0m> \i task_instance.dump >>> SET >>> SET >>> SET >>> SET >>> SET >>> set_config >>> ------------ >>> (1 row) >>> >>> SET >>> SET >>> SET >>> SET >>> psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist >>> LINE 3: permitted_work >>> ^ >> >> In your \d task_instance above I do not see a trigger that calls >> public.check_permission(). Does one exist or was it cut off the output you >> pasted? > > It exists. Perhaps I am using a different /dt format? I am not accustomed > to seeing the triggers when I do it. > >> >> Also look in the dump file. Given that you are using 11.5 I'm going to >> assume it is resetting the search_path and that the unqualified schema name >> of permitted_work below is your issue. > > That makes sense. If I delete all the SET lines things do work. > > Is this a known bug on version 11.5? Or am I doing something incorrectly? > BTW 11.5 is the latest package for Ubuntu, I believe. Not a bug, a security fix: https://www.postgresql.org/about/news/1834/ PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22 released! https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path All versions greater then and equal to those mentioned above have the fix. > > Should I start always specifying the schema? Seems to add confusion to me. Yes. See CVE link for why that is important. > > Thanks for the education here. > >> >>> QUERY: SELECT >>> permit FROM >>> permitted_work >>> WHERE >>> NEW.employee_key = permitted_work.employee_key >>> AND >>> NEW.work_type_key = permitted_work.work_type_key >>> CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement >>> COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." >>> >>> After that error, U cannot see any objects in the table >>> >>> [1m[local] stan@stan=[0m> \dt >>> Did not find any relations. >>> [1m[local] stan@stan=[0m> \q >>> ]0;stan@smokey: ~stan@smokey:~$ exit >>> >>> Script done on 2019-10-24 06:30:48-0400 >>> >>> quiting psql and reconecting shows that the obkects ARE there, with the >>> taks)instance table empty. >>> >>> What am I doing wrong? >>> >> >> >> -- >> Adrian Klaver >> adrian.klaver@aklaver.com > -- Adrian Klaver adrian.klaver@aklaver.com
On Thu, Oct 24, 2019 at 07:40:29AM -0700, Adrian Klaver wrote: > On 10/24/19 7:32 AM, stan wrote: > > On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: > > > On 10/24/19 3:52 AM, stan wrote: > > > > > > > > > > > > I have a very confusing isse. I am trying to backup and restre a signle > > > > table . > > > > > > > > first I dump the table. > > > > > > Actually you are just dumping the table data. > > > > > > More below. > > > > > > > > > > > > Script started on 2019-10-24 06:29:12-0400 > > > > ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump > > > > > > > > Then I connect to the db, and verify that things are as expected. > > > > > > > > ]0;stan@smokey: ~stan@smokey:~$ psql > > > > psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) > > > > Type "help" for help. > > > > > > > > [1m[local] stan@stan=[0m> \dt > > > > List of relations > > > > Schema | Name | Type | Owner > > > > --------+--------------------------+-------+------- > > > > public | biz_constants | table | stan > > > > public | bom_item | table | stan > > > > public | costing_unit | table | stan > > > > public | customer | table | stan > > > > public | earthquake | table | stan > > > > public | employee | table | stan > > > > public | expense_report_instance | table | stan > > > > public | gl_code | table | stan > > > > public | mfg | table | stan > > > > public | mfg_part | table | stan > > > > public | mfg_vendor_relationship | table | stan > > > > public | permitted_work | table | stan > > > > public | phone_number_test | table | stan > > > > public | project | table | stan > > > > public | project_budget_component | table | stan > > > > public | project_cost_category | table | stan > > > > public | rate | table | stan > > > > public | salary | table | stan > > > > public | task_instance | table | stan > > > > public | vendor | table | stan > > > > public | work_type | table | stan > > > > (21 rows) > > > > > > > > [1m[local] stan@stan=[0m> \d task_instance > > > > [?1049h[22;0;0t[?1h= Table "public.task_instance" > > > > Column | Type | Collation | Nullable | Default > > > > ---------------+--------------------------+-----------+----------+-------------- --------------------------------- > > > > task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) > > > > project_key | integer | | not null | > > > > employee_key | integer | | not null | > > > > work_type_key | integer | | not null | > > > > hours | numeric(5,2) | | not null | > > > > work_start | timestamp with time zone | | not null | > > > > work_end | timestamp with time zone | | not null | > > > > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > > > > lock | boolean | | | true > > > > descrip | character varying | | | > > > > Indexes: > > > > "task_instance_pkey" PRIMARY KEY, btree (task_instance) > > > > "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) > > > > Foreign-key constraints: > > > > "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work > > > > [?1049h[22;0;0t[?1h= Table "public.permitted_work" > > > > Column | Type | Collation | Nullable | Default > > > > ---------------+--------------------------+-----------+----------+-------------- ----- > > > > employee_key | integer | | not null | > > > > work_type_key | integer | | not null | > > > > permit | boolean | | not null | false > > > > modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP > > > > Indexes: > > > > "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) > > > > Foreign-key constraints: > > > > "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETERESTRICT > > > > "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETERESTRICT > > > > > > > > Then I delete the rows from the table. > > > > > > > > [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; > > > > DELETE 31 > > > > > > > > Then I try to restore from the dump file. > > > > > > > > [1m[local] stan@stan=[0m> \i task_instance.dump > > > > SET > > > > SET > > > > SET > > > > SET > > > > SET > > > > set_config > > > > ------------ > > > > (1 row) > > > > > > > > SET > > > > SET > > > > SET > > > > SET > > > > psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist > > > > LINE 3: permitted_work > > > > ^ > > > > > > In your \d task_instance above I do not see a trigger that calls > > > public.check_permission(). Does one exist or was it cut off the output you > > > pasted? > > > > It exists. Perhaps I am using a different /dt format? I am not accustomed > > to seeing the triggers when I do it. > > > > > > > > Also look in the dump file. Given that you are using 11.5 I'm going to > > > assume it is resetting the search_path and that the unqualified schema name > > > of permitted_work below is your issue. > > > > That makes sense. If I delete all the SET lines things do work. > > > > Is this a known bug on version 11.5? Or am I doing something incorrectly? > > BTW 11.5 is the latest package for Ubuntu, I believe. > > Not a bug, a security fix: > > https://www.postgresql.org/about/news/1834/ > PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22 released! > > https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path > > All versions greater then and equal to those mentioned above have the fix. > > > > > Should I start always specifying the schema? Seems to add confusion to me. > > Yes. See CVE link for why that is important. > > > > > Thanks for the education here. > > > > > > > > > QUERY: SELECT > > > > permit FROM > > > > permitted_work > > > > WHERE > > > > NEW.employee_key = permitted_work.employee_key > > > > AND > > > > NEW.work_type_key = permitted_work.work_type_key > > > > CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement > > > > COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." > > > > > > > > After that error, U cannot see any objects in the table > > > > > > > > [1m[local] stan@stan=[0m> \dt > > > > Did not find any relations. > > > > [1m[local] stan@stan=[0m> \q > > > > ]0;stan@smokey: ~stan@smokey:~$ exit > > > > > > > > Script done on 2019-10-24 06:30:48-0400 > > > > > > > > quiting psql and reconecting shows that the obkects ARE there, with the > > > > taks)instance table empty. > > > > > > > > What am I doing wrong? > > > > And I thought we were done with this :-( So, I created a schema for the project. Gave all the users permissions on that schema, recreated all the object in the new schema, verified that everything, including the functions are in that schema, and I when I dump a table, and try to restore it I get the original error. I see this line in the dump: SELECT pg_catalog.set_config('search_path', '', false); So, it appears that this means that the function cannot be found, even if it is in the new (default) schema. Oh yes, I did make the new schema the first thing in the search path defined in the system-wide postgresql.conf file. Surely this cant be the intended behavior. -- "They that would give up essential liberty for temporary safety deserve neither liberty nor safety." -- Benjamin Franklin
On 10/24/19 2:58 PM, stan wrote: > > On Thu, Oct 24, 2019 at 07:40:29AM -0700, Adrian Klaver wrote: >> On 10/24/19 7:32 AM, stan wrote: >>> On Thu, Oct 24, 2019 at 07:04:11AM -0700, Adrian Klaver wrote: >>>> On 10/24/19 3:52 AM, stan wrote: >>>>> >>>>> >>>>> I have a very confusing isse. I am trying to backup and restre a signle >>>>> table . >>>>> >>>>> first I dump the table. >>>> >>>> Actually you are just dumping the table data. >>>> >>>> More below. >>>>> >>>>> >>>>> Script started on 2019-10-24 06:29:12-0400 >>>>> ]0;stan@smokey: ~stan@smokey:~$ pg_dump -a --table task_instance > task_instance.dump >>>>> >>>>> Then I connect to the db, and verify that things are as expected. >>>>> >>>>> ]0;stan@smokey: ~stan@smokey:~$ psql >>>>> psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1)) >>>>> Type "help" for help. >>>>> >>>>> [1m[local] stan@stan=[0m> \dt >>>>> List of relations >>>>> Schema | Name | Type | Owner >>>>> --------+--------------------------+-------+------- >>>>> public | biz_constants | table | stan >>>>> public | bom_item | table | stan >>>>> public | costing_unit | table | stan >>>>> public | customer | table | stan >>>>> public | earthquake | table | stan >>>>> public | employee | table | stan >>>>> public | expense_report_instance | table | stan >>>>> public | gl_code | table | stan >>>>> public | mfg | table | stan >>>>> public | mfg_part | table | stan >>>>> public | mfg_vendor_relationship | table | stan >>>>> public | permitted_work | table | stan >>>>> public | phone_number_test | table | stan >>>>> public | project | table | stan >>>>> public | project_budget_component | table | stan >>>>> public | project_cost_category | table | stan >>>>> public | rate | table | stan >>>>> public | salary | table | stan >>>>> public | task_instance | table | stan >>>>> public | vendor | table | stan >>>>> public | work_type | table | stan >>>>> (21 rows) >>>>> >>>>> [1m[local] stan@stan=[0m> \d task_instance >>>>> [?1049h[22;0;0t[?1h= Table "public.task_instance" >>>>> Column | Type | Collation | Nullable | Default >>>>> ---------------+--------------------------+-----------+----------+-------------- --------------------------------- >>>>> task_instance | integer | | not null | nextval('task _instance_key_serial'::regclass) >>>>> project_key | integer | | not null | >>>>> employee_key | integer | | not null | >>>>> work_type_key | integer | | not null | >>>>> hours | numeric(5,2) | | not null | >>>>> work_start | timestamp with time zone | | not null | >>>>> work_end | timestamp with time zone | | not null | >>>>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>>>> lock | boolean | | | true >>>>> descrip | character varying | | | >>>>> Indexes: >>>>> "task_instance_pkey" PRIMARY KEY, btree (task_instance) >>>>> "task_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key, pro ject_key, work_start, work_end) >>>>> Foreign-key constraints: >>>>> "task_instance_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES empl :[K[K[?1l>[?1049l[23;0;0t[1m[local]stan@stan=[0m> \d permitted_work >>>>> [?1049h[22;0;0t[?1h= Table "public.permitted_work" >>>>> Column | Type | Collation | Nullable | Default >>>>> ---------------+--------------------------+-----------+----------+-------------- ----- >>>>> employee_key | integer | | not null | >>>>> work_type_key | integer | | not null | >>>>> permit | boolean | | not null | false >>>>> modtime | timestamp with time zone | | not null | CURRENT_TIMES TAMP >>>>> Indexes: >>>>> "permit_constraint" UNIQUE CONSTRAINT, btree (employee_key, work_type_key) >>>>> Foreign-key constraints: >>>>> "permitted_work_employee_key_fkey" FOREIGN KEY (employee_key) REFERENCES emp loyee(employee_key) ON DELETERESTRICT >>>>> "permitted_work_work_type_key_fkey" FOREIGN KEY (work_type_key) REFERENCES w ork_type(work_type_key) ON DELETERESTRICT >>>>> >>>>> Then I delete the rows from the table. >>>>> >>>>> [7m(END)[27m[K[K[?1l>[?1049l[23;0;0t[1m[local] stan@stan=[0m> delete from task_instance ; >>>>> DELETE 31 >>>>> >>>>> Then I try to restore from the dump file. >>>>> >>>>> [1m[local] stan@stan=[0m> \i task_instance.dump >>>>> SET >>>>> SET >>>>> SET >>>>> SET >>>>> SET >>>>> set_config >>>>> ------------ >>>>> (1 row) >>>>> >>>>> SET >>>>> SET >>>>> SET >>>>> SET >>>>> psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist >>>>> LINE 3: permitted_work >>>>> ^ >>>> >>>> In your \d task_instance above I do not see a trigger that calls >>>> public.check_permission(). Does one exist or was it cut off the output you >>>> pasted? >>> >>> It exists. Perhaps I am using a different /dt format? I am not accustomed >>> to seeing the triggers when I do it. >>> >>>> >>>> Also look in the dump file. Given that you are using 11.5 I'm going to >>>> assume it is resetting the search_path and that the unqualified schema name >>>> of permitted_work below is your issue. >>> >>> That makes sense. If I delete all the SET lines things do work. >>> >>> Is this a known bug on version 11.5? Or am I doing something incorrectly? >>> BTW 11.5 is the latest package for Ubuntu, I believe. >> >> Not a bug, a security fix: >> >> https://www.postgresql.org/about/news/1834/ >> PostgreSQL 10.3, 9.6.8, 9.5.12, 9.4.17, and 9.3.22 released! >> >> https://wiki.postgresql.org/wiki/A_Guide_to_CVE-2018-1058%3A_Protect_Your_Search_Path >> >> All versions greater then and equal to those mentioned above have the fix. >> >>> >>> Should I start always specifying the schema? Seems to add confusion to me. >> >> Yes. See CVE link for why that is important. >> >>> >>> Thanks for the education here. >>> >>>> >>>>> QUERY: SELECT >>>>> permit FROM >>>>> permitted_work >>>>> WHERE >>>>> NEW.employee_key = permitted_work.employee_key >>>>> AND >>>>> NEW.work_type_key = permitted_work.work_type_key >>>>> CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement >>>>> COPY task_instance, line 1: "1 1 1 8 17.50 2019-02-01 00:00:00-05 2019-02-08 00:00:00-05 2019-10-2406:28:44.502699-04 t Drawings..." >>>>> >>>>> After that error, U cannot see any objects in the table >>>>> >>>>> [1m[local] stan@stan=[0m> \dt >>>>> Did not find any relations. >>>>> [1m[local] stan@stan=[0m> \q >>>>> ]0;stan@smokey: ~stan@smokey:~$ exit >>>>> >>>>> Script done on 2019-10-24 06:30:48-0400 >>>>> >>>>> quiting psql and reconecting shows that the obkects ARE there, with the >>>>> taks)instance table empty. >>>>> >>>>> What am I doing wrong? >>>>> > > And I thought we were done with this :-( > > So, I created a schema for the project. Gave all the users permissions on that schema, > recreated all the object in the new schema, verified that everything, including the functions > are in that schema, and I when I dump a table, and try to restore it I get the original > error. I see this line in the dump: > > SELECT pg_catalog.set_config('search_path', '', false); > > So, it appears that this means that the function cannot be found, even if it is in the new > (default) schema. The original error was not about finding the function it was about not finding the table in the function: psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist LINE 3: permitted_work ^ QUERY: SELECT permit FROM permitted_work WHERE NEW.employee_key = permitted_work.employee_key AND NEW.work_type_key = permitted_work.work_type_key CONTEXT: PL/pgSQL function public.check_permission() line 4 at SQL statement You need to schema qualify the table name inside the function. > > Oh yes, I did make the new schema the first thing in the search path defined in the system-wide > postgresql.conf file. That won't matter in this case as: SELECT pg_catalog.set_config('search_path', '', false); sets an empty search_path for the session. > > Surely this cant be the intended behavior. > -- Adrian Klaver adrian.klaver@aklaver.com
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 10/24/19 2:58 PM, stan wrote: >> So, it appears that this means that the function cannot be found, even if it is in the new >> (default) schema. > The original error was not about finding the function it was about not > finding the table in the function: > psql:task_instance.dump:55: ERROR: relation "permitted_work" does not exist > LINE 3: permitted_work > ^ > You need to schema qualify the table name inside the function. Or attach a SET clause to the function, so that it executes with a known search_path regardless of the session's prevailing path. regards, tom lane