Re: file oids - Mailing list pgsql-novice

Hi,

On 4/28/05, Cima <ruel.cima@facinf.uho.edu.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.