First experiences with Postgresql 7.0

Rolf Grossmann <grossman@securitas.net> writes:

I ran into a bit of a problem: I couldn't specify NOT NULL PRIMARY KEY
anymore.

For the moment try the other order: PRIMARY KEY NOT NULL. This is a
known parser deficiency that we chose to leave unfixed for the start of
beta, but it should be fixed for 7.0 ...

regards, tom lane

From bouncefilter Wed Feb 23 12:03:31 2000
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA68570
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 12:03:05 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from mcadnote1 (ppm218.noc.fukui.nsk.ne.jp [210.161.188.93])
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id CAA01406; Thu, 24 Feb 2000 02:02:07 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Tom Lane" <tgl@sss.pgh.pa.us>,
"Zeugswetter Andreas SB" <ZeugswetterA@wien.spardat.at>
Cc: "'hackers'" <pgsql-hackers@postgreSQL.org>
Subject: RE: AW: [HACKERS] TRANSACTIONS
Date: Thu, 24 Feb 2000 02:03:29 +0900
Message-ID: <NDBBIJLOILGIKBGDINDFMEPFCCAA.Inoue@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-2022-jp"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
In-Reply-To: <4315.951321286@sss.pgh.pa.us>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300

-----Original Message-----
From: owner-pgsql-hackers@postgreSQL.org
[mailto:owner-pgsql-hackers@postgreSQL.org]On Behalf Of Tom Lane

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

You are absolutely right. The whole point is that either a) everything
commits or b) nothing commits.
Having some kinds of exceptions allow a partial commit while other
exceptions rollback the transaction seems like a very error-prone
programming environment to me.

In this sense a commit is not partial. The commit should commit
all statements that were not in error.

That interpretation eliminates an absolutely essential capability
(all-or-none behavior) in favor of what strikes me as a very minor
programming shortcut.

All other DB's behave in this way.

I find this hard to believe,

At least Oracle does so. AFAIK,transaction cancel
could be avoided except FATAL error cases using
embedded SQL. Dupicate index error is the typical
one.

Vadim has already planned to implement savepoint.
Of cource implicit per statement rollback is one of
the case. I have thought it had already been a
consensus.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Wed Feb 23 12:07:31 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA69299
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 12:07:19 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id JAA07562;
Wed, 23 Feb 2000 09:06:45 -0800 (PST)
Message-Id: <3.0.1.32.20000223090431.010db970@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Wed, 23 Feb 2000 09:04:31 -0800
To: Ed Loehr <eloehr@austin.rr.com>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] interesting observatation regarding views and
V7.0
Cc: pgsql-hackers@postgreSQL.org
In-Reply-To: <38B41339.1B1DF9A6@austin.rr.com>
References: <3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 11:04 AM 2/23/00 -0600, Ed Loehr wrote:

Don Baccus wrote:

The creation of column aliases for tables referenced by views causes
the rule created on the underlying virtual table to be in some cases
considerably longer than the corresponding rule in V6.5.

...In my case, I recompiled PG with a blocksize of 16KB...

...This "fixed" my problem with views, too.

Thanks for this info, Don. Would you mind posting your patch, simple
as it may be?

That was it, I just recompiled PG with a blocksize of 16KB, i.e.
edited src/include/config.h.in's BLCKSZ definition, ran configure,
and did a gmake all/gmake install.

As I mentioned, I had other reasons for wanting to run with a 16KB
blocksize while waiting for TOASTed large text (and other) types,
so it's no biggie for me.

Others might find this change a lot more annoying.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Wed Feb 23 12:03:31 2000
Received: from mail.austin.rr.com (sm1.texas.rr.com [24.93.35.54])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA68587
for <pgsql-hackers@postgresql.org>;
Wed, 23 Feb 2000 12:03:14 -0500 (EST)
(envelope-from eloehr@austin.rr.com)
Received: from austin.rr.com ([24.27.34.146]) by mail.austin.rr.com with
Microsoft SMTPSVC(5.5.1877.197.19); Wed, 23 Feb 2000 11:03:43 -0600
Sender: ed
Message-ID: <38B41339.1B1DF9A6@austin.rr.com>
Date: Wed, 23 Feb 2000 11:04:57 -0600
From: Ed Loehr <eloehr@austin.rr.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Don Baccus <dhogaza@pacifier.com>
CC: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] interesting observatation regarding views and V7.0
References: <3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Don Baccus wrote:

The creation of column aliases for tables referenced by views causes
the rule created on the underlying virtual table to be in some cases
considerably longer than the corresponding rule in V6.5.

...In my case, I recompiled PG with a blocksize of 16KB...

...This "fixed" my problem with views, too.

Thanks for this info, Don. Would you mind posting your patch, simple
as it may be?

Cheers,
Ed Loehr

From bouncefilter Wed Feb 23 12:10:31 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA69659
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 12:09:45 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
MAA03512;
Wed, 23 Feb 2000 12:08:14 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002231708.MAA03512@candle.pha.pa.us>
Subject: Re: [HACKERS] Beta for 4:30AST ... ?
In-Reply-To: <200002231651.LAA02886@candle.pha.pa.us> from Bruce Momjian at
"Feb 23, 2000 11:51:56 am"
To: Bruce Momjian <pgman@candle.pha.pa.us>
Date: Wed, 23 Feb 2000 12:08:14 -0500 (EST)
CC: Tom Lane <tgl@sss.pgh.pa.us>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I am still going through the CVS logs, and I can already say that this
release will have more updated items than any previous release. We can
blame Tom Lane for most of this. :-)

Let me cast blame on Peter Eisentraut too. ;-)

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Wed Feb 23 12:13:40 2000
Received: from ara.zf.jcu.cz (zakkr@ara.zf.jcu.cz [160.217.161.4])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA70142
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 12:12:52 -0500 (EST) (envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
by ara.zf.jcu.cz (8.9.3/8.9.3/Debian/GNU) with SMTP id SAA29125;
Wed, 23 Feb 2000 18:11:22 +0100
Date: Wed, 23 Feb 2000 18:11:22 +0100 (CET)
From: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Hiroshi Inoue <Inoue@tpf.co.jp>,
pgsql-hackers <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Cache query (PREPARE/EXECUTE)
In-Reply-To: <4650.951324791@sss.pgh.pa.us>
Message-ID: <Pine.LNX.3.96.1000223180302.15013F-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 23 Feb 2000, Tom Lane wrote:

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

I think so. The problem is that Node struct couldn't be freed safely
due to the lack of reference count in its definition. As far as I see
plans could be destroyed only when the memory context in which
they are placed are destroyed.

This is overly conservative. It should be safe to destroy a plan tree
via freeObject() if it was created via copyObject() --- and that is
certainly how the plan would get into a permanent memory context.

Yes, SPI and my PREPARE use copyObject() for saving to TopMemoryContext.

Well, I believe you Tom that freeObject() is correct and I start
implement PlanCacheMemoryContext's routines for PREPARE (and
SPI's saveplan ?).

Karel Z.

From bouncefilter Wed Feb 23 12:34:32 2000
Received: from sd.tpf.co.jp (sd.tpf.co.jp [210.161.239.34])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA73411
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 12:33:40 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from mcadnote1 (ppm225.noc.fukui.nsk.ne.jp [210.161.188.100])
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id CAA01423; Thu, 24 Feb 2000 02:32:42 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "Karel Zak - Zakkr" <zakkr@zf.jcu.cz>,
"pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Cache query (PREPARE/EXECUTE)
Date: Thu, 24 Feb 2000 02:34:04 +0900
Message-ID: <NDBBIJLOILGIKBGDINDFEEPGCCAA.Inoue@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-2022-jp"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook IMO, Build 9.0.2416 (9.0.2910.0)
In-Reply-To: <4650.951324791@sss.pgh.pa.us>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

I think so. The problem is that Node struct couldn't be freed safely
due to the lack of reference count in its definition. As far as I see
plans could be destroyed only when the memory context in which
they are placed are destroyed.

This is overly conservative. It should be safe to destroy a plan tree
via freeObject() if it was created via copyObject() --- and that is
certainly how the plan would get into a permanent memory context.

I proposed the implementation of copyObject() which keeps the
references among objects once before. It seems unnatural to me
that such kind of implementation would never be allowed by this
restriction.
Why is memory context per plan bad ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Wed Feb 23 13:34:52 2000
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de
[53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id NAA82143
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 13:34:24 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12NgQS-0003kgC; Wed, 23 Feb 100 19:22 MET
Message-Id: <m12NgQS-0003kgC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Cache query (PREPARE/EXECUTE)
In-Reply-To: <4650.951324791@sss.pgh.pa.us> from Tom Lane at "Feb 23,
2000 11:53:11 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 23 Feb 2000 19:22:16 +0100 (CET)
CC: Hiroshi Inoue <Inoue@tpf.co.jp>, Karel Zak - Zakkr <zakkr@zf.jcu.cz>,
pgsql-hackers <pgsql-hackers@postgreSQL.org>
Reply-To: Jan Wieck <wieck@debis.com>
X-Mailer: ELM [version 2.4ME+ PL68 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

"Hiroshi Inoue" <Inoue@tpf.co.jp> writes:

I think so. The problem is that Node struct couldn't be freed safely
due to the lack of reference count in its definition. As far as I see
plans could be destroyed only when the memory context in which
they are placed are destroyed.

This is overly conservative. It should be safe to destroy a plan tree
via freeObject() if it was created via copyObject() --- and that is
certainly how the plan would get into a permanent memory context.

Currently, rule definitions are leaked in CacheContext at relcache
flushes. I plan to start freeing them via freeObject at the beginning
of the 7.1 development cycle --- I didn't want to risk it during the
runup to 7.0, but I believe it will work fine.

I don't see any reason, why each saved plan or rule
definition shouldn't go into it's own, private memory
context. Then, a simple destruction of the entire context
will surely free all it's memory, and I think it will also be
faster since the en-block allocation, done for many small
objects, doesn't need to free all them separately - it throws
away the entire blocks. No need to traverse the node tree,
nor any problems with multiple object references inside the
tree.

Since plans are (ought to be) saved via SPI_saveplan(plan),
there is already a central point where it can be done for
plans. And a corresponding SPI_freeplan(savedplan) should be
easy to create, since the context can be held in the SPI plan
structure itself.

Needs only some general naming convention for these memory
contexts. But something like a

MemoryContext CreateObjectMemoryContext();

that guarantees uniqueness in the context name and no
conflicts by using some appropriate prefix in them should do
it.

The overhead, payed for separate contexts is IMHO negligible.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

From bouncefilter Wed Feb 23 13:50:33 2000
Received: from ara.zf.jcu.cz (zakkr@ara.zf.jcu.cz [160.217.161.4])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA83969
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 13:50:20 -0500 (EST) (envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
by ara.zf.jcu.cz (8.9.3/8.9.3/Debian/GNU) with SMTP id TAA00811;
Wed, 23 Feb 2000 19:48:25 +0100
Date: Wed, 23 Feb 2000 19:48:25 +0100 (CET)
From: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
To: Hiroshi Inoue <Inoue@tpf.co.jp>
cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Cache query (PREPARE/EXECUTE)
In-Reply-To: <NDBBIJLOILGIKBGDINDFEEPGCCAA.Inoue@tpf.co.jp>
Message-ID: <Pine.LNX.3.96.1000223192816.15013G-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 24 Feb 2000, Hiroshi Inoue wrote:

This is overly conservative. It should be safe to destroy a plan tree
via freeObject() if it was created via copyObject() --- and that is
certainly how the plan would get into a permanent memory context.

I proposed the implementation of copyObject() which keeps the
references among objects once before. It seems unnatural to me
that such kind of implementation would never be allowed by this
restriction.

Why is memory context per plan bad ?

One context is more simple.

We talking about a *cache*. If exist interface for this cache and
all operations are with copy/freeObject it not has restriction.

For how action it will restriction?

The PlanCacheMemoryContext will store space only, it isn't space for
any action.

Karel Z.

From bouncefilter Wed Feb 23 15:23:34 2000
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de
[53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id PAA94798
for <pgsql-hackers@postgresql.org>;
Wed, 23 Feb 2000 15:23:21 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgresql.org
id m12Ni7o-0003kgC; Wed, 23 Feb 100 21:11 MET
Message-Id: <m12Ni7o-0003kgC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Cache query (PREPARE/EXECUTE)
In-Reply-To: <Pine.LNX.3.96.1000223192816.15013G-100000@ara.zf.jcu.cz> from
Karel Zak - Zakkr at "Feb 23, 2000 07:48:25 pm"
To: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
Date: Wed, 23 Feb 2000 21:11:08 +0100 (CET)
CC: Hiroshi Inoue <Inoue@tpf.co.jp>, Tom Lane <tgl@sss.pgh.pa.us>,
pgsql-hackers <pgsql-hackers@postgresql.org>
Reply-To: Jan Wieck <wieck@debis.com>
X-Mailer: ELM [version 2.4ME+ PL68 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Karel wrote:

Why is memory context per plan bad ?

One context is more simple.

I don't see much complexity difference between one context
per plan vs. one context for all. At least if we do it
transparently inside of SPI_saveplan() and SPI_freeplan().

We talking about a *cache*. If exist interface for this cache and
all operations are with copy/freeObject it not has restriction.

For how action it will restriction?

No restrictions I can see.

But I think one context per plan is still better, since first
there is no leakage/multiref problem. Second, there is a
performance difference between explicitly pfree()'ing
hundreds of small allocations (in freeObject() traverse), and
just destroying a context. The changes I made to the
MemoryContextAlloc stuff for v6.5 (IIRC), using bigger blocks
incl. padding/reuse for small allocations, caused a speedup
of 5+% for the entire regression test. This was only because
it uses lesser real calls to malloc()/free() and the context
destruction does not need to process a huge list of all,
however small allocations anymore. It simply throws away all
blocks now.

This time, we talk about a more complex, recursive
freeObject(), switch()'ing for every node type into separate,
per object type specific functions, pfree()'ing all the
little chunks. So there is at least a difference in
first/second-level RAM cache rows required. And if that can
simply be avoided by using one context per plan, I vote for
1by1.

Then again, copyObject/freeObject must be fixed WRT
leakage/multiref anyway.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

From bouncefilter Wed Feb 23 15:13:38 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA93868
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 15:13:18 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id PAA06498
for pgsql-hackers@postgreSQL.org; Wed, 23 Feb 2000 15:12:54 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002232012.PAA06498@candle.pha.pa.us>
Subject: Changes in 7.0
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: Wed, 23 Feb 2000 15:12:54 -0500 (EST)
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=ELM951336774-1990-0_
Content-Transfer-Encoding: 7bit

--ELM951336774-1990-0_
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Here is my list of 7.0 changes. Please let me know of any changes I
should make to it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

--ELM951336774-1990-0_
Content-Type: text/plain
Content-Disposition: inline; filename="/root/cvslog"
Content-Transfer-Encoding: 7bit

This release shows the continued growth of PostgreSQL. There are more
updated items in 7.0 than in any previous release. Don't be concerned
this is a dot-zero release. PostgreSQL does its best to put
out only solid releases, and this one is no exception.

Major changes in this release:

Foreign Keys: Foreign keys are now implemented, with the exception of
PARTIAL MATCH foreign keys. Many users have been asking for this
feature, and are pleased to finally offer it.

Optimizer Overhaul: Continuing on work started a year ago, the
optimizer has been overhauled in many significant ways, allowing better
query execution processing with faster performance and less memory
usage.

Updated psql: psql, our interactive terminal monitor, has been updated,
with a variety of new features. See the psql manual page for the details.

Upcoming Features: In 7.1, we plan to have outer joins, storage for very long
rows, and a write-ahead logging system.

Bug Fixes
---------
Prevent function calls with more than maximum number of arguments (Tom)
Many fixes for CASE (Tom)
Many array fixes (Tom)
Fix SELECT coalesce(f1,0) FROM int4_tbl GROUP BY f1 (Tom)
Fix SELECT sentence.words[0] FROM sentence GROUP BY sentence.words[0] (Tom)
Allow utility statements in plpgsql (Tom)
Fix GROUP BY scan bug (Tom)
Optimize btree searching for cases where many equal keys exist (Tom)
Allow bare column names to be subscripted as arrays (Tom)
Improvements in SQL grammar processing(Tom)
Fix for views involved in INSERT ... SELECT ... (Tom)
Fix for SELECT a/2, a/2 FROM test_missing_target GROUP BY a/2 (Tom)
Fix for subselects in INSERT ... SELECT (Tom)
Prevent INSERT ... SELECT ... ORDER BY (Tom)
Improve type casting of int and float constants (Tom)
Cleanups for int8 inputs, range checking, and type conversion (Tom)
Fix for SELECT timespan('21:11:26'::time) (Tom)
Fixes for relations greater than 2GB, including vacuum
Improve communication of system table changes to other running backends (Tom)
Improve communication of user table modifications to other running backends (Tom)
Fix handling of temp tables in complex situations (Bruce, Tom)
Disallow DROP TABLE/DROP INDEX inside a transaction block
Prevent exponential space consumption with many AND's and OR's (Tom)
Collect attribute selectivity values for system columns (Tom)
Allow table locking when tables opened, improving concurrent reliability (Tom)
Fix for netmask('x.x.x.x/0') is 255.255.255.255 instead of 0.0.0.0
(Oleg Sharoiko)
Properly quote sequence names in pg_dump (Ross J. Reedstrom)
Prevent DESTROY DATABASE while others accessing
Prevent any rows from being returned by GROUP BY if no rows processed (Tom)
Reduce memory usage of aggregates (Tom)
Fix SELECT COUNT(1) FROM table WHERE ...' if no rows matching WHERE (Tom)
Fix pg_upgrade so it works for MVCC(Tom)
Add nbtree operator class for NUMERIC(Jan)
Fix for SELECT ... WHERE x IN (SELECT ... HAVING SUM(x) > 1) (Tom)
Make TABLE optional keyword in LOCK TABLE (Bruce)
Fix for "f1 datetime default 'now'" (Tom)
Allow comment-only lines, and ;;; lines too. (Tom)
Improve recovery after failed disk writes, disk full (Hiroshi)
Fix cases where table is mentioned in FROM but not joined (Tom)
Allow HAVING clause without aggregate functions (Tom)
Fix for "--" comment and no trailing newline, as seen in Perl
Improve pg_dump failure error reports (Bruce)
Perl fix for BLOBs containing NUL characters (Douglas Thomson)
Allow sorts and hashes to exceed 2GB file sizes (Tom)
ODBC fix for for large objects (free)
Fix for pg_dump dumping of inherited rules (Tom)
Fix for NULL handling comparisons (Tom)
Fix inconsistent state caused by failed CREATE/DROP commands
Fix for dbname with dash
Fix problems with CURRENT_DATE used in DEFAULT (Tom)
Prevent DROP INDEX from interfering with other backends (Tom)
Fix file descriptor leak in verify_password()
Fix for "Unable to identify an operator =$" problem
Fix ODBC so no segfault if CommLog and Debug enabled (Dirk Niggemann)
Fix for recursive exit call (Massimo)
Fix indexing of cidr
Fix for extra-long timezones (Jeroen van Vianen)
Make pg_dump preserve primary key information (Peter E)
Prevent databases with single quotes (Peter E)
Prevent DROP DATABASE inside transaction (Peter E)
ecpg memory leak fixes (Stephen Birch)
Fix for Ethernet MAC addresses (macaddr type) comparisons
Fix for SELECT null::text, SELECT int4fac(null) and SELECT 2 + (null) (Tom)
Fix for LIKE optimization to use indexes with multi-byte encodings (Tom)
Y2K timestamp fix (Massimo)
Fix for date/time types when overflows happened in computations (Tom)
Fix for VACUUM 'HEAP_MOVED_IN was not expected' errors (Tom)
Fix for views with tables/columns containing spaces (Tom)
Allow array on int8 (Peter E)
Prevent permissions on indexes (Peter E)
Fix for rounding/overflow of NUMERIC type, like NUMERIC(4,4) (Tom)
Fix for spinlock stuck problem when error is generated (Hiroshi)
Allow NUMERIC arrays
Fix ipcclean on Linux
Fix handling of NULL constraint conditions (Tom)
Fix bugs in NUMERIC ceil() and floor() functions (Tom)
Make char_length()/octet_length including trailing blanks (Tom)
Made abstime/reltime use int4 instead of time_t (Peter E)
Fix memory leak in odbc driver (Nick Gorham)
Fix r-tree index optimizer selectivity (Thomas)

Enhancements
------------
New CLI interface include file sqlcli.h, based on SQL3/SQL98
Remove all limits on query length, row length limit still exists (Tom)
Improve optimizer selectivity computations and functions (Tom)
Enable fast LIKE index processing only if index present (Tom)
Revise parse_coerce() to handle coercion of int and float constants (Tom)
Re-use free space on index pages with duplicates (Tom)
Improve hash join processing (Tom)
Prevent descending sort if result is already sorted(Hiroshi)
Allow commuting of index scan query qualifications (Tom)
Prefer index scans in cases where ORDER BY/GROUP BY is required (Tom)
Allocate large memory requests in fix-sized chunks for performance (Tom)
Fix vacuum's performance by reducing memory allocation requests (Tom)
Update jdbc protocol to 2.0 (Jens Glaser jens@jens.de)
Add TRUNCATE command to quickly truncate relation (Mike Mascari)
Implement constant-expression simplification (Bernard Frankpitt, Tom)
Fix to give super user and createdb user proper update catalog rights (Peter E)
Allow more than first column to be used to determine start of index scan
(Hiroshi)
Allow ecpg bool variables to have NULL values (Christof)
Issue ecpg error if NULL value is returned to variable with no NULL
indicator (Christof)
Allow ^C to cancel COPY command (Massimo)
Add SET FSYNC and SHOW PG_OPTIONS commands(Massimo)
Improve CREATE FUNCTION to allow type conversion specification
(Bernie Frankpitt)
Add CmdTuples() to libpq++(Vince)
New CREATE CONSTRAINT TRIGGER and SET CONSTRAINTS commands(Jan)
Allow CREATE FUNCTION WITH clause to be used for all language types
configure --enable-debug adds -g (Peter E)
configure --disable-debug removes -g (Peter E)
Allow more complex default expressions (Tom)
First real FOREIGN KEY constraint trigger functionality (Jan)
Add FOREIGN KEY ... REFERENCES ... MATCH FULL (Jan)
Add FOREIGN KEY ... MATCH FULL ... ON DELETE CASCADE (Jan)
Allow WHERE restriction on ctid (physical heap location) (Hiroshi)
Move pginterface from contrib to interface directory, rename to pgeasy (Bruce)
Add DEC and SESSION_USER as reserved words
Prevent quadruple use of disk space when doing internal sorting (Tom)
Require SELECT DISTINCT target list to have all ORDER BY columns (Tom)
Add Oracle's COMMENT ON command (Mike Mascari <mascarim@yahoo.
libpq's PQsetNoticeProcessor function now returns previous hook(Peter E)
Prevent PQsetNoticeProcessor from being set to NULL (Peter E)
Make USING in COPY optional (Bruce)
Faster sorting by calling fewer functions (Tom)
Create system indexes to match all system caches(Bruce, Hiroshi)
Make system caches use system indexes(Bruce)
Make all system indexes unique(Bruce)
Allow subselects in the target list (Tom)
Allow subselects on the left side of comparison operators (Tom)
New parallel regression test (Jan)
Change backend-side COPY to write files with permissions 644 not 666 (Tom)
Force permissions on PGDATA directory to be secure, even if it exists (Tom)
Added psql LastOid variable to return last inserted oid (Peter E)
Improve pg_statistics management for VACUUM speed improvement (Tom)
Allow concurrent vacuum and remove pg_vlock vacuum lock file (Tom)
Add permissions check so only Postgres superuser or table owner can
vacuum (Peter E)
New C-routines to implement a BIT and BIT VARYING type in /contrib
(Adriaan Joubert)
New Oracle compatible DateTime routines TO_CHAR(), TO_DATE() and
FROM_CHAR() (Karel)
New libpq functions to allow asynchronous connections: PQconnectStart(),
PQconnectPoll(), PQresetStart(), PQresetPoll(), PQsetenvStart(),
PQsetenvPoll(), PQsetenvAbort (Ewan Mellor)
New libpq PQsetenv() function (Ewan Mellor)
create/alter user extension (Peter E)
New postmaster.pid and postmaster.opts under $PGDATA (Tatsuo)
New scripts for create/drop user/db (Peter E)
Major psql overhaul(Peter E)
Add const to libpq interface(Peter E)
New libpq function PQoidValue (Peter E)
Show specific non-aggregate causing problem with GROUP BY (Tom)
Force changes to pg_shadow recreate pg_pwd file (Peter E)
Add aggregate(DISTINCT ...) (Tom)
Allow flag to control COPY input/output of NULLs (Peter E)
Make postgres user have a password by default (Peter E)
Add CREATE/ALTER/DROP GROUP (Peter E)
All administration scripts now support --long options (Peter E, Karel)
Vacuumdb script now supports --alldb option (Peter E)
ecpg new portable FETCH syntax
Add ecpg EXEC SQL IFDEF, EXEC SQL IFNDEF, EXEC SQL ELSE, EXEC SQL ELIF
and EXEC SQL ENDIF directives
Add pg_ctl script to control backend startup (Tatsuo)
Add postmaster.opts.default file to store startup flags (Tatsuo)
Allow --with-mb=SQL_ASCII
Increase maximum number of index keys to 16 (Bruce)
Increase maximum number of function arguments to 16 (Bruce)
Allow user configuration of maximum number of index keys and arguments
(Bruce)
Flush backend cache less frequently (Tom)
Allow unprivileged users change their own passwords (Peter E)
With password authentication enabled, new users without passwords can't
connect (Peter E)
Disallow dropping a user who owns a database (Peter E)
Add initdb --enable-multibyte option (Peter E)
Add option for initdb to prompts for superuser password (Peter E)
COPY now reuses previous memory allocation, improving performance (Tom)
Allow complex type casts like col::numeric(9,2) and col::int2::float8 (Tom)
Updated user interfaces on initdb, initlocation, pg_dump, ipcclean
(Peter E)
NUMERIC now accepts scientific notation (Tom)
NUMERIC to int4 rounds (Tom)
Convert float4/8 to NUMERIC properly (Tom)
New pg_char_to_encoding() and pg_encoding_to_char() functions
Libpq non-blocking mode (Alfred Perlstein)
Improve conversion of types in casts that don't specify a length
New plperl internal programming language (Mark Hollomon)
Allow COPY IN to read file that do not end with a newline (Tom)
Improve optimization cost estimation (Tom)
Indicate when long identifiers are truncated (Tom)
Improve optimizer estimate of range queries x > lowbound AND x < highbound (Tom)
Allow aggregates to use type equivalency (Peter E)
Add Oracle's to_char(), to_date(), to_datetime(), to_timestamp(), to_number()
conversion functions (Karel Zak <zakkr@zf.jcu.cz>)
Add SELECT DISTINCT ON (expr [, expr ...]) targetlist ... (Tom)
Check to be sure ORDER BY is compatible with the DISTINCT operation (Tom)
Use DNF instead of CNF where appropriate (Tom, Taral)
Add NUMERIC and int8 types to ODBC
Improve EXPLAIN results for Append, Group, Agg, Unique (Tom)
Added ALTER TABLE ... ADD CONSTRAINT (Stephan Szabo)
Further cleanup for OR-of-AND WHERE-clauses (Tom)
Make use of index in OR clauses (x = 1 AND y = 2) OR (x = 2 AND y = 4) (Tom)
Allow SELECT .. FOR UPDATE in PL/pgSQL
Enable backward sequential scan even after reaching EOF
Add btree indexing of boolean values (Don Baccus)
Print current line number when COPY FROM fails (Massimo)
Recognize special case of POSIX time zone: "GMT+8" and "GMT-8" (Thomas)
Add "DEC" as synonym for "DECIMAL (Thomas)
Add "SESSION_USER" as SQL92 keyword, same as CURRENT_USER (Thomas)
Implement column aliases (aka correlation names) and more join syntax
(Thomas)
Allow queries like SELECT a FROM t1 tx (a) (Thomas)
Allow queries like SELECT * FROM t1 NATURAL JOIN t2 (Thomas)
Smarter optimizer computations for random index page access (Tom)
New SET variable to control optimizer costs (Tom)
Optimizer queries based on LIMIT, OFFSET, and EXISTS qualifications (Tom)
Reduce optimizer internal housekeeping of join paths for speedup (Tom)
Make "INTERVAL" reserved word allowed as a column identifier (Thomas)
Allow type conversion with NUMERIC (Thomas)
Make ISO date style (2000-02-16 09:33) the default (Thomas)
Implement REINDEX command
Accept ALL in aggregate function SUM(ALL col) (Tom)
Prevent GROUP BY from using column aliases (Tom)
New psql \encoding option (Tatsuo)
Allow PQrequestCancel() to terminate when in waiting-for-lock state (Jan)
Allow negation of a negative number in all cases

Source Tree Changes
-------------------
Fix for linux PPC compile
New generic expression-tree-walker subroutine (Tom)
Change form() to varargform() to prevent portability problems.
Improved range checking for large integers on Alpha's
Clean up #include in /include directory (Bruce)
Add scripts for checking includes (Bruce)
Remove un-needed #include's from *.c files (Bruce)
Change #include's to use <> and "" as appropriate (Bruce)
Enable WIN32 compilation of libpq
Alpha spinlock fix from Uncle George <gatgul@voicenet.com>
Overhaul of optimizer data structures (Tom)
Fix to cygipc library (Yutaka Tanida)
Allow pgsql to work on newer Cygwin snapshots(Dan)
New catalog version number (Tom)
Add Linux ARM.
Rename heap_replace to heap_update
Update for QNX (Kardos, Dr. Andrea)
New platform-specific regression handling (Tom)
Rename oid8 -> oidvector and int28 -> int2vector (Bruce)
Included all yacc and lex files into the distribution (Peter E.)
Remove lextest, no longer needed (Peter E)
Fix for libpq and psql on Win32 (Magnus)
Internally change datetime and timespan into timestamp and interval (Thomas)

--ELM951336774-1990-0_
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

--ELM951336774-1990-0_--

From bouncefilter Wed Feb 23 17:40:38 2000
Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu
[128.42.12.154])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA05282
for <pgsql-hackers@postgresql.org>;
Wed, 23 Feb 2000 17:40:18 -0500 (EST)
(envelope-from reedstrm@wallace.ece.rice.edu)
Received: by wallace.ece.rice.edu via sendmail from stdin
id <m12NkS6-000LELC@wallace.ece.rice.edu> (Debian Smail3.2.0.102)
for pgsql-hackers@postgresql.org; Wed, 23 Feb 2000 16:40:14 -0600 (CST)
Date: Wed, 23 Feb 2000 16:40:14 -0600
From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: Feature Request
Message-ID: <20000223164014.E6190@rice.edu>
References: <38B3254C.D0780368@jumpline.com>
<200002230123.UAA09531@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <200002230123.UAA09531@candle.pha.pa.us>;
from pgman@candle.pha.pa.us on Tue, Feb 22, 2000 at 08:23:17PM
-0500

On Tue, Feb 22, 2000 at 08:23:17PM -0500, Bruce Momjian wrote:

Dear Mr Momjian

Just a quick suggestion for an added feature: "DROP COLUMN columnname
FROM table..." and "ALTER COLUMN columname FROM table..." queries would
spare my hair when I make mistakes in table creation.

We have the DROP, but will not appear in 7.0.

One last little note on the whole DROP COLUMN discussion:

A snip from Phil Greenspun's photo.net site:

"Adding a column to a relational database table seldom breaks
queries. Until Oracle 8.1.5, you weren't able to drop a column."

My guess would be that Oracle found out internally what we've been
discussing here: doing DROP COLUMN right is _hard_.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

From bouncefilter Wed Feb 23 17:41:36 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA05335
for <pgsql-hackers@postgresql.org>;
Wed, 23 Feb 2000 17:41:25 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id RAA05827;
Wed, 23 Feb 2000 17:40:47 -0500 (EST)
To: Jan Wieck <wieck@debis.com>
cc: Karel Zak - Zakkr <zakkr@zf.jcu.cz>, Hiroshi Inoue <Inoue@tpf.co.jp>,
pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Cache query (PREPARE/EXECUTE)
In-reply-to: <m12Ni7o-0003kgC@orion.SAPserv.Hamburg.dsh.de>
References: <m12Ni7o-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Comments: In-reply-to wieck@debis.com (Jan Wieck)
message dated "Wed, 23 Feb 2000 21:11:08 +0100"
Date: Wed, 23 Feb 2000 17:40:47 -0500
Message-ID: <5824.951345647@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

wieck@debis.com (Jan Wieck) writes:

But I think one context per plan is still better, since first
there is no leakage/multiref problem. Second, there is a
performance difference between explicitly pfree()'ing
hundreds of small allocations (in freeObject() traverse), and
just destroying a context.

Agreed, though one would hope that performance of cache flushes
is not a major consideration ;-).

What I find attractive about going in this direction is the idea
that we could get rid of freeObject() entirely, and eliminate that
part of the work involved in changing node definitions.

Then again, copyObject/freeObject must be fixed WRT
leakage/multiref anyway.

Not if we decide to get rid of freeObject, instead.

I think that a little work would have to be done to support efficient
use of large numbers of contexts, but it's certainly doable. This
path seems more attractive than trying to make the world safe for
freeObject of arbitrary node trees.

regards, tom lane

From bouncefilter Wed Feb 23 17:55:35 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA06477
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 17:54:43 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id RAA05886;
Wed, 23 Feb 2000 17:54:36 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] interesting observatation regarding views and V7.0
In-reply-to: <3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
References: <3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Wed, 23 Feb 2000 08:17:25 -0800"
Date: Wed, 23 Feb 2000 17:54:36 -0500
Message-ID: <5883.951346476@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

The creation of column aliases for tables referenced by views causes
the rule created on the underlying virtual table to be in some cases
considerably longer than the corresponding rule in V6.5.
In practice, this means that several of the views used in the web
toolkit I'm porting no longer can be created.

Yes, this is exactly the concern I raised last week. Thomas didn't
seem to be very worried about the issue, but when he gets back from
his vacation we can lean on him to fix it.

Something else we might consider as a stopgap is to resurrect the
"compressed text" datatype that Jan wrote, and then removed in
anticipation of having TOAST. Jan was concerned about creating
future compatibility problems by having a datatype with only a
one-release-cycle expected lifetime ... but I think it might be
OK to use it just internally for rules.

regards, tom lane

From bouncefilter Wed Feb 23 18:35:41 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA09395
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 18:35:24 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id PAA01276;
Wed, 23 Feb 2000 15:34:45 -0800 (PST)
Message-Id: <3.0.1.32.20000223150328.010d5a60@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Wed, 23 Feb 2000 15:03:28 -0800
To: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] Re: Feature Request
In-Reply-To: <20000223164014.E6190@rice.edu>
References: <200002230123.UAA09531@candle.pha.pa.us>
<38B3254C.D0780368@jumpline.com>
<200002230123.UAA09531@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 04:40 PM 2/23/00 -0600, Ross J. Reedstrom wrote:

"Adding a column to a relational database table seldom breaks
queries. Until Oracle 8.1.5, you weren't able to drop a column."

My guess would be that Oracle found out internally what we've been
discussing here: doing DROP COLUMN right is _hard_.

They ended up providing both kinds of drop that have been discussed
here, i.e. a slow one that actually mucks through the table getting
rid of physical data, and a quick one that simply marks the column
as being invisible resulting in it being ignored in the future.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Wed Feb 23 18:35:41 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA09405
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 18:35:35 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id PAA01305;
Wed, 23 Feb 2000 15:34:48 -0800 (PST)
Message-Id: <3.0.1.32.20000223151718.01093840@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Wed, 23 Feb 2000 15:17:18 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] interesting observatation regarding views and
V7.0
Cc: pgsql-hackers@postgreSQL.org
In-Reply-To: <5883.951346476@sss.pgh.pa.us>
References: <3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
<3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 05:54 PM 2/23/00 -0500, Tom Lane wrote:

Don Baccus <dhogaza@pacifier.com> writes:

The creation of column aliases for tables referenced by views causes
the rule created on the underlying virtual table to be in some cases
considerably longer than the corresponding rule in V6.5.
In practice, this means that several of the views used in the web
toolkit I'm porting no longer can be created.

Yes, this is exactly the concern I raised last week. Thomas didn't
seem to be very worried about the issue, but when he gets back from
his vacation we can lean on him to fix it.

OK, I saw some of the exchange last week but was so busy I
didn't really read it, other than to note when he'd committed changes
so I could update and throw the web toolkit at them. The ecommerce
module wasn't part of what I was throwing at it last week since
I knew it wasn't going to get ported from Oracle in time for our
very preliminary first cut at a port. This week, though, hasn't
been as crazy. Otherwise I would've yelped at Thomas a week ago.

"Here, YOU rewrite all these queries that use these views!" :)

Something else we might consider as a stopgap is to resurrect the
"compressed text" datatype that Jan wrote, and then removed in
anticipation of having TOAST. Jan was concerned about creating
future compatibility problems by having a datatype with only a
one-release-cycle expected lifetime ... but I think it might be
OK to use it just internally for rules.

Yeah, that's not a bad idea at all.

Also...interbase's "text" type is apparently compressed, and that's
an interesting idea for "text" itself (as opposed to "varchar()" of
a given size). Someone who just says "text" probably wants to be
able to stuff as much text into the column as possible, I know
I do! The price of compression/decompression is to some extent
balanced by not having to drag as many bytes around during joins
and sorts and the like. Decompression in particular should be
very cheap and in the kind of systems I'm working on one hopes
one's ad, product description, Q&A post etc is selected (read)
many more times than inserted (written). One hopes!

Just an interesting notion...I was kinda excited about lzText when
Jan implemented it, though a smart TOASTer is even more exciting so
I won't whine about the delay.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Wed Feb 23 18:34:36 2000
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de
[53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id SAA09221
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 18:33:33 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12Nl5w-0003kgC; Thu, 24 Feb 100 00:21 MET
Message-Id: <m12Nl5w-0003kgC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Cache query (PREPARE/EXECUTE)
In-Reply-To: <5824.951345647@sss.pgh.pa.us> from Tom Lane at "Feb 23,
2000 05:40:47 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 00:21:24 +0100 (CET)
CC: Jan Wieck <wieck@debis.com>, Karel Zak - Zakkr <zakkr@zf.jcu.cz>,
Hiroshi Inoue <Inoue@tpf.co.jp>,
pgsql-hackers <pgsql-hackers@postgreSQL.org>
Reply-To: Jan Wieck <wieck@debis.com>
X-Mailer: ELM [version 2.4ME+ PL68 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

wieck@debis.com (Jan Wieck) writes:

Then again, copyObject/freeObject must be fixed WRT
leakage/multiref anyway.

Not if we decide to get rid of freeObject, instead.

I think that a little work would have to be done to support efficient
use of large numbers of contexts, but it's certainly doable. This
path seems more attractive than trying to make the world safe for
freeObject of arbitrary node trees.

Yes, little work to build the framework. All
alloc/realloc/free functions for a particular context are
just function-pointers inside the context structure itself.
So ther'll be no additional call overhead when dealing with
large numbers of contexts.

OTOH, this new per-object-context stuff could hand down some
lifetime flag, let's say MCXT_UNTIL_STATEMENT, MCXT_UTIL_XEND
and MCXT_UNTIL_INFINITY to start from. The memory context
creation/destruction routines could manage some global lists
of contexts, that automatically get destroyed on
AtXactCommitMemory and so on, making such a kind of per-
object memory context a fire'n'forget missile (Uh - played
F15 too excessively :-). It should still be destroyed
explicitly if not needed anymore, but if allocated with the
correct lifetime, wouldn't hurt that much if forgotten.

More work to get all the existing places in the backend
making use of this functionality where applicable.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

From bouncefilter Wed Feb 23 18:39:36 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA09800
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 18:38:43 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id SAA05985;
Wed, 23 Feb 2000 18:38:29 -0500 (EST)
To: Jan Wieck <wieck@debis.com>
cc: Karel Zak - Zakkr <zakkr@zf.jcu.cz>, Hiroshi Inoue <Inoue@tpf.co.jp>,
pgsql-hackers <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Cache query (PREPARE/EXECUTE)
In-reply-to: <m12Nl5w-0003kgC@orion.SAPserv.Hamburg.dsh.de>
References: <m12Nl5w-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Comments: In-reply-to wieck@debis.com (Jan Wieck)
message dated "Thu, 24 Feb 2000 00:21:24 +0100"
Date: Wed, 23 Feb 2000 18:38:29 -0500
Message-ID: <5981.951349109@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

wieck@debis.com (Jan Wieck) writes:

OTOH, this new per-object-context stuff could hand down some
lifetime flag, let's say MCXT_UNTIL_STATEMENT, MCXT_UTIL_XEND
and MCXT_UNTIL_INFINITY to start from.

A good thing to keep in mind, but for the short term I'm not sure
we need it; the proposed new contexts are all for indefinite-lifetime
caches, so there's no chance to make them go away automatically.
Eventually we might have more uses for limited-lifetime contexts,
though.

Something else that needs to be looked at is how memory contexts
are tied to "portals" presently. That mechanism probably needs
to be redesigned. I have to admit I don't understand what it's
for...

regards, tom lane

From bouncefilter Wed Feb 23 18:47:41 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA10996
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 18:47:19 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id SAA06020;
Wed, 23 Feb 2000 18:46:44 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: Jan Wieck <wieck@debis.com>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] interesting observatation regarding views and V7.0
In-reply-to: <3.0.1.32.20000223151718.01093840@mail.pacifier.com>
References: <3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
<3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
<3.0.1.32.20000223151718.01093840@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Wed, 23 Feb 2000 15:17:18 -0800"
Date: Wed, 23 Feb 2000 18:46:44 -0500
Message-ID: <6017.951349604@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

Something else we might consider as a stopgap is to resurrect the
"compressed text" datatype that Jan wrote, and then removed in
anticipation of having TOAST.

Also...interbase's "text" type is apparently compressed, and that's
an interesting idea for "text" itself (as opposed to "varchar()" of
a given size). Someone who just says "text" probably wants to be
able to stuff as much text into the column as possible, I know
I do!

Just quietly make text compressed-under-the-hood, you mean? Hmm.
Interesting idea, all right, and it wouldn't create any long-term
compatibility problem since users couldn't see it directly. I think
we might have some places in the system that assume char/varchar/text
all have the same internal representation, but that could probably
be fixed without too much grief.

The price of compression/decompression is to some extent
balanced by not having to drag as many bytes around during joins
and sorts and the like.

Also, there could be a threshold: don't bother trying to compress
fields that are less than, say, 1K bytes.

Jan, what do you think? I might be able to find some time to try this,
if you approve of the idea but just don't have cycles to spare.

regards, tom lane

From bouncefilter Wed Feb 23 19:17:49 2000
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de
[53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id TAA16146
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 19:17:08 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12Nlm2-0003kgC; Thu, 24 Feb 100 01:04 MET
Message-Id: <m12Nlm2-0003kgC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] interesting observatation regarding views and V7.0
In-Reply-To: <5883.951346476@sss.pgh.pa.us> from Tom Lane at "Feb 23,
2000 05:54:36 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 01:04:54 +0100 (CET)
CC: Don Baccus <dhogaza@pacifier.com>, pgsql-hackers@postgreSQL.org
Reply-To: Jan Wieck <wieck@debis.com>
X-Mailer: ELM [version 2.4ME+ PL68 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

Something else we might consider as a stopgap is to resurrect the
"compressed text" datatype that Jan wrote, and then removed in
anticipation of having TOAST. Jan was concerned about creating
future compatibility problems by having a datatype with only a
one-release-cycle expected lifetime ... but I think it might be
OK to use it just internally for rules.

Ech - must be YOU!

If I hadn't deleted the entire (including catalog changes for
pg_type ... pg_rewrite) patch, I'd be the one to suggest. We
could easily add some warning, like "LZTEXT will disappear in
a subsequent release again - be warned", spit out during
parse, if someone explicitly uses the lztext type.

I'll spend some time with CVS to see if I can regenerate the
patch from there.

But I can feel the punches of Marc already - this patch will
cause catalog changes after official BETA start - Uh - Oh.

Jan

BTW: Good chance for Vince to LOL if I fail on that one, since I
got very impatiant once about "correct usage of CVS". Was a
little off-list flamewar that turned out to be mostly "you
got me wrong" during a phone call. But things like that
linger in background until prooven :-). Showtime!

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

From bouncefilter Wed Feb 23 19:12:48 2000
Received: from mailgw5.prontomail.com (mailgw5.prontomail.com
[209.185.149.196]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA15854
for <pgsql-hackers@postgresql.org>;
Wed, 23 Feb 2000 19:12:03 -0500 (EST)
(envelope-from rob.c@virgilio.it)
Received: from web23 (209.185.149.223) by mailgw5.prontomail.com (NPlex
2.0.123); Wed, 23 Feb 2000 16:02:18 -0800
From: "Roberto Cornacchia" <rob.c@virgilio.it>
Message-Id: <2DF9D9DEB3AE3D11783200807CFB3229@rob.c.virgilio.it>
Date: Wed, 23 Feb 2000 19:10:36 -0500
X-Priority: Normal
Content-Type: text/plain; charset=iso-8859-1
To: tgl@sss.pgh.pa.us
Subject: Re: about 7.0 LIMIT optimization
CC: pgsql-hackers@postgresql.org
X-Mailer: Web Based Pronto
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

- You cannot select the top N rows according to criterion A ordering
the results with a different criterion B.

True, but I don't see how to do that with one indexscan (for that
matter, I don't even see how to express it in the SQL subset that
we support...)

...That's why we proposed this syntax extension:

SELECT
.
.
STOP AFTER <N> (we changed the name, but this is the LIMIT)
RANK BY <A>
ORDER BY <B>

Here you can select the best <N> rows according to <A> and then order the results on <B>.
We note that, not accounting for a similar extension, you could do the same thing only using a subselect (with an ORDER BY clause in the inner select, that is non-standard as well).

- If you ask for the best 10 rows, from a relation including
100000 rows, you have to do a traditional sort on 100000 rows and
then retain only the first 10, doing more comparisons than requested.

Not if there's an index that implements the ordering --- and if there
is not, I don't see how to avoid the sort anyway.

Of course, if you have an index there is no problem.
It is even true that if you don't have an index there is no way to avoid the sort, but in that case we use a specialized sort, which does much less comparisons.
For example, if you want the 10 best rows from 100000, these are the average numbers of comparisons:

QuickSort: 1.6E+14
SortStop: 1.5E+11

- You can choose a "fast-start" plan (i.e., basically,
a pipelined plan), but you cannot performe an "early-stop" of
the stream when you have a "slow-start" plan (e.g. involving sorts
or hash tables).

Why not? The executor *will* stop when it has as many output rows as
the LIMIT demands.

Yes, but consider this very simple case:

LIMIT 10
[something else]
MergeJoin (100000 rows)
Sort (100000 rows)
SeqScan on Table1 (100000 rows)

IndexScan on Table2 (100 rows)

Assuming that referential constraints allow us to do it, we would do the following:

[something else]
MergeJoin (10 rows)
SortStop 10 (10 rows)
SeqScan on Table1 (100000 rows)
IndexScan on Table2 (100 rows)

Here, we get only 10 rows from the outer relation. *In general*, this is NOT correct, but referential constraints make it safe in many cases. You can see that in the second approach, the "[something else]" will operate with an input stream cardinality of 10, against 100000 of the first approach. This is what we call the "push-down" of the Stop operator.

I'd be the first to admit that the cost model needs some fine-tuning
still. It's just a conceptual structure at this point.

We hope you are not considering our posts as a criticism. We used PostgreSQL as a base to our proposal, finding good results, and now we are wondering if you are interested to continue in this sense.
Btw, DB2 currently adopts "LIMIT" optimization techniques similar to ours.

Regards

Roberto Cornacchia

===========================================================

VIRGILIO MAIL - Il tuo indirizzo E-mail gratis e per sempre
http://mail.virgilio.it/

VIRGILIO - La guida italiana a Internet
http://www.virgilio.it/

From bouncefilter Wed Feb 23 19:28:48 2000
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de
[53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id TAA17093
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 19:28:38 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12NlxH-0003ksC; Thu, 24 Feb 100 01:16 MET
Message-Id: <m12NlxH-0003ksC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Cache query (PREPARE/EXECUTE)
In-Reply-To: <5981.951349109@sss.pgh.pa.us> from Tom Lane at "Feb 23,
2000 06:38:29 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 01:16:31 +0100 (CET)
CC: Jan Wieck <wieck@debis.com>, Karel Zak - Zakkr <zakkr@zf.jcu.cz>,
Hiroshi Inoue <Inoue@tpf.co.jp>,
pgsql-hackers <pgsql-hackers@postgreSQL.org>
Reply-To: Jan Wieck <wieck@debis.com>
X-Mailer: ELM [version 2.4ME+ PL68 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

wieck@debis.com (Jan Wieck) writes:

OTOH, this new per-object-context stuff could hand down some
lifetime flag, let's say MCXT_UNTIL_STATEMENT, MCXT_UTIL_XEND
and MCXT_UNTIL_INFINITY to start from.

A good thing to keep in mind, but for the short term I'm not sure
we need it; the proposed new contexts are all for indefinite-lifetime
caches, so there's no chance to make them go away automatically.
Eventually we might have more uses for limited-lifetime contexts,
though.

Sure, was only what I thought might be useful in some cases.
If not used, would it hurt to have support for it either?
Some unused List*'ers somewhere - nothing important.

Something else that needs to be looked at is how memory contexts
are tied to "portals" presently. That mechanism probably needs
to be redesigned. I have to admit I don't understand what it's
for...

U2? Makes 2 of us.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

Hi,

on Wed, 23 Feb 2000 11:56:02 -0500 Tom Lane wrote
concerning "Re: [BUGS] First experiences with Postgresql 7.0 " something like this:

Rolf Grossmann <grossman@securitas.net> writes:

I ran into a bit of a problem: I couldn't specify NOT NULL PRIMARY KEY
anymore.

For the moment try the other order: PRIMARY KEY NOT NULL.

That doesn't work either.

This is a
known parser deficiency that we chose to leave unfixed for the start of
beta, but it should be fixed for 7.0 ...

That's good to hear.

Thanks, Rolf

From bouncefilter Wed Feb 23 20:19:51 2000
Received: from orion.SAPserv.Hamburg.dsh.de (Tpolaris2.sapham.debis.de
[53.2.131.8]) by hub.org (8.9.3/8.9.3) with SMTP id UAA23216
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 20:19:11 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12Nmjn-0003ktC; Thu, 24 Feb 100 02:06 MET
Message-Id: <m12Nmjn-0003ktC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] interesting observatation regarding views and V7.0
In-Reply-To: <6017.951349604@sss.pgh.pa.us> from Tom Lane at "Feb 23,
2000 06:46:44 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 02:06:39 +0100 (CET)
CC: Don Baccus <dhogaza@pacifier.com>, Jan Wieck <wieck@debis.com>,
pgsql-hackers@postgreSQL.org
Reply-To: Jan Wieck <wieck@debis.com>
X-Mailer: ELM [version 2.4ME+ PL68 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

Don Baccus <dhogaza@pacifier.com> writes:

Also...interbase's "text" type is apparently compressed, and that's
an interesting idea for "text" itself (as opposed to "varchar()" of
a given size). Someone who just says "text" probably wants to be
able to stuff as much text into the column as possible, I know
I do!

Just quietly make text compressed-under-the-hood, you mean? Hmm.
Interesting idea, all right, and it wouldn't create any long-term
compatibility problem since users couldn't see it directly. ...

If we wheren't in BETA code freeze right now, I'd call for
another month delay - surely.

The price of compression/decompression is to some extent
balanced by not having to drag as many bytes around during joins
and sorts and the like.

Also, there could be a threshold: don't bother trying to compress
fields that are less than, say, 1K bytes.

Jan, what do you think? I might be able to find some time to try this,
if you approve of the idea but just don't have cycles to spare.

It's a very temping solution, turn "text" into "lztext"
silently, and revert that internal changes in the next
release again while implementing TOAST. Remember that the
lztext I implemented had the mentioned threshold paramenter -
say 256 - from the very beginning. And you know 256->1K is a
one-liner in my coding style. Moreover, it was a global
parameter set driven value, and thus potentially prepared to
be a runtime configurable one (the other values of the
parameter set where minimum compression ratio to gain,
maximum result size to force compression even if ratio below,
GOOD size to stop history lookup and finally history lookup
GOOD lowering factor during lookups).

The algorithm I used for compression is one, loosing possible
compression ratio to gain speed. It uses a poor XOR
combination of the next 4 input-bytes, to lookup a history
table - and that's anything but perfect from a hashing
algorithms point of view. But it was enough to make a 50+
column view fit easily into pg_rewrite. And that's what it
was made for.

Anyway, there are far too many direct references to VARDATA
on "text" plus all the assumptions on binary compatibility
between text, varchar etc. in the code, to start on it during
BETA.

Thus, I see a good chance for a 7.1 release, really soon
after 7.0. Then have a longer delay for the next one,
featuring TOAST.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#========================================= wieck@debis.com (Jan Wieck) #

From bouncefilter Wed Feb 23 20:37:48 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA24553
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 20:36:51 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id RAA20859;
Wed, 23 Feb 2000 17:35:42 -0800 (PST)
Message-Id: <3.0.1.32.20000223171520.0108c3e0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Wed, 23 Feb 2000 17:15:20 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] interesting observatation regarding views and
V7.0
Cc: Jan Wieck <wieck@debis.com>, pgsql-hackers@postgreSQL.org
In-Reply-To: <6017.951349604@sss.pgh.pa.us>
References: <3.0.1.32.20000223151718.01093840@mail.pacifier.com>
<3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
<3.0.1.32.20000223081725.010c5c90@mail.pacifier.com>
<3.0.1.32.20000223151718.01093840@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 06:46 PM 2/23/00 -0500, Tom Lane wrote:

Just quietly make text compressed-under-the-hood, you mean? Hmm.

Yep...

Interesting idea, all right, and it wouldn't create any long-term
compatibility problem since users couldn't see it directly. I think
we might have some places in the system that assume char/varchar/text
all have the same internal representation, but that could probably
be fixed without too much grief.

I've kind of assumed this might be the case, but have truly been
too busy to dig around looking (which in my case takes a fairly
long time because I'm really only barely familiar with the code)

The price of compression/decompression is to some extent
balanced by not having to drag as many bytes around during joins
and sorts and the like.

Also, there could be a threshold: don't bother trying to compress
fields that are less than, say, 1K bytes.

Right, I thought about that possibility, too, but it seems a bit
more complicated so I thought I'd raise the simpler-sounding idea
first :)

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Wed Feb 23 20:36:48 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA24493
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 20:36:24 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id RAA20898;
Wed, 23 Feb 2000 17:35:49 -0800 (PST)
Message-Id: <3.0.1.32.20000223173315.0170ba80@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Wed, 23 Feb 2000 17:33:15 -0800
To: "Roberto Cornacchia" <rob.c@virgilio.it>, tgl@sss.pgh.pa.us
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] Re: about 7.0 LIMIT optimization
Cc: pgsql-hackers@postgreSQL.org
In-Reply-To: <2DF9D9DEB3AE3D11783200807CFB3229@rob.c.virgilio.it>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 07:10 PM 2/23/00 -0500, Roberto Cornacchia wrote:

Of course, if you have an index there is no problem.
It is even true that if you don't have an index there is no way to avoid

the sort, but in that case we use a specialized sort, which does much less
comparisons.

For example, if you want the 10 best rows from 100000, these are the

average numbers of comparisons:

QuickSort: 1.6E+14
SortStop: 1.5E+11

This makes sense ... you can stop once you can guarantee that the first
ten rows are in proper order. I'm not familiar with the algorithm
but not terribly surprised that one exists.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Wed Feb 23 21:15:49 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA27885
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 21:15:19 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id VAA29702;
Wed, 23 Feb 2000 21:14:54 -0500 (EST)
To: Jan Wieck <wieck@debis.com>
cc: Don Baccus <dhogaza@pacifier.com>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] interesting observatation regarding views and V7.0
In-reply-to: <m12Nlm2-0003kgC@orion.SAPserv.Hamburg.dsh.de>
References: <m12Nlm2-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Comments: In-reply-to wieck@debis.com (Jan Wieck)
message dated "Thu, 24 Feb 2000 01:04:54 +0100"
Date: Wed, 23 Feb 2000 21:14:53 -0500
Message-ID: <29699.951358493@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

wieck@debis.com (Jan Wieck) writes:

But I can feel the punches of Marc already - this patch will
cause catalog changes after official BETA start - Uh - Oh.

You can hide behind me ;-) ... I just did commit some catalog changes
(but didn't need to force initdb, since they were only additions).

Also, I am more than half expecting that I will have to force an initdb
to clean up the INET/CIDR comparison business; very likely we are
going to end up needing to have separate comparison operators for
INET and CIDR.

Still waiting for input on that from the folks who use the datatypes,
though. (D'Arcy, are you still out there?)

regards, tom lane

From bouncefilter Wed Feb 23 21:22:49 2000
Received: from rage.hub.org (root@nat195.216.mpoweredpc.net [142.177.195.216])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA28590
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 21:22:17 -0500 (EST) (envelope-from jeff@pgsql.com)
Received: from localhost (jeff@localhost)
by rage.hub.org (8.9.3/8.9.3) with ESMTP id WAA73373;
Wed, 23 Feb 2000 22:21:29 -0400 (AST) (envelope-from jeff@pgsql.com)
X-Authentication-Warning: rage.hub.org: jeff owned process doing -bs
Date: Wed, 23 Feb 2000 22:21:27 -0400 (AST)
From: Jeff MacDonald <jeff@pgsql.com>
X-Sender: jeff@rage.hub.org
Reply-To: Jeff MacDonald <jeff@pgsql.com>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: James.Chalex@informit.com,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Re: Interested in writing a PostgreSQL article?
In-Reply-To: <200002231558.KAA29354@candle.pha.pa.us>
Message-ID: <Pine.BSF.4.10.10002232219420.67178-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Hi Bruce, James,

I would be interested in writing a PHP / PostgreSQL basic
tutorial. Please contact me at +902 542 0713.

Jeff

On Wed, 23 Feb 2000, Bruce Momjian wrote:

I am writing a book and don't have time for additional writing at this
time. I am CC'ing the hackers list to see if anyone can do it.

Bruce,

My name is James Chalex -- I'm an acquisitions editor with
informit.com. I work on our Linux subsite, and am interested in
having you write an article on using PostgreSQL with Linux. This
could be as simple as an introduction and a installation guide,
or something more focused, like administrative tips, or maybe
a scripting article for using PHP, Python, etc.

I'm very open to your ideas as well -- ideally I'd like something
that is new and/or has caused problems for people in the past.
Our preferred audience member would already have a good deal of
database experience, but is still looking to master subtleties.

We pay anywhere from $250 to $500 per article, depending on
length, scope, etc.

If for whatever reason you're not interested, I would greatly
appreciate it if you could pass this along to someone who would
be interested in this kind of work.

I look forward to hearing from you,

James

James Chalex - Acquisitions Editor, InformIT james.chalex@informit.com
www.informit.com phone: 317.817.7489 free : 800.545.5914 fax
: 317.817.7232

InformIT 201 West 103rd Street Indianapolis, IN 46290

--
Bruce Momjian                        |  http://www.op.net/~candle
pgman@candle.pha.pa.us               |  (610) 853-3000
+  If your life is a hard drive,     |  830 Blythe Avenue
+  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

************

Jeff MacDonald
jeff@pgsql.com

From bouncefilter Wed Feb 23 21:24:49 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA28827
for <pgsql-hackers@postgresql.org>;
Wed, 23 Feb 2000 21:23:58 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id VAA29733;
Wed, 23 Feb 2000 21:23:46 -0500 (EST)
To: "Roberto Cornacchia" <rob.c@virgilio.it>
cc: pgsql-hackers@postgresql.org
Subject: Re: about 7.0 LIMIT optimization
In-reply-to: <2DF9D9DEB3AE3D11783200807CFB3229@rob.c.virgilio.it>
References: <2DF9D9DEB3AE3D11783200807CFB3229@rob.c.virgilio.it>
Comments: In-reply-to "Roberto Cornacchia" <rob.c@virgilio.it>
message dated "Wed, 23 Feb 2000 19:10:36 -0500"
Date: Wed, 23 Feb 2000 21:23:46 -0500
Message-ID: <29730.951359026@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Roberto Cornacchia" <rob.c@virgilio.it> writes:

Why not? The executor *will* stop when it has as many output rows as
the LIMIT demands.

Yes, but consider this very simple case:

LIMIT 10
[something else]
MergeJoin (100000 rows)
Sort (100000 rows)
SeqScan on Table1 (100000 rows)

IndexScan on Table2 (100 rows)

Assuming that referential constraints allow us to do it, we would do the
following:

[something else]
MergeJoin (10 rows)
SortStop 10 (10 rows)
SeqScan on Table1 (100000 rows)
IndexScan on Table2 (100 rows)

Here, we get only 10 rows from the outer relation. *In general*, this is
NOT correct, but referential constraints make it safe in many cases. You
can see that in the second approach, the "[something else]" will operate
with an input stream cardinality of 10, against 100000 of the first
approach. This is what we call the "push-down" of the Stop operator.

If I understand your point correctly, the existing code arrives at this
same effect through another direction: it will choose the right plan for
the query when the [something else] node doesn't need to read very many
rows. This isn't reflected in the EXPLAIN output very well, which might
be fooling you as to what's really happening.

I'm not sure about your comment about referential constraints. If you
are doing analysis of restriction clauses to prove that a particular
stage doesn't require reading as many rows as it otherwise would, then
you've done more than I have.

regards, tom lane

From bouncefilter Wed Feb 23 21:31:49 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA29707
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 21:31:02 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id VAA29762;
Wed, 23 Feb 2000 21:30:29 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: "Roberto Cornacchia" <rob.c@virgilio.it>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: about 7.0 LIMIT optimization
In-reply-to: <3.0.1.32.20000223173315.0170ba80@mail.pacifier.com>
References: <3.0.1.32.20000223173315.0170ba80@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Wed, 23 Feb 2000 17:33:15 -0800"
Date: Wed, 23 Feb 2000 21:30:29 -0500
Message-ID: <29759.951359429@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

For example, if you want the 10 best rows from 100000, these are the

average numbers of comparisons:

QuickSort: 1.6E+14
SortStop: 1.5E+11

Are there some zeroes missing here? That sounds like an awful lot of
operations for a quicksort of only 1E5 elements...

This makes sense ... you can stop once you can guarantee that the first
ten rows are in proper order. I'm not familiar with the algorithm
but not terribly surprised that one exists.

The obvious way to do it would be with a heap-based sort. After you've
built the heap, you pull out the first ten elements and then stop.
Offhand this only seems like it'd save about half the work, though,
so maybe Roberto has a better idea.

regards, tom lane

From bouncefilter Wed Feb 23 21:37:50 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id VAA30422
for <pgsql-hackers@postgreSQL.org>;
Wed, 23 Feb 2000 21:37:00 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id SAA12527;
Wed, 23 Feb 2000 18:35:53 -0800 (PST)
Message-Id: <3.0.1.32.20000223183336.0170dc60@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Wed, 23 Feb 2000 18:33:36 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] Re: about 7.0 LIMIT optimization
Cc: "Roberto Cornacchia" <rob.c@virgilio.it>, pgsql-hackers@postgreSQL.org
In-Reply-To: <29759.951359429@sss.pgh.pa.us>
References: <3.0.1.32.20000223173315.0170ba80@mail.pacifier.com>
<3.0.1.32.20000223173315.0170ba80@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 09:30 PM 2/23/00 -0500, Tom Lane wrote:

Don Baccus <dhogaza@pacifier.com> writes:

For example, if you want the 10 best rows from 100000, these are the

average numbers of comparisons:

QuickSort: 1.6E+14
SortStop: 1.5E+11

Are there some zeroes missing here? That sounds like an awful lot of
operations for a quicksort of only 1E5 elements...

Yeah, obviously one or more of his numbers are wrong. Let's see, a
bubble sort's only O(n^2), "only" 1E10/2 comparisons for 1E5 elements,
right? Surely O(n*log(n)) is quicker :)

This makes sense ... you can stop once you can guarantee that the first
ten rows are in proper order. I'm not familiar with the algorithm
but not terribly surprised that one exists.

The obvious way to do it would be with a heap-based sort. After you've
built the heap, you pull out the first ten elements and then stop.
Offhand this only seems like it'd save about half the work, though,
so maybe Roberto has a better idea.

I'd like to see some elaboration.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Thu Feb 24 00:47:07 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id AAA48069
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 00:46:40 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id AAA14208;
Thu, 24 Feb 2000 00:45:56 -0500 (EST)
To: "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir.Benes@pvt.cz>
cc: pgsql-hackers@postgreSQL.org,
"=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm@pvt.net>
Subject: Re: [HACKERS] Out of memory problem (forwarded bug report)
In-reply-to: <001e01bf7dcf$42506310$451c11ac@p53apk.chv.pvt.cz>
References: <001e01bf7dcf$42506310$451c11ac@p53apk.chv.pvt.cz>
Comments: In-reply-to "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?="
<Vladimir.Benes@pvt.cz>
message dated "Wed, 23 Feb 2000 08:26:11 +0100"
Date: Thu, 24 Feb 2000 00:45:56 -0500
Message-ID: <14205.951371156@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Vladimir,
Thanks for the details. I think you are undoubtedly running into
expression evaluation memory leaks. Basically, any expression that
yields a non-pass-by-value data type consumes memory that is not
reclaimed until end of statement --- so when you process a few million
rows, that memory starts to add up. (Yes, I realize this is a horrible
misfeature. It's on our TO-DO list to fix it, but it probably won't
happen until 7.1 or 7.2.) In the meantime the best I can offer you
is workarounds.

I think the major problems here are coming from the
"date_trunc('day',start)" calculation (because its datetime result is
pass-by-reference) and to a lesser extent from the sum(bytes)
calculation (because int8 is pass-by-reference). You could easily
replace "date_trunc('day',start)" with "date(start)"; since date is
a pass-by-value type, that won't leak memory, and it should give
equivalent results. The int8 sum is not quite so easy to fix.
I assume you can't get away with switching to int4 --- probably
your sum would overflow an int4? It may be that just fixing the
inefficient date_trunc calc will reduce your memory requirements
enough to get by. If not, the only good news I have is that release
7.0 does fix the memory-leak problem for internal calculations of
aggregate functions like sum(). You can get the first beta release
for 7.0 now.

regards, tom lane

"=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir.Benes@pvt.cz> writes:

-----P���vodn��� zpr���va-----
Od: Tom Lane <tgl@sss.pgh.pa.us>
Komu: Oliver Elphick <olly@lfix.co.uk>
Kopie: pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>;
Vladimir.Benes@pvt.cz <Vladimir.Benes@pvt.cz>
Datum: 22. ���nora 2000 18:06
P���edm���t: Re: [HACKERS] Out of memory problem (forwarded bug report)

"Oliver Elphick" <olly@lfix.co.uk> writes:

Can someone advise, please, how to deal with this problem in 6.5.3?

My guess is that the cause is memory leaks during expression evaluation;
but without seeing the complete view definitions and underlying table
definitions, it's impossible to know what processing is being invoked
by this query...

regards, tom lane

Well, I will append views and underlying table definition:

1) Once again - failure query:
select comm_type,name,tot_bytes,tot_packets
from flow_sums_days_send_200002_view
where day='2000-02-21' and name not like '@%'
union all
select comm_type,name,tot_bytes,tot_packets
from flow_sums_days_receive_200002_view
where day='2000-02-21' and name not like '@%'

2) views definition:
create view flow_sums_days_send_200002_view as
select
'send'::varchar as comm_type, date_trunc('day',start) as day,
src_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, src_name

create view flow_sums_days_receive_200002_view as
select
'receive'::varchar as comm_type, date_trunc('day',start) as day,
dst_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, dst_name

I wanted create only one usefull view:

create view flow_sums_days_200002_view as
select
'send'::varchar as comm_type, date_trunc('day',start) as day,
src_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, src_name
UNION ALL
select
'receive'::varchar as comm_type, date_trunc('day',start) as day,
dst_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, dst_name

...but Postgres cann't use clause UNION ALL at view definition. So I created
two views mentioned above and I wanted use this ones with UNION ALL clause
only.

3) underlaying table definition:
create table flow_sums_200002 (
primary_collector varchar(50) not null,
start datetime not null,
end_period datetime not null,
dead_time_rel float4 not null,
src_name varchar(50) not null,
dst_name varchar(50) not null,
bytes int8 not null,
packets int4 not null
)

Today this table has about 3 000 000 rows and the select command
mentioned above returns 190 + 255 rows.

Now I don't use clause "UNION ALL" and the program executes two queryes
and then adds both result to new result. I reduced time increment of number
rows to flow_sums_200002 table (three times less). This table contains data
of February 2000 and the program will create table flow_sums_200003 with
relevant views next month.
Well, now this solution solve my problem but always depends on number of
rows - I only moved limit of rows count.

Thank You, V. Benes

P.S.: I append part of top on my system while the query is running:

CPU states: 98.6% user, 1.3% system, 0.0% nice, 0.0% idle
Mem: 127256K av, 124316K used, 2940K free, 29812K shrd, 2620K buff
Swap: 128516K av, 51036K used, 77480K free 7560K cached

PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME COMMAND
2942 postgres 20 0 141M 99M 17348 R 0 99.0 80.4 1:22 postmaster

=> postmaster later took 80 - 95% of memory, free memory decressed to 2 MB,
CPU was overloaded (0% idle and 99% by user process of postmaster). Have You
ever seen something similar :-) ?

From bouncefilter Thu Feb 24 03:37:54 2000
Received: from design.nl (lexington.design.nl [193.78.77.101])
by hub.org (8.9.3/8.9.3) with ESMTP id DAA67024
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 03:37:25 -0500 (EST)
(envelope-from jeroen@design.nl)
Received: from benny (ranger.design.nl [212.206.76.159])
by design.nl (General Design 1.1) with ESMTP
id JAA09200; Thu, 24 Feb 2000 09:34:11 +0100 (NLD)
Message-Id: <4.2.2.20000222171620.00a9a100@mail.design.nl>
X-Sender: jeroenv@mail.design.nl
X-Mailer: QUALCOMM Windows Eudora Pro Version 4.2.2
Date: Thu, 24 Feb 2000 09:35:19 +0100
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Jeroen van Vianen <jeroen@design.nl>
Subject: Re: [HACKERS] Re: [PATCHES] Patch for more readable parse
error messages
Cc: Peter Eisentraut <peter_e@gmx.net>, pgsql-hackers@postgresql.org
In-Reply-To: <23758.951235934@sss.pgh.pa.us>
References: <4.2.2.20000222103948.00ac8d10@mail.design.nl>
<4.2.0.58.20000220151757.00956ba0@mail.design.nl>
<4.2.2.20000222103948.00ac8d10@mail.design.nl>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"; format=flowed

At 11:12 22-02-00 -0500, Tom Lane wrote:

Jeroen van Vianen <jeroen@design.nl> writes:

What I see now is something like this (for psql):

psql sends a query
psql reads response
if response is error
get error location and find context in which error

occurred

print error message, with error location and context
otherwise
do what it used to do

and for the other interfaces nothing changes.

This is something I might be able to implement for 7.1.

This looks much better to me than doing it in the backend. What still
needs a little thought is how to send back the error location from
backend to client app.

I'd be inclined to say that the location info should be imbedded as
text in the existing textual error message, rather than trying to add
a separate message with a machine-readable location value. The first
way is much less likely to create compatibility problems with old client
apps. One way to do it is to say that if the last line of the error
message looks like

Error-location: nnn

then libpq should recognize that, strip the line out of the saved
textual error message, and make the location value available through
a new API call.

Isn't it possible to get this kind of information from a call to a new API
struct errorinfo * PQerrorInfo(conn) where the struct contains info about
the error message, location and code, rather than a call to
PQerrorMessage(conn) ?

The reason I suggest a label is that we could further extend this
protocol to handle some other things that people have been griping
about for a long time: providing identifying error code numbers that
client code could rely on instead of trying to match against the error
text, and separating out the info about which routine generated the
error (which is mighty handy for backend debugging but is useless
info for Joe Average user). Someday the message being sent back
might look less like

ERROR: relation_info: Relation 12345 not found

and more like

ERROR: Failed to find relation OID 12345 in system catalogs
Error-code: 4242
Reporting-routine: relation_info, plancat.c line 543

of which only the first line is really meant for the user.

This might even allow the client app to write out a customized error
message, instead of 'foreign key ... violation' write 'You cannot delete
any ... when there are still ...', based upon error codes.

Of course, making that happen will be a lot of work, and I'm not
asking you to volunteer for it. But what you do now should fit
in with further development of the error handling stuff...

Jeroen

From bouncefilter Thu Feb 24 03:49:56 2000
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
by hub.org (8.9.3/8.9.3) with ESMTP id DAA68125
for <pgsql-general@postgresql.org>;
Thu, 24 Feb 2000 03:48:54 -0500 (EST)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (sfcabop1.nettuno.it [193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 4.1) with ESMTP id JAA03338;
Thu, 24 Feb 2000 09:47:24 +0100 (MET)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 12NuwG-0001HS-00; Thu, 24 Feb 2000 09:52:04 +0000
Message-ID: <38B4F0C3.C93B78AB@sferacarta.com>
Date: Thu, 24 Feb 2000 09:50:11 +0100
From: Jose Soares <jose@sferacarta.com>
Organization: Sfera Carta
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Wim Ceulemans <wim.ceulemans@nice.be>
CC: "'general'" <pgsql-general@postgresql.org>
Subject: Re: [GENERAL] AW: [HACKERS] TRANSACTIONS
References:
<219F68D65015D011A8E000006F8590C604AF7CF0@sdexcsrv1.f000.d0188.sd.spardat.at>
<38B3BFDA.6476F0F7@nice.be>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Wim Ceulemans wrote:

Zeugswetter Andreas SB wrote:

Jose Soares <jose@sferacarta.com> writes:

-------------------------------------------------------
Interbase, Oracle,Informix,Solid,Ms-Access,DB2:
-------------------------------------------------------
connect hygea.gdb;
create table temp(a int);
insert into temp values (1);
insert into temp values (1000000000000000000000000000000000);
commit;
select * from temp;

arithmetic exception, numeric overflow, or string truncation

A
===========
1

I would like to know what the Standard says and who is in the rigth path
PostgreSQL or the others, considering the two examples reported below.

I think those other guys are unquestionably failing to
conform to SQL92.
6.10 general rule 3.a says

All others also throw an error for this statement, and thus conform.
As you can see from the select only the first row is inserted.
I think the numeric is only an example of an error, it could also be
any other error, like "duplicate key" or the like.

......

and 3.3.4.1 says

The phrase "an exception condition is raised:", followed by the
name of a condition, is used in General Rules and elsewhere to
indicate that the execution of a statement is unsuccessful, ap-
plication of General Rules, other than those of Subclause 12.3,
"<procedure>", and Subclause 20.1, "<direct SQL statement>", may
be terminated, diagnostic information is to be made available,
and execution of the statement is to have no effect on SQL-data

or

Note here, that they say "the statement", which does not say anything about
other statements in the same transaction.

schemas. The effect on <target specification>s and SQL descriptor
areas of an SQL-statement that terminates with an exception

condi-

tion, unless explicitly defined by this International Standard,

is

implementation-dependent.

I see no way that allowing the transaction to commit after an overflow
can be called consistent with the spec.

Of course it can not commit this single statement that was in error.
All he wants is to commit all other statements, before and after the
error statement inside this same transaction.

Isn't the intention of a transaction that it is atomic, i.e. either all
statements pass or none of them? (see section 5.4 in the standard).

There's another problem, in the following example the transaction il failed but
the transation it is not automatically rolledback, it remains instead in an "ABORT
STATE" waitting for an explicit ROLLBACK or COMMIT.
If I'm using transactions from a client program I don't know what's happened to
the back end.

first example:
^^^^^^^^^^
prova=> begin work;
BEGIN
prova=> create table tmp(a int);
ERROR: Relation 'tmp' already exists
prova=> drop table tmp;
NOTICE: (transaction aborted): all queries ignored until end of transaction block

*ABORT STATE*
prova=> commit;
END
-----------------------------------------------------------------------
What is happening ?
Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a
COMMIT/ROLLBACK ?
Why PostgreSQL allows a COMMIT in this case ?

second example:
^^^^^^^^

prova=> begin;
BEGIN
prova=> create table tmp(a int);
CREATE
prova=> create table tmp(a int);
ERROR: Relation 'tmp' already exists
prova=> select * from tmp;
ERROR: mdopen: couldn't open tmp: No such file or directory
prova=> commit;
END
prova=> select * from tmp;
ERROR: tmp: Table does not exist.
-----------------------------------------------------------------------
What is happening ?
Apparently the transaction was successful but the TMP table doesn't exist after a
successful COMMIT.
Why PostgreSQL allows a COMMIT in this case ?
Why in this case PostgreSQL doesn't show the:
NOTICE: (transaction aborted): all queries ignored until end of
transaction block
*ABORT STATE*

--
Jose' Soares
Bologna, Italy Jose@sferacarta.com

From bouncefilter Thu Feb 24 04:05:54 2000
Received: from gandalf.it-austria.net (gandalf.it-austria.net [213.150.1.65])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA69956
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 04:05:00 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at (sdgtw.sd.spardat.at
[172.18.1.16])
by gandalf.it-austria.net (xxx/xxx) with ESMTP id KAA92458;
Thu, 24 Feb 2000 10:04:11 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <1TSZGC22>; Thu, 24 Feb 2000 10:04:10 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C604AF7CF3@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: "'hackers'" <pgsql-hackers@postgreSQL.org>
Subject: AW: AW: [HACKERS] TRANSACTIONS
Date: Thu, 24 Feb 2000 10:04:10 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

In this sense a commit is not partial. The commit should commit
all statements that were not in error.

That interpretation eliminates an absolutely essential capability
(all-or-none behavior) in favor of what strikes me as a very minor
programming shortcut.

The all-or-none behavior is what you get if you simply do a rollback
on any error or warning. I don't see a special programming difficulty here.

All other DB's behave in this way.

I find this hard to believe, and even harder to believe that it's
mandated by the standard. What you're essentially claiming is that
everyone but us has nested transactions

They don't necessarily have nested tx, although some have.
All they provide is atomicity of single statements.

(which'd be the only way to
roll back a single failed statement inside a transaction) and that
SQL92 requires nested transactions --- yet it never uses the
phrase nor
makes the obvious step to allowing user-specified nested transactions.

Yes, but they say "statement" when they mention the all-or-none behavior,
not transaction.

Andreas

From bouncefilter Thu Feb 24 04:09:54 2000
Received: from gandalf.it-austria.net (gandalf.it-austria.net [213.150.1.65])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA70462
for <hackers@postgreSQL.org>; Thu, 24 Feb 2000 04:09:03 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at (sdgtw.sd.spardat.at
[172.18.1.16])
by gandalf.it-austria.net (xxx/xxx) with ESMTP id KAA97820;
Thu, 24 Feb 2000 10:08:43 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <1TSZGC25>; Thu, 24 Feb 2000 10:08:42 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C604AF7CF4@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'Mike Mascari'" <mascarm@mascari.com>
Cc: "hackers@postgresql.org" <hackers@postgreSQL.org>
Subject: AW: AW: [HACKERS] TRANSACTIONS
Date: Thu, 24 Feb 2000 10:08:43 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="windows-1252"

OK. May be I miss something.

I don't think so. Not with respect to Oracle. Andreas knows that
Oracle implicitly commits your running transaction -- and starts
a new one whenever a DDL statement is encountered. A large
discussion about this arose about 4 months ago...I can't speak
for DB2.

Yes, sorry, I think we should leave out the ddl statements here.
The real essential part is the dml statement block in this example.
Since the create table was the first statement in the block,
the only difference between the other db's is wheather the table
exists after a rollback. They will all have the table with one row in
it after a commit.

Andreas

From bouncefilter Thu Feb 24 04:19:54 2000
Received: from gandalf.it-austria.net (gandalf.it-austria.net [213.150.1.65])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA71637
for <hackers@postgresql.org>; Thu, 24 Feb 2000 04:19:40 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at (sdgtw.sd.spardat.at
[172.18.1.16])
by gandalf.it-austria.net (xxx/xxx) with ESMTP id KAA130318;
Thu, 24 Feb 2000 10:18:58 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <1TSZGCK7>; Thu, 24 Feb 2000 10:18:58 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C604AF7CF5@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'kdebisschop@range.infoplease.com'"
<kdebisschop@range.infoplease.com>
Cc: "'hackers@postgresql.org'" <hackers@postgresql.org>
Subject: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS
Date: Thu, 24 Feb 2000 10:18:58 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

========================= example =========================

$dbh->{AutoCommit} = 0;
$dbh->do("CREATE TABLE tmp (a int unique,b int)");
while (<>){
if (/([0-9]+) ([0-9]+)/) {
$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
}
}
$dbh->commit;
$dbh->disconnect;

========================= end ============================

This is a very good example, and is unfortunately currently not possible in
PostgreSQL. But I am sure Vadim is on his way to fix that :-)

Andreas

From bouncefilter Thu Feb 24 05:51:56 2000
Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2])
by hub.org (8.9.3/8.9.3) with ESMTP id FAA79410
for <hackers@postgreSQL.org>; Thu, 24 Feb 2000 05:51:17 -0500 (EST)
(envelope-from oleg@sai.msu.su)
Received: from ra (ra [158.250.29.2])
by ra.sai.msu.su (8.9.3/8.9.3) with SMTP id NAA17250;
Thu, 24 Feb 2000 13:46:39 +0300 (GMT)
Date: Thu, 24 Feb 2000 13:46:38 +0300 (GMT)
From: Oleg Bartunov <oleg@sai.msu.su>
X-Sender: megera@ra
To: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
cc: "'kdebisschop@range.infoplease.com'" <kdebisschop@range.infoplease.com>,
"'hackers@postgresql.org'" <hackers@postgreSQL.org>
Subject: Re: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS
In-Reply-To:
<219F68D65015D011A8E000006F8590C604AF7CF5@sdexcsrv1.f000.d0188.sd.spardat.at>
Message-ID: <Pine.GSO.3.96.SK.1000224134525.22930M-100000@ra>
Organization: Sternberg Astronomical Institute (Moscow University)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Here is my example:

package Apache::Hits;

use strict;
use Apache::Constants qw(:common);

my ($sth_lock, $sth_upd, $sth_ins );

sub handler {
my $r = shift;
if ( $r->args() =~ /msg_id=(\d+)/o ) {
$HTML::Mason::Commands::dbs ||= My::DB->new(1);
my $dbh = $HTML::Mason::Commands::dbs->{dbh};
$sth_lock ||= $dbh->prepare("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
$sth_upd ||= $dbh->prepare("UPDATE hits SET count=count+1,last_access=now() WHERE msg_id=?");
$sth_ins ||= $dbh->prepare("INSERT INTO hits (msg_id,count) VALUES (?, 1)");
$dbh->{AutoCommit} = 0;
my $success = 1;
$success &&= $sth_lock->execute();
$success &&= $sth_upd->execute( $1 );
$success &&= $sth_ins->execute( $1 ) if ( $success eq '0E0');
my $result = ($success ? $dbh->commit : $dbh->rollback);
unless ( $result ) {
$r->log_error("Unable to process transaction: ". $dbh->errstr ."\n");
}
}
return OK;
}

1;
__END__

On Thu, 24 Feb 2000, Zeugswetter Andreas SB wrote:

Date: Thu, 24 Feb 2000 10:18:58 +0100
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'kdebisschop@range.infoplease.com'"

<kdebisschop@range.infoplease.com>

Cc: "'hackers@postgresql.org'" <hackers@postgreSQL.org>
Subject: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS

========================= example =========================

$dbh->{AutoCommit} = 0;
$dbh->do("CREATE TABLE tmp (a int unique,b int)");
while (<>){
if (/([0-9]+) ([0-9]+)/) {
$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
}
}
$dbh->commit;
$dbh->disconnect;

========================= end ============================

This is a very good example, and is unfortunately currently not possible in
PostgreSQL. But I am sure Vadim is on his way to fix that :-)

Andreas

************

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

From bouncefilter Thu Feb 24 06:19:55 2000
Received: from mail.enterprise.net (mail.enterprise.net [194.72.192.18])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA81804
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 06:19:33 -0500 (EST) (envelope-from olly@lfix.co.uk)
Received: from linda.lfix.co.uk (max03-077.enterprise.net [194.72.196.77])
by mail.enterprise.net (8.8.5/8.8.5) with ESMTP id LAA09603;
Thu, 24 Feb 2000 11:19:30 GMT
Received: from lfix.co.uk (olly@localhost [127.0.0.1])
by linda.lfix.co.uk (8.9.3/8.9.3/Debian 8.9.3-6) with ESMTP id LAA16307;
Thu, 24 Feb 2000 11:22:38 GMT
Message-Id: <200002241122.LAA16307@linda.lfix.co.uk>
X-Authentication-Warning: linda.lfix.co.uk: Host olly@localhost [127.0.0.1]
claimed to be lfix.co.uk
X-Mailer: exmh version 2.1.1 10/15/1999 (debian)
To: "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir.Benes@pvt.cz>
Cc: pgsql-hackers@postgreSQL.org,
"=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm@pvt.net>
Subject: Re: [HACKERS] Out of memory problem (forwarded bug report)
In-Reply-To: Message from Tom Lane <tgl@sss.pgh.pa.us>
of "Thu, 24 Feb 2000 00:45:56 EST." <14205.951371156@sss.pgh.pa.us>
References: <001e01bf7dcf$42506310$451c11ac@p53apk.chv.pvt.cz>
<14205.951371156@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Mime-Version: 1.0
Date: Thu, 24 Feb 2000 11:22:38 +0000
From: "Oliver Elphick" <olly@lfix.co.uk>
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id GAA81845

Tom Lane wrote:
...

your sum would overflow an int4? It may be that just fixing the
inefficient date_trunc calc will reduce your memory requirements
enough to get by. If not, the only good news I have is that release
7.0 does fix the memory-leak problem for internal calculations of
aggregate functions like sum(). You can get the first beta release
for 7.0 now.

I'm putting together a Debian release of the beta at the moment.

--
Oliver Elphick Oliver.Elphick@lfix.co.uk
Isle of Wight http://www.lfix.co.uk/oliver
PGP key from public servers; key ID 32B8FAA1
========================================
"Thy word is a lamp unto my feet, and a light unto my
path." Psalms 119:105

From bouncefilter Thu Feb 24 07:06:57 2000
Received: from fw.pvt.cz (fw.pvt.cz [194.149.101.194])
by hub.org (8.9.3/8.9.3) with SMTP id HAA85442
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 07:06:18 -0500 (EST)
(envelope-from Vladimir.Benes@pvt.cz)
Received: by fw.pvt.cz; (5.65v4.0/1.3/10May95) id AA09776;
Thu, 24 Feb 2000 13:05:37 +0100
Received: from p53w02.chv.pvt.cz (p53w02.chv.pvt.cz [172.17.28.6])
by mh.pvt.cz (8.9.1/8.9.1) with ESMTP id NAA13936;
Thu, 24 Feb 2000 13:05:36 +0100 (MET)
Received: from p53apk (p53apk.chv.pvt.cz [172.17.28.69]) by p53w02.chv.pvt.cz
with SMTP (Microsoft Exchange Internet Mail Service Version
5.5.2650.21) id FLP7C0QC; Thu, 24 Feb 2000 13:05:36 +0100
Message-Id: <010101bf7ebf$888368f0$451c11ac@p53apk.chv.pvt.cz>
From: "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir.Benes@pvt.cz>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: <pgsql-hackers@postgreSQL.org>,
"=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm@pvt.net>
Subject: Re: [HACKERS] Out of memory problem (forwarded bug report)
Date: Thu, 24 Feb 2000 13:06:08 +0100
Mime-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-2"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-Msmail-Priority: Normal
X-Mailer: Microsoft Outlook Express 4.72.2106.4
X-Mimeole: Produced By Microsoft MimeOLE V4.72.2106.4

Hi,

thank You very much, You helped me :-)

Well, I will use function date() and not date_trunc() in my views.

I tryed change type of "bytes" column from int8 to int4. Range 0 .. +2
147 483 647 will suffice here. But I must retype this type to int8 in sum
function because overflow can occur:

collector=> create table flow_sums (
collector-> primary_collector varchar(50) not null,
collector-> start datetime not null,
collector-> end_period datetime not null,
collector-> dead_time_rel float4 not null,
collector-> src_name varchar(50) not null,
collector-> dst_name varchar(50) not null,
collector-> bytes int4 not null,
collector-> packets int4 not null);
CREATE
collector=> insert into
flow_sums(primary_collector,start,end_period,dead_time_rel,src_name,dst_name
,bytes,packets)
collector-> select
primary_collector,start,end_period,dead_time_rel,src_name,dst_name,bytes,pac
kets
collector-> from flow_sums_200002;
INSERT 0 3198588
collector=> create unique index flow_sums_pk on
flow_sums(primary_collector,start, src_name, dst_name);
CREATE
collector=>
collector=> select sum(bytes) from flow_sums;
sum
-----------
-1712976144
(1 row)
collector=> select sum(int8(bytes)) from flow_sums;
sum
------------
603877412592
(1 row)

collector=>

So my views will retype attributes in sums to int8 and they will use
function date to round datetime attribute.

Thank You very much and please forward this mail to
pgsql-hackers@postgreSQL.org becouse the robot will bounce my CC.

Thanks, V. Benes

-----P���vodn��� zpr���va-----
Od: Tom Lane <tgl@sss.pgh.pa.us>
Komu: Vladim���r Bene��� <Vladimir.Benes@pvt.cz>
Kopie: pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>;
M���hlpachr Michal <michalm@pvt.net>
Datum: 24. ���nora 2000 6:46
P���edm���t: Re: [HACKERS] Out of memory problem (forwarded bug report)

Vladimir,
Thanks for the details. I think you are undoubtedly running into
expression evaluation memory leaks. Basically, any expression that
yields a non-pass-by-value data type consumes memory that is not
reclaimed until end of statement --- so when you process a few million
rows, that memory starts to add up. (Yes, I realize this is a horrible
misfeature. It's on our TO-DO list to fix it, but it probably won't
happen until 7.1 or 7.2.) In the meantime the best I can offer you
is workarounds.

I think the major problems here are coming from the
"date_trunc('day',start)" calculation (because its datetime result is
pass-by-reference) and to a lesser extent from the sum(bytes)
calculation (because int8 is pass-by-reference). You could easily
replace "date_trunc('day',start)" with "date(start)"; since date is
a pass-by-value type, that won't leak memory, and it should give
equivalent results. The int8 sum is not quite so easy to fix.
I assume you can't get away with switching to int4 --- probably
your sum would overflow an int4? It may be that just fixing the
inefficient date_trunc calc will reduce your memory requirements
enough to get by. If not, the only good news I have is that release
7.0 does fix the memory-leak problem for internal calculations of
aggregate functions like sum(). You can get the first beta release
for 7.0 now.

regards, tom lane

"=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir.Benes@pvt.cz> writes:

-----P���vodn��� zpr���va-----
Od: Tom Lane <tgl@sss.pgh.pa.us>
Komu: Oliver Elphick <olly@lfix.co.uk>
Kopie: pgsql-hackers@postgreSQL.org <pgsql-hackers@postgreSQL.org>;
Vladimir.Benes@pvt.cz <Vladimir.Benes@pvt.cz>
Datum: 22. ���nora 2000 18:06
P���edm���t: Re: [HACKERS] Out of memory problem (forwarded bug report)

"Oliver Elphick" <olly@lfix.co.uk> writes:

Can someone advise, please, how to deal with this problem in 6.5.3?

My guess is that the cause is memory leaks during expression evaluation;
but without seeing the complete view definitions and underlying table
definitions, it's impossible to know what processing is being invoked
by this query...

regards, tom lane

Well, I will append views and underlying table definition:

1) Once again - failure query:
select comm_type,name,tot_bytes,tot_packets
from flow_sums_days_send_200002_view
where day='2000-02-21' and name not like '@%'
union all
select comm_type,name,tot_bytes,tot_packets
from flow_sums_days_receive_200002_view
where day='2000-02-21' and name not like '@%'

2) views definition:
create view flow_sums_days_send_200002_view as
select
'send'::varchar as comm_type, date_trunc('day',start) as day,
src_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, src_name

create view flow_sums_days_receive_200002_view as
select
'receive'::varchar as comm_type, date_trunc('day',start) as day,
dst_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, dst_name

I wanted create only one usefull view:

create view flow_sums_days_200002_view as
select
'send'::varchar as comm_type, date_trunc('day',start) as day,
src_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, src_name
UNION ALL
select
'receive'::varchar as comm_type, date_trunc('day',start) as day,
dst_name as name, sum(bytes) as tot_bytes, sum(packets) as tot_packets
from flow_sums_200002
group by day, dst_name

...but Postgres cann't use clause UNION ALL at view definition. So I

created

two views mentioned above and I wanted use this ones with UNION ALL

clause

only.

3) underlaying table definition:
create table flow_sums_200002 (
primary_collector varchar(50) not null,
start datetime not null,
end_period datetime not null,
dead_time_rel float4 not null,
src_name varchar(50) not null,
dst_name varchar(50) not null,
bytes int8 not null,
packets int4 not null
)

Today this table has about 3 000 000 rows and the select command
mentioned above returns 190 + 255 rows.

Now I don't use clause "UNION ALL" and the program executes two

queryes

and then adds both result to new result. I reduced time increment of

number

rows to flow_sums_200002 table (three times less). This table contains

data

of February 2000 and the program will create table flow_sums_200003 with
relevant views next month.
Well, now this solution solve my problem but always depends on number

of

rows - I only moved limit of rows count.

Thank You, V. Benes

P.S.: I append part of top on my system while the query is running:

CPU states: 98.6% user, 1.3% system, 0.0% nice, 0.0% idle
Mem: 127256K av, 124316K used, 2940K free, 29812K shrd, 2620K buff
Swap: 128516K av, 51036K used, 77480K free 7560K

cached

PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU %MEM TIME

COMMAND

2942 postgres 20 0 141M 99M 17348 R 0 99.0 80.4 1:22

postmaster

=> postmaster later took 80 - 95% of memory, free memory decressed to 2

MB,

CPU was overloaded (0% idle and 99% by user process of postmaster). Have

You

ever seen something similar :-) ?

From bouncefilter Thu Feb 24 07:47:56 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id HAA88442
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 07:47:43 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id NAA07633;
Thu, 24 Feb 2000 13:47:38 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id NAA17465;
Thu, 24 Feb 2000 13:47:37 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 13:47:36 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Rolf Grossmann <grossman@securitas.net>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <200002231430.PAA55935@blue.securitas.net>
Message-ID: <Pine.GSO.4.02A.10002241344100.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Well, don't I look stupid here. Once upon a time I recall to have fixed
exactly this issue, apparently it snuck back in.

If you run psql in non-interactive mode the psqlrc file shouldn't be read
at all. Unless people want that flag, but I don't like that better.

Perhaps this is a good time to ask when and how any fix to this should be
applied.

On Wed, 23 Feb 2000, Rolf Grossmann wrote:

There is another problem with the regression tests: If the user running the
tests has a .psqlrc file all regression tests fail, because commands from
that file are echoed to the result file. Additionally, it a transaction
is started from that file, regression tests fail, because they include tests
for error cases and a transaction needs to be aborted after an error.

A possible solution would probably be to add a flag to psql that inhibits
reading the .psqlrc file and using that flag with the regression tests.

On a related note (not a bug of course ;))... would it be possible to add
some option to psql (or even libpq?) to always keep a transaction active?

Bye, Rolf

************

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 07:51:56 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id HAA92152
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 07:51:49 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id NAA07992;
Thu, 24 Feb 2000 13:51:42 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id NAA17469;
Thu, 24 Feb 2000 13:51:41 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 13:51:40 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: GNU make (Re: [HACKERS] Re: [PATCHES] Patch for more readable
parse error messages)
In-Reply-To: <4553.951323410@sss.pgh.pa.us>
Message-ID: <Pine.GSO.4.02A.10002241348580.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 23 Feb 2000, Tom Lane wrote:

Peter Eisentraut <e99re41@DoCS.UU.SE> writes:

I challenge everyone to show me a make without VPATH. In fact, show me two
makes without a feature that you can't live without, and I shall forever
hold my peace.

Out of the four systems I have easy access to: HPUX 10, HPUX 9, Linux
(some fairly old RedHat version), and SunOS 4.1.4, two have makes
without VPATH

You win. ;)

I surveyed several machines as well (Solaris, IRIX, FreeBSD, HPUX) which
all had this feature. I feel better now with actual data points, I hope
that's fair enough.

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 08:05:57 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA94160
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 08:05:04 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id OAA08976;
Thu, 24 Feb 2000 14:04:54 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id OAA17482;
Thu, 24 Feb 2000 14:04:52 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 14:04:52 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Changes in 7.0
In-Reply-To: <200002232012.PAA06498@candle.pha.pa.us>
Message-ID: <Pine.GSO.4.02A.10002241400260.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 23 Feb 2000, Bruce Momjian wrote:

Here is my list of 7.0 changes. Please let me know of any changes I
should make to it.

Allow ^C to cancel COPY command (Massimo)

That's cool, but if you look closely, psql doesn't do that (anymore). :(
Is it safe to send PQcancelRequest in a copy state and then just forget
about it? What's the correct behaviour? With everyone requesting longjmp's
at the last minute, I had to disable ^C during COPY.

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

There is another problem with the regression tests: If the user running the
tests has a .psqlrc file all regression tests fail, because commands from
that file are echoed to the result file. Additionally, it a transaction
is started from that file, regression tests fail, because they include tests
for error cases and a transaction needs to be aborted after an error.

Incidentally, this should also be the behaviour of the old psql, so it
shouldn't be all that surprising. Will be fixed of course, though.

On a related note (not a bug of course ;))... would it be possible to add
some option to psql (or even libpq?) to always keep a transaction active?

The backend would be the right place for this, and yes, it's possible, but
there seems to be some disagreement whether we should do it.

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 08:28:57 2000
Received: from marvin.muc.de (marvin.muc.de [193.149.48.2])
by hub.org (8.9.3/8.9.3) with SMTP id IAA96054
for <hackers@postgresql.org>; Thu, 24 Feb 2000 08:28:22 -0500 (EST)
(envelope-from
moderators-muc-lists-postgres-hackers-owner@moderators.muc.de)
Received: (qmail 65611 invoked by alias); 24 Feb 2000 13:27:22 -0000
Delivered-To: moderators-muc-lists-postgres-hackers@moderators.muc.de
Received: (qmail 65606 invoked from network); 24 Feb 2000 13:27:22 -0000
Received: from mailout00.sul.t-online.de (194.25.134.16)
by marvin.muc.de with SMTP; 24 Feb 2000 13:27:22 -0000
Received: from imh00.btx.dtag.de by mailout00.sul.t-online.de with smtp
id 12NyIz-0004kT-00; Thu, 24 Feb 2000 14:27:45 +0100
Received: from news05.btx.dtag.de by imh00.btx.dtag.de
with esmtp id 12NyIz-0005Nw-00; Thu, 24 Feb 2000 14:27:45 +0100
Received: from news by news05.btx.dtag.de
with local id 12NyIy-0006Ul-00; Thu, 24 Feb 2000 14:27:44 +0100
To: muc-lists-postgres-hackers@moderators.muc.de
Path: news.btx.dtag.de!not-for-mail
From: Sebastian Fack <Sebastian.Fack@t-online.de>
Newsgroups: muc.lists.postgres.hackers
Subject: Hacker Pages Fnet
Date: Thu, 24 Feb 2000 14:27:42 +0100
Organization: T-Online
Lines: 7
Message-ID: <38B531CE.51CCCB1A@t-online.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
X-Trace: news05.btx.dtag.de 951398864 24962 320037169611-0002 000224 13:27:44
X-Complaints-To: abuse@t-online.de
X-Sender: 320037169611-0002@t-dialin.net
X-Mailer: Mozilla 4.7 [de] (X11; I; Linux 2.2.13 i586)
X-Accept-Language: en
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id IAA96097

Hi all

searching for good Hacker Pages and a Net called Freak net. I���m not sure
if it really exists!

Bye

Hi,

on Thu, 24 Feb 2000 13:47:36 +0100 (MET) Peter Eisentraut wrote
concerning "Re: [BUGS] First experiences with Postgresql 7.0" something like this:

Well, don't I look stupid here. Once upon a time I recall to have fixed
exactly this issue, apparently it snuck back in.

If you run psql in non-interactive mode the psqlrc file shouldn't be read
at all. Unless people want that flag, but I don't like that better.

After doing some more experimenting, I noticed that psql does (indeed)
not read the psqlrc file when given the -f option. Alas, the regression
tests don't use -f but send the file in via stdio. So I think this
behaviour is The Right Thing, but the regression tests should be fixed
(probably to use -f).

Bye, Rolf

On Thu, 24 Feb 2000, Rolf Grossmann wrote:

not read the psqlrc file when given the -f option. Alas, the regression
tests don't use -f but send the file in via stdio. So I think this
behaviour is The Right Thing, but the regression tests should be fixed
(probably to use -f).

But the output of "-f" vs "<" differs, in particular "-f" gives you error
messages like
psql:inputfile:lineno: ERROR: ...

and I believe no one wants to fix up the regression tests in that
direction, after we already did it once.

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 09:47:01 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA03280
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 09:46:25 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id GAA21668;
Thu, 24 Feb 2000 06:44:57 -0800 (PST)
Message-Id: <3.0.1.32.20000224061920.01715af0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Thu, 24 Feb 2000 06:19:20 -0800
To: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>,
"'Tom Lane'" <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: AW: AW: [HACKERS] TRANSACTIONS
Cc: "'hackers'" <pgsql-hackers@postgreSQL.org>
In-Reply-To: <219F68D65015D011A8E000006F8590C604AF7CF3@sdexcsrv1.f000.d0
188.sd.spardat.at>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 10:04 AM 2/24/00 +0100, Zeugswetter Andreas SB wrote:

In this sense a commit is not partial. The commit should commit
all statements that were not in error.

That interpretation eliminates an absolutely essential capability
(all-or-none behavior) in favor of what strikes me as a very minor
programming shortcut.

The all-or-none behavior is what you get if you simply do a rollback
on any error or warning. I don't see a special programming difficulty here.

Unfortunately (for the current implementation of Postgres) I've come
to the conclusion that this is indeed what standard SQL specifies.

It is up to the application or user to rollback the entire transaction
if that's the behavior that's desired.

Of course the whole concept of an explicit "begin" is non-standard,
too. In SQL you're always in a transaction, commit and rollback
terminate transactions and start a new one. Oracle, at least, provides
a "autocommit" mode (which works like Postgres when you're outside a
begin/commit block).

I suspect that most applications don't notice the difference. Most
will catch errors and roll back the current transaction, because that's
the logical thing to do in most cases.

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Thu Feb 24 09:26:59 2000
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA00875
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 09:26:20 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
JAA16060;
Thu, 24 Feb 2000 09:22:42 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241422.JAA16060@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <Pine.GSO.4.02A.10002241344100.17421-100000@Hummer.DoCS.UU.SE>
from Peter Eisentraut at "Feb 24, 2000 01:47:36 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 24 Feb 2000 09:22:42 -0500 (EST)
CC: Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Well, don't I look stupid here. Once upon a time I recall to have fixed
exactly this issue, apparently it snuck back in.

If you run psql in non-interactive mode the psqlrc file shouldn't be read
at all. Unless people want that flag, but I don't like that better.

Perhaps this is a good time to ask when and how any fix to this should be
applied.

I see the same problem here. Also, the regression tests required me to
define PGLIB.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 09:26:59 2000
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA00835
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 09:26:08 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
JAA16070;
Thu, 24 Feb 2000 09:23:52 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241423.JAA16070@candle.pha.pa.us>
Subject: Re: [HACKERS] Changes in 7.0
In-Reply-To: <Pine.GSO.4.02A.10002241400260.17421-100000@Hummer.DoCS.UU.SE>
from Peter Eisentraut at "Feb 24, 2000 02:04:52 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 24 Feb 2000 09:23:52 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Wed, 23 Feb 2000, Bruce Momjian wrote:

Here is my list of 7.0 changes. Please let me know of any changes I
should make to it.

Allow ^C to cancel COPY command (Massimo)

That's cool, but if you look closely, psql doesn't do that (anymore). :(
Is it safe to send PQcancelRequest in a copy state and then just forget
about it? What's the correct behaviour? With everyone requesting longjmp's
at the last minute, I had to disable ^C during COPY.

I assume it was during COPY and not \copy.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Hi,

on Thu, 24 Feb 2000 13:47:36 +0100 (MET) Peter Eisentraut wrote
concerning "Re: [BUGS] First experiences with Postgresql 7.0" something like this:

Well, don't I look stupid here. Once upon a time I recall to have fixed
exactly this issue, apparently it snuck back in.

If you run psql in non-interactive mode the psqlrc file shouldn't be read
at all. Unless people want that flag, but I don't like that better.

After doing some more experimenting, I noticed that psql does (indeed)
not read the psqlrc file when given the -f option. Alas, the regression
tests don't use -f but send the file in via stdio. So I think this
behaviour is The Right Thing, but the regression tests should be fixed
(probably to use -f).

But is it right to not read the psqlrc file with -f? Can psqlrc be
read but not displayed with -q. regress.sh uses -a and -q, which seem
to conflict with each other.

-a Echo all input from script
-q Run quietly (no messages, only query output)

I will admit regress.sh may be using the wrong flags now. Also, PGLIB
is used by createlang. Not sure how it used to work.

CREATE DATABASE
=============== installing PL/pgSQL... =================
createlang: missing required argument PGLIB directory
(This is the directory where the interpreter for the procedural
language is stored. Traditionally, these are installed in whatever
'lib' directory was specified at configure time.)
createlang failed

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 09:53:59 2000
Received: from sirius.edu.sollentuna.se (sirius.edu.sollentuna.se
[195.67.128.12]) by hub.org (8.9.3/8.9.3) with ESMTP id JAA03928
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 09:53:21 -0500 (EST)
(envelope-from mha@sollentuna.net)
Received: by sirius.edu.sollentuna.se with Internet Mail Service (5.5.2448.0)
id <WJ7XVNX4>; Thu, 24 Feb 2000 15:53:15 +0100
Message-ID:
<215896B6B5E1CF11BC5600805FFEA82103045BF7@sirius.edu.sollentuna.se>
From: Magnus Hagander <mha@sollentuna.net>
To: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
Subject: Minor problems reloading dump in 7.0beta1
Date: Thu, 24 Feb 2000 15:53:08 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

Hi!

When upgrading a server from version 6.5.2 to 7.0beta1, we hit the following
problems when reloading the data from pg_dumpall:

- Is it a "known feature" that when using "psql -f pgfull.dump" it aborts on
the "\connect" lines from pg_dumpall? The dump works fine if I start psql
and use \i to load it. (I reliase the documentation says use "psql <
filename", but I didn't read that far :-)

- It fails to reload datetime indexes, because of "DefineIndex: datetime_ops
class not found". I take it this is because of the new datetime stuff, but
it should at least be put into some notes somewhere that you have to tweak
the pg_dump output for these.

- It also failed to reload a view, but that was because the old pg_dump
dumped it as both a table and a view. Nothing wrong with 7.0 there.

Apart from these very minor issues, the reload went just fine. Just figured
that these small things should perhaps go into some notices somewhere.

//Magnus

From bouncefilter Thu Feb 24 10:12:58 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA05827
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:12:19 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id QAA21228;
Thu, 24 Feb 2000 16:12:07 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id QAA17835;
Thu, 24 Feb 2000 16:12:05 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 16:12:04 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Magnus Hagander <mha@sollentuna.net>
cc: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Minor problems reloading dump in 7.0beta1
In-Reply-To:
<215896B6B5E1CF11BC5600805FFEA82103045BF7@sirius.edu.sollentuna.se>
Message-ID: <Pine.GSO.4.02A.10002241608250.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 24 Feb 2000, Magnus Hagander wrote:

When upgrading a server from version 6.5.2 to 7.0beta1, we hit the following
problems when reloading the data from pg_dumpall:

- Is it a "known feature" that when using "psql -f pgfull.dump" it aborts on
the "\connect" lines from pg_dumpall?

No. It will abort if it cannot actually perform the connect, however ...

The dump works fine if I start psql and use \i to load it. (I reliase
the documentation says use "psql < filename", but I didn't read that
far :-)

... makes this look like a really weird bug. What's the connect statement,
and what's the error message? (It better not abort without one. ;)

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 10:29:01 2000
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA07730
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:28:49 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
KAA17550;
Thu, 24 Feb 2000 10:18:35 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241518.KAA17550@candle.pha.pa.us>
Subject: Re: [HACKERS] Minor problems reloading dump in 7.0beta1
In-Reply-To:
<215896B6B5E1CF11BC5600805FFEA82103045BF7@sirius.edu.sollentuna.se>
from Magnus Hagander at "Feb 24, 2000 03:53:08 pm"
To: Magnus Hagander <mha@sollentuna.net>
Date: Thu, 24 Feb 2000 10:18:35 -0500 (EST)
CC: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Hi!

When upgrading a server from version 6.5.2 to 7.0beta1, we hit the following
problems when reloading the data from pg_dumpall:

- Is it a "known feature" that when using "psql -f pgfull.dump" it aborts on
the "\connect" lines from pg_dumpall? The dump works fine if I start psql
and use \i to load it. (I reliase the documentation says use "psql <
filename", but I didn't read that far :-)

This is a new psql feature that scripts exit on first failure, I think.

- It fails to reload datetime indexes, because of "DefineIndex: datetime_ops
class not found". I take it this is because of the new datetime stuff, but
it should at least be put into some notes somewhere that you have to tweak
the pg_dump output for these.

We need to fix that somehow.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 10:25:52 2000
Received: from alert.infoplease.com ([208.222.166.25])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA07248
for <hackers@postgreSQL.org>; Thu, 24 Feb 2000 10:24:03 -0500 (EST)
(envelope-from kdebisschop@range.infoplease.com)
Received: from skillet.infoplease.com (skillet [10.0.1.212])
by alert.infoplease.com (8.9.3+Sun/8.9.3) with ESMTP id KAA04288;
Thu, 24 Feb 2000 10:23:23 -0500 (EST)
Received: (from kdebisschop@localhost)
by skillet.infoplease.com (8.9.3/8.9.1) id KAA02174;
Thu, 24 Feb 2000 10:23:23 -0500
Date: Thu, 24 Feb 2000 10:23:23 -0500
Message-Id: <200002241523.KAA02174@skillet.infoplease.com>
X-Authentication-Warning: skillet.infoplease.com: kdebisschop set sender to
kdebisschop@spaceheater.infoplease.com using -f
From: Karl DeBisschop <kdebisschop@range.infoplease.com>
To: oleg@sai.msu.su
CC: ZeugswetterA@wien.spardat.at, hackers@postgreSQL.org
In-reply-to: <Pine.GSO.3.96.SK.1000224134525.22930M-100000@ra> (message from
Oleg Bartunov on Thu, 24 Feb 2000 13:46:38 +0300 (GMT))
Subject: Re: AW: [GENERAL] Re: [HACKERS] TRANSACTIONS
Reply-to: kdebisschop@range.infoplease.com
References: <Pine.GSO.3.96.SK.1000224134525.22930M-100000@ra>

Here is my example:

package Apache::Hits;

use strict;
use Apache::Constants qw(:common);

my ($sth_lock, $sth_upd, $sth_ins );

sub handler {
my $r = shift;
if ( $r->args() =~ /msg_id=(\d+)/o ) {
$HTML::Mason::Commands::dbs ||= My::DB->new(1);
my $dbh = $HTML::Mason::Commands::dbs->{dbh};
$sth_lock ||= $dbh->prepare("LOCK TABLE hits IN SHARE ROW EXCLUSIVE MODE");
$sth_upd ||= $dbh->prepare("UPDATE hits SET count=count+1,last_access=now() WHERE msg_id=?");
$sth_ins ||= $dbh->prepare("INSERT INTO hits (msg_id,count) VALUES (?, 1)");
$dbh->{AutoCommit} = 0;
my $success = 1;
$success &&= $sth_lock->execute();
$success &&= $sth_upd->execute( $1 );
$success &&= $sth_ins->execute( $1 ) if ( $success eq '0E0');
my $result = ($success ? $dbh->commit : $dbh->rollback);
unless ( $result ) {
$r->log_error("Unable to process transaction: ". $dbh->errstr ."\n");
}
}
return OK;
}

1;
__END__

Maybe I'm missing something - I don't use the Apache module, nor the
mason module, so it's difficult for me to decode this. But I looks to
me like this commits or rolls back one statement at a time.

What I want to be able to do is run a multi-statement transaction, and
for each statement in the transaction try to fix it if an error is
generated. If I cannot fix it, I want to roll back all of the
previous statements in the transaction. If I can fix it, I want to
clear the error status and continue on to the next statement in the
transaction.

Does your counter example do this in some way that I am not seeing?

--
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net

From bouncefilter Thu Feb 24 10:29:59 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA07756
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:29:02 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA21573;
Thu, 24 Feb 2000 10:28:47 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-reply-to: <Pine.GSO.4.02A.10002241344100.17421-100000@Hummer.DoCS.UU.SE>
References: <Pine.GSO.4.02A.10002241344100.17421-100000@Hummer.DoCS.UU.SE>
Comments: In-reply-to Peter Eisentraut <e99re41@DoCS.UU.SE>
message dated "Thu, 24 Feb 2000 13:47:36 +0100"
Date: Thu, 24 Feb 2000 10:28:47 -0500
Message-ID: <21570.951406127@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <e99re41@DoCS.UU.SE> writes:

If you run psql in non-interactive mode the psqlrc file shouldn't be read
at all. Unless people want that flag, but I don't like that better.

Perhaps this is a good time to ask when and how any fix to this should be
applied.

This is arguably a bug fix, so you needn't worry about it being beta
phase. However, there seems to be some doubt about exactly how it
*should* work, so you should hold off until there is consensus.

I take it you are considering "only read psqlrc if stdin is a tty",
rather than providing a switch-selectable choice. I think that might
be too inflexible. The regression tests clearly need to be able to
disregard psqlrc, but ordinary users will very likely want to write
scripts that depend on their psqlrc. (For sure, we will get bug reports
"this works by hand but not in a script" that trace back to psqlrc
settings or lack of 'em.)

Using -f would work if you hadn't already overloaded it with another
meaning; but as you say I don't much want to add line numbers to all
the regress test expected outputs. (That would mean that
adding/deleting lines in a test would create many bogus differences
further down in its output, which would be a pain in the neck for the
initial hand-validation of the changed output.)

So I vote for a switch that suppresses reading psqlrc ...

regards, tom lane

From bouncefilter Thu Feb 24 10:35:58 2000
Received: from sirius.edu.sollentuna.se (sirius.edu.sollentuna.se
[195.67.128.12]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA08566
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 10:35:14 -0500 (EST)
(envelope-from mha@sollentuna.net)
Received: by sirius.edu.sollentuna.se with Internet Mail Service (5.5.2448.0)
id <WJ7XVN64>; Thu, 24 Feb 2000 16:35:08 +0100
Message-ID:
<215896B6B5E1CF11BC5600805FFEA82103045BFA@sirius.edu.sollentuna.se>
From: Magnus Hagander <mha@sollentuna.net>
To: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
Subject: 7.0beta1 on Win32
Date: Thu, 24 Feb 2000 16:35:07 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: multipart/mixed;
boundary="----_=_NextPart_000_01BF7EDC.BB05B4B0"

This message is in MIME format. Since your mail reader does not understand
this format, some or all of this message may not be legible.

------_=_NextPart_000_01BF7EDC.BB05B4B0
Content-Type: text/plain;
charset="iso-8859-1"

Hi!

The following is required in order to make beta1 compile on the Win32
platform (psql and libpq):

The file "config.h.win32" in the include\ directory (from my patch from
2000-01-18) is missing from the tree. It needs to be put back :-)

The following patch has to be applied in the interfaces\libpq directory.

//Magnus

<<beta_win32.patch>>

------_=_NextPart_000_01BF7EDC.BB05B4B0
Content-Type: application/octet-stream;
name="beta_win32.patch"
Content-Transfer-Encoding: quoted-printable
Content-Disposition: attachment;
filename="beta_win32.patch"

*** ./fe-connect.c.orig	Thu Feb 24 16:19:26 2000=0A=
--- ./fe-connect.c	Thu Feb 24 16:19:21 2000=0A=
***************=0A=
*** 592,597 ****=0A=
--- 592,598 ----=0A=
  #ifndef WIN32
  	if (fcntl(conn->sock, F_SETFL, O_NONBLOCK) < 0)
  #else
+ 	int on =3D 1;
  	if (ioctlsocket(conn->sock, FIONBIO, &on) !=3D 0)
  #endif
  	{
*** ./libpqdll.def.orig	Thu Feb 24 16:19:27 2000=0A=
--- ./libpqdll.def	Thu Feb 24 16:19:23 2000=0A=
***************=0A=
*** 72,78 ****=0A=
  	initPQExpBuffer		@ 69
  	resetPQExpBuffer	@ 70
  	PQoidValue		@ 71
! 	PQclientencoding	@ 72
  	PQenv2encoding		@ 73
  	appendBinaryPQExpBuffer	@ 74
  	appendPQExpBufferStr	@ 75
--- 72,78 ----=0A=
  	initPQExpBuffer		@ 69
  	resetPQExpBuffer	@ 70
  	PQoidValue		@ 71
! 	PQclientEncoding	@ 72
  	PQenv2encoding		@ 73
  	appendBinaryPQExpBuffer	@ 74
  	appendPQExpBufferStr	@ 75
*** ./win32.h.orig	Thu Feb 24 16:19:27 2000=0A=
--- ./win32.h	Thu Feb 24 16:19:24 2000=0A=
***************=0A=
*** 4,9 ****=0A=
--- 4,10 ----=0A=
   * strcasecmp() is not in Windows, stricmp is, though
   */
  #define strcasecmp(a,b) stricmp(a,b)
+ #define strncasecmp(a,b,c) _strnicmp(a,b,c)
 =20
  #define SOCKET_SIZE_TYPE int
 =20
***************=0A=
*** 16,21 ****=0A=
--- 17,23 ----=0A=
  #define write(a,b,c) _write(a,b,c)
  #define popen(a,b) _popen(a,b)
  #define pclose(a) _pclose(a)
+ #define vsnprintf(a,b,c,d) _vsnprintf(a,b,c,d)
 =20
  /*
   * crypt not available (yet)

------_=_NextPart_000_01BF7EDC.BB05B4B0--

From bouncefilter Thu Feb 24 10:42:15 2000
Received: from sirius.edu.sollentuna.se (sirius.edu.sollentuna.se
[195.67.128.12]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA09924
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:40:42 -0500 (EST)
(envelope-from mha@sollentuna.net)
Received: by sirius.edu.sollentuna.se with Internet Mail Service (5.5.2448.0)
id <WJ7XVN7J>; Thu, 24 Feb 2000 16:40:33 +0100
Message-ID:
<215896B6B5E1CF11BC5600805FFEA82103045BFD@sirius.edu.sollentuna.se>
From: Magnus Hagander <mha@sollentuna.net>
To: "'Peter Eisentraut'" <peter_e@gmx.net>
Cc: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Minor problems reloading dump in 7.0beta1
Date: Thu, 24 Feb 2000 16:40:32 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="windows-1252"

When upgrading a server from version 6.5.2 to 7.0beta1, we

hit the following

problems when reloading the data from pg_dumpall:

- Is it a "known feature" that when using "psql -f

pgfull.dump" it aborts on

the "\connect" lines from pg_dumpall?

No. It will abort if it cannot actually perform the connect,
however ...

That's expected. But I could connect manually.

The dump works fine if I start psql and use \i to load it.

(I reliase

the documentation says use "psql < filename", but I didn't read that
far :-)

... makes this look like a really weird bug. What's the
connect statement,
and what's the error message? (It better not abort without one. ;)

First it was:
\connect template1

Noticing it was not like the statements further down in the file, I changed
it to:
\connect template1 postgres

It dies with:
psql: FATAL 1: Database "postgres" does not exist in the system catalog.

Same error message in both cases.

//Magnus

From bouncefilter Thu Feb 24 10:46:59 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA10891
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:46:14 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA21688;
Thu, 24 Feb 2000 10:42:04 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Changes in 7.0
In-reply-to: <Pine.GSO.4.02A.10002241400260.17421-100000@Hummer.DoCS.UU.SE>
References: <Pine.GSO.4.02A.10002241400260.17421-100000@Hummer.DoCS.UU.SE>
Comments: In-reply-to Peter Eisentraut <e99re41@DoCS.UU.SE>
message dated "Thu, 24 Feb 2000 14:04:52 +0100"
Date: Thu, 24 Feb 2000 10:42:03 -0500
Message-ID: <21685.951406923@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <e99re41@DoCS.UU.SE> writes:

Allow ^C to cancel COPY command (Massimo)

That's cool, but if you look closely, psql doesn't do that (anymore). :(
Is it safe to send PQcancelRequest in a copy state and then just forget
about it? What's the correct behaviour?

For a COPY OUT (from the backend), the correct behavior is same as for
non-copy state: fire off the cancel request and then forget about it.
If the backend decides to honor the request then it will terminate the
copy in the usual way. For a COPY IN, it's up to you to stop sending
data...

regards, tom lane

From bouncefilter Thu Feb 24 10:51:59 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA11348
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:49:17 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
KAA18408;
Thu, 24 Feb 2000 10:44:57 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241544.KAA18408@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <21570.951406127@sss.pgh.pa.us> from Tom Lane at "Feb 24,
2000 10:28:47 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 10:44:57 -0500 (EST)
CC: Peter Eisentraut <peter_e@gmx.net>,
Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Using -f would work if you hadn't already overloaded it with another
meaning; but as you say I don't much want to add line numbers to all
the regress test expected outputs. (That would mean that
adding/deleting lines in a test would create many bogus differences
further down in its output, which would be a pain in the neck for the
initial hand-validation of the changed output.)

So I vote for a switch that suppresses reading psqlrc ...

Yes, but are there cases where we would want psqlrc values set? Should
we specifically set all the variables ourselves on startup, just
over-riding what is in psqlrc?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 10:45:58 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA10770
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:45:09 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA21725;
Thu, 24 Feb 2000 10:45:01 -0500 (EST)
To: "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir.Benes@pvt.cz>
cc: pgsql-hackers@postgreSQL.org,
"=?iso-8859-2?Q?M=FChlpachr_Michal?=" <michalm@pvt.net>
Subject: Re: [HACKERS] Out of memory problem (forwarded bug report)
In-reply-to: <010101bf7ebf$888368f0$451c11ac@p53apk.chv.pvt.cz>
References: <010101bf7ebf$888368f0$451c11ac@p53apk.chv.pvt.cz>
Comments: In-reply-to "=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?="
<Vladimir.Benes@pvt.cz>
message dated "Thu, 24 Feb 2000 13:06:08 +0100"
Date: Thu, 24 Feb 2000 10:45:01 -0500
Message-ID: <21722.951407101@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"=?iso-8859-2?B?VmxhZGlt7XIgQmVuZbk=?=" <Vladimir.Benes@pvt.cz> writes:

collector=> select sum(int8(bytes)) from flow_sums;

That will not help, because you're still invoking the int8 flavor of
sum(). Might as well leave it alone and update to 7.0 beta.

regards, tom lane

From bouncefilter Thu Feb 24 10:52:58 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA11638
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 10:52:19 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
KAA18752;
Thu, 24 Feb 2000 10:49:16 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241549.KAA18752@candle.pha.pa.us>
Subject: Re: [HACKERS] 7.0beta1 on Win32
In-Reply-To:
<215896B6B5E1CF11BC5600805FFEA82103045BFA@sirius.edu.sollentuna.se>
from Magnus Hagander at "Feb 24, 2000 04:35:07 pm"
To: Magnus Hagander <mha@sollentuna.net>
Date: Thu, 24 Feb 2000 10:49:16 -0500 (EST)
CC: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Applied.

[Charset iso-8859-1 unsupported, filtering to ASCII...]

Hi!

The following is required in order to make beta1 compile on the Win32
platform (psql and libpq):

The file "config.h.win32" in the include\ directory (from my patch from
2000-01-18) is missing from the tree. It needs to be put back :-)

The following patch has to be applied in the interfaces\libpq directory.

//Magnus

<<beta_win32.patch>>

[Attachment, skipping...]

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 10:55:59 2000
Received: from sirius.edu.sollentuna.se (sirius.edu.sollentuna.se
[195.67.128.12]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA12288
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:55:41 -0500 (EST)
(envelope-from mha@sollentuna.net)
Received: by sirius.edu.sollentuna.se with Internet Mail Service (5.5.2448.0)
id <WJ7XVN8S>; Thu, 24 Feb 2000 16:55:40 +0100
Message-ID:
<215896B6B5E1CF11BC5600805FFEA82103045C00@sirius.edu.sollentuna.se>
From: Magnus Hagander <mha@sollentuna.net>
To: Magnus Hagander <mha@sollentuna.net>, "'Peter Eisentraut'"
<peter_e@gmx.net>
Cc: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Minor problems reloading dump in 7.0beta1
Date: Thu, 24 Feb 2000 16:55:37 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="windows-1252"

The dump works fine if I start psql and use \i to load it.

(I reliase

the documentation says use "psql < filename", but I

didn't read that

far :-)

... makes this look like a really weird bug. What's the
connect statement,
and what's the error message? (It better not abort without one. ;)

First it was:
\connect template1

Noticing it was not like the statements further down in the
file, I changed
it to:
\connect template1 postgres

It dies with:
psql: FATAL 1: Database "postgres" does not exist in the
system catalog.

Same error message in both cases.

Ok. Kill me. I know the problem - user error.
I forgot to enter which database psql should connect to first. So it tried
'postgres', since 'postgres' was the user I was logged in as.
Stupid.

Sorry about this one.

//Magnus

From bouncefilter Thu Feb 24 11:00:59 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA13223
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:00:08 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA21814;
Thu, 24 Feb 2000 10:56:12 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Peter Eisentraut <peter_e@gmx.net>,
Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-reply-to: <200002241544.KAA18408@candle.pha.pa.us>
References: <200002241544.KAA18408@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Thu, 24 Feb 2000 10:44:57 -0500"
Date: Thu, 24 Feb 2000 10:56:12 -0500
Message-ID: <21811.951407772@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Yes, but are there cases where we would want psqlrc values set? Should
we specifically set all the variables ourselves on startup, just
over-riding what is in psqlrc?

No. In the first place, we've already got a dozen or two SET variables
(and the list keeps changing); do you really want to reset all of those
in each regress test? In the second place, a psqlrc script could screw
things up in more creative ways than just issuing SET commands. IIRC,
Rolf's original example was a psqlrc that issued a BEGIN to leave the
system in an open-transaction state. In the third place, the psql echo
output from any commands issued by psqlrc would itself be enough to
cause bogus "failures" of all the tests.

One advantage of using a switch is that if someone *did* want to
experiment with regress test behavior with non-default settings,
he could set up a psqlrc file and then remove that switch from
the regression driver script. Of course he'd have to ignore a
lot of bogus differences...

regards, tom lane

From bouncefilter Thu Feb 24 11:00:00 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA13159
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 10:59:54 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id QAA26188;
Thu, 24 Feb 2000 16:59:51 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id QAA17925;
Thu, 24 Feb 2000 16:59:49 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 16:59:48 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <21570.951406127@sss.pgh.pa.us>
Message-ID: <Pine.GSO.4.02A.10002241646020.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 24 Feb 2000, Tom Lane wrote:

Perhaps this is a good time to ask when and how any fix to this should be
applied.

This is arguably a bug fix, so you needn't worry about it being beta
phase.

I'm not sure how this works now: Do I just commit it to the tree, so it
will be in when, say, beta2 gets generated?

I take it you are considering "only read psqlrc if stdin is a tty",

This is how shells work, that's always my default decision for unchartered
territory. (Of course psql is not a shell, but that's why we're discussing
...)

Using -f would work if you hadn't already overloaded it with another
meaning;

Huh, "-f" is not a new option. "-f" is different from "<" because of two
reasons: 1) if they were the same, we wouldn't need one of them, and 2) a
program should behave the same independent of what kind of device its
standard input comes from. (That's why "<" doesn't print out error
messages with line numbers.) This is an ideal state of course.

[5 min later ...]

Ah, a tcsh user! ;) I could go for an -X option to suppress reading the
startup file, with default being that it is read in any mode. A pretty
dump option letter, but not all that far-fetched.

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 11:11:00 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA14837
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:10:19 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA23055;
Thu, 24 Feb 2000 11:00:49 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241600.LAA23055@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <21811.951407772@sss.pgh.pa.us> from Tom Lane at "Feb 24,
2000 10:56:12 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 11:00:49 -0500 (EST)
CC: Peter Eisentraut <peter_e@gmx.net>,
Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Bruce Momjian <pgman@candle.pha.pa.us> writes:

Yes, but are there cases where we would want psqlrc values set? Should
we specifically set all the variables ourselves on startup, just
over-riding what is in psqlrc?

No. In the first place, we've already got a dozen or two SET variables
(and the list keeps changing); do you really want to reset all of those
in each regress test? In the second place, a psqlrc script could screw
things up in more creative ways than just issuing SET commands. IIRC,
Rolf's original example was a psqlrc that issued a BEGIN to leave the
system in an open-transaction state. In the third place, the psql echo
output from any commands issued by psqlrc would itself be enough to
cause bogus "failures" of all the tests.

Yes, I see. Just asking.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 11:01:59 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA13518
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:01:57 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id RAA26303;
Thu, 24 Feb 2000 17:01:54 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id RAA17931;
Thu, 24 Feb 2000 17:01:52 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 17:01:51 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Changes in 7.0
In-Reply-To: <21685.951406923@sss.pgh.pa.us>
Message-ID: <Pine.GSO.4.02A.10002241700400.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 24 Feb 2000, Tom Lane wrote:

For a COPY OUT (from the backend), the correct behavior is same as for
non-copy state: fire off the cancel request and then forget about it.

Do I have to call PQendcopy() is the question.

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 11:03:59 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA13812
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:03:50 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id RAA26497;
Thu, 24 Feb 2000 17:03:43 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id RAA17935;
Thu, 24 Feb 2000 17:03:41 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 17:03:41 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <200002241422.JAA16060@candle.pha.pa.us>
Message-ID: <Pine.GSO.4.02A.10002241702380.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 24 Feb 2000, Bruce Momjian wrote:

I see the same problem here. Also, the regression tests required me to
define PGLIB.

Is that because of createlang or initdb or both? Which regression driver?

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 11:06:59 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA14214
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 11:06:06 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA21906;
Thu, 24 Feb 2000 11:06:00 -0500 (EST)
To: Magnus Hagander <mha@sollentuna.net>
cc: "'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Minor problems reloading dump in 7.0beta1
In-reply-to:
<215896B6B5E1CF11BC5600805FFEA82103045BF7@sirius.edu.sollentuna.se>
References:
<215896B6B5E1CF11BC5600805FFEA82103045BF7@sirius.edu.sollentuna.se>
Comments: In-reply-to Magnus Hagander <mha@sollentuna.net>
message dated "Thu, 24 Feb 2000 15:53:08 +0100"
Date: Thu, 24 Feb 2000 11:05:59 -0500
Message-ID: <21903.951408359@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Magnus Hagander <mha@sollentuna.net> writes:

- It fails to reload datetime indexes, because of "DefineIndex: datetime_ops
class not found".

Oops. Comparing 6.5 and current pg_opclass contents, I see we are going
to have this trouble with timespan_ops and oid8_ops as well as with
datetime_ops (although very likely there are no user tables with indexes
on oid8? Not certain though).

Bruce, do you want to hack the parser to drop these opclass names the
same way it's dropping network_ops? Or do we need to adopt a cleaner
solution?

regards, tom lane

From bouncefilter Thu Feb 24 11:12:59 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA15181
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:12:17 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA26621;
Thu, 24 Feb 2000 11:08:01 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241608.LAA26621@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <Pine.GSO.4.02A.10002241702380.17421-100000@Hummer.DoCS.UU.SE>
from Peter Eisentraut at "Feb 24, 2000 05:03:41 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 24 Feb 2000 11:08:01 -0500 (EST)
CC: Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Thu, 24 Feb 2000, Bruce Momjian wrote:

I see the same problem here. Also, the regression tests required me to
define PGLIB.

Is that because of createlang or initdb or both? Which regression driver?

See later message. createlang is causing it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 11:12:01 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA15073
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:11:44 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA21956;
Thu, 24 Feb 2000 11:11:37 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-reply-to: <Pine.GSO.4.02A.10002241646020.17421-100000@Hummer.DoCS.UU.SE>
References: <Pine.GSO.4.02A.10002241646020.17421-100000@Hummer.DoCS.UU.SE>
Comments: In-reply-to Peter Eisentraut <e99re41@DoCS.UU.SE>
message dated "Thu, 24 Feb 2000 16:59:48 +0100"
Date: Thu, 24 Feb 2000 11:11:37 -0500
Message-ID: <21953.951408697@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <e99re41@DoCS.UU.SE> writes:

This is arguably a bug fix, so you needn't worry about it being beta
phase.

I'm not sure how this works now: Do I just commit it to the tree, so it
will be in when, say, beta2 gets generated?

Right. No real difference in commit procedures at this point.

At some point after the release, we will set up a branch for REL_7.0,
and after that, ordinary commits will only apply to new development
for 7.1, not to the stable release branch. But no need to worry about
that for now.

Ah, a tcsh user! ;) I could go for an -X option to suppress reading the
startup file, with default being that it is read in any mode. A pretty
dump option letter, but not all that far-fetched.

Works for me...

regards, tom lane

From bouncefilter Thu Feb 24 11:16:59 2000
Received: from www.wgcr.org (IDENT:root@www.wgcr.org [206.74.232.194])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA15760
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 11:15:29 -0500 (EST)
(envelope-from lamar.owen@wgcr.org)
Received: from wgcr.org ([206.74.232.197])
by www.wgcr.org (8.9.3/8.9.3/WGCR) with ESMTP id LAA00646;
Thu, 24 Feb 2000 11:13:52 -0500
Message-ID: <38B558BF.58FE5FC3@wgcr.org>
Date: Thu, 24 Feb 2000 11:13:51 -0500
From: Lamar Owen <lamar.owen@wgcr.org>
Organization: WGCR Internet Radio
X-Mailer: Mozilla 4.61 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Peter Eisentraut <peter_e@gmx.net>
CC: Bruce Momjian <pgman@candle.pha.pa.us>,
Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
References: <Pine.GSO.4.02A.10002241702380.17421-100000@Hummer.DoCS.UU.SE>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Peter Eisentraut wrote:

On Thu, 24 Feb 2000, Bruce Momjian wrote:

I see the same problem here. Also, the regression tests required me to
define PGLIB.

Is that because of createlang or initdb or both? Which regression driver?

Createlang has done this for some time -- at least since I've been
packaging the RPM's with the regression tests. I have had to define
PGLIB in regress.sh -- otherwise, createlang doesn't know where to find
the pl .so. As my 7.0 installation is at home, I can't check the 7.0
regress.sh from here -- however, the 6.5.x regress.sh did it's own PGLIB
definition.

--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

From bouncefilter Thu Feb 24 11:18:59 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA16217
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:18:28 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA21991;
Thu, 24 Feb 2000 11:17:02 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Changes in 7.0
In-reply-to: <Pine.GSO.4.02A.10002241700400.17421-100000@Hummer.DoCS.UU.SE>
References: <Pine.GSO.4.02A.10002241700400.17421-100000@Hummer.DoCS.UU.SE>
Comments: In-reply-to Peter Eisentraut <e99re41@DoCS.UU.SE>
message dated "Thu, 24 Feb 2000 17:01:51 +0100"
Date: Thu, 24 Feb 2000 11:17:02 -0500
Message-ID: <21988.951409022@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <e99re41@DoCS.UU.SE> writes:

On Thu, 24 Feb 2000, Tom Lane wrote:

For a COPY OUT (from the backend), the correct behavior is same as for
non-copy state: fire off the cancel request and then forget about it.

Do I have to call PQendcopy() is the question.

Yes, but only after the backend sends the usual copy termination
message. The cancel request doesn't affect the protocol state machine
nor the app's interaction with libpq in the slightest. It's just a side
communication to the backend ("Psst! I'd really appreciate it if we
could wrap this up sooner rather than later.")

For COPY IN, you want to stop sending data lines and send a terminator,
then PQendcopy() in the usual way.

regards, tom lane

From bouncefilter Thu Feb 24 11:19:01 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA16248
for <pgsql-general@postgreSQL.org>;
Thu, 24 Feb 2000 11:18:36 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Hummer.DoCS.UU.SE (e99re41@Hummer.DoCS.UU.SE [130.238.9.175])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id RAA27668;
Thu, 24 Feb 2000 17:18:26 +0100 (MET)
Received: from localhost (e99re41@localhost) by Hummer.DoCS.UU.SE
(8.6.12/8.6.12) with SMTP id RAA17979;
Thu, 24 Feb 2000 17:18:24 +0100
X-Authentication-Warning: Hummer.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Thu, 24 Feb 2000 17:18:23 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Jose Soares <jose@sferacarta.com>
cc: Wim Ceulemans <wim.ceulemans@nice.be>,
"'general'" <pgsql-general@postgreSQL.org>
Subject: Re: [GENERAL] AW: [HACKERS] TRANSACTIONS
In-Reply-To: <38B4F0C3.C93B78AB@sferacarta.com>
Message-ID: <Pine.GSO.4.02A.10002241713480.17421-100000@Hummer.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 24 Feb 2000, Jose Soares wrote:

NOTICE: (transaction aborted): all queries ignored until end of transaction block

*ABORT STATE*

Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a
COMMIT/ROLLBACK ?

The PostgreSQL transaction paradigm seems to be that if you explicitly
start a transaction, you get to explicitly end it. This is of course at
odds with SQL, but it seems internally consistent to me. I hope that one
of these days we can offer the other behaviour as well.

Why PostgreSQL allows a COMMIT in this case ?

Good question. I assume it doesn't actually commit though, does it? I
think a CHECK_IF_ABORTED (sp?) before calling the commit utility routine
would be appropriate. Anyone?

--
Peter Eisentraut Sernanders vaeg 10:115
peter_e@gmx.net 75262 Uppsala
http://yi.org/peter-e/ Sweden

From bouncefilter Thu Feb 24 11:24:59 2000
Received: from s-mail2.hanmail.net (s-mail1.hanmail.net [211.32.117.61])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA17475
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 11:24:27 -0500 (EST)
(envelope-from teletraco@mixmail.com)
Received: from www3.mixmail.com (www.mixmail.com [211.32.117.93] (may be
forged)) by s-mail2.hanmail.net (8.9.1/8.9.1) with ESMTP id AAA20878;
Fri, 25 Feb 2000 00:23:23 +0900
Received: (from hanadmin@localhost)
by www3.mixmail.com (8.9.1/8.9.1) id BAA19094;
Fri, 25 Feb 2000 01:20:16 +0900 (KST)
From: "Post Message" <teletraco@mixmail.com>
To: pgsql-hackers@postgresql.org
Subject: Solid timer
X-Mailer: Daum Web Mailer 1.0
Date: Thu, 24 Feb 2000 03:20:16 -1300
Message-Id: <20000224172016.HM.a0000000000DA9p@mixmail.com>
Errors-To: teletraco@mixmail.com
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 8bit

Hi,
Help me please.
I need solid edge timer fix, send it please :(
teletaco@mixmail.com
Bye&Txh4all

Tu correo gratis en MixMail http://www.mixmail.com
Inicia tu navegacion en http://www.ya.com

From bouncefilter Thu Feb 24 11:32:00 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA31405
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:31:35 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA26974;
Thu, 24 Feb 2000 11:25:42 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241625.LAA26974@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <Pine.GSO.4.02A.10002241646020.17421-100000@Hummer.DoCS.UU.SE>
from Peter Eisentraut at "Feb 24, 2000 04:59:48 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Thu, 24 Feb 2000 11:25:42 -0500 (EST)
CC: Tom Lane <tgl@sss.pgh.pa.us>, Rolf Grossmann <grossman@securitas.net>,
pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

On Thu, 24 Feb 2000, Tom Lane wrote:

Perhaps this is a good time to ask when and how any fix to this should be
applied.

This is arguably a bug fix, so you needn't worry about it being beta
phase.

I'm not sure how this works now: Do I just commit it to the tree, so it
will be in when, say, beta2 gets generated?

Betas are not static releases. We live in beta for over a month, with
people making changes to fix user problems.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 11:30:00 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA30926
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 11:29:22 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA26986;
Thu, 24 Feb 2000 11:26:26 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241626.LAA26986@candle.pha.pa.us>
Subject: Re: [HACKERS] Minor problems reloading dump in 7.0beta1
In-Reply-To: <21903.951408359@sss.pgh.pa.us> from Tom Lane at "Feb 24,
2000 11:05:59 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 11:26:26 -0500 (EST)
CC: Magnus Hagander <mha@sollentuna.net>,
"'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Magnus Hagander <mha@sollentuna.net> writes:

- It fails to reload datetime indexes, because of "DefineIndex: datetime_ops
class not found".

Oops. Comparing 6.5 and current pg_opclass contents, I see we are going
to have this trouble with timespan_ops and oid8_ops as well as with
datetime_ops (although very likely there are no user tables with indexes
on oid8? Not certain though).

Bruce, do you want to hack the parser to drop these opclass names the
same way it's dropping network_ops? Or do we need to adopt a cleaner
solution?

Doing it now.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 11:35:59 2000
Received: from s-mail2.hanmail.net (s-mail1.hanmail.net [211.32.117.61])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA32261
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 11:35:36 -0500 (EST)
(envelope-from teletraco@mixmail.com)
Received: from www3.mixmail.com (www.mixmail.com [211.32.117.93] (may be
forged)) by s-mail2.hanmail.net (8.9.1/8.9.1) with ESMTP id AAA23696;
Fri, 25 Feb 2000 00:35:25 +0900
Received: (from hanadmin@localhost)
by www3.mixmail.com (8.9.1/8.9.1) id BAA29439;
Fri, 25 Feb 2000 01:32:01 +0900 (KST)
From: "Post Message" <teletraco@mixmail.com>
To: pgsql-hackers@postgresql.org
Subject: Solid time
X-Mailer: Daum Web Mailer 1.0
Date: Thu, 24 Feb 2000 03:32:00 -1300
Message-Id: <20000224173200.HM.a0000000000DAHN@mixmail.com>
Errors-To: teletraco@mixmail.com
MIME-Version: 1.0
Content-Type: text/plain
Content-Transfer-Encoding: 8bit

Hello, I'm looking for solid edge "timer fix", help me
please
send it to teletraco@mixmail.com

Bye&Thx4All

Tu correo gratis en MixMail http://www.mixmail.com
Inicia tu navegacion en http://www.ya.com

From bouncefilter Thu Feb 24 11:36:01 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA32249
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 11:35:32 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA27384;
Thu, 24 Feb 2000 11:33:30 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241633.LAA27384@candle.pha.pa.us>
Subject: Re: [HACKERS] Minor problems reloading dump in 7.0beta1
In-Reply-To: <21903.951408359@sss.pgh.pa.us> from Tom Lane at "Feb 24,
2000 11:05:59 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 24 Feb 2000 11:33:30 -0500 (EST)
CC: Magnus Hagander <mha@sollentuna.net>,
"'pgsql-hackers@postgresql.org'" <pgsql-hackers@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Applied.

Magnus Hagander <mha@sollentuna.net> writes:

- It fails to reload datetime indexes, because of "DefineIndex: datetime_ops
class not found".

Oops. Comparing 6.5 and current pg_opclass contents, I see we are going
to have this trouble with timespan_ops and oid8_ops as well as with
datetime_ops (although very likely there are no user tables with indexes
on oid8? Not certain though).

Bruce, do you want to hack the parser to drop these opclass names the
same way it's dropping network_ops? Or do we need to adopt a cleaner
solution?

regards, tom lane

************

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 11:35:00 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA32097
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:34:46 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA22079;
Thu, 24 Feb 2000 11:34:19 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>,
"'hackers'" <pgsql-hackers@postgreSQL.org>
Subject: Re: AW: AW: [HACKERS] TRANSACTIONS
In-reply-to: <3.0.1.32.20000224061920.01715af0@mail.pacifier.com>
References: <3.0.1.32.20000224061920.01715af0@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Thu, 24 Feb 2000 06:19:20 -0800"
Date: Thu, 24 Feb 2000 11:34:18 -0500
Message-ID: <22076.951410058@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

It is up to the application or user to rollback the entire transaction
if that's the behavior that's desired.

Of course the whole concept of an explicit "begin" is non-standard,
too. In SQL you're always in a transaction, commit and rollback
terminate transactions and start a new one.

True, although SQL doesn't mandate exactly how that is accomplished.
We have some client interfaces that provide that behavior,
and that's a compliant way of doing it AFAICS.

We ought to consider ways of providing the same behavior in psql,
but it's not gonna happen for 7.0 --- too big a change for beta.

I suspect that most applications don't notice the difference. Most
will catch errors and roll back the current transaction, because that's
the logical thing to do in most cases.

You are assuming that the app has the intelligence to do so. A psql
script, for example, lacks that intelligence.

I do agree that this is an area where we need to do some work, but
it's not going to be a simple or small change. We will need nested-
transaction support in the backend, and some very careful rethinking
of the client interfaces to try to avoid breaking existing apps.

regards, tom lane

From bouncefilter Thu Feb 24 11:40:59 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA33160
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:40:19 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA22113;
Thu, 24 Feb 2000 11:39:58 -0500 (EST)
To: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
cc: "'hackers'" <pgsql-hackers@postgreSQL.org>
Subject: Re: AW: AW: [HACKERS] TRANSACTIONS
In-reply-to:
<219F68D65015D011A8E000006F8590C604AF7CF3@sdexcsrv1.f000.d0188.sd.spardat.at>
References:
<219F68D65015D011A8E000006F8590C604AF7CF3@sdexcsrv1.f000.d0188.sd.spardat.at>
Comments: In-reply-to Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
message dated "Thu, 24 Feb 2000 10:04:10 +0100"
Date: Thu, 24 Feb 2000 11:39:58 -0500
Message-ID: <22110.951410398@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at> writes:

I find this hard to believe, and even harder to believe that it's
mandated by the standard. What you're essentially claiming is that
everyone but us has nested transactions

They don't necessarily have nested tx, although some have.
All they provide is atomicity of single statements.

If it looks like a duck, walks like a duck, and quacks like a duck,
it's a duck no matter what it's called. How would you provide atomicity
of a single statement without a transaction-equivalent implementation?
That statement might be affecting many tuples in several different
tables. It's not noticeably easier to roll back one statement than
a whole sequence of them.

regards, tom lane

From bouncefilter Thu Feb 24 11:43:01 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA33549
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 11:42:35 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
LAA27533;
Thu, 24 Feb 2000 11:42:06 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241642.LAA27533@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <38B558BF.58FE5FC3@wgcr.org> from Lamar Owen at "Feb 24,
2000 11:13:51 am"
To: Lamar Owen <lamar.owen@wgcr.org>
Date: Thu, 24 Feb 2000 11:42:06 -0500 (EST)
CC: Peter Eisentraut <peter_e@gmx.net>,
Rolf Grossmann <grossman@securitas.net>, pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Peter Eisentraut wrote:

On Thu, 24 Feb 2000, Bruce Momjian wrote:

I see the same problem here. Also, the regression tests required me to
define PGLIB.

Is that because of createlang or initdb or both? Which regression driver?

Createlang has done this for some time -- at least since I've been
packaging the RPM's with the regression tests. I have had to define
PGLIB in regress.sh -- otherwise, createlang doesn't know where to find
the pl .so. As my 7.0 installation is at home, I can't check the 7.0
regress.sh from here -- however, the 6.5.x regress.sh did it's own PGLIB
definition.

For some reason, I didn't need it until recently.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 11:45:01 2000
Received: from gandalf.it-austria.net (gandalf.it-austria.net [213.150.1.65])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA33823
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:43:59 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at (sdgtw.sd.spardat.at
[172.18.1.16])
by gandalf.it-austria.net (xxx/xxx) with ESMTP id RAA90364;
Thu, 24 Feb 2000 17:43:42 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <1TSZGF51>; Thu, 24 Feb 2000 17:43:42 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C604AF7CF9@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>, Don Baccus <dhogaza@pacifier.com>
Cc: "'hackers'" <pgsql-hackers@postgreSQL.org>
Subject: AW: AW: AW: [HACKERS] TRANSACTIONS
Date: Thu, 24 Feb 2000 17:43:40 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

I suspect that most applications don't notice the difference. Most
will catch errors and roll back the current transaction, because that's
the logical thing to do in most cases.

You are assuming that the app has the intelligence to do so. A psql
script, for example, lacks that intelligence.

I thought that psql is the only frontend that would not have a problem
with the new behavior, because it now has the feature of "exit on first
error"
and thus rolls back the last open transaction anyway.

I do agree that this is an area where we need to do some work, but
it's not going to be a simple or small change. We will need nested-
transaction support in the backend, and some very careful rethinking
of the client interfaces to try to avoid breaking existing apps.

Yes, unfortunately.

Andreas

From bouncefilter Thu Feb 24 11:45:02 2000
Received: from securitas.net (root@extern.securitas.net [212.66.1.45])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA33941
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:44:48 -0500 (EST)
(envelope-from grossman@securitas.net)
Received: from blue.securitas.net (blue.securitas.net [212.66.0.24])
by securitas.net (8.9.3/8.9.3) with ESMTP id RAA06856;
Thu, 24 Feb 2000 17:43:38 +0100 (MET)
Received: (from grossman@localhost)
by blue.securitas.net (8.9.3/8.9.3) id RAA58547;
Thu, 24 Feb 2000 17:44:15 +0100 (CET)
Date: Thu, 24 Feb 2000 17:44:15 +0100 (CET)
Message-Id: <200002241644.RAA58547@blue.securitas.net>
From: Rolf Grossmann <grossman@securitas.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
To: Peter Eisentraut <peter_e@gmx.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>, Rolf Grossmann <grossman@securitas.net>,
pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <Pine.GSO.4.02A.10002241646020.17421-100000@Hummer.DoCS.UU.SE>
References: <21570.951406127@sss.pgh.pa.us>
<Pine.GSO.4.02A.10002241646020.17421-100000@Hummer.DoCS.UU.SE>
X-Mailer: VM 6.75 under Emacs 19.34.1

Hi,

on Thu, 24 Feb 2000 16:59:48 +0100 (MET) Peter Eisentraut wrote
concerning "Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0 " something like this:

(Of course psql is not a shell, but that's why we're discussing ...)

Now, be careful with this statement. Personally, I have already tried to
use psql as a shell and I think it would be really cool if you could just
write #!/path/to/psql -f to write sql scripts.
However, that would require psql to treat # as a comment starter and we're
moving away from SQL standards with that. So I'm a bit weary of suggesting
such a thing.

Using -f would work if you hadn't already overloaded it with another
meaning;

[5 min later ...]

Ah, a tcsh user! ;) I could go for an -X option to suppress reading the
startup file, with default being that it is read in any mode. A pretty
dump option letter, but not all that far-fetched.

Uhm ... my tcsh manual describes those options differently:

-f The shell ignores ~/.tcshrc, and thus starts faster.
-X Is to -x as -V is to -v.

Of course, as we have noted above, psql is not a shell, so I wonder if
that's the way to go. Personally, I'd say just pick a letter.

Bye, Rolf

From bouncefilter Thu Feb 24 11:49:01 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA34593
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:48:24 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id LAA22186;
Thu, 24 Feb 2000 11:48:07 -0500 (EST)
To: Rolf Grossmann <grossman@securitas.net>
cc: Peter Eisentraut <peter_e@gmx.net>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-reply-to: <200002241644.RAA58547@blue.securitas.net>
References: <21570.951406127@sss.pgh.pa.us>
<Pine.GSO.4.02A.10002241646020.17421-100000@Hummer.DoCS.UU.SE>
<200002241644.RAA58547@blue.securitas.net>
Comments: In-reply-to Rolf Grossmann <grossman@securitas.net>
message dated "Thu, 24 Feb 2000 17:44:15 +0100"
Date: Thu, 24 Feb 2000 11:48:06 -0500
Message-ID: <22183.951410886@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Rolf Grossmann <grossman@securitas.net> writes:

(Of course psql is not a shell, but that's why we're discussing ...)

Now, be careful with this statement. Personally, I have already tried to
use psql as a shell and I think it would be really cool if you could just
write #!/path/to/psql -f to write sql scripts.

[ straying off-topic ]

Have you tried pgbash? I haven't, but it sounds pretty cool if you
think psql and your shell should be the same thing...

regards, tom lane

From bouncefilter Thu Feb 24 12:03:02 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA39259
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 12:02:25 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id JAA06891;
Thu, 24 Feb 2000 09:01:33 -0800 (PST)
Message-Id: <3.0.1.32.20000224085146.0171bb90@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Thu, 24 Feb 2000 08:51:46 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: AW: AW: [HACKERS] TRANSACTIONS
Cc: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>,
"'hackers'" <pgsql-hackers@postgreSQL.org>
In-Reply-To: <22076.951410058@sss.pgh.pa.us>
References: <3.0.1.32.20000224061920.01715af0@mail.pacifier.com>
<3.0.1.32.20000224061920.01715af0@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 11:34 AM 2/24/00 -0500, Tom Lane wrote:

We ought to consider ways of providing the same behavior in psql,
but it's not gonna happen for 7.0 --- too big a change for beta.

Oh, yeah, no doubt about that.

I suspect that most applications don't notice the difference. Most
will catch errors and roll back the current transaction, because that's
the logical thing to do in most cases.

You are assuming that the app has the intelligence to do so. A psql
script, for example, lacks that intelligence.

I did say "most", not "all".

I do agree that this is an area where we need to do some work, but
it's not going to be a simple or small change. We will need nested-
transaction support in the backend, and some very careful rethinking
of the client interfaces to try to avoid breaking existing apps.

Well...Oracle provides "autocommit" as a convenience. Perhaps we
could let the user select between old-style or SQL92-compliant behavior
during a transition period?

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Thu Feb 24 11:54:59 2000
Received: from gandalf.it-austria.net (gandalf.it-austria.net [213.150.1.65])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA37295
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 11:54:06 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at (sdgtw.sd.spardat.at
[172.18.1.16])
by gandalf.it-austria.net (xxx/xxx) with ESMTP id RAA198186;
Thu, 24 Feb 2000 17:53:56 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <1TSZGF6W>; Thu, 24 Feb 2000 17:53:56 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C604AF7CFA@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>
Cc: "'hackers'" <pgsql-hackers@postgreSQL.org>
Subject: AW: AW: AW: [HACKERS] TRANSACTIONS
Date: Thu, 24 Feb 2000 17:53:54 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

They don't necessarily have nested tx, although some have.
All they provide is atomicity of single statements.

If it looks like a duck, walks like a duck, and quacks like a duck,
it's a duck no matter what it's called. How would you
provide atomicity
of a single statement without a transaction-equivalent implementation?
That statement might be affecting many tuples in several different
tables. It's not noticeably easier to roll back one statement than
a whole sequence of them.

Yes, the only difference seems to be, that the changes need not
be sync'd to disk, and you only need one level of nesting as long
as the user is not presented the ability to use nested tx.

Andreas

From bouncefilter Thu Feb 24 12:04:01 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA39427
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 12:03:14 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id MAA22324;
Thu, 24 Feb 2000 12:03:00 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Lamar Owen <lamar.owen@wgcr.org>, Peter Eisentraut <peter_e@gmx.net>,
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-reply-to: <200002241642.LAA27533@candle.pha.pa.us>
References: <200002241642.LAA27533@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Thu, 24 Feb 2000 11:42:06 -0500"
Date: Thu, 24 Feb 2000 12:03:00 -0500
Message-ID: <22321.951411780@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Bruce Momjian <pgman@candle.pha.pa.us> writes:

PGLIB in regress.sh -- otherwise, createlang doesn't know where to find
the pl .so. As my 7.0 installation is at home, I can't check the 7.0
regress.sh from here -- however, the 6.5.x regress.sh did it's own PGLIB
definition.

For some reason, I didn't need it until recently.

I have PGDATA and PGLIB defined in .profile for my postgres account,
so I wouldn't have noticed whether the regress tests need it or not :-(
Possibly the same is true for most of the other developers.

IIRC, "make all" to set up the regress tests also needs PGLIB to be set.

regards, tom lane

From bouncefilter Thu Feb 24 12:38:00 2000
Received: from ara.zf.jcu.cz (zakkr@ara.zf.jcu.cz [160.217.161.4])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA43738
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 12:37:12 -0500 (EST) (envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
by ara.zf.jcu.cz (8.9.3/8.9.3/Debian/GNU) with SMTP id SAA32705;
Thu, 24 Feb 2000 18:35:14 +0100
Date: Thu, 24 Feb 2000 18:35:14 +0100 (CET)
From: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
To: Jan Wieck <wieck@debis.com>
cc: Hiroshi Inoue <Inoue@tpf.co.jp>, Tom Lane <tgl@sss.pgh.pa.us>,
pgsql-hackers <pgsql-hackers@postgreSQL.org>
Subject: [HACKERS] Cache query implemented
In-Reply-To: <m12Ni7o-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Message-ID: <Pine.LNX.3.96.1000224175739.17426A-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 23 Feb 2000, Jan Wieck wrote:

I don't see much complexity difference between one context
per plan vs. one context for all. At least if we do it
transparently inside of SPI_saveplan() and SPI_freeplan().

Well, I explore PG's memory context routines and is probably more
simple destroy mem context (than use feeeObject()) and create new context
for plan is simple too. (Jan, Hiroshi and PG's source convince me :-)

Today afternoon I rewrite query cache and now is implemented as
'context-per-plan'. It allows me write a 'DROP PLAN' command. We can use
this cache in SPI too, and create new command SPI_freeplan() (and stop
TopMemoryContext feeding).

Now, PREPARE/EXECUTE are ready to usage. See:

test=# prepare my_plan as select * from tab where id = $1 using int;
PREPARE
test=# execute my_plan using 2;
id | data
----+------
2 | aaaa
(1 row)

test=# drop plan my_plan;
DROP
test=# execute my_plan using 2;
ERROR: Plan with name 'my_plan' not exist

I still not sure with PREPARE/EXECUTE keywords, I vote for:

CREATE PLAN name AS query [ USING type, ... ]
EXECUTE PLAN name [ USING values, ... ]
DROP PLAN name

Comments? (Please. I really not SQL's standard guru...)

Karel

From bouncefilter Thu Feb 24 13:12:01 2000
Received: from fb01.eng00.mindspring.net (fb01.eng00.mindspring.net
[207.69.229.19]) by hub.org (8.9.3/8.9.3) with ESMTP id NAA65592;
Thu, 24 Feb 2000 13:11:09 -0500 (EST)
(envelope-from keithmur@mindspring.com)
Received: from mindspring.com (pool-207-205-128-243.atln.grid.net
[207.205.128.243])
by fb01.eng00.mindspring.net (8.9.3/8.8.5) with ESMTP id NAA32029;
Thu, 24 Feb 2000 13:11:07 -0500 (EST)
Message-ID: <38B57564.46BB28C0@mindspring.com>
Date: Thu, 24 Feb 2000 12:16:04 -0600
From: "Keith G. Murphy" <keithmur@mindspring.com>
Reply-To: keithmur@mindspring.com
Organization: A small shoebox in the middle of the road
X-Mailer: Mozilla 4.7 [en] (Win95; U)
X-Accept-Language: en,pdf
MIME-Version: 1.0
CC: pgsql-hackers@postgreSQL.org, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
References: <38B27760.DB921B57@sferacarta.com> <23935.951237171@sss.pgh.pa.us>
<38B3E365.D2B61959@sferacarta.com>
<200002231516.KAA23839@skillet.infoplease.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Karl DeBisschop wrote:

Sorry for my english, Tom, but the point is another, I'm talking
about transactions not about error messages.

This is only a stupid example how to abort a transaction, PostgreSQL
aborts automatically transactions if an error occurs, even an warning
or a syntax error.

I can believe that all other databases are wrong and only we
(PostgreSQL) are right, but please try to understand me. This is not
easy to believe anyway.

I'm looking for another database with a behavior like PostgreSQL but
I can't find it, and I tried a lot of them until now.

Do you know some database with transactions like PostgreSQL?

I personally don't feel qualified to interpret the standard. But I
would like to pipe in a little on the issue of what is desirable.

By default, as a developer, I would be quite unhappy with the behavior
of those other databases (allowing a commit after an insert has
failed). If I do a bulk copy into an existing database, and one copy
fails, that sort of behavior could concievably render my database
unusable with not possibility of recovery. So in that sense, from the
point of view of desirability I think postgres got it right.

But then I thought about if from a programming language point of
view. Consider the following code (I use perl/DBI as an example).

========================= example =========================

$dbh->{AutoCommit} = 0;
$dbh->do("CREATE TABLE tmp (a int unique,b int)");
while (<>){
if (/([0-9]+) ([0-9]+)/) {
$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
}
}
$dbh->commit;
$dbh->disconnect;

========================= end ============================

This incorporates a very common idiom within a transaction block. Of
course, this fails. As far as I can tell from the preceding
discussion, there is no way to "sanitize" the transaction once you
have fixed the error. IMHO, it would be EXTREMELY useful to be able to
implement the above transaction. But not by default.

I'm not sure what a resonable syntax would be - several come to mind.
You could have "SANITIZE TRANSACTION" or "\unset warning", whatever,
the exact syntax matters little to me. But without this sort of
capability, people who do programatic error checking and correction
(which seems like a good thing) are essentially penalized because they
cannot effectively use transactions.

To continue with your example, this should work:

$dbh->{AutoCommit} = 0;
$dbh->do("CREATE TABLE tmp (a int unique,b int)");
while (<>){
if (/([0-9]+) ([0-9]+)/) {
eval{$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)")};
if ($@) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
}
}
$dbh->commit;
$dbh->disconnect;

Sadly, it does not, as far as I can tell. In fact, it seems to corrupt
the database to where you can't create the table tmp anymore, on my
system. I certainly never get a table.

What's the rationale behind having the database blow out eval's error
trapping? Can't see where letting a program recover from an error in a
statement compromises atomicity.

Apologies if it is already possible to do this.

Likewise.

From bouncefilter Thu Feb 24 13:36:00 2000
Received: from ra.sai.msu.su (ra.sai.msu.su [158.250.29.2])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA93657
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 13:35:36 -0500 (EST) (envelope-from oleg@sai.msu.su)
Received: from ra (ra [158.250.29.2])
by ra.sai.msu.su (8.9.3/8.9.3) with SMTP id VAA26857
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 21:35:32 +0300 (GMT)
Date: Thu, 24 Feb 2000 21:35:32 +0300 (GMT)
From: Oleg Bartunov <oleg@sai.msu.su>
X-Sender: megera@ra
To: pgsql-hackers@postgreSQL.org
Subject: problems with TEMP table (6.5.3)
Message-ID: <Pine.GSO.3.96.SK.1000224210952.22930i-100000@ra>
Organization: Sternberg Astronomical Institute (Moscow University)
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

I have a problem with 6.5.3 and TEMP table inside transaction.

test=> begin;
BEGIN
test=> create temp table tempid (id int, level int);
CREATE
test=> \q

Postgres process doesn't stopped. The same happens if I
use rollback before ending transaction.

test=> \d
Couldn't find any tables, sequences or indices!
test=> begin;
BEGIN
test=> create temp table tempid (id int, level int);
CREATE
test=> insert into tempid ( id, level ) values (1 ,1);
INSERT 332330 1
test=> rollback;
ABORT
test=> end;
NOTICE: EndTransactionBlock and not inprogress/abort state
END
test=> \q

zen:~/app/pgsql$ psql test
Welcome to the POSTGRESQL interactive sql monitor:
Please read the file COPYRIGHT for copyright terms of POSTGRESQL
[PostgreSQL 6.5.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66]

type \? for help on slash commands
type \q to quit
type \g or terminate with semicolon to execute query
You are currently connected to the database: test

test=> vacuum;
NOTICE: AbortTransaction and not in in-progress state
ERROR: cannot write block 0 of pg_temp.8928.0 [test] blind
test=>

I checked 7.0 from cvs and it looks better.

Also, I see a lot of opened files when doing
select/inserts into temp table inside transaction
(temp table was created before transaction). So,
if I do a lot of selects/inserts I easily reach file description limit.

Do I miss here ?

Regards,

Oleg

_____________________________________________________________
Oleg Bartunov, sci.researcher, hostmaster of AstroNet,
Sternberg Astronomical Institute, Moscow University (Russia)
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(095)939-16-83, +007(095)939-23-83

From bouncefilter Thu Feb 24 14:10:01 2000
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA97657
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 14:09:13 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id NAA39012
for pgsql-hackers@postgresql.org; Thu, 24 Feb 2000 13:55:14 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: Kyle <kaf@_nwlink_._com_>
X-Newsgroups: comp.databases.postgresql.hackers
Subject: postgresql performance, smp vs non-smp
Date: 24 Feb 2000 10:50:21 -0800
Organization: patentinvestor.com
Lines: 19
Sender: kaf@ip146.usw5.rb1.bel.nwlink.com
Message-ID: <893uhd$gqg$1@ip146.usw5.rb1.bel.nwlink.com>
Keywords: smp,apache,performance
X-Trace: 24 Feb 2000 10:43:25 -0800, ip146.usw5.rb1.bel.nwlink.com
To: pgsql-hackers@postgresql.org

Has anyone done any performance metrics for PostgreSQL on smp vs
non-smp systems? I'm guessing it won't add much, as the hard disk
performance is probably a greater concern for a database (assumption).

My situation is I need some new hardware and am strapped for cash. My
options are a single processor (probably Intel PII 450 or comparable)
or a dual system based on the same. It'll be running PostgreSQL and
Apache (yeah, I know I ought to separate them onto different machines
but humor me here). I'm exploring how Apache and PostgreSQL fair with
dual processor systems. Given that I'll have both running, it may be
a wise choice. Any wisdom people have would be very useful.

Thanks,
-Kyle
kaf@_nwlink_._com_
remove underscores to reply

From bouncefilter Thu Feb 24 14:17:01 2000
Received: from alert.infoplease.com (range.infoplease.com [208.222.166.25])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA98239;
Thu, 24 Feb 2000 14:16:36 -0500 (EST)
(envelope-from kdebisschop@range.infoplease.com)
Received: from skillet.infoplease.com (skillet [10.0.1.212])
by alert.infoplease.com (8.9.3+Sun/8.9.3) with ESMTP id OAA07834;
Thu, 24 Feb 2000 14:16:05 -0500 (EST)
Received: (from kdebisschop@localhost)
by skillet.infoplease.com (8.9.3/8.9.1) id OAA07901;
Thu, 24 Feb 2000 14:16:05 -0500
Date: Thu, 24 Feb 2000 14:16:05 -0500
Message-Id: <200002241916.OAA07901@skillet.infoplease.com>
X-Authentication-Warning: skillet.infoplease.com: kdebisschop set sender to
kdebisschop@spaceheater.infoplease.com using -f
From: Karl DeBisschop <kdebisschop@range.infoplease.com>
To: pgsql-hackers@postgreSQL.org, pgsql-general@postgreSQL.org
In-reply-to: <38B57564.46BB28C0@mindspring.com> (keithmur@mindspring.com)
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Reply-to: kdebisschop@range.infoplease.com
References: <38B27760.DB921B57@sferacarta.com> <23935.951237171@sss.pgh.pa.us>
<38B3E365.D2B61959@sferacarta.com>
<200002231516.KAA23839@skillet.infoplease.com>
<38B57564.46BB28C0@mindspring.com>

To summarize, I stated that the following does not work with
postgresql:

$dbh->{AutoCommit} = 0;
$dbh->do("CREATE TABLE tmp (a int unique,b int)");
while (<>){
if (/([0-9]+) ([0-9]+)/) {
$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");
if ($rtv) {$dbh->do("UPDATE tmp SET b=$2 where a=$1")};
}
}
$dbh->commit;
$dbh->disconnect;

I further said that regardless of what the SQL standard gurus decide,
I felt that postgresql currently gives desirable behavior - once a
transaction is started, it's either all or nothing. But then I
qualified that by saying I'd like somehow to be able to "sanitize" the
transaction so that the common idiom above could be made to work.

From my examination, the difference between our two examples is

Original:
KD> $rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)");

Modified:
KM> eval{$rtv = $dbh->do("INSERT INTO tmp VALUES ($1,$2)")};

From the point of view if the DBMS, i believe these are identical - in

both cases the query is issued to the DMBS and the overall transaction
becomes "contaminated". And as I said before, this is exactly what
I'd like to have happen in the default case.

It's not that eval's error trapping is blown out - it's that the
transaction defined by the AutoCommit cannot complete because a part
of it cannot complete -- that's what atomicity means.

At least that's the way it looks to me. But as I started out saying,
I don't feel qualified to interpret the standard - I might be wrong,
plain and simple.

--
Karl DeBisschop <kdebisschop@alert.infoplease.com>
617.832.0332 (Fax: 617.956.2696)

Information Please - your source for FREE online reference
http://www.infoplease.com - Your Ultimate Fact Finder
http://kids.infoplease.com - The Great Homework Helper

Netsaint Plugins Development
http://netsaintplug.sourceforge.net

From bouncefilter Thu Feb 24 15:04:01 2000
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA03240
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 15:03:09 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
OAA04380;
Thu, 24 Feb 2000 14:42:54 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002241942.OAA04380@candle.pha.pa.us>
Subject: Re: [HACKERS] problems with TEMP table (6.5.3)
In-Reply-To: <Pine.GSO.3.96.SK.1000224210952.22930i-100000@ra> from Oleg
Bartunov at "Feb 24, 2000 09:35:32 pm"
To: Oleg Bartunov <oleg@sai.msu.su>
Date: Thu, 24 Feb 2000 14:42:54 -0500 (EST)
CC: pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I have a problem with 6.5.3 and TEMP table inside transaction.

test=> vacuum;
NOTICE: AbortTransaction and not in in-progress state
ERROR: cannot write block 0 of pg_temp.8928.0 [test] blind
test=>

Yes, there were some problems with temp tables and transaction scoping
that we could not fix in 6.5.*. Those are all addressed in 7.0.

I checked 7.0 from cvs and it looks better.

Also, I see a lot of opened files when doing
select/inserts into temp table inside transaction
(temp table was created before transaction). So,
if I do a lot of selects/inserts I easily reach file description limit.

Do I miss here ?

File descriptors are kept open as a cache. They will be closed as not
needed.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

From bouncefilter Thu Feb 24 15:59:02 2000
Received: from mailgw1.prontomail.com (mailgw1.prontomail.com
[209.185.149.197]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA08030
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 15:59:00 -0500 (EST)
(envelope-from rob.c@virgilio.it)
Received: from web03 (209.185.149.203) by mailgw1.prontomail.com (NPlex
2.0.123); Thu, 24 Feb 2000 12:56:58 -0800
From: "Roberto Cornacchia" <rob.c@virgilio.it>
Message-Id: <9E673EF76FAE3D1178E200807CFD6BF8@rob.c.virgilio.it>
Date: Thu, 24 Feb 2000 15:56:58 -0500
X-Priority: Normal
Content-Type: text/plain; charset=iso-8859-1
To: dhogaza@pacifier.com, tom lane <tgl@sss.pgh.pa.us>
Subject: Re: [HACKERS] Re: about 7.0 LIMIT optimization
CC: pgsql-hackers@postgresql.org
X-Mailer: Web Based Pronto
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

QuickSort: 1.6E+14
SortStop: 1.5E+11

Are there some zeroes missing here? That sounds like an awful lot of
operations for a quicksort of only 1E5 elements...

Yeah, obviously one or more of his numbers are wrong. Let's see, a
[...]

Yes I'm sorry, those numbers were completely wrong, I shoud have realized immediately. Here are the correct ones:

QuickSort: 1.66096e+06
SortStop: 1.00327e+05

The obvious way to do it would be with a heap-based sort. After you've
built the heap, you pull out the first ten elements and then stop.
Offhand this only seems like it'd save about half the work, though,
so maybe Roberto has a better idea.

I'd like to see some elaboration.

It is, indeed, a heap-based sort, but you don't need to do so many insertions in the heap.
It works like that:

- put first 10 rows in the heap, whith the worst value on head
- compare each other 99990 rows with the current head
- if new row is better, then
trash current head and insert new row into the heap,
otherwise trash the new row.

In this way the number of insertions in the heap is considerably lower (you can find more details on Knuth, vol III). Moreover, only <N> rows (10 in this case) are kept in memory at the same time, reducing the probability to need an external-sort.

Regards

Roberto Cornacchia

===========================================================

VIRGILIO MAIL - Il tuo indirizzo E-mail gratis e per sempre
http://mail.virgilio.it/

VIRGILIO - La guida italiana a Internet
http://www.virgilio.it/

From bouncefilter Thu Feb 24 16:49:03 2000
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA11512
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 16:48:45 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id NAA26990;
Thu, 24 Feb 2000 13:48:03 -0800 (PST)
Message-Id: <3.0.1.32.20000224133654.01095c20@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Thu, 24 Feb 2000 13:36:54 -0800
To: "Roberto Cornacchia" <rob.c@virgilio.it>, tom lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] Re: about 7.0 LIMIT optimization
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <9E673EF76FAE3D1178E200807CFD6BF8@rob.c.virgilio.it>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 03:56 PM 2/24/00 -0500, Roberto Cornacchia wrote:

QuickSort: 1.6E+14
SortStop: 1.5E+11

Are there some zeroes missing here? That sounds like an awful lot of
operations for a quicksort of only 1E5 elements...

Yeah, obviously one or more of his numbers are wrong. Let's see, a
[...]

Yes I'm sorry, those numbers were completely wrong, I shoud have realized

immediately. Here are the correct ones:

QuickSort: 1.66096e+06
SortStop: 1.00327e+05

Yeah, there you go! Thanks...

- put first 10 rows in the heap, whith the worst value on head
- compare each other 99990 rows with the current head
- if new row is better, then
trash current head and insert new row into the heap,
otherwise trash the new row.

In this way the number of insertions in the heap is considerably lower

(you can find more details on Knuth, vol III).

"Sorting and searching", right, where would we be without Knuth?

- Don Baccus, Portland OR <dhogaza@pacifier.com>
Nature photos, on-line guides, Pacific Northwest
Rare Bird Alert Service and other goodies at
http://donb.photo.net.

From bouncefilter Thu Feb 24 16:43:03 2000
Received: from mailgw3.prontomail.com (mailgw3.prontomail.com
[209.185.149.199]) by hub.org (8.9.3/8.9.3) with ESMTP id QAA10903
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 16:42:30 -0500 (EST)
(envelope-from rob.c@virgilio.it)
Received: from web03 (209.185.149.203) by mailgw3.prontomail.com (NPlex
2.0.123); Thu, 24 Feb 2000 13:36:10 -0800
From: "Roberto Cornacchia" <rob.c@virgilio.it>
Message-Id: <C6973EF76FAE3D1178E200807CFD6BF8@rob.c.virgilio.it>
Date: Thu, 24 Feb 2000 16:41:25 -0500
X-Priority: Normal
Content-Type: text/plain; charset=iso-8859-1
To: tgl@sss.pgh.pa.us
Subject: Re: about 7.0 LIMIT optimization
CC: pgsql-hackers@postgresql.org
X-Mailer: Web Based Pronto
Mime-Version: 1.0
Content-Transfer-Encoding: 7bit

Hi,

I'm not sure about your comment about referential constraints. If you
are doing analysis of restriction clauses to prove that a particular
stage doesn't require reading as many rows as it otherwise would, then
you've done more than I have.

Yes, that's what we do. Here is a clarifying example:

-----
"Retrieve name, salary and Dept name of the 10 most paid employees"

SELECT Emp.name, Emp.salary, Dep.name
FROM Emp, Dep
WHERE Emp.dno=Dept.dno
STOP AFTER 10
RANK BY Emp.salary DESC;
-----

Suppose you have a referential constraint like:
Emp->dno --> Dep.dno (foreign --> primary)

In this case we can do :

join (Emp.dno = Dep.dno)
Stop 10
Scan Emp
Scan Dept

since we are sure that every employee works in a departement (because of the constraints), so the 10 most paid employees will survive after the join. In this way you can reduce the cardinality of one of the input stream of the join, obtaining the same final results.

Note that this is a very simple case. In many plans involving a larger number of joins you can place a Stop operator in a deep position, reducing the work of all the following joins.

We have formalized a set of rules which allow us to determine wheter or not a position in the plan for the Stop operator is safe and then we have developed a fast algorithm able to take the right decision.

Regards

R. Cornacchia
A. Ghidini
Dr. P. Ciaccia

===========================================================

VIRGILIO MAIL - Il tuo indirizzo E-mail gratis e per sempre
http://mail.virgilio.it/

VIRGILIO - La guida italiana a Internet
http://www.virgilio.it/