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

[Greg Stark]

Neil Conway <[EMAIL PROTECTED]> 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


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

[HACKERS] Release now live ...

[Marc G. Fournier]

'k, I just moved the release into the /pub/source/v7.4 directory from the
v7.4beta one ... RC2 is still in place, so that I don't break a bunch of
links ... tomorrow night, I'll remove the RC2 ...



---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

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

[Bruce Momjian]

Tom Lane wrote:
> Neil Conway <[EMAIL PROTECTED]> 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
  [EMAIL PROTECTED]   |  (610) 359-1001
  +  If your life is a hard drive, |  13 Roberts Road
  +  Christ can be your backup.|  Newtown Square, Pennsylvania 19073

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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

[Tom Lane]

Neil Conway <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

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

[Neil Conway]

Tom Lane <[EMAIL PROTECTED]> 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


---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

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

[Neil Conway]

Hannu Krosing <[EMAIL PROTECTED]> 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


---(end of broadcast)---
TIP 8: explain analyze is your friend

Re: [HACKERS] We're finally there ...

[bpalmer]

> 'k, I just tag'd REL7_4 and built the bundles ... the files are available
> under ftp://ftp.postgresql.org/pub/source/v7.4beta, and I've open'd up the
> ftp server there to 100 connections so that ppl can get in and test it ...

ftp3.us.postgresql.org is in sync as well if anyone is interested.  10M  
connection.

- Brandon


 c: 917-697-8665h: 201-435-6226
 b. palmer,  [EMAIL PROTECTED]   pgp:crimelabs.net/bpalmer.pgp5

---(end of broadcast)---
TIP 8: explain analyze is your friend

[HACKERS] We're finally there ...

[Marc G. Fournier]

'k, I just tag'd REL7_4 and built the bundles ... the files are available
under ftp://ftp.postgresql.org/pub/source/v7.4beta, and I've open'd up the
ftp server there to 100 connections so that ppl can get in and test it ...

It is 6:15pm AST here right now ... at ~9pm, I will move those files to
the v7.4 directory, baring any reports of problems ...


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[HACKERS] Bittorrent test

[David Fetter]

Kind people,

I think that the bittorrent (cf )
server is ready to go at .  Bug/failure
reports more than welcome :)

Cheers,
D
-- 
David Fetter [EMAIL PROTECTED] http://fetter.org/
phone: +1 510 893 6100cell: +1 415 235 3778

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] interesting SIGNAL 10 (BUSERR) on CVS

[Larry Rosenman]

--On Sunday, November 16, 2003 13:15:27 -0500 Tom Lane <[EMAIL PROTECTED]> 
wrote:

Larry Rosenman <[EMAIL PROTECTED]> writes:
Is it repeatable?  It's hard to see how _bt_getroot() could core
except maybe in the presence of serious data corruption in the index ...

it happened once, and postgres did a restart.  The cronjob that triggered
it (WebCalendar's sendreminders script), has run since.  So, I don't
know.
Hmm.  It might be worth running memtest86 to look for flaky RAM.
I doubt it's worth the effort to try to extract any info from a core
file with no symbols :-(
Okie.  I just figured I'd give a heads up that it happened.

--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749


pgp0.pgp
Description: PGP signature

Re: [HACKERS] interesting SIGNAL 10 (BUSERR) on CVS

[Tom Lane]

Larry Rosenman <[EMAIL PROTECTED]> writes:
>> Is it repeatable?  It's hard to see how _bt_getroot() could core
>> except maybe in the presence of serious data corruption in the index ...

> it happened once, and postgres did a restart.  The cronjob that triggered
> it (WebCalendar's sendreminders script), has run since.  So, I don't know.

Hmm.  It might be worth running memtest86 to look for flaky RAM.
I doubt it's worth the effort to try to extract any info from a core
file with no symbols :-(

regards, tom lane

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match

Re: [HACKERS] interesting SIGNAL 10 (BUSERR) on CVS

[Larry Rosenman]

--On Sunday, November 16, 2003 12:59:43 -0500 Tom Lane <[EMAIL PROTECTED]> 
wrote:

Larry Rosenman <[EMAIL PROTECTED]> writes:
I got the following:

$ debug -ic -c core* /usr/local/pgsql/bin/postgres
Warning: No debugging information in /usr/local/pgsql/bin/postgres
Core image of postgres (process p1) created
CORE FILE [_bt_getroot]
SIGNALED 10 (bus code[BUS_OBJERR] address[0xbb12c550]) in p1
0x808f057 (_bt_getroot+135:)movzwl 16(%edi),%edx
Is it repeatable?  It's hard to see how _bt_getroot() could core
except maybe in the presence of serious data corruption in the index ...
it happened once, and postgres did a restart.  The cronjob that triggered
it (WebCalendar's sendreminders script), has run since.  So, I don't know.
I just thought it was weird.

LER



--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749


pgp0.pgp
Description: PGP signature

Re: [HACKERS] interesting SIGNAL 10 (BUSERR) on CVS

[Tom Lane]

Larry Rosenman <[EMAIL PROTECTED]> writes:
> I got the following:

> $ debug -ic -c core* /usr/local/pgsql/bin/postgres
> Warning: No debugging information in /usr/local/pgsql/bin/postgres
> Core image of postgres (process p1) created
> CORE FILE [_bt_getroot]
> SIGNALED 10 (bus code[BUS_OBJERR] address[0xbb12c550]) in p1
> 0x808f057 (_bt_getroot+135:)movzwl 16(%edi),%edx

Is it repeatable?  It's hard to see how _bt_getroot() could core
except maybe in the presence of serious data corruption in the index ...

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [HACKERS] cvs head? initdb?

[Bruce Momjian]

Jan Wieck wrote:
> Robert Treat wrote:
> > 
> > but how do you test this if you cant run them both against each other to 
> > compare?  (initally running vs 7.4 does tell you something, but even now, 7.5 
> > improved cross datatype index improvments could skew the results of any 
> > comparisons)
> 
> Right. But with the current two candidates (LRU and ARC) in the field 
> only, there isn't much to compare. If someone wants to implement another 
> algorithm, I will be happy to put the required switchboard (something 
> like the jump table in smgr) into the code. Right now I see much more 
> bang for the buck in creating the background writer.

Better Journalistic Manipulator ==> BJM.  :-)

_B_ruce _J_. _M_omjian.

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

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

[HACKERS] interesting SIGNAL 10 (BUSERR) on CVS

[Larry Rosenman]

running 7.4RC2+ (from last week).

I got the following:

$ debug -ic -c core* /usr/local/pgsql/bin/postgres
Warning: No debugging information in /usr/local/pgsql/bin/postgres
Core image of postgres (process p1) created
CORE FILE [_bt_getroot]
SIGNALED 10 (bus code[BUS_OBJERR] address[0xbb12c550]) in p1
   0x808f057 (_bt_getroot+135:)movzwl 16(%edi),%edx
debug> trace
  ^
Error: Unrecognized keyword at trace
debug> stack
Stack Trace for p1, Program postgres
*[0] _bt_getroot(0x83520dc, 0x1)[0x808f057]
[1] _bt_search(presumed: 0x83520dc, 0x1, 0x838560c)[0x809350d]
[2] _bt_first(presumed: 0x83853d8, 0x1, 0x8385400) [0x809443e]
[3] btgettuple()   [0x8091fa6]
debug>
It's a one shot, and I have a core, but, I don't have the debug symbols
as UnixWare's cc doesn't allow -g and -O.
Do we care?



--
Larry Rosenman http://www.lerctr.org/~ler
Phone: +1 972-414-9812 E-Mail: [EMAIL PROTECTED]
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749


pgp0.pgp
Description: PGP signature

Re: [HACKERS] cvs head? initdb?

[Jan Wieck]

Robert Treat wrote:
but how do you test this if you cant run them both against each other to 
compare?  (initally running vs 7.4 does tell you something, but even now, 7.5 
improved cross datatype index improvments could skew the results of any 
comparisons)
Right. But with the current two candidates (LRU and ARC) in the field 
only, there isn't much to compare. If someone wants to implement another 
algorithm, I will be happy to put the required switchboard (something 
like the jump table in smgr) into the code. Right now I see much more 
bang for the buck in creating the background writer.

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

Re: [HACKERS] cvs head? initdb?

[Jan Wieck]

Bruce Momjian wrote:

I figured it should begin with debug_ or log_, maybe:

	debug_shared_buffers = 10  # seconds
If it's just that and since nobody else seemed to care ... changed.

Jan

--
#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
  http://www.postgresql.org/docs/faqs/FAQ.html

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

[Dennis Bjorklund]

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


---(end of broadcast)---
TIP 8: explain analyze is your friend

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

[Tom Lane]

Hannu Krosing <[EMAIL PROTECTED]> 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

---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])

Re: [HACKERS] [PATCHES] ALTER TABLE modifications

[Rod Taylor]

-- moving to -hackers

> Do you have special cases for type changes which don't need data
> transforms. 

> I mean things like changing VARCHAR(10) to VARCHAR(20), dropping the NOT
> NULL constraint or changing CHECK A < 3 to CHECK A < 4. 

There are basically 3 types of change.

The first is simple, a removal. These do not kick off scans of any form.
You simply do not add any work to the post-processing queue.

The second requests a table scan. This is intended for check constraint,
etc. additions.

The third is a rewrite of the records which is done with via a file
swap. All type changes, column + default additions, etc. are done this
way. It will simultaneously take care of the check constraint, etc.
requests as well.

> All these could be done with no data migration or extra checking.

I'll leave it for someone else to add in the queue bypasses for simple
items. I've otherwise attempted to maintain the current processes (new
check constraint always scans, etc.).


Queued foreign keys are going to need to work a little differently since
they are cross table checks. I'm currently deferring them until
everything else has been accomplished (affects alter type only at the
moment).



---(end of broadcast)---
TIP 8: explain analyze is your friend

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

[Hannu Krosing]

Redirected to -hackers

Neil Conway kirjutas L, 15.11.2003 kell 22:20:
> Tom Lane <[EMAIL PROTECTED]> 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



---(end of broadcast)---
TIP 6: Have you searched our list archives?

   http://archives.postgresql.org

[HACKERS] Defaut database encoding

[Jean-Michel POURE]

Dear friends,

Recently, several pgAdmin3 users complained about missing accentuated 
characters. The problems mostly came from the ASCII database encoding, which 
provides arbitrary storage of accentuated characters.

During installation of a Debian station, I noticed that the Debian 
initialisation script asked the user to define a default database encoding.

This reduces the risk for choosing an ASCIII database. Are there plans to make 
such an initialisation script default in PostgreSQL? I was not able to read 
discussions about the InitDB script. Maybe such a solution is on the way...

Maybe the script could check the locale and propose an appropriate database 
encoding.

Best regards,
Jean-Michel



---(end of broadcast)---
TIP 8: explain analyze is your friend