Re: pg_stat_statements : how to catch non successfully finished statements ?
Hello, On Thu, Apr 26, 2018 at 01:24:25PM -0700, legrand legrand wrote: > Hello all, > > I was wondering if there is a hook to collect non successfully finished SQL > statements in pg_stat_statements (timed-out, cancelled, killed, or simply > errored) ? Some time ago I looked for a such hook. My case was to log failed access permissions checks. I didn't find a way to do it, except adding a narrow-focused hook within ExecCheckRTPerms(). There is ErrorContextCallback. Context callbacks are called when an error was raised. But I don't think that it is a good approach for pg_stat_statements cases. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: bad url in docs
On Thu, Sep 06, 2018 at 04:57:17PM -0600, Rob Sargent wrote: > Sorry. I didn't see the specific form for documentations issues. There is a link [1] on bottom of the documentation page [2] to the report form. It is in the "Submit correction" section. A documentation issue is sent to pgsql-docs mailing list. 1 - https://www.postgresql.org/account/comments/new/10/libpq-ssl.html/ 2 - https://www.postgresql.org/docs/10/static/libpq-ssl.html -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: Enabling autovacuum per table
On 10/15/18 11:01 AM, Rijo Roy wrote: Hello Experts, Is there any possibility for autovacuum to work on a user table if we set Alter table sometable set (autovacuum_enabled = true) ; even if the parameter autovacuum = off in Postgresql.conf I am using Postgresql 10 on Linux 6.9. According to me, it won't work without setting autovacuum = on except for Template0 database. What is your opinion? I think you are right, autovacuum won't vacuum and analyze a table without setting autovacuum = on. But PostgreSQL might want to force vacuum if a table is at risk of wraparound. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: pgaudit.log_parameter
Hello, On 31.07.2019 14:21, Luca Ferrari wrote: Hello, I'm a little confused about the setting pgaudit.log_parameter of the pgaudit extension (https://github.com/pgaudit/pgaudit/blob/master/README.md). What's the purpose of this? AN example of query that will trigger such parameter logging? Apparently I cannot get it providing me more information than ''. pgaudit.log_parameter allows to log parameters of prepared statements. See the documentation: https://www.postgresql.org/docs/current/sql-prepare.html The following example logs parameters if pgaudit.log_parameter is on: EXECUTE fooplan(1, 'Hunter Valley', 't', 200.00); It should log parameters: 1, 'Hunter Valley', 't', 200.00 -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: <-> Operator on Trigram Index
Hello, On 14.11.2018 01:42, Jeffrey Kamei wrote: I'm trying to get the <-> operator to recognize a trigram index (GIST) I've set on a table. Using `EXPLAIN VERBOSE` I can see the query engine ignoring the trigram index when using the `<->` operator. However if I use the `%` operator, the index is found and used. Can you explain why this is happening? As far as I can tell from the documentation, the `<->` operator should be using the index as well. Yes <-> operator should use a GiST index. Can you show your query and its plan? -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: psql profiles?
On 07.12.2018 01:34, Matt Zagrabelny wrote: On Thu, Dec 6, 2018 at 4:24 PM Alvaro Herrera <mailto:alvhe...@2ndquadrant.com>> wrote: Sure, just define a pg_service.conf file. https://www.postgresql.org/docs/11/libpq-pgservice.html Thanks Alvaro! Is there any shorter version than: psql "service=foo" ? If not, I can make a shell alias that puts the "service=$@" into the command. Also you can set environment variable PGSERVICE=foo in your .bashrc. Or you can just set variable PGHOST=db-host-1.example.com. In last case you don't need pg_service.conf file. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: pg_ctl kill INT vs. pg_cancel_backend
Hello, On 08.12.2018 20:30, Ron wrote: Hi, Is there a substantive difference between the two, or are they just different interfaces to the same action? I think they both only execute kill() and send a signal to a process. But pg_cancel_backend() after checking privileges sends only SIGINT and only to a PostgreSQL's backend process. pg_ctl can send a specified signal to any process. From the documentation: pg_ctl kill signal_name process_id Here signal_name is HUP, INT and others. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: Get attributes names
On 24.01.2019 11:54, ramsiddu007 wrote:
Dear all,
I hope you are all doing well. Today i got one
requirement, for that i need attribute names as below example.
Xml:
In the above sample input xml, i want attributes list of emp_deails like.
Attributes
___
emp_id
emp_name
dept
Thanking you,
In PostgreSQL 11 (and 10 I think) you can do something like this:
=# CREATE TABLE xmldata AS SELECT xml $$
$$
as data;
=# SELECT xmltable.*
FROM xmldata,
xmltable('/emp/emp_details/@*' passing data
columns atrr text path 'name()');
atrr
--
emp_id
emp_name
dept
--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
Re: Cannot vacuum even in single-user mode after xidStopLimit is reached
Hello, On 18.02.2019 15:05, Jahwan Kim wrote: So apparently I'm in some loop without any clear way out. The most similar thing I found was "Could not finish anti-wraparound VACUUM when stop limit is reached" https://www.postgresql.org/message-id/53820ed9.3010...@vmware.com. This is quite old, though. Two questions: (1) Can I revive this database? (2) How did this possibly happen? 1 - I think yes. But you need to drop *ALL* (in all bases) temporary tables manually and only then do VACUUM (in single mode of course). Unfortunately VACUUM do not drop and vacuum orphan temporary tables. 2 - I think you have a long lived session with long lived temporary tables. Autovacuum do not freeze temporary tables and it cannot move relfrozenxid. That's why you get wraparound. To avoid it you need to drop unnecessary temporary tables or do VACUUM in a long lived session. -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: Cannot vacuum even in single-user mode after xidStopLimit is reached
On 18.02.2019 17:18, Jahwan Kim wrote: Thanks for the reply. (1) The problem is, I cannot drop any of the temp tables, not even in the single-user mode. It says 'vacuum', 'You might also need to commit or roll back old prepared transactions.' And, no, there seems to be no pending prepared transaction either. Can you show the exact whole message? Do I understand correctly. Are temp tables persist in pg_class even after trying to drop temp tables in single-user mode? -- Arthur Zakirov Postgres Professional: http://www.postgrespro.com Russian Postgres Company
Re: How to use full-text search URL parser to filter query results by domain name?
On 07.04.2019 07:06, Jess Wren wrote:
However, I can't figure out how I would integrate this into the above
query to filter out duplicate domains from the results. And because this
is the docs for "testing and debugging text search
<https://www.postgresql.org/docs/11/textsearch-debugging.html#TEXTSEARCH-PARSER-TESTING>",
I don't know if this use of `ts_parse()` is even related to how the URL
parser is intended to be used in practice.
How would I use the "host" parser in my query above to return one row
per domain? Also, how would I appropriately index the "links" table for
"host" and "url" token lookup?
I think it is normal to use ts_parse(). And I suppose you might use
windows functions.
For example, you have table links:
=# create table links (score int, link text);
=# insert into links values
(1, 'http://www.foo.com/bar'),
(2, 'http://www.foo.com/foo'),
(2, 'http://www.bar.com/foo'),
(1, 'http://www.bar.com/bar');
You can use the following query:
=# with l as (
select score, token, link,
rank() over (partition by token order by score) as rank
from links,
lateral ts_parse('default', link)
where tokid = 6)
select score, token, link from l where rank = 1;
score |token| link
---+-+
1 | www.bar.com | http://www.bar.com/bar
1 | www.foo.com | http://www.foo.com/bar
It is just the idea, probably the query might be simpler.
--
Arthur Zakirov
Postgres Professional: http://www.postgrespro.com
Russian Postgres Company
