Canceling statement due to user request

[kustodian]

Today on one of our Postgres 9.1 database pgcompact (1.0.2) exited with this error:

Thu Mar 12 00:24:02 2015 db1, public.message ERROR A database error occurred, exiting:
DatabaseError Can not executie command: 
 DROP INDEX public.message_is_ready_idx;
 ERROR:  canceling statement due to user request

Here is what was logged in the postgres log:

2015-03-12 00:23:59 CET [22772]: [202-1] LOG:  duration: 3405.028 ms  statement: CREATE INDEX CONCURRENTLY pgcompact_index_22738 ON message USING btree (is_ready) TABLESPACE index WHERE (is_ready = false);
2015-03-12 00:24:00 CET [22772]: [203-1] ERROR:  canceling statement due to statement timeout
2015-03-12 00:24:00 CET [22772]: [204-1] STATEMENT:  DROP INDEX public.message_is_ready_idx;
2015-03-12 00:24:01 CET [22772]: [205-1] ERROR:  canceling statement due to statement timeout
2015-03-12 00:24:01 CET [22772]: [206-1] STATEMENT:  DROP INDEX public.message_is_ready_idx;
2015-03-12 00:24:02 CET [22772]: [207-1] LOG:  duration: 1074.371 ms  statement: DROP INDEX public.message_is_ready_idx;
2015-03-12 00:24:02 CET [22772]: [208-1] ERROR:  canceling statement due to user request
2015-03-12 00:24:02 CET [22772]: [209-1] STATEMENT:  DROP INDEX public.message_is_ready_idx;

As you can see a temp index was created, but pgcompact due to a time out couldn't drop the old index (and after the second retry it cancelled the DROP query). The problem is that pgcompact exited after this and it didn't clean up the temp index.

Is this normal? Shouldn't pgcompact continue working even if one index fails? I guess pgcompact should at least drop the temp index?

[grayhemp]

For versions <9.2, that do not support DROP INDEX CONCURRENTLY, to soften locks impact, pgcompact sets statement timeout locally for its transaction to 500ms and performs several retries. The first two errors show that.

However, the third error tells that pgcompact was cancelled by the user on the client side. Probably by Ctrl+C.

2015-03-12 00:24:02 CET [22772]: [208-1] ERROR:  canceling statement
due to user request

[kustodian]

The thing is pgcompact is run via cron and no one was logged in at that time on the server to send a kill signal.

On Mon, Mar 30, 2015, 23:25 Sergey Konoplev notifications@github.com wrote:

Closed #6 https://github.com/grayhemp/pgtoolkit/issues/6.

— Reply to this email directly or view it on GitHub https://github.com/grayhemp/pgtoolkit/issues/6#event-269114460.

[grayhemp]

I might be missing something, but that is what I see is happening.

On Tue, Mar 31, 2015 at 4:33 AM, Strahinja Kustudic < notifications@github.com> wrote:

The thing is pgcompact is run via cron and no one was logged in at that time on the server to send a kill signal.

On Mon, Mar 30, 2015, 23:25 Sergey Konoplev notifications@github.com wrote:

Closed #6 https://github.com/grayhemp/pgtoolkit/issues/6.

— Reply to this email directly or view it on GitHub https://github.com/grayhemp/pgtoolkit/issues/6#event-269114460.

— Reply to this email directly or view it on GitHub https://github.com/grayhemp/pgtoolkit/issues/6#issuecomment-88054094.

Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray.ru@gmail.com

[kustodian]

When this had happened I analyzed everything and couldn't find anything which would send SIGINT to pgcompact, but I might have missed something. Leave it closed, if it happens again I'll check once again, and report back if it's pgcompact related.

On Tue, Mar 31, 2015, 11:54 Sergey Konoplev notifications@github.com wrote:

I might be missing something, but that is what I see is happening.

On Tue, Mar 31, 2015 at 4:33 AM, Strahinja Kustudic < notifications@github.com> wrote:

The thing is pgcompact is run via cron and no one was logged in at that time on the server to send a kill signal.

On Mon, Mar 30, 2015, 23:25 Sergey Konoplev notifications@github.com wrote:

Closed #6 https://github.com/grayhemp/pgtoolkit/issues/6.

— Reply to this email directly or view it on GitHub https://github.com/grayhemp/pgtoolkit/issues/6#event-269114460.

— Reply to this email directly or view it on GitHub https://github.com/grayhemp/pgtoolkit/issues/6#issuecomment-88054094.

Kind regards, Sergey Konoplev PostgreSQL Consultant and DBA

http://www.linkedin.com/in/grayhemp +1 (415) 867-9984, +7 (499) 346-7196, +7 (988) 888-1979 gray.ru@gmail.com

—

Reply to this email directly or view it on GitHub https://github.com/grayhemp/pgtoolkit/issues/6#issuecomment-88118895.