Thread: scrollable cursor sup. for SPI

scrollable cursor sup. for SPI

From
"Pavel Stehule"
Date:
Hello,

this patch adds scrollable support functions to SPI. It's necessary for
scrollable cursors in plpgsql. Original API is without changes.

Regards
Pavel Stehule

_________________________________________________________________
Chcete sdilet sve obrazky a hudbu s prateli? http://messenger.msn.cz/

Attachment

Re: scrollable cursor sup. for SPI

From
Neil Conway
Date:
On Sun, 2007-01-14 at 13:57 +0100, Pavel Stehule wrote:
> this patch adds scrollable support functions to SPI. It's necessary for
> scrollable cursors in plpgsql. Original API is without changes.

One trivial point is that if you're going to use an int * to represent
an "optional int" argument, it would be better to at least use a "const"
qualifier. However, in this case, it might be simpler to just represent
"no options passed" as 0.

Barring any objections, I'll apply a slightly improved version of this
patch tomorrow.

-Neil



Re: scrollable cursor sup. for SPI

From
Neil Conway
Date:
On Mon, 2007-01-15 at 12:26 -0500, Neil Conway wrote:
> Barring any objections, I'll apply a slightly improved version of this
> patch tomorrow.

BTW, how do people feel about the function names:

    SPI_cursor_open_with_options
    SPI_scroll_cursor_fetch
    SPI_scroll_cursor_move

The first seems fairly verbose to me, although I'm not sure I have a
better suggestion. Also, it seems unfortunate to clutter the API with
redundant versions of three functions that already exist, although I
suppose we can't afford to break backward compatibility.

Suggestions welcome.

-Neil



Re: scrollable cursor sup. for SPI

From
Tom Lane
Date:
Neil Conway <neilc@samurai.com> writes:
> BTW, how do people feel about the function names:

>     SPI_cursor_open_with_options
>     SPI_scroll_cursor_fetch
>     SPI_scroll_cursor_move

I dislike the SPI_cursor_open_with_options API on the grounds that it
lets people break things (CURSOR_OPT_HOLD for instance isn't likely
to do anything good) and it doesn't actually provide any functionality
that wasn't there before (the existing code already sets OPT_SCROLL
if possible).  I'd suggest losing that one entirely and just adding
the FetchDirection-as-substitute-for-"forward" entry points.

As for names, maybe
    SPI_cursor_fetch_direction
    SPI_cursor_move_direction
?

            regards, tom lane

Re: scrollable cursor sup. for SPI

From
"Pavel Stehule"
Date:
>Neil Conway <neilc@samurai.com> writes:
> > BTW, how do people feel about the function names:
>
> >     SPI_cursor_open_with_options
> >     SPI_scroll_cursor_fetch
> >     SPI_scroll_cursor_move
>
>I dislike the SPI_cursor_open_with_options API on the grounds that it
>lets people break things (CURSOR_OPT_HOLD for instance isn't likely
>to do anything good) and it doesn't actually provide any functionality
>that wasn't there before (the existing code already sets OPT_SCROLL
>if possible).  I'd suggest losing that one entirely and just adding
>the FetchDirection-as-substitute-for-"forward" entry points.
>

First variant of this "open" function controlls only sroll option. But there
are other option which we have to controll in futere like INSENSITIVE,
SENSITIVE, FOR UPDATE and it's reason for _with_options.

if I comprehended it well CURSOR_OPT_SCROLL is set only when SCROLL is cheap
(not when is possible). It's true?

      /*
         * Set up options for portal.
         *
         * If the user didn't specify a SCROLL type, allow or disallow
scrolling
         * based on whether it would require any additional runtime overhead
to do
         * so.
         */
        portal->cursorOptions = stmt->options;
        if (!(portal->cursorOptions & (CURSOR_OPT_SCROLL |
CURSOR_OPT_NO_SCROLL)))
        {
                if (ExecSupportsBackwardScan(plan))
                        portal->cursorOptions |= CURSOR_OPT_SCROLL;
                else
                        portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;

_________________________________________________________________
Najdete si svou lasku a nove pratele na Match.com. http://www.msn.cz/


Re: scrollable cursor sup. for SPI

From
Tom Lane
Date:
"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> if I comprehended it well CURSOR_OPT_SCROLL is set only when SCROLL is cheap
> (not when is possible). It's true?

Nope.  If you want a scrollable plan you need to make sure you tell the
planner about it.  SPI_cursor_open is not in charge, it's merely looking
at what the planner did.

As for that other stuff, when and if we support it, it would be time to
add a SPI entry point that supports it.  I'm disinclined to add an API
on speculation though --- by the time the feature actually exists, we
might not like the API anymore anyway.

            regards, tom lane

Re: scrollable cursor sup. for SPI

From
"Pavel Stehule"
Date:
>
>"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> > if I comprehended it well CURSOR_OPT_SCROLL is set only when SCROLL is
>cheap
> > (not when is possible). It's true?
>
>Nope.  If you want a scrollable plan you need to make sure you tell the
>planner about it.  SPI_cursor_open is not in charge, it's merely looking
>at what the planner did.

aha. Then modification of open function hasn't sence. But it needs enhance
SPI_prepare. I don't know  when cursors are not scrollable implicitly. it
it's important. If not, then I need for scrollable cursors in plpgsql only
fetch and move scroll funcs.

Pavel Stehule

_________________________________________________________________
Citite se osamele? Poznejte nekoho vyjmecneho diky Match.com.
http://www.msn.cz/


Re: scrollable cursor sup. for SPI

From
"Pavel Stehule"
Date:
>"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> > if I comprehended it well CURSOR_OPT_SCROLL is set only when SCROLL is
>cheap
> > (not when is possible). It's true?
>
>Nope.  If you want a scrollable plan you need to make sure you tell the
>planner about it.  SPI_cursor_open is not in charge, it's merely looking
>at what the planner did.
>
>As for that other stuff, when and if we support it, it would be time to
>add a SPI entry point that supports it.  I'm disinclined to add an API
>on speculation though --- by the time the feature actually exists, we
>might not like the API anymore anyway.
>

I did step back and did some test. game with CURSOR_OPT_SCROLL has not sense
in SPI_cursor_open. I didn't tests with table's joins where this problem is
visible before.

I propose add new function SPI_prepare_with_option(..) which allow set
option for planner. Isn't problem add to SPI_open_cursor assert for holdable
cursors. This needs propably rewriting functions pg_plan_query and
pg_plan_queries.

Or new function SPI_prepare_cursor(...) which can be similar
PerformCursorOpen. This doesn't change others functions.

All comments are welcome
Pavel Stehule

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/


Re: scrollable cursor sup. for SPI

From
Bruce Momjian
Date:
Where are we on this patch?

---------------------------------------------------------------------------

Pavel Stehule wrote:
>
> >"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> > > if I comprehended it well CURSOR_OPT_SCROLL is set only when SCROLL is
> >cheap
> > > (not when is possible). It's true?
> >
> >Nope.  If you want a scrollable plan you need to make sure you tell the
> >planner about it.  SPI_cursor_open is not in charge, it's merely looking
> >at what the planner did.
> >
> >As for that other stuff, when and if we support it, it would be time to
> >add a SPI entry point that supports it.  I'm disinclined to add an API
> >on speculation though --- by the time the feature actually exists, we
> >might not like the API anymore anyway.
> >
>
> I did step back and did some test. game with CURSOR_OPT_SCROLL has not sense
> in SPI_cursor_open. I didn't tests with table's joins where this problem is
> visible before.
>
> I propose add new function SPI_prepare_with_option(..) which allow set
> option for planner. Isn't problem add to SPI_open_cursor assert for holdable
> cursors. This needs propably rewriting functions pg_plan_query and
> pg_plan_queries.
>
> Or new function SPI_prepare_cursor(...) which can be similar
> PerformCursorOpen. This doesn't change others functions.
>
> All comments are welcome
> Pavel Stehule
>
> _________________________________________________________________
> Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
> http://messenger.msn.cz/
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--
  Bruce Momjian   bruce@momjian.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

Re: scrollable cursor sup. for SPI

From
"Pavel Stehule"
Date:
Hello,

I am working on new version. Old patch doesn't solve scrollability well.

Regards
Pavel Stehule
>
>
>Where are we on this patch?
>
>---------------------------------------------------------------------------
>
>Pavel Stehule wrote:
> >
> > >"Pavel Stehule" <pavel.stehule@hotmail.com> writes:
> > > > if I comprehended it well CURSOR_OPT_SCROLL is set only when SCROLL
>is
> > >cheap
> > > > (not when is possible). It's true?
> > >
> > >Nope.  If you want a scrollable plan you need to make sure you tell the
> > >planner about it.  SPI_cursor_open is not in charge, it's merely
>looking
> > >at what the planner did.
> > >
> > >As for that other stuff, when and if we support it, it would be time to
> > >add a SPI entry point that supports it.  I'm disinclined to add an API
> > >on speculation though --- by the time the feature actually exists, we
> > >might not like the API anymore anyway.
> > >
> >
> > I did step back and did some test. game with CURSOR_OPT_SCROLL has not
>sense
> > in SPI_cursor_open. I didn't tests with table's joins where this problem
>is
> > visible before.
> >
> > I propose add new function SPI_prepare_with_option(..) which allow set
> > option for planner. Isn't problem add to SPI_open_cursor assert for
>holdable
> > cursors. This needs propably rewriting functions pg_plan_query and
> > pg_plan_queries.
> >
> > Or new function SPI_prepare_cursor(...) which can be similar
> > PerformCursorOpen. This doesn't change others functions.
> >
> > All comments are welcome
> > Pavel Stehule
> >
> > _________________________________________________________________
> > Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
> > http://messenger.msn.cz/
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: don't forget to increase your free space map settings
>
>--
>   Bruce Momjian   bruce@momjian.us
>   EnterpriseDB    http://www.enterprisedb.com
>
>   + If your life is a hard drive, Christ can be your backup. +
>
>---------------------------(end of broadcast)---------------------------
>TIP 3: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq

_________________________________________________________________
Emotikony a pozadi programu MSN Messenger ozivi vasi konverzaci.
http://messenger.msn.cz/