Re: [GENERAL] Incremental refresh - Materialized view

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. There is no such feature

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

Re: [GENERAL] Combine multiple text search configuration

Hi, Thank for your proposition but when to use this query : (to_tsvector('english', document) || to_tsvector('french', document)) @@ (to_tsquery('english', query) || to_tsquery('french', query)) I think that the performance decrease and not a good solution for big amount of data. Is it?

Re: [GENERAL] idle in transaction, why

Rob Sargent schrieb am 06.11.2017 um 23:09: > Gosh I wish I could learn to proof-read my posts. > My support crew graciously set > > idle_transaction_timeout = 1 > > Now to ponder if I need zero or some large number. The unit of that setting is milliseconds (if no unit is specified). zero

Re: [GENERAL] Incremental refresh - Materialized view

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

[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] Naming conventions for column names

Hi, It seems people worrying about failure of client side code after changes in column names. Melvin also mention that just change in one column was broken many things. > > > My intension is to improve naming conventions and increase naming string > > where naming conventions are correct but

Re: [GENERAL] the database system is shutting down - terminating walsender process due to replication timeout

Thanks for you reply. In the meantime we figured out what the issue was. We had a virtual IP that was being released from the master server just as the master would try to stream the last checkpoint. This caused wal_sender process to hang until the timeout value was reached. Technically

Re: [GENERAL] idle in transaction, why

On 11/06/2017 02:38 PM, Merlin Moncure wrote: On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value

Re: [GENERAL] idle in transaction, why

On 11/06/2017 02:38 PM, Merlin Moncure wrote: On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On Mon, Nov 6, 2017 at 12:35 PM, Tom Lane wrote: > David Pacheco writes: > > ... that process appears to have exited due to a fatal error > > (out of memory). (I know it exited because the process still exists in > the > > kernel -- it hasn't been reaped

Re: [GENERAL] idle in transaction, why

On Mon, Nov 6, 2017 at 2:50 PM, Rob Sargent wrote: > > > On 11/06/2017 01:41 PM, Tom Lane wrote: >> >> Rob Sargent writes: >>> >>>idle_in_transaction_session_timeout | 0 | default | >>> || A value of 0 turns off the timeout. |

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:50 PM, Rob Sargent wrote: On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:41 PM, Tom Lane wrote: Rob Sargent writes: idle_in_transaction_session_timeout | 0 | default | || A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong tree anyway: so far as I can find, there is no

Re: [GENERAL] idle in transaction, why

Rob Sargent writes: > idle_in_transaction_session_timeout | 0 | default | > || A value of 0 turns off the timeout. | user Meh. I think we're barking up the wrong tree anyway: so far as I can find, there is no error message reading 'idle

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

David Pacheco writes: > I ran into what appears to be a deadlock in the logging subsystem. It > looks like what happened was that the syslogger process exited because it > ran out of memory. But before the postmaster got a chance to handle the > SIGCLD to restart it, it handled

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:17 PM, Tom Lane wrote: "David G. Johnston" writes: You should probably login as your application user and do "show idle_in_transaction_session_timeout" to see what a clean session has for a value and then figure out from there where that value is

Re: [GENERAL] idle in transaction, why

On 11/06/2017 01:09 PM, David G. Johnston wrote: On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent >wrote: Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4 The

Re: [GENERAL] idle in transaction, why

"David G. Johnston" writes: > You should probably login as your application user and do "show > idle_in_transaction_session_timeout" to see what a clean session has for a > value and then figure out from there where that value is coming from. You don't have to guess

Re: [GENERAL] idle in transaction, why

On Mon, Nov 6, 2017 at 12:32 PM, Rob Sargent wrote: > Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and > this JDBC driver postgresql:42.1.4 > > > The postgresql.conf file has > > #idle_in_transaction_session_timeout = 0# in milliseconds, 0

[GENERAL] postmaster deadlock while logging after syslogger exited

Hello, I ran into what appears to be a deadlock in the logging subsystem. It looks like what happened was that the syslogger process exited because it ran out of memory. But before the postmaster got a chance to handle the SIGCLD to restart it, it handled a SIGUSR1 to start an autovacuum

Re: [GENERAL] Combine multiple text search configuration

Hi, On 2017-11-06 09:17, hmidi slim wrote: > Hi, > I want to know if I can combine multiple text search configurations when > I tried to use FTS. > Is there any options like this: > *to_tsvector(['english', 'french'], document)* > * > * > Trying to create a new text configuration: > *Create text

[GENERAL] idle in transaction, why

Using postgres 10-beta3 (hopefully 10.0 this week) on virtual CentOS7 and this JDBC driver postgresql:42.1.4 The postgresql.conf file has #idle_in_transaction_session_timeout = 0# in milliseconds, 0 is disabled my db url has "?prepareThreshold=0" since I bump into "already

Re: [GENERAL] Postgresql 9.3 service doesn't start on RedHat Linux 6.8

On 11/6/2017 5:28 AM, Vikas Sharma wrote: I am having issues in starting up postgresql service on RHEL 6.8, It was all working fine yesterday but now it doesn't start and throws error - same kind of error that is received connecting to remote postgres database. what /exact/ error is it

Re: [GENERAL] Naming conventions for column names

Sachin Kotwal wrote: > I believe these naming conventions will be at two levels: > > 1. Internal code of PostgreSQL , structures getting used internally > 2. SQL/C functions get executed at the time of database initialization to > create default objects and system catalogs. > > > I will see

Re: [GENERAL] Naming conventions for column names

On Mon, Nov 6, 2017 at 10:04 AM, Karsten Hilbert wrote: > On Mon, Nov 06, 2017 at 08:23:07PM +0530, Sachin Kotwal wrote: > > > You are right. Those naming conventions are old and that is why we have > to > > improve those where ever and when ever required. > > I'd love

Re: [GENERAL] Naming conventions for column names

On Mon, Nov 06, 2017 at 08:23:07PM +0530, Sachin Kotwal wrote: > You are right. Those naming conventions are old and that is why we have to > improve those where ever and when ever required. I'd love to see the "requirement" defined. Regards, Karsten -- GPG key ID E4071346 @

Re: [GENERAL] xmin increasing within a transaction block?

On Mon, Nov 6, 2017 at 3:26 PM, Tom Lane wrote: > Alvaro Herrera writes: >> Luca Ferrari wrote: >>> Why is xmin greater than the current transaction id (and most notably >>> not "fixed")? > >> Something is using subtransactions there. My first guess

Re: [GENERAL] Naming conventions for column names

Hi Tom, You are right. Those naming conventions are old and that is why we have to improve those where ever and when ever required. If no one has objection, I will give a try to improve this part. I believe these naming conventions will be at two levels: 1. Internal code of PostgreSQL ,

Re: [GENERAL] Naming conventions for column names

>Those naming conventions are twenty-five years old, and there is an >astonishing amount of client code that would break if we ran around >changing existing system catalog column names. It's very unlikely that >any proposal to do that would even receive serious consideration. >The bar to using

Re: [GENERAL] Naming conventions for column names

Sachin Kotwal writes: > I can understand that it is important to maintain naming pattern same as > system catalogs, but in that case we may need to redefine system catalogs > naming conventions . Those naming conventions are twenty-five years old, and there is an astonishing

Re: [GENERAL] xmin increasing within a transaction block?

Alvaro Herrera writes: > Luca Ferrari wrote: >> Why is xmin greater than the current transaction id (and most notably >> not "fixed")? > Something is using subtransactions there. My first guess would be that > there are triggers with EXCEPTION blocks, but your example

[GENERAL] Converting AGE() to something human readable

Hi, How is this done in v8.4? postgres=# SELECT datname, datfrozenxid, age(datfrozenxid) postgres-# FROM pg_database;   datname  | datfrozenxid |    age ---+--+--- template1 |   3603334165 |  25735089 template0 |   3603470462 |  25598792 postgres  |   3576970250 | 

Re: [GENERAL] xmin increasing within a transaction block?

Luca Ferrari wrote: > Any other idea? None here. Maybe try attaching a debugger, setting a breakpoint on AssignTransactionId, and grab backtraces when it is hit. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Re: [GENERAL] xmin increasing within a transaction block?

On Mon, Nov 6, 2017 at 2:29 PM, Andres Freund wrote: > That doesn't look like plain postgres behaviour to me. Any chance you're > using a pooler in statement mode in front of postgres? None I'm aware of, since the machine is using postgresql locally and I'm connecting to it

Re: [GENERAL] xmin increasing within a transaction block?

On Mon, Nov 6, 2017 at 1:53 PM, Alvaro Herrera wrote: > Something is using subtransactions there. My first guess would be that > there are triggers with EXCEPTION blocks, but your example doesn't show > any. Or maybe you have event triggers. Thanks, but I don't see any

Re: [GENERAL] Naming conventions for column names

Hi Peter, I can understand that it is important to maintain naming pattern same as system catalogs, but in that case we may need to redefine system catalogs naming conventions . So that we can use those newly added naming conventions in system views as well. It is difficult to understand

Re: [GENERAL] xmin increasing within a transaction block?

Hi, On 2017-11-06 10:36:06 +0100, Luca Ferrari wrote: > Hi all, > I suspect this has a trivial explaination, but this is what I'm experiencing: > > > CREATE TABLE foo( i int ); > > BEGIN; > * > INSERT INTO foo(i) VALUES( 1 ); > * > INSERT INTO foo(i) VALUES( 2 ); > * > SELECT xmin, cmin, xmax,

[GENERAL] Postgresql 9.3 service doesn't start on RedHat Linux 6.8

Hi, I am having issues in starting up postgresql service on RHEL 6.8, It was all working fine yesterday but now it doesn't start and throws error - same kind of error that is received connecting to remote postgres database. for the meantime I have started postgresql as below -

Re: [GENERAL] Naming conventions for column names

On 11/6/17 05:36, Sachin Kotwal wrote: > Is there any special reason to keep column names as usesysid > and usename instead of usersysid and username in below system View? The reason to *keep* them is compatibility. The reason they are like that to start with is because that is the naming

Re: [GENERAL] xmin increasing within a transaction block?

Luca Ferrari wrote: > Hi all, > I suspect this has a trivial explaination, but this is what I'm experiencing: > > > CREATE TABLE foo( i int ); > > BEGIN; > * > INSERT INTO foo(i) VALUES( 1 ); > * > INSERT INTO foo(i) VALUES( 2 ); > * > SELECT xmin, cmin, xmax, cmax, i FROM foo; > xmin | cmin |

Re: [GENERAL] Naming conventions for column names

Hi All, Correcting my words. Is there any special reason to keep column names as usesysid and usename instead of usersysid and username in below system View? On Mon, Nov 6, 2017 at 4:03 PM, Sachin Kotwal wrote: > Hi All, > > Is there any reason to keep column names as

[GENERAL] Naming conventions for column names

Hi All, Is there any reason to keep column names as usesysid and senate instead of usersysid and username ? postgres=# select * from pg_stat_replication ; pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start |

[GENERAL] xmin increasing within a transaction block?

Hi all, I suspect this has a trivial explaination, but this is what I'm experiencing: > CREATE TABLE foo( i int ); > BEGIN; * > INSERT INTO foo(i) VALUES( 1 ); * > INSERT INTO foo(i) VALUES( 2 ); * > SELECT xmin, cmin, xmax, cmax, i FROM foo; xmin | cmin | xmax | cmax | i

[GENERAL] Combine multiple text search configuration

Hi, I want to know if I can combine multiple text search configurations when I tried to use FTS. Is there any options like this: *to_tsvector(['english', 'french'], document)* Trying to create a new text configuration: *Create text search configuration test (copy=simple)* *Alter text search