Re: [phpOpenTracker-User] Duplicate key insertion (bug?) with postgreSQL

I have come up with a new solution that will not affect other databases
except for postgres.

The solution is to add a before insert trigger to each table to which
you might try and insert a duplicate record that would generate a
primary key violation error. I am not sure which tables that might be
but from looking at my error logs it would seem to be the following:

pot_documents_pkey
pot_hostnames_pkey
pot_operating_systems_pkey
pot_referers_pkey
pot_user_agents_pkey

Hopefully this function/trigger code can simply be added to the database
creation script that you already provide?

I am not very familiar with writing plpgsql functions/triggers but I
have written and tested small function that seems to work. I'll try and
see if I can make a faster version. Here is the code:

create or replace function pot_doc_dup_check() returns trigger as '
  DECLARE pkExists BOOLEAN := false;
  BEGIN
    SELECT INTO pkExists EXISTS (SELECT null FROM pot_documents where
data_id=NEW.data_id);
    IF pkExists = true THEN
      RETURN null;
    ELSE
      RETURN NEW;
    END IF;
  END;
' LANGUAGE 'plpgsql' with (iscachable);

create trigger pot_doc_dup_check BEFORE INSERT ON pot_documents
  for each ROW EXECUTE PROCEDURE pot_doc_dup_check();


Is this a better solution?

Jean-Christian Imbeault



-------------------------------------------------------
This SF.Net email sponsored by: Free pre-built ASP.NET sites including
Data Reports, E-commerce, Portals, and Forums are available now.
Download today and enter to win an XBOX or Visual Studio .NET.
http://aspnet.click-url.com/go/psa00100006ave/direct;at.asp_061203_01/01