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

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

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'?

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

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

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: > > >

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

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 >>

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

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

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

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

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

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 >>

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

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 >

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:

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

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

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: > > > > > >

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: > > > > > > >

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 > >

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 >>

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:

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

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

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

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.

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

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

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

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

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

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 (