Preserving the source code of views

On 20/10/13 16:38, Brian Crowell wrote:

Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in
our in-house systems. I've been really impressed with it so far, and
I'm eager to try it with our data sets.

I've run across one thing that would make a transfer difficult.
Postgres doesn't preserve the source code for views, as far as I can
tell. It parses them and then prints them its own way. We have a lot
of complicated views, where both the formatting and the comments are
significant. In fact, we produce some of our system documentation
directly from the comments.

Is there currently a way to preserve the original source code of a
view as entered in the CREATE VIEW statement?

--Brian

The 'Real Experts' may well have more practically elegant solutions, but
a couple possibilities I can think of are as follows:

1. You have access to the source of PostgreSQL, so you could modify the
source code to preserve the source code of the views.

2. This might be able to be done using the extension mechanism, but I
have not looked into that myself.

So yes, you can do what you want, but not necessarily as easily as you
would like.

Cheers,
Gavin

Hello

2013/10/20 Brian Crowell <brian@fluggo.com>

Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our
in-house systems. I've been really impressed with it so far, and I'm eager
to try it with our data sets.

I've run across one thing that would make a transfer difficult. Postgres
doesn't preserve the source code for views, as far as I can tell. It parses
them and then prints them its own way. We have a lot of complicated views,
where both the formatting and the comments are significant. In fact, we
produce some of our system documentation directly from the comments.

Is there currently a way to preserve the original source code of a view as
entered in the CREATE VIEW statement?

I don't known about any way, how to do it (without hacking postgresql
source code). PostgreSQL saves a views in preprocessed form from
performance reasons.

There are a few recommendation how to solve this issue - I never had a
problem with it, because I use a different workflow.

a) never modify a database object structure in database with admin tools.
Use a SQL scripts ever.
* a admin tools has not good has not good editors
* there are no possibility to join related code together
* there are no good versioning
* a portability of handly written SQL scripts is significantly better than
SQL scripts generated by admin tools

I ever write a SQL scripts saved in files - then I can to push on one place
(one file) related different objects - triggers, views, tables, procedures
- with comments on file start, and with comments before any object.

b) if you don't like @a, use a COMMENTs

postgres=# create view simply as select 10;
CREATE VIEW
postgres=# comment on view simply is 'very simple view';
COMMENT

postgres=# \dv+
List of relations
Schema │ Name │ Type │ Owner │ Size │ Description
────────┼────────┼──────┼───────┼─────────┼──────────────────
public │ simply │ view │ pavel │ 0 bytes │ very simple view
(1 row)

a 9.2 and newer PostgreSQL formats a materialized view - so it lost your
formatting, but result will be in good format too.

postgres=# create view simply as select 10 from pg_class where true;
CREATE VIEW
Time: 97.584 ms
postgres=# \d+ simply
View "public.simply"
Column │ Type │ Modifiers │ Storage │ Description
──────────┼─────────┼───────────┼─────────┼─────────────
?column? │ integer │ │ plain │
View definition:
SELECT 10
FROM pg_class
WHERE true;

For my work is very significant @a point - I wrote and I am writing usually
database centric stored procedures centric applications and @a works
perfect. For me a SQL code is code as any other - I use a my favourite
editor, I use a GIT for versioning, I can simple distributed application to
my customers.

Regards

Pavel

--Brian

On Sat, 19 Oct 2013 22:38:28 -0500 Brian Crowell <brian@fluggo.com> wrote:

Hello! I'm evaluating PostgreSQL as a replacement for SQL Server in our
in-house systems. I've been really impressed with it so far, and I'm eager
to try it with our data sets.

I've run across one thing that would make a transfer difficult. Postgres
doesn't preserve the source code for views, as far as I can tell. It parses
them and then prints them its own way. We have a lot of complicated views,
where both the formatting and the comments are significant. In fact, we
produce some of our system documentation directly from the comments.

Is there currently a way to preserve the original source code of a view as
entered in the CREATE VIEW statement?

You could adjust your workflow to use something like dbsteward:
http://dbsteward.org/

--
Bill Moran <wmoran@potentialtech.com>

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Brian Crowell <brian@fluggo.com> writes:

I've run across one thing that would make a transfer difficult. Postgres
doesn't preserve the source code for views, as far as I can tell. It parses
them and then prints them its own way. We have a lot of complicated views,
where both the formatting and the comments are significant. In fact, we
produce some of our system documentation directly from the comments.

Is there currently a way to preserve the original source code of a view as
entered in the CREATE VIEW statement?

No, and it's very unlikely that there ever will be, because it's
completely against the system structure at a number of levels. However,
there's more than one way to skin this cat. Many people keep their DDL as
text in some external CMS, and just load it into the database again after
any change. If you write the view as "CREATE OR REPLACE VIEW ..." in your
DDL, this is easy, at least for cases where you're not changing the set of
columns provided by the view.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, and it's very unlikely that there ever will be, because it's
completely against the system structure at a number of levels. However,
there's more than one way to skin this cat. Many people keep their DDL as
text in some external CMS, and just load it into the database again after
any change. If you write the view as "CREATE OR REPLACE VIEW ..." in your
DDL, this is easy, at least for cases where you're not changing the set of
columns provided by the view.

All right, so I'm getting the picture. Postgres developers are keeping
their code in larger scripts and committing them to databases as
batches, rather than editing them in place. We had a pretty slick
setup going with developers writing code against a live dev database,
with system documentation pulled from the SQL object comments, and the
ability to script the whole database to/from git when needed for
source control purposes.

In some raw form, the workflow here is more like: write a module in a
script file that re-commits all its objects to the database, and then
execute that whole batch when testing or deploying. Commit that script
to git. If I want to keep my documentation scheme, I'll need to pull
the comments out of that file. A harder sell to my developers, but not
impossible.

I noticed some objects (like views) are very picky about dependents.
Do you drop all the module's objects at the beginning of the script,
just in case there's a change in the number or types of columns? That
seems tricky, especially considering there will be modules that depend
on yours.

You also mentioned an external CMS. Any suggestions?

--Brian

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sun, Oct 20, 2013 at 7:01 AM, Bill Moran <wmoran@potentialtech.com> wrote:

You could adjust your workflow to use something like dbsteward:
http://dbsteward.org/

Nifty, but without an editor, I don't think I could convince our
developers to author the databases in XML.

--Brian

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Sat, Oct 19, 2013 at 11:37 PM, Pavel Stehule <pavel.stehule@gmail.com> wrote:

For my work is very significant @a point - I wrote and I am writing usually
database centric stored procedures centric applications and @a works
perfect. For me a SQL code is code as any other - I use a my favourite
editor, I use a GIT for versioning, I can simple distributed application to
my customers.

What do you do to manage dependencies among the objects? How do you
have your scripts set up to handle deploying, say, an extra column to
an existing view?

Our databases tended to be SP-heavy before I came along. I'm big on
views because that allows my client code to do very specific queries
without having to write new SPs all the time.

--Brian

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

2013/10/21 Brian Crowell <brian@fluggo.com>

On Sat, Oct 19, 2013 at 11:37 PM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

For my work is very significant @a point - I wrote and I am writing

usually

database centric stored procedures centric applications and @a works
perfect. For me a SQL code is code as any other - I use a my favourite
editor, I use a GIT for versioning, I can simple distributed application

to

my customers.

What do you do to manage dependencies among the objects? How do you
have your scripts set up to handle deploying, say, an extra column to
an existing view?

I write a delta scripts - that ensure moving database structure to expected
state. Now, you can use a infrastructure in PostgreSQL related to CREATE
EXTENSION that is able to manage these delta scripts. But I wrote own
(simple) three years ago. When you manage these scripts (together with
regress tests) continuously, then it works - and delta scripts are a good
self documentation again - there is usually different delta than GIT
repository. I'll do more often commits in GIT than I delivery features to
customer.

Our databases tended to be SP-heavy before I came along. I'm big on
views because that allows my client code to do very specific queries
without having to write new SPs all the time.

There is little bit different approach in PG than MSSQL. Some times is
recommended to write procedures for all in MSSQL. PG is similar to Oracle.
Procedures (functions) are written only for processes that changes data (or
for triggers). For queries use a views mainly. Only when views cannot be
used from some reasons (more complex security, some auditing) use a table
functions.

Regards

Pavel

Brian,

* Brian Crowell (brian@fluggo.com) wrote:

On Sun, Oct 20, 2013 at 4:24 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

No, and it's very unlikely that there ever will be, because it's
completely against the system structure at a number of levels. However,
there's more than one way to skin this cat. Many people keep their DDL as
text in some external CMS, and just load it into the database again after
any change. If you write the view as "CREATE OR REPLACE VIEW ..." in your
DDL, this is easy, at least for cases where you're not changing the set of
columns provided by the view.

All right, so I'm getting the picture. Postgres developers are keeping
their code in larger scripts and committing them to databases as
batches, rather than editing them in place. We had a pretty slick
setup going with developers writing code against a live dev database,
with system documentation pulled from the SQL object comments, and the
ability to script the whole database to/from git when needed for
source control purposes.

Sounds pretty neat.

In some raw form, the workflow here is more like: write a module in a
script file that re-commits all its objects to the database, and then
execute that whole batch when testing or deploying. Commit that script
to git. If I want to keep my documentation scheme, I'll need to pull
the comments out of that file. A harder sell to my developers, but not
impossible.

I noticed some objects (like views) are very picky about dependents.
Do you drop all the module's objects at the beginning of the script,
just in case there's a change in the number or types of columns? That
seems tricky, especially considering there will be modules that depend
on yours.

You also mentioned an external CMS. Any suggestions?

I'm a big fan of git, but if you really want to keep things in-database
and track dependencies, etc, it occurs to me that you might be able to
use an actual table in the database to store the raw form of your view
definitions and then have an SP or something which can link up that
table to the actual views in the database and then you can look at
dependencies through the PG system catalogs...

Haven't really thought this through completely, but wanted to mention
the idea as it might help you.

Thanks,

Stephen

2013/10/22 Stephen Frost <sfrost@snowman.net>:

You also mentioned an external CMS. Any suggestions?

I'm a big fan of git, but if you really want to keep things in-database
and track dependencies, etc, it occurs to me that you might be able to
use an actual table in the database to store the raw form of your view
definitions and then have an SP or something which can link up that
table to the actual views in the database and then you can look at
dependencies through the PG system catalogs...

Haven't really thought this through completely, but wanted to mention
the idea as it might help you.

At my current workplace, view definitions (which are very old-school with
revision history etcetera recorded in comments) are stored in PL/PgSQL functions
which are updated and executed whenever views are modified. I'm looking at
adding a wrapper function to automatically detect and recreate dependencies,
not sure how well that would work. We keep track of schema changes via
pg_extractor[1]https://github.com/omniti-labs/pg_extractor which integrates quite nicely with GIT.

[1]: https://github.com/omniti-labs/pg_extractor

Regards

Ian Barwick

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general