Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
On Tue, Apr 1, 2025 at 9:28 AM David G. Johnston wrote: > On Mon, Mar 31, 2025 at 8:39 PM Jayadevan M > wrote: > >> Hello PG members, >> I used 'IST' in a query like this - * (timestamp_hour) at time zone >> 'IST' time_ist *and did not get the expected output - timestamp in >> Indian Standard Time. So I queried the 2 views that provide timezone info >> and did not really understand the abbrev column. >> select name, abbrev, utc_offset from pg_timezone_names where abbrev = >> 'IST' ; >> > > Since the S and T are non-location specific you get 26 different timezone > abbreviations to choose from. That wasn't enough for the world. So IST is > non-unique; and for historical reasons Ireland (Eire, which contains > Dublin) is given default priority. > > >> name | abbrev | utc_offset >> ---++ >> Eire | IST| 01:00:00 >> Asia/Kolkata | IST| 05:30:00 >> Asia/Calcutta | IST| 05:30:00 >> Europe/Dublin | IST| 01:00:00 >> > > Suggest you adapt to using ISO names (the name column above) for > timezones; which are long enough and location-specific enough to be > unique. In your case, pick your preferred spelling of Calcutta I suppose. > > Thank you. I used Calcutta. Regards, Jayadevan
Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
On Mon, Mar 31, 2025 at 8:39 PM Jayadevan M wrote: > Hello PG members, > I used 'IST' in a query like this - * (timestamp_hour) at time zone > 'IST' time_ist *and did not get the expected output - timestamp in Indian > Standard Time. So I queried the 2 views that provide timezone info and did > not really understand the abbrev column. > select name, abbrev, utc_offset from pg_timezone_names where abbrev = > 'IST' ; > Since the S and T are non-location specific you get 26 different timezone abbreviations to choose from. That wasn't enough for the world. So IST is non-unique; and for historical reasons Ireland (Eire, which contains Dublin) is given default priority. > name | abbrev | utc_offset > ---++ > Eire | IST| 01:00:00 > Asia/Kolkata | IST| 05:30:00 > Asia/Calcutta | IST| 05:30:00 > Europe/Dublin | IST| 01:00:00 > Suggest you adapt to using ISO names (the name column above) for timezones; which are long enough and location-specific enough to be unique. In your case, pick your preferred spelling of Calcutta I suppose. There is a way to get a different interpretation for IST to be recognized but I'd have to find it or wait for others to chime in. David J.
Re: Doubt on pg_timezone_names and pg_timezone_abbrevs
Jayadevan M writes: > I used 'IST' in a query like this - * (timestamp_hour) at time zone 'IST' > time_ist *and did not get the expected output - timestamp in Indian > Standard Time. I think IST defaults to 'Israel Standard Time', a/k/a Asia/Jerusalem, a/k/a UTC+2. To get it to mean Indian Standard Time a/k/a Asia/Calcutta you need set timezone_abbreviations TO 'India'; (or more likely, adjust that in your installation's postgresql.conf). See https://www.postgresql.org/docs/current/datetime-config-files.html regards, tom lane
Doubt on pg_timezone_names and pg_timezone_abbrevs
Hello PG members, I used 'IST' in a query like this - * (timestamp_hour) at time zone 'IST' time_ist *and did not get the expected output - timestamp in Indian Standard Time. So I queried the 2 views that provide timezone info and did not really understand the abbrev column. select name, abbrev, utc_offset from pg_timezone_names where abbrev = 'IST' ; name | abbrev | utc_offset ---++ Eire | IST| 01:00:00 Asia/Kolkata | IST| 05:30:00 Asia/Calcutta | IST| 05:30:00 Europe/Dublin | IST| 01:00:00 select * from pg_timezone_abbrevs where abbrev = 'IST' ; abbrev | utc_offset | is_dst ++ IST| 02:00:00 | f This is PostgreSQL 13.15 on AWS RDS. We have the same abbrev for 4 timezones in pg_timezone_names. Regards, Jayadevan
Re: SQL Server's WITH (NOLOCK) equivalent in PostgreSQL?
On Sun, 2025-03-30 at 22:10 -0700, David G. Johnston wrote: > On Sunday, March 30, 2025, 이현진 wrote: > > Since PostgreSQL uses MVCC, I'm wondering what the best practice is for > > non-blocking reads, > > and whether there's an equivalent to dirty reads or READ UNCOMMITTED. > > https://www.postgresql.org/docs/current/transaction-iso.html > > We are unable to implement read uncommitted because of our choice to > implement MVCC. Perhaps it would be better to say "there is no need to implement a READ UNCOMMITTED isolation level that actually allows for dirty reads". On databases like SQL Server you don't use READ UNCOMMITTED because you desperately want to see dirty, inconsistent data. It is just something you have to accept in order to avoid read locks. Since PostgreSQL doesn't use read locks, there is no need for that. Yours, Laurenz Albe
RDS IO Read time
Hi all, Hopefully, someone here could help us understand whats going on with our deployment.. We are running Postgres 17.4 on AWS RDS on an ec2 instance that has: * 32vCPU * 128GB RAM * gp3 with 25K IOPS and 4000MiB/s throughput * the instance supports up to 3125GB/s of throughput. Whenever a query fetches data from the disk (index scan, bitmap scans, etc.) we’re reaching 23 – 30MB/s of IO Read time. We changed our autovacuum settings to be more aggressive (0 scale factor and 10K threshold) so our index only scans will (hopefully) read data from the disk rarely. However, we can’t optimize all the queries to use index-only-scan since our users can dynamically select which columns to see and filtering. Is this expected IO read rate? I can’t help but feel we’re missing something here.. Thanks a lot! 😊
Re: Querying one partition in a function takes locks on all partitions
On Mon, 2025-03-31 at 18:41 +0200, Renan Alves Fonseca wrote: > I would add another Note below like: > "Except when inlined, an SQL function is always executed with a > generic plan. This behavior may not be desired in some situations, and > it will be fixed in future versions." But that is not true, as far as I can tell: just like any other prepared statement, it may keep using custom plans. Yours, Laurenz Albe
Re: Querying one partition in a function takes locks on all partitions
On Mon, Mar 31, 2025 at 9:42 AM Renan Alves Fonseca wrote: > I'm not sure if we should mention the fix or if we should mention a > workaround... > Workarounds are ok but my observation is that "this may change in the future" comments are pointless and should be stricken from the manual because people are bad at predicting the future. Just document what is and don't string people along. We never go back to old releases and say "This was fixed in v17." David J.
Re: Querying one partition in a function takes locks on all partitions
Laurenz Albe writes: > On Mon, 2025-03-31 at 18:41 +0200, Renan Alves Fonseca wrote: >> I would add another Note below like: >> "Except when inlined, an SQL function is always executed with a >> generic plan. This behavior may not be desired in some situations, and >> it will be fixed in future versions." > But that is not true, as far as I can tell: just like any other prepared > statement, it may keep using custom plans. No, it is a generic plan in the current code. See functions.c's init_execution_state, which calls pg_plan_query without passing any boundParams. That's kind of necessary in the current scheme of things, because the SQL function holds onto the plan for the duration of the current query and re-uses the plan during subsequent calls with (possibly) different parameter values. So it's sort of a poor man's plan cache, but implemented in about the stupidest way possible. Jacking that up and rolling the plancache.c infrastructure underneath has been on my to-do list for years. regards, tom lane
Re: RDS IO Read time
On 3/31/25 06:54, Eden Aharoni wrote: Hi all, Hopefully, someone here could help us understand whats going on with our deployment.. We are running Postgres 17.4 on AWS RDS on an ec2 instance that has: * 32vCPU * 128GB RAM * gp3 with 25K IOPS and 4000MiB/s throughput * the instance supports up to 3125GB/s of throughput. Whenever a query fetches data from the disk (index scan, bitmap scans, etc.) we’re reaching 23 – 30MB/s of IO Read time. We changed our autovacuum settings to be more aggressive (0 scale factor and 10K threshold) so our index only scans will (hopefully) read data from the disk rarely. However, we can’t optimize all the queries to use index-only-scan since our users can dynamically select which columns to see and filtering. Is this expected IO read rate? I can’t help but feel we’re missing something here.. RDS is a black box controlled by AWS, you are going to need to reach out to their tech support. Thanks a lot! 😊 -- Adrian Klaver adrian.kla...@aklaver.com
Re: Querying one partition in a function takes locks on all partitions
On Mon, Mar 31, 2025 at 5:10 AM David Rowley wrote: > > On Sat, 29 Mar 2025 at 10:30, Renan Alves Fonseca > wrote: > > Currently, in the SQL function path the plan is always generic. The > > planner ignores the function arguments. The plan_cache_mode setting > > has no effect in this path. > > > > I agree that the docs should be more explicit about this. There is a > > high penalty for using generic plans in complex functions. > > If you have any suggestions about where you think those should be > added or wording for that, please feel free to suggest. > There is a specific chapter about functions written in SQL: [1]. It is in an advanced section of the docs, so I think it is a suitable place to address this level of detail. There is a Note that says: "The entire body of an SQL function is parsed before any of it is executed. While an SQL function can contain commands that alter ..." I would add another Note below like: "Except when inlined, an SQL function is always executed with a generic plan. This behavior may not be desired in some situations, and it will be fixed in future versions." I'm not sure if we should mention the fix or if we should mention a workaround... If I understood well [2], then both notes may be discarded together in the next version. Renan [1] https://www.postgresql.org/docs/17/xfunc-sql.html [2] https://www.postgresql.org/message-id/db42573039cc66815e80a48589eebea8%40postgrespro.ru
Re: [EXTERNAL] RDS IO Read time
> On Mar 31, 2025, at 10:32, Eden Aharoni wrote: > > First, thanks for the reply :) > So, I do know which part is taking a lot of IO time and it's to be honest any > node that reads from the disk.. of course, we're running EXPLAIN on our > queries (to be more specific we use auto_explain) but we can't seem to find > what could cause an index scan that reads 34 MB to take more than a second > (1.2 sec).. we do know that our dataset doesn't fit the memory and we were ok > with that but with IO being so slow we just don't know what to do anymore. > Any other suggestions other than contact AWS (which we did but it seems this > path won't lead anywhere). The performance of EBS is definitely a question for AWS. I can say that, in our experience, EBS mounts for RDS almost never approach the stated maximum throughput, although io2 tends to be closer than gp2 or gp3.
Re: RDS IO Read time
> On Mar 31, 2025, at 06:54, Eden Aharoni wrote: > Is this expected IO read rate? I can’t help but feel we’re missing something > here.. Really, no particular I/O rate is "expected": if PostgreSQL needs that much data, it'll use that much I/O to get it. From your description, it's likely that it's a case of the working set for the database just not fitting into the memory you have, so PostgreSQL needs to go out to secondary storage a lot to fetch the data. The best first step is to use Performance Insights to see which queries are using I/O, and run sample ones with EXPLAIN (ANALYZE, BUFFERS) to see where the I/O is being used within the query. Given that you allow users to assemble arbitrary queries, it's likely that PostgreSQL is having to use a wide variety of indexes (or sequential scans), so it can't successfully cache a particular set in memory.
Re: [EXTERNAL] RDS IO Read time
> On Mar 31, 2025, at 10:54, Eden Aharoni wrote: > > So you believe it's strictly an EBS issue? Well, PostgreSQL certainly can read faster than 34MB/s off of disk. With the data you've given, I can't really say if it's purely an EBS issue.
