Thread: BUG #6660: losing schema name in pg_dump
The following bug has been logged on the website:
Bug reference: 6660
Logged by: C P Kulkarni
Email address: shreeseva.it@gmail.com
PostgreSQL version: 9.1.3
Operating system: Fedora 16
Description:=20=20=20=20=20=20=20=20
Many times I have to dump all objects from a schema (single schema holding
only functions and views) in plain text format. It is found that pg_dump
includes a set search_path statement at the beginning and drops all
occurrences of the schema name (to which dumped object belongs) from
function/view bodies even if they are explicitly defined while creating
functions/views.
Functions or view definition from information_schema tables shows the
explicitly defined schema names but pg_dump does not shows them.=20
This makes it problematic to maintain the software available for another
RDBMS as for most other RDBMS we have to explicitly define schema names
always.=20
I have two tables software.orders and software.products.
I created two views.
CREATE OR REPLACE VIEW software.o1 AS
SELECT orders.orderid, orders.productid, orders.amount
FROM software.orders;
CREATE OR REPLACE VIEW software.o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname
FROM software.o1
JOIN software.products ON o1.productid =3D products.productid;
Now I take backup for plain type to generate a sql file.
In that file pg_dump shows the view definitions as follows.
CREATE VIEW o1 AS
SELECT orders.orderid, orders.productid, orders.amount FROM orders;
CREATE VIEW o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname FROM
(o1 JOIN products ON ((o1.productid =3D products.productid)));
If I changed view o2 like this.
CREATE OR REPLACE VIEW software.o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname
FROM software.o1
JOIN core.products ON o1.productid =3D products.productid;
and again generated sql script from pg_dump then it still removes the schema
name occurrences of the same schema only where the object resides, from the
view definition.
CREATE VIEW o2 AS
SELECT o1.orderid, o1.productid, o1.amount, products.productname FROM
(o1 JOIN core.products ON ((o1.productid =3D products.productid)));
It keeps the schema name 'core' as it is but removes 'software'.
So it makes difficult porting the database to other RDBMS systems and also
to manage updates to customer installations. Having few hundred views and
few hundred functions makes it more difficult. So it will be better not to
remove any schema name when it is explicitly defined.
shreeseva.it@gmail.com writes:
> Many times I have to dump all objects from a schema (single schema holding
> only functions and views) in plain text format. It is found that pg_dump
> includes a set search_path statement at the beginning and drops all
> occurrences of the schema name (to which dumped object belongs) from
> function/view bodies even if they are explicitly defined while creating
> functions/views.
This is not a bug. It's intentional behavior and is convenient more
often than not.
> This makes it problematic to maintain the software available for another
> RDBMS as for most other RDBMS we have to explicitly define schema names
> always.
This claim seems quite baseless. You certainly haven't offered any
actual evidence of a situation where omitting unnecessary schema names
is problematic.
More generally, though, pg_dump has never attempted to promise that its
output can be loaded into other DBMSes without pain, and it would be
practically impossible to make such a promise; there are too many subtle
differences in the various dialects of SQL. If you have SQL that is
intended to work in multiple DBMSes, you would be best advised to
maintain it as text files (perhaps in an SCM) rather than hope that
pg_dump's output will still be useful for the other systems.
regards, tom lane
Thanks for your reply. I know that pg_dump is not intended to provide SQL script to be working in many other DBMS. But even if I have to dump all functions/views (tables and other objects are not changed a lot like functions/views due to changes in business rules or behaviour) from a certain schema with explicit schema names and if I have objects with same name in other schemas which are also part of the search_path then it creates a problem. I didn't understand when user have defined schema names explicitly in definition of the objects and most database developers insist on writing schema names explicitly, why pg_dump is not outputting it as it is. I know that using SCM is better but many times such object definitions are directly transferred to clients without SCM and it can create problems when using same object names in multiple schemas. Now I got details about pg_get_functiondef(oid) from pg_catalog which outputs the definitions of functions as it is. So thanks a lot. It will be still helpful not omit schema name when explicitly defined. You are also requested to give more details about why t is more convenient to omit schema names? Regards, C P Kulkarni On Tue, May 22, 2012 at 7:09 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > shreeseva.it@gmail.com writes: > > Many times I have to dump all objects from a schema (single schema > holding > > only functions and views) in plain text format. It is found that pg_dump > > includes a set search_path statement at the beginning and drops all > > occurrences of the schema name (to which dumped object belongs) from > > function/view bodies even if they are explicitly defined while creating > > functions/views. > > This is not a bug. It's intentional behavior and is convenient more > often than not. > > > This makes it problematic to maintain the software available for another > > RDBMS as for most other RDBMS we have to explicitly define schema names > > always. > > This claim seems quite baseless. You certainly haven't offered any > actual evidence of a situation where omitting unnecessary schema names > is problematic. > > More generally, though, pg_dump has never attempted to promise that its > output can be loaded into other DBMSes without pain, and it would be > practically impossible to make such a promise; there are too many subtle > differences in the various dialects of SQL. If you have SQL that is > intended to work in multiple DBMSes, you would be best advised to > maintain it as text files (perhaps in an SCM) rather than hope that > pg_dump's output will still be useful for the other systems. > > regards, tom lane > -- Chaitanya P. Kulkarni Copyright. All rights are reserved for any object, text, ideas, images and any other things. ----------------------------------------------------------------------- Keep your Environment clean and green. --------------------------xox----------------------------------------
Chaitany Kulkarni <shreeseva.it@gmail.com> writes:
> I didn't understand when user have defined schema names explicitly in
> definition of the objects and most database developers insist on writing
> schema names explicitly, why pg_dump is not outputting it as it is.
The short answer to that is that the internal representation of views
and other objects only stores object identities, not how they were named
in the input. We don't store whitespace or layout choices either, and
there are various other ways that the output can vary from what you
typed. So really, if you're intent on that, keeping the original source
file is the way to go.
regards, tom lane
Sorry for the late reply. But after comparing output results of pg_dump and other tools in SQL format, I think it will helpful if an another switch is provided in pg_dump to output data and definition of objects of only given type. e.g. pg_dump --type views It will export only views in given format for given other options such as database, schema etc. Currently pg_dump supports to export the objects as per the list but it is very difficult to list few hundred items there. So it will be helpful when definitions and data of only certain types from a schema are needed. For SCM, I am looking for various options but SCM do not provide tools to compare objects from two or more databases and database developers need more options to manage database objects. Many times clients can change the object definitions as per there needs and it becomes very hard to maintain a version for each customer for a product. So looking to use Extension mechanism or some thing else. Thanks and regards, C P Kulkarni On Wed, May 23, 2012 at 11:11 PM, Chaitany Kulkarni <shreeseva.it@gmail.com>wrote: > On Tue, May 22, 2012 at 8:33 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote: > >> Chaitany Kulkarni <shreeseva.it@gmail.com> writes: >> > I didn't understand when user have defined schema names explicitly in >> > definition of the objects and most database developers insist on writing >> > schema names explicitly, why pg_dump is not outputting it as it is. >> >> The short answer to that is that the internal representation of views >> and other objects only stores object identities, not how they were named >> in the input. We don't store whitespace or layout choices either, and >> there are various other ways that the output can vary from what you >> typed. So really, if you're intent on that, keeping the original source >> file is the way to go. >> >> regards, tom lane >> > > > > > -- > Chaitanya P. Kulkarni > > Copyright. All rights are reserved for any object, text, ideas, images and > any other things. > ----------------------------------------------------------------------- > Keep your Environment clean and green. > --------------------------xox---------------------------------------- > > -- Chaitanya P. Kulkarni Copyright. All rights are reserved for any object, text, ideas, images and any other things. ----------------------------------------------------------------------- Keep your Environment clean and green. --------------------------xox----------------------------------------