Обсуждение: getting all constraint violations

In response to Gauthier, Dave :
> Hi:
>
>
>
> I have a table with many constraints.  A user tries to insert a record that
> violates many of them.  The error message I get back lists the first violation.
> How cani I (or can I) get them all?

I think that isn't possible: the first violation throws the error, the
transaction stopped.


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

On Fri, May 21, 2010 at 1:27 PM, Gauthier, Dave <dave.gauthier@intel.com> wrote:
> Hi:
>
>
>
> I have a table with many constraints.  A user tries to insert a record that
> violates many of them.  The error message I get back lists the first
> violation. How cani I (or can I) get them all?

the database stops processing after the first error.  the only way to
generate a list is to check it on the client first, or do something
like make a 'before' trigger to pre-scan.

merlin

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> I have a table with many constraints.  A user tries to insert a record that violates many of them.  The error message
Iget back lists the first violation. How cani I (or can I) get them all? 

You can't, it stops running the command at the first error.

            regards, tom lane

Is there a way to temporarily suspend constraint checking for a particular constraint inside of the transaction, try
theinsert again, capture the next violation, then the next, etc... then rollback after all have been collected? 

-dave

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 21, 2010 1:36 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] getting all constraint violations

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> I have a table with many constraints.  A user tries to insert a record that violates many of them.  The error message
Iget back lists the first violation. How cani I (or can I) get them all? 

You can't, it stops running the command at the first error.

            regards, tom lane

On Fri, May 21, 2010 at 11:46 AM, Gauthier, Dave
<dave.gauthier@intel.com> wrote:
> Is there a way to temporarily suspend constraint checking for a particular constraint inside of the transaction, try
theinsert again, capture the next violation, then the next, etc... then rollback after all have been collected? 

If the constraint has the ability to be defined as deferr-able you can do this.

from the online pg documentation:

http://www.postgresql.org/docs/8.4/interactive/sql-createtable.html

[ CONSTRAINT constraint_name ]
{ NOT NULL |
  NULL |
  UNIQUE index_parameters |
  PRIMARY KEY index_parameters |
  CHECK ( expression ) |
  REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL |
MATCH SIMPLE ]
    [ ON DELETE action ] [ ON UPDATE action ] }
[ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]


DEFERRABLE
NOT DEFERRABLE

    This controls whether the constraint can be deferred. A constraint
that is not deferrable will be checked immediately after every
command. Checking of constraints that are deferrable can be postponed
until the end of the transaction (using the SET CONSTRAINTS command).
NOT DEFERRABLE is the default. Only foreign key constraints currently
accept this clause. All other constraint types are not deferrable.
INITIALLY IMMEDIATE
INITIALLY DEFERRED

    If a constraint is deferrable, this clause specifies the default
time to check the constraint. If the constraint is INITIALLY
IMMEDIATE, it is checked after each statement. This is the default. If
the constraint is INITIALLY DEFERRED, it is checked only at the end of
the transaction. The constraint check time can be altered with the SET
CONSTRAINTS command.



IIRC,  there is an exception to the deferrable rule.  I believe that
constraint triggers can also be made to be deferrable.

http://www.postgresql.org/docs/8.4/interactive/sql-createconstraint.html

--
Regards,
Richard Broersma Jr.

Visit the Los Angeles PostgreSQL Users Group (LAPUG)
http://pugs.postgresql.org/lapug

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> Is there a way to temporarily suspend constraint checking for a particular constraint inside of the transaction, try
theinsert again, capture the next violation, then the next, etc... then rollback after all have been collected? 

You could do something like

    BEGIN;
    ALTER TABLE DROP CONSTRAINT ...
    INSERT ...
    ROLLBACK;

The major deficiency of this is that the ALTER TABLE would grab
exclusive lock on the table, so this doesn't scale if you need to
have several insertions happening in parallel.

            regards, tom lane

I'm thinking more along the lines of creating a bunch of temp tables, each with one of the constraints.  Then, in a
loop,throw the record at each of these temp tables and collect up the violations.  

Exploring now hot to get the pieces I need from the metadata tables to do this.


-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Friday, May 21, 2010 3:10 PM
To: Gauthier, Dave
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] getting all constraint violations

"Gauthier, Dave" <dave.gauthier@intel.com> writes:
> Is there a way to temporarily suspend constraint checking for a particular constraint inside of the transaction, try
theinsert again, capture the next violation, then the next, etc... then rollback after all have been collected? 

You could do something like

    BEGIN;
    ALTER TABLE DROP CONSTRAINT ...
    INSERT ...
    ROLLBACK;

The major deficiency of this is that the ALTER TABLE would grab
exclusive lock on the table, so this doesn't scale if you need to
have several insertions happening in parallel.

            regards, tom lane