Re: [HACKERS] Truncate Permission
Nick Barr <[EMAIL PROTECTED]> writes: > I was looking to start development on the following TODO entry. > Add a separate TRUNCATE permission Is there actually a use-case for that? It seems like mostly pointless complication to me. (Note that in the role world, one can effectively have a table owned by a group, so the real issue here is only whether there is a point in letting someone truncate a table without having any other owner-grade permissions for it.) regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Truncate Permission
Hi, I was looking to start development on the following TODO entry. Add a separate TRUNCATE permission Currently only the owner can TRUNCATE a table because triggers are not called, and the table is locked in exclusive mode. Does anyone have any objections? Looks like there is a change freeze on at the moment, so I assume my patch will not get reviewed until 8.4 starts development? Now some functional questions... 1. I assume you want something like the following? grant truncate on [table] bla to user; revoke truncate on [table] bla from user; Are there any other statements that need to be included? 2. When executing a truncate command, the owner permission check is replaced by a truncate privilege check. Would you prefer both privileges to be checked? 3. Can I reuse the old ACL_RULE bit position and display character? src/include/nodes/parsenodes.h - 1<<4 src/include/utils/acl.h - character 'R' Will this break dump/restores? I would have preferred to use 't' or 'T' but these are both used. 4. Should the truncate permission be contained within the all privileges? If a user does grant all [privileges] on [table] bla to user; revoke all [privileges] on [table] bla from user; If everyone is too busy at the moment with the 8.3 release, let me know and I will resubmit this stuff in a couple of months when things have calmed down. Ta Nick ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Avoiding legal email signatures
I know we have talked about how to avoid legal email signatures on this list. One idea would be for a small percentage of our users to ignore emails with a legal signature. I know I am less likely to reply to such an email. If enough people do that, it might coerce people to avoid them, and perhaps we could put something in the FAQ about it. -- Bruce Momjian <[EMAIL PROTECTED]> http://momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[HACKERS] Updatable cursors thoughts
I've been looking at the updatable-cursors patch http://archives.postgresql.org/pgsql-patches/2007-05/msg00264.php which attempts to implement the SQL-spec UPDATE/DELETE WHERE CURRENT OF syntax. It's pretty much of a mess, but there are some salvageable ideas. There are two big things I don't like about it: one is the parsetree representation (fails to reverse-list nicely) and the other is its assumption that updatable cursors must have FOR UPDATE specified. That is not required by the SQL spec AFAICS, and since our version of FOR UPDATE is not really compatible with the spec (they say it lists columns, we say it lists tables), I don't want to require its use in a patch that is supposedly moving towards spec compliance. Another objection is that the patch seriously reduces the previously available functionality of FOR UPDATE by requiring that a cursor mentioning FOR UPDATE be simply updatable (ie, no joins or grouping). That would cause compatibility problems for existing apps. What I think we could do instead is not change any existing behavior of cursor declarations, but when WHERE CURRENT OF is used, dig through the execution node tree of the cursor to find the scan node for the target table. The "dig" would refuse to descend below join or grouping nodes, and thus implement the restriction that the cursor be simply updatable. This also means that the digging would be cheap enough that it wouldn't be a performance bottleneck. This would be enough to implement SQL92's notion of updatability. SQL2003 has a more complex notion of updatability that can allow updating of join members in some cases. I'm not sure whether execution-tree examination could be extended to handle that, but I suspect that the practical use-cases for WHERE CURRENT OF don't need it anyway. In any case the currently proposed patch hasn't got any clear path to supporting that either. As far as parsetree representation goes, I'm thinking of having the grammar generate a "CurrentOfExpr" node type that just carries the target cursor name, and let that propagate as-is through parse analysis. In the planner or maybe rewriter, expand it to target_table.ctid = pg_current_of_cursor("cursor name", target_table.tableoid) where pg_current_of_cursor is a function (name,oid) returning TID. It would look up the cursor's portal, dig down to find the scan node, and return the CTID of the tuple currently stored in the scan node's scan tuple slot. Passing the tableoid is necessary to handle inheritance cases --- the cursor plan could involve an Append and we have to be able to tell which Append member to look at. Also, the function has to return NULL (preventing a match) when invoked for some other member of the inheritance tree than the one currently being scanned. The reason for the CurrentOfExpr node representation before planning is to have something that ruleutils.c can reverse-list into WHERE CURRENT OF, instead of some nonstandard comparison on ctid. Comments? regards, tom lane ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Command tags in create/drop scripts
On Fri, Jun 08, 2007 at 08:12:22PM -0500, Jim C. Nasby wrote: > On Tue, Jun 05, 2007 at 05:52:39PM -, Andrew Hammond wrote: > > On Jun 5, 9:19 am, [EMAIL PROTECTED] (Alvaro Herrera) wrote: > > > Zdenek Kotala wrote: > > > > Tom Lane wrote: > > > > >Bruce Momjian <[EMAIL PROTECTED]> writes: > > > > >>Is this a TODO? > > > > > > > >I don't think so; there is no demand from anybody but Zdenek to remove > > > > >those programs. Has it ever even come up before? > > > > > > Personally I found really strange to have "createuser" and "createdb" > > > shipped by Postgres when I started using it. I just didn't complain. > > > > +1. Given the prevalence of the pg_foo convention, those names are > > clunky. So is initdb. I'm less creative than Zdenek, so I'd suggest > > simply renaming to pg_createuser and friends with the same command > > line options as the originals. Have the binaries check $0 and emit a > > warning about using the deprecated name to STDERR if called by a name > > that doesn't have the pg_ prefix. Default to symlinking the old names > > for backwards compatibility until 9.0. > > +1 +1 It's a lot easier just to prefix the names than to do something "clever." Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! Consider donating to PostgreSQL: http://www.postgresql.org/about/donate ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Tsearch vs Snowball, or what's a source file?
Teodor Sigaev <[EMAIL PROTECTED]> writes: > 2 Snowball's compiling infrastructure doesn't support Windows target. Yeah. Another problem with using their original source code is that running the Snowball compiler during build would not work for cross-compiled builds of Postgres, at least not without solving the problem of building some code for the host platform instead of the target. So what I'm thinking now is we should import libstemmer instead of the snowball_code representation. I haven't gotten as far as thinking about exactly how to lay out the files though. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Tsearch vs Snowball, or what's a source file?
1 Compiling from .sbl by original Snowball's makefile requires Perl and doesn't work cleanly: AFAIK, you don't receive any answers on your email in snowball mailing list. 2 Snowball's compiling infrastructure doesn't support Windows target. 3 I understand your wish about including only real sources and snowball_code.tgz contains some extra data (doc, examples) which isn't needed for tsearch & pgsql 4 Your suggest to simplify support of snowball by using original file's layout from Snowball For that reasons, we suggest to put into source tree (in src/snowball) only three directory for snowball_code.tgz: - /compiler - compiler from *.sbl to *.c - /runtime - common code for all stemmers - /algorithms - *.sbl files and use pgsql's makefile infrastructure to compiling stemmers. Comments, objections? -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] COPYable logs status
Tom Lane wrote: Markus Schiltknecht <[EMAIL PROTECTED]> writes: Tom Lane wrote: We *have* a log-writing process. The problem is in getting the data to it. Remember the imessages approach I'm using for Postgres-R? It passes messages around using shared memory and signals the receiver on incoming data. It's not perfect, sure, but it's a general solution to a common problem. Uh-huh. And how will you get libc's dynamic-link code to buy into issuing link error messages this way? Not to mention every other bit of code that might get linked into the backend? I was refering to the 'getting data to another process' problem. If that's the problem (as you said upthread) imessages might be a solution. Trapping what comes out of stderr is simply too useful a behavior to lose. Sure. I've never said anything against that. Regards Markus ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Issues with factorial operator
"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > So at the very least the documentation is confusing: > The type numeric can store numbers with up to 1000 digits of precision > and perform calculations exactly. This documentation is outright wrong. The grain of truth behind the statement is that the parser won't let you declare numeric(N) columns with N > 1000. But unconstrained numeric can be a lot larger. The hard limit of the format seems to be 10^128K. I agree that a CHECK_FOR_INTERRUPTS in numeric_fac wouldn't be a bad idea, and we can reject arguments that are clearly going to overflow. regards, tom lane ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Command tags in create/drop scripts
"Joshua D. Drake" <[EMAIL PROTECTED]> writes: > Jim C. Nasby wrote: >> On Tue, Jun 05, 2007 at 05:52:39PM -, Andrew Hammond wrote: >>> +1. Given the prevalence of the pg_foo convention, those names are >>> clunky. So is initdb. I'm less creative than Zdenek, so I'd suggest >>> simply renaming to pg_createuser and friends with the same command >>> line options as the originals. Have the binaries check $0 and emit a >>> warning about using the deprecated name to STDERR if called by a name >>> that doesn't have the pg_ prefix. Default to symlinking the old names >>> for backwards compatibility until 9.0. >> >> +1 > +1 I like this better than the original proposal, too. Doing more than this seems like more work and more confusion for users than the problem justifies. regards, tom lane ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Binary mode copy in from stdin
"Dann Corbit" <[EMAIL PROTECTED]> writes: > Why was it decided that binary mode is not allowed for stdin or stdout? Works fine when using V3 frontend protocol. The old protocol for it isn't 8-bit-clean. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] Using the GPU
On Sat, June 9, 2007 07:36, Gregory Stark wrote: > "Billings, John" <[EMAIL PROTECTED]> writes: > >> Does anyone think that PostgreSQL could benefit from using the video >> card as a parallel computing device? I'm working on a project using >> Nvidia's CUDA with an 8800 series video card to handle non-graphical >> algorithms. I'm curious if anyone thinks that this technology could be >> used to speed up a database? If so which part of the database, and what >> kind of parallel algorithms would be used? > > There has been some interesting research on sorting using the GPU which > could be very interesting for databases. > Perhaps this can be done using OpenGL already but I kind of doubt it. GPUs have been used to great effect for spatial joins. And yes, using OpenGL so that it was portable. I saw a paper about that as an Oracle plugin a few years back. It works something like this, IIRC: a spatial join looks for objects that overlap with the query area. Normally you go through an R-tree index to identify objects that are in the same general area (space-filling curves help there). Then you filter the objects you get, to see which ones actually overlap your query area. The GL trick inserted an intermediate filter that set up the objects found in the R-tree index, and the query area, as 3D objects. Then it used GL's collision detection as an intermediate filter to find apparent matches. It has to be slightly conservative because GL doesn't make the sort of guarantees you'd want for this trick, so there's a final software pass that only needs to look at cases where there's any doubt. Jeroen ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] COPYable logs status
"Matthew T. O'Connor" <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> We *have* a log-writing process. The problem is in getting the data to it. > By that I assume you mean the bgwriter, I thought that was for WAL data, No, I'm talking about src/backend/postmaster/syslogger.c regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] COPYable logs status
Markus Schiltknecht <[EMAIL PROTECTED]> writes: > Tom Lane wrote: >> We *have* a log-writing process. The problem is in getting the data to it. > Remember the imessages approach I'm using for Postgres-R? It passes > messages around using shared memory and signals the receiver on incoming > data. It's not perfect, sure, but it's a general solution to a common > problem. Uh-huh. And how will you get libc's dynamic-link code to buy into issuing link error messages this way? Not to mention every other bit of code that might get linked into the backend? Trapping what comes out of stderr is simply too useful a behavior to lose. regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] Using the GPU
Gregory Stark wrote: "Billings, John" <[EMAIL PROTECTED]> writes: Does anyone think that PostgreSQL could benefit from using the video card as a parallel computing device? I'm working on a project using Nvidia's CUDA with an 8800 series video card to handle non-graphical algorithms. I'm curious if anyone thinks that this technology could be used to speed up a database? If so which part of the database, and what kind of parallel algorithms would be used? There has been some interesting research on sorting using the GPU which could be very interesting for databases. Without knowing a thing about all of this, my first thought it might be useful for GIS and things of that sort. regards, Lukas ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [HACKERS] Issues with factorial operator
yeah, simple and correct, I like that. :-) 2007/6/9, Dann Corbit <[EMAIL PROTECTED]>: > -Original Message- [snip] > Hum... I think there is a little improvement: when n is too large,(say > n>10, 000) we can use Stirling's formula to get the estimated value of > n!:-) Or (rather) the log base 10 of Stirling's formula. The n! estimator will overflow for sure, unless we take the log of it. Rather than all that, why not just figure out what the largest number of digits we will allow is and then don't allow inputs that will generate more than that. The program I gave could be run with the target accuracy as the break out of the loop and then the test would be: factorial( n) { if (n > CONSTANT_PRECOMPUTED_LIMIT) return NULL; else { return compute_actual_factorial(n); } } ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] COPYable logs status
Hi, Tom Lane wrote: We *have* a log-writing process. The problem is in getting the data to it. Remember the imessages approach I'm using for Postgres-R? It passes messages around using shared memory and signals the receiver on incoming data. It's not perfect, sure, but it's a general solution to a common problem. Maybe it's worth a thought, instead of fiddling with signals, special shmem areas and possible races every time the 'getting data to another process'-problem comes up? Regards Markus ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] Issues with factorial operator
> -Original Message- [snip] > Hum... I think there is a little improvement: when n is too large,(say > n>10, 000) we can use Stirling's formula to get the estimated value of > n!:-) Or (rather) the log base 10 of Stirling's formula. The n! estimator will overflow for sure, unless we take the log of it. Rather than all that, why not just figure out what the largest number of digits we will allow is and then don't allow inputs that will generate more than that. The program I gave could be run with the target accuracy as the break out of the loop and then the test would be: factorial( n) { if (n > CONSTANT_PRECOMPUTED_LIMIT) return NULL; else { return compute_actual_factorial(n); } } ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] Issues with factorial operator
2007/6/9, Dann Corbit <[EMAIL PROTECTED]>: #include double log10nfactorialestimate(unsigned n) { unsignedi; double estimate = 0; for (i = 1; i < n; i++) estimate += log10(n); return estimate; } #ifdef UNIT_TEST #include #include int main(void) { clock_t start, end; double answer; start = clock(); end = clock(); answer = log10nfactorialestimate(92838278); printf("log 10 of 92838278! is pretty close to %g and took %g seconds\n", answer, (end - start) / (1.0 * CLOCKS_PER_SEC)); return 0; } #endif /* C:\tmp>cl /W4 /Ox /DUNIT_TEST log10EST.C Microsoft (R) 32-bit C/C++ Optimizing Compiler Version 14.00.50727.42 for 80x86 Copyright (C) Microsoft Corporation. All rights reserved. log10EST.C Microsoft (R) Incremental Linker Version 8.00.50727.42 Copyright (C) Microsoft Corporation. All rights reserved. /out:log10EST.exe log10EST.obj C:\tmp>log10est log 10 of 92838278! is pretty close to 7.3971e+008 and took 0 seconds */ Hum... I think there is a little improvement: when n is too large,(say n>10, 000) we can use Stirling's formula to get the estimated value of n!:-) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] GiST intarray rd-tree indexes using intbig
It is documented that intbig utilizes 4096 bit signatures to represent the set nodes in the tree. However, I am unable to find any reference to a 4kbit signature in the code and am wondering where this is implemented. _int.h: /* bigint defines */ #define SIGLENINT 63 /* >122 => key will toast, so very slow!!! */ #define SIGLEN ( sizeof(int)*SIGLENINT ) #define SIGLENBIT (SIGLEN*BITS_PER_BYTE) 63 /*ints*/ * 4 /* byte per int */ * 8 /* bits per byte */ = 2016 bits From our experience, using power of 2 number of bits causes bad hashing of array. You can play with value of SIGLENINT: - less value decreases index's size, but increase number of false drops, so index will return more values and pgsql will drop it after rechecking of table's value. That increases table's access - greater value increase index's size but decrease number of false drops. So, you can find optimal SIGLENINT value for your sets of data. Also, is the leaf comparison also a signature comparison like the nodes? Or is this an exact comparison? From my understanding of the What do you mean: comparison of signatures? RD-Tree doesn't use any comparison functions like B-Tree does. Here we use distance function. Distance might be defined in different meaning, but we use Hemming distance (_intbig_gist.c:hemdistsign) which is number of different bits in signatures. code, it doesn’t appear to be an exact comparison. If this is the case, how can I access the original intarray that is being referenced by this signature? Index doesn't store original int[] at all. From GiST support fuction there is no way to get access to table's value :(. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Controlling Load Distributed Checkpoints
On Thu, Jun 07, 2007 at 10:16:25AM -0400, Tom Lane wrote: > Heikki Linnakangas <[EMAIL PROTECTED]> writes: > > Thinking about this whole idea a bit more, it occured to me that the > > current approach to write all, then fsync all is really a historical > > artifact of the fact that we used to use the system-wide sync call > > instead of fsyncs to flush the pages to disk. That might not be the best > > way to do things in the new load-distributed-checkpoint world. > > > How about interleaving the writes with the fsyncs? > > I don't think it's a historical artifact at all: it's a valid reflection > of the fact that we don't know enough about disk layout to do low-level > I/O scheduling. Issuing more fsyncs than necessary will do little > except guarantee a less-than-optimal scheduling of the writes. If we extended relations by more than 8k at a time, we would know a lot more about disk layout, at least on filesystems with a decent amount of free space. -- Jim Nasby [EMAIL PROTECTED] EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) pgpD131BxuJOC.pgp Description: PGP signature
Re: [HACKERS] Using the GPU
2007/6/9, Gregory Stark <[EMAIL PROTECTED]>: There has been some interesting research on sorting using the GPU which could be very interesting for databases. However I think Postgres would be unlikely to go the route of having compiled driver code for every possible video card. It's unlikely to be interesting for database developers until there's some abstract interface designed for these kinds of optimizations which it can use without caring about the specific graphics card. Perhaps this can be done using OpenGL already but I kind of doubt it. http://en.wikipedia.org/wiki/GLSL> There are (of course) competing "standards" such as: http://en.wikipedia.org/wiki/High_Level_Shader_Language> and: http://en.wikipedia.org/wiki/Cg_%28programming_language%29>. greetings, Nicolas -- Nicolas Barbier http://www.gnu.org/philosophy/no-word-attachments.html ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[HACKERS] GiST intarray rd-tree indexes using intbig
Hello all, I am working on a search-related project where scaling is a major issue. Recently I've been experimenting with the beautifully designed rd-tree indexes and intarray contrib module, and it seems like a great solution for me. I've hit a few bumps and am looking for clarification from anyone familiar with the implementation of the intbig versions of intarray. It is documented that intbig utilizes 4096 bit signatures to represent the set nodes in the tree. However, I am unable to find any reference to a 4kbit signature in the code and am wondering where this is implemented. Also, is the leaf comparison also a signature comparison like the nodes? Or is this an exact comparison? From my understanding of the code, it doesn't appear to be an exact comparison. If this is the case, how can I access the original intarray that is being referenced by this signature? Thanks in advance. I have spent many hours digging through code (and deciphering macros) and need a bit of help to get a better understanding before I can move forward. Jonathan Gray