Re: [HACKERS] -HEAD planner issue wrt hash_joins on dbt3 ?
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: http://www.kaltenbrunner.cc/files/analyze_q9_beta1.txt The next problem seems to be the drastic misestimation of this join size: - Nested Loop (cost=0.00..6872092.36 rows=135 width=28) (actual time=94.762..14429291.129 rows=3554044 loops=1) - Merge Join (cost=0.00..519542.74 rows=449804 width=16) (actual time=48.197..49636.006 rows=474008 loops=1) Merge Cond: (part.p_partkey = partsupp.ps_partkey) - Index Scan using pk_part on part (cost=0.00..105830.22 rows=112447 width=4) (actual time=34.646..14381.644 rows=118502 loops=1) Filter: ((p_name)::text ~~ '%ghost%'::text) - Index Scan using i_ps_partkey on partsupp (cost=0.00..388943.05 rows=8000278 width=12) (actual time=13.511..22659.364 rows=7999685 loops=1) - Index Scan using i_l_suppkey_partkey on lineitem (cost=0.00..14.11 rows=1 width=24) (actual time=4.415..30.310 rows=7 loops=474008) Index Cond: ((partsupp.ps_partkey = lineitem.l_partkey) AND (partsupp.ps_suppkey = lineitem.l_suppkey)) With a factor-of-25000 error in that rowcount estimate, it's amazing the plans aren't worse than they are. It evidently thinks that most of the rows in the join of part and partsupp won't have any matching rows in lineitem, whereas on average there are about 7 matching rows apiece. So that's totally wacko, and it's not immediately obvious why. Could we see the pg_stats entries for part.p_partkey, partsupp.ps_partkey, partsupp.ps_suppkey, lineitem.l_partkey, lineitem.l_suppkey? http://www.kaltenbrunner.cc/files/dbt3_beta1_statistics.txt Stefan ---(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
[HACKERS] horo(r)logy test fail on solaris (again and solved)
I tried regression test with Postgres Beta and horology test field. See attached log. It appears few month ago - see http://archives.postgresql.org/pgsql-ports/2006-06/msg4.php I used Sun Studio 11 with -fast flag and SPARC platform. I played little bit with cc flags and following flags work fine for me: export CFLAGS=-fast export LDFLAGS=-lm -fast The fast switch for compiler is very important too, because it links fast library. Could anybody confirm that it works on his machine? But the question is if the -fast flag is good for postgres. The -fast flag sets brutal floating point optimization and some operation should have less precision. Is possible verify that floating point operation works well? I read postgres documentation about floating point datatypes and that implementation is platform specific. Developer must take care about it discrepancies, but should there any other part of postgres code where -fast switch generate some computing defect - it means that result must be platform independent? The cc flags are describes in http://docs.sun.com/source/819-3688/cc_ops.app.html. Zdenek *** ./expected/horology.out Tue Jul 25 05:51:22 2006 --- ./results/horology.out Tue Sep 26 14:19:10 2006 *** *** 2466,2472 SELECT '' AS ten, f1 AS interval, reltime(f1) AS reltime FROM INTERVAL_TBL; ten | interval|reltime ! -+---+--- | @ 1 min | @ 1 min | @ 5 hours | @ 5 hours | @ 10 days | @ 10 days --- 2466,2472 SELECT '' AS ten, f1 AS interval, reltime(f1) AS reltime FROM INTERVAL_TBL; ten | interval| reltime ! -+---+-- | @ 1 min | @ 1 min | @ 5 hours | @ 5 hours | @ 10 days | @ 10 days *** *** 2474,2480 | @ 3 mons | @ 3 mons | @ 14 secs ago | @ 14 secs ago | @ 1 day 2 hours 3 mins 4 secs | @ 1 day 2 hours 3 mins 4 secs ! | @ 6 years | @ 6 years | @ 5 mons | @ 5 mons | @ 5 mons 12 hours | @ 5 mons 12 hours (10 rows) --- 2474,2480 | @ 3 mons | @ 3 mons | @ 14 secs ago | @ 14 secs ago | @ 1 day 2 hours 3 mins 4 secs | @ 1 day 2 hours 3 mins 4 secs ! | @ 6 years | @ 5 years 12 mons 5 days 6 hours | @ 5 mons | @ 5 mons | @ 5 mons 12 hours | @ 5 mons 12 hours (10 rows) == parallel group (13 tests): text varchar name char boolean oid int8 int4 int2 float4 float8 bit numeric boolean ... ok char ... ok name ... ok varchar ... ok text ... ok int2 ... ok int4 ... ok int8 ... ok oid ... ok float4 ... ok float8 ... ok bit ... ok numeric ... ok test strings ... ok test numerology ... ok parallel group (20 tests): lseg point box comments abstime reltime timetz circle time polygon tinterval inet path interval timestamp date timestamptz type_sanity oidjoins opr_sanity point... ok lseg ... ok box ... ok path ... ok polygon ... ok circle ... ok date ... ok time ... ok timetz ... ok timestamp... ok timestamptz ... ok interval ... ok abstime ... ok reltime ... ok tinterval... ok inet ... ok comments ... ok oidjoins ... ok type_sanity ... ok opr_sanity ... ok test geometry ... ok test horology ... FAILED test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok parallel group (2 tests): copyselect copy copy ... ok copyselect ... ok parallel group (8 tests): create_aggregate constraints create_operator drop_if_exists triggers vacuum create_misc inherit constraints ... ok triggers ... ok create_misc ... ok create_aggregate ... ok create_operator
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Zdenek Kotala [EMAIL PROTECTED] writes: But the question is if the -fast flag is good for postgres. The -fast flag sets brutal floating point optimization and some operation should have less precision. Is possible verify that floating point operation works well? That's a pretty good way to guarantee that you'll break the datetime code. It might be acceptable if you use --enable-integer-datetimes. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Buildfarm alarms
-Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED] Sent: 26 September 2006 08:57 To: Joachim Wieland Cc: Dave Page; [EMAIL PROTECTED] Subject: Re: [HACKERS] Buildfarm alarms On Mon, Sep 25, 2006 at 09:20:19PM +0200, Joachim Wieland wrote: Michael, could you please check and apply? Works for me, so I applied it. But then I only tested on Linux. :-) OK, I now see just one, date format related failure: == running regression test queries== /usr/local/src/postgresql-8.2-dev/src/interfaces/ecpg/test/./tmp_check/i nstall//usr/local/pgsql/bin/createuser -R -S -D -q regressuser1 /usr/local/src/postgresql-8.2-dev/src/interfaces/ecpg/test/./tmp_check/i nstall//usr/local/pgsql/bin/createuser -R -S -D -q connectuser /usr/local/src/postgresql-8.2-dev/src/interfaces/ecpg/test/./tmp_check/i nstall//usr/local/pgsql/bin/createuser -R -S -D -q connectdb testing connect/test1.pgc ... ok testing connect/test2.pgc ... ok testing connect/test3.pgc ... ok testing connect/test4.pgc ... ok testing connect/test5.pgc ... ok testing compat_informix/charfuncs.pgc ... ok testing compat_informix/dec_test.pgc ... ok testing compat_informix/rfmtdate.pgc ... ok testing compat_informix/rfmtlong.pgc ... ok testing compat_informix/rnull.pgc ... ok testing compat_informix/test_informix.pgc ... ok testing compat_informix/test_informix2.pgc ... ok testing preproc/comment.pgc... ok testing preproc/define.pgc ... ok testing preproc/init.pgc ... ok testing preproc/type.pgc ... ok testing preproc/variable.pgc ... FAILED (log, output) testing preproc/whenever.pgc ... ok testing pgtypeslib/dt_test.pgc ... ok testing pgtypeslib/dt_test2.pgc... ok testing pgtypeslib/num_test.pgc... ok testing pgtypeslib/num_test2.pgc ... ok testing sql/array.pgc ... ok testing sql/binary.pgc ... ok testing sql/code100.pgc... ok testing sql/copystdout.pgc ... ok testing sql/define.pgc ... ok testing sql/desc.pgc ... ok testing sql/dynalloc.pgc ... ok testing sql/dynalloc2.pgc ... ok testing sql/dyntest.pgc... ok testing sql/execute.pgc... ok testing sql/fetch.pgc ... ok testing sql/func.pgc ... ok testing sql/indicators.pgc ... ok testing sql/quote.pgc ... ok testing sql/show.pgc ... ok testing sql/update.pgc ... ok testing thread/thread.pgc ... ok testing thread/thread_implicit.pgc ... ok == shutting down postmaster == server stopped make[1]: *** [check] Error 1 make[1]: Leaving directory `/usr/local/src/postgresql-8.2-dev/src/interfaces/ecpg/test' make: *** [check] Error 2 *** expected/preproc-variable.stderrFri Sep 8 10:03:40 2006 --- results/preproc-variable.stderr Tue Sep 26 09:51:00 2006 *** *** 44,50 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGstore_result: line 68: allocating memory for 1 tuples [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGget_data line 68: RESULT: 07-14-1987 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 68: RESULT: 3 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 --- 44,50 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGstore_result: line 68: allocating memory for 1 tuples [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGget_data line 68: RESULT: 14-07-1987 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 68: RESULT: 3 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 *** *** 60,66 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGstore_result: line 68: allocating memory for 1 tuples [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGget_data line 68: RESULT: 07-14-1987 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 68: RESULT: 3 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 --- 60,66 [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGstore_result: line 68: allocating memory for 1 tuples [NO_PID]: sqlca: code: 0, state: 0 ! [NO_PID]: ECPGget_data line 68: RESULT: 14-07-1987 offset: -1 array: Yes [NO_PID]: sqlca: code: 0, state: 0 [NO_PID]: ECPGget_data line 68: RESULT: 3 offset:
Re: [PATCHES] [HACKERS] large object regression tests
On Sun, 24 Sep 2006, Jeremy Drake wrote: On Thu, 21 Sep 2006, Tom Lane wrote: I think we could do without the Moby Dick extract too ... I am open to suggestions. I saw one suggestion that I use an image of an elephant, but I suspect that was tongue-in-cheek. I am not very fond of the idea of generating repetitious data, as I think it would be more difficult to determine whether or not the loseek/tell functions put me in the right place in the middle of the file. I just had the idea that I could use one of the existing data files which are used for testing COPY instead of the Moby Dick extract. They are already there, a few of them are pretty good sized, they have data in the file which is not just simple repetition so it would be pretty obvious if the seek function broke, and they are very unlikely to change. I am considering changing the test I put together to use tenk.data as the input file tomorrow and send in what I have again, since I also am doing a test of \lo_import (which also requires a patch to psql I sent in earlier to fix the output of the \lo_* commands to respect the output settings). -- When does summertime come to Minnesota, you ask? Well, last year, I think it was a Tuesday. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Buildfarm alarms
On Tue, Sep 26, 2006 at 09:57:16AM +0100, Dave Page wrote: OK, I now see just one, date format related failure: ... Did you run it with Joachim's patch or with up-to-date CVS checkout? It seems to me that you do not have the latest changes to CVS. We added a set datestyle to variable.pgc that should fix this failure. Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De, Michael at Meskes dot (De|Com|Net|Org) ICQ: 179140304, AIM/Yahoo: michaelmeskes, Jabber: [EMAIL PROTECTED] Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL! ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Buildfarm alarms
-Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Dave Page Sent: 26 September 2006 10:41 To: Michael Meskes Cc: Joachim Wieland; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Buildfarm alarms -Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED] Sent: 26 September 2006 10:39 To: Dave Page Cc: Joachim Wieland; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Buildfarm alarms On Tue, Sep 26, 2006 at 09:57:16AM +0100, Dave Page wrote: OK, I now see just one, date format related failure: ... Did you run it with Joachim's patch or with up-to-date CVS checkout? It seems to me that you do not have the latest changes to CVS. We added a set datestyle to variable.pgc that should fix this failure. No, I used Joachim's patch as anoncvs hadn't caught up. I'll run it again - thanks. Yep - passes all tests now :-) Thanks, Dave. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Phantom Command ID
Tom Lane wrote: We could rename pg_attribute as pg_userattribute, and remove all the system attributes from that. To stay backwards-compatible, we could have a pg_attribute view on top of that contained the system attributes as well. I don't really think this is necessary. How many client programs have you seen that don't explicitly exclude attnum0 anyway? The places that will need work are inside the backend, and a view won't help them. None, there probably isn't any client programs like that. It would be nice for programs to be able to discover what system attributes there is, though. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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
[HACKERS] Block B-Tree concept
I've been experimenting with the idea of a so-called Block B-Tree. The basic idea is that instead of storing an index tuple for each heap tuple, we store an index tuple for each heap block. This dramatically reduces the size of an index, leading to savings on I/O. This idea was briefly discussed in January: http://archives.postgresql.org/pgsql-hackers/2006-01/msg00565.php To make it actually work, the semantics of the B-Tree has been modified so that every index tuple represents 1 or more heap tuples that fall within some range of values, and are on the same heap page. The range that an index tuple represents is from X, inclusive, to Y, exclusive, where X is the key of the index tuple and Y is the key of the *next* index tuple in the index. If the heap is in index order (as after CLUSTER), we get a very compact index this way, effectively eliminating the leaf level of the B-tree. To locate the actual matching items on the heap page, we have to scan the heap page because we don't have the item ids, so this is a tradeoff between CPU and I/O. However, we could have a hybrid where we initially store heap tuple pointers like we do now, and when there's more than X consecutive pointers to the same page, we collapse them to just one pointer to the whole page. This would potentially give us the best of both worlds. This design is more flexible and less invasive than true index-organized-tables, because it doesn't require perfect ordering of the heap or moving heap tuples around. You can also define than one Block B-Tree on a table, though you wouldn't get much benefit from using Block B-Tree instead of normal B-Tree if there's no correlation between the index order and the heap order. It also fits in nicely with the bitmap scans, since there's already support for lossy bitmap pages. Doing normal ordered index scans requires some coding, but can be done. Thoughts? I'm thinking of getting this in early in the 8.3 cycle. We'll have to take a look at the indexam API to support both bitmap indexes and block B-trees nicely. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] heap_markpos and heap_restrpos
What's the purpose of mark/restrpos in heapam.c? AFAIK, mark/restore is only used by merge joins, and you can't feed a merge join from a heap scan because merge join requires sorted input. Unless I'm missing something, heap_markpos and heap_restrpos are dead code. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] horo(r)logy test fail on solaris (again and
Zdenek Kotala wrote: I tried regression test with Postgres Beta and horology test field. See attached log. It appears few month ago - see http://archives.postgresql.org/pgsql-ports/2006-06/msg4.php I used Sun Studio 11 with -fast flag and SPARC platform. Are you looking for ways to contort Solaris to make PostgreSQL fail? That doesn't prove much about PostgreSQL, but rather about Solaris. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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] Buildfarm alarms
On Mon, Sep 25, 2006 at 02:23:39PM +0100, Dave Page wrote: testing connect/test1.pgc ... FAILED (log) testing compat_informix/dec_test.pgc ... FAILED (output) testing preproc/variable.pgc ... FAILED (log, output) testing pgtypeslib/dt_test.pgc ... FAILED (log, output) testing pgtypeslib/num_test.pgc... FAILED (output) testing pgtypeslib/num_test2.pgc ... FAILED (output) All should be fine now. I tested successfully with both cygwin and MinGW. Joachim -- Joachim Wieland [EMAIL PROTECTED] GPG key available ---(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] Block B-Tree concept
Teodor Sigaev wrote: Right now, if an index entry points to a dead tuple, we set a bit in the index so future lookups do not access the heap. We could set a bit for block index entries that point to a page that has no live rows, and have vacuum remove the index entry later. GIN don't support this feature... I'm only talking about B-trees at this stage. ISTM that you could do the same thing with hash indexes, but I haven't given it much thought. Anyway, I think you'd usually want to use bitmap scans with a Block B-tree, unless you need sorted output. And bitmap scans don't set the LP_DELETE flag either. We might want to do something about that. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Block B-Tree concept
Heikki Linnakangas wrote: Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: I've been experimenting with the idea of a so-called Block B-Tree. The basic idea is that instead of storing an index tuple for each heap tuple, we store an index tuple for each heap block. This dramatically reduces the size of an index, leading to savings on I/O. VACUUM? There's a few options that I've thought of this far: 1. Whenever a tuple is found dead on page X, vacuum of the index will have to go to that page again to see if there's any matching tuples left. Right now, if an index entry points to a dead tuple, we set a bit in the index so future lookups do not access the heap. We could set a bit for block index entries that point to a page that has no live rows, and have vacuum remove the index entry later. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Release Notes: Major Changes in 8.2
Hi, Bruce, Bruce Momjian wrote: listitem para Allow inheritance to be removed from tables /para /listitem I'd enhance that to Allow table inheritance relationships to be defined for and removed from pre-existing tables. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Phantom Command ID
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: If we're going to fool with these, I'd like to renew the suggestion I made awhile back that none of the system columns should have explicit entries in pg_attribute, but rather their lookup should be special-cased in the parser. What was the original reason for the proposal? Space savings? Partly that, and partly that it'd make it much easier to alter the set of system attributes. We could rename pg_attribute as pg_userattribute, and remove all the system attributes from that. To stay backwards-compatible, we could have a pg_attribute view on top of that contained the system attributes as well. I don't really think this is necessary. How many client programs have you seen that don't explicitly exclude attnum0 anyway? The places that will need work are inside the backend, and a view won't help them. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Questions about guc units
Casey Duncan wrote: Seems like the unit used for shared_buffers (and others) should be megabytes then with a minimum of 1 (or more). Is less than 1MB granularity really useful here? Yes, there are platforms that allow as little as 512 kB of shared memory by default. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Please to technical check of upcoming release
On Mon, 2006-09-25 at 14:03 -0700, Josh Berkus wrote: Here: http://pgfoundry.org/docman/view.php/147/233/release82.zip is a zip file of a draft of the PostgreSQL 8.2 release and accompanying press kit. Please check if the technical details are correct, and get back to me with any corrections by Thursday. Suggested changes: Warm Standby Databases Online Index Builds: index builds occur while applications write to database tables, allowing performance tuning without downtime -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] heap_markpos and heap_restrpos
Heikki Linnakangas [EMAIL PROTECTED] writes: What's the purpose of mark/restrpos in heapam.c? It's deadwood --- see the comment for ExecSupportsMarkRestore: /* * ExecSupportsMarkRestore - does a plan type support mark/restore? * * XXX Ideally, all plan node types would support mark/restore, and this * wouldn't be needed. For now, this had better match the routines above. * But note the test is on Plan nodetype, not PlanState nodetype. * * (However, since the only present use of mark/restore is in mergejoin, * there is no need to support mark/restore in any plan type that is not * capable of generating ordered output. So the seqscan, tidscan, * functionscan, and valuesscan support is actually useless code at present.) */ I haven't seen a reason to take it out, but if you have in mind something that would actively break it, removing it is no problem. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Bitmap index status
On Tue, 26 Sep 2006, Heikki Linnakangas wrote:
Looks a bit better now, though I think you need to think more about the
encapsulation of the structs. More detailed comments below.
Jie Zhang wrote:
Essentially, we want to have a stream bitmap object that has an iterator,
which will be able to iterate through the bitmaps. The BitmapIndexscan,
BitmapAnd, BitmapOr will be executed once and return a streamp bitmap or a
hash bitmap. The BitmapHeapscan then calls tbm_iterate() to iterate
through
the bitmaps.
The StreamBitmap structure will look like below.
struct StreamBitmap {
NodeTag type; /* to make it a valid Node */
PagetableEntry entry; /* a page of tids in this stream bitmap */
/* the iterator function */
void (*next)(StreamBitmap*);
Node* state; /* store how this stream bitmap generated,
and all necessary information to
obtain the next stream bitmap. */
};
I'd suggest making state just a (void *). It's private to the producer
of the bitmap, and I don't see a reason to expose it. I assume that the
next-function fills in the PageTableEntry with the next set of tids.
Two new state objects will look like below. At the same time, we introduce
three new node types: T_StreamBitmapAND, T_StreamBitmapOR,
And T_StreamBitmapIndex, to define different states.
/*
* Stores the necessary information for iterating through the stream
bitmaps
* generated by nodeBitmapAnd or nodeBitmapOr.
*/
struct StreamBitmapOp {
NodeTag type; /* handles T_StreamBitmapAND and T_StreamBitmapOR */
List* bitmaps;
};
AFAICS, this struct is private to tidbitmap.c, where the new
stream-enabled tbm_intersect etc. functions are defined. Also, I don't
see a reason why it needs to by a valid Node.
Well, making it a valid nodes makes it easy to identify (IsA) and gives us
access to copy/equal frameworks. I do think that it is best to bury this
in the bitmap code however.
* Stores some necessary information for iterating through the stream
* bitmaps generated by nodeBitmapIndexscan.
*/
struct StreamBitmapIndex {
NodeTag type; /* handle T_StreamBitmapIndex */
IndexScanDesc scan;
BlockNumber nextBlockNo;/* next block no to be read */
};
Where would this struct be defined? I think different index access
methods might want to have different kind of states. The struct above
assumes that the position of an index scan is always represented by the
nextBlockNo. That seems to be the right thing for the bitmap indexam, so
this struct is fine for StreamBitmaps returned by bmgetbitmap, but not
necessary for others.
right.
Then we will have the iterator functions like the following:
...
void StreamBitmapIndexNext(StreamBitmap* node) {
StreamBitmapIndex* sbi = (StreamBitmapIndex*) node-state;
amgetbitmap(sbi-scan, NULL, sbi-nextBlockNo);
}
This means that the amgetbitmap function would still be called many
times in each scan. What would amgetbitmap return? A new StreamBitmap
on each call?
I'd like to see just one call to amgetbitmap. It would a) fill in the
hash bitmap passed to it, b) return a new hash bitmap, or c) return a
new StreamBitmap, with a indexam specific next-function that returns the
tids one page at a time. I think we'll also need some kind of a
destructor in StreamBitmap that's called by the consumer of the bitmap
after it's done with it.
Right, I agree. I am working on this now.
Thanks,
gavin
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Phantom Command ID
On Tue, Sep 26, 2006 at 12:35:54PM +0100, Heikki Linnakangas wrote: Tom Lane wrote: We could rename pg_attribute as pg_userattribute, and remove all the system attributes from that. To stay backwards-compatible, we could have a pg_attribute view on top of that contained the system attributes as well. I don't really think this is necessary. How many client programs have you seen that don't explicitly exclude attnum0 anyway? The places that will need work are inside the backend, and a view won't help them. None, there probably isn't any client programs like that. It would be nice for programs to be able to discover what system attributes there is, though. +1; we need to have some way for users to find that info out, and I can't think of a better way than pg_attribute. If we want to create a set of views that are more human friendly I'm all for it (it's why we started the newsysviews project afterall), but I don't know if y'all want to open that can of worms back up. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Release Notes: Major Changes in 8.2
Markus Schaber wrote: -- Start of PGP signed section. Hi, Bruce, Bruce Momjian wrote: listitem para Allow inheritance to be removed from tables /para /listitem I'd enhance that to Allow table inheritance relationships to be defined for and removed from pre-existing tables. Good point. Updated wording: Allow table inheritance to be added and removed from pre-existing tables -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] Block B-Tree concept
Teodor Sigaev wrote: Right now, if an index entry points to a dead tuple, we set a bit in the index so future lookups do not access the heap. We could set a bit for block index entries that point to a page that has no live rows, and have vacuum remove the index entry later. GIN don't support this feature... I think block indexes would only be for btrees. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Release Notes: Major Changes in 8.2
Hi, Bruce, Bruce Momjian wrote: Allow inheritance to be removed from tables I'd enhance that to Allow table inheritance relationships to be defined for and removed from pre-existing tables. Good point. Updated wording: Allow table inheritance to be added and removed from pre-existing tables Agree, that's excellent. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
Jeanna Geier [EMAIL PROTECTED] writes: [ hostssl works with 'trust' but not 'md5' ] It's only when I change the connection method to 'md5' that I'm running into problems -- then I cannot connect from pgadmin or the command line. I experimented with this using CVS HEAD, and found that SSL+md5 works fine as long as I enter the correct password ... but if I give a wrong password I get $ psql -h localhost regression Password: psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user tgl, database regression, SSL off $ which is at best pretty misleading :-(. I think libpq is probably mishandling the bad password error and concluding that it should fall back to a non-SSL connection, which the server then rejects. Will look into it. As for Jeanna's problem, I don't see any password prompt at all in her example. I've forgotten the details, but wasn't there a password prompting problem with 8.0.x on Windows? 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] horo(r)logy test fail on solaris (again and solved)
Tom Lane wrote: Zdenek Kotala [EMAIL PROTECTED] writes: But the question is if the -fast flag is good for postgres. The -fast flag sets brutal floating point optimization and some operation should have less precision. Is possible verify that floating point operation works well? That's a pretty good way to guarantee that you'll break the datetime code. ! | @ 6 years | @ 5 years 12 mons 5 days 6 hours Doesn't this look odd regardless of what bad results come back from the FP library? cheers andrew ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
On Tue, 26 Sep 2006, Tom Lane wrote: Jeanna Geier [EMAIL PROTECTED] writes: [ hostssl works with 'trust' but not 'md5' ] It's only when I change the connection method to 'md5' that I'm running into problems -- then I cannot connect from pgadmin or the command line. As for Jeanna's problem, I don't see any password prompt at all in her example. I've forgotten the details, but wasn't there a password prompting problem with 8.0.x on Windows? It worked great with 8.1.4. Let me download 8.0.8 and try that on Windows since that appears to be what she's using. More later. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] CVS HEAD psql won't let you out of a Password: prompt
If psql wants a Password:, it won't gracefully give up in response to ^C, ^D, or ^J ... you *must* enter a nonempty string before you can get your console back. This is pretty unfriendly, and I don't recall prior versions behaving that way (though I so seldom use a password with PG that I might be wrong). regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Tom Lane napsal(a): Zdenek Kotala [EMAIL PROTECTED] writes: But the question is if the -fast flag is good for postgres. The -fast flag sets brutal floating point optimization and some operation should have less precision. Is possible verify that floating point operation works well? That's a pretty good way to guarantee that you'll break the datetime code. It might be acceptable if you use --enable-integer-datetimes. I suggest to remove mention about -fast flag from FAQ.Solaris or add warning about usage of this. Josh do you have any cc flags suggestion? regards, Zdenek ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[HACKERS] Questions/observations about set_ps_display ()
We're looking for some advice and/or comments. During performance and scalability testing with 8.1.4 and also 8.2Beta1, OProfile reported that the strncpy () C library call was taking a large amount of CPU time while we were running one of our benchmarks. We traced a partial benchmark run using ltrace and collected statistics on all the strncpy () calls that were being made. We noticed that a number of the calls were similar to the following: strncpy (0x80808080, BIND, 2500); This usage of strncpy () can be traced back to the set_ps_display () function call. We could probably turn off set_ps_display (), but it's a useful tool. The specification for strncpy () indicates that when the length of the source string (4 bytes) is less than the length of the number of bytes to copy (2500 bytes), the remainder of the destination string will be padded with NULL bytes (2496). Based on the GLIBC source code, strncpy () is written to pad the destination string a byte at a time and this is where all the time was taken, according to OProfile. We only have access to SLES 9 SP3 and RHEL 4 U3 based environments. To assess the performance improvement, we have replaced the strncpy () call in set_ps_display () with a strcpy () call, as this seemed safe for our environments. We're seeing ~3% performance improvement. However, we realize that our environments do not represent all the environments where Postgres is available. The NULL padding side effect of strncpy () does mean that the process status buffer would be cleared of any previous output. We are not sure if there might be any security concerns when replacing the strncpy () with strcpy (). A strncpy () call could still be used with a calculated length for the PS activity, rather than the environment size. Although, this might incur a penalty for using strlen () against the activity and perhaps a bounds check to make sure it would fit into the process status buffer. Are there any implications using PS_USE_CHANGE_ARGV on Linux rather than PS_USE_CLOBBER_ARGV, as this seems to only use a 256 byte buffer for the process status buffer? We're wondering if a patch worth pursuing? There could be issues with different platforms and the performance gain is very narrow. However, we thought we would pass this information on. Thanks David ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Block B-Tree concept
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: I've been experimenting with the idea of a so-called Block B-Tree. The basic idea is that instead of storing an index tuple for each heap tuple, we store an index tuple for each heap block. This dramatically reduces the size of an index, leading to savings on I/O. VACUUM? There's a few options that I've thought of this far: 1. Whenever a tuple is found dead on page X, vacuum of the index will have to go to that page again to see if there's any matching tuples left. 2. Have a reference counter on index tuple that's increased on insert and decreased by vacuum. 3. Do nothing. Let index scans mark the index tuple as dead when it's convenient. There's no correctness problem with just leaving dead index tuples there, because you have to check the index quals on each heap tuple anyway when you scan. 3. probably isn't an option in itself, but we might want to do some kind of a mixture of 1 and 3. I'm thinking that Block B-Tree is not a candidate for non-MVCC system catalogs, but I think that's OK. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Release Notes: Major Changes in 8.2
Andrew Dunstan wrote: Bruce Momjian wrote: I created a major features list for 8.2 and put it into CVS. Instead of going into detail (meaning the item would not appear in the Changes section below, I just highlighted some of the big stuff, and was purposely vague about the details, so people just have an overview of what is below. Let me know how it looks. Some of these just look rather vague. For example: * More control over creating/dropping objects and inheritance If I did not know what the features were, that item would convey nothing to me. The fact that you can add/drop the inheritance characteristics of a table after its creation isn't something I would just lump under more control - it's a major new feature that will possibly revolutionize the way people use inheritance, especially for partitioning. OK, split items up: listitem para More control over creating and dropping objects /para /listitem listitem para Allow inheritance to be removed from tables /para /listitem -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://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] Block B-Tree concept
Heikki Linnakangas [EMAIL PROTECTED] writes: Tom Lane wrote: VACUUM? There's a few options that I've thought of this far: 1. Whenever a tuple is found dead on page X, vacuum of the index will have to go to that page again to see if there's any matching tuples left. Anything that involves having VACUUM re-evaluate index expressions is a nonstarter ... or have you already forgotten the optimizations we put into 8.2 that assume, eg, no sub-transactions within a VACUUM? 2. Have a reference counter on index tuple that's increased on insert and decreased by vacuum. The increase on insert part I understand, the decrease by vacuum part seems to have the same problem as #1. How do you tell which index entries should be changed? 3. Do nothing. Let index scans mark the index tuple as dead when it's convenient. There's no correctness problem with just leaving dead index tuples there, because you have to check the index quals on each heap tuple anyway when you scan. And we're back to routine REINDEX I guess :-(. This doesn't seem like a satisfactory answer. 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] Buildfarm alarms
-Original Message- From: Michael Meskes [mailto:[EMAIL PROTECTED] Sent: 26 September 2006 10:39 To: Dave Page Cc: Joachim Wieland; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] Buildfarm alarms On Tue, Sep 26, 2006 at 09:57:16AM +0100, Dave Page wrote: OK, I now see just one, date format related failure: ... Did you run it with Joachim's patch or with up-to-date CVS checkout? It seems to me that you do not have the latest changes to CVS. We added a set datestyle to variable.pgc that should fix this failure. No, I used Joachim's patch as anoncvs hadn't caught up. I'll run it again - thanks. Regards Dave ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Bitmap index status
Looks a bit better now, though I think you need to think more about the
encapsulation of the structs. More detailed comments below.
Jie Zhang wrote:
Essentially, we want to have a stream bitmap object that has an iterator,
which will be able to iterate through the bitmaps. The BitmapIndexscan,
BitmapAnd, BitmapOr will be executed once and return a streamp bitmap or a
hash bitmap. The BitmapHeapscan then calls tbm_iterate() to iterate
through
the bitmaps.
The StreamBitmap structure will look like below.
struct StreamBitmap {
NodeTag type; /* to make it a valid Node */
PagetableEntry entry; /* a page of tids in this stream bitmap */
/* the iterator function */
void (*next)(StreamBitmap*);
Node* state; /* store how this stream bitmap generated,
and all necessary information to
obtain the next stream bitmap. */
};
I'd suggest making state just a (void *). It's private to the producer
of the bitmap, and I don't see a reason to expose it. I assume that the
next-function fills in the PageTableEntry with the next set of tids.
Two new state objects will look like below. At the same time, we introduce
three new node types: T_StreamBitmapAND, T_StreamBitmapOR,
And T_StreamBitmapIndex, to define different states.
/*
* Stores the necessary information for iterating through the stream
bitmaps
* generated by nodeBitmapAnd or nodeBitmapOr.
*/
struct StreamBitmapOp {
NodeTag type; /* handles T_StreamBitmapAND and T_StreamBitmapOR */
List* bitmaps;
};
AFAICS, this struct is private to tidbitmap.c, where the new
stream-enabled tbm_intersect etc. functions are defined. Also, I don't
see a reason why it needs to by a valid Node.
/*
* Stores some necessary information for iterating through the stream
* bitmaps generated by nodeBitmapIndexscan.
*/
struct StreamBitmapIndex {
NodeTag type; /* handle T_StreamBitmapIndex */
IndexScanDesc scan;
BlockNumber nextBlockNo;/* next block no to be read */
};
Where would this struct be defined? I think different index access
methods might want to have different kind of states. The struct above
assumes that the position of an index scan is always represented by the
nextBlockNo. That seems to be the right thing for the bitmap indexam, so
this struct is fine for StreamBitmaps returned by bmgetbitmap, but not
necessary for others.
Then we will have the iterator functions like the following:
...
void StreamBitmapIndexNext(StreamBitmap* node) {
StreamBitmapIndex* sbi = (StreamBitmapIndex*) node-state;
amgetbitmap(sbi-scan, NULL, sbi-nextBlockNo);
}
This means that the amgetbitmap function would still be called many
times in each scan. What would amgetbitmap return? A new StreamBitmap
on each call?
I'd like to see just one call to amgetbitmap. It would a) fill in the
hash bitmap passed to it, b) return a new hash bitmap, or c) return a
new StreamBitmap, with a indexam specific next-function that returns the
tids one page at a time. I think we'll also need some kind of a
destructor in StreamBitmap that's called by the consumer of the bitmap
after it's done with it.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.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] horo(r)logy test fail on solaris (again and
I suspect the '-fast' introduced arithmetic associativity transformations that horology is sensitive to. I've seen this in the past. The solution I used was to mod the Makefile to exclude the sensitive routines from the aggressive optimizations. As I recall, adt.c was the prime culprit. - Luke Msg is shrt cuz m on ma treo -Original Message- From: Bruce Momjian [mailto:[EMAIL PROTECTED] Sent: Tuesday, September 26, 2006 11:51 AM Eastern Standard Time To: Zdenek Kotala Cc: pgsql-hackers@postgresql.org; Tom Lane; [EMAIL PROTECTED] Subject:Re: [HACKERS] horo(r)logy test fail on solaris (again and Zdenek Kotala wrote: I tried regression test with Postgres Beta and horology test field. See attached log. It appears few month ago - see http://archives.postgresql.org/pgsql-ports/2006-06/msg4.php I used Sun Studio 11 with -fast flag and SPARC platform. Are you looking for ways to contort Solaris to make PostgreSQL fail? That doesn't prove much about PostgreSQL, but rather about Solaris. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Block B-Tree concept
Heikki Linnakangas [EMAIL PROTECTED] writes: I've been experimenting with the idea of a so-called Block B-Tree. The basic idea is that instead of storing an index tuple for each heap tuple, we store an index tuple for each heap block. This dramatically reduces the size of an index, leading to savings on I/O. VACUUM? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Block B-Tree concept
And we're back to routine REINDEX I guess :-(. This doesn't seem like a satisfactory answer. If the reindex works online, it could be a satisfactory solution. Cheers, Csaba. ---(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] Phantom Command ID
Tom Lane wrote: Heikki Linnakangas [EMAIL PROTECTED] writes: Another question is, what should cmin and cmax system columns return? If we're going to fool with these, I'd like to renew the suggestion I made awhile back that none of the system columns should have explicit entries in pg_attribute, but rather their lookup should be special-cased in the parser. And whatever we do with cmin/cmax, the infomask should become exposed as well. I just looked back at that discussion in the archives (http://archives.postgresql.org/pgsql-hackers/2005-02/msg00615.php). What was the original reason for the proposal? Space savings? We could rename pg_attribute as pg_userattribute, and remove all the system attributes from that. To stay backwards-compatible, we could have a pg_attribute view on top of that contained the system attributes as well. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Block B-Tree concept
Right now, if an index entry points to a dead tuple, we set a bit in the index so future lookups do not access the heap. We could set a bit for block index entries that point to a page that has no live rows, and have vacuum remove the index entry later. GIN don't support this feature... -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
On Tue, 26 Sep 2006, Jeff Frost wrote: As for Jeanna's problem, I don't see any password prompt at all in her example. I've forgotten the details, but wasn't there a password prompting problem with 8.0.x on Windows? It worked great with 8.1.4. Let me download 8.0.8 and try that on Windows since that appears to be what she's using. More later. Looks like the windows 8.0.8 psql worked fine against my running windows 8.1.4 server: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres postgres Password: Welcome to psql 8.0.8, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. postgres=# Do you remember if the problem was on the 8.0.8 server side that caused the lack of prompting? -- Jeff 'Frosty' Frost - AFM #996 - Frost Consulting, LLC Racing http://www.frostconsultingllc.com/ http://www.motonation.com/ http://www.suomy-usa.com/ http://www.motionpro.com/ http://www.motorexusa.com/ http://www.lockhartphillipsusa.com/ http://www.zoomzoomtrackdays.com/ http://www.braking.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] horo(r)logy test fail on solaris (again and solved)
Bruce Momjian napsal(a): Zdenek Kotala wrote: I tried regression test with Postgres Beta and horology test field. See attached log. It appears few month ago - see http://archives.postgresql.org/pgsql-ports/2006-06/msg4.php I used Sun Studio 11 with -fast flag and SPARC platform. Are you looking for ways to contort Solaris to make PostgreSQL fail? That doesn't prove much about PostgreSQL, but rather about Solaris. It is not about Solaris, It is about recommended setting for Sun Studio in the FAQ.Solaris. regards Zdenek ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
Jeff Frost [EMAIL PROTECTED] writes: Do you remember if the problem was on the 8.0.8 server side that caused the lack of prompting? No, I'm pretty sure it was a client-side issue (and I thought we'd fixed it by 8.0.8 anyway, so I'm glad to see your test agrees). Jeanna, do you maybe have a pgpass file or something else that would short-circuit the password prompt? It could be that your problem boils down to supplying the wrong password behind-the-scenes. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] Block B-Tree concept
Tom Lane wrote: Anything that involves having VACUUM re-evaluate index expressions is a nonstarter ... or have you already forgotten the optimizations we put into 8.2 that assume, eg, no sub-transactions within a VACUUM? Umm, I'm afraid I have. Could you give me a clue? 3. Do nothing. Let index scans mark the index tuple as dead when it's convenient. There's no correctness problem with just leaving dead index tuples there, because you have to check the index quals on each heap tuple anyway when you scan. And we're back to routine REINDEX I guess :-(. This doesn't seem like a satisfactory answer. In general, it isn't. Though there are interesting use cases where it would be fine. For example, if you remove old data by dropping a partition, there's never really need to vacuum. Or if all of the data is accessed during normal operation, the index scans set the LP_DELETE flags and no additional vacuum is really needed. Also, now that we have concurrent CREATE INDEX, we could implement concurrent REINDEX as well, I believe. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.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] horo(r)logy test fail on solaris (again and solved)
Andrew Dunstan [EMAIL PROTECTED] writes: ! | @ 6 years | @ 5 years 12 mons 5 days 6 hours Doesn't this look odd regardless of what bad results come back from the FP library? It looks exactly like the sort of platform-dependent rounding issue that Bruce and Michael Glaesemann spent a lot of time on recently. It might be interesting to see if CVS HEAD works any better under these conditions ... but if it doesn't, that doesn't mean I'll be interested in fixing it. Getting the float datetime code to work is hard enough without having a compiler that thinks it can take shortcuts. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
On Tue, 26 Sep 2006, Tom Lane wrote: Jeff Frost [EMAIL PROTECTED] writes: Do you remember if the problem was on the 8.0.8 server side that caused the lack of prompting? No, I'm pretty sure it was a client-side issue (and I thought we'd fixed it by 8.0.8 anyway, so I'm glad to see your test agrees). Jeanna, do you maybe have a pgpass file or something else that would short-circuit the password prompt? It could be that your problem boils down to supplying the wrong password behind-the-scenes. Interestingly, I receive the same error when I disable SSL on the server: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres postgres psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user postgres, database postgres, SSL off But, when I put the trust line back with hostssl, I do not get connected as per her original indication. Of course this is with my 8.1.4 windows server and not 8.0.8. Is it possible that 8.0.8 was more liberal with the hostssl vs host interpretation if ssl was disabled? I also tried making it so the postgres user could not read the server.crt and server.key files and this yielded the same result: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres postgres psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user postgres, database postgres, SSL off Can anyone think of an iteration I haven't tried? I'll go reset the postgres user password to something I know and start the 8.0.8 server by hand momentarily. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [Fwd: Re: [HACKERS] pdfs of the conference]
Hello guys, I would like to ask you to change my name on picture from Radovan Jablonov to Radovan Jablonovsky. I am guy bellow Postgresql sign sitting on the ground in dark red shirt. Sincerely, Radovan Jablonovsky Database Architect/DBA Arrow Transportation Systems Inc. Tel: (250) 571-7773 Email: [EMAIL PROTECTED] WWW: www.arrowtransportation.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: September 25, 2006 1:40 PM To: Radovan Jablonov Subject: [Fwd: Re: [HACKERS] pdfs of the conference] Tak tomu napis a oprav si jmeno :-) Original Message Subject: Re: [HACKERS] pdfs of the conference Date: Wed, 20 Sep 2006 19:01:30 +0200 From: Magnus Hagander [EMAIL PROTECTED] To: Dave Page dpage@vale-housing.co.uk, [EMAIL PROTECTED], pgsql-hackers@postgresql.org References: [EMAIL PROTECTED] [EMAIL PROTECTED] [EMAIL PROTECTED] Here's a starter though: The guy with the PostgreSQL sign round his neck is Devrim Gunduz. The guy holding one up at the back is Chris Browne. On the front row theres Josh Berkus in the middle, Peter Eisentraut to the right, and Gavin Sherry on the far right. Bruce is behind Gavin in the light blue shirt, next to Tatsuo Ishii and Alvaro. Tom is one row from the back, in the middle with the grey-brown shirt on, and to the left of him is D'arcy (with the hat) and then Magnus Hagander, Thomas Hallgren and Neil Clifford. I started with an imagemap version of this one after the conference. Let me finish off some of that and put it up somewhere so people can complete it. Ok. I've got this up at http://www.postgresql.org/files/community/conference06/conference_group. html. Help requested to complete the names not yet added - send private email. //Magnus ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Questions/observations about set_ps_display ()
Strong, David [EMAIL PROTECTED] writes: The specification for strncpy () indicates that when the length of the source string (4 bytes) is less than the length of the number of bytes to copy (2500 bytes), the remainder of the destination string will be padded with NULL bytes (2496). Based on the GLIBC source code, strncpy () is written to pad the destination string a byte at a time and this is where all the time was taken, according to OProfile. Hm. In the PS_USE_CLOBBER_ARGV case, this is pretty silly considering we're going to MemSet the rest of the space anyway. We should probably replace the StrNCpy with something that doesn't uselessly fill the rest of the buffer, perhaps something like memcpy(dest, src, Min(strlen(dest) + 1, avail space)); I believe that most of our uses of StrNCpy actually do not expect the pad-out behavior, so maybe there are other uses for something along this line ... regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] Too many messages from Autovacuum
Simon Riggs [EMAIL PROTECTED] writes: Recommending downgrade of these annoying messages from LOG to DEBUG1: 2006-09-23 15:57:56 EDT_3147 LOG: transaction ID wrap limit is 2147484170, limited by database postgres Seems reasonable --- we put that in at LOG level for purposes of testing the 8.1 XID wraparound prevention logic, but by now I think we can trust that code a bit more. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Block B-Tree concept
Heikki Linnakangas wrote: Tom Lane wrote: Anything that involves having VACUUM re-evaluate index expressions is a nonstarter ... or have you already forgotten the optimizations we put into 8.2 that assume, eg, no sub-transactions within a VACUUM? Umm, I'm afraid I have. Could you give me a clue? I think I found it. Is this what you're talking about (in commands/vacuum.c): /* * During a lazy VACUUM we do not run any user-supplied functions, * and so it should be safe to not create a transaction snapshot. * * We can furthermore set the inVacuum flag, which lets other * concurrent VACUUMs know that they can ignore this one while * determining their OldestXmin. (The reason we don't set inVacuum * during a full VACUUM is exactly that we may have to run user- * defined functions for functional indexes, and we want to make * sure that if they use the snapshot set above, any tuples it * requires can't get removed from other tables. An index function * that depends on the contents of other tables is arguably broken, * but we won't break it here by violating transaction semantics.) * * Note: the inVacuum flag remains set until CommitTransaction or * AbortTransaction. We don't want to clear it until we reset * MyProc-xid/xmin, else OldestXmin might appear to go backwards, * which is probably Not Good. */ MyProc-inVacuum = true; -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Block B-Tree concept
Heikki Linnakangas [EMAIL PROTECTED] writes: Heikki Linnakangas wrote: Tom Lane wrote: Anything that involves having VACUUM re-evaluate index expressions is a nonstarter ... or have you already forgotten the optimizations we put into 8.2 that assume, eg, no sub-transactions within a VACUUM? I think I found it. Is this what you're talking about (in commands/vacuum.c): That's part of it, but I seem to recall other things too --- in particular, the point about subtransactions troubles me. Whatever you think about an index function looking at other tables, it is perfectly legitimate to have an exception block in an index function, and that requires subtransactions (at least as plpgsql is now implemented). 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] Block B-Tree concept
Heikki Linnakangas [EMAIL PROTECTED] writes: Also, now that we have concurrent CREATE INDEX, we could implement concurrent REINDEX as well, I believe. That's probably more easily said than done --- in particular, I don't understand what the committed state after the first transaction would look like. CREATE INDEX can get away with it because nothing need be depending on the new index, but you can't say that for an existing index (esp. if it's UNIQUE). regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Zdenek, Zdenek Kotala [EMAIL PROTECTED] writes: But the question is if the -fast flag is good for postgres. The -fast flag sets brutal floating point optimization and some operation should have less precision. Is possible verify that floating point operation works well? That's a pretty good way to guarantee that you'll break the datetime code. It might be acceptable if you use --enable-integer-datetimes. I suggest to remove mention about -fast flag from FAQ.Solaris or add warning about usage of this. Josh do you have any cc flags suggestion? Using Sun Studio? I'm hardly the expert. Maybe Jignesh? --Josh Berkus ---(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] horo(r)logy test fail on solaris (again and
Tom, On 9/26/06 9:15 AM, Tom Lane [EMAIL PROTECTED] wrote: Andrew Dunstan [EMAIL PROTECTED] writes: ! | @ 6 years | @ 5 years 12 mons 5 days 6 hours Doesn't this look odd regardless of what bad results come back from the FP library? It looks exactly like the sort of platform-dependent rounding issue that Bruce and Michael Glaesemann spent a lot of time on recently. It might be interesting to see if CVS HEAD works any better under these conditions ... but if it doesn't, that doesn't mean I'll be interested in fixing it. Getting the float datetime code to work is hard enough without having a compiler that thinks it can take shortcuts. How about fixing the compilation so that the routines in adt that are sensitive to FP optimizations are isolated from aggressive optimization? - Luke ---(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
[HACKERS] Sane error messages for SSL retry cases
As per a recent discussion in pgsql-admin, http://archives.postgresql.org/pgsql-admin/2006-09/msg00297.php libpq doesn't cope well with the situation where the server is configured to allow only SSL connections (or only non-SSL connections) and there is some unrelated-to-SSL connection problem such as wrong password. The reason is that libpq is set up to retry with the other kind of connection (either dropping or adding SSL) for just about any sort of error returned by the server. This may lead to reporting no pg_hba.conf entry, or some such, rather than the more useful password authentication failed. I am tempted to propose that libpq should only retry in the other mode when the server specifically returns no pg_hba.conf entry, and not for other server errors (beyond the initial do-you-do-SSL-at-all handshake of course). This would save a useless fork() cycle on the server as well as make it more likely that we return a useful error message. There are some corner cases where this might fail to connect when a blind retry would have succeeded, but they all involve the server offering different auth methods depending on SSL or not --- an example is hostssl + ident and hostnossl + password, and you fail the ident test but could have produced the correct password. ISTM that is a scenario where the user should use the sslmode parameter to control which method is tried first. One problem with implementing this proposal is that we currently use the generic INVALID_AUTHORIZATION_SPECIFICATION sqlstate for a bunch of distinct conditions including no pg_hba.conf entry. Looking directly at the error string is of course not localization-proof, so we'd have to break down that errcode into some more-specific categories. Which is probably not a bad idea anyway, but it would mean that the nicer behavior would only happen when talking to an 8.2 or later server. Thoughts? Is this something to tackle during beta, or must we put it off till 8.3? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] CVS HEAD psql won't let you out of a Password: prompt
On Tue, Sep 26, 2006 at 11:58:27AM -0400, Tom Lane wrote: If psql wants a Password:, it won't gracefully give up in response to ^C, ^D, or ^J ... you *must* enter a nonempty string before you can get your console back. This is pretty unfriendly, and I don't recall prior versions behaving that way (though I so seldom use a password with PG that I might be wrong). For ^C it may have been a side-effect of the SIGINT changes, though I doubt it. One of the first things psql does is setup a ^C handler and it only ever did a siglongjmp in that case. But there was no setjmp done at that point, so a ^C would have been a no-op. psql uses simple_prompt which I beleive comes from the port directory. And there is definitly a loop there to retry on no input. That hasn't changed any time recently as far as I can tell. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Please to technical check of upcoming release
Ühel kenal päeval, E, 2006-09-25 kell 14:03, kirjutas Josh Berkus: All, Here: http://pgfoundry.org/docman/view.php/147/233/release82.zip is a zip file of a draft of the PostgreSQL 8.2 release and accompanying press kit. Please check if the technical details are correct, and get back to me with any corrections by Thursday. should probably go into: Additional Features in this Release PL/Python has been enchanced so that now functions can return records and sets of records. Any iterables (lists of list, lists of dictionaries and python generators and iterators) can be used to return recordsets. Also, named function arguments are now available in pl/python Thanks! -- Hannu Krosing Database Architect Skype Technologies OÜ Akadeemia tee 21 F, Tallinn, 12618, Estonia Skype me: callto:hkrosing Get Skype for free: http://www.skype.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Internal Transaction
Hello list would like to know postgres implements ACID ? has some document ? thanks
Re: [HACKERS] Sane error messages for SSL retry cases
On Tue, Sep 26, 2006 at 02:18:59PM -0400, Tom Lane wrote: at the error string is of course not localization-proof, so we'd have to break down that errcode into some more-specific categories. Which is probably not a bad idea anyway, but it would mean that the nicer behavior would only happen when talking to an 8.2 or later server. Thoughts? Is this something to tackle during beta, or must we put it off till 8.3? It sounds to me like a very nice idea that has to wait for the next cycle. Just getting agreement on the categories will take time and cycles, no? A -- Andrew Sullivan | [EMAIL PROTECTED] When my information changes, I alter my conclusions. What do you do sir? --attr. John Maynard Keynes ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Internal Transaction
Hi, Marlon, Marlon Petry wrote: would like to know postgres implements ACID ? has some document ? http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html HTH, Schabi -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
[HACKERS] Faster StrNCpy
David Strong points out here
http://archives.postgresql.org/pgsql-hackers/2006-09/msg02071.php
that some popular implementations of strncpy(dst,src,n) are quite
inefficient when strlen(src) is much less than n, because they don't
optimize the zero-pad step that is required by the standard.
It looks to me like we have a good number of places that are using
either StrNCpy or strncpy directly to copy into large buffers that
we do not need full zero-padding in, only a single guaranteed null
byte. While not all of these places are in performance-critical
paths, some are. David identified set_ps_display, and the other
thing that's probably significant is unnecessary use of strncpy
for keys of string-keyed hash tables. (We used to actually need
zero padding for string-keyed hash keys, but that was a long time ago.)
I propose adding an additional macro in c.h, along the lines of
#define StrNCopy(dst,src,len) \
do \
{ \
char * _dst = (dst); \
Size _len = (len); \
\
if (_len 0) \
{ \
const char * _src = (src); \
Size _src_len = strlen(_src); \
\
if (_src_len _len) \
memcpy(_dst, _src, _src_len + 1); \
else \
{ \
memcpy(_dst, _src, _len - 1); \
_dst[_len-1] = '\0'; \
} \
} \
} while (0)
Unlike StrNCpy, this requires that the source string be null-terminated,
so it would not be a drop-in replacement everywhere. Also, it could be
a performance loss if strlen(src) is much larger than len ... but that
is not usually the case for the places we'd want to apply it.
Thoughts, objections? In particular, is the name OK, or do we need
something a bit further away from StrNCpy?
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] Faster StrNCpy
On Tue, Sep 26, 2006 at 04:24:51PM -0400, Tom Lane wrote: David Strong points out here http://archives.postgresql.org/pgsql-hackers/2006-09/msg02071.php that some popular implementations of strncpy(dst,src,n) are quite inefficient when strlen(src) is much less than n, because they don't optimize the zero-pad step that is required by the standard. I think that's why strlcpy was invented, to deal with the issues with strncpy. http://www.gratisoft.us/todd/papers/strlcpy.html There's an implementation here (used in glib), though you could probably find more. http://mail.gnome.org/archives/gtk-devel-list/2000-May/msg00029.html Do you really think it's worth making a macro rather than just a normal function? Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ From each according to his ability. To each according to his ability to litigate. signature.asc Description: Digital signature
Re: [HACKERS] Faster StrNCpy
Martijn van Oosterhout wrote: On Tue, Sep 26, 2006 at 04:24:51PM -0400, Tom Lane wrote: David Strong points out here http://archives.postgresql.org/pgsql-hackers/2006-09/msg02071.php that some popular implementations of strncpy(dst,src,n) are quite inefficient when strlen(src) is much less than n, because they don't optimize the zero-pad step that is required by the standard. I think that's why strlcpy was invented, to deal with the issues with strncpy. http://www.gratisoft.us/todd/papers/strlcpy.html There's an implementation here (used in glib), though you could probably find more. http://mail.gnome.org/archives/gtk-devel-list/2000-May/msg00029.html That one would be LGPL (glib's license). Here is OpenBSD's version, linked from that one: ftp://ftp.openbsd.org/pub/OpenBSD/src/lib/libc/string/strlcpy.c You'll notice that it iterates once per char. Between that and the strlen() call in Tom's version, not sure which is the lesser evil. -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(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] Faster StrNCpy
Martijn van Oosterhout kleptog@svana.org writes: I think that's why strlcpy was invented, to deal with the issues with strncpy. http://www.gratisoft.us/todd/papers/strlcpy.html strlcpy does more than we need (note that none of the existing uses care about counting the overflowed bytes). Not sure if it's worth adopting those semantics when they're not really standard, but if you think a lot of people would be familiar with strlcpy, maybe we should. Do you really think it's worth making a macro rather than just a normal function? Only in that a macro in c.h is less work than a configure test plus a replacement file in src/port. But if we want to consider this a standard function that just doesn't happen to exist everywhere, I suppose we should use configure. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Faster StrNCpy
On Tue, 2006-09-26 at 16:53 -0400, Tom Lane wrote: strlcpy does more than we need (note that none of the existing uses care about counting the overflowed bytes). Not sure if it's worth adopting those semantics when they're not really standard, but if you think a lot of people would be familiar with strlcpy, maybe we should. I think we should -- while strlcpy() is not standardized, it is widely used (in libc on all the BSDs, Solaris and OS X, as well as private copies in Linux, glib, etc.). A wholesale replacement of strncpy() calls is probably worth doing -- replacing them with strlcpy() if the source string is NUL-terminated, and I suppose memcpy() otherwise. -Neil ---(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] Faster StrNCpy
Alvaro Herrera [EMAIL PROTECTED] writes: You'll notice that it iterates once per char. Between that and the strlen() call in Tom's version, not sure which is the lesser evil. Yeah, I was wondering that too. My code would require two scans of the source string (one inside strlen and one in memcpy), but in much of our usage the source and dest should be reasonably well aligned and one could expect memcpy to be using word rather than byte operations, so you might possibly make it back on the strength of fewer write cycles. And on the third hand, for short source strings none of this matters and the extra function call involved for strlen/memcpy probably dominates. I'm happy to just use the OpenBSD version as a src/port module. Any objections? regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Faster StrNCpy
Neil Conway [EMAIL PROTECTED] writes: A wholesale replacement of strncpy() calls is probably worth doing -- replacing them with strlcpy() if the source string is NUL-terminated, and I suppose memcpy() otherwise. What I'd like to do immediately is put in strlcpy() and hit the two or three places I think are performance-relevant. I agree with trying to get rid of StrNCpy/strncpy calls over the long run, but it's just code beautification and probably not appropriate for beta. 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
[HACKERS] Constant changes (Re-Build)
Hi I'm a student from Valencia-Venezuela and I'm working with some other friends to make PostgreSQL allows the definition of Temporal Databases and their respective Selection, Insertion and some other functions needed to treat this paradigm (all based in TSQL2 Query Language). Right now we are working directly on the source code and making different changes during the day, so I'd like to ask you which is the better choice for re-building (I'm not sure if that is the right term) only the code files that I just have changed. I'm working on a Slow PC with not to many recourse, so every time I make (-configure/-make/-make-install/) i lose like 30 minutes of work, and I have been thinking in some other way to only re-configure the files I've recently changed. Thanks anyway. I'll be waiting for your answer...-- Luis D. García M.Telf: 0414-3482018- FACYT - UC -- Computación -
Re: [HACKERS] Constant changes (Re-Build)
luis garcia wrote: Hi I'm a student from Valencia-Venezuela and I'm working with some other friends to make PostgreSQL allows the definition of Temporal Databases and their respective Selection, Insertion and some other functions needed to treat this paradigm (all based in TSQL2 Query Language). Right now we are working directly on the source code and making different changes during the day, so I'd like to ask you which is the better choice for re-building (I'm not sure if that is the right term) only the code files that I just have changed. I'm working on a Slow PC with not to many recourse, so every time I make (-configure/-make/-make-install/) i lose like 30 minutes of work, and I have been thinking in some other way to only re-configure the files I've recently changed. You don't need to configure each time. Just do a make ; make install, and that will only compile the files you changed. Be sure to use the --enable-depend option to configure the first time, though. You'd only need to mess with configure if you added directories, or you changed configure input files (e.g. pg_config.h, configure.in, etc). -- Alvaro Herrerahttp://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] -HEAD planner issue wrt hash_joins on dbt3 ?
Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Tom Lane wrote: It evidently thinks that most of the rows in the join of part and partsupp won't have any matching rows in lineitem, whereas on average there are about 7 matching rows apiece. So that's totally wacko, and it's not immediately obvious why. Could we see the pg_stats entries for part.p_partkey, partsupp.ps_partkey, partsupp.ps_suppkey, lineitem.l_partkey, lineitem.l_suppkey? http://www.kaltenbrunner.cc/files/dbt3_beta1_statistics.txt OK, so we have 2 million parts and 10 suppliers, and ANALYZE doesn't seem to have been too far off at estimating either of those numbers. I think the problem is that there are not very many suppliers for any particular part, and thus the condition part match AND supplier match is really not much more selective than part match alone. The planner is supposing that their selectivities are independent, which they aren't. Offhand I don't see any good way to fix this without multi-column statistics, which is something that's certainly not happening for 8.2 :-( regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[HACKERS] Isn't strdup.h useless code?
While looking around to see where to insert strlcpy(), I couldn't help noticing that port/strdup.c has its very own header file, include/strdup.h. AFAICS this is utterly redundant given that we have #ifndef HAVE_STRDUP extern char *strdup(char const *); #endif in port.h. Can anyone see a reason not to remove the extra header? regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] horo(r)logy test fail on solaris (again and solved)
Zdenek, Hmmm ... we're not using the -fast option for the standard PostgreSQL packages. Where did you start using it? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] Faster StrNCpy
Tom, What I'd like to do immediately is put in strlcpy() and hit the two or three places I think are performance-relevant. I agree with trying to get rid of StrNCpy/strncpy calls over the long run, but it's just code beautification and probably not appropriate for beta. Immediately? Presumably you mean for 8.3? -- --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(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] Faster StrNCpy
Josh Berkus josh@agliodbs.com writes: What I'd like to do immediately is put in strlcpy() and hit the two or three places I think are performance-relevant. Immediately? Presumably you mean for 8.3? No, I mean now. This is a performance bug and it's still open season on bugs. If we were close to having a release-candidate version, I'd hold off, but the above proposal seems sufficiently low-risk for the current stage of the cycle. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
[HACKERS] Cross-table statistics idea
Since I don't recall any ideas ever having been thrown out on how to do this... ISTM that we could gain additional insight on how many rows would likely result from a join be comparing the shape of the histogram for the joining columns. For example, if the histogram arrays were exactly identical, we're essentially stuck looking at the ratio of reltuples between the two tables. (AFAIK that's the only estimate we make today) If one histogram ended at a value smaller than the start of the other histogram, we would estimate that no rows would result from an equal join. Am I right about how our estimates work right now? Where can I look in the code? Has anyone looked down this path in the past? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Cross-table statistics idea
Jim C. Nasby [EMAIL PROTECTED] writes: ISTM that we could gain additional insight on how many rows would likely result from a join be comparing the shape of the histogram for the joining columns. eqjoinsel already does this for the case of comparing the MCV lists. If you're serious about using the histograms: well, maybe, but it seems to require far stronger assumptions about the behavior of the datatype than we use now. Am I right about how our estimates work right now? Where can I look in the code? Has anyone looked down this path in the past? src/backend/utils/adt/selfuncs.c 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] Block B-Tree concept
On Tue, Sep 26, 2006 at 11:16:54AM +0100, Heikki Linnakangas wrote: To locate the actual matching items on the heap page, we have to scan the heap page because we don't have the item ids, so this is a tradeoff between CPU and I/O. However, we could have a hybrid where we initially store heap tuple pointers like we do now, and when there's more than X consecutive pointers to the same page, we collapse them to just one pointer to the whole page. This would potentially give us the best of both worlds. This design is more flexible and less invasive than true index-organized-tables, because it doesn't require perfect ordering of the heap or moving heap tuples around. You can also define than one Block B-Tree on a table, though you wouldn't get much benefit from using Block B-Tree instead of normal B-Tree if there's no correlation between the index order and the heap order. No, but I think there's scenarios where you may not have extremely high correlation but you'd still benefit, especially with the hybrid approach. If you have a field with rather skewed histogram, for example, where you're likely to have a lot of tuples with one value on any given page. Of course, you probably would want to exclude that value from the index entirely if it's on *every* page, but anything where you see paterns of data wouldn't be like that. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
Jeff Frost [EMAIL PROTECTED] writes: Interestingly, I receive the same error when I disable SSL on the server: If SSL is disabled then hostssl lines in pg_hba.conf effectively become no-ops --- they can never be matched since no incoming connection will be SSL-ified. So that part of it sounds reasonable to me. (Perhaps we could log some kind of complaint in this case, though the easy places to put in such a message would generate an unacceptably large number of repetitions of the message :-() But, when I put the trust line back with hostssl, I do not get connected as per her original indication. Please be clearer about what you mean here --- Jeanna *was* able to connect in this case, if I'm not totally confused. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
On Tue, 26 Sep 2006, Tom Lane wrote: Jeff Frost [EMAIL PROTECTED] writes: Interestingly, I receive the same error when I disable SSL on the server: If SSL is disabled then hostssl lines in pg_hba.conf effectively become no-ops --- they can never be matched since no incoming connection will be SSL-ified. So that part of it sounds reasonable to me. (Perhaps we could log some kind of complaint in this case, though the easy places to put in such a message would generate an unacceptably large number of repetitions of the message :-() But, when I put the trust line back with hostssl, I do not get connected as per her original indication. Please be clearer about what you mean here --- Jeanna *was* able to connect in this case, if I'm not totally confused. Sorry, Tom. I should have been more clear. I was trying to reproduce her problem by leaving ssl=off in the postgresql.conf (as if she didn't restart postgres after the pg_hba.conf change), to see if the hostssl line magically became a host line. But, she later indicated that she saw the SSL encryption info in the psql line when she got connected with this method, so that kind of ruled that out. See my later e-mail where I tried lots of different methods. I suppose it's also possible there is a host all all 127.0.0.1/32 trust line later in the pg_hba.conf that it's falling through and hitting, but I think your .pgpass theory is the best. -- Jeff 'Frosty' Frost - AFM #996 - Frost Consulting, LLC Racing http://www.frostconsultingllc.com/ http://www.motonation.com/ http://www.suomy-usa.com/ http://www.motionpro.com/ http://www.motorexusa.com/ http://www.lockhartphillipsusa.com/ http://www.zoomzoomtrackdays.com/ http://www.braking.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
On Tue, 26 Sep 2006, Jeff Frost wrote: But, when I put the trust line back with hostssl, I do not get connected as per her original indication. Of course this is with my 8.1.4 windows server and not 8.0.8. Is it possible that 8.0.8 was more liberal with the hostssl vs host interpretation if ssl was disabled? I also tried making it so the postgres user could not read the server.crt and server.key files and this yielded the same result: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres postgres psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user postgres, database postgres, SSL off Can anyone think of an iteration I haven't tried? I'll go reset the postgres user password to something I know and start the 8.0.8 server by hand momentarily. Well, here's what happens with 8.0.8 server and 8.0.8 client. I ran through as many iterations as I could think of, so this gets rather long. If you just want to skip to the bottom and see that Tom appears to have nailed the cause, that'll save you some reading. :-) With proper server.crt and server.key, and ssl=true and this pg_hba.conf: # TYPE DATABASE USER CIDR-ADDRESS METHOD # IPv4 local connections: #host all all 127.0.0.1/32 trust # IPv6 local connections: #host all all ::1/128 trust hostssl all all 127.0.0.1/32 md5 I get: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres template1 Password: Welcome to psql 8.0.8, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. So that seems to work ok. With ssl=false and the same settings above, I get: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres template1 psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user postgres, database template1, SSL off Also, as you would expect. If postgres can't read server.key (with ssl=true), you get the following error when starting the postmaster (as expected): C:\temp\pgsql\lib..\bin\postmaster -D ../data FATAL: could not load private key file C:/temp/pgsql/lib/../data/server.key: Input/output error If postgres can read server.key (with ssl=true), but can't read server.crt you get the expected error: C:\temp\pgsql\lib..\bin\postmaster -D ../data FATAL: could not load server certificate file C:/temp/pgsql/lib/../data/server.crt: Input/output error Testing the pgpass theory of Tom's seems to make Tom the winner again. I modified my %appdata%\postgresql\pgpass.conf and put a bad password in like so: localhost:5432:*:postgres:p0stgres I was then rewarded with the exact same error message Jeanna is receiving: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres template1 psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user postgres, database template1, SSL off Removing it and I'm back in business: C:\temp\pgsql\lib..\bin\psql -h localhost -U postgres template1 Password: Welcome to psql 8.0.8, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. template1=# So, I'd say that's near definitive proof. Jeanna, check your %appdata%\postgresql\pgpass.conf. The default path for that would be something like this for my user jeff: C:\Documents and Settings\jeff\Application Data\postgresql BTW, looks like that's where pgadmin3 stores passwords (I was suprised to see a pgpass.conf full of various connection info before I realized pgadmin must be storing them here), so that's likely how you would've gotten the wrong one in there in the first place. -- Jeff Frost, Owner [EMAIL PROTECTED] Frost Consulting, LLC http://www.frostconsultingllc.com/ Phone: 650-780-7908 FAX: 650-649-1954 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
OK, so after doing some more testing and configuring to see if I can narrow this down, I'm more confused than ever! =) Because now I cannot connect to my database unless the method is 'trust'; shouldn't I be able to connect using the correct password if 'password' is the method in the pg_hba.conf file? To look into Tom's theory of the password being short-circuited, I did a search on my pc for 'pgpass' and only came up with an html file, and I don't think that's doing it... and I don't know of any other places where this could/would be occuring. In my pg_hba.conf file I set up six different configurations (restarting the server between each one, to be sure it was using the new settings), with the following results: No HostSSL --- 1) hostssl disabled; host enabled - method: md5 log-in results: pgadmin: passwd prompt passwd authentication failed cmd pmpt: passwd prompt psql: FATAL: password authentication failed for user postgres 2) hostssl disabled; host enabled - method: password log-in results: pgadmin: passwd prompt passwd authentication failed cmd pmpt: passwd prompt psql: FATAL: password authentication failed for user postgres 3) hostssl disabled; host enabled - method: trust log-in results: pgadmin: passwd prompt connects after password is entered cmd pmpt: no password prompt connects with SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) line displayed With HostSSL - 4) host disabled; hostssl enabled - method: md5 log-in results: pgadmin: no passwd prompt; Connecting to databaseFailed. cmd pmpt: passwd prompt psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user postgres, database apt, SSL off 5) host disabled; hostssl enabled - method: password log-in results: pgadmin: no passwd prompt; Connecting to databaseFailed. cmd pmpt: passwd prompt psql: FATAL: no pg_hba.conf entry for host 127.0.0.1, user postgres, database apt, SSL off 6) host disabled; hostssl enabled - method: trust log-in results: pgadmin: passwd prompt connects after password is entered cmd pmpt: no password prompt connects with SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) line displayed Any thoughts?? Like I said previously, I did build this on Windows from source so we could use the SSL option.could I have missed something when I was doing that? (It was my first time and I was following instructions from the INSTALL docs) Thanks so much for your time and assistance! -Jeanna - Original Message - From: Jeff Frost [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: Jeanna Geier [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql-hackers@postgresql.org Sent: Tuesday, September 26, 2006 11:40 AM Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues On Tue, 26 Sep 2006, Tom Lane wrote: Jeff Frost [EMAIL PROTECTED] writes: Interestingly, I receive the same error when I disable SSL on the server: If SSL is disabled then hostssl lines in pg_hba.conf effectively become no-ops --- they can never be matched since no incoming connection will be SSL-ified. So that part of it sounds reasonable to me. (Perhaps we could log some kind of complaint in this case, though the easy places to put in such a message would generate an unacceptably large number of repetitions of the message :-() But, when I put the trust line back with hostssl, I do not get connected as per her original indication. Please be clearer about what you mean here --- Jeanna *was* able to connect in this case, if I'm not totally confused. Sorry, Tom. I should have been more clear. I was trying to reproduce her problem by leaving ssl=off in the postgresql.conf (as if she didn't restart postgres after the pg_hba.conf change), to see if the hostssl line magically became a host line. But, she later indicated that she saw the SSL encryption info in the psql line when she got connected with this method, so that kind of ruled that out. See my later e-mail where I tried lots of different methods. I suppose it's also possible there is a host all all 127.0.0.1/32 trust line later in the pg_hba.conf that it's falling through and hitting, but I think your .pgpass theory is the best. -- Jeff 'Frosty' Frost - AFM #996 - Frost Consulting, LLC Racing http://www.frostconsultingllc.com/ http://www.motonation.com/ http://www.suomy-usa.com/ http://www.motionpro.com/ http://www.motorexusa.com/ http://www.lockhartphillipsusa.com/ http://www.zoomzoomtrackdays.com/ http://www.braking.com/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
On Tue, 26 Sep 2006, Jeanna Geier wrote: Any thoughts?? Like I said previously, I did build this on Windows from source so we could use the SSL option.could I have missed something when I was doing that? (It was my first time and I was following instructions from the INSTALL docs) Jeanna, see my earlier email regarding all the different variations and also where to find your pgpass file on windows. But, please note, you don't have to build the windows version from source to use SSL. The two binary versions I was using for testing both worked fine with SSL. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
Searched again for 'pgpass' and for the 'Application Data' directory with no luck... And, tell me it ain't so you don't have to build the windows version from source to use SSL -- I had two seperate posters tell me that I did and I wrestled with it for a bit...for nothing?? Ah, live and learn! :o) I don't think I'll consider myself a 'newbie' after this project is done. :o) - Original Message - From: Jeff Frost [EMAIL PROTECTED] To: Jeanna Geier [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql-hackers@postgresql.org Sent: Tuesday, September 26, 2006 12:16 PM Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues On Tue, 26 Sep 2006, Jeanna Geier wrote: Any thoughts?? Like I said previously, I did build this on Windows from source so we could use the SSL option.could I have missed something when I was doing that? (It was my first time and I was following instructions from the INSTALL docs) Jeanna, see my earlier email regarding all the different variations and also where to find your pgpass file on windows. But, please note, you don't have to build the windows version from source to use SSL. The two binary versions I was using for testing both worked fine with SSL. ---(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] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
Jeanna Geier wrote: Searched again for 'pgpass' and for the 'Application Data' directory with no luck... The file is called pgpass.conf on Windows. As for the Application Data, it may be called differently if your Windows is localized -- try looking for %APPDATA%. (I think I'd do this by opening a terminal window and echo %APPDATA% or cd %APPDATA%). -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
On Tue, 26 Sep 2006, Alvaro Herrera wrote: Jeanna Geier wrote: Searched again for 'pgpass' and for the 'Application Data' directory with no luck... The file is called pgpass.conf on Windows. As for the Application Data, it may be called differently if your Windows is localized -- try looking for %APPDATA%. (I think I'd do this by opening a terminal window and echo %APPDATA% or cd %APPDATA%). You can also just click start, run then type %appdata% and windows will open an explorer window in that directory. I guess it's also possible you need to turn on the view hidden and system directories in the explorer options to see/find in that directory, but I'm not sure. -- Jeff 'Frosty' Frost - AFM #996 - Frost Consulting, LLC Racing http://www.frostconsultingllc.com/ http://www.motonation.com/ http://www.suomy-usa.com/ http://www.motionpro.com/ http://www.motorexusa.com/ http://www.lockhartphillipsusa.com/ http://www.zoomzoomtrackdays.com/ http://www.braking.com/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
Thank you, Thank you, Thank you!! :o) Jeff - Thanks in particular for your help on this, it is greatly appreciated! It was a hidden folder, but not anymore!! I found the file and re-set the password for the 'postgres' user and can now connect using my 'md5' hostssl connection: hostssl all all 127.0.0.1/32 md5 __ C:\msys\1.0\local\pgsql\binpsql -d apt -U postgres Password: Welcome to psql 8.0.8, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256) Warning: Console code page (437) differs from Windows code page (1252) 8-bit characters may not work correctly. See psql reference page Notes for Windows users for details. apt=# Again, thanks for everyone's time and effort on this! This mailing list is top-notch!! -Jeanna - Original Message - From: Jeff Frost [EMAIL PROTECTED] To: Alvaro Herrera [EMAIL PROTECTED] Cc: Jeanna Geier [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql-hackers@postgresql.org Sent: Tuesday, September 26, 2006 12:35 PM Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues On Tue, 26 Sep 2006, Alvaro Herrera wrote: Jeanna Geier wrote: Searched again for 'pgpass' and for the 'Application Data' directory with no luck... The file is called pgpass.conf on Windows. As for the Application Data, it may be called differently if your Windows is localized -- try looking for %APPDATA%. (I think I'd do this by opening a terminal window and echo %APPDATA% or cd %APPDATA%). You can also just click start, run then type %appdata% and windows will open an explorer window in that directory. I guess it's also possible you need to turn on the view hidden and system directories in the explorer options to see/find in that directory, but I'm not sure. -- Jeff 'Frosty' Frost - AFM #996 - Frost Consulting, LLC Racing http://www.frostconsultingllc.com/ http://www.motonation.com/ http://www.suomy-usa.com/ http://www.motionpro.com/ http://www.motorexusa.com/ http://www.lockhartphillipsusa.com/ http://www.zoomzoomtrackdays.com/ http://www.braking.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] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues
I have run into the issue with our linux boxes connecting with the JDBC driver. Lucky for us our connections already go over encrypted VPN connections so I could get by with the following in my pg_hba.conf hostssl all all 192.168.176.0 255.255.255.0 md5 hostall all 192.168.176.2 255.255.255.255 md5 hostall all 192.168.176.9 255.255.255.255 md5 hostall all 192.168.176.21 255.255.255.255 md5 hostall all 192.168.176.22 255.255.255.255 md5 This will select the SSL connection first and then fall back to the non-ssl which are restricted to our tomcat web servers. This work around was set up in 7.4 of postgres. We are currently upgrading to 8.1, but I have not had a chance to revisit the SSL with JDBC yet. Woody IGLASS Networks -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jeanna Geier Sent: Tuesday, September 26, 2006 1:24 PM To: Jeff Frost Cc: Tom Lane; pgsql-admin@postgresql.org; pgsql-hackers@postgresql.org Subject: Re: [HACKERS] [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues Searched again for 'pgpass' and for the 'Application Data' directory with no luck... And, tell me it ain't so you don't have to build the windows version from source to use SSL -- I had two seperate posters tell me that I did and I wrestled with it for a bit...for nothing?? Ah, live and learn! :o) I don't think I'll consider myself a 'newbie' after this project is done. :o) - Original Message - From: Jeff Frost [EMAIL PROTECTED] To: Jeanna Geier [EMAIL PROTECTED] Cc: Tom Lane [EMAIL PROTECTED]; pgsql-admin@postgresql.org; pgsql-hackers@postgresql.org Sent: Tuesday, September 26, 2006 12:16 PM Subject: Re: [ADMIN] pg_hba.conf: 'trust' vs. 'md5' Issues On Tue, 26 Sep 2006, Jeanna Geier wrote: Any thoughts?? Like I said previously, I did build this on Windows from source so we could use the SSL option.could I have missed something when I was doing that? (It was my first time and I was following instructions from the INSTALL docs) Jeanna, see my earlier email regarding all the different variations and also where to find your pgpass file on windows. But, please note, you don't have to build the windows version from source to use SSL. The two binary versions I was using for testing both worked fine with SSL. ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Block B-Tree concept
On Tue, Sep 26, 2006 at 05:27:56PM +0100, Heikki Linnakangas wrote: Heikki Linnakangas wrote: Tom Lane wrote: Anything that involves having VACUUM re-evaluate index expressions is a nonstarter ... or have you already forgotten the optimizations we put into 8.2 that assume, eg, no sub-transactions within a VACUUM? Umm, I'm afraid I have. Could you give me a clue? I think I found it. Is this what you're talking about (in commands/vacuum.c): /* * During a lazy VACUUM we do not run any user-supplied functions, * and so it should be safe to not create a transaction snapshot. * * We can furthermore set the inVacuum flag, which lets other * concurrent VACUUMs know that they can ignore this one while * determining their OldestXmin. (The reason we don't set inVacuum * during a full VACUUM is exactly that we may have to run user- * defined functions for functional indexes, and we want to make * sure that if they use the snapshot set above, any tuples it * requires can't get removed from other tables. An index function * that depends on the contents of other tables is arguably broken, * but we won't break it here by violating transaction semantics.) * * Note: the inVacuum flag remains set until CommitTransaction or * AbortTransaction. We don't want to clear it until we reset * MyProc-xid/xmin, else OldestXmin might appear to go backwards, * which is probably Not Good. */ MyProc-inVacuum = true; Do I understand that to mean that you can no longer lazy vacuum a functional index? -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] Block B-Tree concept
On Tue, Sep 26, 2006 at 08:51:10AM -0400, Tom Lane wrote: 3. Do nothing. Let index scans mark the index tuple as dead when it's convenient. There's no correctness problem with just leaving dead index tuples there, because you have to check the index quals on each heap tuple anyway when you scan. And we're back to routine REINDEX I guess :-(. This doesn't seem like a satisfactory answer. Couldn't vacuum just eliminate tuples marked dead? Heck, don't we do that anyway right now? Granted, you'd want to periodically ensure that you scan the entire index, but that shouldn't be horribly hard to set up. -- Jim Nasby[EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) ---(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] -HEAD planner issue wrt hash_joins on dbt3 ?
Tom Lane wrote: Stefan Kaltenbrunner [EMAIL PROTECTED] writes: Tom Lane wrote: It evidently thinks that most of the rows in the join of part and partsupp won't have any matching rows in lineitem, whereas on average there are about 7 matching rows apiece. So that's totally wacko, and it's not immediately obvious why. Could we see the pg_stats entries for part.p_partkey, partsupp.ps_partkey, partsupp.ps_suppkey, lineitem.l_partkey, lineitem.l_suppkey? http://www.kaltenbrunner.cc/files/dbt3_beta1_statistics.txt OK, so we have 2 million parts and 10 suppliers, and ANALYZE doesn't seem to have been too far off at estimating either of those numbers. I think the problem is that there are not very many suppliers for any particular part, and thus the condition part match AND supplier match is really not much more selective than part match alone. The planner is supposing that their selectivities are independent, which they aren't. looks like there are exactly 4 suppliers for any given part so that seems indeed like the problem :-( Offhand I don't see any good way to fix this without multi-column statistics, which is something that's certainly not happening for 8.2 :-( too bad - however any idea on one of the other troubling querys (q21) I mentioned in the mail I resent to the list (after the original one got lost)? http://archives.postgresql.org/pgsql-hackers/2006-09/msg02011.php Stefan ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
