Re: How to debug a connection that's "active" but hanging?

Jurrie Overgoor writes: > Then I tried this. The query plans are indeed not consistent. > Most of the time the first line of the query plan is: Unique  > (cost=4892.35..4892.35 rows=1 width=64) [1] > I have seen other costs: 5818.30, 6350.85 and 6514.73. They all complete > correctly. [2],

Re: How to debug a connection that's "active" but hanging?

On 12-07-2021 20:56, Vijaykumar Jain wrote: On Mon, 12 Jul 2021 at 23:16, Tom Lane > wrote: The backtraces you captured look like the query is not "hung", it's just computing away. He mentioned earlier that the query was hung as 'active' for 8 hours and

Re: Why can't I drop a tablespace?

Laurenz Albe wrote: 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

Re: How to debug a connection that's "active" but hanging?

On Mon, 12 Jul 2021 at 23:16, Tom Lane wrote: > > The backtraces you captured look like the query is not "hung", it's > just computing away. > > He mentioned earlier that the query was hung as 'active' for 8 hours and on. incase this is due to bad plan, @Jurrie Overgoor is it also possible

Re: Use of '&' as table prefix in query [RESOLVED]

On Mon, 12 Jul 2021, Rich Shepard wrote: Long ago I wrote a query which was greatly improved (i.e., it actually worked as intended) by help here): Ah! It finally came back to me as I looked to revise it. What I want both and are specific names passed in from the user. Now I know what they

Re: Use of '&' as table prefix in query

On Mon, 12 Jul 2021, Rob Sargent wrote: Having a hard time seeing the value in p.lname = p.lname and straight sql. Me, too. That's why I wanted an explanation. Seems to me I added it sometime for no valid reason. As I've not run that query in a very long time I'll clean up the query and test

Re: Use of '&' as table prefix in query

On Mon, 12 Jul 2021, Adrian Klaver wrote: You should also follow Tom's suggestion and do: \do+ & in psql. It's the bitwise 'and': # \do+ & List of operators Schema | Name | Left arg type | Right arg type | Result type | Function | Description

Re: Use of '&' as table prefix in query

On 7/12/21 11:47 AM, Rich Shepard wrote: On Mon, 12 Jul 2021, Rob Sargent wrote: These look like value substitutions, usually done on the client at it sends the sql. How is this sql getting to the server (presumably after substitution). Rob, I was running queries from the psql back then.

Re: Use of '&' as table prefix in query

On 7/12/21 10:45 AM, Rich Shepard wrote: On Mon, 12 Jul 2021, Tom Lane wrote: AFAICS this is invoking a prefix operator named "&". There is no such operator built into Postgres. Maybe psql's "\do+ &" would jog your memory about where yours came from. tom, I thought it wasn't part of

Re: Use of '&' as table prefix in query

On 7/12/21 10:45 AM, Rich Shepard wrote: On Mon, 12 Jul 2021, Tom Lane wrote: AFAICS this is invoking a prefix operator named "&". There is no such operator built into Postgres. Maybe psql's "\do+ &" would jog your memory about where yours came from. tom, I thought it wasn't part of

Re: Use of '&' as table prefix in query

On Mon, 12 Jul 2021, Rob Sargent wrote: These look like value substitutions, usually done on the client at it sends the sql. How is this sql getting to the server (presumably after substitution). Rob, I was running queries from the psql back then. Now I'm adding a GUI (tkinter) and using

Re: How to debug a connection that's "active" but hanging?

Jurrie Overgoor writes: > Hi Vijaykumar, thanks for replying. Yes, this is reproducible. About 50% > of the times, my connection is hanging. It's always on the same query, > which I shared in the previous post. The backtraces you captured look like the query is not "hung", it's just computing

Re: Use of '&' as table prefix in query

On Mon, 12 Jul 2021, Tom Lane wrote: AFAICS this is invoking a prefix operator named "&". There is no such operator built into Postgres. Maybe psql's "\do+ &" would jog your memory about where yours came from. tom, I thought it wasn't part of postgres. I've no idea why it's there, but I'll

Re: Use of '&' as table prefix in query

On 7/12/21 11:25 AM, Rich Shepard wrote: Long ago I wrote a query which was greatly improved (i.e., it actually worked as intended) by help here): /* This query selects all activity information for a named person */ SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone, p.active,   

Re: How to debug a connection that's "active" but hanging?

On 10-07-2021 10:26, Vijaykumar Jain wrote: On Sat, 10 Jul 2021 at 00:29, Jurrie Overgoor > wrote: Hi everyone, We are in the process of upgrading from PostgreSQL 9.6 to 13. When our database gets created in our regression tests, we run

Re: Use of '&' as table prefix in query

Rich Shepard writes: > Long ago I wrote a query which was greatly improved (i.e., it actually > worked as intended) by help here): > SELECT ... > WHERE p.lname = AND p.fname = > I did not save the reason why the ampersand is used in the WHERE row selection > phrase and want now to learn why

Use of '&' as table prefix in query

Long ago I wrote a query which was greatly improved (i.e., it actually worked as intended) by help here): /* This query selects all activity information for a named person */ SELECT p.lname, p.fname, p.loc_nbr, p.job_title, p.direct_phone, p.active, o.org_name, l.loc_nbr,

Re: Undocumented array_val[generate_series(...)] functionality?

"David G. Johnston" writes: > I agree the material in Extending SQL - SQL Functions, plus normal > expectations, mean that we do indeed cover the topic sufficiently. The > location and specifics of the material and use of cross-references could > use some attention though its also not an area

Re: Why can't I drop a tablespace?

"Phil Endecott" writes: > Thanks Laurenz. I was looking at the source for "alter table set > tablespace" yesterday trying to work out what is supposed to happen. > There is a comment at tablecmds.c line 3989: "Thanks to the magic of > MVCC, an error anywhere along the way rolls back the whole

RE: 3867653, EMM1 cluster issue on 23rd May--core generated --design feedback

Hi All, We are getting following core with following use case: Case: We made max_connections as 15 and 10 different processes opening 15 connections with server. It is running fine. But after few hours it giving following core. When we increase the max_connections then core come

Re: Undocumented array_val[generate_series(...)] functionality?

On Monday, July 12, 2021, Tom Lane wrote: > Still, I'm with David that no new docs > are needed. IMO the former restriction was the surprising thing, and > the current behavior is simply what one would expect from assembling > those parts in that order. > > I agree the material in Extending SQL

Re: Undocumented array_val[generate_series(...)] functionality?

Dean Rasheed writes: > BTW, this is something that started working in PG10 (in 9.6, an error > is thrown), and I think that it's a result of this release note item, > which matches your conclusions: > Change the implementation of set-returning functions appearing in > a query's SELECT list

Re: Removing a subscription that does not exist

On 7/11/21 7:38 PM, Kyotaro Horiguchi wrote: 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. ... Look at the subdbid field in the first query result. You were logging into the

Re: pg_upgrade as a way of cloning an instance?

On Thu, Jul 8, 2021 at 2:30 AM Bruce Momjian wrote: > Also, pg_upgrade will throw an error if copying from one version to the > same version if there are tablespaces since the tablespace directory > name is the catalog version name. A very good point to discourage this usage! Thanks, Luca

Re: Undocumented array_val[generate_series(...)] functionality?

On Mon, 12 Jul 2021 at 02:39, David G. Johnston wrote: > > One, the select generate_series(1,3) function call causes multiple rows to be > generated where there would usually be only one. Yes. > Two, composition results in an inside-to-outside execution order: the SRF is > evaluated first,