CockroachDB TIL: Volume 3
This is a new series of articles covering short "Today I learned" topics as I peel the layers on CockroachDB. This is meant to resemble release notes. I decided to mix it up with the format for these posts as they may not justify an entire blog.
Previous articles
Topics
Topic 1 Force lookup join instead of merge or hash join
set prefer_lookup_joins_for_fks=on;
show prefer_lookup_joins_for_fks;
prefer_lookup_joins_for_fks
-------------------------------
on
To make the optimizer prefer lookup joins over merge or hash joins when performing foreign key checks, set the prefer_lookup_joins_for_fks session variable to on (merge joins are the default for single row inserts and hash joins are likely to appear with more rows. There are times when a lookup join will be more efficient than the chosen plan by the optimizer. Lesson here is to test all available options and not to accept the defaults!
Topic 2 Using pg_isready to check connection status of CockroachDB
You may want to set up your own monitoring using tools you've accustomed to. pg_ready is one of those tools coming from the Postgresql world that can be adapted to CockroachDB. Since we're PG wire compatible, this works out of the box.
pg_isready -h free-tier.gcp-us-central1.cockroachlabs.cloud -p 26257
free-tier.gcp-us-central1.cockroachlabs.cloud:26257 - accepting connections
~ echo $?
0
The exit status definitions are:
- pg_isready returns 0 to the shell if the server is accepting connections normally
- 1 if the server is rejecting connections (for example during startup)
- 2 if there was no response to the connection attempt
- 3 if no attempt was made (for example due to invalid parameters).
So let's pass the wrong port, because we're not PG
pg_isready -h free-tier.gcp-us-central1.cockroachlabs.cloud -p 5432
free-tier.gcp-us-central1.cockroachlabs.cloud:5432 - no response
~ echo $?
2
Topic 3 Clearing the terminal
Imagine you can clear the SQL CLI in the same way you can clear the terminal shell? Well you absolutely can using \! clear syntax or simply with CTRL + l keyboard shortcut. This works in psql as well as in cockroach CLI.
demo@127.0.0.1:26257/defaultdb> \?
You are using 'cockroach sql', CockroachDB's lightweight SQL client.
General
\q, quit, exit exit the shell (Ctrl+C/Ctrl+D also supported).
Help
\? or "help" print this help.
\h [NAME] help on syntax of SQL commands.
\hf [NAME] help on SQL built-in functions.
...
More documentation about our SQL dialect and the CLI shell is available online:
https://www.cockroachlabs.com/docs/v21.1/sql-statements.html
https://www.cockroachlabs.com/docs/v21.1/use-the-built-in-sql-client.html
demo@127.0.0.1:26257/defaultdb> \! clear
At this point your CLI will clear the screen and you will return to the SQL prompt
demo@127.0.0.1:26257/defaultdb>
Same goes for the keyboard shortcut
In fact, you can execute different terminal shell commands with the \! combination
demo@127.0.0.1:26257/defaultdb> \! pwd
/Users/artem
The output following the sql command is my current directory on the filesystem but I digress.
Anyway, I've been using this trick consistently since I learned about it!
Topic 4 Cockroach demo opened as a process and override password
This particular question has come up at least three times in the last week. How do you run cockroach demo which has a temporary enterprise license enabled for 60min in the background as a process?
cockroach demo is a small in-memory environment that you can quickly set up to work with CockroachDB. It is not meant to be representative of a real cluster and only there for demo purposes. I've grown to like the tool so much that it's my primary go to when I want to experiement with something. The main issue is that it spins up in-process and once you exit, you lose the environment. So what if you wanted to spin it up and access it from another shell, as a different user? You need to start it with pg_sleep command, something you've learned in an earlier blog and override the password because you no longer have access to stdout where the default password is printed out. Doing all of that in a single query will yield the results we need.
cockroach demo -e "alter user demo with password "test"; select pg_sleep(10000);"
cockroach sql --url "postgres://demo:test@127.0.0.1:26257?sslmode=require"
Topic 5 Change display format in CLI
You can quickly change format of the CLI to fit your needs
There are several formats available, the setting display_format controls the output which by default is set to table.
demo@127.0.0.1:26257/movr> select * from vehicles limit 1;
id | city | type | owner_id | creation_time | status | current_location | ext
---------------------------------------+-----------+---------+--------------------------------------+---------------------+--------+-----------------------+-------------------
aaaaaaaa-aaaa-4800-8000-00000000000a | amsterdam | scooter | c28f5c28-f5c2-4000-8000-000000000026 | 2019-01-02 03:04:05 | in_use | 62609 Stephanie Route | {"color": "red"}
(1 row)
Time: 1ms total (execution 1ms / network 0ms)
demo@127.0.0.1:26257/movr>
Say we want to output a lot of rows which can cause buffering issues on the client. You can quickly change the display_format to something like csv which will output the results in a more compact form
demo@127.0.0.1:26257/movr> \set display_format csv;
demo@127.0.0.1:26257/movr> select * from vehicles limit 1;
id,city,type,owner_id,creation_time,status,current_location,ext
aaaaaaaa-aaaa-4800-8000-00000000000a,amsterdam,scooter,c28f5c28-f5c2-4000-8000-000000000026,2019-01-02 03:04:05,in_use,62609 Stephanie Route,"{""color"": ""red""}"
Time: 1ms total (execution 0ms / network 0ms)
demo@127.0.0.1:26257/movr>
let me show you another interesting format, records
demo@127.0.0.1:26257/movr> \set display_format records;
demo@127.0.0.1:26257/movr> select * from vehicles limit 1;
-[ RECORD 1 ]
id | aaaaaaaa-aaaa-4800-8000-00000000000a
city | amsterdam
type | scooter
owner_id | c28f5c28-f5c2-4000-8000-000000000026
creation_time | 2019-01-02 03:04:05
status | in_use
current_location | 62609 Stephanie Route
ext | {"color": "red"}
Time: 1ms total (execution 0ms / network 0ms)
Here's one I just learned about called sql. This is very handy as it provides the necessary SQL statements to re-create the table schema and records in the output.
demo@127.0.0.1:26257/movr> select * from vehicles limit 10;
CREATE TABLE results (
id STRING,
city STRING,
type STRING,
owner_id STRING,
creation_time STRING,
status STRING,
current_location STRING,
ext STRING
);
INSERT INTO results VALUES ('aaaaaaaa-aaaa-4800-8000-00000000000a', 'amsterdam', 'scooter', 'c28f5c28-f5c2-4000-8000-000000000026', '2019-01-02 03:04:05', 'in_use', '62609 Stephanie Route', '{"color": "red"}');
INSERT INTO results VALUES ('bbbbbbbb-bbbb-4800-8000-00000000000b', 'amsterdam', 'scooter', 'bd70a3d7-0a3d-4000-8000-000000000025', '2019-01-02 03:04:05', 'available', '57637 Mitchell Shoals Suite 59', '{"color": "blue"}');
INSERT INTO results VALUES ('22222222-2222-4200-8000-000000000002', 'boston', 'scooter', '2e147ae1-47ae-4400-8000-000000000009', '2019-01-02 03:04:05', 'in_use', '19659 Christina Ville', '{"color": "blue"}');
INSERT INTO results VALUES ('33333333-3333-4400-8000-000000000003', 'boston', 'scooter', '33333333-3333-4400-8000-00000000000a', '2019-01-02 03:04:05', 'in_use', '47259 Natasha Cliffs', '{"color": "green"}');
INSERT INTO results VALUES ('99999999-9999-4800-8000-000000000009', 'los angeles', 'scooter', '9eb851eb-851e-4800-8000-00000000001f', '2019-01-02 03:04:05', 'in_use', '43051 Jonathan Fords Suite 36', '{"color": "red"}');
INSERT INTO results VALUES ('00000000-0000-4000-8000-000000000000', 'new york', 'skateboard', '051eb851-eb85-4ec0-8000-000000000001', '2019-01-02 03:04:05', 'in_use', '64110 Richard Crescent', '{"color": "black"}');
INSERT INTO results VALUES ('11111111-1111-4100-8000-000000000001', 'new york', 'scooter', '147ae147-ae14-4b00-8000-000000000004', '2019-01-02 03:04:05', 'in_use', '86667 Edwards Valley', '{"color": "black"}');
INSERT INTO results VALUES ('cccccccc-cccc-4000-8000-00000000000c', 'paris', 'skateboard', 'c7ae147a-e147-4000-8000-000000000027', '2019-01-02 03:04:05', 'in_use', '19202 Edward Pass', '{"color": "black"}');
INSERT INTO results VALUES ('dddddddd-dddd-4000-8000-00000000000d', 'paris', 'skateboard', 'cccccccc-cccc-4000-8000-000000000028', '2019-01-02 03:04:05', 'available', '2505 Harrison Parkway Apt. 89', '{"color": "red"}');
INSERT INTO results VALUES ('eeeeeeee-eeee-4000-8000-00000000000e', 'rome', 'bike', 'fae147ae-147a-4000-8000-000000000031', '2019-01-02 03:04:05', 'in_use', '64935 Matthew Flats Suite 55', '{"brand": "Pinarello", "color": "blue"}');
-- 10 rows
Time: 1ms total (execution 1ms / network 0ms)
Here's another intersting one html. It will output each row wrapped in HTML tags!
demo@127.0.0.1:26257/movr> \set display_format html;
demo@127.0.0.1:26257/movr> select * from vehicles limit 10;
<table>
<thead><tr><th>row</th><th>id</th><th>city</th><th>type</th><th>owner_id</th><th>creation_time</th><th>status</th><th>current_location</th><th>ext</th></tr></thead>
<tbody>
<tr><td>1</td><td>aaaaaaaa-aaaa-4800-8000-00000000000a</td><td>amsterdam</td><td>scooter</td><td>c28f5c28-f5c2-4000-8000-000000000026</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>62609 Stephanie Route</td><td>{"color": "red"}</td></tr>
<tr><td>2</td><td>bbbbbbbb-bbbb-4800-8000-00000000000b</td><td>amsterdam</td><td>scooter</td><td>bd70a3d7-0a3d-4000-8000-000000000025</td><td>2019-01-02 03:04:05</td><td>available</td><td>57637 Mitchell Shoals Suite 59</td><td>{"color": "blue"}</td></tr>
<tr><td>3</td><td>22222222-2222-4200-8000-000000000002</td><td>boston</td><td>scooter</td><td>2e147ae1-47ae-4400-8000-000000000009</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>19659 Christina Ville</td><td>{"color": "blue"}</td></tr>
<tr><td>4</td><td>33333333-3333-4400-8000-000000000003</td><td>boston</td><td>scooter</td><td>33333333-3333-4400-8000-00000000000a</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>47259 Natasha Cliffs</td><td>{"color": "green"}</td></tr>
<tr><td>5</td><td>99999999-9999-4800-8000-000000000009</td><td>los angeles</td><td>scooter</td><td>9eb851eb-851e-4800-8000-00000000001f</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>43051 Jonathan Fords Suite 36</td><td>{"color": "red"}</td></tr>
<tr><td>6</td><td>00000000-0000-4000-8000-000000000000</td><td>new york</td><td>skateboard</td><td>051eb851-eb85-4ec0-8000-000000000001</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>64110 Richard Crescent</td><td>{"color": "black"}</td></tr>
<tr><td>7</td><td>11111111-1111-4100-8000-000000000001</td><td>new york</td><td>scooter</td><td>147ae147-ae14-4b00-8000-000000000004</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>86667 Edwards Valley</td><td>{"color": "black"}</td></tr>
<tr><td>8</td><td>cccccccc-cccc-4000-8000-00000000000c</td><td>paris</td><td>skateboard</td><td>c7ae147a-e147-4000-8000-000000000027</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>19202 Edward Pass</td><td>{"color": "black"}</td></tr>
<tr><td>9</td><td>dddddddd-dddd-4000-8000-00000000000d</td><td>paris</td><td>skateboard</td><td>cccccccc-cccc-4000-8000-000000000028</td><td>2019-01-02 03:04:05</td><td>available</td><td>2505 Harrison Parkway Apt. 89</td><td>{"color": "red"}</td></tr>
<tr><td>10</td><td>eeeeeeee-eeee-4000-8000-00000000000e</td><td>rome</td><td>bike</td><td>fae147ae-147a-4000-8000-000000000031</td><td>2019-01-02 03:04:05</td><td>in_use</td><td>64935 Matthew Flats Suite 55</td><td>{"brand": "Pinarello", "color": "blue"}</td></tr>
</tbody>
<tfoot><tr><td colspan=9>10 rows</td></tr></tfoot></table>
Time: 1ms total (execution 1ms / network 0ms)
Let's see what it produces as a page. Copy the html from <table> to </table> tags and save inside a file with .html extension. Then open it in a browser.
Feel free to experiment with the other available formats at your leisure!
Comments