Re: Extensions, patch v19 (encoding brainfart fix) (was: [HACKERS] Extensions, patch v18 (merge against master, bitrot-only-fixes))

[Itagaki Takahiro]

On Fri, Dec 17, 2010 at 02:00, Dimitri Fontaine  wrote:
> So, attached patch fixes the v18 regression wrt to script file encoding
> and establish UTF-8 as the default encoding to consider to read a script
> file. Thanks for your comments.

You probably compared wrong versions of source trees. The patch contains
many diffs not related to EXTENSION. It cannot be applied cleanly.

BTW, only earthdistance.sql.in has @extsch...@.
Didn't you forget to remove it?

--- b/contrib/earthdistance/earthdistance.sql.in
! SET search_path = @extschema@;


I've not read the patch well yet, but I'd like to make sure of
two specs in the patch:

#1. I found the patch specifies "version" in each control file. We will
need to maintain them manually, but I assume it was a conclusion in the
discussion for v18 patch. So, no more changes are required here.

--- b/contrib/XXX/XXX.control
+ version = '9.1devel'

#2. The patch replaces "\i XXX.sql" to "CREATE EXTENSION XXX". They are a
bit different because CREATE EXTENSION uses the installed sql files instead
of the source directory. But I think this is the correct behavior. We should
have used only installed files because they are used in "make *installcheck*".

*** a/contrib/XXX/sql/XXX.sql
***
  SET client_min_messages = warning;
! \set ECHO none
! \i XXX.sql
! \set ECHO all
  RESET client_min_messages;
--- 7,13 
  SET client_min_messages = warning;
! CREATE EXTENSION XXX;
  RESET client_min_messages;

-- 
Itagaki Takahiro

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

Re: [HACKERS] proposal : cross-column stats

[tv]

> On Dec17, 2010, at 23:12 , Tomas Vondra wrote:
>> Well, not really - I haven't done any experiments with it. For two
>> columns selectivity equation is
>>
>>  (dist(A) * sel(A) + dist(B) * sel(B)) / (2 * dist(A,B))
>>
>> where A and B are columns, dist(X) is number of distinct values in
>> column X and sel(X) is selectivity of column X.
>
> Huh? This is the selectivity estimate for "A = x AND B = y"? Surely,
> if A and B are independent, the formula must reduce to sel(A) * sel(B),
> and I cannot see how that'd work with the formula above.

Yes, it's a selectivity estimate for P(A=a and B=b). It's based on
conditional probability, as

   P(A=a and B=b) = P(A=a|B=b)*P(B=b) = P(B=b|A=a)*P(A=a)

and "uniform correlation" assumption so that it's possible to replace the
conditional probabilities with constants. And those constants are then
estimated as dist(A)/dist(A,B) or dist(B)/dist(A,B).

So it does not reduce to sel(A)*sel(B) exactly, as the dist(A)/dist(A,B)
is just an estimate of P(B|A). The paper states that this works best for
highly correlated data, while for low correlated data it (at least)
matches the usual estimates.

I don't say it's perfect, but it seems to produce reasonable estimates.

Tomas


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

Re: [HACKERS] plperlu problem with utf8

[Alex Hunsaker]

On Fri, Dec 17, 2010 at 18:04, David E. Wheeler  wrote:
> On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:

> Yeah. So I just wrote and tested this function on 9.0 with Perl 5.12.2:
>
>    CREATE OR REPLACE FUNCTION perlgets(
>        TEXT
>    ) RETURNS TABLE(length INT, is_utf8 BOOL) LANGUAGE plperl AS $$
>       my $text = shift;
>       return_next {
>           length  => length $text,
>           is_utf8 => utf8::is_utf8($text) ? 1 : 0
>       };
>    $$;
>
> In a utf-8 database:
>
>    utf8=# select * from perlgets('foo');
>     length │ is_utf8
>    ┼─
>          8 │ t
>    (1 row)
>
>
> In a latin-1 database:
>
>    latin=# select * from perlgets('foo');
>     length │ is_utf8
>    ┼─
>          8 │ f
>    (1 row)
>
> I would argue that in the latter case, is_utf8 should be true, too. That is, 
> PL/Perl should decode from Latin-1 to Perl's internal form.

Just to reiterate in a different way what  David C. said, the flag is
irrelevant in this case. Begin set on that input string is the same as
it not being set.

per perldoc perlunicode:
  The "UTF8" flag being on does not mean that there are any characters
of code points greater than 255 (or 127) in the scalar or that there
are even any characters in the scalar.  What the "UTF8" flag means is
that the sequence of octets in the representation of the scalar is the
sequence of UTF-8 encoded code points of the characters of a string.
The "UTF8" flag being off means that each octet in this representation
encodes a single character with code point 0..255 within the string.

Basically perl has *2* internal forms and certain strings can be
represented in both.

> Interestingly, when I created a function that takes a bytea argument, utf8 
> was *still* enabled in the utf-8 database. That doesn't seem right to me.

Hrm, yeah that seems bogus.  Ill have to play with that more.

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

Re: [HACKERS] plperlu problem with utf8

[Alex Hunsaker]

On Fri, Dec 17, 2010 at 18:22, David E. Wheeler  wrote:
> On Dec 17, 2010, at 5:04 PM, David E. Wheeler wrote:
>
>>> see? Either uri_unescape() should be decoding that utf8() or you need
>>> to do it *after* you call uri_unescape().  Hence the maybe it could be
>>> considered a bug in uri_unescape().
>>
>> Agreed.
>
> On second thought, no. You can in fact encode anything in a URI. URI::Escape 
> can't know what to decode to. So *I believe* it just unescapes the raw bytes. 
> It might be handy for it to have a new function, though, to complement its 
> uri_escape_utf() function:
>
>    sub uri_unescape_utf8 { Encode::decode_utf8(uri_unescape(@_)) }
>
> Just to make things a bit clearer.
>
> But that's a separate issue from the, erm, inconsistency with which PL/Perl 
> treats encoding and decoding of its inputs and outputs.

Yay! So I think we can finally agree that for Oleg's original test
case postgres was getting right.  I hope ? :)

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

Re: [HACKERS] plperlu problem with utf8

[Alex Hunsaker]

On Fri, Dec 17, 2010 at 22:32, David Christensen  wrote:
>
> On Dec 17, 2010, at 7:04 PM, David E. Wheeler wrote:
>
>> On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:
>>
 No, URI::Escape is fine. The issue is that if you don't decode text to 
 Perl's internal form, it assumes that it's Latin-1.
>>>
>>> So... you are saying "\xc3\xa9" eq "\xe9" or chr(233) ?
>>
>> Not knowing what those mean, I'm not saying either one, to my knowledge. 
>> What I understand, however, is that Perl, given a scalar with bytes in it, 
>> will treat it as latin-1 unless the utf8 flag is turned on.
>
> This is a correct assertion as to Perl's behavior.  As far as PostgreSQL 
> is/should be concerned in this case, this is the correct handling for 
> URI::Escape,

Right, so no postgres bug here..  Postgres showing é instead of é is
right as far as its concerned.

>> PostgreSQL should do everything it can to decode to Perl's internal format 
>> before passing arguments, and to decode from Perl's internal format on 
>> output.
>
> +1 on the original sentiment, but only for the case that we're dealing with 
> data that is passed in/out as arguments.  In the case that the 
> server_encoding is UTF-8, this is as trivial as a few macros on the 
> underlying SVs for text-like types.  If the server_encoding is SQL_ASCII (= 
> byte soup), this is a trivial case of doing nothing with the conversion 
> regardless of data type.

Right and thats what we do for the above.  Minus some mis-handling of
non character datatypes like bytea in the UTF-8 case.

> For any other server_encoding, the data would need to be converted from the 
> server_encoding to UTF-8, presumably using the built-in conversions before 
> passing it off to the first code path.  A similar handling would need to be 
> done for the return values, again datatype-dependent.

Yeah, thats what we *should* do.  Right now we just leave it as byte
soup for the user to decode/encode. :(

> [ correctness of perl character ops in the non utf8 case] One thought I had 
> was that we could expose the server_encoding to the plperl interpreters in a 
> special variable to make it easy to explicitly decode...

Should not need to do anything as complicated as that. Can just encode
the string to utf8 before we hand it off to perl.

[...]
> $ perl -MURI::Escape -e'print 
> length(uri_unescape(q{comment%20passer%20le%20r%C3%A9veillon}))'
> 28
>
> $ perl -MEncode -MURI::Escape -e'print 
> length(decode_utf8(uri_unescape(q{comment%20passer%20le%20r%C3%A9veillon})))'
> 27
[...]
> As shown above, the character length for the example should be 27, while the 
> octet length for the UTF-8 encoded version is 28.  I've reviewed the source 
> of URI::Escape, and can say definitively that: a) regular uri_escape does not 
> handle > 255 code points in the encoding, but there exists a uri_escape_utf8 
> which will convert the source string to UTF8 first and then escape the 
> encoded value, and

And why should it? properly escaped URIs should have all those
escaped, I imagine.  Anyway not really relevant for postgres.

> b) uri_unescape has *no* logic in it to automatically decode from UTF8 into 
> perl's internal format (at least as far as the version that I'm looking at, 
> which came with 5.10.1).

>>> Either uri_unescape() should be decoding that utf8() or you need
>>> to do it *after* you call uri_unescape().  Hence the maybe it could be
>>> considered a bug in uri_unescape().
>>
>> Agreed.
>
> -1; if you need to decode from an octets-only encoding, it's your 
> responsibility to do so after you've unescaped it.

-1? thats basically what I said:  "... you need to do it (decode the
utf8) *after* you call uri_unescape"

>  Perhaps later versions of the URI::Escape module contain a 
> uri_unescape_utf8() function, but it's trivially: sub uri_unescape_utf8 { 
> Encode::decode_utf8(uri_unescape(shift))}.  This is definitely not a bug in 
> uri_escape, as it is only defined to return octets.

Ahh So -1 because I said maybe you could call it a bug in
uri_unescape(). Really, I was only saying you *might* be able to
consider it a bug-- or perhaps deficiency is a better word, in
uri_unescape iff URI's are defined to have escaped characters as a %
escaped utf8 sequence.  I dont know that they do, so I don't know if
its a bug :)

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

Re: [HACKERS] proposal : cross-column stats

[Florian Pflug]

On Dec17, 2010, at 23:12 , Tomas Vondra wrote:
> Well, not really - I haven't done any experiments with it. For two
> columns selectivity equation is
> 
>  (dist(A) * sel(A) + dist(B) * sel(B)) / (2 * dist(A,B))
> 
> where A and B are columns, dist(X) is number of distinct values in
> column X and sel(X) is selectivity of column X.

Huh? This is the selectivity estimate for "A = x AND B = y"? Surely,
if A and B are independent, the formula must reduce to sel(A) * sel(B),
and I cannot see how that'd work with the formula above.

best regards,
Florian Pflug


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

Re: [HACKERS] plperlu problem with utf8

[David Christensen]

On Dec 17, 2010, at 7:04 PM, David E. Wheeler wrote:

> On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:
> 
>>> No, URI::Escape is fine. The issue is that if you don't decode text to 
>>> Perl's internal form, it assumes that it's Latin-1.
>> 
>> So... you are saying "\xc3\xa9" eq "\xe9" or chr(233) ?
> 
> Not knowing what those mean, I'm not saying either one, to my knowledge. What 
> I understand, however, is that Perl, given a scalar with bytes in it, will 
> treat it as latin-1 unless the utf8 flag is turned on.

This is a correct assertion as to Perl's behavior.  As far as PostgreSQL 
is/should be concerned in this case, this is the correct handling for 
URI::Escape, as the input string to the function was all ASCII (= valid UTF-8), 
so the function author would need to be responsible for the proper conversion 
to the internal encoding.  This would just be a simple decode_utf8() call in 
the case that you've URI-escaped UTF-8-encoded unicode, however since URI 
escaping is only defined for octets, the meaning of those unescaped octets is 
detached from their encoding.  There are similar issues with using other 
modules which traditionally have not distinguished between characters and bytes 
(as an examples Digest::MD5); Digest::MD5 does not work on wide characters, as 
the algorithm only deals with octets, so you need to pick a target encoding for 
wide characters and encode the octets themselves rather than the characters.

>> Im saying they are not, and if you want \xc3\xa9 to be treated as
>> chr(233) you need to tell perl what encoding the string is in (err
>> well actually decode it so its in "perl space" as unicode characters
>> correctly).
> 
> PostgreSQL should do everything it can to decode to Perl's internal format 
> before passing arguments, and to decode from Perl's internal format on output.

+1 on the original sentiment, but only for the case that we're dealing with 
data that is passed in/out as arguments.  In the case that the server_encoding 
is UTF-8, this is as trivial as a few macros on the underlying SVs for 
text-like types.  If the server_encoding is SQL_ASCII (= byte soup), this is a 
trivial case of doing nothing with the conversion regardless of data type.  For 
any other server_encoding, the data would need to be converted from the 
server_encoding to UTF-8, presumably using the built-in conversions before 
passing it off to the first code path.  A similar handling would need to be 
done for the return values, again datatype-dependent.

This certainly seems like it could be inefficient in the case that we're using 
a non-utf8 server_encoding (there are people who do?? :-P), however from the 
standpoint of correctness, any plperl function that deals with this data as 
characters (using common things like regexes, length, ord, chr, substring, \w 
metachars) will have the potential of operating incorrectly when provided with 
data that is in a different encoding than perl's internal format.  One thought 
I had was that we could expose the server_encoding to the plperl interpreters 
in a special variable to make it easy to explicitly decode if we needed, but 
the problems with this are: a) there's no guarantee that Encode.pm will have a 
alias/support for the specific server_encoding name as provided by Pg, and b) 
in the case of plperl (i.e., not u), there are horrendous issues when trying to 
deal with Safe.pm and character encoding.  Recent upgrades of the Encode module 
included with perl 5.10+ have caused issues wherein circular dependencies 
between Encode and Encode::Alias have made it impossible to load in a Safe 
container without major pain.  (There may be some better options than I'd had 
on a previous project, given that we're embedding our own interpreters and 
accessing more through the XS guts, so I'm not ruling out this possibility 
completely).

Perhaps we could set a function attribute or similar which indicated that we 
wanted to decode the input properly on input, whether or not this should be the 
default, or at the very least expose a function to the plperl[u]? runtime that 
would decode/upgrade on demand without the caller of the function needing to 
know the encoding of the database it is running in.  This would solve issue a), 
because any supported server_encoding would have an internal conversion to 
utf8, and solves b) because we're avoiding the conversion from inside Safe and 
simply running our XS function on the input data.  (As much as I hate the 
ugliness of it, if we decide the decoding behavior shouldn't be the default we 
could even use one of those ugly function pragmas in the function bodies.)

>>> Maybe I'm misunderstanding, but it seems to me that:
>>> 
>>> * String arguments passed to PL/Perl functions should be decoded from the 
>>> server encoding to Perl's internal representation before the function 
>>> actually gets them.
>> 
>> Currently postgres has 2 behaviors:
>> 1) If the database is utf8, turn on the utf8 flag. According to 

Re: [HACKERS] unlogged tables

[Robert Haas]

Here's an attempt to summarize the remaining issues with this patch
that I know about.  I may have forgotten something, so please mention
it if you notice something missing.

1. pg_dump needs an option to control whether unlogged tables are
dumped.  --no-unlogged-tables seems like the obvious choice, assuming
we want the default to be to dump them, which seems like the safest
option.

2. storage.sgml likely needs to be updated.  We have a section on the
free space map and one on the visibility map, so I suppose the logical
thing to do is add a similar section on the initialization fork.

3. It's unnecessary to include unlogged relation buffers in
non-shutdown checkpoints.  I've recently realized that this is true
independently of whether or not we want unlogged tables to survive a
clean shutdown.  Whether or not we can survive a clean shutdown is a
function of whether we register dirty segments when buffers are
written, which is independent of whether we choose to write such
buffers as part of a checkpoint.  And indeed, unless we're about to
shut down, there's no reason to do so, because the whole point of
checkpointing is to advance the redo pointer, and that's irrelevant
for unlogged tables.

4. It's arguably unnecessary to register dirty segments for unlogged
relations.  Given #3, this now seems a little less important.  If the
unlogged relation is hot and fits in shared_buffers, then omitting it
from the checkpoint process means we'll never write out those dirty
buffers, so the fact that they'd cause fsyncs if we did write them
doesn't matter.  However, it's still not totally irrelevant, because a
relation that fits in the OS buffer cache but not in shared buffers
will probably generate fsyncs at every checkpoint.  (And on the third
hand, the OS may decide to write the dirty data anyway, especially if
it's a largish percentage of RAM.)  There are a couple of possible
ways of dealing with this:

4A. The solution Andres proposed - Iterate through all unlogged
relations at shutdown time and fsyncing them all.  Possibly
complicated to handle fsync failures.
4B. Another idea I just thought of - register dirty segments as
normal, but teach the background writer to accumulate them in a
separate queue that is only flushed at shutdown, or when it reaches
some maximum size, rather than at every checkpoint.
4C. Decree that this is an area for future enhancement and forget
about it for now.  I am leaning toward this option.

5. Make it work with GIST indexes.  Per discussion on the other
thread, the current proposal seems to be: (a) add a BM_FLUSH_XLOG bit;
when clear, don't flush XLOG; this then allows pages to have fake
LSNs; (b) add an XLogRecPtr structure in shared memory, protected by a
spinlock; (c) use the structure described in (b) to generate fake LSNs
every time an operation is performed on an unlogged GIST index.  I am
not clear on how we make this work across shutdowns - it seems you'd
need to save this structure somewhere during a clean shutdown (where?)
and restore it on startup, unless we go back to truncating even on a
clean shutdown.

6. Make it work with GIN indexes.  I haven't looked at what's involved here yet.

Advice, comments, feedback appreciated...  I'd like to put this one to bed.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] psql expanded auto

[Itagaki Takahiro]

On Sat, Dec 18, 2010 at 07:12, Peter Eisentraut  wrote:
> I have often found myself wanting that psql automatically switch between
> normal and \x mode depending on the width of the output.  Would others
> find this useful?

+1

> Attached is a crude demo patch.  Enable with \pset expanded auto.

How about adding not only "auto" but also "on" and "off" as arguments
for \x?  If not specified, the default behavior will be still "toggle".

-- 
Itagaki Takahiro

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

Re: [HACKERS] SQL/MED - file_fdw

[Itagaki Takahiro]

On Fri, Dec 17, 2010 at 11:49, Shigeru HANADA  wrote:
> I've just moved permission check and read-only check from BeginCopy()
> to DoCopy().  Please see attached patch.

Thanks!

Are there any objections for the change? If acceptable,
I'd like to apply it prior to SQL/MED and file_fdw patches.

We could have some better name for Begin/Next/EndCopyFrom() and
the exported CopyState. As Shigeru mentioned, COPY FROM consists of
"a file reader" and "a heap inserter", but file_fdw only uses the
file reader. In addition, we could split CopyState into two versions
for COPY FROM and COPY TO later. So, it might be better to export
them as "FileReader API" or some similar names rather than CopyFrom.

-- 
Itagaki Takahiro

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

Re: [HACKERS] Tab completion for ALTER ... SET SCHEMA

[Robert Haas]

On Fri, Dec 17, 2010 at 9:47 PM, Andreas Karlsson  wrote:
> Sorry, everyone.
>
> Ignore my patch. This was already fixed in HEAD and while I wrote my
> patch for the HEAD I somehow failed to spot that it was already fixed
> when testing without my patch.
>
> Nice that is fixed, and sorry for the noise.

No sweat.  It's sort of weird the way it's set up.  Apparently we
complete with a list of schemas any time the previous word is SCHEMA.

rhaas=# bumble schema 
information_schema  pg_temp_1   pg_toast_temp_1
pg_catalog  pg_toastpublic

I guess that makes sense but the name words_after_create certainly
gives one the wrong impression about how it's actually used.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Tab completion for ALTER ... SET SCHEMA

[Andreas Karlsson]

Sorry, everyone.

Ignore my patch. This was already fixed in HEAD and while I wrote my
patch for the HEAD I somehow failed to spot that it was already fixed
when testing without my patch.

Nice that is fixed, and sorry for the noise.

Andreas



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

Re: [HACKERS] Tab completion for ALTER ... SET SCHEMA

[Robert Haas]

On Fri, Dec 17, 2010 at 9:30 PM, Andreas Karlsson  wrote:
> On Fri, 2010-12-17 at 21:20 -0500, Robert Haas wrote:
>> On Fri, Dec 17, 2010 at 9:14 PM, Andreas Karlsson  wrote:
>> > What it does is gets rid of the incorrect completion which comes from
>> > the completion rule for "SET foo TO bar" by adding the correct
>> > completion for "SET SCHEMA" higher up in the completion function.
>> >
>> > So instead of an incorrect completion we get the correct one.
>>
>> But that's not necessarily wrong, if "foo" happens to be the name of a GUC.
>
> Agreed, which is why I made the new rule only match
>
> ALTER x x SET SCHEMA
>
> while the rest will fall down and match
>
> SET
>
> .  So I should be safe since SCHEMA is a reserved words. When I think of
> it I may even have be unnecessary to require the ALTER verb.

Hmm.  Using 9.1devel, if I type ALTER TABLE foo SET SCHEMA , I
get a list of schemas even without this patch.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Tab completion for ALTER ... SET SCHEMA

[Andreas Karlsson]

On Fri, 2010-12-17 at 21:20 -0500, Robert Haas wrote:
> On Fri, Dec 17, 2010 at 9:14 PM, Andreas Karlsson  wrote:
> > What it does is gets rid of the incorrect completion which comes from
> > the completion rule for "SET foo TO bar" by adding the correct
> > completion for "SET SCHEMA" higher up in the completion function.
> >
> > So instead of an incorrect completion we get the correct one.
> 
> But that's not necessarily wrong, if "foo" happens to be the name of a GUC.

Agreed, which is why I made the new rule only match

ALTER x x SET SCHEMA

while the rest will fall down and match

SET

.  So I should be safe since SCHEMA is a reserved words. When I think of
it I may even have be unnecessary to require the ALTER verb.

Regards,
Andreas



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

Re: [HACKERS] Tab completion for ALTER ... SET SCHEMA

[Robert Haas]

On Fri, Dec 17, 2010 at 9:14 PM, Andreas Karlsson  wrote:
> Ah, sorry forgot the most important part of my explanation.
>
> What it does is gets rid of the incorrect completion which comes from
> the completion rule for "SET foo TO bar" by adding the correct
> completion for "SET SCHEMA" higher up in the completion function.
>
> So instead of an incorrect completion we get the correct one.

But that's not necessarily wrong, if "foo" happens to be the name of a GUC.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Tab completion for ALTER ... SET SCHEMA

[Andreas Karlsson]

Ah, sorry forgot the most important part of my explanation.

What it does is gets rid of the incorrect completion which comes from
the completion rule for "SET foo TO bar" by adding the correct
completion for "SET SCHEMA" higher up in the completion function.

So instead of an incorrect completion we get the correct one.

Regards,
Andreas Karlsson

On Fri, 2010-12-17 at 21:00 -0500, Robert Haas wrote:
> On Fri, Dec 17, 2010 at 8:34 PM, andreas  wrote:
> > It has annoys me every time I want to move a table to another schema
> > that it completes to SET SCHEMA TO DEFAULT after a couple of presses of
> > the tab key. So today I decided to get off my lazy ass to write a tiny
> > patch to fix this behaviour. :)
> >
> > My first patch for PostgreSQL so a question: Should I add this to the
> > open commitfest?
> 
> In general, yes, but before you do that...  the patch you've pasted
> below doesn't implement the behavior you've described above.  Above,
> you're describing removing a completion, but below, you're adding one.
>  So I'm confused.
> 
> > 
> >
> > *** a/src/bin/psql/tab-complete.c
> > --- b/src/bin/psql/tab-complete.c
> > *** psql_completion(char *text, int start, i
> > *** 1387,1392 
> > --- 1387,1399 
> > pg_strcasecmp(prev_wd, "USER") == 0)
> >COMPLETE_WITH_QUERY(Query_for_list_of_roles);
> >
> > +   /* ALTER   SET SCHEMA */
> > +   else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 &&
> > +   pg_strcasecmp(prev2_wd, "SET") == 0  &&
> > +   pg_strcasecmp(prev_wd, "SCHEMA") == 0)
> > +   COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
> > +
> > +
> >  /* BEGIN, END, ABORT */
> >else if (pg_strcasecmp(prev_wd, "BEGIN") == 0 ||
> > pg_strcasecmp(prev_wd, "END") == 0 ||
> >
> 
> Two other points:
> 
> 1. ALTER TABLE and ALTER FUNCTION both seem to have completions for
> this already, so maybe the above should be made specific to whatever
> other object type you're concerned about.
> 
> 2. Attaching the diff makes it much easier to extract than embedding
> it in the body of the email.
> 
> Thanks,
> 
> -- 
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
> 



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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Robert Haas]

On Fri, Dec 17, 2010 at 7:35 PM, Josh Berkus  wrote:
>
>> Well, maybe. Also, giving the sort of feedback Josh seems to want
>> probably would not be nearly as easy as he seems to think, ISTM.
>
> Oh, I don't think it would be easy.  I can't think, right now, of a good
> way to do it.

I mean, it wouldn't be enormously difficult to look for something of
the form \d+(\.\d+)?e\d+ and give a different error message for that
case, like "scientific notation is not allowed for integer inputs",
but I don't think it's really worth it.  A person who can't figure it
out without that is probably more confused than we're going to be able
to fix with a one-line error message.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Tab completion for ALTER ... SET SCHEMA

[Robert Haas]

On Fri, Dec 17, 2010 at 8:34 PM, andreas  wrote:
> It has annoys me every time I want to move a table to another schema
> that it completes to SET SCHEMA TO DEFAULT after a couple of presses of
> the tab key. So today I decided to get off my lazy ass to write a tiny
> patch to fix this behaviour. :)
>
> My first patch for PostgreSQL so a question: Should I add this to the
> open commitfest?

In general, yes, but before you do that...  the patch you've pasted
below doesn't implement the behavior you've described above.  Above,
you're describing removing a completion, but below, you're adding one.
 So I'm confused.

> 
>
> *** a/src/bin/psql/tab-complete.c
> --- b/src/bin/psql/tab-complete.c
> *** psql_completion(char *text, int start, i
> *** 1387,1392 
> --- 1387,1399 
>                         pg_strcasecmp(prev_wd, "USER") == 0)
>                COMPLETE_WITH_QUERY(Query_for_list_of_roles);
>
> +       /* ALTER   SET SCHEMA */
> +       else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 &&
> +                       pg_strcasecmp(prev2_wd, "SET") == 0  &&
> +                       pg_strcasecmp(prev_wd, "SCHEMA") == 0)
> +               COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
> +
> +
>  /* BEGIN, END, ABORT */
>        else if (pg_strcasecmp(prev_wd, "BEGIN") == 0 ||
>                         pg_strcasecmp(prev_wd, "END") == 0 ||
>

Two other points:

1. ALTER TABLE and ALTER FUNCTION both seem to have completions for
this already, so maybe the above should be made specific to whatever
other object type you're concerned about.

2. Attaching the diff makes it much easier to extract than embedding
it in the body of the email.

Thanks,

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] unlogged tables vs. GIST

[Robert Haas]

On Fri, Dec 17, 2010 at 4:17 PM, Tom Lane  wrote:
>> Since these bits will only be set/cleared when the buffer mapping is
>> changed, can we examine this bit without taking the spinlock?
>
> Only if you're willing for the result to be unreliable.

If we read the bits while someone else is writing them, we'll get
either the old or the new value, but the BM_FLUSH_XLOG bit will be the
same either way.  When FlushBuffer() is called, a shared content log
and a pin are held, so the buffer can't be renamed under us.  If
that's really unacceptable, we can do something like the attached, but
I think this is unnecessarily gross.  We already assume in other
places that the read or write of an integer is atomic.  In this case
we don't even need it to be fully atomic - we just need the particular
byte that contains this bit not to go through some intermediate state
where the bit is unset.  Is there really a memory architecture out
there that's crazy enough to let such a thing happen?  If so, I'd
expect things to be breaking right and left on that machine anyway.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


bm-flush-xlog-paranoid.patch
Description: Binary data

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

[HACKERS] Tab completion for ALTER ... SET SCHEMA

[andreas]

Hi,

It has annoys me every time I want to move a table to another schema
that it completes to SET SCHEMA TO DEFAULT after a couple of presses of
the tab key. So today I decided to get off my lazy ass to write a tiny
patch to fix this behaviour. :)

My first patch for PostgreSQL so a question: Should I add this to the
open commitfest?



*** a/src/bin/psql/tab-complete.c
--- b/src/bin/psql/tab-complete.c
*** psql_completion(char *text, int start, i
*** 1387,1392 
--- 1387,1399 
 pg_strcasecmp(prev_wd, "USER") == 0)
COMPLETE_WITH_QUERY(Query_for_list_of_roles);
  
+   /* ALTER   SET SCHEMA */
+   else if (pg_strcasecmp(prev4_wd, "ALTER") == 0 &&
+   pg_strcasecmp(prev2_wd, "SET") == 0  &&
+   pg_strcasecmp(prev_wd, "SCHEMA") == 0)
+   COMPLETE_WITH_QUERY(Query_for_list_of_schemas);
+ 
+ 
  /* BEGIN, END, ABORT */
else if (pg_strcasecmp(prev_wd, "BEGIN") == 0 ||
 pg_strcasecmp(prev_wd, "END") == 0 ||

Regards,
Andreas Karlsson



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

Re: [HACKERS] plperlu problem with utf8

[David E. Wheeler]

On Dec 17, 2010, at 5:04 PM, David E. Wheeler wrote:

>> see? Either uri_unescape() should be decoding that utf8() or you need
>> to do it *after* you call uri_unescape().  Hence the maybe it could be
>> considered a bug in uri_unescape().
> 
> Agreed.

On second thought, no. You can in fact encode anything in a URI. URI::Escape 
can't know what to decode to. So *I believe* it just unescapes the raw bytes. 
It might be handy for it to have a new function, though, to complement its 
uri_escape_utf() function:

sub uri_unescape_utf8 { Encode::decode_utf8(uri_unescape(@_)) }

Just to make things a bit clearer.

But that's a separate issue from the, erm, inconsistency with which PL/Perl 
treats encoding and decoding of its inputs and outputs.

Best,

David


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

Re: [HACKERS] plperlu problem with utf8

[David E. Wheeler]

On Dec 16, 2010, at 8:39 PM, Alex Hunsaker wrote:

>> No, URI::Escape is fine. The issue is that if you don't decode text to 
>> Perl's internal form, it assumes that it's Latin-1.
> 
> So... you are saying "\xc3\xa9" eq "\xe9" or chr(233) ?

Not knowing what those mean, I'm not saying either one, to my knowledge. What I 
understand, however, is that Perl, given a scalar with bytes in it, will treat 
it as latin-1 unless the utf8 flag is turned on.

> Im saying they are not, and if you want \xc3\xa9 to be treated as
> chr(233) you need to tell perl what encoding the string is in (err
> well actually decode it so its in "perl space" as unicode characters
> correctly).

PostgreSQL should do everything it can to decode to Perl's internal format 
before passing arguments, and to decode from Perl's internal format on output.

>> Maybe I'm misunderstanding, but it seems to me that:
>> 
>> * String arguments passed to PL/Perl functions should be decoded from the 
>> server encoding to Perl's internal representation before the function 
>> actually gets them.
> 
> Currently postgres has 2 behaviors:
> 1) If the database is utf8, turn on the utf8 flag. According to the
> perldoc snippet I quoted this should mean its a sequence of utf8 bytes
> and should interpret it as such.

Well that works for me. I always use UTF8. Oleg, what was the encoding of your 
database where you saw the issue?

> 2) its not utf8, so we just leave it as octets.

Which mean's Perl will assume that it's Latin-1, IIUC.

> So in "perl space" length($_[0]) returns the number of characters when
> you pass in a multibyte char *not* the number of bytes.  Which is
> correct, so um check we do that.  Right?

Yeah. So I just wrote and tested this function on 9.0 with Perl 5.12.2:

CREATE OR REPLACE FUNCTION perlgets(
TEXT
) RETURNS TABLE(length INT, is_utf8 BOOL) LANGUAGE plperl AS $$
   my $text = shift;
   return_next {
   length  => length $text,
   is_utf8 => utf8::is_utf8($text) ? 1 : 0
   };
$$;

In a utf-8 database:

utf8=# select * from perlgets('foo');
 length │ is_utf8 
┼─
  8 │ t
(1 row)


In a latin-1 database:

latin=# select * from perlgets('foo');
 length │ is_utf8 
┼─
  8 │ f
(1 row)

I would argue that in the latter case, is_utf8 should be true, too. That is, 
PL/Perl should decode from Latin-1 to Perl's internal form.

Interestingly, when I created a function that takes a bytea argument, utf8 was 
*still* enabled in the utf-8 database. That doesn't seem right to me.

> In the URI::Escape example we have:
> 
> # CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar  AS $$
>   use URI::Escape;
>   warn(length($_[0]));
>   return uri_unescape($_[0]); $$ LANGUAGE plperlu;
> 
> # select url_decode('comment%20passer%20le%20r%C3%A9veillon');
> WARNING: 38 at line 2

What's the output? And what's the encoding of the database?

> Ok that length looks right, just for grins lets try add one multibyte char:
> 
> # SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon☺');
> WARNING:  39 CONTEXT:  PL/Perl function "url_decode" at line 2.
>  url_decode
> ---
> comment passer le réveillon☺
> (1 row)
> 
> Still right,

The length is right, but the é is wrong. It looks like Perl thinks it's 
latin-1. Or, rather, unescape_uri() dosn't know that it should be returning 
utf-8 characters. That *might* be a bug in URI::Escape.

> now lets try the utf8::decode version that "works".  Only
> lets look at the length of the string we are returning instead of the
> one we are passing in:
> 
> # CREATE OR REPLACE FUNCTION url_decode(Vkw varchar) RETURNS varchar  AS $$
>   use URI::Escape;
>   utf8::decode($_[0]);
>   my $str = uri_unescape($_[0]);
>   warn(length($str));
>   return $str;
> $$ LANGUAGE plperlu;
> 
> # SELECT url_decode('comment%20passer%20le%20r%C3%A9veillon');
> WARNING:  28 at line 5.
> CONTEXT:  PL/Perl function "url_decode"
> url_decode
> -
> comment passer le réveillon
> (1 row)
> 
> Looks harmless enough...

Looks far better, in fact. Interesting that URI::Escape does the right thing 
only if the utf8 flag has been turned on in the string passed to it. But in 
Perl it usually won't be, because the encoded string should generally have only 
ASCII characters.

> # SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
> WARNING:  28 at line 5.
> CONTEXT:  PL/Perl function "url_decode"
> length
> 
> 27
> (1 row)
> 
> Wait a minute... those lengths should match.
> 
> Post patch they do:
> # SELECT length(url_decode('comment%20passer%20le%20r%C3%A9veillon'));
> WARNING:  28 at line 5.
> CONTEXT:  PL/Perl function "url_decode"
> length
> 
> 28
> (1 row)
> 
> Still confused? Yeah me too.

Yeah…

> Maybe this will help:
> 
> #!/usr/bin/perl
> use URI::Escape;
> my $str = uri_unescape("%c3%a9");
>

Re: [HACKERS] Why don't we accept exponential format for integers?

[Josh Berkus]

> Well, maybe. Also, giving the sort of feedback Josh seems to want
> probably would not be nearly as easy as he seems to think, ISTM.

Oh, I don't think it would be easy.  I can't think, right now, of a good
way to do it.


-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Andrew Dunstan]

On 12/17/2010 07:03 PM, Robert Haas wrote:




If it's not a good idea to enable that functionality,
then it would be nice to come up with some way to make it more clear why
it's failing.

I guess I'm about to show my arrogance and utter lack of sympathy for
the common man here, but it's hard for me to imagine anyone who has
any experience at all as a programmer seeing the message ERROR:
invalid input syntax for integer: "1e+01" and having NO idea what the
problem could possibly be.  I can imagine them thinking, as you said,
that it's stupid and arbitrary, even though I don't agree with that
myself.  But I have a hard time imagining someone looking at that
error and not knowing what they need to do to correct it, unless they
don't know the meaning of the word "integer".


Well, maybe. Also, giving the sort of feedback Josh seems to want 
probably would not be nearly as easy as he seems to think, ISTM.


cheers

andrew

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

Re: [HACKERS] psql expanded auto

[Chris Browne]

pete...@gmx.net (Peter Eisentraut) writes:
> I have often found myself wanting that psql automatically switch between
> normal and \x mode depending on the width of the output.  Would others
> find this useful?

I haven't tested the patch, but that *does* sound generally useful.
It's no fun trying to get one's eyes to visually line up output that
spans 3 lines...
-- 
select 'cbbrowne' || '@' || 'gmail.com';
http://linuxdatabases.info/info/nonrdbms.html
"Very little is known about the War of 1812 because the Americans lost
it."  -- Eric Nicol

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Robert Haas]

On Fri, Dec 17, 2010 at 6:49 PM, Josh Berkus  wrote:
> On 12/17/10 3:34 PM, Robert Haas wrote:
>> On Fri, Dec 17, 2010 at 6:09 PM, Josh Berkus  wrote:
>>> Oh, *I* understand the difference.  Any app developer is going to see it
>>> as stupidly arbitrary, though.
>>
>> Speaking as someone who spent 9 years doing app development, I dispute
>> the word "any".
>
> Ok, "lots" then.

Fair enough.

> If it's not a good idea to enable that functionality,
> then it would be nice to come up with some way to make it more clear why
> it's failing.

I guess I'm about to show my arrogance and utter lack of sympathy for
the common man here, but it's hard for me to imagine anyone who has
any experience at all as a programmer seeing the message ERROR:
invalid input syntax for integer: "1e+01" and having NO idea what the
problem could possibly be.  I can imagine them thinking, as you said,
that it's stupid and arbitrary, even though I don't agree with that
myself.  But I have a hard time imagining someone looking at that
error and not knowing what they need to do to correct it, unless they
don't know the meaning of the word "integer".

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Josh Berkus]

On 12/17/10 3:34 PM, Robert Haas wrote:
> On Fri, Dec 17, 2010 at 6:09 PM, Josh Berkus  wrote:
>> Oh, *I* understand the difference.  Any app developer is going to see it
>> as stupidly arbitrary, though.
> 
> Speaking as someone who spent 9 years doing app development, I dispute
> the word "any".

Ok, "lots" then.  If it's not a good idea to enable that functionality,
then it would be nice to come up with some way to make it more clear why
it's failing.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

[HACKERS] mingw make vs shell vs pwd -W

[Andrew Dunstan]

On my new Mingw setup, the following makefile fails:

foo := $(shell pwd -W)
all:
@echo foo: $(foo)

with output looking like:

pwd: invalid option -- W
Try `pwd --help' for more information.
foo:

while the following works:

foo := $(shell sh -c "pwd -W")
all:
@echo foo: $(foo)

and produces as expected:

foo: C:/Mingw/msys/1.0/home/andrew

My working theory is that this is because "pwd -W" is a shell builtin 
and the current version of mingw make is not invoking "sh" with $(shell).


I thought  might have screwed up my setup, but the same results occurred 
on a different machine with a completely fresh Mingw installation.


This failure is causing a regression failure as can be seen here:


Luckily, we have got rid of almost all the uses of "pwd -W", and since 
vpath builds don't work on Mingw (or at least on mine) I believe the 
only one that actually matters at all right now is the one in 8.2's  
/src/test/regress/GNUmakefile.


I'm going to fix that one. My inclination is to let these others be, 
sort of like a vermiform appendix.


   pg_8_2/src/interfaces/ecpg/test/Makefile
   pg_8_2/src/pl/plpython/Makefile
   pg_8_2/src/pl/tcl/Makefile
   pg_8_2/src/pl/plperl/GNUmakefile
   pg_8_3/src/interfaces/ecpg/test/Makefile
   pg_8_3/src/pl/plpython/Makefile
   pg_8_3/src/pl/tcl/Makefile
   pg_8_3/src/pl/plperl/GNUmakefile
   pg_8_3/src/test/regress/GNUmakefile
   pg_8_4/src/interfaces/ecpg/test/Makefile
   pg_9_0/src/interfaces/ecpg/test/Makefile
   pg_head/src/interfaces/ecpg/test/Makefile



Thoughts?

cheers

andrew


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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Robert Haas]

On Fri, Dec 17, 2010 at 6:09 PM, Josh Berkus  wrote:
> Oh, *I* understand the difference.  Any app developer is going to see it
> as stupidly arbitrary, though.

Speaking as someone who spent 9 years doing app development, I dispute
the word "any".

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Josh Berkus]

>> Well, that's stupidly arbitrary.  If we're not going to accept
>> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.
> 
> It's not arbitrary in the slightest.  One is a run-time type conversion;
> the other is a question of what strings the type-specific input routine
> for integer will accept.

Oh, *I* understand the difference.  Any app developer is going to see it
as stupidly arbitrary, though.

Anyway, this answered my basic question.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Charles.McDevitt]

> > And so does:
> > SELECT 1.23e+01::Integer
> >
> >
> >> > which I find just as dangerous as
> >> > SELECT '1.234e+01'::Integer;
> >
> > Add quotes to either of the other two, and then they don't work either.
> 
> Well, that's stupidly arbitrary.  If we're not going to accept
> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.
> 

Isn't this a case of an explicit cast?  Shouldn't our answer to 
1.234e+1::Integer be the same as CAST(1234e+1 AS Integer)?
Which is legal ISO SQL, as far as I can see.

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Marti Raudsepp]

On Sat, Dec 18, 2010 at 00:05, Josh Berkus  wrote:
> Well, that's stupidly arbitrary.  If we're not going to accept
> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.

Not surprising to me. This is how many languages implement type conversion.

Python:
>>> int(1.234e+01)
12
>>> int('1.234e+01')
ValueError: invalid literal for int() with base 10: '1.234e+01'

PHP:
print intval(1.234e+01) . "\n";
print intval('1.234e+01') . "\n";
gives:
12
1
Because PHP's int->string cast terminates parsing when it sees an
unrecognized character.

Java makes the difference quite explicit and obvious:
int a = (int)1.234e+01;
int a = Integer.parseInt("1.234e+01);

Regards,
Marti

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Tom Lane]

Josh Berkus  writes:
> On 12/17/10 12:46 PM, Jeff Janes wrote:
>> Add quotes to either of the other two, and then they don't work either.

> Well, that's stupidly arbitrary.  If we're not going to accept
> '1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.

It's not arbitrary in the slightest.  One is a run-time type conversion;
the other is a question of what strings the type-specific input routine
for integer will accept.

regards, tom lane

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

[HACKERS] psql expanded auto

[Peter Eisentraut]

I have often found myself wanting that psql automatically switch between
normal and \x mode depending on the width of the output.  Would others
find this useful?

Attached is a crude demo patch.  Enable with \pset expanded auto.

diff --git i/src/bin/psql/command.c w/src/bin/psql/command.c
index c1edf44..7e24903 100644
--- i/src/bin/psql/command.c
+++ w/src/bin/psql/command.c
@@ -2129,14 +2129,21 @@ do_pset(const char *param, const char *value, printQueryOpt *popt, bool quiet)
 	/* set expanded/vertical mode */
 	else if (strcmp(param, "x") == 0 || strcmp(param, "expanded") == 0 || strcmp(param, "vertical") == 0)
 	{
-		if (value)
+		if (value && pg_strcasecmp(value, "auto") == 0)
+			popt->topt.expanded = 2;
+		else if (value)
 			popt->topt.expanded = ParseVariableBool(value);
 		else
 			popt->topt.expanded = !popt->topt.expanded;
 		if (!quiet)
-			printf(popt->topt.expanded
-   ? _("Expanded display is on.\n")
-   : _("Expanded display is off.\n"));
+		{
+			if (popt->topt.expanded == 1)
+printf(_("Expanded display is on.\n"));
+			else if (popt->topt.expanded == 2)
+printf(_("Expanded display is used automatically.\n"));
+			else
+printf(_("Expanded display is off.\n"));
+		}
 	}
 
 	/* locale-aware numeric output */
diff --git i/src/bin/psql/print.c w/src/bin/psql/print.c
index c946076..07f27bc 100644
--- i/src/bin/psql/print.c
+++ w/src/bin/psql/print.c
@@ -122,9 +122,11 @@ const printTextFormat pg_utf8format =
 
 /* Local functions */
 static int	strlen_max_width(unsigned char *str, int *target_width, int encoding);
-static void IsPagerNeeded(const printTableContent *cont, const int extra_lines,
+static void IsPagerNeeded(const printTableContent *cont, const int extra_lines, bool expanded,
 			  FILE **fout, bool *is_pager);
 
+static void print_aligned_vertical(const printTableContent *cont, FILE *fout);
+
 
 static void *
 pg_local_malloc(size_t size)
@@ -713,6 +715,13 @@ print_aligned_text(const printTableContent *cont, FILE *fout)
 		}
 	}
 
+	if (cont->opt->expanded == 2 &&
+		(output_columns < total_header_width || output_columns < width_total))
+	{
+		print_aligned_vertical(cont, fout);
+		return;
+	}
+
 	/* If we wrapped beyond the display width, use the pager */
 	if (!is_pager && fout == stdout && output_columns > 0 &&
 		(output_columns < total_header_width || output_columns < width_total))
@@ -756,7 +765,7 @@ print_aligned_text(const printTableContent *cont, FILE *fout)
 extra_row_output_lines = 0;
 			}
 		}
-		IsPagerNeeded(cont, extra_output_lines, &fout, &is_pager);
+		IsPagerNeeded(cont, extra_output_lines, false, &fout, &is_pager);
 	}
 
 	/* time to output */
@@ -2265,14 +2274,14 @@ printTableCleanup(printTableContent *const content)
  * Setup pager if required
  */
 static void
-IsPagerNeeded(const printTableContent *cont, const int extra_lines, FILE **fout,
+IsPagerNeeded(const printTableContent *cont, const int extra_lines, bool expanded, FILE **fout,
 			  bool *is_pager)
 {
 	if (*fout == stdout)
 	{
 		int			lines;
 
-		if (cont->opt->expanded)
+		if (expanded)
 			lines = (cont->ncolumns + 1) * cont->nrows;
 		else
 			lines = cont->nrows + 1;
@@ -2313,8 +2322,8 @@ printTable(const printTableContent *cont, FILE *fout, FILE *flog)
 	/* print_aligned_text() handles the pager itself */
 	if ((cont->opt->format != PRINT_ALIGNED &&
 		 cont->opt->format != PRINT_WRAPPED) ||
-		cont->opt->expanded)
-		IsPagerNeeded(cont, 0, &fout, &is_pager);
+		cont->opt->expanded == 1)
+		IsPagerNeeded(cont, 0, true, &fout, &is_pager);
 
 	/* print the stuff */
 
@@ -2324,32 +2333,32 @@ printTable(const printTableContent *cont, FILE *fout, FILE *flog)
 	switch (cont->opt->format)
 	{
 		case PRINT_UNALIGNED:
-			if (cont->opt->expanded)
+			if (cont->opt->expanded == 1)
 print_unaligned_vertical(cont, fout);
 			else
 print_unaligned_text(cont, fout);
 			break;
 		case PRINT_ALIGNED:
 		case PRINT_WRAPPED:
-			if (cont->opt->expanded)
+			if (cont->opt->expanded == 1)
 print_aligned_vertical(cont, fout);
 			else
 print_aligned_text(cont, fout);
 			break;
 		case PRINT_HTML:
-			if (cont->opt->expanded)
+			if (cont->opt->expanded == 1)
 print_html_vertical(cont, fout);
 			else
 print_html_text(cont, fout);
 			break;
 		case PRINT_LATEX:
-			if (cont->opt->expanded)
+			if (cont->opt->expanded == 1)
 print_latex_vertical(cont, fout);
 			else
 print_latex_text(cont, fout);
 			break;
 		case PRINT_TROFF_MS:
-			if (cont->opt->expanded)
+			if (cont->opt->expanded == 1)
 print_troff_ms_vertical(cont, fout);
 			else
 print_troff_ms_text(cont, fout);
diff --git i/src/bin/psql/print.h w/src/bin/psql/print.h
index 1f54075..b79a73b 100644
--- i/src/bin/psql/print.h
+++ w/src/bin/psql/print.h
@@ -70,8 +70,8 @@ typedef struct printTextFormat
 typedef struct printTableOpt
 {
 	enum printFormat format;	/* see enum above */
-	bool		expanded;		/* expanded/vertical output (if supported by
- * output format) */
+	unsigned short in

Re: [HACKERS] proposal : cross-column stats

[Tomas Vondra]

Dne 17.12.2010 22:41, Heikki Linnakangas napsal(a):
> On 17.12.2010 23:13, Tomas Vondra wrote:
>> Dne 17.12.2010 19:58, Robert Haas napsal(a):
>>> I haven't read the paper yet (sorry) but just off the top of my head,
>>> one possible problem here is that our n_distinct estimates aren't
>>> always very accurate, especially for large tables.  As we've discussed
>>> before, making them accurate requires sampling a significant
>>> percentage of the table, whereas all of our other statistics can be
>>> computed reasonably accurately by sampling a fixed amount of an
>>> arbitrarily large table.  So it's possible that relying more heavily
>>> on n_distinct could turn out worse overall even if the algorithm is
>>> better.  Not sure if that's an issue here, just throwing it out
>>> there...
>>
>> Yes, you're right - the paper really is based on (estimates of) number
>> of distinct values for each of the columns as well as for the group of
>> columns.
>>
>> AFAIK it will work with reasonably precise estimates, but the point is
>> you need an estimate of distinct values of the whole group of columns.
>> So when you want to get an estimate for queries on columns (a,b), you
>> need the number of distinct value combinations of these two columns.
>>
>> And I think we're not collecting this right now, so this solution
>> requires scanning the table (or some part of it).
> 
> Any idea how sensitive it is to the accuracy of that estimate on
> distinct value combinations? If we get that off by a factor of ten or a
> hundred, what kind of an effect does it have on the final cost estimates?

Well, not really - I haven't done any experiments with it. For two
columns selectivity equation is

  (dist(A) * sel(A) + dist(B) * sel(B)) / (2 * dist(A,B))

where A and B are columns, dist(X) is number of distinct values in
column X and sel(X) is selectivity of column X.

dependency on dist(A), dist(B) and dist(A,C)


So it seems to be proportional to dist(A) and dist(B), and inverse
proportional to dist(A,B). So when increasing the dist(A) and dist(B)
10x, you'll get a 10x the original estimate. Similarly, by increasing
the dist(A,B) 10x, you'll get 10x lower estimate.

upper bound
---

Unless dist(A) or dist(B) is greater than dist(A,B), the estimated
selectivity is bounded by

  (sel(A) + sel(B)) / 2

I guess we can say that (sel(A) > sel(A,B)) is generally the same as

  sel(A) = sel(A,B)

i.e. we can use the heuristict I've already offered in the PoC.

lower bound
---

Not sure what the lower bound is, but it might be 0 if the dist(A) and
dist(B) are very small and/or dist(A,B) is huge.

regards
Tomas


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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Josh Berkus]

On 12/17/10 12:46 PM, Jeff Janes wrote:
> 
> And so does:
> SELECT 1.23e+01::Integer
> 
> 
>> > which I find just as dangerous as
>> > SELECT '1.234e+01'::Integer;
> 
> Add quotes to either of the other two, and then they don't work either.

Well, that's stupidly arbitrary.  If we're not going to accept
'1.234e+01'::Integer, then we shouldn't accept 1.234e+01::Integer either.

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] ps_status on fastpath

[Alvaro Herrera]

Excerpts from Tom Lane's message of vie dic 17 18:21:48 -0300 2010:
> Alvaro Herrera  writes:
> > Excerpts from Tom Lane's message of vie dic 17 12:41:06 -0300 2010:
> >> Hm, what about pgstat_report_activity()?
> 
> > Just noticed that it's already handled in postgres.c, before calling
> > HandleFunctionRequest.  Probably not worth messing with.
> 
> Ah.  Well, the ps_status update should be adjacent to the other one,
> not off in some other module altogether.

Agreed -- committed that way.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] proposal : cross-column stats

[Tomas Vondra]

Dne 17.12.2010 22:24, Tom Lane napsal(a):
> That seems likely to be even more unreliable than our existing
> single-column estimates :-(
> 
>   regards, tom lane

Well, yes :-(

I guess this is a place where we could use a multi-column index, if it
contains all the interesting columns. We could scan the index, not the
whole table.

That could save us tremendous amount of I/O and should be quite precise
(unless it's severely bloated).

Another thing is those 'discrete' columns are usually quite stable, i.e.
there's usually a limited list of values and it does not change very
often. Think about ZIP codes, states, etc. And the combinations are
quite stable too (counties do not move to other states, etc.).

So I think scanning a reasonable part of a table should be enough in
these cases.

regards
Tomas

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

Re: [HACKERS] proposal : cross-column stats

[Heikki Linnakangas]

On 17.12.2010 23:13, Tomas Vondra wrote:

Dne 17.12.2010 19:58, Robert Haas napsal(a):

I haven't read the paper yet (sorry) but just off the top of my head,
one possible problem here is that our n_distinct estimates aren't
always very accurate, especially for large tables.  As we've discussed
before, making them accurate requires sampling a significant
percentage of the table, whereas all of our other statistics can be
computed reasonably accurately by sampling a fixed amount of an
arbitrarily large table.  So it's possible that relying more heavily
on n_distinct could turn out worse overall even if the algorithm is
better.  Not sure if that's an issue here, just throwing it out
there...


Yes, you're right - the paper really is based on (estimates of) number
of distinct values for each of the columns as well as for the group of
columns.

AFAIK it will work with reasonably precise estimates, but the point is
you need an estimate of distinct values of the whole group of columns.
So when you want to get an estimate for queries on columns (a,b), you
need the number of distinct value combinations of these two columns.

And I think we're not collecting this right now, so this solution
requires scanning the table (or some part of it).


Any idea how sensitive it is to the accuracy of that estimate on 
distinct value combinations? If we get that off by a factor of ten or a 
hundred, what kind of an effect does it have on the final cost estimates?


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

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

Re: [HACKERS] proposal : cross-column stats

[Tom Lane]

Tomas Vondra  writes:
> AFAIK it will work with reasonably precise estimates, but the point is
> you need an estimate of distinct values of the whole group of columns.
> So when you want to get an estimate for queries on columns (a,b), you
> need the number of distinct value combinations of these two columns.

That seems likely to be even more unreliable than our existing
single-column estimates :-(

regards, tom lane

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

Re: [HACKERS] ps_status on fastpath

[Tom Lane]

Alvaro Herrera  writes:
> Excerpts from Tom Lane's message of vie dic 17 12:41:06 -0300 2010:
>> Hm, what about pgstat_report_activity()?

> Just noticed that it's already handled in postgres.c, before calling
> HandleFunctionRequest.  Probably not worth messing with.

Ah.  Well, the ps_status update should be adjacent to the other one,
not off in some other module altogether.

regards, tom lane

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

Re: [HACKERS] ps_status on fastpath

[Alvaro Herrera]

Excerpts from Tom Lane's message of vie dic 17 12:41:06 -0300 2010:
> Alvaro Herrera  writes:
> > I noticed that the fastpath code doesn't update ps_status, which would
> > be harmless except that it leads to "idle in transaction" being logged
> > in log_line_prefix for the command tag.
> 
> > Are there objections to applying this?
> 
> Hm, what about pgstat_report_activity()?

Just noticed that it's already handled in postgres.c, before calling
HandleFunctionRequest.  Probably not worth messing with.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] unlogged tables vs. GIST

[Tom Lane]

[ hit send too soon ... ]

Robert Haas  writes:
> Since these bits will only be set/cleared when the buffer mapping is
> changed, can we examine this bit without taking the spinlock?

Only if you're willing for the result to be unreliable.  In the
case of the xlog flush bit, I don't believe an extra locking cycle
should be necessary anyway, as you surely had the lock when you 
found the page to be dirty in the first place.  You could grab the
bit then.  I'm not sure where you envision checking the other bit,
but I doubt it can be all that far removed from a lock acquisition.

regards, tom lane

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

Re: [HACKERS] unlogged tables vs. GIST

[Tom Lane]

Robert Haas  writes:
> Allright, what do you want to call the other bit, then?  BM_SKIP_XLOG_FLUSH?

Like I said, I'd be tempted to invert the sense, so that the flag is set
for normal relations.  Then it becomes something like BM_FLUSH_XLOG.

regards, tom lane

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

Re: [HACKERS] proposal : cross-column stats

[Tomas Vondra]

Dne 17.12.2010 19:58, Robert Haas napsal(a):
> I haven't read the paper yet (sorry) but just off the top of my head,
> one possible problem here is that our n_distinct estimates aren't
> always very accurate, especially for large tables.  As we've discussed
> before, making them accurate requires sampling a significant
> percentage of the table, whereas all of our other statistics can be
> computed reasonably accurately by sampling a fixed amount of an
> arbitrarily large table.  So it's possible that relying more heavily
> on n_distinct could turn out worse overall even if the algorithm is
> better.  Not sure if that's an issue here, just throwing it out
> there...

Yes, you're right - the paper really is based on (estimates of) number
of distinct values for each of the columns as well as for the group of
columns.

AFAIK it will work with reasonably precise estimates, but the point is
you need an estimate of distinct values of the whole group of columns.
So when you want to get an estimate for queries on columns (a,b), you
need the number of distinct value combinations of these two columns.

And I think we're not collecting this right now, so this solution
requires scanning the table (or some part of it).

I know this is a weak point of the whole solution, but the truth is
every cross-column stats solution will have to do something like this. I
don't think we'll find a solution with 0 performance impact, without the
need to scan sufficient part of a table.

That's why I want to make this optional so that the users will use it
only when really needed.

Anyway one possible solution might be to allow the user to set these
values manually (as in case when ndistinct estimates are not precise).

regards
Tomas

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

[HACKERS] typed table casting

[Peter Eisentraut]

Here is a small patch that allows casting a table's row type to the
table's supertype if it's a typed table.  This is analogous to the
existing facility that allows casting a row type to a supertable's row
type.

diff --git i/src/backend/parser/parse_coerce.c w/src/backend/parser/parse_coerce.c
index 4eb48ff..ccc4d31 100644
--- i/src/backend/parser/parse_coerce.c
+++ w/src/backend/parser/parse_coerce.c
@@ -15,6 +15,7 @@
 #include "postgres.h"
 
 #include "catalog/pg_cast.h"
+#include "catalog/pg_class.h"
 #include "catalog/pg_inherits_fn.h"
 #include "catalog/pg_proc.h"
 #include "catalog/pg_type.h"
@@ -48,6 +49,7 @@ static Node *coerce_record_to_complex(ParseState *pstate, Node *node,
 		 CoercionForm cformat,
 		 int location);
 static bool is_complex_array(Oid typid);
+static bool typeIsOfTypedTable(Oid reltypeId, Oid reloftypeId);
 
 
 /*
@@ -371,7 +373,8 @@ coerce_type(ParseState *pstate, Node *node,
 		/* NB: we do NOT want a RelabelType here */
 		return node;
 	}
-	if (typeInheritsFrom(inputTypeId, targetTypeId))
+	if (typeInheritsFrom(inputTypeId, targetTypeId)
+		|| typeIsOfTypedTable(inputTypeId, targetTypeId))
 	{
 		/*
 		 * Input class type is a subclass of target, so generate an
@@ -482,7 +485,8 @@ can_coerce_type(int nargs, Oid *input_typeids, Oid *target_typeids,
 		/*
 		 * If input is a class type that inherits from target, accept
 		 */
-		if (typeInheritsFrom(inputTypeId, targetTypeId))
+		if (typeInheritsFrom(inputTypeId, targetTypeId)
+			|| typeIsOfTypedTable(inputTypeId, targetTypeId))
 			continue;
 
 		/*
@@ -2046,3 +2050,34 @@ is_complex_array(Oid typid)
 
 	return (OidIsValid(elemtype) && ISCOMPLEX(elemtype));
 }
+
+
+/*
+ * Check whether reltypeId is the row type of a typed table of type
+ * reloftypeId.  (This is conceptually similar to the subtype
+ * relationship checked by typeInheritsFrom().)
+ */
+static bool
+typeIsOfTypedTable(Oid reltypeId, Oid reloftypeId)
+{
+	Oid relid = typeidTypeRelid(reltypeId);
+	bool result = false;
+
+	if (relid)
+	{
+		HeapTuple	tp;
+		Form_pg_class reltup;
+
+		tp = SearchSysCache1(RELOID, ObjectIdGetDatum(relid));
+		if (!HeapTupleIsValid(tp))
+			elog(ERROR, "cache lookup failed for relation %u", relid);
+
+		reltup = (Form_pg_class) GETSTRUCT(tp);
+		if (reltup->reloftype == reloftypeId)
+			result = true;
+
+		ReleaseSysCache(tp);
+	}
+
+	return result;
+}
diff --git i/src/test/regress/expected/typed_table.out w/src/test/regress/expected/typed_table.out
index 6db8d4c..0874a64 100644
--- i/src/test/regress/expected/typed_table.out
+++ w/src/test/regress/expected/typed_table.out
@@ -92,3 +92,18 @@ drop cascades to function get_all_persons()
 drop cascades to table persons2
 drop cascades to table persons3
 DROP TABLE stuff;
+-- implicit casting
+CREATE TYPE person_type AS (id int, name text);
+CREATE TABLE persons OF person_type;
+INSERT INTO persons VALUES (1, 'test');
+CREATE FUNCTION namelen(person_type) RETURNS int LANGUAGE SQL AS $$ SELECT length($1.name) $$;
+SELECT id, namelen(persons) FROM persons;
+ id | namelen 
++-
+  1 |   4
+(1 row)
+
+DROP TYPE person_type CASCADE;
+NOTICE:  drop cascades to 2 other objects
+DETAIL:  drop cascades to table persons
+drop cascades to function namelen(person_type)
diff --git i/src/test/regress/sql/typed_table.sql w/src/test/regress/sql/typed_table.sql
index 1afede1..b0d452c 100644
--- i/src/test/regress/sql/typed_table.sql
+++ w/src/test/regress/sql/typed_table.sql
@@ -47,3 +47,15 @@ DROP TYPE person_type RESTRICT;
 DROP TYPE person_type CASCADE;
 
 DROP TABLE stuff;
+
+
+-- implicit casting
+
+CREATE TYPE person_type AS (id int, name text);
+CREATE TABLE persons OF person_type;
+INSERT INTO persons VALUES (1, 'test');
+
+CREATE FUNCTION namelen(person_type) RETURNS int LANGUAGE SQL AS $$ SELECT length($1.name) $$;
+SELECT id, namelen(persons) FROM persons;
+
+DROP TYPE person_type CASCADE;

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Bill Moran]

In response to Jeff Janes :

> On Fri, Dec 17, 2010 at 12:16 PM, Bill Moran  wrote:
> > In response to Tom Lane :
> >
> >> Josh Berkus  writes:
> >> > postgres=# select '1e+01'::Integer
> >> > postgres-# ;
> >> > ERROR:  invalid input syntax for integer: "1e+01"
> >>
> >> I have never heard of any programming system anywhere that accepts such
> >> a syntax for integers (assuming it distinguishes integers from other
> >> numbers at all).  I'm not excited about being the first.  Why does this
> >> error surprise you?  It doesn't seem particularly different from arguing
> >> that 1.000 should be considered an integer, which strikes me as a
> >> seriously bad idea.
> >
> > But
> > SELECT 1.000::Integer;
> > works.  And so does
> > SELECT 1.234::Integer;
> 
> And so does:
> SELECT 1.23e+01::Integer
> 
> 
> > which I find just as dangerous as
> > SELECT '1.234e+01'::Integer;
> 
> Add quotes to either of the other two, and then they don't work either.

Ah ... I wasn't looking carefully enough, that changes the landscape
quite a bit ...

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [HACKERS] unlogged tables vs. GIST

[Robert Haas]

On Fri, Dec 17, 2010 at 3:15 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Fri, Dec 17, 2010 at 3:03 PM, Tom Lane  wrote:
>>> Yeah.  I think that BM_UNLOGGED might be a poor choice for the flag name,
>>> just because it overstates what the bufmgr needs to assume.
>
>> I was actually thinking of adding BM_UNLOGGED even before this
>> discussion, because that would allow unlogged buffers to be excluded
>> from non-shutdown checkpoints.  We could add two flags with different
>> semantics that take on, under present rules, the same value, but I'd
>> be disinclined to burn the extra bit without a concrete need.
>
> bufmgr is currently using eight bits out of a 16-bit flag field, and
> IIRC at least five of those have been there since the beginning.  So our
> accretion rate is something like one bit every four years.  I think not
> being willing to use two bits to describe two unrelated behaviors is
> penny-wise and pound-foolish --- bufmgr is already complicated enough,
> let's not add useless barriers to readability.

Allright, what do you want to call the other bit, then?  BM_SKIP_XLOG_FLUSH?

I have a feeling we may also be creating BM_UNTIDY rather soon, per
previous discussion of hint bit I/O.

Since these bits will only be set/cleared when the buffer mapping is
changed, can we examine this bit without taking the spinlock?  If not,
we're going to have to stick an extra spinlock acquire/release into
FlushBuffer(), which sounds rather unappealing.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] proposal : cross-column stats

[Tomas Vondra]

Hi,

I've read about 10 papers about estimation this week, and I have gained
some insight. I'm not going to describe each of the papers here, I plan
to set up a wiki page about cross column statistics

  http://wiki.postgresql.org/wiki/Cross_Columns_Stats

and I'll put the list of papers and some basic info there. There was a
lot of discussion in this thread, and it's difficult to follow it, so
I'll put there some details about the proposal etc.

So I'll just briefly describe the interesting things I've learned from
those papers.

-- instances of the problem --

Generally, there are four quite different cases of the problem.

First, the columns may be either real-valued or discrete. And by
'discrete' I mean the value is rather a label than a value. It does not
make sense to add or subtract them, etc. So for example city names or
zip codes are discrete values (although zip codes are numbers etc).

Second, the queries are consist of equality or inequality (range)
conditions.

So actually there are four instances of the problem:

  | equality conditions | inequality conditions |
 
  discrete values |  A  |   D   |
  numeric values  |  C  |   B   |
 

I think those four problems should be treated separately, although some
of the techniques may be common.

A) discrete values and inequality conditions

   One of the papers (A Bayesian Approach to Estimating The Selectivity
   of Conjuctive Predicates) describes a quite interesting solution to
   this problem - I've already posted a description on how to apply it
   to the ZIP code / city name problem - see this

   http://archives.postgresql.org/pgsql-hackers/2010-12/msg01576.php

B) numeric values and inequality conditions

   Most of the papers dealing with this problem are based on
   discretization and multi-dimensional histograms to approximate the
   joint distribution. So I believe my initial proposal was not a
   complete nonsense.

   Once we have a working histogram-based solution, we can work on
   precision and efficiency (how much space is needed to store it, how
   long does it take to compute an estimate, etc.). There are two
   ways to do that.

   First, most of the papers offer an enhanced type of histogram
   (although the histogram proposed in the paper is always evaluated as
   the most efficient one, which is a bit suspicious). Anyway there are
   advanced quite-promising ways to build multi-dimensional histograms.

   Second, the paper "Selectivity estimation using probabilistic
   models") describes a solution based on bayesian networks. That means
   really really promising (actually it addresses join selectivity
   estimation too).

   So yeas, I'm quite confident we can solve this issue and improve the
   efficiency - either by an advanced histogram or using bayesian
   networks.

C) numeric values and equality conditions

   OK, I'm not sure how to solve this case. But the queries against
   numeric queries are range queries in most cases I guess, so maybe
   that's not that big deal.

D) discrete values and inequality conditions

   Basically, this can be handled just like numeric values after
   discretization, i.e. using a histogram. But this is usually

E) a combination of discrete / numeric columns

   I'm not sure how to deal with this. Obviously it's possible to
   build multi-dimensional histogram, and estimate as many queries as
   possible.

---

The papers describe some interesting alternative approaches, e.g. based
on SVD (singular value decomposition) or random sampling (or kernel
estimators, which an enhanced version of sampling).

But there are various issues associated with those solutions. SVD is
applicable to 2D only, so we'd be stuck with 2 columns, and random
sampling sounds a bit suspicious to me).

regards
Tomas

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Bill Moran]

In response to "Joshua D. Drake" :

> 
> > Just another example of the fact that PHP was designed by incompetent
> > amateurs :-(
> > 
> > http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html
> 
> Unless I am misunderstanding the argument... perl and python both
> support what is suggested here.
> 
> j...@jd-desktop:~$ perl -e 'print int('1e+01')';
> 10

Try the equivalent of:

$i = 1; while ($i < 1) { $i *= 10; echo $i . "\n";}

In languages other than PHP.  In PHP the output is:

10
100
1000
1
10
100
1000
1
10
100
1000
1
10
100
1000
1
10
100
1.0E+19
1.0E+20
1.0E+21
1.0E+22
1.0E+23
1.0E+24

The result being that a construct such as:
$query = "INSERT INTO some_table (int_column) VALUES ($i)";

Could end up being:
$query = "INSERT INTO some_table (int_column) VALUES (1.0E+24)";

Now, I want to make it clear that I'm not arguing that this is correct.
PHP's bizarre ideas about what constitutes types is one of my biggest
gripes against that language.  I'm only pointing it out because it's
a clear case where _not_ having the suggested conversion might cause
errors in a program.  Again, I'd be liable to argue that in such a
case the error is with PHP and not PostgreSQL, but if many other
languages behave the same, it might be a legitimate argument in favor
of supporting such an automatic conversion.

A strong argument against this is the fact that I've had problems with
MSSQL converting strings such as 1034297182365013256e109613205819326501
(i.e., that's an unfortunate hexidecimal string, not an exponential
number) into numbers and then returning overflow errors, which I find
extremely annoying and outright wrong, and which requires hacks in the
application code to prevent.

Now that I consider those points, I think I'm actually arguing on Tom's
side, that we should not support such a conversion ... actually, I'm
not sure what side of this I'm on right now, I'm just providing
evidence ...

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Jeff Janes]

On Fri, Dec 17, 2010 at 12:16 PM, Bill Moran  wrote:
> In response to Tom Lane :
>
>> Josh Berkus  writes:
>> > postgres=# select '1e+01'::Integer
>> > postgres-# ;
>> > ERROR:  invalid input syntax for integer: "1e+01"
>>
>> I have never heard of any programming system anywhere that accepts such
>> a syntax for integers (assuming it distinguishes integers from other
>> numbers at all).  I'm not excited about being the first.  Why does this
>> error surprise you?  It doesn't seem particularly different from arguing
>> that 1.000 should be considered an integer, which strikes me as a
>> seriously bad idea.
>
> But
> SELECT 1.000::Integer;
> works.  And so does
> SELECT 1.234::Integer;

And so does:
SELECT 1.23e+01::Integer


> which I find just as dangerous as
> SELECT '1.234e+01'::Integer;

Add quotes to either of the other two, and then they don't work either.

Cheers,

Jeff

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Tom Lane]

Christophe Pettus  writes:
> Python 2.6.1 (r261:67515, Jun 24 2010, 21:47:49) 
> [GCC 4.2.1 (Apple Inc. build 5646)] on darwin
> Type "help", "copyright", "credits" or "license" for more information.
> isinstance(10,int)
> True
> isinstance(1e10,int)
> False

Right.  Possibly a more concrete reason why this doesn't seem like a
great idea:

1e+1integer?
1e+0integer?
1e-0integer?
1e-1definitely not an integer

regards, tom lane

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Christophe Pettus]

Python 2.6.1 (r261:67515, Jun 24 2010, 21:47:49) 
[GCC 4.2.1 (Apple Inc. build 5646)] on darwin
Type "help", "copyright", "credits" or "license" for more information.
>>> isinstance(10,int)
True
>>> isinstance(1e10,int)
False

--
-- Christophe Pettus
   x...@thebuild.com


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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Robert Haas]

On Fri, Dec 17, 2010 at 3:31 PM, Joshua D. Drake  wrote:
>
>> Just another example of the fact that PHP was designed by incompetent
>> amateurs :-(
>>
>> http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html
>
> Unless I am misunderstanding the argument... perl and python both
> support what is suggested here.
>
> j...@jd-desktop:~$ perl -e 'print int('1e+01')';
> 10

You're misunderstanding the argument.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Joshua D. Drake]

> Just another example of the fact that PHP was designed by incompetent
> amateurs :-(
> 
> http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html

Unless I am misunderstanding the argument... perl and python both
support what is suggested here.

j...@jd-desktop:~$ perl -e 'print int('1e+01')';
10

Sincerely,

Joshua D. Drake


-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Nathan Boley]

 print int(1e+01)
> 10

>

That isn't building an integer: it is creating a float and casting to an int.

try:

int( 1e100 )

Best,
Nathan

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Tom Lane]

"Joshua D. Drake"  writes:
> On Fri, 2010-12-17 at 14:35 -0500, Tom Lane wrote:
>> I have never heard of any programming system anywhere that accepts
>> such
>> a syntax for integers (assuming it distinguishes integers from other
>> numbers at all).

> Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56) 
> [GCC 4.4.5] on linux2
> Type "help", "copyright", "credits" or "license" for more information.
> print int(1e+01)
> 10

That's a conversion, not an integer natively.

regards, tom lane

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Joshua D. Drake]

On Fri, 2010-12-17 at 14:35 -0500, Tom Lane wrote:
> Josh Berkus  writes:
> > postgres=# select '1e+01'::Integer
> > postgres-# ;
> > ERROR:  invalid input syntax for integer: "1e+01"
> 
> I have never heard of any programming system anywhere that accepts
> such
> a syntax for integers (assuming it distinguishes integers from other
> numbers at all).  I'm not excited about being the first.  Why does
> this
> error surprise you?  It doesn't seem particularly different from
> arguing
> that 1.000 should be considered an integer, which strikes me as a
> seriously bad idea.
> 
Python 2.6.6 (r266:84292, Sep 15 2010, 16:22:56) 
[GCC 4.4.5] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> print int(1e+01)
10
>>> 

Sincerely,

Joshua D. Drake


> regards, tom lane 
-- 
PostgreSQL.org Major Contributor
Command Prompt, Inc: http://www.commandprompt.com/ - 509.416.6579
Consulting, Training, Support, Custom Development, Engineering
http://twitter.com/cmdpromptinc | http://identi.ca/commandprompt


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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Tom Lane]

Bill Moran  writes:
> In response to Tom Lane :
>> I have never heard of any programming system anywhere that accepts such
>> a syntax for integers (assuming it distinguishes integers from other
>> numbers at all).  I'm not excited about being the first.

> But
> SELECT 1.000::Integer;
> works.  And so does

Sure.  That's a datatype conversion, though; it's not a case of taking
the value as an integer natively.

> One of the exciting (but possibly wrong) arguments in favor of this is the
> fact that some programming languages will output integers in exponential
> notation when the numbers are very large (PHP is the only example that
> comes to mind, but it's a pretty common language)

Just another example of the fact that PHP was designed by incompetent
amateurs :-(

http://www.junauza.com/2010/12/top-50-programming-quotes-of-all-time.html

regards, tom lane

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Bill Moran]

In response to Tom Lane :

> Josh Berkus  writes:
> > postgres=# select '1e+01'::Integer
> > postgres-# ;
> > ERROR:  invalid input syntax for integer: "1e+01"
> 
> I have never heard of any programming system anywhere that accepts such
> a syntax for integers (assuming it distinguishes integers from other
> numbers at all).  I'm not excited about being the first.  Why does this
> error surprise you?  It doesn't seem particularly different from arguing
> that 1.000 should be considered an integer, which strikes me as a
> seriously bad idea.

But
SELECT 1.000::Integer;
works.  And so does
SELECT 1.234::Integer;
which I find just as dangerous as
SELECT '1.234e+01'::Integer;

One of the exciting (but possibly wrong) arguments in favor of this is the
fact that some programming languages will output integers in exponential
notation when the numbers are very large (PHP is the only example that
comes to mind, but it's a pretty common language) which may cause numbers
formatted thusly to be included in queries without the programmers prior
realization.

-- 
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/

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

Re: [HACKERS] unlogged tables vs. GIST

[Tom Lane]

Robert Haas  writes:
> On Fri, Dec 17, 2010 at 3:03 PM, Tom Lane  wrote:
>> Yeah.  I think that BM_UNLOGGED might be a poor choice for the flag name,
>> just because it overstates what the bufmgr needs to assume.

> I was actually thinking of adding BM_UNLOGGED even before this
> discussion, because that would allow unlogged buffers to be excluded
> from non-shutdown checkpoints.  We could add two flags with different
> semantics that take on, under present rules, the same value, but I'd
> be disinclined to burn the extra bit without a concrete need.

bufmgr is currently using eight bits out of a 16-bit flag field, and
IIRC at least five of those have been there since the beginning.  So our
accretion rate is something like one bit every four years.  I think not
being willing to use two bits to describe two unrelated behaviors is
penny-wise and pound-foolish --- bufmgr is already complicated enough,
let's not add useless barriers to readability.

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Tom Lane]

Robert Haas  writes:
> On Fri, Dec 17, 2010 at 2:58 PM, Tom Lane  wrote:
>> plpgsql's parser is rickety enough that I don't have a lot of confidence
>> in its ability to do things that way.

> Bummer.  Rickety is not good.

Agreed, but it's not entirely the parser's fault: the language
definition is pretty d*mn bogus to start with.  Read the comments for
the for_variable production, and ask yourself whether you really want
to inject even more difficult-to-disambiguate cases right there.

regards, tom lane

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

Re: [HACKERS] unlogged tables vs. GIST

[Robert Haas]

On Fri, Dec 17, 2010 at 3:03 PM, Tom Lane  wrote:
> Heikki Linnakangas  writes:
>> On 17.12.2010 21:32, Robert Haas wrote:
>>> I guess the question is whether it's right to conflate "table is
>>> unlogged" with "LSN is fake".  It's not immediately obvious to me that
>>> those concepts are isomorphic, although though the reverse isn't
>>> obvious to me either.
>
>> The buffer manager only needs to know if it has to flush the WAL before
>> writing the page to disk. The flag just means that the buffer manager
>> never needs to do that for this buffer. You're still free to store a
>> real LSN there if you want to, it just won't cause any WAL flushes.
>
> Yeah.  I think that BM_UNLOGGED might be a poor choice for the flag name,
> just because it overstates what the bufmgr needs to assume.  It might be
> better to reverse the flag sense, and have a new flag that *is* set if
> the page contains an LSN that we have to check against WAL.

I was actually thinking of adding BM_UNLOGGED even before this
discussion, because that would allow unlogged buffers to be excluded
from non-shutdown checkpoints.  We could add two flags with different
semantics that take on, under present rules, the same value, but I'd
be disinclined to burn the extra bit without a concrete need.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] unlogged tables vs. GIST

[Tom Lane]

Heikki Linnakangas  writes:
> On 17.12.2010 21:32, Robert Haas wrote:
>> I guess the question is whether it's right to conflate "table is
>> unlogged" with "LSN is fake".  It's not immediately obvious to me that
>> those concepts are isomorphic, although though the reverse isn't
>> obvious to me either.

> The buffer manager only needs to know if it has to flush the WAL before 
> writing the page to disk. The flag just means that the buffer manager 
> never needs to do that for this buffer. You're still free to store a 
> real LSN there if you want to, it just won't cause any WAL flushes.

Yeah.  I think that BM_UNLOGGED might be a poor choice for the flag name,
just because it overstates what the bufmgr needs to assume.  It might be
better to reverse the flag sense, and have a new flag that *is* set if
the page contains an LSN that we have to check against WAL.

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Robert Haas]

On Fri, Dec 17, 2010 at 2:58 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> On Fri, Dec 17, 2010 at 2:15 PM, Tom Lane  wrote:
>>> Robert Haas  writes:
 Unfortunately, there are likely to be a limited number of such
 keywords available.  While I agree it's helpful to have a clear
 distinction between what FOR does and what FOREACH does, it's wholly
 conventional here and won't be obvious without careful reading of the
 documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
 FORGET, it'd quickly become notational soup.
>
>>> All true, but in the absence of any plausible candidate for third or
>>> fourth or fifth types of iteration, this objection seems a bit thin.
>
>> Well, Heikki just pointed out one that Oracle supports, so that makes
>> at least #3...
>
> If you posit that we might someday wish to support what Oracle is doing
> there, it seems to me to be a precedent for using a different first
> keyword, not for what you're suggesting.  I'm not arguing that we might
> want to duplicate Oracle's syntax; only that if it's going to be cited
> as a precedent that we consider what it's actually a precedent for.

I don't quite follow what you're getting at here.  My goal was to try
to think of something more mnemonic than FOREACH, and I thought
something involving the word "element" or "array" would do the trick.
The problem is only to find a place to put it that's before the word
"IN".  But maybe that's hopeless and we should just go with FOREACH.

>>> I'm afraid that's only really feasible if you are willing for the second
>>> word to be a fully reserved word, so it can be distinguished from a
>>> plain variable name in that position.
>
>> What if we cheat and peak ahead an extra token?
>
> plpgsql's parser is rickety enough that I don't have a lot of confidence
> in its ability to do things that way.

Bummer.  Rickety is not good.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Tom Lane]

Robert Haas  writes:
> On Fri, Dec 17, 2010 at 2:15 PM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> Unfortunately, there are likely to be a limited number of such
>>> keywords available.  While I agree it's helpful to have a clear
>>> distinction between what FOR does and what FOREACH does, it's wholly
>>> conventional here and won't be obvious without careful reading of the
>>> documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
>>> FORGET, it'd quickly become notational soup.

>> All true, but in the absence of any plausible candidate for third or
>> fourth or fifth types of iteration, this objection seems a bit thin.

> Well, Heikki just pointed out one that Oracle supports, so that makes
> at least #3...

If you posit that we might someday wish to support what Oracle is doing
there, it seems to me to be a precedent for using a different first
keyword, not for what you're suggesting.  I'm not arguing that we might
want to duplicate Oracle's syntax; only that if it's going to be cited
as a precedent that we consider what it's actually a precedent for.

>> I'm afraid that's only really feasible if you are willing for the second
>> word to be a fully reserved word, so it can be distinguished from a
>> plain variable name in that position.

> What if we cheat and peak ahead an extra token?

plpgsql's parser is rickety enough that I don't have a lot of confidence
in its ability to do things that way.  In particular, there's too much
knowledge at the lexer level instead of the grammar --- you'd have to
have a way of keeping the lexer from returning T_DATUM in this one
particular context, even if "element" happened to match some variable.

regards, tom lane

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

Re: [HACKERS] unlogged tables vs. GIST

[Heikki Linnakangas]

On 17.12.2010 21:32, Robert Haas wrote:

I guess the question is whether it's right to conflate "table is
unlogged" with "LSN is fake".  It's not immediately obvious to me that
those concepts are isomorphic, although though the reverse isn't
obvious to me either.


The buffer manager only needs to know if it has to flush the WAL before 
writing the page to disk. The flag just means that the buffer manager 
never needs to do that for this buffer. You're still free to store a 
real LSN there if you want to, it just won't cause any WAL flushes.


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

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Robert Haas]

On Fri, Dec 17, 2010 at 2:15 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> Unfortunately, there are likely to be a limited number of such
>> keywords available.  While I agree it's helpful to have a clear
>> distinction between what FOR does and what FOREACH does, it's wholly
>> conventional here and won't be obvious without careful reading of the
>> documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
>> FORGET, it'd quickly become notational soup.
>
> All true, but in the absence of any plausible candidate for third or
> fourth or fifth types of iteration, this objection seems a bit thin.

Well, Heikki just pointed out one that Oracle supports, so that makes
at least #3...

>> I am still wondering if
>> there's a way to make something like "FOR ELEMENT e IN a" work.  I
>> suspect we'd be less likely to paint ourselves into a corner that way.
>
> I'm afraid that's only really feasible if you are willing for the second
> word to be a fully reserved word, so it can be distinguished from a
> plain variable name in that position.

What if we cheat and peak ahead an extra token?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] ps_status on fastpath

[Alvaro Herrera]

Excerpts from Tom Lane's message of vie dic 17 16:25:17 -0300 2010:
> Alvaro Herrera  writes:
> > Excerpts from Tom Lane's message of vie dic 17 12:41:06 -0300 2010:
> >> Hm, what about pgstat_report_activity()?
> 
> > I wasn't sure about that, because of the overhead, but now that I look
> > at it, it's supposed to be cheaper than changing the ps_status in some
> > cases, so I guess there's no harm.
> 
> Yeah, if we can afford a possible kernel call to set ps status, it
> doesn't seem like pgstat_report_activity should be a problem.  I'm
> also of the opinion that more people look at pg_stat_activity than
> ps output these days.

Well, actually, if the functions are cheap, presumably they aren't going
to stay for much time in either status report; what I'm after is fixing
the log_line_prefix stuff (%i I think it is).

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] Why don't we accept exponential format for integers?

[Tom Lane]

Josh Berkus  writes:
> postgres=# select '1e+01'::Integer
> postgres-# ;
> ERROR:  invalid input syntax for integer: "1e+01"

I have never heard of any programming system anywhere that accepts such
a syntax for integers (assuming it distinguishes integers from other
numbers at all).  I'm not excited about being the first.  Why does this
error surprise you?  It doesn't seem particularly different from arguing
that 1.000 should be considered an integer, which strikes me as a
seriously bad idea.

regards, tom lane

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

Re: [HACKERS] unlogged tables vs. GIST

[Robert Haas]

On Fri, Dec 17, 2010 at 2:31 PM, Tom Lane  wrote:
> Robert Haas  writes:
>> Another possibly-useful thing about mandating a full page header for
>> every page is that it might give us a way of avoiding unnecessary full
>> page writes.  As I wrote previously:
>
> Could we do that via a bufmgr status bit, instead?  Heikki's idea has
> the merit that it actually reduces bufmgr's knowledge of page headers,
> rather than increasing it (since a buffer marked UNLOGGED would need
> no assumptions at all about its content).

That was my first thought, but it doesn't work.  The buffer could be
evicted from shared_buffers and read back in.  If a checkpoint
intervenes meanwhile, we're OK, but otherwise you fail to emit an
otherwise-needed FPI.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] unlogged tables vs. GIST

[Robert Haas]

On Fri, Dec 17, 2010 at 2:22 PM, Tom Lane  wrote:
> Heikki Linnakangas  writes:
>> On 17.12.2010 21:07, Tom Lane wrote:
>>> IIUC, the problem is that the bufmgr might think that a GIST NSN is an
>>> LSN that should affect when to force out a dirty buffer?  What if we
>>> taught it the difference?  We could for example dedicate a pd_flags
>>> bit to marking pages whose pd_lsn isn't actually an LSN.
>
>> I'm not very fond of expanding buffer manager's knowledge of the page
>> layout. How about a new flag in the buffer desc, BM_UNLOGGED?
>
> That could work too, if you can explain how the flag comes to be set
> without a bunch of ugliness all over the system.  I don't want callers
> of ReadBuffer to have to supply the bit for example.

That's easy enough to solve - ReadBuffer() takes a Relation as an
argument, so you can easily check RelationNeedsWAL(reln).

I guess the question is whether it's right to conflate "table is
unlogged" with "LSN is fake".  It's not immediately obvious to me that
those concepts are isomorphic, although though the reverse isn't
obvious to me either.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] unlogged tables vs. GIST

[Tom Lane]

Robert Haas  writes:
> Another possibly-useful thing about mandating a full page header for
> every page is that it might give us a way of avoiding unnecessary full
> page writes.  As I wrote previously:

Could we do that via a bufmgr status bit, instead?  Heikki's idea has
the merit that it actually reduces bufmgr's knowledge of page headers,
rather than increasing it (since a buffer marked UNLOGGED would need
no assumptions at all about its content).

regards, tom lane

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

Re: [HACKERS] unlogged tables vs. GIST

[Robert Haas]

On Fri, Dec 17, 2010 at 2:07 PM, Tom Lane  wrote:
> Robert Haas  writes:
> IIUC, the problem is that the bufmgr might think that a GIST NSN is an
> LSN that should affect when to force out a dirty buffer?  What if we
> taught it the difference?  We could for example dedicate a pd_flags
> bit to marking pages whose pd_lsn isn't actually an LSN.
>
> This solution would probably imply that all pages in the shared buffer
> pool have to have a standard PageHeaderData header, not just an LSN at
> the front as is assumed now.  But that doesn't seem like a bad thing to
> me, unless maybe we were dumb enough to not use a standard page header
> in some of the secondary forks.

Ah, interesting idea.  visibilitymap.c has this:

#define MAPSIZE (BLCKSZ - MAXALIGN(SizeOfPageHeaderData))

and fsm_internals.h has this:

#define NodesPerPage (BLCKSZ - MAXALIGN(SizeOfPageHeaderData) - \
  offsetof(FSMPageData, fp_nodes))

...which seems to imply, at least on a quick look, that we might be
OK, as far as the extra forks go.

I have a vague recollection that the documentation says somewhere that
a full page header on every page isn't required.  And I'm a little
worried about my ability to track down every place in the system that
might get broken by a change in this area.  What do you think about
committing the unlogged tables patch initially without support for
GIST indexes, and working on fixing this as a separate commit?

Another possibly-useful thing about mandating a full page header for
every page is that it might give us a way of avoiding unnecessary full
page writes.  As I wrote previously:

> However, I think we can avoid this too, by
> allocating an additional bit in pd_flags, PD_FPI.  Instead of emitting
> an FPI when the old LSN precedes the redo pointer, we'll emit an FPI
> when the FPI bit is set (in which case we'll also clear the bit) OR
> when the old LSN precedes the redo pointer.  Upon emitting a WAL
> record that is torn-page safe (such as a freeze or all-visible
> record), we'll pass a flag to XLogInsert that arranges to suppress
> FPIs, bump the LSN, and set PD_FPI.  That way, if the page is touched
> again before the next checkpoint by an operation that does NOT
> suppress FPI, one will be emitted then.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] ps_status on fastpath

[Tom Lane]

Alvaro Herrera  writes:
> Excerpts from Tom Lane's message of vie dic 17 12:41:06 -0300 2010:
>> Hm, what about pgstat_report_activity()?

> I wasn't sure about that, because of the overhead, but now that I look
> at it, it's supposed to be cheaper than changing the ps_status in some
> cases, so I guess there's no harm.

Yeah, if we can afford a possible kernel call to set ps status, it
doesn't seem like pgstat_report_activity should be a problem.  I'm
also of the opinion that more people look at pg_stat_activity than
ps output these days.

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Pavel Stehule]

2010/12/17 Tom Lane :
> Pavel Stehule  writes:
>> Second semi argument for using ARRAY keyword is a verbosity of
>> PL/pgSQL. So from this perspective a ARRAY should be minimally
>> optional and ensure, so expr result will be really a array. But with a
>> optional ARRAY keyword we leaving a simple enhancing in future (on
>> parser level).
>
> No.  If we are going to put a keyword there, it can't be optional.
> Making it optional would require it to be a fully reserved word
> --- and in the case of ARRAY, even that isn't good enough, because
> of the conflict with ARRAY[...] syntax.

yes, it's true

Pavel

>
>                        regards, tom lane
>

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

Re: [HACKERS] unlogged tables vs. GIST

[Tom Lane]

Heikki Linnakangas  writes:
> On 17.12.2010 21:07, Tom Lane wrote:
>> IIUC, the problem is that the bufmgr might think that a GIST NSN is an
>> LSN that should affect when to force out a dirty buffer?  What if we
>> taught it the difference?  We could for example dedicate a pd_flags
>> bit to marking pages whose pd_lsn isn't actually an LSN.

> I'm not very fond of expanding buffer manager's knowledge of the page 
> layout. How about a new flag in the buffer desc, BM_UNLOGGED?

That could work too, if you can explain how the flag comes to be set
without a bunch of ugliness all over the system.  I don't want callers
of ReadBuffer to have to supply the bit for example.

regards, tom lane

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

Re: [HACKERS] ps_status on fastpath

[Alvaro Herrera]

Excerpts from Tom Lane's message of vie dic 17 12:41:06 -0300 2010:
> Alvaro Herrera  writes:
> > I noticed that the fastpath code doesn't update ps_status, which would
> > be harmless except that it leads to "idle in transaction" being logged
> > in log_line_prefix for the command tag.
> 
> > Are there objections to applying this?
> 
> Hm, what about pgstat_report_activity()?

I wasn't sure about that, because of the overhead, but now that I look
at it, it's supposed to be cheaper than changing the ps_status in some
cases, so I guess there's no harm.

The other argument to support the case that this should be done is that
the docs suggest that you should use prepared statements instead, which
do have the reporting overhead.

-- 
Álvaro Herrera 
The PostgreSQL Company - Command Prompt, Inc.
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

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

Re: [HACKERS] unlogged tables vs. GIST

[Heikki Linnakangas]

On 17.12.2010 21:07, Tom Lane wrote:

IIUC, the problem is that the bufmgr might think that a GIST NSN is an
LSN that should affect when to force out a dirty buffer?  What if we
taught it the difference?  We could for example dedicate a pd_flags
bit to marking pages whose pd_lsn isn't actually an LSN.

This solution would probably imply that all pages in the shared buffer
pool have to have a standard PageHeaderData header, not just an LSN at
the front as is assumed now.  But that doesn't seem like a bad thing to
me, unless maybe we were dumb enough to not use a standard page header
in some of the secondary forks.


I'm not very fond of expanding buffer manager's knowledge of the page 
layout. How about a new flag in the buffer desc, BM_UNLOGGED? There was 
some talk about skipping flushing of unlogged tables at checkpoints, I 
think we'd need BM_UNLOGGED for that anyway. Or I guess we could hang 
that behavior on the pd_flags bit too, but it doesn't seem like the 
right place for that information.


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

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Tom Lane]

Pavel Stehule  writes:
> Second semi argument for using ARRAY keyword is a verbosity of
> PL/pgSQL. So from this perspective a ARRAY should be minimally
> optional and ensure, so expr result will be really a array. But with a
> optional ARRAY keyword we leaving a simple enhancing in future (on
> parser level).

No.  If we are going to put a keyword there, it can't be optional.
Making it optional would require it to be a fully reserved word
--- and in the case of ARRAY, even that isn't good enough, because
of the conflict with ARRAY[...] syntax.

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Tom Lane]

Robert Haas  writes:
> Unfortunately, there are likely to be a limited number of such
> keywords available.  While I agree it's helpful to have a clear
> distinction between what FOR does and what FOREACH does, it's wholly
> conventional here and won't be obvious without careful reading of the
> documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
> FORGET, it'd quickly become notational soup.

All true, but in the absence of any plausible candidate for third or
fourth or fifth types of iteration, this objection seems a bit thin.

> I am still wondering if
> there's a way to make something like "FOR ELEMENT e IN a" work.  I
> suspect we'd be less likely to paint ourselves into a corner that way.

I'm afraid that's only really feasible if you are willing for the second
word to be a fully reserved word, so it can be distinguished from a
plain variable name in that position.  Which is probably worse than
inventing multiple initial keywords.  It doesn't seem to me that this
would reduce the intellectual burden of remembering which syntax does
what, anyway.

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Pavel Stehule]

2010/12/17 Robert Haas :
> On Fri, Dec 17, 2010 at 1:38 PM, Tom Lane  wrote:
>> "David E. Wheeler"  writes:
>>> On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:
 Well, we did beat up Pavel over trying to shoehorn this facility into
 the existing FOR syntax, so I can hardly blame him for thinking this
 way.  The question is whether we're willing to assume that FOREACH will
 be limited to iterating over arrays, meaning we'll be stuck with
 inventing yet another initial keyword if some other fundamentally
 different concept comes along.  Right at the moment I can't think of
 any plausible candidates, but ...
>>
>>>     FOREACH pair IN HSTORE…
>>
>> I don't actually see any problem with allowing that (or any other
>> "collection" kind of object) with the same syntax as for arrays.
>>
>> The issue that we had with adding this to FOR was that it wasn't clear
>> whether the expression after IN should be thought of as a source of
>> rows, or as a "scalar" expression yielding a collection object that
>> should get iterated through --- and because SQL allows sub-SELECT as a
>> kind of expression, this was an actual formal ambiguity not just the
>> sort of near-ambiguity that trips up users.  If you will, it wouldn't
>> have been clear whether to iterate vertically or horizontally.
>>
>> The direction that this proposal establishes is that FOR is for vertical
>> iteration and FOREACH is for horizontal iteration; that is, the argument
>> of FOREACH is a scalar expression in SQL terms, but it yields some kind
>> of collection object that we are going to iterate through the members
>> of.  Given that understanding, I'm not seeing a need for the syntax to
>> distinguish whether the collection object is an array, an hstore, or
>> some other kind of collection.  It's sufficient if we can determine this
>> by examining the type of the expression.
>>
>> We would need an extra keyword if there were some third kind of
>> iteration that was fundamentally different from either of these, but
>> like I said, I don't see a plausible candidate.  So right at the moment,
>> I'm leaning to the position that we could do without the ARRAY keyword
>> in FOREACH.  If we do think of something else that could need its own
>> keyword there, it's arguably going to be different enough that a
>> different leading keyword would be a better idea anyhow.
>
> Unfortunately, there are likely to be a limited number of such
> keywords available.  While I agree it's helpful to have a clear
> distinction between what FOR does and what FOREACH does, it's wholly
> conventional here and won't be obvious without careful reading of the
> documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
> FORGET, it'd quickly become notational soup.  I am still wondering if
> there's a way to make something like "FOR ELEMENT e IN a" work.  I
> suspect we'd be less likely to paint ourselves into a corner that way.

I understand. But it is true too , so now is FOR statement
implementation too rich. You can see to attachment with initial
implementation. It's absolutely clean and simple.  There is more valid
ideas then one. One valid idea is so FOR statement is compatible with
PL/SQL (what isn't true now :() and FOREACH can carry a pg's specific
features.

But I absolutely agree with you, so we can use a only one pg specific
keyword. There are FOREACH (pg), FOR (shared with PL/SQL), FORALL (not
implemented yet - use a PL/SQL).

Pavel

>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com
> The Enterprise PostgreSQL Company
>

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Heikki Linnakangas]

On 17.12.2010 21:04, Robert Haas wrote:

Unfortunately, there are likely to be a limited number of such
keywords available.  While I agree it's helpful to have a clear
distinction between what FOR does and what FOREACH does, it's wholly
conventional here and won't be obvious without careful reading of the
documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
FORGET, it'd quickly become notational soup.  I am still wondering if
there's a way to make something like "FOR ELEMENT e IN a" work.  I
suspect we'd be less likely to paint ourselves into a corner that way.


As a side note, Oracle has FORALL, which is a kind of bulk update 
operation over a collection type. So whatever we choose, not FORALL...


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

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

Re: [HACKERS] plperlu problem with utf8

[Alex Hunsaker]

On Fri, Dec 17, 2010 at 11:51, Alex Hunsaker  wrote:
> Also note this is just a simple test case, perl *could* elect to store
> completely ascii strings internally as utf8.  In those cases we still

Erm... not ascii I mean bytes >127

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

Re: [HACKERS] unlogged tables vs. GIST

[Tom Lane]

Robert Haas  writes:
> Given the foregoing discussion, I see only two possible paths forward here.

> 1. Just decide that that unlogged tables can't have GIST indexes, at
> least until someone figures out a way to make it work.  That's sort of
> an annoying limitation, but I think we could live with it.

> 2. Write WAL records even though the GIST index is supposedly
> unlogged.  We could either (1) write the usual XLOG_GIST_* records,
> and arrange to ignore them on replay when the relation in question is
> unlogged, or (2) write an XLOG_NOOP record to advance the current LSN.
>  The latter sounds safer to me, but it will mean that the XLOG code
> for GIST needs three separate cases (temp, perm, unlogged).  Either
> way we give up a significant chunk of the benefit of making the
> relation unlogged in the first place.

> Thoughts?

IIUC, the problem is that the bufmgr might think that a GIST NSN is an
LSN that should affect when to force out a dirty buffer?  What if we
taught it the difference?  We could for example dedicate a pd_flags
bit to marking pages whose pd_lsn isn't actually an LSN.

This solution would probably imply that all pages in the shared buffer
pool have to have a standard PageHeaderData header, not just an LSN at
the front as is assumed now.  But that doesn't seem like a bad thing to
me, unless maybe we were dumb enough to not use a standard page header
in some of the secondary forks.

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Pavel Stehule]

> We would need an extra keyword if there were some third kind of
> iteration that was fundamentally different from either of these, but
> like I said, I don't see a plausible candidate.  So right at the moment,
> I'm leaning to the position that we could do without the ARRAY keyword
> in FOREACH.  If we do think of something else that could need its own
> keyword there, it's arguably going to be different enough that a
> different leading keyword would be a better idea anyhow.
>

Maybe I propage a higher verbosity than is necessary, but it descrease
a risk so code will do some unexpected work. With ARRAY keyword we can
verify so result of expression is really a array. Next advantage is a
clean implementation now and in future. Without a auxilary keyword is
necessary to wait on execution time. So now, when we have full control
over syntax, we can protect self before "FOR" statement
implementation's complexity.

Personally - syntax without ARRAY keyword isn't significant problem
for me. Just I think so using it wisely.

Second semi argument for using ARRAY keyword is a verbosity of
PL/pgSQL. So from this perspective a ARRAY should be minimally
optional and ensure, so expr result will be really a array. But with a
optional ARRAY keyword we leaving a simple enhancing in future (on
parser level).

Pavel


>                        regards, tom lane
>

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

[HACKERS] Why don't we accept exponential format for integers?

[Josh Berkus]

Folks,

Is there any good reason that this works:

postgres=# select ('1e+01'::numeric)::integer
postgres-# ;
 int4
--
   10

But this doesn't?

postgres=# select '1e+01'::Integer
postgres-# ;
ERROR:  invalid input syntax for integer: "1e+01"
LINE 1: select '1e+01'::Integer

... or did we just never implement it?

-- 
  -- Josh Berkus
 PostgreSQL Experts Inc.
 http://www.pgexperts.com

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Robert Haas]

On Fri, Dec 17, 2010 at 1:38 PM, Tom Lane  wrote:
> "David E. Wheeler"  writes:
>> On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:
>>> Well, we did beat up Pavel over trying to shoehorn this facility into
>>> the existing FOR syntax, so I can hardly blame him for thinking this
>>> way.  The question is whether we're willing to assume that FOREACH will
>>> be limited to iterating over arrays, meaning we'll be stuck with
>>> inventing yet another initial keyword if some other fundamentally
>>> different concept comes along.  Right at the moment I can't think of
>>> any plausible candidates, but ...
>
>>     FOREACH pair IN HSTORE…
>
> I don't actually see any problem with allowing that (or any other
> "collection" kind of object) with the same syntax as for arrays.
>
> The issue that we had with adding this to FOR was that it wasn't clear
> whether the expression after IN should be thought of as a source of
> rows, or as a "scalar" expression yielding a collection object that
> should get iterated through --- and because SQL allows sub-SELECT as a
> kind of expression, this was an actual formal ambiguity not just the
> sort of near-ambiguity that trips up users.  If you will, it wouldn't
> have been clear whether to iterate vertically or horizontally.
>
> The direction that this proposal establishes is that FOR is for vertical
> iteration and FOREACH is for horizontal iteration; that is, the argument
> of FOREACH is a scalar expression in SQL terms, but it yields some kind
> of collection object that we are going to iterate through the members
> of.  Given that understanding, I'm not seeing a need for the syntax to
> distinguish whether the collection object is an array, an hstore, or
> some other kind of collection.  It's sufficient if we can determine this
> by examining the type of the expression.
>
> We would need an extra keyword if there were some third kind of
> iteration that was fundamentally different from either of these, but
> like I said, I don't see a plausible candidate.  So right at the moment,
> I'm leaning to the position that we could do without the ARRAY keyword
> in FOREACH.  If we do think of something else that could need its own
> keyword there, it's arguably going to be different enough that a
> different leading keyword would be a better idea anyhow.

Unfortunately, there are likely to be a limited number of such
keywords available.  While I agree it's helpful to have a clear
distinction between what FOR does and what FOREACH does, it's wholly
conventional here and won't be obvious without careful reading of the
documentation.  If we had FOR and FOREACH and FOREVERY and, uh,
FORGET, it'd quickly become notational soup.  I am still wondering if
there's a way to make something like "FOR ELEMENT e IN a" work.  I
suspect we'd be less likely to paint ourselves into a corner that way.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] unlogged tables vs. GIST

[Andy Colson]

Given the foregoing discussion, I see only two possible paths forward here.

1. Just decide that that unlogged tables can't have GIST indexes, at
least until someone figures out a way to make it work.  That's sort of
an annoying limitation, but I think we could live with it.



+1

In the small subset of situations that need unlogged tables, I would 
think the subset of those that need gist is exceedingly small.


Unless someone can come up with a use case that needs both unlogged and 
gist, I'd vote not to spend time on it.  (And, if ever someone does come 
along with a really good use, then time can be put toward it).


-Andy

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

Re: [HACKERS] proposal : cross-column stats

[Robert Haas]

On Fri, Dec 17, 2010 at 12:58 PM, Tomas Vondra  wrote:
> In the end, all they need to compute an estimate is number of distinct
> values for each of the columns (we already have that in pg_stats) and a
> number of distinct values for the group of columns in a query. They
> really don't need any multidimensional histogram or something like that.

I haven't read the paper yet (sorry) but just off the top of my head,
one possible problem here is that our n_distinct estimates aren't
always very accurate, especially for large tables.  As we've discussed
before, making them accurate requires sampling a significant
percentage of the table, whereas all of our other statistics can be
computed reasonably accurately by sampling a fixed amount of an
arbitrarily large table.  So it's possible that relying more heavily
on n_distinct could turn out worse overall even if the algorithm is
better.  Not sure if that's an issue here, just throwing it out
there...

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] unlogged tables vs. GIST

[Robert Haas]

On Tue, Dec 14, 2010 at 5:14 PM, Robert Haas  wrote:
> On Tue, Dec 14, 2010 at 4:55 PM, Tom Lane  wrote:
>> Robert Haas  writes:
>>> On Tue, Dec 14, 2010 at 4:24 PM, Heikki Linnakangas
>>>  wrote:
 Hmm, the first idea that comes to mind is to use a counter like the
 GetXLogRecPtrForTemp() counter I used for temp tables, but global, in 
 shared
 memory. However, that's a bit problematic because if we store a value from
 that counter to LSN, it's possible that the counter overtakes the XLOG
 insert location, and you start to get xlog flush errors. We could avoid 
 that
 if we added a new field to the GiST page header, and used that to store the
 value in the parent page instead of the LSN.
>>
>>> That doesn't seem ideal, either, because now you're eating up some
>>> number of bytes per page in every GIST index just on the off chance
>>> that one of them is unlogged.
>>
>> On-disk compatibility seems problematic here as well.
>
> Good point.

Given the foregoing discussion, I see only two possible paths forward here.

1. Just decide that that unlogged tables can't have GIST indexes, at
least until someone figures out a way to make it work.  That's sort of
an annoying limitation, but I think we could live with it.

2. Write WAL records even though the GIST index is supposedly
unlogged.  We could either (1) write the usual XLOG_GIST_* records,
and arrange to ignore them on replay when the relation in question is
unlogged, or (2) write an XLOG_NOOP record to advance the current LSN.
 The latter sounds safer to me, but it will mean that the XLOG code
for GIST needs three separate cases (temp, perm, unlogged).  Either
way we give up a significant chunk of the benefit of making the
relation unlogged in the first place.

Thoughts?

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

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

Re: [HACKERS] plperlu problem with utf8

[Alex Hunsaker]

On Fri, Dec 17, 2010 at 08:30, David Fetter  wrote:
> On Thu, Dec 16, 2010 at 07:24:46PM -0800, David Wheeler wrote:
>> On Dec 16, 2010, at 6:39 PM, Alex Hunsaker wrote:
>>
>> > Grr that should error out with "Invalid server encoding", or worst
>> > case should return a length of 3 (it utf8 encoded 128 into 2 bytes
>> > instead of leaving it as 1).  In this case the 333 causes perl
>> > store it internally as utf8.
>>
>> Well with SQL_ASCII anything goes, no?
>
> Anything except a byte that's all 0s :(

Keyword being byte, right?  Last time I checked 333 wont fit in a byte
:P.  In this case perl stores "333" as the utf8 that represents the
unicode code point 333.  Postgres uses whatever that internal
representation is, so in our SQL_ASCII database we actually end up
getting back utf8 which _is_ valid SQL_ASCII, but I wouldn't call it
"right". The behavior im aiming for is similar to what the built-in
chr does:
# SELECT chr(333);
ERROR:  requested character too large for encoding: 333

Also note this is just a simple test case, perl *could* elect to store
completely ascii strings internally as utf8.  In those cases we still
"do the wrong thing", that is we get back utf8ified bytes :(  Although
obviously from the lack of bug reports its quite uncommon.

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Tom Lane]

"David E. Wheeler"  writes:
> On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:
>> Well, we did beat up Pavel over trying to shoehorn this facility into
>> the existing FOR syntax, so I can hardly blame him for thinking this
>> way.  The question is whether we're willing to assume that FOREACH will
>> be limited to iterating over arrays, meaning we'll be stuck with
>> inventing yet another initial keyword if some other fundamentally
>> different concept comes along.  Right at the moment I can't think of
>> any plausible candidates, but ...

> FOREACH pair IN HSTORE…

I don't actually see any problem with allowing that (or any other
"collection" kind of object) with the same syntax as for arrays.

The issue that we had with adding this to FOR was that it wasn't clear
whether the expression after IN should be thought of as a source of
rows, or as a "scalar" expression yielding a collection object that
should get iterated through --- and because SQL allows sub-SELECT as a
kind of expression, this was an actual formal ambiguity not just the
sort of near-ambiguity that trips up users.  If you will, it wouldn't
have been clear whether to iterate vertically or horizontally.

The direction that this proposal establishes is that FOR is for vertical
iteration and FOREACH is for horizontal iteration; that is, the argument
of FOREACH is a scalar expression in SQL terms, but it yields some kind
of collection object that we are going to iterate through the members
of.  Given that understanding, I'm not seeing a need for the syntax to
distinguish whether the collection object is an array, an hstore, or
some other kind of collection.  It's sufficient if we can determine this
by examining the type of the expression.

We would need an extra keyword if there were some third kind of
iteration that was fundamentally different from either of these, but
like I said, I don't see a plausible candidate.  So right at the moment,
I'm leaning to the position that we could do without the ARRAY keyword
in FOREACH.  If we do think of something else that could need its own
keyword there, it's arguably going to be different enough that a
different leading keyword would be a better idea anyhow.

regards, tom lane

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

[HACKERS] relaxing sync commit if no WAL written (was Re: unlogged tables)

[Robert Haas]

On Wed, Dec 15, 2010 at 2:20 AM, Heikki Linnakangas
 wrote:
> Looks ok. I'd suggest rewording this comment though:
>
> [ the comment in question ]
>
> It's a bit hard to follow, as it first lists exceptions on when we must
> flush XLOG immediately, and then lists conditions on when we can skip it.

See if the attached looks better to you.  I mostly adopted your
proposal, with a bit of additional wordsmithing, and I also added a
parenthetical comment about why we don't skip writing the commit
record altogether in this case, since that's come up twice now.

I've removed the references to unlogged tables for now, as I'm
thinking it makes sense to commit this part first.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


relax-sync-commit-v2.patch
Description: Binary data

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[David E. Wheeler]

On Dec 17, 2010, at 9:31 AM, Tom Lane wrote:

> Well, we did beat up Pavel over trying to shoehorn this facility into
> the existing FOR syntax, so I can hardly blame him for thinking this
> way.  The question is whether we're willing to assume that FOREACH will
> be limited to iterating over arrays, meaning we'll be stuck with
> inventing yet another initial keyword if some other fundamentally
> different concept comes along.  Right at the moment I can't think of
> any plausible candidates, but ...

FOREACH pair IN HSTORE…

David


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

Re: [HACKERS] proposal : cross-column stats

[Tomas Vondra]

Dne 12.12.2010 15:43, Heikki Linnakangas napsal(a):
> On 12.12.2010 15:17, Martijn van Oosterhout wrote:
>> On Sun, Dec 12, 2010 at 03:58:49AM +0100, Tomas Vondra wrote:
>> Very cool that you're working on this.
> 
> +1
> 
>>> Lets talk about one special case - I'll explain how the proposed
>>> solution works, and then I'll explain how to make it more general, what
>>> improvements are possible, what issues are there. Anyway this is by no
>>> means a perfect or complete solution - it's just a starting point.
>>
>> It looks like you handled most of the issues. Just a few points:
>>
>> - This is obviously applicable to more than just integers, probably
>>anything with a b-tree operator class. What you've coded seems rely
>>on calculations on the values. Have you thought about how it could
>>work for, for example, strings?
>>
>> The classic failure case has always been: postcodes and city names.
>> Strongly correlated, but in a way that the computer can't easily see.
> 
> Yeah, and that's actually analogous to the example I used in my
> presentation.
> 
> The way I think of that problem is that once you know the postcode,
> knowing the city name doesn't add any information. The postcode implies
> the city name. So the selectivity for "postcode = ? AND city = ?" should
> be the selectivity of "postcode = ?" alone. The measurement we need is
> "implicativeness": How strongly does column A imply a certain value for
> column B. Perhaps that could be measured by counting the number of
> distinct values of column B for each value of column A, or something
> like that. I don't know what the statisticians call that property, or if
> there's some existing theory on how to measure that from a sample.
> 
> That's assuming the combination has any matches. It's possible that the
> user chooses a postcode and city combination that doesn't exist, but
> that's no different from a user doing "city = 'fsdfsdfsd'" on a single
> column, returning no matches. We should assume that the combination
> makes sense.

Well, I've read several papers this week, in an attempt to find out what
possibilities are there when implementing cross-column statistics. One
of the papers seems like a reasonable solution to this particular
problem - discrete data + equality queries.

The article is "A Bayesian Approach to Estimating The Selectivity of
Conjuctive Predicates" (written by Heimel, Markl and Murthy). It's
freely available as a PDF

http:://subs.emis.de/LNI/Proceedings/Proceedings144/52.pdf

I do have a PDF with my own notes in it (as annotations), let me know if
you're interested ...

The basic principle is that instead of 'attribute value independence'
(which is the problem with correlated columns) they assume 'uniform
correlation' which is a much weaker assumption.

In the end, all they need to compute an estimate is number of distinct
values for each of the columns (we already have that in pg_stats) and a
number of distinct values for the group of columns in a query. They
really don't need any multidimensional histogram or something like that.

The funny thing is I've implemented most of the PoC before reading the
article - the only difference was the way to combine the estimates (

 pros and cons --

So let's see the pros:

* it's reasonably simple, the overhead should be minimal I guess (we're
  already estimating distinct values for the columns, so I guess we can
  do that for multiple columns with reasonable impact)

* there are no 'advanced data structures' as multi-dimensional
   histograms that are expensive to build and maintain

* it consistently produces better estimates than the current estimator
  based on attribute value independence assumption, and it's reasonably
  accurate (I've been playing with it for some time, so we'll need
  more tests of course)

* it's easily extensible to more columns

* I guess we could improve the estimated by our own heuristicts, to
  catch the special case when one column is perfectly implied by
  another one (e.g. when state is implied by ZIP code) - we can catch
  that by 'distinct for combination = distinct for column' and use just
  the estimate for one of the column

but there are some cons too:

* this really is not designed to work with real-valued data, it's a
  very nice solution for discrete data (namely 'labels' as for example
  city/state names, ZIP codes etc.)

* you need to know the list of columns in advance (there's nothing like
  'let's build' the stats for columns (a,b,c) and then query just (a,b))
  as you need to know the count of distinct values for the queried
  columns (well, maybe it's possible - I guess we could 'integrate'
  over all possible values of the omited column)

* it's built for 'equality' qeuries, although maybe we could modify it
  for inequalities too (but it won't be as elegant), but I guess the
  equality queries are much more common in case of discrete data (OK,
  you can run something like

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Pavel Stehule]

2010/12/17 Andrew Dunstan :
>
>
> On 12/17/2010 12:15 PM, Pavel Stehule wrote:
>>
>> 2010/12/17 Itagaki Takahiro:
>>>
>>> It should be not a main subject, but I remember there was a discussion
>>> that "IN ARRAY array-expression" looks redundant for a literal array:
>>>
>>>  IN ARRAY ARRAY[1, 3, 5]
>>>
>>> Are there any improvement for the issue?
>>
>> yes. It know it. The reason for this is bigger space for possible
>> future features related to FOREACH loop.
>>
>
> So what you're saying is we need to allow ugliness now so we can have more
> ugliness in future? I don't find that a convincing argument. I share the
> dislike for this syntax.

can be strange from me, but it is. If we close a back door now, then
we have not a space after ten years. There can be possible loops over
records, maybe over other iterable data. With this design is important
one think. A keyword after K_IN must not be a reserved keyword.

I am expecting, so typical use case doesn't be a iteration over
constant array, but over variable

so mostly often you have to write

FOREACH var IN ARRAY second_var
LOOP
  ...
END LOOP

Regards

Pavel

>
> cheers
>
> andrew
>

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Tom Lane]

Andrew Dunstan  writes:
> On 12/17/2010 12:15 PM, Pavel Stehule wrote:
>> The reason for this is bigger space for possible
>> future features related to FOREACH loop.

> So what you're saying is we need to allow ugliness now so we can have 
> more ugliness in future? I don't find that a convincing argument. I 
> share the dislike for this syntax.

Well, we did beat up Pavel over trying to shoehorn this facility into
the existing FOR syntax, so I can hardly blame him for thinking this
way.  The question is whether we're willing to assume that FOREACH will
be limited to iterating over arrays, meaning we'll be stuck with
inventing yet another initial keyword if some other fundamentally
different concept comes along.  Right at the moment I can't think of
any plausible candidates, but ...

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Tom Lane]

Merlin Moncure  writes:
> On Fri, Dec 17, 2010 at 12:15 PM, Tom Lane  wrote:
>> [ scratches head... ]  I don't follow what you envision this doing,
>> exactly?

> It's like _pg_expandarray but alterted support multiple dimensions:

> select * from unnest_dims(array[['a','b'],['c','d']]) returns
> [1,1], 'a'
> [1,2], 'b'
> [2,1], 'c'
> [2,2], 'd'

Oh, so that's an *output* not an input.  And IIUC what you are returning
is the subscripts associated with the current element, not the array's
dimensions.  Seems like it should go beside the normal target variable
then, not at the end.

FOREACH variable_for_value [, variable_for_subscripts ] IN ARRAY ...

regards, tom lane

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

Re: [HACKERS] proposal: FOREACH-IN-ARRAY (probably for 9.2?)

[Andrew Dunstan]

On 12/17/2010 12:15 PM, Pavel Stehule wrote:

2010/12/17 Itagaki Takahiro:


It should be not a main subject, but I remember there was a discussion
that "IN ARRAY array-expression" looks redundant for a literal array:

  IN ARRAY ARRAY[1, 3, 5]

Are there any improvement for the issue?

yes. It know it. The reason for this is bigger space for possible
future features related to FOREACH loop.



So what you're saying is we need to allow ugliness now so we can have 
more ugliness in future? I don't find that a convincing argument. I 
share the dislike for this syntax.


cheers

andrew

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