OID Usage

On Jan 14, 2005, at 16:03, Bo Lorentsen wrote:

Now, are there any danger in using this method ? And if there is, how
can I do this trick without knowing the layout of the table I insert
into ?

You can use currval() to get the sequence value that was pulled from
your insert. You can check the documentation for usage, as well as
searching the archives for discussions of using OIDs as part of your
database logic.

Hope this helps.

Michael Glaesemann
grzm myrealbox com

Michael Glaesemann wrote:

You can use currval() to get the sequence value that was pulled from
your insert. You can check the documentation for usage, as well as
searching the archives for discussions of using OIDs as part of your
database logic.

I know this, but i like not to know anything about the metadata of the
table i use. Basicly using the same functionality, as given in mysql in
the mysql_insert_id, as I use the same low level code for both DB's
(until my boss give in totally to PG :-)).

/BL

Hi,

On Fri, 14 Jan 2005, Bo Lorentsen wrote:

Michael Glaesemann wrote:

You can use currval() to get the sequence value that was pulled from your
insert. You can check the documentation for usage, as well as searching
the archives for discussions of using OIDs as part of your database logic.

I know this, but i like not to know anything about the metadata of the table
i use. Basicly using the same functionality, as given in mysql in the
mysql_insert_id, as I use the same low level code for both DB's (until my
boss give in totally to PG :-)).

why should your application not want to know about the metadata of it's
own tables ? That sounds quite strange when you think about it.

If you name your sequences in a generic way you can alway construct the
name of the sequence from the name of the table and the id column.

We use this in our php framework

function insert_id()
{
global $pg_conn;
if(isset($pg_conn)) {
$query = sprintf("SELECT currval('%s_%s_seq') AS id",$this->table,$this->id_column);
$result = @pg_query($pg_conn,$query);
$row = pg_fetch_assoc($result);
return strval($row["id"]);
} else {
return 0;
}
}

Greetings
Christian

--
Christian Kratzer ck@cksoft.de
CK Software GmbH http://www.cksoft.de/
Phone: +49 7452 889 135 Fax: +49 7452 889 136

On Fri, Jan 14, 2005 at 10:32:18AM +0100, Christian Kratzer wrote:

$query = sprintf("SELECT currval('%s_%s_seq') AS
id",$this->table,$this->id_column);

PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
returns the sequence name for a particular column so you don't have
to construct it. This is useful when a table or column has been
renamed, in which case the above will probably break.

CREATE TABLE foo (fooid serial);
ALTER TABLE foo RENAME TO bar;
ALTER TABLE bar RENAME fooid TO barid;
\d bar
Table "public.bar"
Column | Type | Modifiers
--------+---------+--------------------------------------------------------
barid | integer | not null default nextval('public.foo_fooid_seq'::text)

SELECT pg_get_serial_sequence('bar', 'barid');
pg_get_serial_sequence
------------------------
public.foo_fooid_seq
(1 row)

INSERT INTO bar VALUES (DEFAULT);
SELECT currval(pg_get_serial_sequence('bar', 'barid'));
currval
---------
1
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Christian Kratzer wrote:

why should your application not want to know about the metadata of it's
own tables ? That sounds quite strange when you think about it.

Well, the ideer is to be compatible with mysql at the same level in the
code. This works nicely, as I have descriped, but I am concerned if
there is any strains attached to this method.

It is all found in the : http://lue.dk/prj/dbc/index.html

If you name your sequences in a generic way you can alway construct
the name of the sequence from the name of the table and the id column.

We use this in our php framework

function insert_id()
{
global $pg_conn;
if(isset($pg_conn)) {
$query = sprintf("SELECT currval('%s_%s_seq')
AS id",$this->table,$this->id_column);
$result = @pg_query($pg_conn,$query);
$row = pg_fetch_assoc($result);
return strval($row["id"]);
} else {
return 0;
}
}

Thanks, but this demands you to have the table and id_column name in
your hand, and I don't right now.

Also ... the "currval" function are specifik to postgresql, and there
are nothing like it in mysql that can make any garanti for getting row
for newly inserted data. You can access autoincrement values in mysql,
but no garanties are given about its value (someone else have inserted a
new in the same table).

But thanks for your interrest., anyway.

/BL

Michael Fuhr wrote:

PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
returns the sequence name for a particular column so you don't have
to construct it. This is useful when a table or column has been
renamed, in which case the above will probably break.

Quite nice but not what I need, as I still need to know the id column name.

But thanks anyway.

/BL

On Fri, Jan 14, 2005 at 11:39:54AM +0100, Bo Lorentsen wrote:

Thanks, but this demands you to have the table and id_column name in
your hand, and I don't right now.

You can create a function to get the sequence name attached to a table.
Of course, you should take into account the fact that there could be
more than one (two serial fields in a table are rare but not
impossible), but if your tables have only one sequence you should be OK.
Something with

select relname, relkind
from pg_depend join pg_class on (oid = objid)
where pg_depend.refobjid = 'foo'::regclass
and relkind = 'S';

(only lightly tested). Then you can use that to construct your argument
to the nextval() function.

Also ... the "currval" function are specifik to postgresql, and there
are nothing like it in mysql that can make any garanti for getting row
for newly inserted data. You can access autoincrement values in mysql,
but no garanties are given about its value (someone else have inserted a
new in the same table).

This doesn't happen with sequences on Postgres. The value you get is
guaranteed to be the one the sequence generated for you.

--
Alvaro Herrera (<alvherre[@]dcc.uchile.cl>)
"Those who use electric razors are infidels destined to burn in hell while
we drink from rivers of beer, download free vids and mingle with naked
well shaved babes." (http://slashdot.org/comments.pl?sid=44793&amp;cid=4647152)

Bo Lorentsen <bl@netgroup.dk> writes:

I use normal tabel ID (SERIAL and BIGSERIAL) all over the place for FK
constaints, but I use OID in one special situation. When I insert a
single row into a table, I like my low level code to be kompatible with
mysql ( mysql_insert_id ), and fetch the row that I just inserted. This
I do by using the PGoidValue function, and then select the row by the
oid. This works quite nice .... but when a table get large, it become a
big search (seq scan) so I have added an index on oid's on the table
where I use this trick, and this have helper :-)

The thing you have to worry about is the possibility of duplicate OIDs
once your DB has been running long enough for the OID counter to wrap
around (2^32 OIDs). You should make sure that index is specifically
declared as UNIQUE, so that any attempt to insert a duplicate OID will
fail. That might be enough for you, or you might want to add logic to
your application to retry automatically after such a failure.

regards, tom lane

On Fri, Jan 14, 2005 at 11:47:25AM +0100, Bo Lorentsen wrote:

Michael Fuhr wrote:

PostgreSQL 8.0 will have a pg_get_serial_sequence() function that
returns the sequence name for a particular column so you don't have
to construct it. This is useful when a table or column has been
renamed, in which case the above will probably break.

Quite nice but not what I need, as I still need to know the id column name.

You could query the system catalogs for the table's primary key,
either on the client side or in a server-side function. The
pg_attrdef table even has the default value's nextval() expression
with the sequence name, which could be converted into a currval()
call.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Michael Fuhr wrote:

You could query the system catalogs for the table's primary key,
either on the client side or in a server-side function. The
pg_attrdef table even has the default value's nextval() expression
with the sequence name, which could be converted into a currval()
call.

This is not a bad ideer, I will take a look in the "pg_attrdef" table to
see what I can find, and some good caching may help alot too :-)

But, does this mean that the oid sollution I have decriped (and
implimentet) have some unknown problems, or will oid's become obsolete
in the near future ?

/BL

Alvaro Herrera wrote:

You can create a function to get the sequence name attached to a table.
Of course, you should take into account the fact that there could be
more than one (two serial fields in a table are rare but not
impossible), but if your tables have only one sequence you should be OK.

Are there a way to find and test if it is a primary key ?

Something with

select relname, relkind
from pg_depend join pg_class on (oid = objid)
where pg_depend.refobjid = 'foo'::regclass
and relkind = 'S';

Hmm, need to play more around using the "pg_" system tables.

Are they all well documentet, or need I some guessing ?

(only lightly tested). Then you can use that to construct your argument
to the nextval() function.

:-)

This doesn't happen with sequences on Postgres. The value you get is
guaranteed to be the one the sequence generated for you.

I know, and this is one of the reasons for not using MySQL :-)

/BL

Tom Lane wrote:

The thing you have to worry about is the possibility of duplicate OIDs
once your DB has been running long enough for the OID counter to wrap
around (2^32 OIDs). You should make sure that index is specifically
declared as UNIQUE, so that any attempt to insert a duplicate OID will
fail. That might be enough for you, or you might want to add logic to
your application to retry automatically after such a failure.

Ahh, yes ... this was what I thought may have be the problem, Not that
2^32 is a small number, but as time goes by on a busy system, this will
happened one day.

Unique index is a good plan, it will make an error but no data will be
harmed then !

How does PG itself handle a search on an duplicated oid, without a index
... return two rows ?

Will there be a future substitute for PGoidValue that is more reliable,
like a rowid ?

Thanks anyway !

/BL

On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:

But, does this mean that the oid sollution I have decriped (and
implimentet) have some unknown problems, or will oid's become obsolete
in the near future ?

The PostgreSQL documentation discourages the use of OIDs for primary
keys. For example, the "Object Identifier Types" section in the
"Data Types" chapter says:

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. OIDs are best used only for references to system
tables.

The "System Columns" section of the "Data Definition" chapter says:

OIDs are 32-bit quantities and are assigned from a single
cluster-wide counter. In a large or long-lived database, it is
possible for the counter to wrap around. Hence, it is bad practice
to assume that OIDs are unique, unless you take steps to ensure that
they are unique.

The CREATE TABLE documentation in the Reference part says:

Once the counter wraps around, uniqueness of OIDs can no longer be
assumed, which considerably reduces their usefulness. Specifying
WITHOUT OIDS also reduces the space required to store the table on
disk by 4 bytes per row of the table, thereby improving performance.

The 8.0 Release Notes say the following under "Deprecated Features":

By default, tables in PostgreSQL 8.0 and earlier are created with
OIDs. In the next release, this will _not_ be the case: to create a
table that contains OIDs, the WITH OIDS clause must be specified or
the default_with_oids configuration parameter must be enabled.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Jan 14, 2005 at 07:39:16PM +0100, Bo Lorentsen wrote:

But, does this mean that the oid sollution I have decriped (and
implimentet) have some unknown problems, or will oid's become obsolete
in the near future ?

It means using OIDs as you described has very well known problems and
they will break on you eventually. You can mitigate the damage by
creating a UNIQUE index on the oid column but you'd better be sure your
application can handle the side-effects.

OIDs won't become obsolete, but they'll probably no longer be enabled
by default at some stage.

Hope this helps,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote:

Alvaro Herrera wrote:

You can create a function to get the sequence name attached to a table.
Of course, you should take into account the fact that there could be
more than one (two serial fields in a table are rare but not
impossible), but if your tables have only one sequence you should be OK.

Are there a way to find and test if it is a primary key ?

pg_index has an indisprimary column.

Hmm, need to play more around using the "pg_" system tables.

Are they all well documentet, or need I some guessing ?

See the "System Catalogs" chapter in the documentation.

If you run "psql -E" you'll see the queries that psql executes for
commands like "\d foo". Those commands query the system catalogs.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

On Fri, Jan 14, 2005 at 12:20:50PM -0700, Michael Fuhr wrote:

On Fri, Jan 14, 2005 at 07:44:18PM +0100, Bo Lorentsen wrote:

Alvaro Herrera wrote:

You can create a function to get the sequence name attached to a table.
Of course, you should take into account the fact that there could be
more than one (two serial fields in a table are rare but not
impossible), but if your tables have only one sequence you should be OK.

Are there a way to find and test if it is a primary key ?

pg_index has an indisprimary column.

Yeah, though things get hairy that way because you have to peek at
pg_attribute to match the objsubid in pg_depend; and self-join pg_class
to get to the index itself. Not sure if it all can be done in a single
query.

If you run "psql -E" you'll see the queries that psql executes for
commands like "\d foo". Those commands query the system catalogs.

Sadly, there's hardly anything there that uses pg_depend.

--
"I dream about dreams about dreams", sang the nightingale
under the pale moon (Sandman)

Michael Fuhr wrote:

The PostgreSQL documentation discourages the use of OIDs for primary
keys. For example, the "Object Identifier Types" section in the
"Data Types" chapter says:

...

Thanks for taking you the time to snip this together, I think I will try
to find a way to find the propper primary key (using pg_* tables), and
if this uses the "nextval", I may be able to retrive the currently
inserted row by using currval.

The 8.0 Release Notes say the following under "Deprecated Features":

Why have this not happend before ? The "PGoidValue need to be depricated
too. And why is it not substitutet with something else ?

/BL

Martijn van Oosterhout wrote:

It means using OIDs as you described has very well known problems and
they will break on you eventually. You can mitigate the damage by
creating a UNIQUE index on the oid column but you'd better be sure your
application can handle the side-effects.

Ok, Tom told me about the same :-( But why are oid's still in PG, that
are they good for ? Will there be a real unique row id, like there is in
Oracle, or will this be keept as an internal value only ?

OIDs won't become obsolete, but they'll probably no longer be enabled
by default at some stage.

Is this because some old application's using oid's in somewhat small
dataset ?

Hope this helps,

It did thanks.

/BL

Michael Fuhr wrote:

See the "System Catalogs" chapter in the documentation.

Ok, I think I will compile all the given information in this thread, to
make a new and more non oid'ish solution, as the dataset I manage are
going to grow quite a lot :-)

If you run "psql -E" you'll see the queries that psql executes for
commands like "\d foo". Those commands query the system catalogs.

This may be very usefull, thanks.

/BL