Обсуждение: Executing a Function with an INSERT INTO command fails

Hello,

I have this function that executes a C extention function in it and returns
a SETOF TEXT

    CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams
CHARACTER VARYING) RETURNS SETOF TEXT AS $outputVar$
    DECLARE
        tempVar1 CHARACTER VARYING;
        tempVar2 CHARACTER VARYING;
        outputVar text;
        sealArray TEXT[];
        outputArray TEXT[];
    BEGIN
        FOR i IN 1..2 LOOP
            SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, tempVar2
FROM public.nyc2015_09_enc WHERE id=i;
            sealArray := (SELECT public.seal_diff_benchmark(tempVar1, tempVar2,
sealparams));
            outputArray[i] := sealArray[1];

             INSERT INTO public.runtime_benchmark (test_number, column_names,
execution_time, operation_type, seal_or_sql) VALUES (1, 'Pickup_longitude,
Dropoff_longitude', sealArray[2], 'sub', 'seal');

        END LOOP;

        FOREACH outputVar IN ARRAY outputArray LOOP
            RETURN NEXT outputVar;
        END LOOP;
    END;
    $outputVar$ LANGUAGE plpgsql;

Inside the first FOR LOOP... I've an INSERT INTO... command but nothing gets
inserted into the public.runtime_benchmark table. Executing the INSERT INTO
command separately works without any problems so why doesn't it work inside
my PostgreSQL function?

The user executing the function above with the INSERT command is alsow the
owner of the public.runtime_benchmark table. Do I have to create a trigger
function for the public.runtime_benchmark table to be able to insert into it
using some other function? Or is there a simpler way to modify the function
above to achieve my goal?

Best regards,
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Hi

> -----Original Message-----
> From: TalGloz [mailto:glozmantal@gmail.com]
> Sent: Mittwoch, 29. August 2018 11:12
> To: pgsql-general@postgresql.org
> Subject: Executing a Function with an INSERT INTO command fails
> 
> Hello,
> 
> I have this function that executes a C extention function in it and returns a SETOF TEXT
> 
>     CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams
> CHARACTER VARYING) RETURNS SETOF TEXT AS $outputVar$
>     DECLARE
>         tempVar1 CHARACTER VARYING;
>         tempVar2 CHARACTER VARYING;
>         outputVar text;
>         sealArray TEXT[];
>         outputArray TEXT[];
>     BEGIN
>         FOR i IN 1..2 LOOP
>             SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, tempVar2 FROM public.nyc2015_09_enc
> WHERE id=i;
>             sealArray := (SELECT public.seal_diff_benchmark(tempVar1, tempVar2, sealparams));
>             outputArray[i] := sealArray[1];
> 
>              INSERT INTO public.runtime_benchmark (test_number, column_names, execution_time, operation_type,
> seal_or_sql) VALUES (1, 'Pickup_longitude, Dropoff_longitude', sealArray[2], 'sub', 'seal');
> 
>         END LOOP;
> 
>         FOREACH outputVar IN ARRAY outputArray LOOP
>             RETURN NEXT outputVar;
>         END LOOP;
>     END;
>     $outputVar$ LANGUAGE plpgsql;
> 
> Inside the first FOR LOOP... I've an INSERT INTO... command but nothing gets inserted into the
> public.runtime_benchmark table. Executing the INSERT INTO command separately works without any problems so why
> doesn't it work inside my PostgreSQL function?
> 
> The user executing the function above with the INSERT command is alsow the owner of the public.runtime_benchmark
> table. Do I have to create a trigger function for the public.runtime_benchmark table to be able to insert into
> it using some other function? Or is there a simpler way to modify the function above to achieve my goal?

No. It is possible to use a normal function.
Do you get any error?

Does the select deliver any result at all?

If yes, is there maybe already a trigger on table public.runtime_benchmark?

If not, is there maybe a policy on either public.nyc2015_09_enc or public.runtime_benchmark?

If not, could you provide more information on the table?

Regards
Charles

> Best regards,
> Tal
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Charles Clavadetscher wrote
> Do you get any error?

The function executes perfectly and does what it's supposed to except of the
INSERT INTO part. I don't get any errors.


> Does the select deliver any result at all?

Yes, booth SELECT deliver everything they supposed to.


> If yes, is there maybe already a trigger on table
> public.runtime_benchmark?

I didn’t crate any triggers for the table manually and I don't think they
are crated automatically.


> If not, is there maybe a policy on either public.nyc2015_09_enc or
> public.runtime_benchmark?

There are no security policy on any of them, at least I didn't set any while
creating the tables with PgAdmin4. I use the tables on a local server for
testing purposes only.


> If not, could you provide more information on the table?

What kind of information should I provide?


Best regards,
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Hi

> -----Original Message-----
> From: TalGloz [mailto:glozmantal@gmail.com]
> Sent: Mittwoch, 29. August 2018 13:22
> To: pgsql-general@postgresql.org
> Subject: RE: Executing a Function with an INSERT INTO command fails
>
> Charles Clavadetscher wrote
> > Do you get any error?
>
> The function executes perfectly and does what it's supposed to except of the INSERT INTO part. I don't get any
> errors.
>
>
> > Does the select deliver any result at all?
>
> Yes, booth SELECT deliver everything they supposed to.
>
>
> > If yes, is there maybe already a trigger on table
> > public.runtime_benchmark?
>
> I didn’t crate any triggers for the table manually and I don't think they are crated automatically.
>
>
> > If not, is there maybe a policy on either public.nyc2015_09_enc or
> > public.runtime_benchmark?
>
> There are no security policy on any of them, at least I didn't set any while creating the tables with PgAdmin4.
> I use the tables on a local server for testing purposes only.
>
>
> > If not, could you provide more information on the table?
>
> What kind of information should I provide?

The point is to try to reproduce the problem. So the table definition as delivered by \d would be a good starting
point.Or the CREATE TABLE generated by pgAdmin. 

While I was having a closer look at the function I noticed that you call another function to populate the sealArray.

In order to try to reproduce the error I did:

Create public.nyc2015_09_enc on assumptions and populate it with some data.

CREATE TABLE public.nyc2015_09_enc
(
  id INTEGER,
  "Pickup_longitude" TEXT,
  "Dropoff_longitude" TEXT
);

INSERT INTO public.nyc2015_09_enc VALUES (1,'47.0','8.0');
INSERT INTO public.nyc2015_09_enc VALUES (2,'49.0','8.5');

SELECT * FROM public.nyc2015_09_enc;
 id | Pickup_longitude | Dropoff_longitude
----+------------------+-------------------
  1 | 47.0             | 8.0
  2 | 49.0             | 8.5
(2 rows)

Create public.runtime_benchmark based on assuptions.

CREATE TABLE public.runtime_benchmark
(
  test_number INTEGER,
  column_names TEXT,
  execution_time TEXT,
  operation_type TEXT,
  seal_or_sql TEXT
);

Create your function. The sealArray creation is modified, because I don't know how the function
public.seal_diff_benchmarkis defined. 

CREATE OR REPLACE FUNCTION seal_diff_benchmark_pgsql(sealparams CHARACTER VARYING)
RETURNS SETOF TEXT
AS $outputVar$
    DECLARE
        tempVar1 CHARACTER VARYING;
        tempVar2 CHARACTER VARYING;
      outputVar text;
        sealArray TEXT[];
        outputArray TEXT[];
    BEGIN
        FOR i IN 1..2 LOOP
            SELECT "Pickup_longitude", "Dropoff_longitude" INTO tempVar1, tempVar2 FROM public.nyc2015_09_enc WHERE
id=i;
            --sealArray := (SELECT public.seal_diff_benchmark(tempVar1, tempVar2, sealparams));
        sealArray := ARRAY[tempVar1, tempVar2, sealparams];
            outputArray[i] := sealArray[1];

             INSERT INTO public.runtime_benchmark (test_number, column_names, execution_time, operation_type,
seal_or_sql)VALUES (1, 'Pickup_longitude, Dropoff_longitude', sealArray[2], 'sub', 'seal'); 

        END LOOP;

        FOREACH outputVar IN ARRAY outputArray LOOP
            RETURN NEXT outputVar;
        END LOOP;
    END;
    $outputVar$ LANGUAGE plpgsql;

Is there any reason for the loop 1..2?

And test it.

SELECT * FROM public.runtime_benchmark ;
 test_number | column_names | execution_time | operation_type | seal_or_sql
-------------+--------------+----------------+----------------+-------------
(0 rows)

SELECT * FROM seal_diff_benchmark_pgsql('0.12');
 seal_diff_benchmark_pgsql
---------------------------
 47.0
 49.0
(2 rows)

SELECT * FROM public.runtime_benchmark ;
 test_number |            column_names             | execution_time | operation_type | seal_or_sql
-------------+-------------------------------------+----------------+----------------+-------------
           1 | Pickup_longitude, Dropoff_longitude | 8.0            | sub            | seal
           1 | Pickup_longitude, Dropoff_longitude | 8.5            | sub            | seal
(2 rows)

Unfortunately I am not able to reproduce the problem, but maybe with the table and functions definitions, as well as
the2 data rows that are selected in the function, is that easier to analyze. 

Regards
Charles

> Best regards,
> Tal
>
>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

This is very strange, even if I comment all the loops in the function and
leave only the INSERT INTO command the insert still doesn't happen.

> The sealArray creation is modified, because I don't know how the function
> public.seal_diff_benchmark is defined.
>>  public.seal_diff_benchmark(tempVar1, tempVar2, sealparams) is a C
>> extension function that takes TEXT 3 parameters does something with them
>> and returns an array of TEXT with  [text_1, text_2] back. The C extension
>> uses some external project libraries compiled into a .so file and copied
>> into the pgsql-10/lib/.
> Is there any reason for the loop 1..2? 

This is just for doing some tests, the number 2 will be replaced later with
some parameter.

Best regards,
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

TalGloz <glozmantal@gmail.com> writes:
> This is very strange, even if I comment all the loops in the function and
> leave only the INSERT INTO command the insert still doesn't happen.

Maybe you're rolling back the transaction after leaving the function?

            regards, tom lane

> Add "RAISE ERROR" to the top function's BEGIN block and verify that the
> next time you run your calling query it indeed fails.  I'm suspecting that
> the code you are iterating over is not the same code that is being
> executed
> (search_path dynamics probably).

Do you mean like this
BEGIN
RAISE NOTICE
.
.
INSERT INTO public.runtime_benchmark (test_number, column_names,
execution_time, operation_type, seal_or_sql) VALUES (1, 'Pickup_longitude,
Dropoff_longitude', '1', 'sub', 'seal');
.
.
END;

If I use RAISE ERROR i get ERROR:  unrecognized exception condition "error"

Tal




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

> I was referring specifically to:
> 
> https://www.postgresql.org/docs/10/static/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE
> 
> I was going for a conceptual communication, not syntax.
> 
> Though if you indeed get a syntax error then that precludes the "wrong
> object" theory.

I hoe I got it right from the post, If I set RAISE EXCEPTION just right
after the beginning then I get

ERROR:  syntax error at or near "INSERT"
LINE 31:  INSERT INTO public.runtime_benchmark (test_number, column_n...

Tal




--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Tom Lane-2 wrote
> Maybe you're rolling back the transaction after leaving the function?

Why would it happen and how do I stop it if it is happening. I don't seem to
have anything in the code to cause it....

Regards
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

> This is very strange, even if I comment all the loops in the function and
> leave only the INSERT INTO command the insert still doesn't happen.

Now If I execute the function locally in a query window like this: 

SELECT public.seal_diff_benchmark_pgsql('YW55IGNhcm5hbCBwbGVhc3VyZQ==')

Then the INSERT INTO command fires and values are being inserted into the
public.runtime_benchmark table. But when I execute the command form a Client
code (C++) on a different pc using the libpqxx library, then everything else
in the function works except the  INSERT INTO command. I use the right
credentials since everything else in the function works perfectly.

So why is this happening?

Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Hi

> On 29.08.2018, at 19:45, TalGloz <glozmantal@gmail.com> wrote:
>
>
>> This is very strange, even if I comment all the loops in the function and
>> leave only the INSERT INTO command the insert still doesn't happen.
>
> Now If I execute the function locally in a query window like this:
>
> SELECT public.seal_diff_benchmark_pgsql('YW55IGNhcm5hbCBwbGVhc3VyZQ==')
>
> Then the INSERT INTO command fires and values are being inserted into the
> public.runtime_benchmark table. But when I execute the command form a Client
> code (C++) on a different pc using the libpqxx library, then everything else
> in the function works except the  INSERT INTO command. I use the right
> credentials since everything else in the function works perfectly.
>
> So why is this happening?
>
> Tal
>

Tom's assumption is possibly correct. If the client does not have autocommit set then you need to explicitly commit the
transaction.

>
>
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
>

> Tom's assumption is possibly correct. If the client does not have
> autocommit set then you need to explicitly commit the transaction.

I feel a little bit stupid , I forgot the use Work.commit(); in the function
on the client side!

Thanks for the help everybody!
Tal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html