On Tuesday 23 December 2008 6:43:56 am Herouth Maoz wrote: > > Well, every time this happens, I re-run the procedure, with all the > lines in the data files up to the given table deleted. And it works. > Then I restore the original data file. And the next day it works. It > only happens once in a while. See next comment. > > > Also is the actual data file static from one run to the next? > > If you mean the data file that contains the list of tables, then yes. If > you mean the data in the table itself, then no, the data changes - new > records are added and old ones are updated. I should have been more specific. You mentioned you repeat the procedure 5 minutes or so after a failure. Is there a change in the actual data between those runs? > > > Would also help to see the schema for the > > table involved and maybe a sample of the data, if that is possible. > > A sample of the data would be a bit tricky, as this is customers' > private information. But the table schema is: > > CREATE TABLE web1010.users > ( > user_id CHAR(32) PRIMARY KEY NOT NULL, > whitelabel_id NUMERIC(21) NOT NULL, > username VARCHAR(30) NOT NULL, > password CHAR(32) NOT NULL, > perms VARCHAR(255) NOT NULL, > first_name VARCHAR(40) NULL, > last_name VARCHAR(40) NULL, > total_points INTEGER DEFAULT 0 NOT NULL, > date_created TIMESTAMP NOT NULL, > date_birth TIMESTAMP NULL, > gender INTEGER NULL, > city_id NUMERIC(21) NULL, > is_active SMALLINT NOT NULL, > email VARCHAR(255) NULL, > subscriptin_id NUMERIC(21) NULL, > subscriptin_num_of_msg INTEGER NULL, > subscriptin_date_start TIMESTAMP NULL, > subscriptin_sent_datetime TIMESTAMP NULL, > subscriptin_credit_left INTEGER NULL, > subscriptin_status INTEGER NULL, > subscriptin_sent_reference NUMERIC(21) NULL, > first_time_subscribed VARCHAR(10) NULL, > sms_credit INTEGER NULL, > reg_pid NUMERIC(21) NULL, > spam_fl SMALLINT NULL, > constraint PK_USERS unique (whitelabel_id,username) > ) > ; > > I suppose this doesn't happen with other tables in the process, because > most other tables don't have two unique constraints in them - most only > have the primary key. But still, if everything is deleted from the > table, this should not be an issue... In the original post you said the constraint violation was on the PK. Is that the case or is it on PK_USERS? > > I might take Dennis Brakhane's advice and replace the DELETE command > with TRUNCATE, as I see no harm in doing so. Nevertheless, DELETE should > either work or fail saying "could not delete because...". Otherwise > PostgreSQL is not a very reliable... Worth trying. However it does not answer the question of what is going on. While it is possible that there is a DELETE bug, I still believe it is a case of DELETE working in way you are not expecting. > > > Thanks, > > Herouth -- Adrian Klaver aklaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
