Re: [HACKERS] Rewriting Free Space Map
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Mon, Mar 17, 2008 at 01:23:46PM -0400, Tom Lane wrote: Simon Riggs [EMAIL PROTECTED] writes: Tom Lane wrote: The idea that's becoming attractive to me while contemplating the multiple-maps problem is that we should adopt something similar to the old Mac OS idea of multiple forks in a relation. Can we call them maps or metadata maps? forks sounds weird. Actually, I do like forks, but to add a little bit diversity: facets? aspects? FWIW, the idea of mapping a relation to a directory quite compelling. Regards - -- tomás -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.1 (GNU/Linux) iD8DBQFH33c7Bcgs9XrR2kYRAuBQAJ9MjISqgn37umRIydxtUBYONORwDgCbBKkE y7adUy7s/30TxQPQiJZZejA= =PAQ9 -END PGP SIGNATURE- -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Am Dienstag, 18. März 2008 schrieb Dave Page: On Mon, Mar 17, 2008 at 7:44 PM, Peter Eisentraut [EMAIL PROTECTED] wrote: Log Message: --- Enable probes to work with Mac OS X Leopard and other OSes that will support DTrace in the future. Switch from using DTRACE_PROBEn macros to the dynamically generated macros. Use dtrace -h to create a header file that contains the dynamically generated macros to be used in the source code instead of the DTRACE_PROBEn macros. A dummy header file is generated for builds without DTrace support. Do we need some support in the buildfarm to create the header file? The Windows/MSVC build system needs some adjustments. I have no idea how it works, but I'd be glad to explain the details to someone who does. -- 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] count(*) performance improvement ideas
Tom Lane [EMAIL PROTECTED] writes: Stephen Denne [EMAIL PROTECTED] writes: However I'm not after a fast count(*) from table, but more like a fast select grouping_id, count(*) from my_table group by grouping_id You could apply the same technique across each group id, though this certainly is getting beyond what any built-in feature might offer. At that point you're talking about materialized views. Which makes it a whole lot more interesting imho. Does this idea apply with the same efficiency in pre 8.3, non-HOT implementations? I didn't claim it was amazingly efficient in any implementation ;-). HOT in particular is nearly useless since most rows in the count table will never be updated, only inserted and eventually deleted. You might get some mileage on the base row, but that'd be about it. The count table will need frequent vacuums as well as frequent aggregation scans. It might be better not to update this delta table in normal transactional updates. After all the tuples you're deleting are precisely the ones that nobody should be interested in any more. If you locked the table and magically deleted those tuples and updated the master tuple using the global xmin instead of your real xid people would get the same result and you could reclaim the space much much sooner. Locking the table kind of sucks though. And crash recovery would be a problem. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's PostGIS 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
On Tue, Mar 18, 2008 at 9:40 AM, Peter Eisentraut [EMAIL PROTECTED] wrote: Do we need some support in the buildfarm to create the header file? The Windows/MSVC build system needs some adjustments. I have no idea how it works, but I'd be glad to explain the details to someone who does. I was actually thinking of the OS X buildfarm member I setup to exercise this. From your description it sounded like we need to generate the probe header manually if we enable dtrace. I'm sure Magnus would love to hear the details for the MSVC perl scipts :-p -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
On Mon, Mar 17, 2008 at 7:44 PM, Peter Eisentraut [EMAIL PROTECTED] wrote: Log Message: --- Enable probes to work with Mac OS X Leopard and other OSes that will support DTrace in the future. Switch from using DTRACE_PROBEn macros to the dynamically generated macros. Use dtrace -h to create a header file that contains the dynamically generated macros to be used in the source code instead of the DTRACE_PROBEn macros. A dummy header file is generated for builds without DTrace support. Do we need some support in the buildfarm to create the header file? -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
On Tue, Mar 18, 2008 at 11:48 AM, Peter Eisentraut [EMAIL PROTECTED] wrote: Am Dienstag, 18. März 2008 schrieb Dave Page: I was actually thinking of the OS X buildfarm member I setup to exercise this. From your description it sounded like we need to generate the probe header manually if we enable dtrace. Nope. OK, I'll enable it on antelope then. Shouldn't there have been a doc patch with this as well? Last time I read the docs they said only Solaris supports dtrace. -- Dave Page EnterpriseDB UK Ltd: http://www.enterprisedb.com PostgreSQL UK 2008 Conference: http://www.postgresql.org.uk -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] CVS problems
Hi. Yesterday I downloaded source code from CVS following developer.postgresql.org/docs/postgres/cvs.html First of all it had problems downloading files, INSTALL text file has not been downloaded, for example. Today that web page isn't even available... That's probably causing the problems I'm getting during make make check I won't annoy with those problems until I'm sure I downloaded correctly souces from CVS. In case it's just a problem of mine can you please suggest some alternatives? Regards, Manolo.
Re: [HACKERS] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Am Dienstag, 18. März 2008 schrieb Dave Page: I was actually thinking of the OS X buildfarm member I setup to exercise this. From your description it sounded like we need to generate the probe header manually if we enable dtrace. Nope. -- 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] Remove hacks for old bad qsort() implementations?
How about always adding the TID as last key when using qsort for
create
index ?
I think you misunderstood: that's what we do now. I'm proposing
removing it because I think it's probably useless.
Ah, sorry, I did not look at the code, and interpreted your comment as
some exceptional handling.
I think really randomly (regarding TID) ordering highly duplicate keys
is not such a good idea.
But the point is, it does not need to be exact. Basically sorted with a
few exceptions
and jumps, or sorted by blockid only would be ok. How random does our
qsort really return those tids ?
You wrote:
However, oprofile is telling me that doing this is costing
*significantly* more than just returning zero would do:
9081 0.3050 :tuple1 = (IndexTuple) a-tuple;
3759 0.1263 :tuple2 = (IndexTuple) b-tuple;
:
:{
130409 4.3800 :BlockNumber blk1 =
ItemPointerGetBlockNumber(tuple1-t_tid);
So why is this ItemPointerGetBlockNumber so expensive ?
34539 1.1601 :BlockNumber blk2 =
ItemPointerGetBlockNumber(tuple2-t_tid);
Is it not correctly inlined ? Are the shifts for BlockNumber so
expensive ?
Or is this simply some oprofile overhead that is not real at all ?
Andreas
--
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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Dave Page wrote: Shouldn't there have been a doc patch with this as well? Last time I read the docs they said only Solaris supports dtrace. Well, it won't support anything else until somebody tries debugs it ;-) -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- 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] CVS problems
Manolo SupaMA escribió: Hi. Yesterday I downloaded source code from CVS following developer.postgresql.org/docs/postgres/cvs.html First of all it had problems downloading files, INSTALL text file has not been downloaded, for example. Today that web page isn't even available... That URL certainly goes to the CVS instructions. That's probably causing the problems I'm getting during make make check Did you run configure? Did you use --enable-depend? What errors are you getting? -- Alvaro Herrerahttp://www.CommandPrompt.com/ 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] minimal update
On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian [EMAIL PROTECTED] wrote: I assume don't want a TODO for this? (Suppress UPDATE no changed columns) I am starting to implement this. Do we want to have this trigger function in the server, or in an external module? Best regards, --- Andrew Dunstan wrote: Tom Lane wrote: Michael Glaesemann [EMAIL PROTECTED] writes: What would be the disadvantages of always doing this, i.e., just making this part of the normal update path in the backend? (1) cycles wasted to no purpose in the vast majority of cases. (2) visibly inconsistent behavior for apps that pay attention to ctid/xmin/etc. (3) visibly inconsistent behavior for apps that have AFTER triggers. There's enough other overhead in issuing an update (network, parsing/planning/etc) that a sanely coded application should try to avoid issuing no-op updates anyway. The proposed trigger is just a band-aid IMHO. I think having it as an optional trigger is a reasonable compromise. Right. I never proposed making this the default behaviour, for all these good reasons. The point about making the app try to avoid no-op updates is that this can impose some quite considerable code complexity on the app, especially where the number of updated fields is large. It's fragile and error-prone. A simple switch that can turn a trigger on or off will be nicer. Syntax support for that might be even nicer, but there appears to be some resistance to that, so I can easily settle for the trigger. cheers andrew ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- [EMAIL PROTECTED] [EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com EnterpriseDB http://www.enterprisedb.com 17° 29' 34.37N, 78° 30' 59.76E - Hyderabad * 18° 32' 57.25N, 73° 56' 25.42E - Pune 37° 47' 19.72N, 122° 24' 1.69 W - San Francisco http://gurjeet.frihost.net Mail sent from my BlackLaptop device
[HACKERS] Collating records based on a custom group by (aggregate like) function
Hi, I want to create a custom function/query (using the C API?) that collates records from a record set (takes multiple records as input) and produces a set of records (returns multiple records). The Postgres docs only give examples of how to create a custom function that returns multiple records (sets of tuples), but does not show how you can create a function that can take a set of tuples as input and scan them. This requirement has come about because I want to scan an event log table and collate individual log entries (group them) depending on a custom notion of equality. For instance, if we had the table events: timestamp, event 00:00:00, a 00:00:01, b 00:00:02, a 00:10:00, b 00:10:01, b The custom function/query should output: event, start, end a, 00:00:00, 00:00:02 b, 00:00:01, 00:00:01 b, 00:10:00, 00:10:01 It's like grouping the events in the log table by name and timestamp, however the timestamp grouping is not based simply on equality, but the notion that if two events happen within 5 minutes of each other they are collated into the same event bucket. Another way to look at the problem would be to create a special grouping function in a GROUP BY clause, e.g. SELECT name, min(timestamp), max(timestamp) FROM events GROUP BY name, enumerate_event(name, timestamp); Where enumerate_event would take the name (a or b as in the example previously), and the timestamp for the event, then return a unique collated event ID for each distinct event bucket. The problem here is that the enumerate_event function would need to save it's state (a temporary list of active events) between calls. I can't see an easy way to make a custom function using the Postgres C API that can (for each transaction) setup an internal state, repeatedly use the state during the execution of a query, and then free it's state when the query finishes. I've racked my brain about this but can't think of a simple solution, even though this appears to be a simple problem, any suggestions much appreciated. Regards, Dan... -- Dan Searle Adelix Ltd [EMAIL PROTECTED] web: www.adelix.com tel: 0845 230 9590 / fax: 0845 230 9591 / support: 0845 230 9592 snail: The Old Post Office, Bristol Rd, Hambrook, Bristol BS16 1RY. UK. Adelix Ltd is a registered company in England Wales No. 4232156 VAT registration number 779 4232 91 Adelix Ltd is BS EN ISO 9001:2000 Certified (No. GB 12763) Any views expressed in this email communication are those of the individual sender, except where the sender specifically states them to be the views of a member of Adelix Ltd. Adelix Ltd. does not represent, warrant or guarantee that the integrity of this communication has been maintained nor that the communication is free of errors or interference. Scanned for viruses, spam and offensive content by CensorNet MailSafe Professional Web E-mail Filtering from www.censornet.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] minimal update
Gurjeet Singh wrote: On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] wrote: I assume don't want a TODO for this? (Suppress UPDATE no changed columns) I am starting to implement this. Do we want to have this trigger function in the server, or in an external module? I have the trigger part of this done, in fact. What remains to be done is to add it to the catalog and document it. The intention is to make it a builtin as it will be generally useful. If you want to work on the remaining parts then I will happily ship you the C code for the trigger. 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
Re: [HACKERS] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Alvaro Herrera [EMAIL PROTECTED] writes: Dave Page wrote: Shouldn't there have been a doc patch with this as well? Last time I read the docs they said only Solaris supports dtrace. Well, it won't support anything else until somebody tries debugs it ;-) Well, I've got Leopard here, I'd be happy to test it ... but the patch has rendered http://developer.postgresql.org/pgdocs/postgres/dynamic-trace.html into a pack of lies quite independently of which OSes are supported, so I'm not very sure what to do. 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Am Dienstag, 18. März 2008 schrieb Tom Lane: Well, I've got Leopard here, I'd be happy to test it ... but the patch has rendered http://developer.postgresql.org/pgdocs/postgres/dynamic-trace.html into a pack of lies quite independently of which OSes are supported, so I'm not very sure what to do. Ah yes. Robert, do you think you could update the documentation a bit on how to use the tracing? -- 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] minimal update
On Tue, Mar 18, 2008 at 7:46 PM, Andrew Dunstan [EMAIL PROTECTED] wrote:
Gurjeet Singh wrote:
On Fri, Mar 7, 2008 at 9:40 PM, Bruce Momjian [EMAIL PROTECTED]
mailto:[EMAIL PROTECTED] wrote:
I assume don't want a TODO for this? (Suppress UPDATE no changed
columns)
I am starting to implement this. Do we want to have this trigger
function in the server, or in an external module?
I have the trigger part of this done, in fact. What remains to be done
is to add it to the catalog and document it. The intention is to make it
a builtin as it will be generally useful. If you want to work on the
remaining parts then I will happily ship you the C code for the trigger.
In fact, I just finished writing the C code and including it in the catalog
(Just tested that it's visible in the catalog). I will test it to see if it
does actually do what we want it to.
I have incorporated all the suggestions above. Would love to see your code
in the meantime.
Here's the C code:
Datum
trig_ignore_duplicate_updates( PG_FUNCTION_ARGS )
{
TriggerData *trigData;
HeapTuple oldTuple;
HeapTuple newTuple;
if (!CALLED_AS_TRIGGER(fcinfo))
elog(ERROR, trig_ignore_duplicate_updates: not called by trigger
manager.);
if( !TRIGGER_FIRED_BY_UPDATE(trigData-tg_event)
!TRIGGER_FIRED_BEFORE(trigData-tg_event)
!TRIGGER_FIRED_FOR_ROW(trigData-tg_event) )
{
elog(ERROR, trig_ignore_duplicate_updates: Can only be executed for
UPDATE, BEFORE and FOR EACH ROW.);
}
trigData = (TriggerData *) fcinfo-context;
oldTuple = trigData-tg_trigtuple;
newTuple = trigData-tg_newtuple;
if (newTuple-t_len == oldTuple-t_len
newTuple-t_data-t_hoff == oldTuple-t_data-t_hoff
HeapTupleHeaderGetNatts(newTuple-t_data) ==
HeapTupleHeaderGetNatts(oldTuple-t_data)
(newTuple-t_data-t_infomask ~HEAP_XACT_MASK)
== (oldTuple-t_data-t_infomask ~HEAP_XACT_MASK)
memcmp( (char*)(newTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
(char*)(oldTuple-t_data) + offsetof(HeapTupleHeaderData,
t_bits),
newTuple-t_len - offsetof(HeapTupleHeaderData, t_bits)
) == 0 )
{
/* return without crating a new tuple */
return PointerGetDatum( NULL );
}
return PointerGetDatum( trigData-tg_newtuple );
}
--
[EMAIL PROTECTED]
[EMAIL PROTECTED] gmail | hotmail | indiatimes | yahoo }.com
EnterpriseDB http://www.enterprisedb.com
17° 29' 34.37N, 78° 30' 59.76E - Hyderabad *
18° 32' 57.25N, 73° 56' 25.42E - Pune
37° 47' 19.72N, 122° 24' 1.69 W - San Francisco
http://gurjeet.frihost.net
Mail sent from my BlackLaptop device
[HACKERS] whaddya know, a build with no flex noise at all
I am pleased to report that the recently released flex 2.5.35 generates code for our .l files that produces no gcc warnings whatsoever. This is a first :-(, at least for versions of flex without any downstream patches. 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] CVS problems
On Tue, 18 Mar 2008, Manolo SupaMA wrote: Yesterday I downloaded source code from CVS following developer.postgresql.org/docs/postgres/cvs.html There is another tutorial that's got a lot more detail than this one at http://wiki.postgresql.org/wiki/Working_with_CVS which uses rsync to make your own copy of the repository instead. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] [0/4] Proposal of SE-PostgreSQL patches
KaiGai, NSA and SUN announces a new project launched which tries to port FLASK security architecture, it is named as FMAC. It can be a good candidate to host multilevel database security. (SELinux is also based on FLASK security architecture.) SEBSD project is also known. It is a poring of SELinux into BSD platform. SEBSD: http://www.trustedbsd.org/sebsd.html Keen. I'm sure Kathy's team understands all this. It's over my head. ;-) -- Josh Berkus PostgreSQL @ Sun San Francisco -- 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] Need Mentors for Google Summer of Code!
Josh, I'm interested in selectivity functions for FTS, proposed by Jan Urbaski. In case there will be no available mentors for him, I'd work with him. Oleg On Thu, 28 Feb 2008, Josh Berkus wrote: Hackers, Over the last 2 years, Google Summer of Code has been responsible for=20 several new features of PostgreSQL: XML, ghost XID, phpPgAdmin upgrade,=20 and more, as well as some new long-term contributors to the project. We=20 want to have an equally good summer this year. So: we need PostgreSQL contributors to volunteer as mentors. =A0As well as the core code, *any* project in the PostgreSQL family is=20 eligible, including drivers, GUIs, documentation, replication,=20 applications and tools. Don't be shy! Pass this along to sub-projects of= =20 PostgreSQL which might be eligible. Mentors must be patient, friendly, easy to stay in touch with, knowledgeable about their project areas, and able to commit to spending=20 several hours a week helping their student(s) from May to August. GSOC is an unparalled opportunity for our project to recruit new contributors, and get students interested in databases. =A0We have less tha= n=20 week to get everything together. =A0So please respond soon! --=20 --Josh Josh Berkus PostgreSQL @ Sun San Francisco ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], 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] Need Mentors for Google Summer of Code!
Oleg Bartunov wrote: Josh, I'm interested in selectivity functions for FTS, proposed by Jan Urbaski. In case there will be no available mentors for him, I'd work with him. Oleg Thanks! Today I'll post a more detailed plan on -hackers explaining how I think I could approach the problem. Oleg, should I contact you personally to consult the application that I'm going to submit through the Google web app, or should I just send it to Google when the gates open and wait for the results? Regards, Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Need Mentors for Google Summer of Code!
Oleg Bartunov wrote: Josh, I'm interested in selectivity functions for FTS, proposed by Jan Urbaski. In case there will be no available mentors for him, I'd work with him. Oleg Thanks! Today I'll post a more detailed plan on -hackers explaining how I think I could approach the problem. Oleg, should I contact you personally to consult the application that I'm going to submit through the Google web app, or should I just send it to Google when the gates open and wait for the results? Regards, Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Need Mentors for Google Summer of Code!
On Tue, 18 Mar 2008, Jan Urbaski wrote: Oleg Bartunov wrote: Josh, I'm interested in selectivity functions for FTS, proposed by Jan Urbaski. In case there will be no available mentors for him, I'd work with him. Oleg Thanks! Today I'll post a more detailed plan on -hackers explaining how I think I could approach the problem. Oleg, should I contact you personally to consult the application that I'm going to submit through the Google web app, or should I just send it to Google when the gates open and wait for the results? If it's not late we should have your idea listed on http://www.postgresql.org/developer/summerofcode Regards, Jan Regards, Oleg _ Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru), Sternberg Astronomical Institute, Moscow University, Russia Internet: [EMAIL PROTECTED], 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] Need Mentors for Google Summer of Code!
Oleg Bartunov wrote: On Tue, 18 Mar 2008, Jan Urbaski wrote: Oleg Bartunov wrote: Josh, I'm interested in selectivity functions for FTS, proposed by Jan Urbaski. In case there will be no available mentors for him, I'd work with him. Oleg Thanks! Today I'll post a more detailed plan on -hackers explaining how I think I could approach the problem. Oleg, should I contact you personally to consult the application that I'm going to submit through the Google web app, or should I just send it to Google when the gates open and wait for the results? If it's not late we should have your idea listed on http://www.postgresql.org/developer/summerofcode It's kind of there - in the TODO referenced by the summerofcode page: http://www.postgresql.org/docs/faqs.TODO.html#section_19 - Improve selectivity functions for geometric operators But maybe it would by nice to update it to read improve selectivity functions for text search, or move it to the summerofcode page. Regards, Jan -- Jan Urbanski GPG key ID: E583D7D2 ouden estin signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Need Mentors for Google Summer of Code!
Jan, Oleg, should I contact you personally to consult the application that I'm going to submit through the Google web app, or should I just send it to Google when the gates open and wait for the results? Submit it to Google. We have the ability to update submissions if they need to be adjusted. -- Josh Berkus PostgreSQL @ Sun San Francisco -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: [COMMITTERS] pgsql: Don't need -Wno-error anymore, because flex is no longer
Peter Eisentraut wrote: Log Message: --- Don't need -Wno-error anymore, because flex is no longer producing warnings. I see this patch only affects ecpg? Is this related to Tom's report from trying the new flex? Are we assuming that all CVS build people also have the new flex? Is the new flex even installed on the server that makes our tarballs? Are we all supposed to use the new flex? I am confused. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] whaddya know, a build with no flex noise at all
Am Dienstag, 18. März 2008 schrieb Tom Lane: I am pleased to report that the recently released flex 2.5.35 generates code for our .l files that produces no gcc warnings whatsoever. This is a first :-(, at least for versions of flex without any downstream patches. Oh excellent! Now we can build with -Werror without problems (on some platforms at least, I guess). This should make development and verification of patches a bit more robust. Just don't pass -Werror to configure or you will experience very strange behavior. :) -- 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: Don't need -Wno-error anymore, because flex is no longer
Am Dienstag, 18. März 2008 schrieb Bruce Momjian: Peter Eisentraut wrote: Log Message: --- Don't need -Wno-error anymore, because flex is no longer producing warnings. I see this patch only affects ecpg? Is this related to Tom's report from trying the new flex? Yes. There used to be a few -Wno-error uses throughout the source code, near the flex calls, to work around the well-known flex warnings issue in case someone wanted to use -Werror to check his builds. The fact that there was only one such use left in the ecpg subtree shows that this wasn't really maintained and could never have worked reliably. Are we assuming that all CVS build people also have the new flex? Is the new flex even installed on the server that makes our tarballs? Are we all supposed to use the new flex? I think it is reasonable to assume that those wanting to use -Werror should use the new flex, because if they don't the build will fail anyway, at any of the other lexers. -- 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 for Null Bitmap Optimization(for Trailing NULLs)
Added to TODO: * Consider not storing a NULL bitmap on disk if all the NULLs are trailing http://archives.postgresql.org/pgsql-hackers/2007-12/msg00624.php http://archives.postgresql.org/pgsql-patches/2007-12/msg00109.php Tom's comments are: What this lacks is some performance testing to measure the cost of the extra tests in heap_form_tuple. If that can be shown to be negligible then it's probably worth doing though I don't like any part of the actually submitted patch ;-). All this should need is a bit more logic in heap_form_tuple and heap_formtuple. --- Gokulakannan Somasundaram wrote: Hi, Currently we check for the existence of NULL values in the tuple and we set the has_null flag. If the has_null flag is present, the tuple will be storing a null bitmap. What i propose is a) By modifying the functions, heap_form_tuple and heap_fill_tuple, we can check whether all the nulls are trailing nulls. If all the nulls are trailing nulls, then we will not set the has_null flag and we will not have the null bitmap with the tuple. b) While selecting the tuple, we will check whether the tuple offset equals / exceeds the length of the tuple and then mark the remaining attributes of the tuple as null. To be exact, we need to modify the slot_deform_tuple in order to achieve the same. This may not give huge performance benefits, but as you may know, it will help is reducing the disk footprint. Expecting your comments.. -- Thanks, Gokul. CertoSQL Project, Allied Solution Group. (www.alliedgroups.com) -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] Better error message for select_common_type()
Am Dienstag, 18. März 2008 schrieb Tom Lane: Or we could apply Peter's patch more or less as-is, but I don't like that. I don't think it solves the stated problem: if you know that CASE branches 3 and 5 don't match, that still doesn't help you in a monster query with lots of CASEs. I think we can and must do better. Yeah, that and the other reason I sort of gave up on this approach is that it is nearly impossible to find some good terminology that works for all callers of select_common_type() (VALUES, UNION, JOIN, IN, CASE, ARRAY, COALESCE, GREATEST, according to my notes). A pointer into the statement would certainly be much nicer. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Problems with compilation of user-defined C functions for PostgreSQL 8.3.0
Hi,
I am trying to create a kind of simple procedural language for PostgreSQL.
The first version was compiled with MinGW/gcc for PostgreSQL 8.2.6 and it
worked OK.
When PostgreSQL 8.3.0 was shipped, I tried to recompile all my code for this
version, but it
didn't work (almost every Postgres call crashed the server with exception
0xC005).
I recompiled the code with msvc++ but without much success - the server still
crashes.
I removed almost all the code from the project and still cannot make it work.
Here's the simplest example:
-
e.c:
#include executor/spi.h
#include fmgr.h
#include funcapi.h
#include postgres.h
#include access/heapam.h
#include catalog/pg_proc.h
#include catalog/pg_type.h
#include commands/trigger.h
#include storage/ipc.h
#include utils/date.h
#include utils/syscache.h
/*
* Compiled and tested only without HAVE_INT64_TIMESTAMP option
*/
#ifdef HAVE_INT64_TIMESTAMP
#error not implemented with HAVE_INT64_TIMESTAMP option
#endif
/*
* Include the 'magic block' that PostgreSQL 8.2 and up will use to ensure
* that a module is not loaded into an incompatible server.
*/
#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif
/*
* Function handler implementation
*/
extern Datum my_call_handler(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(my_call_handler);
Datum my_call_handler(PG_FUNCTION_ARGS)
{
Datum retval = 0;
ereport(LOG, (errmsg(::0)));
PG_TRY();
{
ereport(LOG, (errmsg(::1)));
}
PG_CATCH();
{
ereport(LOG, (errmsg(::2)));
}
PG_END_TRY();
ereport(LOG, (errmsg(::3)));
return retval;
}
-
build.cmd:
@echo off
SET PG_INC=c:\utils\PostgreSQL\src\postgresql-8.3.0\src\include
SET PG_LIB=c:\utils\PostgreSQL\8.3\lib
call c:\Program Files\Microsoft Visual Studio 8\VC\vcvarsall.bat x86
rem include postgres files
SET INCLUDE=%PG_INC%\port\win32_msvc;%INCLUDE%
SET INCLUDE=%PG_INC%\port\win32;%INCLUDE%
SET INCLUDE=%PG_INC%;%INCLUDE%
echo Compiling...
cl /nologo /c /O2 /EHsc /W4 /MD /wd4127 /wd4100 /D ENABLE_THREAD_SAFETY /D
WIN32 /D _WINDOWS /D __WINDOWS__ /D __WIN32__ /D EXEC_BACKEND /D
WIN32_STACK_RLIMIT=4194304 /D BUILDING_DLL /D _CRT_SECURE_NO_DEPRECATE /D
_CRT_NONSTDC_NO_DEPRECATE /D _USE_32BIT_TIME_T e.c
if ERRORLEVEL 1 goto lError
echo Linking...
link kernel32.lib user32.lib advapi32.lib shfolder.lib wsock32.lib
secur32.lib /nologo /subsystem:windows /dll /incremental:no /machine:i386
/NODEFAULTLIB:libcmt.lib /NODEFAULTLIB:uuid.lib /NODEFAULTLIB:OLDNAMES.lib
/NODEFAULTLIB:libc.lib /NODEFAULTLIB:libcmt.lib /NODEFAULTLIB:libcd.lib
/NODEFAULTLIB:libcmtd.lib /NODEFAULTLIB:msvcrtd.lib /MANIFEST:NO
/EXPORT:my_call_handler /OUT:e.dll *.obj %PG_LIB%\postgres.lib
if ERRORLEVEL 1 goto lError
echo Done
:lError
pause
-
test.sql:
DROP FUNCTION my_call_handler() CASCADE;
CREATE FUNCTION my_call_handler()
RETURNS language_handler AS E'e'
LANGUAGE C;
CREATE TRUSTED LANGUAGE e HANDLER my_call_handler;
CREATE FUNCTION e_test() RETURNS int AS
$$
nothing
$$
LANGUAGE e;
SELECT e_test();
When I run test.sql, I get the following output in data\pg_log:
2008-03-18 20:32:59 EET LOG: database system was shut down at 2008-03-18
20:32:56 EET
2008-03-18 20:32:59 EET LOG: database system is ready to accept connections
2008-03-18 20:33:00 EET LOG: autovacuum launcher started
2008-03-18 20:34:19 EET NOTICE: drop cascades to language e
2008-03-18 20:34:19 EET NOTICE: drop cascades to function e_test()
2008-03-18 20:34:19 EET LOG: ::0
2008-03-18 20:34:19 EET STATEMENT: DROP FUNCTION my_call_handler() CASCADE;
CREATE FUNCTION my_call_handler()
RETURNS language_handler AS E'e'
LANGUAGE C;
CREATE TRUSTED LANGUAGE e HANDLER my_call_handler;
CREATE FUNCTION e_test() RETURNS int AS
$$
nothing
$$
LANGUAGE e;
SELECT e_test();
2008-03-18 20:34:23 EET LOG: server process (PID 4084) was terminated by
exception 0xC005
2008-03-18 20:34:23 EET HINT: See C include file ntstatus.h for a
description of the hexadecimal value.
2008-03-18 20:34:23 EET LOG: terminating any other active server processes
2008-03-18 20:34:23 EET WARNING: terminating connection because of crash of
another server process
2008-03-18 20:34:23 EET DETAIL: The postmaster has commanded this server
process to roll back the current transaction and exit, because another server
process exited abnormally and possibly corrupted shared memory.
2008-03-18 20:34:23 EET HINT: In a moment you should be able to reconnect to
the database and
Re: [HACKERS] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Peter Eisentraut wrote: Well, yes. I meant to say, a build system that can supply the functionality of Gen_fmgrtab can surely implement this new thing. I see there is Perl being used, so it should be simple. I was thinking of using a Perl script to generate the dummy header file but decided against it to avoid disrupting the build on other platforms. If sed doesn't work on Windows for some reason, we can use a Perl script instead. Regards, -Robert -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Peter Eisentraut wrote: Am Dienstag, 18. März 2008 schrieb Tom Lane: Well, I've got Leopard here, I'd be happy to test it ... but the patch has rendered http://developer.postgresql.org/pgdocs/postgres/dynamic-trace.html into a pack of lies quite independently of which OSes are supported, so I'm not very sure what to do. Ah yes. Robert, do you think you could update the documentation a bit on how to use the tracing? Yes, the doc needs to be updated. Will submit a patch soon! Regards, -Robert -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Robert Lor wrote: Peter Eisentraut wrote: Well, yes. I meant to say, a build system that can supply the functionality of Gen_fmgrtab can surely implement this new thing. I see there is Perl being used, so it should be simple. I was thinking of using a Perl script to generate the dummy header file but decided against it to avoid disrupting the build on other platforms. If sed doesn't work on Windows for some reason, we can use a Perl script instead. Perl is required for all MSVC builds, and for all builds from CVS on any platform too, IIRC, so it's a fairly safe bet for any tool you provide. 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
Re: [HACKERS] How large file is really large - pathconf results
Zdenek Kotala schrieb: Regarding to discussion about large segment size of table files a test pathconf function (see http://www.opengroup.org/onlinepubs/009695399/functions/pathconf.html). You can see output there: _PC_FILESIZEBITS - 3rd column _PC_LINK_MAX - 4th column _PC_NAME_MAX - 5th column _PC_PATH_MAX - 6th column Solaris NevadaZFS64-12551024 UFS41327672551024 FAT33181024 NFS41327672551024 Solaris 8UFS41327672551024 NFS40327672551024 Centos4(2.6.11)EXT364320002554096 XFS6421474836472554096 Mac OSX leopardHFS+64327672551024 cygwin 1.5 on NTFS. But 1.7 will a have much larger _PC_PATH_MAX. _PC_FILESIZEBITS undefined _PC_LINK_MAX = 8 _PC_NAME_MAX = 260 _PC_PATH_MAX = 257 So this is really bad. -- Reini Urban -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Andrew Dunstan [EMAIL PROTECTED] writes: Robert Lor wrote: I was thinking of using a Perl script to generate the dummy header file but decided against it to avoid disrupting the build on other platforms. If sed doesn't work on Windows for some reason, we can use a Perl script instead. Perl is required for all MSVC builds, and for all builds from CVS on any platform too, IIRC, so it's a fairly safe bet for any tool you provide. The key phrase in that is from CVS. Changing the sed script to Perl would introduce an unacceptable new dependency for builds from tarballs. However, there's nothing wrong with using Perl in the MSVC case, since those scripts need it anyway. 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Tue, 18 Mar 2008 18:14:32 -0400 Tom Lane [EMAIL PROTECTED] wrote: Perl is required for all MSVC builds, and for all builds from CVS on any platform too, IIRC, so it's a fairly safe bet for any tool you provide. The key phrase in that is from CVS. Changing the sed script to Perl would introduce an unacceptable new dependency for builds from tarballs. At some point, do we recognize that Perl is installed on every reasonable platform in existence or can be easily? I mean, we already require autoconf/make etc... What is adding Perl in practice? Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH4ECDATb/zqfZUUQRApTmAJ9+N+vEuBnydMB1ozkg7FMZgBeA9ACgq6fz WJXzPkgvqrSkkzV9vnoMYiM= =n7BS -END PGP SIGNATURE- -- 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: Don't need -Wno-error anymore, because flex is no longer
Peter Eisentraut [EMAIL PROTECTED] writes: I think it is reasonable to assume that those wanting to use -Werror should use the new flex, because if they don't the build will fail anyway, at any of the other lexers. It's been true for quite a long time that only ecpg's lexer generated any warnings on stable flex releases like 2.5.4. The Makefile configuration was not unreasonable. 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] timestamp datatype cleanup
Give the discussion on this. Is this small patch being considered for inclusion? If not, what do I need to change to make it acceptable? Thanks, wt On Sun, Mar 9, 2008 at 1:32 AM, Warren Turkal [EMAIL PROTECTED] wrote: PosgreSQL hackers, Here's an initial bit of my attempt at cleaning up the the timestamp datatype. I have gone through the backend and made a couple small changes to stop using the HAVE_INT64_TIMESTAMP define to select a type in code by creating typedefs in a header and using the typedef in the code. I think this small bit is ready for inclusion for this small bit, but I have a couple questions for further work. 1) Is there a reason that header information is duplicated between normal posgresql include and ecpg includes instead of defining the info in one place and #including it into the files that need it? 2) Would it be reasonable to change timestamp.h into a file that includes other files that define the specific parts depending on HAVE_INT64_TIMESTAMP instead of testing for HAVE_INT64_TIMESTAMP many times throughout timestamp.h? I think this might more cleanly separate the logic for the different timestamp types. Thanks, wt -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] broken head?
I did checking of current head. There is some problems test boolean ... ok test char ... FAILED test name ... ok test varchar ... FAILED test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test txid ... ok test uuid ... ok test enum ... ok test money... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test tstypes ... ok test comments ... ok test geometry ... ok test horology ... ok test oidjoins ... ok test type_sanity ... ok test opr_sanity ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok test copy ... FAILED test copyselect ... ok test foreign_key ... ok test cluster ... ok test dependency ... ok test guc ... ok test combocid ... ok test tsearch ... ok test plancache... ok test limit... FAILED test plpgsql ... FAILED test copy2... ok test temp ... ok test domain ... ok test rangefuncs ... ok test prepare ... ok test without_oid ... ok test conversion ... ok test tsdicts ... ok test truncate ... ok test alter_table ... ok test sequence ... ok test polymorphism ... ok test rowtypes ... ok test returning... ok test largeobject ... FAILED test xml ... ok test stats... ok test tablespace ... FAILED regards Pavel Stehule [EMAIL PROTECTED] ~]$ uname -a Linux nemesis.nat.buk.cvut.cz 2.6.24.3-34.fc8 #1 SMP Wed Mar 12 18:17:20 EDT 2008 i686 i686 i386 GNU/Linux -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Joshua D. Drake wrote: At some point, do we recognize that Perl is installed on every reasonable platform in existence or can be easily? I mean, we already require autoconf/make etc... What is adding Perl in practice? For one thing, a moving target. If we rely on standard Unix tools, we have something fairly stable, if you are careful to read the documentation to omit the occasional GNU extension. But with Perl, you'd have a constant worry, which Perl versions to support and which features that Perl version provides. -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 19 Mar 2008 00:07:09 +0100 Peter Eisentraut [EMAIL PROTECTED] wrote: Joshua D. Drake wrote: At some point, do we recognize that Perl is installed on every reasonable platform in existence or can be easily? I mean, we already require autoconf/make etc... What is adding Perl in practice? For one thing, a moving target. If we rely on standard Unix tools, we have something fairly stable, if you are careful to read the documentation to omit the occasional GNU extension. But with Perl, you'd have a constant worry, which Perl versions to support and which features that Perl version provides. That is certainly reasonable and I have to admit, I don't follow Perl releases as much as I probably should but I can't remember the last time something I would do in standard perl wouldn't work for every version of perl since 5.6. I guess there is a consideration with the imminent, supposedly in our lifetime release of 6. Fair enough. Sincerely, Joshua D. Drake - -- The PostgreSQL Company since 1997: http://www.commandprompt.com/ PostgreSQL Community Conference: http://www.postgresqlconference.org/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL political pundit | Mocker of Dolphins -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFH4EwQATb/zqfZUUQRAueHAJ9+YH28v862MP1nBSk16XH5rMQKmQCgnWm1 UimwrFV0siRHC5JGp0o/xic= =WKN3 -END PGP SIGNATURE- -- 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] pgindent issue with EXEC_BACKEND-only typedefs
I have commited a change to src/tools/find_typedef that should allow it to run under Linux. The only difference I see is that some unused typedefs do not appear in the Linux version. --- Alvaro Herrera wrote: Bruce Momjian wrote: Does someone want to generate that typedef list in the future? I would love to do that, but objdump --stabs does not work for me like find_typedefs says it should. I only get something like: $ objdump --stabs ../backend/postgres ../backend/postgres: file format elf64-x86-64 $ objdump --version GNU objdump (GNU Binutils for Debian) 2.18.20071027 Copyright 2007 Free Software Foundation, Inc. This program is free software; you may redistribute it under the terms of the GNU General Public License version 3 or (at your option) any later version. This program has absolutely no warranty. I don't know how to make it output the symbol names like it seems to do for you. Having the typedef list in the script itself seems like a barrier for other people to contribute to this thing. I wonder if that can be changed so that the typedef is on a separate list. (Why are we still distributing pgjindent anyway?) I am also wondering if current GNU indent is now better suited to the task. Perhaps the bugs that it had on earlier versions have since been fixed? I remember checking the source code size a couple of years ago and it had grown by an order of magnitude or something like that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] pgindent issue with EXEC_BACKEND-only typedefs
Bruce Momjian wrote: I have commited a change to src/tools/find_typedef that should allow it to run under Linux. The only difference I see is that some unused typedefs do not appear in the Linux version. However, I think pgindent only cares about the typedef references, not the definitions, so I think it might be fine --- --- Alvaro Herrera wrote: Bruce Momjian wrote: Does someone want to generate that typedef list in the future? I would love to do that, but objdump --stabs does not work for me like find_typedefs says it should. I only get something like: $ objdump --stabs ../backend/postgres ../backend/postgres: file format elf64-x86-64 $ objdump --version GNU objdump (GNU Binutils for Debian) 2.18.20071027 Copyright 2007 Free Software Foundation, Inc. This program is free software; you may redistribute it under the terms of the GNU General Public License version 3 or (at your option) any later version. This program has absolutely no warranty. I don't know how to make it output the symbol names like it seems to do for you. Having the typedef list in the script itself seems like a barrier for other people to contribute to this thing. I wonder if that can be changed so that the typedef is on a separate list. (Why are we still distributing pgjindent anyway?) I am also wondering if current GNU indent is now better suited to the task. Perhaps the bugs that it had on earlier versions have since been fixed? I remember checking the source code size a couple of years ago and it had grown by an order of magnitude or something like that. -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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] count(*) performance improvement ideas
(Sorry for the repeat email Tom, I forgot the cc's) Tom Lane wrote: Stephen Denne [EMAIL PROTECTED] writes: I initially thought that what you meant by having transactions enter delta entries was that I have a trigger that would create a row each time it was called, writing how many records where inserted or deleted. I didn't understand how this would be much of an improvement, as each of my rows would contain either +1 or -1. Well, ideally you'd aggregate all the deltas caused by a particular transaction into one entry in the counting table. Yes, that's what I was attempting to do, but without changing the application code. Using txid_current() can do that, so each of a large number of individual inserts or deletes within a transaction updates the same delta row for that transaction. I haven't found any references to this being a solution, and thought it was worth recording. However I'm not after a fast count(*) from table, but more like a fast select grouping_id, count(*) from my_table group by grouping_id You could apply the same technique across each group id, though this certainly is getting beyond what any built-in feature might offer. Agreed. I've tried it out now, and am fairly happy with what I've got. Can you clarify the lack of MVCC problems? The point there is that the right answer actually depends on the observer, since each observer might have a different snapshot and therefore be able to see a different set of committed rows in the underlying table. The multiple-delta table handles this automatically, because you can see a delta entry if and only if you could see the underlying-table changes it represents. Does this idea apply with the same efficiency in pre 8.3, non-HOT implementations? I didn't claim it was amazingly efficient in any implementation ;-). HOT in particular is nearly useless since most rows in the count table will never be updated, only inserted and eventually deleted. You might get some mileage on the base row, but that'd be about it. The count table will need frequent vacuums as well as frequent aggregation scans. It should beat scanning a large underlying table, but it's hardly gonna be free. What I was asking about with those questions, is if a single row is inserted in transaction A, and updated 1000 times still within transaction A, then transaction A commits... does a single row version get written, or 1001, 1000 of which are removable? I tested this with a small summary table freshly vacuum full'ed. 10 transactions, one after the other, each transaction inserted three delta rows, and updates one of them three times, and the other two five times each. So 3 inserts and 13 updates per transaction. The updates only affect non-indexed fields in rows created in the same transaction. The next vacuum full found 52 removable row versions. I repeated the test, and got 13 removable row versions. I repeated the test again, and got 13 removable row versions. I repeated just one of the ten transactions, 13 removable row versions. All inserts and updates are probably in the one page that has a fair bit of free space. Is it possible to update the HOT code to re-use row versions on the same page if they were created in the same transaction? Conclusion: making use of txid_current(), I can get single delta rows with deltas of 1, but doing so creates 1 dead row versions. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- 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] broken head?
I am not seeing a problem here. Can you show use regression.diff? --- Pavel Stehule wrote: I did checking of current head. There is some problems test boolean ... ok test char ... FAILED test name ... ok test varchar ... FAILED test text ... ok test int2 ... ok test int4 ... ok test int8 ... ok test oid ... ok test float4 ... ok test float8 ... ok test bit ... ok test numeric ... ok test txid ... ok test uuid ... ok test enum ... ok test money... ok test strings ... ok test numerology ... ok test point... ok test lseg ... ok test box ... ok test path ... ok test polygon ... ok test circle ... ok test date ... ok test time ... ok test timetz ... ok test timestamp... ok test timestamptz ... ok test interval ... ok test abstime ... ok test reltime ... ok test tinterval... ok test inet ... ok test tstypes ... ok test comments ... ok test geometry ... ok test horology ... ok test oidjoins ... ok test type_sanity ... ok test opr_sanity ... ok test insert ... ok test create_function_1... ok test create_type ... ok test create_table ... ok test create_function_2... ok test copy ... FAILED test copyselect ... ok test foreign_key ... ok test cluster ... ok test dependency ... ok test guc ... ok test combocid ... ok test tsearch ... ok test plancache... ok test limit... FAILED test plpgsql ... FAILED test copy2... ok test temp ... ok test domain ... ok test rangefuncs ... ok test prepare ... ok test without_oid ... ok test conversion ... ok test tsdicts ... ok test truncate ... ok test alter_table ... ok test sequence ... ok test polymorphism ... ok test rowtypes ... ok test returning... ok test largeobject ... FAILED test xml ... ok test stats... ok test tablespace ... FAILED regards Pavel Stehule [EMAIL PROTECTED] ~]$ uname -a Linux nemesis.nat.buk.cvut.cz 2.6.24.3-34.fc8 #1 SMP Wed Mar 12 18:17:20 EDT 2008 i686 i686 i386 GNU/Linux -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Bruce Momjian [EMAIL PROTECTED]http://momjian.us EnterpriseDB http://postgres.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + -- 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: new large object API
lo_import_with_oid added. Note that actually committed function signature is: Oid lo_import_with_oid(PGconn *conn, const char *filename, Oid lobjId); -- Tatsuo Ishii SRA OSS, Inc. Japan I have posted proposed patches to pgsql-patches. -- Tatsuo Ishii SRA OSS, Inc. Japan I would like to propose new large object client side API for 8.4. Currently we have: Oid lo_import(PGconn *conn, const char *filename); But we do not have an API which imports a large object specifying the object id. This is inconvenient and inconsistent since we already have lo_create() and lo_open() which allow to specify the large object id. So I propose to add new API: int lo_import_with_oid(PGconn *conn, Oid lobjId, const char *filename); Another idea is changing the signature of lo_import: Oid lo_import(PGconn *conn, Oid lobjId, const char *filename); which will be cleaner but break the backward compatibility. Comments are welcome. -- Tatsuo Ishii SRA OSS, Inc. Japan -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers -- 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] [COMMITTERS] pgsql: Enable probes to work with Mac OS X Leopard and other OSes that
Peter Eisentraut [EMAIL PROTECTED] writes: Joshua D. Drake wrote: What is adding Perl in practice? For one thing, a moving target. Well, if we were that afraid of Perl version discrepancies, we shouldn't be using it at all anywhere. My point was just that we should not add a new build tool dependency without a darn good reason; and something you can easily do in sed doesn't qualify as a darn good reason. 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
Text search selectivity improvements (was Re: [HACKERS] Google Summer of Code 2008)
OK, here's a more detailed description of the FTS selectivity
improvement idea:
=== Write a typanalyze function for column type tsvector
The function would go through the tuples returned by the BlockSampler
and compute the number of times each distinct lexeme appears inside the
tsvectors of those tuples. It would then store the most common lexemes,
along with their frequencies in pg_statistics.
This will likely require adding a new STATISTIC_KIND_* constant, as with
tsvector statistics we won't store the most common values of the
tsvector column based on some kind of equality operator, but rather the
most common lexemes appearing in those tsvectors.
The frequencies will be the fraction of total rows, that contain a
particular lexeme in the tsvector column being analyzed.
Most frequent lexemes would be stored as text[] in stavalues1 and their
frequencies as real[] in stanumbers1.
XXXs:
- Will looking for most common lexemes in just a few sample rows be
enough to do useful selectivity estimation? Maybe the minimum number of
rows returned by the sampler should be raised for this kind of
stat-gathering. Or maybe it should be documented that it is advisable to
SET STATISTICS for a tsvector column to something fairly large if you
are to get good results from the planner.
- There are typically very few or none at all deletes in tables storing
indexed documents. This means that when whe're regularly sampling rows
and computing most common lexemes, maybe we shouldn't throw away the
previous results? Maybe it would be smart to merge previous results
with the freshly obtained? If assume no deletes were made between the
ANALYZEs we could do the maths and do MCV and frequencies estimates
based on that assumption and the previous results.
- Right now there seems to be some duplicate code in
compute_minimal_stats and compute_scalar_stats, maybe this could be
cleaned up as a side effect. The custom typanalyze function would also
need to estimate the number of nonnull entries and the average width of
the column, perheaps these could be made into separate functions and
called from all three places (compute_minimal_stats,
compute_scalar_stats, tsvector_typanalyze).
- Maybe there are other interesting statistics we could collect for
tsvectors, something more fancy than just most common lexemes?
=== Write a selectivity estimation function for the @@ operator ===
The function would look at the tsquery and the statistics gathered by
the function described earlier and return a selectivity estimation based
on them.
For example, given
SELECT * FROM documents WHERE doc_vector @@ to_tsquery('dog')
if the lexeme 'dog' appears among the MCV of doc_vector and has a
frequency of 0.7, we would get a 0.7 returned rows estimation.
Of course this is a very simple example, it'll be much harder than this.
First, the function would have to walk the TSQuery and take it's
structure in consideration. For example
SELECT * FROM documents WHERE doc_vector @@ to_tsquery('!dog')
would have to return a 0.3 estimation (or something more subtle than
just 1 - 0.7?). Same goes for other modifiers like , |.
If no lexemes from the tsquery are among MCV, we return an arbitrary
0.001, as it is done currently for all queries.
=== Deploy these functions ===
This could at first be deployed as a contrib module, that would define
tsvector_typanalyze (or maybe ts_typanalyze, to be consistent with other
ts_* functions) and tsvectorsel and update pg_operator and pg_type so
tsvector would be ANALYZed and @@ restricted with the new method.
So much for the idea, but I might very well have missed some crucial
things that'd have to be done in order to pull this off. Comments,
suggestions, criticism?
Regards,
Jan
--
Jan Urbanski
GPG key ID: E583D7D2
ouden estin
signature.asc
Description: OpenPGP digital signature
[HACKERS] fast count(*) through statistics collector
Hi hackers, I'm novice in PostgreSQL codebase (and in English too :-)), but I'd be glad to make a modest contribution to this great project. By viewing this list, I see a lot of discussions on the problem of fast count (*), but acceptable decision have not been formulated. Well, I make bold to propose own view on the problem. It can be described as: Improve existing infrastructure of statistics collector and use its for caching the number of rows in the table. I plan to do the following steps to implement this principle: 1. Eliminate 500 ms lag between transaction commit and statistics portion transfer from backend to collector. For this purpose pgstat_report_tabstat() function must call in force mode only. We pay therefor increased backend--collector traffic. As some compensation could be invited to send TabStatusArray items, which have not changed with the last shipment. This measure will reduce the size of a messages. I see here one more pitfall: new transaction can start after the changes made earlier transaction became visible for other backends, but before the statistics collector managed to take and process data (despite the forced transfer). To avoid this, one may transfer information before the changes made transaction will be visible, collector, in one's turn, apply this info after that. It is also possible that the use of shared memory instead of pipes will help increase productivity. 2. Eliminate 500 ms lag between recieve statistics portion and write pgstat.stat file. Realize the next todo item for this purpose: Allow statistics collector information to be pulled from the collector process directly, rather than requiring the collector to write a filesystem file twice a second. As an additional effect, we will be able to reduce the burden on I/O channels. 3. Field n_live_tuples of PgStat_StatTabEntry structure now holds the number of inserted - deleted tuples for successful transactions, which are known to collector. But we need field, which would contain the number of inserted - deleted tuples for ALL successful transactions in the history of the table, or it would be undefined (e.g. -1). If n_live_tuples not suited for this role, creating additional field. In any case, I will call this field live_tuples counter below. 4. Values in the live_tuples counters be questioned, if there was any interruption of statistics collection. Therefore, if trac_counts was set to false in cluster-wide or the collector process crash, then live_tuples become undefined for all tables in the cluster. If pg_stat_reset() call, then live_tuples become undefined for all tables in DB. If pg_stat_clear_snapshot() call, or trac_counts set to false during user session, then live_tuples counters should undefine for all tables covered during this transaction/session. If compile such a list of tables is not possible, well, for all tables in DB. 5. If live_tuples counter contain undefined value, but statistics collector work normal, the counter must be restored through first seqscan. I hope that these steps will give us mvcc-compliant counters and overhead cost will increase little. The next step is relatively simple: 6. In the optimizer/plan/planagg.c file add a function similar to optimize_minmax_aggregates () that return null for undefined tuples_count counters (and count(*) determine by regular way through seqscan) or plan for computation such as: PgStat_StatTabEntry.live_tuples+ PgStat_TableCounts.t_new_lived_tuples + PgStat_TableXactStatus.tuples_inserted - PgStat_TableXactStatus.tuples_deleted Restrictions: 1. Uninterrupted supply of statistics collector necessary for efficient use of this algorithm. 2. Works only for simplest queries like: select count (*) from regular_table Any comments are welcome -- regards, Artem Yazkov -- 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] timestamp datatype cleanup
Is there anything I can do to help? wt On Tue, Mar 18, 2008 at 7:49 PM, Tom Lane [EMAIL PROTECTED] wrote: Warren Turkal [EMAIL PROTECTED] writes: Give the discussion on this. Is this small patch being considered for inclusion? If not, what do I need to change to make it acceptable? It's in the to-do queue for the current commit fest. The queue is kinda long however :-( 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] count(*) performance improvement ideas
Tom Lane wrote Stephen Denne [EMAIL PROTECTED] writes: What I was asking about with those questions, is if a single row is inserted in transaction A, and updated 1000 times still within transaction A, then transaction A commits... does a single row version get written, or 1001, 1000 of which are removable? Umm ... AFAICS there is no need for an UPDATE to touch the count table at all. You'd only need ON INSERT and ON DELETE triggers. I'm not referring to updates of my base table... the single row inserted was referring to the delta row... I'm trying to group together a large number of +1 or -1 deltas into a single delta per transaction. A cut down example: CREATE TABLE document_type_summary_detail ( document_type_id integer NOT NULL, txid bigint NOT NULL DEFAULT 0, documents bigint NOT NULL DEFAULT 0, CONSTRAINT pk_document_type_summary PRIMARY KEY (document_type_id, txid) ); CREATE OR REPLACE FUNCTION document_count_trig() RETURNS TRIGGER AS $$ DECLARE BEGIN IF TG_OP = 'INSERT' THEN UPDATE document_type_summary_detail set documents=documents+1 where document_type_id = NEW.document_type_id and txid=txid_current(); IF NOT FOUND THEN INSERT INTO document_type_summary_detail (document_type_id,documents,txid) VALUES(NEW.document_type_id,1,txid_current()); END IF; RETURN NEW; ELSIF TG_OP = 'DELETE' THEN UPDATE document_type_summary_detail set documents=documents-1 where document_type_id = OLD.document_type_id and txid=txid_current(); IF NOT FOUND THEN INSERT INTO document_type_summary_detail (document_type_id,documents,txid) VALUES(OLD.document_type_id,-1,txid_current()); END IF; RETURN OLD; END IF; END; $$ LANGUAGE 'plpgsql'; create trigger document_count_trig before insert or delete on document for each row execute procedure document_count_trig(); --one off setup: insert into document_type_summary_detail (document_type_id,txid,documents) select dt.id, 0, count(d.id) from document_type dt left join document d on d.document_type_id = dt.id group by 1,2; --useful view: CREATE OR REPLACE VIEW document_type_summary AS SELECT document_type_id, sum(documents) AS documents FROM document_type_summary_detail GROUP BY document_type_id; --scheduled cleanup: CREATE OR REPLACE FUNCTION document_type_summary_aggregate() RETURNS void AS $$ BEGIN INSERT INTO document_type_summary_detail(document_type_id) select distinct document_type_id from document_type_summary_detail except select document_type_id from document_type_summary_detail where txid=0; UPDATE document_type_summary_detail set documents=v.documents from document_type_summary as v where document_type_summary_detail.document_type_id = v.document_type_id and document_type_summary_detail.txid=0 and exists (select 1 from document_type_summary_detail ss where ss.document_type_id = document_type_summary_detail.document_type_id and ss.txid 0); DELETE FROM document_type_summary_detail where txid 0; END; $$ LANGUAGE 'plpgsql'; My assumption is that this solves the locking causes serialisation of transactions problem as the only rows updated are those inserted by the same transaction. Regards, Stephen Denne. Disclaimer: At the Datamail Group we value team commitment, respect, achievement, customer focus, and courage. This email with any attachments is confidential and may be subject to legal privilege. If it is not intended for you please advise by reply immediately, destroy it and do not copy, disclose or use it in any way. __ This email has been scanned by the DMZGlobal Business Quality Electronic Messaging Suite. Please see http://www.dmzglobal.com/dmzmessaging.htm for details. __ -- 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] count(*) performance improvement ideas
Stephen Denne [EMAIL PROTECTED] writes: Tom Lane wrote Umm ... AFAICS there is no need for an UPDATE to touch the count table at all. You'd only need ON INSERT and ON DELETE triggers. I'm not referring to updates of my base table... the single row inserted was referring to the delta row... I'm trying to group together a large number of +1 or -1 deltas into a single delta per transaction. Oh. You can't realistically do that from the level of individual ON INSERT/DELETE triggers --- as you've found out, you end up with about the same number of dead rows anyway. Collapsing a transaction's changes into a single row would require keeping some intra-transaction state, which is do-able at the C-code level but not from plpgsql. 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] count(*) performance improvement ideas
Stephen Denne [EMAIL PROTECTED] writes: What I was asking about with those questions, is if a single row is inserted in transaction A, and updated 1000 times still within transaction A, then transaction A commits... does a single row version get written, or 1001, 1000 of which are removable? Umm ... AFAICS there is no need for an UPDATE to touch the count table at all. You'd only need ON INSERT and ON DELETE triggers. 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] timestamp datatype cleanup
Warren Turkal [EMAIL PROTECTED] writes: Give the discussion on this. Is this small patch being considered for inclusion? If not, what do I need to change to make it acceptable? It's in the to-do queue for the current commit fest. The queue is kinda long however :-( 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] [COMMITTERS] Re: pgsql: Add URLs for : * Speed WAL recovery by allowing more than one
Simon Riggs [EMAIL PROTECTED] writes: It may surprise you but I didn't read Tom's words as being against Simon's approach. Personally I read them as a generic warning, which I agreed with. Maybe Tom can straighten that out. AFAIR, I just said that I'd find it hard to trust any complex mechanism that was being used *only* during WAL replay. If we want to invent a pre-reader process, or aio, or whatever, we should try to get it to be exercised during normal use as well. We're far more likely to find the bugs in it that way. 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] count(*) performance improvement ideas
Tom Lane wrote: Stephen Denne [EMAIL PROTECTED] writes: What I was asking about with those questions, is if a single row is inserted in transaction A, and updated 1000 times still within transaction A, then transaction A commits... does a single row version get written, or 1001, 1000 of which are removable? Umm ... AFAICS there is no need for an UPDATE to touch the count table at all. You'd only need ON INSERT and ON DELETE triggers. This returns to the question of whether count of the whole table is useful, or whether count of a GROUP BY or WHERE is useful. If GROUP BY or WHERE is useful, then trigger on UPDATE becomes necessary. What is the direction here? Is it count of the whole table only? (-- not interesting to me) Or count of more practical real life examples, which I completely agree with Greg, that this gets into the materialized view realm, and becomes very interesting. In my current db project, I never count all of the rows in a table. However, I do use count(*) with GROUP BY and WHERE. Cheers, mark -- Mark Mielke [EMAIL PROTECTED]
Re: [PATCHES] [HACKERS] [0/4] Proposal of SE-PostgreSQL patches
For easier reviewing, the most fundamental patch to implement PGACE (PostgreSQL Access Control Extension) was separated into thress parts. I want to start discussion about PGACE security framework at first. Any comments are welcome. [1/3] PGACE core features http://sepgsql.googlecode.com/files/sepostgresql-8.4devel-pgace-1-core.r713.patch It adds the following new files: - src/include/security/pgace.h declares all of PGACE related hooks and functions. - src/backend/security/pgaceHooks.c provides dummy functions to be invoked when no security modules are enabled. They don't affect anything in access control. - src/backend/security/pgaceCommon.c provides common feature for every security modules, including security-attribute system column support, SQL extention, functions to manage security-attribute of large object. [2/3] Security attribute system column http://sepgsql.googlecode.com/files/sepostgresql-8.4devel-pgace-2-security-attr.r713.patch Guest modules of PGACE can associate a security attribute with a tuple. The guest module can utilize this to make its decision in access control. (Unclassified users cannot access 'Secret' tuples, for example) This attribute is stored in the padding field of HeapTupleHeaderData, as oid doing. It requires additional sizeof(Oid) bytes to store it. Users can refer this attribute via system column. The name of new system column is defined as SECURITY_SYSATTR_NAME at include/pg_config.h.in, and the guest module decide its name. In SE-PostgreSQL, it is named as security_context. EXAMPLE of security attribute) postgres=# SELECT security_context, * FROM drink; security_context | id | name | price | alcohol --++---+---+- unconfined_u:object_r:sepgsql_table_t:s0 | 1 | water | 100 | f unconfined_u:object_r:sepgsql_table_t:s0 | 2 | coke | 120 | f unconfined_u:object_r:sepgsql_table_t:s0 | 3 | juice | 130 | f system_u:object_r:sepgsql_table_t:s0:c0 | 4 | cofee | 180 | f system_u:object_r:sepgsql_table_t:s0:c0 | 5 | beer | 240 | t system_u:object_r:sepgsql_table_t:s0:c0 | 6 | sake | 320 | t (6 rows) We can use this security attribute as a target of UPDATE or INSERT statement. It enables DBA to manage security attribute with normal SQL operation. [3/3] PGACE security hooks http://sepgsql.googlecode.com/files/sepostgresql-8.4devel-pgace-3-security-hooks.r713.patch This patch deploys several PGACE hooks on strategic points in PostgreSQL. These hooks invoke a security module mounted on PGACE, and it can make its decision whether this action should be allowed, or not. The list of PGACE hooks at: http://code.google.com/p/sepgsql/wiki/WhatIsPGACE It shouws us more comprehensive specification about what kind of hooks are provided, what informations are given and what value should be returned. NOTE: I categorized patched files into three parts. However, some of them contains security attribute system column facilities and PGACE hooks facilities. In this case, I categorized these files into part 2. Thanks, -- OSS Platform Development Division, NEC KaiGai Kohei [EMAIL PROTECTED] -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
