Skip to content

Instantly share code, notes, and snippets.

@kaid
Forked from sycobuny/README.md
Created March 17, 2016 07:03
Show Gist options
  • Save kaid/3e98c6c95e719c758d05 to your computer and use it in GitHub Desktop.
Save kaid/3e98c6c95e719c758d05 to your computer and use it in GitHub Desktop.
A problem I'm encountering with pgTAP (http://pgtap.org/)

The Problem

I'm hoping to test a function that I will eventually use in a DOMAIN to verify that any given JSONB object adheres to a certain loose set of constraints (other JSONB types will be built on constraints specified in this DOMAIN).

As the DOMAIN will already fail anything that doesn't pass the function, I did not write the function to raise exceptions, but rather to raise warnings, so that the reason a given object failed could be seen/inspected by the end user.

I also, however, want to test that these warnings are being emitted per the description of the function, so I tried to write a pgtap throws_ok() test to cover it, but, as it is a WARNING and not an EXCEPTION, nothing is thrown. I am curious whether it's possible to actually test for something like a non-fatal WARNING (also, eventually, I will want to test the HINT value as well), or, failing that, to elevate a WARNING at run-time such that it is treated as an exception case (this would be akin to, from the Perl world, use warnings FATAL => all;).

Or am I completely nuts and all of these failures should just be done with RAISE EXCEPTION instead of RAISE WARNING from the beginning?

$ foreman start -f Procfile.test
19:33:03 pg.1 | started with pid 84863
19:33:03 test.1 | started with pid 84864
19:33:03 pg.1 | LOG: database system was shut down at 2015-08-02 19:23:13 EDT
19:33:03 pg.1 | LOG: MultiXact member wraparound protections are now enabled
19:33:03 pg.1 | LOG: database system is ready to accept connections
19:33:03 pg.1 | LOG: autovacuum launcher started
19:33:04 test.1 | ok 1 - Function VALIDATE_SPECIFICATION(JSONB, JSONB) should exist
19:33:04 test.1 | ok 2 - Valid objects are validated
19:33:04 pg.1 | WARNING: Missing required key "oops"
19:33:04 test.1 | ok 3 - Invalid objects are rejected
19:33:04 pg.1 | WARNING: Missing required key "oops"
19:33:04 pg.1 | CONTEXT: SQL statement "
19:33:04 pg.1 | SELECT public.VALIDATE_SPECIFICATION(
19:33:04 pg.1 | '{"example": "correct"}',
19:33:04 pg.1 | '[
19:33:04 pg.1 | {"key": "example", "type": "string", "required": true},
19:33:04 pg.1 | {"key": "oops", "type": "number", "required": true}
19:33:04 pg.1 | ]'
19:33:04 pg.1 | );
19:33:04 pg.1 | "
19:33:04 pg.1 | PL/pgSQL function throws_ok(text,character,text,text) line 16 at EXECUTE statement
19:33:04 test.1 | not ok 4 - Throws useful error messages on objects with missing keys
19:33:04 test.1 | # Failed test 4: "Throws useful error messages on objects with missing keys"
19:33:04 test.1 | # caught: no exception
19:33:04 test.1 | # wanted: 42804
19:33:04 test.1 | # Looks like you failed 1 test of 4
19:33:05 test.1 | exited with code 0
19:33:05 system | sending SIGTERM to all processes
19:33:05 pg.1 | LOG: received smart shutdown request
19:33:05 pg.1 | LOG: autovacuum launcher shutting down
19:33:05 pg.1 | LOG: shutting down
19:33:05 pg.1 | LOG: database system is shut down
19:33:05 pg.1 | exited with code 0
\i test/etc/pgtap_setup.sql
SET client_min_messages = ERROR;
SELECT plan(4);
/**
* Validate the presence of the function
*
* If this fails, the function was not created at all. Note that the datatypes
* as well as the function name are all specified as lower-case in the actual
* test itself; this is required for proper quoting per the SQL spec.
*/
SELECT has_function(
'public', 'validate_specification', ARRAY['jsonb', 'jsonb'],
'Function VALIDATE_SPECIFICATION(JSONB, JSONB) should exist'
);
/**
* Validate that correct object specifications work
*
* This is a general usage test, but also doubles as a way to check that keys
* specified as optional are, in fact, optional.
*/
SELECT is(
public.VALIDATE_SPECIFICATION(
'{"example": "correct"}',
'[
{"key": "example", "type": "string", "required": true},
{"key": "optional", "type": "number", "required": false}
]'
),
TRUE,
'Valid objects are validated'
);
/**
* Validate that required keys are required
*
* In this test, a required key was described in the specification, but not
* given in the object to be validated, so it should fail.
*/
SELECT is(
public.VALIDATE_SPECIFICATION(
'{"example": "correct"}',
'[
{"key": "example", "type": "string", "required": true},
{"key": "oops", "type": "number", "required": true}
]'
),
FALSE,
'Invalid objects are rejected'
);
/**
* Validate error messages given off by missing keys
*
* The validation test above should guarantee that invalid objects with
* missing keys are rejected. This test should validate that it emits a proper
* warning message which indicates why this is the case.
*/
SELECT throws_ok(
$ERR$
SELECT public.VALIDATE_SPECIFICATION(
'{"example": "correct"}',
'[
{"key": "example", "type": "string", "required": true},
{"key": "oops", "type": "number", "required": true}
]'
);
$ERR$,
'42804',
'Missing required key "oops"',
'Throws useful error messages on objects with missing keys'
);
\i test/etc/pgtap_teardown.sql
CREATE OR REPLACE
FUNCTION VALIDATE_SPECIFICATION(JSONB, JSONB)
RETURNS BOOLEAN
LANGUAGE PLPGSQL
AS $PGSQL$
DECLARE
-- extra keys (first use), required keys (second use)
keys TEXT[];
-- iteration variable for checking each existing field
spec RECORD;
-- the return state, i.e., whether the object passes the specification
ret BOOLEAN := TRUE;
BEGIN
-- find any keys which are not covered by the spec
SELECT ARRAY_AGG(jsonb_object_keys) INTO keys
FROM
JSONB_OBJECT_KEYS($1) LEFT JOIN
JSONB_TO_RECORDSET($2) AS ("key" TEXT, t TEXT, r BOOLEAN) ON
jsonb_object_keys = jsonb_to_recordset.key
WHERE jsonb_to_recordset.key IS NULL;
-- if we got any keys from the last query, then we've got extra keys
IF ARRAY_LENGTH(keys, 1) > 0 THEN
RAISE WARNING 'Extra key(s): %', ARRAY_TO_STRING(keys, ', ')
USING ERRCODE = 'datatype_mismatch';
-- this is a failure condition
ret := FALSE;
END IF;
-- collect object keys for quick reference, reusing the 'keys' var
SELECT ARRAY_AGG(jsonb_object_keys) INTO keys
FROM JSONB_OBJECT_KEYS($1);
-- iterate over the specification entries
FOR spec IN
SELECT *
FROM JSONB_TO_RECORDSET($2) AS ("key" TEXT, "type" TEXT, required BOOLEAN)
LOOP
-- if the key declared by the spec is not contained in the object
IF NOT (ARRAY[spec.key] <@ keys) THEN
-- it's no big deal if the spec says it's not required
CONTINUE WHEN NOT spec.required;
-- otherwise make sure to tell the user why this will fail
RAISE WARNING 'Missing required key "%"', spec.key
USING ERRCODE = 'datatype_mismatch';
-- and make sure we'll return that failure state
ret := FALSE;
END IF;
-- check to make sure the JSON type of the given entry is correct
IF JSONB_TYPEOF($1 #> ARRAY[spec.key]) <> spec.type THEN
RAISE WARNING
'Bad value ("%" is type `%`) for key "%"',
$1 #>> ARRAY[spec.key],
JSONB_TYPEOF($1 #> ARRAY[spec.key]),
spec.key
USING
ERRCODE = 'datatype_mismatch',
HINT = 'The proper datatype is `' || spec.type || '`';
-- mismatched types are a failure condition
ret := FALSE;
END IF;
END LOOP;
-- give the calling function our final state
RETURN ret;
END;
$PGSQL$;
COMMENT ON FUNCTION VALIDATE_SPECIFICATION(JSONB, JSONB) IS $MARKDOWN$
Generic JSONB Structure Validation
==================================
The first argument to this function is the JSONB element to be validated. The
second argument is the structure used to validate it, as a JSONB array of
objects. Though the second argument is not validated (to make it possible to
send in extended objects), if each object does not have a minimum of the
following keys, the behavior is undocumented (but the function will likely
blow up):
* `key` - A field name that should be present in the function. It must be a
string.
* `type` - A valid JSONB subtype. See the [docs][] for `jsonb_typeof()` for
valid candidate values for this field.
* `required` - A boolean value indicating whether or not the `key` **must**
be present in the object being validated. If it is false, the `key` is
optional.
Note that, though some fields are *required*, any fields that are not listed
**must** be *absent*, or else a warning will be raised and the object will be
considered not to match.
Diagnostics
-----------
* 'Extra keys: x, y, ...' - When a given specification row does not describe
all of the elements in the object being validated.
* 'Missing required key "x"' - When a given specification row has `required`
set to `true`, but the object being validated does not contain that key.
* 'Bad value ("x" is type y) for key "z"' - When a given object doesn't
match the appropriate target datatype for a given key.
- The 'HINT' value will clarify the requested type.
Examples
--------
### Valid object:
SELECT VALIDATE_SPECIFICATION(
'{"example": "correct"}',
'[
{"key": "example", "type": "string", "required": true}
{"key": "optional", "type": "number", "required": false}
]'
);
=> 't'
### Invalid object, missing required key "oops":
SELECT VALIDATE_SPECIFICATION(
'{"example": "incorrect"}',
'[
{"key": "example", "type": "string", "required": true},
{"key": "oops", "type": "string", "required": true}
]'
);
WARNING: Missing required key "oops"
=> 'f'
### Invalid object, extra keys "oops" and "bad":
SELECT VALIDATE_SPECIFICATION(
'{"example": "incorrect", "oops": 1, "bad": true}',
'[{"key": "example", "type": "string", "required": true}]'
);
WARNING: Extra key(s): oops, bad
=> 'f'
### Invalid object, bad datatype on "bad" key:
SELECT VALIDATE_SPECIFICATION(
'{"example": "incorrect", "bad": "fifty"}',
'[
{"key": "example", "type": "string", "required": true},
{"key": "bad", "type": "number", "required": true},
]'
);
WARNING: Bad value ("fifty" is type `string`) for key "bad"
HINT: The proper datatype is `number`
=> 'f'
-----
docs: http://www.postgresql.org/docs/9.4/static/functions-json.html#FUNCTIONS-JSON-PROCESSING-TABLE
$MARKDOWN$;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment