I got the following after running postlite for a few minutes on go1.21.1-linux-armv6l

runtime: out of memory: cannot allocate 301989888-byte block (3853713408 in use)
fatal error: out of memory

Using TablePlus client connecting does not show the databases or if directly input the database name it doesn't show any tables.
PSQL command line does work fine.

Latest version of tableplus

[recv] &pgproto3.Query{String:"SELECT nspname FROM pg_catalog.pg_namespace;"} received query: "SELECT nspname FROM pg_catalog.pg_namespace;" [recv] &pgproto3.Query{String:"SELECT pg_catalog.pg_get_userbyid(p.proowner) as owner,p.oid AS oid,pg_get_function_identity_arguments(p.oid)AS args,n.nspname AS function_schema,p.proname AS function_name,CASE WHEN p.proisagg THEN'aggregate'WHEN p.proiswindow THEN'window'WHEN p.prorettype='pg_catalog.trigger'::pg_catalog.regtype THEN'trigger'ELSE'function'END AS function_type FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname<>'pg_catalog'AND n.nspname<>'information_schema';"} received query: "SELECT pg_catalog.pg_get_userbyid(p.proowner) as owner,p.oid AS oid,pg_get_function_identity_arguments(p.oid)AS args,n.nspname AS function_schema,p.proname AS function_name,CASE WHEN p.proisagg THEN'aggregate'WHEN p.proiswindow THEN'window'WHEN p.prorettype='pg_catalog.trigger'::pg_catalog.regtype THEN'trigger'ELSE'function'END AS function_type FROM pg_catalog.pg_proc p LEFT JOIN pg_catalog.pg_namespace n ON n.oid=p.pronamespace WHERE n.nspname<>'pg_catalog'AND n.nspname<>'information_schema';" [recv] &pgproto3.Query{String:"(SELECT table_name, table_schema, table_type FROM information_schema.tables) UNION (SELECT c.relname AS table_name, n.nspname AS table_schema, 'MATERIALIZED VIEW' FROM pg_catalog.pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'm');"} received query: "(SELECT table_name, table_schema, table_type FROM information_schema.tables) UNION (SELECT c.relname AS table_name, n.nspname AS table_schema, 'MATERIALIZED VIEW' FROM pg_catalog.pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace WHERE c.relkind = 'm');" [recv] &pgproto3.Query{String:"SELECT p.oid AS oid, p.relname AS table_name, n.nspname as table_schema FROM pg_class AS p JOIN pg_namespace AS n ON p.relnamespace=n.oid WHERE p.relkind='r';"} received query: "SELECT p.oid AS oid, p.relname AS table_name, n.nspname as table_schema FROM pg_class AS p JOIN pg_namespace AS n ON p.relnamespace=n.oid WHERE p.relkind='r';"

Is it possible to use Postgres functions?

👋

Really cool project! I'm not sure if you need to parse any of the incoming queries, but you can use the official PostgreSQL parser easily from Go: https://github.com/pganalyze/pg_query_go

I use this project to power PostgreSQL support in https://github.com/kyleconroy/sqlc

I get the feeling this is designed to work with fly.io. Could you let us know the intention .

FerretDB is a proxy to replace MongoDB with FerretDB + Postgres, because ewwww MongoDB.

But I wanted to be more lightweight so wanted to replace Postgres with something like postlite in this equation so I can use FerretDB + postlite as a drop in replacement for MongoDB.

I saw you said to raise an issue for clients you'd like so see.... so this is me, raising an issue :)

When using the PostgreSQL JDBC driver via Datagrip IDE, the following happens:

[recv] &pgproto3.Parse{Name:"", Query:"select ssl from pg_stat_ssl where pid = pg_backend_pid()", ParameterOIDs:[]uint32(nil)}
connection error, closing: parse message: prepare: no such table: pg_stat_ssl

I also get an Connection was established but closed as invalid message from Datagrip

Full log from start to error:

listening on :5432
connection accepted:  127.0.0.1:41900
received startup message: &pgproto3.StartupMessage{ProtocolVersion:0x30000, Parameters:map[string]string{"DateStyle":"ISO", "TimeZone":"UTC", "client_encoding":"UTF8", "database":"cache.db", "extra_float_digits":"2", "user":"jason"}}
[recv] &pgproto3.Parse{Name:"", Query:"SET extra_float_digits = 3", ParameterOIDs:[]uint32(nil)}
query rewrite: SELECT 'SET'
[recv(p)] &pgproto3.Bind{DestinationPortal:"", PreparedStatement:"", ParameterFormatCodes:[]int16(nil), Parameters:[][]uint8(nil), ResultFormatCodes:[]int16{}}
[recv(p)] &pgproto3.Execute{Portal:"", MaxRows:0x1}
[recv] &pgproto3.Sync{}
[recv] &pgproto3.Parse{Name:"", Query:"SET application_name = ''", ParameterOIDs:[]uint32(nil)}
query rewrite: SELECT 'SET'
[recv(p)] &pgproto3.Bind{DestinationPortal:"", PreparedStatement:"", ParameterFormatCodes:[]int16(nil), Parameters:[][]uint8(nil), ResultFormatCodes:[]int16{}}
[recv(p)] &pgproto3.Execute{Portal:"", MaxRows:0x1}
[recv] &pgproto3.Sync{}
[recv] &pgproto3.Parse{Name:"", Query:"select version()", ParameterOIDs:[]uint32(nil)}
[recv(p)] &pgproto3.Bind{DestinationPortal:"", PreparedStatement:"", ParameterFormatCodes:[]int16(nil), Parameters:[][]uint8(nil), ResultFormatCodes:[]int16{}}
[recv(p)] &pgproto3.Describe{ObjectType:0x50, Name:""}
[recv(p)] &pgproto3.Execute{Portal:"", MaxRows:0x0}
[recv] &pgproto3.Sync{}
[recv] &pgproto3.Parse{Name:"", Query:"SET application_name = 'WebStorm 2023.2.1'", ParameterOIDs:[]uint32(nil)}
query rewrite: SELECT 'SET'
[recv(p)] &pgproto3.Bind{DestinationPortal:"", PreparedStatement:"", ParameterFormatCodes:[]int16(nil), Parameters:[][]uint8(nil), ResultFormatCodes:[]int16{}}
[recv(p)] &pgproto3.Execute{Portal:"", MaxRows:0x1}
[recv] &pgproto3.Sync{}
[recv] &pgproto3.Parse{Name:"", Query:"", ParameterOIDs:[]uint32(nil)}
[recv(p)] &pgproto3.Bind{DestinationPortal:"", PreparedStatement:"", ParameterFormatCodes:[]int16(nil), Parameters:[][]uint8(nil), ResultFormatCodes:[]int16{}}
[recv(p)] &pgproto3.Describe{ObjectType:0x50, Name:""}
[recv(p)] &pgproto3.Execute{Portal:"", MaxRows:0x1}

This project looks very interesting and promising! Thanks for bringing this idea out.

I have a couple of questions after going through the README.

• Is this only targeted at making remote management easier, or can it be used as a drop in replacement for Postgres?
• How would the performance be like?
• Can multiple clients connect and issue transactions?
• How is user authorization taken care of?

Sqlite does not perform well over NFS due to file locking issues that is present with NFS. Using postlite on the NFS server to mitigate that problem would be a humongous value addition from this project.
Edit: I see that you also have worked on Litestream! You don't need me to tell you this :)

If it can perform reasonably well (not expecting production grade due to the backing store being sqlite), I can see this being used as a central database server (or even a DBaaS) for many homelabs too :)

This is a very slick little utility - I have a similar need ... accessing osQuery endpoints. osQuery has its own, special, access points but it would much nicer to have a PostgreSQL wire compatible interface to osQuery endpoints.

Since osQuery is built on top of SQLite, does anyone know if a PostgreSQL wire compatible interface to osQuery endpoints is possible with postlite?

super minor issue, but it looks like postlite will simply log an error when it receives an unexpected message type. I saw elsewhere in the code that you already have a pattern for sending errors to the client, so maybe this would be a good candidate for something worth sending errors to the client about? Otherwise, I'm pretty sure the connection will get into an inconsistent state where the client is stuck waiting for a response, and the end user will likely be very confused.

(edit: I see that the connection will be terminated, which at least helps a bit)

https://github.com/benbjohnson/postlite/blob/main/server.go#L239

Hi and thanks for a great project. I tried using postlite/sqlite when running some integration tests locally. Sadly they fail because postlite does not support the pg_range catalog table. Would it be possible to add this feature?

received ssl request message: &pgproto3.SSLRequest{}
received startup message: &pgproto3.StartupMessage{ProtocolVersion:0x30000, Parameters:map[string]string{"database":"data.db3", "user":"postgres"}}
[recv] &pgproto3.Query{String:"SET DateStyle TO 'ISO, MDY'"}
received query: "SET DateStyle TO 'ISO, MDY'"
[recv] &pgproto3.Terminate{}
connection closed: xxxx

Postico 2 (2.0beta (9418)) does not work with postlite. The error is: no such function: current_schemas

The failing query:

SELECT
	oid
	,nspname
	,nspname = ANY (current_schemas(true)) AS is_on_search_path

	 ,obj_description(oid, 'pg_namespace') AS comment

FROM pg_namespace;

Connecting with psql works fine.

can't connect with dbeaver postgres - can connect with psql

dbeaver config: localhost, 5432, nc_timebilling.db

error:
can't connect with dbeaver postgres - no databases found on the server

works:
$ psql --host localhost nc_timebilling.db

Hi,

I'm likely doing something wrong, but unable to build.

    ~/code/postlite  on   main  go install -tags vtable ./cmd/postlite                                     
    ~/code/postlite  on   main  go build                                                                    
# github.com/benbjohnson/postlite
./pg_class.go:11:79: undefined: sqlite3.VTab
./pg_class.go:54:80: undefined: sqlite3.VTab
./pg_class.go:62:40: undefined: sqlite3.VTabCursor
./pg_class.go:66:48: undefined: sqlite3.InfoConstraint
./pg_class.go:66:77: undefined: sqlite3.InfoOrderBy
./pg_class.go:66:100: undefined: sqlite3.IndexResult
./pg_database.go:11:82: undefined: sqlite3.VTab
./pg_database.go:35:83: undefined: sqlite3.VTab
./pg_database.go:43:43: undefined: sqlite3.VTabCursor
./pg_database.go:47:51: undefined: sqlite3.InfoConstraint
./pg_database.go:47:51: too many errors

Any hints appreciated.

Thannks!

I want to use redash without PostgreSQL backend.
redash use psycopg2 for PostgreSQL driver.
I would be very happy if postlite supports psycopg2.

Here is redash log, use postlite instead of postgres.

 [2023-02-25 11:23:55,763] ERROR in app: Exception on /login [GET]
 Traceback (most recent call last):
   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1982, in wsgi_app
     response = self.full_dispatch_request()
   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1614, in full_dispatch_request
     rv = self.handle_user_exception(e)
   File "/usr/local/lib/python2.7/site-packages/flask_restful/__init__.py", line 271, in error_router
     return original_handler(e)
   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1517, in handle_user_exception
     reraise(exc_type, exc_value, tb)
   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1612, in full_dispatch_request
     rv = self.dispatch_request()
   File "/usr/local/lib/python2.7/site-packages/flask/app.py", line 1598, in dispatch_request
     return self.view_functions[rule.endpoint](**req.view_args)
   File "/usr/local/lib/python2.7/site-packages/flask_limiter/extension.py", line 442, in __inner
     return obj(*a, **k)
   File "/app/redash/handlers/authentication.py", line 147, in login
     if current_org == None and not settings.MULTI_ORG:
   File "/usr/local/lib/python2.7/site-packages/werkzeug/local.py", line 365, in <lambda>
     __eq__ = lambda x, o: x._get_current_object() == o
   File "/usr/local/lib/python2.7/site-packages/werkzeug/local.py", line 302, in _get_current_object
     return self.__local()
   File "/app/redash/authentication/org_resolving.py", line 18, in _get_current_org
     g.org = Organization.get_by_slug(slug)
   File "/app/redash/models/organizations.py", line 33, in get_by_slug
     return cls.query.filter(cls.slug == slug).first()
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2895, in first
     ret = list(self[0:1])
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2687, in __getitem__
     return list(res)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 2995, in __iter__
     return self._execute_and_instances(context)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3016, in _execute_and_instances
     close_with_result=True)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3025, in _get_bind_args
     **kw
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/query.py", line 3007, in _connection_from_session
     conn = self.session.connection(**kw)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1035, in connection
     execution_options=execution_options)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 1040, in _connection_for_bind
     engine, execution_options)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/orm/session.py", line 409, in _connection_for_bind
     conn = bind.contextual_connect()
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2123, in contextual_connect
     self._wrap_pool_connect(self.pool.connect, None),
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2162, in _wrap_pool_connect
     e, dialect, self)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1476, in _handle_dbapi_exception_noconnection
     exc_info
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/compat.py", line 265, in raise_from_cause
     reraise(type(exception), exception, tb=exc_tb, cause=cause)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 2158, in _wrap_pool_connect
     return fn()
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 400, in connect
     return _ConnectionFairy._checkout(self)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 788, in _checkout
     fairy = _ConnectionRecord.checkout(pool)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 529, in checkout
     rec = pool._do_get()
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1193, in _do_get
     self._dec_overflow()
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 66, in __exit__
     compat.reraise(exc_type, exc_value, exc_tb)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 1190, in _do_get
     return self._create_connection()
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 347, in _create_connection
     return _ConnectionRecord(self)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 474, in __init__
     self.__connect(first_connect_check=True)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/pool.py", line 671, in __connect
     connection = pool._invoke_creator(self)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/strategies.py", line 106, in connect
     return dialect.connect(*cargs, **cparams)
   File "/usr/local/lib/python2.7/site-packages/sqlalchemy/engine/default.py", line 412, in connect
     return self.dbapi.connect(*cargs, **cparams)
   File "/usr/local/lib/python2.7/site-packages/psycopg2/__init__.py", line 130, in connect
     conn = _connect(dsn, connection_factory=connection_factory, **kwasync)
 OperationalError: (psycopg2.OperationalError) server didn't return client encoding (Background on this error at: http://sqlalche.me/e/e3q8)
 [2023-02-25 11:23:55,772][PID:14][INFO][metrics] method=GET path=/login end

Here is my investigation repo.
https://github.com/watarukura/redash-postlite
Clone and docker compose up and open http://localhost, you can see error log.

psql password authentication function is required.
Are there any plans to add this feature?

sorry for poor english

Please ask if you need any more information