Re: [GENERAL] Retrieving comment of rules and triggers

[Michael Paquier]

On Fri, Jun 10, 2016 at 6:06 AM, Melvin Davidson  wrote:
> as the developers have a nasty habit of changing pg_catalog
> tables/columns.

... When necessary to improve the quality of the project and the user
experience.
-- 
Michael


-- 
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] High availability and load balancing ...

[Venkata Balaji N]

On Thu, Jun 9, 2016 at 8:01 PM, Sunil N Shinde 
wrote:

> Thanks Venkata.
>
>
>
> I am considering latest version now i.e. 9.4 or 9.5 on Linux 6.
>
> Is there any difference in setup from 9.1 to 9.5?
>

There is no difference in the setup. Streaming Replication in the version
9.5 is a lot better with a lot of bug fixes specific to streaming
replication and with a few extra parameters compared to the version 9.1.
Please refer to postgresql documentation.

Regards,

Venkata B N
Fujitsu Australia

Re: [GENERAL] Retrieving comment of rules and triggers

[Melvin Davidson]

On Thu, Jun 9, 2016 at 5:03 PM, Melvin Davidson 
wrote:

>
>
> On Thu, Jun 9, 2016 at 5:01 PM, David G. Johnston <
> david.g.johns...@gmail.com> wrote:
>
>> On Thu, Jun 9, 2016 at 4:45 PM, Alex Magnum 
>> wrote:
>>
>>> Hi, is there a way to retrieve the comment of rules and triggers. I
>>> worked it out on functions, tables, views but am kind of stuck with rules
>>> and triggers.
>>>
>>
>> ​
>> https://www.postgresql.org/docs/devel/static/functions-info.html
>>
>> ​Table ​9-63. Comment Information Functions
>>
>> obj_description(object_oid, catalog_name)
>>
>> Catalog Names:
>>
>> pg_trigger
>> pg_rules
>>
>> The source catalog for the data is:
>>
>> pg_description
>>
>> I'm suspect of your existing solution given that this is supposed to be a
>> universal interface for comment data...
>>
>> David J.
>>
>>
> -- To get all rule comments
> SELECT DISTINCT r.rulename,
>c.description
>   FROM pg_rewrite r
>   JOIN pg_description c ON c.objoid = r.oid;
>
> -- To get all trigger comments
>
> SELECT DISTINCT t.tgname,
>c.description
>   FROM pg_trigger t
>   JOIN pg_description c ON c.objoid = t.oid;
>
>
> --
> *Melvin Davidson*
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.
>

FYI, in the future, it is always best to give your version of PostgreSQL &
O/S, as the developers have a nasty habit of changing
pg_catalog tables/columns.

-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Retrieving comment of rules and triggers

[Adrian Klaver]

On 06/09/2016 01:45 PM, Alex Magnum wrote:

Hi, is there a way to retrieve the comment of rules and triggers. I
worked it out on functions, tables, views but am kind of stuck with
rules and triggers.

Any help is appreciated.
Thanks
Alex


From psql:

\dd some_rule_name
\dd some_trigger_name

--
Adrian Klaver
adrian.kla...@aklaver.com


--
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] Retrieving comment of rules and triggers

[Melvin Davidson]

On Thu, Jun 9, 2016 at 5:01 PM, David G. Johnston <
david.g.johns...@gmail.com> wrote:

> On Thu, Jun 9, 2016 at 4:45 PM, Alex Magnum  wrote:
>
>> Hi, is there a way to retrieve the comment of rules and triggers. I
>> worked it out on functions, tables, views but am kind of stuck with rules
>> and triggers.
>>
>
> ​
> https://www.postgresql.org/docs/devel/static/functions-info.html
>
> ​Table ​9-63. Comment Information Functions
>
> obj_description(object_oid, catalog_name)
>
> Catalog Names:
>
> pg_trigger
> pg_rules
>
> The source catalog for the data is:
>
> pg_description
>
> I'm suspect of your existing solution given that this is supposed to be a
> universal interface for comment data...
>
> David J.
>
>
-- To get all rule comments
SELECT DISTINCT r.rulename,
   c.description
  FROM pg_rewrite r
  JOIN pg_description c ON c.objoid = r.oid;

-- To get all trigger comments

SELECT DISTINCT t.tgname,
   c.description
  FROM pg_trigger t
  JOIN pg_description c ON c.objoid = t.oid;


-- 
*Melvin Davidson*
I reserve the right to fantasize.  Whether or not you
wish to share my fantasy is entirely up to you.

Re: [GENERAL] Retrieving comment of rules and triggers

[David G. Johnston]

On Thu, Jun 9, 2016 at 4:45 PM, Alex Magnum  wrote:

> Hi, is there a way to retrieve the comment of rules and triggers. I worked
> it out on functions, tables, views but am kind of stuck with rules and
> triggers.
>

​
https://www.postgresql.org/docs/devel/static/functions-info.html

​Table ​9-63. Comment Information Functions

obj_description(object_oid, catalog_name)

Catalog Names:

pg_trigger
pg_rules

The source catalog for the data is:

pg_description

I'm suspect of your existing solution given that this is supposed to be a
universal interface for comment data...

David J.

[GENERAL] Retrieving comment of rules and triggers

[Alex Magnum]

Hi, is there a way to retrieve the comment of rules and triggers. I worked
it out on functions, tables, views but am kind of stuck with rules and
triggers.

Any help is appreciated.
Thanks
Alex

Re: [GENERAL] Automate copy - Postgres 9.2

[rob stone]

On Wed, 2016-06-08 at 23:50 -0400, David G. Johnston wrote:
> 
> POn Wednesday, June 8, 2016, Patrick B 
> wrote:
> > > ERROR:  syntax error at or near "||"
> > > LINE 12:     TO '/var/lib/pgsql/' || crtrow.account_id |...
> > 
> 
> Which tells me you cannot use an expression as a file name.  The docs
> support this conclusion.
> 
> You probably need to use EXECUTE after constricting a string.
> 
> You could also try psql in a shell script.
> 
> David J. 


If this is something that you'll never ever have to do again in the
future, you could download ExecuteQuery which has a function to export
a result set as either XML or CSV delimited file output.

OTOH, if this is going to be something run on a regular basis, I think
you'd be better off writing a little program, or as David suggested
embedding this into a shell script.

HTH,
Rob


-- 
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] Why threads every 30 seconds?

[Bryan Henderson]

>>> Looking at audit logs, I see that my Postgresql server generates a new 
>>> thread
>>> precisely every 30 seconds, in two series (so 4 threads every minute).  This
>>> is an otherwise idle server.
>
>>> Does anyone know what these threads are for?  Just curious.
>
>Autovacuum, likely.  The rate would depend on your autovacuum_naptime
>and how many active databases you have.

Yep, that was it, thanks.

I had the default autovacuum_naptime of 1 minute, and had two Postgresql
clusters runing.  I turned off autovacuum and these threads disappeared.

Still not sure how it comes to twice a minute per cluster.  There are 5
databases, and as far as I know, a client connection to one of them, not
doing anything, while this was happening.

-- 
Bryan Henderson   San Jose, California


-- 
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_trgm] Making similarity(?, ?) < ? use an index

[Tom Lane]

I wrote:
> Greg Navis  writes:
>> I'm not sure how to make this operator use an index. It seems I need to
>> create an operator class but I'm not sure how.

> What you'd want to do is add it to the existing operator class and then
> teach the class's support functions (mostly, the "consistent" function)
> about it.

BTW, you'd probably find this patch instructive:

https://git.postgresql.org/gitweb/?p=postgresql.git=commitdiff=f576b17cd6ba653bdace1f0da9a3b57f4984e460

although it's doing more than just adding one operator.

regards, tom lane


-- 
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_trgm] Making similarity(?, ?) < ? use an index

[Tom Lane]

Greg Navis  writes:
> I'm not sure how to make this operator use an index. It seems I need to
> create an operator class but I'm not sure how.

What you'd want to do is add it to the existing operator class and then
teach the class's support functions (mostly, the "consistent" function)
about it.

regards, tom lane


-- 
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] connection pooling, many users, many datasources

[Michael McInness]

Pgbouncer worked fine for this. Thanks.

On Wed, Jun 8, 2016 at 1:12 PM, Sameer Kumar 
wrote:

>
>
> On Thu, 9 Jun 2016, 12:50 a.m. Michael McInness, 
> wrote:
>
>> I am working with a system that uses JDBC and JNDI-based connection
>> pooling. There are currently many organizations that use the system. Each
>> of the organizations has multiple, individual system users.
>>
>> Currently, each entity has its own database and a corresponding
>> application-based datasource and connection pool.
>>
>> Because of the nature of the data, it is of utmost importance that each
>> organization's data is secure and inaccessible to other entities.
>>
>> Maintaining individual datasources/connection pools for each entity is
>> extremely cumbersome. It just doesn't seem like a good solution as the
>> number of entities grows to many dozens.
>>
>> And while handling connections on-demand would provide a dynamic
>> alternative, it also feels like a bad option as I would hate to lose the
>> performance and robustness of pooling connections.
>>
>
> Consider using a database side pool which can pool connections for each
> user and dbname pair. You might want to check pgpool and pgbouncer.
> With one of them you can get benefits of pooling and yet let
> applicationmake connection on the go as request comes in
>
>
>> How about using SET/RESET ROLE so I could connect to the db under a group
>> role and then changing roles per session to restrict access to
>> entity-specific schema (assuming permissions are set correctly in the
>> database)?
>>
>
> I am not sure if this is the reason or right use case for SET ROLE.
>
> Using different users (either with same app server or different) is the
> best way if ensuring data is not accessible across organizations.
>
>
>> Are there other options?
>>
>> Profuse appreciation for your thoughts and suggestions,
>>
>> Mick
>>
> --
> --
> Best Regards
> Sameer Kumar | DB Solution Architect
> *ASHNIK PTE. LTD.*
>
> 101 Cecil Street, #11-11 Tong Eng Building, Singapore 069 533
>
> T: +65 6438 3504 | M: +65 8110 0350 | www.ashnik.com
>

Re: [GENERAL] High availability and load balancing ...

[Sunil N Shinde]

Thanks Venkata.

I am considering latest version now i.e. 9.4 or 9.5 on Linux 6.
Is there any difference in setup from 9.1 to 9.5?


Thanks & Regards,
Sunil N Shinde

From: Venkata Balaji N [mailto:nag1...@gmail.com]
Sent: 08 June 2016 12:46
To: Sunil N Shinde 
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] High availability and load balancing ...


I need to do the setup for High availability function.
Also want to implement load balancing for 02 nodes.

You will have to build streaming replication which was introduced in 
PostgreSQL-9.0

I think PGPool will be require for that. Can I use PGPool without cost.

pgpool-II is an open source tool which can be used for connection pooling and 
load balancing.

 Can I get the basic steps to do this setup?

Database--  Postgresql 9.1
 OS --  Linux 6

Below is the link which explains the basic steps to setup "streaming 
replication"

https://www.postgresql.org/docs/9.1/static/warm-standby.html

By the way, version 9.1 is very old and will reach end-of-life soon. You are 4 
major versions behind, did you consider using latest version ?

Regards,
Venkata B N

Fujitsu Australia

Re: [GENERAL] [pg_trgm] Making similarity(?, ?) < ? use an index

[Greg Navis]

Artur, no worries, I'm not writing any code ;-)

I did the following:

CREATE TYPE trgm_match AS (match TEXT, threshold NUMERIC);
CREATE OR REPLACE FUNCTION trgm_check_match (string TEXT, match trgm_match)
  RETURNS bool
  AS 'SELECT match.match <-> string <= 1 - match.threshold'
  LANGUAGE SQL;
CREATE OPERATOR %(leftarg = text, rightarg = trgm_match,
procedure=trgm_check_match);

This allows me to write:

SELECT ('Warsaw' % row('Warsw', 0.3)::trgm_match);

I'm not sure how to make this operator use an index. It seems I need to
create an operator class but I'm not sure how. This is how pg_trgm creates
its operator class:

-- create the operator class for gist
CREATE OPERATOR CLASS gist_trgm_ops
FOR TYPE text USING gist
AS
OPERATOR1   % (text, text),
FUNCTION1   gtrgm_consistent (internal, text, smallint,
oid, internal),
FUNCTION2   gtrgm_union (internal, internal),
FUNCTION3   gtrgm_compress (internal),
FUNCTION4   gtrgm_decompress (internal),
FUNCTION5   gtrgm_penalty (internal, internal,
internal),
FUNCTION6   gtrgm_picksplit (internal, internal),
FUNCTION7   gtrgm_same (gtrgm, gtrgm, internal),
STORAGE gtrgm;

Should my operator class mimic the one above?
-- 
Greg Navis
I help tech companies to scale Heroku-hosted Rails apps.
Free, biweekly scalability newsletter for SaaS CEOs

[GENERAL] Optimise OR condiditions across multiple joins

[Andrew Beverley]

Dear all,

Is there a way to efficiently perform OR conditions across multiple
joins?

For example, I have the following statement:

SELECT RECORD.id
FROM   RECORD
   left join string
  ON string.record_id = RECORD.id
 AND string.layout_id = 6
   left join DATE
  ON DATE.record_id = RECORD.id
 AND DATE.layout_id = 8
WHERE  ( string.value_index = 'beverley'
  OR DATE.value = '1980-11-16' );

Which when analysed produces the output at the end of this email. It
appears to be doing a slow filter across all values, once it's
completed the joins across all rows.

If I change the OR condition to an AND condition, then indexes are used
to good effect and the query is very fast.

Does anyone have any advice as to how I can efficiently use indexes for
the OR condition and substantially improve the query time? I can
provide full details of all my current indexes if that helps.

PostgreSQL 9.4.8 on Debian 8.5

Thanks,

Andy


 Hash Right Join  (cost=176359.93..266784.76 rows=135 width=8) (actual 
time=17189.826..17845.939 rows=15 loops=1)
   Hash Cond: (string.record_id = record.id)
   Filter: (((string.value_index)::text = 'beverley'::text) OR (date.value = 
'1980-11-16'::date))
   Rows Removed by Filter: 2094748
   ->  Bitmap Heap Scan on string  (cost=6683.45..76441.68 rows=305938 
width=19) (actual time=45.417..681.269 rows=285099 loops=1)
 Recheck Cond: (layout_id = 6)
 Heap Blocks: exact=19548
 ->  Bitmap Index Scan on string_idx_layout_id  (cost=0.00..6606.97 
rows=305938 width=0) (actual time=39.527..39.527 rows=285103 loops=1)
   Index Cond: (layout_id = 6)
   ->  Hash  (cost=133376.36..133376.36 rows=2088250 width=12) (actual 
time=15917.540..15917.540 rows=2094763 loops=1)
 Buckets: 16384  Batches: 32  Memory Usage: 2611kB
 ->  Hash Right Join  (cost=71333.62..133376.36 rows=2088250 width=12) 
(actual time=7041.758..12268.693 rows=2094763 loops=1)
   Hash Cond: (date.record_id = record.id)
   ->  Seq Scan on date  (cost=0.00..46095.40 rows=285676 width=12) 
(actual time=0.871..769.841 rows=285099 loops=1)
 Filter: (layout_id = 8)
 Rows Removed by Filter: 855297
   ->  Hash  (cost=37072.50..37072.50 rows=2088250 width=8) (actual 
time=7032.415..7032.415 rows=2094763 loops=1)
 Buckets: 16384  Batches: 32  Memory Usage: 2576kB
 ->  Seq Scan on record  (cost=0.00..37072.50 rows=2088250 
width=8) (actual time=0.037..3398.638 rows=2094763 loops=1)



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general