postgresql – History table design for deletions in PG 11.5

I have a question about drafting a history table in Postgres.

The setup is that I have a table that contains a needs list. A site recalculates requested articles every five minutes and forwards this list to Postgres. The current "hot" list can then be retrieved from various client apps. So, every five minutes, the rows were linked to a specific location and deleted, then refilled with all the items that are now up to date. Imagine a screen on a wall in a warehouse where people are looking for urgent tasks. This is more or less a queue / notification table, not a real memory table.

What we are tracking in the list of requested articles are certain parts with IDs. It is valuable for us to collect data (or at least statistics) over time. Certain items may appear in the list each day, while others are rarely displayed. This can help guide buying decisions and so on.

This is the background, I'm in Postgres 11.5, so no generated columns. Does the strategy described below seem to be correct or can it be improved? The base table is called need and the history table is called need_history

need
– Saves the data of interest
— Has a NOW() Assigned to created_dts on INSERT as part of the table construction.
— Has a PER STATEMENT After triggers, the & # 39; transition table & # 39; to get the deleted lines.
– The instruction trigger INSERTS INTO need_history to preserve the data.

need_history

– It's almost a clone of necessity, but bolted with a few extra boxes. specifically, deleted_dts, assigned with NOW() by default when inserting the data and duration_seconds This saves the number of seconds that the record was in the demand table.
– Since this is PG 11.5, no columns generated, I need one EACH ROW trigger to calculate duration_seconds,

Less:
need

with a delete trigger at statement level, which presses need_history,

need_history

with a row-level trigger for calculation duration_seconds because I did not create any columns in PG 11.x.

And to address the obvious question, no, not me to have to save the derived duration_seconds Value as it can be generated on-the-fly, but in this case I want to denormalize to simplify a variety of queries, sorts, and summaries.

My brain also says, "Ask Fillfactors", and I do not know why.

Below is the code for the initial setup if the summary above is not clear. I have not pushed through any data, so there can be mistakes.

I would be grateful for any advice or advice on how best to do this in Postgres.

BEGIN;

DROP TABLE IF EXISTS data.need CASCADE;

CREATE TABLE IF NOT EXISTS data.need (
    id uuid NOT NULL DEFAULT NULL,
    item_id uuid NOT NULL DEFAULT NULL,
    facility_id uuid NOT NULL DEFAULT NULL,
    hsys_id uuid NOT NULL DEFAULT NULL,
    total_qty integer NOT NULL DEFAULT 0,
    available_qty integer NOT NULL DEFAULT 0,
    sterile_qty integer NOT NULL DEFAULT 0,
    still_need_qty integer NOT NULL DEFAULT 0,
    perc_down double precision NOT NULL DEFAULT '0',
    usage_ integer NOT NULL DEFAULT 0,
    need_for_case citext NOT NULL DEFAULT NULL,
    status citext NOT NULL DEFAULT NULL,
    created_dts timestamptz NOT NULL DEFAULT NOW(),

CONSTRAINT need_id_pkey
    PRIMARY KEY (id)
);


ALTER TABLE data.need OWNER TO user_change_structure;

COMMIT;

/* Define the trigger function to copy the deleted rows to the history table. */
CREATE FUNCTION data.need_delete_copy_to_history()  
  RETURNS trigger AS
$BODY$
BEGIN
        /* need.deleted_dts      is auto-assigned on INSERT over in need, and 
           need.duration_seconds is calculated in an INSERT trigger (PG 11.5, not PG 12, no generated columns). */

   INSERT INTO data.need_history 
            (id,
            item_id,
            facility_id,
            hsys_id,
            total_qty,
            available_qty,
            sterile_qty,
            still_need_qty,
            perc_down,
            usage_,
            need_for_case,
            status,
            created_dts)

     SELECT id,
            item_id,
            facility_id,
            hsys_id,
            total_qty,
            available_qty,
            sterile_qty,
            still_need_qty,
            perc_down,
            usage_,
            need_for_case,
            status,
            created_dts

       FROM deleted_rows;

    RETURN NULL; -- result is ignored since this is an AFTER trigger       
END;
$BODY$
LANGUAGE plpgsql;

 /* Bind a trigger event to the function. */
DROP TRIGGER IF EXISTS trigger_need_after_delete ON data.need;
CREATE TRIGGER trigger_need_after_delete 
    AFTER DELETE ON data.need
    REFERENCING OLD TABLE AS deleted_rows
    FOR EACH STATEMENT EXECUTE FUNCTION data.need_delete_copy_to_history();

/* Define the table. */
BEGIN;

DROP TABLE IF EXISTS data.need_history CASCADE;

CREATE TABLE IF NOT EXISTS data.need_history (
    id uuid NOT NULL DEFAULT NULL,
    item_id uuid NOT NULL DEFAULT NULL,
    facility_id uuid NOT NULL DEFAULT NULL,
    hsys_id uuid NOT NULL DEFAULT NULL,
    total_qty integer NOT NULL DEFAULT 0,
    available_qty integer NOT NULL DEFAULT 0,
    sterile_qty integer NOT NULL DEFAULT 0,
    still_need_qty integer NOT NULL DEFAULT 0,
    perc_down double precision NOT NULL DEFAULT '0',
    usage_ integer NOT NULL DEFAULT 0,
    need_for_case citext NOT NULL DEFAULT NULL,
    status citext NOT NULL DEFAULT NULL,
    created_dts timestamptz NOT NULL DEFAULT NULL,
    deleted_dts timestamptz NOT NULL DEFAULT NOW(),
    duration_seconds int4 NOT NULL DEFAULT 0,

CONSTRAINT need_history_id_pkey
    PRIMARY KEY (id)
);


ALTER TABLE data.need_history OWNER TO user_change_structure;

COMMIT;

/* Define the trigger function to update the duration count.
  In PG 12 we'll be able to do this with a generated column...easier. */

CREATE OR REPLACE FUNCTION data.need_history_insert_trigger() 
  RETURNS trigger AS
$BODY$
BEGIN
/* Use DATE_TRUNC seconds to get just the whole seconds part of the timestamps. */
NEW.duration_seconds =
      EXTRACT(EPOCH FROM (
        DATE_TRUNC('second', NEW.deleted_dts) - 
        DATE_TRUNC('second', NEW.created_dts)
        ));
  RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;


/* Bind a trigger event to the function. */
DROP TRIGGER IF EXISTS trigger_need_history_before_insert ON data.need_history;
CREATE TRIGGER trigger_need_history_before_insert 
    BEFORE INSERT ON data.need_history
    FOR EACH ROW EXECUTE FUNCTION data.need_history_insert_trigger();```

How do I transfer the same table header to multiple tables?

So I have several comparison tables on one page. They all have the same company names at the top for comparison on each table. Each section with a table compares different records. How can I eliminate the need to repeat the company name on every table? I thought about a sticky header, but since each table is divided into different sections, I stay in the same boat (and there are sections of text between each table).

sql – Selects the maximum value from one table and the corresponding value from another table

I would like a query to select the maximum price for each manufacturer and the corresponding model. The field I need to use to join the tables is the model. I use the next query:

Select a.model as modelo, max (a.price) as precio, b.maker as fabricante
from pc a, product b
Where a.model = b.model
Group of a.model, b.maker

More than one model is associated with a manufacturer. For this reason, the query is not grouped by manufacturer.

How should I change it to show only the model, the manufacturer and the maximum price?

javascript – How can I use jquery to calculate the total amount for all records in a table?

Good day to the whole community, I show the data of a form in a table and would like to calculate Total to pay all of the products added in this table and display them in a text box In order for the user to visualize the total amount he has to pay, but I can not get the value, I've tried to go through the whole table with foreach and get and add the subtot, but I have not achieved anything yet. I hope someone can help me I would appreciate it.

// esta funcion me permite agregar los valores del
// formulario a la lista
function AgregarLista() {
    var item = 0;
    $('#btnAgregarTabla').click(function () {
        item++;
        cliente = $('#id_cliente').val();
        codigo = $('#id_producto').select2('val');
        descripcion = $('#nombre_producto').val();
        precio = $('#precio').val();
        cantidad = $('#cantidad').val();
        subtotal = precio * cantidad;

        var fila = '' + item + '' + cliente + '' + codigo + '' + descripcion + '' + precio + '' + cantidad + '' + subtotal + '';
        var btn = document.createElement('tr');
        btn.innerHTML = fila;
        document.getElementById('dtVenta').appendChild(btn);
    });
}

// ESTE ES EL CODIGO DEL FORMULARIO Y LA TABLA
Datos del cliente:
Datos del producto:
Nro.Venta Cliente Codigo Descripcion Precio Cantidad SubTotal Acciones

Trimming the tail does not work on custom Xamarin.iOS table cells

In my CustomTableViewCell class, I set the LineBreakBode:

protected override void CreateView()
{
    ...
    MyLabel = new UILabel {
        Font = UIFont.SystemFontOfSize(17f, UIFontWeight.Regular),
        LineBreakMode = UILineBreakMode.TailTruncation
    };
    ...
}

I would like to see "Lorem ipsum dolor sit amet c …".
But it does not seem to work, the result is the following:

Enter image description here

Any ideas on how to fix this?

columnstore – Redshift: Performance-wise Is it ever preferable to store limited values ​​in a single column instead of normalizing them in another table?

I've tried to increase the performance of some spreadsheets that have been uploaded to a Redshift database. One of the columns in a table contains many limited values ​​that must be split and queried at runtime. The table contains approximately 30 million rows, and an average of 10 delimited values ​​would be displayed in that particular column.

Starting with an OLTP background, my instinct is to normalize these values ​​to another table. However, I have seen several posts that suggest this is not the way to go. I am looking for an advice

magento2 – Magento 2.2: Update customer_eav_attribute table 2.1 to 2.2

After updating from Magento 2.1 to Magento 2.2, I get the following error when saving a client.

Enter image description here

I've seen that in the customer_eav_attribute table, the data was not updated correctly to the new version.

For example, I have:

a: 2: {s: 15: "max_text_length"; i: 255; s: 15: "min_text_length"; i: 1;}

And in version 2.2 it is necessary to have:

{"max_text_length": 255, "min_text_length": 1}

How do I update this table? without reinstalling Magento 2.2

SQL Server – sysmessages table problem

I have an annoying problem with a table called SysMessages in one of our databases.
When I execute a select statement, I do not get the contents of that table but the sys.sysmessages view in the master database.
A "use [DBNAME]" does not help, nor "select * from [DBNAME] .dbo.SysMessages".

I am in the middle of a migration to a newer SQL Server version.
In SQL Server 2008 R2, this problem did not occur with this database, but in SQL Server 2016, I can not query my own SysMessages table.

Any help would be appreciated.

Yours sincerely,

Bruno Piqueur

SQL Server – To set up a custom table for self-calculated statistics

I want to do some analysis in my database and collect the results in a custom spreadsheet or custom report so that I can then export the captured values ​​with my c # tool to create a PowerPoint presentation.

Values are e.g. 
correlation: 
answers:
relative counts:

and so on.

Who can help me here? I have a SQL Server Standard Edition