MIME-Version: 1.0
From: Flaris Feller <flaris.feller@gmail.com>
Date: Mon, 6 Mar 2023 07:36:32 -0300
Message-ID: 
 <CANNsrHt-3wC-nGG_owzfKfx+N-sHWFuJQ=QEivGrXGK2_ne0xA@mail.gmail.com>
Subject: Transaction control in a C language function
To: pgsql-interfaces@lists.postgresql.org
Content-Type: multipart/alternative; boundary="00000000000077d42305f638e0da"
Archived-At: 
 <https://www.postgresql.org/message-id/CANNsrHt-3wC-nGG_owzfKfx%2BN-sHWFuJQ%3DQEivGrXGK2_ne0xA%40mail.gmail.com>
Precedence: bulk

--00000000000077d42305f638e0da
Content-Type: text/plain; charset="UTF-8"

Hello all,

I would like to implement transaction control in a function implemented in
C language. Currently I'm using SPI to do the job.
When calling SPI_commit() it returns this error message:
SQL Error [XX000]: ERROR: portal snapshots (0) did not account for all
active snapshots (1)
Below, there is an example.

PG_FUNCTION_INFO_V1(my_commit);

Datum my_commit(PG_FUNCTION_ARGS)
{
    SPIExecuteOptions execopts;

    SPI_connect_ext(SPI_OPT_NONATOMIC);

    memset(&execopts, 0, sizeof(execopts));
    execopts.params = NULL;
    execopts.read_only = false;
    execopts.allow_nonatomic = true;
    execopts.must_return_tuples = false;
    execopts.tcount = 0;
    execopts.dest = NULL;
    execopts.owner = NULL;

    /* Execute some SQL commands */
    SPI_execute_extended("UPDATE mytable SET name = 'done'", &execopts);

    /* Commit the transaction */
    SPI_commit();

    SPI_finish();

    PG_RETURN_NULL();
}

Thanks in advance,
Flaris Feller

--00000000000077d42305f638e0da
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div>Hello all,</div><div><br></div><div>I would like to i=
mplement transaction control in a function implemented in C language. Curre=
ntly I&#39;m using SPI to do the job.</div><div>When calling SPI_commit() i=
t returns this error message:</div><div>SQL Error [XX000]: ERROR: portal sn=
apshots (0) did not account for all active snapshots (1)</div><div>Below, t=
here is an example.</div><div><br>PG_FUNCTION_INFO_V1(my_commit);<br><br>Da=
tum my_commit(PG_FUNCTION_ARGS)<br>{<br>=C2=A0 =C2=A0 SPIExecuteOptions exe=
copts;<br><br>=C2=A0 =C2=A0 SPI_connect_ext(SPI_OPT_NONATOMIC);<br><br>=C2=
=A0 =C2=A0 memset(&amp;execopts, 0, sizeof(execopts));<br>=C2=A0 =C2=A0 exe=
copts.params =3D NULL;<br>=C2=A0 =C2=A0 execopts.read_only =3D false;<br>=
=C2=A0 =C2=A0 execopts.allow_nonatomic =3D true;<br>=C2=A0 =C2=A0 execopts.=
must_return_tuples =3D false;<br>=C2=A0 =C2=A0 execopts.tcount =3D 0;<br>=
=C2=A0 =C2=A0 execopts.dest =3D NULL;<br>=C2=A0 =C2=A0 execopts.owner =3D N=
ULL;<br><br>=C2=A0 =C2=A0 /* Execute some SQL commands */<br>=C2=A0 =C2=A0 =
SPI_execute_extended(&quot;UPDATE mytable SET name =3D &#39;done&#39;&quot;=
, &amp;execopts);<br><br>=C2=A0 =C2=A0 /* Commit the transaction */<br>=C2=
=A0 =C2=A0 SPI_commit();<br><br>=C2=A0 =C2=A0 SPI_finish();<br><br>=C2=A0 =
=C2=A0 PG_RETURN_NULL();<br>}<br></div><div><br></div><div>Thanks in advanc=
e,</div><div>Flaris Feller<br></div></div>

--00000000000077d42305f638e0da--