Created feature for to_date() conversion using patterns 'YYYY-WW', 'YYYY-WW-D', 'YYYY-MM-W' and 'YYYY-MM-W-D'
Hi,
some days ago I ran into a problem with the to_date() function. I
originally described it on StackExchange:
https://dba.stackexchange.com/questions/250111/unexpected-behaviour-for-to-date-with-week-number-and-week-day
The problem:
If you want to parse a date string with year, week and day of week, you
can do this using the ISO week pattern: 'IYYY-IW-ID'. This works as
expected:
date string | to_date()
+
'2019-1-1' | 2018-12-31 -> Monday of the first week of the year
(defined as the week that includes the 4th of January)
'2019-1-2' | 2019-01-01
'2019-1-3' | 2019-01-02
'2019-1-4' | 2019-01-03
'2019-1-5' | 2019-01-04
'2019-1-6' | 2019-01-05
'2019-1-7' | 2019-01-06
'2019-2-1' | 2019-01-07
'2019-2-2' | 2019-01-08
But if you are trying this with the non-ISO pattern '-WW-D', the
result was not expected:
date string | to_date()
-
'2019-1-1' | 2019-01-01
'2019-1-2' | 2019-01-01
'2019-1-3' | 2019-01-01
'2019-1-4' | 2019-01-01
'2019-1-5' | 2019-01-01
'2019-1-6' | 2019-01-01
'2019-1-7' | 2019-01-01
'2019-2-1' | 2019-01-08
'2019-2-2' | 2019-01-08
As you can see, the 'D' part of the pattern doesn't influence the
resulting date.
The answer of Laurenz Albe pointed to a part of the documentation, I
missed so far:
"In to_timestamp and to_date, weekday names or numbers (DAY, D, and
related field types) are accepted but are ignored for purposes of
computing the result. The same is true for quarter (Q) fields."
(https://www.postgresql.org/docs/12/functions-formatting.html)
So, I had a look at the relevant code part. I decided to try a patch by
myself. Now it works as I would expect it:
date string | to_date()
-
'2019-1-1' | 2018-12-30 -> Sunday (!) of the first week of the year
(the first week is at the first day of year)
'2019-1-2' | 2018-12-31
'2019-1-3' | 2019-01-01
'2019-1-4' | 2019-01-02
'2019-1-5' | 2019-01-03
'2019-1-6' | 2019-01-04
'2019-1-7' | 2019-01-05
'2019-2-1' | 2019-01-06
'2019-2-2' | 2019-01-07
Furthermore, if you left the 'D' part, the date would be always set to
the first day of the corresponding week (in that case it is Sunday, in
contrast to the ISO week, which starts mondays).
To be consistent, I added similar code for the week of month pattern
('W'). So, using the pattern '-MM-W-D' yields in:
date string | to_date()
---
'2018-12-5-1' | 2018-12-23
'2018-12-6-1' | 2018-12-30
'2019-1-1-1' | 2018-12-30 -> First day (Su) of the first week of the
first month of the year
'2019-2-2-1' | 2019-02-03 -> First day (Su) of the second week of
February
'2019-10-3-5' | 2019-10-17 -> Fifth day (Th) of the third week of
October
If you left the 'D', it would be set to 1 as well.
The code can be seen here:
https://github.com/S-Man42/postgres/commit/534e6bd70e23864f385d60ecf187496c7f4387c9
I hope, keeping the code style of the surrounding code (especially the
ISO code) is ok for you.
Now the questions:
1. Although the ignorance of the 'D' pattern is well documented, does
the new behaviour might be interesting for you?
2. Does it work as you'd expect it?
3. Because this could be my very first contribution to the PostgreSQL
code base, I really want you to be as critical as possible. I am not
quite sure if I didn't miss something important.
4. Currently something like '2019-1-8' does not throw an exception but
results in the same as '2019-2-1' (8th is the same as the 1st of the
next week). On the other hand, currently, the ISO week conversion gives
out the result of '2019-1-7' for every 'D' >= 7. I am not sure if this
is better. I think a consistent exception handling should be discussed
separately (date roll over vs. out of range exception vs. ISO week
behaviour)
So far, I am very curious about your opinions!
Kind regards,
Mark/S-Man42
RE: Where can I find the doxyfile?
Sometime earlier, I created a filter to annotate regular C comments as doxy comments. I'll attach it along with a sample doxyfile for running it. Just in case it looks useful. I've never been a big fan of Doxygen, but it seems to have gotten better over time. Now that some IDEs display doxy comments on hover, I'm beginning to appreciate it. DoxygenFilter.l Description: Binary data Doxyfile Description: Binary data
Re: [Ext:] Re: Stream Replication not working
> > pid | 1979089 > > usesysid | 16404 > > usename | replacct > > application_name | walreceiver > > client_addr | > > client_hostname | > > client_port | 55096 > > backend_start| 2022-01-06 17:29:51.542784-07 > > backend_xmin | > > state| streaming > > sent_lsn | 0/35000788 > > write_lsn| 0/35000788 > > flush_lsn| 0/35000788 > > replay_lsn | 0/31000500 > > write_lag| 00:00:00.001611 > > flush_lag| 00:00:00.001693 > > replay_lag | 20:38:47.00904 > > sync_priority| 1 > > sync_state | sync > > reply_time | 2022-01-07 14:11:58.996277-07 > > > > postgresql=# > > > > postgresql=# select * from pg_roles; > > rolname | rolsuper | rolinherit | rolcreaterole | > rolcreatedb | rolcanlogin | rolreplication | rolconnlimit | rolpassword | > rolvaliduntil | rolbypassrls | rolconfig | oid > > > ---+--++---+-+-++--+-+---+--+---+--- > > postgresql| t| t | t | t > | t | t | -1 | | > | t| |10 > > pg_monitor| f| t | f | f > | f | f | -1 | | > | f| | 3373 > > pg_read_all_settings | f| t | f | f > | f | f | -1 | | > | f| | 3374 > > pg_read_all_stats | f| t | f | f > | f | f | -1 | | > | f| | 3375 > > pg_stat_scan_tables | f| t | f | f > | f | f | -1 | | > | f| | 3377 > > pg_read_server_files | f| t | f | f > | f | f | -1 | | > | f| | 4569 > > pg_write_server_files | f| t | f | f > | f | f | -1 | | > | f| | 4570 > > pg_execute_server_program | f| t | f | f > | f | f | -1 | | > | f| | 4571 > > pg_signal_backend | f| t | f | f > | f | f | -1 | | > | f| | 4200 > > replacct | t| t | t | t > | t | t | -1 | | > | t| | 16404 > > (10 rows) > > > > postgresql=# > > > > postgresql=# create database test_replication_3; > > CREATE DATABASE > > postgresql=# > > > > postgresql=# select datname from pg_database; > > datname > > > > postgres > > postgresql > > template1 > > template0 > > stream > > test_replication > > test_replication_2 > > test_replication_3 > > (8 rows) > > > > postgresql=# > > > > postgresql=# SELECT pg_current_wal_lsn(); > > pg_current_wal_lsn > > > > 0/35000788 > > (1 row) > > > > postgresql=# > > > > > > *Standby **postgresql Environment* > > postgresql=# select * from pg_stat_wal_receiver; > > -[ RECORD 1 > ]-+--- > > pid | 17340 > > status| streaming > > receive_start_lsn | 0/3000 > > receive_start_tli | 1 > > written_lsn | 0/35000788 > > flushed_lsn | 0/35000788 > > received_tli | 1 > > last_msg_send_time| 2022-01-07 14:09:48.766823-07 > > last_msg_receipt_time | 2022-01-07 14:09:48.767581-07 > > latest_end_lsn| 0/35000788 > > latest_end_time | 2022-01-07 14:08:48.663693-07 > > slot_name | wal_req_x_replica > > sender_host | > > sender_port | > > conninfo | user=replacct password= > channel_binding=prefer dbname=replication host= > port= fallback_application_name=walreceiver > sslmode=prefer sslcompression=0 ssl_min_protocol_version=TLSv1.2 > gssencmode=prefer krbsrvname=postgres target_session_attrs=any > > > > postgresql=# > > > > postgresql=# select datname from pg_database; > > datname > > > > postgres > > postgresql > > template1 > > template0 > > stream > > (5 rows) > > > > postgresql=# select pg_last_wal_receive_lsn(); > > pg_last_wal_receive_lsn > > - > > 0/35000788 > > (1 row) > > > > postgresql=# > > > > > >
回复: WAL Insertion Lock Improvements
Hi Andres Freund This patch improves performance significantly,Commitfest 2023-03 is coming to an end,Is it not submitted yet since the patch still needs to be improved? Best wish 发件人: Nathan Bossart 发送时间: 2023年2月21日 13:49 收件人: Bharath Rupireddy 抄送: Andres Freund ; PostgreSQL Hackers 主题: Re: WAL Insertion Lock Improvements On Thu, Feb 09, 2023 at 11:51:28AM +0530, Bharath Rupireddy wrote: > On Thu, Feb 9, 2023 at 3:36 AM Nathan Bossart > wrote: >> Overall, I think this patch is in reasonable shape. > > Thanks for reviewing. Please see the attached v5 patch. I'm marking this as ready-for-committer. I think a couple of the comments could use some small adjustments, but that probably doesn't need to hold up this patch. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com
Re: Add 64-bit XIDs into PostgreSQL 15
Hi Maxim Orlov: >AFAICS, we have a following options: >1. Making "true" 64�Cbit XIDs. I.e. making every tuple have 64�Cbit xmin and >>xmax fields. >2. Put special in every page where base for XIDs are stored. This is what we >>have done in the current patch set. >3. Put base for XIDs in a fork. >4. Make explicit 64�Cbit XIDs for concrete relations. I.e. CREATE TABLE foo >>WITH (xid8) of smth. I think the first solution will not be agreed by the core committers, they will consider that the change is too big and will affect the stability of PostgreSQL,I think the second solution is actually quite good, and you've been working on it now,and there are successful cases (opengauss is implemented in this way,In order to save space and be compatible with older versions, opengauss design is to store the xmin/xmax of the head of the tuple in two parts, the xmin/xmax of the head of the tuple is the number of uint32; the header of the page stores the 64-bit xid_base, which is the xid_base of the current page.),I think it's best to stick to this solution now. Opengauss tuple structure: [cid:3fae289c-7f88-46be-a775-2d93b1a9c41e] Best wish 发件人: Maxim Orlov 发送时间: 2022年12月28日 18:14 收件人: Pavel Borisov 抄送: Robert Haas ; Chris Travers ; Bruce Momjian ; Aleksander Alekseev ; pgsql-hackers@lists.postgresql.org ; Chris Travers ; Peter Geoghegan ; Fedor Sigaev ; Alexander Korotkov ; Konstantin Knizhnik ; Nikita Glukhov ; Yura Sokolov ; Simon Riggs 主题: Re: Add 64-bit XIDs into PostgreSQL 15 Hi! I want to make a quick summary here. 1. An overall consensus has been reached: we shall focus on committing SLRU changes first. 2. I've created an appropriate patch set here [0]. 3. How [0] is waiting for a review. As always, all opinions will be welcome. 4. While discussing error/warning messages and some other stuff, this thread was marked as "Waiting on Author". 5. I do rebase this patch set once in a week, but do not post it here, since there is no need in it. See (1). 6. For now, I don't understand what changes I have to make here. So, does "Waiting on Author" is appropriate status here? Anyway. Let's discuss on-disk page format, shall we? AFAICS, we have a following options: 1. Making "true" 64�Cbit XIDs. I.e. making every tuple have 64�Cbit xmin and xmax fields. 2. Put special in every page where base for XIDs are stored. This is what we have done in the current patch set. 3. Put base for XIDs in a fork. 4. Make explicit 64�Cbit XIDs for concrete relations. I.e. CREATE TABLE foo WITH (xid8) of smth. There were opinions that the proposed solution (2) is not the optimal. It would be great to hear your concerns and thoughts. [0] https://www.postgresql.org/message-id/CACG%3Dezav34TL%2BfGXD5vJ48%3DQbQBL9BiwkOTWduu9yRqie-h%2BDg%40mail.gmail.com -- Best regards, Maxim Orlov.
回复: Add 64-bit XIDs into PostgreSQL 15
Hi Chris Travers Robertmhaas said that the project Zheap is dead（https://twitter.com/andy_pavlo/status/1590703943176589312）, which means that we cannot use Zheap to deal with the issue of xid wraparound and dead tuples in tables. The dead tuple issue is not a big deal because I can still use pg_repack to handle, although pg_repack will cause wal log to increase dramatically and may take one or two days to handle a large table. During this time the database can be accessed by external users, but the xid wraparound will cause PostgreSQL to be down, which is a disaster for DBAs. Maybe you are not a DBA, or your are from a small country, Database system tps is very low, so xid32 is enough for your database system , Oracle's scn was also 32bits, however, Oracle realized the issue and changed scn to 64 bits. The transaction id in mysql is 48 bits. MySQL didn't fix the transaction id wraparound problem because they think that 48 bits is enough for the transaction id. This project has been running for almost 1 year and now it is coming to an end. I strongly disagree with your idea of stopping this patch, and I suspect you are a saboteur. I strongly disagree with your viewpoint, as it is not a fundamental way to solve the xid wraparound problem. The PostgreSQL community urgently needs developers who solve problems like this, not bury one' head in the sand Best whish 发件人: Peter Geoghegan 发送时间: 2022年12月1日 0:35 收件人: Robert Haas 抄送: Chris Travers ; Bruce Momjian ; Aleksander Alekseev ; pgsql-hackers@lists.postgresql.org ; Chris Travers ; Fedor Sigaev ; Alexander Korotkov ; Konstantin Knizhnik ; Nikita Glukhov ; Yura Sokolov ; Maxim Orlov ; Pavel Borisov ; Simon Riggs 主题: Re: Add 64-bit XIDs into PostgreSQL 15 On Wed, Nov 30, 2022 at 8:13 AM Robert Haas wrote: > I haven't checked the patches to see whether they look correct, and > I'm concerned in particular about upgrade scenarios. But if there's a > way we can get that part committed, I think it would be a clear win. +1 -- Peter Geoghegan
Re: Add 64-bit XIDs into PostgreSQL 15
Hi Aleksander Alekseev I think the xids 32bit transformation project has been dragged on for too long. Huawei's openGauss referenced this patch to implement xids 64bit, and Postgrespro also implemented xids 64bit, which is enough to prove that their worries are redundant.I think postgresql has no reason not to implement xid 64 bit. What about your opinion? Best whish 发件人: Aleksander Alekseev 发送时间: 2022年12月9日 20:49 收件人: pgsql-hackers@lists.postgresql.org 抄送: adherent postgres ; Chris Travers ; Chris Travers ; Bruce Momjian 主题: Re: Add 64-bit XIDs into PostgreSQL 15 Hi adherent, > Robertmhaas said that the project Zheap is > dead（https://twitter.com/andy_pavlo/status/1590703943176589312）, which means > that we cannot use Zheap to deal with the issue of xid wraparound and dead > tuples in tables. The dead tuple issue is not a big deal because I can still > use pg_repack to handle, although pg_repack will cause wal log to increase > dramatically and may take one or two days to handle a large table. During > this time the database can be accessed by external users, but the xid > wraparound will cause PostgreSQL to be down, which is a disaster for DBAs. > Maybe you are not a DBA, or your are from a small country, Database system > tps is very low, so xid32 is enough for your database system , Oracle's scn > was also 32bits, however, Oracle realized the issue and changed scn to 64 > bits. The transaction id in mysql is 48 bits. MySQL didn't fix the > transaction id wraparound problem because they think that 48 bits is enough > for the transaction id. This project has been running for almost 1 year and > now it is coming to an end. I strongly disagree with your idea of stopping > this patch, and I suspect you are a saboteur. I strongly disagree with your > viewpoint, as it is not a fundamental way to solve the xid wraparound > problem. The PostgreSQL community urgently needs developers who solve > problems like this, not bury one' head in the sand This is not uncommon for people on the mailing list to have disagreements. This is part of the process, we all are looking for consensus. It's true that different people have different use cases in mind and different backgrounds as well. It doesn't mean these use cases are wrong and/or the experience is irrelevant and/or the received feedback should be just discarded. Although I also expressed my disagreement with Chris before, let's not assume any bad intent and especially sabotage as you put it. (Unless you have a strong proof of this of course which I doubt you have.) We want all kinds of feedback to be welcomed here. I'm sure our goal here is mutual, to make PostgreSQL even better than it is now. The only problem is that the definition of "better" varies sometimes. I see you believe that 64-bit XIDs are going to be useful. That's great! Tell us more about your case and how the patch is going to help with it. Also, maybe you could test your load with the applied patchset and tell us whether it makes things better or worse? Personally I would love hearing this from you. -- Best regards, Aleksander Alekseev
回复: Add 64-bit XIDs into PostgreSQL 15
Hi Pavel Borisov Now the disk performance has been improved many times, and the capacity has also been increased many times,The wal log already supports lz4 and zstd compression, I think each XLOG record size will increase at least by 4 bytes which is not a big problem.What about your opinion? Best whish 发件人: Pavel Borisov 发送时间: 2022年12月9日 22:13 收件人: adherent postgres 抄送: Aleksander Alekseev ; pgsql-hackers@lists.postgresql.org ; Chris Travers ; Chris Travers ; Bruce Momjian 主题: Re: Add 64-bit XIDs into PostgreSQL 15 Hi, Adherent! On Fri, 9 Dec 2022 at 17:54, adherent postgres wrote: > > Hi Aleksander Alekseev > I think the xids 32bit transformation project has been dragged on for too > long. Huawei's openGauss referenced this patch to implement xids 64bit, and > Postgrespro also implemented xids 64bit, which is enough to prove that their > worries are redundant.I think postgresql has no reason not to implement xid > 64 bit. What about your opinion? I agree it's high time to commit 64xids into PostgreSQL. If you can do your review of the whole proposed patchset or only the part that is likely to be committed earlier [1] it would help a lot! I'd recommend beginning with the last version of the patch in thread [1]. First, it is easier. Also, this review is going to be useful sooner and will help a committer on January commitfest a lot. [1]: https://www.postgresql.org/message-id/CAFiTN-uudj2PY8GsUzFtLYFpBoq_rKegW3On_8ZHdxB1mVv3-A%40mail.gmail.com Regards, Pavel Borisov, Supabase
