Adrian Klaver <adrian.klaver@xxxxxxxxxxx> 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
