On Wed, Mar 31, 2021 at 6:19 PM Jaime Casanova
<jcasa...@systemguards.com.ec> wrote:
>
> On Wed, Mar 31, 2021 at 5:25 PM Tomas Vondra
> <tomas.von...@enterprisedb.com> wrote:
> >
> > Hi,
> >
> > I think I found the issue - it's kinda obvious, really. We need to
> > consider the timezone, because the "time" parts alone may be sorted
> > differently. The attached patch should fix this, and it also fixes a
> > similar issue in the inet data type.
> >
>
> ah! yeah! obvious... if you say so ;)
>
> > As for why the regression tests did not catch this, it's most likely
> > because the data is likely generated in "nice" ordering, or something
> > like that. I'll see if I can tweak the ordering to trigger these issues
> > reliably, and I'll do a bit more randomized testing.
> >
> > There's also the question of rounding errors, which I think might cause
> > random assert failures (but in practice it's harmless, in the worst case
> > we'll merge the ranges a bit differently).
> >
> >
>
> I can confirm this fixes the crash in the query I showed and the original
> case.
>
But I found another, but similar issue.
```
update public.brintest_multi set
intervalcol = (select pg_catalog.avg(intervalcol) from public.brintest_bloom)
;
```
BTW, i can reproduce just by executing "make installcheck" and
immediately execute that query
--
Jaime Casanova
Director de Servicios Profesionales
SYSTEMGUARDS - Consultores de PostgreSQL
#0 __GI_raise (sig=sig@entry=6) at ../sysdeps/unix/sysv/linux/raise.c:50
set = {__val = {4194304, 140732635565408, 2, 6, 6574673,
94040556822512, 4611686018427388799, 140208685218470, 0, 281470681751456, 0, 0,
0, 0, 0, 0}}
pid = <optimized out>
tid = <optimized out>
ret = <optimized out>
#1 0x00007f84e0a82535 in __GI_abort () at abort.c:79
save_stage = 1
act = {__sigaction_handler = {sa_handler = 0x0, sa_sigaction = 0x0},
sa_mask = {__val = {0, 0, 0, 0, 0, 140208682975221, 2, 3978988772531372052,
7003432107538527544, 94040556822512,
7003767243900412736, 0, 11642884921704132352, 140732635565648, 0,
140732635566512}}, sa_flags = -2047090704, sa_restorer = 0x0}
sigs = {__val = {32, 0 <repeats 15 times>}}
#2 0x0000558786650fb6 in ExceptionalCondition (conditionName=0x5587866cb6c3
"delta >= 0", errorType=0x5587866caf74 "FailedAssertion",
fileName=0x5587866caf60 "brin_minmax_multi.c", lineNumber=2166)
at assert.c:69
No locals.
#3 0x0000558785fc7cd4 in brin_minmax_multi_distance_interval
(fcinfo=0x7ffedec05220) at brin_minmax_multi.c:2166
delta = -82532.559085999994
ia = 0x558787f13738
ib = 0x558787efdc20
result = 0x558787f207e0
__func__ = "brin_minmax_multi_distance_interval"
#4 0x000055878665b3ae in FunctionCall2Coll (flinfo=0x558787efbbf0,
collation=0, arg1=94040589678392, arg2=94040589589536) at fmgr.c:1163
fcinfodata = {fcinfo = {flinfo = 0x558787efbbf0, context = 0x0,
resultinfo = 0x0, fncollation = 0, isnull = false, nargs = 2, args =
0x7ffedec05240},
fcinfo_data = "\360\273U", '\000' <repeats 24 times>,
"\002\000\070\067\361\207\207U\000\000\000\000\000\000\000\000\000\000
\334U\000\000\000\a\362\207\207U\000"}
fcinfo = 0x7ffedec05220
result = 94040589731280
__func__ = "FunctionCall2Coll"
#5 0x0000558785fc65b7 in build_distances (distanceFn=0x558787efbbf0,
colloid=0, eranges=0x558787f206f0, neranges=5) at brin_minmax_multi.c:1350
a1 = 94040589678392
a2 = 94040589589536
r = 94040556857001
i = 0
ndistances = 4
distances = 0x558787f20788
#6 0x0000558785fc712f in compactify_ranges (bdesc=0x558787efb428,
ranges=0x558787f12720, max_values=32) at brin_minmax_multi.c:1820
cmpFn = 0x558787efbc28
distanceFn = 0x558787efbbf0
eranges = 0x558787f206f0
neranges = 5
distances = 0x100558700000008
ctx = 0x558787f205d0
oldctx = 0x558787efd560
#7 0x0000558785fc8483 in brin_minmax_multi_serialize (bdesc=0x558787efb428,
src=94040589674272, dst=0x558787efda10) at brin_minmax_multi.c:2354
ranges = 0x558787f12720
s = 0x558787efe7a8
#8 0x0000558785fce67b in brin_form_tuple (brdesc=0x558787efb428, blkno=3,
tuple=0x558787efd680, size=0x7ffedec054b8) at brin_tuple.c:165
datumno = 1
values = 0x558787efe0f8
nulls = 0x558787efdc48
anynulls = false
rettuple = 0x7d7924400
keyno = 14
idxattno = 14
phony_infomask = 0
phony_nullbitmap = 0x558787efe210 "\177\177\177~\177\177\177\177"
len = 94040556891582
hoff = 1024
data_len = 94040589589704
i = 32766
untoasted_values = 0x558787efe230
nuntoasted = 0
#9 0x0000558785fbe9c1 in brininsert (idxRel=0x7f84d79244b0,
values=0x7ffedec05610, nulls=0x7ffedec055f0, heaptid=0x558787eeaf80,
heapRel=0x7f84d79110e0, checkUnique=UNIQUE_CHECK_NO, indexUnchanged=false,
indexInfo=0x558787eeaa38) at brin.c:281
lp = 0x7f84db92dfa4
origsz = 992
newsz = 94040559734857
page = 0x7f84db92df80 "\002"
origtup = 0x558787efdce0
newtup = 0x7ffedec054f0
samepage = false
need_insert = true
off = 4
brtup = 0x7f84db92f3e8
dtup = 0x558787efd680
pagesPerRange = 1
origHeapBlk = 3
heapBlk = 3
bdesc = 0x558787efb428
revmap = 0x558787eecbc8
buf = 7345
tupcxt = 0x558787efd560
oldcxt = 0x558787ede540
autosummarize = false
__func__ = "brininsert"
#10 0x000055878605ea98 in index_insert (indexRelation=0x7f84d79244b0,
values=0x7ffedec05610, isnull=0x7ffedec055f0, heap_t_ctid=0x558787eeaf80,
heapRelation=0x7f84d79110e0, checkUnique=UNIQUE_CHECK_NO,
indexUnchanged=false, indexInfo=0x558787eeaa38) at indexam.c:193
__func__ = "index_insert"
#11 0x0000558786271ffa in ExecInsertIndexTuples (resultRelInfo=0x558787ee9c88,
slot=0x558787eeaf50, estate=0x558787ede660, update=true, noDupErr=false,
specConflict=0x0, arbiterIndexes=0x0)
at execIndexing.c:416
applyNoDupErr = false
checkUnique = UNIQUE_CHECK_NO
indexRelation = 0x7f84d79244b0
indexInfo = 0x558787eeaa38
indexUnchanged = false
satisfiesConstraint = false
tupleid = 0x558787eeaf80
result = 0x0
i = 0
numIndices = 1
relationDescs = 0x558787eea9f8
heapRelation = 0x7f84d79110e0
indexInfoArray = 0x558787eeaa18
econtext = 0x558787eecb08
values = {911611088388, 222, 1222, 5222, 94040589519196, 1040552114,
4576009886973730184, 94040589519216, 94040589519222, 94040589519229, 3622,
8937000000, 18445615241314551616, 18445885747309551616,
94040589519264, 94040589519280, 94040589519292, 94040589519315,
292062319138, 0, 140732635567888, 94040557378514, 140732635568128,
140732635568124, 0, 94040589032792, 1, 94040589512528,
140732635568338, 140208529019104, 15642560, 94040589519128}
isnull = {false <repeats 24 times>, 68, 201, 238, 135, 135, 85, false,
false}
#12 0x00005587862b8540 in ExecUpdate (mtstate=0x558787ee9a70,
resultRelInfo=0x558787ee9c88, tupleid=0x7ffedec058d2, oldtuple=0x0,
slot=0x558787eeaf50, planSlot=0x558787eea1a0, epqstate=0x558787ee9b50,
estate=0x558787ede660, canSetTag=true) at nodeModifyTable.c:1733
lockmode = LockTupleNoKeyExclusive
partition_constraint_failed = false
update_indexes = true
resultRelationDesc = 0x7f84d79110e0
result = TM_Ok
tmfd = {ctid = {ip_blkid = {bi_hi = 22592, bi_lo = 57024}, ip_posid =
32766}, xmax = 2280567448, cmax = 21895, traversed = 160}
recheckIndexes = 0x0
__func__ = "ExecUpdate"
#13 0x00005587862b97f5 in ExecModifyTable (pstate=0x558787ee9a70) at
nodeModifyTable.c:2415
node = 0x558787ee9a70
estate = 0x558787ede660
operation = CMD_UPDATE
resultRelInfo = 0x558787ee9c88
subplanstate = 0x558787eea088
slot = 0x558787eeaf50
planSlot = 0x558787eea1a0
oldSlot = 0x558787eeaca0
tupleid = 0x7ffedec058d2
tuple_ctid = {ip_blkid = {bi_hi = 0, bi_lo = 3}, ip_posid = 5}
oldtupdata = {t_len = 2280514880, t_self = {ip_blkid = {bi_hi = 21895,
bi_lo = 0}, ip_posid = 1}, t_tableOid = 228, t_data = 0x558787ee9f18}
oldtuple = 0x0
proute = 0x0
relinfos = 0x0
lc = 0x558787ec2268
__func__ = "ExecModifyTable"
#14 0x000055878627f601 in ExecProcNodeFirst (node=0x558787ee9a70) at
execProcnode.c:456
No locals.
#15 0x00005587862739b9 in ExecProcNode (node=0x558787ee9a70) at
../../../src/include/executor/executor.h:257
No locals.
#16 0x0000558786276343 in ExecutePlan (estate=0x558787ede660,
planstate=0x558787ee9a70, use_parallel_mode=false, operation=CMD_UPDATE,
sendTuples=false, numberTuples=0, direction=ForwardScanDirection,
dest=0x558787ed2d30, execute_once=true) at execMain.c:1531
slot = 0x558787ede660
current_tuple_count = 0
#17 0x000055878627401a in standard_ExecutorRun (queryDesc=0x558787e0d860,
direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:350
estate = 0x558787ede660
operation = CMD_UPDATE
dest = 0x558787ed2d30
sendTuples = false
oldcontext = 0x558787e0d740
__func__ = "standard_ExecutorRun"
#18 0x0000558786273e30 in ExecutorRun (queryDesc=0x558787e0d860,
direction=ForwardScanDirection, count=0, execute_once=true) at execMain.c:294
No locals.
#19 0x00005587864c0e4d in ProcessQuery (plan=0x558787ed2090,
sourceText=0x558787de58d0 "update public.brintest_multi set \n intervalcol =
(select pg_catalog.avg(intervalcol) from public.brintest_bloom)\n;",
params=0x0, queryEnv=0x0, dest=0x558787ed2d30, qc=0x7ffedec05d10) at
pquery.c:160
queryDesc = 0x558787e0d860
#20 0x00005587864c280a in PortalRunMulti (portal=0x558787e5cc90,
isTopLevel=true, setHoldSnapshot=false, dest=0x558787ed2d30,
altdest=0x558787ed2d30, qc=0x7ffedec05d10) at pquery.c:1267
pstmt = 0x558787ed2090
stmtlist_item__state = {l = 0x558787ed2cd8, i = 0}
active_snapshot_set = true
stmtlist_item = 0x558787ed2cf0
#21 0x00005587864c1d8c in PortalRun (portal=0x558787e5cc90,
count=9223372036854775807, isTopLevel=true, run_once=true, dest=0x558787ed2d30,
altdest=0x558787ed2d30, qc=0x7ffedec05d10) at pquery.c:779
_save_exception_stack = 0x7ffedec05e20
_save_context_stack = 0x0
_local_sigjmp_buf = {{__jmpbuf = {0, -5544282200821052108,
94040556822512, 140732635571008, 0, 0, -5544282200726680268,
-1730383164341334732}, __mask_was_saved = 0, __saved_mask = {__val = {
19434708662, 94040589413656, 128, 94040588441520,
94040588938400, 140732635569264, 47128, 94040563780066, 94040589395248,
140732635569296, 94040563959165, 13, 112, 94040589413792, 112,
3737148576}}}}
_do_rethrow = false
result = false
nprocessed = 94040589413680
saveTopTransactionResourceOwner = 0x558787e23f90
saveTopTransactionContext = 0x558787e75c00
saveActivePortal = 0x0
saveResourceOwner = 0x558787e23f90
savePortalContext = 0x0
saveMemoryContext = 0x558787e75c00
__func__ = "PortalRun"
#22 0x00005587864bb6de in exec_simple_query (query_string=0x558787de58d0
"update public.brintest_multi set \n intervalcol = (select
pg_catalog.avg(intervalcol) from public.brintest_bloom)\n;")
at postgres.c:1185
snapshot_set = true
per_parsetree_context = 0x0
plantree_list = 0x558787ed2cd8
parsetree = 0x558787de6bb8
commandTag = CMDTAG_UPDATE
qc = {commandTag = CMDTAG_UNKNOWN, nprocessed = 0}
querytree_list = 0x558787eb07f0
portal = 0x558787e5cc90
receiver = 0x558787ed2d30
format = 0
parsetree_item__state = {l = 0x558787de6bf0, i = 0}
dest = DestRemote
oldcontext = 0x558787e75c00
parsetree_list = 0x558787de6bf0
parsetree_item = 0x558787de6c08
save_log_statement_stats = false
was_logged = false
use_implicit_block = false
msec_str =
"\320Xއ\207U\000\000t\000\000\000\006\000\000\000`]\300\336\376\177\000\000i\331.\206t\000\000"
__func__ = "exec_simple_query"
#23 0x00005587864bfe07 in PostgresMain (argc=1, argv=0x7ffedec05fa0,
dbname=0x558787e20b68 "regression", username=0x558787e1a720 "jcasanov") at
postgres.c:4415
query_string = 0x558787de58d0 "update public.brintest_multi set \n
intervalcol = (select pg_catalog.avg(intervalcol) from
public.brintest_bloom)\n;"
firstchar = 81
input_message = {data = 0x558787de58d0 "update public.brintest_multi
set \n intervalcol = (select pg_catalog.avg(intervalcol) from
public.brintest_bloom)\n;", len = 117, maxlen = 1024, cursor = 117}
local_sigjmp_buf = {{__jmpbuf = {0, -5544282200860897996,
94040556822512, 140732635571008, 0, 0, -5544282200781206220,
-1730383216888099532}, __mask_was_saved = 1, __saved_mask = {__val = {4194304,
0,
0, 0, 0, 0, 0, 0, 0, 0, 1099511627520, 0, 0, 4294901760,
18446462598732840960, 0}}}}
send_ready_for_query = false
idle_in_transaction_timeout_enabled = false
idle_session_timeout_enabled = false
__func__ = "PostgresMain"
#24 0x00005587863f8050 in BackendRun (port=0x558787e0a730) at postmaster.c:4483
av = {0x5587867f82a7 "postgres", 0x0}
ac = 1
#25 0x00005587863f797f in BackendStartup (port=0x558787e0a730) at
postmaster.c:4205
bn = 0x558787e087c0
pid = 0
__func__ = "BackendStartup"
#26 0x00005587863f3d16 in ServerLoop () at postmaster.c:1737
port = 0x558787e0a730
i = 2
rmask = {fds_bits = {128, 0 <repeats 15 times>}}
selres = 1
now = 1617253360
readmask = {fds_bits = {224, 0 <repeats 15 times>}}
nSockets = 8
last_lockfile_recheck_time = 1617253322
last_touch_time = 1617253169
__func__ = "ServerLoop"
#27 0x00005587863f3565 in PostmasterMain (argc=3, argv=0x558787ddeee0) at
postmaster.c:1409
opt = -1
status = 0
userDoption = 0x558787e027b0 "data"
listen_addr_saved = true
i = 64
output_config_variable = 0x0
__func__ = "PostmasterMain"
#28 0x00005587862f0ac0 in main (argc=3, argv=0x558787ddeee0) at main.c:209
do_check_root = true
