Thread: BUG #2372: dblink_exec doesn't return. NEVER!

The following bug has been logged online:

Bug reference:      2372
Logged by:          William Leite Araújo
Email address:      william.bh@gmail.com
PostgreSQL version: 8.0.7
Operating system:   i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.3.5
(Debian 1:3.3.5-13)
Description:        dblink_exec doesn't return. NEVER!
Details:

A trigger start a dblink_exec local connection to insert on a table "A" as
SuperUser. Table "A" fires insert trigger that, by LOG messages, ends, but
the function never return to initial trigger.
   UPDATE TRIGGER T call dblink_exec to INSERT
     REMOTE INSERT fire TRIGGER that ends, but bdlink_exec doesn't return on
trigger.

"William Leite Araújo" <william.bh@gmail.com> writes:
> A trigger start a dblink_exec local connection to insert on a table "A" as
> SuperUser. Table "A" fires insert trigger that, by LOG messages, ends, but
> the function never return to initial trigger.

Perhaps the other transaction is blocked on some lock held by your
original transaction?

dblink connections to your own database are very seldom a good idea;
there is always a better, more efficient, less deadlock-prone way
to do it.  In this case I'd venture that you want to use a SECURITY
DEFINER function, and not dblink at all.

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> dblink connections to your own database are very seldom a good idea;
> there is always a better, more efficient, less deadlock-prone way
> to do it.  In this case I'd venture that you want to use a SECURITY
> DEFINER function, and not dblink at all.

The one case I've seen where people are trying to do this is where they
want to provide some kind of notification or log to a table to be
visible prior to the current transaction actually committing.  I can see
how this could be useful for long-running transactions or function calls
(It'd actually be useful in some of the work I've been doing, but we've
worked around it by providing NOTICE's when we're debugging and
splitting the long running function into multiple function calls when we
want more fine-grained information about where we're at in the process
for users to view).

    Thanks,

        Stephen

     I need this to give a credit from canceled billets.
     But anyone instead of roots can insert on this table.
     If it's done by a function, only roots must run it. So, the trigger will fail because is runned a no "su"...




--
William Leite Araújo

"William Leite Araújo" <william.bh@gmail.com> writes:
>      I need this to give a credit from canceled billets.
>      But anyone instead of roots can insert on this table.
>      If it's done by a function, only roots must run it. So, the trigger
> will fail because is runned a no "su"...

You need to read up on SECURITY DEFINER functions.
        regards, tom lane

On 4/3/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:

  Ok, I'll do this way, but still don't understand why it doesn't returns.
   I'm doing things simillar to this to create users automatically, and works fine...

--
William Leite Araújo

On Apr 5, 2006, at 7:28 AM, William Leite Ara=FAjo wrote:

> On 4/3/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> (...)
> You need to read up on SECURITY DEFINER functions.
>
>                         regards, tom lane
>
>
>   Ok, I'll do this way, but still don't understand why it doesn't=20=20
> returns.
>    I'm doing things simillar to this to create users automatically,=20=20
> and works fine...

As Tom mentioned, it's probably an issue of waiting on a lock that it=20=20
can't acquire. Just because it worked for creating users doesn't mean=20=20
it's a good idea. It's certainly less efficient than a SECURITY=20=20
DEFINER function, for starters...
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461