postgrespro / vops Goto Github PK

Hello，
Does vops support native partition in PostgreSQL 10? How shuld I do to use VOPS in partition tables?
Can you provide me a demo?

Hello,
As I would like to test VOPS, I'm trying to build it with code (b8edd2b) on Ubuntu (using postgresql-server-dev-9.5/xenial-updates,now 9.5.9).

I have the following error:

make USE_PGXS=1
...
vops.c: In function ‘vops_expression_tree_mutator’:
vops.c:2705:37: error: ‘Aggref {aka struct Aggref}’ has no member named ‘aggargtypes’
if (is_vops_type(linitial_oid(agg->aggargtypes))) {

May be I'm not using the good version ?
Regards
PAscal

as title.

Hello，
Another error was found when I execute 'count(*)', Please check.
1:
VOPS_TEST=# create table std_table(pk_column int2,column_int2 int2, column_int4 int4, column_int8 int8);
CREATE TABLE
2:
INSERT INTO std_table VALUES(random()*100,2,5,random()*1000);
INSERT INTO std_table VALUES(random()*100,2,5,random()*1000);
INSERT INTO std_table VALUES(random()*100,7,3,random()*1000);

3:
VOPS_TEST=# table std_table;
pk_column | column_int2 | column_int4 | column_int8
-----------+-------------+-------------+-------------
68 | 2 | 5 | 339
92 | 2 | 5 | 607
90 | 7 | 3 | 372
(3 rows)

4:
VOPS_TEST=# create table vops_table_gb(pk_column vops_int2,column_int2 int2, column_int4 int4, column_int8 vops_int8);
CREATE TABLE

5:
VOPS_TEST=# select populate(destination:='vops_table_gb'::regclass, source:='std_table'::regclass, sort := 'column_int2,column_int4');
populate
----------
3
(1 row)

6:
VOPS_TEST=# select column_int2,column_int4,column_int8 from vops_table_gb;
column_int2 | column_int4 | column_int8
-------------+-------------+-------------------------------------------------------------------------
2 | 5 | {339,607,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,}
7 | 3 | {372,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,}
(2 rows)

7:
VOPS_TEST=# SELECT column_int2,column_int4,sum(column_int8),count(*) FROM std_table GROUP BY column_int2,column_int4 ORDER BY column_int2,column_int4;
column_int2 | column_int4 | sum | count
-------------+-------------+-----+-------
2 | 5 | 946 | 2
7 | 3 | 372 | 1
(2 rows)

8:

VOPS_TEST=# SELECT column_int2,column_int4,sum(column_int8),count(*) FROM vops_table_gb GROUP BY column_int2,column_int4 ORDER BY column_int2,column_int4;
column_int2 | column_int4 | sum | count
-------------+-------------+-----+-------
2 | 5 | 946 | 64
7 | 3 | 372 | 64
(2 rows)

Error on load data in VOPS table:row is too big

Hello,
When I load data in VOPS table using VOPS populate(...) function and VOPS import(...) function,An error occurred :
VOPS=# select populate(destination:='vops_table'::regclass, source:='std_table'::regclass);
ERROR: row is too big: size 16392, maximum size 8160

VOPS=# select import(destination := 'vops_table'::regclass, csv_path := '/data/vops_data/std_table.csv', separator := '|');
ERROR: row is too big: size 16392, maximum size 8160 CONTEXT: IMPORT vops_table, line 65, column 63

'std_table' is a normal table which contains 64 columns.The type of all columns are int(int2、int4、int8) or float(float4、float8).

I tried to solve the problem,but the methods that I tried did not work.

So how to solve this problem ?.

best regards, thank you.

We were running the example queries from your README, and noticed that heap and vops tables don't necessarily return the same results. When we ran this query, we noticed that the float4 columns didn't match with the equivalent query in heap:

-- Mixed mode: let's Postgres does group by and calculates VOPS aggregates for each group
select
    l_returnflag,
    l_linestatus,
    sum(l_quantity) as sum_qty,
    sum(l_extendedprice) as sum_base_price,
    sum(l_extendedprice*(1-l_discount)) as sum_disc_price,
    sum(l_extendedprice*(1-l_discount)*(1+l_tax)) as sum_charge,
    avg(l_quantity) as avg_qty,
    avg(l_extendedprice) as avg_price,
    avg(l_discount) as avg_disc,
    count(*) as count_order
from
    vops_lineitem_projection
where
    l_shipdate <= '1998-12-01'::date
group by
    l_returnflag,
    l_linestatus
order by
    l_returnflag,
    l_linestatus;

In a simplified example:

CREATE TABLE
pivotal=# create table vops_foo(a vops_float4);
CREATE TABLE
pivotal=# insert into foo select (i+.1258) from generate_series(1, 1000000)i;
INSERT 0 1000000
pivotal=# select populate(destination := 'vops_foo'::regclass, source := 'foo'::regclass, sort := 'a');
 populate
----------
  1000000
(1 row)

When we run sum(a) on foo, it gets a number that is significantly different than a sum on vops_foo:

pivotal=# select sum(a) from foo;
      sum
---------------
 5.0000986e+11
(1 row)

pivotal=# select sum(a) from vops_foo;
        sum
-------------------
 500000625015.1876
(1 row)

Why does this happen? Is vops_float4 incompatible with the standard float4? Is this a bug?

I am first time to use vops,but I can not find how to install vops?
Are there any blogs?

После установки расширения у меня появилась проблема с тем, что перестал работать pgAdmin 4.0, аналогично отваливается DataGrip при получении объектов из схемы public.

Возникает следующая ошибка:

with schema_procs as (select prorettype, proargtypes, proallargtypes from pg_catalog.pg_proc where pronamespace = ?::oid and xmin::varchar::bigint > ? ), schema_opers as (select oprleft, oprright, oprresult from pg_catalog.pg_operator where oprnamespace = ?::oid and xmin::varchar::bigint > ? ), schema_aggregates as (select A.aggtranstype , A.aggmtranstype from pg_catalog.pg_aggregate A join pg_catalog.pg_proc P on A.aggfnoid = P.oid where P.pronamespace = ?::oid and (A.xmin::varchar::bigint > ? or P.xmin::varchar::bigint > ?) ), schema_arg_types as ( select prorettype as type_id from schema_procs union select distinct unnest(proargtypes) as type_id from schema_procs union select distinct unnest(proallargtypes) as type_id from schema_procs union select oprleft as type_id from schema_opers where oprleft is not null union select oprright as type_id from schema_opers where oprright is not null union select oprresult as type_id from schema_opers where oprresult is not null union select aggtranstype::oid as type_id from schema_aggregates union select aggmtranstype::oid as type_id from schema_aggregates ) select type_id, pg_catalog.format_type(type_id, null) as type_spec from schema_arg_types where type_id
0 [42725] org.postgresql.util.PSQLException: ОШИБКА: функция unnest(oidvector) не уникальна Подсказка: Не удалось выбрать лучшую кандидатуру функции. Возможно, вам следует добавить явные приведения типов. Позиция: 1006

После удаления расширения ошибка пропадает.

Используемая версия Postgres:
PostgresPro 11.1.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.3.0-27ubuntu1~18.04) 7.3.0, 64-bit

Установленные расширения:

[pgrel@centos01 ~]$ psql -h /pgreltmp/ postgres
psql (9.6.2)
Type "help" for help.

postgres=# select sum(review_rating) from vops_customer_reviews ;
sum

(1 row)

postgres=# select sum(review_rating) from vops_customer_reviews ;
sum

2602248
(1 row)

Is there any install guide for vops?

Please, add type vops_interval for system type interval.

Hello Konstantin,

I shared thoses informations here, for other users that may hit the same problem.

SELECT
public.wiki_page.pagename,
sum(public.fdw_data.requests)
FROM
public.wiki_cat
INNER JOIN public.fdw_data
ON public.wiki_cat.cat_id = public.fdw_data.cat_id
INNER JOIN public.wiki_page
ON public.fdw_data.page_id = public.wiki_page.page_id
where
public.wiki_cat.category='en'
and public.wiki_page.pagename ='List_of_stock_market_crashes_and_bear_markets'
GROUP BY
public.wiki_page.pagename
ORDER BY
sum(public.fdw_data.requests) DESC
limit 10
;

sudo -u postgres gdb -q -c /var/lib/postgresql/10/main/core /usr/lib/postgresql/10/bin/postgres
Reading symbols from /usr/lib/postgresql/10/bin/postgres...(no debugging symbols found)...done.
[New LWP 4479]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/i386-linux-gnu/libthread_db.so.1".
Core was generated by `postgres: 10/main: postgres postgres 127.0.0.1(52050) SELECT '.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 __memcpy_ssse3 () at ../sysdeps/i386/i686/multiarch/memcpy-ssse3.S:2673
2673 ../sysdeps/i386/i686/multiarch/memcpy-ssse3.S: Aucun fichier ou dossier de ce type.
(gdb) bt
#0 __memcpy_ssse3 () at ../sysdeps/i386/i686/multiarch/memcpy-ssse3.S:2673
#1 0xb6cbfb45 in __memcpy_ssse3 ()
at ../sysdeps/i386/i686/multiarch/memcpy-ssse3.S:136
#2 0x080b851c in heap_fill_tuple ()
#3 0x080b92c4 in heap_form_tuple ()
#4 0xb4542eb5 in postgresIterateForeignScan (node=0xa2137f4) at vops_fdw.c:764
#5 0x082400aa in ?? ()
#6 0x08223bca in ExecScan ()
#7 0x0823ff76 in ?? ()
#8 0x082323a1 in ?? ()
#9 0x0823a902 in ?? ()
#10 0x0822921d in ?? ()
#11 0x0822b072 in ?? ()
#12 0x0823cbbd in ?? ()
#13 0x08234e5a in ?? ()
#14 0x0821e2a5 in standard_ExecutorRun ()
#15 0x0834188a in ?? ()
#16 0x08342c0f in PortalRun ()
#17 0x0833fcad in PostgresMain ()
#18 0x080b01e1 in ?? ()
#19 0x082d662b in PostmasterMain ()
#20 0x080b1427 in main ()

Hi,
I am recently test VOPS and find that the result of sum and avg are not the same as PostgreSQL.
The following code is my test case.

postgres=# CREATE EXTENSION vops;
CREATE EXTENSION
postgres=# CREATE TABLE LINEITEM ( L_ORDERKEY    INTEGER NOT NULL,
                             L_PARTKEY     INTEGER NOT NULL,
                             L_SUPPKEY     INTEGER NOT NULL,
                             L_LINENUMBER  INTEGER NOT NULL,
                             L_QUANTITY    DECIMAL(15,2) NOT NULL,
                             L_EXTENDEDPRICE  DECIMAL(15,2) NOT NULL,
                             L_DISCOUNT    DECIMAL(15,2) NOT NULL,
                             L_TAX         DECIMAL(15,2) NOT NULL,
                             L_RETURNFLAG  CHAR(1) NOT NULL,
                             L_LINESTATUS  CHAR(1) NOT NULL,
                             L_SHIPDATE    DATE NOT NULL,
                             L_COMMITDATE  DATE NOT NULL,
                             L_RECEIPTDATE DATE NOT NULL,
                             L_SHIPINSTRUCT CHAR(25) NOT NULL,
                             L_SHIPMODE     CHAR(10) NOT NULL,
                             L_COMMENT      VARCHAR(44) NOT NULL);
CREATE TABLE
postgres=# COPY LINEITEM FROM '/path/to/lineitem1.csv' DELIMITER '|' CSV;
COPY 986052
postgres=# CREATE TABLE vops_discount( l_discount vops_float4 not null);
CREATE TABLE
postgres=# select populate(destination := 'vops_discount'::regclass, source := 'LINEITEM'::regclass);
 populate
----------
   986052
(1 row)

postgres=# select sum(l_discount) from lineitem;
   sum
----------
 49368.98
(1 row)

postgres=# select sum(l_discount) from vops_discount;
         sum
----------------------
 7.17329743361851e-32
(1 row)

postgres=# select avg(l_discount) from lineitem;
          avg
------------------------
 0.05006731896492274241
(1 row)

postgres=# select avg(l_discount) from vops_discount;
         avg
---------------------
 7.2747658679446e-38
(1 row)

The VOPS commit is 7ffe6fd9ffc62e2, and PostgreSQL is REL_10_7.

HI,
I use vops with PG 10 beta1, but when i query the countall with parallel, the result is not correct.
Is it a bug?

the real count is :

postgres=# set max_parallel_workers_per_gather =0;
SET
Time: 0.153 ms
postgres=# select countall(*) from vc;
 countall  
-----------
 150000000
(1 row)
Time: 933.433 ms

parallel

ostgres=# set max_parallel_workers_per_gather =2;
SET
Time: 0.110 ms
postgres=# select countall(*) from vc;
 countall 
----------
 71552512
(1 row)

Time: 503.081 ms
postgres=# explain select countall(*) from vc;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=179617.95..179617.96 rows=1 width=8)
   ->  Gather  (cost=179617.94..179617.95 rows=2 width=8)
         Workers Planned: 2
         ->  Partial Aggregate  (cost=179617.94..179617.95 rows=1 width=8)
               ->  Parallel Seq Scan on vc  (cost=0.00..177176.55 rows=976555 width=0)
(5 rows)

Time: 0.313 ms


postgres=# set max_parallel_workers_per_gather =4;
SET
Time: 0.134 ms
postgres=# explain select countall(*) from vc;
                                      QUERY PLAN                                       
---------------------------------------------------------------------------------------
 Finalize Aggregate  (cost=174735.18..174735.19 rows=1 width=8)
   ->  Gather  (cost=174735.16..174735.17 rows=4 width=8)
         Workers Planned: 4
         ->  Partial Aggregate  (cost=174735.16..174735.17 rows=1 width=8)
               ->  Parallel Seq Scan on vc  (cost=0.00..173270.33 rows=585933 width=0)
(5 rows)

Time: 0.331 ms
postgres=# select countall(*) from vc;
 countall 
----------
 46793600
(1 row)

Time: 351.865 ms

best regards, thank you.

Hello,
Through recent practice, I found out that query performance has greatly improved, but populate function consumes a lot of time.
The populate function will take more than 200 seconds when normal table contains 2000,000 rows.
So, what should I do to make it better?

postgres=# create table vops_lineitem(
postgres(# l_shipdate vops_date not null,
postgres(# l_quantity vops_float4 not null,
postgres(# l_extendedprice vops_float4 not null,
postgres(# l_discount vops_float4 not null,
postgres(# l_tax vops_float4 not null,
postgres(# l_returnflag vops_char not null,
postgres(# l_linestatus vops_char not null
postgres(# );
CREATE TABLE
postgres=# select populate(destination := 'vops_lineitem'::regclass, source := 'lineitem'::regclass);
2021-09-01 15:04:54.658 CST [26612] ERROR: Incompatible type of attribute 6: "char" vs. character
2021-09-01 15:04:54.658 CST [26612] STATEMENT: select populate(destination := 'vops_lineitem'::regclass, source := 'lineitem'::regclass);
ERROR: Incompatible type of attribute 6: "char" vs. character

https://github.com/digoal/blog/blob/master/201702/20170225_01.md

Hello,

Would it be possible to use it via JDBC driver (to both populate entities via COPY interface and fetch via standard JDBC)?
We would need support of these two functionalities in order to use it (as an extension, not FDW).

Cheers
Krzysztof@ESA Gaia