Postgres Pro
Downloads
Home   >   mailing lists

Thread: can't CREATE TRIGGER

can't CREATE TRIGGER

From
gustavo halperin
Date:
Hello

I can't create triggers, when I call for example:
ficha=> CREATE TRIGGER TRG_persons_id AFTER INSERT ON ficha_ofperson
ficha-> EXECUTE PROCEDURE add_person_id ( 'family_id', 'person_id' );

, the creation never finish and finally, after many minutes, I kill the
creation with Ctrl+c.
I try also a simple trigger creation with a function without arguments
and also the creation never finish.
 The functions work OK, I can call theme without problems, see below:
ficha=> select add_person_id (1,7);

 I also add a permissions as postgres user's, see below:
ficha=# GRANT TRIGGER ON TABLE ficha_ofperson TO PUBLIC;
GRANT

  So.., any Idea what is my error ???

   Thank you in advance,
             Gustavo

--
                           ||\             // \
                           | \\           //   |
I'm thinking.               \  \\  l\\l_ //    |
    _              _         |  \\/ `/  `.|    |
  /~\\   \        //~\       | Y |   |   ||  Y |
  |  \\   \      //  |       |  \|   |   |\ /  |
  [   ||        ||   ]       \   |  o|o  | >  /
 ] Y  ||        ||  Y [       \___\_--_ /_/__/
 |  \_|l,------.l|_/  |       /.-\(____) /--.\
 |   >'          `<   |       `--(______)----'
 \  (/~`--____--'~\)  /           U// U / \
  `-_>-__________-<_-'            / \  / /|
      /(_#(__)#_)\               ( .) / / ]
      \___/__\___/                `.`' /   [
       /__`--'__\                  |`-'    |
    /\(__,>-~~ __)                 |       |__
 /\//\\(  `--~~ )                 _l       |--:.
 '\/  <^\      /^>               |  `   (  <   \\
      _\ >-__-< /_             ,-\  ,-~~->. \   `:.___,/He
     (___\    /___)           (____/    (____)    `---'

Re: can't CREATE TRIGGER

From
Stephan Szabo
Date:
On Mon, 22 Jan 2007, gustavo halperin wrote:

> I can't create triggers, when I call for example:
> ficha=> CREATE TRIGGER TRG_persons_id AFTER INSERT ON ficha_ofperson
> ficha-> EXECUTE PROCEDURE add_person_id ( 'family_id', 'person_id' );
>
> , the creation never finish and finally, after many minutes, I kill the
> creation with Ctrl+c.
> I try also a simple trigger creation with a function without arguments
> and also the creation never finish.

One possibility is that some transaction has a lock on the table which
would block the create trigger, do you have any long running transactions
or some kind of transaction pooling that might be keeping the table locked
for a long time? Can you create simple triggers on another table?

Re: can't CREATE TRIGGER

From
"Jaime Casanova"
Date:
On 1/21/07, gustavo halperin <ggh.develop@gmail.com> wrote:
> Hello
>
> I can't create triggers, when I call for example:
> ficha=> CREATE TRIGGER TRG_persons_id AFTER INSERT ON ficha_ofperson
> ficha-> EXECUTE PROCEDURE add_person_id ( 'family_id', 'person_id' );
>
> , the creation never finish and finally, after many minutes, I kill the
> creation with Ctrl+c.

perhaps the table is locked, view in pg_locks...

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
                                       Richard Cook

Re: can't CREATE TRIGGER

From
Michael Fuhr
Date:
On Mon, Jan 22, 2007 at 12:21:48AM +0200, gustavo halperin wrote:
> I can't create triggers, when I call for example:
> ficha=> CREATE TRIGGER TRG_persons_id AFTER INSERT ON ficha_ofperson
> ficha-> EXECUTE PROCEDURE add_person_id ( 'family_id', 'person_id' );
>
> , the creation never finish and finally, after many minutes, I kill the
> creation with Ctrl+c.

You probably have an open transaction with a lock on ficha_ofperson,
so the CREATE TRIGGER statement blocks until that lock is released.
Query pg_locks to see what transaction that is.  Make sure your
applications don't keep transactions open longer than necessary.

--
Michael Fuhr