public inbox for [email protected]
help / color / mirror / Atom feedRemove the refint contrib module (for v20)
15+ messages / 7 participants
[nested] [flat]
* Remove the refint contrib module (for v20)
@ 2026-06-05 19:27 Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:09 ` Re: Remove the refint contrib module (for v20) Tom Lane <[email protected]>
2026-06-29 06:23 ` Re: Remove the refint contrib module (for v20) Fujii Masao <[email protected]>
0 siblings, 3 replies; 15+ messages in thread
From: Ayush Tiwari @ 2026-06-05 19:27 UTC (permalink / raw)
To: pgsql-hackers; +Cc: Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>
Hi,
This is intended for v20.
refint has long been documented as superseded by the built-in foreign
key mechanism, and the recent run of fixes (cascade-UPDATE NULL
crash, SQL-injection docs, removal of the broken private SPI plan
cache) suggests it's costing more to maintain than its sample-code
value justifies. On the plan-cache thread [1], the idea of just
removing it in v20 came up and seemed to have some agreement, so
moving that to its own thread to discuss on its own terms.
The patch set is two pieces:
0001 removes refint:
- removes refint.c, refint.control, refint--1.0.sql, refint.example,
sql/refint.sql and expected/refint.out from contrib/spi/;
- drops refint from contrib/spi/Makefile and contrib/spi/meson.build,
the refint section from doc/src/sgml/contrib-spi.sgml, and the
now-unused EPlan typedef from src/tools/pgindent/typedefs.list.
0002 (test-only) teaches AdjustUpgrade.pm to drop the refint
extension and its leftover tables (pkeys, fkeys, fkeys2) from
contrib_regression_spi / regression_spi when upgrading from a version
that still shipped refint, so cross-version pg_upgrade tests don't
fail dump comparison once v20 opens.
I followed the existing < 19 btree_gist block style.
If we go ahead with this, a release-notes entry can land once
doc/src/sgml/release-20.sgml is created.
I can't run cross-version upgrade tests against v20 yet, so
the 0002 hunk would benefit from a look by someone with an xversion
buildfarm animal.
Thoughts? Does this look like the right approach, or is there a
reason to keep refint around?
[1] PostgreSQL: [PATCH] refint: Avoid reusing cascade UPDATE plans.
<https://www.postgresql.org/message-id/flat/CAJTYsWXU%2BfhuzrEd_bnrxyGH3%2Bny8QRQC2QHf3ws6s9iki3c2Q%4...;
Regards,
Ayush
Attachments:
[application/octet-stream] v1-0001-Remove-the-refint-contrib-module.patch (35.1K, ../../CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1=Zi=5N1Q_GSEQ@mail.gmail.com/3-v1-0001-Remove-the-refint-contrib-module.patch)
download | inline diff:
From 380e431166a1f87c5bd6e651049e63e2e8334e6f Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Fri, 5 Jun 2026 19:10:28 +0000
Subject: [PATCH v1 1/2] Remove the refint contrib module.
refint was sample code from the pre-built-in-FK era and has long been
documented as superseded by the built-in foreign key mechanism. Recent
fixes (cascade-UPDATE crash with NULL keys, removal of the broken private
SPI plan cache) made it clear that the code has more issues than its
sample-code value justifies.
Remove the C source, control file, SQL script, example file, and
regression test from contrib/spi/. Adjust the contrib/spi Makefile and
meson.build, drop the refint section from contrib-spi.sgml, and drop the
now-unused EPlan typedef from typedefs.list.
The other contrib/spi extensions (autoinc, insert_username, moddatetime)
are unaffected.
Discussion: https://postgr.es/m/CAJTYsWXU%2BfhuzrEd_bnrxyGH3%2Bny8QRQC2QHf3ws6s9iki3c2Q%40mail.gmail.com
---
contrib/spi/Makefile | 13 +-
contrib/spi/expected/refint.out | 113 -------
contrib/spi/meson.build | 30 --
contrib/spi/refint--1.0.sql | 14 -
contrib/spi/refint.c | 538 -------------------------------
contrib/spi/refint.control | 5 -
contrib/spi/refint.example | 82 -----
contrib/spi/sql/refint.sql | 97 ------
doc/src/sgml/contrib-spi.sgml | 51 ---
src/tools/pgindent/typedefs.list | 1 -
10 files changed, 4 insertions(+), 940 deletions(-)
delete mode 100644 contrib/spi/expected/refint.out
delete mode 100644 contrib/spi/refint--1.0.sql
delete mode 100644 contrib/spi/refint.c
delete mode 100644 contrib/spi/refint.control
delete mode 100644 contrib/spi/refint.example
delete mode 100644 contrib/spi/sql/refint.sql
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile
index 7ccbef8c926..805f7653e58 100644
--- a/contrib/spi/Makefile
+++ b/contrib/spi/Makefile
@@ -1,23 +1,18 @@
# contrib/spi/Makefile
-MODULES = autoinc insert_username moddatetime refint
+MODULES = autoinc insert_username moddatetime
-EXTENSION = autoinc insert_username moddatetime refint
+EXTENSION = autoinc insert_username moddatetime
DATA = autoinc--1.0.sql \
insert_username--1.0.sql \
- moddatetime--1.0.sql \
- refint--1.0.sql
+ moddatetime--1.0.sql
PGFILEDESC = "spi - examples of using SPI and triggers"
-REGRESS = autoinc refint
+REGRESS = autoinc
DOCS = $(addsuffix .example, $(MODULES))
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-PG_CPPFLAGS = -DREFINT_VERBOSE
-
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/spi/expected/refint.out b/contrib/spi/expected/refint.out
deleted file mode 100644
index 79633603217..00000000000
--- a/contrib/spi/expected/refint.out
+++ /dev/null
@@ -1,113 +0,0 @@
-CREATE EXTENSION refint;
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
-CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-ERROR: duplicate key value violates unique constraint "pkeys_i"
-DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
- trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
-----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
- check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | |
- check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | |
-(10 rows)
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/contrib/spi/meson.build b/contrib/spi/meson.build
index 4a9a2bef0a5..85f0b2276f7 100644
--- a/contrib/spi/meson.build
+++ b/contrib/spi/meson.build
@@ -79,35 +79,6 @@ install_data('moddatetime.example',
)
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-refint_cflags = ['-DREFINT_VERBOSE']
-
-refint_sources = files(
- 'refint.c',
-)
-
-if host_system == 'windows'
- refint_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
- '--NAME', 'refint',
- '--FILEDESC', 'spi - examples of using SPI and triggers',])
-endif
-
-refint = shared_module('refint',
- refint_sources,
- c_args: refint_cflags,
- kwargs: contrib_mod_args,
-)
-contrib_targets += refint
-
-install_data('refint.control', 'refint--1.0.sql',
- kwargs: contrib_data_args,
-)
-
-install_data('refint.example',
- kwargs: contrib_doc_args,
-)
-
tests += {
'name': 'spi',
'sd': meson.current_source_dir(),
@@ -115,7 +86,6 @@ tests += {
'regress': {
'sql': [
'autoinc',
- 'refint',
],
},
}
diff --git a/contrib/spi/refint--1.0.sql b/contrib/spi/refint--1.0.sql
deleted file mode 100644
index faf797c2157..00000000000
--- a/contrib/spi/refint--1.0.sql
+++ /dev/null
@@ -1,14 +0,0 @@
-/* contrib/spi/refint--1.0.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION refint" to load this file. \quit
-
-CREATE FUNCTION check_primary_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
-CREATE FUNCTION check_foreign_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
diff --git a/contrib/spi/refint.c b/contrib/spi/refint.c
deleted file mode 100644
index 0bfd963fa83..00000000000
--- a/contrib/spi/refint.c
+++ /dev/null
@@ -1,538 +0,0 @@
-/*
- * contrib/spi/refint.c
- *
- *
- * refint.c -- set of functions to define referential integrity
- * constraints using general triggers.
- */
-#include "postgres.h"
-
-#include <ctype.h>
-
-#include "commands/trigger.h"
-#include "executor/spi.h"
-#include "utils/builtins.h"
-#include "utils/rel.h"
-
-PG_MODULE_MAGIC_EXT(
- .name = "refint",
- .version = PG_VERSION
-);
-
-/*
- * check_primary_key () -- check that key in tuple being inserted/updated
- * references existing tuple in "primary" table.
- * Though it's called without args You have to specify referenced
- * table/keys while creating trigger: key field names in triggered table,
- * referenced table name, referenced key field names:
- * EXECUTE PROCEDURE
- * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
- */
-
-PG_FUNCTION_INFO_V1(check_primary_key);
-
-Datum
-check_primary_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: column names and table name */
- int nkeys; /* # of key columns (= nargs / 2) */
- Datum *kvals; /* key values */
- char *relname; /* referenced relation name */
- Relation rel; /* triggered relation */
- HeapTuple tuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr pplan; /* prepared plan */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- int ret;
- int i;
- StringInfoData sql;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_primary_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_primary_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired for row");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired by AFTER trigger");
-
- /* If INSERTion then must check Tuple to being inserted */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- tuple = trigdata->tg_trigtuple;
-
- /* Not should be called for DELETE */
- else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: cannot process DELETE events");
-
- /* If UPDATE, then must check new Tuple, not old one */
- else
- tuple = trigdata->tg_newtuple;
-
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs % 2 != 1) /* odd number of arguments! */
- /* internal error */
- elog(ERROR, "check_primary_key: odd number of arguments should be specified");
-
- nkeys = nargs / 2;
- relname = args[nkeys];
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum(tuple);
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
-
- initStringInfo(&sql);
-
- /*
- * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 = $1
- * [AND Pkey2 = $2 [...]]
- */
- appendStringInfo(&sql, "select 1 from %s where ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args[i + nkeys], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- pfree(sql.data);
-
- /*
- * Ok, execute prepared plan.
- */
- ret = SPI_execp(pplan, kvals, NULL, 1);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
-
- /*
- * If there are no tuples returned by SELECT then ...
- */
- if (SPI_processed == 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("tuple references non-existent key"),
- errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
-
- SPI_finish();
-
- return PointerGetDatum(tuple);
-}
-
-/*
- * check_foreign_key () -- check that key in tuple being deleted/updated
- * is not referenced by tuples in "foreign" table(s).
- * Though it's called without args You have to specify (while creating trigger):
- * number of references, action to do if key referenced
- * ('restrict' | 'setnull' | 'cascade'), key field names in triggered
- * ("primary") table and referencing table(s)/keys:
- * EXECUTE PROCEDURE
- * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
- * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
- */
-
-PG_FUNCTION_INFO_V1(check_foreign_key);
-
-Datum
-check_foreign_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: as described above */
- char **args_temp;
- int nrefs; /* number of references (== # of plans) */
- char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
- int nkeys; /* # of key columns */
- Datum *kvals; /* key values */
- char *relname; /* referencing relation name */
- Relation rel; /* triggered relation */
- HeapTuple trigtuple = NULL; /* tuple to being changed */
- HeapTuple newtuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr *splan; /* prepared plan(s) */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
- int is_update = 0;
- int ret;
- int i,
- r;
- char **args2;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_foreign_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired for row");
-
- /* Not should be called for INSERT */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: cannot process INSERT events");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired by AFTER trigger");
-
- /* Have to check tg_trigtuple - tuple being deleted */
- trigtuple = trigdata->tg_trigtuple;
-
- /*
- * But if this is UPDATE then we have to return tg_newtuple. Also, if key
- * in tg_newtuple is the same as in tg_trigtuple then nothing to do.
- */
- is_update = 0;
- if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
- {
- newtuple = trigdata->tg_newtuple;
- is_update = 1;
- }
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs < 5) /* nrefs, action, key, Relation, key - at
- * least */
- /* internal error */
- elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
-
- nrefs = pg_strtoint32(args[0]);
- if (nrefs < 1)
- /* internal error */
- elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
- action = pg_ascii_tolower((unsigned char) *(args[1]));
- if (action != 'r' && action != 'c' && action != 's')
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
- nargs -= 2;
- args += 2;
- nkeys = (nargs - nrefs) / (nrefs + 1);
- if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
- nargs + 2, nrefs);
-
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
- }
-
- /*
- * If UPDATE then get column value from new tuple being inserted and
- * compare is this the same as old one. For the moment we use string
- * presentation of values...
- */
- if (newtuple != NULL)
- {
- char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
- char *newval;
-
- /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
- if (oldval == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_getvalue returned %s", SPI_result_code_string(SPI_result));
- newval = SPI_getvalue(newtuple, tupdesc, fnumber);
- if (newval == NULL || strcmp(oldval, newval) != 0)
- isequal = false;
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
- args_temp = args;
- nargs -= nkeys;
- args += nkeys;
- args2 = args;
-
- splan = (SPIPlanPtr *) palloc(nrefs * sizeof(SPIPlanPtr));
-
- for (r = 0; r < nrefs; r++)
- {
- StringInfoData sql;
- SPIPlanPtr pplan;
-
- initStringInfo(&sql);
-
- relname = args2[0];
-
- /*---------
- * For 'R'estrict action we construct SELECT query:
- *
- * SELECT 1
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to check is tuple referenced or not.
- *---------
- */
- if (action == 'r')
- appendStringInfo(&sql, "select 1 from %s where ", relname);
-
- /*---------
- * For 'C'ascade action we construct DELETE query
- *
- * DELETE
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to delete all referencing tuples.
- *---------
- */
-
- /*
- * Max : Cascade with UPDATE query i create update query that updates
- * new key values in referenced tables
- */
-
-
- else if (action == 'c')
- {
- if (is_update == 1)
- {
- int fn;
- char *nv;
- int k;
-
- appendStringInfo(&sql, "update %s set ", relname);
- for (k = 1; k <= nkeys; k++)
- {
- fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
- Assert(fn > 0); /* already checked above */
- nv = SPI_getvalue(newtuple, tupdesc, fn);
-
- appendStringInfo(&sql, " %s = %s ",
- args2[k],
- nv ? quote_literal_cstr(nv) : "NULL");
- if (k < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
- else
- /* DELETE */
- appendStringInfo(&sql, "delete from %s where ", relname);
- }
-
- /*
- * For 'S'etnull action we construct UPDATE query - UPDATE
- * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]] WHERE
- * Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in all
- * referencing tuples to NULL.
- */
- else if (action == 's')
- {
- appendStringInfo(&sql, "update %s set ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = null", args2[i]);
- if (i < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
-
- /* Construct WHERE qual */
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args2[i], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- splan[r] = pplan;
-
- args2 += nkeys + 1; /* to the next relation */
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql.data);
-#endif
-
- pfree(sql.data);
- }
-
- /*
- * If UPDATE and key is not changed ...
- */
- if (newtuple != NULL && isequal)
- {
- SPI_finish();
- return PointerGetDatum(newtuple);
- }
-
- /*
- * Ok, execute prepared plan(s).
- */
- for (r = 0; r < nrefs; r++)
- {
- /*
- * For 'R'estrict we may to execute plan for one tuple only, for other
- * actions - for all tuples.
- */
- int tcount = (action == 'r') ? 1 : 0;
-
- relname = args[0];
-
- ret = SPI_execp(splan[r], kvals, NULL, tcount);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("SPI_execp returned %d", ret)));
-
- /* If action is 'R'estrict ... */
- if (action == 'r')
- {
- /* If there is tuple returned by SELECT then ... */
- if (SPI_processed > 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("\"%s\": tuple is referenced in \"%s\"",
- trigger->tgname, relname)));
- }
- else
- {
-#ifdef REFINT_VERBOSE
- const char *operation;
-
- if (action == 'c')
- operation = is_update ? "updated" : "deleted";
- else
- operation = "set to null";
-
- elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
- trigger->tgname, SPI_processed, relname, operation);
-#endif
- }
- args += nkeys + 1; /* to the next relation */
- }
-
- SPI_finish();
-
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
-}
diff --git a/contrib/spi/refint.control b/contrib/spi/refint.control
deleted file mode 100644
index cbede45784c..00000000000
--- a/contrib/spi/refint.control
+++ /dev/null
@@ -1,5 +0,0 @@
-# refint extension
-comment = 'functions for implementing referential integrity (obsolete)'
-default_version = '1.0'
-module_pathname = '$libdir/refint'
-relocatable = true
diff --git a/contrib/spi/refint.example b/contrib/spi/refint.example
deleted file mode 100644
index 299166d5041..00000000000
--- a/contrib/spi/refint.example
+++ /dev/null
@@ -1,82 +0,0 @@
---Column ID of table A is primary key:
-
-CREATE TABLE A (
- ID int4 not null
-);
-CREATE UNIQUE INDEX AI ON A (ID);
-
---Columns REFB of table B and REFC of C are foreign keys referencing ID of A:
-
-CREATE TABLE B (
- REFB int4
-);
-CREATE INDEX BI ON B (REFB);
-
-CREATE TABLE C (
- REFC int4
-);
-CREATE INDEX CI ON C (REFC);
-
---Trigger for table A:
-
-CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
-EXECUTE PROCEDURE
-check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
-/*
-2 - means that check must be performed for foreign keys of 2 tables.
-cascade - defines that corresponding keys must be deleted.
-ID - name of primary key column in triggered table (A). You may
- use as many columns as you need.
-B - name of (first) table with foreign keys.
-REFB - name of foreign key column in this table. You may use as many
- columns as you need, but number of key columns in referenced
- table (A) must be the same.
-C - name of second table with foreign keys.
-REFC - name of foreign key column in this table.
-*/
-
---Trigger for table B:
-
-CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFB', 'A', 'ID');
-
-/*
-REFB - name of foreign key column in triggered (B) table. You may use as
- many columns as you need, but number of key columns in referenced
- table must be the same.
-A - referenced table name.
-ID - name of primary key column in referenced table.
-*/
-
---Trigger for table C:
-
-CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFC', 'A', 'ID');
-
--- Now try
-
-INSERT INTO A VALUES (10);
-INSERT INTO A VALUES (20);
-INSERT INTO A VALUES (30);
-INSERT INTO A VALUES (40);
-INSERT INTO A VALUES (50);
-
-INSERT INTO B VALUES (1); -- invalid reference
-INSERT INTO B VALUES (10);
-INSERT INTO B VALUES (30);
-INSERT INTO B VALUES (30);
-
-INSERT INTO C VALUES (11); -- invalid reference
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (30);
-
-DELETE FROM A WHERE ID = 10;
-DELETE FROM A WHERE ID = 20;
-DELETE FROM A WHERE ID = 30;
-
-SELECT * FROM A;
-SELECT * FROM B;
-SELECT * FROM C;
diff --git a/contrib/spi/sql/refint.sql b/contrib/spi/sql/refint.sql
deleted file mode 100644
index 63458127917..00000000000
--- a/contrib/spi/sql/refint.sql
+++ /dev/null
@@ -1,97 +0,0 @@
-CREATE EXTENSION refint;
-
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
-
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
-
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
-
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
-
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 6fa9479d1b9..b585083c6fb 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -24,57 +24,6 @@
separately-installable extension.
</para>
- <sect2 id="contrib-spi-refint">
- <title>refint — Functions for Implementing Referential Integrity</title>
-
- <para>
- <function>check_primary_key()</function> and
- <function>check_foreign_key()</function> are used to check foreign key constraints.
- (This functionality is long since superseded by the built-in foreign
- key mechanism, of course, but the module is still useful as an example.)
- </para>
-
- <para>
- <function>check_primary_key()</function> checks the referencing table.
- To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this
- function on a table referencing another table. Specify as the trigger
- arguments: the referencing table's column name(s) which form the foreign
- key, the referenced table name, and the column names in the referenced table
- which form the primary/unique key. To handle multiple foreign
- keys, create a trigger for each reference.
- </para>
-
- <para>
- <function>check_foreign_key()</function> checks the referenced table.
- To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this
- function on a table referenced by other table(s). Specify as the trigger
- arguments: the number of referencing tables for which the function has to
- perform checking, the action if a referencing key is found
- (<literal>cascade</literal> — to delete the referencing row,
- <literal>restrict</literal> — to abort transaction if referencing keys
- exist, <literal>setnull</literal> — to set referencing key fields to null),
- the triggered table's column names which form the primary/unique key, then
- the referencing table name and column names (repeated for as many
- referencing tables as were specified by first argument). Note that the
- primary/unique key columns should be marked NOT NULL and should have a
- unique index.
- </para>
-
- <para>
- Note that if these triggers are executed from
- another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
- example, if a user inserts row1 and then the <literal>BEFORE</literal>
- trigger inserts row2 and calls a trigger with the
- <function>check_foreign_key()</function>,
- the <function>check_foreign_key()</function>
- function will not see row1 and will fail.
- </para>
-
- <para>
- There are examples in <filename>refint.example</filename>.
- </para>
- </sect2>
-
<sect2 id="contrib-spi-autoinc">
<title>autoinc — Functions for Autoincrementing Fields</title>
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cf40c87043..7903f1640c1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -723,7 +723,6 @@ ENGINE
EOM_flatten_into_method
EOM_get_flat_size_method
EPQState
-EPlan
EState
EStatus
EVP_CIPHER
--
2.43.0
[application/octet-stream] v1-0002-Handle-refint-removal-in-cross-version-pg_upgrade-te.patch (1.9K, ../../CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1=Zi=5N1Q_GSEQ@mail.gmail.com/4-v1-0002-Handle-refint-removal-in-cross-version-pg_upgrade-te.patch)
download | inline diff:
From 1d3ca3f90db4d669b80286535352be4549c54b5f Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Fri, 5 Jun 2026 19:10:35 +0000
Subject: [PATCH v1 2/2] Handle refint removal in cross-version pg_upgrade
tests.
The previous commit removes the refint extension in v20. When cross-
version pg_upgrade tests upgrade from a version that still shipped
refint, the upgraded contrib_regression_spi (and regression_spi)
databases still have the refint extension and its tables (pkeys,
fkeys, fkeys2) in their catalogs, but a fresh v20 install does not.
Without adjustment the dump comparison fails.
Teach AdjustUpgrade.pm to drop the refint extension and its leftover
tables in those databases when the source version is < 20, matching
the existing pattern for prior cross-version test fixtures.
Discussion: https://postgr.es/m/CAJTYsWXU%2BfhuzrEd_bnrxyGH3%2Bny8QRQC2QHf3ws6s9iki3c2Q%40mail.gmail.com
---
.../perl/PostgreSQL/Test/AdjustUpgrade.pm | 21 +++++++++++++++++++
1 file changed, 21 insertions(+)
diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index 8a0c6c98a39..18b52d26f3e 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -141,6 +141,27 @@ sub adjust_database_contents
}
}
+ # refint was removed in v20
+ if ($old_version < 20)
+ {
+ if ($dbnames{"contrib_regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'contrib_regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ if ($dbnames{"regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ }
+
# we removed these test-support functions in v18
if ($old_version < 18)
{
--
2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-05 21:05 ` Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2 siblings, 1 reply; 15+ messages in thread
From: Nathan Bossart @ 2026-06-05 21:05 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-hackers; Álvaro Herrera <[email protected]>
On Sat, Jun 06, 2026 at 12:57:56AM +0530, Ayush Tiwari wrote:
> Thoughts? Does this look like the right approach, or is there a
> reason to keep refint around?
+1. Do we need an appendix-obsolete entry for the docs?
--
nathan
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
@ 2026-06-05 22:05 ` Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Daniel Gustafsson @ 2026-06-05 22:05 UTC (permalink / raw)
To: Nathan Bossart <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; pgsql-hackers; Álvaro Herrera <[email protected]>
> On 5 Jun 2026, at 23:05, Nathan Bossart <[email protected]> wrote:
>
> On Sat, Jun 06, 2026 at 12:57:56AM +0530, Ayush Tiwari wrote:
>> Thoughts? Does this look like the right approach, or is there a
>> reason to keep refint around?
>
> +1. Do we need an appendix-obsolete entry for the docs?
Seems like a reasonable thing to add. Once merged, we should probably add (and
backpatch) a mention to the docs that the functionality will be removed in v20.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
@ 2026-06-08 19:14 ` Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Ayush Tiwari @ 2026-06-08 19:14 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; Nathan Bossart <[email protected]>; +Cc: pgsql-hackers; Álvaro Herrera <[email protected]>; Tom Lane <[email protected]>
Hi,
On Sat, 6 Jun 2026 at 03:35, Daniel Gustafsson <[email protected]> wrote:
> > On 5 Jun 2026, at 23:05, Nathan Bossart <[email protected]>
> wrote:
> >
> > On Sat, Jun 06, 2026 at 12:57:56AM +0530, Ayush Tiwari wrote:
> >> Thoughts? Does this look like the right approach, or is there a
> >> reason to keep refint around?
> >
> > +1. Do we need an appendix-obsolete entry for the docs?
>
> Seems like a reasonable thing to add. Once merged, we should probably add
> (and
> backpatch) a mention to the docs that the functionality will be removed in
> v20.
>
v2 attached. 0001 now adds an appendix-obsolete entry under the same
contrib-spi-refint id, so existing /current/ documentation links keep
resolving (mirrors the auth-radius pattern). 0002 is unchanged.
The back-patched "will be removed in v20" notice on v19 (and older)
I can send it post this merge, as suggested.
Please let me know if I've missed something more. Or if I should
split v2-0001 into 2 parts, one for removal and the other for appendix-
obsolete entry.
Regards,
Ayush
Attachments:
[application/octet-stream] v2-0001-Remove-the-refint-contrib-module.patch (37.5K, ../../CAJTYsWXoWWyB6JNAqi2Fpv7DgapM5TbHei8G=UsmWgXkSAccbg@mail.gmail.com/3-v2-0001-Remove-the-refint-contrib-module.patch)
download | inline diff:
From 75e2af6a08de4d04e113e60fee9833a81a997970 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 8 Jun 2026 19:06:31 +0000
Subject: [PATCH v2 1/2] Remove the refint contrib module.
refint was sample code from the pre-built-in-FK era and has long been
documented as superseded by the built-in foreign key mechanism. Recent
fixes (cascade-UPDATE crash with NULL keys, removal of the broken private
SPI plan cache) made it clear that the code has more issues than its
sample-code value justifies, and there was consensus on the prior thread
to drop it once v20 opens for development.
Remove the C source, control file, SQL script, example file, and
regression test from contrib/spi/. Adjust the contrib/spi Makefile and
meson.build, drop the refint section from contrib-spi.sgml, and drop the
now-unused EPlan typedef from typedefs.list. Add an appendix-obsolete
entry under the same contrib-spi-refint id so existing /current/
documentation links keep resolving.
The other contrib/spi extensions (autoinc, insert_username, moddatetime)
are unaffected.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
contrib/spi/Makefile | 13 +-
contrib/spi/expected/refint.out | 113 -----
contrib/spi/meson.build | 30 --
contrib/spi/refint--1.0.sql | 14 -
contrib/spi/refint.c | 538 ---------------------
contrib/spi/refint.control | 5 -
contrib/spi/refint.example | 82 ----
contrib/spi/sql/refint.sql | 97 ----
doc/src/sgml/appendix-obsolete-refint.sgml | 24 +
doc/src/sgml/appendix-obsolete.sgml | 1 +
doc/src/sgml/contrib-spi.sgml | 51 --
doc/src/sgml/filelist.sgml | 1 +
src/tools/pgindent/typedefs.list | 1 -
13 files changed, 30 insertions(+), 940 deletions(-)
delete mode 100644 contrib/spi/expected/refint.out
delete mode 100644 contrib/spi/refint--1.0.sql
delete mode 100644 contrib/spi/refint.c
delete mode 100644 contrib/spi/refint.control
delete mode 100644 contrib/spi/refint.example
delete mode 100644 contrib/spi/sql/refint.sql
create mode 100644 doc/src/sgml/appendix-obsolete-refint.sgml
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile
index 7ccbef8c926..805f7653e58 100644
--- a/contrib/spi/Makefile
+++ b/contrib/spi/Makefile
@@ -1,23 +1,18 @@
# contrib/spi/Makefile
-MODULES = autoinc insert_username moddatetime refint
+MODULES = autoinc insert_username moddatetime
-EXTENSION = autoinc insert_username moddatetime refint
+EXTENSION = autoinc insert_username moddatetime
DATA = autoinc--1.0.sql \
insert_username--1.0.sql \
- moddatetime--1.0.sql \
- refint--1.0.sql
+ moddatetime--1.0.sql
PGFILEDESC = "spi - examples of using SPI and triggers"
-REGRESS = autoinc refint
+REGRESS = autoinc
DOCS = $(addsuffix .example, $(MODULES))
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-PG_CPPFLAGS = -DREFINT_VERBOSE
-
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/spi/expected/refint.out b/contrib/spi/expected/refint.out
deleted file mode 100644
index 79633603217..00000000000
--- a/contrib/spi/expected/refint.out
+++ /dev/null
@@ -1,113 +0,0 @@
-CREATE EXTENSION refint;
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
-CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-ERROR: duplicate key value violates unique constraint "pkeys_i"
-DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
- trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
-----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
- check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | |
- check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | |
-(10 rows)
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/contrib/spi/meson.build b/contrib/spi/meson.build
index 4a9a2bef0a5..85f0b2276f7 100644
--- a/contrib/spi/meson.build
+++ b/contrib/spi/meson.build
@@ -79,35 +79,6 @@ install_data('moddatetime.example',
)
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-refint_cflags = ['-DREFINT_VERBOSE']
-
-refint_sources = files(
- 'refint.c',
-)
-
-if host_system == 'windows'
- refint_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
- '--NAME', 'refint',
- '--FILEDESC', 'spi - examples of using SPI and triggers',])
-endif
-
-refint = shared_module('refint',
- refint_sources,
- c_args: refint_cflags,
- kwargs: contrib_mod_args,
-)
-contrib_targets += refint
-
-install_data('refint.control', 'refint--1.0.sql',
- kwargs: contrib_data_args,
-)
-
-install_data('refint.example',
- kwargs: contrib_doc_args,
-)
-
tests += {
'name': 'spi',
'sd': meson.current_source_dir(),
@@ -115,7 +86,6 @@ tests += {
'regress': {
'sql': [
'autoinc',
- 'refint',
],
},
}
diff --git a/contrib/spi/refint--1.0.sql b/contrib/spi/refint--1.0.sql
deleted file mode 100644
index faf797c2157..00000000000
--- a/contrib/spi/refint--1.0.sql
+++ /dev/null
@@ -1,14 +0,0 @@
-/* contrib/spi/refint--1.0.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION refint" to load this file. \quit
-
-CREATE FUNCTION check_primary_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
-CREATE FUNCTION check_foreign_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
diff --git a/contrib/spi/refint.c b/contrib/spi/refint.c
deleted file mode 100644
index 0bfd963fa83..00000000000
--- a/contrib/spi/refint.c
+++ /dev/null
@@ -1,538 +0,0 @@
-/*
- * contrib/spi/refint.c
- *
- *
- * refint.c -- set of functions to define referential integrity
- * constraints using general triggers.
- */
-#include "postgres.h"
-
-#include <ctype.h>
-
-#include "commands/trigger.h"
-#include "executor/spi.h"
-#include "utils/builtins.h"
-#include "utils/rel.h"
-
-PG_MODULE_MAGIC_EXT(
- .name = "refint",
- .version = PG_VERSION
-);
-
-/*
- * check_primary_key () -- check that key in tuple being inserted/updated
- * references existing tuple in "primary" table.
- * Though it's called without args You have to specify referenced
- * table/keys while creating trigger: key field names in triggered table,
- * referenced table name, referenced key field names:
- * EXECUTE PROCEDURE
- * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
- */
-
-PG_FUNCTION_INFO_V1(check_primary_key);
-
-Datum
-check_primary_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: column names and table name */
- int nkeys; /* # of key columns (= nargs / 2) */
- Datum *kvals; /* key values */
- char *relname; /* referenced relation name */
- Relation rel; /* triggered relation */
- HeapTuple tuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr pplan; /* prepared plan */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- int ret;
- int i;
- StringInfoData sql;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_primary_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_primary_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired for row");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired by AFTER trigger");
-
- /* If INSERTion then must check Tuple to being inserted */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- tuple = trigdata->tg_trigtuple;
-
- /* Not should be called for DELETE */
- else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: cannot process DELETE events");
-
- /* If UPDATE, then must check new Tuple, not old one */
- else
- tuple = trigdata->tg_newtuple;
-
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs % 2 != 1) /* odd number of arguments! */
- /* internal error */
- elog(ERROR, "check_primary_key: odd number of arguments should be specified");
-
- nkeys = nargs / 2;
- relname = args[nkeys];
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum(tuple);
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
-
- initStringInfo(&sql);
-
- /*
- * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 = $1
- * [AND Pkey2 = $2 [...]]
- */
- appendStringInfo(&sql, "select 1 from %s where ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args[i + nkeys], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- pfree(sql.data);
-
- /*
- * Ok, execute prepared plan.
- */
- ret = SPI_execp(pplan, kvals, NULL, 1);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
-
- /*
- * If there are no tuples returned by SELECT then ...
- */
- if (SPI_processed == 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("tuple references non-existent key"),
- errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
-
- SPI_finish();
-
- return PointerGetDatum(tuple);
-}
-
-/*
- * check_foreign_key () -- check that key in tuple being deleted/updated
- * is not referenced by tuples in "foreign" table(s).
- * Though it's called without args You have to specify (while creating trigger):
- * number of references, action to do if key referenced
- * ('restrict' | 'setnull' | 'cascade'), key field names in triggered
- * ("primary") table and referencing table(s)/keys:
- * EXECUTE PROCEDURE
- * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
- * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
- */
-
-PG_FUNCTION_INFO_V1(check_foreign_key);
-
-Datum
-check_foreign_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: as described above */
- char **args_temp;
- int nrefs; /* number of references (== # of plans) */
- char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
- int nkeys; /* # of key columns */
- Datum *kvals; /* key values */
- char *relname; /* referencing relation name */
- Relation rel; /* triggered relation */
- HeapTuple trigtuple = NULL; /* tuple to being changed */
- HeapTuple newtuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr *splan; /* prepared plan(s) */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
- int is_update = 0;
- int ret;
- int i,
- r;
- char **args2;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_foreign_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired for row");
-
- /* Not should be called for INSERT */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: cannot process INSERT events");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired by AFTER trigger");
-
- /* Have to check tg_trigtuple - tuple being deleted */
- trigtuple = trigdata->tg_trigtuple;
-
- /*
- * But if this is UPDATE then we have to return tg_newtuple. Also, if key
- * in tg_newtuple is the same as in tg_trigtuple then nothing to do.
- */
- is_update = 0;
- if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
- {
- newtuple = trigdata->tg_newtuple;
- is_update = 1;
- }
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs < 5) /* nrefs, action, key, Relation, key - at
- * least */
- /* internal error */
- elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
-
- nrefs = pg_strtoint32(args[0]);
- if (nrefs < 1)
- /* internal error */
- elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
- action = pg_ascii_tolower((unsigned char) *(args[1]));
- if (action != 'r' && action != 'c' && action != 's')
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
- nargs -= 2;
- args += 2;
- nkeys = (nargs - nrefs) / (nrefs + 1);
- if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
- nargs + 2, nrefs);
-
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
- }
-
- /*
- * If UPDATE then get column value from new tuple being inserted and
- * compare is this the same as old one. For the moment we use string
- * presentation of values...
- */
- if (newtuple != NULL)
- {
- char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
- char *newval;
-
- /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
- if (oldval == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_getvalue returned %s", SPI_result_code_string(SPI_result));
- newval = SPI_getvalue(newtuple, tupdesc, fnumber);
- if (newval == NULL || strcmp(oldval, newval) != 0)
- isequal = false;
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
- args_temp = args;
- nargs -= nkeys;
- args += nkeys;
- args2 = args;
-
- splan = (SPIPlanPtr *) palloc(nrefs * sizeof(SPIPlanPtr));
-
- for (r = 0; r < nrefs; r++)
- {
- StringInfoData sql;
- SPIPlanPtr pplan;
-
- initStringInfo(&sql);
-
- relname = args2[0];
-
- /*---------
- * For 'R'estrict action we construct SELECT query:
- *
- * SELECT 1
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to check is tuple referenced or not.
- *---------
- */
- if (action == 'r')
- appendStringInfo(&sql, "select 1 from %s where ", relname);
-
- /*---------
- * For 'C'ascade action we construct DELETE query
- *
- * DELETE
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to delete all referencing tuples.
- *---------
- */
-
- /*
- * Max : Cascade with UPDATE query i create update query that updates
- * new key values in referenced tables
- */
-
-
- else if (action == 'c')
- {
- if (is_update == 1)
- {
- int fn;
- char *nv;
- int k;
-
- appendStringInfo(&sql, "update %s set ", relname);
- for (k = 1; k <= nkeys; k++)
- {
- fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
- Assert(fn > 0); /* already checked above */
- nv = SPI_getvalue(newtuple, tupdesc, fn);
-
- appendStringInfo(&sql, " %s = %s ",
- args2[k],
- nv ? quote_literal_cstr(nv) : "NULL");
- if (k < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
- else
- /* DELETE */
- appendStringInfo(&sql, "delete from %s where ", relname);
- }
-
- /*
- * For 'S'etnull action we construct UPDATE query - UPDATE
- * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]] WHERE
- * Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in all
- * referencing tuples to NULL.
- */
- else if (action == 's')
- {
- appendStringInfo(&sql, "update %s set ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = null", args2[i]);
- if (i < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
-
- /* Construct WHERE qual */
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args2[i], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- splan[r] = pplan;
-
- args2 += nkeys + 1; /* to the next relation */
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql.data);
-#endif
-
- pfree(sql.data);
- }
-
- /*
- * If UPDATE and key is not changed ...
- */
- if (newtuple != NULL && isequal)
- {
- SPI_finish();
- return PointerGetDatum(newtuple);
- }
-
- /*
- * Ok, execute prepared plan(s).
- */
- for (r = 0; r < nrefs; r++)
- {
- /*
- * For 'R'estrict we may to execute plan for one tuple only, for other
- * actions - for all tuples.
- */
- int tcount = (action == 'r') ? 1 : 0;
-
- relname = args[0];
-
- ret = SPI_execp(splan[r], kvals, NULL, tcount);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("SPI_execp returned %d", ret)));
-
- /* If action is 'R'estrict ... */
- if (action == 'r')
- {
- /* If there is tuple returned by SELECT then ... */
- if (SPI_processed > 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("\"%s\": tuple is referenced in \"%s\"",
- trigger->tgname, relname)));
- }
- else
- {
-#ifdef REFINT_VERBOSE
- const char *operation;
-
- if (action == 'c')
- operation = is_update ? "updated" : "deleted";
- else
- operation = "set to null";
-
- elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
- trigger->tgname, SPI_processed, relname, operation);
-#endif
- }
- args += nkeys + 1; /* to the next relation */
- }
-
- SPI_finish();
-
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
-}
diff --git a/contrib/spi/refint.control b/contrib/spi/refint.control
deleted file mode 100644
index cbede45784c..00000000000
--- a/contrib/spi/refint.control
+++ /dev/null
@@ -1,5 +0,0 @@
-# refint extension
-comment = 'functions for implementing referential integrity (obsolete)'
-default_version = '1.0'
-module_pathname = '$libdir/refint'
-relocatable = true
diff --git a/contrib/spi/refint.example b/contrib/spi/refint.example
deleted file mode 100644
index 299166d5041..00000000000
--- a/contrib/spi/refint.example
+++ /dev/null
@@ -1,82 +0,0 @@
---Column ID of table A is primary key:
-
-CREATE TABLE A (
- ID int4 not null
-);
-CREATE UNIQUE INDEX AI ON A (ID);
-
---Columns REFB of table B and REFC of C are foreign keys referencing ID of A:
-
-CREATE TABLE B (
- REFB int4
-);
-CREATE INDEX BI ON B (REFB);
-
-CREATE TABLE C (
- REFC int4
-);
-CREATE INDEX CI ON C (REFC);
-
---Trigger for table A:
-
-CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
-EXECUTE PROCEDURE
-check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
-/*
-2 - means that check must be performed for foreign keys of 2 tables.
-cascade - defines that corresponding keys must be deleted.
-ID - name of primary key column in triggered table (A). You may
- use as many columns as you need.
-B - name of (first) table with foreign keys.
-REFB - name of foreign key column in this table. You may use as many
- columns as you need, but number of key columns in referenced
- table (A) must be the same.
-C - name of second table with foreign keys.
-REFC - name of foreign key column in this table.
-*/
-
---Trigger for table B:
-
-CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFB', 'A', 'ID');
-
-/*
-REFB - name of foreign key column in triggered (B) table. You may use as
- many columns as you need, but number of key columns in referenced
- table must be the same.
-A - referenced table name.
-ID - name of primary key column in referenced table.
-*/
-
---Trigger for table C:
-
-CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFC', 'A', 'ID');
-
--- Now try
-
-INSERT INTO A VALUES (10);
-INSERT INTO A VALUES (20);
-INSERT INTO A VALUES (30);
-INSERT INTO A VALUES (40);
-INSERT INTO A VALUES (50);
-
-INSERT INTO B VALUES (1); -- invalid reference
-INSERT INTO B VALUES (10);
-INSERT INTO B VALUES (30);
-INSERT INTO B VALUES (30);
-
-INSERT INTO C VALUES (11); -- invalid reference
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (30);
-
-DELETE FROM A WHERE ID = 10;
-DELETE FROM A WHERE ID = 20;
-DELETE FROM A WHERE ID = 30;
-
-SELECT * FROM A;
-SELECT * FROM B;
-SELECT * FROM C;
diff --git a/contrib/spi/sql/refint.sql b/contrib/spi/sql/refint.sql
deleted file mode 100644
index 63458127917..00000000000
--- a/contrib/spi/sql/refint.sql
+++ /dev/null
@@ -1,97 +0,0 @@
-CREATE EXTENSION refint;
-
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
-
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
-
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
-
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
-
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/doc/src/sgml/appendix-obsolete-refint.sgml b/doc/src/sgml/appendix-obsolete-refint.sgml
new file mode 100644
index 00000000000..2ec2029153f
--- /dev/null
+++ b/doc/src/sgml/appendix-obsolete-refint.sgml
@@ -0,0 +1,24 @@
+<!-- doc/src/sgml/appendix-obsolete-refint.sgml -->
+<!--
+ See doc/src/sgml/appendix-obsolete.sgml for why this file exists. Do not change the id attribute.
+-->
+
+<sect1 id="contrib-spi-refint">
+ <title>refint Module Removed</title>
+
+ <indexterm zone="contrib-spi-refint">
+ <primary>refint</primary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> 19 and below shipped a
+ contrib module named <literal>refint</literal> that provided the
+ trigger functions <function>check_primary_key()</function> and
+ <function>check_foreign_key()</function> as an early way to enforce
+ referential integrity. This functionality was long superseded by
+ the built-in foreign key mechanism (see
+ <xref linkend="ddl-constraints-fk"/>), and the module was removed
+ in <productname>PostgreSQL</productname> 20.
+ </para>
+
+</sect1>
diff --git a/doc/src/sgml/appendix-obsolete.sgml b/doc/src/sgml/appendix-obsolete.sgml
index cc002653052..759a65cb5e0 100644
--- a/doc/src/sgml/appendix-obsolete.sgml
+++ b/doc/src/sgml/appendix-obsolete.sgml
@@ -39,5 +39,6 @@
&obsolete-pgresetxlog;
&obsolete-pgreceivexlog;
&obsolete-auth-radius;
+ &obsolete-refint;
</appendix>
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 6fa9479d1b9..b585083c6fb 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -24,57 +24,6 @@
separately-installable extension.
</para>
- <sect2 id="contrib-spi-refint">
- <title>refint — Functions for Implementing Referential Integrity</title>
-
- <para>
- <function>check_primary_key()</function> and
- <function>check_foreign_key()</function> are used to check foreign key constraints.
- (This functionality is long since superseded by the built-in foreign
- key mechanism, of course, but the module is still useful as an example.)
- </para>
-
- <para>
- <function>check_primary_key()</function> checks the referencing table.
- To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this
- function on a table referencing another table. Specify as the trigger
- arguments: the referencing table's column name(s) which form the foreign
- key, the referenced table name, and the column names in the referenced table
- which form the primary/unique key. To handle multiple foreign
- keys, create a trigger for each reference.
- </para>
-
- <para>
- <function>check_foreign_key()</function> checks the referenced table.
- To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this
- function on a table referenced by other table(s). Specify as the trigger
- arguments: the number of referencing tables for which the function has to
- perform checking, the action if a referencing key is found
- (<literal>cascade</literal> — to delete the referencing row,
- <literal>restrict</literal> — to abort transaction if referencing keys
- exist, <literal>setnull</literal> — to set referencing key fields to null),
- the triggered table's column names which form the primary/unique key, then
- the referencing table name and column names (repeated for as many
- referencing tables as were specified by first argument). Note that the
- primary/unique key columns should be marked NOT NULL and should have a
- unique index.
- </para>
-
- <para>
- Note that if these triggers are executed from
- another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
- example, if a user inserts row1 and then the <literal>BEFORE</literal>
- trigger inserts row2 and calls a trigger with the
- <function>check_foreign_key()</function>,
- the <function>check_foreign_key()</function>
- function will not see row1 and will fail.
- </para>
-
- <para>
- There are examples in <filename>refint.example</filename>.
- </para>
- </sect2>
-
<sect2 id="contrib-spi-autoinc">
<title>autoinc — Functions for Autoincrementing Fields</title>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 25a85082759..082d5565215 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -209,3 +209,4 @@
<!ENTITY obsolete-pgresetxlog SYSTEM "appendix-obsolete-pgresetxlog.sgml">
<!ENTITY obsolete-pgreceivexlog SYSTEM "appendix-obsolete-pgreceivexlog.sgml">
<!ENTITY obsolete-auth-radius SYSTEM "appendix-obsolete-auth-radius.sgml">
+<!ENTITY obsolete-refint SYSTEM "appendix-obsolete-refint.sgml">
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cf40c87043..7903f1640c1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -723,7 +723,6 @@ ENGINE
EOM_flatten_into_method
EOM_get_flat_size_method
EPQState
-EPlan
EState
EStatus
EVP_CIPHER
--
2.43.0
[application/octet-stream] v2-0002-Handle-refint-removal-in-cross-version-pg_upgrade-te.patch (1.9K, ../../CAJTYsWXoWWyB6JNAqi2Fpv7DgapM5TbHei8G=UsmWgXkSAccbg@mail.gmail.com/4-v2-0002-Handle-refint-removal-in-cross-version-pg_upgrade-te.patch)
download | inline diff:
From 9c0838ecfe382e255263fc52357dc35b9c517629 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 8 Jun 2026 19:06:31 +0000
Subject: [PATCH v2 2/2] Handle refint removal in cross-version pg_upgrade
tests.
The previous commit removes the refint extension in v20. When cross-
version pg_upgrade tests upgrade from a version that still shipped
refint, the upgraded contrib_regression_spi (and regression_spi)
databases still have the refint extension and its tables (pkeys,
fkeys, fkeys2) in their catalogs, but a fresh v20 install does not.
Without adjustment the dump comparison fails.
Teach AdjustUpgrade.pm to drop the refint extension and its leftover
tables in those databases when the source version is < 20, matching
the existing pattern for prior cross-version test fixtures.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
.../perl/PostgreSQL/Test/AdjustUpgrade.pm | 21 +++++++++++++++++++
1 file changed, 21 insertions(+)
diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index 8a0c6c98a39..18b52d26f3e 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -141,6 +141,27 @@ sub adjust_database_contents
}
}
+ # refint was removed in v20
+ if ($old_version < 20)
+ {
+ if ($dbnames{"contrib_regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'contrib_regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ if ($dbnames{"regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ }
+
# we removed these test-support functions in v18
if ($old_version < 18)
{
--
2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-09 04:46 ` Ayush Tiwari <[email protected]>
2026-06-09 20:42 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-25 11:28 ` Re: Remove the refint contrib module (for v20) solai v <[email protected]>
2026-06-28 08:14 ` Re: Remove the refint contrib module (for v20) Álvaro Herrera <[email protected]>
0 siblings, 3 replies; 15+ messages in thread
From: Ayush Tiwari @ 2026-06-09 04:46 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; Nathan Bossart <[email protected]>; +Cc: pgsql-hackers; Álvaro Herrera <[email protected]>; Tom Lane <[email protected]>
Hi,
On Tue, 9 Jun 2026 at 00:44, Ayush Tiwari <[email protected]>
wrote:
> Hi,
>
> On Sat, 6 Jun 2026 at 03:35, Daniel Gustafsson <[email protected]> wrote:
>
>> > On 5 Jun 2026, at 23:05, Nathan Bossart <[email protected]>
>> wrote:
>> >
>> > On Sat, Jun 06, 2026 at 12:57:56AM +0530, Ayush Tiwari wrote:
>> >> Thoughts? Does this look like the right approach, or is there a
>> >> reason to keep refint around?
>> >
>> > +1. Do we need an appendix-obsolete entry for the docs?
>>
>> Seems like a reasonable thing to add. Once merged, we should probably
>> add (and
>> backpatch) a mention to the docs that the functionality will be removed
>> in v20.
>>
>
> v2 attached. 0001 now adds an appendix-obsolete entry under the same
> contrib-spi-refint id, so existing /current/ documentation links keep
> resolving (mirrors the auth-radius pattern). 0002 is unchanged.
>
> The back-patched "will be removed in v20" notice on v19 (and older)
> I can send it post this merge, as suggested.
>
> Please let me know if I've missed something more. Or if I should
> split v2-0001 into 2 parts, one for removal and the other for appendix-
> obsolete entry.
>
Rebasing.
Regards,
Ayush
Attachments:
[application/octet-stream] v3-0002-Handle-refint-removal-in-cross-version-pg_upgrade-te.patch (1.9K, ../../CAJTYsWWm+67r=yf2bCY9bbriYKaK3q79sWJpgqbHQ3endHe9gA@mail.gmail.com/3-v3-0002-Handle-refint-removal-in-cross-version-pg_upgrade-te.patch)
download | inline diff:
From 291d9c390a86820560a6043c6e8672a82f8b421e Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 8 Jun 2026 19:06:31 +0000
Subject: [PATCH v3 2/2] Handle refint removal in cross-version pg_upgrade
tests.
The previous commit removes the refint extension in v20. When cross-
version pg_upgrade tests upgrade from a version that still shipped
refint, the upgraded contrib_regression_spi (and regression_spi)
databases still have the refint extension and its tables (pkeys,
fkeys, fkeys2) in their catalogs, but a fresh v20 install does not.
Without adjustment the dump comparison fails.
Teach AdjustUpgrade.pm to drop the refint extension and its leftover
tables in those databases when the source version is < 20, matching
the existing pattern for prior cross-version test fixtures.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
.../perl/PostgreSQL/Test/AdjustUpgrade.pm | 21 +++++++++++++++++++
1 file changed, 21 insertions(+)
diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index 8a0c6c98a39..18b52d26f3e 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -141,6 +141,27 @@ sub adjust_database_contents
}
}
+ # refint was removed in v20
+ if ($old_version < 20)
+ {
+ if ($dbnames{"contrib_regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'contrib_regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ if ($dbnames{"regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ }
+
# we removed these test-support functions in v18
if ($old_version < 18)
{
--
2.43.0
[application/octet-stream] v3-0001-Remove-the-refint-contrib-module.patch (39.9K, ../../CAJTYsWWm+67r=yf2bCY9bbriYKaK3q79sWJpgqbHQ3endHe9gA@mail.gmail.com/4-v3-0001-Remove-the-refint-contrib-module.patch)
download | inline diff:
From ef500c6878cd5e3cf97c82711ad97010751a3249 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 8 Jun 2026 19:06:31 +0000
Subject: [PATCH v3 1/2] Remove the refint contrib module.
refint was sample code from the pre-built-in-FK era and has long been
documented as superseded by the built-in foreign key mechanism. Recent
fixes (cascade-UPDATE crash with NULL keys, removal of the broken private
SPI plan cache) made it clear that the code has more issues than its
sample-code value justifies, and there was consensus on the prior thread
to drop it once v20 opens for development.
Remove the C source, control file, SQL script, example file, and
regression test from contrib/spi/. Adjust the contrib/spi Makefile and
meson.build, drop the refint section from contrib-spi.sgml, and drop the
now-unused EPlan typedef from typedefs.list. Add an appendix-obsolete
entry under the same contrib-spi-refint id so existing /current/
documentation links keep resolving.
The other contrib/spi extensions (autoinc, insert_username, moddatetime)
are unaffected.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
contrib/spi/Makefile | 13 +-
contrib/spi/expected/refint.out | 113 -----
contrib/spi/meson.build | 30 --
contrib/spi/refint--1.0.sql | 14 -
contrib/spi/refint.c | 538 ---------------------
contrib/spi/refint.control | 5 -
contrib/spi/refint.example | 82 ----
contrib/spi/sql/refint.sql | 97 ----
doc/src/sgml/appendix-obsolete-refint.sgml | 24 +
doc/src/sgml/appendix-obsolete.sgml | 1 +
doc/src/sgml/contrib-spi.sgml | 107 ----
doc/src/sgml/filelist.sgml | 1 +
src/tools/pgindent/typedefs.list | 1 -
13 files changed, 30 insertions(+), 996 deletions(-)
delete mode 100644 contrib/spi/expected/refint.out
delete mode 100644 contrib/spi/refint--1.0.sql
delete mode 100644 contrib/spi/refint.c
delete mode 100644 contrib/spi/refint.control
delete mode 100644 contrib/spi/refint.example
delete mode 100644 contrib/spi/sql/refint.sql
create mode 100644 doc/src/sgml/appendix-obsolete-refint.sgml
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile
index 7ccbef8c926..805f7653e58 100644
--- a/contrib/spi/Makefile
+++ b/contrib/spi/Makefile
@@ -1,23 +1,18 @@
# contrib/spi/Makefile
-MODULES = autoinc insert_username moddatetime refint
+MODULES = autoinc insert_username moddatetime
-EXTENSION = autoinc insert_username moddatetime refint
+EXTENSION = autoinc insert_username moddatetime
DATA = autoinc--1.0.sql \
insert_username--1.0.sql \
- moddatetime--1.0.sql \
- refint--1.0.sql
+ moddatetime--1.0.sql
PGFILEDESC = "spi - examples of using SPI and triggers"
-REGRESS = autoinc refint
+REGRESS = autoinc
DOCS = $(addsuffix .example, $(MODULES))
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-PG_CPPFLAGS = -DREFINT_VERBOSE
-
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/spi/expected/refint.out b/contrib/spi/expected/refint.out
deleted file mode 100644
index 79633603217..00000000000
--- a/contrib/spi/expected/refint.out
+++ /dev/null
@@ -1,113 +0,0 @@
-CREATE EXTENSION refint;
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
-CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-ERROR: duplicate key value violates unique constraint "pkeys_i"
-DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
- trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
-----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
- check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | |
- check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | |
-(10 rows)
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/contrib/spi/meson.build b/contrib/spi/meson.build
index 4a9a2bef0a5..85f0b2276f7 100644
--- a/contrib/spi/meson.build
+++ b/contrib/spi/meson.build
@@ -79,35 +79,6 @@ install_data('moddatetime.example',
)
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-refint_cflags = ['-DREFINT_VERBOSE']
-
-refint_sources = files(
- 'refint.c',
-)
-
-if host_system == 'windows'
- refint_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
- '--NAME', 'refint',
- '--FILEDESC', 'spi - examples of using SPI and triggers',])
-endif
-
-refint = shared_module('refint',
- refint_sources,
- c_args: refint_cflags,
- kwargs: contrib_mod_args,
-)
-contrib_targets += refint
-
-install_data('refint.control', 'refint--1.0.sql',
- kwargs: contrib_data_args,
-)
-
-install_data('refint.example',
- kwargs: contrib_doc_args,
-)
-
tests += {
'name': 'spi',
'sd': meson.current_source_dir(),
@@ -115,7 +86,6 @@ tests += {
'regress': {
'sql': [
'autoinc',
- 'refint',
],
},
}
diff --git a/contrib/spi/refint--1.0.sql b/contrib/spi/refint--1.0.sql
deleted file mode 100644
index faf797c2157..00000000000
--- a/contrib/spi/refint--1.0.sql
+++ /dev/null
@@ -1,14 +0,0 @@
-/* contrib/spi/refint--1.0.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION refint" to load this file. \quit
-
-CREATE FUNCTION check_primary_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
-CREATE FUNCTION check_foreign_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
diff --git a/contrib/spi/refint.c b/contrib/spi/refint.c
deleted file mode 100644
index 0bfd963fa83..00000000000
--- a/contrib/spi/refint.c
+++ /dev/null
@@ -1,538 +0,0 @@
-/*
- * contrib/spi/refint.c
- *
- *
- * refint.c -- set of functions to define referential integrity
- * constraints using general triggers.
- */
-#include "postgres.h"
-
-#include <ctype.h>
-
-#include "commands/trigger.h"
-#include "executor/spi.h"
-#include "utils/builtins.h"
-#include "utils/rel.h"
-
-PG_MODULE_MAGIC_EXT(
- .name = "refint",
- .version = PG_VERSION
-);
-
-/*
- * check_primary_key () -- check that key in tuple being inserted/updated
- * references existing tuple in "primary" table.
- * Though it's called without args You have to specify referenced
- * table/keys while creating trigger: key field names in triggered table,
- * referenced table name, referenced key field names:
- * EXECUTE PROCEDURE
- * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
- */
-
-PG_FUNCTION_INFO_V1(check_primary_key);
-
-Datum
-check_primary_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: column names and table name */
- int nkeys; /* # of key columns (= nargs / 2) */
- Datum *kvals; /* key values */
- char *relname; /* referenced relation name */
- Relation rel; /* triggered relation */
- HeapTuple tuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr pplan; /* prepared plan */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- int ret;
- int i;
- StringInfoData sql;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_primary_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_primary_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired for row");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired by AFTER trigger");
-
- /* If INSERTion then must check Tuple to being inserted */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- tuple = trigdata->tg_trigtuple;
-
- /* Not should be called for DELETE */
- else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: cannot process DELETE events");
-
- /* If UPDATE, then must check new Tuple, not old one */
- else
- tuple = trigdata->tg_newtuple;
-
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs % 2 != 1) /* odd number of arguments! */
- /* internal error */
- elog(ERROR, "check_primary_key: odd number of arguments should be specified");
-
- nkeys = nargs / 2;
- relname = args[nkeys];
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum(tuple);
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
-
- initStringInfo(&sql);
-
- /*
- * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 = $1
- * [AND Pkey2 = $2 [...]]
- */
- appendStringInfo(&sql, "select 1 from %s where ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args[i + nkeys], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- pfree(sql.data);
-
- /*
- * Ok, execute prepared plan.
- */
- ret = SPI_execp(pplan, kvals, NULL, 1);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
-
- /*
- * If there are no tuples returned by SELECT then ...
- */
- if (SPI_processed == 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("tuple references non-existent key"),
- errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
-
- SPI_finish();
-
- return PointerGetDatum(tuple);
-}
-
-/*
- * check_foreign_key () -- check that key in tuple being deleted/updated
- * is not referenced by tuples in "foreign" table(s).
- * Though it's called without args You have to specify (while creating trigger):
- * number of references, action to do if key referenced
- * ('restrict' | 'setnull' | 'cascade'), key field names in triggered
- * ("primary") table and referencing table(s)/keys:
- * EXECUTE PROCEDURE
- * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
- * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
- */
-
-PG_FUNCTION_INFO_V1(check_foreign_key);
-
-Datum
-check_foreign_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: as described above */
- char **args_temp;
- int nrefs; /* number of references (== # of plans) */
- char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
- int nkeys; /* # of key columns */
- Datum *kvals; /* key values */
- char *relname; /* referencing relation name */
- Relation rel; /* triggered relation */
- HeapTuple trigtuple = NULL; /* tuple to being changed */
- HeapTuple newtuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr *splan; /* prepared plan(s) */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
- int is_update = 0;
- int ret;
- int i,
- r;
- char **args2;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_foreign_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired for row");
-
- /* Not should be called for INSERT */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: cannot process INSERT events");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired by AFTER trigger");
-
- /* Have to check tg_trigtuple - tuple being deleted */
- trigtuple = trigdata->tg_trigtuple;
-
- /*
- * But if this is UPDATE then we have to return tg_newtuple. Also, if key
- * in tg_newtuple is the same as in tg_trigtuple then nothing to do.
- */
- is_update = 0;
- if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
- {
- newtuple = trigdata->tg_newtuple;
- is_update = 1;
- }
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs < 5) /* nrefs, action, key, Relation, key - at
- * least */
- /* internal error */
- elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
-
- nrefs = pg_strtoint32(args[0]);
- if (nrefs < 1)
- /* internal error */
- elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
- action = pg_ascii_tolower((unsigned char) *(args[1]));
- if (action != 'r' && action != 'c' && action != 's')
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
- nargs -= 2;
- args += 2;
- nkeys = (nargs - nrefs) / (nrefs + 1);
- if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
- nargs + 2, nrefs);
-
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
- }
-
- /*
- * If UPDATE then get column value from new tuple being inserted and
- * compare is this the same as old one. For the moment we use string
- * presentation of values...
- */
- if (newtuple != NULL)
- {
- char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
- char *newval;
-
- /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
- if (oldval == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_getvalue returned %s", SPI_result_code_string(SPI_result));
- newval = SPI_getvalue(newtuple, tupdesc, fnumber);
- if (newval == NULL || strcmp(oldval, newval) != 0)
- isequal = false;
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
- args_temp = args;
- nargs -= nkeys;
- args += nkeys;
- args2 = args;
-
- splan = (SPIPlanPtr *) palloc(nrefs * sizeof(SPIPlanPtr));
-
- for (r = 0; r < nrefs; r++)
- {
- StringInfoData sql;
- SPIPlanPtr pplan;
-
- initStringInfo(&sql);
-
- relname = args2[0];
-
- /*---------
- * For 'R'estrict action we construct SELECT query:
- *
- * SELECT 1
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to check is tuple referenced or not.
- *---------
- */
- if (action == 'r')
- appendStringInfo(&sql, "select 1 from %s where ", relname);
-
- /*---------
- * For 'C'ascade action we construct DELETE query
- *
- * DELETE
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to delete all referencing tuples.
- *---------
- */
-
- /*
- * Max : Cascade with UPDATE query i create update query that updates
- * new key values in referenced tables
- */
-
-
- else if (action == 'c')
- {
- if (is_update == 1)
- {
- int fn;
- char *nv;
- int k;
-
- appendStringInfo(&sql, "update %s set ", relname);
- for (k = 1; k <= nkeys; k++)
- {
- fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
- Assert(fn > 0); /* already checked above */
- nv = SPI_getvalue(newtuple, tupdesc, fn);
-
- appendStringInfo(&sql, " %s = %s ",
- args2[k],
- nv ? quote_literal_cstr(nv) : "NULL");
- if (k < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
- else
- /* DELETE */
- appendStringInfo(&sql, "delete from %s where ", relname);
- }
-
- /*
- * For 'S'etnull action we construct UPDATE query - UPDATE
- * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]] WHERE
- * Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in all
- * referencing tuples to NULL.
- */
- else if (action == 's')
- {
- appendStringInfo(&sql, "update %s set ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = null", args2[i]);
- if (i < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
-
- /* Construct WHERE qual */
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args2[i], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- splan[r] = pplan;
-
- args2 += nkeys + 1; /* to the next relation */
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql.data);
-#endif
-
- pfree(sql.data);
- }
-
- /*
- * If UPDATE and key is not changed ...
- */
- if (newtuple != NULL && isequal)
- {
- SPI_finish();
- return PointerGetDatum(newtuple);
- }
-
- /*
- * Ok, execute prepared plan(s).
- */
- for (r = 0; r < nrefs; r++)
- {
- /*
- * For 'R'estrict we may to execute plan for one tuple only, for other
- * actions - for all tuples.
- */
- int tcount = (action == 'r') ? 1 : 0;
-
- relname = args[0];
-
- ret = SPI_execp(splan[r], kvals, NULL, tcount);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("SPI_execp returned %d", ret)));
-
- /* If action is 'R'estrict ... */
- if (action == 'r')
- {
- /* If there is tuple returned by SELECT then ... */
- if (SPI_processed > 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("\"%s\": tuple is referenced in \"%s\"",
- trigger->tgname, relname)));
- }
- else
- {
-#ifdef REFINT_VERBOSE
- const char *operation;
-
- if (action == 'c')
- operation = is_update ? "updated" : "deleted";
- else
- operation = "set to null";
-
- elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
- trigger->tgname, SPI_processed, relname, operation);
-#endif
- }
- args += nkeys + 1; /* to the next relation */
- }
-
- SPI_finish();
-
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
-}
diff --git a/contrib/spi/refint.control b/contrib/spi/refint.control
deleted file mode 100644
index cbede45784c..00000000000
--- a/contrib/spi/refint.control
+++ /dev/null
@@ -1,5 +0,0 @@
-# refint extension
-comment = 'functions for implementing referential integrity (obsolete)'
-default_version = '1.0'
-module_pathname = '$libdir/refint'
-relocatable = true
diff --git a/contrib/spi/refint.example b/contrib/spi/refint.example
deleted file mode 100644
index 299166d5041..00000000000
--- a/contrib/spi/refint.example
+++ /dev/null
@@ -1,82 +0,0 @@
---Column ID of table A is primary key:
-
-CREATE TABLE A (
- ID int4 not null
-);
-CREATE UNIQUE INDEX AI ON A (ID);
-
---Columns REFB of table B and REFC of C are foreign keys referencing ID of A:
-
-CREATE TABLE B (
- REFB int4
-);
-CREATE INDEX BI ON B (REFB);
-
-CREATE TABLE C (
- REFC int4
-);
-CREATE INDEX CI ON C (REFC);
-
---Trigger for table A:
-
-CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
-EXECUTE PROCEDURE
-check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
-/*
-2 - means that check must be performed for foreign keys of 2 tables.
-cascade - defines that corresponding keys must be deleted.
-ID - name of primary key column in triggered table (A). You may
- use as many columns as you need.
-B - name of (first) table with foreign keys.
-REFB - name of foreign key column in this table. You may use as many
- columns as you need, but number of key columns in referenced
- table (A) must be the same.
-C - name of second table with foreign keys.
-REFC - name of foreign key column in this table.
-*/
-
---Trigger for table B:
-
-CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFB', 'A', 'ID');
-
-/*
-REFB - name of foreign key column in triggered (B) table. You may use as
- many columns as you need, but number of key columns in referenced
- table must be the same.
-A - referenced table name.
-ID - name of primary key column in referenced table.
-*/
-
---Trigger for table C:
-
-CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFC', 'A', 'ID');
-
--- Now try
-
-INSERT INTO A VALUES (10);
-INSERT INTO A VALUES (20);
-INSERT INTO A VALUES (30);
-INSERT INTO A VALUES (40);
-INSERT INTO A VALUES (50);
-
-INSERT INTO B VALUES (1); -- invalid reference
-INSERT INTO B VALUES (10);
-INSERT INTO B VALUES (30);
-INSERT INTO B VALUES (30);
-
-INSERT INTO C VALUES (11); -- invalid reference
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (30);
-
-DELETE FROM A WHERE ID = 10;
-DELETE FROM A WHERE ID = 20;
-DELETE FROM A WHERE ID = 30;
-
-SELECT * FROM A;
-SELECT * FROM B;
-SELECT * FROM C;
diff --git a/contrib/spi/sql/refint.sql b/contrib/spi/sql/refint.sql
deleted file mode 100644
index 63458127917..00000000000
--- a/contrib/spi/sql/refint.sql
+++ /dev/null
@@ -1,97 +0,0 @@
-CREATE EXTENSION refint;
-
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
-
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
-
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
-
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
-
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/doc/src/sgml/appendix-obsolete-refint.sgml b/doc/src/sgml/appendix-obsolete-refint.sgml
new file mode 100644
index 00000000000..2ec2029153f
--- /dev/null
+++ b/doc/src/sgml/appendix-obsolete-refint.sgml
@@ -0,0 +1,24 @@
+<!-- doc/src/sgml/appendix-obsolete-refint.sgml -->
+<!--
+ See doc/src/sgml/appendix-obsolete.sgml for why this file exists. Do not change the id attribute.
+-->
+
+<sect1 id="contrib-spi-refint">
+ <title>refint Module Removed</title>
+
+ <indexterm zone="contrib-spi-refint">
+ <primary>refint</primary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> 19 and below shipped a
+ contrib module named <literal>refint</literal> that provided the
+ trigger functions <function>check_primary_key()</function> and
+ <function>check_foreign_key()</function> as an early way to enforce
+ referential integrity. This functionality was long superseded by
+ the built-in foreign key mechanism (see
+ <xref linkend="ddl-constraints-fk"/>), and the module was removed
+ in <productname>PostgreSQL</productname> 20.
+ </para>
+
+</sect1>
diff --git a/doc/src/sgml/appendix-obsolete.sgml b/doc/src/sgml/appendix-obsolete.sgml
index cc002653052..759a65cb5e0 100644
--- a/doc/src/sgml/appendix-obsolete.sgml
+++ b/doc/src/sgml/appendix-obsolete.sgml
@@ -39,5 +39,6 @@
&obsolete-pgresetxlog;
&obsolete-pgreceivexlog;
&obsolete-auth-radius;
+ &obsolete-refint;
</appendix>
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 7e4e580bc74..b585083c6fb 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -24,113 +24,6 @@
separately-installable extension.
</para>
- <sect2 id="contrib-spi-refint">
- <title>refint — Functions for Implementing Referential Integrity</title>
-
- <para>
- <function>check_primary_key()</function> and
- <function>check_foreign_key()</function> are used to check foreign key constraints.
- (This functionality is long since superseded by the built-in foreign
- key mechanism, of course, but the module is still useful as an example.)
- </para>
-
- <note>
- <para>
- <filename>refint</filename> requires a
- <link linkend="ddl-schemas-patterns">secure schema usage pattern</link> and
- data types where the equality operator is named <literal>=</literal>.
- </para>
- </note>
-
- <para>
- <function>check_primary_key()</function> checks the referencing table.
- To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this
- function on a table referencing another table. Specify as the trigger
- arguments: the referencing table's column name(s) which form the foreign
- key, the referenced table name, and the column names in the referenced table
- which form the primary/unique key. To handle multiple foreign
- keys, create a trigger for each reference.
- </para>
-
- <note>
- <para>
- The <emphasis>referenced</emphasis> table name and column name arguments to
- <function>check_primary_key()</function> are copied as-is into internally
- generated SQL statements and therefore must be double-quoted by the user as
- necessary in the <command>CREATE TRIGGER</command> command. See
- <xref linkend="sql-syntax-identifiers"/> for more information about quoting
- SQL identifiers. Conversely, the <emphasis>referencing</emphasis> table
- column name arguments should not be double quoted. See the following mock
- example of proper use of <function>check_primary_key()</function>:
-<programlisting>
-CREATE TRIGGER mytrigger
-AFTER INSERT OR UPDATE ON referencing_table
-FOR EACH ROW EXECUTE PROCEDURE
-check_primary_key (
- 'column A', 'column B', -- referencing table columns
- 'myschema."referenced table"', -- referenced table
- '"column A"', '"column B"' -- referenced table columns
-);
-</programlisting>
- </para>
- </note>
-
- <para>
- <function>check_foreign_key()</function> checks the referenced table.
- To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this
- function on a table referenced by other table(s). Specify as the trigger
- arguments: the number of referencing tables for which the function has to
- perform checking, the action if a referencing key is found
- (<literal>cascade</literal> — to delete the referencing row,
- <literal>restrict</literal> — to abort transaction if referencing keys
- exist, <literal>setnull</literal> — to set referencing key fields to null),
- the referenced table's column names which form the primary/unique key, then
- the referencing table name and column names (repeated for as many
- referencing tables as were specified by first argument). Note that the
- primary/unique key columns should be marked NOT NULL and should have a
- unique index.
- </para>
-
- <note>
- <para>
- The <emphasis>referencing</emphasis> table name and column name arguments
- to <function>check_foreign_key()</function> are copied as-is into
- internally generated SQL statements and therefore must be double-quoted by
- the user as necessary in the <command>CREATE TRIGGER</command> command.
- See <xref linkend="sql-syntax-identifiers"/> for more information about
- quoting SQL identifiers. Conversely, the <emphasis>referenced</emphasis>
- table column name arguments should not be double quoted. See the following
- mock example of proper use of <function>check_foreign_key()</function>:
-<programlisting>
-CREATE TRIGGER mytrigger
-AFTER DELETE OR UPDATE ON referenced_table
-FOR EACH ROW EXECUTE PROCEDURE
-check_foreign_key (
- 1, -- number of referencing tables
- 'cascade', -- action
- 'column A', 'column B', -- referenced table columns
- 'myschema."referencing table"', -- referencing table
- '"column A"', '"column B"' -- referencing table columns
-);
-</programlisting>
- </para>
- </note>
-
- <para>
- Note that if these triggers are executed from
- another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
- example, if a user inserts row1 and then the <literal>BEFORE</literal>
- trigger inserts row2 and calls a trigger with the
- <function>check_foreign_key()</function>,
- the <function>check_foreign_key()</function>
- function will not see row1 and will fail.
- </para>
-
- <para>
- There are examples in <filename>refint.example</filename>.
- </para>
- </sect2>
-
<sect2 id="contrib-spi-autoinc">
<title>autoinc — Functions for Autoincrementing Fields</title>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 25a85082759..082d5565215 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -209,3 +209,4 @@
<!ENTITY obsolete-pgresetxlog SYSTEM "appendix-obsolete-pgresetxlog.sgml">
<!ENTITY obsolete-pgreceivexlog SYSTEM "appendix-obsolete-pgreceivexlog.sgml">
<!ENTITY obsolete-auth-radius SYSTEM "appendix-obsolete-auth-radius.sgml">
+<!ENTITY obsolete-refint SYSTEM "appendix-obsolete-refint.sgml">
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index 8cf40c87043..7903f1640c1 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -723,7 +723,6 @@ ENGINE
EOM_flatten_into_method
EOM_get_flat_size_method
EPQState
-EPlan
EState
EStatus
EVP_CIPHER
--
2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-09 20:42 ` Nathan Bossart <[email protected]>
2 siblings, 0 replies; 15+ messages in thread
From: Nathan Bossart @ 2026-06-09 20:42 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; pgsql-hackers; Álvaro Herrera <[email protected]>; Tom Lane <[email protected]>
On Tue, Jun 09, 2026 at 10:16:37AM +0530, Ayush Tiwari wrote:
>> v2 attached. 0001 now adds an appendix-obsolete entry under the same
>> contrib-spi-refint id, so existing /current/ documentation links keep
>> resolving (mirrors the auth-radius pattern). 0002 is unchanged.
I'd likely combine these two before committing.
>> The back-patched "will be removed in v20" notice on v19 (and older)
>> I can send it post this merge, as suggested.
Thanks.
> Rebasing.
LGTM
--
nathan
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-25 11:28 ` solai v <[email protected]>
2 siblings, 0 replies; 15+ messages in thread
From: solai v @ 2026-06-25 11:28 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Nathan Bossart <[email protected]>; pgsql-hackers; Álvaro Herrera <[email protected]>; Tom Lane <[email protected]>
Hi Ayush,
I tested the v3 patch series on current HEAD.
For v3-0001, i verified that the refint extension files were removed
from contrib/spi, the related build system entries were updated, and
PostgreSQL built successfully after applying the patch. I also
confirmed that CREATE EXTENSION refint fails in a fresh database as
expected once the extension is removed.
For v3-0002, i verified that the patch applied cleanly and reviewed
the changes in AdjustUpgrade.pm. The added handling for upgrades from
versions older than v20, including dropping the refint extension and
its associated test tables during upgrade testing, looks correct. The
tree also built successfully with this patch applied.
I did not perform an actual cross-version pg_upgrade test, but the
patch series applied cleanly and behaved as expected in my testing.
Regards,
solai
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-28 08:14 ` Álvaro Herrera <[email protected]>
2026-06-28 18:59 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2 siblings, 1 reply; 15+ messages in thread
From: Álvaro Herrera @ 2026-06-28 08:14 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Daniel Gustafsson <[email protected]>; Nathan Bossart <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>
Hello,
The doc patch says that Postgres 19 shipped a contrib module named
refint, but that's not fully correct. It shipped an *extension* named
refint inside the contrib module named spi. I think that wording could
be improved.
Regards
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-28 08:14 ` Re: Remove the refint contrib module (for v20) Álvaro Herrera <[email protected]>
@ 2026-06-28 18:59 ` Daniel Gustafsson <[email protected]>
2026-06-29 04:41 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Daniel Gustafsson @ 2026-06-28 18:59 UTC (permalink / raw)
To: Álvaro Herrera <[email protected]>; +Cc: Ayush Tiwari <[email protected]>; Nathan Bossart <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>
> On 28 Jun 2026, at 10:14, Álvaro Herrera <[email protected]> wrote:
> The doc patch says that Postgres 19 shipped a contrib module named
> refint, but that's not fully correct. It shipped an *extension* named
> refint inside the contrib module named spi. I think that wording could
> be improved.
Agreed.
+ trigger functions <function>check_primary_key()</function> and
We also typically don't append parentheses to function names in the docs.
--
Daniel Gustafsson
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-28 08:14 ` Re: Remove the refint contrib module (for v20) Álvaro Herrera <[email protected]>
2026-06-28 18:59 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
@ 2026-06-29 04:41 ` Ayush Tiwari <[email protected]>
2026-06-29 08:02 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Ayush Tiwari @ 2026-06-29 04:41 UTC (permalink / raw)
To: Daniel Gustafsson <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>
Hi,
On Mon, 29 Jun 2026 at 00:29, Daniel Gustafsson <[email protected]> wrote:
> > On 28 Jun 2026, at 10:14, Álvaro Herrera <[email protected]> wrote:
>
> > The doc patch says that Postgres 19 shipped a contrib module named
> > refint, but that's not fully correct. It shipped an *extension* named
> > refint inside the contrib module named spi. I think that wording could
> > be improved.
>
> Agreed.
>
> + trigger functions <function>check_primary_key()</function> and
>
> We also typically don't append parentheses to function names in the docs.
>
Thanks for the review!
Attaching patch with improved wording and parentheses
changes.
Basic git diff between v3 and v4:
- <title>refint Module Removed</title>
+ <title>refint Extension Removed</title>
- PostgreSQL 19 and below shipped a
- contrib module named refint that provided the
- trigger functions check_primary_key() and
- check_foreign_key() as an early way to enforce
+ PostgreSQL 19 and below shipped an
+ extension named refint, part of the
+ spi contrib module, providing the trigger
+ functions check_primary_key and
+ check_foreign_key as an early way to enforce
- ...), and the module was removed
+ ...), and the extension was removed
Regards,
Ayush
Attachments:
[application/octet-stream] v4-0001-Remove-the-refint-contrib-module.patch (39.9K, ../../CAJTYsWVofHXC_uPoE_qUCprH=RseLkNHbJyHDH5Jfxf02AErCA@mail.gmail.com/3-v4-0001-Remove-the-refint-contrib-module.patch)
download | inline diff:
From b921256d4d7da72c835523bd5fe98e551d9c43a7 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 29 Jun 2026 04:27:14 +0000
Subject: [PATCH v4 1/2] Remove the refint contrib module.
refint was sample code from the pre-built-in-FK era and has long been
documented as superseded by the built-in foreign key mechanism. Recent
fixes (cascade-UPDATE crash with NULL keys, removal of the broken private
SPI plan cache) made it clear that the code has more issues than its
sample-code value justifies, and there was consensus on the prior thread
to drop it once v20 opens for development.
Remove the C source, control file, SQL script, example file, and
regression test from contrib/spi/. Adjust the contrib/spi Makefile and
meson.build, drop the refint section from contrib-spi.sgml, and drop the
now-unused EPlan typedef from typedefs.list. Add an appendix-obsolete
entry under the same contrib-spi-refint id so existing /current/
documentation links keep resolving.
The other contrib/spi extensions (autoinc, insert_username, moddatetime)
are unaffected.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
contrib/spi/Makefile | 13 +-
contrib/spi/expected/refint.out | 113 -----
contrib/spi/meson.build | 30 --
contrib/spi/refint--1.0.sql | 14 -
contrib/spi/refint.c | 538 ---------------------
contrib/spi/refint.control | 5 -
contrib/spi/refint.example | 82 ----
contrib/spi/sql/refint.sql | 97 ----
doc/src/sgml/appendix-obsolete-refint.sgml | 25 +
doc/src/sgml/appendix-obsolete.sgml | 1 +
doc/src/sgml/contrib-spi.sgml | 107 ----
doc/src/sgml/filelist.sgml | 1 +
src/tools/pgindent/typedefs.list | 1 -
13 files changed, 31 insertions(+), 996 deletions(-)
delete mode 100644 contrib/spi/expected/refint.out
delete mode 100644 contrib/spi/refint--1.0.sql
delete mode 100644 contrib/spi/refint.c
delete mode 100644 contrib/spi/refint.control
delete mode 100644 contrib/spi/refint.example
delete mode 100644 contrib/spi/sql/refint.sql
create mode 100644 doc/src/sgml/appendix-obsolete-refint.sgml
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile
index 7ccbef8c926..805f7653e58 100644
--- a/contrib/spi/Makefile
+++ b/contrib/spi/Makefile
@@ -1,23 +1,18 @@
# contrib/spi/Makefile
-MODULES = autoinc insert_username moddatetime refint
+MODULES = autoinc insert_username moddatetime
-EXTENSION = autoinc insert_username moddatetime refint
+EXTENSION = autoinc insert_username moddatetime
DATA = autoinc--1.0.sql \
insert_username--1.0.sql \
- moddatetime--1.0.sql \
- refint--1.0.sql
+ moddatetime--1.0.sql
PGFILEDESC = "spi - examples of using SPI and triggers"
-REGRESS = autoinc refint
+REGRESS = autoinc
DOCS = $(addsuffix .example, $(MODULES))
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-PG_CPPFLAGS = -DREFINT_VERBOSE
-
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/spi/expected/refint.out b/contrib/spi/expected/refint.out
deleted file mode 100644
index 79633603217..00000000000
--- a/contrib/spi/expected/refint.out
+++ /dev/null
@@ -1,113 +0,0 @@
-CREATE EXTENSION refint;
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
-CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-ERROR: duplicate key value violates unique constraint "pkeys_i"
-DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
- trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
-----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
- check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | |
- check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | |
-(10 rows)
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/contrib/spi/meson.build b/contrib/spi/meson.build
index 4a9a2bef0a5..85f0b2276f7 100644
--- a/contrib/spi/meson.build
+++ b/contrib/spi/meson.build
@@ -79,35 +79,6 @@ install_data('moddatetime.example',
)
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-refint_cflags = ['-DREFINT_VERBOSE']
-
-refint_sources = files(
- 'refint.c',
-)
-
-if host_system == 'windows'
- refint_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
- '--NAME', 'refint',
- '--FILEDESC', 'spi - examples of using SPI and triggers',])
-endif
-
-refint = shared_module('refint',
- refint_sources,
- c_args: refint_cflags,
- kwargs: contrib_mod_args,
-)
-contrib_targets += refint
-
-install_data('refint.control', 'refint--1.0.sql',
- kwargs: contrib_data_args,
-)
-
-install_data('refint.example',
- kwargs: contrib_doc_args,
-)
-
tests += {
'name': 'spi',
'sd': meson.current_source_dir(),
@@ -115,7 +86,6 @@ tests += {
'regress': {
'sql': [
'autoinc',
- 'refint',
],
},
}
diff --git a/contrib/spi/refint--1.0.sql b/contrib/spi/refint--1.0.sql
deleted file mode 100644
index faf797c2157..00000000000
--- a/contrib/spi/refint--1.0.sql
+++ /dev/null
@@ -1,14 +0,0 @@
-/* contrib/spi/refint--1.0.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION refint" to load this file. \quit
-
-CREATE FUNCTION check_primary_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
-CREATE FUNCTION check_foreign_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
diff --git a/contrib/spi/refint.c b/contrib/spi/refint.c
deleted file mode 100644
index 0bfd963fa83..00000000000
--- a/contrib/spi/refint.c
+++ /dev/null
@@ -1,538 +0,0 @@
-/*
- * contrib/spi/refint.c
- *
- *
- * refint.c -- set of functions to define referential integrity
- * constraints using general triggers.
- */
-#include "postgres.h"
-
-#include <ctype.h>
-
-#include "commands/trigger.h"
-#include "executor/spi.h"
-#include "utils/builtins.h"
-#include "utils/rel.h"
-
-PG_MODULE_MAGIC_EXT(
- .name = "refint",
- .version = PG_VERSION
-);
-
-/*
- * check_primary_key () -- check that key in tuple being inserted/updated
- * references existing tuple in "primary" table.
- * Though it's called without args You have to specify referenced
- * table/keys while creating trigger: key field names in triggered table,
- * referenced table name, referenced key field names:
- * EXECUTE PROCEDURE
- * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
- */
-
-PG_FUNCTION_INFO_V1(check_primary_key);
-
-Datum
-check_primary_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: column names and table name */
- int nkeys; /* # of key columns (= nargs / 2) */
- Datum *kvals; /* key values */
- char *relname; /* referenced relation name */
- Relation rel; /* triggered relation */
- HeapTuple tuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr pplan; /* prepared plan */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- int ret;
- int i;
- StringInfoData sql;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_primary_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_primary_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired for row");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired by AFTER trigger");
-
- /* If INSERTion then must check Tuple to being inserted */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- tuple = trigdata->tg_trigtuple;
-
- /* Not should be called for DELETE */
- else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: cannot process DELETE events");
-
- /* If UPDATE, then must check new Tuple, not old one */
- else
- tuple = trigdata->tg_newtuple;
-
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs % 2 != 1) /* odd number of arguments! */
- /* internal error */
- elog(ERROR, "check_primary_key: odd number of arguments should be specified");
-
- nkeys = nargs / 2;
- relname = args[nkeys];
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum(tuple);
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
-
- initStringInfo(&sql);
-
- /*
- * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 = $1
- * [AND Pkey2 = $2 [...]]
- */
- appendStringInfo(&sql, "select 1 from %s where ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args[i + nkeys], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- pfree(sql.data);
-
- /*
- * Ok, execute prepared plan.
- */
- ret = SPI_execp(pplan, kvals, NULL, 1);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
-
- /*
- * If there are no tuples returned by SELECT then ...
- */
- if (SPI_processed == 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("tuple references non-existent key"),
- errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
-
- SPI_finish();
-
- return PointerGetDatum(tuple);
-}
-
-/*
- * check_foreign_key () -- check that key in tuple being deleted/updated
- * is not referenced by tuples in "foreign" table(s).
- * Though it's called without args You have to specify (while creating trigger):
- * number of references, action to do if key referenced
- * ('restrict' | 'setnull' | 'cascade'), key field names in triggered
- * ("primary") table and referencing table(s)/keys:
- * EXECUTE PROCEDURE
- * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
- * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
- */
-
-PG_FUNCTION_INFO_V1(check_foreign_key);
-
-Datum
-check_foreign_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: as described above */
- char **args_temp;
- int nrefs; /* number of references (== # of plans) */
- char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
- int nkeys; /* # of key columns */
- Datum *kvals; /* key values */
- char *relname; /* referencing relation name */
- Relation rel; /* triggered relation */
- HeapTuple trigtuple = NULL; /* tuple to being changed */
- HeapTuple newtuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr *splan; /* prepared plan(s) */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
- int is_update = 0;
- int ret;
- int i,
- r;
- char **args2;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_foreign_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired for row");
-
- /* Not should be called for INSERT */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: cannot process INSERT events");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired by AFTER trigger");
-
- /* Have to check tg_trigtuple - tuple being deleted */
- trigtuple = trigdata->tg_trigtuple;
-
- /*
- * But if this is UPDATE then we have to return tg_newtuple. Also, if key
- * in tg_newtuple is the same as in tg_trigtuple then nothing to do.
- */
- is_update = 0;
- if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
- {
- newtuple = trigdata->tg_newtuple;
- is_update = 1;
- }
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs < 5) /* nrefs, action, key, Relation, key - at
- * least */
- /* internal error */
- elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
-
- nrefs = pg_strtoint32(args[0]);
- if (nrefs < 1)
- /* internal error */
- elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
- action = pg_ascii_tolower((unsigned char) *(args[1]));
- if (action != 'r' && action != 'c' && action != 's')
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
- nargs -= 2;
- args += 2;
- nkeys = (nargs - nrefs) / (nrefs + 1);
- if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
- nargs + 2, nrefs);
-
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
- }
-
- /*
- * If UPDATE then get column value from new tuple being inserted and
- * compare is this the same as old one. For the moment we use string
- * presentation of values...
- */
- if (newtuple != NULL)
- {
- char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
- char *newval;
-
- /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
- if (oldval == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_getvalue returned %s", SPI_result_code_string(SPI_result));
- newval = SPI_getvalue(newtuple, tupdesc, fnumber);
- if (newval == NULL || strcmp(oldval, newval) != 0)
- isequal = false;
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
- args_temp = args;
- nargs -= nkeys;
- args += nkeys;
- args2 = args;
-
- splan = (SPIPlanPtr *) palloc(nrefs * sizeof(SPIPlanPtr));
-
- for (r = 0; r < nrefs; r++)
- {
- StringInfoData sql;
- SPIPlanPtr pplan;
-
- initStringInfo(&sql);
-
- relname = args2[0];
-
- /*---------
- * For 'R'estrict action we construct SELECT query:
- *
- * SELECT 1
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to check is tuple referenced or not.
- *---------
- */
- if (action == 'r')
- appendStringInfo(&sql, "select 1 from %s where ", relname);
-
- /*---------
- * For 'C'ascade action we construct DELETE query
- *
- * DELETE
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to delete all referencing tuples.
- *---------
- */
-
- /*
- * Max : Cascade with UPDATE query i create update query that updates
- * new key values in referenced tables
- */
-
-
- else if (action == 'c')
- {
- if (is_update == 1)
- {
- int fn;
- char *nv;
- int k;
-
- appendStringInfo(&sql, "update %s set ", relname);
- for (k = 1; k <= nkeys; k++)
- {
- fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
- Assert(fn > 0); /* already checked above */
- nv = SPI_getvalue(newtuple, tupdesc, fn);
-
- appendStringInfo(&sql, " %s = %s ",
- args2[k],
- nv ? quote_literal_cstr(nv) : "NULL");
- if (k < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
- else
- /* DELETE */
- appendStringInfo(&sql, "delete from %s where ", relname);
- }
-
- /*
- * For 'S'etnull action we construct UPDATE query - UPDATE
- * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]] WHERE
- * Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in all
- * referencing tuples to NULL.
- */
- else if (action == 's')
- {
- appendStringInfo(&sql, "update %s set ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = null", args2[i]);
- if (i < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
-
- /* Construct WHERE qual */
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args2[i], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- splan[r] = pplan;
-
- args2 += nkeys + 1; /* to the next relation */
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql.data);
-#endif
-
- pfree(sql.data);
- }
-
- /*
- * If UPDATE and key is not changed ...
- */
- if (newtuple != NULL && isequal)
- {
- SPI_finish();
- return PointerGetDatum(newtuple);
- }
-
- /*
- * Ok, execute prepared plan(s).
- */
- for (r = 0; r < nrefs; r++)
- {
- /*
- * For 'R'estrict we may to execute plan for one tuple only, for other
- * actions - for all tuples.
- */
- int tcount = (action == 'r') ? 1 : 0;
-
- relname = args[0];
-
- ret = SPI_execp(splan[r], kvals, NULL, tcount);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("SPI_execp returned %d", ret)));
-
- /* If action is 'R'estrict ... */
- if (action == 'r')
- {
- /* If there is tuple returned by SELECT then ... */
- if (SPI_processed > 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("\"%s\": tuple is referenced in \"%s\"",
- trigger->tgname, relname)));
- }
- else
- {
-#ifdef REFINT_VERBOSE
- const char *operation;
-
- if (action == 'c')
- operation = is_update ? "updated" : "deleted";
- else
- operation = "set to null";
-
- elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
- trigger->tgname, SPI_processed, relname, operation);
-#endif
- }
- args += nkeys + 1; /* to the next relation */
- }
-
- SPI_finish();
-
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
-}
diff --git a/contrib/spi/refint.control b/contrib/spi/refint.control
deleted file mode 100644
index cbede45784c..00000000000
--- a/contrib/spi/refint.control
+++ /dev/null
@@ -1,5 +0,0 @@
-# refint extension
-comment = 'functions for implementing referential integrity (obsolete)'
-default_version = '1.0'
-module_pathname = '$libdir/refint'
-relocatable = true
diff --git a/contrib/spi/refint.example b/contrib/spi/refint.example
deleted file mode 100644
index 299166d5041..00000000000
--- a/contrib/spi/refint.example
+++ /dev/null
@@ -1,82 +0,0 @@
---Column ID of table A is primary key:
-
-CREATE TABLE A (
- ID int4 not null
-);
-CREATE UNIQUE INDEX AI ON A (ID);
-
---Columns REFB of table B and REFC of C are foreign keys referencing ID of A:
-
-CREATE TABLE B (
- REFB int4
-);
-CREATE INDEX BI ON B (REFB);
-
-CREATE TABLE C (
- REFC int4
-);
-CREATE INDEX CI ON C (REFC);
-
---Trigger for table A:
-
-CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
-EXECUTE PROCEDURE
-check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
-/*
-2 - means that check must be performed for foreign keys of 2 tables.
-cascade - defines that corresponding keys must be deleted.
-ID - name of primary key column in triggered table (A). You may
- use as many columns as you need.
-B - name of (first) table with foreign keys.
-REFB - name of foreign key column in this table. You may use as many
- columns as you need, but number of key columns in referenced
- table (A) must be the same.
-C - name of second table with foreign keys.
-REFC - name of foreign key column in this table.
-*/
-
---Trigger for table B:
-
-CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFB', 'A', 'ID');
-
-/*
-REFB - name of foreign key column in triggered (B) table. You may use as
- many columns as you need, but number of key columns in referenced
- table must be the same.
-A - referenced table name.
-ID - name of primary key column in referenced table.
-*/
-
---Trigger for table C:
-
-CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFC', 'A', 'ID');
-
--- Now try
-
-INSERT INTO A VALUES (10);
-INSERT INTO A VALUES (20);
-INSERT INTO A VALUES (30);
-INSERT INTO A VALUES (40);
-INSERT INTO A VALUES (50);
-
-INSERT INTO B VALUES (1); -- invalid reference
-INSERT INTO B VALUES (10);
-INSERT INTO B VALUES (30);
-INSERT INTO B VALUES (30);
-
-INSERT INTO C VALUES (11); -- invalid reference
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (30);
-
-DELETE FROM A WHERE ID = 10;
-DELETE FROM A WHERE ID = 20;
-DELETE FROM A WHERE ID = 30;
-
-SELECT * FROM A;
-SELECT * FROM B;
-SELECT * FROM C;
diff --git a/contrib/spi/sql/refint.sql b/contrib/spi/sql/refint.sql
deleted file mode 100644
index 63458127917..00000000000
--- a/contrib/spi/sql/refint.sql
+++ /dev/null
@@ -1,97 +0,0 @@
-CREATE EXTENSION refint;
-
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
-
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
-
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
-
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
-
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/doc/src/sgml/appendix-obsolete-refint.sgml b/doc/src/sgml/appendix-obsolete-refint.sgml
new file mode 100644
index 00000000000..c024ff4839e
--- /dev/null
+++ b/doc/src/sgml/appendix-obsolete-refint.sgml
@@ -0,0 +1,25 @@
+<!-- doc/src/sgml/appendix-obsolete-refint.sgml -->
+<!--
+ See doc/src/sgml/appendix-obsolete.sgml for why this file exists. Do not change the id attribute.
+-->
+
+<sect1 id="contrib-spi-refint">
+ <title>refint Extension Removed</title>
+
+ <indexterm zone="contrib-spi-refint">
+ <primary>refint</primary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> 19 and below shipped an
+ extension named <literal>refint</literal>, part of the
+ <literal>spi</literal> contrib module, providing the trigger
+ functions <function>check_primary_key</function> and
+ <function>check_foreign_key</function> as an early way to enforce
+ referential integrity. This functionality was long superseded by
+ the built-in foreign key mechanism (see
+ <xref linkend="ddl-constraints-fk"/>), and the extension was removed
+ in <productname>PostgreSQL</productname> 20.
+ </para>
+
+</sect1>
diff --git a/doc/src/sgml/appendix-obsolete.sgml b/doc/src/sgml/appendix-obsolete.sgml
index cc002653052..759a65cb5e0 100644
--- a/doc/src/sgml/appendix-obsolete.sgml
+++ b/doc/src/sgml/appendix-obsolete.sgml
@@ -39,5 +39,6 @@
&obsolete-pgresetxlog;
&obsolete-pgreceivexlog;
&obsolete-auth-radius;
+ &obsolete-refint;
</appendix>
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 7e4e580bc74..b585083c6fb 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -24,113 +24,6 @@
separately-installable extension.
</para>
- <sect2 id="contrib-spi-refint">
- <title>refint — Functions for Implementing Referential Integrity</title>
-
- <para>
- <function>check_primary_key()</function> and
- <function>check_foreign_key()</function> are used to check foreign key constraints.
- (This functionality is long since superseded by the built-in foreign
- key mechanism, of course, but the module is still useful as an example.)
- </para>
-
- <note>
- <para>
- <filename>refint</filename> requires a
- <link linkend="ddl-schemas-patterns">secure schema usage pattern</link> and
- data types where the equality operator is named <literal>=</literal>.
- </para>
- </note>
-
- <para>
- <function>check_primary_key()</function> checks the referencing table.
- To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this
- function on a table referencing another table. Specify as the trigger
- arguments: the referencing table's column name(s) which form the foreign
- key, the referenced table name, and the column names in the referenced table
- which form the primary/unique key. To handle multiple foreign
- keys, create a trigger for each reference.
- </para>
-
- <note>
- <para>
- The <emphasis>referenced</emphasis> table name and column name arguments to
- <function>check_primary_key()</function> are copied as-is into internally
- generated SQL statements and therefore must be double-quoted by the user as
- necessary in the <command>CREATE TRIGGER</command> command. See
- <xref linkend="sql-syntax-identifiers"/> for more information about quoting
- SQL identifiers. Conversely, the <emphasis>referencing</emphasis> table
- column name arguments should not be double quoted. See the following mock
- example of proper use of <function>check_primary_key()</function>:
-<programlisting>
-CREATE TRIGGER mytrigger
-AFTER INSERT OR UPDATE ON referencing_table
-FOR EACH ROW EXECUTE PROCEDURE
-check_primary_key (
- 'column A', 'column B', -- referencing table columns
- 'myschema."referenced table"', -- referenced table
- '"column A"', '"column B"' -- referenced table columns
-);
-</programlisting>
- </para>
- </note>
-
- <para>
- <function>check_foreign_key()</function> checks the referenced table.
- To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this
- function on a table referenced by other table(s). Specify as the trigger
- arguments: the number of referencing tables for which the function has to
- perform checking, the action if a referencing key is found
- (<literal>cascade</literal> — to delete the referencing row,
- <literal>restrict</literal> — to abort transaction if referencing keys
- exist, <literal>setnull</literal> — to set referencing key fields to null),
- the referenced table's column names which form the primary/unique key, then
- the referencing table name and column names (repeated for as many
- referencing tables as were specified by first argument). Note that the
- primary/unique key columns should be marked NOT NULL and should have a
- unique index.
- </para>
-
- <note>
- <para>
- The <emphasis>referencing</emphasis> table name and column name arguments
- to <function>check_foreign_key()</function> are copied as-is into
- internally generated SQL statements and therefore must be double-quoted by
- the user as necessary in the <command>CREATE TRIGGER</command> command.
- See <xref linkend="sql-syntax-identifiers"/> for more information about
- quoting SQL identifiers. Conversely, the <emphasis>referenced</emphasis>
- table column name arguments should not be double quoted. See the following
- mock example of proper use of <function>check_foreign_key()</function>:
-<programlisting>
-CREATE TRIGGER mytrigger
-AFTER DELETE OR UPDATE ON referenced_table
-FOR EACH ROW EXECUTE PROCEDURE
-check_foreign_key (
- 1, -- number of referencing tables
- 'cascade', -- action
- 'column A', 'column B', -- referenced table columns
- 'myschema."referencing table"', -- referencing table
- '"column A"', '"column B"' -- referencing table columns
-);
-</programlisting>
- </para>
- </note>
-
- <para>
- Note that if these triggers are executed from
- another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
- example, if a user inserts row1 and then the <literal>BEFORE</literal>
- trigger inserts row2 and calls a trigger with the
- <function>check_foreign_key()</function>,
- the <function>check_foreign_key()</function>
- function will not see row1 and will fail.
- </para>
-
- <para>
- There are examples in <filename>refint.example</filename>.
- </para>
- </sect2>
-
<sect2 id="contrib-spi-autoinc">
<title>autoinc — Functions for Autoincrementing Fields</title>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 25a85082759..082d5565215 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -209,3 +209,4 @@
<!ENTITY obsolete-pgresetxlog SYSTEM "appendix-obsolete-pgresetxlog.sgml">
<!ENTITY obsolete-pgreceivexlog SYSTEM "appendix-obsolete-pgreceivexlog.sgml">
<!ENTITY obsolete-auth-radius SYSTEM "appendix-obsolete-auth-radius.sgml">
+<!ENTITY obsolete-refint SYSTEM "appendix-obsolete-refint.sgml">
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..ee7f539ecfb 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -723,7 +723,6 @@ ENGINE
EOM_flatten_into_method
EOM_get_flat_size_method
EPQState
-EPlan
EState
EStatus
EVP_CIPHER
--
2.43.0
[application/octet-stream] v4-0002-Handle-refint-removal-in-cross-version-pg_upgrade.patch (1.9K, ../../CAJTYsWVofHXC_uPoE_qUCprH=RseLkNHbJyHDH5Jfxf02AErCA@mail.gmail.com/4-v4-0002-Handle-refint-removal-in-cross-version-pg_upgrade.patch)
download | inline diff:
From 1fb5d171db77154ca1b6ebaf1bfca91f33734f8d Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 29 Jun 2026 04:27:14 +0000
Subject: [PATCH v4 2/2] Handle refint removal in cross-version pg_upgrade
tests.
The previous commit removes the refint extension in v20. When cross-
version pg_upgrade tests upgrade from a version that still shipped
refint, the upgraded contrib_regression_spi (and regression_spi)
databases still have the refint extension and its tables (pkeys,
fkeys, fkeys2) in their catalogs, but a fresh v20 install does not.
Without adjustment the dump comparison fails.
Teach AdjustUpgrade.pm to drop the refint extension and its leftover
tables in those databases when the source version is < 20, matching
the existing pattern for prior cross-version test fixtures.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
.../perl/PostgreSQL/Test/AdjustUpgrade.pm | 21 +++++++++++++++++++
1 file changed, 21 insertions(+)
diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index dbddd41bfca..216e4d55fb1 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -141,6 +141,27 @@ sub adjust_database_contents
}
}
+ # refint was removed in v20
+ if ($old_version < 20)
+ {
+ if ($dbnames{"contrib_regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'contrib_regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ if ($dbnames{"regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ }
+
# we removed these test-support functions in v18
if ($old_version < 18)
{
--
2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-28 08:14 ` Re: Remove the refint contrib module (for v20) Álvaro Herrera <[email protected]>
2026-06-28 18:59 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-29 04:41 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-29 08:02 ` Ayush Tiwari <[email protected]>
2026-07-08 21:24 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
0 siblings, 1 reply; 15+ messages in thread
From: Ayush Tiwari @ 2026-06-29 08:02 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>
Hi,
On Mon, 29 Jun 2026 at 11:53, Fujii Masao <[email protected]> wrote:
> On Sat, Jun 6, 2026 at 4:28 AM Ayush Tiwari <[email protected]>
> wrote:
> > 0002 (test-only) teaches AdjustUpgrade.pm to drop the refint
> > extension and its leftover tables (pkeys, fkeys, fkeys2) from
> > contrib_regression_spi / regression_spi when upgrading from a version
> > that still shipped refint, so cross-version pg_upgrade tests don't
> > fail dump comparison once v20 opens.
> > I followed the existing < 19 btree_gist block style.
>
> In make-based contrib tests with USE_MODULE_DB=1, contrib/spi uses
> contrib_regression_autoinc database, since autoinc is the first entry
> in MODULES. So, in 0002 patch, shouldn't we also remove
> the refint-related objects from contrib_regression_autoinc?
>
Thanks for the review, Fujii-san!
Yes, you are right, I missed removing that in 0002 patch.
v5-0001 is identical to v4-0001.
Adding you suggested changes, in v5.
Regards,
Ayush
>
Attachments:
[application/octet-stream] v5-0001-Remove-the-refint-contrib-module.patch (39.9K, ../../CAJTYsWVMX_csjBafWngAqAAXBa=+Uy+rY2Je4jM6CJ11tF38Mw@mail.gmail.com/3-v5-0001-Remove-the-refint-contrib-module.patch)
download | inline diff:
From 29a6e2fa3ab45d704ccedf3cd00ff62854d82a07 Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 29 Jun 2026 04:27:14 +0000
Subject: [PATCH v5 v5 1/2] Remove the refint contrib module.
refint was sample code from the pre-built-in-FK era and has long been
documented as superseded by the built-in foreign key mechanism. Recent
fixes (cascade-UPDATE crash with NULL keys, removal of the broken private
SPI plan cache) made it clear that the code has more issues than its
sample-code value justifies, and there was consensus on the prior thread
to drop it once v20 opens for development.
Remove the C source, control file, SQL script, example file, and
regression test from contrib/spi/. Adjust the contrib/spi Makefile and
meson.build, drop the refint section from contrib-spi.sgml, and drop the
now-unused EPlan typedef from typedefs.list. Add an appendix-obsolete
entry under the same contrib-spi-refint id so existing /current/
documentation links keep resolving.
The other contrib/spi extensions (autoinc, insert_username, moddatetime)
are unaffected.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
contrib/spi/Makefile | 13 +-
contrib/spi/expected/refint.out | 113 -----
contrib/spi/meson.build | 30 --
contrib/spi/refint--1.0.sql | 14 -
contrib/spi/refint.c | 538 ---------------------
contrib/spi/refint.control | 5 -
contrib/spi/refint.example | 82 ----
contrib/spi/sql/refint.sql | 97 ----
doc/src/sgml/appendix-obsolete-refint.sgml | 25 +
doc/src/sgml/appendix-obsolete.sgml | 1 +
doc/src/sgml/contrib-spi.sgml | 107 ----
doc/src/sgml/filelist.sgml | 1 +
src/tools/pgindent/typedefs.list | 1 -
13 files changed, 31 insertions(+), 996 deletions(-)
delete mode 100644 contrib/spi/expected/refint.out
delete mode 100644 contrib/spi/refint--1.0.sql
delete mode 100644 contrib/spi/refint.c
delete mode 100644 contrib/spi/refint.control
delete mode 100644 contrib/spi/refint.example
delete mode 100644 contrib/spi/sql/refint.sql
create mode 100644 doc/src/sgml/appendix-obsolete-refint.sgml
diff --git a/contrib/spi/Makefile b/contrib/spi/Makefile
index 7ccbef8c926..805f7653e58 100644
--- a/contrib/spi/Makefile
+++ b/contrib/spi/Makefile
@@ -1,23 +1,18 @@
# contrib/spi/Makefile
-MODULES = autoinc insert_username moddatetime refint
+MODULES = autoinc insert_username moddatetime
-EXTENSION = autoinc insert_username moddatetime refint
+EXTENSION = autoinc insert_username moddatetime
DATA = autoinc--1.0.sql \
insert_username--1.0.sql \
- moddatetime--1.0.sql \
- refint--1.0.sql
+ moddatetime--1.0.sql
PGFILEDESC = "spi - examples of using SPI and triggers"
-REGRESS = autoinc refint
+REGRESS = autoinc
DOCS = $(addsuffix .example, $(MODULES))
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-PG_CPPFLAGS = -DREFINT_VERBOSE
-
ifdef USE_PGXS
PG_CONFIG = pg_config
PGXS := $(shell $(PG_CONFIG) --pgxs)
diff --git a/contrib/spi/expected/refint.out b/contrib/spi/expected/refint.out
deleted file mode 100644
index 79633603217..00000000000
--- a/contrib/spi/expected/refint.out
+++ /dev/null
@@ -1,113 +0,0 @@
-CREATE EXTENSION refint;
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys2_pkey_exist" found tuple referencing non-existent key in "pkeys".
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey_exist" found tuple referencing non-existent key in "pkeys".
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-ERROR: tuple references non-existent key
-DETAIL: Trigger "check_fkeys_pkey2_exist" found tuple referencing non-existent key in "fkeys2".
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-ERROR: "check_fkeys2_fkey_restrict": tuple is referenced in "fkeys"
-CONTEXT: SQL statement "delete from fkeys2 where fkey21 = $1 and fkey22 = $2 "
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are deleted
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are deleted
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys are updated
-NOTICE: check_pkeys_fkey_cascade: 1 tuple(s) of fkeys2 are updated
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-ERROR: duplicate key value violates unique constraint "pkeys_i"
-DETAIL: Key (pkey1, pkey2)=(7, 70) already exists.
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
- trigger_name | event_manipulation | event_object_schema | event_object_table | action_order | action_condition | action_orientation | action_timing | action_reference_old_table | action_reference_new_table
-----------------------------+--------------------+---------------------+--------------------+--------------+------------------+--------------------+---------------+----------------------------+----------------------------
- check_fkeys2_fkey_restrict | DELETE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_fkey_restrict | UPDATE | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | INSERT | public | fkeys2 | 1 | | ROW | AFTER | |
- check_fkeys2_pkey_exist | UPDATE | public | fkeys2 | 2 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | INSERT | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey2_exist | UPDATE | public | fkeys | 1 | | ROW | AFTER | |
- check_fkeys_pkey_exist | INSERT | public | fkeys | 2 | | ROW | AFTER | |
- check_fkeys_pkey_exist | UPDATE | public | fkeys | 2 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | DELETE | public | pkeys | 1 | | ROW | AFTER | |
- check_pkeys_fkey_cascade | UPDATE | public | pkeys | 1 | | ROW | AFTER | |
-(10 rows)
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/contrib/spi/meson.build b/contrib/spi/meson.build
index 4a9a2bef0a5..85f0b2276f7 100644
--- a/contrib/spi/meson.build
+++ b/contrib/spi/meson.build
@@ -79,35 +79,6 @@ install_data('moddatetime.example',
)
-# this is needed for the regression tests;
-# comment out if you want a quieter refint package for other uses
-refint_cflags = ['-DREFINT_VERBOSE']
-
-refint_sources = files(
- 'refint.c',
-)
-
-if host_system == 'windows'
- refint_sources += rc_lib_gen.process(win32ver_rc, extra_args: [
- '--NAME', 'refint',
- '--FILEDESC', 'spi - examples of using SPI and triggers',])
-endif
-
-refint = shared_module('refint',
- refint_sources,
- c_args: refint_cflags,
- kwargs: contrib_mod_args,
-)
-contrib_targets += refint
-
-install_data('refint.control', 'refint--1.0.sql',
- kwargs: contrib_data_args,
-)
-
-install_data('refint.example',
- kwargs: contrib_doc_args,
-)
-
tests += {
'name': 'spi',
'sd': meson.current_source_dir(),
@@ -115,7 +86,6 @@ tests += {
'regress': {
'sql': [
'autoinc',
- 'refint',
],
},
}
diff --git a/contrib/spi/refint--1.0.sql b/contrib/spi/refint--1.0.sql
deleted file mode 100644
index faf797c2157..00000000000
--- a/contrib/spi/refint--1.0.sql
+++ /dev/null
@@ -1,14 +0,0 @@
-/* contrib/spi/refint--1.0.sql */
-
--- complain if script is sourced in psql, rather than via CREATE EXTENSION
-\echo Use "CREATE EXTENSION refint" to load this file. \quit
-
-CREATE FUNCTION check_primary_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
-
-CREATE FUNCTION check_foreign_key()
-RETURNS trigger
-AS 'MODULE_PATHNAME'
-LANGUAGE C;
diff --git a/contrib/spi/refint.c b/contrib/spi/refint.c
deleted file mode 100644
index 0bfd963fa83..00000000000
--- a/contrib/spi/refint.c
+++ /dev/null
@@ -1,538 +0,0 @@
-/*
- * contrib/spi/refint.c
- *
- *
- * refint.c -- set of functions to define referential integrity
- * constraints using general triggers.
- */
-#include "postgres.h"
-
-#include <ctype.h>
-
-#include "commands/trigger.h"
-#include "executor/spi.h"
-#include "utils/builtins.h"
-#include "utils/rel.h"
-
-PG_MODULE_MAGIC_EXT(
- .name = "refint",
- .version = PG_VERSION
-);
-
-/*
- * check_primary_key () -- check that key in tuple being inserted/updated
- * references existing tuple in "primary" table.
- * Though it's called without args You have to specify referenced
- * table/keys while creating trigger: key field names in triggered table,
- * referenced table name, referenced key field names:
- * EXECUTE PROCEDURE
- * check_primary_key ('Fkey1', 'Fkey2', 'Ptable', 'Pkey1', 'Pkey2').
- */
-
-PG_FUNCTION_INFO_V1(check_primary_key);
-
-Datum
-check_primary_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: column names and table name */
- int nkeys; /* # of key columns (= nargs / 2) */
- Datum *kvals; /* key values */
- char *relname; /* referenced relation name */
- Relation rel; /* triggered relation */
- HeapTuple tuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr pplan; /* prepared plan */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- int ret;
- int i;
- StringInfoData sql;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_primary_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_primary_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired for row");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: must be fired by AFTER trigger");
-
- /* If INSERTion then must check Tuple to being inserted */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- tuple = trigdata->tg_trigtuple;
-
- /* Not should be called for DELETE */
- else if (TRIGGER_FIRED_BY_DELETE(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_primary_key: cannot process DELETE events");
-
- /* If UPDATE, then must check new Tuple, not old one */
- else
- tuple = trigdata->tg_newtuple;
-
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs % 2 != 1) /* odd number of arguments! */
- /* internal error */
- elog(ERROR, "check_primary_key: odd number of arguments should be specified");
-
- nkeys = nargs / 2;
- relname = args[nkeys];
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(tuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum(tuple);
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
-
- initStringInfo(&sql);
-
- /*
- * Construct query: SELECT 1 FROM _referenced_relation_ WHERE Pkey1 = $1
- * [AND Pkey2 = $2 [...]]
- */
- appendStringInfo(&sql, "select 1 from %s where ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args[i + nkeys], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- pfree(sql.data);
-
- /*
- * Ok, execute prepared plan.
- */
- ret = SPI_execp(pplan, kvals, NULL, 1);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- /* internal error */
- elog(ERROR, "check_primary_key: SPI_execp returned %d", ret);
-
- /*
- * If there are no tuples returned by SELECT then ...
- */
- if (SPI_processed == 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("tuple references non-existent key"),
- errdetail("Trigger \"%s\" found tuple referencing non-existent key in \"%s\".", trigger->tgname, relname)));
-
- SPI_finish();
-
- return PointerGetDatum(tuple);
-}
-
-/*
- * check_foreign_key () -- check that key in tuple being deleted/updated
- * is not referenced by tuples in "foreign" table(s).
- * Though it's called without args You have to specify (while creating trigger):
- * number of references, action to do if key referenced
- * ('restrict' | 'setnull' | 'cascade'), key field names in triggered
- * ("primary") table and referencing table(s)/keys:
- * EXECUTE PROCEDURE
- * check_foreign_key (2, 'restrict', 'Pkey1', 'Pkey2',
- * 'Ftable1', 'Fkey11', 'Fkey12', 'Ftable2', 'Fkey21', 'Fkey22').
- */
-
-PG_FUNCTION_INFO_V1(check_foreign_key);
-
-Datum
-check_foreign_key(PG_FUNCTION_ARGS)
-{
- TriggerData *trigdata = (TriggerData *) fcinfo->context;
- Trigger *trigger; /* to get trigger name */
- int nargs; /* # of args specified in CREATE TRIGGER */
- char **args; /* arguments: as described above */
- char **args_temp;
- int nrefs; /* number of references (== # of plans) */
- char action; /* 'R'estrict | 'S'etnull | 'C'ascade */
- int nkeys; /* # of key columns */
- Datum *kvals; /* key values */
- char *relname; /* referencing relation name */
- Relation rel; /* triggered relation */
- HeapTuple trigtuple = NULL; /* tuple to being changed */
- HeapTuple newtuple = NULL; /* tuple to return */
- TupleDesc tupdesc; /* tuple description */
- SPIPlanPtr *splan; /* prepared plan(s) */
- Oid *argtypes = NULL; /* key types to prepare execution plan */
- bool isnull; /* to know is some column NULL or not */
- bool isequal = true; /* are keys in both tuples equal (in UPDATE) */
- int is_update = 0;
- int ret;
- int i,
- r;
- char **args2;
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key: Enter Function");
-#endif
-
- /*
- * Some checks first...
- */
-
- /* Called by trigger manager ? */
- if (!CALLED_AS_TRIGGER(fcinfo))
- /* internal error */
- elog(ERROR, "check_foreign_key: not fired by trigger manager");
-
- /* Should be called for ROW trigger */
- if (!TRIGGER_FIRED_FOR_ROW(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired for row");
-
- /* Not should be called for INSERT */
- if (TRIGGER_FIRED_BY_INSERT(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: cannot process INSERT events");
-
- if (!TRIGGER_FIRED_AFTER(trigdata->tg_event))
- /* internal error */
- elog(ERROR, "check_foreign_key: must be fired by AFTER trigger");
-
- /* Have to check tg_trigtuple - tuple being deleted */
- trigtuple = trigdata->tg_trigtuple;
-
- /*
- * But if this is UPDATE then we have to return tg_newtuple. Also, if key
- * in tg_newtuple is the same as in tg_trigtuple then nothing to do.
- */
- is_update = 0;
- if (TRIGGER_FIRED_BY_UPDATE(trigdata->tg_event))
- {
- newtuple = trigdata->tg_newtuple;
- is_update = 1;
- }
- trigger = trigdata->tg_trigger;
- nargs = trigger->tgnargs;
- args = trigger->tgargs;
-
- if (nargs < 5) /* nrefs, action, key, Relation, key - at
- * least */
- /* internal error */
- elog(ERROR, "check_foreign_key: too short %d (< 5) list of arguments", nargs);
-
- nrefs = pg_strtoint32(args[0]);
- if (nrefs < 1)
- /* internal error */
- elog(ERROR, "check_foreign_key: %d (< 1) number of references specified", nrefs);
- action = pg_ascii_tolower((unsigned char) *(args[1]));
- if (action != 'r' && action != 'c' && action != 's')
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid action %s", args[1]);
- nargs -= 2;
- args += 2;
- nkeys = (nargs - nrefs) / (nrefs + 1);
- if (nkeys <= 0 || nargs != (nrefs + nkeys * (nrefs + 1)))
- /* internal error */
- elog(ERROR, "check_foreign_key: invalid number of arguments %d for %d references",
- nargs + 2, nrefs);
-
- rel = trigdata->tg_relation;
- tupdesc = rel->rd_att;
-
- /* Connect to SPI manager */
- SPI_connect();
-
- /*
- * We use SPI plan preparation feature, so allocate space to place key
- * values.
- */
- kvals = (Datum *) palloc(nkeys * sizeof(Datum));
-
- /* allocate argtypes for preparation */
- argtypes = (Oid *) palloc(nkeys * sizeof(Oid));
-
- /* For each column in key ... */
- for (i = 0; i < nkeys; i++)
- {
- /* get index of column in tuple */
- int fnumber = SPI_fnumber(tupdesc, args[i]);
-
- /* Bad guys may give us un-existing column in CREATE TRIGGER */
- if (fnumber <= 0)
- ereport(ERROR,
- (errcode(ERRCODE_UNDEFINED_COLUMN),
- errmsg("there is no attribute \"%s\" in relation \"%s\"",
- args[i], SPI_getrelname(rel))));
-
- /* Well, get binary (in internal format) value of column */
- kvals[i] = SPI_getbinval(trigtuple, tupdesc, fnumber, &isnull);
-
- /*
- * If it's NULL then nothing to do! DON'T FORGET call SPI_finish ()!
- * DON'T FORGET return tuple! Executor inserts tuple you're returning!
- * If you return NULL then nothing will be inserted!
- */
- if (isnull)
- {
- SPI_finish();
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
- }
-
- /*
- * If UPDATE then get column value from new tuple being inserted and
- * compare is this the same as old one. For the moment we use string
- * presentation of values...
- */
- if (newtuple != NULL)
- {
- char *oldval = SPI_getvalue(trigtuple, tupdesc, fnumber);
- char *newval;
-
- /* this shouldn't happen! SPI_ERROR_NOOUTFUNC ? */
- if (oldval == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_getvalue returned %s", SPI_result_code_string(SPI_result));
- newval = SPI_getvalue(newtuple, tupdesc, fnumber);
- if (newval == NULL || strcmp(oldval, newval) != 0)
- isequal = false;
- }
-
- /* Get typeId of column */
- argtypes[i] = SPI_gettypeid(tupdesc, fnumber);
- }
- args_temp = args;
- nargs -= nkeys;
- args += nkeys;
- args2 = args;
-
- splan = (SPIPlanPtr *) palloc(nrefs * sizeof(SPIPlanPtr));
-
- for (r = 0; r < nrefs; r++)
- {
- StringInfoData sql;
- SPIPlanPtr pplan;
-
- initStringInfo(&sql);
-
- relname = args2[0];
-
- /*---------
- * For 'R'estrict action we construct SELECT query:
- *
- * SELECT 1
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to check is tuple referenced or not.
- *---------
- */
- if (action == 'r')
- appendStringInfo(&sql, "select 1 from %s where ", relname);
-
- /*---------
- * For 'C'ascade action we construct DELETE query
- *
- * DELETE
- * FROM _referencing_relation_
- * WHERE Fkey1 = $1 [AND Fkey2 = $2 [...]]
- *
- * to delete all referencing tuples.
- *---------
- */
-
- /*
- * Max : Cascade with UPDATE query i create update query that updates
- * new key values in referenced tables
- */
-
-
- else if (action == 'c')
- {
- if (is_update == 1)
- {
- int fn;
- char *nv;
- int k;
-
- appendStringInfo(&sql, "update %s set ", relname);
- for (k = 1; k <= nkeys; k++)
- {
- fn = SPI_fnumber(tupdesc, args_temp[k - 1]);
- Assert(fn > 0); /* already checked above */
- nv = SPI_getvalue(newtuple, tupdesc, fn);
-
- appendStringInfo(&sql, " %s = %s ",
- args2[k],
- nv ? quote_literal_cstr(nv) : "NULL");
- if (k < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
- else
- /* DELETE */
- appendStringInfo(&sql, "delete from %s where ", relname);
- }
-
- /*
- * For 'S'etnull action we construct UPDATE query - UPDATE
- * _referencing_relation_ SET Fkey1 null [, Fkey2 null [...]] WHERE
- * Fkey1 = $1 [AND Fkey2 = $2 [...]] - to set key columns in all
- * referencing tuples to NULL.
- */
- else if (action == 's')
- {
- appendStringInfo(&sql, "update %s set ", relname);
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = null", args2[i]);
- if (i < nkeys)
- appendStringInfoString(&sql, ", ");
- }
- appendStringInfoString(&sql, " where ");
- }
-
- /* Construct WHERE qual */
- for (i = 1; i <= nkeys; i++)
- {
- appendStringInfo(&sql, "%s = $%d ", args2[i], i);
- if (i < nkeys)
- appendStringInfoString(&sql, "and ");
- }
-
- /* Prepare plan for query */
- pplan = SPI_prepare(sql.data, nkeys, argtypes);
- if (pplan == NULL)
- /* internal error */
- elog(ERROR, "check_foreign_key: SPI_prepare returned %s", SPI_result_code_string(SPI_result));
-
- splan[r] = pplan;
-
- args2 += nkeys + 1; /* to the next relation */
-
-#ifdef DEBUG_QUERY
- elog(DEBUG4, "check_foreign_key Debug Query is : %s ", sql.data);
-#endif
-
- pfree(sql.data);
- }
-
- /*
- * If UPDATE and key is not changed ...
- */
- if (newtuple != NULL && isequal)
- {
- SPI_finish();
- return PointerGetDatum(newtuple);
- }
-
- /*
- * Ok, execute prepared plan(s).
- */
- for (r = 0; r < nrefs; r++)
- {
- /*
- * For 'R'estrict we may to execute plan for one tuple only, for other
- * actions - for all tuples.
- */
- int tcount = (action == 'r') ? 1 : 0;
-
- relname = args[0];
-
- ret = SPI_execp(splan[r], kvals, NULL, tcount);
- /* we have no NULLs - so we pass ^^^^ here */
-
- if (ret < 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("SPI_execp returned %d", ret)));
-
- /* If action is 'R'estrict ... */
- if (action == 'r')
- {
- /* If there is tuple returned by SELECT then ... */
- if (SPI_processed > 0)
- ereport(ERROR,
- (errcode(ERRCODE_TRIGGERED_ACTION_EXCEPTION),
- errmsg("\"%s\": tuple is referenced in \"%s\"",
- trigger->tgname, relname)));
- }
- else
- {
-#ifdef REFINT_VERBOSE
- const char *operation;
-
- if (action == 'c')
- operation = is_update ? "updated" : "deleted";
- else
- operation = "set to null";
-
- elog(NOTICE, "%s: " UINT64_FORMAT " tuple(s) of %s are %s",
- trigger->tgname, SPI_processed, relname, operation);
-#endif
- }
- args += nkeys + 1; /* to the next relation */
- }
-
- SPI_finish();
-
- return PointerGetDatum((newtuple == NULL) ? trigtuple : newtuple);
-}
diff --git a/contrib/spi/refint.control b/contrib/spi/refint.control
deleted file mode 100644
index cbede45784c..00000000000
--- a/contrib/spi/refint.control
+++ /dev/null
@@ -1,5 +0,0 @@
-# refint extension
-comment = 'functions for implementing referential integrity (obsolete)'
-default_version = '1.0'
-module_pathname = '$libdir/refint'
-relocatable = true
diff --git a/contrib/spi/refint.example b/contrib/spi/refint.example
deleted file mode 100644
index 299166d5041..00000000000
--- a/contrib/spi/refint.example
+++ /dev/null
@@ -1,82 +0,0 @@
---Column ID of table A is primary key:
-
-CREATE TABLE A (
- ID int4 not null
-);
-CREATE UNIQUE INDEX AI ON A (ID);
-
---Columns REFB of table B and REFC of C are foreign keys referencing ID of A:
-
-CREATE TABLE B (
- REFB int4
-);
-CREATE INDEX BI ON B (REFB);
-
-CREATE TABLE C (
- REFC int4
-);
-CREATE INDEX CI ON C (REFC);
-
---Trigger for table A:
-
-CREATE TRIGGER AT BEFORE DELETE OR UPDATE ON A FOR EACH ROW
-EXECUTE PROCEDURE
-check_foreign_key (2, 'cascade', 'ID', 'B', 'REFB', 'C', 'REFC');
-/*
-2 - means that check must be performed for foreign keys of 2 tables.
-cascade - defines that corresponding keys must be deleted.
-ID - name of primary key column in triggered table (A). You may
- use as many columns as you need.
-B - name of (first) table with foreign keys.
-REFB - name of foreign key column in this table. You may use as many
- columns as you need, but number of key columns in referenced
- table (A) must be the same.
-C - name of second table with foreign keys.
-REFC - name of foreign key column in this table.
-*/
-
---Trigger for table B:
-
-CREATE TRIGGER BT BEFORE INSERT OR UPDATE ON B FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFB', 'A', 'ID');
-
-/*
-REFB - name of foreign key column in triggered (B) table. You may use as
- many columns as you need, but number of key columns in referenced
- table must be the same.
-A - referenced table name.
-ID - name of primary key column in referenced table.
-*/
-
---Trigger for table C:
-
-CREATE TRIGGER CT BEFORE INSERT OR UPDATE ON C FOR EACH ROW
-EXECUTE PROCEDURE
-check_primary_key ('REFC', 'A', 'ID');
-
--- Now try
-
-INSERT INTO A VALUES (10);
-INSERT INTO A VALUES (20);
-INSERT INTO A VALUES (30);
-INSERT INTO A VALUES (40);
-INSERT INTO A VALUES (50);
-
-INSERT INTO B VALUES (1); -- invalid reference
-INSERT INTO B VALUES (10);
-INSERT INTO B VALUES (30);
-INSERT INTO B VALUES (30);
-
-INSERT INTO C VALUES (11); -- invalid reference
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (20);
-INSERT INTO C VALUES (30);
-
-DELETE FROM A WHERE ID = 10;
-DELETE FROM A WHERE ID = 20;
-DELETE FROM A WHERE ID = 30;
-
-SELECT * FROM A;
-SELECT * FROM B;
-SELECT * FROM C;
diff --git a/contrib/spi/sql/refint.sql b/contrib/spi/sql/refint.sql
deleted file mode 100644
index 63458127917..00000000000
--- a/contrib/spi/sql/refint.sql
+++ /dev/null
@@ -1,97 +0,0 @@
-CREATE EXTENSION refint;
-
-create table pkeys (pkey1 int4 not null, pkey2 text not null);
-create table fkeys (fkey1 int4, fkey2 text, fkey3 int);
-create table fkeys2 (fkey21 int4, fkey22 text, pkey23 int not null);
-
-create index fkeys_i on fkeys (fkey1, fkey2);
-create index fkeys2_i on fkeys2 (fkey21, fkey22);
-create index fkeys2p_i on fkeys2 (pkey23);
-
-insert into pkeys values (10, '1');
-insert into pkeys values (20, '2');
-insert into pkeys values (30, '3');
-insert into pkeys values (40, '4');
-insert into pkeys values (50, '5');
-insert into pkeys values (60, '6');
-create unique index pkeys_i on pkeys (pkey1, pkey2);
-
---
--- For fkeys:
--- (fkey1, fkey2) --> pkeys (pkey1, pkey2)
--- (fkey3) --> fkeys2 (pkey23)
---
-create trigger check_fkeys_pkey_exist
- after insert or update on fkeys
- for each row
- execute function
- check_primary_key ('fkey1', 'fkey2', 'pkeys', 'pkey1', 'pkey2');
-
-create trigger check_fkeys_pkey2_exist
- after insert or update on fkeys
- for each row
- execute function check_primary_key ('fkey3', 'fkeys2', 'pkey23');
-
---
--- For fkeys2:
--- (fkey21, fkey22) --> pkeys (pkey1, pkey2)
---
-create trigger check_fkeys2_pkey_exist
- after insert or update on fkeys2
- for each row
- execute procedure
- check_primary_key ('fkey21', 'fkey22', 'pkeys', 'pkey1', 'pkey2');
-
---
--- For pkeys:
--- ON DELETE/UPDATE (pkey1, pkey2) CASCADE:
--- fkeys (fkey1, fkey2) and fkeys2 (fkey21, fkey22)
---
-create trigger check_pkeys_fkey_cascade
- after delete or update on pkeys
- for each row
- execute procedure
- check_foreign_key (2, 'cascade', 'pkey1', 'pkey2',
- 'fkeys', 'fkey1', 'fkey2', 'fkeys2', 'fkey21', 'fkey22');
-
---
--- For fkeys2:
--- ON DELETE/UPDATE (pkey23) RESTRICT:
--- fkeys (fkey3)
---
-create trigger check_fkeys2_fkey_restrict
- after delete or update on fkeys2
- for each row
- execute procedure check_foreign_key (1, 'restrict', 'pkey23', 'fkeys', 'fkey3');
-
-insert into fkeys2 values (10, '1', 1);
-insert into fkeys2 values (30, '3', 2);
-insert into fkeys2 values (40, '4', 5);
-insert into fkeys2 values (50, '5', 3);
--- no key in pkeys
-insert into fkeys2 values (70, '5', 3);
-
-insert into fkeys values (10, '1', 2);
-insert into fkeys values (30, '3', 3);
-insert into fkeys values (40, '4', 2);
-insert into fkeys values (50, '5', 2);
--- no key in pkeys
-insert into fkeys values (70, '5', 1);
--- no key in fkeys2
-insert into fkeys values (60, '6', 4);
-
-delete from pkeys where pkey1 = 30 and pkey2 = '3';
-delete from pkeys where pkey1 = 40 and pkey2 = '4';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 50 and pkey2 = '5';
-update pkeys set pkey1 = 7, pkey2 = '70' where pkey1 = 10 and pkey2 = '1';
-
-SELECT trigger_name, event_manipulation, event_object_schema, event_object_table,
- action_order, action_condition, action_orientation, action_timing,
- action_reference_old_table, action_reference_new_table
- FROM information_schema.triggers
- WHERE event_object_table in ('pkeys', 'fkeys', 'fkeys2')
- ORDER BY trigger_name COLLATE "C", 2;
-
-DROP TABLE pkeys;
-DROP TABLE fkeys;
-DROP TABLE fkeys2;
diff --git a/doc/src/sgml/appendix-obsolete-refint.sgml b/doc/src/sgml/appendix-obsolete-refint.sgml
new file mode 100644
index 00000000000..c024ff4839e
--- /dev/null
+++ b/doc/src/sgml/appendix-obsolete-refint.sgml
@@ -0,0 +1,25 @@
+<!-- doc/src/sgml/appendix-obsolete-refint.sgml -->
+<!--
+ See doc/src/sgml/appendix-obsolete.sgml for why this file exists. Do not change the id attribute.
+-->
+
+<sect1 id="contrib-spi-refint">
+ <title>refint Extension Removed</title>
+
+ <indexterm zone="contrib-spi-refint">
+ <primary>refint</primary>
+ </indexterm>
+
+ <para>
+ <productname>PostgreSQL</productname> 19 and below shipped an
+ extension named <literal>refint</literal>, part of the
+ <literal>spi</literal> contrib module, providing the trigger
+ functions <function>check_primary_key</function> and
+ <function>check_foreign_key</function> as an early way to enforce
+ referential integrity. This functionality was long superseded by
+ the built-in foreign key mechanism (see
+ <xref linkend="ddl-constraints-fk"/>), and the extension was removed
+ in <productname>PostgreSQL</productname> 20.
+ </para>
+
+</sect1>
diff --git a/doc/src/sgml/appendix-obsolete.sgml b/doc/src/sgml/appendix-obsolete.sgml
index cc002653052..759a65cb5e0 100644
--- a/doc/src/sgml/appendix-obsolete.sgml
+++ b/doc/src/sgml/appendix-obsolete.sgml
@@ -39,5 +39,6 @@
&obsolete-pgresetxlog;
&obsolete-pgreceivexlog;
&obsolete-auth-radius;
+ &obsolete-refint;
</appendix>
diff --git a/doc/src/sgml/contrib-spi.sgml b/doc/src/sgml/contrib-spi.sgml
index 7e4e580bc74..b585083c6fb 100644
--- a/doc/src/sgml/contrib-spi.sgml
+++ b/doc/src/sgml/contrib-spi.sgml
@@ -24,113 +24,6 @@
separately-installable extension.
</para>
- <sect2 id="contrib-spi-refint">
- <title>refint — Functions for Implementing Referential Integrity</title>
-
- <para>
- <function>check_primary_key()</function> and
- <function>check_foreign_key()</function> are used to check foreign key constraints.
- (This functionality is long since superseded by the built-in foreign
- key mechanism, of course, but the module is still useful as an example.)
- </para>
-
- <note>
- <para>
- <filename>refint</filename> requires a
- <link linkend="ddl-schemas-patterns">secure schema usage pattern</link> and
- data types where the equality operator is named <literal>=</literal>.
- </para>
- </note>
-
- <para>
- <function>check_primary_key()</function> checks the referencing table.
- To use, create an <literal>AFTER INSERT OR UPDATE</literal> trigger using this
- function on a table referencing another table. Specify as the trigger
- arguments: the referencing table's column name(s) which form the foreign
- key, the referenced table name, and the column names in the referenced table
- which form the primary/unique key. To handle multiple foreign
- keys, create a trigger for each reference.
- </para>
-
- <note>
- <para>
- The <emphasis>referenced</emphasis> table name and column name arguments to
- <function>check_primary_key()</function> are copied as-is into internally
- generated SQL statements and therefore must be double-quoted by the user as
- necessary in the <command>CREATE TRIGGER</command> command. See
- <xref linkend="sql-syntax-identifiers"/> for more information about quoting
- SQL identifiers. Conversely, the <emphasis>referencing</emphasis> table
- column name arguments should not be double quoted. See the following mock
- example of proper use of <function>check_primary_key()</function>:
-<programlisting>
-CREATE TRIGGER mytrigger
-AFTER INSERT OR UPDATE ON referencing_table
-FOR EACH ROW EXECUTE PROCEDURE
-check_primary_key (
- 'column A', 'column B', -- referencing table columns
- 'myschema."referenced table"', -- referenced table
- '"column A"', '"column B"' -- referenced table columns
-);
-</programlisting>
- </para>
- </note>
-
- <para>
- <function>check_foreign_key()</function> checks the referenced table.
- To use, create an <literal>AFTER DELETE OR UPDATE</literal> trigger using this
- function on a table referenced by other table(s). Specify as the trigger
- arguments: the number of referencing tables for which the function has to
- perform checking, the action if a referencing key is found
- (<literal>cascade</literal> — to delete the referencing row,
- <literal>restrict</literal> — to abort transaction if referencing keys
- exist, <literal>setnull</literal> — to set referencing key fields to null),
- the referenced table's column names which form the primary/unique key, then
- the referencing table name and column names (repeated for as many
- referencing tables as were specified by first argument). Note that the
- primary/unique key columns should be marked NOT NULL and should have a
- unique index.
- </para>
-
- <note>
- <para>
- The <emphasis>referencing</emphasis> table name and column name arguments
- to <function>check_foreign_key()</function> are copied as-is into
- internally generated SQL statements and therefore must be double-quoted by
- the user as necessary in the <command>CREATE TRIGGER</command> command.
- See <xref linkend="sql-syntax-identifiers"/> for more information about
- quoting SQL identifiers. Conversely, the <emphasis>referenced</emphasis>
- table column name arguments should not be double quoted. See the following
- mock example of proper use of <function>check_foreign_key()</function>:
-<programlisting>
-CREATE TRIGGER mytrigger
-AFTER DELETE OR UPDATE ON referenced_table
-FOR EACH ROW EXECUTE PROCEDURE
-check_foreign_key (
- 1, -- number of referencing tables
- 'cascade', -- action
- 'column A', 'column B', -- referenced table columns
- 'myschema."referencing table"', -- referencing table
- '"column A"', '"column B"' -- referencing table columns
-);
-</programlisting>
- </para>
- </note>
-
- <para>
- Note that if these triggers are executed from
- another <literal>BEFORE</literal> trigger, they can fail unexpectedly. For
- example, if a user inserts row1 and then the <literal>BEFORE</literal>
- trigger inserts row2 and calls a trigger with the
- <function>check_foreign_key()</function>,
- the <function>check_foreign_key()</function>
- function will not see row1 and will fail.
- </para>
-
- <para>
- There are examples in <filename>refint.example</filename>.
- </para>
- </sect2>
-
<sect2 id="contrib-spi-autoinc">
<title>autoinc — Functions for Autoincrementing Fields</title>
diff --git a/doc/src/sgml/filelist.sgml b/doc/src/sgml/filelist.sgml
index 25a85082759..082d5565215 100644
--- a/doc/src/sgml/filelist.sgml
+++ b/doc/src/sgml/filelist.sgml
@@ -209,3 +209,4 @@
<!ENTITY obsolete-pgresetxlog SYSTEM "appendix-obsolete-pgresetxlog.sgml">
<!ENTITY obsolete-pgreceivexlog SYSTEM "appendix-obsolete-pgreceivexlog.sgml">
<!ENTITY obsolete-auth-radius SYSTEM "appendix-obsolete-auth-radius.sgml">
+<!ENTITY obsolete-refint SYSTEM "appendix-obsolete-refint.sgml">
diff --git a/src/tools/pgindent/typedefs.list b/src/tools/pgindent/typedefs.list
index c5db6ca6705..ee7f539ecfb 100644
--- a/src/tools/pgindent/typedefs.list
+++ b/src/tools/pgindent/typedefs.list
@@ -723,7 +723,6 @@ ENGINE
EOM_flatten_into_method
EOM_get_flat_size_method
EPQState
-EPlan
EState
EStatus
EVP_CIPHER
--
2.43.0
[application/octet-stream] v5-0002-Handle-refint-removal-in-cross-version-pg_upgrade.patch (2.1K, ../../CAJTYsWVMX_csjBafWngAqAAXBa=+Uy+rY2Je4jM6CJ11tF38Mw@mail.gmail.com/4-v5-0002-Handle-refint-removal-in-cross-version-pg_upgrade.patch)
download | inline diff:
From e98f538dfc9ff89749771b4e8b67e731670ec21b Mon Sep 17 00:00:00 2001
From: Ayush Tiwari <[email protected]>
Date: Mon, 29 Jun 2026 04:27:14 +0000
Subject: [PATCH v5 v5 2/2] Handle refint removal in cross-version pg_upgrade
tests.
The previous commit removes the refint extension in v20. When cross-
version pg_upgrade tests upgrade from a version that still shipped
refint, the upgraded contrib_regression_spi (and regression_spi)
databases still have the refint extension and its tables (pkeys,
fkeys, fkeys2) in their catalogs, but a fresh v20 install does not.
Without adjustment the dump comparison fails.
Teach AdjustUpgrade.pm to drop the refint extension and its leftover
tables in those databases when the source version is < 20, matching
the existing pattern for prior cross-version test fixtures.
Discussion: https://postgr.es/m/CAJTYsWUHq8Ohc6-N-xamOPYz-q3qUYMtwQX-1%3DZi%3D5N1Q_GSEQ%40mail.gmail.com
---
.../perl/PostgreSQL/Test/AdjustUpgrade.pm | 29 +++++++++++++++++++
1 file changed, 29 insertions(+)
diff --git a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
index dbddd41bfca..1cb08bfa55a 100644
--- a/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
+++ b/src/test/perl/PostgreSQL/Test/AdjustUpgrade.pm
@@ -141,6 +141,35 @@ sub adjust_database_contents
}
}
+ # refint was removed in v20
+ if ($old_version < 20)
+ {
+ if ($dbnames{"contrib_regression_autoinc"})
+ {
+ _add_st(
+ $result,
+ 'contrib_regression_autoinc',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ if ($dbnames{"contrib_regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'contrib_regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ if ($dbnames{"regression_spi"})
+ {
+ _add_st(
+ $result,
+ 'regression_spi',
+ 'drop extension if exists refint cascade',
+ 'drop table if exists pkeys, fkeys, fkeys2');
+ }
+ }
+
# we removed these test-support functions in v18
if ($old_version < 18)
{
--
2.43.0
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Re: Remove the refint contrib module (for v20) Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-28 08:14 ` Re: Remove the refint contrib module (for v20) Álvaro Herrera <[email protected]>
2026-06-28 18:59 ` Re: Remove the refint contrib module (for v20) Daniel Gustafsson <[email protected]>
2026-06-29 04:41 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-29 08:02 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-07-08 21:24 ` Nathan Bossart <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Nathan Bossart @ 2026-07-08 21:24 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: Fujii Masao <[email protected]>; Álvaro Herrera <[email protected]>; pgsql-hackers; Tom Lane <[email protected]>; Daniel Gustafsson <[email protected]>
Committed.
--
nathan
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-05 22:09 ` Tom Lane <[email protected]>
2 siblings, 0 replies; 15+ messages in thread
From: Tom Lane @ 2026-06-05 22:09 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-hackers; Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>
Ayush Tiwari <[email protected]> writes:
> Thoughts? Does this look like the right approach, or is there a
> reason to keep refint around?
The only reason for refint to exist anymore is as example code,
but I think it's well short of our current standards, so it
really would need more work if we want to keep it. Some things
I'd want to see worked on if we keep it:
* The comments are not great
* Error handling is haphazard, eg inconsistent mix of elog and ereport
* After the cache-ectomy, it's hardly clear why it's still using
SPI_prepare rather than just SPI_execute_with_args
I doubt that anyone really wants to put in that work, so I'm
good with just removing it.
regards, tom lane
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
@ 2026-06-29 06:23 ` Fujii Masao <[email protected]>
2026-06-29 08:05 ` Re: Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2 siblings, 1 reply; 15+ messages in thread
From: Fujii Masao @ 2026-06-29 06:23 UTC (permalink / raw)
To: Ayush Tiwari <[email protected]>; +Cc: pgsql-hackers; Álvaro Herrera <[email protected]>; Nathan Bossart <[email protected]>
On Sat, Jun 6, 2026 at 4:28 AM Ayush Tiwari <[email protected]> wrote:
> 0002 (test-only) teaches AdjustUpgrade.pm to drop the refint
> extension and its leftover tables (pkeys, fkeys, fkeys2) from
> contrib_regression_spi / regression_spi when upgrading from a version
> that still shipped refint, so cross-version pg_upgrade tests don't
> fail dump comparison once v20 opens.
> I followed the existing < 19 btree_gist block style.
In make-based contrib tests with USE_MODULE_DB=1, contrib/spi uses
contrib_regression_autoinc database, since autoinc is the first entry
in MODULES. So, in 0002 patch, shouldn't we also remove
the refint-related objects from contrib_regression_autoinc?
For example:
foreach my $dbname (
'contrib_regression_autoinc',
'contrib_regression_spi',
'regression_spi')
{
next unless $dbnames{$dbname};
_add_st(
$result,
$dbname,
'drop extension if exists refint cascade',
'drop table if exists pkeys, fkeys, fkeys2');
}
Regards,
--
Fujii Masao
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: Remove the refint contrib module (for v20)
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-29 06:23 ` Re: Remove the refint contrib module (for v20) Fujii Masao <[email protected]>
@ 2026-06-29 08:05 ` Ayush Tiwari <[email protected]>
0 siblings, 0 replies; 15+ messages in thread
From: Ayush Tiwari @ 2026-06-29 08:05 UTC (permalink / raw)
To: Fujii Masao <[email protected]>; +Cc: pgsql-hackers
Hi,
On Mon, 29 Jun 2026 at 11:53, Fujii Masao <[email protected]> wrote:
> On Sat, Jun 6, 2026 at 4:28 AM Ayush Tiwari <[email protected]>
> wrote:
> > 0002 (test-only) teaches AdjustUpgrade.pm to drop the refint
> > extension and its leftover tables (pkeys, fkeys, fkeys2) from
> > contrib_regression_spi / regression_spi when upgrading from a version
> > that still shipped refint, so cross-version pg_upgrade tests don't
> > fail dump comparison once v20 opens.
> > I followed the existing < 19 btree_gist block style.
>
> In make-based contrib tests with USE_MODULE_DB=1, contrib/spi uses
> contrib_regression_autoinc database, since autoinc is the first entry
> in MODULES. So, in 0002 patch, shouldn't we also remove
> the refint-related objects from contrib_regression_autoinc?
Thanks for the review!
Addressed in this thread:
https://www.postgresql.org/message-id/CAJTYsWVMX_csjBafWngAqAAXBa%3D%2BUy%2BrY2Je4jM6CJ11tF38Mw%40ma...
Regards,
Ayush
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2026-07-08 21:24 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-06-05 19:27 Remove the refint contrib module (for v20) Ayush Tiwari <[email protected]>
2026-06-05 21:05 ` Nathan Bossart <[email protected]>
2026-06-05 22:05 ` Daniel Gustafsson <[email protected]>
2026-06-08 19:14 ` Ayush Tiwari <[email protected]>
2026-06-09 04:46 ` Ayush Tiwari <[email protected]>
2026-06-09 20:42 ` Nathan Bossart <[email protected]>
2026-06-25 11:28 ` solai v <[email protected]>
2026-06-28 08:14 ` Álvaro Herrera <[email protected]>
2026-06-28 18:59 ` Daniel Gustafsson <[email protected]>
2026-06-29 04:41 ` Ayush Tiwari <[email protected]>
2026-06-29 08:02 ` Ayush Tiwari <[email protected]>
2026-07-08 21:24 ` Nathan Bossart <[email protected]>
2026-06-05 22:09 ` Tom Lane <[email protected]>
2026-06-29 06:23 ` Fujii Masao <[email protected]>
2026-06-29 08:05 ` Ayush Tiwari <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox