Re: UNION ALL

2019-08-15 Thread 066ce286
Generally speaking, when executing UNION ; a DISTINCT is run afterward on the 
resultset.

So, if you're sure that each part of UNION cannot return a line returned by 
another one, you may use UNION ALL, you'll cut the cost of the final implicit 
DISTINCT.


- Mail original -
De: "Mark Pasterkamp" 
À: pgsql-hackers@lists.postgresql.org
Envoyé: Jeudi 15 Août 2019 20:37:06
Objet: UNION ALL


Dear all, 


I was wondering if someone could help me understands what a union all actually 
does. 


For my thesis I am using Apache Calcite to rewrite queries into using 
materialized views which I then give to a Postgres database. 
For some queries, this means that they will be rewritten in a UNION ALL style 
query between an expression and a table scan of a materialized view. 
However, contrary to what I expected, the UNION ALL query is actually a lot 
slower. 


As an example, say I have 2 tables: actor and movie. Furthermore, there is also 
a foreign key index on movie to actor. 
I also have a materialized view with the join of these 2 tables for all movies 
<= 2015 called A. 
Now, if I want to query all entries in the join between actor and movie, I 
would assume that a UNION ALL between the join of actor and movie for movies 
>2015 and A is faster than executing the original query.. 
If I look at the explain analyze part, I can certainly see a reduction in cost 
up until the UNION ALL part, which carries a respective cost more than negating 
the cost reduction up to a point where I might as well not use the existing 
materialized view. 


I have some trouble understanding this phenomenon. 
One thought which came to my mind was that perhaps UNION ALL might create a 
temporary table containing both result sets, and then do a table scan and 
return that result. 

this would greatly increase IO cost which could attribute to the problem. 
However, I am really not sure what UNION ALL actually does to append both 
result sets so I was wondering if someone would be able to help me out with 
this. 




Mark




Re: mysql_fdw crash

2018-11-20 Thread 066ce286
Hi,

>Seems some basic mistake I think it should as below
>(*param_types)[i] = exprType(param_expr);
>
>After this it works


Seems to work fine from my side.

Thank you very much, it'd painful for me to find the bug, I've been too far 
away from C coding for a too long time :-(

-- 
Hervé LEFEBVRE



Re: mysql_fdw crash

2018-11-20 Thread 066ce286
Hi,


>When gdb will be active, then use command c, and then run query in session. 
>gdb should to catch segfault. 



Thank you very much. It's been helpfull.

BTW behaviour is strange. When I'm executing following, I do have always a SEGV 
:

psql (11.1)
Type "help" for help.

herve=# CREATE OR REPLACE FUNCTION public.test_bug2(text,integer,timestamp with 
time zone)
herve-#  RETURNS integer
herve-# 
herve-# AS '
herve'# 
herve'# select coalesce(max(id),1) from sact_v1.autocalls where  label=$1 
and machine_id=$2 and created_date=$3;
herve'# '
herve-#  LANGUAGE sql;
CREATE FUNCTION
herve=# select test_bug2('BSM_CRITICAL_SYSLOG',18843,now());

The GDB session :

Continuing.

Program received signal SIGSEGV, Segmentation fault.
prepare_query_params (param_types=0x1c86ac8, param_values=0x1c86ac0, 
param_exprs=0x1c86ab8, param_flinfo=0x1c86ab0, numParams=3, 
fdw_exprs=0x1c6b5b8, node=0x1c792d8) at mysql_fdw.c:2139
2139*param_types[i] = exprType(param_expr);
(gdb) bt
#0  prepare_query_params (param_types=0x1c86ac8, param_values=0x1c86ac0, 
param_exprs=0x1c86ab8, param_flinfo=0x1c86ab0, numParams=3, 
fdw_exprs=0x1c6b5b8, node=0x1c792d8)
at mysql_fdw.c:2139
#1  mysqlBeginForeignScan (node=0x1c792d8, eflags=) at 
mysql_fdw.c:503
#2  0x0062ae94 in ExecInitForeignScan ()
#3  0x006077bf in ExecInitNode ()
#4  0x0061117d in ExecInitAgg ()
#5  0x00607717 in ExecInitNode ()
#6  0x00601cf4 in standard_ExecutorStart ()
#7  0x0060d6ec in fmgr_sql ()
#8  0x005fd504 in ExecInterpExpr ()
#9  0x006258fb in ExecResult ()
#10 0x006009aa in standard_ExecutorRun ()
#11 0x0073eaec in PortalRunSelect ()
#12 0x0073fede in PortalRun ()
#13 0x0073bd82 in exec_simple_query ()
#14 0x0073d249 in PostgresMain ()
#15 0x0047cff6 in ServerLoop ()
#16 0x006cf7b3 in PostmasterMain ()
#17 0x0047ded1 in main ()

What is confusing, is that if I do the same with a pl/pgsql function (see 
below) I can run it 5 times, and the 6th exec hit the same SEGV...


CREATE OR REPLACE FUNCTION public.test_bug(text,text)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
plabel ALIAS FOR $1;
spmachine_id ALIAS FOR $2;
rid integer;
lnow timestamp with time zone;
pmachine_id INTEGER;

BEGIN
pmachine_id := cast(spmachine_id as INTEGER);
lnow:=now();

select max(id) into rid from sact_v1.autocalls where  label=plabel and 
machine_id=pmachine_id and created_date=lnow;
rid := coalesce(rid,-1);

return  rid;
END;
$function$;

CREATE FUNCTION
herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
--
   -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
--
   -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
--
   -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
--
   -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');
 test_bug 
--
   -1
(1 row)

herve=# select test_bug('BSM_CRITICAL_SYSLOG','18843');



Program received signal SIGSEGV, Segmentation fault.
prepare_query_params (param_types=0x1ca3558, param_values=0x1ca3550, 
param_exprs=0x1ca3548, param_flinfo=0x1ca3540, numParams=3, 
fdw_exprs=0x1ca8638, node=0x1cade28) at mysql_fdw.c:2139
2139*param_types[i] = exprType(param_expr);
(gdb) bt
#0  prepare_query_params (param_types=0x1ca3558, param_values=0x1ca3550, 
param_exprs=0x1ca3548, param_flinfo=0x1ca3540, numParams=3, 
fdw_exprs=0x1ca8638, node=0x1cade28)
at mysql_fdw.c:2139
#1  mysqlBeginForeignScan (node=0x1cade28, eflags=) at 
mysql_fdw.c:503
#2  0x0062ae94 in ExecInitForeignScan ()
#3  0x006077bf in ExecInitNode ()
#4  0x0061117d in ExecInitAgg ()
#5  0x00607717 in ExecInitNode ()
#6  0x00601cf4 in standard_ExecutorStart ()
#7  0x00632946 in _SPI_execute_plan ()
#8  0x00632d0b in SPI_execute_plan_with_paramlist ()
#9  0x7ffb349aba22 in exec_stmt_execsql () from 
/usr/local/pgsql/lib/plpgsql.so
#10 0x7ffb349ace43 in exec_stmts () from /usr/local/pgsql/lib/plpgsql.so
#11 0x7ffb349af6d3 in exec_stmt_block () from 
/usr/local/pgsql/lib/plpgsql.so
#12 0x7ffb349af88f in plpgsql_exec_function () from 
/usr/local/pgsql/lib/plpgsql.so
#13 0x7ffb349a3375 in plpgsql_call_handler () from 
/usr/local/pgsql/lib/plpgsql.so
#14 0x005fd504 in ExecInterpExpr ()
#15 0x006258fb in ExecResult ()
#16 0x006009aa in standard_ExecutorRun ()
#17 0x0073eaec in PortalRunSelect ()
#18 0x0073fede in PortalRun ()
#19 0x0073bd82 in exec_simple_query ()
#20 0x0073d249 in PostgresMain ()
#21 0x0047cff6 in ServerLoop ()
#22 0x006cf7b3 in PostmasterMain ()
#23 0x0047ded1 in main ()








mysql_fdw crash

2018-11-20 Thread 066ce286
Hi,

I do have a reproductible crash with mysql_fdw when executing a plpgsql 
function. I'm running pg 11.1 with current mysql_fdw, but I had the same crash 
with the pg 9.6 and mysql_fdw provided with ubuntu packages.

>From psql side :

server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Failed.
!>

In syslog :

Nov 20 10:52:58 sact2Dev kernel: [322982.294765] postgres[10364]: segfault at 0 
ip 7fc8ab7b5350 sp 7ffc4312a4f0 error 6 in 
mysql_fdw.so[7fc8ab7ac000+d000]

I've located the crash cause on the line :

*param_types[i] = exprType(param_expr);

( file mysql_fdw.c ; function prepare_query_params() ; in the forEach() loop)

I've recompiled the fdw with a -g option. Could you please tell me (or point me 
a documentation) how to have a core dump from the segfaulted lib ; so that I 
can open it in a debugger to inspect variable contents ?

Or any advice ?

Thank you.