Re: Copy/delete issue

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(at)comcast(dot)net