Thread: [BUGS] BUG #14888: Need to recheck contraints
The following bug has been logged on the website: Bug reference: 14888 Logged by: Daniel Migowski Email address: dmigowski@ikoffice.de PostgreSQL version: 9.5.9 Operating system: doesn't matter Description: The is no way to VALIDATE constraints that PostgreSQL already thinks of as VALID. I would love a "force" option here, because when disabling all triggers there is still the possibility for errors and I would love to check our databases for them. How do I do it? -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
On 2017-11-06 06:50, dmigowski@ikoffice.de wrote: > The is no way to VALIDATE constraints that PostgreSQL already thinks of as > VALID. I would love a "force" option here, because when disabling all > triggers there is still the possibility for errors and I would love to check > our databases for them. > > How do I do it? This is a feature request, but I need it too. My work-around is to use a DO statement that iterates over pg_constraint to duplicate all interesting constraints giving each a new name and ends with a RAISE EXCEPTION 'OK'. If the execution ends with that "error", all constraints are valid. I guess you could wrap it into a BEGIN ... ROLLBACK to get the same effect. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs
Okey, my current workaround is just setting convalidated in pg_constraint to false just prior to checking it. Also nice becausethe constraint stays invalidated until fixed. Regards, Daniel Migowski -----Ursprüngliche Nachricht----- Von: Piotr Stefaniak [mailto:postgres@piotr-stefaniak.me] Gesendet: Montag, 6. November 2017 07:48 An: Daniel Migowski; pgsql-bugs@postgresql.org Betreff: Re: [BUGS] BUG #14888: Need to recheck contraints On 2017-11-06 06:50, dmigowski@ikoffice.de wrote: > The is no way to VALIDATE constraints that PostgreSQL already thinks > of as VALID. I would love a "force" option here, because when > disabling all triggers there is still the possibility for errors and I > would love to check our databases for them. > > How do I do it? This is a feature request, but I need it too. My work-around is to use a DO statement that iterates over pg_constraint to duplicate all interesting constraints givingeach a new name and ends with a RAISE EXCEPTION 'OK'. If the execution ends with that "error", all constraints arevalid. I guess you could wrap it into a BEGIN ... ROLLBACK to get the same effect. -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs