Re: [GENERAL] EXPLAIN command just hangs...

[Tom Lane]

Rhhh Lin  writes:
> So I decided to intervene and terminate some active sessions (Which were 
> active and not waiting) which were all querying this table with a similar 
> query, by using pg_backend_terminate(pid). Some of these sessions were in an 
> active state for a few days executing this query. However, the termination of 
> the PIDs [The command succeeded and returned 't']has not closed down the 
> sessions. They are still present, and still active and still executing. Those 
> current sessions are for whatever reason completely "hung", locking out 
> exclusive access to the table (from vacuum) and cannot be killed. I have not 
> yet resorted to trying to kill the process PID's from the OS perspective. 
> Instead, I will try to schedule an outage over the next few days, restart the 
> server, implement a vacuum execution and see where I am at then.

Well, that's pretty interesting in itself.  Any chance of attaching to one
of those unkillable backends with gdb and getting a stack trace?

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] pg_logical/snapshots directory

[Mark Fletcher]

Hi All,

While trying to track down my logical decoding problem, I noticed that
my pg_logical/snapshots directory has ~5000 .snap files and is growing at a
rate of about 4 files a minute. The earliest file is from yesterday
afternoon, dating to the time I took our site down and added a column to a
large table.

I have not been able to find any docs on this directory. Can someone point
me to an explanation of this directory and how to manage the number of
.snap files in it (and/or whether I should be worried about the number of
files in it)?

Thanks,
Mark

[GENERAL] ERROR: invalid input syntax for integer: "INSERT"

[Robert Lakes]

Guys,
New to Postgres - here's my code inside an event trigger:
ELSIF (TG_OP = 'INSERT') THEN
EXECUTE format('INSERT INTO %I SELECT statement_timestamp(),
''INSERT'', $1.*', TG_TABLE_NAME || '_cdc')
USING NEW;
RETURN NEW;


Here's the error I am receiving - when I am attempting to insert a record
into a table:

ERROR:  invalid input syntax for integer: "INSERT"
LINE 1: ...T INTO listings_cdc SELECT statement_timestamp(), 'INSERT', ...
 ^
QUERY:  INSERT INTO listings_cdc SELECT statement_timestamp(), 'INSERT',
$1.*
CONTEXT:  PL/pgSQL function audit_func() line 28 at EXECUTE

Re: [GENERAL] ERROR: invalid input syntax for integer: "INSERT"

[David G. Johnston]

On Saturday, November 4, 2017, Robert Lakes  wrote:
>
> Here's the error I am receiving - when I am attempting to insert a record
> into a table:
>
> ERROR:  invalid input syntax for integer: "INSERT"
> LINE 1: ...T INTO listings_cdc SELECT statement_timestamp(), 'INSERT', ...
>
>

The second column of listings_cdc has a type of integer.  The word INSERT
is not a valid value to store there.

David J.