Re: Execution plan does not use index

út 10. 11. 2020 v 8:18 odesílatel Peter Coppens napsal: > Michael > > Many thanks for spending your time on this. Your alternative does not help > unfortunately (see execution plan) > > Still a sequential scan on the complete table. I have tried many > alternatives and somehow whenever I add a co

Re: Execution plan does not use index

Michael Many thanks for spending your time on this. Your alternative does not help unfortunately (see execution plan) Still a sequential scan on the complete table. I have tried many alternatives and somehow whenever I add a column that is not in the index (I64_01) the optimizer decides not to

Re: How to set up a schema default date to '2020-01-01'?

On Tue, Nov 10, 2020 at 10:51:02AM +0530, mark armon <1994hej...@gmail.com> wrote: > On Mon, Nov 9, 2020 at 9:44 PM David G. Johnston > wrote: > > > On Mon, Nov 9, 2020 at 9:08 AM mark armon <1994hej...@gmail.com> wrote: > > > >> How to set up a schema default date (now) to '2020-01-01'? Whate

Re: How to set up a schema default date to '2020-01-01'?

On Mon, Nov 9, 2020 at 10:21 PM mark armon <1994hej...@gmail.com> wrote: > like I create a schema: test, I want the default date to 2020-01-01, so > when I do > > select test.now; > > the result is 2020-01-01 > That is not presently a feature that PostgreSQL implements. While you can leverage pro

Re: How to set up a schema default date to '2020-01-01'?

like I create a schema: test, I want the default date to 2020-01-01, so when I do select test.now; the result is 2020-01-01 On Mon, Nov 9, 2020 at 9:44 PM David G. Johnston wrote: > On Mon, Nov 9, 2020 at 9:08 AM mark armon <1994hej...@gmail.com> wrote: > >> >> How to set up a schema default

Re: initdb --data-checksums

On Mon, Nov 09, 2020 at 06:03:43PM +0100, Paul Förster wrote: > indeed, it is. Have a look at: > > https://www.postgresql.org/docs/12/app-pgchecksums.html > > Make sure the database is cleanly shut down before doing it. This tool is really useful with upgrades after pg_upgrade. Please note that

Re: New "function tables" in V13 documentation

On Sun, Nov 8, 2020 at 3:57 PM Thomas Kellerer wrote: > > In case someone is interested: there is a little discussion going on on > Reddit whether the new format of presenting functions in V13 is a step > backwards: > > > https://www.reddit.com/r/PostgreSQL/comments/jpi0rp/does_anyone_else_f

Re: Execution plan does not use index

On Mon, Nov 9, 2020 at 1:11 PM Peter Coppens wrote: > Adding the tzn.utc_offset results in the fact that the execution plan no > longer considers to use the index on the measurement_value table. Is there > any way the SQL can be rewritten so that the index is used? Or any other > solution so that

Re: New "function tables" in V13 documentation

On Mon, Nov 9, 2020 at 3:30 PM Ron wrote: > On 11/9/20 3:05 PM, David G. Johnston wrote: > > On Mon, Nov 9, 2020 at 2:01 PM Ron wrote: > >> My suggestion is to add a "table of contents" at the top of non-trivial >> sections that simply lists available functions by name (generally ignoring >> arg

Re: New "function tables" in V13 documentation

On 11/9/20 3:05 PM, David G. Johnston wrote: On Mon, Nov 9, 2020 at 2:01 PM Ron > wrote: My suggestion is to add a "table of contents" at the top of non-trivial sections that simply lists available functions by name (generally ignoring argument variat

Re: New "function tables" in V13 documentation

On Mon, Nov 9, 2020 at 2:01 PM Ron wrote: > My suggestion is to add a "table of contents" at the top of non-trivial > sections that simply lists available functions by name (generally ignoring > argument variations) and a quick one line description of purpose. Once a > person finds the name of t

Re: New "function tables" in V13 documentation

On 11/9/20 2:47 PM, David G. Johnston wrote: On Mon, Nov 9, 2020 at 1:41 PM Tom Lane > wrote: Alvaro Herrera mailto:alvhe...@alvh.no-ip.org>> writes: > On 2020-Nov-08, Adrian Klaver wrote: >> Yeah, I would agree with the mobile first design comments. Then

Re: New "function tables" in V13 documentation

=?UTF-8?B?Sm9zZWYgxaBpbcOhbmVr?= writes: > But it is not clear for me what exactly was the problem with the old > format. Is there any discussion anyone can point me to to ensure I'll > not just revive the old problems, but improve the overall situation? The primary discussion threads for this ch

Re: New "function tables" in V13 documentation

On 11/9/20 12:35 PM, Alvaro Herrera wrote: On 2020-Nov-09, Adrian Klaver wrote: If you have suggestion on how to improve the new format, I'm sure we can discuss that. It seems pretty clear to me that we're not going back to the old format. Improve it by going back to old format. Not sure why

Re: New "function tables" in V13 documentation

On Mon, Nov 9, 2020 at 1:41 PM Tom Lane wrote: > Alvaro Herrera writes: > > On 2020-Nov-08, Adrian Klaver wrote: > >> Yeah, I would agree with the mobile first design comments. Then again > that > >> plague is hitting most sites these days. My 2 cents is it is a step > >> backwards. You can cove

Re: New "function tables" in V13 documentation

Alvaro Herrera writes: > On 2020-Nov-08, Adrian Klaver wrote: >> Yeah, I would agree with the mobile first design comments. Then again that >> plague is hitting most sites these days. My 2 cents is it is a step >> backwards. You can cover more ground quickly and digest it faster in the old >> form

Re: New "function tables" in V13 documentation

po 9. 11. 2020 v 21:35 odesílatel Alvaro Herrera napsal: > > On 2020-Nov-09, Adrian Klaver wrote: > > > > If you have suggestion on how to improve the new format, I'm sure we can > > > discuss that. It seems pretty clear to me that we're not going back to > > > the old format. > > > > Improve it

Re: New "function tables" in V13 documentation

On Mon, Nov 9, 2020 at 1:33 PM Adrian Klaver wrote: > On 11/9/20 12:06 PM, Alvaro Herrera wrote: > > > If you have suggestion on how to improve the new format, I'm sure we can > > discuss that. It seems pretty clear to me that we're not going back to > > the old format. > > Improve it by going b

Re: New "function tables" in V13 documentation

On 2020-Nov-09, Adrian Klaver wrote: > > If you have suggestion on how to improve the new format, I'm sure we can > > discuss that. It seems pretty clear to me that we're not going back to > > the old format. > > Improve it by going back to old format. Not sure why that is not open to > discussi

Re: New "function tables" in V13 documentation

On 11/9/20 12:06 PM, Alvaro Herrera wrote: On 2020-Nov-08, Adrian Klaver wrote: On 11/8/20 1:57 PM, Thomas Kellerer wrote: In case someone is interested: there is a little discussion going on on Reddit whether the new format of presenting functions in V13 is a step backwards: https://ww

Re: Foreign Data Wrapper Handler

On 11/9/20 9:21 AM, Susan Hurst wrote: Thanks for the detailed instructions, Laurenz! "The foreign server encapsulates the connection string to access a remote PostgreSQL database.  Define one per remote database you want to access." Where do I define "one per remote database"?.in pg_hba.co

Execution plan does not use index

Hello, Consider the following PostgreSQL 9.6.18 tables - measurement_value: time series table with a unique key on (device_id,timestamp) columns and a number of columns with measurements. Table contains a large number of rows (>150million) - device table: with device properties (short_id joins to

Re: New "function tables" in V13 documentation

On 2020-Nov-08, Adrian Klaver wrote: > On 11/8/20 1:57 PM, Thomas Kellerer wrote: > > In case someone is interested: there is a little discussion going on on > > Reddit whether the new format of presenting functions in V13 is a step > > backwards: > > > > > > https://www.reddit.com/r/Postgre

Re: New "function tables" in V13 documentation

On Mon, 9 Nov 2020 at 02:54, Adrian Klaver wrote: > On 11/8/20 1:57 PM, Thomas Kellerer wrote: > > In case someone is interested: there is a little discussion going on on > > Reddit whether the new format of presenting functions in V13 is a step > > backwards: > > > > > > > https://www.reddit.com

Re: After vacuum application runs very slow ? is this common behavior ?

Thank you for your response. On Fri, Nov 6, 2020 at 11:14 PM Adrian Klaver wrote: > On 11/6/20 8:20 PM, Sri Linux wrote: > > Hi All, > > > > Our production database size is about 2TB and we had run into issues and > > Postgres log did recommend running the vacuum in single-user mode. We > > have

Re: Temporary tables usage in functions

po 9. 11. 2020 v 18:19 odesílatel Michael Lewis napsal: > Also may I know if excessive use of temporary tables may cause locks? >>> >> >> Usually there are no problems with locks, but there is a problem with >> system tables bloating. Creating and dropping temp tables is expensive like >> creatin

Re: Foreign Data Wrapper Handler

On Mon, 2020-11-09 at 11:21 -0600, Susan Hurst wrote: > "The foreign server encapsulates the connection string to access a remote > PostgreSQL database. Define one per remote database you want to access." > > Where do I define "one per remote database"?.in pg_hba.conf? No, in SQL: CREATE

Re: Foreign Data Wrapper Handler

Thanks for the detailed instructions, Laurenz! "The foreign server encapsulates the connection string to access a remote PostgreSQL database. Define one per remote database you want to access." Where do I define "one per remote database"?.in pg_hba.conf? ---

Re: Temporary tables usage in functions

> > Also may I know if excessive use of temporary tables may cause locks? >> > > Usually there are no problems with locks, but there is a problem with > system tables bloating. Creating and dropping temp tables is expensive like > creating or dropping normal tables. > Dropping a real table require

Re: Foreign Data Wrapper Handler

On Sun, 2020-11-08 at 13:09 -0600, Susan Hurst wrote: > The first pass thru https://www.postgresql.org/docs/12/fdwhandler.html > does not tell me what I think I need to know, but I will digest this > more thoroughly. Maybe I need to understand more of the lingo re: > foreign data wrappers. I do

Re: initdb --data-checksums

Hi Matt, > On 09. Nov, 2020, at 18:00, Matt Zagrabelny wrote: > > Hello, > > I see the --data-checksums option for initdb. Is it possible to use > --data-checksums after the cluster has been initialized? I'm guessing "not", > but thought I'd ask. > > I'm running v12 on Debian. > > Thanks fo

initdb --data-checksums

Hello, I see the --data-checksums option for initdb. Is it possible to use --data-checksums after the cluster has been initialized? I'm guessing "not", but thought I'd ask. I'm running v12 on Debian. Thanks for any help! -m

Re: Database system was interrupted. Possible reasons for a database to suddenly stop accepting connections?

On Mon, 2020-11-09 at 13:53 +, Buzenets, Yuriy (GE Renewable Energy, consultant) wrote: If I delete all the noise from the log, file, this remains: > < 2020-10-29 11:51:59.345 PDT >STATEMENT: SELECT NULL AS TABLE_CAT, > n.nspname AS TABLE_SCHEM, [...] > < 2020-10-29 12:04:09.700 PDT >LOG:

Re: How to set up a schema default date to '2020-01-01'?

On Mon, Nov 9, 2020 at 9:08 AM mark armon <1994hej...@gmail.com> wrote: > > How to set up a schema default date (now) to '2020-01-01'? Whatever > timezone would be OK. > What is a "schema default" (date or otherwise)? David J.

How to set up a schema default date to '2020-01-01'?

How to set up a schema default date (now) to '2020-01-01'? Whatever timezone would be OK.

Re: database aliasing options ?

David Gauthier writes: > Our IT dept has configured our PG DB as a "High Availability" database. It > has a primary and backup server (disks too). Normally both are running but > if one goes down, the other is still available for use, effectively keeping > the DB up while the failed server is be

database aliasing options ?

Hi: version 11.5 on linux. Our IT dept has configured our PG DB as a "High Availability" database. It has a primary and backup server (disks too). Normally both are running but if one goes down, the other is still available for use, effectively keeping the DB up while the failed server is being

Re: Different result behavior when using ANY(ARRAY(SELECT)) and IN (SELECT)

Davide Jensen writes: > I'm encountering some problems in understanding the behavior of a query > that uses an IN operator, the query i'm executing is the following: > SELECT * FROM ( > SELECT _id, > ROW_NUMBER() OVER () AS _rownumber I think your problem is

Re: Database system was interrupted. Possible reasons for a database to suddenly stop accepting connections?

On 11/9/20 5:53 AM, Buzenets, Yuriy (GE Renewable Energy, consultant) wrote: Some time ago the database at my work suddenly stopped accepting connections. In the logs there was a message “the database system was interrupted; last known up at 2020-10-29 12:03:16 PDT”, followed by a lot of “the d

Re: RAISE INFO in function

po 9. 11. 2020 v 14:46 odesílatel Yambu napsal: > Hi > > May i know if RAISE INFO impacts performance significantly in a function? > > Should i comment them out once i'm done using/debugging ? > It depends on more factors - but expressions in RAISE statements are calculated every time and someti

Database system was interrupted. Possible reasons for a database to suddenly stop accepting connections?

Some time ago the database at my work suddenly stopped accepting connections. In the logs there was a message "the database system was interrupted; last known up at 2020-10-29 12:03:16 PDT", followed by a lot of "the database system is starting up" messages. It seems like the database tried to r

RAISE INFO in function

Hi May i know if RAISE INFO impacts performance significantly in a function? Should i comment them out once i'm done using/debugging ? regards

Re: Temporary tables usage in functions

Hi po 9. 11. 2020 v 13:07 odesílatel Yambu napsal: > Hi > > May I know if a temporary table is dropped at the end of a function? > PostgreSQL temporary tables can be dropped on the end of transaction or end of session. > > Also may I know if excessive use of temporary tables may cause locks?

Re: Temporary tables usage in functions

po 9. 11. 2020 v 13:07 odesílatel Yambu napsal: > > Hi > > May I know if a temporary table is dropped at the end of a function? Check https://www.postgresql.org/docs/12/sql-createtable.html#SQL-CREATETABLE-TEMPORARY, especially the "ON COMMIT" part. > > Also may I know if excessive use of tempo

Temporary tables usage in functions

Hi May I know if a temporary table is dropped at the end of a function? Also may I know if excessive use of temporary tables may cause locks? regards

Different result behavior when using ANY(ARRAY(SELECT)) and IN (SELECT)

Hi everyone, I'm encountering some problems in understanding the behavior of a query that uses an IN operator, the query i'm executing is the following: SELECT * FROM ( SELECT _id, ROW_NUMBER() OVER () AS _rownumber FROM (

Backup Restore from other node after switchover/failover

Hi, haven’t tested it yet but maybe I can get a quick answer here. We have discussed the following scenario. Few nodes as streaming replication cluster all in sync with taking backup only from one dedicated node. Now that node which is responsible for the backups goes down. For sure I have a full