Content AND Presentation

2020-03-28 psql as a PostgreSQL teacher

A week ago, one of the commenters of my post from two weeks ago asked about the way to do stuff like \d (“describe table”) or \l (list the databases) etc. from SQL blocks in Org-mode. Well, the answer is – rather expectedly – “this is PostgreSQL, of course it’s possible”. Here’s how psql can teach you how to do it.

Instead of starting psql normally, run it with the -E (--echo-hidden) command-line parameter. (Alternatively, you can say \set ECHO_HIDDEN on.) Then, every backslash command which queries the database displays the SQL spell it uses.

For instance, \d turns out to be

SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' END as "Type",
  pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','p','v','m','S','f','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname <> 'information_schema'
      AND n.nspname !~ '^pg_toast'
  AND pg_catalog.pg_table_is_visible(c.oid)

and \l is just

SELECT d.datname as "Name",
       pg_catalog.pg_get_userbyid(d.datdba) as "Owner",
       pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding",
       d.datcollate as "Collate",
       d.datctype as "Ctype",
       pg_catalog.array_to_string(d.datacl, E'\n') AS "Access privileges"
FROM pg_catalog.pg_database d

Granted, this is not pretty, but at least you can see how it works under the hood.

And by the way, since Org-mode apparently uses psql anyway, you can also say things like

#+begin_src sql :engine postgresql :dbhost localhost :dbuser postgres :dbpassword docker :database postgres :dbport 5432

to see the list of tables etc.

CategoryEnglish, CategoryBlog, CategoryPostgreSQL

Comments on this page

2020-03-23 A rebase trick with disappearing commit

Today, while working with Git, I discovered a very nice feature. I was working on a dedicated feature branch. Suddenly I discovered a bug which had to be fixed in order for that feature branch to make sense. However, the bug was not really connected with the feature I was working on. Well, it was connected in a sense, but it could affect other things as well, and I decided it should be fixed much earlier than I could possibly finish working on that feature – in other words, it deserved its own branch, based on the develop branch.

So, I stashed my work, switched the current branch to develop, created a fixbug branch, fixed the bug, created a merge request and asked a colleague to review it.

But here’s the deal: I could not work on my feature without that particular bug fixed. On the other hand, I wanted my feature branch to be based on develop and not on some other, random branch (well, that would work, but I like the history to be as clean as possible). What to do?

Here is what I have done. I cherry-picked the (sole) commit from the fixbug branch to my feature branch and continued working. When the colleague merged by fixbug branch some time later, I pulled develop and rebased feature onto it. Since rebasing in Git means “replaying all commits on top of some other commit”, and my fix was already present on develop, the cherry-picked commit was silently discarded as unnecessary (since I did not explicitly tell git rebase to --keep-empty commits). That was really nice – I didn’t have to manually delete the cherry-picked commit from where it didn’t really belong.

Tl;dr – here was the situation after I fixed the bug:

          D---E---H'---I  feature
A---B---C---F---G  develop
                  H  fixbug

(H' is the cherry-picked commit, identical to H).

This was the state of the project after my merge request was approved:

          D---E---H'---I  feature
A---B---C---F---G---H  develop

and this is what happened during the rebase:

                      D---E---I  feature
A---B---C---F---G---H  develop

Clever Git!

CategoryEnglish, CategoryBlog, CategoryGit

Comments on this page

2020-03-16 COVID-19

It seems that everyone is talking about the COVID-19 pandemic now. Well, I decided to jump the bandwagon with a PSA. As all long-time readers of my blog know, it is a small tradition here to offer a decade of Rosary for all my readers on occasions of Easter and Christmas. I figure that a situation of life-endangering pandemic is a perfect opportunity to remember about prayer, so I am going to do the same also today.

While I am not a qualified theologian, let me also mention that – contrary to the popular (?) belief – we, Catholics, do not give up on medicine. While the power of prayer is huge, God gave us reason, well, for a reason, and we should follow sound medical advice. And even if we fall sick – maybe even die – this is not the worst thing that might happen.

Again, I wish all my readers strong faith, heaven after death, and health. God bless you all!

CategoryEnglish, CategoryBlog, CategoryFaith

Comments on this page


CategoryEnglish, CategoryBlog