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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

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

David Gauthier
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.  

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 ?

Thanks in Advance.
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

David Gauthier
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

David Gauthier
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 <[hidden email]> 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 <[hidden email]
> <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

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

Adrian Klaver-4
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
[hidden email]


Reply | Threaded
Open this post in threaded view
|

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

Laurenz Albe
In reply to this post by David Gauthier
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