Re: [GENERAL] PostgreSQL Developer Best Practices
Melvin Davidson schrieb am 22.08.2015 um 17:15: > I've attached a file with a few starters that although are numbered, > are in no special order. > 2. End ALL queries with a semi-colon (;) >EG: SELECT some_column FROM a_table; > >Although autocommit is on by default, it is always a good idea to signal > the query processor that a statement is complete with the semicolon. >Failure to do so could result in , which will >hold locks on the tables involved and prevent other queries from being > processed. Terminating a statement with ; has nothing to do with "" connections. It is a mere syntax thing to make the SQL client (e.g. psql) recognize the end of the statement. If you don't use it, your statement won't be executed in the first place - at least with psql as it will wait indefinitely until you finish typing the statement. A GUI client might simply send the wrong statement to the backend. If you run with autocommit disabled, ending each statement with a semicolon, will not prevent your connection from getting into that "" state. You have to end the _transaction_ using commit or rollback to avoid that. I do agree with the "end all queries with a semi-colon" rule, but the explanation is wrong. You should have another rule that says: End all transactions as soon as possible using commit or rollback. Thomas -- 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] PostgreSQL Developer Best Practices
Melvin Davidson schrieb am 22.08.2015 um 21:40: > Thank you for pointing out "run with standard_conforming_strings = ON".. > However, that is NOT the problem. > What is occurring is that the developers are sending strings like 'Mr. > M\'vey', > which, if we set standard_conforming_strings = ON, would, and does, result in > errors and the statement failing, > which is a lot less desirable that a simple warning. > > Therefore, I am trying to educate the developers in the proper method of > escaping strings, > instead of loading up the error log with annoying warnings. I strongly disagree: the error is not "annoying" and the statement _should_ fail. The only way you can make the developers stop using that non-standard syntax is to make the satement fail. Thomas -- 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] fastes way to retrieve segmented without using loop
Sorry for the confusion. I will later find a better way to present the
issue.
But thanks for everyone's help.
On Sun, Aug 23, 2015 at 6:20 PM, Melvin Davidson
wrote:
> Yes, I agree. I was a bit confused by the term "time" column. Not mention,
> the O/S and PG version were not given.
> If column t is truly type time, then only 24 rows can be returned
> regardless of limit, as in this Universe, there are only 24 hrs in time.
> However, if t is a timestamp, that is a whole other animal and the
> DISTINCT would have to be adjusted to include date & hour.
> Perhaps if we were given a more accurate table structure, a more exact
> solution could be provided.
>
> On Sun, Aug 23, 2015 at 6:09 PM, John McKown > wrote:
>
>> Melvin's use of DISTINCT ON (...) is superior to my use of DISTINCT(...)
>> because it doesn't return the value to your program. I keep forgetting this
>> way. I learned it the other way. Old dog + new trick == problem.
>>
>> On Sun, Aug 23, 2015 at 5:04 PM, John McKown <
>> john.archie.mck...@gmail.com> wrote:
>>
>>> On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith
>>> wrote:
>>>
Hello:
I have a time series table,
using below sql, loop (psque code), I can get one row for each hour
>>>
>>> s/psque/pseudo/g;
>>>
>>>
>>>
for( H=1: H< 9; H++){
select * from table where t >= H and t < H+1 limit 1
}
t (time column) is indexed).
Is there a better way to use a issue a SINGLE SQL
with an array of time start/end pair
so it will be executed once to send back result, which would be much
faster
than issuing sql again and again (even with prepared statement and
using sql function).
Thanks in Advance
>>>
>>> Well, I have a bit of a problem if "t" is a "time column". Do you mean a
>>> "time interval"? Or do you really mean it is an integer of some sort. I ask
>>> because H sure looks like a plain old integer to me.
>>>
>>> In any case, if "t" is an "int" as opposed to a "time interval", then
>>> you could start with something like:
>>>
>>> SELECT DISTINCT(t), columns FROM table WHERE t BETWEEN 1 AND
>>> 9;
>>>
>>> But if "t" really is a "time interval" in the PostgreSQL sense, and H is
>>> like the "hour" portion (H --> Hour, makes sense to this weirdo). And you
>>> want to select one row of data where the "t" interval is 1 hour, another
>>> where the "t" interval is 2 hours, another where the "t" interval is 3
>>> hours, and so on up to an interval of at most 99_999 hours. Then you might
>>> need something like:
>>>
>>> SELECT DISTINCT(EXTRACT(HOUR FROM t)) AS interval_truncated_to_hour, *
>>> FROM table WHERE t BETWEEN 1 AND 9;
>>>
>>> I don't know of a way to eliminate the first field from the result. But,
>>> in reality, I would _never_ issue a SELECT * in a "normal" program. Only
>>> ask for the columns you are actually going to need. Because, someday,
>>> someone, is going to change the schema on the table and your program is
>>> (im)pure porcine excrement at that point. With no errors returned to it.
>>> IMO, it is an unshakable rule to ONLY and ALWAYS specify the variable
>>> names. The only exception is if your program actually examines the schema
>>> of the table before doing a SELECT and dynamically constructs it.
>>>
>>>
>>>
>>>
>>>
>>>
>>> --
>>>
>>> Schrodinger's backup: The condition of any backup is unknown until a
>>> restore is attempted.
>>>
>>> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will
>>> be.
>>>
>>> He's about as useful as a wax frying pan.
>>>
>>> 10 to the 12th power microphones = 1 Megaphone
>>>
>>> Maranatha! <><
>>> John McKown
>>>
>>
>>
>>
>> --
>>
>> Schrodinger's backup: The condition of any backup is unknown until a
>> restore is attempted.
>>
>> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will
>> be.
>>
>> He's about as useful as a wax frying pan.
>>
>> 10 to the 12th power microphones = 1 Megaphone
>>
>> Maranatha! <><
>> John McKown
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
Re: [GENERAL] Problem with pl/python procedure connecting to the internet
On 08/23/2015 01:15 PM, Igor Sosa Mayor wrote: Igor Sosa Mayor writes: My question is therefore: 1. is there a way to permit the pl/python to connect to the internet all the time and with a better configuration? 2. or should I forget the procedure and write a python script outside the database? I want to update the situation in this email following up my own email. Sorry for not answering every question (in any case: I don't have SElinux or similar). The situation is very strange. It works now perfectly BUT ONLY after restarting PG. I'm playing with the log options in PG and I don't see in the logs nothing strange (PG starts without problems). As I understand it you are running everything on a laptop. Are you saying the laptop has been running non-stop the three days this discussion has been going on and Postgres was never shut down till now? But I see that the log options in PG are really rich. Could maybe someone tell me which could be the best options to find the problem? I will be offline now during 24h, but I will try to make some experiments in the meantime. From here: http://www.postgresql.org/docs/9.4/interactive/runtime-config-logging.html#RUNTIME-CONFIG-LOGGING-WHAT I would at least log: log_connections log_disconnections log_statement 'mod' log_line_prefix %u %m %p So does looking back in the logs, either Postgres or system, show anything? Thanks again for your help. -- Adrian Klaver adrian.kla...@aklaver.com -- 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] fastes way to retrieve segmented without using loop
Yes, I agree. I was a bit confused by the term "time" column. Not mention,
the O/S and PG version were not given.
If column t is truly type time, then only 24 rows can be returned
regardless of limit, as in this Universe, there are only 24 hrs in time.
However, if t is a timestamp, that is a whole other animal and the DISTINCT
would have to be adjusted to include date & hour.
Perhaps if we were given a more accurate table structure, a more exact
solution could be provided.
On Sun, Aug 23, 2015 at 6:09 PM, John McKown
wrote:
> Melvin's use of DISTINCT ON (...) is superior to my use of DISTINCT(...)
> because it doesn't return the value to your program. I keep forgetting this
> way. I learned it the other way. Old dog + new trick == problem.
>
> On Sun, Aug 23, 2015 at 5:04 PM, John McKown > wrote:
>
>> On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith
>> wrote:
>>
>>> Hello:
>>>
>>> I have a time series table,
>>> using below sql, loop (psque code), I can get one row for each hour
>>>
>>
>> s/psque/pseudo/g;
>>
>>
>>
>>>
>>> for( H=1: H< 9; H++){
>>>select * from table where t >= H and t < H+1 limit 1
>>> }
>>>
>>> t (time column) is indexed).
>>>
>>> Is there a better way to use a issue a SINGLE SQL
>>> with an array of time start/end pair
>>> so it will be executed once to send back result, which would be much
>>> faster
>>> than issuing sql again and again (even with prepared statement and using
>>> sql function).
>>>
>>> Thanks in Advance
>>>
>>
>> Well, I have a bit of a problem if "t" is a "time column". Do you mean a
>> "time interval"? Or do you really mean it is an integer of some sort. I ask
>> because H sure looks like a plain old integer to me.
>>
>> In any case, if "t" is an "int" as opposed to a "time interval", then you
>> could start with something like:
>>
>> SELECT DISTINCT(t), columns FROM table WHERE t BETWEEN 1 AND
>> 9;
>>
>> But if "t" really is a "time interval" in the PostgreSQL sense, and H is
>> like the "hour" portion (H --> Hour, makes sense to this weirdo). And you
>> want to select one row of data where the "t" interval is 1 hour, another
>> where the "t" interval is 2 hours, another where the "t" interval is 3
>> hours, and so on up to an interval of at most 99_999 hours. Then you might
>> need something like:
>>
>> SELECT DISTINCT(EXTRACT(HOUR FROM t)) AS interval_truncated_to_hour, *
>> FROM table WHERE t BETWEEN 1 AND 9;
>>
>> I don't know of a way to eliminate the first field from the result. But,
>> in reality, I would _never_ issue a SELECT * in a "normal" program. Only
>> ask for the columns you are actually going to need. Because, someday,
>> someone, is going to change the schema on the table and your program is
>> (im)pure porcine excrement at that point. With no errors returned to it.
>> IMO, it is an unshakable rule to ONLY and ALWAYS specify the variable
>> names. The only exception is if your program actually examines the schema
>> of the table before doing a SELECT and dynamically constructs it.
>>
>>
>>
>>
>>
>>
>> --
>>
>> Schrodinger's backup: The condition of any backup is unknown until a
>> restore is attempted.
>>
>> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will
>> be.
>>
>> He's about as useful as a wax frying pan.
>>
>> 10 to the 12th power microphones = 1 Megaphone
>>
>> Maranatha! <><
>> John McKown
>>
>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Re: [GENERAL] fastes way to retrieve segmented without using loop
Melvin's use of DISTINCT ON (...) is superior to my use of DISTINCT(...)
because it doesn't return the value to your program. I keep forgetting this
way. I learned it the other way. Old dog + new trick == problem.
On Sun, Aug 23, 2015 at 5:04 PM, John McKown
wrote:
> On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith
> wrote:
>
>> Hello:
>>
>> I have a time series table,
>> using below sql, loop (psque code), I can get one row for each hour
>>
>
> s/psque/pseudo/g;
>
>
>
>>
>> for( H=1: H< 9; H++){
>>select * from table where t >= H and t < H+1 limit 1
>> }
>>
>> t (time column) is indexed).
>>
>> Is there a better way to use a issue a SINGLE SQL
>> with an array of time start/end pair
>> so it will be executed once to send back result, which would be much
>> faster
>> than issuing sql again and again (even with prepared statement and using
>> sql function).
>>
>> Thanks in Advance
>>
>
> Well, I have a bit of a problem if "t" is a "time column". Do you mean a
> "time interval"? Or do you really mean it is an integer of some sort. I ask
> because H sure looks like a plain old integer to me.
>
> In any case, if "t" is an "int" as opposed to a "time interval", then you
> could start with something like:
>
> SELECT DISTINCT(t), columns FROM table WHERE t BETWEEN 1 AND
> 9;
>
> But if "t" really is a "time interval" in the PostgreSQL sense, and H is
> like the "hour" portion (H --> Hour, makes sense to this weirdo). And you
> want to select one row of data where the "t" interval is 1 hour, another
> where the "t" interval is 2 hours, another where the "t" interval is 3
> hours, and so on up to an interval of at most 99_999 hours. Then you might
> need something like:
>
> SELECT DISTINCT(EXTRACT(HOUR FROM t)) AS interval_truncated_to_hour, *
> FROM table WHERE t BETWEEN 1 AND 9;
>
> I don't know of a way to eliminate the first field from the result. But,
> in reality, I would _never_ issue a SELECT * in a "normal" program. Only
> ask for the columns you are actually going to need. Because, someday,
> someone, is going to change the schema on the table and your program is
> (im)pure porcine excrement at that point. With no errors returned to it.
> IMO, it is an unshakable rule to ONLY and ALWAYS specify the variable
> names. The only exception is if your program actually examines the schema
> of the table before doing a SELECT and dynamically constructs it.
>
>
>
>
>
>
> --
>
> Schrodinger's backup: The condition of any backup is unknown until a
> restore is attempted.
>
> Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
>
> He's about as useful as a wax frying pan.
>
> 10 to the 12th power microphones = 1 Megaphone
>
> Maranatha! <><
> John McKown
>
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
Re: [GENERAL] fastes way to retrieve segmented without using loop
On Sun, Aug 23, 2015 at 4:05 PM, Tom Smith wrote:
> Hello:
>
> I have a time series table,
> using below sql, loop (psque code), I can get one row for each hour
>
s/psque/pseudo/g;
>
> for( H=1: H< 9; H++){
>select * from table where t >= H and t < H+1 limit 1
> }
>
> t (time column) is indexed).
>
> Is there a better way to use a issue a SINGLE SQL
> with an array of time start/end pair
> so it will be executed once to send back result, which would be much faster
> than issuing sql again and again (even with prepared statement and using
> sql function).
>
> Thanks in Advance
>
Well, I have a bit of a problem if "t" is a "time column". Do you mean a
"time interval"? Or do you really mean it is an integer of some sort. I ask
because H sure looks like a plain old integer to me.
In any case, if "t" is an "int" as opposed to a "time interval", then you
could start with something like:
SELECT DISTINCT(t), columns FROM table WHERE t BETWEEN 1 AND
9;
But if "t" really is a "time interval" in the PostgreSQL sense, and H is
like the "hour" portion (H --> Hour, makes sense to this weirdo). And you
want to select one row of data where the "t" interval is 1 hour, another
where the "t" interval is 2 hours, another where the "t" interval is 3
hours, and so on up to an interval of at most 99_999 hours. Then you might
need something like:
SELECT DISTINCT(EXTRACT(HOUR FROM t)) AS interval_truncated_to_hour, * FROM
table WHERE t BETWEEN 1 AND 9;
I don't know of a way to eliminate the first field from the result. But, in
reality, I would _never_ issue a SELECT * in a "normal" program. Only ask
for the columns you are actually going to need. Because, someday, someone,
is going to change the schema on the table and your program is (im)pure
porcine excrement at that point. With no errors returned to it. IMO, it is
an unshakable rule to ONLY and ALWAYS specify the variable names. The only
exception is if your program actually examines the schema of the table
before doing a SELECT and dynamically constructs it.
--
Schrodinger's backup: The condition of any backup is unknown until a
restore is attempted.
Yoda of Borg, we are. Futile, resistance is, yes. Assimilated, you will be.
He's about as useful as a wax frying pan.
10 to the 12th power microphones = 1 Megaphone
Maranatha! <><
John McKown
Re: [GENERAL] fastes way to retrieve segmented without using loop
Correction:
SELECT DISTINCT ON EXTRACT(HOUR FROM you_time_column)
*
FROM table
WHERE t >=1
LIMIT 8;
On Sun, Aug 23, 2015 at 5:38 PM, Melvin Davidson
wrote:
> Well, I would say the following might work:
>
> SELECT *
> FROM table
> WHERE t >=1
> LIMIT 8;
>
> On Sun, Aug 23, 2015 at 5:05 PM, Tom Smith
> wrote:
>
>> Hello:
>>
>> I have a time series table,
>> using below sql, loop (psque code), I can get one row for each hour
>>
>> for( H=1: H< 9; H++){
>>select * from table where t >= H and t < H+1 limit 1
>> }
>>
>> t (time column) is indexed).
>>
>> Is there a better way to use a issue a SINGLE SQL
>> with an array of time start/end pair
>> so it will be executed once to send back result, which would be much
>> faster
>> than issuing sql again and again (even with prepared statement and using
>> sql function).
>>
>> Thanks in Advance
>>
>
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize. Whether or not you
> wish to share my fantasy is entirely up to you.
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Re: [GENERAL] fastes way to retrieve segmented without using loop
Well, I would say the following might work:
SELECT *
FROM table
WHERE t >=1
LIMIT 8;
On Sun, Aug 23, 2015 at 5:05 PM, Tom Smith wrote:
> Hello:
>
> I have a time series table,
> using below sql, loop (psque code), I can get one row for each hour
>
> for( H=1: H< 9; H++){
>select * from table where t >= H and t < H+1 limit 1
> }
>
> t (time column) is indexed).
>
> Is there a better way to use a issue a SINGLE SQL
> with an array of time start/end pair
> so it will be executed once to send back result, which would be much faster
> than issuing sql again and again (even with prepared statement and using
> sql function).
>
> Thanks in Advance
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
[GENERAL] fastes way to retrieve segmented without using loop
Hello:
I have a time series table,
using below sql, loop (psque code), I can get one row for each hour
for( H=1: H< 9; H++){
select * from table where t >= H and t < H+1 limit 1
}
t (time column) is indexed).
Is there a better way to use a issue a SINGLE SQL
with an array of time start/end pair
so it will be executed once to send back result, which would be much faster
than issuing sql again and again (even with prepared statement and using
sql function).
Thanks in Advance
Re: [GENERAL] Problem with pl/python procedure connecting to the internet
Igor Sosa Mayor writes: > My question is therefore: > 1. is there a way to permit the pl/python to connect to the internet all >the time and with a better configuration? > 2. or should I forget the procedure and write a python script outside >the database? I want to update the situation in this email following up my own email. Sorry for not answering every question (in any case: I don't have SElinux or similar). The situation is very strange. It works now perfectly BUT ONLY after restarting PG. I'm playing with the log options in PG and I don't see in the logs nothing strange (PG starts without problems). But I see that the log options in PG are really rich. Could maybe someone tell me which could be the best options to find the problem? I will be offline now during 24h, but I will try to make some experiments in the meantime. Thanks again for your help. -- :: Igor Sosa Mayor :: joseleopoldo1...@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :::: -- 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
In cases like this I normally restart the progresql under strace/truss etc and then wade through the output, it will normally tell me which process was invoked. On 23/08/15 18:49, Tom Lane wrote: > Igor Sosa Mayor writes: >> Adrian Klaver writes: >>> Or more to the point how where they built? >> just installed with postgresql which comes with archlinux. I did not >> built anything. But, really: I dont think the problem is plpython2u o 3: >> 1. I have in my system both and both have the same libraries (in this >>case, geopy); >> 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the >>internet work perfectly. > Well, that hardly proves that Python code that *does* connect to the net > would work. The possibility that you're using a different Python version > inside Postgres and it's broken for network access is one you should take > very seriously. > > However, what this smells like to me is a permissions problem. I think > you were way too quick to dismiss the idea that SELinux (or something > just like it) is restricting outbound internet connections from Postgres. > It's standard for SELinux to be configured so that network-accessible > daemons like Postgres are locked down harder than the very same code > would be treated when being invoked from the command line --- and network > access would be one of the prime candidates to be disabled by default. > > Have you poked around under /var/log/ to see if the kernel logs anything > when the connection attempt doesn't work? For that matter, have you > checked the postmaster log to see what Postgres logs about it? > > regards, tom lane > > -- 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
On 8/23/2015 10:49 AM, Tom Lane wrote: However, what this smells like to me is a permissions problem. I think you were way too quick to dismiss the idea that SELinux (or something just like it) is restricting outbound internet connections from Postgres. It's standard for SELinux to be configured so that network-accessible daemons like Postgres are locked down harder than the very same code would be treated when being invoked from the command line --- and network access would be one of the prime candidates to be disabled by default. Have you poked around under/var/log/ to see if the kernel logs anything when the connection attempt doesn't work? For that matter, have you checked the postmaster log to see what Postgres logs about it? also, `getenforce` ... if it comes back enabled, as root, do... setenforce permissive and try your trigger again (don't reboot or restart anything, just do it) -- john r pierce, recycling bits in santa cruz
Re: [GENERAL] Problem with pl/python procedure connecting to the internet
Igor Sosa Mayor writes: > Adrian Klaver writes: >> Or more to the point how where they built? > just installed with postgresql which comes with archlinux. I did not > built anything. But, really: I dont think the problem is plpython2u o 3: > 1. I have in my system both and both have the same libraries (in this >case, geopy); > 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the >internet work perfectly. Well, that hardly proves that Python code that *does* connect to the net would work. The possibility that you're using a different Python version inside Postgres and it's broken for network access is one you should take very seriously. However, what this smells like to me is a permissions problem. I think you were way too quick to dismiss the idea that SELinux (or something just like it) is restricting outbound internet connections from Postgres. It's standard for SELinux to be configured so that network-accessible daemons like Postgres are locked down harder than the very same code would be treated when being invoked from the command line --- and network access would be one of the prime candidates to be disabled by default. Have you poked around under /var/log/ to see if the kernel logs anything when the connection attempt doesn't work? For that matter, have you checked the postmaster log to see what Postgres logs about it? regards, tom lane -- 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
On 08/23/2015 10:38 AM, Igor Sosa Mayor wrote: Adrian Klaver writes: I tried both... in my system (archlinux) there is plpython2u and plpythonu. I think the 2nd one is python3. How did they get there? Or more to the point how where they built? just installed with postgresql which comes with archlinux. I did not built anything. But, really: I dont think the problem is plpython2u o 3: 1. I have in my system both and both have the same libraries (in this case, geopy); 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the internet work perfectly. The problem is the connection, either the setup in my laptop or the setup in my network (but I get the same problem at home and at work...) You are using the same machine, the laptop, in both locations, correct? If so then the problem is some sort of access control issue. When you try an INSERT, what shows in?: 1) The Postgres logs. 2) The system log. What happens if you change your geocodificar to use plpythonu? exactly the same. I tested all combinations... (thanks again!) -- Adrian Klaver adrian.kla...@aklaver.com -- 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
Adrian Klaver writes: > What is the default system Python? python3. But see my other answer. I dont think this is the problem. -- 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
Adrian Klaver writes: >> I tried both... in my system (archlinux) there is plpython2u and >> plpythonu. I think the 2nd one is python3. > > How did they get there? > > Or more to the point how where they built? just installed with postgresql which comes with archlinux. I did not built anything. But, really: I dont think the problem is plpython2u o 3: 1. I have in my system both and both have the same libraries (in this case, geopy); 2. other procedures with plpython (2 or 3) which DO NOT CONNECT to the internet work perfectly. The problem is the connection, either the setup in my laptop or the setup in my network (but I get the same problem at home and at work...) > What happens if you change your geocodificar to use plpythonu? exactly the same. I tested all combinations... (thanks again!) -- 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
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] Problem with pl/python procedure connecting to the internet
On 08/23/2015 10:02 AM, Igor Sosa Mayor wrote: Adrian Klaver writes: I tried it here, using your functions, and could not get it to fail: well... i'm happy my function is working... my 1. procedure in PG... Now maybe I'm able to get it working in my laptop... So it is something to do with the setup on your laptop. yes, or the network I'm in... may I ask if you are running the fucntion in a server? It is running in the Postgres server, if that is what you are asking? The machine it is running on is my home desktop unit, so nothing special. -- Adrian Klaver adrian.kla...@aklaver.com -- 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
On 08/23/2015 09:53 AM, Igor Sosa Mayor wrote: Adrian Klaver writes: What happens if you run the function below directly in psql and not through the trigger? exactly the same error. Do you have both Python 2 and Python 3 on this machine? I have both. Did you create both the 2u and 3u variants of plpythonu? I tried both... in my system (archlinux) there is plpython2u and plpythonu. I think the 2nd one is python3. Meant to add: What is the default system Python? -- Adrian Klaver adrian.kla...@aklaver.com -- 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
On 08/23/2015 09:53 AM, Igor Sosa Mayor wrote: Adrian Klaver writes: What happens if you run the function below directly in psql and not through the trigger? exactly the same error. Do you have both Python 2 and Python 3 on this machine? I have both. Did you create both the 2u and 3u variants of plpythonu? I tried both... in my system (archlinux) there is plpython2u and plpythonu. I think the 2nd one is python3. How did they get there? Or more to the point how where they built? What happens if you change your geocodificar to use plpythonu? -- Adrian Klaver adrian.kla...@aklaver.com -- 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
Adrian Klaver writes: > I tried it here, using your functions, and could not get it to fail: well... i'm happy my function is working... my 1. procedure in PG... Now maybe I'm able to get it working in my laptop... > So it is something to do with the setup on your laptop. yes, or the network I'm in... may I ask if you are running the fucntion in a server? -- 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 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... -- :: Igor Sosa Mayor :: joseleopoldo1...@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :::: -- 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
Adrian Klaver writes: > What happens if you run the function below directly in psql and not > through the trigger? exactly the same error. > Do you have both Python 2 and Python 3 on this machine? I have both. > Did you create both the 2u and 3u variants of plpythonu? I tried both... in my system (archlinux) there is plpython2u and plpythonu. I think the 2nd one is python3. -- 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
"Charles Clavadetscher" writes: >> Yes, exactly the same code. Exactly the same query: two consoles, one with >> postgres, other with ipython. Postgres gives the error; ipython works. > > Somehow I expected that answer. I am not a python expert, but it happens > that different pieces of code at the end do not use the same libraries or > framework code by configuration. But if you are positive on that, no more > discussion. Well, I put the same code... what exactly python does is another question... > Sorry for asking obvious things, but it happened to me to oversee some of > them in the past: Are you making the calls from the same computer? obvious things are often the most important ones. But: yes, this is all the time my laptop. And I did not change the config of postgres since I put the configurations I mentioned in my first email. > So it looks like a timeout problem with a confusing error message. > A possible way to follow this up would be to call another webservice from a > python function in PG and see if it behaves the same. I had the same idea... but a small script querying the IMDB database works perfectly in the console, but gives an error in PG. THe error is not so obvious like in the geocoder case, but in any case the movie data are not loaded. > Another way to analyze the problem could be to sniff the network traffic and > see what really happens when you make the function call. This could help at > least to discard network problems and may lead to some useful > information. GOod idea. I see wireshark changed a lot since the last time I used it... if I'm using it the right way, the thing is pointing in the direction I thought: there is NO CONNECTION from PG to the internet when I use the function. I dont see any packages. From the python console there is a connection to nominatim (openstreetmap). I think there is exactly the problem: PG does not make any attempt (or it does but it fails even before any package is being sent) to the internet. THnaks for your ideas and help. -- :: Igor Sosa Mayor :: joseleopoldo1...@gmail.com :: :: GnuPG: 0x1C1E2890 :: http://www.gnupg.org/ :: :: jabberid: rogorido :::: -- 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
On 08/23/2015 12:09 AM, 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.
I tried it here, using your functions, and could not get it to fail:
Running as postgres user.
test=# select version();
version
-
PostgreSQL 9.4.2 on i686-pc-linux-gnu, compiled by gcc (SUSE Linux)
4.8.1 20130909 [gcc-4_8-branch revision 202388], 32-bit
(1 row)
Python 2. I did change the plpython2u to plpythonu in geocodificar as I
have only the one version of plpythonu installed.
test=# create table lat_long(ciudad text, pais text, latitute numeric
not null, longitude numeric not null);
create trigger lat_lon before insert or update on lat_long for each row
execute procedure anadirgeocoord();
test=# insert into lat_long (ciudad, pais) values ('Bellingham', 'USA');
INSERT 0 1
test=# insert into lat_long (ciudad, pais) values ('Seattle', 'USA');
INSERT 0 1
test=# insert into lat_long (ciudad, pais) values ('London', 'UK');
INSERT 0 1
test=# insert into lat_long (ciudad, pais) values ('Perth', 'AU');
INSERT 0 1
test=# select * from lat_long ;
ciudad | pais | latitute | longitude
+--+-+--
Bellingham | USA | 48.754402 | -122.4788602
Seattle| USA | 47.6038321 | -122.3300624
London | UK | 51.5073219 | -0.1276474
Perth | AU | -31.9527121 | 115.8604796
(4 rows)
test=# update lat_long set pais= 'UK' where ciudad ='Bellingham';
UPDATE 1
test=# select * from lat_long ;
ciudad | pais | latitute | longitude
+--+-+--
Seattle| USA | 47.6038321 | -122.3300624
London | UK | 51.5073219 | -0.1276474
Perth | AU | -31.9527121 | 115.8604796
Bellingham | UK | 55.1443709 | -2.2549272
(4 rows)
So it is something to do with the setup on your laptop.
--
Adrian Klaver
adrian.kla...@aklaver.com
--
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] 9.5 beta pg_upgrade documentation
I think we should add a step 6.5 (before step 7 Stop both servers) with something like: If you are upgrading both a primary and standby, then we need to make sure the standby is caught up. If you are wal shipping then on primary run: select pg_switch_xlog(); shut down primary before you shut down the standby make sure it gets caught up to the primary. I don't think its 100% required for them to be exact, is it? If they are a little different then rsync has more data to xfer from primary to standby ... but it would still work. Right? -Andy -- 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
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
On 08/23/2015 12:09 AM, 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. What happens if you run the function below directly in psql and not through the trigger? CREATE OR REPLACE FUNCTION geocodificar(direccion text) returns coordenadas AS $$ from geopy.geocoders import Nominatim geoloc = Nominatim() location = geoloc.geocode(direccion) return(location.latitude, location.longitude) $$ LANGUAGE 'plpython2u'; Hmm, just noticed plpython2u. Do you have both Python 2 and Python 3 on this machine? Did you create both the 2u and 3u variants of plpythonu? -- Adrian Klaver adrian.kla...@aklaver.com -- 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] master/master replication with load balancer in front
El 21/08/15 a las 20:45, Florin Andrei escribió: > > The single instance scheme is not very reliable. I need to build a new > DB backend. I'll set up Postgres 9.4. Ideally, I'd like to setup 2 > instances, each instance placed in a different availability zone. > Master/master replication. I'll put a load balancer (ELB) in front of > both instances. It's not clear if the main goal is reliability (or availability), or to balance writes. If you are looking for HA, single master with multiple standbys is your best bet (you can put standbys on different zones). You can also look at BDR and have masters geographically distributed, but I'd strongly suggest you look at the link http://bdr-project.org/docs/stable/weak-coupled-multimaster.html, and keep in mind that with multi-master systems you will be more prone to data modification conflicts. You also have to be aware that BDR tries to keeps the schemas consistent across all nodes. This is done by replicating DDL statements (not all, you'll need to check for the not replicated statements and the prohibited ones here http://bdr-project.org/docs/stable/ddl-replication-statements.html) which can bring up locking issues. If you want to balance writes, I don't think a multi-master clustering will fit your needs. If this is the case your needs would be better satisfied with sharding solutions. > The batch updates and the queries will be sent by the ELB to any > instance in the cluster; replication will take care of copying the data > to all instances. I want the whole cluster + the load balancer to act as > a single instance to everyone connecting to it. > > "Eventually consistent" replication is fine. I don't want to share > storage between PG instances if I can avoid it. I would like to use the > 9.4.4 packages made for Ubuntu if at all possible (avoiding any patching). BDR requires patching PostgreSQL for 9.4 and 9.5. > I see there are many different ways to build a PG cluster. What would be > the best choice in my case? > > If I were to drop the master/master requirement and just do > master/slave, sending updates to one node, and doing all analytics on > the other node, what would be the best replication technique in this case? Stream replication seems the one which might fit better. Trigger based replication would choke on large bulk loads (unless you split them up into smaller pieces) Regards, -- Martín Marquéshttp://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- 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
Hi Weird. According to http://geopy.readthedocs.org/en/latest/#geopy.exc.GeocoderServiceError this is the most generic exception, only used when there is not a more specific one (which include by the way problems caused by connectivity and authorization). The message of the exception is supposed to be the one delivered by the original application. In this case: "Nombre o servicio desconocido": I am wondering if there is an error message matching the one you receive in PostgreSQL. > > 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. Somehow I expected that answer. I am not a python expert, but it happens that different pieces of code at the end do not use the same libraries or framework code by configuration. But if you are positive on that, no more discussion. > > 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... Well, if it worked a few times and then never again, then something must have changed in your system. This could also be a possible reason for the error. Sorry for asking obvious things, but it happened to me to oversee some of them in the past: Are you making the calls from the same computer? > 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. So it looks like a timeout problem with a confusing error message. A possible way to follow this up would be to call another webservice from a python function in PG and see if it behaves the same. Another way to analyze the problem could be to sniff the network traffic and see what really happens when you make the function call. This could help at least to discard network problems and may lead to some useful information. I hope that you will find a solution. Bye Charles -- 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
"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
