Re: storing zipped SQLite inside PG ?
On Tue, Dec 21, 2021 at 10:06 PM David Gauthier wrote: > I'll have to read more about sqlite_fdw. Thanks for that Steve ! > > Each SQLite isn't that big (billions of records), more like 30K records or > so. But there are lots and lots of these SQLite DBs which add up over time > to perhaps billions of records. > > This is for a big corp with an IT dept. Maybe I can get them to upgrade > the DB itself. > Thank You too David ! > >> >> So, more similar to the image storage question than I first thought, but still large enough where the specific usage patterns and needs end up being the deciding factor (keeping in mind you can pick multiple solutions - so that really old data, ideally on a partition, can be removed from the DB while still remaining accessible if just more slowly or laboriously). One possibility to consider - ditch the SQLite dependency and just store CSV (but maybe with a funky delimiter sequence). You can then us "string_to_table(...)" on that delimiter to materialize a table out of the data right in a query. David J.
Re: storing zipped SQLite inside PG ?
I'll have to read more about sqlite_fdw. Thanks for that Steve ! Each SQLite isn't that big (billions of records), more like 30K records or so. But there are lots and lots of these SQLite DBs which add up over time to perhaps billions of records. This is for a big corp with an IT dept. Maybe I can get them to upgrade the DB itself. Thank You too David ! On Tue, Dec 21, 2021 at 10:14 PM David G. Johnston < david.g.johns...@gmail.com> wrote: > On Tuesday, December 21, 2021, David Gauthier > wrote: > >> >> OK, you get the picture. I'm all ears :-) And thanks in advance for any >> suggestions ! >> > > This is basically a variant of “should I store images in the database” but > the content sizes involved are insanely high compared to most images. > External storage, with a pointer in the DB, would seem like a much better > choice. > > Either way, though, not applying a minor release in over two years is just > wrong; you should get on top of that before building new functionality on > top of said database. > > David J. > >
Re: How to confirm the pg_hba.conf service is correctly working
Please don't top post; the convention here is to inline or bottom post and trim unnecessary quoting. On Tue, Dec 21, 2021 at 9:24 PM shing dong wrote: > > - remove > yum remove postgresql* > > --- install > yum -y install > https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm > > yum install postgresql10 postgresql10-server postgresql10-contrib > postgresql10-libs postgresql10-dev* -y > Frankly, if you did this and you are still seeing errors, start over on a fresh, never hacked, machine. Test that the new machine is secure then restore your backup into it. --- > I have Check again the content of pg_hba.conf and "select * from > pg_hba_file_rules" consistent > If you want to continue having a public debugging session (on the problem machine) I suggest that you perform all queries and inspect all files using a terminal and then provide, with some light editing only, a copy of the terminal transcript for evaluation. You may wish to stand up a clean machine (VM or otherwise) for debugging, just to ensure that your experiments produce the expected results. David J.
Re: How to confirm the pg_hba.conf service is correctly working
*DEAR TOM* just one PG instance in host I did an experiment When I remove pg and reinstall pg, the function of pg_hba is working ,represent that the location of pg_hba is right - remove yum remove postgresql* --- install yum -y install https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm yum install postgresql10 postgresql10-server postgresql10-contrib postgresql10-libs postgresql10-dev* -y --- I have Check again the content of pg_hba.conf and "select * from pg_hba_file_rules" consistent Yes, this question is very tricky Tom Lane 於 2021年12月21日 週二 下午10:42寫道： > shing dong writes: > > 1. The rules in pg_hba.conf are almost invalid > > 2. pg_hba.conf is only useful for METHOD = trust > > 3. check SHOW hba_file; the file location is correct > > 4. select * from pg_hba_file_rules; checked is correct > > 5.DB version : PostgreSQL 10.19 on x86_64-pc-linux-gnu, compiled by gcc > > (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit > > Even if you delete the text in pg_hba.conf > > Keep only > > host VJ VJ_USER 10.10.10.1/32 md5 > > After pg_ctl reload and Restart DB , any ip, user still can log in to > DB > > It's hard to say where your mistake is, but probably the first > thing to check is whether you're really restarting the postmaster. > I'm wondering in particular if there's more than one PG instance > on the machine and you're reconfiguring or restarting the wrong > one. Other than that, retrace your steps carefully, because at > least one of the above statements must be wrong. > > (I guess if you were feeling *really* paranoid, you could wonder > whether somebody replaced your postmaster executable with a hacked > version that doesn't apply any pg_hba checks. But pilot error > seems like a far more probable explanation.) > > regards, tom lane >
storing zipped SQLite inside PG ?
On Tuesday, December 21, 2021, David Gauthier wrote: > > OK, you get the picture. I'm all ears :-) And thanks in advance for any > suggestions ! > This is basically a variant of “should I store images in the database” but the content sizes involved are insanely high compared to most images. External storage, with a pointer in the DB, would seem like a much better choice. Either way, though, not applying a minor release in over two years is just wrong; you should get on top of that before building new functionality on top of said database. David J.
Re: storing zipped SQLite inside PG ?
Could you use the SQLite FDW - https://github.com/pgspider/sqlite_fdw ? Steve On Wed, Dec 22, 2021 at 1:27 PM David Gauthier wrote: > Hi: I need a sanity check (brainstorming) before I jump into coding > something that might have a better solution known to a community like this > one. Here's the situation... > > To begin with... PG 11.5 on linux. > Some code (nature unimportant to this discussion) generates a lot (a lot) > of data and stuffs it in a SQLite DB which, once the process is complete, > gets zipped (compression ratio seems to be about 5). We want to keep this > data in a persistent store which others can get at should they need it. > PG seems like a great place to put this especially as the identifying > context of the SQLite already exists in our PG DB. > > So I was thinking about storing the zipped SQLite as a blob in PG. The > record it would be added to would give it all the context needed for proper > retrieval. After retrieval (in a perl script) I was thinking about writing > it out to the shell, unzipping it and then opening it using perl/DBI. The > metadata of the SQLite could be replicated in a temp table in PG and the > data content loaded into that for use. In theory, multiple SQLite DBs > could be opened like this and then loaded in the same temp tables (as long > as the metadata is the same... which it will be). > > OK, so that's the plan. What I want to ask this community about is > whether or not there's a better way to approach this. Brute force loading > the SQLite data in regular PG tables would result in billions of records > and the DB management issues that come with that. And there's really no > need to keep all that data immediately accessible like that. Is there some > sort of innate PG functionality that would allow me to store and then > compress my data for targeted retrieval/decompression (even if it's not > accessible to SQL in that form) ? > > OK, you get the picture. I'm all ears :-) And thanks in advance for any > suggestions ! >
storing zipped SQLite inside PG ?
Hi: I need a sanity check (brainstorming) before I jump into coding something that might have a better solution known to a community like this one. Here's the situation... To begin with... PG 11.5 on linux. Some code (nature unimportant to this discussion) generates a lot (a lot) of data and stuffs it in a SQLite DB which, once the process is complete, gets zipped (compression ratio seems to be about 5). We want to keep this data in a persistent store which others can get at should they need it. PG seems like a great place to put this especially as the identifying context of the SQLite already exists in our PG DB. So I was thinking about storing the zipped SQLite as a blob in PG. The record it would be added to would give it all the context needed for proper retrieval. After retrieval (in a perl script) I was thinking about writing it out to the shell, unzipping it and then opening it using perl/DBI. The metadata of the SQLite could be replicated in a temp table in PG and the data content loaded into that for use. In theory, multiple SQLite DBs could be opened like this and then loaded in the same temp tables (as long as the metadata is the same... which it will be). OK, so that's the plan. What I want to ask this community about is whether or not there's a better way to approach this. Brute force loading the SQLite data in regular PG tables would result in billions of records and the DB management issues that come with that. And there's really no need to keep all that data immediately accessible like that. Is there some sort of innate PG functionality that would allow me to store and then compress my data for targeted retrieval/decompression (even if it's not accessible to SQL in that form) ? OK, you get the picture. I'm all ears :-) And thanks in advance for any suggestions !
Re: Freeing transient memory in aggregate functions
On 21/12/2021, at 10:25 AM, Tom Lane wrote: > Not quite like that. Look into nodeAgg.c, which solves a similar problem > for the transvalues themselves with code like > >/* forget the old value, if any */ >if (!oldIsNull && !pertrans->inputtypeByVal) >pfree(DatumGetPointer(oldVal)); Oh great, thanks for the tip! — m@
Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL
út 21. 12. 2021 v 19:58 odesílatel Michael Lewis napsal: > On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule > wrote: > >> I wrote about it. Did you read this article? >> >> https://okbob.blogspot.com/2018/02/schema-variables.html >> >> The goals of this project: >> >>- fast non transactional non persistent (session based) storage, >> >> Would there be statistics that are considered in query planning, or would > that be impossible or just out of scope initially? > The session variable has no statistics - but it is used like any other external parameter - like PL/pgSQL variables. postgres=# create variable xx as int; CREATE VARIABLE postgres=# create table xxx(a int); CREATE TABLE postgres=# insert into xxx select 0 from generate_series(1,1); INSERT 0 1 postgres=# insert into xxx select 1 from generate_series(1,10); INSERT 0 10 postgres=# analyze xxx; ANALYZE postgres=# create index on xxx(a); CREATE INDEX postgres=# let xx = 1; LET postgres=# explain analyze select * from xxx where a = xx; ┌──┐ │ QUERY PLAN │ ╞══╡ │ Index Only Scan using xxx_a_idx on xxx (cost=0.29..8.46 rows=10 width=4) (actual time=0.044..0.048 rows=10 loops=1) │ │ Index Cond: (a = xx) │ │ Heap Fetches: 10 │ │ Planning Time: 0.237 ms │ │ Execution Time: 0.072 ms │ └──┘ (5 rows) postgres=# let xx = 0; LET postgres=# explain analyze select * from xxx where a = xx; ┌───┐ │ QUERY PLAN │ ╞═══╡ │ Seq Scan on xxx (cost=0.00..170.12 rows=1 width=4) (actual time=0.036..4.373 rows=1 loops=1) │ │ Filter: (a = xx) │ │ Rows Removed by Filter: 10 │ │ Planning Time: 0.281 ms │ │ Execution Time: 5.711 ms │ └───┘ (5 rows)
Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL
On Tue, Dec 21, 2021 at 11:50 AM Pavel Stehule wrote: > I wrote about it. Did you read this article? > > https://okbob.blogspot.com/2018/02/schema-variables.html > > The goals of this project: > >- fast non transactional non persistent (session based) storage, > > Would there be statistics that are considered in query planning, or would that be impossible or just out of scope initially?
Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL
út 21. 12. 2021 v 19:28 odesílatel Bryn Llewellyn
napsal:
> *pavel.steh...@gmail.com wrote:*
>
>
> *b...@yugabyte.com wrote:*
>
> I’m still hoping that I might get some pointers to whitepapers or blog
> posts that expand on those bullets that I quoted from the PG doc: «Instead
> of packages, use schemas to organize your functions into groups.» and
> «Since there are no packages, there are no package-level variables either.
> This is somewhat annoying. You can keep per-session state in temporary
> tables instead.»
>
>
> I fixed a patch https://commitfest.postgresql.org/36/1608/ so you can
> check it.
>
> Using temporary tables instead of session variables is not too practical.
> There are more alternative ways - a) one extension, b) using global
> variables from Perl, c) using global configuration variables. The @c is
> most common today
>
> http://okbob.blogspot.com/2021/06/current_setting
>
>
> Do you have a plain English account of what your patch will bring for the
> application programmer?
>
>
I wrote about it. Did you read this article?
https://okbob.blogspot.com/2018/02/schema-variables.html
The goals of this project:
- fast non transactional non persistent (session based) storage,
- possibility to control access to stored data with PostgreSQL
GRANT/REVOKE commands - schema variable can be filled by security definer
function, and anywhere in session can be read, but cannot be changed,
- possibility to share data between different PostgreSQL environments
(client side, server side, PL/Python, PL/Perl, ...)
- possibility to have an analogy of package variables for PLpgSQL,
- it should not block a possibility to check PLpgSQL code by
plpgsql_check.
> Your current_setting blog post shows me that you use this:
>
> pts := current_setting('tps.ts', true)::timestamp with time zone;
>
> Is that your point? I so, then thanks. Yes, I’ve used this technique
> myself.
>
The advantage of my implementation against GUC is:
a) performance and correctness - session variables are typed and stored
in binary format, GUC is just text, data should be converted every time
b) possibility to set secure access,
c) persistence in schema (data are not persistent)
d) more comfortable work - there is not necessary to use helper functions
The advantages/disadvantage against PL/SQL package variables is deeper
integration with SQL engine and generally missing schema private objects.
But this is not supported by Postgres yet, and this needs to be supported
by Postgres, if we want to use this feature from PL/pgSQL.
There is an important difference between PL/SQL and PL/pgSQL. PL/SQL is an
independent environment with possibility to embedded SQL. PL/pgSQL is just
glue for SQL - any expression in PL/pgSQL is SQL expression and it is
evaluated by SQL engine. So everything supported in PL/pgSQL has to be
supported by SQL engine.
Regards
Pavel
Re: Packages, inner subprograms, and parameterizable anonymous blocks for PL/pgSQL
> pavel.steh...@gmail.com wrote:
>
>> b...@yugabyte.com wrote:
>>
>> I’m still hoping that I might get some pointers to whitepapers or blog posts
>> that expand on those bullets that I quoted from the PG doc: «Instead of
>> packages, use schemas to organize your functions into groups.» and «Since
>> there are no packages, there are no package-level variables either. This is
>> somewhat annoying. You can keep per-session state in temporary tables
>> instead.»
>
> I fixed a patch https://commitfest.postgresql.org/36/1608/ so you can check
> it.
>
> Using temporary tables instead of session variables is not too practical.
> There are more alternative ways - a) one extension, b) using global variables
> from Perl, c) using global configuration variables. The @c is most common
> today
>
> http://okbob.blogspot.com/2021/06/current_setting
Do you have a plain English account of what your patch will bring for the
application programmer?
Your current_setting blog post shows me that you use this:
pts := current_setting('tps.ts', true)::timestamp with time zone;
Is that your point? I so, then thanks. Yes, I’ve used this technique myself.
Re: How to confirm the pg_hba.conf service is correctly working
shing dong writes: > 1. The rules in pg_hba.conf are almost invalid > 2. pg_hba.conf is only useful for METHOD = trust > 3. check SHOW hba_file; the file location is correct > 4. select * from pg_hba_file_rules; checked is correct > 5.DB version : PostgreSQL 10.19 on x86_64-pc-linux-gnu, compiled by gcc > (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit > Even if you delete the text in pg_hba.conf > Keep only > host VJ VJ_USER 10.10.10.1/32 md5 > After pg_ctl reload and Restart DB , any ip, user still can log in to DB It's hard to say where your mistake is, but probably the first thing to check is whether you're really restarting the postmaster. I'm wondering in particular if there's more than one PG instance on the machine and you're reconfiguring or restarting the wrong one. Other than that, retrace your steps carefully, because at least one of the above statements must be wrong. (I guess if you were feeling *really* paranoid, you could wonder whether somebody replaced your postmaster executable with a hacked version that doesn't apply any pg_hba checks. But pilot error seems like a far more probable explanation.) regards, tom lane
How to confirm the pg_hba.conf service is correctly working
Hello all A while ago, our company had an Postgres DB that was Hacked login db to modify data found that pg_hba.conf is not work Any ip, user can log in to DB 1. The rules in pg_hba.conf are almost invalid 2. pg_hba.conf is only useful for METHOD = trust 3. check SHOW hba_file; the file location is correct 4. select * from pg_hba_file_rules; checked is correct 5.DB version : PostgreSQL 10.19 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit Even if you delete the text in pg_hba.conf Keep only host VJ VJ_USER 10.10.10.1/32 md5 After pg_ctl reload and Restart DB , any ip, user still can log in to DB Please help how to check whether the pg_hba.conf service is defective If you need any information, I will provide it Thanks
