No Clocks

In PostgreSQL, a generated column is a special type of column whose values are automatically calculated based on expressions or values from other columns. A generated column is referred to as a computed column in the SQL Server or a virtual column in Oracle .

There are two kinds of generated columns: Stored: A stored generated column is calculated when it is inserted or updated and occupies storage space. Virtual: A virtual generated column is computed when it is read and does not occupy storage space.

A virtual generated column is like a view, whereas a stored generated column is similar to a materialized view. Unlike a material view, PostgreSQL automatically updates data for stored generated columns.

PostgreSQL currently implements only stored generated columns.

In PostgreSQL, a sequence is a database object that allows you to generate a sequence of unique integers. Typically, you use a sequence to generate a unique identifier for a primary key in a table. Additionally, you can use a sequence to generate unique numbers across tables. To create a new sequence, you use the CREATE SEQUENCE statement.

Listing all sequences in a database To list all sequences in the current database, you use the following query: SELECT relname sequence_name FROM pg_class WHERE relkind = 'S';

PostgreSQL version 10 introduced a new constraint GENERATED AS IDENTITY that allows you to automatically assign a unique number to a column.

The GENERATED AS IDENTITY constraint is the SQL standard-conforming variant of the good old SERIAL column.

The following illustrates the syntax of the GENERATED AS IDENTITY constraint: column_name type GENERATED { ALWAYS | BY DEFAULT } AS IDENTITY[ ( sequence_option ) ]

In this syntax: The type can be SMALLINT, INT, or BIGINT. The GENERATED ALWAYS instructs PostgreSQL to always generate a value for the identity column. If you attempt to insert (or update) values into the GENERATED ALWAYS AS IDENTITY column, PostgreSQL will issue an error. The GENERATED BY DEFAULT instructs PostgreSQL to generate a value for the identity column. However, if you supply a value for insert or update, PostgreSQL will use that value to insert into the identity column instead of using the system-generated value.

PostgreSQL allows a table to have more than one identity column. Like the SERIAL, the GENERATED AS IDENTITY constraint also uses the SEQUENCE object internally.

To fix the error, you can use the OVERRIDING SYSTEM VALUE clause as follows: INSERT INTO color (color_id, color_name) OVERRIDING SYSTEM VALUE VALUES(2, 'Green');

Alternatively, you can use GENERATED BY DEFAULT AS IDENTITY instead.

Because the GENERATED AS IDENTITY constraint uses the SEQUENCE object, you can specify the sequence options for the system-generated values.

For example, you can specify the starting value and the increment as follows: DROP TABLE color; CREATE TABLE color ( color_id INT GENERATED BY DEFAULT AS IDENTITY (START WITH 10 INCREMENT BY 10), color_name VARCHAR NOT NULL );

In this example, the system-generated value for the color_id column starts with 10 and the increment value is also 10.

Twice a year, the tidyverse team sets a week aside for “spring cleaning,” bringing all of our R packages up to snuff with the most current tooling and standardizing various bits of our development process. Some of these updates can happen by calling a single function, while others are much more involved. One of those more involved updates is updating erroring code, transitioning away from base R (e.g.  stop()), rlang (e.g.  rlang::abort()), glue, and homegrown combinations of them. cli’s new syntax is easier to work with as a developer and more visually pleasing as a user.

In some cases, transitioning is almost as simple as Finding + Replacing rlang::abort() to cli::cli_abort():

# before: rlang::abort("`save_pred` can only be used if the initial results saved predictions.") # after: cli::cli_abort("{.arg save_pred} can only be used if the initial results saved predictions.")

In others, there’s a mess of ad-hoc pluralization, paste0()s, glue interpolations, and other assorted nonsense to sort through:

Thus was born clipal1, a (now-superseded) R package that allows users to select erroring code, press a keyboard shortcut, wait a moment, and watch the updated code be inlined in to the selection.

clipal was a huge boost for us in the most recent spring cleaning. Depending on the code being updated, these erroring calls used to take 30 seconds to a few minutes. With clipal, though, the model could usually get the updated code 80% or 90% of the way there in a couple seconds. Up to this point, irritated by autocomplete and frustrated by the friction of copying and pasting code and typing out the same bits of context into chats again and again, I had been relatively skeptical that LLMs could make me more productive. After using clipal for a week, though, I began to understand how seamlessly LLMs could automate the cumbersome and uninteresting parts of my work.

clipal itself is now superseded by pal, a more general solution to the problem that clipal solved. I’ve also written two additional packages like pal that solve two other classes of pal-like problems using similar tools, ensure and gander. In this post, I’ll write a bit about how I’ve used a pair of tools in three experiments that have made me much more productive as an R developer

After using clipal during our spring cleaning, I approached another spring cleaning task for the week: updating testing code. testthat 3.0.0 was released in 2020, bringing with it numerous changes that were both huge quality of life improvements for package developers and also highly breaking changes. While some of the task of converting legacy unit testing code to testthat 3e is relatively straightforward, other components can be quite tedious. Could I do the same thing for updating to testthat 3e that I did for transitioning to cli? I sloppily threw together a sister package to clipal that would convert tests for errors to snapshot tests, disentangle nested expectations, and transition from deprecated functions like ⁠expect_known_*(). ⁠(If you’re interested, the current prompt for that functionality is here.) That sister package was also a huge boost for me, but the package reused as-is almost every piece of code from clipal other than the prompt. Thus, I realized that the proper solution would provide all of this scaffolding to attach a prompt to a keyboard shortcut, but allow for an arbitrary set of prompts to help automate these wonky, cumbersome tasks.

The next week, pal was born. The pal package ships with three prompts centered on package development: the cli pal and testthat pal mentioned previously, as well as the roxygen pal, which drafts minimal roxygen documentation based on a function definition. Here’s what pal’s interface looks like now:

ensure

While deciding on the initial set of prompts that pal would include, I really wanted to include some sort of “write unit tests for this function” pal. To really address this problem, though, requires violating two of pal’s core assumptions:

All of the context that you need is in the selection and the prompt. In the case of writing unit tests, it’s actually pretty important to have other pieces of context. If a package provides some object type potato, in order to write tests for some function that takes potato as input, it’s likely very important to know how potatoes are created and the kinds of properties they have. pal’s sister package for writing unit tests, ensure, can thus “see” the rest of the file that you’re working on, as well as context from neighboring files like other .R source files, the corresponding test file, and package vignettes, to learn about how to interface with the function arguments being tested.

The LLM’s response can prefix, replace, or suffix the active selection in the same file. In the case of writing unit tests for R, the place that tests actually ought to go is in a corresponding test file in tests/testthat/. Via the RStudio API, ensure can open up the corresponding test file and write to it rather than the source file where it was triggered from.3

gander

It is recommended to use the same structure in your tests/testthat/ directory as your R/ directory, i.e., a test file for every .R file.