Re: [HACKERS] synchronized snapshots
Hi Joachim, On 14/09/2011 05:37, Joachim Wieland wrote: Here is a new version of this patch In a sequence such as this: BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE; INSERT INTO foo VALUES (-1); SELECT pg_export_snapshot(); the row added to "foo" is not visible in the exported snapshot. If that's the desired behaviour, I think it should be mentioned in the documentation. I can make a patched backend die with an assertion failure by trying to export a snapshot after rolling back a transaction which exported a snapshot. Seems like no cleanup is done at transaction abort. I think that trying to import a snapshot that doesn't exist deserves a better error message. There's currently no way for the user to know that the snapshot didn't exist, other than looking at the SQLSTATE (22023), and even that doesn't tell me a whole lot without looking at the manual. Finally, the comment in ImportSnapshot() still mentions the old syntax. Other than these four problems, the patch looks good. -- Marko Tiikkajahttp://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] Updated version of pg_receivexlog
On Tue, Aug 16, 2011 at 9:32 AM, Magnus Hagander wrote: > Here's an updated version of pg_receivexlog, that should now actually > work (it previously failed miserably when a replication record crossed > a WAL file boundary - something which I at the time could not properly > reproduce, but when I restarted my work on it now could easily > reproduce every time :D). > > It also contains an update to pg_basebackup that allows it to stream > the transaction log in the background while the backup is running, > thus reducing the need for wal_keep_segments (if the client can keep > up, it should eliminate the need completely). > reviewing this... i found useful pg_receivexlog as an independent utility, but i'm not so sure about the ability to call it from pg_basebackup via --xlog option. this is because pg_receivexlog will continue streaming even after pg_basebackup if it's called independently but not in the other case so the use case for --xlog seems more narrow and error prone (ie: you said that it reduces the need for wal_keep_segments *if the client can keep up*... how can we know that before starting pg_basebackup?) pg_receivexlog worked good in my tests. pg_basebackup with --xlog=stream gives me an already recycled wal segment message (note that the file was in pg_xlog in the standby): FATAL: could not receive data from WAL stream: FATAL: requested WAL segment 0001005C has already been removed haven't read all the code in the detail but seems fine to me in other things: do we need to include src/bin/pg_basebackup/.gitignore in the patch? -- Jaime Casanova www.2ndQuadrant.com Professional PostgreSQL: Soporte 24x7 y capacitación -- 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_regress input/output directory option
Hi all, Why are there no options in_regress to specify the directory where input and output data are located? Such options would bring more flexibility when running regressions without make check/installcheck for an external application. Is there a particular reason for that? Or do you think that pg_regress should be only used with make check? Regards, -- Michael Paquier http://michael.otacoo.com
Re: [HACKERS] pg_upgrade automatic testing
Tom Lane wrote: > Bruce Momjian writes: > > I propose I just remove the 8.4 > > test and always allow toast table names not to match --- the oids are > > still checked and are preserved. > > +1. You'll still make the check for non-toast tables, of course? Yes, only toast tables will skip the check. Proposed patch attached. -- Bruce Momjian http://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + diff --git a/contrib/pg_upgrade/info.c b/contrib/pg_upgrade/info.c new file mode 100644 index e41ab2b..7b1ab36 *** a/contrib/pg_upgrade/info.c --- b/contrib/pg_upgrade/info.c *** gen_db_file_maps(DbInfo *old_db, DbInfo *** 57,69 old_db->db_name, old_rel->reloid, new_rel->reloid); /* ! * In pre-8.4, TOAST table names change during CLUSTER; in >= 8.4 ! * TOAST relation names always use heap table oids, hence we cannot ! * check relation names when upgrading from pre-8.4. */ if (strcmp(old_rel->nspname, new_rel->nspname) != 0 || ! ((GET_MAJOR_VERSION(old_cluster.major_version) >= 804 || ! strcmp(old_rel->nspname, "pg_toast") != 0) && strcmp(old_rel->relname, new_rel->relname) != 0)) pg_log(PG_FATAL, "Mismatch of relation names in database \"%s\": " "old name \"%s.%s\", new name \"%s.%s\"\n", --- 57,73 old_db->db_name, old_rel->reloid, new_rel->reloid); /* ! * TOAST table names initially match the heap pg_class oid. ! * However, in pre-8.4, TOAST table names change during CLUSTER, and ! * in pre-9.0, TOAST table names change during ALTER TABLE. Because ! * an 8.3 or 8.4 system might be upgraded to 9.0 and then 9.1 (and ! * still have a mismatch between toast table name and heap oid), ! * we can't use the old cluster version to know if all toast ! * table names match. Hence we don't check a match of toast table ! * names. */ if (strcmp(old_rel->nspname, new_rel->nspname) != 0 || ! (strcmp(old_rel->nspname, "pg_toast") != 0 && strcmp(old_rel->relname, new_rel->relname) != 0)) pg_log(PG_FATAL, "Mismatch of relation names in database \"%s\": " "old name \"%s.%s\", new name \"%s.%s\"\n", -- 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] fix for pg_upgrade
panam wrote: > Hi Bruce, > > here is the whole dump (old DB): > http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt Wow, that is interesting. I see this in the dump output: -- For binary upgrade, must preserve relfilenodes SELECT binary_upgrade.set_next_heap_relfilenode('465783'::pg_catalog.oid); SELECT binary_upgrade.set_next_toast_relfilenode('465786'::pg_catalog.oid); SELECT binary_upgrade.set_next_index_relfilenode('465788'::pg_catalog.oid); CREATE TABLE accounts ( guid character varying(32) NOT NULL, name character varying(2048) NOT NULL, account_type character varying(2048) NOT NULL, commodity_guid character varying(32), commodity_scu integer NOT NULL, non_std_scu integer NOT NULL, parent_guid character varying(32), code character varying(2048), description character varying(2048), hidden integer, placeholder integer ); and it is clearly saying the oid/relfilenode should be 465783, but your 9.1 query shows: C:\Program Files\PostgreSQL\9.1\bin>psql -c "select * from pg_class where oid = 465783 or oid = 16505;" -p 5433 -U postgres relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relpersistence | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions --+--+-+---+--+---+-+---+--+---+---+---+-+-++-+--+---+++-+++--++ accounts | 2200 | 16507 | 0 |16417 | 0 | 16505 | 0 |0 | 0 | 16508 | 0 | t | f | p | r | 11 | 0 | f | t | f | f | f | 3934366 || (1 row) and 9.0 says correctly 465783: C:\Program Files\PostgreSQL\9.0\bin>psql -c "select * from pg_class where oid = 465783 or oid = 16505;" -p 5432 -U postgres relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace | relpages | reltuples | reltoastrelid | reltoastidxid | relhasindex | relisshared | relistemp | relkind | relnatts | relchecks | relhasoids | relhaspkey | relhasexclusion | relhasrules | relhastriggers | relhassubclass | relfrozenxid | relacl | reloptions --+--+-+---+--+---+-+---+--+---+---+---+-+-+---+-+--+---+++-+-+++--++ accounts | 465781 | 465785 | 0 | 456619 | 0 | 465783 | 0 |3 | 122 |465786 | 0 | t | f | f | r | 11 | 0 | f | t | f | f | f | f | 3934366 || (1 row) It is as though the system ignoring the set_next_heap_relfilenode() call, but I don't see how that could happen. I don't see any other 'accounts' table in that dump. My only guess at this point is that somehow the -b/IsBinaryUpgrade flag is not being processed or regognized, and hence the binary_upgrade 'set' routines are not working. Is this 9.1 final or later? Can you turn on debug mode and send me the pg_upgrade log file that is generated? I am going go look for the pg_ctl -o '-b' flag. Are all databases/objects failing or just this one? -- 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] pg_upgrade automatic testing
Bruce Momjian writes: > I propose I just remove the 8.4 > test and always allow toast table names not to match --- the oids are > still checked and are preserved. +1. You'll still make the check for non-toast tables, of course? 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_upgrade automatic testing
Bruce Momjian wrote: > Peter Eisentraut wrote: > > 8.4 -> master upgrade fails like this: > > > > Restoring user relation files > > Mismatch of relation names in database "regression": old name > > "pg_toast.pg_toast_27437", new name "pg_toast.pg_toast_27309" > > Failure, exiting > > > > This has been 100% reproducible for me. > > I can now reproduce this failure and will research the cause, probably > not before next week though. :-( What is interesting is that loading > the regression tests from an SQL dump does not show the failure, but > running the regression tests and then upgrading does. OK, I found time to research this and I think I have a fix. The problem is caused by an ALTER TABLE in 8.4 not preserving a toast table name that matches the heap oid. Below you can see that 8.4 does not preserve this, while 9.0 does: 8.4 --- test=> CREATE TABLE test5(aa TEXT, bb TEXT); CREATE TABLE test=> INSERT INTO test5 VALUES ('123', '323'); INSERT 0 1 test=> ALTER TABLE test5 ALTER COLUMN aa TYPE INTEGER USING bit_length(aa); ALTER TABLE test=> SELECT oid, reltoastrelid FROM pg_class WHERE relname = 'test5'; oid | reltoastrelid ---+--- ---> 16406 | 16415 (1 row) test=> SELECT relname FROM pg_class WHERE oid = 16415; relname pg_toast_16412 <--- (1 row) 9.0 --- test=> CREATE TABLE test5(aa TEXT, bb TEXT); CREATE TABLE test=> INSERT INTO test5 VALUES ('123', '323'); INSERT 0 1 test=> ALTER TABLE test5 ALTER COLUMN aa TYPE INTEGER USING bit_length(aa); ALTER TABLE test=> SELECT oid, reltoastrelid FROM pg_class WHERE relname = 'test5'; oid | reltoastrelid ---+--- ---> 16409 | 16418 (1 row) test=> SELECT relname FROM pg_class WHERE oid = 16418; relname pg_toast_16409 <--- (1 row) We must have fixed this in 9.0 and I missed it. Anyway, the pg_upgrade code already assumes pre-8.4 doesn't have stable toast names: /* * In pre-8.4, TOAST table names change during CLUSTER; in >= 8.4 * TOAST relation names always use heap table oids, hence we cannot * check relation names when upgrading from pre-8.4. */ if (strcmp(old_rel->nspname, new_rel->nspname) != 0 || ((GET_MAJOR_VERSION(old_cluster.major_version) >= 804 || strcmp(old_rel->nspname, "pg_toast") != 0) && strcmp(old_rel->relname, new_rel->relname) != 0)) pg_log(PG_FATAL, "Mismatch of relation names in database \"%s\": " "old name \"%s.%s\", new name \"%s.%s\"\n", old_db->db_name, old_rel->nspname, old_rel->relname, new_rel->nspname, new_rel->relname); Looking at this code now, I realize it is wrong even without the 8.4 ALTER issue. If someone uses pg_upgrade to go from 8.3 to 8.4, they would then still have the toast table name mismatch when going to 9.0, so the test in itself is wrong anyway. I propose I just remove the 8.4 test and always allow toast table names not to match --- the oids are still checked and are preserved. The current code is just too conservative and throws an error during upgrade (but not during check mode) when it shouldn't. This code only exists in 9.1 and HEAD. -- 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] Online base backup from the hot-standby
On Wed, Sep 28, 2011 at 8:10 AM, Steve Singer wrote: > This is the test procedure I'm trying today, I wasn't able to reproduce the > crash. What I was doing the other day was similar but I can't speak to > unintentional differences. Thanks for the info! I tried your test case three times, but was not able to reproduce the issue, too. BTW, I created the shell script (attached) which runs your test scenario and used it for the test. If the issue will happen again, please feel free to share the information about it. I will diagnose it. > It looks like data3 is still pulling files with the recovery command after > it sees the touch file (is this expected behaviour?) Yes, that's expected behavior. After the trigger file is found, PostgreSQL tries to replay all available WAL files in pg_xlog directory and archive one. So, if there is unreplayed archived WAL file at that time, PostgreSQL tries to pull it by calling the recovery command. And, after WAL replay is done, PostgreSQL tries to re-fetch the last replayed WAL record in order to identify the end of replay location. So, if the last replayed record is included in the archived WAL file, it's pulled by the recovery command. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center test.sh Description: Bourne shell script -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [PATCH] Log crashed backend's query v2
Florian Pflug writes: > On Sep28, 2011, at 00:19 , Marti Raudsepp wrote: >> (I'm still not sure what "adt" means) > I always assumed it stood for "abstract data type". Yeah, that's what I think too. Over time it's been used to hold most code that is a SQL-callable function, many of which are not directly connected to any SQL datatype. Not sure if it's worth trying to clean that up. Another annoying thing is that "adt" should probably have been directly under src/backend/ --- dropping it under utils/ seems just weird for a category that is going to hold a ton of code. (I had once had some hope that git would allow us to move code around more easily, but my experiments with back-patching after code movement have convinced me that it doesn't work any better for that than CVS. So I'm not in favor of massive code rearrangements just to make the source tree structure cleaner.) 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_upgrade - add config directory setting
On Tue, Sep 27, 2011 at 04:13:41PM -0700, Steve Crawford wrote: > It would perhaps be useful to add optional --old-confdir and > --new-confdir parameters to pg_upgrade. If these parameters are absent > then pg_upgrade would work as it does now and assume that the config > files are in the datadir. > > The reason for this suggestion is that packages for Ubuntu (and I > suppose Debian and possibly others) place the config files in a > different directory than the data files. > > The Ubuntu packaging, for example, puts all the configuration files in > /etc/postgresql/VERSION/main/. > > If I set the data-directories to /var/lib/postgresql/VERSION/main then > pg_upgrade complains about missing config files. > > If I set the data directories to /etc/postgresql/VERSION/main/ then > pg_upgrade complains that the "base" subdirectory is missing. > > Temporarily symlinking postgresql.conf and pg_hba.conf from the config > directory to the data directory allowed the upgrade to run successfully > but is a bit more kludgey and non-obvious. > > Cheers, > Steve I was just about to submit this suggestion. We do the same on Gentoo, as a default anyway. (Users can pick their own locations for the configuration files and data directories.) It would simplify the upgrade process by eliminating two to four steps. (Symlink/copy configuration files in /etc/postgresql-${SLOT} to /var/lib/postgresql-${SLOT}, same to $version++, pg_upgrade, remove symlinks.) -- Mr. Aaron W. Swenson Pseudonym: TitanOfOld Gentoo Developer pgpA97Tt6jV2d.pgp Description: PGP signature
Re: [HACKERS] [PATCH] Addition of some trivial auto vacuum logging
Alvaro Herrera writes: > Excerpts from Royce Ausburn's message of mar sep 27 21:28:26 -0300 2011: >> Tom's suggestion looks like it's less trivial that I can do just yet, but >> I'll take a look and ask for help if I need it. > It's not that difficult. Just do a search on "git log > src/backend/postmaster/pgstat.c" for patches that add a new column > somewhere. Yeah, I was just about to say the same thing. Find a previous patch that adds a feature similar to what you have in mind, and crib like mad. We've added enough stats counters over time that you should have several models to work from. 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] contrib/sepgsql regression tests are a no-go
On Tue, Sep 27, 2011 at 6:30 PM, Tom Lane wrote: >>> I have not touched the documentation, either. One thing I'd like to do >>> is adjust both the SGML documentation and the hints printed by the >>> script to uniformly use "sudo ...root-privileged-command..." rather than >>> recommending use of "su". > >> I think that's your own preference showing. How about just telling >> people to run the commands as root without specifying how they should >> accomplish that? > > Well, maybe, but it seems hard to do without being verbose. If you just > say > > $ sudo blah blah blah > > the meaning is obvious (or if it isn't, you got no business playing with > SELinux anyway), and you can easily include, or not, the "sudo" part when > copying and pasting the command. Right now we've got things like > > $ cd .../contrib/sepgsql > $ make -f /usr/share/selinux/devel/Makefile > $ su > # semodule -u sepgsql-regtest.pp > # semodule -l | grep sepgsql > sepgsql-regtest 1.03 > > What I'd prefer is > > $ cd .../contrib/sepgsql > $ make -f /usr/share/selinux/devel/Makefile > $ sudo semodule -u sepgsql-regtest.pp > $ sudo semodule -l | grep sepgsql > sepgsql-regtest 1.03 > > If I have to break up the recipe with annotations like "run this part as > root" and then "these commands no longer need root", I don't think > that's going to be an improvement over either of the above. Fair enough, I'm not going to get bent out of shape about it. There's some aesthetic value in the way you're proposing, and anyone who is doing this ought to know enough to make the details of how you write it out mostly irrelevant. -- 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] [PATCH] Addition of some trivial auto vacuum logging
Excerpts from Royce Ausburn's message of mar sep 27 21:28:26 -0300 2011: > Thanks for the tips guys. > > Just a question: is the utility great enough to warrant me working further > on this? I have no real desire to implement this particular feature -- I > simply saw an opportunity to cut my teeth on something easy. I'd be happy to > find something on the TODO list instead if this feature isn't really > worthwhile. First patches are always going to be small things. If you try to tackle something too large, chances are you'll never finish, despair utterly and throw yourself off a nearby bridge. Surely it's better to set realistic goals, start small and build slowly from there. > Tom's suggestion looks like it's less trivial that I can do just yet, but > I'll take a look and ask for help if I need it. It's not that difficult. Just do a search on "git log src/backend/postmaster/pgstat.c" for patches that add a new column somewhere. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 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] [PATCH] Addition of some trivial auto vacuum logging
* Royce Ausburn (royce...@inomial.com) wrote: > Just a question: is the utility great enough to warrant me working further > on this? I have no real desire to implement this particular feature -- I > simply saw an opportunity to cut my teeth on something easy. I'd be happy to > find something on the TODO list instead if this feature isn't really > worthwhile. Seeing as how it's already got one committer willing to consider it (and that one tends to go the other direction on new features..), I'd definitely say it's worthwhile. That doesn't mean it's guaranteed to get in, but I'd put the probability above 75% given that feedback. That's pretty good overall. :) > Tom's suggestion looks like it's less trivial that I can do just yet, but > I'll take a look and ask for help if I need it. Don't let the notion of fiddling with the catalogs (system tables) discourage you.. It's really not all *that* bad. What you will need to figure out (and which I don't recall offhand..) is if you can just update those catalogs directly from VACUUM or if you need to go through the statistics collecter (which involves a bit of UDP communication, but hopefully we've abstracted that out enough that you won't have to deal with it directly really..). Looking at an existing example case where VACUUM is doing something that updates the stat tables (such as under the 'ANALYZE' option) will help out a lot, I'm sure. Thanks, Stephen signature.asc Description: Digital signature
Re: [HACKERS] [PATCH] Addition of some trivial auto vacuum logging
Thanks for the tips guys. Just a question: is the utility great enough to warrant me working further on this? I have no real desire to implement this particular feature -- I simply saw an opportunity to cut my teeth on something easy. I'd be happy to find something on the TODO list instead if this feature isn't really worthwhile. Tom's suggestion looks like it's less trivial that I can do just yet, but I'll take a look and ask for help if I need it. Cheers! --Royce On 28/09/2011, at 4:42 AM, Kevin Grittner wrote: > Royce Ausburn wrote: > >> As this is my first patch to postgresql, I'm expecting I've done > < something wrong. Please if you want me to fix something up, or >> just go away please say so ;) I appreciate that this is a trivial >> patch, and perhaps doesn't add value except for my very specific >> use case* feel free to ignore it =) > > Thanks for offering this to the community. I see you've already > gotten feedback on the patch, with a suggestion for a different > approach. Don't let that discourage you -- very few patches get in > without needing to be modified based on review and feedback. > > If you haven't already done so, please review this page and its > links: > > http://www.postgresql.org/developer/ > > Of particular interest is the Developer FAQ: > > http://wiki.postgresql.org/wiki/Developer_FAQ > > You should also be aware of the development cycle, which (when not > in feature freeze for beta testing) involves alternating periods of > focused development and code review (the latter called CommitFests): > > http://wiki.postgresql.org/wiki/CommitFest > > We are now in the midst of a CF, so it would be great if you could > join in that as a reviewer. Newly submitted patches should be > submitted to the "open" CF: > > http://commitfest.postgresql.org/action/commitfest_view/open > > You might want to consider what Tom said and submit a modified patch > for the next review cycle. > > Welcome! > > -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Mismatch of relation names: pg_toast.pg_toast_nnn during pg_upgrade from 8.4 to 9.1
Hi - I've had no problem upgrading copies our qa databases (which are backed up and restored with pg_dump/pg_restore) but have run into the same problem each time I try to upgrade a copy of our production database (backed up and restored via PITR). After verifying a successful restore and vacuum analyze, I ran a successful pg_upgrade check run with: pg_upgrade -b /usr/local/pgsql-8.4.2/bin -B /usr/local/pgsql-9.1.0/bin -c -d /data/pgsql/prod-84 -D /data/pgsql/prod-91 -G /home/postgres/pg_upgrade_prod.check.debug -k -l /home/postgres/pg_upgrade_prod.check.log -p 5435 -P 5436 -v Then when I ran the actual pg_upgrade: pg_upgrade -b /usr/local/pgsql-8.4.2/bin -B /usr/local/pgsql-9.1.0/bin -d /data/pgsql/prod-84 -D /data/pgsql/prod-91 -G /home/postgres/pg_upgrade_prod.debug -k -l /home/postgres/pg_upgrade_prod.log -p 5435 -P 5436 -v It fails at this stage: Restoring user relation files linking /data/pgsql/prod-84/base/11564/2613 to /data/pgsql/prod-91/base/12698/12570 linking /data/pgsql/prod-84/base/11564/2683 to /data/pgsql/prod-91/base/12698/12572 Mismatch of relation names: database "prod1", old rel pg_toast.pg_toast_54542379, new rel pg_toast.pg_toast_16735 Failure, exiting These are the log files generated: -rw-rw-r-- 1 postgres postgres 0 Sep 27 12:47 pg_upgrade_prod.check.debug -rw-rw-r-- 1 postgres postgres 8524 Sep 27 13:27 pg_upgrade_prod.check.log -rw-rw-r-- 1 postgres postgres 0 Sep 27 13:31 pg_upgrade_prod.debug -rw-rw-r-- 1 postgres postgres 2374 Sep 27 14:12 pg_upgrade_dump_globals.sql -rw-rw-r-- 1 postgres postgres 257696 Sep 27 14:12 pg_upgrade_dump_db.sql -rw-rw-r-- 1 postgres postgres 260092 Sep 27 14:12 pg_upgrade_dump_all.sql -rw-rw-r-- 1 postgres postgres 75413 Sep 27 14:12 pg_upgrade_prod.log I don't see any helpful (to me) explanation within them, but after some searching ran across this comment in the relevant section of source: 00059 /* 00060 * In pre-8.4, TOAST table names change during CLUSTER; in >= 8.4 00061 * TOAST relation names always use heap table oids, hence we cannot 00062 * check relation names when upgrading from pre-8.4. 00063 */ At this point I still don't understand the underlying issue. Our databases were migrated in place (linked) with pg_migrator from 8.3 to 8.4 could that be the cause or related? Where would you recommend I look to discover a cause and hopefully a solution? Thanks in advance, Jamie -- 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_upgrade - add config directory setting
It would perhaps be useful to add optional --old-confdir and --new-confdir parameters to pg_upgrade. If these parameters are absent then pg_upgrade would work as it does now and assume that the config files are in the datadir. The reason for this suggestion is that packages for Ubuntu (and I suppose Debian and possibly others) place the config files in a different directory than the data files. The Ubuntu packaging, for example, puts all the configuration files in /etc/postgresql/VERSION/main/. If I set the data-directories to /var/lib/postgresql/VERSION/main then pg_upgrade complains about missing config files. If I set the data directories to /etc/postgresql/VERSION/main/ then pg_upgrade complains that the "base" subdirectory is missing. Temporarily symlinking postgresql.conf and pg_hba.conf from the config directory to the data directory allowed the upgrade to run successfully but is a bit more kludgey and non-obvious. Cheers, Steve -- 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] Online base backup from the hot-standby
On 11-09-26 10:56 PM, Fujii Masao wrote: Looks weired. Though the WAL record starting from 0/6000298 was read successfully, then re-fetch of the same record fails at the end of recovery. One possible cause is the corruption of archived WAL file. What restore_command on the standby and archive_command on the master are you using? Could you confirm that there is no chance to overwrite archive WAL files in your environment? I tried to reproduce this problem several times, but I could not. Could you provide the test case which reproduces the problem? This is the test procedure I'm trying today, I wasn't able to reproduce the crash. What I was doing the other day was similar but I can't speak to unintentional differences. I have my master server data port=5439 wal_level=hot_standby archive_mode=on archive_command='cp -i %p /usr/local/pgsql92git/archive/%f' hot_standby=on I then run select pg_start_backup('foo'); $ rm -r ../data2 $ cp -r ../data ../data2 $ rm ../data2/postmaster.pid select pg_stop_backup(); I edit data2/postgresql.conf so port=5438 I commented out archive_mode and archive_command (or at least today I did) recovery.conf is standby_mode='on' primary_conninfo='host=127.0.0.1 port=5439 user=ssinger dbname=test' restore_command='cp /usr/local/pgsql92git/archive/%f %p' I then start up the second cluster. On it I run select pg_start_backup('1'); $ rm -r ../data3 $ rm -r ../archive2 $ cp -r ../data2 ../data3 $ cp ../data2/global/pg_control ../data3/global select pg_stop_backup(); I edit ../data2/postgresql.conf port=5437 archive_mode=on # (change requires restart) archive_command='cp -i %p /usr/local/pgsql92git/archive2/%f' recovery.conf is standby_mode='on' primary_conninfo='host=127.0.0.1 port=5439 user=ssinger dbname=test' restore_command='cp /usr/local/pgsql92git/archive/%f %p' trigger_file='/tmp/3' $ postgres -D ../data3 The first time I did this postgres came up quickly. $ touch /tmp/3 worked fine. I then stopped data3 $ rm -r ../data3 on data 2 I run pg_start_backup('1') $ cp -r ../data2 ../data3 $ cp ../data2/global/pg_control ../data3/global select pg_stop_backup() # on data2 $ rm ../data3/postmaster.pid vi ../data3/postgresql.conf # same changes as above for data3 vi ../data3/recovery.conf # same as above for data 3 postgres -D ../data3 This time I got ./postgres -D ../data3 LOG: database system was interrupted while in recovery at log time 2011-09-27 22:04:17 GMT HINT: If this has occurred more than once some data might be corrupted and you might need to choose an earlier recovery target. LOG: entering standby mode cp: cannot stat `/usr/local/pgsql92git/archive/0001000C': No such file or directory LOG: redo starts at 0/C20 LOG: record with incorrect prev-link 0/958 at 0/CB0 cp: cannot stat `/usr/local/pgsql92git/archive/0001000C': No such file or directory LOG: streaming replication successfully connected to primary FATAL: the database system is starting up FATAL: the database system is starting up LOG: consistent recovery state reached at 0/CE8 LOG: database system is ready to accept read only connections In order to get the database to come in read only mode I manually issued a checkpoint on the master (data) shortly after the checkpoint command the data3 instance went to read only mode. then touch /tmp/3 trigger file found: /tmp/3 FATAL: terminating walreceiver process due to administrator command cp: cannot stat `/usr/local/pgsql92git/archive/0001000C': No such file or directory LOG: record with incorrect prev-link 0/9000298 at 0/C0002F0 cp: cannot stat `/usr/local/pgsql92git/archive/0001000C': No such file or directory LOG: redo done at 0/C000298 cp: cannot stat `/usr/local/pgsql92git/archive/0001000C': No such file or directory cp: cannot stat `/usr/local/pgsql92git/archive/0002.history': No such file or directory LOG: selected new timeline ID: 2 cp: cannot stat `/usr/local/pgsql92git/archive/0001.history': No such file or directory LOG: archive recovery complete LOG: database system is ready to accept connections LOG: autovacuum launcher started It looks like data3 is still pulling files with the recovery command after it sees the touch file (is this expected behaviour?) $ grep archive ../data3/postgresql.conf #wal_level = minimal# minimal, archive, or hot_standby #archive_mode = off# allows archiving to be done archive_mode=on archive_command='cp -i %p /usr/local/pgsql92git/archive2/%f' I have NOT been able to make postgres crash during a recovery (today). It is *possible* that on some of my runs the other day I had skipped changing the archive command on data3 to write to archive2 instead of archive. I have also today not been able to get it to attempt to restore the same WAL file twice. If a base backup is in progress on a recovery database an
Re: [HACKERS] [PATCH] Log crashed backend's query v2
On Sep28, 2011, at 00:19 , Marti Raudsepp wrote: > (I'm still not sure what "adt" means) I always assumed it stood for "abstract data type". Most of the files in this directory seem to correspond to an SQL-level data type like intX, varchar, tsquery, ..., and contain the I/O functions for that type, plus some supporting operations and functions. Over time, it seems that this directory was also used for SQL-level functions not directly related to a single type, like windowfuncs.c and pgstatfuncs.c. The fact that ri_triggers.c lives there also might be a relict from times where you'd create FK constraint with CREATE CONSTRAINT TRIGGER and specified one of the functions from ri_triggers.c as the procedure to execute. best regards, Florian Pflug -- 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] Hot Backup with rsync fails at pg_clog if under load
On Sep23, 2011, at 21:10 , Robert Haas wrote: > So the actual error message in the last test was: > > 2011-09-21 13:41:05 CEST FATAL: could not access status of transaction > 1188673 > > ...but we can't tell if that was before or after nextXid, which seems > like it would be useful to know. > > If Linas can rerun his experiment, but also capture the output of > pg_controldata before firing up the standby for the first time, then > we'd able to see that information. Hm, wouldn't pg_controldata quite certainly show a nextId beyond the clog if copied after pg_clog/*? Linas, could you capture the output of pg_controldata *and* increase the log level to DEBUG1 on the standby? We should then see nextXid value of the checkpoint the recovery is starting from. FWIW, I've had a few more theories about what's going on, but none survived after looking at the code. My first guess was that there maybe are circumstances under which the nextId from the control file, instead of the one from the pre-backup checkpoint, ends up becoming the standby's nextXid. But there doesn't seem to be a way for that to happen. My next theory was that something increments nextIdx before StartupCLOG(). The only possible candidate seems to be PrescanPreparedTransactions(), which does increment nextXid if it's smaller than some sub-xid of a prepared xact. But we only call that before StartupCLOG() if we're starting from a shutdown checkpoint, which shouldn't be the case for the OP. I also checked what rsync does when a file vanishes after rsync computed the file list, but before it is sent. rsync 3.0.7 on OSX, at least, complains loudly, and doesn't sync the file. It BTW also exits non-zero, with a special exit code for precisely that failure case. best regards, Florian Pflug -- 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] contrib/sepgsql regression tests are a no-go
Robert Haas writes: > On Tue, Sep 27, 2011 at 3:39 PM, Tom Lane wrote: >> Accordingly, the attached patch does what I suggested above, namely dike >> out the Makefile's knowledge of how to run the regression tests and put >> it into the chkselinuxenv script. > Seems fine. The rename is definitely needed. We may want to > back-patch this into 9.1 to avoid the headache of dealing with this > for 5 years. I'm definitely gonna back-patch it, because otherwise I'll be carrying it as a RHEL and Fedora patch for that long ;-) >> I have not touched the documentation, either. One thing I'd like to do >> is adjust both the SGML documentation and the hints printed by the >> script to uniformly use "sudo ...root-privileged-command..." rather than >> recommending use of "su". > I think that's your own preference showing. How about just telling > people to run the commands as root without specifying how they should > accomplish that? Well, maybe, but it seems hard to do without being verbose. If you just say $ sudo blah blah blah the meaning is obvious (or if it isn't, you got no business playing with SELinux anyway), and you can easily include, or not, the "sudo" part when copying and pasting the command. Right now we've got things like $ cd .../contrib/sepgsql $ make -f /usr/share/selinux/devel/Makefile $ su # semodule -u sepgsql-regtest.pp # semodule -l | grep sepgsql sepgsql-regtest 1.03 What I'd prefer is $ cd .../contrib/sepgsql $ make -f /usr/share/selinux/devel/Makefile $ sudo semodule -u sepgsql-regtest.pp $ sudo semodule -l | grep sepgsql sepgsql-regtest 1.03 If I have to break up the recipe with annotations like "run this part as root" and then "these commands no longer need root", I don't think that's going to be an improvement over either of the above. 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] [PATCH] Log crashed backend's query v2
On Sat, Sep 24, 2011 at 22:57, Marti Raudsepp wrote: > However, I now realize that it does make sense to write a separate > simpler function for the crashed backend case with no > vbeentry->st_changecount check loops, no checkUser, etc. That would be > more robust and easier to review. I implemented this now, but I'm not convinced anymore that it's the right way to go. I'm duplicating some amount of code that could be subject to bitrot in the future since this code path won't be excercised often. But I'll let the reviewers decide. Is there a sane way to regression-test backend crashes? > I propsed replacing non-ASCII characters with '?' earlier. This is also in. I created a new function in backend/utils/adt/ascii.c. It didn't quite fit in because all other functions in this file are dealing with Datums, but I couldn't find a better place. (I'm still not sure what "adt" means) Regards, Marti From 0f46bb1357bafbe940e7df8fce38c01e2237f57e Mon Sep 17 00:00:00 2001 From: Marti Raudsepp Date: Wed, 28 Sep 2011 00:46:48 +0300 Subject: [PATCH] Log crashed backend's query (activity string) The crashing query is often a good starting point in debugging the cause, and much more easily accessible than core dumps. We're extra-paranoid in reading the activity buffer since it might be corrupt. All non-ASCII characters are replaced with '?' Example output: LOG: server process (PID 31451) was terminated by signal 9: Killed DETAIL: Running query: DO LANGUAGE plpythonu 'import os;os.kill(os.getpid(),9)' --- src/backend/postmaster/pgstat.c | 59 +++ src/backend/postmaster/postmaster.c | 17 +++--- src/backend/utils/adt/ascii.c | 31 ++ src/include/pgstat.h|1 + src/include/utils/ascii.h |1 + 5 files changed, 104 insertions(+), 5 deletions(-) diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c index eb9adc8..812bf04 100644 --- a/src/backend/postmaster/pgstat.c +++ b/src/backend/postmaster/pgstat.c @@ -58,6 +58,7 @@ #include "storage/pg_shmem.h" #include "storage/pmsignal.h" #include "storage/procsignal.h" +#include "utils/ascii.h" #include "utils/guc.h" #include "utils/memutils.h" #include "utils/ps_status.h" @@ -2747,6 +2748,64 @@ pgstat_get_backend_current_activity(int pid, bool checkUser) return ""; } +/* -- + * pgstat_get_backend_current_activity() - + * + * Return a string representing the current activity of the backend with + * the specified PID. Like the function above, but reads shared memory with + * the expectation that it may be corrupt. + * + * This function is only intended to be used by postmaster to report the + * query that crashed the backend. In particular, no attempt is made to + * follow the correct concurrency protocol when accessing the + * BackendStatusArray. But that's OK, in the worst case we get a corrupted + * message. We also must take care not to trip on ereport(ERROR). + * + * Note: return strings for special cases match pg_stat_get_backend_activity. + * -- + */ +const char * +pgstat_get_crashed_backend_activity(int pid) +{ + volatile PgBackendStatus *beentry; + int i; + + beentry = BackendStatusArray; + for (i = 1; i <= MaxBackends; i++) + { + if (beentry->st_procpid == pid) + { + /* Read pointer just once, so it can't change after validation */ + const char *activity = beentry->st_activity; + char *buffer; + + /* + * We can't access activity pointer before we verify that it + * falls into BackendActivityBuffer. To make sure that the + * string's ending is contained within the buffer, the string + * can start at offset (MaxBackends - 1) at most. + */ + if (activity < BackendActivityBuffer || +activity > (BackendActivityBuffer + + (MaxBackends - 1) * pgstat_track_activity_query_size)) +return ""; + + if (*(activity) == '\0') +return ""; + + buffer = (char *) palloc(pgstat_track_activity_query_size); + ascii_safe_strncpy(buffer, activity, + pgstat_track_activity_query_size); + + return buffer; + } + + beentry++; + } + + /* PID not found */ + return ""; +} /* * Local support functions follow diff --git a/src/backend/postmaster/postmaster.c b/src/backend/postmaster/postmaster.c index 94b57fa..9ba622c 100644 --- a/src/backend/postmaster/postmaster.c +++ b/src/backend/postmaster/postmaster.c @@ -2763,6 +2763,8 @@ HandleChildCrash(int pid, int exitstatus, const char *procname) static void LogChildExit(int lev, const char *procname, int pid, int exitstatus) { + const char *activity = pgstat_get_crashed_backend_activity(pid); + if (WIFEXITED(exitstatus)) ereport(lev, @@ -2770,7 +2772,8 @@ LogChildExit(int lev, const char *procname, int pid, int exitstatus) translator: %s is a noun phrase describing a child process, such as "server process" */ (errmsg("%s (PID %d) exited with exi
Re: [HACKERS] contrib/sepgsql regression tests are a no-go
On Tue, Sep 27, 2011 at 3:39 PM, Tom Lane wrote: > I wrote: >> I think it should be possible to still use all the existing testing >> infrastructure if the check/test script does something like >> make REGRESS="label dml misc" check > > I've now worked through the process of actually running the sepgsql > regression tests, and I must say that I had no idea how utterly invasive > they were --- the idea that they could ever be part of a default "make > check" sequence is even more ridiculous than I thought before. > > Accordingly, the attached patch does what I suggested above, namely dike > out the Makefile's knowledge of how to run the regression tests and put > it into the chkselinuxenv script. It would be appropriate to rename that > script to something like "test_sepgsql", but I didn't do that yet to > reduce the displayed size of the patch. Seems fine. The rename is definitely needed. We may want to back-patch this into 9.1 to avoid the headache of dealing with this for 5 years. > I have not touched the documentation, either. One thing I'd like to do > is adjust both the SGML documentation and the hints printed by the > script to uniformly use "sudo ...root-privileged-command..." rather than > recommending use of "su". I don't like the latter because it makes it > less than clear exactly which commands require root, encourages you to > forget to switch out of root mode, and IMO is against local policy on > any well-run box. I recognize however that that might be mostly my > own preferences showing --- what do others think? I think that's your own preference showing. How about just telling people to run the commands as root without specifying how they should accomplish that? -- 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] random isolation test failures
Alvaro Herrera writes: > Excerpts from Tom Lane's message of mar sep 27 01:11:39 -0300 2011: >> Hmm, is that really an appropriate fix? I'm worried that it might mask >> event-ordering differences that actually are significant. > In the attached, it only affects the case where there is one blocking > command and another command that unblocks it; this is only exercised by > the much-beaten fk-deadlock cases. If either of the steps fails with a > deadlock error, it is reported identically, i.e. the error message is > emitted as > "error in s1u1 s2u1: ERROR: deadlock detected" > So the deadlock could have been detected in either s1u1 or s2u1; we > don't really care. Hmm. For the case of "deadlock detected", we actually don't *want* to care because the infrastructure is such that either process might report it. So I agree that this is a good fix for that case. I'm just worried whether it will obscure other situations where it's important to know which command failed. But if you're convinced there aren't any, fine. 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] contrib/sepgsql regression tests are a no-go
I wrote: > I think it should be possible to still use all the existing testing > infrastructure if the check/test script does something like > make REGRESS="label dml misc" check I've now worked through the process of actually running the sepgsql regression tests, and I must say that I had no idea how utterly invasive they were --- the idea that they could ever be part of a default "make check" sequence is even more ridiculous than I thought before. Accordingly, the attached patch does what I suggested above, namely dike out the Makefile's knowledge of how to run the regression tests and put it into the chkselinuxenv script. It would be appropriate to rename that script to something like "test_sepgsql", but I didn't do that yet to reduce the displayed size of the patch. I have not touched the documentation, either. One thing I'd like to do is adjust both the SGML documentation and the hints printed by the script to uniformly use "sudo ...root-privileged-command..." rather than recommending use of "su". I don't like the latter because it makes it less than clear exactly which commands require root, encourages you to forget to switch out of root mode, and IMO is against local policy on any well-run box. I recognize however that that might be mostly my own preferences showing --- what do others think? Comments? regards, tom lane diff --git a/contrib/sepgsql/Makefile b/contrib/sepgsql/Makefile index 033c41a..140419a 100644 *** a/contrib/sepgsql/Makefile --- b/contrib/sepgsql/Makefile *** OBJS = hooks.o selinux.o uavc.o label.o *** 5,15 database.o schema.o relation.o proc.o DATA_built = sepgsql.sql ! REGRESS = label dml misc ! REGRESS_PREP = check_selinux_environment ! REGRESS_OPTS = --launcher $(top_builddir)/contrib/sepgsql/launcher ! ! EXTRA_CLEAN = -r tmp *.pp sepgsql-regtest.if sepgsql-regtest.fc ifdef USE_PGXS PG_CONFIG = pg_config --- 5,13 database.o schema.o relation.o proc.o DATA_built = sepgsql.sql ! # Note: because we don't tell the Makefile there are any regression tests, ! # we have to clean those result files explicitly ! EXTRA_CLEAN = -r $(pg_regress_clean_files) tmp *.pp sepgsql-regtest.if sepgsql-regtest.fc ifdef USE_PGXS PG_CONFIG = pg_config *** include $(top_srcdir)/contrib/contrib-gl *** 23,28 endif SHLIB_LINK += -lselinux - - check_selinux_environment: - @$(top_builddir)/contrib/sepgsql/chkselinuxenv "$(bindir)" "$(datadir)" --- 21,23 diff --git a/contrib/sepgsql/chkselinuxenv b/contrib/sepgsql/chkselinuxenv index a7c81b2..2eeeb67 100755 *** a/contrib/sepgsql/chkselinuxenv --- b/contrib/sepgsql/chkselinuxenv *** *** 1,11 #!/bin/sh # ! # SELinux environment checks to ensure configuration of the operating system ! # satisfies prerequisites to run regression test. ! # If incorrect settings are found, this script suggest user a hint. # ! PG_BINDIR="$1" ! PG_DATADIR="$2" echo echo "== checking selinux environment ==" --- 1,18 #!/bin/sh # ! # Run the sepgsql regression tests, after making a lot of environmental checks ! # to try to ensure that the SELinux environment is set up appropriately and ! # the database is configured correctly. # ! # Note that this must be run against an installed Postgres database. ! # There's no equivalent of "make check", and that wouldn't be terribly useful ! # since much of the value is in checking that you installed sepgsql into ! # your database correctly. ! # ! # This must be run in the contrib/sepgsql directory of a Postgres build tree. ! # ! ! PG_BINDIR=`pg_config --bindir` echo echo "== checking selinux environment ==" *** fi *** 224,230 echo "found ${NUM}" # ! # check complete - # ! echo "" ! exit 0 --- 231,242 echo "found ${NUM}" # ! # checking complete - let's run the tests # ! ! echo ! echo "== running sepgsql regression tests ==" ! ! make REGRESS="label dml misc" REGRESS_OPTS="--launcher ./launcher" installcheck ! ! # exit with the exit code provided by "make" -- 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] random isolation test failures
Excerpts from Tom Lane's message of mar sep 27 01:11:39 -0300 2011: > > Alvaro Herrera writes: > > I just tweaked isolationtester so that it collects the error messages > > and displays them all together at the end of the test. After seeing it > > run, I didn't like it -- I think I prefer something more local, so that > > in the only case where we call try_complete_step twice in the loop, we > > report any errors in either. AFAICS this would make both expected cases > > behave identically in test output. > > Hmm, is that really an appropriate fix? I'm worried that it might mask > event-ordering differences that actually are significant. In the attached, it only affects the case where there is one blocking command and another command that unblocks it; this is only exercised by the much-beaten fk-deadlock cases. If either of the steps fails with a deadlock error, it is reported identically, i.e. the error message is emitted as "error in s1u1 s2u1: ERROR: deadlock detected" So the deadlock could have been detected in either s1u1 or s2u1; we don't really care. The way error messages are reported in all the other cases is not changed, and these do not have a prefix; so if anything were to behave differently, we would find out because a spurious prefix would appear. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support isolation-fix-2.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] [PATCH] Addition of some trivial auto vacuum logging
Royce Ausburn wrote: > As this is my first patch to postgresql, I'm expecting I've done < something wrong. Please if you want me to fix something up, or > just go away please say so ;) I appreciate that this is a trivial > patch, and perhaps doesn't add value except for my very specific > use case* feel free to ignore it =) Thanks for offering this to the community. I see you've already gotten feedback on the patch, with a suggestion for a different approach. Don't let that discourage you -- very few patches get in without needing to be modified based on review and feedback. If you haven't already done so, please review this page and its links: http://www.postgresql.org/developer/ Of particular interest is the Developer FAQ: http://wiki.postgresql.org/wiki/Developer_FAQ You should also be aware of the development cycle, which (when not in feature freeze for beta testing) involves alternating periods of focused development and code review (the latter called CommitFests): http://wiki.postgresql.org/wiki/CommitFest We are now in the midst of a CF, so it would be great if you could join in that as a reviewer. Newly submitted patches should be submitted to the "open" CF: http://commitfest.postgresql.org/action/commitfest_view/open You might want to consider what Tom said and submit a modified patch for the next review cycle. Welcome! -Kevin -- 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] [PATCH] Addition of some trivial auto vacuum logging
Royce Ausburn writes: > The attached patch adds extra detail the the existing autovacuum log message > that is emitted when the log_autovacuum_min_duration threshold is met, > exposing the unremovable dead tuple count similar to what you get from VACUUM > VERBOSE. > Sample log output (my addition in bold): > LOG: automatic vacuum of table "test.public.test": index scans: 0 > pages: 0 removed, 5 remain > tuples: 0 removed, 1000 remain, 999 dead but not removable > system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec This proposal seems rather ill-designed. In the first place, these numbers are quite unrelated to vacuum duration, and in the second place, most people who might need the info don't have that setting turned on anyway. I wonder whether it wouldn't be more helpful to have a pg_stat_all_tables column that reports the number of unremovable tuples as of the last vacuum. I've been known to object to more per-table stats counters in the past on the basis of space required, but perhaps this one would be worth its keep. 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] [PATCH] Addition of some trivial auto vacuum logging
Hi all, I spent a bit of today diagnosing a problem where long held transactions were preventing auto vacuum from doing its job. Eventually I had set log_autovacuum_min_duration to 0 to see what was going on. Unfortunately it wasn't until I tried a VACUUM VERBOSE that I found there were dead tuples not being removed. Had the unremoveable tuple count been included in the autovacuum log message life would have been a tiny bit easier. I've been meaning for a while to dabble in postgres, so I thought this might be a good trivial thing for me to improve. The attached patch adds extra detail the the existing autovacuum log message that is emitted when the log_autovacuum_min_duration threshold is met, exposing the unremovable dead tuple count similar to what you get from VACUUM VERBOSE. Sample log output (my addition in bold): LOG: automatic vacuum of table "test.public.test": index scans: 0 pages: 0 removed, 5 remain tuples: 0 removed, 1000 remain, 999 dead but not removable system usage: CPU 0.00s/0.00u sec elapsed 0.00 sec My patch adds another member to the LVRelStats struct named undeleteable_dead_tuples. lazy_scan_heap() sets undeleteable_dead_tuples to the value of lazy_scan_heap()'s local variable "nkeep", which is the same variable that is used to emit the VACUUM VERBOSE unremoveable dead row count. As this is my first patch to postgresql, I'm expecting I've done something wrong. Please if you want me to fix something up, or just go away please say so ;) I appreciate that this is a trivial patch, and perhaps doesn't add value except for my very specific use case… feel free to ignore it =) --Royce diff --git a/src/backend/commands/vacuumlazy.c b/src/backend/commands/vacuumlazy.c index cf8337b..12f03d7 100644 --- a/src/backend/commands/vacuumlazy.c +++ b/src/backend/commands/vacuumlazy.c @@ -91,6 +91,7 @@ typedef struct LVRelStats double scanned_tuples; /* counts only tuples on scanned pages */ double old_rel_tuples; /* previous value of pg_class.reltuples */ double new_rel_tuples; /* new estimated total # of tuples */ + double undeleteable_dead_tuples; /* count of dead tuples not yet removeable */ BlockNumber pages_removed; double tuples_deleted; BlockNumber nonempty_pages; /* actually, last nonempty page + 1 */ @@ -256,7 +257,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, ereport(LOG, (errmsg("automatic vacuum of table \"%s.%s.%s\": index scans: %d\n" "pages: %d removed, %d remain\n" - "tuples: %.0f removed, %.0f remain\n" + "tuples: %.0f removed, %.0f remain, %.0f dead but not removable\n" "system usage: %s", get_database_name(MyDatabaseId), get_namespace_name(RelationGetNamespace(onerel)), @@ -266,6 +267,7 @@ lazy_vacuum_rel(Relation onerel, VacuumStmt *vacstmt, vacrelstats->rel_pages, vacrelstats->tuples_deleted, new_rel_tuples, + vacrelstats->undeleteable_dead_tuples, pg_rusage_show(&ru0; } } @@ -829,6 +831,7 @@ lazy_scan_heap(Relation onerel, LVRelStats *vacrelstats, /* save stats for use later */ vacrelstats->scanned_tuples = num_tuples; vacrelstats->tuples_deleted = tups_vacuumed; + vacrelstats->undeleteable_dead_tuples = nkeep; /* now we can compute the new value for pg_class.reltuples */ vacrelstats->new_rel_tuples = vac_estimate_reltuples(onerel, false,
Re: [HACKERS] Support UTF-8 files with BOM in COPY FROM
Peter Eisentraut writes: > Alternative consideration: We could allow this in CSV format if we made > users quote the first value if it starts with a BOM. This might be a > reasonable way to get MS compatibility. I don't think we can get away with a retroactive restriction on the contents of data files. If we're going to do this at all, I still think an explicit BOM option for COPY, to either eat (and require) a BOM on input or emit a BOM on output, would be the sanest way. None of the "automatic" approaches seem safe to me. 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] Postgresql parser
Excerpts from Florian Pflug's message of mar sep 27 08:28:00 -0300 2011: > On Sep27, 2011, at 10:44 , andurkar wrote: > > Currently I am working on Postgresql... I need to study the gram.y and > > scan.l parser files...since I want to do some qery modification. Can anyone > > please help me to understand the files. What should I do ? Is there any > > documentation available ? > > scan.l defines the lexer, i.e. the algorithm that splits a string (containing > an SQL statement) into a stream of tokens. A token is usually a single word > (i.e., doesn't contain spaces but is delimited by spaces), but can also be > a whole single or double-quoted string for example. The lexer is basically > defined in terms of regular expressions which describe the different token > types. Seemed a good answer so I added it to the developer's faq http://wiki.postgresql.org/wiki/Developer_FAQ#I_need_to_do_some_changes_to_query_parsing._Can_you_succintly_explain_the_parser_files.3F Feel free to edit. -- Álvaro Herrera The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 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] Support UTF-8 files with BOM in COPY FROM
On mån, 2011-09-26 at 21:49 +0300, Peter Eisentraut wrote: > If I store a BOM in row 1, column 1 of my table, because, > well, maybe it's an XML document or something, then it needs to be > able to survive a copy out and in. The only way we could proceed with > this would be if we prohibited BOMs in all user-data. Alternative consideration: We could allow this in CSV format if we made users quote the first value if it starts with a BOM. This might be a reasonable way to get MS compatibility. -- 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] the big picture for index-only scans
On Sun, Aug 21, 2011 at 3:13 AM, Heikki Linnakangas wrote: > PS. Robert, the LOCKING section in the header comment of visibilitymap.c is > out-of-date: it claims that the VM bit is cleared after releasing the lock > on the heap page. Fixed, along with your other observation a couple of emails upthread. -- 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] CUDA Sorting
Hey hackers, I'm still having problems reading the values of the columns in tuplesort.c, in order to understand how to port this to CUDA. Should I use the heap_getattr macro to read them? 2011/9/24 Hannu Krosing > On Mon, 2011-09-19 at 10:36 -0400, Greg Smith wrote: > > On 09/19/2011 10:12 AM, Greg Stark wrote: > > > With the GPU I'm curious to see how well > > > it handles multiple processes contending for resources, it might be a > > > flashy feature that gets lots of attention but might not really be > > > very useful in practice. But it would be very interesting to see. > > > > > > > The main problem here is that the sort of hardware commonly used for > > production database servers doesn't have any serious enough GPU to > > support CUDA/OpenCL available. The very clear trend now is that all > > systems other than gaming ones ship with motherboard graphics chipsets > > more than powerful enough for any task but that. I just checked the 5 > > most popular configurations of server I see my customers deploy > > PostgreSQL onto (a mix of Dell and HP units), and you don't get a > > serious GPU from any of them. > > > > Intel's next generation Ivy Bridge chipset, expected for the spring of > > 2012, is going to add support for OpenCL to the built-in motherboard > > GPU. We may eventually see that trickle into the server hardware side > > of things too. > > > > I've never seen a PostgreSQL server capable of running CUDA, and I don't > > expect that to change. > > CUDA sorting could be beneficial on general server hardware if it can > run well on multiple cpus in parallel. GPU-s being in essence parallel > processors on fast shared memory, it may be that even on ordinary RAM > and lots of CPUs some CUDA algorithms are a significant win. > > and then there is non-graphics GPU availabe on EC2 > > Cluster GPU Quadruple Extra Large Instance > > 22 GB of memory > 33.5 EC2 Compute Units (2 x Intel Xeon X5570, quad-core “Nehalem” > architecture) > 2 x NVIDIA Tesla “Fermi” M2050 GPUs > 1690 GB of instance storage > 64-bit platform > I/O Performance: Very High (10 Gigabit Ethernet) > API name: cg1.4xlarge > > It costs $2.10 per hour, probably a lot less if you use the Spot > Instances. > > > -- > > Greg Smith 2ndQuadrant USg...@2ndquadrant.com Baltimore, MD > > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.us > > > > > > > > -- > 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] [v9.1] sepgsql - userspace access vector cache
On Fri, Sep 2, 2011 at 12:38 PM, Kohei Kaigai wrote: >> I've committed this, but I still think it would be helpful to revise >> that comment. The turn "boosted up" is not very precise or >> informative. Could you submit a separate, comment-only patch to >> improve this? >> > I tried to put more detailed explanation about the logic of do { ... } while > loop of sepgsql_avc_check_valid and the cache field of new security label to > be switched on execution of the procedure. Is it helpful? I edited this and committed it along with a bunch of further wordsmithing on the comments in that file. Please let me know if you see any isuses. Thanks, -- 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] heap_update temporary release of buffer lock
On Tue, Sep 20, 2011 at 3:47 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Excerpts from Robert Haas's message of mar sep 20 16:04:03 -0300 2011: On 20.09.2011 20:42, Alvaro Herrera wrote: > I notice that heap_update releases the buffer lock, after checking the > HeapTupleSatifiesUpdate result, and before marking the tuple as updated, > to pin the visibility map page -- heapam.c lines 2638ff in master branch. > >>> The easiest fix seems to be (as you suggest) to add "goto l2" after >>> reacquiring the lock. Can we get away with (and is there any benefit >>> to) doing that only if xmax has changed? > >> Hmm ... I think that works, and it would suit my purposes too. Note >> this means you have to recheck infomask too (otherwise consider that >> IS_MULTI could be set the first time, and not set the second time, and >> that makes the Xmax have a different meaning.) OTOH if you just do it >> always, it is simpler. > > Yeah, I think a "goto l2" is correct and sufficient. As the comment > already notes, this need not be a high-performance path, so why spend > extra code (with extra risk of bugs)? Done. -- 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] Postgresql parser
On Sep27, 2011, at 10:44 , andurkar wrote: > Currently I am working on Postgresql... I need to study the gram.y and > scan.l parser files...since I want to do some qery modification. Can anyone > please help me to understand the files. What should I do ? Is there any > documentation available ? scan.l defines the lexer, i.e. the algorithm that splits a string (containing an SQL statement) into a stream of tokens. A token is usually a single word (i.e., doesn't contain spaces but is delimited by spaces), but can also be a whole single or double-quoted string for example. The lexer is basically defined in terms of regular expressions which describe the different token types. gram.y defines the grammar (the syntactical structure) of SQL statements, using the tokens generated by the lexer as basic building blocks. The grammar is defined in BNF notation. BNF resembles regular expressions but works on the level of tokens, not characters. Also, patterns (called rules or productions in BNF) are named, and may be recursive, i.e. use themselves as sub-patters. The actual lexer is generated from scan.l by a tool called flex. You can find the manual at http://flex.sourceforge.net/manual/ The actual parser is generated from gram.y by a tool called bison. You can find the manual at http://www.gnu.org/s/bison/. Beware, though, that you'll have a rather steep learning curve ahead of you if you've never used flex or bison before. best regards, Florian Pflug -- 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] bug of recovery?
On Sep27, 2011, at 07:59 , Heikki Linnakangas wrote: > On 27.09.2011 00:28, Florian Pflug wrote: >> On Sep26, 2011, at 22:39 , Tom Lane wrote: >>> It might be worthwhile to invoke XLogCheckInvalidPages() as soon as >>> we (think we) have reached consistency, rather than leaving it to be >>> done only when we exit recovery mode. >> >> I believe we also need to prevent the creation of restart points before >> we've reached consistency. > > Seems reasonable. We could still allow restartpoints when the hash table is > empty, though. And once we've reached consistency, we can throw an error > immediately in log_invalid_page(), instead of adding the entry in the hash > table. That mimics the way the rm_safe_restartpoint callbacks work, which is good. Actually, why don't we use that machinery to implement this? There's currently no rm_safe_restartpoint callback for RM_XLOG_ID, so we'd just need to create one that checks whether invalid_page_tab is empty. best regards, Florian Pflug -- 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] fix for pg_upgrade
Hi Bruce, here is the whole dump (old DB): http://postgresql.1045698.n5.nabble.com/file/n4844725/dump.txt dump.txt Regards, panam -- View this message in context: http://postgresql.1045698.n5.nabble.com/fix-for-pg-upgrade-tp3411128p4844725.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] Postgresql parser
On Tue, Sep 27, 2011 at 11:44, andurkar wrote: > Hello, > Currently I am working on Postgresql... I need to study the gram.y and > scan.l parser files...since I want to do some qery modification. Can anyone > please help me to understand the files. What should I do ? Is there any > documentation available ? > > Regards, > Aditi. > What kind of modifications do you want to do? regards, Kerem KAT -- 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] unite recovery.conf and postgresql.conf
On Mon, Sep 26, 2011 at 7:45 PM, Peter Eisentraut wrote: > On sön, 2011-09-25 at 12:58 -0400, Tom Lane wrote: >> And it's not like we don't break configuration file >> contents in most releases anyway, so I really fail to see why this one >> has suddenly become sacrosanct. > > Well, there is a slight difference. Changes in postgresql.conf > parameter names and settings are adjusted automatically for me by my > package upgrade script. If we, say, changed the names of recovery.conf > parameters, I'd have to get a new version of my $SuperReplicationTool. > That tool could presumably look at PG_VERSION and put a recovery.conf > with the right spellings in the right place. > > But if we completely change the way the replication configuration > interacts, it's not clear that a smooth upgrade is possible without > significant effort. That said, I don't see why it wouldn't be possible, > but let's design with upgradability in mind, instead of claiming that we > have never supported upgrades of this kind anyway. Currently recovery.conf has two roles: #1. recovery.conf is used as a trigger file to enable archive recovery. At the end of recovery, recovery.conf is renamed to recovery.done. #2. recovery.conf is used as a configuration file for recovery parameters. Which role do you think we should support in 9.2 because of the backward compatibility? Both? Unless I misunderstand the discussion so far, Tom and Robert (and I) agree to get rid of both. Simon seems to agree to remove only the former role, but not the latter. How about you? If you agree to remove the former, too, let's focus on the discussion about whether the latter role should be supported in 9.2. Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Postgresql parser
Hello, Currently I am working on Postgresql... I need to study the gram.y and scan.l parser files...since I want to do some qery modification. Can anyone please help me to understand the files. What should I do ? Is there any documentation available ? Regards, Aditi. -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgresql-parser-tp4844522p4844522.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] contrib/sepgsql regression tests are a no-go
2011/9/26 Tom Lane : > Kohei KaiGai writes: >> How about this fix on regression test of sepgsql? > > IMO, the fundamental problem with the sepgsql regression tests is that > they think they don't need to play by the rules that apply to every > other PG regression test. I don't think this patch is fixing that > problem; it's just coercing pgxs.mk to assist in not playing by the > rules, and adding still more undocumented complexity to the PGXS > mechanisms in order to do so. > > If we have to have a nonstandard command for running the sepgsql > regression tests, as it seems that we do, we might as well just make > that an entirely local affair within contrib/sepgsql. > Are you suggesting to make a command/script to check OS environment and run its own regression test without touching existing pg_regress framework, even if it just utilizes existing options? It seems to me re-inventment of a wheel Thanks, -- KaiGai Kohei -- 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] [v9.2] Fix Leaky View Problem
2011/9/26 Robert Haas : > On Mon, Sep 12, 2011 at 3:31 PM, Kohei KaiGai wrote: >> The Part-2 tries to tackles a leaky-view scenarios by functions with >> very tiny cost >> estimation value. It was same one we had discussed in the commitfest-1st. >> It prevents to launch functions earlier than ones come from inside of views >> with >> "security_barrier" option. >> >> The Part-3 tries to tackles a leaky-view scenarios by functions that >> references >> one side of join loop. It prevents to distribute qualifiers including >> functions without >> "leakproof" attribute into relations across security-barrier. > > I took a little more of a look at this today. It has major problems. > > First, I get compiler warnings (which you might want to trap in the > future by creating src/Makefile.custom with COPT=-Werror when > compiling). > > Second, the regression tests fail on the select_views test. > > Third, it appears that the part2 patch works by adding an additional > traversal of the entire query tree to standard_planner(). I don't > think we want to add overhead to the common case where no security > views are in use, or at least it had better be very small - so this > doesn't seem acceptable to me. > The reason why I put a walker routine on the head of standard_planner() was that previous revision of this patch tracked strict depth of sub-queries, not a number of times to go through security barrier. The policy to count-up depth of qualifier was changed according to Noad's suggestion is commit-fest 1st, however, the suitable position to mark the depth value was kept. I'll try to revise the suitable position to track the depth value. It seems to me one candidate is pull_up_subqueries during its recursive call, because this patch originally set FromExpr->security_barrier here. In addition to the two points you mentioned above, I'll update this patch as follows: * Use CREATE [SECURITY] VIEW statement, instead of reloptions. the flag shall be stored within a new attribute of pg_class, and it shall be kept when an existing view getting replaced. * Utilize RangeTblEntry->relid, instead of rte->security_barrier, and the flag shall be pulled from the catalog on planner stage. * Documentation and Regression test. Thanks, -- KaiGai Kohei -- 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] bug of recovery?
On Tue, Sep 27, 2011 at 6:54 AM, Heikki Linnakangas wrote: > I think you're mixing this up with the multi-page page split operations in > b-tree. This is different from that. What the "invalid_page_tab" is for is > the situation where you for example, insert to a page on table X, and later > table X is dropped, and then you crash. On WAL replay, you will see the > insert record, but the file for the table doesn't exist, because the table > was dropped. In that case we skip the insert, note what happened in > invalid_page_tab, and move on with recovery. When we see the later record to > drop the table, we know it was OK that the file was missing earlier. But if > we don't see it before end of recovery, we PANIC, because then the file > should've been there. OK, yes, I see. Thanks. -- 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