[HACKERS] help for old extension, first_last_agg bug in install with pg9.5

[Peter Krauss]

The C implementation is simple, the problem is with the makefile,
   https://github.com/wulczer/first_last_agg/issues/2

*some clues?*

- - -

make
Makefile:25: /usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk:
  No such file or directory
make: *** No rule to make target
  `/usr/lib/postgresql/9.5/lib/pgxs/src/makefiles/pgxs.mk'.
  Stop.

Using UBUNTU 14 LTS

See also https://github.com/wulczer/first_last_agg/blob/master/Makefile

Re: [HACKERS] problem with precendence order in JSONB merge operator

[Peter Krauss]

Subjective notes to contextualize (try to explain on bad-English) my
"precedence order" and JSONB visions:

JSON datatype is perfect as workaround, and for many simple and less
exigent applications.
JSONB is the  "first class" datatype for user community, we expected years
(!) for it ... Need some "first class" and friendly behaviour.

In this context JSONB is not "any other" datatype, it is the bridge between
relational data and flexible data...
It is the Holy Grail and the Rosetta Stone :-)

I think JSONB operators need some more attention, in semantic and usability
contexts.   If you want to add  some friendliness and orthogonality in
JSONB operators, will be natural to see -> operator as a kind of
object-oriented *path* operator...
By other hand, of course, you can do the simplest to implement JSONB... But
you do a lot <http://www.postgresql.org/docs/9.5/static/functions-json.html>
(!), it was not easy to arrive here, and need only a little bit more
to  reach perfection ;-)



2016-03-22 18:42 GMT-03:00 David G. Johnston <david.g.johns...@gmail.com>:

> On Tue, Mar 22, 2016 at 1:52 PM, Peter Krauss <ppkra...@gmail.com> wrote:
>
>> Seems that parser not using precedence ideal order, and that casting
>> obligation losts performance.
>>
>> The first problem is self-evident in this example:
>>
>> SELECT '{"x":1}'::jsonb || (('{"A":{"y":2}}'::jsonb)->'A')
>>   -- it is ok, expected result with (x,y)
>> SELECT '{"x":1}'::jsonb || '{"A":{"y":2}}'::jsonb)->'A'
>>   -- non-expected result (y).
>>
>> Higher precedence <https://en.wikipedia.org/wiki/Order_of_operations> most
>> be for -> operator, that is like an object-oriented *path* operator,
>> always higher than algebric ones.
>>
> ​There is presently no formal concept of "path operator" in PostgreSQL.
>  "->" is a user-defined operator, as is "||"​ and thus have equal
> precedence and left associativity.
>
> http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html
>
> Regardless, "||" is not an "algebric" [sic] operator...I'm curious what
> source you are using to back your claim of operator precedence between
> different so-called "operator types".
>
> Its highly undesirable to make changes to operator precedence.
>
> Operators are simply symbols to the parser - there is no context involved
> that would allow making their precedence dynamic.  So all PostgreSQL sees
> is "||", not a "JSONB merge operator".
>
> Other problem is using this operation as SQL function
>>
>>   CREATE FUNCTION term_lib.junpack(jsonb,text) RETURNS JSONB AS $f$
>> SELECT ($1-$2)::JSONB || ($1->>$2)::JSONB;
>>   $f$ LANGUAGE SQL IMMUTABLE;
>>
>> without casting produce error. Perhaps will be "more friendly" without
>> cast obligation,
>>
>> and it is a performance problem, the abusive use of castings losts
>> performance.
>>
> I cannot make this work...
>
> version
> PostgreSQL 9.5.1 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
> 4.8.2-19ubuntu1) 4.8.2, 64-bit
>
> SELECT ('{"a":1,"b":2}'::jsonb - 'b'::text)::jsonb ||
> ('{"a":1,"b":2}'::jsonb #> 'b'::text)::jsonb
>
> > ​SQL Error: ERROR: invalid concatenation of jsonb objects
> ​
> This seems like user error but without a self-contained test case
> exercising the query (the use of a function in this context should be
> immaterial) I'm finding it hard to explain why.  My simple case returns a
> non-object with rightly cannot be appended to an object.
>
> In isolatoin you can avoid casting the RHS of the || operator by using the
> "#>(jsonb,text[])" operator
>
> SELECT pg_typeof('{"a":1,"b":{"c":2}}'::jsonb #> array['b']::text[])
> --jsonb
>
> JSON, IME, still needs some fleshing out.  Efficient usage might require
> additional features but for now one needs to get very familiar with all the
> various operator variants that allow the user to choose whether to return
> json or text and to pick the correct one for their needs.
>
> ​David J.
> ​
>
>

[HACKERS] problem with precendence order in JSONB merge operator

[Peter Krauss]

Seems that parser not using precedence ideal order, and that casting
obligation losts performance.

The first problem is self-evident in this example:

SELECT '{"x":1}'::jsonb || (('{"A":{"y":2}}'::jsonb)->'A')
  -- it is ok, expected result with (x,y)
SELECT '{"x":1}'::jsonb || '{"A":{"y":2}}'::jsonb)->'A'
  -- non-expected result (y).

Higher precedence  most
be for -> operator, that is like an object-oriented *path* operator, always
higher than algebric ones.


Other problem is using this operation as SQL function,

  CREATE FUNCTION term_lib.junpack(jsonb,text) RETURNS JSONB AS $f$
SELECT ($1-$2)::JSONB || ($1->>$2)::JSONB;
  $f$ LANGUAGE SQL IMMUTABLE;

without casting produce error. Perhaps will be "more friendly" without cast
obligation,

and it is a performance problem, the abusive use of castings losts
performance.

Re: [HACKERS] looking for an array-extract-item-as-it operator

[Peter Krauss]

(ops, sending to the pgsql-hackers, see the complete thread below)

Adding a formal suggestion after discussion: to  include a fast
array_getarray() function!


CREATE FUNCTION array_getarray( m anyarray, idx int ) RETURNS anyarray AS
$f$
-- this is a slow workaround for an (need for) internal operation
WITH item AS (SELECT unnest($1[$2:$2]) as x)
SELECT array_agg(x) FROM item;
$f$ LANGUAGE sql IMMUTABLE;

-- EXAMPLE:
  SELECT array_getarray(zz,2) as x, zz[2:2] as y  -- x is not same as y!
  FROM ( SELECT '{{1,2},{33,44}}'::int[][] as zz  ) as tt




2016-01-07 7:26 GMT-02:00 Peter Krauss <ppkra...@gmail.com>:

>
>
> 2016-01-06 20:50 GMT-02:00 Tom Lane <t...@sss.pgh.pa.us>:
>
>> Peter Krauss <ppkra...@gmail.com> writes:
>> > I need to access an array-item from an array of arrays.
>>
>> Multi-dimensional arrays in Postgres are *not* "arrays of arrays".
>>
>
> Thanks,  you expressed in a little phrase something fundamental to think
> about pg-arrays (!), the pg-Guide need some notices like yours, to remember
> people like me ;-)  Well... The good answer closes the question.
>
> - - - -
>
> We can imagine that the "multidimensional array world" is like a data
> type, that is distinct from the "usual array" data type...
> I am starting other discussion...
>
> Let me explain how the question arrives for me: was after working with
> JSONB, where arrays are of "usual array" type.
> Now that PostgreSQL 9.4+ incorporated definitely JSONB, the SQL array data
> type is an important "intermediate" between JSONB and usual SQL structures
> (and type operation algebras).
>
> So, perhaps, PostgreSQL 9.4+ will need a kind of "usual array type", a new
> internal type, and a *cast* function: with this new type will be possible
> to simplify the work with JSONB, and do other things like
>  array_agg(array[x,y]).
> ... It is not for final user, perhaps only for developers, or library
> plugins: an "intermediate" type that not broken compatibility... Not very
> useful, a type only to formally express things like to eficient cast, etc.
>
>
>
>
>
>

[HACKERS] looking for an array-extract-item-as-it operator

[Peter Krauss]

I need to access an array-item from an array of arrays. Suppose


WITH t AS (SELECT '{{1,2,3},{33,44,55}}'::int[][] as a)SELECT
 a[2],-- returns null (!), why not works?
 a[2:2],  -- returns array-into-array, not a simple arrayFROM t;


There are a simple function or operator to acess it as it?

Summarizing: I am looking for a  f(a,2)  that returns  {33,44,55}, not
{{33,44,55}}.

[HACKERS] Proposal for JSONB functions for internal representation casting insted text-casting

[Peter Krauss]

The usefulness of  ->>  operator is indisputable, but even with boolean or
numeric values, with good binary internal representation, it returns JSONB
value as text data type.

The simple *(myJSONB->>'myField')::expectedType* is not enough because:

1) there are no internal optimization,  need two-step casting, first
bynary-to-text, them text-to-expectedType.

2) if expectedType is not the expected (in the associated jsonb_typeof),
generates an error... The ideal "return NULL" convention is not easy to
implement with usual casting.

More details and some examples at
   http://stackoverflow.com/q/34579758/287948

- - - -
CONTEXT OF USEFULNESS

As section "8.14. JSON Types" in the pg9.4 guide,
"Representing data as JSON can be considerably more flexible (...) is quite
possible for both approaches to co-exist and complement each other (...)
However, even for applications where maximal flexibility is desired, it is
still recommended that JSON documents have a somewhat fixed structure".

The proposed casting functions of JSONB is a part of "predictable but fluid
structure" demands in JSON representation, and makes it easier to write
queries that mix usual data types with JSONB.

- - - -
Formal requeriment  for
a C implementation below

CREATE FUNCTION jbval_to_numeric(JSONB, varchar) RETURNS numeric AS $f$
  SELECT CASE
WHEN jsonb_typeof($1->$2)='number' THEN ($1->>$2)::numeric
ELSE NULL::numeric
  END;$f$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION jbval_to_float(JSONB, varchar) RETURNS float AS $f$
  SELECT CASE
WHEN jsonb_typeof($1->$2)='number' THEN ($1->>$2)::float
ELSE NULL::float
  END;$f$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION jbval_to_int(JSONB, varchar, boolean DEFAULT true)
RETURNS int AS $f$
  SELECT CASE
WHEN jsonb_typeof($1->$2)='number' THEN
   CASE WHEN $3 THEN ($1->>$2)::int ELSE ($1->>$2)::float::int END
ELSE NULL::int
  END;$f$ LANGUAGE sql IMMUTABLE;
CREATE FUNCTION jbval_to_boolean(JSONB, varchar) RETURNS boolean AS $f$
  SELECT CASE
WHEN jsonb_typeof($1->$2)='boolean' THEN ($1->>$2)::boolean
ELSE NULL::boolean
  END;$f$ LANGUAGE sql IMMUTABLE;

[HACKERS] need of anonymous record

[Peter Krauss]

My notion of anonymous record, and the need of this kind of higher-order
type, are discussed in the links below,

http://stackoverflow.com/q/23439240
  Functions can not to *return individual items of a record*

http://stackoverflow.com/q/21246201
  PostgreSQL v9.X have real '*array of record*' ?

The first question is about performance: *returns table* have the same
performance than *returns record*??
If yes, the *record* datatype is somewhat outdated?


The second question/discussion shows a more deeper problem, where perhaps
the use of string index in array syntax (something like x['a']) would be a
good syntax solution.
Craig Ringer shows another suggestion in the (second) link.


I am new here, I do not know how, but I'm available to help... Can a future
version of PostgreSQL implement some solution?


Peter