"Delayed" Error in PostgreSQL

Programming
1

Hey everyone :waving_hand: :grinning_face_with_smiling_eyes:

Context

I’m doing the Build a Database of Video Game Characters workshop in the Relational Databases course using GitHub Codespaces.

Question

Accidentially using the wrong slash in the details command (i.e. /d instead of \d) initially does nothing. It returns neither an error nor the intended output. Repeating the command with the correct syntax then works fine.

When I later try to add a column to my table, it returns a syntax error about the wrong slash and seemingly cancels the column addition command. Immediately then repeating the command to add a column, completely verbatim, works normally.

You can see this happen twice in the following terminal code.

second_database=> /d second_table
second_database-> \d second_table
                Table "public.second_table"
+--------------+---------+-----------+----------+---------+
|    Column    |  Type   | Collation | Nullable | Default |
+--------------+---------+-----------+----------+---------+
| first_column | integer |           |          |         |
+--------------+---------+-----------+----------+---------+

second_database-> ALTER TABLE second_table ADD COLUMN id INT;
ERROR:  syntax error at or near "/"
LINE 1: /d second_table
        ^
second_database=> ALTER TABLE second_table ADD COLUMN id INT;
ALTER TABLE
second_database=> /d second_table
second_database-> \d second_table
                Table "public.second_table"
+--------------+---------+-----------+----------+---------+
|    Column    |  Type   | Collation | Nullable | Default |
+--------------+---------+-----------+----------+---------+
| first_column | integer |           |          |         |
| id           | integer |           |          |         |
+--------------+---------+-----------+----------+---------+

second_database-> ALTER TABLE second_table ADD COLUMN age INT;
ERROR:  syntax error at or near "/"
LINE 1: /d second_table
        ^
second_database=> ALTER TABLE second_table ADD COLUMN age INT;
second_database=> ALTER TABLE

Why and how does this happen? Why does the error return and command interruption happen later instead of right after the command that caused it?

Thanks!

2

pay attention to the prompt arrows.

The normal prompt arrow is => and after you mistyped it turned into a ->
This 2nd arrow indicates that you have started typing a command (on the previous line where the normal prompt was) and that you have not finished it yet by typing a semi-colon. So at this point, everything you type is still part of the same command.

3

Thanks for the quick reply! I have a few follow-up questions:

Question #1:

If the commands starting with /, like /d (EDIT: I meant “\, like \d”), require a semicolon to be terminated, why it is that typing \d and then pressing enter works, but \d; doesn’t?

Example:

second_database=> \d;
invalid command \d;
Try \? for help.
second_database=> \d
               List of relations
+--------+--------------+-------+--------------+
| Schema |     Name     | Type  |    Owner     |
+--------+--------------+-------+--------------+
| public | first_table  | table | freecodecamp |
| public | second_table | table | freecodecamp |
+--------+--------------+-------+--------------+
(2 rows)

When should I be using a semicolon and when should I not be?

Question #2:

Under what circumstances does PSQL determine that I’ve entered an incomplete command and switch from => to -> to indicate it’s awaiting further input? It can’t be “any input not terminated by a semicolon”, since \d; doesn’t work and \d does (see question 1).

Question #3:

Shouldn’t the successful execution of a command end this state and return the prompt arrow from -> to =>? That doesn’t happen, as seen in my initial example:

second_database=> /d second_table
second_database-> \d second_table
                Table "public.second_table"
+--------------+---------+-----------+----------+---------+
|    Column    |  Type   | Collation | Nullable | Default |
+--------------+---------+-----------+----------+---------+
| first_column | integer |           |          |         |
+--------------+---------+-----------+----------+---------+

second_database-> ALTER TABLE second_table ADD COLUMN id INT;
ERROR:  syntax error at or near "/"
LINE 1: /d second_table
        ^
second_database=> ALTER TABLE second_table ADD COLUMN id INT;
ALTER TABLE

The “awaiting rest of command” state doesn’t terminate when I type in the correct version of the details command. This even happens if I type in the correct version with a semicolon at the end, as seen here:

second_database=> /d second_table
second_database-> \d second_table;
second_database->                      Table "public.second_table"
+----------+-----------------------+-----------+----------+---------+
|  Column  |         Type          | Collation | Nullable | Default |
+----------+-----------------------+-----------+----------+---------+
| id       | integer               |           |          |         |
| username | character varying(30) |           |          |         |
+----------+-----------------------+-----------+----------+---------+

;
ERROR:  syntax error at or near "/"
LINE 1: /d second_table
        ^
second_database=>

Only when I typed in a lone semi-colon and then pressed enter did the “awaiting rest of command” state terminate.

4

Also

Question #4:

Why does the prompt arrow sometimes simply not appear? This seems to sometimes (but not always) happen after commands that print something to the console. Example:

\d characters;
mario_database=>                                              Table "public.characters"
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
|     Column     |         Type          | Collation | Nullable |                     Default                      |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
| character_id   | integer               |           | not null | nextval('characters_character_id_seq'::regclass) |
| name           | character varying(30) |           | not null |                                                  |
| homeland       | character varying(60) |           |          |                                                  |
| favorite_color | character varying(30) |           |          |                                                  |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+

INSERT INTO characters(name, homeland, favorite_color) VALUES('Mario', 'Mushroom Kingdom', 'Red');
INSERT 0 1
mario_database=>

Note the lack of mario_database=> on the line where I typed the INSERT INTO command. Is this a bug or intentional? If intentional, why?

5

I suggest trying to google these questions to see what you can find out.

6

you shouldn’t be typing commands until the ready prompt appears. I cannot tell from the image what was going on, but in general, some commands require you to press the space bar to see the full results and possibly an enter to get back to the prompt (I can’t recall which - when) and some may need you to press an esc (again, can’t recall which when).

Command lines usually behave the same way across systems (whether psql or linux or otherwise). The more you use them, the less issues you will have anticipating their behaviour (and the less you will think about understanding it - I’m just so used to it, I don’t think about it at all)

7

Good points. Thanks!

8

Question 1:
there’s sort two types of commands you can enter in the psql shell - the slash/shortcut commands (e.g: \d), and SQL commands (e.g: CREATE DATABASE ...;). The shortcut commands are part of the psql program - added to make it easier to use I suppose. I think only those built-in shortcut commands don’t need a semi-colon. Anything else is interpreted as SQL and needs a semi-colon - so /d; is not a shortcut command → so it executes as SQL → and is invalid.

Quite sure there is a much more complicated SQL equivalent for all the shortcut commands.

Question 2:
Maybe answered in the response to question 1? Anything that’s not a shortcut command and doesn’t have the semi-colon is incomplete. \d; and \d are both complete commands presumably. \d;has the semi-colon, so it doesn’t see it as a shortcut command? Edit: actually, this one is trying to use ; as the argument to the \d command I think - and it’s invalid.

Question 3:
Seems like this is maybe an intended or unintended feature of psql. On the first line, you started a command a didn’t finish it, then displayed your table → which worked, and then it was waiting for you to finish the command you started on the first line. Perhaps that’s so you can view the tables and other info while in the middle of writing a complex command? So those shortcut commands execute in their own context or something.

External research says: SQL is buffered until ; and meta-commands are executed immediately. The program sees the commands separately - interesting.

Question 4:
This is caused by some of the techniques used to create the course. The test runner is intercepting the output of the command to read it or something, which is causing the prompt to sometimes not show up. This would not happen if you were running psql in your local terminal. Just press enter once when the prompt doesn’t show up to jump to a new line.

9

I appreciate you taking the time to go in-depth on this stuff.

Would you mind elaborating on the following?

This is caused by some of the techniques used to create the course. The test runner is intercepting the output of the command to read it or something, which is causing the prompt to sometimes not show up.

Which part is actually doing this? When you say “the test runner” are you referring to CodeRoad / one of its processes, or something else?

Just press enter once when the prompt doesn’t show up to jump to a new line.

Yeah, I realized. It’s not really an issue; I’m just curious as to how it works. This is especially since I’ve noticed an additional bug, which, while similarly completely insignificant, is just so strange that it made me curious.

If anyone’s interested in this strange and insignificant bug, here’s how it works:

If

  1. The aforementioned bug where the prompt doesn’t show up happens.
  2. I press the up arrow to fill in a previous command
  3. I try to erase that command with backspace (EDIT: or just press the up arrow again fill in the command before that)

I sometimes find that it doesn’t let me erase the first n characters, where n is the length of missing prompt (e.g. postgres=> ). I can then type in another command normally, and it runs, despite being preceded by the chopped-off beginning of another command. It even appears as-is in the command line history.

For an example, if I had done this in the earlier example I gave for the prompt not showing up:

\d characters;
mario_database=>                                              Table "public.characters"
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
|     Column     |         Type          | Collation | Nullable |                     Default                      |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
| character_id   | integer               |           | not null | nextval('characters_character_id_seq'::regclass) |
| name           | character varying(30) |           | not null |                                                  |
| homeland       | character varying(60) |           |          |                                                  |
| favorite_color | character varying(30) |           |          |                                                  |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+

INSERT INTO characters(name, homeland, favorite_color) VALUES('Mario', 'Mushroom Kingdom', 'Red');
INSERT 0 1
mario_database=>

it would’ve instead looked something like this:

\d characters;
mario_database=>                                              Table "public.characters"
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
|     Column     |         Type          | Collation | Nullable |                     Default                      |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+
| character_id   | integer               |           | not null | nextval('characters_character_id_seq'::regclass) |
| name           | character varying(30) |           | not null |                                                  |
| homeland       | character varying(60) |           |          |                                                  |
| favorite_color | character varying(30) |           |          |                                                  |
+----------------+-----------------------+-----------+----------+--------------------------------------------------+

SELECT * FROM chaINSERT INTO characters(name, homeland, favorite_color) VALUES('Mario', 'Mushroom Kingdom', 'Red');
INSERT 0 1
mario_database=>

Note how SELECT * FROM cha is the same length as mario_database=> .

I tried recreating this bug while writing this, and it initially worked, but when I dragged the edge of my terminal in VSCode to resize it, the bug disappeared and the chopped-off former command (like SELECT * FROM cha from the example above) completely disappeared, and was replaced by the proper prompt (like mario_database=> ), which wasn’t even there to begin with!

Since the command (like INSERT INTO in my example) still works, it’s clearly just a visual bug, but it’s just such an absurd bug that it caught my attention and prompted me to look into it further.

11

The techniques used to create the course…

I’ll try and explain what I think I know. The tests are code in your workspace that you can’t see. CodeRoad runs those tests and looks at the output - did it pass or fail - e.g: if the lesson says to create a table, the test code looks in the database for the table you were supposed to create, if it’s there, it passes. CodeRoad runs those tests and checks the output - if it passed, the lesson passes and you can move on.

The code that runs the tests use node and some testing libraries. Node can easily access the files in the workspace and look at the database. So if there’s a lesson where you need to create a file or add text to it, node can just look at the file. If there’s a lesson where you need to change something in the database, node can just look in the database. Something that it can’t easily do is see the commands you put in the terminal or the output of those commands (whether it’s the bash shell or psql shell). So if there’s a lesson that asks you to enter echo hello world in the bash shell - or \d in the psql shell - no files were changed (by default) and nothing changed in the database to easily check. So in order to see what you did in those type of lessons, there’s some settings that are changed to write certain things to a file so the tests can check the file and see what you did.

For bash, a few things are written to a file - the last command you entered, and the folder you entered it in, among some others. These are configured in the .bashrc file. That’s a file that has instructions for the shell - when you open a bash shell, it uses those instructions. That file is part of the course. When you first open a VM to run a course, none of the course files are there yet, CodeRoad loads them all in. That’s why you see the theme change from light to dark. Additionally, the bash terminal is (usually?) already open and doesn’t have the .bashrc instructions that just got loaded. That’s why you sometimes need to close the terminal and open a new one at the start of a course. When you do, the new terminal uses those instructions, which writes your commands to a file, which the tests can see, which tell CodeRoad if you passed the lesson.

Similarly, there’s a .psqlrc file which has instructions for the psql shell. Even though it opens in the same terminal, it is a different program. Since that program isn’t started when the course loads, those instructions are applied when you start psql and you don’t (shouldn’t) ever have to close and reopen it. In those instructions is a command to write the output of all your psql commands to a file for the tests to see - and to write the output to the terminal. So one command in, and it outputs to two places - this causes the terminal to get out of sync a little bit or something. If you look at your screenshot for question 4, the prompt is actually there, it’s just at the top. So you entered \d characters , the instructions intercepted the output - the prompt showed back up - then it wrote to the file and to the terminal (or something along those lines). Not sure why the prompt sometimes show up at the bottom vs the top - just a timing thing I suppose. Bash commands don’t have that problem cause the technique for those isn’t to write the output of a single command to two places, it’s to run a separate command which outputs to the file - which isn’t possible or is much more difficult for psql.

For the other strange bug you found…

This is likely just a side-effect of the things mentioned above. The terminal thinks the prompt is there or something and doesn’t let you delete the characters. Or it does let you delete the characters and it just appears like they’re still there. Probably something along one of those two lines. And yes, sometimes resizing the terminal can make the formatting all messed up - or, fix it if it’s already messed up.