Re: [HACKERS] PL/PgSQL for counting all rows in all tables.
-Original Message- From: Robert Treat [mailto:[EMAIL PROTECTED] Sent: 12 October 2004 21:21 To: Dave Page Cc: [EMAIL PROTECTED] Subject: Re: [HACKERS] PL/PgSQL for counting all rows in all tables. Maybe I didn't phrase that quite right. How would a user know that he needs to do a real count? For example, if I have a table with est 1 million rows, and I load another 1 million rows into it, wont pgadmin show me 1 million rows until I run an analyze? Even if I run a manual count, wont it show 1 million next time I come into the application, and that time I may not realize that the table is off by 1 million rows so I take the estimated count at face value. Yeah, that's exactly what can happen. If it makes you feel any better about doing it in phppgadmin, then we never got any complaints about it! We simply rely on the fact that it's labelled as an estimate. I can't see that there's any other way around it that doesn't require running a potentially very expensive ANALYZE to update the stats. Regards, Dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] plans for bitmap indexes?
On Sun, 2004-10-10 at 03:36, Chris Browne wrote: There are doubtless cases where the optimizer won't use them where it would be plausible to do so; that suggests, to me, possibilities for enhancing the optimizer. Speaking of which, if anyone has any examples of queries for which we ought to be able to use a partial index but currently cannot, please speak up (or mail me privately). -Neil ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] plans for bitmap indexes?
The most nearly comparable thing is be the notion of partial indexes, where, supposing you had 60 region codes (e.g. - 50 US states, 10 Canadian provinces), you might set up indices thus: For example, imagine you have a table on a dating website with 18 columns representing 18 different characteristics for matching. Imagine that you index each of those columns seperately. If you do: SELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city = 'San Francisco'; I think bitmap indexes do have valid use cases, but partitioned indexes are really a wonderful feature with a lot of use cases, maybe including this one. Workable examples for useful partitioned indexes, that help here are: create index people_male_ix on people (city) where gender = 'male'; create index people_gay_ix on people (city) where orientation = 'gay'; create index people_male_gay_ix on people (city) where gender = 'male' and orientation = 'gay'; Note, that the indexed column differs from the partitioning clause. Note also, that the last index will perform way better than a combo of bitmap indexes. Andreas ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] plans for bitmap indexes?
On K, 2004-10-13 at 00:09, Greg Stark wrote: Josh Berkus [EMAIL PROTECTED] writes: SELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city = 'San Francisco'; There are actually two TODOs here. 1) a bitmap scan that would be usable with any type of index. The tuple locations can be read in for each criteria and sorted by location and built into bitmaps. The results can be combined using bitmap operations and the tuples scanned in physical order. 2) A persistent bitmap index that would enable skipping the first step of the above. In the case if all the columns were btree indexes it might still make sense to scan through all the indexes and combine the results before reading in the actual tuples. Especially if the tuples are very wide and each column individually very unselective, but the combination very selective. However it would work even better if gender and orientation could be stored on disk in a bitmap representation. They're very low cardinality and could be stored quite compactly. The result would read the data faster, skip the sort, and be able to start returning tuples even before it finished reading the entire index. We could go even further and use the same bm indexes for selecting the page where the tuple is stored (found by AND of all bitmap indexes plus fsm) and achieve natural clustering If this is done consistently we need only 1 bit/page in our index (straight bitmap for 1GB fits in 16384 kb or 4 database pages) This approach may result in poor utilisation of database pages for small tables, but one would not use bitmap indexes for small tables in the first place. -- Hannu ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [CYGWIN] [HACKERS] open item: tablespace handing in pg_dump/pg_restore
Leeuw van der, Tim schrieb: There are certainly cygwin-users trying out PostgreSQL on cygwin on WinXX. If the newest cygwin-version will suddenly stop working under WinXX, they will not be happy. That's why we use cygwin symlinks, not junctions. I've given consideration to the argument that you can no longer take data-directories from the cygwin-version to the native-version... And I think that there's not a *huge* loss there. For me, as an observer and occiasional user/developer, I think the loss of not running on cygwin+winXX is larger. After all, the data can still be dumped / reloaded. And what gives me the certainty that the two versions of PostgreSQL, the cygwin and the native version, are not already compiled in such way that they're not binary compatible? (remember, I'm an outsider on this, with no knowledge of the binary formats, and I'm trying to remain in that perspective for this discussion) See below. Conflicting --enable-integer-datetimes and --enable-multibyte would be an issue. I don't know if and how our converters handle multibyte/non-multibyte, when the backend changes. I don't know what the failure will be when you now try to move a data-directory from the cygwin version to the native version, when cygwin uses a .lnk hack and native uses a junction. Did anyone try? What do the results look like? Is there an acceptable way to stop ppl from trying / give sensible errors without introducing too much crap in the code and without harming ppls data? That's a non-critical issue. You can always replace the cygwin .lnk dir with an actual junction on cygwin also. You'd need to be superuser and use ln -d or get junction from sysinternals.com. But than you must have NTFS4 (same drive) or NTFS5 (other local drive). You can also replace the junction with a cygwin .lnk if you switch to FAT, but then you MUST use the cygwin binaries on the data. Or don't use tablespace at all. It's a pretty esoteric feature at all. But it will get problematic on big/little endian machine changes, and different integer sizes. Don't know if the data is converted on the fly then. I only know of AutoCAD's DWG: they designed its data format and accessors to be machine and CPU independent. And you usually don't copy machine dependent /usr/share/postgresql trees to other machines. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of Tom Lane Sent: Tuesday, October 12, 2004 1:02 AM To: Bruce Momjian Cc: Reini Urban; PostgreSQL Developers; [EMAIL PROTECTED] Subject: Re: [CYGWIN] [HACKERS] open item: tablespace handing in pg_dump/pg_restore Bruce Momjian [EMAIL PROTECTED] writes: OK, I have applied the following patch that uses Cygwin native symlink() instead of the Win32 junctions. The reason for this is that Cygwin symlinks work on Win95/98/ME where junction points do not and we have no way to know what system will be running the Cygwin binaries so the safest bet is to use the Cygwin versions. On Win32 native we only run on systems that support junctions. I think this is probably a net loss, because what it will mean is that you cannot take a data directory built under a Cygwin postmaster and use it under a native postmaster, nor vice versa. Given the number of other ways in which we do not support pre-NT4 Windows systems, what is the benefit of allowing this one? -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] plans for bitmap indexes?
Josh Berkus schrieb: The most nearly comparable thing is be the notion of partial indexes, where, supposing you had 60 region codes (e.g. - 50 US states, 10 Canadian provinces), you might set up indices thus: I'm afraid that you're mistaken about the functionality of bitmap indexes. The purpose of a bitmap index is not to partition an index, but to allow multiple indexes to be used in the same operation. uh. sorry! In my first harsh replay I didn't know that. I thought you wanted a new index type for binary images in BLOBS. (just some hash, maybe optimized for image similarity) For example, imagine you have a table on a dating website with 18 columns representing 18 different characteristics for matching. Imagine that you index each of those columns seperately. If you do: SELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city = 'San Francisco'; ... then the planner can use an index on orientation OR on gender OR on city, but not all three. Multicolumn indexes are no solution for this use case because you'd have to create a multicolumn index for each possible combo of two or three columns ( 18! ). The Bitmap index allows the query executor to use several indexes on the same operation, comparing them and selecting rows where they overlap like a Venn diagram. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] plans for bitmap indexes?
Zeugswetter Andreas DAZ SD [EMAIL PROTECTED] writes: Workable examples for useful partitioned indexes, that help here are: create index people_male_ix on people (city) where gender = 'male'; create index people_gay_ix on people (city) where orientation = 'gay'; create index people_male_gay_ix on people (city) where gender = 'male' and orientation = 'gay'; Note, that the indexed column differs from the partitioning clause. Note also, that the last index will perform way better than a combo of bitmap indexes. This is definitely a useful technique in some cases, but it's got its limits. You have to have only a fairly small number of interesting conditions (else the number of indexes gets out of hand) and those conditions have to be spelled out explicitly in the query. That is, the last index will indeed work for SELECT * FROM people WHERE orientation = 'gay' AND gender = 'male' AND city = 'San Francisco'; but it will not work for SELECT * FROM people WHERE orientation = $1 AND gender = $2 AND city = $3; which is the sort of thing that the planner is increasingly going to have to deal with. Combining bitmaps at runtime is certainly somewhat more expensive to execute, but it can deal with cases where the specific values being searched for are not known until runtime. regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] more dirmod CYGWIN
Bruce Momjian schrieb: Great, just glad we could get it all working. ... Just that regression suite stopped working a while ago :( That's by far more serious than the tiny build patches. http://archives.postgresql.org/pgsql-hackers/2004-09/msg00252.php http://archives.postgresql.org/pgsql-hackers/2004-10/msg00193.php (contains a bad analysis) http://archives.postgresql.org/pgsql-hackers/2004-10/msg00236.php (contains a better description) http://archives.postgresql.org/pgsql-hackers/2004-09/msg00259.php (fails also, but is not related) http://archives.postgresql.org/pgsql-hackers/2004-10/msg00411.php Not related to any patches I posted. Probably related to the fixes you made to make plperl work? Between 4.Sep and 10.Sep. Around that time. 10.Sep was my first hang, but that was the day when I did the cvs up against my 4.Sep release for cygwin. Looks like a strange memory problem to me. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Two-phase commit security restrictions
What kind of security restrictions do we want for prepared transactions? Who has the right to finish a transaction that was started by user A? At least the original user, I suppose, but who else? Under what account is the transaction manager typically going to run? A separate TM account perhaps? Do we need a GRANT TRANSACTION command to give permission to finish 2PC transcations? Another approach I've been thinking about is to allow anyone that knows the (user-supplied) global transaction identifier to finish the transaction, and hide the gids of running transactions from regular users. That way, the gid acts as a secret token that's only known by the transaction manager, much like the cancel key. - Heikki ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[HACKERS] Why we still see some reports of could not access transaction status
Having seen a couple recent reports of could not access status of transaction for old, not-obviously-corrupt transaction numbers, I went looking to see if I could find a way that the system could truncate CLOG before it's really marked all occurrences of old transaction numbers as known-dead or known-good. I found one. The problem is that there are several places where a tqual.c routine is called without checking to see if it changed the tuple's commit hint bits, and without necessarily writing the page immediately after. One example is the code path in heap_update where we decide that we can't update the tuple because a concurrent transaction did so. If HeapTupleSatisfiesUpdate had set the XMIN_COMMITTED or XMAX_COMMITTED bits, those bits would remain set in the shared buffer, but *the buffer would not get marked dirty*. Before PG 7.2 this was not a bug, because the hint bits could always be set again later. But now, consider this scenario: while the buffer remains in memory, VACUUM passes over the table. It doesn't find any changes needed in that page, so it doesn't write the page either. At completion of the vacuum, we check whether we can truncate CLOG, discover we can, and do so. At some later point, the in-memory buffer is discarded, still without having been written. When next read in, the page contains an un-hinted transaction status that could easily point to a transaction before the new CLOG boundary. Ooops. The odds of such a problem seem exceedingly small ... in other words, just about right to explain the small numbers of reports we get. I think what we ought to do to solve this problem permanently is to stop making the callers of the HeapTupleSatisfiesFoo() routines responsible for checking for hint bit updates. It would be a lot safer, and AFAICS not noticeably less efficient, for those routines to call SetBufferCommitInfoNeedsSave for themselves. This would require adding to their parameter lists, because they aren't currently told which buffer the tuple is in, but that's no big deal considering we get to simplify the calling logic in all the places that are faithfully doing the t_infomask update check. Comments? regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Two-phase commit security restrictions
Heikki Linnakangas [EMAIL PROTECTED] writes: What kind of security restrictions do we want for prepared transactions? Who has the right to finish a transaction that was started by user A? At least the original user, I suppose, but who else? I would say the original user, any superuser, and nobody else. This conforms to Postgres' usual practices (compare to the right to DROP an object). Do we need a GRANT TRANSACTION command to give permission to finish 2PC transcations? Overkill. Another approach I've been thinking about is to allow anyone that knows the (user-supplied) global transaction identifier to finish the transaction, and hide the gids of running transactions from regular users. Security-by-obscurity isn't really security, and I think that hiding the GIDs is likely to make things noticeably more painful to manage. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Two-phase commit security restrictions
Heikki Linnakangas wrote: What kind of security restrictions do we want for prepared transactions? Who has the right to finish a transaction that was started by user A? At least the original user, I suppose, but who else? Under what account is the transaction manager typically going to run? A separate TM account perhaps? Do we need a GRANT TRANSACTION command to give permission to finish 2PC transcations? Another approach I've been thinking about is to allow anyone that knows the (user-supplied) global transaction identifier to finish the transaction, and hide the gids of running transactions from regular users. That way, the gid acts as a secret token that's only known by the transaction manager, much like the cancel key. Personally I prefer the last. It should be infeasible to crack as long as the gid is long enough (e.g. sufficiently random 128bit value or more) and the channel between the TM and Postgres is secure. The problem is, we cannot guarantee that a TM will generate a good random gid, or even a long enough one. (But then a good TM should assume that RM doesn't have any protection on global transactions and thus generate a good secret-like gid). Does the XA standard regulate about this security issue? -- dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] plans for bitmap indexes?
Andreas, I think bitmap indexes do have valid use cases, but partitioned indexes are really a wonderful feature with a lot of use cases, Sure, no question. That's why we have them. maybe including this one. Nope, not at all. Workable examples for useful partitioned indexes, that help here are: create index people_male_ix on people (city) where gender = 'male'; create index people_gay_ix on people (city) where orientation = 'gay'; create index people_male_gay_ix on people (city) where gender = 'male' and orientation = 'gay'; You've forgotten part of my premise (based on a real case I discussed on IRC) that there are EIGHTEEN criteria columns. That would require, by the method you have above, roughly 18(3rd factorial) indexes, times the number of values allowed by each column, which if it averaged, say 5 values, would be 24,480 indexes. A little impractical, hmmm? I think that might even break a system limit somewhere. Tom, Note that what Josh is describing is not really a distinct index type, but a different way of using an index: that is, you pull candidate tuple locations from several indexes and intersect or union those sets before you go to the heap. In principle this works whatever the index access methods are. Yes, exactly.They're known as bitmap indexes because that's how Oracle implemented them, and AFAIK only Oracle currently has anything analogous. I'd personally be interested in any scheme that allowed the relatively efficient usage of multiple indexes on a single operation. BTW, Tom, I was talking to Sean last night and he was saying that our current planner cost calculations assume that a 2-column index fetch will be twice as expensive as a 1-column index fetch. Is this right? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] plans for bitmap indexes?
Josh Berkus [EMAIL PROTECTED] writes: BTW, Tom, I was talking to Sean last night and he was saying that our current planner cost calculations assume that a 2-column index fetch will be twice as expensive as a 1-column index fetch. Is this right? No. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Hypothetical Indexes
Marcos, http://www.inf.puc-rio.br/~postgresql/ There you will find a link to a tutorial based description of the hypothetical indexes feature we have implemented on PostgreSQL 7.4 beta 3. I would love to see this as an add-in project on pgFoundry. Particularly since your online tutorial only works in Internet Explorer, so I can't read it. I believe that there was/is a team exploring a set of utilities to produce database optimization hints for the admin. Your idea would dovetail nicely with that. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Two-phase commit security restrictions
On Wed, Oct 13, 2004 at 11:58:21PM +0700, David Garamond wrote: Heikki Linnakangas wrote: Another approach I've been thinking about is to allow anyone that knows the (user-supplied) global transaction identifier to finish the transaction, and hide the gids of running transactions from regular users. That way, the gid acts as a secret token that's only known by the transaction manager, much like the cancel key. Personally I prefer the last. It should be infeasible to crack as long as the gid is long enough (e.g. sufficiently random 128bit value or more) and the channel between the TM and Postgres is secure. So it is possible for a user connected to the DB to send random commit or cancel commands, just in case she happens to hit a valid GID? -- Alvaro Herrera (alvherre[a]dcc.uchile.cl) La realidad se compone de muchos sueños, todos ellos diferentes, pero en cierto aspecto, parecidos... (Yo, hablando de sueños eróticos) ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] open item: tablespace handing in pg_dump/pg_restore
OK, I have applied the following patch that uses Cygwin native symlink() instead of the Win32 junctions. The reason for this is that Cygwin symlinks work on Win95/98/ME where junction points do not Is this really a Win95/98/ME vs NT distinction or a FAT32 vs NTFS distinction? In which case does an NT machine that happens to be using a FAT32 file system have the same problem? I believe it is OS, not file system. No, it's file system. Junctions will not work on NT OS:es with FAT32. Directory junctions require NTFSv5, which is only available on Windows 2000 and newer. My point was that junctions don't exist on Win98 no matter what file system you use. I don't think the API even exists on those OS's, never mind what file system you have. The API exists - it's just a call to DeviceIOControl(). That one exists on all win32 platforms. It will return a not supported error value, but the API is there. (Unlike some other APIs where the program won't even start because the entrypoint is not present in the system DLLs) The only reason they are not in 9x is that 9x does not support NTFS. The function is implemented only in the filesystem, not in the OS itself. //Magnus ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] Two-phase commit security restrictions
Heikki Linnakangas wrote: What kind of security restrictions do we want for prepared transactions? Who has the right to finish a transaction that was started by user A? At least the original user, I suppose, but who else? Do we not require transaction initiation and conclusion to happen in the same session, so that the transaction could be considered a session-local object? -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Two-phase commit security restrictions
On Wed, 13 Oct 2004, Peter Eisentraut wrote: Heikki Linnakangas wrote: What kind of security restrictions do we want for prepared transactions? Who has the right to finish a transaction that was started by user A? At least the original user, I suppose, but who else? Do we not require transaction initiation and conclusion to happen in the same session, so that the transaction could be considered a session-local object? No. After we have told the transaction manager that the transaction has been successfully prepared, we must not lose it. Otherwise we break the atomicity of the global transaction. Successful preparation is a promise to the TM that we will be able to finish the 2nd phase commit later, no matter what. - Heikki ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] more dirmod CYGWIN
So those URL's are all fixed? Would you post your remaining failures? --- Reini Urban wrote: Bruce Momjian schrieb: Great, just glad we could get it all working. ... Just that regression suite stopped working a while ago :( That's by far more serious than the tiny build patches. http://archives.postgresql.org/pgsql-hackers/2004-09/msg00252.php http://archives.postgresql.org/pgsql-hackers/2004-10/msg00193.php (contains a bad analysis) http://archives.postgresql.org/pgsql-hackers/2004-10/msg00236.php (contains a better description) http://archives.postgresql.org/pgsql-hackers/2004-09/msg00259.php (fails also, but is not related) http://archives.postgresql.org/pgsql-hackers/2004-10/msg00411.php Not related to any patches I posted. Probably related to the fixes you made to make plperl work? Between 4.Sep and 10.Sep. Around that time. 10.Sep was my first hang, but that was the day when I did the cvs up against my 4.Sep release for cygwin. Looks like a strange memory problem to me. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED]) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Two-phase commit security restrictions
Heikki Linnakangas wrote: Another approach I've been thinking about is to allow anyone that knows the (user-supplied) global transaction identifier to finish the transaction, and hide the gids of running transactions from regular users. That way, the gid acts as a secret token that's only known by the transaction manager, much like the cancel key. Doesn't this break recovery? The TM needs to find all outstanding GIDs for a particular resource. I guess if we treated (database + authenticated user) as the equivalence key for XAResources (XAResource.isSameRM() in Java-speak) it might work. Then only transactions initiated by the current user need to be visible. Either way, it seems necessary to have some way for recovery to get the set of GIDs that are in doubt and the current user has permission to resolve. Otherwise the TM is going to get confused when it tries to resolve a transaction that appears to be needing recovery but it does not have permission to resolve. -O ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Two-phase commit security restrictions
Heikki, Another approach I've been thinking about is to allow anyone that knows the (user-supplied) global transaction identifier to finish the transaction, and hide the gids of running transactions from regular users. That way, the gid acts as a secret token that's only known by the transaction manager, much like the cancel key. I agree with Tom; this is just inviting trouble. Restrict it to the original user and the superuser.Since any 3rd-party transaction manager (such as CJDBC) would use a single user to manage the transactions, I don't see that this restriction is a problem. -- --Josh Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] implementing another hash join...
Hi, Im new to postgres and I'm about to implement the algorithm double pipelined hash join. I've started using SPI functions but soon I realised that I 'm not at the right way (am I?). Is there a way that I'm not obliged to change code of the optimizer of postgres but to write a seperate code as a new function?I've been told by my supervisor that I should use the function that is doing the hashing of a relation. There is a chaos Please help!!I'm reading the documentation of postgres and some '.c .h' files (mainly the createplan.c at the directory ~/src/backend/optimizer/plan/) to figer out what to do and from where to start. Regards Martha ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] implementing another hash join...
Martha Chronopoulou [EMAIL PROTECTED] writes: Im new to postgres and I'm about to implement the algorithm double pipelined hash join. I've started using SPI functions but soon I realised that I 'm not at the right way (am I?). Not even close. You need to be implementing new executor node types (see backend/executor) and teaching the planner about the properties of this join method. SPI is at a completely different level of abstraction ... Is there a way that I'm not obliged to change code of the optimizer of postgres Nope. The set of join types it knows about is pretty much hard-wired. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [HACKERS] Two-phase commit security restrictions
Alvaro Herrera wrote: Another approach I've been thinking about is to allow anyone that knows the (user-supplied) global transaction identifier to finish the transaction, and hide the gids of running transactions from regular users. That way, the gid acts as a secret token that's only known by the transaction manager, much like the cancel key. Personally I prefer the last. It should be infeasible to crack as long as the gid is long enough (e.g. sufficiently random 128bit value or more) and the channel between the TM and Postgres is secure. So it is possible for a user connected to the DB to send random commit or cancel commands, just in case she happens to hit a valid GID? It is not essentially different from someone trying to bruteforce a password. A 128bit value like a random GUID is as strong as a 16 char password comprising ASCII 0-255 characters. And I would argue that this is _not_ security through obscurity. Security through obscurity is relying on unpublished methods/algorithms. This is not. But I understand that everybody seems to be against this idea. -- dave ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [HACKERS] Two-phase commit security restrictions
David Garamond wrote: So it is possible for a user connected to the DB to send random commit or cancel commands, just in case she happens to hit a valid GID? It is not essentially different from someone trying to bruteforce a password. A 128bit value like a random GUID is as strong as a 16 char password comprising ASCII 0-255 characters. And I would argue that this is _not_ security through obscurity. Security through obscurity is relying on unpublished methods/algorithms. This is not. You have no guarantees that GIDs generated by an external transaction manager are random. An obvious implementation is TM-identity plus sequence number, which is very predictable. -O ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] more dirmod CYGWIN
I have added the attached patch to allow Cygwin /contrib compiles. I am a little confused why Cygwin requires -lpgport and no other platform does, but it is in the Cygwin-specific section so we can always improve it later if we find the cause. Thanks. --- Reini Urban wrote: Bruce Momjian schrieb: Reini Urban wrote: Bruce Momjian schrieb: I have applied all parts of your patch now. Thanks. Core builds and works fine now. (plperl IPC problems aside) But there's are still some more minor SHLIB glitches, which only affects contrib, because -lpgport is missing for various dll's. FYI, I think we fixed plperl for Win32 today. !! good to hear. I will come with my promised basic plperl regressiontests soon. No time at all yet. SHLIB_LINK doesn't contain the libs only the paths, because they are filtered out somewhere. But first I want to find the real cause of the problem. Maybe LIB is just missing a -lpgport. Would you please post the link command and error that is failing below: well, all dll contrib's which use pgport functions miss -lpgport. ltree, spi, tsearch, tsearch2, ... make[1]: Entering directory `/usr/src/postgresql/postgresql-8.0.0cvs/contrib/ltree' sed 's,MODULE_PATHNAME,$libdir/ltree,g' ltree.sql.in ltree.sql gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o ltree_io.o ltree_io.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o ltree_op.o ltree_op.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o lquery_op.o lquery_op.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o _ltree_op.o _ltree_op.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o crc32.o crc32.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o ltxtquery_io.o ltxtquery_io.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o ltxtquery_op.o ltxtquery_op.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o ltree_gist.o ltree_gist.c gcc -g -fno-strict-aliasing -Wall -Wmissing-prototypes -Wmissing-declarations -DLOWER_NODE -I. -I.. /../src/include -c -o _ltree_gist.o _ltree_gist.c dlltool --export-all --output-def ltree.def ltree_io.o ltree_op.o lquery_op.o _ltree_op.o crc32.o ltxtquery_io.o ltxtquery_op.o ltree_gist.o _ltree_gist.o dllwrap -o ltree.dll --dllname ltree.dll --def ltree.def ltree_io.o ltree_op.o lquery_op.o _ltree_op.o crc32.o ltxtquery_io.o ltxtquery_op.o ltree_gist.o _ltree_gist.o ../../src/utils/dllinit.o -L../../src/port -L/usr/local/lib -L../../src/backend -lpostgres lquery_op.o(.text+0x1a4): In function `checkLevel': /usr/src/postgresql/postgresql-8.0.0cvs/contrib/ltree/lquery_op.c:94: undefined reference to `_pg_strncasecmp' ltxtquery_op.o(.text+0x1b6): In function `checkcondition_str': /usr/src/postgresql/postgresql-8.0.0cvs/contrib/ltree/ltxtquery_op.c:57: undefined reference to `_pg_strncasecmp' collect2: ld gab 1 als Ende-Status zuruck dllwrap: gcc exited with status 1 make[1]: *** [libltree.a] Fehler 1 make[1]: Leaving directory `/usr/src/postgresql/postgresql-8.0.0cvs/contrib/ltree' I still have to live with the attached patch, which will give then: make[1]: Entering directory `/usr/src/postgresql/postgresql-8.0.0cvs/contrib/ltree' dlltool --export-all --output-def ltree.def ltree_io.o ltree_op.o lquery_op.o _ltree_op.o crc32.o ltxtquery_io.o ltxtquery_op.o ltree_gist.o _ltree_gist.o dllwrap -o ltree.dll --dllname ltree.dll --def ltree.def ltree_io.o ltree_op.o lquery_op.o _ltree_op.o crc32.o ltxtquery_io.o ltxtquery_op.o ltree_gist.o _ltree_gist.o ../../src/utils/dllinit.o -L../ ../src/port -L/usr/local/lib -L../../src/backend -lpostgres -lpgport dlltool --dllname ltree.dll --def ltree.def --output-lib libltree.a make[1]: Leaving directory `/usr/src/postgresql/postgresql-8.0.0cvs/contrib/ltree' make -C src ok make -C contrib ok make check MAX_CONNECTIONS=5 ... hangs as reported today in parallel schedule of create_misc. INSERT INTO iportaltest (i, d, p) VALUES (2, 89.05, '(4.0,2.0),(3.0,1.0)'::polygon); hangs ... until Cancel request sent FATAL: terminating connection due to administrator command I'll investigate why. -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ --- postgresql-8.0.0cvs/src/Makefile.shlib.orig 2004-09-03 01:06:43.0 +0200 +++
Re: [HACKERS] more dirmod CYGWIN
Bruce Momjian schrieb: I have added the attached patch to allow Cygwin /contrib compiles. I am a little confused why Cygwin requires -lpgport and no other platform does, but it is in the Cygwin-specific section so we can always improve it later if we find the cause. thanks. duplicate does not harm. I tell you when I'll find the real culprit. I thought I knew it last month, but the LDFLAGS / LIBS issue (adding all libs to LDFLAGS) is already fixed now. Index: src/Makefile.shlib === RCS file: /cvsroot/pgsql/src/Makefile.shlib,v retrieving revision 1.83 diff -c -c -r1.83 Makefile.shlib *** src/Makefile.shlib 13 Oct 2004 09:51:47 - 1.83 --- src/Makefile.shlib 13 Oct 2004 10:17:36 - *** *** 216,221 --- 216,223 ifeq ($(PORTNAME), cygwin) shlib = $(NAME)$(DLSUFFIX) + # needed for /contrib modules, not sure why + SHLIB_LINK += -lpgport endif ifeq ($(PORTNAME), win32) -- Reini Urban http://xarch.tu-graz.ac.at/home/rurban/ ---(end of broadcast)--- TIP 8: explain analyze is your friend
