Re: LibPQ: PQresultMemorySize as proxy to transfered bytes
On Wed, Jun 28, 2023 at 1:22 PM Tom Lane wrote: > I wrote: > > That number is the total space actually requested from malloc() for > > the PGresult object. But we request space in blocks (typically 2KB > > each), so there's some overhead due to fields not exactly filling > > a block, unused space in the last block, etc. If you're testing > > with very small result sets (say a couple hundred KB) > > Sigh, obviously I meant a couple hundred *bytes* there. -ENOCAFFEINE > Thanks Tom. --DD PS: I was hoping for answers to my other questions too, but I guess that means there are no good answers to those.
Topological sort of tables, based on FK relationships
Assuming a particular schema is standalone, i.e. does not depend on any other external schema, I'd like to know the order in which to (re)populate tables from data coming from (an existing) custom storage. When the schema's table have foreign key relationships, "parent" tables must be populated before children tables referencing the parent ones. I've looked at pg_depend, and there doesn't seem to be any "direct relationships" between parent and child tables, i.e. I found no rows with the parent and child tables as refobjid and objid of the same row. One must apparently go through a pg_constraint dependency first. But even then, I'm guessing I need a CTE to do the topological sort. I've done topological sorts in C++, but not in a functional language like SQL. Would anyone happen to have a query to returns that order for a schema? A complication is that sometimes there are circular dependencies between tables, which are "solved" by deferring one constraint to "break the cycle". Would the above query handle that? I guess any tool that restores a "backup" has the same problem, no? Or are those tools somehow bypassing that issue? Perhaps by disabling constraints when reloading the data, then re-enabling them? Thanks for any insights. --DD
Re: need explanation about an explain plan
Le mer. 28 juin 2023 à 22:46, Laurenz Albe a écrit : > On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote: > > Hi Laurenz, as said, in each partition there is only one value for > ladate. > > The planner doesn't seem to take that into account. > Indeed. I did check values in pg_statistic. And rerun analyze. No change... > > Yours, > Laurenz Albe >
analyze partition
Hi, the documentation, on chapter 5.11.3.3 caveat says that a manual vacuum or analyze on the root table does it only for that said root table. To my understanding, the root table when used with declarative partitioning, does not contain data, so vacuuming or analyzing should be 'fast'. If I run vacuum analyze ma_table on my big partitioned table (130+ partitions) it does work for quite a long time. Can someone clarify ? thanks Marc MILLAS Senior Architect +33607850334 www.mokadb.com
Re: analyze partition
On Thu, Jun 29, 2023 at 7:55 PM Marc Millas wrote: > > Hi, > > the documentation, on chapter 5.11.3.3 caveat says that a manual vacuum or > analyze on the root table does it only for that said root table. To my > understanding, the root table when used with declarative partitioning, does > not contain data, so vacuuming or analyzing should be 'fast'. > If I run vacuum analyze ma_table on my big partitioned table (130+ > partitions) it does work for quite a long time. > > Can someone clarify ? > > thanks > > > Marc MILLAS > Senior Architect > +33607850334 > www.mokadb.com > per manual: > 5.11.3.3. Caveats > The following caveats apply to partitioning implemented using inheritance: . > If you are using manual VACUUM or ANALYZE commands, don't forget that you > need to run them on each child table individually. A command like: > > ANALYZE measurement; > > will only process the root table. declarative partitioning is not the same. Here the caveats refers to partitioning implemented using inheritance. These two are different things.
Re: need explanation about an explain plan
Hi, @Marc, I think there is no problem.Even though it says it is filtered by ladate, it is not. Because of the partition. As you can see for each index scan it uses a different partition and those partition boundaries are already specified logically. For example; "Parallel Index Scan using table1_p_201802_numfic_idx on table1_p_201802 t_3". If the names correctly matches the partition concept, the partition table1_p_201802 only contains values for between 2018.02.01 and 2018.03.01. So, even though there is a filter, there is not. Thus, filtering only occurs for your numfic column. The following link might help to understand how it does reading and skipping. In your case, it does not do any harm, but maybe it would be better change how it looks on the execution plan to prevent confusion. https://www.postgresql.org/docs/current/indexes-multicolumn.html#:~:text=For%20example%2C%20given,be%20scanned%20through . Thanks! On Thu, Jun 29, 2023 at 12:08 PM Marc Millas wrote: > > > Le mer. 28 juin 2023 à 22:46, Laurenz Albe a > écrit : > >> On Wed, 2023-06-28 at 19:25 +0200, Marc Millas wrote: >> > Hi Laurenz, as said, in each partition there is only one value for >> ladate. >> >> The planner doesn't seem to take that into account. >> > Indeed. I did check values in pg_statistic. And rerun analyze. No > change... > >> >> Yours, >> Laurenz Albe >> >
psql and pgpass.conf on Windows
Hi, Trying to write a script that will run on Linux, Windows, and Mac. The "standard" credentials file contains: hostname:port:database:username:password in: Linux: .pgpass Windows: %APPDATA%\postgresql\pgpass.conf Mac: (I'm not there yet...) On Linux, this works. However, on Windows, psql will not read pgpass.conf (tried in just about every location I could think of) Even: "set PGPASSFILE=" does not work. Finally, out of frustration, tried: set PGPASSWORD= and that got me past the password issue, only to now get: 'more' is not recognized as an internal or external command, operable program or batch file. Given the number of queries about pgpass.conf and finding no answer that works, is there no bug report on this? Thinking that psql was not adjusted for Windows, tried naming the file: .pgpass .pgpass.conf also in various locations to no avail... What am I (and all the others found in searches) missing? Or are there unresolved bugs in psql? - pgpass.conf - expecting external executable: 'more' Thanks, Pierre
Re: psql and pgpass.conf on Windows
Hi, On Thu, Jun 29, 2023 at 10:42:00PM -0400, p...@pfortin.com wrote: > > Windows: %APPDATA%\postgresql\pgpass.conf > > On Linux, this works. However, on Windows, psql will not read > pgpass.conf (tried in just about every location I could think of) > > Even: "set PGPASSFILE=" does not work. > > Finally, out of frustration, tried: > set PGPASSWORD= > and that got me past the password issue, only to now get: > 'more' is not recognized as an internal or external command, > operable program or batch file. > > Given the number of queries about pgpass.conf and finding no answer that > works, is there no bug report on this? > > Thinking that psql was not adjusted for Windows, tried naming the file: > .pgpass > .pgpass.conf > also in various locations to no avail... > What am I (and all the others found in searches) missing? Or are there > unresolved bugs in psql? >- pgpass.conf >- expecting external executable: 'more' The file should indeed be by default %APPDATA%/postgresql/pgpass.conf, and it's known to be functional on Windows. The fact that you hit some error with a "more" program makes me think that your script setup some environment variables (like PAGER=more, which would explain why you hit that error) that maybe interfere with file location and/or name. Now, since setting PGPASSFILE also doesn't work I start to wonder if there's another problem. Does the password (or any other field) contain some non-ASCII characters? There could be an encoding issue in the file, or maybe the problem is with the presence or absence of a BOM in the file. Another thing you should try just in case is to replace backwards slashes with forward slashes. If none of that work, you could also check what file psql is trying to open using the equivalent of "strace" for Windows, if such a thing exists.
Re: psql and pgpass.conf on Windows
On Thu, Jun 29, 2023 at 7:42 PM wrote: > Trying to write a script that will run on Linux, Windows, and Mac. > This seems impossible on its face unless you use WSL within the Windows environment. And if you are doing that, then the pathing would be WSL pathing, not native Windows. David J.
