OID's

On Nov 15, 2004, at 3:52 PM, Jamie Deppeler wrote:

Hi,

I am planning to use OID for referencing as instead PK -->> FK on
this situation would require alot of tables, OID would seen to nice
solution.

My worry with OID's is when i do SQL dump and rebuild the Database
will OID will change making referencing certain  records impossible.

Don't use OIDS. Just add a nice SERIAL column to the tables you want as
foreign keys (and if you have questions about sequences, check the
FAQ).

OIDS are a system level implementation. They are no longer required
(you can make tables without OIDS) and they may go away someday. Plus,
you avoid nastiness like OID wraparound.

Michael Glaesemann
grzm myrealbox com

(Is it just me, or have there been a slew of these OID posts lately?)

On Nov 15, 2004, at 4:05 PM, Michael Glaesemann wrote:

Just add a nice SERIAL column to the tables you want as foreign keys
(and if you have questions about sequences, check the FAQ).

Erg... SERIALs on the tables as primary keys. Integers on tables
referencing the primary key for foreign keys.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann zei:

OIDS are a system level implementation. They are no longer required
(you can make tables without OIDS) and they may go away someday.

Out of curiosiry: how will we handle blobs once the OID's are gone?

Am Dienstag, 16. November 2004 10:01 schrieb Joolz:

Michael Glaesemann zei:

OIDS are a system level implementation. They are no longer required
(you can make tables without OIDS) and they may go away someday.

Out of curiosiry: how will we handle blobs once the OID's are gone?

They won't go away. This is one reason.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

On Tue, 16 Nov 2004 08:01 pm, Joolz wrote:

Michael Glaesemann zei:

OIDS are a system level implementation. They are no longer required
(you can make tables without OIDS) and they may go away someday.

Out of curiosiry: how will we handle blobs once the OID's are gone?

I would guess bytea would become the standard for blob use. The size
is limited to about 1G compressed, but I would guess most people don't
store 2G files in there DB at the moment, or have that much ram to be
able to handle a value that big.

On Nov 16, 2004, at 6:42 PM, Peter Eisentraut wrote:

Am Dienstag, 16. November 2004 10:01 schrieb Joolz:

Michael Glaesemann zei:

OIDS are a system level implementation. They are no longer required
(you can make tables without OIDS) and they may go away someday.

Out of curiosiry: how will we handle blobs once the OID's are gone?

They won't go away. This is one reason.

Peter,

You sound pretty certain. I can imagine there might be a way to handle
BLOBs without OIDs. I'm not saying that I know what it is, but I
recognize the possibility.

Michael Glaesemann
grzm myrealbox com

Am Dienstag, 16. November 2004 10:52 schrieb Michael Glaesemann:

You sound pretty certain. I can imagine there might be a way to handle
BLOBs without OIDs. I'm not saying that I know what it is, but I
recognize the possibility.

There are certainly ways to handle this. But no one has seriously proposed
getting rid of OIDs and presented a plan for fixing all the other holes that
move would leave.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

There are certainly ways to handle this. But no one has seriously proposed
getting rid of OIDs and presented a plan for fixing all the other holes that
move would leave.

Right; I certainly have no intention of trying to remove OIDs any time
soon. However, I _will_ be proposing that we set default_with_oids to
false by default in 8.1, per previous discussion on pgsql-hackers. Among
other things, this will mean that CREATE TABLE will not include OIDs by
default: if you want OIDs on a particular table, you can either specify
WITH OIDS explicitly or change the default_with_oids configuration
parameter.

-Neil

Michael Glaesemann wrote:

On Nov 15, 2004, at 3:52 PM, Jamie Deppeler wrote:

Hi,

I am planning to use OID for referencing as instead PK -->> FK on
this situation would require alot of tables, OID would seen to nice
solution.

My worry with OID's is when i do SQL dump and rebuild the Database
will OID will change making referencing certain records impossible.

Don't use OIDS. Just add a nice SERIAL column to the tables you want as
foreign keys (and if you have questions about sequences, check the FAQ).

OIDS are a system level implementation. They are no longer required (you
can make tables without OIDS) and they may go away someday. Plus, you
avoid nastiness like OID wraparound.

Michael Glaesemann
grzm myrealbox com

(Is it just me, or have there been a slew of these OID posts lately?)

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

I have read about oid wraparound in many messages but I don't understand
when it happens and when it is dangerus for my tables.

It affects developers that uses OIDS in their queryies?
What about database and tables (not total or total) disappearences?

Kostis.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A little bit OT, but:
is there a way of removing duplicate rows in a table without OIDs?

Mit freundlichem Gruß / With kind regards
Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFBmeVA1Xdt0HKSwgYRAklNAJ4l0KtMVF2Tkhx5ZgyPR38LHXd/LACeNk4q
mwf/f5rI7VdckPfgfUotnSc=
=qpV0
-----END PGP SIGNATURE-----

On Nov 16, 2004, at 8:32 PM, Holger Klawitter wrote:

A little bit OT, but:
is there a way of removing duplicate rows in a table without OIDs?

One method that I believe works (haven't tried it in a while):

BEGIN;

CREATE TEMP TABLE foo_temp AS
SELECT DISTINCT bar, bat, baz
FROM foo;

TRUNCATE foo;

INSERT INTO TABLE foo (bar, bat, baz)
SELECT bar, bat, baz
FROM foo_temp;

DROP TABLE foo_temp;

COMMIT;

There are others. Googling would probably reveal some.

Michael Glaesemann
grzm myrealbox com

On Tue, 16 Nov 2004 08:01 pm, Joolz wrote:

Michael Glaesemann zei:

OIDS are a system level implementation. They are no longer required
(you can make tables without OIDS) and they may go away someday.

Out of curiosiry: how will we handle blobs once the OID's are gone?

I would guess bytea would become the standard for blob use. The size
is limited to about 1G compressed, but I would guess most people don't
store 2G files in there DB at the moment, or have that much ram to be
able to handle a value that big.

Bytea cannot be a replacement of large objects Besides the 1G limit of
bytea, storing 1G requires over 2G RAM is a serious problem.

To be honest I don't understand why people hate OIDs. Most of problems
with OID just come from the fact that it's a 32bit. Once extending it
64bit, all problems would go away.

However using OIDs with large object is not a very good idea IMO. I
think using user specified key for large objects would be better.
--
Tatsuo Ishii

Hi,

OIDS are a system level implementation. They are no longer required
(you can make tables without OIDS) and they may go away someday.

What about tableoids? Are they from the same generator as row oids (and
hence may suffer wrap-around)? Or are they unique across the db?

I ask because I'm currently using them to join a single table to rows in
arbitrary tables, something like:

+-------------+
| JOINME |
+-------------+
| foreign_oid |
| foreign_id |
| ... |
+-------------+

Where foreign_oid is the tableoid of the table and foreign_id is the
(serial, not oid) id of the row in that table.

I dunno if it's bad design, but it's darn handy.

M

If you have a primary key you can self join the table on the rows that would
define a table as duplicate and delete the one with a higher primary key
field. If the table is related to other tables (ie the pk is an fk in
another table) you have to make sure you update all the rows to point to the
new key.

If there is no pkey, then you would do a select distinct into a temp table
as was suggested by Michael in the post above mine

"Holger Klawitter" <lists@klawitter.de> wrote in message
news:200411161232.17642.lists@klawitter.de...
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A little bit OT, but:
is there a way of removing duplicate rows in a table without OIDs?

Mit freundlichem Gru� / With kind regards
Holger Klawitter
- --
lists <at> klawitter <dot> de
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.2 (GNU/Linux)

iD8DBQFBmeVA1Xdt0HKSwgYRAklNAJ4l0KtMVF2Tkhx5ZgyPR38LHXd/LACeNk4q
mwf/f5rI7VdckPfgfUotnSc=
=qpV0
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

On Tue, 2004-11-16 at 12:32, Holger Klawitter wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

A little bit OT, but:
is there a way of removing duplicate rows in a table without OIDs?

ALTER TABLE ... ADD COLUMN myoid int;
CREATE TEMP SEQUENCE myoidsequence;
UPDATE ... SET myoid=nextval('myoidsequence');

then do the usual

SELECT a.* FROM ... a, ... b WHERE a.something=b.something AND
a.myoid<b.myoid;

Dance and if you are done with it,
ALTER TABLE ... DROP COLUMN myoid;
DROP TEMP SEQUENCE myoidsequence;

HTH
Tino

Peter Eisentraut zei:

Am Dienstag, 16. November 2004 10:01 schrieb Joolz:

Michael Glaesemann zei:

OIDS are a system level implementation. They are no longer

required

(you can make tables without OIDS) and they may go away someday.

Out of curiosiry: how will we handle blobs once the OID's are
gone?

They won't go away. This is one reason.

Thanks.

Btw what's going on on the list??? I seems someone is posting a lot
of obscene nonsense, using my email address. I saw this happen
before with someone else. Is there anything I can do about it?

Thanks again!

On Nov 16, 2004, at 11:44 PM, Joolz wrote:

Btw what's going on on the list??? I seems someone is posting a lot
of obscene nonsense, using my email address.

net.kook

I saw this happen
before with someone else. Is there anything I can do about it?

Filter aggressively and be patient. Not a happy situation, but the best
we can do.

Michael Glaesemann
grzm myrealbox com

--- Joolz <joolz@arbodienst-limburg.nl> wrote:

Thanks.

Btw what's going on on the list??? I seems someone
is posting a lot
of obscene nonsense, using my email address. I saw
this happen
before with someone else. Is there anything I can do
about it?

This is the down side of free will. Human cleverness
can be used for bad purposes as well as good. Those
who do things like this gain gratification from the
responses that they get from others. If we ignore
them (as everyone else on the list appears to be
doing) they will go away eventually.

Thanks again!

---------------------------(end of
broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

__________________________________
Do you Yahoo!?
The all-new My Yahoo! - Get yours free!
http://my.yahoo.com

Matt <matt@kynx.org> writes:

I ask because I'm currently using them to join a single table to rows in
arbitrary tables, something like:

+-------------+
| JOINME |
+-------------+
| foreign_oid |
| foreign_id |
| ... |
+-------------+

Where foreign_oid is the tableoid of the table and foreign_id is the
(serial, not oid) id of the row in that table.

I dunno if it's bad design, but it's darn handy.

How do you make use of this? It seems like you would need your code to know
which foreign_oid referred to which table to actually perform the join.

--
greg