Skip to content

Commit d75206f

Browse files
vadim4ovadim4o
committed
General function for SERIAL/IDENTITY/AUTOINCREMENT feature.
Handle INSERT event in timetravel().
1 parent cd7c56e commit d75206f

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-
You are to create trigger (BEFORE INSERT OR UPDATE) using this
12-
function on a table referencing another table. You are to specify
11+
You have to create trigger (BEFORE INSERT OR UPDATE) using this
12+
function on a table referencing another table. You have 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-
You are to create trigger (BEFORE DELETE OR UPDATE) using this
22-
function on a table referenced by another table(s). You are to specify
21+
You have to create trigger (BEFORE DELETE OR UPDATE) using this
22+
function on a table referenced by another table(s). You have 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-
you are to add to a table two columns of abstime type to store
45+
you have 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_on abstime default currabstime(),
52-
date_off abstime default 'infinity'
51+
date_on abstime,
52+
date_off abstime
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" you are to add _stop_date_ = 'infinity'
81+
1. To get tuples "valid now" you have 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+
HeapTuple autoinc(void);
6+
7+
extern int4 nextval(struct varlena * seqin);
8+
9+
HeapTuple
10+
autoinc()
11+
{
12+
Trigger *trigger; /* to get trigger name */
13+
int nargs; /* # of arguments */
14+
int *chattrs; /* attnums of attributes to change */
15+
int chnattrs = 0; /* # of above */
16+
Datum *newvals; /* vals of above */
17+
char **args; /* arguments */
18+
char *relname; /* triggered relation name */
19+
Relation rel; /* triggered relation */
20+
HeapTuple rettuple = NULL;
21+
TupleDesc tupdesc; /* tuple description */
22+
bool isnull;
23+
int i;
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+
else if (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+
struct varlena *seqname;
59+
int attnum = SPI_fnumber (tupdesc, args[i]);
60+
int32 val;
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+
id int4,
8+
idesc text
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
char ident[2 * NAMEDATALEN];
6769
bool isnull; /* to know is some column NULL or not */
70+
bool isinsert = false;
6871
int ret;
6972
int i;
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+
int chnattrs = 0;
142+
int chattrs[2];
143+
Datum newvals[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_id int4,
45
price_val int4,
5-
price_on abstime default currabstime(),
6-
price_off abstime default 'infinity'
6+
price_on abstime,
7+
price_off abstime
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+
before insert or delete 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)