Thread: Preventing variables from "rolling over"
I assume that if I keep on adding to an INT4, at some point it will roll over and become negative, and then if I add some more, it could become positive. Is there a way to prevent this? Ie, I would like for it to end with an error instead of wrapping around. Or is there a way to check for this condition before I do the addition? Is there a way to put a constraint on a variable, such that it's always positive? Thanks
On 30 May 2001, Dr. Evil wrote: > > I assume that if I keep on adding to an INT4, at some point it will > roll over and become negative, and then if I add some more, it could > become positive. Is there a way to prevent this? Ie, I would like > for it to end with an error instead of wrapping around. Or is there a > way to check for this condition before I do the addition? Is there a > way to put a constraint on a variable, such that it's always positive? > > Thanks Not exactly, but close: use a sequence instead of an int4 and define the maxvalue to be the max positive for int. Also don'tallow it to cycle. When it reaches the maxvalue you'll get an error. cheers, thalis
>>>>> "d" == drevil <drevil@sidereal.kz> writes: d> way to check for this condition before I do the addition? Is there a d> way to put a constraint on a variable, such that it's always positive? I have bunches of these: pid integer CHECK (pid >= 0), They work great. Your update will return failure so you'll only find out after the fact, but the table will not have been updated in any case. -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Vivek Khera, Ph.D. Khera Communications, Inc. Internet: khera@kciLink.com Rockville, MD +1-240-453-8497 AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/
http://www.zend.com/zend/art/databases.php Needless to say Postgresql comes out looking pretty good. Good to have links like this to show the advantages of Postgresql when I'm trying to convert others to using it.
Michael <mwaples@waples.net> writes: > http://www.zend.com/zend/art/databases.php > > Needless to say Postgresql comes out looking pretty good. Good to have > links like this to show the advantages of Postgresql when I'm trying to > convert others to using it. Good article. The only inaccuracy I saw was that he claims Postgres' storage of large objects is "in the filesystem" and "inefficient". From my reading of the docs this is not true--large objects are stored within the database just as with any other data. The programming interface to LOs may or may not be clunkier than that of other databases, but the storage is no less efficient. I thought about posting a comment on the article, but I didn't feel like registering at Yet Another Website in order to do so. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
Hello, Heh... I wrote that :) J On Sat, 2 Jun 2001, Michael wrote: > http://www.zend.com/zend/art/databases.php > > Needless to say Postgresql comes out looking pretty good. Good to have > links like this to show the advantages of Postgresql when I'm trying to > convert others to using it. > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
> Good article. The only inaccuracy I saw was that he claims Postgres' > storage of large objects is "in the filesystem" and "inefficient". > >From my reading of the docs this is not true--large objects are stored > within the database just as with any other data. The programming > interface to LOs may or may not be clunkier than that of other > databases, but the storage is no less efficient. As the person who wrote it :) It was my understanding that PostgreSQL stores large objects on the filesystem outside of the database tables. They may be indexed but I thought there was only an identifier within the table that pointed to the large object. If this is not true, could someone please describe the actual process, I can update the article. Joshua Drake > > I thought about posting a comment on the article, but I didn't feel > like registering at Yet Another Website in order to do so. > > -Doug >
Doug McNaught <doug@wireboard.com> writes:
> Good article. The only inaccuracy I saw was that he claims Postgres'
> storage of large objects is "in the filesystem" and "inefficient".
He may be thinking about the fact that Postgres used to store each large
object as a separate table, which did indeed get pretty inefficient if
you had large numbers of large objects (not so much Postgres itself, as
that most Unix filesystems don't handle huge directories efficiently).
This is fixed as of 7.1, however.
regards, tom lane
<pgsql-general@commandprompt.com> writes: > > Good article. The only inaccuracy I saw was that he claims Postgres' > > storage of large objects is "in the filesystem" and "inefficient". > > >From my reading of the docs this is not true--large objects are stored > > within the database just as with any other data. The programming > > interface to LOs may or may not be clunkier than that of other > > databases, but the storage is no less efficient. > > > As the person who wrote it :) It was my understanding that PostgreSQL > stores large objects on the filesystem outside of the database tables. > They may be indexed but I thought there was only an identifier within the > table that pointed to the large object. > If this is not true, could someone please describe the actual process, I > can update the article. You're close, but not completely accurate. To quote the 7.1 docs: [http://postgresql.readysetnet.com/users-lounge/docs/7.1/programmer/largeobjects.html] 2.1. Historical Note Originally, Postgres 4.2 supported three standard implementations of large objects: as files external to Postgres, as external files managed by Postgres, and as data stored within the Postgres database. It causes considerable confusion among users. As a result, we only support large objects as data stored within the Postgres database in PostgreSQL. Even though it is slower to access, it provides stricter data integrity. For historical reasons, this storage scheme is referred to as Inversion large objects. (We will use Inversion and large objects interchangeably to mean the same thing in this section.) Since PostgreSQL 7.1 all large objects are placed in one system table called pg_largeobject. It's interesting that the docs claim this table-level storage is slower to access than using external files. On one respect, though, it's more efficient than storing LOs in files--if you have a lot of LO files in a single directory, access to those files can get very slow on many filesystems (Linux ext2, BSD ufs) due to linear searching of the directory. So, from my knowledge and reading of the docs (I've used LOs a bit but not extensively), here's my understanding: * LOs are stored within a single system table (as above) which is indexed by OID. * User applications store references to LOs in other tables as column type "oid". * Postgres provides file-descriptor-style access (open, read, write, seek, tell) to LOs through their OIDs. This is something you want, since you don't want to be forced to read an entire LO into memory in order to work with it. It might be nice also to have more "convenient" interfaces to manipulate large objects in a way that approximates "normal" column data, for LOs that are not too big. You could probably do a lot of this by writing custom functions. -Doug -- The rain man gave me two cures; he said jump right in, The first was Texas medicine--the second was just railroad gin, And like a fool I mixed them, and it strangled up my mind, Now people just get uglier, and I got no sense of time... --Dylan
> As the person who wrote it :) It was my understanding that PostgreSQL
> stores large objects on the filesystem outside of the database tables.
Definitely not true.
I think that was once an available option, many years ago ... but
it's not there now.
> could someone please describe the actual process,
Large objects are now stored in pg_largeobject. Each LO is broken into
chunks of a couple K apiece, and each chunk becomes a row. (Chunking
makes partial updates more efficient, since you don't have to rewrite
the entire LO.) The LO operations aren't really special, they're just a
convenient interface for insert/update/delete operations in this table.
Before 7.1, it worked the same except that each LO had its own table.
regards, tom lane
From: "Tom Lane" <tgl@sss.pgh.pa.us> > > could someone please describe the actual process, > > Large objects are now stored in pg_largeobject. Each LO is broken into > chunks of a couple K apiece, and each chunk becomes a row. (Chunking > makes partial updates more efficient, since you don't have to rewrite > the entire LO.) The LO operations aren't really special, they're just a > convenient interface for insert/update/delete operations in this table. > > Before 7.1, it worked the same except that each LO had its own table. Perhaps a clear note in the docs - I definitely had the (mis-)impression that LO's were standard files referenced by PG. That could have been my background though, since that's how I tend to deal with documents/images anyway. - Richard Huxton
"Richard Huxton" <dev@archonet.com> writes:
> Perhaps a clear note in the docs -
AFAICT, the docs are perfectly clear about this already; see sections
2.1 and 2.2 of the Programmer's Guide.
The large object documentation *could* use some cleanup, no doubt ---
for one thing, what's it doing in the libpq chapter? But it's there.
regards, tom lane
http://www.zend.com/zend/art/databases.php Needless to say Postgresql comes out looking pretty good. Good to have links like this to show the advantages of Postgresql.