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] Malformed Array Literal in PL/pgSQL Exception Block

[David E. Wheeler]

On Apr 9, 2017, at 9:59 PM, Andrew Gierth  wrote:

> Tom's response has the explanation of why it fails (everywhere, not just
> in the exception block): parse analysis prefers to match the (array ||
> array) form of the operator when given input of (array || unknown). Just
> cast the 'foo' to the array element type.

Tried to reduce this from some code I’m working on. I have a whole bunch of 
code that appends to an array in this way without casting ‘foo’ to text or 
text[]. It’s only in an exception block that it’s complaining.

Hrm, looking back through my code, it looks like I’m mostly calling format() to 
append to an array, which of course returns a ::text, so no ambiguity. Guess 
that’s my issue.

Thanks,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Malformed Array Literal in PL/pgSQL Exception Block

[David E. Wheeler]

On Apr 9, 2017, at 9:52 PM, Andrew Gierth  wrote:

> This "raise" statement is not reached, because the previous line raises
> the "malformed array literal" error.

Bah!

> David> EXCEPTION WHEN OTHERS THEN
> 
> If you change this to  EXCEPTION WHEN division_by_zero THEN, the
> reported error becomes:
> 
> ERROR:  malformed array literal: "foo"
> LINE 1: SELECT things || 'foo'

So the issue stands, yes?

D



smime.p7s
Description: S/MIME cryptographic signature

[HACKERS] Malformed Array Literal in PL/pgSQL Exception Block

[David E. Wheeler]

Hackers,

I’ve been happily using the array-to-element concatenation operator || to 
append a single value to an array, e.g, 

SELECT array || 'foo';

And it works great, including in PL/pgSQL functions, except in an exception 
block. When I run this:

BEGIN;

CREATE OR REPLACE FUNCTION foo(
) RETURNS BOOLEAN IMMUTABLE LANGUAGE PLPGSQL AS $$
DECLARE
things TEXT[] := '{}';
BEGIN
things := things || 'foo';
RAISE division_by_zero;
EXCEPTION WHEN OTHERS THEN
things := things || 'bar';
END;
$$;

SELECT foo();

ROLLBACK;

The output is:

psql:array.sql:15: ERROR:  malformed array literal: "bar"
LINE 1: SELECT things || 'bar'
 ^
DETAIL:  Array value must start with "{" or dimension information.
QUERY:  SELECT things || 'bar'
CONTEXT:  PL/pgSQL function foo() line 8 at assignment

Note that it’s fine with the use of || outside the exception block, but not 
inside! I’ve worked around this by using `things || '{bar}'` instead, but it 
seems like a bug or perhaps unforeseen corner case that appending a value to an 
array doesn’t work in an exception-handling block.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Fetch JSONB Value for UNIQUE Constraint

[David E. Wheeler]

On Mar 24, 2017, at 5:00 PM, Peter Geoghegan  wrote:

>> So it’s a fine workaround, but maybe there’s something missing from the 
>> parsing of the CREATE TABLE statement? This is on 9.6.1.
> 
> Unique constraints don't support expressions, or a predicate (partial-ness).

Oh. Okay. I assumed the syntax would be identical to a unique index, since 
that’s ultimately what a unique constraint is, IIUC. My mistake.

Thanks Peter!

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

[HACKERS] Fetch JSONB Value for UNIQUE Constraint

[David E. Wheeler]

Dear Hackers,

Should this work?

CREATE TABLE things (
user_id  INTEGER NOT NULL,
document JSONB   NOT NULL,
UNIQUE (user_id, document->>'name')
);
ERROR:  syntax error at or near "->>"
LINE 4: UNIQUE (user_id, document->>’name')

I tried adding parens, but that didn’t work, either:

CREATE TABLE things (
user_id  INTEGER NOT NULL,
document JSONB   NOT NULL,
UNIQUE (user_id, (document->>'name'))
);
ERROR:  syntax error at or near "("
LINE 4: UNIQUE (user_id, (document->>'name'))

It works fine to create a unique index, though:

CREATE TABLE things (
user_id  INTEGER NOT NULL,
document JSONB   NOT NULL
);
CREATE UNIQUE INDEX ON things(user_id, (document->>'name'));

So it’s a fine workaround, but maybe there’s something missing from the parsing 
of the CREATE TABLE statement? This is on 9.6.1.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

[HACKERS] Unacccented Fractions

[David E. Wheeler]

Hello Hackers,

I noticed that unaccent.rules has spaces in front of the unaccented 
representation of fraction glyphs:

¼1/4
½1/2
¾3/4

Note the space after the tab. In case my client kills what I’ve pasted, those 
lines match

¼\t[ ]1/4
½\t[ ]1/2
¾\t[ ]3/4

This makes sense to me, as I’d like “1¼”, for example to become “1 1/4”. 
However, that’s not what seems to happen:

=# SELECT unaccent('1¼');
 unaccent 
--
 11/4

Should that space from the rules file be preserved, so that the text doesn’t 
become eleven fourths?

Thanks,

David

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] removing tsearch2

[David E. Wheeler]

On Feb 27, 2017, at 1:53 PM, Bruce Momjian  wrote:

> Oh, does CPAN distribute compiled modules or requires users to compile
> them.

Like PGXN, it formally does not care, but its implementation expects source 
code distributions what will be built and installed by users. Note that the 
vast majority of those modules, -- even pure Perl modules -- are built with 
make.

So users typically get their Perl modules in one of these ways:

1. As binaries from their distribution’s package manager. These tend to be 
updated manually by volunteers and not integrated into CPAN, though there are 
solutions such as [rpmcpan](https://github.com/iovation/rpmcpan) and 
[PPM](http://www.activestate.com/activeperl/ppm-perl-modules) which do regular 
distro package builds.

2. As source code from CPAN, from which they are compiled (when necessary), 
built, and installed by the user or a build system such as 
[Homebrew](https://brew.sh).

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] removing tsearch2

[David E. Wheeler]

On Feb 27, 2017, at 12:04 PM, Bruce Momjian  wrote:

> Just stating the obvious, but one of the reasons CPAN works so well is
> that most of the modules are written in Perl and hence don't need
> per-platform compilation.

There are a *lot* of C-baded modules on CPAN; and my guess is that, more often 
than not, Perl modules depend on other C-based modules.

I daresay a lot of PostgreSQL extensions can be written in pure SQL or PL/pgSQL.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] removing tsearch2

[David E. Wheeler]

On Feb 17, 2017, at 12:54 AM, Magnus Hagander  wrote:

> If we could somehow integrate PGXN with both the RPM build process, the DEB 
> build process and a Windows build process (whether driven by PGXN or just 
> "fed enough data" by PGXN is a different question), I think that would go a 
> long way towards the goal.

My thought was that someone could rsync the PGXN repo every hour or something 
and build any new modules there. That’s how the search site is built: Every 
five minutes, it rsyncs rsync://master.pgxn.org/pgxn, parses the output to see 
new releases, and updates the index.

> Also being able to use this somehow to drive continuous builds and tests 
> (kind of like a buildfarm-lite for a subset of platforms) would be useful for 
> reaching a point where extensions outside of core can come at least close to 
> what we deliver in core.

Personally I just use Travis and Coveralls on GitHub for that sort of thing.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] removing tsearch2

[David E. Wheeler]

On Feb 14, 2017, at 9:37 AM, Magnus Hagander  wrote:

> It's a failing in one of the two at least. It either needs to be easier to 
> build the things on windows, or pgxn would need to learn to do binary 
> distributions. 

PGXN makes no effort to support installation on any platform at all. Happy to 
work with anyone who wants to add binary distribution, but supporting multiple 
platforms might be a PITA. Maybe there’d be a way to integrate with the RPM and 
.deb and Windows repos (is there something like that for Windows?).

> Even if we get the building easier on windows, it'll likely remain a second 
> class citizen (though better than today's third class), given the amount of 
> windows machines that actually have a compiler on them for start. Pgxs in 
> Windows would be a big improvement, but it won't solve the problem. 

Yep.

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] removing tsearch2

[David E. Wheeler]

On Feb 14, 2017, at 5:37 AM, Jim Nasby  wrote:

>> Until pgxn has a way of helping users on for example Windows (or other
>> platforms where they don't have a pgxs system and a compiler around),
>> it's always going to be a "second class citizen".
> 
> I view that as more of a failing of pgxs than pgxn. Granted, the most common 
> (only?) pgxn client right now is written in python, but it's certainly 
> possible to run that on windows with some effort (BigSQL does it), and I'm 
> fairly certain it's not that hard to package a python script as a windows 
> .exe.

Yeah, that’s outside of PGXN’s mandate. It doesn’t do any installing at all, 
just distribution (release, search, download). Even the Python client just 
looks to see what build support is in a distribution it downloads to decide how 
to build it (make, configure, etc.), IIRC.

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] anyelement -> anyrange

[David E. Wheeler]

On Aug 18, 2016, at 11:49 AM, Jim Nasby  wrote:

> Well crap, I searched for range stuff on PGXN before creating 
> http://pgxn.org/dist/range_tools/ and the only thing that came up was your 
> range_partitioning stuff, which AFAICT is unrelated. 
> http://pgxn.org/dist/range_type_functions/still doesn't show up in search, 
> maybe because it's marked unstable?

Yep. https://github.com/pgxn/pgxn-api/issues/2

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Add hint for function named "is"

[David E. Wheeler]

On Aug 11, 2016, at 2:11 PM, Jim Nasby  wrote:

> CREATE FUNCTION pg_temp.is() RETURNS text LANGUAGE sql AS $$SELECT 
> 'x'::text$$;
> SELECT 'x'||is();
> ERROR:  syntax error at or near "("
> LINE 1: SELECT 'x'||is();
> 
> I was finally able to figure out this was because "is" needs to be quoted; is 
> there a way this could be hinted?
> 
> FWIW, the real-world case here comes from using pgTap, which has an is() 
> function. I've used that countless times by itself without quoting, so it 
> never occurred to me that the syntax error was due to lack of quotes.

Why does it need quotation marks in this case?

D

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

Sorry for the pgTAP off-topicness here, hackers. Please feel free to ignore.

On May 17, 2016, at 8:10 AM, Jim Nasby  wrote:

> Speaking specifically to is(), what I'd find most useful is if it at least 
> hinted that there might be some type shenanigans going on, because I've run 
> across something like your example more than once and it always takes a lot 
> to finally figure out WTF is going on.

Agreed. Same for the relation testing functions. Maybe some additional 
diagnostics could be added in the event of failure.

> I think it'd also be useful to be able to specify an equality operator to 
> is(), though that means not using IS DISTINCT.

You can use cmp_ok().

  http://pgxn.org/dist/pgtap/doc/pgtap.html#cmp_ok.

> Something else to keep in mind here is that is() is defined as is(anyelement, 
> anyelement, text), which means you've lost your original type information 
> when you use it. I don't think you could actually do anything useful here 
> because of that.

pg_typeof() will give it to you.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 17, 2016, at 7:58 AM, Jim Nasby  wrote:

> Probably in an attempt to bypass parse overhead on ingestion.
> 
> Possibly because JSONB silently eats duplicated keys while JSON doesn't 
> (though in that case even casting to JSONB is probably not what you want).

It’s also when you’d want text equivalent semantics.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 12, 2016, at 12:02 PM, Tom Lane  wrote:

> Andrew mentions in the extension you pointed to that providing a default
> comparison operator would enable people to do UNION, DISTINCT, etc on JSON
> columns without thinking about it.  I'm not convinced that "without
> thinking about it" is a good thing here.  But if we were going to enable
> that, I'd feel better about making it default to jsonb semantics ...

If you want the JSONB semantics, why wouldn’t you use JSONB instead of JSON?

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 12, 2016, at 11:19 AM, Fabrízio de Royes Mello  
wrote:

> Yeah.. it's ugly but you can do something like that:

I could, but I won’t, since this is pgTAP and users of the library might have 
defined their own json operators. 

Andrew Dunstan has done the yeoman’s work of creating such operators, BTW:

  https://bitbucket.org/adunstan/jsoncmp

Some might argue that it ought to compare JSON objects, effectively be the 
equivalent of ::jsonb = ::jsonb, rather than ::text = ::text. But as Andrew 
points out to me offlist, “if that's what they want why aren't they using jsonb 
in the first place?”

So I think that, up to the introduction of JSONB, it was important not to side 
one way or the other and put a JSON = operator in core. But now what we have 
JSONB, perhaps it makes sense to finally take sides and intoduce JSON = that 
does plain text comparison. Thoughts?

Best,

David

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 11, 2016, at 11:01 AM, Fabrízio de Royes Mello  
wrote:

> I know... but you can do that just in case the current behaviour fail by 
> cathing it with "begin...exception...", so you'll minimize the looking for 
> process on catalog.

Yeah, I guess. Honestly 90% of this issue would go away for me if there was a 
`json = json` operator. I know there are a couple different ways to interpret 
JSON equality, though.

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 11, 2016, at 10:34 AM, Kevin Grittner  wrote:

> I'm not clear enough on your intended usage to know whether these
> operators are a good fit, but they are sitting there waiting to be
> used if they do fit.

Huh. I haven’t had any problems with IS DISTINCT FROM for rows, except for the 
situation in which a failure is thrown because the types vary, say between TEXT 
and CITEXT. That can drive the tester crazy, since it says something like:

Results differ beginning at row 3:
have: (44,Anna)
want: (44,Anna)

But overall I think that’s okay; the tester really does want to make sure the 
type is correct.

Thanks,

David




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 11, 2016, at 10:19 AM, Kevin Grittner  wrote:

> As long as you don't assume too much about *what* is equal.
> 
> test=# select '(1,1)(2,2)'::box = '(-4.5,1000)(-2.5,1000.5)'::box;
> ?column?
> --
> t
> (1 row)

Oh, well crap. Maybe I’d be better off just comparing the plain text of the 
expressions as Tom suggested.

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 10, 2016, at 5:56 PM, Fabrízio de Royes Mello  
wrote:

> Searching for the operator in pg_operator catalog isn't enought?

Seems like overkill, but will do if there’s nothing else.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Does Type Have = Operator?

[David E. Wheeler]

On May 10, 2016, at 6:14 PM, Tom Lane  wrote:

> Given that you're coercing both one input value and the result to text,
> I don't understand why you don't just compare the text representations.

Because sometimes the text is not equal when the casted text is. Consider

'foo'::citext = 'FOO':citext

> I'm also not very clear on what you mean by "comparing column defaults".
> A column default is an expression (in the general case anyway), not just
> a value of the type.

Yeah, the pgTAP column_default_is() function takes a string representation of 
an expression.

> Maybe if you'd shown us the is() function, as well as a typical usage
> of _def_is(), this would be less opaque.

Here’s is():

CREATE OR REPLACE FUNCTION is (anyelement, anyelement, text)
RETURNS TEXT AS $$
DECLARE
result BOOLEAN;
output TEXT;
BEGIN
-- Would prefer $1 IS NOT DISTINCT FROM, but that's not supported by 
8.1.
result := NOT $1 IS DISTINCT FROM $2;
output := ok( result, $3 );
RETURN output || CASE result WHEN TRUE THEN '' ELSE E'\n' || diag(
   'have: ' || CASE WHEN $1 IS NULL THEN 'NULL' ELSE 
$1::text END ||
E'\nwant: ' || CASE WHEN $2 IS NULL THEN 'NULL' ELSE 
$2::text END
) END;
END;
$$ LANGUAGE plpgsql;

_def_is() is called by another function, which effectively is:

CREATE OR REPLACE FUNCTION _cdi ( NAME, NAME, NAME, anyelement, TEXT )
RETURNS TEXT AS $$
BEGIN

RETURN _def_is(
pg_catalog.pg_get_expr(d.adbin, d.adrelid),
pg_catalog.format_type(a.atttypid, a.atttypmod),
$4, $5
)
  FROM pg_catalog.pg_namespace n, pg_catalog.pg_class c, 
pg_catalog.pg_attribute a,
   pg_catalog.pg_attrdef d
 WHERE n.oid = c.relnamespace
   AND c.oid = a.attrelid
   AND a.atthasdef
   AND a.attrelid = d.adrelid
   AND a.attnum = d.adnum
   AND n.nspname = $1
   AND c.relname = $2
   AND a.attnum > 0
   AND NOT a.attisdropped
   AND a.attname = $3;
END;
$$ LANGUAGE plpgsql;

That function si called like this:

_cdi( :schema, :table, :column, :default, :description );

Best,

David





smime.p7s
Description: S/MIME cryptographic signature

[HACKERS] Does Type Have = Operator?

[David E. Wheeler]

Hackers,

pgTAP has a function that compares two values of a given type, which it uses 
for comparing column defaults. It looks like this:

CREATE OR REPLACE FUNCTION _def_is( TEXT, TEXT, anyelement, TEXT )
RETURNS TEXT AS $$
DECLARE
thing text;
BEGIN
IF $1 ~ '^[^'']+[(]' THEN
-- It's a functional default.
RETURN is( $1, $3, $4 );
END IF;

EXECUTE 'SELECT is('
 || COALESCE($1, 'NULL' || '::' || $2) || '::' || $2 || ', '
 || COALESCE(quote_literal($3), 'NULL') || '::' || $2 || ', '
 || COALESCE(quote_literal($4), 'NULL')
|| ')' INTO thing;
RETURN thing;
END;
$$ LANGUAGE plpgsql;

The is() function does an IS DISTINCT FROM to compare the two values passed to 
it. This has been working pretty well for years, but one place it doesn’t work 
is with JSON values. I get:

LINE 1: SELECT NOT $1 IS DISTINCT FROM $2
  ^
HINT:  No operator matches the given name and argument type(s). You might 
need to add explicit type casts.
QUERY:  SELECT NOT $1 IS DISTINCT FROM $2

This makes sense, of course, and I could fix it by comparing text values 
instead of json values when the values are JSON. But of course the lack of a = 
operator is not limited to JSON. So I’m wondering if there’s an interface at 
the SQL level to tell me whether a type has an = operator? That way I could 
always use text values in those situations.

Thanks,

David




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Releasing in September

[David E. Wheeler]

On Jan 20, 2016, at 9:42 AM, Joshua D. Drake  wrote:

> 4. Submit a patch, review a patch.
> 
> Don't review patches? Don't submit patches.

There will always be patches desirable-enough that they will be reviewed 
whether or not the submitter reviewed other patches.

And there will often be patches that generate so little interest that they’ll 
never be reviewed no matter how many other patches the submitter reviews.

That said, it’s not a bad heuristic, and I suspect that someone who reviews 
patches is more likely to get their patch reviewed. But obviously there are no 
guarantees.

Best,

David

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Very confusing installcheck behavior with PGXS

[David E. Wheeler]

On Jan 7, 2016, at 11:20 AM, Jim Nasby  wrote:

>>> Also worth noting: the only reason I'm using pg_regress is it's the easiest
>>> way to get a test cluster. If not for that, I'd just use pg_prove since I'm
>>> already using pgTap.
>> 
>> In 9.5 you might want to "use PostgresNode" which allows you to initdb
>> and such.
> 
> Oooh, thanks! I might well just copy that into my pgxntool utility.

Is this documented somewhere? If it’s Perl, seems like it’d only be useful for 
those of us who compile from source, yes?

David



smime.p7s
Description: S/MIME cryptographic signature

[HACKERS] El Capitan Removes OpenSSL Headers

[David E. Wheeler]

Hackers,

Looks like Mac OS X 10.11 El Capitan has remove the OpenSSL header files. They 
recommend building your own or using native OS X SDKs, like Secure Transport:

  http://lists.apple.com/archives/macnetworkprog/2015/Jun/msg00025.html

I don’t suppose anyone has looked at what it would take to get PostgreSQL use 
Secure Transport, right? Here are the docs:

  
https://developer.apple.com/library/ios/documentation/Security/Reference/secureTransportRef/index.html

If it’s not feasible, those of use who need SSL connections on OS X will just 
have to build OpenSSL ourselves (or install from Homebrew or MacPorts).

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] OS X El Capitan and DYLD_LIBRARY_PATH

[David E. Wheeler]

On Nov 4, 2015, at 8:37 PM, Michael Paquier  wrote:

> There is:
> http://openradar.appspot.com/22807197

Yep, I filed that because I was unable to build the DBD::Oracle Perl module, 
since I can’t tell it where to find the SQL*Plus libraries. Big PITA.

Apple says that the more people file bugs, the more likely the issue is to get 
attention. So by all means, please file radars about this. You can reference 
21732670 as the bug you’re duping (they marked mine as a dupe for that one).

  https://http://bugreport.apple.com/

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Patch to install config/missing

[David E. Wheeler]

On Nov 2, 2015, at 1:07 PM, Tom Lane  wrote:

> I wonder how much we need that script at all though.  If, say, configure
> doesn't find bison, what's so wrong with just defining BISON=bison and
> letting the usual shell "bison: command not found" error leak through?

+1 This would certainly make it easier for downstream use cases, as well. Was 
not relishing having to parse the PERL variable to find out if Perl was missing.

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] [patch] extensions_path GUC

[David E. Wheeler]

On Oct 23, 2015, at 9:26 AM, Jim Nasby  wrote:

> I would love it if make check worked. make installcheck adds extra effort to 
> extension develoopment, not to mention leaving your actual install in a less 
> than pristine state.

I’ve wanted this for a long time. I think it would have to create a temporary 
cluster, fire up a server, install the extension(s), run the tests, shut down 
the server and delete the cluster.

> Possibly related to this... I'd also like to have other options for running 
> unit tests, besides pg_regress. I looked at it briefly and the big PITA about 
> doing it was having to manage the temporary database (and ideally temporary 
> cluster). If standing those up was separated from pg_regress it would make it 
> a lot easier for someone to customize how testing works under PGXS.

Right, then pg_regress could just be the default test framework.

Dvaid



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] tsvector work with citext

[David E. Wheeler]

On Sep 18, 2015, at 7:29 AM, Teodor Sigaev  wrote:

>> Fixable?
> 
> Fixed (9acb9007de30b3daaa9efc16763c3bc6e3e0a92d), but didn't backpatch 
> because it isn't a critical bug.

Great, thank you!

For those on older versions, what’s the simplest workaround?

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] tsvector work with citext

[David E. Wheeler]

On Sep 17, 2015, at 6:17 AM, Teodor Sigaev  wrote:

> I'm wrong, in this commit it was just renamed. It was originally coded by me. 
> But it's still oversight.

Fixable?

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

[HACKERS] tsvector work with citext

[David E. Wheeler]

Hey Hackers,

Is there a way to get tsvector_update_trigger() to work with citext columns? 
The attached case throws an error:

ERROR:  column "title" is not of a character type

Is the fact that citext is a (non-preferred) member of the string category not 
sufficient for this to work? If not, are there any workarounds?

Thanks,

David



try.sql
Description: application/sql



-- 
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] Horizontal scalability/sharding

[David E. Wheeler]

On Sep 1, 2015, at 1:47 PM, Robert Haas  wrote:

> Admittedly, there are some problems with snapshots here: if you don't
> do anything special about snapshots, then what you have here will be
> "eventually consistent" behavior.  But that might be suitable for some
> environments, such as very loosely coupled system where not all nodes
> are connected all the time.

Given that we’re discussing multi-node architectures here, you should expect 
that not all nodes will be connected at any time. Nodes fail, but the cluster 
should not.

> And, for those environments where you do
> need consistent snapshots, we can imagine ways to get that behavior,
> like having the GTM consider the transaction uncommitted until it's
> been logically replicated to every node.

Again, you need a way to deal with nodes going down. I can envision building a 
cluster with twelve nodes replicated to each of three 
geographically-distributed data centers. Each replication/sync model needs to 
be able to handle nodes going up and down, data centers or racks going up or 
down, and nodes being added and removed.

But even with smaller clusters, there’s no way around the fact that no system 
can guarantee that all nodes will be available at all times.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] pg_upgrade + Extensions

[David E. Wheeler]

On Aug 31, 2015, at 4:20 PM, Bruce Momjian  wrote:

>> I think it would help if its noted somewhere in the document as it would have
>> helped us save some time understanding why it was failing and why it was
>> looking for json_build. 
> 
> The problem is that this is a rare case where you had an extension that
> was later included in Postgres.

Maybe not so rare. Thanks to Andrew, we’ve had to do this for both 9.2-9.3 
(json_object) and 9.3-9.4 (json_build).

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] pg_upgrade + Extensions

[David E. Wheeler]

On Aug 31, 2015, at 4:58 PM, Tom Lane  wrote:

> In any case, there is plenty of precedent for hard-coding knowledge about
> specific version updates into pg_upgrade.  The question here is whether
> it's feasible to handle extensions that way.  I think we could reasonably
> expect to know about cases where a formerly separate extension got
> integrated into core,

+1

> but are there other cases where pg_upgrade would
> need to ignore an extension in the old database?

Not that I can think of, unless it’s already present because it was in 
template1 or something.

David



smime.p7s
Description: S/MIME cryptographic signature

[HACKERS] pg_upgrade + Extensions

[David E. Wheeler]

Hackers,

My co-workers tell me that pg_upgrade told them to drop the colnames and 
hostname extensions before upgrading from 9.3 to 9.4. Fortunately, Postgres had 
 not recorded any dependencies on functions from these extensions (not sure why 
not, since we do user them, but for the moment grateful), so it wasn’t a big 
deal to drop them and then add them back after finishing the upgrade. But 
frankly I don’t understand why this was necessary. It’s true that they’re C 
extensions with shared libraries, but there are separate .so files for the 9.3 
and 9.4 installs.

Would there be a way to convince pg_upgrade that extensions don’t need to be 
dropped before upgrading?

Thanks,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] pg_upgrade + Extensions

[David E. Wheeler]

On Jul 10, 2015, at 11:32 AM, Smitha Pamujula smitha.pamuj...@iovation.com 
wrote:

 I just tested and yes that worked. Once we have the new library for the 
 hostname, pg_upgrade is not complaining about the hostname extension. 

Great, thank you Smitha -- and Tom for the pointer.

 Your installation references loadable libraries that are missing from the
 new installation.  You can add these libraries to the new installation,
 or remove the functions using them from the old installation.  A list of
 problem libraries is in the file:
 loadable_libraries.txt
 
 Failure, exiting
 [postgres@pdxdvrptsrd04 ~]$ cat loadable_libraries.txt
 Could not load library json_build
 ERROR:  could not access file json_build: No such file or directory

So you drop the json_build extension before upgrading, but pg_upgrade still 
complains that it’s missing? That seems odd.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] creating extension including dependencies

[David E. Wheeler]

On Jul 7, 2015, at 6:41 AM, Andres Freund and...@anarazel.de wrote:

 At the minimum I'd like to see that CREATE EXTENSION foo; would install
 install extension 'bar' if foo dependended on 'bar' if CASCADE is
 specified. Right now we always error out saying that the dependency on
 'bar' is not fullfilled - not particularly helpful.

+1

If `yum install foo` also installs bar, and `pgxn install foo` downloads, 
builds, and installs bar, it makes sense to me that `CREATE EXTENSION foo` 
would install bar if it was available, and complain if it wasn’t.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] RFC: Remove contrib entirely

[David E. Wheeler]

On Jun 5, 2015, at 12:34 AM, Jim Nasby jim.na...@bluetreble.com wrote:

 A number of modules also run Travis-CI. Might be worth having a way for a 
 module to provide it's status .png.

Right. Just stick it in your README.

  http://blog.pgxn.org/post/116087351668/badges

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Further issues with jsonb semantics, documentation

[David E. Wheeler]

On Jun 4, 2015, at 12:16 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote:

 I'm just skimming here, but if a jsonb_path type is being proposed,

Is this not the purpose of JSQuery?

  https://code.google.com/p/gwtquery/wiki/JsQuery

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] RFC: Remove contrib entirely

[David E. Wheeler]

On Jun 4, 2015, at 11:53 AM, Neil Tiffin ne...@neiltiffin.com wrote:

 I have looked at PGXN and would never install anything from it.  Why?  
 Because it is impossible to tell, without inside knowledge or a lot of work, 
 what is actively maintained and tested, and what is an abandoned 
 proof-of-concept or idea.

Well, you can see the last release dates for a basic idea of that sort of 
thing. Also the release status (stable, unstable, testing).

 There is no indication of what versions of pg any of PGXN modules are tested 
 on, or even if there are tests that can be run to prove the module works 
 correctly with a particular version of pg.

Yeah, I’ve been meaning to integrate http://pgxn-tester.org/ results for all 
modules, which would help with that. In the meantime you can hit that site 
itself. Awesome work by Tomas Vondra.

 There are many modules that have not been updated for several years.  What is 
 their status?  If they break is there still someone around to fix them or 
 even cares about them?  If not, then why waste my time.

These are challenges to open-source software in general, and not specific to 
PGXN.

 So adding to Jim’s comment above, anything that vets or approves PGXN modules 
 is, in my opinion, essentially required to make PGXN useful for anything 
 other than a scratchpad.

Most of the distributions on PGXN feature links to their source code 
repositories.

 A big help would be to pull in the date of the last git commit in the module 
 overview and ask the authors to edit the readme to add what major version of 
 pg the author last tested or ran on.

That’s difficult to maintain; I used to do it for pgTAP, was too much work. 
pgxn-tester.org is a much better idea.

Best,

David




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] jsonb_set: update or upsert default?

[David E. Wheeler]

On May 22, 2015, at 7:22 PM, Andrew Dunstan and...@dunslane.net wrote:

 The proposed flag for jsonb_set (the renamed jsonb_replace) in the patch I 
 recently published is set to false, meaning that the default behaviour is to 
 require all elements of the path including the last to be present. What that 
 does is effectively UPDATE for jsonb. If the flag is true, then the last 
 element can be absent, in which case it's created, so this is basically 
 UPSERT for jsonb. The question is which should be the default. We got into 
 the weeds on this with suggestions of throwing errors on missing paths, but 
 that's going nowhere, and I want to get discussion back onto the topic of 
 what should be the default.

Here’s JavaScript in Chrome, FWIW:

var f = {}
f[foo][0] = “bar
Uncaught TypeError: Cannot set property '0' of undefined
at anonymous:2:13
at Object.InjectedScript._evaluateOn (anonymous:895:140)
at Object.InjectedScript._evaluateAndWrap (anonymous:828:34)
at Object.InjectedScript.evaluate (anonymous:694:21)

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Fixing busted citext function declarations

[David E. Wheeler]

On May 11, 2015, at 5:01 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Me too.  Something fell through the cracks rather badly there :-(.
 Would you check your commit history to see if anything else got missed?

Let’s see…

In 
https://github.com/theory/citext/commit/4030b4e1ad9fd9f994a6cdca1126a903682acae4
 I copied your use of specifying the full path to pg_catalog function, which is 
still in core.

In 
https://github.com/theory/citext/commit/c24132c098a822f5a8669ed522e747e01e1c0835,
 I made some tweaks based on you change you made to some version of my patch. 
Most are minor, or just for functions needed for 8.4 and not later versions.

In 
https://github.com/theory/citext/commit/2c7e997fd60e2b708d06c128e5fd2db51c7a9f33,
 I added a cast to bpchar, which is in core.

In 
https://github.com/theory/citext/commit/cf988024d18a6ddd9a8146ab8cabfe6e0167ba26
 and 
https://github.com/theory/citext/commit/22f91a0d50003a0c1c27d1fbf0bb5c0a1e3a3cad
 I switched from VARSIZE_ANY_EXHDR() to strlen() at your suggestion. Also still 
there.


Anyway, those are all from 2008 and pretty much just copy changes you made to 
core. The return value of regexp_matches() is the only significant change since 
then. So I think we’re good.

Best,

David\

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Fixing busted citext function declarations

[David E. Wheeler]

Tom,

On May 5, 2015, at 9:40 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 In
 http://www.postgresql.org/message-id/bn1pr04mb37467aa1d412223b3d4a595df...@bn1pr04mb374.namprd04.prod.outlook.com
 it's revealed that the citext extension misdeclares its versions of
 regexp_matches(): they should return SETOF text[] but they're marked
 as returning just text[].

I wanted to make sure my backport was fixed for this, but it turns out it was 
already fixed as of this commit:

  https://github.com/theory/citext/commit/99c925f

Note that I credited you for the spot --- way back in October 2009! Pretty 
confused how the same change wasn’t made to the core contrib module back then.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Fixing busted citext function declarations

[David E. Wheeler]

On May 5, 2015, at 10:07 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote:

 So AFAICS we need to actually drop and recreate
 the citext regexp_matches() functions in the upgrade script.  That means
 ALTER EXTENSION citext UPDATE will fail if these functions are being
 used in any views.  That's annoying but I see no way around it.  (We
 could have the upgrade script do DROP CASCADE, but that seems way too
 destructive.)
 
 I think we do need to have the upgrade script drop/recreate without
 cascade.  Then, users can alter extension upgrade, note the
 problematic views (which should be part of the error message), drop
 them, then retry the extension update and re-create their views.  This
 is necessarily a manual procedure -- I don't think we can re-create
 views using the function automatically.  CASCADE seems pretty dangerous.

FWIW, this is a challenge inherent in all extension upgrade scripts. It’d be 
great if there was a way to defer such dependency errors to COMMIT time, so if 
a function is replaced with a new one that’s compatible with the old, the 
dependency tree could be updated automatically.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Make more portable TAP tests of initdb

[David E. Wheeler]

On Apr 14, 2015, at 9:05 AM, Alvaro Herrera alvhe...@2ndquadrant.com wrote:

 http://perldoc.perl.org/File/Path.html
 With this formulation:
 remove_tree($tempdir, {keep_root = 1});
 
 Does Perl 5.8 have this?

Yes, it does.

  http://cpansearch.perl.org/src/NWCLARK/perl-5.8.9/lib/File/Path.pm

Best,

David

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Make more portable TAP tests of initdb

[David E. Wheeler]

On Apr 14, 2015, at 1:21 PM, Alvaro Herrera alvhe...@2ndquadrant.com wrote:

 Castoroides has 5.8.4.  Oops.

WUT.

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Patch: Add launchd Support

[David E. Wheeler]

On Mar 20, 2015, at 4:11 PM, David E. Wheeler da...@justatheory.com wrote:

 No one replied. Want a new patch with that?

Here it is.

Best,

David


launchd2.patch
Description: Binary data




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Patch: Add launchd Support

[David E. Wheeler]

On Mar 19, 2015, at 8:12 PM, Bruce Momjian br...@momjian.us wrote:

 Where are we on this?

I suggested this plist:

dict
keyDisabled/key
false/
keyLabel/key
stringorg.postgresql.postgresql/string
keyUserName/key
stringpostgres/string
keyGroupName/key
stringpostgres/string
keyProgramArguments/key
array
string/usr/local/pgsql/bin/postgres/string
string-D/string
string/usr/local/pgsql/data/string
/array
   keyStandardOutPath/key
   string/usr/local/pgsql/data/launchd.log/string
   keyStandardErrorPath/key
   string/usr/local/pgsql/data/launchd.log/string
keyOnDemand/key!-- OS X 10.4 --
false/
keyKeepAlive/key!-- OS X 10.5+ --
true/
/dict
/plist

No one replied. Want a new patch with that?

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Patch: Add launchd Support

[David E. Wheeler]

On Mar 20, 2015, at 4:21 PM, Jim Nasby jim.na...@bluetreble.com wrote:
 
 On 3/20/15 6:11 PM, David E. Wheeler wrote:
  keyProgramArguments/key
  array
  string/usr/local/pgsql/bin/postgres/string
  string-D/string
  string/usr/local/pgsql/data/string
  /array
 
 Hrm, would /var/db/postgres be better? I'm not sure if the stuff Apple does 
 with /private/ would cause problems though. (In any case, I think postgres is 
 better than pgsql.)
 
keyStandardOutPath/key
string/usr/local/pgsql/data/launchd.log/string
keyStandardErrorPath/key
string/usr/local/pgsql/data/launchd.log/string
 
 Wouldn't /var/log be better?

/usr/local/pgsql has been the standard install location for the PostgreSQL core 
distribution for as long as I can remember, including on OS X. Our original OS 
X startup script refers to it. I figure it’s best to keep it consistent.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] using Core Foundation locale functions

[David E. Wheeler]

On Nov 28, 2014, at 8:43 AM, Peter Eisentraut pete...@gmx.net wrote:
 
 At the moment, this is probably just an experiment that shows where
 refactoring and better abstractions might be suitable if we want to
 support multiple locale libraries.  If we want to pursue ICU, I think
 this could be a useful third option.

Gotta say, I’m thrilled to see movement on this front, and especially pleased 
to see how consensus seems to be building around an abstracted interface to 
keep options open. This platform-specific example really highlights the need 
for it (I had no idea that there was separate and more up-to-date collation 
support in Core Foundation than in the UNIX layer of OS X).

Really looking forward to seeing where we end up.

Best,

David
 



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Trailing comma support in SELECT statements

[David E. Wheeler]

On Oct 24, 2014, at 6:36 AM, Alex Goncharov alex.goncharov@gmail.com 
wrote:

 Another dimension of the trouble is breaking the operation of the
 tools that parse SQL statements for various purposes, e.g. for
 dependency analysis.

That’s a valid point.

 This is a misfeature for the benefit of edit-lazy users only.

This one, however, is more a judgment of people and their practices rather than 
the feature itself. Color me unimpressed.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Trailing comma support in SELECT statements

[David E. Wheeler]

On Oct 18, 2014, at 7:06 PM, Jim Nasby jim.na...@bluetreble.com wrote:

 Yes.
 
 The only case I can think of where we wouldn't want this is COPY.
 
 BTW, this should also apply to delimiters other than commas; for example, 
 some geometry types use ; as a delimiter between points.

I don’t think it should apply to the internals of types, necessarily. JSON, for 
example, always dies on an trailing comma, so should probably stay that way. 
Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want 
their behaviors to diverge.

D



smime.p7s
Description: S/MIME cryptographic signature

[HACKERS] Patch: Add launchd Support

[David E. Wheeler]

Hackers,

In Mac OS X 10.10 “Yosemite,” Apple removed SystemStarter, upon which our OS X 
start script has relied since 2007. So here is a patch that adds support for 
its replacement, launchd. It includes 7 day log rotation like the old script 
did. The install script still prefers the SystemStarter approach for older 
versions of the OS, for the sake of easier backward compatibility. We could 
change that if we wanted, since launchd has been part of the OS for around a 
decade.



launchd.patch
Description: Binary data




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Patch: Add launchd Support

[David E. Wheeler]

On Oct 20, 2014, at 4:36 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 (1) I'd vote for just removing the SystemStarter stuff: it complicates
 understanding what's happening, to no very good end.  We can easily
 check that the launchd way works back to whatever we think our oldest
 supported OS X release is.  (10.4.x according to the buildfarm, at least;
 and I think SystemStarter was deprecated even then ...)

Okay. Might have to use OnDemand instead of KeepAlive on 10.4. The former was 
deprecated in 10.5, but I’m not sure when the former was added.

 (2) AFAICS, this .plist file doesn't do anything about launchd's habit of
 not waiting for the network to come up.  See my comments in today's thread
 in -general:
 http://www.postgresql.org/message-id/1239.1413823...@sss.pgh.pa.us

Ha! How funny you posted a call for a patch today. I didn’t see that, just 
needed to get it working today myself.

Anyway, I knew there was a reason I didn’t bother with this years ago: launchd 
does not support dependencies. From the launchd.plist(5)

 DEPENDENCIES
  Unlike many bootstrapping daemons, launchd has no explicit dependency 
 model.
  Interdependencies are expected to be solved through the use of IPC. It is
  therefore in the best interest of a job developer who expects dependents 
 to
  define all of the sockets in the configuration file. This has the added 
 ben-
  efit of making it possible to start the job based on demand instead of 
 imme-
  diately.  launchd will continue to place as many restrictions on jobs 
 that
  do not conform to this model as possible.

This another reason not to use KeepAlive, I guess. OnDemand is supposed to fire 
up a job only when it’s needed. No idea what that means. We might be able to 
put something in LaunchEvents that gets it to fire when the network launches, 
but documentation is hella thin (and may only be supported on Yosemite, where 
there are a bunch of poorly-documented launchd changes).

 (3) I don't think you want Disabled = true.

It’s the default. When you run `launchctl load -w` it overrides it to false in 
its database. I’m fine to have it be less opaque, though.

 (4) I'm suspicious of all the -c arguments in the .plist file.  In general
 I'm not a fan of specifying GUCs on the postmaster command line; that
 makes it impossible to override their values via normal methods like
 postgresql.conf or ALTER SYSTEM.

Yeah, I am okay with removing those; they weren’t in the SystemStarter script. 
Was the only way to replicate the log rotation stuff, but probably best not to 
do that in the start script, anyway.

 (5) According to the launchd.plist man page, there are options for
 redirecting stdout and stderr to someplace useful.  It might be worth
 exercising those ...

Suggestions?

Best,

David




smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Patch: Add launchd Support

[David E. Wheeler]

On Oct 20, 2014, at 4:58 PM, Jim Nasby jim.na...@bluetreble.com wrote:

 You're enabling POSTGRESQL in /etc/hostconfig before any of the files are 
 copied over... what happens if we puke before the files get copied? Would it 
 be better to enable after the scripts are in place?

That code was there; I just indented it in an if/then block.

 BTW, Mavericks has a comment that /etc/hostconfig is going away, but google 
 isn't telling me what's replacing it...

launchd.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Patch: Add launchd Support

[David E. Wheeler]

On Oct 20, 2014, at 5:17 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 [ looks ... ]  Yeah, there's no mention of KeepAlive in 10.4's
 launchd.plist man page.  It does have a convenient example
 saying that OnDemand = false does what we want:

Yeah, let’s see if we can cover both.

 I'd just drop them into files in the data directory; we're still going
 to recommend that people use the logging_collector, so this is just a
 stopgap to collect startup errors.

How about this?

plist version=1.0
dict
keyDisabled/key
false/
keyLabel/key
stringorg.postgresql.postgresql/string
keyUserName/key
stringpostgres/string
keyGroupName/key
stringpostgres/string
keyProgramArguments/key
array
string/usr/local/pgsql/bin/postgres/string
string-D/string
string/usr/local/pgsql/data/string
/array
keyStandardOutPath/key
string/usr/local/pgsql/data/launchd.log/string
keyStandardErrorPath/key
string/usr/local/pgsql/data/launchd.log/string
keyOnDemand/key!-- OS X 10.4 --
false/
keyKeepAlive/key!-- OS X 10.5+ --
true/
/dict
/plist

No fix for the networking issue, of course.

Best,

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] Trailing comma support in SELECT statements

[David E. Wheeler]

On Oct 17, 2014, at 3:18 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Yeah, exactly.  Personally I'm *not* for this, but if we do it we should
 do it consistently: every comma-separated list in the SQL syntax should
 work the same.

PL/pgSQL, too, I presume.

D

smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] json (b) and null fields

[David E. Wheeler]

On Sep 29, 2014, at 9:09 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 I seem to recall that we've run into practical difficulties with moving
 extensions into core.  It might be OK for a functions-only extension
 though.

It does make upgrading difficult, though, as I’ve learned the hard way with 
when upgrading from 9.2 with json_enhancements to 9.3 without. We had to do 
some selective dropping and re-creating of functions, views, and triggers to 
get it all to work properly.

Best,

David


signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] jsonb format is pessimal for toast compression

[David E. Wheeler]

On Sep 4, 2014, at 7:26 PM, Jan Wieck j...@wi3ck.info wrote:

 This is only because the input data was exact copies of the same strings over 
 and over again. PGLZ can very well compress slightly less identical strings 
 of varying lengths too. Not as well, but well enough. But I suspect such 
 input data would make it fail again, even with lengths.

We had a bit of discussion about JSONB compression at PDXPUG Day this morning. 
Josh polled the room, and about half though we should apply the patch for 
better compression, while the other half seemed to want faster access 
operations. (Some folks no doubt voted for both.) But in the ensuing 
discussion, I started to think that maybe we should leave it as it is, for two 
reasons:

1. There has been a fair amount of discussion about ways to better deal with 
this in future releases, such as hints to TOAST about how to compress, or the 
application of different compression algorithms (or pluggable compression). I’m 
assuming that leaving it as-is does not remove those possibilities.

2. The major advantage of JSONB is fast access operations. If those are not as 
important for a given use case as storage space, there’s still the JSON type, 
which *does* compress reasonably well. IOW, We already have a JSON alternative 
the compresses well. So why make the same (or similar) trade-offs with JSONB?

Just my $0.02. I would like to see some consensus on this, soon, though, as I 
am eager to get 9.4 and JSONB, regardless of the outcome!

Best,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Missing plpgsql.o Symbols on OS X

[David E. Wheeler]

On Aug 27, 2014, at 9:53 PM, Ashesh Vashi ashesh.va...@enterprisedb.com wrote:

 Please add -arch x86_64 to your LD_FLAGS and CFLAGS in your make file.

This made no difference:

LDFLAGS = -arch x86_64
CFLAGS = -arch x86_64

Best,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Missing plpgsql.o Symbols on OS X

[David E. Wheeler]

On Aug 27, 2014, at 4:20 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Yeah, but plpgsql.so is mentioned nowhere on your command line.
 
 I'm not too sure about the dynamic-linking rules on OS X, but I'd not be
 surprised if you need to provide a reference to plpgsql.so in its final
 installed location (ie, a reference to it in the build tree may appear to
 link and then fail at runtime).

Ah. Is there a recommended way to do that in a PGXS-powered Makefile?

Thanks,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

[HACKERS] Missing plpgsql.o Symbols on OS X

[David E . Wheeler]

Hackers,

I’m trying to build Pavel’s plpgsql_check against the 9.4 beta on OS X 10.9, 
but get these errors:

make
gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith 
-Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute 
-Wformat-security -fno-strict-aliasing -fwrapv  
-I/usr/local/pgsql/lib/pgxs/src/makefiles/../../src/pl/plpgsql/src -bundle 
-multiply_defined suppress -o plpgsql_check.so plpgsql_check.o 
-L/usr/local/pgsql/lib -L/usr/local/lib  -L/usr/local/lib 
-Wl,-dead_strip_dylibs   -bundle_loader /usr/local/pgsql/bin/postgres
Undefined symbols for architecture x86_64:
 _exec_get_datum_type, referenced from:
 _check_target in plpgsql_check.o
 _plpgsql_build_datatype, referenced from:
 _check_stmt in plpgsql_check.o
 _plpgsql_compile, referenced from:
 _check_plpgsql_function in plpgsql_check.o
 _plpgsql_parser_setup, referenced from:
 _prepare_expr in plpgsql_check.o
 _plpgsql_stmt_typename, referenced from:
 _put_error in plpgsql_check.o
ld: symbol(s) not found for architecture x86_64
clang: error: linker command failed with exit code 1 (use -v to see invocation)
make: *** [plpgsql_check.so] Error 1

Which is odd, because plpgsql_check.c includes plpgsql.h, and those symbols do 
appear to be in plpgsql.so:

$ nm /usr/local/pgsql/lib/plpgsql.so | grep _exec_get_datum_type
f110 T _exec_get_datum_type
f380 T _exec_get_datum_type_info

So, uh, what gives? Do I need to something extra to get it to properly find 
plpgsql.so?

Thanks,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Why is it JSQuery?

[David E. Wheeler]

On Jun 15, 2014, at 1:58 PM, Josh Berkus j...@agliodbs.com wrote:

 In other words, what I'm saying is: I don't think there's an existing,
 poplular syntax we could reasonably use.

Okay, I’m good with that. Would be handy to document it in such a way as to 
kind of put it forward as a standard. :-)

D



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] make check For Extensions

[David E. Wheeler]

On Jun 15, 2014, at 12:25 AM, Fabien COELHO coe...@cri.ensmp.fr wrote:

 I'm not sure the extension is sought for in the cluster (ie the database data 
 directory). If you do make install the shared object is installed in some 
 /usr/lib/postgresql/... directory (under unix), and it is loaded from there, 
 but I understood that you wanted to test WITHOUT installing against the 
 current postgresql.

I would assume there is a way to do it with a path…it’ just a SMOP, of course.

D



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] make check For Extensions

[David E. Wheeler]

On Jun 12, 2014, at 11:40 PM, Fabien COELHO coe...@cri.ensmp.fr wrote:

 I would suggest to add that to https://wiki.postgresql.org/wiki/Todo.
 
 I may look into it when I have time, over the summer. The key point is that 
 there is no need for a temporary installation, but only of a temporary 
 cluster, and to trick this cluster into loading the uninstalled extension, 
 maybe by playing with dynamic_library_path in the temporary cluster.

The temporary cluster will be in a temporarty `initdb`ed directory, no? If so, 
you can just install the extension there.

Best,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] make check For Extensions

[David E. Wheeler]

On Jun 12, 2014, at 11:28 AM, Fabien COELHO coe...@cri.ensmp.fr wrote:

 My 0.02€: It is expected to work, more or less, see the end of
 
 http://www.postgresql.org/docs/9.3/static/extend-pgxs.html

That says:

“The scripts listed in the REGRESS variable are used for regression testing of 
your module, which can be invoked by make installcheck after doing make 
install. For this to work you must have a running PostgreSQL server.”

That does not mean that it starts a new cluster on a port. It means it will 
test it against an existing cluster after you have installed into that cluster.

 It invokes psql which is expected to work directly. Note that there is no 
 temporary installation, it is tested against the installed and running 
 postgres. Maybe having the ability to create a temporary installation, as you 
 suggest, would be a nice extension.

Yes, that’s what I would like, so I could test *before* installing.

Best,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

[HACKERS] make check For Extensions

[David E. Wheeler]

Hackers,

Andres said during the unconference last month that there was a way to get 
`make check` to work with PGXS. The idea is that it would initialize a 
temporary cluster, start it on an open port, install an extension, and run the 
extension's test suite. I think the pg_regress --temp-install, maybe? I poked 
through the PGXS makefiles, and although it looks like there *might* be 
something like this for in-core contrib extensions, but not for 
externally-distributed extensions.

Is there something I could add to my extension Makefiles so that `make check` 
or `make test` will do a pre-install test on a temporary cluster?

Thanks,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Why is it JSQuery?

[David E. Wheeler]

On Jun 6, 2014, at 3:50 PM, Josh Berkus j...@agliodbs.com wrote:

 Maybe we should call it jsonesque  ;-)

I propose JOQL: JSON Object Query Language.

Best,

David

PS: JAQL sounds better, but [already exists](http://code.google.com/p/jaql/).


signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Why is it JSQuery?

[David E. Wheeler]

On Jun 10, 2014, at 12:06 PM, Oleg Bartunov obartu...@gmail.com wrote:

 we have many other tasks than guessing the language name.
 jsquery is just an extension, which we invent to test our indexing
 stuff.  Eventually, it grew out.  I think we'll think on better name
 if developers agree to have it in core. For now, jsquery is good
 enough to us.
 
 jsquery name doesn't need to be used at all, by the way.

Yeah, I was more on about syntax than the name. We can change that any time 
before you release it.

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Why is it JSQuery?

[David E. Wheeler]

On Jun 6, 2014, at 6:54 AM, Oleg Bartunov obartu...@gmail.com wrote:

 Jsquery - is QUERY language, JsonPath - is language to EXTRACT json parts.
 
Sure, but could we not potentially build on its syntax, instead of building a 
new one? I’m not saying we *should*, but if we don’t, I think there should be a 
discussion about why not. For example, I think it would not be a good idea to 
follow [JSONiq](http://www.jsoniq.org/) because who wants to write queries in 
JSON? (Have we learned nothing from XSLT?).

Here’s a (partial) list of existing JSON query languages:

  http://stackoverflow.com/a/7812073/79202

The arguments might be:

* [JSONiq](http://jsoniq.org/): Queries in JSON? Gross!
* [UNQL](http://www.unqlspec.org/): Too similar to SQL
* [JAQL](https://code.google.com/p/jaql/): Too different from SQL
* [JSONPath](http://goessner.net/articles/JsonPath/): Too verbose
* [JSON Query](https://github.com/mmckegg/json-query): Too little there
* [Mongo](http://www.mongodb.org/display/DOCS/Inserting#Inserting-JSON): Gross 
syntax
* [LINQ](http://james.newtonking.com/archive/2008/03/02/json-net-2-0-beta-2): 
Too similar to SQL
* [searchjs](https://github.com/deitch/searchjs): Queries in JSON? Gross!
* [JQuery](http://jquery.org/): It's for HTML, not JSON
* [SpahQL](http://danski.github.io/spahql/): More like XPath
* [ObjectPath](http://adriank.github.io/ObjectPath/): Too verbose
* [JFunk](https://code.google.com/p/jfunk/): XPathy
* [JData](http://jaydata.org): Queries in JavaScript? C’mon.

These are just off-the-cuff evaluations in 10 minutes of looking -- surely not 
all of them are accurate. Some of them maybe *are* useful to emulate. It’s 
definitely worthwhile, IMHO, to evaluate prior art and decide what, if any of 
it, should inspire the JSQuery syntax, and there should be reasons why and why 
not.

I do think that the name should be changed if we don’t follow an existing 
standard, as [JSQuery](https://code.google.com/p/gwtquery/wiki/JsQuery) is 
already a thing.

Best,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Why is it JSQuery?

[David E. Wheeler]

On Jun 6, 2014, at 12:51 PM, Josh Berkus j...@agliodbs.com wrote:

 * [JAQL](https://code.google.com/p/jaql/): Too different from SQL
 * [JSONPath](http://goessner.net/articles/JsonPath/): Too verbose
 
 I don't agree with the too verbose, but lacking AND|OR is pretty crippling.

I had enough people complain about Test::XPath, which tests the structure of 
XML and HTML documents using XPath. They didn't like how verbose XPath was, 
preferring CSS selectors. So I ended up with a patch to support CSS syntax, 
too. CSS-style syntax is part of what people like about JQuery, too.

 Well, I'd also say that we don't care about syntaxes which are not
 already popular.  There's no point in being compatible with something
 nobody uses.  How many of the above have any uptake?

I think there is JQuery, JSONPath, and everything else, really. If we can draw 
some parallels, I think that would be sufficient to make people comfortable.

 I do think that the name should be changed if we don’t follow an existing 
 standard, as [JSQuery](https://code.google.com/p/gwtquery/wiki/JsQuery) is 
 already a thing.
 
 I saw that too, but I don't get the impression that Google jsquery is
 all that active.   No?

It’s Google. You really want to wrangle with their attorneys?

David




signature.asc
Description: Message signed with OpenPGP using GPGMail

[HACKERS] Why is it JSQuery?

[David E. Wheeler]

Oleg, Teodor, and Hackers:

Love what you’re doing with JSQuery. I’m curious, though, whether you 
considered adopting an existing syntax, such as JSONPath.

  http://goessner.net/articles/JsonPath/

Might be easier for people to pick up and use. Thoughts?

Best,

David


signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] Why is it JSQuery?

[David E. Wheeler]

On Jun 5, 2014, at 5:25 PM, Andrew Dunstan and...@dunslane.net wrote:

 My understanding is that it's meant to be analogous to tsquery.
 
 At first glance, JsonPath doesn't seem to support AND and OR operators, which 
 would make it rather less expressive than I gather JSQuery is meant to be.

Yes, but perhaps it could be a superset.

I guess my real question is: Should it not be based on some existing dialect, 
preferably something in fairly wide use outside the Postgres community?

Unless that something is awful, of course.

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] [PATCH] Replacement for OSSP-UUID for Linux and BSD

[David E. Wheeler]

On May 27, 2014, at 7:44 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 In either case, the problem remains of exactly what to call the
 e2fsprogs-derived implementation.  It does seem that people who are
 familiar with these libraries call it that, but I'm worried that such
 a name will confuse those not so familiar.

--with-libuuid?

D



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] [PATCH] Replacement for OSSP-UUID for Linux and BSD

[David E. Wheeler]

On May 26, 2014, at 6:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 This means that if we want to give users control over which implementation
 gets selected, we actually need *three* configure switches.  In the
 attached revision of Matteo's patch, I called them --with-ossp-uuid
 (the existing switch name), --with-linux-uuid, and --with-bsd-uuid.
 I'm not necessarily wedded to the latter two names; in particular it seems
 unfortunate that the right one to use on OS X is --with-linux-uuid.
 But I think --with-e2fsprogs-uuid is right out, so it's not clear what
 would be better.

How about --with-unix-uuid? Or --with-ext2-uuid?

Which one is the default -- or is there one? Should we use some sort of mapping 
to select the right switch by platform, or if ossp-uuid appears to be installed?

Best,

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] [PATCH] Replacement for OSSP-UUID for Linux and BSD

[David E. Wheeler]

On May 26, 2014, at 9:30 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 How about --with-unix-uuid? Or --with-ext2-uuid?
 
 Meh.  Unix certainly subsumes BSD, so that doesn't seem like a very
 useful distinction.  I guess we could use ext2 but that would just
 confuse most people.

--with-uuid?

 Which one is the default -- or is there one?
 
 The point here is that we won't make a default choice.

So no UUID functions by default, which I guess has been the case all along?

Always seemed weird to me that there was a core configure option specific to a 
contrib module.

D



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] jsonb inequality operators

[David E. Wheeler]

On May 20, 2014, at 4:39 PM, Andrew Dunstan and...@dunslane.net wrote:

 I have just noticed as I am preparing my slides well ahead of time :-) that 
 we haven't documented the inequality operators of jsonb. Is that deliberate 
 or an oversight?

That’s gotta be an oversight. The hash and btree index support is documented, 
which implies those operators, yes?

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)

[David E. Wheeler]

On May 6, 2014, at 2:20 PM, Bruce Momjian br...@momjian.us wrote:

 Stuck on the naming question.  I'd be willing to do the patch legwork
 if we had a consensus (or even a proposal) for what to rename the
 current jsonb_ops to.
 
 Well, then, we only have a few days to come up with a name.

What are the options?

D



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] default opclass for jsonb (was Re: Call for GIST/GIN/SP-GIST opclass documentation)

[David E. Wheeler]

On May 6, 2014, at 3:39 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Meh.  I would not think that that represents effective use of JSON:
 if the rows are all the same, why aren't you exposing that structure
 as regular SQL columns?  IMHO, the value of JSON fields within a SQL
 table is to deal with data that is not so well structured.

The use of JSON will not be ideal -- not in this sense. For example, at $work, 
we’re using it in place of an EAV model. Hence most rows have the same keys (or 
a subset of known keys). Or think of your favorite JSON API: every call to 
http://api.pgxn.org/user/$username.json is going to have a very similar 
structure.

 In any case, it was certainly the complaint that insertions might
 fail altogether that made me (and I assume others) want to not have
 jsonb_ops as the default opclass.  Is there a good reason not to
 fix that limitation while we still can?

Fixing++

David



signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [HACKERS] psql blows up on BOM character sequence

[David E. Wheeler]

On Mar 23, 2014, at 8:03, Guillaume Lelarge guilla...@lelarge.info wrote:
 
 Just a quick comment on this. Yes, pgAdmin always added a BOM in every
 SQL files it wrote.

From 
http://stackoverflow.com/questions/2223882/whats-different-between-utf-8-and-utf-8-without-bom:

According to the Unicode standard, the BOM for UTF-8 files is not recommended:

2.6 Encoding Schemes

... Use of a BOM is neither required nor recommended for UTF-8, but may be 
encountered in contexts where UTF-8 data is converted from other encoding forms 
that use a BOM or where the BOM is used as a UTF-8 signature. See the “Byte 
Order Mark” subsection in Section 16.8, Specials, for more information.

Re: [HACKERS] psql blows up on BOM character sequence

[David E. Wheeler]

On Mar 21, 2014, at 2:16 PM, Andrew Dunstan and...@dunslane.net wrote:

 Surely if it were really a major annoyance, someone would have sent code to 
 fix it during the last 4 years and more since the above.
 
 I suspect it's a minor annoyance :-)
 
 But by all means add it to the TODO list if it's not there already.

I have cleaned up many a BOM added to files that made psql blow up. I think 
PGAdmin III was a culprit, though I’m not sure (I don’t use, it, cleaned up 
after coworkers who do).

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] jsonb and nested hstore

[David E. Wheeler]

On Mar 6, 2014, at 1:51 AM, Peter Geoghegan p...@heroku.com wrote:

 It's true for perl. Syntax of hstore is close to hash/array syntax and it's
 easy serialize/deserialize hstore to/from perl. Syntax of hstore was
 inspired by perl.
 
 I understand that. There is a module on CPAN called Pg::hstore that
 will do this; it appears to have been around since 2011. I don't use
 Perl, so I don't know a lot about it. Perhaps David Wheeler has an
 opinion on the value of Perl-like syntax, as a long time Perl
 enthusiast?

HSTORE was inspired by the syntax of Perl hash declarations, but it is not 
compatible. Notably, HSTORE the HSTORE can have a value `NULL`, while in Perl 
hashes it’s `undef`. So you cannot simply `eval` an HSTORE to get a Perl hash 
unless you are certain there are no NULLs.

Besides, string eval in Perl is considered unsafe. Parsing is *much* safer.

 In any case, Perl has excellent support for JSON, just like every
 other language - you are at no particular advantage in Perl by having
 a format that happens to more closely resemble the format of Perl
 hashes and arrays. I really feel that we should concentrate our
 efforts on one standardized format here. It makes the effort to
 integrate your good work, in a way that makes it available to everyone
 so much easier.

I agree. I like HSTORE, but now that JSON is so standard (in fact, as of this 
week, a *real* standard! http://rfc7159.net/rfc7159), and its support is so 
much better than that of HSTORE, including in Perl, I believe that it should be 
priority over HSTORE. I’m happy if HSTORE has the same functionality as JSONB, 
but given the choice, all other things being equal, as a Perl hacker I will 
always choose JSONB.

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] jsonb and nested hstore

[David E. Wheeler]

On Mar 5, 2014, at 8:49 AM, Andrew Dunstan and...@dunslane.net wrote:

 I think that was my estimate, but Peter did offer to do it. He certainly 
 asserted that the effort required would not be great. I'm all for taking up 
 his offer.

+1 to this. Can you and Peter collaborate somehow to get it knocked out?

 Incidentally, this would probably have been done quite weeks ago if people 
 had not objected to my doing any more on the feature. Of course missing the 
 GIN/GIST ops was not part of the design. Quite the contrary.

That was my understanding, as well.

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] jsonb and nested hstore

[David E . Wheeler]

On Feb 27, 2014, at 3:54 AM, Robert Haas robertmh...@gmail.com wrote:

 It's not very clear to me why we think it's a good idea to share the
 tree-ish representation between json and hstore.  In deference to your
 comments that this has been very publicly discussed over quite a
 considerable period, I went back and tried to find the email in which
 the drivers for that design decision were laid out.  I can find no
 such email; in fact, the first actual nested hstore patch I can find
 is from January 13th and the first jsonb patch I can find is from
 February 9th.  Neither contains anything much more than the patch
 itself, without anything at all describing the design, let alone
 explaining why it was chosen.  And although there are earlier mentions
 of both nested hstore and jsonb, there's nothing that says, OK, this
 is why we're doing it that way.  Or if there is, I couldn't find it.

FWIW, It was discussed quite a bit in meatspace, at the PGCon unconference last 
spring.

 Unless I've missed some emails sent earlier than the dates noted
 above, which is possible, the comments by myself and others on this
 thread ought to be regarded as timely review.  The basic problem here
 is that this patch wasn't timely submitted, still doesn't seem to be
 very done, and it's getting rather late.

The hstore patch landed in the Nov/Dec patch fest, sent to the list on Nov 12. 
The discussion that led to the decision to implement jsonb was carried out for 
the week after that. Here’s the thread:

  http://www.postgresql.org/message-id/528274f3.3060...@sigaev.ru

There was also quite a bit of discussion that week in the “additional json 
functionality” thread.

  http://www.postgresql.org/message-id/528274d0.7070...@dunslane.net

I submitted a review of hstore2, adding documentation, on Dec 20. Andrew got 
the patch updated with jsonb type, per discussion, and based on a first cut by 
Teodor, in January, I forget when. v7 was sent to the list on Jan 29. So while 
some stuff has been added a bit late, it was based on discussion and the 
example of hstore's code.

I think you might have missed quite a bit of the earlier discussion because it 
was in an hstore thread, not a JSON or JSONB thread.

 We therefore face the usual
 problem of deciding whether to commit something that we might regret
 later.  If jsonb turns out to the wrong solution to the json problem,
 will there be community support for adding a jsonc type next year? I
 bet not.  

Bit of a red herring, that. You could make that argument about just about *any* 
data type. I realize it's more loaded for object data types, but personally I 
have a hard time imagining something other than a text-based type or a binary 
type. There was disagreement as to whether the binary type should replace the 
text type, and the consensus of the discussion was to have both. (And then we 
had 10,000 messages bike-sheadding the name of the binary type, naturally.)

 You may think this is most definitely the right direction to
 go and you may even be right, but our ability to maneuver and back out
 of things goes down to nearly zero once a release goes out the door,
 so I think it's entirely appropriate to question whether we're
 charting the best possible course.  But I certainly understand the
 annoyance.

Like the hstore type, the jsonb type has a version bit, so if we decide to 
change its representation to make it more efficient in the future, we will be 
able to do so without having to introduce a new type. Maybe someday we will 
want a completely different JSON implementation based on genetic mappings or 
quantum superpositions or something, but I would not hold up the ability to 
improve the speed of accessing values, let alone full path indexing via GIN 
indexing, because we might want to do something different in the future. 
Besides, hstore has proved itself pretty well over time, so I think it’s pretty 
safe to adopt its implementation to make an awesome jsonb 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] extension_control_path

[David E. Wheeler]

On Feb 6, 2014, at 6:51 AM, Greg Stark st...@mit.edu wrote:

 Homebrew sounds kind of confused. Having a non-root user have access
 to make global system changes sounds like privilege escalation
 vulnerability by design.

Well, the point is that it *doesn’t* make global system changes. I got an error 
on OS X Server with my original formula, because there was no permission to 
install in $PGROOT/share/extensions.

 However putting that aside, it is fairly standard for software to
 provide two directories for extensions/modules/plugins/etc. One for
 distribution-built software such as /usr/share/emacs/site-lisp/ and
 another for sysadmin customizations such as
 /usr/local/share/emacs/site-lisp. The same idea as /usr/share/perl and
 /usr/local/share/perl or with Python or anything else.

Right. And you can also add additional paths for those applications to search.

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] extension_control_path

[David E. Wheeler]

On Feb 6, 2014, at 7:32 AM, Stephen Frost sfr...@snowman.net wrote:

 The end-admin would have to modify the system-installed postgresql.conf
 anyway to enable this other directory.  David wasn't suggesting that
 Homebrew *should* be able to do so, he was pointing out that it *can't*,
 which all makes sense imv.

Yeah, or be able to add a directory as a Postgres super user at runtime.

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] jsonb and nested hstore

[David E. Wheeler]

On Feb 5, 2014, at 3:59 PM, Andrew Dunstan and...@dunslane.net wrote:

 I got a slightly earlier start ;-) For people wanting to play along, here's 
 what this change looks like: 
 https://github.com/feodor/postgres/commit/3fe899b3d7e8f806b14878da4a4e2331b0eb58e8

Man I love seeing all that read. :-)

D



-- 
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] extension_control_path

[David E. Wheeler]

On Feb 6, 2014, at 9:14 AM, Greg Stark st...@mit.edu wrote:

 Installing into /usr/local is a global system change. Only root should
 be able to do that and any user that can do that can easily acquire
 root privileges.

I agree with you, but I don’t think the Homebrew folks do. Or at least their 
current implementation doesn’t. OT though.

 Well, users can do whatever they want at run-time but there are
 blessed paths that are the correct place to install things that these
 systems are configured to search automatically. My point was just that
 there are generally two such blessed paths, one for the distribution
 and one for the local sysadmin.

Yeah, two blessed would be very useful, but I think the ability to add any 
number of paths would be even better.

 What you do not want is to have a different path for each piece of
 software. That way lies the
 /usr/local/kde/bin:/usr/local/gnome/bin:/usr/local/myfavouritehack/bin:...
 madness. You can do this with Python or Perl but they won't do it
 automatically and everyone who does this with environment variables or
 command line flags eventually realizes what a mess it is. (Except Java
 programmers)

Agreed.

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] extension_control_path

[David E. Wheeler]

On Jan 30, 2014, at 10:06 AM, Sergey Muraviov sergey.k.murav...@gmail.com 
wrote:

 Now it looks fine for me.

Just as another data point, I recently submitted pgTAP to the Homebrew project 
This is the build-from-source system for OS X, used by a lot of web developers. 
In my build script, I originally had

   depends_on :postgresql

Which means, “require any version of PostgreSQL.” But then tests failed on OS X 
Server, which includes a system-distributed PostgreSQL. Homebrew installs 
everything in /usr/local, and not only does it disallow installing anything 
outside of that directory, it doesn’t have any permissions to do so. The 
install failed, of course, because extensions want to install in 
$PGROOT/share/extensions. For now, I had to change it to

   depends_on 'postgresql'

A subtle difference that means, “require the latest version of the 
Homebrew-built PostgreSQL in /usr/local.”

However, if extension_control_path was supported, I could change it back to 
requiring any Postgres and install pgTAP somewhere under /usr/local, as 
required for Homebrew. Then all the user would have to do to use it with their 
preferred Postgres would be to set extension_control_path.

In other words, I am strongly in favor of this patch, as it gives distribution 
systems a lot more flexibility (for better and for worse) in determining where 
extensions should be installed.

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] nested hstore patch

[David E. Wheeler]

On Jan 11, 2014, at 1:47 PM, Andrew Dunstan and...@dunslane.net wrote:

 It's been committed at 
 https://github.com/feodor/postgres/commit/a21a4be55a5b12c4bd89b6ab2f77cf32e319de31.
  It will be in the next version of the patch posted.

Bah! Sorry about that. Habit from decades of typing HTML.

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] nested hstore patch

[David E. Wheeler]

On Nov 12, 2013, at 10:35 AM, Teodor Sigaev teo...@sigaev.ru wrote:

 Hi!
 
 Attatched patch adds nesting feature, types (string, boll and numeric 
 values), arrays and scalar to hstore type.

My apologies for not getting to this sooner, work has been a bit nutty. The 
truth is that I reviewed this patch quite a bit a month back, mostly so I could 
write documentation, the results of which are included in this patch. And I'm 
super excited for what's to come in the next iteration, as I hear that Teodor 
and Andrew are hard at work adding jsonb as a binary-compatible JSON data type.

Meanwhile, for this version, a quick overview of what has changed since 9.2.

Contents  Purpose
==

Improved Data Type Support
--

* Added data type support for values. Previously they could only be strings or 
NULL, but with this patch they can also be numbers or booleans.

* Added array support. Values can be arrays of other values. The format for 
arrays is a bracketed, comma-delimited list.

* Added nesting support. hstore values can themselves be hstores. Nested 
hstores are wrapped in braces, but the root-level hstore is not (for 
compatibility with the format of previous versions of hstore).

* An hstore value is no longer required to be an hstore object. It can now be 
any scalar value.

These three items make the basic format feature-complete with JSON. Here's an 
example where the values are scalars:

=% SELECT 'foo'::hstore, 'hi \bob\'::hstore, '1.0'::hstore, 
'true'::hstore, NULL::hstore;
 hstore |hstore| hstore | hstore | hstore 
+--+++
 foo  | hi \bob\ | 1.0| t  | 

And here are a couple of arrays with strings, numbers, booleans, and NULLs:

SELECT '[k,v]'::hstore, '[1.0, hi there, false, null]'::hstore;
   hstore   |   hstore   
+
 [k, v] | [1.0, hi there, f, NULL]

Here's a complicated example formatted with `hstore.pretty_print` enabled.

=% SET hstore.pretty_print=true;
=% SELECT '{
  type = Feature,
  bbox = [-180.0, -90.0, 180.0, 90.0],
  geometry = {
type = Polygon,
coordinates = [[
  [-180.0, 10.0], [20.0, 90.0], [180.0, -5.0], [-30.0, -90.0]
  ]]
}
}'::hstore;
  hstore  
--
 bbox=+
 [   +
 -180.0, +
 -90.0,  +
 180.0,  +
 90.0+
 ],  +
 type=Feature,  +
 geometry=+
 {   +
 type=Polygon,  +
 coordinates= +
 [   +
 [   +
 [   +
 -180.0, +
 10.0+
 ],  +
 [   +
 20.0,   +
 90.0+
 ],  +
 [   +
 180.0,  +
 -5.0+
 ],  +
 [   +
 -30.0,  +
 -90.0   +
 ]   +
 ]   +
 ]   +
 }

So, exact feature parity with the JSON data type.

* hstore.pretty_print is a new GUC, specifically to allow an HSTORE value to be 
pretty-printed. There is also a function to pretty-print, so we might be able 
to just do away with the GUC.

Interface
-

* New operators:
  + `hstore - int`: Get string value at array index (starting at 0)
  + `hstore ^ text`:Get numeric value for key
  + `hstore ^ int`: Get numeric value at array index
  + `hstore ? text`:Get boolean value for key
  + `hstore ? int`: Get boolean value at array index
  + `hstore # text[]`:  Get string value for key path
  + `hstore #^ text[]`: Get numeric value for key path
  + `hstore #? text[]`: Get boolean value for key path
  + `hstore % text`:Get hstore value for key
  + `hstore % int`: Get hstore value at array index
  + `hstore #% text[]`: Get hstore value for key path
  + `hstore ? int`:  Does hstore contain array index
  + `hstore #? text[]`:  Does hstore contain key path
  + `hstore - int`:  Delete index from left operand
  + `hstore #- text[]`:  Delete key path from left operand

* New functions:
  + `hstore(text)`: Make a text scalar hstore
  + `hstore(numeric)`:  Make a numeric scalar hstore
  + `hstore(boolean)`:  Make a boolean scalar hstore
  + `hstore(text, hstore)`: Make a nested hstore
  + `hstore(text, numeric)`:Make an hstore with a 

Re: [HACKERS] Proposal: variant of regclass

[David E. Wheeler]

On Dec 5, 2013, at 7:52 AM, Tom Lane t...@sss.pgh.pa.us wrote:

 BTW, another arguable advantage of fixing this via new functions is
 that users could write equivalent (though no doubt slower) functions
 for use in pre-9.4 releases, and thus not need to maintain multiple
 versions of app code that relies on this behavior.

+1 to this idea. Feels cleanest.

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] Extension Templates S03E11

[David E. Wheeler]

On Dec 3, 2013, at 9:14 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:

 I understand that it can happen, it still really sucks when it does.
 
  delusionnal paragraph, censored for lack of humour (incl. sarcasm)

I have not followed this project closely, Dimitri, but I for one have 
appreciated your tenacity in following through on it. Extensions are awesome, 
thanks to you, and I’m happy to see all efforts to make it more so.

Thank you.

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] Extension Templates S03E11

[David E. Wheeler]

On Dec 2, 2013, at 6:14 AM, Dimitri Fontaine dimi...@2ndquadrant.fr wrote:

 Whether you're targetting a file system template or a catalog template,
 PGXN is not a complete solution, you still need to build the extension.

This is true today, but only because PostgreSQL provides the infrastructure for 
building and installing extensions that entails `make  make install`. If 
Postgres provided some other method of building and installing extensions, you 
could start using it right away on PGXN. The *only* requirement for PGXN 
distributions, really, is a META.json file describing the extension.

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] Toward a Database URI Standard

[David E. Wheeler]

Hackers,

I've been toying with the idea of a standard for database URIs, mostly inspired 
by the libpq and JDBC formats Here's a writeup:

  http://theory.so/rfc/2013/11/26/toward-a-database-uri-standard/

What do you think?

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] nested hstore patch

[David E. Wheeler]

On Nov 20, 2013, at 6:19 AM, Peter Eisentraut pete...@gmx.net wrote:

 openjade:hstore.sgml:206:16:E: document type does not allow element 
 VARLISTENTRY here; assuming missing VARIABLELIST start-tag

Thanks, I fixed this one.

David

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