Re: [GENERAL] journaling / time travel

[George Neuner]

Coming late to this, but ...

On Mon, 19 Sep 2016 17:48:20 +0200, Willy-Bas Loos
 wrote:

>The use case of legal disputes being fought with our data as evidence and
>digging up the exact data from a certain point of time never occurred in
>those 10 years, and it is unlikely that it ever will.
>But it might, if anyone could reasonably expect this to be possible.
>
>:
>
>My question to you all is:
>* Is the legal thing actualy something one could expect of us?
>* Is the security thing really a good practice?
>* Is this a common use case that is normally solved with standard
>components?

I am not a lawyer.

You don't say where you are specifically, but in the US, there is a
legal notion that changes/deletions done in the "normal course of
business" generally are permitted.  That is, e.g., if once a month you
routinely purge records older than 3 years, then you can't be expected
to produce records from 4 years ago.  But you have to prove to the
court that this is normal for your business: e.g., show documentation
of your record keeping procedures.

The problem comes when you do get notice of a legal action.  From that
moment forward you must preserve any data that might be relevent to
the case ... including any new data that is created ... in the event
that it ever is subpoenaed by the court. 

This can become a major issue when you realize that a court case may
drag on for many years, and you may not know exactly what data has to
be preserved.  Lawyers often go on "fishing expeditions", asking for
data in many different ways [by different keywords, etc.], hoping to
find something by comparing the results.

Journaling solves the retention problem and may provide other nice
features like an audit trail of who made the changes.  Of course,
journaling may take a lot of extra space unless it stores only deltas.


Many locales have similar requirements for data preservation in the
face of a legal action.  You need to find out what is expected where
you are.  I'd have to advise that you talk to your lawyers rather than
ask here.

At least in the US, the "normal course of business" applies to archive
data as well as to live data, so you may be able to limit how long you
need to keep the journals.


Hope this ... doesn't further confuse.
George



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

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

[Leonardo M . Ramé]

El 02/09/16 a las 18:28, Scott Marlowe escribió:

On Thu, Sep 1, 2016 at 8:48 PM, Patrick B  wrote:
Why not just subscribe to another cluster on the master, then sub the
slaves to that, then switchover to the new cluster on the master?



Maybe he  doesn't know how to do that, would you care to explain?

Regards,

--
Leonardo M. Ramé
Medical IT - Griensu S.A.
Av. Colón 636 - Piso 8 Of. A
X5000EPT -- Córdoba
Tel.: +54(351)4246924 +54(351)4247788 +54(351)4247979 int. 19
Cel.: +54 9 (011) 40871877


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

Re: [GENERAL] 2.5TB Migration from SATA to SSD disks - PostgreSQL 9.2

[Jim Nasby]

On 9/12/16 3:24 PM, Jeff Janes wrote:

The man page you quote seems clear to me that setting it to 1, rather
than leaving it at 0, makes the opportunity for corruption wider, not
narrower.


Yeah, I actually read it backwards. :/ I don't see how --modify-window 
is helpful at all here; you need to use --ignore-times.



I thought that David's "-1" suggestions was tongue in cheek.  But it
turns out that that actually does work.  Of course, it works by forcing
every file to be copied, which removes the point of using this over
pg_basebackup, but nonetheless it would preserve the integrity of the data.


AFAIK pg_basebackup blindly copies all data files, while rsync will 
transfer only the parts of the files that have actually changed (see 
--block-size). If the source and destination are on different servers, 
that can mean less data transferred over the network.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532)   mobile: 512-569-9461


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

Re: [GENERAL] jsonb_set for nested new item?

[Vitaly Burovoy]

> On Sep 23, 2016 5:12 PM, "Vitaly Burovoy"  wrote:
> On 9/23/16, Deven Phillips  wrote:
>> On Fri, Sep 23, 2016 at 10:14 AM, Deven Phillips
>>  wrote:
>>> Is there a way to set a nested element for which the parent paths do not
>>> yet exist?
>>>
>>> For example, if I have a JSONB value called 'data':
>>>
>>> {
>>> "foo": "bar"
>>> }
>>>
>>> and run
>>>
>>> jsonb_set(data, {'boo', 'baz'}, 'newvalue')
>>>
>>> I would expect the output to be:
>>>
>>> {
>>> "foo": "bar",
>>> "boo": {
>>> "baz": "newvalue"
>>>  }
>>> }
>>>
>>> But that does not appear to work..
>>>
>>> Any suggestions would be appreciated.
>>>
>>
>> Actually, it looks like I have to create all of the parent objects first
>> before it would work... Is that correct?
>>
>> Deven
>
> Yes, you are correct. The documentation[1] says:
>> Returns target ... with new_value added if create_missing is true ...
>> and the item designated by path does not exist.
>
> There is nothing about a "path", only about a "new_value".
> I think it is because of impossibility to understand what intermediate
> objects are needed to be created (objects or arrays).
>
> There is no easy way to create variadic intermediate objects, but in
> your particular case (only one subobject) it can be like:
>
> SELECT
> jsonb_set(
> CASE
> WHEN DATA ? 'boo'
> THEN DATA
> ELSE jsonb_set(DATA, array['boo'], '{}')
> END,
> '{boo,baz}'::text[],
> '"newvalue"'
> )
> FROM (VALUES('{"foo": "bar"}'::jsonb)) AS t(data)
>
>
> [1] https://www.postgresql.org/docs/devel/static/functions-json.html

On 9/23/16, Deven Phillips  wrote:
> Thanks for the confirmation. Unfortunately, I will need to handle more
> complex situations. I will look into creating a recursive subroutine to
> handle things.

In such a case the best way is to create a function:
CREATE OR REPLACE FUNCTION jsonb_set_recursive(data jsonb, path
text[], new_value jsonb)
RETURNS jsonb
LANGUAGE plpgsql AS
$$
DECLARE
chk_path text[];
cur_path text[];
cur_idx text;
cur_value jsonb;
def_obj jsonb default '{}'::jsonb;
BEGIN
chk_path := path[:array_length(path, 1) - 1];
IF (data #> chk_path IS NULL) THEN  -- fast check
FOREACH cur_idx IN ARRAY chk_path
LOOP
cur_path := cur_path || cur_idx;
cur_value = data #> cur_path;

IF (cur_value IS NULL) THEN
data = jsonb_set(data, cur_path, def_obj);
ELSIF (jsonb_typeof(cur_value) NOT IN ('object', 'array')) THEN
RAISE EXCEPTION 'path element by % is neither object
nor array', cur_path;
END IF;
END LOOP;
ELSIF (jsonb_typeof(data #> chk_path) NOT IN ('object', 'array')) THEN
RAISE EXCEPTION 'path element by % is neither object nor
array', chk_path;
END IF;
RETURN jsonb_set(data, path, new_value);
END
$$
STABLE;

and use it:
postgres=# \x
Expanded display is on.
postgres=# SELECT
postgres-# jsonb_set_recursive(data,'{xoo}'::text[],'"newvalue"'),
postgres-# jsonb_set_recursive(data,'{boo,baz}'::text[],'"newvalue"'),
postgres-# 
jsonb_set_recursive(data,'{boo,baG,z,n,2,a}'::text[],'"newvalue"')
postgres-# FROM (VALUES('{"boo": {"baz": "oldvalue"}, "foo":
"bar"}'::jsonb)) AS t(data);
-[ RECORD 1 
]---+--
jsonb_set_recursive | {"boo": {"baz": "oldvalue"}, "foo": "bar",
"xoo": "newvalue"}
jsonb_set_recursive | {"boo": {"baz": "newvalue"}, "foo": "bar"}
jsonb_set_recursive | {"boo": {"baG": {"z": {"n": {"2": {"a":
"newvalue", "baz": "oldvalue"}, "foo": "bar"}


but if a jsonb object has a non-array and non-object value by a path,
exception is raised (you can change it by modifying the function
above):

postgres=# SELECT
postgres-# 
jsonb_set_recursive(data,'{boo,baz,z,n,2,a}'::text[],'"newvalue"')
postgres-# FROM (VALUES('{"boo": {"baz": "oldvalue"}, "foo":
"bar"}'::jsonb)) AS t(data);
ERROR:  path element by {boo,baz} is neither object nor array
CONTEXT:  PL/pgSQL function jsonb_set_recursive(jsonb,text[],jsonb)
line 19 at RAISE

-- 
Best regards,
Vitaly Burovoy


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

Re: [GENERAL] jsonb_set for nested new item?

[René Leonhardt]

Please note that only the first case is not supported in jsonb_set(),
which was a very important addition to PostgreSQL 9.5.
If this case would be added, a simple call with the same path
'{boo,baz}' and '"newvalue"' would always be sufficient.

Goal: first path level (boo) does not exist, create subelement, but if
there is a second path level (baz) requested, create a subdict instead
of just the given value (newvalue).

-- Creating a new 2nd-level dict with a missing 1st-level key/dict just
in the path does not work
SELECT jsonb_set('{"foo": "bar"}'::jsonb, '{boo,baz}', '"newvalue"');

   jsonb_set   

 {"foo": "bar"}

-- Only with the complete subdict as value the new 1st-level key 'boo'
is added(so the caller has to know if the key is missing)
SELECT jsonb_set('{"foo": "bar"}'::jsonb, '{boo}', '{"baz": "newvalue"}');

 jsonb_set 

 {"boo": {"baz": "newvalue"}, "foo": "bar"}


-- All other cases work fine (when the 1st-level key 'boo' already exists)
SELECT jsonb_set('{"foo": "bar", "boo": {"otherkey":
"othervalue"}}'::jsonb, '{boo,baz}', '"newvalue"');

  jsonb_set  
--
 {"boo": {"baz": "newvalue", "otherkey": "othervalue"}, "foo": "bar"}

SELECT jsonb_set('{"foo": "bar", "boo": {"baz": "oldvalue", "otherkey":
"othervalue"}}'::jsonb, '{boo,baz}', '"newvalue"');

  jsonb_set  
--
 {"boo": {"baz": "newvalue", "otherkey": "othervalue"}, "foo": "bar"}



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

Re: [GENERAL] jsonb_set for nested new item?

[René Leonhardt]

Am 23.09.16 um 16:14 schrieb Deven Phillips:

> Is there a way to set a nested element for which the parent paths do not
> yet exist?
>
> For example, if I have a JSONB value called 'data':
>
> {
> "foo": "bar"
> }
>
> and run
>
> jsonb_set(data, {'boo', 'baz'}, 'newvalue')
>
> I would expect the output to be:
>
> {
> "foo": "bar",
> "boo": {
> "baz": "newvalue"
>  }
> }
I don't know why jsonb_set() does not simply allow
SELECT jsonb_set('{"foo": "bar"}'::jsonb, '{boo,baz}', '"newvalue"');
even not in PostgreSQL 9.6.

The trick for now is to use JSONB operators to get the (maybe existing)
old 1st-level value and insert/overwrite the 2nd-level key.

-- 1st-level key 'boo' does not exist
WITH jsonb_table AS (SELECT '{"foo": "bar"}'::jsonb AS jsonb_column)
SELECT jsonb_column, jsonb_set(jsonb_column, '{boo}',
coalesce(jsonb_column->'boo', '{}) || '{"baz": "newvalue"}') FROM
jsonb_table;

  jsonb_column  | jsonb_set 
+
 {"foo": "bar"} | {"boo": {"baz": "newvalue"}, "foo": "bar"}

-- 2nd-level key 'baz' does not exist (but other keys)
WITH jsonb_table AS (SELECT '{"foo": "bar", "boo": {"otherkey":
"othervalue"}}'::jsonb AS jsonb_column)
SELECT jsonb_column, jsonb_set(jsonb_column, '{boo}',
coalesce(jsonb_column->'boo', '{}') || '{"baz": "newvalue"}') FROM
jsonb_table;

   jsonb_column   
|  jsonb_set  
---+--
 {"boo": {"otherkey": "othervalue"}, "foo": "bar"} | {"boo": {"baz":
"newvalue", "otherkey": "othervalue"}, "foo": "bar"}


-- 2nd-level key 'baz' exists (and other keys)
WITH jsonb_table AS (SELECT '{"foo": "bar", "boo": {"baz": "oldvalue",
"otherkey": "othervalue"}}'::jsonb AS jsonb_column)
SELECT jsonb_column, jsonb_set(jsonb_column, '{boo}',
coalesce(jsonb_column->'boo', '{}') || '{"baz": "newvalue"}') FROM
jsonb_table;

 jsonb_column
|  jsonb_set  
--+--
 {"boo": {"baz": "oldvalue", "otherkey": "othervalue"}, "foo": "bar"} |
{"boo": {"baz": "newvalue", "otherkey": "othervalue"}, "foo": "bar"}

Please note that the actual jsonb_set() call is always the same, only
jsonb_column changes to show all possible cases.
The 2 JSONB literals empty/new in the jsonb_set() call need no casting,
just the jsonb_column for the -> operator and jsonb_set() to work
(already done in the WITH clause).

The WITH clause is just there for this example, otherwise you would have
to duplicate the same value. Just use your existing JSONB column instead.

JSONB sorts the keys in alphanumerical order, so don't get confused by
the insert positions.


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