Dear All,
Apologies the way i am asking question as i am more a SQL Server person and
a new postgre man..
I have used a query store in SQL server. it provides me option to load
statistics data to temp table and get below important information.
1. Last run duration
2. Average time for
Thanks Tom,David and Chris for detailed opinions
Regards,
Arun
On Sat, 23 Mar 2024 at 09:25, arun chirappurath
wrote:
> Hi All,
>
> I have a table named users with index on user name.
>
> CREATE TABLE users (
> user_id SERIAL PRIMARY KEY,
> username VARCHAR(50) NOT NULL,
> email
arun chirappurath writes:
> I have a table named users with index on user name.
> ...
> When I try to do below select query it's taking seq scan and query returns
> in 5ms.
5ms is an okay runtime, I would think. Is the table empty?
> I am trying to force query to use indexes using query
On Fri, Mar 22, 2024 at 8:55 PM arun chirappurath
wrote:
>
> I am trying to force query to use indexes using query hints.
>
> Set enable indexscan to ON,
> Same for bitmap and index only scan
>
Everything is on by default in the planner. You need to think in terms of
what you don't want to
> On Mar 22, 2024, at 20:55, arun chirappurath wrote:
> I am trying to force query to use indexes using query hints.
PostgreSQL does not have query hints. Enabling index scans using parameters
doesn't *disable* other types of query nodes.
You can disable sequential scans using:
Hi All,
I have a table named users with index on user name.
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
username VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL,
age INT
);
CREATE INDEX idx_username ON users (username);
When I try to do below select query it's
On Fri, Mar 22, 2024 at 6:58 AM ushi wrote:
> Hello List,
>
> i am playing with the idea to implement a job queuing system using
> PostgreSQL. To meet requirements the system needs to offer some advanced
> features compared to "classic" queuing systems:
>
> - users can create new queues at any
Generally, I'd suggest you think carefully about the nature of the jobs,
and draw up a list of must-have properties (performance of course, but also
things like whether jobs have to survive planned or unplanned outages, be
visible across a WAN, numbers of readers and writers, delivery guarantees,
On 3/22/24 12:41, Fred Habash wrote:
Lock tree: All PID's waiting on a lock held by/blocked by single blocker
PID. Similar to what you see in the output of this script:
https://github.com/dataegret/pg-utils/blob/master/sql/locktree.sql
Lock tree: All PID's waiting on a lock held by/blocked by single blocker
PID. Similar to what you see in the output of this script:
https://github.com/dataegret/pg-utils/blob/master/sql/locktree.sql . It
uses the dot connotation to draw a tree.
Waiters: The PID (first column) returned by this
On Fri, Mar 22, 2024 at 1:27 PM Tom Lane wrote:
> Matthias Apitz writes:
> > We have a PostgreSQL 15.1 server in production at a customer for some
> > weeks (migrated from an older version) on SuSE SLES 15.
>
> > The customer is facing machine locks and before the Linux server does
> > not
Matthias Apitz writes:
> We have a PostgreSQL 15.1 server in production at a customer for some
> weeks (migrated from an older version) on SuSE SLES 15.
> The customer is facing machine locks and before the Linux server does
> not respond any more (not even on SSH, only power-cycle reset helps
We have a PostgreSQL 15.1 server in production at a customer for some
weeks (migrated from an older version) on SuSE SLES 15.
The customer is facing machine locks and before the Linux server does
not respond any more (not even on SSH, only power-cycle reset helps to
get it back), short before
> On Mar 22, 2024, at 09:25, Fred Habash wrote:
>
> Facing an issue where sometimes humans login to a database and run DDL
> statements causing a long locking tree of over 1000 waiters. As a workaround,
> we asked developers to always start their DDL sessions with 'SET lock_timeout
> =
On 3/22/24 09:25, Fred Habash wrote:
Facing an issue where sometimes humans login to a database and run DDL
statements causing a long locking tree of over 1000 waiters. As a
The above needs more explanation:
1) Define locking tree.
2) Define waiters.
3) Provide examples of the DDL.
Facing an issue where sometimes humans login to a database and run DDL
statements causing a long locking tree of over 1000 waiters. As a
workaround, we asked developers to always start their DDL sessions
with 'SET lock_timeout = 'Xs'.
I reviewed the native lock timeout parameter in Postgres and
Am 22.03.2024 um 14:15 schrieb Fred Habash:
We developed a home-grown queue system using Postgres, but its
performance was largely hindered by que tables bloating and the need to
continuously vacuum them. It did not scale whatsoever. With some
workarounds, we ended up designing three sets
On Fri, 22 Mar 2024 at 15:01, Nick Renders wrote:
>
> We now have a second machine with this issue: it is an Intel Mac mini
> running macOS Sonoma (14.4) and PostgreSQL 16.2.
> This one only has a single Data directory, so there are no multiple
> instances running.
>
I don't think that having a
On 3/22/24 07:01, Nick Renders wrote:
On 13 Mar 2024, at 12:35, Stephen Frost wrote:
We now have a second machine with this issue: it is an Intel Mac mini running
macOS Sonoma (14.4) and PostgreSQL 16.2.
This one only has a single Data directory, so there are no multiple instances
running.
arun chirappurath writes:
> I am running below query on a database. why is it creating a exclusive lock
> on a virtualxid? I am running some SELECT queries and its creating an
> ExclusiveLock in virtualxid? is this normal?
Yes. That lock has nothing to do with any table, only with the
On 13 Mar 2024, at 12:35, Stephen Frost wrote:
> Greetings,
>
> * Nick Renders (postg...@arcict.com) wrote:
>>> ...run them under different users on the system.
>>
>> Are you referring to the "postgres" user / role? Does that also mean setting
>> up 2 postgres installation directories?
>
> Yes,
We developed a home-grown queue system using Postgres, but its performance
was largely hindered by que tables bloating and the need to
continuously vacuum them. It did not scale whatsoever. With some
workarounds, we ended up designing three sets of queue tables, switching
between them based on
On 2024-03-22 12:43:40 +0100, ushi wrote:
> i am playing with the idea to implement a job queuing system using
> PostgreSQL. To meet requirements the system needs to offer some advanced
> features compared to "classic" queuing systems:
>
> - users can create new queues at any time
> - queues have
On Fri, 2024-03-22 at 16:07 +0530, Vijaykumar Jain wrote:
> On Fri, 22 Mar 2024 at 15:39, Laurenz Albe wrote:
> > On Fri, 2024-03-22 at 13:41 +0530, Daulat wrote:
> > > We are unable to take the backup of our database. While taking backup we
> > > are getting the same error.
> > >
> > > psql:
On Fri, Mar 22, 2024 at 8:05 AM Dominique Devienne wrote:
>
> On Fri, Mar 22, 2024 at 12:58 PM ushi wrote:
>>
>> i am playing with the idea to implement a job queuing system using
>> PostgreSQL.
>
>
> FYI, two bookmarks I have on this subject, that I plan to revisit eventually:
> *
On Fri, Mar 22, 2024 at 12:58 PM ushi wrote:
> i am playing with the idea to implement a job queuing system using
> PostgreSQL.
FYI, two bookmarks I have on this subject, that I plan to revisit
eventually:
* https://news.ycombinator.com/item?id=20020501
*
Hello List,
i am playing with the idea to implement a job queuing system using PostgreSQL. To meet requirements the system needs to offer some advanced
features compared to "classic" queuing systems:
- users can create new queues at any time
- queues have priorities
- priorities of queues can
On Fri, 22 Mar 2024 at 15:39, Laurenz Albe wrote:
> On Fri, 2024-03-22 at 13:41 +0530, Daulat wrote:
> > We are unable to take the backup of our database. While taking backup we
> are getting the same error.
> >
> > psql: error: connection to server at "localhost" (::1), port 5014
> failed:
On Fri, 2024-03-22 at 12:43 +0530, arun chirappurath wrote:
> I am running below query on a database. why is it creating a exclusive lock
> on a virtualxid?
> I am running some SELECT queries and its creating an ExclusiveLock in
> virtualxid? is this normal?
>
> SELECT datname, pid, state,
On Fri, 2024-03-22 at 13:41 +0530, Daulat wrote:
> We are unable to take the backup of our database. While taking backup we are
> getting the same error.
>
> psql: error: connection to server at "localhost" (::1), port 5014 failed:
> FATAL: pg_attribute catalog is missing 1 attribute(s) for
We are unable to take the backup of our database. While taking backup we
are getting the same error.
psql: error: connection to server at "localhost" (::1), port 5014 failed:
FATAL: pg_attribute catalog is missing 1 attribute(s) for relation OID 2662
Thanks.
On Fri, Mar 22, 2024 at 12:35 PM
Dear all,
I am running below query on a database. why is it creating a exclusive lock
on a virtualxid? I am running some SELECT queries and its creating an
ExclusiveLock in virtualxid? is this normal?
SELECT datname, pid, state, query, age(clock_timestamp(), query_start) AS
age
FROM
On Fri, 2024-03-22 at 10:56 +0530, Daulat wrote:
> We recently started seeing an error “ERROR: uncommitted xmin 3100586
> from before xid cutoff 10339367 needs to be frozen” on our user tables.
> I’m unable to do ‘vacuum’, ‘vacuum freeze’ or ‘vacuum full’ on Postgres 14.4
> running on a windows
33 matches
Mail list logo
