Обсуждение: RI Constraint display
Referential integrity constraints are displayed as $n.
Is this a bug or a feature? I first ran into the issue
with the RI error message and I thought it was just a message
bug. But I can also see the $n as a name with \d via psql.
Looking at pg_trigger, though, makes me think that there
might be some confusion between tgname and tgconstrname
(what is tgconstrname??) and tgargs.
Or is this how it is supposed to be? I really expected the
trigger name to be displayed where it is displaying $1 and $2
in the error message and in psql.
postgresql 7.3.1
SuSe 7.3
--elein
create table rr_reports (
r_name text not null,
rt_type text not null,
ag_name text,
r_title text,
r_sdesc text,
r_ldesc text,
PRIMARY KEY (r_name)
, FOREIGN KEY (rt_type) references rr_types
, FOREIGN KEY (ag_name) references rr_appgroups
);
From log after bad INSERTS:
ERROR: $2 referential integrity violation - key referenced from rr_reports
not found in rr_appgroups
ERROR: $1 referential integrity violation - key referenced from rr_rprompts
not found in rr_reports
But I also see it using \d:
\d rr_reports
Table "public.rr_reports"
Column | Type | Modifiers
---------+------+-----------
r_name | text | not null
rt_type | text | not null
ag_name | text |
r_title | text |
r_sdesc | text |
r_ldesc | text |
Indexes: rr_reports_pkey primary key btree (r_name)
Foreign Key constraints: $1 FOREIGN KEY (rt_type) REFERENCES
rr_types(rt_type) ON UPDATE NO
ACTION ON DELETE NO ACTION,
$2 FOREIGN KEY (ag_name) REFERENCES
rr_appgroups(ag_name) ON UPDATE NO ACTION ON DELETE NO ACTION
select * from pg_trigger;
tgrelid | tgname | tgfoid | tgtype | tgenabled |
tgisconstraint | tgconstrname | tgconstrrelid | tgdeferrable | tginitdeferred
| tgnargs | tgattr |
tgargs
---------+-----------------------------+--------+--------+-----------+----------------+--------------+---------------+--------------+----------------+---------+--------+---------------------------------------------------------------------------
437264 | RI_ConstraintTrigger_437272 | 1644 | 21 | t | t
| $1
| 437254 | f | f | 6 | |
$1\000rr_reports\000rr_types\000UNSPECIFIED\000rt_type\000rt_type\000
437254 | RI_ConstraintTrigger_437273 | 1654 | 9 | t | t
| $1
| 437264 | f | f | 6 | |
$1\000rr_reports\000rr_types\000UNSPECIFIED\000rt_type\000rt_type\000
437254 | RI_ConstraintTrigger_437274 | 1655 | 17 | t | t
| $1
| 437264 | f | f | 6 | |
$1\000rr_reports\000rr_types\000UNSPECIFIED\000rt_type\000rt_type\000
437264 | RI_ConstraintTrigger_437276 | 1644 | 21 | t | t
| $2
| 437244 | f | f | 6 | |
$2\000rr_reports\000rr_appgroups\000UNSPECIFIED\000ag_name\000ag_name\000
437244 | RI_ConstraintTrigger_437277 | 1654 | 9 | t | t
| $2
| 437264 | f | f | 6 | |
$2\000rr_reports\000rr_appgroups\000UNSPECIFIED\000ag_name\000ag_name\000
437244 | RI_ConstraintTrigger_437278 | 1655 | 17 | t | t
| $2
| 437264 | f | f | 6 | |
$2\000rr_reports\000rr_appgroups\000UNSPECIFIED\000ag_name\000ag_name\000
--
----------------------------------------------------------------------------------------
elein@varlena.com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.
elein <elein@sbcglobal.net> writes:
> Referential integrity constraints are displayed as $n.
If you didn't assign a name to the constraint, that's what the generated
names look like. Try "CONSTRAINT foo FOREIGN KEY ...".
regards, tom lane
Then this is a distinction between the trigger name and
the constraint name? The trigger name is RI_ConstraintTrigger_437278
(or some such oid). The trigger is the implementation of the constraint
so the trigger name is what I had expected to see.
Almost all of the system generated names, sequences, triggers, etc,
have constructed names. $n for constrain names seems like an anomaly.
elein
On Thursday 26 December 2002 13:45, Tom Lane wrote:
> elein <elein@sbcglobal.net> writes:
> > Referential integrity constraints are displayed as $n.
>
> If you didn't assign a name to the constraint, that's what the generated
> names look like. Try "CONSTRAINT foo FOREIGN KEY ...".
>
> regards, tom lane
--
----------------------------------------------------------------------------------------
elein@varlena.com Database Consulting www.varlena.com
I have always depended on the [QA] of strangers.
On Sat, 28 Dec 2002, elein wrote: > > Then this is a distinction between the trigger name and > the constraint name? The trigger name is RI_ConstraintTrigger_437278 > (or some such oid). The trigger is the implementation of the constraint > so the trigger name is what I had expected to see. There are three triggers for the constraint though. It needs a name separate from those of the triggers (or it could pick one of the triggers to name it after, but that seems just as confusing to me). > Almost all of the system generated names, sequences, triggers, etc, > have constructed names. $n for constrain names seems like an anomaly. I think it's been that way for check constraints for a long time unless I remember incorrectly. When the change was made to actually name the constraint (rather than naming them all unnamed) I figure the current naming convention was carried across.
Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> On Sat, 28 Dec 2002, elein wrote:
>> Almost all of the system generated names, sequences, triggers, etc,
>> have constructed names. $n for constrain names seems like an anomaly.
> I think it's been that way for check constraints for a long time unless I
> remember incorrectly.
I think you remember correctly.
The "$n" convention is somewhat arbitrary, but in my mind it certainly
beats the OID-based convention we have used for RI triggers. For one
thing, if you issue the same table declaration twice, you'll get the
same names associated with unnamed constraints...
regards, tom lane
OK, got it. Thank you for the clarifications. --elein On Monday 30 December 2002 20:40, Tom Lane wrote: > Stephan Szabo <sszabo@megazone23.bigpanda.com> writes: > > On Sat, 28 Dec 2002, elein wrote: > >> Almost all of the system generated names, sequences, triggers, etc, > >> have constructed names. $n for constrain names seems like an anomaly. > > > > I think it's been that way for check constraints for a long time unless I > > remember incorrectly. > > I think you remember correctly. > > The "$n" convention is somewhat arbitrary, but in my mind it certainly > beats the OID-based convention we have used for RI triggers. For one > thing, if you issue the same table declaration twice, you'll get the > same names associated with unnamed constraints... > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- ---------------------------------------------------------------------------------------- elein@varlena.com Database Consulting www.varlena.com I have always depended on the [QA] of strangers.