New articles - OurBigBook.com

EF-Tu by

Ciro Santilli  40 Updated 2025-07-16

PostgreSQL getting started by

Ciro Santilli  40 Updated 2025-07-16

On Ubuntu 20.10 PostgreSQL 12.6, login with psql on my default username without sudo fails with: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist

This is the one that worked on Ubuntu 21.04: stackoverflow.com/questions/11919391/postgresql-error-fatal-role-username-does-not-exist/38444152#38444152

sudo -u postgres createuser -s $(whoami)
createdb $(whoami)

Explanation:

sudo -u postgres uses the postgres user via peer authentication
-s in createuser -s: make it a superuser
createdb: TODO why do we have to create a table with the same name as the user? Otherwise login fails.

You can now run psql without any password. This works without password due to peer authentication:

sudo cat /etc/postgresql/12/main/pg_hba.conf

shows that peer authentication is available to all users apparently:

local   all             postgres                                peer

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer

List users:

psql -c '\du'

output:

                                    List of roles
  Role name  |                         Attributes                         | Member of
-------------+------------------------------------------------------------+-----------
 ciro        | Superuser, Create role, Create DB                          | {}
 owning_user |                                                            | {}
 postgres    | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

Delete user later on:

psql -c 'DROP USER username;'

Create a database:

createdb testdb0

Help toplevel:

help

Get help for Postgres commands such as \h and so on:

\?

List supported SQL commands:

\h

Show syntax for one type of command:

\h SELECT

List all databases:

psql -c '\l'

which shows:

    Name     |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-------------+----------+----------+-------------+-------------+-----------------------
 ciro        | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 postgres    | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
 template0   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 template1   | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =c/postgres          +
             |          |          |             |             | postgres=CTc/postgres
 testdb0     | postgres | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 |
(6 rows)

Delete a database:

psql -c 'DROP DATABASE "testdb0";'

If you didn't give a database from the command line e.g.:

psql

you can do that afterwards with:

\c testdb0

Let's create a table and test that it is working:

psql testdb0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'

List tables, no special tables:

psql testdb0 -c '\dt'

gives:

        List of relations
 Schema |  Name  | Type  | Owner
--------+--------+-------+-------
 public | table0 | table | ciro
(1 row)

View table schema: stackoverflow.com/questions/109325/postgresql-describe-table

psql testdb0 -c '\d+ table0'

output:

                                      Table "public.table0"
 Column |     Type      | Collation | Nullable | Default | Storage  | Stats target | Description
--------+---------------+-----------+----------+---------+----------+--------------+-------------
 int0   | integer       |           |          |         | plain    |              |
 char0  | character(16) |           |          |         | extended |              |

Insert some data into it and get the data out:

psql testdb0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
psql testdb0 -c 'SELECT * FROM table0;'

output:

 int0 |      char0
------+------------------
    2 | two
    3 | three
    5 | five
    7 | seven
(4 rows)

Delete the table:

psql testdb0 -c 'DROP TABLE table0;'

Create a test user in PostgreSQL by

Ciro Santilli  40 Updated 2025-07-16

In order to create a test user with password instead of peer authentication, let's create test user:

createuser -P user0
createdb user0

-P makes it prompt for the users password.

Alternatively, to create the password non-interactively stackoverflow.com/questions/42419559/postgres-createuser-with-password-from-terminal:

psql -c "create role NewRole with login password 'secret'"

Can't find a way using the createuser helper.

We can then login with that password with:

psql -U user0 -h localhost

which asks for the password we've just set, because the -h option turns off peer authentication, and turns off password authentication.

The password can be given non-interactively as shown at stackoverflow.com/questions/6405127/how-do-i-specify-a-password-to-psql-non-interactively with the PGPASSWORD environment variable:

PGPASSWORD=a psql -U user0 -h localhost

Now let's create a test database which user0 can access with an existing superuser account:

createdb user0db0
psql -c 'GRANT ALL PRIVILEGES ON DATABASE user0db0 TO user0'

We can check this permission with:

psql -c '\l'

which now contains:

                                  List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 user0db0  | ciro     | UTF8     | en_GB.UTF-8 | en_GB.UTF-8 | =Tc/ciro             +
           |          |          |             |             | ciro=CTc/ciro        +
           |          |          |             |             | user0=CTc/ciro

The permission letters are explained at:

user0 can now do the usual table operations on that table:

PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'CREATE TABLE table0 (int0 INT, char0 CHAR(16));'
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c "INSERT INTO table0 (int0, char0) VALUES (2, 'two'), (3, 'three'), (5, 'five'), (7, 'seven');"
PGPASSWORD=a psql -U user0 -h localhost user0db0 -c 'SELECT * FROM table0;'

Peer authentication by

Ciro Santilli  40 Updated 2025-07-16

www.postgresql.org/docs/13/auth-peer.html

Uses the name of the current Linux user to login without a password.

PostgreSQL logging by

Ciro Santilli  40 Updated 2025-07-16

stackoverflow.com/questions/722221/how-to-log-postgresql-queries

Ubuntu 21.10 has a certain default level of logging by default to:

/var/log/postgresql/postgresql-13-main.log

but it does not log everything, only/mostly errors it seems.

Setting:

log_statement = 'all'

under:

/etc/postgresql/13/main/postgresql.conf

and then restarting the server:

sudo service restart postgresql

just works.

Realtime monitoring for long queries instead: stackoverflow.com/questions/8597516/app-to-monitor-postgresql-queries-in-real-time

PostgreSQL serialization failure by

Ciro Santilli  40 Updated 2025-07-16

When using SQL REPEATABLE READ isolation level and SQL SERIALIZABLE isolation level, concurrent transactions may fail with a serialization failure, and then you might need to retry them. You server code or your ORM must always account for that.

A good way to explore when it happens is to use the example

Related questions:

Acousto-optic modulator by

Ciro Santilli  40 Updated 2025-07-16

An optical multiplexer!

Control Light with Sound! by Les' Lab (2021)

Source.

Interferometer by

Ciro Santilli  40 Updated 2025-07-16

Optical fibre by

Ciro Santilli  40 Updated 2025-07-16

The Story of Light by Bell Labs (2015)

Source. A ultra quick and Bell Labs focused overview of the development of optical fibre.

Optical table by

Ciro Santilli  40 Updated 2025-07-16

The breadboard of photonics!

For example, that is how most modern microscopes are prototyped, see for example Video "Two Photon Microscopy by Nemonic NeuroNex (2019)".

This is kind of why they are also sometimes called "optical breadboarbds", since breadboards are what we use for early prototyping in electronics. Wikipedia however says "optical breadboard" is a simpler and cheaper type of optical table with less/no stabilization.

A simple refracting telescope built on an optical bench by plenum88 (2013)

Source.

Optical ring resonator by

Ciro Santilli  40 Updated 2025-07-16

Polarization of light by

Ciro Santilli  40 Updated 2025-07-16

This section discusses the pre-photon understanding of the polarization of light. For the photon one see: photon polarization.

polarization.com/history/history.html is a good page.

People were a bit confused when experiments started to show that light might be polarized. How could a wave that propages through a 3D homgenous material like luminiferous aether have polarization?? Light would presumably be understood to be analogous to a sound wave in 3D medium, which cannot have polarization. This was before Maxwell's equations, in the early 19th century, so there was no way to know.

Polarizer by

Ciro Santilli  40 Updated 2025-07-16

A device that modifies photon polarization.

As mentioned at Video "Quantum Mechanics 9b - Photon Spin and Schrodinger's Cat II by ViaScience (2013)", it can be modelled as a bra.

Epistle to the Romans by

Ciro Santilli  40 Updated 2025-07-16

Ilana Wisby by

Ciro Santilli  40 Updated 2025-07-16

Founding CEO of Oxford Quantum Circuits.

As mentioned at www.investmentmonitor.ai/tech/innovation/in-conversation-with-oxford-quantum-circuits-ilana-wisby she is not the original tech person:

she was finally headhunted by Oxford Science and Innovation to become the founding CEO of OQC. The company was spun out of Oxford University's physics department in 2017, at which point Wisby was handed "a laptop and a patent".

Did they mean Oxford Sciences Enterprises? There's nothing called "Oxford Science and Innovation" on Google. Yes, it is just a typo oxfordscienceenterprises.com/news/meet-the-founder-ilana-wisby-ceo-of-oxford-quantum-circuits/ says it clearly:

I was headhunted by Oxford Sciences Enterprises to be the founding CEO of OQC.

oxfordquantumcircuits.com/story mentions that the core patent was by Dr. Peter Leek: www.linkedin.com/in/peter-leek-00954b62/

How to use an Oxford Nanopore MinION to extract DNA from river water and determine which bacteria live in it by

Ciro Santilli  40 Updated 2025-07-16

This article gives an idea of how this kind of biological experiment feels like to a software engineer who has never done any biology like Ciro Santilli.

octokit.js by

Ciro Santilli  40 Updated 2025-07-16

github.com/philschatz/octokit.js

Node.js worker_threads by

Ciro Santilli  40 Updated 2025-07-16

Sequelize by

Ciro Santilli  40 Updated 2025-07-16

Source code: github.com/sequelize/sequelize

Some usage examples under: Section "Sequelize example".

As of 2021, this library is extremely painful to use. It does feel semi-mature, but there are just too much horrible things going on;

the documentation is a bit messy and misses a lot of stuff. The examples are often too short, and it is hard to understand what specific options they are talking about do because they lack clear input/expected output pairs. Examples:
- github.com/sequelize/sequelize/issues/5385#issuecomment-324479607
- github.com/sequelize/sequelize/issues/1775#issuecomment-360028396
the implementation has several inelegant/unintuitive annoyances/requirements of code repetition that drive you mad.
The association API feels notably bad, it took a few days for Ciro Santilli to learn to do what he considers "basic" association operations, knowledge which he dumped to: stackoverflow.com/questions/22958683/how-to-implement-many-to-many-association-in-sequelize/67973948#67973948
See also: how to decide if an ORM is good?.
bugs are piling up. It appears that many key devs left, and current maintainers are just not being able to keep up.
And they have setup a stupid bot that closes every thread automatically after a few days, what's the point... valid bugs are being closed due to this, and it is impossible to distinguish what is solved and what isn't since everything gets closed.

Some glaring issues are listed at the horrors of Sequelize.

 Pinned article: Introduction to the OurBigBook Project

Welcome to the OurBigBook Project! Our goal is to create the perfect publishing platform for STEM subjects, and get university-level students to write the best free STEM tutorials ever.

Everyone is welcome to create an account and play with the site: ourbigbook.com/go/register. We belive that students themselves can write amazing tutorials, but teachers are welcome too. You can write about anything you want, it doesn't have to be STEM or even educational. Silly test content is very welcome and you won't be penalized in any way. Just keep it legal!