Partitioning constraints vs before-trigger
Hi all, Could anybody explain, what happens first: constraint check or before-trigger execution? I have a table, partitioned by date: CREATE TABLE foo ( unid text NOT NULL, logtime timestamp with time zone NOT NULL, size integer, CONSTRAINT foo_pkey PRIMARY KEY (unid) ); -- There is an before-insert trigger which works perfectly, creates a new monthly partition if neccessary and inserts new record into the partition. -- Here is how partitions look like: CREATE TABLE foo_2018_01 ( CONSTRAINT foo_2018_01_pkey PRIMARY KEY (unid), CONSTRAINT foo_2018_01_logtime_check CHECK (logtime >= '2018-01-01 00:00:00+00'::timestamp with time zone AND logtime < '2018-02-01 00:00:00+00'::timestamp with time zone) ) INHERITS (foo); I cannot change anything in the application, as it's proprietary. So I had to do partitioning myself with a trigger. Now there's a new problem. It looks like the application sometimes do UPDATEs to the "logtime" column, which I use for partitioning. So the application can do something like UPDATE foo SET logtime='2017-12-01 00:00:00+00', size=5 WHERE unid='blahblablah', althrough this record had logtime='2018-01-18 00:00:00+00' and was in different partition. In such case, I can see the error (and transaction aborts): ERROR: new row for relation "foo_2018_01" violates check constraint "foo_2018_01_logtime_check" For business logic, it wouldn't be critical if I forbid/undo/replace modification of logtime column. But other columns must be updated by the application when neccessary. Now I need to ignore new value for "logtime" column for every UPDATE to table "foo". Here is my idea: CREATE OR REPLACE FUNCTION logtime_update_trigger() RETURNS trigger AS $BODY$ BEGIN IF (NEW.logtime != OLD.logtime) THEN NEW.logtime := OLD.logtime; END IF; RETURN NEW; END; $BODY$ LANGUAGE plpgsql VOLATILE; CREATE TRIGGER trg_foo_update BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE logtime_update_trigger(); Unfortunately, it seems like this trigger is not even being executed and I still get the same error: ERROR: new row for relation "foo_2018_01" violates check constraint "foo_2018_01_logtime_check" I suppose that's because contraint check is performed before the trigger is fired? Is there any workarounds here? I also tried to create a rule: CREATE OR REPLACE RULE test_rule AS ON UPDATE TO foo WHERE new.logtime <> old.logtime DO INSTEAD UPDATE foo SET size = new.size WHERE foo.unid = old.unid AND foo.logtime = old.logtime; But then I get recursion error: ERROR: infinite recursion detected in rules for relation "foo" Possibly because the recursion analysis doesn't take WHERE condition into account. Any help would be greatly appreciated. PostgreSQL version: 9.0.1 on CentOS 5 i686. Best regards, Nikolay Karikh.
Re: Reindex doesn't eliminate bloat
On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson wrote: > v8.4.12 > This is *very* old version, not supported by the community for many years. Check https://www.postgresql.org/ to see currently supported versions. You need to upgrade it.
Re: Reindex doesn't eliminate bloat
On Tue, Mar 13, 2018 at 1:28 AM, Ron Johnson wrote: > On 03/12/2018 05:20 PM, Nikolay Samokhvalov wrote: > > On Tue, Mar 13, 2018 at 1:05 AM, Ron Johnson > wrote: > >> v8.4.12 >> > > This is *very* old version, not supported by the community for many years. > Check https://www.postgresql.org/ to see currently supported versions. > You need to upgrade it. > > > Don't even think I'm in control of when -- or even if -- the customer > decides to upgrade. > > That being the case, do you have an answer to the question? > Those queries from wiki for table and index bloat estimation are for estimation only. In many cases they show very wrong results. Better (yet not ideal) approach is using pgstattuple extension (though I'm not sure it existed back in 2009). Can you provide table and index definition and, if you can, some sample data?
Re: Rapid disk usage spikes when updating large tables with GIN indexes
Why not set up a spot EC2 instance with Postgres 10.1, load database from a dump (yes you’ll need to create one from RDS because they don’t provide direct access to dumps/backups; probably you need to get only specific tables) and repeat your actions, closely looking at filesystem. ср, 16 мая 2018 г. в 13:10, Jonathan Marks : > Hi Tom — > > We turned on log_temp_files and since the last stats reset (about a week > ago) we’re seeing 0 temp files altogether (grabbing that info from > pg_stat_database). > > So, as far as we know: > > 1) It’s not WAL > 2) It’s not tempfiles > 3) It’s not the size of the error logs > 4) It’s not the size of the actual rows in the database or the indexes > > Another thread we found suggested pg_subtrans — this seems less likely > because we’ve been able to replicate this across many different types of > connections etc. but thought it might be a potential source. > > Any other system-monitoring queries that we can run that might further > illuminate the issue? > > Thank you! > > > On May 14, 2018, at 3:31 PM, Jonathan Marks < > jonathanaveryma...@gmail.com> wrote: > > > > We’ll turn on log_temp_files and get back to you to see if that’s the > cause. Re: the exact queries — these are just normal INSERTs and UPDATEs. > This occurs as part of normal database operations — i.e., we are processing > 10% of a table and marking changes to a particular row, or happen to be > inserting 5-10% of the table volume with new rows. Whenever we bulk load we > have to drop the indexes because the disk space loss just isn’t tenable. > > > > Re: extra disk space consumption not within PG — the AWS folks can’t > tell me what the problem is because it’s all internal to the PG part of the > instance they can’t access. Doesn’t mean your last suggestion can’t be the > case but makes it slightly less likely. > > > > Any chance that GIN indexes are double-logging? I.e. with fastupdate off > they are still trying to keep track of the changes in the pending list or > something? > > > > Our thought has been temp files for a while, but we’re not sure what we > should do if that turns out to be the case. > > > >> On May 14, 2018, at 3:08 PM, Tom Lane wrote: > >> > >> [ please keep the list cc'd ] > >> > >> Jonathan Marks writes: > >>> Thanks for your quick reply. Here’s a bit more information: > >>> 1) to measure the “size of the database” we run something like `select > datname, pg_size_pretty(pg_database_size(datname)) from pg_database;` I’m > not sure if this includes WAL size. > >>> 2) I’ve tried measuring WAL size with `select sum(size) from > pg_ls_waldir();` — this also doesn’t budge. > >>> 3) Our current checkpoint_timeout is 600s with a > checkpoint_completion_target of 0.9 — what does that suggest? > >> > >> Hmph. Your WAL-size query seems on point, and that pretty much destroys > >> my idea about a WAL emission spike. > >> > >> pg_database_size() should include all regular and temporary > tables/indexes > >> in the named DB. It doesn't include WAL (but we've eliminated that), > nor > >> cluster-wide tables such as pg_database (but those seem pretty unlikely > >> to be at issue), nor non-relation temporary files such as sort/hash temp > >> space. At this point I think we have to focus our attention on what > might > >> be creating large temp files. I do not see anything in the GIN index > code > >> that could do that, especially not if you have fastupdate off. I wonder > >> whether there is something about the particular bulk-insertion queries > >> you're using that could result in large temp files --- which'd make the > >> apparent correlation with GIN index use a mirage, but we're running out > >> of other ideas. You could try enabling log_temp_files to see if there's > >> anything to that. > >> > >> In the grasping-at-straws department: are you quite sure that the extra > >> disk space consumption is PG's to begin with, rather than something > >> outside the database entirely? > >> > >> regards, tom lane > > > > >
Re: Disabling/Enabling index before bulk loading
On Tue, Jul 10, 2018 at 12:26 PM Ravi Krishna wrote: > > > > https://fle.github.io/temporarily-disable-all-indexes-of-a-postgresql-table.html > > > > > This does not work in RDS. In order to update system catalog tables > (pg_index), one needs privileges which is > denied in RDS. In RDS terminology, the user must belong to role rdsadmin > and that role is reserved only for AWS > internal users. > Right, but pl/pgsql works fine there, so you can backup&drop all indexes and then restore them using `do $$ ... $$ language plpgsql;`, as Thomas Kellerer already suggested. One note here: there is some old confusion between concepts of unique indexes and unique constraints in Postgres (see https://www.postgresql.org/message-id/flat/CANNMO%2BKHkkDg-FBi0_78ADmfLiT9kODmz%2B8m6fR6f5kPL-n_ZQ%40mail.gmail.com#cfb3a9eaed8649d7d24ad7944ccb37cf), so there is a chance that after such backup/restore procedure you'll get logically correct but phisically different state.
Re: Upgrade/Downgrade
On Thu, Aug 23, 2018 at 11:44 AM Joshua D. Drake wrote: > On 08/23/2018 04:47 AM, Achilleas Mantzios wrote: > > On 23/08/2018 14:30, Sonam Sharma wrote: > >> No, I didn't. The version is not compatible with application, so need > >> to downgrade it > > > > Have you tried to tweak postgresql.conf ? > > What do you mean is not compatible? What's the problem? > > My guess would be that they have a vendor supported application that is > *only* supported on 9.5. I run into this more often than I like. > Same story. I suppose it's related (at least partially) xlog->wal and **_location->**_lsn renamings in function names (full list made in Postgres 10: https://wiki.postgresql.org/wiki/New_in_postgres_10#Renaming_of_.22xlog.22_to_.22wal.22_Globally_.28and_location.2Flsn.29 ) Those 10's changes are really painful. I wish there were be some grace period, say 2-3 years, when both pg*_xlog_* function names would also work as aliases for pg*_wal_**. BTW, if the reason of downgrading is really related only these renamings, it might be less stressful just to create function aleases, like: create function pg_catalog.pg_last_xlog_receive_location() returns pg_lsn as $$ select pg_current_wal_lsn(); $$ language sql; + granting proper permissions to specified DB roles, where needed. Of course, it's hacky and should be only as a temporary solution until the app is adjusted to support Postgres 10+. But it gives an ability to have all good things added to Postgres 9.6 and 10.
Re: Can you please suggest how to configure hot_standby_feedback?
On Sat, Nov 2, 2019 at 8:52 PM Konstantin Gredeskoul wrote: > A related question is — *how can you avoid doing auto vacuum during the > busy peak hours, and do more aggressive auto vacuuming at night during low > traffic?* Is that possible? > > I tried achieving that with a manual vacuum, but that causes all replicas > to get far behind while it’s running, which is also not acceptable. > What is the main concern here? Disk IO? So we want to allow autovacuum workers to hit our disks less during busy hours and more during quiet hours, right? If so, then one of the ideas here could be the changing quotas for the autovacuum workers dynamically using cron or its alternative: 1) throttle autovacuum before peak hours, changing autovacuum_vacuum_cost_delay (or autovacuum_vacuum_cost_limit). The pre-12 default settings (_cost_delay 20ms and cost_limit 200) will give all the workers the shared "quota" that means, roughly, 8 MiB/s for reads, less for writes (good article: https://www.2ndquadrant.com/en/blog/autovacuum-tuning-basics/), and in 12 (where _cost_delay was reduced to 2ms) it will be ~80 MiB/s. Monitoring + logs analysis is needed to understand real IO though. Individually tuned tables will have individual quotas. 2) change those settings back to normal after busy hours. In any case, I highly recommend to monitor "autovacuum queue" -- the list of tables that should be already autovacuumed, but they are not, due to some reasons. Having proper graphs in monitoring and alerts if the size of this "queue" exceeds some reasonable number (say, 2 * autovacuum_max_workers) will help a lot to understand the autovacuum behavior for the concrete system. Here is an excellent example of how to do it, from Avito: https://github.com/avito-tech/dba-utils/blob/master/munin/vacuum_queue, and here is my version, with some improvements: https://gitlab.com/snippets/1889668.
Re: RPC via WebSockets.
This is a very common task, definitely. As for WebSockets, there is an addition to PostgREST, postgrest-websockets https://github.com/diogob/postgres-websockets#readme PostgREST is not only for "give me contents", you can write functions (in any PL) and call them using POST /rpc/functionname. There is also an attempt to build similar tool in Go (although I'm not sure how popular and powerful it is): pREST https://github.com/prest/prest Finally, there is a whole line of trendy and quite popular tools for having GraphQL on top of Postgres, including but not limited to: - PostGraphile https://github.com/graphile/postgraphile - Hasura https://github.com/hasura/graphql-engine - Prisma https://github.com/prisma/prisma On Fri, Jan 17, 2020 at 13:18 Dmitry Igrishin wrote: > Hello! > > Who needs to talk to Postgres right from a WEB-browser via WebSockets? For > example, conveniently call storable functions/procedures? > > Yes, i know about PostgREST. But I want to focus on RPC via WebSockets > without all these REST or "give me contents of that table by this URL" > stuff... > > I'm thinking about writing an application which is simple to > install/deploy, simple to configure, with a connection pool built-in. > Ideally, one binary and one config in JSON format. > > I need some feedback before start. So please don't hesitate to share any > of your thoughts/ideas/comments/scepticism here :-) > > Thanks. > >
Re: POLL: Adding transaction status to default psql prompt
+1 of course On Wed, Feb 5, 2020 at 6:55 PM Vik Fearing wrote: > Hello, > > I proposed a patch to add %x to PROMPT1 and PROMPT2 by default in psql. > > The effect of this is: > > - nothing at all when not in a transaction, > - adding a '*' when in a transaction or a '!' when in an aborted > transaction. > > Before making a change to a long-time default, a poll in this group was > requested. > > Please answer +1 if you want or don't mind seeing transaction status by > default in psql or -1 if you would prefer to keep the current default. > > Thanks! > > +1 from me. > -- > Vik Fearing > > >
Re: POLL: Adding transaction status to default psql prompt
On Wed, Feb 5, 2020 at 8:05 PM Vik Fearing wrote: > Because it isn't always easy to modify the .psqlrc file. This is > especially true if you frequently connect to other people's systems. > Exactly. You can tune your psql a lot, including excellent pspg, but then you frequently troubleshoot other people's systems, you deal with defaults. And finally, the default settings is what most people will always use.
Re: Christopher Browne
What a sad news. Rest In Peace, Christopher. Condolences to friends and family. On Wed, Nov 4, 2020 at 15:29 Steve Singer wrote: > > It is with much sadness that I am letting the community know that Chris > Browne passed away recently. > > Chris had been a long time community member and was active on various > Postgresql mailing lists. He was a member of the funds committee, the > PgCon > program committee and served on the board of the PostgreSQL Community > Association of Canada. Chris was a maintainer of the Slony > replication system and worked on various PG related tools. > > I worked with Chris for over 10 years and anytime someone had a problem he > would jump at the chance to help and propose solutions. He > always had time to listen to your problem and offer ideas or explain how > something worked. > > I will miss Chris > > Steve > > > >
Re: Check constraints do not seem to be working!!!
On Wed, Nov 11, 2020 at 12:26 AM Jitendra Loyal wrote: > Despite the above two constraints, the following rows get into the table: > insert into t (b , c) values (null, true), (null, false); > This behavior is described in the docs https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-CHECK-CONSTRAINTS : > It should be noted that a check constraint is satisfied if the check expression evaluates to true or the null value. Since most expressions will evaluate to the null value if any operand is null, they will not prevent null values in the constrained columns. To ensure that a column does not contain null values, the not-null constraint described in the next section can be used.
Re: What have I done!?!?!? :-)
On Thu, Apr 7, 2022 at 8:10 AM Jan Wieck wrote: > So **IF** Active Record is using that feature, then it can dump any > amount of garbage into your PostgreSQL database and PostgreSQL will > happily accept it with zero integrity checking. > It's DISABLE TRIGGER ALL https://github.com/rails/rails/blob/831031a8cec5bfe59ef653ae2857d4fe64c5698d/activerecord/lib/active_record/connection_adapters/postgresql/referential_integrity.rb#L12
Re: Patroni vs pgpool II
On Thu, Apr 6, 2023 at 9:17 PM Tatsuo Ishii wrote: > With quorum failover is enabled, w0, w1, and w2 communicate each other > to vote who is correct (if it cannot communicate, it regards other > watchdog is down). In the case above w0 and w1 are majority and will > win. Communication takes time – network latencies. What if during this communication, the situation becomes different? What if some of them cannot communicate with each other due to network issues? What if pg1 is currently primary, pg0 is standby, both are healthy, but due not network issues, both pg1 and w2 are not reachable to other nodes? Will pg1 remain primary, and w0 and w1 decide to promote pg0?
Re: Patroni vs pgpool II
On Thu, Apr 6, 2023 at 11:13 PM Tatsuo Ishii wrote: > I am welcome you to > join and continue the discussion on pgpool mailing list. I truly believe that this problem – HA – is PostgreSQL's, not 3rd party's. And it's a shame that Postgres itself doesn't solve this. So we're discussing it here. > > What if pg1 is currently primary, pg0 is standby, both are healthy, but > > due not network issues, both pg1 and w2 are not reachable to other > > nodes? Will pg1 remain primary, and w0 and w1 decide to promote pg0? > > pg1 will remain primary but it is set to "quarantine" state from > pgpool's point of view, which means clients cannot access pg1 via > pgpool. So we have a split brain here – two primaries. Especially if some clients communicate with PG directly. And even if there are no such clients, archive_command is going to work on both nodes, monitoring will show two primaries confusing humans (e.g, SREs) and various systems, if we have many standby nodes, some of them might continue replicating from the old primary if they happen to be in the same network partition, and so on. I don't see how all these things can be solved with this approach.
Re: pg_checksums?
On Mon, Oct 30, 2023 at 6:57 AM Alexander Kukushkin wrote: ... > As Michael already said, the following workflow works just fine (I did it > dozens of times): > 1. enable checksums on the standby node > 2. start the standby and let it catch up with the primary > 3. switchover to a standby node > 4. enable checksums on the former primary (now replica). There is also a good trick described in https://www.crunchydata.com/blog/fun-with-pg_checksums to avoid accidental start of Postgres: after pg_ctl stop and before pg_checksums --enable, do: mv data/pg_twophase data/pg_twophase.DO_NOT_START_THIS_DATABASE and once pg_checksums --enable is done, move it back. Additionally, I compiled some thoughts about running pg_checksums without downtime (Patroni-friendly, of course) here: https://twitter.com/samokhvalov/status/1719961485160689993.
Re: Framework for 0 downtime deploys
No silver bullets exist (yet). A couple of things I can recommend: 1. GitLab's experience of changing schema without downtime and maintaining backward compatibility – they have open documentation and a lot of things solved and documented - start here: https://docs.gitlab.com/ee/development/migration_style_guide.html - check their migration_helpers.rb, plenty of useful things there 2. What my team and I are doing with respect to database migration testing: https://postgres.ai/. We created Database Lab Engine, an open-source tool to clone databases of any size in seconds and test, manually or automatically in CI, anything you want using "full-size" databases. It can help you catch and block dangerous changes leading to downtime, as well as (if you have a well-maintained set of tests for CI) enforce the backward compability. Nik On Sat, May 22, 2021 at 2:12 PM Zahir Lalani wrote: > Confidential > > Hello All > > > > I wonder if I could garner some of the wealth of experience on this group: > > > > Our current application deployments (every 3 weeks) require about 30min > downtime. We are now tasked of making this 0 downtime. > > From all the reading I have done, we have solutions for the infrastructure > and code deploy, but with regards to the DB the main issue seems to be > keeping the new deploy backwards compatible – functions/tables/fields – all > of it. > > > > That seems like quite a large management task and would require careful > reviews of changes. Is there any type of framework that already manages > this type of capability? Or are there aspects of PG that we should be using > in this regard? > > > > Thx > > > > Z >
Re: bottom / top posting
My thoughts: https://twitter.com/samokhvalov/status/1403408281389789189. Apologies for top-posting. On Fri, Jun 11, 2021 at 10:04 AM Bruce Momjian wrote: > On Thu, Jun 10, 2021 at 10:21:00PM +0200, Peter J. Holzer wrote: > > > I wonder about the tolerance of the world we live in. Somehow, I can > deal with > > > top-posting, bottom-posting, middle-posting, HTML eMail, straight-text > eMails, > > > 80-column eMails, variable-width eMails, occasional ALL CAPS eMails, & > stupid > > > multi-line signatures, all without getting my tail in a knot over it. > > > > I can deal with it. Especially when I'm paid for it. I think it's a > > waste of my time (but if somebody else pays ...) and it is inefficient, > > as it is very easy to overlook relevant details in that ever-growing > > mess. I never understood why so many people hated e-mail as a > > communication medium. Now I do. > -- > > OK, that was profound. I also wondered why people hate email, and now > also know the answer. I also can't figure out how people can use > streaming chat for complex, multi-day communication since it feels just > like top-posting email. > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > If only the physical world exists, free will is an illusion. > > > >
Re: Upgrade 9.5 cluster on Ubuntu 16.04
Logical way – dump/restore. Bringing PGDATA physically may lead to corrupted indexes due to glibc version changes. 16.04 -> 18.04 shouldn't cause it, but it may. You can check btree index with amcheck and GIN indexes with a patched version of it (some backporting would be needed). You can find examples here: https://gitlab.com/-/snippets/2001962 But back to the question, if you do dump/restore, indexes will be freshly created, on new machine - therefore there is no question about corruption for them. I'd choose this path in your situation. On Thu, Sep 2, 2021 at 10:38 AM Vano Beridze wrote: > Hello, > > I've got 2 VMs with Postgresql 9.5 cluster with streaming replication. > VMs have Ubuntu 16.04. > I would like to upgrade Ubuntu and Postgresql to newer versions. > Ubuntu 16.04 supports upgrading to 18.04. > What is the safest way to upgrade Postgresql cluster along with it? > The database is not big and I can afford completely shutdown the cluster > during the upgrade. > > What would you suggest? > > Kind regards, > Vano >
Re: Upgrade 9.5 cluster on Ubuntu 16.04
Forgot to mention: the same concerns about possible index corruption are relevant for the pg_upgrade option too (because it doesn't rebuild indexes). So, I'd definitely choose dump/restore if the database is small. In the case of pg_upgrade, I would rebuild all the indexes during the maintenance window or just some of them (which ones – amcheck can help to answer, for 9.5 it should be taken from here: https://github.com/petergeoghegan/amcheck). On Thu, Sep 2, 2021 at 11:17 AM Nikolay Samokhvalov wrote: > Logical way – dump/restore. > > Bringing PGDATA physically may lead to corrupted indexes due to glibc > version changes. 16.04 -> 18.04 shouldn't cause it, but it may. You can > check btree index with amcheck and GIN indexes with a patched version of it > (some backporting would be needed). You can find examples here: > https://gitlab.com/-/snippets/2001962 > > But back to the question, if you do dump/restore, indexes will be freshly > created, on new machine - therefore there is no question about corruption > for them. I'd choose this path in your situation. > > On Thu, Sep 2, 2021 at 10:38 AM Vano Beridze wrote: > >> Hello, >> >> I've got 2 VMs with Postgresql 9.5 cluster with streaming replication. >> VMs have Ubuntu 16.04. >> I would like to upgrade Ubuntu and Postgresql to newer versions. >> Ubuntu 16.04 supports upgrading to 18.04. >> What is the safest way to upgrade Postgresql cluster along with it? >> The database is not big and I can afford completely shutdown the cluster >> during the upgrade. >> >> What would you suggest? >> >> Kind regards, >> Vano >> >
Re: AWS vs GCP storage
On Tue, Feb 22, 2022 at 12:27 PM Torsten Förtsch wrote: > Hi, > > I have a few databases with several TB-sized tables. We recently moved one > of those databases from AWS EC2 to GCP. Today I ran VACUUM FREEZE on those > tables and every time I saw our application freezing up (and throwing tons > of errors) for a few minutes right after the message about the end of > vacuum (duration: 30182257.392 ms statement: vacuum freeze verbose ...). > We never saw anything like that on AWS. > > The database is 9.6. At the moment I have no details about the storage > configuration on GCP. The machine was set up by another department. > > Any suggestions on why that is happening and how to prevent it would be > appreciated. > The first thing to check is that you're using SSD PDs, not regular PDs. Second: GCP has 2 rules for disk IO throttling (independent for regular PDs and SSD PDs): based on disk size, and based on the number of vCPUs. You need more vCPUs to get maximum disk IO, which may be counter-intuitive. I suggest checking out the docs and benchmarking the disks using fio: - https://cloud.google.com/compute/docs/disks/performance - https://cloud.google.com/compute/docs/disks/benchmarking-pd-performance
Re: Meaning of query age in pg_stat_activity
On Mon, Jan 29, 2018 at 3:06 PM, Jan De Moerloose wrote: ... > SELECT pid, age(clock_timestamp(),query_start) as age, usename, query, > state from pg_stat_activity order by age; > > When the cpu is 100% and the app slowing down, i can see that some queries > have a long age. > What is the value of "state" column for those queries?
Re: Meaning of query age in pg_stat_activity
On Mon, Jan 29, 2018 at 3:19 PM, Jan De Moerloose wrote: > The state is 'idle in transaction'. > So you have long-running *transactions*, not queries. This is not good for an OLTP system, because some transaction can wait of others, which are "idle in transaction" but do nothing at the moment. Think how you can make them shorter, commit faster. Also, if your server version is 9.6+ consider setting idle_in_transaction_session_timeout to some low value: https://www.postgresql.org/docs/current/static/runtime-config-client.html#GUC-IDLE-IN-TRANSACTION-SESSION-TIMEOUT
Re: Meaning of query age in pg_stat_activity
On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose wrote: > So the query is just the latest query and the time is the transaction time > since this query, i suppose ? > Thanks for your answer, i will try to make the transaction shorter as you > suggest. > Yep. This is a very common confusion, however it usually happens regarding records with "state" = 'idle' – notice that they also have something in "query" column while they are actually doing nothing.
Re: Meaning of query age in pg_stat_activity
On Mon, Jan 29, 2018 at 9:52 PM, Nikolay Samokhvalov wrote: > > > On Mon, Jan 29, 2018 at 3:45 PM, Jan De Moerloose > wrote: > >> So the query is just the latest query and the time is the transaction >> time since this query, i suppose ? >> Thanks for your answer, i will try to make the transaction shorter as you >> suggest. >> > > Yep. This is a very common confusion, however it usually happens regarding > records with "state" = 'idle' > – notice that they also have something in "query" column while they are > actually doing nothing. > One correction: "the time is the transaction time since this query" is not an accurate statement. You mentioned query_start – it reflects the last query's starting time, not transaction's. There are other timestamps: backend_start, xact_start, and state_change. All of them are useful in different contexts.
