Re: [HACKERS] Broken hint bits (freeze)
Sergey Burladyan writes: > 1. create master > 2. create standby from it > 3. create unlogged table and hash index like: > create unlogged table test (id int primary key, v text); > create index on test using hash (id); > 3. stop master > 4. promote standby > > now, if you try to upgrade this new promoted master pg_upgrade will stop > on this hash index: > error while creating link for relation "public.test_id_idx" > ("s/9.2/base/16384/16393" to "m/9.4/base/16422/16393"): No such file or > directory > Failure, exiting > > I touch this file (s/9.2/base/16384/16393) and rerun pg_upgrade from > scratch and it complete successfully. Missed test script for it. -- Sergey Burladyan test_hash.sh Description: Bourne shell script -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Bruce Momjian writes: > On Wed, Jun 21, 2017 at 07:49:21PM +0530, Amit Kapila wrote: > > On Tue, Jun 20, 2017 at 7:24 PM, Amit Kapila > > wrote: > > > Hmm. I think we need something that works with lesser effort because > > > not all users will be as knowledgeable as you are, so if they make any > > > mistakes in copying the file manually, it can lead to problems. How > > > about issuing a notification (XLogArchiveNotifySeg) in shutdown > > > checkpoint if archiving is enabled? > > > > > > > I have thought more about the above solution and it seems risky to > > notify archiver for incomplete WAL segments (which will be possible in > > this case as there is no guarantee that Checkpoint record will fill > > the segment). So, it seems to me we should update the document unless > > you or someone has some solution to this problem. > As far as I know this is the only remaining open issue. Sergey, please > verify. I appreciate the work everyone has done to improve this, and > all the existing fixes have been pushed to all supported branches. :-) Yes, thank you all for your help! Yes, this is last issue with checkpoint that I know, how to ensure that standby sync all shared buffers into disk on it shutdown. I thinking about enforce restartpoint on shutdown, like: src/backend/access/transam/xlog.c - 8639 if (XLogRecPtrIsInvalid(lastCheckPointRecPtr) || - 8640 XLByteLE(lastCheckPoint.redo, ControlFile->checkPointCopy.redo)) - 8641 { + 8639 if ( !(flags & CHECKPOINT_IS_SHUTDOWN) && (XLogRecPtrIsInvalid(lastCheckPointRecPtr) || + 8640 XLByteLE(lastCheckPoint.redo, ControlFile->checkPointCopy.redo) ) + 8641 { But I still not read source and not sure about this solution. PS: I successfully upgraded last night from 9.2 to 9.4 and find other issue :-) It is about hash index and promote: 1. create master 2. create standby from it 3. create unlogged table and hash index like: create unlogged table test (id int primary key, v text); create index on test using hash (id); 3. stop master 4. promote standby now, if you try to upgrade this new promoted master pg_upgrade will stop on this hash index: error while creating link for relation "public.test_id_idx" ("s/9.2/base/16384/16393" to "m/9.4/base/16422/16393"): No such file or directory Failure, exiting I touch this file (s/9.2/base/16384/16393) and rerun pg_upgrade from scratch and it complete successfully. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Bruce Momjian writes: > On Tue, Jun 20, 2017 at 06:42:58PM +0300, Sergey Burladyan wrote: > > If file at standby in old data directory is different from same file at > > master, but it have same size, it will be hardlinked into new data > > directory at standby and does not copied from master. > > Only if pg_upgrade created the hardlinks, right? Yes, I have not tested rsync itself, but I think that you are right. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Amit Kapila writes: > > I am not sure about rsync, in my production server I have for example > > 111 GB in pg_xlog and if I run rsync for pg_xlog it must send ~ 40GB > > of new WALs I think. > > > > Isn't the difference between old and new is just the last WAL segment > file? What is the source of this difference? Postgres generate WAL files forward, and at standby too :-( For example: === master === $ psql -c 'select pg_current_xlog_insert_location()' pg_current_xlog_insert_location - 4ED09/34A74590 (1 row) $ ls 9.2/main/pg_xlog/ | awk '/4ED090034/,/xxx/ { print }' | wc -l 2262 == === standby === $ psql -c 'select pg_last_xlog_replay_location()' pg_last_xlog_replay_location -- 4ED0A/AECFD7B8 (1 row) postgres@avi-sql29:~$ ls 9.2/main/pg_xlog/ | awk '/4ED0A00AE/,/xxx/ { print }' | wc -l 2456 === See https://www.postgresql.org/docs/9.2/static/wal-configuration.html > they are recycled (renamed to become the next segments in the numbered > sequence) -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Bruce Momjian writes: > On Tue, Jun 20, 2017 at 01:10:26PM +0300, Sergey Burladyan wrote: > > Bruce Momjian writes: > > > > Uh, as I understand it the rsync is going to copy the missing WAL file > > > > from the new master to the standby, right, and I think pg_controldata > > > > too, so it should be fine. Have you tested to see if it fails? > > > > It need old WAL files from old version for correct restore heap > > files. New WAL files from new version does not have this information. > > > > > The point is that we are checking the "Latest checkpoint location" to > > > make sure all the WAL was replayed. We are never going to start the > > > old standby server. Rsync is going to copy the missing/changed files. > > > > Only if missing/changed files changed in size, because rsync run with > > --size-only it does not copy changed files with same size. > > I am sorry but I am not understanding. Step 10.b says: > > 10.b Make sure the new standby data directories do not exist > > Make sure the new standby data directories do not exist or are empty. If > initdb was run, delete the standby server data directories. > > so the _entire_ new data directory is empty before rsync is run, meaning > that it is an exact copy of the new master. Yes, new data directory at standby is empty, but you missed old data directory at standby which is hardlink'ed by rsync into new as at master. rsync run with _three_ arguments and with --hard-links option: rsync --archive --delete --hard-links --size-only old_pgdata new_pgdata remote_dir (remote_dir is parent directory for old and new data at standby) In this mode rsync compare not only new_pgdata with new empty data directory at standby, but also compare it with old data directory from standby and with --size-only it doing this compare only by the file existence or file size. If file at standby in old data directory is different from same file at master, but it have same size, it will be hardlinked into new data directory at standby and does not copied from master. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Amit Kapila writes: > On Tue, Jun 20, 2017 at 3:40 PM, Sergey Burladyan wrote: > > Bruce Momjian writes: > > > >> On Mon, Jun 19, 2017 at 10:59:19PM -0400, Bruce Momjian wrote: > >> > On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: > >> > > 20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" > >> > > написал: > >> > > > >> > > > >> > > We are saying that Log-Shipping should match "Latest checkpoint > >> > > location", but the WAL for that will not be sent to the standby, > >> > > so it > >> > > will not match, but that is OK since the only thing in the > >> > > non-shipped > >> > > WAL file is the checkpoint record. How should we modify the > >> > > wording on > >> > > this? > >> > > > >> > > > >> > > I am afraid that without this checkpoint record standby cannot make > >> > > restartpoint > >> > > and without restartpoint it does not sync shared buffers into disk at > >> > > shutdown. > >> > > > It seems to me at shutdown time on standby servers we specifically > make restart points. See below code in ShutdownXLOG() > > .. > if (RecoveryInProgress()) > CreateRestartPoint(CHECKPOINT_IS_SHUTDOWN | CHECKPOINT_IMMEDIATE); > .. > > Do you have something else in mind? What buffers this restartpoint will save into disk? I think what it can save only buffers with LSN lower or equal to "Latest checkpoint location". Buffers with LSN between "Minimum recovery ending location" and "Latest checkpoint location" will not saved at all. I set log_min_messages=debug2 and it is more clearly what happened here: 2017-06-20 13:18:32 GMT LOG: restartpoint starting: xlog ... 2017-06-20 13:18:33 GMT DEBUG: postmaster received signal 15 2017-06-20 13:18:33 GMT LOG: received smart shutdown request 2017-06-20 13:18:33 GMT DEBUG: updated min recovery point to 0/1200 2017-06-20 13:18:33 GMT CONTEXT: writing block 2967 of relation base/16384/16385 2017-06-20 13:18:33 GMT DEBUG: checkpoint sync: number=1 file=global/12587 time=0.001 msec 2017-06-20 13:18:33 GMT DEBUG: checkpoint sync: number=2 file=base/16384/12357 time=0.000 msec 2017-06-20 13:18:33 GMT DEBUG: checkpoint sync: number=3 file=base/16384/16385 time=0.000 msec 2017-06-20 13:18:33 GMT DEBUG: attempting to remove WAL segments older than log file 0001000B 2017-06-20 13:18:33 GMT DEBUG: recycled transaction log file "0001000B" 2017-06-20 13:18:33 GMT DEBUG: recycled transaction log file "0001000A" 2017-06-20 13:18:33 GMT DEBUG: recycled transaction log file "00010009" 2017-06-20 13:18:33 GMT DEBUG: SlruScanDirectory invoking callback on pg_subtrans/ 2017-06-20 13:18:33 GMT LOG: restartpoint complete: wrote 1824 buffers (44.5%); 0 transaction log file(s) added, 0 removed, 3 recycled; write=1.389 s, sync=0.000 s, total=1.389 s; sync files=3, longest=0.000 s, average=0.000 s 2017-06-20 13:18:33 GMT LOG: recovery restart point at 0/F008D28 2017-06-20 13:18:33 GMT DETAIL: last completed transaction was at log time 2017-06-20 13:18:29.282645+00 2017-06-20 13:18:33 GMT LOG: shutting down 2017-06-20 13:18:33 GMT DEBUG: skipping restartpoint, already performed at 0/F008D28 2017-06-20 13:18:33 GMT LOG: database system is shut down I use pg 9.2 and "skipping restartpoint, already performed at" is from src/backend/access/transam/xlog.c:8643 after this statement it return from CreateRestartPoint() and do not run 8687 CheckPointGuts(lastCheckPoint.redo, flags); > >> > Uh, as I understand it the rsync is going to copy the missing WAL file > >> > from the new master to the standby, right, and I think pg_controldata > >> > too, so it should be fine. Have you tested to see if it fails? > > > > It need old WAL files from old version for correct restore heap > > files. New WAL files from new version does not have this information. > > > > So in such a case can we run rsync once before pg_upgrade? I just copy last WAL from stopped old master into running old standby before it shutdown and wait till it replayed. After that standby can issue restartpoint at the same location as in stopped master. I am not sure about rsync, in my production server I have for example 111 GB in pg_xlog and if I run rsync for pg_xlog it must send ~ 40GB of new WALs I think. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Bruce Momjian writes: > On Mon, Jun 19, 2017 at 10:59:19PM -0400, Bruce Momjian wrote: > > On Tue, Jun 20, 2017 at 03:50:29AM +0300, Sergey Burladyan wrote: > > > 20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" > > > написал: > > > > > > > > > We are saying that Log-Shipping should match "Latest checkpoint > > > location", but the WAL for that will not be sent to the standby, so it > > > will not match, but that is OK since the only thing in the non-shipped > > > WAL file is the checkpoint record. How should we modify the wording > > > on > > > this? > > > > > > > > > I am afraid that without this checkpoint record standby cannot make > > > restartpoint > > > and without restartpoint it does not sync shared buffers into disk at > > > shutdown. > > > > Uh, as I understand it the rsync is going to copy the missing WAL file > > from the new master to the standby, right, and I think pg_controldata > > too, so it should be fine. Have you tested to see if it fails? It need old WAL files from old version for correct restore heap files. New WAL files from new version does not have this information. > The point is that we are checking the "Latest checkpoint location" to > make sure all the WAL was replayed. We are never going to start the > old standby server. Rsync is going to copy the missing/changed files. Only if missing/changed files changed in size, because rsync run with --size-only it does not copy changed files with same size. I have this test script and without copy_last_wal it make standby broken in the first few loops, like: === run 1, cnt: 70 === run 2, cnt: 729450 PS: I think what with big shared_buffers I can make it broken more quickly, but with big shared_buffers I cannot break it at all, hm... -- Sergey Burladyan test_rsync.sh Description: Bourne shell script -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
20 июн. 2017 г. 1:21 пользователь "Bruce Momjian" написал: We are saying that Log-Shipping should match "Latest checkpoint location", but the WAL for that will not be sent to the standby, so it will not match, but that is OK since the only thing in the non-shipped WAL file is the checkpoint record. How should we modify the wording on this? I am afraid that without this checkpoint record standby cannot make restartpoint and without restartpoint it does not sync shared buffers into disk at shutdown.
Re: [HACKERS] Broken hint bits (freeze)
Bruce Momjian writes: > On Fri, Jun 16, 2017 at 04:33:16AM +0300, Sergey Burladyan wrote: > > Bruce Momjian writes: > > > ! > > > ! Also, if upgrading standby servers, change wal_level > > > ! to replica in the postgresql.conf file on > > > ! the new cluster. > > > > > > > > > > I am not sure how this help. > > > > wal_level is reset by pg_resetxlog during pg_upgrade, so it does not > > depend on postgresql.conf. After pg_upgrade wal_level always is > > 'minimal', that is why you must start and stop new master before rsync: > > > > output > > $ "$bin"/pg_controldata "$ver" | grep wal_level > > wal_level setting:replica > > > > $ "$bin"/pg_resetwal "$ver" > > Write-ahead log reset > > > > $ "$bin"/pg_controldata "$ver" | grep wal_level > > wal_level setting:minimal > > > > Yes, I see that, but pg_resetxlog is run _before_ the _new_ cluster is > started for the last time, so in my testing the wal_level at the end of > pg_upgrade matches the value in postgresql.conf, e.g. "replica". For > example: > > Upgrade Complete > > Optimizer statistics are not transferred by pg_upgrade so, > once you start the new server, consider running: > ./analyze_new_cluster.sh > > Running this script will delete the old cluster's data files: > ./delete_old_cluster.sh > > $ pg_controldata /u/pg/data/ | grep wal_level > wal_level setting:replica > > The way pg_upgrade uses rsync, the standby never needs to replay the WAL > when it starts up because we already copied the changed system tables > and hard linked the user data files. Oh, it is my fail, I was not run test script completely for current git master. In git master it work as expected. But not in previous versions. I used this test script and got this result: 9.2 -> master: wal_level setting:replica 9.2 -> 9.6: wal_level setting:minimal 9.2 -> 9.5: wal_level setting:minimal 9.2 -> 9.4: Current wal_level setting:minimal I also save strace for pg_upgrade: === 9.6 === pg_resetxlog", ["/home/sergey/inst/pg9.6/bin/pg_resetxlog", "-l", "00010002", "9.6"], pg_ctl", ["/home/sergey/inst/pg9.6/bin/pg_ctl", "-w", "-l", "pg_upgrade_server.log", "-D", "9.6", pg_ctl", ["/home/sergey/inst/pg9.6/bin/pg_ctl", "-w", "-D", "9.6", "-o", "", "-m", "smart", "stop"], pg_resetxlog", ["/home/sergey/inst/pg9.6/bin/pg_resetxlog", "-o", "16393", "9.6"], [/* 68 vars */]) = 0 === It is exec pg_resetxlog last for set next OID, it is from src/bin/pg_upgrade/pg_upgrade.c:149 === master === pg_resetwal", ["/home/sergey/inst/pg-master/bin/pg_resetwal", "-l", "00010002", "master"], pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-l", "pg_upgrade_server.log", "-D", "master", pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-D", "master", "-o", "", "-m", "smart", "stop"], pg_resetwal", ["/home/sergey/inst/pg-master/bin/pg_resetwal", "-o", "16393", "master"], [/* 70 vars */]) = 0 pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-l", "pg_upgrade_server.log", "-D", "master", pg_ctl", ["/home/sergey/inst/pg-master/bin/pg_ctl", "-w", "-D", "master", "-o", "", "-m", "smart", "stop"], == >From git master pg_upgrade is restart new master again after pg_resetwal -o, as you said. It is from src/bin/pg_upgrade/check.c:176 void issue_warnings(void) { /* Create dummy large object permissions for old < PG 9.0? */ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 804) { start_postmaster(&new_cluster, true); new_9_0_populate_pg_largeobject_metadata(&new_cluster, false); stop_postmaster(false); } /* Reindex hash indexes for old < 10.0 */ if (GET_MAJOR_VERSION(old_cluster.major_version) <= 906) { start_postmaster(&new_cluster, true); old_9_6_invalidate_hash_indexes(&new_cluster, false); stop_postmaster(false); } } -- Sergey Burladyan sh_wfR3JYaTl.sh Description: Bourne shell script -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Bruce Momjian writes: > On Fri, Jun 16, 2017 at 08:10:13PM +0530, Amit Kapila wrote: > > On Fri, Jun 16, 2017 at 7:03 AM, Sergey Burladyan > > wrote: > > > Bruce Momjian writes: > > > > > >> ! against the old primary and standby clusters. Verify that the > > >> ! Latest checkpoint location values match in all clusters. > > > > > > For "Log-Shipping only" standby server this cannot be satisfied, because > > > last WAL from master (with shutdown checkpoint) never archived. > > > > > > > Yeah, we have ensured that all the transactions before shutdown > > checkpoint got archived. It is done in commit > > 2e6107cb621d003dcab0df53ac8673ea67c4e467. However, it is not clear to > > me neither it is mentioned in comments why we have done it that way. > > Yes, I am confused why Sergey doesn't see that behavior. I think this last new switched WAL with shutdown checkpoint record is incomplete and it does not marked as *.ready in pg_xlog/archive_status/ and not archived. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Bruce Momjian writes: > ! against the old primary and standby clusters. Verify that the > ! Latest checkpoint location values match in all clusters. For "Log-Shipping only" standby server this cannot be satisfied, because last WAL from master (with shutdown checkpoint) never archived. For example (git master): postgresql.conf === port = 5430 shared_buffers = 32MB wal_level = hot_standby archive_mode = on archive_command = 'test ! -f "$ARH/%f" && ( echo "arch %p"; cp %p "$ARH/%f"; )' max_wal_senders = 5 hot_standby = on log_line_prefix = '%t ' log_checkpoints = on lc_messages = C pg_control pg_control version number:1002 Catalog version number: 201705301 Database system identifier: 6432034080221219745 Database cluster state: shut down pg_control last modified: Fri Jun 16 03:57:22 2017 Latest checkpoint location: 0/D28 Prior checkpoint location:0/1604878 Latest checkpoint's REDO location:0/D28 Latest checkpoint's REDO WAL file:0001000D WALs archive -rw--- 1 sergey users 16777216 Jun 16 03:57 00010003 -rw--- 1 sergey users 16777216 Jun 16 03:57 00010004 -rw--- 1 sergey users 16777216 Jun 16 03:57 00010005 -rw--- 1 sergey users 16777216 Jun 16 03:57 00010006 -rw--- 1 sergey users 16777216 Jun 16 03:57 00010007 -rw--- 1 sergey users 16777216 Jun 16 03:57 00010008 -rw--- 1 sergey users 16777216 Jun 16 03:57 00010009 -rw--- 1 sergey users 16777216 Jun 16 03:57 0001000A -rw--- 1 sergey users 16777216 Jun 16 03:57 0001000B -rw--- 1 sergey users 16777216 Jun 16 03:57 0001000C == logfile arch pg_wal/0001000A arch pg_wal/0001000B 2017-06-16 00:57:21 GMT LOG: received fast shutdown request 2017-06-16 00:57:21 GMT LOG: aborting any active transactions 2017-06-16 00:57:21 GMT LOG: shutting down arch pg_wal/0001000C 2017-06-16 00:57:21 GMT LOG: checkpoint starting: shutdown immediate 2017-06-16 00:57:22 GMT LOG: checkpoint complete: wrote 4058 buffers (99.1%); 0 WAL file(s) added, 0 removed, 0 recycled; write=0.033 s, sync=0.949 s, total=1.144 s; sync files=32, longest=0.598 s, average=0.029 s; distance=190445 kB, estimate=190445 kB 2017-06-16 00:57:22 GMT LOG: database system is shut down = There is no 0001000D in archive and after shutdown, standby can only be at it previous restartpoint (0/1604878) because it does not receive latest checkpoint (0/D28) from master. So, after shutdown master and "Log-Shipping only" standby, it always "one checkpoint early" then master and "Latest checkpoint location" never match for it. I think this must be mentioned somehow in documentation. > ! > ! Also, if upgrading standby servers, change wal_level > ! to replica in the postgresql.conf file on > ! the new cluster. > > I am not sure how this help. wal_level is reset by pg_resetxlog during pg_upgrade, so it does not depend on postgresql.conf. After pg_upgrade wal_level always is 'minimal', that is why you must start and stop new master before rsync: output $ "$bin"/pg_controldata "$ver" | grep wal_level wal_level setting:replica $ "$bin"/pg_resetwal "$ver" Write-ahead log reset $ "$bin"/pg_controldata "$ver" | grep wal_level wal_level setting:minimal If you rsync standby now (without start/stop new master after pg_upgrade) you will send pg_control with wal_level=minimal into it and after that standby abort on startup: standby logfile 2017-06-16 01:22:14 GMT LOG: entering standby mode 2017-06-16 01:22:14 GMT WARNING: WAL was generated with wal_level=minimal, data may be missing 2017-06-16 01:22:14 GMT HINT: This happens if you temporarily set wal_level=minimal without taking a new base backup. 2017-06-16 01:22:14 GMT FATAL: hot standby is not possible because wal_level was not set to "replica" or higher on the master server 2017-06-16 01:22:14 GMT HINT: Either set wal_level to "replica" on the master, or turn off hot_standby here. 2017-06-16 01:22:14 GMT LOG: startup process (PID 27916) exited with exit code 1 = PS: Thank you for answer, Bruce! -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Vladimir Borodin writes: > > 6 июня 2017 г., в 23:30, Sergey Burladyan написал(а): > > > > Dmitriy Sarafannikov writes: > > > >> Starting and stopping master after running pg_upgrade but before rsync to > >> collect statistics > >> was a bad idea. > > > > But, starting and stopping master after running pg_upgrade is *required* > > by documentation: > > https://www.postgresql.org/docs/9.6/static/pgupgrade.html > >> f. Start and stop the new master cluster > >> In the new master cluster, change wal_level to replica in the > >> postgresql.conf file and then start and stop the cluster. > > > > and there is no any suggestion to disable autovacuum for it. > Yep. This should probably be fixed in the documentation? I think so. There is some problem in pg_upgrade documentation, nothing about: 1. preventing heap change by vacuum, analyze, something also when master restarted after pg_upgrade but before rsync 2. log-shipping only standby cannot shutdown at the same checkpoint with master I try to start discuss about this: https://www.postgresql.org/message-id/87y3ta49zp.fsf%40seb.koffice.internal but without luck :-) PS: I CC'd Bruce here. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Broken hint bits (freeze)
Dmitriy Sarafannikov writes: > Starting and stopping master after running pg_upgrade but before rsync to > collect statistics > was a bad idea. But, starting and stopping master after running pg_upgrade is *required* by documentation: https://www.postgresql.org/docs/9.6/static/pgupgrade.html > f. Start and stop the new master cluster > In the new master cluster, change wal_level to replica in the postgresql.conf > file and then start and stop the cluster. and there is no any suggestion to disable autovacuum for it. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Questions about upgrade standby with rsync
Hello, all! I have problem with upgrading standby via rsync. Documentation say: > Verify that the "Latest checkpoint location" values match in all clusters. But it is impossible for "WAL archive only" standby, if I not mistaken. Standby can make restartpoint only at master checkpoint location, because standby cannot write WALs. When you use WAL archive only, without streaming replication, last WAL from master with shutdown checkpoint do not archived. I think may be because WAL archiving process already terminated, when last WAL switched. For example: standby log === 2017-06-01 13:09:25 GMT LOG: recovery restart point at 0/3000790 2017-06-01 13:09:26 GMT LOG: recovery restart point at 0/603FB10 2017-06-01 13:09:27 GMT LOG: recovery restart point at 0/903ED28 2017-06-01 13:09:33 GMT LOG: recovery restart point at 0/C03EFF0 2017-06-01 13:09:38 GMT LOG: recovery restart point at 0/F040E28 now stop master and then standby: master control data after shutdown === standby control data after shutdown === Latest checkpoint location: 0/1220 Latest checkpoint location: 0/110402A8 Prior checkpoint location:0/110402A8 Prior checkpoint location:0/D067A00 Latest checkpoint's REDO location:0/1220 Latest checkpoint's REDO location:0/F040E28 Latest checkpoint's TimeLineID: 1 Latest checkpoint's TimeLineID: 1 Latest checkpoint's full_page_writes: on Latest checkpoint's full_page_writes: on Latest checkpoint's NextXID: 0/1267 Latest checkpoint's NextXID: 0/1267 Latest checkpoint's NextOID: 16393 Latest checkpoint's NextOID: 24576 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiXactId: 1 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's NextMultiOffset: 0 Latest checkpoint's oldestXID:1254 Latest checkpoint's oldestXID:1254 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestXID's DB: 1 Latest checkpoint's oldestActiveXID: 0 Latest checkpoint's oldestActiveXID: 1266 Time of latest checkpoint:Thu Jun 1 16:09:32 2017 Time of latest checkpoint:Thu Jun 1 16:09:30 2017 Minimum recovery ending location: 0/0Minimum recovery ending location: 0/1200 As you can see, when standby was run — it was consistent with master: "Minimum recovery ending location: 0/1200" but it last restartpoint is previous checkpoint (from master), because last WAL (00010012) from master do not archived: xlogdump m/9.2/pg_xlog/00010012: [page:0, xlp_info:6, xlp_tli:1, xlp_pageaddr:0/1200] XLP_LONG_HEADER XLP_BKP_REMOVABLE Unexpected page info flags 0006 at offset 0 [cur:0/1220, xid:0, rmid:0(XLOG), len/tot_len:64/96, info:0, prev:0/11387BB8] checkpoint: redo 0/1220; tli 1; nextxid 1267; nextoid 16393; nextmulti 1; nextoffset 0; shutdown at 2017-06-01 16:09:32 MSK ReadRecord: record with zero len at 0/1280 This WAL file is only at master pg_xlog, and not in WAL archive. And my second question, this algorithm with rsync described only starting from pg 9.5, is it possible to use it for upgrade from pg 9.2 to pg 9.4? Thanks! -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_upgrade and epoch
On Wed, Apr 23, 2014 at 6:38 AM, Sergey Konoplev wrote: > > BTW, I didn't manage to make a test case yet. Recently, when I was > migrating several servers to skytools3 and upgrading from 9.0 to 9.2, > I noticed that epoch was copied, timeline id was >0 after upgrade, but > ... This is strange, if I not mistaken XID copied by copy_clog_xlog_xid(void): http://doxygen.postgresql.org/pg__upgrade_8c_source.html#l00398 and there is no epoch (-e XIDEPOCH) in pg_resetxlog call args I test it like this: ~/inst/pg9.2/bin/initdb d9.2 /usr/lib/postgresql/9.3/bin/initdb d9.3 ~/inst/pg9.2/bin/postgres -D d9.2 -p 5433 ^C ~/inst/pg9.2/bin/pg_resetxlog -e 8 d9.2/ ~/inst/pg9.2/bin/postgres -D d9.2 -p 5433 psql -At -h localhost -p 5433 -c 'select txid_current()' postgres 34359739064 ^C /usr/lib/postgresql/9.3/bin/pg_upgrade -d d9.2 -D d9.3 -b ~/inst/pg9.2/bin -B /usr/lib/postgresql/9.3/bin -k /usr/lib/postgresql/9.3/bin/postgres -D d9.3 -p 5433 -k`pwd`/s psql -At -h localhost -p 5433 -c 'select txid_current()' postgres 756 34359739064 switched to 756 after upgrade -- Sergey Burladyan
[HACKERS] pg_upgrade and epoch
Hi All! Current pg_upgrade copy XID into new cluster, but not it epoch. Why? Without epoch from old cluster txid_current() in upgraded database return lower value than before upgrade. This break, for example, PgQ and it must be fixed by hand after upgrade with pg_resetxlog. PS: see http://lists.pgfoundry.org/pipermail/skytools-users/2014-April/001812.html -- Sergey Burladyan
Re: [HACKERS] [BUGS] BUG #9223: plperlu result memory leak
Hi! On Thu, Mar 6, 2014 at 6:59 AM, Alex Hunsaker wrote: . . . > This will apply cleanly all the way to REL9_2_STABLE. It applies (with > fuzz, but cleanly to REL9_1). REL9_0 does this completely differently > and so does not have this leak. > Looks like patch still not pushed to repo. -- Sergey Burladyan
Re: [HACKERS] [BUGS] BUG #9223: plperlu result memory leak
Alex Hunsaker writes: > On Tue, Feb 25, 2014 at 6:56 AM, Sergey Burladyan wrote: > > > It looks like I found the problem, Perl use reference count and something > > that > > is called "Mortal" for memory management. As I understand it, mortal is > > free > > after FREETMPS. Plperl call FREETMPS in plperl_call_perl_func() but after > > it, > > plperl ask perl interpreter again for new mortal SV variables, for example, > > in > > hek2cstr from plperl_sv_to_datum, and this new SV is newer freed. > > So I think hek2cstr is the only place we leak (its the only place I > can see that allocates a mortal sv without being wrapped in > ENTER/SAVETMPS/FREETMPS/LEAVE). Yeah, I also try to fix only hek2cstr, but failed. > Does the attached fix it for you? Yes, your patch is fix it for me, thank you, Alex! -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [BUGS] BUG #9223: plperlu result memory leak
Hello, All! eshkin...@gmail.com writes: > create function perl_test(IN data text, OUT v1 text, OUT v2 integer, OUT v3 > integer, OUT v4 json, OUT v5 json) > returns record as > $BODY$ > > use strict; > use warnings; > > my $res->{'v1'} = 'content'; > > return $res; > > $BODY$ > language plperlu volatile strict; > test case: > select count(perl_test('')) from generate_series(1, 100); It looks like I found the problem, Perl use reference count and something that is called "Mortal" for memory management. As I understand it, mortal is free after FREETMPS. Plperl call FREETMPS in plperl_call_perl_func() but after it, plperl ask perl interpreter again for new mortal SV variables, for example, in hek2cstr from plperl_sv_to_datum, and this new SV is newer freed. I experiment with this patch, and it fix memory leak in my case, but patch is incomplete. It does not free on error and fix only plperl_func_handler, plperl_trigger_handler and may be plperl_inline_handler must be also fixed. Patch for REL9_2_STABLE. without patch: PIDVSZ RSS 2503 74112 7740 2503 152928 86860 2503 232208 165836 2503 310732 244508 2503 389264 323032 with patch: PIDVSZ RSS 4322 74112 7740 4322 74380 8340 4322 74380 8340 4322 74380 8340 4322 74380 8340 diff --git a/src/pl/plperl/plperl.c b/src/pl/plperl/plperl.c index 49d50c4..9c9874d 100644 --- a/src/pl/plperl/plperl.c +++ b/src/pl/plperl/plperl.c @@ -2173,6 +2173,9 @@ plperl_func_handler(PG_FUNCTION_ARGS) ReturnSetInfo *rsi; ErrorContextCallback pl_error_context; + ENTER; + SAVETMPS; + if (SPI_connect() != SPI_OK_CONNECT) elog(ERROR, "could not connect to SPI manager"); @@ -2271,6 +2274,9 @@ plperl_func_handler(PG_FUNCTION_ARGS) SvREFCNT_dec(perlret); + FREETMPS; + LEAVE; + return retval; } -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Draft release notes for 9.3.2
On Mon, Dec 2, 2013 at 4:56 AM, Tom Lane wrote: > Andres Freund writes: > > On 2013-12-01 18:56:19 -0500, Tom Lane wrote: > >> I'd like to do any required editing on the notes at this stage, > >> before I start extracting relevant subsets for the older branches. > > > When do you plan to backpatch the documentation? > > Tomorrow afternoon (my time). > > regards, tom lane > > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers > Hello! Is it possible to fix my surname in changelog? -Sergey Burladyn +Sergey Burladyan it is not a problem if it is impossible :-) Thanks! -- Sergey Burladyan
Re: [HACKERS] Recovery target 'immediate'
On Thu, Apr 18, 2013 at 10:11 PM, Heikki Linnakangas < hlinnakan...@vmware.com> wrote: > I just found out that if you use continuous archiving and online backups, > it's surprisingly difficult to restore a backup, without replaying any more > WAL than necessary. > You can find first WAL file name in backup_label "START WAL LOCATION". Last WAL file name location depends on source type, if backup from slave - use pg_control from backup and "Minimum recovery ending location", if backup from master - use "STOP WAL LOCATION" from backup .history file :-) Then I just copy needed WALs from archive into pg_xlog and remove recovery.conf. It seems that we're missing a setting, something like recovery_target = > 'immediate', which would mean "stop as soon as consistency is reached". Or > am I missing some trick? > This will be helpful :) -- Sergey Burladyan
Re: [HACKERS] Patch for BUG #6480, psql incorrect indent for inherited tables names with UTF-8 NLS
Alvaro Herrera writes: > I'm sorry, but the releases are already tagged :-( So they will contain > the buggy output for a while yet. Ah, I see, ok, wait next! :) -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Patch for BUG #6480, psql incorrect indent for inherited tables names with UTF-8 NLS
See http://archives.postgresql.org/pgsql-bugs/2012-02/msg00164.php This need function for count characters, not bytes. I find this pg_wcswidth but it is not used anywhere and broken. So, I fix it and also change it prototype for remove type casting. PS: Please help, next week release will be with Russian translation again (thank you, Alexander!) and this tiny bug is slightly annoying. >From aaa828e05691ca58067d74cea43dabe8863ccdf7 Mon Sep 17 00:00:00 2001 From: Sergey Burladyan Date: Thu, 23 Feb 2012 04:09:10 +0400 Subject: [PATCH] Fix NLS text width and pg_wcswidth function --- src/bin/psql/describe.c |4 ++-- src/bin/psql/mbprint.c |7 --- src/bin/psql/mbprint.h |2 +- 3 files changed, 7 insertions(+), 6 deletions(-) diff --git a/src/bin/psql/describe.c b/src/bin/psql/describe.c index b2c54b5..2b63df4 100644 *** a/src/bin/psql/describe.c --- b/src/bin/psql/describe.c *** describeOneTableDetails(const char *sche *** 2068,2074 if (i == 0) printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0)); else ! printfPQExpBuffer(&buf, "%*s %s", (int) strlen(s), "", PQgetvalue(result, i, 0)); if (i < tuples - 1) appendPQExpBuffer(&buf, ","); --- 2068,2074 if (i == 0) printfPQExpBuffer(&buf, "%s: %s", s, PQgetvalue(result, i, 0)); else ! printfPQExpBuffer(&buf, "%*s %s", pg_wcswidth(s, strlen(s), pset.encoding), "", PQgetvalue(result, i, 0)); if (i < tuples - 1) appendPQExpBuffer(&buf, ","); *** describeOneTableDetails(const char *sche *** 2109,2115 ct, PQgetvalue(result, i, 0)); else printfPQExpBuffer(&buf, "%*s %s", ! (int) strlen(ct), "", PQgetvalue(result, i, 0)); if (i < tuples - 1) appendPQExpBuffer(&buf, ","); --- 2109,2115 ct, PQgetvalue(result, i, 0)); else printfPQExpBuffer(&buf, "%*s %s", ! pg_wcswidth(ct, strlen(ct), pset.encoding), "", PQgetvalue(result, i, 0)); if (i < tuples - 1) appendPQExpBuffer(&buf, ","); diff --git a/src/bin/psql/mbprint.c b/src/bin/psql/mbprint.c index 248a4db..477e35d 100644 *** a/src/bin/psql/mbprint.c --- b/src/bin/psql/mbprint.c *** mb_utf_validate(unsigned char *pwcs) *** 173,179 * only appear on one line. OTOH it is easier to use if this applies to you. */ int ! pg_wcswidth(const unsigned char *pwcs, size_t len, int encoding) { int width = 0; --- 173,179 * only appear on one line. OTOH it is easier to use if this applies to you. */ int ! pg_wcswidth(const char *pwcs, size_t len, int encoding) { int width = 0; *** pg_wcswidth(const unsigned char *pwcs, s *** 182,196 int chlen, chwidth; ! chlen = PQmblen((const char *) pwcs, encoding); if (chlen > len) break;/* Invalid string */ ! chwidth = PQdsplen((const char *) pwcs, encoding); if (chwidth > 0) width += chwidth; pwcs += chlen; } return width; } --- 182,197 int chlen, chwidth; ! chlen = PQmblen(pwcs, encoding); if (chlen > len) break;/* Invalid string */ ! chwidth = PQdsplen(pwcs, encoding); if (chwidth > 0) width += chwidth; pwcs += chlen; + len -= chlen; } return width; } diff --git a/src/bin/psql/mbprint.h b/src/bin/psql/mbprint.h index f729ef0..5c13d97 100644 *** a/src/bin/psql/mbprint.h --- b/src/bin/psql/mbprint.h *** struct lineptr *** 10,16 }; extern unsigned char *mbvalidate(unsigned char *pwcs, int encoding); ! extern int pg_wcswidth(const unsigned char *pwcs, size_t len, int encoding); extern void pg_wcsformat(unsigned char *pwcs, size_t len, int encoding, struct lineptr * lines, int count); extern void pg_wcssize(unsigned char *pwcs, size_t len, int encoding, int *width, int *height, int *format_size); --- 10,16 }; extern unsigned char *mbvalidate(unsigned char *pwcs, int encoding); ! extern int pg_wcswidth(const char *pwcs, size_t len, int encoding); extern void pg_wcsformat(unsigned char *pwcs, size_t len, int encoding, struct lineptr * lines, int count); extern void pg_wcssize(unsigned char *pwcs, size_t len, int encoding, int *width, int *height, int *format_size); -- 1.7.9 -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] FreeBSD 8.0 i386, plpythonu, threaded Python not supported on this platform
Hi all! ./configure --prefix=$HOME/inst/pg-9 --enable-nls --enable-debug --enable-depend --enable-cassert --enable-thread-safety --with-pgport=5431 --with-libxml --with-libxslt --with-python --with-perl --with-tcl FLEX=/usr/local/bin/flex --8<---cut here---start->8--- checking for python... /usr/local/bin/python checking for Python distutils module... yes checking Python configuration directory... /usr/local/lib/python2.6/config checking how to link an embedded Python application... -L/usr/local/lib/python2.6/config -lpython2.6 -lutil -lm checking whether Python is compiled with thread support... yes configure: error: threaded Python not supported on this platform --8<---cut here---end--->8--- Where can I find a description of this problem? Is any chance to fix it? -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] UTF8 regexp and char classes still does not work
Tom Lane writes: > Hmm, you're right. I only tested that on Latin1 characters, for which > it does work because those have Unicode points below 256. I'm not > sure of a reasonable solution for the general case --- we certainly > don't want this function iterating up to 2^21 or thereabouts. Yes, i understand this problem. How perl do this? May be this Unicode table can be precomputed or linked to postgres binary from external source? > Your test case seems to be using KOI8 encoding, though, which doesn't > have anything to do with UTF8 behavior. It's just for example of expected result. See first test, it is UTF8, two bytes per character: > > --- CYRILLIC SMALL LETTER ZHE ~* CYRILLIC CAPITAL LETTER ZHE > > select E'\320\266' ~* E'\320\226', E'\320\266' ~ '[[:alpha:]]+', 'g' ~ > > '[[:alpha:]]+'; > > ?column? | ?column? | ?column? > > --+--+-- > > t| f| t -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] UTF8 regexp and char classes still does not work
I see this in 9.0 Release note: - Support locale-specific regular expression processing with UTF-8 server encoding (Tom Lane) Locale-specific regular expression functionality includes case-insensitive matching and locale-specific character classes. But character classes still does not work, example (git REL9_0_STABLE c767c3bd): select version(); version PostgreSQL 9.0.0 on x86_64-unknown-linux-gnu, compiled by GCC gcc (Debian 4.4.4-8) 4.4.5 20100728 (prerelease), 64-bit --- CYRILLIC SMALL LETTER ZHE ~* CYRILLIC CAPITAL LETTER ZHE select E'\320\266' ~* E'\320\226', E'\320\266' ~ '[[:alpha:]]+', 'g' ~ '[[:alpha:]]+'; ?column? | ?column? | ?column? --+--+-- t| f| t all must be true, like below: create database koi8 template template0 encoding 'koi8r' lc_collate 'ru_RU.KOI8-R' lc_ctype 'ru_RU.KOI8-R'; \c koi8 set client_encoding TO utf8; select E'\326' ~* E'\366', E'\326' ~ '[[:alpha:]]+', 'g' ~ '[[:alpha:]]+'; ?column? | ?column? | ?column? --+--+-- t| t| t As i can see in Tom's patch 0d323425 only functions like pg_wc_isalpha is changed, but this pg_wc_isalpha is called from static struct cvec * cclass(struct vars * v,/* context */ const chr *startp, /* where the name starts */ const chr *endp,/* just past the end of the name */ int cases) /* case-independent? */ function, and this function have comment "For the moment, assume that only char codes < 256 can be in these classes" and it call pg_wc_isalpha like this: for (i = 0; i <= UCHAR_MAX; i++) { if (pg_wc_isalpha((chr) i)) addchr(cv, (chr) i); } UCHAR_MAX is 255 I do not understand fully this algorithm of regular expressions, but i think cclass function also need fix. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] git.postgresql.org vs. REL8_1_STABLE
Joshua Tolley writes: > Am I the only one having problems building 8.1 from git? (Am I the only one > building 8.1 from git?) In a clean repository, I've checked out REL8_1_STABLE, > configured with only one argument, to set --prefix, and make gives me this: Still does not compile. I trying to compile 8.1 from git://git.postgresql.org/git/postgresql.git and can not do this. git clone git://git.postgresql.org/git/postgresql.git git checkout -b REL8_1_STABLE origin/REL8_1_STABLE ./configure --prefix=$HOME/inst/pg-dev --enable-nls --enable-debug --enable-depend --enable-cassert --enable-thread-safety --with-pgport=5433 --with-python --with-perl (configured ok) make ../../../../src/include/nodes/execnodes.h:23:29: error: nodes/tidbitmap.h: No such file or directory ../../../../src/include/nodes/execnodes.h:934: error: expected specifier-qualifier-list before 'TIDBitmap' ../../../../src/include/nodes/execnodes.h:959: error: expected specifier-qualifier-list before 'TIDBitmap' make[4]: *** [indexvalid.o] Error 1 postgresql-8.1.18.tar.bz2 from www.postgresql.org/ compiled successful. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
Tom Lane writes: > Sergey Burladyan writes: > > Oh, now problem with simple query: > > > 8.4.0 from Debian > > explain analyze select i from t where i >= 10 and i = 1; > > QUERY PLAN > > > > Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=0 > > loops=1) > >One-Time Filter: false > > Total runtime: 0.030 ms > > > CVS HEAD > > explain analyze select i from t where i >= 10 and i = 1; > > QUERY PLAN > > --- > > Seq Scan on t (cost=0.00..17030.00 rows=1 width=4) (actual > > time=449.651..449.651 rows=0 loops=1) > >Filter: ((i >= 10) AND (i = 1)) > > Total runtime: 449.726 ms > Hmm, that's got nothing to do with my recent patch, because there's no > appendrel anywhere. Are you sure you are using the same > constraint_exclusion setting in both cases? Oops, of course you are right, i have constraint_exclusion = on in Debian, but constraint_exclusion = partition by default in CVS HEAD %) Thanks for help, Tom ! -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
Oh, now problem with simple query: 8.4.0 from Debian explain analyze select i from t where i >= 10 and i = 1; QUERY PLAN Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.002..0.002 rows=0 loops=1) One-Time Filter: false Total runtime: 0.030 ms CVS HEAD explain analyze select i from t where i >= 10 and i = 1; QUERY PLAN --- Seq Scan on t (cost=0.00..17030.00 rows=1 width=4) (actual time=449.651..449.651 rows=0 loops=1) Filter: ((i >= 10) AND (i = 1)) Total runtime: 449.726 ms -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
Tom Lane writes: > As of CVS HEAD you get > > QUERY PLAN > > > Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.011..0.011 rows=0 > loops=1) >One-Time Filter: false > Total runtime: 0.179 ms > (3 rows) Thank you, Tom ! -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
Sergey Burladyan writes: > Thinks ! Th_a_nks ! :) -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] 8.4, One-Time Filter and subquery ( ... FROM function() union all ... )
I am testing some of my queries with 8.4 and find some performance decline. 8.4 always execute functions in this subquery, even if result do not need it. 8.3 correctly optimize this and do not execute this functions, here is example: create function foo() returns int language sql as $$ select pg_sleep(5); select 1 $$; PostgreSQL 8.3.7 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.3-5) 4.3.3 EXPLAIN ANALYZE select * from (select 1 as i, r from foo() r union all select 2, r from foo() r) as x where i = 3; QUERY PLAN Result (cost=0.00..0.54 rows=2 width=8) (actual time=0.006..0.006 rows=0 loops=1) -> Append (cost=0.00..0.54 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1) -> Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) One-Time Filter: false -> Function Scan on foo r (cost=0.00..0.26 rows=1 width=4) (never executed) -> Result (cost=0.00..0.26 rows=1 width=4) (actual time=0.001..0.001 rows=0 loops=1) One-Time Filter: false -> Function Scan on foo r (cost=0.00..0.26 rows=1 width=4) (never executed) Total runtime: 0.053 ms PostgreSQL 8.4.0 on i486-pc-linux-gnu, compiled by GCC gcc-4.3.real (Debian 4.3.3-13) 4.3.3, 32-bit EXPLAIN ANALYZE select * from (select 1 as i, r from foo() r union all select 2, r from foo() r) as x where i = 3; QUERY PLAN --- Result (cost=0.00..0.53 rows=2 width=36) (actual time=10007.352..10007.352 rows=0 loops=1) -> Append (cost=0.00..0.53 rows=2 width=36) (actual time=10007.351..10007.351 rows=0 loops=1) -> Function Scan on foo r (cost=0.00..0.26 rows=1 width=36) (actual time=5003.342..5003.342 rows=0 loops=1) Filter: (1 = 3) -> Function Scan on foo r (cost=0.00..0.26 rows=1 width=36) (actual time=5004.004..5004.004 rows=0 loops=1) Filter: (2 = 3) Total runtime: 10007.464 ms BTW, if i move function from FROM to SELECT - 8.4 correctly optimize it like 8.3: EXPLAIN ANALYZE select * from (select 1 as i, foo() as r union all select 2, foo()) as x where i = 3; QUERY PLAN Result (cost=0.00..0.54 rows=2 width=8) (actual time=0.005..0.005 rows=0 loops=1) -> Append (cost=0.00..0.54 rows=2 width=8) (actual time=0.003..0.003 rows=0 loops=1) -> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.001..0.001 rows=0 loops=1) One-Time Filter: false -> Result (cost=0.00..0.26 rows=1 width=0) (actual time=0.000..0.000 rows=0 loops=1) One-Time Filter: false Total runtime: 0.048 ms Is this expected behavior ? Can 8.4 optimize first query like 8.3 ? Thinks ! ps: no response in pgsql-performance so i try ask in pgsql-hackers -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Not quite a security hole in internal_in
Tom Lane writes: > This would be a serious security problem if it weren't for the fact that > nearly all internal-accepting functions in the backend are also marked > STRICT, and so they won't get called in this type of scenario. A query > to pg_proc shows that the only ones that aren't strict are > > regression=# select oid::regprocedure from pg_proc where 'internal'::regtype > = any (proargtypes) and not proisstrict; > oid > > array_agg_transfn(internal,anyelement) > array_agg_finalfn(internal) > domain_recv(internal,oid,integer) > (3 rows) > > The first two are new in 8.4, and the third has adequate defenses > already. So we don't have a security hole in any released version > right now. How about contrib/ ? I have this in my test 8.3.7 database: seb=> select oid::regprocedure from pg_proc where 'internal'::regtype = any (proargtypes) and not proisstrict; oid --- domain_recv(internal,oid,integer) utils_pg.gtrgm_same(utils_pg.gtrgm,utils_pg.gtrgm,internal) utils_pg.gin_extract_trgm(text,internal) utils_pg.gin_extract_trgm(text,internal,internal) utils_pg.gin_trgm_consistent(internal,internal,text) utils_pg.ghstore_compress(internal) utils_pg.ghstore_decompress(internal) utils_pg.ghstore_picksplit(internal,internal) utils_pg.ghstore_union(internal,internal) utils_pg.ghstore_same(internal,internal,internal) utils_pg.ghstore_consistent(internal,internal,integer) utils_pg.gin_extract_hstore(internal,internal) utils_pg.gin_extract_hstore_query(internal,internal,smallint) utils_pg.gin_consistent_hstore(internal,smallint,internal) utils_pg.gtrgm_consistent(utils_pg.gtrgm,internal,integer) utils_pg.gtrgm_compress(internal) utils_pg.gtrgm_decompress(internal) utils_pg.gtrgm_picksplit(internal,internal) utils_pg.gtrgm_union(bytea,internal) (19 rows) -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Improving the ngettext() patch
Tom Lane writes: > ereport(ERROR, > (errcode(ERRCODE_TOO_MANY_ARGUMENTS), > errmsg(ngettext("functions cannot have more than %d > argument", > "functions cannot have more than %d > arguments", > FUNC_MAX_ARGS), > FUNC_MAX_ARGS))); > > It seems extremely far-fetched that FUNC_MAX_ARGS would ever be small > enough that it would make any language's special cases kick in. Russian plural forms for 100, 101, 102 etc. is different, as for 0, 1, 2. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Solution of the file name problem of copy on windows.
Itagaki Takahiro writes: > There are some issues: > * Is it possible to determine the platform encoding? There is no platform encoding in linux. File name encoding depend on user locale, so different users can have different encoding of file name. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: XML only working in UTF-8 - Re: [HACKERS] 8.4 open items list
Tom Lane writes: > As near as I can tell, every place where you see an explicit cast > between char * and xmlChar * is probably broken. I think we ought > to approach this by refactoring to have all those conversions go > through subroutines, instead of blithely casting. There is another issue (from sql.ru forum): seb=> select xmlelement(name язык, xmlattributes('русский' as "значение")); xmlelement -- <язык значение="русский"/> xmlattributes always encode non-latin text as html entities server_encoding UTF8 client_encoding UTF8 This is strange behavior of libxml... i can't find documentation about this. http://www.xmlsoft.org/examples/testWriter.c use xmlTextWriterStartDocument and set output encoding with it. Without it, all non-latin nodes and it values written correctly (it is UTF-8), except attribute value, this is strange, imho. xmltype * xmlelement(XmlExprState *xmlExpr, ExprContext *econtext) from xml.c not use xmlTextWriterStartDocument and return html entities in attribute values. -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gettext, plural form and translation
Peter Eisentraut writes: > I have now committed a more extensive pluralization, but your case is > included > there. > > As for the ru.po file, please see http://babel.postgresql.org/. Great! I am translating 8.3 messages now. After this, i will go to HEAD. ps: By the way, when 8.4 will be released ? Have i time for translate HEAD before release will be ? I try to find 8.4 release date but it is 1st March 2009 %) -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gettext, plural form and translation
Sergey Burladyan writes: > gettext-plural-ru-test.patch: > - correct translation for "1 rows" message hmmm... encoding is broken... i post it again in gzip gettext-plural-ru-test.patch.gz Description: gettext-plural-ru-test.patch -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gettext, plural form and translation
ter[100]; total_records = opt->topt.prior_records + cont.nrows; ! if (total_records == 1) ! snprintf(default_footer, 100, _("(1 row)")); ! else ! snprintf(default_footer, 100, _("(%lu rows)"), total_records); printTableAddFooter(&cont, default_footer); } --- 2348,2354 char default_footer[100]; total_records = opt->topt.prior_records + cont.nrows; ! snprintf(default_footer, 100, _P("(%lu row)", "(%lu rows)", total_records), total_records); printTableAddFooter(&cont, default_footer); } *** a/src/include/c.h --- b/src/include/c.h *** *** 91,102 --- 91,108 #include #define _(x) gettext(x) + #ifdef HAVE_NGETTEXT + #define _P(s,p,n) ngettext(s,p,n) + #else + #define _P(s,p,n) ((n) == 1 ? (s) : (p)) + #endif #ifdef ENABLE_NLS #include #else #define gettext(x) (x) #define dgettext(d,x) (x) + #define ngettext(s,p,n) ((n) == 1 ? (s) : (p)) #endif /* *** a/src/include/pg_config.h.in --- b/src/include/pg_config.h.in *** *** 321,326 --- 321,329 /* Define to 1 if you have the header file. */ #undef HAVE_NETINET_TCP_H + /* Define to 1 if you have the ngettext function. */ + #undef HAVE_NGETTEXT + /* Define to 1 if you have the `on_exit' function. */ #undef HAVE_ON_EXIT *** a/src/include/pg_config.h.win32 --- b/src/include/pg_config.h.win32 *** *** 267,272 --- 267,275 /* Define to 1 if you have the header file. */ /* #undef HAVE_NETINET_TCP_H */ + /* Define to 1 if you have the 'ngettext' function. */ + #define HAVE_NGETTEXT 1 + /* Define to 1 if you have the `on_exit' function. */ /* #undef HAVE_ON_EXIT */ *** a/src/bin/psql/po/ru.po --- b/src/bin/psql/po/ru.po *** *** 19,25 msgid "" msgstr "" "Project-Id-Version: PostgreSQL 8.0\n" "POT-Creation-Date: 2005-01-17 19:06+\n" ! "PO-Revision-Date: 2005-01-17 15:36-0500\n" "Last-Translator: Serguei A. Mokhov \n" "Language-Team: pgsql-ru-general \n" "MIME-Version: 1.0\n" --- 19,25 msgstr "" "Project-Id-Version: PostgreSQL 8.0\n" "POT-Creation-Date: 2005-01-17 19:06+\n" ! "PO-Revision-Date: 2009-03-20 05:19+0300\n" "Last-Translator: Serguei A. Mokhov \n" "Language-Team: pgsql-ru-general \n" "MIME-Version: 1.0\n" *** *** 27,32 msgstr "" --- 27,34 "Content-Transfer-Encoding: 8bit\n" "X-Poedit-Language: Russian\n" "X-Poedit-Country: RUSSIAN FEDERATION\n" + "Plural-Forms: nplurals=3; plural=(n%10==1 && n%100!=11 ? 0 : n%10>=2 && n%" + "10<=4 && (n%100<10 || n%100>=20) ? 1 : 2);\n" #: command.c:116 msgid "Warning: This syntax is deprecated.\n" *** *** 930,943 msgstr " msgid "(No rows)\n" msgstr "(Нет записей)\n" ! #: print.c:1200 ! msgid "(1 row)" ! msgstr "(1 запись)" ! ! #: print.c:1202 #, c-format ! msgid "(%d rows)" ! msgstr "(записей: %d)" #: startup.c:138 #, c-format --- 932,944 msgid "(No rows)\n" msgstr "(Нет записей)\n" ! #: print.c:2351 #, c-format ! msgid "(%lu row)" ! msgid_plural "(%lu rows)" ! msgstr[0] "(%lu строка)" ! msgstr[1] "(%lu строки)" ! msgstr[2] "(%lu строк)" #: startup.c:138 #, c-format -- Sergey Burladyan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gettext, plural form and translation
Alvaro Herrera writes: > Sergey Burladyan escribió: > > Alvaro Herrera writes: > > > > Yes, the main reason is that it is not clear whether this is supported on > > > all > > > OS, or moreover that I believe it is not. So some allowances for that > > > will > > > probably have to be made. > > > > maybe build farm can help to test it ? > > Yes, I think we should implement it and see what happens with the > buildfarm. If we stand still and do nothing, we won't be any wiser. > > Care to submit a patch? i will try. -- Sergey Burladyan - -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] gettext, plural form and translation
Alvaro Herrera writes: > Sergey Burladyan escribió: > > gnu gettext have support for correct plural form translation > > (http://www.gnu.org/software/gettext/manual/html_node/Plural-forms.html), > > but postgresql does not use it. why not ? > > maybe it have some problem in some supported OS ? if not, can it > > implemented ? > > maybe someone already doing this ? > > > > ps: i try to translate psql message "(1 row)/(3 rows)" but can't do this > > correctly without plural form support. > You don't need plural forms in this example. We have three separate > messages, one for "(No rows)", another one for the singular "(1 row)" > and a third one for the plural "(N rows)". only one third message for plural is not enough for example for Russian. Russian have three plural forms, for example: 2 rows | 2 zapisy 3 rows | 3 zapisy 5 rows | 5 zapisey 11 rows | 11 zapisey 21 rows | 21 zapis etc > We avoid mixing plurals and singulars. Is this still a problem for you > somewhere? don't know :) i see this untranslated message (N rows) every day and try to translate it and find this issue. Peter Eisentraut writes: > On Wednesday 18 March 2009 11:21:03 Sergey Burladyan wrote: > > gnu gettext have support for correct plural form translation > > (http://www.gnu.org/software/gettext/manual/html_node/Plural-forms.html), > > but postgresql does not use it. why not ? > > maybe it have some problem in some supported OS ? > Yes, the main reason is that it is not clear whether this is supported on all > OS, or moreover that I believe it is not. So some allowances for that will > probably have to be made. maybe build farm can help to test it ? i think about "(N rows)" message today and find other solution. i do not essentially need this support for this message. because if i exchange position of word and number in translated message - it will have right pronunciation, something like: (rows: N) | (zapisey: N) | (записей: N) is it correct to add ':' in translated message ? i think colon is need here because sense part of message is not first... ps: but this change order is look like "hack" and look like "this program is not support correct spelling and use only one plural form" :) and original order with different plural form is more closely to original English text. also, how about other languages ? IMHO not all of it can have simple solution like change words order... i think support for ngettext() still must be implemented. if some problem with it will be found - it can be rejected, isn't it ? =) -- Sergey Burladyan - -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] gettext, plural form and translation
Hi, all. gnu gettext have support for correct plural form translation (http://www.gnu.org/software/gettext/manual/html_node/Plural-forms.html), but postgresql does not use it. why not ? maybe it have some problem in some supported OS ? if not, can it implemented ? maybe someone already doing this ? ps: i try to translate psql message "(1 row)/(3 rows)" but can't do this correctly without plural form support. need some work with source for implement it and xgettext params used for extract messages for http://babel.postgresql.org/ Thanks for comments ! -- Sergey Burladyan - -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers