Check constraints do not seem to be working!!!
Consider this table definition: create table t ( i serial, b bool, c bool, constraint b_c check ( (b = true and c is not null ) or (b is distinct from true and c is null) ) constraint b_c check ( (b = true and c is not null ) or (b = false and c is null) or (b is null and c is null) ) ); Despite the above two constraints, the following rows get into the table: insert into t (b , c) values (null, true), (null, false);
Re: Check constraints do not seem to be working!!!
Thanks Nikolay I read that but is there a way to meet the above requirement. And I will like to add that IS NULL and IS NOT NULL should evaluate to true/false. These operators are made for this and should not be returning NULL. Regards, Jitendra On Wed 11 Nov, 2020, 14:18 Nikolay Samokhvalov, wrote: > On Wed, Nov 11, 2020 at 12:26 AM Jitendra Loyal > wrote: > >> Despite the above two constraints, the following rows get into the table: >> insert into t (b , c) values (null, true), (null, false); >> > > This behavior is described in the docs > https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS > : > > > It should be noted that a check constraint is satisfied if the check > expression evaluates to true or the null value. Since most expressions will > evaluate to the null value if any operand is null, they will not prevent > null values in the constrained columns. To ensure that a column does not > contain null values, the not-null constraint described in the next section > can be used. >
Re: Check constraints do not seem to be working!!!
Thanks Tomas Understood... My bad Was just not looking at that aspect Thanks once again, Regards, Jitendra On Wed, 11 Nov 2020 at 16:17, Tomas Vondra wrote: > > On 11/11/20 10:06 AM, Jitendra Loyal wrote: > > Thanks Nikolay > > > > I read that but is there a way to meet the above requirement. And I will > > like to add that IS NULL and IS NOT NULL should evaluate to true/false. > > These operators are made for this and should not be returning NULL. > > > > This has nothing to do with IS [NOT] NULL, it's the first part of the > expression (b = TRUE) causing trouble. Essentially, the constraint > > (b = true) and (c is not null) > > is evaluated in two steps. First we evaluate the two parts individually, > and for (null, true) the results would look like this: > > (b = true) => null > (c is not null) => true > > and then we combine those results using 'AND' > > null AND true => null > > which is considered as if the constraint matches. If you want to handle > NULL for the first expression, you may do this, for example: > > (b it not null and b = true) and (c is not null) > > Or something like that. > > > regards > > -- > Tomas Vondra > EnterpriseDB: http://www.enterprisedb.com > The Enterprise PostgreSQL Company >
Re: Check constraints do not seem to be working!!!
Thanks Laurenz This is interesting...b is True Thanks and regards, Jitendra On Wed 11 Nov, 2020, 22:52 Laurenz Albe, wrote: > On Wed, 2020-11-11 at 11:47 +0100, Tomas Vondra wrote: > > you may do this, for example: > > > > (b it not null and b = true) and (c is not null) > > > > Or something like that. > > My (equivalent) suggestion: > >b IS TRUE AND c IS NOT NULL > > Yours, > Laurenz Albe > -- > Cybertec | https://www.cybertec-postgresql.com > >
BEFORE ... Statement-level trigger
The behaviour is not clear for BEFORE Statement-level Trigger. This is because transition tables cannot be used. So how does one get access to the rows being affected? It is not documented either. Thanks Jiten
Statement-level trigger results in recursion
The AFTER Statement-level Trigger runs into infinite execution when another set of rows are affected for the same table through this trigger. Consider this use case where a table storage_locations that manages a hierarchy of storage_locations in stores, and thus having following columns (for simplicity): storage_location_id SERIAL NOT NULL PRIMARY KEY, store_id INTEGER NOT NULL, -- REFERENCES stores storage_location_nm VARCHAR (25) NOT NULL, parent_storage_location_id INTEGER NULL REFERENCES storage_locations, NULL for root storage locations storage_location_path TEXT NOT NULL I have a BEFORE ROW trigger, which updates the storage_location_path with parent's storage_location_path, if any, concatenated with its storage_location_name. This works fine - no issues. I have another AFTER UPDATE STATEMENT-level Trigger and function definitions as below (which updates the storage_path of the children): CREATE FUNCTION TRG_storage_locations_b_u_AS_DML () RETURNS TRIGGER AS $$ DECLARE v_separator VARCHAR (1) = '/'; v_cnt INT; BEGIN -- [ -- Required to prevent infinite recursion SELECT COUNT (*) INTO v_cnt FROM new_table; IF (v_cnt > 0) THEN -- ] -- Required to prevent infinite recursion UPDATE storage_locations SET storage_location_path = COALESCE (i.storage_location_path || v_separator, '') || storage_locations.storage_location_nm FROM inserted i JOIN deleted d ON ( i.storage_location_id = d.storage_location_id AND i.storage_location_path != d.storage_location_path ) WHERE storage_locations.parent_storage_location_id = i.storage_location_id; END IF; RETURN NULL; END $$ LANGUAGE plpgsql; CREATE TRIGGER storage_locations_b_u_AS_DML AFTER UPDATE ON storage_locations REFERENCING NEW TABLE AS inserted OLD TABLE AS deleted FOR EACH STATEMENT EXECUTE FUNCTION TRG_storage_locations_b_u_AS_DML (); Notice that the Trigger is getting called endlessly (if the number of rows in the NEW TABLE are NOT checked). I reckon if there are not any rows, what is the need to call the trigger. Or, may be, I am missing something, which I need to learn. Thanks, Jiten
Re: BEFORE ... Statement-level trigger
Thanks Adrian I am trying to understand as to how a BEFORE statement-level trigger can be used. Since it is a trigger, one needs to know which rows are being affected. Regards, Jiten On Mon 18 Feb, 2019, 9:42 PM Adrian Klaver, wrote: > On 2/18/19 4:06 AM, Jitendra Loyal wrote: > > The behaviour is not clear for BEFORE Statement-level Trigger. This is > > because transition tables cannot be used. So how does one get access to > > the rows being affected? It is not documented either. > > If you need the row values then use a FOR ROW trigger. > > > > > Thanks > > Jiten > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Statement-level trigger results in recursion
Unfortunately no! Where can I see those? Will I don't my answer there; I have referred to the documentation and tried various things. Thanks and regards, Ken i On Mon 18 Feb, 2019, 9:59 PM Adrian Klaver, wrote: > On 2/18/19 8:23 AM, Jitendra Loyal wrote: > > My bad! > > > > It is a transition table. Consider the following revised definition of > > trigger: > > > > > > CREATE TRIGGER storage_locations_b_u_AS_DML > > AFTER UPDATE > > ON storage_locations > > REFERENCING NEW TABLE AS new_table > > OLD TABLE AS old_table > > FOR EACH STATEMENT EXECUTE FUNCTION TRG_storage_locations_b_u_AS_DML (); > > Alright I understand now. > > Did you see the rest of my previous post about AFTER STATEMENT running > regardless of number of rows affected? > > > > > Thanks and regards, > > Jiten > > > > On Mon 18 Feb, 2019, 9:32 PM Adrian Klaver, > <mailto:adrian.kla...@aklaver.com>> wrote: > > > > On 2/18/19 4:11 AM, Jitendra Loyal wrote: > > > > > > The AFTER Statement-level Trigger runs into infinite execution > when > > > another set of rows are affected for the same table through this > > > trigger. Consider this use case where a table storage_locations > that > > > manages a hierarchy of storage_locations in stores, and thus > having > > > following columns (for simplicity): > > > > > > > > > > > > > > > storage_location_id SERIAL NOT NULL PRIMARY KEY, > > > store_id INTEGER NOT NULL, -- REFERENCES stores > > > storage_location_nm VARCHAR (25) NOT NULL, > > > parent_storage_location_id INTEGER NULL REFERENCES > > storage_locations, > > > NULL for root storage locations > > > storage_location_path TEXT NOT NULL > > > > > > > > > > > > > > > > > > I have a BEFORE ROW trigger, which updates the > > storage_location_path with > > > parent's storage_location_path, if any, concatenated with its > > > storage_location_name. This works fine - no issues. > > > > > > I have another AFTER UPDATE STATEMENT-level Trigger and function > > definitions > > > as below (which updates the storage_path of the children): > > > > > > > > > > > > > > > CREATE FUNCTION TRG_storage_locations_b_u_AS_DML () > > > RETURNS TRIGGER > > > AS $$ > > > DECLARE > > > v_separator VARCHAR (1) = '/'; > > > v_cnt INT; > > > BEGIN > > > -- [ -- Required to prevent infinite recursion > > > SELECT COUNT (*) INTO v_cnt > > > FROM new_table; > > > > Where is new_table coming from? > > > > > > > > IF (v_cnt > 0) THEN > > > -- ] -- Required to prevent infinite recursion > > > UPDATE storage_locations > > > SET storage_location_path = COALESCE (i.storage_location_path || > > > v_separator, '') || storage_locations.storage_location_nm > > > FROM inserted i > > > JOIN deleted d > > > ON ( i.storage_location_id = d.storage_location_id > > > AND i.storage_location_path != d.storage_location_path > > > ) > > > WHERE storage_locations.parent_storage_location_id = > > i.storage_location_id; > > > END IF; > > > RETURN NULL; > > > END > > > $$ LANGUAGE plpgsql; > > > > > > CREATE TRIGGER storage_locations_b_u_AS_DML > > > AFTER UPDATE > > > ON storage_locations > > > REFERENCING NEW TABLE AS inserted > > > OLD TABLE AS deleted > > > FOR EACH STATEMENT EXECUTE FUNCTION > > TRG_storage_locations_b_u_AS_DML (); > > > > > > Notice that the Trigger is getting called endlessly (if the > > number of > > > rows in the NEW TABLE are NOT checked). I reckon if there are not > > any > > > rows, what is the need to call the trigger. Or, may be, I am > missing > > > something, which I need to learn. > > > > Yes: > > > > https://www.postgresql.org/docs/10/sql-createtrigger.html > > > > "... In contrast, a trigger that is marked FOR EACH STATEMENT only > > executes once for any given operation, regardless of how many rows it > > modifies (in particular, an operation that modifies zero rows will > > still > > result in the execution of any applicable FOR EACH STATEMENT > triggers)." > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Thanks, > > > > > > > > > > > > > > > > > > Jiten > > > > > > > > > > > > > > > > > > -- > > Adrian Klaver > > adrian.kla...@aklaver.com <mailto:adrian.kla...@aklaver.com> > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: Statement-level trigger results in recursion
I do understand that the statement level trigger will be executed once before the operation. My point is.. if one does not know the rows, what kind of use it can be put to. What is the use case? Like in after triggers, one gets the rows in transition tables, how does one do with vefore trigger. Thanks and regards, Jiten
Re: Statement-level trigger results in recursion
My bad! It is a transition table. Consider the following revised definition of trigger: CREATE TRIGGER storage_locations_b_u_AS_DML AFTER UPDATE ON storage_locations REFERENCING NEW TABLE AS new_table OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION TRG_storage_locations_b_u_AS_DML (); Thanks and regards, Jiten On Mon 18 Feb, 2019, 9:32 PM Adrian Klaver, wrote: > On 2/18/19 4:11 AM, Jitendra Loyal wrote: > > > > The AFTER Statement-level Trigger runs into infinite execution when > > another set of rows are affected for the same table through this > > trigger. Consider this use case where a table storage_locations that > > manages a hierarchy of storage_locations in stores, and thus having > > following columns (for simplicity): > > > > > > > > > > storage_location_id SERIAL NOT NULL PRIMARY KEY, > > store_id INTEGER NOT NULL, -- REFERENCES stores > > storage_location_nm VARCHAR (25) NOT NULL, > > parent_storage_location_id INTEGER NULL REFERENCES storage_locations, > > NULL for root storage locations > > storage_location_path TEXT NOT NULL > > > > > > > > > > > > I have a BEFORE ROW trigger, which updates the storage_location_path with > > parent's storage_location_path, if any, concatenated with its > > storage_location_name. This works fine - no issues. > > > > I have another AFTER UPDATE STATEMENT-level Trigger and function > definitions > > as below (which updates the storage_path of the children): > > > > > > > > > > CREATE FUNCTION TRG_storage_locations_b_u_AS_DML () > > RETURNS TRIGGER > > AS $$ > > DECLARE > > v_separator VARCHAR (1) = '/'; > > v_cnt INT; > > BEGIN > > -- [ -- Required to prevent infinite recursion > > SELECT COUNT (*) INTO v_cnt > > FROM new_table; > > Where is new_table coming from? > > > > > IF (v_cnt > 0) THEN > > -- ] -- Required to prevent infinite recursion > > UPDATE storage_locations > > SET storage_location_path = COALESCE (i.storage_location_path || > > v_separator, '') || storage_locations.storage_location_nm > > FROM inserted i > > JOIN deleted d > > ON ( i.storage_location_id = d.storage_location_id > > AND i.storage_location_path != d.storage_location_path > > ) > > WHERE storage_locations.parent_storage_location_id = > i.storage_location_id; > > END IF; > > RETURN NULL; > > END > > $$ LANGUAGE plpgsql; > > > > CREATE TRIGGER storage_locations_b_u_AS_DML > > AFTER UPDATE > > ON storage_locations > > REFERENCING NEW TABLE AS inserted > > OLD TABLE AS deleted > > FOR EACH STATEMENT EXECUTE FUNCTION TRG_storage_locations_b_u_AS_DML (); > > > > Notice that the Trigger is getting called endlessly (if the number of > > rows in the NEW TABLE are NOT checked). I reckon if there are not any > > rows, what is the need to call the trigger. Or, may be, I am missing > > something, which I need to learn. > > Yes: > > https://www.postgresql.org/docs/10/sql-createtrigger.html > > "... In contrast, a trigger that is marked FOR EACH STATEMENT only > executes once for any given operation, regardless of how many rows it > modifies (in particular, an operation that modifies zero rows will still > result in the execution of any applicable FOR EACH STATEMENT triggers)." > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > Thanks, > > > > > > > > > > > > Jiten > > > > > > > > > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: BEFORE ... Statement-level trigger
I have gone through the documentation quite a number of times to establish the understanding. However, I had been wondering about the recursion in the case I put forth. Is there a better way to handle this requirement? The point is that the trigger is being called when no rows are affected. Thanks and regards, Jiten
Re: Statement-level trigger results in recursion
Thanks for all your efforts. I appreciate it. Let us wait and see if someone can enlighten us, or you locate the conversation. Thanks once again Regards, Jiten On Tue 19 Feb, 2019, 3:19 AM Adrian Klaver, wrote: > On 2/18/19 9:07 AM, Jitendra Loyal wrote: > > I do understand that the statement level trigger will be executed once > > before the operation. My point is.. if one does not know the rows, what > > kind of use it can be put to. What is the use case? Like in after > > triggers, one gets the rows in transition tables, how does one do with > > vefore trigger. > > Use FOR EACH ROW. > > Why you cannot use a FOR EACH STATEMENT trigger is something I thought I > remember being discussed on the list before. Unfortunately I cannot find > that conversation at the moment. Someone else will need to weigh in on > this. > > > > > Thanks and regards, > > Jiten > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
Re: BEFORE ... Statement-level trigger
I will not prefer to use a row trigger on this case for that will be relatively inefficient. So can we conclude that a sanity check is essential when using statement level trigger. Thanks and regards, Jiten
RECURSIVE allowed only ONCE in a CTE
I find that the RECURSIVE can be used only once in a CTE. I have the following use-case where there is a hierarchy of store_groups, and then there are stores associated with a store_group. Requirement is to ensure that a store can be used only once in a store group hierarchy. Following definitions help: CREATE TABLE store_groups ( store_group_id SERIAL NOT NULL CONSTRAINT StoreGroups_PK_StoreGroupID PRIMARY KEY, store_group_nm STXT NOT NULL, -- On update, parent_store_group_id should not exist in the sub-tree of store_group_id (if any). This is to prevent cycles. Trigger ensures the same. -- Another trigger ensures that the stores are unique in the new store group hierarchy. parent_store_group_id INTEGER NULL CONSTRAINT StoreGroups_FK_ParentStoreGroupID REFERENCES store_groups, CONSTRAINT StoreGroups_UK_ParentStoreGroupID_StoreGroupName UNIQUE ( parent_store_group_id, store_group_nm ) ); CREATE TABLE store_group_stores ( store_group_store_id SERIAL NOT NULL CONSTRAINT StoreGroupStores_PK_StoreID PRIMARY KEY, store_group_id INTEGER NOT NULL CONSTRAINT StoreGroupStores_FK_StoreGroupID REFERENCES store_groups, -- Trigger ensures that a store exists only once in a Group hierarchy store_id INTEGER NOT NULL -- CONSTRAINT StoreGroupStores_FK_StoreID -- REFERENCES stores, -- Display order of the store in the Store Group -- If display_order is not specified, stores should be listed in alphabetical order store_seq INTEGER NULL, CONSTRAINT StoreGroupStores_UK_StoreGroupID_StoreID UNIQUE ( store_group_id, store_id ) ); To meet the above need, I was writing a funciton (which will be called from triigers) with following algorithm: 1. root_group CTE to get the root store group (needs RECURSIVE) 2. all_groups to collect all the store groups in root_group (needs RECURSIVE) On the second use, I get syntax error. Kindly confirm that RECURSIVE can be used only once. I will find an alternate mechanism. Thanks, Jitendra Loyal
Re: RECURSIVE allowed only ONCE in a CTE
Thanks Andrew I will try this and revert; I was specifying RECURSIVE for the second CTE as well. Regards, Jitendra
Re: RECURSIVE allowed only ONCE in a CTE
Thanks Got it Regards, Jitendra On Fri 22 Feb, 2019, 4:03 PM mariusz On Fri, 22 Feb 2019 13:23:11 +0530 > Jitendra Loyal wrote: > > > I find that the RECURSIVE can be used only once in a CTE. > > > > I have the following use-case where there is a hierarchy of > > store_groups, and then there are stores associated with a > > store_group. Requirement is to ensure that a store can be used only > > once in a store group hierarchy. Following definitions help: > > > > CREATE TABLE store_groups > > ( > > store_group_id SERIAL NOT NULL CONSTRAINT StoreGroups_PK_StoreGroupID > > PRIMARY KEY, > > store_group_nm STXT NOT NULL, > > -- On update, parent_store_group_id should not exist in the sub-tree > > of store_group_id (if any). This is to prevent cycles. Trigger > > ensures the same. > > -- Another trigger ensures that the stores are unique in the new store > > group hierarchy. > > parent_store_group_id INTEGER NULL CONSTRAINT > > StoreGroups_FK_ParentStoreGroupID > > REFERENCES store_groups, > > CONSTRAINT StoreGroups_UK_ParentStoreGroupID_StoreGroupName > > UNIQUE ( parent_store_group_id, store_group_nm ) > > ); > > > > CREATE TABLE store_group_stores > > ( > > store_group_store_id SERIAL NOT NULL CONSTRAINT > > StoreGroupStores_PK_StoreID PRIMARY KEY, > > store_group_id INTEGER NOT NULL CONSTRAINT > > StoreGroupStores_FK_StoreGroupID REFERENCES store_groups, > > -- Trigger ensures that a store exists only once in a Group hierarchy > > store_id INTEGER NOT NULL -- CONSTRAINT StoreGroupStores_FK_StoreID > > -- REFERENCES stores, > > -- Display order of the store in the Store Group > > -- If display_order is not specified, stores should be listed in > > alphabetical order > > store_seq INTEGER NULL, > > CONSTRAINT StoreGroupStores_UK_StoreGroupID_StoreID > > UNIQUE ( store_group_id, store_id ) > > ); > > > > To meet the above need, I was writing a funciton (which will be > > called from triigers) with following algorithm: > > > >1. root_group CTE to get the root store group (needs RECURSIVE) > >2. all_groups to collect all the store groups in root_group (needs > >RECURSIVE) > > > > On the second use, I get syntax error. Kindly confirm that RECURSIVE > > can be used only once. I will find an alternate mechanism. > > > > Thanks, > > Jitendra Loyal > > hi, > > you CAN use multiple recursive ctes within one query, i've done this > many times without issues having two or three recursive ctes between > nonrecursive ones. > > you probably assumed that you need RECURSIVE keyword applied to cte, > and got syntax errors with multiple RECURSIVE keywords. > > what you do is declare WITH RECURSIVE (RECURSIVE added to WITH keyword) > stating that following ctes may contain recursive one (maybe more, and > the recursive one need not be the first one). > > what makes cte a recursive one is it's content, that is union > referencing self-cte within from clause. > > i admit that i haven't analyzed your store case to make even a simple > working example of what you need, neither i'm pasting any exapmles of > my own real queries (one i got open in terminal just now has about 400 > lines, 8 cte, 3 of which are recursive, that would rather be counter > productive as a working example) > > just wanted to assure you that multiple recursive ctes within one query > are possible and need only one RECURSIVE keyword appended to WITH > keyword. > > regards, mariusz >
Use case for BEFORE STATEMENT level trigger
I had been wondering as to where one can use BEFORE STATEMENT level trigger, more so because one does not know (access) what rows are getting affected. Only thing which comes to my mind is that if one wants to do something at a table-level, then this trigger can be used; this is quite unusual though as I have not experienced this need in more than two decades of developing business applications. I am looking forward to some one who can enlighten me with the use case because I may be missing something. Thanks
