In trigger bigint value of field is constant and wrong

[GoogleCodeExporter]

What steps will reproduce the problem?

1. Create table

CREATE TABLE table1 (
  id BIGSERIAL,
  name varchar(50)
  CONSTRAINT objects_pkey PRIMARY KEY(id)
)

CREATE SEQUENCE table1_id_seq
  INCREMENT 1 MINVALUE -9223372036854775808
  MAXVALUE 9223372036854775807 START -9223372036854775808
  CACHE 1;

ALTER SEQUENCE bo.objects_id_seq RESTART WITH -9223372036854775807;

2. create view and trigger for DELETE, INSERT OR UPDATE

CREATE OR REPLACE VIEW table1_view
AS
  SELECT table1.id,
         table1.name
  FROM table1

CREATE OR REPLACE FUNCTION table1_delete_trigger (
)
RETURNS trigger AS
$body$
  plv8.elog(NOTICE, JSON.stringify(OLD));
  return null;
$body$
LANGUAGE 'plv8'
VOLATILE
CALLED ON NULL INPUT
SECURITY INVOKER
COST 100;

3. Insert some rows into table1 and then try delete from table1

delete from table1 where id = -9223372036854775807

Y'll see in console 
NOTICE:  {"id":-9223372036854776000,"name":"some name"}

-9223372036854776000 - appears always whatever row y'll delete

What is the expected output? What do you see instead?
expected to have correct value of bigint in OLD and NEW structures

What version of the product are you using? On what operating system?
PostgreSQL 9.3beta2, compiled by Visual C++ build 1600, 64-bit

Please provide any additional information below.

Original issue reported on code.google.com by vovapjat...@gmail.com on 29 Jun 2013 at 11:22

[GoogleCodeExporter]

there is a bug with bigint

var id = plv8.exec('select nextval(pg_get_serial_sequence(\'table1\', 
\'id\')::regclass)'[0].nextval;

id is always -9223372036854776000

but in psql it's correct value

Original comment by vovapjat...@gmail.com on 3 Jul 2013 at 2:18

[GoogleCodeExporter]

also try:

var id = plv8.execute('insert into table1 (name) values (\'lalala\') RETURNING 
*')[0].id;
plv8.elog(NOTICE, id);

it will always be -9223372036854776000!

Original comment by vovapjat...@gmail.com on 3 Jul 2013 at 2:25

[GoogleCodeExporter]

Yeah, it seems the bigint in postgres and v8's Number don't like each other.

test=# create or replace function bi(n bigint) returns void as $$ 
plv8.elog(NOTICE, n); $$ language plv8;
CREATE FUNCTION
test=# select bi(-9223372036854775807);
NOTICE:  -9223372036854776000
 bi
----

(1 row)

Original comment by umi.tan...@gmail.com on 10 Jul 2013 at 5:42

[GoogleCodeExporter]

The Number of EcmaScript is defined as double-precision 64bit floating point, 
and even in postgres this conversion would loose precision.

test=# select -9223372036854775807::float8::numeric;
       ?column?
----------------------
 -9223372036854780000
(1 row)

Although from the database perspective it is not good, in JavaScript it is 
usually happening... I wonder what to do.

Original comment by umi.tan...@gmail.com on 10 Jul 2013 at 5:48

[GoogleCodeExporter]

so, what for now, the maximum valid value for integer in V8<->PG ?

Original comment by vovapjat...@gmail.com on 11 Jul 2013 at 2:54

[GoogleCodeExporter]

do language plv8 $$
  for (var i = 0; i < 64; i++) {
    rec = plv8.execute("SELECT n, n::text AS t FROM (VALUES((1::bigint <<" + i + ") + 1)) AS v(n)").shift();
    plv8.elog(INFO, i, rec.n + "" == rec.t, rec.n, rec.t)
  }
$$;

INFO:  0 true 2 2
INFO:  1 true 3 3
INFO:  2 true 5 5
INFO:  3 true 9 9
INFO:  4 true 17 17
INFO:  5 true 33 33
INFO:  6 true 65 65
INFO:  7 true 129 129
INFO:  8 true 257 257
INFO:  9 true 513 513
INFO:  10 true 1025 1025
INFO:  11 true 2049 2049
INFO:  12 true 4097 4097
INFO:  13 true 8193 8193
INFO:  14 true 16385 16385
INFO:  15 true 32769 32769
INFO:  16 true 65537 65537
INFO:  17 true 131073 131073
INFO:  18 true 262145 262145
INFO:  19 true 524289 524289
INFO:  20 true 1048577 1048577
INFO:  21 true 2097153 2097153
INFO:  22 true 4194305 4194305
INFO:  23 true 8388609 8388609
INFO:  24 true 16777217 16777217
INFO:  25 true 33554433 33554433
INFO:  26 true 67108865 67108865
INFO:  27 true 134217729 134217729
INFO:  28 true 268435457 268435457
INFO:  29 true 536870913 536870913
INFO:  30 true 1073741825 1073741825
INFO:  31 true 2147483649 2147483649
INFO:  32 true 4294967297 4294967297
INFO:  33 true 8589934593 8589934593
INFO:  34 true 17179869185 17179869185
INFO:  35 true 34359738369 34359738369
INFO:  36 true 68719476737 68719476737
INFO:  37 true 137438953473 137438953473
INFO:  38 true 274877906945 274877906945
INFO:  39 true 549755813889 549755813889
INFO:  40 true 1099511627777 1099511627777
INFO:  41 true 2199023255553 2199023255553
INFO:  42 true 4398046511105 4398046511105
INFO:  43 true 8796093022209 8796093022209
INFO:  44 true 17592186044417 17592186044417
INFO:  45 true 35184372088833 35184372088833
INFO:  46 true 70368744177665 70368744177665
INFO:  47 true 140737488355329 140737488355329
INFO:  48 true 281474976710657 281474976710657
INFO:  49 true 562949953421313 562949953421313
INFO:  50 true 1125899906842625 1125899906842625
INFO:  51 true 2251799813685249 2251799813685249
INFO:  52 true 4503599627370497 4503599627370497
INFO:  53 false 9007199254740992 9007199254740993
INFO:  54 false 18014398509481984 18014398509481985
INFO:  55 false 36028797018963970 36028797018963969
INFO:  56 false 72057594037927940 72057594037927937
INFO:  57 false 144115188075855870 144115188075855873
INFO:  58 false 288230376151711740 288230376151711745
INFO:  59 false 576460752303423500 576460752303423489
INFO:  60 false 1152921504606847000 1152921504606846977
INFO:  61 false 2305843009213694000 2305843009213693953
INFO:  62 false 4611686018427388000 4611686018427387905
INFO:  63 false -9223372036854776000 -9223372036854775807
DO

Original comment by umi.tan...@gmail.com on 12 Jul 2013 at 4:52

[GoogleCodeExporter]

12.07.2013 7:53, plv8js@googlecode.com пишет:

Thanks alot!

Original comment by vovapjat...@gmail.com on 12 Jul 2013 at 8:45