Re: [GENERAL] Some questions on user defined types and functions.
Jeffery Collins wrote: I was wondering if anyone could help me with the following questions. They are all related to user defined types and functions. 1. Environment variables in function pathname. We would like to [...] Create your SQL scripts that define the functions in a make step, invoking sed(1) to substitute a constant string with the content of an environment variable. This worked for the past 20 years and I'm sure it's extremely portable. 2. tid assignment - We make extensive use of user defined types. One, fairly painful, thing we have noticed is the following sequence really doesn't work very well: [...] The reason for possibly wanting to do this is to fix a problem with the implementation of the type mytype. You don't need to DROP and reCREATE the functions and type if you just fixed some bug in the C coding. Recompile it, replace the shared object and reconnect to the database. The new backend (you get at reconnect) will load in the new shared module and use the new code. 3. fid assignment - Basically the same question as above but with functions instead of types. If there is an index that uses a function, it appears that you can't drop and re-create the function without blowing away the index definition. Same as 2. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [GENERAL] function language type?
Tom Lane wrote: Can one create tables using the perl, C, or TCL interfaces? Offhand I think this would work out-of-the-box in pltcl and plperl, because they don't do preplanning. This is also why you can do something like "SELECT ... FROM $1" in those PLs and not in plpgsql: they just form the command as a string and then run it through the whole parse/plan process every time. More than that. PL/Tcl supports saved plans, but also supports direct SPI query execution. So it's the decision of the function programmer, which queries to plan and save once and which don't. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[GENERAL] alter table rename does not update constraints
Hi there, I successfully renamed one of the columns of the primary key of one of my tables (it is used in one of the foreign keys, too -- I think, this is the problem). Unfortunately, the constraint (rule system?) wasn't updated by "alter table" -- have a look: -- db=# create table blatt_schaeden ( nummer smallint not null, datum date not null, typ smallint not null, klasse smallint, schaden smallint, primary key (nummer,datum,typ), foreign key (nummer) references blatt, foreign key (typ) references blatt_schadenstyp ); CREATE db=# alter table blatt_schaeden rename column nummer to blatt_nummer; ALTER db=# update blatt_schaeden set klasse = 0 where schaden=0; ERROR: constraint unnamed: table blatt_schaeden does not have an attribute nummer -- Do you consider this as a bug? There are similar items in the TODO file, related to "alter table add" ... Thanks for your attention, Ulf postgreSQL 7.0.2, i386 Linux 2.2.15/Redhat-6.something -- == Ulf Mehlig[EMAIL PROTECTED] Center for Tropical Marine Ecology/ZMT, Bremen, Germany --
[GENERAL] performance help
Hello all, In writing a perl/DBI based application for our customers, we noticed some very intersting behavoir. Against 2 tables, running a select, when you we do a WHERE clause with a lot of items in it ... the query is fast. When we do it with just one or two items, its hugely slower! Enough so that postgres goes to 100Megs of memory to do the query, and the box begins to swap out! Here are the details: Pentium III 550Mhz 128Mb Ram 20Meg IDE disk Postgres 7.0.0 (Should upgrade to 7.0.2?) Installed and compiled by me, but no compiler tricks Here is the SQL thats fast: cw=# SELECT distinct n.news_id, headline, link, to_char(created, 'mm-dd- hh24:mi'), cw-# created FROM news_article_summary n, news_cat nc WHERE n.news_id = nc.news_id AND cw-# created CURRENT_TIMESTAMP-30 AND nc.code_id cw-# in(14,227,326,321,327,4301,5179,5100,585,5175,567,5185,568,5381,5109,554,5621,5 462, cw(# 597,5324,5117,569,5142,570,5327,571,5167,5481,5145) ORDER BY created desc; Here is the SQL that's slow: cw=# SELECT distinct n.news_id, headline, link, cw-# to_char(created, 'mm-dd- hh24:mi'), cw-# created FROM news_article_summary n, news_cat nc cw-# WHERE n.news_id = nc.news_id AND created CURRENT_TIMESTAMP-30 AND cw-# nc.code_id in(4261,4182) ORDER BY created desc; Here's an explain on the above query: NOTICE: QUERY PLAN: Unique (cost=60322.14..60559.66 rows=1900 width=48) - Sort (cost=60322.14..60322.14 rows=19001 width=48) - Nested Loop (cost=0.00..58651.80 rows=19001 width=48) - Seq Scan on news_article_summary n (cost=0.00..416.14 rows=1898 width=36) - Index Scan using news_cat_news_id on news_cat nc (cost=0.00..30.53 rows=10 width=12) EXPLAIN (I ran this, but I'm not really sure what any of it means! Is there an explanation somewhere). Here is news_article_summary and news_cat cw=# \d news_article_summary Table "news_article_summary" Attribute | Type | Modifier --+---+-- news_id | integer | not null headline | varchar(255) | brief| varchar(4000) | top_of_news | varchar(1)| urgent | varchar(1)| created | timestamp | link | varchar(255) | region_id| integer | ftfl | varchar(1)| service_mark | varchar(1)| syndication_file | varchar(12) | ufs_file | varchar(16) | Indices: news_article_summary_news_id, x1 Constraint: (news_id NOTNULL) cw=# \d news_cat Table "news_cat" Attribute | Type | Modifier ---+--+-- news_id | numeric(7,0) | not null code_id | numeric(7,0) | not null Indices: news_cat_code_id, news_cat_news_id Let me know if more info would be helpfull... I'd appreciate any pointers anyone could give me, I'm new to this RDBMS stuff. Oh,here are my postmaster options PGOPTS="-B 256 -N 64 -S 2048" Thanks! -ernie
Re: [GENERAL] alter table rename does not update constraints
Ulf Mehlig wrote: Hi there, I successfully renamed one of the columns of the primary key of one of my tables (it is used in one of the foreign keys, too -- I think, this is the problem). Unfortunately, the constraint (rule system?) wasn't updated by "alter table" -- have a look: -- db=# create table blatt_schaeden ( nummer smallint not null, datum date not null, typ smallint not null, klasse smallint, schaden smallint, primary key (nummer,datum,typ), foreign key (nummer) references blatt, foreign key (typ) references blatt_schadenstyp ); CREATE db=# alter table blatt_schaeden rename column nummer to blatt_nummer; ALTER db=# update blatt_schaeden set klasse = 0 where schaden=0; ERROR: constraint unnamed: table blatt_schaeden does not have an attribute nummer -- Hallo Ulf, wie geht's? Still mucking around with your leaf's? What's the boy measuring snails doing? Still aggregating them or is he totally slimed? Where the views we developed of help for him? Hope you don't mind, but these where the funniest samples for using aggregates and views I ever heard from. I'm still using them when it boils down to tell fun stories about work. :-) Do you consider this as a bug? There are similar items in the TODO file, related to "alter table add" ... This is a buggy feature. The refint triggers are defined in a way, that they know the attribute names to look for from their trigger arguments. But these trigger definitions aren't updated at RENAME COLUMN time. We need to change that to an OID based system, so they are out of the way for column name changes. We know how to fix it, but time is a finite resource... Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
[GENERAL] pg_dump error
Hello, with pg_dump testdb db.out there is an errorline getTypes(): SELECT failed. Explanation from backend: 'ERROR: cache lookup for userid 201 failed any Idea marc
Re: [GENERAL] adding column constraint
alter table cust add constraint fk_cust_bd_id foreign key (bd_id) references bill_dist (bd_id); At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: Can someone give an example of how to add a foreign key constraint to an existing table? (Pgsql and the man page differ and neither syntax seems to work.) I've tried: alter table mytable add constraint col foreign key references reftable with no luck. Tim -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/
[GENERAL] drop foreign key
hello, after one has put a foreign key constraint on a table, how can one drop that constraint??? mikeo
Re: [GENERAL] adding column constraint
Hmmm... I got it to work, but using a slightly different syntax. Let me see if I understand your example: ALTER TABLE cust# the table to be altered ADD CONSTRAINT fk_cust_bd_id # the column in cust to add the constraint? FOREIGN KEY (bd_id) # foreign key in bill_dist? REFERENCES bill_dist (bd_id); # specifies the column in bill_dist to use? Oddly, I put the foreign table name in place of fk_cust_bd_id and the local column name where you have (bd_id). It seemed to work. Is the identifier after ADD CONSTRAINT a noop? BTW, the problem with the example in the man page is that its impossible to tell which identifiers are table names and which are column names and which column names go with which table, etc. The format above (with comments) would help a lot. (Or use identifiers like the_table_to_be_altered and so on.) Tim mikeo wrote: alter table cust add constraint fk_cust_bd_id foreign key (bd_id) references bill_dist (bd_id); At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: Can someone give an example of how to add a foreign key constraint to an existing table? (Pgsql and the man page differ and neither syntax seems to work.) I've tried: alter table mytable add constraint col foreign key references reftable with no luck. Tim -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/ -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/
Re: [GENERAL] function language type?
Ian Turner [EMAIL PROTECTED] writes: Looking at the source, I see the following parsenodes which are NOT supported by copyObject: Uh, what version of the source are you looking at? Quite a few of those *are* supported. Which of these is it worth supporting? I will implement the necessary _copytype functions. The missing stuff is basically the 600-series node types; any XXXStmt node that you want to be able to use in a plpgsql function needs to be copiable. If you want to support CREATE TABLE you will likely find that some more of the 700-series nodes are also needed for CREATE TABLE infrastructure. It is not worth your trouble to do this unless you are working from current sources (CVS or a recent daily snapshot)... regards, tom lane
Re: [GENERAL] adding column constraint
almost, ALTER TABLE cust# the table to be alteredyes ADD CONSTRAINT fk_cust_bd_id # name of the constraint (see tgconstrname column in pg_trigger) FOREIGN KEY (bd_id) # column in cust to be FK'd to bill_dist REFERENCES bill_dist (bd_id); # specifies the column in bill_dist to use? yes mikeo At 02:26 PM 7/26/00 -0700, Timothy H. Keitt wrote: Hmmm... I got it to work, but using a slightly different syntax. Let me see if I understand your example: ALTER TABLE cust# the table to be altered ADD CONSTRAINT fk_cust_bd_id # the column in cust to add the constraint? FOREIGN KEY (bd_id) # foreign key in bill_dist? REFERENCES bill_dist (bd_id); # specifies the column in bill_dist to use? Oddly, I put the foreign table name in place of fk_cust_bd_id and the local column name where you have (bd_id). It seemed to work. Is the identifier after ADD CONSTRAINT a noop? BTW, the problem with the example in the man page is that its impossible to tell which identifiers are table names and which are column names and which column names go with which table, etc. The format above (with comments) would help a lot. (Or use identifiers like the_table_to_be_altered and so on.) Tim mikeo wrote: alter table cust add constraint fk_cust_bd_id foreign key (bd_id) references bill_dist (bd_id); At 12:57 PM 7/26/00 -0700, Timothy H. Keitt wrote: Can someone give an example of how to add a foreign key constraint to an existing table? (Pgsql and the man page differ and neither syntax seems to work.) I've tried: alter table mytable add constraint col foreign key references reftable with no luck. Tim -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/ -- Timothy H. Keitt National Center for Ecological Analysis and Synthesis 735 State Street, Suite 300, Santa Barbara, CA 93101 Phone: 805-892-2519, FAX: 805-892-2510 http://www.nceas.ucsb.edu/~keitt/
Re: [GENERAL] performance help
Ernie [EMAIL PROTECTED] writes: Here's an explain on the above query: Um, *which* query was that for? And what's the EXPLAIN output for the other query? regards, tom lane
Re: [GENERAL] Some questions on user defined types and functions.
It would seem that it wouldn't break anyone's existing setup, since you couldn't have an env variable in there anyway. (No one really has a directory called $HOME, I hope!) So, perhaps it could just be something in the documentation that has a stern warning about watching your consistency. Caveat hacker and all that. On 26 Jul 2000, at 17:50, Tom Lane wrote: Jeffery Collins [EMAIL PROTECTED] writes: like the following syntax to work: CREATE FUNCTION myfunc(mytype) RETURNS text AS '$HOME/lib/libmyso.so' LANGUAGE 'c': and have the environment variable $HOME "lazy" evaluated. I have looked at the fmgr code and this doesn't look too difficult to add as long as I could get the $HOME past the parser. I have made the changes necessary to allow environment variables to be entered and expanded in file names. Two files had to be changed backend/commands/define.c and backend/utils/fmgr/dfmgr.c. Assuming you are interested in the change, Well, that's a good question. Does anyone else have an opinion on whether this would be a good/bad/indifferent feature? We've seen problems in the past caused by depending on postmaster environment variables (restart the postmaster with different environment than usual, things mysteriously break). So I'm inclined to feel that adding more dependence on them isn't such a hot idea. But I'm not going to veto it if there's interest in the feature from other people. what is the proper way to build a patch file that contains the changes? I have never done this before. "diff -c" against current sources, done so that the correct file pathnames are visible in the diff output; that is, cd to top level of distribution tree and do something like diff -c src/backend/utils/fmgr/dfmgr.c.orig src/backend/utils/fmgr/dfmgr.c Don't forget to include diffs for documentation updates, as well. regards, tom lane -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Some questions on user defined types and functions.
Tom Lane wrote: Jeffery Collins [EMAIL PROTECTED] writes: what is the proper way to build a patch file that contains the changes? I have never done this before. "diff -c" against current sources, done so that the correct file pathnames are visible in the diff output; that is, cd to top level of distribution tree and do something like For the past years I do a cp -R src src.orig after the configure step. Whatever I do in the sources, a diff -cr src src.orig in the toplevel directory gives me a patch I can apply to my CVS checkout. With this I can never forget a single source file touched. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [GENERAL] 4 billion record limit?
THe implications of the OIDs not wrapping are immense. We have some extremely active databases that will easily reach this limit in two or three years. For some applications, such as ecommerce, dumping then reinserting the rows is not an option for large databases due to the 24 hours nature of their work. This is a much more complex problem than it would at first seem as the "tripping up" over old records with low OIDs still presents a problem, yet if the system is changed to try and manage a list of available OIDs, it will then hit performance problems. Simply waiting for 64bit numbers is rather inelegant and also presumes usage parameters for the database... remember Bill Gates saying that he couldn't foresee any usage for more than 64MB of RAM? Besides which, PostgreSQL is the best DB around... there's a high standard to maintain! Some initial ideas: a) The system remains working the way that it does until a configurable cutoff point (% of OIDs remaining), at which point the DBA can either switch on some sort of OID "garbage collection" and take the associated performance hit, or dump the data and reinsert it to pack the OIDs. GARBAGE COLLECTION: b) The system could wrap around, keeping an internal pointer of where it is in the OID chain. It could scan the OIDs sequentially finding the first free OID. It coudl then store that position as the new start point for the next time an OID is needed. c) An OID compression utility could be writen that doesn't require bringing the DB down (but will obviously have a performance it). As running this utilty would be a known impact, provision could be made, or the knock budgeted for and accounted for while not bringing the entire DB to a halt. d) OIDs themselves could form a list. The OID system as it stands now could be the "default mode" but if the OIDs run out, then a new OID list is started. This means the OID usage would then change to work along the same lines that the Intel memory adressing works. Personally I hate it, but it does work. e) OIDs could be scrapped in favour of some other system. Well, that's my two pence worth. Brad Paul Caskey wrote: Tom Lane wrote: Chris Bitmead [EMAIL PROTECTED] writes: Paul Caskey wrote: 1. This implies a hard limit of 4 billion records on a server, right? Basically, yes. It's only a hard limit if your application assumes OIDs are unique. If you don't assume that, then I think it's not a big problem. It's possible (though obviously not especially likely) that you might get OID collisions in the system tables after an OID-counter wraparound. This implies they do wrap around. So they are reused? Chris said no, but you're saying yes. (Maybe they wrap around "by accident", by adding one to MAXINT, which will give zero on an unsigned int, I believe. Will the system choke on zero? Has anyone tested this wraparound?) I will not have 4 billion records in one table or even one database. But on a large server with many databases, it is conceivable to have 4 billion records on one machine. With a lot of insert/delete activity, over a few years, it is certainly conceivable to have 4 billion inserts. If the oids don't wrap, I have a problem. I can ignore it for a long time, but it will loom, like Y2K. :-) Even if they do wrap, if I have some old records lying around with a low OIDs, they will trip me up. Like you said, these are "the outer limits", but I'm thinking ahead. Someone suggested in private that I pg_dump/restore all my data to "repack" the oids which start around 1700 on a fresh database. Thanks for that idea. Also thanks, Tom, for the sanity check Re: terabytes of data with 4 billion records. It's still possible, especially in coming years. It would be a big feather in PG's cap to "fully support" 64-bit platforms such as IRIX and Solaris (finally) and, coming soon to a theater near you, Linux on IA-64. -- Paul Caskey [EMAIL PROTECTED] 505-255-1999 New Mexico Software 5041 Indian School NE Albuquerque, NM 87110 --
RE: [GENERAL] 4 billion record limit?
Simply waiting for 64bit numbers is rather inelegant and also presumes usage parameters for the database... remember Bill Gates saying that he couldn't foresee any usage for more than 64MB of RAM? I've heard this before and I just don't agree. 64MB ram, perhaps, but who is going to need 64 * 2^32? The magnitude of increase is fairly vast! I probably need not mention that a 32 bit value can store up to 4,294,967,296 where a 64 bit number can store a number that is 4,294,967,296 times as big. If 32 bit wasn't enough to keep you happy for more than a few years, a 64 bit oid really should be enough for anyone for long enough that you won't be using the same software/hardware any more. Similar to how a 32 bit unix time in seconds is only good for another ~40 years, but a 64 bit one will keep us going for billions of years. I guess the real issue is rewriting the system so that the type of oid can be chosen at compile time, so you can use whatever damn one you feel like. I would also guess that the majority of systems out there using the latest versions of postgres, already have compiler support for 64 bit integers. So when someone gets around to doing the necessary work, everything will be nice. - Andrew P.S. IMHO if you can't afford to do a drop and reload once in a while, Postgres isn't a good choice at the moment for your application.
Re: [GENERAL] 4 billion record limit?
Tom Lane wrote: Paul Caskey [EMAIL PROTECTED] writes: Tom Lane wrote: It's only a hard limit if your application assumes OIDs are unique. If you don't assume that, then I think it's not a big problem. It's possible (though obviously not especially likely) that you might get OID collisions in the system tables after an OID-counter wraparound. This implies they do wrap around. So they are reused? Chris said no, but you're saying yes. (Maybe they wrap around "by accident", by adding one to MAXINT, which will give zero on an unsigned int, I believe. Will the system choke on zero? Has anyone tested this wraparound?) Yes, yes, and yes ;-). Good answer. :-) Thanks for looking into it. Even if they do wrap, if I have some old records lying around with a low OIDs, they will trip me up. No doubt about it, you're likely to get a few "duplicate key" errors and stuff like that. I'm just observing that it's not likely to be a complete catastrophe, especially not if you don't rely on OIDs to be unique in your user tables. I don't rely on OID uniqueness, but I assumed Postgres does! I don't use the OID for anything; I maintain my own "id" field with a sequence for every table. I can catch a "duplicate key" error in my code, but I'll treat it as if my own "id" caused a problem. It will totally confuse me and my code if there's a "duplicate key" error related to a hidden system field I never create or examine. I won't know if/how to re-insert with a different key to solve the problem. We have talked about offering 8-byte OIDs as a compile-time option, and I think it'll happen eventually, but I'm not holding my breath. Lots of other stuff seems more pressing... Fair enough, and thanks for all your work so far. -- Paul Caskey [EMAIL PROTECTED] 505-255-1999 New Mexico Software 5041 Indian School NE Albuquerque, NM 87110 --
Re: [GENERAL] 4 billion record limit?
Paul Caskey [EMAIL PROTECTED] writes: No doubt about it, you're likely to get a few "duplicate key" errors and stuff like that. I'm just observing that it's not likely to be a complete catastrophe, especially not if you don't rely on OIDs to be unique in your user tables. I don't rely on OID uniqueness, but I assumed Postgres does! Only in the system tables, and not even in all of them. From the system's point of view, there's no real need to assign OIDs to user table rows at all --- so another possible answer is not to do that, unless the user requests it. regards, tom lane
Re: [GENERAL] 4 billion record limit?
brad [EMAIL PROTECTED] writes: snip Simply waiting for 64bit numbers is rather inelegant and also presumes usage parameters for the database... remember Bill Gates saying that he couldn't foresee any usage for more than 64MB of RAM? Besides which, PostgreSQL is the best DB around... there's a high standard to maintain! snip Actually, he was purported to have said that nobody would need more that 640KB or ram, which was the limit of memory on MSDOS. Brings back memories... remember having to juggle the drivers on bootup to plays that game which wanted nearly all of the base memory but you still needed the mouse, soundcard and cdrom access? ;-) -- Prasanth Kumar [EMAIL PROTECTED]
RE: [GENERAL] 4 billion record limit?
Ummm ... I'm a newbie to this list, but hasn't this evolved into a hacker issue? Also, Bill Gates said something along the lines of nobody will ever need more than 640KB of RAM ... which was the usable limit on the old XT's (remember them :) in the early MS-DOS days :) Dave Burbidge Network Administrator -Original Message- snip
RE: [GENERAL] 4 billion record limit?
On Thu, 27 Jul 2000, Dave Burbidge wrote: Also, Bill Gates said something along the lines of nobody will ever need more than 640KB of RAM ... which was the usable limit on the old XT's (remember them :) in the early MS-DOS days :) Actually, I think the quote is an urban legend. Brett W. McCoy http://www.chapelperilous.net/~bmccoy/ --- A girl's conscience doesn't really keep her from doing anything wrong-- it merely keeps her from enjoying it.
Re: [GENERAL] 4 billion record limit?
The Versant ODBMS uses 48 bit oids, and if you do the math I think you'll find that should last you forever. (It uses an additional 16 bits to identify the database, but that's another story.). Any complex scheme to solve this seems like a waste of time. In a couple of years when you are likely to be running out, you'll probably be upgrading your computer to a 64bit one with a newer version of postgres, and then the problem will disappear. brad wrote: THe implications of the OIDs not wrapping are immense. We have some extremely active databases that will easily reach this limit in two or three years. For some applications, such as ecommerce, dumping then reinserting the rows is not an option for large databases due to the 24 hours nature of their work. This is a much more complex problem than it would at first seem as the "tripping up" over old records with low OIDs still presents a problem, yet if the system is changed to try and manage a list of available OIDs, it will then hit performance problems. Simply waiting for 64bit numbers is rather inelegant and also presumes usage parameters for the database... remember Bill Gates saying that he couldn't foresee any usage for more than 64MB of RAM? Besides which, PostgreSQL is the best DB around... there's a high standard to maintain! Some initial ideas: a) The system remains working the way that it does until a configurable cutoff point (% of OIDs remaining), at which point the DBA can either switch on some sort of OID "garbage collection" and take the associated performance hit, or dump the data and reinsert it to pack the OIDs. GARBAGE COLLECTION: b) The system could wrap around, keeping an internal pointer of where it is in the OID chain. It could scan the OIDs sequentially finding the first free OID. It coudl then store that position as the new start point for the next time an OID is needed. c) An OID compression utility could be writen that doesn't require bringing the DB down (but will obviously have a performance it). As running this utilty would be a known impact, provision could be made, or the knock budgeted for and accounted for while not bringing the entire DB to a halt. d) OIDs themselves could form a list. The OID system as it stands now could be the "default mode" but if the OIDs run out, then a new OID list is started. This means the OID usage would then change to work along the same lines that the Intel memory adressing works. Personally I hate it, but it does work. e) OIDs could be scrapped in favour of some other system. Well, that's my two pence worth. Brad Paul Caskey wrote: Tom Lane wrote: Chris Bitmead [EMAIL PROTECTED] writes: Paul Caskey wrote: 1. This implies a hard limit of 4 billion records on a server, right? Basically, yes. It's only a hard limit if your application assumes OIDs are unique. If you don't assume that, then I think it's not a big problem. It's possible (though obviously not especially likely) that you might get OID collisions in the system tables after an OID-counter wraparound. This implies they do wrap around. So they are reused? Chris said no, but you're saying yes. (Maybe they wrap around "by accident", by adding one to MAXINT, which will give zero on an unsigned int, I believe. Will the system choke on zero? Has anyone tested this wraparound?) I will not have 4 billion records in one table or even one database. But on a large server with many databases, it is conceivable to have 4 billion records on one machine. With a lot of insert/delete activity, over a few years, it is certainly conceivable to have 4 billion inserts. If the oids don't wrap, I have a problem. I can ignore it for a long time, but it will loom, like Y2K. :-) Even if they do wrap, if I have some old records lying around with a low OIDs, they will trip me up. Like you said, these are "the outer limits", but I'm thinking ahead. Someone suggested in private that I pg_dump/restore all my data to "repack" the oids which start around 1700 on a fresh database. Thanks for that idea. Also thanks, Tom, for the sanity check Re: terabytes of data with 4 billion records. It's still possible, especially in coming years. It would be a big feather in PG's cap to "fully support" 64-bit platforms such as IRIX and Solaris (finally) and, coming soon to a theater near you, Linux on IA-64. -- Paul Caskey [EMAIL PROTECTED] 505-255-1999 New Mexico Software 5041 Indian School NE Albuquerque, NM 87110 --
