Thread: segfault with plproxy
Hello, I have a problem with PL/Proxy (sorry for not posting to plproxy-users, but I have some problem subscribing there). I try to use it to achieve "single node paralellism" - as MattK nicely put it on http://dba.stackexchange.com/questions/9097/single-node-parallelism-with-pl-proxy My setup is: -- Ubuntu 10.04 -- PostgreSQL 9.1.2 + PL/Proxy 2.3, both compiled from source and installed to $HOME/pg91/ -- Data directory in $HOME/pgdata91/ -- config all default except port=5910 plus logging Following scrip causes segmentation fault. Any ideas why / how to diagnose? drop database if exists testdb; create database testdb; drop user if exists part0; create user part0; drop user if exists part1; create user part1; \c testdb -- master table create table public.users(id serial primary key, name text not null); -- part0 create schema authorization part0; create table part0.users( check(id%2=0) ) inherits (public.users); create or replace function part0.list_users(condition text) returns table(id int,name text) language sql as $$ select id,name from part0.users where name like $1; $$; grant all on all tables in schema part0 to part0; grant all on all functions in schema part0 to part0; -- part1 (identical to part0) create schema authorization part1; create table part1.users( check(id%2=1) ) inherits (public.users); create or replace function part1.list_users(condition text) returns table(id int,name text) language sql as $$ select id,name from part1.users where name like $1; $$; grant all on all tables in schema part1 to part1; grant all on all functions in schema part1 to part1; \i /home/filip/pg91/share/postgresql/contrib/plproxy.sql --router CREATE SERVER testplproxy FOREIGN DATA WRAPPER plproxy OPTIONS ( connection_lifetime '1800', p0 'dbname=testdb host=127.0.0.1 port=5901 user=part0', p1 'dbname=testdb host=127.0.0.1 port=5901 user=part1' ); CREATE USER MAPPING FOR PUBLIC SERVER testplproxy; GRANT USAGE ON FOREIGN SERVER testplproxy TO public; -- router create or replace function public.list_users(condition text) returns table(id int,name text) language plproxy as $$ cluster 'testplproxy'; run on all; $$; select * from public.list_users('%xyz%'); -- crash with segfault
On Sat, Dec 17, 2011 at 10:25:40PM +0100, Filip Rembiałkowski wrote: > Following scrip causes segmentation fault. Any ideas why / how to diagnose? > create table part0.users( check(id%2=0) ) inherits (public.users); > create table part1.users( check(id%2=1) ) inherits (public.users); > create or replace function public.list_users(condition text) > select * from public.list_users('%xyz%'); -- crash with segfault It seems you are making plproxy call public.list_users() recursively. Postgres probably OOM-s somewhere then. Either move plproxy function to some other db, or use TARGET/SELECT to pick different target function. -- marko
W dniu 19 grudnia 2011 10:39 użytkownik Marko Kreen <markokr@gmail.com> napisał: > On Sat, Dec 17, 2011 at 10:25:40PM +0100, Filip Rembiałkowski wrote: >> Following scrip causes segmentation fault. Any ideas why / how to diagnose? > >> create table part0.users( check(id%2=0) ) inherits (public.users); >> create table part1.users( check(id%2=1) ) inherits (public.users); >> create or replace function public.list_users(condition text) > >> select * from public.list_users('%xyz%'); -- crash with segfault > > It seems you are making plproxy call public.list_users() recursively. > Postgres probably OOM-s somewhere then. > > Either move plproxy function to some other db, or use > TARGET/SELECT to pick different target function. Thanks Marko, So is this "single-database, schemas mimic nodes" setup possible to achieve at all? My intention was: #1. client calls func() #2. plproxy calls func() on part0. part0 is defined as 'user=part0' so it directs to part0.func() thanks to current_schema setting. #3. plproxy calls func() on part1 (paralell to #2). logic same as #2. #4. plproxy combines result and sends it to client. Is schema a part of function signature? regards, Filip
W dniu 19 grudnia 2011 10:39 użytkownik Marko Kreen <markokr@gmail.com> napisał: > It seems you are making plproxy call public.list_users() recursively. > Postgres probably OOM-s somewhere then. I have log_statement='all' and the function is called only once: 2011-12-19 13:15:11 CET 20416 [local] testdb filip LOG: statement: select * from list_users('%xyz%'); 2011-12-19 13:15:11 CET 20400 LOG: server process (PID 20416) was terminated by signal 11: Segmentation fault
On Mon, Dec 19, 2011 at 01:05:20PM +0100, Filip Rembiałkowski wrote: > W dniu 19 grudnia 2011 10:39 użytkownik Marko Kreen <markokr@gmail.com> napisał: > > On Sat, Dec 17, 2011 at 10:25:40PM +0100, Filip Rembiałkowski wrote: > >> Following scrip causes segmentation fault. Any ideas why / how to diagnose? > > > >> create table part0.users( check(id%2=0) ) inherits (public.users); > >> create table part1.users( check(id%2=1) ) inherits (public.users); > >> create or replace function public.list_users(condition text) > > > >> select * from public.list_users('%xyz%'); -- crash with segfault > > > > It seems you are making plproxy call public.list_users() recursively. > > Postgres probably OOM-s somewhere then. > > > > Either move plproxy function to some other db, or use > > TARGET/SELECT to pick different target function. > > > Thanks Marko, > > So is this "single-database, schemas mimic nodes" setup possible to > achieve at all? Yes, you just need to avoid calling same function recursively, thats all. > > My intention was: > > #1. client calls func() > > #2. plproxy calls func() on part0. part0 is defined as 'user=part0' so > it directs to part0.func() thanks to current_schema setting. This won't work, plproxy always uses fully-qualified names. > #3. plproxy calls func() on part1 (paralell to #2). logic same as #2. > > #4. plproxy combines result and sends it to client. > > > Is schema a part of function signature? Yes. -- marko
W dniu 20 grudnia 2011 15:36 użytkownik Marko Kreen <markokr@gmail.com> napisał: >> Is schema a part of function signature? > > Yes. Thanks again, that explains everything. In the meantime, depesz has a solution basing on application_name, not on username+schema as I tried. http://www.depesz.com/index.php/2011/12/02/the-secret-ingredient-in-the-webscale-sauce/ - "many shards within the same database".