How can I recreate a view in a new schema such that the view def references tables in the new schema ?

On 3/26/20 10:16 AM, David Gauthier wrote:

Here's an interesting one for you...
psql (9.6.7, server 11.3) on linux

I have 2 DBs, differnet servers/instances.  I want to take all the
metadata and data for a set of tables/views in the public schema of one
DB and move it all over to be inside a schema of a second DB/instance.

Well first, the current minor version of 9.6 is .17 so you are 10
releases behind. In fact the 9.6.8 release includes changes that impact
the below:
https://www.postgresql.org/docs/9.6/release-9-6-8.html

I'm using pg_dump to create the script and I believe I can insert a "set
search_path=myschem" in the output of pg_dump such that when it runs,
the "CREATE TABLE", "CREATE VIEW", "GRANT...", etc.... commands, will
all go into the new schema (which I have prepared).  Problem is the view
defs.
The view defs do not prefix the referenced tables with "myschem.", so
the CREATE VIEW xyx commands fail.

Is there a way to do this ?

By manually changing the definition? It is not an error for a VIEW in
one schema to refer to tables in other schemas. AFAIK the code has no
way of knowing you want to move the underlying tables just by specifying
a search_path.

Thanks in Advance.

--
Adrian Klaver
adrian.klaver@aklaver.com

Thanks Adrian for the quick reply.
I don't have a lot of choice regarding PG version. I work for a large corp
with an IT dept which offers the version I have. They create VMs which are
DB servers and this is the best they offer. But I could request something
newer. Never hurts to try.

Ya, I kinda figured that there's nothing wrong with referencing tables from
the default (public) schema. So I tried to redefine the view by
referencing the public tables literally, as in "public.thetable". The plan
was to do some sort of global replace of "public." with "myschem." in the
output of pg_dump, maybe with sed or something. But even after explicitly
using "public.", it didn't stick in the view def.

On Thu, Mar 26, 2020 at 1:34 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 3/26/20 10:55 AM, David Gauthier wrote:

Thanks Adrian for the quick reply.
I don't have a lot of choice regarding PG version.  I work for a large
corp with an IT dept which offers the version I have.  They create VMs
which are DB servers and this is the best they offer.  But I could
request something newer.  Never hurts to try.

Ya, I kinda figured that there's nothing wrong with referencing tables
from the default (public) schema.  So I tried to redefine the view by
referencing the public tables literally, as in "public.thetable".  The
plan was to do some sort of global replace of "public." with "myschem."
in the output of pg_dump, maybe with sed or something.  But even after
explicitly using "public.", it didn't stick in the view def.

Can we see an example view definition?

On Thu, Mar 26, 2020 at 1:34 PM Adrian Klaver <adrian.klaver@aklaver.com
<mailto:adrian.klaver@aklaver.com>> wrote:

On 3/26/20 10:16 AM, David Gauthier wrote:

Here's an interesting one for you...
psql (9.6.7, server 11.3) on linux

I have 2 DBs, differnet servers/instances.  I want to take all the
metadata and data for a set of tables/views in the public schema

of one

DB and move it all over to be inside a schema of a second

DB/instance.

Well first, the current minor version of 9.6 is .17 so you are 10
releases behind. In fact the 9.6.8 release includes changes that impact
the below:
https://www.postgresql.org/docs/9.6/release-9-6-8.html

I'm using pg_dump to create the script and I believe I can insert

a "set

search_path=myschem" in the output of pg_dump such that when it

runs,

the "CREATE TABLE", "CREATE VIEW", "GRANT...", etc.... commands,

will

all go into the new schema (which I have prepared).  Problem is

the view

defs.
The view defs do not prefix the referenced tables with

"myschem.", so

the CREATE VIEW xyx commands fail.

Is there a way to do this ?

By manually changing the definition? It is not an error for a VIEW in
one schema to refer to tables in other schemas. AFAIK the code has no
way of knowing you want to move the underlying tables just by
specifying
a search_path.

Thanks in Advance.

--
Adrian Klaver
adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.com

sqf-> SELECT sr.project,
sqf-> sr.sqf_id,
sqf-> wa.wa_path,
sqf-> sr.cbwa_type,
sqf-> sr.status,
sqf-> sr.nightly_rg_cl,
sqf-> ( SELECT max(fse.end_datetime) AS max
sqf(> FROM public.flow_step_events fse
sqf(> WHERE fse.sqf_id = sr.sqf_id) AS last_sqf_step_end,
sqf-> ( SELECT DISTINCT f.perl_sub_name
sqf(> FROM public.flows f,
sqf(> public.flow_step_events fse
sqf(> WHERE f.flow_type = fse.flow_type AND fse.sqf_id =
sr.sqf_id AND f.step_number = (( SELECT max(fse2.step_number) AS max
sqf(> FROM public.flow_step_events fse2
sqf(> WHERE fse2.sqf_id = sr.sqf_id))) AS last_step_run
sqf-> FROM public.sqf_runs sr,
sqf-> public.workareas wa
sqf-> WHERE wa.current_user_sqf_id = sr.sqf_id
sqf-> ORDER BY sr.project, wa.wa_path, (( SELECT max(fse.end_datetime) AS
max
sqf(> FROM public.flow_step_events fse
sqf(> WHERE fse.sqf_id = sr.sqf_id));
CREATE VIEW

sqf=> \d+ current_workarea_users;
View "public.current_workarea_users"
Column | Type | Modifiers | Storage |
Description
-------------------+--------------------------+-----------+----------+-------------
project | text | | extended |
sqf_id | text | | extended |
wa_path | text | | extended |
cbwa_type | text | | extended |
status | text | | extended |
nightly_rg_cl | integer | | plain |
last_sqf_step_end | timestamp with time zone | | plain |
last_step_run | text | | extended |
View definition:
SELECT sr.project,
sr.sqf_id,
wa.wa_path,
sr.cbwa_type,
sr.status,
sr.nightly_rg_cl,
( SELECT max(fse.end_datetime) AS max
FROM flow_step_events fse
WHERE fse.sqf_id = sr.sqf_id) AS last_sqf_step_end,
( SELECT DISTINCT f.perl_sub_name
FROM flows f,
flow_step_events fse
WHERE f.flow_type = fse.flow_type AND fse.sqf_id = sr.sqf_id AND
f.step_number = (( SELECT max(fse2.step_number) AS max
FROM flow_step_events fse2
WHERE fse2.sqf_id = sr.sqf_id))) AS last_step_run
FROM sqf_runs sr,
workareas wa
WHERE wa.current_user_sqf_id = sr.sqf_id
ORDER BY sr.project, wa.wa_path, (( SELECT max(fse.end_datetime) AS max
FROM flow_step_events fse
WHERE fse.sqf_id = sr.sqf_id));

sqf=>

You can see the "public." refs in the create view, but not echoed in the
stored view def.

On Thu, Mar 26, 2020 at 1:58 PM Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

On 3/26/20 11:38 AM, David Gauthier wrote:

sqf-> SELECT sr.project,
sqf->     sr.sqf_id,
sqf->     wa.wa_path,
sqf->     sr.cbwa_type,
sqf->     sr.status,
sqf->     sr.nightly_rg_cl,
sqf->     ( SELECT max(fse.end_datetime) AS max
sqf(>            FROM public.flow_step_events fse
sqf(>           WHERE fse.sqf_id = sr.sqf_id) AS last_sqf_step_end,
sqf->     ( SELECT DISTINCT f.perl_sub_name
sqf(>            FROM public.flows f,
sqf(>             public.flow_step_events fse
sqf(>           WHERE f.flow_type = fse.flow_type AND fse.sqf_id =
sr.sqf_id AND f.step_number = (( SELECT max(fse2.step_number) AS max
sqf(>                    FROM public.flow_step_events fse2
sqf(>                   WHERE fse2.sqf_id = sr.sqf_id))) AS last_step_run
sqf->    FROM public.sqf_runs sr,
sqf->     public.workareas wa
sqf->   WHERE wa.current_user_sqf_id = sr.sqf_id
sqf->   ORDER BY sr.project, wa.wa_path, (( SELECT max(fse.end_datetime)
AS max
sqf(>            FROM public.flow_step_events fse
sqf(>           WHERE fse.sqf_id = sr.sqf_id));
CREATE VIEW

sqf=> \d+ current_workarea_users;
                       View "public.current_workarea_users"
      Column       |           Type           | Modifiers | Storage  |
Description
-------------------+--------------------------+-----------+----------+-------------
 project           | text                     |           | extended |
 sqf_id            | text                     |           | extended |
 wa_path           | text                     |           | extended |
 cbwa_type         | text                     |           | extended |
 status            | text                     |           | extended |
 nightly_rg_cl     | integer                  |           | plain    |
 last_sqf_step_end | timestamp with time zone |           | plain    |
 last_step_run     | text                     |           | extended |
View definition:
 SELECT sr.project,
    sr.sqf_id,
    wa.wa_path,
    sr.cbwa_type,
    sr.status,
    sr.nightly_rg_cl,
    ( SELECT max(fse.end_datetime) AS max
           FROM flow_step_events fse
          WHERE fse.sqf_id = sr.sqf_id) AS last_sqf_step_end,
    ( SELECT DISTINCT f.perl_sub_name
           FROM flows f,
            flow_step_events fse
          WHERE f.flow_type = fse.flow_type AND fse.sqf_id = sr.sqf_id
AND f.step_number = (( SELECT max(fse2.step_number) AS max
                   FROM flow_step_events fse2
                  WHERE fse2.sqf_id = sr.sqf_id))) AS last_step_run
   FROM sqf_runs sr,
    workareas wa
  WHERE wa.current_user_sqf_id = sr.sqf_id
  ORDER BY sr.project, wa.wa_path, (( SELECT max(fse.end_datetime) AS max
           FROM flow_step_events fse
          WHERE fse.sqf_id = sr.sqf_id));

sqf=>

You can see the "public." refs in the create view, but not echoed in the
stored view def.

See this post:

/messages/by-id/31367.1572815723@sss.pgh.pa.us

--
Adrian Klaver
adrian.klaver@aklaver.com

On Thu, 2020-03-26 at 13:16 -0400, David Gauthier wrote:

psql (9.6.7, server 11.3) on linux

I have 2 DBs, differnet servers/instances. I want to take all the metadata and data for a set of tables/views
in the public schema of one DB and move it all over to be inside a schema of a second DB/instance.

I'm using pg_dump to create the script and I believe I can insert a "set search_path=myschem" in the output
of pg_dump such that when it runs, the "CREATE TABLE", "CREATE VIEW", "GRANT...", etc.... commands,
will all go into the new schema (which I have prepared). Problem is the view defs.
The view defs do not prefix the referenced tables with "myschem.", so the CREATE VIEW xyx commands fail.

Is there a way to do this ?

The best way would be to use pg_dump to move the schema definitions over
while preserving the "public" schema, and then use

ALTER ... SET SCHEMA newschema;

on all objects.

Yours,
Laurenz Albe
--
Cybertec | https://www.cybertec-postgresql.com