This project forked from scalegenius/pg_bitemporal
Bitemporal tables in Postgres
License: BSD 3-Clause "New" or "Revised" License
Hi Hettie,
We found a small problem.
PG-version 13
Updating a column to null fails when the column is of type other than text.
select * from bitemporal_internal.ll_create_bitemporal_table(
'common',
'person',
$$id int not null,
name text,
phone_number int$$,
$$id$$
);
select bitemporal_internal.ll_bitemporal_insert('common.person',
$$id, name, phone_number$$,
$$1,'john doe', 0123456789$$,
temporal_relationships.timeperiod(now(), 'infinity'),
temporal_relationships.timeperiod(now(), 'infinity'));
=> select * from person;
person_key | id | name | phone_number | effective | asserted | row_created_at
------------+----+----------+--------------+--------------------------------------------+--------------------------------------------+-------------------------------
1 | 1 | john doe | 123456789 | ["2022-04-20 07:48:18.634407+00",infinity) | ["2022-04-20 07:48:18.634407+00",infinity) | 2022-04-20 07:48:18.634407+00
-- Then we update the phone number to null
=> select bitemporal_internal.ll_bitemporal_update('common','person',
$$phone_number$$,
$$null$$,
$$id$$,
$$1$$,
temporal_relationships.timeperiod(now(), 'infinity'),
temporal_relationships.timeperiod(now(), 'infinity'));
ERROR: column "phone_number" is of type integer but expression is of type text
LINE 1: UPDATE common.person SET (phone_number) = (select null )
^
HINT: You will need to rewrite or cast the expression.
QUERY: UPDATE common.person SET (phone_number) = (select null )
WHERE ( person_key )in ( 5 )
CONTEXT: PL/pgSQL function bitemporal_internal.ll_bitemporal_update(text,text,text,text,text,text,temporal_relationships.timeperiod,temporal_relationships.timeperiod) line 89 at EXECUTEWe appreciate your help. Thanks.
I know there are two functions to add a unique constraint, unique_constraint and add_constraint. But unique_constraint can only add a constraint on one column.
I tried to do something like this
alter table bt_table
add constraint unique_bt_table
exclude using gist (col1 with =, col2 with =, asserted with &&, effective with &&);This works for entities that haven't been inactivated/deleted but fails for entities that have been inactivated/deleted.
How do I proceed with this? Is this something that shouldn't be done?
The ll_bitemporal_update_select is limited when it comes to doing a bulk bitemporal update. It allows updating of multiple records but only if the data is same (or uses context of the updating row).
It would be great if the same behaviour can be made possible as in ll_bitemporal_insert_select. Which allows you to insert multiple records with different data.
I did some changes to the function and made it to work for my case. It would be great if you can review it. Happy to raise a PR if you don't see any problems in it.
There are 4 major operations that happens in the update_select function:
The 4th step uses this update statement to update the records:
format($u$ UPDATE %s t SET (%s) = (%s)
WHERE ( %s ) in ( %s ) $u$
, v_table
, p_list_of_fields
, p_values_selected_update
, v_serial_key
, array_to_string(v_keys,','));
For this to work the p_values_selected_update select query must return only one record.
If we can modify it to use the FROM clause, we can achieve the bulk update. Here's what I've done:
This is what my function call looks like:
SELECT bitemporal_internal.ll_bitemporal_update_select(
'org',
'test_table,
'id,first_name,emails',
$$SELECT id, first_name, emails FROM (VALUES ('4143558004', 'Raghav', CAST(ARRAY['[email protected]'] AS text[])), ('4028052004', 'Hettie', CAST(ARRAY['[email protected]'] AS text[]))) ASt(id, first_name, emails)$$,
'id',
$$SELECT id FROM (VALUES ('4143558004', 'Raghav', CAST(ARRAY['[email protected]'] AS text[])), ('4028052004', 'Hettie', CAST(ARRAY['[email protected]'] AS text[]))) AS t(id, first_name, emails)$$,
TEMPORAL_RELATIONSHIPS.TIMEPERIOD(NOW(), CAST('infinity' AS temporal_relationships.time_endpoint)),
TEMPORAL_RELATIONSHIPS.TIMEPERIOD(NOW(), CAST('infinity' AS temporal_relationships.time_endpoint)))
Here's what I modified in the function:
EXECUTE format($s$ SELECT 'x.' || REPLACE(%L, ',', ',x.') $s$, p_list_of_fields) into x_alias_list_of_fields;
EXECUTE format($s$ SELECT 'x.' || REPLACE(%L, ',', ',x.') $s$, p_search_fields) into x_alias_p_search_fields;
EXECUTE format($s$ SELECT 't.' || REPLACE(%L, ',', ',t.') $s$, p_search_fields) into t_alias_p_search_fields;
EXECUTE format($u$ UPDATE %s t SET (%s) = (%s)
FROM (%s) as x WHERE ( %s ) in ( %s ) AND (%s) = (%s) $u$
, v_table
, p_list_of_fields
, x_alias_list_of_fields
, p_values_selected_update
, v_serial_key
, array_to_string(v_keys,',')
, x_alias_p_search_fields
, t_alias_p_search_fields);
The update query will now look like:
UPDATE
test_table t
SET
(id, first_name, emails) = (x.id, x.first_name, x.emails)
FROM (
SELECT
id,
first_name,
emails
FROM (VALUES
('4143558004', 'Raghav', CAST(ARRAY['[email protected]'] AS text[])),
('4028052004', 'Hettie', CAST(ARRAY['[email protected]'] AS text[]))
) AS t(id, first_name, emails)
) AS x
WHERE (test_table_bt_key) IN (83, 84) AND (x.id) = (t.id)
I was referring to this https://github.com/hettie-d/pg_bitemporal/blob/master/generate_bitemporal/generate_bt_update_trigger_template.sql#L39 to implement a similar trigger and was trying to use bitemporal_internal.ll_bitemporal_correction_select instead of bitemporal_internal.ll_bitemporal_correction.
I am inside a transaction and doing two updates, the second update triggers a correction.
When using the bitemporal_internal.ll_bitemporal_correction_select, the operation fails because of the asserted interval of both the correct and incorrect record overlap.
I looked at the implementation at ll_bitemporal_correction and found that
upper(asserted) (https://github.com/hettie-d/pg_bitemporal/blob/master/sql/ll_bitemporal_correction.sql#L51) is being used instead of now() (https://github.com/hettie-d/pg_bitemporal/blob/master/sql/ll_bitemporal_correction_select.sql#L42)
Is this intentional, or is this a bug?
Additionally, the arguments to the ll_bitemporal_correction_select are not qualified by a schema which causes the function creation to fail if the search_path is not set
https://github.com/hettie-d/pg_bitemporal/blob/master/sql/ll_bitemporal_correction_select.sql#L7
A small example to reproduce this problem.
select bitemporal_internal.ll_create_bitemporal_table(
'common', 'person', $$id int not null,
name text, phone_number int$$, $$id$$
);
select
bitemporal_internal.ll_bitemporal_insert(
'common.person',
$$id,
name,
phone_number$$,
$$2,
'Joel Victor',
0123456789$$,
temporal_relationships.timeperiod(now(), 'infinity'),
temporal_relationships.timeperiod(now(), 'infinity')
);
begin;
select
bitemporal_internal.ll_bitemporal_update(
'common', 'person',
$$name,phone_number$$,
$$'Joel S. Victor',0123456789$$,
$$id$$,$$ 2$$,
temporal_relationships.timeperiod(now(), 'infinity'),
temporal_relationships.timeperiod(now(), 'infinity')
);
-- this fails
select
bitemporal_internal.ll_bitemporal_correction_select (
'common.person'::text,
$$name,phone_number$$::text,
$$'Joel S V',0123456789$$::text,
$$id = '2'$$::text,
now(),
now()
);
-- this also fails
select
bitemporal_internal.ll_bitemporal_correction_select (
'common.person'::text,
$$name,phone_number$$::text,
$$'Joel S V',0123456789$$::text,
$$id = '2'$$::text,
now(),
clock_timestamp()
);
-- this works
select
bitemporal_internal.ll_bitemporal_correction (
'common'::text,'person'::text,
$$name,phone_number$$::text,
$$'Joel S V',0123456789$$::text,
$$id$$::text,
$$2$$::text,
temporal_relationships.timeperiod(now(), 'infinity'),
now()
);
abort;
SQL Error [23P01]: ERROR: conflicting key value violates exclusion constraint "person_id_assert_eff_excl"
Detail: Key (id, asserted, effective)=(2, ["2022-08-23 16:22:25.25041+05:30",infinity), ["2022-08-23 16:22:23.056263+05:30",infinity)) conflicts with existing key (id, asserted, effective)=(2, ["2022-08-23 16:22:25.25041+05:30",infinity), ["2022-08-23 16:22:23.056263+05:30","2022-08-23 16:22:25.25041+05:30")).
Where: SQL statement "INSERT INTO common.person ( id,name,phone_number, effective, asserted )
SELECT id,name,phone_number ,effective, temporal_relationships.timeperiod_range(now(),
'infinity', '[)')
FROM common.person WHERE id = '2' AND '2022-08-23 16:22:25.25041+05:30'::timestamptz <@ effective
AND upper(asserted)= '2022-08-23 16:22:25.25041+05:30'
"
PL/pgSQL function bitemporal_internal.ll_bitemporal_correction_select(text,text,text,text,time_endpoint,time_endpoint) line 31 at EXECUTE
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
Django
The Web framework for perfectionists with deadlines.
Laravel
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
Microsoft
Open source projects and samples from Microsoft.
Google
Google ❤️ Open Source for everyone.
Alibaba
Alibaba Open Source for everyone
D3
Data-Driven Documents codes.
Tencent
China tencent open source team.