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
> > 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.
In response to
pgsql-general by date
|Next:||From: Scott Marlowe||Date: 2008-12-23 17:12:59|
|Subject: Re: Question about pattern matching|
|Previous:||From: Scott Marlowe||Date: 2008-12-23 16:15:47|
|Subject: Re: Donwload 8.4|