Skip site navigation (1) Skip section navigation (2)

Re: Copy/delete issue

From: Adrian Klaver <aklaver(at)comcast(dot)net>
To: Herouth Maoz <herouth(at)unicell(dot)co(dot)il>
Cc: pgsql-general(at)postgresql(dot)org, brakhane(at)googlemail(dot)com
Subject: Re: Copy/delete issue
Date: 2008-12-23 16:26:58
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-general
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

In response to

pgsql-general by date

Next:From: Scott MarloweDate: 2008-12-23 17:12:59
Subject: Re: Question about pattern matching
Previous:From: Scott MarloweDate: 2008-12-23 16:15:47
Subject: Re: Donwload 8.4

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group