postgrespro / vops Goto Github PK
View Code? Open in Web Editor NEWLicense: Other
License: Other
PostgreSQL Database Management System ===================================== This directory contains the source code distribution of the PostgreSQL database management system. PostgreSQL is an advanced object-relational database management system that supports an extended subset of the SQL standard, including transactions, foreign keys, subqueries, triggers, user-defined types and functions. This distribution also contains C language bindings. PostgreSQL has many language interfaces, many of which are listed here: http://www.postgresql.org/download See the file INSTALL for instructions on how to build and install PostgreSQL. That file also lists supported operating systems and hardware platforms and contains information regarding any other software packages that are required to build or run the PostgreSQL system. Copyright and license information can be found in the file COPYRIGHT. A comprehensive documentation set is included in this distribution; it can be read as described in the installation instructions. The latest version of this software may be obtained at http://www.postgresql.org/download/. For more information look at our web site located at http://www.postgresql.org/.
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
Установленные расширения:
extname | extowner | extnamespace | extrelocatable | extversion | extconfig | extcondition |
---|---|---|---|---|---|---|
plpgsql | 10 | 11 | false | 1.0 | NULL | NULL |
file_fdw | 10 | 2200 | true | 1.0 | NULL | NULL |
pg_buffercache | 10 | 2200 | true | 1.3 | NULL | NULL |
pg_stat_statements | 10 | 2200 | true | 1.5 | NULL | NULL |
pg_variables | 10 | 2200 | true | 1.1 | NULL | NULL |
tablefunc | 10 | 2200 | true | 1.0 | NULL | NULL |
uuid-ossp | 10 | 2200 | true | 1.1 | NULL | NULL |
pg_pathman | 16384 | 2200 | false | 1.5 | {119515229,119515240} | {,} |
tds_fdw | 16384 | 2200 | true | 2.0.0-alpha.2 | NULL | NULL |
plv8 | 16384 | 11 | false | 2.3.8 | NULL | NULL |
pg_query_state | 16384 | 2200 | true | 1.1 | NULL | NULL |
bloom | 16384 | 2200 | true | 1.0 | NULL | NULL |
pageinspect | 16384 | 2200 | true | 1.7 | NULL | NULL |
btree_gin | 16384 | 2200 | true | 1.3 | NULL | NULL |
pg_trgm | 16384 | 2200 | true | 1.4 | NULL | NULL |
vops | 16384 | 2200 | true | 1.0 | NULL | NULL |
[pgrel@centos01 ~]$ psql -h /pgreltmp/ postgres
psql (9.6.2)
Type "help" for help.
(1 row)
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
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
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.