Thread

Help with count(*)

Rajesh Kumar Mallah <mallah@trade-india.com> — 2003-11-14T07:21:38Z


Hi , 

my database seems to be taking too long for a select count(*)
i think there are lot of dead rows. I do a vacuum full it improves
bu again the performance drops in a short while ,
can anyone please tell me if anything worng with my fsm settings
current fsm=55099264 (not sure how i calculated it)

Regds
Mallah

tradein_clients=# SELECT count(*) from data_bank.profiles ;

+--------+
| count  |
+--------+
| 123065 |
+--------+
(1 row)

Time: 49756.969 ms
tradein_clients=#
tradein_clients=#
tradein_clients=# VACUUM full verbose analyze  data_bank.profiles ;
INFO:  vacuuming "data_bank.profiles"

INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
DETAIL:  246130 dead row versions cannot be removed yet.
Nonremovable row versions range from 136 to 2036 bytes long.
There were 427579 unused item pointers.
Total free space (including removable row versions) is 178536020 bytes.
15934 pages are or will become empty, including 0 at the end of the table.
38112 pages containing 178196624 free bytes are potential move destinations.
CPU 1.51s/0.63u sec elapsed 23.52 sec.
INFO:  index "profiles_pincode" now contains 369195 row versions in 3353 pages
DETAIL:  0 index row versions were removed.
379 index pages have been deleted, 379 are currently reusable.
CPU 0.20s/0.24u sec elapsed 22.73 sec.
INFO:  index "profiles_city" now contains 369195 row versions in 3411 pages
DETAIL:  0 index row versions were removed.
1030 index pages have been deleted, 1030 are currently reusable.
CPU 0.17s/0.21u sec elapsed 20.67 sec.
INFO:  index "profiles_branch" now contains 369195 row versions in 2209 pages
DETAIL:  0 index row versions were removed.
783 index pages have been deleted, 783 are currently reusable.
CPU 0.07s/0.14u sec elapsed 6.38 sec.
INFO:  index "profiles_area_code" now contains 369195 row versions in 2606 pages
DETAIL:  0 index row versions were removed.
856 index pages have been deleted, 856 are currently reusable.
CPU 0.11s/0.17u sec elapsed 19.62 sec.
INFO:  index "profiles_source" now contains 369195 row versions in 3137 pages
DETAIL:  0 index row versions were removed.
1199 index pages have been deleted, 1199 are currently reusable.
CPU 0.14s/0.12u sec elapsed 9.95 sec.
INFO:  index "co_name_index_idx" now contains 368742 row versions in 3945 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.19s/0.69u sec elapsed 11.56 sec.
INFO:  index "address_index_idx" now contains 368898 row versions in 4828 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.16s/0.61u sec elapsed 9.17 sec.
INFO:  index "profiles_exp_cat" now contains 153954 row versions in 2168 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.07s/0.25u sec elapsed 3.14 sec.
INFO:  index "profiles_imp_cat" now contains 73476 row versions in 1030 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.05s/0.11u sec elapsed 8.73 sec.
INFO:  index "profiles_manu_cat" now contains 86534 row versions in 1193 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.13u sec elapsed 1.44 sec.
INFO:  index "profiles_serv_cat" now contains 19256 row versions in 267 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.01u sec elapsed 0.25 sec.
INFO:  index "profiles_pid" now contains 369195 row versions in 812 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.03s/0.12u sec elapsed 0.41 sec.
INFO:  index "profiles_pending_branch_id" now contains 0 row versions in 1 pages
DETAIL:  0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  "profiles": moved 0 row versions, truncated 43423 to 43423 pages
DETAIL:  CPU 1.76s/3.01u sec elapsed 60.39 sec.
INFO:  vacuuming "pg_toast.pg_toast_39873340"
INFO:  "pg_toast_39873340": found 0 removable, 65 nonremovable row versions in 15 pages
DETAIL:  0 dead row versions cannot be removed yet.
Nonremovable row versions range from 47 to 2034 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 17672 bytes.
0 pages are or will become empty, including 0 at the end of the table.
14 pages containing 17636 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.77 sec.
INFO:  index "pg_toast_39873340_index" now contains 65 row versions in 2 pages
DETAIL:  0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.46 sec.
INFO:  "pg_toast_39873340": moved 0 row versions, truncated 15 to 15 pages
DETAIL:  CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO:  analyzing "data_bank.profiles"
INFO:  "profiles": 43423 pages, 123065 rows sampled, 123065 estimated total rows
VACUUM
Time: 246989.138 ms
tradein_clients=# SELECT count(*) from data_bank.profiles ;
+--------+
| count  |
+--------+
| 123065 |
+--------+
(1 row)

Time: 4978.725 ms
tradein_clients=#

IMPORVED but still not very good.

Regds
Mallah.

Re: Help with count(*)

Shridhar Daithankar <shridhar_daithankar@myrealbox.com> — 2003-11-14T07:30:34Z

On Friday 14 November 2003 12:51, Rajesh Kumar Mallah wrote:
> Hi ,
>
> my database seems to be taking too long for a select count(*)
> i think there are lot of dead rows. I do a vacuum full it improves
> bu again the performance drops in a short while ,
> can anyone please tell me if anything worng with my fsm settings
> current fsm=55099264 (not sure how i calculated it)

If you don't need exact count, you can use statistics. Just analyze frequently 
and you will get the statistics.

and I didn't exact;y understand this in the text.

INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 
43423 pages
DETAIL:  246130 dead row versions cannot be removed yet.

Is there a transaction holoding up large amount of stuff?

 Shridhar

Re: Help with count(*)

Chris Browne <cbbrowne@acm.org> — 2003-11-14T14:13:48Z

Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
> INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
> DETAIL:  246130 dead row versions cannot be removed yet.
> Nonremovable row versions range from 136 to 2036 bytes long.

It seems as though you have a transaction open that is holding onto a
whole lot of old rows.

I have seen this happen somewhat-invisibly when a JDBC connection
manager opens transactions for each connection, and then no processing
happens to use those connections for a long time.  The open
transactions prevent vacuums from doing any good...
-- 
If this was helpful, <http://svcs.affero.net/rm.php?r=cbbrowne> rate me
http://cbbrowne.com/info/multiplexor.html
"Waving away a cloud of smoke, I  look up, and am blinded by a bright,
white light.  It's God. No,  not Richard Stallman, or  Linus Torvalds,
but God. In a booming voice, He  says: "THIS IS A SIGN. USE LINUX, THE
FREE Unix SYSTEM FOR THE 386." -- Matt Welsh

Re: Help with count(*)

Hannu Krosing <hannu@tm.ee> — 2003-11-14T17:43:27Z

Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
> Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
> > INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
> > DETAIL:  246130 dead row versions cannot be removed yet.
> > Nonremovable row versions range from 136 to 2036 bytes long.
> 
> It seems as though you have a transaction open that is holding onto a
> whole lot of old rows.
> 
> I have seen this happen somewhat-invisibly when a JDBC connection
> manager opens transactions for each connection, and then no processing
> happens to use those connections for a long time.  The open
> transactions prevent vacuums from doing any good...

Can't the backend be made to delay the "real" start of transaction until
the first query gets executed ?

------------
Hannu

Re: Help with count(*)

Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-14T18:49:59Z

Hannu Krosing <hannu@tm.ee> writes:
> Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
>> I have seen this happen somewhat-invisibly when a JDBC connection
>> manager opens transactions for each connection, and then no processing
>> happens to use those connections for a long time.  The open
>> transactions prevent vacuums from doing any good...

> Can't the backend be made to delay the "real" start of transaction until
> the first query gets executed ?

That is on the TODO list.  I looked at it briefly towards the end of the
7.4 development cycle, and decided that it was nontrivial and I didn't
have time to make it happen before beta started.  I don't recall why it
didn't seem trivial.

			regards, tom lane

Re: Help with count(*)

Chris Browne <cbbrowne@acm.org> — 2003-11-14T19:16:56Z

After a long battle with technology, hannu@tm.ee (Hannu Krosing), an earthling, wrote:
> Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
>> Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
>> > INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
>> > DETAIL:  246130 dead row versions cannot be removed yet.
>> > Nonremovable row versions range from 136 to 2036 bytes long.
>> 
>> It seems as though you have a transaction open that is holding onto a
>> whole lot of old rows.
>> 
>> I have seen this happen somewhat-invisibly when a JDBC connection
>> manager opens transactions for each connection, and then no processing
>> happens to use those connections for a long time.  The open
>> transactions prevent vacuums from doing any good...
>
> Can't the backend be made to delay the "real" start of transaction until
> the first query gets executed ?

One would hope so.  Some time when I have the Round Tuits, I ought to
take a browse of the connection pool code to notice if there's
anything to notice.  

The thing that I keep imagining would be a slick idea would be to have
a thread periodically go through once for however many connections the
pool permits and fire a short transaction through every
otherwise-unoccupied connection in the pool, in effect, doing a sort
of "vacuum" of the connections.  I don't get very favorable reactions
when I suggest that, though...
-- 
(reverse (concatenate 'string "ac.notelrac.teneerf" "@" "454aa"))
http://cbbrowne.com/info/sgml.html
Rules  of  the  Evil Overlord  #80.  "If  my  weakest troops  fail  to
eliminate a  hero, I will send  out my best troops  instead of wasting
time with progressively stronger ones  as he gets closer and closer to
my fortress." <http://www.eviloverlord.com/>

Re: Help with count(*)

Will LaShell <will@lashell.net> — 2003-11-14T19:40:23Z

Hannu Krosing wrote:

>Christopher Browne kirjutas R, 14.11.2003 kell 16:13:
>  
>
>>Martha Stewart called it a Good Thing when mallah@trade-india.com (Rajesh Kumar Mallah) wrote:
>>    
>>
>>>INFO:  "profiles": found 0 removable, 369195 nonremovable row versions in 43423 pages
>>>DETAIL:  246130 dead row versions cannot be removed yet.
>>>Nonremovable row versions range from 136 to 2036 bytes long.
>>>      
>>>
>>It seems as though you have a transaction open that is holding onto a
>>whole lot of old rows.
>>
>>I have seen this happen somewhat-invisibly when a JDBC connection
>>manager opens transactions for each connection, and then no processing
>>happens to use those connections for a long time.  The open
>>transactions prevent vacuums from doing any good...
>>    
>>
>
>Can't the backend be made to delay the "real" start of transaction until
>the first query gets executed ?
>  
>

That seems counter intuitive doesn't it?  Why write more code in the 
server when the client is the thing that has the problem?

Will

Re: Help with count(*)

Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-14T20:38:46Z

Will LaShell <will@lashell.net> writes:
> Hannu Krosing wrote:
>> Can't the backend be made to delay the "real" start of transaction until
>> the first query gets executed ?

> That seems counter intuitive doesn't it?  Why write more code in the 
> server when the client is the thing that has the problem?

Because there are a lot of clients with the same problem :-(

A more principled argument is that we already postpone the setting of
the transaction snapshot until the first query arrives within the
transaction.  In a very real sense, the setting of the snapshot *is*
the start of the transaction.  So it would make sense if incidental
stuff like VACUUM also thought that the transaction hadn't started
until the first query arrives.  (I believe the previous discussion
also agreed that we wanted to postpone the freezing of now(), which
currently also happens at BEGIN rather than the first command after
BEGIN.)

			regards, tom lane

Re: Help with count(*)

Andrew Sullivan <andrew@libertyrms.info> — 2003-11-14T21:30:16Z

On Fri, Nov 14, 2003 at 02:16:56PM -0500, Christopher Browne wrote:
> otherwise-unoccupied connection in the pool, in effect, doing a sort
> of "vacuum" of the connections.  I don't get very favorable reactions
> when I suggest that, though...

Because it's a kludge on top of another kludge, perhaps?  ;-)  This
needs to be fixed properly, not through an ungraceful series of
workarounds.

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

Re: Help with count(*)

Dennis Bjorklund <db@zigo.dhs.org> — 2003-11-15T10:21:09Z

On Fri, 14 Nov 2003, Tom Lane wrote:

> I believe the previous discussion also agreed that we wanted to postpone
> the freezing of now(), which currently also happens at BEGIN rather than
> the first command after BEGIN.

Or should that happen at the first call to now()?

/me should ge back and try to find this previous discussion.

-- 
/Dennis

Re: Help with count(*)

Neil Conway <neilc@samurai.com> — 2003-11-15T20:20:43Z

Tom Lane <tgl@sss.pgh.pa.us> writes:
> (I believe the previous discussion also agreed that we wanted to
> postpone the freezing of now(), which currently also happens at
> BEGIN rather than the first command after BEGIN.)

That doesn't make sense to me: from a user's perspective, the "start
of the transaction" is when the BEGIN is issued, regardless of any
tricks we may play in the backend.

Making now() return the time the current transaction started is
reasonably logical; making now() return "the time when the first
command after the BEGIN in the current transaction was issued" makes a
lot less sense to me.

-Neil

start of transaction (was: Re: [PERFORM] Help with count(*))

Hannu Krosing <hannu@tm.ee> — 2003-11-16T11:58:08Z

Redirected to -hackers

Neil Conway kirjutas L, 15.11.2003 kell 22:20:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
> > (I believe the previous discussion also agreed that we wanted to
> > postpone the freezing of now(), which currently also happens at
> > BEGIN rather than the first command after BEGIN.)
> 
> That doesn't make sense to me: from a user's perspective, the "start
> of the transaction" is when the BEGIN is issued, regardless of any
> tricks we may play in the backend.

For me, the "start of transaction" is not about time, but about grouping
a set of statements into one. So making the exact moment of "start" be
the first statement that actually does something with data seems
perfectly reasonable. If you really need to preserve time, do "select
current_timestamp" and use the result.

> Making now() return the time the current transaction started is
> reasonably logical; making now() return "the time when the first
> command after the BEGIN in the current transaction was issued" makes a
> lot less sense to me.

for me "the time the current transactuion is started" == "the time when
the first command after the BEGIN in the current transaction was issued"
and thus I see no conflict here ;)

Delaying the locking effects of transactions as long as possible can
increase performance overall, not just for pathological clients that sit
on idle open transactions.

Probably the latest time we can start the transaction is ath the start
of executor step after the first statement in a transaction is planned
and optimized.

---------------
Hannu

Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-16T14:50:11Z

Hannu Krosing <hannu@tm.ee> writes:
> Probably the latest time we can start the transaction is ath the start
> of executor step after the first statement in a transaction is planned
> and optimized.

The transaction has to exist before it can take locks, so the above
would not fly.

A complete example of what we have to think about is:

	BEGIN;
	SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
	LOCK TABLE foo;
	UPDATE foo ...  -- or in general a SELECT/UPDATE/INSERT/DELETE query
	... etc ...

The transaction snapshot *must* be set at the time of the first query
(here, the UPDATE).  It obviously can't be later, and it cannot be
earlier either, because in this sort of example you need the requested
locks to be taken before the snapshot is set.

The transaction must be created (as observed by other backends, in
particular VACUUM) not later than the LOCK statement, else there is
nothing that can own the lock.  In principle though, the effects of
BEGIN and perhaps SET could be strictly local to the current backend,
and only when we hit a LOCK or query do we create the transaction
externally.

In practice the problem we observe is clients that issue BEGIN and then
go to sleep (typically because of poorly-designed autocommit behavior in
interface libraries).  Postponing externally-visible creation of the
transaction to the first command after BEGIN would be enough to get
around the real-world issues, and it would not require code changes
nearly as extensive as trying to let other stuff like SET happen
"before" the transaction starts.

There isn't any compelling implementation reason when to freeze the
value of now().  Reasonable options are
	1. at BEGIN (current behavior)
	2. at transaction's external creation 
	3. at freezing of transaction snapshot
#1 and #2 are actually the same at the moment, but could be decoupled
as sketched above, in which case the behavior of #2 would effectively
become "at first command afte BEGIN".

In the previous thread:
http://archives.postgresql.org/pgsql-hackers/2003-03/msg01178.php
I argued that now() should be frozen at the time of the transaction
snapshot, and I still think that that's a defensible behavior.

			regards, tom lane

Re: Help with count(*)

Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-16T15:22:43Z

Neil Conway <neilc@samurai.com> writes:
> Tom Lane <tgl@sss.pgh.pa.us> writes:
>> (I believe the previous discussion also agreed that we wanted to
>> postpone the freezing of now(), which currently also happens at
>> BEGIN rather than the first command after BEGIN.)

> That doesn't make sense to me: from a user's perspective, the "start
> of the transaction" is when the BEGIN is issued, regardless of any
> tricks we may play in the backend.

That's defensible when the user issued the BEGIN himself.  When the
BEGIN is coming from some interface library's autocommit logic, it's
a lot less defensible.  If you consult the archives, you will find
actual user complaints about "why is now() returning a very old time?"
that we traced to use of interface layers that handle "commit()" by
issuing "COMMIT; BEGIN;".

When BEGIN actually is issued by live application logic, I'd expect it
to be followed immediately by some kind of command --- so the user would
be unable to tell the difference in practice.

Hannu moved this thread to -hackers, please follow up there if you want
to discuss it more.

			regards, tom lane

Re: start of transaction (was: Re: [PERFORM] Help with

Dennis Bjorklund <db@zigo.dhs.org> — 2003-11-16T15:51:49Z

On Sun, 16 Nov 2003, Tom Lane wrote:

> There isn't any compelling implementation reason when to freeze the
> value of now().  Reasonable options are
> 	1. at BEGIN (current behavior)
> 	2. at transaction's external creation 
> 	3. at freezing of transaction snapshot
> #1 and #2 are actually the same at the moment, but could be decoupled
> as sketched above, in which case the behavior of #2 would effectively
> become "at first command afte BEGIN".
> 
> I argued that now() should be frozen at the time of the transaction
> snapshot, and I still think that that's a defensible behavior.

Is it important exactly what value is returned as long as it's the same in 
the whole transaction? I think not.

To me it would be just as logical to fix it at the first call to now() in
the transaction. The first time you call it you get the actual time as it
is now and the next time you get the same as before since every operation
in the transaction logically happens at the same time. If you don't call
now() at all, the system time will not be fetched at all.

-- 
/Dennis

Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Neil Conway <neilc@samurai.com> — 2003-11-16T22:55:41Z

Hannu Krosing <hannu@tm.ee> writes:
> For me, the "start of transaction" is not about time, but about grouping
> a set of statements into one. So making the exact moment of "start" be
> the first statement that actually does something with data seems
> perfectly reasonable.

This might be a perfectly logical change in semantics, but what
benefit does it provide over the old way of doing things?

What does BEGIN actually do now, from a user's perspective? At
present, it "starts a transaction block", which is pretty simple. If
we adopted the proposed change, it would "change the state of the
system so that the next command is part of a new transaction". This is
naturally more complex; but more importantly, what benefit does it
ACTUALLY provide to the user?

(I can't see one, but perhaps I'm missing something...)

> Delaying the locking effects of transactions as long as possible can
> increase performance overall, not just for pathological clients that sit
> on idle open transactions.

I agree, but this is irrelevant to the semantics of now().

-Neil

start of transaction (was: Re: [PERFORM] Help with count(*))

Neil Conway <neilc@samurai.com> — 2003-11-16T23:18:02Z

Tom Lane <tgl@sss.pgh.pa.us> writes:
> That's defensible when the user issued the BEGIN himself.  When the
> BEGIN is coming from some interface library's autocommit logic, it's
> a lot less defensible.  If you consult the archives, you will find
> actual user complaints about "why is now() returning a very old time?"
> that we traced to use of interface layers that handle "commit()" by
> issuing "COMMIT; BEGIN;".

Hmmm... I agree this behavior isn't ideal, although I can see the case
for viewing this as a mistake by the application developer: they are
assuming that they know exactly when transactions begin, which is not
a feature provided by their language interface. They should be using
current_timestamp, and/or changing their language interface's
configuration.

That said, I think this is a minor irritation at best. The dual
drawbacks of breaking backward compatibility and making the BEGIN
semantics more confusing is enough to leave me satisfies with the
status quo.

If we do change this, I think Dennis' idea of making now() always
return the same value within a given transaction is interesting: that
might be a way to fix this problem without confusing the semantics of
BEGIN.

-Neil

Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-17T00:08:24Z

Neil Conway <neilc@samurai.com> writes:
> Hmmm... I agree this behavior isn't ideal, although I can see the case
> for viewing this as a mistake by the application developer: they are
> assuming that they know exactly when transactions begin, which is not
> a feature provided by their language interface.

Well, actually, it's a bug in the interface IMHO.  But as I said in the
last thread, it's a fairly widespread bug.  We've been taking the
position that the interface libraries should get fixed, and that's not
happening.  It's probably time to look at a server-side fix.

> If we do change this, I think Dennis' idea of making now() always
> return the same value within a given transaction is interesting:

You mean the time of the first now() call?  I thought that was an
interesting idea also, but it's probably not going to look so hot
when we complete the TODO item of adding access to
the start-of-current-statement time.  Having start-of-transaction be
later than start-of-statement isn't gonna fly :-(.  If we were willing
to abandon that TODO item then I'd be interested in defining now() as
Dennis suggested.

			regards, tom lane

Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-11-17T00:31:13Z

Tom Lane wrote:
> Neil Conway <neilc@samurai.com> writes:
> > Hmmm... I agree this behavior isn't ideal, although I can see the case
> > for viewing this as a mistake by the application developer: they are
> > assuming that they know exactly when transactions begin, which is not
> > a feature provided by their language interface.
> 
> Well, actually, it's a bug in the interface IMHO.  But as I said in the
> last thread, it's a fairly widespread bug.  We've been taking the
> position that the interface libraries should get fixed, and that's not
> happening.  It's probably time to look at a server-side fix.
> 
> > If we do change this, I think Dennis' idea of making now() always
> > return the same value within a given transaction is interesting:
> 
> You mean the time of the first now() call?  I thought that was an
> interesting idea also, but it's probably not going to look so hot
> when we complete the TODO item of adding access to
> the start-of-current-statement time.  Having start-of-transaction be
> later than start-of-statement isn't gonna fly :-(.  If we were willing
> to abandon that TODO item then I'd be interested in defining now() as
> Dennis suggested.

Defining now() as the first call seems pretty arbitrary to me.  I can't
think of any time-based interface that has that API.  And what if a
trigger called now() in an earlier query and you didn't even know about
it.


-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Greg Stark <gsstark@mit.edu> — 2003-11-17T06:11:53Z

Neil Conway <neilc@samurai.com> writes:

> What does BEGIN actually do now, from a user's perspective? 

I think you're thinking about this all wrong. BEGIN doesn't "do" anything.
It's not a procedural statement, it's a declaration. It declares that the
block of statements form a transaction so reads should be consistent and
failures should be handled in a particular way to preserve data integrity.

Given that declaration and the guarantees it requires of the database it's
then up to the database to figure out what constraints that imposes on what
the database can do and still meet the guarantees the BEGIN declaration
requires. The more clever the database is about minimizing those restrictions
the better as it means the database can run more efficiently.

For what it's worth, this is how Oracle handles things too. On the
command-line issuing a BEGIN following a COMMIT is just noise; you're _always_
in a transaction. A COMMIT ends the previous the transaction and implicitly
starts the next transaction. But the snapshot isn't frozen until you first
read from a table.

I'm not sure what other databases do, but I think this is why clients behave
like this. They think of BEGIN as a declaration and therefore initiating a
COMMIT;BEGIN; at the end of every request is perfectly logical, and works fine
in at least Oracle, and probably other databases.

-- 
greg

Re: start of transaction (was: Re: [PERFORM] Help with

Stephan Szabo <sszabo@megazone.bigpanda.com> — 2003-11-17T07:09:05Z

On Sun, 17 Nov 2003, Greg Stark wrote:

> Neil Conway <neilc@samurai.com> writes:
>
> > What does BEGIN actually do now, from a user's perspective?
>
> I think you're thinking about this all wrong. BEGIN doesn't "do" anything.
> It's not a procedural statement, it's a declaration. It declares that the
> block of statements form a transaction so reads should be consistent and
> failures should be handled in a particular way to preserve data integrity.
>
> Given that declaration and the guarantees it requires of the database it's
> then up to the database to figure out what constraints that imposes on what
> the database can do and still meet the guarantees the BEGIN declaration
> requires. The more clever the database is about minimizing those restrictions
> the better as it means the database can run more efficiently.
>
> For what it's worth, this is how Oracle handles things too. On the
> command-line issuing a BEGIN following a COMMIT is just noise; you're _always_
> in a transaction. A COMMIT ends the previous the transaction and implicitly
> starts the next transaction. But the snapshot isn't frozen until you first
> read from a table.

The earlier portion of the described behavior is AFAICS not complient to
SQL99 at least. COMMIT (without AND CHAIN) terminates a transaction and
does not begin a new one. The new transaction does not begin until a
transaction initiating command (for example START TRANSACTION, CREATE
TABLE, INSERT, ...) is executed. The set of things you can do that aren't
initiating is fairly small admittedly, but it's not a null set.

Re: start of transaction (was: Re: [PERFORM] Help with

Hannu Krosing <hannu@tm.ee> — 2003-11-17T10:16:06Z

Tom Lane kirjutas E, 17.11.2003 kell 02:08:
> Neil Conway <neilc@samurai.com> writes:
> > Hmmm... I agree this behavior isn't ideal, although I can see the case
> > for viewing this as a mistake by the application developer: they are
> > assuming that they know exactly when transactions begin, which is not
> > a feature provided by their language interface.
> 
> Well, actually, it's a bug in the interface IMHO.  But as I said in the
> last thread, it's a fairly widespread bug. 

I'm not sure that it is a client-side bug. For example Oracle seems to
_always_ have a transaction going, i.e. you can't be "outside" of
transaction, and you use just COMMIT to commit old _and_start_new_
transaction.

IIRC the same is true for DB2.

For these database the BEGIN TRANSACTION command is mainly used for
starting nested transactions, which we don't have.

> We've been taking the
> position that the interface libraries should get fixed, and that's not
> happening.  It's probably time to look at a server-side fix.

Maybe "fixing" the interface libraries would make them incompatible with
*DBC's for all other databases in some subtle ways ?

-----------------
Hannu

Re: start of transaction (was: Re: [PERFORM] Help with

Hannu Krosing <hannu@tm.ee> — 2003-11-17T10:19:06Z

Bruce Momjian kirjutas E, 17.11.2003 kell 02:31:

> Defining now() as the first call seems pretty arbitrary to me.  I can't
> think of any time-based interface that has that API.  And what if a
> trigger called now() in an earlier query and you didn't even know about
> it.

That would be OK. The whole point of that previous discussion was to
have now() that returns the same value over the span of the whole
transaction.

It would be even better to have now() that returns the time current
transaction is COMMITted as this is the time other backend become aware
of it ;)

-----------
Hannu

Re: start of transaction

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2003-11-17T10:43:28Z

Hannu Krosing wrote:

> Tom Lane kirjutas E, 17.11.2003 kell 02:08:
> 
>>Neil Conway <neilc@samurai.com> writes:
>>
>>>Hmmm... I agree this behavior isn't ideal, although I can see the case
>>>for viewing this as a mistake by the application developer: they are
>>>assuming that they know exactly when transactions begin, which is not
>>>a feature provided by their language interface.
>>
>>Well, actually, it's a bug in the interface IMHO.  But as I said in the
>>last thread, it's a fairly widespread bug. 
> 
> 
> I'm not sure that it is a client-side bug. For example Oracle seems to
> _always_ have a transaction going, i.e. you can't be "outside" of
> transaction, and you use just COMMIT to commit old _and_start_new_
> transaction.
> 
> IIRC the same is true for DB2.

Actually, in oracle a new transaction starts with first DDL after a commit. That 
does not include DML BTW.

And Damn.. Actually I recently fixed a "bug" where I had to force a start of 
transaction in Pro*C, immediately after commit. Otherwise a real start of 
transaction could be anywhere down the line, causing some weird concurrency 
issues. Rather than fiddling with oracle support, I would hack my source code, 
especially this is not the first oracle bug I have worked around....:-(

The fact that I couldn't control exact transaction start was such a irritation 
to put it mildly.. I sooooo missed 'exec sql begin work' in ecpg..:-)

>>We've been taking the
>>position that the interface libraries should get fixed, and that's not
>>happening.  It's probably time to look at a server-side fix.

I hope that does not compramise transaction control I have with libpq/ecpg etc.

And when we are talking about interface libraries, how many of them are within 
PG control and how many are not? With languages maintenend by postgresql group, 
it should behave correctly, right? E.g pl/perl,pl/python etc.

And for other interface libraries, what are they exactly? php? Can't we just 
send them a stinker/patch to get that damn thing right(Whatever wrong they are 
doing. I have kinda lost thread on it..:-) Was it exact time of transaction 
start v/s now()?)

  Shridhar

Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Bruce Momjian <pgman@candle.pha.pa.us> — 2003-11-18T00:52:51Z

Hannu Krosing wrote:
> Bruce Momjian kirjutas E, 17.11.2003 kell 02:31:
> 
> > Defining now() as the first call seems pretty arbitrary to me.  I can't
> > think of any time-based interface that has that API.  And what if a
> > trigger called now() in an earlier query and you didn't even know about
> > it.
> 
> That would be OK. The whole point of that previous discussion was to
> have now() that returns the same value over the span of the whole
> transaction.

I think my issue is that there isn't any predictable way for a user to
know when the now() time is recorded.  By using start of transaction, at
least we know for sure the point in time it is showing.

> It would be even better to have now() that returns the time current
> transaction is COMMITted as this is the time other backend become aware
> of it ;)

True, but implementing that would be very hard.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: start of transaction (was: Re: [PERFORM] Help with count(*))

Tom Lane <tgl@sss.pgh.pa.us> — 2003-11-18T07:34:52Z

Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Hannu Krosing wrote:
>> It would be even better to have now() that returns the time current
>> transaction is COMMITted as this is the time other backend become aware
>> of it ;)

> True, but implementing that would be very hard.

Son, that was a *joke* ...

			regards, tom lane