MVCC mental model in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to select all rows from the table 'employees'.

PostgreSQL

SELECT * FROM [1];

Drag options to blanks, or click blank then click option'

Aemployees

Bdepartments

Csalaries

Dprojects

Attempts:

3 left

💡 Hint

Common Mistakes

Using a wrong table name like 'departments' or 'projects'.

The table name to select from is 'employees'.

2fill in blank

medium

Complete the code to begin a transaction in PostgreSQL.

PostgreSQL

[1] TRANSACTION;

Drag options to blanks, or click blank then click option'

ASTOP

BBEGIN

CCOMMIT

DROLLBACK

Attempts:

3 left

💡 Hint

Common Mistakes

Using COMMIT or ROLLBACK instead of BEGIN.

To start a transaction, use the command BEGIN TRANSACTION.

3fill in blank

hard

Fix the error in the SQL to see the current transaction's snapshot.

PostgreSQL

SELECT txid_snapshot_[1]();

Drag options to blanks, or click blank then click option'

Acurrent

Blast

Cactive

Dfirst

Attempts:

3 left

💡 Hint

Common Mistakes

Using txid_snapshot_last() or txid_snapshot_active() which do not exist.

The correct function to get the current transaction snapshot is txid_snapshot_current().

4fill in blank

hard

Fill both blanks to write a query that shows all visible rows for the current transaction using MVCC.

PostgreSQL

SELECT * FROM employees WHERE xmin [1] [2];

Drag options to blanks, or click blank then click option'

A<

B>

C1000

Dtxid_current()

Attempts:

3 left

💡 Hint

Common Mistakes

Using '>' instead of '<' or a fixed number instead of txid_current().

In MVCC, rows with xmin less than the current transaction ID (txid_current()) are visible.

5fill in blank

hard

Fill all three blanks to write a query that deletes rows visible to the current transaction and committed before it.

PostgreSQL

DELETE FROM employees WHERE ctid IN (SELECT ctid FROM employees WHERE xmin [1] [2] AND xmax = [3]);

Drag options to blanks, or click blank then click option'

A<

B>

C0

Dtxid_current()

Attempts:

3 left

💡 Hint

Common Mistakes

Using wrong comparison operators or wrong values for xmax.

This query deletes rows where xmin is less than the current transaction ID and xmax is 0 (not deleted).