schema-qualified SET CONSTRAINTS

Kris Jurka <books@ejurka.com> writes:

The attached patch allows SET CONSTRAINTS to take a schema qualified
constraint name (myschema.t1_fk_t2) and when given a bare constraint name
it uses the search_path to determine the matching constraint instead of
the previous behavior of disabling all identically named constraints.

This patch seems egregiously non backwards compatible :-(. A behavior
that would be backwards compatible is to retain the previous behavior
given an un-qualified name, while if given a schema-qualified name,
modify all matching constraints within that schema. That doesn't seem
very self-consistent though. A compromise that might succeed in making
*everybody* unhappy would be for the unqualified-name case to only
affect constraints that are visible in the current search path (but
affect all of them, not only one as in this patch).

Given the fundamental point that we don't insist on uniqueness of
constraint names within schemas, I'm not sure that the spec gives us
any useful guidance on what SET CONSTRAINTS should affect.

Anyway, I'm not sure what to do, but I am sure it requires some
discussion not just a patch.

regards, tom lane

On Mon, 10 Apr 2006, Tom Lane wrote:

Kris Jurka <books@ejurka.com> writes:

The attached patch allows SET CONSTRAINTS to take a schema qualified
constraint name (myschema.t1_fk_t2) and when given a bare constraint name
it uses the search_path to determine the matching constraint instead of
the previous behavior of disabling all identically named constraints.

This patch seems egregiously non backwards compatible :-(.

Yes, it does change the existing behavior, but "egregiously"? How many
applications intentionally defer constraints in multiple schemas at once?
Not many. I would guess the more likely situation is that these
applications don't even realize that they are deferring more than one
constraint when it happens. So there will be some very minor pain when
they must select the desired constraint (if it doesn't happen already by
search_path) or explicitly defer more than one constraint, but I'm OK
with that. The existing behavior of SET CONSTRAINTS affecting everything
is not what a user would expect when we have tools like search_path
available.

Kris Jurka

The attached patch allows SET CONSTRAINTS to take a schema

qualified

constraint name (myschema.t1_fk_t2) and when given a bare

constraint name

it uses the search_path to determine the matching constraint

instead of

the previous behavior of disabling all identically named

constraints.

This patch seems egregiously non backwards compatible :-(.

Yes, it does change the existing behavior, but "egregiously"? How many

applications intentionally defer constraints in multiple schemas at

once?

intentionally defer "specifically named" constraints in multiple schemas
(The default application would imho eighter defer all, or a specific
constraint)

Not many. I would guess the more likely situation is that these
applications don't even realize that they are deferring more than one
constraint when it happens.

I agree. I think the new behavior is more intuitive, and would even
argue
the old behavior gets it wrong.

Andreas

Based on discussion, it seems the idea of using search path seems
accepted.

Your patch has been added to the PostgreSQL unapplied patches list at:

http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------

Kris Jurka wrote:

The attached patch allows SET CONSTRAINTS to take a schema qualified
constraint name (myschema.t1_fk_t2) and when given a bare constraint name
it uses the search_path to determine the matching constraint instead of
the previous behavior of disabling all identically named constraints.

Kris Jurka

Content-Description:

[ Attachment, skipping... ]

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Patch applied. Thanks. I also updated our SGML documentation:

The current schema search path is used to find the first matching name
if no schema name is specified.

---------------------------------------------------------------------------

Kris Jurka wrote:

The attached patch allows SET CONSTRAINTS to take a schema qualified
constraint name (myschema.t1_fk_t2) and when given a bare constraint name
it uses the search_path to determine the matching constraint instead of
the previous behavior of disabling all identically named constraints.

Kris Jurka

Content-Description:

[ Attachment, skipping... ]

--
Bruce Momjian http://candle.pha.pa.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +