connectivity issues with a plproxy function

From: robert7390(at)comcast(dot)net
To: pgsql-jdbc(at)postgresql(dot)org
Subject: connectivity issues with a plproxy function
Date: 2010-06-06 22:12:02
Message-ID: 159042738.834991275862322729.JavaMail.root@sz0163a.emeryville.ca.mail.comcast.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi,

I've got a JDBC client that's sending rapid fire inserts to a database. There's two methods I've used: a function call via plproxy and the plpgsql function call on the database in question. The proxy function is only inserting SOME values into the table. Otherwise,the operation works fine when the JDBC client is connecting directly to the function in the database, bypassing the proxy function.

Here's the plroxy function call on the database "my_proxy";

CREATE OR REPLACE FUNCTION proxy_function.f_one (
bigint,
varchar(255),
int,
out did_insert bool
) AS
$$
cluster 'uds';
run on 0;
$$
language plproxy
strict security invoker;

And here's the invoked function on the database "mydatabase".

CREATE OR REPLACE FUNCTION proxy_function.f_one (
IN id_val bigint,
IN name_val character varying(255),
IN carrier_id_in integer,
OUT did_insert boolean
) AS
$$
BEGIN
did_insert := false;
if not exists(select id from mytable where id=id_val) then
begin
insert into mytable (id, name, carrier_id) values (id_val, name_val, carrier_id_in);
did_insert := true;
end;
end if;
END;
$$
LANGUAGE PLPGSQL
VOLATILE
STRICT SECURITY DEFINER;

I'd appreciate any insight.

Thanks

Robert

Browse pgsql-jdbc by date

  From Date Subject
Next Message robert7390 2010-06-06 22:18:53 connectivity issues
Previous Message Lew 2010-06-05 23:13:34 Re: jdbc works in java app, fails in servlet: "no suitable jdbc found"