Обсуждение: SPI: ERROR: no snapshot has been set

[Please CC any replies, thanks]

Hi,

I got this nice error, and according to Tom Lane[1]:

> Depending on what PG version you're using (which you did not say,
> naughty naughty) there are paths in interactive query entry that
> might try to execute datatype input functions before setting the
> query snapshot. But I don't believe it can happen down inside SPI.

[1] http://www.dbforums.com/archive/index.php/t-1065541.html

Good guess, I am inside a type input function trying to use SPI. My
questions are:

- I'm using 7.4, is this fixed/changed in later versions?
- I'm triggering it with: select 'hello'::mytype;
 Is there an easy way of testing my function without triggering this?

select (select 'hello'::mytype);    -- Same problem
select (select 'hello')::mytype     -- gives something about casting unknown
\copy .. from stdin                 -- Works but is inconvenient

- Is there a way to detect if a snapshot has been started and creating
one if there hasn't. I've seen the snapshot related functions but I
don't feel comfortable sticking them in my type input function just to
fix this. Is it safe?

Thanks in advance,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog@svana.org> writes:
> - Is there a way to detect if a snapshot has been started and creating
> one if there hasn't. I've seen the snapshot related functions but I
> don't feel comfortable sticking them in my type input function just to
> fix this. Is it safe?

This would be a pretty bad idea IMHO.  The setting of the first snapshot
in a transaction is a significant event, and it should not happen in
random places like type-specific input functions.

A long-term solution to this might be to do what's envisioned in this
comment in parse_coerce.c:
        * XXX if the typinput function is not immutable, we really ought to        * postpone evaluation of the
functioncall until runtime. But        * there is no way to represent a typinput function call as an        *
expressiontree, because C-string values are not Datums. (XXX        * This *is* possible as of 7.3, do we want to do
it?)

I'm a bit concerned about the possible side-effects of this on existing
behavior, though.  In particular constructs like'now'::timestamp
would change behavior subtly.
        regards, tom lane

On Sat, Aug 13, 2005 at 12:36:42PM -0400, Tom Lane wrote:
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > - Is there a way to detect if a snapshot has been started and creating
> > one if there hasn't. I've seen the snapshot related functions but I
> > don't feel comfortable sticking them in my type input function just to
> > fix this. Is it safe?
>
> This would be a pretty bad idea IMHO.  The setting of the first snapshot
> in a transaction is a significant event, and it should not happen in
> random places like type-specific input functions.

I figured as much. In that case I've been thinking something like:
if (SerializableSnapshot == NULL)    elog( ERROR, "Sorry, 'const'::mytype constructs are not supported in first "
         "transaction, please use mytype('const'::text)" ); 

I hope this works, I havn't gotten around to the casts yet, I hope
non-immutable casts are called late enough. Annoyingly, it breaks a
straight:

INSERT INTO table VALUES ('const','const','const',...)

Which is what GUI frontends are going to try to do... My alternative is
to cache results but that would make whether it works sporadic and
unpredictable.

> A long-term solution to this might be to do what's envisioned in this
> comment in parse_coerce.c:

<snip>

> I'm a bit concerned about the possible side-effects of this on existing
> behavior, though.  In particular constructs like
>     'now'::timestamp
> would change behavior subtly.

It's a good idea, eventually. All it would do is change the time it's
evaluated, and in that example it doesn't change since 'now' is the
transaction start time. However, in general it is a change of
behaviour.

Thanks in advance,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

Martijn van Oosterhout <kleptog@svana.org> writes:
>> I'm a bit concerned about the possible side-effects of this on existing
>> behavior, though.  In particular constructs like
>>    'now'::timestamp
>> would change behavior subtly.

> It's a good idea, eventually. All it would do is change the time it's
> evaluated, and in that example it doesn't change since 'now' is the
> transaction start time.

Well, it could change, eg if this appears in a PREPARE'd statement or a
column default expression.  Offhand it seems likely that the change
would be for the better, but I'm still hesitant.
        regards, tom lane

For future reference, I got around this error (no snapshot has been
set) by removing the use of SPI and just using heap_open /
heap_beginscan / heap_endscan / heap_close. It's only slightly more
code but it works irrespective of the state of the backend.

Have a nice day,

On Sat, Aug 13, 2005 at 03:59:56PM +0200, Martijn van Oosterhout wrote:
> Good guess, I am inside a type input function trying to use SPI. My
> questions are:
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.