Re: What to look for when excessively long commits
On Fri, 2021-07-09 at 06:35 +1000, Steve Baldwin wrote: > Hi all, > > If I'm seeing instances like this in our logs, what should I look for: > > 2021-07-06 22:15:34.702 > UTC,"bcaas_api","bcaas",8124,"10.122.45.33:46386",60e4d5e6.1fbc,222,"COMMIT",2021-07-06 > 22:15:02 UTC,37/0,0,LOG,0,"duration: 7128.250 ms","" > 2021-07-06 22:15:34.702 > UTC,"bcaas_api","bcaas",8483,"10.122.45.33:47274",60e4d5fc.2123,58,"COMMIT",2021-07-06 > 22:15:24 UTC,3/0,0,LOG,0,"duration: 8419.856 ms","" > 2021-07-06 22:15:34.702 > UTC,"bcaas_api","bcaas",7665,"10.122.37.247:37530",60e4d5ca.1df1,286,"COMMIT",2021-07-06 > 22:14:34 UTC,26/0,0,LOG,0,"duration: 8114.358 ms","" > 2021-07-06 22:15:34.702 > UTC,"bcaas_api","bcaas",6431,"10.122.45.33:42568",60e4d57f.191f,1828,"COMMIT",2021-07-06 > 22:13:19 UTC,40/0,0,LOG,0,"duration: 8839.643 ms","" > 2021-07-06 22:15:34.702 > UTC,"bcaas_api","bcaas",8484,"10.122.45.33:47276",60e4d5fc.2124,126,"COMMIT",2021-07-06 > 22:15:24 UTC,7/0,0,LOG,0,"duration: 8606.898 ms","" > 2021-07-06 22:15:34.703 > UTC,"bcaas_api","bcaas",8516,"10.122.37.247:41372",60e4d5fe.2144,94,"COMMIT",2021-07-06 > 22:15:26 UTC,16/0,0,LOG,0,"duration: 7877.650 ms","" > > The instance is in AWS RDS. It is a multi-az db.t3.xlarge machine class > running 10.6. It also has a read replica if that could be relevant. > > Thanks for any suggestions. Possible causes in decreasing order of likelihood: - I/O overload (look at I/O wait % in the CPU time). - large WITH HOLD cursors Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Why can't I drop a tablespace?
On Fri, 2021-07-09 at 20:04 +0100, Phil Endecott wrote: > =# 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: These files don't get cleaned up after a crash, so they may well be leftovers you can remove. Use \dt+ and \di+ to determine if the table or any of its indexes actually resides in the new tablespace. Don't forget the TOAST table. If they are all still in the original tablespace as they should be on account of the transactional guarantees, go ahead and manually remove the files. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Undocumented array_val[generate_series(...)] functionality?
On Sun, Jul 11, 2021 at 6:06 PM David Rowley wrote: > select abs(generate_series(-3,-1)); > > abs() is simply called once per output value of the generate_series > SRF. That seems fairly equivalent to me to what's going on in your > example case. > > Fair point. Both of these are premised on two related facts: One, the select generate_series(1,3) function call causes multiple rows to be generated where there would usually be only one. In short, SRF function calls and non-SRF function calls exhibit different behaviors on the output. Two, composition results in an inside-to-outside execution order: the SRF is evaluated first, the additional rows added, then the outer function (abs or the subscript function respectively in these examples) is evaluated for whatever rows are now present in the result. Is the above something one can learn from our documentation? Is this syntax we are discouraging users from using and thus intentionally not documenting it? I do get this impression but, frankly, given the utility of " [ generate_series(...) ] " I find it hard to recommend something different to get the same result yet don't have a place to point and say "here is how and why it works". David J.
Re: Removing a subscription that does not exist
At Fri, 9 Jul 2021 10:49:46 -0600, Jeff Ross wrote in
> Hello,
>
> I'm working with an RDS instance running 12 that has an old
> subscription that I can't seem to drop.
...
> It shows up here:
>
> mirror_admin@metro_logical> select * from pg_subscription;
>oid │ subdbid │ subname │ subowner │ ...
> ┼─┼─┼─┼ ...
> 83645 │66754 │ cargowel_common │16394 │ ...
> 83646 │66754 │ metro_prod │16394 │ ...
> 51490 │14313 │ metro│16394 │ ...
> (3 rows)
...
> But not in here:
>
> mirror_admin@metro_logical> \dRs+
> List of subscriptions
>Name │ Owner│ Enabled │ Publication
> ─┼───┼─┼───
> cargowel_common │ mirror_admin │ t │ {cargowel_common_prod}
> metro_prod │ mirror_admin │ t │ {metro_prod}
> (2 rows)
>
> And it can't be disabled or dropped:
Look at the subdbid field in the first query result. You were logging
into the databsae with OID=66754 and the subscription "metro" belongs
to the database 14313. The second command doesn't show metro which is
not of the current database.
> mirror_admin@metro_logical> alter subscription metro disable;
> ERROR: subscription "metro" does not exist
> Time: 24.263 ms
> mirror_admin@metro_logical> drop subscription metro;
> ERROR: subscription "metro" does not exist
> Time: 23.648 ms
>
> I did try deleting it directly from the pg_subscription table but that
> failed with a permission denied error. My suspicion is that's because
> of the RDS environment.
>
> What else can I try to remove this old non-functional subscription?
Thus you need to log in to the databse OID=14313 to manipulate on the
subsciption metro.
regards.
--
Kyotaro Horiguchi
NTT Open Source Software Center
Re: Undocumented array_val[generate_series(...)] functionality?
On Mon, 12 Jul 2021 at 12:58, David G. Johnston wrote: > > On Sun, Jul 11, 2021 at 5:43 PM David Rowley wrote: >> >> Isn't this implied by "Each subscript is itself an expression"? >> There's nothing special here with the SRF. That just produces 3 rows >> and passes the subscript as 1, 2 then 3. >> > > One can indeed infer that if the expression chosen for subscript is an SRF > that the resultant output will also be an SRF. I'd say in your example the array with the subscript does not become an SRF anymore than abs() becomes an SRF in the following: select abs(generate_series(-3,-1)); abs() is simply called once per output value of the generate_series SRF. That seems fairly equivalent to me to what's going on in your example case. David
Re: Undocumented array_val[generate_series(...)] functionality?
On Sun, Jul 11, 2021 at 5:43 PM David Rowley wrote: > Isn't this implied by "Each subscript is itself an expression"? > There's nothing special here with the SRF. That just produces 3 rows > and passes the subscript as 1, 2 then 3. > > One can indeed infer that if the expression chosen for subscript is an SRF that the resultant output will also be an SRF. It seems less than helpful to force the reader to infer that, IMO. Especially when the slice syntax, which could conceivably return either a sub-array OR a set, the later being the undocumented reality (our function examples document the actual outputs, these examples probably should do the same. I would be OK if we simply added an SRF example and showed the SRF result it produces without changing the wording - though this doesn't seem like a hard improvement to make. I'll offer something up if a committer at least agrees it could use some work). David J.
Re: Undocumented array_val[generate_series(...)] functionality?
On Mon, 12 Jul 2021 at 11:52, David G. Johnston wrote: > A post over in Reddit had an expression form I've never seen before: > > select (array[1,2,3,4]::integer[])[generate_series(1, 3)]; > Looking at subscripting in the SQL syntax this example doesn't seem to be > documented. > > https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-SUBSCRIPTS > > Is it documented somewhere else, and, regardless, shouldn't it be documented > in the linked location? Isn't this implied by "Each subscript is itself an expression"? There's nothing special here with the SRF. That just produces 3 rows and passes the subscript as 1, 2 then 3. David
Re: Undocumented array_val[generate_series(...)] functionality?
> On Jul 11, 2021, at 5:52 PM, David G. Johnston > wrote: > > > Hey, > > A post over in Reddit had an expression form I've never seen before: > > select (array[1,2,3,4]::integer[])[generate_series(1, 3)]; > === > 1 > 2 > 3 > > Looking at subscripting in the SQL syntax this example doesn't seem to be > documented. > > https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-SUBSCRIPTS > > Is it documented somewhere else, and, regardless, shouldn't it be documented > in the linked location? > > I get this is basically a variant of the slice syntax [n:m], and it is > working as expected, but nothing indicates I can put an SRF inside the > brackets. > > David J. Is that expression saying something like select the first second third element from array 1,2,3,4? >
Undocumented array_val[generate_series(...)] functionality?
Hey, A post over in Reddit had an expression form I've never seen before: select (array[1,2,3,4]::integer[])[generate_series(1, 3)]; === 1 2 3 Looking at subscripting in the SQL syntax this example doesn't seem to be documented. https://www.postgresql.org/docs/current/sql-expressions.html#SQL-EXPRESSIONS-SUBSCRIPTS Is it documented somewhere else, and, regardless, shouldn't it be documented in the linked location? I get this is basically a variant of the slice syntax [n:m], and it is working as expected, but nothing indicates I can put an SRF inside the brackets. David J.
libicu global support
Hi, during the adoption of Centos 8 on our servers we ran into problems with Postgresql (13.3), glibc (delivered by the Centos) and performance of sorting. Because of that we're planning to use the ICU collations (en-x-icu), but the current implementation is quite complicated to adopt as there isn't support of global setup per cluster (initdb) nor creating of database. So, my silly question: is there any chance a work can be done on it with a new version anytime soon? There were already some discussions around some time ago: https://www.postgresql.org/message-id/flat/3366.1498183854%40sss.pgh.pa.us#3366.1498183...@sss.pgh.pa.us https://www.postgresql.org/message-id/flat/5e756dd6-0e91-d778-96fd-b1bcb06c161a%402ndquadrant.com Thank you, - jj
Re: optimization issue
Hi,
Could you suggest me how should I teduce the heap blocks to optimise the
query ?
Regards.
On Thursday, July 8, 2021, Atul Kumar wrote:
> Hi,
>
> I have one query like below :
>
>
> SELECT
> m.iMemberId "memberId",
> m.cFirstName "firstName",
> m.cLastName "lastName",
> m.cFirstName || ' ' ||
> m.cLastName "fullName",
> m.cPlayerStateId "stateId",
> DECODE(m.cBirthdateVerify, 1,
> 'Yes', 'No') "birthdateVerify",
> TO_CHAR(m.dBirthDate,
> 'MM/DD/') "dateOfBirth",
> p.cPosition "position",
> p.cJerseyNumber "number",
> DECODE(daps.status, 2, 'PT',
> 1, 'FT', NULL) "daps",
> op.cCitizenship "citizenship",
> op.cNotes "notes",
> NVL(op.cCountryOfBirth,
> op.cCountryOfBirthOther) "countryOfBirth"
> FROM sam_gameroster r
> INNER JOIN sam_guestParticipant p ON
> p.iMemberId = r.iMemberId AND p.iContainerId = r.iContainerId
> INNER JOIN sam_member m ON m.iMemberId
> = p.iMemberId
> INNER JOIN sam_container c ON
> c.iContainerId = r.iContainerId
> LEFT JOIN sam_container lc ON
> c.iContainerLinkId = lc.iContainerId
> LEFT JOIN sam_participant op ON
> op.iassignContainerId = NVL(c.iContainerLinkId, c.iContainerId) AND
> op.imemberID = m.imemberId
> LEFT JOIN (
> SELECT pp.iMemberId,
> MAX(CASE WHEN
> pp.cDpFtStatus = 'PT' THEN 2
> WHEN
> pp.cDpFtStatus = 'FT' THEN 1
> ELSE 0 END) status
> FROM sam_participant pp
> WHERE pp.igroupid =
> getGroupId() GROUP BY pp.iMemberId
> ) daps ON daps.iMemberId = r.iMemberId
> LEFT JOIN sam_playersuspension ps ON
> ps.iMemberId = m.iMemberId AND ps.iEventId = r.iEventId
> WHERE r.iEventId = '7571049' AND
> r.iContainerId = '15257396' AND ((ps.iIsAutoCreated = 1 AND
> ps.iIsTombstone = 1) OR ps.iPlayerSuspensionId IS NULL)
> ORDER BY LOWER(m.cLastName),
> LOWER(m.cFirstName)
>
>
>
>
>
> QUERY PLAN
>
>
> ---
> Sort (cost=718009.89..718009.89 rows=1 width=377) (actual
> time=6730.489..6730.489 rows=0 loops=1)
>Sort Key: (lower((m.clastname)::text)), (lower((m.cfirstname)::text))
>Sort Method: quicksort Memory: 25kB
>Buffers: shared hit=402621
>-> Nested Loop (cost=686998.22..718009.88 rows=1 width=377)
> (actual time=6730.452..6730.452 rows=0 loops=1)
> Join Filter: (r.imemberid = p.imemberid)
> Buffers: shared hit=402618
> -> Nested Loop Left Join (cost=686997.80..718009.40 rows=1
> width=110) (actual time=6038.397..6730.291 rows=25 loops=1)
>Join Filter: (op.iassigncontainerid =
> nvl(c.icontainerlinkid, c.icontainerid))
>Rows Removed by Join Filter: 94
>Buffers: shared hit=402543
>-> Nested Loop (cost=686997.37..718008.53 rows=1
> width=79) (actual time=6038.363..6729.604 rows=25 loops=1)
> Buffers: shared hit=402349
> -> Hash Right Join (cost=686996.94..718000.08
> rows=1 width=67) (actual time=6038.327..6729.331 rows=25 loops=1)
>Hash Cond: (pp.imemberid = r.imemberid)
>Buffers: shared hit=402249
>-> HashAggregate
> (cost=686983.56..700037.48 rows=1305392 width=11) (actual
> time=6026.588..6466.106 rows=996083 loops=1)
> Group Key: pp.imemberid
> Buffers: shared hit=402093
>
>
>
>
> -> Bitmap Heap Scan on
> sam_participant pp (cost=87058.78..663894.09 rows=2308947 width=10)
> (actual time=508.729..4207.342 rows=2335152 loops=1)
>Recheck Cond: (igroupid =
> ((current_setting('env.groupid'::text))::integer)::numeric)
>Heap
