Re: [GENERAL] Oracle to PostgreSQL
On Thu, 2017-11-09 at 17:01 +0530, Brahmam Eswar wrote: > You can create a composite type in PostgreSQL: > CREATE TYPE complex AS (r integer, i integer); > You would use an array in this case: > DECLARE > carr complex[]; I've once faced the same thing, and did as Laurenz suggested. You will like Postgres' array support. -- 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] Trigger based logging alternative to table_log
> I have some code which uses table_log > (http://pgfoundry.org/projects/tablelog/) to keep a log of changes to > selected tables. > I don't use the restore part, just the logging part. > It creates a new table for each table being logged, with several additional > columns, and adds triggers to insert rows in the new table for changes in the > original. > The problem is that table_log hasn't been maintained in nearly 10 years, and > pgfoundry itself seems to have one foot in the grave and one on a banana peel. > There are several other systems out there which store the data in hstore or > json, which I would probably use if doing this from scratch. But I'd rather > preserve the existing log tables than either throw away that data, or port it > over to a new format. > Is there any better-maintained code out there which would be compatible with > the existing schema used by table_log? >Cheers, >Jeff Afaik, there is no compatible solution. If tablelog works for you then keep it. Do you miss a feature or why do you worry about the unmaintained code base? I think, if there would be a problem with any new version that the developer would fix it. There is also an existing github repo (https://github.com/glynastill/table_log_pl). Recently, I have done a comparison of different audit tools to check how good my creation (pgMemento) works compared to the others. So I know how most of them work. tablelog for example logs both OLD and NEW. So you got all your data twice. Other solutions log either OLD of NEW. tablelog uses only one timestamp field whereas others using two (or a range). As tablelog is using history tables with relational layout I would suggest to consider other extensions that do a similar thing. If you are interested in only logging the data you might check out the temporal_tables extension (http://pgxn.org/dist/temporal_tables/). In my test it had the least impact to write operations and disk consumption. Using hstore or json for logging might sound cool in the first place, but it only has its benefits if you don't want to adapt the auditing behaviour to schema changes (like new columns etc.). With pgMemento I decided to go for jsonb but after many hours of programming complex restoring functions I can say that my only real argument of using it now, is that I only log values of changed fields. I like that but it makes the trigger overhead bigger. Greetings from Berlin Felix -- 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] Getting a delta of two json-typed objects? (a breeze w/ hstore)
Hi, not so long ago, I've asked myself the same question. I've written two
fuction that do this:
SELECT build_json(array_agg(to_json(old.key)), array_agg(old.value))
FROM json_each($1) old
LEFT OUTER JOIN json_each($2) new ON old.key = new.key
WHERE old.value::text new.value::text OR new.key IS NULL
HAVING array_agg(to_json(old.key)) IS NOT NULL
AND array_agg(old.value) IS NOT NULL;
and this:
CREATE OR REPLACE FUNCTION pgmemento.build_json(
json_keys ANYARRAY,
json_values ANYARRAY
) RETURNS JSON AS
$$
DECLARE
json_string TEXT := '{';
delimeter TEXT := '';
json_result JSON;
BEGIN
FOR i IN array_lower(json_keys, 1)..array_upper(json_keys, 1) LOOP
json_string := json_string || delimeter || json_keys[i] || ':' ||
json_values[i];
delimeter := ',';
END LOOP;
json_string := json_string || '}';
EXECUTE format('SELECT %L::json', json_string) INTO json_result;
RETURN json_result;
END
$$
LANGUAGE plpgsql;
Not the best way actually. I wonder, how I could benefit from the new
build_json function in 9.4 json_build_object(VARIADIC any). Have to get my
keys and values in alternating order... hm.
Then I've also found this nice example, which might do the things you are
looking for:
http://schinckel.net/2014/05/25/querying-json-in-postgres/
Ahoi
Felix
Gesendet: Donnerstag, 22. Januar 2015 um 20:37 Uhr
Von: Wells Oliver wellsoli...@gmail.com
An: pgsql-general@postgresql.org pgsql-general@postgresql.org
Betreff: [GENERAL] Getting a delta of two json-typed objects? (a breeze w/
hstore)
Hey all. I have a trigger function which does a delta of two hstore values just
doing a - b; this check is performed to see if there's a delta and if not I
don't log it.
I'm wondering if there's a suitable method for comparison two json objects? I
don't have 9.4 yet so I can't use jsonb, but if there's any input here I'd
appreciate it.
Thanks.
--
Wells Oliver
wellsoli...@gmail.com
--
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] table versioning approach (not auditing)
Hey there. Thank you very much for that fix! Thats why I'd like to have a joint development and joint testing. It's way more convincing for users to go for a solution that is tested by some experts than just by a random developer :) I'm open to create a new project and push the code there. Don't care about the name. Then we might figure out which parts are already good, which parts could be improved and where to go next. I think switching to JSONB for example will be easy, as it offers the same functions than JSON afaik. Gesendet: Dienstag, 30. September 2014 um 21:16 Uhr Von: Adam Brusselback adambrusselb...@gmail.com An: Felix Kunde felix-ku...@gmx.de Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Betreff: Re: [GENERAL] table versioning approach (not auditing) Felix, I'd love to see a single, well maintained project. For example, I just found yours, and gave it a shot today after seeing this post. I found a bug when an update command is issued, but the old and new values are all the same. The trigger will blow up. I've got a fix for that, but if we had one project that more than a handful of people used, stuff like that would be quashed very quickly. I love the design of it by the way. Any idea what it will take to move to JSONB for 9.4? On Tue, Sep 30, 2014 at 7:22 AM, Felix Kunde felix-ku...@gmx.de wrote:Hey yes i'm adding an additional key to each of my tables. First i wanted to use the primary key as one column in my audit_log table, but in some of my tables the PK consists of more than one column. Plus it's nice to have one key that is called the same over all tables. To get a former state for one row at date x I need to join the latest delta BEFORE date x with each delta AFTER date x. If I would log complete rows, this joining part would not be neccessary, but as I usually work with spatial databases that have complex geometries and also image files, this strategy is too harddisk consuming. If there are more users following a similar approach, I wonder why we not throw all the good ideas together, to have one solution that is tested, maintained and improved by more developpers. This would be great. Felix Gesendet: Montag, 29. September 2014 um 23:25 Uhr Von: Abelard Hoffman abelardhoff...@gmail.com[abelardhoff...@gmail.com] An: Felix Kunde felix-ku...@gmx.de[felix-ku...@gmx.de] Cc: pgsql-general@postgresql.org[pgsql-general@postgresql.org] pgsql-general@postgresql.org[pgsql-general@postgresql.org] Betreff: Re: [GENERAL] table versioning approach (not auditing) Thank you Felix, Gavin, and Jonathan for your responses. Felix Jonathan: both of you mention just storing deltas. But if you do that, how do you associate the delta record with the original row? Where's the PK stored, if it wasn't part of the delta? Felix, thank you very much for the example code. I took a look at your table schemas. I need to study it more, but it looks like the way you're handling the PK, is you're adding a separate synthethic key (audit_id) to each table that's being versioned. And then storing that key along with the delta. So then to find all the versions of a given row, you just need to join the audit row with the schema_name.table_name.audit_id column. Is that right? The only potential drawback there is there's no referential integrity between the audit_log.audit_id and the actual table. I do like that approach very much though, in that it eliminates the need to interrogate the json data in order to perform most queries. AH On Mon, Sep 29, 2014 at 12:26 AM, Felix Kunde felix-ku...@gmx.de[felix-ku...@gmx.de] wrote:Hey i've also tried to implement a database versioning using JSON to log changes in tables. Here it is: https://github.com/fxku/audit[https://github.com/fxku/audit][https://github.com/fxku/audit[https://github.com/fxku/audit]] I've got two versioning tables, one storing information about all transactions that happened and one where i put the JSON logs of row changes of each table. I'm only logging old values and not complete rows. Then I got a function that recreates a database state at a given time into a separate schema - either to VIEWs, MVIEWs or TABLES. This database state could then be indexed in order to work with it. You can also reset the production state to the recreated past state. Unfortunately I've got no time to further work on it at the moment + I have not done tests with many changes in the database so I can't say if the recreation process scales well. On downside I've realised is that using the json_agg function has limits when I've got binary data. It gets too long. So I'm really looking forward using JSONB. There are more plans in my mind. By having a Transaction_Log table it should be possible to revert only certain transactions. I'm also thinking of parallel versioning, e.g. different users are all working with their version of the database and commit their changes
Re: [GENERAL] table versioning approach (not auditing)
Hey yes i'm adding an additional key to each of my tables. First i wanted to use the primary key as one column in my audit_log table, but in some of my tables the PK consists of more than one column. Plus it's nice to have one key that is called the same over all tables. To get a former state for one row at date x I need to join the latest delta BEFORE date x with each delta AFTER date x. If I would log complete rows, this joining part would not be neccessary, but as I usually work with spatial databases that have complex geometries and also image files, this strategy is too harddisk consuming. If there are more users following a similar approach, I wonder why we not throw all the good ideas together, to have one solution that is tested, maintained and improved by more developpers. This would be great. Felix Gesendet: Montag, 29. September 2014 um 23:25 Uhr Von: Abelard Hoffman abelardhoff...@gmail.com An: Felix Kunde felix-ku...@gmx.de Cc: pgsql-general@postgresql.org pgsql-general@postgresql.org Betreff: Re: [GENERAL] table versioning approach (not auditing) Thank you Felix, Gavin, and Jonathan for your responses. Felix Jonathan: both of you mention just storing deltas. But if you do that, how do you associate the delta record with the original row? Where's the PK stored, if it wasn't part of the delta? Felix, thank you very much for the example code. I took a look at your table schemas. I need to study it more, but it looks like the way you're handling the PK, is you're adding a separate synthethic key (audit_id) to each table that's being versioned. And then storing that key along with the delta. So then to find all the versions of a given row, you just need to join the audit row with the schema_name.table_name.audit_id column. Is that right? The only potential drawback there is there's no referential integrity between the audit_log.audit_id and the actual table. I do like that approach very much though, in that it eliminates the need to interrogate the json data in order to perform most queries. AH On Mon, Sep 29, 2014 at 12:26 AM, Felix Kunde felix-ku...@gmx.de wrote:Hey i've also tried to implement a database versioning using JSON to log changes in tables. Here it is: https://github.com/fxku/audit[https://github.com/fxku/audit] I've got two versioning tables, one storing information about all transactions that happened and one where i put the JSON logs of row changes of each table. I'm only logging old values and not complete rows. Then I got a function that recreates a database state at a given time into a separate schema - either to VIEWs, MVIEWs or TABLES. This database state could then be indexed in order to work with it. You can also reset the production state to the recreated past state. Unfortunately I've got no time to further work on it at the moment + I have not done tests with many changes in the database so I can't say if the recreation process scales well. On downside I've realised is that using the json_agg function has limits when I've got binary data. It gets too long. So I'm really looking forward using JSONB. There are more plans in my mind. By having a Transaction_Log table it should be possible to revert only certain transactions. I'm also thinking of parallel versioning, e.g. different users are all working with their version of the database and commit their changes to the production state. As I've got a unique history ID for each table and each row, I should be able to map the affected records. Have a look and tell me what you think of it. Cheers Felix Gesendet: Montag, 29. September 2014 um 04:00 Uhr Von: Abelard Hoffman abelardhoff...@gmail.com An: pgsql-general@postgresql.org pgsql-general@postgresql.org Betreff: [GENERAL] table versioning approach (not auditing) Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.). I have studied these two audit trigger examples: https://wiki.postgresql.org/wiki/Audit_trigger[https://wiki.postgresql.org/wiki/Audit_trigger] https://wiki.postgresql.org/wiki/Audit_trigger_91plus I've also read about two other approaches to versioning: 1. maintain all versions in one table, with a flag to indicate which is the current version 2. have a separate versions table for each real table, and insert into the associated version table whenever an update or insert is done. My current implementation is based on the wiki trigger examples, using a single table, and a json column to record the row changes (rather than hstore). What I like about that, in particular, is I can have a global, chronological view of all versioned changes very easily. But there are two types of queries I need to run. 1. Find all changes made by a specific user 2. Find all changes related to a specific record #1 is simple to do. The versioning table has a user_id
Re: [GENERAL] table versioning approach (not auditing)
Hey i've also tried to implement a database versioning using JSON to log changes in tables. Here it is: https://github.com/fxku/audit I've got two versioning tables, one storing information about all transactions that happened and one where i put the JSON logs of row changes of each table. I'm only logging old values and not complete rows. Then I got a function that recreates a database state at a given time into a separate schema - either to VIEWs, MVIEWs or TABLES. This database state could then be indexed in order to work with it. You can also reset the production state to the recreated past state. Unfortunately I've got no time to further work on it at the moment + I have not done tests with many changes in the database so I can't say if the recreation process scales well. On downside I've realised is that using the json_agg function has limits when I've got binary data. It gets too long. So I'm really looking forward using JSONB. There are more plans in my mind. By having a Transaction_Log table it should be possible to revert only certain transactions. I'm also thinking of parallel versioning, e.g. different users are all working with their version of the database and commit their changes to the production state. As I've got a unique history ID for each table and each row, I should be able to map the affected records. Have a look and tell me what you think of it. Cheers Felix Gesendet: Montag, 29. September 2014 um 04:00 Uhr Von: Abelard Hoffman abelardhoff...@gmail.com An: pgsql-general@postgresql.org pgsql-general@postgresql.org Betreff: [GENERAL] table versioning approach (not auditing) Hi. I need to maintain a record of all changes to certain tables so assist in viewing history and reverting changes when necessary (customer service makes an incorrect edit, etc.). I have studied these two audit trigger examples: https://wiki.postgresql.org/wiki/Audit_trigger https://wiki.postgresql.org/wiki/Audit_trigger_91plus I've also read about two other approaches to versioning: 1. maintain all versions in one table, with a flag to indicate which is the current version 2. have a separate versions table for each real table, and insert into the associated version table whenever an update or insert is done. My current implementation is based on the wiki trigger examples, using a single table, and a json column to record the row changes (rather than hstore). What I like about that, in particular, is I can have a global, chronological view of all versioned changes very easily. But there are two types of queries I need to run. 1. Find all changes made by a specific user 2. Find all changes related to a specific record #1 is simple to do. The versioning table has a user_id column of who made the change, so I can query on that. #2 is more difficult. I may want to fetch all changes to a group of tables that are all related by foreign keys (e.g., find all changes to user record 849, along with any changes to their articles, photos, etc.). All of the data is in the json column, of course, but it seems like a pain to try and build a query on the json column that can fetch all those relationships (and if I mess it up, I probably won't generate any errors, since the json is so free-form). So my question is, do you think using the json approach is wrong for this case? Does it seem better to have separate versioning tables associated with each real table? Or another approach? Thanks -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] memory issues with BYTEAs in JSON and hstore
Hey, I've developed a database auditing/versioning using the JSON data type (http://github.com/fxku/audit) and doing some tests now. Unfortunately I'm facing some problems when dealing with tables that store images as BYTEA. Some of them are around 15 MB big. My tool logs changes to JSON and can populate it back to views with json_populate_recordset and json_agg. When performing this procedure on tables containing BYTEAs I receive an 54000 error (Cannot enlarge string buffer). Is this because of json_agg or because of single rows? Executing to_json on the whole column that contains the binary data (size above 500 MB) lead to out-of-memory errors. The same goes for hstore. Executing these functions only on the biggest image was successful but freezed my pgAdmin. When I encoded BYTEA to TEXT before transforming it to JSON or hstore it worked. But trying json_populate_recordset still runs into memory problems (but explain worked). Do you think JSONB will solve my problems in the future? Here is also a comparison in size between the bytea (and encoded versions to TEXT) and JSON / hstore output which I found kinda interesting: operation | bytea | bytea-'escape' | bytea-'hex' | bytea-'base64' --|---|-|--| | 15 MB | 40 MB | 31 MB| 21 MB to_json() | 57 MB | 57 MB | 31 MB| 21 MB hstore() | 46 MB | 40 MB | 31 MB| 21 MB Thanks in advance for any hints. Cheers, Felix -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] comparing two JSON objects in 9.3
Ahoi
Im developing an auditing mechanism where table contents are logged as JSON (github.com/fxku/audit).
At first I just logged the whole row everytime my triggers were fired.
Now Im trying to log only the old state of values that have changed.
Therefore I compare the OLD and NEW entry by using json_each(json).
SELECT old.key, old.value
FROM json_each(row_to_json(OLD)) old
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key
WHERE new.value::text old.value::text OR new.key IS NULL;
Now I aggregate the result to arrays of JSON and call this function, that Ive written:
CREATE OR REPLACE FUNCTION audit.build_json(json_keys ANYARRAY, json_values ANYARRAY) RETURNS JSON AS
DECLARE
json_string TEXT := {;
delimeter TEXT := ;
json_result JSON;
BEGIN
FOR i IN array_lower(json_keys, 1)..array_upper(json_keys, 1) LOOP
json_string := json_string delimeter json_keys[i] : json_values[i];
delimeter := ,;
END LOOP;
json_string := json_string };
EXECUTE format(SELECT %L::json, json_string) INTO json_result;
RETURN json_result;
END
LANGUAGE plpgsql;
In the end the call looks like this:
SELECT audit.build_json(array_agg(to_json(old.key)), array_agg(old.value))
FROM json_each(row_to_json(OLD)) old
LEFT OUTER JOIN json_each(row_to_json(NEW)) new ON old.key = new.key
WHERE new.value::text old.value::text OR new.key IS NULL;
Everything works as expected, but it feels kinda ugly to me.
Any PG-JSON experts around to tell me a better solution?
Did i trapped into some YouShouldNots?
Regards!
Felix Kunde
Re: [GENERAL] Temporary table already exists
I had a similar problem once. The pool is reusing connections and the temporary tables are still there.
Now I always create new temporary tables with a unique name like this:
tmpTableId = TMP + Math.abs(generateUUID().hashCode());
if (tmpTableId.length() 15)
tmpTableId = tmpTableId.substring(tmpTableId.length() - 15, tmpTableId.length());
conn.setAutoCommit(true);
tableStmt = conn.createStatement();
try {
// create global temporary tables
tableStmt.executeUpdate(create temporary table TABLE_ANME_ + tmpTableId + ( ... ) on commit preserve rows);
etc.
Then you have to add the tmpTableId to every statement in your code but it should work fine.
Gesendet:Freitag, 31. Januar 2014 um 12:04 Uhr
Von:mephysto mephystoonh...@gmail.com
An:pgsql-general@postgresql.org
Betreff:Re: [GENERAL] Temporary table already exists
Hi Albe, this is code of my stored function:
CREATE OR REPLACE FUNCTION :FUNCTION_SCHEMA.get_deck_types
(
p_id_deck BIGINT
)
RETURNS BIGINT[] AS
DECLARE
l_id_user BIGINT;
l_cards_number INTEGER;
l_deck_type BIGINT;
l_result BIGINT[];
BEGIN
SELECT INTO STRICT l_id_user id_user
FROM ccg_schema.decks_per_user
WHERE id = p_id_deck;
CREATE LOCAL TEMPORARY TABLE deck_types
ON COMMIT DROP
AS
SELECT stored_functions_v0.get_card_deck_types(t1.id_master_card) AS deck_type_ids
FROM ccg_schema.deck_composition T0
,ccg_schema.cards_per_user T1
WHERE id_deck = p_id_deck
AND t1.id_owner = l_id_user
AND t0.id_card = t1.id_card;
SELECT INTO l_cards_number COUNT(*)
FROM deck_types;
FOR l_deck_type IN SELECT DISTINCT unnest(deck_type_ids) FROM deck_types LOOP
IF (l_cards_number = (SELECT COUNT(*) FROM (SELECT unnest(deck_type_ids) AS id FROM deck_types) T0 WHERE id = l_deck_type)) THEN
l_result := array_append(l_result, l_deck_type);
END IF;
END LOOP;
RETURN l_result;
END;
LANGUAGE PLPGSQL VOLATILE;
ConnectionPool reuse connections, of course, but how you can see from my code, the temporary table deck_types are already defined with ON COMMIT DROP clause, so I think that my work is not in transaction. Am I true? If so, how can I put my code in transaction? Many thanks. Mephysto
View this message in context: Re: Temporary table already exists
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
[GENERAL] Audit database to recreate former states using JSON functions
Hello last december Ive delvoped a few PL/pgSQL functions to log all changes in my database and recreate former database states. I used row_to_json to log and json_populate_recordset to reproduce my tables. I think its cool feature and I like to share and discuss it with you. I would be very happy to recieve some feedback. Im also interested in other versioning approaches, if there are (?). You can find the repository here: https://github.com/FxKu/audit Ive written all the relevant stuff into the README. At last, I really like to thank Hans-Jrgen Schnig (http://www.cybertec.at/) for giving me the advice to use JSON functions. This made the whole developement way more easy. Have a great day. Felix
