N
Neon•13mo ago
conscious-sapphire

read only transaction?

When i connect to a neon endpoint, sometimes it seems to think its a read only transaction? 
psql (16.1, server 16.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

platform-indexer-f35t=> create table test( i int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
platform-indexer-f35t=>
psql (16.1, server 16.4)
SSL connection (protocol: TLSv1.3, cipher: TLS_AES_256_GCM_SHA384, compression: off)
Type "help" for help.

platform-indexer-f35t=> create table test( i int);
ERROR:  cannot execute CREATE TABLE in a read-only transaction
platform-indexer-f35t=>
anyone know why and how to avoid this? for some additional context, there are some connections to this endpoint that use read only transaction mode, but freshly created connections seem to also be created in read only mode for some reason. with other postgres databases, new connections are not created in read only mode (i.e. read only mode is only per connection/session, shouldnt persist across different connections)
8 Replies
useful-bronze
useful-bronze•13mo ago
Are you using pgbouncer? (with -pooler connections) I'm not familiar with how postgres handles the read-only settings, but it's possible that pgbouncer isn't cleaning it up properly
conscious-sapphire
conscious-sapphireOP•13mo ago
i dont think im using any pooling, but im not positive actually 
await this.client.createProject({
            project: {
              org_id: this.conf.orgId,
              name: projName,
              region_id: this.conf.region,
              pg_version: NEON_PG_VERSION,
              provisioner: Provisioner.K8SNeonvm,
              default_endpoint_settings: {
                autoscaling_limit_min_cu: 0.25,
                autoscaling_limit_max_cu: 0.25,
              },
            },
          });
await this.client.createProject({
            project: {
              org_id: this.conf.orgId,
              name: projName,
              region_id: this.conf.region,
              pg_version: NEON_PG_VERSION,
              provisioner: Provisioner.K8SNeonvm,
              default_endpoint_settings: {
                autoscaling_limit_min_cu: 0.25,
                autoscaling_limit_max_cu: 0.25,
              },
            },
          });
here's my createProject for the tenant. then i create a specific branch/database under that project a bit later (attaching screenshot of this part because text limit). i dont think im using the pgbouncer connections feature? but not positive, does it look like i am?
No description
useful-bronze
useful-bronze•13mo ago
Oh, are you running your own platform? Cool! Yeah you wouldn't accidentally connect to pgbouncer in this case Or, do we expose provisioner Neonvm in our public api 🤔 We do! TIL 😅 ok ignore that one
conscious-sapphire
conscious-sapphireOP•13mo ago
yeah currently prototyping replacing rds with neon! any recs for ways to address? thx i need Provisioner.K8SNeonvm for autoscaling to work, right?
useful-bronze
useful-bronze•13mo ago
Are you using the GetConnectionURI api to get the connection string? Looking at the code, it seems that pgbouncer connections are the default there. Unfortunately missing from the docs it seems
conscious-sapphire
conscious-sapphireOP•13mo ago
Ah i just noticed the connection strings im getting the hostname does have -pooler in it!
useful-bronze
useful-bronze•13mo ago
I don't know if that flag actually does anything right now. I was under the impression we removed the K8sPod provisioner. But yes, Neonvm is autoscaling If you set pooled to false, hopefully it gets resolved. I'll try and take a look on Monday at how this is being handled by pgbouncer
conscious-sapphire
conscious-sapphireOP•13mo ago
yeah i am using getConnectionUri method. setting pooled: false, fixed it. thanks for the quick help!

Did you find this page helpful?