フィードアグリゲーター

Laurenz Albe: Triggers to enforce constraints

planet postgresql - 2019-04-25(木) 17:00:23
© Laurenz Albe 2019

 

Sometimes you want to enforce a condition on a table that cannot be implemented by a constraint. In such a case it is tempting to use triggers instead. This article describes how to do this and what to watch out for.

It will also familiarize you with the little-known PostgreSQL feature of “constraint triggers”.

A test case

Suppose we have a table of prisons and a table of prison guards:

CREATE SCHEMA jail_app; CREATE TABLE jail_app.prison ( prison_id integer PRIMARY KEY, prison_name text NOT NULL ); INSERT INTO jail_app.prison (prison_id, prison_name) VALUES (1, 'Karlau'), (2, 'Stein'); CREATE TABLE jail_app.guard ( guard_id integer PRIMARY KEY, guard_name text NOT NULL ); INSERT INTO jail_app.guard (guard_id, guard_name) VALUES (41, 'Alice'), (42, 'Bob'), (43, 'Chris');

Then we have a junction table that stores which guard is on duty in which prison:

CREATE TABLE jail_app.on_duty ( prison_id integer REFERENCES prison, guard_id integer REFERENCES guard, PRIMARY KEY (prison_id, guard_id) ); INSERT INTO jail_app.on_duty (prison_id, guard_id) VALUES (1, 41), (2, 42), (2, 43);

So Alice is on duty in Karlau, and Bob and Chris are on duty in Stein.

Naïve implementation of a constraint as trigger

As guards go on and off duty, rows are added to and deleted from on_duty. We want to establish a constraint that at least one guard has to be on duty in any given prison.

Unfortunately there is no way to write this as a normal database constraint (if you are tempted to write a CHECK constraint that counts the rows in the table, think again).

But it would be easy to write a BEFORE DELETE trigger that ensures the condition:

CREATE FUNCTION jail_app.checkout_trig() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF (SELECT count(*) FROM jail_app.on_duty WHERE prison_id = OLD.prison_id ) < 2 THEN RAISE EXCEPTION 'sorry, you are the only guard on duty'; END IF; RETURN OLD; END;$$; CREATE TRIGGER checkout_trig B[...]
カテゴリー: postgresql

Laurenz Albe: Triggers to enforce constraints

planet postgresql - 2019-04-25(木) 17:00:23
© Laurenz Albe 2019

 

Sometimes you want to enforce a condition on a table that cannot be implemented by a constraint. In such a case it is tempting to use triggers instead. This article describes how to do this and what to watch out for.

It will also familiarize you with the little-known PostgreSQL feature of “constraint triggers”.

A test case

Suppose we have a table of prisons and a table of prison guards:

CREATE SCHEMA jail_app; CREATE TABLE jail_app.prison ( prison_id integer PRIMARY KEY, prison_name text NOT NULL ); INSERT INTO jail_app.prison (prison_id, prison_name) VALUES (1, 'Karlau'), (2, 'Stein'); CREATE TABLE jail_app.guard ( guard_id integer PRIMARY KEY, guard_name text NOT NULL ); INSERT INTO jail_app.guard (guard_id, guard_name) VALUES (41, 'Alice'), (42, 'Bob'), (43, 'Chris');

Then we have a junction table that stores which guard is on duty in which prison:

CREATE TABLE jail_app.on_duty ( prison_id integer REFERENCES prison, guard_id integer REFERENCES guard, PRIMARY KEY (prison_id, guard_id) ); INSERT INTO jail_app.on_duty (prison_id, guard_id) VALUES (1, 41), (2, 42), (2, 43);

So Alice is on duty in Karlau, and Bob and Chris are on duty in Stein.

Naïve implementation of a constraint as trigger

As guards go on and off duty, rows are added to and deleted from on_duty. We want to establish a constraint that at least one guard has to be on duty in any given prison.

Unfortunately there is no way to write this as a normal database constraint (if you are tempted to write a CHECK constraint that counts the rows in the table, think again).

But it would be easy to write a BEFORE DELETE trigger that ensures the condition:

CREATE FUNCTION jail_app.checkout_trig() RETURNS trigger LANGUAGE plpgsql AS $$BEGIN IF (SELECT count(*) FROM jail_app.on_duty WHERE prison_id = OLD.prison_id ) < 2 THEN RAISE EXCEPTION 'sorry, you are the only guard on duty'; END IF; RETURN OLD; END;$$; CREATE TRIGGER checkout_trig B[...]
カテゴリー: postgresql

Voices of the ElePHPant: Interview with Derek Binkley

phpdeveloper.org - 2019-04-25(木) 04:00:01

@derekb_wi

Show Notes Derek’s Blog Taming Your Data with Elasticsearch

Audio

This episode is sponsored by

The post Interview with Derek Binkley appeared first on Voices of the ElePHPant.

カテゴリー: php

Webslesson: Login Registration System with Email Verification in Laravel 5.8

phpdeveloper.org - 2019-04-25(木) 04:00:01

Do you know you can make complete login registration system in Laravel 5.8 with email verification in single artisan command run. If you not know then this post will help you to learn Laravel login authentication and register with step by step guide from scratch. Because Laravel 5.8 has default Logi...

カテゴリー: php

Tom&aacute;&scaron; Votruba Blog: Hidden Gems of PHP Packages: Static Analysis Results Baseliner

phpdeveloper.org - 2019-04-25(木) 04:00:01

Have you used PHPStan, Psalm or Easy Coding Standard on a very old project and got 10 000+ errors?

Do you wish to skip fixing these 10 000 errors and check new code only?

Yes and yes? You'll love SARB.

カテゴリー: php

Derick Rethans: Significant Symbols

phpdeveloper.org - 2019-04-25(木) 04:00:01
Significant Symbols London, UK Tuesday, April 23rd 2019, 09:27 BST Last week a person on the #php IRC channel on freenode, mentioned that he had problems loading some extensions with his self-compiled PHP binary. For example, trying to acti...
カテゴリー: php

murze.be: ★ When to use Gate::after in Laravel

phpdeveloper.org - 2019-04-25(木) 04:00:01

In a Laravel app policies are a great way to organize authorization logic that revolves around models. For the longest time, I've been using Gate::before to allow superadmins to do anything they want. While working on a new app, it finally clicked how Gate::after can be useful too. I'd like to shar...

カテゴリー: php

Umair Shahid: Postgres is the coolest database – Reason #2: The License

planet postgresql - 2019-04-24(水) 23:31:40
Legal documents = SCARY!! That’s the typical equation, and it’s true – except when it comes to PostgreSQL. Let me explain… I have been told by both prospects and clients, that when they sit down to negotiate terms with Oracle, they are faced with more lawyers than they have engineers. No wonder one shudders at […]
カテゴリー: postgresql

Daniel Vérité: OIDs demoted to normal columns: a glance at the past

planet postgresql - 2019-04-24(水) 21:17:04

In PostgreSQL 12, oid columns in system tables will loose their “special” nature, and the optional clause WITH OIDS will disapppear from CREATE TABLE. As a concrete consequence, oid will now be visible when running select * from the catalogs that have OIDs, as well as when querying information_schema.columns, or with \d inside psql. Until now they were hidden, as are all system columns such as xmin or xmax.

The commit message in the source repository mentions this reason for the change:

author Andres Freund <andres (at) anarazel (dot) de>
Wed, 21 Nov 2018 01:36:57 +0200 (15:36 -0800)
[…]
Remove WITH OIDS support, change oid catalog column visibility.
[…]
The fact that the oid column was not an ordinary column necessitated a significant amount of special case code to support oid columns. That already was painful for the existing, but upcoming work aiming to make table storage pluggable, would have required expanding and duplicating that “specialness” significantly.

Pluggable storage is a step towards the much expected zheap, as well as other formats in the future.

Looking back years ago, this can be seen in the perspective of previous changes, which also go in the direction of obsoleting OIDs:

  • 7.2 (Feb 2002), the oid column becomes optional.
  • 8.0 (Jan 2005), the default_with_oids parameter is created.
  • 8.1 (Nov 2005), default_with_oids is now false by default.

But why was the OID as a special column invented in the first place? Originally, as the name “Object ID” suggests, the OID is related to object orientation.

A bit of history: object orientation in Postgres

In the mid-80’s, the object orientation concept was surfacing, with OO languages like C++ in their early stages of design. In the database world, there was also this idea that maybe in the future, people will want to look at their data primarily through OO lenses.

This can explain why in early versions of Postgres, when developed as a research project at the University of Berkeley, object orientation was a significant component.

In programming languages

[...]
カテゴリー: postgresql

Andreas 'ads' Scherbaum: PGConf.EU 2019 - Call for Papers and Call for Sponsorships

planet postgresql - 2019-04-24(水) 19:46:36

PostgreSQL Conference Europe 2019 takes place in Milan, Italy, on October 15-18. Our Call for Papers is now open.

We are accepting proposals for talks in English. Each session will last 45 minutes, and may be on any topic related to PostgreSQL. The submission deadline is July 15th. Selected speakers will be notified before August 10th, 2019.

Please submit your proposals by going to 2019.pgconf.eu/callforpapers/ and following the instructions.

The proposals will be considered by committee who will produce a schedule to be published nearer the conference date. The members of the committee are listed on the website linked above.

All selected speakers will get free entry to the conference (excluding training sessions). We do not in general cover travel and accommodations for speakers, but may be able to do so in limited cases. If you require assistance with funding to be able to attend, please make a note of this in the submission notes field or contact us separately before the submission deadline.

And finally, our Call for Sponsorship is also open. Take your chance to present your services or products to the PostgreSQL community - or see it as a give back opportunity. All sponsorship levels also include one or more free entrance tickets, depending on the level. Please head to 2019.pgconf.eu/becomesponsor/ for more details.

Please note that the hotel room situation in Milan is tense, we advise you to book your room as early as possible. We are working with hotels around the venue to provide more options.

As usual, if you have any questions, don't hesitate to contact us at contact@pgconf.eu.

We look forward to seeing you in Milan in October!

カテゴリー: postgresql

Hubert 'depesz' Lubaczewski: Waiting for PostgreSQL 12 – Support foreign keys that reference partitioned tables

planet postgresql - 2019-04-24(水) 18:48:56
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"
カテゴリー: postgresql

Viorel Tabara: Benchmarking Managed PostgreSQL Cloud Solutions - Part Three: Google Cloud

planet postgresql - 2019-04-24(水) 18:48:00

In this 3rd part of Benchmarking Managed PostgreSQL Cloud Solutions, I took advantage of Google’s GCP free tier offering. It has been a worthwhile experience and as a sysadmin spending most of his time at the console I couldn’t miss the opportunity of trying out cloud shell, one of the console features that sets Google apart from the cloud provider I’m more familiar with, Amazon Web Services.

To quickly recap, in Part 1 I looked at the available benchmark tools and explained why I chose AWS Benchmark Procedure for Aurora. I also benchmarked Amazon Aurora for PostgreSQL version 10.6. In Part 2 I reviewed AWS RDS for PostgreSQL version 11.1.

During this round, the tests based on the AWS Benchmark Procedure for Aurora will be run against Google Cloud SQL for PostgreSQL 9.6 since the version 11.1 is still in beta.

Cloud Instances Prerequisites

As mentioned in the previous two articles, I opted for leaving PostgreSQL settings at their cloud GUC defaults, unless they prevent tests from running (see further down below). Recall from previous articles that the assumption has been that out of the box the cloud provider should have the database instance configured in order to provide a reasonable performance.

The AWS pgbench timing patch for PostgreSQL 9.6.5 applied cleanly to Google Cloud version of PostgreSQL 9.6.10.

Using the information Google put out in their blog Google Cloud for AWS Professionals I matched up the specs for the client and the target instances with respect to the Compute, Storage, and Networking components. For example, Google Cloud equivalent of AWS Enhanced Networking is achieved by sizing the compute node based on the formula:

max( [vCPUs x 2Gbps/vCPU], 16Gbps) Related resources  ClusterControl for PostgreSQL  Benchmarking Managed PostgreSQL Cloud Solutions - Part Two: Amazon RDS  Benchmarking Managed PostgreSQL Cloud Solutions - Part One: Amazon Aurora

When it comes to setting up the target database instance, similarly to AWS, Google Cloud allows no replicas, however, the storage is encr

[...]
カテゴリー: postgresql

@seregazhuk: Building a RESTful API Using ReactPHP: JWT Authentication

phpdeveloper.org - 2019-04-24(水) 06:00:01

Previously we have used Basic HTTP Authentication to protect our RESTful API. This authentication method is pretty simple, but in most cases, it can be used only in the internal network with server-to-server communication. For example, we can’t store Basic Authentication credentials to mobile d...

カテゴリー: php

Webslesson: How to Join Multiple Table in Laravel 5.8

phpdeveloper.org - 2019-04-24(水) 01:00:02

This is one more tutorial on Laravel 5.8 framework and in this post you can find how to join multiple table in Laravel 5.8 framework. Here we will take example of fetch data from two or more table by using inner join in Laravel 5.8 and display on web page. If you are looking for learn how to make in...

カテゴリー: php

Laravel Daily: Taylor Otwell: “Thin” Controllers, “Fat” Models Approach

phpdeveloper.org - 2019-04-24(水) 01:00:02

I was listening to recent podcast by Taylor Otwell, Laravel Snippet episode 11, where he touched on the debate in Laravel community on where you should put your code logic - in Controllers, Models, or Services. Here's what he said.
The post Taylor Otwell: “Thin” Controllers, “FatâÂ...

カテゴリー: php

That Podcast: Episode 59: The First One in 2019

phpdeveloper.org - 2019-04-24(水) 01:00:02

PHP Town Hall - Symfony Round UpMatthew TraskAmanda FolsonRyan WeaverSymfony Roadmap

カテゴリー: php

Delicious Brains: Behind-the-Scenes: Replacing All of WP Migrate DB Pro’s jQuery with React

phpdeveloper.org - 2019-04-24(水) 01:00:02

My latest task for WP Migrate DB Pro is creating a new user interface and rewriting all the JavaScript for the plugin and addons. It is no small feat, let me tell you. Our current codebase consists of thousands of lines of relatively unorganized jQuery code. I’ve given a talk about its current ...

カテゴリー: php

422 Unprocessable Entity

planet PHP - 2019-04-24(水) 00:00:00

The 422 Unprocessable Entity status-code does not appear in the base HTTP specification. Like 102 and 207 it’s part of the WebDAV specification, which is an extension to HTTP.

Unlike the other two, the 422 code has a good general use-case, and is often used in Web API’s.

The 422 code implies that the server understood the general syntax of the request, but the contents were incorrect. For example, a server might expect POST requests that uses the application/json format. If the request is broken JSON, it might be more appropriate to send back the 400 status code, but if the request is valid JSON but the request doesn’t pass validation (via for example json-schema) returning 422 might be better.

If there was something wrong with the request body, you can use the following rules to figure out what to send back:

  • If the Content-Type was not supported, use 415.
  • Else: If the request was not parsable (broken JSON, XML), use 400 Bad Request.
  • Else: If the request was parsable but the specific contents of the payload were wrong (due to validation or otherwise), use 422
Example POST /new-article HTTP/1.1 Content-Type: application/json { "title": "Hello world!"} HTTP/1.1 422 Unprocessable Entity Content-Type: application/problem+json { "type" : "https://example/errors/missing-property", "status": 422, "title": "Missing property: body" } References
カテゴリー: php

Interview with Derek Binkley

planet PHP - 2019-04-23(火) 20:00:00

@derekb_wi Show Notes

Audio

This episode is sponsored by


The post Interview with Derek Binkley appeared first on Voices of the ElePHPant.

カテゴリー: php

ページ