Recently, on irc, there were couple of cases where someone wanted to use uuid as datatype for their primary key.
I opposed, and tried to explain, but IRC doesn't really allow for longer texts, so figured I'll write a blogpost.
Tag: primary key
Waiting for PostgreSQL 12 – Support foreign keys that reference partitioned tables
On 3rd of April 2019, Alvaro Herrera committed patch:
Support foreign keys that reference partitioned tables Previously, while primary keys could be made on partitioned tables, it was not possible to define foreign keys that reference those primary keys. Now it is possible to do that. Author: Álvaro Herrera Discussion: https://postgr.es/m/20181102234158.735b3fevta63msbj@alvherre.pgsql
Continue reading Waiting for PostgreSQL 12 – Support foreign keys that reference partitioned tables
Generate short, random, textual IDs
Couple of people asked, on irc, about how to generate unique, short, ids for rows.
Since I wrote code for this for explain.depesz.com, I figured that instead of pointing to sources, and letting everyone to extract the functions, I will, instead, write a set of functions that what is needed in reusable way.
“= 123″ vs. “= ‘depesz’” – followup
Yesterday I wrote about selects on int4 vs. texts.
One of the comments that caught my attention was question about index creation time. So, let's see…
“= 123” vs. “= ‘depesz'”. What is faster?
There is this idea that normal form in databases require you to use integer, auto incrementing, primary keys.
The idea was discussed by many people, I will just point you to series of three blog posts on the subject by Josh Berkus ( part 1, 2 and 3, and reprise).
One of the points that proponents of surrogate keys (i.e. those based on integer and sequences) raise is that comparing integers is faster than comparing texts. So,
SELECT * FROM users WHERE id = 123
is faster than
SELECT * FROM users WHERE username = 'depesz'
Is it?
Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index
On 25th of January, Tom Lane committed patch:
Implement ALTER TABLE ADD UNIQUE/PRIMARY KEY USING INDEX. This feature allows a UNIQUE OR pkey CONSTRAINT TO be created USING an already-existing UNIQUE INDEX. While the CONSTRAINT isn't very functionally different from the bare index, it's nice TO be able TO do that FOR documentation purposes. The main advantage OVER just issuing a plain ALTER TABLE ADD UNIQUE/PRIMARY KEY IS that the INDEX can be created WITH CREATE INDEX CONCURRENTLY, so that there IS NOT a long INTERVAL WHERE the TABLE IS locked against updates. ON the way, refactor SOME OF the code IN DefineIndex() AND index_create() so that we don't have to pass through those functions in order to create the index constraint's catalog entries. Also, IN parse_utilcmd.c, pass around the ParseState pointer IN struct CreateStmtContext TO save ON notation, AND ADD error location pointers TO SOME error reports that didn't have one before. Gurjeet Singh, reviewed by Steve Singer and Tom Lane
Continue reading Waiting for 9.1 – Add UNIQUE/PRIMARY KEY with index
Waiting for 9.1 – Recognize functional dependency on primary keys.
Yesterday (August, 7th), Tom Lane committed:
Log Message: ----------- Recognize functional dependency on primary keys. This allows a table's other columns to be referenced without listing them in GROUP BY, so long as the primary key column(s) are listed in GROUP BY. Eventually we should also allow functional dependency on a UNIQUE constraint when the columns are marked NOT NULL, but that has to wait until NOT NULL constraints are represented in pg_constraint, because we need to have pg_constraint OIDs for all the conditions needed to ensure functional dependency. Peter Eisentraut, reviewed by Alex Hunsaker and Tom Lane
Continue reading Waiting for 9.1 – Recognize functional dependency on primary keys.
Waiting for 8.5 – deferrable uniqueness
On 29th of July ( I know, there is a long backlog in waiting for 8.5 series. I'm working on it :), Tom Lane committed patch by Dean Rasheed:
Support deferrable uniqueness constraints. The current implementation fires an AFTER ROW trigger for each tuple that looks like it might be non-unique according to the index contents at the time of insertion. This works well as long as there aren't many conflicts, but won't scale to massive unique-key reassignments. Improving that case is a TODO item. Dean Rasheed