Re: Weirdness (bug?) with aggregates and subqueries
On Wed, 2023-11-08 at 16:36 -0500, Tom Lane wrote: > Laurenz Albe writes: > > I found this in a blog > > (https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/): > > CREATE TABLE aa (a INT); > > INSERT INTO aa VALUES (1), (2), (3); > > CREATE TABLE xx (x INT); > > INSERT INTO xx VALUES (10), (20), (30); > > > SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; > > >sum > > ═ > > 6 > > (1 row) > > > Huh? Shouldn't that return three rows, just like > > No. The aggregate function is semantically of the closest query level > that contributes a Var to its argument, so it's evaluated at the "FROM > aa" level, causing that level to become an aggregated query that > returns just one row. Then it acts like an outer reference as far > as the sub-select is concerned. This is documented at the end of > Section 4.2.7 in our manual, > > https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES > > Thank the SQL spec for that weirdness. Thanks for the explanation. Seems like another instance of the standard committee smoking the wrong stuff. Yours, Laurenz Albe
NUMA, PostgreSQL and docker images
Hi everyone, Does anyone here know if the default PostgreSQL images set NUMA policies? I am assuming not? Is there an easy way to make them do this? -- Best Wishes, Chris Travers Efficito: Hosted Accounting and ERP. Robust and Flexible. No vendor lock-in. http://www.efficito.com/learn_more
Re: Server process exited with exit code 4
Finally, we used the Windows tool Gflags.exe and found that other software terminated the postgres process. Replied Message | From | yangsr3411 | | Date | 10/30/2023 19:07 | | To | pgsql-general | | Cc | | | Subject | Fw: Server process exited with exit code 4 | it doesn't seem like a hardware issue because we have over 20 machines running and 5 of them have been experiencing this issue recently. we have encountered problems before with antivirus software causing the database to hang, so we are also considering if other software on the Windows operating system is interfering. Forwarded Message | From | Justin Clift | | Date | 10/30/2023 14:29 | | To | yangsr3411 | | Cc | pgsql-general | | Subject | Re: Server process exited with exit code 4 | On 2023-10-30 14:02, yangsr3411 wrote: > Has anyone encountered similar problem or may know a solution? Just to rule out hardware problems, does the server hardware have some way of showing things like ECC memory errors and similar? Most official server hardware (HPE, Dell, etc) have utilities that can show a log of any recent weirdness that occurred at a hardware level. If yours can, take a look for things like ECC errors or any other strange stuff. :) Regards and best wishes, Justin Clift
Re: Weirdness (bug?) with aggregates and subqueries
Laurenz Albe writes: > I found this in a blog > (https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/): > CREATE TABLE aa (a INT); > INSERT INTO aa VALUES (1), (2), (3); > CREATE TABLE xx (x INT); > INSERT INTO xx VALUES (10), (20), (30); > SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; >sum > ═ > 6 > (1 row) > Huh? Shouldn't that return three rows, just like No. The aggregate function is semantically of the closest query level that contributes a Var to its argument, so it's evaluated at the "FROM aa" level, causing that level to become an aggregated query that returns just one row. Then it acts like an outer reference as far as the sub-select is concerned. This is documented at the end of Section 4.2.7 in our manual, https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-AGGREGATES Thank the SQL spec for that weirdness. regards, tom lane
Weirdness (bug?) with aggregates and subqueries
I found this in a blog (https://buttondown.email/jaffray/archive/sql-scoping-is-surprisingly-subtle-and-semantic/): CREATE TABLE aa (a INT); INSERT INTO aa VALUES (1), (2), (3); CREATE TABLE xx (x INT); INSERT INTO xx VALUES (10), (20), (30); SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; sum ═ 6 (1 row) Huh? Shouldn't that return three rows, just like SELECT (SELECT sum(14) FROM xx LIMIT 1) FROM aa; sum ═ 42 42 42 (3 rows) Looking at the plan of the weird query, the aggregate seems to be in the wrong place: EXPLAIN (COSTS OFF) SELECT (SELECT sum(a) FROM xx LIMIT 1) FROM aa; QUERY PLAN ══ Aggregate -> Seq Scan on aa SubPlan 1 -> Limit -> Seq Scan on xx (5 rows) And this gives an error: SELECT a, (SELECT sum(a) FROM xx LIMIT 1) FROM aa; ERROR: column "aa.a" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT a, (SELECT sum(a) FROM xx LIMIT 1) FROM aa; ^ I think that the optimizer is going astray here... But perhaps I am missing something obvious. Yours, Laurenz Albe
Re: Maximum xid increasing
On Wed, 2023-11-08 at 09:09 -0500, Craig Milhiser wrote: > Our max xid are over 500m and climbing. Auto vacuuming and manual vacuuming > are returning > > autovacuuming to prevent wraparound. > WARNING: oldest xmin is far in the past > HINT: Close open transactions soon to avoid wraparound problems. > You might also need to commit or roll back old prepared transactions, or drop > stale replication slots. > > The autovacuum previously kicked in at 200m and reduced the maximum xid. > Now there is no reduction. > > We have a writer (master) node, 3 readers (slaves) and a hot-standby. We have > an ETL vendor hooked > up to replicate. All of the replications are working. There is current data > in each. We have checked > our replication slots. Everything is in use and working. > > From transactions, we have terminated every writer node PID since before this > climb has started. > Almost every client has been restarted. > > We are handling 100+ insert/updates per second. Most transactions complete in > ~2 seconds. Though some > may take 1 minute. There are no long running idle in transaction activities. > > We have been scouring the internet reading many helpful pages and trying > their advice but we cannot > find a problem. > > We have restored a daily snapshot in another cluster. After vacuuming the max > xid dropped to 50m. > No client was connected. Implying that a restart should fix the issue. > > I am searching for any hints. We have not found a smoking gun to focus on. > This is a 24x7 operation > and would rather not have to restart the server. > > We are on v13.8, AWS RDS. > > Is there a way to find which row or table or lock or some open transaction is > preventing the vacuuming > from lowering xid? Any hints on where to look? If that is not a bug introduced by Amazon, and you are certain that there are no long-running transactions and stale replication slots, there are two more possibilities: 1. a prepared transaction (check pg_prepared_xacts) 2. a long-running query on a standby, and "hot_standby_feedback = on" Yours, Laurenz Albe
Re: No longer available installer for greater version then PostgreSQL 10
On 11/8/23 09:04, Shashikanth Bhardwaj wrote: Hi Team, I am facing some problem because of unavailability of PostgreSQL installer greater then 10 version of PostgreSQL in my case I need postgreSQL-14.0 version installer but unfortunately now not available. Why we used the PostgreSQL -10 version to install the PostgreSQL with the installer : 1. Easy to install 2. Installed offline without any dependency 3. Easy to configure the installation directory 4. Less time taken to install Why its been stopped provide the installer for the Linux and available for other operating systems ? Per my answer here: https://www.postgresql.org/message-id/33987a81-16ca-45f7-a18c-5e2ae6403449%40aklaver.com "Or are you referring to?: https://www.enterprisedb.com/downloads/postgres-postgresql-downloads In that case the page has this to say: *Note: EDB no longer provides Linux installers for PostgreSQL 11 and later versions, and users are encouraged to use the platform-native packages* " This is something you will need to take up with EDB as they are maintaining that particular installer. If it is no longer available then which will be the best option to install the postgres with above mentioned points ? Can I create the installer for the PostgreSQL-14.0 version ? Or can I get similar like installer for 14.0 of PostgreSQL ? Regards, Shashikanth -- Adrian Klaver adrian.kla...@aklaver.com
Re: Finding execution plan
Hello Bruce, I do not see parameters starting with auto_explain. in configuration file and also do I need to enable logging collector. Please guide me on this. On Fri, 3 Nov, 2023, 11:24 pm Bruce Momjian, wrote: > On Fri, Nov 3, 2023 at 10:44:12AM -0700, Adrian Klaver wrote: > > > > On 11/3/23 10:40 AM, shashidhar Reddy wrote: > > > > Hello All, > > > > I have a Postgresql function scheduled to run on the database, some > tines > > it is taking too long than usual time, is it possible to get the > execution > > plan of this function or any other way to troubleshoot > > > > > > You might to take a look at the contrib module: > > > > > > https://www.postgresql.org/docs/current/pgstatstatements.html > > > > > > "The pg_stat_statements module provides a means for tracking planning and > > execution statistics of all SQL statements executed by a server." > > Since he asked about execution plans, I was thinking auto_explain: > > https://www.postgresql.org/docs/current/auto-explain.html > > -- > Bruce Momjian https://momjian.us > EDB https://enterprisedb.com > > Only you can decide what is important to you. >
Logical replication (pgoutput plugin) in streaming mode: peek() always starts from beginning of transaction, not from latest stream block
Hi everyone,
When using logical replication with the pgoutput plugin, on PG 16,we do the
following:
1) SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null,
null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'false')
2) Get LSN of last row (Commit)
3) SELECT * FROM pg_replication_slot_advance('test_slot_v1', );
4) Repeat.
And this works perfectly fine when streaming = false. When turning on streaming
the expectation is that the same thing happens, except the the LSN being passed
to pg_replication_slot_advance() is for a Stream End record. On the next call
to pg_logical_slot_peek_binary_changes() we should get the subsequent Stream
Start record. But instead, the stream starts over from the transaction Begin
record. Observe:
*** Demo starts ***
*** Initially there are no changes, peek() returns nothing: ***
=> SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null,
null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'true')
WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44');
lsn | xid | data
-+-+--
(0 rows)
*** Slot status: ***
=> SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM
pg_replication_slots; slot_name | active | restart_lsn | confirmed_flush_lsn
--++-+-
test_slot_v1 | f | 2/98CE060 | 2/98CE060
(1 rows)
*** Now make some changes (delete then insert a bunch of records) and call
peek() ***
*** The predicate filters out Delete and Insert records, leaving Stream Start
(\x53 = S), ***
*** Relation (\x52 = R), Stream End (\x45 = E), and Stream Commit (\x63 = c)
***
abinitio=> SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1',
null, null,'publication_names', 'cdc', 'proto_version', '4', 'streaming',
'true') WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44');
lsn | xid | data
+--+--
2/A222A20 | 1112 | \x53045801
2/A222A20 | 1112 |
\x520459402a7075626c6963007265706c69636174696f6e5f746573745f76310064000201696417006e616d6519
2/C141BE8 | 1112 | \x45
2/C141C28 | 1112 | \x53045800
2/DF598D8 | 1112 | \x45
2/DF59950 | 1112 |
\x630458020df5991800020df59952aca72900f8a8
2/DF59950 | 1114 | \x53045a01
2/DF59950 | 1114 |
\x52045a402a7075626c6963007265706c69636174696f6e5f746573745f76310064000201696417006e616d6519
2/108918D0 | 1114 | \x45
2/108918D0 | 1114 | \x53045a00
2/131E1310 | 1114 | \x45
2/131E1310 | 1114 | \x53045a00
2/137D7768 | 1114 | \x45
2/137E8448 | 1114 |
\x63045a02137e84180002137e84480002aca729812c96
(14 rows)
*** It was a peek() so the status is unchanged: ***
=> SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM
pg_replication_slots; slot_name | active | restart_lsn | confirmed_flush_lsn
--++-+-
test_slot_v1 | f | 2/98CE060 | 2/98CE060
(1 rows)
*** Now advance the slot to the first Stream End record: ***
=> SELECT * FROM pg_replication_slot_advance('test_slot_v1', '2/C141BE8');
slot_name | end_lsn
--+---
test_slot_v1 | 2/C141BE8
(1 row)
*** confirmed_flush_lsn is updated as expected:
=> SELECT slot_name, active, restart_lsn, confirmed_flush_lsn FROM
pg_replication_slots;
slot_name | active | restart_lsn | confirmed_flush_lsn
--++-+-
test_slot_v1 | f | 2/9B09D10 | 2/C141BE8
(1 rows)
*** Now peek() again. It is starting from earlier than confirmed_flush_lsn: ***
=> SELECT * FROM pg_logical_slot_peek_binary_changes('test_slot_v1', null,
null,'publication_names', 'cdc', 'proto_version', '4', 'streaming', 'true')
WHERE SUBSTRING(data, 1,1) NOT IN ('\x49', '\x44');
lsn | xid | data
+--+--
2/A222A20 | 1112 | \x53045801
2/A222A20 | 1112 |
\x520459402a7075626c6963007265706c69636174696f6e5f746573745f76310064000201696417006e616d6519
2/C141BE8 | 1112 | \x45
2/C141C28 | 1112 | \x53045800
2/DF598D8 | 1112 | \x45
2/DF59950 | 1112 |
\x630458020df5991800020df59952aca72900f8a8
2/DF59950 | 1114 | \x53045a01
2/DF59950 | 1114 |
\x52045a402a7075626c6963007265706c69636174696f6e5f746573745f76310064000201696417006e616d6519
2/108918D0 | 1114 | \x45
2/108918D0 | 1114 | \x53045a00
2/131E1310 | 1114 | \x45
2/131E1310 | 1114 | \x53045a00
2/137D7768 | 1114 | \x45
2/137E8448 | 1114 |
\x63045a02137e84180002137e84480002aca729812c96
(14 rows)
Re: No longer available installer for greater version then PostgreSQL 10
On 11/8/23 11:04, Shashikanth Bhardwaj wrote: Hi Team, I am facing some problem because of unavailability of PostgreSQL installer greater then 10 version of PostgreSQL in my case I need postgreSQL-14.0 version installer but unfortunately now not available. Which OS? Where are you looking? Why we used the PostgreSQL -10 version to install the PostgreSQL with the installer : 1. Easy to install 2. Installed offline without any dependency 3. Easy to configure the installation directory 4. Less time taken to install Why its been stopped provide the installer for the Linux and available for other operating systems ? If it is no longer available then which will be the best option to install the postgres with above mentioned points ? Can I create the installer for the PostgreSQL-14.0 version ? Or can I get similar like installer for 14.0 of PostgreSQL ? Regards, Shashikanth -- Born in Arizona, moved to Babylonia.
No longer available installer for greater version then PostgreSQL 10
Hi Team, I am facing some problem because of unavailability of PostgreSQL installer greater then 10 version of PostgreSQL in my case I need postgreSQL-14.0 version installer but unfortunately now not available. Why we used the PostgreSQL -10 version to install the PostgreSQL with the installer : 1. Easy to install 2. Installed offline without any dependency 3. Easy to configure the installation directory 4. Less time taken to install Why its been stopped provide the installer for the Linux and available for other operating systems ? If it is no longer available then which will be the best option to install the postgres with above mentioned points ? Can I create the installer for the PostgreSQL-14.0 version ? Or can I get similar like installer for 14.0 of PostgreSQL ? Regards, Shashikanth
Re: PGE-XX000: ERROR: Problem with the SSL CA cert (path? access rights?)
Johnathan Tiamoh writes: > How can this error be resolved? > PGE-XX000: ERROR: Problem with the SSL CA cert (path? access rights?) > Is there any postgresql documentation on this ? No, because it's not a Postgres error: there is no such message string anywhere in our source code. You should start by trying to identify what component is actually throwing that error. regards, tom lane
PGE-XX000: ERROR: Problem with the SSL CA cert (path? access rights?)
Hello How can this error be resolved? PGE-XX000: ERROR: Problem with the SSL CA cert (path? access rights?) Is there any postgresql documentation on this ? Kind regards Johnathan T.
Maximum xid increasing
Our max xid are over 500m and climbing. Auto vacuuming and manual vacuuming are returning autovacuuming to prevent wraparound. WARNING: oldest xmin is far in the past HINT: Close open transactions soon to avoid wraparound problems. You might also need to commit or roll back old prepared transactions, or drop stale replication slots. The autovacuum previously kicked in at 200m and reduced the maximum xid. Now there is no reduction. We have a writer (master) node, 3 readers (slaves) and a hot-standby. We have an ETL vendor hooked up to replicate. All of the replications are working. There is current data in each. We have checked our replication slots. Everything is in use and working. >From transactions, we have terminated every writer node PID since before this climb has started. Almost every client has been restarted. We are handling 100+ insert/updates per second. Most transactions complete in ~2 seconds. Though some may take 1 minute. There are no long running idle in transaction activities. We have been scouring the internet reading many helpful pages and trying their advice but we cannot find a problem. We have restored a daily snapshot in another cluster. After vacuuming the max xid dropped to 50m. No client was connected. Implying that a restart should fix the issue. I am searching for any hints. We have not found a smoking gun to focus on. This is a 24x7 operation and would rather not have to restart the server. We are on v13.8, AWS RDS. Is there a way to find which row or table or lock or some open transaction is preventing the vacuuming from lowering xid? Any hints on where to look? Thanks
