[HACKERS] analyze strangeness
We are seeing what seems to me to be very peculiar behaviour. We have a schema upgrade script that alters the schema of an existing production database. One of the things we do is create two new indexes. The script then immediately performs a vacuum analyze. The problem is (or was) that this analyze didn't seem to work. Queries performed thereafter would run slowly. Doing another vacuum analyze later on would fix this, and queries would then perform well. We have two approaches that fix this. The first was to just sleep for two seconds between creating the indexes and doing the vacuum analyze. The second was to perform an explicit checkpoint between index creation and vacuum analyze. The second approach seems the most sound, the sleep approach relies too much on coincidence. But both work in our tests so far. However, why is this so? Can analyze not work properly unless the data files have all been fsynced to disk? Does the WAL really stop analyze from working? Even stranger, it turns out that doing the checkpoint _after_ the vacuum analyze also fixes this behaviour, ie queries perform well immediately. This part is _so_ strange that I'm tempted to just not believe it ever happened... except that it seems it did. Any insights? Is this expected behaviour? Can anyone explain why this is happening? We have a workaround (checkpoint), so we're not too concerned, but would like to understand what's going on. Platform is PG7.1.2 on Red Hat Linux 6.2, x86. Tim -- --- Tim Allen [EMAIL PROTECTED] Proximity Pty Ltd http://www.proximity.com.au/ http://www4.tpg.com.au/users/rita_tim/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
AW: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
I just had an idea about how to avoid this cost: why not recycle old log segments? At the point where the code currently deletes a no-longer-needed segment, just rename it to become the next created-in-advance segment. Yes, since I already suggested this on Feb 26. I naturally think this is a good idea, iirc Vadim also stated similar ideas. http://fts.postgresql.org/db/mw/msg.html?mid=73076 Maybe I did not make myself clear enough though, you clearly did better :-) Another issue is whether the recycling logic should be always recycle (hence number of extant WAL segments will never decrease), or should it be more like recycle if there are fewer than WAL_FILES advance segments, else delete. Yes, I think we should use the WAL_FILES parameter to state how many WAL files should be kept around, or better yet only use it if it is not 0. Thus the default would be to never decrease, but if the admin went to the trouble of specifying a (good) value, that should imho be honored. Andreas ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Memory management
Hi, I use the libraries of function of Postgres in a program. In this script, I keep connected with the postmaster and I submit him a lot of queries without disconnecting each time. At the end of each queries, I use PQclear to clean memory but I notice that the memory used by the process postgres is always increasing until I disconnect. Any idea ? Thanks for your help = ___ Do You Yahoo!? -- Vos albums photos en ligne, Yahoo! Photos : http://fr.photos.yahoo.com ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] ERROR: SELECT DISTINCT ON with postgresql v 7.1.2
Hello,
I have a problem white one sql request. I got this error message :
Warning: PostgreSQL query failed: ERROR: SELECT DISTINCT ON expressions
must match initial ORDER BY expressions in
/export/castor-b7/local-home/kelbertj/Prog/web/lumiere/admin/recherche_realisateurs.php
on line 85 ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions SELECT DISTINCT ON (people_id)
people_id,people_lastname,people_firstname from people where
lower(people_firstname) ~* (SELECT text_accents('\\\Luc\\$')) order by
people_lastname ASC limit 40 offset 0
I didn't find any solution to this problem ! If you have any idea I'll
be most gratefull If you could answer !
Thanks
--
Jean-Michel Kelbert
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] MySQL Gemini code
Bruce Momjian wrote: As some of you know, Nusphere is trying to sell MySQL with an additional transaction-based table manager called Gemini. They enabled download of the source code yesterday at: http://mysql.org/download3.php?file_id=1118 Looking through the 122k lines of C code in the Gemini directory, it is pretty clear from a 'grep -i progress' that the Gemini code is actually the database storage code for the Progress database. Progress is the parent company of Nusphere. And this press release http://www.nusphere.com/releases/071601.htm also explains why they had to do it this way. They disagreed with the policy that every code added to the core system must be owned by MySQL AB, so that these guys can sell it for money in their commercial licenses. IMHO, the MySQL community gives a few people far too much credit anyway. The MySQL AB folks degrade contributions from their community to personal donations to Monty, which he has to scrutinize and often rewrite so that they can stand their (MySQL AB's) standards. Give me a break, but does the entire MySQL community only consist of 16 year old junior pacman players, or are there some real programmers (tm) too? But maybe Mr. Mickos told the truth, that there never have been substantial contributions from the outside and nearly all the code has been written by Monty himself (with little donations from David). In that case, NuSphere's launch of mysql.org was long overdue. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [HACKERS] pg_depend
Wouldn't that work simply by using the oid for the column in pg_attribute as the primary dependency, rather than the table itself, from pg_class? So, the dependency chain would be: view - attribute - table So your examples would 'just work', I think. True. We need to remember to store both sets of dependencies (used attrs as well as the table dependency). Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
On Wed, Jul 18, 2001 at 01:08:15PM +1000, Philip Warner wrote: At 11:25 18/07/01 +0900, Hiroshi Inoue wrote: Oops I made a mistake. Reference name is needed not an object name, i.e object relid object oid relerence relid reference oid reference name I think any deisgn needs to cater for attr dependencies. eg. create table a (f1 int4, f2 int8); create view view_a as select f2 from a; Then alter table a drop f1; -- Is OK. Should just happen alter table a drop f2; -- Should warn about the view, and/or cascade etc. alter table a alter f2 float; -- Should trigger a view recompilation. ...same thing needs to happen with constraints that reference attrs I *think* tables are the only items that can have subobjects with dependant. Wouldn't that work simply by using the oid for the column in pg_attribute as the primary dependency, rather than the table itself, from pg_class? So, the dependency chain would be: view - attribute - table So your examples would 'just work', I think. Ross ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] PQexec() 8191 bytes limit and text fields
First, are you using the latest PG? I was under the impression that all the hard-coded limitations on size had been eliminated in the latest releases. I know for an absolute fact that I can insert multi-megabyte sized text chunks in PG 7.1.2 as I've done just that before... Good luck! -Mitch - Original Message - From: Steve Howe [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 4:51 AM Subject: [HACKERS] PQexec() 8191 bytes limit and text fields Hello all, Writing my interface application, which use the PQexec library, I came across the PQexec() queries 8191 bytes limit. What useful are 4Gb text fields if I have this limit ? I mean, if a user make an update to this field, with a large value (let's say, 4Mb), do I have to call PQexec multiple (more then 500) times, concatenating the strings each time I call it ??? Can't this be better implemented ? This is too slow, and generates much more traffic then I ever wish. This problem also plagues the large objects API, since they're only a wrapper to the built-in large objects API. Does anyone have a better way of doing this ? Best Regards, Steve Howe http://www.vitavoom.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
Patrick Macdonald [EMAIL PROTECTED] writes: Yes, but in a very roundabout way (or so it seems). The main point that I was trying to illustrate was that if a database supports point-in-time recovery, recycling of the only available log segments is a bad thing. Certainly, but deleting them is just as bad ;-). What would need to be changed to use the WAL log for archival purposes is the control logic that decides when an old log segment is no longer needed. Rather than zapping them as soon as they're not needed for crash recovery (our current approach), they'd have to stick around until archived offline, or perhaps for some DBA-specified length of time representing how far back you want to allow for PIT recovery. Nonetheless, at some point an old WAL segment will become deletable (unless you have infinite space on your WAL disk). ISTM that at that point, it makes sense to consider recycling the file rather than deleting it. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: AW: [HACKERS] Idea: recycle WAL segments, don't delete/recreate ' em
Zeugswetter Andreas SB [EMAIL PROTECTED] writes: Yes, since I already suggested this on Feb 26. So you did. Darn, I thought it was original ;-) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] analyze strangeness
Tim Allen [EMAIL PROTECTED] writes:
The problem is (or was) that this analyze didn't seem to work. Queries
performed thereafter would run slowly. Doing another vacuum analyze later
on would fix this, and queries would then perform well.
This makes no sense to me, either. Can you put together a
self-contained test case that demonstrates the problem?
One thing that would be useful is to compare the planner statistics
produced by the first and second vacuums. To see the stats, do
select relname,relpages,reltuples from pg_class where
relname in ('tablename', 'indexname', ...);
(include each index on the table, as well as the table itself) and also
select attname,attdispersion,s.*
from pg_statistic s, pg_attribute a, pg_class c
where starelid = c.oid and attrelid = c.oid and staattnum = attnum
and relname = 'tablename';
Even stranger, it turns out that doing the checkpoint _after_ the vacuum
analyze also fixes this behaviour, ie queries perform well
immediately.
I don't really believe that checkpoint has anything to do with it.
However, if the queries are being done in a different backend than the
one doing the vacuum, is it possible that the other backend is inside an
open transaction and does not see the catalog updates from the
later-starting vacuum transaction?
regards, tom lane
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] ERROR: SELECT DISTINCT ON with postgresql v 7.1.2
On Wed, 18 Jul 2001, Kelbert wrote:
Hello,
I have a problem white one sql request. I got this error message :
Warning: PostgreSQL query failed: ERROR: SELECT DISTINCT ON expressions
must match initial ORDER BY expressions in
/export/castor-b7/local-home/kelbertj/Prog/web/lumiere/admin/recherche_realisateurs.php
on line 85 ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions SELECT DISTINCT ON (people_id)
people_id,people_lastname,people_firstname from people where
lower(people_firstname) ~* (SELECT text_accents('\\\Luc\\$')) order by
people_lastname ASC limit 40 offset 0
I didn't find any solution to this problem ! If you have any idea I'll
be most gratefull If you could answer !
First a warning. The query you've written is potential non-deterministic
if you have a people_id that has multiple rows with different last names
that meet the where clause. This is why the query was rejected in the
first place. The ordering that the rows got chosen (semi-random) would
determine which last name was used and could change the output.
If you *really* want to do this, you can probably put the select distinct
on in a subquery (basically untested, so there might be some syntax
errors)...
select people_id, people_lastname, people_firstname from
( select distinct on (people_id) people_id, people_lastname,
people_firstname from people where lower(people_firstname) ~*
(Select text_accents('\\\Luc\\$')) ) as peop
order by people_lastname asc limit 40 offset 0;
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [HACKERS] MySQL Gemini code
Hi! Nathan == Nathan Myers [EMAIL PROTECTED] writes: Nathan On Wed, Jul 18, 2001 at 08:35:58AM -0400, Jan Wieck wrote: And this press release http://www.nusphere.com/releases/071601.htm also explains why they had to do it this way. Nathan They were always free to fork, but doing it the way they did -- Nathan violating MySQL AB's license -- they shot the dog. Yes, we wouldn't have minded a fork as long as they would have done it under their own name. Now they are causing a lot of confusion and giving both MySQL and open source a bad name :( Of course, PostgreSQL will benefit from this, but I would rather have seen that we would compete with technology instead of with bad PR :( Nathan The lesson? Ask somebody competent, first, before you bet your Nathan company playing license games. The problem is that this doesn't always help. For example if the other part is not playing by the rules, but counts on the fact that because he has more money he will win by the end even if he breaks all the rules going there. Regards, Monty ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Re: OID wraparound (was Re: pg_depend)
On Thursday 19 July 2001 06:08, you wrote: Bruce Momjian [EMAIL PROTECTED] writes: I think it should be off on user tables by default, but kept on system tables just for completeness. It could be added at table creation time or from ALTER TABLEL ADD. It seems we just use them too much for system stuff. pg_description is just one example. and what difference should it make, to have a few extra hundred or thousand OIDs used by system tables, when I insert daily some ten thousand records each using an OID for itself? Why not make OIDs 64 bit? Might slow down a little on legacy hardware, but in a couple of years we'll all run 64 bit hardware anyway. I believe that just using 64 bit would require the least changes to Postgres. Now, why would that look that obvious to me and yet I saw no mentioing of this in the recent postings. Surely it has been discussed before, so which is the point I miss or don't understand? I would need 64 bit sequences anyway, as it is predictable that our table for pathology results will run out of unique IDs in a couple of years. Horst ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] MySQL Gemini code
Hi! Jan == Jan Wieck [EMAIL PROTECTED] writes: Jan Moin Monty, Jan dear fence-guests, Thanks. Please note that we NEVER have asked NuSphere to sign over copyright of Gemini to us. We do it only for the core server, and this is actually not an uncommon thing among open source companies. For example QT (Trolltech) and Ximian (a lot of gnome applications) does the same thing. Assigning over the code is also something that FSF requires for all code contributions. If you criticize us at MySQL AB, you should also criticize the above. Jan I should not criticize the others and Trond already explained Jan why (thank you). Jan All I was doing was summing up some of the latest press Jan releases from NuSphere and MySQL AB. You as CTO and your own Jan CEO have explained detailed enough why the assignment of Jan copyright for all core system related code is so important Jan for your company because of your business modell. As the Jan original banker I am, and as the 13+ year IT consultant I am, Jan I don't have the slightest problem with that and understand Jan it completely. It's not my business at all anyway, so it Jan doesn't matter if I personally think it's good or not. Jan But NuSphere said, that the problem with contributing the Jan Gemini code was because of the copyright questions. Looking Jan at the code now and realizing that it's part of the Progress Jan storage system fits perfectly. NuSphere might have had Jan permission from Progress to release it under the GPL, but not Jan to assign the copyright to MySQL AB. The copyright of parts Jan of the Gemini code is still property of Progress (Britt Jan please come down from the fence and correct me if I'm wrong Jan here). We have never asked for the copyright to Gemini; We don't need the copyright to do an embedded version of MySQL, as MySQL works perfectly without Gemini; We have an agreement with Innobase Oy and an understanding with Sleepycat so we can provide ACID transactions even without Gemini, if any of our commercial customers would require this. (Sorry for the 'business talk', but I just wanted to fill in the background) In my opinion the whole thing with the copyright is a public stunt of NuSphere to explain why they are now doing a fork. I don't have any problems with a fork as long as they don't call it MySQL and don't do it on a site called mysql.org. I had actually hoped to get support from you guy's at PostgreSQL regarding this. You may have similar experience or at least understand our position. The RedHat database may be a good thing for PostgreSQL, but I am not sure if it's a good thing for RedHat or for the main developers to PostgreSQL. Anyway, I think that we open source developers should stick together. We may have our own disagreements, but at least we are working for the same common goal (open source domination). Jan The RedHAT database IS PostgreSQL. And I don't see it Jan becoming something different. All I've seen up to now is that Jan RedHAT will be a contributing member of the PostgreSQL open Jan source community in the same way, PostgreSQL Inc. and Great Jan Bridge LLC are. That they use BIG RED letters while GB uses Jan BIG BLUE ones and PgSQL Inc. a bavarian mix for the Jan marketing, yeah - that's marketing - these folks like logos Jan and colors. The real difference will mature somehow in the Jan service portfolios over time. And since there are many Jan different customers with a broad variety of demands, we'll Jan all find more food than we can eat. No need to fight against Jan each other. Sound's good. I really hope it will be that way in the long run! On the other hand, in the beginning our deal with NuSphere also appeared to be good:( Jan The major advantage in the PostgreSQL case is, that we don't Jan need no dispute about licensing, because whoever thinks he Jan can make a deal out of keeping something proprietary is Jan allowed to. People contributing under the BSD license are Jan just self-confident enough to know that this will become a Jan niche solution or die anyway. Yes, in your case the BSD license is a good license. For us at MySQL AB, that have paid staff doing all most all development work on the server, the GPL license is a better license as this allows to put all software we develop under open source and still make a living. (I am not trying to start a flame war here; I am just saying that both licenses have their use and both benefit open source, but in different ways) Jan And there we are at the point about support regarding THIS. Jan If you're asking for support for the MySQL project, well, I Jan created two procedural languages in PostgreSQL so far and Jan know enough about the
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Hiroshi Inoue [EMAIL PROTECTED] writes: As I mentioned already I'm implementing updatable cursors in ODBC and have half done it. If OIDs would be optional my trial loses its validity but I would never try another implementation. Could you use CTID instead of OID? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Thursday 19 July 2001 12:00 am, Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: However, the utility of INSERT returning a unique identifier to the inserted row needs to be addressed -- I would prefer it return the Another possibility, given that any app using a feature like this is nonportable anyway, is to extend the INSERT statement along the lines that someone (maybe Larry R? I forget now) proposed before: INSERT INTO foo ... RETURNING x,y,z,... where x,y,z, etc are expressions in the variables of the inserted I like this one. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Wednesday 18 July 2001 07:49 pm, Tom Lane wrote: I don't think we should discourage use of OIDs quite as vigorously as you propose ;-). Just playing devil's advocate. As I said, I am one who is using OID's in a client now but who is willing to forgo that feature for large-system stability. All I want is to not expend OIDs on things that have no need for one. That, together with clarifying exactly how unique OIDs should be expected to be, seems to me that it will solve 99% of the problem. 99% solved for 1% effort... The other 1% would take alot more effort. I think you're barking up the right tree, as usual, Tom. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom Lane wrote: Lamar Owen [EMAIL PROTECTED] writes: snip snip Another possibility, given that any app using a feature like this is nonportable anyway, is to extend the INSERT statement along the lines that someone (maybe Larry R? I forget now) proposed before: INSERT INTO foo ... RETURNING x,y,z,... where x,y,z, etc are expressions in the variables of the inserted tuple(s). This could be made to look like a SELECT at the protocol level, which would mean that it wouldn't break client libraries or require a protocol bump, and it's *way* more flexible than any hardwired decision about what columns to return. It wouldn't have any problem with multiple tuples inserted by an INSERT ... SELECT, either. This would be a good thing (tm). I use Oracle quite extensively as well as PG and Oracle's method of RETURNING :avalue is very good for returning values from newly inserted rows. There was some talk a while back about [not?] implementing variable binding. This seems to become very closely related to that. It would seem to solve the problem of having a unique identifier returned for inserts. I'm sure it would please quite a few people in the process, especially ones moving across from Oracle. (kill two birds with one stone) regards, tom lane Ashley Cambrell ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: Is the idea to make oid's optional, with them disabled by default on user tables? My thought is to make OID generation optional on a per-table basis, and disable it on system tables that don't need unique OIDs. (OID would read as NULL on any row for which an OID wasn't generated.) It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. I think it should be off on user tables by default, but kept on system tables just for completeness. It could be added at table creation time or from ALTER TABLEL ADD. It seems we just use them too much for system stuff. pg_description is just one example. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: I think it should be off on user tables by default, but kept on system tables just for completeness. Clearly, certain system tables *must* have OIDs --- pg_class, pg_type, pg_operator, etc --- because we use those OIDs to refer to objects. These are exactly the same tables that have unique indexes on OID. However, I don't see the point of consuming OIDs for entries in, say, pg_listener. The notion that it must have OIDs simply because it's a system table seems silly. pg_attribute is on the edge --- are table columns objects in their own right, deserving of a separate OID, or not? So far I don't see any really good reason why they should have one. Since the goal is to minimize OID consumption, not assigning OIDs to pg_attribute entries seems like a good idea. I don't think this is just a marginal hack. ISTM the main source of OID consumption for an up-and-running system (if it has no large user tables with OIDs) will be creation of temp tables. We can expend two OIDs per temp table (pg_class and pg_type), or we can expend N+9 for an N-column temp table (the seven system attributes plus the N user ones plus pg_class and pg_type). That's *at least* a 5x difference in steady-state rate of OID consumption. If that doesn't get your attention, it should. regards, tom lane ---(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] Idea: recycle WAL segments, don't delete/recreate 'em
Nonetheless, at some point an old WAL segment will become deletable (unless you have infinite space on your WAL disk). ISTM that at that point, it makes sense to consider recycling the file rather than deleting it. Of course, if you plan to keep your WAL files on the same drive, you don't really need point-in-time recovery anyway because you have the physical data files. The only case I can keeping WAL files around for point-in-time is if your WAL files are on a separate drive from the data files, but even then, the page images should be stripped out and the WAL archived somewhere else, hopefully in a configurable way to another disk, tape, or networked computer. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] MySQL Gemini code
On Wed, Jul 18, 2001 at 11:45:54AM -0400, Bruce Momjian wrote: And this press release http://www.nusphere.com/releases/071601.htm ... On a more significant note, I hear the word fork clearly suggested in that text. It is almost like MySQL AB GPL'ed the MySQL code and now they may not be able to keep control of it. Anybody is free to fork MySQL or PostgreSQL alike. The only difference is that all published MySQL forks must remain public, where PostgreSQL forks need not. MySQL AB is demonstrating their legal right to keep as much control as they chose, and NuSphere will lose if it goes to court. The interesting event here is that since NuSphere violated the license terms, they no longer have any rights to use or distribute the MySQL AB code, and won't until they get forgiveness from MySQL AB. MySQL AB would be within their rights to demand that the copyright to Gemini be signed over, before offering forgiveness. If Red Hat forks PostgreSQL, nobody will have any grounds for complaint. (It's been forked lots of times already, less visibly.) Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
Hmmm... my prior appends to this newsgroup are stalled. Hopefully, they'll be available soon. Tom Lane wrote: What you may really be saying is that the existing scheme for management of log segments is inappropriate for PIT usage; if so feel free to propose a better one. But I don't see how recycling of no-longer-wanted segments can break anything. Yes, but in a very roundabout way (or so it seems). The main point that I was trying to illustrate was that if a database supports point-in-time recovery, recycling of the only available log segments is a bad thing. And, yes, in practice if you have point-in-time recovery enabled you better archive your logs with your backup to ensure that you can roll forward as expected. I assume you are not going to do point-in-time recovery by keeping all the WAL segments around on the same disk. You have to copy them off somewhere, right, and once you have copied them, why not reuse them? A possible solution (as I mentioned before)) is to have 2 methods of logging available: circular and forward-recoverable. When a database is created, the creator selects which type of logging to perform. The log segments are exactly the same, only the recycling method is different. Will not fly. We need a solution that is flexible. Hmmm... the more I look at this, the more interested I become. My assumption is that once a log is full the point-in-time recovery daemon will copy that off somewhere, either to a different disk, tape, or over the network to another machine. Once it is done making a copy, the WAL log can be recycled, right? Am I missing something here? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
On Wednesday 18 July 2001 16:06, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is the idea to make oid's optional, with them disabled by default on user tables? It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. SET OIDGEN boolean for database-wide default policy. CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS? ?? Is this sort of thing addressed by any SQL standard (Thomas?)? -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(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] pg_depend
At 11:38 18/07/01 -0400, Tom Lane wrote: Philip Warner [EMAIL PROTECTED] writes: I think any deisgn needs to cater for attr dependencies. eg. I don't really see a need to recognize dependencies at finer than table level. I'd just make the dependency be from view_a to a and keep things simple. What's so wrong with recompiling the view for *every* change of the underlying table? Not a problem for views, but when you get to constraints on large tables, re-evaluating all the constraints unnecessarily could be a nightmare, and especially frustrating when you just dropped an irrelevant attr. Philip Warner| __---_ Albatross Consulting Pty. Ltd. |/ - \ (A.B.N. 75 008 659 498) | /(@) __---_ Tel: (+61) 0500 83 82 81 | _ \ Fax: (+61) 0500 83 82 82 | ___ | Http://www.rhyme.com.au |/ \| |---- PGP key available upon request, | / and from pgp5.ai.mit.edu:11371 |/ ---(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] MySQL Gemini code
And the story goes on... http://www.newsforge.com/comments.pl?sid=01/07/18/0226219commentsort=0mode=flatthreshold=0pid=0 Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # _ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
Hiroshi Inoue [EMAIL PROTECTED] writes: Reference name is needed not an object name, Only if we want to support the notion that drop-and-recreate-with-same-name means that references from other objects should now apply to the new object. I do not think that that's really a good idea, at least not without a heck of a lot of compatibility checking. It'd be way too easy to create cases where the properties of the new object do not match what the referring object expects. The majority of the cases I've heard about where this would be useful are for functions, and we could solve that a lot better with an ALTER FUNCTION command that allows changing the function body (but not the name, arguments, or result type). BTW, name alone is not a good enough referent for functions... you'd have to store the argument types too. 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: OID wraparound (was Re: [HACKERS] pg_depend)
Also, without OID's, how do you fix EXACT duplicate records that happen by accident? LER Original Message On 7/18/01, 3:46:30 PM, Rod Taylor [EMAIL PROTECTED] wrote regarding Re: OID wraparound (was Re: [HACKERS] pg_depend) : If OIDs are dropped a mechanism for retrieving the primary key of the last insert would be greatly appreciated. Heck, it would be useful now (rather than returning OID). I much prefer retrieving the sequence number after the insert than before insert where the insert uses it. Especially when trigger muckary is involved. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Lamar Owen [EMAIL PROTECTED] Cc: Bruce Momjian [EMAIL PROTECTED]; PostgreSQL-development [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 4:30 PM Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend) Lamar Owen [EMAIL PROTECTED] writes: On Wednesday 18 July 2001 16:06, Tom Lane wrote: It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. SET OIDGEN boolean for database-wide default policy. CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS? Something along that line, probably. ?? Is this sort of thing addressed by any SQL standard (Thomas?)? OIDs aren't standard, so the standards are hardly likely to help us decide how they should work. I think the really critical choice here is how much backwards compatibility we want to keep. The most backwards-compatible way, obviously, is OIDs on by default and things work exactly as they do now. But if we were willing to bend things a little then some interesting possibilities open up. One thing I've been wondering about is whether an explicit WITH OIDS spec ought to cause automatic creation of a unique index on OID for that table. ISTM that any application that wants OIDs at all would want such an index... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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 ---(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] Re: Idea: recycle WAL segments, don't delete/recreate'em
* Bruce Momjian [EMAIL PROTECTED] wrote: | Most Unix filesystems will not allocate disk blocks until you write in | them. If you just seek out past end-of-file, the file pointer is moved | but the blocks are unallocated. This is how 'ls' can show a 1gb file | that only uses 4k of disk space. Does this imply that we could get a performance gain by preallocating space for indexes and data itself as well ? I've seen that other database products have a setup step where you have to specify the size of the database. Or does PostgreSQL do any other tricks to prevent fragmentation of data ? If we stored all our tables in one file that would be needed. Since we use the OS to do the defragmenting, I don't think it is an issue. We do allocate in 8k chunks to allow the OS to allocate full filesystem blocks already. Not sure if preallocating even more would help. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
I meant we use them in many cases to link entries, and in pg_description for descriptions and lots of other things that may use them in the future for system table use. pg_description is a point I hadn't thought about --- it uses OIDs to refer to pg_attribute entries. However, pg_description is pretty broken in its assumptions about OIDs anyway. I'm inclined to change it to be indexed by (object type ID, object OID, attributenumber) the same way that Philip proposed indexing pg_depend. Among other things, that'd make it much cheaper to drop comments during a DROP TABLE. You could just scan on (object type ID, object OID), and get both the table and all its columns in a single indexscan search, not one per column as happens now. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] MySQL Gemini code
Hi! As I do have some insight in these matters, I thought I would comment on this thing Jan == Jan Wieck [EMAIL PROTECTED] writes: Jan Bruce Momjian wrote: As some of you know, Nusphere is trying to sell MySQL with an additional transaction-based table manager called Gemini. They enabled download of the source code yesterday at: http://mysql.org/download3.php?file_id=1118 Looking through the 122k lines of C code in the Gemini directory, it is pretty clear from a 'grep -i progress' that the Gemini code is actually the database storage code for the Progress database. Progress is the parent company of Nusphere. Jan And this press release Jan http://www.nusphere.com/releases/071601.htm Jan also explains why they had to do it this way. They disagreed Jan with the policy that every code added to the core system must Jan be owned by MySQL AB, so that these guys can sell it for Jan money in their commercial licenses. Please note that we NEVER have asked NuSphere to sign over copyright of Gemini to us. We do it only for the core server, and this is actually not an uncommon thing among open source companies. For example QT (Trolltech) and Ximian (a lot of gnome applications) does the same thing. Assigning over the code is also something that FSF requires for all code contributions. If you criticize us at MySQL AB, you should also criticize the above. We did never have any problems to include any of GEMINI code into MySQL. We had tried to get them to submit Gemini into MySQL since March, but they didn't want to do that. It was not until we sued NuSphere for, among other things, breaking the GPL that they did finally release Gemini under GPL. We wouldn't mind if they did this 'community thing' with a site named something like NUSPHERE.ORG, but by doing this with MYSQL.ORG and violating our trademark is not something that we can just look upon without reacting. That NuSphere also have had very little regard for the GPL copyright, keeps copyrighted material on their web site and uses mysql.org to push out their own commercial (not free) MySQL distribution tells a lot of their intentions. I had actually hoped to get support from you guy's at PostgreSQL regarding this. You may have similar experience or at least understand our position. The RedHat database may be a good thing for PostgreSQL, but I am not sure if it's a good thing for RedHat or for the main developers to PostgreSQL. Anyway, I think that we open source developers should stick together. We may have our own disagreements, but at least we are working for the same common goal (open source domination). If you ever need any support from us regarding the RedHat database,, please contact me personally about this. I really liked all the PostgreSQL developers I met last year at OSDN; I found it great to be able to exchange ideas, suggest features and talk openly about our products without any restrictions. I hope to be able to do it again this year! Those that has seen my postings knows that I don't publicly criticize PostgreSQL; I do also recommend PostgreSQL for projects where I think it's better suitable than MySQL. I have at many times defended PostgreSQL when I heard people criticize it without a good reason. I am not afraid of pointing out weaknesses in a product if I am sure that I have discovered one, but I try to do that in a professional manner. I don't think you will find that NuSphere is going to be as fair if they get more control over MySQL through mysql.org. Jan IMHO, the MySQL community gives a few people far too much Jan credit anyway. The MySQL AB folks degrade contributions from Jan their community to personal donations to Monty, which he Jan has to scrutinize and often rewrite so that they can stand Jan their (MySQL AB's) standards. Give me a break, but does the Jan entire MySQL community only consist of 16 year old junior Jan pacman players, or are there some real programmers (tm) Jan too? I only rewrite things that are going to be in the MySQL server, not in the clients. As MySQL needs to work in 24/7 systems, we have to be very carefully of what we put into the server. With a background of 20 years of programming, it's also not that hard to rewrite code to make it better so why not do it? Because I know the whole MySQL core code intimately, its much easier for me to remove duplicated functions, optimize things and generalize code to make things works better than the original author had thought of. I am sure that it's the same thing with those of you that has worked a lot of time on the PostgreSQL code... You must also understand that we have a totally different development structure here at MySQL AB than you have. We are 30 people of which 14 are full time developers. 99.99 % of the code in the core MySQL server is written by us or by people that we have paid for the code. We get very few code contributions on the
Re: [HACKERS] pg_depend
Philip Warner [EMAIL PROTECTED] writes: I think any deisgn needs to cater for attr dependencies. eg. I don't really see a need to recognize dependencies at finer than table level. I'd just make the dependency be from view_a to a and keep things simple. What's so wrong with recompiling the view for *every* change of the underlying table? We could support attr-level dependencies within the proposed pg_depend layout if we made pg_attribute one of the allowed object categories. However, I'd prefer not to make OID of pg_attribute rows be a primary key for that table (in the long run I'd like to not assign OIDs at all to pg_attribute, as well as other tables that don't need OIDs). So the better way to do it would be to make the pg_depend entries include attribute numbers. But I really think this is unnecessary complexity. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] PQexec() 8191 bytes limit and text fields
Steve Howe [EMAIL PROTECTED] writes: Writing my interface application, which use the PQexec library, I came across the PQexec() queries 8191 bytes limit. You must have a very out-of-date library. Time to update. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: Remember most pg_description comments are not on column but on functions and stuff. That attributenumber is not going to apply there. Sure, it'd just be zero for non-column items. What do we do with other columns that need descriptions and don't have oid column. Make the attribute column mean something else? I just don't see a huge gain here and lots of confusion. User tables are a different story. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
currval() could work nicely, but thats an additional query. Currently OID (in php among others) can be retrieved along with the insert response which is instantly retrievable. This makes for a very quick middleware enforced foreign key entry in other databases. Returning the entire primary key of the last row inserted without doing additional queries -- this is a known element which could be cached -- could be very useful in these situations. With tables requiring multi-key elements we do a second select asking for currval()s of the sequences. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: Bruce Momjian [EMAIL PROTECTED] To: Rod Taylor [EMAIL PROTECTED] Cc: Lamar Owen [EMAIL PROTECTED]; Tom Lane [EMAIL PROTECTED]; PostgreSQL-development [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 5:06 PM Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend) If OIDs are dropped a mechanism for retrieving the primary key of the last insert would be greatly appreciated. Heck, it would be useful now (rather than returning OID). I much prefer retrieving the sequence number after the insert than before insert where the insert uses it. Especially when trigger muckary is involved. Doesn't currval() work for your needs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[HACKERS] Re: [HACKERS - GENERAL] PQexec() 8191 bytes limit and text fields
Hi Steve, lets approach this from the other angle... I don't see anywhere in your email where you say what makes you think that you can only pass a query 8191 bytes in size to PG. What exactly makes you think that there is some hard coded limit? This limit is not in 7.1.2 so either you have outdated source code or the problem is somewhere else.. Good luck! -Mitch - Original Message - From: Steve Howe [EMAIL PROTECTED] To: Tom Lane [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 1:30 PM Subject: Re: [HACKERS] PQexec() 8191 bytes limit and text fields Hi... The problem is, I compiled it myself from original PostgreSQL version 7.12 C sources using Microsoft's Visual C++ 6.0. I had to compile it because I add a function to free the handlers returned from PQnotifies(), or I would have a memory leak. The resulting libpq.dll seems ok in everything but this issue... I guess I'll do it again, after checking the sources :) Other people reported me they send large queries with no problems, so I guess it should really be a problem of mine... Best Regards, Steve Howe - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Steve Howe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 1:14 PM Subject: Re: [HACKERS] PQexec() 8191 bytes limit and text fields Steve Howe [EMAIL PROTECTED] writes: Writing my interface application, which use the PQexec library, I came across the PQexec() queries 8191 bytes limit. You must have a very out-of-date library. Time to update. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Wed, Jul 18, 2001 at 04:06:28PM -0400, Tom Lane wrote: Bruce Momjian [EMAIL PROTECTED] writes: Is the idea to make oid's optional, with them disabled by default on user tables? My thought is to make OID generation optional on a per-table basis, and disable it on system tables that don't need unique OIDs. (OID would read as NULL on any row for which an OID wasn't generated.) How about generalizing this to user defineable system attributes? OID would just be a special case: it's really just a system 'serial' isn't it? We occasionally get calls for other system type attributes that would be too expensive for every table, but would be useful for individual tables. One is creation_timestamp. Or this could be a route to bringing timetravel back in: start_date stop_date, anyone? It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. Two ways come to mind: either special WITH options, at the end, or a new per attribute SYSTEM keyword: CREATE TABLE ... WITH OIDS CREATE TABLE ... WITH TIMETRAVEL CREATE TABLE ... WITH DATESTAMP CREAT TABLE foo (oid oid SYSTEM, created timestamp SYSTEM DEFAULT CURRENT_TIMESTAMP, my_id serial, my_field text); So, basically it just creates the type and gives it a negative attnum. The 'oid system' case would need to be treated specially, hooking the oid up to the system wide counter. I'm not sure the special behavior of returning NULL for oid on a table without one is going to be useful: any client code that expects everything to have an oid is unlikely to handle NULL better than an error. In fact, in combination with the MS-Access compatability hack of '= NULL' as 'IS NULL', I see a potential great loss of data: SELECT oid,* from some_table; display to user for editing UPDATE some_table set field1=$field1, field2=$field2, ... WHERE oid = $oid; if $oid is NULL ... There goes the entire table. Ross ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Full Text Indexing
Christopher Kings-Lynne [EMAIL PROTECTED] writes: I'm playing around with the Full Text Indexing module, and I notice that it's case-sensitive. This seems to be pretty useless to me - especially for my application. I wonder if there'd be any objections to me modifying it to be case-insensitive. Or at least be configurable either way... Seems like a good idea, but make it configurable. Also, the fti.pl that comes with the contrib seems to be using an outdated version of CPAN's Pg.pm. It hasn't been touched in awhile, so feel free to update it. BTW, someone ought to look at bringing src/interfaces/perl5 into sync with the CPAN version, too. Or possibly we should stop distributing that altogether, if the CPAN copy is being maintained? regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Hiroshi Inoue [EMAIL PROTECTED] writes: I don't love current OIDs. However they have lived in PostgreSQL's world too long and few people have pointed out that there's no magic around OIDs. I agree to change OIDs to be per class but strongly object to let OIDs optional. Uh ... what? I don't follow what you are proposing here. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] MySQL Gemini code
On Wed, Jul 18, 2001 at 08:35:58AM -0400, Jan Wieck wrote: And this press release http://www.nusphere.com/releases/071601.htm also explains why they had to do it this way. They were always free to fork, but doing it the way they did -- violating MySQL AB's license -- they shot the dog. The lesson? Ask somebody competent, first, before you bet your company playing license games. Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
If OIDs are dropped a mechanism for retrieving the primary key of the last insert would be greatly appreciated. Heck, it would be useful now (rather than returning OID). I much prefer retrieving the sequence number after the insert than before insert where the insert uses it. Especially when trigger muckary is involved. Doesn't currval() work for your needs. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: [HACKERS] pg_depend
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Hiroshi Inoue [EMAIL PROTECTED] writes: Reference name is needed not an object name, Only if we want to support the notion that drop-and-recreate-with-same-name means that references from other objects should now apply to the new object. I do not think that that's really a good idea, at least not without a heck of a lot of compatibility checking. It'd be way too easy to create cases where the properties of the new object do not match what the referring object expects. For example, we would process the following step to drop a column. select (all columns except a column) from a into b; drop table a; alter table b rename to a; But we would lose all relelvant objects. Though we may be able to solve this problem by implementing *drop column* properly, we couldn't solve this kind of problems at once. In fact neither *drop column* nor *cluster* is solved. We could always have (at least) the second best way by allowing drop-and-recreate-with-same-name revival. The majority of the cases I've heard about where this would be useful are for functions, and we could solve that a lot better with an ALTER FUNCTION command that allows changing the function body (but not the name, arguments, or result type). BTW, name alone is not a good enough referent for functions... you'd have to store the argument types too. ??? Isn't an entry pg_proc_relid the oid of the function pg_type_relid the oid of an argument type the name of the argument type made ? regards, Hiroshi Inoue ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen [EMAIL PROTECTED] writes: Now for a question: OID creation seems to be a low-overhead task. Is the creation of SERIAL PRIMARY KEY values as efficient? Or will we be shooting ourselves in the performance foot if frequently-accessed system tables go from OID usage to SERIAL PRIMARY KEY usage? Yes, nowhere near, and yes. Sequence objects require disk I/O to update; the OID counter essentially lives in shared memory, and can be bumped for the price of a spinlock access. I don't think we should discourage use of OIDs quite as vigorously as you propose ;-). All I want is to not expend OIDs on things that have no need for one. That, together with clarifying exactly how unique OIDs should be expected to be, seems to me that it will solve 99% of the problem. 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: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen [EMAIL PROTECTED] writes: ... these two issues of ID wrap need to be addressed -- my gut feel is that the reports of OID/XID wrap are going to skyrocket within 6 months as bigger and bigger installations try out PostgreSQL/RHDB Yes, my thoughts exactly. We're trying to play in the big leagues now. I don't believe we can put these problems off any longer. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
On Wednesday 18 July 2001 13:52, Tom Lane wrote: here: I want 7.2 not to have any limitations that prevent it from being used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed now, or nearly. The other stumbling blocks for continuous runs are OID Go for it, Tom. After the posting the other day about the 200GB data per week data load, this _really_ needs to be done. It won't directly affect me, as my needs are a little more modest (just about anything looks modest compared to _that_ data load). Petty limitations such as these two need to go away, and soon -- we're getting used by big installations now. This isn't Stonebraker's research Postgres anymore. The 7.1 removal of previous limitations was nearly overdue -- and these two issues of ID wrap need to be addressed -- my gut feel is that the reports of OID/XID wrap are going to skyrocket within 6 months as bigger and bigger installations try out PostgreSQL/RHDB (fact is that many are going to try it out _because_ it has been relabeled by Red Hat). The MySQL/NuSphere articles illustrate that -- the NuSphere guy goes as far as saying that the support of _Red_Hat_ is what gives PG credibilitiy -- and, you have to admit, RH's adoption of PG does increase, in many circles, PG's credibility. Of course, PG has credibility with me for other reasons -- it was, IMHO, just a matter of time before Red Hat saw the PostgreSQL Light. -- Lamar Owen WGCR Internet Radio 1 Peter 4:11 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] dependent dependants
For the record: http://www.lineone.net/dictionaryof/englishusage/d0081889.html dependent or dependant Dependent is the adjective, used for a person or thing that depends on someone or something: Admission to college is dependent on A-level results. Dependant is the noun, and is a person who relies on someone for financial support: Do you have any dependants? This is not for mailing-list pendantism, but just to make sure that the right spelling gets into the code. (The page mentioned above was found by entering dependent dependant into Google.) Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
Bruce Momjian [EMAIL PROTECTED] writes: I don't see any value in dropping oid from pg_attribute. Conservation of OIDs. Assigning an OID to every row of pg_attribute chews up lots of OIDs, for a table that should never be referenced by OID --- its primary key is (table OID, attribute number). Right now this isn't really significant, but if/when we have an option to suppress OID generation for user tables, I have every intention of applying it to a bunch of the system tables as well. pg_attribute is a prime candidate. (When probably means next month, btw. This is on my 7.2 list...) Yikes, I am not sure we are ready to make oids optional. System table oid's seem like the last place to try and preserve oids. Do we return unused oids back to the pool on backend exit yet? (I don't see it on the TODO list.) That seems like a much more profitable place to start. Will we have cheap 64-bit oids by the time oid wraparound becomes an issue? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] MySQL Gemini code
And this press release http://www.nusphere.com/releases/071601.htm also explains why they had to do it this way. They disagreed with the policy that every code added to the core system must be owned by MySQL AB, so that these guys can sell it for money in their commercial licenses. This is interesting. They mention PostgreSQL twice as an example to emulate for MySQL. They feel the pressure of companies involved with PostgreSQL and see the benefit of a community around the database. On a more significant note, I hear the word fork clearly suggested in that text. It is almost like MySQL AB GPL'ed the MySQL code and now they may not be able to keep control of it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
RE: OID wraparound (was Re: [HACKERS] pg_depend)
If you want to make oids optional on user tables, we can vote on that. Let's vote. I'm proposing optional oids for 2-3 years, so you know how I'll vote -:) However, OID's keep our system tables together. How?! If we want to find function with oid X we query pg_proc, if we want to find table with oid Y we query pg_class - we always use oids in context of class to what an object belongs. This means that two tuples from different system tables could have same oid values and everything would work perfectly. There is no magic around OIDs. Vadim ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen [EMAIL PROTECTED] writes: ... these two issues of ID wrap need to be addressed -- my gut feel is that the reports of OID/XID wrap are going to skyrocket within 6 months as bigger and bigger installations try out PostgreSQL/RHDB Yes, my thoughts exactly. We're trying to play in the big leagues now. I don't believe we can put these problems off any longer. Is the idea to make oid's optional, with them disabled by default on user tables? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
I meant we use them in many cases to link entries, and in pg_description for descriptions and lots of other things that may use them in the future for system table use. pg_description is a point I hadn't thought about --- it uses OIDs to refer to pg_attribute entries. However, pg_description is pretty broken in its assumptions about OIDs anyway. I'm inclined to change it to be indexed by (object type ID, object OID, attributenumber) the same way that Philip proposed indexing pg_depend. Among other things, that'd make it much cheaper to drop comments during a DROP TABLE. You could just scan on (object type ID, object OID), and get both the table and all its columns in a single indexscan search, not one per column as happens now. Remember most pg_description comments are not on column but on functions and stuff. That attributenumber is not going to apply there. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: Yikes, I am not sure we are ready to make oids optional. We've discussed it enough, it's time to do it. I have an ulterior plan here: I want 7.2 not to have any limitations that prevent it from being used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed now, or nearly. The other stumbling blocks for continuous runs are OID wraparound and XID wraparound. We've got unique indexes on OIDs for all system catalogs that need them (we were short a couple as of 7.1, btw), but OID wrap is still likely to lead to unwanted duplicate key failures. So we still need a way to reduce the system's appetite for OIDs. In a configuration where OIDs are used only where *necessary*, it'd be a long time till wrap. I also intend to do something about XID wrap next month... If you want to make oids optional on user tables, we can vote on that. However, OID's keep our system tables together. Though we don't need them on every system table, it seems they should be on all system tables just for completeness. Are we really losing a significant amount of oids through system tables? Do we return unused oids back to the pool on backend exit yet? Since WAL, and that was never a fundamental answer anyway. Will we have cheap 64-bit oids by the time oid wraparound becomes an issue? No, we won't, because OID wrap is an issue already for any long-uptime installation. (64-bit XIDs are not a real practical answer either, btw.) Have we had a wraparound yet? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen [EMAIL PROTECTED] writes: On Wednesday 18 July 2001 16:06, Tom Lane wrote: It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. SET OIDGEN boolean for database-wide default policy. CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS? Something along that line, probably. ?? Is this sort of thing addressed by any SQL standard (Thomas?)? OIDs aren't standard, so the standards are hardly likely to help us decide how they should work. I think the really critical choice here is how much backwards compatibility we want to keep. The most backwards-compatible way, obviously, is OIDs on by default and things work exactly as they do now. But if we were willing to bend things a little then some interesting possibilities open up. One thing I've been wondering about is whether an explicit WITH OIDS spec ought to cause automatic creation of a unique index on OID for that table. ISTM that any application that wants OIDs at all would want such an index... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em
Err PG_DUMP nightly on a 38,000,000+row table that takes forever to dump/unload, and gets updated every 5 minutes with 256KChar worth of updates? Give me a FAST pg_dump, and I'll think about it, until then, no LER (PS: this is also a reason for making a pg_upgrade work IN PLACE on a table). LER Original Message On 7/18/01, 11:35:04 AM, Bruce Momjian [EMAIL PROTECTED] wrote regarding Re: [HACKERS] Idea: recycle WAL segments, don't delete/recreate 'em: Yes, but in a very roundabout way (or so it seems). The main point that I was trying to illustrate was that if a database supports point-in-time recovery, recycling of the only available log segments is a bad thing. And, yes, in practice if you have point-in-time recovery enabled you better archive your logs with your backup to ensure that you can roll forward as expected. I assume you are not going to do point-in-time recovery by keeping all the WAL segments around on the same disk. Of course not. As mentioned, you'd probably archive them with your backup(s). You mean the nigthly backup? Why not do a pg_dump and be done with it. You have to copy them off somewhere, right, and once you have copied them, why not reuse them? I'm not arguing that point. I stated recycling of the only available log segments. Once the log segment is archived (copied) elsewhere you have two available images of the same segment. You can rename the local copy. Yes, OK, I see now. As Tom mentioned, there would have to be some delay where we allow the WAL log to be archived before reusing it. A possible solution (as I mentioned before)) is to have 2 methods of logging available: circular and forward-recoverable. When a database is created, the creator selects which type of logging to perform. The log segments are exactly the same, only the recycling method is different. Will not fly. We need a solution that is flexible. Could you expand on that a little (ie. flexible in which way). Offering the user a choice of two is more flexible than offering no choice. We normally don't give users choices unless we can't come up with a win-win solution to the problem. In this case, we could just query to see if the WAL PIT archiver is running and handle tune reuse of log segments on the fly. In fact, my guess is that the PIT archiver will have to tell the system when it is done with WAL logs anyway. Hmmm... the more I look at this, the more interested I become. My assumption is that once a log is full the point-in-time recovery daemon will copy that off somewhere, either to a different disk, tape, or over the network to another machine. Once it is done making a copy, the WAL log can be recycled, right? Am I missing something here? Ok... I wasn't thinking of having a point-in-time daemon. Some other databases provide, for lack of a better term, user exits to allow user defined scripts or programs to be called to perform log segment archiving. This archiving is somewhat orthogonal to point-in-time recovery proper. Yep, once the archiving is complete, you can do whatever you want with the local log segment. We will clearly need something to transfer these WAL logs somewhere else, and it would be nice if it could be easily configured. I think a PIT logger daemon is the only solution, especially since tape/network transfer could take a long time. It would be forked by the postmaster so would cover all users and databases. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(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] Idea: recycle WAL segments, don't delete/recreate 'em
Yes, but in a very roundabout way (or so it seems). The main point that I was trying to illustrate was that if a database supports point-in-time recovery, recycling of the only available log segments is a bad thing. And, yes, in practice if you have point-in-time recovery enabled you better archive your logs with your backup to ensure that you can roll forward as expected. I assume you are not going to do point-in-time recovery by keeping all the WAL segments around on the same disk. Of course not. As mentioned, you'd probably archive them with your backup(s). You mean the nigthly backup? Why not do a pg_dump and be done with it. You have to copy them off somewhere, right, and once you have copied them, why not reuse them? I'm not arguing that point. I stated recycling of the only available log segments. Once the log segment is archived (copied) elsewhere you have two available images of the same segment. You can rename the local copy. Yes, OK, I see now. As Tom mentioned, there would have to be some delay where we allow the WAL log to be archived before reusing it. A possible solution (as I mentioned before)) is to have 2 methods of logging available: circular and forward-recoverable. When a database is created, the creator selects which type of logging to perform. The log segments are exactly the same, only the recycling method is different. Will not fly. We need a solution that is flexible. Could you expand on that a little (ie. flexible in which way). Offering the user a choice of two is more flexible than offering no choice. We normally don't give users choices unless we can't come up with a win-win solution to the problem. In this case, we could just query to see if the WAL PIT archiver is running and handle tune reuse of log segments on the fly. In fact, my guess is that the PIT archiver will have to tell the system when it is done with WAL logs anyway. Hmmm... the more I look at this, the more interested I become. My assumption is that once a log is full the point-in-time recovery daemon will copy that off somewhere, either to a different disk, tape, or over the network to another machine. Once it is done making a copy, the WAL log can be recycled, right? Am I missing something here? Ok... I wasn't thinking of having a point-in-time daemon. Some other databases provide, for lack of a better term, user exits to allow user defined scripts or programs to be called to perform log segment archiving. This archiving is somewhat orthogonal to point-in-time recovery proper. Yep, once the archiving is complete, you can do whatever you want with the local log segment. We will clearly need something to transfer these WAL logs somewhere else, and it would be nice if it could be easily configured. I think a PIT logger daemon is the only solution, especially since tape/network transfer could take a long time. It would be forked by the postmaster so would cover all users and databases. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
If OIDs are dropped a mechanism for retrieving the primary key of the last insert would be greatly appreciated. Heck, it would be useful now (rather than returning OID). I much prefer retrieving the sequence number after the insert than before insert where the insert uses it. Especially when trigger muckary is involved. -- Rod Taylor Your eyes are weary from staring at the CRT. You feel sleepy. Notice how restful it is to watch the cursor blink. Close your eyes. The opinions stated above are yours. You cannot imagine why you ever felt otherwise. - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Lamar Owen [EMAIL PROTECTED] Cc: Bruce Momjian [EMAIL PROTECTED]; PostgreSQL-development [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 4:30 PM Subject: Re: OID wraparound (was Re: [HACKERS] pg_depend) Lamar Owen [EMAIL PROTECTED] writes: On Wednesday 18 July 2001 16:06, Tom Lane wrote: It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. SET OIDGEN boolean for database-wide default policy. CREATE TABLE WITH OIDS for individual tables? CREATE TABLE WITHOUT OIDS? Something along that line, probably. ?? Is this sort of thing addressed by any SQL standard (Thomas?)? OIDs aren't standard, so the standards are hardly likely to help us decide how they should work. I think the really critical choice here is how much backwards compatibility we want to keep. The most backwards-compatible way, obviously, is OIDs on by default and things work exactly as they do now. But if we were willing to bend things a little then some interesting possibilities open up. One thing I've been wondering about is whether an explicit WITH OIDS spec ought to cause automatic creation of a unique index on OID for that table. ISTM that any application that wants OIDs at all would want such an index... regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: Is the idea to make oid's optional, with them disabled by default on user tables? My thought is to make OID generation optional on a per-table basis, and disable it on system tables that don't need unique OIDs. (OID would read as NULL on any row for which an OID wasn't generated.) It remains to be debated exactly how users should control the choice for user tables, and which choice ought to be the default. I don't have a strong opinion about that either way, and am prepared to hear suggestions. 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: OID wraparound (was Re: [HACKERS] pg_depend)
If you want to make oids optional on user tables, we can vote on that. Let's vote. I'm proposing optional oids for 2-3 years, so you know how I'll vote -:) OK, we need to vote on whether Oid's are optional, and whether we can have them not created by default. However, OID's keep our system tables together. How?! If we want to find function with oid X we query pg_proc, if we want to find table with oid Y we query pg_class - we always use oids in context of class to what an object belongs. This means that two tuples from different system tables could have same oid values and everything would work perfectly. I meant we use them in many cases to link entries, and in pg_description for descriptions and lots of other things that may use them in the future for system table use. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] MySQL Gemini code
Michael Widenius wrote: Hi! Moin Monty, dear fence-guests, Please note that we NEVER have asked NuSphere to sign over copyright of Gemini to us. We do it only for the core server, and this is actually not an uncommon thing among open source companies. For example QT (Trolltech) and Ximian (a lot of gnome applications) does the same thing. Assigning over the code is also something that FSF requires for all code contributions. If you criticize us at MySQL AB, you should also criticize the above. I should not criticize the others and Trond already explained why (thank you). All I was doing was summing up some of the latest press releases from NuSphere and MySQL AB. You as CTO and your own CEO have explained detailed enough why the assignment of copyright for all core system related code is so important for your company because of your business modell. As the original banker I am, and as the 13+ year IT consultant I am, I don't have the slightest problem with that and understand it completely. It's not my business at all anyway, so it doesn't matter if I personally think it's good or not. But NuSphere said, that the problem with contributing the Gemini code was because of the copyright questions. Looking at the code now and realizing that it's part of the Progress storage system fits perfectly. NuSphere might have had permission from Progress to release it under the GPL, but not to assign the copyright to MySQL AB. The copyright of parts of the Gemini code is still property of Progress (Britt please come down from the fence and correct me if I'm wrong here). I had actually hoped to get support from you guy's at PostgreSQL regarding this. You may have similar experience or at least understand our position. The RedHat database may be a good thing for PostgreSQL, but I am not sure if it's a good thing for RedHat or for the main developers to PostgreSQL. Anyway, I think that we open source developers should stick together. We may have our own disagreements, but at least we are working for the same common goal (open source domination). The RedHAT database IS PostgreSQL. And I don't see it becoming something different. All I've seen up to now is that RedHAT will be a contributing member of the PostgreSQL open source community in the same way, PostgreSQL Inc. and Great Bridge LLC are. That they use BIG RED letters while GB uses BIG BLUE ones and PgSQL Inc. a bavarian mix for the marketing, yeah - that's marketing - these folks like logos and colors. The real difference will mature somehow in the service portfolios over time. And since there are many different customers with a broad variety of demands, we'll all find more food than we can eat. No need to fight against each other. The major advantage in the PostgreSQL case is, that we don't need no dispute about licensing, because whoever thinks he can make a deal out of keeping something proprietary is allowed to. People contributing under the BSD license are just self-confident enough to know that this will become a niche solution or die anyway. And there we are at the point about support regarding THIS. If you're asking for support for the MySQL project, well, I created two procedural languages in PostgreSQL so far and know enough about the query rewriting techniques used by Stonebraker and his team to implement views in PostgreSQL. As the open source developer I am, I might possibly find one or the other spare hour to create something similar. The reason I did it for PostgreSQL was because a couple of years ago Bruce Momjian asked me to fix the rule system. Noone ever asked me to do anything for MySQL. But if you're asking for direct support for your company, sorry, but I'm a Great Bridge employee and that's clearly against my interests. Jan But maybe Mr. Mickos told the truth, that there never have Jan been substantial contributions from the outside and nearly Jan all the code has been written by Monty himself (with little Jan donations from David). In that case, NuSphere's launch of Jan mysql.org was long overdue. Why do you think that? MySQL AB is a totally open source company. Everything we develop and sell we also put on open source. I think we have are doing and have always done the right thing for the open source community. That is what your CEO said on NewsForge, SlashDot and whereever. I am committed to free source. Thus I think that the best thing for open source is a free community, which and who's product is not controlled by any commercial entity. I don't think it's really fair to be compare us to NuSphere :( Did
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: Also, without OID's, how do you fix EXACT duplicate records that happen by accident? How about tid's? SELECT tid FROM tab1. SELECT ctid, actually, but that is still the fallback. (Actually it always was --- OIDs aren't necessarily unique either, Larry.) regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] PQexec() 8191 bytes limit and text fields
Hello all, Writing my interface application, which use the PQexec library, I came across the PQexec() queries 8191 bytes limit. What useful are 4Gb text fields if I have this limit ? I mean, if a user make an update to this field, with a large value (let's say, 4Mb), do I have to call PQexec multiple (more then 500) times, concatenating the strings each time I call it ??? Can't this be better implemented ? This is too slow, and generates much more traffic then I ever wish. This problem also plagues the large objects API, since they're only a wrapper to the built-in large objects API. Does anyone have a better way of doing this ? Best Regards, Steve Howe http://www.vitavoom.com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Full Text Indexing
Hi, I'm playing around with the Full Text Indexing module, and I notice that it's case-sensitive. This seems to be pretty useless to me - especially for my application. I wonder if there'd be any objections to me modifying it to be case-insensitive. Or at least be configurable either way... Also, the fti.pl that comes with the contrib seems to be using an outdated version of CPAN's Pg.pm. The Perl script currently does stuff in a procedural way: ie. print(PQErrorMessage($conn)) Where it seems to need to be: print($conn-errorMessage). I'm not sure if I'm missing something here, but I could also update it to use the new interface. Regards, Chris ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] MySQL Gemini code
On Wed, Jul 18, 2001 at 06:37:48PM -0400, Trond Eivind Glomsr?d wrote: Michael Widenius [EMAIL PROTECTED] writes: Assigning over the code is also something that FSF requires for all code contributions. If you criticize us at MySQL AB, you should also criticize the above. This is slightly different - FSF wants it so it will have a legal position to defend its programs: ... MySQL and TrollTech requires copyright assignment in order to sell non-open licenses. Some people will have a problem with this, while not having a problem with the FSF copyright assignment. Nobody who works on MySQL is unaware of MySQL AB's business model. Anybody who contributes to the core server has to expect that MySQL AB will need to relicense anything accepted into the core; that's their right as originators. Everybody who contributes has a choice to make: fork, or sign over. (With the GPL, forking remains possible; Apple and Sun community licenses don't allow it.) Anybody who contributes to PG has to make the same choice: fork, or put your code under the PG license. The latter choice is equivalent to signing over to all proprietary vendors, who are then free to take your code proprietary. Some of us like that. I had actually hoped to get support from you guys at PostgreSQL regarding this. You may have similar experience or at least understand our position. The RedHat database may be a good thing for PostgreSQL, but I am not sure if it's a good thing for RedHat or for the main developers to PostgreSQL. This isn't even a remotely similar situation: ... It's similar enough. One difference is that PG users are less afraid to fork. Another is that without the GPL, we have elected not to (and indeed cannot) stop any company from doing with PG what NuSphere is doing with MySQL. This is why characterizing the various licenses as more or less business-friendly is misleading (i.e. dishonest) -- it evades the question, friendly to whom?. Businesses sometimes compete... Nathan Myers [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
Wouldn't that work simply by using the oid for the column in pg_attribute as the primary dependency, rather than the table itself, from pg_class? So, the dependency chain would be: view - attribute - table So your examples would 'just work', I think. True. We need to remember to store both sets of dependencies (used attrs as well as the table dependency). TODO update with column labels: * Add pg_depend table for dependency recording; use sysrelid, oid, depend_sysrelid, depend_oid, name -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
AW: [HACKERS] Re: Idea: recycle WAL segments, don't delete/recreate 'emm
: Most Unix filesystems will not allocate disk blocks until you write in : them. [...] Yes, I understand that, but how is it a problem for postgresql? Uh, I thought we did that so we were not allocating file system blocks during WAL writes. Performance is bad when we do that. Performance isn't the question. iirc, at the time, performance was also a question, at least on some of the platforms that were tested. The problem is when you get a disk full just in the middle of the need to write important WAL information. While preallocation of a new WAL file, it's OK and controlled, but there are more delicate portions of the code. Of course there should not be, since the write to the WAL is the first IO :-) Imho all modifying activity could be blocked, until disk space is made available by the admin. Could you enlighten us on what the delicate portions are (other than when running in no fsync mode) ? Andreas ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] ERROR: SELECT DISTINCT ON with postgresql v 7.1.2
Hello,
I have a problem white one sql request. I got this error message :
Warning: PostgreSQL query failed: ERROR: SELECT DISTINCT ON expressions
must match initial ORDER BY expressions in
/export/castor-b7/local-home/kelbertj/Prog/web/lumiere/admin/recherche_realisateurs.php
on line 85 ERROR: SELECT DISTINCT ON expressions must match initial
ORDER BY expressions SELECT DISTINCT ON (people_id)
people_id,people_lastname,people_firstname from people where
lower(people_firstname) ~* (SELECT text_accents('\\\Luc\\$')) order by
people_lastname ASC limit 40 offset 0
I didn't find any solution to this problem ! If you have any idea I'll
be most gratefull If you could answer !
Thanks
--
Jean-Michel KELBERT
---(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] Re: Idea: recycle WAL segments, don't delete/recreate 'em
* Bruce Momjian [EMAIL PROTECTED] wrote: | Most Unix filesystems will not allocate disk blocks until you write in | them. If you just seek out past end-of-file, the file pointer is moved | but the blocks are unallocated. This is how 'ls' can show a 1gb file | that only uses 4k of disk space. Does this imply that we could get a performance gain by preallocating space for indexes and data itself as well ? I've seen that other database products have a setup step where you have to specify the size of the database. Or does PostgreSQL do any other tricks to prevent fragmentation of data ? -- Gunnar Rønning - [EMAIL PROTECTED] Senior Consultant, Polygnosis AS, http://www.polygnosis.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
RE: OID wraparound (was Re: [HACKERS] pg_depend)
OK, we need to vote on whether Oid's are optional, and whether we can have them not created by default. Optional OIDs: YES No OIDs by default: YES However, OID's keep our system tables together. How?! If we want to find function with oid X we query pg_proc, if we want to find table with oid Y we query pg_class - we always use oids in context of class to what an object belongs. This means that two tuples from different system tables could have same oid values and everything would work perfectly. I meant we use them in many cases to link entries, and in pg_description for descriptions and lots of other things that may use them in the future for system table use. So, add class' ID (uniq id from pg_class) when linking. Vadim ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] pg_depend
On Wed, 18 Jul 2001, Hiroshi Inoue wrote: Oops I made a mistake. Reference name is needed not an object name, i.e object relid object oid relerence relid reference oid reference name create table a (...); create view view_a as select .. from a; Then we have an pg_depend entry e.g. pg_class_relid oid of the view_a pg_class_relid oid of the table a 'a' the name of the table and so on. drop table a; (unadorned drop). Then the above entry would be changed to pg_class_relid(unchanged) oid of the view_s(unchagned) pg_class_relid(unchanged) InvalidOid 'a' the name of the table(unchanged) create table a (...); Then the pg_depend entry would be pg_class_relid(unchanged) oid of the view_s(unchagned) pg_class_relid(unchanged) the oid of the new table a 'a' the name of the table(unchanged) This step I disagree with. Well, I disagree with the automated aspect of the update. How does postgres know that the new table a is sufficiently like the old table that it should be used? A way the DBA could say, yeah, restablish that, would be fine. Which is better, a view which is broken as the table it was based off of was dropped (even though there's a table of the same name now) or a view which is broken because there is now a table whose name matches its old table's name, but has different columns (either names or types)? I'd say #1. Take care, Bill ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian wrote: If you want to make oids optional on user tables, we can vote on that. Let's vote. I'm proposing optional oids for 2-3 years, so you know how I'll vote -:) OK, we need to vote on whether Oid's are optional, and whether we can have them not created by default. I don't love current OIDs. However they have lived in PostgreSQL's world too long and few people have pointed out that there's no magic around OIDs. I agree to change OIDs to be per class but strongly object to let OIDs optional. It's a big pain for generic applications to lose OIDs. In fact I'm implementing updatable cursors in ODBC using OIDs and Tids. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Ross J. Reedstrom [EMAIL PROTECTED] writes: On Wed, Jul 18, 2001 at 04:06:28PM -0400, Tom Lane wrote: My thought is to make OID generation optional on a per-table basis, and disable it on system tables that don't need unique OIDs. (OID would read as NULL on any row for which an OID wasn't generated.) How about generalizing this to user defineable system attributes? OID would just be a special case: it's really just a system 'serial' isn't it? Hmm. Of the existing system attributes, OID is the only one that's conceivably optional --- ctid,xmin,xmax,cmin,cmax are essential to the functioning of the system. (tableoid doesn't count here, since it's a virtual attribute that doesn't occupy any storage space on disk, and thus making it optional wouldn't buy anything.) So there's no gain to be seen in that direction. In the other direction, I have no desire to buy into adding creation timestamp or anything else in this go-round. Maybe sometime in the future. BTW, I'm not intending to change the on-disk format of tuple headers; if no OID is assigned to a row, the OID field will still be there, it'll just be 0. Given that it's only four bytes, it's probably not worth dealing with a variable header format to suppress the space usage. (On machines where MAXALIGN is 8 bytes, there likely wouldn't be any savings anyway.) I wouldn't much care for dealing with a variable tuple header format to support creation timestamp either, and that leads to the conclusion that it's just going to be a user field anyway. People who need it can do it with a trigger ... I'm not sure the special behavior of returning NULL for oid on a table without one is going to be useful: any client code that expects everything to have an oid is unlikely to handle NULL better than an error. Well, I can see three possible choices: return NULL, return zero, or don't create an OID entry in pg_attribute at all for such a table (I *think* that would be sufficient to prevent people from accessing the OID column, but am not sure). Of these I'd think the first is least likely to break stuff. However, you might be right that breaking stuff is preferable to the possibility of an app that thinks it knows what it's doing causing major data lossage because it doesn't. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: What do we do with other columns that need descriptions and don't have oid column. Like what? Depends what other system tables you are intending to remove oid's for? -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: I don't love current OIDs. However they have lived in PostgreSQL's world too long and few people have pointed out that there's no magic around OIDs. I agree to change OIDs to be per class but strongly object to let OIDs optional. Uh ... what? I don't follow what you are proposing here. I couldn't think of the cases that we need database-wide uniqueness. So the uniqueness of OIDs could be only within a table. But I object to the option that tables could have no OIDs. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: What do we do with other columns that need descriptions and don't have oid column. Like what? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: What do we do with other columns that need descriptions and don't have oid column. Like what? Depends what other system tables you are intending to remove oid's for? Nothing that requires a description ;-) regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
From: Tom Lane [EMAIL PROTECTED] Subject: OID wraparound (was Re: [HACKERS] pg_depend) Date: Wed, 18 Jul 2001 13:52:45 -0400 Message-ID: [EMAIL PROTECTED] Bruce Momjian [EMAIL PROTECTED] writes: Yikes, I am not sure we are ready to make oids optional. We've discussed it enough, it's time to do it. I have an ulterior plan here: I want 7.2 not to have any limitations that prevent it from being used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed now, or nearly. What about pg_log? It will easily become a huge file. Currently the only solution is re-installing whole database, that is apparently unacceptable for very big installation like 1TB. The other stumbling blocks for continuous runs are OID wraparound and XID wraparound. We've got unique indexes on OIDs for all system catalogs that need them (we were short a couple as of 7.1, btw), but OID wrap is still likely to lead to unwanted duplicate key failures. So we still need a way to reduce the system's appetite for OIDs. In a configuration where OIDs are used only where *necessary*, it'd be a long time till wrap. I also intend to do something about XID wrap next month... So are we going to remove OID? I see following in the SQL99 draft (not sure it actually becomes a part of the SQL99 standard, though). Can we implement the Object identifier without the current oid mechanism? - 4.10 Object identifier An object identifier OID is a value generated when an object is created, to give that object an immutable identity. It is unique in the known universe of objects that are instances of abstract data types, and is conceptually separate from the value, or state, of the instance. The object identifier type is described by an object identifier type descriptor. An object identifier type descriptor contains: - an indication that this is an object identifier type; and - the name of the abstract data type within which the object identifier type is used. The object identifier type is only used to define the OID pseudo- column implicitly defined in object ADTs within an ADT definition. ___ An OID literal exists for an object identifier type only if the associated abstract data type was defined WITH OID VISIBLE. The OID value is materialized as a character string with an implementation- defined length and character set SQL_TEXT. - Will we have cheap 64-bit oids by the time oid wraparound becomes an issue? No, we won't, because OID wrap is an issue already for any long-uptime installation. (64-bit XIDs are not a real practical answer either, btw.) What's wrong with 64-bit oids (except extra 4bytes)? -- Tatsuo Ishii ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: What do we do with other columns that need descriptions and don't have oid column. Like what? Depends what other system tables you are intending to remove oid's for? Nothing that requires a description ;-) You are a sly one. :-) -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Idea: recycle WAL segments, don't delete/recreate 'em
Of course not. As mentioned, you'd probably archive them with your backup(s). You mean the nigthly backup? Why not do a pg_dump and be done with it. But the purpose of point-in-time recovery is to restore your backup and then use the WAL to bring the backed up image up to a more current version. My point was that the WAL logs are going to be archived after the backup occurs, right? From the text below, I see you are addressing that. A possible solution (as I mentioned before)) is to have 2 methods of logging available: circular and forward-recoverable. When a database is created, the creator selects which type of logging to perform. The log segments are exactly the same, only the recycling method is different. Will not fly. We need a solution that is flexible. Could you expand on that a little (ie. flexible in which way). Offering the user a choice of two is more flexible than offering no choice. We normally don't give users choices unless we can't come up with a win-win solution to the problem. In this case, we could just query to see if the WAL PIT archiver is running and handle tune reuse of log segments on the fly. In fact, my guess is that the PIT archiver will have to tell the system when it is done with WAL logs anyway. But this could be a win-win situation. If a user doesn't not care about point-in-time recovery, circular logs can be used. When a database is created, a configurable number of log segments are allocated. The database uses those logs in a cyclic manner. No new log segments need to be created under normal use. Automatic reuse. A database requiring point-in-time functionality will log very similar to the method in place today. New log segments will be created when needed. Basically, when the user asks for point-in-time, we can then control how we recycle the logs, right? Hmmm... the more I look at this, the more interested I become. My assumption is that once a log is full the point-in-time recovery daemon will copy that off somewhere, either to a different disk, tape, or over the network to another machine. Once it is done making a copy, the WAL log can be recycled, right? Am I missing something here? Ok... I wasn't thinking of having a point-in-time daemon. Some other databases provide, for lack of a better term, user exits to allow user defined scripts or programs to be called to perform log segment archiving. This archiving is somewhat orthogonal to point-in-time recovery proper. Yep, once the archiving is complete, you can do whatever you want with the local log segment. We will clearly need something to transfer these WAL logs somewhere else, and it would be nice if it could be easily configured. I think a PIT logger daemon is the only solution, especially since tape/network transfer could take a long time. It would be forked by the postmaster so would cover all users and databases. Actually, it would be better if the entire logger was split out into it's own process like the large commercial databases. Archiving the log segments would just be one of the many functions of the logger process. Just a thought. I think we already have a daemon that does checkpoints. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
[HACKERS] Visit to Red Hat Canada
FYI, I will be visiting Red Hat engineers in Toronto tomorrow (Thursday). I will be back online Friday. I should also mention that Jan, Tom, and I will be at the O'Reilly conference all next week. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Tatsuo Ishii [EMAIL PROTECTED] writes: What about pg_log? It will easily become a huge file. Currently the only solution is re-installing whole database, that is apparently unacceptable for very big installation like 1TB. That's part of the XID wraparound issue, which is a separate discussion... but yes, I want to do something about that for 7.2 also. So are we going to remove OID? No, only make it optional for user tables. I see following in the SQL99 draft (not sure it actually becomes a part of the SQL99 standard, though). Can we implement the Object identifier without the current oid mechanism? As near as I can tell, SQL99's idea of OIDs has little to do with ours anyway. Note that they want to assign an OID to an instance of an abstract data type. Thus, if you created a table with several columns each of which is one or another kind of ADT, then each column value would contain an associated OID --- the OID is assigned to each value, not to table rows. My suspicion is that SQL99-style OIDs would be implemented as a separate counter, and would be 8 bytes from the get-go. What's wrong with 64-bit oids (except extra 4bytes)? Portability, mostly. I'm not ready to tell platforms without 'long long' that we don't support them at all anymore. If they don't have int8, or someday they don't have SQL99 OIDs, that's one thing, but zero functionality is something else. I'm also somewhat concerned about the speed price of widening Datum to 8 bytes on machines where that's not a well-supported datatype --- note that we'll pay for that almost everywhere, not only in Oid manipulations. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
What's wrong with 64-bit oids (except extra 4bytes)? Portability, mostly. Oh, there's one other small problem: breaking the on-the-wire protocol. We send OIDs as column datatype identifiers, so an 8-byte-OID backend would not interoperate with clients that didn't also think OID is 8 bytes. Aside from client/server compatibility issues, that raises the portability ante a good deal --- not only your server machine has to have 'long long' support, but so do all your application environments. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] pg_depend
Bill Studenmund wrote: On Wed, 18 Jul 2001, Hiroshi Inoue wrote: Oops I made a mistake. Reference name is needed not an object name, i.e object relid object oid relerence relid reference oid reference name create table a (...); create view view_a as select .. from a; Then we have an pg_depend entry e.g. pg_class_relid oid of the view_a pg_class_relid oid of the table a 'a' the name of the table and so on. drop table a; (unadorned drop). Then the above entry would be changed to pg_class_relid(unchanged) oid of the view_s(unchagned) pg_class_relid(unchanged) InvalidOid 'a' the name of the table(unchanged) create table a (...); Then the pg_depend entry would be pg_class_relid(unchanged) oid of the view_s(unchagned) pg_class_relid(unchanged) the oid of the new table a 'a' the name of the table(unchanged) This step I disagree with. Well, I disagree with the automated aspect of the update. How does postgres know that the new table a is sufficiently like the old table that it should be used? A way the DBA could say, "yeah, restablish that," would be fine. You could DROP a table with CASCADE or RESTRICT keyword if you hate the behavior. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[HACKERS] Re: OID wraparound (was Re: pg_depend)
At 06:10 PM 18-07-2001 -0400, Lamar Owen wrote: applications :-) I guess I'll just need to switch to proper SERIALs and PRIMARY KEYs. Of course, if I wanted to be stubborn, I'd just use the GUC option to enable OIDs system-wide by default The default 32 bit serial primary key isn't immune to roll overs either. I doubt it'll affect my stuff, but it'll affect others. Once you talk about storing petabytes or terabytes of data, 32 bits might not be enough. Cheerio, Link. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Didn't know about that one, at least from the reading of the docs... Thanks, You answered the question. I knew OID's weren't unique, but they are likely to be able to distinguish between 2 rows in the same table. Maybe ctid needs to be documented better? LER Original Message On 7/18/01, 4:32:28 PM, Tom Lane [EMAIL PROTECTED] wrote regarding Re: OID wraparound (was Re: [HACKERS] pg_depend) : Bruce Momjian [EMAIL PROTECTED] writes: Also, without OID's, how do you fix EXACT duplicate records that happen by accident? How about tid's? SELECT tid FROM tab1. SELECT ctid, actually, but that is still the fallback. (Actually it always was --- OIDs aren't necessarily unique either, Larry.) regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] PQexec() 8191 bytes limit and text fields
Hi... The problem is, I compiled it myself from original PostgreSQL version 7.12 C sources using Microsoft's Visual C++ 6.0. I had to compile it because I add a function to free the handlers returned from PQnotifies(), or I would have a memory leak. The resulting libpq.dll seems ok in everything but this issue... I guess I'll do it again, after checking the sources :) Other people reported me they send large queries with no problems, so I guess it should really be a problem of mine... Best Regards, Steve Howe - Original Message - From: Tom Lane [EMAIL PROTECTED] To: Steve Howe [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, July 18, 2001 1:14 PM Subject: Re: [HACKERS] PQexec() 8191 bytes limit and text fields Steve Howe [EMAIL PROTECTED] writes: Writing my interface application, which use the PQexec library, I came across the PQexec() queries 8191 bytes limit. You must have a very out-of-date library. Time to update. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
I wrote: Tom Lane wrote: Hiroshi Inoue [EMAIL PROTECTED] writes: I don't love current OIDs. However they have lived in PostgreSQL's world too long and few people have pointed out that there's no magic around OIDs. I agree to change OIDs to be per class but strongly object to let OIDs optional. Uh ... what? I don't follow what you are proposing here. I couldn't think of the cases that we need database-wide uniqueness. So the uniqueness of OIDs could be only within a table. But I object to the option that tables could have no OIDs. It seems that I'm the only one who objects to optional OIDs as usual:-). IMHO OIDs are not for system but for users. OIDs have lived in PostgreSQL world from the first(???). Isn't it sufficiently long for users to believe that OIDs are unique (at least per table) ? As I mentioned already I'm implementing updatable cursors in ODBC and have half done it. If OIDs would be optional my trial loses its validity but I would never try another implementation. regards, Hiroshi Inoue ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Lamar Owen [EMAIL PROTECTED] writes: However, the utility of INSERT returning a unique identifier to the inserted row needs to be addressed -- I would prefer it return the defined PRIMARY KEY value for the tuple just inserted, if a PRIMARY KEY is defined. If no PRIMARY KEY is defined, return a unique identifier (even a temporary one like the ctid) so that I have that information for use later in the application. The utility of that feature should not be underestimated. That's something that needs to be thought about, all right. I kinda like the idea of returning the ctid, because it is (a) very low overhead, which is nice for something that the client may not actually need, and (b) the tuple can be retrieved *very* quickly given a tid, much more so than was possible with OID. OTOH, if you want to use a tid you'd best use it right away, before someone else can update the row... The major problem with any change away from returning OID is that it'll break client libraries and apps. How much pain do we want to cause ourselves in that line? Certainly, to return anything besides/instead of OID we'd have to change the FE/BE protocol. IIRC, there are a number of other things pending that require protocol changes, so gathering them all together and updating the protocol isn't necessarily a bad thing. But I don't think we have time for it in the 7.2 cycle, unless we slip the schedule past the beta-by-end-of-August that I believe we're shooting for. Another possibility, given that any app using a feature like this is nonportable anyway, is to extend the INSERT statement along the lines that someone (maybe Larry R? I forget now) proposed before: INSERT INTO foo ... RETURNING x,y,z,... where x,y,z, etc are expressions in the variables of the inserted tuple(s). This could be made to look like a SELECT at the protocol level, which would mean that it wouldn't break client libraries or require a protocol bump, and it's *way* more flexible than any hardwired decision about what columns to return. It wouldn't have any problem with multiple tuples inserted by an INSERT ... SELECT, either. regards, tom lane ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
OID wraparound (was Re: [HACKERS] pg_depend)
Bruce Momjian [EMAIL PROTECTED] writes: Yikes, I am not sure we are ready to make oids optional. We've discussed it enough, it's time to do it. I have an ulterior plan here: I want 7.2 not to have any limitations that prevent it from being used in a true 24x7, up-forever scenario. VACUUM lockouts are fixed now, or nearly. The other stumbling blocks for continuous runs are OID wraparound and XID wraparound. We've got unique indexes on OIDs for all system catalogs that need them (we were short a couple as of 7.1, btw), but OID wrap is still likely to lead to unwanted duplicate key failures. So we still need a way to reduce the system's appetite for OIDs. In a configuration where OIDs are used only where *necessary*, it'd be a long time till wrap. I also intend to do something about XID wrap next month... Do we return unused oids back to the pool on backend exit yet? Since WAL, and that was never a fundamental answer anyway. Will we have cheap 64-bit oids by the time oid wraparound becomes an issue? No, we won't, because OID wrap is an issue already for any long-uptime installation. (64-bit XIDs are not a real practical answer either, btw.) 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] pg_depend
Hiroshi Inoue [EMAIL PROTECTED] writes: BTW, name alone is not a good enough referent for functions... you'd have to store the argument types too. ??? Isn't an entry pg_proc_relid the oid of the function pg_type_relid the oid of an argument type the name of the argument type made ? That's the entry that was dropped, no? Given a pg_depend row pointing at a function named foo, with an OID that no longer exists, how will you tell which of the (possibly many) functions named foo is wanted? regards, tom lane ---(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] MySQL Gemini code
Michael Widenius [EMAIL PROTECTED] writes: Please note that we NEVER have asked NuSphere to sign over copyright of Gemini to us. We do it only for the core server, and this is actually not an uncommon thing among open source companies. For example QT (Trolltech) and Ximian (a lot of gnome applications) Ximian isn't doing a lot of gnome applications, just a few (Evolution springs to mind, and their installer). Signing over copyright to Ximian wouldn't make much sense - GNOME isn't a Ximian project, so they can't dual license it anyway. Assigning over the code is also something that FSF requires for all code contributions. If you criticize us at MySQL AB, you should also criticize the above. This is slightly different - FSF wants it so it will have a legal position to defend its programs: http://www.fsf.org/prep/maintain_6.html If you maintain an FSF-copyrighted package, then you should follow certain legal procedures when incorporating changes written by other people. This ensures that the FSF has the legal right to distribute the package, and the right to defend its free status in court if necessary. Before incorporating significant changes, make sure that the person who wrote the changes has signed copyright papers and that the Free Software Foundation has received and signed them. We may also need a disclaimer from the person's employer. MySQL and TrollTech requires copyright assignment in order to sell non-open licenses. Some people will have a problem with this, while not having a problem with the FSF copyright assignment. I had actually hoped to get support from you guy's at PostgreSQL regarding this. You may have similar experience or at least understand our position. The RedHat database may be a good thing for PostgreSQL, but I am not sure if it's a good thing for RedHat or for the main developers to PostgreSQL. This isn't even a remotely similar situation: * For MySQL, the scenario is that a company made available an open version of its product while continuing to sell it under other licenses. * For PostgreSQL, it has been a long living project which spawned companies which then hired some of the core developers. We're not reselling someone elses product with minor enhancements (companies have been known to be doing that to products we create), we're selling support and working on additions to an open project. That may make it harder for the companies now employing the core developers (or may help, as PostgreSQL gets more much deserved publicity and technical credit), but doesn't violate the project's licenses and a company's trademark the way NuSphere did with MySQL. Anyway, I think that we open source developers should stick together. We may have our own disagreements, but at least we are working for the same common goal (open source domination). If you ever need any support from us regarding the RedHat database,, please contact me personally about this. Red Hat is firmly committed to open source, and is definitely a big open source developer. -- Trond Eivind Glomsrød Red Hat, Inc. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
Philip Warner [EMAIL PROTECTED] writes: At 11:38 18/07/01 -0400, Tom Lane wrote: I'd just make the dependency be from view_a to a and keep things simple. What's so wrong with recompiling the view for *every* change of the underlying table? Not a problem for views, but when you get to constraints on large tables, re-evaluating all the constraints unnecessarily could be a nightmare, and especially frustrating when you just dropped an irrelevant attr. Huh? You seem to be thinking that we'd need to re-check the constraint at each row of the table, but I don't see why we'd need to. I was just envisioning re-parsing the constraint source text. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Larry Rosenman [EMAIL PROTECTED] writes: Maybe ctid needs to be documented better? I think it's documented about as well as OID is, actually --- see http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/sql-syntax-columns.html which AFAIR is the only formal documentation of any of the system columns. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: OID wraparound (was Re: [HACKERS] pg_depend)
Also, without OID's, how do you fix EXACT duplicate records that happen by accident? How about tid's? SELECT tid FROM tab1. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] pg_depend
Philip Warner [EMAIL PROTECTED] writes: I think any deisgn needs to cater for attr dependencies. eg. I don't really see a need to recognize dependencies at finer than table level. I'd just make the dependency be from view_a to a and keep things simple. What's so wrong with recompiling the view for *every* change of the underlying table? What about other objects. Foreign keys? Serial? We could support attr-level dependencies within the proposed pg_depend layout if we made pg_attribute one of the allowed object categories. However, I'd prefer not to make OID of pg_attribute rows be a primary key for that table (in the long run I'd like to not assign OIDs at all to pg_attribute, as well as other tables that don't need OIDs). So the better way to do it would be to make the pg_depend entries include attribute numbers. But I really think this is unnecessary complexity. I liked the pg_attribute references for some uses. I agree doing that for a view seems overly complex. I don't see any value in dropping oid from pg_attribute. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(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] Idea: recycle WAL segments, don't delete/recreate 'em
Bruce Momjian wrote: Yes, but in a very roundabout way (or so it seems). The main point that I was trying to illustrate was that if a database supports point-in-time recovery, recycling of the only available log segments is a bad thing. And, yes, in practice if you have point-in-time recovery enabled you better archive your logs with your backup to ensure that you can roll forward as expected. I assume you are not going to do point-in-time recovery by keeping all the WAL segments around on the same disk. Of course not. As mentioned, you'd probably archive them with your backup(s). You mean the nigthly backup? Why not do a pg_dump and be done with it. But the purpose of point-in-time recovery is to restore your backup and then use the WAL to bring the backed up image up to a more current version. A possible solution (as I mentioned before)) is to have 2 methods of logging available: circular and forward-recoverable. When a database is created, the creator selects which type of logging to perform. The log segments are exactly the same, only the recycling method is different. Will not fly. We need a solution that is flexible. Could you expand on that a little (ie. flexible in which way). Offering the user a choice of two is more flexible than offering no choice. We normally don't give users choices unless we can't come up with a win-win solution to the problem. In this case, we could just query to see if the WAL PIT archiver is running and handle tune reuse of log segments on the fly. In fact, my guess is that the PIT archiver will have to tell the system when it is done with WAL logs anyway. But this could be a win-win situation. If a user doesn't not care about point-in-time recovery, circular logs can be used. When a database is created, a configurable number of log segments are allocated. The database uses those logs in a cyclic manner. No new log segments need to be created under normal use. Automatic reuse. A database requiring point-in-time functionality will log very similar to the method in place today. New log segments will be created when needed. Hmmm... the more I look at this, the more interested I become. My assumption is that once a log is full the point-in-time recovery daemon will copy that off somewhere, either to a different disk, tape, or over the network to another machine. Once it is done making a copy, the WAL log can be recycled, right? Am I missing something here? Ok... I wasn't thinking of having a point-in-time daemon. Some other databases provide, for lack of a better term, user exits to allow user defined scripts or programs to be called to perform log segment archiving. This archiving is somewhat orthogonal to point-in-time recovery proper. Yep, once the archiving is complete, you can do whatever you want with the local log segment. We will clearly need something to transfer these WAL logs somewhere else, and it would be nice if it could be easily configured. I think a PIT logger daemon is the only solution, especially since tape/network transfer could take a long time. It would be forked by the postmaster so would cover all users and databases. Actually, it would be better if the entire logger was split out into it's own process like the large commercial databases. Archiving the log segments would just be one of the many functions of the logger process. Just a thought. Cheers, Patrick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] pg_depend
Hiroshi Inoue [EMAIL PROTECTED] writes: Reference name is needed not an object name, Only if we want to support the notion that drop-and-recreate-with-same-name means that references from other objects should now apply to the new object. I do not think that that's really a good idea, at least not without a heck of a lot of compatibility checking. It'd be way too easy to create cases where the properties of the new object do not match what the referring object expects. The majority of the cases I've heard about where this would be useful are for functions, and we could solve that a lot better with an ALTER FUNCTION command that allows changing the function body (but not the name, arguments, or result type). BTW, name alone is not a good enough referent for functions... you'd have to store the argument types too. I assume the name was only for reference use so you could give the user an idea of what is missing. Clearly you don't use that to recreate anything, or I hope not. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026 ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [HACKERS] dependent dependants
[ way off topic, but I can't resist ] [EMAIL PROTECTED] (Nathan Myers) writes: For the record: http://www.lineone.net/dictionaryof/englishusage/d0081889.html dependent or dependant Dependent is the adjective, used for a person or thing that depends on someone or something: Admission to college is dependent on A-level results. Dependant is the noun, and is a person who relies on someone for financial support: Do you have any dependants? In order of increasing heft, my dictionaries have: Webster's New Collegiate: no entry for dependant at all. Random House: dependant is defined with a one-word entry: dependent, for both noun and adjective. OED: entries for both dependant and dependent, but it says now usually spelt [dependent]. Apparently the spellings were once more- or-less interchangeable. Not being an eighteenth-century person, to me dependant looks just plain wrong. I'd never spell it that way, for either noun or adjective. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
