NetNews Usenet Archive 1993 #1/NN_1993_1.iso/spool/comp/database/ingres/2158

Text File | 1993-01-05 | 5.5 KB | 125 lines

Newsgroups: comp.databases.ingres
Path: sparky!uunet!gatech!asuvax!ncar!uchinews!gsbacd.uchicago.edu!cs_mj
From: [e-mail] (Mark Jaeger)
Subject: Re: Help - ABF/4GL questions.
Message-ID: <[e-mail]>
Lines: 113
Sender: [e-mail] (News System)
Organization:
References: <[e-mail]>
Date: Tue, 5 Jan 1993 07:22:34 GMT

In article <[e-mail]>, [e-mail] (Mark D. Gold)
writes:

> I'm having several problems writing 4GL procedures.  I was hoping
> that an INGRES expert could point me in the right direction.
>
> 1.  One 4GL procedure does the following work:
>
>     a.  create temporary table x
>
>     b.  3 large, complex inserts (with selects) into table x
>
>     c.  3 large, complex select loops which each have
>             REPEATED inserts into table x within the loops
>             (the select loops also call C procedures)
>
>    [more repeated inserts...]
>
>     g.  call report passing temporary table.
>
> [Question: how do I use repeated inserts (for performance)
> with a temporary table whose name is generated at run time
> so that one user can have multiple sessions running at the same time?]

You're right that repeated queries can run substantially faster than
non-repeated ones, and so it may be worthwhile to expend extra energy
figuring out how to use them in a situation that is not natural for
them.  This points out just one of the many problems associated with
temporary tables that are generated on the fly at run time.  This leads
to the solution you came to, namely, that you hard code the name of the
temporary table, and each user creates a private version of the temp
table while the report is running.  This leads to the problem of
multiple sessions by the same user trying to access the same temp table.

> Does this mean that I have to take out an exclusive table lock on the
> temporary table (set lockmode on tablename where readlock = exclusive)
> to keep another user on the same account from running the 4GL
> procedure?  How can I see if the temporary table is locked
> by another user at the start of the 4GL procedure so that I can tell
> the second user to try again later?

That's one approach.  It has the advantage that it uses INGRES locking,
which is automatically cleaned up after unnatural interruptions (machine
crash, user hits ^C or ^Y, etc.).  However, it has the disadvantage that
you have to hang on to the lock through the whole 15 minutes it takes to
populate the temp table (and then run the report).  You would have to be
careful that you don't lock out other users who are trying to update the
tables that you read from in order to populate the temp table.

It _is_ possible to find out if another user (or another session by the
same user) has an exclusive lock on the table.  Just use lock timeouts,
and try to access the table:

        commit ;
        set lockmode session where timeout = 10 ;
        my_form := select rct = count(*) from temp_table ;
        inquire_ingres ( eno = errornumber ) ;
        commit ;
        set lockmode session where timeout = 0 ;
        if eno = ??? then
            message 'Temp table is in use.' with style = popup ;
            return ;
        endif ;

> One developer suggested that I DON'T use INGRES locking to prevent
> two users in the same account from using the 4GL procedure, but
> that I create my own locking table for the database that inserts a
> row for a username when the 4GL procedure is being used and deletes
> the row when the procedure finishes.  She didn't think that relying
> on INGRES locking for what I wanted to do was a good idea.  Any
> thoughts?  However, how would I delete a row in this lock table if
> the user CTRL-Y's or CTRL-C's out of the application?

ABF uses a method like this to lock ABF applications and individual
frames/procedures so that multiple developers won't accidently edit the
same part of an application simultaneously.  ABF offers options to
override an existing lock, or to delete an existing lock.  Take a look,
it's pretty slick.

> 2.  I need to be able to run a clean_up procedure specific to each
> 4GL procedure if a user CTRL-Y's, CTRL-C's, or F6's (VMS) interrupts
> out of my application.  This local clean_up procedure would include
> deleting rows from various temporary tables and setting flags on
> other tables.
>
> ...I don't know how to capture any of the possible interrupts
> (CTRL-Y, CTRL-C, F6) and the rollback and then run my clean_up
> procedure.  Any ideas would be greatly appreciated.

That's a tough one.  As I understand it, an ABF application under VMS
installs some executive-mode exit handlers that fire upon ^C interrupts.
Other exit handlers that you install (e.g., via the C "atexit" function)
are installed in user or supervisor mode, and they get ignored when
there is _any_ executive-mode exit handler (I'm a little fuzzy on these
details).  I found that it _was_ possible to capture a ^C interrupt from
a C procedure that was called from an ABF application, but the interrupt
signal handler was ignored once my C function returned to the 4GL
caller.

The only suggestions I have (they aren't very helpful):

1. Don't_ let your users hit ^C or ^Y (how am I supposed to enforce that
one?).

2. Don't use 4GL and write your application all in C (or some other
language that has interrupt handlers--Ada?).

--Mark Jaeger                internet: [e-mail]
Graduate School of Business        yellnet:  [phone]
University of Chicago            faxnet:   [phone]
Disclaimer: My opinions are my own and not those of my employer.
Je suis un virus.  Prends ton pied et copie moi a ta .signature.