leafo / pgmoon Goto Github PK

2014-09-16 17:07:34 UTC LOG:  invalid password packet size

The fastest solution was ngx_postgres. My 1000 times loop took 1.3 sec.
 The second fastest solution was lua-resty-postgres which took 1.8 sec. 
And then, I tested pgmoon which took 4.4 sec. I guess it was slower because it does type conversion in Lua, although I am not sure that this is the only reason.

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

# TYPE  DATABASE        USER            ADDRESS                 METHOD
# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 trust

grep -v '^#' /etc/postgresql/9.5/main/postgresql.conf  | grep -v '^$' | grep -v '#'
archive_mode = on
effective_cache_size = 20GB
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
log_checkpoints = on
log_disconnections = on
log_timezone = 'PRC'
stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp'
datestyle = 'iso, ymd'
timezone = 'PRC'
default_text_search_config = 'pg_catalog.english'
root@25-108:/home/www/test_openresty# grep -v '^#' /etc/postgresql/9.5/main/postgresql.conf  | grep -v '^$' 
data_directory = '/data/pgdata'		# use data in another directory
					# (change requires restart)
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'	# host-based authentication file
					# (change requires restart)
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'	# ident configuration file
					# (change requires restart)
external_pid_file = '/var/run/postgresql/9.5-main.pid'			# write an extra PID file
					# (change requires restart)
listen_addresses = '*'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 4000                  # (change requires restart)
unix_socket_directories = '/var/run/postgresql'	# comma-separated list of directories
					# (change requires restart)
					# (change requires restart)
					# (change requires restart)
					# (change requires restart)
ssl = true				# (change requires restart)
					# (change requires restart)
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'		# (change requires restart)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'		# (change requires restart)
					# 0 selects the system default
					# 0 selects the system default
					# 0 selects the system default
shared_buffers = 16GB # min 128kB
					# (change requires restart)
					# (change requires restart)
temp_buffers = 32MB			# min 800kB
					# (change requires restart)
work_mem = 8192MB				# min 64kB
maintenance_work_mem = 8192MB		# min 1MB
max_stack_depth = 4096			# min 100kB
dynamic_shared_memory_type = posix	# the default is the first option
					# supported by the operating system:
					#   posix
					#   sysv
					#   windows
					#   mmap
					# use none to disable dynamic shared memory
					# in kB, or -1 for no limit
					# (change requires restart)
vacuum_cost_delay = 10ms			# 0-100 milliseconds
vacuum_cost_limit = 10000		# 1-10000 credits
bgwriter_delay = 10ms			# 10-10000ms between rounds
wal_level =  hot_standby  # minimal, archive, hot_standby, or logical
					# (change requires restart)
synchronous_commit = on		# synchronization level;
					# off, local, remote_write, or on
					# supported by the operating system:
					#   open_datasync
					#   fdatasync (default on Linux)
					#   fsync
					#   fsync_writethrough
					#   open_sync
					# (change requires restart)
wal_buffers = 16MB			# min 32kB, -1 sets based on shared_buffers
					# (change requires restart)
wal_writer_delay = 20ms		# 1-10000 milliseconds
checkpoint_timeout = 5min		# range 30s-1h
archive_mode = on
				# (change requires restart)
				# placeholders: %p = path of file to archive
				#               %f = file name only
				# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
				# number of seconds; 0 disables
max_wal_senders = 32		# max number of walsender processes
				# (change requires restart)
				# (change requires restart)
				# (change requires restart)
				# comma-separated list of application_name
				# from standby(s); '*' = all
hot_standby = on # "on" allows queries during recovery
					# (change requires restart)
					# when reading WAL from archive;
					# -1 allows indefinite delay
					# when reading streaming WAL;
					# -1 allows indefinite delay
					# 0 disables
					# query conflicts
					# communication from master
					# in milliseconds; 0 disables
					# retrieve WAL after a failed attempt
effective_cache_size = 20GB
					# JOIN clauses
log_destination = 'csvlog'		# Valid values are combinations of
					# stderr, csvlog, syslog, and eventlog,
					# depending on platform.  csvlog
					# requires logging_collector to be on.
logging_collector = on		# Enable capturing of stderr and csvlog
					# into log files. Required to be on for
					# csvlogs.
					# (change requires restart)
					# can be absolute or relative to PGDATA
					# can include strftime() escapes
					# begin with 0 to use octal notation
log_truncate_on_rotation = on # If on, an existing log file with the
					# same name as the new log file will be
					# truncated rather than appended to.
					# But such truncation only occurs on
					# time-driven rotation, not on restarts
					# or size-driven rotation.  Default is
					# off, meaning append to existing files
					# in all cases.
log_rotation_age = 1d			# Automatic rotation of logfiles will
					# happen after that time.  0 disables.
log_rotation_size = 10MB		# Automatic rotation of logfiles will
					# happen after that much log output.
					# 0 disables.
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'
client_min_messages = notice # values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   log
					#   notice
					#   warning
					#   error
log_min_messages = notice # values in order of decreasing detail:
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic
					#   debug5
					#   debug4
					#   debug3
					#   debug2
					#   debug1
					#   info
					#   notice
					#   warning
					#   error
					#   log
					#   fatal
					#   panic (effectively off)
log_min_duration_statement = 1000ms	# -1 is disabled, 0 logs all statements
					# and their durations, > 0 logs only
					# statements running at least this number
					# of milliseconds
log_checkpoints = on
log_disconnections = on
log_error_verbosity = verbose # terse, default, or verbose messages
log_line_prefix = '%t [%p-%l] %q%u@%d '			# special values:
					#   %a = application name
					#   %u = user name
					#   %d = database name
					#   %r = remote host and port
					#   %h = remote host
					#   %p = process ID
					#   %t = timestamp without milliseconds
					#   %m = timestamp with milliseconds
					#   %i = command tag
					#   %e = SQL state
					#   %c = session ID
					#   %l = session line number
					#   %s = session start timestamp
					#   %v = virtual transaction ID
					#   %x = transaction ID (0 if none)
					#   %q = stop here in non-session
					#        processes
					#   %% = '%'
					# e.g. '<%u%%%d> '
log_lock_waits = on # log lock waits >= deadlock_timeout
					# than the specified size in kilobytes;
					# -1 disables, 0 logs all temp files
log_timezone = 'PRC'
					# (change requires restart)
stats_temp_directory = '/var/run/postgresql/9.5-main.pg_stat_tmp'
autovacuum = on			# Enable autovacuum subprocess?  'on'
					# requires track_counts to also be on.
log_autovacuum_min_duration = 0 	# -1 disables, 0 logs all actions and
					# their durations, > 0 logs only
					# actions running at least this number
					# of milliseconds.
					# (change requires restart)
					# vacuum
					# analyze
					# (change requires restart)
					# before forced vacuum
					# (change requires restart)
					# autovacuum, in milliseconds;
					# -1 means use vacuum_cost_delay
					# autovacuum, -1 means use
					# vacuum_cost_limit
					# only default tablespace
datestyle = 'iso, ymd'
timezone = 'PRC'
					# abbreviations.  Currently, there are
					#   Default
					#   Australia (historical usage)
					#   India
					# You can create your own file in
					# share/timezonesets/.
					# encoding
lc_messages = 'en_US.UTF-8'			# locale for system error message
					# strings
lc_monetary = 'zh_CN.UTF-8'			# locale for monetary formatting
lc_numeric = 'zh_CN.UTF-8'			# locale for number formatting
lc_time = 'zh_CN.UTF-8'				# locale for time formatting
default_text_search_config = 'pg_catalog.english'
					# (change requires restart)
					# (change requires restart)
					# directory 'conf.d'
root@25-108:/home/www/test_openresty# ^C
root@25-108:/home/www/test_openresty# grep -v '^#' /etc/postgresql/9.5/main/postgresql.conf  | grep -v '^$'  
data_directory = '/data/pgdata'		# use data in another directory
					# (change requires restart)
hba_file = '/etc/postgresql/9.5/main/pg_hba.conf'	# host-based authentication file
					# (change requires restart)
ident_file = '/etc/postgresql/9.5/main/pg_ident.conf'	# ident configuration file
					# (change requires restart)
external_pid_file = '/var/run/postgresql/9.5-main.pid'			# write an extra PID file
					# (change requires restart)
listen_addresses = '*'		# what IP address(es) to listen on;
					# comma-separated list of addresses;
					# defaults to 'localhost'; use '*' for all
					# (change requires restart)
port = 5432				# (change requires restart)
max_connections = 4000                  # (change requires restart)
unix_socket_directories = '/var/run/postgresql'	# comma-separated list of directories
					# (change requires restart)
					# (change requires restart)
					# (change requires restart)
					# (change requires restart)
ssl = true				# (change requires restart)
					# (change requires restart)
ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem'		# (change requires restart)
ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key'		# (change requires restart)
					# 0 selects the system default
					# 0 selects the system default
					# 0 selects the system default
shared_buffers = 16GB # min 128kB
					# (change requires restart)
					# (change requires restart)
temp_buffers = 32MB			# min 800kB
					# (change requires restart)
work_mem = 8192MB				# min 64kB
maintenance_work_mem = 8192MB		# min 1MB
max_stack_depth = 4096			# min 100kB
dynamic_shared_memory_type = posix	# the default is the first option
					# supported by the operating system:
					#   posix
					#   sysv
					#   windows
					#   mmap
					# use none to disable dynamic shared memory
					# in kB, or -1 for no limit
					# (change requires restart)
vacuum_cost_delay = 10ms			# 0-100 milliseconds
vacuum_cost_limit = 10000		# 1-10000 credits
bgwriter_delay = 10ms			# 10-10000ms between rounds
wal_level =  hot_standby  # minimal, archive, hot_standby, or logical
					# (change requires restart)
synchronous_commit = on		# synchronization level;
					# off, local, remote_write, or on
					# supported by the operating system:
					#   open_datasync
					#   fdatasync (default on Linux)
					#   fsync
					#   fsync_writethrough
					#   open_sync
					# (change requires restart)
wal_buffers = 16MB			# min 32kB, -1 sets based on shared_buffers
					# (change requires restart)
wal_writer_delay = 20ms		# 1-10000 milliseconds
checkpoint_timeout = 5min		# range 30s-1h
archive_mode = on
				# (change requires restart)
				# placeholders: %p = path of file to archive
				#               %f = file name only
				# e.g. 'test ! -f /mnt/server/archivedir/%f && cp %p /mnt/server/archivedir/%f'
				# number of seconds; 0 disables
max_wal_senders = 32		# max number of walsender processes
				# (change requires restart)
				# (change requires restart)
				# (change requires restart)
				# comma-separated list of application_name
				# from standby(s); '*' = all
hot_standby = on # "on" allows queries during recovery
					# (change requires restart)
					# when reading WAL from archive;
					# -1 allows indefinite delay
					# when reading streaming WAL;
					# -1 allows indefinite delay
					# 0 disables
					# query conflicts
					# communication from master
					# in milliseconds; 0 disables
					# retrieve WAL after a failed attempt
effective_cache_size = 20GB
					# JOIN clauses
log_destination = 'csvlog'		# Valid values are combinations of
					# stderr, csvlog, syslog, and eventlog,
					# depending on platform.  csvlog
					# requires logging_collector to be on.
logging_collector = on		# Enable capturing of stderr and csvlog

local pg_driver = require "pgmoon"
local encode = require "cjson.safe".encode
local type = type
local tostring = tostring
local setmetatable = setmetatable
local error = error
local table_concat = table.concat
local string_format = string.format

local CONNECT_TABLE = { 
        host     = "127.0.0.1", 
        port     = 5432, 
        database = "test", 
        user     = 'postgres', 
        password = '111111', }
local CONNECT_TIMEOUT = 3000
local IDLE_TIMEOUT = 10000
local POOL_SIZE = 50

local function query(statement)
    local db, res, ok, err
    db = pg_driver.new(CONNECT_TABLE) -- always success
    db:settimeout(CONNECT_TIMEOUT) 
    res, err = db:connect()
    if not res then
        return nil, err
    end
    res, err =  db:query(statement) 
    if res ~= nil then
        ok, err = db:keepalive(IDLE_TIMEOUT, POOL_SIZE)
        if not ok then
            return nil, 'fail to set_keepalive:'..err
        end
    end
    if err then
        ngx.log(ngx.ERR, statement)
    end
    return res, err
end

local function main()
    
    local res, err = query('drop table if exists t1;create table t1(id INT PRIMARY KEY NOT NULL);')
    if err then
        return ngx.say(err)
    end
    local t = {}
    for i=1,10^4 do
        local res, err = query('insert into t1 (id) values1 (1);') -- invalid syntax to make errors
        if err then
            t[err]= (t[err] or 0) + 1
        end
    end
    ngx.say(encode(t))
end

main()

worker_processes  1;
user root root;

events {worker_connections  1024;}

http {
    lua_code_cache off;
    access_log  logs/access.log;
    error_log  logs/error.log;

    client_body_temp_path tmp/client_body_temp;
    fastcgi_temp_path tmp/fastcgi_temp;
    proxy_temp_path tmp/proxy_temp;
    scgi_temp_path tmp/scgi_temp;
    uwsgi_temp_path tmp/uwsgi_temp;
    

    
    server {
        listen       8888;
        
        location / {
            content_by_lua_file pg.lua;
        }
    }
    
}

{"ERROR: syntax error at or near \"values1\" (21)":9948,"FATAL: sorry, too many clients already":52}

lua: /usr/share/lua/5.3/pgmoon/init.lua:6: module 'bit' not found:
	no field package.preload['bit']
	no file '/usr/share/lua/5.3/bit.lua'
	no file '/usr/share/lua/5.3/bit/init.lua'
	no file '/usr/lib64/lua/5.3/bit.lua'
	no file '/usr/lib64/lua/5.3/bit/init.lua'
	no file './bit.lua'
	no file './bit/init.lua'
	no file '/usr/lib64/lua/5.3/bit.so'
	no file '/usr/lib64/lua/5.3/loadall.so'
	no file './bit.so'
stack traceback:
	[C]: in function 'require'
	/usr/share/lua/5.3/pgmoon/init.lua:6: in main chunk
	[C]: in function 'require'
	pgmoon.lua:1: in main chunk
	[C]: in ?

local pg = require('pgmoon')
return pg.new({...})

local db = require('app.db')
function ware1(db)
	db:connect()
	-- do work
	db:keepalive()
end

function ware2(db)
	db:connect()
	-- do work
	db:keepalive()
end

function handler(db)
	ware1(db)
	-- do some work
	ware2(db)
	-- do more work
end

CREATE TABLE public.test_nextid
(
  id  serial,
  name character varying(255),
  desc character varying(255)
)

inert into test_nextid (name) values("testname_1")

        local sql = "select * from test_nextid"
        res,err = assert_error(db.query(sql))
        --res,err = db.select("* from test_nextid")

 local db, err = pgsql:new({database= "mqtt",
                              host = "192.168.25.100",
                              port = "5433",
                              user = "mqtt",
                              password = "mqtt123"})
    if not db then
        ngx.say("failed to instantiate pgsql: ", err)
        return
    end
    -- db:set_timeout(conf.timeout) -- 1 sec

    -- local ok, err, errno, sqlstate = db:connect(conf.pgsql)
assert(db:connect())

psql  -U mqtt -h 192.168.25.100  -p 5433 -W
Password for user mqtt: 
psql (9.3.1, server 9.4.9)
WARNING: psql major version 9.3, server major version 9.4.
         Some psql features might not work.
Type "help" for help.

mqtt=> 

lua entry thread aborted: runtime error: /usr/share/lua/5.1/pgmoon/init.lua:200: ngx.socket connect: expecting 1 ~ 5 arguments (including the object), but seen 6
stack traceback:
coroutine 0:
	[C]: in function 'send_ssl_message'
	/usr/share/lua/5.1/pgmoon/init.lua:200: in function 'connect'

...
if t == MSG_TYPE.status then
        return self.sock:sslhandshake(false, nil, self.ssl_verify, nil, self.luasec_opts)
elseif ...

Warning: Failed searching manifest: Failed extracting manifest file
Installing https://raw.githubusercontent.com/rocks-moonscript-org/moonrocks-mirror/master/pgmoon-1.2.0-1.src.rock...
Using https://raw.githubusercontent.com/rocks-moonscript-org/moonrocks-mirror/master/pgmoon-1.2.0-1.src.rock... switching to 'build' mode

Error: Failed unpacking rock file: /tmp/luarocks_luarocks-rock-pgmoon-1.2.0-1-9138/pgmoon-1.2.0-1.src.rock

local literal = function(name)
  if name == 'NULL' then return NULL end
  return name
end

local g = P{"array",
....
   literal = C((P(1) - S("},")) ^ 1) / literal,
}

init_by_lua error: /usr/local/share/lua/5.1/pgmoon/init.lua:240: attempt to call method 'send_message' (a nil value)