Re: How to get schema name which violates fk constraint

FK violation erroro is displayed as

7/23503:ERROR: insert or update on table "summak" violates foreign key
constraint "summak_kontonr_fkey1"
Key (kontonr)=(2421 ) is not present in table "konto".

I have large numbers of schemas all containing tables with same name.

How to determine schema name where error occurs?

Andrus.

Actually this sounds like a TODO to me. I imagine the db knows the
schema and it's just not reporting it in the error message. Bruce?
Tom?

On Tue, Oct 14, 2008 at 9:43 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
> FK violation erroro is displayed as
>
> 7/23503:ERROR: insert or update on table "summak" violates foreign key
> constraint "summak_kontonr_fkey1"
> Key (kontonr)=(2421 ) is not present in table "konto".
>
> I have large numbers of schemas all containing tables with same name.
>
> How to determine schema name where error occurs?
>
> Andrus.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis

The Java version of extended VTD-XmL is released and available for download.
This version supports 256 GB max file sizes and memory mapped capabilities.
The updated documentation is also available for download. In short, you can
basically do full XPath query on documents that are bigger than memory space
available on your machine.

A special thanks to Duane May who provided value suggestions and inputs and
helped refine the VTD specs to make this happen.

To download the package and the documentation, go to
https://sourceforge.net/project/downloading.php?group_id=110612&use_mirror=&filename=vtd-xml_2.4_doc.zip&64621261

https://sourceforge.net/project/downloading.php?group_id=110612&use_mirror=&filename=ximpleware_extended_2.4.zip&99532507

Scott Marlowe escribió:
> Actually this sounds like a TODO to me. I imagine the db knows the
> schema and it's just not reporting it in the error message. Bruce?
> Tom?

Added -- it should be easy to do, so marked as such.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Scott Marlowe escribi:
>> Actually this sounds like a TODO to me. I imagine the db knows the
>> schema and it's just not reporting it in the error message. Bruce?
>> Tom?

> Added -- it should be easy to do, so marked as such.

A comprehensive response to this type of gripe wouldn't be all that
"easy". In the first place, there'd be a lot of code to touch. In the
second place, the reason most of our messages don't already contain
schema names is that in the past we've judged it would be mostly
clutter; and given the infrequency of complaints I see no reason to
change that opinion.

The type of fix I'd like to see would be to not change message texts at
all, but to add separate error-message fields for the name and schema
name of object(s) involved in an error; which would be details that
psql, for example, would show only in VERBOSE mode. Note that error
report fields along this line are actually required by the SQL spec
(cf GET DIAGNOSTICS) but we've never got round to implementing 'em.

regards, tom lane

> The type of fix I'd like to see would be to not change message texts at
> all, but to add separate error-message fields for the name and schema
> name of object(s) involved in an error; which would be details that
> psql, for example, would show only in VERBOSE mode. Note that error
> report fields along this line are actually required by the SQL spec
> (cf GET DIAGNOSTICS) but we've never got round to implementing 'em.

How about adding also a way to get primary key value(s) of the row which
causes error ?

Andrus.

On Oct 22, 2008, at 6:50 AM, Tom Lane wrote:

> In the
> second place, the reason most of our messages don't already contain
> schema names is that in the past we've judged it would be mostly
> clutter; and given the infrequency of complaints I see no reason to
> change that opinion.

Well, FWIW, I also would like to be able to see which schema caused
the violation, as I'm in a similar boat of having the same table name
in multiple schemas.

> Well, FWIW, I also would like to be able to see which schema caused the
> violation, as I'm in a similar boat of having the same table name in
> multiple schemas.

Maybe to report schema name only if it is not public or if same table
exists in different schemas or report it in detail message or hint.

Andrus.

Tom Lane escribió:

> A comprehensive response to this type of gripe wouldn't be all that
> "easy". In the first place, there'd be a lot of code to touch.

Well, that makes it tedious, which is not the same as hard.

> In the second place, the reason most of our messages don't already
> contain schema names is that in the past we've judged it would be
> mostly clutter; and given the infrequency of complaints I see no
> reason to change that opinion.

I tend to disagree. We can run a poll in a wider audience.

> The type of fix I'd like to see would be to not change message texts at
> all, but to add separate error-message fields for the name and schema
> name of object(s) involved in an error; which would be details that
> psql, for example, would show only in VERBOSE mode. Note that error
> report fields along this line are actually required by the SQL spec
> (cf GET DIAGNOSTICS) but we've never got round to implementing 'em.

Now that's a bit more complex than the trivial solution of adding an
extra %s to the error message, but it's still not all that difficult, I
think.

--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane escribi:
>> In the second place, the reason most of our messages don't already
>> contain schema names is that in the past we've judged it would be
>> mostly clutter; and given the infrequency of complaints I see no
>> reason to change that opinion.

> I tend to disagree. We can run a poll in a wider audience.

We already have a large poll: divide the number of complaints on this
topic since 7.3 came out by the number of users ...

regards, tom lane

On Wed, Oct 22, 2008 at 11:27 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
>> Tom Lane escribió:
>>> In the second place, the reason most of our messages don't already
>>> contain schema names is that in the past we've judged it would be
>>> mostly clutter; and given the infrequency of complaints I see no
>>> reason to change that opinion.
>
>> I tend to disagree. We can run a poll in a wider audience.
>
> We already have a large poll: divide the number of complaints on this
> topic since 7.3 came out by the number of users ...

But it could well be a chicken and egg issue. People don't use a lot
of schemas because the support for error reporting and other stuff is
not finished, so they can't complain about a feature they don't use.
Just a thought. I like schemas, but they do feel like some parts
aren't quite done.

On Wed, Oct 22, 2008 at 11:13 AM, Andrus <kobruleht2(at)hot(dot)ee> wrote:
>> Well, FWIW, I also would like to be able to see which schema caused the
>> violation, as I'm in a similar boat of having the same table name in
>> multiple schemas.
>
> Maybe to report schema name only if it is not public or if same table exists
> in different schemas or report it in detail message or hint.

I doubt that would make it any easier to implement, and I don't see
reporting schemaname.relationname as being all that cluttery anyway.

-- When fascism comes to America, it will be draped in a flag and
carrying a cross - Sinclair Lewis

> >> In the second place, the reason most of our messages don't already
> >> contain schema names is that in the past we've judged it would be
> >> mostly clutter; and given the infrequency of complaints I see no
> >> reason to change that opinion.
>
> > I tend to disagree. We can run a poll in a wider audience.
>
> We already have a large poll: divide the number of complaints on this
> topic since 7.3 came out by the number of users ...

Since it seems like we are voting (!) let me say that fully informative
errors that include the schema would be very useful for those of us who
do use schemas to organize their tables. The generic "proper" way to
address a table in a schema (short of user path settings) is to qualify
it by its schema, so that's the unique fully descriptive name of the
table so all errors/diagnostics should reference that. Otherwise schemas
look like they are delegated to a second-class feature ("we have it so
we can check off a feature matrix, but our heart is not fully in it"). I
suspect lack of complaints is largely due to the (small) number of
people using namespaces -- the denominator should be users of the
feature, not all users...

George

George Pavlov wrote:

> I
> suspect lack of complaints is largely due to the (small) number of
> people using namespaces -- the denominator should be users of the
> feature, not all users...

I certainly found it extremely frustrating that errors didn't reference
the involved schema when I was working on a database that used several
sets of tables with the same names and structure under different schema.
As it happens I ended up finding it to be much better to store all the
data in a single set of tables with composite primary keys, but there
are certainly situations where that won't be the case.

I can see, however, that it might be a pretty tedious thing to do and
not very high on anybody's fun-to-code list.

--
Craig Ringer