Re: A very puzzling backup/restore problem

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(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?

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-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

--
"They that would give up essential liberty for temporary safety deserve
neither liberty nor safety."
-- Benjamin Franklin