Re: Assertions in PL/PgSQL

On 9/14/13 11:55 PM, Pavel Stehule wrote:

2013/9/15 Marko Tiikkaja <marko@joh.to <mailto:marko@joh.to>>

    On 2013-09-15 00:09, Pavel Stehule wrote:

        this is a possibility for introduction a new hook and possibility implement
        asserions and similar task in generic form (as extension). it can be
        assertions, tracing, profiling.


    You can already do tracing and profiling in an extension.  I don't see what you would put inside the function body for these two, either.


you cannot mark a tracing points explicitly in current (unsupported now) extensions.

These functions share  same pattern:

CREATE OR REPLACE FUNCTION assert(boolean)
RETURNS void AS $$
IF current_setting('plpgsq.assertions') = 'on' THEN
   IF $1 THEN
     RAISE EXCEPTION 'Assert fails';
   END IF;
END IF;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE FUNCTION trace(text)
RETURNS void AS $$
IF current_setting('plpgsq.trace') = 'on' THEN
     RAISE WARNING 'trace: %', $1; END IF;
END;
$$ LANGUAGE plpgsql;

Depends on usage, these functions will not be extremely slow against to builtin solution - can be faster, if we implement it in C, and little bit faster if we implement it as internal PLpgSQL statement. But if you use a one not simple queries, then overhead is not significant (probably).

You have to watch some global state variable and then execute (or not) some functionality.

FWIW, we've written a framework (currently available in the EnovaTools project on pgFoundry) that allows for very, very fine-grain control over asserts.

- Every assert has a name (and an optional sub-name) as well as a level
- You can globally set the minimum level that will trigger an assert. This is useful for some debugging stuff; have an assert with a negative level and normally it won't fire unless you set the minimum level to be less than zero.
- You can disable an assert globally (across all backends)
- You can disable an assert only within your session

We should eventually allow for disabling an assert only for your transaction; we just haven't gotten around to it yet.

The reason for all this flexibility is the concept of "it should be very difficult but not impossible for the code to do X". We use it for sanity-checking things.

--
Jim C. Nasby, Data Architect                       jim@nasby.net
512.569.9461 (cell)                         http://jim.nasby.net