Re: A very puzzling backup/restore problem

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(at)smokey: ~stan(at)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(at)smokey: ~stan(at)smokey:~$ psql
> psql (11.5 (Ubuntu 11.5-3.pgdg18.04+1))
> Type "help" for help.
>
> [local] stan(at)stan=> \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)
>
> [local] stan(at)stan=> \d task_instance
> [?1049h[?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 :[?1l>[?1049l[local] stan(at)stan=> \d permitted_work
> [?1049h[?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.
>
> (END)[?1l>[?1049l[local] stan(at)stan=> delete from task_instance ;
> DELETE 31
>
> Then I try to restore from the dump file.
>
> [local] stan(at)stan=> \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-24 06:28:44.502699-04 t Drawings..."
>
> After that error, U cannot see any objects in the table
>
> [local] stan(at)stan=> \dt
> Did not find any relations.
> [local] stan(at)stan=> \q
> ]0;stan(at)smokey: ~stan(at)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(dot)klaver(at)aklaver(dot)com