Re: How to keep format of views source code as entered?

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