Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7636e5c

Browse files
committed
Fast default trigger and expand_tuple fixes
Ensure that triggers get properly filled in tuples for the OLD value.Also fix the logic of detecting missing null values. The previous logicfailed to detect a missing null column before the first missing columnwith a default. Fixing this has simplified the logic a bit.Regression tests are added to test changes. This should ensure bettercoverage of expand_tuple().Original bug reports, and some code and test scripts from Tomas VondraBackpatch to release 11.
1 parent60e612b commit7636e5c

File tree

4 files changed

+328
-33
lines changed

4 files changed

+328
-33
lines changed

‎src/backend/access/common/heaptuple.c

Lines changed: 21 additions & 30 deletions
Original file line numberDiff line numberDiff line change
@@ -823,44 +823,35 @@ expand_tuple(HeapTuple *targetHeapTuple,
823823
{
824824
if (attrmiss[firstmissingnum].am_present)
825825
break;
826+
else
827+
hasNulls= true;
826828
}
827829

828830
/*
829-
* If there are no more missing values everything else must be NULL
831+
* Now walk the missing attributes. If there is a missing value
832+
* make space for it. Otherwise, it's going to be NULL.
830833
*/
831-
if (firstmissingnum >=natts)
832-
{
833-
hasNulls= true;
834-
}
835-
else
834+
for (attnum=firstmissingnum;
835+
attnum<natts;
836+
attnum++)
836837
{
837-
838-
/*
839-
* Now walk the missing attributes. If there is a missing value
840-
* make space for it. Otherwise, it's going to be NULL.
841-
*/
842-
for (attnum=firstmissingnum;
843-
attnum<natts;
844-
attnum++)
838+
if (attrmiss[attnum].am_present)
845839
{
846-
if (attrmiss[attnum].am_present)
847-
{
848-
Form_pg_attributeatt=TupleDescAttr(tupleDesc,attnum);
840+
Form_pg_attributeatt=TupleDescAttr(tupleDesc,attnum);
849841

850-
targetDataLen=att_align_datum(targetDataLen,
851-
att->attalign,
852-
att->attlen,
853-
attrmiss[attnum].am_value);
842+
targetDataLen=att_align_datum(targetDataLen,
843+
att->attalign,
844+
att->attlen,
845+
attrmiss[attnum].am_value);
854846

855-
targetDataLen=att_addlength_pointer(targetDataLen,
856-
att->attlen,
857-
attrmiss[attnum].am_value);
858-
}
859-
else
860-
{
861-
/* no missing value, so it must be null */
862-
hasNulls= true;
863-
}
847+
targetDataLen=att_addlength_pointer(targetDataLen,
848+
att->attlen,
849+
attrmiss[attnum].am_value);
850+
}
851+
else
852+
{
853+
/* no missing value, so it must be null */
854+
hasNulls= true;
864855
}
865856
}
866857
}/* end if have missing values */

‎src/backend/commands/trigger.c

Lines changed: 4 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3396,7 +3396,10 @@ ltrmark:;
33963396
LockBuffer(buffer,BUFFER_LOCK_UNLOCK);
33973397
}
33983398

3399-
result=heap_copytuple(&tuple);
3399+
if (HeapTupleHeaderGetNatts(tuple.t_data)<relation->rd_att->natts)
3400+
result=heap_expand_tuple(&tuple,relation->rd_att);
3401+
else
3402+
result=heap_copytuple(&tuple);
34003403
ReleaseBuffer(buffer);
34013404

34023405
returnresult;

‎src/test/regress/expected/fast_default.out

Lines changed: 190 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -539,8 +539,197 @@ FROM t1;
539539
1 | 0
540540
(20 rows)
541541

542-
DROP TABLE t1;
543542
DROP TABLE T;
543+
-- test that we account for missing columns without defaults correctly
544+
-- in expand_tuple, and that rows are correctly expanded for triggers
545+
CREATE FUNCTION test_trigger()
546+
RETURNS trigger
547+
LANGUAGE plpgsql
548+
AS $$
549+
550+
begin
551+
raise notice 'old tuple: %', to_json(OLD)::text;
552+
if TG_OP = 'DELETE'
553+
then
554+
return OLD;
555+
else
556+
return NEW;
557+
end if;
558+
end;
559+
560+
$$;
561+
-- 2 new columns, both have defaults
562+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
563+
INSERT INTO t (a,b,c) VALUES (1,2,3);
564+
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
565+
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
566+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
567+
SELECT * FROM t;
568+
id | a | b | c | x | y
569+
----+---+---+---+---+---
570+
1 | 1 | 2 | 3 | 4 | 5
571+
(1 row)
572+
573+
UPDATE t SET y = 2;
574+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":5}
575+
SELECT * FROM t;
576+
id | a | b | c | x | y
577+
----+---+---+---+---+---
578+
1 | 1 | 2 | 3 | 4 | 2
579+
(1 row)
580+
581+
DROP TABLE t;
582+
-- 2 new columns, first has default
583+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
584+
INSERT INTO t (a,b,c) VALUES (1,2,3);
585+
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
586+
ALTER TABLE t ADD COLUMN y int;
587+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
588+
SELECT * FROM t;
589+
id | a | b | c | x | y
590+
----+---+---+---+---+---
591+
1 | 1 | 2 | 3 | 4 |
592+
(1 row)
593+
594+
UPDATE t SET y = 2;
595+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":4,"y":null}
596+
SELECT * FROM t;
597+
id | a | b | c | x | y
598+
----+---+---+---+---+---
599+
1 | 1 | 2 | 3 | 4 | 2
600+
(1 row)
601+
602+
DROP TABLE t;
603+
-- 2 new columns, second has default
604+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
605+
INSERT INTO t (a,b,c) VALUES (1,2,3);
606+
ALTER TABLE t ADD COLUMN x int;
607+
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
608+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
609+
SELECT * FROM t;
610+
id | a | b | c | x | y
611+
----+---+---+---+---+---
612+
1 | 1 | 2 | 3 | | 5
613+
(1 row)
614+
615+
UPDATE t SET y = 2;
616+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":5}
617+
SELECT * FROM t;
618+
id | a | b | c | x | y
619+
----+---+---+---+---+---
620+
1 | 1 | 2 | 3 | | 2
621+
(1 row)
622+
623+
DROP TABLE t;
624+
-- 2 new columns, neither has default
625+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
626+
INSERT INTO t (a,b,c) VALUES (1,2,3);
627+
ALTER TABLE t ADD COLUMN x int;
628+
ALTER TABLE t ADD COLUMN y int;
629+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
630+
SELECT * FROM t;
631+
id | a | b | c | x | y
632+
----+---+---+---+---+---
633+
1 | 1 | 2 | 3 | |
634+
(1 row)
635+
636+
UPDATE t SET y = 2;
637+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":3,"x":null,"y":null}
638+
SELECT * FROM t;
639+
id | a | b | c | x | y
640+
----+---+---+---+---+---
641+
1 | 1 | 2 | 3 | | 2
642+
(1 row)
643+
644+
DROP TABLE t;
645+
-- same as last 4 tests but here the last original column has a NULL value
646+
-- 2 new columns, both have defaults
647+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
648+
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
649+
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
650+
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
651+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
652+
SELECT * FROM t;
653+
id | a | b | c | x | y
654+
----+---+---+---+---+---
655+
1 | 1 | 2 | | 4 | 5
656+
(1 row)
657+
658+
UPDATE t SET y = 2;
659+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":5}
660+
SELECT * FROM t;
661+
id | a | b | c | x | y
662+
----+---+---+---+---+---
663+
1 | 1 | 2 | | 4 | 2
664+
(1 row)
665+
666+
DROP TABLE t;
667+
-- 2 new columns, first has default
668+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
669+
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
670+
ALTER TABLE t ADD COLUMN x int NOT NULL DEFAULT 4;
671+
ALTER TABLE t ADD COLUMN y int;
672+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
673+
SELECT * FROM t;
674+
id | a | b | c | x | y
675+
----+---+---+---+---+---
676+
1 | 1 | 2 | | 4 |
677+
(1 row)
678+
679+
UPDATE t SET y = 2;
680+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":4,"y":null}
681+
SELECT * FROM t;
682+
id | a | b | c | x | y
683+
----+---+---+---+---+---
684+
1 | 1 | 2 | | 4 | 2
685+
(1 row)
686+
687+
DROP TABLE t;
688+
-- 2 new columns, second has default
689+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
690+
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
691+
ALTER TABLE t ADD COLUMN x int;
692+
ALTER TABLE t ADD COLUMN y int NOT NULL DEFAULT 5;
693+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
694+
SELECT * FROM t;
695+
id | a | b | c | x | y
696+
----+---+---+---+---+---
697+
1 | 1 | 2 | | | 5
698+
(1 row)
699+
700+
UPDATE t SET y = 2;
701+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":5}
702+
SELECT * FROM t;
703+
id | a | b | c | x | y
704+
----+---+---+---+---+---
705+
1 | 1 | 2 | | | 2
706+
(1 row)
707+
708+
DROP TABLE t;
709+
-- 2 new columns, neither has default
710+
CREATE TABLE t (id serial PRIMARY KEY, a int, b int, c int);
711+
INSERT INTO t (a,b,c) VALUES (1,2,NULL);
712+
ALTER TABLE t ADD COLUMN x int;
713+
ALTER TABLE t ADD COLUMN y int;
714+
CREATE TRIGGER a BEFORE UPDATE ON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
715+
SELECT * FROM t;
716+
id | a | b | c | x | y
717+
----+---+---+---+---+---
718+
1 | 1 | 2 | | |
719+
(1 row)
720+
721+
UPDATE t SET y = 2;
722+
NOTICE: old tuple: {"id":1,"a":1,"b":2,"c":null,"x":null,"y":null}
723+
SELECT * FROM t;
724+
id | a | b | c | x | y
725+
----+---+---+---+---+---
726+
1 | 1 | 2 | | | 2
727+
(1 row)
728+
729+
DROP TABLE t;
730+
-- cleanup
731+
DROP FUNCTION test_trigger();
732+
DROP TABLE t1;
544733
DROP FUNCTION set(name);
545734
DROP FUNCTION comp();
546735
DROP TABLE m;

‎src/test/regress/sql/fast_default.sql

Lines changed: 113 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -360,8 +360,120 @@ SELECT a,
360360
AS z
361361
FROM t1;
362362

363-
DROPTABLE t1;
364363
DROPTABLE T;
364+
365+
-- test that we account for missing columns without defaults correctly
366+
-- in expand_tuple, and that rows are correctly expanded for triggers
367+
368+
CREATEFUNCTIONtest_trigger()
369+
RETURNS trigger
370+
LANGUAGE plpgsql
371+
AS $$
372+
373+
begin
374+
raise notice'old tuple: %', to_json(OLD)::text;
375+
if TG_OP='DELETE'
376+
then
377+
return OLD;
378+
else
379+
return NEW;
380+
end if;
381+
end;
382+
383+
$$;
384+
385+
-- 2 new columns, both have defaults
386+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
387+
INSERT INTO t (a,b,c)VALUES (1,2,3);
388+
ALTERTABLE t ADD COLUMN xintNOT NULL DEFAULT4;
389+
ALTERTABLE t ADD COLUMN yintNOT NULL DEFAULT5;
390+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
391+
SELECT*FROM t;
392+
UPDATE tSET y=2;
393+
SELECT*FROM t;
394+
DROPTABLE t;
395+
396+
-- 2 new columns, first has default
397+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
398+
INSERT INTO t (a,b,c)VALUES (1,2,3);
399+
ALTERTABLE t ADD COLUMN xintNOT NULL DEFAULT4;
400+
ALTERTABLE t ADD COLUMN yint;
401+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
402+
SELECT*FROM t;
403+
UPDATE tSET y=2;
404+
SELECT*FROM t;
405+
DROPTABLE t;
406+
407+
-- 2 new columns, second has default
408+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
409+
INSERT INTO t (a,b,c)VALUES (1,2,3);
410+
ALTERTABLE t ADD COLUMN xint;
411+
ALTERTABLE t ADD COLUMN yintNOT NULL DEFAULT5;
412+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
413+
SELECT*FROM t;
414+
UPDATE tSET y=2;
415+
SELECT*FROM t;
416+
DROPTABLE t;
417+
418+
-- 2 new columns, neither has default
419+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
420+
INSERT INTO t (a,b,c)VALUES (1,2,3);
421+
ALTERTABLE t ADD COLUMN xint;
422+
ALTERTABLE t ADD COLUMN yint;
423+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
424+
SELECT*FROM t;
425+
UPDATE tSET y=2;
426+
SELECT*FROM t;
427+
DROPTABLE t;
428+
429+
-- same as last 4 tests but here the last original column has a NULL value
430+
-- 2 new columns, both have defaults
431+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
432+
INSERT INTO t (a,b,c)VALUES (1,2,NULL);
433+
ALTERTABLE t ADD COLUMN xintNOT NULL DEFAULT4;
434+
ALTERTABLE t ADD COLUMN yintNOT NULL DEFAULT5;
435+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
436+
SELECT*FROM t;
437+
UPDATE tSET y=2;
438+
SELECT*FROM t;
439+
DROPTABLE t;
440+
441+
-- 2 new columns, first has default
442+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
443+
INSERT INTO t (a,b,c)VALUES (1,2,NULL);
444+
ALTERTABLE t ADD COLUMN xintNOT NULL DEFAULT4;
445+
ALTERTABLE t ADD COLUMN yint;
446+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
447+
SELECT*FROM t;
448+
UPDATE tSET y=2;
449+
SELECT*FROM t;
450+
DROPTABLE t;
451+
452+
-- 2 new columns, second has default
453+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
454+
INSERT INTO t (a,b,c)VALUES (1,2,NULL);
455+
ALTERTABLE t ADD COLUMN xint;
456+
ALTERTABLE t ADD COLUMN yintNOT NULL DEFAULT5;
457+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
458+
SELECT*FROM t;
459+
UPDATE tSET y=2;
460+
SELECT*FROM t;
461+
DROPTABLE t;
462+
463+
-- 2 new columns, neither has default
464+
CREATETABLEt (idserialPRIMARY KEY, aint, bint, cint);
465+
INSERT INTO t (a,b,c)VALUES (1,2,NULL);
466+
ALTERTABLE t ADD COLUMN xint;
467+
ALTERTABLE t ADD COLUMN yint;
468+
CREATETRIGGERa BEFOREUPDATEON t FOR EACH ROW EXECUTE PROCEDURE test_trigger();
469+
SELECT*FROM t;
470+
UPDATE tSET y=2;
471+
SELECT*FROM t;
472+
DROPTABLE t;
473+
474+
-- cleanup
475+
DROPFUNCTION test_trigger();
476+
DROPTABLE t1;
365477
DROPFUNCTIONset(name);
366478
DROPFUNCTION comp();
367479
DROPTABLE m;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp