Re: [HACKERS] bug or feature, || -operator and NULLs

[Robert Treat]

On Wednesday 18 October 2006 10:35, Lukas Kahwe Smith wrote:
> Neil Conway wrote:
> > I think a more sensible proposal could be made for some sort of optional
> > "compatibility mode", as has been discussed many times in the past:
> > different NULL handling could theoretically be part of an Oracle SQL
> > dialect.
>
> even more exciting in this context would be to add user controllable
> NULL sorting behaviour. afaik this is in sql:2003.
>

Something like 

pagila=# select staff_id from staff order by picture is not null;
 staff_id
--
2
1
(2 rows)

pagila=# select staff_id from staff order by picture;
 staff_id
--
1
2
(2 rows)

-- 
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL

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

Re: [HACKERS] Bitmap index status

[Gavin Sherry]

On Wed, 18 Oct 2006, Heikki Linnakangas wrote:

> Hi,
>
> I don't want to harass you :), but what's the status with the bitmap
> index code? Is there something I can do to help?
>

Hi Heikki,

The streaming is implemented, as are range queries. I need to bring it up
to HEAD and back-patch to bizgres since... it's not diverged fairly
significantly from that code base.

Two outstanding items are handling vacuum and I was considering having a
bitmap selectivity function but I haven't really looked into it.

Once I bring it up to HEAD I'll post.

Thanks,

Gavin


---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Multiple postmaster + RPM + locale issues

[Peter Eisentraut]

Devrim GUNDUZ wrote:
> However, I think we need to install locale files of each major
> releases to different directory.

No, you should rename the locale files, like psql-8.1.po.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] [GENERAL] UDF and cache

[Jim C. Nasby]

And PLEASE do not post something to 3 lists; it's a lot of extra traffic
for no reason.

Moving to -hackers.

On Wed, Oct 18, 2006 at 05:15:13PM -0400, jungmin shin wrote:
> Hello all,
> 
> I read a paper,  which is Query optimization in the presence of Foreign
> Functions.
> And the paper , there is a paragraph like below.
> 
> In order to reduce the number of invocations, caching  the results of
> invocation was suggested in Postgres.
> 
> I'd like to know in detail about how postgres is maintaing the cache of
> UDFs.
> 
> Thanks,
> Jungmin
> 
> 
> -- 
> Jungmin Shin

-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

   http://archives.postgresql.org

[HACKERS] Multiple postmaster + RPM + locale issues

[Devrim GUNDUZ]

Hi,

I have almost finished working on multiple rpm + postmaster issue today.
The spec file and patches in pgsqlrpms cvs work almost as expected and
does some preliminary work about multiple postmaster installation issue
(we can build the rpms and they are installed correctly).

I have a question about locale files. AFAICS, our configure script has
hardcoded localedir value:

  # Note: share/locale is always the default, independent of $datadir
  localedir='${prefix}/share/locale'

However, I think we need to install locale files of each major releases
to different directory. I can patch the line above in spec file; however
I'm not sure where to install those locale files.

The question is:

- Is it a good idea to add a new --localedir parameter to configure
script so that the localedir will be configurable?

- If not, where should I install locale specific files? 

- ... or should I do something different?

Thanks.

Regards,
-- 
The PostgreSQL Company - Command Prompt, Inc. 1.503.667.4564
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Managed Services, Shared and Dedicated Hosting
Co-Authors: plPHP, plPerlNG - http://www.commandprompt.com/



signature.asc
Description: This is a digitally signed message part

Re: [HACKERS] UDF and cache

[Jim C. Nasby]

On Wed, Oct 18, 2006 at 05:15:13PM -0400, jungmin shin wrote:
> Hello all,
> 
> I read a paper,  which is Query optimization in the presence of Foreign
> Functions.
> And the paper , there is a paragraph like below.
> 
> In order to reduce the number of invocations, caching  the results of
> invocation was suggested in Postgres.
> 
> I'd like to know in detail about how postgres is maintaing the cache of
> UDFs.

It's not. See list archives for past discussions.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

[HACKERS] UDF and cache

[jungmin shin]

Hello all,
 
I read a paper,  which is Query optimization in the presence of Foreign Functions.
And the paper , there is a paragraph like below.
 
In order to reduce the number of invocations, caching  the results of invocation was suggested in Postgres.
 
I'd like to know in detail about how postgres is maintaing the cache of UDFs.
 
Thanks,
Jungmin
-- Jungmin Shin 

Re: [HACKERS] Performance/Security question on caching function OIDs for a connection

[Tom Lane]

Weslee Bilodeau <[EMAIL PROTECTED]> writes:
> On the input and output functions for the new type, I lookup the
> encrypt/decrypt functions using -
>   FuncnameGetCandidates( list_make1( makeString( "decrypt" ) )
> Running through the list and getting the functions OID, then calling
> with OidFunctionCall3().

Yech.  That sounds trivially crashable, just define decrypt() to do
something other than what your code is expecting

regards, tom lane

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

   http://archives.postgresql.org

[HACKERS] Performance/Security question on caching function OIDs for a connection

[Weslee Bilodeau]

I'm working on my custom encryption types (as outlined in another
thread) and was curious of one potential performance hit.

On the input and output functions for the new type, I lookup the
encrypt/decrypt functions using -

FuncnameGetCandidates( list_make1( makeString( "decrypt" ) )

Running through the list and getting the functions OID, then calling
with OidFunctionCall3().

I'm concerned about the performance impact if say, importing thousands
of rows in a COPY, doing the lookup for every row. To get around this I
was thinking of doing the lookup once then caching the OIDs for the
functions (a per-connection cache, not globally).

I know the function can be dropped, I'm trapping the error so I'm not as
concerned, but is it possible for someone to replace the same OID with
another function?

My thoughts were if the call failed, perform another lookup for the new
OID, if it can't be found error out. But if the OID just points to a new
function it may not generate an error and just return something unexpected.

I'm not sure if its safe, or if I shouldn't even bother trying to cache
for performance?

I know it makes little difference when inserting one row as encryption
takes the bulk of the time, but when you scale to thousands+ inserts at
once?

Weslee

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[Magnus Hagander]

> > > Should work fine on Windows. fileno() is deprecated however, with 
> > > the following comment:
> > > C:\Program Files\Microsoft Visual Studio
> > > 8\VC\INCLUDE\stdio.h(688) : see
> > >  declaration of 'fileno'
> > > Message: 'The POSIX name for this item is deprecated. 
> > > Instead, use the ISO C++ conformant name: _fileno. See 
> online help for details.'
> > 
> > Only Microsoft would think that they get to unilaterally 
> deprecate a 
> > POSIX standard API :-(

Well.
a) It's not a POSIX platform.
b) There is a #define you can set to disable the deprecation, from what
I can tell.


> Yeah, these are the same guys who chose to implement select() 
> to only work on sockets and not on plain file descriptors 
> (for example you can't make it work on a pipe).  This must be 
> the single most far-reaching change they did to POSIX 
> compatibility.  It's incredibly idiotic and problematic.  And 
> note that it's not that it's deprecated -- it just doesn't 
> work at all :-(

You will notice that in the win32 API there is no such thing as a "plain
file descriptor". The Windows API has you using HANDLEs for both sockets
and files, and as long as you use the Windows API, you can pass them
both to the same function (just use waitformultipleobjects instead of
select).
select() is only in the winsock library, which is there only for
compatibility with Berkeley Sockets. And it works with sockets created
that way...

That said, it can certainly be irritating :-) Oh, and if you want it for
pipes, look at some of the port code for win32 - claudio did a pipe()
implementation that works fine with select().

//Magnus

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Mirror problems for download

[Magnus Hagander]

> > I haven't noticed any torrent files for postgreSQL releases 
> - maybe we 
> > can look into setting this up to share the load, 
> particularly around 
> > release times.
> 
> We used to release torrents but stopped because they weren't 
> all that popular. Search the archives for details.

Not only that, it also didn't really help anybody given that the
postgresql distribution files are so small.

Also, ifr it wasn't clear, this was *not* a load case. It was a case of
a dangling file with incorrect permissions that blocked a script from
doing it's job. A permanent fix is being worked on.

//Magnus

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

   http://archives.postgresql.org

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[Tom Lane]

"George Pavlov" <[EMAIL PROTECTED]> writes:
>> It'd be interesting to verify whether it's the same on
>> George's machine though.

> Let me know how to test this.

Identify the PID of one of your active backends (from "ps" or by looking
in pg_stat_activity), and then run

strace -p backend-pid-here 2>tmpfile

You'll need to do that as either root or postgres.  Let it run for a
minute or three, preferably long enough to capture some long log
messages being emitted by that backend.  (Bonus points if you can
capture one that's actually intermixed with others in the log, but
that might take some luck.)  Stop it with control-C whenever you get
bored, and then grep through the tmpfile for lines beginning "write(2,".
It should be pretty clear whether log messages are being sent as single
or multiple write() operations.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[George Pavlov]

> the behavior.  It'd be interesting to verify whether it's the same on
> George's machine though.

Let me know how to test this. (Please do a "for dummies" version -- I am
not sure I can figure it out from the thread even though someone else
might be able to.)


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[Tom Lane]

I wrote:
> I checked around with some kernel/glibc gurus in Red Hat, and the
> consensus seemed to be that we'd be better off to bypass fprintf() and
> just send message strings to stderr using write() --- ie, instead of
> elog.c doing
> fprintf(stderr, "%s", buf.data);
> do
> write(fileno(stderr), buf.data, strlen(buf.data));

I did some strace'ing of the backend, and observed that even for very
long messages (upwards of 100K) the fprintf results in a single write()
call.  This was true on both Fedora Core 5 and a pretty old HPUX version.
So it'd seem that most versions of libc already know about optimizing
fprintf-%s into a direct write(), and changing our code wouldn't change
the behavior.  It'd be interesting to verify whether it's the same on
George's machine though.

regards, tom lane

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

   http://archives.postgresql.org

Re: [HACKERS] pg_internal.init is hazardous to your health

[Simon Riggs]

On Wed, 2006-10-18 at 13:24 -0400, Tom Lane wrote:
> "Simon Riggs" <[EMAIL PROTECTED]> writes:
> > RelationCacheInitFileInvalidate() is also called on each
> > FinishPreparedTransaction().
> 
> Surely not...

I take that to mean there's nothing special about prepared transactions
and invalidating the rel cache, so we *do* need to have a separate WAL
record in all cases.

OK, I'll write up a patch later today (working in US for few days).

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[Alvaro Herrera]

Tom Lane wrote:
> "Magnus Hagander" <[EMAIL PROTECTED]> writes:
> > Should work fine on Windows. fileno() is deprecated however, with the
> > following comment:
> > C:\Program Files\Microsoft Visual Studio
> > 8\VC\INCLUDE\stdio.h(688) : see
> >  declaration of 'fileno'
> > Message: 'The POSIX name for this item is deprecated. Instead,
> > use the ISO C++ conformant name: _fileno. See online help for details.'
> 
> Only Microsoft would think that they get to unilaterally deprecate a
> POSIX standard API :-(

Yeah, these are the same guys who chose to implement select() to only
work on sockets and not on plain file descriptors (for example you can't
make it work on a pipe).  This must be the single most far-reaching
change they did to POSIX compatibility.  It's incredibly idiotic and
problematic.  And note that it's not that it's deprecated -- it just
doesn't work at all :-(

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] bug or feature, || -operator and NULLs

[Jeff Davis]

On Wed, 2006-10-18 at 14:28 +0200, Andreas Joseph Krogh wrote:
> On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
> > > The following query returns NULL in PG:
> > > SELECT NULL || 'fisk';
> > >
> > > But in Oracle, it returns 'fisk':
> > > SELECT NULL || 'fisk' FROM DUAL;
> > >
> > > The latter seems more logical...
> >
> > Why would it be more logical ?
> 
> How many times do you *really* want to get the "not known" answer here 
> instead 
> of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?
> 

When you pass the result to an aggregate function. Example:

=> create table test(days int);
CREATE TABLE
=> insert into test values(1);
INSERT 0 1
=> insert into test values(2);
INSERT 0 1
=> insert into test values(NULL);
INSERT 0 1
=> select sum((days::text||' days')::interval) from test;
  sum

 3 days
(1 row)

=> select sum((coalesce(days::text,'')||' days')::interval) from test;
ERROR:  invalid input syntax for type interval: " days"

The last query represents the "auto-coalescing" behavior you are looking
for. However, it creates an error on a query that is perfectly valid. 

Regards,
Jeff Davis



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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] bug or feature, || -operator and NULLs

[Martijn van Oosterhout]

On Wed, Oct 18, 2006 at 11:36:44AM -0700, Jeff Davis wrote:
> > What's being suggested simply violates common sense. Basically:
> > 
> > if (a = b) then (a||c = b||c)
> > 
> 
> If a is 'x' and b is 'x' and c is NULL, the above statement doesn't hold
> in PostgreSQL.

Heh, well, c is supposed to be not NULL. Missed that. I was using the
equals to include (NULL = NULL) but in SQL it's not like that.

> I'm not disagreeing with your overall point, I'm just missing what you
> meant by the above statement. What are a, b, and c supposed to be?

I was trying to point out that what was being proposed was:

NULL || 'bar'=> 'bar'
'' || 'bar'  => 'bar'

But NULL is not an empty string. Oracle chooses to make NULL and the
empty string the same, we don't. So logically we shouldn't treat them
the same for text concatination either.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] Bug?

[Jim C. Nasby]

Moving to -sql.

On Wed, Oct 18, 2006 at 06:53:46PM +0530, Indira Muthuswamy wrote:
> Hai,
> 
> I have encountered a problem with PostgreSQL.I have created a table
> 'tab1' with a column 'a' with serial type.I entered 20 records into the
> table.So the query
>  select max(a) from tab1;
> returned 20.When I tried the same query after the command
> truncate table tab1;
> I found that the output of the first query as
> 
> max
> -
> 
> (1 row)
> When I tried to insert a new row into the table tab1 I found that the
> value at column a incremented to the value 21.
> But I heard from my friends that the value of the serial column gets
> decremented whenever we issue the 'truncate table' command (in MS SQL
> SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me on
> this?

Your friends are mistaken. TRUNCATE does nothing to affect sequences.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] bug or feature, || -operator and NULLs

[Jim C. Nasby]

Yes, well, we english speakers get to deal with the monstrosity that is
'www'. :)

In any case, I believe coalesce is in the standard, and even if it's
not, Oracle is the only database I know of that doesn't use it.

If you're that unhappy with coalesce and ||, you can always create
functions that will do what you want.

On Wed, Oct 18, 2006 at 04:15:00PM +0200, Mario Weilguni wrote:
> Yes it's hard for me, maybe because I am no native english speaker. 
> 
> -Urspr?ngliche Nachricht-
> Von: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
> Gesendet: Mittwoch, 18. Oktober 2006 16:11
> An: Lukas Kahwe Smith
> Cc: Mario Weilguni; pgsql-hackers@postgresql.org
> Betreff: Re: [HACKERS] bug or feature, || -operator and NULLs
> 
> Lukas Kahwe Smith wrote:
> > Mario Weilguni wrote:
> >> Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even 
> >> harder to type.
> >
> > amen .. coalesce was invented by a sadistic twit (something which 
> > people have also called me .. so it goes).
> 
> Perhaps people are trying to pronounce it wrongly. According to m-w, the 
> right ways is:
> 
>Pronunciation: "kO-&-'les
> 
> 
> or more informally "koh a less".
> 
> Is that really so hard?
> 
> cheers
> 
> andrew
> 
> 
> 
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings
> 

-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Jeff Davis]

On Wed, 2006-10-18 at 15:57 +0200, Martijn van Oosterhout wrote:
> On Wed, Oct 18, 2006 at 03:44:05PM +0200, Andreas Joseph Krogh wrote:
> > > When in doubt, consult the standard ... Oracle's treatment of NULL is
> > > known to violate the standard, IIRC. Your measure of correctness seems
> > > to be "appears to me more logical", but ours is "complies with the
> > > standard".
> > 
> > I know PG violates the standard in other places and core's favourite 
> > argument 
> > for doing so is "the standard is braindead here, so we do it our way".
> 
> But they're few and far between and not on things people actually
> notice much.
> 
> What's being suggested simply violates common sense. Basically:
> 
> if (a = b) then (a||c = b||c)
> 

If a is 'x' and b is 'x' and c is NULL, the above statement doesn't hold
in PostgreSQL.

I'm not disagreeing with your overall point, I'm just missing what you
meant by the above statement. What are a, b, and c supposed to be?

Regards,
Jeff Davis


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

Re: [HACKERS] Mirror problems for download

[Jim C. Nasby]

On Wed, Oct 18, 2006 at 09:25:01PM +0930, Shane Ambler wrote:
> I haven't noticed any torrent files for postgreSQL releases - maybe we 
> can look into setting this up to share the load, particularly around 
> release times.

We used to release torrents but stopped because they weren't all that
popular. Search the archives for details.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] analyzing debugging sentences.

[Martijn van Oosterhout]

On Wed, Oct 18, 2006 at 02:11:53PM -0400, jungmin shin wrote:
> Hello,
> 
> I set on for following items in postgresql.conf
> 
> debug_print_parse
> debug_print_rewritten
> debug_print_plan
> debug_print_pretty
> 
> now, I can see something in PGAdmin query tool when I execute a query.
> can I get some idea how I can read comprehend the output?



It's almost (with a few exceptions) an exact repesentation of the in
memory structures. If you look in readfuncs.c and outfuncs.c you can
see the code that generates it.

CONST is a ConstNode
OPEXPR is an OpExprNode

etc...

So you'll have to read the header files to understand what they mean.

Hope this helps,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function

[Weslee Bilodeau]

Jim C. Nasby wrote:
> On Tue, Oct 17, 2006 at 04:34:35PM +0300, Marko Kreen wrote:
>>> I'm not sure if anyone else needs something like it, but it allows us to
>>> transparently encrypt data directly in the tables. Minimum application
>>> changes ('select enc_key' at connection) - the main requirement when
>>> working on legacy code that needs to match todays security polices quickly.
>> Some want row-level access control, then your scheme would not be enough.
>>
>> Maybe it would be better to avoid combining the keys, instead have
>> hidden key in database and several user keys that grant access to that
>> key, thus you can revoke access from only some users.
>>
>> But one thing I suggest strongly - use PGP encryption instead
>> of old encrypt()/decrypt().  PGP hides the data much better,
>> espacially in case of lot of small data with same key.
>
> Better yet, allow the user to plug in encryption modules. Different
> people want different kinds of encryption. For example, I believe credit
> card companies require AES192.


As its really just a type wrapper around automatically calling
pgcrypto's encrypt/decrypt functions, this should be very easy to do.

I currently default it to 'bf' (blowfish) I can just make the type
creator have an additional parameter that takes any method recognized by
the crypto library.


Weslee

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function

[Weslee Bilodeau]

Marko Kreen wrote:
> On 10/16/06, Weslee Bilodeau <[EMAIL PROTECTED]> wrote:
>> Marko Kreen wrote:
>> > The PGP functions happen to do it already - pgp_key_id().
>>
>> Actually, Tom helped me realize I made a mistake, which I'm following
>> his suggestion. Not tying keys to OIDs which change when backup/restored.
> 
> Yeah, tying to oids is bad, you should link to names,
> preferably schema-qualified.  Anyway, that was just off-hand
> suggestion.
> 
> 
>> [ snip nice description ]
> 
> 
>> I'm not sure if anyone else needs something like it, but it allows us to
>> transparently encrypt data directly in the tables. Minimum application
>> changes ('select enc_key' at connection) - the main requirement when
>> working on legacy code that needs to match todays security polices
>> quickly.
> 
> Some want row-level access control, then your scheme would not be enough.

Right now once I have it working, at least in the column-level keys I'm
looking for, I can worry about this.

My main concern at having multiple keys on the same column is the
ability to index it.

Right now you can index the encrypted values, however you can only do
exact matches to use the index.

IE -

select * from table where credit_card = '';

That would work, as it encrypts the '' then compares the encrypted
values in the index.

By having multiple keys, I can't do that.

Aside from breaking the index, you can't do any comparisons at all anymore.

Because '' is encrypted then compared.
If credit_card was encrypted using key1, and '' is encrypted using
key2, there can be no match, even if the unencrytped values match.

You'd have to decrypt everything and then compare, which is a large
performance hit.

Also, I like the ability to do this -

insert into table ( credit_card ) values ( '' ) ;

And its automatically encrypted. To support multiple keys, there has to
be a way to switch the keys.

Something like -

select enc_key( 'key1' );
insert into table ( credit_card ) values ( '' ) ;

select enc_key( 'key2' );
insert into table ( credit_card ) values ( '' ) ;

Its possible, just a bit messy.

The other thing is when doing -

select * from table;

If you only specified key1, you can view the '', but if you don't
specify the key2, what is displayed as it doesn't have the decryption
key for ''?

Right now it errors out if attempting to view a record you haven't
provided the key for. In this case you need to give all keys before you
can do the select.



> 
> Maybe it would be better to avoid combining the keys, instead have
> hidden key in database and several user keys that grant access to that
> key, thus you can revoke access from only some users.

I like more security. Its a combined two keys for the basic idea that -

The database itself does not have the key. If you hack into the server,
you will not be able to decrypt the values.

The application itself does not have the key.

If you hack the application and have access to enc_raw_read() (this
allows backups to work - selects the raw-encrypted values), you can't
decrypt the values.

Basically you call enc_key( 'application_key' ), the encrypt function is
called with a basic database_key || application_key, ensuring both parts
are always needed.

Thus backups are secure, and the application is a little more secure.

For things like storing credit cards, SSNs, personal information, this
allows us to say if you run off with the server, you can't do anything.

By storing the key in the database as you suggest and just giving access
to that key, any DBA can decrypt the values, and anyone who runs off
with a backup can as well. Your only securing the SQL-access, not the
backups. Which, more often its backups that are stolen, lost, etc.

> 
> But one thing I suggest strongly - use PGP encryption instead
> of old encrypt()/decrypt().  PGP hides the data much better,
> espacially in case of lot of small data with same key.
> 
> 

I may look into PGP once I get this part working.
Smaller steps for me, I'm new to postgres internal API. :)

Weslee

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] analyzing debugging sentences.

[Andrew Dunstan]

jungmin shin wrote:


Hello,
 
I set on for following items in postgresql.conf
 
debug_print_parse

debug_print_rewritten
debug_print_plan
debug_print_pretty
 
now, I can see something in PGAdmin query tool when I execute a query.

can I get some idea how I can read comprehend the output?
 


Please ask your question on the right list - this list is only for 
discussion about development of postgres itself, not about how to use 
it. You probably want pgsql-general.


cheers

andrew

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

[HACKERS] analyzing debugging sentences.

[jungmin shin]

Hello,
 
I set on for following items in postgresql.conf
 
debug_print_parse
debug_print_rewritten
debug_print_plan
debug_print_pretty
 
now, I can see something in PGAdmin query tool when I execute a query.
can I get some idea how I can read comprehend the output?
 
-- Executing query:
select 0.4 * 0.5;
DEBUG:  parse tree:
DETAIL: {QUERY 
   :commandType 1 
   :querySource 0 
   :canSetTag true 
   :utilityStmt <> 
   :resultRelation 0 
   :into <> 
   :hasAggs false 
   :hasSubLinks false 
   :rtable <> 
   :jointree 
  {FROMEXPR 
  :fromlist <> 
  :quals <>
  }
   :rowMarks <> 
   :forUpdate false 
   :rowNoWait false 
   :targetList (
  {TARGETENTRY 
  :expr 
 {OPEXPR 
 :opno 1760 
 :opfuncid 0 
 :opresulttype 1700 
 :opretset false 
 :args (
{CONST 
:consttype 1700 
:constlen -1 
:constbyval false 
:constisnull false 
:constvalue 10 [ 10 0 0 0 -1 -1 1 0 -96 15 ]
}
{CONST 
:consttype 1700 
:constlen -1 
:constbyval false 
:constisnull false 
:constvalue 10 [ 10 0 0 0 -1 -1 1 0 -120 19 ]
}
 )
 }
  :resno 1 
  :resname ?column? 
  :ressortgroupref 0 
  :resorigtbl 0 
  :resorigcol 0 
  :resjunk false
  }
   )
   :groupClause <> 
   :havingQual <> 
   :distinctClause <> 
   :sortClause <> 
   :limitOffset <> 
   :limitCount <> 
   :setOperations <> 
   :resultRelations <>
   }

DEBUG:  rewritten parse tree:
DETAIL:  (
   {QUERY 
   :commandType 1 
   :querySource 0 
   :canSetTag true 
   :utilityStmt <> 
   :resultRelation 0 
   :into <> 
   :hasAggs false 
   :hasSubLinks false 
   :rtable <> 
   :jointree 
  {FROMEXPR 
  :fromlist <> 
  :quals <>
  }
   :rowMarks <> 
   :forUpdate false 
   :rowNoWait false 
   :targetList (
  {TARGETENTRY 
  :expr 
 {OPEXPR 
 :opno 1760 
 :opfuncid 0 
 :opresulttype 1700 
 :opretset false 
 :args (
{CONST 
:consttype 1700 
:constlen -1 
:constbyval false 
:constisnull false 
:constvalue 10 [ 10 0 0 0 -1 -1 1 0 -96 15 ]
}
{CONST 
:consttype 1700 
:constlen -1 
:constbyval false 
:constisnull false 
:constvalue 10 [ 10 0 0 0 -1 -1 1 0 -120 19 ]
}
 )
 }
  :resno 1 
  :resname ?column? 
  :ressortgroupref 0 
  :resorigtbl 0 
  :resorigcol 0 
  :resjunk false
  }
   )
   :groupClause <> 
   :havingQual <> 
   :distinctClause <> 
   :sortClause <> 
   :limitOffset <> 
   :limitCount <> 
   :setOperations <> 
   :resultRelations <>
   }
)

DEBUG:  plan:
DETAIL: {RESULT 
   :startup_cost 0.00 
   :total_cost 0.01 
   :plan_rows 1 
   :plan_width 0 
   :targetlist (
  {TARGETENTRY 
  :expr 
 {OPEXPR 
 :opno 1760 
 :opfuncid 1726 
 :opresulttype 1700 
 :opretset false 
 :args (
{CONST 
:consttype 1700 
:constlen -1 
:constbyval false 
:constisnull false 
:constvalue 10 [ 10 0 0 0 -1 -1 1 0 -96 15 ]
}
{CONST 
:consttype 1700 
:constlen -1 
:constbyval false 
:constisnull false 
:constvalue 10 [ 10 0 0 0 -1 -1 1 0 -120 19 ]
}
 )
 }
  :resno 1 
  :resname ?column? 
  :ressortgroupref 0 
  :resorigtbl 0 
  :resorigcol 0 
  :resjunk false
  }
   )
   :qual <> 
   :lefttree <> 
   :righttree <> 
   :initPlan <> 
   :extParam (b)
   :allParam (b)
   :nParamExec 0 
   :resconstantqual <>
   }
 
 -- Jungmin Shin 

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[Tom Lane]

"Magnus Hagander" <[EMAIL PROTECTED]> writes:
> Should work fine on Windows. fileno() is deprecated however, with the
> following comment:
> C:\Program Files\Microsoft Visual Studio
> 8\VC\INCLUDE\stdio.h(688) : see
>  declaration of 'fileno'
> Message: 'The POSIX name for this item is deprecated. Instead,
> use the ISO C++ conformant name: _fileno. See online help for details.'

Only Microsoft would think that they get to unilaterally deprecate a
POSIX standard API :-(

We appear to be using fileno() in some dozens of places already,
so I see no reason to worry about this.

regards, tom lane

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] Getting the type Oid in a CREATE TYPE output function

[Jim C. Nasby]

On Tue, Oct 17, 2006 at 04:34:35PM +0300, Marko Kreen wrote:
> >I'm not sure if anyone else needs something like it, but it allows us to
> >transparently encrypt data directly in the tables. Minimum application
> >changes ('select enc_key' at connection) - the main requirement when
> >working on legacy code that needs to match todays security polices quickly.
> 
> Some want row-level access control, then your scheme would not be enough.
> 
> Maybe it would be better to avoid combining the keys, instead have
> hidden key in database and several user keys that grant access to that
> key, thus you can revoke access from only some users.
> 
> But one thing I suggest strongly - use PGP encryption instead
> of old encrypt()/decrypt().  PGP hides the data much better,
> espacially in case of lot of small data with same key.

Better yet, allow the user to plug in encryption modules. Different
people want different kinds of encryption. For example, I believe credit
card companies require AES192.
-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] pg_internal.init is hazardous to your health

[Tom Lane]

"Simon Riggs" <[EMAIL PROTECTED]> writes:
> RelationCacheInitFileInvalidate() is also called on each
> FinishPreparedTransaction().

Surely not...

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Additional stats for Relations

[Jim C. Nasby]

Also how many times a relation has been vacuumed (which puts all the
other numbers in more perspective... good catch Simon). And I think
number of pages that could not be added to the FSM would also be
extremely valuable.

On Wed, Oct 18, 2006 at 11:27:39AM +0530, NikhilS wrote:
> Hi,
> 
> So:
> heap_blks_reused (with Jim's semantics), heap_blks_extend,
> heap_blks_truncate are the "interesting" stats? Will try to work up a patch
> for this.
> 
> Regards,
> Nikhils
> EnterpriseDB   http://www.enterprisedb.com
> On 10/15/06, Simon Riggs <[EMAIL PROTECTED]> wrote:
> >
> >On Sat, 2006-10-14 at 11:32 +0530, NikhilS wrote:
> >
> >> On 10/13/06, Jim C. Nasby <[EMAIL PROTECTED]> wrote:
> >
> >
> >> I'm also not sure if this metric is what you actually want,
> >> since a
> >> single page can be returned many times from the FSM even
> >> between
> >> vacuums. Tracking how many pages for a relation have been put
> >> into the
> >> FSM might be more useful...
> >>
> >> 
> >> Pages might be put into the FSM, but by this metric don't we get the
> >> actual usage of the pages from the FSM? Agreed a single page can be
> >> returned multiple times, but since it serves a new tuple, shouldn't we
> >> track it?
> >> 
> >
> >This makes sense for indexes, but only makes sense for heaps when we
> >know that the backend will keep re-accessing the block until it is full
> >- so only of interest in steady-state workloads.
> >
> >IMHO Jim's proposal makes more sense for general use.
> >
> >> > heap_blks_extend: The number of times file extend was
> >> invoked on the
> >> > relation
> >
> >Sounds good
> >
> >> > heap_blks_truncate: The total number of blocks that have
> >> been truncated due
> >> > to vacuum activity e.g.
> >
> >Sounds good
> >
> >> > As an addendum to the truncate stats above, we can also have
> >> the additional
> >> > following stats:
> >> >
> >> > heap_blks_maxtruncate: The max block of buffers truncated in
> >> one go
> >> >
> >> > heap_blks_ntruncate: The number of times truncate was called
> >> on this
> >> > relation
> >
> >Those last 2 sound too complex for normal use and ntruncate is most
> >likely the same as number of vacuums anyway. Hmmm...Perhaps nvacuums is
> >a more interesting metric? We've got last vacuum date, but no indication
> >of how frequently a vacuum has run.
> >
> >> Do you have a use-case for this info? I can see where it might
> >> be neat
> >> to know, but I'm not sure how you'd actually use it in the
> >> real world.
> >>
> >> 
> >> The use-case according to me is that these stats help prove the
> >> effectiveness of autovacuum/vacuum operations. By varying some autovac
> >> guc variables, and doing subsequent (pgbench e.g.) runs, one can find
> >> out the optimum values for these variables using these stats.
> >> 
> >
> >This should be useful for tuning space allocation/deallocation. If we
> >get this patch in early it should help get feedback on this area.
> >
> >--
> >  Simon Riggs
> >  EnterpriseDB   http://www.enterprisedb.com
> >
> >
> >
> 
> 
> -- 
> All the world's a stage, and most of us are desperately unrehearsed.

-- 
Jim Nasby[EMAIL PROTECTED]
EnterpriseDB  http://enterprisedb.com  512.569.9461 (cell)

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Lock partitions

[Mark Wong]

Tom Lane wrote:

Mark Wong <[EMAIL PROTECTED]> writes:

Tom Lane wrote:

Hmm, what sort of errors are we talking about?



ERROR:  too many LWLocks taken


That really shouldn't happen ... are you sure you did a full recompile
after changing NUM_LOCK_PARTITIONS?

Actually ... wait a moment.  The default value of NUM_LOCK_PARTITIONS
is already 16 (1 << LOG2_NUM_LOCK_PARTITIONS where the latter is 4).
Are you saying you set LOG2_NUM_LOCK_PARTITIONS to 16?  That would be
way too many partitions.  I was thinking of testing
LOG2_NUM_LOCK_PARTITIONS in the range of about 2 to 5.


Oops, I can't read bit shifting. =p  I'll do again.

Mark

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

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Lock partitions

[Tom Lane]

Mark Wong <[EMAIL PROTECTED]> writes:
> Tom Lane wrote:
>> Hmm, what sort of errors are we talking about?

> ERROR:  too many LWLocks taken

That really shouldn't happen ... are you sure you did a full recompile
after changing NUM_LOCK_PARTITIONS?

Actually ... wait a moment.  The default value of NUM_LOCK_PARTITIONS
is already 16 (1 << LOG2_NUM_LOCK_PARTITIONS where the latter is 4).
Are you saying you set LOG2_NUM_LOCK_PARTITIONS to 16?  That would be
way too many partitions.  I was thinking of testing
LOG2_NUM_LOCK_PARTITIONS in the range of about 2 to 5.

regards, tom lane

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] 8.1.5 is out

[Tom Lane]

Teodor Sigaev <[EMAIL PROTECTED]> writes:
> 1) Release notice mentions wronng 
>  E.1.1. Migration to version 8.1.4
>   ^ should be 8.1.5

Yup, noticed that the day after :-(

> 2) It's very pity that ours Win32 fixes isn't in 8.1.5 :(

That was intentional, at least from my point of view --- I don't think
those changes have been tested well enough to deserve to go into a
stable release at the last moment.  If we've not seen any problems by
the end of 8.2 beta then I'll stop worrying about them.  But I don't
trust patches that are trying to work around a problem we don't even
understand.

regards, tom lane

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] pg_internal.init is hazardous to your health

[Simon Riggs]

On Wed, 2006-10-18 at 12:49 +1000, Gavin Sherry wrote:

> > We don't actually need to *update* the file, per se, we only need to
> > remove it if no longer valid --- the next incoming backend will rebuild
> > it.  I could see fixing this by making WAL recovery run around and zap
> > all the .init files (only problem is to find 'em), or we could add a new
> > kind of WAL record saying "remove the .init file for database XYZ"
> > to be emitted whenever someone removes the active one.  Thoughts?

Yes, that assessment seems good.

> The latter seems the Right Way except, I guess, that the decision to
> remove the file is buried deep inside inval.c.

I'd prefer the zap everything approach, but emitting a WAL record looks
mostly straightforward and just as good.

RelationCacheInitFileInvalidate() can easily emit a WAL record. This is
called twice in succession, so we would emit WAL on the
RelationCacheInitFileInvalidate(true) call only. I'll work out a patch
for that...XLOG_XACT_RELCACHE_INVALIDATE

RelationCacheInitFileInvalidate() is also called on each
FinishPreparedTransaction(). If that is called 100% of the time, then we
can skip writing an additional record for prepared transactions by
triggering the removal of pg_internal.init when we see a
XLOG_XACT_COMMIT_PREPARED during replay. 
Not sure whether we need to do that, Heikki? Anyone?
I'm guessing no, but it seems sensible to check.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Lock partitions

[Mark Wong]

Tom Lane wrote:

Mark Wong <[EMAIL PROTECTED]> writes:
The number of transaction errors increased when I increased the 
NUM_LOCK_PARTITIONS, which I think is the reason it failed to run when I 
set it to 16.


Hmm, what sort of errors are we talking about?  I wonder if you've
exposed a bug.  Changing NUM_LOCK_PARTITIONS really shouldn't have any
semantic effect.


The libpq client (error log: 
http://dbt.osdl.org/dbt/dbt2dev/results/dev4-015/180/client/error.log) 
is saying things like this:


ERROR:  too many LWLocks taken
CONTEXT:  SQL statement "DELETE FROM new_order
WHERE no_o_id = 2101
  AND no_w_id = 349
  AND no_d_id = 1"

A grep through that file shows that all the unexpected errors appear to 
be due to "too many LWLocks taken".


Mark


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

Re: [HACKERS] Lock partitions

[Tom Lane]

Mark Wong <[EMAIL PROTECTED]> writes:
> The number of transaction errors increased when I increased the 
> NUM_LOCK_PARTITIONS, which I think is the reason it failed to run when I 
> set it to 16.

Hmm, what sort of errors are we talking about?  I wonder if you've
exposed a bug.  Changing NUM_LOCK_PARTITIONS really shouldn't have any
semantic effect.

regards, tom lane

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Andreas Joseph Krogh]

On Wednesday 18 October 2006 14:44, Mario Weilguni wrote:
> Am Mittwoch, 18. Oktober 2006 13:52 schrieb Andreas Joseph Krogh:
> > This has been been discussed before, but Oracle behaves differently, and
> > IMHO in a more correct way.
> >
> > The following query returns NULL in PG:
> > SELECT NULL || 'fisk';
> >
> > But in Oracle, it returns 'fisk':
> > SELECT NULL || 'fisk' FROM DUAL;
> >
> > The latter seems more logical...
>
> I've worked alot with oracle a few years ago and I agree, the feature is
> handy and makes sometimes life easier, but it's simply wrong. I heard a
> while ago that newer oracle versions changed this to sql - standard, is
> this true?

Oracle(10.1.0.4.0) still treats '' as NULL.

Why do these discussions always end in academic arguments over whats more 
logical then not? From a *user's* point of view I really would like it to 
treat the NULL operand of || as '', and obviously many other (at least 
Oracle) users tend to agree with me on that.

On Wednesday 18 October 2006 14:42, Csaba Nagy wrote:
> And it would really return null, if aggregates wouldn't ignore the NULL
> values altogether... the null values are skipped before they get into
> the summing. The same happens with count, if you specify a column it
> will only count the ones which are not null:

If aggregates ignore NULL one could argue that so shuld the ||-operator?

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

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

   http://archives.postgresql.org

Re: [HACKERS] Bitmap index status

[Jie Zhang]

On 10/18/06 2:41 AM, "Heikki Linnakangas" <[EMAIL PROTECTED]> wrote:

> Hi,
> 
> I don't want to harass you :), but what's the status with the bitmap
> index code? Is there something I can do to help?

Not at all. We will send you the new patch soon. Your comments are most
appreciated.

Thanks,
Jie



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] 8.1.5 is out

[Teodor Sigaev]

Is this fix going to make it into 8.2?


8.0, 8.1 and 8.2. It commited in _STABLE branches

--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

  http://archives.postgresql.org

Re: [HACKERS] 8.1.5 is out

[Jan de Visser]

On Wednesday 18 October 2006 11:15, Oleg Bartunov wrote:
> On Wed, 18 Oct 2006, Teodor Sigaev wrote:
> > 1) Release notice mentions wronng
> > (http://www.postgresql.org/docs/8.1/interactive/release.html#RELEASE-8-1-
> >5): E.1.1. Migration to version 8.1.4
> > ^ should be 8.1.5
> > 2) It's very pity that ours Win32 fixes isn't in 8.1.5 :(
> > tarball:2006-10-13 02:05:40 commit: Fri Oct 13 14:00:17
> > 2006 UTC
> > release date:   2006-10-16
>
> This fixes come from our real life experience on Win32 SMP platform
> (actually, Teodor demonstrated the problem even on uniprocessor Win32),
> so I suggest to quick release 8.1.6.

Is this fix going to make it into 8.2?

>
>   Regards,
>   Oleg

jan

> _
> Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
> Sternberg Astronomical Institute, Moscow University, Russia
> Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
> phone: +007(495)939-16-83, +007(495)939-23-83
>
> ---(end of broadcast)---
> TIP 5: don't forget to increase your free space map settings

-- 
--
Jan de Visser                     [EMAIL PROTECTED]

                Baruk Khazad! Khazad ai-menu!
--

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Lock partitions

[Mark Wong]

Tom Lane wrote:

Bruce Momjian <[EMAIL PROTECTED]> writes:

I see this in the CVS commits for 8.2.  Did we determine the proper
number of lock partitions?  Should it be based on the number of buffers
or concurrent sessions allowed?


No.  NUM_LOCK_PARTITIONS needs to be a compile-time constant for a
number of reasons, and there is absolutely zero evidence to justify
making any effort (and spending any cycles) on a variable value.

It would be nice to see some results from the OSDL tests with, say, 4,
8, and 16 lock partitions before we forget about the point though.
Anybody know whether OSDL is in a position to run tests for us?


I have a couple of bigger runs now against a CVS checkout on 2006-09-20 
(please forgive my NUM_BUFFER_PARTITIONS note if you notice that on the 
web pages):


Baseline (default NUM_LOCK_PARTITIONS=4):
notpm 6589
http://dbt.osdl.org/dbt/dbt2dev/results/dev4-015/184/

NUM_LOCK_PARTITIONS=8:
notpm 4471
http://dbt.osdl.org/dbt/dbt2dev/results/dev4-015/180/

NUM_LOCK_PARTITIONS=16:
Failed to run.


The number of transaction errors increased when I increased the 
NUM_LOCK_PARTITIONS, which I think is the reason it failed to run when I 
set it to 16.  And the throughput went down significantly (32%).  Should 
I try against a more recent checkout?


Mark

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Peter Eisentraut]

Am Mittwoch, 18. Oktober 2006 15:07 schrieb Andreas Joseph Krogh:
> Why do these discussions always end in academic arguments over whats more
> logical then not?

Because that is ultimately the reason why SQL behaves the way it does.  I'm 
sure we could all come up with a long list of behaviors that would 
be "useful" or "better" or "intuitive", and we could also come up with a long 
list of reasons why such a database system would be totally crappy.  You only 
need to check with our friends in Uppsala for some examples.

-- 
Peter Eisentraut
http://developer.postgresql.org/~petere/

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] bug or feature, || -operator and NULLs

[Csaba Nagy]

> The following query returns NULL in PG:
> SELECT NULL || 'fisk';
> 
> But in Oracle, it returns 'fisk':
> SELECT NULL || 'fisk' FROM DUAL;
> 
> The latter seems more logical...

Why would it be more logical ?

NULL means "value not known".

Concatenate "value not known" with 'fisk' -> what's the logical answer?

I would say the logical result is 'value not known'... if one of the
components is not known, how can you know what is the result ?

Cheers,
Csaba.



---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] 8.1.5 is out

[Oleg Bartunov]

On Wed, 18 Oct 2006, Teodor Sigaev wrote:

1) Release notice mentions wronng 
(http://www.postgresql.org/docs/8.1/interactive/release.html#RELEASE-8-1-5):

   E.1.1. Migration to version 8.1.4
^ should be 8.1.5
2) It's very pity that ours Win32 fixes isn't in 8.1.5 :(
tarball: 	2006-10-13 02:05:40	commit:		Fri Oct 13 14:00:17 
2006 UTC

release date:   2006-10-16


This fixes come from our real life experience on Win32 SMP platform
(actually, Teodor demonstrated the problem even on uniprocessor Win32),
so I suggest to quick release 8.1.6.

Regards,
Oleg
_
Oleg Bartunov, Research Scientist, Head of AstroNet (www.astronet.ru),
Sternberg Astronomical Institute, Moscow University, Russia
Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/
phone: +007(495)939-16-83, +007(495)939-23-83

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] bug or feature, || -operator and NULLs

[Teodor Sigaev]

even more exciting in this context would be to add user controllable 
NULL sorting behaviour. afaik this is in sql:2003.


ORDER BY .. [ NULLS (FIRST|LAST) ] ?

Wait a bit :), I'm waiting for separate 8.2 branch.

--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

  http://www.postgresql.org/docs/faq

[HACKERS] 8.1.5 is out

[Teodor Sigaev]

1) Release notice mentions wronng 
(http://www.postgresql.org/docs/8.1/interactive/release.html#RELEASE-8-1-5):

E.1.1. Migration to version 8.1.4
^ should be 8.1.5
2) It's very pity that ours Win32 fixes isn't in 8.1.5 :(
tarball:2006-10-13 02:05:40 
commit: Fri Oct 13 14:00:17 2006 UTC
release date:   2006-10-16
--
Teodor Sigaev   E-mail: [EMAIL PROTECTED]
   WWW: http://www.sigaev.ru/

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

  http://www.postgresql.org/docs/faq

Re: [HACKERS] pg_internal.init is hazardous to your health

[Simon Riggs]

On Tue, 2006-10-17 at 22:29 -0400, Tom Lane wrote:
> Dirk Lutzebaeck and I just spent a tense couple of hours trying to
> figure out why a large database Down Under wasn't coming up after being
> reloaded from a base backup plus PITR recovery.  The symptoms were that
> the recovery went fine, but backend processes would fail at startup or
> soon after with "could not open relation XX/XX/XX: No such file" type of
> errors.

Understand the tension...

> The answer that ultimately emerged was that they'd been running a
> nightly maintenance script that did REINDEX SYSTEM (among other things
> I suppose).  The PITR base backup included pg_internal.init files that
> were appropriate when it was taken, and the PITR recovery process did
> nothing whatsoever to update 'em :-(.  So incoming backends picked up
> init files with obsolete relfilenode values.

OK, I'm looking at this now for later discussion.

-- 
  Simon Riggs 
  EnterpriseDB   http://www.enterprisedb.com



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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Lukas Kahwe Smith]

Neil Conway wrote:


I think a more sensible proposal could be made for some sort of optional
"compatibility mode", as has been discussed many times in the past:
different NULL handling could theoretically be part of an Oracle SQL
dialect.


even more exciting in this context would be to add user controllable 
NULL sorting behaviour. afaik this is in sql:2003.


regards,
Lukas

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Neil Conway]

On Wed, 2006-10-18 at 15:44 +0200, Andreas Joseph Krogh wrote:
> I'm not advocating that NULL should have a string-vaule of anything, just 
> that 
> the ||-operator shuld treat NULL as "dont bother with it and proceed 
> concatenation".

Not only is the current behavior more logical (IMHO) and backward
compatible with existing Postgres application, it is consistent with the
SQL spec and most non-broken implementations of it. I think your chances
of getting the default behavior changed are slim indeed.

I think a more sensible proposal could be made for some sort of optional
"compatibility mode", as has been discussed many times in the past:
different NULL handling could theoretically be part of an Oracle SQL
dialect.

-Neil



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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Csaba Nagy]

On Wed, 2006-10-18 at 16:15, Mario Weilguni wrote:
> Yes it's hard for me, maybe because I am no native english speaker. 

Considering the pure latin origin of the word, that's a funny argument
:-)

BTW, I pronounce it as an Italian would (that would be the closest to
it's origins): "koh-ah-less-cheh" (I'm not sure if I got the sounds
right for native english speakers, I'm also not one of them ;-).

Cheers,
Csaba.



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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

Yes it's hard for me, maybe because I am no native english speaker. 

-Ursprüngliche Nachricht-
Von: Andrew Dunstan [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 18. Oktober 2006 16:11
An: Lukas Kahwe Smith
Cc: Mario Weilguni; pgsql-hackers@postgresql.org
Betreff: Re: [HACKERS] bug or feature, || -operator and NULLs

Lukas Kahwe Smith wrote:
> Mario Weilguni wrote:
>> Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even 
>> harder to type.
>
> amen .. coalesce was invented by a sadistic twit (something which 
> people have also called me .. so it goes).

Perhaps people are trying to pronounce it wrongly. According to m-w, the right 
ways is:

   Pronunciation: "kO-&-'les


or more informally "koh a less".

Is that really so hard?

cheers

andrew



---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] bug or feature, || -operator and NULLs

[Andrew Dunstan]

Lukas Kahwe Smith wrote:

Mario Weilguni wrote:
Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even 
harder to type.


amen .. coalesce was invented by a sadistic twit (something which 
people have also called me .. so it goes).


Perhaps people are trying to pronounce it wrongly. According to m-w, the 
right ways is:


  Pronunciation: "kO-&-'les


or more informally "koh a less".

Is that really so hard?

cheers

andrew



---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] bug or feature, || -operator and NULLs

[Lukas Kahwe Smith]

Mario Weilguni wrote:

Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even harder to 
type.


amen .. coalesce was invented by a sadistic twit (something which people 
have also called me .. so it goes).


regards,
Lukas

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

  http://archives.postgresql.org

Re: [HACKERS] bug or feature, || -operator and NULLs

[Martijn van Oosterhout]

On Wed, Oct 18, 2006 at 03:44:05PM +0200, Andreas Joseph Krogh wrote:
> > When in doubt, consult the standard ... Oracle's treatment of NULL is
> > known to violate the standard, IIRC. Your measure of correctness seems
> > to be "appears to me more logical", but ours is "complies with the
> > standard".
> 
> I know PG violates the standard in other places and core's favourite argument 
> for doing so is "the standard is braindead here, so we do it our way".

But they're few and far between and not on things people actually
notice much.

What's being suggested simply violates common sense. Basically:

if (a = b) then (a||c = b||c)

That seems a perfectly good rule, which works for both Oracle and
PostgreSQL. Breaking seems to be a bad idea all round.

> I'm not advocating that NULL should have a string-vaule of anything, just 
> that 
> the ||-operator shuld treat NULL as "dont bother with it and proceed 
> concatenation".

I would argue it's inconsistant. No other function treats a NULL like
an empty string, so I really don't see why textcat() should.

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] bug or feature, || -operator and NULLs

[Andrew Dunstan]

Andreas Joseph Krogh wrote:

When in doubt, consult the standard ... Oracle's treatment of NULL is
known to violate the standard, IIRC. Your measure of correctness seems
to be "appears to me more logical", but ours is "complies with the
standard".



I know PG violates the standard in other places and core's favourite argument 
for doing so is "the standard is braindead here, so we do it our way".


  


In very few places. If you think that an argument like that will make us 
break well established standards-compliant behaviour, you are surely 
sadly mistaken.


cheers

andrew

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

Nice, but I still prefer nvl. Coalesce is hard to pronounce, and even harder to 
type.

-Ursprüngliche Nachricht-
Von: Andreas Joseph Krogh [mailto:[EMAIL PROTECTED] 
Gesendet: Mittwoch, 18. Oktober 2006 15:48
An: pgsql-hackers@postgresql.org
Cc: Mario Weilguni
Betreff: Re: [HACKERS] bug or feature, || -operator and NULLs

On Wednesday 18 October 2006 15:15, Mario Weilguni wrote:
> >If you want this behaviour you will have to explicitly handle it with
>
> COALESCE().
>
> >regards,
> >Lukas
>
> True. But there's a point where oracle is really better here, they 
> named "coalesce" "nvl" => a lot easier to type ;-)

They actually support COALESCE now and explicit JOINs too.

--
Andreas Joseph Krogh <[EMAIL PROTECTED]> Senior Software Developer / Manager 
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] bug or feature, || -operator and NULLs

[Andreas Joseph Krogh]

On Wednesday 18 October 2006 15:15, Mario Weilguni wrote:
> >If you want this behaviour you will have to explicitly handle it with
>
> COALESCE().
>
> >regards,
> >Lukas
>
> True. But there's a point where oracle is really better here, they named
> "coalesce" "nvl" => a lot easier to type ;-)

They actually support COALESCE now and explicit JOINs too.

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Andreas Joseph Krogh]

On Wednesday 18 October 2006 15:13, Andrew Dunstan wrote:
> Andreas Joseph Krogh wrote:
> > This has been been discussed before, but Oracle behaves differently, and
> > IMHO in a more correct way.
> >
> > The following query returns NULL in PG:
> > SELECT NULL || 'fisk';
> >
> > But in Oracle, it returns 'fisk':
> > SELECT NULL || 'fisk' FROM DUAL;
> >
> > The latter seems more logical...
>
> When in doubt, consult the standard ... Oracle's treatment of NULL is
> known to violate the standard, IIRC. Your measure of correctness seems
> to be "appears to me more logical", but ours is "complies with the
> standard".

I know PG violates the standard in other places and core's favourite argument 
for doing so is "the standard is braindead here, so we do it our way".

> In any case, why should null have a string value of '' any more than it
> should have a value of 'blurfl'?
>
> Your analogy elsewhere with aggregate functions like sum() is not
> relevant, as these are documented to ignore null values.

I'm not advocating that NULL should have a string-vaule of anything, just that 
the ||-operator shuld treat NULL as "dont bother with it and proceed 
concatenation".

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

>
>If you want this behaviour you will have to explicitly handle it with
COALESCE().
>
>regards,
>Lukas

True. But there's a point where oracle is really better here, they named
"coalesce" "nvl" => a lot easier to type ;-)

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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] Bug?

[Andrew Dunstan]

Indira Muthuswamy wrote:

Hai,
 
 I have encountered a problem with PostgreSQL.I have created a table 
'tab1' with a column 'a' with serial type.I entered 20 records into 
the  table.So the query

  select max(a) from tab1;
returned 20.When I tried the same query after the command
 truncate table tab1;
I found that the output of the first query as
 
 max

-

(1 row)

When I tried to insert a new row into the table tab1 I found that the 
value at column a incremented to the value 21.
But I heard from my friends that the value of the serial column gets 
decremented whenever we issue the 'truncate table' command (in MS SQL 
SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify 
me on this?
 


Sure. You heard wrong about postgres.

Please do not ask questions like this on the -hackers list - this should 
have gone to pgsql-general. The hackers list is for postgresql 
development discussions only.


cheers

andrew

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

[HACKERS] Bug?

[Indira Muthuswamy]

Hai,
 
 I have encountered a problem with PostgreSQL.I have created a table 'tab1' with a column 'a' with serial type.I entered 20 records into the  table.So the query
  select max(a) from tab1; 
returned 20.When I tried the same query after the command
 truncate table tab1;
I found that the output of the first query as 
 
 max-

(1 row)
When I tried to insert a new row into the table tab1 I found that the value at column a incremented to the value 21.
But I heard from my friends that the value of the serial column gets decremented whenever we issue the 'truncate table' command (in MS SQL SERVER).Is the concept same with PostgreSQL or not?Can anyone clarify me on this?

 
Thanks in advance,
Regards,
m.Indira

Re: [HACKERS] bug or feature, || -operator and NULLs

[Csaba Nagy]

> Why do these discussions always end in academic arguments over whats more 
> logical then not? 

Because you asked the (rhetorical from your POV) question 'isn't this
more logical ?'

> From a *user's* point of view I really would like it to 
> treat the NULL operand of || as '', and obviously many other (at least 
> Oracle) users tend to agree with me on that.

They have to, otherwise they can't meaningfully concatenate an empty
string to anything in Oracle, because there's no such thing in Oracle...
empty string = NULL in Oracle, which is the real cause of the problem.
We've been bitten by this on Oracle before.

> If aggregates ignore NULL one could argue that so shuld the ||-operator?

OK, this is more complicated I guess, check out the rules related to
'strict' state transition functions in:

http://www.postgresql.org/docs/8.1/static/sql-createaggregate.html

Basically, if you like, you could define a 'my_sum' aggregate which does
not ignore nulls. Or you can define an operator which treats NULLs as
empty string if you like...

Cheers,
Csaba.




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

   http://www.postgresql.org/docs/faq

Re: [HACKERS] bug or feature, || -operator and NULLs

[Andrew Dunstan]

Andreas Joseph Krogh wrote:
This has been been discussed before, but Oracle behaves differently, and IMHO 
in a more correct way.


The following query returns NULL in PG:
SELECT NULL || 'fisk';

But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;

The latter seems more logical...

  


When in doubt, consult the standard ... Oracle's treatment of NULL is 
known to violate the standard, IIRC. Your measure of correctness seems 
to be "appears to me more logical", but ours is "complies with the 
standard".


In any case, why should null have a string value of '' any more than it 
should have a value of 'blurfl'?


Your analogy elsewhere with aggregate functions like sum() is not 
relevant, as these are documented to ignore null values.


cheers

andrew

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

  http://archives.postgresql.org

Re: [HACKERS] bug or feature, || -operator and NULLs

[Alvaro Herrera]

Andreas Joseph Krogh wrote:

> Why do these discussions always end in academic arguments over whats more 
> logical then not? From a *user's* point of view I really would like it to 
> treat the NULL operand of || as '', and obviously many other (at least 
> Oracle) users tend to agree with me on that.

So coalesce the column to the empty string if that's what you want:

select coalesce(NULL, '') || 'fisk'

will get you 'fisk'.

-- 
Alvaro Herrerahttp://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] bug or feature, || -operator and NULLs

[Lukas Kahwe Smith]

Andreas Joseph Krogh wrote:


If aggregates ignore NULL one could argue that so shuld the ||-operator?


I agree that this behaviour may seem pedantic, but changing this is only 
going to lead to a huge wtf? factor. The baviour for NULL in aggregates 
is pretty well documented and known. Even MySQL returns NULL in this 
case, and they are known todo all sorts of changes for better "ease of use".


If you want this behaviour you will have to explicitly handle it with 
COALESCE().


regards,
Lukas


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

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Mirror problems for download

[Michael Paesold]

Magnus Hagander wrote:

Michael Paesold wrote:
I just wanted to download the postgresql-8.0.9 tarball. The 
page I got was this:


Choose a download mirror
Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz
We could not query the database or no mirrors could be found!
Download PostgreSQL from the primary site Read this if you 
would like to host a mirror.


Of course the primary FTP site is already unavailable (530 - 
maximum number of users reached).


I get the same error for older releases, too. Can someone 
look into this?


Thanks for reporting this. It has now been fixed - it was a problem with
the mirror checking script being fooled by a temporary file that
couldn't be removed because it was owned by the wrong user. Db is
updating now, all mirrors should be back in 10 minutes or so.


Thanks for fixing. Works again for me.


(Man, it's convenient with wireless internet on the airplane
somtimes...)


:-)

Best Regards
Michael Paesold


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

  http://www.postgresql.org/docs/faq

Re: [HACKERS] Mirror problems for download

[Magnus Hagander]

> Not being subscribed to any more appropriate list, I post 
> this here on hackers.
> 
> I just wanted to download the postgresql-8.0.9 tarball. The 
> page I got was this:
> 
> Choose a download mirror
> Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz
> We could not query the database or no mirrors could be found!
> Download PostgreSQL from the primary site Read this if you 
> would like to host a mirror.
> 
> Of course the primary FTP site is already unavailable (530 - 
> maximum number of users reached).
> 
> I get the same error for older releases, too. Can someone 
> look into this?

Thanks for reporting this. It has now been fixed - it was a problem with
the mirror checking script being fooled by a temporary file that
couldn't be removed because it was owned by the wrong user. Db is
updating now, all mirrors should be back in 10 minutes or so.

(Man, it's convenient with wireless internet on the airplane
somtimes...)

//Magnus

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] Mirror problems for download

[Michael Paesold]

Shane Ambler wrote:

Michael Paesold wrote:
Not being subscribed to any more appropriate list, I post this here on 
hackers.


I just wanted to download the postgresql-8.0.9 tarball. The page I got 
was this:


Choose a download mirror
Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz
We could not query the database or no mirrors could be found!
Download PostgreSQL from the primary site
Read this if you would like to host a mirror.

Of course the primary FTP site is already unavailable (530 - maximum 
number of users reached).


I get the same error for older releases, too. Can someone look into this?



Your seeing a general overload problem - with new versions just released 
everyone is jumping on and downloading at the same time.


This message (see above) does not look just like an overload problem, no?
>> We could not query the database or no mirrors could be found!

Best Regards
Michael Paesold


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] bug or feature, || -operator and NULLs

[Mario Weilguni]

Am Mittwoch, 18. Oktober 2006 13:52 schrieb Andreas Joseph Krogh:
> This has been been discussed before, but Oracle behaves differently, and
> IMHO in a more correct way.
>
> The following query returns NULL in PG:
> SELECT NULL || 'fisk';
>
> But in Oracle, it returns 'fisk':
> SELECT NULL || 'fisk' FROM DUAL;
>
> The latter seems more logical...

I've worked alot with oracle a few years ago and I agree, the feature is handy 
and makes sometimes life easier, but it's simply wrong. I heard a while ago 
that newer oracle versions changed this to sql - standard, is this true?


---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] +1400 is a valid time zone offset

[Martijn van Oosterhout]

On Wed, Oct 18, 2006 at 02:52:35PM +0530, Abhijit Menon-Sen wrote:
> In 1995, Kiribati decided to move the international date line, so that
> all of the constituent islands lie on the same side. As a consequence,
> the Easternmost islands are now at GMT+1400.
> 
> (There's some controvery about whether they had the right to move the
> IDL, but they have the right to decide what time zone they're in. :-)

Not a big deal.

> http://www.google.com/search?q=1995+%22International+Date+Line%22+Kiribati
> 
> Would it be painful to make Postgres accept +1400?

I doubt it. We support +1300 already, and we also support timezone
'Pacific/Kiritimati' which is at +1400, so I doubt it's an issue.

Wanna send a patch?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] bug or feature, || -operator and NULLs

[Lukas Kahwe Smith]

Martijn van Oosterhout wrote:


By following your suggestion we would get the following oddity:

SELECT NULL = '', NULL || 'fisk' = '' || 'fisk';

We would return NULL for the first and true for the second. Surely
that's not logical?


The problem is really that Oracle does not differntiate properly between 
'' and NULL.


regards,
Lukas

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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Csaba Nagy]

> How many times do you *really* want to get the "not known" answer here 
> instead 
> of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?

All the time. If I would want the answer 'fisk', I would store ''
instead of NULL... your problem is that Oracle treats NULL as '' (empty
string), so even if you insert an empty string it will end up as NULL,
that's why they HAVE TO give the result you say it's more logical.

> That's like saying: SELECT sum(field) should return NULL(value not known) if 
> some of the tuples are NULL, which is definitly not what you want.

And it would really return null, if aggregates wouldn't ignore the NULL
values altogether... the null values are skipped before they get into
the summing. The same happens with count, if you specify a column it
will only count the ones which are not null:

cnagy=# create table test_null(a integer);
CREATE TABLE
cnagy=# insert into test_null values (1);
INSERT 0 1
cnagy=# insert into test_null values (null);
INSERT 0 1
cnagy=# insert into test_null values (2);
INSERT 0 1
cnagy=# select sum(a) from test_null;
 sum
-
   3
(1 row)
 
cnagy=# select count(a) from test_null;
 count
---
 2
(1 row)

But:

cnagy=# select (1 + 2 + null) is null;
 ?column?
--
 t
(1 row)

Cheers,
Csaba.



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

Re: [HACKERS] bug or feature, || -operator and NULLs

[Martijn van Oosterhout]

On Wed, Oct 18, 2006 at 02:28:50PM +0200, Andreas Joseph Krogh wrote:
> On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
> > > The following query returns NULL in PG:
> > > SELECT NULL || 'fisk';
> > >
> > > But in Oracle, it returns 'fisk':
> > > SELECT NULL || 'fisk' FROM DUAL;
> > >
> > > The latter seems more logical...

> How many times do you *really* want to get the "not known" answer here 
> instead 
> of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?

In general, if you pass a NULL to a function, you get a NULL return. An
operator is just a function call.

IIRC, this works on oracle too:

SELECT NULL = '';

returns true. On postgresql it return null (sql standard).

By following your suggestion we would get the following oddity:

SELECT NULL = '', NULL || 'fisk' = '' || 'fisk';

We would return NULL for the first and true for the second. Surely
that's not logical?

Have a nice day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

Re: [HACKERS] bug or feature, || -operator and NULLs

[Andreas Joseph Krogh]

On Wednesday 18 October 2006 14:15, Csaba Nagy wrote:
> > The following query returns NULL in PG:
> > SELECT NULL || 'fisk';
> >
> > But in Oracle, it returns 'fisk':
> > SELECT NULL || 'fisk' FROM DUAL;
> >
> > The latter seems more logical...
>
> Why would it be more logical ?

How many times do you *really* want to get the "not known" answer here instead 
of 'fisk'? To put it another way: When will it be *wrong* to return 'fisk'?

> NULL means "value not known".

I know.

> Concatenate "value not known" with 'fisk' -> what's the logical answer?
>
> I would say the logical result is 'value not known'... if one of the
> components is not known, how can you know what is the result ?

That's like saying: SELECT sum(field) should return NULL(value not known) if 
some of the tuples are NULL, which is definitly not what you want.

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

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

Re: [HACKERS] Asynchronous I/O Support

[Martijn van Oosterhout]

On Wed, Oct 18, 2006 at 08:04:29PM +1300, Mark Kirkwood wrote:
> >"bgwriter doing aysncronous I/O for the dirty buffers that it is 
> >supposed to sync"
> >Another decent use-case?

Good idea, but async i/o is generally poorly supported.

> Is it worth considering using readv(2) instead?

Err, readv allows you to split a single consecutive read into multiple
buffers. Doesn't help at all for reads on widely areas of a file.

Have a ncie day,
-- 
Martijn van Oosterhout  http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to 
> litigate.


signature.asc
Description: Digital signature

[HACKERS] bug or feature, || -operator and NULLs

[Andreas Joseph Krogh]

This has been been discussed before, but Oracle behaves differently, and IMHO 
in a more correct way.

The following query returns NULL in PG:
SELECT NULL || 'fisk';

But in Oracle, it returns 'fisk':
SELECT NULL || 'fisk' FROM DUAL;

The latter seems more logical...

-- 
Andreas Joseph Krogh <[EMAIL PROTECTED]>
Senior Software Developer / Manager
gpg public_key: http://dev.officenet.no/~andreak/public_key.asc
+-+
OfficeNet AS| The most difficult thing in the world is to |
Karenslyst Allé 11  | know how to do a thing and to watch |
PO. Box 529 Skøyen  | somebody else doing it wrong, without   |
0214 Oslo   | comment.|
NORWAY  | |
Mobile: +47 909  56 963 | |
+-+

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[Magnus Hagander]

> >> Hmm.  If the messages are less than PIPE_BUF bytes long 
> (4096 bytes 
> >> on
> >> Linux) then the writes are supposed to be atomic.
> 
> > Some of them involve long messages (>4K), but there are 
> many that do 
> > not (like the ones I had posted at the start of this thread).
> 
> I checked around with some kernel/glibc gurus in Red Hat, and 
> the consensus seemed to be that we'd be better off to bypass 
> fprintf() and just send message strings to stderr using 
> write() --- ie, instead of elog.c doing
> 
> fprintf(stderr, "%s", buf.data);
> 
> do
> 
> write(fileno(stderr), buf.data, strlen(buf.data));
> 
> Anyone have any comments on possible portability risks?  In 
> particular, will this work on Windows?

Should work fine on Windows. fileno() is deprecated however, with the
following comment:
C:\Program Files\Microsoft Visual Studio
8\VC\INCLUDE\stdio.h(688) : see
 declaration of 'fileno'
Message: 'The POSIX name for this item is deprecated. Instead,
use the ISO C++ conformant name: _fileno. See online help for details.'


It still works, and there is a define to get around that warning though,
so it's definitly not critical.


//Magnus

---(end of broadcast)---
TIP 6: explain analyze is your friend

Re: [HACKERS] Mirror problems for download

[Shane Ambler]

Michael Paesold wrote:
Not being subscribed to any more appropriate list, I post this here on 
hackers.


I just wanted to download the postgresql-8.0.9 tarball. The page I got 
was this:


Choose a download mirror
Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz
We could not query the database or no mirrors could be found!
Download PostgreSQL from the primary site
Read this if you would like to host a mirror.

Of course the primary FTP site is already unavailable (530 - maximum 
number of users reached).


I get the same error for older releases, too. Can someone look into this?



Your seeing a general overload problem - with new versions just released 
everyone is jumping on and downloading at the same time.


Indicates that the popularity of PostgreSQL is at a level where the 
current resources are reaching thier limits and more mirror sites may be 
needed.


I haven't noticed any torrent files for postgreSQL releases - maybe we 
can look into setting this up to share the load, particularly around 
release times.



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

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

  http://archives.postgresql.org

Re: [HACKERS] Syntax bug? Group by?

[Przemek]

Dnia 17-10-2006 o godz. 23:21 Tom Lane napisał(a):
> Andrew Dunstan <[EMAIL PROTECTED]> writes:
> > Mark Woodward wrote:
> >> My question, is it a syntactic technicality that PostgreSQL asks for a
> >> "group by," or a bug in the parser?
> 
> > AFAIK what you want is not per sql spec.
> 
> It would in fact be a violation of spec.  Consider the case where there
> are no rows matching 15.  In this case
> 
> select min(tindex), avg(tindex) from y where ycis_id = 15;
> 
> will yield one row containing NULLs, whereas
> 
> select min(tindex), avg(tindex) from y where ycis_id = 15 group by 
> ycis_id;
> 
> will yield no rows (because there are no groups).  Therefore, if
> Postgres were to implicitly insert a GROUP BY to make it legal to
> reference ycis_id directly, we'd be changing the query behavior
> and breaking spec.
> 
>   regards, tom lane
> 


Hello

IMHO:

Also take into consider that - what should be expected behavior of this:

select ycis_id, min(tindex), avg(tindex) from y where ycis_id = 
truncate(random()*100);

Since result of comparission is not known on parsing phase it couldn't be done 
here if there is no other requirements on argum



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

   http://www.postgresql.org/docs/faq

[HACKERS] Bitmap index status

[Heikki Linnakangas]

Hi,

I don't want to harass you :), but what's the status with the bitmap 
index code? Is there something I can do to help?


--
  Heikki Linnakangas
  EnterpriseDB   http://www.enterprisedb.com

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
  choose an index scan if your joining column's datatypes do not
  match

[HACKERS] +1400 is a valid time zone offset

[Abhijit Menon-Sen]

In 1995, Kiribati decided to move the international date line, so that
all of the constituent islands lie on the same side. As a consequence,
the Easternmost islands are now at GMT+1400.

(There's some controvery about whether they had the right to move the
IDL, but they have the right to decide what time zone they're in. :-)

http://www.google.com/search?q=1995+%22International+Date+Line%22+Kiribati

Would it be painful to make Postgres accept +1400?

-- ams

---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
   choose an index scan if your joining column's datatypes do not
   match

[HACKERS] Mirror problems for download

[Michael Paesold]

Not being subscribed to any more appropriate list, I post this here on 
hackers.


I just wanted to download the postgresql-8.0.9 tarball. The page I got 
was this:


Choose a download mirror
Downloading: /source/v8.0.9/postgresql-8.0.9.tar.gz
We could not query the database or no mirrors could be found!
Download PostgreSQL from the primary site
Read this if you would like to host a mirror.

Of course the primary FTP site is already unavailable (530 - maximum 
number of users reached).


I get the same error for older releases, too. Can someone look into this?

Best Regards
Michael Paesold

[ CC: to [EMAIL PROTECTED] ]


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

  http://www.postgresql.org/docs/faq

Re: [HACKERS] 8.1.4 verified on Intel Mac OS 10.4.8

[Shane Ambler]

Douglas Toltzman wrote:

The subject line says it all.

I just completed a build, test, and install of PostgreSQL 8.1.4 on an 
Intel Mac with OS 10.4.8.  Every single step executed flawlessly, and 
all 98 tests passed, running the regression tests.


Just for info - there is an automated build farm that tests building 
each version on different systems. It is at http://pgbuildfarm.org
You can view the results page and see what systems have compiled each 
version of postgres and what results they got.

A machine with OSX 10.4.7 on Intel is in the farm.
If you are interested in joining the build farm and have your machine 
regularly building and sending feedback then you can find details at the 
site.


I've got a suggestion for the documentation (INSTALL file).  I may be 
the only person in the world who does everything backwards, but my first 
attempt at regression tests failed because I had already started the 
postmaster.  I know that's pretty stupid, but I had to scratch my head 
for a few minutes when it said initdb failed, and left me no clue as to 
why.  You might add a note in with the instructions for regression 
testing to shutdown the server before running the tests.  It does say, 
after all, that you can run them any time!


I have seen this gotcha several times but haven't pinpointed why. (I may 
sit down and nut it out one day)


With no postgres running there is no problem but with a version of 
postgres running I have found the results vary.

One day I can make check and have a problem the next it will run fine.
(today it is working(day before yesterday make check on 8.1.5 didn't))


p.s. I noticed I am a build behind, but I would think that 8.1.5 would give 
similar results.


As 8.1.5 was only released a couple of days ago I wouldn't say you are 
behind yet.



--

Shane Ambler
[EMAIL PROTECTED]

Get Sheeky @ http://Sheeky.Biz

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings

Re: [HACKERS] [GENERAL] query log corrupted-looking entries

[Albe Laurenz]

Tom Lane wrote:
> I checked around with some kernel/glibc gurus in Red Hat, and the
> consensus seemed to be that we'd be better off to bypass fprintf() and
> just send message strings to stderr using write() --- ie, instead of
> elog.c doing
> 
> fprintf(stderr, "%s", buf.data);
> 
> do
> 
> write(fileno(stderr), buf.data, strlen(buf.data));
> 
> Anyone have any comments on possible portability risks?  In 
> particular, will this work on Windows?

The following program compiles and runs fine:

#include 
#include 
#include 

int main(int argc, char **argv) {
const char *s="Hello!\n";

write(fileno(stderr), s, strlen(s));
return 0;
}

Yours,
Laurenz Albe

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

Re: [HACKERS] Asynchronous I/O Support

[Mark Kirkwood]

NikhilS wrote:

Hi,

"bgwriter doing aysncronous I/O for the dirty buffers that it is 
supposed to sync"

Another decent use-case?

Regards,
Nikhils
EnterpriseDB   http://www.enterprisedb.com

On 10/15/06, *Luke Lonergan* <[EMAIL PROTECTED] 
> wrote:


Martijn,

On 10/15/06 10:56 AM, "Martijn van Oosterhout" mailto:kleptog@svana.org>> wrote:

 > Have enough systems actually got to the point of actually supporting
 > async I/O that it's worth implementing?

I think there are enough high end applications / systems that need it at
this point.

The killer use-case we've identified is for the scattered I/O
associated
with index + heap scans in Postgres.  If we can issue ~5-15 I/Os in
advance
when the TIDs are widely separated it has the potential to increase
the I/O
speed by the number of disks in the tablespace being scanned.  At this
point, that pattern will only use one disk.



Is it worth considering using readv(2) instead?

Cheers

Mark

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