Обсуждение: Query cancelled errors

We have encountered numerous instances when we have encountered the following error message:
        ERROR:  Query was cancelled.
Specific instances include:

Attempting an export with pg_dump:
        % pg_dump -c -C -D -F t -f /data/export/daily/CT_ADMIN~EXPORT~20030916.sql.tar CT_ADMIN
        % pg_dump: query to obtain list of data types failed: ERROR:  Query was cancelled.

Doing a vacuum:
        % vacuumdb -a -f -z -v
        % INFO:  --Relation pg_catalog.pg_conversion--
        INFO:  Pages 2: Changed 0, reaped 0, Empty 0, New 0; Tup 114: Vac 0, Keep/VTL 0/0, UnUsed 0, MinLen 117, MaxLen 117; Re-using: Free/Avail. Space 2208/2096; EndEmpty/Avail. Pages 0/1.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
        INFO:  Index pg_conversion_default_index: Pages 2; Tuples 114.
        CPU 0.00s/0.01u sec elapsed 0.00 sec.
        INFO:  Index pg_conversion_name_nsp_index: Pages 4; Tuples 114.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
        INFO:  Index pg_conversion_oid_index: Pages 2; Tuples 114.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
        INFO:  Rel pg_conversion: Pages: 2 --> 2; Tuple(s) moved: 0.
        CPU 0.00s/0.00u sec elapsed 0.00 sec.
        INFO:  Analyzing pg_catalog.pg_conversion
        INFO:  --Relation pg_catalog.pg_depend--
        INFO:  Pages 21: Changed 0, reaped 1, Empty 0, New 0; Tup 2800: Vac 0, Keep/VTL 0/0, UnUsed 56, MinLen 49, MaxLen 49; Re-using: Free/Avail. Space 3388/3148; EndEmpty/Avail. Pages 0/1.
        CPU 0.01s/0.00u sec elapsed 0.01 sec.
        INFO:  Index pg_depend_depender_index: Pages 17; Tuples 2800: Deleted 0.
        CPU 0.01s/0.00u sec elapsed 0.08 sec.
        INFO:  Index pg_depend_reference_index: Pages 21; Tuples 2800: Deleted 0.
        CPU 0.01s/0.02u sec elapsed 0.06 sec.
        ERROR:  Query was cancelled.
        vacuumdb: vacuum  testdb failed

Getting table comments inside psql session:
        CT_ADMIN=# \dd pg_conversion
        ERROR:  Query was cancelled.

Any ideas?

--

David Rickard
Software Engineer
The GTS Companies
A TechBooks Company

----------------------------------------------------------------------------------
The GTS Companies:
GTS Publishing Services, GTS Graphics, GTS Innova:
Your Single-Source Solution!
Los Angeles CA  *  York, PA  *  Boston MA  *  New Delhi, India
----------------------------------------------------------------------------------


David.Rickard@GTSCompanies.com
Visit us on the World Wide Web
http://www.gtscompanies.com
5650 Jillson St., Los Angeles, CA 90040
(323) 888-8889 x331
(323) 888-1849 [fax]

David Rickard <David.Rickard@GTScompanies.com> writes:
> We have encountered numerous instances when we have encountered the
> following error message:
>          ERROR:  Query was cancelled.

Something is sending the backend a SIGINT.  What platform are you on?
(For that matter, what PG version are you using?)

You might look to see if your platform sends SIGINT when it thinks
a process has exhausted resource limits, or some such.  Most Unixen
I'm familiar with use different signals for that, but it's hard to
think of other obvious reasons for unsolicited signals.

Also, how did you start the postmaster?  If you launched it by hand
and weren't careful to dissociate it from the shell session, then
control-C typed at that shell could SIGINT the postmaster and backends.

            regards, tom lane

At 02:45 PM 9/16/2003 -0400, Tom Lane wrote:

David Rickard <David.Rickard@GTScompanies.com> writes:
> We have encountered numerous instances when we have encountered the
> following error message:
>          ERROR:  Query was cancelled.

Something is sending the backend a SIGINT.  What platform are you on?
(For that matter, what PG version are you using?)

System: SunOS rutabaga 5.9 Generic_112233-07 sun4u sparc SUNW,Ultra-250
PG:  7.3.4

You might look to see if your platform sends SIGINT when it thinks
a process has exhausted resource limits, or some such.  Most Unixen
I'm familiar with use different signals for that, but it's hard to
think of other obvious reasons for unsolicited signals.

Also, how did you start the postmaster?  If you launched it by hand
and weren't careful to dissociate it from the shell session, then
control-C typed at that shell could SIGINT the postmaster and backends.

postmaster is running in the background; started via:
pg_ctl start -D PG_DATA -s -l PG_LOG -o '-i'

                        regards, tom lane

--

David Rickard
Software Engineer
The GTS Companies
A TechBooks Company

----------------------------------------------------------------------------------
The GTS Companies:
GTS Publishing Services, GTS Graphics, GTS Innova:
Your Single-Source Solution!
Los Angeles CA  *  York, PA  *  Boston MA  *  New Delhi, India
----------------------------------------------------------------------------------


David.Rickard@GTSCompanies.com
Visit us on the World Wide Web
http://www.gtscompanies.com
5650 Jillson St., Los Angeles, CA 90040
(323) 888-8889 x331
(323) 888-1849 [fax]

David Rickard <David.Rickard@GTScompanies.com> writes:
>>> ERROR:  Query was cancelled.

> postmaster is running in the background; started via:
> pg_ctl start -D PG_DATA -s -l PG_LOG -o '-i'

Hm.  I notice that pg_ctl does not use "nohup" to start the postmaster,
which seems like a really bad idea now that I think about it.  Do things
get better if you make the relevant section of pg_ctl look like

    if [ -n "$logfile" ]; then
        nohup "$po_path" ${1+"$@"} </dev/null >>$logfile 2>&1 &
    else
        # when starting without log file, redirect stderr to stdout, so
        # pg_ctl can be invoked with >$logfile and still have pg_ctl's
        # stderr on the terminal.
        nohup "$po_path" ${1+"$@"} </dev/null 2>&1 &
    fi

?

            regards, tom lane

David Rickard <David.Rickard@GTScompanies.com> writes:
>>> ERROR:  Query was cancelled.
>>
>> Something is sending the backend a SIGINT.  What platform are you on?
>> (For that matter, what PG version are you using?)

> System: SunOS rutabaga 5.9 Generic_112233-07 sun4u sparc SUNW,Ultra-250
> PG:  7.3.4

A bit of googling turned up this:
http://dbforums.com/arch/128/2002/7/425070

which suggests that the solution is to not use /sbin/sh as your root
shell; or perhaps better, arrange to spawn Postgres from a startup
script rather than manually from a root shell.

Judging from that discussion, my idea of interposing nohup won't help.
I had thought that maybe nohup would dissociate Postgres from the
root shell's process group, but evidently it doesn't do that on Solaris.
(I can't find any evidence that it does so on Linux or HPUX, either,
so I'm not going to modify the standard version of pg_ctl, unless
someone comes up with evidence that it would help somewhere.)

            regards, tom lane

I don't no hup pg_ctl ever and I'm fine. (Though I am on Linux)

And you mentioned SIGINT. I'd have thought that disconnecting would only
send a SIGHUP?

I'm betting there is some external resource management screwing with it.
(processor/memory quota?)


> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Tuesday, September 16, 2003 3:09 PM
> To: David Rickard
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Query cancelled errors
>
>
> David Rickard <David.Rickard@GTScompanies.com> writes:
> >>> ERROR:  Query was cancelled.
>
> > postmaster is running in the background; started via:
> > pg_ctl start -D PG_DATA -s -l PG_LOG -o '-i'
>
> Hm.  I notice that pg_ctl does not use "nohup" to start the postmaster,
> which seems like a really bad idea now that I think about it.  Do things
> get better if you make the relevant section of pg_ctl look like
>
>     if [ -n "$logfile" ]; then
>         nohup "$po_path" ${1+"$@"} </dev/null >>$logfile 2>&1 &
>     else
>         # when starting without log file, redirect stderr to stdout, so
>         # pg_ctl can be invoked with >$logfile and still have pg_ctl's
>         # stderr on the terminal.
>         nohup "$po_path" ${1+"$@"} </dev/null 2>&1 &
>     fi
>
> ?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Could we try 'screen' maybe and see if it persists?

'screen' - detatch and re-attach to a terminal
http://www.math.fu-berlin.de/~guckes/screen/


> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Tuesday, September 16, 2003 3:26 PM
> To: David Rickard
> Cc: pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] Query cancelled errors
>
>
> David Rickard <David.Rickard@GTScompanies.com> writes:
> >>> ERROR:  Query was cancelled.
> >>
> >> Something is sending the backend a SIGINT.  What platform are you on?
> >> (For that matter, what PG version are you using?)
>
> > System: SunOS rutabaga 5.9 Generic_112233-07 sun4u sparc SUNW,Ultra-250
> > PG:  7.3.4
>
> A bit of googling turned up this:
> http://dbforums.com/arch/128/2002/7/425070
>
> which suggests that the solution is to not use /sbin/sh as your root
> shell; or perhaps better, arrange to spawn Postgres from a startup
> script rather than manually from a root shell.
>
> Judging from that discussion, my idea of interposing nohup won't help.
> I had thought that maybe nohup would dissociate Postgres from the
> root shell's process group, but evidently it doesn't do that on Solaris.
> (I can't find any evidence that it does so on Linux or HPUX, either,
> so I'm not going to modify the standard version of pg_ctl, unless
> someone comes up with evidence that it would help somewhere.)
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>