ERROR: function cannot execute on a QE slice because it accesses relation XX (seg5 slice2 172.25.7.122:45001 pid=14783)

[Pilar de Teodoro]

Hi all,

First of all, hope you are all safe and healthy. Things are not good in Madrid but I am happy to see the group is working as good as usual.

I have found an issue that I do not know how to workaround:

My query is a crossmatch of 2 tables having star information done using q3c extension which looks at stars that are in a certain radius:

SELECT  k.id, gdr2.source_id AS iddr2

FROM euclid.kids_dr4 AS k, 

gaiadr2.gaia_source_2M AS gdr2

WHERE q3c_join(get_ra(gdr2.source_id, 'gaiadr2.gaia_source_2M'),get_dec(gdr2.source_id, 'gaiadr2.gaia_source_2M'), k.raj2000::double precision, k.decj2000::double precision, 0.5/3600);

get_ra and get_dec are 2 functions needed to propagate the coordinates taking in different epochs, that means to apply another function to get the right position so both tables are in same kind of coordinates.

get_ra looks like:

create or replace function get_ra(p_source_id bigint,p_table text) 

returns double precision 

as $$ 

declare 

l_lat double precision; 

begin 

execute 'with spunto as(select EPOCH_PROP_POS(m.ra, m.dec, m.parallax, m.pmra, m.pmdec, m.radial_velocity, 2015.5, 2000) as valor from '||p_table||' m where m.source_id='||p_source_id||') select lat(valor) from spunto' into l_lat; 

return l_lat; end;$$

LANGUAGE plpgsql;

where epoch_prop_pos is another function and lat another one from pgsphere extension.

the result I got:

ERROR: function cannot execute on a QE slice because it accesses relation "gaiadr2.gaia_source_2m" (seg5 slice2 172.25.7.122:45001 pid=14783)

CONTEXT: SQL statement "with spunto as(select EPOCH_PROP_POS(m.ra, m.dec, m.parallax, m.pmra, m.pmdec, m.radial_velocity, 2015.5, 2000) as valor from gaiadr2.gaia_source_2M m where m.source_id=1866715214893245824) select lat(valor) from spunto" PL/pgSQL function get_ra(bigint,text) line 5 at EXECUTE statement SQL state: 0A000

Is it not possible to execute in Greenplum a query like this?

Thank you very much,

Pilar

[Heikki Linnakangas]

Hi!

The problem is that the function executes on the segments, but it also
tries to access the table 'gaia_source_2m'. That's not allowed in GPDB,
because when the function runs on one segment, it won't see the rows
that reside on the other segments. A couple of things you could try to
work around that:

1. If the 'gaiadr2.gaia_source_2m' table isn't too large, you could
change it into a replicated table.

ALTER TABLE gaiadr2.gaia_source_2m SET DISTRIBUTED REPLICATED;

Replicated tables can be accessed by functions on all segments.

2. The get_ra function takes 'p_source_id' and 'p_table' as arguments,
so that it can fetch that row from the given table. You could pass that
whole row to the function as argument, instead. That would be faster
anyway, as you already have that row available in the surrounding query.
So something like this:

create or replace function get_ra(m gaiadr2.gaia_source_2m)

returns double precision
as $$

select lat(EPOCH_PROP_POS(m.ra, m.dec, m.parallax, m.pmra, m.pmdec,
m.radial_velocity, 2015.5, 2000))
$$ language sql stable;

And formulate the query as:

SELECT k.id, gdr2.source_id AS iddr2
FROM euclid.kids_dr4 AS k,
gaiadr2.gaia_source_2M AS gdr2

WHERE q3c_join(get_ra(gdr2.*),get_dec(gdr2.*), k.raj2000::double

precision, k.decj2000::double
precision, 0.5/3600);

This is completely untested as I don't have the full schema, so it's
probably not quite right, but you get the idea. And I assume that
get_dec() similar refactoring as get_ra().

- Heikki

[Pilar de Teodoro]

Thank you very much Heikki!

First option is not possible since that table is 1,6B rows and about 1,2TB in size but second option works. The only thing is that we will need to create a function for every table that needs epoch propagation. Which is better than anything. I have tested and it works fine.

Best regards,

Pilar