Re: [HACKERS] XLByte* usage
On 16.12.2012 16:16, Andres Freund wrote: Now that XLRecPtr's are plain 64bit integers what are we supposed to use in code comparing and manipulating them? There already is plenty example of both, but I would like new code to go into one direction not two... I personally find direct comparisons/manipulations far easier to read than the XLByte* equivalents. I've still used XLByte* macros, but I agree that plain < = > are easier to read. +1 for using < = > in new code. - Heikki -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] is possible enforce \timing on from command line
Helllo I try to search simple solution and I didn't find anything. It is possible? Regards Pavel Stehule -- 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] WIP: index support for regexp search
On Sun, December 16, 2012 22:25, Alexander Korotkov wrote:
> trgm-regexp-0.8.patch.gz 22 k
Hi Alexander,
I gave this a quick try; the patch works when compiled for DEBUG, but crashes
as a
'speed'-compiled binary:
Compile for speed:
$ pg_config --configure
'--prefix=/home/aardvark/pg_stuff/pg_installations/pgsql.trgm_regex8'
'--with-pgport=6556'
'--enable-depend' '--with-openssl' '--with-perl' '--with-libxml'
$ psql
psql
(9.3devel-trgm_regex8-20121216_2336-c299477229559d4ee7db68720d86d3fb391db761)
Type "help" for help.
testdb=# explain analyze select txt from azjunk5 where txt ~ 'x[aeiouy]{2,5}q';
The connection to the server was lost. Attempting reset: Failed.
!> \q
log after such:
-
2012-12-17 09:31:02.337 CET 15801 LOG: server process (PID 16903) was
terminated by signal 11:
Segmentation fault
2012-12-17 09:31:02.337 CET 15801 DETAIL: Failed process was running: explain
analyze select txt
from azjunk5 where txt ~ 'x[aeiouy]{2,5}q';
2012-12-17 09:31:02.347 CET 15801 LOG: terminating any other active server
processes
2012-12-17 09:31:02.348 CET 17049 FATAL: the database system is in recovery
mode
2012-12-17 09:31:02.722 CET 15801 LOG: all server processes terminated;
reinitializing
2012-12-17 09:31:03.506 CET 17052 LOG: database system was interrupted; last
known up at
2012-12-17 09:26:00 CET
2012-12-17 09:31:03.693 CET 17052 LOG: database system was not properly shut
down; automatic
recovery in progress
2012-12-17 09:31:04.493 CET 17052 LOG: record with zero length at 2/7E3C7588
2012-12-17 09:31:04.494 CET 17052 LOG: redo is not required
2012-12-17 09:31:06.940 CET 15801 LOG: database system is ready to accept
connections
A debug-compile with below options runs OK (so far):
Compile for debug:
$ pg_config --configure
'--prefix=/home/aardvark/pg_stuff/pg_installations/pgsql.trgm_regex8b'
'--with-pgport=6560'
'--enable-depend' '--enable-cassert' '--enable-debug' '--with-openssl'
'--with-perl'
'--with-libxml'
which does show some speed gain, I think. When I have time I'll post
comparisons between HEAD,
versions 6, 7, 8. (BTW, is v6 still interesting?)
Thanks,
Erik Rijkers
--
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] is possible enforce \timing on from command line
postgres=# \timing Timing is on. postgres=# SELECT now(); now 2012-12-17 15:56:39.655+07 (1 row) Time: 11.429 ms postgres=# -- View this message in context: http://postgresql.1045698.n5.nabble.com/is-possible-enforce-timing-on-from-command-line-tp5736858p5736860.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- 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] XLByte* usage
On Mon, Dec 17, 2012 at 2:00 PM, Heikki Linnakangas wrote: > On 16.12.2012 16:16, Andres Freund wrote: >> >> Now that XLRecPtr's are plain 64bit integers what are we supposed to use >> in code comparing and manipulating them? There already is plenty example >> of both, but I would like new code to go into one direction not two... >> >> I personally find direct comparisons/manipulations far easier to read >> than the XLByte* equivalents. > > > I've still used XLByte* macros, but I agree that plain < = > are easier to > read. +1 for using < = > in new code. > Do we ever see us changing this from 64-bit integers to something else ? If so, a macro would be much better. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] is possible enforce \timing on from command line
On Mon, December 17, 2012 09:54, Pavel Stehule wrote: > Helllo > > I try to search simple solution and I didn't find anything. It is possible? > Perhaps not strictly 1 commandline but I often use this: echo '\timing on \\ select 1+2' | psql Erik Rijkers -- 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] WIP: index support for regexp search
Hi!
On Mon, Dec 17, 2012 at 12:54 PM, Erik Rijkers wrote:
> On Sun, December 16, 2012 22:25, Alexander Korotkov wrote:
>
> > trgm-regexp-0.8.patch.gz 22 k
>
> Hi Alexander,
>
> I gave this a quick try; the patch works when compiled for DEBUG, but
> crashes as a
> 'speed'-compiled binary:
>
> Compile for speed:
>
> $ pg_config --configure
> '--prefix=/home/aardvark/pg_stuff/pg_installations/pgsql.trgm_regex8'
> '--with-pgport=6556'
> '--enable-depend' '--with-openssl' '--with-perl' '--with-libxml'
>
> $ psql
> psql
> (9.3devel-trgm_regex8-20121216_2336-c299477229559d4ee7db68720d86d3fb391db761)
> Type "help" for help.
>
> testdb=# explain analyze select txt from azjunk5 where txt ~
> 'x[aeiouy]{2,5}q';
> The connection to the server was lost. Attempting reset: Failed.
> !> \q
>
Didn't reproduce it yet. Can you retry it with this line uncommented:
#define TRGM_REGEXP_DEBUG
Then we can see which stage it fails.
--
With best regards,
Alexander Korotkov.
Re: [HACKERS] proposal - assign result of query to psql variable
On Sun, Oct 28, 2012 at 7:16 AM, Pavel Stehule wrote: > Hello > > here is updated patch > > main change - it doesn't touch psql lexer - like Tom proposed > other points respect Phil's notices I reviewed v12 patch. It provides gset command which works consistently with other psql commands, such as \g and \set, and implementation seems reasonable, and follows other reviewer's comments properly. I think we can mark it as "ready for committer", once you have fixed some minor issues below. * Skipping leading blank in inner while loop of command.c seems unnecessary, because (IIUC) psql's scanner skips blanks. Is there any case that scanner returns token with leading/trailing blank? * ISTM that VARLIST_INITIAL can be removed. AFAIS it's same state as VARLIST_EXPECTED_COMMA_OR_IDENT. * I found some cosmetic flaw and typo. Please see attached patch for details. * How about pulling up codes for PGRES_TUPLES_OK case in StoreQueryResult to new static function, say StoreQueryTuple? It would make StoreQueryResult more similar to PrintQueryResult's style, and IMO it makes the code more readable. Regards, -- Shigeru HANADA gset_fix.patch Description: Binary data -- 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] XLByte* usage
On 17.12.2012 11:04, Pavan Deolasee wrote: On Mon, Dec 17, 2012 at 2:00 PM, Heikki Linnakangas wrote: On 16.12.2012 16:16, Andres Freund wrote: Now that XLRecPtr's are plain 64bit integers what are we supposed to use in code comparing and manipulating them? There already is plenty example of both, but I would like new code to go into one direction not two... I personally find direct comparisons/manipulations far easier to read than the XLByte* equivalents. I've still used XLByte* macros, but I agree that plain< => are easier to read. +1 for using< => in new code. Do we ever see us changing this from 64-bit integers to something else ? If so, a macro would be much better. I don't see us changing it again any time soon. Maybe in 20 years time people will start overflowing 2^64 bytes of WAL generated in the lifetime of a database, but I don't think we need to start preparing for that yet. - Heikki -- 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] SP-GiST for ranges based on 2d-mapping and quad-tree
On Fri, 2012-12-14 at 01:31 +0400, Alexander Korotkov wrote: > Hi! > Hi! I have attached a patch with some significant edits. * In your patch, there was still an inconsistency between the comment for bounds_adjacent and the code. I refactored it to ensure it always takes the upper bound as boundA, and the lower bound as boundB, so that it can invert the inclusivities to create A..B to match the comments. * In the consistent method, you were inverting upper to be a lower bound and lower to be an upper bound. I don't understand why (perhaps I did the first time I read the patch), so I removed it. * It looks like the comments for which1/2 were inconsistent with the code. I tried to fix that. * I significantly refactored the comments and code for the consistent method. I had trouble understanding the original comments, particularly around the edge cases. Please take a look and see if it still matches your algorithm properly. This patch is not intended to be a final version (I didn't analyze my code carefully), but just to show you what I mean and how I interpret what the code is trying to do. You don't need to use my refactorings, but if not, the comments in your version need more improvement so I can understand. I found it easier to reason in terms of horizontal and vertical lines, and which quadrants they crossed, and then work out the edge cases. I'm not sure if that reasoning was correct, but it seemed to make more sense to me. Regards, Jeff Davis range_spgist_adjacent-0.4A.patch.gz Description: GNU Zip compressed data -- 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] Switching timeline over streaming replication
On 15.12.2012 01:09, Josh Berkus wrote: Tested this on yesterday's snapshot. Worked great. Thanks for the testing! Now I wanna test a chain of cascading replicas ... how far can we chain these? There's no limit in theory. I tested with one master and two chained standbys myself. Give it a shot, I'm curious to hear how it works with a chain of a hundred standbys ;-). - Heikki -- 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] Set visibility map bit after HOT prune
On Sun, Dec 16, 2012 at 11:14 PM, Tom Lane wrote: > And I agree with him that your proposed > redefinition of the bit's meaning to avoid that is pretty horrid; > it's ugly, complicates the invariant quite a lot, and breaks some > existing usages of the bit. (slammed.. feels the pain) You definitely have better intuitions and understanding of the system than me and I've to be really shameful to defend my idea which was out of the hat anyways, but let me try nevertheless at the risk of being slammed again :-) So whats the current meaning of the VM bit ? If set, it tells us that all tuples in the page are visible to all current and future transactions. But thats not entirely true. Otherwise why wouldn't we mark a page all-visible if it contains a DEAD line pointer ? Of course, we don't do that to support index only scans and make sure that vacuum picks up such page. So would I be too wrong if I take liberty to read VM bit as: if set, every index pointer to the heap page is for sure pointing to *only* all-visible tuple. I also consider vacuum as an activity that is needed because we can't clean up dead line pointers in the heap without first removing the index pointers. So visibility maps go hand-in-hand with indexes. In fact, if a table does not have any index, I am not sure if we even need a VM for that table, especially if we can teach HOT prune to record free space with FSM. My half-cooked idea extends that and says: if VM bit is set, every index pointer to the heap page is pointing to either a all-visible tuple or a valid HOT-chain that has all-visible tuple at the end (aborted HOT tuples at the end are not counted as part of a valid chain). OTOH the page-level bit is set if every tuple in the heap page is all-visible. DEAD line pointers are not counted while arriving at page level bit. So in this new scheme of things, there is a loose correspondence between these two. VM bit is useful for index-only scans and vacuum optimisations while page-level bit has limited use of optimising heap scans. Andres probably said the same thing. Jeff Davis is already talking about removing the page-level bit on other threads, so I don't see much problem breaking a 1-to-1 mapping with the VM bit and the page-level bit. Having said all of this, I am not insisting on this unless we see a value in it. And there could be when you reach a steady state so that almost every update is a HOT update. Today you will still need periodic vacuums just so that index-only scans work. Of course, you talked about other code paths to set all-visible bits. > > If we decide that we don't want to restrict pruning like that, then > this patch probably has merit. But we can't evaluate the two issues > independently. > Fair enough. If someone starts actively working on moving HOT prune logic to the UPDATE path, I'll withdraw this patch. But any change to the HOT prune logic will require extensive and long duration testing to prove its value. After all, we spent hours testing it when we wrote it and had seen how small changes can cause drop in performance. If nobody is volunteering to do this change at the moment, I feel that the patch has value, as you yourself noted, and should be considered on its merits, as things stand today and not based on things that we might do in future. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Switching timeline over streaming replication
On 17 December 2012 12:07, Heikki Linnakangas wrote: > On 15.12.2012 01:09, Josh Berkus wrote: > >> Tested this on yesterday's snapshot. Worked great. >> > > Thanks for the testing! > > > Now I wanna test a chain of cascading replicas ... how far can we chain >> these? >> > > There's no limit in theory. I tested with one master and two chained > standbys myself. Give it a shot, I'm curious to hear how it works with a > chain of a hundred standbys ;-). > I just set up 120 chained standbys, and for some reason I'm seeing these errors: LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: record with zero length at 0/301EC10 LOG: fetching timeline history file for timeline 2 from primary server LOG: restarted WAL streaming at 0/300 on timeline 1 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 1 LOG: new target timeline is 2 LOG: restarted WAL streaming at 0/300 on timeline 2 LOG: replication terminated by primary server DETAIL: End of WAL reached on timeline 2 FATAL: error reading result of streaming command: ERROR: requested WAL segment 00020003 has already been removed ERROR: requested WAL segment 00020003 has already been removed LOG: started streaming WAL from primary at 0/300 on timeline 2 ERROR: requested WAL segment 00020003 has already been removed The "End of WAL reached on timeline 2" appears on all standbys except the one streaming directly from the primary. However, changes continue to cascade to all standbys right to the end of the chain (it takes several minutes to propagate however). -- Thom
Re: [HACKERS] Makefiles don't seem to remember to rebuild everything anymore
On 12/15/12 11:23 AM, Tom Lane wrote: > =?iso-8859-15?q?C=E9dric_Villemain?= writes: >> Le vendredi 14 décembre 2012 23:02:11, Tom Lane a écrit : >>> $ rm gram.o >>> rm: remove regular file `gram.o'? y >>> $ make >>> make: Nothing to be done for `all'. >>> >>> WTF? > >> A previous patch changed the ".SECONDARY" from an if() section to the main >> section of src/Makefile.global.in, > > Hm. I should have made clear that this isn't specific to gram.o --- > I also tried "rm analyze.o" and that didn't trigger any action either. > So it doesn't seem to be a case of the bison rule chain being > specifically at fault. > > I do suspect that this was triggered by some fairly recent makefile > change, though, because I don't recall having seen anything as odd > as this until recently. I can see it in 9.1 but not in 9.0, but I haven't had the time to pinpoint the exact change. Although it's a bit odd, it's not really a problem, I think. If you want to rebuild analyze.o, you should write "make analyze.o". If you want to rebuild postgres, run make in src/backend, and analyze.o (or whatever) will be rebuilt. -- 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] Makefiles don't seem to remember to rebuild everything anymore
On Mon, Dec 17, 2012 at 7:16 PM, Peter Eisentraut wrote: > If you > want to rebuild postgres, run make in src/backend, and analyze.o (or > whatever) will be rebuilt. FWIW for me, make in src/backend fails with this: i686-apple-darwin11-llvm-gcc-4.2: parser/analyze.o: No such file or directory make: *** [postgres] Error 1 Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_basebackup from cascading standby after timeline switch
pg_basebackup -x is supposed to include all the required WAL files in the backup, so that you have everything needed to restore a consistent database. However, it's not including the timeline history files. Usually that's not a problem because normally you don't need to follow any old timelines when restoring, but there is one scenario where it causes a failure to restore: Create a master, a standby, and a cascading standby. Kill the master server, promote the standby to become new master, bumping the timeline. After the cascading standby has followed the timeline switch (either through the archive, which also works on 9.2, or directly via streaming replication which only works on 9.3devel), take a base backup from the cascading standby using pg_basebackup -x. When you try to start the server from the new backup (without setting up a restore_command or streaming replication), you get an error about "unexpected timeline ID 1 in log segment ..." C 2012-12-17 15:55:25.732 EET 534 LOG: database system was interrupted while in recovery at log time 2012-12-17 15:55:15 EET C 2012-12-17 15:55:25.732 EET 534 HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. C 2012-12-17 15:55:25.732 EET 534 LOG: creating missing WAL directory "pg_xlog/archive_status" C 2012-12-17 15:55:25.732 EET 534 LOG: unexpected timeline ID 1 in log segment 00020003, offset 0 C 2012-12-17 15:55:25.732 EET 534 LOG: invalid checkpoint record C 2012-12-17 15:55:25.733 EET 534 FATAL: could not locate required checkpoint record C 2012-12-17 15:55:25.733 EET 534 HINT: If you are not restoring from a backup, try removing the file "/home/heikki/pgsql.master/data-standbyC/backup_label". C 2012-12-17 15:55:25.733 EET 533 LOG: startup process (PID 534) exited with exit code 1 C 2012-12-17 15:55:25.733 EET 533 LOG: aborting startup due to startup process failure The timeline was bumped within the log segment 00020003, so the beginning of the file uses timeline 1, up to the checkpoint record that changes the timeline. Normally, recovery accepts that because timeline 1 is an ancestor of timeline 2, but because the backup does not include the timelime history file, it does not know that. This does not happen if you run pg_basebackup against the master server, because in the master it forces an xlog switch, which ensures that the new xlog file only contains pages with the latest timeline ID. There's even comments in pg_start_backup explaining that that's the reason for the xlog switch: /* * Force an XLOG file switch before the checkpoint, to ensure that the * WAL segment the checkpoint is written to doesn't contain pages with * old timeline IDs. That would otherwise happen if you called * pg_start_backup() right after restoring from a PITR archive: the * first WAL segment containing the startup checkpoint has pages in * the beginning with the old timeline ID. That can cause trouble at * recovery: we won't have a history file covering the old timeline if * pg_xlog directory was not included in the base backup and the WAL * archive was cleared too before starting the backup. * * This also ensures that we have emitted a WAL page header that has * XLP_BKP_REMOVABLE off before we emit the checkpoint record. * Therefore, if a WAL archiver (such as pglesslog) is trying to * compress out removable backup blocks, it won't remove any that * occur after this point. * * During recovery, we skip forcing XLOG file switch, which means that * the backup taken during recovery is not available for the special * recovery case described above. */ if (!backup_started_in_recovery) RequestXLogSwitch(); I'm not happy with the fact that we just ignore the problem in a backup taken from a standby, silently giving the user a backup that won't start up. Why not include the timeline history file in the backup? That seems like a good idea regardless of this issue. I also wonder if pg_basebackup should include *all* timeline history files in the backup, not just the latest one strictly required to restore. They're fairly small, so our approach has generally been to try to include them all in the archive, and not try to prune them, so the same might make sense here. - Heikki -- 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] Makefiles don't seem to remember to rebuild everything anymore
On 12/17/2012 08:46 AM, Peter Eisentraut wrote: On 12/15/12 11:23 AM, Tom Lane wrote: =?iso-8859-15?q?C=E9dric_Villemain?= writes: Le vendredi 14 décembre 2012 23:02:11, Tom Lane a écrit : $ rm gram.o rm: remove regular file `gram.o'? y $ make make: Nothing to be done for `all'. WTF? A previous patch changed the ".SECONDARY" from an if() section to the main section of src/Makefile.global.in, Although it's a bit odd, it's not really a problem, I think. If you want to rebuild analyze.o, you should write "make analyze.o". If you want to rebuild postgres, run make in src/backend, and analyze.o (or whatever) will be rebuilt. That's a pretty nasty violation of the POLA. If our leading developer thinks something about our build process is a problem, it's a problem. cheers andrew -- 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] Enabling Checksums
Jeff Davis writes: >> -A relation name >> -Corruption type (an entry from this list) >> -How many blocks to touch >> >> I'll just loop based on the count, randomly selecting a block each time >> and messing with it in that way. For the messing with it part, did you consider zzuf? http://caca.zoy.org/wiki/zzuf > Does it make sense to have a separate executable (pg_corrupt) just for > corrupting the data as a test? Or should it be part of a > corruption-testing harness (pg_corruptiontester?), that introduces the > corruption and then verifies that it's properly detected? Maybe we need our own zzuf implementation, though. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- 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] Makefiles don't seem to remember to rebuild everything anymore
Pavan Deolasee writes: > On Mon, Dec 17, 2012 at 7:16 PM, Peter Eisentraut wrote: >> If you >> want to rebuild postgres, run make in src/backend, and analyze.o (or >> whatever) will be rebuilt. > FWIW for me, make in src/backend fails with this: > i686-apple-darwin11-llvm-gcc-4.2: parser/analyze.o: No such file or directory > make: *** [postgres] Error 1 Yes, I see the same, on both make 3.81 and 3.82. (BTW, you have to remove the postgres executable, else make won't even try to rebuild it.) IMO this is broken. It did not use to work like that, either. I'm familiar with the behavior here because I frequently recompile individual .o files (eg, to build them with nondefault -O settings) and am in the habit of rm'ing the .o file afterwards to let things go back to normal. They no longer do. Having seen this, I now think .SECONDARY is broken across the board. A case comparable to .o files with nondefault CFLAGS could be that one manually edits gram.c (say, to inject some debugging code), rebuilds, and later wants to revert to a standard build. Formerly you'd just rm gram.c and things would be good. Now, it's quite unobvious what would need to be removed to convince make to do what it's contracted to do. If you didn't watch the rebuild step very closely (or, heaven forbid, used make -s and didn't see anything), you could waste a lot of time before realizing that make hadn't done what it's supposed to. 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
Re: [HACKERS] pg_basebackup from cascading standby after timeline switch
Heikki Linnakangas writes: > I'm not happy with the fact that we just ignore the problem in a backup > taken from a standby, silently giving the user a backup that won't start > up. Why not include the timeline history file in the backup? +1. I was not aware that we weren't doing that --- it seems pretty foolish, especially since as you say they're tiny. 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
Re: [HACKERS] Makefiles don't seem to remember to rebuild everything anymore
Le lundi 17 décembre 2012 16:45:16, Tom Lane a écrit : > Pavan Deolasee writes: > > On Mon, Dec 17, 2012 at 7:16 PM, Peter Eisentraut wrote: > >> If you > >> want to rebuild postgres, run make in src/backend, and analyze.o (or > >> whatever) will be rebuilt. > > > > FWIW for me, make in src/backend fails with this: > > i686-apple-darwin11-llvm-gcc-4.2: parser/analyze.o: No such file or > > directory make: *** [postgres] Error 1 > > Yes, I see the same, on both make 3.81 and 3.82. (BTW, you have to > remove the postgres executable, else make won't even try to rebuild it.) > > IMO this is broken. It did not use to work like that, either. > I'm familiar with the behavior here because I frequently recompile > individual .o files (eg, to build them with nondefault -O settings) > and am in the habit of rm'ing the .o file afterwards to let things > go back to normal. They no longer do. > > Having seen this, I now think .SECONDARY is broken across the board. > A case comparable to .o files with nondefault CFLAGS could be that > one manually edits gram.c (say, to inject some debugging code), > rebuilds, and later wants to revert to a standard build. Formerly > you'd just rm gram.c and things would be good. Now, it's quite > unobvious what would need to be removed to convince make to do what > it's contracted to do. If you didn't watch the rebuild step very > closely (or, heaven forbid, used make -s and didn't see anything), > you could waste a lot of time before realizing that make hadn't > done what it's supposed to. make does what it is supposed to do here IIUC. Then we may just want to move the .SECONDARY in an ifcase again (the .SECONDARY is still interesting) What was the consensus when Peter did the change ? -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Makefiles don't seem to remember to rebuild everything anymore
=?iso-8859-15?q?C=E9dric_Villemain?= writes: > Le lundi 17 décembre 2012 16:45:16, Tom Lane a écrit : >> Having seen this, I now think .SECONDARY is broken across the board. > make does what it is supposed to do here IIUC. Well, it's behaving as documented, but what this means is we need to be very wary of what contexts we can use .SECONDARY in. I'd just as soon try to avoid using it at all --- my example with hand-edited gram.c points out that even rather short dependency chains can have unexpected misbehaviors if the intermediate files are marked .SECONDARY. > What was the consensus when Peter did the change ? It was an experiment, nothing more; or at least that's how I saw it. 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
Re: [HACKERS] Makefiles don't seem to remember to rebuild everything anymore
Le lundi 17 décembre 2012 15:29:09, Andrew Dunstan a écrit : > On 12/17/2012 08:46 AM, Peter Eisentraut wrote: > > On 12/15/12 11:23 AM, Tom Lane wrote: > >> =?iso-8859-15?q?C=E9dric_Villemain?= writes: > >>> Le vendredi 14 décembre 2012 23:02:11, Tom Lane a écrit : > $ rm gram.o > rm: remove regular file `gram.o'? y > $ make > make: Nothing to be done for `all'. > > WTF? > >>> > >>> A previous patch changed the ".SECONDARY" from an if() section to the > >>> main section of src/Makefile.global.in, > > > > Although it's a bit odd, it's not really a problem, I think. If you > > want to rebuild analyze.o, you should write "make analyze.o". If you > > want to rebuild postgres, run make in src/backend, and analyze.o (or > > whatever) will be rebuilt. > > That's a pretty nasty violation of the POLA. If our leading developer > thinks something about our build process is a problem, it's a problem. That's not so obvious. The current behavior is expected by .SECONDARY. In other words, if I just 'touch rewriteDefine.c' then rewriteDefine.o will be rebuilt by make (as expected). $ touch rewriteDefine.c $ make gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno- strict-aliasing -fwrapv -fexcess-precision=standard -I../../../src/include - D_GNU_SOURCE -c -o rewriteDefine.o rewriteDefine.c touch objfiles.txt It is maybe better to do a special case when you want to force rebuild, but in a more intuitive way than specifying each file you want to rebuild. Like that ?! : diff --git a/src/Makefile.global.in b/src/Makefile.global.in index 9cc14da..8597792 100644 --- a/src/Makefile.global.in +++ b/src/Makefile.global.in @@ -31,8 +31,10 @@ all: # started to update the file. .DELETE_ON_ERROR: +ifndef NOTSECONDARY # Never delete any intermediate files automatically. .SECONDARY: +endif $ rm rewriteDefine.o $ make nothing to do ... $ NOTSECONDARY=1 make gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno- strict-aliasing -fwrapv -fexcess-precision=standard -I../../../src/include - D_GNU_SOURCE -c -o rewriteDefine.o rewriteDefine.c touch objfiles.txt -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] pg_basebackup from cascading standby after timeline switch
On Mon, Dec 17, 2012 at 5:19 PM, Tom Lane wrote: > Heikki Linnakangas writes: >> I'm not happy with the fact that we just ignore the problem in a backup >> taken from a standby, silently giving the user a backup that won't start >> up. Why not include the timeline history file in the backup? > > +1. I was not aware that we weren't doing that --- it seems pretty > foolish, especially since as you say they're tiny. Yeah, +1. That should probably have been a part of the whole "basebackup from slave" patch, so it can probably be considered a back-patchable bugfix in itself, no? -- Magnus Hagander Me: http://www.hagander.net/ Work: http://www.redpill-linpro.com/ -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] configure.in and setproctitle/optreset problem
We are regularly teaching PostgreSQL courses at linuxhotel.de. Starting from the course in November, PG doesn't compile anymore on their default Debian Squeeze install laptops they hand out to the participants. After ./configure && make, the error looks like this: postmaster/postmaster.o: In function `PostmasterMain': postmaster.c:(.text+0x40e2): undefined reference to `optreset' tcop/postgres.o: In function `process_postgres_switches': postgres.c:(.text+0x168c): undefined reference to `optreset' utils/misc/ps_status.o: In function `set_ps_display': ps_status.c:(.text+0xba): undefined reference to `setproctitle' collect2: error: ld returned 1 exit status make[2]: *** [postgres] Fehler 1 make[2]: Leaving directory `/home/cbe/projects/postgresql/postgresql/src/backend' This is 9.2.2, but I've seen 9.1.6 failing in the same way. The package set installed is a desktop install, plus what Debian considers to be build dependencies of postgresql-9.2, that is, libedit-dev is installed, but libreadline-dev isn't. $ egrep 'edit|readline|setproc|optreset' configure.out checking for library containing setproctitle... no checking for library containing readline... -ledit checking editline/readline.h usability... yes checking editline/readline.h presence... yes checking for editline/readline.h... yes checking editline/history.h usability... yes checking editline/history.h presence... yes checking for editline/history.h... yes checking for setproctitle... yes checking for optreset... yes I have no clue why no one else has seen this bug before, but the reason for the error seems to be that configure is invoking the setproctitle test including -ledit. libedit.so is linked to libbsd.so, which in turn contains setproctitle(), so HAVE_SETPROCTITLE is set even this is Linux, not BSD. configure.in already contains a workaround for -ledit containing a strlcpy() version, so I'd propose the attached patch to extend that workaround to setproctitle. (Patch for 9.2's configure.in, but the same moving of the two code blocks should work for head as well.) optreset seems to be a similar problem, but I'm not yet sure the patch really fixes it. (I've kind of got lost in too many configure runs...) An alternative fix would be to move the __linux__ test in front of the HAVE_SETPROCTITLE test in src/backend/utils/misc/ps_status.c. Why is -ledit (or -lreadline) being passed to the function tests anyway? Removing that would fix this as well, I guess. (I can provide more verbose configure output on request.) Christoph -- c...@df7cb.de | http://www.df7cb.de/ diff --git a/configure.in b/configure.in new file mode 100644 index af99340..2f019ea *** a/configure.in --- b/configure.in *** PGAC_VAR_INT_TIMEZONE *** 1197,1202 --- 1197,1207 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG + # Some versions of libedit contain strlcpy(); so disregard that library while + # checking for these standard libc functions. + pgac_save_LIBS="$LIBS" + LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` + AC_CHECK_FUNCS([cbrt dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll pstat readlink setproctitle setsid sigprocmask symlink towlower utime utimes waitpid wcstombs wcstombs_l]) AC_REPLACE_FUNCS(fseeko) *** else *** 1311,1321 AC_CHECK_FUNCS([fpclass fp_class fp_class_d class], [break]) fi - # Some versions of libedit contain strlcpy(); so disregard that library while - # checking for these standard libc functions. - pgac_save_LIBS="$LIBS" - LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` - AC_REPLACE_FUNCS([crypt fls getopt getrusage inet_aton random rint srandom strerror strlcat strlcpy]) case $host_os in --- 1316,1321 *** case $host_os in *** 1334,1341 esac - LIBS="$pgac_save_LIBS" - # System's version of getaddrinfo(), if any, may be used only if we found # a definition for struct addrinfo; see notes in src/include/getaddrinfo.h. # (Note: the AC_REPLACE_FUNCS probe fails on Windows, where the available --- 1334,1339 *** if test x"$pgac_cv_gcc_int_atomics" = x" *** 1446,1451 --- 1444,1451 AC_DEFINE(HAVE_GCC_INT_ATOMICS, 1, [Define to 1 if you have __sync_lock_test_and_set(int *) and friends.]) fi + LIBS="$pgac_save_LIBS" + # # Pthreads -- 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] Makefiles don't seem to remember to rebuild everything anymore
Le lundi 17 décembre 2012 17:42:04, Tom Lane a écrit : > =?iso-8859-15?q?C=E9dric_Villemain?= writes: > > Le lundi 17 décembre 2012 16:45:16, Tom Lane a écrit : > >> Having seen this, I now think .SECONDARY is broken across the board. > > > > make does what it is supposed to do here IIUC. > > Well, it's behaving as documented, but what this means is we need to be > very wary of what contexts we can use .SECONDARY in. I'd just as soon > try to avoid using it at all --- my example with hand-edited gram.c > points out that even rather short dependency chains can have unexpected > misbehaviors if the intermediate files are marked .SECONDARY. "touch gram.c" then "make" will build again the affected files. Which is different from "rm gram.c"... A "touch gram.y" will trigger a rebuild of "gram.c" (and "gram.o"). Maybe Makefile has an option to be a little bit more conservative and rebuild any removed intermediate file even if not required. > > What was the consensus when Peter did the change ? > > It was an experiment, nothing more; or at least that's how I saw it. ok. -- Cédric Villemain +33 (0)6 20 30 22 52 http://2ndQuadrant.fr/ PostgreSQL: Support 24x7 - Développement, Expertise et Formation signature.asc Description: This is a digitally signed message part.
Re: [HACKERS] Makefiles don't seem to remember to rebuild everything anymore
On Mon, Dec 17, 2012 at 10:19 PM, Cédric Villemain wrote: > > That's not so obvious. > The current behavior is expected by .SECONDARY. > In other words, if I just 'touch rewriteDefine.c' then rewriteDefine.o will be > rebuilt by make (as expected). > > $ touch rewriteDefine.c > $ make > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- > statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno- > strict-aliasing -fwrapv -fexcess-precision=standard -I../../../src/include - > D_GNU_SOURCE -c -o rewriteDefine.o rewriteDefine.c Isn't that something very basic make is supposed to do anyways ? > touch objfiles.txt > > It is maybe better to do a special case when you want to force rebuild, but in > a more intuitive way than specifying each file you want to rebuild. > I don't understand what are we trying to achieve by doing what you did below. ISTM that the real problem is .SECONDARY without any prerequisites. If at all there are any files that we want to treat as secondary and not remove at the end, shouldn't we add them as prerequisites to this special target ? At least, that what I get reading man pages and samples. Thanks, Pavan > Like that ?! : > > > diff --git a/src/Makefile.global.in b/src/Makefile.global.in > index 9cc14da..8597792 100644 > --- a/src/Makefile.global.in > +++ b/src/Makefile.global.in > @@ -31,8 +31,10 @@ all: > # started to update the file. > .DELETE_ON_ERROR: > > +ifndef NOTSECONDARY > # Never delete any intermediate files automatically. > .SECONDARY: > +endif > > > $ rm rewriteDefine.o > $ make > nothing to do ... > $ NOTSECONDARY=1 make > gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after- > statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno- > strict-aliasing -fwrapv -fexcess-precision=standard -I../../../src/include - > D_GNU_SOURCE -c -o rewriteDefine.o rewriteDefine.c > touch objfiles.txt > > > -- > Cédric Villemain +33 (0)6 20 30 22 52 > http://2ndQuadrant.fr/ > PostgreSQL: Support 24x7 - Développement, Expertise et Formation -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Makefiles don't seem to remember to rebuild everything anymore
=?iso-8859-15?q?C=E9dric_Villemain?= writes: > Le lundi 17 décembre 2012 17:42:04, Tom Lane a écrit : >> Well, it's behaving as documented, but what this means is we need to be >> very wary of what contexts we can use .SECONDARY in. I'd just as soon >> try to avoid using it at all --- my example with hand-edited gram.c >> points out that even rather short dependency chains can have unexpected >> misbehaviors if the intermediate files are marked .SECONDARY. > "touch gram.c" then "make" will build again the affected files. Yes ... but still with the modified contents of gram.c, so this doesn't solve the problem I described. I want make to regenerate the file in question, not (only) its derived files. 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
[HACKERS] Error restoring from a base backup taken from standby
(This is different from the other issue related to timeline switches I
just posted about. There's no timeline switch involved in this one.)
If you do "pg_basebackup -x" against a standby server, in some
circumstances the backup fails to restore with error like this:
C 2012-12-17 19:09:44.042 EET 7832 LOG: database system was not
properly shut down; automatic recovery in progress
C 2012-12-17 19:09:44.091 EET 7832 LOG: record with zero length at
0/1764F48
C 2012-12-17 19:09:44.091 EET 7832 LOG: redo is not required
C 2012-12-17 19:09:44.091 EET 7832 FATAL: WAL ends before end of online
backup
C 2012-12-17 19:09:44.091 EET 7832 HINT: All WAL generated while online
backup was taken must be available at recovery.
C 2012-12-17 19:09:44.092 EET 7831 LOG: startup process (PID 7832)
exited with exit code 1
C 2012-12-17 19:09:44.092 EET 7831 LOG: aborting startup due to startup
process failure
I spotted this bug while reading the code, and it took me quite a while
to actually construct a test case to reproduce the bug, so let me begin
by discussing the code where the bug is. You get the above error, "WAL
ends before end of online backup", when you reach the end of WAL before
reaching the backupEndPoint stored in the control file, which originally
comes from the backup_label file. backupEndPoint is only used in a base
backup taken from a standby, in a base backup taken from the master, the
end-of-backup WAL record is used instead to mark the end of backup. In
the xlog redo loop, after replaying each record, we check if we've just
reached backupEndPoint, and clear it from the control file if we have.
Now the problem is, if there are no WAL records after the checkpoint
redo point, we never even enter the redo loop, so backupEndPoint is not
cleared even though it's reached immediately after reading the initial
checkpoint record.
To deal with the similar situation wrt. reaching consistency for hot
standby purposes, we call CheckRecoveryConsistency() before the redo
loop. The straightforward fix is to copy-paste the check for
backupEndPoint to just before the redo loop, next to the
CheckRecoveryConsistency() call. Even better, I think we should move the
backupEndPoint check into CheckRecoveryConsistency(). It's already
responsible for keeping track of whether minRecoveryPoint has been
reached, so it seems like a good idea to do this check there as well.
Attached is a patch for that (for 9.2), as well as a script I used to
reproduce the bug. The script is a bit messy, and requires tweaking the
paths at the top. Anyone spot a problem with this?
- Heikki
diff --git a/src/backend/access/transam/xlog.c b/src/backend/access/transam/xlog.c
index 702487b..1d1e47b 100644
--- a/src/backend/access/transam/xlog.c
+++ b/src/backend/access/transam/xlog.c
@@ -6776,27 +6776,6 @@ StartupXLOG(void)
/* Pop the error context stack */
error_context_stack = errcontext.previous;
-if (!XLogRecPtrIsInvalid(ControlFile->backupEndPoint) &&
- XLByteLE(ControlFile->backupEndPoint, EndRecPtr))
-{
- /*
- * We have reached the end of base backup, the point where
- * the minimum recovery point in pg_control indicates. The
- * data on disk is now consistent. Reset backupStartPoint
- * and backupEndPoint.
- */
- elog(DEBUG1, "end of backup reached");
-
- LWLockAcquire(ControlFileLock, LW_EXCLUSIVE);
-
- MemSet(&ControlFile->backupStartPoint, 0, sizeof(XLogRecPtr));
- MemSet(&ControlFile->backupEndPoint, 0, sizeof(XLogRecPtr));
- ControlFile->backupEndRequired = false;
- UpdateControlFile();
-
- LWLockRelease(ControlFileLock);
-}
-
/*
* Update lastReplayedEndRecPtr after this record has been
* successfully replayed.
@@ -7177,6 +7156,34 @@ CheckRecoveryConsistency(void)
return;
/*
+ * Have we reached the point where our base backup was completed?
+ */
+ if (!XLogRecPtrIsInvalid(ControlFile->backupEndPoint) &&
+ XLByteLE(ControlFile->backupEndPoint, EndRecPtr))
+ {
+ /*
+ * We have reached the end of base backup, as indicated by pg_control.
+ * The data on disk is now consistent. Reset backupStartPoint and
+ * backupEndPoint, and update minRecoveryPoint to make sure we don't
+ * allow starting up at an earlier point even if recovery is stopped
+ * and restarted soon after this.
+ */
+ elog(DEBUG1, "end of backup reached");
+
+ LWLockAcquire(ControlFileLock, LW_EXCLUSIVE);
+
+ if (XLByteLT(ControlFile->minRecoveryPoint, EndRecPtr))
+ ControlFile->minRecoveryPoint = EndRecPtr;
+
+ MemSet(&ControlFile->backupStartPoint, 0, sizeof(XLogRecPtr));
+ MemSet(&ControlFile->backupEndPoint, 0, sizeof(XLogRecPtr));
+ ControlFile->backupEndRequired = false;
+ UpdateControlFile();
+
+ LWLockRelease(ControlFileLock);
+ }
+
+ /*
* Have we passed our safe starting point? Note that minRecoveryPoint
* is known to be incorrectly set if ControlFile->backupEndRequired,
* until the XLOG_BACK
Re: [HACKERS] XLByte* usage
Heikki Linnakangas writes: > On 17.12.2012 11:04, Pavan Deolasee wrote: >> On Mon, Dec 17, 2012 at 2:00 PM, Heikki Linnakangas >> wrote: >>> I've still used XLByte* macros, but I agree that plain <=> are easier to >>> read. +1 for using <=> in new code. >> Do we ever see us changing this from 64-bit integers to something else >> ? If so, a macro would be much better. > I don't see us changing it again any time soon. Maybe in 20 years time > people will start overflowing 2^64 bytes of WAL generated in the > lifetime of a database, but I don't think we need to start preparing for > that yet. Note that to get to 2^64 in twenty years, an installation would have had to have generated an average of 29GB of WAL per second, 24x7 for the entire twenty years, with never a dump-and-reload. We're still a few orders of magnitude away from needing to think about this. But, if the day ever comes when 64 bits doesn't seem like enough, I bet we'd move to 128-bit integers, which will surely be available on all platforms by then. So +1 for using plain comparisons --- in fact, I'd vote for running around and ripping out the macros altogether. I had already been thinking of fixing the places that are still using memset to initialize XLRecPtrs to "invalid". 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
Re: [HACKERS] XLByte* usage
On 2012-12-17 12:47:41 -0500, Tom Lane wrote: > Heikki Linnakangas writes: > > On 17.12.2012 11:04, Pavan Deolasee wrote: > >> On Mon, Dec 17, 2012 at 2:00 PM, Heikki Linnakangas > >> wrote: > >>> I've still used XLByte* macros, but I agree that plain <=> are easier to > >>> read. +1 for using <=> in new code. > > >> Do we ever see us changing this from 64-bit integers to something else > >> ? If so, a macro would be much better. > > > I don't see us changing it again any time soon. Maybe in 20 years time > > people will start overflowing 2^64 bytes of WAL generated in the > > lifetime of a database, but I don't think we need to start preparing for > > that yet. > > Note that to get to 2^64 in twenty years, an installation would have had > to have generated an average of 29GB of WAL per second, 24x7 for the > entire twenty years, with never a dump-and-reload. We're still a few > orders of magnitude away from needing to think about this. Agreed. And it seems achieving such rates would require architectural changes that would make manually changing all those comparisons the tiniest problem. > But, if the day ever comes when 64 bits doesn't seem like enough, I bet > we'd move to 128-bit integers, which will surely be available on all > platforms by then. So +1 for using plain comparisons --- in fact, I'd > vote for running around and ripping out the macros altogether. I had > already been thinking of fixing the places that are still using memset > to initialize XLRecPtrs to "invalid". I thought about that and had guessed you would be against it because it would cause useless diversion of the branches? Otherwise I am all for it. Greetings, Andres Freund -- Andres Freund http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] XLByte* usage
On Mon, Dec 17, 2012 at 11:17 PM, Tom Lane wrote: > Heikki Linnakangas writes: >> On 17.12.2012 11:04, Pavan Deolasee wrote: >>> On Mon, Dec 17, 2012 at 2:00 PM, Heikki Linnakangas >>> wrote: I've still used XLByte* macros, but I agree that plain <=> are easier to read. +1 for using <=> in new code. > >>> Do we ever see us changing this from 64-bit integers to something else >>> ? If so, a macro would be much better. > >> I don't see us changing it again any time soon. Maybe in 20 years time >> people will start overflowing 2^64 bytes of WAL generated in the >> lifetime of a database, but I don't think we need to start preparing for >> that yet. > > Note that to get to 2^64 in twenty years, an installation would have had > to have generated an average of 29GB of WAL per second, 24x7 for the > entire twenty years, with never a dump-and-reload. We're still a few > orders of magnitude away from needing to think about this. > I probably did not mean increasing that to beyond 64-bit. OTOH I wondered if we would ever want to steal a few bits from the LSN field, given the numbers you just put out. But it was more of a question than objection. Thanks, Pavan -- Pavan Deolasee http://www.linkedin.com/in/pavandeolasee -- 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] Makefiles don't seem to remember to rebuild everything anymore
On 12/17/12 8:46 AM, Peter Eisentraut wrote: > On 12/15/12 11:23 AM, Tom Lane wrote: >> =?iso-8859-15?q?C=E9dric_Villemain?= writes: >>> Le vendredi 14 décembre 2012 23:02:11, Tom Lane a écrit : $ rm gram.o rm: remove regular file `gram.o'? y $ make make: Nothing to be done for `all'. WTF? >> >>> A previous patch changed the ".SECONDARY" from an if() section to the main >>> section of src/Makefile.global.in, >> >> Hm. I should have made clear that this isn't specific to gram.o --- >> I also tried "rm analyze.o" and that didn't trigger any action either. >> So it doesn't seem to be a case of the bison rule chain being >> specifically at fault. >> >> I do suspect that this was triggered by some fairly recent makefile >> change, though, because I don't recall having seen anything as odd >> as this until recently. > > I can see it in 9.1 but not in 9.0, but I haven't had the time to > pinpoint the exact change. The change happened here: [5c788e7cf5026be1cad634f12bb42111a411cd9e] When in automatic dependency mode, never delete any intermediate files automatically. I suppose that you are not using automatic dependency mode, so you are seeing the change just now with the recent introduction of global .SECONDARY. This is working correctly, as far as make is concerned. There is no configuration knob in make that says, build all the files you can think of even though they are not required for the target I gave you. If you are not happy with the current behavior, I suppose the best way forward would be to revert the global .SECONDARY setting and instead explicitly list the files you don't want deleted in localized .SECONDARY targets (something like .SECONDARY: gram.c). Needs some testing, of course. -- 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] XLByte* usage
On Mon, Dec 17, 2012 at 11:26 PM, Pavan Deolasee
wrote:
>>
>
> I probably did not mean increasing that to beyond 64-bit. OTOH I
> wondered if we would ever want to steal a few bits from the LSN field,
> given the numbers you just put out. But it was more of a question than
> objection.
>
BTW, now that XLogRecPtr is uint64, can't we change the pd_lsn field
to use the same type ? At least the following comment in bufpage.h
looks outdated or at the minimum needs some explanation as why LSN in
the page header needs to split into two 32-bit values.
123 /* for historical reasons, the LSN is stored as two 32-bit values. */
124 typedef struct
125 {
126 uint32 xlogid; /* high bits */
127 uint32 xrecoff;/* low bits */
128 } PageXLogRecPtr;
Thanks,
Pavan
--
Pavan Deolasee
http://www.linkedin.com/in/pavandeolasee
--
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] XLByte* usage
Andres Freund writes: > On 2012-12-17 12:47:41 -0500, Tom Lane wrote: >> But, if the day ever comes when 64 bits doesn't seem like enough, I bet >> we'd move to 128-bit integers, which will surely be available on all >> platforms by then. So +1 for using plain comparisons --- in fact, I'd >> vote for running around and ripping out the macros altogether. I had >> already been thinking of fixing the places that are still using memset >> to initialize XLRecPtrs to "invalid". > I thought about that and had guessed you would be against it because it > would cause useless diversion of the branches? Otherwise I am all for > it. That's the only argument I can see against doing it --- but Heikki's patch was already pretty invasive in the same areas this would touch, so I'm thinking this won't make back-patching much worse. The notational simplification seems worth it. 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
Re: [HACKERS] Makefiles don't seem to remember to rebuild everything anymore
Peter Eisentraut writes: > I suppose that you are not using automatic dependency mode, so you are > seeing the change just now with the recent introduction of global > .SECONDARY. True. > This is working correctly, as far as make is concerned. There is no > configuration knob in make that says, build all the files you can think > of even though they are not required for the target I gave you. Meh. I don't agree that parser/gram.o is "not required" when I'm asking to build the postgres executable. The fact that making the latter actually fails, as per Pavan's observation, should convince you of that too. Now perhaps this is not make's fault so much as a lack of adequate dependency specifications. It may be that we can still use .SECONDARY if we add the $(OBJS) lists as explicit targets of "make all" in backend directories, but I'm not sure how invasive that would be. 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
Re: [HACKERS] XLByte* usage
On 2012-12-17 23:45:51 +0530, Pavan Deolasee wrote:
> On Mon, Dec 17, 2012 at 11:26 PM, Pavan Deolasee
> wrote:
>
> >>
> >
> > I probably did not mean increasing that to beyond 64-bit. OTOH I
> > wondered if we would ever want to steal a few bits from the LSN field,
> > given the numbers you just put out. But it was more of a question than
> > objection.
> >
>
> BTW, now that XLogRecPtr is uint64, can't we change the pd_lsn field
> to use the same type ? At least the following comment in bufpage.h
> looks outdated or at the minimum needs some explanation as why LSN in
> the page header needs to split into two 32-bit values.
>
> 123 /* for historical reasons, the LSN is stored as two 32-bit values. */
> 124 typedef struct
> 125 {
> 126 uint32 xlogid; /* high bits */
> 127 uint32 xrecoff;/* low bits */
> 128 } PageXLogRecPtr;
pg_upgrade'ability. The individual bytes aren't necessarily laid out the
same with two such uint32s as with one uint64.
Greetings,
Andres Freund
--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services
--
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] Makefiles don't seem to remember to rebuild everything anymore
I wrote: > Now perhaps this is not make's fault so much as a lack of adequate > dependency specifications. It may be that we can still use .SECONDARY > if we add the $(OBJS) lists as explicit targets of "make all" in backend > directories, but I'm not sure how invasive that would be. I experimented a bit with this: diff --git a/src/backend/common.mk b/src/backend/common.mk index 2e56151..822b1e9 100644 *** a/src/backend/common.mk --- b/src/backend/common.mk *** SUBDIROBJS = $(SUBDIRS:%=%/$(subsysfilen *** 20,26 # top-level backend directory obviously has its own "all" target ifneq ($(subdir), src/backend) ! all: $(subsysfilename) endif SUBSYS.o: $(SUBDIROBJS) $(OBJS) --- 20,26 # top-level backend directory obviously has its own "all" target ifneq ($(subdir), src/backend) ! all: $(subsysfilename) $(OBJS) endif SUBSYS.o: $(SUBDIROBJS) $(OBJS) which seems to fix the main issue, but it's still a bit wonky as far as making objfiles.txt goes: $ cd pgsql/src/backend/parser/ $ rm analyze.o rm: remove regular file `analyze.o'? y $ make gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -g -I. -I. -I../../../src/include -D_GNU_SOURCE -c -o analyze.o analyze.c $ make touch objfiles.txt $ make make: Nothing to be done for `all'. This is definitely not per make's contract, either. I think maybe the "Don't rebuild the list if only the OBJS have changed" hack in common.mk is a brick or two shy of a load, but I don't know how to fix that. 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
Re: [HACKERS] XLByte* usage
Pavan Deolasee writes: > BTW, now that XLogRecPtr is uint64, can't we change the pd_lsn field > to use the same type ? No, at least not without breaking on-disk compatibility on little-endian machines. 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
Re: [HACKERS] proposal - assign result of query to psql variable
2012/12/17 Shigeru Hanada : > On Sun, Oct 28, 2012 at 7:16 AM, Pavel Stehule > wrote: >> Hello >> >> here is updated patch >> >> main change - it doesn't touch psql lexer - like Tom proposed >> other points respect Phil's notices > > I reviewed v12 patch. It provides gset command which works > consistently with other psql commands, such as \g and \set, and > implementation seems reasonable, and follows other reviewer's comments > properly. I think we can mark it as "ready for committer", once you > have fixed some minor issues below. > > * Skipping leading blank in inner while loop of command.c seems > unnecessary, because (IIUC) psql's scanner skips blanks. Is there any > case that scanner returns token with leading/trailing blank? removed > * ISTM that VARLIST_INITIAL can be removed. AFAIS it's same state as > VARLIST_EXPECTED_COMMA_OR_IDENT. removed > * I found some cosmetic flaw and typo. Please see attached patch for details. it is ok, merged > * How about pulling up codes for PGRES_TUPLES_OK case in > StoreQueryResult to new static function, say StoreQueryTuple? It > would make StoreQueryResult more similar to PrintQueryResult's style, > and IMO it makes the code more readable. good idea done Attached updated patch Regards Pavel > > Regards, > -- > Shigeru HANADA gset_13.diff Description: Binary data -- 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] Enabling Checksums
On 14 December 2012 20:15, Greg Smith wrote: > On 12/14/12 3:00 PM, Jeff Davis wrote: >> >> After some thought, I don't see much value in introducing multiple >> instances of corruption at a time. I would think that the smallest unit >> of corruption would be the hardest to detect, so by introducing many of >> them in one pass makes it easier to detect. > > > That seems reasonable. It would eliminate a lot of issues with reproducing > a fault too. I can just print the impacted block number presuming it will > show up in a log, and make it possible to override picking one at random > with a command line input. Discussing this makes me realise that we need a more useful response than just "your data is corrupt", so user can respond "yes, I know, I'm trying to save whats left". We'll need a way of expressing some form of corruption tolerance. zero_damaged_pages is just insane, much better if we set corruption_tolerance = N to allow us to skip N corrupt pages before failing, with -1 meaning keep skipping for ever. Settable by superuser only. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] Enabling Checksums
Simon Riggs writes: > Discussing this makes me realise that we need a more useful response > than just "your data is corrupt", so user can respond "yes, I know, > I'm trying to save whats left". > We'll need a way of expressing some form of corruption tolerance. > zero_damaged_pages is just insane, much better if we set > corruption_tolerance = N to allow us to skip N corrupt pages before > failing, with -1 meaning keep skipping for ever. Settable by superuser > only. Define "skip". Extra points if it makes sense for an index. And what about things like pg_clog pages? 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
Re: [HACKERS] Serious problem: media recovery fails after system or PostgreSQL crash
On Sun, Dec 16, 2012 at 8:38 AM, Tomas Vondra wrote: > On 8.12.2012 03:08, Jeff Janes wrote: >> >> It seems to me you need considerable expertise to figure out how to do >> optimal recovery (i.e. losing the least transactions) in this >> situation, and that that expertise cannot be automated. Do you trust >> a partial file from a good hard drive, or a complete file from a >> partially melted pg_xlog? > > It clearly is a rather complex issue, no doubt about that. And yes, > reliability of the devices with pg_xlog on them is an important detail. > Alghough if the WAL is not written in a reliable way, you're hosed > anyway I guess. > > The recommended archive command is based on the assumption that the > local pg_xlog is intact (e.g. because it's located on a reliable RAID1 > array), which seems to be the assumption of the OP too. > > In my opinion it's more likely to meet an incomplete copy of WAL in the > archive than a corrupted local WAL. And if it really is corrupted, it > would be identified during replay. Wouldn't the way it would be identified be for it to fail a checksum, assume it was garbage left over from the previous WAL file which was the process of being overwritten at the time of crash, and so terminate recovery and open the database? Assuming your goal is to recover all the transactions you possibly can (rather than restore to a known point), I think you would want to try recovery both ways and keep whichever one got the furthest. Cheers, Jeff -- 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] multiple CREATE FUNCTION AS items for PLs
On 12/16/12 1:20 PM, Hannu Krosing wrote: > I was going to suggest some special function name to be pulled out of code > passed to CREATE FUNCTION in line with > > CREATE FUNCTION foo(a,b,c) AS $$ > import x > from __future__ import nex_cool_feature > > def helper_function(x): >... > > def __pg_main__(a,b,c): > defined function body here > > $$; > > so that the whole text gets compiled into module at first call and the > __pg_main__ will > be the function that gets called as foo(a,b,c) from postgresql > > but this would not be backwards compatible, at least not in any obvious way. Yes, this would be a good solution for some applications, but the only way I can think of to manage the compatibility issue is to invent some function attribute system like CREATE FUNCTION ... OPTIONS (call_convention 'xyz') But this is also a lot more typing, so the two-part AS solution still has some appeal if you just want an import. -- 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] Enabling Checksums
On 17 December 2012 19:29, Tom Lane wrote: > Simon Riggs writes: >> Discussing this makes me realise that we need a more useful response >> than just "your data is corrupt", so user can respond "yes, I know, >> I'm trying to save whats left". > >> We'll need a way of expressing some form of corruption tolerance. >> zero_damaged_pages is just insane, much better if we set >> corruption_tolerance = N to allow us to skip N corrupt pages before >> failing, with -1 meaning keep skipping for ever. Settable by superuser >> only. > > Define "skip". Allow data access, but accept that the answer is silently incomplete. Not really much difference from zero_damaged_pages which just removes the error by removing any chance of repair or recovery, and then silently gives the wrong answer. > Extra points if it makes sense for an index. I guess not, but that's no barrier to it working on heap pages only, in my suggested use case. > And what about things like pg_clog pages? SLRUs aren't checksummed because of their lack of header space. Perhaps that is a major point against the patch. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] multiple CREATE FUNCTION AS items for PLs
2012/12/17 Peter Eisentraut : > On 12/16/12 1:20 PM, Hannu Krosing wrote: >> I was going to suggest some special function name to be pulled out of code >> passed to CREATE FUNCTION in line with >> >> CREATE FUNCTION foo(a,b,c) AS $$ >> import x >> from __future__ import nex_cool_feature >> >> def helper_function(x): >>... >> >> def __pg_main__(a,b,c): >> defined function body here >> >> $$; >> >> so that the whole text gets compiled into module at first call and the >> __pg_main__ will >> be the function that gets called as foo(a,b,c) from postgresql >> >> but this would not be backwards compatible, at least not in any obvious way. > > Yes, this would be a good solution for some applications, but the only > way I can think of to manage the compatibility issue is to invent some > function attribute system like > > CREATE FUNCTION ... OPTIONS (call_convention 'xyz') > > But this is also a lot more typing, so the two-part AS solution still > has some appeal if you just want an import. > two-part AS is not intuitive and it is looking really obscure Regards Pavel > > -- > Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-hackers -- 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] Error restoring from a base backup taken from standby
On 17 December 2012 17:39, Heikki Linnakangas wrote: > (This is different from the other issue related to timeline switches I just > posted about. There's no timeline switch involved in this one.) > > If you do "pg_basebackup -x" against a standby server, in some circumstances > the backup fails to restore with error like this: > > C 2012-12-17 19:09:44.042 EET 7832 LOG: database system was not properly > shut down; automatic recovery in progress > C 2012-12-17 19:09:44.091 EET 7832 LOG: record with zero length at > 0/1764F48 > C 2012-12-17 19:09:44.091 EET 7832 LOG: redo is not required > C 2012-12-17 19:09:44.091 EET 7832 FATAL: WAL ends before end of online > backup > C 2012-12-17 19:09:44.091 EET 7832 HINT: All WAL generated while online > backup was taken must be available at recovery. > C 2012-12-17 19:09:44.092 EET 7831 LOG: startup process (PID 7832) exited > with exit code 1 > C 2012-12-17 19:09:44.092 EET 7831 LOG: aborting startup due to startup > process failure > > I spotted this bug while reading the code, and it took me quite a while to > actually construct a test case to reproduce the bug, so let me begin by > discussing the code where the bug is. You get the above error, "WAL ends > before end of online backup", when you reach the end of WAL before reaching > the backupEndPoint stored in the control file, which originally comes from > the backup_label file. backupEndPoint is only used in a base backup taken > from a standby, in a base backup taken from the master, the end-of-backup > WAL record is used instead to mark the end of backup. In the xlog redo loop, > after replaying each record, we check if we've just reached backupEndPoint, > and clear it from the control file if we have. Now the problem is, if there > are no WAL records after the checkpoint redo point, we never even enter the > redo loop, so backupEndPoint is not cleared even though it's reached > immediately after reading the initial checkpoint record. > > To deal with the similar situation wrt. reaching consistency for hot standby > purposes, we call CheckRecoveryConsistency() before the redo loop. The > straightforward fix is to copy-paste the check for backupEndPoint to just > before the redo loop, next to the CheckRecoveryConsistency() call. Even > better, I think we should move the backupEndPoint check into > CheckRecoveryConsistency(). It's already responsible for keeping track of > whether minRecoveryPoint has been reached, so it seems like a good idea to > do this check there as well. > > Attached is a patch for that (for 9.2), as well as a script I used to > reproduce the bug. The script is a bit messy, and requires tweaking the > paths at the top. Anyone spot a problem with this? Yep. The problem is one of design, not merely a coding error. The design assumes that the master is up, connected and doing work. Which is perfectly reasonable, since this is the reason we are doing a backup from the standby. But obviously not always true, so what we're saying is that the selection of backupEndPoint is incorrect in the first place. Putting code in to cope with that poor choice at recovery seems wrong - we should record the correct location. Comments in do_pg_start_backup() say this * We return the current minimum recovery point as the backup end * location. Note that it can be greater than the exact backup end * location if the minimum recovery point is updated after the backup of * pg_control. This is harmless for current uses. Which would stay wrong if we apply the proposed patch. I think we should record the most recently received LSN, which I notice is not the misnamed receivedUpto in xlog.c Misnamed because it refers to what has been received and written rather than what has been received. So I suggest using latestWalEnd if streaming or similar for file access. In passing, I see another problem as well. We assume that the backup is still valid if we promote the standby to a master during the backup, but that isn't documented and so isn't full analysed to see that is true. I haven't tried to construct a failure case out of that, as yet. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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_basebackup from cascading standby after timeline switch
On 17 December 2012 14:16, Heikki Linnakangas wrote: > I'm not happy with the fact that we just ignore the problem in a backup > taken from a standby, silently giving the user a backup that won't start up. That's spooky. I just found a different issue with prmotion during backup on your other thread. > Why not include the timeline history file in the backup? That seems like a > good idea regardless of this issue. Yeh > I also wonder if pg_basebackup should > include *all* timeline history files in the backup, not just the latest one > strictly required to restore. Why? the timeline history file includes the previous timelines already. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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] multiple CREATE FUNCTION AS items for PLs
On 12/17/2012 10:34 PM, Peter Eisentraut wrote: On 12/16/12 1:20 PM, Hannu Krosing wrote: I was going to suggest some special function name to be pulled out of code passed to CREATE FUNCTION in line with CREATE FUNCTION foo(a,b,c) AS $$ import x from __future__ import nex_cool_feature def helper_function(x): ... def __pg_main__(a,b,c): defined function body here $$; so that the whole text gets compiled into module at first call and the __pg_main__ will be the function that gets called as foo(a,b,c) from postgresql but this would not be backwards compatible, at least not in any obvious way. Yes, this would be a good solution for some applications, but the only way I can think of to manage the compatibility issue is to invent some function attribute system like CREATE FUNCTION ... OPTIONS (call_convention 'xyz') How about using a GUC for setting calling convention? This SET can be even done as part of CREATE FUNCTION . CREATE FUNCTION $$ ... $$ ... SET plpython.cc=9.2; - Hannu But this is also a lot more typing, so the two-part AS solution still has some appeal if you just want an import. Import is just a small sub-case of what you would want in your module environment. And as it is really done only once per backend anyway - starting the second time it is just a dictionary lookup - it should not be singled out, at least not for performance reasons. - Hannu -- 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] small pg_basebackup display bug
On Sun, Dec 16, 2012 at 01:20:53PM -0500, Tom Lane wrote: > Magnus Hagander writes: > > On Sat, Dec 15, 2012 at 2:24 PM, Erik Rijkers wrote: > >> That would make such a truncation less frequent, and after all a truncated > >> display is not > >> particular useful. > > > Agreed - it's useful during testing, but not in a typical production > > use. It might actually be more useful if it's truncated in in the > > other end (keeping the last 30 instead of the first 30 chars) > > +1 for truncating from the left. I think pg_upgrade already does that > in its progress messages. Yes, it does in the current git tree. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] multiple CREATE FUNCTION AS items for PLs
Hannu Krosing writes: > On 12/17/2012 10:34 PM, Peter Eisentraut wrote: >> Yes, this would be a good solution for some applications, but the only >> way I can think of to manage the compatibility issue is to invent some >> function attribute system like >> >> CREATE FUNCTION ... OPTIONS (call_convention 'xyz') > How about using a GUC for setting calling convention? GUCs are a truly bad fit for properties that need to be function-local. > This SET can be even done as part of CREATE FUNCTION . > CREATE FUNCTION $$ ... $$ ... SET plpython.cc=9.2; That doesn't fix the problem, because a setting made that way will affect called functions too. The only way you could use it safely would be to add such a SET clause to every single plpython function in the database. At that point an OPTIONS clause (which can have a backwards-compatible default behavior) looks a lot more attractive. I still think that embedding some type of extension syntax in the function body is the best solution. But if that's too ugly, let's invent something like Peter's OPTIONS syntax above, with the understanding that it affects only the function it's applied to (unlike SET), and contains PL-specific options. 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
Re: [HACKERS] pg_basebackup from cascading standby after timeline switch
Simon Riggs writes: > On 17 December 2012 14:16, Heikki Linnakangas wrote: >> I also wonder if pg_basebackup should >> include *all* timeline history files in the backup, not just the latest one >> strictly required to restore. > Why? the timeline history file includes the previous timelines already. The original intention was that the WAL archive might contain multiple timeline files corresponding to various experimental recovery attempts; furthermore, such files might be hand-annotated (that's why there's a comment provision). So they would be at least as valuable from an archival standpoint as the WAL files proper. I think we ought to just copy all of them, period. Anything less is penny-wise and pound-foolish. 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
Re: [HACKERS] [ADMIN] Problems with enums after pg_upgrade
On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote: > Hello together, > > last thursday I upgraded one of our 9.0.6 postgresql servers to > 9.2.2 with pg_upgrade. So far everything seemed to work but we now > discover problems with the enum types. If we run one specific query > it breaks all time with such an error message: > > ERROR: invalid internal value for enum: 520251 > > if this number should represent the enumtypid it is not existing > anymore in pg_enum. > > How could i solve this problem? should we regenerate all enums? or > what could we do? > Hopefully anyone has a clue, google doesn't seem to be the ressource > for this problem. We seriously tested the enum code so I am pretty confused why this is failing. If you do pg_dump --binary-upgrade --schema-only, do you see that a number like this being defined just before the enum is added? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] Parser Cruft in gram.y
On Sat, Dec 15, 2012 at 11:52 AM, Tom Lane wrote: > "Kevin Grittner" writes: >> Tom Lane wrote: >>> the parser tables are basically number-of-tokens wide by >>> number-of-states high. (In HEAD there are 433 tokens known to the >>> grammar, all but 30 of which are keywords, and 4367 states.) >>> >>> Splitting the grammar into multiple grammars is unlikely to do >>> much to improve this --- in fact, it could easily make matters >>> worse due to duplication. > >> Of course if they were both at 80% it would be a higher total than >> combined, but unless you have a handle on the percentages, it >> doesn't seem like a foregone conclusion. Do you have any feel for >> what the split would be? > > I don't really, but I will note that the scalar-expression subgrammar is > a pretty sizable part of the whole, and it's difficult to see how you'd > make a useful split that didn't duplicate it. I guess you could push > CREATE TABLE, ALTER TABLE, CREATE DOMAIN, ALTER DOMAIN, COPY, and > anything else that included expression arguments over into the "main" > grammar. But that path leads to more and more stuff getting moved to > the "main" grammar over time, making the whole thing more and more > questionable. The whole concept seems ugly and unmaintainable in any > case. I thought a little bit about the sort of thing that Dimitri is proposing in the past, and it seemed to me that one could put DML in one grammar and everything else in another grammar and then decide, based on the first word of the input, which grammar to use. But there are a couple of problems with this. First, the DML grammar has to include an awful lot of stuff, because the grammar for expressions is really complicated and involves a lot of things like special-case syntax for XML that are probably not really carrying their weight but which cannot easily be factored out. Second, the DDL grammar would have to duplicate a lot of stuff that also shows up in the DML grammar, because things like expressions can also show up in DEFAULT or USING clauses which show up in things like CREATE TABLE and ALTER TABLE and CREATE SCHEMA .. CREATE TABLE. Now either one of these problems by itself might not be sufficient to kill the idea: if the DML grammar were a small subset of the full grammar, one might not mind duplicating some stuff, on the grounds that in most cases that full grammar would not be used, and using only the smaller tables most of the time would be easier on the L1 cache. And on the other hand, if you could get a clean split between the two grammars, then regardless of exactly what the split was, it might seem a win. But it seemed to me when I looked at this that you'd have to duplicate a lot of stuff and the small parser still wouldn't end up being very small, which I found hard to get excited about. I'm frankly kind of shocked at the revelation that the parser is already 14% of the backend. I knew it was big; I didn't realize it was THAT big. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- 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] [ADMIN] Problems with enums after pg_upgrade
On Mon, Dec 17, 2012 at 12:14:29PM +0100, Bernhard Schrader wrote: > Hello together, > > last thursday I upgraded one of our 9.0.6 postgresql servers to > 9.2.2 with pg_upgrade. So far everything seemed to work but we now > discover problems with the enum types. If we run one specific query > it breaks all time with such an error message: > > ERROR: invalid internal value for enum: 520251 > > if this number should represent the enumtypid it is not existing > anymore in pg_enum. > > How could i solve this problem? should we regenerate all enums? or > what could we do? > Hopefully anyone has a clue, google doesn't seem to be the ressource > for this problem. Can anyone suggest why enum upgrade is failing for this user? What should he be checking for? -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- 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] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
This is the first pg_upgrade mismatch report we have gotten about 9.1. I have asked the reporter for details. Is what is the full 9.1 version number? --- On Mon, Dec 17, 2012 at 03:33:40PM +0400, Groshev Andrey wrote: > Hello! > I'm trying to update a database from version 9.0 to 9.1 by pg_upgrade. > The test is normal, but the actual conversion fails. > This is a bug from pg_upgrade? > I just saw this in the newsletter similar error with a note that it has been > fixed in 9.1.2, but I already install 9.1.7. > > My environment > centos 6.3 > # uname -rm > 2.6.32-279.14.1.el6.x86_64 x86_64 > > # rpm -qa |grep postgres > postgresql90-devel-9.0.11-1PGDG.rhel6.x86_64 > postgresql91-9.1.7-1PGDG.rhel6.x86_64 > postgresql90-9.0.11-1PGDG.rhel6.x86_64 > postgresql90-server-9.0.11-1PGDG.rhel6.x86_64 > postgresql91-libs-9.1.7-1PGDG.rhel6.x86_64 > postgresql91-server-9.1.7-1PGDG.rhel6.x86_64 > postgresql91-devel-9.1.7-1PGDG.rhel6.x86_64 > postgresql90-libs-9.0.11-1PGDG.rhel6.x86_64 > postgresql90-contrib-9.0.11-1PGDG.rhel6.x86_64 > postgresql91-contrib-9.1.7-1PGDG.rhel6.x86_64 > > > # time sudo -u postgres sh -c '/usr/pgsql-9.1/bin/pg_upgrade -b > /usr/pgsql-9.0/bin/ -B /usr/pgsql-9.1/bin/ -d /var/lib/pgsql/9.0/data/ -D > /var/lib/pgsql/9.1/data/ -vvv -c -l ./log 2>&1 | iconv -f cp1251 -t utf-8' > Running in verbose mode > . > Running in verbose mode > Running in verbose mode > Performing Consistency Checks > - > Checking current, bin, and data directories ok > Checking cluster versions ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" -o "-p > 5432 -c autovacuum=off -c autovacuum_freeze_max_age=20" start >> > "./log" 2>&1 > Checking database user is a superuser ok > Checking for prepared transactions ok > Checking for reg* system oid user data typesok > Checking for contrib/isn with bigint-passing mismatch ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" stop > >> "./log" 2>&1 > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" -o "-p > 5432 -b" start >> "./log" 2>&1 > Checking for presence of required libraries ok > Checking database user is a superuser ok > Checking for prepared transactions ok > > *Clusters are compatible* > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" stop > >> "./log" 2>&1 > > real0m4.344s > user0m0.029s > sys 0m0.051s > > > > Checking current, bin, and data directories ok > Checking cluster versions ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" -o "-p > 5432 -c autovacuum=off -c autovacuum_freeze_max_age=20" start >> > "./log > " 2>&1 > Checking database user is a superuser ok > Checking for prepared transactions ok > Checking for reg* system oid user data typesok > Checking for contrib/isn with bigint-passing mismatch ok > Creating catalog dump > "/usr/pgsql-9.1/bin/pg_dumpall" --port 5432 --username "postgres" > --schema-only --binary-upgrade > -f "/var/lib/pgsql/pg_upgrade/pg_upgrade_dump_all.sql" > ok > "/usr/pgsql-9.0/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.0/data" stop > >> "./log" 2>&1 > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" -o "-p > 5432 -b" start >> "./log" 2>&1 > Checking for presence of required libraries ok > Checking database user is a superuser ok > Checking for prepared transactions ok > > | If pg_upgrade fails after this point, you must > | re-initdb the new cluster before continuing. > | You will also need to remove the ".old" suffix > | from /var/lib/pgsql/9.0/data/global/pg_control.old. > > Performing Upgrade > -- > Adding ".old" suffix to old global/pg_control ok > Analyzing all rows in the new cluster > "/usr/pgsql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all > --analyze >> "./log" 2>&1 > ok > Freezing all rows on the new cluster > "/usr/pgsql-9.1/bin/vacuumdb" --port 5432 --username "postgres" --all > --freeze >> "./log" 2>&1 > ok > "/usr/pgsql-9.1/bin/pg_ctl" -w -l "./log" -D "/var/lib/pgsql/9.1/data" stop > >> "./log" 2>&1 > Deleting new commit clogs ok > Copying old commit clogs to new server cp -Rf > "/var/lib/pgsql/9.0/data/pg_clog" "/var/lib/pgsql/9.1/data/pg_clog" > ok > Setting next transaction id for new cluster > "/usr/pgsql-9.1/bin/pg_resetxlog" -f -x 728832600 "/var/lib/pg
Re: [HACKERS] Enabling Checksums
On Mon, 2012-12-17 at 19:14 +, Simon Riggs wrote: > We'll need a way of expressing some form of corruption tolerance. > zero_damaged_pages is just insane, The main problem I see with zero_damaged_pages is that it could potentially write out the zero page, thereby really losing your data if it wasn't already lost. (Of course, we document that you should have a backup first, but it's still dangerous). I assume that this is the same problem you are talking about. I suppose we could have a new ReadBufferMaybe function that would only be used by a sequential scan; and then just skip over the page if it's corrupt, depending on a GUC. That would at least allow sequential scans to (partially) work, which might be good enough for some data recovery situations. If a catalog index is corrupted, that could just be rebuilt. Haven't thought about the details, though. Regards, Jeff Davis -- 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] configure.in and setproctitle/optreset problem
Christoph Berg writes: > We are regularly teaching PostgreSQL courses at linuxhotel.de. > Starting from the course in November, PG doesn't compile anymore on > their default Debian Squeeze install laptops they hand out to the > participants. After ./configure && make, the error looks like this: > postmaster/postmaster.o: In function `PostmasterMain': > postmaster.c:(.text+0x40e2): undefined reference to `optreset' > tcop/postgres.o: In function `process_postgres_switches': > postgres.c:(.text+0x168c): undefined reference to `optreset' > utils/misc/ps_status.o: In function `set_ps_display': > ps_status.c:(.text+0xba): undefined reference to `setproctitle' > collect2: error: ld returned 1 exit status > make[2]: *** [postgres] Fehler 1 > make[2]: Leaving directory > `/home/cbe/projects/postgresql/postgresql/src/backend' > This is 9.2.2, but I've seen 9.1.6 failing in the same way. The > package set installed is a desktop install, plus what Debian > considers to be build dependencies of postgresql-9.2, that is, > libedit-dev is installed, but libreadline-dev isn't. I installed Debian for the first time in a long time, and verified that their version of libedit exposes bogus definitions of setproctitle, optreset, and god knows what else. > configure.in already contains a workaround for -ledit containing a > strlcpy() version, so I'd propose the attached patch to extend that > workaround to setproctitle. (Patch for 9.2's configure.in, but the > same moving of the two code blocks should work for head as well.) I think we should assume that the libedit developers are utterly clueless about not trampling on application namespace, and just cut that library out of *all* our link checks except for the symbols we specifically expect to get from libedit/libreadline. Hence, I propose the attached slightly more extensive patch. I've verified that this produces a working build with Debian's libedit. regards, tom lane diff --git a/configure.in b/configure.in index 2dee4b392b18511f18c4e2f49cab482f2ec72dc8..d6968bb9c0a82ab1c509443905fc5f5c1f13deab 100644 *** a/configure.in --- b/configure.in *** PGAC_VAR_INT_TIMEZONE *** 1202,1207 --- 1202,1214 AC_FUNC_ACCEPT_ARGTYPES PGAC_FUNC_GETTIMEOFDAY_1ARG + # Some versions of libedit contain strlcpy(), setproctitle(), and other + # symbols that that library has no business exposing to the world. Pending + # acquisition of a clue by those developers, ignore libedit (including its + # possible alias of libreadline) while checking for everything else. + pgac_save_LIBS="$LIBS" + LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` + AC_CHECK_FUNCS([cbrt dlopen fdatasync getifaddrs getpeerucred getrlimit mbstowcs_l memmove poll pstat readlink setproctitle setsid sigprocmask symlink sync_file_range towlower utime utimes wcstombs wcstombs_l]) AC_REPLACE_FUNCS(fseeko) *** else *** 1316,1326 AC_CHECK_FUNCS([fpclass fp_class fp_class_d class], [break]) fi - # Some versions of libedit contain strlcpy(); so disregard that library while - # checking for these standard libc functions. - pgac_save_LIBS="$LIBS" - LIBS=`echo "$LIBS" | sed -e 's/-ledit//g' -e 's/-lreadline//g'` - AC_REPLACE_FUNCS([crypt fls getopt getrusage inet_aton random rint srandom strerror strlcat strlcpy]) case $host_os in --- 1323,1328 *** case $host_os in *** 1338,1346 ;; esac - - LIBS="$pgac_save_LIBS" - # System's version of getaddrinfo(), if any, may be used only if we found # a definition for struct addrinfo; see notes in src/include/getaddrinfo.h. # (Note: the AC_REPLACE_FUNCS probe fails on Windows, where the available --- 1340,1345 *** else *** 1394,1406 AC_SUBST(have_win32_dbghelp,no) fi - if test "$with_readline" = yes; then - PGAC_VAR_RL_COMPLETION_APPEND_CHARACTER - AC_CHECK_FUNCS([rl_completion_matches rl_filename_completion_function]) - AC_CHECK_FUNCS([append_history history_truncate_file]) - fi - - dnl Cannot use AC_CHECK_FUNC because sigsetjmp may be a macro dnl (especially on GNU libc) dnl See also comments in c.h. --- 1393,1398 *** if test x"$pgac_cv_gcc_int_atomics" = x" *** 1451,1456 --- 1443,1457 AC_DEFINE(HAVE_GCC_INT_ATOMICS, 1, [Define to 1 if you have __sync_lock_test_and_set(int *) and friends.]) fi + # Lastly, restore full LIBS list and check for readline/libedit symbols + LIBS="$pgac_save_LIBS" + + if test "$with_readline" = yes; then + PGAC_VAR_RL_COMPLETION_APPEND_CHARACTER + AC_CHECK_FUNCS([rl_completion_matches rl_filename_completion_function]) + AC_CHECK_FUNCS([append_history history_truncate_file]) + fi + # # Pthreads -- 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] configure.in and setproctitle/optreset problem
On 12/17/2012 09:44 PM, Tom Lane wrote: I think we should assume that the libedit developers are utterly clueless about not trampling on application namespace, and just cut that library out of *all* our link checks except for the symbols we specifically expect to get from libedit/libreadline. Hence, I propose the attached slightly more extensive patch. I've verified that this produces a working build with Debian's libedit. +1. This being a family channel I'll refrain from expressing my opinion too frankly of libedit. cheers andrew -- 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] Parser Cruft in gram.y
Robert Haas writes: > I'm frankly kind of shocked at the revelation that the parser is > already 14% of the backend. I knew it was big; I didn't realize it > was THAT big. Yeah, likewise. It makes me wonder whether we aren't past the size of grammar that bison is a good choice for: considering that gram.y is only circa 1% of the source text, it's surprising to find that it compiles to >10% of the object code. I'm not sure what other tool might be better though. I looked through http://en.wikipedia.org/wiki/Comparison_of_parser_generators#Deterministic_context-free_languages but it seems our options are a bit limited if we want something that produces C. It's not clear to me that any of the likely options are as mature as bison, let alone likely to substantially outperform it. (For instance, Hyacc sounded pretty promising until I got to the part about it doesn't yet support %union or %type.) Still, I didn't spend much time on this --- maybe somebody else would like to do a bit more research. 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
Re: [HACKERS] Assert for frontend programs?
cpluspluscheck is having issues with this change: In file included from /tmp/cpluspluscheck.QEdLaR/test.cpp:3:0: ./src/include/postgres_fe.h:34:0: warning: "Assert" redefined [enabled by default] In file included from /tmp/cpluspluscheck.QEdLaR/test.cpp:2:0: src/include/postgres.h:673:0: note: this is the location of the previous definition This probably rather an issue with how cpluspluscheck is set up, since including both postgres.h and postgres_fe.h is not useful. -- 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] Doc patch, put RAISE USING keywords into a table
On Fri, 2012-10-26 at 16:23 -0500, Karl O. Pinc wrote: > On 10/26/2012 10:23:56 AM, Karl O. Pinc wrote: > > On 10/26/2012 09:58:05 AM, Karl O. Pinc wrote: > > > > > The attached patch, raise_using_keyword_table.patch, > > > puts the pl/pgsql RAISE USING keywords into a table, > > > replacing a prose description. > > Attached is: raise_using_keyword_table-v3.patch > It uses a variable list instead of a table. I have committed this "list" version with some small modifications. -- 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] Switching timeline over streaming replication
Since Thom already did the destruction test, I only chained 7 standbies, just to see if I could reproduce his error. In the process, I accidentally connected one standby to itself. This failed, but the error message wasn't very helpful; it just gave me "FATAL: could not connect, the database system is starting up". Surely there's some way we could tell the user they've tried to connect a standby to itself? Anyway, I was unable to reproduce Thom's error. I did not see the error message he did. Without any read queries running on the standbys, lag from master to replica7 averaged about 0.5 seconds, ranging between 0.1 seconds and 1.2 seconds. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- 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] [GENERAL] trouble with pg_upgrade 9.0 -> 9.1
18.12.2012, 05:22, "Bruce Momjian" : > This is the first pg_upgrade mismatch report we have gotten about 9.1. > I have asked the reporter for details. > > Is what is the full 9.1 version number? > > --- >> # rpm -qa |grep postgres >> postgresql90-devel-9.0.11-1PGDG.rhel6.x86_64 >> postgresql91-9.1.7-1PGDG.rhel6.x86_64 >> postgresql90-9.0.11-1PGDG.rhel6.x86_64 >> postgresql90-server-9.0.11-1PGDG.rhel6.x86_64 >> postgresql91-libs-9.1.7-1PGDG.rhel6.x86_64 >> postgresql91-server-9.1.7-1PGDG.rhel6.x86_64 >> postgresql91-devel-9.1.7-1PGDG.rhel6.x86_64 >> postgresql90-libs-9.0.11-1PGDG.rhel6.x86_64 >> postgresql90-contrib-9.0.11-1PGDG.rhel6.x86_64 >> postgresql91-contrib-9.1.7-1PGDG.rhel6.x86_64 >> Full version ? It is not full postgresql91-9.1.7-1PGDG.rhel6.x86_64 or I do not understand something? I installed latest postgresql from the repository http://yum.pgrpms.org -- 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] [PERFORM] Slow query: bitmap scan troubles
[moved to hackers] On Wednesday, December 5, 2012, Tom Lane wrote: > Jeff Janes writes: > > I now see where the cost is coming from. In commit 21a39de5809 (first > > appearing in 9.2) the "fudge factor" cost estimate for large indexes > > was increased by about 10 fold, which really hits this index hard. > > > This was fixed in commit bf01e34b556 "Tweak genericcostestimate's > > fudge factor for index size", by changing it to use the log of the > > index size. But that commit probably won't be shipped until 9.3. > > Hm. To tell you the truth, in October I'd completely forgotten about > the January patch, and was thinking that the 1/1 cost had a lot > of history behind it. But if we never shipped it before 9.2 then of > course that idea is false. Perhaps we should backpatch the log curve > into 9.2 --- that would reduce the amount of differential between what > 9.2 does and what previous branches do for large indexes. > I think we should backpatch it for 9.2.3. I've seen another email which is probably due to the same issue (nested loop vs hash join). And some monitoring of a database I am responsible for suggests it might be heading in that direction as well as the size grows. But I am wondering if it should be present at all in 9.3. When it was introduced, the argument seemed to be that smaller indexes might be easier to keep in cache. And surely that is so. But if a larger index that covers the same type of queries exists when a smaller one also exists, we can assume the larger one also exists for a reason. While it may be easier to keep a smaller index in cache, it is not easier to keep both a larger and a smaller one in cache as the same time. So it seems to me that this reasoning is a wash. (Countering this argument is that a partial index is more esoteric, and so if one exists it is more likely to have been well-thought out) The argument for increasing the penalty by a factor of 10 was that the smaller one could be "swamped by noise such as page-boundary-roundoff behavior". I don't really know what that means, but it seems to me that if it is so easily swamped by noise, then it probably isn't so important in the first place which one it chooses. Whereas, I think that even the log based penalty has the risk of being too much on large indexes. (For one thing, it implicitly assumes the fan-out ratio at each level of btree is e, when it will usually be much larger than e.) One thing which depends on the index size which, as far as I can tell, is not currently being counted is the cost of comparing the tuples all the way down the index. This would be proportional to log2(indextuples) * cpu_index_tuple_cost, or maybe log2(indextuples) * (cpu_index_tuple_cost+cpu_operator_cost), or something like that. This cost would depend on the number index tuples, not baserel tuples, and so would penalize large indexes. It would be much smaller than the current log(pages/1) penalty, but it would be more principle-based rather than heuristic-based. The log(pages/1) change is more suitable for back-patching because it is more conservative, being asymptotic with the previous behavior at the low end. But I don't think that the case for that previous behavior was ever all that strong. If we really want a heuristic to give a bonus to partial indexes, maybe we should explicitly give them a bonus, rather than penalizing ordinary indexes. maybe something like bonus = 0.05 * (reltuples-indextuples)/reltuples Cheers, Jeff >
Re: [HACKERS] configure.in and setproctitle/optreset problem
On Mon, 2012-12-17 at 18:02 +0100, Christoph Berg wrote: > I have no clue why no one else has seen this bug before, but the > reason for the error seems to be that configure is invoking the > setproctitle test including -ledit. libedit.so is linked to libbsd.so, > which in turn contains setproctitle(), so HAVE_SETPROCTITLE is set > even this is Linux, not BSD. I reported this here: http://archives.postgresql.org/pgsql-bugs/2012-07/msg00127.php The correct fix, IMO/IIRC, is to add LDFLAGS=-Wl,--as-needed before running most of the configure checks, instead of after. -- 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] WIP: index support for regexp search
On Mon, Dec 17, 2012 at 1:16 PM, Alexander Korotkov wrote: > Didn't reproduce it yet. Can you retry it with this line uncommented: > #define TRGM_REGEXP_DEBUG > Then we can see which stage it fails. > Bug is found and fixed in attached patch. -- With best regards, Alexander Korotkov. trgm-regexp-0.9.patch.gz Description: GNU Zip compressed data -- 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] WIP: index support for regexp search
On Tue, December 18, 2012 08:04, Alexander Korotkov wrote: > trgm-regexp-0.9.patch.gz 22 k Hi. I ran the same test again: HEAD versus trgm_regex v6, 7 and 9. In v9 there is some gain but also some regression. It remains a difficult problem... If I get some time in the holidays I'll try to diversify the test program; it is now too simple. Thanks, Erik Rijkers trgmre_6_7_9.txt.gz Description: GNU Zip compressed data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
