[HACKERS] [sepgsql] missing checks of process:transition on trusted procedure invocation
Sorry, I missed a permission check on invocation of trusted procedures. When client's label getting switched to Y from X, we needed to check process:transition permission between label X and label Y. It is same manner when OS launches a program with a special label to cause domain transition. The attached patch adds checks this permission when user tries to invoke a trusted procedure and switch security label of the client. In addition, it also adds a case of regression test of this problem. Thanks, -- NEC Europe Ltd, SAP Global Competence Center KaiGai Kohei kohei.kai...@eu.nec.com sepgsql-fix-domain-transition.1.patch Description: sepgsql-fix-domain-transition.1.patch -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Reading from a REFCURSOR in a C language function
Hi, I'm trying to write a C language function that has a REFCURSOR argument. Could anyone please give me an example of reading from a cursor in C code? -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
Hi, Robert Haas robertmh...@gmail.com writes: The attached patch merges synchronous_replication into synchronous_commit. Committed Without discussion? I would think that this patch is stepping on the other one toes and that maybe would need to make a decision about sync rep behavior before to commit this change. Maybe it's just me, but I'm struggling to understand current community processes and decisions. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
On Tue, Apr 5, 2011 at 4:53 PM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Hi, Robert Haas robertmh...@gmail.com writes: The attached patch merges synchronous_replication into synchronous_commit. Committed Without discussion? I would think that this patch is stepping on the other one toes and that maybe would need to make a decision about sync rep behavior before to commit this change. Hmm.. I think that we reached the consensus about merging two GUCs in previous discussion. You argue that synchronization level should be controlled in separate two parameters? Regards, -- Fujii Masao NIPPON TELEGRAPH AND TELEPHONE CORPORATION NTT Open Source Software Center -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
Fujii Masao masao.fu...@gmail.com writes: Hmm.. I think that we reached the consensus about merging two GUCs in previous discussion. You argue that synchronization level should be controlled in separate two parameters? No, sorry about confusion. One GUC is better. What I'm wondering is why commit it *now*, because I think we didn't yet decide on what the supported behaviors supported in 9.1 should be. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
On 05.04.2011 11:31, Dimitri Fontaine wrote: Fujii Masaomasao.fu...@gmail.com writes: Hmm.. I think that we reached the consensus about merging two GUCs in previous discussion. You argue that synchronization level should be controlled in separate two parameters? No, sorry about confusion. One GUC is better. What I'm wondering is why commit it *now*, because I think we didn't yet decide on what the supported behaviors supported in 9.1 should be. What do you mean by supported behaviors? -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
Heikki Linnakangas heikki.linnakan...@enterprisedb.com writes: No, sorry about confusion. One GUC is better. What I'm wondering is why commit it *now*, because I think we didn't yet decide on what the supported behaviors supported in 9.1 should be. What do you mean by supported behaviors? Well, I'm thinking about Simon's patch that some decided on procedural grounds only that it was too late to apply in 9.1, and some others were saying that given the cost benefit ratio of such a small patch that the design had already been agreed on, it is legible for 9.1. I think that we just expressed opinions on the async|recv|fsync|apply patch, and that we've yet to reach a consensus and make a decision. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
On Mon, Apr 4, 2011 at 9:01 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 4, 2011 at 12:38 PM, Alexander Korotkov aekorot...@gmail.com wrote: relatively small when q = 5. Accordingly, I think we should expect indexes to be usable with at least with q = 5. I defer to your opinion on this, since you know more about it than I do. But I think it would still be worthwhile to write a quick Perl script and calculate the number q-grams in various sample texts for various values of q. The worst case is surely exponential in q, so it'd be nice to have some evidence of what the real-world behavior is. Here is distribution of numbers of different q-grams count in various datasets. Q-grams didn't pass any preprocessing, preprocessed q-grams (for example, lowercased) should have lower counts. q ds1 ds2 ds3ds4 2 231334611625 1288 315146 25094 14090 10728 458510 105908 69127 47499 5 161801 298466 182680 110929 6 351175 633750 331090 176336 7 613299 1049088 496426 234730 8 921962 1450715 657965 283698 9 1248339 1793158 802188 321261 10 1556838 2066775 926043 348058 ds1 - J. R. R. Tolkien, The Lord of the Rings, 2805204 bytes ds2 - Leo Tolstoy, War and Peace volume 1, 3197190 bytes ds3 - set of person first and last names, 2142298 bytes ds4 - english dictionary, 931708 bytes Sure, q-grams count grows with q increasing. At low q we can see approximately exponential grow. At high q grow is slowing and it is approximately linear. In the worst case count of q-grams is exponential in q if we think data volume to be much higher then number of possible q-grams. But with high q real limitation is total number of q-grams extracted from dataset. In worst case each extracted q-gram is unique. This means that entries pages number would be comparable with data pages number. In this case index size with high q would be few times greater that index size with low q. With best regards, Alexander Korotkov.
Re: [HACKERS] Re: [COMMITTERS] pgsql: Support comments on FOREIGN DATA WRAPPER and SERVER objects.
On Tue, 05 Apr 2011 13:37:48 +0900 Shigeru HANADA han...@metrosystems.co.jp wrote: On Mon, 4 Apr 2011 12:47:18 -0400 Robert Haas robertmh...@gmail.com wrote: BTW, I think you can merge patches 0001 to 0004 into a single patch. They were separated just for review, so I'll post revised and unified patch ASAP. Please find attached revised comment-on-user-mapping patches. * The comment_user_mapping_core.patch includes syntax support, catalog manipulation, pg_dump support, documents and regression tests. Some functions were exposed to merge logic of user mapping owner check. * The comment_user_mapping_psql.patch includes only psql tab-completion feature. It can be applied separately. Regards, -- Shigeru Hanada comment_user_mapping_core.patch Description: Binary data comment_user_mapping_psql.patch Description: Binary data -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transforming IN (...) to ORs, volatility
On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: We sometimes transform IN-clauses to a list of ORs: postgres=# explain SELECT * FROM foo WHERE a IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..39.10 rows=19 width=12) Filter: ((a = b) OR (a = c)) But what if you replace a with a volatile function? It doesn't seem legal to do that transformation in that case, but we do it: postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..68.20 rows=19 width=12) Filter: random() * 2::double precision))::integer = b) OR (((random() * 2::double precision))::integer = c)) Is there a similar problem with the BETWEEN clause transformation into AND expressions? marti= explain verbose select random() between 0.25 and 0.75; Result (cost=0.00..0.02 rows=1 width=0) Output: ((random() = 0.25::double precision) AND (random() = 0.75::double precision)) As expected, I get a statistical skew of 0.4375 / 0.5625, whereas the correct would be 50/50: marti= select random() between 0.25 and 0.75 as result, count(*) from generate_series(1,100) i group by 1; result | count + f | 437262 t | 562738 I also always noticed that BETWEEN with subqueries produces two subplan nodes, this seems suboptimal. marti= explain verbose select (select random()) between 0.25 and 0.75; Result (cost=0.03..0.04 rows=1 width=0) Output: (($0 = 0.25::double precision) AND ($1 = 0.75::double precision)) InitPlan 1 (returns $0) - Result (cost=0.00..0.01 rows=1 width=0) Output: random() InitPlan 2 (returns $1) - Result (cost=0.00..0.01 rows=1 width=0) Output: random() Regards, Marti -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
On Tue, Apr 5, 2011 at 3:53 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: The attached patch merges synchronous_replication into synchronous_commit. Committed Without discussion? I would think that this patch is stepping on the other one toes and that maybe would need to make a decision about sync rep behavior before to commit this change. Err, I thought we did. We had a protracted discussion of Simon's patch: 9 people expressed an opinion; 6 were opposed. With respect to this patch, the basic design was discussed previously and Simon, Fujii Masao, Greg Stark and myself all were basically in favor of something along these lines, and to the best of my recollection no one spoke against it. Maybe it's just me, but I'm struggling to understand current community processes and decisions. Well, I've already spent a fair amount of time trying to explain my understanding of it, and for my trouble I got accused of being long-winded. Which is probably true, but makes me think I should probably keep this response short. I'm not unwilling to talk about it, though, and perhaps someone else would like to chime in. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
On Tue, Apr 5, 2011 at 4:52 AM, Alexander Korotkov aekorot...@gmail.com wrote: On Mon, Apr 4, 2011 at 9:01 PM, Robert Haas robertmh...@gmail.com wrote: On Mon, Apr 4, 2011 at 12:38 PM, Alexander Korotkov aekorot...@gmail.com wrote: relatively small when q = 5. Accordingly, I think we should expect indexes to be usable with at least with q = 5. I defer to your opinion on this, since you know more about it than I do. But I think it would still be worthwhile to write a quick Perl script and calculate the number q-grams in various sample texts for various values of q. The worst case is surely exponential in q, so it'd be nice to have some evidence of what the real-world behavior is. Here is distribution of numbers of different q-grams count in various datasets. Q-grams didn't pass any preprocessing, preprocessed q-grams (for example, lowercased) should have lower counts. q ds1 ds2 ds3 ds4 2 2313 3461 1625 1288 3 15146 25094 14090 10728 4 58510 105908 69127 47499 5 161801 298466 182680 110929 6 351175 633750 331090 176336 7 613299 1049088 496426 234730 8 921962 1450715 657965 283698 9 1248339 1793158 802188 321261 10 1556838 2066775 926043 348058 ds1 - J. R. R. Tolkien, The Lord of the Rings, 2805204 bytes ds2 - Leo Tolstoy, War and Peace volume 1, 3197190 bytes ds3 - set of person first and last names, 2142298 bytes ds4 - english dictionary, 931708 bytes Sure, q-grams count grows with q increasing. At low q we can see approximately exponential grow. At high q grow is slowing and it is approximately linear. In the worst case count of q-grams is exponential in q if we think data volume to be much higher then number of possible q-grams. But with high q real limitation is total number of q-grams extracted from dataset. In worst case each extracted q-gram is unique. This means that entries pages number would be comparable with data pages number. In this case index size with high q would be few times greater that index size with low q. So with q=5, the index will be approximately 10x larger than with q=3. Maybe that's OK, I'm not sure. But it is a big difference. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
On Tue, Apr 5, 2011 at 3:56 PM, Robert Haas robertmh...@gmail.com wrote: So with q=5, the index will be approximately 10x larger than with q=3. Maybe that's OK, I'm not sure. But it is a big difference. Not whole index will be approximately 10x larger, but only entries pages number (which contains btree on gin keys, i.e. q-grams), while data pages number (which contains links to rows in lists or btrees) will be similar. In dependence on data volume index size can be 10x larger (on small datasets) or few percents larger (on large datasets). With best regards, Alexander Korotkov.
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
For example, here is distribution of q-grams count in 120 Mb of dblp paper titles (pretty large dataset). q count 27218 3 115107 4 589428 5 1648453 6 3336685 Number of 5-grams if about 15x larger than number of 3-grams. But most part of index space will be occupied by links to the rows(about 120 millions of links), while size of q-grams itself will be almost ignorable in comparison with it. With best regards, Alexander Korotkov.
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
On Tue, Apr 5, 2011 at 8:41 AM, Alexander Korotkov aekorot...@gmail.com wrote: For example, here is distribution of q-grams count in 120 Mb of dblp paper titles (pretty large dataset). q count 2 7218 3 115107 4 589428 5 1648453 6 3336685 Number of 5-grams if about 15x larger than number of 3-grams. But most part of index space will be occupied by links to the rows(about 120 millions of links), while size of q-grams itself will be almost ignorable in comparison with it. I am probably being stupid here, but doesn't the number of links to rows grow proportionately to the number of n-grams? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] 9.0.3 SIGFAULT on FreeBSD with dtrace
On Saturday, April 02, 2011 09:12:32 PM Tom Lane's cat walking on the keyboard wrote: It's possible that we need to adjust PG's dtrace code to support the FreeBSD implementation, but if so we'd need advice from an expert on what needs to be changed. Thanks. In the meantime I attached a debugger and found that postgres crashes immediatly as it enters in dtrace_dof_init(), I don't know what it does. However I'm asking on the freebsd forums for help and involvement. Luca -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions Dependency Checking
Aidan Van Dyk ai...@highrise.ca writes: I think the general movement is toward *feature* dependancies. So for intstance, an extension can specify what *feature* it requires, and difference versions of an extension can provide different features. That sounds like what Emacs is doing too. But checking http://developer.postgresql.org/pgdocs/postgres/extend-extensions.html, I don't see any provides mechanism. That might be something actually needed if we are trying to avoid version comparisons and want to be describing actual dependencies... The 'provides' mechanism can be added later I think. It's like saying that in 9.1 an extension 'foo' provides the feature 'foo' and you can't control that, whereas in future version you will be able to control what your extension (and its specific version) provides. Also we will be able to list what the PostgreSQL server provides, maybe, so that compile time options can be depended on by extensions. Regards, -- Dimitri Fontaine http://2ndQuadrant.fr PostgreSQL : Expertise, Formation et Support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] cast from integer to money
On Tue, Apr 5, 2011 at 1:10 AM, Joseph Adams joeyadams3.14...@gmail.com wrote: Attached is an updated version of the patch to allow conversion of int4/int8 directly to money. I added overflow checks, dropped int2-cash, and updated the documentation. Excellent, thanks. My only gripe is that I don't think we should duplicate int8mul, so I've changed your patch to use this incantation: + result = DatumGetInt64(DirectFunctionCall2(int8mul, Int64GetDatum(amount + Int64GetDatum(scale))); ...which is parallel to what the existing numeric - money cast already does. That results in a slightly different error message, but I think that's OK: no one has complained about the numeric - cash error message, or the fact that the remaining functions in this module do no overflow checking at all. With that change, committed. Thanks for picking this one up. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
On Tue, Apr 5, 2011 at 5:05 PM, Robert Haas robertmh...@gmail.com wrote: I am probably being stupid here, but doesn't the number of links to rows grow proportionately to the number of n-grams? Number of links to rows grow proportionally to total number of extracted q-grams, but not proportionally to number of unique q-grams. Though, if extracted q-grams are not unique inside same indexed value, then it can reduce number of links (but it is rarity). Lets consider simple example. Two rows contains strings 'aaa' and 'aaab'. We extract 3-gram 'aaa' from first string and 3-grams 'aaa' and 'aab' from second string (for simplicity, there is no padding here). GIN index will contain structure, which can be represented so: 'aaa' = 1, 2 'aab' = 2 We can see, that there are 2 unique 3-grams, but 3 links to the rows. With best regards, Alexander Korotkov.
Re: [HACKERS] Typed-tables patch broke pg_upgrade
On Wed, Mar 30, 2011 at 9:32 PM, Noah Misch n...@leadboat.com wrote: On Wed, Mar 30, 2011 at 07:50:12PM +0300, Peter Eisentraut wrote: On tor, 2011-02-10 at 06:31 +0200, Peter Eisentraut wrote: ERROR: cannot drop column from typed table which probably is because test_type2 has a dropped column. It should call ALTER TYPE test_type2 DROP ATTRIBUTE xyz CASCADE; instead. That will propagate to the table. Here is a patch that addresses this problem. This only works when exactly one typed table uses each composite type having dropped columns. With zero users, the placeholder column never gets dropped. Actually, it happens to work for 1 user, but only because ALTER TYPE mistakenly only touches the first table-of-type: create type t as (x int, y int); create table is_a of t; create table is_a2 of t; alter type t drop attribute y cascade, add attribute z int cascade; \d is_a Table public.is_a Column | Type | Modifiers +-+--- x | integer | z | integer | Typed table of type: t \d is_a2 Table public.is_a2 Column | Type | Modifiers +-+--- x | integer | y | integer | Typed table of type: t Might be a simple fix; looks like find_typed_table_dependencies() only grabs the first match. Incidentally, this led me to notice that you can hang a typed table off a table row type. ALTER TABLE never propagates to such typed tables, allowing them to get out of sync: create table t (x int, y int); create table is_a of t; create table is_a2 of t; alter table t drop y, add z int; \d is_a Table public.is_a Column | Type | Modifiers +-+--- x | integer | y | integer | Typed table of type: t Perhaps we should disallow the use of table row types in CREATE TABLE ... OF? It looks like Noah Misch might have found another problem in this area. We'll have to investigate that. Your bits in dumpCompositeType() are most of what's needed to fix that, I think. Where are we on this? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
For what it's worth it seems to me this patch makrmes it at least conceptually easier to add new modes like Simon plans, not harder. It's worth making sure we pick names that still make sense when the new functionality goes in of course. The other question is whether it's fair that one kind of patch goes in and not the other. Personally I feel changes to GUCs are the kind of thing we most often want to do in alpha. Patches that change functionality require a higher barrier and need to be fixing user complaints or bugs. My perception was that Simon's patch was ggreenberg latter. On Apr 5, 2011 12:52 PM, Robert Haas robertmh...@gmail.com wrote: On Tue, Apr 5, 2011 at 3:53 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Robert Haas robertmh...@gmail.com writes: The attached patch merges synchronous_replication into synchronous_commit. Committed Without discussion? I would think that this patch is stepping on the other one toes and that maybe would need to make a decision about sync rep behavior before to commit this change. Err, I thought we did. We had a protracted discussion of Simon's patch: 9 people expressed an opinion; 6 were opposed. With respect to this patch, the basic design was discussed previously and Simon, Fujii Masao, Greg Stark and myself all were basically in favor of something along these lines, and to the best of my recollection no one spoke against it. Maybe it's just me, but I'm struggling to understand current community processes and decisions. Well, I've already spent a fair amount of time trying to explain my understanding of it, and for my trouble I got accused of being long-winded. Which is probably true, but makes me think I should probably keep this response short. I'm not unwilling to talk about it, though, and perhaps someone else would like to chime in. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Re: [HACKERS] Open issues for collations
Reading through this thread... On Sat, Mar 26, 2011 at 12:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: ** Selecting a field from a record-returning function's output. Currently, we'll use the field's declared collation; except that if the field has default collation, we'll replace that with the common collation of the function's inputs, if any. Is either part of that sane? Do we need to make this work for functions invoked with other syntax than a plain function call, eg operator or cast syntax? There were a couple of different ideas about which way we ought to go with this, but I'm happy to defer to what Tom and Martijn hashed out: MO That seems all a bit weird. I spent some time reading through the SQL MO spec to see if I could came up with a few ideas about what they thought MO relevent. I think the gist of it is that I think the result row should MO have for each column its declared collation in all cases. TL That interpretation would be fine with me. It would let us get rid of TL the special-case code at lines 307-324 of parse_collate.c, which I put TL in only because there are cases in the collate.linux.utf8.sql regression TL test that fail without it. But I'm perfectly happy to conclude that TL those test cases are mistaken. I'm not sure whether that's been done, though, or whether we're even going to do it. ** What to do with domains whose declaration includes a COLLATE clause? Currently, we'll impute that collation to the result of a cast to the domain type --- even if the cast's input expression includes an explicit COLLATE clause. It's not clear that that's per spec. If it is correct, should we behave similarly for functions that are declared to return a domain type? Should it matter if the cast-to-domain is explicit or implicit? Perhaps it'd be best if domain collations only mattered for columns declared with that domain type. Then we'd have a general rule that collations only come into play in an expression as a result of (a) the declared type of a column reference or (b) an explicit COLLATE clause. I think we had agreement than a cast to a domain type with a collation should stomp on any existing collation on the contained expression. * In plpgsql, is it OK for declared local variables to inherit the function's input collation? Should we provide a COLLATE option in variable declarations to let that be overridden? I think everyone who responded said yes to both questions. * RI triggers should insert COLLATE clauses in generated queries to satisfy SQL2008 9.13 SR 4a, which says that RI comparisons use the referenced column's collation. Right now you may get either table's collation depending on which query type is involved. I think an obvious failure may not be possible so long as equality means the same thing in all collations, but it's definitely possible that the planner might decide it can't use the referenced column's unique index, which would suck for performance. (Note: this rule seems to prove that the committee assumes equality can mean different things in different collations, else they'd not have felt the need to specify.) I tested this and, indeed, if the collations don't match, the index can't be used. CREATE TABLE me (x character varying COLLATE en_US); CREATE TABLE me2 (x character varying COLLATE es_ES); CREATE TABLE you (x character varying COLLATE es_ES NOT NULL, PRIMARY KEY (x)); ALTER TABLE me ADD FOREIGN KEY (x) REFERENCES you(x); ALTER TABLE me2 ADD FOREIGN KEY (x) REFERENCES you(x); SET enable_seqscan=false; SET enable_hashjoin=false; With that setup, this still does a seqscan-and-sort: EXPLAIN select * from me, you where me.x = you.x; But this uses the index: EXPLAIN select * from me2, you where me2.x = you.x; I found another problem, too: rhaas=# insert into you values ('1'); INSERT 0 1 rhaas=# insert into me values ('1'); INSERT 0 1 rhaas=# alter table me alter column x set data type varchar collate en_GB; ERROR: could not determine which collation to use for string comparison HINT: Use the COLLATE clause to set the collation explicitly. CONTEXT: SQL statement SELECT fk.x FROM ONLY public.me fk LEFT OUTER JOIN ONLY public.you pk ON ( pk.x::pg_catalog.text OPERATOR(pg_catalog.=) fk.x::pg_catalog.text) WHERE pk.x IS NULL AND (fk.x IS NOT NULL) * It'd sure be nice if we had some nontrivial test cases that work in encodings besides UTF8. I'm still bothered that the committed patch failed to cover single-byte-encoding cases in upper/lower/initcap. Seems like no one knows how to do this. * Remove initdb's warning about useless locales? Seems like pointless noise, or at least something that can be relegated to debug mode. Everyone was in favor of this. * Is it worth adding a cares-about-collation flag to pg_proc? Probably too late to be worrying about such refinements for 9.1. Probably too late for this. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company --
Re: [HACKERS] time table for beta1
Dan Ports d...@csail.mit.edu writes: On Mon, Apr 04, 2011 at 07:04:59PM -0400, Robert Haas wrote: On Mon, Apr 4, 2011 at 6:41 PM, Stephen Frost sfr...@snowman.net wrote: What'd be horribly useful would be the pid and the *time* that the lock was taken. Well, I don't think we're likely to redesign pg_locks at this point, so it's a question of making the best use of the fields we have to work with. Agreed. Note that the vxid of the transaction that took the lock is included in there, so that's at least something you could correlate with a logfile. Another problem is that supporting that would imply injecting gettimeofday() into the lock-acquisition sequence, and that's pretty damn expensive on some platforms. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Proposal: q-gram GIN and GiST indexes
Alexander Korotkov aekorot...@gmail.com writes: On Tue, Apr 5, 2011 at 5:05 PM, Robert Haas robertmh...@gmail.com wrote: I am probably being stupid here, but doesn't the number of links to rows grow proportionately to the number of n-grams? Number of links to rows grow proportionally to total number of extracted q-grams, but not proportionally to number of unique q-grams. Sure. The number of links is exactly proportional to the size of the text, no? An n-character text contains exactly n-q+1 q-grams, no more, no less. You might have some rules that cause you to discard some of them, but basically the TID portion of the index will be proportional to data volume, with no measurable dependence on q. Or at least that's what it seems like before I've had my morning caffeine fix... regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
Robert Haas robertmh...@gmail.com wrote: Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Maybe it's just me, but I'm struggling to understand current community processes and decisions. Well, I've already spent a fair amount of time trying to explain my understanding of it, and for my trouble I got accused of being long-winded. Which is probably true, but makes me think I should probably keep this response short. I'm not unwilling to talk about it, though, and perhaps someone else would like to chime in. I rather liked the brief comment in a recent post of yours where you said that at this point we should only be accepting patches which stabilize what has already been committed, rather than new features which might require further stabilization. I don't know whether the patch under discussion satisfies that test, but that should be the main consideration at this point in the release cycle, in my view. Of course, with anything this complex there will be gray areas where people could have honest disagreement. -Kevin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
Kevin Grittner kevin.gritt...@wicourts.gov writes: Robert Haas robertmh...@gmail.com wrote: Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Maybe it's just me, but I'm struggling to understand current community processes and decisions. Well, I've already spent a fair amount of time trying to explain my understanding of it, and for my trouble I got accused of being long-winded. Which is probably true, but makes me think I should probably keep this response short. I'm not unwilling to talk about it, though, and perhaps someone else would like to chime in. I rather liked the brief comment in a recent post of yours where you said that at this point we should only be accepting patches which stabilize what has already been committed, rather than new features which might require further stabilization. Quite. While we're on the subject, why did that int-money patch get committed so quickly? I had assumed that was 9.2 material, because it didn't seem to be addressing any new-in-9.1 issue. I'm not going to ask for it to be backed out, but I am wondering what the decision process was. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Set hint bits upon eviction from BufMgr
On Mar 28, 2011, at 9:48 AM, Merlin Moncure wrote: On Mon, Mar 28, 2011 at 9:29 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: The major problem with all of this is that the bgwriter has no idea which buffers contain heap pages. And I'm not convinced it's a good idea to try to let it know that. If we get to the point where bgwriter is trying to do catalog accesses, we are in for a world of pain. (Can you say modularity violation? How about deadlock?) How about having a BackgroundPrepareForWriteFunction variable associated with each page the bgwriter might see, which would be a pointer to a function to call (if the variable is not NULL) before writing? The bgwriter would still have no idea what kind of page it was or what the function did Well, that is much cleaner from abstraction point of view but you lose the ability to adjust scan priority before flushing out the page...I'm assuming by the time this function is called, you've already made the decision to write it out. (maybe priority is necessary and maybe it isn't, but I don't like losing the ability to tune at that level). You could though put a priority inspection facility behind a similar abstraction fence (BackgroundGetWritePriority) though. Maybe that's more trouble than it's worth though. Merlin, does your new work on CLOG caching negate anything in this thread? I think there's some ideas here worth further investigation and want to make sure they don't get lost. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Recursive containment of composite types
On Mar 28, 2011, at 10:43 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 03/28/2011 11:14 AM, Tom Lane wrote: I think the most straightforward and reliable fix for this would be to forbid recursive containment of a rowtype in itself --- ie, the first ALTER should have been rejected. Can anyone think of a situation where it would be sane to allow such a thing? I think we should forbid it for now. If someone comes up with a) a good way to make it works and b) a good use case, we can look at it then. I expect the PostgreSQL type system to be a good deal more constrained than a general in-memory programming language type system. If lack of working type recursion were a serious problem surely we'd have seen more squawks about this by now. The immediate issue in CheckAttributeType() could be fixed by tracking which types it was processing and not recursing into an already-open type. Which, not at all coincidentally, is 90% the same code it'll need to have to throw error. The issue for really making it work is how do we know if there are any other places that need a recursion defense? I'm pretty sure that find_composite_type_dependencies would, and I don't know where else there might be a hidden assumption that column references don't loop. So I think that it's mostly about testing rather than anything else. If I were fairly confident that I knew where all the risk spots were, I'd just fix them rather than trying to forbid the construction. Perhaps forbid it for now (for safety) but provide the reporter with a patch that would unblock them so they can do further testing? The concept is certainly interesting so it'd be nice to support it if we could. It seems like a good fit for things like storing tree structures. Though, if the type is still hauling around a heap tuple header I think they'll find the performance of this whole thing to be rather lacking... :( -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Mar 28, 2011, at 3:18 PM, Peter Eisentraut wrote: On lör, 2011-03-26 at 09:41 -0400, Robert Haas wrote: You can't be guaranteed that they won't standardize something incompatible no matter what we do. We could choose to do it as you've proposed and they could then standardize some weird syntax - the = is a fairly relevant example of exactly that. The matter of how to resolve SQL parameter names is already standardized. See clause on identifier chain. Was there a final consensus on this? FWIW, if we go with using function name, it'd be nice to be allowed to alias that. I don't have a strong opinion between that and using : or $ or whatever. I do feel strongly that we must continue to support existing SQL functions in a reasonable fashion. Having the function blow up on the first invocation is no better than breaking the dump. There should be either a backwards-compatibility mode, or better yet, a way to automatically convert functions to be compatible with the new syntax. -- Jim C. Nasby, Database Architect j...@nasby.net 512.569.9461 (cell) http://jim.nasby.net -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
On Tue, Apr 5, 2011 at 11:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Kevin Grittner kevin.gritt...@wicourts.gov writes: Robert Haas robertmh...@gmail.com wrote: Dimitri Fontaine dimi...@2ndquadrant.fr wrote: Maybe it's just me, but I'm struggling to understand current community processes and decisions. Well, I've already spent a fair amount of time trying to explain my understanding of it, and for my trouble I got accused of being long-winded. Which is probably true, but makes me think I should probably keep this response short. I'm not unwilling to talk about it, though, and perhaps someone else would like to chime in. I rather liked the brief comment in a recent post of yours where you said that at this point we should only be accepting patches which stabilize what has already been committed, rather than new features which might require further stabilization. Quite. While we're on the subject, why did that int-money patch get committed so quickly? I had assumed that was 9.2 material, because it didn't seem to be addressing any new-in-9.1 issue. I'm not going to ask for it to be backed out, but I am wondering what the decision process was. Well, I posted a note about this on Thursday: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01930.php I didn't feel strongly that it needed to be done, but there seemed to be some support for doing it: http://archives.postgresql.org/pgsql-hackers/2011-03/msg01940.php http://archives.postgresql.org/pgsql-hackers/2011-03/msg01943.php But I'm wondering whether that was really the right decision. It might have been better just to drop it, and I'll certainly back it out if people feel that's more appropriate. I am also wondering about the open issue of supporting comments to SQL/MED objects. I thought that was pretty straightforward, but given that it took me three commits to get servers and foreign data wrappers squared away and then it turned out that we're still missing support for user mappings, I've been vividly reminded of the danger of seemingly harmless commits. Now I'm thinking that I should have just replied to the initial report with good point, but it's not a new regression, so we'll fix it in 9.2. But given that part of the work has already been done, I'm not sure whether I should (a) finish it, so we don't have to revisit this in 9.2, (b) leave it well enough alone, and we'll finish it in 9.2, or (c) back out what's already been done and plan to fix the whole thing in 9.2. Everything else on the open items list appears to be a bona fide bug, though the generate_series thing is not a new regression. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transforming IN (...) to ORs, volatility
On 05.04.2011 13:19, Marti Raudsepp wrote: On Fri, Apr 1, 2011 at 14:24, Heikki Linnakangas heikki.linnakan...@enterprisedb.com wrote: We sometimes transform IN-clauses to a list of ORs: postgres=# explain SELECT * FROM foo WHERE a IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..39.10 rows=19 width=12) Filter: ((a = b) OR (a = c)) But what if you replace a with a volatile function? It doesn't seem legal to do that transformation in that case, but we do it: postgres=# explain SELECT * FROM foo WHERE (random()*2)::integer IN (b, c); QUERY PLAN Seq Scan on foo (cost=0.00..68.20 rows=19 width=12) Filter: random() * 2::double precision))::integer = b) OR (((random() * 2::double precision))::integer = c)) Is there a similar problem with the BETWEEN clause transformation into AND expressions? marti= explain verbose select random() between 0.25 and 0.75; Result (cost=0.00..0.02 rows=1 width=0) Output: ((random()= 0.25::double precision) AND (random()= 0.75::double precision)) Yes, good point. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Set hint bits upon eviction from BufMgr
On Tue, Apr 5, 2011 at 9:49 AM, Jim Nasby j...@nasby.net wrote: On Mar 28, 2011, at 9:48 AM, Merlin Moncure wrote: On Mon, Mar 28, 2011 at 9:29 AM, Kevin Grittner kevin.gritt...@wicourts.gov wrote: Tom Lane t...@sss.pgh.pa.us wrote: The major problem with all of this is that the bgwriter has no idea which buffers contain heap pages. And I'm not convinced it's a good idea to try to let it know that. If we get to the point where bgwriter is trying to do catalog accesses, we are in for a world of pain. (Can you say modularity violation? How about deadlock?) How about having a BackgroundPrepareForWriteFunction variable associated with each page the bgwriter might see, which would be a pointer to a function to call (if the variable is not NULL) before writing? The bgwriter would still have no idea what kind of page it was or what the function did Well, that is much cleaner from abstraction point of view but you lose the ability to adjust scan priority before flushing out the page...I'm assuming by the time this function is called, you've already made the decision to write it out. (maybe priority is necessary and maybe it isn't, but I don't like losing the ability to tune at that level). You could though put a priority inspection facility behind a similar abstraction fence (BackgroundGetWritePriority) though. Maybe that's more trouble than it's worth though. Merlin, does your new work on CLOG caching negate anything in this thread? I think there's some ideas here worth further investigation and want to make sure they don't get lost. No, they don't -- and I plan to work on this independently. The performance tradeoffs here are much more complicated and will require extensive benchmarking to analyze. A process local clog cache, if it can be made to work (and that's be no means certain) is going to affect how this is put together. In particular, i'd be even more disinclined to adjust scan priorty or do anything fancy like that -- and more amenable to checking every tuple. I'm particularly interested in setting the PD_ALL_VISIBLE bit at eviction time if it's available to be set and the page is already dirty. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler da...@kineticode.com wrote: On Mar 25, 2011, at 9:12 PM, Robert Haas wrote: As I've said before, I believe that the root cause of this problem is that using the same syntax for variables and column names is a bad idea in the first place. If we used $foo or ?foo or ${foo} or $.foo or foo!!$#? to mean the parameter called foo, then this would all be a non-issue. Yes *please*. Man that would make maintenance of such functions easier. +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Support comments on FOREIGN DATA WRAPPER and SERVER objects.
On Tue, Apr 5, 2011 at 6:03 AM, Shigeru HANADA han...@metrosystems.co.jp wrote: On Tue, 05 Apr 2011 13:37:48 +0900 Shigeru HANADA han...@metrosystems.co.jp wrote: On Mon, 4 Apr 2011 12:47:18 -0400 Robert Haas robertmh...@gmail.com wrote: BTW, I think you can merge patches 0001 to 0004 into a single patch. They were separated just for review, so I'll post revised and unified patch ASAP. Please find attached revised comment-on-user-mapping patches. * The comment_user_mapping_core.patch includes syntax support, catalog manipulation, pg_dump support, documents and regression tests. I don't think it's going to fly to add a function pg_usermapping_ownercheck() with a randomly different API than all the parallel functions for other object types. There is probably some more refactoring that needs to be done here to make this sane, but I'm coming around to the view that trying to slip this into 9.1 is not the best thing for us to be spending time on, especially considering that it doesn't seem to be straightforward to figure out how it should actually work. I am inclined to punt this to 9.2. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
On Tue, Apr 5, 2011 at 11:25 AM, Robert Haas robertmh...@gmail.com wrote: I am also wondering about the open issue of supporting comments to SQL/MED objects. I thought that was pretty straightforward, but given that it took me three commits to get servers and foreign data wrappers squared away and then it turned out that we're still missing support for user mappings, I've been vividly reminded of the danger of seemingly harmless commits. Now I'm thinking that I should have just replied to the initial report with good point, but it's not a new regression, so we'll fix it in 9.2. But given that part of the work has already been done, I'm not sure whether I should (a) finish it, so we don't have to revisit this in 9.2, (b) leave it well enough alone, and we'll finish it in 9.2, or (c) back out what's already been done and plan to fix the whole thing in 9.2. On further review, I think (a) is not even an option worth discussing. The permissions-checking logic for user mappings is quite different from what we do in the general case, and it seems likely to me that cleaning this up is going to require far more time and thought than we ought to be putting into what is really a relatively minor wart. In retrospect, it seems clear that this wasn't worth messing with in the first place at this late date in the release cycle. If there are any other items on the open items list that seem like things we should not be worrying about right now, please point them out. I'm likely guilty of tunnel vision, as I have been heavily focused on trying to make the list go to zero, and of course committing stuff is only one of two possible ways to get them off the list - the other is to decide that that they shouldn't have been added in the first place. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reading from a REFCURSOR in a C language function
On 4/5/2011 3:24 AM, Vlad Arkhipov wrote: Hi, I'm trying to write a C language function that has a REFCURSOR argument. Could anyone please give me an example of reading from a cursor in C code? Sorry, I don't have a code example. A refcursor data type is basically a string, containing the name of an open cursor (portal). It is stored binary compatible to the text data type. In the C function, you extract that name (using the textout function) and use it inside the FETCH query as the cursor name. You may need to double-quote that string. Jan -- Anyone who trades liberty for security deserves neither liberty nor security. -- Benjamin Franklin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Support comments on FOREIGN DATA WRAPPER and SERVER objects.
On mån, 2011-04-04 at 19:49 +0900, Shigeru HANADA wrote: 1) Who can comment on a user mapping? I'm not sure that it's necessary to allow commenting on user mappings. You can't comment on role grants either, for example. They're somewhat similar things. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. What standardization risk? The standard has already existed for 10 years and is widely implemented. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] small fix for Windows build
On mån, 2011-04-04 at 17:08 -0400, Andrew Dunstan wrote: On 04/04/2011 04:41 PM, Peter Eisentraut wrote: My not yet complete attempt at doing a Windows build produces several of these warnings during the build phase: Hash %ENV missing the % in argument 1 of each() at -e line 1. I believe the attached patch is the fix for that. I am not seeing any such errors on currawong or mastodon. So I'm not sure what you're doing that's causing you to get the errors, or if your platform is different. But I think we need to get to the bottom of it before changing something that's working. My Perl installation is 5.12, which is the latest from ActiveState. According to http://dev.perl.org/perl5/news/2010/perl-5.12.0.html, Perl now warns the user about the use of deprecated features by default. (I also see a bunch of other warnings, btw.) The code in question is perl -e require 'src/tools/msvc/buildenv.pl'; while(($k,$v) = each %ENV) { print qq[\@SET $k=$v\n]; } bldenv.bat The % is apparently interpolated, and I guess that since there is no bat variable %ENV, it just removes the % and effectively executes perl -e require 'src/tools/msvc/buildenv.pl'; while(($k,$v) = each ENV) { print qq[\@SET $k=$v\n]; } bldenv.bat which will draw a warning when run with -w in any recent Perl version. If you replace the % by %%, one % will remain in the final command. I suggest that someone who is more fluent with the Windows build either run the whole build with Perl 5.12, or with Perl 5.10 plus warnings, and clean it up. In the future, this stuff might break. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On mån, 2011-04-04 at 15:02 -0400, Robert Haas wrote: AFAICT, the biggest problem with our existing toolchain is that it's hard for some people to get it working. In theory, we have documentation that explains this: http://www.postgresql.org/docs/current/static/docguide-toolsets.html However, in contrast to the vast majority of our documentation, it stinks. Umm, if you look under Debian Packages, there is a one-line command to execute, which, as far as I can tell, is pretty much guaranteed to get you going. If that doesn't apply to the OS you are working, then either a) You haven't contributed better installation documentation, or b) The makers of your OS haven't bothered to package it properly. No other toolchain will make that principle easier. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On mån, 2011-04-04 at 15:08 -0400, Tom Lane wrote: One thing I'd like to know is whether docbook v5 is any more portable/easier to install. I don't see why. It's just a newer version of the same thing. If you change the sources to XML and switch to the XSL toolchain, you don't have to install the DTD or other schema as such, which would simplify the installation. But that could already be had with DocBook 4. The drawback of not having the schema is that you can't verify the correct structure of the document, and the XSLT processor will just produce garbage. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
Excerpts from Gabriele Bartolini's message of lun abr 04 16:47:26 -0400 2011: Il 04/04/11 22:26, Robert Haas ha scritto: I think you still need to update Solution.pm to match. Here it is, including change of 3 'Id' attributes (I made them lowercase). Pushed this one also. -- Álvaro Herrera alvhe...@commandprompt.com The PostgreSQL Company - Command Prompt, Inc. PostgreSQL Replication, Consulting, Custom Development, 24x7 support -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Support comments on FOREIGN DATA WRAPPER and SERVER objects.
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 6:03 AM, Shigeru HANADA han...@metrosystems.co.jp wrote: * The comment_user_mapping_core.patch includes syntax support, catalog manipulation, pg_dump support, documents and regression tests. I don't think it's going to fly to add a function pg_usermapping_ownercheck() with a randomly different API than all the parallel functions for other object types. There is probably some more refactoring that needs to be done here to make this sane, but I'm coming around to the view that trying to slip this into 9.1 is not the best thing for us to be spending time on, especially considering that it doesn't seem to be straightforward to figure out how it should actually work. I am inclined to punt this to 9.2. I agree --- this can clearly contains more worms than we expected. Supporting user mappings in COMMENT, EXTENSION, etc is not so critical that we should push a possibly misdesigned notion of ownership into the system for it. Better to take our time and think about that. (BTW, it might be useful to reconsider casts while we are thinking about this. Those don't have a proper notion of ownership either. I'm a bit inclined to think that we should just bite the bullet and add owner columns to both these catalogs. But, again, let's not be hasty.) regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On mån, 2011-04-04 at 19:26 +0200, Susanne Ebrecht wrote: Honestly, for German I don't mind yet if it is XML or SGML. XML might be better in future for maintenance tools. Anyway, I figured out there is another argument for XML: My information is that DocBook 5.0 won't support SGML anymore. Which means - sooner or later a reaction is needed. In the spirit of CVS_to_Git, I have started this page now: http://wiki.postgresql.org/wiki/Switching_PostgreSQL_documentation_from_SGML_to_XML Edit away. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: synchronous_commit and synchronous_replication Re: [COMMITTERS] pgsql: Efficient transaction-controlled synchronous replication.
Robert Haas robertmh...@gmail.com writes: On Tue, Apr 5, 2011 at 11:25 AM, Robert Haas robertmh...@gmail.com wrote: I am also wondering about the open issue of supporting comments to SQL/MED objects. I thought that was pretty straightforward, but given that it took me three commits to get servers and foreign data wrappers squared away and then it turned out that we're still missing support for user mappings, I've been vividly reminded of the danger of seemingly harmless commits. Now I'm thinking that I should have just replied to the initial report with good point, but it's not a new regression, so we'll fix it in 9.2. But given that part of the work has already been done, I'm not sure whether I should (a) finish it, so we don't have to revisit this in 9.2, (b) leave it well enough alone, and we'll finish it in 9.2, or (c) back out what's already been done and plan to fix the whole thing in 9.2. On further review, I think (a) is not even an option worth discussing. The permissions-checking logic for user mappings is quite different from what we do in the general case, and it seems likely to me that cleaning this up is going to require far more time and thought than we ought to be putting into what is really a relatively minor wart. In retrospect, it seems clear that this wasn't worth messing with in the first place at this late date in the release cycle. I agree that we should leave user mappings alone at the moment. I don't see a need to back out the work that's been done for the other object types, unless you think there may be flaws in that. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On Tue, Apr 5, 2011 at 2:18 PM, Peter Eisentraut pete...@gmx.net wrote: On mån, 2011-04-04 at 15:02 -0400, Robert Haas wrote: AFAICT, the biggest problem with our existing toolchain is that it's hard for some people to get it working. In theory, we have documentation that explains this: http://www.postgresql.org/docs/current/static/docguide-toolsets.html However, in contrast to the vast majority of our documentation, it stinks. Umm, if you look under Debian Packages, there is a one-line command to execute, which, as far as I can tell, is pretty much guaranteed to get you going. If that doesn't apply to the OS you are working, then either a) You haven't contributed better installation documentation, or b) The makers of your OS haven't bothered to package it properly. No other toolchain will make that principle easier. I don't know whether some other toolchain would be easier or not. I believe that the directions for RPM installation aren't completely up-to-date; I think you need docbook-style-dsssl at least on newer Fedoras, and there isn't any stylesheet package on such systems. There are also no directions for MacOS X at all. My biggest gripe is that when things fail, it's often not obvious what the problem is. I've had failures due to missing packages (but the package that's missing is far from obvious) and I've also had failures, I believe, from not being connected to the Internet, which is surprising because it's not at all obvious that building the docs should require an Internet connection. At least if you are missing something like zlib it says checking for zlib... not found. And while it may be that you have zlib and are missing zlib-devel, it at least gets you pointed in the right direction, whereas the docbook stuff tends to spew out 50 pages of error messages that I at least don't find terribly intuitive. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On Tue, Apr 5, 2011 at 7:55 PM, Robert Haas robertmh...@gmail.com wrote: I've also had failures, I believe, from not being connected to the Internet, which is surprising because it's not at all obvious that building the docs should require an Internet connection. Oh, I've run into that and had it cause delays when I've been building release installers before now (sourceforge were having issues iirc) - not good. -- Dave Page Blog: http://pgsnake.blogspot.com Twitter: @pgsnake EnterpriseDB UK: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Visibillity testing - some numbers on current performance.
Hi. I initially set out to put some numbers on why the visibillity map was important for select count(*), primarily to give some feedback to Simon Riggs stating: Your tests and discussion remind me that I haven't yet seen any tests that show that index-only scans would be useful for performance. I created at small testscript that (on my lousy desktop) created a bunch of different tables, all with 2.000.000 rows and different width. The tests are created so that the tuple-with of around 1500bytes ends around the size of memory of my system, so we eventually have some number for when we're going to disk. My desktop is only a single SATA 7200 rpm drive, 3GB of memory and no battery backed write cache (who would ever run a REAL database on such a system anyway). (script and output-data, graphs in links at the end of the email). The on this system, visibillity testing take from: 0.2s to 0.5s in total for 2.000.000 tuples dependent on tuple-size (header+14 bytes to header + 504 bytes), this is all in situations where we can assume that hit-bits are set and all thing have kicked in and the complete dataset is memory cached. This made me conclude that, this is just awesome, if I could get somewhere near these numbers in my production system then I would have been haunting perfomance in totally different areas. One the first select after load, still in situations where we're fully memory recident the range is instead: 0.6s to 35s in total for 2.000.000 tuples (First run - second run for +10 bytes and +500 bytes respectively). This degradation can mostly be attributed to concurrent writeout of hit-bits on a single SATA-drive (one-spindle), who would sanely run a db on such a system anyway, this number is probably the least robust one in the test, but the huge range should lead to some concern anyway. The last range is the range where we're hitting disk, and that is fairly uninteresting as is can more or less be seen as a speed of the underlying disk-system, where the one in this one is in the low range. But is seen from a sequential throughput perspective which this one tests is still does about 80MB/s and an expensive one will not buy an order of magnitude in this area. The range is in this case: 1.5s to 42s in total for 2.000.000 tuples. the first one for a +10 bytes tuple, the last one for a +1500 bytes. Of the really non-interesting information is that when I add +2500 bytes to the tuple it goes down to 2.1s, which is due to toast kicking in and the fact that the data I load are highly compressable so it ends up filling next to nothing. Conclusion: Visibillity testing of 2.000.000 tuples takes between 0.2s and 42s, where your system fits into that range hugely depends on your tuple-size, the amount of bloat on your tables, the amount of memory for caching compared to the total database size and if you have sufficient activity on your system for keeping your active dataset in memory in favor of background activities. If your active dataset approaches cache-size of the system, you're very likely to hit in the last part of that range. So Simon Riggs question is really good, since the answer would be it depends. It seems plausible that it is really hard to beat the 0.2s-0.5s for 2m tuples we currently meet for any kind of memory resident dataset. The approach currently being pursues, splitting of the PD_ALL_VISIBLE bit and using that for visibillity testing, would improve the situation enourmously making all the diskbound cases to be in the order of primary-key-index-size+vm-map-size/disk-throughput instead of main-table-size/disk-throughput Which for slim tables wouldn't be that much, but for fat tables it can/would be substantial. But it would be crusial to have the bit set at all, and the likelihood would fall with the amount of churn in the table. The worst-case situation would be where the bit is not set anyway and there the speed would be a primary index worse than currently and if the best-case would be better at all. But I'm fairly sure that the average case would be quite a lot lower than it is today, just by the likelyhood of indexes and vm-maps being in-memory . (at least for databases hitting disk occationally). Getting below 0.2s for 2.000.000 tuples would somewhat be nice but gettting the worst-case numbers an order of magnitue down would be an enourmous benefit to large or infrequently used databases. My conclusion is somewhere along the line of: Gettting the visibillity map crash-safe and updated is not the primary goal, but getting the visibillity testing separated from the varying size of tuples seems to be the key point here and doing it by moving the PD_ALL_VISIBLE bit out is definately one way of doing it. Another approach could be to way more aggressively push to TOAST, this would effectively push the worst-case behaviours down. (I'll try to do some sane benchmarking around that). A third approach could be to do a slim table, with only the relevant bits from the
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. What standardization risk? The standard has already existed for 10 years and is widely implemented. What is the standard, and who is it that has implemented it that way? -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Visibillity testing - some numbers on current performance.
On Tue, Apr 5, 2011 at 3:04 PM, Jesper Krogh jes...@krogh.cc wrote: I initially set out to put some numbers on why the visibillity map was important for select count(*), primarily to give some feedback to Simon Riggs stating: Your tests and discussion remind me that I haven't yet seen any tests that show that index-only scans would be useful for performance. I'm not sure what this has to do with index-only scans. At least as I understand it, the concern about the way we do it now is primarily that scanning the index will lead to random I/O on the underlying table, which you aren't going to trigger with count(*). I agree that the question about how much this benefits performance is a worthwhile one. If your database is RAM cached I suspect it makes very little difference. You might save something on MVCC visibility checks and shared_buffers churn, but it probably won't be a lot. Where I would expect to see a benefit is if the database is much larger than available memory, and especially if the index fits but the index+table doesn't. Now reading rows randomly from the index based on a stream of many queries for the form SELECT a, b FROM foo WHERE a = some constant ought to be much faster if you can look at the index in memory and be done, and much slower if you have to read a heap block from disk every time. Now how we measure this without having built it is an interesting question. There is probably some way of getting useful numbers out, but I'm not sure I know what it is. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
Merlin Moncure wrote: On Fri, Mar 25, 2011 at 10:05 PM, David E. Wheeler da...@kineticode.com wrote: On Mar 25, 2011, at 9:12 PM, Robert Haas wrote: As I've said before, I believe that the root cause of this problem is that using the same syntax for variables and column names is a bad idea in the first place. If we used $foo or ?foo or ${foo} or $.foo or foo!!$#? to mean the parameter called foo, then this would all be a non-issue. Yes *please*. Man that would make maintenance of such functions easier. +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. The foo syntax should be orthogonal to everything else and not have anything specifically to do with parameters. Rather, foo anywhere is just a delimited case-sensitive identifier and can be used anywhere that foo can where the latter is a case-insensitive identifier. As for the SQL standard for bind parameters, as I recall they use :foo and so :foo would be the sensitive more general case of that. -- Darren Duncan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. What standardization risk? The standard has already existed for 10 years and is widely implemented. What is the standard, and who is it that has implemented it that way? As mentioned earlier, see under clause on identifier chain. The summary is that in CREATE FUNCTION foo(a int) you can refer to the parameter as either of a foo.a with some scoping rules to resolve ambiguities with column references. (These are essentially the same scoping rules that tell you what a refers to when you have multiple tables with an a column in a query.) As far as I can tell, the syntax is implemented, more or less, at least in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they do with the scoping rules, of course. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. What standardization risk? The standard has already existed for 10 years and is widely implemented. What is the standard, and who is it that has implemented it that way? As mentioned earlier, see under clause on identifier chain. The summary is that in CREATE FUNCTION foo(a int) you can refer to the parameter as either of a foo.a with some scoping rules to resolve ambiguities with column references. (These are essentially the same scoping rules that tell you what a refers to when you have multiple tables with an a column in a query.) As far as I can tell, the syntax is implemented, more or less, at least in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they do with the scoping rules, of course. Talking about the standards compliance of functions is a bit silly: our implementation of functions isn't even close to approximating what looks to be the standard (according to this at least: http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html) and there is no point pretending that it is. In practice, database functions and procedures are 100% vendor incompatible with each other, and with the standard. I was just talking about $ getting reserved for some special meaning in the future. mysql supports psm, which we don't. oracle supports pl/sql, which is similar to pl/pgsql, but means nothing in terms of postgresql sql language argument disambiguation afaict. It's our language and we should be able to extend it. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/4/5 Peter Eisentraut pete...@gmx.net: On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. What standardization risk? The standard has already existed for 10 years and is widely implemented. What is the standard, and who is it that has implemented it that way? As mentioned earlier, see under clause on identifier chain. The summary is that in CREATE FUNCTION foo(a int) you can refer to the parameter as either of a foo.a with some scoping rules to resolve ambiguities with column references. (These are essentially the same scoping rules that tell you what a refers to when you have multiple tables with an a column in a query.) This is a good design. If we disallow a ambiguities, there isn't a space for bugs. And if anybody needs to accent any parameter, then there are still $n notation. There is lot of notation and I don't think so it is necessary to add new one MySQL, MSSQL uses @, DB2, ANSI SQL no prefix, Oracle and Firebird uses :, but in different context. simply - chaos. There was request for some alias on function name. It could be. PL/pgSQL knows a #option, so there can be some similar in SQL. CREATE OR REPLACE FUNCTION longnamefunc(param integer) RETURNS ... AS $$ #alias longnamefunc ln SELECT ln.param; $$ Regards Pavel Stehule As far as I can tell, the syntax is implemented, more or less, at least in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they do with the scoping rules, of course. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
2011/4/5 Merlin Moncure mmonc...@gmail.com: On Tue, Apr 5, 2011 at 2:33 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 15:05 -0400, Robert Haas wrote: On Tue, Apr 5, 2011 at 1:45 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 11:21 -0500, Merlin Moncure wrote: +1 on using $foo. Even with the standardization risk I think it's the best choice. Prefer $foo to ${foo} though. What standardization risk? The standard has already existed for 10 years and is widely implemented. What is the standard, and who is it that has implemented it that way? As mentioned earlier, see under clause on identifier chain. The summary is that in CREATE FUNCTION foo(a int) you can refer to the parameter as either of a foo.a with some scoping rules to resolve ambiguities with column references. (These are essentially the same scoping rules that tell you what a refers to when you have multiple tables with an a column in a query.) As far as I can tell, the syntax is implemented, more or less, at least in Oracle, DB2, MySQL, Firebird, and HSQL. I haven't checked what they do with the scoping rules, of course. Talking about the standards compliance of functions is a bit silly: our implementation of functions isn't even close to approximating what looks to be the standard (according to this at least: http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html) and there is no point pretending that it is. In practice, database functions and procedures are 100% vendor incompatible with each other, and with the standard. I was just talking about $ getting reserved for some special meaning in the future. mysql supports psm, which we don't. A PSM support for PostgreSQL is almost done. I expect a production quality for 9.2. MySQL support own language based on PSM with lot of inspiration in T-SQL. In MySQL - local variables are clasic, only session variables has a prefix @. Regards Pavel oracle supports pl/sql, which is similar to pl/pgsql, but means nothing in terms of postgresql sql language argument disambiguation afaict. It's our language and we should be able to extend it. merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Please review test report form
All, For 9.1, I'm trying to get beta testing a *bit* more organized in hopes of shortening the beta period. Since we're not up and running on Django on the main website yet, and thus I can't make an app for collecting test reports, I've created a Google form: http://tinyurl.com/3gp94er Please provide some feedback on what we should be collecting differently, if anything. The idea is that results from this test form will be displayed in detail and summary form so that hackers can refer to the test results. Among other things, we particularly want to collect *positive* test results as well as bugs so that we know how we're doing. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions Dependency Checking
On Apr 4, 2011, at 3:57 PM, Tom Lane wrote: I think the general movement is toward *feature* dependancies. So for intstance, an extension can specify what *feature* it requires, and difference versions of an extension can provide different features. Right. Sounds like a book-keeping nightmare for extension developers. It will discourage large or rapidly-evolving extensions like pgTAP because it will be a PITA to specify features. But checking http://developer.postgresql.org/pgdocs/postgres/extend-extensions.html, I don't see any provides mechanism. Yes, some sort of manual Provides: (in addition to automatically extracted Provides:) would likely be part of any serious solution. shed type=bikeI'd like to request Features: instead of Provides:./shed We're not there yet, and we're not going to get there in time for 9.1. But in any case, mechanisms that involve version ordering comparisons seem to be on their way out for deciding whether package A is compatible with package B. This is news to me, frankly, and the bookkeeping requirements seem potentially awful. If it's possible that it won't work out this way, that those arguing for version dependency resolution end up getting the consensus, not having a version string format is going to be a nightmare. On the other hand, if we added one now, and feature dependency tracking won the day, well, a version string format could always be loosened later. Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions Dependency Checking
On Tue, Apr 5, 2011 at 4:20 PM, David E. Wheeler da...@kineticode.com wrote: On Apr 4, 2011, at 3:57 PM, Tom Lane wrote: I think the general movement is toward *feature* dependancies. So for intstance, an extension can specify what *feature* it requires, and difference versions of an extension can provide different features. Right. Sounds like a book-keeping nightmare for extension developers. It will discourage large or rapidly-evolving extensions like pgTAP because it will be a PITA to specify features. Sure, but if you want, the feature you can provide can be something like: pgtap-1.0 (or any of pgtap-0.2{0,1,2,3,4}). And if your package is backwards compatable, it could even provide: pgtap-0.25 pgtap-0.24 pgtap-0.23 And that also means that you don't have to screw every body over when some future pgtap-123.45 is no longer compatible, and the extensions have relied on $VERSION 0.23 meaning they'll work with it. I mean, PG itself is an example. Does pg 8.4 mean your code will work with all future (or even past, but 8.4) PG versions? We're not there yet, and we're not going to get there in time for 9.1. But in any case, mechanisms that involve version ordering comparisons seem to be on their way out for deciding whether package A is compatible with package B. This is news to me, frankly, and the bookkeeping requirements seem potentially awful. If it's possible that it won't work out this way, that those arguing for version dependency resolution end up getting the consensus, not having a version string format is going to be a nightmare. On the other hand, if we added one now, and feature dependency tracking won the day, well, a version string format could always be loosened later. As someone who has had to try and deal with package versions for dependencies in RPM and DEB, and been through the hell that is open source package variants, all with the ability to turn on/off features at configure/compile time, a just versions even with , =, =, =, all mapped correctly isn't good enough. Of course, I'ld love for extension in 9.1 to provide a basic provides/features for my extension to give, but if that train has already left the station, I don't have much choice ;-( a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote: Talking about the standards compliance of functions is a bit silly: our implementation of functions isn't even close to approximating what looks to be the standard That doesn't mean it couldn't be better in the future. We shouldn't take it further away, in any case. As long as we use LANGUAGE SQL, we are both technically and morally in standards-space. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions Dependency Checking
On Apr 5, 2011, at 1:42 PM, Aidan Van Dyk wrote: Sure, but if you want, the feature you can provide can be something like: pgtap-1.0 (or any of pgtap-0.2{0,1,2,3,4}). And if your package is backwards compatable, it could even provide: pgtap-0.25 pgtap-0.24 pgtap-0.23 I see, I get it. And that also means that you don't have to screw every body over when some future pgtap-123.45 is no longer compatible, and the extensions have relied on $VERSION 0.23 meaning they'll work with it. I see. I mean, PG itself is an example. Does pg 8.4 mean your code will work with all future (or even past, but 8.4) PG versions? I see. So the extension author can more easily tell users when compatibility has been dropped for something. That makes sense. As someone who has had to try and deal with package versions for dependencies in RPM and DEB, and been through the hell that is open source package variants, all with the ability to turn on/off features at configure/compile time, a just versions even with , =, =, =, all mapped correctly isn't good enough. Yeah. The use of an implicit = in CPAN modules has been a decent 90% solution, but it does cause headaches for people that they can't express things better. The ability to do so would require a mini-language with more operators, precedence, grouping, etc. Of course, I'ld love for extension in 9.1 to provide a basic provides/features for my extension to give, but if that train has already left the station, I don't have much choice ;-( Yeah, but the way it is doesn't break the ability to do it later. I suspect that Dim and Tom will be thinking about it for 9.2. Anyway, your post helps me to understand things better, and so I'm less insistent about imposing a version numbering scheme now (though I still think it would be more useful to have one than not). Best, David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Extensions Dependency Checking
On Tue, Apr 5, 2011 at 4:51 PM, David E. Wheeler da...@kineticode.com wrote: Of course, I'ld love for extension in 9.1 to provide a basic provides/features for my extension to give, but if that train has already left the station, I don't have much choice ;-( Yeah, but the way it is doesn't break the ability to do it later. I suspect that Dim and Tom will be thinking about it for 9.2. Anyway, your post helps me to understand things better, and so I'm less insistent about imposing a version numbering scheme now (though I still think it would be more useful to have one than not). Versions are useful for figuring out if I should upgrade packages or not. But I believe the extension framework has explicitly made the upgrade problem a manual one at this point, either taking destination versions from the control, or the alter command. So for PGXN's problem, I see the point of versions being required. But for installation the dependancy graph, provides/features rather than versions are much more useful.And automatic feature/provides (like library so, and symbol versions in the OS package world, objects in PG world) would definitely be nice, but my Makefile can build those for me for now until 9.2 (or 9.3, 9.3, etc), if only I had a way to track them with my installed extension ;-) /stop begging a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On tis, 2011-04-05 at 14:55 -0400, Robert Haas wrote: and I've also had failures, I believe, from not being connected to the Internet, which is surprising because it's not at all obvious that building the docs should require an Internet connection. I understand this problem, but just to clarify, this is supposed to help, because then you don't need to install anything, as whatever is necessary will be downloaded automatically. This is a feature of the XSLT processor. In the case of xsltproc, it can be turned off with the --nonet option. On the other hand, if you have a proper local installation, then it should map the Internet URIs to the local installation. -- should -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: [COMMITTERS] pgsql: Support comments on FOREIGN DATA WRAPPER and SERVER objects.
On tis, 2011-04-05 at 14:47 -0400, Tom Lane wrote: Supporting user mappings in COMMENT, EXTENSION, etc is not so critical that we should push a possibly misdesigned notion of ownership into the system for it. Better to take our time and think about that. (BTW, it might be useful to reconsider casts while we are thinking about this. Those don't have a proper notion of ownership either. I'm a bit inclined to think that we should just bite the bullet and add owner columns to both these catalogs. But, again, let's not be hasty.) As I said elsewhere, I think of user mappings as similar to role grants. An owner there would be similar to a grantor, so it would make sense. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC proposal: Fast GiST index build
On Mon, Apr 4, 2011 at 8:50 PM, Robert Haas robertmh...@gmail.com wrote: OK. Could you briefly describe the algorithm you propose to implement, bearing in mind that I haven't read the paper? The technique can be very briefly described in following rules. M = number of index keys fitting in RAM; B = number of index keys in one page; 1) Additional buffers of M/(2*B) pages each is attached to all nodes of some levels. Levels are selected with step floor(log(M/4B, B))), leaf nodes don't contain buffers. I.e. nodes in levels i*floor(log(M/4B, B))), i = 1,2,3,... contain buffers (numbering is going from down to up, level 0 contain leaf nodes). 2) When entry reaches node with buffer, it is placed into buffer. 3) When buffer is overflowed it runs down into lower buffers or leaf pages. 4) When split occurs in node with buffer, then this buffers splits into two buffers using penalty function. With best regards, Alexander Korotkov.
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 3:47 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 14:45 -0500, Merlin Moncure wrote: Talking about the standards compliance of functions is a bit silly: our implementation of functions isn't even close to approximating what looks to be the standard That doesn't mean it couldn't be better in the future. We shouldn't take it further away, in any case. As long as we use LANGUAGE SQL, we are both technically and morally in standards-space. sql standard functions are psm routines aiui. Are you making the case that 'language sql' in postgresql could or should in fact be psm at some point in the future? I say that's not the case -- our 'language sql' is not psm. That said, if you well and truly stated that it was project objective to allow psm constructions in 'language sql', and you could figure out a way to do that without breaking current sql code, I would have to say i'm coming around to your point of view. Either way, our $N notation is already non-standard and highly in use - what's the big deal about making it more useful? merlin -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On 04/05/2011 03:45 PM, Merlin Moncure wrote: Talking about the standards compliance of functions is a bit silly: our implementation of functions isn't even close to approximating what looks to be the standard (according to this at least: http://farrago.sourceforge.net/design/UserDefinedTypesAndRoutines.html) and there is no point pretending that it is. In practice, database functions and procedures are 100% vendor incompatible with each other, and with the standard. I was just talking about $ getting reserved for some special meaning in the future. mysql supports psm, which we don't. oracle supports pl/sql, which is similar to pl/pgsql, but means nothing in terms of postgresql sql language argument disambiguation afaict. It's our language and we should be able to extend it. That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] .ini support for .pgpass
The current structure of .pgpass is: hostname:port:database:username:password Bare, useful, but not really friendly nor flexible. I would love to be able to do this: If no ini block: hostname:port:database:username:password else: [ecom] hostname= port= database= username= password= [drupal] hostname= port= database= username= password= psql ecom boom, I am in. Thoughts? JD -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP: Allow SQL-language functions to reference parameters by parameter name
On Tue, Apr 5, 2011 at 5:52 PM, Andrew Dunstan and...@dunslane.net wrote: That doesn't mean we should arbitrarily break compatibility with pl/sql, nor that we should feel free to add on warts such as $varname that are completely at odds with the style of the rest of the language. That doesn't do anything except produce a mess. Well, what it does is avoid breaking compatibility with previous versions of PostgreSQL. I think that actually does have some value. Otherwise, we'd be folding to upper-case by default. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On 4/5/11 3:34 PM, Joshua D. Drake wrote: Bare, useful, but not really friendly nor flexible. I would love to be able to do this: I'll second that I help people troubleshoot a lot of .pgpass files where the basic issue is getting the fields out of order. -- Josh Berkus PostgreSQL Experts Inc. http://pgexperts.com -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On Apr 5, 2011, at 3:34 PM, Joshua D. Drake wrote: boom, I am in. Thoughts? boom, you have patch? David -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On Tue, Apr 5, 2011 at 4:59 PM, Peter Eisentraut pete...@gmx.net wrote: On tis, 2011-04-05 at 14:55 -0400, Robert Haas wrote: and I've also had failures, I believe, from not being connected to the Internet, which is surprising because it's not at all obvious that building the docs should require an Internet connection. I understand this problem, but just to clarify, this is supposed to help, because then you don't need to install anything, as whatever is necessary will be downloaded automatically. This is a feature of the XSLT processor. In the case of xsltproc, it can be turned off with the --nonet option. On the other hand, if you have a proper local installation, then it should map the Internet URIs to the local installation. -- should Well, that explains why it worked without a network connection on some systems but not others. I don't really object to the toolchain we're using; it works OK for me, and switching would be a pain in the neck. But I find it's not as easy to use as some things. -- Robert Haas EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On Tue, 2011-04-05 at 15:38 -0700, David E. Wheeler wrote: On Apr 5, 2011, at 3:34 PM, Joshua D. Drake wrote: boom, I am in. Thoughts? boom, you have patch? I'll write it, if I am not going to be tied up for months arguing about it :P. Thus, I wanted to see if the community was interested first. Sincerely, Joshua D. Drake David -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On Tue, Apr 5, 2011 at 6:34 PM, Joshua D. Drake j...@commandprompt.com wrote: Bare, useful, but not really friendly nor flexible. I would love to be able to do this: [ecom] hostname= port= database= username= password= That looks a lot like a pg_service file. psql ecom boom, I am in. Thoughts? So you're really looking to make psql use service connection definitions more easily, not just retrieve the password associated with the given (maybe defaulted) host:port:database:user, right? a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On Tue, 2011-04-05 at 18:52 -0400, Aidan Van Dyk wrote: On Tue, Apr 5, 2011 at 6:34 PM, Joshua D. Drake j...@commandprompt.com wrote: Bare, useful, but not really friendly nor flexible. I would love to be able to do this: [ecom] hostname= port= database= username= password= That looks a lot like a pg_service file. psql ecom boom, I am in. Thoughts? So you're really looking to make psql use service connection definitions more easily, not just retrieve the password associated with the given (maybe defaulted) host:port:database:user, right? Well any libpq app but yes. I actually wonder as to the legitmacy of having both a pgpass and a pg_service. Why not just one of them? JD a. -- Aidan Van Dyk Create like a god, ai...@highrise.ca command like a king, http://www.highrise.ca/ work like a slave. -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] GSoC Proposal - Caching query results in pgpool-II
Hello My name is Masanori Yamazaki. I am sending my proposal about Google Summer Of Code2011. It would be nice if you could give me your opinion. ・title Caching query results in pgpool-II ・Synopsis Pgpool-II has query caching functionality using storage provided by dedicated PostgreSQL (system database). This has several drawbacks however. 1)it is slow because it needs to access disk storage 2)it does not invalidate the cache automatically. This proposal tries to solve these problems. - To speed up the cache access, it will be placed on memory, rather than database. The memory will be put on shared memory or external memory services such as memcached so that the cache can be shared by multiple sessions. Old cache entries will be deleted by LRU manner. - The cache will be invalidated automatically upon the timing when the relevant tables are updated. Note that this is not always possible because the query result might come from multiple tables, views or even functions. In this case the cache will be invalidated by timeout(or they are not cached at all). - Fine tuning knobs need to be invented to control the cache behavior though they are not clear at this moment. ・Benefits to the PostgreSQL Community: Query caching will effectively enhance the performance of PostgreSQL and this project will contribute to increase the number of users of PostgreSQL, who need more high performance database systems. Note that implementing query cache in pgpool-II will bring merits not only to the latest version of PostgreSQL but to the previous releases of PostgreSQL. ・Project Schedule -April preparation -May 1 - May 22 write a specification -May 23 - June 19 coding -June 20 - July 22 test -July 23 - August 12 complete of coding and test, commit ・Personal Data and Biographical Information Name : Masanori Yamazaki Born : 23.1.1981 School :Currently I learn contemporary philosophy, culture and literature at Waseda University in Japan. Coding : 1.About five years job as web application programer(PHP, Java). 2.I experienced projects used framework such as Symfony, Zend Framework, CakePHP, and Struts. 3.I am interested in OSS and like coding. Regards
[HACKERS] Transaction log
Hi All, I was wondering if anyone can tell me how i can access the transaction log within postgresql 9.0.3. I have carried out some updated and deletions within the database and am hoping the transaction logs have records of this. Cheers all -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4285471.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [DOCS] [HACKERS] Uppercase SGML entity declarations
On Mon, Apr 4, 2011 at 3:02 PM, Robert Haas robertmh...@gmail.com wrote: In theory, we have documentation that explains this: http://www.postgresql.org/docs/current/static/docguide-toolsets.html While we're on the subject.. Attached is a patch against that page suggesting using openjade 1.3, not 1.4devel as part of the doc build toolset. Source of this recommendation: http://old.nabble.com/openjade-segfault-on-Postgres-PDF%3A-flow-object-not-accepted-by-port-to30764268.html I just double checked, and with the latest openjade package (1.4devel) on Ubuntu 10.10, I still see the same segfault; downgrading to package openjade1.3 allows me to make postgres-A4.pdf successfully. Josh diff --git a/doc/src/sgml/docguide.sgml b/doc/src/sgml/docguide.sgml index 7ec75a3..7bbe324 100644 *** a/doc/src/sgml/docguide.sgml --- b/doc/src/sgml/docguide.sgml *** CATALOG docbook/4.2/catalog *** 268,274 available for productnameDebian GNU/Linux/productname. To install, simply use: programlisting ! apt-get install docbook docbook-dsssl docbook-xsl openjade xsltproc /programlisting /para /sect2 --- 268,274 available for productnameDebian GNU/Linux/productname. To install, simply use: programlisting ! apt-get install docbook docbook-dsssl docbook-xsl openjade1.3 xsltproc /programlisting /para /sect2 *** make install *** 309,314 --- 309,327 installed and you want to install the rest of the toolchain locally.) /para + +note +para + Some users have reported encountering a segmentation fault using + openjade 1.4devel to build the PDFs, with a message like: + screen + openjade:./stylesheet.dsl:664:2:E: flow object not accepted by port; only display flow objects accepted + make: *** [postgres-A4.tex-pdf] Segmentation fault + /screen + Downgrading to openjade 1.3 should get rid of this error. +/para +/note + /step step id=doc-openjade-install -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] pg_upgrade bug found!
OK, thanks to RhodiumToad on IRC, I was able to determine the cause of the two reported pg_upgrade problems he saw via IRC. It seems toast tables have xids and pg_dump is not preserving the toast relfrozenxids as it should. Heap tables have preserved relfrozenxids, but if you update a heap row but don't change the toast value, and the old heap row is later removed, the toast table can have an older relfrozenxids than the heap table. The fix for this is to have pg_dump preserve toast relfrozenxids, which can be easily added and backpatched. We might want to push a 9.0.4 for this. Second, we need to find a way for people to detect and fix existing systems that have this problem, perhaps looming when the pg_class relfrozenxid passes the toast relfrozenxid, and thirdly, we need to figure out how to get this information to users. Perhaps the communication comes through the 9.0.4 release announcement. Yes, this is not good! :-( I will still add a special flag to postgres to turn off autovacuum, but as we suspected, this is only a marginal improvement and not the cause of the 9.0.X failures. The good news is that only two people have seen this problem and it only happens when the hint bits have not been set on the toast rows and the oldest heap rows have been updated. -- Bruce Momjian br...@momjian.ushttp://momjian.us EnterpriseDB http://enterprisedb.com + It's impossible for everything to be true. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Transaction log
On Tue, 2011-04-05 at 18:25 -0700, aaronenabs wrote: Hi All, I was wondering if anyone can tell me how i can access the transaction log within postgresql 9.0.3. I have carried out some updated and deletions within the database and am hoping the transaction logs have records of this. You can't, easily. Do you mean to say your updates and deletions were unintended? JD Cheers all -- View this message in context: http://postgresql.1045698.n5.nabble.com/Transaction-log-tp4285471p4285471.html Sent from the PostgreSQL - hackers mailing list archive at Nabble.com. -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579 Consulting, Training, Support, Custom Development, Engineering http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] .ini support for .pgpass
On tis, 2011-04-05 at 16:04 -0700, Joshua D. Drake wrote: Well any libpq app but yes. I actually wonder as to the legitmacy of having both a pgpass and a pg_service. Why not just one of them? So you can keep passwords in a safer place (= less permissions) than the rest of the connection information. Note also that .pgpass is a mapping from connection information to password, whereas pg_service.conf is a mapping from service name to connection information. So they operate on different levels. It's not actually clear from your syntax example what semantics you are trying to achieve. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] GSoC proposal: Fast GiST index build
Just to clarify situation a bit. I noticed buffer tree technique while reseaching sp-gist and got an idea to use it for improving CREATE INDEX for GiST, which is what we were looking many times. Alexander is working on his thesis and this project suits ideally for him and community. Since I and Teodor are very busy in the moment, it's very important to have one more gist developer available, especially, keeping in mind the energy and motivation of Alexander. He already did several contributions and I have no doubt his work will be useful for us. So, I suggest support his work ! Oleg On Wed, 6 Apr 2011, Alexander Korotkov wrote: On Mon, Apr 4, 2011 at 8:50 PM, Robert Haas robertmh...@gmail.com wrote: OK. Could you briefly describe the algorithm you propose to implement, bearing in mind that I haven't read the paper? The technique can be very briefly described in following rules. M = number of index keys fitting in RAM; B = number of index keys in one page; 1) Additional buffers of M/(2*B) pages each is attached to all nodes of some levels. Levels are selected with step floor(log(M/4B, B))), leaf nodes don't contain buffers. I.e. nodes in levels i*floor(log(M/4B, B))), i = 1,2,3,... contain buffers (numbering is going from down to up, level 0 contain leaf nodes). 2) When entry reaches node with buffer, it is placed into buffer. 3) When buffer is overflowed it runs down into lower buffers or leaf pages. 4) When split occurs in node with buffer, then this buffers splits into two buffers using penalty function. With best regards, Alexander Korotkov. Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: o...@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(495)939-16-83, +007(495)939-23-83 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reading from a REFCURSOR in a C language function
06.04.2011 02:06, Jan Wieck wrote: On 4/5/2011 3:24 AM, Vlad Arkhipov wrote: Hi, I'm trying to write a C language function that has a REFCURSOR argument. Could anyone please give me an example of reading from a cursor in C code? Sorry, I don't have a code example. A refcursor data type is basically a string, containing the name of an open cursor (portal). It is stored binary compatible to the text data type. In the C function, you extract that name (using the textout function) and use it inside the FETCH query as the cursor name. You may need to double-quote that string. Jan Thanks for the hint. It works great. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers