how to create index on timestamp field in pre v7 database

Alex Howansky <alex@wankwood.com> writes:

My question is, how "equivalent" are these types?

They're the same code: we jacked up the name "timestamp" and rolled the
old datetime code underneath. Strictly a matter of coming closer to
the SQL standard names for these datatypes.

Can I use datetime_ops to index a timestamp field in a v6.5.3 database?

Similarly, "datetime_ops" in 6.5 is now "timestamp_ops".

As a rule, I'd suggest not bothering with opclasses in index
declarations. The only situation where you need to select one is
where there is more than one possible opclass for the same datatype.
This holds for some of the geometric types, but not for any plain scalar
types like numerics or date/time types. (You could think of an opclass
as specifying which sort order the index uses...)

regards, tom lane

PS: Actually there's a second case where you must specify an opclass,
which is if you are creating a functional index; for some reason the
system can't figure out the right opclass in that case. This is a bug,
no doubt ... never looked at it hard enough to see why it's failing.

From bouncefilter Thu Feb 24 19:19:05 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 TAA42947
for <pgsql-hackers@postgresql.org>;
Thu, 24 Feb 2000 19:18:05 -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 TAA23389;
Thu, 24 Feb 2000 19:17:36 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Jeroen van Vianen <jeroen@design.nl>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Re: [PATCHES] Patch for more readable parse error
messages
In-reply-to: <Pine.LNX.4.21.0002242244030.4316-100000@localhost.localdomain>
References: <Pine.LNX.4.21.0002242244030.4316-100000@localhost.localdomain>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
message dated "Fri, 25 Feb 2000 00:38:47 +0100"
Date: Thu, 24 Feb 2000 19:17:36 -0500
Message-ID: <23386.951437856@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <peter_e@gmx.net> writes:

IMHO, the use of error messages in PostgreSQL has a big conceptual
problem. It's only too tempting to write elog(ERROR, "I don't know what to
do now.") anywhere and any time. This is very convenient for the
developers but not very nice for client applications that want to
recognize, categorize, and recover from errors.

The vast majority of the one-off error messages are internal consistency
checks. It seems to me that a workable compromise is to insist on
standardized error codes/texts for reporting user mistakes, but to
continue to allow spur-of-the-moment messages for internal errors.
Most or all internal errors would have the same classification anyway
from the point of view of an application trying to decide what to do,
so they could all share one or a few "error ID numbers".

A necessary extension to the above would be a way to pass along supportive
data. The tricky part will be to figure out a syntax that is not too
cumbersome, not too restrictive, and encourages help by the compiler.

A printf/elog-like syntax should still work --- the message catalog that
PGE_TRIGGERED_DATA_CHANGE_VIOLATION indexes into would contain strings
that still have %-escapes, but that shouldn't make life any more
difficult for internationalization. And we do have the opportunity
to check mistakes with gcc, if we stick to the standard printf escapes.

Or do we? Hmm ... not if the error message text isn't available at
the call site ... Here's a thought: suppose that error code macros like
PGE_TRIGGERED_DATA_CHANGE_VIOLATION normally expand to an error code
number, which eventually gets used as an index into a localizable table
of error format strings; but we have the option to run with header files
that define all these macros as the actual error message literal
strings. Then gcc could check for parameter mismatch in that case.
For development work that might even be the normal thing, and only
in production scenarios would you introduce the extra level of
indirection to get to an error message string.

In any case, I believe that the actual error message string should be
assembled in the front-end.

That will not work, because the set of possible error messages will
undoubtedly change with every backend release, and we do *not* want
to get into a situation where out-of-date clients mean you get no
error message (or worse, a wrong error message). It will be better
to have the message table on the backend side. As long as the backend
ships an identifying code number along with the message text, I think
that will satisfy the needs of applications to avoid reverse-parsing
error messages.

Other than that, I agree with everything you say ;-)

Comments? Anyone interested? This would be very dear to my heart so I'd be
very willing to spend a lot of time on it.

It will take a lot of time to clean this up, but I think everyone agrees
we need to do it. It's just been a matter of someone taking on the job.

regards, tom lane

From bouncefilter Thu Feb 24 19:25:08 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 TAA43371
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 19:25:03 -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 QAA29697;
Thu, 24 Feb 2000 16:23:56 -0800 (PST)
Message-Id: <3.0.1.32.20000224162043.010928d0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Thu, 24 Feb 2000 16:20:43 -0800
To: Bruce Momjian <pgman@candle.pha.pa.us>, Peter Eisentraut <peter_e@gmx.net>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] Re: [PATCHES] Patch for more readable parse
error messages
Cc: Jeroen van Vianen <jeroen@design.nl>, Tom Lane <tgl@sss.pgh.pa.us>,
pgsql-hackers@postgreSQL.org
In-Reply-To: <200002242350.SAA21853@candle.pha.pa.us>
References: <Pine.LNX.4.21.0002242244030.4316-100000@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 06:50 PM 2/24/00 -0500, Bruce Momjian wrote:

In any case, I believe that the actual error message string should be
assembled in the front-end. I'm not too fond of the idea of letting
clients parse out the interesting parts of an error out of a blob of text.

Comments? Anyone interested? This would be very dear to my heart so I'd be
very willing to spend a lot of time on it.

Vadim strongly believes in error mesage numbers. We certainly should do
better, if only to print a code before the error code or something.

I do, too. Anyone else with a language implementation background is likely
to share that bias.

For starters ... you can at least imagine doing things like provide error
messages in languages other than English. Actually...Vadim could probably
force the issue by commiting a version with all the error messages in
Russian! Hmmm...wonder if he's thought of that? :)

And for applications it often makes a lot more sense to just get a
defined code.

When I improved on the AOLserver driver for Postgres, one of my goals
was to have it survive the closing of a backend. This gets less
crucial with each bug fix, but, heck ... the backend still pees its
pants and crashes occasionally, let's face it. In this case, the
driver wants to reestablish the connection to the backend (because
it's being managed as part of a persistent pool of connections by
the web server) but return an error.

Afterwards, all other backends close themselves and pass back a
delightfully wordy message that one should retry their query because
it didn't really crash, but rather is closing just in case shared
memory has been corrupted by the very naughty backend that really
did crash. In this case, the driver wants to reconnect and
retry the query, and if it succeeds return normally, with the
web server none the wiser.

(works great, BTW)

There's no documented way to distinguish between the two kinds of
backend closures that I could find. Interpreting the string in
general seems to be how one is expected to probe to determine exactly
what has happened, not only in this case but with other errors, too.

This sucks, IMO.

It turns out there's a trivial way to distinguish these two particular
cases I mention, without resorting to looking at the actual error message,
but I think it illustrates the general kludginess of returning strings
with no error code.

- 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 19:37:05 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 TAA44254
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 19:36:37 -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 QAA04652;
Thu, 24 Feb 2000 16:35:56 -0800 (PST)
Message-Id: <3.0.1.32.20000224163341.0108da50@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Thu, 24 Feb 2000 16:33:41 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>, Peter Eisentraut <peter_e@gmx.net>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] Re: [PATCHES] Patch for more readable parse
error messages
Cc: Jeroen van Vianen <jeroen@design.nl>, pgsql-hackers@postgreSQL.org
In-Reply-To: <23386.951437856@sss.pgh.pa.us>
References: <Pine.LNX.4.21.0002242244030.4316-100000@localhost.localdomain>
<Pine.LNX.4.21.0002242244030.4316-100000@localhost.localdomain>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 07:17 PM 2/24/00 -0500, Tom Lane wrote:

The vast majority of the one-off error messages are internal consistency
checks. It seems to me that a workable compromise is to insist on
standardized error codes/texts for reporting user mistakes, but to
continue to allow spur-of-the-moment messages for internal errors.
Most or all internal errors would have the same classification anyway
from the point of view of an application trying to decide what to do,
so they could all share one or a few "error ID numbers".

I have no problem with this. Why not just prepend them with an "internal"
error code? Clients can't really do much other than gasp "omigosh!" when
confronted with an internal error anyway...

Or do we? Hmm ... not if the error message text isn't available at
the call site ... Here's a thought: suppose that error code macros like
PGE_TRIGGERED_DATA_CHANGE_VIOLATION normally expand to an error code
number, which eventually gets used as an index into a localizable table
of error format strings; but we have the option to run with header files
that define all these macros as the actual error message literal
strings. Then gcc could check for parameter mismatch in that case.
For development work that might even be the normal thing, and only
in production scenarios would you introduce the extra level of
indirection to get to an error message string.

Something like this sounds like a fine.

In any case, I believe that the actual error message string should be
assembled in the front-end.

That will not work, because the set of possible error messages will
undoubtedly change with every backend release, and we do *not* want
to get into a situation where out-of-date clients mean you get no
error message (or worse, a wrong error message). It will be better
to have the message table on the backend side.

Yes, this is where it belongs. An application gets an error number,
then asks for a message to go with it if it wants one. Or, the
error's returned as an error code and message, either way.

As long as the backend
ships an identifying code number along with the message text, I think
that will satisfy the needs of applications to avoid reverse-parsing
error messages.

Yep.

Other than that, I agree with everything you say ;-)

Comments? Anyone interested? This would be very dear to my heart so I'd be
very willing to spend a lot of time on it.

It will take a lot of time to clean this up, but I think everyone agrees
we need to do it. It's just been a matter of someone taking on the job.

Go, Peter!

- 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 19:36:06 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 TAA44182
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 19:35: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 TAA23572;
Thu, 24 Feb 2000 19:35:00 -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.LNX.4.21.0002250013250.4316-100000@localhost.localdomain>
References: <Pine.LNX.4.21.0002250013250.4316-100000@localhost.localdomain>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
message dated "Fri, 25 Feb 2000 00:39:15 +0100"
Date: Thu, 24 Feb 2000 19:35:00 -0500
Message-ID: <23569.951438900@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <peter_e@gmx.net> writes:

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.

I wasn't actually implying to have picked -X in accordance with tcsh, I
was just confused about how Tom talked about -f.

Oh, sorry, I just meant that -f already has one special behavior in
addition to just physically selecting the input source, namely
causing line numbers to get attached to error messages. That's fine,
but adding two special behaviors that aren't really closely related
to the same switch is not so great.

regards, tom lane

From bouncefilter Thu Feb 24 20:01:06 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 UAA46238
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 20:00:08 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from cadzone ([126.0.1.40] (may be forged))
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id JAA02266; Fri, 25 Feb 2000 09:59:59 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Zeugswetter Andreas SB" <ZeugswetterA@wien.spardat.at>
Cc: "'hackers'" <pgsql-hackers@postgreSQL.org>,
"'Tom Lane'" <tgl@sss.pgh.pa.us>
Subject: RE: AW: AW: [HACKERS] TRANSACTIONS
Date: Fri, 25 Feb 2000 10:06:15 +0900
Message-ID: <000501bf7f2c$83c55720$2801007e@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To:
<219F68D65015D011A8E000006F8590C604AF7CFA@sdexcsrv1.f000.d0188.sd.spardat.at>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal

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

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.

Hmm,what do you want now ?

Note that (f)sync is irrelevant at all.
Partial rollback is the problem of only the backend to be rollbacked
except locking.

Vadim has already planned savepoints functionality instead of nested
tx. I have never heard objections to the proposal.
I could see little difference between the implementation of rollback
to arbitrary savepoints and the implemention of rollback only to the
savepoint implicitly placed immediately before current statement.

Do you want another hack ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Thu Feb 24 20:08:05 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 UAA46802
for <pgsql-hackers@postgreSQL.org>;
Thu, 24 Feb 2000 20:07:47 -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 CAA13150;
Fri, 25 Feb 2000 02:06:38 +0100 (MET)
Received: (from grossman@localhost)
by blue.securitas.net (8.9.3/8.9.3) id CAA60091;
Fri, 25 Feb 2000 02:07:13 +0100 (CET)
Date: Fri, 25 Feb 2000 02:07:13 +0100 (CET)
Message-Id: <200002250107.CAA60091@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: Rolf Grossmann <grossman@securitas.net>, Tom Lane <tgl@sss.pgh.pa.us>,
pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0
In-Reply-To: <Pine.LNX.4.21.0002250013250.4316-100000@localhost.localdomain>
References: <200002241644.RAA58547@blue.securitas.net>
<Pine.LNX.4.21.0002250013250.4316-100000@localhost.localdomain>
X-Mailer: VM 6.75 under Emacs 19.34.1

Hi,

on Fri, 25 Feb 2000 00:39:15 +0100 (CET) Peter Eisentraut wrote
concerning "Re: [HACKERS] Re: [BUGS] First experiences with Postgresql 7.0 "
something like this:

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.

I considered that briefly, but dismissed it equally fast. psql is a shell
to the PostgreSQL backend, if you will, not to the system. It's optimized
as a batch processor and for being called from shell scripts, not for
being a programming language of it's own. (In the future it would be nice
to have a PL/Pgsql based front-end available for that sort of stuff.)

Well, if you're saying psql is a shell, then maybe we should consider moving
in that direction. Not everything that's called with #! is a shell to the
system. The most notable example is probably perl, but there are other
programs like sed or awk that are being used with #! but certainly nobody
ever considered using awk as a system shell ;)

As for the programming language: You're already going in that direction
by implementing something like pl/sql. Now if that was available from
psql you're already way down the programming language road.

Just some thoughts ...
Rolf

From bouncefilter Fri Feb 25 00:00:11 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 XAA67294;
Thu, 24 Feb 2000 23:59:20 -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 XAA06349;
Thu, 24 Feb 2000 23:59:19 -0500 (EST)
To: Michael Meskes <meskes@postgreSQL.org>
cc: pgsql-hackers@postgreSQL.org
Subject: ecpg seems rather badly broken
Date: Thu, 24 Feb 2000 23:59:19 -0500
Message-ID: <6346.951454759@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

In current sources, ecpg doesn't compile anymore:

make[3]: Entering directory `/home/postgres/pgsql/src/interfaces/ecpg/lib'
make[3]: *** No rule to make target `dynamic.c', needed by `ecpglib.o'.
gcc -I../../../include -I../../../backend -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -O -g -I../include -I../../../interfaces/libpq -fPIC -c -o typename.o typename.c
gcc -I../../../include -I../../../backend -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -O -g -I../include -I../../../interfaces/libpq -fPIC -c -o descriptor.o descriptor.c
descriptor.c: In function `get_char_item':
descriptor.c:131: dereferencing pointer to incomplete type
descriptor.c:133: dereferencing pointer to incomplete type
descriptor.c:135: dereferencing pointer to incomplete type
descriptor.c:136: dereferencing pointer to incomplete type
descriptor.c:137: dereferencing pointer to incomplete type
make[3]: *** [descriptor.o] Error 1

(similar errors in several additional source files)

regards, tom lane

From bouncefilter Fri Feb 25 01:42:09 2000
Received: from mecomb.com (gw.mecomb.com [161.142.249.98])
by hub.org (8.9.3/8.9.3) with ESMTP id BAA73055;
Fri, 25 Feb 2000 01:41:13 -0500 (EST)
(envelope-from lylyeoh@mecomb.com)
Received: (from mail@localhost) by mecomb.com (8.8.7/8.8.7) id OAA06124;
Fri, 25 Feb 2000 14:41:12 +0800
Received: from <lylyeoh@mecomb.com> (ilab2.mecomb.po.my [192.168.3.22]) by
ns.mecomb.com via smap (V2.1)
id xma006121; Fri, 25 Feb 00 14:41:04 +0800
Message-Id: <3.0.5.32.20000225144132.008c4df0@pop.mecomb.po.my>
X-Sender: lylyeoh@pop.mecomb.po.my
X-Mailer: QUALCOMM Windows Eudora Light Version 3.0.5 (32)
Date: Fri, 25 Feb 2000 14:41:32 +0800
To: kdebisschop@range.infoplease.com, pgsql-hackers@postgreSQL.org,
pgsql-general@postgreSQL.org
From: Lincoln Yeoh <lylyeoh@mecomb.com>
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
In-Reply-To: <200002241916.OAA07901@skillet.infoplease.com>
References: <38B57564.46BB28C0@mindspring.com>
<38B27760.DB921B57@sferacarta.com> <23935.951237171@sss.pgh.pa.us>
<38B3E365.D2B61959@sferacarta.com>
<200002231516.KAA23839@skillet.infoplease.com>
<38B57564.46BB28C0@mindspring.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 02:16 PM 24-02-2000 -0500, Karl DeBisschop wrote:

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)");
$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;

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.

Maybe I don't understand the situation. But it doesn't seem to be a big
problem.

With postgres you have ensure that your application filters the data
properly before sticking it into the database. Then if the insert fails,
it's probably a serious database problem and in that case it's best that
the whole transaction is aborted anyway.

It indeed is a problem if the database engine is expected to parse the
data. For example - if you send in a date value, and the database engine
chokes on it. With the nonpostgresql behaviour you can still insert a NULL
instead for "Bad date/ Unknown date".

But from the security point of view it is best to reduce the amount of
parsing done by the database engine. Make sure the app sanitises and
massages everything so that the database has no problems with the data. It
can be a pain sometimes to figure out what the database can take (which is
why I've been asking for the limits for Postgresql fields and such- so the
app can keep everything within bounds or grumble to the user/vandal). Once
everything is set up nicely, if the database grumbles then the app screwed
up somehow (the vandal got through) and it's best to rollback everything
(we're lucky if the database just grumbled).

Cheerio,

Link.

From bouncefilter Fri Feb 25 02:07:09 2000
Received: from athene.i.eunet.no (IDENT:postfix@athene.i.eunet.no
[193.71.2.52]) by hub.org (8.9.3/8.9.3) with ESMTP id CAA74721
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 02:06:20 -0500 (EST)
(envelope-from tih@Norway.EU.net)
Received: by athene.i.eunet.no (Postfix, from userid 1001)
id 803EF7E3C; Fri, 25 Feb 2000 08:06:15 +0100 (CET)
To: Hannu Krosing <hannu@tm.ee>
Cc: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>,
"'Tom Lane'" <tgl@sss.pgh.pa.us>,
"'hackers'" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: AW: AW: AW: .... (off-topic)
References:
<219F68D65015D011A8E000006F8590C604AF7CFA@sdexcsrv1.f000.d0188.sd.spardat.at>
<38B5B2AA.B6F1866@tm.ee>
From: Tom Ivar Helbekkmo <tih@KPNQwest.NO>
Date: 25 Feb 2000 08:06:14 +0100
In-Reply-To: Hannu Krosing's message of "Fri, 25 Feb 2000 00:37:30 +0200"
Message-ID: <86ema1g3rt.fsf@athene.i.eunet.no>
Lines: 12
X-Mailer: Gnus v5.6.44/Emacs 20.3
Sender: tih@Norway.EU.net

Hannu Krosing <hannu@tm.ee> writes:

Does anyone know how to teach netscape navigators mail client to
recognize additional (national) forms of Re:

The right solution is for people who use Microsoft Outlook to upgrade
to the latest version, where Microsoft (incredible as it sounds) has
fixed this bug. Outlook now generates the correct "Re: " prefix.

-tih
--
Popularity is the hallmark of mediocrity. --Niles Crane, "Frasier"

From bouncefilter Fri Feb 25 06:00:12 2000
Received: from feivel.fam-meskes.de (p3E9B988F.dip0.t-ipconnect.de
[62.155.152.143]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA01218
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 05:59:25 -0500 (EST)
(envelope-from michael@fam-meskes.de)
Received: by feivel.fam-meskes.de (Postfix, from userid 1000)
id 6A2052BB3B; Fri, 25 Feb 2000 09:02:37 +0100 (CET)
Date: Fri, 25 Feb 2000 09:02:37 +0100
From: Michael Meskes <meskes@postgreSQL.org>
To: pgsql-hackers@postgreSQL.org
Subject: Re: ecpg seems rather badly broken
Message-ID: <20000225090237.A9258@fam-meskes.de>
Mail-Followup-To: pgsql-hackers@postgreSQL.org
References: <6346.951454759@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0.1i
In-Reply-To: <6346.951454759@sss.pgh.pa.us>;
from tgl@sss.pgh.pa.us on Thu, Feb 24, 2000 at 11:59:19PM -0500
Sender: michael@fam-meskes.de

On Thu, Feb 24, 2000 at 11:59:19PM -0500, Tom Lane wrote:

In current sources, ecpg doesn't compile anymore:
...

Oops. The last commit left out the changes to one include file. Will commit
this ASAP.

Michael

--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De | Use PostgreSQL!

From bouncefilter Fri Feb 25 06:00:12 2000
Received: from feivel.fam-meskes.de (p3E9B988F.dip0.t-ipconnect.de
[62.155.152.143]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA01217
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 05:59:25 -0500 (EST)
(envelope-from michael@fam-meskes.de)
Received: by feivel.fam-meskes.de (Postfix, from userid 1000)
id D3FDA2BB3D; Fri, 25 Feb 2000 09:03:53 +0100 (CET)
Date: Fri, 25 Feb 2000 09:03:53 +0100
From: Michael Meskes <meskes@postgresql.org>
To: PostgreSQL Hacker <pgsql-hackers@postgresql.org>
Subject: Compile woes
Message-ID: <20000225090353.A9494@fam-meskes.de>
Mail-Followup-To: PostgreSQL Hacker <pgsql-hackers@postgresql.org>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0.1i
Sender: michael@fam-meskes.de
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from quoted-printable to 8bit by hub.org id GAA01292

It seems I cannot compile the backend anymore. Here's what I get after make
distclean; configure; make:

...
gcc -I../../../include -I../../../backend -O2 -Wall -Wmissing-prototypes -Wmissing-declarations -I../.. -c -o tupdesc.o tupdesc.c
ld -r -o SUBSYS.o heaptuple.o indextuple.o indexvalid.o printtup.o scankey.o tupdesc.o
make[3]: Leaving directory /home/postgres/pgsql/src/backend/access/common'
make -C gist SUBSYS.o
make[3]: Entering directory /home/postgres/pgsql/src/backend/access/gist'
make[3]: *** No rule to make target ../../../include/utils/dt.h', needed by
ist.o'. Stop.
make[3]: Leaving directory /home/postgres/pgsql/src/backend/access/gist'
make[2]: *** [submake] Error 2
make[2]: Leaving directory /home/postgres/pgsql/src/backend/access'
make[1]: *** [access.dir] Error 2
make[1]: Leaving directory /home/postgres/pgsql/src/backend'
make: *** [all] Error 2

Newly updated archive.

Michael
--
Michael Meskes | Go SF 49ers!
Th.-Heuss-Str. 61, D-41812 Erkelenz | Go Rhein Fire!
Tel.: (+49) 2431/72651 | Use Debian GNU/Linux!
Email: Michael@Fam-Meskes.De | Use PostgreSQL!

From bouncefilter Fri Feb 25 04:53:11 2000
Received: from ns.plaut.de (ns.plaut.de [194.39.177.166])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA94686
for <hackers@postgreSQL.org>; Fri, 25 Feb 2000 04:52:17 -0500 (EST)
(envelope-from root@nihil.plaut.de)
Received: (from uucp@localhost)
by ns.plaut.de (8.9.3/8.9.3) with UUCP id KAA51365
for hackers@postgreSQL.org; Fri, 25 Feb 2000 10:51:43 +0100 (CET)
(envelope-from root@nihil.plaut.de)
Received: from localhost (root@localhost)
by nihil.plaut.de (8.9.3/8.8.8) with ESMTP id KAA06055
for <hackers@postgreSQL.org>; Fri, 25 Feb 2000 10:50:58 +0100 (CET)
(envelope-from root@nihil.plaut.de)
Date: Fri, 25 Feb 2000 10:50:58 +0100 (CET)
From: Michael Reifenberger <root@nihil.plaut.de>
To: PostgreSQL Hackers <hackers@postgreSQL.org>
Subject: failing to compile -current on FreeBSD
Message-ID: <Pine.BSF.4.21.0002251049060.5990-100000@nihil.plaut.de>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Hi,
I get:

su-2.01$ gmake MAKE=gmake
gmake -C include all
gmake[1]: Entering directory
`/usr/home/pgtest/pgsql/src/interfaces/ecpg/include'
Nothing to be done.
gmake[1]: Leaving directory `/usr/home/pgtest/pgsql/src/interfaces/ecpg/include'
gmake -C lib all
gmake[1]: Entering directory `/usr/home/pgtest/pgsql/src/interfaces/ecpg/lib'
gmake[1]: *** No rule to make target `dynamic.c', needed by `ecpglib.o'. Stop.
gmake[1]: Leaving directory `/usr/home/pgtest/pgsql/src/interfaces/ecpg/lib'
gmake: *** [all] Error 2

Bye!
----
Michael Reifenberger
Plaut Software GmbH, R/3 Basis

----------------------------------------------------------------
Moray.McConnachie@computing-services.oxford.ac.uk
----- Original Message -----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Alex Howansky <alex@wankwood.com>
Cc: <pgsql-sql@postgreSQL.org>
Sent: Thursday, February 24, 2000 11:57 PM
Subject: Re: [SQL] how to create index on timestamp field in pre v7
database

Alex Howansky <alex@wankwood.com> writes:

My question is, how "equivalent" are these types?

They're the same code: we jacked up the name "timestamp" and rolled

the

old datetime code underneath. Strictly a matter of coming closer to
the SQL standard names for these datatypes.

Can I use datetime_ops to index a timestamp field in a v6.5.3

database?

Similarly, "datetime_ops" in 6.5 is now "timestamp_ops".

As a rule, I'd suggest not bothering with opclasses in index
declarations. The only situation where you need to select one is
where there is more than one possible opclass for the same datatype.
This holds for some of the geometric types, but not for any plain

scalar

types like numerics or date/time types. (You could think of an

opclass

as specifying which sort order the index uses...)

Agreed - but note that pg_dump currently produces CREATE INDEX
statements with opclasses included.

That means running a script created by pg_dump v.6.5.x will fail under
7.0 because there is no index opclass of the type datetime?

Judging from my brief experiments that looks to be the case, anyway.
It looks like replacing all occurrences of datetime with timestamp in
the script works - even for the few functions I have that used the
datetime() function.

Yours,
Moray

From bouncefilter Mon Feb 28 14:38:07 2000
Received: from imcwaw3.plusnet (imcwaw3.polkomtel.com.pl [212.2.96.75])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA60377
for <pgsql-hackers@postgreSQL.org>;
Mon, 28 Feb 2000 14:37:31 -0500 (EST)
(envelope-from andrzej.mazurkiewicz@polkomtel.com.pl)
Received: by imcwaw3.plusnet with Internet Mail Service (5.5.2448.0)
id <F57620LZ>; Mon, 28 Feb 2000 19:12:04 +0100
Message-ID: <13288F4408ADD11186FF0060B06A431303648C64@MSGWAW1>
From: Andrzej Mazurkiewicz <andrzej.mazurkiewicz@polkomtel.com.pl>
To: James.Chalex@informit.com
Cc: "'Bruce Momjian'" <pgman@candle.pha.pa.us>, PostgreSQL-development
<pgsql-hackers@postgreSQL.org>
Subject: [HACKERS] Re: Interested in writing a PostgreSQL article?
Date: Fri, 25 Feb 2000 14:31:15 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain

James,
I would be interested in writing tutorial (a couple of parts) on basic
system design using postgres:
- specification,
- entity-relationship diagram + data flow diagram + state transition diagram
or slightly modified object diagram (CY) + state transition diagram using
Dome522 free case,
- refinements,
- grants and access,
- basic business rules in postgres (functions, triggers, constrains ..)
- preparing data definition (database) SQL scripts;
- preparing basic processing scripts in SQL,
- preparing basic perl/tcl processing scripts,
- preparing simple interface for importing data (data cleaning process),
- preparing of basic user interface (for example CGI script).

I see that in repeatible process starting from simplest case of 3 - 4
objects (tables) being refined to more sofisticated scheme including
successive constructs.

Of course it is a rough idea now. If you are interested I will prepare
detailed table of contents.

Regards,
Andrzej Mazurkiewicz

andrzej@mazurkiewicz.org

www.mazurkiewicz.org

From bouncefilter Fri Feb 25 08:55:14 2000
Received: from web108.yahoomail.com (web108.yahoomail.com [205.180.60.75])
by hub.org (8.9.3/8.9.3) with SMTP id IAA10657
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 08:54:16 -0500 (EST)
(envelope-from neduma@yahoo.com)
Received: (qmail 29237 invoked by uid 60001); 25 Feb 2000 13:55:01 -0000
Message-ID: <20000225135501.29236.qmail@web108.yahoomail.com>
Received: from [203.197.139.10] by web108.yahoomail.com;
Fri, 25 Feb 2000 05:55:01 PST
Date: Fri, 25 Feb 2000 05:55:01 -0800 (PST)
From: Nedu <neduma@yahoo.com>
Reply-To: neduma@yahoo.com
Subject: Problem - Postgres Running continously
To: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Dear sir,

i have one problem in postgres and could u
help me in this regard.?

i am relatively new guy to postgres. since
the guy who handled the postgres is not available.
i'm forced to look into postgres.

Here is the problem, the postgres server
does't stop after certain time [ the query is execuing
for long time ],it's running forever and eating lot of
memory [ up to 18 MB in RAM], so the machine [ also
running httpd ] is become slow. it cannot serve pages
fastly.

i'm using postgres 6.4, perl scripts [ DBI/DBD
], linux redhat 6.0, [ 128 MB Ram ]

the postmaster is started as

$ postmaster -i -S -B 3000

i don't know where the problem lies, is the query is
wrong?, or i'm often connecting postgres thru PERL
DBI,is it wrong? or the database is corrupted.?

I really appreciate if u say anything needful

Very Thanks.

Sincerely,
Ned.

the facts and states of my server is

$ uname -a

Linux saregama 2.2.5-15 #1 Mon Apr 19 21:39:28 EDT
1999 i686 unknown

$ top

2:44pm up 101 days, 7:05, 1 user, load average:
30.69, 30.24, 27.92
78 processes: 45 sleeping, 33 running, 0 zombie, 0
stopped
CPU states: 97.0% user, 2.9% system, 0.0% nice,
0.0% idle
Mem: 128028K av, 99996K used, 28032K free, 614976K
shrd, 2840K buff
Swap: 128484K av, 19764K used, 108720K free
52488K cached

PID USER PRI NI SIZE RSS SHARE STAT LIB %CPU
%MEM TIME COMMAND
6652 postgres 20 0 20644 20M 20124 R 0 7.4
16.0 0:48 postmaster
6714 postgres 16 0 20524 19M 20000 R 0 5.5
15.9 0:42 postmaster
5122 postgres 20 0 21256 20M 20696 R 0 4.7
16.2 3:23 postmaster
5209 postgres 10 0 21216 20M 20676 R 0 4.3
16.2 3:13 postmaster
6382 postgres 10 0 20608 19M 20040 R 0 4.3
15.7 1:08 postmaster
6721 postgres 10 0 22128 21M 21612 R 0 4.3
17.2 0:42 postmaster
5029 postgres 10 0 21212 20M 20664 R 0 4.1
16.2 3:39 postmaster
5043 postgres 10 0 21212 20M 20656 R 0 4.1
16.2 3:39 postmaster
5056 postgres 10 0 21216 20M 20672 R 0 4.1
16.2 3:33 postmaster
6186 postgres 10 0 21136 20M 20572 R 0 4.1
16.1 1:21 postmaster
6651 postgres 10 0 20688 20M 20168 R 0 4.1
16.1 0:48 postmaster
7253 saregama 10 0 2064 2064 992 R 0 4.1
1.6 0:00 saregama
6339 postgres 10 0 20844 19M 20284 R 0 3.9
15.9 1:08 postmaster
7254 saregama 10 0 2064 2064 992 R 0 3.9
1.6 0:00 saregama
7030 postgres 10 0 18888 18M 18368 R 0 3.7
14.7 0:18 postmaster
5025 postgres 9 0 21212 20M 20660 R 0 3.5
16.2 3:43 postmaster
5079 postgres 9 0 21212 20M 20656 R 0 3.3
16.2 3:34 postmaster
6214 postgres 10 0 21212 20M 20656 R 0 3.3
16.2 1:14 postmaster
3991 postgres 10 0 21412 20M 20716 R 0 3.1
16.3 21:48 postmaster
4549 postgres 10 0 21208 20M 20664 R 0 3.1
16.2 9:51 postmaster
5232 postgres 15 0 21212 20M 20668 R 0 2.9
16.2 3:12 postmaster
4879 postgres 9 0 21212 20M 20652 R 0 2.7
16.2 5:24 postmaster
6483 postgres 9 0 21204 20M 20644 R 0 2.7
16.2 0:50 postmaster
6991 postgres 9 0 19572 19M 19052 R 0 2.3
15.2 0:20 postmaster
7255 root 10 0 892 892 640 R 0 1.7
0.6 0:00 xkbcomp

$ ps xafw

2832 ? S 0:00 postmaster -i -S -B 2500
3991 ? R 21:38 \_ [postmaster]
4549 ? R 9:41 \_ [postmaster]
4557 ? R 9:03 \_ [postmaster]
4879 ? R 5:14 \_ [postmaster]
4916 ? R 4:33 \_ [postmaster]
5025 ? R 3:33 \_ [postmaster]
5029 ? R 3:29 \_ [postmaster]
5043 ? R 3:29 \_ [postmaster]
5056 ? R 3:23 \_ [postmaster]
5079 ? R 3:23 \_ [postmaster]
5122 ? R 3:13 \_ [postmaster]
5193 ? R 3:04 \_ [postmaster]
5209 ? R 3:03 \_ [postmaster]
5232 ? R 3:02 \_ [postmaster]
6184 ? R 1:10 \_ [postmaster]
6186 ? R 1:11 \_ [postmaster]
6214 ? R 1:04 \_ [postmaster]
6339 ? R 0:58 \_ [postmaster]
6373 ? R 0:51 \_ [postmaster]
6382 ? R 0:57 \_ [postmaster]
6483 ? R 0:40 \_ [postmaster]
6651 ? R 0:38 \_
/usr/local/pgsql/bin/postgres localhost yantra rp
6652 ? R 0:38 \_
/usr/local/pgsql/bin/postgres localhost yantra rp
6714 ? R 0:32 \_
/usr/local/pgsql/bin/postgres localhost yantra rp
6721 ? R 0:32 \_
/usr/local/pgsql/bin/postgres localhost postgres
6766 ? R 0:28 \_
/usr/local/pgsql/bin/postgres localhost yantra rp
6962 ? R 0:13 \_
/usr/local/pgsql/bin/postgres localhost postgres
6991 ? R 0:10 \_
/usr/local/pgsql/bin/postgres localhost yantra rp
7030 ? R 0:07 \_
/usr/local/pgsql/bin/postgres localhost yantra rp

__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com

From bouncefilter Fri Feb 25 17:04:33 2000
Received: from hotmail.com (f217.law7.hotmail.com [216.33.237.217])
by hub.org (8.9.3/8.9.3) with SMTP id RAA66596
for <pgsql-docs@postgresql.org>; Fri, 25 Feb 2000 17:04:09 -0500 (EST)
(envelope-from jemonje@hotmail.com)
Received: (qmail 61434 invoked by uid 0); 25 Feb 2000 22:03:37 -0000
Message-ID: <20000225220337.61433.qmail@hotmail.com>
Received: from 216.72.5.201 by www.hotmail.com with HTTP;
Fri, 25 Feb 2000 14:03:37 PST
X-Originating-IP: [216.72.5.201]
From: "Joaquin Eduardo Monje" <jemonje@hotmail.com>
To: pgsql-hackers@postgresql.org, pgsql-ports@postgresql.org,
pgsql-docs@postgresql.org
Subject: subscribe
Date: Fri, 25 Feb 2000 14:03:37 PST
Mime-Version: 1.0
Content-Type: text/plain; format=flowed

subscribe
______________________________________________________
Get Your Private, Free Email at http://www.hotmail.com

"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:

Agreed - but note that pg_dump currently produces CREATE INDEX
statements with opclasses included.

Right, as it should since its purpose is to ensure you rebuild exactly
the same database. I was just opining that handwritten CREATE INDEXes
usually can omit the opclass. (BTW, I fixed the problem with functional
indexes needing an explicit opclass spec last night.)

That means running a script created by pg_dump v.6.5.x will fail under
7.0 because there is no index opclass of the type datetime?

An embarrassing problem. We are going to work around this by having
the 7.0 parser discard the word "datetime" if it sees it in the opclass
position. There are a couple of other now-dead opclass names that will
be discarded in the same way. Klugy, but it will get the job done for
reading old dump files.

(This hack is not in 7.0beta1, but will be in beta2.)

regards, tom lane

From bouncefilter Fri Feb 25 11:37:16 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 LAA27935
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 11:36:45 -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 LAA07622;
Fri, 25 Feb 2000 11:36:24 -0500 (EST)
To: "Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: missing function datetime()
In-reply-to: <006e01bf7f81$b9a91d40$760e01a3@oucs.ox.ac.uk>
References: <Pine.LNX.4.21.0002241604480.17558-100000@net-srv-0001.bvrd.com>
<23337.951436670@sss.pgh.pa.us>
<006e01bf7f81$b9a91d40$760e01a3@oucs.ox.ac.uk>
Comments: In-reply-to "Moray McConnachie"
<moray.mcconnachie@computing-services.oxford.ac.uk>
message dated "Fri, 25 Feb 2000 11:16:10 +0000"
Date: Fri, 25 Feb 2000 11:36:23 -0500
Message-ID: <7619.951496583@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

moray.mcconnachie@computing-services.oxford.ac.uk writes:

It looks like replacing all occurrences of datetime with timestamp in
the script works - even for the few functions I have that used the
datetime() function.

Oooh --- another case we didn't think about. We really should continue
to make a function named datetime() available, even though the type it
produces will officially be named timestamp.

I can see two ways to do that: either add another function to pg_proc
(five of them actually :-(), or hack the parser to translate a function
name 'datetime' to 'timestamp'. Ugly as the second one sounds, it has
a couple of advantages. First, it would provide an automatic upgrade
path: future dumps of 7.0 databases would show the correct function
name, at least for uses in rules. So we could hope to get rid of the
compatibility hack someday. Second, the parser has special treatment
for functions that are named the same as datatypes --- it knows they
represent type coercions --- so a function named "datetime" won't really
work quite the way it should for type resolution.

Not sure which way to jump. Comments anyone?

regards, tom lane

From bouncefilter Fri Feb 25 11:48:16 2000
Received: from web2903.mail.yahoo.com (web2903.mail.yahoo.com [128.11.68.46])
by hub.org (8.9.3/8.9.3) with SMTP id LAA29382
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 11:47:32 -0500 (EST)
(envelope-from rinid@rocketmail.com)
Received: (qmail 18955 invoked by uid 60001); 25 Feb 2000 16:47:31 -0000
Message-ID: <20000225164731.18954.qmail@web2903.mail.yahoo.com>
Received: from [206.114.238.111] by web2903.mail.yahoo.com;
Fri, 25 Feb 2000 08:47:31 PST
Date: Fri, 25 Feb 2000 08:47:31 -0800 (PST)
From: Rini Dutta <rinid@rocketmail.com>
Subject: Using libpq in a multithreaded environment
To: pgsql-interfaces@postgresql.org
Cc: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

Hi,
I need to use libpq in a multithreaded environment.
Ideally I would like to open a single connection to
the database and use that for all threads.
I just discovered that the PGconn returned when a
connection is opened is usable for only one thread
since it also seems to store the result (PGresult *)
and the errorMessage. Could you tell me if there is a
way to use one connection in multiple threads, or
whether I will need to necessarily open one Connection
per thread ? Which is the preferred course of action ?

Thanks,
Rini

ps : Sorry, but I've been unable to search the
archives before sending this mail. For the last three
days each time I try a search I get the folllowing
message :

The document contained no data. Try again later or
contact the server's administrator.
__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com

"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:

Agreed - but note that pg_dump currently produces CREATE INDEX
statements with opclasses included.

Right, as it should since its purpose is to ensure you rebuild exactly
the same database. I was just opining that handwritten CREATE INDEXes
usually can omit the opclass. (BTW, I fixed the problem with functional
indexes needing an explicit opclass spec last night.)

That means running a script created by pg_dump v.6.5.x will fail under
7.0 because there is no index opclass of the type datetime?

An embarrassing problem. We are going to work around this by having
the 7.0 parser discard the word "datetime" if it sees it in the opclass
position. There are a couple of other now-dead opclass names that will
be discarded in the same way. Klugy, but it will get the job done for
reading old dump files.

(This hack is not in 7.0beta1, but will be in beta2.)

It is my understanding we are generating a new beta every night, so it
should be there 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 Fri Feb 25 13:20:17 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 NAA39198;
Fri, 25 Feb 2000 13:19: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 NAA07805;
Fri, 25 Feb 2000 13:19:35 -0500 (EST)
To: Rini Dutta <rinid@rocketmail.com>
cc: pgsql-interfaces@postgreSQL.org, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Using libpq in a multithreaded environment
In-reply-to: <20000225164731.18954.qmail@web2903.mail.yahoo.com>
References: <20000225164731.18954.qmail@web2903.mail.yahoo.com>
Comments: In-reply-to Rini Dutta <rinid@rocketmail.com>
message dated "Fri, 25 Feb 2000 08:47:31 -0800"
Date: Fri, 25 Feb 2000 13:19:34 -0500
Message-ID: <7802.951502774@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Rini Dutta <rinid@rocketmail.com> writes:

I need to use libpq in a multithreaded environment.
Ideally I would like to open a single connection to
the database and use that for all threads.

Not unless you provide some interlock that will prevent multiple threads
from issuing queries at the same time...

I just discovered that the PGconn returned when a
connection is opened is usable for only one thread
since it also seems to store the result (PGresult *)
and the errorMessage.

This is the least of your worries --- the backend is not multithreaded
either, so it will not accept concurrent queries; nor is the frontend-
to-backend protocol capable of keeping track of concurrent queries.

Unless your intended use of the connection is considerably more
restricted than you have indicated, you will want one connection
per query-issuing thread.

This would not prevent you from handing off completed PGresults to
other threads for processing; but operations on a PGconn should be
either restricted to one thread or protected by a mutex.

regards, tom lane

From bouncefilter Fri Feb 25 14:10:18 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 OAA42930
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 14:09:18 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id NAA41253
for pgsql-hackers@postgresql.org; Fri, 25 Feb 2000 13:44:10 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
Reply-To: "M. Brady" <spinaches@vo.lu>
From: "M. Brady" <mrbrady@vo.lu>
X-Newsgroups: anhalt.hackerecke, apana.lists.os.freebsd.hackers,
citycs.list.freebsd.hackers, clinet.list.amanda-hackers,
clinet.list.freebsd-hackers, clinet.list.pgsql-hackers,
cn.bbs.comp.hacker, comp.databases.postgresql.hackers,
comp.hackers
Subject: XML and Security
Date: Fri, 25 Feb 2000 19:44:53 +0100
Lines: 12
X-Newsreader: Microsoft Outlook Express 4.72.3110.5
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3
Message-ID: <38b6cd51$1@news.vo.lu>
X-Trace: 25 Feb 2000 19:43:29 +0100, ppp170.vo.lu
To: pgsql-hackers@postgresql.org

Can anyone provide references (docs, whitepapers, personal opinions, ...)
about the security of XML based systems?
I'm not interested in digital signature only.
I'd like to know how many information hackers can "steal" monitoring the
network.
How many doors does a system open when it is based on XML?

Thanks for any info you might want to send back

MB

From bouncefilter Fri Feb 25 14:28:29 2000
Received: from alert.infoplease.com ([207.121.98.49])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA45961;
Fri, 25 Feb 2000 14:27:23 -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 OAA04178;
Fri, 25 Feb 2000 14:26:47 -0500 (EST)
Received: (from kdebisschop@localhost)
by skillet.infoplease.com (8.9.3/8.9.1) id OAA03223;
Fri, 25 Feb 2000 14:26:48 -0500
Date: Fri, 25 Feb 2000 14:26:48 -0500
Message-Id: <200002251926.OAA03223@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: lylyeoh@mecomb.com
CC: pgsql-hackers@postgreSQL.org, pgsql-general@postgreSQL.org
In-reply-to: <3.0.5.32.20000225144132.008c4df0@pop.mecomb.po.my> (message from
Lincoln Yeoh on Fri, 25 Feb 2000 14:41:32 +0800)
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Reply-to: kdebisschop@range.infoplease.com
References: <38B57564.46BB28C0@mindspring.com>
<38B27760.DB921B57@sferacarta.com> <23935.951237171@sss.pgh.pa.us>
<38B3E365.D2B61959@sferacarta.com>
<200002231516.KAA23839@skillet.infoplease.com>
<38B57564.46BB28C0@mindspring.com>
<3.0.5.32.20000225144132.008c4df0@pop.mecomb.po.my>

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)");
$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;

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.

Maybe I don't understand the situation. But it doesn't seem to be a big
problem.

With postgres you have ensure that your application filters the data
properly before sticking it into the database. Then if the insert fails,
it's probably a serious database problem and in that case it's best that
the whole transaction is aborted anyway.

This reason this idiom is used has nothing to do with validation. I
agree that the application has the resopnsibility to cehck for valid
data.

The usefulness of the idion is that in a mutli-user environment, this
is a basic way to update data that may or may not already have a key
in the table. You can't do a "SELECT COUNT" because in the time
between when you SELECT and INSERT (assuming the key is not already
there) someone may have done a separate insert. The only other way I
know to do this is to lock the entire table against INSERTs which has
obvious performance effects.

--
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 Fri Feb 25 15:14:52 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 PAA50743
for <pgsql-general@postgreSQL.org>;
Fri, 25 Feb 2000 15:13:32 -0500 (EST)
(envelope-from keithmur@mindspring.com)
Received: from mindspring.com (pool-207-205-239-233.atln.grid.net
[207.205.239.233])
by fb01.eng00.mindspring.net (8.9.3/8.8.5) with ESMTP id PAA07712
for <pgsql-general@postgreSQL.org>;
Fri, 25 Feb 2000 15:13:30 -0500 (EST)
Message-ID: <38B6E390.37E02A04@mindspring.com>
Date: Fri, 25 Feb 2000 14:18:24 -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
To: 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>
<38B57564.46BB28C0@mindspring.com>
<200002241916.OAA07901@skillet.infoplease.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Karl DeBisschop wrote:

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.

I don't have the SQL92 standard with me, so I can't speak to how it
defines atomicity. Seems to me it's a means to an end, though, the end
being that all of the statements in the sequence are performed, or
none. But if the program traps an error, then does something to
recover, you could argue that it's changed the sequence.

As long as the program has to explicitly Commit, why not? It seems
desirable to me that if one statement causes an error, it doesn't affect
the database, and the error is returned to the client. If the client
has RaiseError on, which he should, and doesn't do anything to
explicitly trap, it's going to blow out the program and thus the
transaction should be rolled back, which is a good thing. But if he
does explicitly trap, as I do above, why not let him stay within the
transaction, since the statement in error has not done anything?

I agree that do get Postgresql to do this might be a lot to expect
(nested transactions are required, I guess). I'm just not sure that
it's a *wrong*, or non-conformant, thing to expect.

(By the way, I know VB/Access does it this way. My production code,
however, never takes advantage of this, to my knowledge.)

Addressing Lincoln Yeoh's point in another post, to take the approach
that all your data should conform to all database requirements before
you enter a transaction seems to me to lead to redundancy: the program
code checks and the database checks. Should you have to synchronize all
relevant code every time a field requirement is changed?

I agree that to simply continue without error and let the program
blindly commit, which some folks claim other databases do, is wrong and
screws atomicity.

What is also wrong is to allow you to do a Commit when the database is
in an error state, so that you have (in this case) a table in limbo that
can't be created or seen, behavior that Jose Soares and I both saw with
Postgresql (6.5.1 in my case). Why shouldn't Postgresql just implicitly
Rollback at this point, since you can't do anything (constructive) to
the database within the transaction anyway?

From bouncefilter Fri Feb 25 15:49:19 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 PAA55751
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 15:48:24 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12ORTW-0003kgC; Fri, 25 Feb 100 21:36 MET
Message-Id: <m12ORTW-0003kgC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: LZTEXT for rule plan stings
To: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Date: Fri, 25 Feb 2000 21:36:34 +0100 (CET)
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

Hi,

there was discussion about readding the lztext type for
internal use in pg_rewrite for 7.0, then remove it again once
we have TOAST.

Was because significant growth of the rule plan strings due
to other changes can cause views/rules to be rejected by 7.0,
that easily worked with 6.5.

I've reconstructed the entire lztext type now from CVS, could
reapply changes and reactivate deleted files from ./Attic.
Will work on a NOTICE/ERROR message now, preventing users to
use it in their schemas.

But it requires an initdb and we're in BETA. So I better ask
if someone complains.

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 Fri Feb 25 14:54:18 2000
Received: from picasso.realtyideas.com (IDENT:kaiq@207-18-128-210.flex.net
[207.18.128.210] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id OAA48697
for <pgsql-general@postgreSQL.org>;
Fri, 25 Feb 2000 14:53:37 -0500 (EST)
(envelope-from kaiq@picasso.realtyideas.com)
Received: from localhost (kaiq@localhost)
by picasso.realtyideas.com (8.9.3/8.9.3) with ESMTP id OAA17673;
Fri, 25 Feb 2000 14:49:19 -0600
Date: Fri, 25 Feb 2000 14:49:19 -0600 (CST)
From: <kaiq@realtyideas.com>
To: Karl DeBisschop <kdebisschop@range.infoplease.com>
cc: lylyeoh@mecomb.com, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
In-Reply-To: <200002251926.OAA03223@skillet.infoplease.com>
Message-ID:
<Pine.LNX.4.10.10002251447090.16512-100000@picasso.realtyideas.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 25 Feb 2000, Karl DeBisschop wrote:

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)");
$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;

The usefulness of the idion is that in a mutli-user environment, this
is a basic way to update data that may or may not already have a key
in the table. You can't do a "SELECT COUNT" because in the time
between when you SELECT and INSERT (assuming the key is not already
there) someone may have done a separate insert. The only other way I
know to do this is to lock the entire table against INSERTs which has
obvious performance effects.

sounds right, but ;-) why you use the transaction in the first place?

From bouncefilter Fri Feb 25 16:15:19 2000
Received: from thelab.hub.org (nat196.115.mpoweredpc.net [142.177.196.115])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA57556
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 16:14:28 -0500 (EST) (envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id RAA07213;
Fri, 25 Feb 2000 17:14:25 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 25 Feb 2000 17:14:25 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Jan Wieck <wieck@debis.com>
cc: PostgreSQL HACKERS <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-Reply-To: <m12ORTW-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Message-ID: <Pine.BSF.4.21.0002251713440.81087-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 25 Feb 2000, Jan Wieck wrote:

Hi,

there was discussion about readding the lztext type for
internal use in pg_rewrite for 7.0, then remove it again once
we have TOAST.

Was because significant growth of the rule plan strings due
to other changes can cause views/rules to be rejected by 7.0,
that easily worked with 6.5.

I've reconstructed the entire lztext type now from CVS, could
reapply changes and reactivate deleted files from ./Attic.
Will work on a NOTICE/ERROR message now, preventing users to
use it in their schemas.

But it requires an initdb and we're in BETA. So I better ask
if someone complains.

this close to the beginning of beta, I would say go for it ... the
benefits of doing so, as I undesrtand it, *far* outweighs the disadvantage
of requiring an initdb ...

From bouncefilter Fri Feb 25 16:34:19 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 QAA58982
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 16:33:33 -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 NAA18800;
Fri, 25 Feb 2000 13:32:57 -0800 (PST)
Message-Id: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Fri, 25 Feb 2000 13:29:40 -0800
To: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-Reply-To: <m12ORTW-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 09:36 PM 2/25/00 +0100, Jan Wieck wrote:

But it requires an initdb and we're in BETA. So I better ask
if someone complains.

Well...here's an example of a view that worked in 6.5, with an 8KB
block size, that fails in 7.0 unless I build with a 16KB block size:

create view ec_products_displayable
as
select * from ec_products
where active_p='t';

Impressively large, eh? :) I was kinda grossed out when Postgres
choked on it, to be honest.

ec_products in this case has quite a few columns...

You know, I've investigated further and the rule string itself is
no where near 8KB. More like 1KB. So there is more to the story
than just the string itself.

Still, it should help because most of my failing views were just
a bit over 8KB. One still fails with a 16KB block size, though!
Fortunately it's not currently used in the web tool kit.

Anyway, it seems to me that we need SOME solution to this problem.
It is going to be hard to convince users that views like the one
above are really too complex for Postgres to handle.

I still like the idea of "text" being implemented under the hood
as lzText for a quick 7.1 release if that idea works out ...

- 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 Fri Feb 25 16:59:25 2000
Received: from alert.infoplease.com ([207.121.98.49])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA66113
for <pgsql-general@postgreSQL.org>;
Fri, 25 Feb 2000 16:59:19 -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 QAA04974;
Fri, 25 Feb 2000 16:58:40 -0500 (EST)
Received: (from kdebisschop@localhost)
by skillet.infoplease.com (8.9.3/8.9.1) id QAA07583;
Fri, 25 Feb 2000 16:58:42 -0500
Date: Fri, 25 Feb 2000 16:58:42 -0500
Message-Id: <200002252158.QAA07583@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: kaiq@realtyideas.com
CC: lylyeoh@mecomb.com, pgsql-general@postgreSQL.org
In-reply-to:
<Pine.LNX.4.10.10002251447090.16512-100000@picasso.realtyideas.com>
(kaiq@realtyideas.com)
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
Reply-to: kdebisschop@range.infoplease.com
References:
<Pine.LNX.4.10.10002251447090.16512-100000@picasso.realtyideas.com>

From: <kaiq@realtyideas.com>
On Fri, 25 Feb 2000, Karl DeBisschop wrote:

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)");
$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;

The usefulness of the idion is that in a mutli-user environment, this
is a basic way to update data that may or may not already have a key
in the table. You can't do a "SELECT COUNT" because in the time
between when you SELECT and INSERT (assuming the key is not already
there) someone may have done a separate insert. The only other way I
know to do this is to lock the entire table against INSERTs which has
obvious performance effects.

sounds right, but ;-) why you use the transaction in the first place?

Rememeber that this is just an example to illustrate what sort of
behaviour one user would find useful in tranasctions, so it is a
little simplistic. Not overly simplistic, though, I think.

I'd want a transaction because I'm doing a bulk insert into this live
database - say syncing in a bunch of data from a slave server while
the master is still running. If one (or more) insert(s) fail, I want
to revert back to the starting pint so I can fix the cause of the
failed insert and try again with the database in a known state.
(there may, for instance, be relationships beteewn the b field such
that if only part of the bulk insert suceeds, the database is rendered
corrupt).

--
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 Fri Feb 25 17:26:21 2000
Received: from alert.infoplease.com ([207.121.98.49])
by hub.org (8.9.3/8.9.3) with ESMTP id RAA68528
for <pgsql-general@postgreSQL.org>;
Fri, 25 Feb 2000 17:25:33 -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 RAA05160;
Fri, 25 Feb 2000 17:24:55 -0500 (EST)
Received: (from kdebisschop@localhost)
by skillet.infoplease.com (8.9.3/8.9.1) id RAA08367;
Fri, 25 Feb 2000 17:24:57 -0500
Date: Fri, 25 Feb 2000 17:24:57 -0500
Message-Id: <200002252224.RAA08367@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: keithmur@mindspring.com
CC: pgsql-general@postgreSQL.org
In-reply-to: <38B6E390.37E02A04@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>
<200002241916.OAA07901@skillet.infoplease.com>
<38B6E390.37E02A04@mindspring.com>

From: "Keith G. Murphy" <keithmur@mindspring.com>

Karl DeBisschop wrote:

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.

I don't have the SQL92 standard with me, so I can't speak to how it
defines atomicity. Seems to me it's a means to an end, though, the end
being that all of the statements in the sequence are performed, or
none. But if the program traps an error, then does something to
recover, you could argue that it's changed the sequence.

I agree

As long as the program has to explicitly Commit, why not? It seems
desirable to me that if one statement causes an error, it doesn't affect
the database, and the error is returned to the client. If the client
has RaiseError on, which he should, and doesn't do anything to
explicitly trap, it's going to blow out the program and thus the
transaction should be rolled back, which is a good thing. But if he
does explicitly trap, as I do above, why not let him stay within the
transaction, since the statement in error has not done anything?

It is not sufficient that the statement in error has done nothing -
the postmaster in the general case cannot know what relationships
should exist between the non-key data. It is quite possible that not
having a record inserted could make the database fundamentally
unusable. Of course, in my original example and in yours, error is
trapped and the situation is (hopefully) fixed by the subsequent
update. Thus, in my post I suggested that postgres could provide some
sort of mechanism to explicitly 'sanitize' the transaction and allow
it to commit.

In otherwords, I think we are basically proposing the same thing.

I agree that do get Postgresql to do this might be a lot to expect
(nested transactions are required, I guess). I'm just not sure that
it's a *wrong*, or non-conformant, thing to expect.

(By the way, I know VB/Access does it this way. My production code,
however, never takes advantage of this, to my knowledge.)

From what I gather, extending postgresql this way is planned anyway -

it may not happen tomorrow, but notheing in here seems like a very new
concept to the development team.

Addressing Lincoln Yeoh's point in another post, to take the approach
that all your data should conform to all database requirements before
you enter a transaction seems to me to lead to redundancy: the program
code checks and the database checks. Should you have to synchronize all
relevant code every time a field requirement is changed?

I agree that to simply continue without error and let the program
blindly commit, which some folks claim other databases do, is wrong and
screws atomicity.

What is also wrong is to allow you to do a Commit when the database is
in an error state, so that you have (in this case) a table in limbo that
can't be created or seen, behavior that Jose Soares and I both saw with
Postgresql (6.5.1 in my case). Why shouldn't Postgresql just implicitly
Rollback at this point, since you can't do anything (constructive) to
the database within the transaction anyway?

Yes, the table in limbo is certainly a problem/bug. But even this
bug, in my estimation, is better than allowing a transaction with an
error in it to commit without explicitly clearing the error status.
The bug is a pain in the neck, but it apparently has been fixed in
6.5.3 -- so why not upgrade, no dumps are required. But even with the
bug, it can save you from unknowingly foisting inaccurate data on your
customers which is still a good thing.

As for whether postgress should implicitly roll back, I don't think it
should - remember that the frontend, which is very likely operating in
robot mode, is still firing queries at the database. An inpmlicit
rollback means starting a new transaction. And that could lead to a
data integrity problem as well.

--
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 Fri Feb 25 17:45:20 2000
Received: from picasso.realtyideas.com (IDENT:kaiq@207-18-128-210.flex.net
[207.18.128.210] (may be forged))
by hub.org (8.9.3/8.9.3) with ESMTP id RAA70935
for <pgsql-general@postgreSQL.org>;
Fri, 25 Feb 2000 17:44:22 -0500 (EST)
(envelope-from kaiq@picasso.realtyideas.com)
Received: from localhost (kaiq@localhost)
by picasso.realtyideas.com (8.9.3/8.9.3) with ESMTP id RAA23109;
Fri, 25 Feb 2000 17:40:09 -0600
Date: Fri, 25 Feb 2000 17:40:09 -0600 (CST)
From: <kaiq@realtyideas.com>
To: Karl DeBisschop <kdebisschop@range.infoplease.com>
cc: lylyeoh@mecomb.com, pgsql-general@postgreSQL.org
Subject: Re: [GENERAL] Re: [HACKERS] TRANSACTIONS
In-Reply-To: <200002252158.QAA07583@skillet.infoplease.com>
Message-ID:
<Pine.LNX.4.10.10002251726100.20593-100000@picasso.realtyideas.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 25 Feb 2000, Karl DeBisschop wrote:

From: <kaiq@realtyideas.com>
On Fri, 25 Feb 2000, Karl DeBisschop wrote:

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)");
$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;

The usefulness of the idion is that in a mutli-user environment, this
is a basic way to update data that may or may not already have a key
in the table. You can't do a "SELECT COUNT" because in the time
between when you SELECT and INSERT (assuming the key is not already
there) someone may have done a separate insert. The only other way I
know to do this is to lock the entire table against INSERTs which has
obvious performance effects.

sounds right, but ;-) why you use the transaction in the first place?

Rememeber that this is just an example to illustrate what sort of
behaviour one user would find useful in tranasctions, so it is a
little simplistic. Not overly simplistic, though, I think.

I'd want a transaction because I'm doing a bulk insert into this live
database - say syncing in a bunch of data from a slave server while
the master is still running. If one (or more) insert(s) fail, I want
to revert back to the starting pint so I can fix the cause of the
failed insert and try again with the database in a known state.
(there may, for instance, be relationships beteewn the b field such
that if only part of the bulk insert suceeds, the database is rendered
corrupt).

thanks. I'm on your side now ;-) -- it is a useful senario.
the question are: 1) can nested transaction be typically interpreted
to handle this situation? If is is, then, it should be handled by that
"advanced feature", not plain transaction ;
2) on the other hand, can sql92's (plain) transaction be interpreted
in the way that above behavior is legitimate?

From bouncefilter Fri Feb 25 19:54:22 2000
Received: from hu.tm.ee (ppp749.tele2.ee [212.107.37.49])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA81048
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 19:53:33 -0500 (EST) (envelope-from hannu@tm.ee)
Received: from tm.ee (localhost [127.0.0.1]) by hu.tm.ee (Postfix) with ESMTP
id 63C6A3B03; Sat, 26 Feb 2000 03:02:22 +0200 (EET)
Sender: hannu@hu.tm.ee
Message-ID: <38B7261E.F2E5356D@tm.ee>
Date: Sat, 26 Feb 2000 03:02:22 +0200
From: Hannu Krosing <hannu@tm.ee>
Organization: Trust-O-Matic =?iso-8859-1?Q?O=DC?=
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.13-7mdk i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Don Baccus <dhogaza@pacifier.com>
Cc: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Don Baccus wrote:

I still like the idea of "text" being implemented under the hood
as lzText for a quick 7.1 release if that idea works out ...

But without TOAST it would result in _undefined_ max tuple length,
which is probably not desirable.

Using it for views is another thing as their max size was undefined
to begin with.

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

From bouncefilter Fri Feb 25 20:19:24 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 UAA82994
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 20:19:03 -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 RAA14742;
Fri, 25 Feb 2000 17:18:10 -0800 (PST)
Message-Id: <3.0.1.32.20000225171552.00f96a10@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Fri, 25 Feb 2000 17:15:52 -0800
To: Hannu Krosing <hannu@tm.ee>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <38B7261E.F2E5356D@tm.ee>
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 03:02 AM 2/26/00 +0200, Hannu Krosing wrote:

Don Baccus wrote:

I still like the idea of "text" being implemented under the hood
as lzText for a quick 7.1 release if that idea works out ...

But without TOAST it would result in _undefined_ max tuple length,
which is probably not desirable.

Boy, I'd sure find it desirable. There's nothing to stop people from
using varchar(8000) or whatever if they want a predictable top limit.
Text is not a standard type, and this wouldn't break standard semantics.

lzText wasn't removed because folks thought it was useless, IIRC,
it was removed because TOAST was an exciting and much more powerful
approach and no one wanted to introduce a new type doomed to disappear
after a single release cycle.

With TOAST, from the user's point of view you'll still have an
_undefined_ max tuple length - the max will just be really, really
large. Sure, the tuples will actually be fixed but large varying
types can be split off into a series of tuples in the TOASTer
oven, so to speak. So I guess I have difficulty understanding
your argument.

If text were implemented as lzText for a quick 7.1, which apparently
was Jan's spin on the idea, then for 7.1 we'd say:

"maximum number of characters you can store in a column of type
text varies"

and after TOAST we'd say:

"maximum number of characters you can store in a column of type
text varies"

Right? The only difference is that the _undefined_ maximum in
the non-TOAST case is "thousands of characters" and in the TOAST
case "gigabytes of characters" but undefined is undefined in my
book.

- 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 Fri Feb 25 20:18:22 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 UAA82882
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 20:17: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 UAA09095;
Fri, 25 Feb 2000 20:17:18 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-reply-to: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Fri, 25 Feb 2000 13:29:40 -0800"
Date: Fri, 25 Feb 2000 20:17:17 -0500
Message-ID: <9092.951527837@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

Well...here's an example of a view that worked in 6.5, with an 8KB
block size, that fails in 7.0 unless I build with a 16KB block size:

create view ec_products_displayable
as
select * from ec_products
where active_p='t';

You know, I've investigated further and the rule string itself is
no where near 8KB. More like 1KB. So there is more to the story
than just the string itself.

Really? That's interesting. Could you send me a test case
(create table and create view commands)?

regards, tom lane

From bouncefilter Fri Feb 25 22:11:27 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 WAA92011
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 22:11:15 -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 TAA21737;
Fri, 25 Feb 2000 19:10:33 -0800 (PST)
Message-Id: <3.0.1.32.20000225171737.00f960b0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Fri, 25 Feb 2000 17:17:37 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <9092.951527837@sss.pgh.pa.us>
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 08:17 PM 2/25/00 -0500, Tom Lane wrote:

Don Baccus <dhogaza@pacifier.com> writes:

Well...here's an example of a view that worked in 6.5, with an 8KB
block size, that fails in 7.0 unless I build with a 16KB block size:

create view ec_products_displayable
as
select * from ec_products
where active_p='t';

You know, I've investigated further and the rule string itself is
no where near 8KB. More like 1KB. So there is more to the story
than just the string itself.

Really? That's interesting. Could you send me a test case
(create table and create view commands)?

I'll try to get to it soon.

- 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 Fri Feb 25 20:19:22 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 UAA82971
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 20:18:35 -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 UAA09113;
Fri, 25 Feb 2000 20:18:31 -0500 (EST)
To: Jan Wieck <wieck@debis.com>
cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-reply-to: <m12ORTW-0003kgC@orion.SAPserv.Hamburg.dsh.de>
References: <m12ORTW-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Comments: In-reply-to wieck@debis.com (Jan Wieck)
message dated "Fri, 25 Feb 2000 21:36:34 +0100"
Date: Fri, 25 Feb 2000 20:18:31 -0500
Message-ID: <9110.951527911@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

wieck@debis.com (Jan Wieck) writes:

Will work on a NOTICE/ERROR message now, preventing users to
use it in their schemas.

I think a NOTICE is sufficient --- people who really need it will
go in and dike out any ERROR anyway ;-)

But it requires an initdb and we're in BETA. So I better ask
if someone complains.

Not me.

regards, tom lane

From bouncefilter Fri Feb 25 22:11:36 2000
Received: from clio.trends.ca (root@clio.trends.ca [209.47.148.2])
by hub.org (8.9.3/8.9.3) with ESMTP id WAA92018
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 22:11:21 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by clio.trends.ca (8.9.3+Sun/8.9.3) with ESMTP id WAA01413
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 22:11:11 -0500 (EST)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id TAA21743;
Fri, 25 Feb 2000 19:10:34 -0800 (PST)
Message-Id: <3.0.1.32.20000225173205.00f926e0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Fri, 25 Feb 2000 17:32:05 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>, Jan Wieck <wieck@debis.com>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <9110.951527911@sss.pgh.pa.us>
References: <m12ORTW-0003kgC@orion.SAPserv.Hamburg.dsh.de>
<m12ORTW-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 08:18 PM 2/25/00 -0500, Tom Lane wrote:

wieck@debis.com (Jan Wieck) writes:

Will work on a NOTICE/ERROR message now, preventing users to
use it in their schemas.

I think a NOTICE is sufficient --- people who really need it will
go in and dike out any ERROR anyway ;-)

It will also be very easy to upgrade to TOAST for those of us who
are knowledgable enough to edit our pg_dumps from "lztext" to "text"
when that feature finally comes out. We'll undoubtably have to
dump and reload anyway :)

- 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 Fri Feb 25 20:34:22 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA85090
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 20:33:53 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:61650 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S337930AbQBZBdM>;
Sat, 26 Feb 2000 02:33:12 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12OW9I-0001Bc-00; Sat, 26 Feb 2000 02:36:00 +0100
Date: Sat, 26 Feb 2000 02:36:00 +0100 (CET)
From: 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: ^C in psql (was Re: [HACKERS] Changes in 7.0)
In-Reply-To: <21988.951409022@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.21.0002251356420.3063-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>

Tom Lane writes:

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

That's trickier than it sounds. If I simply do a longjmp from the signal
handler and do the clean up at the setjmp destination I have no idea what
the state of the output buffer is. Worse yet, PQputline doesn't seem to
cope so well with longjmps. The second alternative is to set a flag in the
signal handler and have handleCopyIn() check that once in a while. But
that leads to some non-obvious behaviour if I'm entering copy data by
hand, such as ^C only taking effect after I press enter, and/or an extra
zero (default) row being inserted. The way it currently looks I can't
guarantee any consistent state either way. The proper solution would
seemingly be to write separate handlers for interactive and file
input. I'll keep that in mind for next time.

For now I could only offer the hard exit in script mode and letting people
enter their own "\." or ^D in interactive mode (i.e., ignore ^C in that
case).

Meanwhile, ^C during COPY OUT seems back on track.

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

From bouncefilter Fri Feb 25 20:35:23 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA85128
for <pgsql-hackers@postgresql.org>;
Fri, 25 Feb 2000 20:34:23 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:61717 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S337943AbQBZBd3>;
Sat, 26 Feb 2000 02:33:29 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12OW9S-0001Bw-00; Sat, 26 Feb 2000 02:36:10 +0100
Date: Sat, 26 Feb 2000 02:36:10 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
cc: Jan Wieck <wieck@debis.com>, Hiroshi Inoue <Inoue@tpf.co.jp>,
Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Cache query implemented
In-Reply-To: <Pine.LNX.3.96.1000224175739.17426A-100000@ara.zf.jcu.cz>
Message-ID: <Pine.LNX.4.21.0002251507370.3063-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>

Karel Zak - Zakkr writes:

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

SQL seems to have something like the following. (Note: The section on
dynamic SQL is mostly incomprehensible to me.)

PREPARE name AS query
DESCRIBE INPUT name [ USING x, ... ]
DESCRIBE [OUTPUT] name [ USING x, ... ]
EXECUTE name [ INTO x, y, ... ] [ USING a, b, ... ]
DEALLOCATE PREPARE name

I'm not sure if these match exactly what you're doing, but if it is at all
possible to match what you're doing to these, I'd say it would be a shame
not to do it. You've got time.

Meanwhile I'm wondering whether it would not be possible to provide the
plan caching functionality even if all you do is send the same SELECT
twice in a row. Might be tricky, of course.

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

From bouncefilter Fri Feb 25 22:11:27 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 WAA92009
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 22:11:15 -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 TAA21749;
Fri, 25 Feb 2000 19:10:36 -0800 (PST)
Message-Id: <3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Fri, 25 Feb 2000 17:54:43 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <9092.951527837@sss.pgh.pa.us>
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 08:17 PM 2/25/00 -0500, Tom Lane wrote:

Really? That's interesting. Could you send me a test case
(create table and create view commands)?

Normally, I wouldn't post the test case to the whole group
but figured folks might find this interesting. It's not all
that complex a table and the view of course is very simple.

Now...this is running on a snapshot from last weekend, just
before you fixed the pg_dump/reload problem associated with
column alias and views. I tried downloading the latest sources
via CVS and got bit by the "it don't compile" problem others
have complained about earlier today (ecpg). Here's the test
case:

create table ec_products (
product_id integer not null primary key,
sku varchar(100),
product_name varchar(200),
creation_date datetime default current_timestamp not null,
one_line_description varchar(400),
detailed_description varchar(4000),
search_keywords varchar(4000),
price numeric,
shipping numeric,
shipping_additional numeric,
weight float4,
dirname varchar(200),
present_p char(1) check (present_p in ('f','t')) default 't',
active_p char(1) check (active_p in ('f','t')) default 't',
available_date datetime default current_timestamp not null,
announcements varchar(4000),
announcements_expire datetime,
url varchar(300),
template_id integer,
stock_status char(1) check (stock_status in ('o','q','m','s','i')),
last_modified datetime not null,
last_modifying_user integer not null,
modified_ip_address varchar(20) not null
);

create view ec_products_displayable
as
select * from ec_products e
where active_p='t';

- 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 Fri Feb 25 22:19:25 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 WAA92586
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 22:18:23 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12OXYT-0003kgC; Sat, 26 Feb 100 04:06 MET
Message-Id: <m12OXYT-0003kgC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-Reply-To: <3.0.1.32.20000225171552.00f96a10@mail.pacifier.com> from Don
Baccus at "Feb 25, 2000 05:15:52 pm"
To: Don Baccus <dhogaza@pacifier.com>
Date: Sat, 26 Feb 2000 04:06:05 +0100 (CET)
CC: Hannu Krosing <hannu@tm.ee>, Jan Wieck <wieck@debis.com>,
PostgreSQL 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

Don Baccus wrote:

At 03:02 AM 2/26/00 +0200, Hannu Krosing wrote:

Don Baccus wrote:

I still like the idea of "text" being implemented under the hood
as lzText for a quick 7.1 release if that idea works out ...

But without TOAST it would result in _undefined_ max tuple length,
which is probably not desirable.

True.

Boy, I'd sure find it desirable. There's nothing to stop people from
using varchar(8000) or whatever if they want a predictable top limit.
Text is not a standard type, and this wouldn't break standard semantics.

lzText wasn't removed because folks thought it was useless, IIRC,
it was removed because TOAST was an exciting and much more powerful
approach and no one wanted to introduce a new type doomed to disappear
after a single release cycle.

True.

With TOAST, from the user's point of view you'll still have an
_undefined_ max tuple length - the max will just be really, really
large. Sure, the tuples will actually be fixed but large varying
types can be split off into a series of tuples in the TOASTer
oven, so to speak. So I guess I have difficulty understanding
your argument.

False.

With TOAST, the maximum tuple length is limited by available
disk space (minus some overhead) and/or the number of bits we
use to represent the values original size and/or the size
addressable by the TOAST'ers table at all. Available space
allways limits the amount of data in a DB, and you allways
have to take some overhead into account, but calling this
_undefined_ isn't correct IMHO - better call it hard to
figure out.

The number of bits representing the attributes size is
another story, because we already decided to use some of the
top bits for special purposes, so a single attribute will
have some limit around 1/4 to 1 GB. Not too bad I think, who
would ever attempt to store a complete server backup in one
tuple? And which client/server combo will be able to handle
the required queries using the existing FE/BE protocol and
libpq implementation either. Thus there are other limits
causing problems before we need to continue this discussion,
surely.

If text were implemented as lzText for a quick 7.1, which apparently
was Jan's spin on the idea, then for 7.1 we'd say:

On the first look, it was a tempting solution. But there are
ton's of places in the backend, that assume text is binary
compatible to something or the bytes after the VARSIZE are
plain value bytes, not some compressed garbage to be passed
through a function first. Replacing TEXT by LZTEXT therefore
wouldn't be such an easy job, but would be working for the
wastebasked from the very beginning anyway, because TOAST
needs to revert it all again.

I don't like that kind of work.

Maybe I found some kind of compromise:

- We make LZTEXT a released type, without warning and anyone
can use it as needed.

- When featuring TOAST, we remove it and create a type
alias. This way, the "backend" will convert the table
schemas (WRT lztext->text) at reload time of the
conversion.

- We keep the type alias active past the next one or two
major releases. Someone skipping major releases,
converting from say 7.1 to 9.2, will have other problems
than replacing all occurences of lztext by text in his
dumps.

Actually I have some problems with the type coercion stuff.
There are functions lztext(text) and vice versa, but the
system is unable to find an "=" operator for lztext and text
when issuing

SELECT * FROM t1, t2, WHERE t1.lztext_att = t2.text_att;

This worked in the past releases (IIRC), so I wonder if the
failure above is a wanted "feature". I'll commit the stuff I
have tomorrow and hope someone can help me to get the
coercion working. All we have to do then is to tell in the
release notes and docs "Never use LZTEXT type name explicitly
in an application query (like for type casting) - use TEXT
instead".

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 Fri Feb 25 23:32:24 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 XAA97356
for <pgsql-hackers@postgreSQL.org>;
Fri, 25 Feb 2000 23:31:27 -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
XAA14752;
Fri, 25 Feb 2000 23:12:26 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002260412.XAA14752@candle.pha.pa.us>
Subject: ALTER TABLE DROP COLUMN
In-Reply-To: <Pine.LNX.4.21.0002171537260.3047-100000@localhost.localdomain>
from Peter Eisentraut at "Feb 17, 2000 06:05:31 pm"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Fri, 25 Feb 2000 23:12:26 -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

Marc called me today to discuss ALTER TABLE DROP COLUMN options.

Our new idea is to do the ALTER TABLE DROP COLUMN in place in the
existing table, rather than make a new one and try and preserve all the
table attributes.

You can exclusively lock the table, then do a heap_getnext() scan over
the entire table, remove the dropped column, do a heap_insert(), then a
heap_delete() on the current tuple, making sure to skip over the tuples
inserted by the current transaction. When completed, remove the column
from pg_attribute, mark the transaction as committed (if desired), and
run vacuum over the table to remove the deleted rows.

Seems this would be a very clean implementation for 7.1. It also would
be roll-backable in cases where the operation failed half-way during the
process.

-- 
  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 Sat Feb 26 00:18:25 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 AAA99603
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 00:18: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 VAA00402;
Fri, 25 Feb 2000 21:17:29 -0800 (PST)
Message-Id: <3.0.1.32.20000225211513.016e43b0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Fri, 25 Feb 2000 21:15:13 -0800
To: Jan Wieck <wieck@debis.com>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: Hannu Krosing <hannu@tm.ee>, Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <m12OXYT-0003kgC@orion.SAPserv.Hamburg.dsh.de>
References: <3.0.1.32.20000225171552.00f96a10@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 04:06 AM 2/26/00 +0100, Jan Wieck wrote:

False.

With TOAST, the maximum tuple length is limited by available
disk space (minus some overhead) and/or the number of bits we
use to represent the values original size and/or the size
addressable by the TOAST'ers table at all. Available space
allways limits the amount of data in a DB, and you allways
have to take some overhead into account, but calling this
_undefined_ isn't correct IMHO - better call it hard to
figure out.

Same is true for non-TOAST lzText.

Or...non lzText text, for that matter.

Of course, the size of text IS UNDEFINED TODAY.

create table foo (
t1 text,
t2 text);

Pray tell, what is the maximum size of t1?

Is it independent of t2? Or...correct me if I'm mistaken...if t2
contains 8,000+ characters won't "insert" bomb me if I try to
insert 8,000+ characters into t1? Or even a few characters? Exactly
where is this vaunted and well defined limit?

(oops - you can't answer that question because it depends on the
size of BLCKSZ, which of course one can change at will)

The maximum size of "text" is already undefined, as it depends on:

BLCKSZ (which the user may not've set herself, and maybe is unaware of
if she's a user at the mercy of some sysadmin)

and

the actual bytes occupied by other variable-length columns.

"bytea" for instance. "text" for instance. "varchar(n)" for instance,
which actually is a variable-length string which has a maximum value.

PG lets me do this:

create table foo (
t1 varchar(8000),
t2 varchar(8000),
tn varchar(8000) -- n fairly large
);

Can I insert 8K chars into t1?

Into t2?

Into t3?

Trick PG question - into all three at once?

Sorry, but this is a crap argument. There is no way to know how
many characters you can insert into a "text" column unless you have
detailed knowledge of the table, not only the types in the table
but the data stored in the pertinent row of the table.

I should know, I've been fighting this when porting code over from
Oracle, where the blocksize truly limits the size of ONE COLUMN,
not a row (tuple) at large.

If I can really have a tuple with 1000 varchar(BLCKSZ-overhead) columns,
fully filled with data, could you please tell me how to do this? My
life will be much simpler.

If text were implemented as lzText for a quick 7.1, which apparently
was Jan's spin on the idea, then for 7.1 we'd say:

On the first look, it was a tempting solution. But there are
ton's of places in the backend, that assume text is binary
compatible to something or the bytes after the VARSIZE are
plain value bytes, not some compressed garbage to be passed
through a function first. Replacing TEXT by LZTEXT therefore
wouldn't be such an easy job, but would be working for the
wastebasked from the very beginning anyway, because TOAST
needs to revert it all again.

I don't like that kind of work.

Nor do I, which is why I didn't suggest it when lzText first came up
and drifted into a TOAST discussion. Clearly, TOAST is a better
solution.

In particular, it solves Hannu's objection regarding the fact that
a compressed text type would have no fixed upper limit.

Better yet, it would solve Hannu's misunderstanding that today's
text type has such a limit.

Because (I love flogging dead horses):

create table foo (
i: integer,
t: text);

and

create table bar (
t: text);

create two columns T with different maximum limits. Because the
limit is based on tuple-size.

A compressed text type is only a bad idea because it's a dead end.
Not because it turns a "defined" max text limit into an undefined
max text limit. The maximum number of chars you can stuff into
a text var is always undefined unless you dissect exactly how
other columns eat storage.

Maybe I found some kind of compromise:

- We make LZTEXT a released type, without warning and anyone
can use it as needed.

- When featuring TOAST, we remove it and create a type
alias. This way, the "backend" will convert the table
schemas (WRT lztext->text) at reload time of the
conversion.

I have no strong feelings here. Personally, I can live with just
compiling PG with a 16KB blocksize, for the work I'm doing today.

But I don't think the upgrade problem's a big deal. If the type's
not popularized, only those of us "inside" will know of it, and as
far as I'm concerned, hand-editing a pg_dump would be fine with me if
I choose to use it.

But I'm only speaking for myself.

TOAST is clearly the way to go.

On the other hand, I don't see people flinging bricks at Interbase
for compressing their text type. After all, they have outer joins...

Actually I have some problems with the type coercion stuff.
There are functions lztext(text) and vice versa, but the
system is unable to find an "=" operator for lztext and text
when issuing

SELECT * FROM t1, t2, WHERE t1.lztext_att = t2.text_att;

This worked in the past releases (IIRC), so I wonder if the
failure above is a wanted "feature". I'll commit the stuff I
have tomorrow and hope someone can help me to get the
coercion working. All we have to do then is to tell in the
release notes and docs "Never use LZTEXT type name explicitly
in an application query (like for type casting) - use TEXT
instead".

Despite the above, I have no really strong feelings. I only raised
the compressed text issue because my (belated) reading of the Interbase
docs made it clear that they do this, and Tom resurrected lztext in
regard to views (and my problems there probably made it a red herring
in this case, too!) It's an interesting idea, and if TOAST is indeed
implemented probably a moot one. Though...where is the crossover between
an in-place compression and moving an item to the TOASTed table. And...
all of the problems with the backend making assumptions about text
etc will have to be addressed by the TOASTER, too.

For instance...varchar(4000) might still benefit from being compressed,
even if it is not TOASTed, due to PG's love of dragging full tuples
around. Saves disk space. Bigger slices of tables can be sorted in
memory vs. disk for any given backend sort/hash buffer size parameter.
Today's x86 CPUs, at least, favor shrinking the memory footprint of
data due to the fact that CPUs tend to be data-starved when working
on large amounts of data in RAM. Etc etc etc. So such a compressed
implementation may actually be a win even if Hannu's made happy by
affixing fixed varchar(n) limits on the column length.

- 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 Sat Feb 26 00:36:25 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 AAA00571
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 00:35:41 -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 AAA12426;
Sat, 26 Feb 2000 00:35:19 -0500 (EST)
To: Timothy Dyck <Timothy_Dyck@zd.com>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] PC Week Labs benchmark results
In-reply-to: <3708.949678531@sss.pgh.pa.us>
References: <8525687B.003BD14A.00@mailer.zd.com>
<3708.949678531@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
message dated "Fri, 04 Feb 2000 10:35:31 -0500"
Date: Sat, 26 Feb 2000 00:35:19 -0500
Message-ID: <12423.951543319@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I wrote:

Timothy Dyck <Timothy_Dyck@zd.com> writes:

8. Can't start postmaster with more than 65536 buffers as I get a "FATAL
1: couldn't initialize shared buffer pool Hash Tbl". Variable overflow?

Probably. Hadn't occurred to me that we need to check for a sane upper
bound on the number of buffers, but I guess we do. (You do realize that
would be half a gig of in-memory buffers, right? If you've actually got
that much RAM, it's probably better to let the OS use it for general-
purpose disk buffers instead of dedicating it all to Postgres.)

Just FYI, this is now fixed for 7.0. Turns out there was a bogus
hard-wired assumption about the maximum size of the hashtable for
shared buffers.

I still doubt that anyone really *needs* more than 64K buffers ;-)
... but it will work if you have the RAM.

regards, tom lane

From bouncefilter Sat Feb 26 01:02:25 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 BAA05426
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 01:01: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 BAA14228;
Sat, 26 Feb 2000 01:01:34 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Peter Eisentraut <peter_e@gmx.net>,
PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN
In-reply-to: <200002260412.XAA14752@candle.pha.pa.us>
References: <200002260412.XAA14752@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Fri, 25 Feb 2000 23:12:26 -0500"
Date: Sat, 26 Feb 2000 01:01:33 -0500
Message-ID: <14225.951544893@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

You can exclusively lock the table, then do a heap_getnext() scan over
the entire table, remove the dropped column, do a heap_insert(), then a
heap_delete() on the current tuple, making sure to skip over the tuples
inserted by the current transaction. When completed, remove the column
from pg_attribute, mark the transaction as committed (if desired), and
run vacuum over the table to remove the deleted rows.

Hmm, that would work --- the new tuples commit at the same instant that
the schema updates commit, so it should be correct. You have the 2x
disk usage problem, but there's no way around that without losing
rollback ability.

A potentially tricky bit will be persuading the tuple-reading and tuple-
writing subroutines to pay attention to different versions of the tuple
structure for the same table. I haven't looked to see if this will be
difficult or not. If you can pass the TupleDesc explicitly then it
shouldn't be a problem.

I'd suggest that the cleanup vacuum *not* be an automatic part of
the operation; just recommend that people do it ASAP after dropping
a column. Consider needing to drop several columns...

regards, tom lane

From bouncefilter Sat Feb 26 01:28:28 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 BAA10299
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 01:27: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 BAA17754;
Sat, 26 Feb 2000 01:27:20 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-reply-to: <3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Fri, 25 Feb 2000 17:54:43 -0800"
Date: Sat, 26 Feb 2000 01:27:19 -0500
Message-ID: <17751.951546439@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

Here's the test case:

Hmm. I get a rule string exceeding 8K out of this (in current sources),
as checked by breakpointing at InsertRule() in rewriteDefine.c and
looking at 'actiontree'.

What's your basis for asserting the rule is only ~ 1K?

regards, tom lane

From bouncefilter Sat Feb 26 05:09:28 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 FAA84751
for <pgsql-hackers@postgresql.org>;
Sat, 26 Feb 2000 05:09:20 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id EAA07945
for pgsql-hackers@postgresql.org; Sat, 26 Feb 2000 04:54:09 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: esling@sandiway.u-net.com
X-Newsgroups: comp.databases.postgresql.hackers
Subject: Web Site Opportunity for Database Expert
Reply-To: esling@sandiway.u-net.com
Message-ID: <tl8fbs078iuq9f8gfg4vqq9qjd6cfgeqd2@4ax.com>
X-Newsreader: Forte Agent 1.7/32.534
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Lines: 7
Date: Sat, 26 Feb 2000 09:57:56 +0000
X-Complaints-To: news@u-net.net
X-Trace: newsr2.u-net.net 951558876 195.102.198.174 (Sat,
26 Feb 2000 09:54:36 GMT)
Organization: (Posted via) U-NET Internet Ltd.
To: pgsql-hackers@postgresql.org

Web Site Opportunity for Database Expert

A group of business men and web site designers in the UK are working
on a revolutionary new web site that will take the retail-shopping
world by storm. They need to involve a leading UK database expert to
complete the work. Interested persons should contact me for further
details with a short resume of their experience.

From bouncefilter Sat Feb 26 07:33:30 2000
Received: from hu.tm.ee (ppp93.tele2.ee [212.107.33.93])
by hub.org (8.9.3/8.9.3) with ESMTP id HAA69877
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 07:32:34 -0500 (EST) (envelope-from hannu@tm.ee)
Received: from tm.ee (localhost [127.0.0.1]) by hu.tm.ee (Postfix) with ESMTP
id D68733B1C; Sat, 26 Feb 2000 14:41:29 +0200 (EET)
Sender: hannu@hu.tm.ee
Message-ID: <38B7C9F9.1E76D949@tm.ee>
Date: Sat, 26 Feb 2000 14:41:29 +0200
From: Hannu Krosing <hannu@tm.ee>
Organization: Trust-O-Matic =?iso-8859-1?Q?O=DC?=
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.13-7mdk i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Don Baccus <dhogaza@pacifier.com>
Cc: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225171552.00f96a10@mail.pacifier.com>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Don Baccus wrote:

Boy, I'd sure find it desirable. There's nothing to stop people from
using varchar(8000) or whatever if they want a predictable top limit.
Text is not a standard type, and this wouldn't break standard semantics.

lzText wasn't removed because folks thought it was useless, IIRC,
it was removed because TOAST was an exciting and much more powerful
approach and no one wanted to introduce a new type doomed to disappear
after a single release cycle.

With TOAST, from the user's point of view you'll still have an
_undefined_ max tuple length - the max will just be really, really
large. Sure, the tuples will actually be fixed but large varying
types can be split off into a series of tuples in the TOASTer
oven, so to speak. So I guess I have difficulty understanding
your argument.

Acutually it was not undefined but variable that made me uncertain -
i.e. the fact that max size depends on the contents of string

If text were implemented as lzText for a quick 7.1, which apparently
was Jan's spin on the idea, then for 7.1 we'd say:

"maximum number of characters you can store in a column of type
text varies"

... varies from below 8K to ~100K depending on the redundancy of data"

and after TOAST we'd say:

"maximum number of characters you can store in a column of type
text varies"

Rather "maximum number of characters you can store in a column of type
text is limited by available memory and/or disk space"

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

From bouncefilter Sat Feb 26 10:02: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 KAA74111
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 10:02:01 -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 HAA29937;
Sat, 26 Feb 2000 07:01:01 -0800 (PST)
Message-Id: <3.0.1.32.20000226064623.00fa1250@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 26 Feb 2000 06:46:23 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <17751.951546439@sss.pgh.pa.us>
References: <3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 01:27 AM 2/26/00 -0500, Tom Lane wrote:

Don Baccus <dhogaza@pacifier.com> writes:

Here's the test case:

Hmm. I get a rule string exceeding 8K out of this (in current sources),
as checked by breakpointing at InsertRule() in rewriteDefine.c and
looking at 'actiontree'.

What's your basis for asserting the rule is only ~ 1K?

I looked at the string dumped by pg_dump and it didn't appear to be
anywhere near 8KB, so I presumed that the actual data stuffed into
the rule is larger than whatever gets dumped out as the source
representation.

I've never looked at the implementation of rules, so it's unclear
to me just exactly what is being saved and just how much of it
using lzText would impact.

I had breakpointed the debugger at first and that's why I first
said apparently the rule string was > 8KB. Then I looked at
pg_dump output and had doubts that the answer was this simple...

- 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 Sat Feb 26 10:02:32 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 KAA74109
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 10:01:41 -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 HAA29959;
Sat, 26 Feb 2000 07:01:05 -0800 (PST)
Message-Id: <3.0.1.32.20000226065840.00fa6c10@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 26 Feb 2000 06:58:40 -0800
To: Hannu Krosing <hannu@tm.ee>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <38B7C9F9.1E76D949@tm.ee>
References: <3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225171552.00f96a10@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 02:41 PM 2/26/00 +0200, Hannu Krosing wrote:

Rather "maximum number of characters you can store in a column of type
text is limited by available memory and/or disk space"

TOAST is obviously ideal, so in a sense this discussion's pointless
because I have no doubt TOAST will happen.

We could still put an 8KB upper limit on a compressed text type if
we wish. The size savings would be a plus, and you'd be able to
have full-sized 8KB text columns in many tables, at least, that carry
a bunch of other cruft around.

That's really the problem I run in porting over the web toolkit
from arsDigita.

I see tables that have two or three varchar(4000) columns with other
data, i.e. names and stuff that are also varchar but smaller. I know
that these don't actually get stuffed with 4000 chars but rather
that 4KB is the upper limit of the size of an Oracle varchar and that
the author's been lazy. If I had a compressed text or varchar type
I'd be quite confident that the application code would run even with
an 8KB block size.

In the interim. Until TOAST comes or until I have time to dig into
the code and determine more accurate and reasonable sizes for the
varchars.

On the other hand, as I've mentioned I'm also just as happy to run
with a 16KB block size. From the point of view of distributing the
web toolkit, some of our little group feel uncomfortable with that
requirement but it doesn't really bother me as I know TOAST will solve
the problem and that by end of year we'll be able to run the toolkit
on a default installation of Postgres.

So I'm happy, I run with a 16KB block size and eagerly await TOASTed
tuples.

- 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 Sat Feb 26 11:02:32 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 LAA76089
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 11:01:36 -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
KAA02325;
Sat, 26 Feb 2000 10:36:21 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002261536.KAA02325@candle.pha.pa.us>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-Reply-To: <3.0.1.32.20000226065840.00fa6c10@mail.pacifier.com> from Don
Baccus at "Feb 26, 2000 06:58:40 am"
To: Don Baccus <dhogaza@pacifier.com>
Date: Sat, 26 Feb 2000 10:36:20 -0500 (EST)
CC: Hannu Krosing <hannu@tm.ee>, Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <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 see tables that have two or three varchar(4000) columns with other
data, i.e. names and stuff that are also varchar but smaller. I know
that these don't actually get stuffed with 4000 chars but rather
that 4KB is the upper limit of the size of an Oracle varchar and that
the author's been lazy. If I had a compressed text or varchar type
I'd be quite confident that the application code would run even with
an 8KB block size.

Just to clearify, varchar(4000) does not take 4000 chars on disk, while
char(4000) does use 4000 chars on the disk.

-- 
  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 Sat Feb 26 12:15:33 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 MAA79798
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 12:15: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 MAA27315;
Sat, 26 Feb 2000 12:15:20 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-reply-to: <3.0.1.32.20000226064623.00fa1250@mail.pacifier.com>
References: <3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
<3.0.1.32.20000226064623.00fa1250@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Sat, 26 Feb 2000 06:46:23 -0800"
Date: Sat, 26 Feb 2000 12:15:19 -0500
Message-ID: <27312.951585319@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

What's your basis for asserting the rule is only ~ 1K?

I looked at the string dumped by pg_dump and it didn't appear to be
anywhere near 8KB, so I presumed that the actual data stuffed into
the rule is larger than whatever gets dumped out as the source
representation.

Yes, the source representation is *vastly* more compact. A single
result column might look like "tab1.product_id" when dumped by pg_dump,
but the nodetree dump looks more like

{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 23
:restypmod -1
:resname product_id
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}

:expr
{ VAR
:varno 1
:varattno 1
:vartype 23
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}

and (except for not using any excess whitespace) that is exactly what
goes into a rule action string.

As you can see, this is very amenable to compression, especially
when you have a lot of columns in a view.

Someday we might think about using a more compact representation for
stored rules, but there are advantages to using a format that's fairly
easy for a human to examine.

regards, tom lane

From bouncefilter Sat Feb 26 15:43:35 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 PAA05539
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 15:43:01 -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 MAA27551;
Sat, 26 Feb 2000 12:41:18 -0800 (PST)
Message-Id: <3.0.1.32.20000226104914.016e2940@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 26 Feb 2000 10:49:14 -0800
To: Bruce Momjian <pgman@candle.pha.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: Hannu Krosing <hannu@tm.ee>, Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <200002261536.KAA02325@candle.pha.pa.us>
References: <3.0.1.32.20000226065840.00fa6c10@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 10:36 AM 2/26/00 -0500, Bruce Momjian wrote:

I see tables that have two or three varchar(4000) columns with other
data, i.e. names and stuff that are also varchar but smaller. I know
that these don't actually get stuffed with 4000 chars but rather
that 4KB is the upper limit of the size of an Oracle varchar and that
the author's been lazy. If I had a compressed text or varchar type
I'd be quite confident that the application code would run even with
an 8KB block size.

Just to clearify, varchar(4000) does not take 4000 chars on disk, while
char(4000) does use 4000 chars on the disk.

Yes, I know. The point is that without digging into how the code actually
uses these tables, I don't know which, if any, of the columns might
actually get stuffed with two, three, or four thousand characters. If
I'm unlucky, all of them will be. For now my simple solution is to
run with a 16KB blocksize and not worry about it.

This isn't postgresql's fault or whatever, the basic problem is a
lazy web hacker arbitrarily declaring varchar(4000) columns rather
than sitting down and determining what they need, because in Oracle
the amount taken is also only the number of bytes in the string
stuffed into the column.

This is kind of a pointless discussion. We all know that TOAST is
going to be ultra-slick.

lztext was resurrected as an idea by Tom Lane in response to the
explosion in the length of the rule strings generated for views
in PG7.0.

That just triggered a memory on my part that Interbase apparently
compresses their text type, a fact I found interesting enough to
mention.

I'm neither lobbying for or against Postgres implementation of lztext,
text as lztext, or anything else.

I just found the notion interesting...

It would be nice if a simple table/view combination such as I posted
here earlier didn't bomb PG7.0 with a default 8KB blocksize, though!

My own views are working fine since I've switched to a 16KB blocksize
for the reasons hinted at above, but the fact that this example fails
in the default 8KB version is pretty grotty. Tom Lane will probably
have it all fixed via lztext or some other method before most of the
folks on this list read this note :)

Regarding large types, TOAST is clearly the path to follow, and Jan's
plans for TOASTed couples includes compression when appropriate. I
also think we can layer SQL3-compliant BLOBs and CLOBs on top of his
TOAST implementation later on - for compatibility reasons only, of
course.

- 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 Sat Feb 26 15:42:35 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 PAA05433
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 15:41:54 -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 MAA27559;
Sat, 26 Feb 2000 12:41:20 -0800 (PST)
Message-Id: <3.0.1.32.20000226105512.016e6c60@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 26 Feb 2000 10:55:12 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <27312.951585319@sss.pgh.pa.us>
References: <3.0.1.32.20000226064623.00fa1250@mail.pacifier.com>
<3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225132940.01098760@mail.pacifier.com>
<3.0.1.32.20000225175443.00f9aa40@mail.pacifier.com>
<3.0.1.32.20000226064623.00fa1250@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 12:15 PM 2/26/00 -0500, Tom Lane wrote:

and (except for not using any excess whitespace) that is exactly what
goes into a rule action string.

As you can see, this is very amenable to compression, especially
when you have a lot of columns in a view.

Someday we might think about using a more compact representation for
stored rules, but there are advantages to using a format that's fairly
easy for a human to examine.

Oh, now I understand, I didn't realize the tree was being stored in
human-readable form as a string, but thought it was being parsed into
a binary form. That's why I began having doubts that I might've triggered
unecessary work on Jan's part regarding lztext. Yes, since it's
stored as a text string lztext should help a LOT.

- 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 Sat Feb 26 16:35: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 QAA08852
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 16:35: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 QAA06157;
Sat, 26 Feb 2000 16:35:09 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
In-reply-to: <3.0.1.32.20000226104914.016e2940@mail.pacifier.com>
References: <3.0.1.32.20000226065840.00fa6c10@mail.pacifier.com>
<3.0.1.32.20000226104914.016e2940@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Sat, 26 Feb 2000 10:49:14 -0800"
Date: Sat, 26 Feb 2000 16:35:09 -0500
Message-ID: <6154.951600909@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

My own views are working fine since I've switched to a 16KB blocksize
for the reasons hinted at above, but the fact that this example fails
in the default 8KB version is pretty grotty. Tom Lane will probably
have it all fixed via lztext or some other method before most of the
folks on this list read this note :)

Not me --- Jan gets the credit for lztext.

regards, tom lane

From bouncefilter Sat Feb 26 16:45:36 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 QAA22187;
Sat, 26 Feb 2000 16:45: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
QAA07442;
Sat, 26 Feb 2000 16:44:50 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002262144.QAA07442@candle.pha.pa.us>
Subject: Web page on bug reports
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: Sat, 26 Feb 2000 16:44:50 -0500 (EST)
CC: webmaster@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

This was a helpful introduction to submitting bug reports:

http://www.freshmeat.net/news/2000/02/26/951627540.html

Maybe add it to the web site?

-- 
  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 Sat Feb 26 20:34:38 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 UAA32734
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 20:33:53 -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 RAA15914;
Sat, 26 Feb 2000 17:33:08 -0800 (PST)
Message-Id: <3.0.1.32.20000226135700.00f960d0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 26 Feb 2000 13:57:00 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] LZTEXT for rule plan stings
Cc: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <6154.951600909@sss.pgh.pa.us>
References: <3.0.1.32.20000226104914.016e2940@mail.pacifier.com>
<3.0.1.32.20000226065840.00fa6c10@mail.pacifier.com>
<3.0.1.32.20000226104914.016e2940@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 04:35 PM 2/26/00 -0500, Tom Lane wrote:

Don Baccus <dhogaza@pacifier.com> writes:

My own views are working fine since I've switched to a 16KB blocksize
for the reasons hinted at above, but the fact that this example fails
in the default 8KB version is pretty grotty. Tom Lane will probably
have it all fixed via lztext or some other method before most of the
folks on this list read this note :)

Not me --- Jan gets the credit for lztext.

Did he hook it up to pg_rewrite, then? If so, I'll try downloading
it and I'll toss my stuff at it...

- 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 Sat Feb 26 18:08:37 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 SAA25344
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 18:08:28 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from mcadnote1 (ppm202.noc.fukui.nsk.ne.jp [210.161.188.77])
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id IAA03193; Sun, 27 Feb 2000 08:07:36 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "pgsql-hackers" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Numeric with '-'
Date: Sun, 27 Feb 2000 08:09:00 +0900
Message-ID: <NDBBIJLOILGIKBGDINDFAEADCDAA.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: <22275.951195189@sss.pgh.pa.us>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal

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

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

They didn't give any such warning before, either. I doubt I've
made anything worse.

Before your change
INSERT into t (numdata) values (-1234567890.1234567);
caused an error
ERROR: Unable to convert left operator '-' from type 'unknown'.
but currently inserts a constant -1234567890.12346.

Yipes, you are right. I think that that sort of construct should
result in the value not getting converted at all until the parser
knows that it must be converted to the destination column's type.
Let me see if I can find out what's going wrong. If this doesn't
seem to be fixable, I may have to back off the patch...

This seems to be fixed.
Thanks a lot.

However there still remains the following case.
select * from num_data where val = 1.1;
ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast

SQL standard seems to say 1.1 is a numeric constant and
it's not good to treat a numeric value as an aproximate value.
For example,what do you think about the following.

select 11111111111111 * 1.1;
?column?
------------------
12222222222222.1
(1 row)

select 111111111111111 * 1.1;
?column?
-----------------
122222222222222
(1 row)

select 100000000 + .000001;
?column?
------------------
100000000.000001
(1 row)

select 100000000 + .0000001;
?column?
-----------
100000000
(1 row)

select 100000000.0000001;
?column?
-------------------
100000000.0000001
(1 row)

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Sat Feb 26 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 SAA27396
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 18:46:38 -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 SAA15557;
Sat, 26 Feb 2000 18:46:32 -0500 (EST)
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>
cc: "pgsql-hackers" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Numeric with '-'
In-reply-to: <NDBBIJLOILGIKBGDINDFAEADCDAA.Inoue@tpf.co.jp>
References: <NDBBIJLOILGIKBGDINDFAEADCDAA.Inoue@tpf.co.jp>
Comments: In-reply-to "Hiroshi Inoue" <Inoue@tpf.co.jp>
message dated "Sun, 27 Feb 2000 08:09:00 +0900"
Date: Sat, 26 Feb 2000 18:46:32 -0500
Message-ID: <15554.951608792@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

However there still remains the following case.
select * from num_data where val = 1.1;
ERROR: Unable to identify an operator '=' for types 'numeric' and 'float8'
You will have to retype this query using an explicit cast

Yeah. I'm not sure that that can be fixed without a major redesign of
the type-conversion hierarchy, which is not something I care to try
during beta ;-).

In fact, it's arguable that the system is doing the right thing by
forcing the user to specify whether he wants a NUMERIC or FLOAT8
comparison to be used. There are other examples where we *must*
refuse to decide. For example:

regression=# create table ff (f1 char(8), f2 varchar(20));
CREATE
regression=# select * from ff where f1 = f2;
ERROR: Unable to identify an operator '=' for types 'bpchar' and 'varchar'
You will have to retype this query using an explicit cast

This is absolutely the right thing, because bpchar and varchar do not
have the same comparison semantics (trailing blanks are significant in
one case and not in the other), so the user has to tell us which he
wants.

SQL standard seems to say 1.1 is a numeric constant and
it's not good to treat a numeric value as an aproximate value.
For example,what do you think about the following.

That argument is untenable. NUMERIC has limitations just as bad as
FLOAT's; they're merely different. For example:

regression=# select 1.0/300000.0;
?column?
----------------------
3.33333333333333e-06
(1 row)

regression=# select 1.0::numeric / 300000.0::numeric;
?column?
--------------
0.0000033333
(1 row)

Notice the completely unacceptable loss of precision ;-) in the second
case.

When you look at simple cases like "var = constant" it seems easy to
say that the system should just do the right thing, but in more complex
cases it's not always easy to know what the right thing is.

I think what you are proposing is to change the system's default
assumption about decimal constants from float8 to numeric. I think
that's a very risky change that is likely to break existing applications
(and if we throw in automatic conversions, it'll break 'em silently).
I'm not eager to do that.

regards, tom lane

From bouncefilter Sat Feb 26 19:59:38 2000
Received: from ns1.foothill.net (ns1.foothill.net [206.170.175.1])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA30808
for <pgsql-interfaces@postgreSQL.org>;
Sat, 26 Feb 2000 19:58:56 -0500 (EST)
(envelope-from stephen@sealteam.demon.co.uk)
Received: from stephens (Q237.135.foothill.net [216.111.237.135])
by ns1.foothill.net (8.9.3/8.9.3) with SMTP id QAA10806
for <pgsql-interfaces@postgreSQL.org>;
Sat, 26 Feb 2000 16:54:43 -0800 (PST)
From: "Stephen Martin" <stephen@sealteam.demon.co.uk>
To: <pgsql-interfaces@postgreSQL.org>
Subject:
Date: Sat, 26 Feb 2000 17:01:55 -0800
Message-ID: <NDBBKKNKKLBACABPPCCNGEHPDOAA.stephen@sealteam.demon.co.uk>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
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)
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200

Hello,
How can one differential between apparently duplicated usernames?
For example I have a postgres id 'www' present within my pg_shadow/pg_user.
I recently installed a package and inadvertantly created another 'www' user.
I have managed to remove the duplicate user by deleting it from pg_shadow by
identifying it via its usesysid field.

How can I

1) Ensure I canot in future create duplicate names?
2) If duplicate users are possible by what other means can the
various instances of them be referenced?

Stephen

----------------------------------------------
010000C9

From bouncefilter Sat Feb 26 20:12:38 2000
Received: from ns1.foothill.net (ns1.foothill.net [206.170.175.1])
by hub.org (8.9.3/8.9.3) with ESMTP id UAA31528
for <pgsql-interfaces@postgreSQL.org>;
Sat, 26 Feb 2000 20:12:29 -0500 (EST)
(envelope-from stephen@sealteam.demon.co.uk)
Received: from stephens (Q237.135.foothill.net [216.111.237.135])
by ns1.foothill.net (8.9.3/8.9.3) with SMTP id RAA13680
for <pgsql-interfaces@postgreSQL.org>;
Sat, 26 Feb 2000 17:08:20 -0800 (PST)
From: "Stephen Martin" <stephen@sealteam.demon.co.uk>
To: <pgsql-interfaces@postgreSQL.org>
Subject: RE: [INTERFACES] Date: Sat, 26 Feb 2000 17:01:55 -0800
Date: Sat, 26 Feb 2000 17:15:34 -0800
Message-ID: <NDBBKKNKKLBACABPPCCNEEIADOAA.stephen@sealteam.demon.co.uk>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
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: <NDBBKKNKKLBACABPPCCNGEHPDOAA.stephen@sealteam.demon.co.uk>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200

Hello,
as a post script to this..
Under normal circumstances one cannot add
another user of the same name (phew!:) )
I think this was the result of a setup
script
writing directly into pg_shadow and pg_user;

Stephen

----------------------------------------------
010000C9

-----Original Message-----
From: owner-pgsql-interfaces@postgreSQL.org
[mailto:owner-pgsql-interfaces@postgreSQL.org]On Behalf Of Stephen
Martin
Sent: Saturday, February 26, 2000 5:00 PM
To: pgsql-interfaces@postgreSQL.org
Subject: [INTERFACES] Date: Sat, 26 Feb 2000 17:01:55 -0800

Hello,
How can one differential between apparently duplicated usernames?
For example I have a postgres id 'www' present within my pg_shadow/pg_user.
I recently installed a package and inadvertantly created another 'www' user.
I have managed to remove the duplicate user by deleting it from pg_shadow by
identifying it via its usesysid field.

How can I

1) Ensure I canot in future create duplicate names?
2) If duplicate users are possible by what other means can the
various instances of them be referenced?

Stephen

----------------------------------------------
010000C9

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

From bouncefilter Sat Feb 26 20:47:39 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 UAA33458;
Sat, 26 Feb 2000 20:47:13 -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 UAA22651;
Sat, 26 Feb 2000 20:47:08 -0500 (EST)
To: "Stephen Martin" <stephen@sealteam.demon.co.uk>
cc: pgsql-interfaces@postgreSQL.org, pgsql-hackers@postgreSQL.org
Subject: Re: [INTERFACES] Date: Sat, 26 Feb 2000 17:01:55 -0800
In-reply-to: <NDBBKKNKKLBACABPPCCNGEHPDOAA.stephen@sealteam.demon.co.uk>
References: <NDBBKKNKKLBACABPPCCNGEHPDOAA.stephen@sealteam.demon.co.uk>
Comments: In-reply-to "Stephen Martin" <stephen@sealteam.demon.co.uk>
message dated "Sat, 26 Feb 2000 19:59:46 -0500"
Date: Sat, 26 Feb 2000 20:47:08 -0500
Message-ID: <22648.951616028@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Stephen Martin" <stephen@sealteam.demon.co.uk> writes:

For example I have a postgres id 'www' present within my pg_shadow/pg_user.
I recently installed a package and inadvertantly created another 'www' user.

How can I
1) Ensure I canot in future create duplicate names?

There should probably be a unique index on pg_shadow's usename field,
and another one on the usesysid field (otherwise there's not a unique
map from sysids to users, which is bad since we use sysids as
referential keys in other tables).

I'm surprised this hasn't been pointed out before :-(

I'm not sure how difficult it would be to do it. Just creating
an index with CREATE INDEX will not work, because pg_shadow is
an installation-wide table and its index must be as well. There's
some routine somewhere in the backend that would have to be taught
about the index.

regards, tom lane

From bouncefilter Sat Feb 26 23:49: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 XAA41661
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 23:48:51 -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 XAA11588
for <pgsql-hackers@postgreSQL.org>;
Sat, 26 Feb 2000 23:48:49 -0500 (EST)
To: pgsql-hackers@postgreSQL.org
Subject: update_pg_pwd trigger does not work very well
Date: Sat, 26 Feb 2000 23:48:49 -0500
Message-ID: <11585.951626929@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I was looking at the trigger function that's been added to try to
update pg_pwd automatically if pg_shadow is updated via standard
SQL commands. It's got some problems:

1. Since the trigger is executed as soon as a tuple is inserted/
updated/deleted, it will write pg_pwd before the transaction is
committed. If you then abort the transaction, pg_pwd contains wrong
data. Even if you don't abort, the postmaster may read and act on the
updated pg_pwd before you've committed, which could have bad
consequences (logging in a user who doesn't exist yet, for example).

2. The trigger tries to grab AccessExclusiveLock on pg_shadow.
Since this is being done in the middle of a transaction that has
previously grabbed some lower level of lock on pg_shadow, it's
very easy to create a deadlock situation. All you need is two
different transactions modifying pg_shadow concurrently, and
it'll fail.

3. CREATE USER and friends refuse to run inside a transaction block
in the vain hope of making life safe for the trigger. It's vain
since the above problems will occur anyway, if one simply alters
pg_shadow using ordinary SQL commands. (And if we're not going to
support that, why bother with the trigger?) I think this is a rather
unpleasant restriction, especially so when it isn't buying any
safety at all.

A possible solution for these problems is to have the trigger procedure
itself do nothing except set a flag variable. The flag is examined
somewhere in xact.c after successful completion of a transaction,
and if it's set then we run a new transaction cycle in which we
read pg_shadow and write pg_pwd. (A new transaction is needed so
that it's safe to demand AccessExclusiveLock on pg_shadow --- we
have to release all our old locks before we can do that.) Note that
*only* this second transaction would need AccessExclusiveLock; CREATE
USER and friends would not.

I am not quite certain that this is completely bulletproof when there
are multiple backends concurrently updating pg_shadow, but I have not
been able to think of a case where it'd fail. The worst possibility
is that a committed update in pg_shadow might not get propagated to
pg_pwd for a while because some other transaction is holding a lock on
pg_shadow. (But pg_pwd updates can be delayed for that reason now,
so it's certainly no worse than before.)

Comments?

regards, tom lane

From bouncefilter Sun Feb 27 00:36:41 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 AAA45863
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 00:35:51 -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
AAA14214;
Sun, 27 Feb 2000 00:35:42 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002270535.AAA14214@candle.pha.pa.us>
Subject: Re: [HACKERS] update_pg_pwd trigger does not work very well
In-Reply-To: <11585.951626929@sss.pgh.pa.us> from Tom Lane at "Feb 26,
2000 11:48:49 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 27 Feb 2000 00:35:42 -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 am not quite certain that this is completely bulletproof when there
are multiple backends concurrently updating pg_shadow, but I have not
been able to think of a case where it'd fail. The worst possibility
is that a committed update in pg_shadow might not get propagated to
pg_pwd for a while because some other transaction is holding a lock on
pg_shadow. (But pg_pwd updates can be delayed for that reason now,
so it's certainly no worse than before.)

Comments?

I see your point. Guess we have to do it inside xact.

-- 
  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 Sun Feb 27 00:55:41 2000
Received: from nichewareinc.com (IDENT:root@[63.78.34.254])
by hub.org (8.9.3/8.9.3) with ESMTP id AAA46475
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 00:54:51 -0500 (EST)
(envelope-from messier@nichewareinc.com)
Received: from delllaptop (adsl-63-195-149-143.dsl.scrm01.pacbell.net
[63.195.149.143])
by nichewareinc.com (8.9.3/8.8.7) with ESMTP id WAA17547
for <pgsql-hackers@postgreSQL.org>; Sat, 26 Feb 2000 22:01:39 -0800
Reply-To: <messier@nichewareinc.com>
From: "Ray Messier" <messier@nichewareinc.com>
To: <pgsql-hackers@postgreSQL.org>
Subject:
Date: Sat, 26 Feb 2000 21:52:14 -0800
Message-ID: <NCBBJEKDMLKBHHEMCENNIECPCGAA.messier@nichewareinc.com>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
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: <200002270535.AAA14214@candle.pha.pa.us>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300

help

From bouncefilter Sun Feb 27 01:58:42 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 BAA47926;
Sun, 27 Feb 2000 01:57:59 -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 BAA15845;
Sun, 27 Feb 2000 01:57:52 -0500 (EST)
To: Michael Meskes <meskes@postgreSQL.org>
cc: PostgreSQL Hacker <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Compile woes
In-reply-to: <20000225090353.A9494@fam-meskes.de>
References: <20000225090353.A9494@fam-meskes.de>
Comments: In-reply-to Michael Meskes <meskes@postgreSQL.org>
message dated "Fri, 25 Feb 2000 09:03:53 +0100"
Date: Sun, 27 Feb 2000 01:57:52 -0500
Message-ID: <15842.951634672@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Michael Meskes <meskes@postgreSQL.org> writes:

It seems I cannot compile the backend anymore. Here's what I get after make
distclean; configure; make:

make[3]: Entering directory /home/postgres/pgsql/src/backend/access/gist'
make[3]: *** No rule to make target ../../../include/utils/dt.h', needed by
ist.o'. Stop.

Michael, did you solve this yet? My guess is you need a new 'make depend'.

regards, tom lane

From bouncefilter Sun Feb 27 05:54:45 2000
Received: from sraigw.sra.co.jp (sraigw.sra.co.jp [202.32.10.2])
by hub.org (8.9.3/8.9.3) with ESMTP id FAA58854
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 05:54:39 -0500 (EST)
(envelope-from t-ishii@sra.co.jp)
Received: from sranhk.sra.co.jp (sranhk [133.137.36.134])
by sraigw.sra.co.jp (8.8.7/3.7W-sraigw) with ESMTP id TAA26519;
Sun, 27 Feb 2000 19:54:37 +0900 (JST)
Received: from localhost (IDENT:t-ishii@portsv3-11 [133.137.84.11])
by sranhk.sra.co.jp (8.9.3/3.7W-srambox) with ESMTP id TAA07915;
Sun, 27 Feb 2000 19:54:35 +0900
To: pgman@candle.pha.pa.us
Cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Changes in 7.0
In-Reply-To: <200002232012.PAA06498@candle.pha.pa.us>
References: <200002232012.PAA06498@candle.pha.pa.us>
X-Mailer: Mew version 1.94 on Emacs 19.34 / Mule 2.3 (SUETSUMUHANA)
Mime-Version: 1.0
Content-Type: Text/Plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Message-Id: <20000227200053U.t-ishii@sra.co.jp>
Date: Sun, 27 Feb 2000 20:00:53 +0900
From: Tatsuo Ishii <t-ishii@sra.co.jp>
X-Dispatcher: imput version 990905(IM130)
Lines: 15

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

New pg_char_to_encoding() and pg_encoding_to_char() functions

done by me.

Also, can you add followings:

New libpq functions PQsetClientEncoding(), PQclientEncoding()
Add support for SJIS user defined characters
Add SQL_ASCII test case to the regression test
--with-mb now deprecated
--
Tatsuo Ishii

From bouncefilter Sun Feb 27 07:15:46 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 HAA60404
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 07:15:17 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12P2QD-0003kGC; Sun, 27 Feb 100 13:03 MET
Message-Id: <m12P2QD-0003kGC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: type coerce problem with lztext
To: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Date: Sun, 27 Feb 2000 13:03:37 +0100 (CET)
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

Well,

LZTEXT is there again, and pg_rewrite uses it for action and
qual strings. This is what it tells:

pgsql=# select rulename, length(ev_action), octet_length(ev_action)
pgsql-# from pg_rewrite;
rulename | length | octet_length
----------------+--------+--------------
_RETpg_user | 3043 | 855
_RETpg_rules | 3074 | 1139
_RETpg_views | 4261 | 1252
_RETpg_tables | 5187 | 1338
_RETpg_indexes | 3525 | 1122
(5 rows)

Yes, the 3043 bytes long rule action string got stored in 855
bytes in pg_rewrite. That's 71.9% compression rate on this
attempt!

There are functions text(lztext) and lztext(text) too, but
the system is unable to find an operator if one compares
text=lztext in a query. IIRC, creating a function named as
the target type and taking the source type is what made auto-
type-conversion work - so what am I missing here?

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 Sun Feb 27 09:56:47 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 JAA66782
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 09:56: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
JAA28325;
Sun, 27 Feb 2000 09:48:17 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002271448.JAA28325@candle.pha.pa.us>
Subject: Re: [HACKERS] Changes in 7.0
In-Reply-To: <20000227200053U.t-ishii@sra.co.jp> from Tatsuo Ishii at "Feb 27,
2000 08:00:53 pm"
To: Tatsuo Ishii <t-ishii@sra.co.jp>
Date: Sun, 27 Feb 2000 09:48:16 -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

Done.

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

New pg_char_to_encoding() and pg_encoding_to_char() functions

done by me.

Also, can you add followings:

New libpq functions PQsetClientEncoding(), PQclientEncoding()
Add support for SJIS user defined characters
Add SQL_ASCII test case to the regression test
--with-mb now deprecated
--
Tatsuo Ishii

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

-- 
  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 Sun Feb 27 09:56:47 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 JAA66792
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 09:56:31 -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
JAA28342;
Sun, 27 Feb 2000 09:50:45 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002271450.JAA28342@candle.pha.pa.us>
Subject: Re: [HACKERS] type coerce problem with lztext
In-Reply-To: <m12P2QD-0003kGC@orion.SAPserv.Hamburg.dsh.de> from Jan Wieck at
"Feb 27, 2000 01:03:37 pm"
To: Jan Wieck <wieck@debis.com>
Date: Sun, 27 Feb 2000 09:50:45 -0500 (EST)
CC: PostgreSQL HACKERS <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

Yes, the 3043 bytes long rule action string got stored in 855
bytes in pg_rewrite. That's 71.9% compression rate on this
attempt!

There are functions text(lztext) and lztext(text) too, but
the system is unable to find an operator if one compares
text=lztext in a query. IIRC, creating a function named as
the target type and taking the source type is what made auto-
type-conversion work - so what am I missing here?

Added to Features:

New lztext data type for compressed text fields
Larger views/rules supported

-- 
  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 Sun Feb 27 10:50: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 KAA77363
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 10:50:14 -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 HAA13002;
Sun, 27 Feb 2000 07:50:10 -0800 (PST)
Message-Id: <3.0.1.32.20000227073407.00fa97b0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sun, 27 Feb 2000 07:34:07 -0800
To: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] type coerce problem with lztext
In-Reply-To: <m12P2QD-0003kGC@orion.SAPserv.Hamburg.dsh.de>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 01:03 PM 2/27/00 +0100, Jan Wieck wrote:

Well,

LZTEXT is there again, and pg_rewrite uses it for action and
qual strings. This is what it tells:

pgsql=# select rulename, length(ev_action), octet_length(ev_action)
pgsql-# from pg_rewrite;
rulename | length | octet_length
----------------+--------+--------------
_RETpg_user | 3043 | 855
_RETpg_rules | 3074 | 1139
_RETpg_views | 4261 | 1252
_RETpg_tables | 5187 | 1338
_RETpg_indexes | 3525 | 1122
(5 rows)

Yes, the 3043 bytes long rule action string got stored in 855
bytes in pg_rewrite. That's 71.9% compression rate on this
attempt!

This will greatly help counter 7.0's "rule length explosion".

Thanks.

- 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 Sun Feb 27 12:55:50 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 MAA96507
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 12:55:49 -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 MAA04734;
Sun, 27 Feb 2000 12:55:46 -0500 (EST)
To: Jan Wieck <wieck@debis.com>
cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] type coerce problem with lztext
In-reply-to: <m12P2QD-0003kGC@orion.SAPserv.Hamburg.dsh.de>
References: <m12P2QD-0003kGC@orion.SAPserv.Hamburg.dsh.de>
Comments: In-reply-to wieck@debis.com (Jan Wieck)
message dated "Sun, 27 Feb 2000 13:03:37 +0100"
Date: Sun, 27 Feb 2000 12:55:45 -0500
Message-ID: <4731.951674145@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

wieck@debis.com (Jan Wieck) writes:

There are functions text(lztext) and lztext(text) too, but
the system is unable to find an operator if one compares
text=lztext in a query. IIRC, creating a function named as
the target type and taking the source type is what made auto-
type-conversion work - so what am I missing here?

I'll take a look. I think the key may be teaching TypeCategory
to know that lztext is a member of the text type class.

regards, tom lane

From bouncefilter Sun Feb 27 13:00:50 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 NAA97042
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 13:00: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 NAA04783
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 13:00:23 -0500 (EST)
To: pgsql-hackers@postgreSQL.org
Subject: Is anyone working on pg_dump?
Date: Sun, 27 Feb 2000 13:00:23 -0500
Message-ID: <4779.951674423@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

One of the major must-fix items remaining on my to-do-for-7.0 list
is making pg_dump smarter about the order to dump stuff in, so as
to avoid problems like table constraints referring to not-yet-
defined functions.

We have talked about this before, and I thought some people had
expressed interest in fixing it, but I haven't seen any results.
Is anyone working on it?

If not, I'm willing to do the quick-and-dirty approach of sorting
the objects by OID. This could be extended later to allow a
dependency-based sort, but I don't have the time or interest to
attempt that now.

I'd be just as happy to yield the project to someone else though ;-)

regards, tom lane

From bouncefilter Sun Feb 27 12:58:49 2000
Received: from hu.tm.ee (ppp735.tele2.ee [212.107.37.35])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA96696
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 12:58:06 -0500 (EST) (envelope-from hannu@tm.ee)
Received: from tm.ee (localhost [127.0.0.1]) by hu.tm.ee (Postfix) with ESMTP
id 619C03A4B; Sun, 27 Feb 2000 20:07:00 +0200 (EET)
Sender: hannu@hu.tm.ee
Message-ID: <38B967C3.C2C36FE5@tm.ee>
Date: Sun, 27 Feb 2000 20:06:59 +0200
From: Hannu Krosing <hannu@tm.ee>
Organization: Trust-O-Matic =?iso-8859-1?Q?O=DC?=
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.13-7mdk i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Bruce Momjian <pgman@candle.pha.pa.us>,
Peter Eisentraut <peter_e@gmx.net>,
PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN
References: <200002260412.XAA14752@candle.pha.pa.us>
<14225.951544893@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

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

You can exclusively lock the table, then do a heap_getnext() scan over
the entire table, remove the dropped column, do a heap_insert(), then a
heap_delete() on the current tuple, making sure to skip over the tuples
inserted by the current transaction. When completed, remove the column
from pg_attribute, mark the transaction as committed (if desired), and
run vacuum over the table to remove the deleted rows.

Hmm, that would work --- the new tuples commit at the same instant that
the schema updates commit, so it should be correct. You have the 2x
disk usage problem, but there's no way around that without losing
rollback ability.

A potentially tricky bit will be persuading the tuple-reading and tuple-
writing subroutines to pay attention to different versions of the tuple
structure for the same table. I haven't looked to see if this will be
difficult or not. If you can pass the TupleDesc explicitly then it
shouldn't be a problem.

I'd suggest that the cleanup vacuum *not* be an automatic part of
the operation; just recommend that people do it ASAP after dropping
a column. Consider needing to drop several columns...

Does SQL92 syntax allow dropping several columns, i.e.

ALTER TABLE mytable DROP COLUMN col1,col5,col6;

If it does, it would be very desirable to implement it to avoid the need
for vacuum between each DROP in order to have _only_ 2X disk usage.

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

From bouncefilter Sun Feb 27 13:41: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 NAA01584
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 13:41:02 -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 KAA15905;
Sun, 27 Feb 2000 10:20:37 -0800 (PST)
Message-Id: <3.0.1.32.20000227101752.00fae940@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sun, 27 Feb 2000 10:17:52 -0800
To: Hannu Krosing <hannu@tm.ee>, Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] ALTER TABLE DROP COLUMN
Cc: Bruce Momjian <pgman@candle.pha.pa.us>,
Peter Eisentraut <peter_e@gmx.net>,
PostgreSQL Development <pgsql-hackers@postgreSQL.org>
In-Reply-To: <38B967C3.C2C36FE5@tm.ee>
References: <200002260412.XAA14752@candle.pha.pa.us>
<14225.951544893@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 08:06 PM 2/27/00 +0200, Hannu Krosing wrote:

Does SQL92 syntax allow dropping several columns, i.e.

ALTER TABLE mytable DROP COLUMN col1,col5,col6;

My reading of the syntax says no, it is not allowed.

If it does, it would be very desirable to implement it to avoid the need
for vacuum between each DROP in order to have _only_ 2X disk usage.

However, implementing useful extensions to the standard in an
upward-compatible way doesn't bother me.

I'm not fond of language implementations that are full of gratuitous
extensions, but when extensions address real shortcomings in a standard
or intersect with a particular implementation in a useful way, then
it makes sense to add them. In this case, you're asking for an
extension that's useful because Postgres doesn't reclaim storage when
a tuple's deleted, but only when the table's vacuumed. Seems fair
enough.

Whether or not it would be hard to implement is another matter...

- 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 Sun Feb 27 13:58:50 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 NAA03000
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 13:58:34 -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 NAA16103;
Sun, 27 Feb 2000 13:58:31 -0500 (EST)
To: Jan Wieck <wieck@debis.com>
cc: PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] type coerce problem with lztext
In-reply-to: <m12P2QD-0003kGC@orion.SAPserv.Hamburg.dsh.de>
References: <m12P2QD-0003kGC@orion.SAPserv.Hamburg.dsh.de>
Comments: In-reply-to wieck@debis.com (Jan Wieck)
message dated "Sun, 27 Feb 2000 13:03:37 +0100"
Date: Sun, 27 Feb 2000 13:58:31 -0500
Message-ID: <16100.951677911@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

wieck@debis.com (Jan Wieck) writes:

There are functions text(lztext) and lztext(text) too, but
the system is unable to find an operator if one compares
text=lztext in a query. IIRC, creating a function named as
the target type and taking the source type is what made auto-
type-conversion work - so what am I missing here?

Yup, TypeCategory was the missing ingredient. Seems to work now.

Yes, the 3043 bytes long rule action string got stored in 855
bytes in pg_rewrite. That's 71.9% compression rate on this
attempt!

Over all the rules in the regression test database, I see:

regression=# select sum(length(ev_action)),sum(octet_length(ev_action)) from pg
_rewrite;
sum | sum
--------+-------
105270 | 38091
(1 row)

or about 64% compression. Not bad...

regards, tom lane

From bouncefilter Sun Feb 27 14:07:50 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 OAA03736
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 14:07: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
OAA01236;
Sun, 27 Feb 2000 14:06:21 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002271906.OAA01236@candle.pha.pa.us>
Subject: Re: [HACKERS] type coerce problem with lztext
In-Reply-To: <16100.951677911@sss.pgh.pa.us> from Tom Lane at "Feb 27,
2000 01:58:31 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 27 Feb 2000 14:06:21 -0500 (EST)
CC: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <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

Yup, TypeCategory was the missing ingredient. Seems to work now.

Yes, the 3043 bytes long rule action string got stored in 855
bytes in pg_rewrite. That's 71.9% compression rate on this
attempt!

Over all the rules in the regression test database, I see:

regression=# select sum(length(ev_action)),sum(octet_length(ev_action)) from pg
_rewrite;
sum | sum
--------+-------
105270 | 38091
(1 row)

or about 64% compression. Not bad...

We clearly needed this for 7.0 because of the larger plans. Good thing
Jan had it available, becuase I can imagine some major headaches for
people without 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 Sun Feb 27 15:47:53 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 PAA09601
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 15:47:51 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12PAQD-0003kGC; Sun, 27 Feb 100 21:36 MET
Message-Id: <m12PAQD-0003kGC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] type coerce problem with lztext
In-Reply-To: <16100.951677911@sss.pgh.pa.us> from Tom Lane at "Feb 27,
2000 01:58:31 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 27 Feb 2000 21:36:09 +0100 (CET)
CC: Jan Wieck <wieck@debis.com>,
PostgreSQL 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

wieck@debis.com (Jan Wieck) writes:

There are functions text(lztext) and lztext(text) too, but
the system is unable to find an operator if one compares
text=lztext in a query. IIRC, creating a function named as
the target type and taking the source type is what made auto-
type-conversion work - so what am I missing here?

Yup, TypeCategory was the missing ingredient. Seems to work now.

Tnx

Over all the rules in the regression test database, I see:

regression=# select sum(length(ev_action)),sum(octet_length(ev_action)) from pg
_rewrite;
sum | sum
--------+-------
105270 | 38091
(1 row)

or about 64% compression. Not bad...

Amazing, when looking at the simpleness of the algorithm,
isn't it?

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 Sun Feb 27 16:14: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 QAA12662
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 16:14:32 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m12PApo-0003kGC; Sun, 27 Feb 100 22:02 MET
Message-Id: <m12PApo-0003kGC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] type coerce problem with lztext
In-Reply-To: <200002271906.OAA01236@candle.pha.pa.us> from Bruce Momjian at
"Feb 27, 2000 02:06:21 pm"
To: Bruce Momjian <pgman@candle.pha.pa.us>
Date: Sun, 27 Feb 2000 22:02:36 +0100 (CET)
CC: Tom Lane <tgl@sss.pgh.pa.us>, Jan Wieck <wieck@debis.com>,
PostgreSQL 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

Bruce Momjian wrote:

We clearly needed this for 7.0 because of the larger plans. Good thing
Jan had it available, becuase I can imagine some major headaches for
people without it.

I haven't had it available. But where able to dig out some
revision numbers, then take some CVS diffs, and reactivate
two files from the CVS Attic directories.

Here's something close to the new limit:

rulename | length | octet_length
----------------+--------+--------------
_RETv1 | 64677 | 7440

The view v1 is a simple 'SELECT * FROM t1' and t1 is a table
of 220 columns with the same names and types as nearly all
attributes of the system catalogs. Makes me feel comfortable.

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 Sun Feb 27 16:18:52 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 QAA13024
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 16:18: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
QAA06323;
Sun, 27 Feb 2000 16:18:30 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002272118.QAA06323@candle.pha.pa.us>
Subject: Re: [HACKERS] type coerce problem with lztext
In-Reply-To: <m12PApo-0003kGC@orion.SAPserv.Hamburg.dsh.de> from Jan Wieck at
"Feb 27, 2000 10:02:36 pm"
To: Jan Wieck <wieck@debis.com>
Date: Sun, 27 Feb 2000 16:18:30 -0500 (EST)
CC: Tom Lane <tgl@sss.pgh.pa.us>,
PostgreSQL HACKERS <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 wrote:

We clearly needed this for 7.0 because of the larger plans. Good thing
Jan had it available, becuase I can imagine some major headaches for
people without it.

I haven't had it available. But where able to dig out some
revision numbers, then take some CVS diffs, and reactivate
two files from the CVS Attic directories.

Here's something close to the new limit:

rulename | length | octet_length
----------------+--------+--------------
_RETv1 | 64677 | 7440

The view v1 is a simple 'SELECT * FROM t1' and t1 is a table
of 220 columns with the same names and types as nearly all
attributes of the system catalogs. Makes me feel comfortable.

Wow, that's a large number, 64k.

-- 
  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 Sun Feb 27 16:33:52 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 QAA14108
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 16:33:30 -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 NAA07684;
Sun, 27 Feb 2000 13:33:13 -0800 (PST)
Message-Id: <3.0.1.32.20000227133028.00fa2a10@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sun, 27 Feb 2000 13:30:28 -0800
To: Bruce Momjian <pgman@candle.pha.pa.us>, Jan Wieck <wieck@debis.com>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] type coerce problem with lztext
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <200002272118.QAA06323@candle.pha.pa.us>
References: <m12PApo-0003kGC@orion.SAPserv.Hamburg.dsh.de>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 04:18 PM 2/27/00 -0500, Bruce Momjian wrote:

Bruce Momjian wrote:

The view v1 is a simple 'SELECT * FROM t1' and t1 is a table
of 220 columns with the same names and types as nearly all
attributes of the system catalogs. Makes me feel comfortable.

Wow, that's a large number, 64k.

This is the "explosion" in length due to the column aliases now
being inserted into the rule, apparently. The limit on views now
is much more tied to the number of columns in the referenced table(s)
than on the actual complexity of the view's definition per se.

lztext is doing a GREAT job of sweeping this problem under the rug,
so to speak, but it's still there...

- 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 Sun Feb 27 16:36:52 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 QAA14296
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 16:36: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 QAA16775;
Sun, 27 Feb 2000 16:35:54 -0500 (EST)
To: Jan Wieck <wieck@debis.com>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] type coerce problem with lztext
In-reply-to: <m12PApo-0003kGC@orion.SAPserv.Hamburg.dsh.de>
References: <m12PApo-0003kGC@orion.SAPserv.Hamburg.dsh.de>
Comments: In-reply-to wieck@debis.com (Jan Wieck)
message dated "Sun, 27 Feb 2000 22:02:36 +0100"
Date: Sun, 27 Feb 2000 16:35:54 -0500
Message-ID: <16771.951687354@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

wieck@debis.com (Jan Wieck) writes:

rulename | length | octet_length
----------------+--------+--------------
_RETv1 | 64677 | 7440

The view v1 is a simple 'SELECT * FROM t1' and t1 is a table
of 220 columns with the same names and types as nearly all
attributes of the system catalogs. Makes me feel comfortable.

Wow, better than 8-to-1. I guess you'd expect good compression on that,
considering the very repetitive nature of the targetlist node string.
Have you tried something with a long, boring WHERE-clause?

regards, tom lane

From bouncefilter Sun Feb 27 17:02:53 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 RAA15986
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 17:02: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 RAA16924;
Sun, 27 Feb 2000 17:02:02 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] type coerce problem with lztext
In-reply-to: <3.0.1.32.20000227133028.00fa2a10@mail.pacifier.com>
References: <m12PApo-0003kGC@orion.SAPserv.Hamburg.dsh.de>
<3.0.1.32.20000227133028.00fa2a10@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Sun, 27 Feb 2000 13:30:28 -0800"
Date: Sun, 27 Feb 2000 17:02:02 -0500
Message-ID: <16921.951688922@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

This is the "explosion" in length due to the column aliases now
being inserted into the rule, apparently.
lztext is doing a GREAT job of sweeping this problem under the rug,
so to speak, but it's still there...

Actually, as far as I can tell 7.0 should be only marginally worse than
prior releases in terms of verbosity of the rule parsetree string.
As a check I did

create table foo (f1 int, f2 char(10), f3 text);
create view foov as select * from foo;
select ev_action from pg_rewrite where rulename = '_RETfoov';

and got (linebreaks inserted for readability)

({ QUERY :command 1 :utility <> :resultRelation 0 :into <> :isPortal
false :isBinary false :isTemp false :unionall false :distinctClause <>
:sortClause <>
:rtable (
{ RTE :relname foov :ref { ATTR :relname *CURRENT*
:attrs ( "f1" "f2" "f3" )}
:relid 148363 :inh false :inFromCl false :inJoinSet false :skipAcl false}
{ RTE :relname foov :ref { ATTR :relname *NEW*
:attrs ( "f1" "f2" "f3" )}
:relid 148363 :inh false :inFromCl false :inJoinSet false :skipAcl false}
{ RTE :relname foo :ref { ATTR :relname foo
:attrs ( "f1" "f2" "f3" )}
:relid 148352 :inh false :inFromCl true :inJoinSet true :skipAcl false})
:targetlist (
{ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname f1 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 3 :varoattno 1}}
{ TARGETENTRY :resdom { RESDOM :resno 2 :restype 1042 :restypmod 14
:resname f2 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 3 :varattno 2 :vartype 1042 :vartypmod 14
:varlevelsup 0 :varnoold 3 :varoattno 2}}
{ TARGETENTRY :resdom { RESDOM :resno 3 :restype 25 :restypmod -1
:resname f3 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 3 :varattno 3 :vartype 25 :vartypmod -1
:varlevelsup 0 :varnoold 3 :varoattno 3}})
:qual <> :groupClause <> :havingQual <> :hasAggs false :hasSubLinks
false :unionClause <> :intersectClause <> :limitOffset <> :limitCount <>
:rowMark <>})

The thrice-repeated list of attribute names in the rtable entries is
new with Thomas' latest changes, and I'd like to see it go away again,
but even so it's not very long compared to the targetlist entries.

The inJoinSet fields in rtable entries are new, and ressortgroupref
used to be called resgroupref which is costing us 4 more bytes per
targetlist item ;-). But otherwise it's three occurrences of the
field name added onto an existing cost of about 230 bytes per target
entry. This is not an "explosion".

In fact, if I do
select length(ev_action) from pg_rewrite where rulename = '_RETfoov';
I get 1507 in current sources and 1318 in 6.5.3, or about 15% growth.

My guess is that Don's problems are stemming from rules that reference
tables that have many more columns than are being output. Citations
of the otherwise-unreferenced columns in the rtable could add a lot of
bulk that wasn't there before. But it doesn't look to me like the size
of a simple "SELECT *" rule string has grown all that much.

regards, tom lane

From bouncefilter Sun Feb 27 17:24:53 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 RAA17465
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 17:24:13 -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 OAA22345;
Sun, 27 Feb 2000 14:23:35 -0800 (PST)
Message-Id: <3.0.1.32.20000227141624.00fa8cb0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sun, 27 Feb 2000 14:16:24 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] type coerce problem with lztext
Cc: Bruce Momjian <pgman@candle.pha.pa.us>, Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgreSQL.org>
In-Reply-To: <16921.951688922@sss.pgh.pa.us>
References: <3.0.1.32.20000227133028.00fa2a10@mail.pacifier.com>
<m12PApo-0003kGC@orion.SAPserv.Hamburg.dsh.de>
<3.0.1.32.20000227133028.00fa2a10@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 05:02 PM 2/27/00 -0500, Tom Lane wrote:

My guess is that Don's problems are stemming from rules that reference
tables that have many more columns than are being output. Citations
of the otherwise-unreferenced columns in the rtable could add a lot of
bulk that wasn't there before. But it doesn't look to me like the size
of a simple "SELECT *" rule string has grown all that much.

I'll buy that. A couple of the views I was having problems with were
indeed returning a few columns from a view joining a couple of tables, with
in two cases a "where" clause with a further subselect returning
a single column (used on the right of an "="). I might add that the
problem was made worse by the fact that the view itself wasn't as
complex earlier - I updated my PG7.0 snapshot to include Thomas'
last changes at roughly same time I updated the web toolkit.

I picked out one doing just a "select *" as an example because I
felt it would kind of drive the point home that simple views on
relatively small tables were failing...

- 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 Sun Feb 27 17:39:53 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 RAA18432
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 17:39:16 -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 RAA17075
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 17:39:07 -0500 (EST)
To: pgsql-hackers@postgreSQL.org
Subject: A further thought on rule string size
Date: Sun, 27 Feb 2000 17:39:07 -0500
Message-ID: <17072.951691147@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Looking at the example I just cited, it's hard to avoid noticing how
much space is being used on purely-decorative field labels.
For example,

{ TARGETENTRY :resdom { RESDOM :resno 1 :restype 23 :restypmod -1
:resname f1 :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false }
:expr { VAR :varno 3 :varattno 1 :vartype 23 :vartypmod -1
:varlevelsup 0 :varnoold 3 :varoattno 1}}

doesn't really contain any information that's not in

{ TARGETENTRY { RESDOM 1 23 -1 f1 0 0 0 false } { VAR 3 1 23 -1 0 3 1}}

which takes a third as much space. Now I think I'd want to stick with
the more-verbose form for EXPLAIN output and debugging displays, but
I wonder if it isn't worth while to strip the labels from stored rule
strings.

Removing the labels would actually save code in readfuncs.c, which
wouldn't have to skip over them. In outfuncs.c, we could either
have every node-writing subroutine know about two output modes, or
make a post-pass that strips anything that looks like a field label.
The latter would be less maintenance work in the long run.

Comments?

regards, tom lane

From bouncefilter Sun Feb 27 18:42:53 2000
Received: from anchor-post-34.mail.demon.net (anchor-post-34.mail.demon.net
[194.217.242.92]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA27199
for <hackers@postgresql.org>; Sun, 27 Feb 2000 18:42:20 -0500 (EST)
(envelope-from emkxp01@mtcc.demon.co.uk)
Received: from mtcc.demon.co.uk ([158.152.183.103])
by anchor-post-34.mail.demon.net with esmtp (Exim 2.12 #1)
id 12PDKN-000IBk-0Y
for hackers@postgreSQL.org; Sun, 27 Feb 2000 23:42:19 +0000
Received: from mtcc by mtcc.demon.co.uk (8.9.1b+Sun/SMI-SVR4)
id XAA27610; Sun, 27 Feb 2000 23:42:01 GMT
Message-Id: <200002272342.XAA27610@mtcc.demon.co.uk>
Date: Sun, 27 Feb 2000 23:42:00 +0000 (GMT)
From: Keith Parks <emkxp01@mtcc.demon.co.uk>
Reply-To: Keith Parks <emkxp01@mtcc.demon.co.uk>
Subject: Rogue line in regression resultmap file.
To: hackers@postgresql.org
MIME-Version: 1.0
Content-Type: TEXT/plain; charset=us-ascii
Content-MD5: m6BZu8Zi/w0q913d7dSW5g==
X-Mailer: dtmail 1.3.0 CDE Version 1.3 SunOS 5.7 sun4m sparc

Found a rogue line in the resultmap file.

It's only a few days since I last ran the regression tests
so it must have appreared fairly recently.

Here's a patch,
Keith.

*** src/test/regress/resultmap.orig     Sun Feb 27 23:37:03 2000
--- src/test/regress/resultmap  Sun Feb 27 23:37:18 2000
***************
*** 20,24 ****
  horology/sparc-sun-solaris=horology-solaris-1947
  abstime/sparc-sun-solaris=abstime-solaris-1947
  tinterval/sparc-sun-solaris=tinterval-solaris-1947
- #include <sys/types.h>                                /* For pid_t */
- 
--- 20,22 ----

From bouncefilter Sun Feb 27 19:00:54 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 TAA29423
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 19:00:48 -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
SAA10668;
Sun, 27 Feb 2000 18:49:29 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002272349.SAA10668@candle.pha.pa.us>
Subject: Re: [HACKERS] A further thought on rule string size
In-Reply-To: <17072.951691147@sss.pgh.pa.us> from Tom Lane at "Feb 27,
2000 05:39:07 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sun, 27 Feb 2000 18:49:29 -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

Removing the labels would actually save code in readfuncs.c, which
wouldn't have to skip over them. In outfuncs.c, we could either
have every node-writing subroutine know about two output modes, or
make a post-pass that strips anything that looks like a field label.
The latter would be less maintenance work in the long run.

Comments?

If you could keep the labels just for EXPLAIN, go for 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 Sun Feb 27 18:52:54 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA28039
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 18:52:44 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:64005 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S348164AbQB0Xvq>;
Mon, 28 Feb 2000 00:51:46 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12PDWE-0000pe-00; Mon, 28 Feb 2000 00:54:34 +0100
Date: Mon, 28 Feb 2000 00:54:34 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: ALTER TABLE DROP COLUMN
In-Reply-To: <200002260412.XAA14752@candle.pha.pa.us>
Message-ID: <Pine.LNX.4.21.0002280034590.2468-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>

Bruce Momjian writes:

You can exclusively lock the table, then do a heap_getnext() scan over
the entire table, remove the dropped column, do a heap_insert(), then a
heap_delete() on the current tuple,

Wow, that almost seems to easy to be true. I never thought that having
tuples of different structures in the table at the same time would be
possible. If so then I don't see a reason why this would be too hard to
do.

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

From bouncefilter Sun Feb 27 18:53:01 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA28040
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 18:52:45 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:64196 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S348168AbQB0Xvz>;
Mon, 28 Feb 2000 00:51:55 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12PDWP-0000pn-00; Mon, 28 Feb 2000 00:54:45 +0100
Date: Mon, 28 Feb 2000 00:54:45 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] update_pg_pwd trigger does not work very well
In-Reply-To: <11585.951626929@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.21.0002280037230.2468-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>

Tom Lane writes:

1. Since the trigger is executed as soon as a tuple is inserted/
updated/deleted, it will write pg_pwd before the transaction is
committed. If you then abort the transaction, pg_pwd contains wrong
data.

Wow, that implies that every trigger that contains non-database
side-effects is potentially bogus. That never occured to me. Perhaps (as a
future plan), it would be a good idea to have deferred triggers as well?
Now that I think of it, wasn't that the very reason Jan had to invent the
separate constraint triggers?

2. The trigger tries to grab AccessExclusiveLock on pg_shadow.

It doesn't actually need that exclusive lock, I think. A shared read lock
(i.e., none really) would suffice.

A possible solution for these problems is to have the trigger procedure
itself do nothing except set a flag variable. The flag is examined
somewhere in xact.c after successful completion of a transaction,
and if it's set then we run a new transaction cycle in which we
read pg_shadow and write pg_pwd.

If you think that this is okay (and not just a hack), then go for it. If
the above mentioned deferred triggers are at all in the near future I
wouldn't mind scrapping that trigger altogether. There isn't a good reason
to muck with pg_shadow.{usename|password|validuntil} anyway. And it is in
general not safe to muck with system catalogs period. (Try to rename a
table by updating pg_class.relname. ;)

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

From bouncefilter Sun Feb 27 18:52:54 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA28055
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 18:52:52 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:64490 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S348178AbQB0XwI>;
Mon, 28 Feb 2000 00:52:08 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12PDWX-0000q3-00; Mon, 28 Feb 2000 00:54:53 +0100
Date: Mon, 28 Feb 2000 00:54:53 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] type coerce problem with lztext
In-Reply-To: <200002271450.JAA28342@candle.pha.pa.us>
Message-ID: <Pine.LNX.4.21.0002280048190.2468-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>

Bruce Momjian writes:

Added to Features:

New lztext data type for compressed text fields

I strongly suggest to not name this new feature. All the attempts to make
it go away silently in 7.1 will get a blow in the face from this.

Regarding which: Make a default description/comment (DESCR macro) "for
internal use only" and don't mention it in the documention (see last
paragraph), that should suffice. If people disregard that, they probably
use int2vector for their production applications as well.

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

From bouncefilter Sun Feb 27 18:54:54 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA28346;
Sun, 27 Feb 2000 18:54:23 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:62856 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S348168AbQB0Xxf>;
Mon, 28 Feb 2000 00:53:35 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12PDYC-0000tc-00; Mon, 28 Feb 2000 00:56:36 +0100
Date: Mon, 28 Feb 2000 00:56:36 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgresql.org>,
webmaster@postgresql.org
Subject: Re: [HACKERS] Web page on bug reports
In-Reply-To: <200002262144.QAA07442@candle.pha.pa.us>
Message-ID: <Pine.LNX.4.21.0002280055280.2468-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>

Bruce Momjian writes:

This was a helpful introduction to submitting bug reports:

http://www.freshmeat.net/news/2000/02/26/951627540.html

Maybe add it to the web site?

Wow, that is long. I once thought of writing up something along similar
lines. Since you are apparently interested, I could but an abbreviated and
fitted to PostgreSQL edition in the user's guide.

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

From bouncefilter Sun Feb 27 18:56:54 2000
Received: from merganser.its.uu.se (merganser.its.uu.se [130.238.6.236])
by hub.org (8.9.3/8.9.3) with ESMTP id SAA28732
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 18:56:13 -0500 (EST)
(envelope-from peter@localhost.its.uu.se)
Received: from regulus.its.uu.se ([130.238.7.19]:61228 "EHLO
regulus.its.uu.se")
by merganser.its.uu.se with ESMTP id <S348169AbQB0XzY>;
Mon, 28 Feb 2000 00:55:24 +0100
Received: from peter (helo=localhost)
by regulus.its.uu.se with local-esmtp (Exim 3.02 #2)
id 12PDZz-0000te-00; Mon, 28 Feb 2000 00:58:27 +0100
Date: Mon, 28 Feb 2000 00:58:27 +0100 (CET)
From: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Is anyone working on pg_dump?
In-Reply-To: <4779.951674423@sss.pgh.pa.us>
Message-ID: <Pine.LNX.4.21.0002280057150.2468-100000@localhost.localdomain>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=ISO-8859-1
Content-Transfer-Encoding: 8BIT
Sender: Peter Eisentraut <peter@hub.org>

Tom Lane writes:

We have talked about this before, and I thought some people had
expressed interest in fixing it, but I haven't seen any results.
Is anyone working on it?

That was me, but not for 7.0. Feel free to try the oid thing, if it works
well, I might not have to do my thing so early.

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

From bouncefilter Sun Feb 27 19:31:55 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 TAA33188
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 19:31: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
TAA11268;
Sun, 27 Feb 2000 19:17:08 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002280017.TAA11268@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: ALTER TABLE DROP COLUMN
In-Reply-To: <Pine.LNX.4.21.0002280034590.2468-100000@localhost.localdomain>
from Peter Eisentraut at "Feb 28, 2000 00:54:34 am"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Sun, 27 Feb 2000 19:17:08 -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

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

Bruce Momjian writes:

You can exclusively lock the table, then do a heap_getnext() scan over
the entire table, remove the dropped column, do a heap_insert(), then a
heap_delete() on the current tuple,

Wow, that almost seems to easy to be true. I never thought that having
tuples of different structures in the table at the same time would be
possible. If so then I don't see a reason why this would be too hard to
do.

If the transaction is not committed, I don't think anything actually
reads the tuple columns, so you are safe.

-- 
  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 Sun Feb 27 19:31:58 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 TAA33192
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 19:31: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
TAA11340;
Sun, 27 Feb 2000 19:18:22 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002280018.TAA11340@candle.pha.pa.us>
Subject: Re: [HACKERS] update_pg_pwd trigger does not work very well
In-Reply-To: <Pine.LNX.4.21.0002280037230.2468-100000@localhost.localdomain>
from Peter Eisentraut at "Feb 28, 2000 00:54:45 am"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Sun, 27 Feb 2000 19:18:22 -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

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

Tom Lane writes:

1. Since the trigger is executed as soon as a tuple is inserted/
updated/deleted, it will write pg_pwd before the transaction is
committed. If you then abort the transaction, pg_pwd contains wrong
data.

Wow, that implies that every trigger that contains non-database
side-effects is potentially bogus. That never occured to me. Perhaps (as a
future plan), it would be a good idea to have deferred triggers as well?
Now that I think of it, wasn't that the very reason Jan had to invent the
separate constraint triggers?

Yes! I remember him talking about this. I bet you can just modify your
trigger to be of that type.

-- 
  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 Sun Feb 27 20:54:55 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 UAA40568
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 20:54:31 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from cadzone ([126.0.1.40] (may be forged))
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id KAA03525; Mon, 28 Feb 2000 10:54:15 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Bruce Momjian" <pgman@candle.pha.pa.us>
Cc: "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>,
"Peter Eisentraut" <peter_e@gmx.net>
Subject: RE: [HACKERS] Re: ALTER TABLE DROP COLUMN
Date: Mon, 28 Feb 2000 11:00:35 +0900
Message-ID: <000601bf818f$9a2e5f60$2801007e@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To: <200002280017.TAA11268@candle.pha.pa.us>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal

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

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

Bruce Momjian writes:

You can exclusively lock the table, then do a heap_getnext() scan over
the entire table, remove the dropped column, do a

heap_insert(), then a

heap_delete() on the current tuple,

Wow, that almost seems to easy to be true. I never thought that having
tuples of different structures in the table at the same time would be
possible. If so then I don't see a reason why this would be too hard to
do.

If the transaction is not committed, I don't think anything actually
reads the tuple columns, so you are safe.

Hmm,tuples of multiple version in a table ?
This is neither clean nor easy for me.
There's no such stuff which takes the case into account,AFAIK.

Seems no one but me object to it. I'm tired of this issue and it's
painful for me to continue discussion further in my poor English.
I may be able to provide another implementation on trial and it
may be easier than only objecting to your proposal.
Is it OK ?

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Sun Feb 27 21:04:55 2000
Received: from paprika.michvhf.com (paprika.michvhf.com [209.57.60.12])
by hub.org (8.9.3/8.9.3) with SMTP id VAA41422
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 21:04:29 -0500 (EST) (envelope-from vev@michvhf.com)
Received: (qmail 2124 invoked by uid 1001); 28 Feb 2000 02:04:29 -0000
Date: Sun, 27 Feb 2000 21:04:29 -0500 (EST)
From: Vince Vielhaber <vev@michvhf.com>
To: Peter Eisentraut <peter_e@gmx.net>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
PostgreSQL-development <pgsql-hackers@postgresql.org>,
webmaster@postgresql.org
Subject: Re: [HACKERS] Web page on bug reports
In-Reply-To: <Pine.LNX.4.21.0002280055280.2468-100000@localhost.localdomain>
Message-ID: <Pine.BSF.4.05.10002272102560.1615-100000@paprika.michvhf.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Mon, 28 Feb 2000, Peter Eisentraut wrote:

Bruce Momjian writes:

This was a helpful introduction to submitting bug reports:

http://www.freshmeat.net/news/2000/02/26/951627540.html

Maybe add it to the web site?

Wow, that is long. I once thought of writing up something along similar
lines. Since you are apparently interested, I could but an abbreviated and
fitted to PostgreSQL edition in the user's guide.

That is extremely long. I'd be interested in seeing what Peter could
do in a shortened version. It could go on the website.

Vince.
--
==========================================================================
Vince Vielhaber -- KA8CSH email: vev@michvhf.com http://www.pop4.net
128K ISDN: $24.95/mo or less - 56K Dialup: $17.95/mo or less at Pop4
Online Campground Directory http://www.camping-usa.com
Online Giftshop Superstore http://www.cloudninegifts.com
==========================================================================

From bouncefilter Sun Feb 27 21:13:55 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 VAA42313
for <hackers@postgreSQL.org>; Sun, 27 Feb 2000 21:13: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
VAA13958;
Sun, 27 Feb 2000 21:12:54 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002280212.VAA13958@candle.pha.pa.us>
Subject: Re: [HACKERS] Rogue line in regression resultmap file.
In-Reply-To: <200002272342.XAA27610@mtcc.demon.co.uk> from Keith Parks at "Feb
27, 2000 11:42:00 pm"
To: Keith Parks <emkxp01@mtcc.demon.co.uk>
Date: Sun, 27 Feb 2000 21:12:54 -0500 (EST)
CC: 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

Found a rogue line in the resultmap file.

It's only a few days since I last ran the regression tests
so it must have appreared fairly recently.

Here's a patch,
Keith.

*** src/test/regress/resultmap.orig     Sun Feb 27 23:37:03 2000
--- src/test/regress/resultmap  Sun Feb 27 23:37:18 2000
***************
*** 20,24 ****
horology/sparc-sun-solaris=horology-solaris-1947
abstime/sparc-sun-solaris=abstime-solaris-1947
tinterval/sparc-sun-solaris=tinterval-solaris-1947
- #include <sys/types.h>                                /* For pid_t */
- 

What, how did that get in there? I assume it was during:

revision 1.7
date: 2000/02/23 15:46:15; author: momjian; state: Exp; lines: +5 -0
1. miscadmin.h needs to include sys/types.h for a definition of pid_t

It is already in miscadmin.h, so I guess it was some fluke on my end.
Sorry.

-- 
  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 Sun Feb 27 21:18:55 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 VAA42905;
Sun, 27 Feb 2000 21:18:34 -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
VAA14094;
Sun, 27 Feb 2000 21:18:24 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002280218.VAA14094@candle.pha.pa.us>
Subject: Re: [HACKERS] Web page on bug reports
In-Reply-To: <Pine.LNX.4.21.0002280055280.2468-100000@localhost.localdomain>
from Peter Eisentraut at "Feb 28, 2000 00:56:36 am"
To: Peter Eisentraut <peter_e@gmx.net>
Date: Sun, 27 Feb 2000 21:18:24 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>,
webmaster@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...]

Bruce Momjian writes:

This was a helpful introduction to submitting bug reports:

http://www.freshmeat.net/news/2000/02/26/951627540.html

Maybe add it to the web site?

Wow, that is long. I once thought of writing up something along similar
lines. Since you are apparently interested, I could but an abbreviated and
fitted to PostgreSQL edition in the user's guide.

If you want. Seems it is helpful for all bug report cases, not just
PostgreSQL ones.

-- 
  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 Sun Feb 27 21:24:55 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 VAA43630
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 21:24:34 -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 VAA20105;
Sun, 27 Feb 2000 21:24:12 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Bruce Momjian <pgman@candle.pha.pa.us>, Jan Wieck <wieck@debis.com>,
PostgreSQL HACKERS <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] type coerce problem with lztext
In-reply-to: <Pine.LNX.4.21.0002280048190.2468-100000@localhost.localdomain>
References: <Pine.LNX.4.21.0002280048190.2468-100000@localhost.localdomain>
Comments: In-reply-to Peter Eisentraut <peter_e@gmx.net>
message dated "Mon, 28 Feb 2000 00:54:53 +0100"
Date: Sun, 27 Feb 2000 21:24:11 -0500
Message-ID: <20102.951704651@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Peter Eisentraut <peter_e@gmx.net> writes:

New lztext data type for compressed text fields

I strongly suggest to not name this new feature. All the attempts to make
it go away silently in 7.1 will get a blow in the face from this.

People *will* use it, if it's there. Don't fool yourself.

However, we can make it "go away silently" the same way we are making
datetime go away: the 7.1 (or whatever) parser can just translate the
typename lztext to text. If that weren't feasible then I'd be pretty
worried too.

regards, tom lane

From bouncefilter Sun Feb 27 21:30:55 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 VAA44011
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 21:30: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 VAA20135;
Sun, 27 Feb 2000 21:30:14 -0500 (EST)
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>
cc: "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Re: ALTER TABLE DROP COLUMN
In-reply-to: <000601bf818f$9a2e5f60$2801007e@tpf.co.jp>
References: <000601bf818f$9a2e5f60$2801007e@tpf.co.jp>
Comments: In-reply-to "Hiroshi Inoue" <Inoue@tpf.co.jp>
message dated "Mon, 28 Feb 2000 11:00:35 +0900"
Date: Sun, 27 Feb 2000 21:30:14 -0500
Message-ID: <20132.951705014@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

Hmm,tuples of multiple version in a table ?
This is neither clean nor easy for me.

I'm worried about it too. I think it could maybe be made to work,
but it seems fragile.

I may be able to provide another implementation on trial and it
may be easier than only objecting to your proposal.

If you have a better idea, let's hear it!

regards, tom lane

From bouncefilter Sun Feb 27 21:35:55 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 VAA44427
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 21:35:16 -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
VAA14532;
Sun, 27 Feb 2000 21:34:55 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002280234.VAA14532@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: ALTER TABLE DROP COLUMN
In-Reply-To: <000601bf818f$9a2e5f60$2801007e@tpf.co.jp> from Hiroshi Inoue at
"Feb 28, 2000 11:00:35 am"
To: Hiroshi Inoue <Inoue@tpf.co.jp>
Date: Sun, 27 Feb 2000 21:34:55 -0500 (EST)
CC: PostgreSQL Development <pgsql-hackers@postgreSQL.org>,
Peter Eisentraut <peter_e@gmx.net>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Wow, that almost seems to easy to be true. I never thought that having
tuples of different structures in the table at the same time would be
possible. If so then I don't see a reason why this would be too hard to
do.

If the transaction is not committed, I don't think anything actually
reads the tuple columns, so you are safe.

Hmm,tuples of multiple version in a table ?
This is neither clean nor easy for me.
There's no such stuff which takes the case into account,AFAIK.

Seems no one but me object to it. I'm tired of this issue and it's
painful for me to continue discussion further in my poor English.
I may be able to provide another implementation on trial and it
may be easier than only objecting to your proposal.
Is it OK ?

Sure, whatever you want. No one is going to start coding anything for a
while. Seemed like a clean solution with no rename() 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 Sun Feb 27 22:16:56 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 WAA47081
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 22:16:02 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from cadzone ([126.0.1.40] (may be forged))
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id MAA03587; Mon, 28 Feb 2000 12:15:15 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "Tom Lane" <tgl@sss.pgh.pa.us>
Cc: "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Re: ALTER TABLE DROP COLUMN
Date: Mon, 28 Feb 2000 12:21:36 +0900
Message-ID: <000801bf819a$eb8b8800$2801007e@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 8.5, Build 4.71.2173.0
In-Reply-To: <20132.951705014@sss.pgh.pa.us>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal

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

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

Hmm,tuples of multiple version in a table ?
This is neither clean nor easy for me.

I'm worried about it too. I think it could maybe be made to work,
but it seems fragile.

I may be able to provide another implementation on trial and it
may be easier than only objecting to your proposal.

If you have a better idea, let's hear it!

I don't want a final implementation this time.
What I want is to provide a quick hack for both others and me
to judge whether this direction is good or not.

My idea is essentially an invisible column implementation.
DROP COLUMN would change the target pg_attribute tuple
as follows..

attnum -> an offset - attnum;
atttypid -> 0

We would be able to see where to change by tracking error/
crashes caused by this change.

I would also change attname to '*already dropped %d' for
examle to avoid duplicate attname.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Sun Feb 27 22:40:57 2000
Received: from thelab.hub.org (nat196.115.mpoweredpc.net [142.177.196.115])
by hub.org (8.9.3/8.9.3) with ESMTP id WAA48615
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 22:40:25 -0500 (EST) (envelope-from scrappy@hub.org)
Received: from localhost (scrappy@localhost)
by thelab.hub.org (8.9.3/8.9.1) with ESMTP id XAA24766;
Sun, 27 Feb 2000 23:40:11 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Sun, 27 Feb 2000 23:40:11 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Hiroshi Inoue <Inoue@tpf.co.jp>
cc: Tom Lane <tgl@sss.pgh.pa.us>,
PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: RE: [HACKERS] Re: ALTER TABLE DROP COLUMN
In-Reply-To: <000801bf819a$eb8b8800$2801007e@tpf.co.jp>
Message-ID: <Pine.BSF.4.21.0002272337290.81087-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Mon, 28 Feb 2000, Hiroshi Inoue wrote:

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

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

Hmm,tuples of multiple version in a table ?
This is neither clean nor easy for me.

I'm worried about it too. I think it could maybe be made to work,
but it seems fragile.

I may be able to provide another implementation on trial and it
may be easier than only objecting to your proposal.

If you have a better idea, let's hear it!

I don't want a final implementation this time.
What I want is to provide a quick hack for both others and me
to judge whether this direction is good or not.

My idea is essentially an invisible column implementation.
DROP COLUMN would change the target pg_attribute tuple
as follows..

attnum -> an offset - attnum;
atttypid -> 0

We would be able to see where to change by tracking error/
crashes caused by this change.

I would also change attname to '*already dropped %d' for
examle to avoid duplicate attname.

Okay, just curious here, but ... what you are proposing *sounds* to me
like half-way to what started this thread. (*Please* correct me if I'm
wrong) ...

Essentially, in your proposal, when you drop a column, all subsequent
tuples inserted/updated would have ... that one column missing? So,
instead of doing a massive sweep through the table and removing that
column, only do it when an insert/update happens?

Basically, eliminate the requirement to re-write every tuples, only those
that have activity?

From bouncefilter Sun Feb 27 22:54:57 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 WAA50008
for <pgsql-hackers@postgreSQL.org>;
Sun, 27 Feb 2000 22:54:06 -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
WAA16870;
Sun, 27 Feb 2000 22:52:30 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200002280352.WAA16870@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: ALTER TABLE DROP COLUMN
In-Reply-To: <Pine.BSF.4.21.0002272337290.81087-100000@thelab.hub.org> from
The
Hermit Hacker at "Feb 27, 2000 11:40:11 pm"
To: The Hermit Hacker <scrappy@hub.org>
Date: Sun, 27 Feb 2000 22:52:30 -0500 (EST)
CC: Hiroshi Inoue <Inoue@tpf.co.jp>, 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 would also change attname to '*already dropped %d' for
examle to avoid duplicate attname.

Okay, just curious here, but ... what you are proposing *sounds* to me
like half-way to what started this thread. (*Please* correct me if I'm
wrong) ...

Essentially, in your proposal, when you drop a column, all subsequent
tuples inserted/updated would have ... that one column missing? So,
instead of doing a massive sweep through the table and removing that
column, only do it when an insert/update happens?

Basically, eliminate the requirement to re-write every tuples, only those
that have activity?

And I think the problem was that there was too much code to modify to
allow this.

-- 
  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 Sun Feb 27 23:19:57 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 XAA51908
for <pgsql-hackers@postgresql.org>;
Sun, 27 Feb 2000 23:19:35 -0500 (EST) (envelope-from Inoue@tpf.co.jp)
Received: from cadzone ([126.0.1.40] (may be forged))
by sd.tpf.co.jp (2.5 Build 2640 (Berkeley 8.8.6)/8.8.4) with SMTP
id NAA03626; Mon, 28 Feb 2000 13:18:44 +0900
From: "Hiroshi Inoue" <Inoue@tpf.co.jp>
To: "The Hermit Hacker" <scrappy@hub.org>
Cc: "Tom Lane" <tgl@sss.pgh.pa.us>,
"PostgreSQL Development" <pgsql-hackers@postgresql.org>
Subject: RE: [HACKERS] Re: ALTER TABLE DROP COLUMN
Date: Mon, 28 Feb 2000 13:25:06 +0900
Message-ID: <000e01bf81a3$ca22e4c0$2801007e@tpf.co.jp>
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0
In-Reply-To: <Pine.BSF.4.21.0002272337290.81087-100000@thelab.hub.org>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
Importance: Normal

-----Original Message-----
From: The Hermit Hacker [mailto:scrappy@hub.org]

On Mon, 28 Feb 2000, Hiroshi Inoue wrote:

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

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

Hmm,tuples of multiple version in a table ?
This is neither clean nor easy for me.

I'm worried about it too. I think it could maybe be made to work,
but it seems fragile.

I may be able to provide another implementation on trial and it
may be easier than only objecting to your proposal.

If you have a better idea, let's hear it!

[snip]

Okay, just curious here, but ... what you are proposing *sounds* to me
like half-way to what started this thread. (*Please* correct me if I'm
wrong) ...

My proposal is essentially same as what I proposed once in this thread.
I don't think DROP COLUMN feature is very important.
DROP/ADD CONSTRAINT feature seems much more important.
Why do you want a heavy iplementation like vacuum after 2x disk
usage for this feature ?
My implementation won't touch the target table at all and would never
remove dropped columns practically. It would only make them invisible
and NULL would be set for newly insert/updated columns.

If you want a really clean table for DROP TABLE command,my
proposal is useless.

Regards.

Hiroshi Inoue
Inoue@tpf.co.jp

From bouncefilter Mon Feb 28 00:34:58 2000
Received: from clio.trends.ca (root@clio.trends.ca [209.47.148.2])
by hub.org (8.9.3/8.9.3) with ESMTP id AAA58576
for <pgsql-hackers@postgreSQL.org>;
Mon, 28 Feb 2000 00:34:29 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from eclipse.pacifier.com (eclipse.pacifier.com [199.2.117.78])
by clio.trends.ca (8.9.3+Sun/8.9.3) with ESMTP id AAA20990
for <pgsql-hackers@postgreSQL.org>;
Mon, 28 Feb 2000 00:34:25 -0500 (EST)
Received: from desktop (dsl-dhogaza.pacifier.net [216.65.147.68])
by eclipse.pacifier.com (8.9.3/8.9.3pop) with SMTP id VAA04080;
Sun, 27 Feb 2000 21:34:14 -0800 (PST)
Message-Id: <3.0.1.32.20000227212630.00fbfb20@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sun, 27 Feb 2000 21:26:30 -0800
To: "Hiroshi Inoue" <Inoue@tpf.co.jp>, "Tom Lane" <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: RE: [HACKERS] Re: ALTER TABLE DROP COLUMN
Cc: "PostgreSQL Development" <pgsql-hackers@postgreSQL.org>
In-Reply-To: <000801bf819a$eb8b8800$2801007e@tpf.co.jp>
References: <20132.951705014@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 12:21 PM 2/28/00 +0900, Hiroshi Inoue wrote:

My idea is essentially an invisible column implementation.
DROP COLUMN would change the target pg_attribute tuple
as follows..

I don't see such a solution as being mutually exclusive with
the other one on the table.

Remember ... Oracle provides both. I suspect that they did so
because they were under customer pressure to provide a "real"
column drop and a "fast" (and non-2x tablesize!) solution. So
they did both. Also keep in mind that being able to drop a
column in Oracle is a year 1999 feature ... and both are provided.
More evidence of pressure from two points of view.

Of course, PG suffers because the "real" column drop is a 2x
space solution, so the "invisibility" approach may more frequently
be desired.

Still... as time goes on and PG gets adopted by more and more
serious, large-scale users (which we all are working towards,
right?) I suspect that each camp will want to be served.

- 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 Mon Feb 28 00:34:57 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 AAA58577
for <pgsql-hackers@postgreSQL.org>;
Mon, 28 Feb 2000 00:34:29 -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 VAA04102;
Sun, 27 Feb 2000 21:34:17 -0800 (PST)
Message-Id: <3.0.1.32.20000227213006.00fc1750@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sun, 27 Feb 2000 21:30:06 -0800
To: The Hermit Hacker <scrappy@hub.org>, Hiroshi Inoue <Inoue@tpf.co.jp>
From: Don Baccus <dhogaza@pacifier.com>
Subject: RE: [HACKERS] Re: ALTER TABLE DROP COLUMN
Cc: Tom Lane <tgl@sss.pgh.pa.us>,
PostgreSQL Development <pgsql-hackers@postgreSQL.org>
In-Reply-To: <Pine.BSF.4.21.0002272337290.81087-100000@thelab.hub.org>
References: <000801bf819a$eb8b8800$2801007e@tpf.co.jp>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 11:40 PM 2/27/00 -0400, The Hermit Hacker wrote:

Okay, just curious here, but ... what you are proposing *sounds* to me
like half-way to what started this thread. (*Please* correct me if I'm
wrong) ...

Essentially, in your proposal, when you drop a column, all subsequent
tuples inserted/updated would have ... that one column missing? So,
instead of doing a massive sweep through the table and removing that
column, only do it when an insert/update happens?

Basically, eliminate the requirement to re-write every tuples, only those
that have activity?

Yes, this was one of the ideas that cropped up in previous discussion.

- 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 Mon Feb 28 00:49:58 2000
Received: from web122.yahoomail.com (web122.yahoomail.com [205.180.60.57])
by hub.org (8.9.3/8.9.3) with SMTP id AAA59892
for <pgsql-hackers@postgresql.org>;
Mon, 28 Feb 2000 00:49:09 -0500 (EST)
(envelope-from subaesh1253@yahoo.com)
Received: (qmail 13964 invoked by uid 60001); 28 Feb 2000 05:54:09 -0000
Message-ID: <20000228055409.13963.qmail@web122.yahoomail.com>
Received: from [24.42.0.44] by web122.yahoomail.com;
Sun, 27 Feb 2000 21:54:09 PST
Date: Sun, 27 Feb 2000 21:54:09 -0800 (PST)
From: subaesh ramjan <subaesh1253@yahoo.com>
Subject: info
To: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

i was wondering if u have the crack for catia v5 r3

and nastranv70.5

thanks i realy need those cracks

__________________________________________________
Do You Yahoo!?
Talk to your friends online with Yahoo! Messenger.
http://im.yahoo.com