Hello
I created relative large (about 200K lines) simple table (one jsonb column):
It works, but I got a errors:
TRAP: FailedAssertion("!(va.type == jbvArray || va.type == jbvObject)",
File: "jsonb_util.c", Line: 208)
LOG: server process (PID 3851) was terminated by signal 6: Aborted
DETAIL: Failed process was running: autovacuum: ANALYZE public.t4
LOG: terminating any other active server processes
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because ano
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
WARNING: terminating connection because of crash of another server process
DETAIL: The postmaster has commanded this server process to roll back the
current transaction and exit, because ano
HINT: In a moment you should be able to reconnect to the database and
repeat your command.
LOG: all server processes terminated; reinitializing
LOG: database system was interrupted; last known up at 2014-05-20 20:07:32
CEST
LOG: database system was not properly shut down; automatic recovery in
progress
Data are based on opened citylots.json (avaiable on net by google)
postgres=# \lo_import ~/citylots.json
lo_import 16510
CREATE OR REPLACE FUNCTION public.bytea_to_text(bytea)
RETURNS text
LANGUAGE sql
AS $function$
SELECT convert_from($1, current_setting('server_encoding'))
$function$
postgres=# insert into xx select bytea_to_text(lo_get(16510));
INSERT 0 1
create table t1 (data json);
insert into t1 select a::json from xx;
postgres=# create table t3(data json);
CREATE TABLE
postgres=# insert into t3 select
json_array_elements(json_object_field(data, 'features')) from t1;
INSERT 0 206560
postgres=# insert into t4 select data::jsonb from t3;
INSERT 0 206560
postgres=# select * from t4 limit 1;
data
-----------------------------------------------------------------------------------------------------------------------
{"type": "Feature", "geometry": {"type": "Polygon", "coordinates":
[[[-122.422003528252475, 37.808480096967251, 0.0],.
. [-122.422076013325281, 37.808835019815085, 0.0], [-122.421102174348633,
37.808803534992904, 0.0], [-122.421062569067.
.274, 37.808601056818148, 0.0], [-122.422003528252475, 37.808480096967251,
0.0]]]}, "properties": {"TO_ST": "0", "BLKL.
.OT": "0001001", "STREET": "UNKNOWN", "FROM_ST": "0", "LOT_NUM": "001",
"ST_TYPE": null, "ODD_EVEN": "E", "BLOCK_NUM":.
. "0001", "MAPBLKLOT": "0001001"}}
(1 row)
postgres=# analyze t4;
The connection to the server was lost. Attempting reset: Failed
postgres=# select version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 9.4beta1 on x86_64-unknown-linux-gnu, compiled by gcc (GCC)
4.8.2 20131212 (Red Hat 4.8.2-7), 64-bit
(1 row)
Program received signal SIGABRT, Aborted.
0x000000382fc35c39 in raise () from /lib64/libc.so.6
(gdb) bt
#0 0x000000382fc35c39 in raise () from /lib64/libc.so.6
#1 0x000000382fc37348 in abort () from /lib64/libc.so.6
#2 0x000000000078d8d7 in ExceptionalCondition (
conditionName=conditionName@entry=0x91daa0 "!(va.type == jbvArray ||
va.type == jbvObject)",
errorType=errorType@entry=0x7c62bc "FailedAssertion",
fileName=fileName@entry=0x91d5db "jsonb_util.c",
lineNumber=lineNumber@entry=208) at assert.c:54
#3 0x0000000000708815 in compareJsonbContainers (a=a@entry=0x7fd8371b7cbc,
b=b@entry=0x11537b4)
at jsonb_util.c:208
#4 0x0000000000706cc3 in jsonb_cmp (fcinfo=0x11aad18) at jsonb_op.c:244
#5 0x00000000007b32f9 in comparison_shim (x=<optimized out>, y=<optimized
out>, ssup=<optimized out>)
at sortsupport.c:53
#6 0x0000000000555893 in ApplySortComparator (isNull1=0 '\000', isNull2=0
'\000', ssup=0x7fff7aff12d0,
datum2=<optimized out>, datum1=<optimized out>) at
../../../src/include/utils/sortsupport.h:143
#7 compare_scalars (a=<optimized out>, b=<optimized out>,
arg=0x7fff7aff12c0) at analyze.c:2784
#8 0x00000000007c4625 in qsort_arg (a=a@entry=0x7fd834d3e048, n=<optimized
out>, n@entry=27648, es=es@entry=16,
cmp=cmp@entry=0x555870 <compare_scalars>, arg=arg@entry=0x7fff7aff12c0)
at qsort_arg.c:156
#9 0x0000000000554c21 in compute_scalar_stats (stats=0x11488f8,
fetchfunc=<optimized out>, samplerows=30000,
totalrows=206560) at analyze.c:2366
#10 0x0000000000556e1e in do_analyze_rel (onerel=onerel@entry=0x7fd8375c8950,
acquirefunc=<optimized out>,
relpages=18392, inh=inh@entry=0 '\000', elevel=elevel@entry=13,
vacstmt=0x10e6bf8, vacstmt=0x10e6bf8)
at analyze.c:528
#11 0x0000000000557bc1 in analyze_rel (relid=relid@entry=22085,
vacstmt=vacstmt@entry=0x10e6bf8,
bstrategy=<optimized out>) at analyze.c:268
#12 0x00000000005a9bf4 in vacuum (vacstmt=vacstmt@entry=0x10e6bf8,
relid=relid@entry=0,
do_toast=do_toast@entry=1 '\001', bstrategy=<optimized out>,
bstrategy@entry=0x0,
for_wraparound=for_wraparound@entry=0 '\000',
isTopLevel=isTopLevel@entry=1 '\001') at vacuum.c:251
#13 0x00000000006b1b9a in standard_ProcessUtility (parsetree=0x10e6bf8,
queryString=<optimized out>,
context=<optimized out>, params=0x0, dest=<optimized out>,
completionTag=<optimized out>) at utility.c:645
#14 0x00000000006aec11 in PortalRunUtility (portal=portal@entry=0x11428a8,
utilityStmt=utilityStmt@entry=0x10e6bf8, isTopLevel=isTopLevel@entry=1
'\001', dest=dest@entry=0x10e6fa0,
completionTag=completionTag@entry=0x7fff7aff1b40 "") at pquery.c:1187
#15 0x00000000006af8b2 in PortalRunMulti (portal=portal@entry=0x11428a8,
isTopLevel=isTopLevel@entry=1 '\001',
dest=dest@entry=0x10e6fa0, altdest=altdest@entry=0x10e6fa0,
completionTag=completionTag@entry=0x7fff7aff1b40 "") at pquery.c:1318
#16 0x00000000006b0460 in PortalRun (portal=portal@entry=0x11428a8,
count=count@entry=9223372036854775807,
isTopLevel=isTopLevel@entry=1 '\001', dest=dest@entry=0x10e6fa0,
altdest=altdest@entry=0x10e6fa0,
completionTag=completionTag@entry=0x7fff7aff1b40 "") at pquery.c:816
#17 0x00000000006ae03b in exec_simple_query (query_string=0x10e6158
"analyze t4;") at postgres.c:1045
#18 PostgresMain (argc=<optimized out>, argv=argv@entry=0x1080df0,
dbname=0x1080c50 "postgres",
username=<optimized out>) at postgres.c:4004
#19 0x0000000000461307 in BackendRun (port=0x10a0060) at postmaster.c:4117
#20 BackendStartup (port=0x10a0060) at postmaster.c:3791
#21 ServerLoop () at postmaster.c:1570
#22 0x000000000065404e in PostmasterMain (argc=argc@entry=3,
argv=argv@entry=0x107f5c0)
at postmaster.c:1223
#23 0x0000000000461fce in main (argc=3, argv=0x107f5c0) at main.c:225
I can share this data if there is some request - it is about 50MB
Regards
Pavel
