Re: [GENERAL] Spelling dictionaries
On Fri, Nov 10, 2017 at 10:16:06AM +0100, hmidi slim wrote: > Hi, > I'm using Full Text search and I'm interested on using spelling dictionary. > I found a website that has a special functionality, for example when I > enter the word 'activities' it gives me the suggestions containing this > word. But when I enter the word 'activityns' it returns 'No result for > 'activityns'' But it gives me some suggestions contained the word > 'activities'. > In cas I entered the word 'activityng' it returns the same message 'No > result for 'activityng' ' But I got some suggestions containing the word > 'activity'. > Does this the behaviour of the lspell dictionary, because I didn't > understand how does he give suggestions when the word is wrong. You can try pg_trgm extension [1]. Also we have a little demo web-site [2]. It uses pg_trgm. You can look at its code [3]. 1 - https://www.postgresql.org/docs/current/static/pgtrgm.html#idm4642863576 2 - http://tsdemo.postgrespro.ru/search?pattern=Galaksy=rank_func=ts_rank 3 - https://github.com/postgrespro/apod_fts/blob/master/apod.py -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_audit to mask literal sql
On Mon, Oct 30, 2017 at 10:55:17AM -0700, rakeshkumar464 wrote: > Is there a way in pgaudit to mask literal sqls like the below: > > insert into table (col1,col2) values(1,2) > select * from table where col1 = 1 > > These sqls are typed by our QA folks using pgadmin. pgaudit records this > verbatim which runs afoul of our HIPAA requirement. Prepared statements are > not an issue since pgaudit provides a way to suppress values. > I doubt that. But I'm not sure that I understood you correctly. What do you mean by "mask"? Some additional examples may be useful too. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tgrm index for word_similarity
On Sat, Oct 21, 2017 at 10:01:56PM -0700, Igal @ Lucee.org wrote: > > 1) I thought that the whole idea behind indexes on expressions is that the > index would be used in a WHERE clause? See > https://www.postgresql.org/docs/10/static/indexes-expressional.html - Am I > missing something? > I think the idea is a little bit different. It is about computing index entries only once, during index creation. During scan PostgreSQL doesn't compute such entries every time. I am not very good at PostgreSQL's planner. But I know that PostgreSQL uses index scan for pg_trgm only with %, <%, ~~, ~~*, ~, ~* operators. pg_trgm's operator classes (which should be implemented for index scan) are designed in this way. > 2) A query with `WHERE input <% name` utilizes the index, but a query > without a WHERE clause at all does not? Because sequential scan is cheaper here than index scan. > > 3) What happens if I do not create an index at all? Does the query that I > run in 30 - 40ms, the one that does not utilize an index, creates all of the > tri-grams on the fly each time that it runs? Would it be possible for me to > create a TABLE or a VIEW with the tri-grams so that there is no need to > create them each time the query runs? > As far as I know you can't do it nowadays. You can't create an trigram column, as you can do it for FTS, you can create an tsvector column. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to get login user name and host name in pgaudit
On Sun, Oct 22, 2017 at 05:32:56AM -0700, rakeshkumar464 wrote: > I installed latest pgaudit (1.2) with pg10. I am testing it and I see that > it does not log the login user name and host name. > > For example, if user mary is running select * from sensitive_table, I want > Mary and the machine from where she ran in the log. > > It seems to log the ids which needs to be joined with pg_ views to convert > it into login user name and host name. > > any pointers on how to get it done. > > thanks. > According to the README [1] you need to set the log_line_prefix GUC variable [2]. It's default value is '%m [%p]'. For example: =# alter system set log_line_prefix to '%m [%p] %u %h'; =# select pg_reload_conf(); This GUC variable will change all log lines of PostgreSQL, not only pgaudit's. 1 - https://github.com/pgaudit/pgaudit#format 2 - https://www.postgresql.org/docs/10/static/runtime-config-logging.html#guc-log-line-prefix -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] tgrm index for word_similarity
On Thu, Oct 19, 2017 at 04:54:19PM -0700, Igal @ Lucee.org wrote:
>
> My query at the moment is:
>
> SELECT name, popularity
> FROM temp.items3_v
> ,(values ('some phrase'::text)) consts(input)
> WHERE true
> and word_similarity(input, name) > 0.01 -- be lenient as some names
> are 75 characters long and we want to match even on a few characters of
> input
> ORDER BY 2, input <<-> name
>
PostgreSQL doesn't use index scan with functions within WHERE clause. So
you always need to use operators instead. You can try <% operator and
pg_trgm.word_similarity_threshold variable:
=# SET pg_trgm.word_similarity_threshold TO 0.1;
=# SELECT name, popularity
FROM temp.items3_v
,(values ('some phrase'::text)) consts(input)
WHERE input <% name
ORDER BY 2, input <<-> name;
--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configuration of pgaudit settings in postgreSQL.conf causes postgreSQL to fail to start
On Wed, Sep 13, 2017 at 02:42:18PM +, Troy Hardin wrote: > Putting either of these two lines in the .conf file cause it to fail to start. Can you show error messages from logs? -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Where is pg_hba.conf
Hello, On Sun, Aug 13, 2017 at 10:00:23AM -0400, Igor Korot wrote: > However, I can't find this file anywhere on the system. > I am able to connec to the server locally with psql. I can also > connect to the server > from the ODBC driver from my program. However doing a global search I > can't find that file. > > Can someone please help? You can execute the query: =# select setting from pg_settings where name = 'hba_file'; -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Audit based on role
Hello, On Mon, Aug 07, 2017 at 04:33:21PM -0700, anand086 wrote: > Hi, > > I am postgres newbie. > > We are running Postgres 9.6.3 version and have requirement, where we want to > audit any DML action performed by a user whose has module_dml role granted. > > What would be the best way to do that? I was thinking to write something > like https://wiki.postgresql.org/wiki/Audit_trigger, but I am not sure on > how to integrate "user whose has module_dml role granted" into the function. > > Instead of trigger is there any better way to achieve this? > Did you try pgaudit extension? https://github.com/pgaudit/pgaudit -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Configure Qt Creator to work with PostgreSQL to extensions development
On Sunday, 25 June 2017 17:34:11 MSK, Fabiana Zioti wrote: > I'm creating an extension to PostgreSQL, with user-defined types and > user-defined functions. > > Extensions can be written in C as well as C ++, correct? > I am currently using ATOM to develop in Ubuntu. But I would like to work > with Qt. > > PostgreSQL provides the tutorial for working with eclipse: > https://wiki.postgresql.org/wiki/Working_with_Eclipse# > > Is there any tutorial for Qt? Or are the setup steps similar to Eclipse? > > Thank you very much in advance. I am using QtCreator currently. I tried Atom and Sublime Text. But QtCreator is more convenient to me. In short you need to do the following things: 1 - setup code style, use tabs instead of spaces, you can setup autoindentation 2 - create QtCreator project for PostgreSQL and your extensions by the following way: - select File > New File or Project > Import Project > Import Existing Project, and choose PostgreSQL or extension folder - in next step choose all files - QtCreator will create 3 files: *.config, *.files, *.includes - if you created project for an extension you need to define path to PostgreSQL include headers. Open *.includes file and write the path there. With QtCreator you can quickly search necessary files or methods. https://www.dropbox.com/s/r2b7vgt0wh5ibpm/qtcreator.png?dl=0 I didn't setup other options, because I make PostgreSQL and extensions from command line. I debug them using dbg, but you can do it also from QtCreator. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
