Re: [HACKERS] proposal: schema variables

[Hannu Krosing]

but you can always do

with a (id, value) as (
  values (1, 'foo'), (2, 'bar'), (3, 'baz')
)
select set_config('custom.value',(select value from a where id = 2),true);

if you are worried about the evaluation order

On 29 October 2017 at 09:51, Chris Travers  wrote:

>
>
> On Sat, Oct 28, 2017 at 4:56 PM, Pavel Stehule 
> wrote:
>
>>
>>>
>> The creating database objects and necessary infrastructure is the most
>> simple task of this project. I'll be more happy if there are zero
>> intersection because variables and GUC are designed for different purposes.
>> But due SET keyword the intersection there is.
>>
>> When I thinking about it, I have only one, but important reason, why I
>> prefer design new type of database object -the GUC are stack based with
>> different default granularity - global, database, user, session, function.
>> This can be unwanted behave for variables - it can be source of hard to
>> detected bugs. I afraid so this behave can be too messy for usage as
>> variables.
>>
>> @1 I have not clean opinion about it - not sure if rights are good enough
>> - probably some user limits can be more practical - but can be hard to
>> choose result when some user limits and GUC will be against
>>
>
> I was mostly thinking that users can probably set things like work_mem and
> possibly this might be a problem.
>
>
>> @2 With variables typed custom GUC are not necessary
>>
>
> I don't know about that.  For example with the geoip2lookup extension it
> is nice that you could set the preferred language for translation on a per
> user basis or the mmdb path on a per-db basis.
>
>
>> @3 Why you need it? It is possible with set_config function now.
>>
>
> Yeah you could do it safely with set_config and a CTE, but suppose I have:
>
> with a (Id, value) as (values (1::Int, 'foo'), (2, 'bar'), (3, 'baz'))
> SELECT set_config('custom_val', value) from a where id = 2;
>
> What is the result out of this?  I would *expect* that this would probably
> run set_config 3 times and filter the output.
>
>
>>
>> Regards
>>
>> Pavel
>>
>>
>>
>>
>>>
>>>
 regards

 Pavel



>>>
>>>
>>> --
>>> Best Regards,
>>> Chris Travers
>>> Database Administrator
>>>
>>> Tel: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr |
>>> www.adjust.com
>>> Saarbrücker Straße 37a, 10405 Berlin
>>> 
>>>
>>>
>>
>
>
> --
> Best Regards,
> Chris Travers
> Database Administrator
>
> Tel: +49 162 9037 210 <+49%20162%209037210> | Skype: einhverfr |
> www.adjust.com
> Saarbrücker Straße 37a, 10405 Berlin
> 
>
>

Re: [HACKERS] Why we lost Uber as a user

[Hannu Krosing]

On 07/27/2016 12:07 AM, Tom Lane wrote:
>
>> 4. Now, update that small table 500 times per second.
>> That's a recipe for runaway table bloat; VACUUM can't do much because
>> there's always some minutes-old transaction hanging around (and SNAPSHOT
>> TOO OLD doesn't really help, we're talking about minutes here), and
>> because of all of the indexes HOT isn't effective.
> Hm, I'm not following why this is a disaster.  OK, you have circa 100%
> turnover of the table in the lifespan of the slower transactions, but I'd
> still expect vacuuming to be able to hold the bloat to some small integer
> multiple of the minimum possible table size.  (And if the table is small,
> that's still small.)  I suppose really long transactions (pg_dump?) could
> be pretty disastrous, but there are ways around that, like doing pg_dump
> on a slave.
Is there any theoretical obstacle which would make it impossible to
teach VACUUM not to hold back the whole vacuum horizon, but just
to leave a single transaction alone in case of a long-running
REPEATABLE READ transaction ?

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic Ltd



-- 
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] Design for In-Core Logical Replication

[Hannu Krosing]

On 07/20/2016 10:08 AM, Simon Riggs wrote:
> 
>   Monitoring
>   
> pg_stat_replication
>   
>   
> pg_stat_subscription
>   
> 

and probably also `pg_stat_publication` or some other way to see, what
tables are currently in a PUBLICATION, who has subscribed etc.

> 
> CREATE PUBLICATION mypub;
> ALTER PUBLICATION mypub ADD TABLE users, departments;
> 
Would a subscription just be a logical grouping or would it be something
stronger
like meaning atomic subscriptions and/or a dedicated replication slot ?

Can you subscribe to multiple publications through single SUBSCRIPTION ?

What is supposed to happen if table A is in two subscriptions S1 and S2,
and you
subscribe to both? Will you get table a only once (both initial copy and
events)?

Would a subscription of "mypub" pop up on subscriber side atomically, or
will subscribed
tables appear one-by one when they are ready (initial copy + catchup
event replay completed) ?

I recall that one of the drivers of developing pgq/skytools to replace
Slony was the
fact that Slony's "replication group" design made it very easy to
blunder subscription
changes in more complex topologies which manifested in deadlocks.

PGQ-s table-by-table subscription avoided this entirely at the cost on
non-atomic
subscribed table appearance.

Of course once subscribed, everything was transaction-consistent again.

> 
> CREATE SUBSCRIPTION mysub WITH CONNECTION dbname=foo host=bar
> user=repuser PUBLICATION mypub;
> 
For the pgq-like version which consider a PUBLICATION just as list of
tables to subscribe, I would add

CREATE SUBSCRIPTION mysub WITH CONNECTION 'dbname=foo host=bar
user=repuser' PUBLICATION mypub, mypub1;

ALTER SUBSCRIPTION mysub DROP PUBLICATION mypub1;

ALTER SUBSCRIPTION mysub ADD PUBLICATION mypub2;





-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 10/10/2014 11:38 AM, Simon Riggs wrote:
 On 10 October 2014 10:29, Heikki Linnakangas hlinnakan...@vmware.com wrote:
 On 10/10/2014 11:57 AM, Simon Riggs wrote:
 Postgres currently supports column level SELECT privileges.

 1. If we want to confirm a credit card number, we can issue SELECT 1
 FROM customer WHERE stored_card_number = '1234 5678 5344 7733'

 2. If we want to look for card fraud, we need to be able to use the
 full card number to join to transaction data and look up blocked card
 lists etc..

 3. We want to block the direct retrieval of card numbers for
 additional security.
 In some cases, we might want to return an answer like ' * 
 7733'

 We can't do all of the above with current facilities inside the database.

 Deny access to the underlying tables. Write SQL functions to do 1. and 2.,
 and grant privileges to the functions, instead. For 3. create views that do
 the redaction.
 If everything were easy to lock down the approach you suggest is of
 course the best way.

 The problem there is that the SQL for (2) changes frequently, so we
 want to give people SQL access.
1. Give people access to development system with safe data where they
write their functions

2. once function is working, pass it to auditors

3. deploy and use the function.
 Just not the ability to retrieve data in a usable form.
For an attacker any access is in a usable form, for honest people you
can just provide a view or set-returning function.

btw, one way to do the redaction you suggested above is to write a
special
type, which redacts data on output.

You can even make the type output function dependent on backup role.

Just make sure that users are aware that it is not really a security
feature
which protects against attackers.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/18/2014 08:41 PM, Andrew Dunstan wrote:

 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.
One possible syntax would be extending WITH to somehow enable on-spot
functions in addition to on-spot views

WITH FUNCTION myfunc(...) RETURNS TABLE(...) LANGUAGE plpgsql AS $$
...
$$
SELECT f.*
  FROM myfunc(x,y,z);


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/18/2014 02:37 PM, Pavel Stehule wrote:

 if we would to need a single use function, then we should to
 implement it, and we should not to rape some different objects. Some,
 what has behave like function should be function.

 After some thinking, probably CTE design can be only one frame, where
 we can do it

 WITH
  FUNCTION f1(a int) RETURNS int AS $$ .. $$ LANGUAGE plpgsql,
  FUNCTION f2(a int) RETURNS SETOF int AS $$ .. $$ LANGUAGE
 plpgsql,
   SELECT f1(x) FROM f2(z) LATERAL 

 We can generalize WITH clause, so there SEQENCES, VIEWS, .. can be
 defined for single usage
+2

I just proposed the same thing in another branch of this discussion
before reading this :)

I guess it proves (a little) that WITH is the right place to do these
kind of things ...


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/18/2014 10:40 PM, Marko Tiikkaja wrote:
 On 2014-09-18 10:29 PM, Vik Fearing wrote:
 On 09/18/2014 10:16 PM, Hannu Krosing wrote:
 I guess it proves (a little) that WITH is the right place to do these
 kind of things ...

 I've been wanting this syntax for a few years now, so I certainly vote
 for it.

 I've also been wanting do to something like:

   WITH mytyp AS (a int, b int, c int)
   SELECT (tup).* FROM
   (
 SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp
 WHEN .. THEN ROW(2,3,4)
 ELSE ROW (3,4,5) END AS tup
 FROM ..
   ) ss
+1


 .marko





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

[Hannu Krosing]

On 09/19/2014 12:14 AM, Hannu Krosing wrote:
 On 09/18/2014 10:40 PM, Marko Tiikkaja wrote:
 On 2014-09-18 10:29 PM, Vik Fearing wrote:
 On 09/18/2014 10:16 PM, Hannu Krosing wrote:
 I guess it proves (a little) that WITH is the right place to do these
 kind of things ...
 I've been wanting this syntax for a few years now, so I certainly vote
 for it.
 I've also been wanting do to something like:

   WITH mytyp AS (a int, b int, c int)
   SELECT (tup).* FROM
   (
 SELECT CASE WHEN .. THEN ROW(1,2,3)::mytyp
 WHEN .. THEN ROW(2,3,4)
 ELSE ROW (3,4,5) END AS tup
 FROM ..
   ) ss
 +1
Though it would be even nicer to have fully in-line type definition

SELECT (tup).* FROM
  (
SELECT CASE WHEN .. THEN ROW(1,2,3)::(a int, b text, c int2)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
  ) ss

or an incomplete type with names, as types can be given in ROW

SELECT (tup).* FROM
  (
SELECT CASE WHEN .. THEN ROW(1,2::text,3::int2)::(a, b, c)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
  ) ss

or just normal select query syntax:

SELECT (tup).* FROM
  (
SELECT CASE WHEN .. THEN ROW(1 AS a,2::text AS b,3::int2 AS c)
WHEN .. THEN ROW(2,3,4)
ELSE ROW (3,4,5) END AS tup
FROM ..
  ) ss




Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/16/2014 09:15 AM, Pavel Stehule wrote:


 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com
 mailto:hlinnakan...@vmware.com:

 On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:

 I'd like to propose support for IN and OUT parameters in 'DO'
 blocks.

 Currently, anonymous code blocks (DO statements) can not
 receive or
 return parameters.

 I suggest:

 1) Add a new clause to DO statement for specifying names, types,
 directions and values of parameters:

 DO code [LANGUAGE lang] [USING (arguments)]

 where arguments has the same syntax as in
 'CREATE FUNCTION name (arguments)'.

 Example:

 do $$ begin z := x || y; end; $$
 language plpgsql
 using
 (
x text = '1',
in out y int4 = 123,
out z text
 );

 2) Values for IN and IN OUT parameters are specified using
 syntax for
 default values of function arguments.

 3) If DO statement has at least one of OUT or IN OUT
 parameters then it
 returns one tuple containing values of OUT and IN OUT parameters.

 Do you think that this feature would be useful? I have a
 proof-of-concept patch in progress that I intend to publish soon.


 There are two features here. One is to allow arguments to be
 passed to DO statements. The other is to allow a DO statement to
 return a result. Let's discuss them separately.

 1) Passing arguments to a DO block can be useful feature, because
 it allows you to pass parameters to the DO block without injecting
 them into the string, which helps to avoid SQL injection attacks.

 I don't like the syntax you propose though. It doesn't actually
 let you pass the parameters out-of-band, so I don't really see the
 point. I think this needs to work with PREPARE/EXECUTE, and the
 protocol-level prepare/execute mechanism. Ie. something like this:

 PREPARE mydoblock (text, int4) AS DO $$ ... $$
 EXECUTE mydoblock ('foo', 123);

 2) Returning values from a DO block would also be handy. But I
 don't see why it should be restricted to OUT parameters. I'd
 suggest allowing a RETURNS clause, like in CREATE FUNCTION:

 DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;

 or

 DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2 int4);

 - Heikki


 Why we don't introduce a temporary functions instead?

As I see it, the DO blocks _are_ temporary (or rather in-line)
functions, though quite restricted in not taking arguments and not
returning anything.


DO you have a better syntax for temporary / in-line functions ?

What I would like to to is to make DO blocks equal to any other data
source, so you could do

WITH mydoblock(col1, col2)(DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE
(col1 text, col2 int4))
SELECT * FROM mydoblock;

or

SELECT *
FROM (DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text, col2
int4)) mydoblock;

and for the parameter-taking version

SELECT (DO $$ ... $$ LANGUAGE plpgsql USING (user) RETURNS
int4)(username) AS usernum
FROM users;


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] Anonymous code block with parameters

[Hannu Krosing]

On 09/16/2014 09:44 AM, Pavel Stehule wrote:


 2014-09-16 9:24 GMT+02:00 Heikki Linnakangas hlinnakan...@vmware.com
 mailto:hlinnakan...@vmware.com:

 On 09/16/2014 10:15 AM, Pavel Stehule wrote:

 2014-09-16 9:10 GMT+02:00 Heikki Linnakangas
 hlinnakan...@vmware.com mailto:hlinnakan...@vmware.com:

 On 09/16/2014 09:38 AM, Kalyanov Dmitry wrote:

 I'd like to propose support for IN and OUT parameters
 in 'DO' blocks.

 Currently, anonymous code blocks (DO statements) can
 not receive or
 return parameters.

 I suggest:

 1) Add a new clause to DO statement for specifying
 names, types,
 directions and values of parameters:

 DO code [LANGUAGE lang] [USING (arguments)]

 where arguments has the same syntax as in
 'CREATE FUNCTION name (arguments)'.

 Example:

 do $$ begin z := x || y; end; $$
 language plpgsql
 using
 (
 x text = '1',
 in out y int4 = 123,
 out z text
 );

 2) Values for IN and IN OUT parameters are specified
 using syntax for
 default values of function arguments.

 3) If DO statement has at least one of OUT or IN OUT
 parameters then it
 returns one tuple containing values of OUT and IN OUT
 parameters.

 Do you think that this feature would be useful? I have a
 proof-of-concept patch in progress that I intend to
 publish soon.


 There are two features here. One is to allow arguments to
 be passed to DO
 statements. The other is to allow a DO statement to return
 a result. Let's
 discuss them separately.

 1) Passing arguments to a DO block can be useful feature,
 because it
 allows you to pass parameters to the DO block without
 injecting them into
 the string, which helps to avoid SQL injection attacks.

 I don't like the syntax you propose though. It doesn't
 actually let you
 pass the parameters out-of-band, so I don't really see the
 point. I think
 this needs to work with PREPARE/EXECUTE, and the
 protocol-level
 prepare/execute mechanism. Ie. something like this:

 PREPARE mydoblock (text, int4) AS DO $$ ... $$
 EXECUTE mydoblock ('foo', 123);

 2) Returning values from a DO block would also be handy.
 But I don't see
 why it should be restricted to OUT parameters. I'd suggest
 allowing a
 RETURNS clause, like in CREATE FUNCTION:

 DO $$ ... $$ LANGUAGE plpgsql RETURNS int4;

 or

 DO $$ ... $$ LANGUAGE plpgsql RETURNS TABLE (col1 text,
 col2 int4);


 Why we don't introduce a temporary functions instead?


 You can already do that:

 create function pg_temp.tempfunc(i int4) returns int4 as $$ begin
 end; $$ language plpgsql;


 it looks much more like workaround than supported feature.
a straightforward CREATE TEMPORARY FUNCTION implementation would do
exactly that.
  


 Compared to DO, you have to do extra steps to create the function,
 and drop it when you're done. And you can't use them in a hot
 standby, because it changes the catalogs. (although a better
 solution to that would be to make it work, as well as temporary
 tables, but that's a much bigger project).

 Maybe we don't need any of this, you can just use temporary
 function. But clearly someone though that DO statements are useful
 in general, because we've had temporary functions for ages and we
 nevertheless added the DO statement.


 I afraid so we create little bit obscure syntaxes, without real effect
 and real cost
I would agree with you if we had session-level temporary functions

But then we would still miss anonymous/in-line/on-the-spot functions

 Any new useful syntax should be clean, simple, natural and shorter
 than create function ...
This is not how SQL works, nor ADA nor pl/pgsql ;)

 and without risks a conflicts with ANSI SQL

 I prefer a typed session variables, where is not risk of SQL injection
 or some performance lost. The benefit of typed server side variables
 can be for wide group of users.
Agreed

but this would be a much bigger project, as Heikki already mentioned re.
temp things on replicas.

especially if typed session variables could hold temporary functions .

DECLARE FUNCTION mytempfucntion () ...


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance

Re: [HACKERS] PL/pgSQL 1.2

[Hannu Krosing]

On 09/04/2014 02:40 PM, Pavel Stehule wrote:



 2014-09-04 14:37 GMT+02:00 Joel Jacobson j...@trustly.com
 mailto:j...@trustly.com:



 On 4 sep 2014, at 11:42, Pavel Stehule pavel.steh...@gmail.com
 mailto:pavel.steh...@gmail.com wrote:
 2014-09-04 11:22 GMT+02:00 Joel Jacobson j...@trustly.com
 mailto:j...@trustly.com:

 The point was, RETURNS returns 1 while RETURNS SETOF returns
 0 .. n.


 no RETURNS return VALUE (it is not a row) .. and in combination
 with SELECT - value will be a row. RETURNS SETOF returns rows

 I intentionally excluded the data type of what is returned.
 1 VALUE vs 0...n VALUES
 Do you still fail to see the point 1 VALUE is special in the
 context of what a function returns?


 sorry, I don't understand .. for me SRF functions are absolutly
 different monsters than scalar, array or composite function - so its
 impossible to compare it.
When looking from the other end of the problem, we are
using SELECT/INSERT/UPDATE/DELETE *SET statements* in pl/pgsql
when we really want scalars.

My understanding is that one main drivers of starting this thread
was wanting also guaranteed SCALAR versions of these.

And wanting them in a way that is easy to use.


Cheers


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] PL/pgSQL 1.2

[Hannu Krosing]

On 09/03/2014 05:09 PM, Marko Tiikkaja wrote:
 On 9/3/14 5:05 PM, Bruce Momjian wrote:
 On Wed, Sep  3, 2014 at 07:54:09AM +0200, Pavel Stehule wrote:
 I am not against to improve a PL/pgSQL. And I repeat, what can be
 done and can
 be done early:

 a) ASSERT clause -- with some other modification to allow better
 static analyze
 of DML statements, and enforces checks in runtime.

 b) #option or PRAGMA clause with GUC with function scope that
 enforce check on
 processed rows after any DML statement

 c) maybe introduction automatic variable ROW_COUNT as shortcut for GET
 DIAGNOSTICS rc = ROW_COUNT

 All these ideas are being captured somewhere, right?  Where?

 I'm working on a wiki page with all these ideas.  Some of them break
 backwards compatibility somewhat blatantly, some of them could be
 added into PL/PgSQL if we're okay with reserving a keyword for the
 feature. All of them we think are necessary.

Ok, here are my 0.5 cents worth of proposals for some features discussed
in this thread

They should be backwards compatible, but perhaps they are not very
ADA/SQL-kosher  ;)

They also could be implemented as macros first with possible
optimisations in the future


1. Conditions for number of rows returned by SELECT or touched by UPDATE
or DELETE
-

Enforcing number of rows returned/affected could be done using the
following syntax which is concise and clear (and should be in no way
backwards incompatible)

SELECT[1]   - select exactly one row, anything else raises error
SELECT[0:1]   - select zero or one rows, anything else raises error
SELECT[1:] - select one or more rows

plain SELECT is equivalent to SELECT[0:]

same syntax could be used for enforcing sane affected row counts
for INSERT and DELETE


A more SQL-ish way of doing the same could probably be called COMMAND
CONSTRAINTS
and look something like this

SELECT
...
CHECK (ROWCOUNT BETWEEN 0 AND 1);



2. Substitute for EXECUTE with string manipulation


using backticks `` for value/command substitution in SQL as an alternative
to EXECUTE string

Again it should be backwards compatible as , as currently `` are not
allowed inside pl/pgsql functions

Sample 1:

ALTER USER `current_user` PASSWORD newpassword;

would be expanded to

EXECUTE 'ALTER USER ' || current_user ||
' PASSWORD = $1' USING newpassword;

Sample2:

SELECT * FROM `tablename` WHERE `idcolumn` = idvalue;

this could be expanded to

EXECUTE 'SELECT * FROM ' || tablename ||
' WHERE quote_ident(idcolumn) = $1' USING idvalue;

Notice that the use of  around `` forced use of quote_ident()


3. A way to tell pl/pggsql not to cache plans fro normal queries
---

This could be done using a #pragma or special /* NOPLANCACHE */
comment as suggested by Pavel

Or we could expand the [] descriptor from 1. to allow more options

OR we could do it in SQL-ish way using like this:

SELECT
...
USING FRESH PLAN;


Best Regards

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/04/2014 12:17 AM, Marko Tiikkaja wrote:
 On 2014-09-03 23:19, Hannu Krosing wrote:
 1. Conditions for number of rows returned by SELECT or touched by UPDATE
 or DELETE
 -


 Enforcing number of rows returned/affected could be done using the
 following syntax which is concise and clear (and should be in no way
 backwards incompatible)

 SELECT[1]   - select exactly one row, anything else raises error
 SELECT[0:1]   - select zero or one rows, anything else raises error
 SELECT[1:] - select one or more rows

 plain SELECT is equivalent to SELECT[0:]

 same syntax could be used for enforcing sane affected row counts
 for INSERT and DELETE

 I'm not sure how much I like that syntax in cases like:

   WITH t AS (
 -- multi-line query here
   )
   SELECT[0:] foo, bar
   INTO _bat, _man
   FROM foo
   JOIN ..
   JOIN ..
   WHERE ..
   -- etc.

 It seems quite well hidden compared to a single keyword at the
 beginning of the query.
What do you have in mind ?

Is your wiki page already available somewhere ?

 It's also not clear whether all of this flexibility is required.
 Enforcing exactly one conveniently is my main priority.
What do you want here on top of SELECT ... INTO STRICT ... ?
 Supporting the at most one case could be nice, too, but anything
 else feels like overkill.  Though if the syntax is based on numbers
 (and not a keyword), then I guess we get the flexibility for free anyway.

 I also have my doubts about how easy it would be to implement this
 syntax given that we're using the real SQL parser.
Definitely not trivial, but at least doable :)

Finding and processing SELECT[...] could probably even be done with
a (regex-based ?) pre-parser .


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/02/2014 11:52 AM, Álvaro Hernández Tortosa wrote:

 On 02/09/14 11:44, Pavel Stehule wrote:





 For 9.4, we have the media already saying Postgres has NoSQL
 capabilities (which is only partially true). For x.y we could
 have the media saying Postgres adds Oracle compatibility (which
 would be only partially true). But that brings a lot of users to
 postgres, and that helps us all.


 Partial true can enforce so lot of people will hate postgres too.
 False promises are wrong

 Then let's stop talking about postgres being NoSQL. NoSQL is
 basically schema-less (really bad name) plus infinite scalability
 (which basically means transparent sharding). We fail to provide the
 latter very clearly...
Have you ever tried any of the real NoSQL products version of
infinite scalability ?

We are no worse than most if you use just the unstructured part (which
is what the NoSQL crowd provides) and something like pl/proxy for scaling.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] PL/pgSQL 2

[Hannu Krosing]

On 09/02/2014 06:27 PM, Joel Jacobson wrote:
 On Tue, Sep 2, 2014 at 6:11 PM, Álvaro Hernández Tortosa a...@nosys.es 
 wrote:
 We are definitely worse. This is the problem, we only look to our own
 belly bottom (if this expression exists in English). All NoSQL scale
 *easily*, *transparently* beyond one node. Postgres doesn't. I'm not saying
 they don't suck at many many other things, or that some of them may be worse
 solution than the problem. But despite JSON/JSONB in pg is awesome, it's far
 far away from what we need to compete agains NoSQL in these regards.
 So the discussion started out with a desire to improve PL/pgSQL. Now
 somehow NoSQL and JSON is discussed in the same thread. Interesting.
 Godwin's Law never fails :-)
 http://en.wikipedia.org/wiki/Godwin's_law
Not to mention completely unsubstantiated claims about *all* NoSQL
scaling *easily* and *transparently* beyond one node :)

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/01/2014 11:24 AM, Andres Freund wrote:
 On 2014-09-01 11:04:53 +0200, Joel Jacobson wrote:
 For those of you who use PL/pgSQL every day, I'm quite certain you all feel
 there are a number of things you would like to change in the language, but
 realize it cannot be achieved without possibly breaking compatibility, at
 least in theory. Even though you own code would survive the change, there
 might be code somewhere in the world which would break. This is of course
 not acceptable and that's why we have the current status quo of
 development, or at least not far away from a status quo.

 So instead of continue to adding optional settings to the config file, and
 instead of killing discussions around what can be done by bringing up the
 backwards-compatibility argument, let's instead fork the language and call
 it plpgsql2. Since no code is yet written in plpgsql2, we can start of from
 a clean sheet, and no good ideas need to be killed due to
 backwards-compatibility concerns.

 The interest for such a project is probably limited to a small number of
 companies/people around the world, as most users are probably perfectly
 happy with the current version of plpgsql, as they only use it occasionally
 and not every day like we do at my company.

 Just like with plpgsql, once released, plpgsql2 cannot break compatibility
 with future versions, so we only have one chance to carefully think though
 what we would like to change in the language.

 From the top of my head, these are Things I personally would want to see in
 plpgsql2:
 + Make UPDATE/INSERT/DELETE throw error if they didnt' modify exactly 1
 row, as that's the most common use-case, and provide alternative syntax to
 modify multiple or zero rows.
 + Make SELECT .. INTO .. throw an error if it selects more than 1 row. INTO
 STRICT only works if no rows should be an error, but there is currently no
 nice way if no rows OR exactly 1 row should be found by the query.
 + Change all warnings into errors
 -many.

 Look at the *disaster* the few minor changes in python3 were. It's now,
 years after, only starting to get used again.

 You're going to have to find a more gradual way of doing this.
Probably a better way (and there has been some talk of it) is
having some kind of PRAGMA functionality, or pl/pgsql specific
LOCAL SET to affect just this function and not spill to nested
functions as is the case for SETs now.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/01/2014 12:00 PM, Marko Tiikkaja wrote:
 On 9/1/14 11:53 AM, Hannu Krosing wrote:
 On 09/01/2014 11:24 AM, Andres Freund wrote:
 Look at the *disaster* the few minor changes in python3 were. It's now,
 years after, only starting to get used again.

 You're going to have to find a more gradual way of doing this.
 Probably a better way (and there has been some talk of it) is
 having some kind of PRAGMA functionality, or pl/pgsql specific
 LOCAL SET to affect just this function and not spill to nested
 functions as is the case for SETs now.

 I can't imagine how that would work for anyone who has thousands of
 functions.

 I've tried my best over the past ~year or so, but any attempts at
 breaking backwards compatibility have been rejected.  I really don't
 see any gradual way of doing this.  We either break things, live with
 what we have right now, or create a new language.


 .marko
My approach would be to add optional LOCAL modifier to WITH, so instead

CREATE [ OR REPLACE ] FUNCTION
name ( [ [ argmode ] [ argname ] argtype [ { DEFAULT | = }
default_expr ] [, ...] ] )
...
[ WITH ( attribute [, ...] ) ]

it would be

...
[ WITH ( [LOCAL] attribute [, ...] ) ]

where LOCAL attributes are _not_ inherited by nested functions
but the LOCALs would shadow globals in the function definitions
that have them.

I know it is easier said than done, but from the user perspective
this could be a nice clean approach.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/01/2014 12:55 PM, Andres Freund wrote:
 On 2014-09-01 12:49:22 +0200, Marko Tiikkaja wrote:
 On 9/1/14 12:12 PM, Andres Freund wrote:
 On 2014-09-01 12:00:48 +0200, Marko Tiikkaja wrote:
 On 9/1/14 11:53 AM, Hannu Krosing wrote:
 You're going to have to find a more gradual way of doing this.
 Probably a better way (and there has been some talk of it) is
 having some kind of PRAGMA functionality, or pl/pgsql specific
 LOCAL SET to affect just this function and not spill to nested
 functions as is the case for SETs now.
 I can't imagine how that would work for anyone who has thousands of
 functions.
 How's that fundamentally different from changing languages? If we had a
 way to *add* such attributes to *existing* functions I don't see the
 fundamental problem?
 Adding 5-10 of these for every function you create seems significantly more
 painful than saying this function uses plpgsql2.  
You could package up these 5-10 SET LOCAL options as a separate language
handler (called plpgsql2) which really does nothing more than set the
local options and call the base handler :)

this will still leave you with flexibility of adding/removing features for
single functions

For extra convenience you could even create a CREATE META LANGUAGE ...
option for defining such language handlers.

 Though perhaps what's
 being suggested is a *single* option which changes everything at once?  Then
 there wouldn't be a huge difference.
 The likelihood of us now knowing all the things that we want to break
 rigth now seems about zero. There *will* be further ones. If we go with
 the approach of creating new language versions for all of them we'll end
 up with a completely unmaintainable mess. For PG devs, application dev
 and DBAs.

 Since what you seemingly want - sensibly so imo - is to set the default
 errors for *new* functions, but leave the old set of errors for
 preexisting ones, I suggest adding a GUC that defines the set of
 warnings/errors *new* functions get. There'd need to be some syntax to
 opt out for pg_dump and similar, but that sounds unproblematic.

 One question here imo is whether we design something for plpgsql or more
 generic...
I for one would like to have a generic SET LOCAL feature so it
could also be used for pl/pythonu or pl/v8


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/01/2014 03:45 PM, Craig Ringer wrote:
 On 09/01/2014 05:04 PM, Joel Jacobson wrote:
 Just like with plpgsql, once released, plpgsql2 cannot break
 compatibility with future versions, so we only have one chance to
 carefully think though what we would like to change in the language.
 You're not proposing to copy plpgsql's runtime though, right? Just add
 conditional paths where v1 and v2 differ.

 Personally I'd rather look at adding language version pragmas or a new
 function attribute, but that's mostly bikeshedding.

 From the top of my head, these are Things I personally would want to see
 in plpgsql2:
 - Accept RECORD input, dynamic access to fields of records without
   resorting to hstore hacks. This is certainly my #1.

Also, an easy way to tell pl/pgsql to *not* cache plans without
resorting to EXECUT'ins trings would nice

Cheers
Hannu

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 09/01/2014 05:52 PM, Andres Freund wrote:
 On 2014-08-29 20:12:16 +0200, Hannu Krosing wrote:
 It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
 pagenr, tupnr).

 Cross-partition indexes are especially needed if we want to allow putting
 UNIQUE constraints on non-partition-key columns.
 I actually don't think this is necessary. I'm pretty sure that you can
 build an efficient and correct version of unique constraints with
 several underlying indexes in different partitions each. The way
 exclusion constraints are implemented imo is a good guide.

 I personally think that implementing cross partition indexes has a low
 enough cost/benefit ratio that I doubt it's wise to tackle it anytime
 soon.
Also it has the downside of (possibly) making DROP PARTITION either
slow or wasting space until next VACUUM.

So if building composite unique indexes over multiple per-partition
indexes is doable, I would much prefer this.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 08/31/2014 10:03 PM, Tom Lane wrote:
 Another thought about this general topic:

 Alvaro Herrera alvhe...@2ndquadrant.com writes:
 ...
 Allowed actions on a RELKIND_PARTITION:
 * CREATE INDEX .. ON PARTITION n ON TABLE xyz
 ...
 Still To Be Designed
 
 * Are indexes/constraints inherited from the parent rel?
 I think one of the key design decisions we have to make is whether
 partitions are all constrained to have exactly the same set of indexes.
 If we don't insist on that it will greatly complicate planning compared
 to what we'll get if we do insist on it, because then the planner will
 need to generate a separate customized plan subtree for each partition.
 Aside from costing planning time, most likely that would forever prevent
 us from pushing some types of intelligence about partitioning into the
 executor.

 Now, in the current model, it's up to the user what indexes to create
 on each partition, and sometimes one might feel that maintaining a
 particular index is unnecessary in some partitions.  But the flip side
 of that is it's awfully easy to screw yourself by forgetting to add
 some index when you add a new partition.  
The forgetting part is easy to solve by inheriting all indexes from
parent (or template) partition unless explicitly told not to.

One other thing that has been bothering me about this proposal
is the ability to take partitions offline for maintenance or to load
them offline ant then switch in.

In current scheme we do this using ALTER TABLE ... [NO] INHERIT ...

If we also want to have this with the not-directly-accessible partitions
then perhaps it could be done by having a possibility to move
a partition between two tables with exactly the same structure ?

 So I'm not real sure which
 approach is superior from a purely user-oriented perspective.
What we currently have is a very flexible scheme which has a few
drawbacks

1) unnecessarily complex for simple case
2) easy to shoot yourself in the foot by forgetting something
3) can be hard on planner, especially with huge number of partitions

An alternative way of solving these problems is adding some
(meta-)constraints to current way of doing things and some more
automation

CREATE TABLE FOR PARTITIONMASTER
WITH (ALL_INDEXES_SAME=ON,
  SAME_STRUCTURE_ALWAYS=ON,
  SINGLE_INHERITANCE_ONLY=ON,
  NESTED_INHERITS=OFF,
  PARTITION_FUNCTION=default_range_partitioning(int)
);

and then force these when adding inherited tables (in this case
partition tables)
either via CREATE TABLE or ALTER TABLE

Best Regards

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 08/29/2014 07:15 PM, Tom Lane wrote:
 Alvaro Herrera alvhe...@2ndquadrant.com writes:
 Tom Lane wrote:
 One other interesting thought that occurs to me: are we going to support
 UPDATEs that cause a row to belong to a different partition?  If so, how
 are we going to handle the update chain links?
 Bah, I didn't mention it?  My current thinking is that it would be
 disallowed; if you have chosen your partitioning key well enough it
 shouldn't be necessary.  As a workaround you can always DELETE/INSERT.
 Maybe we can allow it later, but for a first cut this seems more than
 good enough.
 Hm.  I certainly agree that it's a case that could be disallowed for a
 first cut, but it'd be nice to have some clue about how we might allow it
 eventually.
There needs to be some structure that is specific to partitions and not
multiple plain tables which would then be used for both update chains and
cross-partition indexes (as you seem to imply by jumping from indexes
to update chains a few posts back).

It would need to replace plain tid (pagenr, tupnr) with triple of (partid,
pagenr, tupnr).

Cross-partition indexes are especially needed if we want to allow putting
UNIQUE constraints on non-partition-key columns.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 08/29/2014 05:56 PM, Alvaro Herrera wrote:
 Prompted by a comment in the UPDATE/LIMIT thread, I saw Marko Tiikkaja
 reference Tom's post
 http://www.postgresql.org/message-id/1598.1399826...@sss.pgh.pa.us
 which mentions the possibility of a different partitioning
 implementation than what we have so far.  As it turns out, I've been
 thinking about partitioning recently, so I thought I would share what
 I'm thinking so that others can poke holes.  My intention is to try to
 implement this as soon as possible.


 Declarative partitioning
 
 ...
 Still To Be Designed
 
 * Dependency issues
 * Are indexes/constraints inherited from the parent rel?
I'd say mostly yes.

There could some extra constraint exclusion type magic for
conditional indexes, but the rest probably should come from main table

And there should be some kind of cross-partition indexes. At
 partitioning capability, this can probably wait for version 2.

 * Multiple keys?  
Why not. But probably just for hash partitioning.
 Subpartitioning? 
Probably not. If you need speed for huge numbers of partitions, use
Gregs idea of keeping the partitions in a tree (or just having a
partition index).
  Hash partitioning?
At some point definitely.


Also one thing you left unmentioned is dropping (and perhaps also
truncating)
a partition. We still may want to do historic data management the same way
we do it now, by just getting rid of the whole partition or its data.

At some point we may also want to do redistributing data between
partitions,
maybe for case where we end up with 90% of the data in on partition due to
bad partitioning key or partitioning function choice. This is again
something
that is hard now and can therefore be left to a later version.

 Open Questions
 --

 *  What's the syntax to refer to specific partitions within a partitioned
table?
We could do TABLE xyz PARTITION n, but for example if in
the future we add hash partitioning, we might need some non-integer
addressing (OTOH assigning sequential numbers to hash partitions doesn't
seem so bad).  Discussing with users of other DBMSs partitioning feature,
one useful phrase is TABLE xyz PARTITION FOR value.
Or more generally

TABLE xyz PARTITION FOR/WHERE col1=val1, col2=val2, ...;



Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 08/08/2014 06:17 AM, 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.
How hard and how expensive would it be to teach pg_lzcompress to
apply a delta filter on suitable data ?

So that instead of integers their deltas will be fed to the real
compressor


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] UPDATE SET (a,b,c) = (SELECT ...) versus rules

[Hannu Krosing]

On 06/14/2014 09:35 PM, Tom Lane wrote:
 As I mentioned awhile ago, I'm thinking about implementing the
 SQL-standard construct

   UPDATE foo SET ..., (a,b,...) = (SELECT x,y,...), ...

 I've run into a rather nasty problem, which is how does this interact
 with expansion of NEW references in ON UPDATE rules?  
Was'nt there a plan (consensus?) about deprecating rules altogether ?

Cheers
Hannu
 For example,
 suppose foo has a rule

   ON UPDATE DO ALSO INSERT INTO foolog VALUES (new.a, new.b, ...);

 The existing implementation relies on being able to pull expressions
 for individual fields' new values out of the UPDATE targetlist; but
 there is no independent expression for the new value of a here.
 Worse yet, the NEW references might be in WHERE quals, or some other
 place outside the targetlist of the rule query, which pretty much
 breaks the implementation I'd sketched earlier.

 The best that I think is reasonable to do in such cases is to pull out
 a separate copy of the sub-select for each actual NEW reference in a
 rule query.  So the example above would give rise to an expanded
 rule query along the lines of

   INSERT INTO foolog VALUES ( (SELECT x as a, y as b, ...).a,
   (SELECT x as a, y as b, ...).b,
   ... );

 which would work, but it would re-evaluate the sub-select more times
 than the user might be hoping.  (Of course, if there are volatile
 functions in the sub-select, he's screwed, but that's not a new
 problem with rules.)

 Given that ON UPDATE rules are close to being a deprecated feature,
 it doesn't seem appropriate to work harder than this; and frankly
 I don't see how we could avoid multiple sub-select evaluations anyway,
 if the NEW references are in WHERE or other odd places.

 Another possible answer is to just throw a not implemented error;
 but that doesn't seem terribly helpful, and I think it wouldn't save
 a lot of code anyway.

 Thoughts?

   regards, tom lane




-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] UPDATE SET (a,b,c) = (SELECT ...) versus rules

[Hannu Krosing]

On 06/17/2014 11:22 AM, Vik Fearing wrote:
 On 06/17/2014 09:43 AM, Hannu Krosing wrote:
 On 06/14/2014 09:35 PM, Tom Lane wrote:
 As I mentioned awhile ago, I'm thinking about implementing the
 SQL-standard construct

UPDATE foo SET ..., (a,b,...) = (SELECT x,y,...), ...

 I've run into a rather nasty problem, which is how does this interact
 with expansion of NEW references in ON UPDATE rules?  
 Was'nt there a plan (consensus?) about deprecating rules altogether ?
 I believe that was just for user access to them, ie CREATE RULE.  I
 don't think there was ever question of purging them from the code base.
But are there any cases, where UPDATE rules are created behind the scenes ?

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] RETURNING PRIMARY KEY syntax extension

[Hannu Krosing]

On 06/10/2014 03:19 AM, Tom Dunstan wrote:
 A definite +1 on this feature. A while ago I got partway through
 hacking the hibernate postgres dialect to make it issue a RETURNING
 clause to spit out the primary key before I realised that the driver
 was already doing a RETURNING * internally.

 On 10 June 2014 05:53, Jim Nasby j...@nasby.net
 mailto:j...@nasby.net wrote:

  I was wondering that myself. I think it's certainly reasonable to expect
  someone would wan RETURNING SEQUENCE VALUES, which would return the
 value of
  every column that owned a sequence (ie: ALTER SEQUENCE ... OWNED
 BY). ISTM
  that would certainly handle the performance aspect of this, and it
 sounds
  more in line with what I'd expect getGeneratedKeys() to do.

 Keep in mind that not all generated keys come from sequences. Many
 people have custom key generator functions, including UUIDs and other
 exotic things like Instagram's setup [1].

 RETURNING GENERATED KEYS perhaps, but then how do we determine that?
What about RETURNING CHANGED FIELDS ?

Might be quite complicated technically, but this is what is probably wanted.
 Any column that was filled with a default value? But that's
 potentially returning far more values than the user will want - I bet
 99% of users just want their generated primary key.

Probably not true - you would want your ORM model to be in sync with
what is database after you save it if you plan to do any further
processing using it.

At least I would :)

 The spec is a bit vague [2]: 

 Retrieves any auto-generated keys created as a result of executing
 this Statement object. If this Statement object did not generate any
 keys, an empty ResultSet object is returned.

 Note:If the columns which represent the auto-generated keys were
 not specified, the JDBC driver implementation will determine the
 columns which best represent the auto-generated keys.

 The second paragraph refers to [3] and [4] where the application can
 specify which columns it's after. Given that there's a mechanism to
 specify which keys the application wants returned in the driver, and
 the driver in that case can just issue a RETURNING clause with a
 column list, my gut feel would be to just support returning primary
 keys as that will handle most cases of e.g. middleware like ORMs
 fetching that without needing to know the specific column names.
Why not then just leave the whole thing as it is on server side, and let
the ORM specify which generated keys it wants ?

 Cheers

 Tom


 [1]
 http://instagram-engineering.tumblr.com/post/10853187575/sharding-ids-at-instagram
 [2]
 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys()
 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#getGeneratedKeys%28%29
 [3] 
 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20int[])
 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute%28java.lang.String,%20int[]%29
 [4] 
 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute(java.lang.String,%20java.lang.String[])
 http://docs.oracle.com/javase/7/docs/api/java/sql/Statement.html#execute%28java.lang.String,%20java.lang.String[]%29


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] RETURNING PRIMARY KEY syntax extension

[Hannu Krosing]

On 06/10/2014 11:02 AM, Tom Dunstan wrote:
 On 10 June 2014 17:49, Hannu Krosing ha...@2ndquadrant.com
 mailto:ha...@2ndquadrant.com wrote:

 RETURNING GENERATED KEYS perhaps, but then how do we determine that?
 What about RETURNING CHANGED FIELDS ?

 Might be quite complicated technically, but this is what is
 probably wanted.


 Seems to be getting further away from something that describes the
 main use
 case - changed fields sounds like something that would apply to an
 update statement.
Not really - it applies to both INSERT and UPDATE if there are any
triggers and/or default values

The use-case is an extended version of getting the key, with the main
aim of making sure
that your ORM model is the same as what is saved in database.

 Any column that was filled with a default value? But that's
 potentially returning far more values than the user will want - I
 bet 99% of users just want their generated primary key.

 Probably not true - you would want your ORM model to be in sync
 with what is database after you save it if you plan to do any
 further processing using it.


 Well, yes, but since RETURNING is non-standard most ORMs are unlikely
 to support fetching other generated values that way anyway. The ones
 that I've dealt with will do an insert, then a select to get the extra
 fields. I don't know if other JDBC drivers allow applications to just
 specify any old list of non-key columns to the execute method, but I
 suspect not, given that the way they fetch those columns is rather
 less general-purpose than our RETURNING syntax.
  


 The second paragraph refers to [3] and [4] where the application
 can specify which columns it's after. Given that there's a
 mechanism to specify which keys the application wants returned in
 the driver, and the driver in that case can just issue a
 RETURNING clause with a column list, my gut feel would be to just
 support returning primary keys as that will handle most cases of
 e.g. middleware like ORMs fetching that without needing to know
 the specific column names.
 Why not then just leave the whole thing as it is on server side,
 and let the ORM specify which generated keys it wants ?


 Because java-based ORMs (at least) mostly don't have to - other
 server/driver combos manage to implement getGeneratedKeys() without
 being explicitly given a column list, they just do the sane thing and
 return the appropriate identity column or whatever for the inserted row.
  
 I agree that in hand-crafted JDBC there's no particular problem in
 making a user specify a column list, (although I don't think I've EVER
 seen anyone actually do that in the wild), but most middleware will
 expect getGeneratedKeys() to just work and we should try to do
 something about making that case work a bit more efficiently than it
 does now.
But does the ORM already not know the names of auto-generated keys and
thus could easily replace them for * in RETURNING ?

 Cheers

 Tom


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] RETURNING PRIMARY KEY syntax extension

[Hannu Krosing]

On 06/09/2014 06:58 AM, Ian Barwick wrote:
 Hi,

 The JDBC API provides the getGeneratedKeys() method as a way of
 retrieving
 primary key values without the need to explicitly specify the primary key
 column(s).
Is it defined by the standard, to return _only_ generated primary keys,
and not
for example generated alternate keys ?

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

[Hannu Krosing]

On 05/27/2014 11:00 PM, Tom Lane wrote:
 Andrew Dunstan and...@dunslane.net writes:
 On 05/27/2014 03:57 PM, Heikki Linnakangas wrote:
 On 05/27/2014 10:53 PM, Andrew Dunstan wrote:
 I've been on the receiving end of a couple of mumbles about the fact
 that the JSON rendering code ignores casts of builtin types to JSON.
 This was originally done as an optimization to avoid doing cache lookups
 for casts for things we knew quite well how to turn into JSON values
 (unlike, say, hstore). However, there is at least one concrete case
 where this has some possibly undesirable consequences, namely
 timestamps. Many JSON processors, especially JavaScript/ECMAScript
 processors, require timestamp values to be in ISO 8601 format, with a
 'T' between the date part and the time part, and thus they barf on the
 output we produce for such values.
 I don't understand what ignoring casts of builtin types to JSON means. 
 Can you give an example?
 See src/backend/utils/adt/json.c:json_categorize_type() lines 1280-1300.
 When rendering some value as part of a json string, if a cast exists 
 from the data type to json, then the cast function is used to render the 
 json instead of the type's normal output function, but only if it's not 
 a builtin type.
 How exactly would disabling that code have any effect on timestamp
 rendering?  There's no cast to json from timestamps (nor any other
 builtin type, except jsonb).
I think Andrews idea was, that if cast were used, one could fix the above
problem by defining a correct cast.



 I'd be inclined to think a more useful answer to this issue would be to
 make json.c special-case timestamps, as it already does for numerics.

   regards, tom lane
But I agree that special-casing the code to use the de-facto json standard
of using ISO 8601 date representation is a better solution.

Just make sure you get the TZ part right - this is another place where
PostgreSQL often differs from other systems' understanding of ISO
timestamps.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Priority table or Cache table

[Hannu Krosing]

On 05/26/2014 04:16 PM, Fujii Masao wrote:
 On Sun, May 25, 2014 at 6:52 PM, Hannu Krosing ha...@2ndquadrant.com wrote:
 On 05/20/2014 01:46 PM, Fujii Masao wrote:
 On Mon, Mar 17, 2014 at 1:16 PM, Haribabu Kommi
 kommi.harib...@gmail.com wrote:
 ...
 I Implemented a proof of concept patch to see whether the buffer pool
 split can improve the performance or not.

 Summary of the changes:
 1. The priority buffers are allocated as continuous to the shared buffers.
 2. Added new reloption parameter called buffer_pool to specify the
 buffer_pool user wants the table to use.
 I'm not sure if storing the information of priority table into
 database is good
 because this means that it's replicated to the standby and the same table
 will be treated with high priority even in the standby server. I can imagine
 some users want to set different tables as high priority ones in master and
 standby.
 There might be a possibility to override this in postgresql.conf for
 optimising what you described but for most uses it is best to be in
 the database, at least to get started.
 Overriding the setting in postgresql.conf rather than that in database might
 confuse users because it's opposite order of the priority of the GUC setting.

 Or, what about storig the setting into flat file like replication slot?
seems like a good time to introduce a notion of non-replicated tables :)

should be a good fit with logical replication.

Cheers
Hannu

 Regards,



-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Priority table or Cache table

[Hannu Krosing]

On 05/20/2014 01:46 PM, Fujii Masao wrote:
 On Mon, Mar 17, 2014 at 1:16 PM, Haribabu Kommi
 kommi.harib...@gmail.com wrote:
 ...
 I Implemented a proof of concept patch to see whether the buffer pool
 split can improve the performance or not.

 Summary of the changes:
 1. The priority buffers are allocated as continuous to the shared buffers.
 2. Added new reloption parameter called buffer_pool to specify the
 buffer_pool user wants the table to use.
 I'm not sure if storing the information of priority table into
 database is good
 because this means that it's replicated to the standby and the same table
 will be treated with high priority even in the standby server. I can imagine
 some users want to set different tables as high priority ones in master and
 standby.
There might be a possibility to override this in postgresql.conf for
optimising what you described but for most uses it is best to be in
the database, at least to get started.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Considerer Harmful Considered Harmful categorized as Mostly Harmless

[Hannu Krosing]

On 04/30/2014 02:53 PM, Andrew Dunstan wrote:

 On 04/30/2014 02:35 AM, Heikki Linnakangas wrote:
 On 04/30/2014 01:27 AM, Stephen Frost wrote:
 * Tom Lane (t...@sss.pgh.pa.us) wrote:
 Josh Berkus j...@agliodbs.com writes:
 ... so let's stop using that phrase, OK?
 http://meyerweb.com/eric/comment/chech.html

 Shrug ... what I see there is a rant from a guy with no sense of
 humor.

 +1

 'pt', I say.

 I wasn't sure if the whole article was a parody.




 The rebuttal would be: 'Considered Harmful' Considered Harmful
 Considered Harmful.

 Don't you just love recursion?

Nah, I'd categorize it as Mostly Harmless :)


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] 9.4 Proposal: Initdb creates a single table

[Hannu Krosing]

On 04/24/2014 04:57 PM, Tom Lane wrote:
 Simon Riggs si...@2ndquadrant.com writes:
 On 24 April 2014 05:32, David Fetter da...@fetter.org wrote:
 Since contrib/pgcrypto is a module that might well not be installed,
 people can't just build software for PostgreSQL and have UUIDs
 available, certainly not in the sense that, for example, BIGSERIAL is.
 +1 to include in core - strange to have a UUID datatype in core but no
 means to generate
 The reason why there's no generation function in core is that there is no
 standardized, guaranteed-to-produce-a-universally-unique-value generation
 algorithm.  That was the reason for not putting something in core when the
 type was first created, and I do not see that the technology has advanced.
Why can't we implement all 5 variants from 
http://en.wikipedia.org/wiki/Universally_unique_identifier
and just warn about the dangers in documentation ?

we could expose it something like next_uuid(version nr);

As the article points out  Since the identifiers have a finite size, it is
possible for two differing items to share the same identifier. so it is a
known limitation of UUID and not something PostgreSQL specific.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] 9.4 Proposal: Initdb creates a single table

[Hannu Krosing]

On 04/23/2014 08:11 AM, Simon Riggs wrote:
 We start with a database called Postgres and a schema called Public.
 Yet we don't start up with any usable tables.

 I propose we add a single table called Postgres when we Initdb

   CREATE TABLE Postgres (Id Integer, Data Jsonb);
   COMMENT ON TABLE Postgres IS 'Single table for quick start usage -
 design your database';

 The purpose of this is to make the database immediately usable.
With default access controls it still aint :(

And most of complaints I have heard are about the need of
fidgeting with pg_hba.conf to get *anything* started.

So maybe we could start with something like this at the end of initdb:

 createdb example

 psql example

sample# create table stuff(id serial primary key, data jsonb);
sample# grant all on stuff to public;

And also have the following lines in pg_hba.conf for it

local   all all trust
hostall all 127.0.0.1/32trust
hostall all ::1/128 trust


This would solve most of the frustration with starting pg development
for newcomers

Cheers
Hannu

  By
 including this table in the default  initdb it will mean that programs
 can rely on the existence of this table and begin working quicker.

 By now, some of you will be doubled over laughing as if this is an
 April fool joke.  I don't mean it to be at all.

 The idea is to have a stupidly obvious and easy table that will
 potentially be usable by just about everyone, in any language.

 If you don't like it, don't use it. If you really dislike it, drop it.

 But for new people coming to Postgres, they will have a data object to
 access and begin using the database immediately. Their code will work,
 their examples will work. OK, so they need to go back and think about
 the design, but at least they got it to work and will be encouraged to
 do more.

 Remember when we didn't have a database called Postgres? Remember how
 much simpler life is now? Remember that now.

 We can also insert a single row, Id = 0 with Postgres sample data in
 it, but that seems a step too far.



-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Clock sweep not caching enough B-Tree leaf pages?

[Hannu Krosing]

On 04/17/2014 10:39 PM, Andres Freund wrote:
 On 2014-04-17 13:33:27 -0700, Peter Geoghegan wrote:
 Just over 99.6% of pages (leaving aside the meta page) in the big 10
 GB pgbench_accounts_pkey index are leaf pages.

What is the depth of b-tree at this percentage ?

Cheers
Hannu


-- 
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] AXLE Plans for 9.5 and 9.6

[Hannu Krosing]

On 04/22/2014 01:24 AM, Josh Berkus wrote:
 On 04/21/2014 03:41 PM, Simon Riggs wrote:
 Storage Efficiency
 * Compression
 * Column Orientation
 You might look at turning this:

 http://citusdata.github.io/cstore_fdw/

 ... into a more integrated part of Postgres.
What would be of more general usefulness is probably
better planning and better performance of FDW interface.

So instead of integrating one specific FDW it would make
sense to improve postgresql so that it can use (properly written)
FDWs at native speeds

Regards

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] AXLE Plans for 9.5 and 9.6

[Hannu Krosing]

On 04/22/2014 02:04 PM, Simon Riggs wrote:
 On 22 April 2014 00:24, Josh Berkus j...@agliodbs.com wrote:
 On 04/21/2014 03:41 PM, Simon Riggs wrote:
 Storage Efficiency
 * Compression
 * Column Orientation
 You might look at turning this:

 http://citusdata.github.io/cstore_fdw/

 ... into a more integrated part of Postgres.
 Of course I'm aware of that work - credit to them. Certainly, many
 people feel that it is now time to do as you suggest and include
 column store features within PostgreSQL.

 As to turning it into a more integrated part of Postgres, we have a
 few problems there

 1. cstore_fdw code has an incompatible licence

 2. I don't think FDWs are the right place for complex new
 architectures such as column store, massively parallel processing or
 sharding. 
I agree that FDW is not an end-all solution for all these, but it is a
reasonable starting point and it just might be that the extra things
needed could be added to our FDW API instead of sewing it directly
into backend guts.


I recently tried to implement sharding at FDW level and the main
problem I ran into was a missing join type for efficiently using it
for certain queries.

The specific use case was queries of form

select l.*, r*
from remotetable r
join localtable l
on l.key1 = r.id and l.n = N;

PostgreSQL offered only two options:

1) full scan on remote table

2) single id=$ selects

neither of which are what is actually needed, as firs performs badly
if there are more than a few rows in remote table and 2nd performs
badly if l.n = N returns more than a few rows

when I manually rewrote the query to

select l.*, r*
from remotetable r where r.id = ANY(ARRAY(select key1 from localtable
where n = N))
join localtable l
on l.key1 = r.id and l.n = N;

it run really well.

Unfortunately this is not something that postgreSQL considers by itself
while optimising.

BTW, this kind of optimisation should also be a win for really large IN
queries if we
could have an indexed IN whic would not start each lookup from the index
root, but
rather would sort the IN contents and do an index merge vis skipping
from current position.


Cheers










-- 
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] DISCARD ALL (Again)

[Hannu Krosing]

On 04/18/2014 01:38 AM, Fabrízio de Royes Mello wrote:



 On Thu, Apr 17, 2014 at 6:51 PM, Alvaro Herrera
 alvhe...@2ndquadrant.com mailto:alvhe...@2ndquadrant.com wrote:
 
  It does sounds a legitimate feature request to me.  I don't remember if
  we honored the request to add resetting of cached sequences, though; if
  we didn't, this one is probably going to be tough too.
 

 +1


  Another point is that to implement this I think there will need to be
  another per-PL entry point to discard session data; are we okay with
  that?  Since this probably means a new column in pg_language, we
  couldn't even consider the idea of back-patching.  Unless we add a hook,
  which is registered in the PL's _PG_init()?
 

 This week I had some similar trouble, but using dblink and pgbouncer.
 As expected DISCARD ALL don't clear the extension resources.

Should DISCARD ALL to also clear cached connections in pl/proxy ?

Cheers
Hannu

 I was thinking if is possible to every extension register his own
 discard procedure and then the DISCARD ALL can execute all
 registered extension cleanup procedures. Makes sense?


  Are we going to backpatch a doc change that says releases all temporary
  resources, except for plptyhon's and plperl's GD?  Surely not ...
 

 Maybe this doc can be like that:

 releases all temporary resources, except for extensions

 Grettings,

 --
 Fabrízio de Royes Mello
 Consultoria/Coaching PostgreSQL
  Timbira: http://www.timbira.com.br
  Blog sobre TI: http://fabriziomello.blogspot.com
  Perfil Linkedin: http://br.linkedin.com/in/fabriziomello
  Twitter: http://twitter.com/fabriziomello

Re: [HACKERS] DISCARD ALL (Again)

[Hannu Krosing]

On 04/18/2014 06:44 PM, Joshua D. Drake wrote:

 On 04/18/2014 08:01 AM, Peter Eisentraut wrote:

 On 4/17/14, 4:44 PM, Joshua D. Drake wrote:
 That we should also release the GD?

 In some cases, SD or GD are used to cache things.  Having the connection
 pooler blow that away would defeat the point.

 Not on a per session basis. Although I can see your point.
 The GD is supposed to be global per session.
 If, I discard the session to it's original state, that is going to
 predate the creation of the GD. That is expected behavior.
The reason (I assume) you want DISCARD ALL instead of reconnect is
performance.

Often stuff (like another connection) is cached in GD also for performance.

Another things I sometimes do in pl/pythonu is add my own functions to
__builtins__
and also use pl/python functions as modules by having some global
state in definitions.

The only way to automatically reset all that would be complete reset of
interpreter (as Tom mentioned above)

For these reasons I suggest that the cleanest way to achieve discard
all for pl/python
would be ability to register a clean-up function and not trying to
second-guess what
global cached state *might* exists.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Question about optimising (Postgres_)FDW

[Hannu Krosing]

On 04/16/2014 01:35 PM, Etsuro Fujita wrote:
 (2014/04/16 6:55), Hannu Krosing wrote:
...

 Maybe I'm missing something, but I think that you can do what I think
 you'd like to do by the following procedure:
No, what I'd like PostgreSQL to do is to

1. select the id+set from local table
2. select the rows from remote table with WHERE ID IN (set selected in
step 1)
3. then join the original set to selected set, with any suitable join
strategy

The things I do not want are

A. selecting all rows from remote table
(this is what your examples below do)

or

B. selecting rows from remote table by single selects using ID = $
(this is something that I managed to do by some tweaking of costs)

as A will be always slow if there are millions of rows in remote table
and B is slow(ish) when the idset is over a few hundred ids

I hope this is a bit better explanation than I provided before .

Cheers
Hannu

P.S. I am not sure if this is a limitation of postgres_fdw or postgres
itself

P.P.S I tested a little with with Multicorn an postgresql did not
request row
counts for any IN plans, so it may be that the planner does not consider
this
kind of plan at all. (testing was on PgSQL 9.3.4)

Hannu

 postgres=# ALTER SERVER loop OPTIONS (ADD fdw_startup_cost '1000');
 ALTER SERVER
 postgres=# EXPLAIN VERBOSE SELECT * FROM onemillion_pgsql WHERE id in
 (SELECT id FROM onemillion WHERE data  '0.9' LIMIT 100);
   QUERY PLAN
 ---

  Hash Semi Join  (cost=1023.10..41983.21 rows=100 width=30)
Output: onemillion_pgsql.id, onemillion_pgsql.inserted,
 onemillion_pgsql.data
Hash Cond: (onemillion_pgsql.id = onemillion.id)
-  Foreign Scan on public.onemillion_pgsql 
 (cost=1000.00..39334.00 rows=100 width=29)
  Output: onemillion_pgsql.id, onemillion_pgsql.inserted,
 onemillion_pgsql.data
  Remote SQL: SELECT id, inserted, data FROM public.onemillion
-  Hash  (cost=21.85..21.85 rows=100 width=4)
  Output: onemillion.id
  -  Limit  (cost=0.00..20.85 rows=100 width=4)
Output: onemillion.id
-  Seq Scan on public.onemillion  (cost=0.00..20834.00
 rows=99918 width=4)
  Output: onemillion.id
  Filter: (onemillion.data  '0.9'::text)
  Planning time: 0.690 ms
 (14 rows)

 or, that as Tom mentioned, by disabling the use_remote_estimate function:

 postgres=# ALTER FOREIGN TABLE onemillion_pgsql OPTIONS (SET
 use_remote_estimate 'false');
 ALTER FOREIGN TABLE
 postgres=# EXPLAIN VERBOSE SELECT * FROM onemillion_pgsql WHERE id in
 (SELECT id FROM onemillion WHERE data  '0.9' LIMIT 100);
   QUERY PLAN
 --

  Hash Semi Join  (cost=123.10..41083.21 rows=100 width=30)
Output: onemillion_pgsql.id, onemillion_pgsql.inserted,
 onemillion_pgsql.data
Hash Cond: (onemillion_pgsql.id = onemillion.id)
-  Foreign Scan on public.onemillion_pgsql  (cost=100.00..38434.00
 rows=100 width=30)
  Output: onemillion_pgsql.id, onemillion_pgsql.inserted,
 onemillion_pgsql.data
  Remote SQL: SELECT id, inserted, data FROM public.onemillion
-  Hash  (cost=21.85..21.85 rows=100 width=4)
  Output: onemillion.id
  -  Limit  (cost=0.00..20.85 rows=100 width=4)
Output: onemillion.id
-  Seq Scan on public.onemillion  (cost=0.00..20834.00
 rows=99918 width=4)
  Output: onemillion.id
  Filter: (onemillion.data  '0.9'::text)
  Planning time: 0.215 ms
 (14 rows)

 Thanks,

 Best regards,
 Etsuro Fujita





-- 
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] Question about optimising (Postgres_)FDW

[Hannu Krosing]

On 04/16/2014 03:16 PM, Hannu Krosing wrote:
 On 04/16/2014 01:35 PM, Etsuro Fujita wrote:
 (2014/04/16 6:55), Hannu Krosing wrote:
 ...
 Maybe I'm missing something, but I think that you can do what I think
 you'd like to do by the following procedure:
 No, what I'd like PostgreSQL to do is to

 1. select the id+set from local table
 2. select the rows from remote table with WHERE ID IN (set selected in
 step 1)
 3. then join the original set to selected set, with any suitable join
 strategy

 The things I do not want are

 A. selecting all rows from remote table
 (this is what your examples below do)

 or

 B. selecting rows from remote table by single selects using ID = $
 (this is something that I managed to do by some tweaking of costs)

 as A will be always slow if there are millions of rows in remote table
 and B is slow(ish) when the idset is over a few hundred ids

 I hope this is a bit better explanation than I provided before .

 Cheers
 Hannu

 P.S. I am not sure if this is a limitation of postgres_fdw or postgres
 itself

 P.P.S I tested a little with with Multicorn an postgresql did not
 request row
 counts for any IN plans, so it may be that the planner does not consider
 this
 kind of plan at all. (testing was on PgSQL 9.3.4)

 Hannu
Also a sample run of the two plans to illustrate my point

How it is run now:

testdb=# explain analyse verbose
select r.data, l.data
  from onemillion_pgfdw r
  join onemillion l
on r.id = l.id and l.id between 10 and 100100;
 
QUERY
PLAN 
--
 Hash Join  (cost=111.61..198.40 rows=1 width=16) (actual
time=7534.360..8731.541 rows=101 loops=1)
   Output: r.data, l.data
   Hash Cond: (r.id = l.id)
   -  Foreign Scan on public.onemillion_pgfdw r  (cost=100.00..178.25
rows=2275 width=12) (actual time=1.628..8364.688 rows=100 loops=1)
 Output: r.id, r.inserted, r.data
 Remote SQL: SELECT id, data FROM public.onemillion
   -  Hash  (cost=10.39..10.39 rows=98 width=12) (actual
time=0.179..0.179 rows=101 loops=1)
 Output: l.data, l.id
 Buckets: 1024  Batches: 1  Memory Usage: 5kB
 -  Index Scan using onemillion_pkey on public.onemillion l 
(cost=0.42..10.39 rows=98 width=12) (actual time=0.049..0.124 rows=101
loops=1)
   Output: l.data, l.id
   Index Cond: ((l.id = 10) AND (l.id = 100100))
 Total runtime: 8732.213 ms
(13 rows)

Time: 8733.799 ms


And how the above query should be planned/executed:

testdb=# explain analyse verbose
select r.data, l.data
  from (select * from onemillion_pgfdw where id = any (array(select id
from onemillion where id between 10 and 100100))) r
  join onemillion l
on r.id = l.id;

QUERY
PLAN

 Nested Loop  (cost=110.81..1104.30 rows=111 width=16) (actual
time=2.756..3.738 rows=101 loops=1)
   Output: onemillion_pgfdw.data, l.data
   InitPlan 1 (returns $0)
 -  Index Only Scan using onemillion_pkey on public.onemillion 
(cost=0.42..10.39 rows=98 width=4) (actual time=0.055..0.118 rows=101
loops=1)
   Output: onemillion.id
   Index Cond: ((onemillion.id = 10) AND (onemillion.id =
100100))
   Heap Fetches: 101
   -  Foreign Scan on public.onemillion_pgfdw  (cost=100.00..163.41
rows=111 width=12) (actual time=2.729..3.012 rows=101 loops=1)
 Output: onemillion_pgfdw.id, onemillion_pgfdw.inserted,
onemillion_pgfdw.data
 Remote SQL: SELECT id, data FROM public.onemillion WHERE ((id =
ANY ($1::integer[])))
   -  Index Scan using onemillion_pkey on public.onemillion l 
(cost=0.42..8.37 rows=1 width=12) (actual time=0.005..0.006 rows=1
loops=101)
 Output: l.id, l.inserted, l.data
 Index Cond: (l.id = onemillion_pgfdw.id)
 Total runtime: 4.469 ms
(14 rows)

Time: 6.437 ms




 postgres=# ALTER SERVER loop OPTIONS (ADD fdw_startup_cost '1000');
 ALTER SERVER
 postgres=# EXPLAIN VERBOSE SELECT * FROM onemillion_pgsql WHERE id in
 (SELECT id FROM onemillion WHERE data  '0.9' LIMIT 100);
   QUERY PLAN
 ---

  Hash Semi Join  (cost=1023.10..41983.21 rows=100 width=30)
Output: onemillion_pgsql.id, onemillion_pgsql.inserted,
 onemillion_pgsql.data
Hash Cond: (onemillion_pgsql.id = onemillion.id)
-  Foreign Scan on public.onemillion_pgsql 
 (cost=1000.00..39334.00 rows=100 width=29)
  Output

[HACKERS] Question about optimising (Postgres_)FDW

[Hannu Krosing]

Hi

I am playing around with postgres_fdw and found that the following code ...

--
CREATE EXTENSION postgres_fdw;

CREATE SERVER loop foreign data wrapper postgres_fdw
  OPTIONS (port '5432', dbname 'testdb');
 
CREATE USER MAPPING FOR PUBLIC SERVER loop;

create table onemillion (
id serial primary key,
inserted timestamp default clock_timestamp(),
data text
);

insert into onemillion(data) select random() from
generate_series(1,100);

CREATE FOREIGN TABLE onemillion_pgfdw (
id int,
inserted timestamp,
data text
) SERVER loop
OPTIONS (table_name 'onemillion',
 use_remote_estimate 'true');

testdb=# explain analyse
select * from onemillion_pgfdw where id in (select id from onemillion
where data  '0.9' limit 100);
   QUERY
PLAN   
-
 Nested Loop  (cost=122.49..10871.06 rows=50 width=44) (actual
time=4.269..93.444 rows=100 loops=1)
   -  HashAggregate  (cost=22.06..23.06 rows=100 width=4) (actual
time=1.110..1.263 rows=100 loops=1)
 -  Limit  (cost=0.00..20.81 rows=100 width=4) (actual
time=0.038..1.026 rows=100 loops=1)
   -  Seq Scan on onemillion  (cost=0.00..20834.00
rows=100115 width=4) (actual time=0.036..0.984 rows=100 loops=1)
 Filter: (data  '0.9'::text)
 Rows Removed by Filter: 805
   -  Foreign Scan on onemillion_pgfdw  (cost=100.43..108.47 rows=1
width=29) (actual time=0.772..0.773 rows=1 loops=100)
 Total runtime: 93.820 ms
(8 rows)

Time: 97.283 ms
--

... actually performs 100 distinct SELECT * FROM onemillion WHERE id =
$1 calls on remote side.

Is there a way to force it to prefer a plan where the results of (select
id from onemillion where data  '0.9' limit 100)
are passed to FDW as a single IN ( = ANY(...)) query and are retrieved
all at once ?

If not, how hord would it be to add this feature ?

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Question about optimising (Postgres_)FDW

[Hannu Krosing]

On 04/16/2014 01:25 AM, Tom Lane wrote:
 Hannu Krosing ha...@2ndquadrant.com writes:
 Is there a way to force it to prefer a plan where the results of (select
 id from onemillion where data  '0.9' limit 100)
 are passed to FDW as a single IN ( = ANY(...)) query and are retrieved
 all at once ?
 You could write the query like that:

 select * from onemillion_pgfdw where id = any (array(select id from
 onemillion where data  '0.9' limit 100));
My actual use-case was about a join between a local and a remote table
and without rewriting the query (they come from ORM)

I was hoping to be able to nudge postgresql towards a better plan via some
tuning of table/fdw options or GUCs.

for example, would postgresql use the WHERE id IN (...) query on remote
side for a query like

select r.data, l.data
  from onemillion_pgfdw r
  join onemillion l
on r.id = l.id and l.data  '0.999';

if it recognizes that the local side returns only 1000 rows ?

or would it still use 1000 individual WHERE id = $1 queries.

Is getting the foreign data via IN and then turning the data into a hash
for joining one of the plans it considers at all ?

Best
Hannu


 Or at least you should be able to, except when I try it I get

 explain analyze
 select * from onemillion_pgfdw where id = any (array(select id from
 onemillion where data  '0.9' limit 100));
 ERROR:  operator does not exist: integer = integer[]
 HINT:  No operator matches the given name and argument type(s). You might 
 need to add explicit type casts.
 CONTEXT:  Remote SQL command: EXPLAIN SELECT id, inserted, data FROM 
 public.onemillion WHERE ((id = ANY ((SELECT null::integer[]

 so there's something the remote-estimate code is getting wrong here.
 (It seems to work without remote_estimate, though.)

   regards, tom lane


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Question about optimising (Postgres_)FDW

[Hannu Krosing]

On 04/16/2014 06:12 AM, Hannu Krosing wrote:
 On 04/16/2014 01:25 AM, Tom Lane wrote:
 Hannu Krosing ha...@2ndquadrant.com writes:
 Is there a way to force it to prefer a plan where the results of (select
 id from onemillion where data  '0.9' limit 100)
 are passed to FDW as a single IN ( = ANY(...)) query and are retrieved
 all at once ?
 You could write the query like that:

 select * from onemillion_pgfdw where id = any (array(select id from
 onemillion where data  '0.9' limit 100));
 My actual use-case was about a join between a local and a remote table
 and without rewriting the query (they come from ORM)

 I was hoping to be able to nudge postgresql towards a better plan via some
 tuning of table/fdw options or GUCs.

 for example, would postgresql use the WHERE id IN (...) query on remote
 side for a query like

 select r.data, l.data
   from onemillion_pgfdw r
   join onemillion l
 on r.id = l.id and l.data  '0.999';

 if it recognizes that the local side returns only 1000 rows ?

 or would it still use 1000 individual WHERE id = $1 queries.

 Is getting the foreign data via IN and then turning the data into a hash
 for joining one of the plans it considers at all ?
It sees that could we need an extra tuning parameter for choosing the

ID IN (...) + HASH plan over individual SELECT .. WHERE ID = $1

something between

`fdw_startup_cost` and `fdw_tuple_cost`

to signify that an IN query returning 1000 rows runs faster than 1000 =
queries

as I understan currently they both would be estimated as

fdw_startup_cost + 1000 * fdw_tuple_cost

the new parameter could be fdw_call_cost or fdw_query_cost and would
estimate
how much each individual call to fdw costs, thus favouring calls which
return more
data in one call

Cheers
Hannu

 Best
 Hannu

 Or at least you should be able to, except when I try it I get

 explain analyze
 select * from onemillion_pgfdw where id = any (array(select id from
 onemillion where data  '0.9' limit 100));
 ERROR:  operator does not exist: integer = integer[]
 HINT:  No operator matches the given name and argument type(s). You might 
 need to add explicit type casts.
 CONTEXT:  Remote SQL command: EXPLAIN SELECT id, inserted, data FROM 
 public.onemillion WHERE ((id = ANY ((SELECT null::integer[]

 so there's something the remote-estimate code is getting wrong here.
 (It seems to work without remote_estimate, though.)

  regards, tom lane



-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Autonomous Transaction (WIP)

[Hannu Krosing]

On 04/09/2014 08:44 AM, Pavan Deolasee wrote:
 On Wed, Apr 9, 2014 at 11:03 AM, Rajeev rastogi
 rajeev.rast...@huawei.com mailto:rajeev.rast...@huawei.com wrote:


 Though autonomous transaction uses mixed approach of
 sub-transaction as well as main
 transaction, transaction state of autonomous transaction is
 handled independently.


 Whenever I was asked to have a look at implementing this feature, I
 always wondered about the great amount of global state that a backend
 maintains which is normally tied to a single top transaction. Since AT
 will have same characteristics as a top level transaction, I wonder
 how do you plan to separate those global state variables ? Sure, we
 can group them in a structure and put them on a stack when an AT
 starts and pop them off when the original top transaction becomes
 active again, finding all such global state variables is going to be
 tricky.
I would hope most of this to be solved by having one (read only) virtual
transaction and
then juggling the ATs in a way similar to current subtransaction machinery.

The main differences would be that:

 A) the top level transaction stays virtual

and

 B) ATs are committed independantly

This would be greatly simplified if we can accept the restriction that
there is only single
snapshot per backend (not per transaction). To me this seems a
completely sensible restriction.

Re syntax, I think we need a way to name the transactions so we can have
a way
to switch between multiple parallel active autonomous transactions.

-
BEGIN TRANSACTION myfirsttransaction;

do something in myfirsttransaction;

BEGIN TRANSACTION anothertransaction;

do something in anothertransaction;

SET TRANSACTION myfirsttransaction;

more work in myfirsttransaction;

ROLLBACK anothertransaction;

COMMIT; -- or COMMIT myfirsttransaction;


Cheers
Hannu



 Thanks,
 Pavan

 -- 
 Pavan Deolasee
 http://www.linkedin.com/in/pavandeolasee

Re: [HACKERS] json(b) equality rules

[Hannu Krosing]

On 04/03/2014 04:32 AM, Oleg Bartunov wrote:
 Hi there,

 I'm wondering if we should follow all js equility rules as
 nicely visualized in
 http://strilanc.com/visualization/2014/03/27/Better-JS-Equality-Table.html
Probably not as JSON is general interchange format.

If somebody wants JavaScript rules, they can use pl/v8

Any equality operations specific for JSON should be related
to array and object/dictionary equality and not data
store inside JSON


Cheers
Hannu


-- 
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] GSoC proposal - make an unlogged table logged

[Hannu Krosing]

On 03/03/2014 05:22 PM, Tom Lane wrote:
 Stephen Frost sfr...@snowman.net writes:
...
 ISTR the discussion going something along the lines of we'd have to WAL
 log the entire table to do that, and if we have to do that, what's the
 point?.
 IIRC, the reason you'd have to do that is to make the table contents
 appear on slave servers.  If you don't consider replication then it might
 seem easier.
So switch on logging and then perform CLUSTER/VACUUM FULL ?

Should this work, or is something extra needed ?

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] jsonb and nested hstore

[Hannu Krosing]

On 02/26/2014 09:17 AM, Christophe Pettus wrote:
 On Feb 25, 2014, at 1:57 PM, Hannu Krosing ha...@2ndquadrant.com wrote:

 It is not in any specs, but nevertheless all major imlementations do it and
 some code depends on it.
 I have no doubt that some code depends on it, but all major implementations 
 is 
 too strong a statement.  BSON, in particular, does not have stable field 
 order.
First, BSON is not JSON :)

And I do not really see how the don't preserve the field order - the
structure
is pretty similar to tnetstrings, just binary concatenation of datums
with a bit
more types.

It is possible that some functions on BSON do not preserve it for some
reason ...


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] jsonb and nested hstore

[Hannu Krosing]

On 02/26/2014 07:41 PM, Josh Berkus wrote:
 On 02/26/2014 07:02 AM, Merlin Moncure wrote:
 On Tue, Feb 25, 2014 at 3:57 PM, Hannu Krosing ha...@2ndquadrant.com wrote:
 It is not in any specs, but nevertheless all major imlementations do it and
 some code depends on it.
 IIRC, this behaviour is currently also met only by json and not by jsonb.
 Yes: This was the agreement that was struck and is the main reason why
 there are two json types, not one.  JSON does not guarantee field
 ordering as I read the spec and for the binary form ordering is not
 maintained as a concession to using the hstore implementation.
 Actually, that's not true; neither Mongo/BSON nor CouchDB preserve field
 ordering.  
That is strange at least for BSON, as it does not have any nearly as
sophisticated
internal format as hstore - no hash tables or anything, just a binary
serialisation.
It would take an extra effort to *not* keep the order there :)

http://bsonspec.org/#/specification


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] jsonb and nested hstore

[Hannu Krosing]

On 02/25/2014 08:54 PM, Josh Berkus wrote:
 That's called a straw man argument, Robert.
 Me: We should recommend that people use jsonb unless they have a
 specific reason for using json.
We could also make the opposite argument - people use json unless they
have a specific reason for using jsonb.

btw, there is one more thing about JSON which I recently learned - a lot of
JavaScript people actually expect the JSON binary form to retain field order

It is not in any specs, but nevertheless all major imlementations do it and
some code depends on it.
IIRC, this behaviour is currently also met only by json and not by jsonb.

 Merlin: We should present them side-by-side with a complex comparison.
 Robert: Josh wants to junk all relational data and use only jsonb! I
 mean, really, WTF? 

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] WAL Rate Limiting

[Hannu Krosing]

On 02/20/2014 04:16 PM, Robert Haas wrote:
 On Thu, Feb 20, 2014 at 9:43 AM, Simon Riggs si...@2ndquadrant.com wrote:
 The design choice of making the limit only apply to bulk ops is
 because that is where the main problem lies. Rate limiting will cause
 a loss of performance in the main line for non-bulk operations, so
 adding tests there will not be valuable.
 That's pure sophistry.  Of course rate limiting will cause a loss of
 performance in the main line for non-bulk operations. 
I think he meant that *adding code for* rate limiting there will cause
loss of performance even if not used.
  Rate limiting
 will also cause a loss of performance for bulk operations.  The whole
 point of rate limiting is to cause a loss of performance.
Not when it is not enabled it should not.
   That's not
 a bug; that's what the feature is explicitly designed to do.

 So-called non-bulk operations, which seems to be defined as more or
 less anything where actually making this feature work seemed hard,
NO, it is anything that a decent DBA could not script to run in
chunks, which he would do anyway for other reasons, like not
to lock out OLTP.
 can include huge inserts, updates, or deletes that greatly depress
 performance for other parts of the system, either individually or in
 aggregate.  And it's just as legitimate to want to tamp down that
 activity as it is to want to slow down CLUSTER.  Remember, this is a
 GUC, so it need not be set identically for every user session.  It is
 not hard at all to imagine a situation where the user wishes to limit
 the rate at which some sessions can write WAL so as to avoid
 interfering with other, higher-priority tasks happening in other
 sessions. 
It is hard to imagine, but it is still a much more infrequent than needing
 to do concurrent ops like VACUUM and CREATE INDEX CONCURRENTLY .
  That is hardly a niche use case; I think I've seen it
 reported, if anything, even more frequently than problems with what
 you're calling bulk operations.
Could be, but they are two separate cases. One helps DBA get stuff
done without stepping on users toes, the other is about protecting
one user from the other.

It is arguable that the first is a sub-case of the other, but I don't think
these should be controlled by the same GUC though you may want to
have some checking for sane values between the two

Maybe call this one `maintenance_wal_rate_limit_delay` same way as we 
have `maintenance_work_mem`


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Ctrl+C from sh can shut down daemonized PostgreSQL cluster

[Hannu Krosing]

On 02/15/2014 02:25 AM, Greg Stark wrote:


 On 14 Feb 2014 23:07, Tom Lane t...@sss.pgh.pa.us
 mailto:t...@sss.pgh.pa.us wrote:
 
  If this is, as it sounds to be, a Solaris shell bug, doesn't it
  affect other daemons too?

 This is simmering i never exactly followed but i think if the shell
 doesn't support job control it's expected behaviour, not a bug. Only
 shells that support job control create new process groups for every
 backgrounded command.

 I would have expected if I run postgres myself that it be attached to
 the terminal and die when I C-c it but if it's started by pg_ctl I
 would have thought it was running independently of my terminal and shell.

In this case maybe it is pg_ctl which should do the deamoinizing ?


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] jsonb and nested hstore

[Hannu Krosing]

On 02/11/2014 01:16 AM, Merlin Moncure wrote:
 On Mon, Feb 10, 2014 at 5:52 PM, Andres Freund and...@2ndquadrant.com wrote:
 It works in enough cases atm that it's worthwile trying to keep it
 working. Sure, it could be better, but it's what we have right now. Atm
 it's e.g. the only realistic way to copy larger amounts of bytea between
 servers without copying the entire cluster.
 That's the thing -- it might work today, but what about tomorrow?
 We'd be sending the wrong signals.  People start building processes
 around all of this and now we've painted ourselves into a box.  Better
 in my mind to simply educate users that this practice is dangerous and
 unsupported, as we used to do. I guess until now.  It seems completely
 odd to me that we're attaching a case to the jsonb type, in the wrong
 way -- something that we've never attached to any other type before.
 For example, why didn't we attach a version code to the json type send
 function?  
JSON is supposed to be a *standard* way of encoding data in
strings. If the ever changes, it will not be JSON type anymore.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] jsonb and nested hstore

[Hannu Krosing]

On 02/05/2014 06:48 PM, Tom Lane wrote:
 Andrew Dunstan and...@dunslane.net writes:
 On 02/05/2014 11:40 AM, Tom Lane wrote:
 switching to binary is the same as text may well be the most prudent
 path here.
 If we do that we're going to have to live with that forever, aren't we? 
 Yeah, but the other side of that coin is that we'll have to live forever
 with whatever binary format we pick, too.  If it turns out to be badly
 designed, that could be much worse than eating some parsing costs during
 dump/restore.
The fastest and lowest parsing cost format for JSON is tnetstrings
http://tnetstrings.org/ why not use it as the binary wire format ?

It would be as binary as it gets and still be generally parse-able by
lots of different platforms, at leas by all of these  we care about.

Cheers
Hannu


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

Re: [HACKERS] jsonb and nested hstore

[Hannu Krosing]

On 02/10/2014 11:05 AM, Andres Freund wrote:
 Hi,

 On 2014-02-06 18:47:31 -0500, Andrew Dunstan wrote:
  * switching to using text representation in jsonb send/recv
 +/*
 + * jsonb type recv function
 + *
 + * the type is sent as text in binary mode, so this is almost the same
 + * as the input function.
 + */
 +Datum
 +jsonb_recv(PG_FUNCTION_ARGS)
 +{
 +StringInfo  buf = (StringInfo) PG_GETARG_POINTER(0);
 +text   *result = cstring_to_text_with_len(buf-data, buf-len);
 +
 +return deserialize_json_text(result);
 +}
 +/*
 + * jsonb type send function
 + *
 + * Just send jsonb as a string of text
 + */
 +Datum
 +jsonb_send(PG_FUNCTION_ARGS)
 +{
 +Jsonb  *jb = PG_GETARG_JSONB(0);
 +StringInfoData buf;
 +char   *out;
 +
 +out = JsonbToCString(NULL, (JB_ISEMPTY(jb)) ? NULL : VARDATA(jb), 
 VARSIZE(jb));
 +
 +pq_begintypsend(buf);
 +pq_sendtext(buf, out, strlen(out));
 +PG_RETURN_BYTEA_P(pq_endtypsend(buf));
 +}
 I'd suggest making the format discernible from possible different future
 formats, to allow introducing a proper binary at some later time. Maybe
 just send a int8 first, containing the format.
+10

Especially as this is one type where we may want add type-specific
compression options at some point


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] jsonb and nested hstore

[Hannu Krosing]

On 01/30/2014 06:45 PM, Andrew Dunstan wrote:

 On 01/30/2014 12:34 PM, Merlin Moncure wrote:
 On Thu, Jan 30, 2014 at 9:50 AM, Andrew Dunstan and...@dunslane.net
 wrote:
 Now, if we're agreed on that, I then also wonder if the 'as_text'
 argument needs to exist at all for the populate functions except for
 backwards compatibility on the json side (not jsonb).  For
 non-complex
 structures it does best effort casting anyways so the flag is moot.

 Well, I could certainly look at making the populate_record{set} and
 to_record{set} logic handle types that are arrays or composites
 inside the
 record. It might not be terribly hard to do - not sure.
 A quick analysis suggests that this is fixable with fairly minimal
 disturbance in the jsonb case. 
As row_to_json() works with arbitrarily complex nested types (for
example row having a field
of type array of another (table)type containing arrays of third type) it
would be really nice if
you can get the result back into that row without too much hassle.

and it should be ok to treat json as source type and require it to be
translated to jsonb
for more complex operations
 In the json case it would probably involve
 reparsing the inner json. That's probably doable, because the
 routines are
 all reentrant, but not likely to be terribly efficient. It will also
 be a
 deal more work.
 Right.  Also the text json functions are already in the wild anyways
 -- that's not in the scope of this patch so if they need to be fixed
 that could be done later.

 ISTM then the right course of action is to point jsonb 'populate'
 variants at hstore implementation, not the text json one and remove
 the 'as text' argument.  Being able to ditch that argument is the main
 reason why I think this should be handled now (not forcing hstore
 dependency to handle complex json is gravy).


 We can't reference any hstore code in jsonb. There is no guarantee
 that hstore will even be loaded.

 We'd have to move that code from hstore to jsonb_support.c and then
 make hstore refer to it.
Or just copy it and leave hstore alone - the code duplication is not
terribly huge
here and hstore might still want to develop independently.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/24/2014 10:29 PM, Josh Berkus wrote:
 On 01/24/2014 12:47 PM, Heikki Linnakangas wrote:
 ISTM the consensus is that we need better monitoring/administration
 interfaces so that people can script the behavior they want in external
 tools. Also, a new synchronous apply replication mode would be handy,
 but that'd be a whole different patch. We don't have a patch on the
 table that we could consider committing any time soon, so I'm going to
 mark this as rejected in the commitfest app.
 I don't feel that we'll never do auto-degrade is determinative;
 several hackers were for auto-degrade, and they have a good use-case
 argument.  
Auto-degrade may make sense together with synchronous apply
mentioned by Heikki.

I do not see much use for synchronous-(noapply)-if-you-can mode,
though it may make some sense in some scenarios if sync failure
is accompanied by loud screaming (hey DBA, we are writing checks
with no money in the bank, do something fast!)

Perhaps some kind of sync-with-timeout mode, where timing out
results with a weak error (something between current
warning and error) returned to client and/or where it causes and
external command to be run which could then be used to flood
admins mailbox :)
 However, we do have consensus that we need more scaffolding
 than this patch supplies in order to make auto-degrade *safe*.

 I encourage the submitter to resumbit and improved version of this patch
 (one with more monitorability) for  9.5 CF1.  That'll give us a whole
 dev cycle to argue about it.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/17/2014 06:40 AM, Dave Chinner wrote:
 On Thu, Jan 16, 2014 at 08:48:24PM -0500, Robert Haas wrote:
 On Thu, Jan 16, 2014 at 7:31 PM, Dave Chinner da...@fromorbit.com wrote:
 But there's something here that I'm not getting - you're talking
 about a data set that you want ot keep cache resident that is at
 least an order of magnitude larger than the cyclic 5-15 minute WAL
 dataset that ongoing operations need to manage to avoid IO storms.
 Where do these temporary files fit into this picture, how fast do
 they grow and why are do they need to be so large in comparison to
 the ongoing modifications being made to the database?
 [ snip ]

 Temp files are something else again.  If PostgreSQL needs to sort a
 small amount of data, like a kilobyte, it'll use quicksort.  But if it
 needs to sort a large amount of data, like a terabyte, it'll use a
 merge sort.[1] 
 IOWs the temp files contain data that requires transformation as
 part of a query operation. So, temp file size is bound by the
 dataset, 
Basically yes, though the size of the dataset can be orders of
magnitude bigger than the database in case of some queries.
 growth determined by data retreival and transformation
 rate.

 IOWs, there are two very different IO and caching requirements in
 play here and tuning the kernel for one actively degrades the
 performance of the other. Right, got it now.
Yes. A step in right solutions would be some way to tune this
on per-device basis, but as large part of this in linux seems
to be driven from the keeping-vm-clean side it guess it will
be far from simple.

 Cheers,

 Dave.


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/14/2014 06:12 PM, Robert Haas wrote:
 This would be pretty similar to copy-on-write, except
 without the copying. It would just be
 forget-from-the-buffer-pool-on-write. 

+1

A version of this could probably already be implement using MADV_DONTNEED
and MADV_WILLNEED

Thet is, just after reading the page in, use MADV_DONTNEED on it. When
evicting
a clean page, check that it is still in cache and if it is, then
MADV_WILLNEED it.

Another nice thing to do would be dynamically adjusting kernel
dirty_background_ratio
and other related knobs in real time based on how many buffers are dirty
inside postgresql.
Maybe in background writer.

Question to LKM folks - will kernel react well to frequent changes to
/proc/sys/vm/dirty_*  ?
How frequent can they be (every few second? every second? 100Hz ?)

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/15/2014 12:16 PM, Hannu Krosing wrote:
 On 01/14/2014 06:12 PM, Robert Haas wrote:
 This would be pretty similar to copy-on-write, except
 without the copying. It would just be
 forget-from-the-buffer-pool-on-write. 
 +1

 A version of this could probably already be implement using MADV_DONTNEED
 and MADV_WILLNEED

 Thet is, just after reading the page in, use MADV_DONTNEED on it. When
 evicting
 a clean page, check that it is still in cache and if it is, then
 MADV_WILLNEED it.

 Another nice thing to do would be dynamically adjusting kernel
 dirty_background_ratio
 and other related knobs in real time based on how many buffers are dirty
 inside postgresql.
 Maybe in background writer.

 Question to LKM folks - will kernel react well to frequent changes to
 /proc/sys/vm/dirty_*  ?
 How frequent can they be (every few second? every second? 100Hz ?)
One obvious use case of this would be changing dirty_background_bytes
linearly to almost zero during a checkpoint to make final fsync fast.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/15/2014 02:01 PM, Jan Kara wrote:
 On Wed 15-01-14 12:16:50, Hannu Krosing wrote:
 On 01/14/2014 06:12 PM, Robert Haas wrote:
 This would be pretty similar to copy-on-write, except
 without the copying. It would just be
 forget-from-the-buffer-pool-on-write. 
 +1

 A version of this could probably already be implement using MADV_DONTNEED
 and MADV_WILLNEED

 Thet is, just after reading the page in, use MADV_DONTNEED on it. When
 evicting
 a clean page, check that it is still in cache and if it is, then
 MADV_WILLNEED it.

 Another nice thing to do would be dynamically adjusting kernel
 dirty_background_ratio
 and other related knobs in real time based on how many buffers are dirty
 inside postgresql.
 Maybe in background writer.

 Question to LKM folks - will kernel react well to frequent changes to
 /proc/sys/vm/dirty_*  ?
 How frequent can they be (every few second? every second? 100Hz ?)
   So the question is what do you mean by 'react'. We check whether we
 should start background writeback every dirty_writeback_centisecs (5s). We
 will also check whether we didn't exceed the background dirty limit (and
 wake writeback thread) when dirtying pages. However this check happens once
 per several dirtied MB (unless we are close to dirty_bytes).

 When writeback is running we check roughly once per second (the logic is
 more complex there but I don't think explaining details would be useful
 here) whether we are below dirty_background_bytes and stop writeback in
 that case.

 So changing dirty_background_bytes every few seconds should work
 reasonably, once a second is pushing it and 100 Hz - no way. But I'd also
 note that you have conflicting requirements on the kernel writeback. On one
 hand you want checkpoint data to steadily trickle to disk (well, trickle
 isn't exactly the proper word since if you need to checkpoing 16 GB every 5
 minutes than you need a steady throughput of ~50 MB/s just for
 checkpointing) so you want to set dirty_background_bytes low, on the other
 hand you don't want temporary files to get to disk so you want to set
 dirty_background_bytes high. 
Is it possible to have more fine-grained control over writeback, like
configuring dirty_background_bytes per file system / device (or even
a file or a group of files) ?

If not, then how hard would it be to provide this ?

This is a bit backwards from keeping-the-cache-clean perspective,
but would help a lot with hinting the writer that a big sync is coming.

 And also that changes of
 dirty_background_bytes probably will not take into account other events
 happening on the system (maybe a DB backup is running...). So I'm somewhat
 skeptical you will be able to tune dirty_background_bytes frequently in a
 useful way.



Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

 which were copied away for potential modifying but ended
up clean in the end.

And let the linux kernel decide if and how long to keep these pages
in its  cache using its superior knowledge of disk subsystem and
about what else is going on in the system in general.

Just food for thought

We want to have all the performance and complexity provided
by linux, and we would like it to work even better with postgresql by
having a bit more information for its decisions.

We just don't want to re-implement it ;)

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] [Lsf-pc] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/13/2014 11:22 PM, James Bottomley wrote:

 The less exciting, more conservative option would be to add kernel
 interfaces to teach Postgres about things like raid geometries. Then
 Postgres could use directio and decide to do prefetching based on the
 raid geometry, how much available i/o bandwidth and iops is available,
 etc.

 Reimplementing i/o schedulers and all the rest of the work that the
 kernel provides inside Postgres just seems like something outside our
 competency and that none of us is really excited about doing.
 This would also be a well trodden path ... I believe that some large
 database company introduced Direct IO for roughly this purpose.

The file system at that time were much worse than they are now,
so said large companies had no choice but to write their own.

As linux file handling has been much better for most of active
development of postgresql we have been able to avoid
it and still have reasonable performance.

What was been pointed out above are some (allegedly
desktop/mobile influenced) decisions which broke good
performance.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/14/2014 09:39 AM, Claudio Freire wrote:
 On Tue, Jan 14, 2014 at 5:08 AM, Hannu Krosing ha...@2ndquadrant.com wrote:
 Again, as said above the linux file system is doing fine. What we
 want is a few ways to interact with it to let it do even better when
 working with postgresql by telling it some stuff it otherwise would
 have to second guess and by sometimes giving it back some cache
 pages which were copied away for potential modifying but ended
 up clean in the end.
 You don't need new interfaces. Only a slight modification of what
 fadvise DONTNEED does.

 This insistence in injecting pages from postgres to kernel is just a
 bad idea. 
Do you think it would be possible to map copy-on-write pages
from linux cache to postgresql cache ?

this would be a step in direction of solving the double-ram-usage
of pages which have not been read from syscache to postgresql
cache without sacrificing linux read-ahead (which I assume does
not happen when reads bypass system cache).

and we can write back the copy at the point when it is safe (from
postgresql perspective)  to let the system write them back ?

Do you think it is possible to make it work with good performance
for a few million 8kb pages ?

 At the very least, it still needs postgres to know too much
 of the filesystem (block layout) to properly work. Ie: pg must be
 required to put entire filesystem-level blocks into the page cache,
 since that's how the page cache works. 
I was more thinking of an simple write() interface with extra
flags/sysctls to tell kernel that we already have this on disk
 At the very worst, it may
 introduce serious security and reliability implications, when
 applications can destroy the consistency of the page cache (even if
 full access rights are checked, there's still the possibility this
 inconsistency might be exploitable).
If you allow write() which just writes clean pages, I can not see
where the extra security concerns are beyond what normal
write can do.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/14/2014 05:44 PM, James Bottomley wrote:
 On Tue, 2014-01-14 at 10:39 -0500, Tom Lane wrote:
 James Bottomley james.bottom...@hansenpartnership.com writes:
 The current mechanism for coherency between a userspace cache and the
 in-kernel page cache is mmap ... that's the only way you get the same
 page in both currently.
 Right.

 glibc used to have an implementation of read/write in terms of mmap, so
 it should be possible to insert it into your current implementation
 without a major rewrite.  The problem I think this brings you is
 uncontrolled writeback: you don't want dirty pages to go to disk until
 you issue a write()
 Exactly.

 I think we could fix this with another madvise():
 something like MADV_WILLUPDATE telling the page cache we expect to alter
 the pages again, so don't be aggressive about cleaning them.
 Don't be aggressive isn't good enough.  The prohibition on early write
 has to be absolute, because writing a dirty page before we've done
 whatever else we need to do results in a corrupt database.  It has to
 be treated like a write barrier.

 The problem is we can't give you absolute control of when pages are
 written back because that interface can be used to DoS the system: once
 we get too many dirty uncleanable pages, we'll thrash looking for memory
 and the system will livelock.
 Understood, but that makes this direction a dead end.  We can't use
 it if the kernel might decide to write anyway.
 No, I'm sorry, that's never going to be possible.  No user space
 application has all the facts.  If we give you an interface to force
 unconditional holding of dirty pages in core you'll livelock the system
 eventually because you made a wrong decision to hold too many dirty
 pages.   I don't understand why this has to be absolute: if you advise
 us to hold the pages dirty and we do up until it becomes a choice to
 hold on to the pages or to thrash the system into a livelock, why would
 you ever choose the latter?  And if, as I'm assuming, you never would,
 why don't you want the kernel to make that choice for you?
The short answer is crash safety.

A database system worth its name must make sure that all data
reported as stored to clients is there even after crash.

Write ahead log is the means for that. And writing wal files and
data pages has to be in certain order to guarantee consistent
recovery after crash.

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/13/2014 04:12 PM, Florian Pflug wrote:
 On Jan12, 2014, at 04:18 , Josh Berkus j...@agliodbs.com wrote:
 Thing is, when we talk about auto-degrade, we need to determine things
 like Is the replica down or is this just a network blip? and take
 action according to the user's desired configuration.  This is not
 something, realistically, that we can do on a single request.  Whereas
 it would be fairly simple for an external monitoring utility to do:

 1. decide replica is offline for the duration (several poll attempts
 have failed)

 2. Send ALTER SYSTEM SET to the master and change/disable the
 synch_replicas.

 In other words, if we're going to have auto-degrade, the most
 intelligent place for it is in
 RepMgr/HandyRep/OmniPITR/pgPoolII/whatever.  It's also the *easiest*
 place.  Anything we do *inside* Postgres is going to have a really,
 really hard time determining when to degrade.
 +1

 This is also how 2PC works, btw - the database provides the building
 blocks, i.e. PREPARE and COMMIT, and leaves it to a transaction manager
 to deal with issues that require a whole-cluster perspective.


++1

I like Simons idea to have a pg_xxx function for switching between
replication modes, which should be enough to support a monitor
daemon doing the switching.

Maybe we could have an 'syncrep_taking_too_long_command' GUC
which could be used to alert such a monitoring daemon, so it can
immediately check weather to

a) switch master to async rep or standalone mode (in case of sync slave
becoming unavailable)

or

b) to failover to slave (in almost equally likely case that it was the
master
which became disconnected from the world and slave is available)

or

c) do something else depending on circumstances/policy :)


NB! Note that in case of b) 'syncrep_taking_too_long_command' will
very likely also not reach the monitor daemon, so it can not relay on
this as main trigger!

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [Lsf-pc] [HACKERS] Linux kernel impact on PostgreSQL performance

[Hannu Krosing]

On 01/13/2014 09:53 PM, Trond Myklebust wrote:
 On Jan 13, 2014, at 15:40, Andres Freund and...@2ndquadrant.com wrote:

 On 2014-01-13 15:15:16 -0500, Robert Haas wrote:
 On Mon, Jan 13, 2014 at 1:51 PM, Kevin Grittner kgri...@ymail.com wrote:
 I notice, Josh, that you didn't mention the problems many people
 have run into with Transparent Huge Page defrag and with NUMA
 access.
 Amen to that.  Actually, I think NUMA can be (mostly?) fixed by
 setting zone_reclaim_mode; is there some other problem besides that?
 I think that fixes some of the worst instances, but I've seen machines
 spending horrible amounts of CPU ( BUS) time in page reclaim
 nonetheless. If I analyzed it correctly it's in RAM  working set
 workloads where RAM is pretty large and most of it is used as page
 cache. The kernel ends up spending a huge percentage of time finding and
 potentially defragmenting pages when looking for victim buffers.

 On a related note, there's also the problem of double-buffering.  When
 we read a page into shared_buffers, we leave a copy behind in the OS
 buffers, and similarly on write-out.  It's very unclear what to do
 about this, since the kernel and PostgreSQL don't have intimate
 knowledge of what each other are doing, but it would be nice to solve
 somehow.
 I've wondered before if there wouldn't be a chance for postgres to say
 my dear OS, that the file range 0-8192 of file x contains y, no need to
 reread and do that when we evict a page from s_b but I never dared to
 actually propose that to kernel people...
 O_DIRECT was specifically designed to solve the problem of double buffering 
 between applications and the kernel. Why are you not able to use that in 
 these situations?
What is asked is the opposite of O_DIRECT - the write from a buffer inside
postgresql to linux *buffercache* and telling linux that it is the same
as what
is currently on disk, so don't bother to write it back ever.

This would avoid current double-buffering between postgresql and linux
buffer caches while still making use of linux cache when possible.

The use case is  pages that postgresql has moved into its buffer cache
but which it has not modified. They will at some point be evicted from the
postgresql cache, but it is likely that they will still be needed
sometime soon,
so what is required is writing them back to the original file, only
they should
not really be written - or marked dirty to be written later - more
levels than
just to the linux cache, as they *already* are on the disk.

It is probably ok to put them in the LRU position as they are written
out from postgresql, though it may be better if we get some more control
over
where in the LRU order they would be placed. It may make sense to put them
there based on when they were last read while residing inside postgresql
cache

Cheers


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Disallow arrays with non-standard lower bounds

[Hannu Krosing]

On 01/14/2014 12:33 AM, Craig Ringer wrote:
 On 01/14/2014 12:40 AM, Merlin Moncure wrote:
 On Sun, Jan 12, 2014 at 4:38 AM, Craig Ringer cr...@2ndquadrant.com wrote:
 Implicit casts to text, anybody?
 This backward compatibility break orphaned the company I work for on
 8.1 until last year and very nearly caused postgres to be summarily
 extirpated (only rescued at the last minute by my arrival).
 You're far from the only one, too. Until last year I was still seeing
 people saying they can't upgrade because of this. OTOH, that was a
 sudden and drastic change, with no BC switch like the removal of
 implicit joins had, that affected wide swaths of code. Lets not do that
 again.

 Removal of lower bounds for arrays is unlikely to even get noticed by
 the vast majority of users, and can be done progressively with BC features.

 The real issue IMO is how to get those few to stop using it so it can be
 truly removed. Past experience has shown that people just turn the
 compatibility flag on and forget they're using the deprecated feature.
 If there are warnings they'll silence them in their application and
 still forget they're using the deprecated feature. If there are log
 messages, they'll probably turn logging detail down to hide them and
 STILL forget they're using the deprecated feature.

 Then whine about it three years later when it gets removed.

 So I guess the question is: Is it worth all that hassle to remove a
 misfeature you have to go out of your way to use? Is support for non-1
 lower bounds stopping us from doing something useful and important? Or
 is it just an irritation that it exists?

Let's just add user defined operator for '[]' (weirdly-positioned but
2 argument, almost infix :) ) and add that to JSON arrays to get
0-based ones into poastgresq  ;)

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/10/2014 05:09 PM, Simon Riggs wrote:
 On 10 January 2014 15:47, Bruce Momjian br...@momjian.us wrote:

 I know there was a desire to remove this TODO item, but I think we have
 brought up enough new issues that we can keep it to see if we can come
 up with a solution.
 Can you summarise what you think the new issues are? All I see is some
 further rehashing of old discussions.

 There is already a solution to the problem because the docs are
 already very clear that you need multiple standbys to achieve commit
 guarantees AND high availability. RTFM is usually used as some form of
 put down, but that is what needs to happen here.

If we want to get the guarantees that often come up in sync rep
discussions - namely that you can assume that your change is applied
on standby when commit returns - then we could implement this by
returning LSN from commit at protocol level and having an option in
queries on standby to wait for this LSN (again passed on wire below
the level of query)  to be applied.

This can be mostly hidden in drivers and would need very little effort
from end user to use. basically you tell the driver that one connection
is bound as the slave of another and driver can manage using the
right LSNs. That is the last LSN received from master is always
attached to queries on slaves.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/10/2014 11:59 PM, Joshua D. Drake wrote:

 On 01/10/2014 02:57 PM, Stephen Frost wrote:

 Yes, if you have a BBU that memory is authoritative in most
 cases. But
 in that case the argument of having two disks is pretty much
 pointless,
 the SPOF suddenly became the battery + ram.


 If that is a concern then use multiple controllers. Certainly not
 unheard of- look at SANs...


 And in PostgreSQL we obviously have the option of having a third or
 fourth standby but that isn't the problem we are trying to solve.
The problem you are trying to solve is a controller with enough
Battery Backed Cache RAM to cache the entire database but with
write-though mode.

And you want it to degrade to write-back in case of disk failure so that
you can continue while the disk is broken.

People here are telling you that it would not be safe, use at least RAID-1
if you want availability

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/09/2014 05:09 AM, Robert Treat wrote:
 On Wed, Jan 8, 2014 at 6:15 PM, Josh Berkus j...@agliodbs.com wrote:
 Stephen,


 I'm aware, my point was simply that we should state, up-front in
 25.2.7.3 *and* where we document synchronous_standby_names, that it
 requires at least three servers to be involved to be a workable
 solution.
 It's a workable solution with 2 servers.  That's a low-availability,
 high-integrity solution; the user has chosen to double their risk of
 not accepting writes against never losing a write.  That's a perfectly
 valid configuration, and I believe that NTT runs several applications
 this way.

 In fact, that can already be looked at as a kind of auto-degrade mode:
 if there aren't two nodes, then the database goes read-only.

 Might I also point out that transactions are synchronous or not
 individually?  The sensible configuration is for only the important
 writes being synchronous -- in which case auto-degrade makes even less
 sense.

 I really think that demand for auto-degrade is coming from users who
 don't know what sync rep is for in the first place.  The fact that other
 vendors are offering auto-degrade as a feature instead of the ginormous
 foot-gun it is adds to the confusion, but we can't help that.

 I think the problem here is that we tend to have a limited view of
 the right way to use synch rep. If I have 5 nodes, and I set 1
 synchronous and the other 3 asynchronous, I've set up a known
 successor in the event that the leader fails. 
But there is no guarantee that the synchronous replica actually
is ahead of async ones.

 In this scenario
 though, if the successor fails, you actually probably want to keep
 accepting writes; since you weren't using synchronous for durability
 but for operational simplicity. I suspect there are probably other
 scenarios where users are willing to trade latency for improved and/or
 directed durability but not at the extent of availability, don't you?

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/09/2014 12:05 AM, Stephen Frost wrote:
 * Andres Freund (and...@2ndquadrant.com) wrote:
 On 2014-01-08 17:56:37 -0500, Stephen Frost wrote:
 * Andres Freund (and...@2ndquadrant.com) wrote:
 That's why you should configure a second standby as another (candidate)
 synchronous replica, also listed in synchronous_standby_names.
 Perhaps we should stress in the docs that this is, in fact, the *only*
 reasonable mode in which to run with sync rep on?  Where there are
 multiple replicas, because otherwise Drake is correct that you'll just
 end up having both nodes go offline if the slave fails.
 Which, as it happens, is actually documented.
 I'm aware, my point was simply that we should state, up-front in
 25.2.7.3 *and* where we document synchronous_standby_names, that it
 requires at least three servers to be involved to be a workable
 solution.

 Perhaps we should even log a warning if only one value is found in
 synchronous_standby_names...
You can have only one name in synchronous_standby_names and
have multiple slaves connecting with that name

Also, I can attest that I have had clients who want exactly that - a system
stop until admin intervention in case of a designated sync standby failing.

And they actually run more than one standby, they just want to make
sure that sync rep to 2nd data center always happens.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/08/2014 11:49 PM, Tom Lane wrote:
 Joshua D. Drake j...@commandprompt.com writes:
 On 01/08/2014 01:55 PM, Tom Lane wrote:
 Sync mode is about providing a guarantee that the data exists on more than
 one server *before* we tell the client it's committed.  If you don't need
 that guarantee, you shouldn't be using sync mode.  If you do need it,
 it's not clear to me why you'd suddenly not need it the moment the going
 actually gets tough.
 As I understand it what is being suggested is that if a subscriber or 
 target goes down, then the master will just sit there and wait. When I 
 read that, I read that the master will no longer process write 
 transactions. If I am wrong in that understanding then cool. If I am not 
 then that is a serious problem with a production scenario. There is an 
 expectation that a master will continue to function if the target is 
 down, synchronous or not.
 Then you don't understand the point of sync mode, and you shouldn't be
 using it.  The point is *exactly* to refuse to commit transactions unless
 we can guarantee the data's been replicated.
For single host scenario this would be similar to asking for
a mode which turns fsync=off in case of disk failure :)


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/09/2014 01:57 PM, MauMau wrote:
 From: Andres Freund and...@2ndquadrant.com
 On 2014-01-08 14:42:37 -0800, Joshua D. Drake wrote:
 If we have the following:

 db0-db1:down

 Using the model (as I understand it) that is being discussed we have
 increased our failure rate because the moment db1:down we also lose
 db0. The
 node db0 may be up but if it isn't going to process transactions it is
 useless. I can tell you that I have exactly 0 customers that would
 want that
 model because a single node failure would cause a double node failure.

 That's why you should configure a second standby as another (candidate)
 synchronous replica, also listed in synchronous_standby_names.

 Let me ask a (probably) stupid question.  How is the sync rep
 different from RAID-1?

 When I first saw sync rep, I expected that it would provide the same
 guarantees as RAID-1 in terms of durability (data is always mirrored
 on two servers) and availability (if one server goes down, another
 server continues full service).
What you describe is most like A-sync rep.

Sync rep makes sure that data is always replicated before confirming to
writer.


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/09/2014 02:01 AM, Jim Nasby wrote:
 On 1/8/14, 6:05 PM, Tom Lane wrote:
 Josh Berkusj...@agliodbs.com  writes:
 On 01/08/2014 03:27 PM, Tom Lane wrote:
 What we lack, and should work on, is a way for sync mode to have
 M larger
 than one.  AFAICS, right now we'll report commit as soon as
 there's one
 up-to-date replica, and some high-reliability cases are going to
 want
 more.
 Sync N times is really just a guarantee against data loss as long as
 you lose N-1 servers or fewer.  And it becomes an even
 lower-availability solution if you don't have at least N+1 replicas.
 For that reason, I'd like to see some realistic actual user demand
 before we take the idea seriously.
 Sure.  I wasn't volunteering to implement it, just saying that what
 we've got now is not designed to guarantee data survival across failure
 of more than one server.  Changing things around the margins isn't
 going to improve such scenarios very much.

 It struck me after re-reading your example scenario that the most
 likely way to figure out what you had left would be to see if some
 additional system (think Nagios monitor, or monitors) had records
 of when the various database servers went down.  This might be
 what you were getting at when you said logging, but the key point
 is it has to be logging done on an external server that could survive
 failure of the database server.  postmaster.log ain't gonna do it.

 Yeah, and I think that the logging command that was suggested allows
 for that *if configured correctly*.
*But* for relying on this, we would also need to make logging
*synchronous*,
which would probably not go down well with many people, as it makes things
even more fragile from availability viewpoint (and slower as well).

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] Standalone synchronous master

[Hannu Krosing]

On 01/09/2014 04:15 PM, MauMau wrote:
 From: Hannu Krosing ha...@2ndquadrant.com
 On 01/09/2014 01:57 PM, MauMau wrote:
 Let me ask a (probably) stupid question.  How is the sync rep
 different from RAID-1?

 When I first saw sync rep, I expected that it would provide the same
 guarantees as RAID-1 in terms of durability (data is always mirrored
 on two servers) and availability (if one server goes down, another
 server continues full service).
 What you describe is most like A-sync rep.

 Sync rep makes sure that data is always replicated before confirming to
 writer.

 Really?  RAID-1 is a-sync?
Not exactly, as there is no master just controller writing to two
equal disks.

But having a degraded mode makes it
more like async - it continues even with single disk and syncs later if
and when the 2nd disk comes back.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] nested hstore patch

[Hannu Krosing]

On 12/23/2013 12:28 PM, Robert Haas wrote:
 On Fri, Dec 20, 2013 at 6:16 PM, David E. Wheeler da...@justatheory.com 
 wrote:
 * New operators:
   + `hstore - int`: Get string value at array index (starting at 0)
   + `hstore ^ text`:Get numeric value for key
   + `hstore ^ int`: Get numeric value at array index
   + `hstore ? text`:Get boolean value for key
   + `hstore ? int`: Get boolean value at array index
   + `hstore # text[]`:  Get string value for key path
   + `hstore #^ text[]`: Get numeric value for key path
   + `hstore #? text[]`: Get boolean value for key path
   + `hstore % text`:Get hstore value for key
   + `hstore % int`: Get hstore value at array index
   + `hstore #% text[]`: Get hstore value for key path
   + `hstore ? int`:  Does hstore contain array index
   + `hstore #? text[]`:  Does hstore contain key path
   + `hstore - int`:  Delete index from left operand
   + `hstore #- text[]`:  Delete key path from left operand
 Although in some ways there's a certain elegance to this, it also
 sorta looks like punctuation soup.  I can't help wondering whether
 we'd be better off sticking to function names.

Has anybody looked into how hard it would be to add method notation
to postgreSQL, so that instead of calling

getString(hstorevalue, n)

we could use

hstorevalue.getString(n)

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] [PATCH] Negative Transition Aggregate Functions (WIP)

[Hannu Krosing]

On 12/16/2013 08:39 AM, David Rowley wrote:
 On Mon, Dec 16, 2013 at 6:00 AM, Ants Aasma ants.aa...@eesti.ee
 mailto:ants.aa...@eesti.ee wrote:

 On Dec 15, 2013 6:44 PM, Tom Lane t...@sss.pgh.pa.us
 mailto:t...@sss.pgh.pa.us wrote:
  David Rowley dgrowle...@gmail.com
 mailto:dgrowle...@gmail.com writes:
   I've attached an updated patch which includes some documentation.
   I've also added support for negfunc in CREATE AGGREGATE.
 Hopefully that's
   an ok name for the option, but if anyone has any better ideas
 please let
   them be known.
 
  I'd be a bit inclined to build the terminology around reverse
 instead of
  negative --- the latter seems a bit too arithmetic-centric.
  But that's
  just MHO.

 To contribute to the bike shedding, inverse is often used in
 similar contexts.

 I guess it's not really bike shedding, most of the work I hope is
 done, so I might as well try to get the docs polished up and we'd need
 a consensus on what we're going to call them before I can get that done.
  
 I like both of these better than negative transition function and I
 agree negative implies arithmetic rather than opposite.
 Out of these 2 I do think inverse fits better than reverse, so I guess
 that would make it inverse aggregate transition function. 
 Would that make the CREATE AGGREGATE option be INVFUNC ?
  
 Any other ideas or +1's for any of the existing ones?
+1, inverse good :)

 Regards

 David Rowley

 --
 Ants Aasma




-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] [PATCH] Add transforms feature

[Hannu Krosing]

On 12/11/2013 01:40 PM, Robert Haas wrote:
 On Tue, Dec 10, 2013 at 10:35 PM, Peter Eisentraut pete...@gmx.net wrote:
 On Fri, 2013-12-06 at 11:28 +0100, Dimitri Fontaine wrote:
 Here is an idea.  Add a GUC that basically says something like
 use_transforms = on|off.  You can then attach that to individual
 functions, which is the right granularity, because only the function
 knows whether its code expects transforms or not.  But you can use
 the
 full power of GUC to configure it any way you want.
 Here is an updated patch that implements this, makes some of the
 documentation improvements that you suggested, and rebases everything.
 I'm still kinda unimpressed by this.  Behavior-changing GUC, uggh.

It should work ok if we could somehow check that the GUC is set
on the function and fall back to session GUC in case it is not.

Not sure if this is possible though.

The need from this arises from calling other functions from a new func.
At the moment if there is a new function defined as

CREATE FUNCTION f_uses_xforms() AS $$ ... $$ SET use_transforms=on;

calls a legacy function which will break if transforms are used then the
_old_ function declaration needs to be modified to add (use_transforms=off)

It is much easier than debugging/rewriting the function, but this is
something I'd like us to be able to avoid.

PS. maybe we could resurrect the  WITH (attribute, ...) available in
CREATE FUNCTION syntax for passing function-specific flags ?


Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] ANALYZE sampling is too good

[Hannu Krosing]

On 12/11/2013 01:44 AM, Greg Stark wrote:
 On Wed, Dec 11, 2013 at 12:40 AM, Simon Riggs si...@2ndquadrant.com wrote:
 When we select a block we should read all rows on that block, to help
 identify the extent of clustering within the data.
 So how do you interpret the results of the sample read that way that
 doesn't introduce bias?

Initially/experimentally we could just compare it to our current approach :)

That is, implement *some* block sampling and then check it against what
we currently have. Then figure out the bad differences. Rinse. Repeat.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] [PATCH] Add transforms feature

[Hannu Krosing]

On 12/06/2013 07:25 AM, Peter Eisentraut wrote:
 On Tue, 2013-11-26 at 18:10 +0100, Dimitri Fontaine wrote:
 The problem is installing a set of extensions where some of them are
 already using the new transform feature and some of them are not. We
 need a way to cater with that, I think.
 Here is an idea.  Add a GUC that basically says something like
 use_transforms = on|off.  You can then attach that to individual
 functions, which is the right granularity, because only the function
 knows whether its code expects transforms or not.  But you can use the
 full power of GUC to configure it any way you want.
It would requite the old extensions to be modified to have
(SET use_transforms = off) in all their definitions so that they
would not accidentally be called with  use_transforms = on
from new functions, but else it seems like a good way to get
it done without too much effort.

 The only thing this doesn't give you is per-argument granularity, but I
 think the use cases for that are slim, and we don't have a good existing
 mechanism to attach arbitrary attributes to function arguments.
Agreed. And we are quite unlikely to need multiple transforms for
the same type in the same language.
 Actually, I'd take this two steps further.

 First, make this parameter per-language, so something like
 plpython.use_transforms.  Then it's up to the language implementation
 how they want to deal with this.  A future new language could just
 ignore the whole issue and require transforms from the start.
I do not really see much need for this, as it will need to be set for
each individual function anyway.

Actually what we could do is just declare a new language for this
so functions declared with LANGUAGE plpythonu will not be using
transforms and those with LANGUAGE plpythonuxf will use it.

This would only need one extra function to be defined in source
code, namely the compile function for the new language.


Some not-transforms-related wild ideas follow :)

Adding a new language would also be a good way to fix the bad syntax
choices in pl/python which require code manipulation before compiling .

I came up with this idea after seeing how pl/jsv8 supports multiple
JavaScript-based languages (standard JavaScript, CoffeeScript, LiveScript)
from the same codebase.

Taking the plv8 ideas further we could also create a JavaScript-based
sandboxed python using thins like skulpt and pyjamas which compile
python source code to JavaScript VM and inherit all the sandboxing of
v8.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] [PATCH] Add transforms feature

[Hannu Krosing]

On 11/15/2013 05:04 PM, Dimitri Fontaine wrote:
 Hi,

 Peter Eisentraut pete...@gmx.net writes:
 Rebased patch.  No changes except that merge conflicts were resolved,
 and I had to add some Data::Dumper tweaks to the regression tests so
 that the results came out in  consistent order on different versions of
 Perl.

 On the higher level design, the big question here is about selective
 behavior. As soon as you CREATE TRANSFORM FOR hstore LANGUAGE plperl
 then any plperl function will now receive its hstore arguments as a
 proper perl hash rather than a string.

 Any pre-existing plperl function with hstore arguments or return type
 then needs to be upgraded to handle the new types nicely, and some of
 those might not be under the direct control of the DBA running the
 CREATE TRANSFORM command, when using some plperl extensions for example.

 A mechanism allowing for the transform to only be used in some functions
 but not others might be useful. The simplest such mechanism I can think
 of is modeled against the PL/Java classpath facility as specified in the
 SQL standard: you attach a classpath per schema.
If we start adding granularity, then why not go all the way?

I mean, we could do it in the following way

1) create named transforms

CREATE [DEFAULT] TRANSFORM xformname FOR type LANGUAGE lang 
(...details...);

2) use it when declaring a function

CREATE function funcname(
IN argname type [[USING] [TRANSFORM] xformname],
INOUT argname type [[USING] [IN] [TRANSFORM] xformname] [[USING] 
[OUT] [TRANSFORM] xformname],
OUT argname type [[USING] [TRANSFORM] xformname],

 ... 
) LANGUAGE lang $$
funcdef
$$;

This approach allows full flexibility in using old packages, especially
if we define old transform behaviour as DEFAULT TRANSFORM

Default transforms also allow easy way for rewriting current type i/o
conversions between languages into transforms.

There are immediately a few transforms that I would find useful

A) pass field data to language as pairs of (typeoid, typebin)

this is useful for speed, especially if you do not want to use many
of the passed arguments on most invocations

B) pass field data in as (typeoid, typebin), except do not de-toast
values but
pass in the toast ids, so the function is free to use only parts of
toasted values as it needs

C) pass field data in as string, probably the default behaviour for
languages like pl/tcl and pl/sh

D) and then of course just having a sensible transforms for extension
types like the current patch provides.

 Worst case, that I really don't think we need, would be addressing that
 per-argument:

   CREATE FUNCTION foo (hash hstore WITH TRANSFORM, kv hstore) …

 I certainly hope we don't need that, and sure can't imagine use cases
 for that level of complexity at the time of writing this review.

A typical use case would be to have a short hstore always passed in as
dictionary
and have another possibly large hstore passed in as toast pointer.

And if we want to have all type conversions between postgres and pls
re-written
as transforms, then we do need named transforms, not just one per (pl,
type) pair.

Also, if we allow flexibility, the it is probably a good idea to
implement full flexibility
first and then look at making usage easy after that, instead of adding
flexibility in
small steps.

Once we have per-argument transforms in place, we can look at setting
per-schema
defaults for ease of use.

As large part of this is actually abstracting i/o conversions out of pl
function code,
I think we should look at allowing the conversion functions to be
written in the
target pl language in addition to C.

I'll see if I can resurrect my patch for support of cstring and
internal types in pl/python
function defs for this.

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] [PATCH] Add transforms feature

[Hannu Krosing]

On 11/12/2013 12:21 PM, Peter Eisentraut wrote:
 A transform is an SQL object that supplies to functions for converting
 between data types and procedural languages. 
How hard would it be to extend this to add transforms directly
between pairs of procedural languages ?

One example would be calling a pl/v8 function from pl/python
and converting directly between integers in both, without going
through PostgreSQL type.

Another and maybe even more interesting would be automatic
null-transforms between two pl/python functions.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] [PATCH] Add transforms feature

[Hannu Krosing]

On 11/20/2013 10:58 PM, Robert Haas wrote:
 On Wed, Nov 20, 2013 at 11:51 AM, Peter Eisentraut pete...@gmx.net wrote:
 This is a transition problem.  Nobody is required to install the
 transforms into their existing databases.  They probably shouldn't.
 Sure, but that's like saying nobody's required to use this
 behavior-changing GUC, so it's OK to have a behavior-changing GUC.

 The point I think Dimitri is making, which IMHO is entirely valid, is
 that the feature as currently designed is database-wide.  You either
 get this behavior for all of your functions, or you get it for none of
 them, and that might well not be what you want.  For example, it's
 easy to imagine that you might want to install extensions A and B.  A
 expects that a certain transform is loaded into the database, and B
 expects that it isn't.  You now have created a situation where
 extensions A and B can't be used together.  That sucks.

 If the transform were a property of particular function argument
 positions, this wouldn't be a problem.  You could declare, in effect,
 that a certain function takes a transformed hstore, and this other one
 takes a non-transformed hstore.  Now life is good.  But that's not
 what is being proposed.
You mean something like

CREATE FUNCTION f(i int, h1 hstore USING TRANSFORM x, h2 hstore) ...

where h1 would go through transform x and 1 and h2
would use default transform ?

Cheers


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/18/2013 06:49 PM, Josh Berkus wrote:
 On 11/18/2013 06:13 AM, Peter Eisentraut wrote:
 On 11/15/13, 6:15 PM, Josh Berkus wrote:
 Thing is, I'm not particularly concerned about *Merlin's* specific use
 case, which there are ways around. What I am concerned about is that we
 may have users who have years of data stored in JSON text fields which
 won't survive an upgrade to binary JSON, because we will stop allowing
 certain things (ordering, duplicate keys) which are currently allowed in
 those columns.  At the very least, if we're going to have that kind of
 backwards compatibilty break we'll want to call the new version 10.0.
 We could do something like SQL/XML and specify the level of validity
 in a typmod, e.g., json(loose), json(strict), etc.
 Doesn't work; with XML, the underlying storage format didn't change.
 With JSONB, it will ... so changing the typemod would require a total
 rewrite of the table.  That's a POLS violation if I ever saw one
We do rewrites on typmod changes already.

To me having json(string) and json(hstore) does not seem too bad.

Cheers
Hannu


-- 
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] additional json functionality

[Hannu Krosing]

On 11/18/2013 05:19 AM, Andrew Dunstan wrote:

 On 11/17/2013 08:49 PM, Josh Berkus wrote:
 Now, if it turns out that the new hstore is not dealing with json input
 and output, we could have json, jstore and hstore.
 Jstore isn't the worst name suggestion I've heard on this thread.  The
 reason I prefer JSONB though, is that a new user looking for a place to
 put JSON data will clearly realize that JSON and JSONB are alternatives
 and related in some way.  They won't necessarily expect that jstore
 has anything to do with JSON, especially when there is another type
 called JSON.  Quite a few people are liable to think it's something to
 do with Java.

 Besides, we might get sued by these people: http://www.jstor.org/  ;-)


 I don't think any name that doesn't begin with json is acceptable.
 I could live with jsonb. It has the merit of brevity, but maybe it's
 a tad
 too close to json to be the right answer.
How about jsondoc, or jsonobj ?

It is still reasonably 'json' but not too easy to confuse with existing
json
when typing

And it perhaps hints better at the main difference from string-json, namely
that it is an object and not textual source code / notation / processing
info .

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] pre-commit triggers

[Hannu Krosing]

On 11/17/2013 01:42 AM, Tom Lane wrote:
 Hannu Krosing ha...@2ndquadrant.com writes:
 I have not looked at the patch, but does it also run pre-rollback ?
 error in trigger - instant infinite loop.
Means this needs to have some kind of recursion depth limit, like python

 def x():
... return x()
...
 x()
... (a few thousand messages like the following) ...
  File stdin, line 2, in x
RuntimeError: maximum recursion depth exceeded


 Besides, exactly what would you do in such a trigger?  
The use case would be telling another system about the rollback.

Basically sending a ignore what I told you to do message

So it would send a network message, a signal or writing something to
external file.

 Not modify
 the database, for certain, because we're about to roll back.

   regards, tom lane

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] pre-commit triggers

[Hannu Krosing]

On 11/17/2013 04:20 PM, Alvaro Herrera wrote:
 Hannu Krosing wrote:

 So it would send a network message, a signal or writing something to
 external file.
 If you're OK with a C function, you could try registering a callback,
 see RegisterXactCallback().

I already have an implementation doing just that, thoughg having a
trigger would be perhaps clearer :)

And I suspect that calling a pl/* function after the ROLLBACK has
actually happened due to
error is a no-go anyway, so it has to be C.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] pre-commit triggers

[Hannu Krosing]

On 11/17/2013 07:31 PM, Andres Freund wrote:
 On 2013-11-17 09:39:26 +0100, Hannu Krosing wrote:
 Besides, exactly what would you do in such a trigger?  
 The use case would be telling another system about the rollback.

 Basically sending a ignore what I told you to do message
 But you can't rely on it - if e.g. the server restarted/crashed, there
 won't be any messages about it. In that light, I really don't see what
 you could do with it.
I can get the info about non-commit earlier :)

At some point I can call back into the database and see if the
transaction is still running.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/17/2013 09:02 PM, David E. Wheeler wrote:
 On Nov 16, 2013, at 2:04 PM, Hannu Krosing ha...@2ndquadrant.com wrote:

 It’s still input and output as JSON, though.
 Yes, because JavaScript Object Notation *is* a serialization format
 (aka Notation) for converting JavaScript Objects to text format
 and back :)
 I still like JSONB best.
 To me it feels redundant, like binarytextbinary

 the binary representation of JSON is JavaScript(-like) Object, not
 binary json

 So my vote would be either jsobj or jsdoc (as document databases) tend
 to call the structured types documents
 You know that both types support scalar values right? 
 'a'::JSON works now, 
Yeah, and I remember all the bikeshedding about how
scalars should not be supported as they are
not really JSON by standard ...

At that time I was also quite vocal about not painting
ourselves in corner by not normalising json on input and
thus generating a backwards compatibility problem in
case we would ever get proper json support.
 and 'a'::hstore works with the WIP patch. For that reason I would not think 
 that doc or obj would be good choices.
this is like claiming that text should not be text because you
can store a single character there as well.

I feel that both doc and obj convey the meaning that it is a
structured type meant for fast component lookup as opposed to
jsoN(otation) type which is text.

Also jsdoc/jsobj would be a natural bridge to pgdoc/pgobj which would be
similar to new json but allow any type supported by postgresql as a value.

(... and in several languages even scalars really are objects)

 I like JSONB because:

 1. The B means binary
Binary has really little to do with the fact that we
normalise on input, which is the real significant feature
of the new json type.
 2. The B means second
Why not just json2 , (you know, like varchar2 in a certain other database ;)
 3. It's short
jsobj and jsdoc are exactly as short as jsonb
 4. See also BYTEA.
BYTEA is byte array, so not really relevant.

(unless you try to rhyme a byte-a, json-b sequence ;) )

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/17/2013 10:51 PM, Dimitri Fontaine wrote:
 David E. Wheeler da...@justatheory.com writes:
 You know that both types support scalar values right? 'a'::JSON works now,
 and 'a'::hstore works with the WIP patch. For that reason I would not think
 that doc or obj would be good choices.
 I'm wondering about just pushing hstore in core (even if technically
 still an extension, install it by default, like we do for PLpgSQL), and
 calling it a day.

 If you need pre-9.4 JSON-is-text compatibility, use the json datatype,
 if you want something with general index support, use hstore.
+1 for getting also hstore in

I think hstore needs to keep its text format compatible with older hstore
(in this discussion lets call this text format hson, short for
HStore Object Notation for added confusion :)

 For bikeshedding purposes, what about calling it jstore, 
+1 for jstore as well. I am happy with jstore, jsdoc, jsobj

jstore/jsobj/jsdoc really is *not* JSON, but a bona-fide freeform
structured datatype that happens to have JSON as convenient
I/O format.

You may want to use jstore even if you have never needed
JSON as serialisation/transport format before.

I do not like jsonB (sounds too much like json2, i.e. like we were
trying to cover up a design accident) nor json_strict (as this is not
really strict as it indeed does accept scalars, not just Arrays/Lists
and Objects/Dictionaries as per JSON standard)
 as in “we actually know how to store your json documents”?

 Regards,

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] pre-commit triggers

[Hannu Krosing]

On 11/15/2013 07:01 PM, Andrew Dunstan wrote:

 Attached is a patch to provide a new event trigger that will fire on
 transaction commit. I have tried to make certain that it fires at a
 sufficiently early stage in the commit process that some of the evils
 mentioned in previous discussions on this topic aren't relevant.

 The triggers don't fire if there is no real XID, so only actual data
 changes should cause the trigger to fire.
I have not looked at the patch, but does it also run pre-rollback ?

If not, how hard would it be to make it so ?

 They also don't fire in single user mode, so that if you do something
 stupid like create a trigger that unconditionally raises an error you
 have a way to recover.

 This is intended to be somewhat similar to the same feature in the
 Firebird database, and the initial demand came from a client migrating
 from that system to Postgres.

 cheers

 andrew




-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ

Re: [HACKERS] additional json functionality

[Hannu Krosing]

On 11/16/2013 12:15 AM, Josh Berkus wrote:
 On 11/15/2013 02:59 PM, Merlin Moncure wrote:
  On Fri, Nov 15, 2013 at 4:31 PM, Hannu Krosing ha...@2ndquadrant.com 
 wrote:
 I think you may be on to something here.  This might also be a way
 opt-in to fast(er) serialization (upthread it was noted this is
 unimportant; I'm skeptical).  I deeply feel that two types is not the
 right path but I'm pretty sure that this can be finessed.

 As far as I understand merlin is mostly ok with stored json being
 normalised and the problem is just with constructing extended
 json (a.k.a. processing instructions) to be used as source for
 specialised parsers and renderers.
 Thing is, I'm not particularly concerned about *Merlin's* specific use
 case, which there are ways around. What I am concerned about is that we
 may have users who have years of data stored in JSON text fields which
 won't survive an upgrade to binary JSON, because we will stop allowing
 certain things (ordering, duplicate keys) which are currently allowed in
 those columns.  At the very least, if we're going to have that kind of
 backwards compatibilty break we'll want to call the new version 10.0.

 That's why naming old JSON as json_text won't work; it'll be a
 hardened roadblock to upgrading.
Then perhaps name the new binary json as jsob (JavaScript Object Binary)
or just jsobj (JavaScript Object) and keep current json for what it is,
namely
JavaScript Object Notation.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/16/2013 10:30 PM, David E. Wheeler wrote:
 On Nov 16, 2013, at 12:04 PM, Hannu Krosing ha...@2ndquadrant.com wrote:

 Then perhaps name the new binary json as jsob (JavaScript Object Binary)
 or just jsobj (JavaScript Object) and keep current json for what it is,
 namely
 JavaScript Object Notation.
 It’s still input and output as JSON, though.
Yes, because JavaScript Object Notation *is* a serialization format
(aka Notation) for converting JavaScript Objects to text format
and back :)
  I still like JSONB best.
To me it feels redundant, like binarytextbinary

the binary representation of JSON is JavaScript(-like) Object, not
binary json

So my vote would be either jsobj or jsdoc (as document databases) tend
to call the structured types documents

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/15/2013 09:25 PM, Merlin Moncure wrote:
 On Fri, Nov 15, 2013 at 1:51 PM, David E. Wheeler da...@justatheory.com 
 wrote:
 On Nov 15, 2013, at 6:35 AM, Merlin Moncure mmonc...@gmail.com wrote:

 Here are the options on the table:
 1) convert existing json type to binary flavor (notwithstanding objections)
 2) maintain side by side types, one representing binary, one text.
 unfortunately, i think the text one must get the name 'json' due to
 unfortunate previous decision.
 3) merge the behaviors into a single type and get the best of both
 worlds (as suggested upthread).

 I think we need to take a *very* hard look at #3 before exploring #1
 or #2: Haven't through it through yet but it may be possible to handle
 this in such a way that will be mostly transparent to the end user and
 may have other benefits such as a faster path for serialization.
 If it’s possible to preserve order and still get the advantages of binary 
 representation --- which are substantial (see 
 http://theory.so/pg/2013/10/23/testing-nested-hstore/ and 
 http://theory.so/pg/2013/10/25/indexing-nested-hstore/ for a couple of 
 examples) --- without undue maintenance overhead, then great.

 I am completely opposed to duplicate key preservation in JSON, though. It 
 has caused us a fair number of headaches at $work.
Let's just  change the current json-constructing functions return type to
json_text which is exactly like text with 2 extra properties:

1) it is syntax-checked for valid json (that is it can be cast to json)

and

2) if included in outer json as data, it is included directly and is not
quoted like text


With just these two it should possible to have the following

a) Merlin and others can keep (ab)using json_text as this
wonderfully versatile format for feeding json parsers and
visualisers which accept duplicates and consider order significant

b) cast this to binary json object if de-duplication and fast access to
internals is needed

I do not think we need anything else for this

As far as I understand merlin is mostly ok with stored json being
normalised and the problem is just with constructing extended
json (a.k.a. processing instructions) to be used as source for
specialised parsers and renderers.

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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

Re: [HACKERS] nested hstore patch

[Hannu Krosing]

On 11/14/2013 01:32 AM, David E. Wheeler wrote:
 On Nov 13, 2013, at 3:59 PM, Hannu Krosing ha...@2ndquadrant.com wrote:

 I remember strong voices in support of *not* normalising json, so that
 things like

 {a:1,a:true, a:b, a:none}

 would go through the system unaltered, for claimed standard usage of
 json as
 processing instructions. That is as source code which can possibly
 converted
 to JavaScript Object and not something that would come out of
 serialising of
 any existing JavaScript Object.
 My recollection from PGCon was that there was consensus to normalize on 
 the way in --
Great news! I remember advocating this approach in the mailing lists
but having been out-voted based on current real-world usage out there :)
  or at least, if we switched to a binary representation as proposed by 
 Oleg  Teodor, it was not worth the hassle to try to keep it.
Very much agree. For the source code approach I'd recommend
text type with maybe a check that it is possible to convert it to json.



-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/14/2013 12:20 AM, Josh Berkus wrote:
 Merlin,


 I use pg/JSON all over the place.  In several cases I have to create
 documents with ordered keys because the parser on the other side wants
 them that way -- this is not a hypothetical argument.  The current
 json serialization API handles that just fine and the hstore stuff
 coming down the pike will not.  I guess that's a done deal based on
 'performance'.  I'm clearly not the only one to have complained about
 this though.
 It's not just a matter of performance.  It's the basic conflict of
 JSON as document format vs. JSON as data storage.  For the latter,
 unique, unordered keys are required, or certain functionality isn't
 remotely possible: indexing, in-place key update, transformations, etc.

 XML went through the same thing, which is part of how we got a bunch of
 incompatible dialects of XML.

 Now, your use case does show us that there's a case to be made for still
 having text JSON even after we have binary JSON. 
text-json could easily be a domain (text + check that it is convertible
to json)

maybe it is even possible to teach pg_upgrade to do this automatically
  There's a strong simplicity argument against that, though ...
I think it confuses most people, similar to how storing 1+1 as
processing instructions instead of just evaluationg it and storing 2 :)

OTOH we are in this mess now and have to solve the backwards
compatibility somehow.

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/14/2013 01:42 AM, Andrew Dunstan wrote:

 On 11/13/2013 07:01 PM, Hannu Krosing wrote:

 I guess we should not replace current JSON type with hstore based
 one, but add something json-like based on nested hstore instead.


 Well, that's two voices for that course of action.
I am not really for it (I would have liked to have a
json_object/json_structure instead of
json_string as the meaning of json) but I think there is quite strong
argument
for not breaking backwards compatibility.

 Interesting that I don't think I heard a single voice for this either
 at pgCon or pgOpen,
I attended neither, but I did voice my preferences for _not_ having the
json-as-source-code
type on the mailing lists during previous json discussions.

 although I spent large amounts of time at both talking to people about
 Json, so I'd be interested to hear more voices.

 It would actually simplify things in a way if we do that - we've been
 working on
 a way of doing this that wouldn't upset pg_upgrade. This would render
 that effort unnecessary.
I wonder how hard it would be to rename current json to json_source and
have a new
nested-hstore based json ?


 However it will complicate things for users who will have to choose
 between the data types,
 and function authors who will possibly have to write versions of
 functions to work with both types.
You mostly want the functions for json-object type.

This is supported by the fact that current functions on json-source
treat it as json-object (for example key lookup gives you the value
of latest key and not a list of all matching key values).

You may want some new functions on json-source
(maybe json_source_enumerate_key_values(json, key))
but the current ones are really for json-object.


-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



-- 
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] additional json functionality

[Hannu Krosing]

On 11/14/2013 04:07 PM, Merlin Moncure wrote:
 On Wed, Nov 13, 2013 at 6:01 PM, Hannu Krosing ha...@2ndquadrant.com wrote:

 I guess we should not replace current JSON type with hstore based
 one, but add something json-like based on nested hstore instead.

 Maybe call it jsdoc or jdoc or jsobj or somesuch.
 This is exactly what needs to be done, full stop (how about: hstore).
hstore has completely different i/o formats and thus has similar
backwards compatibility problems.
 It really comes down to this: changing the serialization behaviors
It is really not serialisation behaviours as there is nothing you
can sensibly serialise to have repeated keys.

I agree that you can generate such JSON which would be valid
input tu any json parser, but no JavaScript Object which really serializes
to such JSON.
 that have been in production for 2 releases (three if you count the
 extension) is bad enough, but making impossible some legal json
 constructions which are currently possible is an unacceptable
 compatibility break.  
we should have disallowed this from the beginning and should
have encourages using text as storage for JavaScript source code.
 It's going to break applications I've currently
 put into production with no clear workaround.  
we could rename the old json type during pg_upgrade, but this
would likely break at least implicit casts in functions.
 This is quite frankly
 not ok and and I'm calling foul.  The RFC may claim that these
 constructions are dubious but that's irrelevant.  It's up to the
 parser to decide that and when serializing you are not in control of
 the parser.
You could choose a sane serializer ;)

The main argument here is still weather json is source
code or serialization result for JavaScript Object (Notation).

 Had the json type been stuffed into an extension, there would be a
 clearer path to get to where you want to go since we could have walled
 off the old functionality and introduced side by side API calls.  As
 things stand now, I don't see a clean path to do that.

 I use pg/JSON all over the place.  In several cases I have to create
 documents with ordered keys because the parser on the other side wants
 them that way -- this is not a hypothetical argument.  
But one could argue that this is not json either but rather some
json-like input format for special parsers.

Current recommendation is to use text for these kinds of things.

 The current
 json serialization API handles that just fine and the hstore stuff
 coming down the pike will not.  I guess that's a done deal based on
 'performance'.  I'm clearly not the only one to have complained about
 this though.
 It's not just a matter of performance.  It's the basic conflict of
 JSON as document format vs. JSON as data storage.  For the latter,
 unique, unordered keys are required, or certain functionality isn't
 remotely possible: indexing, in-place key update, transformations, etc.
All these would be possible if we redefined json as another notation
for XML instead of string representation of JavaScript Object :)

And things could really be in-place only inside pl/language functions,
as PostgreSQL is still MVCC.

What should be faster is access to nested values, though I suspect
that it is not significantly faster unless you have very large json
documents.

Cheers

-- 
Hannu Krosing
PostgreSQL Consultant
Performance, Scalability and High Availability
2ndQuadrant Nordic OÜ



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