[postgresql] Probs met het terug leggen van de primary key *

[MadMan81]

Ik heb de volgende functie om mijn host tabel te updaten en wat op te ruimen (ivm preformance).

code:

CREATE FUNCTION update_host(varchar,cidr)
RETURNS text
AS 'DECLARE
    in_new_hostname ALIAS FOR $1;
    in_new_ip ALIAS FOR $2;
    Max_lastseen timestamp without time zone;
    Sum_total integer;
    new_hostid integer;

-- This function is to be used to update a hostname, it wil create a new hostID and copy all data to that host ID
-- The will reslut in some less detail information about the hosts that flushed, but wil increase the preformance
-- If the loss of detail is more important then the preformance, normal updates on the hosts table should be used
    BEGIN
    --lock tables
    LOCK hosts, blocks, sum_blocks, dupes IN ACCESS EXCLUSIVE MODE;
    --get Max_lastseen and Sum_total to create a new hostid
    SELECT INTO Max_lastseen max(lastseen)
        FROM hosts
        WHERE hostip <<= in_new_ip;
    SELECT INTO Sum_total sum(total)
        FROM hosts
        WHERE hostip <<= in_new_ip;
    INSERT INTO hosts (hostip,hostname,lastseen,total)
        VALUES (in_new_ip,in_new_hostname,Max_lastseen,Sum_total);
    --get the new hostid
    SELECT INTO new_hostid last_value
        FROM hosts_hostid_seq;
    --fix the blocks table
    UPDATE blocks set hostid=new_hostid
        where hostid in
        (select hostid from hosts where hostip <<= in_new_ip and hostid <> new_hostid);
    --and the dupes table
    UPDATE dupes set hostid=new_hostid
        where hostid in
        (select hostid from hosts where hostip <<= in_new_ip and hostid <> new_hostid);
    --to fix sum_blocks the PK has to be droped, then the update can take place
    --After that an agragation is done and the result is put in a temp table
    --the old record will then be delete from the sum_blcoks table and the data from the temp table is copied
    --At last, the temp table is removed and the old hosts are removed
    ALTER TABLE sum_blocks DROP CONSTRAINT sum_blocks_pkey;
    UPDATE sum_blocks set hostid=new_hostid
        where hostid in
        (select hostid from hosts where hostip <<= in_new_ip and hostid <> new_hostid);

    CREATE TEMP TABLE s_blocks as
        Select blockdate, blockhour, hostid, email, osid, cpuid, version, core, sum(amount) as amount
         from sum_blocks
         where hostid = new_hostid
         group by blockdate, blockhour, hostid, email, osid, cpuid, version, core;

    DELETE from sum_blocks where hostid=new_hostid;
    ALTER TABLE sum_blocks ADD PRIMARY KEY (blockdate, blockhour, hostid, email, osid, cpuid, "version", core);
    INSERT INTO sum_blocks (blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount)
        SELECT blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount from s_blocks;
    DROP TABLE s_blocks;
    DELETE from hosts
        where hostip <<= in_new_ip and hostid <> new_hostid;
    RETURN (''OK'');
    END;'
LANGUAGE 'plpgsql';

Nu gaat dat bij een heleboel hosts en ranges prima, echter bij sommige krijg ik de volgende foutmelding (Regelnummering is vanaf AS):

code:

ERROR:  Cannot create unique index. Table contains non-unique values
WARNING:  Error occurred while executing PL/pgSQL function update_host
WARNING:  line 50 at SQL statement

Maar volgens mij KAN dat niet, behalve als hij op het momen van het opleggen van die PK nog niet klaar is met het deleten...

Iemand een idee? En als mijn theorie klopt, hoe los ik dat dan op?

[MadMan81]

Oeps, vergeten de topic titel af te maken, zou een van de mods hem kunnen aanpassen:
[postgresql] Probs met het terug leggen van de primary key

[jochemd]

Volgens mij is dit al een stukje schoner

code:

    --fix sum_blocks
    CREATE TEMP TABLE s_blocks as
        Select blockdate, blockhour, hostid, email, osid, cpuid, version, core, sum(amount) as amount
         from sum_blocks
         where hostid in (select hostid from hosts where hostip <<= in_new_ip)
         group by blockdate, blockhour, hostid, email, osid, cpuid, version, core;

    DELETE from sum_blocks where hostid IN (select hostid from hosts where hostip <<= in_new_ip);
    INSERT INTO sum_blocks (blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount)
        SELECT blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount from s_blocks;
    DROP TABLE s_blocks;
    DELETE from hosts
        where hostip <<= in_new_ip and hostid <> new_hostid;

Iemand een idee?

Caching misschien. Open je elke keer een nieuwe connectie? En hoeveel rijen zou je gaan aggregreren?

[MadMan81]

Maar zo aggregeer je niet: Het aantal record zo gelijk blijven, volgens bij bedoel jij dit:

code:

   --fix sum_blocks
    CREATE TEMP TABLE s_blocks AS
        SELECT blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount
         FROM sum_blocks
         WHERE hostid IN (select hostid from hosts where hostip <<= in_new_ip)

    DELETE FROM sum_blocks WHERE hostid IN (select hostid from hosts where hostip <<= in_new_ip);
    UPDATE s_blocks SET hostid=new_hostid;
    INSERT INTO sum_blocks (blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount)
      SELECT blockdate, blockhour, hostid, email, osid, cpuid, version, core, sum(amount) AS amount 
        FROM s_blocks 
        GROUP BY blockdate, blockhour, hostid, email, osid, cpuid, version, core;
    DROP TABLE s_blocks;
    DELETE from hosts
        WHERE hostip <<= in_new_ip AND hostid <> new_hostid;

[MadMan81]

Scriptje getest, en werkt prima! Dus probleem is nu opgelost, toch raar dat het andere het niet deed...

[jochemd]

MadMan81 schreef op 28 november 2003 @ 15:55:
Maar zo aggregeer je niet: Het aantal record zo gelijk blijven, volgens bij bedoel jij dit:

code:

   --fix sum_blocks
    CREATE TEMP TABLE s_blocks AS
        SELECT blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount
         FROM sum_blocks
         WHERE hostid IN (select hostid from hosts where hostip <<= in_new_ip)

    DELETE FROM sum_blocks WHERE hostid IN (select hostid from hosts where hostip <<= in_new_ip);
    UPDATE s_blocks SET hostid=new_hostid;
    INSERT INTO sum_blocks (blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount)
      SELECT blockdate, blockhour, hostid, email, osid, cpuid, version, core, sum(amount) AS amount 
        FROM s_blocks 
        GROUP BY blockdate, blockhour, hostid, email, osid, cpuid, version, core;
    DROP TABLE s_blocks;
    DELETE from hosts
        WHERE hostip <<= in_new_ip AND hostid <> new_hostid;

Niet helemaal, dan eerder:

code:

   --fix sum_blocks
    CREATE TEMP TABLE s_blocks AS
        SELECT blockdate, blockhour, new_hostid, email, osid, cpuid, version, core, amount
         FROM sum_blocks
         WHERE hostid IN (select hostid from hosts where hostip <<= in_new_ip)

    DELETE FROM sum_blocks WHERE hostid IN (select hostid from hosts where hostip <<= in_new_ip);
    INSERT INTO sum_blocks (blockdate, blockhour, hostid, email, osid, cpuid, version, core, amount)
      SELECT blockdate, blockhour, hostid, email, osid, cpuid, version, core, sum(amount) AS amount 
        FROM s_blocks 
        GROUP BY blockdate, blockhour, hostid, email, osid, cpuid, version, core;
    DROP TABLE s_blocks;
    DELETE from hosts
        WHERE hostip <<= in_new_ip AND hostid <> new_hostid;

Het moet volgens mij zonder die update kunnen.

[MadMan81]

Nee, dat zou niet werken: je groepeerd dan niet. Det resultaat zou het zelfde zijn als waarmee je was begonnen.

Het doel van de functie: Vervang allee oude hosts door een nieuwe (meer omvattender). Alleen kom je dan in de problemen met je PK, dus moet je agregeren..

Heb inmiddels wel een nieuw probleempje:

De volgende query onder mysql:

code:

select hostid, blockdate, max(amount) from sum_blocks group by hostid

Geeft je van iedere hostid het max(amount) en de blockdate van het record waarop die max(amount) gevonden was.
Echter postgresql eist dat je ook het blockdate-veld groepeerd. Dan krijg je dus iets heel anders. Hoe zou die query onder postgresql dan moeten?