View Issue Details

IDProjectCategoryView StatusLast Update
0000136Pgpool-IIBugpublic2015-06-20 02:33
ReporterjaultAssigned Tot-ishii 
PrioritynormalSeveritymajorReproducibilityalways
Status resolvedResolutionopen 
PlatformLinuxOSUbuntuOS Version14.04
Product Version 
Target VersionFixed in Version 
Summary0000136: pgpool's use of to_regclass ignores double quotes
DescriptionI am trying to introduce pgpool into my environment and run my test suite, but consistently run into this error every time, at the same test. Here are the logs from pgpool II version 3.4.2 run with `sudo pgpool -n`

2015-06-03 15:40:41: pid 2899: LOG: DB node id: 0 backend pid: 2943 statement: Parse: SELECT typinput='array_in'::regproc, typtype FROM pg_catalog.pg_type LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r from pg_namespace as ns join ( select s.r, (current_schemas(false))[s.r] as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespace WHERE typname = $1 ORDER BY sp.r, pg_type.oid DESC LIMIT 1
2015-06-03 15:40:41: pid 2899: LOG: DB node id: 0 backend pid: 2943 statement: B message
2015-06-03 15:40:41: pid 2899: LOG: DB node id: 0 backend pid: 2943 statement: D message
2015-06-03 15:40:41: pid 2899: LOG: DB node id: 0 backend pid: 2943 statement: Execute: SELECT typinput='array_in'::regproc, typtype FROM pg_catalog.pg_type LEFT JOIN (select ns.oid as nspoid, ns.nspname, r.r from pg_namespace as ns join ( select s.r, (current_schemas(false))[s.r] as nspname from generate_series(1, array_upper(current_schemas(false), 1)) as s(r) ) as r using ( nspname ) ) as sp ON sp.nspoid = typnamespace WHERE typname = $1 ORDER BY sp.r, pg_type.oid DESC LIMIT 1
2015-06-03 15:40:41: pid 2899: LOG: DB node id: 0 backend pid: 2943 statement: SELECT count(*) FROM pg_catalog.pg_attrdef AS d, pg_catalog.pg_class AS c WHERE d.adrelid = c.oid AND d.adsrc ~ 'nextval' AND c.oid = to_regclass('acc0db07-bca5-4485-bf57-8b719c418b1f_Coffee Shop')
2015-06-03 15:40:41: pid 2899: FATAL: Backend throw an error message
2015-06-03 15:40:41: pid 2899: DETAIL: Exiting current session because of an error from backend
2015-06-03 15:40:41: pid 2899: HINT: BACKEND Error: "invalid name syntax"
2015-06-03 15:40:41: pid 2899: CONTEXT: while searching system catalog, When relcache is missed
2015-06-03 15:40:41: pid 2886: LOG: child process with pid: 2899 exits with status 256
2015-06-03 15:40:41: pid 2886: LOG: fork a new child process with pid: 2947

Here are the logs from postgres 9.4

2015-06-03 15:40:41 UTC [2943-1] mydb@mytable ERROR: invalid name syntax
2015-06-03 15:40:41 UTC [2943-2] mydb@mytable STATEMENT: SELECT count(*) FROM pg_catalog.pg_attrdef AS d, pg_catalog.pg_class AS c WHERE d.adrelid = c.oid AND d.adsrc ~ 'nextval' AND c.oid = to_regclass('acc0db07-bca5-4485-bf57-8b719c418b1f_Coffee Shop')

I have a single pgpool II running in front of two postgresql 9.4 nodes setup properly with master/slave streaming replication.
Steps To ReproduceUnfortunately the test suite and product I am testing is not open sourced but in general, to reproduce this you need to introduce table names with spaces in them. In postgres 9.4 the to_regclass function fails with the invalid_name syntax error when given a single quoted string with a space in it. Manually running that query and using double quotes instead fixes the issue.
Additional InformationThis is a pgpool II problem because I can connect to the master node directly and everything works as expected. Mostly because this query with to_regclass is never run and is something pgpool introduces.

My application is a java application connecting with JDBC. The query we are running uses double quotes for the table name and looks like this

! Hint: BACKEND Error: "invalid name syntax" [statement:"INSERT INTO "<tableName>" (content) VALUES (:content)", located:"INSERT INTO "acc0db07-bca5-4485-bf57-8b719c418b1f_Coffee Shop" (content) VALUES (:content)", rewritten:"INSERT INTO "acc0db07-bca5-4485-bf57-8b719c418b1f_Coffee Shop" (content) VALUES (?)

Note the rewritten portion. Is there a way I can have pgpool keep the double quotes? Or perhaps turn off this querying ahead of time functionality? Another thing about this application is that it creates and drops these tables on the fly. So any caching done on this is not needed. I've attached my pgpool.conf file.
TagsNo tags attached.

Activities

jault

2015-06-04 01:08

reporter  

pgpool.conf (33,369 bytes)

t-ishii

2015-06-20 02:32

developer   ~0000540

Fix committed.

Issue History

Date Modified Username Field Change
2015-06-04 01:08 jault New Issue
2015-06-04 01:08 jault File Added: pgpool.conf
2015-06-18 17:09 t-ishii Assigned To => t-ishii
2015-06-18 17:09 t-ishii Status new => assigned
2015-06-20 02:32 t-ishii Note Added: 0000540
2015-06-20 02:33 t-ishii Status assigned => resolved