Nguyen Hoai Nam wrote:
> In fact, in mysql, trigger can detect this case. But when I apply this method to Postgresql, it's not
> ok. Do you know why?
I cannot tell you about MySQL, but if you insert two rows at the same time
in PostgreSQL, the SQL queries run from the trigger can run concurrently.
None of them will see the row of the other insert, because this will become
only visible when the transaction (which includes the execution trigger)
has completed.
> I will follow your suggestion. However imaging we add a "network_id" column (it contain the id of
> network) to "subnet" tables.. so I need to do what to prevent a record with overlap CIDR and same
> network_id.?
There is no need to add a new column, just add a constraint.
If the table was defined with a varchar column like this:
CREATE TABLE network (id integer PRIMARY KEY, subnet varchar(30) NOT NULL);
Add the following constraint:
ALTER TABLE network ADD CONSTRAINT network_subnet_excl EXCLUDE USING gist ((subnet::cidr) inet_ops WITH &&);
Then you are done.
Yours,
Laurenz Albe
