Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Tom Lane a écrit : > Bruce Momjian <[EMAIL PROTECTED]> writes: >> Alvaro Herrera wrote: >>> Does this mean that if I commit something in these days to those >>> branches, it will not show up in the releases? > >> It certainly will show up if you do it before the packagers pull their >> CVS copies. > > No, it will show up if you do it before Marc "cvs tag"s the release. > Which I am currently thinking shouldn't happen till Friday or so. > Is it Marc's job to sync the translation on PostgreSQL CVS with those from the pgtranslation project ? I remember this is a part of the build process but I don't know who does this. Thanks. Regards. -- Guillaume. http://www.postgresqlfr.org/ http://dalibo.com/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Guillaume Lelarge wrote: Tom Lane a écrit : Bruce Momjian <[EMAIL PROTECTED]> writes: Alvaro Herrera wrote: Does this mean that if I commit something in these days to those branches, it will not show up in the releases? It certainly will show up if you do it before the packagers pull their CVS copies. No, it will show up if you do it before Marc "cvs tag"s the release. Which I am currently thinking shouldn't happen till Friday or so. Is it Marc's job to sync the translation on PostgreSQL CVS with those from the pgtranslation project ? I remember this is a part of the build process but I don't know who does this. No, thats Peter. I'm not sure if he usually does it for point releases though. Regards, Dave ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Dave Page a écrit : > Guillaume Lelarge wrote: >> Tom Lane a écrit : >>> Bruce Momjian <[EMAIL PROTECTED]> writes: Alvaro Herrera wrote: > Does this mean that if I commit something in these days to those > branches, it will not show up in the releases? It certainly will show up if you do it before the packagers pull their CVS copies. >>> No, it will show up if you do it before Marc "cvs tag"s the release. >>> Which I am currently thinking shouldn't happen till Friday or so. >>> >> >> Is it Marc's job to sync the translation on PostgreSQL CVS with those >> from the pgtranslation project ? I remember this is a part of the build >> process but I don't know who does this. > > No, thats Peter. > > I'm not sure if he usually does it for point releases though. > I hope he already did it for minor releases. If not, I wonder why pgtranslation project has major branches. I really need to see this happening at least on these minor releases. I worked a lot on the .po files from 7.4, 8.0, 8.1 and 8.2, fixing some translation's issues, "fine-tuning" the translations. Why isn't there some kind of automatic process that sync the translations, once per month for example ? if this can be done, I can work on this. -- Guillaume. http://www.postgresqlfr.org/ http://dalibo.com/ ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] pgcrypto related backend crash on solaris 10/x86_64
Zdenek Kotala wrote: Marko Kreen wrote: On 9/11/07, Zdenek Kotala <[EMAIL PROTECTED]> wrote: Marko Kreen wrote: This is crashing because of the crippled OpenSSL on some version of Solaris. Zdenek Kotala posted a workaround for that, I am cleaning it but have not found the time to finalize it. I'll try to post v03 of Zdenek's patch ASAP. However, I guess there still will be a problem with regression tests, because pg_crypto will reports error in case when user tries to use stronger cipher, but it generates diff between expected and real output. I don't know if is possible select different output based on test if strong crypto is installed or not. Maybe some magic in Makefile/Configure. Test should be: # ldd /usr/postgres/8.2/lib/pgcrypto.so | grep libcrypto_extra # libcrypto_extra.so.0.9.8 => (file not found) if output contains (file not found) library is not installed or not in path (/usr/sfw/lib). Failing regression tests are fine - it is good if user can easily see that the os is broken. But if build machine still complain about problem we can easily overlook another problems. There are two possible solution 1) modify reg test or 2) recommend to install crypto package on all affected build machine. Anyway I plan to add some mention into solaris FAQ when we will have final patch. I also think It should be good to mention in pg_crypto README or add comment into regression test expected output file which will be visible in regression.diff. well in my opinion we should simply fail regression(not crash like we do now) in case we have to deal with such a crippled openssl installation. Adding information about that issue to the Solaris FAQ seems also like a good thing. Stefan ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] A Silly Idea for Vertically-Oriented Databases
Ühel kenal päeval, E, 2007-09-10 kell 11:01, kirjutas Alvaro Herrera: > Mark Mielke wrote: > > Simon Riggs wrote: > >> ISTM we would be able to do this fairly well if we implemented > >> Index-only columns. i.e. columns that don't exist in the heap, only in > >> an index. > >> Taken to the extreme, all columns could be removed from the heap and > >> placed in an index(es). Only the visibility information would remain on > >> the heap. > >> > > Wouldn't the extreme be - even the visibility information is in the index? > > Maybe we could extract the visibility information and put it in a > storage separate from the heap. A seqscan would be a bit slower (have > to check the heap and the visibility storage) but some index scans could > be faster (can check the index and visibility storage, skipping the > heap), and updates would be faster too (append into the heap, update > visibility storage). This would allow something closer to index-only > scans. Of course, the key is to allow efficient lookup of visibility > info given a TID. > > Has this been explored before? I wrote to this list about a vague plan for doing it some months ago. And I don't think that even seqscans need to be slower, as at least for typical Data Warehouse application the visibility info can be compressed a lot by even a simple RLE compression and thus you just do the sequscan like this while 1: get next visible range read in the visible range, without checking each tuple I suspect that this can be made much more cache-friendly than current scheme. (A Guess: I even go as far as to claim that separate visibility heap _may_ be more cahce friendly even uncompressed, so if we have every second tuple deleted, it will still be faster (at least for bigger tuples) to look up visibility in a dense visibility array and then access just the visible tuples. Here even separate heap may be not needed, just rearranging the heap structure to have visibility info in the beginning of page together with tuple pointers may be a win ) Another bonus of separate visibility heap is that it can be maintained separately, that is it can be shuffled around, CID's cleaned up, compressed, etc. much more effectively than current one, which, among other things will make VACUUM much more efficient, as neither all-visible or all-dead pages need to be visited at all. It can probably be made to serve as both DSM and VACUUM map also. And it can be used for finding "best" insert spot for CLUSTERing-preserving inserts/updates. On subject of vertical or column-per-heap storage I see the biggest obstacle to be te use of TID-s as tuple identifiers, as the "real" TIDs (pageno32:tupleno:16) will be different for each column. So if we have a table with a 1 kb text column, we will also have to store just 8 ints per page in the int column if we want to preserve unique TID->tuple mapping. What we could do of course is start defining TID as just a 48-bit tuple number and then have some rules for finding the PAGE:NR "tid" from that it would be easy for fixed-size columns (PAGE:NR) = (TID/items_per_page:TID mod items_per_page) but more complicated for VARLEN fields. All the ideas I've had sofar for solving this have quite a lot of downsides. -- Hannu ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
On Tue, 2007-09-11 at 18:56 -0400, Bruce Momjian wrote: > Preparations are being made for PostgreSQL releases 8.2.5, 8.1.10, > 8.0.14, 7.4.18, 7.3.20. The CVS branches are nearly ready. The > releases will happen sometime early next week. The packagers have been > contacted. The following bug fix has not yet been applied to CVS http://archives.postgresql.org/pgsql-patches/2007-06/msg00100.php It needs to be applied to CVS HEAD and REL8_2_STABLE. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] HOT breaks CLUSTER, a bit
Tom Lane wrote: > While editing Bruce's README.HOT documentation I ran into a problem. > I had idly added the following text after thinking about the different > sorts of snapshots we use: > > --- > Another unpleasant consequence is that it is no longer very meaningful to > use SnapshotAny in an index scan: if the index was created more recently > than the last vacuum, it's possible that some of the visited tuples do not > match the index entry they are linked to. This does not seem to be a > fatal objection in practice, since there are few users of SnapshotAny and > they all use seqscans. > --- > > However, a quick grep to confirm that turned up a problem: CLUSTER uses > SnapshotAny with an indexscan. This essentially means that CLUSTER > might fetch recently-dead tuples out of order, because it finds them > attached to an index HOT chain that's for a different index key value. > > I think that this is not a fatal objection; the out-of-order-ness is > limited and won't be seen at all by transactions with snapshots > postdating the CLUSTER, and CLUSTER can't guarantee the ordering will > stay pristine for long anyway. But it's a bit worrisome. Does anyone > see a bigger problem here than I do? Hmm. Normally all tuples in a HOT chain have the same index key, but right after CREATE INDEX that might indeed not be true. I think a warning in the comments for SnapshotAny and index_getnext to not use SnapshotAny with index scans (except for CLUSTER) is enough. > BTW, the proposed HOT code in indexam.c that special-cases SnapshotAny > is a crock ... It was written under the assumption that all tuples in a HOT chain have the same index key, which isn't true after CREATE INDEX as you pointed out. Is there something else wrong with it? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] regression tests of dictionaries and Windows
All windows boxes are failed on tsdicts test: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=mastodon&dt=2007-09-12%2007:00:00 http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=skylark&dt=2007-09-12%2003:00:01 http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=baiji&dt=2007-09-11%2022:00:01 with the same error: + ERROR: could not open dictionary file "C:/pgBuild/BuildFarm/BuildRoot/HEAD/pgsql.3204/src/test/regress/./tmp_check/install/share/tsearch_data/ispell_sample.dict": No such file or directory Does anybody know a needed magic to fix that? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] regression tests of dictionaries and Windows
Teodor Sigaev wrote: All windows boxes are failed on tsdicts test: http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=mastodon&dt=2007-09-12%2007:00:00 http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=skylark&dt=2007-09-12%2003:00:01 http://www.pgbuildfarm.org/cgi-bin/show_log.pl?nm=baiji&dt=2007-09-11%2022:00:01 with the same error: + ERROR: could not open dictionary file "C:/pgBuild/BuildFarm/BuildRoot/HEAD/pgsql.3204/src/test/regress/./tmp_check/install/share/tsearch_data/ispell_sample.dict": No such file or directory Does anybody know a needed magic to fix that? It's not all Windows boxes, only those building with MSVC. Mingw and Cygwin builds are working fine. The MSVC build process doesn't use make and friends. The required magic has to go in src/tools/msvc/Install.pm. I will look at it later today. cheers andrew ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] regression tests of dictionaries and Windows
Do you mean something like this:
./src/tools/msvc/Install.pm
*** ./src/tools/msvc/Install.pm.origWed Sep 12 16:30:25 2007
--- ./src/tools/msvc/Install.pm Wed Sep 12 16:31:29 2007
***
*** 66,71
--- 66,72
GenerateTimezoneFiles($target,$conf);
GenerateTsearchFiles($target);
CopySetOfFiles('Stopword files',
"src\\backend\\snowball\\stopwords\\*.stop", $target . '/share/tsearch_data/');
+ CopySetOfFiles('Dictionaries sample files',
"src\\backend\\tsearch\\\*_sample.*", $target . '/share/tsearch_data/');
CopyContribFiles($config,$target);
CopyIncludeFiles($target);
The MSVC build process doesn't use make and friends. The required magic
has to go in src/tools/msvc/Install.pm. I will look at it later today.
--
Teodor Sigaev E-mail: [EMAIL PROTECTED]
WWW: http://www.sigaev.ru/
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] regression tests of dictionaries and Windows
Correct, that fixes the problem. I've verified it on my machine, and
committed the patch.
//Magnus
On Wed, Sep 12, 2007 at 04:32:57PM +0400, Teodor Sigaev wrote:
> Do you mean something like this:
> ./src/tools/msvc/Install.pm
> *** ./src/tools/msvc/Install.pm.origWed Sep 12 16:30:25 2007
> --- ./src/tools/msvc/Install.pm Wed Sep 12 16:31:29 2007
> ***
> *** 66,71
> --- 66,72
> GenerateTimezoneFiles($target,$conf);
> GenerateTsearchFiles($target);
> CopySetOfFiles('Stopword files',
> "src\\backend\\snowball\\stopwords\\*.stop", $target .
> '/share/tsearch_data/');
> + CopySetOfFiles('Dictionaries sample files',
> "src\\backend\\tsearch\\\*_sample.*", $target . '/share/tsearch_data/');
> CopyContribFiles($config,$target);
> CopyIncludeFiles($target);
>
>
> >The MSVC build process doesn't use make and friends. The required magic
> >has to go in src/tools/msvc/Install.pm. I will look at it later today.
>
> --
> Teodor Sigaev E-mail: [EMAIL PROTECTED]
>WWW:
>http://www.sigaev.ru/
>
> ---(end of broadcast)---
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [HACKERS] HOT breaks CLUSTER, a bit
"Heikki Linnakangas" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> BTW, the proposed HOT code in indexam.c that special-cases SnapshotAny >> is a crock ... > It was written under the assumption that all tuples in a HOT chain have > the same index key, which isn't true after CREATE INDEX as you pointed > out. Is there something else wrong with it? It's the wrong special case. It should assume that *all* non-MVCC snapshots require walking the whole chain. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] A small mistake in the initial latestCompletedXid idea
Hi When I initially proposed to use the latest *committed* xid as the xmax instead of ReadNewTransactionId(), I believed that this would cause tuples created by a later aborted transaction not to be vacuumed until another transaction (with a higher xid) commits later. The idea was therefore modified to store the latest *completed* xid, instead of the latest committed one. I just realized that my fear was unjustified. AFAICS, VACUUM will aways remove tuples created by aborted transactions, even if the xid is >= OldestXmin. Therefore, I suggest that we rename latestCompletedXid to latestCommittedXid, and update it only on commits. Admittedly, this won't bring any measurable performance benefit in itself (it will slightly reduce the average snapshot size, though), but not doing so might stand in the way of possible future optimizations in that area. I'll submit a patch to the patches list shortly. greetings, Florian Pflug ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Dave Page <[EMAIL PROTECTED]> writes: > Guillaume Lelarge wrote: >> Is it Marc's job to sync the translation on PostgreSQL CVS with those >> from the pgtranslation project ? I remember this is a part of the build >> process but I don't know who does this. > No, thats Peter. Peter usually does it --- in theory any committer could, but he actually knows what to do and the rest of us would have to study ;-) Peter, have you got time to sync the translation files before Friday? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] buildfarm failure after ICC configure change
Jeremy Drake <[EMAIL PROTECTED]> writes: > On Wed, 12 Sep 2007, Tom Lane wrote: >> Argh! Can someone quote chapter and verse from the ICC manual about >> this? I was just following what Sergey said was the approved spelling >> of the switch ... > Here are the docs for the two options. So why have they got two switches for what seems to be the same thing? I think I'll just revert the configure change. We know the gcc-compatible spelling works ... regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Tom Lane wrote: Dave Page <[EMAIL PROTECTED]> writes: Guillaume Lelarge wrote: Is it Marc's job to sync the translation on PostgreSQL CVS with those from the pgtranslation project ? I remember this is a part of the build process but I don't know who does this. No, thats Peter. Peter usually does it --- in theory any committer could, but he actually knows what to do and the rest of us would have to study ;-) Study or figure it out? If it hasn't already been it should be documented as part of the release process. /D ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Simon Riggs <[EMAIL PROTECTED]> writes: > The following bug fix has not yet been applied to CVS > http://archives.postgresql.org/pgsql-patches/2007-06/msg00100.php Frankly, this looks much more like it creates a bug than fixes one. I have not looked at all of the original thread, but this adds a wart (two warts, really) that seems certain to do the wrong thing in cases other than the one you are thinking of. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Dave Page <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> Peter usually does it --- in theory any committer could, but he actually >> knows what to do and the rest of us would have to study ;-) > Study or figure it out? If it hasn't already been it should be > documented as part of the release process. Well, RELEASE_CHANGES has * Translation updates Translations are kept in the project "pgtranslation" on PgFoundry. 1. Check out the messages module (of the right branch). 2. Check out the admin module. 3. Run "sh .../admin/cp-po .../messages .../pgsql 4. Commit. but it's not real clear (to me) which is "the right branch" and what the ...s signify. It's not a big knowledge gap but I have other things to worry about ... regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] A small mistake in the initial latestCompletedXid idea
"Florian G. Pflug" <[EMAIL PROTECTED]> writes: > Therefore, I suggest that we rename latestCompletedXid to latestCommittedXid, > and update it only on commits. Admittedly, this won't bring any measurable > performance benefit in itself (it will slightly reduce the average snapshot > size, though), but not doing so might stand in the way of possible future > optimizations in that area. This is a bad idea. As you say, it doesn't directly save anything, and the downside is that it may result in RecentGlobalXmin not moving forward. Consider a situation where there's a long string of aborts and nary a commit. latestCommittedXid won't advance, therefore each new transaction continues to compute xmin = xmax = latestCommittedXid+1, and so the window between global xmin and the newest active XIDs gets wider and wider. That puts performance stress on pg_clog and pg_subtrans buffers --- if it goes on long enough, we get into a context swap storm caused by pg_subtrans buffer thrashing. We need to be sure that xmin/xmax move forward when XIDs exit the ProcArray, whether they commit or not. Your post made me think for awhile about whether we really need to serialize aborts at all. From a transactional correctness standpoint I think maybe we don't, but again the difficulty is with xmin tracking. If an aborting xact can remove its XID from ProcArray without locking, then it is possible that two concurrent scans of ProcArray arrive at different xmin values, which means that GetOldestXmin might deliver an incorrectly large answer, and that's fatal. (One of the possible consequences is truncating pg_subtrans too soon, but I believe there are other ones too.) Subtransactions don't affect xmin, of course, so there may be an argument here that we don't have to do this stuff for a subtransaction abort. But I remain unconvinced that optimizing subtransaction abort will really buy a performance gain worth taking any risk for. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] RETURNING and DO INSTEAD ... Intentional or not?
All, A Hibernate developer pointed out the following odd behavior to me in 8.2.1: create table test ( test1 text ); create table test2 ( test_col text ); create rule test_insert as on insert to test do instead insert into test2 values ( NEW.test1 ) RETURNING test2.test_col; postgres=# insert into test values ( 'joe' ); INSERT 0 1 ... no RETURNING. In fact, there doesn't seem to be any way to capture the RETURNING output if you have a DO INSTEAD rule on an insert. Is this intentional, or a bug? -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] A small mistake in the initial latestCompletedXid idea
Tom Lane wrote: "Florian G. Pflug" <[EMAIL PROTECTED]> writes: Therefore, I suggest that we rename latestCompletedXid to latestCommittedXid, and update it only on commits. Admittedly, this won't bring any measurable performance benefit in itself (it will slightly reduce the average snapshot size, though), but not doing so might stand in the way of possible future optimizations in that area. This is a bad idea. As you say, it doesn't directly save anything, and the downside is that it may result in RecentGlobalXmin not moving forward. Consider a situation where there's a long string of aborts and nary a commit. latestCommittedXid won't advance, therefore each new transaction continues to compute xmin = xmax = latestCommittedXid+1, and so the window between global xmin and the newest active XIDs gets wider and wider. That puts performance stress on pg_clog and pg_subtrans buffers --- if it goes on long enough, we get into a context swap storm caused by pg_subtrans buffer thrashing. We need to be sure that xmin/xmax move forward when XIDs exit the ProcArray, whether they commit or not. Hm.. Ok.. I see your point. Maybe we should then make your initial argument against this hold, by adding a check for xid > latestCompletedXid into TransactionIdIsInProgress. That is cheap, might save some unnecessary proc array scanning, and justifies advancing latestCommittedXid during subxact abort (where your argument above doesn't hold, as you say yourself later on). Your post made me think for awhile about whether we really need to serialize aborts at all. From a transactional correctness standpoint I think maybe we don't, but again the difficulty is with xmin tracking. Agreed - For transaction correctness, aborted and in-progress transactions are similar enough that it doesn't matter much in which pot our snapshot puts them. They cases where the difference matters rechecks with TransactionIdIsInProgress anyway and/or waits on the xid's lock. If an aborting xact can remove its XID from ProcArray without locking, then it is possible that two concurrent scans of ProcArray arrive at different xmin values, which means that GetOldestXmin might deliver an incorrectly large answer, and that's fatal. (One of the possible consequences is truncating pg_subtrans too soon, but I believe there are other ones too.) I'm not yet sure if that deviation in the xmin calculations poses any real risk, or not. After all, even the transaction ending up with the larger xmin won't actually see xids between the smaller and the larger xid as in-progress. I haven't yet been able to come up with either a counterexample, or an argument that this is indeed safe. Subtransactions don't affect xmin, of course, so there may be an argument here that we don't have to do this stuff for a subtransaction abort. But I remain unconvinced that optimizing subtransaction abort will really buy a performance gain worth taking any risk for. That depends largely on the workload, I would think. If there is any benefit, I'd expect to see it for workloads involving deeply nested BEGIN/END/EXCEPTION blocks. Especially because we currently roll back each sub-transaction seperatly AFAICS, meaning we might take that exclusive lock many times in short succession. greetings, Florian Pflug ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] RETURNING and DO INSTEAD ... Intentional or not?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Josh Berkus wrote: > All, > > A Hibernate developer pointed out the following odd behavior to me in 8.2.1: > > create table test ( test1 text ); > create table test2 ( test_col text ); > create rule test_insert as on insert to test do instead insert into test2 > values ( NEW.test1 ) RETURNING test2.test_col; > > postgres=# insert into test values ( 'joe' ); > INSERT 0 1 > > ... no RETURNING. In fact, there doesn't seem to be any way to capture the > RETURNING output if you have a DO INSTEAD rule on an insert. Is this > intentional, or a bug? I think this is a side effect of rules. I seem to remember that creating update view with rules is broken in a similar fashion. Sincerely, Joshua D. Drake - -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 24x7/Emergency: +1.800.492.2240 PostgreSQL solutions since 1997 http://www.commandprompt.com/ UNIQUE NOT NULL Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFG6BlhATb/zqfZUUQRAntqAJ9rmCeX7t/23i5NIW1PpWIi8HGm/ACgkaMg k+VQip5jZolm+Xs7BsiZwkw= =hdNt -END PGP SIGNATURE- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] RETURNING and DO INSTEAD ... Intentional or not?
Josh Berkus <[EMAIL PROTECTED]> writes: > A Hibernate developer pointed out the following odd behavior to me in 8.2.1: > create table test ( test1 text ); > create table test2 ( test_col text ); > create rule test_insert as on insert to test do instead insert into test2 > values ( NEW.test1 ) RETURNING test2.test_col; > postgres=# insert into test values ( 'joe' ); > INSERT 0 1 > ... no RETURNING. It would surely be quite broken for an INSERT that has *not* got a returning clause to spit data at you, don't you think? What the RETURNING clause in the rule does is let you define the data that should be returned if the rewritten INSERT had a returning clause to start with. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] RETURNING and DO INSTEAD ... Intentional or not?
Tom, > What the RETURNING clause in the rule does is let you define the data > that should be returned if the rewritten INSERT had a returning clause > to start with. Hmmm. Aha, that works: postgres=# insert into test values ( 'mary' ) returning test1; test1 --- mary So, this should probably be documented to avoid confusion like mine. Will write something up ... -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] RETURNING and DO INSTEAD ... Intentional or not?
Tom Lane wrote: Josh Berkus <[EMAIL PROTECTED]> writes: A Hibernate developer pointed out the following odd behavior to me in 8.2.1: create table test ( test1 text ); create table test2 ( test_col text ); create rule test_insert as on insert to test do instead insert into test2 values ( NEW.test1 ) RETURNING test2.test_col; postgres=# insert into test values ( 'joe' ); INSERT 0 1 ... no RETURNING. It would surely be quite broken for an INSERT that has *not* got a returning clause to spit data at you, don't you think? What the RETURNING clause in the rule does is let you define the data that should be returned if the rewritten INSERT had a returning clause to start with. Sorry - haven't got a CSV download here, or I'd check myself. Does this just allow an INSERT...RETURNING inside the rule, or could it be something like: CREATE RULE ... AS ON INSERT ... DO INSTEAD SELECT f(NEW.test1); -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
[HACKERS] Representation of redirected line pointers in HOT
I find the HOT patch's representation of redirected line pointers pretty klugy. It's got a magic offset number to mean one thing, and a magic length number to mean something else, and the assumption that either of these can't correspond to a real offset or length seems pretty weak. (It would fail if we could have one-byte tuples, which of course is nowhere near reality, but still...) What I'm thinking is that we should instead do this by extending the use of the lp_flags field. lp_flags is two bits, which we currently define as independent LP_USED and LP_DELETE bits, but in fact LP_DELETE is never used in heap pages. (It is used in indexes.) I propose that we redefine lp_flags as having four states, say LP_UNUSED 0 LP_NORMAL 1 LP_REDIRECT 2 LP_DEAD 3 The LP_DEAD state would have slightly different meanings in indexes and heap pages: in an index this would represent an entry that is known dead but hasn't been deleted, whereas in a heap page this would correspond to what the HOT patch calls a "redirect dead" line pointer, that is one that has no associated tuple storage but can't be removed because index entries still link to it. We could make that difference explicit in the line pointer, though: if it still has storage then lp_offset and lp_len point to that storage, and if it doesn't have storage then they are set to zero. UNUSED pointers would also have offset = len = 0, and REDIRECT pointers would have offset = link to next line pointer and len = 0, leading to the general rule that "if it's got storage, len > 0, otherwise len = 0". The above state values are chosen with malice aforethought to match the bit patterns for the LP_USED/LP_DELETE combinations that are actually in use today, so this change is upward compatible, except that I'm not sure how careful we are about setting len = 0 in unused line pointers. This seems hardly any uglier than the way the code stands today, and certainly a lot less ugly than what the current HOT patch proposes. Comments? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] RETURNING and DO INSTEAD ... Intentional or not?
Richard Huxton <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> What the RETURNING clause in the rule does is let you define the data >> that should be returned if the rewritten INSERT had a returning clause >> to start with. > Sorry - haven't got a CSV download here, or I'd check myself. Does this > just allow an INSERT...RETURNING inside the rule, or could it be > something like: > CREATE RULE ... AS ON INSERT ... DO INSTEAD SELECT f(NEW.test1); Well, that's what you do if you want to deliberately break the normal behavior of INSERT, ie, have it fire back data unconditionally. What the rule definition of RETURNING is intended for is to let you write rules that support an updatable view that does the right things, ie INSERT and INSERT RETURNING on the view both do what you'd expect them to do if the view were a plain table. Josh, this *is* documented; see the CREATE RULE reference page for full details, and there's at least passing references here: http://developer.postgresql.org/pgdocs/postgres/rules-update.html#RULES-UPDATE-VIEWS regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] RETURNING and DO INSTEAD ... Intentional or not?
Tom, > Josh, this *is* documented; see the CREATE RULE reference page for full > details, and there's at least passing references here: > http://developer.postgresql.org/pgdocs/postgres/rules-update.html#RULES-UPD >ATE-VIEWS Yeah, it's just hard to find since it's buried in an offhand example in a subsection which is 5 pages long, and the necessity to match up columns and data types in order is not clearly explained. I've submitted what I believe are improvements. I'll note that we currently prevent adding RETURNING to a *conditional* DO INSTEAD rule. This means that if we have a conditional DO INSTEAD rule which inserts into a different table than the final unconditional rule, we'll be RETURNING wrong or empty values. Mind you, that's a pretty extreme corner case. -- Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] [ADMIN] reindexdb hangs
Tom Lane wrote: > Alvaro Herrera <[EMAIL PROTECTED]> writes: > > I am unsure if I should backpatch to 8.1: the code in cluster.c has > > changed, and while it is relatively easy to modify the patch, this is a > > rare bug and nobody has reported it in CLUSTER (not many people clusters > > temp tables, it seems). Should I patch only REINDEX? How far back? > > I'd say go as far back as you can conveniently modify the patch for. > This is a potential data-loss bug (even if only for temporary data) > so we ought to take it seriously. OK, I fixed it all the way back that it was needed: 7.4 for CLUSTER and 8.1 for REINDEX. Before 7.4 there wasn't a database-wide version of CLUSTER. This wasn't a very serious bug in any case, because it would have thrown an ERROR if it tried to cluster a remote temp table. So apparently no one ever saw it, because I can't remember any report about it. For REINDEX the story is very different, because what happens is that queries start silently returning wrong data. My test case was alvherre=# create temp table foo (a int primary key); NOTICE: CREATE TABLE / PRIMARY KEY creará el índice implícito «foo_pkey» para la tabla «foo» CREATE TABLE alvherre=# insert into foo select * from generate_series(1,4); INSERT 0 4 -- "reindex database alvherre" in another session alvherre=# select * from foo where a = 400; a - (0 rows) If you now REINDEX this table in the current session, it correctly returns one tuple. So if somebody is executing a procedure which involve temp tables and someone else concurrently does a REINDEX DATABASE, the queries of the first session are automatically corrupted. It seems like the worst kind of bug. Maybe we need additional protections on the bufmgr to prevent this kind of problem. We only introduced REINDEX DATABASE as a way to reindex user indexes in 8.1. Before that, it only considered system catalogs, which ISTM are never temp. Many thanks to dx k9 for the original report. -- Alvaro Herrera http://www.PlanetPostgreSQL.org/ "Endurecerse, pero jamás perder la ternura" (E. Guevara) ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Patch queue triage
For those who have forgotten the progress we have made toward 8.3, here are the open patches we had for 8.3 as of May 1, 2006: --- Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> writes: > > FYI, Tom, Heikki, I need one of you to post the list of patches and > > where we think we are on each one, even if the list is imperfect. > > This message is an attempt to sort out which patch queue entries have > no hope of getting into 8.3 (and so we shouldn't spend more time on them > right now), which ones can get in but are waiting on their authors for > more work, and which ones are ready for immediate review. > > You'll notice that numerically quite a lot of the patches fall into the > "dead" category. This isn't actually all that surprising because if > they were apply-able they'd have gotten in. (It's not like we've > completely neglected applying patches for the last six months...) > However, many of the remaining live patches are huge ones, like HOT and > delayed commit, and are going to consume considerable review effort > (again, if they didn't, they might have been in already). > > The bottom line is that we are desperately in need of more review > manpower. I'm pleased to report that Heikki Linnakangas has promised to > devote most of the next few weeks to helping review, and has already > picked out some patches he feels qualified to work on, as you'll see > below. I have also been coordinating reviewing assignments off-list with > Neil and Magnus to avoid duplication of effort. But I'd like to encourage > everyone who's done any backend hacking to look at the live patches not > shown as assigned to someone specific. The more eyeballs the better; > anything you catch is something someone else might miss. Also, several > of the open patches are in need of more performance testing, so if you > can help out in that fashion please do so. > > With that, the patches: > > > * Re: [pgsql-patches] [PATCHES] [HACKERS] [Fwd: Index Advisor] >/Gurjeet Singh/ > > I don't think this can be applied in anything like its current state. > The internal interface to the planner is not very good, and ditto for the > user API. What I would suggest trying to do is get a set of plugin hooks > defined for the planner that would allow the advisor to be implemented > entirely as an external module, and then let it be worked on as a > pgfoundry project. I have some ideas about appropriate design of the > plugin hooks (as mentioned in my review message of a month ago), but I > have to say that getting that done for 8.3 is lower-priority than some of > the other patches. > > * [pgsql-patches] Ctid chain following enhancement >/Pavan Deolasee/ > > I'm not very excited about this --- it seems to me to complicate the code > in some places that are not in fact performance-critical. While it > doesn't seem likely to break things, I'm not in favor of reducing code > readability unless a measurable performance improvement can be shown. > Can we have some tests showing this is worthwhile? > > * [PATCHES] Error correction for n_dead_tuples > /ITAGAKI Takahiro/ > > Waiting for OldestXmin patch to be accepted or rejected. However, > regardless of the fate of that patch, I'm concerned that this one creates > an open-loop behavior in which the n_dead_tuples estimate will diverge > arbitrarily far from reality over time. I criticized the original > proposal on that basis, and I'm not convinced this version fixes it, > because of the fact that stats counter updates occur much later than the > actions they count. (My recent patch to rate-limit tabstat messages made > that problem worse, but it existed anyway.) What might make sense is for > vacuum to count the number of dead-but-not-removable tuples it skips over, > and apply that as the value of n_dead_tuples on receipt of the vacuum > message (instead of setting to zero as now). This is likely to be wrong > with respect to the actions of transactions running concurrently with the > vacuum, but I think so is the proposed patch; and at least in this form > the error certainly cannot accumulate across vacuum cycles. > > * [PATCHES] Table function support /Pavel Stehule/ > > Neil has promised to review this. AFAIK there are no showstoppers but > there are some disagreements on the details of the functionality. > > * [HACKERS] Grouped Index Tuples /Heikki Linnakangas/ > * [HACKERS] Grouped Index Tuples / Clustered Indexes >/Heikki Linnakangas/ > > Needs review. I'm not sure how many people besides Heikki have really > looked at this (I know I haven't). > > * Re: [PATCHES] POSIX shared memory support > /Chris Marcellino/ > > I'm not convinced that we want this at all. The original proposal was > to provide an alternative for platforms without SysV shmem support, > but the working versions of the patch fail to remove the SysV dependency, > and the porta
Re: [HACKERS] Preparation for PostgreSQL releases 8.2.5, 8.1.10, 8.0.14, 7.4.18, 7.3.20
Tom Lane a écrit :
> Dave Page <[EMAIL PROTECTED]> writes:
>> Tom Lane wrote:
>>> Peter usually does it --- in theory any committer could, but he actually
>>> knows what to do and the rest of us would have to study ;-)
>
>> Study or figure it out? If it hasn't already been it should be
>> documented as part of the release process.
>
> Well, RELEASE_CHANGES has
>
> * Translation updates
> Translations are kept in the project "pgtranslation" on PgFoundry.
> 1. Check out the messages module (of the right branch).
> 2. Check out the admin module.
> 3. Run "sh .../admin/cp-po .../messages .../pgsql
> 4. Commit.
>
> but it's not real clear (to me) which is "the right branch"
They are named the same way PostgreSQL named its branches. For example
REL8_2 for PostgreSQL 8.2.
They are available here :
http://pgfoundry.org/scm/?group_id=164
> and what the ...s signify.
.../admin/cp-po : ... is the path to the cp-po shell script you get when
you did step 2 ("Check out the admin module").
.../messages : ... is the path to the messages branch you get when you
did step 1 (Check out the messages module...)
.../pgsql : ... is the path to your source dir (same branch as messages)
> It's not a big knowledge gap but I have other things
> to worry about ...
It seems pretty straightforward now. Perhaps it can be used with cron.
Regards.
--
Guillaume.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Representation of redirected line pointers in HOT
"Tom Lane" <[EMAIL PROTECTED]> writes: > LP_UNUSED 0 > LP_NORMAL 1 > LP_REDIRECT 2 > LP_DEAD 3 > This seems hardly any uglier than the way the code stands today, and > certainly a lot less ugly than what the current HOT patch proposes. > > Comments? If I understand correctly this still leaves open the possibility of implementing in the future "quick pruning" as we've been speculating about. We could represent that with a line pointer which is LP_DEAD but still has a length and offset. I'm not sure we need to do it now but I'll be glad if we aren't foreclosing the possibility. These kinds of rethinks are typical of the tension between someone writing a patch to submit for review, where they often want to keep the lines of code changed to a minimum to avoid conflicts and to avoid giving reviewers extra code to read which, and normal code maintenance. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Representation of redirected line pointers in HOT
Gregory Stark wrote: > "Tom Lane" <[EMAIL PROTECTED]> writes: > > > LP_UNUSED 0 > > LP_NORMAL 1 > > LP_REDIRECT 2 > > LP_DEAD 3 > > > This seems hardly any uglier than the way the code stands today, and > > certainly a lot less ugly than what the current HOT patch proposes. > > > > Comments? > > If I understand correctly this still leaves open the possibility of > implementing in the future "quick pruning" as we've been speculating about. We > could represent that with a line pointer which is LP_DEAD but still has a > length and offset. I'm not sure we need to do it now but I'll be glad if we > aren't foreclosing the possibility. > > These kinds of rethinks are typical of the tension between someone writing a > patch to submit for review, where they often want to keep the lines of code > changed to a minimum to avoid conflicts and to avoid giving reviewers extra > code to read which, and normal code maintenance. Yes, good point. This is why I am glad Tom can give it a full review. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Representation of redirected line pointers in HOT
Gregory Stark <[EMAIL PROTECTED]> writes: > If I understand correctly this still leaves open the possibility of > implementing in the future "quick pruning" as we've been speculating about. We > could represent that with a line pointer which is LP_DEAD but still has a > length and offset. I'm not sure we need to do it now but I'll be glad if we > aren't foreclosing the possibility. You could still do that, but I'm not sure I see the point. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Representation of redirected line pointers in HOT
On 9/13/07, Tom Lane <[EMAIL PROTECTED]> wrote: > > > What I'm thinking is that we should instead do this by extending the use > of the lp_flags field. lp_flags is two bits, which we currently define > as independent LP_USED and LP_DELETE bits, but in fact LP_DELETE is > never used in heap pages. (It is used in indexes.) I propose that > we redefine lp_flags as having four states, say > > LP_UNUSED 0 > LP_NORMAL 1 > LP_REDIRECT 2 > LP_DEAD 3 > > Sounds good to me. I saw you committed this change. Do you want me to update HOT patch to use this or you are already doing that ? Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
Re: [HACKERS] New Zealand - TZ change
Zdenek Kotala wrote: > I would like to inform, that New Zealand changed DST rules and new > timezone files are available. See > http://www.dia.govt.nz/diawebsite.nsf/wpg_URL/Services-Daylight-Saving-Daylight-saving-to-be-extended > > Patch for head attached. I kept zic.c untouched, but I think it would be > nice to update it as well. > > Are there any updated release scheduled 8.0-8.2? FYI, we will have a release containing the New Zealand time zone changes early next week. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] Representation of redirected line pointers in HOT
"Pavan Deolasee" <[EMAIL PROTECTED]> writes: > Sounds good to me. I saw you committed this change. Do you want me > to update HOT patch to use this or you are already doing that ? I'll clean up my own mess ;-) regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Patch queue triage
On 9/13/07, Bruce Momjian <[EMAIL PROTECTED]> wrote: > > > For those who have forgotten the progress we have made toward 8.3, here > are the open patches we had for 8.3 as of May 1, 2006: > > You mean May 1, 2007 ;-) Thanks, Pavan -- Pavan Deolasee EnterpriseDB http://www.enterprisedb.com
