Re: [GENERAL] Problem with pl/python procedure connecting to the internet

[s d]

It might be a dumb thought, but...
Did you run the script from ipython as postgres user?
External programs called by triggers run as postgres, and maybe your
postgres user doesn't have rights to reach the remote service.

Regards,
Sandor



On 23 August 2015 at 09:09, Igor Sosa Mayor 
wrote:

> "Charles Clavadetscher"  writes:
>
> > Are you sure that you really use exactly the same code? The indication
> > "survice unknown" seems to point to an error in the URL and not to an
> > authorization problem. Maybe some encoding problem?
>
> THnaks for your hints.
>
> Yes, exactly the same code. Exactly the same query: two consoles, one
> with postgres, other with ipython. Postgres gives the error; ipython
> works.
>
> > The irritating thing is that you mention that it works sometimes and
> > sometimes not. Getting back to the encoding problem idea, is there a
> > difference if you call the function with a name containing special
> > characters (e.g. "é", "à", "ñ") or not?
>
> No special characters in the query. To be honest: it worked only 2-3
> times at work and I thought 'finally I managed to do it work'. But since
> then, it is not working anymore...
>
> In any case: I'm pretty sure it is a problem of postgres which is not
> able to make the connection. If I put a parameter timeout=15,
> postgres gives immediately the same error.
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [GENERAL] Problem with pl/python procedure connecting to the internet

[s d]

On 23 August 2015 at 18:59, Igor Sosa Mayor 
wrote:

> s d  writes:
>
> > It might be a dumb thought, but...
>
> thanks. There are not dumb thoughts with such problems...
>
> > Did you run the script from ipython as postgres user?
> > External programs called by triggers run as postgres, and maybe your
> > postgres user doesn't have rights to reach the remote service.
>
> i don't understand exactly (it is the 1. time I write a procedure even
> in PG). It is so:
> 1. I run ipython as my user (igor);
> 2. I run postgres both as user and as postgres. In both cases the same
>error. PG does not run ipython, because ipython is just a console for
>python. PG should only run python (2 or 3).
>
> IN any case, important is your last thought... I dont see in the doc of
> PG in the chapter of rules and privileges nothing about privileges to
> make internet connections...
>
>
Ok. You run IPython, as Igor, so your script runs in it as the user igor,
with igor's permissions.
When you run the same script from PG it runs as the OS user
postgres(regardless of which PG user fired it), with the permissions of the
OS user postgres.
And maybe, just maybe the postgres user can't get trough your firewall,
iptables rule, or something.
By the way: What OS do you use?
If it's possible try to log into your OS as postgres, start IPython and run
your script. If it fails we have a suspect.

Regards,
Sandor

Re: [GENERAL] Replacement for Oracle Text

[s d]

On 19 February 2016 at 14:19, Bruce Momjian  wrote:

> On Fri, Feb 19, 2016 at 11:53:26AM +, Simon Riggs wrote:
> > On 19 February 2016 at 11:46, Thomas Kellerer 
> wrote:
> >
> > Daniel Westermann schrieb am 19.02.2016 um 12:41:
> >  if I'd need to implement/replace Oracle Text (ww.oracle.com/
> > technetwork/testcontent/index-098492.html).
> > > What choices do I have in PostgreSQL (9.5+) ?
> > >
> > >>Postgres also has a full text search (which I find much easier to
> use
> > than Oracle's):
> > >>
> > >>http://www.postgresql.org/docs/current/static/textsearch.html
> > >
> > > Yes, i have seen this. Can this be used to index and search binary
> > documents, e.g. pdf ?
> >
> > Ah, no. That's not possible
> >
> >
> > ...not possible, Yet.
> >
> > PostgreSQL grows by adding the features people need and its changing
> rapidly.
>
> I wonder if PLPerl could be used to extract the words from a PDF
> document and create a tsvector column from it.
>
>
 I don't know about PLPerl(I'm pretty sure it could be used for this
purpose, though.).  On the other hand I've written code for this in Python
which should be easy to adapt for PLPython, if necessary.



Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com 
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [GENERAL] Update foreign table with trigger

[s d]

On 26 February 2016 at 20:02, Leonardo M. Ramé  wrote:

>
> El 26/02/16 a las 15:55, John R Pierce escribió:
>
>> On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:
>>
>>> Hi, I created a Postgres_FDW table (TABLE_A) and need to do an update on
>>> that table.
>>>
>>> As TABLE_A has a trigger, and the trigger does an insert on another
>>> table (TABLE_B), I had to create another foreign table called TABLE_B,
>>> that's ok.
>>>
>>
>>
>> that trigger is defined on the server that actually has table_a, right?
>>  or did you define a trigger on the FDW table ?
>>
>> Hi John, yes, the trigger is only defined on the foreign server.
>
>
Let's check we get this right!
You have two "real" table in the remote server with a trigger doing it's
job on them and on the local server you have and FDW on each remote table.
Right?

Regards,
Sándor


Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com

<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [GENERAL] Update foreign table with trigger

[s d]

On 26 February 2016 at 20:19, Leonardo M. Ramé  wrote:

>
>
> El 26/02/16 a las 16:18, s d escribió:
>
>>
>> On 26 February 2016 at 20:02, Leonardo M. Ramé > <mailto:l.r...@griensu.com>> wrote:
>>
>>
>> El 26/02/16 a las 15:55, John R Pierce escribió:
>>
>> On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:
>>
>> Hi, I created a Postgres_FDW table (TABLE_A) and need to do
>> an update on that table.
>>
>> As TABLE_A has a trigger, and the trigger does an insert on
>> another table (TABLE_B), I had to create another foreign
>> table called TABLE_B, that's ok.
>>
>>
>>
>> that trigger is defined on the server that actually has table_a,
>> right?   or did you define a trigger on the FDW table ?
>>
>> Hi John, yes, the trigger is only defined on the foreign server.
>>
>>
>> Let's check we get this right!
>> You have two "real" table in the remote server with a trigger doing it's
>> job on them and on the local server you have and FDW on each remote
>> table. Right?
>>
>>
> Yes, that's right.
>

Then try to do the update on the remote db directly.

In the meantime could you provide the table and trigger definitions?


Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [GENERAL] Update foreign table with trigger

[s d]

On 26 February 2016 at 20:42, Leonardo M. Ramé  wrote:

>
>
> El 26/02/16 a las 16:33, s d escribió:
>
>> On 26 February 2016 at 20:19, Leonardo M. Ramé > <mailto:l.r...@griensu.com>> wrote:
>>
>>
>>
>> El 26/02/16 a las 16:18, s d escribió:
>>
>>
>> On 26 February 2016 at 20:02, Leonardo M. Ramé
>> mailto:l.r...@griensu.com>
>> <mailto:l.r...@griensu.com <mailto:l.r...@griensu.com>>> wrote:
>>
>>
>>  El 26/02/16 a las 15:55, John R Pierce escribió:
>>
>>  On 2/26/2016 10:29 AM, Leonardo M. Ramé wrote:
>>
>>  Hi, I created a Postgres_FDW table (TABLE_A) and
>> need to do
>>  an update on that table.
>>
>>  As TABLE_A has a trigger, and the trigger does an
>> insert on
>>  another table (TABLE_B), I had to create another
>> foreign
>>  table called TABLE_B, that's ok.
>>
>>
>>
>>  that trigger is defined on the server that actually has
>> table_a,
>>  right?   or did you define a trigger on the FDW table ?
>>
>>  Hi John, yes, the trigger is only defined on the foreign
>> server.
>>
>>
>> Let's check we get this right!
>> You have two "real" table in the remote server with a trigger
>> doing it's
>> job on them and on the local server you have and FDW on each
>> remote
>> table. Right?
>>
>>
>> Yes, that's right.
>>
>>
>> Then try to do the update on the remote db directly.
>>
>> In the meantime could you provide the table and trigger definitions?
>>
>>
> I don't understand why the trigger is run in the caller database instead
> of the called (foreign) one.
>

It isn't. You get this error message because the reason why the local
command fails is in the remote trigger somewhere.

Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [GENERAL] Update foreign table with trigger

[s d]

On 26 February 2016 at 21:02, Leonardo M. Ramé  wrote:

>
>
> El 26/02/16 a las 16:49, s d escribió:
>
>> On 26 February 2016 at 20:42, Leonardo M. Ramé > <mailto:l.r...@griensu.com>> wrote:
>>
>>
>>
>>
>> Then try to do the update on the remote db directly.
>>
>> In the meantime could you provide the table and trigger
>> definitions?
>>
>>
>> I don't understand why the trigger is run in the caller database
>> instead of the called (foreign) one.
>>
>>
>> It isn't. You get this error message because the reason why the local
>> command fails is in the remote trigger somewhere.
>>
>>
> But, when I update TABLE_A from the remote server everything works ok.
>

I'm pretty sure we won't get further without seeing your defs.

Ezt az e-mailt egy Avast védelemmel rendelkező, vírusmentes számítógépről
küldték.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail>
<#DDB4FAA8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [GENERAL] PLPythonu for production server

[s d]

On 4 March 2016 at 10:46, Rémi Cura  wrote:

> Thanks for the answer guys.
>
> I should have mentionned that I had read the doc,
> and was looking for non explicit knowledge,
> like :
> - what is the reputation of plpython for a dba?
>

Dunno.

- are there actual production system that use it
>

I used it in an SMS processing system and it worked like a charm. That
system never made it to the production phase, though.


> - what would be the recommended usage perimeter ?
>   (only administration script like function, advanced processing, etc ...)
>

I would say, in general, use untrusted languages only if the trusted ones
are not capable or practical for your goals. (Reading/writing/processing
data outside of the database, using third party tools for processing, etc.)

Regards,
Sándor