Обсуждение: How can I recreate a view in a new schema such that the view defreferences 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

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

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:

https://www.postgresql.org/message-id/31367.1572815723%40sss.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