Indexes, constraints, and deferred

[NotionCommotion]

I am using Doctrine and also wish to utilize PostgreSQL's deferred constraints.  It is my understanding that Doctrine is not aware of deferred constraints and while I would like to keep the amount of behind the scenes modification to a minimum, am okay with making manual modifications to the schema.  My use cases are being able to perform within a transaction adding entities which contain another entities key and the other entity also contains theirs and updating some unique series which spans multiple entities without violating unique constraints.

Originally, I thought I would just have Doctrine create the unique constraint using @UniqueConstraint and manually make it deferred, however, it appears that Doctrine doesn't create a constraint but a unique index.  Then I tried adding a constraint with the same name as the index, however, didn't go as there was already a constraint or index on the table.  Which then made me realize I really didn't understand the implications of indexes and constraints.

Are they somewhat the same thing or totally different.  Can they share the same name?  If I create a constraint, it is my understanding that PostgreSQL will automatically create an index for it.  Can I manually create one first and then it will use it?  When dropping indexes, must any constraints that use that index be dropped first?  Can a constraint be altered or must it first be dropped and then re-added?

Please know that I have tried all of the above, however, have not reached a conclusion on what it means and would like to have better insight on their implications.

Thanks

[requinix]

Whatever the problem is, I doubt needing deferrable constraints is the answer.

Why do you think you need them?

[NotionCommotion]

1 hour ago, requinix said:

Whatever the problem is, I doubt needing deferrable constraints is the answer.

Why do you think you need them?

They just seem like a simple and reliable solution for some scenarios.  Initiate a transaction, do the work, and commit.

Examples include:

• Chicken or the egg scenario.  A person can create an account where that account is also available to the individual's associates and each account needs a single administrator which will be the individual who initially set up the account, but then can later be changed.  My MySQL solution was temporary disabling foreign keys and PostgreSQL was temporarily disabling triggers so I can utilize FK constraints.  Another similar example is any time one needs some user defined default value entity for their account where this default value entity references their account entity and their account entity references this default value entity.
• When uniqueness is required.  I brought up the example of order of lists a while back, and while some had the opinion that it was not critical, it is kind of important and I was having difficulty implementing it for some more complex cases where it wasn't just some one dimensional series.   I have since creates multiple entities which each handle their own responsibilities and it is much more robust, however, having the database also enforces uniqueness would be very nice.

Are there drawbacks to using deferrable constraints?  I expect some decrease in performance which is a small price to pay when they make sense.  Do you not like them and if so why?

Thanks

 

[requinix]

18 minutes ago, NotionCommotion said:

They just seem like a simple and reliable solution for some scenarios.  Initiate a transaction, do the work, and commit.

You don't need to defer constraints to use transactions.

Quote

Chicken or the egg scenario.  A person can create an account where that account is also available to the individual's associates and each account needs a single administrator which will be the individual who initially set up the account, but then can later be changed.  My MySQL solution was temporary disabling foreign keys and PostgreSQL was temporarily disabling triggers so I can utilize FK constraints.

I can't tell for sure but it sounds like you're establishing a circular dependency somewhere in there.

Quote

Another similar example is any time one needs some user defined default value entity for their account where this default value entity references their account entity and their account entity references this default value entity.

Can either of those entities exist in their own right without the other?

Also, circular dependency. Rethink the architecture.

Quote

When uniqueness is required.  I brought up the example of order of lists a while back, and while some had the opinion that it was not critical, it is kind of important and I was having difficulty implementing it for some more complex cases where it wasn't just some one dimensional series.   I have since creates multiple entities which each handle their own responsibilities and it is much more robust, however, having the database also enforces uniqueness would be very nice.

That's a very good reason to not defer the uniqueness...

Quote

Are there drawbacks to using deferrable constraints?  I expect some decrease in performance which is a small price to pay when they make sense.  Do you not like them and if so why?

Deferring a constraint means you need to perform a series of operations where a constraint must be violated. So before saying deferring is the answer, think about whether you really do have to violate a constraint. If the answer is not "no", think again.

There is only one good reason I know of to do this: importing lots of data. Constraints necessarily slow operations, and slowing a lot of data means it takes longer to load it all. That's when you use deferred constraints, except the process goes:

1. Start with tables that are not already deferred - because there was no reason for them to be deferred until now
2. Modify to enable deferring
3. Start a transaction and begin deferring
4. Load all the data for all the tables
5. Commit
6. Modify the tables back to not allow deferring

And remember: this is all my opinion. I'm sure there are DBAs out there who believe that all constraints should be deferrable, or that there are more lax requirements for doing it, or who otherwise disagree with me. And that's okay.

[NotionCommotion]

Thank you requinix,  I appreciate your advise.  I will give thought to your questioning about circular dependency.

Maybe I misunderstand the purpose of referring.  Let me give more background.

I have these objects that reflect real-time environmental data.  They are injected in this point object that reflects either real-time or historic time values and/or sums multiple real-time values.  For the chart application, these points are injected into data objects, these data objects are injected in series objects, and series objects are injected in chart objects.  Now a bar chart might have categories such as country which are common to multiple series such as population over time, however, a pie chart doesn't have this concept of categories and the user interacts with the data differently based on the chart type.  To keep it simple, when a request to modify an entity arrives, I pull from the database and create the entity, add/move/delete/modify the entity as necessary, and then save it back to the database.  But I am not doing everything manually but am using Doctrine, and Doctrine doesn't know the exact order to execute the inserts, deletes, or updates.  I give it an entity which is valid and doesn't violate any constraints, but it is only valid in its final state and executing individual queries to get it there will result in constraint violations.

My hopes were make the constraints deferred, wrap it in a transaction, let Doctrine save each table as it wants without working about constraints, commit, and then let PostgreSQL choke if it doesn't like what it was given.

Is this not the purpose of deferred constraints or am I off base?

[requinix]

They could be used that way, but I would not.

How are your dependencies set up in code? Are you using relationships? Doctrine should be able to save in the right order but can only do so if it knows about the relationships between all objects.

I haven't really used Doctrine myself, but I would expect that you should be able to construct the whole chart hierarchy in code, with objects contained in other objects, and then tell Doctrine to save the whole thing. It would then combine that with its knowledge of your foreign keys to save the the dependencies before whatever is using them.

[NotionCommotion]

9 hours ago, requinix said:

They could be used that way, but I would not.

Good.  At least I wasn't totally off on what they are.  And you wouldn't because you shouldn't need to as Doctrine should know?  Yes, I am using relationships and think I am doing everything correct, but agree Doctrine should know and I better take a step back before relying on my deferred constraint band-aide.

PS.  Regardless of whether one "should" do this, would appreciate your thoughts on my original questions.

Edited April 26, 2020 by NotionCommotion

[kicken]

Doctrine does it's queries in a more or less fixed sequence that is designed around entity relationships and foreign keys.  See this old bug report (UnitOfWork commit order problem) and the code for information.

That doesn't always play well with other unique constraints, as you discovered in your list ordering problem.   As mentioned in that thread though, there's something to be said for over-using unique constraints, make sure you really need one.

For situations doctrine doesn't handle well then the work around is usually to use force queries to run in a particular order by using EntityManager::flush() frequently.

I don't use pgsql so I'm not familiar with the deferred constraints but if they seem to provide a solution then it may be worth exploring.  However I'd first look at the code and see if there is a solution there through a different design or just removing the constraint. 

[NotionCommotion]

Thanks kicken,  Like the bug report.  Now it is obvious.  Maybe I should just ditch the unique constraints but that is a slippery slope.   Guess the multiple flush solution is easy enough.  Still like the idea of constraints which are only based on the final state of the entity and will investigate and post my findings.

 

Quote

 

The order is:

• all entity inserts (topological order)
• all entity updates (topological order)
• all collection deletions
• all collection updates
• all entity deletions (reverse toplogical order)

 

•  

[NotionCommotion]

Getting the results I was looking for as shown below.  While I was certain there would no issues with Doctrine, I've also tested that as being successful.  I do feel it is a clean solution, however, will post future negative implications should they arise.

greenbeand=# SELECT * FROM chart_series WHERE chart_id=638;
 id  | chart_id |  name   | position | type
-----+----------+---------+----------+-------
 310 |      638 | Series1 |        1 | point
 345 |      638 | fasdd   |        2 | point
(2 rows)

# OUTSIDE OF TRANSACTION
greenbeand=# UPDATE public.chart_series SET position = 1 WHERE id = 310;
ERROR:  duplicate key value violates unique constraint "unique_position_serie"
DETAIL:  Key (chart_id, "position")=(638, 1) already exists.

# COMMITTING BEFORE COMPLETE
greenbeand=# START TRANSACTION;
START TRANSACTION
greenbeand=# UPDATE public.chart_series SET position = 2 WHERE id = 310;
UPDATE 1
greenbeand=# COMMIT TRANSACTION;
ERROR:  duplicate key value violates unique constraint "unique_position_serie"
DETAIL:  Key (chart_id, "position")=(638, 2) already exists.

# SUCCESS
greenbeand=# START TRANSACTION;
START TRANSACTION
greenbeand=# UPDATE public.chart_series SET position = 2 WHERE id = 310;
UPDATE 1
greenbeand=# UPDATE public.chart_series SET position = 1 WHERE id = 345;
UPDATE 1
greenbeand=# COMMIT TRANSACTION;
COMMIT
greenbeand=# SELECT * FROM chart_series WHERE chart_id=638;
 id  | chart_id |  name   | position | type
-----+----------+---------+----------+-------
 310 |      638 | Series1 |        2 | point
 345 |      638 | fasdd   |        1 | point
(2 rows)

greenbeand=#

 

On 4/25/2020 at 1:07 PM, NotionCommotion said:

...Which then made me realize I really didn't understand the implications of indexes and constraints.

Are they somewhat the same thing or totally different.  Can they share the same name?  If I create a constraint, it is my understanding that PostgreSQL will automatically create an index for it....

While I very much appreciate the help getting me to where I am, was still trying to understand the difference between unique indexes and unique constraints regardless of whether they are deferred.  The first output below uses a unique index and the second uses a deferred unique constraint.  The relevant two lines are:

1.  UNIQUE, btree (chart_id, "position")
2.  UNIQUE CONSTRAINT, btree (chart_id, "position") DEFERRABLE INITIALLY DEFERRED

Is there an index on the second or just a unique constraint?
 

greenbeand=# \d+ chart_series
                                                        Table "public.chart_series"
  Column  |         Type          | Collation | Nullable |                 Default                  | Storage  | Stats target | Description
----------+-----------------------+-----------+----------+------------------------------------------+----------+--------------+-------------
 id       | integer               |           | not null | nextval('chart_series_id_seq'::regclass) | plain    |              |
 chart_id | integer               |           | not null |                                          | plain    |              |
 name     | character varying(45) |           | not null |                                          | extended |              |
 position | integer               |           | not null |                                          | plain    |              |
 type     | character varying(32) |           | not null |                                          | extended |              |
Indexes:
    "chart_series_pkey" PRIMARY KEY, btree (id)
    "unique_name_serie" UNIQUE, btree (chart_id, name)
    "unique_position_serie" UNIQUE, btree (chart_id, "position")
    "idx_582b2d4dbef83e0a" btree (chart_id)
Foreign-key constraints:
    "fk_582b2d4dbef83e0a" FOREIGN KEY (chart_id) REFERENCES chart(id) ON DELETE CASCADE
Referenced by:
    TABLE "time_chart_series" CONSTRAINT "fk_81234495bf396750" FOREIGN KEY (id) REFERENCES chart_series(id) ON DELETE CASCADE
    TABLE "point_chart_series" CONSTRAINT "fk_aea4d429bf396750" FOREIGN KEY (id) REFERENCES chart_series(id) ON DELETE CASCADE
Access method: heap

greenbeand=# \d+ chart_series;
                                                        Table "public.chart_series"
  Column  |         Type          | Collation | Nullable |                 Default                  | Storage  | Stats target | Description
----------+-----------------------+-----------+----------+------------------------------------------+----------+--------------+-------------
 id       | integer               |           | not null | nextval('chart_series_id_seq'::regclass) | plain    |              |
 chart_id | integer               |           | not null |                                          | plain    |              |
 name     | character varying(45) |           | not null |                                          | extended |              |
 position | integer               |           | not null |                                          | plain    |              |
 type     | character varying(32) |           | not null |                                          | extended |              |
Indexes:
    "chart_series_pkey" PRIMARY KEY, btree (id)
    "unique_name_serie" UNIQUE, btree (chart_id, name)
    "unique_position_serie" UNIQUE CONSTRAINT, btree (chart_id, "position") DEFERRABLE INITIALLY DEFERRED
    "idx_582b2d4dbef83e0a" btree (chart_id)
Foreign-key constraints:
    "fk_582b2d4dbef83e0a" FOREIGN KEY (chart_id) REFERENCES chart(id) ON DELETE CASCADE
Referenced by:
    TABLE "time_chart_series" CONSTRAINT "fk_81234495bf396750" FOREIGN KEY (id) REFERENCES chart_series(id) ON DELETE CASCADE
    TABLE "point_chart_series" CONSTRAINT "fk_aea4d429bf396750" FOREIGN KEY (id) REFERENCES chart_series(id) ON DELETE CASCADE
Access method: heap

 

[requinix]

1 hour ago, NotionCommotion said:

While I very much appreciate the help getting me to where I am, was still trying to understand the difference between unique indexes and unique constraints regardless of whether they are deferred.

They're basically the same thing. A constraint exists to enforce some rule on the table, and an index exists to support queries. A unique constraint enforces uniqueness, and the way PostgreSQL implements that is by setting up a unique index.

In other words, you just about always want a constraint. Because you want to enforce uniqueness. The index created for you is a bonus.