Re: Copy/delete issue

On Wednesday 17 December 2008 12:38:40 am Herouth Maoz wrote:
> I have a strange situation that occurs every now and again.
>
> We have a reports system that gathers all the data from our various
> production systems during the night, where we can run heavy reports on
> it without loading the production databases.
>
> I have two shell scripts that do this nightly transfer of data. The
> production database is Sybase. So I have a shell script that scans a
> list of tables and databases and dumps them into a format suitable for
> postgres COPY. After it dumps everything, another shell script scans the
> same list, and loads each dump file into the proper table.
>
> The shell script first runs psql with a DELETE command. For transaction
> tables (ones where data accumulates by date) the records for two days
> are deleted, and for non-transaction tables (ones that have records that
> might change but don't accumulate based on time) it's DELETE without WHERE.
>
> I run psql with ON_ERROR_STOP and check the exit status. If the DELETE
> failed, I should get an error status, so I do not proceed to the copy.
>
> Then I run psql again, with ON_ERROR_STOP, and run a \copy command that
> loads the data to the same table.
>
> For some reason, once in a while, that fails. Always on the same table -
> violating the unique constraint of the primary key. Now, this is
> impossible because there was a successful delete beforehand, as I said,
> and the data comes from a database where that same primary key is
> enforced. Moreover, when I re-run the script, everything runs fine.
>
> This happens at least once a week - always with the same table.
>
> Can anybody think of a reason why psql will not report an error on
> deletion? Or why it would tell me that a constraint has been violated
> when loading the same data 5 minutes later works fine?
>
> Thanks,
> Herouth
>
>
>
> Here is the relevant shell code (the relevant table has "*' in the file
> for datefield):
>

<Snip>

> deletion.
>
> if [ "$datefield" = "*" ]
> then
> CMD="DELETE FROM $local_table"
> else
> CMD="DELETE FROM $local_table WHERE $datefield >= current_date - 2"
> fi
>
> # Run the deletion command
>
> echo -e "\\\\set ON_ERROR_STOP\\n$CMD;" | $PSQLCMD -q -f - >
> $TMPFILE 2>&1
>

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

--
Adrian Klaver
aklaver(at)comcast(dot)net