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

Commitd75206f

Browse files
committed
General function for SERIAL/IDENTITY/AUTOINCREMENT feature.
Handle INSERT event in timetravel().
1 parentcd7c56e commitd75206f

File tree

8 files changed

+241
-34
lines changed

8 files changed

+241
-34
lines changed

‎contrib/spi/Makefile

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,8 @@ ifdef REFINT_VERBOSE
99
CFLAGS+= -DREFINT_VERBOSE
1010
endif
1111

12-
TARGETS= refint$(DLSUFFIX) refint.sql timetravel$(DLSUFFIX) timetravel.sql
12+
TARGETS= refint$(DLSUFFIX) refint.sql timetravel$(DLSUFFIX) timetravel.sql\
13+
autoinc$(DLSUFFIX) autoinc.sql
1314

1415
CLEANFILES+=$(TARGETS)
1516

‎contrib/spi/README

Lines changed: 38 additions & 18 deletions
Original file line numberDiff line numberDiff line change
@@ -8,8 +8,8 @@ table/field names (as described below) while creating a trigger.
88

99
check_primary_key () is to used for foreign keys of a table.
1010

11-
Youare to create trigger (BEFORE INSERT OR UPDATE) using this
12-
function on a table referencing another table. Youare to specify
11+
Youhave to create trigger (BEFORE INSERT OR UPDATE) using this
12+
function on a table referencing another table. Youhave to specify
1313
as function arguments: triggered table column names which correspond
1414
to foreign key, referenced table name and column names in referenced
1515
table which correspond to primary/unique key.
@@ -18,8 +18,8 @@ one reference.
1818

1919
check_foreign_key () is to used for primary/unique keys of a table.
2020

21-
Youare to create trigger (BEFORE DELETE OR UPDATE) using this
22-
function on a table referenced by another table(s). Youare to specify
21+
Youhave to create trigger (BEFORE DELETE OR UPDATE) using this
22+
function on a table referenced by another table(s). Youhave to specify
2323
as function arguments: number of references for which function has to
2424
performe checking, action if referencing key found ('cascade' - to delete
2525
corresponding foreign key, 'restrict' - to abort transaction if foreign keys
@@ -42,20 +42,26 @@ refint.source).
4242

4343
Old internally supported time-travel (TT) used insert/delete
4444
transaction commit times. To get the same feature using triggers
45-
youare to add to a table two columns of abstime type to store
45+
youhave to add to a table two columns of abstime type to store
4646
date when a tuple was inserted (start_date) and changed/deleted
4747
(stop_date):
4848

4949
CREATE TABLE XXX (
5050
......
51-
date_onabstime default currabstime(),
52-
date_offabstime default 'infinity'
51+
date_onabstime,
52+
date_offabstime
5353
......
5454
);
5555

56-
- so, tuples being inserted with NULLs in date_on/date_off will get
57-
_current_date_ in date_on (name of start_date column in XXX) and INFINITY in
58-
date_off (name of stop_date column in XXX).
56+
CREATE TRIGGER timetravel
57+
BEFORE INSERT OR DELETE OR UPDATE ON tttest
58+
FOR EACH ROW
59+
EXECUTE PROCEDURE
60+
timetravel (date_on, date_off);
61+
62+
Tuples being inserted with NULLs in date_on/date_off will get current
63+
date in date_on (name of start_date column in XXX) and INFINITY in date_off
64+
(name of stop_date column in XXX).
5965

6066
Tuples with stop_date equal INFINITY are "valid now": when trigger will
6167
be fired for UPDATE/DELETE of a tuple with stop_date NOT equal INFINITY then
@@ -72,7 +78,7 @@ DELETE: new tuple will be inserted with stop_date setted to current date
7278
(and with the same data in other columns as in tuple being deleted).
7379

7480
NOTE:
75-
1. To get tuples "valid now" youare to add _stop_date_ = 'infinity'
81+
1. To get tuples "valid now" youhave to add _stop_date_ = 'infinity'
7682
to WHERE. Internally supported TT allowed to avoid this...
7783
Fixed rewriting RULEs could help here...
7884
As work arround you may use VIEWs...
@@ -83,22 +89,36 @@ DELETE: new tuple will be inserted with stop_date setted to current date
8389

8490
timetravel() is general trigger function.
8591

86-
You are to create trigger BEFORE (!!!) UPDATE OR DELETE using this
87-
function on a time-traveled table. You are to specify two arguments: name of
88-
start_date column and name of stop_date column in triggered table.
89-
90-
currabstime() may be used in DEFAULT for start_date column to get
91-
current date.
92+
You have to create trigger BEFORE (!!!) INSERT OR UPDATE OR DELETE using
93+
this function on a time-traveled table. You have to specify two arguments:
94+
name of start_date column and name of stop_date column in triggered table.
9295

9396
set_timetravel() allows you turn time-travel ON/OFF for a table:
9497

9598
set_timetravel('XXX', 1) will turn TT ON for table XXX (and report
9699
old status).
97100
set_timetravel('XXX', 0) will turn TT OFF for table XXX (-"-).
98101

99-
Turning TT OFF allows you do with a table ALL what you want.
102+
Turning TT OFF allows you do with a table ALL what you want!
100103

101104
There is example in timetravel.example.
102105

103106
To CREATE FUNCTIONs use timetravel.sql (will be made by gmake from
104107
timetravel.source).
108+
109+
110+
3. autoinc.c - function for implementing AUTOINCREMENT/IDENTITY feature.
111+
112+
You have to create BEFORE INSERT OR UPDATE trigger using function
113+
autoinc(). You have to specify as function arguments: column name
114+
(of int4 type) for which you want to get this feature and name of
115+
SEQUENCE from which next value has to be fetched when NULL or 0
116+
value is being inserted into column (, ... - you are able to specify
117+
as many column/sequence pairs as you need).
118+
119+
There is example in autoinc.example.
120+
121+
To CREATE FUNCTION use autoinc.sql (will be made by gmake from
122+
autoinc.source).
123+
124+

‎contrib/spi/autoinc.c

Lines changed: 100 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,100 @@
1+
2+
#include"executor/spi.h"/* this is what you need to work with SPI */
3+
#include"commands/trigger.h"/* -"- and triggers */
4+
5+
HeapTupleautoinc(void);
6+
7+
externint4nextval(structvarlena*seqin);
8+
9+
HeapTuple
10+
autoinc()
11+
{
12+
Trigger*trigger;/* to get trigger name */
13+
intnargs;/* # of arguments */
14+
int*chattrs;/* attnums of attributes to change */
15+
intchnattrs=0;/* # of above */
16+
Datum*newvals;/* vals of above */
17+
char**args;/* arguments */
18+
char*relname;/* triggered relation name */
19+
Relationrel;/* triggered relation */
20+
HeapTuplerettuple=NULL;
21+
TupleDesctupdesc;/* tuple description */
22+
boolisnull;
23+
inti;
24+
25+
if (!CurrentTriggerData)
26+
elog(WARN,"autoinc: triggers are not initialized");
27+
if (TRIGGER_FIRED_FOR_STATEMENT(CurrentTriggerData->tg_event))
28+
elog(WARN,"autoinc: can't process STATEMENT events");
29+
if (TRIGGER_FIRED_AFTER(CurrentTriggerData->tg_event))
30+
elog(WARN,"autoinc: must be fired before event");
31+
32+
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
33+
rettuple=CurrentTriggerData->tg_trigtuple;
34+
elseif (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
35+
rettuple=CurrentTriggerData->tg_newtuple;
36+
else
37+
elog(WARN,"autoinc: can't process DELETE events");
38+
39+
rel=CurrentTriggerData->tg_relation;
40+
relname=SPI_getrelname(rel);
41+
42+
trigger=CurrentTriggerData->tg_trigger;
43+
44+
nargs=trigger->tgnargs;
45+
if (nargs <=0||nargs %2!=0)
46+
elog(WARN,"autoinc (%s): even number gt 0 of arguments was expected",relname);
47+
48+
args=trigger->tgargs;
49+
tupdesc=rel->rd_att;
50+
51+
CurrentTriggerData=NULL;
52+
53+
chattrs= (int*)palloc (nargs/2*sizeof (int));
54+
newvals= (Datum*)palloc (nargs/2*sizeof (Datum));
55+
56+
for (i=0;i<nargs; )
57+
{
58+
structvarlena*seqname;
59+
intattnum=SPI_fnumber (tupdesc,args[i]);
60+
int32val;
61+
62+
if (attnum<0 )
63+
elog(WARN,"autoinc (%s): there is no attribute %s",relname,args[i]);
64+
if (SPI_gettypeid (tupdesc,attnum)!=INT4OID)
65+
elog(WARN,"autoinc (%s): attribute %s must be of INT4 type",
66+
relname,args[i]);
67+
68+
val=DatumGetInt32 (SPI_getbinval (rettuple,tupdesc,attnum,&isnull));
69+
70+
if (!isnull&&val!=0)
71+
{
72+
i+=2;
73+
continue;
74+
}
75+
76+
i++;
77+
chattrs[chnattrs]=attnum;
78+
seqname=textin (args[i]);
79+
newvals[chnattrs]=Int32GetDatum (nextval (seqname));
80+
if (DatumGetInt32 (newvals[chnattrs])==0 )
81+
newvals[chnattrs]=Int32GetDatum (nextval (seqname));
82+
pfree (seqname);
83+
chnattrs++;
84+
i++;
85+
}
86+
87+
if (chnattrs>0)
88+
{
89+
rettuple=SPI_modifytuple (rel,rettuple,chnattrs,chattrs,newvals,NULL);
90+
if (rettuple==NULL )
91+
elog (WARN,"autoinc (%s): %d returned by SPI_modifytuple",
92+
relname,SPI_result);
93+
}
94+
95+
pfree (relname);
96+
pfree (chattrs);
97+
pfree (newvals);
98+
99+
return (rettuple);
100+
}

‎contrib/spi/autoinc.example

Lines changed: 35 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,35 @@
1+
DROP SEQUENCE next_id;
2+
DROP TABLE ids;
3+
4+
CREATE SEQUENCE next_id START -2 MINVALUE -2;
5+
6+
CREATE TABLE ids (
7+
idint4,
8+
idesctext
9+
);
10+
11+
CREATE TRIGGER ids_nextid
12+
BEFORE INSERT OR UPDATE ON ids
13+
FOR EACH ROW
14+
EXECUTE PROCEDURE autoinc (id, next_id);
15+
16+
INSERT INTO ids VALUES (0, 'first (-2 ?)');
17+
INSERT INTO ids VALUES (null, 'second (-1 ?)');
18+
INSERT INTO ids(idesc) VALUES ('third (1 ?!)');
19+
20+
SELECT * FROM ids;
21+
22+
UPDATE ids SET id = null, idesc = 'first: -2 --> 2'
23+
WHERE idesc = 'first (-2 ?)';
24+
UPDATE ids SET id = 0, idesc = 'second: -1 --> 3'
25+
WHERE id = -1;
26+
UPDATE ids SET id = 4, idesc = 'third: 1 --> 4'
27+
WHERE id = 1;
28+
29+
SELECT * FROM ids;
30+
31+
SELECT 'Wasn''t it 4 ?' as nextval, nextval ('next_id') as value;
32+
33+
insert into ids (idesc) select textcat (idesc, '. Copy.') from ids;
34+
35+
SELECT * FROM ids;

‎contrib/spi/autoinc.source

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,6 @@
1+
DROP FUNCTION autoinc();
2+
3+
CREATE FUNCTION autoinc()
4+
RETURNS opaque
5+
AS '_OBJWD_/autoinc_DLSUFFIX_'
6+
LANGUAGE 'c';

‎contrib/spi/timetravel.c

Lines changed: 48 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -38,6 +38,8 @@ static EPlan *find_plan(char *ident, EPlan ** eplan, int *nplans);
3838
* 2.IF an delete affects tuple with stop_date eq INFINITY
3939
* then insert the same tuple with stop_date eq current date
4040
* ELSE - skip deletion of tuple.
41+
* 3.On INSERT, if start_date is NULL then current date will be
42+
* inserted, if stop_date is NULL then INFINITY will be inserted.
4143
*
4244
* In CREATE TRIGGER you are to specify start_date and stop_date column
4345
* names:
@@ -65,6 +67,7 @@ timetravel()
6567
EPlan*plan;/* prepared plan */
6668
charident[2*NAMEDATALEN];
6769
boolisnull;/* to know is some column NULL or not */
70+
boolisinsert= false;
6871
intret;
6972
inti;
7073

@@ -86,7 +89,7 @@ timetravel()
8689

8790
/* INSERT ? */
8891
if (TRIGGER_FIRED_BY_INSERT(CurrentTriggerData->tg_event))
89-
elog (WARN,"timetravel: can't process INSERT event");
92+
isinsert= true;
9093

9194
if (TRIGGER_FIRED_BY_UPDATE(CurrentTriggerData->tg_event))
9295
newtuple=CurrentTriggerData->tg_newtuple;
@@ -133,14 +136,57 @@ timetravel()
133136
relname,args[0],args[1]);
134137
}
135138

139+
if (isinsert)/* INSERT */
140+
{
141+
intchnattrs=0;
142+
intchattrs[2];
143+
Datumnewvals[2];
144+
145+
oldon=SPI_getbinval (trigtuple,tupdesc,attnum[0],&isnull);
146+
if (isnull)
147+
{
148+
newvals[chnattrs]=GetCurrentAbsoluteTime ();
149+
chattrs[chnattrs]=attnum[0];
150+
chnattrs++;
151+
}
152+
153+
oldoff=SPI_getbinval (trigtuple,tupdesc,attnum[1],&isnull);
154+
if (isnull)
155+
{
156+
if ((chnattrs==0&&DatumGetInt32 (oldon) >=NOEND_ABSTIME)||
157+
(chnattrs>0&&DatumGetInt32 (newvals[0]) >=NOEND_ABSTIME))
158+
elog (WARN,"timetravel (%s): %s ge %s",
159+
relname,args[0],args[1]);
160+
newvals[chnattrs]=NOEND_ABSTIME;
161+
chattrs[chnattrs]=attnum[1];
162+
chnattrs++;
163+
}
164+
else
165+
{
166+
if ((chnattrs==0&&DatumGetInt32 (oldon) >=
167+
DatumGetInt32 (oldoff))||
168+
(chnattrs>0&&DatumGetInt32 (newvals[0]) >=
169+
DatumGetInt32 (oldoff)))
170+
elog (WARN,"timetravel (%s): %s ge %s",
171+
relname,args[0],args[1]);
172+
}
173+
174+
pfree (relname);
175+
if (chnattrs <=0 )
176+
return (trigtuple);
177+
178+
rettuple=SPI_modifytuple (rel,trigtuple,chnattrs,
179+
chattrs,newvals,NULL);
180+
return (rettuple);
181+
}
182+
136183
oldon=SPI_getbinval (trigtuple,tupdesc,attnum[0],&isnull);
137184
if (isnull)
138185
elog(WARN,"timetravel (%s): %s must be NOT NULL",relname,args[0]);
139186

140187
oldoff=SPI_getbinval (trigtuple,tupdesc,attnum[1],&isnull);
141188
if (isnull)
142189
elog(WARN,"timetravel (%s): %s must be NOT NULL",relname,args[1]);
143-
144190
/*
145191
* If DELETE/UPDATE of tuple with stop_date neq INFINITY
146192
* then say upper Executor to skip operation for this tuple

‎contrib/spi/timetravel.example

Lines changed: 12 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -1,21 +1,26 @@
11
drop table tttest;
2+
23
create table tttest (
34
price_idint4,
45
price_valint4,
5-
price_onabstime default currabstime(),
6-
price_offabstime default 'infinity'
6+
price_onabstime,
7+
price_offabstime
78
);
89

9-
insert into tttest values (1, 1, null, null);
10-
insert into tttest values (2, 2, null, null);
11-
insert into tttest values (3, 3, null, null);
12-
1310
create trigger timetravel
14-
before delete or update on tttest
11+
beforeinsert ordelete or update on tttest
1512
for each row
1613
execute procedure
1714
timetravel (price_on, price_off);
1815

16+
insert into tttest values (1, 1, null, null);
17+
insert into tttest(price_id, price_val) values (2, 2);
18+
insert into tttest(price_id, price_val,price_off) values (3, 3, 'infinity');
19+
20+
insert into tttest(price_id, price_val,price_off) values (3, 3,
21+
datetime_abstime(datetime_mi_span('now', '100')));
22+
insert into tttest(price_id, price_val,price_on) values (3, 3, 'infinity');
23+
1924
select * from tttest;
2025
delete from tttest where price_id = 2;
2126
select * from tttest;

‎contrib/spi/timetravel.source

Lines changed: 0 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1,12 +1,6 @@
1-
DROP FUNCTION currabstime();
21
DROP FUNCTION timetravel();
32
DROP FUNCTION set_timetravel(name, int4);
43

5-
CREATE FUNCTION currabstime()
6-
RETURNS abstime
7-
AS '_OBJWD_/timetravel_DLSUFFIX_'
8-
LANGUAGE 'c';
9-
104
CREATE FUNCTION timetravel()
115
RETURNS opaque
126
AS '_OBJWD_/timetravel_DLSUFFIX_'

0 commit comments

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