Re: 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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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(at)gmail(dot)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".