Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/03/2014 10:11 PM, Tom Lane wrote: Josh Berkusj...@agliodbs.com writes: On 11/02/2014 11:41 AM, Tom Lane wrote: Nothing that I recall at the moment, but there is certainly plenty of stuff of dubious quality in there. I'd argue that chkpass, intagg, intarray, isn, spi, and xml2 are all in worse shape than the money type. Why are we holding on to xml2 again? IIRC, there's some xpath-related functionality in there that's not yet available in core (and needs some redesign before it'd ever get accepted into core, so there's not a real quick fix to be had). Yes, xpath_table is badly broken, as Robert Haas documented and I expanded on a while back. See for example http://www.postgresql.org/message-id/4d6bcc1e.3010...@dunslane.net I don't have any time of my own to work on this any time soon. But I know that it has users, so just throwing it out would upset some people. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] get_cast_func syscache utility function
here's a patch for a utility function to look up the cast function for a from/to pair of types, as recently suggested by Alvaro. Although it only contains one use (in json.c), the upcoming jsonb generators would also use it twice. I'd like to get this committed fairly quickly so I can prepare an updated patch for the jsonb generators. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] get_cast_func syscache utility function
On 11/04/2014 12:57 PM, Andrew Dunstan wrote:
here's a patch for a utility function to look up the cast function for
a from/to pair of types, as recently suggested by Alvaro. Although it
only contains one use (in json.c), the upcoming jsonb generators would
also use it twice. I'd like to get this committed fairly quickly so I
can prepare an updated patch for the jsonb generators.
This time with patch.
cheers
andrew
diff --git a/src/backend/utils/adt/json.c b/src/backend/utils/adt/json.c
index d2bf640..fe9cf83 100644
--- a/src/backend/utils/adt/json.c
+++ b/src/backend/utils/adt/json.c
@@ -1299,22 +1299,12 @@ json_categorize_type(Oid typoid,
/* but let's look for a cast to json, if it's not built-in */
if (typoid = FirstNormalObjectId)
{
- HeapTuple tuple;
+ Oid castfunc = get_cast_func(typoid, JSONOID);
- tuple = SearchSysCache2(CASTSOURCETARGET,
- ObjectIdGetDatum(typoid),
- ObjectIdGetDatum(JSONOID));
- if (HeapTupleIsValid(tuple))
+ if (OidIsValid(castfunc))
{
- Form_pg_cast castForm = (Form_pg_cast) GETSTRUCT(tuple);
-
- if (castForm-castmethod == COERCION_METHOD_FUNCTION)
- {
- *tcategory = JSONTYPE_CAST;
- *outfuncoid = castForm-castfunc;
- }
-
- ReleaseSysCache(tuple);
+ *tcategory = JSONTYPE_CAST;
+ *outfuncoid = castfunc;
}
}
}
diff --git a/src/backend/utils/cache/lsyscache.c b/src/backend/utils/cache/lsyscache.c
index 552e498..0da08fe 100644
--- a/src/backend/utils/cache/lsyscache.c
+++ b/src/backend/utils/cache/lsyscache.c
@@ -21,6 +21,7 @@
#include bootstrap/bootstrap.h
#include catalog/pg_amop.h
#include catalog/pg_amproc.h
+#include catalog/pg_cast.h
#include catalog/pg_collation.h
#include catalog/pg_constraint.h
#include catalog/pg_namespace.h
@@ -2889,3 +2890,36 @@ get_range_subtype(Oid rangeOid)
else
return InvalidOid;
}
+
+/*
+ *
+ * get_cast_func(fromtype, totype) - funcoid
+ *
+ * find a cast function if any from fromtype to totype.
+ *
+ * return the Oid of the fucntion found or InvalidOid otherwise.
+ */
+
+Oid
+get_cast_func(Oid from_type, Oid to_type)
+{
+ HeapTuple tuple;
+ Oid castfunc = InvalidOid;
+
+ tuple = SearchSysCache2(CASTSOURCETARGET,
+ ObjectIdGetDatum(from_type),
+ ObjectIdGetDatum(to_type));
+ if (HeapTupleIsValid(tuple))
+ {
+ Form_pg_cast castForm = (Form_pg_cast) GETSTRUCT(tuple);
+
+ if (castForm-castmethod == COERCION_METHOD_FUNCTION)
+ {
+ castfunc = castForm-castfunc;
+ }
+
+ ReleaseSysCache(tuple);
+ }
+
+ return castfunc;
+}
diff --git a/src/include/utils/lsyscache.h b/src/include/utils/lsyscache.h
index 07d24d4..0291b96 100644
--- a/src/include/utils/lsyscache.h
+++ b/src/include/utils/lsyscache.h
@@ -151,6 +151,7 @@ extern void free_attstatsslot(Oid atttype,
float4 *numbers, int nnumbers);
extern char *get_namespace_name(Oid nspid);
extern Oid get_range_subtype(Oid rangeOid);
+extern Oid get_cast_func(Oid from_type, Oid to_type);
#define type_is_array(typid) (get_element_type(typid) != InvalidOid)
/* type_is_array_domain accepts both plain arrays and domains over arrays */
--
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] COPY TO returning empty result with parallel ALTER TABLE
On 11/04/2014 01:51 PM, Tom Lane wrote: Bernd Helmle maili...@oopsware.de writes: --On 3. November 2014 18:15:04 +0100 Sven Wegener sven.wege...@stealer.net wrote: I've check git master and 9.x and all show the same behaviour. I came up with the patch below, which is against curent git master. The patch modifies the COPY TO code to create a new snapshot, after acquiring the necessary locks on the source tables, so that it sees any modification commited by other backends. Well, i have the feeling that there's nothing wrong with it. The ALTER TABLE command has rewritten all tuples with its own XID, thus the current snapshot does not see these tuples anymore. I suppose that in SERIALIZABLE or REPEATABLE READ transaction isolation your proposal still doesn't return the tuples you'd like to see. Not sure. The OP's point is that in a SELECT, you do get unsurprising results, because a SELECT will acquire its execution snapshot after it's gotten AccessShareLock on the table. Arguably COPY should behave likewise. Or to be even more concrete, COPY (SELECT * FROM tab) TO ... probably already acts like he wants, so why isn't plain COPY equivalent to that? Yes, that seems like an outright bug. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/02/2014 10:01 AM, Jaime Casanova wrote: El nov 2, 2014 7:54 AM, Andres Freund and...@2ndquadrant.com mailto:and...@2ndquadrant.com escribió: On 2014-11-01 16:59:35 -0700, Josh Berkus wrote: All, While there's argument about hash indexes, it looks like nobody minds if the MONEY type goes bye-bye. So, time for a patch ... FWIW there have been somewhat recent patches for money and it was undeprecated not too long ago. So apparently there's users for it out there. I personally would never use money, but I doubt the gain is worth the cost of breaking existing setups without a warning period. Not knowing how difficult it could be maybe a fair compromise is to move MONEY datatype to a contrib. And documenting its limitations. That's pretty much dead in the water, I think. Builtin types and functions have Oid values in different ranges from non-builtin types such as those in contrib. It's one reason we have no chance of bringing hstore into core as people have previously asked for. And for the same reason I think moving a core type out to contrib won't work. In any case, contrib shouldn't be a rubbish heap of old deprecated features. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/02/2014 11:53 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 11/02/2014 10:01 AM, Jaime Casanova wrote: Not knowing how difficult it could be maybe a fair compromise is to move MONEY datatype to a contrib. And documenting its limitations. That's pretty much dead in the water, I think. Builtin types and functions have Oid values in different ranges from non-builtin types such as those in contrib. It's one reason we have no chance of bringing hstore into core as people have previously asked for. And for the same reason I think moving a core type out to contrib won't work. Well, the OID compatibility issue could be dodged by saying that we can't do a pg_upgrade (in-place upgrade) of a database containing MONEY columns. In fact, we might be able to just reject databases containing MONEY[] (array) columns, which seems like it might be only a minor hazard. Either way, requiring a dump/reload for upgrade is surely a better answer for users of the type than just summarily screwing them. Well, OK, yes, if we're prepared to abandon pg_upgrade-ability. In any case, contrib shouldn't be a rubbish heap of old deprecated features. There's a fair amount of contrib that was never anything else, so I don't agree with that reasoning too much. Maybe my memory is failing. What in contrib is stuff that used to be in core? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/02/2014 02:41 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 11/02/2014 11:53 AM, Tom Lane wrote: Well, the OID compatibility issue could be dodged by saying that we can't do a pg_upgrade (in-place upgrade) of a database containing MONEY columns. In fact, we might be able to just reject databases containing MONEY[] (array) columns, which seems like it might be only a minor hazard. Either way, requiring a dump/reload for upgrade is surely a better answer for users of the type than just summarily screwing them. Well, OK, yes, if we're prepared to abandon pg_upgrade-ability. Not following your point? Removing the type entirely would certainly break pg_upgrade-ability as well. I'm not entirely convinced that we should remove it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/01/2014 01:26 PM, Andres Freund wrote: On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: On 10/31/2014 03:07 PM, Tom Lane wrote: I don't care one way or the other about the money type, but I will defend hash indexes, especially seeing that we've already added a pretty in-your-face warning as of 9.5: regression=# create table foo(f1 int); CREATE TABLE regression=# create index on foo using hash (f1); WARNING: hash indexes are not WAL-logged and their use is discouraged CREATE INDEX Yes, and I'm arguing that is the wrong decision. If hash indexes are discouraged, then they shouldn't be in core in the first place. Last time we discussed it there were people (IIRC Andrew was one of them) commenting that they use hash indexes *precisely* because they're not WAL logged and that they can live with the dangers that creates. I don't think that's sufficient justification for introducing the feature at all. But it's nothing new that removing a feature has to fit quite different criteria than adding one. So, by that argument we could remove hash indexes once we have unlogged indexes on logged tables. But then there's no need to remove them anymore... Yes, although there might not be much reason to use them either. I think Tom's first step of making hash indexes automatically unlogged makes sense. Longer term I'd like to see unlogged as an option for all AMs - especially btree. It makes plenty of sense to me to be able to make the data resilient even if the indexes, which can after all be recreated in the unlikely event of a crash, are not. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/01/2014 01:58 PM, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-11-01 10:18:03 -0700, Josh Berkus wrote: Yes, and I'm arguing that is the wrong decision. If hash indexes are discouraged, then they shouldn't be in core in the first place. Last time we discussed it there were people (IIRC Andrew was one of them) commenting that they use hash indexes *precisely* because they're not WAL logged and that they can live with the dangers that creates. I don't think that's sufficient justification for introducing the feature at all. But it's nothing new that removing a feature has to fit quite different criteria than adding one. So, by that argument we could remove hash indexes once we have unlogged indexes on logged tables. But then there's no need to remove them anymore... Yeah. When we last discussed this, the difficulty was around how to make that combination work. An unlogged index on an unlogged table is no problem: the init-fork mechanism is able to make them both go to empty after a crash. But for an unlogged index on a logged table, just making the index go to empty is not the correct recovery action. We'd been wondering how to make crash recovery change the index's pg_index entry into not indisvalid/indisready status. That's hard to do. But maybe we don't have to. What about having the init-fork mechanism restore a hash index into a state with the metapage marked as invalid? hashinsert etc could simply do nothing when they see this metapage state. hashgettuple could throw an error saying the index isn't usable until it's been REINDEXed. This is not quite as nice as an indisvalid-based solution, because it requires some extra cooperation from the index AM's code. But setting up an init fork requires work from the index AM anyway, so that objection doesn't seem terribly strong to me. Thoughts? Isn't the planner still going to try to use the index in that case? If it's not then I'd be OK with it, but if it's going to make the table largely unusable until it's reindexed that would be rather sad. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/01/2014 02:34 PM, Andres Freund wrote: Yeah, if we were trying to duplicate the behavior of indisvalid, there'd need to be a way to detect the invalid index at plan time and not use it. But I'm not sure that that's actually an improvement from the user's standpoint: what they'd see is queries suddenly, and silently, performing a lot worse than they expect. An explicit complaint about the necessary REINDEX seems more user-friendly from where I sit. A REINDEX is imo unlikely to be acceptable. It takes long (why would you bother on a small table?) and locks the relation/indexes. It's a bit of a pity we don't have REINDEX CONCURRENTLY. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Let's drop two obsolete features which are bear-traps for novices
On 11/01/2014 02:39 PM, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: A REINDEX is imo unlikely to be acceptable. It takes long (why would you bother on a small table?) and locks the relation/indexes. I think the goalposts just took a vacation to Acapulco. What exactly do you think is going to make a crashed unlogged index valid again without a REINDEX? Certainly the people who are currently using hash indexes in the way Andrew describes are expecting to have to REINDEX them after a crash. That's certainly true. They were warned of the risks and found them acceptable. The real question here is whether the table should continue to be usable in a degraded state until it's reindexed. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/30/2014 09:17 PM, Andres Freund wrote: On 2014-10-30 21:03:43 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-10-30 20:13:53 -0400, Tom Lane wrote: As I said upthread, that approach seems to me to be contrary to the project policy about how configure should behave. I don't think that holds much water. There's a fair amount of things that configure detects automatically. I don't think the comparison to plperl or such is meaningful - that's a runtime/install time difference. These tests are not. Meh. Right now, it's easy to dismiss these tests as unimportant, figuring that they play little part in whether the completed build is reliable. But that may not always be true. If they do become a significant part of our test arsenal, silently omitting them will not be cool for configure to do. Well, I'm all for erroring out if somebody passed --enable-foo-tests and the prerequisites aren't there. What I *am* against is requiring an explicit flag to enable them because then they'll just not be run in enough environments. And that's what's much more likely to cause unnoticed bugs. When this is properly sorted out I will enable this in the buildfarm default configuration. So I don't think that's going to be an issue in the long term. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] CREATE INDEX CONCURRENTLY?
On 10/31/2014 10:28 AM, Mark Woodward wrote: I have not kept up with PostgreSQL changes and have just been using it. A co-worker recently told me that you need to word CONCURRENTLY in CREATE INDEX to avoid table locking. I called BS on this because to my knowledge PostgreSQL does not lock tables. I referenced this page in the documentation: http://www.postgresql.org/docs/9.3/static/locking-indexes.html That page refers to using the indexes, not creating them. However, I do see this sentence in the indexing page that was not in the docs prior to 8.0: Creating an index can interfere with regular operation of a database. Normally PostgreSQL locks the table to be indexed against writes and performs the entire index build with a single scan of the table. Is this true? When/why the change? When we use concurrently, it seems to hang. I am looking into it. Creating indexes always did lock tables. See for example http://www.postgresql.org/docs/7.4/static/explicit-locking.html#LOCKING-TABLES there CREATE INDEX is documented to take a SHARE lock on the table. CONCURRENTLY was an additional feature to allow you to get around this, at the possible cost of some extra processing. So we haven't made things harder, we've made them easier, and your understanding of old releases is incorrect. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/30/2014 05:23 PM, Tom Lane wrote: I wrote: Yup, you read that right, it took 32 seconds to run those dozen utterly trivial tests. As far as I could tell by eyeball, pretty much all of the time went into test 11, which is odd since it seems not significantly different from the others. I think there's something wacky about IPC::Run on this platform. Oh, never mind: after digging into the test source I eventually realized that there's an initdb happening between tests 10 and 11, and that's what's eating the time. It might be a thought to print something to indicate that a time-consuming step is happening; but the lack of any feedback here isn't exactly IPC::Run's fault. Anyway, I can confirm Peter's statement that the current tests work even on quite old platforms, as long as you install IPC::Run. So, I'm a bit confused. Is the --enable-tap-tests config setting still on the table? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/30/2014 09:37 PM, Andres Freund wrote: On 2014-10-30 21:24:04 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-10-30 21:03:43 -0400, Tom Lane wrote: Meh. Right now, it's easy to dismiss these tests as unimportant, figuring that they play little part in whether the completed build is reliable. But that may not always be true. If they do become a significant part of our test arsenal, silently omitting them will not be cool for configure to do. Well, I'm all for erroring out if somebody passed --enable-foo-tests and the prerequisites aren't there. What I *am* against is requiring an explicit flag to enable them because then they'll just not be run in enough environments. And that's what's much more likely to cause unnoticed bugs. Once they're at the point where they're actually likely to catch stuff of interest, I'll be all for enabling them by default. Great. We already are at that point due to the pg_basebackup tests. If we slightly extend it to also start up the newly made base backups we will have the first minimal automated test of recovery... There are other issues. I am not going to enable this in the buildfarm until the check test can work from a single install. It's insane for the bin tests to take an order of magnitude longer than the main regression suite. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] pg_dump/pg_restore seem broken on hamerkop
On 10/29/2014 12:26 AM, Tom Lane wrote: I wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: [Some more code and git-log reading later] I see that the %z is a very recent addition: it only got there as of commit ad5d46a449, of September 5th ... and now I also see that hamerkop's last green run before the failure, on Oct 13rd, did *not* include the pg_upgrade check. So I'm thinking this was broken much earlier than 0eea804. Ooohh ... you are right, the first failing build involved not only the pg_dump refactoring commit, but an upgrade in the buildfarm script that hamerkop was using (from 4.4 to 4.14). So it's entirely possible this issue was already there and we just weren't seeing it tested. hamerkop is still failing in its runs done today. However, I'm not sure that that proves anything about our hoped-for fix, because the commit SHAs it's showing on the buildfarm status page are still from Oct 13. It looks like hamerkop has failed to pull any git updates since it was migrated to the new buildfarm script version. This machine appears to be misconfigured for git. I will email the owner about fixing it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb generator functions
On 10/27/2014 05:57 PM, Alvaro Herrera wrote:
Andrew Dunstan wrote:
This bit:
+/*
+ * Determine how we want to render values of a given type in datum_to_jsonb.
+ *
+ * Given the datatype OID, return its JsonbTypeCategory, as well as the type's
+ * output function OID. If the returned category is JSONBTYPE_CAST, we
+ * return the OID of the type-JSON cast function instead.
+ */
+static void
+jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid)
+{
seems like it can return without having set the category and func OID,
if there's no available cast. Callers don't seem to check for this
condition; is this a bug? If not, why not? Maybe some extra comments
are warranted.
Umm, no. The outfuncoid is set by the call to getTypeOutputInfo() and
the category is set by every branch of the switch. We override the
funcoid in the case where there's a cast to json or jsonb.
I'll add a comment to that effect.
Right now, for the general case there, there are two syscache lookups
rather than one. The fix is simple: just do the getTypeOutputInfo call
inside each case inside the switch instead of once at the beginning, so
that the general case can omit it; then there is just one syscache
access in all the cases. json.c suffers from the same problem.
We only do more than one if it's not a builtin type, or an array or
composite. So 99% of the time this won't even be called.
Anyway this whole business of searching through the CASTSOURCETARGET
syscache seems like it could be refactored. If I'm counting correctly,
that block now appears four times (three in this patch, once in json.c).
Can't we add a new function to (say) lsyscache and remove that?
Twice, not three times in this patch, unless I'm going crazier than I
thought.
I can add a function to lsyscache along the lines of
Oid get_cast_func(Oid from_type, Oid to_type)
if you think it's worth it.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] foreign data wrapper option manipulation during Create foreign table time?
On 10/28/2014 05:26 PM, Demai Ni wrote: hi, guys, I am looking for a couple pointers here about fdw, and how to change the option values during CREATE table time. I am using postgres-xc-1.2.1 right now. For example, it contains file_fdw, whose create-table-stmt looks like: CREATE FOREIGN TABLE t1() SERVER file_server OPTIONS(format 'text',filename *'testing.txt'*); I would like to replace the 'testing.txt' with absolute path like '/user/testor1/testing.txt', and make sure the new value is saved in pg_foreign_table; the file_fdw_validator is used to validate the options, but is there a way to replace the optionValue here? And get the new value stored in pg_foreign_table? Thanks BTW, in my real use case, I am trying to interpret a hdfs file and would need to save some hostname/port information in the option value, which not necessary specified by user. This is the wrong list to ask this - it's a usage question that belongs on pgsql-general See the documentation for ALTER FOREIGN TABLE. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] how to handle missing prove
On 10/28/2014 09:16 PM, Tom Lane wrote: Peter Eisentraut pete...@gmx.net writes: Here is a patch to use missing to handle the case when prove is not present. Wouldn't it be easier to do what we do for Perl, viz in Makefile.global.in ifneq (@PERL@,) # quoted to protect pathname with spaces PERL = '@PERL@' else PERL = $(missing) perl endif However, with either of these approaches, make check-world gets a hard failure if you lack prove. Is that what we want? It's certainly not very consistent with what you've been doing to make the tests just slide by (rather than fail on) missing/too old Perl modules. ISTM that the project policy for external components like this has been don't rely on them unless user says to use them, in which case fail if they aren't present. So perhaps what we ought to have is a configure switch along the lines of --enable-tap-tests. If you don't specify it, prove_check expands to nothing. If you do specify it, we fail if we lack any of the expected support, both prove and whatever the agreed-on set of Perl modules is. +1 If we go this way I'll add a tap icon to the buildfarm so you can see which animals are running the tests. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb generator functions
On 10/15/2014 03:54 PM, Andrew Dunstan wrote:
I checked a code, and I have only two small objection - a name
jsonb_object_two_arg is not good - maybe json_object_keys_values ?
It's consistent with the existing json_object_two_arg. In all cases I
think I kept the names the same except for changing json to jsonb.
Note that these _two_arg functions are not visible at the SQL level -
they are only visible in the C code.
I'm happy to be guided by others in changing or keeping these names.
Next: there are no tests for to_jsonb function.
Oh, my bad. I'll add some.
Thank for the review.
Here is a new patch that includes documentation and addresses all these
issues, except that I didn't change the name of jsonb_object_two_arg to
keep it consistent with the name of json_object_two_arg. I'm happy to
change both if people feel it matters.
cheers
andrew
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7e5bcd9..21ce293 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10245,9 +10245,10 @@ table2-mapping
para
xref linkend=functions-json-creation-table shows the functions that are
- available for creating typejson/type values.
- (Currently, there are no equivalent functions for typejsonb/, but you
- can cast the result of one of these functions to typejsonb/.)
+ available for creating typejson/type and typejsonb/type values.
+ (There are no equivalent functions for typejsonb/, of the literalrow_to_json/
+ and literalarray_to_json/ functions. However, the literalto_jsonb/
+ function supplies much the same functionality as these functions would.)
/para
indexterm
@@ -10268,6 +10269,18 @@ table2-mapping
indexterm
primaryjson_object/primary
/indexterm
+ indexterm
+ primaryto_jsonb/primary
+ /indexterm
+ indexterm
+ primaryjsonb_build_array/primary
+ /indexterm
+ indexterm
+ primaryjsonb_build_object/primary
+ /indexterm
+ indexterm
+ primaryjsonb_object/primary
+ /indexterm
table id=functions-json-creation-table
titleJSON Creation Functions/title
@@ -10282,17 +10295,19 @@ table2-mapping
/thead
tbody
row
+ entryparaliteralto_json(anyelement)/literal
+ /paraparaliteralto_jsonb(anyelement)/literal
+ /para/entry
entry
- literalto_json(anyelement)/literal
- /entry
- entry
- Returns the value as JSON. Arrays and composites are converted
+ Returns the value as typejson/ or typejsonb/.
+ Arrays and composites are converted
(recursively) to arrays and objects; otherwise, if there is a cast
- from the type to typejson/type, the cast function will be used to
- perform the conversion; otherwise, a JSON scalar value is produced.
+ from the type to typejson/type or typejsonb/ in the case of
+ literalto_jsonb/, the cast function will be used to
+ perform the conversion; otherwise, a scalar value is produced.
For any scalar type other than a number, a Boolean, or a null value,
- the text representation will be used, properly quoted and escaped
- so that it is a valid JSON string.
+ the text representation will be used, in such a fashion that it is a
+ valid typejson/ or typejsonb/ value.
/entry
entryliteralto_json('Fred said Hi.'::text)/literal/entry
entryliteralFred said \Hi.\/literal/entry
@@ -10321,9 +10336,9 @@ table2-mapping
entryliteral{f1:1,f2:foo}/literal/entry
/row
row
- entry
- literaljson_build_array(VARIADIC any)/literal
- /entry
+ entryparaliteraljson_build_array(VARIADIC any)/literal
+ /paraparaliteraljsonb_build_array(VARIADIC any)/literal
+ /para/entry
entry
Builds a possibly-heterogeneously-typed JSON array out of a variadic
argument list.
@@ -10332,9 +10347,9 @@ table2-mapping
entryliteral[1, 2, 3, 4, 5]/literal/entry
/row
row
- entry
- literaljson_build_object(VARIADIC any)/literal
- /entry
+ entryparaliteraljson_build_object(VARIADIC any)/literal
+ /paraparaliteraljsonb_build_object(VARIADIC any)/literal
+ /para/entry
entry
Builds a JSON object out of a variadic argument list. By
convention, the argument list consists of alternating
@@ -10344,9 +10359,9 @@ table2-mapping
entryliteral{foo: 1, bar: 2}/literal/entry
/row
row
- entry
- literaljson_object(text[])/literal
- /entry
+ entryparaliteraljson_object(text[])/literal
+ /paraparaliteraljsonb_object(text[])/literal
+ /para/entry
entry
Builds a JSON object out of a text array. The array must have either
exactly one dimension with an even number of members, in which case
@@ -10359,9 +10374,9 @@ table2-mapping
entryliteral{a: 1, b: def, c: 3.5}/literal
Re: [HACKERS] TAP test breakage on MacOS X
On 10/27/2014 11:41 AM, Robert Haas wrote: On Sun, Oct 26, 2014 at 12:29 PM, Tom Lane t...@sss.pgh.pa.us wrote: The larger issue though is that even with both the above things fixed, the TAP tests would still be an expensive no-op on the majority of buildfarm members. AFAICT, I do not own a single machine on which the current TAP tests will consent to run, and in most cases that's after going out of my way to fetch CPAN modules that aren't in the vendor Perl installs. Peter's mostly been fixing the portability issues by disabling tests, which I guess is better than no fix at all, but it leaves darn little useful functionality. I agree, emphatically. Honestly, if we can't get these tests running everywhere with reasonable effort, we should just rip them out. We've gone to a lot of trouble in general to make sure that our source code can be ported even to systems that arguably nobody uses any more, and instrumental to that effort is keeping the system requirements to install and test PostgreSQL minimal. At this point, I wouldn't mind moving the goalposts from C89 to C89 + a bunch of C99 features that are available on all the platforms we have buildfarm coverage for, and I wouldn't mind require perl to compile and install, full stop. But this patch has gone much further than that: you need a new-enough version of perl, and a new-enough version of a bunch of modules that aren't installed by default, and maybe not even in the vendor install, and the documentation on how to make it work is an embarrassment: http://www.postgresql.org/docs/devel/static/regress-tap.html Beyond all that, I have serious doubts about whether, even if we eventually get these tests mostly working in most places, whether they will actually catch any bugs. For example, consider dropdb. The TAP tests cover the following points: - When run with --help or --version, it should exit with code 0, print something on stderr, and print nothing on stdout. - When run with --not-a-valid-option, it should exit with a non-zero exit code, print something on stderr, and print nothing on stdout. - dropdb foobar1 should cause statement: DROP DATABASE foobar1 to show up in the postgresql log file. - dropdb nonexistent should exit non-zero. These are certainly good things to test, but I'd argue that once you've verified that they are working, they're unlikely to get broken again in the future. I'm generally supportive of the idea that we need more automated tests, but these tests seem pretty low-value to me, even if they were running everywhere, and even moreso if they aren't. Yeah. I think at the very least they should be removed from the check-world and installcheck-world targets until this is sorted out. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: CREATE DATABASE vs. (partial) CHECKPOINT
On 10/27/2014 05:58 PM, Tomas Vondra wrote: On 27.10.2014 17:24, Heikki Linnakangas wrote: On 10/27/2014 03:46 PM, Tom Lane wrote: Heikki Linnakangas hlinnakan...@vmware.com writes: On 10/27/2014 03:21 PM, Tomas Vondra wrote: Thinking about this a bit more, do we really need a full checkpoint? That is a checkpoint of all the databases in the cluster? Why checkpointing the source database is not enough? A full checkpoint ensures that you always begin recovery *after* the DBASE_CREATE record. I.e. you never replay a DBASE_CREATE record during crash recovery (except when you crash before the transaction commits, in which case it doesn't matter if the new database's directory is borked). Yeah. After re-reading the 2005 thread, I wonder if we shouldn't just bite the bullet and redesign CREATE DATABASE as you suggest, ie, WAL-log all the copied files instead of doing a cp -r-equivalent directory copy. That would fix a number of existing replay hazards as well as making it safe to do what Tomas wants. In the small scale this would cause more I/O (2 copies of the template database's data) but in production situations we might well come out ahead by avoiding a forced checkpoint of the rest of the cluster. Also I guess we could skip WAL-logging if WAL archiving is off, similarly to the existing optimization for CREATE INDEX etc. That would be a nasty surprise for anyone who's using CREATE DATABASE as a fast way to clone a large database. But I would be OK with that, at least if we can skip the WAL-logging with wal_level=minimal. That's true. Sadly, I can't think of a solution that would address both use cases at the same time :-( The only thing I can think of is having two CREATE DATABASE flavors. One keeping the current approach (suitable for fast cloning) and one with the WAL logging (minimizing the CREATE DATABASE duration the impact on other backends). It will probably make the code significantly more complex, which is not exactly desirable, I guess. Also, if we keep the current code (even if only as a special case) it won't eliminate the existing replay hazards (which was one of the Tom's arguments for biting the bullet). I'm also thinking that for wal_level=archive and large databases, this won't really eliminate the checkpoint as it will likely generate enough WAL to hit checkpoint_segments and trigger a checkpoint anyway. No? That being said, our CREATE DATABASE docs currently say this Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose COPY DATABASE facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes. See Section 21.3 for more information. I think that this limitation pretty much means no one should use CREATE DATABASE for cloning live databases in production environment (because of the locking). It also seems to me the general-purpose COPY DATABASE described in the docs is what we're describing in this thread. Notwithstanding what the docs say, I have seen CREATE DATABASE used plenty of times, and quite effectively, to clone databases. I don't think making it do twice the IO in the general case is going to go down well. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: CREATE DATABASE vs. (partial) CHECKPOINT
On 10/27/2014 07:01 PM, Andres Freund wrote: On 2014-10-27 18:57:27 -0400, Andrew Dunstan wrote: On 10/27/2014 05:58 PM, Tomas Vondra wrote: On 27.10.2014 17:24, Heikki Linnakangas wrote: I'm also thinking that for wal_level=archive and large databases, this won't really eliminate the checkpoint as it will likely generate enough WAL to hit checkpoint_segments and trigger a checkpoint anyway. No? That being said, our CREATE DATABASE docs currently say this Although it is possible to copy a database other than template1 by specifying its name as the template, this is not (yet) intended as a general-purpose COPY DATABASE facility. The principal limitation is that no other sessions can be connected to the template database while it is being copied. CREATE DATABASE will fail if any other connection exists when it starts; otherwise, new connections to the template database are locked out until CREATE DATABASE completes. See Section 21.3 for more information. I think that this limitation pretty much means no one should use CREATE DATABASE for cloning live databases in production environment (because of the locking). It also seems to me the general-purpose COPY DATABASE described in the docs is what we're describing in this thread. Notwithstanding what the docs say, I have seen CREATE DATABASE used plenty of times, and quite effectively, to clone databases. I don't think making it do twice the IO in the general case is going to go down well. I think they're actually more likely to be happy that we wouldn't need do a immediate checkpoint anymore. The performance penalty from that likely to be much more severe than the actual IO. At the very least that needs to be benchmarked. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/07/2014 01:57 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: I don't much like the idea of doing an install/initdb/start for every directory in src/bin, though. Can't we at least manage a single installation directory for all these? Peter had a patch to eliminate the overhead of multiple subinstalls; not sure where that stands, but presumably it would address your issue. Is there any progress on this. I'm reluctant to add this to the buildfarm client until it's solved. These tests currently take a heck of a lot longer than any other test suite. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/26/2014 12:29 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 10/07/2014 01:57 PM, Tom Lane wrote: Peter had a patch to eliminate the overhead of multiple subinstalls; not sure where that stands, but presumably it would address your issue. Is there any progress on this. I'm reluctant to add this to the buildfarm client until it's solved. These tests currently take a heck of a lot longer than any other test suite. While I'd like to see that patch committed to cut the runtime of make check in contrib, it's hardly the only stumbling block between us and enabling TAP tests in the buildfarm. The pathname length problem I noted in http://www.postgresql.org/message-id/16477.1413831...@sss.pgh.pa.us seems like a show-stopper as well, since undoubtedly a number of buildfarm critters are using buildroots with paths long enough to trigger it. +1 for fixing that, although it seems like a problem in what's being tested rather than in the test suite. The larger issue though is that even with both the above things fixed, the TAP tests would still be an expensive no-op on the majority of buildfarm members. AFAICT, I do not own a single machine on which the current TAP tests will consent to run, and in most cases that's after going out of my way to fetch CPAN modules that aren't in the vendor Perl installs. Peter's mostly been fixing the portability issues by disabling tests, which I guess is better than no fix at all, but it leaves darn little useful functionality. I think we need a serious discussion about choosing a baseline Perl version on which we need the TAP tests to work, and then some effort to make the tests actually work (not just skip tests) on all versions beyond that. As far as the buildfarm goes, we could make it a cheap noop by checking for the presence of the required modules (AFAIK that's Test::More, IPC::CMD and IPC::Run). I agree that just having it not run tests on most platforms is hardly a solution. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] strip nulls functions for json and jsonb
On 10/16/2014 04:12 PM, Pavel Stehule wrote:
1. missing documentation
2. I miss more comments related to this functions. This code is
relative simple, but some more explanation can be welcome.
3. why these functions are marked as stable?
New patch:
Docs added, functions marked immutable, more comments added.
cheers
andrew
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7e5bcd9..352b408 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10716,6 +10716,19 @@ table2-mapping
/programlisting
/entry
/row
+ row
+ entryparaliteraljson_strip_nulls(from_json json)/literal
+ /paraparaliteraljsonb_strip_nulls(from_json jsonb)/literal
+ /para/entry
+ entryparatypejson/type/paraparatypejsonb/type/para/entry
+ entry
+ Returns replaceablefrom_json/replaceable
+ with all object fields that have null values omitted. Other null values
+ are untouched.
+ /entry
+ entryliteraljson_strip_nulls('[{f1:1,f2:null},2,null,3]')/literal/entry
+ entryliteral[{f1:1},2,null,3]/literal/entry
+ /row
/tbody
/tgroup
/table
@@ -10752,6 +10765,16 @@ table2-mapping
/para
/note
+ note
+para
+ If the argument to literaljson_strip_nulls/ contains duplicate
+ field names in any object, the result could be semantically somewhat
+ different, depending on the order in which they occur. This is not an
+ issue for literaljsonb_strip_nulls/ since jsonb values never have
+ duplicate object field names.
+/para
+ /note
+
para
See also xref linkend=functions-aggregate for the aggregate
function functionjson_agg/function which aggregates record
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 2d00dbe..06db3e4 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -105,6 +105,15 @@ static void populate_recordset_object_end(void *state);
static void populate_recordset_array_start(void *state);
static void populate_recordset_array_element_start(void *state, bool isnull);
+/* semantic action functions for json_strip_nulls */
+static void sn_object_start(void *state);
+static void sn_object_end(void *state);
+static void sn_array_start(void *state);
+static void sn_array_end(void *state);
+static void sn_object_field_start (void *state, char *fname, bool isnull);
+static void sn_array_element_start (void *state, bool isnull);
+static void sn_scalar(void *state, char *token, JsonTokenType tokentype);
+
/* worker function for populate_recordset and to_recordset */
static Datum populate_recordset_worker(FunctionCallInfo fcinfo, const char *funcname,
bool have_record_arg);
@@ -225,6 +234,13 @@ typedef struct PopulateRecordsetState
MemoryContext fn_mcxt; /* used to stash IO funcs */
} PopulateRecordsetState;
+/* state for json_strip_nulls */
+typedef struct StripnullState{
+ JsonLexContext *lex;
+ StringInfo strval;
+ bool skip_next_null;
+} StripnullState;
+
/* Turn a jsonb object into a record */
static void make_row_from_rec_and_jsonb(Jsonb *element,
PopulateRecordsetState *state);
@@ -2996,3 +3012,184 @@ findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags,
return findJsonbValueFromContainer(container, flags, k);
}
+
+/*
+ * Semantic actions for json_strip_nulls.
+ *
+ * Simply repeat the input on the output unless we encounter
+ * a null object field. State for this is set when the field
+ * is started and reset when the scalar action (which must be next)
+ * is called.
+ */
+
+static void
+sn_object_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '{');
+}
+
+static void
+sn_object_end(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '}');
+}
+
+static void
+sn_array_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '[');
+}
+
+static void
+sn_array_end(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, ']');
+}
+
+static void
+sn_object_field_start (void *state, char *fname, bool isnull)
+{
+ StripnullState *_state = (StripnullState *) state;
+
+ if (isnull)
+ {
+ /*
+ * The next thing must be a scalar or isnull couldn't be true,
+ * so there is no danger of this state being carried down
+ * into a nested object or array. The flag will be reset in the
+ * scalar action.
+ */
+ _state-skip_next_null = true;
+ return;
+ }
+
+ if (_state-strval-data[_state-strval-len - 1] != '{')
+ appendStringInfoCharMacro(_state-strval, ',');
+
+ /*
+ * Unfortunately we don't have the quoted and escaped string any more,
+ * so we have to re-escape it.
+ */
+ escape_json(_state-strval,fname);
+
+ appendStringInfoCharMacro(_state-strval, ':');
+}
+
+static
Re: [HACKERS] strip nulls functions for json and jsonb
On 10/26/2014 03:50 PM, Pavel Stehule wrote:
Hi
I have a question,
what is expected result of null strip of
{a: {b: null, c, null} }
?
Please remember not to top-post.
The above is not legal json, so the answer would be an error.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] strip nulls functions for json and jsonb
On 10/26/2014 04:14 PM, Thom Brown wrote:
On 26 October 2014 20:07, Andrew Dunstan and...@dunslane.net
mailto:and...@dunslane.net wrote:
On 10/26/2014 03:50 PM, Pavel Stehule wrote:
Hi
I have a question,
what is expected result of null strip of
{a: {b: null, c, null} }
?
Please remember not to top-post.
The above is not legal json, so the answer would be an error.
I believe Pavel means:
{a: {b: null, c: null} }
This is the expected result:
andrew=# select json_strip_nulls('{a: {b: null, c: null} }');
json_strip_nulls
--
{a:{}}
(1 row)
It is NOT expected that we replace an empty object with NULL (and then
strip it if it's a field value of an outer level object).
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] strip nulls functions for json and jsonb
On 10/26/2014 04:22 PM, Pavel Stehule wrote:
2014-10-26 21:18 GMT+01:00 Andrew Dunstan and...@dunslane.net
mailto:and...@dunslane.net:
On 10/26/2014 04:14 PM, Thom Brown wrote:
On 26 October 2014 20:07, Andrew Dunstan and...@dunslane.net
mailto:and...@dunslane.net mailto:and...@dunslane.net
mailto:and...@dunslane.net wrote:
On 10/26/2014 03:50 PM, Pavel Stehule wrote:
Hi
I have a question,
what is expected result of null strip of
{a: {b: null, c, null} }
?
Please remember not to top-post.
The above is not legal json, so the answer would be an error.
I believe Pavel means:
{a: {b: null, c: null} }
This is the expected result:
andrew=# select json_strip_nulls('{a: {b: null, c: null} }');
json_strip_nulls
--
{a:{}}
(1 row)
It is NOT expected that we replace an empty object with NULL (and
then strip it if it's a field value of an outer level object).
ok,
This case should be in regress test probably
Patch attached.
cheers
andrew
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7e5bcd9..352b408 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -10716,6 +10716,19 @@ table2-mapping
/programlisting
/entry
/row
+ row
+ entryparaliteraljson_strip_nulls(from_json json)/literal
+ /paraparaliteraljsonb_strip_nulls(from_json jsonb)/literal
+ /para/entry
+ entryparatypejson/type/paraparatypejsonb/type/para/entry
+ entry
+ Returns replaceablefrom_json/replaceable
+ with all object fields that have null values omitted. Other null values
+ are untouched.
+ /entry
+ entryliteraljson_strip_nulls('[{f1:1,f2:null},2,null,3]')/literal/entry
+ entryliteral[{f1:1},2,null,3]/literal/entry
+ /row
/tbody
/tgroup
/table
@@ -10752,6 +10765,16 @@ table2-mapping
/para
/note
+ note
+para
+ If the argument to literaljson_strip_nulls/ contains duplicate
+ field names in any object, the result could be semantically somewhat
+ different, depending on the order in which they occur. This is not an
+ issue for literaljsonb_strip_nulls/ since jsonb values never have
+ duplicate object field names.
+/para
+ /note
+
para
See also xref linkend=functions-aggregate for the aggregate
function functionjson_agg/function which aggregates record
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 2d00dbe..06db3e4 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -105,6 +105,15 @@ static void populate_recordset_object_end(void *state);
static void populate_recordset_array_start(void *state);
static void populate_recordset_array_element_start(void *state, bool isnull);
+/* semantic action functions for json_strip_nulls */
+static void sn_object_start(void *state);
+static void sn_object_end(void *state);
+static void sn_array_start(void *state);
+static void sn_array_end(void *state);
+static void sn_object_field_start (void *state, char *fname, bool isnull);
+static void sn_array_element_start (void *state, bool isnull);
+static void sn_scalar(void *state, char *token, JsonTokenType tokentype);
+
/* worker function for populate_recordset and to_recordset */
static Datum populate_recordset_worker(FunctionCallInfo fcinfo, const char *funcname,
bool have_record_arg);
@@ -225,6 +234,13 @@ typedef struct PopulateRecordsetState
MemoryContext fn_mcxt; /* used to stash IO funcs */
} PopulateRecordsetState;
+/* state for json_strip_nulls */
+typedef struct StripnullState{
+ JsonLexContext *lex;
+ StringInfo strval;
+ bool skip_next_null;
+} StripnullState;
+
/* Turn a jsonb object into a record */
static void make_row_from_rec_and_jsonb(Jsonb *element,
PopulateRecordsetState *state);
@@ -2996,3 +3012,184 @@ findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags,
return findJsonbValueFromContainer(container, flags, k);
}
+
+/*
+ * Semantic actions for json_strip_nulls.
+ *
+ * Simply repeat the input on the output unless we encounter
+ * a null object field. State for this is set when the field
+ * is started and reset when the scalar action (which must be next)
+ * is called.
+ */
+
+static void
+sn_object_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '{');
+}
+
+static void
+sn_object_end(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '}');
+}
+
+static void
+sn_array_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '[');
+}
+
+static void
+sn_array_end(void *state
Re: [HACKERS] idea: allow AS label inside ROW constructor
On 10/23/2014 09:27 AM, Merlin Moncure wrote:
On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
Hi
here is a prototype
postgres=# select row_to_json(row(10 as A, row(30 as c, 20 AS B) as x));
row_to_json
--
{a:10,x:{c:30,b:20}}
(1 row)
postgres=# select row_to_json(row(10, row(30, 20)));
row_to_json
--
{f1:10,f2:{f1:30,f2:20}}
(1 row)
wow -- this is great. I'll take a a look.
Already in 9.4:
andrew=# select
json_build_object('a',10,'x',json_build_object('c',30,'b',20));
json_build_object
{a : 10, x : {c : 30, b : 20}}
(1 row)
So I'm not sure why we want another mechanism unless it's needed in some
other context.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: allow AS label inside ROW constructor
On 10/23/2014 09:57 AM, Florian Pflug wrote:
On Oct23, 2014, at 15:39 , Andrew Dunstan and...@dunslane.net wrote:
On 10/23/2014 09:27 AM, Merlin Moncure wrote:
On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule pavel.steh...@gmail.com wrote:
postgres=# select row_to_json(row(10 as A, row(30 as c, 20 AS B) as x));
row_to_json
--
{a:10,x:{c:30,b:20}}
(1 row)
wow -- this is great. I'll take a a look.
Already in 9.4:
andrew=# select json_build_object('a',10,'x',json_build_object('c',30,'b',20));
json_build_object
{a : 10, x : {c : 30, b : 20}}
(1 row)
So I'm not sure why we want another mechanism unless it's needed in some other
context.
I've wanted to name the field of rows created with ROW() on more than
one occasion, quite independent from whether the resulting row is converted
to JSON or not. And quite apart from usefulness, this is a matter of
orthogonality. If we have named fields in anonymous record types, we should
provide a convenient way of specifying the field names.
So to summarize, I think this is an excellent idea, json_build_object
non-withstanding.
Well, I think we need to see those other use cases. The only use case I
recall seeing involves the already provided case of constructing JSON.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] idea: allow AS label inside ROW constructor
On 10/23/2014 11:36 AM, David G Johnston wrote:
Andrew Dunstan wrote
On 10/23/2014 09:57 AM, Florian Pflug wrote:
On Oct23, 2014, at 15:39 , Andrew Dunstan lt;
andrew@
gt; wrote:
On 10/23/2014 09:27 AM, Merlin Moncure wrote:
On Thu, Oct 23, 2014 at 4:34 AM, Pavel Stehule lt;
pavel.stehule@
gt; wrote:
postgres=# select row_to_json(row(10 as A, row(30 as c, 20 AS B) as
x));
row_to_json
--
{a:10,x:{c:30,b:20}}
(1 row)
wow -- this is great. I'll take a a look.
Already in 9.4:
andrew=# select
json_build_object('a',10,'x',json_build_object('c',30,'b',20));
json_build_object
{a : 10, x : {c : 30, b : 20}}
(1 row)
So I'm not sure why we want another mechanism unless it's needed in some
other context.
I've wanted to name the field of rows created with ROW() on more than
one occasion, quite independent from whether the resulting row is
converted
to JSON or not. And quite apart from usefulness, this is a matter of
orthogonality. If we have named fields in anonymous record types, we
should
provide a convenient way of specifying the field names.
So to summarize, I think this is an excellent idea, json_build_object
non-withstanding.
Well, I think we need to see those other use cases. The only use case I
recall seeing involves the already provided case of constructing JSON.
Even if it simply allows CTE and sibqueries to form anonymous record types
which can then be re-expanded in the outer layer for table-like final output
this feature would be useful. When working with wide tables and using
multiple aggregates and joins being able to avoid specifying individual
columns repeatedly is quite desirable.
It would be especially nice to not have to use as though, if the source
fields are already so named.
You can already name the output of CTEs and in many cases subqueries,
too. Maybe if you or someone gave a concrete example of something you
can't do that this would enable I'd be more convinced.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trailing comma support in SELECT statements
On 10/20/2014 11:59 AM, David E. Wheeler wrote: On Oct 18, 2014, at 7:06 PM, Jim Nasby jim.na...@bluetreble.com wrote: Yes. The only case I can think of where we wouldn't want this is COPY. BTW, this should also apply to delimiters other than commas; for example, some geometry types use ; as a delimiter between points. I don’t think it should apply to the internals of types, necessarily. JSON, for example, always dies on an trailing comma, so should probably stay that way. Well, maybe allow it on JSONB input, but not JSON. Though we perhaps don’t want their behaviors to diverge. The JSON spec is quite clear on this. Leading and trailing commas are not allowed. I would fight tooth and nail not to allow it for json (and by implication jsonb, since they use literally the same parser - in fact we do that precisely so their input grammars can't diverge). cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] json function volatility
Following up something Pavel wrote, I notice that json_agg() and json_object_agg() are both marked as immutable, even though they invoke IO functions, while json_object is marked stable, even though it does not, and can probably be marked as immutable. Mea maxima culpa. I'm not sure what we should do about these things now. Is it a tragedy if we let these escape into the 9.4 release that way? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] narwhal and PGDLLIMPORT
On 10/15/2014 01:53 AM, Craig Ringer wrote: On 10/15/2014 12:53 PM, Noah Misch wrote: Windows Server 2003 isn't even EOL yet. I'd welcome a buildfarm member with that OS and a modern toolchain. It's possible to run multiple buildfarm animals on a single Windows instance, each with a different toolchain. Indeed, and even using the same buildroot. That's been built into the buildfarm for a long time. For example, nightjar and friarbird do this. There's the chance of interactions that can't occur if each SDK is used in isolation on its own machine, but it should be pretty minimal. Make sure each has a distinct base_port. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb generator functions
On 10/15/2014 07:38 AM, Pavel Stehule wrote: 2014-10-13 17:22 GMT+02:00 Andrew Dunstan and...@dunslane.net mailto:and...@dunslane.net: On 10/13/2014 09:37 AM, Andrew Dunstan wrote: On 09/26/2014 04:54 PM, Andrew Dunstan wrote: Here is a patch for the generator and aggregate functions for jsonb that we didn't manage to get done in time for 9.4. They are all equivalents of the similarly names json functions. Included are to_jsonb jsonb_build_object jsonb_build_array jsonb_object jsonb_agg jsonb_object_agg Still to come: documentation. Adding to the next commitfest. Revised patch to fix compiler warnings. And again, initializing an incompletely initialized variable, as found by Pavel Stehule. I checked a code, and I have only two small objection - a name jsonb_object_two_arg is not good - maybe json_object_keys_values ? It's consistent with the existing json_object_two_arg. In all cases I think I kept the names the same except for changing json to jsonb. Note that these _two_arg functions are not visible at the SQL level - they are only visible in the C code. I'm happy to be guided by others in changing or keeping these names. Next: there are no tests for to_jsonb function. Oh, my bad. I'll add some. Thank for the review. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb generator functions
On 10/15/2014 03:54 PM, I wrote: On 10/15/2014 07:38 AM, Pavel Stehule wrote: I checked a code, and I have only two small objection - a name jsonb_object_two_arg is not good - maybe json_object_keys_values ? It's consistent with the existing json_object_two_arg. In all cases I think I kept the names the same except for changing json to jsonb. Note that these _two_arg functions are not visible at the SQL level - they are only visible in the C code. I'm happy to be guided by others in changing or keeping these names. If we really want to change the name of json_object_two_arg, it would probably be best to change it NOW in 9.4 before it gets out into a production release at all. Thoughts? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb generator functions
On 10/15/2014 05:47 PM, Alvaro Herrera wrote: Andrew Dunstan wrote: If we really want to change the name of json_object_two_arg, it would probably be best to change it NOW in 9.4 before it gets out into a production release at all. Doesn't it require initdb? If so, I think it's too late now. Yeah, you're right, it would. OK, forget that. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] narwhal and PGDLLIMPORT
On 10/14/2014 06:44 PM, Dave Page wrote: On Tue, Oct 14, 2014 at 11:38 PM, Tom Lane t...@sss.pgh.pa.us wrote: Alvaro Herrera alvhe...@2ndquadrant.com writes: It seems we left this in broken state. Do we need to do more here to fix narwhal, or do we want to retire narwhal now? Something else? Are we waiting on someone in particular to do something specific? I think we're hoping that somebody will step up and investigate how narwhal's problem might be fixed. However, the machine's owner (Dave) doesn't appear to have the time/interest to do that. That means that our realistic choices are to retire narwhal or revert the linker changes that broke it. Since those linker changes were intended to help expose missing-PGDLLIMPORT bugs, I don't much care for the second alternative. It's a time issue right now I'm afraid (always interested in fixing bugs). However, if fixing it comes down to upgrading the seriously old compiler and toolchain on that box (which frankly is so obsolete, I can't see why anyone would want to use anything like it these days), then I think the best option is to retire it, and replace it with Windows 2012R2 and a modern release of MinGW/Msys which is far more likely to be similar to what someone would want to use at present. Does anyone really think there's a good reason to keep maintaining such an obsolete animal? I do not. I upgraded from this ancient toolset quite a few years ago, and I'm actually thinking of retiring what I replaced it with. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb generator functions
On 09/26/2014 04:54 PM, Andrew Dunstan wrote:
Here is a patch for the generator and aggregate functions for jsonb
that we didn't manage to get done in time for 9.4. They are all
equivalents of the similarly names json functions. Included are
to_jsonb
jsonb_build_object
jsonb_build_array
jsonb_object
jsonb_agg
jsonb_object_agg
Still to come: documentation.
Adding to the next commitfest.
Revised patch to fix compiler warnings.
cheers
andrew
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 2fd87fc..2712761 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -12,11 +12,20 @@
*/
#include postgres.h
+#include miscadmin.h
+#include access/htup_details.h
+#include access/transam.h
+#include catalog/pg_cast.h
+#include catalog/pg_type.h
#include libpq/pqformat.h
#include utils/builtins.h
+#include utils/datetime.h
+#include utils/lsyscache.h
#include utils/json.h
#include utils/jsonapi.h
#include utils/jsonb.h
+#include utils/syscache.h
+#include utils/typcache.h
typedef struct JsonbInState
{
@@ -24,6 +33,23 @@ typedef struct JsonbInState
JsonbValue *res;
} JsonbInState;
+/* unlike with json categories, we need to treat json and jsonb differently */
+typedef enum /* type categories for datum_to_jsonb */
+{
+ JSONBTYPE_NULL,/* null, so we didn't bother to identify */
+ JSONBTYPE_BOOL,/* boolean (built-in types only) */
+ JSONBTYPE_NUMERIC, /* numeric (ditto) */
+ JSONBTYPE_TIMESTAMP, /* we use special formatting for timestamp */
+ JSONBTYPE_TIMESTAMPTZ, /* ... and timestamptz */
+ JSONBTYPE_JSON,/* JSON */
+ JSONBTYPE_JSONB, /* JSONB */
+ JSONBTYPE_ARRAY, /* array */
+ JSONBTYPE_COMPOSITE, /* composite */
+ JSONBTYPE_JSONCAST, /* something with an explicit cast to JSON */
+ JSONBTYPE_JSONBCAST, /* something with an explicit cast to JSONB */
+ JSONBTYPE_OTHER/* all else */
+} JsonbTypeCategory;
+
static inline Datum jsonb_from_cstring(char *json, int len);
static size_t checkStringLen(size_t len);
static void jsonb_in_object_start(void *pstate);
@@ -33,6 +59,22 @@ static void jsonb_in_array_end(void *pstate);
static void jsonb_in_object_field_start(void *pstate, char *fname, bool isnull);
static void jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal);
static void jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid);
+static void composite_to_jsonb(Datum composite, JsonbInState *result);
+static void array_dim_to_jsonb(JsonbInState *result, int dim, int ndims, int *dims,
+ Datum *vals, bool *nulls, int *valcount,
+ JsonbTypeCategory tcategory, Oid outfuncoid);
+static void array_to_jsonb_internal(Datum array, JsonbInState *result);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid);
+static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
+ JsonbTypeCategory tcategory, Oid outfuncoid,
+ bool key_scalar);
+static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
+ Oid val_type, bool key_scalar);
/*
* jsonb type input function
@@ -462,3 +504,1282 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
return out-data;
}
+
+
+/*
+ * Determine how we want to render values of a given type in datum_to_jsonb.
+ *
+ * Given the datatype OID, return its JsonbTypeCategory, as well as the type's
+ * output function OID. If the returned category is JSONBTYPE_CAST, we
+ * return the OID of the type-JSON cast function instead.
+ */
+static void
+jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid)
+{
+ bool typisvarlena;
+
+ /* Look through any domain */
+ typoid = getBaseType(typoid);
+
+ /* We'll usually need to return the type output function */
+ getTypeOutputInfo(typoid, outfuncoid, typisvarlena);
+
+ /* Check for known types */
+ switch (typoid)
+ {
+ case BOOLOID:
+ *tcategory = JSONBTYPE_BOOL;
+ break;
+
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ *tcategory = JSONBTYPE_NUMERIC;
+ break;
+
+ case TIMESTAMPOID:
+ *tcategory = JSONBTYPE_TIMESTAMP;
+ break;
+
+ case TIMESTAMPTZOID:
+ *tcategory = JSONBTYPE_TIMESTAMPTZ;
+ break;
+
+ case JSONBOID:
+ *tcategory = JSONBTYPE_JSONB;
+ break;
+
+ case JSONOID:
+ *tcategory = JSONBTYPE_JSON;
+ break;
+
+ default:
+ /* Check for arrays and composites */
+ if (OidIsValid(get_element_type(typoid)))
+*tcategory = JSONBTYPE_ARRAY;
+ else if (type_is_rowtype(typoid))
+*tcategory = JSONBTYPE_COMPOSITE;
+ else
+ {
+/* It's probably the general case ... */
+*tcategory = JSONBTYPE_OTHER;
+
+/*
+ * but let's look for a cast to json or jsonb, if it's not
+ * built
Re: [HACKERS] JsonbValue to Jsonb conversion
On 10/13/2014 06:41 AM, Pavel Stehule wrote: Hi I am working on review of this patch. The patch attached to the message you are replying to was never intended to be reviewed. It was only given by way of illustration of a technique. The original patch to be reviewed is on the message http://www.postgresql.org/message-id/5425d277.4030...@dunslane.net as shown on the commitfest app. I have just submitted a revised patch to fix the compiler warnings you complained of, at http://www.postgresql.org/message-id/543bd598.4020...@dunslane.net I have not found any segfaults in the regression tests. And please don't top-post on the PostgreSQL lists. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb generator functions
On 10/13/2014 09:37 AM, Andrew Dunstan wrote:
On 09/26/2014 04:54 PM, Andrew Dunstan wrote:
Here is a patch for the generator and aggregate functions for jsonb
that we didn't manage to get done in time for 9.4. They are all
equivalents of the similarly names json functions. Included are
to_jsonb
jsonb_build_object
jsonb_build_array
jsonb_object
jsonb_agg
jsonb_object_agg
Still to come: documentation.
Adding to the next commitfest.
Revised patch to fix compiler warnings.
And again, initializing an incompletely initialized variable, as found
by Pavel Stehule.
cheers
andrew
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 2fd87fc..33a19be 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -12,11 +12,20 @@
*/
#include postgres.h
+#include miscadmin.h
+#include access/htup_details.h
+#include access/transam.h
+#include catalog/pg_cast.h
+#include catalog/pg_type.h
#include libpq/pqformat.h
#include utils/builtins.h
+#include utils/datetime.h
+#include utils/lsyscache.h
#include utils/json.h
#include utils/jsonapi.h
#include utils/jsonb.h
+#include utils/syscache.h
+#include utils/typcache.h
typedef struct JsonbInState
{
@@ -24,6 +33,23 @@ typedef struct JsonbInState
JsonbValue *res;
} JsonbInState;
+/* unlike with json categories, we need to treat json and jsonb differently */
+typedef enum /* type categories for datum_to_jsonb */
+{
+ JSONBTYPE_NULL,/* null, so we didn't bother to identify */
+ JSONBTYPE_BOOL,/* boolean (built-in types only) */
+ JSONBTYPE_NUMERIC, /* numeric (ditto) */
+ JSONBTYPE_TIMESTAMP, /* we use special formatting for timestamp */
+ JSONBTYPE_TIMESTAMPTZ, /* ... and timestamptz */
+ JSONBTYPE_JSON,/* JSON */
+ JSONBTYPE_JSONB, /* JSONB */
+ JSONBTYPE_ARRAY, /* array */
+ JSONBTYPE_COMPOSITE, /* composite */
+ JSONBTYPE_JSONCAST, /* something with an explicit cast to JSON */
+ JSONBTYPE_JSONBCAST, /* something with an explicit cast to JSONB */
+ JSONBTYPE_OTHER/* all else */
+} JsonbTypeCategory;
+
static inline Datum jsonb_from_cstring(char *json, int len);
static size_t checkStringLen(size_t len);
static void jsonb_in_object_start(void *pstate);
@@ -33,6 +59,22 @@ static void jsonb_in_array_end(void *pstate);
static void jsonb_in_object_field_start(void *pstate, char *fname, bool isnull);
static void jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal);
static void jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid);
+static void composite_to_jsonb(Datum composite, JsonbInState *result);
+static void array_dim_to_jsonb(JsonbInState *result, int dim, int ndims, int *dims,
+ Datum *vals, bool *nulls, int *valcount,
+ JsonbTypeCategory tcategory, Oid outfuncoid);
+static void array_to_jsonb_internal(Datum array, JsonbInState *result);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid);
+static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
+ JsonbTypeCategory tcategory, Oid outfuncoid,
+ bool key_scalar);
+static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
+ Oid val_type, bool key_scalar);
/*
* jsonb type input function
@@ -462,3 +504,1284 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
return out-data;
}
+
+
+/*
+ * Determine how we want to render values of a given type in datum_to_jsonb.
+ *
+ * Given the datatype OID, return its JsonbTypeCategory, as well as the type's
+ * output function OID. If the returned category is JSONBTYPE_CAST, we
+ * return the OID of the type-JSON cast function instead.
+ */
+static void
+jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid)
+{
+ bool typisvarlena;
+
+ /* Look through any domain */
+ typoid = getBaseType(typoid);
+
+ /* We'll usually need to return the type output function */
+ getTypeOutputInfo(typoid, outfuncoid, typisvarlena);
+
+ /* Check for known types */
+ switch (typoid)
+ {
+ case BOOLOID:
+ *tcategory = JSONBTYPE_BOOL;
+ break;
+
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ *tcategory = JSONBTYPE_NUMERIC;
+ break;
+
+ case TIMESTAMPOID:
+ *tcategory = JSONBTYPE_TIMESTAMP;
+ break;
+
+ case TIMESTAMPTZOID:
+ *tcategory = JSONBTYPE_TIMESTAMPTZ;
+ break;
+
+ case JSONBOID:
+ *tcategory = JSONBTYPE_JSONB;
+ break;
+
+ case JSONOID:
+ *tcategory = JSONBTYPE_JSON;
+ break;
+
+ default:
+ /* Check for arrays and composites */
+ if (OidIsValid(get_element_type(typoid)))
+*tcategory = JSONBTYPE_ARRAY;
+ else if (type_is_rowtype(typoid))
+*tcategory = JSONBTYPE_COMPOSITE;
+ else
+ {
+/* It's probably the general case
Re: [HACKERS] Missing IPv6 for pgbuildfarm.org
On 07/10/2014 09:15 AM, Andrew Dunstan wrote: On 07/02/2014 05:08 AM, Torsten Zuehlsdorff wrote: Hello, i've tried to setup a FreeBSD 10 machine as buildfarm-member. But it's an IPv6 only machine and there is no IPv6 for the homepage. Can anyone add support for IPv6 to it? I'm looking into this. This should now be working. The postgresql.org DNS maintainers might like to add this address to their entries for the server, too: 2604:da00:2:2:5054:ff:fe49:e8e cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Context lenses to set/get values in json values.
On 10/08/2014 04:38 AM, Paweł Cesar Sanjuan Szklarz wrote:
Hello.
I am interested in the json type on postgresql. I would like to
implement additional operations on the json structure that may
extract/insert table like information from the json tree structure.
I have a implementation on javascript that shows this type of
operations. You can see examples in this page
https://github.com/paweld2/eelnss/wiki
Following the examples in the previous page, it may by possible to
implement a function similar to json_populate_record to extract
multiple records from a single json value, for example:
select * from json_populate_records_with_clen(null::myrowtype_users,
'app.users.{:uID}.(email,data.name http://data.name,isActive)', '...
nested json value ...')
may return
uID | email | name | isActive
--
u1 | ad...@pmsoft.eu mailto:ad...@pmsoft.eu| administrator
| true
u2 | nor...@pmsoft.eu mailto:nor...@pmsoft.eu | user
| true
u3 | testu...@pmsoft.eu mailto:testu...@pmsoft.eu | testUser
| false
Also, assuming that we have a table User as above (uID, email, name,
isActive), with context lenses it is very simple to map the table to a
json object. I assume that a similar api to table_to_xml,query_to_xml
may be provided:
table_to_json( Person, 'app.users.{:uID}.(email,data.name
http://data.name,isActive)');
query_to_json( 'select * from Person where ... ',
'app.users.{:uID}.(email,data.name http://data.name,isActive)');
I don't know the details about the integration of functions/operators
to sql queries, but because context lenses maps between tables and
tree objects, it may be possible to use a column json value as a
separate table in the queries. Assume the table
create table Person {
pID Integer
address Json
}
then it may be possible to query:
select * from Person as P left join ( select * from
json_populate_records_with_clen(null::addressType,
'addres.(street.number, street.local,city.code,city.name
http://city.name)', P.address);
A final api for such functions needs to be defined. If such functions
may be usefull, I can try to prepare a implementation in postgres base
code.
I don't think we need to import Mongo type notation here. But there is
probably a good case for some functions like:
json_table_agg(anyrecord) - json
which would work like json_agg() but would return an array of arrays
instead of an array of objects. The caller would be assumed to know
which field is which in the array. That should take care of both the
table_to_json and query_to_json suggestions above.
In the other direction, we could have something like:
json_populate_recordset_from_table(base anyrecord, fields text[],
jsontable json) - setof record
where jsontable is an array of arrays of values and fields is a
corresponding array of field names.
I'm not sure how mainstream any of this is. Maybe an extension would be
more appropriate?
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Context lenses to set/get values in json values.
On 10/08/2014 12:13 PM, Paweł Cesar Sanjuan Szklarz wrote: I don't think we need to import Mongo type notation here. But there is probably a good case for some functions like: json_table_agg(anyrecord) - json which would work like json_agg() but would return an array of arrays instead of an array of objects. The caller would be assumed to know which field is which in the array. That should take care of both the table_to_json and query_to_json suggestions above. In the other direction, we could have something like: json_populate_recordset_from_table(base anyrecord, fields text[], jsontable json) - setof record where jsontable is an array of arrays of values and fields is a corresponding array of field names. I'm not sure how mainstream any of this is. Maybe an extension would be more appropriate? Hello. My personal interest is to send updates to a single json value in the server. Which is the best way to make a update to a json value in postgres without a full update of the already stored value?? the - operator extract a internal value, but to update the value I don't see any operator. I was not familiar with the extensions, but it looks like the best way to start is to create a extension with possible implementations of new functions. I will do so. In my project I considered to use mongo, but in my case the core part of the model match perfectly a relational schema. I have some leaf concepts that will change frequently, and to avoid migrations I store that information in a json value. To make changes in such leaf values I would like to have a context lenses like api in the server. I will start with some toy extension and try to feel if this make sense. There is work already being done on providing update operations. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Context lenses to set/get values in json values.
On 10/08/2014 02:04 PM, Thom Brown wrote: There is work already being done on providing update operations. I've been looking out for that. Has there been a discussion on how that would look yet that you could point me to? https://github.com/erthalion/jsonbx Note that a) it's an extension, and b) it's jsonb only. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/07/2014 12:15 AM, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Oct 6, 2014 at 8:15 PM, Peter Eisentraut pete...@gmx.net wrote: The TAP tests are arguably already much easier to debug than pg_regress ever was. Well, maybe. I wasn't able, after about 5 minutes of searching, to locate either a log file with details of the failure or the code that revealed what the test, the expected result, and the actual result were. It's possible that all that information is there and I just don't know where to look; it took me a while to learn where the various logs (postmaster.log, initdb.log, results) left behind by pg_regress were, too. If that information is not there, then I'd say it's not easier to debug. If it is and I don't know where to look ... well then I just need to get educated. The given case seemed pretty opaque to me too. Could we maybe have some documentation about how to debug TAP failures? Or in other words, if they're arguably easier to debug, how about presenting that argument? Also to the point: does the buildfarm script know how to collect the information needed to debug a TAP failure? No. In fact, it doesn't yet know how to run those tests. That's on my TODO list. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] TAP test breakage on MacOS X
On 10/07/2014 09:53 AM, Andrew Dunstan wrote: On 10/07/2014 12:15 AM, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: On Mon, Oct 6, 2014 at 8:15 PM, Peter Eisentraut pete...@gmx.net wrote: The TAP tests are arguably already much easier to debug than pg_regress ever was. Well, maybe. I wasn't able, after about 5 minutes of searching, to locate either a log file with details of the failure or the code that revealed what the test, the expected result, and the actual result were. It's possible that all that information is there and I just don't know where to look; it took me a while to learn where the various logs (postmaster.log, initdb.log, results) left behind by pg_regress were, too. If that information is not there, then I'd say it's not easier to debug. If it is and I don't know where to look ... well then I just need to get educated. The given case seemed pretty opaque to me too. Could we maybe have some documentation about how to debug TAP failures? Or in other words, if they're arguably easier to debug, how about presenting that argument? Also to the point: does the buildfarm script know how to collect the information needed to debug a TAP failure? No. In fact, it doesn't yet know how to run those tests. That's on my TODO list. OK, I have a preliminary cut at adding these tests to the client. See http://www.pgbuildfarm.org/cgi-bin/show_stage_log.pl?nm=crakedt=2014-10-07%2015%3A38%3A04stg=bin-check for an example run. The patch is at https://github.com/PGBuildFarm/client-code/commit/6f644b779c90b16f96e4454b807e804bde48b563 I don't much like the idea of doing an install/initdb/start for every directory in src/bin, though. Can't we at least manage a single installation directory for all these? Also I notice that the tests remove their data directories. That could make collecting any diagnosis data more difficult. Right now, I have no idea what I'm looking for anyway. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Trailing comma support in SELECT statements
On 10/03/2014 12:20 PM, Bruce Momjian wrote: On Sun, Sep 28, 2014 at 01:42:46PM +0200, Bogdan Pilch wrote: Hi, I have created a small patch to postgres source (in particular the psql part of it) that accepts trailing comma at the end of list in SELECT statement. The idea is to be able to say both (with the same result): SELECT a, b, c from t; SELECT a, b, c, from t; Attached you can find a patch containing regression test (incorporated into the serial_schedule). My patch is relative to origin/REL9_4_STABLE branch as that is the one I started from. My plea is to have this change merged into the main stream so that it becomes available in upcoming releases. This modification does not require any interaction with user. It does not create any backward compatibility issues. Interesting --- I know some languages allow trailing delimiters, like Perl and Javascript. Could this mask query errors? Does any other database accept this? Seems this would need to be done in many other places, like UPDATE, but let's first decide if we want this. FYI, it is usually better to discuss a feature before showing a patch. Javascript might accept it, but it's not valid JSON. The case for doing it is that then you can easily comment out any entry at all in a select list: select foo as f1, bar as f2, -- baz as f3, from blurfl cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] strip nulls functions for json and jsonb
As discussed recently, here is an undocumented patch for
json_strip_nulls and jsonb_strip_nulls.
cheers
andrew
diff --git a/src/backend/utils/adt/jsonfuncs.c b/src/backend/utils/adt/jsonfuncs.c
index 2d00dbe..e9636d8 100644
--- a/src/backend/utils/adt/jsonfuncs.c
+++ b/src/backend/utils/adt/jsonfuncs.c
@@ -105,6 +105,15 @@ static void populate_recordset_object_end(void *state);
static void populate_recordset_array_start(void *state);
static void populate_recordset_array_element_start(void *state, bool isnull);
+/* semantic action functions for json_strip_nulls */
+static void sn_object_start(void *state);
+static void sn_object_end(void *state);
+static void sn_array_start(void *state);
+static void sn_array_end(void *state);
+static void sn_object_field_start (void *state, char *fname, bool isnull);
+static void sn_array_element_start (void *state, bool isnull);
+static void sn_scalar(void *state, char *token, JsonTokenType tokentype);
+
/* worker function for populate_recordset and to_recordset */
static Datum populate_recordset_worker(FunctionCallInfo fcinfo, const char *funcname,
bool have_record_arg);
@@ -225,6 +234,13 @@ typedef struct PopulateRecordsetState
MemoryContext fn_mcxt; /* used to stash IO funcs */
} PopulateRecordsetState;
+/* state for json_strip_nulls */
+typedef struct StripnullState{
+ JsonLexContext *lex;
+ StringInfo strval;
+ bool skip_next_null;
+} StripnullState;
+
/* Turn a jsonb object into a record */
static void make_row_from_rec_and_jsonb(Jsonb *element,
PopulateRecordsetState *state);
@@ -2996,3 +3012,169 @@ findJsonbValueFromContainerLen(JsonbContainer *container, uint32 flags,
return findJsonbValueFromContainer(container, flags, k);
}
+
+static void
+sn_object_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '{');
+}
+
+static void
+sn_object_end(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '}');
+}
+
+static void
+sn_array_start(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, '[');
+}
+
+static void
+sn_array_end(void *state)
+{
+ StripnullState *_state = (StripnullState *) state;
+ appendStringInfoCharMacro(_state-strval, ']');
+}
+
+static void
+sn_object_field_start (void *state, char *fname, bool isnull)
+{
+ StripnullState *_state = (StripnullState *) state;
+
+ if (isnull)
+ {
+ _state-skip_next_null = true;
+ return;
+ }
+
+ if (_state-strval-data[_state-strval-len - 1] != '{')
+ appendStringInfoCharMacro(_state-strval, ',');
+
+ /*
+ * Unfortunately we don't have the quoted and escaped string any more,
+ * so we have to re-escape it.
+ */
+ escape_json(_state-strval,fname);
+
+ appendStringInfoCharMacro(_state-strval, ':');
+}
+
+static void
+sn_array_element_start (void *state, bool isnull)
+{
+ StripnullState *_state = (StripnullState *) state;
+
+ if (_state-strval-data[_state-strval-len - 1] != '[')
+ appendStringInfoCharMacro(_state-strval, ',');
+}
+
+static void
+sn_scalar(void *state, char *token, JsonTokenType tokentype)
+{
+ StripnullState *_state = (StripnullState *) state;
+
+ if (_state-skip_next_null)
+ {
+ _state-skip_next_null = false;
+ return;
+ }
+
+ if (tokentype == JSON_TOKEN_STRING)
+ escape_json(_state-strval, token);
+ else
+ appendStringInfoString(_state-strval, token);
+}
+
+/*
+ * SQL function json_strip_nulls(json) - json
+ */
+Datum
+json_strip_nulls(PG_FUNCTION_ARGS)
+{
+ text *json = PG_GETARG_TEXT_P(0);
+ StripnullState *state;
+ JsonLexContext *lex;
+ JsonSemAction *sem;
+
+ lex = makeJsonLexContext(json, true);
+ state = palloc0(sizeof(StripnullState));
+ sem = palloc0(sizeof(JsonSemAction));
+
+ state-strval = makeStringInfo();
+ state-skip_next_null = false;
+ state-lex = lex;
+
+ sem-semstate = (void *) state;
+ sem-object_start = sn_object_start;
+ sem-object_end = sn_object_end;
+ sem-array_start = sn_array_start;
+ sem-array_end = sn_array_end;
+ sem-scalar = sn_scalar;
+ sem-array_element_start = sn_array_element_start;
+ sem-object_field_start = sn_object_field_start;
+
+ pg_parse_json(lex, sem);
+
+ PG_RETURN_TEXT_P(cstring_to_text_with_len(state-strval-data,
+ state-strval-len));
+
+}
+
+/*
+ * SQL function jsonb_strip_nulls(jsonb) - jsonb
+ */
+Datum
+jsonb_strip_nulls(PG_FUNCTION_ARGS)
+{
+ Jsonb * jb = PG_GETARG_JSONB(0);
+ JsonbIterator *it;
+ JsonbParseState *parseState = NULL;
+ JsonbValue *res;
+ int type;
+ JsonbValue v,k;
+ bool last_was_key = false;
+
+ if (JB_ROOT_IS_SCALAR(jb))
+ PG_RETURN_POINTER(jb);
+
+ it = JsonbIteratorInit(jb-root);
+
+ while ((type = JsonbIteratorNext(it, v, false)) != WJB_DONE)
+ {
+ Assert( ! (type == WJB_KEY last_was_key));
+
+ if (type == WJB_KEY)
+ {
+ /* stash the key until we know if it has a null value */
+ k = v;
+ last_was_key = true;
+ continue;
+ }
+
+ if (last_was_key)
+ {
+ /* skip this
Re: [HACKERS] json (b) and null fields
On 09/29/2014 10:38 AM, Robert Haas wrote: On Sat, Sep 27, 2014 at 11:00 PM, Andrew Dunstan and...@dunslane.net wrote: On 09/27/2014 10:52 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 09/27/2014 06:27 PM, Tom Lane wrote: So my vote is for a separate function and no optional arguments. You mean like row_to_json_no_nulls() and json_agg_no_nulls()? I thought you were proposing that we should revert the committed patch lock-stock-n-barrel, and instead invent json_strip_null_fields(). That's instead, not in addition to. Even if you weren't saying that exactly, that's where my vote goes. I was just exploring alternatives. But I think that's where my vote goes too. +1. I am sort of surprised that anyone things this null-stripping behavior is something that ought to be part of core PostgreSQL instead of, I don't know, relegated to an extension somewhere far from the bright center of the galaxy. I've never heard of that requirement anywhere but here. But if other people feel we should have it, that's fine - but certainly making it a separate function makes a lot more sense. Fetching data from a missing field should return null, so stripping null fields can give you in effect the same result, depending on what your app does, without actually having to store the key and the null. And if we're producing JSON for an external processor from a table that is fairly sparsely populated, this could reduce the size of the JSON enormously. That said, doing this as an extension is probably a good way to go, as I suggested upthread, since we could then make it available for 9.4, rather than making people wait until 9.5. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] json (b) and null fields
On 09/29/2014 04:32 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 09/29/2014 04:14 PM, Tom Lane wrote: More to the point, the way to fix any concerns about double parsing is to create row_to_jsonb(), not to plaster a bunch of options on row_to_json(). row_to_jsonb would be completely redundant with to_jsonb() in my recent patch. Right, which raises the question of whether we shouldn't just be deprecating both array_to_json() and row_to_json()... And I don't want to add options like this there for the same reasons I didn't want them in row_to_json(). Agreed. IMO the place to have put the pretty functionality was in some sort of json-to-text conversion function; it never belonged in an input conversion function. Yes, if we had pretty printing functions we could deprecate and eventually remove row_to_json and array_to_json. That's probably worth putting on the TODO list. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] json (b) and null fields
On 09/27/2014 08:00 AM, Stephen Frost wrote: Andrew, all, * Andrew Dunstan (and...@dunslane.net) wrote: I should have been paying a bit more attention to the recent work on adding an ignore_nulls option to row_to_json(). Here are some belated thought. I apologize to Pavel and Stephen for not having commented earlier. No problem at all and thanks for continuing to think about it! We certainly still have quite a bit of time til 9.5 to get this right. I think this is really a bandaid, and it will fail to catch lots of cases. Several examples: As discussed on IRC- I agree. I tend to think of JSON objects as relatively simple hstore-like structures and so hadn't considered the complex structure case (as I'm guessing Pavel hadn't either). I think a much more comprehensive solution would be preferable. What I have in mind is something like json_strip_null_fields(json) - json and a similar function for jsonb. Right, this makes sense to me. These would operate recursively. There is a downside, in that they would be required to reprocess the json/jsonb. But adding an option like this to all the json generator functions would be seriously ugly, especially since they are mostly aggregate functions or variadic functions. At least in the jsonb case the cost of reprocessing is likely to be fairly low. Yeah, I don't see adding this option to all json generator functions as making a lot of sense but rather just to the select few things which it really makes sense for and then having a function which can be used by users to do the same for results from other operations. I guess I'm questioning the wisdom of keeping it for row_to_json given that it doesn't operate recursively anyway (and making it do so would be difficult and ugly). The counter argument for this is that nested composites and arrays of composites are relatively rare in records, so providing a fast non-recursive stripping of nulls for the common case is reasonable. If we're going to keep this, I think we also need to provide it (non-recursive) for json_agg via an optional second argument. This should be a fairly simple change: just steer the result via composite_to_json if it's a record, rather than to datum_to_json. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] json (b) and null fields
On 09/27/2014 06:27 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 09/27/2014 08:00 AM, Stephen Frost wrote: Yeah, I don't see adding this option to all json generator functions as making a lot of sense but rather just to the select few things which it really makes sense for and then having a function which can be used by users to do the same for results from other operations. I guess I'm questioning the wisdom of keeping it for row_to_json given that it doesn't operate recursively anyway (and making it do so would be difficult and ugly). IMO, adding it to row_to_json was really ugly to start with, independently of whether it's difficult or not. That function had one too many optional arguments already, and in its current form it's nothing but a loaded gun pointed at users' feet. (Quick, which bool argument is which?) If we're going to keep this, I think we also need to provide it (non-recursive) for json_agg via an optional second argument. This should be a fairly simple change: just steer the result via composite_to_json if it's a record, rather than to datum_to_json. Unfortunately, any such thing will fall foul of an established project policy. I quote the opr_sanity regression test that will complain: -- Check that there are not aggregates with the same name and different -- numbers of arguments. While not technically wrong, we have a project policy -- to avoid this because it opens the door for confusion in connection with -- ORDER BY: novices frequently put the ORDER BY in the wrong place. -- See the fate of the single-argument form of string_agg() for history. So my vote is for a separate function and no optional arguments. You mean like row_to_json_no_nulls() and json_agg_no_nulls()? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] json (b) and null fields
I should have been paying a bit more attention to the recent work on adding an ignore_nulls option to row_to_json(). Here are some belated thought. I apologize to Pavel and Stephen for not having commented earlier. I think this is really a bandaid, and it will fail to catch lots of cases. Several examples: * it doesn't apply recursively, so if the row has a nested composite, or an array of composites, none of those will have nulls ignored, only the top level will. * it doesn't apply to other json generators, notably json_agg(). That's a very big omission. * it does nothing to allow us to strip nulls from existing json/jsonb data. I think a much more comprehensive solution would be preferable. What I have in mind is something like json_strip_null_fields(json) - json and a similar function for jsonb. These would operate recursively. There is a downside, in that they would be required to reprocess the json/jsonb. But adding an option like this to all the json generator functions would be seriously ugly, especially since they are mostly aggregate functions or variadic functions. At least in the jsonb case the cost of reprocessing is likely to be fairly low. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] jsonb generator functions
Here is a patch for the generator and aggregate functions for jsonb that
we didn't manage to get done in time for 9.4. They are all equivalents
of the similarly names json functions. Included are
to_jsonb
jsonb_build_object
jsonb_build_array
jsonb_object
jsonb_agg
jsonb_object_agg
Still to come: documentation.
Adding to the next commitfest.
cheers
andrew
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 2fd87fc..6c23b75 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -12,11 +12,20 @@
*/
#include postgres.h
+#include miscadmin.h
+#include access/htup_details.h
+#include access/transam.h
+#include catalog/pg_cast.h
+#include catalog/pg_type.h
#include libpq/pqformat.h
#include utils/builtins.h
+#include utils/datetime.h
+#include utils/lsyscache.h
#include utils/json.h
#include utils/jsonapi.h
#include utils/jsonb.h
+#include utils/syscache.h
+#include utils/typcache.h
typedef struct JsonbInState
{
@@ -24,6 +33,23 @@ typedef struct JsonbInState
JsonbValue *res;
} JsonbInState;
+/* unlike with json categories, we need to treat json and jsonb differently */
+typedef enum /* type categories for datum_to_jsonb */
+{
+ JSONBTYPE_NULL,/* null, so we didn't bother to identify */
+ JSONBTYPE_BOOL,/* boolean (built-in types only) */
+ JSONBTYPE_NUMERIC, /* numeric (ditto) */
+ JSONBTYPE_TIMESTAMP, /* we use special formatting for timestamp */
+ JSONBTYPE_TIMESTAMPTZ, /* ... and timestamptz */
+ JSONBTYPE_JSON,/* JSON */
+ JSONBTYPE_JSONB, /* JSONB */
+ JSONBTYPE_ARRAY, /* array */
+ JSONBTYPE_COMPOSITE, /* composite */
+ JSONBTYPE_JSONCAST, /* something with an explicit cast to JSON */
+ JSONBTYPE_JSONBCAST, /* something with an explicit cast to JSONB */
+ JSONBTYPE_OTHER/* all else */
+} JsonbTypeCategory;
+
static inline Datum jsonb_from_cstring(char *json, int len);
static size_t checkStringLen(size_t len);
static void jsonb_in_object_start(void *pstate);
@@ -33,6 +59,22 @@ static void jsonb_in_array_end(void *pstate);
static void jsonb_in_object_field_start(void *pstate, char *fname, bool isnull);
static void jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal);
static void jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid);
+static void composite_to_jsonb(Datum composite, JsonbInState *result);
+static void array_dim_to_jsonb(JsonbInState *result, int dim, int ndims, int *dims,
+ Datum *vals, bool *nulls, int *valcount,
+ JsonbTypeCategory tcategory, Oid outfuncoid);
+static void array_to_jsonb_internal(Datum array, JsonbInState *result);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid);
+static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
+ JsonbTypeCategory tcategory, Oid outfuncoid,
+ bool key_scalar);
+static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
+ Oid val_type, bool key_scalar);
/*
* jsonb type input function
@@ -462,3 +504,1278 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
return out-data;
}
+
+
+/*
+ * Determine how we want to render values of a given type in datum_to_jsonb.
+ *
+ * Given the datatype OID, return its JsonbTypeCategory, as well as the type's
+ * output function OID. If the returned category is JSONBTYPE_CAST, we
+ * return the OID of the type-JSON cast function instead.
+ */
+static void
+jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory * tcategory,
+ Oid *outfuncoid)
+{
+ bool typisvarlena;
+
+ /* Look through any domain */
+ typoid = getBaseType(typoid);
+
+ /* We'll usually need to return the type output function */
+ getTypeOutputInfo(typoid, outfuncoid, typisvarlena);
+
+ /* Check for known types */
+ switch (typoid)
+ {
+ case BOOLOID:
+ *tcategory = JSONBTYPE_BOOL;
+ break;
+
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+ *tcategory = JSONBTYPE_NUMERIC;
+ break;
+
+ case TIMESTAMPOID:
+ *tcategory = JSONBTYPE_TIMESTAMP;
+ break;
+
+ case TIMESTAMPTZOID:
+ *tcategory = JSONBTYPE_TIMESTAMPTZ;
+ break;
+
+ case JSONBOID:
+ *tcategory = JSONBTYPE_JSONB;
+ break;
+
+ case JSONOID:
+ *tcategory = JSONBTYPE_JSON;
+ break;
+
+ default:
+ /* Check for arrays and composites */
+ if (OidIsValid(get_element_type(typoid)))
+*tcategory = JSONBTYPE_ARRAY;
+ else if (type_is_rowtype(typoid))
+*tcategory = JSONBTYPE_COMPOSITE;
+ else
+ {
+/* It's probably the general case ... */
+*tcategory = JSONBTYPE_OTHER;
+
+/*
+ * but let's look for a cast to json or jsonb, if it's not
+ * built-in
+ */
+if (typoid = FirstNormalObjectId)
+{
+ HeapTuple tuple;
+
+ tuple =
Re: [HACKERS] jsonb generator functions
On 09/26/2014 05:00 PM, Peter Geoghegan wrote: On Fri, Sep 26, 2014 at 1:54 PM, Andrew Dunstan and...@dunslane.net wrote: Here is a patch for the generator and aggregate functions for jsonb that we didn't manage to get done in time for 9.4. That's cool, but I hope someone revisits adding a concatenate operator. That's a biggest omission IMHO. I'm not going to have time for that. This patch is the work that I have publicly promised to do. Dmitry Dolgov is in fact working on jsonb_concat(), and several other utility functions. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] json_object_agg return on no rows
Probably due to an oversight on my part, json_object_agg currently returns a json object with no fields rather than NULL, which the docs say it will, and which would be consistent with all other aggregates except count(). I don't think we ever discussed this, so it's probably just a slip up. I'm going to change it to return NULL in this case unless there is some objection. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] proposal: rounding up time value less than its unit.
On 09/23/2014 10:29 AM, Tom Lane wrote: Robert Haas robertmh...@gmail.com writes: Three people have voted for making it an *error* to supply a value that needs to be rounded, instead of changing the rounding behavior. Votes or no votes, that's a horrible idea; it breaks the design goal that users shouldn't need to remember the precise unit size when making postgresql.conf entries. And I'm not sure what votes you're counting, anyway. People's opinions have changed as the discussion proceeded ... I don't think I've weighed in on this, and yes, I'm very late to the party. The round away from zero suggestion seemed the simplest and most easily understood rule to me. As Tom, I think, remarked, if that seems silly because 1 second gets rounded up to 1 hour or whatever, then we've chosen the wrong units in the first place. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] RLS feature has been committed
On 09/23/2014 11:19 AM, Robert Haas wrote: On Tue, Sep 23, 2014 at 11:16 AM, Dave Page dp...@pgadmin.org wrote: Regardless of what Robert may feel, review should only generally be *expected* during a commitfest, but it can be done at any time. Committers are free to commit at any time. The process was never intended to restrict what committers do or when - in fact when I introduced the process to -hackers, it was specifically worded to say that developers are strongly encouraged to take part in the commitfest reviews, but not forced to, and may continue to work on their patches as they see fit. So, just to be clear, you're saying that committers are free to commit things even when other community members (who may themselves be committers) ask them not to? I don't think anyone is suggesting that. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] JsonbValue to Jsonb conversion
On 09/23/2014 12:23 PM, Dmitry Dolgov wrote:
Hi all,
I'm faced with some troubles about the jsonb implementation, and I
hope I'll get little advice =)
If I understand correctly, an abstract function for jsonb modification
should have the following stages:
Jsonb - JsonbValue - Modification - JsonbValue - Jsonb
One can convert the *JsonbValue* to the *Jsonb* only by
*JsonbValueToJsonb* function. So, my question is can be *JsonbValue*,
that contains few *jbvBinary* elements, converted to *Jsonb* by this
function? It will be very useful, if you want modify only small part
of your JsonbValue (e.g. replace value of some key). But when I'm
trying to do this, an exception unknown type of jsonb container
appears. Maybe I missed something? Or is there another approach to do
this conversion?
If you can come up with a way of handling the jbvBinary values then by
all means send a patch.
But this problem is fairly easily worked around by using an iterator
over the binary value. The attached patch, which is work in progress for
adding in the currently missing json functions for jsonb, contains a
sort of example of doing this in jsonb_agg_transfn.
cheers
andrew
diff --git a/src/backend/utils/adt/jsonb.c b/src/backend/utils/adt/jsonb.c
index 2fd87fc..82dae72 100644
--- a/src/backend/utils/adt/jsonb.c
+++ b/src/backend/utils/adt/jsonb.c
@@ -12,11 +12,20 @@
*/
#include postgres.h
+#include miscadmin.h
+#include access/htup_details.h
+#include access/transam.h
+#include catalog/pg_cast.h
+#include catalog/pg_type.h
#include libpq/pqformat.h
#include utils/builtins.h
+#include utils/datetime.h
+#include utils/lsyscache.h
#include utils/json.h
#include utils/jsonapi.h
#include utils/jsonb.h
+#include utils/syscache.h
+#include utils/typcache.h
typedef struct JsonbInState
{
@@ -24,6 +33,23 @@ typedef struct JsonbInState
JsonbValue *res;
} JsonbInState;
+/* unlike with json categories, we need to treat json and jsonb differently */
+typedef enum /* type categories for datum_to_jsonb */
+{
+ JSONBTYPE_NULL,/* null, so we didn't bother to identify */
+ JSONBTYPE_BOOL,/* boolean (built-in types only) */
+ JSONBTYPE_NUMERIC, /* numeric (ditto) */
+ JSONBTYPE_TIMESTAMP,/* we use special formatting for timestamp */
+ JSONBTYPE_TIMESTAMPTZ, /* ... and timestamptz */
+ JSONBTYPE_JSON,/* JSON */
+ JSONBTYPE_JSONB, /* JSONB */
+ JSONBTYPE_ARRAY, /* array */
+ JSONBTYPE_COMPOSITE, /* composite */
+ JSONBTYPE_JSONCAST, /* something with an explicit cast to JSON */
+ JSONBTYPE_JSONBCAST, /* something with an explicit cast to JSONB */
+ JSONBTYPE_OTHER/* all else */
+} JsonbTypeCategory;
+
static inline Datum jsonb_from_cstring(char *json, int len);
static size_t checkStringLen(size_t len);
static void jsonb_in_object_start(void *pstate);
@@ -33,6 +59,22 @@ static void jsonb_in_array_end(void *pstate);
static void jsonb_in_object_field_start(void *pstate, char *fname, bool isnull);
static void jsonb_put_escaped_value(StringInfo out, JsonbValue *scalarVal);
static void jsonb_in_scalar(void *pstate, char *token, JsonTokenType tokentype);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory *tcategory,
+ Oid *outfuncoid);
+static void composite_to_jsonb(Datum composite, JsonbInState *result);
+static void array_dim_to_jsonb(JsonbInState *result, int dim, int ndims, int *dims,
+ Datum *vals, bool *nulls, int *valcount,
+ JsonbTypeCategory tcategory, Oid outfuncoid);
+static void array_to_jsonb_internal(Datum array, JsonbInState *result);
+static void jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory *tcategory,
+ Oid *outfuncoid);
+static void datum_to_jsonb(Datum val, bool is_null, JsonbInState *result,
+ JsonbTypeCategory tcategory, Oid outfuncoid,
+ bool key_scalar);
+static void add_jsonb(Datum val, bool is_null, JsonbInState *result,
+ Oid val_type, bool key_scalar);
/*
* jsonb type input function
@@ -462,3 +504,1070 @@ JsonbToCString(StringInfo out, JsonbContainer *in, int estimated_len)
return out-data;
}
+
+
+/*
+ * Determine how we want to render values of a given type in datum_to_jsonb.
+ *
+ * Given the datatype OID, return its JsonbTypeCategory, as well as the type's
+ * output function OID. If the returned category is JSONBTYPE_CAST, we
+ * return the OID of the type-JSON cast function instead.
+ */
+static void
+jsonb_categorize_type(Oid typoid,
+ JsonbTypeCategory *tcategory,
+ Oid *outfuncoid)
+{
+ bool typisvarlena;
+
+ /* Look through any domain */
+ typoid = getBaseType(typoid);
+
+ /* We'll usually need to return the type output function */
+ getTypeOutputInfo(typoid, outfuncoid, typisvarlena);
+
+ /* Check for known types */
+ switch (typoid)
+ {
+ case BOOLOID:
+ *tcategory = JSONBTYPE_BOOL;
+ break;
+
+ case INT2OID:
+ case INT4OID:
+ case INT8OID:
+ case FLOAT4OID:
+ case FLOAT8OID:
+ case NUMERICOID:
+
Re: [HACKERS] Help to startup
On 09/16/2014 01:51 PM, Tapan Halani wrote: Hello everyone..i am new to PostgreSQL project. I had prior experience with sql+ , with oracle 11g database server. Kindly help me grasp more about the project. The first thing you need to do is learn to ask your question in the right forum. pgsql-hackers is not that forum. pgsql-general is, but you should ask specific questions there, not just completely general and essentially unanswerable questions such as this. Before you ask questions there you should read the excellent PostgreSQL documentation at http://www.postgresql.org/docs/current/static/index.html cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Should we excise the remnants of borland cc support?
On 09/20/2014 09:24 AM, Andres Freund wrote: Hi, At the moment there's some rememnants of support for borland CC. I don't believe it's likely that any of it still works. I can't remember ever seing a buildfarm animal running it either - not surprising it's ~15 years since the last release. Since there's both msvc and mingw support for windows builds - borlands only platform - I see little point in continuing to support it. The reason I'm wondering is that the atomics patch cargo cults forward some stuff specific to borland and I'd rather not do that. And I'd rather be explicit about stopping to do so than slyly doing it. I thought the Borland stuff was there only so we could build client libraries for use with things like Delphi. It might be worth casting the net a little wider to find out if it still has any users. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Anonymous code block with parameters
On 09/18/2014 07:40 AM, Andres Freund wrote: On 2014-09-17 22:17:22 +0200, Pavel Stehule wrote: 2014-09-17 22:07 GMT+02:00 Vik Fearing vik.fear...@dalibo.com: On 09/16/2014 10:09 AM, Heikki Linnakangas wrote: On 09/16/2014 10:57 AM, Craig Ringer wrote: On 09/16/2014 03:15 PM, Pavel Stehule wrote: Why we don't introduce a temporary functions instead? I think that'd be a lot cleaner and simpler. It's something I've frequently wanted, and as Hekki points out it's already possible by creating the function in pg_temp, there just isn't the syntax sugar for CREATE TEMPORARY FUNCTION. So why not just add CREATE TEMPORARY FUNCTION? Sure, why not. Because you still have to do SELECT pg_temp.my_temp_function(blah); to execute it. this problem should be solvable. I can to use a temporary tables without using pg_temp schema. I fail to see why that is so much preferrable for you to passing parameter to DO? 1) You need to think about unique names for functions 2) Doesn't work on HOT STANDBYs 3) Causes noticeable amount of catalog bloat 4) Is about a magnitude or two more expensive So yes, TEMPORARY FUNCTION would be helpful. But it's simply a different feature. +1 If my memory isn't failing, when we implemented DO there were arguments for this additional feature, but we decided that it wouldn't be done at least on the first round. But we've had DO for a while and it's proved its worth. So I think now is a perfect time to revisit the issue. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [Windows,PATCH] Use faster, higher precision timer API
On 09/17/2014 08:27 AM, Craig Ringer wrote: Hi all Attached is a patch to switch 9.5 over to using the GetSystemTimeAsFileTime call instead of separate GetSystemTime and SystemTimeToFileTime calls. This patch the first step in improving PostgreSQL's support for Windows high(er) resolution time. In addition to requiring one less call into the platform libraries, this change permits capture of timestamps at up to 100ns precision, instead of the current 1ms limit. Unfortunately due to platform timer resolution limitations it will in practice only report with 1ms resolution and 0.1ms precision - or sometimes even as much as 15ms resolution. (If you want to know more, see the README for https://github.com/2ndQuadrant/pg_sysdatetime). On Windows 2012 and Windows 8 I'd like to use the new GetSystemTimePreciseAsFileTime call instead. As this requires some extra hoop-jumping to safely and efficiently use it without breaking support for older platforms I suggest that we start with just switching over to GetSystemTimeAsFileTime, which has been supported since Windows 2000. Then more precise time capture can be added in a later patch. That will presumably breaK XP. I know XP has been declared at EOL, but there are still a heck of a lot of such systems out there, especially in places like ATMs, but I saw it in use recently at a US surgical facility (which is slightly scary, although this wasn't for life-sustaining functionality). My XP system is still actually getting some security updates sent from Microsoft. I'm fine with doing this - frogmouth and currawong would retire on the buildfarm. Just wanted to be up front about it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Windows exit code 128 ... it's baaack
On 04/07/2014 10:26 AM, Andres Freund wrote: On 2014-04-05 11:05:09 -0400, Tom Lane wrote: Andres Freund and...@2ndquadrant.com writes: On 2014-02-27 19:14:13 -0500, Tom Lane wrote: I looked at the postmaster log for the ongoing issue on narwhal (to wit, that the contrib/dblink test dies the moment it tries to do anything dblink-y), and looky here what the postmaster has logged: One interesting bit about this is that it seems to work in 9.3... Well, yeah, it seems to have been broken somehow by the Windows linking changes we did awhile back to try to ensure that missing PGDLLIMPORT markers would be detected reliably. Which we did not back-patch. Hard to say since there's been no working builds for HEAD for so long on narwahl :(. This issue has been hanging around for many months, possibly much longer since the last successful build on narwhal was 2012-08-01 and then it went quiet until 2014-02-03, when it came back with this error. If we don't care to find a fix, I think we need to declare narwhal's fairly ancient compiler out of support and decommission it. Other gcc systems we have with more modern compilers are not getting this issue. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] [Windows,PATCH] Use faster, higher precision timer API
On 09/17/2014 12:51 PM, Andres Freund wrote: On 2014-09-17 11:19:36 -0400, Andrew Dunstan wrote: On 09/17/2014 08:27 AM, Craig Ringer wrote: Hi all Attached is a patch to switch 9.5 over to using the GetSystemTimeAsFileTime call instead of separate GetSystemTime and SystemTimeToFileTime calls. This patch the first step in improving PostgreSQL's support for Windows high(er) resolution time. In addition to requiring one less call into the platform libraries, this change permits capture of timestamps at up to 100ns precision, instead of the current 1ms limit. Unfortunately due to platform timer resolution limitations it will in practice only report with 1ms resolution and 0.1ms precision - or sometimes even as much as 15ms resolution. (If you want to know more, see the README for https://github.com/2ndQuadrant/pg_sysdatetime). On Windows 2012 and Windows 8 I'd like to use the new GetSystemTimePreciseAsFileTime call instead. As this requires some extra hoop-jumping to safely and efficiently use it without breaking support for older platforms I suggest that we start with just switching over to GetSystemTimeAsFileTime, which has been supported since Windows 2000. Then more precise time capture can be added in a later patch. That will presumably breaK XP. The proposed patch? I don't really see why? GetSystemTimeAsFileTime() is documented to be available since win2k? Oh, hmm, yes, you're right. For some reason I was thinking W2K was later than XP. I get more random memory errors as I get older ... cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: proposal: ignore null fields in not relation type composite type based constructors
On 09/11/2014 08:29 AM, Stephen Frost wrote: * Pavel Stehule (pavel.steh...@gmail.com) wrote: Can I help with something, it is there some open question? I had been hoping for a more definitive answer regarding this option for array_to_json, or even a comment about the change to row_to_json. Andrew- any thoughts on this? (that's what the ping on IRC is for :). The change in row_to_json looks OK, I think. we're replacing an overloading with use of default params, yes? That seems quite reasonable, and users shouldn't notice the difference. There might be a case for optionally suppressing nulls in array_to_json, and it might work reasonably since unlike SQL arrays JSON arrays don't have to be regular (if nested they are arrays of arrays, not multi-dimensional single arrays). OTOH I'm not sure if it's worth doing just for the sake of orthogonality. If someone wants it, then go for it. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Problems with config.php and non default ports (postgresql - sugarcrm)
On 09/08/2014 05:27 PM, Bianca Santana Espichicoquez wrote: Hello, I've a problem, we're using sugarcrm, and we have a database postgresql, but not in the default port, so, when I try to connect after I put the port in the db_port parameter, but seems like he not recognized, because still pointing to the other instance of the database that have the 5432 port, and I can see in that log when sugar it's trying to connect, even when I change the port. Postgresql 9.3 Sugarcrm 6.5.0 If i put in the db_host_name parameter: ip:port not work either. So, I need to know if exist another file where need to put the port value? I'll appreciate your help. Thanks. Please post on the correct list. Neither pgsql-advocacy nor pgsql-hackers is the correct list. Either a Sugarcrm list or pgsql-general might be the correct forum. Also, cross-posting on more than one postgresql list is generally frowned on. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/05/2014 12:37 PM, Merlin Moncure wrote:
On Thu, Sep 4, 2014 at 6:40 PM, Florian Pflug f...@phlo.org wrote:
Cost of hidden IO cast is negative too. If we can change it, then we can
increase a sped.
But the whole power of PL/pgSQL comes from the fact that it allows you to
use the full set of postgres data types and operatores, and that it seamlessly
integrated with SQL. Without that, PL/pgSQL is about as appealing as BASIC
as a programming language...
Right, and it's exactly those types and operators that are the cause
of the performance issues. A compiled pl/pgsql would only get serious
benefit for scenarios involving tons of heavy iteration or funky local
data structure manipulation. Those scenarios are somewhat rare in
practice for database applications and often better handled in a
another pl should they happen.
plv8 is emerging as the best non-sql it's JIT compiled by the plv8
runtime, the javascript language is designed for embedding. and the
json data structure has nice similarities with postgres's arrays and
types. In fact, if I *were* to attempt pl/pgsql compiling, I'd
probably translate the code to plv8 and hand it off to the llvm
engine. You'd still have to let postgres handle most of the operator
and cast operations but you could pull some things into the plv8
engine. Probably, this would be a net loser since plv8 (unlike
plpgsql) has to run everything through SPI.
plpgsql makes extensive use of SPI. Just look at the source code if you
don't believe me.
plv8 also has a nice find_function gadget that lets you find and call
another plv8 function directly instead of having to use an SPI call.
It has two serious defects in my view, that it inherits from v8.
First, and foremost, it has the old really really horrible Javascript
scoping rules for variables. This makes it totally unsuitable for
anything except trivially short functions. There is good news and bad
news on this front: modern versions of v8 have code to allow proper
lexical scoping as provided for in the draft ECMASCRIPT6 standard (the
feature is named harmony scoping). Example of command line use:
andrew@vpncli plv8js]$ d8 --use-strict --harmony
V8 version 3.14.5.10 [console: readline]
d8 var i = 10; for (let i = 0; i 3; i++) { let j = i; for (let i
= 4; i 6; i++) { print (j + j + i + i); } }
j 0 i 4
j 0 i 5
j 1 i 4
j 1 i 5
j 2 i 4
j 2 i 5
d8 print(i);
10
d8
The bad news is that neither Hitosho nor I (yet) know how to allow
setting these flags for the plv8 embedded engine.
The other defect is that its string handling is just awful. It has
neither multiline strings, not interpolation into strings.
The good news is that the new draft standard addresses these issues too,
with something called template strings. The bad news is that V8 doesn't
yet have code to support the feature, AFAICT. The Mozilla people are a
bit ahead here, and this feature is due in a release of their rhino
javascript library that will be in Mozilla 34, due out in November,
AIUI. Let's hope that the V8 guys get their act together on this.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 05:44 AM, Álvaro Hernández Tortosa wrote:
On 02/09/14 11:34, Mark Kirkwood wrote:
On 02/09/14 21:25, Álvaro Hernández Tortosa wrote:
On 02/09/14 05:24, Craig Ringer wrote:
I couldn't disagree more.
If we were to implement anything, it'd be PL/PSM
(http://en.wikipedia.org/wiki/SQL/PSM). I'm sure it's as bizarre and
quirky as anything else the SQL committee has brought forth, but
it's at
least a standard(ish) language.
So we'd choose a bizarre and quirky language instead of anything
better just because it's standard. I'm sure current and prospective
users will surely prefer a bizarre and quirky language that is standard
approved, rather than a modern, comfortable, easy-to-use, that is not
embodied by the ISO. No doubt ^_^
Well there is the risk that by randomly adding new syntax to PL/pgSQL
we turn it in a bizarre and quirky *non standard* language. Part of
the attraction of PL/pgsql is that it is Ada like - if we break that
too much then...well...that would be bad. So I think a careful
balance is needed, to add new features that keep the spirit of the
original language.
I agree. I think I haven't suggested adding new syntax to
pl/pgsql. But having its syntax similar to ADA is IMHO not something
good. I'm sure few prospective postgres users would be compelled to
that. They are compelled about JavaScript, python, Scala or Ruby, to
name a few, but definitely not ADA.
Just as a small nit pick - the name of the language is not ADA, but Ada.
It isn't an acronym. The language is named after Ada Lovelace, arguably
the world's first programmer. If you're not familiar with modern Ada,
let me recommend the newly published Programming in Ada 2012 by John
Barnes. But I digress.
JavaScript would actually be quite a good alternative. However, using it
involves something others have objected to, namely calling SQL via a
function call. It's true that plpgsql lets you call SQL commands without
explicitly invoking SPI. OTOH, it actually relies on SPI under the hood
a lot more that other PLs, which I have little doubt is responsible for
timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
But I'm not suggesting we should implement a Javascript PL in core either.
Finally, +1 to Tom's suggestion upthread that we implement different
behaviours via pragmas rather than some new offshoot language. Maybe a
GUC could specify a default set of such pragmas, so you wouldn't need to
decorate every function with them.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 08:41 AM, Ryan Pedela wrote: If PL/Javascript is a serious consideration, how will int64 and numeric be handled? Please don't top-post on the PostgreSQL lists. See http://idallen.com/topposting.html Unfortunately, I think the short answer is not very well. In theory we cauld add in new types to a Javascript interpreter to handle them, but that would still leave you scrambling to handle user defined types. One of the advantages of plpgsql is that it can handle any Postgres data type without having to do anything special. The truth is that different PLs meet different needs and have different strengths and weaknesses. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 09:08 AM, Pavel Stehule wrote:
JavaScript would actually be quite a good alternative. However,
using it involves something others have objected to, namely
calling SQL via a function call. It's true that plpgsql lets you
call SQL commands without explicitly invoking SPI. OTOH, it
actually relies on SPI under the hood a lot more that other PLs,
which I have little doubt is responsible for timings like this:
andrew=# do $$ declare x int = 1; i int = 1; begin while i
1000 loop i := i + 1; x := x + 46; end loop; raise notice ' x =
%',x; end; $$;
NOTICE: x = 45955
DO
Time: 13222.195 ms
andrew=# do $$ var x = 1; var i = 1; while (i 1000) { i += 1;
x += 46; } plv8.elog(NOTICE, x = + x); $$ language plv8;
NOTICE: x = 45955
DO
Time: 27.976 ms
this test is unfair to plpgsql, and you know it well :)
any operations over native types will be faster than in plpgsql,
although this difference is maybe too much. Doesn't use
--enable-cassert ?
It's not unfair, and no it isn't using cassert. This was from a
production grade server.
PLV8 has its own issues (see discussion elsewhere in this thread re
int64 and numeric). It's just that speed isn't one of them :-)
Please note that I'm not unhappy with plpgsql. I have my own small list
of things that I would like improved, but there isn't very much that
bugs me about it.
A few years ago I was largely instrumental in building an entire billing
system, including some very complex tax rating, for a small Telco, using
plpgsql plus a tiny bit of plperlu glue where we needed unsafe
operations. It was quite fast enough - see my talk at pgopen a few years
back.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 12:12 PM, Joel Jacobson wrote: On Tue, Sep 2, 2014 at 6:03 PM, Heikki Linnakangas hlinnakan...@vmware.com wrote: I think that would actually be a good way to enforce the rule that an UPDATE only updates a single row. Just put a ASSERT ROW_COUNT=1; after the update. So instead of one line of code, I would need to write two lines of code at almost *all* places where a currently have an UPDATE. :-( In that case, I think RETURNING TRUE INTO STRICT _OK is less ugly. I think the problem with my perspective is my ambitions. I use PL/pgSQL not as a secondary language, but it's my primary language for developing applications. For me, updating a row, is like setting a variable in a normal language. No normal language would require two rows to set a variable. It would be like having to do: my $var = 10; die unless $var == 10; in Perl to set a variable. That's really a problem with your perspective. UPDATE is inherently set oriented. It's emphatically NOT like setting a single variable. I must have written tens, possibly hundreds of thousands of lines of plpgsql, and this have never ever been a problem for me. I'd be very opposed to adding some special new plpgsql-only syntax to have UPDATE or DELETE error out if they affected more than a single row. And as you and others have observed, you can do that now with the RETURNING true INTO STRICT ok trick. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/02/2014 06:50 PM, Jan Wieck wrote: On 09/02/2014 06:41 PM, Joshua D. Drake wrote: On 09/02/2014 02:47 PM, Álvaro Hernández Tortosa wrote: Yeah, we differ there. I think having an Oracle compatibility layer in PostgreSQL would be the-next-big-thing we could have. Oracle is has orders of magnitude bigger user base than postgres has; and having the ability to attract them would bring us many many more users which, in turn, would benefit us all very significantly. It would be my #1 priority to do in postgres (but yes, I know -guess- how hard and what resources that would require). But dreaming is free :) Oracle compatibility certainly has merit, I just don't see it as useful for core. I would be far more interested in MSSQL compatibility honestly. That said, Postgres itself is a rockstar and I think we can make our own case without having to copy others. PL/pgSQL's syntax was modelled to look like PL/SQL. Which is a Ada/COBOL lookalike. Ada yes, COBOL no. Instead of trying to mimic what it was or a T-SQL thing instead ... maybe it is time to come up with a true PostgreSQL specific PL for a change? Just for the sake of being something new, and not a copy of some old opossum, that's rotting like road kill on the side of the highway for a decade already. People are free to do what they want, but to my mind that would be a massive waste of resources, and probably imposing a substantial extra maintenance burden on the core committers. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PL/pgSQL 2
On 09/01/2014 08:09 PM, Neil Tiffin wrote: On Sep 1, 2014, at 10:45 AM, Tom Lane t...@sss.pgh.pa.us wrote: What is actually being proposed, AFAICS, is a one-shot fix for a bunch of unfortunate choices. That might be worth doing, but let's not fool ourselves about whether it’s one-shot or not. Well, one shot every 18 years is not so bad. I am only a casual user and as such probably do not merit much consideration from the experts here. I only work with PL/pgSQL occasionally, but never go weeks without doing it and sometimes it is all I do for weeks. That said and this being the internet, IMO working in PL/pgSQL is only slightly better than stabbing myself in the leg with a knife compared to other non-PL/pgSQL languages I work in. Mostly my hate is directed at the silly quoting. But it has lots of other quirks that are not all that obvious unless you work with it all day every day. Now I could use other languages as was suggested upstream. Lets see, I use R all the time, but R is not a first class language, not in core, and its slow. Python 3 would be acceptable to me, but its untrusted. tcl I don’t know and don’t want to learn as no one else seems to use it (in my world anyway). perl is the only possibility left and again, no one in my world is using Perl and it’s not clear if there is a performance penalty. The docs say the best language for performance is PL/pgSQL after pure SQL. Really, this is from the docs a_output := a_output || '' if v_'' || referrer_keys.kind || '' like '' || referrer_keys.key_string || '' then return '' || referrer_keys.referrer_type || ‘'; end if;''; The docs also tell you how to avoid having to do this, using dollar quoting. That should be enough alone to suggest postgreSQL start working on a modern, in core, fast, fully supported language. Of course PL/pgSQL works, but so did one-line 5k perl programs that nobody likes today. Everything can be done in assembler, but no one suggests that today. Today, it is all about programmer productivity. PL/pgSQL has a lot of unnecessary stuff that sucks the life out of programmer productivity. And this should be very much a concern of the professionals that support PostgreSQL For example: DECLARE declarations BEGIN statements END This looks a lot like COBOL or Pascal, and today is mostly unnecessary. It looks like Ada, and that's not an accident. (Nor is it a bad thing.) The very last thing we should be doing is to invent a new language. There are already plenty to choose from. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing dependency to wsock32.lib when compiling code on WIndows
On 08/29/2014 10:15 AM, Noah Misch wrote: On Thu, Aug 21, 2014 at 11:29:31PM -0400, Noah Misch wrote: On Thu, Aug 21, 2014 at 09:18:22AM -0400, Andrew Dunstan wrote: What's happening about this? Buildfarm animal jacana is consistently red because of this. If nobody plans to do the aforementioned analysis in the next 4-7 days, I suggest we adopt one of Michael's suggestions: force configure to reach its old conclusion about getaddrinfo() on Windows. Then the analysis can proceed on an unhurried schedule. Done. Incidentally, jacana takes an exorbitant amount of time, most recently 87 minutes, to complete the ecpg-check step. Frogmouth takes 4 minutes, and none of the other steps have such a large multiplier between the two animals. That pattern isn't new and appears on multiple branches. I wonder if ecpg tickles a performance bug in 64-bit MinGW-w64. Good pickup. I wonder what it could be. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP Patch for GROUPING SETS phase 1
On 08/22/2014 02:42 PM, Greg Stark wrote: On Fri, Aug 22, 2014 at 7:02 PM, Tom Lane t...@sss.pgh.pa.us wrote: So the proposal you are pushing is going to result in seriously teeing off some fraction of our userbase; and the argument why that would be acceptable seems to boil down to I think there are few enough of them that we don't have to care (an opinion based on little evidence IMO FWIW here's some evidence... Craig Kersteins did a talk on the statistics across the Heroku fleet: Here are the slides from 2013 though I think there's an updated slide deck with more recent numbers out there: https://speakerdeck.com/craigkerstiens/postgres-what-they-really-use Cube shows up as the number 9 most popular extension with about 1% of databases having it installed (tied with pg_crypto and earthdistance). That's a lot more than I would have expected actually. That's an interesting statistic. What I'd be more interested in is finding out how many of those are actually using it as opposed to having loaded it into a database. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Removing dependency to wsock32.lib when compiling code on WIndows
On 08/15/2014 11:00 PM, Noah Misch wrote: On Fri, Aug 15, 2014 at 12:49:36AM -0700, Michael Paquier wrote: Btw, how do you determine if MSVC is using HAVE_GETADDRINFO? Is it decided by the inclusion of getaddrinfo.c in @pgportfiles of Mkvdbuild.pm? src/include/pg_config.h.win32 dictates it, and we must keep @pgportfiles synchronized with the former's verdict. What's happening about this? Buildfarm animal jacana is consistently red because of this. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] WIP Patch for GROUPING SETS phase 1
On 08/21/2014 02:48 PM, Merlin Moncure wrote: Basically, I'm afraid that unilaterally renaming cube is going to break enough applications that there will be more people who flat out don't want this patch than there will be who get benefit from it, and we end up voting to revert the feature altogether. If you'd like to take that risk then feel free to charge full steam ahead, but don't say you were not warned. And don't bother arguing that CUBE is reserved according to the standard, because that will not make one damn bit of difference to the people who will be unhappy. I have to respectfully disagree. Certainly, if there is some reasonable way to not have to change 'cube' then great. But the tonnage rule applies here: even considering compatibility issues, when considering the importance of standard SQL (and, I might add, exceptionally useful) syntax and a niche extension, 'cube' is going to have to get out of the way. There are view valid reasons to break compatibility but blocking standard syntax is definitely one of them. I'm inclined to think that the audience for this is far larger than the audience for the cube extension, which I have not once encountered in the field. But I guess we all have different experiences. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] New PostgreSQL buildfarm client release 4.14 - bug fix for MSVC
There is a new release - version 4.14 - of the buildfarm client, now available at http://www.pgbuildfarm.org/downloads/releases/build-farm-4_14.tgz The only change of note is that a bug which only affects MSVC clients (such that the client will not complete a run) and is present in releases 4.12 and 4.13 is fixed. Clients on other platforms do not need to upgrade. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why not ISO 8601 format for date values rendered into JSON?
On 08/17/2014 08:42 PM, Tom Lane wrote:
I was just going over the release notes, and noticed the bit about
timestamp and timestamptz now being rendered in a fixed ISO-8601-compliant
format rather than whatever random DateStyle is in use. That's fine,
but I wonder why the same approach wasn't applied to type date?
regression=# set datestyle to postgres;
SET
regression=# select row_to_json(row(now()));
row_to_json
---
{f1:2014-08-17T20:34:54.424237-04:00}
(1 row)
regression=# select row_to_json(row(current_date));
row_to_json
-
{f1:08-17-2014}
(1 row)
Doesn't seem real consistent ...
Good point. Probably because I didn't get a complaint about it, which in
turn is probably because JavaScript's builtin Date class is in fact
(from our POV) more or less a timestamp(tz) type.
See
https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Date
But yes, I agree it should be fixed. Whatever we output should be
suitable as input for the string-argument constructor of class Date.
cheers
andrew
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why not ISO 8601 format for date values rendered into JSON?
On 08/17/2014 09:53 PM, Tom Lane wrote: OK. I think I can fix it, if you don't have time. [offlist] Thanks. FYI I am still recovering from treatment for prostate cancer I had not long after pgcon ... it's taken more out of me that I expected, so time is limited. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Why not ISO 8601 format for date values rendered into JSON?
On 08/17/2014 10:50 PM, Andrew Dunstan wrote: On 08/17/2014 09:53 PM, Tom Lane wrote: OK. I think I can fix it, if you don't have time. [offlist] Thanks. FYI I am still recovering from treatment for prostate cancer I had not long after pgcon ... it's taken more out of me that I expected, so time is limited. Darn it. well, I guess everyone knows now. *sigh* cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb format is pessimal for toast compression
On 08/13/2014 09:01 PM, Tom Lane wrote: I wrote: That's a fair question. I did a very very simple hack to replace the item offsets with item lengths -- turns out that that mostly requires removing some code that changes lengths to offsets ;-). I then loaded up Larry's example of a noncompressible JSON value, and compared pg_column_size() which is just about the right thing here since it reports datum size after compression. Remembering that the textual representation is 12353 bytes: json: 382 bytes jsonb, using offsets: 12593 bytes jsonb, using lengths: 406 bytes Oh, one more result: if I leave the representation alone, but change the compression parameters to set first_success_by to INT_MAX, this value takes up 1397 bytes. So that's better, but still more than a 3X penalty compared to using lengths. (Admittedly, this test value probably is an outlier compared to normal practice, since it's a hundred or so repetitions of the same two strings.) What does changing to lengths do to the speed of other operations? cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb format is pessimal for toast compression
On 08/07/2014 11:17 PM, Tom Lane wrote: I looked into the issue reported in bug #11109. The problem appears to be that jsonb's on-disk format is designed in such a way that the leading portion of any JSON array or object will be fairly incompressible, because it consists mostly of a strictly-increasing series of integer offsets. This interacts poorly with the code in pglz_compress() that gives up if it's found nothing compressible in the first first_success_by bytes of a value-to-be-compressed. (first_success_by is 1024 in the default set of compression parameters.) [snip] There is plenty of compressible data once we get into the repetitive strings in the payload part --- but that starts at offset 944, and up to that point there is nothing that pg_lzcompress can get a handle on. There are, by definition, no sequences of 4 or more repeated bytes in that area. I think in principle pg_lzcompress could decide to compress the 3-byte sequences consisting of the high-order 24 bits of each offset; but it doesn't choose to do so, probably because of the way its lookup hash table works: * pglz_hist_idx - * * Computes the history table slot for the lookup by the next 4 * characters in the input. * * NB: because we use the next 4 characters, we are not guaranteed to * find 3-character matches; they very possibly will be in the wrong * hash list. This seems an acceptable tradeoff for spreading out the * hash keys more. For jsonb header data, the next 4 characters are *always* different, so only a chance hash collision can result in a match. There is therefore a pretty good chance that no compression will occur before it gives up because of first_success_by. I'm not sure if there is any easy fix for this. We could possibly change the default first_success_by value, but I think that'd just be postponing the problem to larger jsonb objects/arrays, and it would hurt performance for genuinely incompressible data. A somewhat painful, but not yet out-of-the-question, alternative is to change the jsonb on-disk representation. Perhaps the JEntry array could be defined as containing element lengths instead of element ending offsets. Not sure though if that would break binary searching for JSON object keys. Ouch. Back when this structure was first presented at pgCon 2013, I wondered if we shouldn't extract the strings into a dictionary, because of key repetition, and convinced myself that this shouldn't be necessary because in significant cases TOAST would take care of it. Maybe we should have pglz_compress() look at the *last* 1024 bytes if it can't find anything worth compressing in the first, for values larger than a certain size. It's worth noting that this is a fairly pathological case. AIUI the example you constructed has an array with 100k string elements. I don't think that's typical. So I suspect that unless I've misunderstood the statement of the problem we're going to find that almost all the jsonb we will be storing is still compressible. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb format is pessimal for toast compression
On 08/08/2014 11:18 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 08/07/2014 11:17 PM, Tom Lane wrote: I looked into the issue reported in bug #11109. The problem appears to be that jsonb's on-disk format is designed in such a way that the leading portion of any JSON array or object will be fairly incompressible, because it consists mostly of a strictly-increasing series of integer offsets. Back when this structure was first presented at pgCon 2013, I wondered if we shouldn't extract the strings into a dictionary, because of key repetition, and convinced myself that this shouldn't be necessary because in significant cases TOAST would take care of it. That's not really the issue here, I think. The problem is that a relatively minor aspect of the representation, namely the choice to store a series of offsets rather than a series of lengths, produces nonrepetitive data even when the original input is repetitive. It would certainly be worth validating that changing this would fix the problem. I don't know how invasive that would be - I suspect (without looking very closely) not terribly much. 2. Are we going to ship 9.4 without fixing this? I definitely don't see replacing pg_lzcompress as being on the agenda for 9.4, whereas changing jsonb is still within the bounds of reason. Considering all the hype that's built up around jsonb, shipping a design with a fundamental performance handicap doesn't seem like a good plan to me. We could perhaps band-aid around it by using different compression parameters for jsonb, although that would require some painful API changes since toast_compress_datum() doesn't know what datatype it's operating on. Yeah, it would be a bit painful, but after all finding out this sort of thing is why we have betas. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb format is pessimal for toast compression
On 08/08/2014 12:04 PM, John W Higgins wrote: Would an answer be to switch the location of the jsonb header data to the end of the field as opposed to the beginning of the field? That would allow pglz to see what it wants to see early on and go to work when possible? Add an offset at the top of the field to show where to look - but then it would be the same in terms of functionality outside of that? Or pretty close? That might make building up jsonb structures piece by piece as we do difficult. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb format is pessimal for toast compression
On 08/08/2014 11:54 AM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: On 08/08/2014 11:18 AM, Tom Lane wrote: That's not really the issue here, I think. The problem is that a relatively minor aspect of the representation, namely the choice to store a series of offsets rather than a series of lengths, produces nonrepetitive data even when the original input is repetitive. It would certainly be worth validating that changing this would fix the problem. I don't know how invasive that would be - I suspect (without looking very closely) not terribly much. I took a quick look and saw that this wouldn't be that easy to get around. As I'd suspected upthread, there are places that do random access into a JEntry array, such as the binary search in findJsonbValueFromContainer(). If we have to add up all the preceding lengths to locate the corresponding value part, we lose the performance advantages of binary search. AFAICS that's applied directly to the on-disk representation. I'd thought perhaps there was always a transformation step to build a pointer list, but nope. It would be interesting to know what the performance hit would be if we calculated the offsets/pointers on the fly, especially if we could cache it somehow. The main benefit of binary search is in saving on comparisons, especially of strings, ISTM, and that could still be available - this would just be a bit of extra arithmetic. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] jsonb format is pessimal for toast compression
On 08/08/2014 08:45 PM, Tom Lane wrote: Perhaps another options would be a new storage type which basically says just compress it, no matter what? We'd be able to make that the default for jsonb columns too, no? Meh. We could do that, but it would still require adding arguments to toast_compress_datum() that aren't there now. In any case, this is a band-aid solution; and as Josh notes, once we ship 9.4 we are going to be stuck with jsonb's on-disk representation pretty much forever. Yeah, and almost any other solution is likely to mean non-jsonb users potentially paying a penalty for fixing this for jsonb. So if we can adjust the jsonb layout to fix this problem I think we should do so. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New developer TODO suggestions
On 07/29/2014 10:43 AM, Bruce Momjian wrote: * A function that converts a json array to a PostgreSQL array of a given type if all json members are compatible with the type * Expanding the set of json/jsonb operations to introduce features that people are used to from jquery, mongo, etc. Replace-key-if-exists-without-adding, add-or-replace-key, etc. I think you have to ask Andrew on these. Both these might be possible. I am not planning on doing them, at least. My current json plans for 9.5 are limited to implementing jsonb equivalents of those json functions that didn't make it into the 9.4 jsonb work due to pressure of time, i.e. the json generating functions and the aggregates. That work has been started and with luck will hit the next commitfest. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] New developer TODO suggestions
On 07/30/2014 06:11 PM, Peter Geoghegan wrote: On Wed, Jul 30, 2014 at 2:37 PM, Andrew Dunstan and...@dunslane.net wrote: Both these might be possible. I am not planning on doing them, at least. My current json plans for 9.5 are limited to implementing jsonb equivalents of those json functions that didn't make it into the 9.4 jsonb work due to pressure of time, i.e. the json generating functions and the aggregates. That work has been started and with luck will hit the next commitfest. Does that include the concatenate operator? That's probably the single biggest thing we missed. No, the only thing I am doing to provide jsonb equivaents of existing json functions where they don't currently exist. There is no existing json concatenation operator. I think there are quite a few operations that we could very usefully provide. Given the buzz that our json work has been generating, that would probably be a very productive area to work on. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pdfs
On 07/27/2014 11:28 AM, Tom Lane wrote: Personally I find the PDF docs to be an anachronism: surely nobody is printing them on dead trees any more, and for on-computer usage, what do they offer that the HTML format doesn't? So I'm unexcited about making them slightly prettier. If they are then maybe there's no point in trying to build them in the buildfarm constantly. One advantage that they have over the HTML docs is that they encapsulate the docs in a single file. But then, so does the epub format, which, unlike PDFs, can adapt to display dimensions. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Reminder: time to stand down from 8.4 maintenance
I have removed it from the buildfarm server's branches_of_interest.txt. buildfarm members that rely in this file won't need to take any action, except possibly to clean up their build root. cheers andrew On 07/28/2014 07:41 PM, Tom Lane wrote: PG 8.4.x is EOL as of last week's releases, so it's time to remove that branch from any auto-update scripts you might have, reconfigure buildfarm members that are force-building it, etc. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] building pdfs
Is there any standard set of packages on any supported platform that will allow for building the doc PDFs? So far I have not found one on either Fedora 20 or Ubuntu 14.04, but maybe I'm missing something. I am looking at adding a buildfarm facility to build the docs, but I'm not prepared to make buildfarm owners turn handsprings in order to do so. IMNSHO, if we can't build the docs in all their formsts with standard distro packages then there's a problem right there we should address. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pdfs
On 07/26/2014 02:22 PM, Andres Freund wrote: On 2014-07-26 14:14:15 -0400, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Is there any standard set of packages on any supported platform that will allow for building the doc PDFs? So far I have not found one on either Fedora 20 or Ubuntu 14.04, but maybe I'm missing something. On either Fedora or RHEL, installing the authoring tools package group (not quite the right name, but it's close) has worked for me for many years. I've not bothered to figure out what the minimum required subset of that might be. Not sure what the Debian equivalent is, but borka/guaibasaurus is able to build the docs, so the packages certainly are available. Peter might have a better idea. 'apt-get build-dep postgresql-$version' IIRC does the trick there. Installs a thing or two more, but those are things that a debian buildfarm member is going have installed anyway. Yes, I did that and generated a PDF, but I got an enormous number of errors or warnings. See https://www.dropbox.com/s/9n4hhijin3qn8mw/postgres-US.log for example. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] building pdfs
On 07/26/2014 06:44 PM, Tom Lane wrote: Andrew Dunstan and...@dunslane.net writes: Yes, I did that and generated a PDF, but I got an enormous number of errors or warnings. See https://www.dropbox.com/s/9n4hhijin3qn8mw/postgres-US.log for example. If they're things like overfull hbox from the TeX step, they're expected. That's rather sad. How would we find out that something has actually gone wrong, short of it failing to write a PDF altogether? Searching through 204,000 lines of output doesn't sound like fun. There are lots of these: Package Fancyhdr Warning: \fancyhead's `E' option without twoside option is use less on input line 83877. and quite a few overfull hboxes that are more than 72pt too wide. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] PDF builds broken again
On 07/23/2014 06:31 AM, Magnus Hagander wrote: Do we actually have any buildfarm boxes building the PDFs? And if so, any idea why they didn't catch it? AFAIK, nobody's ever asked for such a thing. The docs optional step just builds the default docs target, which is simply the HTML docs. It should be possible, just a SMOC. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some bogus results from prairiedog
On 07/22/2014 12:24 AM, Tom Lane wrote: According to http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prairiedogdt=2014-07-21%2022%3A36%3A55 prairiedog saw a crash in make check on the 9.4 branch earlier tonight; but there's not a lot of evidence as to why in the buildfarm report, because the postmaster log file is truncated well before where things got interesting. Fortunately, I was able to capture a copy of check.log before it got overwritten by the next run. I find that the place where the webserver report stops matches this section of check.log: [53cd99bb.134a:158] LOG: statement: create index test_range_gist_idx on test_range_gist using gist (ir); [53cd99bb.134a:159] LOG: statement: insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; ^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^\ @^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@[53cd99ba.1344:329] LOG: statement: INSERT INTO num_exp_div VALUES (7,8,'-1108.80577182462841041118'); [53cd99ba.1344:330] LOG: statement: INSERT INTO num_exp_add VALUES (7,9,'-107955289.045047420'); [53cd99ba.1344:331] LOG: statement: INSERT INTO num_exp_sub VALUES (7,9,'-58101680.954952580'); The ^@'s represent nul bytes, which I find runs of elsewhere in the file as well. I think they are an artifact of OS X buffering policy caused by multiple processes writing into the same file without any interlocks. Perhaps we ought to consider making buildfarm runs use the logging collector by default? But in any case, it seems uncool that either the buildfarm log-upload process, or the buildfarm web server, is unable to cope with log files containing nul bytes. The data is there, just click on the check stage link at the top of the page to see it in raw form. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Some bogus results from prairiedog
On 07/22/2014 10:55 AM, Andrew Dunstan wrote: On 07/22/2014 12:24 AM, Tom Lane wrote: According to http://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=prairiedogdt=2014-07-21%2022%3A36%3A55 prairiedog saw a crash in make check on the 9.4 branch earlier tonight; but there's not a lot of evidence as to why in the buildfarm report, because the postmaster log file is truncated well before where things got interesting. Fortunately, I was able to capture a copy of check.log before it got overwritten by the next run. I find that the place where the webserver report stops matches this section of check.log: [53cd99bb.134a:158] LOG: statement: create index test_range_gist_idx on test_range_gist using gist (ir); [53cd99bb.134a:159] LOG: statement: insert into test_range_gist select int4range(g, g+10) from generate_series(1,2000) g; ^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^\ @^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@^@[53cd99ba.1344:329] LOG: statement: INSERT INTO num_exp_div VALUES (7,8,'-1108.80577182462841041118'); [53cd99ba.1344:330] LOG: statement: INSERT INTO num_exp_add VALUES (7,9,'-107955289.045047420'); [53cd99ba.1344:331] LOG: statement: INSERT INTO num_exp_sub VALUES (7,9,'-58101680.954952580'); The ^@'s represent nul bytes, which I find runs of elsewhere in the file as well. I think they are an artifact of OS X buffering policy caused by multiple processes writing into the same file without any interlocks. Perhaps we ought to consider making buildfarm runs use the logging collector by default? But in any case, it seems uncool that either the buildfarm log-upload process, or the buildfarm web server, is unable to cope with log files containing nul bytes. The data is there, just click on the check stage link at the top of the page to see it in raw form. I have made a change in the upload receiver app to escape nul bytes in the main log field too. This will operate prospectively. Using the logging collector would be a larger change, but we can look at it if this isn't sufficient. cheers andrew -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
