Re: PostgreSQL processes use large amount of private memory on Windows
On 17/09/2020 15:06, Tom Lane wrote: =?UTF-8?Q?=C3=98ystein_Kolsrud?= writes: So my question is: When does a postgres process forked for a connection use private memory instead of shared, and what can I do to avoid this? The only significant long-term consumption of private memory is for caches. There are catalog caches, which can get large if the session accesses a whole lot of database objects (e.g., thousands of different tables). Some of the PLs maintain caches with parsed versions of any function that's been executed. (An ex-employer of mine had a lot of trouble in that regard, because they had hundreds of thousands of lines worth of plpgsql functions.) There isn't any user-accessible knob for limiting the size of those caches. If you have a problem of that sort, about the only way to mitigate it is to use fewer backends so that the total memory consumption stays in bounds, or redesign your application. In some cases it might help to restart your sessions when they get too big, but that seems like at best a band-aid. regards, tom lane Would executing DISCARD ALL release the PL cache? Regards, Chris Sterritt
Column aliases in GROUP BY and HAVING
In my mental model of how SQL works, the clauses of an SQL query (if present) are processed in a certain order: FROM WHERE SELECT GROUP BY HAVING ORDER BY LIMIT and each processes the output of the previous one. However, consider this: hjp=> select * from employees; ╔╤═══╤╗ ║ssn │ name │ other_data ║ ╟┼───┼╢ ║ 1234010400 │ Alice │ (∅)║ ║ 2345180976 │ Bob │ (∅)║ ║ 2645101276 │ Carol │ (∅)║ ║ 9843100395 │ David │ (∅)║ ╚╧═══╧╝ hjp=> select substring(ssn, 9, 2) as year, count(*) from employees group by year; ╔══╤═══╗ ║ year │ count ║ ╟──┼───╢ ║ 95 │ 1 ║ ║ 76 │ 2 ║ ║ 00 │ 1 ║ ╚══╧═══╝ (3 rows) In the GROUP BY clause I can use the alias year which was defined earlier in SELECT. HAVING comes after GROUP BY, so I should be able to use that there, too. Right? hjp=> select substring(ssn, 9, 2) as year, count(*) from employees group by year having year > '20'; ERROR: column "year" does not exist LINE 1: ...ear, count(*) from employees group by year having year > '20... Wrong. I have to use the whole expression again: hjp=> select substring(ssn, 9, 2) as year, count(*) from employees group by year having substring(ssn, 9, 2) > '20'; ╔══╤═══╗ ║ year │ count ║ ╟──┼───╢ ║ 95 │ 1 ║ ║ 76 │ 2 ║ ╚══╧═══╝ (2 rows) This seems inconsistent to me. Is there a technical or semantic reason for this or is just "because the standard says so". hp PS: Please no discussions about the appropriateness of using an SSN as an id. This is a completely made-up example. -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: Column aliases in GROUP BY and HAVING
"Peter J. Holzer" writes: > In my mental model of how SQL works, the clauses of an SQL query (if > present) are processed in a certain order: > FROM > WHERE > SELECT > GROUP BY > HAVING > ORDER BY > LIMIT The SELECT list is certainly done after GROUP BY/HAVING. Consider SELECT 1/x FROM ... GROUP BY x HAVING x > 0; One would be justifiably upset to get a divide-by-zero error from this. Its relationship to ORDER BY is a bit more tenuous, mainly because of the SQL92 legacy notion that you can ORDER BY a select-list column. I'm too lazy to check the code right now, but I think our current policy is that SELECT expressions are evaluated after ORDER BY/LIMIT unless they are used as GROUP BY or ORDER BY keys. Without that, you'd get unhappy performance results from SELECT id, expensive_function(x) FROM ... ORDER BY id LIMIT 1; > In the GROUP BY clause I can use the alias year which was defined > earlier in SELECT. This is a pretty unfortunate legacy thing that we support because backwards compatibility (and because "GROUP BY 1" is so frequently a handy shortcut). Semantically, it's a mess, not only because of the when-to-evaluate confusion but because it's not too clear whether a column name refers to a SELECT output column or to some table column emitted by the FROM clause. We try to limit the potential for that sort of confusion by only trying to match SELECT output names to GROUP/ORDER BY items when the latter are simple identifiers. > HAVING comes after GROUP BY, so I should be able to use that there, too. > Right? No. We're not going there. The core reason why not is that HAVING expressions are seldom plain column names, so it wouldn't work anyway unless we opened the floodgates on where SELECT output names could be matched. > This seems inconsistent to me. Is there a technical or semantic reason > for this or is just "because the standard says so". I think SQL versions newer than SQL92 disallow these references entirely. (I'm being fairly brief here, but this has all been litigated multiple times before. See the archives.) regards, tom lane
Re: Any interest in adding match_recognize?
On Fri, 2020-10-09 at 09:25 -0700, Guyren Howe wrote: > I can find no evidence it’s ever been discussed here and there’s no mention > of it on the PG website. > > So: is anyone considering adding this feature? I think it would be useful, but non-trivial to implement. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
Re: Column aliases in GROUP BY and HAVING
On 2020-10-12 10:40:03 -0400, Tom Lane wrote: > "Peter J. Holzer" writes: > > In the GROUP BY clause I can use the alias year which was defined > > earlier in SELECT. > > This is a pretty unfortunate legacy thing that we support because > backwards compatibility (and because "GROUP BY 1" is so frequently > a handy shortcut). [...] > I think SQL versions newer than SQL92 disallow these references entirely. > > (I'm being fairly brief here, but this has all been litigated multiple > times before. See the archives.) That's ok, you've answered my question. Thanks. hp -- _ | Peter J. Holzer| Story must make more sense than reality. |_|_) || | | | h...@hjp.at |-- Charles Stross, "Creative writing __/ | http://www.hjp.at/ | challenge!" signature.asc Description: PGP signature
Re: rum index supported on pg13?
No, instead i posted the compile errors. I am still new to github protocol, so i was not sure if asking about longer term support of rum was appropriate for the github issues posting. the differences between pg12 and pg13 seem considerable. our internal use of rum has been spectacularly successful, we may be able to justify resources to fixing the compile issues with pg13, but the effort will be considerable. -john On Sun, Oct 11, 2020 at 7:16 PM Michael Paquier wrote: > > On Thu, Oct 08, 2020 at 09:29:31PM -0500, John the Scott wrote: > > will rum index from postgrespro be supported in pg13? > > numerous errors occur when compiling rum in pg13 and > > no replies from github. the differences from pg12 > > to pg13 seem to be significant > > > > https://github.com/postgrespro/rum > > Did you ask directly this question to the authors of the extension on > the page of the project you are quoting above? > -- > Michael -- Fast is fine, But accuracy is final. You must learn to be slow in a hurry. - Wyatt Earp
Re: rum index supported on pg13?
On Mon, Oct 12, 2020 at 12:17:04PM -0500, John the Scott wrote: > I am still new to github protocol, so i was not sure > if asking about longer term support of rum was appropriate for > the github issues posting. Most of the original developers of rum are registered on this mailing list so there would be some visibility, but I would guess that posting an issue or a question from the actual project page does not hurt either. > the differences between pg12 and pg13 seem considerable. > our internal use of rum has been spectacularly successful, > we may be able to justify resources to fixing the compile issues with pg13, > but the effort will be considerable. Glad to hear that. -- Michael signature.asc Description: PGP signature
Re: What's your experience with using Postgres in IoT-contexts?
> I want to have long term storage and access to individual telegrams An IOT is not designed for that. It is used for control or delivery of data to a server. You could have a PostgreSQL-client in the IOT but an MQ might be better. Long term storage also means backup and recovery and I don't think you have that planned for your IOT. ./hans
