Re: pgsql_fdw, FDW for PostgreSQL server

This is Gerald Devotta, Recruitment Specialist with Newt Global LLC, a
global staffing solutions provider that has been serving the
telecommunications and utility industries.

I am contacting you to let you know that your resume came to my attention
while I was conducting a job search for a project in Bloomington Illinois. I
have reviewed your resume with particular interest and am excited to let you
know that many of your qualifications match my client's needs

PostgreSQL database developer

Bloomington Illinois

2 +Years Contract

Attractive Compensation + Expenses paid

Required:

Solid experience with PgSQL programming on PostgreSQL ORBMS

Person can work 4 days at site and one day from home

If you are interested kindly send your update resume with expected
rate/salary

Regards,

Gerald Devotta

Recruitment Analyst

Newt Global Consulting LLC

Phone: 202.470.2492

Email: gdevotta(at)newtglobal(dot)com <mailto:hr5(at)newtglobal(dot)com> |
www.newtglobal.com

1300 W Walnut Hill Lane, Suite #230, Irving, TX 75038

From: Shigeru Hanada-2 [via PostgreSQL]
[mailto:ml-node+s1045698n5626807h34(at)n5(dot)nabble(dot)com]
Sent: Monday, April 09, 2012 12:39 AM
To: Gerald Devotta
Subject: Re: pgsql_fdw, FDW for PostgreSQL server

(2012/04/08 5:19), Thom Brown wrote:
> 2012/4/7 Shigeru HANADA<[hidden email]>:
>> I've updated pgsql_fdw so that it can collect statistics from foreign
>> data with new FDW API.
>
> I notice that if you restart the remote server, the connection is
> broken, but the client doesn't notice this until it goes to fire off
> another command. Should there be an option to automatically
> re-establish the connection upon noticing the connection has dropped,
> and issue a NOTICE that it had done so?

Hm, I'd prefer reporting the connection failure and aborting the local
transaction, because reconnecting to the server would break consistency
between the results come from multiple foreign tables. Server shutdown
(or other troubles e.g. network failure) might happen at various timing
in the sequence of remote query (or sampling in ANALYZE). For example,
when we execute a local query which contains two foreign tables, foo and
bar, then the sequence of libpq activity would be like this.

1) connect to the server at the beginning of the local query
2) execute EXPLAIN for foreign table foo
3) execute EXPLAIN for foreign table bar
4) execute actual query for foreign table foo
5) execute actual query for foreign table bar
6) disconnect from the server at the end of the local query

If the connection has broken between 4) and 5), and immediate reconnect
succeeded, retrieved results for foo and bar might be inconsistent from
the viewpoint of transaction isolation.

In current implementation, next local query which contains foreign table
of failed foreign table tries to reconnect to the server.

> Also I'm not particularly keen on the message provided to the user in
> this event:
>
> ERROR: could not execute EXPLAIN for cost estimation
> DETAIL: FATAL: terminating connection due to administrator command
> FATAL: terminating connection due to administrator command
>
> There's no explanation what the "administrator" command was, and I
> suspect this is really just a "I don't know what's happened here"
> condition. I don't think we should reach that point.

That FATAL message is returned by remote backend's ProcessInterrupts()
during some administrator commands, such as immediate shutdown or
pg_terminate_backend(). If remote backend died of fast shutdown or
SIGKILL, no error message is available (see the sample below).

postgres=# select * From pgsql_branches ;
ERROR: could not execute EXPLAIN for cost estimation
DETAIL:
HINT: SELECT bid, bbalance, filler FROM public.pgbench_branches

I agree that the message is confusing. How about showing message like
"pgsql_fdw connection failure on <servername>" or something with remote
error message for such cases? It can be achieved by adding extra check
for connection status right after PQexec()/PQexecParams(). Although
some word polishing would be required :)

postgres=# select * from pgsql_branches ;
ERROR: pgsql_fdw connection failure on subaru_pgbench
DETAIL: FATAL: terminating connection due to administrator command
FATAL: terminating connection due to administrator command

This seems to impress users that remote side has some trouble.

Regards,
--
Shigeru HANADA

--
Sent via pgsql-hackers mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

_____

If you reply to this email, your message will be added to the discussion
below:

http://postgresql.1045698.n5.nabble.com/pgsql-fdw-FDW-for-PostgreSQL-server-
tp4935560p5626807.html

To unsubscribe from PostgreSQL, click
<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=unsu
bscribe_by_code&node=1843779&code=Z2Rldm90dGFAbmV3dGdsb2JhbC5jb218MTg0Mzc3OX
wtNTE4Mjg3MzQy> here.

<http://postgresql.1045698.n5.nabble.com/template/NamlServlet.jtp?macro=macr
o_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.B
asicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.templ
ate.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-insta
nt_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml>
NAML

-----

Regards,
Gerald Devotta
Recruitment Analyst
Newt Global Consulting LLC
Phone: 202.470.2492
Email: gdevotta(at)newtglobal(dot)com | www.newtglobal.com
1300 W Walnut Hill Lane, Suite #230, Irving, TX 75038

--
View this message in context: http://postgresql.1045698.n5.nabble.com/pgsql-fdw-FDW-for-PostgreSQL-server-tp4935560p5626813.html
Sent from the PostgreSQL - hackers mailing list archive at Nabble.com.