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

[Laurenz Albe]

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

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

[Adrian Klaver]

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.kla...@aklaver.com

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

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

[Adrian Klaver]

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 > 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.kla...@aklaver.com 




--
Adrian Klaver
adrian.kla...@aklaver.com

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 
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.kla...@aklaver.com
>

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

[Adrian Klaver]

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.kla...@aklaver.com