Thread: Preserving the source code of views
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
On 20/10/13 16:38, Brian Crowell wrote:
The 'Real Experts' may well have more practically elegant solutions, but a couple possibilities I can think of are as follows: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
- You have access to the source of PostgreSQL, so you could modify the source code to preserve the source code of the views.
- 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>
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)
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;
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>
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
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
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
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
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,
* 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
Attachment
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] which integrates quite nicely with GIT. [1] https://github.com/omniti-labs/pg_extractor Regards Ian Barwick