datovy typ dle tabulky

[Michal Kozusznik]

Zajima me zda jsou nejake zname omezeni pouzivani datoveho typu zalozeneho na tabulce.

Ptam se proto stalo se nam ze po pridani noveho sloupce do tabulky, zacely padat s chybou INSERTy to teto tabulky - chyba ukazovala na trigger v ktere byla pouzita promenna.

Presne jednalo se o trigger INS/UPD/DEL a reseni jednotneho dostupu do udaju:


DECLARE
    _ticket tabulka_tickets;
BEGIN

    IF (TG_OP='INSERT') THEN
        _ticket = NEW;
    ELSEIF (TG_OP='UPDATE') THEN
        _ticket = NEW;
    ELSEIF (TG_OP='DELETE') THEN
        _ticket = OLD;
    END IF;

Dale byla pouzivana promenna _ticket, vcetne RETURN _ticket (typuju ze chyba vznikla prave u toho returnu.
Chybova hlaska bohuzel neukazala nap resne misto vyskytu:

ERROR:  returned row structure does not match the structure of the triggering table
2013-05-07T14:16:33+02:00 czdc-dbclr-03 czdc-PREMATCH[14154]: [10282-2] 2013-05-07 14:16:33 CEST sazky 10.254.254.146 saz DETAIL:  Number of returned columns (67) does not match expected column count (68).
2013-05-07T14:16:33+02:00 czdc-dbclr-03 czdc-PREMATCH[14154]: [10282-3] 2013-05-07 14:16:33 CEST sazky 10.254.254.146 saz CONTEXT:  PL/pgSQL function "tr_tabsaz_champs3" during function exit

Zajimave je ze jsme to zkouseli na testovaci DB a nepodarilo se nam to nasimulovat.

Existuji nejaka pravidla a omezeni s kteryma je treba pocitat kdyz se pouziva takove datove typy?

Dik

[Pavel Stehule]

Nedropovali jste sloupce na těch tabulkách? Vzpomínám si na jednu podobnou chybu.

Pavel

Dne 7. května 2013 15:42 Michal Kozusznik <maxym...@gmail.com> napsal(a):

--
Tuto zprávu jste obdrželi, protože jste přihlášeni k odběru skupiny PostgreSQL-cz ve Skupinách Google.
Pokud chcete zrušit odběr skupiny, aby vám z ní již nechodily e-maily, zašlete e-mail na adresu postgresql-c...@googlegroups.com.
Další možnosti najdete na adrese https://groups.google.com/groups/opt_out.
 
 

[Jiri Skopik/8BC Ltd.]

Stalo se mi nedávno totéž (verze 9.2.3). Akorát to nebyl trigger, ale normální funkce. Při spuštění bezprostředně po smazání slupce vracela tu stejnou chybu, ale nějak víc jsem to neřešil. Podle mě je to bug?

 

--

[Pavel Stehule]

Dne 7. května 2013 15:46 Jiri Skopik/8BC Ltd. <sko...@8bc.com> napsal(a):

Stalo se mi nedávno totéž (verze 9.2.3). Akorát to nebyl trigger, ale normální funkce. Při spuštění bezprostředně po smazání slupce vracela tu stejnou chybu, ale nějak víc jsem to neřešil. Podle mě je to bug?

 

juju je to bug - v postgresu se dropnutý sloupec jenom označkuje v systémových tabulkách - nicméně dále se s ním pracuje, jako kdyby existoval. Jen někdy se musí udělat korekce a ty dropnuté sloupce se musí ignorovat.

Můžete mi poslat příklady, na kterých by to bylo demonstrovatelné a info o verzích, které používáte?

Pavel

[Jiri Skopik/8BC Ltd.]

Zkouším to, ale umím to jenom na „velké“ databázi, na jednoduchém příkladu se mi to nedaří a nemůžu přijít na to proč…

[Pavel Stehule]

Dne 7. května 2013 16:41 Jiri Skopik/8BC Ltd. <sko...@8bc.com> napsal(a):

Zkouším to, ale umím to jenom na „velké“ databázi, na jednoduchém příkladu se mi to nedaří a nemůžu přijít na to proč…

s velikostí by to nemělo mít co do činění, ale i tak zkuste třeba napsat jednoduchý generátor pomocí generate_series.

[Jiri Skopik/8BC Ltd.]

Ne, s velikostí to nesouvisí, spíš jde o to, že ta funkce není triviální, ale volá několik dalších funkci (jedna z nich je navíc rekurzivní). Když si napíšu jednoduchou funkci s podobnou strukturou, ale bez těchto složitostí, funguje to správně.

Ještě to zkusím.

[Michal Kozusznik]

U mne funkce vypada takhle:

CREATE OR REPLACE FUNCTION client_champs.tr_tabsaz_champs3()
  RETURNS trigger AS
$BODY$
DECLARE
    _ticket tabsaz;
BEGIN

    -- let's manage existence of NEW and OLD objects

    IF (TG_OP='INSERT') THEN
        _ticket = NEW;
    ELSEIF (TG_OP='UPDATE') THEN
        _ticket = NEW;
    ELSEIF (TG_OP='DELETE') THEN
        _ticket = OLD;
   
    END IF;

    IF coalesce(_ticket.klient,0) = 0 THEN
        RETURN _ticket;
    END IF;

    IF (TG_OP='INSERT') THEN

        IF NEW.status=1 THEN
            PERFORM client_champs_app.process_queue_push(NEW, 1, 2);
        END IF;
        RETURN NEW;
    END IF;
   
    IF (TG_OP='DELETE') THEN
        IF coalesce(session_var_get('suppress_del_trigger'),'0')='1' THEN
            RETURN OLD;
        END IF;
       
        PERFORM client_champs_app.process_queue_push(OLD, -1, 1);
        PERFORM client_champs_app.process_queue_push(OLD, -1, 2);
        RETURN OLD;
    END IF;
   
    IF (TG_OP='UPDATE') THEN
        IF NEW.status IS DISTINCT FROM OLD.status THEN
           
            IF NEW.status = 1 AND coalesce(OLD.status,-1) < 0 THEN
                PERFORM client_champs_app.process_queue_push(NEW, 1, 2);
               
            ELSEIF NEW.status=4 THEN
                PERFORM client_champs_app.process_queue_push(NEW, 1, 1);

            ELSEIF OLD.status = 4 THEN
                PERFORM client_champs_app.process_queue_push(OLD, -1, 1);
               
            ELSEIF NEW.status = 3 THEN
                PERFORM client_champs_app.process_queue_push(NEW, -1, 2);

            ELSEIF OLD.status = 3 THEN
                PERFORM client_champs_app.process_queue_push(OLD, 1, 2);
            END IF;
           
        END IF;

        RETURN NEW;
    END IF;

EXCEPTION WHEN OTHERS THEN
    RAISE WARNING 'ERR_CHAMPPNTS: % [%] %', TG_OP, SQLSTATE, SQLERRM;
    RETURN _ticket;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

A chyba se vyskytla nejpravdepodobne na radku return _ticket;
Jak jsem ale drive napsal, nepodarilo se to nasimulovat v testovacim prostredi.
Zajimave take je, ze z dalsi funkce (process_queue_push), ktera take ocekava parametr typu tabsaz problem ne byl, a jeji pregenerovani nebylo nutne.

Pouzivame v8.4 v RH6

[Pavel Stehule]

Co mate presne za verzi - minor verzi?

pripadne muzete poslat strukturu te tabulky nad kterou se spousti provadi trigger?

Ja jsem tam nasel trochu jinou mouchu - ale na pg8.4.15 mi testovaci priklad bezel

create table f1(a int, b int, c varchar, dropped_column numeric, d varchar);

create or replace function f1_trg()
returns trigger as $$
declare _f1_var f1;
begin raise notice 'run trigger';
  _f1_var := new;
  return _f1_var;
end;
$$ language plpgsql;

create trigger xx before insert on f1 for row execute procedure f1_trg();

insert into f1 values(1,1,'aaa',1.1,'aaa');
alter table f1 drop column dropped_column ;

-- je nutne se odhlasit

-- po prihlaseni

insert into f1 values(1,1,'aaa','aaa');

-- melo by to zafungovat

Pavel

2013/5/7 Michal Kozusznik <maxym...@gmail.com>

--

[Michal Kozusznik]

Pozivame 8.4.13-1 (udajne posledni oficialni verze v distro RedHat6)

Podarilo se mi take situaci nasimulovat.
Pripravil jsem test case.

Provedeni:
1. nainstalovat schama michaltest, tabulku trigtable, trigger funkci, a trigger
2. v pgadmin sputit par inzertu (viz dole) aby si potvrdit ze funguji
3. Dale pridat sloupec do tabulky (dotaz viz dole). je to mozne provest ve stejnem okne pgadmin
4. pokusit se pridat dalsi radek

vysledek:

ERROR:  returned row structure does not match the structure of the triggering table

DETAIL:  Returned type character varying does not match expected type N/A (dropped column) in column "........pg.dropped.6........".
CONTEXT:  PL/pgSQL function "tr_trigtest" during function exit

********** Error **********

ERROR: returned row structure does not match the structure of the triggering table

SQL state: 42804
Detail: Returned type character varying does not match expected type N/A (dropped column) in column "........pg.dropped.6........".
Context: PL/pgSQL function "tr_trigtest" during function exit


Prekvapilo mne, ze resenim je zrusit pripojeni k DB a vytvorit nove (zavrit aktivni query okno pgadmina a otevrit nove).
Nechapu proc se to chova per session. Je to docela smutne :(


CREATE SCHEMA michaltest;

CREATE TABLE michaltest.trigtable
(
  id integer NOT NULL,
  field_a int,
  CONSTRAINT pk PRIMARY KEY (id)
);


CREATE OR REPLACE FUNCTION michaltest.tr_trigtest()

  RETURNS trigger AS
$BODY$
DECLARE

    _row michaltest.trigtable;

BEGIN

    -- let's manage existence of NEW and OLD objects
    IF (TG_OP='INSERT') THEN

        _row = NEW;

    ELSEIF (TG_OP='UPDATE') THEN

        _row = NEW;

    ELSEIF (TG_OP='DELETE') THEN

        _row = OLD;
    END IF;
   
    RETURN _row;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

CREATE TRIGGER tr_trigtest
AFTER INSERT OR UPDATE OR DELETE
ON michaltest.trigtable
FOR EACH ROW
EXECUTE PROCEDURE  michaltest.tr_trigtest()

-- priklad dotazu pro pridavani radku:
INSERT INTO michaltest.trigtable (id,field_a) VALUES (1,0);
INSERT INTO michaltest.trigtable (id,field_a) VALUES (2,0);
INSERT INTO michaltest.trigtable (id,field_a) VALUES (3,0);
INSERT INTO michaltest.trigtable (id,field_a) VALUES (4,0);
INSERT INTO michaltest.trigtable (id,field_a) VALUES (5,0);
INSERT INTO michaltest.trigtable (id,field_a) VALUES (6,0);
INSERT INTO michaltest.trigtable (id,field_a) VALUES (7,0);

-- priklad dotazy pro pridani/odebrani sloupce
ALTER TABLE michaltest.trigtable ADD COLUMN field_b VARCHAR;
ALTER TABLE michaltest.trigtable DROP  COLUMN field_b;

[Pavel Stehule]

Dne 9. května 2013 16:24 Michal Kozusznik <maxym...@gmail.com> napsal(a):

Pozivame 8.4.13-1 (udajne posledni oficialni verze v distro RedHat6)

Podarilo se mi take situaci nasimulovat.
Pripravil jsem test case.

Provedeni:
1. nainstalovat schama michaltest, tabulku trigtable, trigger funkci, a trigger
2. v pgadmin sputit par inzertu (viz dole) aby si potvrdit ze funguji
3. Dale pridat sloupec do tabulky (dotaz viz dole). je to mozne provest ve stejnem okne pgadmin
4. pokusit se pridat dalsi radek

vysledek:

ERROR:  returned row structure does not match the structure of the triggering table

DETAIL:  Returned type character varying does not match expected type N/A (dropped column) in column "........pg.dropped.6........".
CONTEXT:  PL/pgSQL function "tr_trigtest" during function exit

********** Error **********

jj tak to je podobny problem, ktery jsem nasimuloval - i kdyz asi jeste nekde jinde.

 

ERROR: returned row structure does not match the structure of the triggering table

SQL state: 42804
Detail: Returned type character varying does not match expected type N/A (dropped column) in column "........pg.dropped.6........".
Context: PL/pgSQL function "tr_trigtest" during function exit


Prekvapilo mne, ze resenim je zrusit pripojeni k DB a vytvorit nove (zavrit aktivni query okno pgadmina a otevrit nove).
Nechapu proc se to chova per session. Je to docela smutne :(

PL/pgSQL pouziva vlastni cache, ktere sice vetsinou duplikuji systemove cache, ale zaroven vetsinou o rad zrychluji pristup k informacim, ktere jsou ulozene v cache. Tyto lokalni cache vetsinou nemaji zadnou inteligenci - budto plati po dobu behu SQL dotazu nebo po dobu trvani session (po odhlaseni zanikaji). Jsou extremne rychle, jsou jednoduche ale nejsou sesynchronizovane se systemovou cache. V dobach, kdy to vymysleli se reseni typu odhlasit se, povazovalo za rozumne a dostatecne - staci zmeny struktur neprovadet za provozu.  Postupne se tyto zavislosti opravuji - ale je to docela pracne - i navic to vetsionou vyzaduje zasadni zmeny v kodu, takze se "opravy" tohoto typu nebackportuji.

pripadne - misto odhlaseni staci provest refresh vsech dotcenych funkci.

Nasel jsem pro Vas workaround

pokud pouzijete typ RECORD misto ROW typu -

tj

CREATE OR REPLACE FUNCTION michaltest.tr_trigtest()

  RETURNS trigger AS
$BODY$
DECLARE

    _row michaltest.trigtable; --- ZDE _row RECORD;

BEGIN

tak to funguje - a tak jak to pouzivate, by nemel byt ani rozdil v rychlosti.

Pavel

 

[Michal Kozusznik]

Diky za radu.
Predpokladam ale ze tento problem ne tyka se pouze trigger funkci. Co kdyz trigger zavola jinou funkci a preda ji data typu teto tabulky?

Co kdyz nekde v kodu mame
SELECT * INTO _row from tabulka
kde _row je typu tabulka?

Trochu mne to desi.

[Pavel Stehule]

Dne 10. května 2013 12:47 Michal Kozusznik <maxym...@gmail.com> napsal(a):

Diky za radu.
Predpokladam ale ze tento problem ne tyka se pouze trigger funkci. Co kdyz trigger zavola jinou funkci a preda ji data typu teto tabulky?

Ne tohle je obecnejsi problem ..

 

Co kdyz nekde v kodu mame
SELECT * INTO _row from tabulka
kde _row je typu tabulka?

Trochu mne to desi.

ano tohle muze nastat.

Nikdo kdysi nepredpokladal, ze by se ALTER TABLE delal bez vypadku. Snaha byla dobu vypadku minimalizovat, ten vypadek - na par vterin, tam ale je nutny.

Kdyz budu mit v lete cas, tak se na to zkusim podivat - jestli se to neda udelat nejak robustneji.

Pavel