Re: Problem with COPY on RedHat 8 with PostgreSQL 7.3.2

I am facing an error I haven't seen in my entire life.
I am running a simple set of COPY statements:

BEGIN TRANSACTION;
DELETE FROM t_artikel_attribut WHERE lief_nr = '9060';
COPY t_artikel_attribut (art_attr_id, a_code_lief, art_attr,
bearb_dat, bearb_usr, lief_nr) FROM stdin DELIMITER ';'
NULL AS 'NULL';
\.
DELETE FROM t_artikel WHERE lief_nr = '9060';
COPY t_artikel (art_nr_lief, kurz_text, lang_text, vp_einh, vp_menge,
vp_druck, bearb_dat, bearb_usr,
index_num, index_txt, info_txt,
lief_nr, prodcode)
FROM stdin DELIMITER ';' NULL AS 'NULL';
703462;HEIßKLEBEPISTOLE PKP 30LE;Art.Nr.: 703462 HEIßKLEBEPISTOLE PKP
30LE;Stk;1;Stk;2003-04-27
12:05:25.200928+02;jp;NULL;NULL;NULL;9060;9060703462
703441;SCHLAGBOHRMASCHINE PSB 1200-2R;Art.Nr.: 703441 SCHLAGBOHRMASCHINE
PSB 1200-2R;Stk;1;Stk;2003-04-27
12:05:25.613064+02;jp;NULL;NULL;NULL;9060;9060703441
703471;HEISSLUFTGEBLÄSE PHG 530-2;Art.Nr.: 703471 HEISSLUFTGEBLÄSE PHG
530-2;Stk;1;Stk;2003-04-27
12:05:25.637054+02;jp;NULL;NULL;NULL;9060;9060703471
.
.
.

What I am doing is DELETE -> COPY into serveral tables. The problem is:
It seemed to work pretty fine up to now but now PostgreSQL stops in the
middle of the COPY command. The CPU load goes down to zero but the
process stays alive. When I press CTRL + C the command line isn't
blocked anymore but the COPY command starts to work as a background
process again (100% CPU load).

hs(at)shoppingnet tmp]$ psql sn1 < K9060.sql

How can a process I have killed with CTRL + C START working? In my
opinion it should die :(.
Did anybody encounter similar problems on RedHat 8? It looks like some
hardware failure to me.

Also, I had some

NOTICE: Rel pg_artikel: Uninitialized page 74 - fixing
NOTICE: Rel pg_artikel: Uninitialized page 75 - fixing

before ...

Is it a bug or a hardware problem?

Hans

Hans-Juergen Schoenig <postgres(at)cybertec(dot)at> writes:
> What I am doing is DELETE -> COPY into serveral tables. The problem is:
> It seemed to work pretty fine up to now but now PostgreSQL stops in the
> middle of the COPY command. The CPU load goes down to zero but the
> process stays alive.

Bizarre.

> When I press CTRL + C the command line isn't
> blocked anymore but the COPY command starts to work as a background
> process again (100% CPU load).

Perhaps the backend's gotten into some kind of loop, and isn't reaching
the point at which it would realize that the client connection is gone
and then exit. Can you attach to the looping backend with a debugger
and get a stack trace for us?

> Also, I had some
> NOTICE: Rel pg_artikel: Uninitialized page 74 - fixing
> NOTICE: Rel pg_artikel: Uninitialized page 75 - fixing
> before ...

These are suspicious, but not hard evidence of a problem. IIRC we've
determined that there are legitimate cases where zero pages could get
inserted into a table. See past threads about "Uninitialized page"
messages.

regards, tom lane

Okay, I think I see the problem: the backend is hanging up here:

#0 0x420dac82 in send () from /lib/i686/libc.so.6
#1 0x080dbe69 in secure_write ()
#2 0x080defd6 in pq_flush ()
#3 0x081618fe in send_message_to_frontend ()
#4 0x081610ac in elog ()
#5 0x4038a6be in ?? () -- plpgsql, didn't have the symbols loaded
#6 0x403898b9 in ?? ()
#7 0x40389791 in ?? ()
#8 0x40389b04 in ?? ()
#9 0x40389832 in ?? ()
#10 0x40389791 in ?? ()
#11 0x40389f23 in ?? ()
#12 0x4038986a in ?? ()
#13 0x40389791 in ?? ()
#14 0x4038962f in ?? ()
#15 0x403891a0 in ?? ()
#16 0x40386751 in ?? ()
#17 0x080c280f in ExecCallTriggerFunc ()
#18 0x080c2909 in ExecBRInsertTriggers ()
#19 0x080b34da in CopyFrom ()
#20 0x080b2563 in DoCopy ()
#21 0x081198fb in pg_exec_query_string ()
#22 0x0811a685 in PostgresMain ()

Looking at the log, it's apparent that you've got a trigger that is
emitting lots of NOTICEs. What I see happening is that psql is busy
doing its side of the COPY and is not swallowing the NOTICE traffic.
It would eat it at the end of the COPY --- but the kernel will only
buffer a limited amount of stuff in a pipe, and so after awhile it
blocks the backend.

When you kill the psql process, the kernel discards the output pipe data
and lets the backend run again. Now it starts chewing on whatever
the psql side had managed to push into the input pipe before blocking.
Eventually it will run out of that data, detect EOF, and exit. The
trigger seems to be slow enough that that'll take awhile, though.

If there's any bug here, it's that the libpq COPY functions are too
singleminded to check for NOTICE output while they are pushing input
data to the backend. Not sure if it's worth making them do so or not.
My recommendation would be to get rid of the NOTICEs in your trigger
in any case...

regards, tom lane