Re: [ADMIN] OID assistance
Spiegelberg, Greg [EMAIL PROTECTED] writes: It's come to our attention that in 14-16 days our OID's will wrap around and we need some advice. Are you actually using the OIDs for anything? If not, don't worry about it. Unless you are actually depending on OIDs to be unique in some of your user tables, wraparound shouldn't matter. The only possible bad consequence is that you might once in a while get a collision in the system tables (eg, a CREATE TABLE fails because the OID generated for the table conflicts with an existing table). The odds of that are small enough that it's probably not going to be as much of a problem as a dump/reload would be. Just retry the transaction and it'll usually work the next time. regards, tom lane ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [ADMIN] OID assistance
Tom Lane wrote: Spiegelberg, Greg [EMAIL PROTECTED] writes: It's come to our attention that in 14-16 days our OID's will wrap around and we need some advice. Are you actually using the OIDs for anything? If not, don't worry about it. We use OID's to store files in the database. Does that qualify? Unless you are actually depending on OIDs to be unique in some of your user tables, wraparound shouldn't matter. The only possible bad consequence is that you might once in a while get a collision in the system tables (eg, a CREATE TABLE fails because the OID generated for the table conflicts with an existing table). The odds of that are small enough that it's probably not going to be as much of a problem as a dump/reload would be. Just retry the transaction and it'll usually work the next time. Retrying the transaction isn't something we want to worry about. Our code doesn't have that capability built into it. I read a mailing list article from a while back and there was mentioned in it the possibility of OID's becoming a INT8. Does this exist in any of the new versions? Greg -- Greg Spiegelberg Product Development Manager Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Technology. Integrity. Focus. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [ADMIN] OID assistance
Greg Spiegelberg [EMAIL PROTECTED] writes: I read a mailing list article from a while back and there was mentioned in it the possibility of OID's becoming a INT8. Does this exist in any of the new versions? Don't hold your breath. Exactly how are you using OIDs to store files? Do you mean you're using large objects? regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] OID assistance
Tom Lane wrote:
Exactly how are you using OIDs to store files? Do you mean you're
using large objects?
The table is
Table public.imgs
Column | Type | Modifiers
--++--
id | bigint | not null default nextval('public.imgs_id_seq'::text)
file | text |
contents | oid|
Indexes: imgs_pkey primary key btree (id)
Data is loaded using INSERT's.
insert into imgs values (1,'/path/to/myfile',lo_import('/path/to/myfile'));
Greg
--
Greg Spiegelberg
Product Development Manager
Cranel, Incorporated.
Phone: 614.318.4314
Fax: 614.431.8388
Email: [EMAIL PROTECTED]
Technology. Integrity. Focus.
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
Re: [ADMIN] OID assistance
Greg Spiegelberg [EMAIL PROTECTED] writes:
Tom Lane wrote:
Exactly how are you using OIDs to store files? Do you mean you're
using large objects?
The table is
Table public.imgs
Column | Type | Modifiers
--++--
id | bigint | not null default nextval('public.imgs_id_seq'::text)
file | text |
contents | oid|
Indexes: imgs_pkey primary key btree (id)
Data is loaded using INSERT's.
insert into imgs values (1,'/path/to/myfile',lo_import('/path/to/myfile'));
Well, put a unique index on the contents column. Better to fail an
insert than to get a conflict of LO OIDs.
If you were on 7.4 or later you could do ALTER TABLE SET WITHOUT OIDS so
you'd not be sucking up OIDs for the table rows themselves. On 7.3 the
only way would be to drop and recreate the larger tables WITHOUT OIDS,
which is probably going to be painful :-(.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [ADMIN] OID assistance
Tom Lane wrote: Well, put a unique index on the contents column. Better to fail an insert than to get a conflict of LO OIDs. Can't do that. Our app won't handle it. If you were on 7.4 or later you could do ALTER TABLE SET WITHOUT OIDS so you'd not be sucking up OIDs for the table rows themselves. On 7.3 the only way would be to drop and recreate the larger tables WITHOUT OIDS, which is probably going to be painful :-(. I figured as much. We're working to automate the dump-restore-alter table process now. This does lead me to 2 questions... first, why is this still an issue and not fixed in the backend where OID's are managed? Second, shouldn't there be something like use WITHOUT OIDS for data warehouses be in the FAQ? Thanks for the quick response. Greg -- Greg Spiegelberg Product Development Manager Cranel, Incorporated. Phone: 614.318.4314 Fax: 614.431.8388 Email: [EMAIL PROTECTED] Technology. Integrity. Focus. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [ADMIN] OID assistance
Greg Spiegelberg [EMAIL PROTECTED] writes: Tom Lane wrote: Well, put a unique index on the contents column. Better to fail an insert than to get a conflict of LO OIDs. Can't do that. Our app won't handle it. Actually, I think the lo_import() will fail anyway, whether you like it or not. There's a unique index on pg_largeobject. This does lead me to 2 questions... first, why is this still an issue and not fixed in the backend where OID's are managed? When you're two major versions behind, you don't have a lot of leeway to complain about why things are still an issue ;-). But the short answer is that making OIDs 8 bytes would permanently break platforms that don't have int64 support, and be a nontrivial performance hit on those where int64 is substantially slower than int32. So I'd say it's a good ways into the future yet. Eventually we'll decide we don't care about 32-bit machines anymore, but not for awhile. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
