Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Creating a read only client #569

Closed
davetapley opened this issue Apr 15, 2014 · 7 comments
Closed

Creating a read only client #569

davetapley opened this issue Apr 15, 2014 · 7 comments

Comments

@davetapley
Copy link

I'd like to ensure all the queries going though a client are read only.

I can simulate this in psql using SET SESSION CHARACTERISTICS, thus:

psql (9.3.4, server 9.2.8)
Type "help" for help.

dave=# SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;                                              
SET
dave=# DELETE FROM visit WHERE date = '2014-04-08 09:52:54.09';
ERROR:  cannot execute DELETE in a read-only transaction

What would be a smart way to go about doing this in node-postgres?

@davetapley davetapley changed the title SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY; Creating a read only client Apr 15, 2014
@booo
Copy link
Contributor

booo commented Apr 16, 2014

Do you want to use the connection pool or do you want to create Client instances without the connection pool?

If you use new Client(...) create the client, invoke the connect function and issue the SET SESSION CHARACTERISTICS query. The client is one session so calling the function once should be enough. Example below:

var pg = require('pg');

var client = new pg.Client('postgres://testing:testing@localhost:5432/testing');

client.connect(function connect_handler(error){
  if(error) { console.error(error); }
  else {
    var result_handler = function result_handler(error, result) {
      if(error) { console.error(error); }
      else {
        console.log(result);
      }                                                                                                                                   
    };
    client.query('CREATE TABLE foo (bar int);', result_handler);
    client.query('INSERT INTO foo (bar) VALUES (42);', result_handler);
    client.query('SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;', result_handler);
    client.query('DELETE FROM foo;', result_handler);
    client.query('SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY;', result_handler);
  }
});

If you use the connection pool I would call the function every time I want to issue a query. Another solution might be to attach some state to the client and check if it's a new client after requesting the client from the pool and issue the query then.

As far as I know there is no way to check if a client is new but we have a feature request here:

#556

If you need the feature please participate in the discussion.

@davetapley
Copy link
Author

I wanted to use the connection pool.
Presumably, once you SET SESSION CHARACTERISTICS AS TRANSACTION READ ONLY on a pooled connection, it would remain read only when it goes back to the pool?

Ideally I'd be able to define some query to be run only once when a new connection is added to the pool. Do you think that'd be appropriate for node-postgres?

@hoegaarden
Copy link
Contributor

Hey @dukedave ...

I just hacked up a small gist which should do what you want: execute a custom query for every new client.
Well, it's not tested that much, but if there's demand, I could make a module based on this with tests and all this sweet stuff ...

HTH

@brianc
Copy link
Owner

brianc commented May 2, 2014

@dukedave It might be cool to make that it's own module. What would be ideal is breaking the pool out of node-postgres into it's own module, making it more extensible, and then having node-postgres consume that module for backwards compatibility. I've long had plans to do that, but I've been way too busy to get to it. :(

@davetapley
Copy link
Author

@hoegaarden thanks!
I'll take a look and let you know.

@booo
Copy link
Contributor

booo commented Aug 30, 2014

I do think we can close this one. @brianc should maybe create a ticket about restructuring node-postgres?

@booo booo closed this as completed Aug 30, 2014
@rex-remind101
Copy link

rex-remind101 commented Jul 18, 2019

Have there been any related changes to more easily allow read only clients since this was last discussed?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

5 participants