[prev in list] [next in list] [prev in thread] [next in thread] 

List:       postgresql-admin
Subject:    Re: Need to check disabled constraints
From:       "David G. Johnston" <david.g.johnston () gmail ! com>
Date:       2018-01-29 13:27:39
Message-ID: CAKFQuwb2bLY3LQ9N9adkBC-86OjZNWDf-OocvoXGRqwjFHVNaw () mail ! gmail ! com
[Download RAW message or body]

On Mon, Jan 29, 2018 at 12:19 AM, Sohel Tamboli <sohel.tamboli0016@gmail.com
> wrote:

> Hi,
>
> Any updates?
> Also I need to disable all FK in schema/database for database migration.
> How can I achieve that?
>
>
​pg_dump/pg_restore handles this for you...but see:

https://www.postgresql.org/docs/10/static/sql-altertable.html
"DISABLE/ENABLE [ REPLICA | ALWAYS ] TRIGGER"


> On 25-Jan-2018 12:57 pm, "Sohel Tamboli" <sohel.tamboli0016@gmail.com>
> wrote:
>
>> Can anyone please let me know how I can check disabled constraints in
>> postgresql 9.1 database.
>> I know that ALTER TABLE tablename DISABLE TRIGGER ALL; can disable
>> foriegn constraints, but how I can check if it is really disabled.
>> Similarly I need to check if constraint is enable.
>>
>> In oracle we have option to check status of constraint in
>> user_constraints table but do we have same in postgresql as well?
>>
>>
A doc skim should have led you to pg_triggers in the "System Catalogs"
chapter.  The column tgenabled looks like what you are looking for.

David J.

[Attachment #3 (text/html)]

<div dir="ltr"><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><span \
style="font-family:arial,sans-serif">On Mon, Jan 29, 2018 at 12:19 AM, Sohel Tamboli \
</span><span dir="ltr" style="font-family:arial,sans-serif">&lt;<a \
href="mailto:sohel.tamboli0016@gmail.com" \
target="_blank">sohel.tamboli0016@gmail.com</a>&gt;</span><span \
style="font-family:arial,sans-serif"> wrote:</span><br></div><div \
class="gmail_extra"><div class="gmail_quote"><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="auto">Hi,  <div \
dir="auto"><br></div><div dir="auto">Any updates?  </div><div dir="auto">Also I need \
to disable all FK in schema/database for database migration. How can I achieve that?  \
</div><div dir="auto"><br></div></div></blockquote><div><br></div><div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">​pg_dump/pg_restore handles this for \
you...but see:</div></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div><div \
class="gmail_default"><font face="arial, helvetica, sans-serif"><a \
href="https://www.postgresql.org/docs/10/static/sql-altertable.html">https://www.postgresql.org/docs/10/static/sql-altertable.html</a></font></div><div \
class="gmail_default"><font face="arial, helvetica, sans-serif">&quot;DISABLE/ENABLE \
[ REPLICA | ALWAYS ] TRIGGER&quot;</font></div><div class="gmail_default"><font \
face="arial, helvetica, sans-serif"><br></font></div><blockquote class="gmail_quote" \
style="margin:0px 0px 0px 0.8ex;border-left:1px solid \
rgb(204,204,204);padding-left:1ex"><div dir="auto"><div dir="auto"></div></div><div \
class="gmail-HOEnZb"><div class="gmail-h5"><div class="gmail_extra"><br><div \
class="gmail_quote">On 25-Jan-2018 12:57 pm, &quot;Sohel Tamboli&quot; &lt;<a \
href="mailto:sohel.tamboli0016@gmail.com" \
target="_blank">sohel.tamboli0016@gmail.com</a>&gt; wrote:<br \
type="attribution"><blockquote class="gmail_quote" style="margin:0px 0px 0px \
0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"><div dir="auto"><div \
dir="auto">Can anyone please let me know how I can check disabled constraints in \
postgresql 9.1 database.  <br></div><div dir="auto">I know that ALTER TABLE tablename \
DISABLE TRIGGER ALL; can disable foriegn constraints, but how I can check if it is \
really disabled. Similarly I need to check if constraint is enable.  </div><div \
dir="auto"><br></div><div dir="auto">In oracle we have option to check status of \
constraint in user_constraints table but do we have same in postgresql as well?  \
</div><div dir="auto"><br></div></div></blockquote></div></div></div></div></blockquote><div><br></div><div \
class="gmail_default" style="font-family:arial,helvetica,sans-serif">A doc skim \
should have led you to pg_triggers in the &quot;System Catalogs&quot; chapter.   The \
column tgenabled looks like what you are looking for.</div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div><div class="gmail_default" \
style="font-family:arial,helvetica,sans-serif">David J.</div><div \
class="gmail_default" \
style="font-family:arial,helvetica,sans-serif"><br></div></div></div></div>



[prev in list] [next in list] [prev in thread] [next in thread]