[pgpool-general: 9321] Re: pgpool-II INSERTS are not persistent (most likely with prepared statements)

[Bo Peng]

Hi,

> After the upgrade, I'm having a problem that some INSERTS (looks like 
> with Prepared Statements only) don't work and are executed correctly on 
> the PostgreSQL Server (e.g. sequences are counted up). SELECTs done 
> immediatly (e.g. web page updates) get the new data BUT FOR ONLY SOME 
> seconds. Then the data is removed somehow (connection loss/different 
> connection?).

Could you enable the following parameters and try "2x PHP apps with prepared statements" again?

  log_client_messages = on
  log_per_node_statement = on

Please share the pgpool logs.

On Mon, 13 Jan 2025 18:34:07 +0100
Gerhard Wiesinger <lists at wiesinger.com> wrote:

> Hello,
> 
> I made an upgrade of the following PostgreSQL and pgpool-II components:
> - PostgreSQL: 9.6.10 to 17.2
> - pgpool-II: pgpool-II-3.7.5 to pgpool-II-4.5.5
> 
> I only want to use pgpool-II for connection pooling (worked flawless 
> with all of the old versions).
> 
> Architecture looks like:
> Clients <-> pgpool-II <-> PostgreSQL-Server (single instance)
> 
> After the upgrade, I'm having a problem that some INSERTS (looks like 
> with Prepared Statements only) don't work and are executed correctly on 
> the PostgreSQL Server (e.g. sequences are counted up). SELECTs done 
> immediatly (e.g. web page updates) get the new data BUT FOR ONLY SOME 
> seconds. Then the data is removed somehow (connection loss/different 
> connection?).
> 
> SELECTs in general work well.
> 
> When I'm removing pgpool-II from the chain everything works well!
> 
> Status of different clients for different scenarios of INSERTs:
> - 2x PHP apps with prepared statements: NOT OK
> - Perl Code without prepared statements: OK
> - PGAdmin4 manual execution (as reference): OK
> 
> Non default pgpool-II config: see below:
> 
> Any ideas what's happening?
> Does the configuration for connection pooling look feasible?
> Any ideas why this happens only with prepared statements?
> 
> OS: Fedora 41
> all other versions from PGDG.
> detailed pgpool version: pgpool-II-4.5.5-1PGDG.f41.x86_64
> detailed PostgreSQL version: postgresql17-server-17.2-1PGDG.f41.x86_64
> 
> If you need further information let me know.
> 
> Thnx.
> 
> Ciao,
> Gerhard
> 
> Non standard config values:
> backend_clustering_mode = 'raw'
> listen_addresses = '*'
> unix_socket_directories = '/run/pgpool-II'
> pcp_listen_addresses = '*'
> backend_hostname0 = 'localhost'
> backend_port0 = 5432
> backend_weight0 = 1
> backend_data_directory0 = '/var/lib/pgsql/17/data'
> backend_flag0 = 'ALLOW_TO_FAILOVER'
> enable_pool_hba = off
> num_init_children = 32
> child_life_time = 10
> connection_life_time = 2
> pid_file_name = '/run/pgpool-II/pgpool.pid'
> hostname0 = ''
> _______________________________________________
> pgpool-general mailing list
> pgpool-general at pgpool.net
> http://www.pgpool.net/mailman/listinfo/pgpool-general


-- 
Bo Peng <pengbo at sraoss.co.jp>
SRA OSS K.K.
TEL: 03-5979-2701 FAX: 03-5979-2702
URL: https://www.sraoss.co.jp/