Re: [BUGS] 7.1beta1 JDBC Nested cursor problem

[peter]

Quoting Stu Coates <[EMAIL PROTECTED]>:

> I think that I've found a little bug in the 7.1beta1 JDBC drivers.
> Attached is a simple test case which produces the problem on my setup
> (LinuxPPC 2000, on Apple PowerMac G3-400Mhz, 512MB).  It would seem that
> the drivers and/or the DBMS has a problem with nested queries, even
> simple ones.
> 
> Here's the stacktrace:
> 
> Bad Long
>   at org.postgresql.jdbc2.ResultSet.getLong(ResultSet.java:284)
>   at Test.go(Test.java:35)
>   at Test.main(Test.java:15)
> 
> I'm using the 7.0.2 JDBC drivers against the 7.1beta1 backend and
> everything seems to be working fine with those, just the new ones
> (7.1beta1) have this problem.

Yes, I'm still tracing this one. I discovered it a few weeks ago while looking 
at rewriting the standard queries in DatabaseMetaData, but the backend seems to 
return not an Int/Long but the type name instead.

It's interesting to see that other code is getting the same problem.

Peter

[BUGS] Trying Cygwin version of PostgreSQL

[Peter]

Hello PostgreSQL,
I am trying to install the PostgreSQL 7.2 downloaded by Cygwin but the
install does not match any of the documentation. Help!

Last year I installed PostgreSQL 7.1 on NT for a one off project then
deleted it. I used the pg download then added Cygwin using the Cygwin
download. Everything worked after I put together documentation from
several included files, a few web pages and lots of experiments.

This time I am attempting to use the Cygwin download including
PostgreSQL. The documentation is scattered over several documents and
none match the actual installation process. 

I resorted to trial and error. Initdb hangs when "Creating template1
database" so there must be a step missing. Given the many "try this"
paragraphs across several documents, I estimate the number of
combinations will exceed the storage capacity of a 32 bit integer.

Should I give up on Cygwin and go back to what I did last time? Is there
a document or web page that describes a successful installation step by
step?

Peter

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

[BUGS] Now trying Cygwin with normal download of PostgreSQL

[Peter]

I have the latest Cygwin and PostgreSQL 7.2. Using the installation
instructions at http://people.freebsd.org/~kevlo/postgres/portNT.html,
make produces the messages:
pg_passwd.o(.text+0x763):pg_passwd.c: undefined reference to 'crypt'
collect2: ld returned 1 exit status
make[3]: *** [pg_passwd] Error 1

The installation using the standalone PostgreSQL download works better
than trying to install the Cygwin version. The target of this project is
to install SQL-ledger but, if PostgreSQL works well, I might use it for
other projects.

The reason I persevere on NT is I need solutions that work on NT,
Solaris, and Linux. MySQL, using Berkeley tables for transactions, does
work on all those platforms but I hear the Berkeley tables use page
locking instead of row locking. Oracle is an easy to deploy alternative
but I am not about to pay $40,000 for an Oracle licence just so I can
perform a few hours development each month. What I need is a bullet
proof way of installing PostgreSQL on NT.

Peter

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

[BUGS] Trying Cygwin version of PostgreSQL again

[Peter]

Justin Clift suggested:
http://www.ejip.net/faq/postgresql_win_setup_faq.jsp
I started from scratch following the page and adapting it where it is
out of date. I reached the stage of starting postmaster and receiving
the message:
DEBUG: pq_recvbuf: unexpected EOF on client connection
DEBUG: incomplete startup packet

Peter

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

Re: [BUGS] BUG #4295: DELETE WHERE Condition

[Peter Eisentraut]

Am Freitag, 11. Juli 2008 schrieb Fred Perniss:
> In Postgres the switch add_mising_from=on the SQL-Command
>
> DELETE FROM t2
> WHERE (t2.t2_t1_id=t1.t1_id)
> AND t1.t1_inhalt=5
> ;
>
> works (with message in Log).
>
> If the switch add_missing_from=off following Error-Message will be promted:
>
> Fehlender Eintrag in FROM Klausel für Tabelle t1

You want to use DELETE .. USING.  See the man page for details.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Re: BUG #4319: lower()/upper() does not know about UNICODE case mapping

[Peter Eisentraut]

Am Tuesday, 22. July 2008 schrieb valgog:
> Why Postgres allows creating UNICODE database with the locale, that
> can possibly corrupt my data?

It doesn't allow it, as of 8.3.  In 8.2 it does, but we have fixed that, for 
the reasons that are becoming obvious to you now.

Perhaps part of the problem is that en_EN isn't actually a valid locale, as 
far as I can tell, unless SUSE has invented a new country. :)  Try locale -a 
and pick one from that list.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4344: initdb -L timezone directory

[Peter Eisentraut]

Am Thursday, 7. August 2008 schrieb Tom Lane:
> I rather wonder whether -L has any reason to live at all.  initdb's
> default is to locate PGSHAREDIR relative to where it finds the backend
> executable, which is consistent with what the backend itself is going
> to do.  Is there any scenario where specifying a different location
> wouldn't be broken?

I think the -L option only exists for legacy reasons.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #3818: Cross compilation problems

[Peter Eisentraut]

Am Saturday, 9. August 2008 schrieb Richard Evans:
> I've made a couple of patches, for 8.3.3 and 8.2.9, which enable cross
> compilation for windows (mingw32) from a unix platform.

This looks good, with a couple of tweaks.  I don't think we are going to be 
making these kinds of changes in the 8.2 and 8.3 branches, so I suggest you 
prepare a patch for 8.4.  Some of the makefiles have changed, so a bit of 
work might be involved.

> 2. in various makefiles, checking BUILDOS not PORTNAME when deciding
> whether to use 'pwd -W' or just 'pwd'

I was wondering, what is the difference between these?  And couldn't we just 
use $(CURDIR) instead?

> 5. Working round a binutils-2.18 bug in windres when the rc file has /
> or \ in the path

What is the status of this bug?  Shouldn't it be fixed instead?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] xmlAgg not found..

[Peter Eisentraut]

Am Friday, 15. August 2008 schrieb Tariq Aziz:
> I have installed PostGRESql 8.3 and try in to retrieve data in xml
> format for which I need to use function "xmlAgg". but that's not
> supported in the instance I have configured. Kindly guide me If there is
> some service pack or patch required to get this function working for me.

Since you haven't actually shown what you are trying to do and what results 
you are getting, it is pretty much impossible to help you.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4357: SERIAL pseudotype and related SEQUENCE object

[Peter Eisentraut]

Am Friday, 15. August 2008 schrieb Grigory Zinin:
> We can create SERIAL field. But INTEGER type will be really set. It's well
> known that INTEGER field doesn't match values more than 4 bytes. But
> related SEQUENCE object has a 8 byte value.
> It looks strange for me that 4 bytes of these 8 byte value will never be
> used.
> Is it bug or feature?

Well, it is that way by design, because most people will want the faster int4 
type for their key fields.  If you want a bigger type, you have of course 
bigserial available.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4340: SECURITY: Is SSL Doing Anything?

[Peter Eisentraut]

Dan Kaminsky wrote:
> >> 1) No roots (but still works for some unknown reason)
> >> 2) Explicitly configured corporate roots
> >> 3) Explicitly configured corporate roots, AND global roots
> >> 4) Global roots (but still works for some unknown reason)

> So, if you do nothing special, it's #1?  Sounds like the path of least
> resistance is no security.  Uh oh.

Yeah, in the average, if not common case, a user interested in SSL use would 
probably just follow the recipe in the documentation for creating and 
installing a self-signed certificate with no certificate checking in the 
client.  Which, as you correctly observe, is pretty much completely useless.

Someone should probably redesign, reconfigure, and redocument this.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Postgresql v8.3.3 + Perl v5.10

[Peter Eisentraut]

[EMAIL PROTECTED] wrote:
> Is this possible?

That is a question, not a bug.

But yes, it is possible.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #3818: Cross compilation problems

[Peter Eisentraut]

Richard Evans wrote:
> I'm not sure why the makefiles need the current directory. pwd -W is
> specific to mingw, I think it gives the directory in windows format.  It
> has to be changed for cross compilation otherwise you get errors.

What does $(CURDIR) resolve to on mingw?  Try the following makefile:

default:
@echo $(shell pwd)
@echo $(shell pwd -W)
@echo $(CURDIR)

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Postgresql v8.3.3 + Perl v5.10

[Peter Eisentraut]

[EMAIL PROTECTED] wrote:
> 1. Install Perl v5.10
> 2. Install PG
> 3. PG does not see that machine has Perl
> 4. Remove Perl and PG
> 5. Instll Perl v5.8
> 6. Install PG
> 7. PG work fine
> Problem is that PG does not recognize Perl v5.10 =(

Please provide the actual commands and the actual output, not abstract 
descriptions.

I can tell you that I am building and running PostgreSQL and Perl 5.10 just 
fine.  So the problem must be in the details, which are not evident from your 
description.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] range value problem with double precision [PG 8.3.3]

[Peter Eisentraut]

Zahid Khan wrote:
> Hi ,
>
>  I see one issue pg 8.3.3 .
>
> 1.
> According to the documentation of pg "The double precision type
> typically has a range of around 1E-307 to 1E+308 with a precision of at
> least 15 digits".
>
> ref:-
>  http://www.postgresql.org/docs/8.3/static/datatype-numeric.html
>
> but in PG8.3 the values less then minimum range are being inserted.
>
> e.g
> . on insertion of 1E-308 we should get an error of "out of range or
> underflow value" but this value is being accepted by server.

The operating words are "typically" and "around".  You can put in smaller and 
larger values, but then the precision is going to degrade, as you can observe 
here:

peter=# select '1E-305'::float8;
 float8

 1e-305
(1 row)

peter=# select '1E-310'::float8;
float8
---
 9.97e-311
(1 row)

peter=# select '1E-315'::float8;
float8
---
 9.9998481684e-316
(1 row)

peter=# select '1E-320'::float8;
float8
---
 9.99988867182683e-321
(1 row)

peter=# select '1E-325'::float8;
ERROR:  22003: "1E-325" is out of range for type double precision
LOCATION:  float8in, float.c:426

The following, however, appears to be a bug:

peter=# select '1E-324'::float8;
ERROR:  22P02: invalid input syntax for type double precision: "1E-324"
LOCATION:  float8in, float.c:431

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Latin2 and UTF-8 encoding.

[Peter Eisentraut]

On Monday 25 August 2008 13:29:14 [EMAIL PROTECTED] wrote:
> ERROR:  encoding LATIN2 does not match server's locale en_AU.UTF-8
> DETAIL:  The server's LC_CTYPE setting requires encoding UTF8.

In 8.3, it no longer works to create databases of different or incompatible 
encodings.  You need to pick one encoding for all databases and you need to 
pick a locale setting to matches that.

If you work for a Hungarian library, you probably don't want a locale setting 
en_AU... either.  Instead, you probably want hu_HU.utf8.

> The problem is that we need both UTF-8, LATIN1 and LATIN2 characters.

UTF-8 can store all characters that LATIN1 and LATIN2 can encode.  You just 
need to configure your client encoding correctly and the data sent by the 
client will automatically be converted to UTF-8 and back.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4281: some types of errors do not log statements

[Peter Eisentraut]

Thomas H. wrote:
> maybe its by design (to not insert badly encoded characters into the
> utf8 encoded logs)? nevertheless to debug those faulty programm/codes,
> it would help to see what query provokes the error...

Well, the problem is mainly that there is no query, because the bytes arriving 
are garbage.  A human observer could make sense of it in some cases, but not 
a computer in the general case.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] Aborted VACUUM FULL -> crash + corruption (xlog non-existent)

[Peter Schuller]

ns 
"stats_start_collector" and "stats_row_level".
Aug 26 17:17:11 host postgres[28512]: [4-1] user=,db= LOG:  database system was 
interrupted at 2008-08-26 17:15:52 CEST
Aug 26 17:17:11 host postgres[28512]: [5-1] user=,db= LOG:  could not open file 
"pg_xlog/0001001800ED" (log file 24, segment 237): No such file or 
directory
Aug 26 17:17:11 host postgres[28512]: [6-1] user=,db= LOG:  invalid primary 
checkpoint record
Aug 26 17:17:11 host postgres[28512]: [7-1] user=,db= LOG:  could not open file 
"pg_xlog/0001001800ED" (log file 24, segment 237): No such file or 
directory
Aug 26 17:17:11 host postgres[28512]: [8-1] user=,db= LOG:  invalid secondary 
checkpoint record
Aug 26 17:17:11 host postgres[28512]: [9-1] user=,db= PANIC:  could not locate 
a valid checkpoint record
Aug 26 17:17:11 host postgres[28511]: [4-1] user=,db= LOG:  startup process 
(PID 28512) was terminated by signal 6
Aug 26 17:17:11 host postgres[28511]: [5-1] user=,db= LOG:  aborting startup 
due to startup process failure

The pg_xlog directory contains:

-rw---  1 postgres postgres 16777216 Aug 26 17:16 0001001800EE
drwx--  2 postgres postgres   305232 Aug 26 17:14 archive_status

Note that the archival of the ED xlog file started at 17:14:52,
and I cancelled the VACUUM FULL at 17:16:06.

It is entirely likely, though I cannot say for sure, that I started
the VACUUM FULL prior to the archival of ...ED.

I still have the entire pgsql database directory untouched except for
the manual start-up attempt. I am afraid I cannot dump the thing
somewhere for inspection (it contains confidential information), but I
can assist in inspecting the contents.

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <[EMAIL PROTECTED]>'
Key retrieval: Send an E-Mail to [EMAIL PROTECTED]
E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org



pgpooalCAihTU.pgp
Description: PGP signature

Re: [BUGS] Aborted VACUUM FULL -> crash + corruption (xlog non-existent)

[Peter Schuller]

> > -rw---  1 postgres postgres 16777216 Aug 26 17:16 
> > 0001001800EE
> > drwx--  2 postgres postgres   305232 Aug 26 17:14 archive_status
> > 
> > Note that the archival of the ED xlog file started at 17:14:52,
> > and I cancelled the VACUUM FULL at 17:16:06.
> 
> What's your archive_command?

archive_command = '/path/to/wal-archive.sh "%p" "%f"'

(only path to script changed for privacy)

The script just removes the archive file; the intent was to have
archival enabled such that we could start using it for real without
re-starting the server in the future. The script contents is:

===
#!/bin/bash

set -e

path="$1"
file="$2"

# put segment somewhere useful here

rm $path

echo "$(date): $path" > /tmp/wal-archive-stamp

exit 0
===

It would be embarressing if I caused this problem myself by
misunderstanding wal_archiving. My understanding has been that once
wal_archive gets called, no one ever cares what happens with the file
except if I want to do PITR (since the whole point is to offload it
somewhere or similar).

I'll go and re-read the documentation on this immediately. If this is
the problem, I do apologies for the noise and people's time.

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <[EMAIL PROTECTED]>'
Key retrieval: Send an E-Mail to [EMAIL PROTECTED]
E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org



pgp8RkHOz5fkY.pgp
Description: PGP signature

Re: [BUGS] Aborted VACUUM FULL -> crash + corruption (xlog non-existent)

[Peter Schuller]

> I'll go and re-read the documentation on this immediately. If this is
> the problem, I do apologies for the noise and people's time.

So it is, as far as I can tell. The script should not remove the
file. I don't know what I was thinking. This was not a smart thing to
do...

I sincerely apologies for wasting people's time; thank you VERY much
for the quick response!

I will go hide in a corner now...

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <[EMAIL PROTECTED]>'
Key retrieval: Send an E-Mail to [EMAIL PROTECTED]
E-Mail: [EMAIL PROTECTED] Web: http://www.scode.org



pgpipP2nZwPY5.pgp
Description: PGP signature

Re: [BUGS] issue with postgres/xml

[Peter Eisentraut]

Cindy Moore wrote:

I looked through the lists and couldn't figure out where else to put
this.  I'm trying to create indices on xpath expressions for columns
of type xml.
I'm running postgres version 8.3.3, running on solaris.

Text=# create index trclass on lsj_xml ((xpath
('//[EMAIL PROTECTED]"tr"]/text()', entry)::text));
ERROR:  functions in index expression must be marked IMMUTABLE


xpath returns an array.  I'm not sure offhand why the cast from array to 
text is not immutable, but if you select an array element, it works:


create index trclass on lsj_xml (((xpath('//[EMAIL PROTECTED]"tr"]/text()', 
entry))[1]::text));



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4421: convert_to() should be immutable

[Peter Eisentraut]

Andreas Peer wrote:
Well, the use case is a strange one... I would like to use a varchar() 
column for storing a variable-length vector of integers. The numbers are 
represented by the codepoints. Therefore, I need to sort them as binary 
data, not as characters. I would often need to get all the vectors that 
lie in between to vectors, therefore I need the "binary" index.
And the code should be as database independent as possible, therefore I 
cannot use an array or another data type that may not be supported by 
other DBMS.


What makes you think messing around with encoding conversions is going 
to be portable.  Arrays are at least in the SQL standard.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] ERROR: unexpected data beyond EOF in block XXXXX of relation "file"

[Peter Eisentraut]

David Fetter wrote:

On Sun, Sep 28, 2008 at 11:51:49AM -0700, austijc wrote:

That's going to be a problem for the continued viability of
Postgres.


Funny, I thought running a DBMS over a known-unreliable storage system
was a problem for the continued viability of Oracle.  When, not if,
people lose enough data to this silliness, they'll be thinking hard
about how to get Oracle out and something reliable in.


NFS is not "unreliable", it is just different in some respects from 
other file systems.  That paired with some poor NFS implementations in 
certain operating systems and this evident general misunderstanding make 
it a poor fit for PostgreSQL.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Locale (unsupported) bug. uk_UA.KOI8-U

[Peter Eisentraut]

ivocs admin wrote:

initdb -E ISO-8859-1 -D data/


This is probably not a good idea.  You should specify a locale and let 
initdb figure out the matching encoding.  Otherwise you might end up 
with an incompatible locale/encoding combination.  (initdb would 
probably not even allow this to proceed, but you have other problems as 
well, see below.)


The files belonging to this database system will be owned by user 
"postgres".

This user must also own the server process.

The database cluster will be initialized with locale uk_UA.KOI8-U.
could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U"
initdb: could not find suitable text search configuration for locale 
uk_UA.KOI8-U


PostgreSQL currently doesn't support the KOI8-U encoding.  You could 
consider using UTF-8 instead.  Try running this


initdb --locale=uk_UA.utf8 -D data/


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4462: Adding COUNT to query causes massive slowdown

[Peter Eisentraut]

Jussi Pakkanen wrote:

However when I try to count the amount of distinct codes, I get this:


EXPLAIN SELECT COUNT(DISTINCT code) FROM log;
   QUERY PLAN

-
 Aggregate  (cost=100801488.30..100801488.31 rows=1 width=10)
   ->  Seq Scan on log  (cost=1.00..100721245.24 rows=32097224
width=10)
(2 rows)


This looks like you have one of the enable_${plantype} parameters turned 
off.  1 is the penalty that is added when a plantype if turned off.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4461: Should avoid create tblspcs in system location

[Peter Eisentraut]

Dmitry Orlov wrote:

if I issue create tablespace user_ts location
'/var/lib/postgresql/8.3/main/pg_tblspc' then appears nested links in
/var/lib/postgresql/8.3/main/pg_tblspc. So should avoid to create
tablespaces in pg_tblspc


If this is the location you give it, why should it avoid creating 
tablespaces there?



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Locale (unsupported) bug. uk_UA.KOI8-U

[Peter Eisentraut]

Tom Lane wrote:

Peter Eisentraut <[EMAIL PROTECTED]> writes:

ivocs admin wrote:

The database cluster will be initialized with locale uk_UA.KOI8-U.
could not determine encoding for locale "uk_UA.KOI8-U": codeset is "KOI8-U"
initdb: could not find suitable text search configuration for locale 
uk_UA.KOI8-U


PostgreSQL currently doesn't support the KOI8-U encoding.  You could 
consider using UTF-8 instead.  Try running this

initdb --locale=uk_UA.utf8 -D data/


It's still not going to find a suitable text search configuration,
since we haven't got anything for Ukranian ...


But initdb will still succeed, so if you don't need the text search 
feature, you can proceed.  If you do need text search, you can possibly 
get away with using the configuration for russian (see initdb -T 
option).  Do let us know if we need to make any adjustments to support 
Ukrainian better.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4465: GROUP BY is not to SQL standard

[Peter Eisentraut]

Tony Marston wrote:

The following bug has been logged online:

Bug reference:  4465
Logged by:  Tony Marston
Email address:  [EMAIL PROTECTED]
PostgreSQL version: 8.3.4
Operating system:   Windows XP
Description:GROUP BY is not to SQL standard
Details: 


The Postgresql implementation of GROUP BY does not conform to either the
1999 or 2003 SQL standard. The documentation states that every field in the
SELECT list which is not aggregated must be specified in the GROUP BY
clause. While this was true in the 1992 standard, in 1999 this was changed
to "any non-aggregated column appearing in the SELECT list is functionally
dependent upon the GROUP BY clause". In the example both p.name and p.price
are functionally dependent on product_id, therefore there is no need for
them to be included in the GROUP BY clause.



SQL 2003 (and similarly 1999) also says

"""
Without Feature T301, “Functional dependencies”, in conforming SQL 
language, if T is a grouped table,
then in each  contained in the , each 
 that references a
column of T shall reference a grouping column or be specified in an 
aggregated argument of a 
specification>.
"""

and as you can read in the documentation 
(http://www.postgresql.org/docs/current/static/unsupported-features-sql-standard.html), 
PostgreSQL does not support feature T301.


Therefore the implemented behavior is not "wrong", but it could arguably 
be enhanced.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4465: GROUP BY is not to SQL standard

[Peter Eisentraut]

Tony Marston wrote:

I think your definition of "Feature T301 Functional Dependencies" is
extremely questionable. A functional dependency in relational theory
automatically exists where a non-key column on a table is functionally
dependent on the key of that table. It is not something that can be turned
on or off with code, it is built into the design of the table, so it is
erroneous to say that "Postgresql does not support functional dependencies".
If you support both key and non-key columns on a table then you support
functional dependencies whether you like it or not.


I think you misunderstand what "Features" in the SQL standard mean. 
Surely a relational database system supports some kind of functional 
dependency system, and there is a common definition for that in the 
computer science literature (and there is yet another one in the SQL 
standard, part 2, clause 4.18).  But in the SQL standard, features 
contain conformance claims.  Your product can either conform to SQL:2003 
with T301, in which case it should behave as in 7.12 GR 15, or it can 
conform to SQL:2003 without T301, in which case it should behave as in 
7.12 CR 3.  Both of these can claim to conform to SQL, if they declare 
the details correctly.  The fact that T301 is labeled "Functional 
dependencies" is an informative label for what the feature generally 
tries to achieve, but it is not a normative description of the feature 
itself, since that is given elsewhere in the standard.


Since PostgreSQL is documented not to support feature T301, we apply 
7.12 CR 3, and no one has so far disputed that we do so incorrectly.


Now you might say, then PostgreSQL is not a real relational database 
system.  But I believe it is widely understood that no SQL 
implementation implements relational theory correctly.  That's a whole 
different can of worms.



As for your statement that PostgreSQL has never claimed that it is fully
SQL-compliant, every time I have posted a message to a PG newsgroup and
compared it with MySQL the immediate response which I receive has always
been along the lines of "don't compare PG with MySQL as that is a toy
database that does not follow the standards". As soon as I point out an SQL
standard that you DON'T follow I get a barrage of weasel words and pathetic
excuses.


The level of SQL conformance as evaluated by the PostgreSQL developers 
can be found in the documentation.  If MySQL has a similar document, you 
can draw your own comparisons.  I know of no such document, but I would 
guess that MySQL is less conforming than PostgreSQL.


You should, however, not mistake the chatter of the PostgrSQL newsgroup 
mob as facts, authorative statements, or representative of the opinions 
of the project leadership.  We cannot choose the people our newsgroups 
attract.


I am sorry that you interpret my attempts to explain my reading of the 
SQL standards to you as weasel words and pathetic excuses.  If you 
cannot restrict your comments to rational arguments and have to resort 
to name-calling, then I should probably not waste any more time 
discussing with you.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4465: GROUP BY is not to SQL standard

[Peter Eisentraut]

Tony Marston wrote:

You are still missing the point - "functional dependencies" is not a
separate module that can be turned on or off with code,


It is in the SQL standard.


they are inherent in
the database design. According to relational theory any non-key field on a
table is functionally dependent of the key of that table, so if you support
both key and non-key fields on a table then you automatically support
functional dependencies. How can you possibly say otherwise?


Again, you are confusing the SQL standard with relational theory, and an 
SQL standard conformance feature name with the computer science 
interpretation of that name.  I suggest you read Part 1 "Framework" of 
SQL 2003 which explains how SQL conformance works.



Where does it describe in the SQL standards EXACTLY what the term
"functional dependencies" means? Is it the same as in relational theory, or
is it something different?


We are talking about feature T301, which is defined by the Conformance 
rules that mention it.  That is all that matters.  It does not matter 
what the name of that feature is.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Upgrade from 7.4.5 to 8.3.3

[Peter Eisentraut]

Harvey, Allan AC wrote:

Zdenek,

Hmm, It does not look good. Your OS does not return proper 
information about 
codeset. Following code is broken:


setlocale(LC_CTYPE, ctype);
sys = nl_langinfo(CODESET);
sys = strdup(sys);

See
http://www.opengroup.org/onlinepubs/009695399/functions/nl_lan

ginfo.html

It seems you need to fix OS first. After that we can add CODESET mapping to 
PostgreSQL.



Zdenek


I don't think this is possible.

Can you give me an indication what problems I should look out for?


SQL_ASCII might work tolerably if all your clients use the same client 
encoding.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4502: pgAdmin Display Refresh Rate cannot be less that 7 secs

[Peter Mengaziol]

The following bug has been logged online:

Bug reference:  4502
Logged by:  Peter Mengaziol
Email address:  [EMAIL PROTECTED]
PostgreSQL version: EDB 8.3.0.12
Operating system:   Mac OS X 10.4.11
Description:pgAdmin Display Refresh Rate cannot be less that 7 secs
Details: 

Postgres Studio (pgAdmin III V.1.8.3-EDB (rev 7181))

I cannot set the Admin Display Refresh Rate cannot be less that 7 secs. I
was able to set it to 1 sec before.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4509: array_cat's null behaviour is inconsistent

[Peter Eisentraut]

Kevin Field wrote:

Section 9.2 in the docs say, 'The ordinary comparison operators yield null
(signifying "unknown") when either input is null.'  This applies to other
operators too.  For example, the result of tacking an unknown value onto a
known one is unknown, because you don't know what exactly you just tacked
on.  So

   select null::text || 'hello';

...returns NULL, which makes sense.  But then this

   select array_cat(null::integer[], '{3}'::integer[]);

...doesn't return NULL, which it should, for consistency.


Doesn't make sense to me either.  I found that this was changed between 
8.1 (where it returns null) and 8.2, but I find nothing in the 
respective release notes or commit messages about this change.  Tom?


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4509: array_cat's null behaviour is inconsistent

[Peter Eisentraut]

Tom Lane wrote:

It's really all kind of messy ... we need to trade off simplicity of
definition, ease of use, backwards compatibility, and standards
compliance (though the standard has only 1-D arrays so it's of just
limited help here).


AFAICT, the standard would certainly allow a type specification of

   basetype ARRAY ARRAY ARRAY ...

which gives you a multidimensional array (in the same sense that C has 
multidimensional arrays).  If my reading of everything is right, the 
subscript order that this would require is the opposite of what 
PostgreSQL uses for multidimensional arrays.  Talk about messy ...


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] executing SELECT xmlelement(name foo); causes "server closed the connection unexpectedly" Error

[Peter Eisentraut]

Sushil wrote:

I am trying to exploit XML features of PostgreSQL 8.3.0 DB.


You should upgrade to the latest 8.3 release, currently 8.3.5.  There 
were some fixes in this area (and other areas).



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] executing SELECT xmlelement(name foo); causes "server closed the connection unexpectedly" Error

[Peter Eisentraut]

Sushil wrote:
*postgres: postgres testdb [local] SELECT: symbol lookup error: 
postgres: postgres testdb [local] SELECT: undefined symbol: 
xmlNewTextWriterMemory*


Your problem appears to be here.  Check you libxml installation.  Maybe 
someone forgot to export this symbol.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4548: Documentation Contradiction for 8.3

[Peter Eisentraut]

On Monday 24 November 2008 23:37:02 Barry Reddy wrote:
> Can anyone clarify if this apparent contradiction is an oversight ?  Old
> documentation with new archiving documentation patched on, with no
> attention paid to the seeming contradiction on guidelines for filesystem
> backups of a running PG database ?

It is not trivial to understand the difference.  Perhaps the documentation 
doesn't make that entirely clear.

When you do a base backup with archiving on, that backup is only usable 
together with the WAL segments that were written while the backup mode was 
active.

So you can either make a backup: shutdown, tar, start
And then recover with: shutdown, untar, start

Or make a backup: pg_start_backup, tar, pg_stop_backup
And then recover with: shutdown, untar, recovery

In particular, it will *not* work to do:

Backup: pg_start_backup, tar, pg_stop_backup
Restore: shutdown, untar, start

So if you read both sections separately, they are correct.  It is only the 
partial overlap in the otherwise diffferent procedures that is confusing.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4551: Implementation of the "line" type..

[Peter Eisentraut]

On Friday 28 November 2008 15:31:51 Barry Sanford wrote:
> looking at PostgreSQL, I noticed that you have various geometric data types
> as standard.  However, I was somewhat surprised that you are attempting to
> implement the 'line' type via a two point scheme.  While this is okay for
> line segments, it totally sucks for infinite lines in a plane or space. 

Well, it's the way Euclid defined it.  (Yes, PostgreSQL is really 
old ... ;-) )

No really, the built-in geometry types are considered kind of legacy.  If you 
want to deal with something more modern, you may want to look into PostGIS.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Windows: pg_dump doesn't recognize -n option

[Peter Eisentraut]

On Thursday 04 December 2008 13:03:16 wstrzalka wrote:
> This is my output on Windows:
> ---
>- 

> D:\Code>pg_dump -U postgres -d test -n public
> pg_dump: too many command-line arguments (first is "-n")
> Try "pg_dump --help" for more information.

The -d option doesn't take an argument.  (It does not mean database.)  
Hence 'test' is the first non-option argument, and everything after that is 
also interpreted as non-option argument (unless you use GNU getopt).

What you appear to want should be written as

pg_dump -U postgres -n public test

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #3818: Cross compilation problems

[Peter Eisentraut]

On Sunday 07 December 2008 01:02:05 Bruce Momjian wrote:
> Where are we on this?

Some of this has been fixed.  But a lot of the code has been moved around 
between 8.3 and 8.4, so we can't just take take the patches as is.  If 
Richard is still interested, I suggest he try out 8.4 and then submit any 
remaining desired changes.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Bug

[Peter Eisentraut]

Vincent Predoehl wrote:

I was running fink and it said to report this:

configure: WARNING: krb5.h: present but cannot be compiled
configure: WARNING: krb5.h: check for missing prerequisite headers?
configure: WARNING: krb5.h: see the Autoconf documentation
configure: WARNING: krb5.h: section "Present But Cannot Be Compiled"
configure: WARNING: krb5.h: proceeding with the preprocessor's result
configure: WARNING: krb5.h: in the future, the compiler will take precedence
configure: WARNING: ##  ##
configure: WARNING: ## Report this to pgsql-bugs@postgresql.org 
 ##

configure: WARNING: ##  ##


Which PostgreSQL version?

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Bug

[Peter Eisentraut]

On Saturday 20 December 2008 02:15:05 Vincent Predoehl wrote:
> On Dec 19, 2008, at 3:48 AM, Peter Eisentraut wrote:
> > Vincent Predoehl wrote:
> >> I was running fink and it said to report this:
> >> configure: WARNING: krb5.h: present but cannot be compiled
> >> configure: WARNING: krb5.h: check for missing prerequisite
> >> headers?
> >> configure: WARNING: krb5.h: see the Autoconf documentation
> >> configure: WARNING: krb5.h: section "Present But Cannot Be
> >> Compiled"
> >> configure: WARNING: krb5.h: proceeding with the preprocessor's result
> >> configure: WARNING: krb5.h: in the future, the compiler will take
> >> precedence
> >> configure: WARNING: ##
> >>  ##
> >> configure: WARNING: ## Report this to pgsql-
> >> b...@postgresql.org <mailto:pgsql-bugs@postgresql.org> ##
> >> configure: WARNING: ##
> >>  ##
> >
> > Which PostgreSQL version?
>
> 8.3.5

Could you attach your config.log file then?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4596: information_schema.table_privileges is way too slow

[Peter Eisentraut]

On Friday 26 December 2008 01:12:26 Tom Lane wrote:
> More generally, there are a *whole lot* of ridiculous inefficiencies
> in our information_schema views; I'm surprised there haven't been
> more complaints about them.  Sometime someone ought to go through
> the whole set and see what other refactorings might be appropriate
> to make them work better.

Added to TODO.  Could probably be accomplished for 8.5.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Bug

[Peter Eisentraut]

Vincent Predoehl wrote:


On Dec 20, 2008, at 9:15 AM, Peter Eisentraut wrote:


On Saturday 20 December 2008 02:15:05 Vincent Predoehl wrote:

On Dec 19, 2008, at 3:48 AM, Peter Eisentraut wrote:

Vincent Predoehl wrote:

I was running fink and it said to report this:
configure: WARNING: krb5.h: present but cannot be compiled
configure: WARNING: krb5.h: check for missing prerequisite
headers?
configure: WARNING: krb5.h: see the Autoconf documentation
configure: WARNING: krb5.h: section "Present But Cannot Be
Compiled"
configure: WARNING: krb5.h: proceeding with the preprocessor's result
configure: WARNING: krb5.h: in the future, the compiler will take
precedence
configure: WARNING: ##
 ##
configure: WARNING: ## Report this to pgsql-
b...@postgresql.org <mailto:pgsql-bugs@postgresql.org> ##
configure: WARNING: ##
 ##


Which PostgreSQL version?


8.3.5


Could you attach your config.log file then?


Here it is.  I ran ./configure since I posted the error occurred because 
I was having problems with my system … it didn't reboot after the last 
update.  Hopefully, this will give you the info you're looking for.  Let 
me know if I can be of further assistance.


This is not the config.log file from the run that produced the warning 
you are complaining about.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Bug

[Peter Eisentraut]

Vincent Predoehl wrote:
This is not the config.log file from the run that produced the warning 
you are complaining about.


I did run configure several times since the error.  I know nothing of 
autoconf and didn't know to save the config.log file.  I just ran 
./configure again for 8.3.5 and did not get the error.  I have installed 
other things since the error that probably fixed it.  If you know what 
package contains krb5.h, I can try to reproduce it. Otherwise, it seems 
to be working fine now.


The warning is about krb5.h, so in order to reproduce it, you should 
probably at least put --with-krb5 into the configure line.


If we are to analyze the problem, then we must look at the config.log 
file from the configure run that shows the warning.


In general, I think you are the first to show this problem, so it is 
possible that it is a local misconfiguration rather than a general bug.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #3818: Cross compilation problems

[Peter Eisentraut]

On Tuesday 30 December 2008 17:49:16 Richard Evans wrote:
> I've taken a look at the current development snapshot ane made a new
> patch.  This is against the snapshot source dated 2008-12-30.

Half of this patch appears to attempt to fix not cross-compilation problems, 
but out-of-tree builds (vpath builds).  Considering that other developers 
regularly exercise this, it looks a bit suspicious.  Can you clarify your 
intent?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #3818: Cross compilation problems

[Peter Eisentraut]

Richard Evans wrote:
I've taken a look at the current development snapshot ane made a new 
patch.  This is against the snapshot source dated 2008-12-30.


This is now committed.

--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Status of issue 4593

[Peter Eisentraut]

On Tuesday 06 January 2009 02:03:14 Tom Lane wrote:
> I don't think there's a bug here, at least not in the sense that it
> isn't Operating As Designed.  But it does seem like we could do with
> some more/better documentation about exactly how FOR UPDATE works.
> The sequence of operations is evidently a bit more user-visible than
> I'd realized.

Well, if the effect of ORDER BY + FOR UPDATE is "it might in fact not be 
ordered", then it's pretty broken IMO.  It would be pretty silly by analogy 
for example, if the effect of GROUP BY + FOR UPDATE were "depending on 
concurrent events, it may or may not be fully grouped".

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4622: xpath only work in utf-8 server encoding

[Peter Eisentraut]

On Thursday 22 January 2009 15:39:00 Sergey Burladyan wrote:
> seb=# select xpath('/русский/text()', v::xml) from (select
> xml('<русский>язык')) as x(v);
> ERROR:  could not parse XML data
> DETAIL:  Entity: line 1: parser error : Input is not proper UTF-8, indicate
> encoding !
> Bytes: 0xF0 0xF3 0xF1 0xF1
> <русский>язык
> ^

This raises the question: What are the rules about encoding the characters in 
XPath expressions themselves?  I haven't found anything about that in the 
standard.  Anyone know?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] create database warning

[Peter Eisentraut]

Mykola Stryebkov wrote:

Hi,

# psql83 template1
Password:
Welcome to psql83 8.3.5, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

template1=# drop database inf;
DROP DATABASE
template1=# create database inf with encoding 'windows-1251';
WARNING:  could not determine encoding for locale "uk_UA.KOI8-U": 
codeset is "KOI8-U"

DETAIL:  Please report this to .
CREATE DATABASE
template1=#

What's wrong with it?


PostgreSQL does not support the KOI8-U encoding.  You could try to use 
uk_UA.utf8.



--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4671: Cluster Initialisation Fails on FreeBSD

[Peter Woodward]

The following bug has been logged online:

Bug reference:  4671
Logged by:  Peter Woodward
Email address:  pe...@petew.org.uk
PostgreSQL version: 8.3
Operating system:   FreeBSD 7.0
Description:Cluster Initialisation Fails on FreeBSD
Details: 

The following script shows that when I try to create the cluster using the
script initdb, then the script fails.

I'd initially installed direct from the ports directory. This failed (with
the same error). I then decided to build directly from the sources. The
build worked, but as before the cluster initialisation script fails.

I also include output from the pg_config commmand.

%uname -a
FreeBSD thinkpadr31.petew.org 7.0-RELEASE FreeBSD 7.0-RELEASE #0: Sun Feb 24
19:59:52 UTC 2008
r...@logan.cse.buffalo.edu:/usr/obj/usr/src/sys/GENERIC  i386
%whoami
postgres
%initdb -D /usr/local/pgsql/data
The files belonging to this database system will be owned by user
"postgres".
This user must also own the server process.

The database cluster will be initialized with locale C.
The default database encoding has accordingly been set to SQL_ASCII.
The default text search configuration will be set to "english".

fixing permissions on existing directory /usr/local/pgsql/data ... ok
creating subdirectories ... ok
selecting default max_connections ... 40
selecting default shared_buffers/max_fsm_pages ... 20MB/128000
creating configuration files ... ok
creating template1 database in /usr/local/pgsql/data/base/1 ... ok
initializing pg_authid ... ok
initializing dependencies ... ok
creating system views ... ok
loading system objects' descriptions ... ok
creating conversions ... FATAL:  could not access file
"$libdir/ascii_and_mic": No such file or directory
STATEMENT:  CREATE OR REPLACE FUNCTION ascii_to_mic (INTEGER, INTEGER,
CSTRING, INTERNAL, INTEGER) RETURNS VOID AS '$libdir/ascii_and_mic',
'ascii_to_mic' LANGUAGE C STRICT;

child process exited with exit code 1
initdb: removing contents of data directory "/usr/local/pgsql/data"


%pg_config
BINDIR = /usr/local/bin
DOCDIR = /usr/local/share/doc/postgresql
INCLUDEDIR = /usr/local/include
PKGINCLUDEDIR = /usr/local/include/postgresql
INCLUDEDIR-SERVER = /usr/local/include/postgresql/server
LIBDIR = /usr/local/lib
PKGLIBDIR = /usr/local/lib/postgresql
LOCALEDIR = /usr/local/share/locale
MANDIR = /usr/local/man
SHAREDIR = /usr/local/share/postgresql
SYSCONFDIR = /usr/local/etc/postgresql
PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--with-libraries=/usr/local/lib'
'--with-includes=/usr/local/include' '--enable-thread-safety'
'--with-docdir=/usr/local/share/doc/postgresql' '--with-openssl'
'--with-system-tzdata=/usr/share/zoneinfo' '--enable-nls'
'--prefix=/usr/local' '--mandir=/usr/local/man' '--infodir=/usr/local/info/'
'--build=i386-portbld-freebsd7.0' 'CC=cc' 'CFLAGS=-O2 -fno-strict-aliasing
-pipe ' 'LDFLAGS= -pthread -rpath=/usr/lib:/usr/local/lib'
'build_alias=i386-portbld-freebsd7.0'
CC = cc
CPPFLAGS = -I/usr/local/include
CFLAGS = -O2 -fno-strict-aliasing -pipe  -Wall -Wmissing-prototypes
-Wpointer-arith -Winline -Wdeclaration-after-statement -Wendif-labels
-fno-strict-aliasing -fwrapv
CFLAGS_SL = -fPIC -DPIC
LDFLAGS = -pthread -rpath=/usr/lib:/usr/local/lib -L/usr/local/lib
-Wl,-R'/usr/local/lib'
LDFLAGS_SL = 
LIBS = -lpgport -lintl -lssl -lcrypto -lz -lreadline -lcrypt -lm 
VERSION = PostgreSQL 8.3.6


Thanks,
Peter Woodward

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4689: Expanding the length of a VARCHAR column should not induce a table rewrite

[Peter Eisentraut]

Heikki Linnakangas wrote:

xuan--2009.03--submitbug--support--postgresql@baldauf.org wrote:

When executing
"ALTER TABLE sometable ALTER COLUMN somecolumn TYPE VARCHAR(7)", the 
whole
table is re-written, and this rewrite takes many hours. During these 
hours,

all writers on this table stall, making the database effectively
unavailable.

However, in almost all cases, there is no need for such relaxing of 
limits

to require a table rewrite.


While this isn't a bug, it's a reasonable feature request. I've added 
this to the TODO page: http://wiki.postgresql.org/wiki/Todo#ALTER


Patches are welcome.


The question is how you want to implement this in a data type 
independent fashion.  You can't assume that increasing the typmod is a 
noop for all data types.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4690: an select query is not using the index

[Peter Eisentraut]

vikas wrote:

The following bug has been logged online:

Bug reference:  4690
Logged by:  vikas
Email address:  vikas.du...@newgen.co.in
PostgreSQL version: PostgreSQL 7.3


Time to upgrade.


Operating system:   i686-pc-linux-gnu
Description:an select query is not using the index
Details: 


hi

there is a table PDBFolder whose one column is Parentfolderindex on which an
index 'idx_folder_parentfolderindex' is created.


More details please.


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4692: VACUUM: write to WAL gets very slow and seems redundant

[Peter Much]

The following bug has been logged online:

Bug reference:  4692
Logged by:  Peter Much
Email address:  p...@citylink.dinoex.sub.org
PostgreSQL version: 8.2.7
Operating system:   FreeBSD 6.3
Description:VACUUM: write to WAL gets very slow and seems redundant
Details: 

While most of the time the system does run very fine, sometimes (i perceived
it only during VACUUM) the throughput breaks down dramatically: while
normally the VACUUM fills multiple WAL logs per minute, it then takes 10
minutes to fill each WAL log, while the system is rather idle:

Normal: 
Mar  4 03:18:29  edge postgres[1781]: [11-1] :[] LOG:  archived
transaction log file "000200420097"
Mar  4 03:19:09  edge postgres[1781]: [12-1] :[] LOG:  archived
transaction log file "000200420098"
Mar  4 03:20:26  edge postgres[1781]: [13-1] :[] LOG:  archived
transaction log file "000200420099"
Mar  4 03:21:11  edge postgres[1781]: [14-1] :[] LOG:  archived
transaction log file "00020042009A"

Defect:
Mar  4 10:16:16  edge postgres[1781]: [84-1] :[] LOG:  archived
transaction log file "0002004200E0"
Mar  4 10:25:19  edge postgres[1781]: [85-1] :[] LOG:  archived
transaction log file "0002004200E1"
Mar  4 10:37:20  edge postgres[1781]: [86-1] :[] LOG:  archived
transaction log file "0002004200E2"
Mar  4 10:44:04  edge postgres[1781]: [87-1] :[] LOG:  archived
transaction log file "0002004200E3"

Looking closer at this. 
1.) The only process being active on the system is the VACUUM (FULL) task:
 6322  ??  Ds17:16.52 postgres: pgsql bacula 192.168.98.6(50283) VACUUM
(po

2.) the system load is near idle.

3.) "top" shows the vacuum process waiting in "bo_waa" (or "biowr") nearly
all the time:
  PID USERNAME  THR PRI NICE   SIZERES STATETIME   WCPU COMMAND
 6322 pgsql   1  -50 76596K 44324K bo_wwa  17:22  1.03% postgres
 
4.) The disk with the WAL logs is 98% busy, and the only file that is read
or written there is the current WAL log:
[r...@edge /var/pgsql/data]# find  . -type f  -exec ls -lu {} \;  | grep
"Mar  4 12:4"
[r...@edge /var/pgsql/data]# find  . -type f  -exec ls -l {} \;  | grep "Mar
 4 12:4"
-rw---  1 pgsql  pgsql  16777216 Mar  4 12:41
./pg_xlog/0002004200F0

5.) lsof shows the file-pointer in the WAL log proceeding:
[r...@edge /var/pgsql/data]# lsof -o pg_xlog/0002004200F0 ;
sleep 10; lsof -o pg_xlog/0002004200F0
COMMAND   PID  USER   FD   TYPE DEVICE OFFSET  NODE NAME
postgres 1780 pgsql9u  VREG  0,167  0t8585216 14344
pg_xlog/0002004200F0
postgres 6322 pgsql   11u  VREG  0,167 0t14090240 14344
pg_xlog/0002004200F0
COMMAND   PID  USER   FD   TYPE DEVICE OFFSET  NODE NAME
postgres 1780 pgsql9u  VREG  0,167  0t8585216 14344
pg_xlog/0002004200F0
postgres 6322 pgsql   11u  VREG  0,167 0t14524416 14344
pg_xlog/0002004200F0

So, during 10 seconds the 6322 process has proceeded 434176 bytes, giving 43
kBps.

6.) iostat shows the WAL disk processing ~60 accesses per second, 16kB each
(while the database disk does nearly nothing):
WAL
 KB/t tps  MB/s
16.70  69  1.12
16.76  63  1.03
16.00  45  0.70
16.00  58  0.90
16.71  68  1.10

This shows about 1000 kBps.
iostat does not show me which process does this activity, but since the
VACUUM process 6322 is the only active process, and since this process is in
"biowr" state all the time, the activity could hardly come from an other
process.

Evaluating, we have ~1000 kBps thruput but only 43 kBps write advance on the
WAL log, this gives about factor 1:20. And from the logs we see: about
30secs per WAL log in normal operation, and about 10mins in the defect
state, this is also 1:20.

So it seems I am looking at the right thing, and there is no problem on the
system. The only question is: what the  is this VACUUM process doing?

When killing the 6322 process, the next task behaves normal:
WAL   DB
 KB/t tps  MB/s   KB/t tps  MB/s
82.67   3  0.24  22.79 197  4.39
31.25   8  0.24  21.16 154  3.19
26.00   8  0.20  21.43 164  3.44
44.80   5  0.22  24.44 143  3.42
22.40   5  0.11  23.85 162  3.78
24.00   4  0.09  22.45 195  4.28


The behaviour occurs only occasionally; as we can see from the postgres log
above, at 3AM the VACUUM was running fine, only later it started with this
behaviour - for no apparent reason.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4697: to_tsvector hangs on input

[Peter Guarino]

The following bug has been logged online:

Bug reference:  4697
Logged by:  Peter Guarino
Email address:  peterguar...@earthlink.net
PostgreSQL version: 8.3.3
Operating system:   Suse 10.x
Description:to_tsvector hangs on input
Details: 

Certain strings involving the @ character cause the to_tsvector function to
hang and the postgres server process handling the connection to spin the cpu
at 100%. Attempts to gracefully kill the server process are unsuccessful and
a 'kill -9' becomes necessary. Here is an example of a such a string:
4...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4@d...@d4
@d...@d4@d...@d4@d...@d4@D2?C

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] shared_buffers/SHMMAX defaults?

[Peter Eisentraut]

On Monday 30 March 2009 17:34:36 Martin Pitt wrote:
> recently, I started to get quite a bunch of bug reports a la
> "PostgreSQL fails to start due to too little shared memory" [1]. I
> have never seen this before, neither in Debian, so I guess the
> SHMMAX defaults changed somewhat in Linux 2.6.27. It seems that with
> other components, such as X.org, using large amounts of shared memory
> as well, startup sometimes works and sometimes doesn't.

I think this calls for a distribution-specific policy.  E.g., why shouldn't X 
be forced to use a "more conservative setting"?  How do we even know how much 
memory X will use today or tomorrow, or whether X is installed or will be 
installed?


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4749: trigonometric functions broken

[Peter Eisentraut]

On Sunday 05 April 2009 13:44:37 Stefano Salvador wrote:
>select sin(pi());
>
> returns: 1.2246
>
> or:
>
>select cos(pi()/2);
>
> returns: 6.123
>
> but sin and cos are limited between -1 and 1 !!!

I get

=> select sin(pi());
 sin
--
 1.22460635382238e-16
(1 row)

=> select cos(pi()/2);
 cos
--
 6.12303176911189e-17
(1 row)

Apparently, your interface is truncating the output.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Friday 10 April 2009 08:39:33 Martin Pitt wrote:
> Tom Lane [2009-04-10  1:15 -0400]:
> > Martin Pitt  writesyuqhom#3:
> > > The test suite detected one regression in libpq, though: Setting
> > > $PGHOST now complains about a missing root.crt, although this is only
> > > relevant on the server side (or did I misunderstood this?)
> >
> > No, that's a progression: the client wants to validate the server's
> > cert, too.
>
> Indeed it is nice to see this feature (great to prevent spoofing), but
> if I don't have a ~/.postgresql/root.crt at all, it shouldn't
> certainly break completely? (which it does now).

I assume the server has the snakeoil certificate installed?  In that case, it 
is correct that the client refuses to proceed, although the exact manner of 
breaking could perhaps be improved.


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Friday 10 April 2009 17:13:55 Martin Pitt wrote:
> However, we can't afford to break existing installations. If a user
> has 8.4 installed locally, he'll use libpq from 8.4, and suddenly he
> could not connect to a remote SSL 8.3 cluster any more. So the check
> needs at least be turned into a warning for connecting to a pre-8.4
> server.

This is not a question of new client with old server.  The new version of the 
client has a more secure default that will possibly prevent it from connecting 
to *any* server that is not adequately configured.

But it's a default, so the user can change it.

Consider the analogy that a new web browser comes out that verifies server 
certificates (as of course all respectable browsers do nowadays) whereas the 
previous version one didn't.  The right fix there is certainly not to 
downgrade this to a warning when connecting to an older web server.

Not to mention the security implications: A rogue server could simply pretend 
to be of an older version to circumvent the client's security check.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Friday 10 April 2009 21:27:54 Stephen Frost wrote:
> I agree with this.  Avoiding spoofing is good, but so is on the wire
> encryption even if you don't have anti-spoofing.  This is a reasonable
> set-up and we shouldn't just fail on it.

This whole debate hinges on the argument that encryption without anti-spoofing 
is *not* useful.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Friday 10 April 2009 21:32:29 Stephen Frost wrote:
> A properly configured server could cause a failure too unless the client
> is *also* properly configured.  Sure, it's good for people to do.  No, I
> don't think we should break things if people don't build out a whole PKI
> for PG and configure all their certs correctly.  It's pie-in-the-sky to
> think everyone will do that, and in the end most will just say "SSL
> breaks stuff, so we'll disable it" which certainly isn't better.

That's debatable.  I think it's better.

> > But it's a default, so the user can change it.
>
> It should be the default to connect, maybe with a warning.

If you connect with a warning, you have possibly already given up sensitive 
information.  That's no good.

> > Consider the analogy that a new web browser comes out that verifies
> > server certificates (as of course all respectable browsers do nowadays)
> > whereas the previous version one didn't.  The right fix there is
> > certainly not to downgrade this to a warning when connecting to an older
> > web server.
>
> Uh, no, the right fix is to have a warning/prompt (as pretty much all
> web browsers today do) but then continue to connect.

Yes, this was under discussion a while ago but no one wanted to implement it.

> Also, the
> web-browser analogy completely falls apart when you consider that the
> use case is significantly different (how many times have you connected
> to a PG server that you didn't know?).

This is a fuzzy argument.  What do you mean by "know", and how do you verify 
what you "know" and whether what you "know" is correct?  And why are you using 
SSL at all if you think you "know" everything?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Friday 10 April 2009 22:44:44 Bruce Momjian wrote:
> The problem is that libpq doesn't have any ability to warn/prompt like
> SSH and web browsers do, so I think Magnus patterned the libpq behavior
> around cases where warning/prompt failed in these environments.
>
> I am not saying the current behavior is correct, only why it was
> configured that way.

Of course.  But if we really wanted to, this is nothing that can't be solved 
with callbacks or something like that.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Friday 10 April 2009 21:32:29 Stephen Frost wrote:
> Uh, no, the right fix is to have a warning/prompt (as pretty much all
> web browsers today do) but then continue to connect.

On that matter, it is interesting to observe where web browsers are heading 
today.

It used to be that web browsers said, "Cannot verify server certificate. 
Continue? [Yes] [No]" and everyone clicked yes.

The new firefox just says "invalid certificate" and nothing else, and then 
somewhere below there is a small link to "Add an exception" and you need a 
total of four clicks to proceed.  So that looks a lot like that they are 
moving away from easily allowing unverifyable server certificates as well.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Friday 10 April 2009 22:50:02 Tom Lane wrote:
> Peter Eisentraut  writes:
> > On Friday 10 April 2009 21:27:54 Stephen Frost wrote:
> >> I agree with this.  Avoiding spoofing is good, but so is on the wire
> >> encryption even if you don't have anti-spoofing.  This is a reasonable
> >> set-up and we shouldn't just fail on it.
> >
> > This whole debate hinges on the argument that encryption without
> > anti-spoofing is *not* useful.
>
> If we believe that then we need to also change the server to require
> a root.crt.

That would make sense if the server required SSL in the first place.  But the 
default configuration of the server is to take anything.  It would conceivably 
be proper to require a stronger client authentication mechanism than "trust" 
on hostssl lines.  (This doesn't have to be SSL-based client authentication.)

But we ship the server with a wide-open client access policy. Do you want to 
change that?  I think not.  But if packagers want to change that, by all means 
set up something stronger.

> I do not believe it --- there is a significant difference
> in the difficulty of passive listening and active spoofing.

Sure, there is a difference.  But what is it, and what percentage of users do 
you think are affected by it and can judge the difference?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Sunday 12 April 2009 01:58:26 Magnus Hagander wrote:
> "sslmode=prefer" honestly makes no sense - if I don't care if it ends up
> encrypted or not (which it means), then why not just run with SSL off
> and not have to deal with the overhead?

Perhaps a large part of the problem at hand is in fact that the default is 
sslmode=prefer, which, if the server is set up with some snakeoil certificate, 
causes all these cn verification problems, when the user really didn't care in 
the first place.

Another thing is that not all combinations of sslmode and sslverify make 
sense.  If the user cares little about SSL ("allow", "prefer"), then insisting 
on a verifyable certificate is pointless.

One random idea is to fold both of these settings into sslmode, with the 
following progression:

disable, allow, prefer, require, require-cert, require-cn

And then set the default to "disable", because as you say "prefer" is pretty 
silly.  And then users can explictly choose which level of SSL-ness they want.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Sunday 12 April 2009 12:52:53 Magnus Hagander wrote:
> This is a different way to do bruces suggestion of a different  
> default. That's possibly even clearer. So I can definitely go with  
> this, but I think two different parameters makes it more clear and is  
> better.

I think altogether changing the default ssmode to disable would solve all the 
problems with the least amount of work, complication, and controversy.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Tuesday 14 April 2009 17:05:45 Martin Pitt wrote:
> Of course I assumed that the server and client are on different
> systems. If they are on the same, then we just use the Unix socket and
> don't need all this SSL fuss at all.

That's what you think.  Just read the hackers thread about SSL over Unix-
domain sockets. ;-)

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] libpq 8.4 beta1: $PGHOST complains about missing root.crt

[Peter Eisentraut]

On Monday 20 April 2009 11:19:04 Magnus Hagander wrote:
> Bruce Momjian wrote:
> > Magnus Hagander wrote:
> >> On 14 apr 2009, at 04.33, Bruce Momjian  wrote:
> >>> Magnus Hagander wrote:
> > I would actually call the two parameters 'verify-cert' and 'verify-
> > cn',
> > and document that they also have "require" behavior.  Obviously you
> > can't verify certificates unless you require SSL.
> 
>  I would prefer having "verify", "verify-no-cn" and "no-verify" or
>  something like that. Making it the "default choice" to have
>  verification
>  enabled, and very clear that you're turning something off if you're
>  not.
>  And then just map require to verify. Or they could be "require-no-cn"
>  and "require-no-cert" perhaps?
> 
>  ("default choice" only for those using ssl of course - we'd still
>  have
>  "disable" as the default *value* of the parameter)
> >>>
> >>> I think the "no" options are odd because they have _negative_
> >>> designations.
> >>
> >> That's the intention. When you're turning off something, I think it
> >> makes sense to use "no"
> >
> > But that doesn't scale:  sslmode currently has four options, soon
> > perhaps to be six.   The idea is that the items should be of increasing
> > security, and adding "no" in the middle doesn't allow that to be clear.
>
> Here's a patch for this. Obviously, a lot needs to be done about the
> docs here, I'm working on that.
>
> I went with the names "require", "verify-ca" and "verify-full".
>
> Patch also changes the default from "prefer" to "disable", per discussion.

Looks good to me.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4767: create operator - negator doesn't work

[Peter Eisentraut]

On Tuesday 21 April 2009 14:04:01 fduerr wrote:
> CREATE OR REPLACE FUNCTION eq_int_bool(INTEGER, BOOLEAN) RETURNS BOOLEAN AS
>  'SELECT CAST($1 AS BOOLEAN)=$2;' LANGUAGE SQL IMMUTABLE;
> CREATE OPERATOR = (
>  LEFTARG=INTEGER,
>  RIGHTARG=BOOLEAN,
>  PROCEDURE=eq_int_bool,
>  COMMUTATOR= = ,
>  NEGATOR= <>
> );
>
> And thats, what i got:
>
> SELECT 1=false;
>  ?column?
> --
>  f
> (1 row)
>
> SELECT 1<>false;
> 8.3.x: ERROR: cache lookup failed for function 0
> 8.4b1: ERROR: operator is only a shell: integer <> boolean
>
> The workaround is, of course, to define an operator with '<>' as commutator
> that returns the negated result. Still i wonder: is it me, the
> documentation or pg?

What you did above is to specify which operator is the negator of =.  But you 
still need to create that operator; it is not created automatically.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] WARNING: silent data corruption possible from PL/ruby 0.5.0 (after Ruby upgrade)

[Peter Much]

Dear all,

since upgrading Ruby from 1.8.6.287 to 1.8.7.72, and recompiling 
PL/ruby 0.5.0, functions written in PL/ruby may ignore their 
given parameters and instead compute with undefined values,
providing nonsense results.

The test-suite provided with PL/ruby will show the problem.

Upgrading to PL/ruby 0.5.3 does not solve the problem, but that
version seems to detect the problem and raise an error.

I have figured out how the problem appears, but do not understand
why it appears or where exactly the cause is.
The problem seems related to the way PL/ruby uses a Ruby function
named rb_block_call. That function will only be used when detected
during compilation of PL/ruby. While this was not the case with 
Ruby 1.8.6, it is with Ruby 1.8.7.
So, a workaround to get the applications operative is simply to 
suppress the detection of that function.

It is unclear if the actual problem is in Ruby or in PL/ruby. It it
also unclear what this function rb_block_call is good for or why
it did appear with Ruby 1.8.7.

The author of PL/ruby has been contacted, but did not answer.

Further details can be found in that bug report:
http://www.freebsd.org/cgi/query-pr.cgi?pr=ports/134300


rgds,
PMc

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4801: Performance failure: 600 MB written to each WAL log

[Peter Much]

The following bug has been logged online:

Bug reference:  4801
Logged by:  Peter Much
Email address:  p...@citylink.dinoex.sub.org
PostgreSQL version: 8.2.13
Operating system:   FreeBSD 7.2
Description:Performance failure: 600 MB written to each WAL log
Details: 

Server becomes very slow while tremendous amounts of data are written
to WAL logs - disk activity log shows 600 MB of effective write requests
before each 16 MB WAL log is archived and recycled.

Problem appears occasionally all of a sudden with no apparent reason.
I perveived it with 8.2.5(?), 8.2.11 and now 8.2.13, with FreeBSD Rel. 6.3
and 7.2.

I perceived it only during "vaccum full" yet, but then a long-running
"vacuum full" on a large table is the only significant activity on the
server. (I know that "vacuum full" may not be really necessary, but
I think it should practically function.)

When starting the "vacuum full", the activity looks what I think normal:
there is heavy activity on the database table files, accompanied by
a certain amount of accesses to the WAL logs. The amount of disk writes
to WAL then figures (more or less) to the WAL switching frequency.
And it may stay so and the vacuum may complete successful.

But sometimes (same "vacuum full", same table) the server suddenly
changes into a very slow state of proceeding: then there is only few
activity on the database table disk, while the WAL disk is busy writing
at 100%. But the WAL logs are filled very slow, too.
I can watch the proceeding of the vacuum by listing the open file
handle offsets on the database table files (with lsof). And the ratio
between these offsets proceeding and the number of archived WAL logs
is the same in both states.

Also, the server functions correctly by all means when in this slow
state, and all requests are processed. And there are no error messages.
The only difference is that in normal state of operation the amount of
data written to one WAL log is just some more than the 16 MB it contains,
while in the slow state it is about 600 MB - and this slows down
operation. (The vacuum job must be killed then, because it would take
days instead of hours to complete.)

I have not really figured out the conditions that do bring the server
into this slow mode of operation. It seems to happen when a certain
amount of other activity (on other databases within the same server)
occurs. That is, as long as there is not much other activity on the
system, the vacuum will usually complete with normal speed.

But after the server has fallen into the slow state, it will not get
out of it again. It has to be stopped and restarted to get back to
normal speed; killing and restarting the vacuum process will not
help.

The WAL handling configuration in the config file is as default, only
I do WAL archiving. I have tried to increae wal_buffers, this had no
effect.
I have moved the whole installation from FreeBSD-UFS diskspace to ZFS,
and have switched off full_page_writes. This does significantly reduce
the absolute amount of produced WALs, and therefore the slow state of
operation becomes faster than before, but besides this the behaviour
did not change.

The command "show wal_sync_method" yields "fsync".

Over all, I do not think that the problem relates to WAL storage. It
seems to be mainly the question: what is this tremendous amount of
-obviousely redundant- data that is written to WAL, and how do I get
the server out of this mode again without disrupting operation?

One other thing should be remarked here: The server is a very small
system. It has not much memory and will do a real lot of paging; the
disks are also not fast.
I occasionally experience that under such conditions software shows
bugs that would never become visible on the usual oversized systems.

Configuration of the server is as follows (as changed from defaults):
> max_connections = 40
> shared_buffers = 28MB
> max_fsm_pages = 179200
> effective_cache_size = 12MB
> autovacuum = off
(autovacuum is off to reduce noise - disks will spindown when
databases are not used.)

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4801: Performance failure: 600 MB written to each WAL log

[Peter Much]

Dear Rafael,

I agree with You. And that is actually what I have done up to now,
although for a different reason: the amount of WAL logs produced
by VACUUM FULL is practically unpredictable and can be much more than
the database size, and since that database belongs to the backup system,
the backup system cannot archive them during VACUUM FULL.
I have solved this now with ZFS, abandoning the need for 
full_page_writes.

On Fri, May 08, 2009 at 02:59:04PM +0200, Rafael Martinez wrote:
! How large is that 'large table' you are talking about?

20 Mio. tuples. Which is quite a lot for a pentium-II mainly 
used as a router with some attached tapedrives.
OTOH, the machine has not much else to do, so it can well run
VACUUM FULL once in a while.

! vacuum full on a large database has so long execution time and uses so
! many resources (IO/ram/time) that it is almost useless. If you have such
! a problem with your database, that you need the functionality delivered
! by vacuum full, it is better to export and import your database.

Yes. That respective postgres installation used to contain (and
still does) a couple of databases for my needs, some of them
experimental, none of them big. I considered it very practical 
to run VACUUM FULL (and Index rebuild) thru all instances from 
cron every week or month, so the databases would reshape by 
themselves if some mess was left behind.

Then I aquired the backup software, it needed a database, so I
placed it into the existing postgres installation - and after
noticing that it tends to get some hundred times bigger than 
the others together, I gave it some extra diskspace. And I 
thought, postgresQL is extremely scaleable, it can do it.

So, I should exclude that backup-system DB from the VACUUM FULL
cronjob. Okay, well, if it works properly, it runs some hours and
does not hurt anybody - and, as it is done regularly at a fixed
time, I can look into my recorded "df" outputs later, and they 
will show me the exact amount the DB is growing over time.

! This does not explain your problem, but maybe if you can avoid running
! vacuum full unnecessarily, you will also avoid this problem you are
! experiencing.

Yes. But this is a really strange behaviour, and it does not at
all match with the usual postgresQL style, which is very precise.
Or, in other words: it creeps like a bug, it smells like a bug,
it looks like a bug - maybe it is a bug. And I am not fond of letting
identified bugs creep under the carpet - they tend to come out at
unpleasant moments.
Or, said again differntly: that server does something which seems
not to make sense at all. Lets hunt it down, at least figure out
why it does this. 

It might be possible to approach this by logical analysis, without 
debugging gear, by asking: >under which circumstances are WAL logs
written redundantly?< 
Or, if there is someone who would like to analyze the matter, I could
try to get the FreeBSD GEOM or something there to do a tracelog 
of the stuff that is actually written to WAL.
Maybe then we find something that is not limited to VACUUM FULL, or 
maybe we find something that does NOT do greater havoc only by good 
luck. Or maybe it is just the way it should work...

best regards,
Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4801: Performance failure: 600 MB written to each WAL log

[Peter Much]

Dear Simon,

On Fri, May 08, 2009 at 07:15:19PM +0100, Simon Riggs wrote:
! > Server becomes very slow while tremendous amounts of data are written
! > to WAL logs - disk activity log shows 600 MB of effective write
! > requests before each 16 MB WAL log is archived and recycled.
! 
! VACUUM FULL does many things, most of them slowly. It re-writes normal
! database blocks setting hint bits, which are not WAL logged. This may
! explain the figures you have.

You did not get me fully right. The 600 MB are written ONTO EACH
16 MB WAL LOGFILE before it gets switched. And that is a lot more
than superfluous flushes should account for. 

And since this does not happen always, only after the server falls
into this strange mode of operation it will stay there - and after
restarting,  the vacuum process will practically do the same things
again and may work correctly then - I consider this a remarkable 
effect, because such things usually tend to point to some race 
condition somewhere.

! 8.2 is also a fairly poor performer with VACUUM and will cause many WAL
! unnecessary flushes per WAL file. 

Yes, I see that too. 

! Not sure why you are using an old
! release of PostgreSQL on a new BSD version,

Hm. Because it works, and it is stable and reliable? I am using this 
database system more or less since it was called Ingres, and I think 
it has already improved a lot. ;)

The only reason for upgrading the BSD was to get ZFS, and get rid of the
full_block_writes. Up to now I was practically drowning in WAL logs;
now they have reduced by factor 8, and I am very happy with that.
(And for a backup system one MUST archive the WAL logs, too. It
doesnt make much sense to do offline backups, and not have current offline
backups of the database needed to find something on these backups...)

I am not very fond of major upgrades. Due to the heap of 
various functionalities that I have accumulated over time, each
major upgrade will bring some problems, incompatibilities and
features, and then the first thing to recognize is always: something
that used to work does no longer or different. And then I have to
dig into that respective construct and figure out how it needs to be
adjusted. For instance, the interface between postgresQL and Ruby-
on-Rails Rel. 1.2 is still for postgres-7 - it works with 8.2, but
there is an issue with escapes in strings ("standard_conforming_strings");
so likely I have to go for RoR Rel. 2.x - which means work thru
my RoR apps and fixup things that are deprecated. Etc. etc., You get 
the idea.

So, usually I try to upgrade at least two major levels at once.

! but if you upgrade
! PostgreSQL and use VACUUM instead you will see improvement.

I do daily VACUUM, anyway. But out of good habit I also do a monthly
VACUUM FULL. I like self-cleaning systems (You should see the mess in
my rooms ;))


So, well, on the bottomline I read from Your message: You guys have
redesigned the VACUUM process in 8.3, and therefore nobody is really
eager to dig into the old stuff and search for strange problems
there. That's an argument, I can understand.

best regards,
Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Sorting dates

[Peter Eisentraut]

On Friday 15 May 2009 01:07:11 Hershel Fisch wrote:
> Hi, I realized that sorting date is done like text and not numeric (dates)
>  e.g. SELELCT * FROM database_name ORDER BY date ASC
>
> Return order
>
> 3/02/09
> 4/19/09
> 4/2/09
>
> Thanks,

This report would make a lot more sense if you posted your schema definition.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] DDL triggers

[Peter Eisentraut]

On Thursday 21 May 2009 10:52:33 Целуйко Дмитрий wrote:
> When DDL triggers will be supported by PostgreSQL?

There are currently no concrete plans for that, but if someone comes up with a 
good design and implementation, it could be acceptable.  But don't hold your 
breath.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4824: KRB5/GSSAPI authentication fails when user != principal

[Peter Koczan]

The following bug has been logged online:

Bug reference:  4824
Logged by:  Peter Koczan
Email address:  pjkoc...@gmail.com
PostgreSQL version: 8.4beta2
Operating system:   Red Hat Enterprise Linux 5.3
Description:KRB5/GSSAPI authentication fails when user != principal
Details: 

When authenticating with Kerberos/GSSAPI, if the Kerberos principal is not
the same as the shell user, authentication fails. For instance, as root (for
local fs access) with other tickets (for database and network fs access).

Note: runauth is our homegrown utility to get stashed kerberos tickets.

[r...@mitchell ~]# /s/std/bin/runauth -a -l postgres
/s/postgresql-8.4-beta/bin/psql -h mitchell -p 49173 postgres
psql: FATAL:  role "root" does not exist

It appears to assume that the shell user is the user to connect as. However,
using an 8.3 client works as previously expected.

[r...@mitchell ~]# /s/std/bin/runauth -a -l postgres
/s/postgresql-8.3/bin/psql -h mitchell -p 49173 postgres
Welcome to psql 8.3.6 (server 8.4beta2), the PostgreSQL interactive
terminal.
...
postgres=# select current_role;
 current_user 
--
 postgres
(1 row)

This is a difference on the client side.

Even if this is newly expected behavior, it is a change and I could not find
any reference to it in the release notes.

Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4824: KRB5/GSSAPI authentication fails when user != principal

[Peter Koczan]

> We should probably at least clarify this release note.  Do you want
> to make an argument that this is a fundamental breakage and we need
> to revert it?  If so, what's the argument?

Certainly.

It seems like with the changes in 8.4, krb5/gssapi auth looks for a
valid ticket, and if it finds one, connects without regard for the
principal in that ticket. This is a gaping security hole, because it
is very nearly the same as trust authentication.

I'm me...

[koc...@mitchell] ~ $ klist
...
Default principal: koc...@cs.wisc.edu
...

I connect as me...

[koc...@mitchell] ~ $ /s/postgresql-8.4-beta/bin/psql -h mitchell -p
49173 postgres
psql (8.4beta2)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=> select current_role;
 current_user
--
 koczan
(1 row)

Now, I connect as someone else...

[koc...@mitchell] ~ $ /s/postgresql-8.4-beta/bin/psql -h mitchell -p
49173 -U strivia postgres
psql (8.4beta2)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=> select current_role;
 current_user
--
 strivia
(1 row)

Now, I connect as superuser...

[koc...@mitchell] ~ $ /s/postgresql-8.4-beta/bin/psql -h mitchell -p
49173 -U postgres postgres
psql (8.4beta2)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=# select current_role;
 current_user
--
 postgres
(1 row)

Old clients also exhibit this behavior, so it's also a server-side issue.

[koc...@mitchell] ~ $ /s/postgresql-8.3.6/bin/psql -h mitchell -p
49173 -U postgres postgres
Welcome to psql 8.3.6 (server 8.4beta2), the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

WARNING:  You are connected to a server with major version 8.4,
but your psql client is major version 8.3.  Some backslash commands,
such as \d, might not work properly.

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

postgres=# select current_role;
 current_user
--
 postgres
(1 row)

And just to show you that there is no trickery, I will attempt to
connect without Kerberos tickets.

bash-3.2$ whoami
koczan
bash-3.2$ klist
klist: No credentials cache found (ticket cache
FILE:/var/adm/krb5/tmp/tkt/krb5cc_0_N26236)
...
bash-3.2$ /s/postgresql-8.4-beta/bin/psql -h mitchell -p 49173
postgrespsql: GSSAPI continuation error: Unspecified GSS failure.
Minor code may provide more information
GSSAPI continuation error: Unknown code krb5 195

bash-3.2$ /s/postgresql-8.4-beta/bin/psql -h mitchell -p 49173 -U
postgres postgres
psql: GSSAPI continuation error: Unspecified GSS failure.  Minor code
may provide more information
GSSAPI continuation error: Unknown code krb5 195

This is trust authentication with one rather inconsequential bit of
verification, that's a fundamental breakage. One of the major points
of Kerberos is that, for anything that talks Kerberos, you are the
principal in that ticket. I understand the desire to change some of
that old code, but why is that principal being ignored?

Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4824: KRB5/GSSAPI authentication fails when user != principal

[Peter Koczan]

I don't know if it's much use now, but here you go.

On Wed, May 27, 2009 at 3:15 PM, Magnus Hagander  wrote:
> We are certainly *supposed* to do that. And we have been doing that. So
> if that's not done, it's been broken in 8.4 (most likely by me).
>
> Peter, are you using gssapi or krb5? Only krb5 has changed wrt libpq,
> but from your messages it looks like you have gssapi?

gssapi

> Can you show us your pg_hba.conf file, and all lines with krb in them
> from postgresql.conf?

pg_hba.conf

# this part disables remote "postgres" superuser connections
hostssl all postgres127.0.0.1/32   gss
hostssl all postgres128.105.207.19/32  gss
hostssl all postgres128.105.0.0/16 reject
hostssl all postgres198.133.224.0/24   reject

# this part enables non-superuser connections
hostssl all nobody  128.105.0.0/16 trust
hostssl all nobody  198.133.224.0/24   trust
hostssl all all 128.105.0.0/16 gss
hostssl all all 198.133.224.0/24   gss


postgresql.conf
# Kerberos and GSSAPI
krb_server_keyfile = '/etc/v5srvtab.postgres'
#krb_srvname = 'postgres'   # (Kerberos only)
#krb_caseins_users = off

> Also, can you try it with the server set to log at DEBUG4, and let us
> know what output you get?

Connecting like this...
[koc...@ator] koczan $ /s/postgresql-8.4-beta/bin/psql -h mitchell -p
49173 -U strivia postgres

Produced this in the syslog.
May 27 15:37:29 mitchell postgres[30574]: [624-1] DEBUG:  forked new
backend, pid=30609 socket=8
May 27 15:37:29 mitchell postgres[30609]: [624-1] LOG:  connection
received: host=ator.cs.wisc.edu port=44228
May 27 15:37:29 mitchell postgres[30609]: [625-1] DEBUG:  SSL
connection from "(anonymous)"
May 27 15:37:29 mitchell postgres[30609]: [626-1] DEBUG:  Processing
received GSS token of length 477
May 27 15:37:29 mitchell postgres[30609]: [627-1] DEBUG:  sending GSS
response token of length 114
May 27 15:37:29 mitchell postgres[30609]: [628-1] DEBUG:  sending GSS
token of length 114
May 27 15:37:29 mitchell postgres[30609]: [629-1] LOG:  provided
username (koczan) and authenticated username (strivia) don't match
May 27 15:37:29 mitchell postgres[30609]: [630-1] LOG:  connection
authorized: user=strivia database=postgres
May 27 15:37:29 mitchell postgres[30609]: [631-1] DEBUG:  postgres
child[30609]: starting with (
May 27 15:37:29 mitchell postgres[30609]: [632-1] DEBUG:postgres
May 27 15:37:29 mitchell postgres[30609]: [633-1] DEBUG:-v196608
May 27 15:37:29 mitchell postgres[30609]: [634-1] DEBUG:-y
May 27 15:37:29 mitchell postgres[30609]: [635-1] DEBUG:postgres
May 27 15:37:29 mitchell postgres[30609]: [636-1] DEBUG:  )
May 27 15:37:29 mitchell postgres[30609]: [637-1] DEBUG:  InitPostgres
May 27 15:37:29 mitchell postgres[30609]: [638-1] DEBUG:  my backend id is 1
May 27 15:37:29 mitchell postgres[30609]: [639-1] DEBUG:  StartTransaction
May 27 15:37:29 mitchell postgres[30609]: [640-1] DEBUG:  name:
unnamed; blockState:   DEFAULT; state: INPROGR, xid/subid/cid:
0/1/0, nestlvl: 1, children:
May 27 15:37:29 mitchell postgres[30609]: [641-1] DEBUG:  CommitTransaction
May 27 15:37:29 mitchell postgres[30609]: [642-1] DEBUG:  name:
unnamed; blockState:   STARTED; state: INPROGR, xid/subid/cid:
0/1/0, nestlvl: 1, children:

May 27 15:37:55 mitchell postgres[30574]: [625-1] DEBUG:  reaping dead processes
May 27 15:37:55 mitchell postgres[30574]: [626-1] DEBUG:  server
process (PID 30612) exited with exit code 0
May 27 15:38:24 mitchell postgres[30609]: [643-1] DEBUG:  shmem_exit(0)
May 27 15:38:24 mitchell postgres[30609]: [644-1] DEBUG:  proc_exit(0)
May 27 15:38:24 mitchell postgres[30609]: [645-1] LOG:  disconnection:
session time: 0:00:54.389 user=strivia database=postgres
host=ator.cs.wisc.edu port=44228
May 27 15:38:24 mitchell postgres[30609]: [646-1] DEBUG:  SSL: write
alert (0x0100)
May 27 15:38:24 mitchell postgres[30609]: [647-1] DEBUG:  exit(0)
May 27 15:38:24 mitchell postgres[30609]: [648-1] DEBUG:  shmem_exit(-1)
May 27 15:38:24 mitchell postgres[30609]: [649-1] DEBUG:  proc_exit(-1)

Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4824: KRB5/GSSAPI authentication fails when user != principal

[Peter Koczan]

On Wed, May 27, 2009 at 5:16 PM, Tom Lane  wrote:
> What this still leaves us with is whether that change is a bad idea or
> not.  I still think it's OK, but maybe Peter can point to something
> else.

I recompiled postgres with the auth.c patch.

This is only an issue if you are trying to connect as a principal
that's not the same as your current username. Where I work, we have
security and mode bits set up on Kerberos tickets and keytabs such
that you either have to be the user in question or root to get those
credentials. And we only have a couple of things that run as root with
others' principals.

There is a workaround to get the "expected behavior", just specify the
proper username in the connection parameters. This should be
explicitly said in the release notes.

/s/std/bin/runauth -a -l postgres /s/postgresql-8.4-beta/bin/psql -h
mitchell -p 49173 postgres
psql: FATAL:  GSSAPI authentication failed for user "root"
FATAL:  no pg_hba.conf entry for host "128.105.207.19", user "root",
database "postgres", SSL off

[r...@mitchell ~]# /s/std/bin/runauth -a -l postgres
/s/postgresql-8.4-beta/bin/psql -h mitchell -p 49173 -U postgres
postgres
psql (8.4beta2)
SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
Type "help" for help.

postgres=#

I should also point out that old clients still exhibit the old behavior.

[r...@mitchell ~]# /s/std/bin/runauth -a -l postgres
/s/postgresql-8.3.6/bin/psql -h mitchell -p 49173 postgres
Welcome to psql 8.3.6 (server 8.4beta2), the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
   \h for help with SQL commands
   \? for help with psql commands
   \g or terminate with semicolon to execute query
   \q to quit

WARNING:  You are connected to a server with major version 8.4,
but your psql client is major version 8.3.  Some backslash commands,
such as \d, might not work properly.

SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)

postgres=#

It was rather convenient to know that whatever Kerberos principal was
used was going to be the database user. The old behavior is more in
line with other Kerberos'd systems like AFS or NFSv4 that take their
authentication from the Kerberos principal (through an AFS token or
LDAP), sometimes regardless of the shell user. Of course, file systems
are different animals than DBMS's since they need to "always be on"
instead of requiring explicit connections every time you authenticate.
I can't think of any other major services that explicitly use
Kerberos, so I'm not sure how many other models exist.

From a practical standpoint, I can accept this change since there is a
simple workaround and since Kerberos is properly verifying the user.
Though it would be nice to make the assumption that the principal is
the user for Kerberos/GSSAPI connections, I don't think it's worth it
to differently handle that rare case where it would actually make a
difference.

Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4824: KRB5/GSSAPI authentication fails when user != principal

[Peter Koczan]

On Thu, May 28, 2009 at 1:30 PM, Tom Lane  wrote:
> Peter Koczan  writes:
>> It was rather convenient to know that whatever Kerberos principal was
>> used was going to be the database user.
>
> Isn't that still true?  (Modulo the auth.c bug fix of course.)  The only
> issue here is where the default guess for a not-explicitly-specified
> username comes from, not whether you'll be allowed to connect or not.

That's what I meant. It was convenient to have the default guess be
the Kerberos principal for krb5/gss connections. This is still the
case in the vast majority of connections, so it's probably not worth
bending over backwards to satisfy these edge cases.

Sorry for the confusion.

Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4824: KRB5/GSSAPI authentication fails when user != principal

[Peter Koczan]

On Thu, May 28, 2009 at 2:07 PM, Peter Koczan  wrote:
> On Thu, May 28, 2009 at 1:30 PM, Tom Lane  wrote:
>> Peter Koczan  writes:
>>> It was rather convenient to know that whatever Kerberos principal was
>>> used was going to be the database user.
>>
>> Isn't that still true?  (Modulo the auth.c bug fix of course.)  The only
>> issue here is where the default guess for a not-explicitly-specified
>> username comes from, not whether you'll be allowed to connect or not.
>
> That's what I meant. It was convenient to have the default guess be
> the Kerberos principal for krb5/gss connections. This is still the
> case in the vast majority of connections, so it's probably not worth
> bending over backwards to satisfy these edge cases.

And by "this is still the case", I mean that the principal name and
the username line up and exhibit the same overt behavior. Not that the
principal forces the username.

I need a break. :-)

Peter

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [HACKERS] [BUGS] BUG #4822: xmlattributes encodes '&' twice

[Peter Eisentraut]

On Thursday 28 May 2009 13:31:16 Itagaki Takahiro wrote:
> Here is a patch to fix the bug. I added a parameter 'encode' to
> map_sql_value_to_xml_value() and pass false for xml attributes.

I have committed your patch with minor editing.  Thanks.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [HACKERS] [BUGS] BUG #4822: xmlattributes encodes '&' twice

[Peter Eisentraut]

On Sunday 31 May 2009 20:00:44 Tom Lane wrote:
> Itagaki Takahiro  writes:
> > Here is a patch to fix the bug. I added a parameter 'encode' to
> > map_sql_value_to_xml_value() and pass false for xml attributes.
>
> One thing I was wondering about, which is sort of highlighted by your
> patch, is why is there the special exception for XML type in the
> existing code, and how does that interact with this behavior?

This is so that

xmlelement(name element, xml '')

results in



and

xmlelement(name claim, text '1 < 2')

results in

1 < 2

> Seems like there could be cases where we're getting one too many or too
> few encoding passes when the input is XML.

The patch doesn't actually change anything when the input datum is of type 
XML.  But anyway I have added a few regression test bits to make the 
expectations more explicit.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] to_timestamp error handling.

[Peter Eisentraut]

On Wednesday 10 June 2009 18:02:45 Dhaval Jaiswal wrote:
> Yes,   there isn't a use case for a month value outside 1-12, i found this
> due a typo.

What Would Oracle Do?


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4876: author of MD5 says it's seriously broken - hash collision resistance problems

[Peter Eisentraut]

On Wednesday 24 June 2009 04:59:05 Jim Michaels wrote:
> If you are looking for hash collision protection, start looking at SHA-256
> or SHA-512.

Well, are we looking for that?  We are not using MD5 for digital signatures.


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4883: tar xf fails on NFS4 mounts

[Peter Eisentraut]

On Friday 26 June 2009 00:04:02 Alfred Monticello wrote:
> in doc/Makefile, tar is run to extract an archive with xf options. Needs
> oxf to map to owner of person running tar, otherwise Invalid Argument
> occurs and the Makefile errors out.
>
> A better solution might be to compact postgres as UID/GID 0 instead of UID
> 258 which does not exist on my system.
>
> Problem only occurs when mounting NFSv4 from a Solaris box using ZFS.
> Client is Ubuntu Jaunty. Tar tries to preserve UID 258 but errors out
> because it doesn't exist.

Are you running the extraction as root user?

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4889: Accent Sensitive

[Peter Eisentraut]

On Saturday 27 June 2009 15:06:30 Ricardo wrote:
> Need support for acent sensitive in UTF-8, in clause like SQL.

This is not a bug.  But you may find this helpful:

http://wiki.postgresql.org/wiki/Strip_accents_from_strings

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4894: [patch] documentation bug on 'include' directive

[Peter Schuller]

The following bug has been logged online:

Bug reference:  4894
Logged by:  Peter Schuller
Email address:  peter.schul...@infidyne.com
PostgreSQL version: CVS
Operating system:   N/A
Description:[patch] documentation bug on 'include' directive
Details: 

The documentation says to use:

  include 'filename'

Which fails with a generic error. Correct syntax is:

  include = 'filename'

Suggested fix:

Since the form is likely to munch whitespace, the authoritative patch is
here:

   http://distfiles.scode.org/mlref/pg-include-doc.patch

For convenience here's a cut'n'paste (which is likely broken):

Index: doc/src/sgml/config.sgml
===
RCS file: /projects/cvsroot/pgsql/doc/src/sgml/config.sgml,v
retrieving revision 1.220
diff -u -r1.220 config.sgml
--- doc/src/sgml/config.sgml17 Jun 2009 21:58:48 -  1.220
+++ doc/src/sgml/config.sgml29 Jun 2009 15:44:57 -
@@ -82,11 +82,12 @@
 another file to read and process as if it were inserted into the
 configuration file at this point.  Include directives simply look
like:
 
-include 'filename'
+include = 'filename'
 
 If the file name is not an absolute path, it is taken as relative to
 the directory containing the referencing configuration file.
-Inclusions can be nested.
+Inclusions can be nested. Contrary to what is implied by the syntax,
+it is not an assignment and multiple include directives can be used.

 


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4894: [patch] documentation bug on 'include' directive

[Peter Schuller]

> > The documentation says to use:
> >   include 'filename'
> > Which fails with a generic error.
> 
> What generic error?

Error: Invalid line 496 in /etc/postgresql/8.3/main/postgresql.conf: »include 
'filename' « failed!

> > Correct syntax is:
> >   include = 'filename'
> 
> The stated syntax should work just fine.

Ok. This is with a Debian PostgreSQL 8.3. I did have a look through
the debian diffs before submittig the report and didn't find anything
immediately obvious that would break the include directive. That said
perhaps it has been changed in CVS; I didn't test that, but only had a
quick look at the code.

Since I thought the lack of = was indeed incorrect syntax I claimed
CVS in the bug report since the documentation has that syntax in CVS,
but I didn't test the CVS version of the backend. I'll see about doing
that but it's going to have to wait a bit...

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller '
Key retrieval: Send an E-Mail to getpgp...@scode.org
E-Mail: peter.schul...@infidyne.com Web: http://www.scode.org



pgpxOoLu69YvC.pgp
Description: PGP signature

Re: [BUGS] BUG #4894: [patch] documentation bug on 'include' directive

[Peter Schuller]

> >> What generic error?
> 
> > Error: Invalid line 496 in /etc/postgresql/8.3/main/postgresql.conf: 
> > »include 'filename' « failed!
> 
> > This is with a Debian PostgreSQL 8.3.
> 
> There is no such error message in Postgres 8.3 (or any other version).
> I speculate that you are dealing with some bit of Debian-provided script
> that tries to parse the config file but doesn't know enough to do it
> correctly.

You are entirely correct, it was the Debian init scripts trying to
parse the config file. My apologies for wasting your time. I should
have confirmed where the error message came from.

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller '
Key retrieval: Send an E-Mail to getpgp...@scode.org
E-Mail: peter.schul...@infidyne.com Web: http://www.scode.org



pgpPkr3qEEV4q.pgp
Description: PGP signature

Re: [BUGS] BUG #4883: tar xf fails on NFS4 mounts

[Peter Eisentraut]

On Thursday 02 July 2009 02:45:24 John R Pierce wrote:
> ajmcello wrote:
> > The only downside with adding o to tar that I can see is if it isn't
> > supported by a non-GNU version of tar.
>
> On solaris 9 and 10 at least, tar -o means set ownership of extracted
> files to the runner and not the uid in the tar, which I assume is what
> we want here.
>
> Note, this is on Solaris /usr/bin/tar
>
> I'd suggest that if this works on...
>
> A) solaris 8+
> B) hp-ux
> C) AIX 5.x+
> D) Free/Net/OpenBSD
>
> it would be safe for 'unix' systems, as there really aren't many other
> flavors of 'unix' in common use anymore.

But it doesn't, hence this thread.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4883: tar xf fails on NFS4 mounts

[Peter Eisentraut]

On Thursday 02 July 2009 09:56:35 Peter Eisentraut wrote:
> On Thursday 02 July 2009 02:45:24 John R Pierce wrote:
> > ajmcello wrote:
> > > The only downside with adding o to tar that I can see is if it isn't
> > > supported by a non-GNU version of tar.
> >
> > On solaris 9 and 10 at least, tar -o means set ownership of extracted
> > files to the runner and not the uid in the tar, which I assume is what
> > we want here.
> >
> > Note, this is on Solaris /usr/bin/tar
> >
> > I'd suggest that if this works on...
> >
> > A) solaris 8+
> > B) hp-ux
> > C) AIX 5.x+
> > D) Free/Net/OpenBSD
> >
> > it would be safe for 'unix' systems, as there really aren't many other
> > flavors of 'unix' in common use anymore.
>
> But it doesn't, hence this thread.

I meant rather, hence the other thread, where we had to revert this.


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4899: Open parenthesis breaks query plan

[Peter Headland]

The following bug has been logged online:

Bug reference:  4899
Logged by:  Peter Headland
Email address:  pheadl...@actuate.com
PostgreSQL version: 8.4.0
Operating system:   Windows
Description:Open parenthesis breaks query plan
Details: 

In a moderate-size table (~400,000 rows), an equality match on an unindexed
varchar column to a string that contains an open parenthesis '(' prevents
the optimizer from using an obvious index. Changing the open parenthesis to
another character, such as ')' allows the obvious index to be used. I have
been unable to reproduce this on simple test data so far, so it is obviously
fairly subtle.

Abstract example of the issue:

o table t has a composite index i comprising columns c1, c2, c3

o column t.c4 is not indexed

Illustration of the queries:

-- Full table scan
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = '(';

-- Uses index i
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = ')';

I am really hoping that this defect can be found by inspection of the
source, because trying to reproduce it is fast getting me nowhere.
Unfortunately, the data involved are customer confidential, so I cannot
provide the original table.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

[BUGS] BUG #4900: Query planner misses obvious optimization on ordered UNION DISTINCT

[Peter Headland]

The following bug has been logged online:

Bug reference:  4900
Logged by:  Peter Headland
Email address:  pheadl...@actuate.com
PostgreSQL version: 8.4.0
Operating system:   Windows
Description:Query planner misses obvious optimization on ordered
UNION DISTINCT
Details: 

Consider the following union:

SELECT a, b, c FROM t WHERE d = 1
UNION DISTINCT
SELECT a, b, c FROM t WHERE d = 2
ORDER BY b, c;

I have a table for which the plan for the above is ...->sort->unique->sort.
I infer that the first sort is a,b,c.

The obvious optimization is to reorder the columns used in the first sort to
eliminate the need for the second sort. To illustrate this, I change the
query to 

SELECT b, c, a FROM t WHERE d = 1
UNION DISTINCT
SELECT b, c, a FROM t WHERE d = 2
ORDER BY b, c;

the plan now becomes ...->sort->distinct.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4899: Open parenthesis breaks query plan

[Peter Headland]

While noodling around some more, I found that a comparison to '()' allows use 
of the index, as does '(abc)' and even '(a(b(c)d)e)'. It appears that 
mismatched open/close paren pairs trigger the bug. Obviously something is 
parsing the string literal and mishandling parentheses. I don't understand why 
parentheses should be significant inside a string literal in the first place.

Also, just to be 100% clear, the open paren can be anywhere in the string, so a 
comparison to 'abcdefgh(ijklmnop' still triggers the bug.

-- 
Peter Headland
Architect - e.Reports
Actuate Corporation

-Original Message-
From: Peter Headland 
Sent: Saturday, July 04, 2009 18:03
To: pgsql-bugs@postgresql.org
Subject: BUG #4899: Open parenthesis breaks query plan


The following bug has been logged online:

Bug reference:  4899
Logged by:  Peter Headland
Email address:  pheadl...@actuate.com
PostgreSQL version: 8.4.0
Operating system:   Windows
Description:Open parenthesis breaks query plan
Details: 

In a moderate-size table (~400,000 rows), an equality match on an unindexed
varchar column to a string that contains an open parenthesis '(' prevents
the optimizer from using an obvious index. Changing the open parenthesis to
another character, such as ')' allows the obvious index to be used. I have
been unable to reproduce this on simple test data so far, so it is obviously
fairly subtle.

Abstract example of the issue:

o table t has a composite index i comprising columns c1, c2, c3

o column t.c4 is not indexed

Illustration of the queries:

-- Full table scan
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = '(';

-- Uses index i
SELECT COUNT(*) FROM t WHERE t.c1 = 123 AND t.c4 = ')';

I am really hoping that this defect can be found by inspection of the
source, because trying to reproduce it is fast getting me nowhere.
Unfortunately, the data involved are customer confidential, so I cannot
provide the original table.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4899: Open parenthesis breaks query plan

[Peter Headland]

> your theory is pretty much nonsense
...
> What I think is happening is that '(' is a sufficiently common value
that
> the planner thinks a seqscan is superior to an indexscan for it.

Your theory is also "pretty much nonsense" if you read the detailed
description I gave in my initial post, in which I explain that presence
of a '(' character anywhere at all in the string literal triggers the
problem. For example 'abc(def'. It also totally fails to explain the way
that matched parentheses prevent the problem (which makes it obvious
that _something_ somewhere is doing enough parsing to count
parentheses).

Now that I know about EXPLAIN ANALYZE, I got these (I apologise for the
redactions and obfuscation, which represent the downside of
self-documenting column names):


"Aggregate  (cost=534.40..534.41 rows=1 width=0) (actual
time=0.442..0.444 rows=1 loops=1)"
"  Output: count(*)"
"  ->  Bitmap Heap Scan on a_table  (cost=9.49..534.39 rows=1 width=0)
(actual time=0.412..0.412 rows=0 loops=1)"
"Output: ... 21 columns ..."
"Recheck Cond: (an_integer_column = 65)"
"Filter: ((a_varchar_column)::text = 'abc(def'::text)"
"->  Bitmap Index Scan on an_index  (cost=0.00..9.49 rows=146
width=0) (actual time=0.118..0.118 rows=197 loops=1)"
"  Index Cond: (an_integer_column = 65)"
"Total runtime: 0.611 ms"



"Aggregate  (cost=534.40..534.41 rows=1 width=0) (actual
time=0.418..0.421 rows=1 loops=1)"
"  Output: count(*)"
"  ->  Bitmap Heap Scan on a_table  (cost=9.49..534.39 rows=1 width=0)
(actual time=0.395..0.395 rows=0 loops=1)"
"Output: ... 21 columns ..."
"Recheck Cond: (an_integer_column = 65)"
"Filter: ((a_varchar_column)::text = 'abc()def'::text)"
"->  Bitmap Index Scan on an_index  (cost=0.00..9.49 rows=146
width=0) (actual time=0.108..0.108 rows=197 loops=1)"
"  Index Cond: (an_integer_column = 65)"
"Total runtime: 0.563 ms"


This puzzles me, because it seems to say that the plan is the same in
both cases, but the graphical display of the plan in pgAdmin III looks
different for the two queries (is there some way/somewhere I can post
screen grabs?). I think the issue is a bug in the way pgAdmin III parses
the output from EXPLAIN. My inability to reproduce the issue with dummy
data would be down to the fact I'd have to get the optimizer to choose
the same plan, which is beyond my ability at this stage.

I also just realized that the graphical display of the plan in pgAdmin
III does not show a full table scan for the mismatched parentheses case;
it shows something that looks like a variant of the index scan symbol,
but with the name of the table underneath. The difference between
display of the two plans is that the initial symbol with the name of the
index underneath vanishes when there is an unmatched open parenthesis. I
have been unable to find an explanation of the symbols used in pgAdmin
III - is there such a thing anywhere?

If we are agreed that the issue is a bug in pgAdmin III, please advise
where I should report such things.

-- 
Peter Headland
Architect - e.Reports
Actuate Corporation


-Original Message-
From: Tom Lane [mailto:t...@sss.pgh.pa.us] 
Sent: Sunday, July 05, 2009 08:39
To: Peter Headland
Cc: pgsql-bugs@postgresql.org
Subject: Re: [BUGS] BUG #4899: Open parenthesis breaks query plan 

"Peter Headland"  writes:
> While noodling around some more, I found that a comparison to '()'
> allows use of the index, as does '(abc)' and even '(a(b(c)d)e)'. It
> appears that mismatched open/close paren pairs trigger the
> bug. Obviously something is parsing the string literal and mishandling
> parentheses.

This isn't "obvious" at all, and in fact your theory is pretty much
nonsense.  What I think is happening is that '(' is a sufficiently
common value that the planner thinks a seqscan is superior to an
indexscan for it.  However, since you have not shown us EXPLAIN output
(much less EXPLAIN ANALYZE output), that's just a guess.

regards, tom lane

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4899: Open parenthesis breaks query plan

[Peter Headland]

As I said further down my previous e-mail, it looks as if the optimizer
is just fine, and the problem is simply a bug in the way pgAdmin III
parses and displays EXPLAIN ANALYZE output in its graphical view.

-- 
Peter Headland
Architect - e.Reports
Actuate Corporation


-Original Message-
From: gsst...@gmail.com [mailto:gsst...@gmail.com] On Behalf Of Greg
Stark
Sent: Monday, July 06, 2009 10:35
To: Peter Headland
Cc: Tom Lane; pgsql-bugs@postgresql.org
Subject: Re: BUG #4899: Open parenthesis breaks query plan

On Mon, Jul 6, 2009 at 5:40 PM, Peter Headland
wrote:
> presence
> of a '(' character anywhere at all in the string literal triggers the
> problem. For example 'abc(def'.

Except according to that explain analyze 'abc(def' ran exactly the
speed as 'abc()def'.

This all seems much more likely to depend on the c1/an_integer_column
value you're querying for than on the string. Keep trying different
values for both columns until you find one that triggers the problem
and send the explain analyze result for that. It could be that '(' was
such a value yesterday but not today if autovacuum has run analyze
since.


-- 
greg
http://mit.edu/~gsstark/resume.pdf

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] BUG #4914: uuid_generate_v4 not present in eithersource or yum/rpm

[Peter Eisentraut]

On Monday 13 July 2009 19:17:49 David Kerr wrote:
> We're using SLES 11, and uuid-ossp isn't delivered in the
> postgresql-contrib package, we opened a case with Novell and this was their
> reply:
> [some nonsense]

I'm sorry to say that SUSE just isn't a good source if you want to do serious 
PostgreSQL deployment.  They ship PostgreSQL because it's there, but they, as 
an organization, just don't care enough to consistently provide the packages, 
updates, and support that you'd want for serious use.  So if you can't use a 
different operating system, either accept what you get and hope you get away 
with it, or prepare to spend some resources to do your own maintenance.

-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Huge speed penalty using <>TRUE instead of =FALSE

[Peter Eisentraut]

On Friday 17 July 2009 11:12:41 Jan-Ivar Mellingen wrote:
> One of our customers discovered that by replacing <>TRUE with =FALSE in
> a query of a table containing 750.000 records reduced the query time
> from about 12 seconds to about 60 milliseconds!

> This is a dramatical difference, but I cannot understand why. In my head
> "<>TRUE" should behave exactly the same as "=FALSE". This looks like a
> bug to me, or am I overlooking something?

The planner just isn't that smart.  The boolean type is a special case where 
<> some_value implies = some_other_value, but this doesn't generalize well to 
other data types.  And the planner doesn't have a whole lot of data type 
specific knowledge.

I think a better index definition might actually be on alarm_status, with a 
partial index predicate on logg_avsluttet = false.


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Re: [BUGS] Huge speed penalty using <>TRUE instead of =FALSE

[Peter Eisentraut]

On Friday 17 July 2009 12:45:47 Mikael Krantz wrote:
> It might be that your column may be NULL as well as TRUE or FALSE. I
> am no expert in this matter though.

Nulls also need to be considered when attempting to substitute purportedly 
equivalent clauses.  But in this case it wouldn't actually matter, because

WHERE foo <> TRUE

and

WHERE foo = false

would both omit the row if foo is null.  Both expressions only return true if 
foo has the value "false".  But again, this is data type specific knowledge.


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs