Re: [HACKERS] Fwd: Have a problem with citext

[David E. Wheeler]

On Oct 1, 2017, at 20:22, Robert Haas  wrote:

>> Are permissions correct in the citext extension?
> 
> Not to be picky, but couldn't you investigate that a bit before posting here?

Normally I would, but my attention is far from Postgres these days, sadly, and 
I tend to think of citext (IT’S NOT SPELLED “CUTEST”, SIRI!) as part of the 
core, now, and I have forgotten more than I think I ever knew about it. Sorry.

D



signature.asc
Description: Message signed with OpenPGP

[HACKERS] Fwd: Have a problem with citext

[David E. Wheeler]

Hackers,

Are permissions correct in the citext extension?

Best,

David

> Begin forwarded message:
> 
> From: Sadek Touati 
> Subject: Have a problem with citext
> Date: September 29, 2017 at 17:02:50 EDT
> To: "da...@kineticode.com" 
> 
> Dear sir,
> I'm using the citext datatype in my application. I have PostgresSql 9.6 
> installed by EnterpriseDB
> 
> 
> psql mydatabase postgres
> create extension citext with schema myschema
> 
> \c mydatabase biguser
> 
> set search_path to myschema;
> 
> create table tst(v citext);
> insert into tst values('sadek');
> > select strpos(v, 'd') from tst;
> ERROR:  permission denied for function strpos
> 
> > select strpos(v, 'd'::citext) from tst; (If I read the documentation 
> > correctly this should work! alas, it doesn't)
> ERROR:  permission denied for function strpos
> 
> > select strpos(v::citext, 'd'::citext) from tst;
> ERROR:  permission denied for function strpos
> 
> > select strpos(v::citext, 'd') from tst;
> ERROR:  permission denied for function strpos
> 
> > select strpos(v::citext, 'd'::citext) from tst;
> ERROR:  permission denied for function strpos
> 
> > select strpos(v::text, 'd'::text) from tst;
>  strpos
> 
>   3
> (1 row)
> 
> Am I missing something here?
> 
> thanks in advance



signature.asc
Description: Message signed with OpenPGP

Re: [HACKERS] [BUGS] BUG #14825: enum type: unsafe use?

[David E. Wheeler]

On Sep 25, 2017, at 10:55, Andrew Dunstan  
wrote:

> Let's ask a couple of users who I think are or have been actually
> hurting on this point. Christophe and David, any opinions?

If I understand the issue correctly, I think I’d be fine with requiring ALTER 
TYPE ADD LABEL to be disallowed in a transaction that also CREATEs the type if 
it’s not currently possible to reliably tell when an enum was created in a 
transaction. Once you can do that, then by all means allow it!

My $2.

Best,

David



signature.asc
Description: Message signed with OpenPGP

Re: [HACKERS] Patch: Tie stats options to autovacuum in postgresql.conf

[David E. Wheeler]

On Sep 28, 2006, at 16:39, Jim C. Nasby wrote:


+1. I was just at a client today that had run into this problem.

Actually, I'm in favor of refusing to start if autovac is on but the
proper stats settings aren't. I'd rather that then people ending up  
with

bloated databases and crappy performance.


I agree, but I figured that this was a start, at least.

Best,

David

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

[HACKERS] Mysterious Bus Error with get_fn_expr_argtype()

[David E. Wheeler]

Howdy,

I'm trying to write a simple function that will return a string with  
the type name of a value. Unfortunately, it keeps dying on me. I don't  
even get any useful debugging information with --enable-cassert, just  
this:


LOG:  server process (PID 96946) was terminated by signal 10: Bus error
LOG:  terminating any other active server processes
LOG:  all server processes terminated; reinitializing

I stuck in a few calls to elog(), and it looks like this is the line  
that's choking:


typeoid = get_fn_expr_argtype(fcinfo-flinfo, 0);

But that's copied directly from enum.c. So I'm pretty mystified. Any  
help would be greatly appreciated.


Here's the complete code:

#include postgres.h
#include fmgr.h
#include utils/builtins.h

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

extern Datum type_of (PG_FUNCTION_ARGS);

Datum
type_of(PG_FUNCTION_ARGS)
{
Oidtypeoid;
Datum  result;
char   *typename;

typeoid = get_fn_expr_argtype(fcinfo-flinfo, 0);
if (typeoid == InvalidOid) {
ereport(
ERROR, (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(could not determine data type of argument to  
type_of())

)
);
}

typename = format_type_be(typeoid);
result = DirectFunctionCall1(textin, CStringGetDatum(typename));
PG_RETURN_DATUM(result);
}

And I load the function like so:

CREATE OR REPLACE FUNCTION type_of(anyelement)
RETURNS text
AS '$libdir/type_of'
LANGUAGE C STRICT IMMUTABLE;

Thanks,

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] Mysterious Bus Error with get_fn_expr_argtype()

[David E. Wheeler]

On Sep 1, 2008, at 16:55, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

Here's the complete code:


Looks like you forgot PG_FUNCTION_INFO_V1(), so what's being passed to
this isn't an fcinfo ...


Bah! I knew I had to be missing something really fundamental. Thanks  
Tom.


BTW, anyone have any interest in this function in core? Its purpose is  
to return a string identifying the data type of its argument. It's  
useful for dynamically building queries to pass to PL/pgSQL's EXECUTE  
statement when you don't know the data types of values you're putting  
into the statement.


Thanks,

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] Mysterious Bus Error with get_fn_expr_argtype()

[David E. Wheeler]

On Sep 1, 2008, at 22:31, Brendan Jurd wrote:


Oh, another thing: it shouldn't be STRICT.  Nulls have perfectly good
types.


Agreed.

Barring any further comments/objections, I'll go ahead and prepare a
patch to add this to core.


So it will return a text representation or an Oid?

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] Mysterious Bus Error with get_fn_expr_argtype()

[David E. Wheeler]

On Sep 2, 2008, at 08:58, David E. Wheeler wrote:


On Sep 1, 2008, at 22:31, Brendan Jurd wrote:

Oh, another thing: it shouldn't be STRICT.  Nulls have perfectly  
good

types.


Agreed.

Barring any further comments/objections, I'll go ahead and prepare a
patch to add this to core.


So it will return a text representation or an Oid?


Looks like regtype displays as an integer. So how about pg_regtypeof()  
and pg_typeof()?


PG_FUNCTION_INFO_V1(pg_regtypeof);

Datum
pg_regtypeof(PG_FUNCTION_ARGS)
{
PG_RETURN_OID(get_fn_expr_argtype(fcinfo-flinfo, 0));
}

PG_FUNCTION_INFO_V1(pg_typeof);

Datum
pg_typeof(PG_FUNCTION_ARGS)
{
Oidtypeoid;

typeoid = get_fn_expr_argtype(fcinfo-flinfo, 0);
if (typeoid == InvalidOid) {
ereport(
ERROR, (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(could not determine data type of argument to  
pg_typeof())

)
);
}

PG_RETURN_DATUM(CStringGetDatum(format_type_be(typeoid)));
}

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] Mysterious Bus Error with get_fn_expr_argtype()

[David E. Wheeler]

On Sep 2, 2008, at 10:43, David E. Wheeler wrote:

Looks like regtype displays as an integer. So how about  
pg_regtypeof() and pg_typeof()?


Sorry, make that:

PG_FUNCTION_INFO_V1(pg_regtypeof);

Datum
pg_regtypeof(PG_FUNCTION_ARGS)
{
PG_RETURN_OID(get_fn_expr_argtype(fcinfo-flinfo, 0));
}

PG_FUNCTION_INFO_V1(pg_typeof);

Datum
pg_typeof(PG_FUNCTION_ARGS)
{
Oidtypeoid;

typeoid = get_fn_expr_argtype(fcinfo-flinfo, 0);
if (typeoid == InvalidOid) {
ereport(
ERROR, (
errcode(ERRCODE_FEATURE_NOT_SUPPORTED),
errmsg(could not determine data type of argument to  
pg_typeof())

)
);
}

	PG_RETURN_DATUM(DirectFunctionCall1(textin,  
CStringGetDatum(format_type_be(typeoid;

}

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] Mysterious Bus Error with get_fn_expr_argtype()

[David E. Wheeler]

On Sep 2, 2008, at 11:06, Tom Lane wrote:


Better try that again.

regression=# select 1043::regtype;
 regtype
---
character varying
(1 row)

regression=#

I see no need for two functions here.


Oh. I tried:

try=# select 1::regtype;
 regtype
-
 1

I had assumed that 1 would be some type, but apparently not. Oops.

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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 4, 2008, at 21:40, Ryan Bradetich wrote:


Overall I think the patch looks good.   After reviewing the patch, I
played with
citext for an hour or so and I did not encounter any bugs or other  
surprises.


Thanks for the review, Ryan!

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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 5, 2008, at 11:30, Tom Lane wrote:


Thanks for reviewing.  I've committed this with your suggestions and
one additional non-cosmetic change: schema-qualify names in the
bodies of the SQL functions so that they are not search_path  
dependent.


Thanks, I'll check that out.


One thing that didn't make a lot of sense to me was the last new
function:

CREATE OR REPLACE FUNCTION translate( citext, citext, text ) RETURNS  
TEXT AS $$
   SELECT  
pg_catalog.translate( pg_catalog.translate( $1::pg_catalog.text,  
pg_catalog.lower($2::pg_catalog.text), $3),  
pg_catalog.upper($2::pg_catalog.text), $3);

$$ LANGUAGE SQL IMMUTABLE STRICT;

Why is it using upper()?


To make translate() work case-insensitively, it does two translates:  
One lowercase and one uppercase. This allows the translated value to  
be returned with its original casing in tact. No, this isn't ideal,  
but it was simple to do.


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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 5, 2008, at 11:33, David E. Wheeler wrote:


On Sep 5, 2008, at 11:30, Tom Lane wrote:


Thanks for reviewing.  I've committed this with your suggestions and
one additional non-cosmetic change: schema-qualify names in the
bodies of the SQL functions so that they are not search_path  
dependent.


Thanks, I'll check that out.


Finally got to this; sorry for the delay.

Two things I noticed:

1. Did I neglect to include the documentation patch? I've attached it  
here. It's necessary because of the addition of the new functions.


2. Many thanks for switching to using the network_show function  
instead of the SQL-based casting I had. Can you tell me how to go  
about finding such functions? Because for my 8.3 version of citext, I  
have a whole bunch of functions that do casting like this:


CREATE OR REPLACE FUNCTION int8(citext)
RETURNS int8
AS 'SELECT int8( $1::text )'
LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext(int8)
RETURNS citext
AS 'SELECT text( $1 )::citext'
LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION int4(citext)
RETURNS int4
AS 'SELECT int4( $1::text )'
LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext(int4)
RETURNS citext
AS 'SELECT text( $1 )::citext'
LANGUAGE SQL IMMUTABLE STRICT;

...and so on. I'd love to be able to replace these (and many others)  
with internal C functions, if  only I could figure out what those  
functions were. A pointer to making that determination (if they even  
exist in 8.3) would be greatly appreciated.


Thanks,

David



citext_doc.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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 12, 2008, at 10:58, Tom Lane wrote:


1. Did I neglect to include the documentation patch? I've attached it
here. It's necessary because of the addition of the new functions.


Maybe it got left out of the later patch iterations?  Anyway,
will take care of it.


Great, thank you.


2. Many thanks for switching to using the network_show function
instead of the SQL-based casting I had. Can you tell me how to go
about finding such functions?


Er, look into pg_cast and then pg_proc?  For instance

select oid::regprocedure, prosrc from pg_proc
where oid in (select castfunc from pg_cast);


That looks like *exactly* what I need. Thanks!

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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 12, 2008, at 11:06, David E. Wheeler wrote:


Er, look into pg_cast and then pg_proc?  For instance

select oid::regprocedure, prosrc from pg_proc
where oid in (select castfunc from pg_cast);


That looks like *exactly* what I need. Thanks!


Pity. Looks like there were only a few I wasn't using, text_char,  
char_text, text_name, and texttoxml. Do I really need to keep all my  
other casts like these in 8.3?


CREATE OR REPLACE FUNCTION int8(citext)
RETURNS int8
AS 'SELECT int8( $1::text )'
LANGUAGE SQL IMMUTABLE STRICT;

CREATE OR REPLACE FUNCTION citext(int8)
RETURNS citext
AS 'SELECT text( $1 )::citext'
LANGUAGE SQL IMMUTABLE STRICT;

Thanks,

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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 12, 2008, at 11:14, David E. Wheeler wrote:

Pity. Looks like there were only a few I wasn't using, text_char,  
char_text, text_name, and texttoxml.


Oh, and text_name seems to give me this error:

 ERROR:  compressed data is corrupt

That's when I have this cast:

 CREATE OR REPLACE FUNCTION citext(name)
 RETURNS citext
 AS 'text_name'
 LANGUAGE internal IMMUTABLE STRICT;

This version does not give me an error:

 CREATE OR REPLACE FUNCTION citext(name)
 RETURNS citext
 AS 'SELECT text( $1 )::citext'
 LANGUAGE SQL IMMUTABLE STRICT;

Maybe I did something wrong?

Thanks,

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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 12, 2008, at 11:31, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

Oh, and text_name seems to give me this error:



 ERROR:  compressed data is corrupt



That's when I have this cast:



 CREATE OR REPLACE FUNCTION citext(name)
 RETURNS citext
 AS 'text_name'
 LANGUAGE internal IMMUTABLE STRICT;


I think you've got the direction backwards.


Oh. Duh.


BTW, I removed the Limitations entry about I/O casting not working
with citext; we fixed that, no?


Yes, we did. Thanks for the catch.

I've got another patch I'm working on adding support for char (and  
tests for char). Just to fill out a gap I saw in the casting coverage.  
I'm trying to get it done now. With that, AFAIK, citext will work just  
like text.


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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 12, 2008, at 11:34, Tom Lane wrote:


CREATE OR REPLACE FUNCTION int8(citext)
RETURNS int8
AS 'SELECT int8( $1::text )'
LANGUAGE SQL IMMUTABLE STRICT;


Yeah, those are all replaced by the CoerceViaIO mechanism


Okay, thanks for the sanity check. The SQL versions are fine for me in  
8.3.


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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 12, 2008, at 11:35, David E. Wheeler wrote:

I've got another patch I'm working on adding support for char (and  
tests for char). Just to fill out a gap I saw in the casting  
coverage. I'm trying to get it done now. With that, AFAIK, citext  
will work just like text.


Looks like the IO conversions handle char and char, so the attached  
patch just updates the regression test.


Best,

David


char_casts.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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

On Sep 12, 2008, at 12:49, Alvaro Herrera wrote:


Looks like the IO conversions handle char and char, so the attached
patch just updates the regression test.


There are unresolved conflicts in the patch ...


Bah! Sorry. Let me try that again.

Best,

David


char_tests.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] Do we really need a 7.4.22 release now?

[David E. Wheeler]

On Sep 18, 2008, at 07:38, Tom Lane wrote:


I wasn't intending to start a discussion about how/when to EOL 7.4,
but since the thread has gone in that direction: my vote would be to
announce now (say, with the announcement of this set of releases) that
7.4 will be EOL'd with our first set of updates in 2009.  That would
probably be the next update after this one, maybe two updates away
if we find any really serious bugs in the next month or two.


+1

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

[HACKERS] Where to Host Project

[David E . Wheeler]

Howdy,

Not *exactly* hackers-related, but I wanted to get a feel for this  
from those who are likely to use project hosting, and to minimize the  
chances of a flame war.


Right now I have pgTAP on pgFoundry, which is okay, though it appears  
to be largely unmaintained. PostgreSQL module projects seem to mainly  
just flounder there.


So I'm wondering, given the various discussions of PostgreSQL module  
hosting in the past, where would be a good place to put a PostgreSQL  
module project? The things I would like to have are:


  * SVN or git hosting (I've not used git, but would try it)
  * Ability to hand out commit bits to other folks
  * A project home page and/or wiki
  * Good search results rankings in Google et al.
  * Mail lists
  * Bug tracking
  * Release management

Overall, it should be easy to find my project, and easy to download it  
and build it for PostgreSQL. I've had the following suggestions for  
places to try, in addition to pgFoundry:


  * github
  * Google Code
  * LaunchPad
  * WebFaction

I've not used any of these. So my question is, what do you prefer for  
third-party PostgreSQL modules. Where is it that the the PostgreSQL  
community is likely to aggregate with its modules?


Thanks!

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] Where to Host Project

[David E. Wheeler]

On Sep 18, 2008, at 11:27, Joshua Drake wrote:


  * LaunchPad



Is backed by PostgreSQL. It is the only logical choice :). Seriously
though it is a good service.


Looks pretty nice, though it doesn't have project home pages. Having  
just created one for pgTAP on pgFoundry, I'd like to keep it. :-)


I posted a question about this to see if it's in the plans:

  https://answers.launchpad.net/launchpad/+question/45640

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] Where to Host Project

[David E. Wheeler]

On Sep 18, 2008, at 18:43, Robert Treat wrote:


   * Google Code


does not offer mailing lists


I get mail for the test-more project there. It's through Google  
Groups, which is a little weird, but works.



  * LaunchPad


does not offer svn or git, and i think they dont offer a home page  
service


It uses Bazaar. WTF is that? I've never heard of it.


  * WebFaction


dont really know anything about these guys, but i thought they did web
hosting, not project hosting.


Yeah, looks that way.

Just for the record, you have overlooked SourceForge. While it  
appears to
fallen out of favor with the open source crowd, it is the one  
service that

does provide everything you wanted.


Good point. I've not used it in years. Last time I looked the mail  
archives still sucked pretty hard. Otherwise, now that it has SVN, and  
if it has eliminated the performance problems, it might just do the  
trick.


I've been saying for some time now we need to get out of the project  
hosting
service, and get into the project directory service. What we really  
want is
to make it easy for people to find postgresql related projects,  
regardless of

where they are.


That's an excellent idea. Do you have a plan for this?

Thanks,

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] Where to Host Project

[David E. Wheeler]

On Sep 18, 2008, at 19:01, Alvaro Herrera wrote:


Why not host the code on (say) GitHub, and the rest of the stuff on
pgFoundry?


That's kind of what I'm doing now. But I'm wondering if I should  
bother with pgFoundry at all. It seems pretty dead (see Josh Berkus's  
reply).


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] Where to Host Project

[David E. Wheeler]

On Sep 19, 2008, at 01:25, Dimitri Fontaine wrote:


There's a french non-profit team offering those:
 http://tuxfamily.org/en/main

You can even take their open source hosting facility software and  
offer your
own services based on it, and/or extend their perl code to add new  
features.

I tried to talk pgfoundry admins into this solution in the past, but I
understand maintaining pgfoundry is a PITA.


Looks pretty interesting. I've never heard of it. Anyone else have  
experience with it?


Thanks,

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] Where to Host Project

[David E. Wheeler]

On Sep 22, 2008, at 10:08, Stefan Kaltenbrunner wrote:

The machine is ready to go and as far as I know even has a jail.  
Stefan

would know more.

OK, cool. Stefan; what's your take on where we're at?


yeah there is a box and a jail I set up a while ago but for various  
reasons the actual migration (planning and testing) never happened.
I'm still prepared to handle the required sysadmin level work but I  
don't have time for anything more fancy right now.


If this upgrade happens, and I can use SVN with pgFoundry, that's  
exactly where I'll stay. That would make me happy.


Whether or not it was a good idea to get into the hosting business,  
since we do, as a community, have a hosting platform, it behooves us  
to try to keep it up-to-date. I'd be willing to give a bit of time for  
this.


But I do agree with Robert that we *should* get into the indexing  
business. This is CPAN's secret: It doesn't host anything, but  
provides a distributed index of Perl modules. What would be useful is  
to make it easy for people to add their stuff to the index; and if  
that could be automated with pgFoundry, so much the better for those  
who host there.


My $0.02. Thanks for the discussion, folks.

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

[HACKERS] Upgrading pgFoundry (Was: Where to Host Project)

[David E . Wheeler]

On Sep 24, 2008, at 14:02, Joshua Drake wrote:

I think one problem we have right now, is nobody knows what it is  
going

to take. I would expect that our current version is sufficiently old
enough to cause some migration pain?

I know we have two members willing to help that are not Stefan and I.
Which is good, but this doesn't appear to be a small project.


Does anyone know what needs to be done? If so, and you'd like to reply  
with a list of tasks, I can put in a little time this week and maybe  
next starting down that road. If it's a big job, I likely can't do it  
all myself, but I'm certainly happy to help out!


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] Bug in ILIKE?

[David E. Wheeler]

On Sep 25, 2008, at 20:45, Tom Lane wrote:


Well, there are two possible interpretations: (1) it's a bug, or (2)
it's an intentional, about-to-be-documented feature that  
backslashing a

letter makes it case-sensitive in ILIKE.

I do not care for interpretation #2 ... especially in view of your
observation (confirmed here) that pre-8.3 releases didn't do this.
I think it's just a bug in 8.3.


+1 I think it would be very difficult to come up with a justification  
for backslashes making a comparison case-sensitive. It's just…weird.


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] Ad-hoc table type?

[David E. Wheeler]

On Sep 28, 2008, at 17:46, Tom Lane wrote:


BTW, I think it is (or should be) possible to create an index on
hstore-'mycol', so at least one of the reasons why you should *need*
to switch to a real database column seems bogus.


The docs say:

  titleIndexes/title

  para
   typehstore/ has index support for literal@gt;/ and  
literal?/
   operators.  You can use either GiST or GIN index types.  For  
example:

  /para
  programlisting
CREATE INDEX hidx ON testhstore USING GIST(h);

CREATE INDEX hidx ON testhstore USING GIN(h);
  /programlisting

I'm not sure what that means. Can you create normal btree or hash  
indexes on hstore columns? And is the index useful for both `@` and `? 
`?


Thanks,

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] Ad-hoc table type?

[David E. Wheeler]

On Sep 28, 2008, at 23:46, [EMAIL PROTECTED] wrote:

I'm not sure what that means. Can you create normal btree or hash  
indexes

on hstore columns? And is the index useful for both `@` and `?`?


That means that those operations are supported by a GiST (or GIN)  
index,

that is:

 find the records where col contains 'foo = 1, bar = 2'

is supported by the index. Likewise for is contained in and has  
key.

It's a bit like having mini-indexes on all keys (although I guess not
that efficient). Pretty cool, I'd say.


Yeah, that does sound good. I look forward to having an excuse for  
playing with this type…


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] [Review] Tests citext casts by David Wheeler.

[David E. Wheeler]

Just want to make sure that this wasn't lost in the shuffle somewhere…

Best,

David
On Sep 14, 2008, at 15:42, David E. Wheeler wrote:


On Sep 12, 2008, at 12:49, Alvaro Herrera wrote:

Looks like the IO conversions handle char and char, so the  
attached

patch just updates the regression test.


There are unresolved conflicts in the patch ...


Bah! Sorry. Let me try that again.

Best,

David




char_tests.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] \ef should probably append semicolons

[David E. Wheeler]

On Oct 10, 2008, at 20:27, Joshua Tolley wrote:


Now, if you want to fix psql so that even with a semicolon there it
will redisplay the command buffer and wait for a return, then I'd  
agree
that that's an improvement.  I couldn't figure out how to get  
readline

to cooperate with that ... but I didn't spend a lot of time looking.


Mmm... but that's so much harder than a 2 line patch :)  Thanks for
commenting. I may consider that in the (admittedly unlikely) event I
feel like getting personal with readline.


Not to mention libedit! :-)

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

[HACKERS] Version Number Function?

[David E. Wheeler]

Howdy,

Any interest in adding a function like this to core?

Datum
pg_version(PG_FUNCTION_ARGS)
{
PG_RETURN_INT32(PG_VERSION_NUM);
}


That returns an integer, such as

try=# select pg_version();
 pg_version

  80304
(1 row)

I've whipped this up for pgtap, as it'll be useful for determing when  
to skip tests based on a version of PostgreSQL, but I thought it might  
be generally useful enough to add to core.


Thoughts?

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] Version Number Function?

[David E. Wheeler]

On Oct 11, 2008, at 19:57, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

Any interest in adding a function like this to core?


No, because it's already there: see show server_version_num.

(It's probably worth noting that none of our existing clients that  
would

have any use for this information look at server_version_num, because
it's only available in 8.2 and up.  A function introduced as of 8.4
would be an additional two major releases behind the curve.)


Yeah, but I want to use it in WHERE clauses or CASE statements. I'm  
fine to just include it in pgtap, though.


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] Version Number Function?

[David E. Wheeler]

On Oct 12, 2008, at 11:21, Magnus Hagander wrote:

Yeah, but I want to use it in WHERE clauses or CASE statements. I'm  
fine

to just include it in pgtap, though.


You could do:
select setting from pg_settings where name='server_version_num';

(wrapped in the appropriate subselect to use it in a WHERE clause)


Right, but I want to make it as simple as possible for test writers to  
use.


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] Version Number Function?

[David E. Wheeler]

On Oct 12, 2008, at 12:42, Tom Lane wrote:


Yeah, but I want to use it in WHERE clauses or CASE statements.


current_setting('server_version_num')


Hrm. That's nice. I don't suppose there's any way to get something  
like that in 8.1 and earlier? I was going to fake it in the .c file.


Thanks,

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] Version Number Function?

[David E. Wheeler]

On Oct 12, 2008, at 14:11, Tom Lane wrote:


You'd have to parse the result of version().


As I figured. This is what I'm trying:

pg_version_num(PG_FUNCTION_ARGS)
{
#ifdef PG_VERSION_NUM
PG_RETURN_INT32(PG_VERSION_NUM);
#else
/* Code borrowed from dumputils.c. */
int cnt;
int vmaj,
vmin,
vrev;

cnt = sscanf(PG_VERSION, %d.%d.%d, vmaj, vmin, vrev);

if (cnt  2)
return -1;

if (cnt == 2)
vrev = 0;

PG_RETURN_INT32( (100 * vmaj + vmin) * 100 + vrev );
#endif

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] A small note about the difficulty of managing subprojects

[David E. Wheeler]

On Oct 12, 2008, at 20:15, Joshua D. Drake wrote:

Yeah I would agree with that. I find that only real issue with  
PgFoundry is the PgFoundry (aka Gforge) not the quality of the  
projects being hosted.


The other thing that could use some love is searching for projects.  
Google doesn't rank pgFoundry stuff very highly, and Gforge's search  
functionality leaves something to be desired. As a lover of CPAN, I  
have to say that I don't use CPAN itself all that much; rather, I use  
search.cpan.org, which makes it dead easy to search for modules that  
have functionality I'm looking for.


But improving search should come after fixing/upgrading Gforge, IMHO.

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

[HACKERS] Year 2038 Bug?

[David E. Wheeler]

Howdy,

Not sure if PostgreSQL uses time_t, but if so, this project provides  
useful code (a drop-in replacement for time.h) to address the 2038 bug  
on 32-bit platforms.


  http://code.google.com/p/y2038/

Useful for PostgreSQL?

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] Year 2038 Bug?

[David E. Wheeler]

On Oct 13, 2008, at 11:01, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

Not sure if PostgreSQL uses time_t,


We got rid of that some time ago.


Probably no problem, then. Do dates in PostgreSQL work for their  
entire documented ranges on 32bit processors?


Thanks,

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] Year 2038 Bug?

[David E. Wheeler]

On Oct 13, 2008, at 11:13, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

Probably no problem, then. Do dates in PostgreSQL work for their
entire documented ranges on 32bit processors?


As long as the C compiler supports int64 ...


I was afraid you'd say that. See:

  http://code.google.com/p/y2038/wiki/WhyBother

Especially the 64 bit CPU doesn't mean 2038 clean section. Again,  
maybe this doesn't apply to PostgreSQL; I'm just doing a bit of  
diligence. :-)


Cheers,

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] Year 2038 Bug?

[David E. Wheeler]

On Oct 13, 2008, at 11:22, Zdenek Kotala wrote:

PostgreSQL 8.4 uses 64bit data type for time. But if you use system  
timezone then you can get in trouble if system does not support  
64bit zic files.


I've never noticed a problem with the TZinfo database that ships on  
systems I've used. How would I know that there was a problem? What  
sort of trouble could I get into?


Thanks,

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] Year 2038 Bug?

[David E. Wheeler]

On Oct 13, 2008, at 11:24, Andrew Chernow wrote:

PostgreSQL doesn't use the standard time_t and time functions for  
its timestamp types.  Therefore, any limitations in regards to 64- 
bit time_t values on 32-bit platforms don't apply; other than the  
limitation Tom spoke of ... no 64-bit int.


Gotcha, thanks for the clarification.

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] Year 2038 Bug?

[David E. Wheeler]

On Oct 13, 2008, at 11:37, Zdenek Kotala wrote:

when you use --with-system-tzdata and run make check on head it  
fails on systemes without 64bit tzinfo support.


Oh. Is it not preferable to use the tzdata that ships with PostgreSQL?

Thanks,

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] Year 2038 Bug?

[David E. Wheeler]

On Oct 13, 2008, at 11:53, Tom Lane wrote:

Oh. Is it not preferable to use the tzdata that ships with  
PostgreSQL?


Not necessarily; the system might have a more up-to-date tzdata.


Gotcha.

Generally you'd use --with-system-tzdata on a platform where you  
expect

to receive routine package updates for the tzdata files, independently
of the Postgres release cycle.  It seems reasonable to assume that
anyone currently shipping tzdata is offering 64-bit files.  (But we do
have that regression test check in there to make sure.)


Understood. Thanks for the explanation (and the regression test!).

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] Year 2038 Bug?

[David E. Wheeler]

On Oct 13, 2008, at 12:35, Zdenek Kotala wrote:


Tom Lane napsal(a):

Generally you'd use --with-system-tzdata on a platform where you  
expect
to receive routine package updates for the tzdata files,  
independently

of the Postgres release cycle.  It seems reasonable to assume that
anyone currently shipping tzdata is offering 64-bit files.  (But we  
do

have that regression test check in there to make sure.)


Unfortunately, you are not correct here :( see:

http://bugs.opensolaris.org/view_bug.do?bug_id=4246033


So ideally all OS venders would ship 64-bit tzdata files, eh?

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] Version Number Function?

[David E. Wheeler]

Well, the C version I borrowed from dumpitils seems to work great. Any  
reason I shouldn't stay with that?


Best,

David

Sent from my iPhone

On Oct 14, 2008, at 7:44, Hannu Krosing [EMAIL PROTECTED] wrote:


On Sun, 2008-10-12 at 14:39 -0700, David E. Wheeler wrote:

On Oct 12, 2008, at 14:11, Tom Lane wrote:


You'd have to parse the result of version().


As I figured. This is what I'm trying:


if performance is not critical, then you could use this:

hannu=# create or replace function pg_version_num() returns int  
language

SQL as $$
 select
 1 *
 cast(substring(version()
from
   '^PostgreSQL +([0-9]+)[.][0-9]+[.][0-9]+ +') as int)
 +
 100 *
 cast(substring(version()
from
   '^PostgreSQL +[0-9]+[.]([0-9]+)[.][0-9]+ +') as int)
 +
 cast(substring(version()
from
   '^PostgreSQL +[0-9]+[.][0-9]+[.]([0-9]+) +') as int);
$$;
CREATE FUNCTION

hannu=# select pg_version_num();
pg_version_num

 80303
(1 row)


pg_version_num(PG_FUNCTION_ARGS)
{
#ifdef PG_VERSION_NUM
PG_RETURN_INT32(PG_VERSION_NUM);
#else
/* Code borrowed from dumputils.c. */
   intcnt;
   intvmaj,
   vmin,
   vrev;

   cnt = sscanf(PG_VERSION, %d.%d.%d, vmaj, vmin, vrev);

   if (cnt  2)
   return -1;

   if (cnt == 2)
   vrev = 0;

   PG_RETURN_INT32( (100 * vmaj + vmin) * 100 + vrev );
#endif

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] Version Number Function?

[David E. Wheeler]

On Oct 14, 2008, at 08:33, David E. Wheeler wrote:

Well, the C version I borrowed from dumpitils seems to work great.  
Any reason I shouldn't stay with that?


Also, here's a simpler SQL version, for those following along at home:

create or replace function pg_version_num() returns int language
SQL as $$
SELECT SUM(
(string_to_array(current_setting('server_version'), '.')) 
[i]::int

* CASE i WHEN 1 THEN 1 WHEN 2 THEN 100 ELSE 1 end
)::int FROM generate_series(1, 3) AS gen(i);
$$;
CREATE FUNCTION

There must be a way to get string_to_array() to evaluate only once, yes?

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] spoonbill is failing citext test

[David E. Wheeler]

On Oct 14, 2008, at 12:36, Alvaro Herrera wrote:

I don't know if you have noticed, but the spoonbill buildfarm member  
is
failing the citext test.  Unfortunately the regression diff is not  
very

helpful:

Binary files /home/pgbuild/pgbuildfarm/HEAD/pgsql.2397/contrib/ 
citext/expected/citext.out and /home/pgbuild/pgbuildfarm/HEAD/pgsql. 
2397/contrib/citext/results/citext.out differ


http://buildfarm.postgresql.org/cgi-bin/show_stage_log.pl?nm=spoonbilldt=2008-10-14%20100509stg=contrib-install-check

Maybe we should pass -a to diff so that it displays the difference  
even

if it thinks the file is binary.


What does the diff look like now?

Thanks,

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] spoonbill is failing citext test

[David E. Wheeler]

On Oct 14, 2008, at 14:00, Stefan Kaltenbrunner wrote:

well what we are looking at here are actually two issues - one is  
the regression failure - diff -a produces:


http://www.kaltenbrunner.cc/files/citext.diff.txt


Am I reading that right? Is there really just an issue of a different  
number of spaces before the caret in that error message? Seems kinda  
weird…


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] Version Number Function?

[David E. Wheeler]

On Oct 14, 2008, at 14:32, Hannu Krosing wrote:


On Tue, 2008-10-14 at 08:33 -0700, David E. Wheeler wrote:
Well, the C version I borrowed from dumpitils seems to work great.  
Any

reason I shouldn't stay with that?


SQL is the only PL available by default, no need to compile or  
install

anything.

It can be written more effectively in almost any other pl, and  
probably

in SQL as well ;)


Yes, but I'm putting this into pgTAP, where I already have some C  
functions I'm defining, so another won't hurt any. :-)


Thanks,

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] Version Number Function?

[David E. Wheeler]

On Oct 14, 2008, at 14:50, Hannu Krosing wrote:


Was current_setting('server_version') available in 8.1 ?


Yes. In 8.0, too.

There must be a way to get string_to_array() to evaluate only once,  
yes?


SELECT s.a[1]::int * 1 + s.a[2]::int * 100 + s.a[3]::int
FROM
(SELECT string_to_array(current_setting('server_version'), '.') AS a)
AS s;


Oh, duh. Even better!

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] So what's an empty array anyway?

[David E. Wheeler]

On Oct 21, 2008, at 12:08, Simon Riggs wrote:


Please remove zero-dimension arrays.

The number of dimensions of an empty array really ought to be NULL, or
if we fix it to be non-NULL then 1+. Zero just makes a weird case  
for no

reason. An empty string only makes sense in the context of that
particular function, it doesn't really help with other maths.


If we got rid of zero dimension arrays, how would I declare a new  
empty array in a PL/pgSQL function?


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] So what's an empty array anyway?

[David E. Wheeler]

On Oct 21, 2008, at 13:00, Andrew Chernow wrote:


On Oct 21, 2008, at 12:08, Simon Riggs wrote:
If we got rid of zero dimension arrays, how would I declare a new  
empty array in a PL/pgSQL function?


Why would you want to do that?  Is there a use case for that?


Perhaps not. In older versions of PostgreSQL, I *had* to initialize an  
empty array in a DECLARE block or else I couldn't use it with  
array_append() to collect things in an array in a loop. I don't have  
to do so 8.3, but I keep it that way in some modules for compatibility  
reasons.


But since that was perhaps an issue with older versions of PostgreSQL  
that has since been addressed, I guess I just think too much like a  
Perl hacker, where I can add things to an array as I need to. That's  
different from SQL arrays, where you can't add a value to an existing  
array, create a new array from an old one plus a new value.


So I guess I don't *have* to have it, but for compatibility with older  
versions of PostgreSQL, I think they should be kept.


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] So what's an empty array anyway?

[David E. Wheeler]

On Oct 21, 2008, at 13:58, Tom Lane wrote:


If we got rid of zero dimension arrays, how would I declare a new
empty array in a PL/pgSQL function?


Same as before, I think: initialize it to '{}'.  What's at stake here
is exactly what does that notation mean ...


An empty, single-dimension array. But I got the impression from Simon  
that he thought it should be NULL.


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] So what's an empty array anyway?

[David E. Wheeler]

On Oct 21, 2008, at 14:16, Tom Lane wrote:

Well, we can't do that because it would clearly break too much  
existing

code.  '{}' has got to result in something you can successfully
concatenate more elements to.


Right, that's what I was trying to day. Badly, I guess.


But either the current behavior with
a zero-dimension array, or a one-dimensional length-zero array would
presumably work okay.


Right, that sounds right to me.

Thanks,

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] So what's an empty array anyway?

[David E. Wheeler]

On Oct 22, 2008, at 00:40, Simon Riggs wrote:


An empty, single-dimension array. But I got the impression from Simon
that he thought it should be NULL.


I meant the dimension of {} should be NULL.


To me that's 0.

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

[HACKERS] ERROR: cache lookup failed for function 0

[David E . Wheeler]

[Re-sending to hackers, since the original message to pgsql-bugs has  
not been approved for delivery in the last four days…apologies for any  
duplicates.]


Howdy,

I ran into this error on 8.2 a while ago, and just figured out what  
was causing it. Here's a quick example on 8.2:


BEGIN;

-- Compare name[]s more or less like 8.3 does.
CREATE OR REPLACE FUNCTION namearray_text(name[])
RETURNS TEXT AS 'SELECT textin(array_out($1));'
LANGUAGE sql IMMUTABLE STRICT;

CREATE CAST (name[] AS text) WITH FUNCTION namearray_text(name[]) AS  
IMPLICIT;


CREATE OR REPLACE FUNCTION namearray_eq( name[], name[] )
RETURNS bool
AS 'SELECT $1::text = $2::text;'
LANGUAGE sql IMMUTABLE STRICT;

CREATE OPERATOR = (
   LEFTARG= name[],
   RIGHTARG   = name[],
   NEGATOR= ,
   PROCEDURE  = namearray_eq
);

SELECT '{foo}'::name[]  '{bar}'::name[];

ROLLBACK;

If you comment out the NEGATOR line, the error is changed to the more  
useful


 ERROR:  operator is not unique: name[]  name[]

I'm assuming that, if you did this for 8.3 (which has name[]  
comparison operators in core, so it'd have to be an operator with some  
other type), you'd get the same useless error.


Ideally, in the situation where a NEGATOR (or commutator, too?) is  
specified but has not actually been defined, you'd get an error such as:


 ERROR:  operator not defined: name[]  name[]

Thanks,

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] pg_typeof() patch review

[David E. Wheeler]

On Nov 3, 2008, at 1:28 AM, Kurt Harriman wrote:


2) func.sgml: clarifying that the function returns an OID rather
  than a string


Actually, it returns a regtype, no?

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] pg_typeof() patch review

[David E. Wheeler]

On Nov 3, 2008, at 10:02 AM, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

On Nov 3, 2008, at 1:28 AM, Kurt Harriman wrote:

2) func.sgml: clarifying that the function returns an OID rather
than a string



Actually, it returns a regtype, no?


I thought the description was good, because it emphasizes that the
result is-a OID; the table entry says regtype but people might not
realize that that means they can use it as, eg, something to compare
to pg_attribute.atttypid.


Well, as someone who was until recently unfamiliar with regtypes, and  
who thinks of an OID as essentially just a number, I would find it  
very useful if the description indicated that, as a regtype, the  
return value could be used as either an OID or as string. Otherwise,  
I'd find the description kind of confusing (in one place it says it  
returns a regtype, whatever *that* is, and in one place it says an  
OID).


Just thinking at this from the point of view of a relative newbiew…

Thanks,

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] pg_typeof() patch review

[David E. Wheeler]

On Nov 3, 2008, at 10:52 AM, Alvaro Herrera wrote:


Give this a read
http://www.postgresql.org/docs/8.3/static/datatype-oid.html


Yeah.

Maybe we should link to this page in the pg_typeof() description.   
Also,

perhaps this page needs more examples.


Yes, both of those would help a lot, I think.

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] Tests citext casts

[David E. Wheeler]

On Nov 5, 2008, at 6:40 AM, Kenneth Marshall wrote:


I installed and ran the citext tests both with and without
the patch and had failures both times. The patch applied
cleanly and the make;make install completed without errors.
I have attached the two regression.diffs files, one without
the patch applied and the other with the patch.


What patch was it you applied? And is this CVS HEAD that you're  
testing? What locale/collation is your database configured with?


Thanks,

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: [RRR] [HACKERS] Tests citext casts

[David E. Wheeler]

On Nov 5, 2008, at 12:34 PM, Kenneth Marshall wrote:


I am using the anonymous CVS repository, it returns the following
information in pg_catalog.pg_settings:


What is lc_collate set to?

% show lc_collate;

FWIW, I just ran the tests myself and all passed, with and without the  
patch (using en_US.UTF-8). I think that the regression tests generally  
expect to be run with the C locale, though en_US generally works fine,  
too, given that ASCII ordering has the same semantics.


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] pg_typeof() patch review

[David E. Wheeler]

On Nov 3, 2008, at 11:15 AM, Tom Lane wrote:


David E. Wheeler [EMAIL PROTECTED] writes:

On Nov 3, 2008, at 10:52 AM, Alvaro Herrera wrote:

Maybe we should link to this page in the pg_typeof() description.
Also,
perhaps this page needs more examples.



Yes, both of those would help a lot, I think.


Feel free to send in a docs patch ...


Well, I wasn't sure of the appropriate place to add examples to  
datatype.sgml. But this patch would certainly make the output of  
pg_typeof() much clearer to newbies like me.


Thanks,

David



pg_typeof_doc.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: [RRR] [HACKERS] Tests citext casts

[David E. Wheeler]

On Nov 7, 2008, at 10:43 AM, Kenneth Marshall wrote:


Thank you for the pointers. lc_collate is set to en_US.UTF-8.


Huh. Same as for me.


I re-initdb the database with the --no-locale option and then the
tests passed successfully. Thank you for the reminder that the
regression tests need to run against a C locale database.


Great, thank you!

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: [RRR] [HACKERS] Tests citext casts

[David E. Wheeler]

On Nov 7, 2008, at 11:15 AM, Tom Lane wrote:

In a quick test on a Fedora box, citext is the only core or contrib  
test
that fails in en_US.  (This is true in HEAD, even without having  
applied

the proposed patch.)  It would be good to clean that up.


Huh. There must be something different about the collation for en_US  
on Fedora than there is for darwin (what I'm using), because for me,  
as I said, all tests pass. It's just ASCII, though, so I don't know  
why it would be any different.



We could fix it by having multiple variant expected files for C and
non-C locales, which is exactly what the core tests do.  However,
I'm loath to apply that approach when the citext test already has  
XML vs

no-XML variants; we would then need two variant files per locale
variant, which is a bit unreasonable from a maintenance standpoint.


This is why I like TAP.

My inclination is to remove the XML-dependent citext tests, which  
don't

seem especially useful, and then we can have whatever variants we need
for locales.  citext locale behavior seems much more interesting than
testing whether it casts to xml or not.


Agreed, but I admit to being mystified as to why things would be  
sorting any differently on darwin vs. Fedora. I kept everything in  
ASCII, on your advice, to keep from having to deal with crap like this.


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: [RRR] [HACKERS] Tests citext casts

[David E. Wheeler]

On Nov 7, 2008, at 11:50 AM, Tom Lane wrote:


This is why I like TAP.


And how would TAP reduce the number of expected results?


TAP doesn't compare output to expected output files. It's simply a  
test result output stream. A separate program then harnesses that  
output, looks at what passed and what failed, and emits a report. So  
you only have to maintain one file of tests. It makes test-driven  
development a lot simpler, not to mention enabling better conditional  
testing, TODO tests, skipping tests, etc.


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: [RRR] [HACKERS] Tests citext casts

[David E. Wheeler]

On Nov 7, 2008, at 12:12 PM, Tom Lane wrote:


... and you have very limited visibility into what went wrong, if
anything goes wrong.  That's not real attractive for the buildfarm
environment.  I like being able to see the actual query output.


It depends on how you write it - you can add a lot of descriptive  
information about what's being tested in each assertion. To me, the  
results are the most important, and I can look in the test file for  
the troubling code.


Anyway, we all like what we're used to, I guess.

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] pg_typeof() patch review

[David E. Wheeler]

On Nov 7, 2008, at 2:55 PM, Tom Lane wrote:


Well, I wasn't sure of the appropriate place to add examples to
datatype.sgml. But this patch would certainly make the output of
pg_typeof() much clearer to newbies like me.


Applied with some further editorialization.


Thanks!

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: [RRR] [HACKERS] Tests citext casts

[David E. Wheeler]

On Nov 7, 2008, at 3:18 PM, Tom Lane wrote:


Agreed, but I admit to being mystified as to why things would be
sorting any differently on darwin vs. Fedora. I kept everything in
ASCII, on your advice, to keep from having to deal with crap like  
this.


Patch applied with this adjustment.


Great. So does it now pass all tests on Fedora?

Thanks,

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] gram.y=preproc.y

[David E. Wheeler]

On Nov 10, 2008, at 8:03 AM, Tom Lane wrote:

We should probably standardize on the perl version, ugly or not,  
because

otherwise we'll have a difference in build process between Unix and
Windows machines.  Personally I don't really care how ugly it is as  
long
as no one has to look at it ;-) ... but if someone wants to beautify  
the

perl script they're surely welcome to do so.


I'd be happy to, but I haven't really been following this thread. What  
does it do, and how do I make sure it continues to work as I refactor  
it?


I'm fine to wait until it's committed, too.




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] gram.y=preproc.y

[David E. Wheeler]

On Nov 10, 2008, at 11:17 AM, Andrew Dunstan wrote:

David, how is your awk-fu? If it's any good then I'm happy to leave  
it to you. Otherwise I will try to make a few hours somewhere to un- 
uglify this.


My Perl is excellent, but my awk is remedial. What part does awk play?

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] TODO list request: FK to unique expression indexes

[David E. Wheeler]

On Nov 19, 2008, at 9:12 AM, Josh Berkus wrote:


Folks,

Since it's too late to look at this for 8.4, can the following go on  
the TODO list?


Referential Integrity

[] Allow creation of FKs targeting unique expression indexes on the  
referenced table.  Syntax: REFERENCES reftable ( ( column  
expression ) )


Reason: current FK rules do not allow creating FKs to columns which  
are defined as, for example, unique(lower(column)).  This forces  
users to either abandon RI for that table, to store duplicate data,  
or create superfluous indexes.


Hmmm ... I suppose the above would require enabling expression  
indexes for PKs as well, no?


In 8.4 you should be able to get around this particular example using  
citext.


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] Distinct types

[David E. Wheeler]

On Nov 28, 2008, at 12:46 PM, Peter Eisentraut wrote:

I understand, but the work required to make it work properly is too  
much under the commit fest spirit right now.  In particular, I'm  
thinking we should try to devise a clever way to make the CREATE  
ORDERING facility that SQL has for user-defined types interface with  
our more general operator and operator class mechanisms.  This would  
then also benefit other sorts of user-defined types.  There are also  
a number of unclear assumptions about the domain behavior implicitly  
in the system that will possibly require a lengthy shaking-out  
process if we add other sorts of derived types


Speaking of other sorts of derived types: might they include something  
just like enums, but sorting on the string values defined for the enum  
rather than on the order in which the values were defined in the enum?  
I'd use something like that all the time…


Thanks,

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] Distinct types

[David E. Wheeler]

On Nov 28, 2008, at 5:09 PM, Andrew Dunstan wrote:

Speaking of other sorts of derived types: might they include  
something just like enums, but sorting on the string values defined  
for the enum rather than on the order in which the values were  
defined in the enum? I'd use something like that all the time…


order by foo_enum::text ...


Ah, I didn't realize that. I guess I'd have to index it on ::text,  
too. And then, to use the index in WHERE clauses, I'd further have to  
compare to ::text, eh?


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] WIP: default values for function parameters

[David E. Wheeler]

On Nov 30, 2008, at 6:49 PM, Tom Lane wrote:


Peter Eisentraut [EMAIL PROTECTED] writes:

There are two ways to fix this, both having some validity:


1. We create a second version of pg_get_function_arguments() that  
produces

arguments without default values decoration.  This is probably the
technically sound thing to do.


Yes.  I think that the argument for allowing parameter names in  
commands

like ALTER FUNCTION is that the user might consider them part of the
function's identity.  This can hardly be claimed for default values.


Agreed, default values should not be a part of function signatures,  
although it might be nice if ALTER FUNCTION to allow default values to  
be changed.


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] New to_timestamp implementation is pretty strict

[David E. Wheeler]

On Dec 1, 2008, at 1:08 PM, Heikki Linnakangas wrote:

postgres=# SELECT to_timestamp('29-12-2005 01:02:3', 'DD-MM-  
HH24:MI:SS'); -- doesn't work

ERROR:  source string too short for SS formatting field
DETAIL:  Field requires 2 characters, but only 1 remain.
HINT:  If your source string is not fixed-width, try using the FM  
modifier.


I think the end of string should be treated like a field separator,  
colon in this example, and we should accept both of the above.  
Opinions?


I'm generally in favor of being generous in the input one can accept,  
but in this case it seems ambiguous to me. Is that supposed to be :30  
or :03? There's no way to tell.


My $0.02.

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] New to_timestamp implementation is pretty strict

[David E. Wheeler]

On Dec 1, 2008, at 3:52 PM, Tom Lane wrote:


I'm generally in favor of being generous in the input one can accept,
but in this case it seems ambiguous to me. Is that supposed to be :30
or :03? There's no way to tell.


But notice that we are allowing a single digit for the hour and minute
fields.  It's inconsistent that the last field works differently.
(And it is that it's the last field, not that it's SS --- try minutes
as the last field.)


Oh, well yeah, it should be consistent. But I'm still not sure that :3  
should be allowed. OTOH, who does that, anyway?


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] New to_timestamp implementation is pretty strict

[David E. Wheeler]

On Dec 1, 2008, at 3:55 PM, Dave Page wrote:

I'm generally in favor of being generous in the input one can  
accept, but in
this case it seems ambiguous to me. Is that supposed to be :30 or : 
03?

There's no way to tell.


How is it ambiguous? The leading zero is technically redundant. A
trailing on most certainly isn't.


it depends on how you look at it, I suppose. If you look at :xy as  
x being the 10s position and y being the 1s position, it makes no  
sense. If you look at it as an integer, it does.


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] New to_timestamp implementation is pretty strict

[David E. Wheeler]

On Dec 1, 2008, at 4:07 PM, Alvaro Herrera wrote:


David E. Wheeler wrote:

Oh, well yeah, it should be consistent. But I'm still not sure  
that :3

should be allowed. OTOH, who does that, anyway?


Anyone who prints times as %d:%d:%d.  You can find those in the wild.


I guess I should have expected that. Sheesh.

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] New to_timestamp implementation is pretty strict

[David E. Wheeler]

On Dec 1, 2008, at 4:09 PM, Dave Page wrote:

On Mon, Dec 1, 2008 at 3:02 PM, David E. Wheeler  
[EMAIL PROTECTED] wrote:


it depends on how you look at it, I suppose. If you look at :xy  
as x
being the 10s position and y being the 1s position, it makes no  
sense.


Suffice it to say, I don't look at it that way :-). I'd wager most
people wouldn't either, but I have no data to back that up of course.


Yeah, I could see that. It makes no sense to me (:3 just looks  
weird), but maybe I just think too much like a computer. ;-)


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] Polymorphic types vs. domains

[David E. Wheeler]

On Dec 8, 2008, at 2:46 AM, Tom Lane wrote:


Comments?


+1


If this is agreed to be a bug, should we consider
back-patching it?  (I'd vote not, I think, because the behavioral
change could conceivably break some apps that work now.)


+1

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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 10:19 AM, Greg Stark wrote:

 I like the special marker idea.  A '$' would be nice because its already in
 use for similar purposes, but I think that would lead to ambiguity with
 dollar quoting.
 
 I think that would be a big break with everything else and very
 non-sql-ish. We don't use these in plpgsql and we don't use them
 anywhere else in sql.

*ahem* $1 *ahem*

 Moreover you would still have conflicts possible because sql can quote
 identifiers so people can have columns named $foo. You would have a
 weird syntactic detail where $foo would mean something different
 than $foo even though they're both valid identifiers.

Same with Foo and Foo, no?

 I'm not sure it wouldn't conflict with some drivers either. DBI uses
 :foo and ? but I have a vague recollection some drivers did use $foo.

I don't think that would come up, because the $vars are in the body of the 
function, not in a typical driver call.

Personally, I like $var, but @var would be okay, and @@var is acceptable. But 
I'm JAPH, so my biases should be obvious.

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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 10:54 AM, Greg Stark wrote:

 I'm japh too -- but that doesn't mean grabbing one little aesthetic
 from Perl without copying the whole concept behind it makes any sense.
 Perl sigils are an important part of the language and are a basic part
 of the syntax. They aren't just a this is a variable marker.
 Dropping one use of them into a language that doesn't use them
 anywhere else just makes the language into a mishmash.

Well, no, just because we're talking about adopting $var doesn't mean we're 
trying to turn SQL or PL/pgSQL into Perl. It means that we want to signify that 
a token is a variable, as opposed to something else (hence “sigil”). That 
doesn't make it a mishmash unless you think you suddenly have Perl (or shell) 
semantics, which would be a pretty weird expectation.

 I don't see any purpose to using such markers anyways. We have a
 parser, we have a symbol table, we should use them; these identifiers
 are just like other identifiers.

See the discussion of conflicts with column names in the recent thread. A sigil 
would eliminate that problem -- and we already have $1 and friends, so this is 
just an extension of that in my view.

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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 11:21 AM, Greg Stark wrote:

 No, that's not the same.
 
 The point is that $ is a perfectly valid SQL identifier character and
 $foo is a perfectly valid identifier. You can always quote any
 identifier (yes, after case smashing) so you would expect if $foo is a
 valid identifier then $foo would refer to the same identifier.
 You're introducing a meaning for $foo but saying there's no valid way
 to quote the identifier to get the same thing. And worse, if you do
 quote it you get something else entirely different.

$foo should be killed off as a valid identifier, IMNSHO.

But failing that, some other sigil would be most welcome.

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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 12:09 PM, Greg Stark wrote:

 1) Error messages which mention column names are supposed to quote the
 column name to set it apart from the error string. This also
 guarantees that weird column names are referenced correctly as foo
 bar or $foo so the reference in the error string is unambiguous and
 can be pasted into queries. This won't work for $foo which would have
 to be embedded in the error text without quotes.

What? You can't have a column named $foo without the quotes.

 2) What would the default names for columns be if you did something like
 
  create function f(foo) as 'select $foo'

It would be f (without the quotes), just like now:

try=# create function f(int) RETURNS int as 'SELECT $1' LANGUAGE sql;
CREATE FUNCTION
try=# select f(1);
 f 
---
 1
(1 row)

 If I then use this in another function
 
 create function g(foo) as 'select $foo+$foo from f()'
 
 I have to quote the column?

No, that's a syntax error. It would be `SELECT f + $foo from f();`

 3) If I have a report generator which takes a list of columns to
 include in the report, or an ORM which tries to generate queries the
 usual way to write such things is to just routinely quote every
 identifier. This is less error-prone and simpler to code than trying
 to identify which identifiers need quoting and which don't. However in
 if the query is then dropped into a function the ORM or query
 generator would have to know which columns cannot be quoted based on
 syntactic information it can't really deduce.

You already have to quote everything, because $foo isn't a valid column name. 
And functions use the function name as the default column name, not a variable 
name. The same is true of set-returning functions, BTW:

try=# create function b(int) RETURNS setof int as 'values ($1), ($1)' LANGUAGE 
sql; CREATE FUNCTION
try=# select b(1);
 b 
---
 1
 1
(2 rows)

So there is no leaking out. The variables are scoped within the function.

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] named parameters in SQL functions

[David E. Wheeler]

On Nov 15, 2009, at 11:35 AM, Greg Stark wrote:

 I don't think SQL is the height of language design either. But trying
 to turn it into another language piece by piece is not gong to make it
 any nicer.

I don't know of anyone suggesting such a thing.

 A sigil here doesn't accomplish anything. The identifiers in question
 are *just* like other identifiers. They can be used in expressions
 just like other columns, they have various types, they have the same
 syntax as other columns, the sigil doesn't mean anything.

So what is the $ for in $1, $2, etc.?

 I think what may be making this tempting is that they look vaguely
 like ODBC/JDBC/DBI placeholders like :foo. However they're very very
 different. In those cases the sigil is marking the sigil outside the
 SQL syntax. They will be replaced textually without parsing the SQL at
 all. It's actually very confusing having $foo indicate something
 within SQL since it makes it look like it's some external thing from
 another layer like the placeholders.

It's not in SQL; it's in SQL functions (and DO blocks). AFAIK, the major 
database vendors all use some sort of character to identify variables within 
functions. It's proven, avoids conflicts (you can't have an identifier named 
$foo, as Andrew just pointed out), and just generally makes maintenance easier.

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] Summary and Plan for Hot Standby

[David E. Wheeler]

On Nov 15, 2009, at 2:17 PM, Tom Lane wrote:

 So I'm in favor of committing part of the HS code even if there are
 known failure conditions, as long as those conditions are well-defined.
 
 If we're thinking of committing something that is known broken, I would
 want to have a clearly defined and trust-inspiring escape strategy.
 We can always revert the patch later inspires absolutely zero
 confidence here, because in a patch this large there are always going to
 be overlaps with other later patches.  If it gets to be February and HS
 is still unshippable, reverting is going to be a tricky and risky
 affair.
 
 I agree with Heikki that it would be better not to commit as long as
 any clear showstoppers remain unresolved.

If ever there were an argument for topic branches, *this is it*.

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] next CommitFest

[David E. Wheeler]

On Nov 17, 2009, at 9:15 AM, Andrew Dunstan wrote:

 Indeed. I once suggested only half jokingly that we should have a Coder of 
 the month award.

I suggest that it be named The Tom Lane award, and disqualify Tom from 
winning (sorry Tom). ;-)

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] plperl and inline functions -- first draft

[David E. Wheeler]

On Nov 20, 2009, at 10:50 PM, Tim Bunce wrote:

 I'd suggest:
 
...; PL/Perl functions created with CREATE FUNCTION are called in a
scalar context, so can't return a list.  You can return more complex
structures (arrays, records, and sets) by returning a reference, as
discussed below.
 
 That only mentions functions created with CREATE FUNCTION though.
 Perhaps it needs to be generalized to cover DO as well.

FWIW, DO is run in a VOID context. Return values are ignored (or perhaps 
trigger an exception?).

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

[HACKERS] [PATCH] hstore documentation update

[David E. Wheeler]

From: David E. Wheeler da...@justatheory.com

As I threatened when I reviewed hstore in the last two commit
fests, I've finally seen may way to edit the documentation. This
is mostly word-smithing, making sure that all ``s are encoded,
making sure that various text is properly tagged with `type`
and `literal` tags, plus an extra note or two. I submit this
patch for the next CommitFest (though I don't know how much CFing
is needed for a pure documenation patch).

Best,

David

---
 doc/src/sgml/hstore.sgml |  190 +-
 1 files changed, 102 insertions(+), 88 deletions(-)

diff --git a/doc/src/sgml/hstore.sgml b/doc/src/sgml/hstore.sgml
index f237be7..fcff6e3 100644
*** a/doc/src/sgml/hstore.sgml
--- b/doc/src/sgml/hstore.sgml
***
*** 8,69 
   /indexterm
  
   para
!   This module implements a data type typehstore/ for storing sets of
!   (key,value) pairs within a single productnamePostgreSQL/ data field.
This can be useful in various scenarios, such as rows with many attributes
that are rarely examined, or semi-structured data.  Keys and values are
!   arbitrary text strings.
   /para
  
   sect2
titletypehstore/ External Representation/title
  
para
!The text representation of an typehstore/ value includes zero
!or more replaceablekey/ literal=gt;/ replaceablevalue/
!items, separated by commas.  For example:
  
 programlisting
! k = v
! foo = bar, baz = whatever
! 1-a = anything at all
 /programlisting
  
!The order of the items is not considered significant (and may not be
!reproduced on output).  Whitespace between items or around the
!literal=gt;/ sign is ignored.  Use double quotes if a key or
!value includes whitespace, comma, literal=/ or literalgt;/.
!To include a double quote or a backslash in a key or value, precede
!it with another backslash.
/para
  
para
!A value (but not a key) can be a SQL NULL.  This is represented as
  
 programlisting
! key = NULL
 /programlisting
  
!The literalNULL/ keyword is not case-sensitive.  Again, use
!double quotes if you want the string literalnull/ to be treated
!as an ordinary data value.
/para
  
note
para
!Keep in mind that the above format, when used to input hstore values,
!applies emphasisbefore/ any required quoting or escaping. If you
!are passing an hstore literal via a parameter, then no additional
!processing is needed. If you are passing it as a quoted literal
!constant, then any single-quote characters and (depending on the
!setting of varnamestandard_conforming_strings/) backslash characters
!need to be escaped correctly. See xref linkend=sql-syntax-strings.
/para
/note
  
para
!Double quotes are always used to surround key and value
!strings on output, even when this is not strictly necessary.
/para
  
   /sect2
--- 8,83 
   /indexterm
  
   para
!   This module implements the typehstore/ data type for storing sets of
!   key/value pairs within a single productnamePostgreSQL/ value.
This can be useful in various scenarios, such as rows with many attributes
that are rarely examined, or semi-structured data.  Keys and values are
!   simply text strings.
   /para
  
   sect2
titletypehstore/ External Representation/title
  
para
! 
!The text representation of an typehstore/, used for input and output,
!includes zero or more replaceablekey/ literal=gt;/
!replaceablevalue/ pairs separated by commas. Some examples:
  
 programlisting
! k =gt; v
! foo =gt; bar, baz =gt; whatever
! 1-a =gt; anything at all
 /programlisting
  
!The order of the pairs is not significant (and may not be reproduced on
!output). Whitespace between pairs or around the literal=gt;/ sign is
!ignored. Double-quote keys and values that include whitespace, commas,
!literal=/s or literalgt;/s. To include a double quote or a
!backslash in a key or value, escape it with a backslash.
/para
  
para
!Each key in an typehstore/ is unique. If you declare an typehstore/
!with duplicate keys, only one will be stored in the typehstore/ and
!there is no guarantee as to which will be kept:
  
 programlisting
! % select 'a=gt;1,a=gt;2'::hstore;
!   hstore
! --
!  a=gt;1
 /programlisting
+   /para
  
!   para
!A value (but not a key) can be an SQL literalNULL/. For example:
! 
!programlisting
! key =gt; NULL
!/programlisting
! 
!The literalNULL/ keyword is case-insensitive. Double-quote the
!literalNULL/ to treat it as the ordinary string NULL.
/para
  
note
para
!Keep in mind that the typehstore/ text format, when used for input,
!applies emphasisbefore/ any required quoting or escaping. If you are
!passing an typehstore/ literal via a parameter, then no additional
!processing is needed. But if you're passing

Re: [HACKERS] Ignoring white space in regression tests really a good idea?

[David E. Wheeler]

On Nov 22, 2009, at 7:49 AM, Tom Lane wrote:

 I'm thinking maybe we should remove -w.  Comments?

Have you tried it on the existing tests to see what happens?

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] Timezones (in 8.5?)

[David E. Wheeler]

On Nov 28, 2009, at 5:40 PM, Bruce Momjian wrote:

 I think there is general agreement that we should have a timezone data
 type which validates against pg_timezone_names().name.  It might be
 enough to just document how users can create such a domain data type,
 but I don't know of a way to do that.  Is this a TODO?

From 
http://justatheory.com/computers/databases/postgresql/citext-patch-submitted.html

CREATE OR REPLACE FUNCTION is_timezone( tz TEXT ) RETURNS BOOLEAN as $$
BEGIN
  PERFORM now() AT TIME ZONE tz;
  RETURN TRUE;
EXCEPTION WHEN invalid_parameter_value THEN
  RETURN FALSE;
END;
$$ language plpgsql STABLE;

CREATE DOMAIN timezone AS CITEXT
CHECK ( is_timezone( value ) );

It could also be TEXT I suppose, but America/Los_Angeles and 
america/los_angeles should be considered the same.

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] [PATCH] hstore documentation update

[David E. Wheeler]

On Dec 1, 2009, at 2:56 AM, Bruce Momjian wrote:


Applied.  Thanks.


Thanks, I'll remove it from the next CF list, then.

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] [PATCH] hstore documentation update

[David E . Wheeler]

On Dec 1, 2009, at 3:01 AM, David E. Wheeler wrote:


On Dec 1, 2009, at 2:56 AM, Bruce Momjian wrote:


Applied.  Thanks.


Thanks, I'll remove it from the next CF list, then.


Oh, you already marked it as committed. Thanks!

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] CommitFest status/management

[David E. Wheeler]

On Nov 30, 2009, at 8:08 PM, Tom Lane wrote:

 I'm going to look at the YAML format for EXPLAIN patch shortly.
 
 Do we have consensus yet that we want YAML?  It seemed, well,
 yet another format without all that much advantage over what's
 there.

Legibility++

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] First feature patch for plperl - draft [PATCH]

[David E. Wheeler]

On Dec 3, 2009, at 3:30 PM, Tim Bunce wrote:

 - New GUC plperl.on_perl_init='...perl...' for admin use.
 - New GUC plperl.on_trusted_init='...perl...' for plperl user use.
 - New GUC plperl.on_untrusted_init='...perl...' for plperlu user use.

Since there is no documentation yet, how do these work, exactly? Or should I 
just wait for the docs?

 - END blocks now run at backend exit (fixes bug #5066).
 - Stored procedure subs are now given names ($name__$oid).
 - More error checking and reporting.
 - Warnings no longer have an extra newline in the NOTICE text.
 - Various minor optimizations like pre-growing data structures.

Nice.

 I'm working on adding tests and documentation now, meanwhile I'd very
 much appreciate any feedback on the patch.
 
 Tim.
 
 p.s. Once this patch is complete I plan to work on patches that:
 - add quote_literal and quote_identifier functions in C.

I expect you can just use the C versions in PostgreSQL. They're in 
utils/builtins.h, along with quote_nullable(), which might also be useful to 
add.

 - generalize the Safe setup code to enable more control.
 - formalize namespace usage, moving things out of main::

Nice.

 - add a way to perform inter-sub calling (at least for simple cases).
 - possibly rewrite _plperl_to_pg_array in C.

Sounds great, Tim. I'm not really qualified to say anything about the C code, 
but I'd be happy to try it out once there are docs.

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

[HACKERS] Format Typmod?

[David E. Wheeler]

Hey Hackers,

I just had reason to want the way that format_type converts type names (e.g., 
varchar = character varying) in pgTAP, but didn't want the namespace 
qualification (used by format_type() when the type in question is not visible). 
I figured out that I could get that conversion by simply casting the OID 
argument to regtype, but then to get the typmod, I still have to use 
format_type() like so:

CREATE OR REPLACE FUNCTION display_type ( OID, INTEGER )
RETURNS TEXT AS $$
SELECT $1::regtype || COALESCE(
substring(pg_catalog.format_type($1, $2), '[(][^)]+[)]$'),
'')
$$ LANGUAGE SQL;

Is there a saner way to do it than this? That is, is there a better way to get 
the typmod than by munging the value returned by format_type()?

Thanks,

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] operator exclusion constraints

[David E. Wheeler]

On Dec 3, 2009, at 6:26 PM, Robert Haas wrote:

 Yeah, I don't remember any such consensus either, but it's not a dumb
 name.  I have been idly wondering throughout this process whether we
 should try to pick a name that conveys the fact that these constraints
 are inextricably tied to the opclass/index machinery - but I'm not
 sure it's possible to really give that flavor in a short phrase, or
 that it's actually important to do so.  IOW... whatever.  :-)

Whatever constraints? Operator Whatevers? WhatEVERs? I like it.

David

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