Thread: SPI error with non-volatile functions

Dear Hackers

I've been using SPI to execute some queries and this time I've tried to 
issue CREATE TABLE commands through SPI. I've been getting the message 
"ERROR: CREATE TABLE AS is not allowed in a non-volatile function".

I'm a bit confused because my functions are set as volatile when I got 
that result. I was sure I'd be able to issue CREATE TABLE through SPI 
because a possible return value of SPI_execute is SPI_OK_UTILITY if a 
utility command such as CREATE TABLE was executed.

Maybe the caveat is the following. I am actually invoking my function 
through CREATE LANGUAGE's inline handler using an anonymous do block. So 
maybe I need to take some additional considerations for this reason?

The following is what my functions look like:

CREATE FUNCTION myLanguage.myLanguage_function_call_handler()
RETURNS language_handler
AS 'MODULE_PATHNAME','myLanguage_function_call_handler'
LANGUAGE C VOLATILE;

CREATE FUNCTION myLanguage.myLanguage_inline_function_handler(internal)
RETURNS void
AS 'MODULE_PATHNAME','myLanguage_inline_function_handler'
LANGUAGE C VOLATILE;

CREATE LANGUAGE myLanguage
HANDLER myLanguage.myLanguage_function_call_handler
INLINE myLanguage.myLanguage_inline_function_handler;
COMMENT ON LANGUAGE myLanguage IS 'My Language';


Have I correctly approached the issue? Maybe there is a workaround?

Best regards
Tom

Hi,

On 2019-11-13 05:09:31 +0000, Tom Mercha wrote:
> I've been using SPI to execute some queries and this time I've tried to 
> issue CREATE TABLE commands through SPI. I've been getting the message 
> "ERROR: CREATE TABLE AS is not allowed in a non-volatile function".

Any chance you're specifying read_only = true to
SPI_execute()/execute_plan()/...?

Greetings,

Andres Freund

On 13/11/2019 06:13, Andres Freund wrote:
> Hi,
> 
> On 2019-11-13 05:09:31 +0000, Tom Mercha wrote:
>> I've been using SPI to execute some queries and this time I've tried to
>> issue CREATE TABLE commands through SPI. I've been getting the message
>> "ERROR: CREATE TABLE AS is not allowed in a non-volatile function".
> 
> Any chance you're specifying read_only = true to
> SPI_execute()/execute_plan()/...?
> 
> Greetings,
> 
> Andres Freund
> 

Dear Andres

That's exactly what's up! Everything is working as intended now. So 
sorry this was a bit silly of me, I didn't understand the message as a 
reference to that configuration.

Thanks so much.

Best regards
Tom