leafo / pgmoon Goto Github PK
View Code? Open in Web Editor NEWA pure Lua Postgres driver for use in OpenResty & more
License: MIT License
A pure Lua Postgres driver for use in OpenResty & more
License: MIT License
local encode_array = require("pgmoon.arrays").encode_array
local my_array = {“abcd”,“1234”}
pg:query("insert into some_table (text_array_column) values(" .. pg:escape_literal(encode_array(my_array)) .. ")")
In some situations i need to specify SSL protocol (e.g. tlsv1_2).
Now this parameter is hardcoded to tlsv1.
I defined many functions which return value is jsonb data type in postgresql. How to call pg's udf and handle jsonb return value in pgmoon?
I see version 1.8.0 in main branch, but I use opm get that is version 1.7.0, is it the latest stable version?
cannot connect database when use hostname, such as:
local pg = pgmoon.new({
host = "server1",
port = "5432",
database = "mydb",
user = "postgres"
})
Any time i use pg:connect() on Openresty this pops up on postgres log:
2014-09-16 17:07:34 UTC LOG: invalid password packet size
Not sure how it affects though since the queries still work.
edit: i have the default md5 authentication in use
I am using pgmoon in the context of a Lapis app deployed on Heroku. Postgres databases hosted by Heroku require SSL encryption for remote TCP connections.
Investigating possible fixes, I learned that ngx.socket.tcp does support SSL connections. It was used last year to add support for SSL connections in lua-resty-mysql (see here). Is this a feature that can be added to pgmoon too?
I'm not sure if this is intentional, but encode_array
simply returns "]" when the passed in table is empty, which causes an error in Postgresql.
I came across this error when trying to update a Postgresql array column in lapis:
-- ...
import array from require "lapis.db"
class Something extends Model
some_method: =>
@the_arr_column = array { } -- "["
@update "the_arr_column" -- syntax error because "["
Instead, I had to do something like this:
some_method: =>
do_something!
-- ...
-- cannot do:
-- @the_arr_column = array new_arr_column
-- workaround: we know it's empty, but encode_array can't take empty tables.
@the_arr_column = "{ }" if #new_arr_column == 0
@update "the_arr_column"
It seems that pgmoon lacks Lua API for setting the timeout threshold?
Hi,
I wonder if I do:
pgmoon = require('pgmoon')
in init_by_lua_block,
and then in a location handler, use content_by_lua_file with
local pg = pgmoon.new({...})
-- Do work
pg:keepalive()
Will I get the same pool shared among all workers ?
High loads and slow db responses can generate a large number of connections. There should be a limit of simultaneous db connections.
Please, include a license file with your project containing the full text of the MIT license. This is required by the MIT license itself.
For more info and examples: https://en.wikipedia.org/wiki/MIT_License
It is no longer possible to "require" pgmoon outside openresty. The error I get is:
loop or previous error loading module 'pgmoon'
stack traceback:
[C]: in function 'require'
./pgmoon.lua:3: in main chunk
[C]: in function 'require'
stdin:1: in main chunk
[C]: ?
Hello,
Is it possible to add support for http://www.postgresql.org/docs/9.4/static/sql-listen.html ?
The use case might be:
Thank you !
the opts argument to sslhandshake is ignored.
Support for certificate authentication would be nice.
I came across a blog post: http://mansion.im/2014/speed-comparison-of-postgresql-modules-for-nginx-openresty/ showing performance comparisons.
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.
Have you done performance analysis?
The default (at least on Fedora 23) settings for pg_hba.conf
do not work with pgmoon.
# 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
had to be set to:
# 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
Hello,here my postgresql conf.
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
016-12-20 11:14:09 CST [24633-1] [unknown]@[unknown] LOG: incomplete startup packet
2016-12-20 11:14:09 CST [25669-1] mqtt@test_mqtt FATAL: sorry, too many clients already
2016-12-20 11:14:09 CST [25667-1] mqtt@test_mqtt FATAL: remaining connection slots are reserved for non-replication superuser connections
2016-12-20 11:14:09 CST [25668-1] mqtt@test_mqtt FATAL: remaining connection slots are reserved for non-replication superuser connections
2016-12-20 11:14:09 CST [25670-1] mqtt@test_mqtt FATAL: remaining connection slots are reserved for non-replication superuser connections
In production, I insert 100K+items and too many clients already
error happens. I want to know:
Below is minimum example here . just clone it , config the database and run mkdir logs tmp && nginx -c nginx.conf -p .
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;
}
}
}
the browser output:
{"ERROR: syntax error at or near \"values1\" (21)":9948,"FATAL: sorry, too many clients already":52}
I'm trying to run pgmoon on a Lua 5.3 installation, and I receive the following:
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 ?
I think the code should be tweaked to accept the external bit library (for Lua 5.1 and Lua 5.2) and the internal one for Lua 5.3
This used to work in 1.6 but no longer in 1.7 or 1.8, with error
.../pgmoon/init.lua:194: attempt to index field 'sock' (a nil value)
File app/db.lua
local pg = require('pgmoon')
return pg.new({...})
File app/handler.lua
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
I found that the self.sock handle is set to nil after keepalive().
Can we call connect() after keepalive() if they are within the same request?
Thanks!
My table has three column
CREATE TABLE public.test_nextid
(
id serial,
name character varying(255),
desc character varying(255)
)
And i insert one recorder
inert into test_nextid (name) values("testname_1")
And i select all data from the table
local sql = "select * from test_nextid"
res,err = assert_error(db.query(sql))
--res,err = db.select("* from test_nextid")
But there is no desc in result....
I want to see the "desc " column in the result,what should i do? thank u
Why not try to convert lua's table into JSON object? Especially since pgmoon can deserialize data from JSON field.
I'm not an expert at PostgreSQL, but shouldn't backslashes also be doubled too, to prevent an attacker from escaping ''
with \''
?
Line 415 in 5e3ce03
Hi,this is a connect db lua code. connect is refused , but use psql connect is worked.
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())
at same host , I use psql connect db is worked.
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=>
my application connects to several PostgreSQL scheme or database according to client's choice. If I used pg:keepalive() in my codes, and client visit different scheme almost at the same time, pgmoon does not switch to right scheme, still connect to last scheme.
below is some of my config(different user use differnt scheme which is decided by postgresql database):
{
"id": 1,
"host": "127.0.0.1",
"port": "5432",
"database": "mydb",
"user": "develop",
"password": "123456"
},
{
"id": 2,
"host": "127.0.0.1",
"port": "5432",
"database": "mydb",
"user": "test",
"password": "123456"
}
below is my code:
`local g_vars = require("global_vars")
local pg_conf = g_vars.get_server_unit(ngx.var.db_id)
if not pg_conf then
ngx.say([[{"return_code": -1, "error_message": "db_id is wrong"}]])
return
end
local pgmoon = require("pgmoon")
local pg = pgmoon.new(pg_conf)
if not pg:connect() then
ngx.say([[{"return_code": -2, "error_message": "fail to connect database"}]])
return
end
local result_obj = pg:query("SELECT get_classes() AS result")
pg:keepalive()
local json = require "cjson.safe"
local result_str = json.encode(result_obj[1].result)
ngx.say(result_str)`
Hi,
Is it possible for pgmoon to support postgresql's "connection service" files[1]? I am hoping to use this to separate the database configuration from our applications configuration, which would simplify some things for some of our developers.
[1]
https://www.postgresql.org/docs/9.4/static/libpq-pgservice.html
https://www.postgresql.org/docs/9.4/static/libpq-connect.html#LIBPQ-PARAMKEYWORDS
When I run INSERT or UPDATE sql with returning some columns, the result only contains affected_rows, I cannot find returned columns in my sql.
such as:
update t1 set name='b' where id=1 returning id,name;
Hi, @leafo! Thanks for ur work!
Here is a little error, i've found in openresty/ssl environment:
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'
As i can see, there is no check for environment in which script is runing, so you always pass a luasec_opt to self.sock:sslhandshake. But, thats not correct for ngx.socket .
Error is on pgmoon/init.lua#L579:
...
if t == MSG_TYPE.status then
return self.sock:sslhandshake(false, nil, self.ssl_verify, nil, self.luasec_opts)
elseif ...
Because I want to include pgmoon in my project for portability.
Can't figure out if it's possible to set pg connection pool limit for pgmoon?
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
Apologies i'm new to lua, so this may be some other issue. But any help would be great.
Arrays like {NULL,"NULL"}
should be handeled to `{pg.NULL, 'NULL'}.
I use yours lpeg parser with simple patch.
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,
}
Test case: sql - select ARRAY[NULL,'1','NULL1','NULL',NULL,'2',NULL]
, result in text mode {NULL,1,NULL1,"NULL",NULL,2,NULL}
I think same cahnges need in Array building.
So I can't query tables which has a array field.
errormodule 'lpeg' not found
Am I correct in seeing that pgmoon doesn't support parameterized queries?
e.g. pg:query("SELECT * FROM foo WHERE bar = ?", somevalue)
Using pgmoon in certain contexts, like init_by_lua, doesn't work because cosockets aren't available there.
https://www.nginx.com/resources/wiki/modules/lua/#cosockets-not-available-everywhere
Would it be worthwhile to check the context in socket.lua/socket.moon? The below has worked for me.
if ngx and ngx.get_phase ~= nil and ngx.get_phase() ~= "init" then
Pgmoon should add the Pg database name to the default connection pool name, just like the lua-resty-mysql library:
https://github.com/openresty/lua-resty-mysql/blob/master/lib/resty/mysql.lua#L514
Otherwise, when the app (or multiple apps) running in the same OpenResty, one app would get Pg connections associated with the wrong database (used by another app or other parts of the same app) from the shared connection pool.
Hello.
First I would like to apologize for my English :)
I have an issue using this module:
when i'm trying to access stored function returning value:
local res, err = pg:query("select * from test()")
ngx.say(tostring(res))
I'm get the following result
--> table: 0x416ad5f0
how can I fix it?
regards
@leafo
Summary :
There is a problem found on ARM platform that is when postgres table contains the type of array, when execute SQL statement query, there is a "Error: bad light userdata pointer" error occur. I try to find the cause of the problem, it seems that the lpeg.so library not work correctly on ARM platform. in the file pgmoom/array.lua , the code as showing below, the problem occurs on the line code "local out = (assert(g:match(str), "failed to parse postgresql array"))", the invocation seems not return.
do
local P, R, S, V, Ct, C, Cs
do
local _obj_0 = require("lpeg")
P, R, S, V, Ct, C, Cs = _obj_0.P, _obj_0.R, _obj_0.S, _obj_0.V, _obj_0.Ct, _obj_0.C, _obj_0.Cs
end
local g = P({
"array",
array = Ct(V("open") * (V("value") * (P(",") * V("value")) ^ 0) ^ -1 * V("close")),
value = V("invalid_char") + V("string") + V("array") + V("literal"),
string = P('"') * Cs((P([[\]]) / [[]] + P([["]]) / [["]] + (P(1) - P('"'))) ^ 0) * P('"'),
literal = C((P(1) - S("},")) ^ 1),
invalid_char = S(" \t\r\n") / function()
return error("got unexpected whitespace")
end,
open = P("{"),
delim = P(","),
close = P("}")
})
decode_array = function(str, convert_fn)
local out = (assert(g:match(str), "failed to parse postgresql array")) --- the problem occurs on this line
setmetatable(out, PostgresArray.__base)
if convert_fn then
return convert_values(out, convert_fn)
else
return out
end
end
end
Also, the discuss of the similar problem about Openresty can see here, it maybe helpful: openresty/lua-nginx-module#1152
Any suggestion, thanks!
When my server‘s memory is exhausted, pgmoon report below errors:
attempt to get length of local 'err_msg' (a nil value)
2018/08/09 16:15:33 [error] 15433#15433: *9161 recv() failed (104: Connection reset by peer), client: 127.0.0.1, server: localhost, request: "POST /api/test HTTP/1.0", host: "xxx.com"
2018/08/09 16:15:33 [error] 15433#15433: *9161 lua entry thread aborted: runtime error: /usr/local/openresty/site/lualib/pgmoon/init.lua:390: attempt to get length of local 'err_msg' (a nil value)
stack traceback:
coroutine 0:
/usr/local/openresty/site/lualib/pgmoon/init.lua: in function 'parse_error'
/usr/local/openresty/site/lualib/pgmoon/init.lua:243: in function 'auth'
/usr/local/openresty/site/lualib/pgmoon/init.lua:211: in function 'connect'
I use lapis with pgmoon, and I have a table like this
create table visits (id bigint primary key, ua varchar(255));
store one record
visit = Visits\create { id: '811205756297019392', ua: ngx.var.http_user_agent }
fetch the record
v = Visits\find '811205756297019392'
print v.id -- 8.1120575629702e+17
I try same code with mysql, it's fine.
So, pgmoon is pure lua(jit), has not int64 support?
thanks.
In reviewing the documentation and code I cannot see how Unix-domain sockets (https://www.postgresql.org/docs/9.5/libpq-connect.html#AEN43017) are supported.
#19 implies it may be supported.
Is it currently supported? If not, would you consider adding support? How can I help?
Would it make sense to implement a feature so that a client can read from a slave node, but write to a different master node?
In a master/slave Postgres setup this would effectively cover the following use-cases:
I got on my database the following value 1026066510839283908
however pgmoon
is returning instead 1.0260665108393e+18
postgres data type for my field is int8
What could be happening?
such as pool's size, min and max value, keepalive time, etc.
To support JSONB (new type added in postgresql 9.4) you need to add to PG_TYPES table this value:
[3802] = "json"
pgmoon is able to connect fine, but sending a query results in:
init_by_lua error: /usr/local/share/lua/5.1/pgmoon/init.lua:240: attempt to call method 'send_message' (a nil value)
String arrays with values that have leading numbers e.g. "1one" are throwing "failed to parse postgresql array".
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.