Re: [HACKERS] [JDBC] Where are we on stored procedures?
Hi, Francisco, Francisco Figueiredo Jr. schrieb: In fact, I think people keep requesting me support on Npgsql for that because MS Sql server supports it and they are porting their code to use Postgresql and facing that difficult. Indeed, for (a) we could use an approach similar to Ms sql server. We could have a way of the procedure say if it wanted to send the rows affected information or not. And about (b) I think that it is only on trivial cases that people really want to use this feature :) As you need to tweak the function/procedure source to implement (a), you can also tweak the function/procedure to return the row cound and whatever other diagnostics you need. For newly implemented stored procedures, we could create a special diagnostics result set that every procedure creates. This usually contains some success/error information, but the procedure could add additional rows to it. Markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature
Re: [JDBC] [HACKERS] Where are we on stored procedures?
Hi, Tom, Tom Lane schrieb: Yeah, but only because you have to do it explicitly. I was wondering whether we couldn't bury that mechanism under the hood. (In particular, given the improved support in 8.0 for anonymous record types, we could in theory have the backend invent a record type on-the-fly to match whatever list of OUT parameters a particular function has.) It would not be necessarily on the fly, at least in the first step we possibly get away with declaraing the returned tuples at creation time and implicitly creating those tuple types. The declaration could be like returns (touchedrows int, somethingelse datetime), setof (article int, description text) for a function/method that has two resultsets, one of those with always one row. markus -- markus schaber | dipl. informatiker logi-track ag | rennweg 14-16 | ch 8001 zürich phone +41-43-888 62 52 | fax +41-43-888 62 53 mailto:[EMAIL PROTECTED] | www.logi-track.com signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [JDBC] Statement Timeout and Locking
Hi, Tom, Tom Lane schrieb: Anyway the short-term answer for Markus is don't do it that way. We ought to think about making the backend's behavior more consistent, though. I'll split the query into three. Having it in one query just was a convenience here. Thanks, Markus ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] [PERFORM] Bad n_distinct estimation; hacks suggested?
Hi, Josh, Josh Berkus wrote: Yes, actually. We need 3 different estimation methods: 1 for tables where we can sample a large % of pages (say, = 0.1) 1 for tables where we sample a small % of pages but are easily estimated 1 for tables which are not easily estimated by we can't afford to sample a large % of pages. If we're doing sampling-based estimation, I really don't want people to lose sight of the fact that page-based random sampling is much less expensive than row-based random sampling. We should really be focusing on methods which are page-based. Would it make sense to have a sample method that scans indices? I think that, at least for tree based indices (btree, gist), rather good estimates could be derived. And the presence of a unique index should lead to 100% distinct values estimation without any scan at all. Markus ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index
Hi, Ron, Ron wrote: ...and of course if you know enough about the data to be sorted so as to constrain it appropriately, one should use a non comparison based O(N) sorting algorithm rather than any of the general comparison based O(NlgN) methods. Sounds interesting, could you give us some pointers (names, URLs, papers) to such algorithms? Thanks a lot, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index
Hi, Mark, Mark Lewis schrieb: It seems that instead of maintaining a different sorting code path for each data type, you could get away with one generic path and one (hopefully faster) path if you allowed data types to optionally support a 'sortKey' interface by providing a function f which maps inputs to 32- bit int outputs, such that the following two properties hold: f(a)=f(b) iff a=b if a==b then f(a)==f(b) Hmm, to remove redundancy, I'd change the = to a and define: if a==b then f(a)==f(b) if ab then f(a)=f(b) Data types which could probably provide a useful function for f would be int2, int4, oid, and possibly int8 and text (at least for SQL_ASCII). With int2 or some restricted ranges of oid and int4, we could even implement a bucket sort. Markus ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create Index
Hi, David, David Lang schrieb: In SQL_ASCII, just take the first 4 characters (or 8, if using a 64-bit sortKey as elsewhere suggested). The sorting key doesn't need to be a one-to-one mapping. that would violate your second contraint ( f(a)==f(b) iff (a==b) ) no, it doesn't. When both strings are equal, then the first characters are equal, too. If they are not equal, the constraint condition does not match. The first characters of the strings may be equal as f(a) may be equal to f(b) as to the other constraint. Markus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] qsort again (was Re: [PERFORM] Strange Create
Hi, Ron, Ron schrieb: OK, so here's _a_ way (there are others) to obtain a mapping such that if a b then f(a) f (b) and if a == b then f(a) == f(b) Pretend each row is a integer of row size (so a 2KB row becomes a 16Kb integer; a 4KB row becomes a 32Kb integer; etc) Since even a 1TB table made of such rows can only have 256M - 512M possible values even if each row is unique, a 28b or 29b key is large enough to represent each row's value and relative rank compared to all of the others even if all row values are unique. By scanning the table once, we can map say 001h (Hex used to ease typing) to the row with the minimum value and 111h to the row with the maximum value as well as mapping everything in between to their appropriate keys. That same scan can be used to assign a pointer to each record's location. But with a single linear scan, this cannot be accomplished, as the table contents are neither sorted nor distributed linearly between the minimum and the maximum. For this mapping, you need a full table sort. That initial scan to set up the keys is expensive, but if we wish that cost can be amortized over the life of the table so we don't have to pay it all at once. In addition, once we have created those keys, then can be saved for later searches and sorts. But for every update or insert, you have to resort the keys, which is _very_ expensive as it basically needs to update a huge part of the table. Markus ---(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
Re: [HACKERS] [PERFORM] Looking for a tool to * pg tables as ERDs
Hi, Ron,
Ron Peacetree wrote:
Where * ==
{print | save to PDF | save to mumble format | display on screen}
Anyone know of one?
psql with fancy output formatting comes to my mind, or COPY table TO
file SQL command.
HTH,
Markus
--
Markus Schaber | Logical TrackingTracing International AG
Dipl. Inf. | Software Development GIS
Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [HACKERS] [PERFORM] Looking for a tool to * pg tables as ERDs
Hi, Andrew, Andrew Dunstan wrote: How on earth can either of these have to do with producing an ERD? Sorry, the ERD thing got lost in my mind while resolving the *. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [SQL] Interval subtracting
Hi, Scott, Scott Marlowe wrote: But it isn't '-2 months, -1 day'. I think what you are saying is what I am saying, that we should make the signs consistent. Pretty much. It just seems wrong to have different signs in what is essentially a single unit. We don't say 42 degrees, -12 minutes when measuring arc, do we? Then again, maybe some folks do. It just seems wrong to me. But we say quarter to twelve, at least in some areas on this planet. The problem is that months have different lengths. '2 months - 1 day' can be '1 month 27 days', '1 month 28 days', '1 month 29 days' or '1 month 30 days', depending on the timestamp we apply the interval. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
Hi, PFC, PFC wrote: The problem is that you need a set-returning function to retrieve the values. SRFs don't have rowcount estimates, so the plans suck. What about adding some way of rowcount estimation to SRFs, in the way of: CREATE FUNCTION foo (para, meters) RETURNS SETOF bar AS $$ ... function code ... $$ LANGUAGE plpgsql ROWCOUNT_ESTIMATOR $$ ... estimation code ... $$ ; Internally, this could create two functions, foo (para, meters) and estimate_foo(para, meters) that are the same language and coupled together (just like a SERIAL column and its sequence). The estimator functions have an implicit return parameter of int8. Parameters may be NULL when they are not known at query planning time. What do you think about this idea? The same scheme could be used to add a CPUCOST_ESTIMATOR to expensive functions. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PERFORM] Big IN() clauses etc : feature proposal
Hi, PFC, PFC wrote: You need to do some processing to know how many rows the function would return. Often, this processing will be repeated in the function itself. Sometimes it's very simple (ie. the function will RETURN NEXT each element in an array, you know the array length...) Sometimes, for functions returning few rows, it might be faster to compute the entire result set in the cost estimator. I know, but we only have to estmiate the number of rows to give a hint to the query planner, so we can use lots of simplifications. E. G. for generate_series we return ($2-$1)/$3, and for some functions even constant estimates will be good enough. - please execute me and store my results in a temporary storage, count the rows returned, and plan the outer query accordingly That's an interesting idea. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] [PERFORM] Big IN() clauses etc : feature proposal
Hi, Nils, Nis Jorgensen wrote: It will probably be quite common for the number to depend on the number of rows in other tables. Even if this is fairly constant within one db (some assumption), it is likely to be different in others using the same function definition. Perhaps a better solution would be to cache the result of the estimator function. Sophisticated estimator functions are free to use the pg_statistics views for their row count estimation. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] large object regression tests
Hi, Jeremy, Jeremy Drake wrote: I am considering, and I think that in order to get a real test of the large objects, I would need to load data into a large object which would be sufficient to be loaded into more than one block (large object blocks were 1 or 2K IIRC) so that the block boundary case could be tested. Is there any precedent on where to grab such a large chunk of data from? You could generate such data on the fly, as part of the test scripts. E. G. a blob of zero bytes, blob of 0xff bytes, a blob of pseudo random data... Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Fixed length data types issue
Hi, Tom, Tom Lane wrote: The only way we could pack stuff without alignment is to go over to the idea that memory and disk representations are different --- where in this case the conversion might just be a memcpy to a known-aligned location. The performance costs of that seem pretty daunting, however, especially when you reflect that simply stepping over a varlena field would require memcpy'ing its length word to someplace. AFAICS, PostGIS already uses this approach internally, mostly because its current format requires a mix of byte-sized and larger (int, double) fields. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Proposal for GUID datatype
Hi, Gevik, Gevik Babakhani wrote: typreceive = not supported typsend = not supported Any reason why you don't want to support binary transmissions? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] dump / restore functionality
Hi, Tom, Tom Lane wrote: AFAIR what was discussed was separating - schema stuff needed before loading data - table data - schema stuff needed after loading data where the last category boils down to indexes and then foreign keys. All the other stuff such as functions really needs to be in the first part ... or at least there's no visible benefit to delaying loading it. I agree, it has to be in the first part, especially as data types and input functions needed for the table definitions and table data may be defined therein. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in EU! www.ffii.org www.nosoftwarepatents.org ---(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] Getting a move on for 8.2 beta
Hi, Jeremy, Jeremy Drake wrote: Another possibility would be to test these patches in some kind of virtual machine that gets blown away every X days, so that even if someone did get something malicious in there it wouldn't last long. Or just have a snapshot which is reverted after each run, and read-only access to files used to do the build. I know vmware supports this, probably others too... A chroot / fakeroot combined with unionfs should do the same, probably with less effort. There are other user-mode jail projects that also block networking. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Fixed length data types issue
Hi, Jim, Jim Nasby wrote: I'd love to have the ability to control toasting thresholds manually. This could result in a lot of speed improvements in cases where a varlena field isn't frequently accessed and will be fairly large, yet not large enough to normally trigger toasting. An address field would be a good example. Being able to force a field to be toasted before it normally would could drastically improve tuple density without requiring the developer to use a 'side table' to store the data. Sounds good. But I remember that the query planner underestimated sequential scans when lots of TOAST data was in the table. IIRC, The specific case (that was discussent on pgperform) was about 70 PostGIS geometries, amounting to about 35MB of TOAST data and only 2 or 3 pages in the actual table. The query planner used an sequential scan instead of an GIST index scan ( operator), leading to deTOASTing and processing all 35 MB of geometries, instead of just those 2 small ones that matched the index condition. So I think before we start toasting more, we should check whether the query planner could be affected negatively. It should have statistics about TOAST data, and then see whether he'd need to detoast for condition checking and for actual data fetching. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [ADMIN] Vacuum error on database postgres
Hi, Tom, Tom Lane wrote: We could add another LockTagType just for ANALYZE, but that seems like rather a lot of infrastructure to support an extremely narrow corner case, namely two people doing database-wide ANALYZE at the same time inside transaction blocks. (If they do it outside a transaction block then the ANALYZE is divided into multiple xacts and so doesn't try to hold locks on multiple tables concurrently. autovacuum won't try to do that either.) Is there any reason to allow ANALYZE run insinde a transaction at all? Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [ADMIN] Vacuum error on database postgres
Hi, Andrew, Andrew - Supernews wrote: Is there any reason to allow ANALYZE run insinde a transaction at all? Absolutely. In a large transaction that radically changes the content of the database, it is often necessary to analyze in order to avoid getting extremely bad query plans for later commands in the transaction. OK, I see. But this leads to the danger that, should the transaction abort afterwards, we're left with borked stats, or are those rolled back accordingly? Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] minor feature request: Secure defaults during
Hi, Martijn, Martijn van Oosterhout wrote: Someone writing SECURITY DEFINER in their function definition has to be understood to know what they're doing. After all, chmod +s doesn't reset global execute permissions either, because that would be far too confusing. The same applies here IMHO. The whole point is to be executed by other users. But I have the possibility to chmod a-x before chmod +s the file. Maybe we should add [NOT] PUBLICLY EXCUTABLE[1] keywords to CREATE FUNCTION, with the default being the current behaviour for now (possibly configurable). Add an appropriate note in the docs for CREATE FUNCTION, so users are informed about the security implications. [1] alternative spelling proposals: [NOT] PUBLIC or PUBLIC | PRIVATE Thinking about it, CREATE [OR REPLACE] [PUBLIC|PRIVATE] FUNCTION ... seems the most sexy variant in my eyes. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Truncation of email subject lines
Hi, Bruce, Bruce Momjian wrote: Should I try hacking my mail reader to prevent this? I think I see where it is happening in the code. AFAICT, the wrapping of long header lines by indentation (as your mailer seems to do) is RFC conformant, so I think it is majordomo who needs the fix. The only possible bug I could see is that your mailer implements the indentation incorrectly (tabs vs. spaces, incorrect level of indentation etc.). Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Truncation of email subject lines
Hi, Bruce, Markus Schaber wrote: Should I try hacking my mail reader to prevent this? I think I see where it is happening in the code. AFAICT, the wrapping of long header lines by indentation (as your mailer seems to do) is RFC conformant, so I think it is majordomo who needs the fix. The only possible bug I could see is that your mailer implements the indentation incorrectly (tabs vs. spaces, incorrect level of indentation etc.). I just re-read http://www.faqs.org/rfcs/rfc2822.html and it seems that the first character of the continuation has to be a space or tab, so I assume that your mailer wors correctly. Btw, header lines have a limit of 998 characters, so, for longer subjects, wrapping them is a must. :-) HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [pgsql-www] [HACKERS] Developer's Wiki
Hi, Martijn, Martijn van Oosterhout wrote: 2. I can see the official todo list being in CVS, which gives it all the access protection it needs. A wiki todo list can stay where it is, just that it's not official. [I've just made a reference to the TODO list in CVS from the wiki, that should help]. Maybe you should rename the public writable Wiki page list to Wishlist instead of Todo, to make the difference more explicit. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] [PATCHES] Patch for UUID datatype (beta)
Hi, Mark, [EMAIL PROTECTED] wrote: The versions that include a MAC address, time, and serial number for the machine come pretty close, presuming that the user has not overwritten the MAC address with something else. It's unique at manufacturing time. Not even that is guaranteed. I remember that, about 8 years ago, me and a co-student bought a cheap network starting kit each, containing two network kards and a crossover cable. Now, it turned out, that the first cards in both packages had the same mac address, and the second cards as well, so we could not network together using proper cabling and a hub. Luckily, the mac address was flashable in an eeprom, and so my friend fixed his hards with those from two 10 MBit Coax cards we had abandoned in favour of the new twisted pair network. AFAIR, in the end it turned out that the whole charge of cards was manufactured this way. Officially, it was a bug in the eeprom content generating software, but there were rumours that the manufacturer wanted to avoid paying the registration fees for the mac address ranges... Just gettin' off topic, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Index bloat problem in 7.4
Hi, Dave, Dave Cramer wrote: I am aware that more recent versions 8.x have fixed this problem, I checked the 7.4 release notes but can't see if any of the fixes made it into 7.4. Usually, only critical data loss and security fixes are put into the minor updates (e. G. 7.4.0 to 7.4.13). So, if it's not in the release notes, it is unlikely that it dit make it into the 7.4 releases. You may try backporting it yourself, or use a cronjob or such issuing regular REINDEX commands during the night (or whenever you have idle hours). HTH; Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Cause of moving-target FSM space-needed reports
Hi, Tom, Tom Lane wrote: I think it's reasonable for vacuumlazy.c to track at most MaxFSMPages pages as it's doing now --- but it should keep a separate count of the total number of pages with at least threshold amount of free space, and pass that as a separate argument to RecordRelationFreeSpace. This will not take any more space in shared memory than we already use, but it will allow us to report a truthful value for number of pages needed, which we clearly are failing to do now. It might also be a good idea if vacuum verbose reported this page count, since when you've got a single table bloated like this, VACUUM FULL or CLUSTER might be a more appropriate solution than increasing the FSM size --- but there's no way to know which rel is the problem from the FSM total. In fact, maybe vacuum should just throw a WARNING when it finds a single rel with more than MaxFSMPages pages with useful free space? +1 for both from my side, it has bitten me and our admins several times now. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Index bloat problem in 7.4
Hi, Alvaro, Alvaro Herrera wrote: I am aware that more recent versions 8.x have fixed this problem, I checked the 7.4 release notes but can't see if any of the fixes made it into 7.4. Usually, only critical data loss and security fixes are put into the minor updates (e. G. 7.4.0 to 7.4.13). So, if it's not in the release notes, it is unlikely that it dit make it into the 7.4 releases. Except it was solved in 7.4: Allow B-tree index compaction and empty page reuse (Tom) Yes, you're right, the change is listed on http://www.postgresql.org/docs/7.4/interactive/release-7-4.html So, as it _is_ in the release notes, this does not render my statement wrong, but pointless :-) Thanks for your correction, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] PostgreSQL 8.2beta1 w/ VALUES
Hi, Luke, Luke Lonergan wrote: If it's going to roll back the entire load after that one warning, it should terminate there. AFAIK, a warning is no reason for PostgreSQL to roll back anything. That's the difference between a warning and an error. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] pgsql: We're going to have to spell dotless i
Hi, Hannu, Hannu Krosing wrote: Are you sure it's UCS-4 ? I've always thought that XML is what is given in xml tag, and utf-8 if no charset is given. You have to distinguish between the supported charset, and the document encoding. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] pgsql: We're going to have to spell dotless i
Hi, Hannu, Hannu Krosing wrote: Are you sure it's UCS-4 ? I've always thought that XML is what is given in xml tag, and utf-8 if no charset is given. You have to distinguish between the supported charset, and the document encoding. UCS-4 and UTF-8 are both encodings for UNICODE see: http://en.wikipedia.org/wiki/UTF-32 Yes, I know. The Point I wanted to make was that the document encoding is independent from the allowed charset (except having to be a subset). That is what XML entities were defined for. So even in an document using LATIN-1 as encoding, the charset still is Unicode, giving us the possibility to use entities; to use non-latin1 characters. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] pgsql: We're going to have to spell dotless i
Hi, Bruce, Bruce Momjian wrote: I don't think that any of our SGML documentation is actually in UCS-4 encoding. The source files use nothing beyond plain ASCII (and should remain that way, IMHO) so there isn't any need to inquire very far into exactly what the toolchain thinks the document encoding is. The issue at hand here is what the *output* character set is, which is to say the document character set if I have the jargon right. That is the space over which we are permitted to use -entities. Just for reference, if we could support UTF8, I was hoping to add non-Latin names as alternates to the ASCII versions, so we could have Japanese and Russian-lettered names in the release notes. I thought it would be a nice touch. We don't need UTF8 encoding for this. It's also possible using ASCII encoding + #4711; entities. But we need the Charset to be Unicode. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
[HACKERS] DELETE RETURNING
Hi, I just read the docs about DELETE RETURNING in 8.2, and a small idea arised: INSERT INTO logtable DELETE * FROM lifetable WHERE date'2006-01-01' RETURNING *; Will this work as expected? It might be a good example to put into the docs then. If not, it may be worth the effort to make it work in 8.3. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
[HACKERS] Small docu mismatch
Hi, http://developer.postgresql.org/pgdocs/postgres/release-8-2.html tells: Add pg_dump -X no-data-for-failed-tables option to suppress loading data if table creation failed (the table already exists) (Martin Pitt) However, http://developer.postgresql.org/pgdocs/postgres/app-pgdump.html seem not to mention this option. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] DELETE RETURNING
Hi, Dave, Dave Cramer wrote: I just read the docs about DELETE RETURNING in 8.2, and a small idea arised: INSERT INTO logtable DELETE * FROM lifetable WHERE date'2006-01-01' RETURNING *; Will this work as expected? What is your expected result here ? It would return all the rows that were deleted ? Yes. It would be shorthand for moving rows between tables, faster than first using INSERT INTO .. SELECT and then DELETE afterwards, as it saves at least on table scan. The question is whether INSERT INTO only allows SELECT as data source, or every query returning a ResultSet. I don't see any usecases for using UPDATE RETURNING and INSERT RETURNING as data source for INSERT INTO yet, especially as UPDATE RETURNING returns the new versions of the rows. And I see that the same behaviour could be achieved with triggers, but with much higher overhead for non-regular tasks. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] DROP FUNCTION IF EXISTS
Hi, Csaba, Csaba Nagy wrote: dbval=# select 1; ERROR: column 1 does not exist The full story is that I typed 'ü' (u-umlaut if it won't render correctly) and backspace before the '1'. I guess the backspace will delete byte-wise and will so fail to delete properly multi-byte characters. I have no idea if this is a problem of psql or some other problem, and it was not annoying enough to report it... I think this could be a problem with libreadline / libedit not being utf8-safe. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Release Notes: Major Changes in 8.2
Hi, Bruce, Bruce Momjian wrote: listitem para Allow inheritance to be removed from tables /para /listitem I'd enhance that to Allow table inheritance relationships to be defined for and removed from pre-existing tables. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Release Notes: Major Changes in 8.2
Hi, Bruce, Bruce Momjian wrote: Allow inheritance to be removed from tables I'd enhance that to Allow table inheritance relationships to be defined for and removed from pre-existing tables. Good point. Updated wording: Allow table inheritance to be added and removed from pre-existing tables Agree, that's excellent. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Internal Transaction
Hi, Marlon, Marlon Petry wrote: would like to know postgres implements ACID ? has some document ? http://www.postgresql.org/docs/8.1/interactive/transaction-iso.html HTH, Schabi -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] jar in repository
Hi, Strk, strk wrote: Markus, I noticed we have a .jar file in the repository. Why is that ? Can't we build it from sources with free software tools ? It was a mistake when chekcing in the EJB3 code. It's just a copy of the normal postgis.jar that's needed to compile the EJB3 code, it was part of the archive file when I unpacked, and I forgot to ignore it when checking in. It's removed now, together with a clarification in the README. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Ignore that mail (was: [HACKERS] jar in repository)
Hi, Markus Schaber wrote: [something about a postgis.jar] Please ignore that mail, it got to the wrong list. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Faster StrNCpy
Hi, Tom, Tom Lane wrote: Strong, David [EMAIL PROTECTED] writes: Just wondering - are any of these cases where a memcpy() would work just as well? Or are you not sure that the source string is at least 64 bytes in length? In most cases, we're pretty sure that it's *not* --- it'll just be a palloc'd C string. I'm disinclined to fool with the restriction that namestrcpy zero-pad Name values, because they might end up on disk, and allowing random memory contents to get written out is ungood from a security point of view. There's another disadvantage of always copying 64 bytes: It may be that the 64-byte range crosses a page boundary. Now guess what happens when this next page is not mapped - segfault. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Backup and restore through JDBC
Hi, Marlon, Marlon Petry wrote: But I would need to have installed pg_dump and pg_restore in machine client? Without having installed pg_dump and pg_restore,how I could make pg_dump and pg_restore should be runnable (possible with a small shell / bash wrapper script) without any installation, simply having them and all neded libs lying in the current directory. They don't need any registry keys, weird environment settings or such, just some libs which should be present on most platforms, except libpq. Using a java application for dump/restore will burden you with installing a JVM, the PostgreSQL JDBC drivers, and your application, which seems at least equal effort and more ressources. Btw, another idea is to run pg_dump on the server, but pipe its output to the client, e. G. via running it through telnet or SSH (yes there are SSH servers for windows), and then piping it to a file on the client (e. G. using plink.exe from the putty package). Or use netcat or so. On a unix box, when you're really crazy, and want to ignore all security restrictions, you could even install pg_dump via inetd, and then everyone connecting via TCP on the appropriate port gets a dump of the database. :-) HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
[HACKERS] send()/receive() and on-disk storage
Hi, Currently, there's a discussion on the pljava list, and we're confused about a small detail: Does PostgreSQL call the datatypes' defined send() function before storing the tuple data in the table, on disk, and call receive() when reading it again? My position was that send()/receive() are only used for client communication in the V3 protocol binary mode, and COPY in binary mode, but the backend stores them in the tables as_is (modulo compression/ TOASTing). Is my assumption correct? Btw, I'm going to triple-check this in the source and via tests, too. Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] send()/receive() and on-disk storage
Hi, Martijn, Martijn van Oosterhout wrote: It would be terribly inefficient to call those functions for each read/write. The disk has the internal format, send/receive deal with a portable not-host-dependant representation of the data. Thanks. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Backup and restore through JDBC
Hi, Tom, Tom Dunstan wrote: On a unix box, when you're really crazy, and want to ignore all security restrictions, you could even install pg_dump via inetd, and then everyone connecting via TCP on the appropriate port gets a dump of the database. :-) Oh, man, my head just exploded reading that. That's taking evil and being *creative* with it. :) Well, combine that with some firewall / hosts.allow rules, and sslwrap with certificate based 2-way authentication, if you insist on security. :-) Keep on lauging, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
[HACKERS] timestamptz alias
Hi, I'm happy that the rather verbose timestamp with time zone has the much nicer alias timestamptz, however it seems that this alias is not documented, neither at http://developer.postgresql.org/pgdocs/postgres/datatype-datetime.html nor at http://www.postgresql.org/docs/8.1/interactive/datatype-datetime.html I see it mentioned at http://www.postgresql.org/docs/8.1/interactive/datatype.html but that's possibly not where people look at first, when they search for the timestamp type. (At least I found it only when grepping for timestamptz in the docs. :-) Should the alias be mentioned on the datetime page? The same for timetz? What do you think? Thanks, 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] timestamptz alias
Hi, Jim, Jim Nasby wrote: There's a difference between promoting and withholding info. I'd rather see us explicitly state which is preferred and why. Here's a small patch that adds an appropriate explanation. Index: doc/src/sgml/datatype.sgml === RCS file: /projects/cvsroot/pgsql/doc/src/sgml/datatype.sgml,v retrieving revision 1.176 diff -u -r1.176 datatype.sgml --- doc/src/sgml/datatype.sgml 22 Sep 2006 16:20:00 - 1.176 +++ doc/src/sgml/datatype.sgml 3 Oct 2006 09:14:32 - @@ -1372,6 +1372,17 @@ /para /note + note +para + productnamePostgreSQL/productname also supports the aliases + typetimestamptz/type for typetimestamp with time zone/type + and typetimetz/type for typetime with time zone/type. It + is recommended to avoid them, as the more verbose variants comply + to the SQL standard, and thus are more portable. But there are no + plans to drop the short aliases in future versions. +/para + /note + para typetime/type, typetimestamp/type, and typeinterval/type accept an optional precision value HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] timestamptz alias
Hi, Tom, Tom Lane wrote: Here's a small patch that adds an appropriate explanation. If we're going to document these aliases, what of float4, float8, and bool? Also, although the docs mention int2/int4/int8, it's more or less left to the reader's imagination to deduce what they are. Perhaps it'd be better to provide a small table of recognized type aliases, rather than inserting equivalent notes into three or four places. It's not only about documenting the pure existence of the aliases (which was already documented in the table on the datatype TOC page), it's also about telling the user which of the names are the ones to avoid, and the reasons to do so. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] tsearch2 error msg
Hi, Tom, Tom Lane wrote: No, it should be something like syntax error in tsearch query: contents of string since it's not always the case that you know exactly what string got fed to to_tsquery(). I agree. It's also possible that you have more than one tsearch expression in the query (e. G. by joining different tables), so that will help further. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] timestamptz alias
Hi, Andrew, Andrew Dunstan wrote: It's not only about documenting the pure existence of the aliases (which was already documented in the table on the datatype TOC page), it's also about telling the user which of the names are the ones to avoid, and the reasons to do so. *blink* Why do any need to be avoided? What you use is a matter of taste, and your organisation's coding standards. From a purely technical POV I don't see any reason to avoid using either the canonical type names or the various aliases. At least compatibility with the SQL standard, as well as with other Databases might be a reason. Using pure timestamp may lead human readers to be confused, because it has both meanings with and without timezone historically, this might be a reason to prefer the timestamp with[out] time zone wording. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] PL/pgSQL Todo, better information in errcontext from
Hi, Tom, Tom Lane wrote: I miss some info in context: function's oid, function's argumenst and schema. Maybe: 199292 function public.foo(int, int, int) language plpgsql statement return line 10 Putting the OID there is a seriously awful idea, not least because it would make it impossible to have stable regression-test outputs. I'm not really convinced that we need more than the function name. I also think that the OID will hurt here, but schema and argument types may be useful in some corner cases. Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] pg_dump exclusion switches and functions/types
Hi, Tom, Tom Lane wrote: One issue is what to do with procedural languages and large objects, which don't have any associated schema. If we treat them as being outside all schemas, we'd have semantics like this: dump the PLs and blobs unless one or more --schema switches appeared. Is that OK? Sounds fine. Is there a possibility to dump only those objects? Maybe --large-objects and --languages? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Upgrading a database dump/restore
Hi, Mark, Mark Woodward wrote: People are working it, someone even got so far as dealing with most catalog upgrades. The hard part going to be making sure that even if the power fails halfway through an upgrade that your data will still be readable... Well, I think that any *real* DBA understands and accepts that issues like power failure and hardware failure create situations where suboptimal conditions exist. :-) Stopping the database and copying the pg directory addresses this problem, upon failure, it is a simple mv bkdir pgdir, gets you started again. But when people have enough bandwith and disk space to copy the pg directory, they also have enough to create and store a bzip2 compressed dump of the database. Or did I miss something? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Interface of the R-tree in order to work with postgresql
Hi, Jorge, jorge alberto wrote: I'm Jorge from Peru South America, and this is my first post I want to know how can I add a new spatial access method into the postgresql (I'm doing research on spatial access methods( reading a lot of papers and programming a lot too ) but also I want to know how can I add my new data structure( if someday i get it, of course =) ) in the postgresql, I mean where can i find the .h that describes the interface that a spatial access method, like the R-tree, must have in order to work with postgresql. I think that to understand how postgresql works with spatial access methods, like the R-tree, I need to find a .h or .c to begin reading the code, so can you tell me where to start? Besides the hints others posted here, you might want to look at contrib/tsearch2 and PostGIS for examples of how to use GIST. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] query optimization with UDFs
Hi, Tom, Tom Lane wrote: Neil Conway [EMAIL PROTECTED] writes: BTW, I think it would make sense to implement a limited subset of the xfunc ideas: add options to CREATE FUNCTION to allow cost information to be specified, and then take advantage of this information instead of using the existing constant kludges. This would be a tangible improvement, and would have minimal impact on the planner. The trick is to figure out what a useful parameterized cost model would look like. IIRC, the main reason the xfunc code rotted on the vine was that its cost parameters didn't seem to be either easy to select or powerful in predicting actual cost. We'd have to do better this time. I don't know what the xfunc people did, but at least for some varlen data types (Arrays, PostGIS, text), some function costs (concatenation, GeomUnion etc.) can be estimated via the average field size of the tables. Has that idea been considered? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] GROUP BY on a large table -- an idea
Hi, Dawid, Dawid Kuroczko wrote: The hybrid approach means: sort as much as you can without spilling to disk, then aggregate and store aggregate state variables in safe place (like a tree above), get more tuples from the table, sort them, update aggregate state variables, lather, rince, repeat. For this to work, you need an additional function in the aggregate definition, that allows to merge two states into one, for the update aggregate state variables step. Recently, there was some discussion that the Bizgres MPP people already have such a function for merging states of different backend processes, and that the query planner could benefit from such a function e. G. in case of UNION or table partitioning. Maybe we should come up with an exact definition of syntax and semantics of this function, that satisfies all the needs of the three usecases above? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] SQL functions, INSERT/UPDATE/DELETE RETURNING, and
Hi, Tom, Tom Lane wrote: Jim C. Nasby [EMAIL PROTECTED] writes: The specific concern I have is large result sets, like 10s or 100s of MB (or more). We just added support for not buffering those in psql, so it seems like a step backwards to have the backend now buffering it (unless I'm confused on how a tuplestore works...) Well, a tuplestore can dump to disk, so at least you don't need to worry about out-of-memory considerations. Would it be possible to kinda wrap the query execution into the tuplestore interface, so that the tuples are generated on the fly when fetched from the tuplestore, for large resultsets? Thanks, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Postgresql Caching
Hi, Shane, Shane Ambler wrote: CREATE TABLESPACE myramcache LOCATION MEMORY(2GB); It's already possible to do this, just create the TABLESPACE in a ramdisk / tmpfs or whatever is available for your OS. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] postgres database crashed
Hi, Ashish, Ashish Goel wrote: But the same code worked when I inserted around 2500 images in the database. After that it started crashing. Testing can never prove that there are no bugs. It's like the proof that all odd numbers above 1 are prime: 3 is prime, 5 is prime, 7 is prime, so I conclude that all odd numbers above 1 are prime. So , I don't think it's because of error in the code. Can u suggest some other possible reasons and also why is it crashing at call to memcpy(). - broken hardware - compiler bugs - bugs in PostgreSQL But without having seen your code, I tend to assume that it's something like a wrong length flag in some corner case in your code. ... Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] postgres database crashed
Hi, Ashish, Ashish Goel wrote: I am sorry but I forgot to mention that in the database schema we are maintaining referrences to the main table xyz(int id, img image, fname varhcar(50)) There are around 14 tables referrencing this table . The referrences are being made to the column id. The code works well if we don't maintain the referrences but when we include the referrences then the database crashes somewhere between 2500-3000 transactions. So could this problem be due to the multiple referrences being made to the same table ? I doubt so. Foreign key references are among the basics of SQL, they're pretty well tested. Could you try to replace your image type e. G. with bytea for your test purposes, and see, whether it crashes, too? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Syntax bug? Group by?
Hi, Mark, Mark Woodward wrote: Shouldn't this work? select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; ERROR: column y.ycis_id must appear in the GROUP BY clause or be used in an aggregate function If I am asking for a specific column value, should I, technically speaking, need to group by that column? Try: SELECT 15 as ycis_id, min(tindex), avt(tindex) from y where ycis_id = 15; HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org ---(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
Re: [HACKERS] Syntax bug? Group by?
Hi, Mark, Mark Woodward wrote: Stephen Frost wrote: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; But back to the query the issue comes in that the ycis_id value is included with the return values requested (a single row value with aggregate values that isn't grouped) - if ycis_id is not unique you will get x number of returned tuples with ycis_id=15 and the same min() and avg() values for each row. Removing the ycis_id after the select will return the aggregate values you want without the group by. I still assert that there will always only be one row to this query. This is an aggregate query, so all the rows with ycis_id = 15, will be aggregated. Since ycis_id is the identifying part of the query, it should not need to be grouped. My question, is it a syntactic technicality that PostgreSQL asks for a group by, or a bug in the parser? I think that it's a lack of special-casing the = operator. Imagine where ycis_id15 or where ycis_id @| $RECTANGLE or other (probably user defined) operators on (probably user defined) datatypes. The parser has no real knowledge what the operators do, it simply requests one that returns a bool. One could make the parser to special case the = operator, and maybe some others, however I doubt it's worth the effort. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Syntax bug? Group by?
Hi, Mark, Mark Woodward wrote: My question, is it a syntactic technicality that PostgreSQL asks for a group by, or a bug in the parser? I think that it's a lack of special-casing the = operator. Imagine where ycis_id15 or where ycis_id @| $RECTANGLE or other (probably user defined) operators on (probably user defined) datatypes. The parser has no real knowledge what the operators do, it simply requests one that returns a bool. One could make the parser to special case the = operator, and maybe some others, however I doubt it's worth the effort. I understand the SQL, and this isn't a sql question else it would be on a different list, it is a PostgreSQL internals question and IMHO potential bug. And that's why I talked about PostgreSQL internals. The original query: select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 15; Should NOT require a group by to get ycis_id in the results. And, as I wrote, this is only possible when the query parser special-cases the = operator (compared to all other operators). HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] bug or feature, || -operator and NULLs
Hi, Martijn, Martijn van Oosterhout wrote: On Wed, Oct 18, 2006 at 11:36:44AM -0700, Jeff Davis wrote: What's being suggested simply violates common sense. Basically: if (a = b) then (a||c = b||c) If a is 'x' and b is 'x' and c is NULL, the above statement doesn't hold in PostgreSQL. Heh, well, c is supposed to be not NULL. Missed that. I was using the equals to include (NULL = NULL) but in SQL it's not like that. Maybe you should replace = with IS NOT DISTINCT FROM :-) HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] Multiple postmaster + RPM + locale issues
Hi, Devrim, Devrim GUNDUZ wrote: I have almost finished working on multiple rpm + postmaster issue today. The spec file and patches in pgsqlrpms cvs work almost as expected and does some preliminary work about multiple postmaster installation issue (we can build the rpms and they are installed correctly). Did you look at how the debian guys handle this? HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [HACKERS] xlogdump fixups and WAL log question.
Hi, Simon, Simon Riggs wrote: 1. Provide a filter that can be easily used by archive_command to remove full page writes from WAL files. This would require us to disable the file size test when we begin recovery on a new WAL files, plus would need to redesign initial location of the checkpoint record since we could no longer rely on the XLogRecPtr being a byte offset within the file. pg_WAL_filter could correct the XLogRecPtr and file sizes during the filter run. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
Re: [DOCS] [HACKERS] Replication documentation addition
Hi, Cesar, Cesar Suga wrote: If people (who read the documentation) professionally work with PostgreSQL, they may already have been briefed by those commercial offerings in some way. I think only the source and its tightly coupled (read: can compile along with, free as PostgreSQL) components should be packaged into the tarball. However, I find Bruce's unofficial wiki idea a good one for comparisons. My suggestion is that the docs should mention only the pure existence of important third-party packages and projects in those places where it talks about the deficits that are supposedly fixed by those. E. G. There are some third-party packages and projects that aim to provide multi-master replication, you can search for more information at http://[unofficial wiki page url] or your favourite search engine. This way, the docs stay neutral, but point the user to possible solutions of his problem. HTH, Markus -- Markus Schaber | Logical TrackingTracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org signature.asc Description: OpenPGP digital signature
