Skip site navigation (1) Skip section navigation (2)

Re: file oids

From: Volkan YAZICI <volkan(dot)yazici(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Re: file oids
Date: 2005-04-28 10:52:29
Message-ID: 7104a737050428035238c3efc8@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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.

In response to

pgsql-novice by date

Next:From: Jessica DittDate: 2005-04-28 12:11:29
Subject: Problem with DirectFunctionCall3(array_in,...)
Previous:From: CimaDate: 2005-04-28 04:51:59
Subject: file oids

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group