Re: optimization issue

[rob stone]

Hello Ken,

On Fri, 2021-07-09 at 13:27 -0500, Kenneth Marshall wrote:
> On Fri, Jul 09, 2021 at 01:16:16PM +1000, rob stone wrote:
> > Hello,
> > 
> > I am curious.
> > 
> > NVL, DECODE and SELECT FROM dual are Oracle methods and these
> > appear in
> > your code.
> > 
> > How did you make these work in Postgres? 
> > 
> > Cheers,
> > Rob
> 
> Hi Rob,
> 
> At a guess, they are using the Orafce PostgreSQL extension. It
> provides
> a lot of compatibility functions.
> 
> Regards,
> Ken


Thanks. Found it on github.

Regards,
Rob

Re: How to debug a connection that's "active" but hanging?

[Vijaykumar Jain]

On Sat, 10 Jul 2021 at 00:29, Jurrie Overgoor <
postgresql-mailingl...@jurr.org> wrote:

> Hi everyone,
>
> We are in the process of upgrading from PostgreSQL 9.6 to 13. When our
> database gets created in our regression tests, we run some unit tests
> first. We see one of those tests hang.
>
> It seems the client is waiting on more data to arrive from the
> PostgreSQL server. A thread dump shows it waiting on the socket.
>
> On the server, I see the connection from the client. Looking at
> pg_stat_activity, I see it is in state 'active'. I have seen query_start
> be as old as eight hours ago. The documentation states that 'active'
> means a query is being executed. The query in question is:
>
> Looking at pg_locks, I only see locks with granted = true for the PID.
>

Is this reproducible, I mean this happens multiple times?
can you please run the below query in a separate session and share the
result, feel free to anonymize sensitive stuff.

SELECT db.datname, locktype, relation::regclass, mode, transactionid
AS tid, virtualtransaction AS vtid, pid, granted
FROM pg_catalog.pg_locks l
LEFT JOIN pg_catalog.pg_database db ON db.oid = l.database
WHERE NOT pid = pg_backend_pid();



> There is nothing in the logs as far as I can see. Configuration
> variables log_min_messages and log_min_error_statement are on 'debug1'.
> This is a snippet of the logs:
>
> 2021-07-09 20:35:16.374 CEST [30399] STATEMENT:  START TRANSACTION
> 2021-07-09 20:35:18.703 CEST [30399] WARNING:  there is already a
> transaction in progress
>
Are you having multiple BEGIN tx not matching commit/rollbacks ? not sure
related, but fixing this will help reduce noise.


>
> I am a bit out of ideas - does anyone have any tips where I should look
> to see what is causing the query to hang?
>

Although I am not an expert at this, I am exploring myself.
Can you collect backtrace for this pid?

Getting a stack trace of a running PostgreSQL backend on Linux/BSD -
PostgreSQL wiki


You can try enabling core dumps, then run a core dump using `gcore` on the
hanging pid (if you do not want to attach to a live process)
and generate a backtrace on that pid using `gdb` and share the trace.
I think that might help.

-- 
Thanks,
Vijay
Mumbai, India

Re: Why can't I drop a tablespace?

[Ron]

On 7/10/21 10:43 AM, Phil Endecott wrote:

Phil Endecott wrote:
Yesterday I had a disk-nearly-full problem, and decided to try to resolve 
it by moving one large table to a spare disk in a new tablespace:


=# create tablespace tempspace location "/db_temp";
=# alter table requests set tablespace tempspace;

[snip]

- Is my idea that these files should have been deleted but weren't
during the disk-full panic plausible?

- If I do move them away, what will Postgres do if it tries to access
them and finds they are missing? Is it well-behaved in this case?

- If I move them away, I would try to exercise the database (e.g.
do variants of "select sum(column1) from table") in some way to make
sure that it is all functioning. Any suggestions about how best to do
this?


I'd shut down postgres, rename those files, and then analyzing the relevant 
tables after startup.  Probably also run amcheck against the relevant 
tables' indices.


https://access.crunchydata.com/documentation/amcheck-next/1.5/#interface

--
Angular momentum makes the world go 'round.

Re: Why can't I drop a tablespace?

[Phil Endecott]

Phil Endecott wrote:
Yesterday I had a disk-nearly-full problem, and decided to try to resolve 
it by moving one large table to a spare disk in a new tablespace:


=# create tablespace tempspace location "/db_temp";
=# alter table requests set tablespace tempspace;

That didn't work; I think disk space had actually reached zero:

PANIC:  could not write to file "pg_wal/xlogtemp.19369": No space left on device
STATEMENT:  alter table requests set tablespace tempspace;

So I shut down the database and resolved the problem in a more conventional 
way by resizing the filesystem. It is now (apparently) functioning normally.


BUT: I am unable to drop the tablespace that I created:

=# drop tablespace tempspace;
ERROR:  tablespace "tempspace" is not empty

On inspection /db_temp does contain a few GB of data that looks 
consistent with my table "requests" that I had tried to move.


postgres:/db_temp/PG_11_201809051$ ls -l 17829/
total 2894972
-rw--- 1 postgres postgres   32137216 Jul  8 18:35 486095
-rw--- 1 postgres postgres   37240832 Jul  8 18:57 494286
-rw--- 1 postgres postgres 1073741824 Jul  8 19:02 502478
-rw--- 1 postgres postgres 1073741824 Jul  8 19:03 502478.1
-rw--- 1 postgres postgres  747577344 Jul  8 19:03 502478.2

I can't find what is using it:

=# select * from pg_tables where tablespace = 'tempspace';
(0 rows)



I've now had a look at the source for "DROP TABLESPACE" and also for
the pg_tablespace_databases function, and it seems that they both
inspect the filesystem, rather than looking in any catalog tables,
to determine whether a database has any tables in the tablespace.

Since the tablespace doesn't seem to be mentioned in pg_tables or
pg_indexes, or anywhere else that I've looked, my guess is that these
files are orphans that shouldn't be there - perhaps they should have
been deleted when the "alter table set tablespace" or the "vacuum full"
failed, but weren't. So I am tempted to delete them (or at least move
them away, initially).

Questions:

- Any advice about identifying what the tables are? I note that the
first one, 486095, is the OID of the tablespace itself plus one; does
that give a clue?

- Is my idea that these files should have been deleted but weren't
during the disk-full panic plausible?

- If I do move them away, what will Postgres do if it tries to access
them and finds they are missing? Is it well-behaved in this case?

- If I move them away, I would try to exercise the database (e.g.
do variants of "select sum(column1) from table") in some way to make
sure that it is all functioning. Any suggestions about how best to do
this?


Regards, Phil.