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(at)svana(dot)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(at)svana(dot)org>
To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
Cc: <pgsql-general(at)postgresql(dot)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(at)svana(dot)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(at)svana(dot)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/

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(at)svana(dot)org>
> To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
> Cc: <pgsql-general(at)postgresql(dot)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(at)svana(dot)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
>

"Dominic J. Eidson" <sauron(at)the-infinite(dot)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

...
> > 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

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(at)wireboard(dot)com>
To: "Dominic J. Eidson" <sauron(at)the-infinite(dot)org>
Cc: <thomas(at)pgsql(dot)com>; "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>;
<pgsql-general(at)postgresql(dot)org>
Sent: Wednesday, October 24, 2001 6:12 PM
Subject: Re: [GENERAL] Disable Transaction - plans ?

> "Dominic J. Eidson" <sauron(at)the-infinite(dot)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 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

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/

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(at)mascari(dot)com

> > 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.

> 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(at)mascari(dot)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

> > 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

Thomas Lockhart <lockhart(at)fourpalms(dot)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

> 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

Thomas Lockhart <lockhart(at)fourpalms(dot)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

> 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(at)candle(dot)pha(dot)pa(dot)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(at)fourpalms(dot)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(at)mascari(dot)com

Mike Mascari <mascarm(at)mascari(dot)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

Tom Lane wrote:
>
> Mike Mascari <mascarm(at)mascari(dot)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(at)mascari(dot)com

> Mike Mascari <mascarm(at)mascari(dot)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(at)candle(dot)pha(dot)pa(dot)us | (610) 853-3000
+ If your life is a hard drive, | 830 Blythe Avenue
+ Christ can be your backup. | Drexel Hill, Pennsylvania 19026

> > Mike Mascari <mascarm(at)mascari(dot)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(at)candle(dot)pha(dot)pa(dot)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(at)fourpalms(dot)org>
To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
Cc: "Doug McNaught" <doug(at)wireboard(dot)com>; <pgsql-general(at)postgresql(dot)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
--------------------------

...
> 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

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(at)fourpalms(dot)org>
To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
Cc: <thomas(at)pgsql(dot)com>; "Doug McNaught" <doug(at)wireboard(dot)com>;
<pgsql-general(at)postgresql(dot)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
>

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(at)fourpalms(dot)org>
> To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
> Cc: "Doug McNaught" <doug(at)wireboard(dot)com>; <pgsql-general(at)postgresql(dot)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(at)csl(dot)co(dot)uk>
To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
Cc: <thomas(at)pgsql(dot)com>; "Doug McNaught" <doug(at)wireboard(dot)com>;
<pgsql-general(at)postgresql(dot)org>; "Lee Kindness" <lkindness(at)csl(dot)co(dot)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(at)fourpalms(dot)org>
> > To: "Ben-Nes Michael" <miki(at)canaan(dot)co(dot)il>
> > Cc: "Doug McNaught" <doug(at)wireboard(dot)com>;
<pgsql-general(at)postgresql(dot)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(at)mascari(dot)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(at)postgresql(dot)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