Error when inserting data into the hash table

[Александр Попов]

Hello. I found an error, when I inserting data in hash table. Maybe it is known Issues.

1) create table 

  drop table if exists test_data_hash_id ;
  create table test_data_hash_id  (
    id           bigint not null primary key,
    type         integer not null,
    date_created timestamp with time zone not null,
    summ         numeric not null
   );
  SELECT master_create_distributed_table('test_data_hash_id ', 'id', 'hash');
  SELECT master_create_worker_shards('test_data_hash_id ',2, 4);

the table has no data.

2) insert data into table in one transaction 

do $$
declare
    i bigint;
    t integer;
    d timestamp with time zone;
    s numeric;
begin
    for i in 1 ..100
    loop
        t = round(random()*100);
        d = (to_date('01.01.2015', 'DD.MM.YYYY') +  random() * (timestamp '2005-01-01 00:00:00' - timestamp '2015-01-01 00:00:00'));
        s = round((random()::numeric * 1000000),2)::numeric;
        insert into test_data_hash_id (id, type,date_created,summ) values(i,t,d,s);
    end loop;
end $$;

I have an error

------------------------

ERROR:  cannot plan sharded modification containing values which are not constants or constant expressions
CONTEXT:  SQL statement "insert into test_data_hash_id (id, type,date_created,summ) values(i,t,d,s)"
PL/pgSQL function inline_code_block line 13 at SQL statement


********** Error **********


ERROR: cannot plan sharded modification containing values which are not constants or constant expressions
SQL state: 0A000
Context: SQL statement "insert into test_data_hash_id (id, type,date_created,summ) values(i,t,d,s)"
PL/pgSQL function inline_code_block line 13 at SQL statement

-------------------------

the query select * from test_data_hash_id; gives 5 lines.

tpch30=# select * from test_data_hash_id;
 id | type |         date_created          |   summ    
----+------+-------------------------------+-----------
  1 |   73 | 2005-09-02 03:38:46.080161+04 | 450113.62
  3 |   62 | 2014-03-20 23:31:50.621595+04 | 579127.11
  4 |   58 | 2005-08-17 03:45:21.270293+04 | 498021.81
  5 |   75 | 2007-11-26 21:51:10.961416+03 | 646999.54
  2 |   22 | 2005-08-05 13:00:39.68913+04  | 144351.94

Maybe, it is better todisable inserting data more, then one line. 

 PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.1) 4.8

.4, 64-bit

(1 row)

[Brian Cloutier]

This is probably caused by #306, Citus doesn't yet support parameterized queries.

I think the workaround is to run something like

execute format('insert into test_data_hash_id (id, type,date_created,summ) values(%s,%s,%s,%s);', i, t, d, s);

in place of the insert statement

--
You received this message because you are subscribed to the Google Groups "citus-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to citus-users...@googlegroups.com.
To post to this group, send email to citus...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/citus-users/1ca50772-e3eb-449d-8476-4a66c3a2d073%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Eugen Konkov]

Maybe you will be interested also at: https://github.com/citusdata/citus/issues/500#issuecomment-219034806 as workaround (for perl) you may look something similar

пятница, 13 мая 2016 г., 14:27:40 UTC+3 пользователь Александр Попов написал:

[Jason Petersen]

Eugen, your column is declared as timestamp with time zone, but the values you're inserting don't have a time zone. What happens if you change the code to explicitly include a time zone in the inserted values?