Відео

@necrotikS should be fairly easy by using a partial index, i.e. just append the following: where status = 'SCHEDULED' ...to the index definition

@@cybertecpostgresql Yes, I had already tried using this WHERE clause, however, the only thing that it changes is that it checks for overlapping when the new row has status "SCHEDULED". But I need the check to run for any status, and throw the error if overlapping with any "SCHEDULED" appointments. With this WHERE you told me to use, I can have a appointment with status "PENDING" overlapping with an already existing appointment with status "SCHEDULED", but that can never happen. It only works for creating a new appointment with status "SCHEDULED", then, it throws the error.

@@necrotikS The easy options to deal with the status column in an exclusion constraint are: * ignore it * exclude (status with = ) * only look at SCHEDULED - where (status = 'SCHEDULED') What you are asking for is "exclude if one of the status values is 'SCHEDULED'" which is unfortunately more complicated. The idea would be to create a new operator to exclude on in place of = which check if either argument is 'SCHEDULED'. That part is easy: create function one_is_scheduled (a text, b text) returns boolean language sql return 'scheduled' in (a, b) ; create operator ==*== ( leftarg = text, rightarg = text, function = one_is_scheduled, commutator = ==*==); And then define the table as follows: create extension btree_gist; create table events ( field_id int, status text, time tstzrange, exclude using gist ( field_id with =, status with ==*==, time with && ) ); However, that is not enough: ERROR: 42809: operator ==*==(text,text) is not a member of operator family "gist_text_ops" DETAIL: The exclusion operator must be related to the index operator class for the constraint. At this point, I'd just give up since defining operator families is a quite deep rabbit hole. On a side note, exclusion constraints are symmetric. This means, that while if the above worked, it would prevent any new inserts of a PENDING event if a matching SCHEDULED event is already there, it would also prevent a SCHEDULED event to be inserted if a PENDING event is also there. Not sure this would be desired in the workflow you describe. I'd be curious if anyone finds a clever solution that works, but my current guess would be it's not easy.

@@ChristophBergPG thank you for the answer. Well, I guess I'll stick to code validation. Probably I'll change the columns I already have to a single range column (currently I don't use it), and before inserting any new rows, I use the available range operators to check for overlapping and status, since they're very good and simpler than my current SQL queries for finding that.

Perhaps as a practical answer: I would probably go with the "where (status = 'SCHEDULED') partial exclusion constraint that solves the problem in the strict sense that no two events can be scheduled in parallel, and deal with the "soft" part of the problem on the application side by simply checking for any conflicting events before inserting a PENDING one.

Hi, here is the documentation for the customized yaml setup pgwatch2.readthedocs.io/en/latest/custom_installation.html?highlight=yaml#yaml-based-setup if it doesn't work you can open an issue on Github github.com/cybertec-postgresql/pgwatch2 Hope that helps 🙂

@MuhammadFahreza there are some dashboards for this, e.g. "Stat statements Top". You can check it out on our demo site: demo.pgwatch.com/d/stat-statements-top/stat-statements-top

Well, every system has different ways of handling those things. Collations are never easy as they change sort order which of course impacts sort order and all that. We believe there can never be an ideal solution.

Hi Domien Bakker, Thanks a lot for your input. ora2pg is indeed an excellent product and for small migrations certainly has a lot of incentives. However, for large and complex databases, it tends to be very slow and complicated. Especially, when talking about downtime, the CYBERTEC Migrator excels at data load speed (NIC saturation) and the UI is very user-friendly. We have a lot of excellent staff working on this project and also offer commercial support and assistance with migrations. Contact us if you need more information! Cheers, CYBERTEC team