Re: Interacting with PostgreSQL

[Jean Louis]

* Tim Landscheidt <tim@tim-landscheidt.de> [2020-11-25 02:15]:
> As a mere mortal :-), when for example I add triggers to
> five tables with three test cases (INSERT, UPDATE, DELETE)
> each, I will not remember which of the triggers or test
> cases have been set/inserted/updated/deleted.

Thank you.

I am probably not getting it right. I got impression that when I set
triggers to work they are set to be forgotten and I do not need to
think of it any more.

In general I am developing SQL in the SQL file, so anything I do I
write first in the file. Rarely I will go to sql-postgres to change
some view or similar.

Better workflow is always writing it first in the file and then
executing chunks from file. It is just fine for testing. Benefit is
that I can recreate new database from the file that has much better
readability then the SQL dump.

> Also, my mind will be focussed on fixing the functions/etc.  Worst
> case is that I miss that psql has reverted to auto-commit mode and
> my not-yet-working trigger/query overwrites good data with garbage.

OK, I do not know about that. And I am not using auto commit mode, did
not know that it exists and will not switch to it.

> So I definitely want to be sure that my changes are (easily)
> revertable until they are working properly.

Quite understandable. I did not encounter similar problems in last so
many years.

> I prefer to use shell-quote-argument and other prebaked
> functions because "did not break yet" means that there is a
> chance that I will discover at 3 AM that my data has been
> garbled, and now I would have to find the error in my quote
> function, fix all the data that has been trashed, and do all
> that under time pressure because I have a deadline at 4 AM.

I said "did not break yet" as the function is escaping the quotes,
maybe backslashes, and I do not remember now if there is anything else
to be escaped really. That function did not break ever is also the
case. When it is there you can inspect it and look into specification
and then make test cases and see if it works well or not.

I am sure that Perl has also somewhere SQL escaping functions and it
also has bugs, so there is not much difference. PostgreSQL has also
bugs, programmers are not perfect.

I have used this function in Perl.

sub sql_escape ($) {
        my ($self, $string) = @_;
        return unless $string;
        $string =~ s/'/''/g;
        $string =~ s/\\/\\\\/g;
        return $string;
}

> So if I have a working solution with call-process and DBI
> that is partially tested every day by millions of users, I
> rather not replace just for the sake of replacing it.

Hm, yes and no. Good when you are familiar with it. My side I like to
interact with Emacs Lisp directly to be kind of free of Perl.

I am interested in your work.

What do you store in the database?

Do you interact through Emacs with it?