PostgreSQL – How to Get pg_locks for Specific Database

lockingpostgresql

This question is related to my previous one here

In SQL Server, I could get the lock type from a specific database using the code below:

SELECT request_mode FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars');

In Postgres 11, how to filter the pg_locks result to a specific database like how SQL Server's resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars') works?

Thanks

Best Answer

As documented in the manual pg_locks contains a column database which you can join to the pg_database view:

select l.*
from pg_locks l
  join pg_database d on d.oid = l.database
where d.datname = current_database();

Related Solutions

PostgreSQL – How to Detach All Other Users from a Database

You could try connecting to the database as the postgres user and running:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )    -- 1. don't terminate your own session
    AND datname =                     -- 2. don't terminate connections to 
    (SELECT datname                   --    other databases in the cluster
       FROM pg_stat_activity
      WHERE procpid = pg_backend_pid( )
    );

update An even better query gets rid of the subselect:

SELECT pg_terminate_backend( procpid )
FROM pg_stat_activity
WHERE procpid <> pg_backend_pid( )
    AND datname = current_database( );

PostGIS – Efficiently Get Points Within X Distance in PostgreSQL

I only need the distance to be accurate on a local scale and so curvature of the earth shouldn't be an issue for my application, but is there a performance benefit of either POINT or GIS in this context?

The native point is for geometry. If you want distance on Earth then those "points" are being projected onto a representation of Earth (sphereoid) and you need GIS. If you're using PostgreSQL that means you need PostGIS. For a PostGIS query that returns all points in a table within a 1 km (1000 m) distance of a specific point, you can use ST_DWithin

SELECT *
FROM tbl
WHERE ST_DWithin( tbl.geog, ST_MakePoint(x,y)::geography, 1000 );

That will also use a GIST index (if any) on tbl.geog.

Best Answer

Related Solutions

PostgreSQL – How to Detach All Other Users from a Database

PostGIS – Efficiently Get Points Within X Distance in PostgreSQL

Related Question