PostgreSQL Discussion - "Upcalls" (sort of) from the database

Author

"Upcalls" (sort of) from the database

Don Y

2006-04-06, 11:29 am

Hi,

I wasn't prepared to ask this question, yet :< but
all the talk of stored procedures, etc. suggests
this might be a good time to venture forth...

Humor me: assume I have done the analysis and *know*
this to be correct for my situation :>

I want to embed a good deal of the invariant aspects
of my application *domain* in the databases that
service it -- instead of in the applications riding
*above* them. So, I let the database, itself, do
sanity checking of data on input -- if the database
rejects the INSERT, the application *knows* there is
something wrong with the data (instead of building
libraries to check each datum in each application
and *hoping* that the checks are implemented
consistently from one application to the next, etc.)

Anyway, the problem I have is how to handle cases
where the "database" needs user confirmation of an
action (!). I.e. it has verified that the data
coming in is legitimate (adheres to all of the rules)
and *can* be stored in the appropriate tables -- BUT,
notices "something" that causes it to wonder if the
user REALLY wants to INSERT this data. The database
would like to alert the user to what it has noticed
and get confirmation from the user (of course, I
mean my *application* wants to do this -- but, based
on observations made *by* the database, itself).

By way of example, the *toy* application I am playing with
to explore my implementation options is a "book" database;
it tracks titles, books, authors, publishers, etc.
The sort of thing a library could use to manage its
collection.

Assume the user tries to INSERT an entry for a "book".
Legitimately, this can be:
- a new title that the database has never seen before
- a new title by an author with other titles in the database
- an existing title thus another copy of that title

However, it can also just *appear* to be a legitimate new
title!

For example, the title may match an existing entry -- but
the author may be different (e.g., misspelled, or some
"other" author listed on a book having multiple authors, etc.).
Ideally, I would like the database to suspend the INSERT,
ask for confirmation (and "why") and then, either commit
the INSERT or abort it (based on the user's response).

Nearest I can imagine, there's only one ways I can do this:
issue a query that looks for these types of problems and
based on the result, let the *application* prompt the
user for confirmation. Then, *if* confirmed, do the real
INSERT.

Is there a more elegant way?

Thanks,
--don

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Bernhard Weisshuhn

2006-04-06, 1:29 pm

Don Y wrote:
> Hi,
>
> I wasn't prepared to ask this question, yet :< but
> all the talk of stored procedures, etc. suggests
> this might be a good time to venture forth...
>
> Humor me: assume I have done the analysis and *know*
> this to be correct for my situation :>
>
> I want to embed a good deal of the invariant aspects
> of my application *domain* in the databases that
> service it -- instead of in the applications riding
> *above* them. So, I let the database, itself, do
> sanity checking of data on input -- if the database
> rejects the INSERT, the application *knows* there is
> something wrong with the data (instead of building
> libraries to check each datum in each application
> and *hoping* that the checks are implemented
> consistently from one application to the next, etc.)
>
> Anyway, the problem I have is how to handle cases
> where the "database" needs user confirmation of an
> action (!). I.e. it has verified that the data
> coming in is legitimate (adheres to all of the rules)
> and *can* be stored in the appropriate tables -- BUT,
> notices "something" that causes it to wonder if the
> user REALLY wants to INSERT this data. The database
> would like to alert the user to what it has noticed
> and get confirmation from the user (of course, I
> mean my *application* wants to do this -- but, based
> on observations made *by* the database, itself).
>
> By way of example, the *toy* application I am playing with
> to explore my implementation options is a "book" database;
> it tracks titles, books, authors, publishers, etc.
> The sort of thing a library could use to manage its
> collection.
>
> Assume the user tries to INSERT an entry for a "book".
> Legitimately, this can be:
> - a new title that the database has never seen before
> - a new title by an author with other titles in the database
> - an existing title thus another copy of that title
>
> However, it can also just *appear* to be a legitimate new
> title!
>
> For example, the title may match an existing entry -- but
> the author may be different (e.g., misspelled, or some
> "other" author listed on a book having multiple authors, etc.).
> Ideally, I would like the database to suspend the INSERT,
> ask for confirmation (and "why") and then, either commit
> the INSERT or abort it (based on the user's response).
>
> Nearest I can imagine, there's only one ways I can do this:
> issue a query that looks for these types of problems and
> based on the result, let the *application* prompt the
> user for confirmation. Then, *if* confirmed, do the real
> INSERT.

You could *insert* the data and then *rollback* the transaction. Then
you would *know* the data is *valid*.
Only if the user *confirms* the action, then you do it *again* and
actually *commit* the transaction.

P.S. these* *stars* are *unnerving* ;-)

bkw

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Don Y

2006-04-06, 1:29 pm

Bernhard Weisshuhn wrote:
> Don Y wrote:

[snip]

>
> You could *insert* the data and then *rollback* the transaction. Then
> you would *know* the data is *valid*.
> Only if the user *confirms* the action, then you do it *again* and
> actually *commit* the transaction.

Ah, OK. More elegant. But, it still moves responsibility for this
to the application layer, not the database, itself. I can't see
any way of avoiding this :-(

OTOH, an API with like insert_data(...., bool confirm) would
remind the application developers that the intended interface
is:

switch (insert_data(..., FALSE)) {
case INVALID:
/* something wonky in the data, abort */
break;
case QUESTIONABLE:
/* possible typographical error, require confirmation */
if (confirmed)
insert_data(..,TRUE);
break;
case LOOKS_GOOD:
insert_data(..., TRUE);
}

> P.S. these* *stars* are *unnerving* ;-)

<frown> Sorry, i've been writing specifications for the past
few days and use the "emphasis" SGML tag quite a bit :-/
(the idea of posting in HTML is just anathema...)

--don

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Dawid Kuroczko

2006-04-06, 1:29 pm

On 4/6/06, Don Y <pgsql@dakotacom.net> wrote:
>
> For example, the title may match an existing entry -- but
> the author may be different (e.g., misspelled, or some
> "other" author listed on a book having multiple authors, etc.).
> Ideally, I would like the database to suspend the INSERT,
> ask for confirmation (and "why") and then, either commit
> the INSERT or abort it (based on the user's response).
>
> Nearest I can imagine, there's only one ways I can do this:
> issue a query that looks for these types of problems and
> based on the result, let the *application* prompt the
> user for confirmation. Then, *if* confirmed, do the real
> INSERT.
>
> Is there a more elegant way?

Well, your application could:
BEGIN TRANSACTION;
Then it would
INSERT INTO... or call a stored procedure. The triggers/stored procedure
would
do all what's needed to perform such action, but when it notices something
suspicious it would RAISE (see PLpgSQL) a notice describing the problem(s).

If your user application notices such messages, it issues a message
"WARNING:
the message, do you want to continue?" and if user presses yes, you
do COMMIT. Otherwise you do ROLLBACK.

The thing is to use
http://www.postgresql.org/docs/8.1/...d-messages.html

Regards,
Dawid

Eric E

2006-04-06, 1:29 pm

Don Y wrote:
> Hi,
>
> I wasn't prepared to ask this question, yet :< but
> all the talk of stored procedures, etc. suggests
> this might be a good time to venture forth...
>
> Humor me: assume I have done the analysis and *know*
> this to be correct for my situation :>
>
> I want to embed a good deal of the invariant aspects
> of my application *domain* in the databases that
> service it -- instead of in the applications riding
> *above* them.
Keeping in mind that you've done plenty of analysis, I'd raise the
standard cautionary note that you have to be pretty certain that you're
right about what's invariant if you're going to couple your application
logic very tightly to your data model.

> Anyway, the problem I have is how to handle cases
> where the "database" needs user confirmation of an
> action (!). I.e. it has verified that the data
> coming in is legitimate (adheres to all of the rules)
> and *can* be stored in the appropriate tables -- BUT,
> notices "something" that causes it to wonder if the
> user REALLY wants to INSERT this data. The database
> would like to alert the user to what it has noticed
> and get confirmation from the user (of course, I
> mean my *application* wants to do this -- but, based
> on observations made *by* the database, itself).
I've been dealing this myself, as it happens, in the context of
importing and validating data (an ETL kind of thing). My answer is to
use temporary tables as a way of staging the data. One of the
advantages of that is that you can wrap up the DDL for the temporary
table inside your code (whether in or out of the database) and thus
insulate that part of the process from other changes in the destination
tables. Then you can fire off whatever rules you like about whether to
alert the user of certain conditions with the data without worrying
about commits. Of course the transaction approach can probably handle
that, too.

Cheers,

Eric

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Scott Ribe

2006-04-06, 8:25 pm

> Ah, OK. More elegant. But, it still moves responsibility for this
> to the application layer, not the database, itself. I can't see
> any way of avoiding this :-(

Sure, I had a similar problem where newer data is required to specify
certain fields, but a legacy application can't provide that data. So there's
a flag, that defaults to false, that when set to true means "I know, I know,
just shut up and insert the data anyway."

The "flag" could be an argument to a stored procedure. In my case it's an
actual column in the table, because I wanted to use check constraints and I
wanted a record of the fact that the entry is "incomplete" because of its
source.

--
Scott Ribe
scott_ribe@killerbyt
es.com
http://www.killerbytes.com/
(303) 722-0567 voice

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links