[HACKERS] Script to compute randon page cost

[Hans-Jürgen Schönig]

Linux RedHat 7.3 (ext3, kernel 2.4.18-3)
512MB Ram
AMD Athlon 500
IBM 120GB IDE


[hs@backup hs]$ ./randcost.sh /data/db/
Collecting sizing information ...
Running random access timing test ...
Running sequential access timing test ...

random_page_cost = 0.901961



[hs@backup hs]$ ./randcost.sh /data/db/
Collecting sizing information ...
Running random access timing test ...
Running sequential access timing test ...

random_page_cost = 0.901961


Great script - it should be in contrib.

Hans



-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at , cluster.postgresql.at 
, www.cybertec.at 
, kernel.cybertec.at 


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

Re: [HACKERS] Script to compute random page cost

[Dave Page]

Dell Inspiron 8100 laptop, 1.2GHz Pentium, 512Mb RAM, Windows XP Pro
CYGWIN_NT-5.1 PC9 1.3.10(0.51/3/2) 2002-02-25 11:14 i686 unknown

random_page_cost = 0.924119

Regards, Dave.


> -Original Message-
> From: Bruce Momjian [mailto:[EMAIL PROTECTED]] 
> Sent: 09 September 2002 07:14
> To: PostgreSQL-development
> Subject: Re: [HACKERS] Script to compute random page cost
> 
> 
> 
> OK, turns out that the loop for sequential scan ran fewer 
> times and was skewing the numbers.  I have a new version at:
> 
>   ftp://candle.pha.pa.us/pub/postgresql/randcost
> 
> I get _much_ lower numbers now for random_page_cost.

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Script to compute random page cost

[Tatsuo Ishii]

> OK, turns out that the loop for sequential scan ran fewer times and was
> skewing the numbers.  I have a new version at:
> 
>   ftp://candle.pha.pa.us/pub/postgresql/randcost
> 
> I get _much_ lower numbers now for random_page_cost.

I got:

random_page_cost = 1.047619

Linux kernel 2.4.18
Pentium III 750MHz
Memory 256MB
IDE HDD

(A notebook/SONY VAIO PCG-Z505CR/K)
--
Tatsuo Ishii

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

[HACKERS] PREPARE code notes

[Karel Zak]

 Probably nothing important, but I saw it in
 src/backend/commands/prepare.c:

 1/ ExecuteQuery() (line 110). Why is needful use copyObject()? The
PostgreSQL executor modify query planns? I think copyObject() is
expensive call.

 2/ Lines 236 -- 245. Why do you "check for pre-existing entry of
same name" if you create hash table? I think better is use "else"
for this block of code and check it only if hash table already
exist.
 
 3/ Last is cosmetic: see line 404, what happen if memory context
is not valid? :-) (maybe use some elog() call)

  Thanks
Karel

-- 
 Karel Zak  <[EMAIL PROTECTED]>
 http://home.zf.jcu.cz/~zakkr/
 
 C, PostgreSQL, PHP, WWW, http://docs.linux.cz, http://mape.jcu.cz

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

http://archives.postgresql.org

Re: [HACKERS] Script to compute randon page cost

[Christopher Kings-Lynne]

Assuming it's giving out correct information, there seems to be a lot of
evidence for dropping the default random_page_cost to 1...

Chris

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Hans-Jürgen
> Schönig
> Sent: Monday, 9 September 2002 4:06 PM
> To: pgsql-hackers
> Subject: [HACKERS] Script to compute randon page cost
>
>
> Linux RedHat 7.3 (ext3, kernel 2.4.18-3)
> 512MB Ram
> AMD Athlon 500
> IBM 120GB IDE
>
>
> [hs@backup hs]$ ./randcost.sh /data/db/
> Collecting sizing information ...
> Running random access timing test ...
> Running sequential access timing test ...
>
> random_page_cost = 0.901961
>
>
>
> [hs@backup hs]$ ./randcost.sh /data/db/
> Collecting sizing information ...
> Running random access timing test ...
> Running sequential access timing test ...
>
> random_page_cost = 0.901961
>
>
> Great script - it should be in contrib.
>
> Hans
>
>
>
> --
> *Cybertec Geschwinde u Schoenig*
> Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
> Tel: +43/1/913 68 09; +43/664/233 90 75
> www.postgresql.at , cluster.postgresql.at
> , www.cybertec.at
> , kernel.cybertec.at 
>
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
>


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

Re: [HACKERS] Script to compute randon page cost

[Hans-Jürgen Schönig]

Christopher Kings-Lynne wrote:

>Assuming it's giving out correct information, there seems to be a lot of
>evidence for dropping the default random_page_cost to 1...
>
>Chris
>  
>
Some time ago Joe Conway suggest a tool based on a genetic algorithm 
which tries to find the best parameter settings.
As input the user could use a set of SQL statements. The algorithm will 
try to find those settings which lead to the lowest execution time based 
on the set of SQL.

What about something like that?
This way people could tune the database theirselves.

Hans


-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at , cluster.postgresql.at 
, www.cybertec.at 
, kernel.cybertec.at 


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Script to compute random page cost

[Curt Sampson]

On Mon, 9 Sep 2002, Bruce Momjian wrote:

> What do other people get for this value?

With your new script, with a 1.5 GHz Athlon, 512 MB RAM, and a nice fast
IBM 7200 RPM IDE disk, I get random_page_cost = 0.93.

> One flaw in this test is that it randomly reads blocks from different
> files rather than randomly reading from the same file.  Do people have a
> suggestion on how to correct this?  Does it matter?

>From my quick glance, it also does a lot of work work to read each
block, including forking off serveral other programs. This would tend to
push up the cost of a random read. You might want to look at modifying
the randread program (http://randread.sourceforge.net) to do what you
want

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC


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

Re: [HACKERS] contrib/ intarray, ltree, intagg broken(?) by array

[Teodor Sigaev]

> intarray and ltree both seem to be mapping their own declarations onto
> arrays using largely-similar code.  But while intarray fails its
> regression test, I find ltree still passes.  So I'm confused about what
> that code is really doing and don't want to touch it.

Please, apply attached patch, it solves the problem.


-- 
Teodor Sigaev
[EMAIL PROTECTED]




intarray_patch.gz
Description: application/gzip


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

[HACKERS] pg_dump problems in upgrading

[Oliver Elphick]

I am trying to populate a 7.3 database from a 7.2 dump.  I used 7.3's
pg_dumpall, but this did not handle all the issues:

1. The language dumping needs to be improved:

CREATE FUNCTION plperl_call_handler () RETURNS opaque
   ^^
AS '/usr/local/pgsql/lib/plperl.so', 'plperl_call_handler'
LANGUAGE "C";
CREATE FUNCTION
GRANT ALL ON FUNCTION plperl_call_handler () TO PUBLIC;
GRANT
REVOKE ALL ON FUNCTION plperl_call_handler () FROM postgres;
REVOKE
CREATE TRUSTED PROCEDURAL LANGUAGE plperl HANDLER plperl_call_handler;
ERROR:  function plperl_call_handler() does not return type language_handler


2.  Either casts or extra default conversions may be needed:

CREATE TABLE cust_alloc_history (
customer character varying(8) NOT NULL,
product character varying(10) NOT NULL,
"year" integer DEFAULT date_part('year'::text, ('now'::text)::timestamp(6) 
with time zone) NOT NULL,
jan integer DEFAULT 0 NOT NULL,
feb integer DEFAULT 0 NOT NULL,
mar integer DEFAULT 0 NOT NULL,
apr integer DEFAULT 0 NOT NULL,
may integer DEFAULT 0 NOT NULL,
jun integer DEFAULT 0 NOT NULL,
jul integer DEFAULT 0 NOT NULL,
aug integer DEFAULT 0 NOT NULL,
sep integer DEFAULT 0 NOT NULL,
oct integer DEFAULT 0 NOT NULL,
nov integer DEFAULT 0 NOT NULL,
dbr integer DEFAULT 0 NOT NULL,
CONSTRAINT c_a_h_year CHECK (((float8("year") <= date_part('year'::text, 
('now'::text)::timestamp(6) with time zone)) AND ("year" > 1997)))
);
ERROR:  Column "year" is of type integer but default expression is of type double 
precision
You will need to rewrite or cast the expression


3. A view is being created before one of the tables it refers to. 
Should not views be created only at the very end?

-- 
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
 
 "Submit yourselves therefore to God. Resist the devil, 
  and he will flee from you."James 4:7 


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

http://archives.postgresql.org

Re: [HACKERS] bug?

[Tom Lane]

Joe Conway <[EMAIL PROTECTED]> writes:
> I found the following while poking around. RangeVarGetRelid takes a 
> second parameter that is intended to allow it to not fail, returning 
> InvalidOid instead. However it calls LookupExplicitNamespace, which does 
> not honor any such request, and happily generates an error on a bad 
> namespace name:

ISTR deciding that that was okay, and there was no need to clutter
LookupExplicitNamespace with an extra parameter.  Don't recall the
reasoning at the moment...

regards, tom lane

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

Re: [HACKERS] 7.3beta and ecpg

[Tom Lane]

Michael Meskes <[EMAIL PROTECTED]> writes:
> I didn't download the beta but compared the CVS checkouts and it appears
> the ecpg directory is still the one from 7.2 not the one tagged
> big_bison. Will this one be moved into the mainstream source?

Well, I think we can't do that until postgresql.org has a version of
bison installed that will compile it.  And I'm really hesitant to see us
depending on a beta version of bison.  Any word on a new bison official
release?

We still have a few weeks until the situation gets critical, but maybe
it is time to start thinking about a fallback plan...

regards, tom lane

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

http://archives.postgresql.org

Re: [HACKERS] Script to compute random page cost

[Mario Weilguni]

> What do other people get for this value?
> 
> Keep in mind if we increase this value, we will get a more sequential
> scans vs. index scans.

With the new script I get 0.929825 on 2 IBM DTLA 5400RPM (80GB) with a  3Ware
6400 Controller (RAID-1)

Best regards,
Mario Weilguni


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

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Script to compute random page cost

[Oliver Elphick]

On Mon, 2002-09-09 at 07:13, Bruce Momjian wrote:
> 
> OK, turns out that the loop for sequential scan ran fewer times and was
> skewing the numbers.  I have a new version at:
> 
>   ftp://candle.pha.pa.us/pub/postgresql/randcost
> 
> I get _much_ lower numbers now for random_page_cost.
> 
> ---

Five successive runs:

random_page_cost = 0.947368
random_page_cost = 0.894737
random_page_cost = 0.947368
random_page_cost = 0.894737
random_page_cost = 0.894737


linux 2.4.18 SMP
dual Athlon MP 1900+
512Mb RAM
SCSI

-- 
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
 
 "Submit yourselves therefore to God. Resist the devil, 
  and he will flee from you."James 4:7 


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

http://archives.postgresql.org

Re: [HACKERS] Script to compute random page cost

[Rod Taylor]

On Mon, 2002-09-09 at 02:13, Bruce Momjian wrote:
> 
> OK, turns out that the loop for sequential scan ran fewer times and was
> skewing the numbers.  I have a new version at:
> 
>   ftp://candle.pha.pa.us/pub/postgresql/randcost
> 
> I get _much_ lower numbers now for random_page_cost.

The current script pulls way more data for Sequential scan than random
scan now.

Random is pulling a single page (count=1 for dd) with every loop. 
Sequential does the same number of loops, but pulls count > 1 in each.

In effect, sequential is random with more data load -- which explains
all of the 0.9's.


  Rod Taylor


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

Re: [HACKERS] 7.3beta and ecpg

[Michael Meskes]

On Mon, Sep 09, 2002 at 09:38:39AM -0400, Tom Lane wrote:
> Well, I think we can't do that until postgresql.org has a version of
> bison installed that will compile it.  And I'm really hesitant to see us
> depending on a beta version of bison.  Any word on a new bison official
> release?

No news yet. They just said "as soon as possible". :-)

Michael
-- 
Michael Meskes
[EMAIL PROTECTED]
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[HACKERS] 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM

[Tim Knowles]

Hi,

Been playing with the 7.3beta1 version and I've noticed a small
problem with dependency checking when dropping a column.  If you have
a view which uses JOIN's to join tables then dropping a column will
fail on a dependency check, even though the column being dropped is
not used at all in the view.  If you join the tables in the WHERE
clause the column can be dropped without problems.

Please see below some example SQL to demonstrate:


-- wrap it all up in a transaction so we don't do anything permanent

BEGIN;

CREATE TABLE table1 (col_a text, col_b int);
CREATE TABLE table2 (col_b int, col_c text);

CREATE VIEW tester1 AS SELECT A.col_a,B.col_b FROM table1 A, table2 B
WHERE (b.col_b=a.col_b);

CREATE VIEW tester2 AS SELECT A.col_a,B.col_b FROM table2 B INNER JOIN
table1 A ON (b.col_b=a.col_b);

--Now try and drop column col_c from table2
ALTER TABLE table2 DROP COLUMN col_c RESTRICT;

--You should now get an error to say that col_c is a dependent object
in view tester2

ROLLBACK;


-- I have also noticied the following behaviour when using the SET
command with incorrect option names

SET anythingyoulike = 1,2

--will cause the error to be reported as ERROR: SET anythingyoulike
takes only one argument

SET anythingyoulike = 1
--will cause the error to be reported correctly ('anythingyoulike' is
not a valid option name)

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

http://www.postgresql.org/users-lounge/docs/faq.html

[HACKERS] [SCRIPT] pguniqchk -- checks uniqueness of unique constraints on tables

[David D. Kilzer]

>From the Department of Redundancy Department:

Attached is a perl script called 'pguniqchk'.  It checks the uniqueness
of unique constraints on tables in a PostgreSQL database using the
PG_TABLES and PG_INDEXES system "tables".

Why would this be useful?

If you're planning to dump and restore the database, this might be a
good sanity check to run before doing it.

Apparently, when such an impossible event occurs, the unique index on
the table only "sees" one of the duplicate rows.  In order to even query
both rows, one must run this SQL command (via psql) to turn off index
scans:

  => set enable_indexscan = off;

The attached script does this, then verifies the uniqueness of the
unique index by scanning the entire table.

It is probably useless for 99.999% of PostgreSQL users, but I thought
I'd share it just in case someone finds it useful, even if only
as a simple example of querying system tables.

How I found the problem:

I had a need to alter the data types of a column on two different tables
(VARCHAR(32) -> VARCHAR(128) and VARCHAR(128) -> TEXT) and drop a column
from another table.  The only way to do this in v7.1.x is to perform a
full dump and then restore.  When I tried to reload the data, I got
unique key violation errors, and data for two other tables did not load.

As it turns out, one table had a single pair of duplicate keys while the
other table had five pair of duplicates and one set of triplicates.

The incident happened around April 05, 2002 (from what I can tell of
the duplicated data), but hasn't happened since.  I was having SCSI
disk errors around that time on my production server, which is the prime
suspect.

NOTES:

- Only tested on PostgreSQL 7.1.3.

- When a UNIQUE INDEX is put on a NULLABLE column, duplicates with NULL
  values are possible.  This is a feature, though the script does not
  check for this case (so don't be alarmed if it finds something).

  7.4. Unique Indexes
  http://www.postgresql.org/idocs/index.php?indexes-unique.html

Dave


#!/usr/bin/perl
#
# pguniqchk.pl - Checks uniqueness of unique keys in tables of a
#   PostgreSQL database
#
# Copyright 2002 David D. Kilzer.  All rights reserved.
#
# This program is licensed under the same terms as Perl itself.
#

use strict;
use warnings;

use vars qw( $dbh );# database handle

use DBI;
use Getopt::Long;


my $VERSION = sprintf("%d.%02d", q$Revision: 1.3 $ =~ /(\d+)\.(\d+)/);

my $HELP = < \$opt_help,
'host|h=s'=> \$opt_host,
'port|p=s'=> \$opt_port,
'verbose' => \$opt_verbose,
'version|v'   => \$opt_version,
   );

if (   ! $ret_val
|| $opt_help
|| (scalar(@ARGV) < 3 && ! $opt_version)
   )
{
print STDERR $HELP;
exit (defined $opt_help ? 0 : 1);
}

if ($opt_version)
{
print STDERR "pguniqchk v$VERSION\n";
exit 0;
}

$db_dsn  = 'dbi:Pg:dbname=' . shift @ARGV;
$db_user = shift @ARGV;
$db_pass = shift @ARGV;

if (scalar(@ARGV) > 0)
{
@db_tables = @ARGV;
@ARGV = ();
}


#
# Handle various command-line arguments
#

if ($opt_host)
{
$db_dsn .= ';host=' . $opt_host;
}

if ($opt_port)
{
$db_dsn .= ';port=' . $opt_port;
}


#
# Connect to database
#

eval
{
$::dbh = DBI->connect($db_dsn, $db_user, $db_pass,
+{
RaiseError => 1,
 },
);
};

if ($@)
{
die "Error connecting to database: $@";
}


#
# Grab list of tables to check
#

if (scalar(@db_tables) < 1)
{

eval
{
local $::dbh->{RaiseError} = 1 if (! $::dbh->{RaiseError});

my $i = 0;  # counter
my $sth;# statement handle

$sth = $::dbh->prepare(execute();

while (my $r = $sth->fetchrow_arrayref())
{
push(@db_tables, $r->[0]);
}

$sth->finish();
};

if ($@)
{
$::dbh->disconnect();
die "Error querying list of tables from database: $@";
}

}


# 
# Check uniqueness of unique indices on each table
#

eval
{
local $::dbh->{RaiseError} = 1 if (! $::dbh->{RaiseError});

my $found_dups = 0;

# Turn off index scans so we may check for duplicate keys
print "### Disabling index scans ... " if ($opt_verbose);
$::dbh->do(qq{ set ENABLE_INDEXSCAN = off });
print "done\n" if ($opt_verbose);

foreach my $tab (@db_tables)
{
my $i = 0;  # counter
my $sth;# statement handle

$sth = $::dbh->prepare(execute();

while (my $r = $sth->fetchrow_arrayref())
{
my $constraint = $r->[1];
my $index_def = $r->[2];
$index_def =~ m/\(([^)]+)\)/;
my @cols = map( ${[ split(' ', $_) ]}[0], @{[ split(',', $1) ]} );
my $col = join(', ', @cols);

# $index_def above looks like this (on one line):
#   CREATE UNIQUE INDEX foo_bar_pkey ON foo_bar
#  USING

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Jan Wieck]

Steve Howe wrote:
> 
> Hello all,
> 
> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
> number of rows updated. But that is essential for applications, since
> without it of course we don't know if the updates/delete/insert
> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
> program will return an error message and rollback transaction thinking
> nothing have been updated. In other words, unusable.
> 
> This render views useless (I either use view with rules and don't get
> my program working) and won't allow me to proper use security settings
> on PostgreSQL...
> 
> This is a *major* issue in my opinion that appeared on a May thread
> but I can't see it done on version 7.2.2. Even worst, I can't see
> nothing on the TODO file.
> 
> Will this fix finally  appear on 7.3 ? Any ways to work around this ?
> How can I know at least if *something* succeeded, or how many rows
> (the proper behavior)?

And of course, in the case you insert into a real table you expect if a
trigger procedure suppressed your original INSERT, but fired a cascade
of other triggers by doing a mass UPDATE somewhere else instead, that
all these caused UPDATEs and whatnot's are summed up and returned
instead, right? Or what is proper behavior here?

So please, "proper behavior" is not allways what your favorite tool
expects. And just because you cannot "fix" your tool doesn't make that
behavior any more "proper".


Jan

> 
> Thank you very much.
> 
> -
> Best regards,
>  Steve Howe   mailto:[EMAIL PROTECTED]
> 
> ---(end of broadcast)---
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])


-- 

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [HACKERS] 7.3beta1 DROP COLUMN DEPENDENCY PROBLEM

[Rod Taylor]

On Fri, 2002-09-06 at 06:54, Tim Knowles wrote:
> Hi,
> 
> Been playing with the 7.3beta1 version and I've noticed a small
> problem with dependency checking when dropping a column.  If you have
> a view which uses JOIN's to join tables then dropping a column will

This has to do with the way the JOIN currently functions.  At the moment
the JOIN nodes record an alias which has all columns listed, which is
appropriately picked up by the dependency code.

Tom is debating whether or not the alias on columns not used in the
where or clause or returned is strictly necessary.

Indeed, if you delete the dependencies, then drop the column the view
continues to function but I'm not sure thats always the case.

 
-- 
  Rod Taylor


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] contrib/tsearch

[Teodor Sigaev]

> Should we check for stop words before stemming or after ?

Current implementation supports both variants. Look dictionary interface 
definition in morph.c:

typedef struct
{
 charlocalename[NAMEDATALEN];
 /* init dictionary */
 void   *(*init) (void);
 /* close dictionary */
 void(*close) (void *);
 /* find in dictionary */
 char   *(*lemmatize) (void *, char *, int *);
 int (*is_stoplemm) (void *, char *, int);
 int (*is_stemstoplemm) (void *, char *, int);
}   DICT;

'is_stoplemm'  method is called before 'lemmtize' and 'is_stemstoplemm' after.
dict/porter_english.dct at the end:
TABLE_DICT_START
 "C",
 setup_english_stemmer,
 closedown_english_stemmer,
 engstemming,
 NULL,
 is_stopengword
TABLE_DICT_END

dict/russian_stemming.dct:
TABLE_DICT_START
 "ru_RU.KOI8-R",
 NULL,
 NULL,
 ru_RUKOI8R_stem,
 ru_RUKOI8R_is_stopword,
 NULL
TABLE_DICT_END

So english stemmer defines is lexem stop or not after stemming, but russian before.



-- 
Teodor Sigaev
[EMAIL PROTECTED]



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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Jan Wieck]

Steve Howe wrote:
> 
> Hello Bruce,
> 
> Friday, September 6, 2002, 9:52:18 PM, you wrote:
> 
> BM> I am not any happier about it than you are.  Your report is good because
> BM> it is the first case where returning the wrong value actually breaks
> BM> software.  You may be able to justify adding a fix during beta by saying
> BM> it is a bug fix.
> Actually I think it must have happened with someone else, but they
> must have quit using rules or something...
> Actually I can't ensure security in the system without rules.
> 
> BM> Of course, someone is going to have to generate a patch and champion the
> BM> cause.  This stuff doesn't happen by magic.
> I understand your point. I just was hoping to see more concern about
> the issue by the developers... but that's been broken for months.
> 
> Unhappily I can't do it myself because it would take weeks to get
> familiar with the inners of PostgreSQL...
> 
> Let's hope someone realize how serious is this and make a fix.

Seems you at least realized how serious it is. Even if you cannot code
the "proper" solution, could you please make a complete table of all
possible situations and the expected returns? With complete I mean
including all combinations of rules, triggers, deferred constraints and
the like. Or do you at least see now where in the discussion we got
stuck?

It doesn't help to cry for a quick hack that fixes your particular
problem. That only leads to the situation that someday we have a final
fix that changes the behavior for your case again and then you cry again
and ask us not to break backwards compatibility.


Thanks for your patience and understanding,
Jan

> 
> Thanks again...
> -
> Best regards,
>  Steve Howe   mailto:[EMAIL PROTECTED]
> 
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]


-- 

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

http://archives.postgresql.org

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Jan Wieck]

Steve Howe wrote:
> 
> Hello Bruce,
> 
> Friday, September 6, 2002, 10:58:13 PM, you wrote:
> 
> BM> Well, there was a big discussion, and I did bring up the issue in early
> BM> August to see if I could get a resolution to it and was told no
> BM> conclusion could be made.
> 
> BM> I suggest you read the TODO detail on the item and make a proposal on
> BM> how it _should_ work and if you can get agreement from everyone, you may
> BM> be able to nag someone into doing a patch.
> I think it should return the number of rows modified in the context of
> the view, and not exactly that of each of the tables affected. And
> this would not work well with PQcmdStatus() because it returns a
> single integer entry only.
> 
> This was working on some previous build, wasn't it ? What was the
> previous behavior ? Shouldn't the patch follow that way ?

In previous versions rules even fired in different orders. We cannot get
back to that, because it was the reason for total failure of rules at
all. So no, the patch should follow that way.

You say that the return should be the rows modified in the context of
the view. Er ... what is that? You mean only INSERTS, UPDATES and
DELETES made by rule actions directly to any table referenced by the
view itself count, not if a modification to another third table or view
triggers back a modification to one of these base tables in return ...
would that be through a rule or a trigger?

What about a view over views, that has rules that in turn get rewritten
by the rewrite rules of the views it consists of? What is that views
context in detail?


Jan

> 
> -
> Best regards,
>  Steve Howe   mailto:[EMAIL PROTECTED]
> 
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster


-- 

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [HACKERS] Proposal: Solving the "Return proper effected tuple count

[Zeugswetter Andreas SB SD]

> I don't think we should add tuple counts from different commands, i.e.
> adding UPDATE and DELETE counts just yields a totally meaningless
> number.

Agreed.

 
> I don't think there is any need/desire to add additional API routines to
> handle multiple return values.

Yup.

> Can I get some votes on this?

I vote for Tom's proposal, especially regarding non instead rules (a note to Steve:
non instead rules are not for views).
I also think summing up is good, it would nicely fit the partitioned table 
requirements.  
And even if you imagine an insert statement with one row, even though I would be quite 
surprised if I got 3 rows inserted as an answer, I think it is the dba's 
responsibility 
to do the 2nd and 3rd row with a non instead rule or a trigger. 
For the same reason I would not restrict the count to one tag (do what you don't want 
in 
the count with a non instead rule or a trigger).

I would vote for OID from first or last command. And I would disregarding the tag, 
since that 
gives me the power to transparently move an updated table to a history keeping table, 
that only does inserts.

Whether first or last result is probably not so important, since the rule creator can 
influence what is done first/last, no ? You'd only need to know which. 

Andreas

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Map of developers

[Jan Wieck]

Christopher Kings-Lynne wrote:
> 
> Anyone else think we should add some more pins to the developer map?  At the
> moment, it looks like we have very few developers!

We might as well refresh  that thing a bit. I haven't been to Hamburg
since April 2001! Vince already has my ... er ... rather old coordinates
here in Massachusetts and a newer photo. 

Other pin's that need an update?


Jan

-- 

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Zeugswetter Andreas SB SD]

> could you please make a complete table of all
> possible situations and the expected returns? With complete I mean
> including all combinations of rules, triggers, deferred constraints and
> the like. Or do you at least see now where in the discussion we got
> stuck?

Imho only view rules (== instead rules) should affect the returned info.
Not "non instead" rules, triggers or constraints. Those are imho supposed to 
be transparent as long as they don't abort the statement. 

Especially for triggers and constraints there is no room for flexibility,
since other db's also don't modify the "affected rows" count for these.
Think sqlca.sqlerrd[2] /* number of rows processed */!

Andreas

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

http://archives.postgresql.org

Re: [HACKERS] Script to compute randon page cost

[Tom Lane]

"Christopher Kings-Lynne" <[EMAIL PROTECTED]> writes:
> Assuming it's giving out correct information, there seems to be a lot of
> evidence for dropping the default random_page_cost to 1...

The fact that a lot of people are reporting numbers below 1 is
sufficient evidence that the script is broken.  A value below 1
is physically impossible.

regards, tom lane

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

Re: [HACKERS] PREPARE code notes

[Tom Lane]

Karel Zak <[EMAIL PROTECTED]> writes:
>  1/ ExecuteQuery() (line 110). Why is needful use copyObject()? The
> PostgreSQL executor modify query planns?

Yes, and yes.  Unfortunately.

>  2/ Lines 236 -- 245. Why do you "check for pre-existing entry of
> same name" if you create hash table? I think better is use "else"
> for this block of code and check it only if hash table already
> exist.

The code reads more cleanly as-is; changing it as you suggest would
create an unnecessary interdependency between two logically distinct
concerns.
 
>  3/ Last is cosmetic: see line 404, what happen if memory context
> is not valid? :-) (maybe use some elog() call)

Or just get rid of the MemoryContextIsValid test --- it shouldn't
ever not be valid.  Not very important though.

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] bug?

[Tom Lane]

I said:
> Joe Conway <[EMAIL PROTECTED]> writes:
>> I found the following while poking around. RangeVarGetRelid takes a 
>> second parameter that is intended to allow it to not fail, returning 
>> InvalidOid instead. However it calls LookupExplicitNamespace, which does 
>> not honor any such request, and happily generates an error on a bad 
>> namespace name:

> ISTR deciding that that was okay, and there was no need to clutter
> LookupExplicitNamespace with an extra parameter.  Don't recall the
> reasoning at the moment...

After looking: the only place that calls RangeVarGetRelid with a "true"
second parameter is tcop/utility.c, and it just does it so that it can
give a different error message for the "relation not found" case.  Thus,
we don't actually *want* failures other than "relation not found" to
return from RangeVarGetRelid.  So the code is right as-is.  Perhaps the
comments could stand improvement though, to make it clearer what failOK
is meant to do.

regards, tom lane

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

http://archives.postgresql.org

Re: [HACKERS] bug?

[Joe Conway]

Tom Lane wrote:
>>Joe Conway <[EMAIL PROTECTED]> writes:
>>
>>>I found the following while poking around. RangeVarGetRelid takes a 
>>>second parameter that is intended to allow it to not fail, returning 
>>>InvalidOid instead. However it calls LookupExplicitNamespace, which does 
>>>not honor any such request, and happily generates an error on a bad 
>>>namespace name:
>>
> 
>>ISTR deciding that that was okay, and there was no need to clutter
>>LookupExplicitNamespace with an extra parameter.  Don't recall the
>>reasoning at the moment...
> 
> 
> After looking: the only place that calls RangeVarGetRelid with a "true"
> second parameter is tcop/utility.c, and it just does it so that it can
> give a different error message for the "relation not found" case.  Thus,
> we don't actually *want* failures other than "relation not found" to
> return from RangeVarGetRelid.  So the code is right as-is.  Perhaps the
> comments could stand improvement though, to make it clearer what failOK
> is meant to do.

OK. The reason I brought it up was that while working on the plpgsql patch 
(posted last night), I found that plpgsql gives a better error message if 
RangeVarGetRelid returns InvalidOid instead of simply elog'ing. The comments 
did lead me to believe I could get an InvalidOid, so I was surprized when I 
didn't.

Joe




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

http://archives.postgresql.org

Re: [HACKERS] Script to compute randon page cost

[Brett Schwarz]

On Mon, 2002-09-09 at 01:16, Hans-Jürgen Schönig wrote:
> Christopher Kings-Lynne wrote:
> 
> >Assuming it's giving out correct information, there seems to be a lot of
> >evidence for dropping the default random_page_cost to 1...
> >
> >Chris
> >  
> >
> Some time ago Joe Conway suggest a tool based on a genetic algorithm 
> which tries to find the best parameter settings.
> As input the user could use a set of SQL statements. The algorithm will 
> try to find those settings which lead to the lowest execution time based 
> on the set of SQL.
> 
> What about something like that?
> This way people could tune the database theirselves.
> 

I actually had starting coding a tool like this, but have become
distracted with other things. I plan on continuing with it maybe next
week. If anyone has suggestions, please let me know...

--brett


-- 
Brett Schwarz
brett_schwarz AT yahoo.com


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Script to compute random page cost

[Nick Fankhauser]

Bruce-

With the change in the script that I mentioned to you off-list (which I
believe just pointed it at our "real world" data), I got the following
results with 6 successive runs on each of our two development platforms:

(We're running PGSQL 7.2.1 on Debian Linux 2.4)

System 1:
1.2 GHz Athlon Processor, 512MB RAM, Database on IDE hard drive
random_page_cost = 0.857143
random_page_cost = 0.809524
random_page_cost = 0.809524
random_page_cost = 0.809524
random_page_cost = 0.857143
random_page_cost = 0.884615

System 2:
Dual 1.2Ghz Athlon MP Processors, SMP enabled, 1 GB RAM, Database on Ultra
SCSI RAID 5 with Hardware controller.
random_page_cost = 0.894737
random_page_cost = 0.842105
random_page_cost = 0.894737
random_page_cost = 0.894737
random_page_cost = 0.842105
random_page_cost = 0.894737


I was surprised that the SCSI RAID drive is generally slower than IDE, but
the values are in line with the results that others have been getting.

-Nick

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian
> Sent: Monday, September 09, 2002 1:14 AM
> To: PostgreSQL-development
> Subject: Re: [HACKERS] Script to compute random page cost
>
>
>
> OK, turns out that the loop for sequential scan ran fewer times and was
> skewing the numbers.  I have a new version at:
>
>   ftp://candle.pha.pa.us/pub/postgresql/randcost
>
> I get _much_ lower numbers now for random_page_cost.


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Map of developers

[Vince Vielhaber]

On Mon, 9 Sep 2002, Jan Wieck wrote:

> Christopher Kings-Lynne wrote:
> >
> > Anyone else think we should add some more pins to the developer map?  At the
> > moment, it looks like we have very few developers!
>
> We might as well refresh  that thing a bit. I haven't been to Hamburg
> since April 2001! Vince already has my ... er ... rather old coordinates
> here in Massachusetts and a newer photo.
>
> Other pin's that need an update?

Still don't know where Peter's going to be so his pin may end up
in Dresden.  I've had zero success in getting that tcl tool to work
which is the current holdup but I do have all the updates I know of
that are needed.

Vince.
-- 
==
Vince Vielhaber -- KA8CSHemail: [EMAIL PROTECTED]http://www.pop4.net
 56K Nationwide Dialup from $16.00/mo at Pop4 Networking
  http://www.camping-usa.com  http://www.cloudninegifts.com
   http://www.meanstreamradio.com   http://www.unknown-artists.com
==




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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Steve Howe]

Hello Jan,

Monday, September 9, 2002, 11:26:20 AM, you wrote:

JW> Steve Howe wrote:
>> 
>> Hello Bruce,
>> 
>> Friday, September 6, 2002, 9:52:18 PM, you wrote:
>> 
>> BM> I am not any happier about it than you are.  Your report is good because
>> BM> it is the first case where returning the wrong value actually breaks
>> BM> software.  You may be able to justify adding a fix during beta by saying
>> BM> it is a bug fix.
>> Actually I think it must have happened with someone else, but they
>> must have quit using rules or something...
>> Actually I can't ensure security in the system without rules.
>> 
>> BM> Of course, someone is going to have to generate a patch and champion the
>> BM> cause.  This stuff doesn't happen by magic.
>> I understand your point. I just was hoping to see more concern about
>> the issue by the developers... but that's been broken for months.
>> 
>> Unhappily I can't do it myself because it would take weeks to get
>> familiar with the inners of PostgreSQL...
>> 
>> Let's hope someone realize how serious is this and make a fix.

JW> Seems you at least realized how serious it is. Even if you cannot code
"At least" ?... What do you mean by that ?

JW> the "proper" solution, could you please make a complete table of all
JW> possible situations and the expected returns? With complete I mean
JW> including all combinations of rules, triggers, deferred constraints and
JW> the like. Or do you at least see now where in the discussion we got
JW> stuck?
I had seen and the proposal was posted two days ago.

JW> It doesn't help to cry for a quick hack that fixes your particular
JW> problem. That only leads to the situation that someday we have a final
JW> fix that changes the behavior for your case again and then you cry again
JW> and ask us not to break backwards compatibility.
See, I'm not crying. I'm just another user who needs something
working. The whole problem was that the PostgreSQL knew the problem
existed, had a brief discussion on the subject, and couldn't reach an
agreement. That's ok for me, I understand... but releasing versions
known to be broken is something I can't understand.

- 
Best regards,
 Steve Howe   mailto:[EMAIL PROTECTED]


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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Steve Howe]

Hello Jan,

Monday, September 9, 2002, 11:26:20 AM, you wrote:

JW> Seems you at least realized how serious it is. Even if you cannot code
JW> the "proper" solution, could you please make a complete table of all
JW> possible situations and the expected returns? With complete I mean
JW> including all combinations of rules, triggers, deferred constraints and
JW> the like. Or do you at least see now where in the discussion we got
JW> stuck?
By the way, I don't think triggers and constraints are in focus here,
just as rules other then "DO INSTEAD".
These should be transparent to the user.
I suggest you to read the proposal posted to get aware of the
discussion.

Thanks.

- 
Best regards,
 Steve Howe   mailto:[EMAIL PROTECTED]


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

Re: [HACKERS] Map of developers

[Jan Wieck]

Vince Vielhaber wrote:
> 
> Still don't know where Peter's going to be so his pin may end up
> in Dresden.  I've had zero success in getting that tcl tool to work
> which is the current holdup but I do have all the updates I know of
> that are needed.

You mean that Tcl/Tk application that manages the imagemap for the
popups? What's the problem with it?


Jan

-- 

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Steve Howe]

Hello Jan,

Monday, September 9, 2002, 11:15:47 AM, you wrote:

JW> Steve Howe wrote:
>> 
>> Hello all,
>> 
>> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
>> number of rows updated. But that is essential for applications, since
>> without it of course we don't know if the updates/delete/insert
>> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
>> program will return an error message and rollback transaction thinking
>> nothing have been updated. In other words, unusable.
>> 
>> This render views useless (I either use view with rules and don't get
>> my program working) and won't allow me to proper use security settings
>> on PostgreSQL...
>> 
>> This is a *major* issue in my opinion that appeared on a May thread
>> but I can't see it done on version 7.2.2. Even worst, I can't see
>> nothing on the TODO file.
>> 
>> Will this fix finally  appear on 7.3 ? Any ways to work around this ?
>> How can I know at least if *something* succeeded, or how many rows
>> (the proper behavior)?

JW> And of course, in the case you insert into a real table you expect if a
JW> trigger procedure suppressed your original INSERT, but fired a cascade
JW> of other triggers by doing a mass UPDATE somewhere else instead, that
JW> all these caused UPDATEs and whatnot's are summed up and returned
JW> instead, right? Or what is proper behavior here?
What is documented, and what is expected: PQcmdStatus(),
PQcmdTuples()and PQoidValue() returning the information they should be.

JW> So please, "proper behavior" is not allways what your favorite tool
JW> expects. And just because you cannot "fix" your tool doesn't make that
JW> behavior any more "proper".
Do you have any word more appropriate ?

And just so that you know, I can't "fix" my tool because I have other
job to do (and a lot of that and that job uses PostgreSQL), and
unhappily I couldn't join the development team and thus I'm not aware
of how it works internally. The reason isn't that I just don't have
intellectual capacity.

And it looks like *you* overhauled the query rewrite rule system, so
what we are talking is something that must have passed through you. So
instead of offending me, your "proper" behavior would be try to help
and suggest a solution for the problem, as other developers are doing.

Thanks again.

- 
Best regards,
 Steve Howe   mailto:[EMAIL PROTECTED]


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

Re: [HACKERS] Script to compute random page cost

[scott.marlowe]

I'm getting an infinite wait on that file, could someone post it to the 
list please?



On Mon, 9 Sep 2002, Bruce Momjian wrote:

> 
> OK, turns out that the loop for sequential scan ran fewer times and was
> skewing the numbers.  I have a new version at:
> 
>   ftp://candle.pha.pa.us/pub/postgresql/randcost
> 
> I get _much_ lower numbers now for random_page_cost.


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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Rod Taylor]

> existed, had a brief discussion on the subject, and couldn't reach an
> agreement. That's ok for me, I understand... but releasing versions
> known to be broken is something I can't understand.
 -9' the postmaster

If we didn't do that, then Postgresql never would have been released in
the first place, nor any date between then and now.

There has been, and currently is a ton of known broken, wonky, or
incomplete stuff -- but it's felt that the current version has a lot
more to offer than the previous version, so it's being released.

This works for *all* software.  If you never release, nothing gets
better.


I suspect it'll be several more major releases before we begin to
consider it approaching completely functional.

-- 
  Rod Taylor


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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Stephan Szabo]

On Mon, 9 Sep 2002, Steve Howe wrote:

> JW> Steve Howe wrote:
> >>
> >> Hello all,
> >>
> >> PostgreSQL *still* has a bug where PQcmdStatus() won't return the
> >> number of rows updated. But that is essential for applications, since
> >> without it of course we don't know if the updates/delete/insert
> >> commands succeded. Even worst, on interfaces like Delphi/dbExpress the
> >> program will return an error message and rollback transaction thinking
> >> nothing have been updated. In other words, unusable.

As a note, I assume you realize that it returning any number doesn't
guarantee that the command succeeded if you assume succeeding means doing
what the statement sent would appear to do. ;) Although I think
we need to change the current behavior, we are turning a false "failure"
into a potentially false "success" (I did an update, it said two rows were
changed but there's no visible data change in the entire system?)
Fortunately, the likely bad effects from the false "success" are probably
only going to happen in somewhat degenerate cases.

I quote "failure" and "success" because there's already a notion of
success and failure which is raising an exception condition or not (AFAICT
0 rows is a completion condition - the statement succeeded but nothing was
modified). As such, using the count to determine success of the statement
is wrong for an interface, but it may be meaningful for applications
attempting to apply some sort of business logic.


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Dann Corbit]

> -Original Message-
> From: Rod Taylor [mailto:[EMAIL PROTECTED]] 
> Sent: Monday, September 09, 2002 10:55 AM
> To: Steve Howe
> Cc: PostgreSQL-development
> Subject: Re: [HACKERS] Rule updates and PQcmdstatus() issue
> 
> 
> > existed, had a brief discussion on the subject, and 
> couldn't reach an 
> > agreement. That's ok for me, I understand... but releasing versions 
> > known to be broken is something I can't understand.
>  -9' the postmaster
> 
> If we didn't do that, then Postgresql never would have been 
> released in the first place, nor any date between then and now.
> 
> There has been, and currently is a ton of known broken, 
> wonky, or incomplete stuff -- but it's felt that the current 
> version has a lot more to offer than the previous version, so 
> it's being released.
> 
> This works for *all* software.  If you never release, nothing 
> gets better.
> 
> 
> I suspect it'll be several more major releases before we 
> begin to consider it approaching completely functional.

I believe that the surprise is at the focus, when it comes to a release.
With commercial products (anyway) if you have any sort of show-stopper
bug (crashing, incorrect results, etc.) you do not release the tool
until the bug, and all others like it, are fixed.  Bugs that have to do
with appearance or convenience can be overlooked for a release as long
as they are documented in the release notes.  Now, it is not unlikely
that there are unintentional show-stopper bugs that get through Q/A.
But intentionally passing them through would be incompetent for a
commercial enterprise.

With open source projects, the empasis tends to be on features, with far
less regard for correcting known problems.  Even bugs that can cause a
crash seem to be viewed as acceptable if they happen rarely.

Now, at first blush, the open source strategy seems ludicrous.  After
all, who will want to use a product which could potentially (albeit
unlikely) destroy your data or give wrong results?  Then, after a bit of
thought, you can see that the same sort of strategy as the open source
projects *is* followed by one very large and very successful software
giant.  So maybe "burgeoning featuritis without extreme concern for
robust stability" isn't such a stupid strategy after all. ;-)

All kidding aside, I would like to see increased emphasis on stability
and correctness.  But I will admit that it is a lot less fun than adding
new features.

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

http://archives.postgresql.org

Re: [HACKERS] Script to compute random page cost

[Nick Fankhauser]

Hi again-

I bounced these numbers off of Ray Ontko here at our shop, and he pointed
out that random page cost is measured in multiples of a sequential page
fetch. It seems almost impossible that a random fetch would be less
expensive than a sequential fetch, yet we all seem to be getting results <
1. I can't see anything obviously wrong with the script, but something very
odd is going.

-Nick

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Nick Fankhauser
> Sent: Monday, September 09, 2002 11:25 AM
> To: Bruce Momjian; PostgreSQL-development
> Cc: Ray Ontko
> Subject: Re: [HACKERS] Script to compute random page cost
>
>
> Bruce-
>
> With the change in the script that I mentioned to you off-list (which I
> believe just pointed it at our "real world" data), I got the following
> results with 6 successive runs on each of our two development platforms:
>
> (We're running PGSQL 7.2.1 on Debian Linux 2.4)
>
> System 1:
> 1.2 GHz Athlon Processor, 512MB RAM, Database on IDE hard drive
> random_page_cost = 0.857143
> random_page_cost = 0.809524
> random_page_cost = 0.809524
> random_page_cost = 0.809524
> random_page_cost = 0.857143
> random_page_cost = 0.884615
>
> System 2:
> Dual 1.2Ghz Athlon MP Processors, SMP enabled, 1 GB RAM, Database on Ultra
> SCSI RAID 5 with Hardware controller.
> random_page_cost = 0.894737
> random_page_cost = 0.842105
> random_page_cost = 0.894737
> random_page_cost = 0.894737
> random_page_cost = 0.842105
> random_page_cost = 0.894737
>
>
> I was surprised that the SCSI RAID drive is generally slower than IDE, but
> the values are in line with the results that others have been getting.
>
> -Nick
>
> > -Original Message-
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian
> > Sent: Monday, September 09, 2002 1:14 AM
> > To: PostgreSQL-development
> > Subject: Re: [HACKERS] Script to compute random page cost
> >
> >
> >
> > OK, turns out that the loop for sequential scan ran fewer times and was
> > skewing the numbers.  I have a new version at:
> >
> > ftp://candle.pha.pa.us/pub/postgresql/randcost
> >
> > I get _much_ lower numbers now for random_page_cost.
>
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Importing data from 7.2.2 into 7.3b1 !?

[Peter Eisentraut]

Hervé Piedvache writes:

> Any idea ?

No.

We need the complete details (including the input files), not vague
observations.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] SIMILAR TO

[Peter Eisentraut]

Thomas Lockhart writes:

> > SIMILAR TO doesn't implement the SQL standard, it's only a wrapper around
> > the POSIX regexp matching, which is wrong.  I thought someone wanted to
> > fix that, but if it's not happening it should be removed.
>
> Please be specific on what you would consider correct. I'm not recalling
> any details of past discussions so need some background.

The pattern that should be accepted by SIMILAR TO (as defined in SQL99
part 2 clause 8.6) and the POSIX regular expressions that it accepts now
are not the same.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] Proposal: Solving the "Return proper effected tuple

[Peter Eisentraut]

Steve Howe writes:

> Here are the proposals for solutioning the "Return proper effected
> tuple count from complex commands [return]" issue as seen on TODO.
>
> Any comments ?... This is obviously open to voting and discussion.

We don't have a whole lot of freedom in this; this area is covered by the
SQL standard.  The major premise in the standard's point of view is that
views are supposed to be transparent.  That is, if

SELECT * FROM my_view WHERE condition;

return N rows, then a subsequently executed

UPDATE my_view SET ... WHERE condition;

returns an update count of N, no matter what happens behind the scenes.  I
don't think this matches Tom Lane's view exactly, but it's a lot closer
than your proposal.

-- 
Peter Eisentraut   [EMAIL PROTECTED]


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

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[Tom Lane]

Bruce Momjian <[EMAIL PROTECTED]> writes:
> Barry Lind wrote:
>> How should client interfaces handle this new autocommit feature?  Is it
>> best to just issue a set at the beginning of the connection to ensure
>> that it is always on?

> Yes, I thought that was the best fix for apps that can't deal with
> autocommit being off.

If autocommit=off really seriously breaks JDBC then I don't think a
simple SET command at the start of a session is going to do that much
to improve robustness.  What if the user issues another SET to turn it
on?

I'd suggest just documenting that it is broken and you can't use it,
until such time as you can get it fixed.  Band-aids that only partially
cover the problem don't seem worth the effort to me.

In general I think that autocommit=off is probably going to be very
poorly supported in the 7.3 release.  We can document it as being
"work in progress, use at your own risk".

regards, tom lane

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

http://archives.postgresql.org

Re: [HACKERS] Impossible to import pg_dumpall from 7.2.2 to 7.3b1

[Tom Lane]

=?iso-8859-1?B?SGVydukgUGllZHZhY2hl?= <[EMAIL PROTECTED]> writes:
> But when I try to import it inside 7.3b1 I get this :
> (seems that the copy command is not fully compatible with the 7.2.2 
> pg_dumpall ?)

> Many thinks like this : (I have only copied some parts ...)
> Size of the dump about 1.5 Gb ...

> Query buffer reset (cleared).
> psql:/tmp/dump_mybase.txt:1015274: invalid command \nPour
> Query buffer reset (cleared).

It seems pretty clear that the COPY command itself failed, leaving psql
trying to interpret the following data as SQL commands.  But you have
not shown us either the COPY command or the error message it generated,
so there's not a lot we can say about it...

regards, tom lane

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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Andrew Sullivan]

On Mon, Sep 09, 2002 at 11:30:52AM -0700, Dann Corbit wrote:
> 
> All kidding aside, I would like to see increased emphasis on stability
> and correctness.  But I will admit that it is a lot less fun than adding
> new features.

But in fairness, I think you'd be hard pressed to find a set of
developers anywhere who take more seriously that the PostgreSQL core
the responsibility to provide stable, correct software.  I've
reported show-stopping bugs to commercial database providers and on
the PostgreSQL lists, and I'd be hard pressed to come up with an
occasion where I received from a commercial software company service
that was even 1/10th the quality and speed that I get here.  

A

-- 

Andrew Sullivan 204-4141 Yonge Street
Liberty RMS   Toronto, Ontario Canada
<[EMAIL PROTECTED]>  M2P 2A8
 +1 416 646 3304 x110


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Ross J. Reedstrom]

On Mon, Sep 09, 2002 at 11:30:52AM -0700, Dann Corbit wrote:
> > 
> > I suspect it'll be several more major releases before we 
> > begin to consider it approaching completely functional.
> 
> I believe that the surprise is at the focus, when it comes to a release.
> With commercial products (anyway) if you have any sort of show-stopper
> bug (crashing, incorrect results, etc.) you do not release the tool
> until the bug, and all others like it, are fixed.  Bugs that have to do
> with appearance or convenience can be overlooked for a release as long
> as they are documented in the release notes.  Now, it is not unlikely
> that there are unintentional show-stopper bugs that get through Q/A.
> But intentionally passing them through would be incompetent for a
> commercial enterprise.

Hmm, you don't have any drinking buddies who work QA, do you? _Lots_ of
known, "eat your harddrive" bugs get classified as "to be fixed in future
release" in commercial software, when the release date pressure grows.

> With open source projects, the empasis tends to be on features, with far
> less regard for correcting known problems.  Even bugs that can cause a
> crash seem to be viewed as acceptable if they happen rarely.

Huh? I tend to see exactly the opposite. Actual crash and "wrong
answer" bugs tend to get very prompt attention on all the open source
projects I know and use. What _does_ get delayed or even ignored are "bug
compability" problems, like this one. That is, software that relies on the
"affected rows" count is in fact broken, since it's making assumptions
about that number that were never promised in any standard or interface
docs.



> All kidding aside, I would like to see increased emphasis on stability
> and correctness.  But I will admit that it is a lot less fun than adding
> new features.

And this has got to be trolling: PostgreSQL is one of the _most_
stability and correctness focused software projects I've ever known. In
this particular case, the complaints about this issue where "Your bugfix
broke my tool! make it better!" The answer was "We can't just put it
back, that's an actual bug in there (rules firing in an unpredicatable
order). What's the _correct_ behavior?" The people with the complaints
then did not come up with a compelling, complete description of what
the correct behavior should be. There's always been vague parts to the
"desired behavior" like the phrase Tom pointed out: "in the context of
the view" which was clarified to mean "viewable by the view", which is
nearly impossible to code, if not an example of the halting problem.

PostgreSQL as a project errs on the side of not coding the quick fix,
in favor of waiting for the right answer. Sometimes too long, but this
case isn't one of those, IMHO.

Ross
-- 
Ross Reedstrom, Ph.D. [EMAIL PROTECTED]
Executive Director  phone: 713-348-6166
Gulf Coast Consortium for Bioinformatics  fax: 713-348-6182
Rice University MS-39
Houston, TX 77005

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

http://archives.postgresql.org

Re: [JDBC] [HACKERS] problem with new autocommit config parameter

[Barry Lind]

Daryl,

The problem is an incompatiblity between a new server autocommit feature 
and the existing jdbc autocommit feature.  The problem manifests itself 
when you turn autocommit off on the server (which is new functionality 
in 7.3).  If you leave autocommit turned on on the server (which is the 
way the server has always worked until 7.3) the jdbc driver correctly 
handles issuing the correct begin/commit/rollback commands to support 
autocommit functionality in the jdbc driver.

Autocommit will work with jdbc in 7.3 (and it does now as long as you 
leave autocommit set on in the postgresql.conf file).  We are just need 
to decide what to do in this one corner case.

thanks,
--Barry


Daryl Beattie wrote:
> Dear PostgreSQL people,
> 
>   Sorry for jumping into this conversation in the middle.
>   Autocommit is very important, as appservers may turn it on or off at
> will in order to support EJB transactions (being able to set them up, roll
> them back, commit them, etc. by using the JDBC API). If it is broken, then
> all EJB apps using PostgreSQL may be broken also. ...This frightens me a
> little. Could somebody please explain?
> 
> Sincerely,
> 
>   Daryl.
> 
> 
> 
>>-Original Message-
>>From: Tom Lane [mailto:[EMAIL PROTECTED]]
>>Sent: Monday, September 09, 2002 2:54 PM
>>To: Bruce Momjian
>>Cc: Barry Lind; [EMAIL PROTECTED]; 
>>[EMAIL PROTECTED]
>>Subject: Re: [JDBC] [HACKERS] problem with new autocommit config
>>parameter and jdbc 
>>
>>
>>Bruce Momjian <[EMAIL PROTECTED]> writes:
>>
>>>Barry Lind wrote:
>>>
How should client interfaces handle this new autocommit 
>>>
>>feature?  Is it
>>
best to just issue a set at the beginning of the 
>>>
>>connection to ensure
>>
that it is always on?
>>>
>>>Yes, I thought that was the best fix for apps that can't deal with
>>>autocommit being off.
>>
>>If autocommit=off really seriously breaks JDBC then I don't think a
>>simple SET command at the start of a session is going to do that much
>>to improve robustness.  What if the user issues another SET to turn it
>>on?
>>
>>I'd suggest just documenting that it is broken and you can't use it,
>>until such time as you can get it fixed.  Band-aids that only 
>>partially
>>cover the problem don't seem worth the effort to me.
>>
>>In general I think that autocommit=off is probably going to be very
>>poorly supported in the 7.3 release.  We can document it as being
>>"work in progress, use at your own risk".
>>
>>  regards, tom lane
>>
>>---(end of 
>>broadcast)---
>>TIP 3: if posting/reading through Usenet, please send an appropriate
>>subscribe-nomail command to [EMAIL PROTECTED] so that your
>>message can get through to the mailing list cleanly
>>
> 
> 


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

http://archives.postgresql.org

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Rod Taylor]

> > If we didn't do that, then Postgresql never would have been 
> > released in the first place, nor any date between then and now.

> I believe that the surprise is at the focus, when it comes to a release.
> With commercial products (anyway) if you have any sort of show-stopper
> bug (crashing, incorrect results, etc.) you do not release the tool

Most companies / groups (opensource or otherwise) will not hold back
many bugfixes and features for the sake of getting an additional out of
the way fix in as it tends to piss off the majority of the users.

I'm afraid right now I see this as a very minor item which is heavily
broken, meaning it's really really important to very few users.

Not having foreign keys break when renaming a column or table will
probably affect more people and is awaiting the next release.  Ditto for
security enhancements.  I see these as more important -- since they
affect me :)

If the changes are agreed upon and fixed, great.  It's a better product
because of it.  But forcing others to use an older version with
equivelently broken items because the next one doesn't do everything
perfectly doesn't make for progress.

However, rest assured, with anything if you push and put in the work
require it'll eventually go where you want it to.

-- 
  Rod Taylor


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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Dann Corbit]

> -Original Message-
> From: Ross J. Reedstrom [mailto:[EMAIL PROTECTED]] 
> Sent: Monday, September 09, 2002 12:26 PM
> To: Dann Corbit
> Cc: Rod Taylor; Steve Howe; PostgreSQL-development
> Subject: Re: [HACKERS] Rule updates and PQcmdstatus() issue
> 
> 
> On Mon, Sep 09, 2002 at 11:30:52AM -0700, Dann Corbit wrote:
> > > 
> > > I suspect it'll be several more major releases before we
> > > begin to consider it approaching completely functional.
> > 
> > I believe that the surprise is at the focus, when it comes to a 
> > release. With commercial products (anyway) if you have any sort of 
> > show-stopper bug (crashing, incorrect results, etc.) you do not 
> > release the tool until the bug, and all others like it, are fixed.  
> > Bugs that have to do with appearance or convenience can be 
> overlooked 
> > for a release as long as they are documented in the release notes.  
> > Now, it is not unlikely that there are unintentional 
> show-stopper bugs 
> > that get through Q/A. But intentionally passing them 
> through would be 
> > incompetent for a commercial enterprise.
> 
> Hmm, you don't have any drinking buddies who work QA, do you? 

I do have friends who work in Q/A.

> _Lots_ of known, "eat your harddrive" bugs get classified as 
> "to be fixed in future release" in commercial software, when 
> the release date pressure grows.

I have been programming since 1976 on literally many dozens of projects.
There is no project on which I have been a part where such a thing would
be allowed.  On the other hand, the projects I tend to work on are the
"these tools are used to run your business" MIS sorts of things.
Perhaps other areas of development are different.
 
> > With open source projects, the emphasis tends to be on 
> features, with 
> > far less regard for correcting known problems.  Even bugs that can 
> > cause a crash seem to be viewed as acceptable if they happen rarely.
> 
> Huh? I tend to see exactly the opposite. Actual crash and 
> "wrong answer" bugs tend to get very prompt attention on all 
> the open source projects I know and use. What _does_ get 
> delayed or even ignored are "bug compability" problems, like 
> this one. That is, software that relies on the "affected 
> rows" count is in fact broken, since it's making assumptions 
> about that number that were never promised in any standard or 
> interface docs.

If this particular case is a case of someone relying on undocumented
behavior, then there is no bug.  If this is a case of relying upon
documented behavior and the behavior changes, then there is a bug.
 
> 
> 
> > All kidding aside, I would like to see increased emphasis 
> on stability 
> > and correctness.  But I will admit that it is a lot less fun than 
> > adding new features.
> 
> And this has got to be trolling: PostgreSQL is one of the 
> _most_ stability and correctness focused software projects 
> I've ever known.

There are very serious problems that have been in the release notes for
a very long time and yet have never been addressed.  Most of them are
rather esoteric and won't affect most users.  I have been on many
projects that were far more concerned with correctness.  As I have said,
"no serious bugs are allowed in a release" is not uncommon on the
commercial projects where I have experience.  That includes 9 years as a
subcontractor at Microsoft.  If they have a serious bug that cannot be
fixed, they will simply cut scope.  But my experience was on MS (ITG)
projects.  Perhaps other branches of MS did not require the same rigor.
On the other hand, PostgreSQL is more responsive in this area than any
other open source project that I know of.

> In this particular case, the complaints 
> about this issue where "Your bugfix broke my tool! make it 
> better!" The answer was "We can't just put it back, that's an 
> actual bug in there (rules firing in an unpredicatable 
> order). What's the _correct_ behavior?" The people with the 
> complaints then did not come up with a compelling, complete 
> description of what the correct behavior should be. There's 
> always been vague parts to the "desired behavior" like the 
> phrase Tom pointed out: "in the context of the view" which 
> was clarified to mean "viewable by the view", which is nearly 
> impossible to code, if not an example of the halting problem.

This may be an example where the original poster is asking for something
they should not be asking for.  If the original poster was relying upon
undocumented behavior, then there is nothing that needs to be done, and
the resulting problem is the original poster's fault.
 
> PostgreSQL as a project errs on the side of not coding the 
> quick fix, in favor of waiting for the right answer. 
> Sometimes too long, but this case isn't one of those, IMHO.

You are probably right about this case.  In fact, I am not defending the
original poster's demand.  I have no idea if their request has merit or
not.  I was merely expressing an opinion that a good standard

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Jan Wieck]

Steve Howe wrote:
> 
> Hello Jan,
> 
> Monday, September 9, 2002, 11:15:47 AM, you wrote:
> 
> JW> So please, "proper behavior" is not allways what your favorite tool
> JW> expects. And just because you cannot "fix" your tool doesn't make that
> JW> behavior any more "proper".
> Do you have any word more appropriate ?
> [...]
> And it looks like *you* overhauled the query rewrite rule system, so
> what we are talking is something that must have passed through you. So
> instead of offending me, your "proper" behavior would be try to help
> and suggest a solution for the problem, as other developers are doing.

See, and exactly here lies the problem. Indeed, I spent about 3 months
of my spare time back in 95 or so to fix it, after I spent many more
months over years to get familiar with the internals.

Now, instead of even trying to spend some serious amount of time
yourself, you give some vague hints about the functionality that might
make your problems disappear, name that a proposal and expect someone
else to do what you need for free. This is not exactly how open source
works.

We should surely keep this on a much more technical level and avoid any
personal offendings. To do so, please explain to me why you think that
triggers and constraints are out of focus here? What is the difference
between a trigger, a rule and an instead rule from a business process
oriented point of view? I think there is none at all. They are just
different techniques to do one and the same, implement business logic in
the database system.


Jan

-- 

#==#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.  #
#== [EMAIL PROTECTED] #

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

[HACKERS]

[Laurette Cisneros]

I am trying move my development database to 7.3b1.

However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
the following error:

pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp

pg_restore: [archiver (db)] could not execute query: ERROR:  function
plpgsql_call_handler() does not return type language_handler

Any ideas?

Thanks,

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
--
A wiki we will go...


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[Rod Taylor]

On Mon, 2002-09-09 at 17:04, snpe wrote:

> I'm use 'autocommit=false' and have problem with psql
> When any commnad is lost, then next commnad get error for transactions
> (simple select command).BTW
> 
> snpe> select * from org_ba;
> ERROR: relation org_ba does not exists
> snpe> select * from org_ban;
> ERROR: current transactions is aborted, queries ignored until end of 
> transaction block
> snpe> rollback;
> ROLLBACK
> snpe> select * from org_ban;

Maybe I'm missing something, but isn't that the expected behaviour when
autocommit is turned off?
 
-- 
  Rod Taylor


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

Re: [HACKERS] Proposal: Solving the "Return proper effected tuple count from complex commands [return]" issue

[Steve Howe]

Hello Peter,

Monday, September 9, 2002, 3:41:41 PM, you wrote:

PE> Steve Howe writes:

>> Here are the proposals for solutioning the "Return proper effected
>> tuple count from complex commands [return]" issue as seen on TODO.
>>
>> Any comments ?... This is obviously open to voting and discussion.

PE> We don't have a whole lot of freedom in this; this area is covered by the
PE> SQL standard.  The major premise in the standard's point of view is that
PE> views are supposed to be transparent.  That is, if

PE> SELECT * FROM my_view WHERE condition;

PE> return N rows, then a subsequently executed

PE> UPDATE my_view SET ... WHERE condition;

PE> returns an update count of N, no matter what happens behind the scenes.  I
PE> don't think this matches Tom Lane's view exactly, but it's a lot closer
PE> than your proposal.
If there was a single statement per rules executed, this would be end
of discussion... but as you know there can be possible multiple
statements per rules, and the difficulty is what do to in those
cases.

As far as of now, Tom Lane's proposal seems to be the most accepted,
without using a new API.

- 
Best regards,
 Steve Howe   mailto:[EMAIL PROTECTED]


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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[snpe]

On Monday 09 September 2002 08:53 pm, Tom Lane wrote:
> Bruce Momjian <[EMAIL PROTECTED]> writes:
> > Barry Lind wrote:
> >> How should client interfaces handle this new autocommit feature?  Is it
> >> best to just issue a set at the beginning of the connection to ensure
> >> that it is always on?
> >
> > Yes, I thought that was the best fix for apps that can't deal with
> > autocommit being off.
>
> If autocommit=off really seriously breaks JDBC then I don't think a
> simple SET command at the start of a session is going to do that much
> to improve robustness.  What if the user issues another SET to turn it
> on?
>
> I'd suggest just documenting that it is broken and you can't use it,
> until such time as you can get it fixed.  Band-aids that only partially
> cover the problem don't seem worth the effort to me.
>
> In general I think that autocommit=off is probably going to be very
> poorly supported in the 7.3 release.  We can document it as being
> "work in progress, use at your own risk".
>

I'm use 'autocommit=false' and have problem with psql
When any commnad is lost, then next commnad get error for transactions
(simple select command).BTW

snpe> select * from org_ba;
ERROR: relation org_ba does not exists
snpe> select * from org_ban;
ERROR: current transactions is aborted, queries ignored until end of 
transaction block
snpe> rollback;
ROLLBACK
snpe> select * from org_ban;

this command is ok.
regards
Haris Peco

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

http://archives.postgresql.org

Re: [HACKERS]

[scott.marlowe]

On Mon, 9 Sep 2002, Laurette Cisneros wrote:

> 
> I am trying move my development database to 7.3b1.
> 
> However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> the following error:
> 
> pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  function
> plpgsql_call_handler() does not return type language_handler

I sounds like there's a language installed on your 7.2.2 server that your 
7.3 server doesn't have installed.


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

http://archives.postgresql.org

Re: [HACKERS]

[Oliver Elphick]

On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> 
> I am trying move my development database to 7.3b1.
> 
> However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> the following error:
> 
> pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> 
> pg_restore: [archiver (db)] could not execute query: ERROR:  function
> plpgsql_call_handler() does not return type language_handler
> 
> Any ideas?

At the moment, you have to edit the dump.  Where the language handler
function is declared, change "RETURNS opaque" to "RETURNS
language_handler".


-- 
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK
http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
 
 "Submit yourselves therefore to God. Resist the devil, 
  and he will flee from you."James 4:7 


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

Re: [HACKERS]

[Laurette Cisneros]

Thanks!

On 9 Sep 2002, Oliver Elphick wrote:

> On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> > 
> > I am trying move my development database to 7.3b1.
> > 
> > However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> > the following error:
> > 
> > pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> > 
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler() does not return type language_handler
> > 
> > Any ideas?
> 
> At the moment, you have to edit the dump.  Where the language handler
> function is declared, change "RETURNS opaque" to "RETURNS
> language_handler".
> 
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
--
A wiki we will go...


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

Re: [HACKERS] Script to compute random page cost

[Tom Lane]

"Nick Fankhauser" <[EMAIL PROTECTED]> writes:
> I bounced these numbers off of Ray Ontko here at our shop, and he pointed
> out that random page cost is measured in multiples of a sequential page
> fetch. It seems almost impossible that a random fetch would be less
> expensive than a sequential fetch, yet we all seem to be getting results <
> 1. I can't see anything obviously wrong with the script, but something very
> odd is going.

The big problem with the script is that it involves an invocation of
"dd" - hence, at least one process fork --- for every page read
operation.  The seqscan part of the test is even worse, as it adds a
test(1) call and a shell if/then/else to the overhead.  My guess is that
we are measuring script overhead here, and not the desired I/O quantities
at all --- the script overhead is completely swamping the latter.  The
apparent stability of the results across a number of different platforms
bolsters that thought.

Someone else opined that the script was also not comparing equal
numbers of pages read for the random and sequential cases.  I haven't
tried to decipher the logic enough to see if that allegation is true,
but it's not obviously false.

Finally, I wouldn't believe the results for a moment if they were taken
against databases that are not several times the size of physical RAM
on the test machine, with a total I/O volume also much more than
physical RAM.  We are trying to measure the behavior when kernel
caching is not helpful; if the database fits in RAM then you are just
naturally going to get random_page_cost close to 1, because the kernel
will avoid doing any I/O at all.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

[HACKERS] FreeBSD Packages/Port for 7.3beta1...

[Sean Chittenden]

I've put together some packages for the 7.3beta1 release.  The can be
found here along with a tenative FreeBSD port:

   http://66.250.180.19/postgresql-7.3beta1/

The differences in the files are that the postgresql-7.3b1-O3.tbz has
been compiled with -O3 where as the postgresql-7.3b1.tbz hasn't.  See
my next message for details.

-sc

-- 
Sean Chittenden



msg21991/pgp0.pgp
Description: PGP signature

[HACKERS] Optimization levels when compiling PostgreSQL...

[Sean Chittenden]

In an attempt to beef up the PostgreSQL port for FreeBSD, I've added
an option for adding additional optimization, similar to what MySQL
does by compiling the server with -O6.  I'm only compiling at -O3 with
the flag at the moment, however I wanted to ping the idea around to
make sure this isn't some land-mine that doesn't show up in the
regression tests.  My database hardware is in transition to a new
data center so I can't test this on my own at the moment.  :-/

The size difference between -O and -O3 is only 200K or so... does
anyone think that it'd be safe to head to -O6 on a wide scale?  I
don't want to cream the FreeBSD user base with a bogus recommendation.

I figure this is a database and 200KB doesn't amount to bo-diddly
compared to my data sizes so this seems acceptable in that dept.  I'm
even thinking about going so far as to have flex required for the
build dependencies and setting -Cf or -CF for building the scanner
(need to check the archives for which turned out to be faster).

I'm also tinkering with the idea of automatically turn off fsync if
optimize is set.  Objections?  -sc

-- 
Sean Chittenden



msg21992/pgp0.pgp
Description: PGP signature

Re: [HACKERS]

[Laurette Cisneros]

Ok, am I missing somethig here?

In 7.3, the -Fp option has been removed which leaves the -Fc (which we use
in our 7.2 dumps) or -Ft. 

How does one edit a compressed or tar file?

Also, is this problem going to be fixed in a later beta or regular release
of 7.3?  This could pose a problem to restore full database dumps.

Thanks,

L.
On 9 Sep 2002, Oliver Elphick wrote:

> On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> > 
> > I am trying move my development database to 7.3b1.
> > 
> > However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> > the following error:
> > 
> > pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> > 
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler() does not return type language_handler
> > 
> > Any ideas?
> 
> At the moment, you have to edit the dump.  Where the language handler
> function is declared, change "RETURNS opaque" to "RETURNS
> language_handler".
> 
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
--
A wiki we will go...


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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Steve Howe]

Hello Jan,

Monday, September 9, 2002, 4:56:04 PM, you wrote:

JW> Steve Howe wrote:
>> 
>> Hello Jan,
>> 
>> Monday, September 9, 2002, 11:15:47 AM, you wrote:
>> 
>> JW> So please, "proper behavior" is not allways what your favorite tool
>> JW> expects. And just because you cannot "fix" your tool doesn't make that
>> JW> behavior any more "proper".
>> Do you have any word more appropriate ?
>> [...]
>> And it looks like *you* overhauled the query rewrite rule system, so
>> what we are talking is something that must have passed through you. So
>> instead of offending me, your "proper" behavior would be try to help
>> and suggest a solution for the problem, as other developers are doing.

JW> See, and exactly here lies the problem. Indeed, I spent about 3 months
JW> of my spare time back in 95 or so to fix it, after I spent many more
JW> months over years to get familiar with the internals.

JW> Now, instead of even trying to spend some serious amount of time
JW> yourself, you give some vague hints about the functionality that might
JW> make your problems disappear, name that a proposal and expect someone
JW> else to do what you need for free. This is not exactly how open source
JW> works.
As I told you, this would demand weeks and I just don't have time to
do it. Other developers offered to make a fix and asked me to do that
proposal. And so I did.
It's sad that just you don't seem to be trying to help in
any way. Other developers had considered the proposal and are actually
voting and giving constructive ideas on the subject.

JW> We should surely keep this on a much more technical level and avoid any
JW> personal offendings. To do so, please explain to me why you think that
JW> triggers and constraints are out of focus here? What is the difference
JW> between a trigger, a rule and an instead rule from a business process
JW> oriented point of view? I think there is none at all. They are just
JW> different techniques to do one and the same, implement business logic in
JW> the database system.
Because the affected commands are supposed to give you back
information on what your INSERT/UPDATE/DELETE commands, not what is
making behind the scenes.

And it seems that other people in the thread agree with me, please
read thread.

Since you are probably very familiar with the rules system, why don't
you vote on a proposal too, or just suggest yours. Your opinion is
very important. I'm not saying I'm the truth owner; I'm just another
developer who needs a feature working again.

Thank you.

- 
Best regards,
 Steve Howe   mailto:[EMAIL PROTECTED]


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

Re: [HACKERS]

[Laurette Cisneros]

Ok, I made the changes in the compressed pg_dump file.  Now pg_restore crashes:

pg_restore: [archiver] out of memory

*sigh*

L.
On 9 Sep 2002, Oliver Elphick wrote:

> On Mon, 2002-09-09 at 21:34, Laurette Cisneros wrote:
> > 
> > I am trying move my development database to 7.3b1.
> > 
> > However, when I try to restore from a 7.2.2 dump to the 7.3.b1 server I get
> > the following error:
> > 
> > pg_restore -U nbadmin -h lnc -p 5432 -d stats -Fc /tmp/stats.pgdmp
> > 
> > pg_restore: [archiver (db)] could not execute query: ERROR:  function
> > plpgsql_call_handler() does not return type language_handler
> > 
> > Any ideas?
> 
> At the moment, you have to edit the dump.  Where the language handler
> function is declared, change "RETURNS opaque" to "RETURNS
> language_handler".
> 
> 
> 

-- 
Laurette Cisneros
The Database Group
(510) 420-3137
NextBus Information Systems, Inc.
www.nextbus.com
--
A wiki we will go...


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

http://archives.postgresql.org

Re: [HACKERS]

[Philip Warner]

At 03:54 PM 9/09/2002 -0700, Laurette Cisneros wrote:
>Ok, I made the changes in the compressed pg_dump file.

That's probably a very bad idea.

It's a little more long-winded, but try:

pg_restore -l dumpfile > dump1.lis

copy dump1.lis to dump2.lis

delete everything from dump1.lis at and after the definition that causes 
the problem.

delete everything from dump2.lis at and before the definition that causes 
the problem.

pg_restore -L dump1.lis

manually define the language

pg_restore -L dump2.lis


ALTERNATIVELY, define the language in template1, then just edit dump1.lis 
to remove the line for the language definition, and run pg_restore -L 
dump1.lis.




Philip Warner| __---_
Albatross Consulting Pty. Ltd.   |/   -  \
(A.B.N. 75 008 659 498)  |  /(@)   __---_
Tel: (+61) 0500 83 82 81 | _  \
Fax: (+61) 0500 83 82 82 | ___ |
Http://www.rhyme.com.au  |/   \|
  |----
PGP key available upon request,  |  /
and from pgp5.ai.mit.edu:11371   |/


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

http://archives.postgresql.org

Re: [HACKERS] Impossible to import pg_dumpall from 7.2.2 to 7.3b1

[Hervé Piedvache]

Dear Tom,

>> <[EMAIL PROTECTED]> writes:
>> But when I try to import it inside 7.3b1 I get this :
>> (seems that the copy command is not fully compatible with the 7.2.2
>> pg_dumpall ?)
>>
>> Many thinks like this : (I have only copied some parts ...)
>> Size of the dump about 1.5 Gb ...
>>
>> Query buffer reset (cleared).
>> psql:/tmp/dump_mybase.txt:1015274: invalid command \nPour
>> Query buffer reset (cleared).
>
>It seems pretty clear that the COPY command itself failed, leaving psql
>trying to interpret the following data as SQL commands.  But you have
>not shown us either the COPY command or the error message it generated,
>so there's not a lot we can say about it...
>
>regards, tom lane

OK I have (hope) find the trouble ... may be a mistake from my part but 
which was running with v7.2.2 ... (I think I have to alter my table with 
default current_date ...)

I have this error message :
psql:dump.7.2.2.txt:304: ERROR:  Column "datecrea" is of type date but 
default expression is of type timestamp with time zone
You will need to rewrite or cast the expression

for the field :
"datecrea" date DEFAULT now(),

So after, the importation of the data are making errors messages because the 
previus table has not been created ... I'm right ?

I have also a strange error :
psql:dump.7.2.2.txt:1087: ERROR:  function plpgsql_call_handler() does not 
return type language_handler
psql:dump.7.2.2.txt:1126: ERROR:  language "plpgsql" does not exist

for those lines :
--
-- TOC Entry ID 292 (OID 2083293)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: postgres
--

CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS 
'/usr/local/pgsql/lib/plpgsql.so', 'plpgsql_call_handler' LANGUAGE 'C';

--
-- TOC Entry ID 293 (OID 2083294)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
--

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" 
LANCOMPILER 'PL/pgSQL';

Hope this help ...

Regards,
-- 
Hervé



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

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[snpe]

On Monday 09 September 2002 11:03 pm, Rod Taylor wrote:
> On Mon, 2002-09-09 at 17:04, snpe wrote:
> > I'm use 'autocommit=false' and have problem with psql
> > When any commnad is lost, then next commnad get error for transactions
> > (simple select command).BTW
> >
> > snpe> select * from org_ba;
> > ERROR: relation org_ba does not exists
> > snpe> select * from org_ban;
> > ERROR: current transactions is aborted, queries ignored until end of
> > transaction block
> > snpe> rollback;
> > ROLLBACK
> > snpe> select * from org_ban;
>
> Maybe I'm missing something, but isn't that the expected behaviour when
> autocommit is turned off?
I get this every time.When exists command with error next command don't work 
without explicit rollback and commit (this is not for psql, this error get in 
with JDeveloper - JDBC driver).When autocommit=ture all is fine

haris peco


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[Stephan Szabo]

On Tue, 10 Sep 2002, snpe wrote:

> On Monday 09 September 2002 11:03 pm, Rod Taylor wrote:
> > On Mon, 2002-09-09 at 17:04, snpe wrote:
> > > I'm use 'autocommit=false' and have problem with psql
> > > When any commnad is lost, then next commnad get error for transactions
> > > (simple select command).BTW
> > >
> > > snpe> select * from org_ba;
> > > ERROR: relation org_ba does not exists
> > > snpe> select * from org_ban;
> > > ERROR: current transactions is aborted, queries ignored until end of
> > > transaction block
> > > snpe> rollback;
> > > ROLLBACK
> > > snpe> select * from org_ban;
> >
> > Maybe I'm missing something, but isn't that the expected behaviour when
> > autocommit is turned off?
> I get this every time.When exists command with error next command don't work
> without explicit rollback and commit (this is not for psql, this error get in
> with JDeveloper - JDBC driver).When autocommit=ture all is fine

It starts a transaction, failes the first command and goes into the
error has occurred in this transaction state.  Seems like reasonable
behavior.



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

Re: [HACKERS] Script to compute random page cost

[Bruce Momjian]

Nick Fankhauser wrote:
> Hi again-
> 
> I bounced these numbers off of Ray Ontko here at our shop, and he pointed
> out that random page cost is measured in multiples of a sequential page
> fetch. It seems almost impossible that a random fetch would be less
> expensive than a sequential fetch, yet we all seem to be getting results <
> 1. I can't see anything obviously wrong with the script, but something very
> odd is going.

OK, new version at:

ftp://candle.pha.pa.us/pub/postgresql/randcost

What I have done is to take all of the computation stuff out of the
timed loop so only the 'dd' is done in the loop.

I am getting a 1.0 for random pages cost with this new code, but I don't
have much data in the database so it is very possible I have it all
cached.  Would others please test it?

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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[snpe]

On Tuesday 10 September 2002 03:05 am, Stephan Szabo wrote:
> On Tue, 10 Sep 2002, snpe wrote:
> > On Monday 09 September 2002 11:03 pm, Rod Taylor wrote:
> > > On Mon, 2002-09-09 at 17:04, snpe wrote:
> > > > I'm use 'autocommit=false' and have problem with psql
> > > > When any commnad is lost, then next commnad get error for
> > > > transactions (simple select command).BTW
> > > >
> > > > snpe> select * from org_ba;
> > > > ERROR: relation org_ba does not exists
> > > > snpe> select * from org_ban;
> > > > ERROR: current transactions is aborted, queries ignored until end of
> > > > transaction block
> > > > snpe> rollback;
> > > > ROLLBACK
> > > > snpe> select * from org_ban;
> > >
> > > Maybe I'm missing something, but isn't that the expected behaviour when
> > > autocommit is turned off?
> >
> > I get this every time.When exists command with error next command don't
> > work without explicit rollback and commit (this is not for psql, this
> > error get in with JDeveloper - JDBC driver).When autocommit=ture all is
> > fine
>
> It starts a transaction, failes the first command and goes into the
> error has occurred in this transaction state.  Seems like reasonable
> behavior.
Select command don't start transaction - it is not good
Error command don't start transaction - nothing hapen, only typing error

regards
haris peco


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Bruce Momjian]

Actually, this problem is part of a whole scope of problems that were in
the Berkeley code, because rules, and inheritance, just have a certain
contorting effect on SQL queries where it is difficult to get them
working properly.

If these features didn't come from Berkeley, I doubt we would have ever
implemented them, so in some case there are inherited bugs from features
that weren't 100% thought out when they were added many years ago.

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

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

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Bruce Momjian]

Jan Wieck wrote:
> We should surely keep this on a much more technical level and avoid any
> personal offendings. To do so, please explain to me why you think that
> triggers and constraints are out of focus here? What is the difference
> between a trigger, a rule and an instead rule from a business process
> oriented point of view? I think there is none at all. They are just
> different techniques to do one and the same, implement business logic in
> the database system.

All the problems here are coming from INSTEAD rules.  We don't have
INSTEAD triggers or contraints.

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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Bruce Momjian]

Steve Howe wrote:
> Because the affected commands are supposed to give you back
> information on what your INSERT/UPDATE/DELETE commands, not what is
> making behind the scenes.
> 
> And it seems that other people in the thread agree with me, please
> read thread.
> 
> Since you are probably very familiar with the rules system, why don't
> you vote on a proposal too, or just suggest yours. Your opinion is
> very important. I'm not saying I'm the truth owner; I'm just another
> developer who needs a feature working again.

Jan actually did vote in the first round which appears in TODO.detail. 
He voted that if the INSTEAD rule had only _one_ statement, return that,
if not, return nothing.

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

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[Stephan Szabo]

On Tue, 10 Sep 2002, snpe wrote:

> On Tuesday 10 September 2002 03:05 am, Stephan Szabo wrote:
> > On Tue, 10 Sep 2002, snpe wrote:
> > > On Monday 09 September 2002 11:03 pm, Rod Taylor wrote:
> > > > On Mon, 2002-09-09 at 17:04, snpe wrote:
> > > > > I'm use 'autocommit=false' and have problem with psql
> > > > > When any commnad is lost, then next commnad get error for
> > > > > transactions (simple select command).BTW
> > > > >
> > > > > snpe> select * from org_ba;
> > > > > ERROR: relation org_ba does not exists
> > > > > snpe> select * from org_ban;
> > > > > ERROR: current transactions is aborted, queries ignored until end of
> > > > > transaction block
> > > > > snpe> rollback;
> > > > > ROLLBACK
> > > > > snpe> select * from org_ban;
> > > >
> > > > Maybe I'm missing something, but isn't that the expected behaviour when
> > > > autocommit is turned off?
> > >
> > > I get this every time.When exists command with error next command don't
> > > work without explicit rollback and commit (this is not for psql, this
> > > error get in with JDeveloper - JDBC driver).When autocommit=ture all is
> > > fine
> >
> > It starts a transaction, failes the first command and goes into the
> > error has occurred in this transaction state.  Seems like reasonable
> > behavior.

> Select command don't start transaction - it is not good

I think you need more justification than "it is not good."  If I do a
sequence of select statements in autocommit=false, I'd expect the same
consistancy as if I'd done
begin;
select ...;
select ...;

> Error command don't start transaction - nothing hapen, only typing error

If you do an insert that violates a constraint, does that start an
transaction or not?  I think we have to choose before we start doing the
statement not after.


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

Re: [HACKERS] 7.3beta and ecpg

[Bruce Momjian]

Tom Lane wrote:
> Michael Meskes <[EMAIL PROTECTED]> writes:
> > I didn't download the beta but compared the CVS checkouts and it appears
> > the ecpg directory is still the one from 7.2 not the one tagged
> > big_bison. Will this one be moved into the mainstream source?
> 
> Well, I think we can't do that until postgresql.org has a version of
> bison installed that will compile it.  And I'm really hesitant to see us
> depending on a beta version of bison.  Any word on a new bison official
> release?
> 
> We still have a few weeks until the situation gets critical, but maybe
> it is time to start thinking about a fallback plan...

IMHO, our fallback is to ship based on the bison beta.

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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Stephan Szabo]

On Mon, 9 Sep 2002, Bruce Momjian wrote:

> Jan Wieck wrote:
> > We should surely keep this on a much more technical level and avoid any
> > personal offendings. To do so, please explain to me why you think that
> > triggers and constraints are out of focus here? What is the difference
> > between a trigger, a rule and an instead rule from a business process
> > oriented point of view? I think there is none at all. They are just
> > different techniques to do one and the same, implement business logic in
> > the database system.
>
> All the problems here are coming from INSTEAD rules.  We don't have
> INSTEAD triggers or contraints.

Sure we do, well sort of. :)
Make a before trigger that does a different statement and returns NULL
to abort the original action on that row.



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

http://archives.postgresql.org

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Rod Taylor]

On Mon, 2002-09-09 at 22:11, Bruce Momjian wrote:
> Jan Wieck wrote:
> > We should surely keep this on a much more technical level and avoid any
> > personal offendings. To do so, please explain to me why you think that
> > triggers and constraints are out of focus here? What is the difference
> > between a trigger, a rule and an instead rule from a business process
> > oriented point of view? I think there is none at all. They are just
> > different techniques to do one and the same, implement business logic in
> > the database system.
> 
> All the problems here are coming from INSTEAD rules.  We don't have
> INSTEAD triggers or contraints.

Well.. Triggers could be exclusively INSTEAD.  A trigger could easily
write a few things to a number of other tables, and return NULL in a
BEFORE trigger which would prevent execution of the requested command.



-- 
  Rod Taylor


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

Re: [HACKERS] Proposal: Solving the "Return proper effected tuple

[Bruce Momjian]

Peter Eisentraut wrote:
> Steve Howe writes:
> 
> > Here are the proposals for solutioning the "Return proper effected
> > tuple count from complex commands [return]" issue as seen on TODO.
> >
> > Any comments ?... This is obviously open to voting and discussion.
> 
> We don't have a whole lot of freedom in this; this area is covered by the
> SQL standard.  The major premise in the standard's point of view is that
> views are supposed to be transparent.  That is, if
> 
> SELECT * FROM my_view WHERE condition;
> 
> return N rows, then a subsequently executed
> 
> UPDATE my_view SET ... WHERE condition;
> 
> returns an update count of N, no matter what happens behind the scenes.  I
> don't think this matches Tom Lane's view exactly, but it's a lot closer
> than your proposal.

Oh, this is bad news.  The problem we have is that rules don't
distinguish the UPDATE on the underlying tables of the rule from other
updates that may appear in the query.

If we go with Tom's idea and total just UPDATE's, we will get the right
answer when there is only one UPDATE in the ruleset.

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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[Tom Lane]

snpe <[EMAIL PROTECTED]> writes:
> I'm use 'autocommit=false' and have problem with psql
> When any commnad is lost, then next commnad get error for transactions
> (simple select command).BTW

> snpe> select * from org_ba;
> ERROR: relation org_ba does not exists
> snpe> select * from org_ban;
> ERROR: current transactions is aborted, queries ignored until end of 
> transaction block

Um, what's wrong with that?

It seems to me that an application that is using autocommit=off will
expect the first SELECT to start a transaction block.  If the first
SELECT fails, then subsequent commands *should* fail until you commit
or rollback.  Certainly if you did an explicit BEGIN before the first
SELECT, the above is what you'd get --- why should implicit BEGIN
work differently?

regards, tom lane

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

http://archives.postgresql.org

Re: [HACKERS] Proposal: Solving the "Return proper affected tuple

[Christopher Kings-Lynne]

Sorry guys - it's killing me!  It's 'affected' in the subject line - not
'effected'!!! Sigh :)

Chris

> -Original Message-
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Bruce Momjian
> Sent: Tuesday, 10 September 2002 10:24 AM
> To: Peter Eisentraut
> Cc: Steve Howe; [EMAIL PROTECTED]
> Subject: Re: [HACKERS] Proposal: Solving the "Return proper effected
> tuple
>
>
> Peter Eisentraut wrote:
> > Steve Howe writes:
> >
> > > Here are the proposals for solutioning the "Return proper effected
> > > tuple count from complex commands [return]" issue as seen on TODO.
> > >
> > > Any comments ?... This is obviously open to voting and discussion.
> >
> > We don't have a whole lot of freedom in this; this area is
> covered by the
> > SQL standard.  The major premise in the standard's point of view is that
> > views are supposed to be transparent.  That is, if
> >
> > SELECT * FROM my_view WHERE condition;
> >
> > return N rows, then a subsequently executed
> >
> > UPDATE my_view SET ... WHERE condition;
> >
> > returns an update count of N, no matter what happens behind the
> scenes.  I
> > don't think this matches Tom Lane's view exactly, but it's a lot closer
> > than your proposal.
>
> Oh, this is bad news.  The problem we have is that rules don't
> distinguish the UPDATE on the underlying tables of the rule from other
> updates that may appear in the query.
>
> If we go with Tom's idea and total just UPDATE's, we will get the right
> answer when there is only one UPDATE in the ruleset.
>
> --
>   Bruce Momjian|  http://candle.pha.pa.us
>   [EMAIL PROTECTED]   |  (610) 359-1001
>   +  If your life is a hard drive, |  13 Roberts Road
>   +  Christ can be your backup.|  Newtown Square,
> Pennsylvania 19073
>
> ---(end of broadcast)---
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to [EMAIL PROTECTED] so that your
> message can get through to the mailing list cleanly
>


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

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Tom Lane]

Sean Chittenden <[EMAIL PROTECTED]> writes:
> The size difference between -O and -O3 is only 200K or so... does
> anyone think that it'd be safe to head to -O6 on a wide scale?

Dunno.  I'm not aware of any bits of the code that are unportable enough
to break with max optimization of any correct compiler.  But you might
find such a bug.  Or a bug in your compiler.  Are you feeling lucky
today?

My feeling is that gcc -O2 is quite well tested with the PG code.
I don't have any equivalent confidence in -O6.  Give it a shot for
beta-testing, for sure, but I'm iffy about calling that a
production-grade database release...

> I'm even thinking about going so far as to have flex required for the
> build dependencies and setting -Cf or -CF for building the scanner
> (need to check the archives for which turned out to be faster).

Um, didn't we do that stuff already in the standard build?  AFAIK
you cannot build PG with any lexer except flex, and Peter already
hacked the flags.

> I'm also tinkering with the idea of automatically turn off fsync if
> optimize is set.

No-bloody-way.  Trusting your compiler is an entirely separate issue
from whether you trust your disk hardware, power source, etc.  Puh-leez
do not muddy the waters by introducing a port-specific variation in
choices that only the DBA of a particular installation should make.

regards, tom lane

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] problem with new autocommit config parameter and jdbc

[Curt Sampson]

On Mon, 9 Sep 2002, Tom Lane wrote:

> snpe <[EMAIL PROTECTED]> writes:
>
> > snpe> select * from org_ba;
> > ERROR: relation org_ba does not exists
> > snpe> select * from org_ban;
> > ERROR: current transactions is aborted, queries ignored until end of
> > transaction block
>
> Um, what's wrong with that?
>
> It seems to me that an application that is using autocommit=off will
> expect the first SELECT to start a transaction block.

Yup. In fact, the standard (at least, insofar as I have information
relating to it), specifies that the first SELECT statement above
*must* start a transaction.

>From Date's _A Guide to the SQL Standard_ (Fourth Edition):

An SQL-transaction is initiated when the relevant SQL-agent executes
a "transaction-initiating" SQL Statement (see below) and the
SQL-agent does not already have an SQL-transaction in progress.
...
The following SQL statements are _not_ transaction-initiating:

CONNECT
SET CONNECTION
DISCONNECT
SET SESSION AUTHORIZATION
SET CATALOG
SET SCHEMA
SET NAMES
SET TIME ZONE
SET TRANSACTION
SET CONSTRAINTS
COMMIT
ROLLBACK
GET DIAGNOSTICS

Nor, of course, are the nonexecutable statements DECLARE CURSOR,
DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE
SECTIONS, and WHENEVER.

So SELECT ought always to initiate a transaction, if one is not already
in progress. If auto-commit is enabled, of course, that statement may
be committed immediately after execution, if it doesn't fail.

As far as the JDBC driver goes, I'm not too sure of the issues here, but
it should certainly be ensuring that autocommit is enabled, as per the
JDBC specification, when a new connection is created. I see no reason
this couldn't be done with a "SET AUTOCOMMIT TO OFF" or whatever, if
that's necessary to override a possible configuration file setting.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC


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

Re: [HACKERS] Script to compute random page cost

[Curt Sampson]

On Mon, 9 Sep 2002, Tom Lane wrote:

> Finally, I wouldn't believe the results for a moment if they were taken
> against databases that are not several times the size of physical RAM
> on the test machine, with a total I/O volume also much more than
> physical RAM.  We are trying to measure the behavior when kernel
> caching is not helpful; if the database fits in RAM then you are just
> naturally going to get random_page_cost close to 1, because the kernel
> will avoid doing any I/O at all.

Um...yeah; another reason to use randread against a raw disk device.
(A little hard to use on linux systems, I bet, but works fine on
BSD systems.)

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC


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

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Sean Chittenden]

> > The size difference between -O and -O3 is only 200K or so... does
> > anyone think that it'd be safe to head to -O6 on a wide scale?
> 
> Dunno.  I'm not aware of any bits of the code that are unportable
> enough to break with max optimization of any correct compiler.  But
> you might find such a bug.  Or a bug in your compiler.  Are you
> feeling lucky today?
> 
> My feeling is that gcc -O2 is quite well tested with the PG code.  I
> don't have any equivalent confidence in -O6.  Give it a shot for
> beta-testing, for sure, but I'm iffy about calling that a
> production-grade database release...

I'm thinking about changing this from a beta port to a -devel port
that I'll periodically update with snapshots.  I'll turn on -O6 for
the -devel port and -O2 for production for now.  If I don't hear of
any random bogons in the code I'll see if I can't increase it further
to -O3 and beyond at a slow/incremental rate.

Has there been any talk of doing incremental -snapshots of the code
base?  I've really fallen inlove with the concept for development.
Having incremental changes is much easier to cope with than massive
steps forward.

> > I'm even thinking about going so far as to have flex required for the
> > build dependencies and setting -Cf or -CF for building the scanner
> > (need to check the archives for which turned out to be faster).
> 
> Um, didn't we do that stuff already in the standard build?  AFAIK
> you cannot build PG with any lexer except flex, and Peter already
> hacked the flags.

Hrm, I should go check the archives, but I thought what was used was
one step below -C[fF] and was used because of size concerns for
embedded databases.  My memory for what happens on mailing lists seems
to be fading though so I'll look it up.

> > I'm also tinkering with the idea of automatically turn off fsync if
> > optimize is set.
> 
> No-bloody-way.  Trusting your compiler is an entirely separate issue
> from whether you trust your disk hardware, power source, etc.
> Puh-leez do not muddy the waters by introducing a port-specific
> variation in choices that only the DBA of a particular installation
> should make.

Whoop, guess I won't do that.  :~)  Thanks.  -sc

-- 
Sean Chittenden

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Curt Sampson]

On Mon, 9 Sep 2002, Sean Chittenden wrote:

> I'm thinking about changing this from a beta port to a -devel port
> that I'll periodically update with snapshots.  I'll turn on -O6 for
> the -devel port and -O2 for production for now.  If I don't hear of
> any random bogons in the code I'll see if I can't increase it further
> to -O3 and beyond at a slow/incremental rate.

Keep in mind that, while gcc is pretty stable for i386, the higher
optimization levels (above -O2) do tend to have bogons on other
processors, that vary with which version of gcc you're running.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC


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

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Bruce Momjian]

Tom Lane wrote:
> Sean Chittenden <[EMAIL PROTECTED]> writes:
> > The size difference between -O and -O3 is only 200K or so... does
> > anyone think that it'd be safe to head to -O6 on a wide scale?
> 
> Dunno.  I'm not aware of any bits of the code that are unportable enough
> to break with max optimization of any correct compiler.  But you might
> find such a bug.  Or a bug in your compiler.  Are you feeling lucky
> today?
> 
> My feeling is that gcc -O2 is quite well tested with the PG code.
> I don't have any equivalent confidence in -O6.  Give it a shot for
> beta-testing, for sure, but I'm iffy about calling that a
> production-grade database release...

And of course the big question is whether you will see any performance
improvement with -O6 vs. -O2.  My guess is no.

> 
> > I'm even thinking about going so far as to have flex required for the
> > build dependencies and setting -Cf or -CF for building the scanner
> > (need to check the archives for which turned out to be faster).
> 
> Um, didn't we do that stuff already in the standard build?  AFAIK
> you cannot build PG with any lexer except flex, and Peter already
> hacked the flags.

Yes, I thought that was a done deal too.

> > I'm also tinkering with the idea of automatically turn off fsync if
> > optimize is set.
> 
> No-bloody-way.  Trusting your compiler is an entirely separate issue
> from whether you trust your disk hardware, power source, etc.  Puh-leez
> do not muddy the waters by introducing a port-specific variation in
> choices that only the DBA of a particular installation should make.

Tom is right.  Hardware/power reliability is a different issue.

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

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Bruce Momjian]

Sean Chittenden wrote:
> Hrm, I should go check the archives, but I thought what was used was
> one step below -C[fF] and was used because of size concerns for
> embedded databases.  My memory for what happens on mailing lists seems
> to be fading though so I'll look it up.

I see in parser/Makefile:

FLEXFLAGS = -CF

and

ifdef FLEX
$(FLEX) $(FLEXFLAGS) -o'$@' $<
else
@$(missing) flex $< $@
endif

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

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

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Bruce Momjian]

Curt Sampson wrote:
> On Mon, 9 Sep 2002, Sean Chittenden wrote:
> 
> > I'm thinking about changing this from a beta port to a -devel port
> > that I'll periodically update with snapshots.  I'll turn on -O6 for
> > the -devel port and -O2 for production for now.  If I don't hear of
> > any random bogons in the code I'll see if I can't increase it further
> > to -O3 and beyond at a slow/incremental rate.
> 
> Keep in mind that, while gcc is pretty stable for i386, the higher
> optimization levels (above -O2) do tend to have bogons on other
> processors, that vary with which version of gcc you're running.

Yes, last I heard, FreeBSD/alpha doesn't work in PostgreSQL if compiled
with -O2.  You can see template/freebsd for that alpha flag override.

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

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

Re: [JDBC] [HACKERS] problem with new autocommit config parameter

[Curt Sampson]

On Mon, 9 Sep 2002, Tom Lane wrote:

> If autocommit=off really seriously breaks JDBC then I don't think a
> simple SET command at the start of a session is going to do that much
> to improve robustness.  What if the user issues another SET to turn it
> on?

You mean, to turn it off again? The driver should catch this, in theory.

In practice we could probably live with saying, "Don't use SET
AUTOCOMMIT; use the methods on the Connection class instead."

Probably the driver should be changed for 7.3 just to use the server's
SET AUTOCOMMIT functionality

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Sean Chittenden]

> > > The size difference between -O and -O3 is only 200K or so... does
> > > anyone think that it'd be safe to head to -O6 on a wide scale?
> > 
> > Dunno.  I'm not aware of any bits of the code that are unportable enough
> > to break with max optimization of any correct compiler.  But you might
> > find such a bug.  Or a bug in your compiler.  Are you feeling lucky
> > today?
> > 
> > My feeling is that gcc -O2 is quite well tested with the PG code.
> > I don't have any equivalent confidence in -O6.  Give it a shot for
> > beta-testing, for sure, but I'm iffy about calling that a
> > production-grade database release...
> 
> And of course the big question is whether you will see any performance
> improvement with -O6 vs. -O2.  My guess is no.

Agreed, however some of the loop-unrolling might prove to have some
optimization, but we'll see.  I'd like to think that there's some
actual value in -O6 beyond the geek appeal of being able to say it's
been compiled with all the optimizations possible.  ::shrug::

> > I'm thinking about changing this from a beta port to a -devel port
> > that I'll periodically update with snapshots.  I'll turn on -O6 for
> > the -devel port and -O2 for production for now.  If I don't hear of
> > any random bogons in the code I'll see if I can't increase it further
> > to -O3 and beyond at a slow/incremental rate.
> 
> Keep in mind that, while gcc is pretty stable for i386, the higher
> optimization levels (above -O2) do tend to have bogons on other
> processors, that vary with which version of gcc you're running.

Fully aware of these!!!  I've got a few systems running GCC 3.2 and
3.3 and it's touch and go above -O3, but most of these bogons are
mozilla and GUI related when it comes to complex thread handling.  For
more simple single threaded procs, the bugs get found out about pretty
quickly and end up making their way back into the GCC src tree.  I'm
thinking -O6 for the -devel port should work nicely as a way of
testing things out.  -sc

-- 
Sean Chittenden

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

http://archives.postgresql.org

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Bruce Momjian]

Sean Chittenden wrote:
> > > > The size difference between -O and -O3 is only 200K or so... does
> > > > anyone think that it'd be safe to head to -O6 on a wide scale?
> > > 
> > > Dunno.  I'm not aware of any bits of the code that are unportable enough
> > > to break with max optimization of any correct compiler.  But you might
> > > find such a bug.  Or a bug in your compiler.  Are you feeling lucky
> > > today?
> > > 
> > > My feeling is that gcc -O2 is quite well tested with the PG code.
> > > I don't have any equivalent confidence in -O6.  Give it a shot for
> > > beta-testing, for sure, but I'm iffy about calling that a
> > > production-grade database release...
> > 
> > And of course the big question is whether you will see any performance
> > improvement with -O6 vs. -O2.  My guess is no.
> 
> Agreed, however some of the loop-unrolling might prove to have some
> optimization, but we'll see.  I'd like to think that there's some
> actual value in -O6 beyond the geek appeal of being able to say it's
> been compiled with all the optimizations possible.  ::shrug::

And you think the answer is ...  I think we all know what the answer is.
:-)

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

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

http://www.postgresql.org/users-lounge/docs/faq.html

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Sean Chittenden]

> > > > My feeling is that gcc -O2 is quite well tested with the PG
> > > > code.  I don't have any equivalent confidence in -O6.  Give it
> > > > a shot for beta-testing, for sure, but I'm iffy about calling
> > > > that a production-grade database release...
> > > 
> > > And of course the big question is whether you will see any
> > > performance improvement with -O6 vs. -O2.  My guess is no.
> > 
> > Agreed, however some of the loop-unrolling might prove to have
> > some optimization, but we'll see.  I'd like to think that there's
> > some actual value in -O6 beyond the geek appeal of being able to
> > say it's been compiled with all the optimizations possible.
> > ::shrug::
> 
> And you think the answer is ...  I think we all know what the answer
> is.  :-)

I think the newbie/l33t geek appeal of being able to say something's
compiled and works with -O6 is probably worth more in terms of
marketing than it is interms of actual technical merrit.  Those that
need 10K lookups per second should be serializing data into a bdb file
with a unique key and not using a relational database (or helping out
with pgsql-replication).  :~) -sc

-- 
Sean Chittenden

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

http://archives.postgresql.org

Re: [HACKERS] Rule updates and PQcmdstatus() issue

[Steve Howe]

Hello Bruce,

Monday, September 9, 2002, 11:13:20 PM, you wrote:

BM> Steve Howe wrote:
>> Because the affected commands are supposed to give you back
>> information on what your INSERT/UPDATE/DELETE commands, not what is
>> making behind the scenes.
>> 
>> And it seems that other people in the thread agree with me, please
>> read thread.
>> 
>> Since you are probably very familiar with the rules system, why don't
>> you vote on a proposal too, or just suggest yours. Your opinion is
>> very important. I'm not saying I'm the truth owner; I'm just another
>> developer who needs a feature working again.

BM> Jan actually did vote in the first round which appears in TODO.detail. 
BM> He voted that if the INSTEAD rule had only _one_ statement, return that,
BM> if not, return nothing.
We still need Tom's word and Hiroshi, since they were the most related
to the subject, and the other developer's opinion... :)

- 
Best regards,
 Steve Howe   mailto:[EMAIL PROTECTED]


---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Proposal: Solving the "Return proper affected tuple

[Steve Howe]

Hello Christopher,

Monday, September 9, 2002, 11:36:44 PM, you wrote:

CKL> Sorry guys - it's killing me!  It's 'affected' in the subject line - not
CKL> 'effected'!!! Sigh :)

lol... my bad, English is not my primary language and these things
just seem to happen sometimes... I apologize.

- 
Best regards,
 Steve Howe   mailto:[EMAIL PROTECTED]


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

Re: [JDBC] [HACKERS] problem with new autocommit config parameter and jdbc

[Tom Lane]

Curt Sampson <[EMAIL PROTECTED]> writes:
> Probably the driver should be changed for 7.3 just to use the server's
> SET AUTOCOMMIT functionality

That should happen eventually, IMHO, but I am not going to tell the JDBC
developers that they must make it happen for 7.3.  They've already got a
pile of much-higher-priority things to fix for 7.3, like schema
compatibility and dropped-column handling.

My feeling about the original complaint is very simple: setting server
autocommit to off is not supported with JDBC (nor is it fully supported
with any other of our frontend clients, right at this instant, though
that may improve somewhat before 7.3 release).  If you don't like it,
tough; contribute the required fixes or stop complaining.  Someone else
will fix it when they get around to it, but there are bigger problems to
deal with first.  Autocommit is only a work-in-progress today, not
something that we promise will do anything useful for anybody.

regards, tom lane

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

Re: [HACKERS] Script to compute random page cost

[Tom Lane]

Curt Sampson <[EMAIL PROTECTED]> writes:
> On Mon, 9 Sep 2002, Tom Lane wrote:
>> ...  We are trying to measure the behavior when kernel
>> caching is not helpful; if the database fits in RAM then you are just
>> naturally going to get random_page_cost close to 1, because the kernel
>> will avoid doing any I/O at all.

> Um...yeah; another reason to use randread against a raw disk device.
> (A little hard to use on linux systems, I bet, but works fine on
> BSD systems.)

Umm... not really; surely randread wouldn't know anything about
read-ahead logic?

The reason this is a difficult topic is that we are trying to measure
certain kernel behaviors --- namely readahead for sequential reads ---
and not others --- namely caching, because we have other parameters
of the cost models that purport to deal with that.

Mebbe this is an impossible task and we need to restructure the cost
models from the ground up.  But I'm not convinced of that.  The fact
that a one-page shell script can't measure the desired quantity doesn't
mean we can't measure it with more effort.

regards, tom lane

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly

Re: [HACKERS] Script to compute random page cost

[Curt Sampson]

On Mon, 9 Sep 2002, Tom Lane wrote:

> Curt Sampson <[EMAIL PROTECTED]> writes:
> > On Mon, 9 Sep 2002, Tom Lane wrote:
> >> ...  We are trying to measure the behavior when kernel
> >> caching is not helpful; if the database fits in RAM then you are just
> >> naturally going to get random_page_cost close to 1, because the kernel
> >> will avoid doing any I/O at all.
>
> > Um...yeah; another reason to use randread against a raw disk device.
> > (A little hard to use on linux systems, I bet, but works fine on
> > BSD systems.)
>
> Umm... not really; surely randread wouldn't know anything about
> read-ahead logic?

Randread doesn't know anything about read-ahead logic, but I don't
see how that matters one way or the other. The chances of it reading
blocks sequentially are pretty much infinitesimal if you're reading
across a reasonably large area of disk (I recommend at least 4GB),
so readahead will never be triggered.

> The reason this is a difficult topic is that we are trying to measure
> certain kernel behaviors --- namely readahead for sequential reads ---
> and not others --- namely caching, because we have other parameters
> of the cost models that purport to deal with that.

Well, for the sequential reads, the readahead should be trigerred
even when reading from a raw device. So just use dd to measure
that.  If you want to slightly more accurately model postgres'
behaviour, you probably want to pick a random area of the disk,
read a gigabyte, switch areas, read another gigabyte, and so on.
This will model the "split into 1GB" files thing.

cjs
-- 
Curt Sampson  <[EMAIL PROTECTED]>   +81 90 7737 2974   http://www.netbsd.org
Don't you know, in this new Dark Age, we're all light.  --XTC


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

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Neil Conway]

Sean Chittenden <[EMAIL PROTECTED]> writes:
> Agreed, however some of the loop-unrolling might prove to have some
> optimization, but we'll see.  I'd like to think that there's some
> actual value in -O6 beyond the geek appeal of being able to say it's
> been compiled with all the optimizations possible.  ::shrug::

BTW, -O3 is the highest GCC optimization level; anything higher than
that is synonymous with -O3, I believe. Also, -O3 doesn't have
anything to do with loop unrolling, AFAIK.

As for the value of enabling that flag, it depends IMHO on the
performance gain you see. If there is a significance difference, let
-hackers know, and it might be worth considering enabling it by
default for certain platforms. If the performance difference is
negligible (which is what I'd suspect), I don't think it's worth the
code bloat, reduced debuggability, or the potential for running into
more compiler bugs.

Also, if -O3 *is* a good compiler option, I dislike the idea of
enabling it for your own packages but no one else's. IMHO distributors
should not futz with packages more than is strictely necessary, and a
change like this seems both unwarranted, and potentially dangerous. If
-O3 is a good idea, we should make the change for the appropriate
platforms in the official source, and let it get the widespread
testing it requires.

Cheers,

Neil

-- 
Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC


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

Re: [HACKERS] Optimization levels when compiling PostgreSQL...

[Neil Conway]

Sean Chittenden <[EMAIL PROTECTED]> writes:
> Has there been any talk of doing incremental -snapshots of the code
> base?

I don't really see the point. Snapshots of development code are
available from CVS anyway -- and if you're going to be running a
pre-alpha version of a relational database, I don't think that
knowledge of CVS is an onerous requirement.

At any rate, the problem with releasing snapshots is that the system
catalogs would change so often that upgrading between snapshots would
be a headache. i.e. the changes required to upgrade from a 2 week old
development snapshot to a current snapshot would still be non-trivial,
significantly reducing the usefulness of snapshots, IMHO.

Cheers,

Neil

-- 
Neil Conway <[EMAIL PROTECTED]> || PGP Key ID: DB3C29FC


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