[Bizgres-general] [ENG] Re: Statement Queuing take II - Resource Scheduling

[Alon Goldshuv]

Mark,

> Alon Goldshuv wrote:
>> 
>> Keep in mind that:
>> 
>> - in between FETCHes other non related cursor commands may be executed.
>> - there can be many cursors running, just waiting to be retrieved from
>> shared memory when it's their turn to FETCH, and then wait for the next one.
>> I guess this will mean 1 lock for every live cursor.
>> - cursor doesn't always get closed by a CLOSE command. If the transaction is
>> finished the cursor is closed automatically. So maybe PortalDrop is a
>> strategic place to look at (?)
>> 
>>   
> I found 2 bugs connected with cursors and prepared statements last week.
> 
> The first was that I had not handled unnamed prepared statements in
> exec_parse_message, so was trivial to fix.

In the last week 2 cursor bugs were found relating to cursors that are used
via exec_parse_message/prepared statements, another bug fix is pending. I
hope that didn't throw you off, so I am just letting you know. If you want
information about those let me know.
 
> The second relates to having more than one cursor open at the same time
> - the code is garbage collecting the PROCLOCK entry when the first
> cursor closes, and so I get a missing PROCLOCK when the second one tries
> to close (don't know how I missed this till now... but anyway).

I assume you are talking about bizgres, no bizgres mpp. If in bizgres mpp
there is an option to open 2 cursors at the same time it's a bug and needs
to be fixed immediately. However I assume you may want to develop keeping in
mind that this restriction will be removed hopefully sometime next month
(and therefore mpp will be allowed to open as many cursors at the same time
as desired, like in bizgres or postgresql). It seems like you're doing that
so it's good.


> What I need to do (I think), is to alter the lock granularity from queue
> level to queue and statement level. This makes code still work pretty
> much like standard locks and turns out to have some nice benefits for
> the more sophisticated limit types (COST etc) e.g:
> 
> We need to remember the increment that we are requesting (e.g. a
> statement's cost) - for lock acquisition *and* release. This is most
> sensibly done by amending the PROCLOCK structure to accommodate an array
> of increments. Now if we have 1 lock per queue, this array needs to be
> variable sized (1 set of increments for each statement that 1 process
> has running or waiting). However if I have 1 lock per queue and
> statement, then we only need 1 set of increments  (i.e. fixed size) for
> each process and statement. This seems much better!
> 
> Clearly to implement this I need a statement or portal id I can use - It
> seems that portals are referenced by name , which I can't use in a
> LOCKTAG, so before I go and add some simple integer counter similar to
> the unnamed_portal_count in CreateNewPortal, does anyone know of an
> existing one I can use?

There isn't one specific to named portals as far as I know of -
unfortunately portals are referenced by name only. The unnamed_portal_count
is pretty useless too since it's meant only for creating a unique string and
doesn't get reset in the lifetime of the process.

My suggestion will be to not add a counter to the named portal *name* (like
unnamed_portal_count) at creation time - that will lead to a chain of other
problems during the following cursor operations, and extra code to get
around it. One idea would be to add a new field to struct PortalData in
portal.h that can be your counter/id field. Unnamed portals could be always
assigned some default number such as 0 or anything that works for you
keeping in mind that there can only be one unnamed portal at a time. Named
portals can each have a unique id. It's always possible to iterate through
all the currently existing portals by poking at the portal hash table. This
is done for almost each function in portalmem.c, see for example
AtCommit_Portals().

If I misunderstood you and you just need a portal *count* you don't even
need that extra field in portal.h, you can just loop through the hashtable
at any given time and count how many portals there are at that time...

HTH

Alon.