Обсуждение: How to check for successfull inserts

I have a script of the form
begin work
delete
copy from ...
commit work

Basically in a couple of ocassions the file been read from the copy
command was empty. How could I check for this from a pgsql script?

I tried look at PL-SQL manuals, but didn't find anything which would seem
to help in solving this problem.

On Fri, Mar 08, 2002 at 05:33:25PM -0500, Francisco Reyes wrote:
> I have a script of the form
> begin work
> delete
> copy from ...
> commit work
>
> Basically in a couple of ocassions the file been read from the copy
> command was empty. How could I check for this from a pgsql script?
>
> I tried look at PL-SQL manuals, but didn't find anything which would seem
> to help in solving this problem.

Note, this is basically completely whacky, but it does work. Hopefully
someone has a better idea!

select case when (select count(*) from table) = 0
            then date_part('day',now())/0
            else 1 end;

Add that in, then when the table has no rows, the transaction will die with
a divide by zero and everything will be rolled back.

There probably is a function somewhere that you can call like
abort_transaction("error message") but I don't know what it is.

HTH,
--
Martijn van Oosterhout <kleptog@svana.org>
http://svana.org/kleptog/
> If the company that invents a cure for AIDS is expected to make their
> money back in 17 years, why can't we ask the same of the company that
> markets big-titted lip-syncing chicks and goddamn cartoon mice?

Francisco Reyes writes:
>
> I have a script of the form
> begin work
> delete
> copy from ...
> commit work
>
> Basically in a couple of ocassions the file been read from the copy
> command was empty. How could I check for this from a pgsql script?
>
> I tried look at PL-SQL manuals, but didn't find anything which would seem
> to help in solving this problem.
>

Normally I'd point you to this,

http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-statemen
ts.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

but I just checked and COPY FROM doesn't return that count (unlike INSERT).
It would be nice if it did but that doesn't help you much.

The only other thing I can think of off the top of my head is to count(*)
the table in question before and after and return the difference. As you're
within a transaction it will be accurate, if rather unwieldy.

Arguile mistakenly wrote:
>
> The only other thing I can think of off the top of my head is to
> count(*) the table in question before and after and return the
> difference. As you're within a transaction it will be accurate,
> if rather unwieldy.

*Danger! Danger Will Robinson!*

I wasn't thinking properly when I typed this. For the count(*) to be
guaranteed accurate you would have to lock the table in at least a SHARE
MODE level lock. Probably not what you want.

"Arguile" <arguile@lucentstudios.com> writes:
>> The only other thing I can think of off the top of my head is to
>> count(*) the table in question before and after and return the
>> difference. As you're within a transaction it will be accurate,
>> if rather unwieldy.

> *Danger! Danger Will Robinson!*

> I wasn't thinking properly when I typed this.

Yes you were.  Set the transaction mode to SERIALIZABLE and it'll
work just fine ...

            regards, tom lane

On Sat, 9 Mar 2002, Arguile wrote:

> Francisco Reyes writes:
> http://www.ca.postgresql.org/users-lounge/docs/7.2/postgres/plpgsql-statemen
> ts.html#PLPGSQL-STATEMENTS-DIAGNOSTICS

Thanks for the link. Will take a look.

> but I just checked and COPY FROM doesn't return that count (unlike INSERT).
> It would be nice if it did but that doesn't help you much.

Inserts would be too slow.

> The only other thing I can think of off the top of my head is to count(*)
> the table in question before and after and return the difference. As you're
> within a transaction it will be accurate, if rather unwieldy.

count(*) would probably be too slow. I am doing millions of records, plus
there are also million of deletions, so it would become progresivelly
worse (until the weekend when I do a vacuum full).

I am leaning towards an "External" solution, possibly with PHP.
Check if the file is 0 bytes. If it is then do nothing, otherwise process
the load.

This way I would be able to do what I need without having to Learn PL/SQL
which I probably will find time for it in a month or two.

Can PL/SQL even check the size of a file?
I haven't seen on the docs a list of functions available on PL/SQL.