Thread: Disable Transaction - plans ?
Hi Is there any plan to relase a version which allow the user to disable the use of transactions on some of the tables ? -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il --------------------------
On Wed, Oct 24, 2001 at 11:23:49AM +0200, Ben-Nes Michael wrote: > Hi > > Is there any plan to relase a version which allow the user to disable the > use of transactions on some of the tables ? And what would be the use of that? Perhaps you should explain your problem because what you are asking not a solution to anything. HTH, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
Simple. In my dynamic sites I have sessions tables that get select/update/insert allot, So removing as many obstacles from select/update/insert against "very simple" tables will improve the performance. -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -------------------------- ----- Original Message ----- From: "Martijn van Oosterhout" <kleptog@svana.org> To: "Ben-Nes Michael" <miki@canaan.co.il> Cc: <pgsql-general@postgresql.org> Sent: Wednesday, October 24, 2001 12:13 PM Subject: Re: [GENERAL] Disable Transaction - plans ? > On Wed, Oct 24, 2001 at 11:23:49AM +0200, Ben-Nes Michael wrote: > > Hi > > > > Is there any plan to relase a version which allow the user to disable the > > use of transactions on some of the tables ? > > And what would be the use of that? > > Perhaps you should explain your problem because what you are asking not a > solution to anything. > > HTH, > -- > Martijn van Oosterhout <kleptog@svana.org> > http://svana.org/kleptog/ > > Magnetism, electricity and motion are like a three-for-two special offer: > > if you have two of them, the third one comes free. >
On Wed, Oct 24, 2001 at 12:32:06PM +0200, Ben-Nes Michael wrote: > Simple. > > In my dynamic sites I have sessions tables that get select/update/insert > allot, So removing as many obstacles from select/update/insert against "very > simple" tables will improve the performance. Well, I think you may be barking up the wrong tree. The overhead for transactions is very low. You are under the impression that that transactions slow the database down? Removing transactions would basically remove the whole point of having an RDBMS. Seriously, transactions are no performance impediment. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Magnetism, electricity and motion are like a three-for-two special offer: > if you have two of them, the third one comes free.
...
> Is there any plan to relase a version which allow the user to disable the
> use of transactions on some of the tables ?
No. We also have no plans to break other fundamental RDBMS features.
Why would you want such a thing?
- Thomas
On Wed, 24 Oct 2001, Thomas Lockhart wrote:
> > Is there any plan to relase a version which allow the user to disable the
> > use of transactions on some of the tables ?
>
> No. We also have no plans to break other fundamental RDBMS features.
>
> Why would you want such a thing?
Speed? :) (smirk)
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
No, I don't want MySQL I want specific few chosen tables to be without transaction like: session table All the other tables (95%) must have transaction ... foreign keys .... inherits ... views ....... I think it can be very beneficial to chop of portion of the code or even implant another system of tables into Postgres that will enable the most simplest fastest query. If the developers think that the speed increase is irrelevant then lets drop the issue :) -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -------------------------- ----- Original Message ----- From: "Doug McNaught" <doug@wireboard.com> To: "Dominic J. Eidson" <sauron@the-infinite.org> Cc: <thomas@pgsql.com>; "Ben-Nes Michael" <miki@canaan.co.il>; <pgsql-general@postgresql.org> Sent: Wednesday, October 24, 2001 6:12 PM Subject: Re: [GENERAL] Disable Transaction - plans ? > "Dominic J. Eidson" <sauron@the-infinite.org> writes: > > > On Wed, 24 Oct 2001, Thomas Lockhart wrote: > > > > > > Is there any plan to relase a version which allow the user to disable the > > > > use of transactions on some of the tables ? > > > > > > No. We also have no plans to break other fundamental RDBMS features. > > > > > > Why would you want such a thing? > > > > Speed? :) (smirk) > > If you want MySQL, you know where to find it. ;) > > -Doug > -- > Let us cross over the river, and rest under the shade of the trees. > --T. J. Jackson, 1863 >
"Dominic J. Eidson" <sauron@the-infinite.org> writes: > On Wed, 24 Oct 2001, Thomas Lockhart wrote: > > > > Is there any plan to relase a version which allow the user to disable the > > > use of transactions on some of the tables ? > > > > No. We also have no plans to break other fundamental RDBMS features. > > > > Why would you want such a thing? > > Speed? :) (smirk) If you want MySQL, you know where to find it. ;) -Doug -- Let us cross over the river, and rest under the shade of the trees. --T. J. Jackson, 1863
If performance is slowing down, I suggest you run VACUUM or VACUUM ANALYZE once a day. In addition, you can turn DBI's AutoCommit (assuming this is what you are using to connect to Postgres) to on, which will end the transaction block and unlock the table for VACUUM. Without knowing your configuration or your performance problem, it is hard to make any further suggestions. Mark Epilogue.net > Simple. > > In my dynamic sites I have sessions tables that get select/update/insert > allot, So removing as many obstacles from select/update/insert against "very > simple" tables will improve the performance. > > -------------------------- > Canaan Surfing Ltd. > Internet Service Providers > Ben-Nes Michael - Manager > Tel: 972-4-6991122 > http://sites.canaan.co.il > -------------------------- > > ----- Original Message ----- > From: "Martijn van Oosterhout" <kleptog@svana.org> > To: "Ben-Nes Michael" <miki@canaan.co.il> > Cc: <pgsql-general@postgresql.org> > Sent: Wednesday, October 24, 2001 12:13 PM > Subject: Re: [GENERAL] Disable Transaction - plans ? > > > > On Wed, Oct 24, 2001 at 11:23:49AM +0200, Ben-Nes Michael wrote: > > > Hi > > > > > > Is there any plan to relase a version which allow the user to disable > the > > > use of transactions on some of the tables ? > > > > And what would be the use of that? > > > > Perhaps you should explain your problem because what you are asking not a > > solution to anything. > > > > HTH, > > -- > > Martijn van Oosterhout <kleptog@svana.org> > > http://svana.org/kleptog/ > > > Magnetism, electricity and motion are like a three-for-two special > offer: > > > if you have two of them, the third one comes free. > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org >
...
> > Speed? :) (smirk)
> If you want MySQL, you know where to find it. ;)
Hmm. Assuming that "speed" is the outcome of dropping transaction
support is a bit simplistic (and linking "speed" and "mysql" and "no
transactions" has been shown to be a hollow argument). In fact, without
transactions (or with a transaction for every query) you see the full
effects of requiring a "commit" at every query. Buffers must be written
and everything having to do with the query must be cleaned up. With
transactions, some of those steps are postponed until commit, and you
will see a per-query performance gain by lumping many queries into one
transaction.
Also, without transactions and without MVCC, you must tightly coordinate
every query and every affected row in every table, which can choke off
performance as you scale to larger numbers of clients.
- Thomas
...
> If the developers think that the speed increase is irrelevant then lets drop
> the issue :)
No problem. We will be happy to entertain patches or demonstrations that
show a clear benefit in speed for the changes you suggest.
- Thomas
Thomas Lockhart wrote: > > ... > > > Speed? :) (smirk) > > If you want MySQL, you know where to find it. ;) > > Hmm. Assuming that "speed" is the outcome of dropping transaction > support is a bit simplistic (and linking "speed" and "mysql" and "no > transactions" has been shown to be a hollow argument). In fact, without > transactions (or with a transaction for every query) you see the full > effects of requiring a "commit" at every query. Buffers must be written > and everything having to do with the query must be cleaned up. With > transactions, some of those steps are postponed until commit, and you > will see a per-query performance gain by lumping many queries into one > transaction. > > Also, without transactions and without MVCC, you must tightly coordinate > every query and every affected row in every table, which can choke off > performance as you scale to larger numbers of clients. In fact, some could argue that the default behavior of PostgreSQL should be changed (or at least have an option) to behave like Oracle, where a transaction is implicitly begun at the first encounter of an INSERT/UPDATE/DELETE - or in PostgreSQL's case, the first submitted statement. That would make PostgreSQL behave much better out-of-the-box in comparison to mySQL, and would satiate the desires of those Oracle folks transitioning to PostgreSQL that do a: DELETE FROM foo; -- Nuts! ROLLBACK; and expect their DELETE to be undone. FWIW, Mike Mascari mascarm@mascari.com
On Wed, 24 Oct 2001, Thomas Lockhart wrote:
> ...
> > > Speed? :) (smirk)
> > If you want MySQL, you know where to find it. ;)
>
> Hmm. Assuming that "speed" is the outcome of dropping transaction
> support is a bit simplistic (and linking "speed" and "mysql" and "no
> transactions" has been shown to be a hollow argument). In fact, without
In case it escaped a bunch of people - my "Speed?" comment was
tongue-in-cheek making fun of the MySQL crowd...
--
Dominic J. Eidson
"Baruk Khazad! Khazad ai-menu!" - Gimli
-------------------------------------------------------------------------------
http://www.the-infinite.org/ http://www.the-infinite.org/~dominic/
> In case it escaped a bunch of people - my "Speed?" comment was
> tongue-in-cheek making fun of the MySQL crowd...
It didn't escape me, but I didn't want to focus on the fact that you
might be needling someone... ;)
And for someone not familiar with the past discussions, they *might*
have thought you were serious!
- Thomas
Mike Mascari <mascarm@mascari.com> writes:
> In fact, some could argue that the default behavior of PostgreSQL
> should be changed (or at least have an option) to behave like
> Oracle, where a transaction is implicitly begun at the first
> encounter of an INSERT/UPDATE/DELETE - or in PostgreSQL's case, the
> first submitted statement.
If we put in an implicit BEGIN at the start of a connection, when
does it get committed? We certainly dare not do an implicit COMMIT
when the client disconnects, but without that the change would
completely break a lot of existing applications.
Personally I'm perfectly happy with the notion that clients who want
this behavior can send a BEGIN for themselves ...
regards, tom lane
> > If the developers think that the speed increase is irrelevant then lets drop > > the issue :) > > No problem. We will be happy to entertain patches or demonstrations that > show a clear benefit in speed for the changes you suggest. Speed and memory efficiency used to be big issues, but I think now fast hardware is so absurdly cheap that things like stability and other features are more important than speed. I think that's why people are doing a lot of server stuff in java. It's definitely slow, but it's also more solid and lets you develop more flexibly, and that's more important than speed.
Mike Mascari <mascarm@mascari.com> writes:
> So would it be
> possible to modify PQFinish() to examine whether autocommit = true,
> and if so, issue a commit before disconnect, else just disconnect?
That's just a slightly different way of shooting yourself in the foot.
What's the difference whether it's libpq or the backend that pulls
the trigger? It's still not an explicit decision by the client.
I guess I do not understand the motivation for this proposal.
As I see it, the idea is that the client does not want an autocommit,
so he sets an option saying "no autocommit, hold transaction open until
I explicitly commit". Why exactly would such a client think that an
autocommit on disconnect is a good idea? The whole POINT is to require
an explicit commit command. (ie, the client wants the gun in his own
hand, no delegation of the trigger decision, thank you very much)
ISTM people who like autocommit will be using our existing behavior.
regards, tom lane
Thomas Lockhart <lockhart@fourpalms.org> writes:
> When autocommit is "off", the transaction gets committed when you exit
> or when you explicitly commit.
Only when you explicitly commit, please. Autocommit when we lose the
client connection is not acceptable, even if the client thinks he
wants it.
regards, tom lane
Thomas Lockhart <lockhart@fourpalms.org> writes:
> There is a client-side termination handshake, and it could be tied to
> that. If we can distinguish between an intentional exit and an
> unexpected termination, then istm that a commit would be safe to apply
> for the former.
I assert that we cannot safely determine that; it's too easy for a
client logic error to tell libpq to close the connection at the wrong
time, and we should NOT default to committing when that happens.
I think a client using this mode should be expected to send an explicit
commit command before exiting.
regards, tom lane
> Only when you explicitly commit, please. Autocommit when we lose the
> client connection is not acceptable, even if the client thinks he
> wants it.
There is a client-side termination handshake, and it could be tied to
that. If we can distinguish between an intentional exit and an
unexpected termination, then istm that a commit would be safe to apply
for the former.
I'm recalling that this is the behavior of Ingres, but it has been a few
years since I've dealt with it...
- Thomas
> > In fact, some could argue that the default behavior of PostgreSQL
> > should be changed (or at least have an option) to behave like
> > Oracle, where a transaction is implicitly begun at the first
> > encounter of an INSERT/UPDATE/DELETE - or in PostgreSQL's case, the
> > first submitted statement.
> If we put in an implicit BEGIN at the start of a connection, when
> does it get committed? We certainly dare not do an implicit COMMIT
> when the client disconnects, but without that the change would
> completely break a lot of existing applications.
Ingres solved this with a mode switch; "set autocommit on|off" which is
in the style that we would likely use too. It *should* be optional, but
settable, and we can debate which should be the default later.
When autocommit is "off", the transaction gets committed when you exit
or when you explicitly commit.
- Thomas
> In fact, some could argue that the default behavior of PostgreSQL
> should be changed (or at least have an option) to behave like
> Oracle, where a transaction is implicitly begun at the first
> encounter of an INSERT/UPDATE/DELETE - or in PostgreSQL's case, the
> first submitted statement. That would make PostgreSQL behave much
> better out-of-the-box in comparison to mySQL, and would satiate the
> desires of those Oracle folks transitioning to PostgreSQL that do a:
>
> DELETE FROM foo;
> -- Nuts!
> ROLLBACK;
>
> and expect their DELETE to be undone.
TODO has:
* Allow autocommit so always in a transaction block
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Tom Lane wrote: > > Thomas Lockhart <lockhart@fourpalms.org> writes: > > There is a client-side termination handshake, and it could be tied to > > that. If we can distinguish between an intentional exit and an > > unexpected termination, then istm that a commit would be safe to apply > > for the former. > > I assert that we cannot safely determine that; it's too easy for a > client logic error to tell libpq to close the connection at the wrong > time, and we should NOT default to committing when that happens. > > I think a client using this mode should be expected to send an explicit > commit command before exiting. With respect to Oracle, use of the libpq's equivalent to PQFinish(), OCISessionEnd(), implicitly issues a COMMIT before disconnecting, so it appears to be the client's responsibility wrt Oracle. SQL*Plus users will note that, if their SQL*Plus session is exited normally via the QUIT command, their work will also be committed, since it is probably just calling OCISessionEnd() itself. So would it be possible to modify PQFinish() to examine whether autocommit = true, and if so, issue a commit before disconnect, else just disconnect? Mike Mascari mascarm@mascari.com
> Mike Mascari <mascarm@mascari.com> writes: > > In fact, some could argue that the default behavior of PostgreSQL > > should be changed (or at least have an option) to behave like > > Oracle, where a transaction is implicitly begun at the first > > encounter of an INSERT/UPDATE/DELETE - or in PostgreSQL's case, the > > first submitted statement. > > If we put in an implicit BEGIN at the start of a connection, when > does it get committed? We certainly dare not do an implicit COMMIT > when the client disconnects, but without that the change would > completely break a lot of existing applications. > > Personally I'm perfectly happy with the notion that clients who want > this behavior can send a BEGIN for themselves ... In Ingres, you typically do 'SET AUTOCOMMIT ON' and it says that way for the rest of the session. GUC would have a default setting for AUTOCOMMIT too. There is no plan to change the default, just to allow people to modify the default. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
Tom Lane wrote: > > Mike Mascari <mascarm@mascari.com> writes: > > So would it be > > possible to modify PQFinish() to examine whether autocommit = true, > > and if so, issue a commit before disconnect, else just disconnect? Sorry, that should have read autocommit = false. > > That's just a slightly different way of shooting yourself in the foot. > What's the difference whether it's libpq or the backend that pulls > the trigger? It's still not an explicit decision by the client. > > I guess I do not understand the motivation for this proposal. Its only based upon Oracle precedence, really. People start up SQL*Plus, issue a few statements, and quit. Some aren't even aware that they're involved in a transaction; only that they know they can undo their work by issuing a ROLLBACK and their work won't be saved if their machine crashes. I haven't used ecpg, but if I wanted to port Pro*C to PostgreSQL, I would have to modify code to ensure that my application wasn't issuing an implicit commit after every statement. > As I see it, the idea is that the client does not want an autocommit, > so he sets an option saying "no autocommit, hold transaction open until > I explicitly commit". Why exactly would such a client think that an > autocommit on disconnect is a good idea? The whole POINT is to require > an explicit commit command. (ie, the client wants the gun in his own > hand, no delegation of the trigger decision, thank you very much) I guess I was thinking in terms of a postgresql.conf parameter that affected all backends, that it would be a conscious decision by the DBA to install an RDBMS which did not autocommit each statement, but behaved like Oracle. Modifying libpq's PQFinish() would allow a psql talking to an auto-committing system to behave as it does now, but would behave like SQL*Plus for the DBA that chose to run an Oracle-like PostgreSQL installation. A secondary motivation is that I believe IT departments may choose to test PostgreSQL performance by submitting a bunch of their Oracle scripts against it and seeing how long it takes. It would be easier to tell them to set autocommit = false in postgresql.conf then to tell them to modify all their scripts. One day, perhaps, autocommit = false would become the default, and then PostgreSQL's speed, which has been unfairly characterized by the uninformed, will compare favorably to Oracle (and others) out-of-the-box. You know an IT department would do something like this: "Let's test the speed of PostgreSQL": rpm -Uvh postgresql* /etc/rc.d/init.d/postgresql start su -l postgres createdb test date; psql test < my_oracle_timing_test.sql; date "Boy that PostgreSQL sure is slow!" rpm -qa | fgrep "postgres" | xargs rpm -e > > ISTM people who like autocommit will be using our existing behavior. > I'm not married to the idea, BTW ;-) Mike Mascari mascarm@mascari.com
> > Mike Mascari <mascarm@mascari.com> writes: > > > In fact, some could argue that the default behavior of PostgreSQL > > > should be changed (or at least have an option) to behave like > > > Oracle, where a transaction is implicitly begun at the first > > > encounter of an INSERT/UPDATE/DELETE - or in PostgreSQL's case, the > > > first submitted statement. > > > > If we put in an implicit BEGIN at the start of a connection, when > > does it get committed? We certainly dare not do an implicit COMMIT > > when the client disconnects, but without that the change would > > completely break a lot of existing applications. > > > > Personally I'm perfectly happy with the notion that clients who want > > this behavior can send a BEGIN for themselves ... > > In Ingres, you typically do 'SET AUTOCOMMIT ON' and it says that way for > the rest of the session. GUC would have a default setting for > AUTOCOMMIT too. > > There is no plan to change the default, just to allow people to modify > the default. Sorry meant SET AUTOCOMMIT OFF. -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000 + If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania 19026
...
> > I guess I do not understand the motivation for this proposal.
> Its only based upon Oracle precedence, really. People start up
> SQL*Plus, issue a few statements, and quit...
... and it is clearly behavior in other databases also (e.g. Ingres).
These two at least date from the early 1980's. Somehow, the database
community is accustomed to this behavior, and though I'm not sure I'd
insist on using it in my applications I'm also not sure why we should
not provide a similar level of functionality and behavior as an option.
- Thomas
Hi All If the speed will be much higher I might humbly suggest to develop two kind of tables. 1. like the current one ( with great integrity ) 2. Like MySQL or even less sophisticated for application that need to work very fast against one table only. Example: News page that require the most simple select ( select * from news; ) and only one man update it once a day. session table which require on each web page update,insert,select,delete. ( some times more then once ) especially if I store class var init. Think on the programing benefit and the commercial publicity Postgresql can get by telling: "we supply Oracle complexity & MySQL simplicity" Cheer ----- Original Message ----- From: "Thomas Lockhart" <lockhart@fourpalms.org> To: "Ben-Nes Michael" <miki@canaan.co.il> Cc: "Doug McNaught" <doug@wireboard.com>; <pgsql-general@postgresql.org> Sent: Wednesday, October 24, 2001 11:01 PM Subject: Re: [GENERAL] Disable Transaction - plans ? > ... > > If the developers think that the speed increase is irrelevant then lets drop > > the issue :) > > No problem. We will be happy to entertain patches or demonstrations that > show a clear benefit in speed for the changes you suggest. > > - Thomas > -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il --------------------------
Well im not a copywriter, but I hope you understood the need for some tables to be lighting fast. If it can achieved with integrity then great :) if without integrity it can work twice the speed it might be a consideration. By the way 95% of my tables must have integrity as it is, only fraction of my tables need to be blazing fast. -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -------------------------- ----- Original Message ----- From: "Thomas Lockhart" <lockhart@fourpalms.org> To: "Ben-Nes Michael" <miki@canaan.co.il> Cc: <thomas@pgsql.com>; "Doug McNaught" <doug@wireboard.com>; <pgsql-general@postgresql.org> Sent: Thursday, October 25, 2001 3:37 PM Subject: Re: [GENERAL] Disable Transaction - plans ? > ... > > Think on the programing benefit and the commercial publicity Postgresql can > > get by telling: "we supply Oracle complexity & MySQL simplicity" > > How about "we supply Oracle features and are faster than MySQL". > > Already there :)) > > - Thomas >
...
> Think on the programing benefit and the commercial publicity Postgresql can
> get by telling: "we supply Oracle complexity & MySQL simplicity"
How about "we supply Oracle features and are faster than MySQL".
Already there :))
- Thomas
What's wrong with using the appropriate tool for the task at hand? If you don't need the features of a database don't use one! If you've got modest DB needs look for an appropriate DB: gdbm: http://www.gnu.org/software/gdbm/ Berkeley DB: http://www.sleepycat.com/ Personally if I only had one table I'd be making my own, highly tuned, access routines and storage... Regards, Lee. Ben-Nes Michael writes: > Hi All > > If the speed will be much higher I might humbly suggest to develop two kind > of tables. > 1. like the current one ( with great integrity ) > 2. Like MySQL or even less sophisticated for application that need to work > very fast against one table only. > > Example: > News page that require the most simple select ( select * from news; ) and > only one man update it once a day. > session table which require on each web page update,insert,select,delete. > ( some times more then once ) especially if I store class var init. > > Think on the programing benefit and the commercial publicity Postgresql can > get by telling: "we supply Oracle complexity & MySQL simplicity" > > Cheer > > > ----- Original Message ----- > From: "Thomas Lockhart" <lockhart@fourpalms.org> > To: "Ben-Nes Michael" <miki@canaan.co.il> > Cc: "Doug McNaught" <doug@wireboard.com>; <pgsql-general@postgresql.org> > Sent: Wednesday, October 24, 2001 11:01 PM > Subject: Re: [GENERAL] Disable Transaction - plans ? > > > > ... > > > If the developers think that the speed increase is irrelevant then lets > drop > > > the issue :) > > > > No problem. We will be happy to entertain patches or demonstrations that > > show a clear benefit in speed for the changes you suggest. > > > > - Thomas > > > -------------------------- > Canaan Surfing Ltd. > Internet Service Providers > Ben-Nes Michael - Manager > Tel: 972-4-6991122 > http://sites.canaan.co.il > -------------------------- > > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
Is it faster ? Any way, less method of storing data in application the better it is. All my old sites are on MySQL since this was the first db I was recommended on, but when I tried Postgres I left MySQL and from then on Im using only Postgres. I could convert all the site for postgres but it will take too much time which I don't have. So currently I have two types of DB on my Server which make admin life more difficult in many topics. So I don't think ill add another one to make life more difficult. -------------------------- Canaan Surfing Ltd. Internet Service Providers Ben-Nes Michael - Manager Tel: 972-4-6991122 http://sites.canaan.co.il -------------------------- ----- Original Message ----- From: "Lee Kindness" <lkindness@csl.co.uk> To: "Ben-Nes Michael" <miki@canaan.co.il> Cc: <thomas@pgsql.com>; "Doug McNaught" <doug@wireboard.com>; <pgsql-general@postgresql.org>; "Lee Kindness" <lkindness@csl.co.uk> Sent: Thursday, October 25, 2001 5:58 PM Subject: Re: [GENERAL] Disable Transaction - plans ? > What's wrong with using the appropriate tool for the task at hand? If > you don't need the features of a database don't use one! > > If you've got modest DB needs look for an appropriate DB: > > gdbm: http://www.gnu.org/software/gdbm/ > Berkeley DB: http://www.sleepycat.com/ > > Personally if I only had one table I'd be making my own, highly tuned, > access routines and storage... > > Regards, Lee. > > Ben-Nes Michael writes: > > Hi All > > > > If the speed will be much higher I might humbly suggest to develop two kind > > of tables. > > 1. like the current one ( with great integrity ) > > 2. Like MySQL or even less sophisticated for application that need to work > > very fast against one table only. > > > > Example: > > News page that require the most simple select ( select * from news; ) and > > only one man update it once a day. > > session table which require on each web page update,insert,select,delete. > > ( some times more then once ) especially if I store class var init. > > > > Think on the programing benefit and the commercial publicity Postgresql can > > get by telling: "we supply Oracle complexity & MySQL simplicity" > > > > Cheer > > > > > > ----- Original Message ----- > > From: "Thomas Lockhart" <lockhart@fourpalms.org> > > To: "Ben-Nes Michael" <miki@canaan.co.il> > > Cc: "Doug McNaught" <doug@wireboard.com>; <pgsql-general@postgresql.org> > > Sent: Wednesday, October 24, 2001 11:01 PM > > Subject: Re: [GENERAL] Disable Transaction - plans ? > > > > > > > ... > > > > If the developers think that the speed increase is irrelevant then lets > > drop > > > > the issue :) > > > > > > No problem. We will be happy to entertain patches or demonstrations that > > > show a clear benefit in speed for the changes you suggest. > > > > > > - Thomas > > > > > -------------------------- > > Canaan Surfing Ltd. > > Internet Service Providers > > Ben-Nes Michael - Manager > > Tel: 972-4-6991122 > > http://sites.canaan.co.il > > -------------------------- > > > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 6: Have you searched our list archives? > > > > http://archives.postgresql.org >
Tom, I think the reason Oracle (and possibly others databases) do it the otherway (i.e. implicit commit if session is closed normally) is to require an explicit rollback, instead of requiring an explicit commit. The assumption being that most of the time you want to commit the changes you just made. Since this is the common case don't require a commit in this case (save the user some work). The corner case is when you don't want your changes and thus it makes sense to require a rollback in this case. In Oracle's SQLPlus the default is to commit on exit, but that can be changed to rollback on exit if desired. Having said that, personally I agree with your position. So perhaps it might make sense to do the following: At the server level don't issue an implicit commit at connection close. However psql could have a mode that does issue an implicit commit when it exits. This would make it possible for psql to mirror the behavior of Oracle's SQLPlus. thanks, --Barry Tom Lane wrote: > Mike Mascari <mascarm@mascari.com> writes: > >>So would it be >>possible to modify PQFinish() to examine whether autocommit = true, >>and if so, issue a commit before disconnect, else just disconnect? >> > > That's just a slightly different way of shooting yourself in the foot. > What's the difference whether it's libpq or the backend that pulls > the trigger? It's still not an explicit decision by the client. > > I guess I do not understand the motivation for this proposal. > As I see it, the idea is that the client does not want an autocommit, > so he sets an option saying "no autocommit, hold transaction open until > I explicitly commit". Why exactly would such a client think that an > autocommit on disconnect is a good idea? The whole POINT is to require > an explicit commit command. (ie, the client wants the gun in his own > hand, no delegation of the trigger decision, thank you very much) > > ISTM people who like autocommit will be using our existing behavior. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > >
At 06:31 PM 10/24/01 +0200, Ben-Nes Michael wrote: >No, I don't want MySQL I want specific few chosen tables to be without >transaction like: session table >All the other tables (95%) must have transaction ... foreign keys .... >inherits ... views ....... > >I think it can be very beneficial to chop of portion of the code or even >implant another system of tables into Postgres that will enable the most >simplest fastest query. > >If the developers think that the speed increase is irrelevant then lets drop >the issue :) Just curious. Is MySQL faster or slower than Postgresql for what you want to do fast now? Cheerio, Link.
On Wed, 24 Oct 2001, Dominic J. Eidson wrote: > On Wed, 24 Oct 2001, Thomas Lockhart wrote: > > > > Is there any plan to relase a version which allow the user to disable the > > > use of transactions on some of the tables ? > > > > No. We also have no plans to break other fundamental RDBMS features. > > > > Why would you want such a thing? > > Speed? :) (smirk) Buy more memory or change the hardware. I am just getting into tuning my initial PostgreSQL database and there are a few settings which may be worthwhile looking into. So far from what I am reading giving more memory to the database can help. Also using more disks and create aliases to certain files may also help. Check www.postgresql.org/docs/awbook.html