Re: file oids

Hi,

On 4/28/05, Cima <ruel(dot)cima(at)facinf(dot)uho(dot)edu(dot)cu> wrote:
> i understand that the oids are descouraged as primary keys becuase of
> possible duplication and possble lack of uniqueness.

As written in documentation:
The oid type is currently implemented as an unsigned four-byte integer.
Therefore, it is not large enough to provide database-wide uniqueness in
large databases, or even in large individual tables. So, using a user-created
table's OID column as a primary key is discouraged.
[http://www.postgresql.org/docs/8.0/interactive/datatype-oid.html]

> what has me worried now is that i'm storing files in my database and
> referencing the oids to these files in my tables. how do i treat this
> possible problem?

Here's a short example:

-- _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
-- _/_/_/_/_/_/_/_/_/_/ HEAD _/_/_/_/_/_/_/_/_/_/
-- _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

-- Creating products table
=> CREATE TABLE products (proname varchar NOT NULL) WITH OIDS;
=> COPY products FROM stdin;
VIC Electro
Fender Electro
Ibanez Acoustic
Slammer Bass
\.

-- Chosing OIDs as primary key
=> ALTER TABLE products ADD CONSTRAINT products_oid_pkey PRIMARY KEY (oid);

-- Creating sales table that references to products table.
=> CREATE TABLE sales (
sid integer REFERENCES products (oid),
instore bool
) WITHOUT OIDS;

-- Inserting some items to sales table
=> SELECT oid FROM products;
oid
-------
18193
18194
18195
18196
(4 rows)
=> COPY sales FROM stdin;
18193 t
18195 f
\.

-- After that schema plan, we decided to create a new column
-- in products table and use it for referencing

-- Before creating a new column, i need to duplicate the table to
-- preserve old OID values. (ALTER will cause OID values to be changed.)
SELECT oid AS old_oid INTO products_with_oids FROM products;

-- Adding proid column to products
=> ALTER TABLE products ADD COLUMN proid serial;

-- Making same for products_with_oids table which will cause both to have
-- same proid values
=> ALTER TABLE products_with_oids ADD COLUMN proid serial;

-- Last status of products table
=> SELECT oid, proid, proname FROM products;
oid | proid | proname
-------+-------+-----------------
18216 | 1 | VIC Electro
18217 | 2 | Fender Electro
18218 | 3 | Ibanez Acoustic
18219 | 4 | Slammer Bass
(4 rows)
-- As you can from above, oid values are changed.

-- Dropping old constraint
=> ALTER TABLE sales DROP CONSTRAINT sales_sid_fkey;

-- Adding new constraint for proid
=> ALTER TABLE products DROP CONSTRAINT products_oid_pkey;
=> ALTER TABLE products ADD CONSTRAINT products_proid_pkey PRIMARY KEY (proid);

CREATE OR REPLACE FUNCTION replace_oids() RETURNS integer AS $$
DECLARE
row record;
BEGIN
FOR row IN
SELECT p.proid, p.proname, s.sid, s.instore FROM sales AS s
LEFT JOIN products_with_oids AS po ON po.old_oid = s.sid
LEFT JOIN products AS p ON p.proid = po.proid
LOOP
UPDATE sales SET sid = row.proid WHERE sid = row.sid;
END LOOP;

RETURN 1;
END
$$ LANGUAGE plpgsql;

-- Lets ROCK!!!
=> SELECT sid, instore FROM sales;
sid | instore
-------+---------
18230 | t
18232 | f
(2 rows)

=> SELECT replace_oids();
replace_oids
--------------
1
(1 row)

=> SELECT sid, instore FROM sales;
sid | instore
-----+---------
1 | t
3 | f
(2 rows)

-- Put a DOT to this messy code!
=> DROP TABLE products_with_oids;
=> ALTER TABLE sales ADD FOREIGN KEY (sid) REFERENCES products (proid);

-- _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/
-- _/_/_/_/_/_/_/_/_/_/ TOE _/_/_/_/_/_/_/_/_/_/_/
-- _/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/

Regards.