Constraints are triggers???

Mladen Gogala

unread,

Oct 29, 2009, 5:27:13 PM10/29/09

to

I just created my first trigger in Postgres and went to check the catalog
schema:
scott=> select
tgname,tgtype,tgenabled,tgisconstraint,tgconstrname,tgconstrrelid

from pg_trigger;

----------------------------+--------+-----------+----------------
+------------------------------------+---------------

pg_sync_pg_database | 28 | O | f
| | 0

pg_sync_pg_authid | 28 | O | f
| | 0

pg_sync_pg_auth_members | 28 | O | f
| | 0

RI_ConstraintTrigger_16797 | 5 | O | t |
fk_deptno | 16781

RI_ConstraintTrigger_16798 | 17 | O | t |
fk_deptno | 16781

RI_ConstraintTrigger_16799 | 9 | O | t |
fk_deptno | 16784

RI_ConstraintTrigger_16800 | 17 | O | t |
fk_deptno | 16784

RI_ConstraintTrigger_17001 | 5 | O | t |
jar_entry_jarid_fkey | 16976

RI_ConstraintTrigger_17002 | 17 | O | t |
jar_entry_jarid_fkey | 16976

RI_ConstraintTrigger_17003 | 9 | O | t |
jar_entry_jarid_fkey | 16989

RI_ConstraintTrigger_17004 | 17 | O | t |
jar_entry_jarid_fkey | 16989

RI_ConstraintTrigger_17006 | 5 | O | t |
jar_repository_deploymentdesc_fkey | 16989

RI_ConstraintTrigger_17007 | 17 | O | t |
jar_repository_deploymentdesc_fkey | 16989

RI_ConstraintTrigger_17008 | 9 | O | t |
jar_repository_deploymentdesc_fkey | 16976

RI_ConstraintTrigger_17009 | 17 | O | t |
jar_repository_deploymentdesc_fkey | 16976

RI_ConstraintTrigger_17016 | 5 | O | t |
classpath_entry_jarid_fkey | 16976

RI_ConstraintTrigger_17017 | 17 | O | t |
classpath_entry_jarid_fkey | 16976

RI_ConstraintTrigger_17018 | 9 | O | t |
classpath_entry_jarid_fkey | 17010

RI_ConstraintTrigger_17019 | 17 | O | t |
classpath_entry_jarid_fkey | 17010

log_trg | 5 | O | f
| | 0

(20 rows)

It seems that every foreign key constraint in the database is, actually,
a trigger? The code is defined like this:

scott=> select prosrc from pg_proc where proname='RI_FKey_check_ins';
prosrc
-------------------
RI_FKey_check_in

I can't go check the source code right now, but I suspect this is
implemented as a C function?
--
http://mgogala.freehostia.com

Laurenz Albe

unread,

Oct 30, 2009, 4:11:09 AM10/30/09

to

Mladen Gogala wrote:
> It seems that every foreign key constraint in the database is, actually,
> a trigger? The code is defined like this:
>
> scott=> select prosrc from pg_proc where proname='RI_FKey_check_ins';
> prosrc
> -------------------
> RI_FKey_check_in
>
> I can't go check the source code right now, but I suspect this is
> implemented as a C function?

Yes:

test=> CREATE TABLE test(
id integer CONSTRAINT test_pkey PRIMARY KEY,
fk integer CONSTRAINT test_fkey REFERENCES test(id)
);

test=> INSERT INTO test VALUES (1, NULL);

test=> SELECT tableoid FROM test;

tableoid
----------
19701
(1 row)

test=> SELECT tgname, tgfoid, tgtype, tgconstrname
FROM pg_trigger WHERE tgrelid=19701;

tgname | tgfoid | tgtype | tgconstrname
----------------------------+--------+--------+--------------
RI_ConstraintTrigger_19707 | 1644 | 5 | test_fkey
RI_ConstraintTrigger_19708 | 1645 | 17 | test_fkey
RI_ConstraintTrigger_19709 | 1654 | 9 | test_fkey
RI_ConstraintTrigger_19710 | 1655 | 17 | test_fkey
(4 rows)

test=> SELECT p.proname, l.lanname
FROM pg_proc AS p JOIN
pg_language AS l ON (p.prolang = l.oid)
WHERE p.oid IN (1644, 1645, 1654, 1655);

Language "internal" means it is a builtin C function.

They are defined in /src/backend/utils/adt/ri_triggers.c

Documentation pointers:
http://www.postgresql.org/docs/8.4/static/sql-createconstraint.html
http://www.postgresql.org/docs/8.4/static/catalog-pg-trigger.html

Yours,
Laurenz Albe

Mladen Gogala

unread,

Nov 2, 2009, 9:14:34 PM11/2/09

to

Thanks, Laurenz. You are also well known in the Oracle community. Do you
see Oracle people starting to use Postgres? Oracle's predatory pricing
did not make them many new friends, that's for sure. You are a person
from both worlds. When would you recommend replacing Oracle with
Postgres?

--
http://mgogala.freehostia.com

Laurenz Albe

unread,

Nov 3, 2009, 7:31:52 AM11/3/09

to

Mladen Gogala wrote:
> Do you
> see Oracle people starting to use Postgres? Oracle's predatory pricing
> did not make them many new friends, that's for sure. You are a person
> from both worlds. When would you recommend replacing Oracle with
> Postgres?

Where I live and work (Austria / Europe) I see a growing awareness
of PostgreSQL, although that view may be biased by my growing
involvement with it. Most people still think MySQL when they hear
"open source database", but I see a shift there.

I know several big companies who either launch first smaller projects
with PostgreSQL or at least consider that. I know no company who
has decided to get rid of all their Oracle databases and use
PostgreSQL instead.

To your second question, when to migrate:

I would say, if there is no Oracle-specific feature involved that
you cannot do without and that you cannot have with PostgreSQL
with reasonable effort.

For example, you cannot have RAC functionality (one database
simultanously used on several machines with all the scaling
and failover capabilities) with PostgreSQL.
But then, often a RAC ic chosen to satisfy some vague ill-defined
availability requirements, and on closer look something else
would also do.

Another thing that makes it difficult to migrate is if you have a
lot of stored procedures that rely heavily on Oracle's standard
packages. You would probably end up rewriting the whole application,
some of it in pl/Perl or C.

Another killer is nontrivial use of database links. There are
possibilities to access data from different databases (dblink
contrib module, DBI-Link), but they basically fetch a result set
for you. You cannot use it for efficient joins across databases
or distributed transactions, so all (ab)use of database links
for such purposes would have to be reconsidered and rewritten.

On a more general side, the difference is that Oracle comes with
a lot of features which are not all strictly database related.
I am thinking of things like database flashback, job scheduling,
AWR, RMAN, log miner and similar, to name a few that my company
finds useful.

Many of these things can somehow be done with PostgreSQL, but
usually you need some third party software and have to be willing
to write some glue yourself. That means additional development
and maintenance cost, and sometimes more work for the DBA.
These are costs you have to consider and balance against Oracle's
license fees.

Yours,
Laurenz Albe