[GENERAL] Postgresql - commercial version
Hi, I have postgreSQL 9.5 installed in the server. Is it possible to find whether the installed postgresql is enterprise db or an open source version through a query. Thanks in Advance.
Re: [GENERAL] Postgresql - commercial version
select version () gives me output something like this PostgreSQL 9.5.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.1.2 20080704 (Red hat 4.1.2-55), 64 bit. Is there any other way to find On 07-Jul-2017 7:28 PM, "Adrian Klaver" wrote: > On 07/07/2017 06:52 AM, Krithika Venkatesh wrote: > >> Hi, >> >> I have postgreSQL 9.5 installed in the server. Is it possible to find >> whether the installed postgresql is enterprise db or an open source version >> through a query. >> > > Not sure, but I would try: > > select version(); > > >> Thanks in Advance. >> > > > -- > Adrian Klaver > adrian.kla...@aklaver.com >
[GENERAL] Materialised view - refresh
Hi I have a materialised view which needs to refreshed every half an hour. Is it possible to refresh the view without using triggers. Do we have something similar to DBMS_SCHEDULER.CREATE_JOB in postgresql to create a job that can be scheduled to refresh the views periodically. Thanks in advance..
[GENERAL] Partitioning
Hi, I have a table that is partitioned on a numeric column (ID). Partitioning works when I query the table with no joins. SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE CREATED_TS = CURRENT_TIMESTAMP) Partitioning doesn't work when I do join. SELECT A.* FROM TABLE A a INNER JOIN TABLE B b ON a.ID = b.ID. Is there any other option that would work. Thanks in Advance..
Re: [GENERAL] Partitioning (constraint exclusion involving joins)
We understand the constraints exclusion will work only on constant values. But in our case we will never pass a constant value to the partitioning key when we query the partition tables. Will the partition be beneficial in this case. If yes, can you please explain. Thanks On 25-Jul-2017 6:46 PM, "Justin Pryzby" wrote: On Tue, Jul 25, 2017 at 06:21:43PM +0530, Krithika Venkatesh wrote: > I have a table that is partitioned on a numeric column (ID). > > Partitioning works when I query the table with no joins. > > SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE > CREATED_TS = CURRENT_TIMESTAMP) > > Partitioning doesn't work when I do join. > > SELECT A.* FROM TABLE A a INNER JOIN TABLE B b ON a.ID = b.ID. I think you mean "constraint exclusion doesn't work when yo do a join", which is because it only works on simple values compiled before the planner gets to see them: main=# explain SELECT COUNT(1) FROM eric_enodeb_metrics WHERE start_time>now(); -- -'999 minutes'::interval; QUERY PLAN - Aggregate (cost=62.44..62.45 rows=1 width=8) -> Append (cost=0.00..62.40 rows=14 width=0) -> Seq Scan on eric_enodeb_metrics (cost=0.00..0.00 rows=1 width=0) Filter: (start_time > now()) -> Index Only Scan using eric_enodeb_201607_idx on eric_enodeb_201607 (cost=0.42..4.44 rows=1 width=0) Index Cond: (start_time > now()) -> Index Only Scan using eric_enodeb_201608_idx on eric_enodeb_201608 (cost=0.42..4.44 rows=1 width=0) Index Cond: (start_time > now()) -> Index Only Scan using eric_enodeb_201609_idx on eric_enodeb_201609 (cost=0.42..4.44 rows=1 width=0) Index Cond: (start_time > now()) https://www.postgresql.org/docs/current/static/ddl-partitioning.html |The following caveats apply to constraint exclusion: | Constraint exclusion only works when the query's WHERE clause contains |constants (or externally supplied parameters). For example, a comparison |against a non-immutable function such as CURRENT_TIMESTAMP cannot be optimized, |since the planner cannot know which partition the function value might fall |into at run time. [..] .. and see an early mail on its implementation, here: https://www.postgresql.org/message-id/1121251997.3970.237.camel@localhost. localdomain Justin
[GENERAL] Constraint exclusion involving joins
Hi, I understand that the constraints exclusion will work only on constant values. But in my case, I will never pass a constant value to the partitioning key when we query the partition table. Will the partition be still be beneficial in this case. If yes, can you please explain Thanks
[GENERAL] Incremental refresh - Materialized view
Hi, I need to implement incremental refresh of materialized view. Please let me know how to do the incremental refresh of materialized view in postgresql 9.5.9 version. Is there anything similar to materialized view log in postgresql. Thanks in Advance! Regards, Krithika
Re: [GENERAL] Incremental refresh - Materialized view
Materialized view log is one of the feature in oracle. It creates a log in which the changes made to the table are recorded. This log is required for an asynchronous materialized view that is refreshed incrementally. I read in the below link about incrementally refreshing the materialized view in postgresql: https://medium.com/@hariprasathnallsamy/postgresql-materialized-view-incremental-refresh-44d1ca742599 Can someone let me how to do incremental refresh using Write Ahead Log Thanks, Krithika On 07-Nov-2017 12:37 PM, "John R Pierce" wrote: On 11/6/2017 10:38 PM, Krithika Venkatesh wrote: > I need to implement incremental refresh of materialized view. > > Please let me know how to do the incremental refresh of materialized view > in postgresql 9.5.9 version. > > Is there anything similar to materialized view log in postgresql. > you refresh a materialized view with REFRESH MATERIALIZED VIEW name; There's no 'incremental' methods, as views can be quite complex. I do not know what you mean by 'materialized view log', is this a feature of some other database server ? -- john r pierce, recycling bits in santa cruz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
