Re: Interacting with PostgreSQL

[Jean Louis]

* Tim Landscheidt <tim@tim-landscheidt.de> [2020-11-30 06:18]:
> Jean Louis <bugs@gnu.support> wrote:
> 
> >> >> The problem is that you do not need to consciously use auto-
> >> >> commit mode, but that psql automatically reverts to it when
> >> >> you rollback or commit a transaction:
> 
> >> >> | tim=> BEGIN WORK;
> >> >> | BEGIN
> >> >> | tim=> INSERT INTO t (ID) VALUES (1);
> >> >> | INSERT 0 1
> >> >> | tim=> ROLLBACK WORK;
> >> >> | ROLLBACK
> >> >> | tim=> INSERT INTO t (ID) VALUES (1);
> >> >> | INSERT 0 1
> >> >> | tim=> -- The row has been committed.
> 
> >> > I understand. I always used it manually and will rather continue. Just
> >> > observing how you do it.
> 
> >> Eh, that /is/ the behaviour using it manually, either on the
> >> command line or via sql-postgres.
> 
> > I understand what you mean. Without BEGIN I am in autocommit
> > mode. That is standard. But why is it problem for you?
> 
> Because if I make a mistake in auto-commit mode, potentially
> all data can be changed or lost.  Therefore I want to use a
> transaction wherever possible so that I can verify the ef-
> fects of a query before committing it.

That is very understandable. And I guess PostgreSQL users will use
BEGIN; and COMMIT; at all sensitive transactions.

My side here there are cases where BEGIN; COMMIT; are not needed and
this may be due to my work flow:

- all SQL is prepared in the file.

- I invoke SQL that has been written in the file, not one written with
  psql command line tool on command line, which I use for other
  purposes

- I am normally creating tables, views and triggers

- If something is wrong with table, it is not created

- If table is created but I forgot proper column name, I can still
  correct it with alter

- If anything major is wrong with table or view, it is new at creation
  time, so I do not mind and I can as well DROP TABLE and start over
  again

- Once satisfied table is working for years, sometimes it gets new
  columns

Within `psql' on command line I am normally inspecting table and
sometimes entering some values. My workflow differs.

When working within buffer with Emacs Lisp, I have something like:

(rcd-sql-begin)
(rcd-sql-do-some-deletion)

and after I am verifying the tables and when satisifed:

(rcd-sql-commit)

Working within Emacs Lisp buffer with SQL is somehow better as I can
work on database while program is being executed. I can revert with
(rcd-sql-commit) stuff related to local buffer.

> > Emacs HyperScope is dynamic knowledge repository that augments
> > knowledge, relates it together and serves as dynamic knowledge
> > repository that follows the technology template project for open
> > hyperdocument systems by Doug Engelbart, definitely similar in its
> > nature to first Javascript based HyperScope. This one is for Emacs.
> 
> > About Dynamic Knowledge Repositories (DKR)
> > https://www.dougengelbart.org/content/view/190/163/
> 
> > TECHNOLOGY TEMPLATE PROJECT OHS Framework
> > https://www.dougengelbart.org/content/view/110/460/
> 
> I know.  So why use that and not Org mode?

One good example is that Org files were meant to be plain text but
today they are not any more, they are text with a lot of structured
information and Org Emacs Lisp programs try to solve the problem by
parsing text.

https://www.dougengelbart.org/content/view/110/460/#2a1a

,----
| Elementary Objects
| 
| Objects are basic content packets of an arbitrary, user and developer
| extensible nature. Types of elementary objects could contain text,
| graphics, equations, tables, spreadsheets, canned-images, video,
| sound, code elements, etc.
`----

I am editing hyperdocuments on a meta level that then later may become
Org documents. Org allows me to insert let us say image, but there is
harder work and more effort to assign that image specific hyperlink.

Org type of a node is normally heading and such have its ambiguous
identifier such as heading name and then it requires users to decide
to make it non-ambiguous by providing either the Org ID or CUSTOM_ID
or some other identifier. In the end I have 50 duplicates minimum on
my system as headings need to be copied and the built-in copy function
C-c C-x M-w does not have and clue what is unique ID, or Org ID or
CUSTOM_ID in other words does not grant any of them their true meaning
and copies the ID even though it is specialized Org based function and
not just Emacs kill.

With few Org files it is manageable, with growing complexity of data
is becomes time wasting machine.

When using Org I can have elementary objects as heading, but in vague
manner. If I change the heading but do not have unique ID it becomes
something else, the reference to heading is lost and hyperlink may not
work. If I use some hyperlink to unique ID, then duplicates are
disturbing.

Org file does not have headings or paragraphs or list items as
elementary objects.

Example of elementary object is this hyperlink below that gives
reference to specific paragraph 2a1a:

https://www.dougengelbart.org/content/view/110/460/#2a1a

Database backed object may have by decision its unique ID about which
user never need to think later and there are no duplicates for
decades. As PostgreSQL user you know what I mean.

- Org file may include graphics but again graphics object is not
  uniquely identified and user has to think of it.

  If I wish to include graphics object I can, and it is uniquely
  identified even if I rename it or displace it, or put somewhere else
  in hierarchy or make symlink to graphics object, it remains there
  for ever for referencing

Same for graphics, equations, tables, spreadsheets, canned-images,
video, sound, code elements, etc.

In a hyperdocument I can put Gnumeric spreadsheet. In Org file I can
only hyperlink to external Gnumeric spreadsheet or use the built-in
Org table features.

Hyperdocument creation does not let user think about it, it should be
maybe one key press to create hyperdocument.

Org mode assumes that users are there to collect their pieces of
hyperlinks and hyperdocuments together. That degrades sharing
capability. 

https://www.dougengelbart.org/content/view/110/460/#2a1a

,----
|  Mixed-Object Documents
|  
|  Documents are a coherent entity made up of an arbitrary mix of
|  elementary objects bundled within a common "envelope" to be stored,
|  transmitted, read, printed, or otherwise be operated on. 2a1b1
| 
|  the MIME specification is an example of a document definition based
|  on a higher level collection of elementary objects.  2a1b2
`----

When elementary objects have been defined then a mixed-object document
can be defined. It may consist of anything. Libreoffice Spreadsheets
and spreadsheets in general, then also ODT files and packages such as
TAR, or email files may be similar to mixed-object document. Yet they
need not offer clear overview of what is inside and no meanings or
relations connecting the dots.

Org file is not mixed-object document, it is rather in itself
elementary object.

Imagine collection of PDF files, and there are 10000 references to
specific articles in PDF files tagged with specific human objects such
as cup, flower, silk and similar. Finding set of articles relating to
flower becomes tedious task. It is useful in creation of art. As that
is exactly how I have found need to create Hyperscope as dynamic
knowledge repository.

The joy of having quick access to PDF specific articles that may be
just 1/3 large on a page is great. No time spending.

One good benefit streamlines quicker location of any node or
elementary object and speed of access to such elementary object.

Enter object once. Never again construct Org hyperlinks by hand. That
principle is used by some features of Org mode but is not well
integrated yet. So people construct hyperlinks by tedious {C-c C-l}
repetition and that is not scalable.

Let us say I wish to insert collection of references, notes, tasks,
images. I can then insert such into Org file without thinking on each
of them, without constructing hyperlinks by hand or doing tedious copy
and paste. General benefit for me is speed and ease.

https://www.dougengelbart.org/content/view/110/460/#2a1c

,----
|     Shared Objects
| 
| Objects and the documents made out of them are shareable among members
| of a networked community: they may be simultaneously viewable,
| manipulable, and changeable according to rules appropriate to their
| nature and supported in access permissions and restrictions contained
| in their definitions.  2a1c1
`----

We all share hyperlinks and documents. But how do I share specific
hyperlink from Org file to specific person or group?

- Copy hyperlink
- Open chat, insert hyperlink
- maybe write description

or

- Copy hyperlink 
- Open up mail client
- Insert hyperlink
- Describe hyperlink
- Insert email address
- maybe designate my own email address

How about:

- choose person to receive this hyperlink
- press ENTER

What about hyperdocuments related to groups and people who should know
about them:

Maybe task have been assigned to group of 3 people. Am I to repeat the
actions of opening files, locating tasks for people, copying task,
opening email client, inserting task, finding email address for person
1, sending email, opening new email, yanking text inside again,
finding email address for person 2, sending email, opening new email,
yanking text, finding email for person 3, sending email.

Then I have 365 such tasks assigned to people, so let me rather kick
the wall with my head.

> Objects and the documents made out of them are shareable among members
> of a networked community: they may be simultaneously viewable

Staff member may use Emacs, but also other interface such as web
browser, or email to access document, receive it, or maybe edit on the
fly by using tramp access.

| manipulable, and changeable according to rules appropriate to their
| nature and supported in access permissions and restrictions contained
| in their definitions.

Fine access permissions and restrictions may be solved with
PostgreSQL.

Row Security Policies
https://www.postgresql.org/docs/13/ddl-rowsecurity.html

My projects are well written in logical order and consists of
strategic plan, tactical plans and multiple plans where some steps of
plans are broken down into projects and each project could be broken
down into specific atomic tasks easily doable or executable by
literate person without higher education.

One set of those tasks in a project may not be for the eyes of people
also participating in the same project. One group of people gets to do
specific tasks, other group of people do other tasks all related to
one project but not all people have permission to get insights into
security concerns. That is all easily solved on project planning with
such attributes such as access permissions and restrictions.

Defining a security policy matter of minutes and later defining group
access or individual access will give permissions accordingly.

https://www.dougengelbart.org/content/view/110/460/#2a1d

,----
|     Object ID-Time Stamps
| 
| Each creation or modification of an object automatically results in
| the creation of a stamp containing information concerning the date,
| time, date, and user identification associated with that modification.
| 2a1d1
| 
| Users can filter and control the portrayal of this information and
| their associated content objects.
`----

Org file has its objects which is handling internally such as
headings, properties, tags, body of a heading. But it is not multi
user environment. It does not have feature to have multiple users to
edit tasks, assign tasks in the same time, report on tasks with
concurrency support, where we may all know WHO edited the task and
WHEN and WHAT was previous version of task.

> > Two safety problems are with PostgreSQL data entry editing, one is to
> > save the previouse entries or historical and that I have solved in
> > very simple manner. Other problem is to solve the currently edited
> > text that is nowhere saved. For that reason I wish to find way to
> > automatically save the buffers somewhere but not that buffer is
> > connected to the file being saved.
> 
> > Does anybody have pointers how to do that?

I still need to find way how to open up buffer, edit string from
database so that the intermediate buffer editing still gets its
automatic saving but that buffer is not really connected to
file.

Maybe I could run of the timer a function in the buffer that is saving
it appropriately even inserting into database the temporary
version. So I guess that may be the simplest solution for that case.

> I have absolutely no idea /why/ someone would store Org mode
> data in a database

Org file and parts of Org files are elementary objects. Maybe because
they are part of one overall hyperdocument as mixed-object. Org file
could be one object, text other, video other object, tasks different
objects, specific paragraphs different, specific parts of Org file
different as by their access permissions. Mixed object may contain
directory subtree with bunch of files belonging to such. Including
bunch of files in Org is not integrated or available feature.

Org does not offer finely grained referencing. For example I cannot
specifically reference to report list under some heading unless I
place <<target>> but then again <<targe>> will work only for
HTML. With finely grained based elementary objects I can reference
such objects from other text parts, objects, files, including Org
files.

Today I was sending again the local village miner daily report to a
supervisor of village miners. When I was using org file the key
sequence would be about 100 chars including spaces, find the file if
not bound in bookmark or register, find specific heading, export
heading to ASCII, copy ASCII, open email, insert into email, send
email.

How about: press key to send it by email, choose person, ENTER and
email is sent.

Trying to do everything with Org is limiting my work and I have to
adapt way too many things. Then it is better working on a meta level
and creating useful functions that will relate to Org but not be
dependant of Org stuff and lack of structure.

> Emacs is very good at editing files, Git is very good at versioning
> them, it has plenty of commands to create branches and worktrees and
> everything else one of the millions of projects using it has ever
> need- ed, and Emacs Lisp is more than versatile enough to code every
> imaginable workflow.

One could say that for any mean of communication but general
availability of a tool does not make it proper for every use
case. People have been collaborating before computers as well and
before Git as well. Tools helps us to streamline our workflows, to
minimize our work and efforts, to lessen those repetitive small tasks.

Let us mention Emacs version control and Git version control. Those
are great tools definitely. But that is not everything there is as we
may indefinitely enhance our human processes. What version control
does is great.

But when looking better into it, one can see that version control
could be as well automatic. Programming RCS simpler and well working
revision control system took some time and effort to do it.

But then creating PostgreSQL table and few triggers to provide version
control for editing of any other table takes much less effort.

Then again, what if version control is simply built into file system?
Would not that alone be better?

Look here:

HAMMER is a file system written for DragonFlyBSD
https://www.dragonflybsd.org/hammer/

A convenient undo command is provided for single-file history, diffs,
and extractions. Snapshots may be used to access entire directory
trees.

That may not be "version control" as it does not solve problems that
Git is solving. But something similar could as well solving the
problems for a user transparently without users ever thinking about
version control commands, tools, how it works.

While Git has solved many problems it also created many problems due
to its complex nature. Just look on Internet how many questions are
there related to Git.

Anyway, solving rudimentary version control with PostgreSQL was few
minutes of work. I have made a table where column values from other
tables will be saved and inserted, and before editing those values,
save them in the table.

(defun hlink-edit-description (id) ;; 
  (let* ((description (hlink-description-value id)) ;; fetch description from 
the hyperdocument ID
         (description (if description description "")) 
         (buffer-name (format "HyperScope Editing ID: %d" id)) ;; prepare 
buffer name
         (new-description (read-from-buffer description buffer-name))) ;; edit 
description
    (hyperscope-vc "hlinks" "hlinks_description" id) ;; fetch previous 
description and store it in version control
    (rcd-db-update-entry "hlinks" "hlinks_description" "text" id 
new-description *hs*))) ;; insert new description

Simple.

Triggers can be added to each database to store the whole column
values BEFORE UPDATE OR INSERT in a version control table. See:
https://www.postgresql.org/docs/13/sql-createtrigger.html

My version control for database entries relate to ANY tables and any
columns that I specifically decide to keep their revisions in the
version table. Version table is centralized.

And yet version control table could be as well automatically
constructed for each table so not to be centralized and to be
specifically related to the table for which one need version
control. Integration can be automatic:

1. Invoke function
2. Choose table A
3. Automatically construct version control table for table A
4. Automatically add triggers

Finished there. After that, no more thinking about version control for
decades. No programming, developing, files, etc. It does not solve
same problems as Git solves it but it gives oppportunity to diff
versions and see what was changed and when in a simple manner and to
request back some specific changes or whole versions from past.

When editing hyperdocuments or editing database entries, one cannot be
satisfied with Git as Git is not made for that case. Hyperdocument may
have a whole bunch of files included, let us say 50 PDF documents, and
they are not really edited rather stored and annotated in a
database. There are user permissions to be changed, so Git is not
finding itself in that sector. It does not have structured version
control, it is file system based control.

When I say structured version control I mean columns in PostgreSQL
database. If I change author's name that column has its identifier
such as hlinks_authorname and Git does not keep identifiers for finely
grained objects. It keeps it for files and finds what modifications
have been made. But I cannot search those modifications like "Tell me
who changed author's name during February 2020". It is more general
system for file versioning, not specific system for structured
objects.

Anyway it is trivial to solve the problem in a database itself so that
user need not think about that ever again. There is not even a need to
checkout, or invoke commands. PostgreSQL handles also replication, so
once connection is configured there is no need to think how it will be
replicated on other servers.

> Some of the brightest minds have worked on them extensively, either
> from a formally educated perspective or with the ex- perience of
> blood and tears.  These giants are inviting everybody to stand on
> their shoulders, and neither would I ignore them nor would I
> recommend others to do so.

Does that mean authority and well known giant minds should influence
programmers not to program but rather trust giant minds to know it
better and only follow their principles or tools or ways and methods of work?

If that is so, let us define well a list of giant minds, and let us
stop enhancing and programming forever.

Those principles would lead to nowhere. Would there be no rudimentary
version control systems, there would be no Git. Git did not come out
of nothing. Would there be no BSD or Unix system, probably would not
be there Minix and without Minix and GNU there would be no Linux
kernel as of today. Collaborating and contributing to each other is
what develops civilization. We cannot stay in place thinking that
everything one needs is already there produced by some giant minds.

And how much effort somebody put into some feature can be great and
awarding for that person and people dependable on that feature.

But it can also be nonsense and of little value for somebody else who
simply thinks different and can find ways in streamlining processes.

Let us say Org mode versus SMOS that I recently found:

SMOS - A Comprehensive Self-Management System by Tom Sydney Kerckhove
https://smos.cs-syd.eu/features

He could recognize that Org mode is not a plaint text and it is
history. Org file is free structure in the eyes of a user that Org
programs try to manage and structure to be useful for users. The
approach to manage in structural way something that was not meant to
be structured leads to complexities.

As an Org mode user he has created SMOS to streamline task
management. This makes repetition redundant and provides more
reliability. Files maybe exported as Yaml and still imported into
normal Org files (trivial to do). No wonder he has logo eating Org
mode in a sandwich. https://github.com/NorfairKing/smos

SMOS have been built as inspired by Org task management. Org have been
built as expired by Outline mode and plethora of other references.

I could say why Org mode was not built on a database backend? Because
author started in plain text. Would database like GDBM or similar
already be included in Emacs maybe it would have until today many
database backed features. Emacs does everything possible to keep
things in a database.

init file is sample of a database that lacks concurrency
support. Users are allowed to edit various variables and configure
them and they are colliding with the custom system or
custom-set-variables.

Old and new users need to cope with same problem of lack of
concurrency since decades.

My database approach to configuration would be simple:

- variable
- value
- boolean toggle if variable is locked or not.

Locked variable would not get overwritten by other variable,
defcustom, you name it. Once user makes decision this would not get
overwritten by anything. Finally init.el or .emacs file belongs to
user, and not to programmer and user should have full control over it.

Jean