1

I have two AWS RDS postgres nodes backing a parallel mode pgpool setup on EC2. After using pgbench to populate test tables, I get odd behavior from test queries. Any query that uses a function produces the error mentioned in the subject line, while other queries work as expected. Three examples showing success, expected failure, and unexpected failure:

Success -- Yields the expected record set:

psql -c "SELECT aid FROM pgbench_accounts" "host=localhost port=9999 user=pgpool password=pass dbname=bench_parallel"
# Giant record set is returned here.

Since the backing nodes are on RDS, md5 authentication is required. Authentication appears to be working fine in the case of non-function queries, as can be seen by replacing the correct password above with an incorrect one.

Expected authentication failure:

psql -c "SELECT aid FROM pgbench_accounts" "host=localhost port=9999 user=pgpool password=notmypass dbname=bench_parallel"
psql: FATAL:  password authentication failed for user "pgpool"

Here's the part that has me baffled -- If I put a function like min() or count() into the query, I get authentication problems:

psql -c "SELECT count(aid) FROM pgbench_accounts" "host=localhost port=9999 user=pgpool password=pass dbname=bench_parallel"
ERROR:  password is required
DETAIL:  Non-superusers must provide a password in the connection string.

As can be seen from this last query, the password is supplied in the connection string (to the fronted, anyway) and it is the correct password as shown in the first query.

Why would my first query work fine with no auth problems, but the third one fail? Have I overlooked a setting somewhere?

Edit 2014-10-23: Adding more information.

I added superuser privileges to user pgpool on the (frontend) system database and no longer get Non-superusers must provide a password in the connection string as the error. Now I get:

ERROR:  could not establish connection
DETAIL:  fe_sendauth: no password supplied

Turning on debugging for pgpool and looking in the log, I see the query being rewritten as the following, which, in the call to dblink, does not contain the password specified in the original connection string:

2014-10-23 19:59:10 DEBUG: pid 1643: OneNode_do_command: Query:  SELECT 
    sum(pool_g$0) AS count FROM 
    dblink('host=ip-10-1-2-17 dbname=bench_parallel port=9999 user=pgpool',
    'SELECT pool_parallel("SELECT count(aid) FROM pgbench_accounts")',false) 
    AS pool_t$0g (pool_g$0 bigint )

2 Answers 2

0

This could be because pgpool sees this query as read-only, but since it's actually calling a function (which may contain INSERT UPDATE or DELETE statements) it breaks while connecting to the read-only server.

0

There is Citus(pgShard) that is supposed to work with standard Amazon RDS instances. It has catches though. You will have a single point of failure if you use the open source version. It's coordinator node is not duplicated.

You can get a fully HA seamless fail over version of it but you have to buy the enterprise licence, but it is CRAZY expensive. It will easily cost you $50,000 to $100,000 or more per year.

Also they are REALLY pushing their cloud version now which is even more insanely expensive.

https://www.citusdata.com/

There is also Postgres-XL but it also does not have fail over. If you lose any node you lose everything.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.