Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} - Mailing list pgsql-hackers
| From | Jeff Janes |
|---|---|
| Subject | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} |
| Date | |
| Msg-id | CAMkU=1zYuNU1ciBG6rRabV5r7k9iur-n7-Fdyi9hU3C98HvPGw@mail.gmail.com Whole thread Raw |
| In response to | Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} (Peter Geoghegan <pg@heroku.com>) |
| Responses |
Re: INSERT ... ON CONFLICT {UPDATE | IGNORE}
Re: INSERT ... ON CONFLICT {UPDATE | IGNORE} |
| List | pgsql-hackers |
On Mon, Dec 15, 2014 at 11:06 PM, Peter Geoghegan <pg@heroku.com> wrote:
Attached pair of revised patch sets fix the OID collision, andOn Mon, Dec 15, 2014 at 4:59 PM, Peter Geoghegan <pg@heroku.com> wrote:
> On Mon, Dec 15, 2014 at 4:22 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
>> Also, in both Linux and MinGW under option 1 patch I get an OID conflict on
>> OID 3261.
>
> I'll take a pass at fixing this bitrot soon. I'll follow Tom's advice
> about macro collisions on MinGW while I'm at it, since his explanation
> seems plausible.
presumably fix the MinGW issue (because IGNORE_P is now used as a
token name). It also polishes approach #2 to value locking in a few
places (e.g. better comments). Finally, both patches have a minor
buglet around EXPLAIN ANALYZE output fixed -- the output now indicates
if tuples are pulled up from auxiliary update nodes.
I naively tried this in vallock1 patch:
create table foo(index int, count int);
create unique index on foo(index);
insert into foo (index, count) values (0,1) on conflict (index) update set count=foo.count + 1 returning foo.count;
insert into foo (index, count) values (0,1) on conflict (index) update set count=foo.count + 1 returning foo.count;
insert into foo (index, count) values (0,1) on conflict (index) update set count=foo.count + 1 returning foo.count;
insert into foo (index, count) values (0,1) on conflict (index) update set count=foo.count + 1 returning foo.count;
After actually reading the documentation more closely, I decided this should be an error because "foo" is not a valid table alias in the "update set" expression. Instead of being a parsing/planning error, this executes and the foo.count on the RHS of the assignment always evaluates as zero (even on subsequent invocations when TARGET.count is 1).
If I switch to a text type, then I get seg faults under the same condition:
create table foo(index int, count text);
create unique index on foo(index);
insert into foo (index, count) values (0,'start ') on conflict (index) update set count=foo.count||' bar' returning count;
insert into foo (index, count) values (0,'start ') on conflict (index) update set count=foo.count||' bar' returning count;
<boom>
#0 pg_detoast_datum_packed (datum=0x0) at fmgr.c:2270
#1 0x000000000074fb7a in textcat (fcinfo=0x1e67a78) at varlena.c:662
#2 0x00000000005a63a5 in ExecMakeFunctionResultNoSets (fcache=0x1e67a08, econtext=0x1e67848, isNull=0x1e68b11 "", isDone=<value optimized out>)
at execQual.c:2026
#3 0x00000000005a2353 in ExecTargetList (projInfo=<value optimized out>, isDone=0x7fffa7fa346c) at execQual.c:5358
#4 ExecProject (projInfo=<value optimized out>, isDone=0x7fffa7fa346c) at execQual.c:5573
#5 0x00000000005a86c2 in ExecScan (node=0x1e67738, accessMtd=0x5baf00 <SeqNext>, recheckMtd=0x5bad60 <SeqRecheck>) at execScan.c:207
#6 0x00000000005a1918 in ExecProcNode (node=0x1e67738) at execProcnode.c:406
#7 0x000000000059ef32 in EvalPlanQualNext (epqstate=<value optimized out>) at execMain.c:2380
#8 0x00000000005b8fcd in ExecLockUpdateTuple (node=0x1e5f750) at nodeModifyTable.c:1098
#9 ExecInsert (node=0x1e5f750) at nodeModifyTable.c:372
#10 ExecModifyTable (node=0x1e5f750) at nodeModifyTable.c:1396
#11 0x00000000005a1958 in ExecProcNode (node=0x1e5f750) at execProcnode.c:383
#12 0x00000000005a0642 in ExecutePlan (queryDesc=0x1dd0908, direction=<value optimized out>, count=0) at execMain.c:1515
#13 standard_ExecutorRun (queryDesc=0x1dd0908, direction=<value optimized out>, count=0) at execMain.c:308
#14 0x00007f601416b9cb in pgss_ExecutorRun (queryDesc=0x1dd0908, direction=ForwardScanDirection, count=0) at pg_stat_statements.c:874
#15 0x000000000069385f in ProcessQuery (plan=0x1e47df0,
....
So I think there needs to be some kind of logic to de-recognize the table alias "foo".
Once I rewrote the query to use TARGET and EXCLUDED correctly, I've put this through an adaptation of my usual torture test, and it ran fine until wraparound shutdown. I'll poke at it more later.
Cheers,
Jeff
pgsql-hackers by date: