Tool Lending Library

Fungible tools which people check out to use.
Track this correctly.

Live demo!

...almost worked

PostgreSQL 11?

Nope :(

Yes!

No!

How?

A trigger

...with its own function

...for each table

...for each ASSERTION

Let's get started.

Trigger function for reaching from available to checked_out

```sql
                        CREATE OR REPLACE FUNCTION check_checked_out_from_available()
                        RETURNS TRIGGER
                        LANGUAGE plpgsql
                        AS $$
                        DECLARE tool TEXT;
                        BEGIN
                            SELECT INTO tool a.tool_type
                            FROM available_tool a JOIN checked_out_tool d
                                ON (
                                    a.tool_id = d.tool_id AND
                                    d.tool_id = NEW.tool_id
                                )
                            GROUP BY a.available, a.tool_type
                            HAVING count(*) > a.available;

IF FOUND THEN
                                RAISE 'People have already checked out all the %s.', tool
                                    USING
                                        ERRCODE = 'integrity_constraint_violation',
                                        HINT = 'Consider getting more.';
                            END IF;
                            RETURN NEW;
                        END;
                        $$;
                        ```

And the trigger

Trigger function for reaching out from available to checked_out

```sql
                        CREATE OR REPLACE FUNCTION check_available_from_checked_out()
                        RETURNS TRIGGER
                        LANGUAGE plpgsql
                        AS $$
                        DECLARE
                            r RECORD;
                        BEGIN
                            SELECT tool_type, available INTO r
                            FROM available_tool a JOIN checked_out_tool d USING (tool_id)
                            WHERE NEW.tool_id = d.tool_id
                            GROUP BY d.tool_id, a.tool_type, a.available
                            HAVING count(*) > a.available;

IF FOUND THEN
                                RAISE 'There are already % checked-out %s.', r.available, r.tool_type
                                    USING
                                        ERRCODE = 'integrity_constraint_violation',
                                        HINT = 'Some of those tools need to come back first';
                            END IF;
                            RETURN NEW;
                        END;
                        $$;

```

And its trigger

Another live demo!

How did we get here?

A Relational Model for Large Shared Data Banks

Nah!

Serializable Snapshot Isolation in PostgreSQL
Dan Ports and Kevin Grittner

From: 	Peter Eisentraut 
To: 	pgsql-hackers 
Subject: 	[PATCH] SQL assertions prototype
Date: 	2013-11-15 03:30:16
Message-ID: 	1384486216.5008.17.camel@vanquo.pezone.net

Note date

Date: 2013-11-15 03:30:16

Years pass

Then

From: 	Joe Wildish 
To: 	PostgreSQL Hackers 
Subject: 	Re: Implementing SQL ASSERTION
Date: 	2018-01-14 23:33:08
Message-ID: 	985632EC-3E39-4C51-B47A-ED0ABF63D64F@elusive.cx
Views: 	Raw Message | Whole Thread | Download mbox

Hackers,

Attached is a WIP patch for SQL assertion. I am posting it for anyone who might
be interested in seeing it, for comments/feedback, and to see if others are keen
to collaborate on taking it further. It is not near production-ready (see
thoughts on that below).

A few months later...

From: 	Joe Wildish 
To: 	David Fetter , PostgreSQL Hackers 
Subject: 	Re: Implementing SQL ASSERTION
Date: 	2018-04-29 18:18:00

Attached is a rebased patch for the prototype.

Cheers,
-Joe

What's in the box?

Documentation

pg_catalog

Information schema

SQL grammar

CREATE, ALTER, and DROP support

planner and executor

Questions?

Comments?

Thanks!
Merci!
谢谢！
ありがとう！
¡Gracias!
Teşekkürler!
धन्यवाद
Спасибо!