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

Re: Copy/delete issue

From: Herouth Maoz <herouth(at)unicell(dot)co(dot)il>
To: Adrian Klaver <aklaver(at)comcast(dot)net>
Cc: pgsql-general(at)postgresql(dot)org, brakhane(at)googlemail(dot)com
Subject: Re: Copy/delete issue
Date: 2008-12-23 14:43:56
Message-ID: 4950F92C.2090500@unicell.co.il (view raw or flat)
Thread:
Lists: pgsql-general
Adrian Klaver wrote:

> On Sunday 21 December 2008 1:49:18 am Herouth Maoz wrote:
>   
>> Adrian Klaver wrote:
>>     
>>> <Snip>
>>>
>>> Are you sure the problem is not in "$datefield" = "*" . That the script
>>> that formats the data file is not correctly adding "*" to the right file.
>>> Seems almost like sometimes the second CMD is being run against the table
>>> that the first CMD should be run on. In other words it is not doing a
>>> complete delete , but a date based one, and you then import duplicate
>>> records.
>>>       
>> Thanks for your reply. The file containing the tables list is static -
>> it doesn't change from one run to the next (unless I edit it personally).
>>
>> Herouth
>>     
>
> Well something is not static :) You mentioned this happens only with one 
> table. Have you tried running your procedure against that table only?
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.
>  Just 
> because a DELETE did not error does not mean it succeeded in the way you 
> wanted. You might want to throw a count() in the mix to see if you are really 
> clearing out the table the way you want to.
I wonder if there is a way to use the result of "count()" in \echo...
>  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.
>  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...

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...


Thanks,

Herouth

In response to

Responses

pgsql-general by date

Next:From: dimitris.sakellariosDate: 2008-12-23 16:05:08
Subject: Question about pattern matching
Previous:From: Shane WrightDate: 2008-12-23 14:28:58
Subject: Re: Using the wrong index (very suboptimal), why?

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