Re: Translations at pgfoundry (was Re: [HACKERS] [PATCHES] Latest Turkish translation updates)
On Thu, 20 Jan 2005 14:08:20 +0100, Peter Eisentraut [EMAIL PROTECTED] wrote: Peter Eisentraut wrote: Maybe we should have a pgfoundry project where all translations were kept, and from which the main CVS could be updated semi-automatically. Then we wouldn't have Peter checking out and committing all the time. That sounds like a fine idea. My only concern would be the not-maintained-here syndrome, which occurs every time some CVS tree contains a file that is actually maintained by an external group, thus blocking the maintainers of the former CVS tree from applying necessary fixes at times. Nevertheless, I think this is a winner. Let's consider it when we start the 8.1 cycle. OK, is anyone opposed to this idea? I would register a pgfoundry project (name suggestions? translations?), give most established translators commit access, and move the statistics pages there. Also, some translation groups seem to have their own mailing lists or web pages, which could optionally also be hosted there. We could then sync the translations either regularly (e.g., once a week) or only at release time. Of course we would need to mirror all the branches there. Comments? Perfectly fine. Please go ahead. -- Peter Eisentraut Nicolai Tufar Turkish Language Translation Team. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] can plpgsql returns more flexibe value ?
Hello, pgsql-hackers
I create a table, a type and a function like below:
CREATE TABLE User
(
Id int4 NOT NULL DEFAULT nextval('public.User_Id_seq'::text),
Name varchar(32) NOT NULL
);
CREATE TYPE UserSet AS
(
Id int4,
Name varchar(32)
);
CREATE OR REPLACE FUNCTION UserSelectById(@Id int4)
RETURNS SETOF User AS
'
declare rec record;
begin
for rec in
select * from User where Id = @Id
loop
return next rec;
end loop;
return;
end; '
LANGUAGE 'plpgsql' VOLATILE;
When I use
select * from UserSelectById(1);
it gives the right result set.
But when I change return type like this
CREATE TYPE UserSet AS
(
Id int4,
Name varchar(32),
LastLogin timestamp --additional column
);
select * from UserSelectById(1) will give the following errors:
ERROR: wrong record type supplied in RETURN NEXT
CONTEXT: PL/pgSQL function UserSelectById line 8 at return next
This problem annoys me, if I have more than several hundred function
which returns different result set, like contains foreign keys,
I have to create many type for function's return.
Can plpgsql returns result set according to what exactly fetched,
then take return type as references to store data for return.
Whether there is some better way to deal with this problem?
Thanks Regards
Arnold.Zhu
2005-01-21
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ARC patent
Ühel kenal päeval (neljapäev, 20. jaanuar 2005, 23:17+1100), kirjutas Neil Conway: Simon Riggs wrote: However, I think the ARC replacement should *not* be a fundamental change in behavior: the algorithm should still attempt to balance recency and frequency, to adjust dynamically to changes in workload, to avoid sequential flooding, and to allow constant-time page replacement. Agreed: Those are the requirements. It must also scale better as well. On thinking about this more, I'm not sure these are the right goals for an 8.0.x replacement algorithm. For 8.1 we should definitely Do The Right Thing and develop a complete ARC replacement. For 8.0.x, I wonder if it would be better to just replace ARC with LRU. The primary advantage to doing this is LRU's simplicity -- if we're concerned about introducing regressions in stability into 8.0, this is likely the best way to reduce the chance of that happening. Furthermore, LRU's behavior with PostgreSQL is well-known and has been extensively tested. If we are going the simple way, i have two simple suggestions: 1) We should do something about seqscans polluting LRU - perhaps insert pages brought into memory by seqscan near the end of LRU list. Or just swich off postgresqls internal cachin alltogether when doing seqscans and rely on underlying systems caching entirely (as we cant switch it off anyway) 2) Another simple, but nondeterministic, hack would be using randomness, i.e. 2.1) select a random buffer in LR side half (or 30% or 60%) of for replacement. 2.2) dont last accessed pages to top of LRU list immediately, just push them uphill some amount, either random, or perhaps 1/2 the way to top at each access. This should be quite qood strategy for avoiding disastrous failings on specific pathological workloads, at the cost of less than optimal behaviour in easily analysed standard cases. Of course, the downside is that we lose the benefits of ARC or an ARC-like algorithm in 8.0. That would be unfortunate, but I don't think it is a catastrophe. The only case this would be a catastrophe, is for production OLTP workloads that did fine with ARC but get overloaded when using LRU. The other bufmgr-related changes (vacuum hints, bgwriter and vacuum delay) should ensure that VACUUM still has a much reduced impact on system performance. Sequential scans will still flood the cache, but I don't view that as an enormous problem. Has anobody some insight, how does linux kernel level disk cache solve this sequencial scan/read pollutes cache problem ? -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] can plpgsql returns more flexibe value ?
This problem annoys me, if I have more than several hundred function which returns different result set, like contains foreign keys, I have to create many type for function's return. Can plpgsql returns result set according to what exactly fetched, then take return type as references to store data for return. Whether there is some better way to deal with this problem? Yeah, you just make your function return 'SETOF record' and specify the types when you do the select: select * from func() as (a int, b text); Chris ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] ARC patent
On Fri, 21 Jan 2005 02:31:40 +0200, Hannu Krosing [EMAIL PROTECTED] wrote: 2) Another simple, but nondeterministic, hack would be using randomness, i.e. 2.1) select a random buffer in LR side half (or 30% or 60%) of for replacement. 2.2) dont last accessed pages to top of LRU list immediately, just push them uphill some amount, either random, or perhaps 1/2 the way to top at each access. Sounds good, but how do find the middle of a linked list? Or the other way round: Given a list element, how do you find out its position in a linked list? So the only approach that is easily implementable is 2.3) If a sequential scan hint flag is set, put the buffer into the free list at a random position. Servus Manfred ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] US Patents vs Non-US software ...
Hannu Krosing wrote: ?hel kenal p?eval (esmasp?ev, 17. jaanuar 2005, 21:45-0300), kirjutas Alvaro Herrera: On Mon, Jan 17, 2005 at 07:31:48PM -0400, Marc G. Fournier wrote: Just curious here, but are patents global? PostgreSQL is not US software, but it is run within the US ... so, would this patent, if it goes through, only affect those using PostgreSQL in the US, or do patents somehow transcend international borders? No, they are limited to the territory they are registered in. Not sure how that applies to somebody who just uses Postgres in the US; of course, IANAL. USAmericans can just place their servers somewhere not under US jurisdiction (Cuba) or even better, in legal vacuum (Quantanamo) and run client over internet. If something infringes then it surely is the server, not the client. Yes, our development group itself is perhaps OK, but that doesn't help US companies using it, nor US companies packaging/distributing commerical versions of PostgreSQL. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] ARC patent
Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: On Mon, 2005-01-17 at 18:43 -0500, Tom Lane wrote: I have already suggested to core that we should insist on 8.1 not requiring an initdb, so as to ensure that people will migrate up to it easily from 8.0. So is it firm policy that changes that require a catversion update cannot be made during the 8.1 cycle? Not yet --- I suggested it but didn't get any yeas or nays. I don't feel this is solely core's decision anyway ... what do the assembled hackers think? I am not in favor of adjusting the 8.1 release based solely on this patent issue. I think the probability of the patent being accepted and enforced against anyone using PostgreSQL to be very unlikely. I would also like to come up with a procedure that would scale to any other patent problems we might have. What if someone finds another patent problem during 8.1 beta? Do we shorten the 8.2 development cycle too? What I would like to do is to pledge that we will put out an 8.0.X to address any patent conflict experienced by our users. This would include ARC or anything else. This way we don't focus just on ARC but have a plan for any patent issues that appear, and we don't have to adjust our development cycle until an actual threat appears. One advantage we have is that we can easily adjust our code to work around patented code by just installing a new binary. (Patents that affect our storage format would be more difficult. A fix would have to perhaps rewrite the on-disk data.) One problem in working around the GIF format patent is that you had to create a file that was readable by many of the existing GIF readers. With PostgreSQL, only we read our own data files so we can more easily make adjustments to avoid patents. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Two-phase commit for 8.1
On Wed, 19 Jan 2005, Tom Lane wrote: Marc G. Fournier [EMAIL PROTECTED] writes: If the patch is ready to be committed early in the cycle, I'd say most definitely ... just depends on how late in the cycle its ready ... My recollection is that it's quite far from being complete. I had hoped to spend some time during the 8.1 cycle helping Heikki finish it up, but if we stick to the 2-month-dev-cycle idea I'm afraid there's no way it'll be done in time. I thought that some time would probably amount to a solid man-month or so, and there's no way I can spend half my time on just one feature for this cycle. If Heikki wants this in for 8.1, the right thing to do is vote against the short-dev-cycle idea. But we need a plausible answer about what to do about ARC to make that credible... I'm not sure what I want. If the 8.1 cycle really is a short one, say 3 months, then I have no problem waiting for 8.2. But we have a very bad track record regarding short-dev-cycles. I honestly don't believe we can get 8.1 released before July. - Heikki ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] US Patents vs Non-US software ...
On Tue, 18 Jan 2005 23:05:57 +0100, Reinoud van Leeuwen [EMAIL PROTECTED] wrote: Contrary to popular misconception, virtually all countries grant software patents. The problem is that people have Thanks to the new European Union member Poland, the Dutch plan to put the software patents on the agenda 3 days before Christmas was revoked. So no software patents in Europe for now. (and the opposition against it seems to grow!) Since Poland's name has been called, Poland is a sample of a Eurpean country which does not grant software/algorithm/etc patents neither directly nor in form of 'technological method' (our patent office is well, very conservative institution :)). As for the EU voting, it was the first time I was really glad that Poland entered Union. Both ways. First that way that powers like USA cannot force their way with patents on Poland, second that Poland give positive input into EU. Ahhh, politics, enough of it. Let's end this thread. ;) Regards, Dawid ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] 8.1 development cycle (was a couple of other threads ;))
Hello, On a slightly different note in regards to the development cycle. I am authoring a new book and it would be helpful to know the approximate completion of the dev cycle. If the dev cycle is going to be really short, are we expecting a more traditional 12-16 month 8.2? J -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] 8.1 development cycle (was a couple of other threads
On Fri, 21 Jan 2005, Joshua D. Drake wrote: Hello, On a slightly different note in regards to the development cycle. I am authoring a new book and it would be helpful to know the approximate completion of the dev cycle. If the dev cycle is going to be really short, are we expecting a more traditional 12-16 month 8.2? That seems to be the norm ... my feel for discussions so far is that 8.1's focus is going to be primarily replacing ARC, and anything else that might slip in ... Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Extending System Views: proposal for 8.1/8.2
Folks, This is for 8.1, or for 8.2 if we have a no-initdb cycle for 8.1. I'm proposing to expand both the coverage and number of system views. Our system views are an extremely useful way to get data about the system if you're not on PSQL. They are a better idea than using the underlying system tables, both becuase the system table output can be kind of cryptic, and because the system tables may change but it will be easy to maintain the views the same. Therefore, I want to run my proposed design past the team, because I'd like to build system views we can live with for the next 3-4 versions, which will allow GUI and library builders to have a reliable, static interface onto the system objects. Suggestions adjustments, please! It shouldn't take me long to write these with a clear spec. (oh, and information_schema really doesn't cover this because the SQL spec is rather limited in what objects it describes) pg_tables ADD comment pg_stats ADD statstarget for each column (the SET STATISTICS for each column) pg_user ADD groups (array) pg_functions -- create new view schemaname functionname functionowner parameters (array) returntype functionsettings (things like STABLE) functionsource comment pg_views ADD comment pg_columns -- new view ** schemaname tablename columnname datatype typemodifiers (NOT NULL, default, etc) comment pg_aggregates -- new view ** schemaname aggregatename aggregateowner datatype initvalue transfunction finalfunction comment pg_operators -- new view ** schemaname operatorname operatorowner operatortype datatypes (array) operatorfunction comment pg_schemas -- new view schemaname schemaowner defaulttablespace comment pg_triggers -- new view *** schemaname tablename triggername triggerowner triggerfunction conditions (update, insert, etc.) modifiers (deferrable, etc.) enabled comment pg_foriegnkeys -- new view parentschema parenttable parentcolumns (array) childschema childtable childcolumns (array) Views I think will be wanted by I've not really figured out how to define yet: pg_types pg_domains pg_constraints pg_groups NOTES QUESTIONS: ** = for these three views, there are an enourmous number of system aggregates, operators, etc. I'm wondering if I should hide the system ones, or simply trust the user to filter by schema? *** = since there will be a seperate FK view, pg_triggers will omit FK constrainttriggers. = I've used the non-canon terms parent and child here. The problem is that the standard terms are completely confusing and unintuitive, such as referring and referenced. Other suggestions are welcome. So, feedback before I start writing SQL? Oh, also what file are the system views defined in? -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 8.1 development cycle (was a couple of other threads
Marc G. Fournier wrote: On Fri, 21 Jan 2005, Joshua D. Drake wrote: Hello, On a slightly different note in regards to the development cycle. I am authoring a new book and it would be helpful to know the approximate completion of the dev cycle. If the dev cycle is going to be really short, are we expecting a more traditional 12-16 month 8.2? That seems to be the norm ... my feel for discussions so far is that 8.1's focus is going to be primarily replacing ARC, and anything else that might slip in ... So are we looking at a 8.1 in June and a 8.2 in say August of 2006? Or something else? I know that it is hard to completely pin these things down but it would be really helpful :) J Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [HACKERS] 8.1 development cycle (was a couple of other threads
On Fri, 21 Jan 2005, Joshua D. Drake wrote: Marc G. Fournier wrote: On Fri, 21 Jan 2005, Joshua D. Drake wrote: Hello, On a slightly different note in regards to the development cycle. I am authoring a new book and it would be helpful to know the approximate completion of the dev cycle. If the dev cycle is going to be really short, are we expecting a more traditional 12-16 month 8.2? That seems to be the norm ... my feel for discussions so far is that 8.1's focus is going to be primarily replacing ARC, and anything else that might slip in ... So are we looking at a 8.1 in June and a 8.2 in say August of 2006? I'd say that was a safe bet ... 8.1, we're looking at a 4 month dev/beta cycle, so end of May, start of Junefor release ... based on current trends, I'd say following June for beta of 8.2, and then however long after that for release, so between Aug and Nov :) Marc G. Fournier Hub.Org Networking Services (http://www.hub.org) Email: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] [pgsql-hackers] Re: Translations at pgfoundry
Peter, Euler, others: OK, is anyone opposed to this idea? I would register a pgfoundry project (name suggestions? translations?), give most established translators commit access, and move the statistics pages there. BTW, there is already a translators mailing list. While I've been using it for the press releases, there's no reason why the translators can use it for all translations. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
On Fri, Jan 21, 2005 at 12:17:08PM -0800, Josh Berkus wrote: Folks, This is for 8.1, or for 8.2 if we have a no-initdb cycle for 8.1. I'm proposing to expand both the coverage and number of system views. Our system views are an extremely useful way to get data about the system if you're not on PSQL. They are a better idea than using the underlying system tables, both becuase the system table output can be kind of cryptic, and because the system tables may change but it will be easy to maintain the views the same. Therefore, I want to run my proposed design past the team, because I'd like to build system views we can live with for the next 3-4 versions, which will allow GUI and library builders to have a reliable, static interface onto the system objects. Suggestions adjustments, please! It shouldn't take me long to write these with a clear spec. (oh, and information_schema really doesn't cover this because the SQL spec is rather limited in what objects it describes) pg_tables ADD comment pg_stats ADD statstarget for each column (the SET STATISTICS for each column) pg_user ADD groups (array) pg_functions -- create new view schemaname functionname functionowner parameters (array) returntype functionsettings (things like STABLE) functionsource comment pg_views ADD comment pg_columns -- new view ** schemaname tablename columnname datatype typemodifiers (NOT NULL, default, etc) comment pg_aggregates -- new view ** schemaname aggregatename aggregateowner datatype initvalue transfunction finalfunction comment pg_operators -- new view ** schemaname operatorname operatorowner operatortype datatypes (array) operatorfunction comment pg_schemas -- new view schemaname schemaowner defaulttablespace comment pg_triggers -- new view *** schemaname tablename triggername triggerowner triggerfunction conditions (update, insert, etc.) modifiers (deferrable, etc.) enabled comment pg_foriegnkeys -- new view parentschema parenttable parentcolumns (array) childschema childtable childcolumns (array) Views I think will be wanted by I've not really figured out how to define yet: pg_types pg_domains pg_constraints pg_groups I don't know how this fits in, but it would be *very* nice to have SQLSTATE meta-information available via SQL. I've sent in a patch for this. Cheers, D -- David Fetter [EMAIL PROTECTED] http://fetter.org/ phone: +1 510 893 6100 mobile: +1 415 235 3778 Remember to vote! ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] ARC patent
Folks, Asking this again as it seems my question got lost, or at least unanswered. Why not just contact IBM, and get their opinion? As I said before, we might just get a promise of a full licence for if/when the patent is granted. ... John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] [pgsql-hackers] Re: Translations at pgfoundry
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Fri, 21 Jan 2005, Josh Berkus wrote: OK, is anyone opposed to this idea? I would register a pgfoundry project (name suggestions? translations?), give most established translators commit access, and move the statistics pages there. BTW, there is already a translators mailing list. While I've been using it for the press releases, there's no reason why the translators can use it for all translations. Are all regional contacts are also the translators of .po files? Translating a press release is somewhat a non-technical issue; but the .po translators have to know PostgreSQL internals, I think... So, translators ml should be limited to the press release translators as it is now; and we need to create a new ml for the .po translators. Just my 0.02 ... Regards, - -- Devrim GUNDUZ devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.tdmsoft.com http://www.gunduz.org -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQFB8Ymdtl86P3SPfQ4RAp4zAJ918KcctE/c90xlA99/cI63wBn04gCdF5RQ ooljgkP59MdcRSrr98AjBzE= =QgSX -END PGP SIGNATURE- ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[HACKERS] TIP9
TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Hi, sorry for using this list, but is not time to change this TIP for something more suitable to the new PG8 capabilities? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] ARC patent
John Hansen wrote: Folks, Asking this again as it seems my question got lost, or at least unanswered. Why not just contact IBM, and get their opinion? 1. We don't have attorneys to do so. 2. The PostgreSQL community is not a legal entity it can license to. 3. It would take weeks if not months to get an answer 4. The patent isn't issed yet. Sincerely, Joshua D. Drake As I said before, we might just get a promise of a full licence for if/when the patent is granted. ... John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] TIP9
Jaime Casanova wrote: TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Hi, sorry for using this list, but is not time to change this TIP for something more suitable to the new PG8 capabilities? Is this still not the case for pg8? I know it is better about casting in general for use with int8 etc... but don't the column datatypes still have to match? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Locale agnostic unicode text
Hello!
One of least liked by me features of PostgreSQL is a need to specify
LC_CTYPE an LC_COLLATE at initdb time. Especially if you intend to
put into DB texts in
different languages (say, Polish, French, German and Russian) and use
functions like lower() or ORDER BY these texts. :)
I guess the need to select these two locales at initdb time is to
avoid problems with corrupted indexes (column first indexed
with lower() function when setlocale('C'), then accessed when
setlocale('ru_RU')... oops. etc.). Probably there are more of
those. :)
To solve this I thought about creating function lower(text, locale), say:
lower ('Name', 'pl_PL.utf8');
Simple enough, I used plperl (plperlu actually) to make it happen and
while doing so I've noticed that perl does unicode-lowercasing/uppercasing on
its own accord, doesn't need locales to it, and does it pretty well.
So the resulting function is:
CREATE DOMAIN unitext text;
CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return lc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
And it seems to work fine regardless of locale set.
So... I thoght, why not use this unitext to sort texts?
So I've created functions, operators and operator class,
This time setlocale() was needed to get the behaviour
I needed (database initdb'ed to 'C', my order set to 'pl_PL',
or whatever locale I need at given moment).
I've attached a 'draft' of unitext,sql, which should create
unitext datatype capable of sorting according Polish locale.
It does not work as it should and I don't know how to make
it work. For example:
SELECT * FROM uni_tab ORDER BY uni_column;
...sorts according to 'C' (initdb's) locale.
I can force my way by issuing:
SELECT * FROM uni_tab ORDER BY uni_column USING ;
...but I would like to force ORDER BY using operators
provided by me without this 'USING ' clause.
Any hints how to do it?
Regards,
Dawid
PS: I like perl's lc() and uc() behaviour in utf8 mode. I'm thinking about
trying to port it from perl source as a C-language extension for PostgreSQL.
What do you think about it?
SET search_path = public;
CREATE DOMAIN unitext text;
BEGIN;
CREATE OR REPLACE FUNCTION lower(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return lc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
CREATE OR REPLACE FUNCTION upper(unitext) RETURNS unitext AS $$
utf8::decode($_[0]);
return uc($_[0]);
$$ LANGUAGE plperlu IMMUTABLE;
CREATE OR REPLACE FUNCTION unitext_lt(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] lt $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
CREATE OR REPLACE FUNCTION unitext_le(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] le $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_gt(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] gt $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_ge(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] ge $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_eq(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] eq $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu STABLE;
CREATE OR REPLACE FUNCTION unitext_ne(unitext,unitext) RETURNS boolean AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = ($_[0] ne $_[1]) ? 't' : 'f';
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
CREATE OR REPLACE FUNCTION unitext_cmp(unitext,unitext) RETURNS integer AS $$
utf8::decode($_[0]); utf8::decode($_[1]);
use POSIX qw(setlocale LC_ALL);
my $loc = setlocale(LC_ALL);
setlocale(LC_ALL, 'pl_PL.utf8');
use locale;
my $ret = $_[0] cmp $_[1];
setlocale(LC_ALL, $loc);
return $ret;
$$ LANGUAGE plperlu;
COMMIT;
CREATE OPERATOR (
LEFTARG = unitext,
RIGHTARG = unitext,
-- COMMUTATOR = ,
-- NEGATOR = =,
PROCEDURE = unitext_lt,
RESTRICT = scalarltsel,
JOIN =
Re: [HACKERS] ARC patent
We could still get their opinion. I have a couple aquaintances at IBM that I can try to contact about it. Rather than assume what IBM will do, why not just ask them? If they don't respond, they don't respond. If they do respond, it's better than us guessing. Yes, it's only going to matter if the patent is issued, but why not make an effort to get some info from them? Joshua D. Drake wrote: John Hansen wrote: Folks, Asking this again as it seems my question got lost, or at least unanswered. Why not just contact IBM, and get their opinion? 1. We don't have attorneys to do so. 2. The PostgreSQL community is not a legal entity it can license to. 3. It would take weeks if not months to get an answer 4. The patent isn't issed yet. Sincerely, Joshua D. Drake As I said before, we might just get a promise of a full licence for if/when the patent is granted. ... John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] ARC patent
Jonah H. Harris wrote: We could still get their opinion. I have a couple aquaintances at IBM that I can try to contact about it. Rather than assume what IBM will do, why not just ask them? If they don't respond, they don't respond. If they do respond, it's better than us guessing. Yes, it's only going to matter if the patent is issued, but why not make an effort to get some info from them? Well I believe it is Core's decision to make. Sincerely, Joshua D. Drkae Joshua D. Drake wrote: John Hansen wrote: Folks, Asking this again as it seems my question got lost, or at least unanswered. Why not just contact IBM, and get their opinion? 1. We don't have attorneys to do so. 2. The PostgreSQL community is not a legal entity it can license to. 3. It would take weeks if not months to get an answer 4. The patent isn't issed yet. Sincerely, Joshua D. Drake As I said before, we might just get a promise of a full licence for if/when the patent is granted. ... John ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] -- Command Prompt, Inc., home of Mammoth PostgreSQL - S/ODBC and S/JDBC Postgresql support, programming shared hosting and dedicated hosting. +1-503-667-4564 - [EMAIL PROTECTED] - http://www.commandprompt.com PostgreSQL Replicator -- production quality replication for PostgreSQL begin:vcard fn:Joshua Drake n:Drake;Joshua org:Command Prompt, Inc. adr:;;PO Box 215 ;Cascade Locks;OR;97014;US email;internet:[EMAIL PROTECTED] title:Consultant tel;work:503-667-4564 tel;fax:503-210-0334 x-mozilla-html:FALSE url:http://www.commandprompt.com version:2.1 end:vcard ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Autotuning Group Commit
Currently, we have group commit functionality via GUC parameters commit_delay and commit_siblings Group commit is either off or on. Since we do not have a log writer daemon, there is no way to know whether that is optimal. There is research to show that setting group commit on when it is not useful actually causes a performance degradation. Clearly, this means that on a server that is sometimes busy and sometimes not, you will be unsure of how to set these parameters. ISTM that we can autotune the group commit functionality: Each transaction commit gets the current time(NULL) immediately before it commits. If we store that value in shared memory behind XLogInsertLock, then each time we commit we would be able to tell how long it has been since the last commit. We could thus make a true/false judgement as to whether it would have gained us anything to wait for the commit_delay time before committing. If we store the results of the last 10 commits (various ways...), then if we have 9+ out of 10 last commits as potentially beneficial group commits then we have a reasonably probability that commits are happening on average faster than commit_delay. As a result, we know to turn on the group commit feature by setting group_commit_recommendation = true. Each backend would start with group commit turned off. Each time it commits it reads the current setting of group_commit_recommendation. If this is set, it copies the group_commit_recommendation to a local variable, so that the next time it commits it will wait for CommitDelay. If CommitDelay is not set, then we would avoid the calculation altogether and this would remain the default. With this proposal, group commit will turn on or off according to recent history reacting within 10*commit_delay milliseconds of a heavy transaction load starting, turning off again even more quickly. None of that would require knowledge, or tuning by the administrator. That is sufficient to react to even small bursts of activity. We would also be able to remove the commit_siblings GUC. It represents a simple heuristic only for determining whether commit_delay should be applied, so is effectively superceded by this proposal. There would be no additional memory per backend and a minor additional shared memory overhead, which could easily be optimised with some crafty code. Overall, the additional minor CPU cost per transaction commit would be worth the potential saving of 10ms on many transactions where group commit would not gain performance at all. In any case, the functionality would be optional and turned off by default. Any comments, please? -- Best Regards, Simon Riggs ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ARC patent
Joshua D. Drake [EMAIL PROTECTED] writes: John Hansen wrote: Folks, Asking this again as it seems my question got lost, or at least unanswered. Why not just contact IBM, and get their opinion? 1. We don't have attorneys to do so. 2. The PostgreSQL community is not a legal entity it can license to. 3. It would take weeks if not months to get an answer 4. The patent isn't issed yet. Don't forget: 5. They would also have to license everyone else who might want to repackage or use Postgres. Such as Fujitsu, a big competitor of theirs. -- greg ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] can plpgsql returns more flexibe value ?
Hello, Christopher Kings-Lynne Yeah, you just make your function return 'SETOF record' and specify the types when you do the select: select * from func() as (a int, b text); Chris This is not a good idea when I use C# to program, I want to Fill the resultset directly into Dataset, this method will should use more sql to get data, it lose function's convenience like stored procedure. Thanks Regards! Arnold.Zhu 2005-01-22 ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] can plpgsql returns more flexibe value ?
On Sat, 22 Jan 2005, Arnold.Zhu wrote: Yeah, you just make your function return 'SETOF record' and specify the types when you do the select: select * from func() as (a int, b text); This is not a good idea when I use C# to program, I want to Fill the resultset directly into Dataset, this method will should use more sql to get data, it lose function's convenience like stored procedure. Perhaps you should look into the refcursor type, which will allow you to return anything you want without specifying it. You can't do things like a join between to refcursor outputs, but it does allow for more return flexibility. Kris Jurka ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] ARC patent
John Hansen wrote: Folks, Asking this again as it seems my question got lost, or at least unanswered. Why not just contact IBM, and get their opinion? As I said before, we might just get a promise of a full licence for if/when the patent is granted. I doubt we can get a license that would cover companies that package PostgreSQL. While I don't think they would attack them I also don't think they can give a blanket approval in writing. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] TIP9
--- Joshua D. Drake [EMAIL PROTECTED] escribió: Jaime Casanova wrote: TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match Hi, sorry for using this list, but is not time to change this TIP for something more suitable to the new PG8 capabilities? Is this still not the case for pg8? I know it is better about casting in general for use with int8 etc... but don't the column datatypes still have to match? http://archives.postgresql.org/pgsql-hackers/2004-11/msg00497.php ??? regards, Jaime Casanova _ Do You Yahoo!? Información de Estados Unidos y América Latina, en Yahoo! Noticias. Visítanos en http://noticias.espanol.yahoo.com ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Extending System Views: proposal for 8.1/8.2
I'm glad to see a types view. A while ago I was trying to figure out a way to query the pg_* views to see if a particular function existed. I quickly got stuck trying to figure out how to properly handle the arguments array. The solution Tom gave in this case was just to hard-code the OID for the type I needed. It works, but it seems rather ugly. I think it would be good if pg_function included an array of parameter types of the standard parameter names, as well as the type OIDs. On Fri, Jan 21, 2005 at 12:17:08PM -0800, Josh Berkus wrote: ** = for these three views, there are an enourmous number of system aggregates, operators, etc. ? I'm wondering if I should hide the system ones, or simply trust the user to filter by schema? I think it would be handy to have a set of views defined that shows everything, and have the 'normal set' (ie, the views with the easiest name to type in) defined to hide the system stuff. I suspect that most of the time people are using these views they don't care about the system stuff. *** = since there will be a seperate FK view, pg_triggers will omit FK constrainttriggers. I think it would be useful if there was a version of the view that showed what these triggers were. If you want to see all the triggers operating on a table, for example. I guess this is a bit of a grey area, since I don't know of any other database that handles RI using triggers. = I've used the non-canon terms parent and child here. ? The problem is that the standard terms are completely confusing and unintuitive, such as referring and referenced. ? ? Other suggestions are welcome. I think parent and child is fine. On another naming note; the naming convention for system stuff has always driven me nuts. Some the letter prefix (ie: tab for tables) in front of every field name, with no underscores or anything. Extensive use of abbreviations that you need to remember (ie: indnatts, indexprs, indpred). No use of underscores (indisunique). Yet the view and table names do use underscores. I realize that there's probably a pretty tight mapping between catalog *tables* and internals and that changing anything there would probably have a huge impact on code. Of course there's also existing code that uses the pg_catalog stuff that's defined today. What I'm hoping is that with the amount of work involved in the changes Josh is suggesting, instituting a more rational naming scheme wouldn't be that much extra effort, at least for things that are being added. One possibility might be to leave the existing views alone (and possibly deprecate them), and just create new views. What I'd like to see is names that are spelled out and underscore delimited. BTW, this is something I can actually work on myself, and I'd be happy to work on the code as consensus is reached on what the different views should look like. My $2.00. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Autotuning Group Commit
1) I'm in favor of autotuning anything possible. 2) In addition to turning group_commit on and off, what about also adjusting the commit delay, based on statistics of recent commits? It might require a slightly larger sample set (maybe the last 100 commits), but it seems it would provide more flexibility (hence more usefulness) to the autotuning. I belive you'd want to first calculate the elapsed time between each commit in the sample set, then look for groupings of elapsed time. If you have a set that looks like this: Time (ms) Number 2 * 4 * 6 8 ** 10 * 12 ** 14 16 ** 18 20 * then you'd want a delay of 16ms. I think this calculation could be done fairly quickly by grouping the commits into buckets of different elapsed times, then look for the largest elapsed time that has a number of commits greater than the mean number of commits for all the buckets. But I'm not a statistician, hopefully someone here is. :) -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: Where do you want to go today? Linux: Where do you want to go tomorrow? FreeBSD: Are you guys coming, or what? ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
