[Bug]: Publication incorrectly compiles SQL when used with multiple tables #8588
Description
Is there an existing issue already for this bug?
- I have searched for an existing issue, and could not find anything. I believe this is a new bug.
I have read the troubleshooting guide
- I have read the troubleshooting guide and I think this is a new bug.
I am running a supported version of CloudNativePG
- I have read the troubleshooting guide and I think this is a new bug.
Contact Details
Version
1.26 (latest patch)
What version of Kubernetes are you using?
1.31
What is your Kubernetes environment?
Other
How did you install the operator?
Helm
What happened?
When creating a publication for multiple tables cnpg incorrectly generates the SQL, and thus fails to create the publication.
In my case this is a publication for a database and 2 tables in the same scheme.
I have tried following example:
spec:
cluster:
name: clusterX
dbname: log_crm
name: dbz_publication
publicationReclaimPolicy: delete
target:
objects:
- table:
name: log_crm.crm_employees
- table:
name: log_crm.crm_user_actions
And this generates following log and fails:
"message":"syntax error at or near \"TABLE\"","query":"CREATE PUBLICATION \"dbz_publication\" FOR TABLE \"log_crm.crm_employees\", TABLE \"log_crm.crm_user_actions\""
One can understand that for each table object in the list the operator tries to append a separate TABLE <table_name> , however seemingly postgres actually expect a single TABLE definition followed by a list of tables.
I have also tried following way to try and create all through a single table object:
spec:
cluster:
name: clusterX
dbname: log_crm
name: dbz_publication
publicationReclaimPolicy: delete
target:
objects:
- table:
name: log_crm.crm_employees,log_crm.crm_user_actions
However, this as well fails:
"message":"relation \"log_crm.crm_employees,log_crm.crm_user_actions\" does not exist","query":"CREATE PUBLICATION \"dbz_publication\" FOR TABLE \"log_crm.crm_employees,log_crm.crm_user_actions\""
Let me note that unlike Publication CRD, cmd using cnpg plugin works pretty well:
$ kubectl cnpg publication create log-crm-infra-2221-postgresql --dbname log_crm --publication dbz_publication --table log_crm.crm_employees,log_crm.crm_user_actions --dry-run
Comand results in following SQL command generated:
CREATE PUBLICATION "dbz_publication" FOR TABLE log_crm.crm_employees, log_crm.crm_user_actions
Cluster resource
apiVersion: postgresql.cnpg.io/v1
kind: Cluster
metadata:
name: clusterX
namespace: default
annotations:
helm.sh/hook-weight: "-5"
spec:
instances: 1
imageName: ghcr.io/cloudnative-pg/postgresql:13.15
imagePullPolicy: IfNotPresent
postgresUID: 26
postgresGID: 26
storage:
size: 20Gi
resources:
limits:
cpu: "0.5"
memory: 2Gi
requests:
cpu: "0.5"
memory: 2Gi
affinity:
additionalPodAntiAffinity:
preferredDuringSchedulingIgnoredDuringExecution:
- podAffinityTerm:
labelSelector:
matchExpressions:
- key: cnpg.io/cluster
operator: In
values:
- clusterX
- key: cnpg.io/podRole
operator: In
values:
- instance
topologyKey: node.cluster.x-k8s.io/esxi-host
weight: 100
enablePodAntiAffinity: true
nodeSelector:
postgres: "true"
podAntiAffinityType: required
tolerations:
- effect: NoSchedule
key: node-role.kubernetes.io/postgres
operator: Exists
topologyKey: kubernetes.io/hostname
primaryUpdateMethod: switchover
primaryUpdateStrategy: unsupervised
logLevel: info
enableSuperuserAccess: true
enablePDB: true
postgresql:
parameters:
archive_timeout: 1min
autovacuum_analyze_scale_factor: "0.05"
autovacuum_naptime: 15s
autovacuum_vacuum_cost_limit: "200"
autovacuum_vacuum_scale_factor: "0.1"
effective_cache_size: 1024MB
log_lock_waits: "on"
log_min_duration_statement: 50ms
max_connections: "400"
max_slot_wal_keep_size: 2GB
pg_stat_statements.max: "10000"
pg_stat_statements.track: all
shared_buffers: 512MB
timezone: UTC
track_activity_query_size: "4096"
wal_compression: "on"
managed:
roles:
- comment: Role placeholder for debezium grants. Further grants updated manually
ensure: present
name: debezium_grants
replication: true
- ensure: present
inRoles:
- debezium_grants
inherit: true
login: true
name: debezium
passwordSecret:
name: clusterX-debezium-credentials-db
monitoring:
enablePodMonitor: false
disableDefaultQueries: false
bootstrap:
initdb:
database: log_crm
secret:
name: clusterX-credentials-dbRelevant log output
"message":"syntax error at or near \"TABLE\"","query":"CREATE PUBLICATION \"dbz_publication\" FOR TABLE \"log_crm.crm_employees\", TABLE \"log_crm.crm_user_actions\""
"message":"relation \"log_crm.crm_employees,log_crm.crm_user_actions\" does not exist","query":"CREATE PUBLICATION \"dbz_publication\" FOR TABLE \"log_crm.crm_employees,log_crm.crm_user_actions\""Code of Conduct
- I agree to follow this project's Code of Conduct