Обсуждение: How to keep format of views source code as entered?
Hi!
Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a views source code is different from the code I entered. The code is formatted differently, comments are gone and e.g. all text constants got an explicit cast to ::text added. (see sample below).
I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to maintain my views SQL code outside of PostgreSQL views?
Any hints welcome!
Here is an example:
I enter this code to define a simple view:
create or replace view myview as
select
product_id,
product_acronym
from
products -- my comment here
where
product_acronym = 'ABC'
;
However, when I open the view my SQL client (DBeaver) again, this is what I get:
CREATE OR REPLACE VIEW myview
AS SELECT product_id,
product_acronym
FROM products
WHERE product_acronym = 'ABC'::text;
So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text constant 'ABC' changed to 'ABC'::text.
Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
> On Jan 7, 2021, at 08:19, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote: > I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to maintainmy views SQL code outside of PostgreSQL views? The text that you get back from the PostgreSQL system catalogs is based on the parsed version of the view definition, ratherthan the literal text you entered. Generally, you maintain your view definition separately in a source code controlsystem in its original form. -- -- Christophe Pettus xof@thebuild.com
"Markhof, Ingolf" <ingolf.markhof@de.verizon.com> writes:
> I want the SLQ code of my views stored as I entered it. Is there any way to achieve this?
No. Lots of people prefer to keep their SQL code in some sort of
source-code-control system, anyway.
regards, tom lane
So, it looks like PostgreSQL does support saving the original source code of a view.
What's best practise to use as a code repository?
I would expect support of multi-user access, access-right management and perhaps versioning as well…?
Thanks for your help!
Ingolf
From: Markhof, Ingolf [mailto:ingolf.markhof@de.verizon.com]
Sent: 07 January 2021 17:19
To: pgsql-general@lists.postgresql.org
Subject: [E] How to keep format of views source code as entered?
Hi!
Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a views source code is different from the code I entered. The code is formatted differently, comments are gone and e.g. all text constants got an explicit cast to ::text added. (see sample below).
I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to maintain my views SQL code outside of PostgreSQL views?
Any hints welcome!
Here is an example:
I enter this code to define a simple view:
create or replace view myview as
select
product_id,
product_acronym
from
products -- my comment here
where
product_acronym = 'ABC'
;
However, when I open the view my SQL client (DBeaver) again, this is what I get:
CREATE OR REPLACE VIEW myview
AS SELECT product_id,
product_acronym
FROM products
WHERE product_acronym = 'ABC'::text;
So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text constant 'ABC' changed to 'ABC'::text.
Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
Hello, > On Jan 7, 2021, at 09:33, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote: > > So, it looks like PostgreSQL does support saving the original source code of a view. To be clear, PostgreSQL itself does not. The suggestion is to use an external source code repository, such as GitHub, GitLab,or one of (many!) other tools or products to store the view definition. This has benefits besides just retaining the original source code, as you mention below: Version control, tracking, issuemanagement and commit merging, etc. -- -- Christophe Pettus xof@thebuild.com
On Thu, Jan 07, 2021 at 05:33:48PM +0000, "Markhof, Ingolf" <ingolf.markhof@de.verizon.com> wrote: > So, it looks like PostgreSQL does support saving the original source code of a view. > > What's best practise to use as a code repository? > > I would expect support of multi-user access, access-right management and perhaps versioning as well…? > > Thanks for your help! > > Ingolf Hi, I've only used stored functions (not views or triggers), and I have tools for auditing, loading, and dropping stored functions to match the code that is in git (so updates can be easily applied to multiple copies of the database), and Postgres has never altered the code that it stores, otherwise, auditing the code in the database against the code in git wouldn't work. But since postgres does store a possibly altered parsed version, you could alter your source to match Postgres's parsed version of it. Maybe I encountered this too long ago to remember having to adjust. For version control, I'd recommend using git, or whatever you are using for the rest of your code. For multi-user access rights management, I'm not sure. You can grant multiple users the right to create things in the database. See the documentation on the grant statement. e.g.: https://www.postgresql.org/docs/12/sql-grant.html I'm guessing that you want: grant create on database ... to ... But I don't know if it can be restricted to only creating views. If not, it might grant too much access. You'll also want to make sure that they all have write access to the same git repository where the views are. cheers, raf > From: Markhof, Ingolf [mailto:ingolf.markhof@de.verizon.com] > Sent: 07 January 2021 17:19 > To: pgsql-general@lists.postgresql.org > Subject: [E] How to keep format of views source code as entered? > > Hi! > > Switching from Oracle SLQ to PostgreSQL I am facing the issue that > the SQL code the system returns when I open a views source code is > different from the code I entered. The code is formatted differently, > comments are gone and e.g. all text constants got an explicit cast to > ::text added. (see sample below). > > I want the SLQ code of my views stored as I entered it. Is there any > way to achieve this? Or will I be forced to maintain my views SQL code > outside of PostgreSQL views? > > Any hints welcome! > > Here is an example: > > I enter this code to define a simple view: > > create or replace view myview as > select > product_id, > product_acronym > from > products -- my comment here > where > product_acronym = 'ABC' > ; > > However, when I open the view my SQL client (DBeaver) again, this is what I get: > > CREATE OR REPLACE VIEW myview > AS SELECT product_id, > product_acronym > FROM products > WHERE product_acronym = 'ABC'::text; > > So, the formatting changed, keywords are capitalized, the comment I > added in the from-part has gone and the text constant 'ABC' changed to > 'ABC'::text. > > Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer:Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
On Thu, Jan 7, 2021 at 4:38 PM raf <raf@raf.org> wrote:
Hi, I've only used stored functions (not views or
triggers),
Extrapolating to these other types of objects based upon experiences with functions isn't all that helpful.
and Postgres has never altered
the code that it stores,
Right, you use functions...
But since postgres does store a possibly altered parsed
version, you could alter your source to match
Postgres's parsed version of it. Maybe I encountered
this too long ago to remember having to adjust.
Now you've introduced PostgreSQL version dependency into the mix.
For version control, I'd recommend using git, or
whatever you are using for the rest of your code.
Yes, consider the original text as being official, not what is stored in the database. Don't allow changes to get pushed to the database unless driven from the source code.
For multi-user access rights management, I'm not sure.
You can grant multiple users the right to create things
in the database. See the documentation on the grant
statement. e.g.:
https://www.postgresql.org/docs/12/sql-grant.html
I'm guessing that you want:
grant create on database ... to ...
But I don't know if it can be restricted to only
creating views. If not, it might grant too much access.
It cannot.
You'll also want to make sure that they all have write
access to the same git repository where the views are.
Huh?
In short, one creates a function by writing:
CREATE FUNCTION ... $$ function body written as a text literal here $$ ...;
and a view:
CREATE VIEW AS SELECT ... (rest of a select statement here) ...;
The fact that a function is simply a body of text is why it is preserved - and generally does't get validated at the time the CREATE statement is executed, only when it is run. CREATE VIEW takes in a fully functioning select command, parses it, figures out its dependencies, and stores the components and meta-data. You get all this extra benefit at the cost of not retaining the original text.
Admittedly, the system probably should be made to save the text, should someone wish to write such a patch. Given the generally better-accepted version control and migration management method of maintaining one's database structure the need and desire to add such a capability to the core server is quite low.
David J.
Thanks for your comments and thoughts.
I am really surprised that PostgreSQL is unable to keep the source text of a view. Honestly, for me the looks like an implementation gap. Consider software development. You are writing code in C++ maybe on a UNIX host. And whenever you feed you source code into the compiler, it will delete it, keeping the resulting executable, only. And you could not even store your source code on the UNIX system. Instead, you'd be forced to do so in a separate system, like GitHub. Stupid, isn't it? Right. There are good reasons to store the source code on GitHub or alike anyhow. Especially when working on larger project and when collaborating with many people. But in case of rather small project with a few people only, this might be an overkill.
It shouldn't be rocket science to enable PostgreSQL to store the original source code as well. It's weird PostgreSQL is not doing it.
Regards,
Ingolf
Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
På fredag 08. januar 2021 kl. 09:38:29, skrev Markhof, Ingolf <ingolf.markhof@de.verizon.com>:
Thanks for your comments and thoughts.
I am really surprised that PostgreSQL is unable to keep the source text of a view. Honestly, for me the looks like an implementation gap. Consider software development. You are writing code in C++ maybe on a UNIX host. And whenever you feed you source code into the compiler, it will delete it, keeping the resulting executable, only. And you could not even store your source code on the UNIX system. Instead, you'd be forced to do so in a separate system, like GitHub. Stupid, isn't it? Right. There are good reasons to store the source code on GitHub or alike anyhow. Especially when working on larger project and when collaborating with many people. But in case of rather small project with a few people only, this might be an overkill.
It shouldn't be rocket science to enable PostgreSQL to store the original source code as well. It's weird PostgreSQL is not doing it.
It isn't rocket-science, of couse, but I'm pretty sure it is implemented like this on purpose. PG doesn't store queries you feed it either, nor any other command. It stores the resulting structure. SQL-scripts, containing DDL/DML should be versioned using scm like Git, not rely on the DB to store it.
--
Andreas Joseph Krogh
Am Fri, Jan 08, 2021 at 08:38:29AM +0000 schrieb Markhof, Ingolf: > I am really surprised that PostgreSQL is unable to keep the > source text of a view. Honestly, for me the looks like an > implementation gap. Consider software development. You are > writing code in C++ maybe on a UNIX host. And whenever you > feed you source code into the compiler, it will delete it, > keeping the resulting executable, only. You expect the compiler to keep your source code for you ? Most certainly, PostgreSQL does not delete your view source code, just as the compiler does. I am not so sure that analogy holds up. Karsten -- GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B
On Fri, 8 Jan 2021, Karsten Hilbert wrote: > Am Fri, Jan 08, 2021 at 08:38:29AM +0000 schrieb Markhof, Ingolf: > >> I am really surprised that PostgreSQL is unable to keep the >> source text of a view. Honestly, for me the looks like an >> implementation gap. Consider software development. You are >> writing code in C++ maybe on a UNIX host. And whenever you >> feed you source code into the compiler, it will delete it, >> keeping the resulting executable, only. > > You expect the compiler to keep your source code for you ? > > Most certainly, PostgreSQL does not delete your view source > code, just as the compiler does. > > I am not so sure that analogy holds up. > The SQL-92 standard requires the source text of a view to be held in order to provide the "information_schema" "view definition" column which "contains a representation of the view descriptors” - to me though it is open to interpretation what that actually means. MariaDB, like PostgreSQL, does not not store an exact copy of the view source either. The SQL-92 standard is completely explicit about column expansion: “NOTE 13: Any implicit column references that were contained in the <query expression> associated with the <view definition> are replaced by explicit column references in VIEW_DEFINITION.” - so any view definition that is stored, solely for the purposes of standard compliance, will at a minimum have to differ from the original source if the source had any implicit column references (and by association table references as well I assume). Arguably if PostgreSQL held an exact copy of the view definition (except for alterations under Note 13 above) then it should also store exact copies of other pre-parsed objects, such as DEFAULT on table columns and WITH on trigger clauses, in order to be useful under the OP's context. See also: http://www.postgresql-archive.org/idea-storing-view-source-in-system-catalogs-td1987401.html http://www.postgresql-archive.org/Preserving-the-source-code-of-views-td5775163.html -- The University of Edinburgh is a charitable body, registered in Scotland, with registration number SC005336.
"Markhof, Ingolf" <ingolf.markhof@de.verizon.com> writes:
> I am really surprised that PostgreSQL is unable to keep the source text
> of a view. Honestly, for me the looks like an implementation gap.
Perhaps, but the "gap" is wider than you seem to think. Consider
CREATE TABLE t1 (f1 int, f2 text);
CREATE VIEW v1 AS SELECT f2 FROM t1;
ALTER TABLE t1 RENAME COLUMN f2 TO zed;
\d+ v1
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+------+-----------+----------+---------+----------+-------------
f2 | text | | | | extended |
View definition:
SELECT t1.zed AS f2
FROM t1;
At this point the original text of the view is useless; with
another rename or two it could become downright misleading.
Another issue revolves around the fact that a textual SQL statement
is seldom totally unambiguous. In the above example, the fact that
"t1" refers to public.t1 and not some other t1 depends on the
search_path as it stood at CREATE VIEW time. If you change your
search_path you might need an explicit schema qualification.
The reverse-parsed view display accounts for that:
# set search_path = pg_catalog;
# \d+ public.v1
View "public.v1"
Column | Type | Collation | Nullable | Default | Storage | Description
--------+------+-----------+----------+---------+----------+-------------
f2 | text | | | | extended |
View definition:
SELECT t1.zed AS f2
FROM public.t1;
but a static source text could not. In PG this hazard applies to
functions and operators not only tables. If pg_dump regurgitated
the original view text, there would be trivially-exploitable
security holes that allow some other user to take control of your
view after a dump/reload.
We actually used to store both text and parsed versions of some
sub-constructs, such as CHECK constraints and column default values.
We got rid of the text versions because there was no reasonable way
to keep them up-to-date. (And, AFAIR, there hasn't been a lot of
push-back about those catalog columns disappearing.) So I don't
think we'd accept a patch to store the text form of a view, unless
some solution to these issues were provided.
regards, tom lane
On 1/8/21 12:38 AM, Markhof, Ingolf wrote:
> Thanks for your comments and thoughts.
>
> I am really surprised that PostgreSQL is unable to keep the source text
> of a view. Honestly, for me the looks like an implementation gap.
> Consider software development. You are writing code in C++ maybe on a
> UNIX host. And whenever you feed you source code into the compiler, it
> will delete it, keeping the resulting executable, only. And you could
> not even store your source code on the UNIX system. Instead, you'd be
> forced to do so in a separate system, like GitHub. Stupid, isn't it?
> Right. There are good reasons to store the source code on GitHub or
> alike anyhow. Especially when working on larger project and when
> collaborating with many people. But in case of rather small project with
> a few people only, this might be an overkill.
The projects I work on are my own personal ones and I find an
independent version control solution the way to go for the following
reasons:
1) It is easy.
a) mkdir project_src
b) cd project_src
c) git init
Now you are set.
2) Even my simple projects generally have multiple layers.
a) Database
b) Middleware
c) UI
And also multiple languages. It makes sense to me to keep all
that information in one repo then having each layer operate independently.
3) It allows me to work on test and production code without stepping on
each other.
4) It serves as an aid to memory. Answers the question; What was I
thinking when I did that? More important it helps anyone else that might
have to deal with the code.
FYI, the program I use to manage database changes is
Sqitch(https://sqitch.org/).
>
> It shouldn't be rocket science to enable PostgreSQL to store the
> original source code as well. It's weird PostgreSQL is not doing it.
>
> Regards,
>
> Ingolf
>
>
> *Verizon Deutschland GmbH* - Sebrathweg 20, 44149 Dortmund, Germany -
> Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig -
> Vorsitzender des Aufsichtsrats: Francesco de Maio
>
--
Adrian Klaver
adrian.klaver@aklaver.com
On Fri, Jan 08, 2021 at 08:38:29AM +0000, "Markhof, Ingolf" <ingolf.markhof@de.verizon.com> wrote: > Thanks for your comments and thoughts. > [...] > And you could not even store your source code on the UNIX > system. Instead, you'd be forced to do so in a separate system, like > GitHub. Stupid, isn't it? Right. > [...] > Regards, > Ingolf I don't think your conclusions are correct. There is nothing that can stop you from "storing your source code on the UNIX system". You don't have to use github. But even if you do you github, you would first need to store your source code on a file system, so that a local git repository could see it, and push it to github. You don't even have to use git if you really don't want to. cheers, raf
Adrian Klaver <adrian.klaver@aklaver.com> writes: > On 1/8/21 12:38 AM, Markhof, Ingolf wrote: >> Thanks for your comments and thoughts. >> >> I am really surprised that PostgreSQL is unable to keep the source text >> of a view. Honestly, for me the looks like an implementation gap. >> Consider software development. You are writing code in C++ maybe on a >> UNIX host. And whenever you feed you source code into the compiler, it >> will delete it, keeping the resulting executable, only. And you could >> not even store your source code on the UNIX system. Instead, you'd be >> forced to do so in a separate system, like GitHub. Stupid, isn't it? >> Right. There are good reasons to store the source code on GitHub or >> alike anyhow. Especially when working on larger project and when >> collaborating with many people. But in case of rather small project with >> a few people only, this might be an overkill. > > The projects I work on are my own personal ones and I find an > independent version control solution the way to go for the following > reasons: > > 1) It is easy. > a) mkdir project_src > b) cd project_src > c) git init > Now you are set. > > 2) Even my simple projects generally have multiple layers. > a) Database > b) Middleware > c) UI > And also multiple languages. It makes sense to me to keep all > that information in one repo then having each layer operate independently. > > 3) It allows me to work on test and production code without stepping on > each other. > > 4) It serves as an aid to memory. Answers the question; What was I > thinking when I did that? More important it helps anyone else that might > have to deal with the code. > > FYI, the program I use to manage database changes is > Sqitch(https://sqitch.org/). > >> This is essentially my workflow as well. I have even used sqitch too. While this has worked well for my projects, attempts to introduce the discipline necessary to use such a workflow in a team has largely failed. This seems to be due to 2 main reasons - 1. Lack of SCCM support built into common tools. There are very few tools which have version control support built in (I believe the jet brains product does). In particular, pgAdmin would benefit here (maybe pgadmin4 does, I've not tried it in a few years). 2. Poor SCCM and DBMS Understanding. Despite it being 2021 and both version control and databases being two very common technologies you need to interact with as a developer, I'm still surprised at how poorly many developers understand these tools. I still frequently come across really bad workflows and practices with version control and code which uses the database as little more than a bit bucket, which re-implement searching and sorting at the client level (and then often moan about poor performance issues). My editor has good support for psql and psql has always been my goto tool for PG. As my editor also has good git support, my workflow works well. However, most people I've worked with prefer things like pgadmin. Tom Lane responded in this thread to point out some of the complexities which make it difficult to maintain current code source within the database itself. This is definitely something which should be kept in version control. The problem is, if your tool does not support the version control system, it is too easy to forget/bypass that stage. When you use something like pgadmin, it is far too easy to modify the source definitions in the database without ever updating the sources on disk in the version control working directory and the changes get lost. The other big challenge is dependency management. Keeping track of what is affected by a change to a table definition can be a challenge within a complex system. I've yet to find a good solution to that issue. It is probably something which needs to be built into a tool. In the past, I've used a modified sqitch approach that also maintains a small 'dbadm' schema containing metadata to track dependencies. Although this worked OK, especially if you understood how all the bits fit together, it still had many corner cases and to some extent highlighted the complexities involved. -- Tim Cross
Tom, all, when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table usingthe old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting inmy context, i.e. my schema. So it is perfectly clear what table I am referring to. Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an dataanalyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new requirements.Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required. What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I finally needto copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver editor windowand copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced collaborationfeatures which I don't really need. At the price that code transfer from SQL (DBeaver) to the code repositoryand vice versa is complete manually?! This doesn't really look like an enhancement. Most likely, there are more professional ways to do that. I'd be glad to get advice. What I would like to have is something that would automatically update the SQL code in the software repository when I runa CREATE OR REPLACE VIEW. Ingolf Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: DetlefEppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
> On Jan 9, 2021, at 06:22, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote: > What I would like to have is something that would automatically update the SQL code in the software repository when I runa CREATE OR REPLACE VIEW. I think you are approaching this backwards. The SQL in the repository should be the definitive version. If you wish tochange the view, you change the CREATE OR REPLACE VIEW command that you have stored in the repository, and then apply thatto the database so it now has the new view definition. You may not, in a small independent project, feel the need for a source code repository, but it becomes very useful veryquickly. -- -- Christophe Pettus xof@thebuild.com
On Sat, Jan 9, 2021 at 9:22 AM Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
Tom, all,
when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table using the old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting in my context, i.e. my schema. So it is perfectly clear what table I am referring to.
This highlights two major differences between Oracle and Postgres. Postgres will never allow you to make an invalid view. Also, the search_path in Postgres acts quite differently from the user context in Oracle. There is no guarantee that a user has a schema in postgres or that the schema would be in the search_path.
Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an data analyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new requirements. Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required.
What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I finally need to copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver editor window and copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced collaboration features which I don't really need. At the price that code transfer from SQL (DBeaver) to the code repository and vice versa is complete manually?! This doesn't really look like an enhancement.
Most likely, there are more professional ways to do that. I'd be glad to get advice.
What I would like to have is something that would automatically update the SQL code in the software repository when I run a CREATE OR REPLACE VIEW.
If you want to use source control (and I think it's a good idea), look into something like flywaydb or liquibase or any of the many other db schema control frameworks.
Ingolf
Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
Markhof, Ingolf <ingolf.markhof@de.verizon.com> writes: > Tom, all, > > when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL client) marks views that refer to the table usingthe old column name as erroneous. So, I can easily identify these cases. And of course I, as a user, I am acting inmy context, i.e. my schema. So it is perfectly clear what table I am referring to. > > Please note: I am not developing any PL/SQL code. I don't have big development projects. I have more the role of an dataanalyst. I just create rather complex SQL queries which, from time to time, may need to be adopted to some new requirements.Or peers want to (re-) use (part of) my SQL queries. There is not really much versioning required. > > What I understood so far is: I can use e.g. DBeaver to interact with PostgreSQL, to develop my SQL code. But I finallyneed to copy the SQL code into e.g. Github. Which is a manual process. I'd mark the SQL code in the DBeaver editorwindow and copy&paste it into some file in e.g. GitHub. Using Github, I'd get version control and other enhanced collaborationfeatures which I don't really need. At the price that code transfer from SQL (DBeaver) to the code repositoryand vice versa is complete manually?! This doesn't really look like an enhancement. > > Most likely, there are more professional ways to do that. I'd be glad to get advice. > > What I would like to have is something that would automatically update the SQL code in the software repository when I runa CREATE OR REPLACE VIEW. > Even with Oracle, I found it works much better to keep all your DDL/DML in files stored within the OS file system. This approach also works fine with tools like DBeaver, pgAdmin, etc as all of these files also support working with files. The main benefits I find with this approach are - 1. Adding version control is easy. Doesn't matter if it is git, hg, bzr, svn or rcs - any version control system works fine. It doesn't have to be a cloud service like github, though some sort of centralised repository can be useful for managing things like backups and sharing code across a team (I've used gitlab hosted locally t great success). Most editors also have built-in support for common version control systems, so the additional overhead associated with using a version control system is very little. 2. Having all your code in version control makes tracking changes trivial. This is often really useful in tracking down problems/bugs caused by a change and other diagnostics. More than once, I have found I've gone down a bad path of changes and want to restore a previous version. Assuming you use your version control system appropriately, this becomes trivial. If your code is only in the db, once you make changes, the old code is gone and cannot easily be restored. 3. Having all the DDL/DML in files makes data migration very simple. I will typically have a development environment where I develop my DDL/DM which is separate from the production environment. This can be very important even in data analysis and data mining type applications as it allows you to develop complex and possibly resource hungry DML in an environment where mistakes won't impact production systems. It also means you can have a dev environment which is populated with specific data sets which have been defined to help in the development process e.g. perhaps smaller, so tests run faster or perhaps ensuring all possible data permutations are included etc. If all your DDL/DML are in files, seting up a new environment is as simple as writing a basic script and using psql (pg) or sqlplus (oracle) to load the DDL/DML. In simpler environments, you can even use a naming scheme for the files which sets the order - loading the data then becomes as easy as 'psql *', avoiding the need to write scripts (even though writing the scripts is typically trivial). 4. Having all your DDL/DML in files allows you to use the many powerful text manipulation tools which exist on most platforms. While such tools are not often required, when they are, it can be a real bonus. Being able to use tools like sed, awk, perl, etc have saved my bacon more than once. Over the years, I have also built up a very useful library of techniques, templates etc. Being able to quickly and easily access this library is very useful. 5. While the built in editors in tools like DBeaver and pgAdmin are OK, I find they are rarely as good as my preferred editor and I often get frustrated at having to know/learn the editors of different tools. Having everything based on files means I can use my preferred editor, which has support for things like completion, familiar syntax highlighting and key bindings, templates etc. These days, many editors even have built-in support for popular databases like pgsql and oracle, so you can load the code and run it without having to leave your editor. Having the code in the database can be useful. I've used this in oracle to provide enhanced debugging support when developing stored procedures and packages and enhanced reporting in unit testing. However, you really don't want this to be the definitive master for your source code. Your source code benefits from being in a location which makes backup, restoration and migration easy/fast, where changes can be tracked, analysed and rolled back, where re-factoring can use advanced tools and can work across projects, not just on a single script, where code sharing is easy and where people can use their preferred tool rather than being forced to use something which understands the database. -- Tim Cross
On Sat, Jan 09, 2021 at 02:22:25PM +0000, "Markhof, Ingolf" <ingolf.markhof@de.verizon.com> wrote:
> Tom, all,
>
> when I change a tables column name in Oracle SQL , SQLdeveloper (a SQL
> client) marks views that refer to the table using the old column name
> as erroneous. So, I can easily identify these cases. And of course
> I, as a user, I am acting in my context, i.e. my schema. So it is
> perfectly clear what table I am referring to.
>
> Please note: I am not developing any PL/SQL code. I don't have big
> development projects. I have more the role of an data analyst. I
> just create rather complex SQL queries which, from time to time, may
> need to be adopted to some new requirements. Or peers want to (re-)
> use (part of) my SQL queries. There is not really much versioning
> required.
>
> What I understood so far is: I can use e.g. DBeaver to interact with
> PostgreSQL, to develop my SQL code. But I finally need to copy the SQL
> code into e.g. Github. Which is a manual process. I'd mark the SQL
> code in the DBeaver editor window and copy&paste it into some file in
> e.g. GitHub. Using Github, I'd get version control and other enhanced
> collaboration features which I don't really need. At the price that
> code transfer from SQL (DBeaver) to the code repository and vice versa
> is complete manually?! This doesn't really look like an enhancement.
>
> Most likely, there are more professional ways to do that. I'd be glad
> to get advice.
>
> What I would like to have is something that would automatically update
> the SQL code in the software repository when I run a CREATE OR REPLACE
> VIEW.
>
> Ingolf
Hi,
If there is a software repository, then I would
recommend considering not using tools like DBeaver to
develop your SQL views. Instead, develop them in
relation with the repository tools, and use psql or
similar to load the view into the database(s).
Alternatively, if you do modify the views "live" in the
databse, get (or have someone create) a tool to fetch
the code of the view from the database, and write it to
a file that can be committed into the repository.
It doesn't have to be copy and paste. A simple program
can be written to extract view source code and write it
to a file. Perhaps your colleagues that want to re-use
your source code can implement it. Such a tool would be
useful with or without a repository.
Here's an example of such a query but it's for
procedures/functions, and would need to be
very different for views.
select
p.proname, -- name
p.proretset, -- returns setof?
p.proisstrict, -- strict 't' or 'f'
p.provolatile, -- volatile or stable 'v' or 's'
p.prosecdef, -- security definer 't' or 'f'
p.pronargs, -- number of in arguments
p.prorettype, -- return type
p.proargtypes, -- space-separated list of in arg types
p.proallargtypes, -- array of in/out arg types (iff there are out args)
p.proargmodes, -- array of in/out arg modes like {i,o,o} (iff there are out args)
p.proargnames, -- array of in/out arg names like {id,code,name}
p.prosrc, -- source code
cast(cast(p.oid as regprocedure) as text) -- nice signature
from
pg_user u,
pg_proc p
where
u.usename = current_user and
p.proowner = u.usesysid and
p.proname like 'myfunc_%' -- Your naming convention
order by
p.proname
The above returns enough information to construct a
corresponding create function statement (except for any
knowledge of precision and scale of numeric parameters).
Actually, I just had a look at the pg_views system
catalog where the source code for views is stored, and
it doesn't seem to contain enough information to
reconstruct a create view statement. It only contains
these columns:
schemaname
viewname
viewowner
definition
But definition is just the query itself.
There is no list of column names (like there is with
procedures in pg_proc).
You can tell the difference between a temporary and
non-temporary view because the schemaname is different
for temporary views (e.g. pg_temp_3, rather than
public).
I don't know if you could tell whether a view is
recursive or not.
And it doesn't look like you can determine if a view
has a local or cascaded check_option parameter, or the
security_barrier parameter.
Is all of that information stored somewhere else in the
system catalogs?
Without them, this query would only find the names and
query code of views:
select
v.viewname,
v.definition
from
pg_views v
where
v.viewname like 'myview_%'; -- Your naming convention
Is there a query that can be used to obtain all of the
information needed to reconstruct the create view
statement that corresponds to a view in pg_views?
cheers,
raf
On Saturday, January 9, 2021, raf <raf@raf.org> wrote:
Actually, I just had a look at the pg_views system
catalog where the source code for views is stored, and
it doesn't seem to contain enough information to
reconstruct a create view statement. It only contains
these columns:
schemaname
viewname
viewowner
definition
But definition is just the query itself.
There is no list of column names (like there is with
procedures in pg_proc).
Is all of that information stored somewhere else in the
system catalogs?
Views are relation-like and thus are primarily recorded on pg_class.
David J.
On Sat, Jan 09, 2021 at 05:26:04PM -0700, "David G. Johnston" <david.g.johnston@gmail.com> wrote:
> On Saturday, January 9, 2021, raf <raf@raf.org> wrote:
>
> > Actually, I just had a look at the pg_views system
> > catalog where the source code for views is stored, and
> > it doesn't seem to contain enough information to
> > reconstruct a create view statement. It only contains
> > these columns:
> >
> > schemaname
> > viewname
> > viewowner
> > definition
> >
> > But definition is just the query itself.
> >
> > There is no list of column names (like there is with
> > procedures in pg_proc).
> >
> > Is all of that information stored somewhere else in the
> > system catalogs?
> >
>
> Views are relation-like and thus are primarily recorded on pg_class.
>
> David J.
Hi David,
Thanks. That's very helpful. The following query should
return enough information to re-construct create view
statements:
select
c.relname as "view_name",
a.attname as "column_name",
format_type(a.atttypid, a.atttypmod) as "column_type",
v.definition as "view_sourcecode",
c.reloptions as "view_options"
from
pg_class c,
pg_attribute a,
pg_views v
where
c.relkind = 'v' and
c.relname like 'myview%' and -- Your naming convention
a.attrelid = c.oid and
v.viewname = c.relname
order by
c.relname,
a.attnum;
Note that view_options can look like
"{check_option=local,security_barrier=false}".
Also, this won't find temporary views (I'm probably not
looking in the right place for them).
Also, it probably doesn't handle recursive views.
But for simple views, it should make a basis for
extracting views into files that can be added to a
source code repository.
I've attached a Python script that can output "create
or replace view" statements for the views in a database
that match a particular naming convention.
Note that:
- Login details are hard-coded and must be changed.
- View naming convention is hard-coded and must be changed.
- It doesn't handle temporary or recursive views.
- It does handle check_option and security_barrier options.
- All views are output to stdout, not separate files.
- Comments are not included (code is in post-parse state).
- It should probably drop then create each view.
I hope it's useful to show how this could be done, but
the loss of comments would bother me. I'd want the
documentation in the source code repository. Having
access to the original source would be much nicer (and
enable auditing views in the database against the views
in the source code repository to see if they've been
changed).
cheers,
raf
Вложения
Hi Ingolf,
For comments in views, I create a unused CTE and put my comments there, e.g.
WITH v_comments AS (
SELECT 'this is my comment' AS comment
)
Alex
Sent with ProtonMail Secure Email.
‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote:
Hi!
Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a views source code is different from the code I entered. The code is formatted differently, comments are gone and e.g. all text constants got an explicit cast to ::text added. (see sample below).
I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to maintain my views SQL code outside of PostgreSQL views?
Any hints welcome!
Here is an example:
I enter this code to define a simple view:
create or replace view myview as
select
product_id,
product_acronym
from
products -- my comment here
where
product_acronym = 'ABC'
;
However, when I open the view my SQL client (DBeaver) again, this is what I get:
CREATE OR REPLACE VIEW myview
AS SELECT product_id,
product_acronym
FROM products
WHERE product_acronym = 'ABC'::text;
So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text constant 'ABC' changed to 'ABC'::text.
Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
> On 12 Jan 2021, at 20:54, Alex Williams <valenceshell@protonmail.com> wrote: > > Hi Ingolf, > > For comments in views, I create a unused CTE and put my comments there, e.g. > > WITH v_comments AS ( > SELECT 'this is my comment' AS comment > ) > > Alex You do know about COMMENT ON VIEW v_comments IS ’this is my comment’, right? > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote: > >> Hi! >> >> >> >> Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a viewssource code is different from the code I entered. The code is formatted differently, comments are gone and e.g. alltext constants got an explicit cast to ::text added. (see sample below). >> >> >> >> I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to maintainmy views SQL code outside of PostgreSQL views? >> >> >> >> Any hints welcome! >> >> >> >> Here is an example: >> >> >> >> I enter this code to define a simple view: >> >> >> >> create or replace view myview as >> >> select >> >> product_id, >> >> product_acronym >> >> from >> >> products -- my comment here >> >> where >> >> product_acronym = 'ABC' >> >> ; >> >> >> >> However, when I open the view my SQL client (DBeaver) again, this is what I get: >> >> >> >> CREATE OR REPLACE VIEW myview >> >> AS SELECT product_id, >> >> product_acronym >> >> FROM products >> >> WHERE product_acronym = 'ABC'::text; >> >> >> >> So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text constant'ABC' changed to 'ABC'::text. >> >> >> >> >> >> >> >> >> >> >> Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer:Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio >> > Alban Hertroys -- There is always an exception to always.
Ugh, I wasn't and just tried it, thanks. I've saved comments before on pgadmin and wasn't aware it using a sql statement to save it, I thought it was local to myenvironment. Sent with ProtonMail Secure Email. ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ On Tuesday, January 12, 2021 4:17 PM, Alban Hertroys <haramrae@gmail.com> wrote: > > On 12 Jan 2021, at 20:54, Alex Williams valenceshell@protonmail.com wrote: > > Hi Ingolf, > > For comments in views, I create a unused CTE and put my comments there, e.g. > > WITH v_comments AS ( > > SELECT 'this is my comment' AS comment > > ) > > Alex > > You do know about COMMENT ON VIEW v_comments IS ’this is my comment’, right? > > > ‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐ > > On Thursday, January 7, 2021 11:19 AM, Markhof, Ingolf ingolf.markhof@de.verizon.com wrote: > > > > > Hi! > > > Switching from Oracle SLQ to PostgreSQL I am facing the issue that the SQL code the system returns when I open a viewssource code is different from the code I entered. The code is formatted differently, comments are gone and e.g. alltext constants got an explicit cast to ::text added. (see sample below). > > > I want the SLQ code of my views stored as I entered it. Is there any way to achieve this? Or will I be forced to maintainmy views SQL code outside of PostgreSQL views? > > > Any hints welcome! > > > Here is an example: > > > I enter this code to define a simple view: > > > create or replace view myview as > > > select > > > product_id, > > > product_acronym > > > from > > > products -- my comment here > > > where > > > product_acronym = 'ABC' > > > ; > > > However, when I open the view my SQL client (DBeaver) again, this is what I get: > > > CREATE OR REPLACE VIEW myview > > > AS SELECT product_id, > > > > > > product_acronym > > > > > > > > > FROM products > > > WHERE product_acronym = 'ABC'::text; > > > So, the formatting changed, keywords are capitalized, the comment I added in the from-part has gone and the text constant'ABC' changed to 'ABC'::text. > > > Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer:Detlef Eppig - Vorsitzender des Aufsichtsrats: Francesco de Maio > > Alban Hertroys > > ----------------- > > There is always an exception to always.
> Admittedly, the system probably should be made to save the text, should someone wish to write such a patch.
Just wanted to throw $0.02 behind this idea if anyone does want to take it up later. Using a source control system is better obviously. But even if you use source control it is still incredibly annoying you cannot compare the view you have in source control to the view definition in PG and tell if it's the same or changed. It has been a major annoyance for views with complex subqueries or where clauses, the PG representation is absolutely unreadable.
-Adam
On Wed, 2021-01-13 at 20:39 -0500, Adam Brusselback wrote: > > Admittedly, the system probably should be made to save the text, should someone wish to write such a patch. > > It has been a major annoyance for views with complex subqueries or where clauses, the PG representation is absolutely unreadable. This is not going to happen, and I dare say that such a patch would be rejected. Since PostgreSQL stores view definitions in their parsed form, the query does not contain the name of the used objects, but only their object ID. This allows you for example to rename the underlying objects, because that does not change the object ID: CREATE TABLE t (id integer); CREATE VIEW v AS SELECT * FROM t; \d+ v [...] View definition: SELECT t.id FROM t; ALTER TABLE t RENAME TO quaxi; \d+ v [...] View definition: SELECT quaxi.id FROM quaxi; If PostgreSQL were to store the original text, either that text would become wrong, or you would have to forbid renaming of anything that is referenced by a view. A database is no source versioning system. The next thing someone will request is that the original CREATE TABLE or CREATE INDEX statements should be preserved. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
On Wed, Jan 13, 2021 at 6:40 PM Adam Brusselback <adambrusselback@gmail.com> wrote:
It has been a major annoyance for views with complex subqueries or where clauses, the PG representation is absolutely unreadable.
The path to a solution here is to write a schema-to-text system that presents the derived output in a more human-friendly way instead of a machine-readable way. Or maybe write a formatter that takes the supposedly unreadable output and does stuff like "change 'character varying' to 'text'".
In any case, though, what you can do is install the view in source into a database, dump both, compare both (same version of PG), and decide whether the database version is different from the source control version and, if so, decide how to update the database. It's a view, it's not like you get to do "alter" incremental changes anyway.
Or hold your nose and adopt a path of least resistance - accept what is easy to accomplish and be glad you aren't writing more user-friendly stuff, but that is only cosmetically different, yourself. Learning what the canonical outputs mean is annoying but not hard, especially if you do have an original human-readable document to answer questions.
David J.
Hi Laurenz, > On 14. Jan, 2021, at 04:59, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > If PostgreSQL were to store the original text, either that text would become > wrong, or you would have to forbid renaming of anything that is referenced > by a view. this is why views, procedures, functions and packages can become invalid in Oracle, which I really hate because as a DBA,it's almost impossible to quickly see (or in fact see at all) why this happens, or having to debug applications thatyou don't know and/or can't fix anyway. Oracle's invalid object concept doesn't make sense. So, I'm not at all in favor of saving the original statement text. Cheers, Paul
Hi! Today, I made an astonishing / disappointing experience related to that source code topic: You may not be able to delete tables / views that are referenced by other users objects, e.g. views. Unless you add the CASCADE option which will cause all depending views to be deleted as well. And the CASCASE will work and delete the other users view even when you don't have the permission to drop that other users view! Looks like the Oracle concept of marking views as invalid makes some sense... Regards, Ingolf -----Original Message----- From: Paul Förster [mailto:paul.foerster@gmail.com] Sent: 14 January 2021 07:16 To: Cybertec Schönig & Schönig GmbH <laurenz.albe@cybertec.at> Cc: Adam Brusselback <adambrusselback@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; raf <raf@raf.org>; pgsql-generallists.postgresql.org<pgsql-general@lists.postgresql.org> Subject: [E] Re: How to keep format of views source code as entered? Hi Laurenz, > On 14. Jan, 2021, at 04:59, Laurenz Albe <laurenz.albe@cybertec.at> wrote: > > If PostgreSQL were to store the original text, either that text would become > wrong, or you would have to forbid renaming of anything that is referenced > by a view. this is why views, procedures, functions and packages can become invalid in Oracle, which I really hate because as a DBA,it's almost impossible to quickly see (or in fact see at all) why this happens, or having to debug applications thatyou don't know and/or can't fix anyway. Oracle's invalid object concept doesn't make sense. So, I'm not at all in favor of saving the original statement text. Cheers, Paul Verizon Deutschland GmbH - Sebrathweg 20, 44149 Dortmund, Germany - Amtsgericht Dortmund, HRB 14952 - Geschäftsführer: DetlefEppig - Vorsitzender des Aufsichtsrats: Francesco de Maio
Hi Ingolf, > On 26. Jan, 2021, at 14:41, Markhof, Ingolf <ingolf.markhof@de.verizon.com> wrote: > > You may not be able to delete tables / views that are referenced by other users objects, e.g. views. Unless you add theCASCADE option which will cause all depending views to be deleted as well. And the CASCASE will work and delete the otherusers view even when you don't have the permission to drop that other users view! > > Looks like the Oracle concept of marking views as invalid makes some sense... yes, PostgreSQL's and Oracle's approaches IMHO both make perfect sense in their own way, depending on your design philosophy.I never said otherwise. I just said that I hate to debug invalid objects in Oracle because Oracle does not clearlyshow dependencies and reading source code can be hard, especially if the author was one of those genius generatorswhich produce tons of code. Cheers, Paul