Assertions in PL/PgSQL

On 14/09/2013 20:47, I wrote:

These are
similar to the Assert() backend macro: they can be disabled during
compile time, but when enabled, abort execution if the passed expression
is not true.

And by "compile time" here, I mean at the time when the PL/PgSQL
function is compiled, not the postgres server binary.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Sat, Sep 14, 2013 at 1:52 PM, Marko Tiikkaja <marko@joh.to> wrote:

On 14/09/2013 20:47, I wrote:

These are
similar to the Assert() backend macro: they can be disabled during
compile time, but when enabled, abort execution if the passed expression
is not true.

Hi,

That's very good, thanks.

And by "compile time" here, I mean at the time when the PL/PgSQL function

is

compiled, not the postgres server binary.

and "compile time" means when the function is created or replaced? or the
first time is used?

if the second. Why not have a plpgsql.enable_assert variable?
A directive you can use per function

then i can keep the ASSERT and activate them by replacing the function

--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
Phone: +593 4 5107566 Cell: +593 987171157

On 2013-09-14 21:55, Jaime Casanova wrote:

On Sat, Sep 14, 2013 at 1:52 PM, Marko Tiikkaja <marko@joh.to> wrote:

And by "compile time" here, I mean at the time when the PL/PgSQL function

is

compiled, not the postgres server binary.

and "compile time" means when the function is created or replaced? or the
first time is used?

Uhh.. I have to admit, I'm not sure. I think this would be when you
CREATE the function for the backend that created the function, and on
the first call in other backends.

if the second. Why not have a plpgsql.enable_assert variable?
A directive you can use per function

then i can keep the ASSERT and activate them by replacing the function

The patch supports a "compiler option" to override the global option and
enable it per function:

create function foof()
returns void
as $$
#enable_assertions
begin
-- failure
assert 1 > 2;
end
$$ language plpgsql;

Does this address your wishes?

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Hello

There is a significant issue - new reserved keyword. There is high
probability so lot of users has a functions named "assert".

I like this functionality, but I dislike any compatibility break for
feature, that can be implemented as extension without any lost of
compatibility or lost of functionality.

So can you redesign this without new keyword?

Regards

Pavel

2013/9/14 Marko Tiikkaja <marko@joh.to>

El 14/09/2013 15:18, "Marko Tiikkaja" <marko@joh.to> escribió:

On 2013-09-14 21:55, Jaime Casanova wrote:

On Sat, Sep 14, 2013 at 1:52 PM, Marko Tiikkaja <marko@joh.to> wrote:

And by "compile time" here, I mean at the time when the PL/PgSQL

function

is

compiled, not the postgres server binary.

and "compile time" means when the function is created or replaced? or the
first time is used?

Uhh.. I have to admit, I'm not sure. I think this would be when you

CREATE the function for the backend that created the function, and on the
first call in other backends.

if the second. Why not have a plpgsql.enable_assert variable?
A directive you can use per function

then i can keep the ASSERT and activate them by replacing the function

The patch supports a "compiler option" to override the global option and

enable it per function:

create function foof()
returns void
as $$
#enable_assertions
begin
-- failure
assert 1 > 2;
end
$$ language plpgsql;

Does this address your wishes?

That's exactly what i wanted. thanks

--
Jaime Casanova
2ndQuadrant: Your PostgreSQL partner

On 2013-09-14 22:24, Pavel Stehule wrote:

There is a significant issue - new reserved keyword. There is high
probability so lot of users has a functions named "assert".

Someone may prove me wrong here, but to me it looks like this would only
prevent ASSERT from being used as the name of a PL/PgSQL variable.
That's still a backwards compatibility break, but the case you were
worried about should still work:

=# create function assert(boolean) returns boolean as $$ begin if $1 is
not true then raise exception 'custom assert()'; end if; return true;
end $$ language plpgsql;
CREATE FUNCTION
=# create function f() returns int as $$
$# begin
$# assert false;
$# perform assert(true);
$# if assert(true) then
$# perform assert(false);
$# end if;
$# end
$# $$ language plpgsql;
CREATE FUNCTION
=# select f();
ERROR: custom assert()
CONTEXT: SQL statement "SELECT assert(false)"
PL/pgSQL function f() line 6 at PERFORM

I like this functionality, but I dislike any compatibility break for
feature, that can be implemented as extension without any lost of
compatibility or lost of functionality.

I don't see how this could be implemented as an extension, even if you
write it in C. I think being able to turn assertions off in production
with no execution time overhead is what justifies having this in-core.
The nicer syntax isn't enough (compared to, say, PERFORM assert(..)).
And if we're only breaking code for people who use "assert" as the
variable name, I'd say we go for it.

So can you redesign this without new keyword?

I don't see an easy way to do that, but I'm not too familiar with
PL/PgSQL's parser so maybe I'm just missing something.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-09-14 22:40, I wrote:

Someone may prove me wrong here, but to me it looks like this would only
prevent ASSERT from being used as the name of a PL/PgSQL variable.

The comment at the beginning of pl_scanner.c seems to suggest same.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/9/14 Marko Tiikkaja <marko@joh.to>

On 2013-09-14 22:40, I wrote:

Someone may prove me wrong here, but to me it looks like this would only
prevent ASSERT from being used as the name of a PL/PgSQL variable.

The comment at the beginning of pl_scanner.c seems to suggest same.

yes, there are no too much possibilities, how to do it.

Effective implementation needs a special PLpgSQL statement - but I am 100%
happy with proposed syntax. We introduce a new special keyword just for one
simple construct.

A some languages has a generic PRAGMA keyword. So I would be much more
happy with something like

PRAGMA Assert(found);

It is much more close to ADA, and it allows a reuse of new keyword for any
other usage in future (your proposal is too simply, without possibility
open new doors in future). And we can define a some standard predefined
asserts too - like Assert, AssertNotNull, AssertNotEmpty, ...

other issue - A asserts macros has one or two parameters usually. You
should to support two params assert (with message).

On 2013-09-14 23:05, Pavel Stehule wrote:

A some languages has a generic PRAGMA keyword. So I would be much more
happy with something like

PRAGMA Assert(found);

It is much more close to ADA, and it allows a reuse of new keyword for any
other usage in future (your proposal is too simply, without possibility
open new doors in future). And we can define a some standard predefined
asserts too - like Assert, AssertNotNull, AssertNotEmpty, ...

I don't see why e.g. PRAGMA AssertNotEmpty(foo); would be better than
ASSERT NotEmpty(foo); and the NotNull version is even sillier
considering the expression is arbitrary SQL, and we'd have to do all
kinds of different versions or people would be disappointed (AssertNull,
AssertNotNull, AssertExists, AssertNotExists, etc.).

I see what you're trying to do, but I don't think crippling new features
just because we might do something similar at some point is a good idea.
I'm guessing this is what happened with the row_count syntax, which
made the feature an absolute nightmare to use.

other issue - A asserts macros has one or two parameters usually. You
should to support two params assert (with message).

That I think is worth looking into.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Dne 14. 9. 2013 23:35 "Marko Tiikkaja" <marko@joh.to> napsal(a):

On 2013-09-14 23:05, Pavel Stehule wrote:

A some languages has a generic PRAGMA keyword. So I would be much more
happy with something like

PRAGMA Assert(found);

It is much more close to ADA, and it allows a reuse of new keyword for

any

other usage in future (your proposal is too simply, without possibility
open new doors in future). And we can define a some standard predefined
asserts too - like Assert, AssertNotNull, AssertNotEmpty, ...

I don't see why e.g. PRAGMA AssertNotEmpty(foo); would be better than

ASSERT NotEmpty(foo); and the NotNull version is even sillier considering
the expression is arbitrary SQL, and we'd have to do all kinds of different
versions or people would be disappointed (AssertNull, AssertNotNull,
AssertExists, AssertNotExists, etc.).

I see what you're trying to do, but I don't think crippling new features

just because we might do something similar at some point is a good idea.
I'm guessing this is what happened with the row_count syntax, which made
the feature an absolute nightmare to use.

a more than one asserts can be my personal preferrence (it is not
important).

but introduction a reserved keword for one very special purpose (without
extensibility) is not prudent.

plpgsql has still lot of relations to pl/sql and ada, and I don't think so
we have to introduce a new original syntax everytime.

Dne 14. 9. 2013 23:55 "Pavel Stehule" <pavel.stehule@gmail.com> napsal(a):

Dne 14. 9. 2013 23:35 "Marko Tiikkaja" <marko@joh.to> napsal(a):

On 2013-09-14 23:05, Pavel Stehule wrote:

A some languages has a generic PRAGMA keyword. So I would be much more
happy with something like

PRAGMA Assert(found);

It is much more close to ADA, and it allows a reuse of new keyword for

any

other usage in future (your proposal is too simply, without possibility
open new doors in future). And we can define a some standard predefined
asserts too - like Assert, AssertNotNull, AssertNotEmpty, ...

I don't see why e.g. PRAGMA AssertNotEmpty(foo); would be better than

ASSERT NotEmpty(foo); and the NotNull version is even sillier considering
the expression is arbitrary SQL, and we'd have to do all kinds of different
versions or people would be disappointed (AssertNull, AssertNotNull,
AssertExists, AssertNotExists, etc.).

I see what you're trying to do, but I don't think crippling new

features just because we might do something similar at some point is a good
idea. I'm guessing this is what happened with the row_count syntax, which
made the feature an absolute nightmare to use.

a more than one asserts can be my personal preferrence (it is not

important).

but introduction a reserved keword for one very special purpose (without

extensibility) is not prudent.

plpgsql has still lot of relations to pl/sql and ada, and I don't think

so we have to introduce a new original syntax everytime

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.

I like a integrated assertions, but I would not close a door to future
enhancing (probably there will not be a possibility intriduce a new keyword
for tracing - although I would to implement a difference between
development an production usage.

so I am against to your proposal - it doesn't allow any extensibility.

On 2013-09-14 23:55, Pavel Stehule wrote:

but introduction a reserved keword for one very special purpose (without
extensibility) is not prudent.

How about using an existing keyword then? ASSERTION used to be reserved
in the SQL standard but is unreserved in postgres. CHECK might work and
is fully reserved. CONSTRAINT? IS?

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

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.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

2013/9/15 Marko Tiikkaja <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.

Regards

Pavel

On Sat, 2013-09-14 at 20:47 +0200, Marko Tiikkaja wrote:

Attached is a patch for supporting assertions in PL/PgSQL. These are
similar to the Assert() backend macro: they can be disabled during
compile time, but when enabled, abort execution if the passed expression
is not true.

Doesn't build:

pl_exec.c: In function ‘exec_stmt_assert’:
pl_exec.c:3647:58: error: ‘ERRCODE_ASSERTION_FAILURE’ undeclared (first use in this function)
pl_exec.c:3647:58: note: each undeclared identifier is reported only once for each function it appears in

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2013-09-15 16:34, Peter Eisentraut wrote:

On Sat, 2013-09-14 at 20:47 +0200, Marko Tiikkaja wrote:

Attached is a patch for supporting assertions in PL/PgSQL. These are
similar to the Assert() backend macro: they can be disabled during
compile time, but when enabled, abort execution if the passed expression
is not true.

Doesn't build:

Ugh. Accidentally edited an auto-generated file. Fixed in the
attached, thanks!

Regards,
Marko Tiikkaja

El 14/09/2013 15:25, "Pavel Stehule" <pavel.stehule@gmail.com> escribió:

Hello

There is a significant issue - new reserved keyword. There is high

probability so lot of users has a functions named "assert".

I like this functionality, but I dislike any compatibility break for

feature, that can be implemented as extension without any lost of
compatibility or lost of functionality.

So can you redesign this without new keyword?

Hi,

If using ASSERT as keyword is not acceptable, not that i agree but in case.
What about using RAISE EXCEPTION WHEN (condition)

--
Jaime Casanova
2ndQuadrant: Your PostgreSQL partner

On 2013-09-15 23:23, Jaime Casanova wrote:

If using ASSERT as keyword is not acceptable, not that i agree but in case.
What about using RAISE EXCEPTION WHEN (condition)

I think it would be extremely surprising if a command like that got
optimized away based on a GUC, so I don't think that would be a good idea.

Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

El 15/09/2013 17:13, "Marko Tiikkaja" <marko@joh.to> escribió:

On 2013-09-15 23:23, Jaime Casanova wrote:

If using ASSERT as keyword is not acceptable, not that i agree but in

case.

What about using RAISE EXCEPTION WHEN (condition)

I think it would be extremely surprising if a command like that got

optimized away based on a GUC, so I don't think that would be a good idea.

Ah! good point, didn't think on that

--
Jaime Casanova
2ndQuadrant: Your PostgreSQL partner