Hi pgsql-general,
(all examples are pseudo-code)
We really love PostgreSQL, it's getting better and better, there is just
one thing, something that has always led to some dislike: OID's
I understand why they did it and all, but still.
To make life easier, it's always good to find a general way of doing things.
But sometimes it just takes a lot more time and effort to find something
you feel even mildly comvertable with.
This is one of those times.
Some people use this way of getting the real insertID:
insert into whatever (text) values ('something');
oid = insertID ();
select id from whatever where whatever.oid = oid;
you get the general idea.
But OID's are optional now... so, not terrible great.
Or with the use of PG's nextval () (which is the preferred/intended
PostgreSQL-way and I agree):
id = nextval ("whatever_id_s eq");
insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of
the sequence, bummer.
So we constructed this query:
SELECT
pg_attrdef.adsr c
FROM
pg_attrdef,
pg_class,
pg_attribute
WHERE
pg_attrdef.adnu m = pg_attribute.at tnum
AND pg_attrdef.adre lid = pg_class.oid
AND pg_attribute.at trelid = pg_class.oid
AND pg_attribute.at tname = 'id'
AND pg_class.relnam e = 'whatever'
(pg_class is a table that holds for instance table-names, etc.,
pg_attribute + pg_attrdef are table's with field-information)
it will result in the default-value of a field of a table..., which means
you get something like this:
nextval('whatev er_id_seq'::tex t)
so, now you have the sequence..., or atleast a way to get to the nextval.
All you have to do is this:
SELECT nextval('whatev er_id_seq'::tex t);
done.
So, now all you have to know is:
- table
- field with ID + default-value
- insert query
Well, maybe that's crazy too, but atleast it's something that'll work.
Probably not the best way, but it's a way.
We're just wondering what people think about such an approach.
Have a nice day,
Lennie.
PS This has been tested with:
- 6.5.3 (Debian Linux Package)
- 8.0 Beta 3 Win32 (msi-install)
_______________ _______________ _______
New things are always on the horizon.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match 9 2456
I think you are correct in not using OIDs, as, firstly, as you point out
they are optional, also that they are not neccessarily unique.
The use of sequences is an idea, however, why the complication? Why not
simply use a sequence called "mytable_sequen ce", or "mytable_id " where
"mytable" is the name of the table? (or some other such standard).
The other thing to be aware of is if a large number of people are
writing to the database concurrently it can go wrong (any method). That
is if you insert a record (using nextval for the sequence), then someone
else quickly inserts a row too before you have a chance to get the
sequence number at the next statement then the sequence number you get
will be wrong (it would be of the new one, not yours). This would be
the case regardless of how the records are committed.
A way around this is to create a function like....
--------------------------------------------------------------------
create function mytable_insert (varchar(50), varchar(50)) returns
integer as '
declare
wseq integer;
begin
select nextval(''mytab le_seq'') into wseq;
insert into mytable(id, a, b)
values (wseq, $1, $2);
return wseq;
end' language 'plpgsql';
--------------------------------------------------------
Then, executing select mytable_insert( 'xx', 'yy');
Will insert the record and return the inserted sequence number
regardless as to what is happening concurrently.
On Sat, 2004-10-23 at 13:52, Leen Besselink wrote: Hi pgsql-general,
(all examples are pseudo-code)
We really love PostgreSQL, it's getting better and better, there is just one thing, something that has always led to some dislike: OID's
I understand why they did it and all, but still.
To make life easier, it's always good to find a general way of doing things.
But sometimes it just takes a lot more time and effort to find something you feel even mildly comvertable with.
This is one of those times.
Some people use this way of getting the real insertID:
insert into whatever (text) values ('something');
oid = insertID ();
select id from whatever where whatever.oid = oid;
you get the general idea.
But OID's are optional now... so, not terrible great.
Or with the use of PG's nextval () (which is the preferred/intended PostgreSQL-way and I agree):
id = nextval ("whatever_id_s eq"); insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of the sequence, bummer.
So we constructed this query:
SELECT pg_attrdef.adsr c FROM pg_attrdef, pg_class, pg_attribute WHERE pg_attrdef.adnu m = pg_attribute.at tnum AND pg_attrdef.adre lid = pg_class.oid AND pg_attribute.at trelid = pg_class.oid AND pg_attribute.at tname = 'id' AND pg_class.relnam e = 'whatever'
(pg_class is a table that holds for instance table-names, etc., pg_attribute + pg_attrdef are table's with field-information)
it will result in the default-value of a field of a table..., which means you get something like this:
nextval('whatev er_id_seq'::tex t)
so, now you have the sequence..., or atleast a way to get to the nextval.
All you have to do is this:
SELECT nextval('whatev er_id_seq'::tex t);
done.
So, now all you have to know is: - table - field with ID + default-value - insert query
Well, maybe that's crazy too, but atleast it's something that'll work.
Probably not the best way, but it's a way.
We're just wondering what people think about such an approach.
Have a nice day, Lennie.
PS This has been tested with: - 6.5.3 (Debian Linux Package) - 8.0 Beta 3 Win32 (msi-install)
_______________ _______________ _______ New things are always on the horizon.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
--
Edward A. Macnaghten http://www.edlsystems.com
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
Eddy Macnaghten <ed**@edlsystem s.com> writes: The other thing to be aware of is if a large number of people are writing to the database concurrently it can go wrong (any method). That is if you insert a record (using nextval for the sequence), then someone else quickly inserts a row too before you have a chance to get the sequence number at the next statement then the sequence number you get will be wrong (it would be of the new one, not yours). This would be the case regardless of how the records are committed.
Not the case. If you use currval(), it will always be the last value
the sequence took *in your session*, so it's immune to other sessions
inserting at the same time. See the docs.
-Doug
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly
Eddy Macnaghten zei: I think you are correct in not using OIDs, as, firstly, as you point out they are optional, also that they are not neccessarily unique.
I'm sorry Eddy, but you most be mistaken:
Every row in POSTGRESQL is assigned a unique, normally invisible number
called an object identification number (OID). When the software is
initialized with initdb , 12.1 a counter is created and set to
approximately seventeen-thousand. The counter is used to uniquely number
every row. Although databases may be created and destroyed, the counter
continues to increase. http://www.postgresql.org/docs/aw_pg...ok/node71.html
The use of sequences is an idea, however, why the complication? Why not simply use a sequence called "mytable_sequen ce", or "mytable_id " where "mytable" is the name of the table? (or some other such standard).
Because a lot of the time we query databases we did not create our selfs,
we were looking for a general way, to handle it.
The other thing to be aware of is if a large number of people are writing to the database concurrently it can go wrong (any method). That is if you insert a record (using nextval for the sequence), then someone else quickly inserts a row too before you have a chance to get the sequence number at the next statement then the sequence number you get will be wrong (it would be of the new one, not yours). This would be the case regardless of how the records are committed.
I thought that was the whole idea of sequences, each call to nextval ()
will actually give you a unique number for that sequence (unless ofcourse
it it wraps..)
A way around this is to create a function like....
--------------------------------------------------------------------
create function mytable_insert (varchar(50), varchar(50)) returns integer as ' declare
wseq integer;
begin
select nextval(''mytab le_seq'') into wseq;
insert into mytable(id, a, b) values (wseq, $1, $2);
return wseq;
end' language 'plpgsql';
--------------------------------------------------------
Then, executing select mytable_insert( 'xx', 'yy');
That just uses a plpgsql function to do what I suggested (other then you
need to know the sequence name)
Will insert the record and return the inserted sequence number regardless as to what is happening concurrently.
Ohh, now I know what you mean, no we don't write concurrently, but a
nextval should be unique for that sequence anyway (otherwise, why even
have them ?). On Sat, 2004-10-23 at 13:52, Leen Besselink wrote: Hi pgsql-general,
(all examples are pseudo-code)
We really love PostgreSQL, it's getting better and better, there is just one thing, something that has always led to some dislike: OID's
I understand why they did it and all, but still.
To make life easier, it's always good to find a general way of doing things.
But sometimes it just takes a lot more time and effort to find something you feel even mildly comvertable with.
This is one of those times.
Some people use this way of getting the real insertID:
insert into whatever (text) values ('something');
oid = insertID ();
select id from whatever where whatever.oid = oid;
you get the general idea.
But OID's are optional now... so, not terrible great.
Or with the use of PG's nextval () (which is the preferred/intended PostgreSQL-way and I agree):
id = nextval ("whatever_id_s eq"); insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of the sequence, bummer.
So we constructed this query:
SELECT pg_attrdef.adsr c FROM pg_attrdef, pg_class, pg_attribute WHERE pg_attrdef.adnu m = pg_attribute.at tnum AND pg_attrdef.adre lid = pg_class.oid AND pg_attribute.at trelid = pg_class.oid AND pg_attribute.at tname = 'id' AND pg_class.relnam e = 'whatever'
(pg_class is a table that holds for instance table-names, etc., pg_attribute + pg_attrdef are table's with field-information)
it will result in the default-value of a field of a table..., which means you get something like this:
nextval('whatev er_id_seq'::tex t)
so, now you have the sequence..., or atleast a way to get to the nextval.
All you have to do is this:
SELECT nextval('whatev er_id_seq'::tex t);
done.
So, now all you have to know is: - table - field with ID + default-value - insert query
Well, maybe that's crazy too, but atleast it's something that'll work.
Probably not the best way, but it's a way.
We're just wondering what people think about such an approach.
Have a nice day, Lennie.
PS This has been tested with: - 6.5.3 (Debian Linux Package) - 8.0 Beta 3 Win32 (msi-install)
_______________ _______________ _______ New things are always on the horizon.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match -- Edward A. Macnaghten http://www.edlsystems.com
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
_______________ _______________ _______
New things are always on the horizon.
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
On Sat, Oct 23, 2004 at 14:52:31 +0200,
Leen Besselink <le**@wirehub.n l> wrote: id = nextval ("whatever_id_s eq"); insert into whatever (id, text) values (id, 'something');
Something that works always... better, but you need to know the name of the sequence, bummer.
In 8.0 (currently in beta) there is a supplied function to return this
string reliably.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)
On Sat, 23 Oct 2004, Leen Besselink wrote: Eddy Macnaghten zei: I think you are correct in not using OIDs, as, firstly, as you point out they are optional, also that they are not neccessarily unique.
I'm sorry Eddy, but you most be mistaken:
Every row in POSTGRESQL is assigned a unique, normally invisible number called an object identification number (OID). When the software is initialized with initdb , 12.1 a counter is created and set to approximately seventeen-thousand. The counter is used to uniquely number every row. Although databases may be created and destroyed, the counter continues to increase.
http://www.postgresql.org/docs/aw_pg...ok/node71.html
Actually it's the book that's mistaken. The counter can roll over, and
will on large databases (oids are only 4 bytes).
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives? http://archives.postgresql.org
On Sat, 2004-10-23 at 17:46 +0200, Leen Besselink wrote: Eddy Macnaghten zei: I think you are correct in not using OIDs, as, firstly, as you point out they are optional, also that they are not neccessarily unique.
I'm sorry Eddy, but you most be mistaken:
Every row in POSTGRESQL is assigned a unique, normally invisible number called an object identification number (OID). When the software is initialized with initdb , 12.1 a counter is created and set to approximately seventeen-thousand. The counter is used to uniquely number every row. Although databases may be created and destroyed, the counter continues to increase.
http://www.postgresql.org/docs/aw_pg...ok/node71.html
That is dated 2002. It is now possible to create a table without oids,
and oids are not guaranteed always to exist in all future releases. It
is likely that the default table creation will switch to being without
oids soon; that can already be specified as the default (in 8.0beta3).
Oids are not guaranteed to be unique, since they wrap round when they
reach the end of their range. If you wanted to use an oid as a
guaranteed unique id, you would need to add a unique index on the oid
column for that table; that could then cause an insertion to fail if an
oid in the table were to be reused. If it were a very large table, that
would cause the application to fail, because many insertions would be
likely to fail after the wrap-around. The use of sequences is an idea, however, why the complication? Why not simply use a sequence called "mytable_sequen ce", or "mytable_id " where "mytable" is the name of the table? (or some other such standard).
Because a lot of the time we query databases we did not create our selfs, we were looking for a general way, to handle it.
Reliance on a database feature, such as oids, as a key is a sign of bad
design; a table row ought to have a unique key of some kind, and if you
insert that row, you must know what that key is. If there is no other
way to distinguish it, you can add a serial column for the sole purpose
of providing a primary key. That would be part of the data rather than
a side-effect of the implementation.
I can't see how the use of oids would help you with a database of
someone else's design, unless the designer used that feature already. The other thing to be aware of is if a large number of people are writing to the database concurrently it can go wrong (any method). That is if you insert a record (using nextval for the sequence), then someone else quickly inserts a row too before you have a chance to get the sequence number at the next statement then the sequence number you get will be wrong (it would be of the new one, not yours). This would be the case regardless of how the records are committed.
I thought that was the whole idea of sequences, each call to nextval () will actually give you a unique number for that sequence (unless ofcourse it it wraps..)
You are correct. nextval() is guaranteed never to give the same number
(unless setval() were used to reset the sequence value). A lot of
people seem not to understand that. The trade-off is that sequences are
not rolled back if a transaction is aborted.
--
Oliver Elphick ol**@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA 92C8 39E7 280E 3631 3F0E 1EC0 5664 7A2F A543 10EA
=============== =============== ==========
"I press toward the mark for the prize of the high
calling of God in Christ Jesus."
Philippians 3:14
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org
> You are correct. nextval() is guaranteed never to give the same number (unless setval() were used to reset the sequence value).
Or unless the sequence wraps around. That's less likely (and less
dangerous) than having the OID wrap around, but not impossible.
I personally believe that there is value in a database-generated unique
value like Oracle's ROWID. (Part of what I like about it is that since
it is a system column it simplifies some application issues, since the
app never has to worry about that column unless it chooses to.)
Making the OID sufficiently large to avoid virtually all wraparound
issues would probably mean going to a 64 bit field, which would certainly
be a non-trivial task.
--
Mike Nolan
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
On Sat, 23 Oct 2004, Mike Nolan wrote: I personally believe that there is value in a database-generated unique value like Oracle's ROWID. (Part of what I like about it is that since it is a system column it simplifies some application issues, since the app never has to worry about that column unless it chooses to.)
If an application needs a column called oid in each table it could very
well just define the tables like that. You could also make a script that
checks all tables and make sure there is a oid if you want to.
--
/Dennis Björklund
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Mike Nolan <no***@gw.tssi. com> writes: You are correct. nextval() is guaranteed never to give the same number (unless setval() were used to reset the sequence value).
Or unless the sequence wraps around. That's less likely (and less dangerous) than having the OID wrap around, but not impossible.
Sequences do not wrap by default (only if you use the CYCLE option).
Anyway, if you use a bigint sequence field you are pretty safe from ever
running out of values...
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: James Thornton |
last post by:
I'm running PG 7.2.3, and I am getting these notices when running
vacuum analzye:
spower=# vacuum analyze;
NOTICE: Rel sec_browser_properties: TID 935/15: OID IS INVALID.
TUPGONE 0.
NOTICE: Rel sec_browser_properties: TID 935/23: OID IS INVALID.
TUPGONE 0.
NOTICE: Rel sec_browser_properties: TID 935/28: OID IS INVALID.
TUPGONE 0.
|
by: Tom Hebbron |
last post by:
Hi list,
test=# SELECT 'pg_catalog.pg_class'::regclass::oid;
oid
------
1259
(1 row)
test=# SELECT '1259'::oid::regclass;
regclass
|
by: Jeff Bohmer |
last post by:
I have a table in a PG 7.4.1 database with 380 duplicate rows,
including duplicate oid and primary key values. Looking through our
backups, the duplicates did not exist before Friday, 02/06/2004. I'm
assuming neither pg_dumpall nor restoring from a pg_dumpall file will
eliminate such duplicates. We upgraded from 7.3.4 to 7.4.1 on
02/02/2004.
What can cause these duplicates?
The server has had several system crashes over the past...
|
by: Sky |
last post by:
Problem:
pg_loopen() unable to open PostgreSQL large object in...
Solution from another mailing list:
My blob oid was very big.
I had to cast to (float) the oid parameter of pg_loopen.
Question:
The oid is a type, isn't it?
I tried to use the oid in a function:
|
by: John Sidney-Woollett |
last post by:
<rant>
Please can someone explain why Postgres cannot recognize that objects
(referenced by pl/pgsql functions) whose OID no longer exists could in
fact be found (as new objects) if the function was reparsed and compiled
again.
Here's an example:
Create table t1 (f1 integer);
| |
by: Raffaele Spizzuoco |
last post by:
Hi!
I'm from Italy, and sorry about my english...
I have a question that I know it is already said in the groups but I
have however some doubts
I have seen it is technically possible to use OID as PRIMARY KEY and
as FOREIGN KEY but it is correct to do so for the database's logical
integrity?
Is it better I use in any case other keys and not oid to avoid the
possible wraparound? or the wraparound is an extreme case and so I can
|
by: frbn |
last post by:
hi all,
we currently experience an original problem
on a production server with a postgresql 7.1.3
( a bit old, I know :\ )
We just want to know if somebody experienced this problem:
the error message :
"ERROR: Unable to locate type oid 0 in catalog"
|
by: Jason James |
last post by:
Guys,
can anyone confirm the process on converting the OID into an array of
bytes for sending to the SNMP device. The code I have seems to only
work for values in the OID that are less than 2^14 but some enterprise
IDs are now larger than that. Do I just contine the conversion on to
additional bytes needed to house the OID in full?
So and enterprise ID of 25000 would be sent as three bytes of the
following:
|
by: Ana C. Dent |
last post by:
PF wrote:
A couple of months ago I found a Note: on MetaLink
which detailed how to use OID to serve DB names.
I was going from TNSNAMES.ORA files to OID, but
it did work. However I never actually implemented
this "solution".
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
| |
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |