Index scan on CIDR field ?

Margarit Nickolov <man@digsys.bg> writes:

I created index on cidr field on table with about 1 000 000 rows, made
'vacuum analyze', but exlain told me that sequental scan is done
on query like that SELECT * FROM table WHERE ipaddr='212.129.92.1'
I'm using PostgreSQL 6.5.1.

Hmm. I think this is an artifact of the recently noticed mistake in
6.5's pg_opclass table: it uses the same name "network_ops" for two
different index operator classes.

I found that current sources seem to work properly:

create table cidr1 (f1 cidr);
create index cidri on cidr1 (f1);
explain select * from cidr1 where f1 = '212.129.92.1';

Index Scan using cidri on cidr1 (cost=2.50 rows=10 width=12)

but 6.5.3 chooses a sequential scan, just as Margarit says.

Furthermore the pg_index entry for cidri is wrong in 6.5.3;
it shows indclass = 935 (pg_opclass entry for inet) whereas current
sources show 652 (the one for cidr). I haven't bothered to track down
exactly where the confusion occurs in the code, but I'll bet some part
of index creation is assuming that index opclass names are unique.
The wrong pg_index entry explains why the optimizer is ignoring the
index; it's looking for one whose opclass matches the cidr '=' op
it's trying to optimize.

Margarit, I think you can fix this in a 6.5.* database as follows:
as postgres, say

UPDATE pg_opclass SET opcname = 'inet_ops' WHERE oid = 935;
UPDATE pg_opclass SET opcname = 'cidr_ops' WHERE oid = 652;

Then drop and recreate the faulty index(es). (Probably any index you
have on a cidr column is messed up.)

Better back up your database before trying this!!! It seemed to work
in a play database, but I make no guarantees.

Note to hackers: perhaps we should recommend that anyone using inet or
cidr indexes do this? If they don't, when it comes time to update to
7.0 their pg_dumped index declarations will fail, since 7.0 won't
recognize "network_ops" as an index opclass name.

regards, tom lane

From bouncefilter Wed Dec 29 11:03:09 1999
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 LAA01132
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 11:02: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 LAA04047;
Wed, 29 Dec 1999 11:01:37 -0500 (EST)
To: Adriaan Joubert <a.joubert@albourne.com>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Index corruption
In-reply-to: <386A1DE5.C56C01A6@albourne.com>
References: <38626197.4ED6092F@albourne.com> <386A0806.33DD9CE0@albourne.com>
<386A1457.432DCE8E@albourne.com> <386A1DE5.C56C01A6@albourne.com>
Comments: In-reply-to Adriaan Joubert <a.joubert@albourne.com>
message dated "Wed, 29 Dec 1999 16:42:45 +0200"
Date: Wed, 29 Dec 1999 11:01:36 -0500
Message-ID: <4044.946483296@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

pg_proc_prosrc_index is the problem, eh? I'll bet a nickel that you're
seeing still another manifestation of btree's problems with oversized
index entries. (See recent thread 'Error "vacuum pg_proc"'.)

Check to see if you have any functions whose definitions exceed 2700
bytes, eg with
select proname from pg_proc where length(prosrc) > 2700;
If so, you need to rewrite them to be smaller, perhaps by breaking
them into multiple functions.

7.0 should fix this problem, but it's a real hazard in 6.5.

regards, tom lane

From bouncefilter Wed Dec 29 11:10:09 1999
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 LAA03836
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 11:09:18 -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 LAA04094;
Wed, 29 Dec 1999 11:07:26 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: subquery performance and EXISTS
In-reply-to: <199912291411.JAA02236@candle.pha.pa.us>
References: <199912291411.JAA02236@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Wed, 29 Dec 1999 09:11:51 -0500"
Date: Wed, 29 Dec 1999 11:07:26 -0500
Message-ID: <4091.946483646@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

Tom, does it still look easy to change, and if so, can you change it to
hash?

We could probably do it, but there is the little problem that hash joins
only work for a very limited set of data types and operators. This
wouldn't be a complete solution on its own.

regards, tom lane

From bouncefilter Wed Dec 29 14:22:12 1999
Received: from news.tht.net (news.hub.org [216.126.91.242])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA45428
for <pgsql-hackers@postgresql.org>;
Wed, 29 Dec 1999 14:21:20 -0500 (EST)
(envelope-from news@news.tht.net)
Received: (from news@localhost) by news.tht.net (8.9.3/8.9.3) id OAA02522
for pgsql-hackers@postgresql.org; Wed, 29 Dec 1999 14:20:30 -0500 (EST)
(envelope-from news)
X-Authentication-Warning: news.tht.net: news set sender to <news> using -f
From: "svn" <svngo@earthlink.net>
X-Newsgroups: comp.databases.postgresql.hackers
Subject: revoke/grant rights issue
Date: Wed, 29 Dec 1999 12:14:55 -0600
X-Posted-Path-Was: not-for-mail
X-Priority: 3
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
X-ELN-Date: 29 Dec 1999 19:14:04 GMT
X-ELN-Insert-Date: Wed Dec 29 11:15:06 1999
Organization: EarthLink Network, Inc.
X-Newsreader: Microsoft Outlook Express 5.00.2314.1300
X-MSMail-Priority: Normal
Lines: 13
Message-ID: <84dmhs$lmu$1@oak.prod.itd.earthlink.net>
To: pgsql-hackers@postgresql.org

Can someone direct me as to why the rights that I specify for a given table
doesn't take affect? I revoked all rights to the "Public" and the postgres
default id "nobody" can still do inserts, deletes, updates, etc. I do a \z
on the table and it shows no rights, but still allows anyone w/ a valid
postgres id to wipe out any row they wish. I've even tried to manually
revoke the rights from "nobody" and it still doesn't work. Are there
setting some where that "turns on" the specified privileges intiated by the
revoke/grant commands?

Thanks in advance.....

From bouncefilter Wed Dec 29 14:08:11 1999
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 OAA43511
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 14:07:53 -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
OAA07128;
Wed, 29 Dec 1999 14:07:32 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912291907.OAA07128@candle.pha.pa.us>
Subject: Re: [HACKERS] Index corruption
In-Reply-To: <386A2CEF.ACC31BA2@albourne.com> from Adriaan Joubert at "Dec 29,
1999 05:46:55 pm"
To: Adriaan Joubert <a.joubert@albourne.com>
Date: Wed, 29 Dec 1999 14:07:32 -0500 (EST)
CC: pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Were you the person I told to use pg_upgrade and re-initdb

your > database? If not, I would recommend that as the fix.
You may need to > re-enable pg_upgrade by editing the script.

Nope, but I've tried this now and it fails miserably for me. I
had to edit the file created by pg_dump quote heavily as I have
user-defined types and they required some initialisation before
they could be used in the definition of indexes. Anyway, I
managed to work around that, and pg_upgrade claimed that everything
had finished successfully. But, although the datafiles were in
the right place and the right size all the tables were empty.
So some system tables were evidently not initialised correctly.
I tried this both with 6.5.2 and 6.5.3, but no luck either way.

All I can say is someone did this recently for a system index problem
and it worked.

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

From bouncefilter Wed Dec 29 14:10:11 1999
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 OAA43676
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 14:09:18 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
OAA07158;
Wed, 29 Dec 1999 14:09:00 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912291909.OAA07158@candle.pha.pa.us>
Subject: Re: [HACKERS] Index corruption
In-Reply-To: <4044.946483296@sss.pgh.pa.us> from Tom Lane at "Dec 29,
1999 11:01:36 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 29 Dec 1999 14:09:00 -0500 (EST)
CC: Adriaan Joubert <a.joubert@albourne.com>, pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

pg_proc_prosrc_index is the problem, eh? I'll bet a nickel that you're
seeing still another manifestation of btree's problems with oversized
index entries. (See recent thread 'Error "vacuum pg_proc"'.)

Check to see if you have any functions whose definitions exceed 2700
bytes, eg with
select proname from pg_proc where length(prosrc) > 2700;
If so, you need to rewrite them to be smaller, perhaps by breaking
them into multiple functions.

7.0 should fix this problem, but it's a real hazard in 6.5.

Wow, do we need that 7.0 release!

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

From bouncefilter Wed Dec 29 14:52:11 1999
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 OAA51996
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 14:51:30 -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 OAA08646
for pgsql-hackers@postgreSQL.org; Wed, 29 Dec 1999 14:51:19 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912291951.OAA08646@candle.pha.pa.us>
Subject: Using aggregate in HAVING
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: Wed, 29 Dec 1999 14:51:19 -0500 (EST)
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

How would I get all friends greater than the average age?

CREATE TABLE friends (
firstname CHAR(15),
lastname CHAR(20),
age INTEGER)

SELECT firstname, lastname
FROM friends
HAVING age >= AVG(age)

ERROR: Attribute friends.firstname must be GROUPed or used in an
aggregate function

This fails too:

SELECT firstname, lastname
FROM friends
WHERE age >= AVG(age)

ERROR: Aggregates not allowed in WHERE clause

This fails. I am stumped.

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

From bouncefilter Wed Dec 29 15:19:12 1999
Received: from corvette.mascari.com (dhcp26131045.columbus.rr.com
[24.26.131.45]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA57880
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 15:19:09 -0500 (EST)
(envelope-from mascarm@mascari.com)
Received: from mascari.com (ferrari.mascari.com [192.168.2.1])
by corvette.mascari.com (8.8.7/8.8.7) with ESMTP id PAA14701;
Wed, 29 Dec 1999 15:15:49 -0500
Message-ID: <386A6C58.C453600D@mascari.com>
Date: Wed, 29 Dec 1999 15:17:28 -0500
From: Mike Mascari <mascarm@mascari.com>
Organization: Mascari Development Inc
X-Mailer: Mozilla 4.7 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Using aggregate in HAVING
References: <199912291951.OAA08646@candle.pha.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Bruce Momjian wrote:

How would I get all friends greater than the average age?

CREATE TABLE friends (
firstname CHAR(15),
lastname CHAR(20),
age INTEGER)

SELECT firstname, lastname
FROM friends
HAVING age >= AVG(age)

ERROR: Attribute friends.firstname must be GROUPed or used in an
aggregate function

This fails too:

SELECT firstname, lastname
FROM friends
WHERE age >= AVG(age)

ERROR: Aggregates not allowed in WHERE clause

This fails. I am stumped.

Without using subselects? With subselects you could also do:

SELECT firstname, lastname
FROM friends
WHERE age >= (SELECT AVG(age) FROM friends);

Are you writing the chapter on aggregates?

Mike Mascari

From bouncefilter Wed Dec 29 15:27:13 1999
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 PAA58648
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 15:26:40 -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
PAA09659;
Wed, 29 Dec 1999 15:25:27 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912292025.PAA09659@candle.pha.pa.us>
Subject: Re: [HACKERS] Using aggregate in HAVING
In-Reply-To: <386A6C58.C453600D@mascari.com> from Mike Mascari at "Dec 29,
1999
03:17:28 pm"
To: Mike Mascari <mascarm@mascari.com>
Date: Wed, 29 Dec 1999 15:25:27 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Bruce Momjian wrote:

How would I get all friends greater than the average age?

CREATE TABLE friends (
firstname CHAR(15),
lastname CHAR(20),
age INTEGER)

SELECT firstname, lastname
FROM friends
HAVING age >= AVG(age)

ERROR: Attribute friends.firstname must be GROUPed or used in an
aggregate function

This fails too:

SELECT firstname, lastname
FROM friends
WHERE age >= AVG(age)

ERROR: Aggregates not allowed in WHERE clause

This fails. I am stumped.

Without using subselects? With subselects you could also do:

SELECT firstname, lastname
FROM friends
WHERE age >= (SELECT AVG(age) FROM friends);

Are you writing the chapter on aggregates?

I have finished the aggregate chapter, and am doing the subselect
chapter. I thought using a subselect for this example would be great,
but then I thought, "Gee, why can't HAVING do that?" However, I am
realizing that HAVING can't because without a GROUP BY, it applies to
all rows as a whole, and there is no meaningful GROUP BY for this case.

My subquery figure actually will show how HAVING fails, and how
subqueries allow this. Now, I am just asking for confirmation that this
is true.

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

From bouncefilter Wed Dec 29 15:59:12 1999
Received: from corvette.mascari.com (dhcp26131045.columbus.rr.com
[24.26.131.45]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA65431
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 15:59:03 -0500 (EST)
(envelope-from mascarm@mascari.com)
Received: from mascari.com (ferrari.mascari.com [192.168.2.1])
by corvette.mascari.com (8.8.7/8.8.7) with ESMTP id PAA14784;
Wed, 29 Dec 1999 15:56:04 -0500
Message-ID: <386A75C8.2CDF0F58@mascari.com>
Date: Wed, 29 Dec 1999 15:57:44 -0500
From: Mike Mascari <mascarm@mascari.com>
Organization: Mascari Development Inc
X-Mailer: Mozilla 4.7 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Using aggregate in HAVING
References: <199912292025.PAA09659@candle.pha.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Bruce Momjian wrote:

I have finished the aggregate chapter, and am doing the subselect
chapter. I thought using a subselect for this example would be great,
but then I thought, "Gee, why can't HAVING do that?" However, I am
realizing that HAVING can't because without a GROUP BY, it applies to
all rows as a whole, and there is no meaningful GROUP BY for this case.

My subquery figure actually will show how HAVING fails, and how
subqueries allow this. Now, I am just asking for confirmation that this
is true.

Well...actually, you can use a self-join like so:

SELECT f1.lastname, f1.firstname, f1.age, avg(f2.age)
FROM friends f1, friends f2
WHERE true
GROUP BY f1.lastname, f1.firstname, f1.age
HAVING f1.age > avg(f2.age);

I don't think you'll be able to state that subselects allow for
queries that HAVING won't. Proving a negative can be very
difficult (although I think you're probably right).

Mike Mascari

From bouncefilter Wed Dec 29 15:53:13 1999
Received: from cap-ferrat.albourne.com (cap-ferrat.albourne.com
[195.89.178.227]) by hub.org (8.9.3/8.9.3) with ESMTP id PAA64871
for <pgsql-hackers@postgresql.org>;
Wed, 29 Dec 1999 15:52:58 -0500 (EST)
(envelope-from a.joubert@albourne.com)
Received: from albourne.com (bishop-rock.albourne.com [195.89.178.230])
by cap-ferrat.albourne.com (8.9.3/8.9.3/Albourne/UKS/2.9/MAPS) with
ESMTP id UAA29237; Wed, 29 Dec 1999 20:52:53 GMT
Sender: a.joubert@albourne.com
Message-ID: <386A7658.13933C0A@albourne.com>
Date: Wed, 29 Dec 1999 23:00:08 +0200
From: Adriaan Joubert <a.joubert@albourne.com>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.0.38 i586)
X-Accept-Language: en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Index corruption
References: <38626197.4ED6092F@albourne.com> <386A0806.33DD9CE0@albourne.com>
<386A1457.432DCE8E@albourne.com>
<386A1DE5.C56C01A6@albourne.com> <4044.946483296@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

pg_proc_prosrc_index is the problem, eh? I'll bet a nickel that you're
seeing still another manifestation of btree's problems with oversized
index entries. (See recent thread 'Error "vacuum pg_proc"'.)

Check to see if you have any functions whose definitions exceed 2700
bytes, eg with
select proname from pg_proc where length(prosrc) > 2700;
If so, you need to rewrite them to be smaller, perhaps by breaking
them into multiple functions.

Yep, I've got two of those. I saw the message about lengths in indexes,
but howcome this is relevant for procedures? I thought it would only be an
index on name and a pointer into pg_proc? Just asking because I want to
understand how this works.

I'll rewrite them and see whether that fixes it. Thanks a lot for the
help!

Adriaan

From bouncefilter Wed Dec 29 16:21:12 1999
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 QAA70565
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 16:20:53 -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
QAA11403;
Wed, 29 Dec 1999 16:20:38 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912292120.QAA11403@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: subquery performance and EXISTS
In-Reply-To: <4091.946483646@sss.pgh.pa.us> from Tom Lane at "Dec 29,
1999 11:07:26 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 29 Dec 1999 16:20:38 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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

Tom, does it still look easy to change, and if so, can you change it to
hash?

We could probably do it, but there is the little problem that hash joins
only work for a very limited set of data types and operators. This
wouldn't be a complete solution on its own.

How hard would it be to have the EXISTS be done automatically? The
performance problem for subqueries has bothered me for two years now. I
don't think I know enough to fix it, though.

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

From bouncefilter Wed Dec 29 17:00:13 1999
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 RAA76797
for <pgsql-hackers@postgresql.org>;
Wed, 29 Dec 1999 17:00:12 -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 QAA04948;
Wed, 29 Dec 1999 16:59:04 -0500 (EST)
To: Adriaan Joubert <a.joubert@albourne.com>
cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Index corruption
In-reply-to: <386A7658.13933C0A@albourne.com>
References: <38626197.4ED6092F@albourne.com> <386A0806.33DD9CE0@albourne.com>
<386A1457.432DCE8E@albourne.com>
<386A1DE5.C56C01A6@albourne.com> <4044.946483296@sss.pgh.pa.us>
<386A7658.13933C0A@albourne.com>
Comments: In-reply-to Adriaan Joubert <a.joubert@albourne.com>
message dated "Wed, 29 Dec 1999 23:00:08 +0200"
Date: Wed, 29 Dec 1999 16:59:04 -0500
Message-ID: <4945.946504744@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Adriaan Joubert <a.joubert@albourne.com> writes:

Check to see if you have any functions whose definitions exceed 2700
bytes, eg with
select proname from pg_proc where length(prosrc) > 2700;
If so, you need to rewrite them to be smaller, perhaps by breaking
them into multiple functions.

Yep, I've got two of those.

Bingo ...

I saw the message about lengths in indexes,
but howcome this is relevant for procedures?

In 6.5 (and before), there's an index on the prosrc field of pg_proc,
ie, the definition of the procedure. There's not any real good reason
to have such an index, so we've removed it for 7.0 ... but in 6.5 it's
there and it creates problems if you have long procedure definitions :-(

regards, tom lane

From bouncefilter Wed Dec 29 17:27:13 1999
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 RAA83334
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 17:26:33 -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 RAA05068;
Wed, 29 Dec 1999 17:25:47 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Re: subquery performance and EXISTS
In-reply-to: <199912292120.QAA11403@candle.pha.pa.us>
References: <199912292120.QAA11403@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Wed, 29 Dec 1999 16:20:38 -0500"
Date: Wed, 29 Dec 1999 17:25:47 -0500
Message-ID: <5065.946506347@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

How hard would it be to have the EXISTS be done automatically? The
performance problem for subqueries has bothered me for two years now.

I think the ideal solution would involve rewriting the subquery into
a single-level query. I haven't looked at how hard this would be...

regards, tom lane

From bouncefilter Wed Dec 29 17:36:13 1999
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 RAA87527
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 17:35:14 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m123RXZ-0003kGC; Wed, 29 Dec 99 23:25 MET
Message-Id: <m123RXZ-0003kGC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Index corruption
To: a.joubert@albourne.com (Adriaan Joubert)
Date: Wed, 29 Dec 1999 23:25:57 +0100 (MET)
Cc: pgsql-hackers@postgreSQL.org
Reply-To: wieck@debis.com (Jan Wieck)
In-Reply-To: <386A0806.33DD9CE0@albourne.com> from "Adriaan Joubert" at Dec
29,
99 03:09:26 pm
X-Mailer: ELM [version 2.4 PL25]
Content-Type: text

OK, I've got the same problem again. I upgraded to 6.5.3 just in case there
was something different, and
it makes no difference. Thousands of pg_proc_prosrc_index..<id> files and
vacuum just seems to be in and endless loop. I really need to get this
pinned down. Any ideas where I could start looking?

I made the patch below against 6.5.2 (you aren't the first
who had trouble with it). Hopefully it will apply on 6.5.3
too, haven't checked. It removes the prosrc index. Due to
the catalog changes you need to dump (BEFORE), reinstall,
initdb and reload.

Should we eventually put it somewhere on the FTP server and
have a pointer in the FAQ?

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

begin 644 remove_prosrc_idx_6.5.2.diff.gz
M'XL("!^I1S@"`W)E;6]V95]P<F]S<F-?:61X7S8N-2XR+F1I9F8`U5A[;YM6
M%/^;?HK35)K`PPT8OZM.BF+<><N2S,ZV5E5E$;A.4&UP`6_QHGSWG7,N$+#!
M:;6IVZS(IN>>QSWO'_7\Q0*:;@1QY+X,(__F^-IQ/XK`.W:=Q%F&-\=^X(D[
M/[AYZ1+/@>-GC4;C<]0HX\B'D7`!+#!;0\,<6@:8@\'@6;/9?,+&CJS91G$I
MVRA_^#)M2^\8P/\$4$Z2)/*O-XDXWZPFI%%':DZ<BJ7O,?GA%=+=6R=2`*!Q
M[JS$?'TS7T>A.\>+^*Z(WZ.&7=H'>`WWET@0WB82))7:>`Y*3KY(31`U)\XB
M]X#=9+L6NW:+-+9[A82"20`B7!STQUTZ<;RKN$1DS:=$*:BF%&%8VP/`I^9_
M+*S_BPA6%:K5,W1KT,M*E<+J>R)V49YK?QZN11"I^W'0Z&9*LEDO!?*>RG[A
MD[%(W-LK.E!OA;/&/#B)'P8ZL&(=XH]BJX.%"IJ`?XJTXR[#6*C,DI]$(ME$
M`;`1HCU(.OY]CXK9!#[OE?/,=0(U,POE.R3B+H$&Y1FY-92^)T/9N?2=3"FD
MY$>Q':%C?./WY@>FYY:5_%H%=CM(HNTD\!/?6?I_"O4;%C4^Z,3#'\RC>NTG
ML=G5P+@SR@=4O)C.:Q%I8):/IN(F=U2#\?S*?GME_USBN0S](!'1&Y'@K3<K
M-?/R,=*',IM%C[G_1F)-K@PNI(K$X@DULM4=Z%:O+5O9>W(3I*U9LPCRT\-[
M(&?[\C50)VH:0V-0OP5,R]1-JRN;BW1+0B^?8`HTCOG77X#Z/$XB=[7FM.6]
MIL,;^]R>3DYG]M7YR4^VIC'__;-OX86_\,0"\&`V'T_>VB,^45`<7>`Z?\WE
M[@>JI,F652BW>#033N3>SK;QJ>/>"IG6R^G%;'JJ2[:LKBK**C6@Z35N]W6S
M;17='B"AX+:R7D1"%#6E5Z,XY!TVB7_%+O)4O&_JME(8"<WODKF'^:'?T/=>
M443$,A;XBWP"TZ;:T^G%5(>C91A^1)\7801Y<.%Z"W&XB5P!@1!>#`O_#M0?
MG$`[TJ0NG*0+MOI`W_1UH%(WB;^,L5XPE,?Q-N:'G7*M9JFNV6K>BL*UJ@OW
M\^2[PY957[V6U=&MCIFC&"5<+&*1A`MU'$8K&HRT=B+Q1^0G6*?B]_FGC;/4
M9/6<_W)VQD\J3<;Q)G`U(-H#3ZS'O9H-$:YU13EN@*Q!:@P:;>D4O)=S[B0H
M[&E9/>D$-'9_^4=:J[XXZ=`A[0S)MS<+F5SPH'+5/+"?]Q=K$3E)&/'6+;D%
MZ-7IV<G('E^]N[2SEC?UM)-Y'E*LVT;=//0#=[GQQ#XLO>6LUQ^7JZN>KZ*R
MNN7*^GS9WM`Z@(Q;IM[*X08T8!:N!.`<\VEIAD',79JB&,#UC9ER,DC'$A2]
M%RP@((4^Q)`A'T6Q$*'M,!1Q'3'L:2BB,D5I[3.4T%45`]T!*3F+R9&3WJ9S
M[]_QMO5UO*V<('V]W<YRG6FH`.AP5/2;G^ALSC5V5)#=P]W`HNQP>%`@QZV/
M`O@5$*`.G.@Q)"3>W!?/^AU*XM@7^R:+\)U0V5$6\,H+EM!_B1V_^'I[(CQ@
M=BW(C!5,\%3!^%NR^JK2TV_I_7Z6'ES"`A=KOGSW8WT(5B,/K]:)QYN\7EGN
MZP%M&?P`^>HCX;J<I(A$6D`)BZ7-!CY2"&-&K?5FO_S5H-*-8FZ?"@<EL2X<
MI:P?4B3]3U+V:F7EU[RGM6%OI<JH1OJFWN_6UXAIM'33Z!?>#T<V[K*I/9^<
MC^RWZE[WI5.,B+")<4'`=4)8#QF`F,)UK+$?U7IJFK).:\H.+(.J>:%S?7"I
MY"2DD+*8KM#/[M"LOT/>V0<,(P_7S*-+55Z56S]51\0OC,[^3*C3E3+E,='2
M3)N&@:GL/(4Q)'3,06,98>P>5N.+7:X*=-&O1A=/27:&^/94BRTZ`[W;V]TW
M;Z87].*$\Z1EE<FS=[/)",GM`GEJ_S:=7-E2HM4I+%>)2TE-MT!]Q'5XT"L<
MG)V<7Y!VI=67E8$@L+GSX0VO*/C>MW$38)?I-7F([T$!`H.5[&%Z/1$>(P4'
IF99+2$*@_V/(WMU4C>-(WG<SK/%/>U_RLUOI9^\K^/D76W26Z346````
`
end

From bouncefilter Wed Dec 29 17:57:13 1999
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 RAA90858
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 17:56:43 -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 RAA13960
for pgsql-hackers@postgreSQL.org; Wed, 29 Dec 1999 17:36:39 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912292236.RAA13960@candle.pha.pa.us>
Subject: correlated subquery
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: Wed, 29 Dec 1999 17:36:39 -0500 (EST)
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Is this a good example of a required correlated subquery:

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1
WHERE age = (
SELECT MAX(age)
FROM friends f2
WHERE f1.state = f2.state
)
ORDER BY firstname, lastname

It finds the oldest person in each state. HAVING can't do that, right?

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

From bouncefilter Wed Dec 29 18:03:14 1999
Received: from cap-ferrat.albourne.com (cap-ferrat.albourne.com
[195.89.178.227]) by hub.org (8.9.3/8.9.3) with ESMTP id SAA94532
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 18:03:01 -0500 (EST)
(envelope-from a.joubert@albourne.com)
Received: from albourne.com (bishop-rock.albourne.com [195.89.178.230])
by cap-ferrat.albourne.com (8.9.3/8.9.3/Albourne/UKS/2.9/MAPS) with
ESMTP id XAA29444; Wed, 29 Dec 1999 23:02:58 GMT
Sender: a.joubert@albourne.com
Message-ID: <386A94D6.95E129C7@albourne.com>
Date: Thu, 30 Dec 1999 01:10:14 +0200
From: Adriaan Joubert <a.joubert@albourne.com>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.0.38 i586)
X-Accept-Language: en
MIME-Version: 1.0
To: Jan Wieck <wieck@debis.com>
CC: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Index corruption
References: <m123RXZ-0003kGC@orion.SAPserv.Hamburg.dsh.de>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I made the patch below against 6.5.2 (you aren't the first
who had trouble with it). Hopefully it will apply on 6.5.3
too, haven't checked. It removes the prosrc index. Due to
the catalog changes you need to dump (BEFORE), reinstall,
initdb and reload.

Great! Thanks a lot, I'll rebuild it all and get it back up. A real life-saver -- I was
getting quite a bit of flak and Oracle was coming up in conversations ;-(. Now I can at
least show them that Postgres response to problems beats anything other db's can offer ;-)

Should we eventually put it somewhere on the FTP server and
have a pointer in the FAQ?

Yep, I think so. It is not obvious that this should be a problem (I certainly didn't
expect an index on the procedure source) and it causes severe problems.

Thanks a lot, Jan!

Adriaan

From bouncefilter Wed Dec 29 18:25:14 1999
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 SAA97167
for <pgsql-hackers@postgresql.org>;
Wed, 29 Dec 1999 18:24:55 -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 SAA05332;
Wed, 29 Dec 1999 18:18:08 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] correlated subquery
In-reply-to: <199912292236.RAA13960@candle.pha.pa.us>
References: <199912292236.RAA13960@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Wed, 29 Dec 1999 17:36:39 -0500"
Date: Wed, 29 Dec 1999 18:18:07 -0500
Message-ID: <5329.946509487@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

Is this a good example of a required correlated subquery:
SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1
WHERE age = (
SELECT MAX(age)
FROM friends f2
WHERE f1.state = f2.state
)
ORDER BY firstname, lastname

It finds the oldest person in each state. HAVING can't do that, right?

Yes, I think you are right. You could find the oldest age in each state
with a HAVING:

SELECT state, MAX(age) FROM friends GROUP BY state;

but I don't see any way to get the other attributes of the record(s)
matching that age, except by using a subselect.

regards, tom lane

From bouncefilter Wed Dec 29 18:37:14 1999
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 SAA01698
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 18:36:28 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m123SUu-0003kGC; Thu, 30 Dec 99 00:27 MET
Message-Id: <m123SUu-0003kGC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Index corruption
To: tgl@sss.pgh.pa.us (Tom Lane)
Date: Thu, 30 Dec 1999 00:27:16 +0100 (MET)
Cc: a.joubert@albourne.com, pgsql-hackers@postgreSQL.org
Reply-To: wieck@debis.com (Jan Wieck)
In-Reply-To: <4044.946483296@sss.pgh.pa.us> from "Tom Lane" at Dec 29,
99 11:01:36 am
X-Mailer: ELM [version 2.4 PL25]
Content-Type: text

Tom Lane wrote:

pg_proc_prosrc_index is the problem, eh? I'll bet a nickel that you're
seeing still another manifestation of btree's problems with oversized
index entries. (See recent thread 'Error "vacuum pg_proc"'.)

[...]

7.0 should fix this problem, but it's a real hazard in 6.5.

I already posted a patch that removes pg_proc_prosrc_index
from 6.5.2. This one is definitely not fixable by anything
else (except changing all functions to <2700).

Anyway, I still think that a new implementation of reindexdb
would be good. Some of the system indices can cause big, big
trouble, if they get corrupted. If you would ever be faced
with a corrupted pg_class_... index, you won't be able to
dump any more, because the backend will fail to startup at
all.

I analyzed the problem of recreating system catalog indices
some weeks ago, and ISTM that during bootstrap operation, ALL
tuples are visible.

I hacked in a "drop index" for the bootstrap parser, and on a
freshly created DB some hand-made BKI script ran smooth and
recreated all the indices well. But it failed on the
regression DB, bacause it bombed out with duplicate errors.
First I was a little puzzled about it, because I allways
thought that only vacuum removes index tuples. So it could
only be the main tuples visibility that prevents from dup
errors between vacuum times.

Thus, IMHO there should be another command added to the
bootstrap parser. This would recreate ALL existing indices
(system and user ones), but tell the visibility code somehow
to ignore deleted tuples. I don't have the time to do it now,
so at least I'd like to have a TODO item for 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 Wed Dec 29 19:02:18 1999
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 TAA05352
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 19:01:33 -0500 (EST) (envelope-from wieck@debis.com)
Received: by orion.SAPserv.Hamburg.dsh.de for pgsql-hackers@postgreSQL.org
id m123StC-0003kGC; Thu, 30 Dec 99 00:52 MET
Message-Id: <m123StC-0003kGC@orion.SAPserv.Hamburg.dsh.de>
From: wieck@debis.com (Jan Wieck)
Subject: Re: [HACKERS] Index corruption
To: tgl@sss.pgh.pa.us (Tom Lane)
Date: Thu, 30 Dec 1999 00:52:22 +0100 (MET)
Cc: a.joubert@albourne.com, pgsql-hackers@postgreSQL.org
Reply-To: wieck@debis.com (Jan Wieck)
In-Reply-To: <4945.946504744@sss.pgh.pa.us> from "Tom Lane" at Dec 29,
99 04:59:04 pm
X-Mailer: ELM [version 2.4 PL25]
Content-Type: text

Tom Lane wrote:

Adriaan Joubert <a.joubert@albourne.com> writes:

I saw the message about lengths in indexes,
but howcome this is relevant for procedures?

In 6.5 (and before), there's an index on the prosrc field of pg_proc,
ie, the definition of the procedure. There's not any real good reason
to have such an index, so we've removed it for 7.0 ... but in 6.5 it's
there and it creates problems if you have long procedure definitions :-(

The usage of it is only #ifdef'd out!

It's a very old standing FEATURE, that doesn't work anyhow.
It has to do with tuple set's, and as far as I read the code
in question, the (no longer supported either) nested dot
notation looked for a 'sql' language function returning a set
of tuples and created that on the fly. Therefore, it checked
by the required functions source text if it exists.

IIRC the #ifdef is somewhat like SETS_FIXED.

Jan

--

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

From bouncefilter Wed Dec 29 19:02:15 1999
Received: from homeworld.bigpanda.org (client-151-198-27-104.bellatlantic.net
[151.198.27.104]) by hub.org (8.9.3/8.9.3) with ESMTP id TAA06328
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 19:02:11 -0500 (EST)
(envelope-from acroyear@homeworld.bigpanda.org)
Received: from homeworld.bigpanda.org (IDENT:acroyear@localhost.awc.net
[127.0.0.1])
by homeworld.bigpanda.org (8.9.3/8.9.3) with ESMTP id SAA01272;
Wed, 29 Dec 1999 18:59:18 -0500
Message-Id: <199912292359.SAA01272@homeworld.bigpanda.org>
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: pgsql-hackers@postgreSQL.org
From: sszabo@bigpanda.com
Subject: Re: [HACKERS] correlated subquery
In-Reply-To: Your message of "Wed, 29 Dec 1999 17:36:39 EST."
<199912292236.RAA13960@candle.pha.pa.us>
Date: Wed, 29 Dec 1999 18:59:18 -0500
Sender: acroyear@bigpanda.com

Is this a good example of a required correlated subquery:

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1
WHERE age = (
SELECT MAX(age)
FROM friends f2
WHERE f1.state = f2.state
)
ORDER BY firstname, lastname

It finds the oldest person in each state. HAVING can't do that, right?

I'm assuming that this is for the book... If so, you might want to also
note that this query can return more people than there are states if
multiple people in the same state have the maximum age for that state.

I'm not sure how deeply you are going into this, but getting only one
person per state looks like it might be fairly painful... You might be
able cheat if there was only one field besides age and state in the output
using group by and an aggregate.

Stephan Szabo

From bouncefilter Wed Dec 29 19:53:15 1999
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 TAA14156
for <pgsql-hackers@postgreSQL.org>;
Wed, 29 Dec 1999 19:52: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
TAA16571;
Wed, 29 Dec 1999 19:44:07 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912300044.TAA16571@candle.pha.pa.us>
Subject: Re: [HACKERS] correlated subquery
In-Reply-To: <199912292359.SAA01272@homeworld.bigpanda.org> from
"sszabo@bigpanda.com" at "Dec 29, 1999 06:59:18 pm"
To: sszabo@bigpanda.com
Date: Wed, 29 Dec 1999 19:44:07 -0500 (EST)
CC: pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Is this a good example of a required correlated subquery:

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1
WHERE age = (
SELECT MAX(age)
FROM friends f2
WHERE f1.state = f2.state
)
ORDER BY firstname, lastname

It finds the oldest person in each state. HAVING can't do that, right?

I'm assuming that this is for the book... If so, you might want to also
note that this query can return more people than there are states if
multiple people in the same state have the maximum age for that state.

I'm not sure how deeply you are going into this, but getting only one
person per state looks like it might be fairly painful... You might be
able cheat if there was only one field besides age and state in the output
using group by and an aggregate.

Yikes, that would be painful. Good point. Fortunately, the data has
only one max person per state.

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

From bouncefilter Thu Dec 30 01:31:19 1999
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 BAA86109
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 01:30:49 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id BAA27180
for pgsql-hackers@postgreSQL.org; Thu, 30 Dec 1999 01:04:03 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912300604.BAA27180@candle.pha.pa.us>
Subject: Source code format vote
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: Thu, 30 Dec 1999 01:04:03 -0500 (EST)
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Current votes are:

(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter M.,
Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

Unless someone else speaks up, I will consider this vote closed. I will
modify pgindent to reflect this format the next time it is run.

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

From bouncefilter Thu Dec 30 02:50:20 1999
Received: from flame.flame.co.za (flame.flame.co.za [160.124.170.1])
by hub.org (8.9.3/8.9.3) with ESMTP id CAA03381
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 02:50:17 -0500 (EST)
(envelope-from theo@flame.co.za)
Received: from flame.co.za (flame.flame.co.za [160.124.170.1])
by flame.flame.co.za (8.8.7/8.8.7) with ESMTP id JAA02479
for <pgsql-hackers@postgreSQL.org>; Thu, 30 Dec 1999 09:47:19 +0200
Sender: theo@flame.flame.co.za
Message-ID: <386B0E07.D881C34B@flame.co.za>
Date: Thu, 30 Dec 1999 09:47:19 +0200
From: Theo Kramer <theo@flame.co.za>
Organization: Flame Computing Enterprises cc
X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.0.35 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Source code format vote
References: <199912300604.BAA27180@candle.pha.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Bruce Momjian wrote:

Current votes are:

(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter M.,
Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

Unless someone else speaks up, I will consider this vote closed. I will
modify pgindent to reflect this format the next time it is run.

Don't know if I qualify to vote - but if I do then I vote for 4-space tabs
(seeing that I often read the code - now if only two-space tabs
were an option...)
--------
Regards
Theo

From bouncefilter Thu Dec 30 03:15:20 1999
Received: from tango.SoftHome.net (tango.SoftHome.net [204.144.231.49])
by hub.org (8.9.3/8.9.3) with SMTP id DAA12187
for <pgsql-hackers@postgresql.org>;
Thu, 30 Dec 1999 03:14:43 -0500 (EST)
(envelope-from j.roeleveld@softhome.net)
Received: (qmail 15987 invoked by uid 417); 30 Dec 1999 08:14:42 -0000
Received: from sentec.demon.nl (HELO joost) (212.238.106.25)
by smtpa.softhome.net with SMTP; 30 Dec 1999 08:14:42 -0000
Message-ID: <008801bf529d$d54d0180$8402a8c0@sentec.demon.nl>
From: "J. Roeleveld" <j.roeleveld@softhome.net>
To: "Theo Kramer" <theo@flame.co.za>,
"PostgreSQL-development" <pgsql-hackers@postgresql.org>
References: <199912300604.BAA27180@candle.pha.pa.us>
<386B0E07.D881C34B@flame.co.za>
Subject: Re: [HACKERS] Source code format vote
Date: Thu, 30 Dec 1999 09:14:00 +0100
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2615.200
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2615.200

Current votes are:

(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter

M.,

Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

Unless someone else speaks up, I will consider this vote closed. I will
modify pgindent to reflect this format the next time it is run.

Don't know if I qualify to vote - but if I do then I vote for 4-space tabs
(seeing that I often read the code - now if only two-space tabs
were an option...)

Same here, I prefer 4-space tabs as well, looks much better on standard
screens.
Also I personally prefer the open bracket not to be placed in the if() line.

But I'm not sure if I qualify to vote either, although I do read the code.

Joost Roeleveld

From bouncefilter Thu Dec 30 04:22:21 1999
Received: from s-nath-exch1.nath-gpbry.co.za (mail.newaftech.com
[209.212.104.161]) by hub.org (8.9.3/8.9.3) with ESMTP id EAA23027
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 04:21:59 -0500 (EST)
(envelope-from Michael.Ansley@intec.co.za)
Received: by S-NATH-EXCH1 with Internet Mail Service (5.5.2448.0)
id <ZW4G6W4Z>; Thu, 30 Dec 1999 11:19:07 +0200
Message-ID: <1BF7C7482189D211B03F00805F8527F748C3B3@S-NATH-EXCH2>
From: "Ansley, Michael" <Michael.Ansley@intec.co.za>
To: "'Theo Kramer'" <theo@flame.co.za>, PostgreSQL-development
<pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] Source code format vote
Date: Thu, 30 Dec 1999 11:07:45 +0200
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

Word is...

Tabs are 8 spaces; not 4, not 2: if your function runs off the end of the
page (screen), sh*t function...

;-)

-----Original Message-----
From: Theo Kramer [mailto:theo@flame.co.za]
Sent: Thursday, December 30, 1999 9:47 AM
To: PostgreSQL-development
Subject: Re: [HACKERS] Source code format vote

Bruce Momjian wrote:

Current votes are:

(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan,

Hiroshi, Peter M.,

Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M.,

Marc, Tatsuo

(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code

format for 7.0.

Unless someone else speaks up, I will consider this vote

closed. I will

modify pgindent to reflect this format the next time it is run.

Don't know if I qualify to vote - but if I do then I vote
for 4-space tabs
(seeing that I often read the code - now if only two-space tabs
were an option...)
--------
Regards
Theo

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

From bouncefilter Thu Dec 30 05:10:21 1999
Received: from cap-ferrat.albourne.com (cap-ferrat.albourne.com
[195.89.178.227]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA34016
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 05:10:04 -0500 (EST)
(envelope-from a.joubert@albourne.com)
Received: from albourne.com (bishop-rock.albourne.com [195.89.178.230])
by cap-ferrat.albourne.com (8.9.3/8.9.3/Albourne/UKS/2.9/MAPS) with
ESMTP id KAA30662
for <pgsql-hackers@postgreSQL.org>; Thu, 30 Dec 1999 10:10:03 GMT
Sender: a.joubert@albourne.com
Message-ID: <386B3133.C3231AFA@albourne.com>
Date: Thu, 30 Dec 1999 12:17:24 +0200
From: Adriaan Joubert <a.joubert@albourne.com>
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.0.38 i586)
X-Accept-Language: en
MIME-Version: 1.0
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Source code format vote
References: <1BF7C7482189D211B03F00805F8527F748C3B3@S-NATH-EXCH2>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

"Ansley, Michael" wrote:

Word is...

Tabs are 8 spaces; not 4, not 2: if your function runs off the end of the
page (screen), sh*t function...

Yep, I agree. No point in trying to change the standard size of a tab (i.e.
8 spaces). How you indent your C-code is a different matter: it is possible
to indent C-code with spaces instead of tabs ;-). But don't go and change
standards.

I prefer indentation that is not too large so that you can see a whole
function, but couldn't care less whether it is 2 or 4 spaces.

And no, I definitely don't qualify to vote, but had to butt in anyway....

From bouncefilter Thu Dec 30 08:31:23 1999
Received: from sandman.acadiau.ca (dcurrie@sandman.acadiau.ca
[131.162.129.111]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA85234
for <pgsql-hackers@postgresql.org>;
Thu, 30 Dec 1999 08:31:10 -0500 (EST)
(envelope-from dcurrie@sandman.acadiau.ca)
Received: (from dcurrie@localhost)
by sandman.acadiau.ca (8.8.8/8.8.8/Debian/GNU) id GAA18698;
Thu, 30 Dec 1999 06:30:58 -0400
From: Duane Currie <dcurrie@sandman.acadiau.ca>
Message-Id: <199912301030.GAA18698@sandman.acadiau.ca>
Subject: Re: [HACKERS] database replication
In-Reply-To: <OFD38C9424.B391F434-ON85256851.0054F41A@black-oak.COM> from
"DWalker@black-oak.com" at "Dec 24, 99 10:27:59 am"
To: DWalker@black-oak.com
Date: Thu, 30 Dec 1999 10:30:58 +0000 (AST)
Cc: pgsql-hackers@postgresql.org
X-Mailer: ELM [version 2.4ME+ PL39 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Hi Guys,

Now for one of my REALLY rare posts.
Having done a little bit of distributed data systems, I figured I'd
pitch in a couple cents worth.

2) The replication system will need to add at least one field to each
table in each database that needs to be re plicated. &nbsp;This
field will be a date/time stamp which identifies the &quot; last
update&quot; of the record. &nbsp;This field will be called PGR_TIME
for la ck of a better name. &nbsp;Because this field will be used
from within programs and triggers it can be longer so as to not
mistake it for a user field.

I just started reading this thread, but I figured I'd throw in a couple
suggestions for distributed data control (a few idioms I've had to
deal with b4):
- Never use time (not reliable from system to system). Use
a version number of some sort that can stay consistent across
all replicas

This way, if a system's time is or goes out of wack, it doesn't
cause your database to disintegrate, and it's easier to track
conflicts (see below. If using time, the algorithm gets
nightmarish)

- On an insert, set to version 1

- On an update, version++

- On a delete, mark deleted, and add a delete stub somewhere for the
replicator process to deal with in sync'ing the databases.

- If two records have the same version but different data, there's
a conflict. A few choices:
1. Pick one as the correct one (yuck!! invisible data loss)
2. Store both copies, pick one as current, and alert
database owner of the conflict, so they can deal with
it "manually."
3. If possible, some conflicts can be merged. If a disjoint
set of fields were changed in each instance, these changes
may both be applied and the record merged. (Problem:
takes a lot more space. Requires a version number for
every field, or persistent storage of some old records.
However, this might help the "which fields changed" issue
you were talking about in #6)

- A unique id across all systems should exist (or something that
effectively simulates a unique id. Maybe a composition of the
originating oid (from the insert) and the originating database
(oid of the database's record?) might do it. Store this as
an extra field in every record.

(Two extra fieldss so far: 'unique id' and 'version')

I do like your approach: triggers and a separate process. (Maintainable!! :)

Anyway, just figured I'd throw in a few suggestions,
Duane

From bouncefilter Thu Dec 30 06:20:22 1999
Received: from flame.flame.co.za (flame.flame.co.za [160.124.170.1])
by hub.org (8.9.3/8.9.3) with ESMTP id GAA44794
for <pgsql-hackers@postgresql.org>;
Thu, 30 Dec 1999 06:19:29 -0500 (EST)
(envelope-from theo@flame.flame.co.za)
Received: (from theo@localhost) by flame.flame.co.za (8.8.7/8.8.7) id
NAA02938;
Thu, 30 Dec 1999 13:16:33 +0200
From: Theo Kramer <theo@flame.flame.co.za>
Message-Id: <199912301116.NAA02938@flame.flame.co.za>
Subject: Re: [HACKERS] Source code format vote
To: Michael.Ansley@intec.co.za (Ansley, Michael)
Date: Thu, 30 Dec 1999 13:16:33 +0200 (SAST)
Cc: pgsql-hackers@postgresql.org
In-Reply-To: <1BF7C7482189D211B03F00805F8527F748C3B3@S-NATH-EXCH2> from
"Ansley, Michael" at Dec 30, 99 11:07:45 am
Content-Type: text

Word is...

Tabs are 8 spaces; not 4, not 2: if your function runs off the end of the
page (screen), sh*t function...

;-)

Tabs are a relic of the typewriters of yesteryear. They, imo, are outdated,
non-standard from computer to printer to display, and a cause for mindless
debate ;).

Anyway, a call for a vote presumes a democratic process with no coercion...?

Regards
Theo

From bouncefilter Thu Dec 30 08:02:23 1999
Received: from onestone.elsinore.klever.net (root@onestone.elsinore.klever.net
[207.175.129.2]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA78920
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 08:02:20 -0500 (EST)
(envelope-from sbirch@ironmountainsystems.com)
Received: from ironmountainsystems.com (ppp78.kross.klever.net
[209.203.65.78]) by onestone.elsinore.klever.net (8.8.7/8.8.0)
with ESMTP id FAA14697; Thu, 30 Dec 1999 05:58:05 -0800
Sender: sbirch@onestone.elsinore.klever.net
Message-ID: <386B578A.6D87D214@ironmountainsystems.com>
Date: Thu, 30 Dec 1999 05:00:58 -0800
From: Stephen Birch <sbirch@ironmountainsystems.com>
Organization: Iron Mountain Systems, Inc.
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.5 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Theo Kramer <theo@flame.co.za>
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Source code format vote
References: <199912300604.BAA27180@candle.pha.pa.us>
<386B0E07.D881C34B@flame.co.za>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I would also vote for 4 space tabs.

But would prefer 2 space tabs.

Steve

Theo Kramer wrote:

Bruce Momjian wrote:

Current votes are:

(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter M.,
Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

Unless someone else speaks up, I will consider this vote closed. I will
modify pgindent to reflect this format the next time it is run.

Don't know if I qualify to vote - but if I do then I vote for 4-space tabs
(seeing that I often read the code - now if only two-space tabs
were an option...)
--------
Regards
Theo

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

From bouncefilter Thu Dec 30 08:18:24 1999
Received: from onestone.elsinore.klever.net (root@onestone.elsinore.klever.net
[207.175.129.2]) by hub.org (8.9.3/8.9.3) with ESMTP id IAA83438
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 08:17:31 -0500 (EST)
(envelope-from sbirch@ironmountainsystems.com)
Received: from ironmountainsystems.com (ppp78.kross.klever.net
[209.203.65.78]) by onestone.elsinore.klever.net (8.8.7/8.8.0)
with ESMTP id GAA15258; Thu, 30 Dec 1999 06:07:39 -0800
Sender: sbirch@onestone.elsinore.klever.net
Message-ID: <386B59C8.9509F92D@ironmountainsystems.com>
Date: Thu, 30 Dec 1999 05:10:32 -0800
From: Stephen Birch <sbirch@ironmountainsystems.com>
Organization: Iron Mountain Systems, Inc.
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.5 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Bruce Momjian <pgman@candle.pha.pa.us>
CC: Tom Lane <tgl@sss.pgh.pa.us>, The Hermit Hacker <scrappy@hub.org>,
Vadim Mikheev <vadim@krs.ru>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] 6.6 release
References: <199912122254.RAA09994@candle.pha.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Is there a writeup of the version numbering and CVS branch scheme in any of the
FAQs?

If not, there should be.

Steve

Bruce Momjian wrote:

The Hermit Hacker <scrappy@hub.org> writes:

It is agreed that Feb 1st is the beta date...it will not include WAL, but
will be numbered v7.0, with v7.1 going BETA as soon as Vadim feels
prepared with the WAL code...

OK, it's decided. Let's quit arguing.

On Feb 1st, the CVS repository will be branched, like we did on the last
release, so that we can beta/debug 7.0 *without* interfering with
development on 7.1. This has proven to work quite well with v6.5.x,

Actually, I thought what worked well was to postpone the branch as long
as possible. Double-patching is no fun...

Ditto. Look at the 6_5 branch and you will see it was done far into the
6.5 release, not at the 6.5.0 release. I don't want to continue
mentioning this for every release.

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

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

From bouncefilter Thu Dec 30 08:44:24 1999
Received: from gandalf.telecom.at (gandalf.telecom.at [194.118.26.84])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA88924
for <hackers@postgresql.org>; Thu, 30 Dec 1999 08:43:55 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at
(sdexcgtw01.f000.d0188.sd.spardat.at [172.18.1.16])
by gandalf.telecom.at (xxx/xxx) with ESMTP id OAA74628;
Thu, 30 Dec 1999 14:42:23 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <Z7Z55XLY>; Thu, 30 Dec 1999 14:42:24 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C603FDC1EA@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'Bruce Momjian'" <pgman@candle.pha.pa.us>
Cc: "'hackers@postgresql.org'" <hackers@postgresql.org>
Subject: AW: [HACKERS] correlated subquery
Date: Thu, 30 Dec 1999 14:42:21 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1
WHERE age = (
SELECT MAX(age)
FROM friends f2
WHERE f1.state = f2.state
)
ORDER BY firstname, lastname

It finds the oldest person in each state. HAVING can't do
that, right?

Having can do that particular case: (e.g. Informix)

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1, friends f2
WHERE f1.state = f2.state
GROUP BY f2.state, f1.firstname, f1.lastname, f1.age, f1.state
HAVING f1.age = max(f2.age)
ORDER BY firstname, lastname;

Andreas

From bouncefilter Thu Dec 30 10:07:25 1999
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 KAA08303
for <hackers@postgreSQL.org>; Thu, 30 Dec 1999 10:06:26 -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 KAA13369;
Thu, 30 Dec 1999 10:05:31 -0500 (EST)
To: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
cc: "'Bruce Momjian'" <pgman@candle.pha.pa.us>,
"'hackers@postgresql.org'" <hackers@postgreSQL.org>
Subject: Re: AW: [HACKERS] correlated subquery
In-reply-to:
<219F68D65015D011A8E000006F8590C603FDC1EA@sdexcsrv1.f000.d0188.sd.spardat.at>
References:
<219F68D65015D011A8E000006F8590C603FDC1EA@sdexcsrv1.f000.d0188.sd.spardat.at>
Comments: In-reply-to Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
message dated "Thu, 30 Dec 1999 14:42:21 +0100"
Date: Thu, 30 Dec 1999 10:05:30 -0500
Message-ID: <13366.946566330@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

It finds the oldest person in each state. HAVING can't do
that, right?

Having can do that particular case: (e.g. Informix)

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1, friends f2
WHERE f1.state = f2.state
GROUP BY f2.state, f1.firstname, f1.lastname, f1.age, f1.state
HAVING f1.age = max(f2.age)
ORDER BY firstname, lastname;

Hmm, yes, and you don't even need the GROUP BY state clauses.

But it's not really the same thing. In particular, if you had two friends
with the same name and age, this would produce only one output record
for both, not two output records as Bruce's original query does.

That's neither likely nor a big problem in the hypothetical application,
but other applications needing this type of query might be more unhappy
about confusing similar records...

regards, tom lane

From bouncefilter Thu Dec 30 10:12:25 1999
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 KAA08765
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 10:11:42 -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 KAA13400;
Thu, 30 Dec 1999 10:11:00 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] Source code format vote
In-reply-to: <199912300604.BAA27180@candle.pha.pa.us>
References: <199912300604.BAA27180@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Thu, 30 Dec 1999 01:04:03 -0500"
Date: Thu, 30 Dec 1999 10:11:00 -0500
Message-ID: <13397.946566660@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

Current votes are:
(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter M.,
Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

It looks to me like there is consensus to change the tab spacing (which
is NOT the same as the logical indent step, for those newcomers who
don't seem to be aware of the difference).

However, I don't see a consensus to change the bracket layout.
7-to-6 is not enough of a margin to justify a wholesale code change.
Especially considering that my original vote was NO, which Bruce chose
to interpret as YES ;-). Change it back to NO, and the vote is the
other way.

I think what we have is a consensus to change the physical tab spacing
but leave the look of the code alone...

regards, tom lane

From bouncefilter Thu Dec 30 11:44:26 1999
Received: from onestone.elsinore.klever.net (root@onestone.elsinore.klever.net
[207.175.129.2]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA25976
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 11:44:08 -0500 (EST)
(envelope-from sbirch@ironmountainsystems.com)
Received: from ironmountainsystems.com (ppp78.kross.klever.net
[209.203.65.78]) by onestone.elsinore.klever.net (8.8.7/8.8.0)
with ESMTP id JAA11928 for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 09:41:05 -0800
Sender: sbirch@onestone.elsinore.klever.net
Message-ID: <386B8BCC.8142230A@ironmountainsystems.com>
Date: Thu, 30 Dec 1999 08:43:56 -0800
From: Stephen Birch <sbirch@ironmountainsystems.com>
Organization: Iron Mountain Systems, Inc.
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.5 i686)
X-Accept-Language: en
MIME-Version: 1.0
CC: pgsql-hackers@postgreSQL.org
Subject: HEAP_MOVED_IN error during vacuum?
References: <10168.943225229@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

When I vacuum the database (PostgreSQL 6.5.3 on SuSE 6.3 Linux, 2.2 kernel), I get the
following error message:

ERROR: HEAP_MOVED_IN was not expected.
vacuumdb: database vacuum failed on ntis

This error only seems to occur after I have used the trim function to clean up one of
the rows in the msg table of a database called ntis:

ntis=>update msg set description = trim(description);
UPDATE 12069
ntis=>

To try and track down the problem, I wrote a C program (using ecpg) that trimmed the
table one row at a time and vacuumed between each row operation. I was hoping that
this program would reveal a problem with the data in one of my records. Unfortunately
the one row at a time approach did not reveal the problem and each vacuum operated
without error.

Can anyone tell me what a HEAP_MOVED_IN error is - I checked the source but was not
familiar enough to understand it? Any ideas on why trim() may have cause it?

From bouncefilter Thu Dec 30 12:03:27 1999
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 MAA30968
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 12:02:32 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
MAA12820;
Thu, 30 Dec 1999 12:01:29 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912301701.MAA12820@candle.pha.pa.us>
Subject: Re: [HACKERS] Source code format vote
In-Reply-To: <386B0E07.D881C34B@flame.co.za> from Theo Kramer at "Dec 30, 1999
09:47:19 am"
To: Theo Kramer <theo@flame.co.za>
Date: Thu, 30 Dec 1999 12:01:29 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Vote is for changing tab size. Indentation will stay at 4 spaces.

Bruce Momjian wrote:

Current votes are:

(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter M.,
Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

Unless someone else speaks up, I will consider this vote closed. I will
modify pgindent to reflect this format the next time it is run.

Don't know if I qualify to vote - but if I do then I vote for 4-space tabs
(seeing that I often read the code - now if only two-space tabs
were an option...)
--------
Regards
Theo

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

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

From bouncefilter Thu Dec 30 12:03:34 1999
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 MAA30985
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 12:02:47 -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
MAA12852;
Thu, 30 Dec 1999 12:01:52 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912301701.MAA12852@candle.pha.pa.us>
Subject: Re: [HACKERS] Source code format vote
In-Reply-To: <008801bf529d$d54d0180$8402a8c0@sentec.demon.nl> from "J.
Roeleveld" at "Dec 30, 1999 09:14:00 am"
To: "J. Roeleveld" <j.roeleveld@softhome.net>
Date: Thu, 30 Dec 1999 12:01:52 -0500 (EST)
CC: Theo Kramer <theo@flame.co.za>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Vote is for changing tab size. Indentation will remain at 4 spaces.

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

Current votes are:

(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter

M.,

Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

Unless someone else speaks up, I will consider this vote closed. I will
modify pgindent to reflect this format the next time it is run.

Don't know if I qualify to vote - but if I do then I vote for 4-space tabs
(seeing that I often read the code - now if only two-space tabs
were an option...)

Same here, I prefer 4-space tabs as well, looks much better on standard
screens.
Also I personally prefer the open bracket not to be placed in the if() line.

But I'm not sure if I qualify to vote either, although I do read the code.

Joost Roeleveld

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

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

From bouncefilter Thu Dec 30 12:05:26 1999
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 MAA31184
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 12:04: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
MAA12910;
Thu, 30 Dec 1999 12:03:26 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912301703.MAA12910@candle.pha.pa.us>
Subject: Re: [HACKERS] 6.6 release
In-Reply-To: <386B59C8.9509F92D@ironmountainsystems.com> from Stephen Birch at
"Dec 30, 1999 05:10:32 am"
To: Stephen Birch <sbirch@ironmountainsystems.com>
Date: Thu, 30 Dec 1999 12:03:26 -0500 (EST)
CC: Tom Lane <tgl@sss.pgh.pa.us>, The Hermit Hacker <scrappy@hub.org>,
Vadim Mikheev <vadim@krs.ru>,
PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Is there a writeup of the version numbering and CVS branch scheme
in any of the FAQs?

There is no item because there isn't a standard yet.

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

From bouncefilter Thu Dec 30 12:31:31 1999
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 MAA56711
for <hackers@postgresql.org>; Thu, 30 Dec 1999 12:31:11 -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
MAA13103;
Thu, 30 Dec 1999 12:07:35 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912301707.MAA13103@candle.pha.pa.us>
Subject: Re: AW: [HACKERS] correlated subquery
In-Reply-To:
<219F68D65015D011A8E000006F8590C603FDC1EA@sdexcsrv1.f000.d0188.sd.spardat.at>
from Zeugswetter Andreas SB at "Dec 30, 1999 02:42:21 pm"
To: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
Date: Thu, 30 Dec 1999 12:07:35 -0500 (EST)
CC: "'hackers@postgresql.org'" <hackers@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1
WHERE age = (
SELECT MAX(age)
FROM friends f2
WHERE f1.state = f2.state
)
ORDER BY firstname, lastname

It finds the oldest person in each state. HAVING can't do
that, right?

Having can do that particular case: (e.g. Informix)

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1, friends f2
WHERE f1.state = f2.state
GROUP BY f2.state, f1.firstname, f1.lastname, f1.age, f1.state
HAVING f1.age = max(f2.age)
ORDER BY firstname, lastname;

Yikes, you are right, and it works on PostgreSQL too. I have added it
to my book. Can anyone suggest queries that _must_ have subqueries?
Seems table aliases can replace subqueries in most/all? cases?

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

From bouncefilter Thu Dec 30 12:31:32 1999
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 MAA56878
for <pgsql-hackers@postgresql.org>;
Thu, 30 Dec 1999 12: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
MAA13119;
Thu, 30 Dec 1999 12:09:24 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912301709.MAA13119@candle.pha.pa.us>
Subject: Re: [HACKERS] Source code format vote
In-Reply-To: <13397.946566660@sss.pgh.pa.us> from Tom Lane at "Dec 30,
1999 10:11:00 am"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 30 Dec 1999 12:09:24 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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

Current votes are:
(8) 8-space tabs: Tom Lane, Peter E., Massimo, Jan, Hiroshi, Peter M.,
Michael, Tatsuo
(5) 4-space tabs: Bruce, Andreas, Vince, Vadim, D'Arcy

open bracket on if () line:
(7) yes: Massimo, Peter, Vince, Tom Lane, Peter M., Marc, Tatsuo
(6) no: Bruce, Vadim, D'Arcy, Jan, Hiroshi, Michael M.

Looks like radical change will come to our source code format for 7.0.

It looks to me like there is consensus to change the tab spacing (which
is NOT the same as the logical indent step, for those newcomers who
don't seem to be aware of the difference).

However, I don't see a consensus to change the bracket layout.
7-to-6 is not enough of a margin to justify a wholesale code change.
Especially considering that my original vote was NO, which Bruce chose
to interpret as YES ;-). Change it back to NO, and the vote is the
other way.

I think what we have is a consensus to change the physical tab spacing
but leave the look of the code alone...

OK, change tab to 8 spaces, keep indent at 4 spaces, and keep open
bracked on a separate line. Got it.

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

From bouncefilter Thu Dec 30 16:08:29 1999
Received: from www.wgcr.org (IDENT:root@www.wgcr.org [206.74.232.194])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA07501;
Thu, 30 Dec 1999 16:08:05 -0500 (EST)
(envelope-from lamar.owen@wgcr.org)
Received: from wgcr.org ([206.74.232.197])
by www.wgcr.org (8.9.3/8.9.3/WGCR) with ESMTP id QAA21096;
Thu, 30 Dec 1999 16:08:12 -0500
Message-ID: <386BC9B4.38C0B5C3@wgcr.org>
Date: Thu, 30 Dec 1999 16:08:04 -0500
From: Lamar Owen <lamar.owen@wgcr.org>
Organization: WGCR Internet Radio
X-Mailer: Mozilla 4.61 [en] (Win95; I)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-ports@postgresql.org, pgsql-hackers@postgresql.org
Subject: RPMS on PostgreSQL.org updated.
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I have updated the RPM distribution on postgresql.org today.

The last two releases, 6.5.3-2 and -3, in both locale and non-locale
versions, are available. I'm keeping two releases rotating online in
case serious problems are found in the latest release that were not in
the next-to-latest release.

The directory structure for the RPMS has been changed somewhat, with the
RPMs now being under /pub/bindist/RPM, rather than being in /pub/RPM and
/pub/SRPMS, although those two locations have been symlinked into the
appropriate directories under /pub/bindist/RPM so that preexisting links
wouldn't be broken.

Many thanks to Marc and Jeff for making the update possible.

Latest release: postgresql-6.5.3-3 and 6.5.3-3nl.

Please read /pub/bindist/RPM/README and README.rpm for more information.

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

From bouncefilter Thu Dec 30 20:50:32 1999
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 UAA62533
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 20:50:27 -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 UAA16232
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 20:50:26 -0500 (EST)
To: pgsql-hackers@postgreSQL.org
Subject: Is backend-libpq's "PQexec/PQfn/portal" code dead?
Date: Thu, 30 Dec 1999 20:50:26 -0500
Message-ID: <16228.946605026@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

As near as I can tell, there are four whole modules of dead code in
src/backend/libpq/: be-dumpdata.c, be-pqexec.c, portal.c, portalbuf.c
(plus some support code in tcop/dest.c). These provide functions called
PQexec and PQfn, which apparently were once backend-side equivalents
to the frontend-libpq functions of the same names, as well as a whole
bunch of other code that may once have been shared with frontend-libpq,
but is so no longer.

The only externally referenced entry point to these modules is an
undocumented SQL function "pqtest", which I strongly suspect no one
is using.

I am strongly inclined to rip all this stuff out, because it's buggy.
I've already found memory-leak problems and dangling-global-pointer
problems, and there are probably more. I'd say this code has been
suffering from software rot for a long time.

Does anyone remember what this code was for, or know a reason to keep it
(and fix it) instead of ripping it out?

regards, tom lane

From bouncefilter Thu Dec 30 21:05:32 1999
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 VAA67108
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 21:05: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 VAA16277;
Thu, 30 Dec 1999 21:05:10 -0500 (EST)
To: Stephen Birch <sbirch@ironmountainsystems.com>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] HEAP_MOVED_IN error during vacuum?
In-reply-to: <386B8BCC.8142230A@ironmountainsystems.com>
References: <10168.943225229@sss.pgh.pa.us>
<386B8BCC.8142230A@ironmountainsystems.com>
Comments: In-reply-to Stephen Birch <sbirch@ironmountainsystems.com>
message dated "Thu, 30 Dec 1999 08:43:56 -0800"
Date: Thu, 30 Dec 1999 21:05:10 -0500
Message-ID: <16274.946605910@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Stephen Birch <sbirch@ironmountainsystems.com> writes:

When I vacuum the database (PostgreSQL 6.5.3 on SuSE 6.3 Linux, 2.2
kernel), I get the following error message:

ERROR: HEAP_MOVED_IN was not expected.

Can anyone tell me what a HEAP_MOVED_IN error is - I checked the
source but was not familiar enough to understand it? Any ideas on why
trim() may have cause it?

When VACUUM moves a tuple from one disk page to another (to compact the
table), the original tuple is marked HEAP_MOVED_OFF and the copy is
marked HEAP_MOVED_IN temporarily, until the VACUUM is ready to commit.
This is supposed to ensure that a failure partway through VACUUM won't
corrupt your table by leaving you with two copies of the same tuple.
(The HEAP_MOVED_OFF copy is valid until VACUUM commits, and the
HEAP_MOVED_IN copy is valid afterwards.)

I haven't heard of other reports of this error message, so I suspect
you have found some hard-to-hit boundary condition error in VACUUM's
data-shuffling logic. I guess that the reason you don't see the error
after a single trim() is that not very much data-shuffling is needed to
compact the table after just one tuple update.

What we need is a reproducible test case so we can chase down the bug
--- any chance you can provide one?

regards, tom lane

From bouncefilter Thu Dec 30 22:37:33 1999
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 WAA82454
for <pgsql-hackers@postgreSQL.org>;
Thu, 30 Dec 1999 22:36:46 -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
WAA02865;
Thu, 30 Dec 1999 22:34:55 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912310334.WAA02865@candle.pha.pa.us>
Subject: Re: [HACKERS] Is backend-libpq's "PQexec/PQfn/portal" code dead?
In-Reply-To: <16228.946605026@sss.pgh.pa.us> from Tom Lane at "Dec 30,
1999 08:50:26 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Thu, 30 Dec 1999 22:34:55 -0500 (EST)
CC: pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Perhaps move to _deadcode directory. That is usually what we do.

As near as I can tell, there are four whole modules of dead code in
src/backend/libpq/: be-dumpdata.c, be-pqexec.c, portal.c, portalbuf.c
(plus some support code in tcop/dest.c). These provide functions called
PQexec and PQfn, which apparently were once backend-side equivalents
to the frontend-libpq functions of the same names, as well as a whole
bunch of other code that may once have been shared with frontend-libpq,
but is so no longer.

The only externally referenced entry point to these modules is an
undocumented SQL function "pqtest", which I strongly suspect no one
is using.

I am strongly inclined to rip all this stuff out, because it's buggy.
I've already found memory-leak problems and dangling-global-pointer
problems, and there are probably more. I'd say this code has been
suffering from software rot for a long time.

Does anyone remember what this code was for, or know a reason to keep it
(and fix it) instead of ripping it out?

regards, tom lane

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

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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 Dec 31 00:54:35 1999
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 AAA07707
for <pgsql-hackers@postgreSQL.org>;
Fri, 31 Dec 1999 00:54:07 -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 AAA17788
for <pgsql-hackers@postgreSQL.org>;
Fri, 31 Dec 1999 00:54:06 -0500 (EST)
To: pgsql-hackers@postgreSQL.org
Subject: LIKE fixed(?) for non-ASCII collation orders
Date: Fri, 31 Dec 1999 00:54:06 -0500
Message-ID: <17785.946619646@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I have just committed what I hope is the final solution for the problem
of LIKE index optimization in non-ASCII locales. indxpath.c now
generates both a lower and upper indexqual in all locales. For example,
x LIKE 'foo%t'
will create indexqual conditions
x >= 'foo' AND x < 'fop'
The "<" condition is omitted only if the code is unable to produce a
string greater than the pattern's constant prefix.

Locale-specific variations in collation order are handled by the
cut-and-try method I suggested a while ago:

The approach I was considering for fixing the problem was to use a
loop that would repeatedly try to generate a string greater than the
prefix string. The basic loop step would increment the rightmost
byte as Goran has done (or, if it's already up to the limit, chop
it off and increment the next character position). Then test to
see whether the '<' operator actually believes the result is
greater than the given prefix, and repeat if not.

Although I believe that the code will work in non-ASCII locales and
MULTIBYTE character sets, I'm not set up to try it easily. Could
some folks try it out and report back?

The critical subroutine is attached, so if you prefer to eyeball
the code, here it is...

regards, tom lane

/*
* Try to generate a string greater than the given string or any string it is
* a prefix of. If successful, return a palloc'd string; else return NULL.
*
* To work correctly in non-ASCII locales with weird collation orders,
* we cannot simply increment "foo" to "fop" --- we have to check whether
* we actually produced a string greater than the given one. If not,
* increment the righthand byte again and repeat. If we max out the righthand
* byte, truncate off the last character and start incrementing the next.
* For example, if "z" were the last character in the sort order, then we
* could produce "foo" as a string greater than "fonz".
*
* This could be rather slow in the worst case, but in most cases we won't
* have to try more than one or two strings before succeeding.
*
* XXX in a sufficiently weird locale, this might produce incorrect results?
* For example, in German I believe "ss" is treated specially --- if we are
* given "foos" and return "foot", will this actually be greater than "fooss"?
*/
static char *
make_greater_string(const char * str, Oid datatype)
{
char *workstr;
int len;

/* Make a modifiable copy, which will be our return value if successful */
workstr = pstrdup((char *) str);

while ((len = strlen(workstr)) > 0)
{
unsigned char *lastchar = (unsigned char *) (workstr + len - 1);

/*
* Try to generate a larger string by incrementing the last byte.
*/
while (*lastchar < (unsigned char) 255)
{
(*lastchar)++;
if (string_lessthan(str, workstr, datatype))
return workstr; /* Success! */
}
/*
* Truncate off the last character, which might be more than 1 byte
* in MULTIBYTE case.
*/
#ifdef MULTIBYTE
len = pg_mbcliplen((const unsigned char *) workstr, len, len-1);
workstr[len] = '\0';
#else
*lastchar = '\0';
#endif
}

/* Failed... */
pfree(workstr);
return NULL;
}

From bouncefilter Fri Dec 31 02:01:35 1999
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 CAA19931
for <pgsql-hackers@postgreSQL.org>;
Fri, 31 Dec 1999 02:00:59 -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 BAA07579
for pgsql-hackers@postgreSQL.org; Fri, 31 Dec 1999 01:08:01 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912310608.BAA07579@candle.pha.pa.us>
Subject: When are subqueries needed
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: Fri, 31 Dec 1999 01:08:01 -0500 (EST)
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Can someone summarize when subqueries are required. I thought
aggregates often needed them, but I see now that table aliases can do it
in most cases. I think NOT IN can not be done without subqueries.

Is there a general rule on this? A rule about correlated subqueries?

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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 Dec 31 03:40:37 1999
Received: from gandalf.telecom.at (gandalf.telecom.at [194.118.26.84])
by hub.org (8.9.3/8.9.3) with ESMTP id DAA41233
for <hackers@postgreSQL.org>; Fri, 31 Dec 1999 03:39:55 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at
(sdexcgtw01.f000.d0188.sd.spardat.at [172.18.1.16])
by gandalf.telecom.at (xxx/xxx) with ESMTP id JAA13440
for <hackers@postgreSQL.org>; Fri, 31 Dec 1999 09:39:49 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <Z7Z555XX>; Fri, 31 Dec 1999 09:39:48 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C603FDC1EB@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'hackers@postgresql.org'" <hackers@postgreSQL.org>
Subject: AW: AW: [HACKERS] correlated subquery
Date: Fri, 31 Dec 1999 09:39:40 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

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

It finds the oldest person in each state. HAVING can't do
that, right?

Having can do that particular case: (e.g. Informix)

SELECT f1.firstname, f1.lastname, f1.age
FROM friends f1, friends f2
WHERE f1.state = f2.state
GROUP BY f2.state, f1.firstname, f1.lastname,

f1.age, f1.state

HAVING f1.age = max(f2.age)
ORDER BY firstname, lastname;

Hmm, yes, and you don't even need the GROUP BY state clauses.

But it's not really the same thing. In particular, if you
had two friends
with the same name and age, this would produce only one output record
for both, not two output records as Bruce's original query does.

That's neither likely nor a big problem in the hypothetical
application,
but other applications needing this type of query might be
more unhappy
about confusing similar records...

Yes, it only gives the same result, if "f1" has some sort of primary key
(e.g. oid),
that can be put into the group by clause.

Andreas

From bouncefilter Fri Dec 31 07:50:39 1999
Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37])
by hub.org (8.9.3/8.9.3) with ESMTP id HAA81621
for <hackers@postgreSQL.org>; Fri, 31 Dec 1999 07:50:25 -0500 (EST)
(envelope-from vadim@krs.ru)
Received: from krs.ru (dune.krs.ru [195.161.16.38])
by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id TAA24627
for <hackers@postgreSQL.org>; Fri, 31 Dec 1999 19:50:23 +0700 (KRS)
Sender: root@sunpine.krs.ru
Message-ID: <386CA68F.7381BDE5@krs.ru>
Date: Fri, 31 Dec 1999 19:50:23 +0700
From: Vadim Mikheev <vadim@krs.ru>
Organization: OJSC Rostelecom (Krasnoyarsk)
X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386)
X-Accept-Language: ru, en
MIME-Version: 1.0
To: PostgreSQL Developers List <hackers@postgreSQL.org>
Subject: Happy New Year!
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Long life to PostgreSQL!
-:)))

Vadim

From bouncefilter Fri Dec 31 08:31:40 1999
Received: from thelab.hub.org (nat200.60.mpoweredpc.net [142.177.200.60])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA89384
for <hackers@postgresql.org>; Fri, 31 Dec 1999 08:31:36 -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 JAA34631;
Fri, 31 Dec 1999 09:26:05 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 31 Dec 1999 09:26:05 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Vadim Mikheev <vadim@krs.ru>
cc: PostgreSQL Developers List <hackers@postgresql.org>
Subject: Re: [HACKERS] Happy New Year!
In-Reply-To: <386CA68F.7381BDE5@krs.ru>
Message-ID: <Pine.BSF.4.21.9912310925210.18498-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 31 Dec 1999, Vadim Mikheev wrote:

Long life to PostgreSQL!
-:)))

Hey, wait for us to catch up, eh? :) Its only, like, 9:30am out here, and
even earlier for the rest of this continent :)

Hope everyone is having a safe one ...

From bouncefilter Fri Dec 31 09:51:52 1999
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 JAA32031
for <hackers@postgreSQL.org>; Fri, 31 Dec 1999 09:50:55 -0500 (EST)
(envelope-from vev@michvhf.com)
Received: from paprika.michvhf.com (paprika.michvhf.com [209.57.60.12])
by clio.trends.ca (8.9.3+Sun/8.9.3) with SMTP id JAA21102
for <hackers@postgreSQL.org>; Fri, 31 Dec 1999 09:39:13 -0500 (EST)
Received: (qmail 28876 invoked by uid 1001); 31 Dec 1999 14:37:53 -0000
Message-ID: <XFMail.991231093753.vev@michvhf.com>
X-Mailer: XFMail 1.4.0 on FreeBSD
X-Priority: 3 (Normal)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
In-Reply-To: <Pine.BSF.4.21.9912310925210.18498-100000@thelab.hub.org>
Date: Fri, 31 Dec 1999 09:37:53 -0500 (EST)
X-Face: *<Qp5V!eyV,gni`N^N%1YX'$I&uuX]ay;
oq#ZL5Hn8EQsu'.oK0j9$#JM0V?!=Q^[i.81u9
@~=ZjeI}gHY`?2_1,xy/,Gde>0^4Iw)<k8}vg!%l;
&]@PF0LjU)N*m*2"R^UO+PAQ<w}/y)5UVE==w
H$q0*b`HN{+Ekeo?5V(0$MH&NZA3~vOThJxhY(7M:"`CrqO9[VC!^W&&eih!MTq4qk=Vg'd&`{dpgp
3-nck}7do'o/|<RI,
igc#cg8t|PZUEh{Rrx4<~tm`/G8E*wE{G:x}bva@[+YVT`g(u]*^!`1iO*
Sender: vev@paprika.michvhf.com
From: Vince Vielhaber <vev@michvhf.com>
To: The Hermit Hacker <scrappy@hub.org>
Subject: Re: [HACKERS] Happy New Year!
Cc: PostgreSQL Developers List <hackers@postgreSQL.org>,
Vadim Mikheev <vadim@krs.ru>

On 31-Dec-99 The Hermit Hacker wrote:

On Fri, 31 Dec 1999, Vadim Mikheev wrote:

Long life to PostgreSQL!
-:)))

Hey, wait for us to catch up, eh? :) Its only, like, 9:30am out here, and
even earlier for the rest of this continent :)

Hope everyone is having a safe one ...

Yep, I'm just waking up. Got back from up north late last nite. We
figure (based on the y2k testing Detroit Edison did a couple of months
ago) that we'll be in the dark at midnite. Should be interesting :)

Happy Y2K Everyone!!

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 Fri Dec 31 09:58:53 1999
Received: from mail.mediascape.de (root@mail.mediascape.de [212.105.192.3])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA37952
for <pgsql-hackers@postgreSQL.org>;
Fri, 31 Dec 1999 09:58:19 -0500 (EST)
(envelope-from ad@papyrus-gmbh.de)
Received: from lx2.noname (papyrus-gmbh.de [212.105.195.67])
by mail.mediascape.de (8.9.3/8.9.2) with ESMTP id QAA25995
for <pgsql-hackers@postgreSQL.org>; Fri, 31 Dec 1999 16:06:29 +0100
Received: from tarzan ([132.147.160.219]) by lx2.noname
with esmtp (ident root using rfc1413) id m1243Td-00114vC
(Debian Smail-3.2.0.102 1998-Aug-2 #2);
Fri, 31 Dec 1999 15:56:25 +0100 (CET)
Received: by papyrus-gmbh.de via sendmail from stdin
id <m1243Td-002nm8C@tarzan> (Debian Smail3.2.0.102)
for pgsql-hackers@postgreSQL.org; Fri, 31 Dec 1999 15:56:25 +0100 (CET)
To: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] LIKE fixed(?) for non-ASCII collation orders
References: <17785.946619646@sss.pgh.pa.us>
Mime-Version: 1.0 (generated by tm-edit 7.108)
Content-Type: text/plain; charset=US-ASCII
From: Andreas Degert <ad@papyrus-gmbh.de>
Date: 31 Dec 1999 15:56:25 +0100
In-Reply-To: Tom Lane's message of "Fri, 31 Dec 1999 00:54:06 -0500"
Message-ID: <87vh5fgnvq.fsf@tarzan.noname.>
Lines: 17
X-Mailer: Gnus v5.5/XEmacs 20.4 - "Emerald"

Tom Lane <tgl@sss.pgh.pa.us> writes:

I have just committed what I hope is the final solution for the problem
of LIKE index optimization in non-ASCII locales. indxpath.c now
generates both a lower and upper indexqual in all locales. For example,
x LIKE 'foo%t'
will create indexqual conditions
x >= 'foo' AND x < 'fop'
The "<" condition is omitted only if the code is unable to produce a
string greater than the pattern's constant prefix.

the .. >= .. < .. condition will result in addtional matches, like 'fo ot',
so you still have to check with LIKE. I'm using such an expression
(without the additional LIKE) in an application, and it seems to match
at least everything that LIKE would match too (my users would have
complained about missing matches, but i never did a formal test or
evaluation).

From bouncefilter Fri Dec 31 10:15:52 1999
Received: from marvin.muc.de (marvin.muc.de [193.149.48.2])
by hub.org (8.9.3/8.9.3) with SMTP id KAA66707
for <hackers@postgresql.org>; Fri, 31 Dec 1999 10:15:38 -0500 (EST)
(envelope-from
moderators-muc-lists-postgres-hackers-owner@moderators.muc.de)
Received: (qmail 16518 invoked by alias); 31 Dec 1999 15:15:08 -0000
Delivered-To: moderators-muc-lists-postgres-hackers@moderators.muc.de
Received: (qmail 16513 invoked from network); 31 Dec 1999 15:15:08 -0000
Received: from mailout06.sul.t-online.de (194.25.134.19)
by marvin.muc.de with SMTP; 31 Dec 1999 15:15:08 -0000
Received: from imh00.btx.dtag.de by mailout06.sul.t-online.de with smtp
id 1243ld-0000ka-00; Fri, 31 Dec 1999 16:15:01 +0100
Received: from news02.btx.dtag.de by imh00.btx.dtag.de
with esmtp id 1243ld-0005g8-00; Fri, 31 Dec 1999 16:15:01 +0100
Received: from news by news02.btx.dtag.de
with local id 1243lc-0001fe-00; Fri, 31 Dec 1999 16:15:00 +0100
To: muc-lists-postgres-hackers@moderators.muc.de
Path: news.btx.dtag.de!not-for-mail
From: "S�ren Kreimeier" <soerenkreimeier@t-online.de>
Newsgroups: muc.lists.postgres.hackers
Subject: How you can put over 20 GB on a single CD!
Date: Fri, 31 Dec 1999 16:17:55 +0100
Organization: T-Online
Lines: 7
Message-ID: <84ih9k$68p$1@news02.btx.dtag.de>
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: news02.btx.dtag.de 946653300 6425 057443576-0001 991231 15:15:00
X-Complaints-To: abuse@t-online.de
X-Sender: 057443576-0001@t-dialin.net
X-Newsreader: Microsoft Outlook Express 4.72.3110.1
X-MimeOLE: Produced By Microsoft MimeOLE V4.72.3110.3

1. you buy a knew HDD.(20GB)
2. Then you do the files on it.
3. After that you take the HDD out of your computer.
4. Then you put it on the CD
5. Finish.

From bouncefilter Fri Dec 31 10:42:53 1999
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 KAA18366
for <pgsql-hackers@postgreSQL.org>;
Fri, 31 Dec 1999 10:42:04 -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 KAA18475;
Fri, 31 Dec 1999 10:40:51 -0500 (EST)
To: Andreas Degert <ad@papyrus-gmbh.de>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] LIKE fixed(?) for non-ASCII collation orders
In-reply-to: <87vh5fgnvq.fsf@tarzan.noname.>
References: <17785.946619646@sss.pgh.pa.us> <87vh5fgnvq.fsf@tarzan.noname.>
Comments: In-reply-to Andreas Degert <ad@papyrus-gmbh.de>
message dated "31 Dec 1999 15:56:25 +0100"
Date: Fri, 31 Dec 1999 10:40:51 -0500
Message-ID: <18472.946654851@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Andreas Degert <ad@papyrus-gmbh.de> writes:

the .. >= .. < .. condition will result in addtional matches, like 'fo ot',
so you still have to check with LIKE.

Of course. The point of all this is just to create a "prefilter" that can
be implemented by scanning an index, so that most of the rows in the
table need not be visited at all when the LIKE pattern has the right
form. But the original LIKE operator is still executed as part of the
WHERE condition for each row.

regards, tom lane

From bouncefilter Fri Dec 31 10:52:54 1999
Received: from thelab.hub.org (nat200.60.mpoweredpc.net [142.177.200.60])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA30576
for <hackers@postgreSQL.org>; Fri, 31 Dec 1999 10:52:14 -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 LAA35387;
Fri, 31 Dec 1999 11:52:09 -0400 (AST) (envelope-from scrappy@hub.org)
X-Authentication-Warning: thelab.hub.org: scrappy owned process doing -bs
Date: Fri, 31 Dec 1999 11:52:09 -0400 (AST)
From: The Hermit Hacker <scrappy@hub.org>
To: Vince Vielhaber <vev@michvhf.com>
cc: PostgreSQL Developers List <hackers@postgreSQL.org>,
Vadim Mikheev <vadim@krs.ru>
Subject: Re: [HACKERS] Happy New Year!
In-Reply-To: <XFMail.991231093753.vev@michvhf.com>
Message-ID: <Pine.BSF.4.21.9912311151420.18498-100000@thelab.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 31 Dec 1999, Vince Vielhaber wrote:

On 31-Dec-99 The Hermit Hacker wrote:

On Fri, 31 Dec 1999, Vadim Mikheev wrote:

Long life to PostgreSQL!
-:)))

Hey, wait for us to catch up, eh? :) Its only, like, 9:30am out here, and
even earlier for the rest of this continent :)

Hope everyone is having a safe one ...

Yep, I'm just waking up. Got back from up north late last nite. We
figure (based on the y2k testing Detroit Edison did a couple of months
ago) that we'll be in the dark at midnite. Should be interesting :)

I'm personally hoping for *some* sort of catastrophe...with all the hype,
something interesting has to happen :)

From bouncefilter Fri Dec 31 11:49:53 1999
Received: from tele-post-20.mail.demon.net (tele-post-20.mail.demon.net
[194.217.242.20]) by hub.org (8.9.3/8.9.3) with ESMTP id LAA69739
for <hackers@postgresql.org>; Fri, 31 Dec 1999 11:49:49 -0500 (EST)
(envelope-from peter@retep.org.uk)
Received: from maidast.demon.co.uk ([158.152.22.37] helo=maidast.retep.org.uk)
by tele-post-20.mail.demon.net with esmtp (Exim 2.12 #2)
id 1245FD-000MI1-0K; Fri, 31 Dec 1999 16:49:40 +0000
Received: from localhost (peter@localhost [127.0.0.1])
by maidast.retep.org.uk (8.9.3/8.9.3) with ESMTP id QAA04331;
Fri, 31 Dec 1999 16:47:22 GMT
Date: Fri, 31 Dec 1999 16:47:22 +0000 (GMT)
From: Peter Mount <peter@retep.org.uk>
To: Vadim Mikheev <vadim@krs.ru>
cc: PostgreSQL Developers List <hackers@postgresql.org>
Subject: Re: [HACKERS] Happy New Year!
In-Reply-To: <386CA68F.7381BDE5@krs.ru>
Message-ID: <Pine.LNX.4.10.9912311527430.4185-100000@maidast.retep.org.uk>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Fri, 31 Dec 1999, Vadim Mikheev wrote:

Long life to PostgreSQL!

Here here!

I'm now off to a party, so I'll wish everyone a happy new year.

Peter

--
Peter T Mount peter@retep.org.uk
Main Homepage: http://www.retep.org.uk
PostgreSQL JDBC Faq: http://www.retep.org.uk/postgres
Java PDF Generator: http://www.retep.org.uk/pdf

From bouncefilter Fri Dec 31 13:52:55 1999
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 NAA90022
for <pgsql-hackers@postgreSQL.org>;
Fri, 31 Dec 1999 13:51:57 -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
NAA25467;
Fri, 31 Dec 1999 13:50:07 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <199912311850.NAA25467@candle.pha.pa.us>
Subject: Re: [HACKERS] LIKE fixed(?) for non-ASCII collation orders
In-Reply-To: <87vh5fgnvq.fsf@tarzan.noname.> from Andreas Degert at "Dec 31,
1999 03:56:25 pm"
To: Andreas Degert <ad@papyrus-gmbh.de>
Date: Fri, 31 Dec 1999 13:50:07 -0500 (EST)
CC: pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Yes, we always still match the LIKE. The additional comparisons just
make the number of rows smaller.

Tom Lane <tgl@sss.pgh.pa.us> writes:

I have just committed what I hope is the final solution for the problem
of LIKE index optimization in non-ASCII locales. indxpath.c now
generates both a lower and upper indexqual in all locales. For example,
x LIKE 'foo%t'
will create indexqual conditions
x >= 'foo' AND x < 'fop'
The "<" condition is omitted only if the code is unable to produce a
string greater than the pattern's constant prefix.

the .. >= .. < .. condition will result in addtional matches, like 'fo ot',
so you still have to check with LIKE. I'm using such an expression
(without the additional LIKE) in an application, and it seems to match
at least everything that LIKE would match too (my users would have
complained about missing matches, but i never did a formal test or
evaluation).

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

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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 Mon Sep 20 08:21:53 1999
Received: from tech.com.au (IDENT:root@techpt.lnk.telstra.net
[139.130.75.122])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA52079
for <pgsql-hackers@hub.org>; Mon, 20 Sep 1999 08:21:43 -0400 (EDT)
(envelope-from chris@tech.com.au)
Received: from tech.com.au (tardis [203.41.180.243])
by tech.com.au (8.9.3/8.9.3) with ESMTP id WAA23341
for <pgsql-hackers@hub.org>; Mon, 20 Sep 1999 22:21:38 +1000
Sender: chris@tech.com.au
Message-ID: <386D3F80.EE1DB6F3@tech.com.au>
Date: Sat, 01 Jan 2000 10:42:56 +1100
From: Chris Bitmead <chris@tech.com.au>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.2.12 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-hackers@hub.org
Subject: Re: [HACKERS] All things equal, we are still alot slower then MySQL?
References: <199909200406.AAA06875@candle.pha.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I am told we are the same as Ingres, and slower than Oracle with no -F,
and faster than Oracle with -F.

What is "-F"?

--
Chris Bitmead
mailto:chris@tech.com.au

From bouncefilter Sat Jan 1 00:11:02 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 AAA97235
for <hackers@postgreSQL.org>; Sat, 1 Jan 2000 00:10:29 -0500 (EST)
(envelope-from vev@michvhf.com)
Received: (qmail 4162 invoked by uid 1001); 1 Jan 2000 05:10:27 -0000
Message-ID: <XFMail.000101001027.vev@michvhf.com>
X-Mailer: XFMail 1.4.0 on FreeBSD
X-Priority: 3 (Normal)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 8bit
MIME-Version: 1.0
In-Reply-To: <m124ExL-0003kGC@orion.SAPserv.Hamburg.dsh.de>
Date: Sat, 01 Jan 2000 00:10:27 -0500 (EST)
X-Face: *<Qp5V!eyV,gni`N^N%1YX'$I&uuX]ay;
oq#ZL5Hn8EQsu'.oK0j9$#JM0V?!=Q^[i.81u9
@~=ZjeI}gHY`?2_1,xy/,Gde>0^4Iw)<k8}vg!%l;
&]@PF0LjU)N*m*2"R^UO+PAQ<w}/y)5UVE==w
H$q0*b`HN{+Ekeo?5V(0$MH&NZA3~vOThJxhY(7M:"`CrqO9[VC!^W&&eih!MTq4qk=Vg'd&`{dpgp
3-nck}7do'o/|<RI,
igc#cg8t|PZUEh{Rrx4<~tm`/G8E*wE{G:x}bva@[+YVT`g(u]*^!`1iO*
Sender: vev@paprika.michvhf.com
From: Vince Vielhaber <vev@michvhf.com>
To: (Jan Wieck) <wieck@debis.com>
Subject: Re: [HACKERS] Happy New Year!
Cc: hackers@postgreSQL.org, vadim@krs.ru,
(The Hermit Hacker) <scrappy@hub.org>

On 01-Jan-00 Jan Wieck wrote:

I'm personally hoping for *some* sort of catastrophe...with all the hype,
something interesting has to happen :)

You too?

Now it's 01/01/2000 04:08 here in Harsefeld, and absolutely
nothing happened, not one single power peak or fail, not any
single radio station telling any kind about Y2K problems.
Really frustrating, if I think about all the stress during
the past months.

Big yawn here. After all the failed Y2k testing it looks like someone
got it all fixed. Happy Y2K Everyone!

Anyway, something interesting has happened.

PostgreSQL, known as the dinosaur of databases, survived Y2K.
And ISTM, it's in a better condition than ever before. Isn't
that worth all the efford, anyone of us spent?

Thanks to all, all over the world, and a happy new year.

My sentiments exactly!!

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 Sat Jan 1 03:41:04 2000
Received: from onestone.elsinore.klever.net (root@onestone.elsinore.klever.net
[207.175.129.2]) by hub.org (8.9.3/8.9.3) with ESMTP id DAA38244
for <pgsql-hackers@postgresql.org>; Sat, 1 Jan 2000 03:40:49 -0500 (EST)
(envelope-from sbirch@ironmountainsystems.com)
Received: from ironmountainsystems.com ([209.234.201.23]) by
onestone.elsinore.klever.net (8.8.7/8.8.0) with ESMTP id
BAA16791 for <pgsql-hackers@postgresql.org>;
Sat, 1 Jan 2000 01:37:52 -0800
Sender: sbirch@onestone.elsinore.klever.net
Message-ID: <386DBD4E.688625ED@ironmountainsystems.com>
Date: Sat, 01 Jan 2000 00:39:42 -0800
From: Stephen Birch <sbirch@ironmountainsystems.com>
Organization: Iron Mountain Systems, Inc.
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.10 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pgsql-hackers@postgresql.org
Subject: HEAP_MOVED_IN during vacuum?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

When I vacuum the database (PostgreSQL 6.5.3 on SuSE 6.3 Linux, 2.2
kernel), I get the following error message:

ERROR: HEAP_MOVED_IN was not expected.
vacuumdb: database vacuum failed on ntis

This error only seems to occur after I have used the trim function to
clean up one of
the rows in the msg table of a database called ntis:

ntis=>update msg set description = trim(description);
UPDATE 12069
ntis=>

To try and track down the problem, I wrote a C program (using ecpg) that
trimmed the table one row at a time and vacuumed between each row
operation. I was hoping that this program would reveal a problem with
the data in one of my records. Unfortunately the one row at a time
approach did not reveal the problem and each vacuum operated without
error.

Can anyone tell me what a HEAP_MOVED_IN error is - I checked the source
but was not familiar enough to understand it? Any ideas on why trim()
may have cause it?

From bouncefilter Sat Jan 1 11:33:10 2000
Received: from mx02.gis.net ([208.218.130.7])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA21456
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 11:32:46 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from laptop.pacifier.com (nas-76-2.boston.navinet.net [216.67.76.2])
by mx02.gis.net (8.8.8/8.8.8+pyrd) with SMTP id LAA26355;
Sat, 1 Jan 2000 11:28:16 -0500 (EST)
Message-Id: <3.0.1.32.20000101104436.00eed84c@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com (Unverified)
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 01 Jan 2000 10:44:36 -0800
To: Peter Eisentraut <peter_e@gmx.net>, Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] dubious improvement in new psql
Cc: pgsql-hackers@postgreSQL.org
In-Reply-To: <Pine.GSO.4.02A.9912282309160.12951-100000@Hamster.DoCS.UU.
SE>
References: <14820.946172131@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 11:14 PM 12/28/99 +0100, Peter Eisentraut wrote:

On Sat, 25 Dec 1999, Tom Lane wrote:

The new psql automatically tries to reconnect if the backend disconnects
unexpectedly. This feature strikes me as ill-conceived; furthermore
it appears to be buggy.

It's ill-conceived because:
(1) under WAL, following a backend crash the postmaster is going to be
spending a few seconds reinitializing; an immediate reconnect attempt
is almost guaranteed to fail.

Good point.

(2) if I'm running an SQL script, I think it's extremely foolhardy
to press on with executing the script as though nothing had happened.
A backend crash is not an event to be lightly ignored.

It only does the reconnect thing if it's used interactively.

This raises a question, then. What should drivers for (say) web
servers that are expected to stay up 24/7 do if reconnecting to a
broken db connection can't be made reliable?

I've currently rewritten the AOLserver driver to do just that, and
it's working fine with 6.5.3. The AOLserver driver for Oracle most
certainly can reconnect to a broken connection - to tell folks that
this can't be done with the WAL version of Postgres will simply
reinforce those of my friends who laugh at me for trying to use
Postgres instead of simply biting the bullet and buying an Oracle
license...

- 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 Jan 1 13:49: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 NAA42665
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 13:48: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 NAA19680;
Sat, 1 Jan 2000 13:48:36 -0500 (EST)
To: Don Baccus <dhogaza@pacifier.com>
cc: Peter Eisentraut <peter_e@gmx.net>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] dubious improvement in new psql
In-reply-to: <3.0.1.32.20000101104436.00eed84c@mail.pacifier.com>
References: <14820.946172131@sss.pgh.pa.us>
<3.0.1.32.20000101104436.00eed84c@mail.pacifier.com>
Comments: In-reply-to Don Baccus <dhogaza@pacifier.com>
message dated "Sat, 01 Jan 2000 10:44:36 -0800"
Date: Sat, 01 Jan 2000 13:48:36 -0500
Message-ID: <19677.946752516@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Don Baccus <dhogaza@pacifier.com> writes:

The AOLserver driver for Oracle most
certainly can reconnect to a broken connection - to tell folks that
this can't be done with the WAL version of Postgres

I said no such thing!

You certainly *can* reconnect, although under WAL it will take a delay
(or better, a retry loop).

However, I think reconnection has to be integrated into the
application's logic at a level where you can have some idea of what
needs to be redone after reconnecting. That's why I objected to having
psql do it. If psql's only going to do it interactively then I guess
it's safe enough, though.

Question for discussion: when the WAL postmaster is running a database
start or restart, perhaps it should simply delay processing of new
connection requests until the DB is ready, instead of rejecting them
immediately? That would eliminate the need for retry loops in
applications, and thereby avoid wasted retry processing on both sides.
On the other hand, I can see where an unexpected multi-second delay to
connect might be bad news, too. Comments?

regards, tom lane

From bouncefilter Sat Jan 1 14:04:12 2000
Received: from Mail.austin.rr.com (sm2.texas.rr.com [24.93.35.55])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA47099
for <pgsql-hackers@postgresql.org>; Sat, 1 Jan 2000 14:03:59 -0500 (EST)
(envelope-from eloehr@austin.rr.com)
Received: from austin.rr.com ([24.27.30.56]) by Mail.austin.rr.com with
Microsoft SMTPSVC(5.5.1877.197.19); Sat, 1 Jan 2000 12:55:08 -0600
Sender: ed
Message-ID: <386E4FEE.CA3E21C3@austin.rr.com>
Date: Sat, 01 Jan 2000 13:05:18 -0600
From: Ed Loehr <eloehr@austin.rr.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: Don Baccus <dhogaza@pacifier.com>, Peter Eisentraut <peter_e@gmx.net>,
pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] dubious improvement in new psql
References: <14820.946172131@sss.pgh.pa.us>
<3.0.1.32.20000101104436.00eed84c@mail.pacifier.com>
<19677.946752516@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

Question for discussion: when the WAL postmaster is running a database
start or restart, perhaps it should simply delay processing of new
connection requests until the DB is ready, instead of rejecting them
immediately? That would eliminate the need for retry loops in
applications, and thereby avoid wasted retry processing on both sides.
On the other hand, I can see where an unexpected multi-second delay to
connect might be bad news, too. Comments?

Suggestion: Make the delay/reconnect optional with configurable
parameters for how many times to retry, how long to retry, etc.

I have an Apache mod-perl app already doing this reconnect logic, and I'm
very glad my app has control over those parameters.

Cheers,
Ed Loehr

From bouncefilter Sat Jan 1 19:27:14 2000
Received: from Mail.austin.rr.com (sm2.texas.rr.com [24.93.35.55])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA02371
for <pgsql-hackers@postgresql.org>; Sat, 1 Jan 2000 19:26:59 -0500 (EST)
(envelope-from eloehr@austin.rr.com)
Received: from austin.rr.com ([24.27.30.56]) by Mail.austin.rr.com with
Microsoft SMTPSVC(5.5.1877.197.19); Sat, 1 Jan 2000 18:18:11 -0600
Sender: ed
Message-ID: <386E9BA4.3E9C1651@austin.rr.com>
Date: Sat, 01 Jan 2000 18:28:20 -0600
From: Ed Loehr <eloehr@austin.rr.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.12-20smp i686)
X-Accept-Language: en
MIME-Version: 1.0
To: pghackers <pgsql-hackers@postgresql.org>
Subject: pgsql y2k bug?
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

With pg 6.5.2, I just noticed this timestamped vacuum output in
my log file...

1000101.18:14:18.555 [6514] DEBUG: --Relation
ui_option_choice--
1000101.18:14:18.555 [6514] DEBUG: Pages 1: Changed 0, Reapped
0, Empty 0, New 0; Tup 5: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0,
MinLen 68, MaxLen 100; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
1000101.18:14:18.558 [6514] DEBUG: Index ui_option_choice_pkey:
Pages 2; Tuples 5. Elapsed 0/0 sec.
1000101.18:14:18.558 [6514] DEBUG: Index
ui_option_choice_id_key: Pages 2; Tuples 5. Elapsed 0/0 sec.
1000101.18:14:18.570 [6514] DEBUG: --Relation
ui_default_preference--

Notice the Jan 1, 100 A.D. timestamp...

% date
Sat Jan 1 18:24:20 CST 2000

AFAICT, this is limited to the logging mechanism (datetime output
appears fine).

Cheers,
Ed Loehr

From bouncefilter Sat Jan 1 20:44:15 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 UAA17534
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 20:43:57 -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
UAA04730;
Sat, 1 Jan 2000 20:42:57 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001020142.UAA04730@candle.pha.pa.us>
Subject: Re: [HACKERS] pgsql y2k bug?
In-Reply-To: <386E9BA4.3E9C1651@austin.rr.com> from Ed Loehr at "Jan 1, 2000
06:28:20 pm"
To: Ed Loehr <eloehr@austin.rr.com>
Date: Sat, 1 Jan 2000 20:42:57 -0500 (EST)
CC: pghackers <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary=ELM946777377-3693-0_
Content-Transfer-Encoding: 7bit

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

With pg 6.5.2, I just noticed this timestamped vacuum output in
my log file...

1000101.18:14:18.555 [6514] DEBUG: --Relation
ui_option_choice--
1000101.18:14:18.555 [6514] DEBUG: Pages 1: Changed 0, Reapped
0, Empty 0, New 0; Tup 5: Vac 0, Keep/VTL 0/0, Crash 0, UnUsed 0,
MinLen 68, MaxLen 100; Re-using: Free/Avail. Space 0/0;
EndEmpty/Avail. Pages 0/0. Elapsed 0/0 sec.
1000101.18:14:18.558 [6514] DEBUG: Index ui_option_choice_pkey:
Pages 2; Tuples 5. Elapsed 0/0 sec.
1000101.18:14:18.558 [6514] DEBUG: Index
ui_option_choice_id_key: Pages 2; Tuples 5. Elapsed 0/0 sec.
1000101.18:14:18.570 [6514] DEBUG: --Relation
ui_default_preference--

Notice the Jan 1, 100 A.D. timestamp...

OK, I have gone through all the code, looking at the handling of
tm_year, and found two possible areas for problems. One was in
DATEDEBUG code that is ifdef'ed out and unused, and the trace code you
reported.

I am attaching a diff to fix the problem. We were reporting only a
2-digit year, and tm_year reports years since 1900, so it was reporting
100 for year 2000. The field was %02d, but the number was three digits
so it printed all three.

If we do not make a new release for this, the fix will appear in 7.0.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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

--ELM946777377-3693-0_
Content-Type: text/plain
Content-Disposition: inline; filename="/bjm/diff"
Content-Transfer-Encoding: 7bit

*** ./backend/utils/misc/trace.c.orig	Sat Jan  1 20:37:01 2000
--- ./backend/utils/misc/trace.c	Sat Jan  1 20:37:15 2000
***************
*** 227,234 ****
  	time = localtime(&tm);

sprintf(pid, "[%d]", MyProcPid);
! sprintf(timestamp, "%02d%02d%02d.%02d:%02d:%02d.%03d %7s ",
! time->tm_year, time->tm_mon + 1, time->tm_mday,
time->tm_hour, time->tm_min, time->tm_sec,
(int) (tv.tv_usec/1000), pid);

--- 227,234 ----
  	time = localtime(&tm);

sprintf(pid, "[%d]", MyProcPid);
! sprintf(timestamp, "%04d%02d%02d.%02d:%02d:%02d.%03d %7s ",
! time->tm_year+1900, time->tm_mon + 1, time->tm_mday,
time->tm_hour, time->tm_min, time->tm_sec,
(int) (tv.tv_usec/1000), pid);

--ELM946777377-3693-0_

--ELM946777377-3693-0_--

From bouncefilter Sat Jan 1 21:01:15 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 VAA22604
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 21:01:03 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: from candle.pha.pa.us (s5-03.ppp.op.net [209.152.195.67])
by clio.trends.ca (8.9.3+Sun/8.9.3) with ESMTP id VAA11467
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 21:00:57 -0500 (EST)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
UAA05000;
Sat, 1 Jan 2000 20:53:47 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001020153.UAA05000@candle.pha.pa.us>
Subject: Is DATEDEBUG useful
To: "Thomas G. Lockhart" <lockhart@alumni.caltech.edu>
Date: Sat, 1 Jan 2000 20:53:47 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

I have removed the DATEDEBUG code. I can easily re-add it. Is there
any use to keeping the code? The code looks clearer without it.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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 Jan 1 21:07: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 VAA23084
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 21:06: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 VAA20765;
Sat, 1 Jan 2000 21:06:23 -0500 (EST)
To: Bruce Momjian <pgman@candle.pha.pa.us>
cc: Ed Loehr <eloehr@austin.rr.com>, pghackers <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] pgsql y2k bug?
In-reply-to: <200001020142.UAA04730@candle.pha.pa.us>
References: <200001020142.UAA04730@candle.pha.pa.us>
Comments: In-reply-to Bruce Momjian <pgman@candle.pha.pa.us>
message dated "Sat, 01 Jan 2000 20:42:57 -0500"
Date: Sat, 01 Jan 2000 21:06:22 -0500
Message-ID: <20762.946778782@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

I am attaching a diff to fix the problem. We were reporting only a
2-digit year, and tm_year reports years since 1900, so it was reporting
100 for year 2000. The field was %02d, but the number was three digits
so it printed all three.

If you are going to go to 4-digit years in timestamps, I think you also
need to increase the TIMESTAMP_SIZE constant used by elog.c when
ELOG_TIMESTAMPS is set.

An alternative solution is to print time->tm_year % 100.

Either of these solutions might break existing programs that analyze
logfiles, if any there be...

regards, tom lane

From bouncefilter Sat Jan 1 21:12:16 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 VAA23723
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 21:11: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
VAA05416;
Sat, 1 Jan 2000 21:10:33 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200001020210.VAA05416@candle.pha.pa.us>
Subject: Re: [HACKERS] pgsql y2k bug?
In-Reply-To: <20762.946778782@sss.pgh.pa.us> from Tom Lane at "Jan 1,
2000 09:06:22 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Sat, 1 Jan 2000 21:10:32 -0500 (EST)
CC: Ed Loehr <eloehr@austin.rr.com>, pghackers <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL65 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

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

I am attaching a diff to fix the problem. We were reporting only a
2-digit year, and tm_year reports years since 1900, so it was reporting
100 for year 2000. The field was %02d, but the number was three digits
so it printed all three.

If you are going to go to 4-digit years in timestamps, I think you also
need to increase the TIMESTAMP_SIZE constant used by elog.c when
ELOG_TIMESTAMPS is set.

An alternative solution is to print time->tm_year % 100.

Either of these solutions might break existing programs that analyze
logfiles, if any there be...

Done. Length increased by 2 from 28 to 30.

-- 
  Bruce Momjian                        |  http://www.op.net/~candle
  maillist@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 Jan 1 19:25:14 2000
Received: from mx02.gis.net ([208.218.130.7])
by hub.org (8.9.3/8.9.3) with ESMTP id TAA02215
for <pgsql-hackers@postgreSQL.org>; Sat, 1 Jan 2000 19:24:35 -0500 (EST)
(envelope-from dhogaza@pacifier.com)
Received: from laptop.pacifier.com (nas-76-57.boston.navinet.net
[216.67.76.57]) by mx02.gis.net (8.8.8/8.8.8+pyrd) with SMTP id
TAA21069; Sat, 1 Jan 2000 19:17:24 -0500 (EST)
Message-Id: <3.0.1.32.20000101190938.00ed45e0@mail.pacifier.com>
X-Sender: dhogaza@mail.pacifier.com
X-Mailer: Windows Eudora Pro Version 3.0.1 (32)
Date: Sat, 01 Jan 2000 19:09:38 -0800
To: Tom Lane <tgl@sss.pgh.pa.us>
From: Don Baccus <dhogaza@pacifier.com>
Subject: Re: [HACKERS] dubious improvement in new psql
Cc: Peter Eisentraut <peter_e@gmx.net>, pgsql-hackers@postgreSQL.org
In-Reply-To: <19677.946752516@sss.pgh.pa.us>
References: <3.0.1.32.20000101104436.00eed84c@mail.pacifier.com>
<14820.946172131@sss.pgh.pa.us>
<3.0.1.32.20000101104436.00eed84c@mail.pacifier.com>
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"

At 01:48 PM 1/1/00 -0500, Tom Lane wrote:

I said no such thing!

You certainly *can* reconnect, although under WAL it will take a delay
(or better, a retry loop).

However, I think reconnection has to be integrated into the
application's logic at a level where you can have some idea of what
needs to be redone after reconnecting. That's why I objected to having
psql do it. If psql's only going to do it interactively then I guess
it's safe enough, though.

OK, my misunderstanding. I couldn't understand why psql in interactive
mode should be a problem and took your comments in a more general context.

Question for discussion: when the WAL postmaster is running a database
start or restart, perhaps it should simply delay processing of new
connection requests until the DB is ready, instead of rejecting them
immediately? That would eliminate the need for retry loops in
applications, and thereby avoid wasted retry processing on both sides.
On the other hand, I can see where an unexpected multi-second delay to
connect might be bad news, too. Comments?

I've been thinking about this one, actually...

Perhaps letting the caller decide in some manner? In my driver environment
I'm not really supposed to call sleep or the like and a busy-wait for the
connection(s) to be rebuilt probably isn't the best thing to do, since the
postmaster is going to be hard at work straightening out things with the
WAL.

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