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@comcast.net