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

[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

[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  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  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] make check For Extensions

[David E. Wheeler]

On Jun 12, 2014, at 11:28 AM, Fabien COELHO  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

Re: [HACKERS] make check For Extensions

[David E. Wheeler]

On Jun 12, 2014, at 11:40 PM, Fabien COELHO  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 15, 2014, at 12:25 AM, Fabien COELHO  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] Why is it "JSQuery"?

[David E. Wheeler]

On Jun 15, 2014, at 1:58 PM, Josh Berkus  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

[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

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

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

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

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

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

[David E. Wheeler]

On Jan 30, 2014, at 10:06 AM, Sergey Muraviov  
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] extension_control_path

[David E. Wheeler]

On Feb 6, 2014, at 6:51 AM, Greg Stark  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  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  wrote:

> I got a slightly earlier start ;-) For people wanting to play along, here's 
> what this change looks like: 
> 

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

[David E . Wheeler]

On Feb 27, 2014, at 3:54 AM, Robert Haas  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] jsonb and nested hstore

[David E. Wheeler]

On Mar 5, 2014, at 8:49 AM, Andrew Dunstan  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 Mar 6, 2014, at 1:51 AM, Peter Geoghegan  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

[HACKERS] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

Hackers,

This seems reasonable:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# SELECT * from now;
david$# END;
david$# $$;
ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function inline_code_block line 3 at SQL statement

This not so much:

david=# DO $$
david$# BEGIN
david$# WITH now AS (SELECT now())
david$# PERFORM * from now;
david$# END;
david$# $$;
ERROR:  syntax error at or near "PERFORM"
LINE 4: PERFORM * from now;
^
Parser bug in PL/pgSQL, perhaps?

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

Hi Pavel,

On Aug 20, 2013, at 2:11 PM, Pavel Stehule  wrote:

>> david=# DO $$
>> david$# BEGIN
>> david$# WITH now AS (SELECT now())
>> david$# PERFORM * from now;
>> david$# END;
>> david$# $$;
>> ERROR:  syntax error at or near "PERFORM"
>> LINE 4: PERFORM * from now;
>> ^
>> Parser bug in PL/pgSQL, perhaps?
> 
> no
> 
> you cannot use a PL/pgSQL statement inside SQL statement.

Well, there ought to be *some* way to tell PL/pgSQL to discard the result. 
Right now I am adding a variable to select into but never otherwise use. 
Inelegant, IMHO. Perhaps I’m missing some other way to do it?

If so, it would help if the hint suggesting the use of PERFORM pointed to such 
alternatives.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 2:24 PM, Marko Tiikkaja  wrote:

>> postgres=# DO $$
>>  BEGIN
>>PERFORM * FROM (WITH now AS (SELECT now())
>>   SELECT * from now) x;
>>  END;
>> $$;
>> DO
> 
> .. which doesn't work if you want to use table-modifying CTEs.

Which, in fact, is exactly my use case (though not what I posted upthread).

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 2:31 PM, Pavel Stehule  wrote:

> but it works
> 
> postgres=# do $$begin with x as (select 10) insert into omega select * from 
> x; end;$$;
> DO

But this does not:

david=# DO $$
david$# BEGIN
david$# PERFORM * FROM (
david$# WITH inserted AS (
david$# INSERT INTO foo values (1) RETURNING id
david$# ) SELECT inserted.id
david$# ) x;
david$# END;
david$# $$;
ERROR:  WITH clause containing a data-modifying statement must be at the top 
level
LINE 2: WITH inserted AS (
 ^
QUERY:  SELECT * FROM (
WITH inserted AS (
INSERT INTO foo values (1) RETURNING id
) SELECT inserted.id
) x
CONTEXT:  PL/pgSQL function inline_code_block line 3 at PERFORM

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 2:41 PM, Pavel Stehule  wrote:

> yes, in this context you should not use a PERFORM
> 
> PL/pgSQL protect you before useless queries - so you can use a CTE without 
> returned result directly or CTE with result via PERFORM statement (and in 
> this case it must be unmodifing CTE).
> 
> Sorry, I don't see any problem - why you return some from CTE and then you 
> throw this result?

I am passing the values returned from a CTE to a call to pg_notify(). I do not 
care to collect the output of pg_notify(), which returns VOID.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 2:44 PM, Pavel Stehule  wrote:

> I think the way forward is to remove the restriction such that data
> returning queries must be PERFORM'd
> 
> I disagree, current rule has sense.

Perhaps a DECLARE FUNCTION attribute that turns off the functionality, 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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 2:53 PM, Pavel Stehule  wrote:

>> I am passing the values returned from a CTE to a call to pg_notify(). I do 
>> not care to collect the output of pg_notify(), which returns VOID.
> 
> it is little bit different issue - PL/pgSQL doesn't check if returned type is 
> VOID - it can be allowed, I am thinking. So check of empty result can be 
> enhanced.

I am confused. I do not need to check the result (except via FOUND). But I am 
sure I can think of other situations where I am calling something where I do 
not care about the result, even if it returns one.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 3:05 PM, Pavel Stehule  wrote:

> When you would to ignore result, then you should to use a PERFORM - actually, 
> it is limited now and should be fixed. Have no problem with it.

Glad to have you on board. :-)

> I don't would to enable a free unbound statement that returns result. 

I have no pony in that race. I think it is useful, though I prefer to unit test 
things enough that I would be fine without it.

But even without it, there may be times when I want to discard a result in a 
function that *does* return a value -- likely a different value. So there needs 
to be a way to distinguish statements that should return a value and those that 
do not.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 3:18 PM, Pavel Stehule  wrote:

> can you show some examples, please

This is not dissimilar to what I am actually doing:

CREATE TABLE foo (id SERIAL PRIMARY KEY, name TEXT);

CREATE OR REPLACE FUNCTION shipit (
VARIADIC things TEXT[]
) RETURNS BOOL LANGUAGE plpgsql AS $$
BEGIN
WITH inserted AS (
INSERT INTO foo (name)
SELECT * FROM unnest(things)
RETURNING id
)
PERFORM pg_notify(
'inserted ids',
ARRAY(SELECT * FROM inserted)::text
);
RETURN FOUND;
END;
$$;

Only I am using a dummy row variable instead of PERFORM, of course.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 20, 2013, at 3:38 PM, Pavel Stehule  wrote:

> pg_notify returns void, so there are no necessary casting to void
> 
> so enhanced check - so all returned columns are void should be enough

What if I call another function I wrote myself that returns an INT, but I do 
not care about the INT? Maybe that function does the insert and returns the 
number of inserted rows.

I can think of all kinds of reasons this might be the case; whether they are 
good or bad approaches is immaterial: sometimes you work with what you have.

I am find with PERFORM to determine when a query's results should be discarded. 
I just think it needs to cover a few more cases.

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] CAST Within EXCLUSION constraint

[David E. Wheeler]

Hackers,

I am trying to do something like this:

CREATE TYPE source AS ENUM(
'fred', 'wilma', 'barney', 'betty'
);

CREATE EXTENSION btree_gist;

CREATE TABLE things (
source source NOT NULL,
within tstzrange NOT NULL,
EXCLUDE USING gist (source WITH =, within WITH &&)
);

Alas, enums are not supported by btree_gist:

try.sql:13: ERROR:  data type source has no default operator class for 
access method "gist"
HINT:  You must specify an operator class for the index or define a default 
operator class for the data type.

Well, maybe I can cast it? But no, changing the EXCLUDE line to

EXCLUDE USING gist (source::text WITH =, within WITH &&)

Yields a syntax error:

try.sql:13: ERROR:  syntax error at or near "::"
LINE 4: EXCLUDE USING gist (source::text WITH =, within WITH &&)

So that's out. Why shouldn't :: be allowed?

No problem, I can use CAST(), right? So I try:

EXCLUDE USING gist (CAST(source AS text) WITH =, within WITH &&)

Not so much:

try.sql:13: ERROR:  functions in index expression must be marked IMMUTABLE

I guess it's because locale settings might change, and therefore change the 
text representation? Seems unlikely, though.

I guess I can create my own IMMUTABLE function over the ENUM:

CREATE FUNCTION source_to_text(
source
) RETURNS TEXT LANGUAGE sql STRICT IMMUTABLE AS $$
SELECT $1::text;
$$;

So this works:

EXCLUDE USING gist (source_to_text(source) WITH =, within WITH &&)

So I guess that’s good enough for now. But should :: really be a syntax error 
in index expressions?

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] CAST Within EXCLUSION constraint

[David E. Wheeler]

On Aug 20, 2013, at 6:50 PM, Tom Lane  wrote:

> You need more parentheses -- (source::text) would've worked.

Alas, no, same problem as for CAST():

  ERROR:  functions in index expression must be marked IMMUTABLE

>> No problem, I can use CAST(), right? So I try:
>>EXCLUDE USING gist (CAST(source AS text) WITH =, within WITH &&)
>> Not so much:
>>try.sql:13: ERROR:  functions in index expression must be marked IMMUTABLE
>> I guess it's because locale settings might change, and therefore change the 
>> text representation? Seems unlikely, though.
> 
> Not locale, just renaming one of the values would be enough to break that.
> Admittedly we don't provide an official way to do that ATM, but you can do
> an UPDATE on pg_enum.

Ah, right. Maybe if there was a way to get at some immutable numeric value…

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] CAST Within EXCLUSION constraint

[David E. Wheeler]

On Aug 21, 2013, at 4:13 PM, Tom Lane  wrote:

>> test=# create cast (source as oid) without function;
>> ERROR:  enum data types are not binary-compatible
> 
> The reason for that is you'd get randomly different results on another
> installation.  In this particular application, I think David doesn't
> really care about what values he gets as long as they're distinct,
> so this might be an OK workaround for him.  But that's the reasoning
> for the general prohibition.

I’m okay with my function that casts to text, at least for now. An integer 
would be nicer, likely smaller for my index, but not a big deal.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 23, 2013, at 8:51 PM, Pavel Stehule  wrote:

> it is about a personal taste - if you prefer more verbose or less verbose 
> languages. 
> 
> I feeling a PERFORM usage as something special and you example is nice case, 
> where I am think so PERFORM is good for verbosity.

I really do not see the point of PERFORM in the current implementation of 
PL/pgSQL. If we were to allow SELECT to run when it is not returning a value or 
selecting into a variable, it would be unambiguous, since the other two cases 
require:

* Using RETURN (or RETURN QUERY)
* The INTO clause

I have come around to the position that I think Tom, Josh, and Merlin have all 
put forward, that PERFORM is unnecessary.

Unless Jan chimes in with something the rest of us have missed, it’s starting 
to feel like a consensus to me, other than your objections, of course.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 27, 2013, at 12:30 AM, Pavel Stehule  wrote:

> I disagree - Tom K. speaking about what he likes or dislikes (and about what 
> he didn't use) He forgot about strong points of implicit result or 
> interesting points. Clients usually has no problem with dynamic datasets - 
> PHP, DBI, Llibpq, GUI components .. all libs support a generic access and 
> this generic access is often used due less dependency on queries.
> 
> There are a three interesting possibilities of implicit result sets:
> 
> * Possibility to return dynamic dataset - when you don't know a result before 
> execution - typical use case is a some form of pivot tables or some analytics 
> queries.
> 
> * Possibility to return multiple results as flattening of some 
> multidimensional data.
> 
> * Possibilty to write multiresults reports for one call execution.

As a dynamic language programmer, I can see this, as long as it’s not to the 
exclusion of strong typing interfaces, as well.

However, I do not think it should be implicit. If a function or procedure wants 
to return values or query results or whatever to the caller, it should 
explicitly do so by using some key word. We already have RETURN, RETURN NEXT, 
RETURN QUERY, and RETURN EXECUTE, which is great for functions. For 
hypothetical functions or procedures that want to return data as it processes, 
rather than buffering the results and returning them all at once, perhaps we 
could add YIELD, YEILD QUERY, and YIELD EXECUTE. In fact, this is pretty much 
exactly what the key word YIELD is for in coroutines:

  https://en.wikipedia.org/wiki/Coroutine

But whatever the keyword, I think it makes sense to require one to return 
results to the caller. Any query that does not return, yield, or capture 
(select into) values should just have its results discarded.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 27, 2013, at 1:36 PM, Pavel Stehule  wrote:

> I agree with David that we should use some new syntax to specify
> return-results-directly-to-client, assuming we ever get any such
> functionality.  It seems like a pretty bad choice of default behavior,
> which is essentially what you're saying it should be.
> 
> this functionality should be disabled in functions. This can be allowed only 
> for procedures started by CALL statements. I don't propose it for functions. 

That does not make it a bad idea. Let me summarize:

I propose to remove the requirement to use PERFORM to execute queries for which 
the result should be discarded. It should instead be implicit that results are 
discarded unless you capture them or return them.

You propose to continue requiring PERFORM to execute queries for which the 
result should be discarded. This is so that, in the future, SQL statements can 
implicitly return to the caller.

That sound about right to you?

I *really* dislike the idea that some SQL execution implicitly returns from a 
PL/pgSQL function or procedure. That just seems too magical. I strongly prefer 
that the scope of the code executed in a function or procedure be limited to 
the scope of the function or procedure itself, and only return data to the 
caller if I explicitly tell it to. Much less magical, 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

Re: [HACKERS] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 27, 2013, at 3:10 PM, Pavel Stehule  wrote:

> CREATE PROCEDURE foo()
> BEGIN
>   SELECT 1,2;
>   SELECT 2;
>   SELECT 3,4
> END;
> 
> And is not strange expect a result
> 
> CALL foo()
> 
> 1,2
> 2
> 3,4
> 
> Procedure is a script (batch) moved to server side for better performance and 
> better reuse. 

I am not familiar with procedures, being a long time Postgres guy, but you’re 
right that it never occurred to me that they be thought of as batch files.

Still, this is PL/pgSQL we’re talking about, not TSQL or SQL/PSM anything else. 
Perhaps your syntax suggestions make sense there, in which case, when you 
develop such functionality to Postgres, you would need to figure out how to get 
PERFORM to work with CTEs. But PL/pgSQL requires an explicit key word to return 
data, and I am hard pressed to see why that would change when it is used in 
procedures. And that makes PERFORM unnecessary, IME.

> You should not thinking about procedures like void functions, because it is a 
> little bit different creature - and void functions is significantly limited 
> in functionality.
> 
> My proposal is consistent - no result goes to /dev/null without special mark. 
> It is disabled (in function) or it goes to client (in procedures).

Consistent, yes. But I’m not convinced -- and I’m *certainly* not convinced 
that PERFORM should be required to discard query results in PL/pgSQL 
*functions*, which is the issue on the table now.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 29, 2013, at 1:11 PM, Pavel Stehule  wrote:

> I cannot to say what is good design for PL/pgSQL - only I feel so some 
> variant of RETURN statement is not good, because semantic is significantly 
> different. And I see a increasing inconsistency between a original ADA and 
> PL/pgSQL. 

So YIELD or implement PL/PSM.

> Sure, When I am thinking about PSM, I am thinking about T-SQL syntax, but 
> there is little bit simpler situation - there is a precedent in PSM 
> implementation in MySQL and some other new databases. 

PL/pgSQL is not PSM.

> so main problem is a impossibility to write
> 
> BEGIN
>CALL fce()
> 
> or
> 
> BEGIN
>   fce();
> 
> A workaround in Postgres is PERFORM - and I really has nothing again to 
> remove PERFORM for start of VOID functions!

No reason SELECT could not work just a well.

> A unhelpful error message has zero relevant to topic - just almost all in 
> PL/pgSQL is SELECT.

Well, it was an aside, but points out another problem with PERFORM: It doesn't 
really exist. I gets replaced with SELECT internally, leading to confusing 
error messages. Solution: Allow SELECT instead of PERFORM.

> Do you would to remove a ":=" statement too?
> 
> postgres=# do $$declare x int; begin x := notexisting(10); end; $$ ;
> ERROR:  function notexisting(integer) does not exist
> LINE 1: SELECT notexisting(10)
>^
> HINT:  No function matches the given name and argument types. You might need 
> to add explicit type casts.
> QUERY:  SELECT notexisting(10)
> CONTEXT:  PL/pgSQL function inline_code_block line 1 at assignment

I agree it would be nice if it didn't report SELECT there, but at least it's 
not *removing* anything from what you see in the source.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 29, 2013, at 2:22 PM, Pavel Stehule  wrote:

> Still I don't think so correct solution is enabling a unbound SELECTs, but 
> correct is a fix a PERFORM and remove a necessity to use a PERFORM for call 
> of VOID functions.

Well, in this thread, I believe you are the only person who feels that way. And 
this proposal still would not let PERFORM work with CTEs.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 29, 2013, at 2:41 PM, Pavel Stehule  wrote:

> I am thinking, so I propose a enough solution for you - when you use CTE for 
> execution of VOID function, then result vill be VOID set, what we can accept 
> as undefined result, and in this case a PERFORM should not be required. If 
> CTE will return some result, then PERFORM should be required and PERFORM must 
> to support CTE in all possible modes - updateable or not updateable queries.

If you can make PERFORM work with CTEs, that would be an improvement over the 
status quo. But I think there is no good reason not to let SELECT results be 
discarded, either. I know you think there are good reasons, but no one else in 
this thread is convince, AFAICT.

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] PL/pgSQL PERFORM with CTE

[David E. Wheeler]

On Aug 29, 2013, at 2:48 PM, Andres Freund  wrote:

>> You have yet to supply any arguments which support this position.
> 
> I am not convinced that's enough of a reason, but the requirement to use
> PERFORM for SELECTs that aren't stored anywhere actually has prevented
> bugs for me. I am not convinced that's worth the cost since I also have
> been annoyed by it several times, but it's not as crystal clear as you
> paint it.

So now we can revise Josh’s assertion to: “I have seen only tepid, unconvincing 
arguments which support this position.”

I have thought that PERFORM was useful to mark queries that discard results in 
the past, but I think now that the mental load is higher, even if it can be 
fixed with CTEs, it’s more trouble than it’s worth.

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] No Index-Only Scan on Partial Index

[David E. Wheeler]

Hackers,

I was trying to figure out why a query was not doing an index-only scan on a 
partial index, when Josh Berkus pointed to this issue, reported by Merlin 
Moncure:

  
http://www.postgresql.org/message-id/CAHyXU0x1OGao48WajAfUsbXqkUDLf=_6ho6hlmb8dsfkwda...@mail.gmail.com

In short, the planner needs the column from the where clause included in the 
index to decide it can do an index-only scan. This test case demonstrates the 
truth of this finding:

CREATE TABLE try (
id INT NOT NULL,
label  TEXT NOT NULL,
active BOOLEAN DEFAULT TRUE
);

INSERT INTO try
SELECT i
 ,  (ARRAY['foo','bar','baz','wig'])[floor((random()*4))::int + 1]
 , (i % 100) = 0
  FROM generate_series(1, 10) i;

VACUUM FREEZE TRY;

CREATE INDEX idx_try_active ON try(id) WHERE active;

-- Does a bitmap heap scan.
EXPLAIN (ANALYZE, FORMAT YAML)
SELECT id FROM try WHERE active;

DROP INDEX idx_try_active;
CREATE INDEX idx_try_active ON try(label, id, active) WHERE active;

-- Does an index-only scan.
EXPLAIN (ANALYZE, FORMAT YAML)
SELECT id FROM try WHERE active;

DROP TABLE try;

The first query does a bitmap heap scan, but after the index that includes the 
active column is added, it does an index-only scan.

However, this does not quite match my case. In my case, I'm using an immutable 
function call in the index where clause:

CREATE INDEX idx_try_active ON try(id, upper_inf(irange)) WHERE 
upper_inf(irange);

I am unable to get the planner do to an index-only scan with this index no 
matter what I do. Here’s the full test case:

CREATE TABLE try (
id INT   NOT NULL,
label  TEXT  NOT NULL,
irange INT4RANGE NOT NULL
);

INSERT INTO try
SELECT i
 ,  (ARRAY['foo','bar','baz','wig'])[floor((random()*4))::int + 1]
 , int4range(1, CASE WHEN random() < 0.01 THEN NULL ELSE 2 END)
  FROM generate_series(1, 10) i;

VACUUM FREEZE TRY;

CREATE INDEX idx_try_active ON try(id) WHERE upper_inf(irange);

-- Does a bitmap heap scan.
EXPLAIN (ANALYZE, FORMAT YAML)
SELECT id FROM try WHERE upper_inf(irange);

DROP INDEX idx_try_active;
CREATE INDEX idx_try_active ON try(label, id, upper_inf(irange)) WHERE 
upper_inf(irange);

-- Also does a bitmap heap scan.
EXPLAIN (ANALYZE, FORMAT YAML)
SELECT id FROM try WHERE upper_inf(irange);

DROP TABLE try;

So is there something about using a function in a conditional index that 
prevents index-only scans? Tested on 9.2 and 9.3, BTW.

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] No Index-Only Scan on Partial Index

[David E. Wheeler]

On Oct 1, 2013, at 3:56 PM, Merlin Moncure  wrote:

> I don't think it has anything to do with the conditional index -- it's
> the functional based.  For some reason postgres always wants to post
> filter (note the filter step below):
> 
> postgres=# create index on try(upper_inf(irange));
> CREATE INDEX
> Time: 12.001 ms
> postgres=# explain select * from try where upper_inf(irange);
>  QUERY PLAN
> ---
> Index Scan using try_upper_inf_idx on try  (cost=0.00..9.25 rows=33 width=40)
>   Index Cond: (upper_inf(irange) = true)
>   Filter: upper_inf(irange)

Hrm. I get a seq scan for that query:

create index on try(upper_inf(irange));
explain select * from try where upper_inf(irange);
QUERY PLAN 
---
 Seq Scan on try  (cost=0.00..1887.00 rows=3 width=68)
   Filter: upper_inf(irange)

True also if I just select the irange. Is the filter the issue, here?

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] No Index-Only Scan on Partial Index

[David E. Wheeler]

On Oct 2, 2013, at 5:07 AM, Merlin Moncure  wrote:

> > Hrm. I get a seq scan for that query:
> >
> > create index on try(upper_inf(irange));
> > explain select * from try where upper_inf(irange);
> > QUERY PLAN
> > ---
> >  Seq Scan on try  (cost=0.00..1887.00 rows=3 width=68)
> >Filter: upper_inf(irange)
> >
> > True also if I just select the irange. Is the filter the issue, here?
> 
> Turn off seq scan...

That rewards me with a bitmap heap scan:

EXPLAIN select * from try where upper_inf(irange);

  QUERY PLAN
  
--
 Bitmap Heap Scan on try  (cost=935.63..2197.63 rows=3 width=68)
   Filter: upper_inf(irange)
   ->  Bitmap Index Scan on try_upper_inf_idx  (cost=0.00..927.30 rows=5 
width=0)
 Index Cond: (upper_inf(irange) = true)

But anyway, I still don’t understand why, if the function used to store the 
value is immutable (as upper_inf() is), why Postgres doesn't do an index scan. 
Is this something that could be improved in the planner?

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] No Index-Only Scan on Partial Index

[David E. Wheeler]

On Oct 3, 2013, at 10:50 AM, Josh Berkus  wrote:

>> 
>> But anyway, I still don’t understand why, if the function used to store the 
>> value is immutable (as upper_inf() is), why Postgres doesn't do an index 
>> scan. Is this something that could be improved in the planner?
> 
> Yes.  This is clearly a TODO.

Added it here:

  https://wiki.postgresql.org/wiki/Todo#Optimizer_.2F_Executor

Teach the planner how to better use partial indexes for index-only scans
• http://www.postgresql.org/message-id/25141.1345072...@sss.pgh.pa.us
• 
http://www.postgresql.org/message-id/79c7d74d-59b0-4d97-a5e5-3ef29...@justatheory.com

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

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] [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] 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] 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] WIP patch for parameterized inner paths

[David E. Wheeler]

On Jan 25, 2012, at 10:24 AM, Tom Lane wrote:

> Anyway, I'd be willing to hold off committing if someone were to
> volunteer to test an unintegrated copy of the patch against some
> moderately complicated application.  But it's a sufficiently large
> patch that I don't really care to sit on it and try to maintain it
> outside the tree for a long time.

Why not create a branch? IIRC the build farm can be configured to run branches.

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 patch for parameterized inner paths

[David E. Wheeler]

On Jan 25, 2012, at 12:19 PM, Tom Lane wrote:

>> Why not create a branch? IIRC the build farm can be configured to run 
>> branches.
> 
> I already know what the patch does against the regression tests.
> Buildfarm testing is not of interest here.  What would be of help is,
> say, Kevin volunteering to load up his Circuit Courts software and data
> into a git-head server and see how performance looks with and without
> the patch.  Distribution of the code doesn't strike me as being the
> bottleneck.

Yeah, but it would be easier to keep a branch up-to-date via `git rebase` than 
to maintain the patch, I would think. And if it’s a remote branch, then simpler 
distribution is a bonus.

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] Inline Extension

[David E. Wheeler]

On Jan 26, 2012, at 9:40 AM, Dimitri Fontaine wrote:

> So I'm going to prepare the next version of the patch with this design:
> 
> - in catalog extension scripts for inline extension
> 
>   pg_extension_script(extoid, oldversion, version, script)
> 
>   oldversion is null when create extension is used
>   unless when using the create extension from 'unpackaged' form

Would you keep all the migration scripts used over time to upgrade from one 
version to another?

> - see about adding more control properties in the catalog?
> 
> - current code that is parsing the filenames to determine the upgrade
>   path will have to be able to take the version strings from the new
>   catalog as an alternative, and getting to the script content must be
>   able to select from the catalog or read a file on disk
> 
> - pg_dump defaults to not dumping extension content
> 
> - pg_dump --include-extension-scripts dumps the scripts found either in
>   the filesystem or the catalog, a create script first then any number
>   of update script as needed to reach the current installed version
> 
> - same as we have -t, add -e --extension to pg_dump so that you can
>   choose to dump only a given extension

Also --exclude-extension?

> The extension dumping will not include the shared modules, so if you
> extension depend on them being installed on the server, you will be much
> better served with some OS level packaging.

Or must make sure it’s installed on the system before you restore.

> Not for 9.2, but I can't help thinking that if we could manage to host
> the .so module itself in the catalogs, we could solve updating it in a
> transactional way and more importantly host it per-database, rather than
> having the modules work per major version (not even per cluster) and the
> extension mechanism work per-database inside each cluster. But that's
> work for another release.

+1 Cloud vendors will *love* 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: [HACKERS] Inline Extension

[David E. Wheeler]

On Jan 27, 2012, at 2:19 AM, Cédric Villemain wrote:

>> Also --exclude-extension?
> 
> It might be the default.
> We need something to dump the content of
> pg_catalog.pg_extension_script (or whatever table is going to contain
> SQL code), per extension or all.

I think dim said --no-extensions would be the default, but I’m thinking it 
would be useful to have --with-extensions to include them all, but then be able 
to --exclude-extension for a select few.

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] Access Error Details from PL/pgSQL

[David E. Wheeler]

Hackers,

In PL/pgSQL exception handling, I'm able to access the error code (SQLSTATE) 
and error message (SQLERRM). Is there any way to get at error details (yet)? If 
not, could SQLDETAIL or some such be added?

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] Access Error Details from PL/pgSQL

[David E. Wheeler]

On Feb 13, 2012, at 9:30 AM, Pavel Stehule wrote:

> no in stable
> 
> http://www.depesz.com/2011/07/20/waiting-for-9-2-stacked-diagnostics-in-plpgsql/

Ah, great, I had forgotten about that.

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: [HACKERS] Triggers with DO functionality

[David E. Wheeler]

On Feb 17, 2012, at 5:22 AM, Thom Brown wrote:

> The purpose being to only have a single statement to set up the
> trigger rather than setting up a separate trigger function which will
> unlikely be re-used by other triggers... or is this of dubious
> benefit?

+1, though I imagine it would just give it a generated name and save it anyway, 
eh?

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] Review: alternate psql file locations

[David E. Wheeler]

Reference: 
http://archives.postgresql.org/message-id/4f00ca9e.9000...@dunslane.net

This patch adds support for two new environment variables:

PSQL_HISTORY: Alternative location for the command history file.
PSQLRC: Alternative location of the user's .psqlrc file.

The context diff patch applies cleanly with a few offsets. Builds normally and 
all tests pass, although it adds no new tests. The documentation looks fine. 
The functionality is useful and works perfectly well in my quick test.

Marking ready for committer.

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] Document hashtext() and Friends?

[David E. Wheeler]

Hackers,

Is there a reason that hashtext() and friends are not documented? Given that 
they’re likely to be used more and more for partitioning and sharding, I think 
it would be useful to do so, starting with something like this. Comments?

*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 1557,1562 
--- 1557,1577 

 
  
+  hashtext
+ 
+ 
hashtext(string)
+
+int
+
+ Generate a hash value for string.
+
+hashtext('greetings, human')
+-1132466231
+   
+ 
+   
+
+ 
   left
  
  left(str 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] Document hashtext() and Friends?

[David E. Wheeler]

On Feb 21, 2012, at 12:11 PM, Michael Glaesemann wrote:

> And hashtext *has* changed across versions, which is why Peter Eisentraut 
> published a version-independent hash function library: 
> https://github.com/petere/pgvihash

Yes, Marko wrote one, too:

  https://github.com/markokr/pghashlib

But as I’m about to build a system that is going to have many billions of 
nodes, I could use a variant that returns a bigint. Anyone got a pointer to 
something like that?

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] Document hashtext() and Friends?

[David E. Wheeler]

On Feb 21, 2012, at 12:14 PM, David E. Wheeler wrote:

>> And hashtext *has* changed across versions, which is why Peter Eisentraut 
>> published a version-independent hash function library: 
>> https://github.com/petere/pgvihash
> 
> Yes, Marko wrote one, too:
> 
>  https://github.com/markokr/pghashlib

Oh, and these are great extensions for PGXN. Any chance of seeing them 
submitted soon, Peter and Marko?

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] overriding current_timestamp

[David E. Wheeler]

On Feb 23, 2012, at 3:08 AM, Peter Eisentraut wrote:

> For (unit) testing, I have often had the need to override the current
> timestamp in the database system.  For example, a column default,
> function, or views would make use of the current timestamp in some way,
> and to test the behavior, it's sometimes useful to tweak the current
> timestamp.
> 
> What might be a good way to do that?
> 
> Just overwrite xactStartTimestamp?  Is that safe?  If it weren't static,
> a user-loaded function could do it.
> 
> Overwrite pg_catalog.now() in the test database?
> 
> Other ideas?
> 
> Some semi-official support for this sort of thing would be good.

I create a "mock" schema, add the function to it, and then put it in the 
search_path ahead of pg_catalog. See the example starting at slide 48 on 
http://www.slideshare.net/justatheory/pgtap-best-practices.

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] overriding current_timestamp

[David E. Wheeler]

On Feb 23, 2012, at 10:54 AM, David E. Wheeler wrote:

> I create a "mock" schema, add the function to it, and then put it in the 
> search_path ahead of pg_catalog. See the example starting at slide 48 on 
> http://www.slideshare.net/justatheory/pgtap-best-practices.

Sorry, starting at slide 480.

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] row_to_json() Bug

[David E. Wheeler]

Looks like row_to_json() thinks 0s are nulls:

postgres=# select row(0);
 row 
-
 (0)
(1 row)

postgres=# SELECT row_to_json(row(0));
 row_to_json 
-
 {"f1":null}
(1 row)

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] row_to_json() Bug

[David E. Wheeler]

On Feb 23, 2012, at 8:49 PM, Andrew Dunstan wrote:

> Fixed, Thanks for the report. (Also fixed in my 9.1 backport).

Awesome, thanks, will try it tomorrow.

David



smime.p7s
Description: S/MIME cryptographic signature

Re: [HACKERS] overriding current_timestamp

[David E. Wheeler]

On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:

>> I create a "mock" schema, add the function to it, and then put it in the 
>> search_path ahead of pg_catalog.
> 
> That doesn't work for current_timestamp and similar built-in functions,
> because they are always mapped to the pg_catalog schema.

I use it for NOW() all the time.

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] overriding current_timestamp

[David E. Wheeler]

On Feb 27, 2012, at 4:29 AM, Peter Eisentraut wrote:

>> Sorry, starting at slide 480.
> 
> That presentation only goes to slide 394.

Crimony, sorry, this presentation:

  http://www.slideshare.net/justatheory/test-drivern-database-development

Note that I put pg_catalog at the end of the search_path, so that my mocked 
function will be found before it gets found in pg_catalog. If you don't add it 
to the end, it's implicitly the first item in the search path.

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

[David E. Wheeler]

On Feb 26, 2012, at 4:53 AM, Peter Eisentraut wrote:

>> I also liked Kevin's suggestion of DISCREET
> 
> That would probably create too much confusion with "discrete".

SECRETE?

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] overriding current_timestamp

[David E. Wheeler]

On Feb 27, 2012, at 11:39 AM, Peter Eisentraut wrote:

>> I use it for NOW() all the time.
> 
> But it won't work for current_timestamp.

Why not? Not challenging your assertion here, just curious why it’s different.

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] overriding current_timestamp

[David E. Wheeler]

On Feb 27, 2012, at 11:43 AM, Peter Eisentraut wrote:

>> Why not? Not challenging your assertion here, just curious why it’s 
>> different.
> 
> Because it's not actually a function, it's hardcoded in the grammar to
> call pg_catalog.now().

Ah, I see. Pity.

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] pgsql_fdw, FDW for PostgreSQL server

[David E. Wheeler]

On Feb 28, 2012, at 8:20 AM, Robert Haas wrote:

> I liked the shorter name, myself, but I'm not going to make a big deal about 
> it.

pg_ is used quite a bit. what about pg_fdw?

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] Custom Operators Cannot be Found for Composite Type Values

[David E. Wheeler]

Hackers,

I’m doing some development with the new JSON type (actually, Andrew’s backport 
to 9.1) and needed to do some very basic equivalence testing. So I created a 
custom operator:

CREATE OR REPLACE FUNCTION json_eq(
json,
json
) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT $1::text = $2::text;
$$;

CREATE OPERATOR = (
LEFTARG   = json,
RIGHTARG  = json,
PROCEDURE = json_eq
);

With this in place, these work:

 SELECT '{}'::json = '{}'::json;
 SELECT ROW('{}'::json) = ROW('{}'::json);

However this does not:

create type ajson AS (a json);
SELECT ROW('{}'::json)::ajson = ROW('{}'::json)::ajson;

That last line emits an error:

ERROR:  could not identify an equality operator for type json

To which my response was: WTF? Is this expected behavior? Is there something 
about custom operators that they can’t be used to compare the values of values 
in composite types?

I’ve worked around it by writing a separate operator to compare ajson types 
using

SELECT $1::text = $2::text

But it’s a bit annoying.

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] Custom Operators Cannot be Found for Composite Type Values

[David E. Wheeler]

On Mar 7, 2012, at 8:23 PM, Tom Lane wrote:

> You have not told the system that your operator is equality for the
> datatype.  It's just a random operator that happens to be named "=".
> We try to avoid depending on operator names as cues to semantics.
> 
> You need to incorporate it into a default hash or btree opclass before
> the composite-type logic will accept it as the thing to use for
> comparing that column.

Ah, okay. Just need more stuff, I guess:

CREATE OR REPLACE FUNCTION json_cmp(
json,
json
) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT bttextcmp($1::text, $2::text);
$$;

CREATE OR REPLACE FUNCTION json_eq(
json,
json
) RETURNS BOOLEAN LANGUAGE SQL STRICT IMMUTABLE AS $$
SELECT bttextcmp($1::text, $2::text) = 0;
$$;

CREATE OPERATOR = (
LEFTARG   = json,
RIGHTARG  = json,
PROCEDURE = json_eq
);

CREATE OPERATOR CLASS json_ops
DEFAULT FOR TYPE JSON USING btree AS
OPERATOR3   =  (json, json),
FUNCTION1   json_cmp(json, json);

This seems to work.

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] Custom Operators Cannot be Found for Composite Type Values

[David E. Wheeler]

On Mar 8, 2012, at 11:16 AM, Tom Lane wrote:

>> This seems to work.
> 
> Urk.  You really ought to provide the whole opclass (all 5 operators).
> I'm not sure what will blow up if you leave it like that, but it won't
> be pretty.

Yes, I expect to have to fill in gaps as I go. These are just for unit tests, 
so I’m not too worried about it (yet).

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] Custom Operators Cannot be Found for Composite Type Values

[David E. Wheeler]

On Mar 8, 2012, at 11:27 AM, Andrew Dunstan wrote:

> Yeah. Note too that this is at best dubious:
> 
>CREATE OR REPLACE FUNCTION json_cmp(
>json,
>json
>) RETURNS INTEGER LANGUAGE SQL STRICT IMMUTABLE AS $$
>SELECT bttextcmp($1::text, $2::text);
>$$;
> 
> 
> Two pieces of JSON might well be textually different but semantically 
> identical (e.g. by one having additional non-semantic whitespace).

Yes. This is just for unit tests, and is fine for the moment. If I end up with 
abnormalities, I will likely rewrite json_cmp() in Perl and use JSON::XS to do 
normalization. Not needed yet, though.

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] regress bug

[David E. Wheeler]

On Mar 8, 2012, at 10:22 AM, Andrew Dunstan wrote:

> This seems like an outright bug. I don't recall any discussion on it. Maybe 
> nobody's come across it before. ISTM the correct behaviour would be to put 
> converted sql files in $inputdir/sql and converted results files in 
> $outputdir/expected.

In my extension distributions, I have

tests/sql
tests/expected

And for that, --inputdir=test works just fine. I don't mess with --outputdir, 
which just seems to affect where the actual output is written to, which is just 
a directory named regression.out at the top of the project.

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] regress bug

[David E. Wheeler]

On Mar 8, 2012, at 12:20 PM, Andrew Dunstan wrote:

> It works fine if you don't need to do any file conversions (i.e. if you don't 
> have "input" or "output" directories). But file_textarray_fdw does.
> 
> Here's a patch that I think fixes the problem.

While you’re there, an issue I noticed is that the MODULE_PATHNAME 
substitutions do not work if you have your SQL files in a subdirectory. My 
extensions have the .sql files in an sql/ directory. So that means when I have 
something like this in sql/plproxy.sql.in:

CREATE FUNCTION plproxy_call_handler ()
RETURNS language_handler AS 'MODULE_PATHNAME' LANGUAGE C;

What I end up with in sql/plproxy.sql is:

CREATE FUNCTION plproxy_call_handler ()
RETURNS language_handler AS 'sql/plproxy' LANGUAGE C;

Which doesn’t work at all, because the file is not installed in an `sql` 
subdirectory, it's just that way in my repository (and the distribution 
tarball). So I avoid the whole MODULE_PATHNAME business for now (and the .in 
file) and just do this, instead:

CREATE FUNCTION plproxy_call_handler ()
RETURNS language_handler AS 'plproxy' LANGUAGE C;

Which is an okay workaround, but I’m not sure that MODULE_PATHNAME is actually 
working correctly.

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] regress bug

[David E. Wheeler]

On Mar 8, 2012, at 12:59 PM, Tom Lane wrote:

> Huh?  MODULE_PATHNAME is not substituted by pg_regress at all (anymore
> anyway).

Yeah, sorry, I meant `make`.

> There's still some vestigial support for it in pgxs.mk, but
> the future of that code is to vanish, not get improved.  You should
> not be needing it to get substituted at build time either.

I still see this pattern a *lot*; I removed it from PL/Proxy last week. The 
attached tarball demonstrates it:

> make
sed 's,MODULE_PATHNAME,$libdir/sql/exttest,g' sql/exttest.sql.in 
>sql/exttest.sql
make: *** No rule to make target `exttest.so', needed by `all'.  Stop.

So MODULE_PATHNAME is replaced with $libdir/sql/exttest rather than 
$libdir/exttest. Maybe that should not be fixed, but there are a *lot* of 
extensions out there using this approach (copied from contrib, which used it 
for years, albeit without the .sql.in files in a subdirectory).

So perhaps DATA_built is to be removed from pgxs.mk? And if so, is the idea 
then that one should just put the module name in the .sql file, rather than 
MODULE_PATHNAME in a .sql.in file?

Best,

David


exttest.tgz
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] regress bug

[David E. Wheeler]

On Mar 8, 2012, at 1:45 PM, Andrew Dunstan wrote:

>> So perhaps DATA_built is to be removed from pgxs.mk? And if so, is the idea 
>> then that one should just put the module name in the .sql file, rather than 
>> MODULE_PATHNAME in a .sql.in file?
> 
> Extensions (unlike non-extension modules) should not monkey with 
> MODULE_PATHNAME at all.
> 
> Change the Makefile def from DATA_built to DATA and rename the file to 
> exttest.sql

I did (and I do). But this is not documented or recommended anywhere. Something 
should be promulgated to encourage existing authors to do that.

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] elegant and effective way for running jobs inside a database

[David E. Wheeler]

On Mar 9, 2012, at 7:55 AM, Merlin Moncure wrote:

> 100% agree  (having re-read the thread and Alvaro's idea having sunk
> in).  Being able to set up daemon processes side by side with the
> postmaster would fit the bill nicely.  It's pretty interesting to
> think of all the places you could go with it.

pgAgent could use it *right now*. I keep forgetting to restart it after 
restarting PostgreSQL and finding after a day or so that no jobs have run.

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] Advisory Lock BIGINT Values

[David E. Wheeler]

Hackers,

The documentation for pg_locks says that, for BIGINT advisory locks:

> A bigint key is displayed with its high-order half in the classid column, its 
> low-order half in the objid column

I was in need of knowing what the bigint is that is waiting on a lock, and 
Andrew Dunstan was kind enough to help me out with that. Since other folks 
might also need it, here’s a doc patch.

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
new file mode 100644
index 9564e01..de1c266
*** a/doc/src/sgml/catalogs.sgml
--- b/doc/src/sgml/catalogs.sgml
***
*** 7313,7319 
 A bigint key is displayed with its
 high-order half in the classid column, its low-order half
 in the objid column, and objsubid equal
!to 1.  Integer keys are displayed with the first key in the
 classid column, the second key in the objid
 column, and objsubid equal to 2.  The actual meaning of
 the keys is up to the user.  Advisory locks are local to each database,
--- 7313,7322 
 A bigint key is displayed with its
 high-order half in the classid column, its low-order half
 in the objid column, and objsubid equal
!to 1. The original bigint value can be reassembled with the
!expression (classid::int::bit(64) << 32 |
!objid::int::bit(64))::bigint. Integer keys are displayed with the
!first key in the
 classid column, the second key in the objid
 column, and objsubid equal to 2.  The actual meaning of
 the keys is up to the user.  Advisory locks are local to each database,

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] Finer Extension dependencies

[David E. Wheeler]

On Mar 29, 2012, at 4:42 AM, Robert Haas wrote:

> 2. Add a new feature to the provides line with every release that does
> anything other than fix bugs, leading to:
> 
> provides = foobar-1.1, foobar-1.2, foobar-1.3, foobar-1.4, foobar-1.5,
> foobar-1.6, foobar-2.0, foobar-2.1, foobar-2.2, foobar-2.3,
> foobar-3.0, foobar-3.1

This is what I have expected to do. In new releases of pgTAP, I’d probably just 
add version lines. I might give certain releases names, but probably not. I’m 
too lazy, and if a given release has more than one new feature, it’d be a bit 
silly.

I’ve never been very keen on this approach, but then I don’t understand 
packaging systems very well, so it might rock, and I just don’t know how to use 
it properly. But I cannot tell.

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] Finer Extension dependencies

[David E. Wheeler]

On Mar 29, 2012, at 11:48 AM, Robert Haas wrote:

> Frankly, I'm not sure we bet on the right horse in not mandating a
> version numbering scheme from the beginning.  But given that we
> didn't, we probably don't want to get too forceful about it too
> quickly.  However, we could ease into it by documenting a recommended
> numbering scheme and making features like version-dependencies work
> only when that scheme is used.

PGXN mandates semantic versions for this reason (currently v1.0.0):

  http://semver.org/spec/v1.0.0.html

Removes all the ambiguity.

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] Finer Extension dependencies

[David E. Wheeler]

On Apr 2, 2012, at 11:24 AM, Peter Eisentraut wrote:

> Or an extension could specify itself which version numbering scheme it
> uses.  This just has to be a reference to a type, which in turn could be
> semver, debversion, or even just numeric or text (well, maybe name).
> Then you'd just need to use the comparison operators of that type to
> figure things out.

Sounds like a lot of work for core to maintain various version comparison 
schemes…

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] Finer Extension dependencies

[David E. Wheeler]

On Apr 2, 2012, at 11:58 AM, Tom Lane wrote:

>> Sounds like a lot of work for core to maintain various version comparison 
>> schemes
> 
> Well, the primary argument for avoiding version comparison semantics to
> begin with was exactly that we didn't want to mandate a particular
> version-numbering scheme.  However, if we're going to decide that we
> have to have version comparisons, I think we should just bite the bullet
> and specify one version numbering scheme.  More than one is going to add
> complexity, sow confusion, and not really buy anything.

Precisely my thinking.

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] Unsigned integer types

[David E. Wheeler]

On May 29, 2013, at 10:48 AM, Fabien COELHO  wrote:

> If you do it, having uint1 (1 byte) would be nice as well.

There is a signed 1byte int on PGXN, FWIW:

  http://pgxn.org/extension/tinyint

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] Perl 5.18 breaks pl/perl regression tests?

[David E. Wheeler]

On Jun 3, 2013, at 7:31 AM, Tom Lane  wrote:

> Hah.  That leads to
> http://perl5.git.perl.org/perl.git/commitdiff/451f421
> in which it's said "What happens is that eval tacks "\n;" on to the end
> of the string if it does not already end with a semicolon."
> 
> So we could likely hide the cross-version difference in behavior by
> adjusting these two test cases to include a semicolon in the eval'd
> string.

And a comment, since that is, shall we say, rather obscure.

David



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