From: | PG Bug reporting form <noreply(at)postgresql(dot)org> |
---|---|
To: | pgsql-bugs(at)lists(dot)postgresql(dot)org |
Cc: | tiago_anastacio(at)yahoo(dot)fr |
Subject: | BUG #15726: parallel queries failed ERROR: invalid name syntax CONTEXT: parallel worker |
Date: | 2019-04-01 11:12:16 |
Message-ID: | 15726-6d67e4fa14f027b3@postgresql.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
The following bug has been logged on the website:
Bug reference: 15726
Logged by: Tiago ANASTACIO
Email address: tiago_anastacio(at)yahoo(dot)fr
PostgreSQL version: 11.2
Operating system: Red Hat Enterprise Linux Server release 7.1 (Maipo
Description:
Hello, I'am using the reporting procedure describe on wiki.
1 - Description
--------------------
I create a sample table, insert data, analyze it, select count(*) and
finally create index
All parallel queries display immediatly an error:
ERROR: invalid name syntax
CONTEXT: parallel worker
appli3=# create table test1(col1 bigint);
CREATE TABLE
appli3=# insert into test1 select generate_series(1,10000000);
INSERT 0 10000000
appli3=# analyse test1;
ANALYZE
appli3=# explain select count(*) from appli3adm.test1;
QUERY PLAN
------------------------------------------------------------------------------------------
Finalize Aggregate (cost=97331.80..97331.81 rows=1 width=8)
-> Gather (cost=97331.58..97331.79 rows=2 width=8)
Workers Planned: 2
-> Partial Aggregate (cost=96331.58..96331.59 rows=1 width=8)
-> Parallel Seq Scan on test1 (cost=0.00..85914.87
rows=4166687 width=0)
(5 rows)
appli3=# select count(*) from appli3adm.test1;
ERROR: invalid name syntax
CONTEXT: parallel worker
appli3=# create index test_1_col1_idx on appli3adm.test1(col1);
ERROR: invalid name syntax
CONTEXT: parallel worker
appli3=#
All postgresql Logs:
--------------------------
2019-04-01 12:38:27 CEST [22962]: [3-1]
user=dbaappli3,db=appli3,app=psql,xid=0,sqlstate=00000,client=[local] LOG:
statement: create table test1(col1 bigint);
2019-04-01 12:38:47 CEST [22962]: [4-1]
user=dbaappli3,db=appli3,app=psql,xid=930,sqlstate=00000,client=[local] LOG:
duration: 9747.640 ms plan:
Query Text: insert into test1 select generate_series(1,10000000);
Insert on appli3adm.test1 (cost=0.00..17.52 rows=1000 width=8)
-> Subquery Scan on "*SELECT*" (cost=0.00..17.52 rows=1000
width=8)
Output: "*SELECT*".generate_series
-> ProjectSet (cost=0.00..5.02 rows=1000 width=4)
Output: generate_series(1, 10000000)
-> Result (cost=0.00..0.01 rows=1 width=0)
2019-04-01 10:40:10.064 GMT [23473] ERROR: invalid name syntax
2019-04-01 12:40:10 CEST [22962]: [5-1]
user=dbaappli3,db=appli3,app=psql,xid=0,sqlstate=42602,client=[local] ERROR:
invalid name syntax
2019-04-01 12:40:10 CEST [22962]: [6-1]
user=dbaappli3,db=appli3,app=psql,xid=0,sqlstate=42602,client=[local]
CONTEXT: parallel worker
2019-04-01 12:40:10 CEST [22962]: [7-1]
user=dbaappli3,db=appli3,app=psql,xid=0,sqlstate=42602,client=[local]
STATEMENT: select count(*) from appli3adm.test1;
2019-04-01 10:40:10.065 GMT [23474] ERROR: invalid name syntax
2019-04-01 12:40:10 CEST [22556]: [9-1]
user=,db=,app=,xid=0,sqlstate=00000,client= LOG: background worker
"parallel worker" (PID 23473) exited with exit code 1
2019-04-01 12:40:10 CEST [22556]: [10-1]
user=,db=,app=,xid=0,sqlstate=00000,client= LOG: background worker
"parallel worker" (PID 23474) exited with exit code 1
2019-04-01 12:40:21 CEST [22962]: [8-1]
user=dbaappli3,db=appli3,app=psql,xid=0,sqlstate=00000,client=[local] LOG:
statement: create index test_1_col1_idx on appli3adm.test1(col1);
2019-04-01 10:40:21.784 GMT [23546] ERROR: invalid name syntax
2019-04-01 10:40:21.784 GMT [23547] ERROR: invalid name syntax
2019-04-01 12:40:21 CEST [22962]: [9-1]
user=dbaappli3,db=appli3,app=psql,xid=933,sqlstate=42602,client=[local]
ERROR: invalid name syntax
2019-04-01 12:40:21 CEST [22962]: [10-1]
user=dbaappli3,db=appli3,app=psql,xid=933,sqlstate=42602,client=[local]
CONTEXT: parallel worker
2019-04-01 12:40:21 CEST [22962]: [11-1]
user=dbaappli3,db=appli3,app=psql,xid=933,sqlstate=42602,client=[local]
STATEMENT: create index test_1_col1_idx on appli3adm.test1(col1);
2019-04-01 12:40:21 CEST [22556]: [11-1]
user=,db=,app=,xid=0,sqlstate=00000,client= LOG: background worker
"parallel worker" (PID 23547) exited with exit code 1
2019-04-01 12:40:21 CEST [22556]: [12-1]
user=,db=,app=,xid=0,sqlstate=00000,client= LOG: background worker
"parallel worker" (PID 23546) exited with exit code 1
2019-04-01 12:40:36 CEST [22962]: [12-1]
user=dbaappli3,db=appli3,app=psql,xid=0,sqlstate=00000,client=[local] LOG:
disconnection: session time: 0:02:18.357 user=dbaappli3 database=appli3
host=[local]
2 - postgresql version()
-------------------------------
appli3=# select version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 11.2 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5
20150623 (Red Hat 4.8.5-36), 64-bit
(1 row)
3 - Postgresql installation
-----------------------------------
community packages for red hat from
http://download.postgresql.org/pub/repos/yum/11/redhat/
lxts247a:/etc$yum list installed | grep ^postgresql11
postgresql11.x86_64 11.2-2PGDG.rhel7
@/postgresql11-11.2-2PGDG.rhel7.x86_64
postgresql11-contrib.x86_64 11.2-2PGDG.rhel7
@/postgresql11-contrib-11.2-2PGDG.rhel7.x86_64
postgresql11-libs.x86_64 11.2-2PGDG.rhel7
@/postgresql11-libs-11.2-2PGDG.rhel7.x86_64
postgresql11-llvmjit.x86_64 11.2-2PGDG.rhel7
@/postgresql11-llvmjit-11.2-2PGDG.rhel7.x86_64
postgresql11-plpython.x86_64 11.2-2PGDG.rhel7
@/postgresql11-plpython-11.2-2PGDG.rhel7.x86_64
postgresql11-server.x86_64 11.2-2PGDG.rhel7
@/postgresql11-server-11.2-2PGDG.rhel7.x86_64
4 - Changes made to postgresql.conf from the sample file
/usr/pgsql-11/share/postgresql.conf.sample
----------------------------------------------------------------------------
listen_addresses = '*'
port = 54322
superuser_reserved_connections = 6
unix_socket_directories = '/var/run/postgresql
unix_socket_permissions = 0722
authentication_timeout = 20s
password_encryption = md5
ssl = on
ssl_ca_file = '/home/appli3/appli/connexion/dbaappli3/tls/root.crt'
ssl_cert_file = '/home/appli3/appli/connexion/dbaappli3/tls/server.crt'
ssl_key_file = '/home/appli3/appli/connexion/dbaappli3/tls/server.key'
shared_buffers = 2GB
temp_buffers = 32MB
work_mem = 128MB
maintenance_work_mem = 3GB
autovacuum_work_mem = 64MB
max_stack_depth = 6MB
vacuum_cost_limit = 2000
effective_io_concurrency = 256
effective_io_concurrency = 256
max_worker_processes = 8
max_parallel_maintenance_workers = 2
max_parallel_workers_per_gather = 2
parallel_leader_participation = on
max_parallel_workers = 4
wal_level = replica
fsync = on
synchronous_commit = remote_write
wal_compression = on
wal_log_hints = on
checkpoint_timeout = 30min
max_wal_size = 15GB
checkpoint_completion_target = 0.9
archive_mode = on
archive_command = 'test ! -f /home/backupdb/appli3/archxlog/%f.gz && cp %p
/home/backupdb/appli3/archxlog/%f && gzip /home/backupdb/appli3/archxlog/%f'
# command to use to archive a logfile segment
archive_timeout = 60s
max_wal_senders = 18
wal_keep_segments = 0
max_replication_slots = 8
hot_standby = on
random_page_cost = 1.0
effective_cache_size = 6GB
jit = on
log_checkpoints = on
log_connections = on
log_disconnections = on
log_line_prefix = '%t [%p]: [%l-1]
user=%u,db=%d,app=%a,xid=%x,sqlstate=%e,client=%h '
log_statement = 'ddl' # none, ddl, mod, all
log_temp_files = 4MB # log temporary files equal or
larger
log_timezone = 'Europe/Paris'
cluster_name = 'appli3'
track_io_timing = on
track_functions = all # none, pl, all
track_activity_query_size = 65536 # (change requires restart)
autovacuum = on
log_autovacuum_min_duration = 60000
autovacuum_vacuum_scale_factor = 0.01
autovacuum_analyze_scale_factor = 0.10
search_path = 'appli3adm, common, dba, "$user"'
default_tablespace = 'data'
temp_tablespaces = 'data'
lc_messages = 'C'
lc_monetary = 'fr_FR.utf8'
lc_numeric = 'fr_FR.utf8'
lc_time = 'fr_FR.utf8'
default_text_search_config = '' #default 'pg_catalog.simple'
shared_preload_libraries = 'auto_explain, pg_stat_statements, auth_delay,
passwordcheck, plugin_debugger, repmgr'
max_locks_per_transaction = 256
include_dir = 'pg_includes'
include_if_exists =
'/home/appli3/appli/connexion/dbaappli3/custom/postgresql_custom.conf'
auth_delay.milliseconds = 100
auto_explain.log_min_duration = '5s'
auto_explain.log_analyze = off
auto_explain.log_buffers = on
auto_explain.log_timing = on
auto_explain.log_triggers = on
auto_explain.log_verbose = on
auto_explain.log_format = text
auto_explain.log_nested_statements = on
pg_stat_statements.max = 10000
pg_stat_statements.track = all
4 - OS details
------------------
OS
-----
Red Hat Enterprise Linux Server release 7.1 (Maipo)
Kernel
--------
lxts247a:/tmp$uname -a
Linux lxts247a 3.10.0-229.el7.x86_64 #1 SMP Thu Jan 29 18:37:38 EST 2015
x86_64 x86_64 x86_64 GNU/Linux
CPU:
-------
lxts247a:/etc$lscpu
Architecture: x86_64
CPU op-mode(s): 32-bit, 64-bit
Byte Order: Little Endian
CPU(s): 12
On-line CPU(s) list: 0-11
Thread(s) per core: 1
Core(s) per socket: 12
Socket(s): 1
NUMA node(s): 1
Vendor ID: GenuineIntel
CPU family: 6
Model: 63
Model name: Intel(R) Xeon(R) CPU E5-2670 v3 @ 2.30GHz
Stepping: 2
CPU MHz: 2297.339
BogoMIPS: 4594.67
Hypervisor vendor: VMware
Virtualization type: full
L1d cache: 32K
L1i cache: 32K
L2 cache: 256K
L3 cache: 30720K
NUMA node0 CPU(s): 0-11
MEMORY
-------------
lxts247a:/etc$free
total used free shared buff/cache
available
Mem: 32782068 1139348 2799556 686408 28843164
30606124
Swap: 4194300 436 4193864
6 - full logs are describe on 1-
Full error message is:
ERROR: invalid name syntax
CONTEXT: parallel worker
7- client
-----------
local psql
lxts247a:/tmp$psql -V
psql (PostgreSQL) 11.2
8-log
I ssee nothing special in the logs parallel_workers are new to me.
Thank you very much for your help guys
Tiago ANASTACIO
Independant DBA
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Paquier | 2019-04-01 13:29:25 | Re: BUG #15726: parallel queries failed ERROR: invalid name syntax CONTEXT: parallel worker |
Previous Message | Etsuro Fujita | 2019-04-01 06:57:29 | Re: BUG #15724: Can't create foreign table as partition |