Re: [HACKERS] Re: [SQL] prob with aggregate and group by - returns multiplesh

Started by Moray McConnachieabout 26 years ago1 messageshackers

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

about 26 years ago

----- Original Message -----
From: Bruce Momjian <pgman@candle.pha.pa.us>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
Cc: Tom Lane <tgl@sss.pgh.pa.us>; 'pgsql-sql@postgresql.org'
<pgsql-sql@postgreSQL.org>; PostgreSQL Development
<pgsql-hackers@postgreSQL.org>
Sent: Wednesday, March 01, 2000 6:18 AM
Subject: Re: [HACKERS] Re: [SQL] prob with aggregate and group by -
returns multiplesh

So are we willing to field questions from people trying to use

TEMP

tables and trying TEMP and not TEMPORARY. I realize the

restriction on

a field called TEMP, but we don't get those very often. How

many people

are going to guess TEMP and not TEMPORARY?

Well, that's one reason to stick to a published standard. No

guessing

required since one could look it up ;)

If you survive reading the standard. :-)

If you can even *find* the standard :->

(is there an on-line source - all links I've ever seen 404ed...?)

From bouncefilter Wed Mar 1 05:19:34 2000
Received: from ara.zf.jcu.cz (zakkr@ara.zf.jcu.cz [160.217.161.4])
by hub.org (8.9.3/8.9.3) with ESMTP id FAA01731
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 05:19:07 -0500 (EST)
(envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
by ara.zf.jcu.cz (8.9.3/8.9.3/Debian/GNU) with SMTP id LAA01710;
Wed, 1 Mar 2000 11:14:12 +0100
Date: Wed, 1 Mar 2000 11:14:12 +0100 (CET)
From: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
To: Peter Eisentraut <peter_e@gmx.net>
cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: [PATCHES] NO-CREATE-TABLE and NO-LOCK-TABLE
In-Reply-To: <Pine.GSO.4.02A.10002291738410.18993-100000@Rama.DoCS.UU.SE>
Message-ID: <Pine.LNX.3.96.1000301103330.29986B-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 29 Feb 2000, Peter Eisentraut wrote:

On Tue, 29 Feb 2000, Karel Zak - Zakkr wrote:

My acl idea:

reloid | user_insert | group_insert | user_delete ..........etc
---------------------------------------------------------
12345 | {"karel", "peter"} | {"group1"} | {"karel"} ..........etc

This still has arrays. (shudder) Try getting the information 'Does Peter
have access to x?' out of that. I was thinking along the lines of

As I say: we can use oid or string with oids instead array.

Example

reloid | user_insert | group_insert |
--------------------------------------
12345 | "1111,2222" | "545454" |

.. parse these strings a easy and 'Does Peter have access to x?' is realy
simple.

create table pg_privilege/pg_acl/? (
objoid oid, -- not only reloid, but types, functions, etc.

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
yes, yes, yes ! (the oracle allow grant priv. to
25(!) different features)

userid int,

If I understend you, you want save to one line information about one
user only (?), if yes this table will huge (sum(object) * sum(users)),
but probably fastly (because it not needs parse any array or string).
Hmm, perhaps it is not bad idea. What say the other?

privilege char, -- maybe 'U' update, 'I' insert, etc.

I really not sure if is good still create this monolithic string,
why not use one (bool) column for update one for insert ..etc?
It is fastly and easy (a string needs parse, etc).

grant_option bool
)

It is goods if you agree with separate acl table :-)

To be extended to cover column access as well. (Might have to be yet
another table.) Mathematically, this will be slower (especially since you
can't use SysCache on composite keys(???)) but similar schemas are
employed throughout by triggers etc.

Yes, a speed will problem, it must be cached (in an separate acl cache?),
or after (connection) start create a temp table with acl for a current user
and with relevant information only.

Karel

From bouncefilter Wed Mar 1 05:38:38 2000
Received: from ara.zf.jcu.cz (zakkr@ara.zf.jcu.cz [160.217.161.4])
by hub.org (8.9.3/8.9.3) with ESMTP id FAA02890
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 05:38:19 -0500 (EST)
(envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
by ara.zf.jcu.cz (8.9.3/8.9.3/Debian/GNU) with SMTP id LAA04232;
Wed, 1 Mar 2000 11:38:09 +0100
Date: Wed, 1 Mar 2000 11:38:08 +0100 (CET)
From: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
To: Hiroshi Inoue <Inoue@tpf.co.jp>
cc: The Hermit Hacker <scrappy@hub.org>, pgsql-hackers@postgreSQL.org
Subject: RE: [HACKERS] Bug report for 7.0beta1 in 'CREATE FUNCTION...'
In-Reply-To: <000601bf8322$adff9c40$2801007e@tpf.co.jp>
Message-ID: <Pine.LNX.3.96.1000301112834.29986C-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Wed, 1 Mar 2000, Hiroshi Inoue wrote:

-----Original Message-----
From: owner-pgsql-hackers@postgresql.org
[mailto:owner-pgsql-hackers@postgresql.org]On Behalf Of The Hermit
Hacker

Can someone look into this, and followup with Don? :)

Currently utility commands aren't executable in PL/pgSQL.
In short,it's due the lack of implementation of copyObject()
for UtilityStatements.
However,there's another essential problem.

Hmm, I see that copyObject() and freeObject() is really problematic
routines.

PL/pgSQL caches prepared plans for fucntions at their
first execution time. Though many oids/numbers ... exist
in the cached plans,they are changed by DML statements
and cached plans would become invalid. Currently once
a plan is cached,it stays in TopMemoryContext forever
and would never be removed/changed.

.. another TopMemoryContext feeder :-) The solution is
context-per-plan cache and small change in SPI (SPI_freeplan..).

I believe that it (SPI) will fixed in any next release.

Karel

From bouncefilter Wed Mar 1 07:21:36 2000
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
by hub.org (8.9.3/8.9.3) with ESMTP id HAA07172
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 07:20:59 -0500 (EST)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (sfcabop1.nettuno.it [193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 4.1) with ESMTP id NAA22794;
Wed, 1 Mar 2000 13:19:29 +0100 (MET)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 12Q95F-00025z-00; Wed, 1 Mar 2000 13:22:33 +0000
Message-ID: <38BD0B7F.A581157@sferacarta.com>
Date: Wed, 01 Mar 2000 13:22:23 +0100
From: Jose Soares <jose@sferacarta.com>
Organization: Sfera Carta
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] having and union in v7beta
References: <38BA829F.C8BA2978@sferacarta.com> <25405.951750847@sss.pgh.pa.us>
<38BA998A.258E068C@sferacarta.com> <27055.951783375@sss.pgh.pa.us>
<38BB84B3.1209A0C7@sferacarta.com> <239.951848861@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

Jose Soares <jose@sferacarta.com> writes:

But it's tough to believe that that accounts for a 3-to-4x
slowdown of this query; certainly I don't see much performance
difference on the datatypes I tried. What datatypes are your fields,
anyway?

6.5 takes 0.463s
7.0 takes 1.640s
the field type is CHAR(4)

Hmm. I see no more than a few percent difference between 6.5 and
current. There's something peculiar going on on your system.

Current code would ultimately invoke strncmp() on the two char fields,
whereas 6.5 used memcmp(). Is it possible that strncmp() is a huge
performance dog on your platform? I assume you are running in a
non-ASCII locale, which might reduce strncmp's performance, but still...

A quick-and-dirty way for you to check this would be to change the
strncmp call to call memcmp (just the one-word change should work)
in bpchareq() in src/backend/utils/adt/varchar.c, and see if that
changes the performance of this query materially.

regards, tom lane

tests with strncmp:
^^^^^^^^^^^^^^^^^^^
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.685s
user 0m0.190s
sys 0m0.050s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.681s
user 0m0.200s
sys 0m0.060s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.680s
user 0m0.140s
sys 0m0.020s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.695s
user 0m0.220s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.681s
user 0m0.150s
sys 0m0.020s
=========================================
tests with memcmp:
^^^^^^^^^^^^^^^^^^
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.714s
user 0m0.220s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.696s
user 0m0.190s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.702s
user 0m0.220s
sys 0m0.010s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.693s
user 0m0.190s
sys 0m0.020s
$ time psql hygea1 -c 'select * from comuni union select * from comuni' >
/dev/n
real 0m1.692s
user 0m0.180s
sys 0m0.030s
======================================

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

From bouncefilter Wed Mar 1 08:24:37 2000
Received: from bologna.nettuno.it (bologna.nettuno.it [193.43.2.1])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA15449
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 08:24:24 -0500 (EST)
(envelope-from jose@sferacarta.com)
Received: from proxy.sferacarta.com (sfcabop1.nettuno.it [193.207.10.213])
by bologna.nettuno.it (8.9.3/8.9.3/NETTuno 4.1) with ESMTP id OAA06025;
Wed, 1 Mar 2000 14:23:04 +0100 (MET)
Received: from rosso.sferacarta.com (sferacarta.com) [10.20.30.5]
by proxy.sferacarta.com with esmtp (Exim 2.05 #1 (Debian))
id 12QA36-0002An-00; Wed, 1 Mar 2000 14:24:24 +0000
Message-ID: <38BD19FE.D02C383B@sferacarta.com>
Date: Wed, 01 Mar 2000 14:24:14 +0100
From: Jose Soares <jose@sferacarta.com>
Organization: Sfera Carta
X-Mailer: Mozilla 4.6 [en] (WinNT; I)
X-Accept-Language: en
MIME-Version: 1.0
To: Tom Lane <tgl@sss.pgh.pa.us>
CC: hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] having and union in v7beta
References: <38BA829F.C8BA2978@sferacarta.com> <25405.951750847@sss.pgh.pa.us>
<38BB7E2F.C481BBC3@sferacarta.com> <514.951853793@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Tom Lane wrote:

Jose Soares <jose@sferacarta.com> writes:

I tried the following query :

select * from comuni where nome in (
select nome from comuni group by nome having 1 < count(nome)
);

on the above table populated with 8342 rows, PostgreSQL begins searching
and I wait for hours without any result.

I'd expect that to be pretty slow, since it's going to execute the inner
select for every tuple examined by the outer select. Shouldn't be any
worse than 6.5 though. IN (sub-SELECT) has always been slow.

In v7.0 this query takes more than 50min to execute, it doesn't work on
v6.5...

select * from comuni where nome in (
select nome from comuni group by nome having count(nome) > 1
);

real 50m25.033s
user 0m0.010s
sys 0m0.000s

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

From bouncefilter Wed Mar 1 08:35:41 2000
Received: from server1.gba.gov.ar (IDENT:root@server1.gba.gov.ar
[170.155.1.4])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA16166
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 08:35:20 -0500 (EST)
(envelope-from sak@tribctas.gba.gov.ar)
Received: from tribctas.gba.gov.ar (IDENT:root@server.tribctas.gba.gov.ar
[10.42.1.1])
by server1.gba.gov.ar (8.9.3/8.8.7) with ESMTP id KAA21280
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:31:23 -0300
Received: from sergio (Sergio.tribctas.gba.gov.ar [10.42.1.64])
by tribctas.gba.gov.ar (8.9.3/8.8.7) with SMTP id KAA01653
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:37:26 -0300
From: "Sergio A. Kessler" <sak@tribctas.gba.gov.ar>
Message-Id: <SAK.2000.03.01.flqhpgbf@sergio>
In-Reply-To: <38BD19FE.D02C383B@sferacarta.com>
Date: Wed, 1 Mar 2000 10:38:40 -0300
X-Priority: 3
X-Mailer: Correo Fï¿½cil
To: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Subject: rpms
Content-Type: Text/Plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8Bit

Lamars,

it shouldn't be better to rename the package postgresql-xxx.rpm
to postgresql-libs-xxx.rpm ??

actually is quite confusing, because at first look it seems that
this package is the real thing (then you discover that is not,
that the package you really want is postgresql-server).

Sergio

From bouncefilter Wed Mar 1 08:48:37 2000
Received: from localhost (IDENT:root@hectic-3.jpl.nasa.gov [128.149.68.205])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA17145
for <hackers@postgresql.org>; Wed, 1 Mar 2000 08:48:33 -0500 (EST)
(envelope-from lockhart@alumni.caltech.edu)
Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1])
by localhost (8.8.7/8.8.7) with ESMTP id NAA13959;
Wed, 1 Mar 2000 13:56:04 GMT
Sender: lockhart@hub.org
Message-ID: <38BD2174.8870B5D1@alumni.caltech.edu>
Date: Wed, 01 Mar 2000 13:56:04 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "T.Lingk" <std7107@et.FH-Osnabrueck.DE>,
Postgres Hackers List <hackers@postgresql.org>
Subject: Re: Bugs in Postgres
References: <38BD173F.E30E66A6@et.fh-osnabrueck.de>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Thanks for the information Thorsten.

I'm forwarding this message to the "hackers" list for further comment.
afaik, the IPC issue on Solaris is a FAQ, and the fix requires
configuring IPC in the Sun kernel with sufficient size, or starting
Postgres with smaller buffers (see the docs on starting the
postmaster).

The plpgsql function problem sounds like an issue with an index on a
system table, and may have been fixed for the upcoming release, but I
don't recall anything specifically. Perhaps others will recall more
detail.

Regards.

- Thomas

I encountered some problems during the use of of Postgres 6.5.3.
I'm a student in Germany and I'm currently working on a project
together with two other students. In our project we use Postgres
as a database server.

During our work we encountered two problems:

- We removed an existing plpgsql-function from our database.
After trying to re-create it, the postmaster printed a message
like: "btree: unable to add item to the page". The only solution
was to delete the entire database and to recreate it.

- Postgres 6.5.3 has problems on sparc-sun-solaris2.7 systems.
Compilation is successful and databases can be initialized, but
starting the postmaster produces the following error message:

IpcMemoryCreate: shmget failed (Invalid argument) key=7107001,
size=1073152,
permission=600
FATAL 1: ShmemCreate: cannot create region

This message does not occur under Linux.

Let me know if you need further information to reproduce the bugs.

yours,
Thorsten Lingk

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

From bouncefilter Wed Mar 1 08:50:40 2000
Received: from localhost (IDENT:root@hectic-3.jpl.nasa.gov [128.149.68.205])
by hub.org (8.9.3/8.9.3) with ESMTP id IAA17287
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 08:49:52 -0500 (EST)
(envelope-from lockhart@alumni.caltech.edu)
Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1])
by localhost (8.8.7/8.8.7) with ESMTP id OAA13974;
Wed, 1 Mar 2000 14:00:27 GMT
Sender: lockhart@hub.org
Message-ID: <38BD227B.C159311E@alumni.caltech.edu>
Date: Wed, 01 Mar 2000 14:00:27 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "Sergio A. Kessler" <sak@tribctas.gba.gov.ar>
CC: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] rpms
References: <SAK.2000.03.01.flqhpgbf@sergio>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

it shouldn't be better to rename the package postgresql-xxx.rpm
to postgresql-libs-xxx.rpm ??
actually is quite confusing, because at first look it seems that
this package is the real thing (then you discover that is not,
that the package you really want is postgresql-server).

Actually, what you suggest was how the naming was in earlier RPMs.
However, I changed the naming convention since the fundamental
installation should require client-side code only, to talk to a remote
server. In cases where Postgres is deployed on many machines, only one
or a few will have the server installed, while all machines will get
the client packages.

Regards.

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

From bouncefilter Wed Mar 1 10:03:40 2000
Received: from smtp7.atl.mindspring.net (smtp7.atl.mindspring.net
[207.69.128.51]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA25181
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:03:00 -0500 (EST)
(envelope-from mhh@mindspring.com)
Received: from mars (user-2inik6s.dialup.mindspring.com [165.121.80.220])
by smtp7.atl.mindspring.net (8.9.3/8.8.5) with ESMTP id KAA16519
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:02:58 -0500 (EST)
Received: from mhh by mars with local (Exim 3.12 #1 (Debian))
id 12Q9wk-00082Q-00
for <pgsql-hackers@postgresql.org>; Wed, 01 Mar 2000 09:17:50 -0500
Date: Wed, 1 Mar 2000 09:17:49 -0500
To: pgsql-hackers@postgresql.org
Subject: patch for plperl Makefile.PL
Message-ID: <20000301091749.A30893@mindspring.com>
Mime-Version: 1.0
Content-Type: multipart/mixed; boundary="tThc/1wpZn/ma/RB"
Content-Disposition: inline
User-Agent: Mutt/1.1.5i
From: Mark Hollomon <mhh@mindspring.com>

--tThc/1wpZn/ma/RB
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

Here is a new MAkefile.PL for plperl.

It not only solves the problem for the missing symbol,
but there is also now a 'make install'.

By the way, earlier, I sent a patch to add plperl
to createlang, droplang. Those patches haven't made
it into the source tree yet. Do I need to resend them?

--
Mark Hollomon
mhh@mindspring.com

--tThc/1wpZn/ma/RB
Content-Type: application/perl
Content-Disposition: attachment; filename="Makefile.PL"

use ExtUtils::MakeMaker;
use ExtUtils::Embed;
use DynaLoader;
use Config;

#
# get the location of the Opcode module
#
my $opcode = '';
{

$modname = 'Opcode';

my $dir;
foreach (@INC) {
if (-d "$_/auto/$modname") {
$dir = "$_/auto/$modname";
last;
}
}

if (defined $dir) {
$opcode = DynaLoader::dl_findfile("-L$dir", $modname);
}

}

WriteMakefile( 'NAME' => 'plperl',
dynamic_lib => { 'OTHERLDFLAGS' => "$opcode -L\$(LIBPQDIR) -lpq @{[ldopts()]}" } ,
INC => '-I$(LIBPQDIR) -I$(SRCDIR)/include -I$(SRCDIR)/backend',
XS => { 'SPI.xs' => 'SPI.c' },
OBJECT => 'plperl.o eloglvl.o SPI.o',
);

sub MY::post_initialize {

q[
SRCDIR=../../../src
include $(SRCDIR)/Makefile.global
];

}

sub MY::install {

q[
install :: all
cp $(INST_DYNAMIC) $(LIBDIR)
];

}

--tThc/1wpZn/ma/RB--

From bouncefilter Wed Mar 1 09:22:47 2000
Received: from server2.gba.gov.ar (IDENT:root@server2.gba.gov.ar
[170.155.1.6])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA21118
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 09:22:09 -0500 (EST)
(envelope-from sak@tribctas.gba.gov.ar)
Received: from tribctas.gba.gov.ar (IDENT:root@server.tribctas.gba.gov.ar
[10.42.1.1])
by server2.gba.gov.ar (8.9.3/8.8.7) with ESMTP id LAA26462
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 11:20:10 -0300
Received: from sergio (Sergio.tribctas.gba.gov.ar [10.42.1.64])
by tribctas.gba.gov.ar (8.9.3/8.8.7) with SMTP id LAA01973
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 11:24:12 -0300
From: "Sergio A. Kessler" <sak@tribctas.gba.gov.ar>
Message-Id: <SAK.2000.03.01.niggpcbc@sergio>
In-Reply-To: <38BD227B.C159311E@alumni.caltech.edu>
Date: Wed, 1 Mar 2000 11:25:26 -0300
X-Priority: 3
X-Mailer: Correo Fï¿½cil
To: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Subject: Re: [HACKERS] rpms
Content-Type: Text/Plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8Bit

Thomas Lockhart <lockhart@alumni.caltech.edu> el dï¿½a Wed, 01 Mar 2000
14:00:27 +0000, escribiï¿½:

it shouldn't be better to rename the package postgresql-xxx.rpm
to postgresql-libs-xxx.rpm ??
actually is quite confusing, because at first look it seems that
this package is the real thing (then you discover that is not,
that the package you really want is postgresql-server).

Actually, what you suggest was how the naming was in earlier RPMs.
However, I changed the naming convention since the fundamental
installation should require client-side code only, to talk to a remote
server. In cases where Postgres is deployed on many machines, only one
or a few will have the server installed, while all machines will get
the client packages.

hi thomas; yup, I agree that the client side is more deployed,
and that the packages should be split into server for one side
and libs in other side.
and I don't have problems with this, I just have problems
with the =name= of the package that contain the libs.

it should be clear that the package contains ONLY the client side,
a package just named "postgresql" appear like it contains PostgreSql,
when this, in fact, is not true.

just like postgresql-server.xxx.rpm, this package is well named IMO
(is pretty clear that it contains the PostgreSql server)

what is more clear/descriptive to you for a package that ONLY
contains PostgreSql libraries:

a) postgresql-libs.xxx.rpm (or maybe postgresql-clientlibs.xxx.rpm ?)
b) postgresql.xxx.rpm

Sergio

From bouncefilter Wed Mar 1 09:36:39 2000
Received: from server1.gba.gov.ar (IDENT:root@server1.gba.gov.ar
[170.155.1.4])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA22400
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 09:36:20 -0500 (EST)
(envelope-from sak@tribctas.gba.gov.ar)
Received: from tribctas.gba.gov.ar (IDENT:root@server.tribctas.gba.gov.ar
[10.42.1.1])
by server1.gba.gov.ar (8.9.3/8.8.7) with ESMTP id LAA23599
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 11:32:26 -0300
Received: from sergio (Sergio.tribctas.gba.gov.ar [10.42.1.64])
by tribctas.gba.gov.ar (8.9.3/8.8.7) with SMTP id LAA02076
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 11:38:32 -0300
From: "Sergio A. Kessler" <sak@tribctas.gba.gov.ar>
Message-Id: <SAK.2000.03.01.cdldgtsm@sergio>
In-Reply-To: <SAK.2000.03.01.niggpcbc@sergio>
Date: Wed, 1 Mar 2000 11:39:46 -0300
X-Priority: 3
X-Mailer: Correo Fï¿½cil
To: pgsql-hackers@postgresql.org
MIME-Version: 1.0
Subject: Re: [HACKERS] rpms
Content-Type: Text/Plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8Bit

"Sergio A. Kessler" <sak@tribctas.gba.gov.ar> el dï¿½a Wed, 1 Mar 2000
11:25:26 -0300, escribiï¿½:

what is more clear/descriptive to you for a package that ONLY
contains PostgreSql libraries:

a) postgresql-libs.xxx.rpm (or maybe postgresql-clientlibs.xxx.rpm ?)
b) postgresql.xxx.rpm

??

and another argument: :)

suposse a newbie looking for a RDBM is told to install PostgreSql,
what you think will be the first package he will try ?

Sergio

From bouncefilter Wed Mar 1 09:47:38 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA23408
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 09:47:27 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Svan.DoCS.UU.SE (e99re41@Svan.DoCS.UU.SE [130.238.9.160])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id PAA26411;
Wed, 1 Mar 2000 15:47:12 +0100 (MET)
Received: from localhost (e99re41@localhost) by Svan.DoCS.UU.SE
(8.6.12/8.6.12) with ESMTP id PAA25571;
Wed, 1 Mar 2000 15:47:11 +0100
X-Authentication-Warning: Svan.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Wed, 1 Mar 2000 15:47:11 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>,
pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Beta for 4:30AST ... ?
In-Reply-To: <38BCBDC5.AE7EA486@alumni.caltech.edu>
Message-ID: <Pine.GSO.4.02A.10003011540520.25449-100000@Svan.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id JAA23422

On Wed, 1 Mar 2000, Thomas Lockhart wrote:

It is unlikely that we can transparently parse two-word types in
gram.y without explicit support for it. Just adding IDENT IDENT to
simple types leads to a shift/reduce conflict.

I am not saying that we should support two token types in general. Only
the SQL types. We already do that anyway, like (kind of)

Type: CHARACTER VARYING { $$ = "varchar"; }
| etc.

All I'm saying is that we add

| BIT VARYING { $$ = "bit varying"; }

No problem so far, right? Especially, if this is dumped out, then it
becomes bit varying without any extra effort.

The only problem is that with the current syntax the bootstrap scanner
cannot insert fields that contain spaces. Simple fix there, and we're
done.

To be clear again: I am not vaguely suggesting that we support any
multi-token types. I am just saying that we shouldn't introduce any new
and unnecessary external/internal type discrepancies just because the
bootstrap scanner is stupid.

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

From bouncefilter Wed Mar 1 09:49:39 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id JAA23544
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 09:48:43 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Svan.DoCS.UU.SE (e99re41@Svan.DoCS.UU.SE [130.238.9.160])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id PAA26520;
Wed, 1 Mar 2000 15:48:39 +0100 (MET)
Received: from localhost (e99re41@localhost) by Svan.DoCS.UU.SE
(8.6.12/8.6.12) with ESMTP id PAA25578;
Wed, 1 Mar 2000 15:48:38 +0100
X-Authentication-Warning: Svan.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Wed, 1 Mar 2000 15:48:38 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
cc: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Beta for 4:30AST ... ?
In-Reply-To: <38BCBDC5.AE7EA486@alumni.caltech.edu>
Message-ID: <Pine.GSO.4.02A.10003011547240.25449-100000@Svan.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id JAA23587

On Wed, 1 Mar 2000, Thomas Lockhart wrote:

TIME WITH
TIMEZONE is currently transparently swallowed to become equivalent to
TIME, for reasons spelled out in the docs. I've toyed with the idea of
implementing the SQL92 version of it, but it is *so* useless and brain
damaged (cf Date et al) that I (at least so far) cannot bring myself
to do so. But if and when, it might be ztime internally.

I've read the documentation and SQL92 and I can't see anything wrong with
it. Care to enlighten me?

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

From bouncefilter Wed Mar 1 10:03:40 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA25236
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:03:16 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Svan.DoCS.UU.SE (e99re41@Svan.DoCS.UU.SE [130.238.9.160])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id QAA27285;
Wed, 1 Mar 2000 16:03:12 +0100 (MET)
Received: from localhost (e99re41@localhost) by Svan.DoCS.UU.SE
(8.6.12/8.6.12) with ESMTP id QAA25709;
Wed, 1 Mar 2000 16:03:11 +0100
X-Authentication-Warning: Svan.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Wed, 1 Mar 2000 16:03:11 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: [PATCHES] NO-CREATE-TABLE and NO-LOCK-TABLE
In-Reply-To: <Pine.LNX.3.96.1000301103330.29986B-100000@ara.zf.jcu.cz>
Message-ID: <Pine.GSO.4.02A.10003011555480.25449-100000@Svan.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id KAA25307

On Wed, 1 Mar 2000, Karel Zak - Zakkr wrote:

Example

reloid | user_insert | group_insert |
--------------------------------------
12345 | "1111,2222" | "545454" |

.. parse these strings a easy and 'Does Peter have access to x?' is realy
simple.

privilege char, -- maybe 'U' update, 'I' insert, etc.

I really not sure if is good still create this monolithic string,
why not use one (bool) column for update one for insert ..etc?
It is fastly and easy (a string needs parse, etc).

Space. A char column takes one byte (when using the internal char1 type),
five or six bool columns take five or six bytes plus all the overhead.
Also you get into the problem where certain flag combinations are not even
valid.

Yes, a speed will problem, it must be cached (in an separate acl cache?),
or after (connection) start create a temp table with acl for a current user
and with relevant information only.

Steal the code on how triggers are looked up. It does an index scan. This
table will be huge (on average probably O(#tables * #users)) but it has
the advantage that it is a direct mapping from what SQL calls a "privilege
descriptor", so implementation could be easier.

I would like to take a look at SQL3 first, because they define some more
privilege stuff which we could take into account (ROLES, for example).

By the way: Regarding your original patch that disallowed LOCK to users, I
looked it up in the source and it turns out that in order to lock a table
you need write access to it. Isn't that sufficient?

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

From bouncefilter Wed Mar 1 10:04:40 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA25456
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:04:25 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Svan.DoCS.UU.SE (e99re41@Svan.DoCS.UU.SE [130.238.9.160])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id QAA27317
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 16:04:19 +0100 (MET)
Received: from localhost (e99re41@localhost) by Svan.DoCS.UU.SE
(8.6.12/8.6.12) with ESMTP id QAA25730 for
<pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 16:04:19 +0100
X-Authentication-Warning: Svan.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Wed, 1 Mar 2000 16:04:19 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: pgsql-hackers@postgresql.org
Subject: Where's the SQL3 spec?
Message-ID: <Pine.GSO.4.02A.10003011603150.25449-100000@Svan.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id KAA25474

Can somebody point me to a place where I can get one of those public
drafts of SQL3? I heard DEC has the somewhere, but where?

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

From bouncefilter Wed Mar 1 10:15:37 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA27046
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 10:15:29 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Svan.DoCS.UU.SE (e99re41@Svan.DoCS.UU.SE [130.238.9.160])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id QAA27778;
Wed, 1 Mar 2000 16:15:26 +0100 (MET)
Received: from localhost (e99re41@localhost) by Svan.DoCS.UU.SE
(8.6.12/8.6.12) with ESMTP id QAA25815;
Wed, 1 Mar 2000 16:15:26 +0100
X-Authentication-Warning: Svan.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Wed, 1 Mar 2000 16:15:26 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
Bruce Momjian <pgman@candle.pha.pa.us>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Beta for 4:30AST ... ?
In-Reply-To: <260.951893617@sss.pgh.pa.us>
Message-ID: <Pine.GSO.4.02A.10003011606090.25449-100000@Svan.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id KAA27057

On Wed, 1 Mar 2000, Tom Lane wrote:

Right. I think what Peter is actually suggesting is that BIT VARYING
(which must be special-cased in gram.y) could be equivalent to
"bit varying" (as a quoted identifier, that works already in most
places, and arguably should work everywhere). There's a certain amount
of intellectual cleanliness in that.

{Grin} That's exactly what I wanted.

OTOH, it's not apparent that it's really any *better* than `varbit' or
your choice of other space-free internal names.

It's better because then you don't need any special casing when you
provide the type back to the client. And it's better because you don't
need to remember that "foo" is really "bar" internally. And it's better
because it wouldn't disallow users from defining "varbit" themselves with
the non-obvious error message that it already exists. (Okay, the last is a
weak reason, but it is one.) Finally, it's better because it already
works, with only a minor change in the bootstrap scanner necessary.

If SQL92 were a moving target then I'd be concerned about having to
track the special cases in a lot of bits of code ... but it's not
a moving target.

But PostgreSQL is a moving target in all regards. Where would you want to
do the endless internal/external type conversions on the way to the
client. In pg_dump? In psql? In libpq? In the server communications code?
Make a view around pg_type? How about nowhere and we just do the above?

Special cases suck. ;)

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

From bouncefilter Wed Mar 1 10:35:37 2000
Received: from gwineta.repas.de (gwineta.repas.de [193.101.49.1])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA29525
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:35:27 -0500 (EST)
(envelope-from kardos@repas-aeg.de)
Received: (from smap@localhost) by gwineta.repas.de (8.8.8/8.8.8) id QAA26649
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 16:35:46 +0100
Received: from dragon.dr.repas.de(172.30.48.206) by gwineta.repas.de via smap
(V2.1) id xma026641; Wed, 1 Mar 00 16:35:23 +0100
Received: from kardos.dr.repas.de ([172.30.48.153])
by dragon.dr.repas.de (UCX V4.2-21C, OpenVMS V6.2 Alpha);
Wed, 1 Mar 2000 16:35:40 +0200
Message-ID: <0b6401bf8393$c2ce3bb0$99301eac@Dr.repas.de>
From: "Kardos, Dr. Andreas" <kardos@repas-aeg.de>
To: "Peter Eisentraut" <peter_e@gmx.net>
Cc: <pgsql-hackers@postgresql.org>
References: <Pine.GSO.4.02A.10003011603150.25449-100000@Svan.DoCS.UU.SE>
Subject: Re: [HACKERS] Where's the SQL3 spec?
Date: Wed, 1 Mar 2000 16:35:22 +0100
Organization: repas AEG Automation GmbH
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 8bit
X-Priority: 3
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook Express 5.00.2314.1300
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300

http://ftp.digital.com/pub/standards/sql/

from August, 1994.

Andreas Kardos

-----Ursprï¿½ngliche Nachricht-----
Von: Peter Eisentraut <e99re41@DoCS.UU.SE>
An: <pgsql-hackers@postgreSQL.org>
Gesendet: Mittwoch, 1. Mï¿½rz 2000 16:04
Betreff: [HACKERS] Where's the SQL3 spec?

Can somebody point me to a place where I can get one of those public
drafts of SQL3? I heard DEC has the somewhere, but where?

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

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

From bouncefilter Wed Mar 1 10:40:38 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 KAA30275
for <hackers@postgreSQL.org>; Wed, 1 Mar 2000 10:40: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 KAA01074;
Wed, 1 Mar 2000 10:36:14 -0500 (EST)
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
cc: "T.Lingk" <std7107@et.FH-Osnabrueck.DE>,
Postgres Hackers List <hackers@postgreSQL.org>
Subject: Re: [HACKERS] Re: Bugs in Postgres
In-reply-to: <38BD2174.8870B5D1@alumni.caltech.edu>
References: <38BD173F.E30E66A6@et.fh-osnabrueck.de>
<38BD2174.8870B5D1@alumni.caltech.edu>
Comments: In-reply-to Thomas Lockhart <lockhart@alumni.caltech.edu>
message dated "Wed, 01 Mar 2000 13:56:04 +0000"
Date: Wed, 01 Mar 2000 10:36:14 -0500
Message-ID: <1071.951924974@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Thomas Lockhart <lockhart@alumni.caltech.edu> writes:

The plpgsql function problem sounds like an issue with an index on a
system table, and may have been fixed for the upcoming release, but I
don't recall anything specifically.

Yes, that sure sounds like an index-tuple-size overflow in the index
that 6.5.* and prior versions kept on pg_proc's prosrc field. 7.0
doesn't keep such an index, so it's proof against this particular limit.

IIRC, the maximum safe length of a procedure definition in <=6.5 is
2700 bytes. Sometimes you will get away with more, sometimes not,
depending on what winds up on the same index page with your procedure...

regards, tom lane

From bouncefilter Wed Mar 1 10:47:38 2000
Received: from ara.zf.jcu.cz (zakkr@ara.zf.jcu.cz [160.217.161.4])
by hub.org (8.9.3/8.9.3) with ESMTP id KAA31279
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 10:47:09 -0500 (EST)
(envelope-from zakkr@zf.jcu.cz)
Received: from localhost (zakkr@localhost)
by ara.zf.jcu.cz (8.9.3/8.9.3/Debian/GNU) with SMTP id QAA29890;
Wed, 1 Mar 2000 16:44:49 +0100
Date: Wed, 1 Mar 2000 16:44:49 +0100 (CET)
From: Karel Zak - Zakkr <zakkr@zf.jcu.cz>
To: Peter Eisentraut <peter_e@gmx.net>
cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
PostgreSQL Development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: [PATCHES] NO-CREATE-TABLE and NO-LOCK-TABLE
In-Reply-To: <Pine.GSO.4.02A.10003011555480.25449-100000@Svan.DoCS.UU.SE>
Message-ID: <Pine.LNX.3.96.1000301161406.6297D-100000@ara.zf.jcu.cz>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

I would like to take a look at SQL3 first, because they define some more
privilege stuff which we could take into account (ROLES, for example).

Yes. Just today I look at Oracle's documentation for ROLEs, PROFILEs
... my idea is prepare acl/account code for this freatures too. What?

IMHO this discussion good adept for any new-acl&accout project. Agree?

By the way: Regarding your original patch that disallowed LOCK to users, I

... and I see your web page, you listen good music :-)

looked it up in the source and it turns out that in order to lock a table
you need write access to it. Isn't that sufficient?

You mean this original PG's code (?):

if (lockstmt->mode == AccessShareLock)
aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL
else
aclresult = pg_aclcheck(lockstmt->relname, GetPgUserName(), ACL

if (aclresult != ACLCHECK_OK)
elog(ERROR, "LOCK TABLE: permission denied");

Yes. The my patch create a lock-permission level over this current code.
It is global setting and example for all non-AccessShareLocks you must have
pg_shadow->locktable privilege and 'write' privilage for table.

It is because I have users which needs update/insert access to tables, but
I not want allow a lock command for these users.

Karel

From bouncefilter Wed Mar 1 10:52:38 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 KAA38688
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 10:52: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 KAA01216;
Wed, 1 Mar 2000 10:52:07 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
Bruce Momjian <pgman@candle.pha.pa.us>, pgsql-hackers@postgreSQL.org
Subject: BIT/BIT VARYING names (was Re: [HACKERS] Beta for 4:30AST)
In-reply-to: <Pine.GSO.4.02A.10003011540520.25449-100000@Svan.DoCS.UU.SE>
References: <Pine.GSO.4.02A.10003011540520.25449-100000@Svan.DoCS.UU.SE>
Comments: In-reply-to Peter Eisentraut <e99re41@DoCS.UU.SE>
message dated "Wed, 01 Mar 2000 15:47:11 +0100"
Date: Wed, 01 Mar 2000 10:52:07 -0500
Message-ID: <1213.951925927@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

All I'm saying is that we add

| BIT VARYING { $$ = "bit varying"; }

No problem so far, right? Especially, if this is dumped out, then it
becomes bit varying without any extra effort.

Well, no, it becomes "bit varying", *with* quotes, if the dumper is
not broken. (Unless we special-case the dumper to know that this
particular typename doesn't need to be quoted despite its embedded
space --- but that's what you hoped to avoid.) So there's no automatic
way of producing a completely SQL-compliant dump for this type name,
and that removes what would otherwise be (IMHO) the strongest argument
for making the internal name be "bit varying" and not "varbit" or
whatever.

A much more significant problem for this particular datatype is that it
requires special syntax regardless, namely a length spec like the ones
for char and varchar:

<bit string type> ::=
BIT [ <left paren> <length> <right paren> ]
| BIT VARYING <left paren> <length> <right paren>

Currently, char and numeric (the existing types that need length
specifications) have to be special-cased everywhere in order to
parse or append the length info. I foresee the same will be needed
for bit and bit varying. If you can find a way to avoid
that special-case logic, I'll get a lot more excited about not
having to treat "bit varying" as a special-case name.

regards, tom lane

From bouncefilter Wed Mar 1 11:02:38 2000
Received: from gate.plzen-city.cz (gate.plzen-city.cz [194.212.191.10])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA40074
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 11:01:48 -0500 (EST)
(envelope-from horak@sit.plzen-city.cz)
Received: (from mail@localhost) by gate.plzen-city.cz (8.9.3/8.9.1) id
RAA18726
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 17:01:41 +0100
Received: from EXCHANGE.mmp.plzen-city.cz(s5srvr2.mmp.plzen-city.cz
192.168.4.22) by gate.plzen-city.cz via smap (v1.3-ps12tp)
id sma018723; Wed Mar 1 17:01:21 2000
Received: by exchange.mmp.plzen-city.cz with Internet Mail Service
(5.5.2650.21) id <F5JRSVM1>; Wed, 1 Mar 2000 17:01:19 +0100
Message-ID:
<E33858CBACEDD3118C6700A024B3BF909955CC@exchange.mmp.plzen-city.cz>
From: =?iso-8859-2?Q?Hor=E1k_Daniel?= <horak@sit.plzen-city.cz>
To: "'pgsql-hackers@postgreSQL.org'" <pgsql-hackers@postgreSQL.org>
Subject: PostgreSQL on Solaris/SPARC with gcc
Date: Wed, 1 Mar 2000 17:01:10 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2650.21)
Content-Type: text/plain;
charset="iso-8859-2"

Hello,

I have a report from compiling PostgreSQL 6.5.3 on Solaris/SPARC with gcc/ld
(it was not run by me, I was ask to help to solve this problem). Everything
goes OK (with template solaris_sparc_gcc autodetected when running
configure) but when trying to load plpgsql or other loaded module it woes
with

ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: ld.so.1:
/usr/local/pgsql/bin/postmaster: fatal: relocation error: file
/usr/local/pgsql/lib/plpgsql.so: symbol CurrentMemoryContext: referenced
symbol not found

it is caused by the fact that the symbol CurrentMemoryContext is not
exported from the main executable (postmaster). The solution is to use
"-export-dynamic" switch when linking postgres executable (it is used on
Linux).

I think it will require a new system specific makefile. The
Makefile.solaris_sparc is done for solaric_sparc_cc template. More details
can be send.

Dan

----------------------------------------------
Daniel Horak
network and system administrator
e-mail: horak@sit.plzen-city.cz
privat e-mail: dan.horak@email.cz ICQ:36448176
----------------------------------------------

From bouncefilter Wed Mar 1 11:14:38 2000
Received: from fep132.fep.ru (mail@fep132.fep.ru [195.230.89.88])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA41680
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 11:14:26 -0500 (EST)
(envelope-from phd@phd.russ.ru)
Received: from localhost [127.0.0.1] (phd)
by fep132.fep.ru with esmtp (Exim 2.05 #1 (Debian))
id 12QBlK-0003Bb-00; Wed, 1 Mar 2000 19:14:10 +0300
Date: Wed, 1 Mar 2000 16:14:10 +0000 (GMT)
From: Oleg Broytmann <phd@phd.russ.ru>
X-Sender: phd@fep132.fep.ru
Reply-To: phd2@earthling.net
To: =?iso-8859-2?Q?Hor=E1k_Daniel?= <horak@sit.plzen-city.cz>
cc: "'pgsql-hackers@postgreSQL.org'" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] PostgreSQL on Solaris/SPARC with gcc
In-Reply-To:
<E33858CBACEDD3118C6700A024B3BF909955CC@exchange.mmp.plzen-city.cz>
Message-ID: <Pine.LNX.4.21.0003011612330.12236-100000@fep132.fep.ru>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=koi8-r
Content-Transfer-Encoding: 8BIT

On Wed, 1 Mar 2000, [iso-8859-2] Horï¿½k Daniel wrote:

I have a report from compiling PostgreSQL 6.5.3 on Solaris/SPARC with gcc/ld
(it was not run by me, I was ask to help to solve this problem). Everything
goes OK (with template solaris_sparc_gcc autodetected when running
configure) but when trying to load plpgsql or other loaded module it woes
with

ERROR: Load of file /usr/local/pgsql/lib/plpgsql.so failed: ld.so.1:
/usr/local/pgsql/bin/postmaster: fatal: relocation error: file
/usr/local/pgsql/lib/plpgsql.so: symbol CurrentMemoryContext: referenced
symbol not found

it is caused by the fact that the symbol CurrentMemoryContext is not
exported from the main executable (postmaster). The solution is to use
"-export-dynamic" switch when linking postgres executable (it is used on
Linux).

I think it will require a new system specific makefile. The
Makefile.solaris_sparc is done for solaric_sparc_cc template. More details
can be send.

I ran many versions of Postgres (6.4.2, 6.5, 6.5.2, 7.0beta1) on Sun
SPARC Solaris 2.5.1, compiled with gcc 2.8.1 and never saw any such
problem...

Oleg.
----
Oleg Broytmann http://members.xoom.com/phd2/ phd2@earthling.net
Programmers don't die, they just GOSUB without RETURN.

From bouncefilter Wed Mar 1 11:25:38 2000
Received: from localhost (IDENT:root@hectic-3.jpl.nasa.gov [128.149.68.205])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA43141
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 11:25:27 -0500 (EST)
(envelope-from lockhart@alumni.caltech.edu)
Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1])
by localhost (8.8.7/8.8.7) with ESMTP id QAA14456;
Wed, 1 Mar 2000 16:33:52 GMT
Sender: lockhart@hub.org
Message-ID: <38BD466F.A5492476@alumni.caltech.edu>
Date: Wed, 01 Mar 2000 16:33:52 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: "Sergio A. Kessler" <sak@tribctas.gba.gov.ar>
CC: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] rpms
References: <SAK.2000.03.01.cdldgtsm@sergio>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

what is more clear/descriptive to you for a package that ONLY
contains PostgreSql libraries:
a) postgresql-libs.xxx.rpm (or maybe postgresql-clientlibs.xxx.rpm ?)
b) postgresql.xxx.rpm

suposse a newbie looking for a RDBM is told to install PostgreSql,
what you think will be the first package he will try ?

Well, the newbie would be best off if he installed every package ;)

Anyway, afaik most RPM distros of a product have one .rpm file which
has the name of the package, and then may have other .rpm files which
have qualifiers, like "-server". So in choosing which .rpm file will
be the base package, it seemed most appropriate that it be the
client-side stuff, as opposed to docs, or server (which btw can't
really be run on its own without the client stuff installed
*somewhere*), or something else.

I appreciate your points, but it isn't clear to me how to eliminate
*all* possibilities for confusion via RPM package names, so chose to
use names which give some appropriate functionality for each package.

Regards.

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

From bouncefilter Wed Mar 1 11:35:38 2000
Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu
[128.42.12.154])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA44547
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 11:35:24 -0500 (EST)
(envelope-from reedstrm@wallace.ece.rice.edu)
Received: by wallace.ece.rice.edu via sendmail from stdin
id <m12QC5f-000LEMC@wallace.ece.rice.edu> (Debian Smail3.2.0.102)
for pgsql-hackers@postgresql.org; Wed, 1 Mar 2000 10:35:11 -0600 (CST)
Date: Wed, 1 Mar 2000 10:35:11 -0600
From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
To: Peter Eisentraut <peter_e@gmx.net>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] Where's the SQL3 spec?
Message-ID: <20000301103511.A15067@rice.edu>
References: <Pine.GSO.4.02A.10003011603150.25449-100000@Svan.DoCS.UU.SE>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <Pine.GSO.4.02A.10003011603150.25449-100000@Svan.DoCS.UU.SE>;
from e99re41@DoCS.UU.SE on Wed, Mar 01, 2000 at 04:04:19PM +0100

On Wed, Mar 01, 2000 at 04:04:19PM +0100, Peter Eisentraut wrote:

Can somebody point me to a place where I can get one of those public
drafts of SQL3? I heard DEC has the somewhere, but where?

Hmm, a quick google search seems to indicate that the various SQL draft
standards have been picked up by the dbperl package, and distributed to
CPAN mirror sites as the directory refinfo inside the module "dbperl". The
home directory seems to be one that may be close (in netspace) to Sweden:

http://src.doc.ic.ac.uk/packages/dbperl/refinfo/

Hmm, there's an interesting Manifesto in there, authored by Darwen and
Date (no copyright date on it, though): Seems they doesn't care for SQL
very much as an implementation of the Relational Model.

Ahh, web search engines are getting good (or is that bad?) enough to
revive a favorite game of mine from childhood: "Things I learned on the
way to looking up other things":

A google search on a bare 'SQL3' turns up this link:

http://www.objs.com/x3h7/fmindex.htm

It's a couple years old, but has some white papers giving interpretations
of the object models from different systems, including SQL3.

The Manifesto I mentioned above also talks about OO (there, cheekly
defined as an abreviation of Other Orthogonal, so the paper talks about RM
Prescriptions and Proscriptions, and OO Prescriptions and Proscriptions)
Specifically, how the Relational and Object Models might (not) interact.

There's also a "state of the standards" page, though it's last modified
date is 1997, and it talks about "upcoming votes" in '97. Still, an
interesting take on the standards setting processes:

http://www.jcc.com/SQLPages/jccs_sql.htm

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

From bouncefilter Wed Mar 1 11:44:38 2000
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA45842
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 11:43:55 -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
LAA08033;
Wed, 1 Mar 2000 11:36:52 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200003011636.LAA08033@candle.pha.pa.us>
Subject: Re: bit types
In-Reply-To: <38BCDDBC.E776E7F8@albourne.com> from Adriaan Joubert at "Mar 1,
2000 11:07:08 am"
To: Adriaan Joubert <a.joubert@albourne.com>
Date: Wed, 1 Mar 2000 11:36:52 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Bruce,

The bit-type that is in contrib is useless as it stands. Those are
only C-routines to implement the functionality, and there are none of
the SQL functions to actually make these usable. This really needs to be
integrated with postgres proper. I don't know how to go about this and
that is why I asked for help. I'm prepared to do whatever SQL function
definitions are needed, do the regression tests etc. Would it be better
to go back to the hackers mailing list to ask for help? Has this missed
7.0 now? If so, we'd better remove the bit-type from contrib.

I clearly dropped the ball on this one. Don't think it can go into 7.0
because it would require catalog changes/initdb. However, I would like
to keep it in contrib and add it as soon as 7.0 finalizes and we move to
7.1.

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

From bouncefilter Wed Mar 1 11:45:39 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 LAA46079
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 11:45: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 LAA08581
for pgsql-hackers@postgreSQL.org; Wed, 1 Mar 2000 11:45:08 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200003011645.LAA08581@candle.pha.pa.us>
Subject: Psql and \H option
To: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
Date: Wed, 1 Mar 2000 11:45:08 -0500 (EST)
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Psql \? does not show \H option to turn on HTML output.

What other options are missing from \? Can someone add them? Peter?

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

From bouncefilter Wed Mar 1 11:41:38 2000
Received: from localhost (IDENT:root@hectic-3.jpl.nasa.gov [128.149.68.205])
by hub.org (8.9.3/8.9.3) with ESMTP id LAA45381
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 11:41:06 -0500 (EST)
(envelope-from lockhart@alumni.caltech.edu)
Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1])
by localhost (8.8.7/8.8.7) with ESMTP id QAA14486;
Wed, 1 Mar 2000 16:49:36 GMT
Sender: lockhart@hub.org
Message-ID: <38BD4A20.897AFF08@alumni.caltech.edu>
Date: Wed, 01 Mar 2000 16:49:36 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Peter Eisentraut <peter_e@gmx.net>
CC: pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Beta for 4:30AST ... ?
References: <Pine.GSO.4.02A.10003011547240.25449-100000@Svan.DoCS.UU.SE>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

I've read the documentation and SQL92 and I can't see anything wrong with
it. Care to enlighten me?

SQL92 "TIME WITH TIMEZONE" carries a single numeric timezone with each
time field. It has no provision for daylight savings time. And a time
field without an associated date has imho no possibility for a
meaningful "timezone" or a meaningful usage. So the definitions and
features are completely at odds with typical date and time usage and
requirements in many countries around the world.

Date et al discuss this, and have the same opinion, so the gods are
with me on this one ;)

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

From bouncefilter Wed Mar 1 12:02:39 2000
Received: from www.wgcr.org (IDENT:root@www.wgcr.org [206.74.232.194])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA51060
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 12:01:43 -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 MAA03012;
Wed, 1 Mar 2000 12:01:25 -0500
Message-ID: <38BD4CDE.B781B0A0@wgcr.org>
Date: Wed, 01 Mar 2000 12:01:18 -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: Thomas Lockhart <lockhart@alumni.caltech.edu>
CC: "Sergio A. Kessler" <sak@tribctas.gba.gov.ar>,
pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] rpms
References: <SAK.2000.03.01.cdldgtsm@sergio>
<38BD466F.A5492476@alumni.caltech.edu>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Thomas Lockhart wrote:

Anyway, afaik most RPM distros of a product have one .rpm file which
has the name of the package, and then may have other .rpm files which
have qualifiers, like "-server". So in choosing which .rpm file will
be the base package, it seemed most appropriate that it be the
client-side stuff, as opposed to docs, or server (which btw can't
really be run on its own without the client stuff installed
*somewhere*), or something else.

I appreciate your points, but it isn't clear to me how to eliminate
*all* possibilities for confusion via RPM package names, so chose to
use names which give some appropriate functionality for each package.

Thanks for fielding this, Thomas. While it is certainly possible to
have a set of subpackages without a 'main' package (the Amanda network
backup package comes to mind), I personally agree with you. Besides,
the comments for the postgresql-x.x.x-x.i386.rpm package states that it
contains only the clients and docs -- or at least I think it does :-).

And there will always be confusion with as many packages as we have.
The only alternative that I see is to integrate all the packages into
one -- and that is by far a worse solution, as it requires way too many
packages installed -- it should not be necessary to have X installed to
run a postgresql server, for instance -- only the tk client and pgaccess
require X.

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

From bouncefilter Wed Mar 1 12:32:39 2000
Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu
[128.42.12.154])
by hub.org (8.9.3/8.9.3) with ESMTP id MAA55337
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 12:32:34 -0500 (EST)
(envelope-from reedstrm@wallace.ece.rice.edu)
Received: by wallace.ece.rice.edu via sendmail from stdin
id <m12QCrm-000LEMC@wallace.ece.rice.edu> (Debian Smail3.2.0.102)
for pgsql-hackers@postgresql.org; Wed, 1 Mar 2000 11:24:54 -0600 (CST)
Date: Wed, 1 Mar 2000 11:24:54 -0600
From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
To: Bruce Momjian <pgman@candle.pha.pa.us>
Cc: Adriaan Joubert <a.joubert@albourne.com>,
PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: bit types
Message-ID: <20000301112454.B15067@rice.edu>
References: <38BCDDBC.E776E7F8@albourne.com>
<200003011636.LAA08033@candle.pha.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <200003011636.LAA08033@candle.pha.pa.us>;
from pgman@candle.pha.pa.us on Wed, Mar 01, 2000 at 11:36:52AM
-0500

On Wed, Mar 01, 2000 at 11:36:52AM -0500, Bruce Momjian wrote:

Bruce,

The bit-type that is in contrib is useless as it stands. Those are
only C-routines to implement the functionality, and there are none of
the SQL functions to actually make these usable. This really needs to be
integrated with postgres proper. I don't know how to go about this and
that is why I asked for help. I'm prepared to do whatever SQL function
definitions are needed, do the regression tests etc. Would it be better
to go back to the hackers mailing list to ask for help? Has this missed
7.0 now? If so, we'd better remove the bit-type from contrib.

I clearly dropped the ball on this one. Don't think it can go into 7.0
because it would require catalog changes/initdb. However, I would like

Hmm, I thought the hard and fast rule was no initdb _after_ release. Surely
this sort of thing is what beta (especially beta1) is for?

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

From bouncefilter Wed Mar 1 12:50:40 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 MAA58209
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 12:50:17 -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 MAA03788;
Wed, 1 Mar 2000 12:50:13 -0500 (EST)
To: Jose Soares <jose@sferacarta.com>
cc: hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] having and union in v7beta
In-reply-to: <38BD0B7F.A581157@sferacarta.com>
References: <38BA829F.C8BA2978@sferacarta.com> <25405.951750847@sss.pgh.pa.us>
<38BA998A.258E068C@sferacarta.com> <27055.951783375@sss.pgh.pa.us>
<38BB84B3.1209A0C7@sferacarta.com>
<239.951848861@sss.pgh.pa.us> <38BD0B7F.A581157@sferacarta.com>
Comments: In-reply-to Jose Soares <jose@sferacarta.com>
message dated "Wed, 01 Mar 2000 13:22:23 +0100"
Date: Wed, 01 Mar 2000 12:50:12 -0500
Message-ID: <3785.951933012@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

But it's tough to believe that that accounts for a 3-to-4x
slowdown of this query; certainly I don't see much performance
difference on the datatypes I tried. What datatypes are your fields,
anyway?

6.5 takes 0.463s
7.0 takes 1.640s
the field type is CHAR(4)

Hmm. I see no more than a few percent difference between 6.5 and
current. There's something peculiar going on on your system.

I compiled up current code with profiling enabled (make PROFILE=-pg
if you want to try it), and found that actually nearly all of the
runtime for
select * from comuni union select * from comuni
is spent in the sort step; so I was on the wrong track in guessing
that there might be a performance problem in the new Unique coding.

I am not sure why you're seeing a performance differential for sorting.
Most of the cycles are going to go into bpcharlt(), which ultimately
calls strcoll() if you have USE_LOCALE defined. That's probably not a
very fast operation, but that code's hardly changed at all since 6.5.
It should be the same speed...

Are you sure you compiled both 6.5 and 7.0 the same way (with or without
USE_LOCALE)? Are you sure they're both running under the same locale
setting?

You might also try running the sort entirely in memory (no temp files).
Starting psql with
setenv PGOPTIONS "-S 10000"
(10 meg sort space) should do it. I'd be interested to know how 6.5
and 7.0 stack up for you that way.

regards, tom lane

From bouncefilter Wed Mar 1 13:10:40 2000
Received: from rage.hub.org (root@nat198.169.mpoweredpc.net [142.177.198.169])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA72826
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 13:10:03 -0500 (EST)
(envelope-from jeff@pgsql.com)
Received: from localhost (jeff@localhost)
by rage.hub.org (8.9.3/8.9.3) with ESMTP id OAA05611
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 14:10:23 -0400 (AST)
(envelope-from jeff@pgsql.com)
X-Authentication-Warning: rage.hub.org: jeff owned process doing -bs
Date: Wed, 1 Mar 2000 14:10:22 -0400 (AST)
From: Jeff MacDonald <jeff@pgsql.com>
X-Sender: jeff@rage.hub.org
Reply-To: Jeff MacDonald <jeff@pgsql.com>
To: pgsql-hackers@postgresql.org
Subject: Locking
Message-ID: <Pine.BSF.4.10.10003011409380.5539-100000@rage.hub.org>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

Hi folks,

I posted this on docs and didn't get a reply, so i'm throwing it in here.

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

Hi,

I was reading the MVCC docs and came across this statement

"Postgres doesn't remember any information about
modified rows in memory and so has no limit to the
number of rows locked without lock escalation. "

And this is how i interpreted it.

you can have unlimited rows locked with out it going to a 'table
lock'

Do these two staements say the samething ?

Jeff MacDonald
jeff@pgsql.com

From bouncefilter Wed Mar 1 13:36:52 2000
Received: from meryl.it.uu.se (root@meryl.it.uu.se [130.238.12.42])
by hub.org (8.9.3/8.9.3) with ESMTP id NAA76440
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 13:35:52 -0500 (EST)
(envelope-from e99re41@DoCS.UU.SE)
Received: from Svan.DoCS.UU.SE (e99re41@Svan.DoCS.UU.SE [130.238.9.160])
by meryl.it.uu.se (8.8.5/8.8.5) with ESMTP id TAA13317;
Wed, 1 Mar 2000 19:35:45 +0100 (MET)
Received: from localhost (e99re41@localhost) by Svan.DoCS.UU.SE
(8.6.12/8.6.12) with ESMTP id TAA07460;
Wed, 1 Mar 2000 19:35:44 +0100
X-Authentication-Warning: Svan.DoCS.UU.SE: e99re41 owned process doing -bs
Date: Wed, 1 Mar 2000 19:35:44 +0100 (MET)
From: Peter Eisentraut <e99re41@DoCS.UU.SE>
Reply-To: Peter Eisentraut <peter_e@gmx.net>
To: Tom Lane <tgl@sss.pgh.pa.us>
cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
Bruce Momjian <pgman@candle.pha.pa.us>, pgsql-hackers@postgreSQL.org
Subject: Re: BIT/BIT VARYING names (was Re: [HACKERS] Beta for 4:30AST)
In-Reply-To: <1213.951925927@sss.pgh.pa.us>
Message-ID: <Pine.GSO.4.02A.10003011928090.25449-100000@Svan.DoCS.UU.SE>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=iso-8859-1
Content-Transfer-Encoding: 8bit
X-MIME-Autoconverted: from QUOTED-PRINTABLE to 8bit by hub.org id NAA76539

On Wed, 1 Mar 2000, Tom Lane wrote:

Well, no, it becomes "bit varying", *with* quotes, if the dumper is
not broken.

I know, but consider psql and others just using plain libpq functionality.

for bit and bit varying. If you can find a way to avoid
that special-case logic, I'll get a lot more excited about not
having to treat "bit varying" as a special-case name.

NOOOOOOOOOOOOOO. I'm not trying to treat "bit varying" as a special case
name. I want to treat it as a normal name. There's absolutely no
difference whether the pg_type entry for the type represented by the
tokens BIT VARYING is "varbit", "bit varying", or "foo". I'm just saying
that the second would be more obvious and convenient, but that it would
require a small fix somewhere.

We're not going to allow any usertype(x) syntax in this life time, are we,
and the fact remains that we have to parse the reserved-word SQL types
separately. But this has all nothing to do with what I'm saying. Why
doesn't anyone understand me?

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

From bouncefilter Wed Mar 1 13:47:43 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 NAA78184
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 13:47:02 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id NAA06299;
Wed, 1 Mar 2000 13:46:00 -0500 (EST)
To: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
cc: Bruce Momjian <pgman@candle.pha.pa.us>,
Adriaan Joubert <a.joubert@albourne.com>,
PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: bit types
In-reply-to: <20000301112454.B15067@rice.edu>
References: <38BCDDBC.E776E7F8@albourne.com>
<200003011636.LAA08033@candle.pha.pa.us>
<20000301112454.B15067@rice.edu>
Comments: In-reply-to "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
message dated "Wed, 01 Mar 2000 11:24:54 -0600"
Date: Wed, 01 Mar 2000 13:46:00 -0500
Message-ID: <6295.951936360@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:

I clearly dropped the ball on this one. Don't think it can go into 7.0
because it would require catalog changes/initdb. However, I would like

Hmm, I thought the hard and fast rule was no initdb _after_ release. Surely
this sort of thing is what beta (especially beta1) is for?

Actually, it's not the initdb that bothers me --- it's that we'd be
talking about dropping in code that is not only not tested, but not
even written yet. It seems a tad late in the 7.0 cycle for that.

Specifically, what's in contrib is only the C functions to support a BIT
data type. Not only do we not have the SQL function definitions, but we
don't have the datatype, nor do we have the parser support needed for
BIT and BIT VARYING (or have you forgotten that those require special
syntax for their length specifications?) So this code is a long way
from being ready for prime time; it's only part of what's needed,
not all of it.

Possibly I misunderstand the rules we set for beta phase, but my
understanding was not so much "no initdbs" as "no new-feature
development". This sure looks like it needs some more feature
development...

regards, tom lane

From bouncefilter Wed Mar 1 14:07:41 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA80655
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 14:07: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 OAA06369;
Wed, 1 Mar 2000 14:06:48 -0500 (EST)
To: Jose Soares <jose@sferacarta.com>, hackers <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] having and union in v7beta
In-reply-to: <3785.951933012@sss.pgh.pa.us>
References: <38BA829F.C8BA2978@sferacarta.com> <25405.951750847@sss.pgh.pa.us>
<38BA998A.258E068C@sferacarta.com> <27055.951783375@sss.pgh.pa.us>
<38BB84B3.1209A0C7@sferacarta.com>
<239.951848861@sss.pgh.pa.us> <38BD0B7F.A581157@sferacarta.com>
<3785.951933012@sss.pgh.pa.us>
Comments: In-reply-to Tom Lane <tgl@sss.pgh.pa.us>
message dated "Wed, 01 Mar 2000 12:50:12 -0500"
Date: Wed, 01 Mar 2000 14:06:47 -0500
Message-ID: <6366.951937607@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

I wrote:

I compiled up current code with profiling enabled (make PROFILE=-pg
if you want to try it), and found that actually nearly all of the
runtime for
select * from comuni union select * from comuni
is spent in the sort step; so I was on the wrong track in guessing
that there might be a performance problem in the new Unique coding.

Wait a second. Stop the presses. I see what's going on here.

6.5.*:
play=> explain select * from comuni union select * from comuni;
NOTICE: QUERY PLAN:

Seq Scan on comuni (cost=512.00 rows=10000 width=84)

7.0beta1:
regression=# explain select * from comuni union select * from comuni;
NOTICE: QUERY PLAN:

Unique (cost=149.66..184.66 rows=200 width=84)
-> Sort (cost=149.66..149.66 rows=2000 width=84)
-> Append (cost=0.00..40.00 rows=2000 width=84)
-> Seq Scan on comuni (cost=0.00..20.00 rows=1000 width=84)
-> Seq Scan on comuni (cost=0.00..20.00 rows=1000 width=84)

7.0beta1's behavior is actually "correct", in the sense that it yields
the SQL-approved result: the UNION implies a DISTINCT pass over its
result, according to SQL, and 7.0beta1 is giving you a DISTINCT result.
6.5 is failing to generate the DISTINCT operation, because it
incorrectly simplifies "select foo union select foo" into "select foo"
if the two select queries are identical. (There is a TODO item for this.)
So that's why 6.5 is a lot faster. But it gives the wrong answer.

*However*, we have not fixed the bug that causes "select foo union
select foo" to be incorrectly simplified --- the UNION code is still
applying cnfify. (Which it probably shouldn't, but I haven't wanted
to touch that code until I have the time to rewrite it completely.)
The reason 7.0beta1 generates the "right" answer is that it has a
recently-introduced bug in the comparison routines that causes it to
think the two select subqueries aren't the same.

I just fixed that bug, with the result that current CVS code is now back
to mis-simplifying this query. (Yes, this is a step forward --- that
bug could have caused the system to unify two queries that AREN'T the
same, which would definitely be a bad thing...)

So, thanks! You did indeed identify a bug! But you should expect that
this query *will* get slower when we fix the other bug ;-). You should
use a less silly test case for UNION if you want to make realistic
performance comparisons across versions.

regards, tom lane

From bouncefilter Wed Mar 1 14:11:43 2000
Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu
[128.42.12.154])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA81012
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 14:11:33 -0500 (EST)
(envelope-from reedstrm@wallace.ece.rice.edu)
Received: by wallace.ece.rice.edu via sendmail from stdin
id <m12QEWy-000LEMC@wallace.ece.rice.edu> (Debian Smail3.2.0.102)
for pgsql-hackers@postgresql.org; Wed, 1 Mar 2000 13:11:32 -0600 (CST)
Date: Wed, 1 Mar 2000 13:11:32 -0600
From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
To: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] Re: bit types
Message-ID: <20000301131132.A15773@rice.edu>
References: <38BCDDBC.E776E7F8@albourne.com>
<200003011636.LAA08033@candle.pha.pa.us>
<20000301112454.B15067@rice.edu> <6295.951936360@sss.pgh.pa.us>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <6295.951936360@sss.pgh.pa.us>;
from tgl@sss.pgh.pa.us on Wed, Mar 01, 2000 at 01:46:00PM -0500

On Wed, Mar 01, 2000 at 01:46:00PM -0500, Tom Lane wrote:

"Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu> writes:

I clearly dropped the ball on this one. Don't think it can go into 7.0
because it would require catalog changes/initdb. However, I would like

Hmm, I thought the hard and fast rule was no initdb _after_ release. Surely
this sort of thing is what beta (especially beta1) is for?

Actually, it's not the initdb that bothers me --- it's that we'd be
talking about dropping in code that is not only not tested, but not
even written yet. It seems a tad late in the 7.0 cycle for that.

Agreed.

Specifically, what's in contrib is only the C functions to support a BIT
data type. Not only do we not have the SQL function definitions, but we
don't have the datatype, nor do we have the parser support needed for
BIT and BIT VARYING (or have you forgotten that those require special
syntax for their length specifications?) So this code is a long way
from being ready for prime time; it's only part of what's needed,
not all of it.

Right, that's the _other_ current thread. ;-)

Possibly I misunderstand the rules we set for beta phase, but my
understanding was not so much "no initdbs" as "no new-feature
development". This sure looks like it needs some more feature
development...

That's how I understood it, as well. It's just that Bruce had (at one
time) offered to do the intergration/development of this type, and one
interpretation of what he posted was that he had the code, but hadn't
integrated it, because of the "no initdb" rule. Since Bruce tends to be
the man for submissions from non-core developers, I just wanted to make
sure everyone was on the same page.

On a completely unrelated note: Apparently, there was a "bug fix" to
SQL92, published in 1996, that goes by the name:

Technical Corrigendum 1:1996 to ISO/IEC 9075:1992

According to the www.iso.ch site in Switzerland, this thing is 80
pages long. Even given the usual front matter, indices, and appendices,
there's got to be something in there that we need to know. Hmm, it's
marked as free. Perhaps I'll see if I can order it somewhere.

Anyone seen this?

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

From bouncefilter Wed Mar 1 14:14:40 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 OAA81366
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 14:13: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
OAA12243;
Wed, 1 Mar 2000 14:13:11 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200003011913.OAA12243@candle.pha.pa.us>
Subject: Re: [HACKERS] patch for plperl Makefile.PL
In-Reply-To: <20000301091749.A30893@mindspring.com> from Mark Hollomon at "Mar
1, 2000 09:17:49 am"
To: Mark Hollomon <mhh@mindspring.com>
Date: Wed, 1 Mar 2000 14:13:11 -0500 (EST)
CC: pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Applied. I assume you made this version from the current Makefile.PL in
the source tree, and not the one you originally sent. The current one
had some fixes in it. I can't figure out if they are still there
because of the size of the changes.

Here is a new MAkefile.PL for plperl.

It not only solves the problem for the missing symbol,
but there is also now a 'make install'.

By the way, earlier, I sent a patch to add plperl
to createlang, droplang. Those patches haven't made
it into the source tree yet. Do I need to resend them?

--
Mark Hollomon
mhh@mindspring.com

[Attachment, skipping...]

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

From bouncefilter Wed Mar 1 14:24:41 2000
Received: from candle.pha.pa.us (pgman@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA82494
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 14:24:08 -0500 (EST)
(envelope-from pgman@candle.pha.pa.us)
Received: (from pgman@localhost) by candle.pha.pa.us (8.9.0/8.9.0) id
OAA13408;
Wed, 1 Mar 2000 14:23:27 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200003011923.OAA13408@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: bit types
In-Reply-To: <20000301112454.B15067@rice.edu> from "Ross J. Reedstrom" at "Mar
1, 2000 11:24:54 am"
To: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Date: Wed, 1 Mar 2000 14:23:27 -0500 (EST)
CC: Adriaan Joubert <a.joubert@albourne.com>,
PostgreSQL-development <pgsql-hackers@postgresql.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

The bit-type that is in contrib is useless as it stands. Those are
only C-routines to implement the functionality, and there are none of
the SQL functions to actually make these usable. This really needs to be
integrated with postgres proper. I don't know how to go about this and
that is why I asked for help. I'm prepared to do whatever SQL function
definitions are needed, do the regression tests etc. Would it be better
to go back to the hackers mailing list to ask for help? Has this missed
7.0 now? If so, we'd better remove the bit-type from contrib.

I clearly dropped the ball on this one. Don't think it can go into 7.0
because it would require catalog changes/initdb. However, I would like

Hmm, I thought the hard and fast rule was no initdb _after_ release. Surely
this sort of thing is what beta (especially beta1) is for?

No, we usually avoid initdb if at all possible during beta. A new data
type is not enough reason for it.

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

From bouncefilter Wed Mar 1 14:32:41 2000
Received: from candle.pha.pa.us (s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA83623
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 14:31:50 -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
OAA13526;
Wed, 1 Mar 2000 14:28:02 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200003011928.OAA13526@candle.pha.pa.us>
Subject: Re: [HACKERS] Re: bit types
In-Reply-To: <20000301131132.A15773@rice.edu> from "Ross J. Reedstrom" at "Mar
1, 2000 01:11:32 pm"
To: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
Date: Wed, 1 Mar 2000 14:28:02 -0500 (EST)
CC: PostgreSQL-development <pgsql-hackers@postgreSQL.org>
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

Possibly I misunderstand the rules we set for beta phase, but my
understanding was not so much "no initdbs" as "no new-feature
development". This sure looks like it needs some more feature
development...

That's how I understood it, as well. It's just that Bruce had (at one
time) offered to do the intergration/development of this type, and one
interpretation of what he posted was that he had the code, but hadn't
integrated it, because of the "no initdb" rule. Since Bruce tends to be
the man for submissions from non-core developers, I just wanted to make
sure everyone was on the same page.

No, I have not written the code. I have been pretty busy and did not go
though my mailbox like I normally do before beta time.

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

From bouncefilter Wed Mar 1 14:30:41 2000
Received: from wallace.ece.rice.edu (root@wallace.ece.rice.edu
[128.42.12.154])
by hub.org (8.9.3/8.9.3) with ESMTP id OAA83378
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 14:30:15 -0500 (EST)
(envelope-from reedstrm@wallace.ece.rice.edu)
Received: by wallace.ece.rice.edu via sendmail from stdin
id <m12QEp4-000LEMC@wallace.ece.rice.edu> (Debian Smail3.2.0.102)
for pgsql-hackers@postgresql.org; Wed, 1 Mar 2000 13:30:14 -0600 (CST)
Date: Wed, 1 Mar 2000 13:30:14 -0600
From: "Ross J. Reedstrom" <reedstrm@wallace.ece.rice.edu>
To: "Ross J. Reedstrom" <reedstrm@ece.rice.edu>
Cc: PostgreSQL-development <pgsql-hackers@postgresql.org>
Subject: SQL92 standard corrections
Message-ID: <20000301133014.B15773@rice.edu>
References: <38BCDDBC.E776E7F8@albourne.com>
<200003011636.LAA08033@candle.pha.pa.us>
<20000301112454.B15067@rice.edu> <6295.951936360@sss.pgh.pa.us>
<20000301131132.A15773@rice.edu>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
User-Agent: Mutt/1.0i
In-Reply-To: <20000301131132.A15773@rice.edu>;
from reedstrm@ece.rice.edu on Wed, Mar 01, 2000 at 01:11:32PM
-0600

On Wed, Mar 01, 2000 at 01:11:32PM -0600, Ross J. Reedstrom wrote:

On a completely unrelated note: Apparently, there was a "bug fix" to
SQL92, published in 1996, that goes by the name:

Technical Corrigendum 1:1996 to ISO/IEC 9075:1992

According to the www.iso.ch site in Switzerland, this thing is 80
pages long. Even given the usual front matter, indices, and appendices,
there's got to be something in there that we need to know. Hmm, it's
marked as free. Perhaps I'll see if I can order it somewhere.

Anyone seen this?

Following up to myself: Turns out there was _another_ TC published in
1999:

ISO/IEC 9075:1992/Cor 3:1999 (Don't ask _me_ where Cor 2 went ;-)

This one's 112 pages. Hmm, time to call my local librarian and see how one
gets a hold of ISO standards around here.

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

From bouncefilter Wed Mar 1 14:57:43 2000
Received: from smtp.pandora.be (hercules.telenet-ops.be [195.130.132.33])
by hub.org (8.9.3/8.9.3) with SMTP id OAA86395
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 14:57:10 -0500 (EST)
(envelope-from Willy.DelaCourt@pandora.be)
Received: (qmail 1088 invoked from network); 1 Mar 2000 19:57:04 -0000
Received: from unknown (HELO stargate.speedy.home) ([195.130.137.182])
(envelope-sender <Willy.DelaCourt@pandora.be>)
by hercules.telenet-ops.be (qmail-ldap-1.03) with SMTP
for <pgsql-hackers@postgreSQL.org>; 1 Mar 2000 19:57:04 -0000
Received: from daffy (DAFFY.speedy.home [192.168.1.30])
by stargate.speedy.home (8.9.3/8.9.3) with SMTP id UAA11938
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 20:57:14 +0100
Received: by localhost with Microsoft MAPI; Wed, 1 Mar 2000 20:58:08 +0100
Message-ID: <01BF83C0.D9066E20.Willy.DelaCourt@pandora.be>
From: Willy De la Court <Willy.DelaCourt@pandora.be>
To: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgreSQL.org>
Subject: empty dates and changing the default date behaviour
Date: Wed, 1 Mar 2000 20:58:07 +0100
X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

Hi,

First of all I want to congratulate all the people working on PostgreSQL for a job well done.

Now my problem.

I want to change the default behaviour of the data_in function without recompiling postgres.
the functionality is the following.

create table test (d date);
insert into test values ('');

this should result in the field d containing NULL and the insert command should work without returning an error.

Is this possible and how do I do this.

From bouncefilter Wed Mar 1 14:59:43 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 OAA86518
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 14:58:58 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id OAA14325;
Wed, 1 Mar 2000 14:58:07 -0500 (EST)
To: Peter Eisentraut <peter_e@gmx.net>
cc: Thomas Lockhart <lockhart@alumni.caltech.edu>,
Bruce Momjian <pgman@candle.pha.pa.us>, pgsql-hackers@postgreSQL.org
Subject: Re: BIT/BIT VARYING names (was Re: [HACKERS] Beta for 4:30AST)
In-reply-to: <Pine.GSO.4.02A.10003011928090.25449-100000@Svan.DoCS.UU.SE>
References: <Pine.GSO.4.02A.10003011928090.25449-100000@Svan.DoCS.UU.SE>
Comments: In-reply-to Peter Eisentraut <e99re41@DoCS.UU.SE>
message dated "Wed, 01 Mar 2000 19:35:44 +0100"
Date: Wed, 01 Mar 2000 14:58:07 -0500
Message-ID: <14322.951940687@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

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

NOOOOOOOOOOOOOO. I'm not trying to treat "bit varying" as a special case
name. I want to treat it as a normal name. There's absolutely no
difference whether the pg_type entry for the type represented by the
tokens BIT VARYING is "varbit", "bit varying", or "foo". I'm just saying
that the second would be more obvious and convenient, but that it would
require a small fix somewhere.

OK, fair enough, but the thing is: is the bootstrap parser the only
place that will have to be changed to make this possible? I doubt it.
The fix may not be as small as you expect.

There's another issue, which is that the routines that implement
operations for a particular type are generally named after the type's
internal name. I trust you are not going to propose that we find a way
to put spaces into C function names ;-). It seems to me that the
confusion created by having support code named differently from the
type's internal name is just as bad as having the internal name
different from the external name.

This being the case, it seems like "bit_varying" might be a reasonable
compromise for the internal name, and that should work already...

regards, tom lane

From bouncefilter Wed Mar 1 15:04:52 2000
Received: from hu.tm.ee (ppp929.tele2.ee [212.107.37.229])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA87101
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 15:04:20 -0500 (EST)
(envelope-from hannu@tm.ee)
Received: from tm.ee (localhost [127.0.0.1]) by hu.tm.ee (Postfix) with ESMTP
id 51F573BC3; Wed, 1 Mar 2000 22:13:21 +0200 (EET)
Sender: hannu@hu.tm.ee
Message-ID: <38BD79E0.FF6E9FB2@tm.ee>
Date: Wed, 01 Mar 2000 22:13:20 +0200
From: Hannu Krosing <hannu@tm.ee>
Organization: Trust-O-Matic =?iso-8859-1?Q?O=DC?=
X-Mailer: Mozilla 4.7 [en] (X11; I; Linux 2.2.13-7mdk i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Jan Wieck <wieck@debis.com>
Cc: Don Baccus <dhogaza@pacifier.com>, The Hermit Hacker <scrappy@hub.org>,
Thomas Lockhart <lockhart@alumni.caltech.edu>,
Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgreSQL.org
Subject: Re: [HACKERS] Re: NOT {NULL|DEFERRABLE} (was: bug in 7.0)
References: <m12PwRm-0003kgC@orion.SAPserv.Hamburg.dsh.de>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Jan Wieck wrote:

What I suggest is, take it as it is. We use to have a
friendly and nice ground noise in our mailing lists. So this
kind of discussion, tending to become flame wars, should be
taken off list at least (IMHO avoided at all, but I'm farest
the last person to judge).

Maybe we need psql-flamewars@postgresql.org where we could migrate
these threads ? or even psql-hackers-flamewars@postgresql.org ;)

I just read on NYTimes direct that a consulting firm has started
educating businesmens wives to not slurp soup or misbehave in other
ways.

We could possibly hire them to consult the hackers list too ;)

I'm sure PostgreSQL Inc. would accept donations to that end ;-p

---------
Hannu
(answers to psql-flamewars@postgresql.org please)

From bouncefilter Wed Mar 1 15:26:42 2000
Received: from sapphire.albourne.com (sapphire.albourne.com [195.212.241.227])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA90214
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 15:26:04 -0500 (EST)
(envelope-from a.joubert@albourne.com)
Received: from albourne.com (akamas.albourne.com [195.212.241.254])
by sapphire.albourne.com (8.9.3/8.9.3/Albourne/CYS/1.8/MAPS) with ESMTP
id WAA24262; Wed, 1 Mar 2000 22:24:41 +0200 (EET)
Sender: a.joubert@albourne.com
Message-ID: <38BD7C3F.D4D34F56@albourne.com>
Date: Wed, 01 Mar 2000 22:23:27 +0200
From: Adriaan Joubert <a.joubert@albourne.com>
X-Mailer: Mozilla 4.7 [en] (X11; U; Linux 2.2.14 i686)
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: bit types
References: <200003011636.LAA08033@candle.pha.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Bruce Momjian wrote:

Bruce,

The bit-type that is in contrib is useless as it stands. Those are
only C-routines to implement the functionality, and there are none of
the SQL functions to actually make these usable. This really needs to be
integrated with postgres proper. I don't know how to go about this and
that is why I asked for help. I'm prepared to do whatever SQL function
definitions are needed, do the regression tests etc. Would it be better
to go back to the hackers mailing list to ask for help? Has this missed
7.0 now? If so, we'd better remove the bit-type from contrib.

I clearly dropped the ball on this one. Don't think it can go into 7.0
because it would require catalog changes/initdb. However, I would like
to keep it in contrib and add it as soon as 7.0 finalizes and we move to
7.1.

Fine by me either way. My systems still run on a non-SQL compliant bit-type I
did earlier. Whenever you get round to it, give me a shout and I'll do
whatever I can to help.

Cheers,

Adriaan

From bouncefilter Wed Mar 1 15:24:42 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id PAA89899
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 15:23:54 -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 PAA24982;
Wed, 1 Mar 2000 15:23:47 -0500 (EST)
To: Willy De la Court <Willy.DelaCourt@pandora.be>
cc: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] empty dates and changing the default date behaviour
In-reply-to: <01BF83C0.D9066E20.Willy.DelaCourt@pandora.be>
References: <01BF83C0.D9066E20.Willy.DelaCourt@pandora.be>
Comments: In-reply-to Willy De la Court <Willy.DelaCourt@pandora.be>
message dated "Wed, 01 Mar 2000 20:58:07 +0100"
Date: Wed, 01 Mar 2000 15:23:47 -0500
Message-ID: <24979.951942227@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Willy De la Court <Willy.DelaCourt@pandora.be> writes:

I want to change the default behaviour of the data_in function without
recompiling postgres. the functionality is the following.

create table test (d date);
insert into test values ('');

this should result in the field d containing NULL and the insert
command should work without returning an error.

Not possible at present, since a datatype's typinput function can't
return a NULL. I suppose it will be possible after we redo the
function manager interface, but in any case you'd have no hope of
changing the behavior "without recompiling postgres".

regards, tom lane

From bouncefilter Wed Mar 1 15:24:44 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 PAA89963
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 15:24:32 -0500 (EST)
(envelope-from vev@michvhf.com)
Received: (qmail 13931 invoked by uid 1001); 1 Mar 2000 20:24:34 -0000
Message-ID: <XFMail.000301152434.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: <38BD79E0.FF6E9FB2@tm.ee>
Date: Wed, 01 Mar 2000 15:24:34 -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: Hannu Krosing <hannu@tm.ee>
Subject: Re: [HACKERS] Re: NOT {NULL|DEFERRABLE} (was: bug in 7.0)
Cc: The Hermit Hacker <scrappy@hub.org>, pgsql-hackers@postgreSQL.org

On 01-Mar-00 Hannu Krosing wrote:

(answers to psql-flamewars@postgresql.org please)

I wonder if we could get Marc to create psql-flamewars and just route it
to /dev/null :)

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 Wed Mar 1 16:01:42 2000
Received: from candle.pha.pa.us (root@s5-03.ppp.op.net [209.152.195.67])
by hub.org (8.9.3/8.9.3) with ESMTP id QAA94332
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 16:00: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
PAA14910;
Wed, 1 Mar 2000 15:26:59 -0500 (EST)
From: Bruce Momjian <pgman@candle.pha.pa.us>
Message-Id: <200003012026.PAA14910@candle.pha.pa.us>
Subject: Re: BIT/BIT VARYING names (was Re: [HACKERS] Beta for 4:30AST)
In-Reply-To: <14322.951940687@sss.pgh.pa.us> from Tom Lane at "Mar 1,
2000 02:58:07 pm"
To: Tom Lane <tgl@sss.pgh.pa.us>
Date: Wed, 1 Mar 2000 15:26:59 -0500 (EST)
CC: Peter Eisentraut <peter_e@gmx.net>,
Thomas Lockhart <lockhart@alumni.caltech.edu>, pgsql-hackers@postgreSQL.org
X-Mailer: ELM [version 2.4ME+ PL72 (25)]
MIME-Version: 1.0
Content-Type: text/plain; charset=US-ASCII
Content-Transfer-Encoding: 7bit

There's another issue, which is that the routines that implement
operations for a particular type are generally named after the type's
internal name. I trust you are not going to propose that we find a way
to put spaces into C function names ;-). It seems to me that the
confusion created by having support code named differently from the
type's internal name is just as bad as having the internal name
different from the external name.

This being the case, it seems like "bit_varying" might be a reasonable
compromise for the internal name, and that should work already...

Having only one type with an underscore seems like a mistake. We already
don't have internal names matching. I would just make it bit, bitvar,
or maybe varbit.

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

From bouncefilter Wed Mar 1 15:42:41 2000
Received: from smtp.pandora.be (hercules.telenet-ops.be [195.130.132.33])
by hub.org (8.9.3/8.9.3) with SMTP id PAA92209
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 15:42:22 -0500 (EST)
(envelope-from Willy.DelaCourt@pandora.be)
Received: (qmail 20941 invoked from network); 1 Mar 2000 20:42:18 -0000
Received: from unknown (HELO stargate.speedy.home) ([195.130.137.182])
(envelope-sender <Willy.DelaCourt@pandora.be>)
by hercules.telenet-ops.be (qmail-ldap-1.03) with SMTP
for <pgsql-hackers@postgreSQL.org>; 1 Mar 2000 20:42:18 -0000
Received: from daffy (DAFFY.speedy.home [192.168.1.30])
by stargate.speedy.home (8.9.3/8.9.3) with SMTP id VAA12052
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 21:42:28 +0100
Received: by localhost with Microsoft MAPI; Wed, 1 Mar 2000 21:43:22 +0100
Message-ID: <01BF83C7.2A629EA0.Willy.DelaCourt@pandora.be>
From: Willy De la Court <Willy.DelaCourt@pandora.be>
To: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] empty dates and changing the default date behaviour
Date: Wed, 1 Mar 2000 21:43:21 +0100
X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

Tom Lane [SMTP:tgl@sss.pgh.pa.us] wrote:
Willy De la Court <Willy.DelaCourt@pandora.be> writes:

I want to change the default behaviour of the data_in function without
recompiling postgres. the functionality is the following.

create table test (d date);
insert into test values ('');

this should result in the field d containing NULL and the insert
command should work without returning an error.

Not possible at present, since a datatype's typinput function can't
return a NULL. I suppose it will be possible after we redo the
function manager interface, but in any case you'd have no hope of
changing the behavior "without recompiling postgres".

Ok how do i do it with recompiling postgres I badly need this functionality.
I know some C programming but not enough to dive into the source of postgres.

any suggestions.

From bouncefilter Wed Mar 1 19:49:46 2000
Received: from smtp.pandora.be (hercules.telenet-ops.be [195.130.132.33])
by hub.org (8.9.3/8.9.3) with SMTP id TAA14094
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 19:48:55 -0500 (EST)
(envelope-from Willy.DelaCourt@pandora.be)
Received: (qmail 14857 invoked from network); 2 Mar 2000 00:48:54 -0000
Received: from unknown (HELO stargate.speedy.home) ([195.130.137.182])
(envelope-sender <Willy.DelaCourt@pandora.be>)
by hercules.telenet-ops.be (qmail-ldap-1.03) with SMTP
for <pgsql-hackers@postgreSQL.org>; 2 Mar 2000 00:48:54 -0000
Received: from daffy (DAFFY.speedy.home [192.168.1.30])
by stargate.speedy.home (8.9.3/8.9.3) with SMTP id BAA12563
for <pgsql-hackers@postgreSQL.org>; Thu, 2 Mar 2000 01:49:07 +0100
Received: by localhost with Microsoft MAPI; Thu, 2 Mar 2000 01:49:56 +0100
Message-ID: <01BF83E9.9CBCC9E0.Willy.DelaCourt@pandora.be>
From: Willy De la Court <Willy.DelaCourt@pandora.be>
To: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgreSQL.org>
Subject: RE: [HACKERS] empty dates and changing the default date behaviour
Date: Thu, 2 Mar 2000 01:49:55 +0100
X-Mailer: Microsoft Internet E-mail/MAPI - 8.0.0.4211
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-Transfer-Encoding: 7bit

I found a solution
you are right tom with the NULLs but I think I found an elegant solution to this

First of all I patch the date_in function like this
--- postgresql-6.5.2/src/backend/utils/adt/datetime.c	Mon Aug  2 07:24:51 1999
+++ postgresql-6.5.2-new/src/backend/utils/adt/datetime.c	Thu Mar  2 00:55:54 2000
@@ -51,8 +51,15 @@
 #ifdef DATEDEBUG
 	printf("date_in- input string is %s\n", str);
 #endif
-	if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
-	 || (DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp) != 0))
+	if (strlen(str) == 0 ) {
+	    tm->tm_year = 9999;
+	    tm->tm_mon = 1;
+	    tm->tm_mday = 1;
+	    dtype = DTK_DATE;
+	}
+	else
+	    if ((ParseDateTime(str, lowstr, field, ftype, MAXDATEFIELDS, &nf) != 0)
+	     || (DecodeDateTime(field, ftype, nf, &dtype, tm, &fsec, &tzp) != 0))
 		elog(ERROR, "Bad date external representation '%s'", str);

switch (dtype)

and the I write some functions (used in triggers) to convert the date 9999/01/01 to nulls.
like this

create function check_date(date) returns date as '
begin
if $1 = ''9999-01-01''::date then
return NULL;
else
return $1;
end if;
end;
' language 'plpgsql';

create function check_peo_dates () returns opaque as '
begin
NEW.PEO_MAIL = check_date(NEW.PEO_MAIL);
NEW.PEO_VISIT = check_date(NEW.PEO_VISIT);
NEW.PEO_SP = check_date(NEW.PEO_SP);
NEW.PEO_VAL = check_date(NEW.PEO_VAL);
NEW.PEO_CRE = check_date(NEW.PEO_CRE);
return NEW;
end;
' language 'plpgsql';

is this a good solution or is it plain dumb.
The only problem with this is you need to write triggers for all tables that have date fields.

What is the SQL92 rule about emty dates? Does anyone know?

Willy De la Court [SMTP:Willy.DelaCourt@pandora.be] wrote:

Tom Lane [SMTP:tgl@sss.pgh.pa.us] wrote:
Willy De la Court <Willy.DelaCourt@pandora.be> writes:

I want to change the default behaviour of the data_in function without
recompiling postgres. the functionality is the following.

create table test (d date);
insert into test values ('');

this should result in the field d containing NULL and the insert
command should work without returning an error.

Not possible at present, since a datatype's typinput function can't
return a NULL. I suppose it will be possible after we redo the
function manager interface, but in any case you'd have no hope of
changing the behavior "without recompiling postgres".

From bouncefilter Wed Mar 1 22:51:51 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 WAA33003
for <pgsql-hackers@postgresql.org>; Wed, 1 Mar 2000 22:51:45 -0500 (EST)
(envelope-from tgl@sss.pgh.pa.us)
Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1])
by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id WAA07359;
Wed, 1 Mar 2000 22:51:42 -0500 (EST)
To: Willy De la Court <Willy.DelaCourt@pandora.be>
cc: "pgsql-hackers@postgreSQL.org" <pgsql-hackers@postgresql.org>
Subject: Re: [HACKERS] empty dates and changing the default date behaviour
In-reply-to: <01BF83E9.9CBCC9E0.Willy.DelaCourt@pandora.be>
References: <01BF83E9.9CBCC9E0.Willy.DelaCourt@pandora.be>
Comments: In-reply-to Willy De la Court <Willy.DelaCourt@pandora.be>
message dated "Thu, 02 Mar 2000 01:49:55 +0100"
Date: Wed, 01 Mar 2000 22:51:42 -0500
Message-ID: <7356.951969102@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Willy De la Court <Willy.DelaCourt@pandora.be> writes:

you are right tom with the NULLs but I think I found an elegant
solution to this

Interesting; I didn't realize that plpgsql supported functions returning
NULLs.

I wouldn't call it an "elegant" solution, by any means ;-). But if it
gets the job done for you, it'll do as a stopgap until datein() can
return a NULL itself.

regards, tom lane

From bouncefilter Wed Mar 1 23:18:49 2000
Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2])
by hub.org (8.9.3/8.9.3) with ESMTP id XAA35552
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 23:18:15 -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 XAA07531;
Wed, 1 Mar 2000 23:18:02 -0500 (EST)
To: Alfred Perlstein <bright@wintelcom.net>
cc: PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] bitten by docs
In-reply-to: <20000301203334.B21720@fw.wintelcom.net>
References: <20000301203334.B21720@fw.wintelcom.net>
Comments: In-reply-to Alfred Perlstein <bright@wintelcom.net>
message dated "Wed, 01 Mar 2000 20:33:34 -0800"
Date: Wed, 01 Mar 2000 23:18:02 -0500
Message-ID: <7528.951970682@sss.pgh.pa.us>
From: Tom Lane <tgl@sss.pgh.pa.us>

Alfred Perlstein <bright@wintelcom.net> writes:

Getting to the point, wouldn't it be easier on the users if either:

a) at release time the www docs are frozen but a link is made to the
HEAD branch of the docs so people can see what's going on in
current development.
b) maintain a -stable (off the 6.5 branch) of the docs that gets
fixes put into it relative to the release while still having a link
to the most current docs. I know the inconvience of maintaining
a branch is annoying but this is the best way imo.

This was discussed a while ago. I thought we had agreed that we needed
to keep two sets of docs on the website, one for the last stable release
(ie, 6.5 currently) and one for the current development tip. But I
guess nothing's been done about it yet...

regards, tom lane

From bouncefilter Wed Mar 1 23:03:50 2000
Received: from fw.wintelcom.net (bright@ns1.wintelcom.net [209.1.153.20])
by hub.org (8.9.3/8.9.3) with ESMTP id XAA34260
for <pgsql-hackers@postgreSQL.org>; Wed, 1 Mar 2000 23:03:06 -0500 (EST)
(envelope-from bright@fw.wintelcom.net)
Received: (from bright@localhost) by fw.wintelcom.net (8.9.3/8.9.3) id
UAA02591
for pgsql-hackers@postgreSQL.org; Wed, 1 Mar 2000 20:33:34 -0800 (PST)
Date: Wed, 1 Mar 2000 20:33:34 -0800
From: Alfred Perlstein <bright@wintelcom.net>
To: PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: bitten by docs
Message-ID: <20000301203334.B21720@fw.wintelcom.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
X-Mailer: Mutt 1.0.1i

Hey I just got bitten by something in the docs:
http://www.postgresql.org/docs/user/datatype1029.htm

specifically:

Note: To ensure compatibility to earlier versions of PostgreSQL
we also continue to provide datetime (equivalent to timestamp)
and timespan (equivalent to interval).
The types abstime and reltime are lower precision types which
are used internally. You are discouraged from using any of these
types in new applications and move any old ones over when
appropriate. Any or all of these type might disappear in a future
release.

after reading this I quickly converted all my datetime into timestamp
then I got bitten because a lot of my scripts were using date functions
that don't seem to support timestamp as an argument, specifically
date_part().

Now if date_part() doesn't take timestamps in 7.0 that needs to be
addressed, however it seems that this change took place some time
after the 6.5 release.

I also realized that my changes to the docs in re async libpq functions
probably are confusing the hell out of people using the 'stable' 6.5.

Getting to the point, wouldn't it be easier on the users if either:

a) at release time the www docs are frozen but a link is made to the
HEAD branch of the docs so people can see what's going on in
current development.
b) maintain a -stable (off the 6.5 branch) of the docs that gets
fixes put into it relative to the release while still having a link
to the most current docs. I know the inconvience of maintaining
a branch is annoying but this is the best way imo.

I think providing for this when 7.0 comes out would really assist
users, as the next release rolls around it's going to be just too
easy to get confused.

thanks,
--
-Alfred Perlstein - [bright@wintelcom.net|alfred@freebsd.org]

From bouncefilter Thu Mar 2 01:21:49 2000
Received: from localhost (IDENT:root@hectic-1.jpl.nasa.gov [128.149.68.203])
by hub.org (8.9.3/8.9.3) with ESMTP id BAA42387
for <pgsql-hackers@postgreSQL.org>; Thu, 2 Mar 2000 01:20:59 -0500 (EST)
(envelope-from lockhart@alumni.caltech.edu)
Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1])
by localhost (8.8.7/8.8.7) with ESMTP id GAA15685;
Thu, 2 Mar 2000 06:29:10 GMT
Sender: lockhart@hub.org
Message-ID: <38BE0A36.6122A0E9@alumni.caltech.edu>
Date: Thu, 02 Mar 2000 06:29:10 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Peter Eisentraut <peter_e@gmx.net>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Bruce Momjian <pgman@candle.pha.pa.us>,
pgsql-hackers@postgreSQL.org
Subject: Re: BIT/BIT VARYING names (was Re: [HACKERS] Beta for 4:30AST)
References: <Pine.GSO.4.02A.10003011928090.25449-100000@Svan.DoCS.UU.SE>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

... But this has all nothing to do with what I'm saying. Why
doesn't anyone understand me?

Uh, could be that we're all a bunch of idiots. Of course, I'd prefer
some other explanation... :))

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

From bouncefilter Thu Mar 2 01:41:52 2000
Received: from localhost (IDENT:root@hectic-1.jpl.nasa.gov [128.149.68.203])
by hub.org (8.9.3/8.9.3) with ESMTP id BAA43836
for <pgsql-hackers@postgreSQL.org>; Thu, 2 Mar 2000 01:41:28 -0500 (EST)
(envelope-from lockhart@alumni.caltech.edu)
Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1])
by localhost (8.8.7/8.8.7) with ESMTP id GAA15731;
Thu, 2 Mar 2000 06:46:47 GMT
Sender: lockhart@hub.org
Message-ID: <38BE0E57.107D1FC3@alumni.caltech.edu>
Date: Thu, 02 Mar 2000 06:46:47 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Vince Vielhaber <vev@michvhf.com>
CC: Tom Lane <tgl@sss.pgh.pa.us>, Alfred Perlstein <bright@wintelcom.net>,
PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] bitten by docs
References: <20000301203334.B21720@fw.wintelcom.net>
<7528.951970682@sss.pgh.pa.us>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

This was discussed a while ago. I thought we had agreed that we needed
to keep two sets of docs on the website, one for the last stable release
(ie, 6.5 currently) and one for the current development tip. But I
guess nothing's been done about it yet...

Perhaps we agreed what should be done, but I'm not sure anyone agreed
to actually do it.

Vince, could you take ownership of this issue (at least to get it on a
ToDo list so we don't forget)? I'm happy to help (will probably need
to redirect the nightly html production to another area of the web
site) but I'm reluctant to muck around with the overall structure of
the site, at least without some adult supervision...

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

From bouncefilter Thu Mar 2 01:40:02 2000
Received: from localhost (IDENT:root@hectic-1.jpl.nasa.gov [128.149.68.203])
by hub.org (8.9.3/8.9.3) with ESMTP id BAA43734
for <pgsql-hackers@postgreSQL.org>; Thu, 2 Mar 2000 01:39:33 -0500 (EST)
(envelope-from lockhart@alumni.caltech.edu)
Received: from alumni.caltech.edu (lockhart@localhost [127.0.0.1])
by localhost (8.8.7/8.8.7) with ESMTP id GAA15737;
Thu, 2 Mar 2000 06:48:07 GMT
Sender: lockhart@hub.org
Message-ID: <38BE0EA7.EC5100F3@alumni.caltech.edu>
Date: Thu, 02 Mar 2000 06:48:07 +0000
From: Thomas Lockhart <lockhart@alumni.caltech.edu>
X-Mailer: Mozilla 4.6 [en] (X11; I; Linux 2.0.36 i686)
X-Accept-Language: en
MIME-Version: 1.0
To: Alfred Perlstein <bright@wintelcom.net>
CC: PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] bitten by docs
References: <20000301203334.B21720@fw.wintelcom.net>
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

Note: To ensure compatibility to earlier versions of PostgreSQL
we also continue to provide datetime (equivalent to timestamp)
and timespan (equivalent to interval).
The types abstime and reltime are lower precision types which
are used internally. You are discouraged from using any of these
types in new applications and move any old ones over when
appropriate. Any or all of these type might disappear in a future
release.

I'll probably rephrase this to emphasize that datetime et al are now
deprecated and will eventually disappear.

- Thomas

--
Thomas Lockhart lockhart@alumni.caltech.edu
South Pasadena, California

From bouncefilter Thu Mar 2 03:55:55 2000
Received: from gandalf.it-austria.net (gandalf.it-austria.net [213.150.1.65])
by hub.org (8.9.3/8.9.3) with ESMTP id DAA59427
for <pgsql-hackers@postgreSQL.org>; Thu, 2 Mar 2000 03:55:29 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at (sdgtw.sd.spardat.at
[172.18.1.16])
by gandalf.it-austria.net (xxx/xxx) with ESMTP id JAA128102;
Thu, 2 Mar 2000 09:55:04 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <1TSZH1ZH>; Thu, 2 Mar 2000 09:55:04 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C604AF7D06@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'Jeff MacDonald'" <jeff@pgsql.com>, "'pgsql-hackers@postgreSQL.org'"
<pgsql-hackers@postgreSQL.org>
Subject: AW: [HACKERS] Locking
Date: Thu, 2 Mar 2000 09:54:57 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

I was reading the MVCC docs and came across this statement

"Postgres doesn't remember any information about
modified rows in memory and so has no limit to the
number of rows locked without lock escalation. "

And this is how i interpreted it.

you can have unlimited rows locked with out it going to a 'table
lock'

Do these two staements say the samething ?

Yes, exactly.

Andreas

From bouncefilter Thu Mar 2 04:04:54 2000
Received: from gandalf.it-austria.net (gandalf.it-austria.net [213.150.1.65])
by hub.org (8.9.3/8.9.3) with ESMTP id EAA60482
for <pgsql-hackers@postgreSQL.org>; Thu, 2 Mar 2000 04:04:28 -0500 (EST)
(envelope-from ZeugswetterA@wien.spardat.at)
Received: from sdexcgtw01.f000.d0188.sd.spardat.at (sdgtw.sd.spardat.at
[172.18.1.16])
by gandalf.it-austria.net (xxx/xxx) with ESMTP id KAA194318;
Thu, 2 Mar 2000 10:03:17 +0100
Received: by sdexcgtw01.f000.d0188.sd.spardat.at with Internet Mail Service
(5.5.2448.0) id <1TSZH166>; Thu, 2 Mar 2000 10:03:17 +0100
Message-ID:
<219F68D65015D011A8E000006F8590C604AF7D07@sdexcsrv1.f000.d0188.sd.spardat.at>
From: Zeugswetter Andreas SB <ZeugswetterA@wien.spardat.at>
To: "'Tom Lane'" <tgl@sss.pgh.pa.us>, "'Jose Soares'" <jose@sferacarta.com>,
"'hackers'" <pgsql-hackers@postgreSQL.org>
Subject: AW: [HACKERS] having and union in v7beta
Date: Thu, 2 Mar 2000 10:03:14 +0100
MIME-Version: 1.0
X-Mailer: Internet Mail Service (5.5.2448.0)
Content-Type: text/plain;
charset="iso-8859-1"

play=> explain select * from comuni union select * from comuni;

*However*, we have not fixed the bug that causes "select foo union
select foo" to be incorrectly simplified --- the UNION code is still
applying cnfify. (Which it probably shouldn't, but I haven't wanted
to touch that code until I have the time to rewrite it completely.)
The reason 7.0beta1 generates the "right" answer is that it has a
recently-introduced bug in the comparison routines that causes it to
think the two select subqueries aren't the same.

But if the two queries are the same, the union CAN be simplified,
since the union of two identical masses (I don't know the correct word here)
is still that one mass.

Thus 6.5 simplification is correct in this particular case.

Andreas

From bouncefilter Thu Mar 2 06:14:56 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 GAA05727
for <pgsql-hackers@postgreSQL.org>; Thu, 2 Mar 2000 06:14:22 -0500 (EST)
(envelope-from vev@michvhf.com)
Received: (qmail 16191 invoked by uid 1001); 2 Mar 2000 11:14:22 -0000
Date: Thu, 2 Mar 2000 06:14:22 -0500 (EST)
From: Vince Vielhaber <vev@michvhf.com>
To: Thomas Lockhart <lockhart@alumni.caltech.edu>
cc: Tom Lane <tgl@sss.pgh.pa.us>, Alfred Perlstein <bright@wintelcom.net>,
PostgreSQL Development <pgsql-hackers@postgreSQL.org>
Subject: Re: [HACKERS] bitten by docs
In-Reply-To: <38BE0E57.107D1FC3@alumni.caltech.edu>
Message-ID: <Pine.BSF.4.05.10003020605350.16161-100000@paprika.michvhf.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Thu, 2 Mar 2000, Thomas Lockhart wrote:

This was discussed a while ago. I thought we had agreed that we needed
to keep two sets of docs on the website, one for the last stable release
(ie, 6.5 currently) and one for the current development tip. But I
guess nothing's been done about it yet...

Perhaps we agreed what should be done, but I'm not sure anyone agreed
to actually do it.

Vince, could you take ownership of this issue (at least to get it on a
ToDo list so we don't forget)? I'm happy to help (will probably need
to redirect the nightly html production to another area of the web
site) but I'm reluctant to muck around with the overall structure of
the site, at least without some adult supervision...

Actually I had planned on doing it with the 7.0 release. I have a number
of website changes to do and things like two sets of docs are considered
a major change - or at least I consider it one. If you can redirect any
release docs to the release-doc directory I just created on the website
that'd help - otherwise it'll hafta wait till 7.0 goes to release.

Import Notes

Reference msg id not found: 200003010618.BAA18585@candle.pha.pa.us