Re: [GENERAL] Unanswered questions about Postgre
Quoting Tom Lane <[EMAIL PROTECTED]>: > The JDBC support for over-the-wire access to large objects used to > have some bugs, but AFAIK those are cleaned up in current sources > (right Peter?) Yes except for DatabaseMetaData.getTables() but thats not directly to do with large objects. As long as things settle down here by Saturday, I'll be sorting out what's outstanding... Peter -- Peter Mount [EMAIL PROTECTED] PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/ RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/
Re: [GENERAL] Unanswered questions about Postgre
> Yes, this was my point. We now have TOAST, but by not going the extra > mile to enable storage of binary files, we really aren't taking full > advantage of our new TOAST feature. > > I can see people saying, "Wow, you can store rows of unlimited length > now. Let me store this jpeg. Oh, I can't because it is binary!" > Well, to me it seems that, when TOAST will be available (i.e. when the long awaited, most desired, more bloated, world- conquering 7.1 version will come-out...), 90% of the work it is already done to support also column-style BLOBs... at least for web applications, that are incidentally my focus. Any web programmer worth its salt could put up a simple layer that does base64 encode/decode and use "CLOBs" (I think TOAST columns could be called that way, right?)... and he should write anyway some interface for file uploading/downloading, since its client are using a browser as their frontend. Using PHP, it's no more than a few rows of code. Granted, base64 encode can waste a LOT of space, but it looks like a columbus' egg in this scenario. Maybe base64 could also be a quick way to write a binary "patch" for TOAST so it would be binary-compatible "natively"? Or am I saying a lot of bullsììt? :-) Just wanted to share some toughts. Merry Christmas to everybody... /\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/\/ Fabrizio Ermini Alternate E-mail: C.so Umberto, 7 [EMAIL PROTECTED] loc. Meleto Valdarno Mail on GSM: (keep it short!) 52020 Cavriglia (AR) [EMAIL PROTECTED]
Re: [GENERAL] Unanswered questions about Postgre
> This is a misunderstanding. You can still use the old-style large > objects (in fact 7.1 has an improved implementation of them too), > and there's always been support for either over-the-wire or > server-filesystem read and write of large objects. In fact the former > is the preferred way; the latter is deprecated because of security > issues. In a standard installation you can't do the server-filesystem > bit at all unless you are superuser. I know we haven't talked about the TOAST/binary interface, but one idea I had was to load the binary into the large object interface, then automatically somehow transfer it to the TOAST column. Same for extracting large objects. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Unanswered questions about Postgre
what is the tentative date for 7.1 release? what is the release date for replication? sandeep
Re: [GENERAL] Unanswered questions about Postgre
Joe Kislo <[EMAIL PROTECTED]> writes: > ... this lack of BLOB support. I understand that the C > API can read/write -files- off the server's filesystem and load them > into the database. Unfortunately we would absolutely require true > over-the-wire blob support through JDBC. AFAIK, even with these "toast" > columns, it still wouldn't fill that need. This is a misunderstanding. You can still use the old-style large objects (in fact 7.1 has an improved implementation of them too), and there's always been support for either over-the-wire or server-filesystem read and write of large objects. In fact the former is the preferred way; the latter is deprecated because of security issues. In a standard installation you can't do the server-filesystem bit at all unless you are superuser. The JDBC support for over-the-wire access to large objects used to have some bugs, but AFAIK those are cleaned up in current sources (right Peter?) Adding a similar feature for TOAST columns will certainly be a notational improvement, but it won't add any fundamental capability that isn't there already. > 2) Postgre does not record rollback segments. We know this is needed. But it will not happen for 7.1, and there's no point in complaining about that; 7.1 is overdue already. regards, tom lane
Re: [GENERAL] Unanswered questions about Postgre
Joe Kislo writes: > First, I was evaluating Postgre for a medium scale application I will I'm just wondering what this "Postgre" thing is you keep talking about... ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] Unanswered questions about Postgre
> > What I think we _really_ need is a large object interface to TOAST data. > > We already have a nice API, and even psql local large object handling. > > > > If I have a file that I want loaded in/out of a TOAST column, we really > > should make a set of functions to do it, just like we do with large > > objects. > > > > This an obvious way to load files in/out of TOAST columns, and I am not > > sure why it has not been done yet. I am afraid we are going to get > > critisized if we don't have it soon. > > Okay, let me criticize you now then :) (just kidding) Over the past > month I've been trying out postgre for two reasons. I've posted a > number of questions to this mailing list, and the postgre community has > been extremely responsive and helpful. Kudos to everybody working on > postgre. Most of my questions have been along the line of asking why a > particular feature works differently then in other databases, or why > postgre seemed to act in an illogical fashion (such as corrupting my > database). Yes, this was my point. We now have TOAST, but by not going the extra mile to enable storage of binary files, we really aren't taking full advantage of our new TOAST feature. I can see people saying, "Wow, you can store rows of unlimited length now. Let me store this jpeg. Oh, I can't because it is binary!" -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup.| Drexel Hill, Pennsylvania 19026
Re: [GENERAL] Unanswered questions about Postgre
> What I think we _really_ need is a large object interface to TOAST data. > We already have a nice API, and even psql local large object handling. > > If I have a file that I want loaded in/out of a TOAST column, we really > should make a set of functions to do it, just like we do with large > objects. > > This an obvious way to load files in/out of TOAST columns, and I am not > sure why it has not been done yet. I am afraid we are going to get > critisized if we don't have it soon. Okay, let me criticize you now then :) (just kidding) Over the past month I've been trying out postgre for two reasons. I've posted a number of questions to this mailing list, and the postgre community has been extremely responsive and helpful. Kudos to everybody working on postgre. Most of my questions have been along the line of asking why a particular feature works differently then in other databases, or why postgre seemed to act in an illogical fashion (such as corrupting my database). First, I was evaluating Postgre for a medium scale application I will working on for my current employer. Technically this is re-architecting a current application built on MySQL and Python. I plan to move the application to java servlets and some database other then MySQL, preferably opensource. Postgre, obviously with its' reputation, was the beginning of this short list of databases to look at. Unfortunately I quickly discovered this lack of BLOB support. I understand that the C API can read/write -files- off the server's filesystem and load them into the database. Unfortunately we would absolutely require true over-the-wire blob support through JDBC. AFAIK, even with these "toast" columns, it still wouldn't fill that need. The need here is to load binary data from the client, transfer it over the JDBC wire, and store it in the database. Some people before suggested a shared NFS partition, then have the server use the existing BLOB support to load the files off disk. That's really not an acceptable solution. So as for using postgre in this upcoming application, it's really a no-go at this point without that ability. I actually suspect a number of people also have a need to store BLOBs in a database, but maybe it's not as important as I think. The second reason why I've been working with Postgre is I'm about to release into the open source a java based object database abstraction layer. This layer maps java objects to a relational database by storing their primitives in database primitives, and using java reflection to reconstitute objects from the database. This allows you to perform complex joins and such in the -database- then map to the actual java objects. When you attach a particular class to a database, you choose the appropriate database adapter (such as one for oracle or postgre). These DBAdapters take care of all the DB specific things, such as native column types, handling auto incrementing columns (generators or "serial columns"), creating tables, altering tables when class definitions change, database independent indexing, and blobs. Programmers mostly work at the object layer, and don't really worry about the particulars of the underlying database. (although they can execute raw SQL if they really need to). So this truly allows an application to be written independent of any particular underlying database (and to my dismay, there appear to be very big differences between these databases!). This allows you to change your underlying database easily, which means you can choose the database server on it's merits, and not because it's been grandfathered into your application :) Anyway, when implementing the Postgre DBAdapter, I found postgre to be quite a nice database (and pretty fast too). But there were two issues which cripple the postgre DBAdapter from supporting the full feature set. 1) No blob support. As I described above, it needs to be possible to insert an arbitrarily large (or atleast up to say 5 megabytes) binary object into the database, and have it accessible by a particular column name in a table. AFAIK, this is not currently possible in postgre 2) Postgre does not record rollback segments. Which means transactions get ABORTed and rolled back for some odd reasons when they don't normally need to. For example, if you just send the SQL server some garbage SQL, (eg: ASDF;) your transaction gets aborted and rolled back; even though your garbage SQL didn't touch any rows. At the object layer in the aforementioned database layer, if you try insert an object into the database and doing so would violate a unique key (such as the primary key), a DuplicateKeyException will be thrown. No other database adapters I've implemented, such as MySQL, interbase or oracle, will *also* abort the transaction. So if at the object layer, a DuplicateKeyException is supposed to happen in that case, I would have to before every obje
Re: [GENERAL] Unanswered questions about Postgre
Joe Kislo wrote: >Yes. There are certainly times when a transaction needs to be > ABORTed. However, there are many reasons why the database should not > abort a transaction if it does not need to. There is obviously no > reason why a transaction needs to be aborted for syntax errors. There > is obviously no reason why a transaction needs to be aborted for say, > trying to insert a duplicate primary key. The -insert- can fail, report > it as such, and the application can determine if a rollback is > nessasary. If you don't believe me, here's two fully SQL-92 compliant > databases, Oracle and interbase, which do not exhibit this behavior: You're right. But it'd be (up to now) impossible to implement in Postgres. Postgres doesn't record any undo information during the execution of a transaction (like Oracle for example does in the rollback segments). The way Postgres works is not to overwrite existing tuples, but to stamp them outdated and insert new ones. In the case of a ROLLBACK, just the stamps made are flagged invalid (in pg_log). If you do a INSERT INTO t1 SELECT * FROM t2; there could occur a duplicate key error. But if it happens in the middle of all the rows inserted, the first half of rows is already in t1, with the stamp of this transaction to come alive. The only way to not let them show up is to invalidate the entire transaction. Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] #
Re: [GENERAL] Unanswered questions about Postgre
On Thu, Nov 30, 2000 at 12:16:39PM -0800, Mikheev, Vadim wrote: > Oracle & Interbase have savepoints. Hopefully PG will also have them in 7.2 A feature that I liked from using Faircom's Ctree (granted not an SQL based DB or one with built in relations) was auto-save points. So, if something failed, it could be rolled back to the previous auto-save point. Just food for thought on how Ctree works. A transaction, by default, will fail on the commit if there were any errors within the transaction (though it would happily process all of your commands after an error without additional failures, so it was less verbose than the original psql demonstration at the beginning of this thread). Also, by default, no auto-save points. One could turn on auto-save points. (If one wanted "normal" save-point activities, you would get the save point counter number and then rollback to that particular save point at some time). This was convenient if you wanted to just rollback the last operation that caused the error (this may have had the side effect of unmarking the fact than an error occured, but I don't think so. There was another command to clear the transaction error, with lots of disclaimers saying if you did that, you took your own responsibilities). Guess, in sort, what I'm saying is, if save points are added, might as well add auto-save points while at it, and give the ability to selectively clear the error and allow a transaction to commit anyway (keeping current method as default, of course). mrc -- Mike Castle Life is like a clock: You can work constantly [EMAIL PROTECTED] and be right all the time, or not work at all www.netcom.com/~dalgoda/ and be right at least twice a day. -- mrc We are all of us living in the shadow of Manhattan. -- Watchmen
Re: [GENERAL] Unanswered questions about Postgre
> > What's nice about PostgreSQL is that, while it hasn't always had > > every SQL92 feature (like outer joins, etc.), it seems to have less > > legacy, nonstandard stuff wired in. :-) > > Oh man, you have n idea. PostgreSQL is legacy headquarters. ;-) Yes, yes, I know about *some* of them [8k limit springs to mind!] (C hackers no doubt no *lots* more.) But, in terms of, "as comes out in our SQL syntax", compared to Oracle, we're free and clear. -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Unanswered questions about Postgre
Joel Burton writes: > What's nice about PostgreSQL is that, while it hasn't always had > every SQL92 feature (like outer joins, etc.), it seems to have less > legacy, nonstandard stuff wired in. :-) Oh man, you have n idea. PostgreSQL is legacy headquarters. ;-) -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
SV: [GENERAL] Unanswered questions about Postgre
Hi,
...
> > > That is what transactions are for. If any errors occur, then the
> > > transacction is aborted. You are supposed to use transactions when you
want
> > > either everything to occur (the whole transaction), or nothing, if an
> > > error occurs.
And thats wrong!
The caller should have a change to handle the error. Like if a "insert"
fails, you might want to use "update" instead. It should be the caller who
decides if the transaction should be aborted ("rollback") or not.
As it is now transactions are _totally_ useless with dba:s that serves more
than one client.
...
> > There is obviously no
> > reason why a transaction needs to be aborted for syntax errors.
Absolutely correct. It should be the caller who decides what he wants to do
with the transaction (rollback, or just continue as nothing happened).
...
> A bank is transferring money from one acount to another. Say the money
> leaves the first account (first update query), and then an error occurs
> when inserting the money into the second account (second update query). If
...
Schematic code snipped:
BEGIN;
update table account set credit = credit + 100;
if( error )
{
insert into account (credit,debet) VALUES( 100,0 );
if( error )
{
ROLLBACK;
return FAILED;
}
}
update table account set debet = debet + 100;
if( error )
{
insert into account (credit, debet) VALUES( 0, 100 );
if( error )
{
ROLLBACK;
return FAILED;
}
}
COMMIT;
That is the _correct_ way to do a bank transaction. And that is how
transactions should work.
...
> That is the whole point of transactions - they are used for an
> "all-or-nothing" approach.
Correct, but it should be the caller who decides what to do. Not the dba.
...
> The transaction succeeds, and you end up with two phones with the same
> number. BAD thing.
Your still wrong about the correct dba behaviour. It should be the callers
decision, not the dba.
> > nessasary. If you don't believe me, here's two fully SQL-92 compliant
> > databases, Oracle and interbase, which do not exhibit this behavior:
I do not give a sh** about SQL9_. There are nothing that forbids a dba to be
better than something.
...
> So, what would you like to be the criteria for aborting or proceeding with
> a transaction?
dba should not try to guess what I want to do with a transaction. It should
repport all errors to me (the caller) and let me decide what to do with the
transaction, period.
...
> > > If you don't like this behaviour, then use auto-commit, and make every
And thats stupid.
...
> > grouping a set of statements and commiting them or rolling them back as
> > a whole. I do not, however, want the transaction aborted by the server
Thats how it should be.
...
> > when it does not need to be. Clearly in the above case, neither
> > interbase nor oracle decided that the transaction had to be aborted.
Neither does Sybase or MSSQL.
// Jarmo
Re: [GENERAL] Unanswered questions about Postgre
On 30 Nov 2000, at 11:58, Joe Kislo wrote: > If you don't believe me, here's two fully SQL-92 > compliant databases, Oracle and interbase, which do not exhibit this > behavior: Ummm... havings lots of experience w/it, I can say many things about Oracle, but "fully SQL-92 compliant" sure isn't one of them. :-) -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
RE: [GENERAL] Unanswered questions about Postgre
> > That is what transactions are for. If any errors occur, then the > > transacction is aborted. You are supposed to use > > transactions when you want either everything to occur > > (the whole transaction), or nothing, if an error occurs. > > Yes. There are certainly times when a transaction needs to be > ABORTed. However, there are many reasons why the database should not > abort a transaction if it does not need to. There is obviously no > reason why a transaction needs to be aborted for syntax errors. There > is obviously no reason why a transaction needs to be aborted for say, > trying to insert a duplicate primary key. The -insert- can > fail, report it as such, and the application can determine if a rollback > is nessasary. If you don't believe me, here's two fully SQL-92 > compliant databases, Oracle and interbase, which do not exhibit this behavior: Oracle & Interbase have savepoints. Hopefully PG will also have them in 7.2 Vadim
Re: [GENERAL] Unanswered questions about Postgre
>> How _much_ longer? (Sorry if it's a FAQ, in that case, I'd appreciate >> a pointer/URL). > Dunno, but I've been using 7.1devel for ~2 months, and so far, > longer rows seem to work fine. > More information on the TOAST project is at > http://www.postgresql.org/projects/devel-toast.html I think I pontificated about this a month or two back, so check the archives; but the short answer is that the effective limit under TOAST is not on the total amount of data in a row, but just on the number of columns. The master copy of the row still has to fit into a block. Worst case, suppose every one of your columns is "wide" and so gets pushed out to BLOB storage. The BLOB pointer that still has to fit into the main row takes 32 bytes. With a maximum main row size of 8K, you can have about 250 columns. In practice, probably some of your columns would be ints or floats or booleans or something else that takes up less than 32 bytes, so the effective limit is probably order-of-magnitude-of 1000 columns in a table. If that seems too small, maybe you need to rethink your database design ;-) There's also a 1G limit on the size of an individual BLOB that can be part of a row. regards, tom lane
Re: [GENERAL] Unanswered questions about Postgre
On 30 Nov 2000, at 1:24, Igor V. Rafienko wrote: > on Nov 29, 2000, 19:17, Joel Burton std::cout'ed: > > [snip] > > | > 5) BLOB Support. > | > | Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard | > right) will support much longer row sizes than 8k. Doesn't remove | > the needs for blobs for many of us, but fixed my problems. > > > How _much_ longer? (Sorry if it's a FAQ, in that case, I'd appreciate > a pointer/URL). Dunno, but I've been using 7.1devel for ~2 months, and so far, longer rows seem to work fine. More information on the TOAST project is at http://www.postgresql.org/projects/devel-toast.html -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
Re: [GENERAL] Unanswered questions about Postgre
on Nov 29, 2000, 19:17, Joel Burton std::cout'ed: [snip] | > 5) BLOB Support. | | Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard | right) will support much longer row sizes than 8k. Doesn't remove | the needs for blobs for many of us, but fixed my problems. How _much_ longer? (Sorry if it's a FAQ, in that case, I'd appreciate a pointer/URL). ivr -- Intelligence est rélative. Par rapport à T*, c'est un génie. -- James Kanze sur "Smart Pointer"
Re: [GENERAL] Unanswered questions about Postgre
[re: question #4, speed/vacuuming] > Do > people need to vaccume their databases hourly? Can you vaccume while > a database is in use? Any discussion on this curious phenomenon would > be appreciated. It still boggles me. I vacuum twice a day, once in the dead of night, once around lunch. Yes, you can vacuum while the db is in use, but many locks (for updates, inserts, etc.) will hold up the vacuum. > 5) BLOB Support. Keep in mind the pgsql 7.1 (about to go beta tomorrow, if I heard right) will support much longer row sizes than 8k. Doesn't remove the needs for blobs for many of us, but fixed my problems. I believe the docs discussing the c-level interfaces talk about lo creation and such. Have you looked in the low-level docs in the programmer/developer manuals? I have only played w/blobs; others can speak better about their use/limitations, but if I have it correct: . blobs cannot be dumped . blobs are not normally vacuumed So, for most of us, I think the TOAST feature of 7.1 that allows >8k row sizes is much nicer. (Unless, of course, you really want to store binary data, not just long text fields.) Good luck, -- Joel Burton, Director of Information Systems -*- [EMAIL PROTECTED] Support Center of Washington (www.scw.org)
