How to check for successfull inserts

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/

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.