Multicolumn index for single-column queries?

Hi. Say there are 2 indexes: "foo_index" btree (foo_id) "multi_index" btree (foo_id, approved, expires_at) foo_id is an integer. Some queries involve all three columns in their WHERE clauses, some involve only foo_id. Would it be ok from general performance standpoint to remove foo_in

Re: ERROR: operator class "gin__int_ops" does not exist for access method "gin"

Thank you! All works now From: Adrian Klaver Sent: Wednesday, April 17, 2019 5:23 PM To: Julie Nishimura; pgsql-general@lists.postgresql.org Subject: Re: ERROR: operator class "gin__int_ops" does not exist for access method "gin" On 4/17/19 5:18 PM, Julie Nishim

Re: Method to pass data between queries in a multi-statement transaction

On Wed, Apr 17, 2019 at 5:30 PM Souvik Bhattacherjee wrote: > > What if I need the result of the join to be stored into table3 as well > as the tuples that participated in the query to be deleted from table1. The > following can be done without the need to transfer values from the previous > quer

Re: is it possible to create partitioned tables using tables from different schemas

Thank you David, I will use list. On Wed, Apr 17, 2019 at 6:42 PM David Rowley wrote: > On Thu, 18 Apr 2019 at 10:19, Adrian Klaver > wrote: > > CREATE TABLE automatic.measurement_automatic PARTITION OF > > automatic.measurement > > test-# FOR VALUES FROM (1) TO (1) > > test-# PARTITION

Re: is it possible to create partitioned tables using tables from different schemas

you are right. What it happens is that the batch importing process drops the schema and recreates it. I would like some solution that is compatible with that. I am sure partitioned tables will have an impact but on the other hand, it will solve the problem I have now without touching the legacy co

Re: is it possible to create partitioned tables using tables from different schemas

On Thu, 18 Apr 2019 at 10:19, Adrian Klaver wrote: > CREATE TABLE automatic.measurement_automatic PARTITION OF > automatic.measurement > test-# FOR VALUES FROM (1) TO (1) > test-# PARTITION BY RANGE (origin); > ERROR: empty range bound specified for partition "measurement_automatic" > DET

Re: is it possible to create partitioned tables using tables from different schemas

On Thu, 18 Apr 2019 at 10:56, Alvaro Herrera wrote: > > Note that unless you regularly query for only-manually-inserted or > only-automatically-inserted data, this will be useless and will make > queries more expensive, with no upside. Going by "Since those rows are inserted by hand, they will be

Re: Method to pass data between queries in a multi-statement transaction

One can argue here that we can use the results from table3 to perform the delete operation instead of joining again. But table3 may be subject to updates which makes the situation tricky. On Wed, Apr 17, 2019 at 8:30 PM Souvik Bhattacherjee wrote: > There are few if any situations where you need

Re: Method to pass data between queries in a multi-statement transaction

There are few if any situations where you need to immediately and completely pass all values from one query to another in the same transaction where the queries cannot just be combined into a single statement. Your representative example is one that is easily combined into a single statement. > W

Re: ERROR: operator class "gin__int_ops" does not exist for access method "gin"

On 4/17/19 5:18 PM, Julie Nishimura wrote: We migrated some tables from 8.3 postgresql to 9.4, and when I compared tables DDL between environments, I've noticed some indices are missing (majority of them are using GIN When I am taking 8.3 ddl for it:   CREATE INDEX serp_test2   ON serp_test

ERROR: operator class "gin__int_ops" does not exist for access method "gin"

We migrated some tables from 8.3 postgresql to 9.4, and when I compared tables DDL between environments, I've noticed some indices are missing (majority of them are using GIN When I am taking 8.3 ddl for it: CREATE INDEX serp_test2 ON serp_test USING gin (domainids gin__int_ops); it giv

Re: is it possible to create partitioned tables using tables from different schemas

On 4/17/19 3:56 PM, Alvaro Herrera wrote: Note that unless you regularly query for only-manually-inserted or only-automatically-inserted data, this will be useless and will make queries more expensive, with no upside. Well the OP's use case is: "I would like to store in that table manually gen

Re: is it possible to create partitioned tables using tables from different schemas

Note that unless you regularly query for only-manually-inserted or only-automatically-inserted data, this will be useless and will make queries more expensive, with no upside. Generally speaking, it's not a problem to put partitions in different schemas. -- Álvaro Herrerahttps://

Re: is it possible to create partitioned tables using tables from different schemas

Thank you! This is exactly was I was looking for. The range thing is good enough for me. Pablo On Wed, Apr 17, 2019 at 3:19 PM Adrian Klaver wrote: > On 4/17/19 2:21 PM, pabloa98 wrote: > > I have a schema with a generated table with information coming from > > batch processes. > > > > I would

Re: Method to pass data between queries in a multi-statement transaction

On 4/17/19 3:04 PM, Souvik Bhattacherjee wrote: Hello, I'm trying to pass some values between queries in a multi-statement transaction. For example, consider the following representative multi-statement transaction: begin; select * from table1 t1, table2 t2 where t1.cid = t2.cid; delete from

Re: is it possible to create partitioned tables using tables from different schemas

On 4/17/19 2:21 PM, pabloa98 wrote: I have a schema with a generated table with information coming from batch processes. I would like to store in that table manually generated information. Since those rows are inserted by hand, they will be lost when the table will be reimported. So I was t

Re: Method to pass data between queries in a multi-statement transaction

On Wed, Apr 17, 2019 at 3:04 PM Souvik Bhattacherjee wrote: > Hello, > > I'm trying to pass some values between queries in a multi-statement > transaction. For example, consider the following representative > multi-statement transaction: > > begin; > select * from table1 t1, table2 t2 where t1.ci

Method to pass data between queries in a multi-statement transaction

Hello, I'm trying to pass some values between queries in a multi-statement transaction. For example, consider the following representative multi-statement transaction: begin; select * from table1 t1, table2 t2 where t1.cid = t2.cid; delete from table1 where cid in (values-to-be-populated-from-the

is it possible to create partitioned tables using tables from different schemas

I have a schema with a generated table with information coming from batch processes. I would like to store in that table manually generated information. Since those rows are inserted by hand, they will be lost when the table will be reimported. So I was thinking of creating a partitioned table wi

Re: Forcing index usage

On Wed, Apr 17, 2019 at 11:16:28AM -0600, Michael Lewis wrote: > > * Michael Lewis (mle...@entrata.com) wrote: > > > Thanks for that advance warning since it is a handy option to force > the > > > planning barrier in my experience. What's a resource to see other > coming > > >

Re: Forcing index usage

> > > * Michael Lewis (mle...@entrata.com) wrote: > > > Thanks for that advance warning since it is a handy option to force the > > > planning barrier in my experience. What's a resource to see other > coming > > > changes in v12 especially changes to default behavior like this? Will > there > > >

Re: Forcing index usage

On Wed, Apr 3, 2019 at 03:28:48PM -0400, Stephen Frost wrote: > Greetings, > > * Michael Lewis (mle...@entrata.com) wrote: > > Thanks for that advance warning since it is a handy option to force the > > planning barrier in my experience. What's a resource to see other coming > > changes in v12 es

Re: Move vs. copy table between databases that share a tablespace?

On Wed, Apr 3, 2019 at 10:10:54AM -0400, Tom Lane wrote: > --- *carefully* --- to find out how to identify the right physical > files. > > A few foot-guns I can think of: > > * Making an identically-declared table might be more complicated than > you'd think, if the table has had any ALTERs done

Re: PostgreSQL ping/pong to client

Francisco Olarte writes: > On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap > wrote: >> Correction: I meant when my java application dies postgres should break all >> the connections that were associated with that peer. > And how is the server supposed to detect that without keepalives? TCP > is de

Re: PostgreSQL ping/pong to client

On Wed, Apr 17, 2019 at 4:49 PM Ajay Pratap wrote: > Correction: I meant when my java application dies postgres should break all > the connections that were associated with that peer. And how is the server supposed to detect that without keepalives? TCP is dessigned to survice for extended perio

Re: Alter domain type / avoiding table rewrite

On 4/17/19 8:34 AM, Tim Kane wrote: On Wed, 17 Apr 2019 at 15:23, Adrian Klaver > wrote: The table definition and the size of the data set would help with interpreting the below. The below example shows the issue on a table with just a single fi

Re: Alter domain type / avoiding table rewrite

On Wed, 17 Apr 2019 at 15:23, Adrian Klaver wrote: > > > The table definition and the size of the data set would help with > interpreting the below. The below example shows the issue on a table with just a single field. I can demonstrate the problem wether there are 100 records or a million r

Re: PostgreSQL ping/pong to client

Correction: I meant when my java application dies postgres should break all the connections that were associated with that peer. On Wed, Apr 17, 2019, 19:59 Adrian Klaver wrote: > On 4/17/19 5:39 AM, Ajay Pratap wrote: > > Hello there, > > I am using PostgreSQL 10.7 as the database, and have Ja

Re: PostgreSQL ping/pong to client

Adrian Klaver writes: > On 4/17/19 5:39 AM, Ajay Pratap wrote: >> I am using PostgreSQL 10.7 as the database, and have Java web app. My >> app takes a lock on the database using the following command whenever my >> server is starting. >> /select /pg_try_advisory_lock/(100)/ >> To make sure only

Re: Possible corrupt index?

On 4/17/19 7:28 AM, Zahir Lalani wrote: Any changes occur between the time it worked and the time it did not? Say a crash, change in schema, new data added and so on. Not as far as we can tell - one of my DB team noticed it day before yesterday - there were no tell tale signs of any issues b

Re: PostgreSQL ping/pong to client

On 4/17/19 5:39 AM, Ajay Pratap wrote: Hello there, I am using PostgreSQL 10.7 as the database, and have Java web app. My app takes a lock on the database using the following command whenever my server is starting. /select /pg_try_advisory_lock/(100)/ To make sure only one instance of the App

RE: Possible corrupt index?

>Any changes occur between the time it worked and the time it did not? >Say a crash, change in schema, new data added and so on. Not as far as we can tell - one of my DB team noticed it day before yesterday - there were no tell tale signs of any issues but we have seen issues yesterday with cer

Re: Alter domain type / avoiding table rewrite

On 4/17/19 2:14 AM, Tim Kane wrote: On Tue, 16 Apr 2019 at 18:04, Adrian Klaver > wrote: Where are you seeing the rewrite in your case? I’m unfamiliar with ctid (which I now plan to read up on)… I’ve been looking at /relfilenode/ I’ve observed

Re: Possible corrupt index?

On 4/17/19 12:42 AM, Zahir Lalani wrote: I am not following above. What is running version 10? Production is 9.6, others are 10 (as we are testing). Prod has always been fine, its been running for 2 years without a hitch. This issue has only occurred recently Any changes occur between the t

Re: text search configuration missing while migration from 8.3 to 9.4

On 4/16/19 10:51 PM, Julie Nishimura wrote: Thank you. Is there a way to find out if I have tables with tsvectors or indexes? I have hundreds of tables... I'm afraid I know the answers, but I will ask anyway: Is there any documentation to go along with the database? Or, is the schema in some

Re: text search configuration missing while migration from 8.3 to 9.4

Julie Nishimura writes: > Thank you. Is there a way to find out if I have tables with tsvectors or > indexes? I have hundreds of tables... Search the system catalogs. I'd probably do it like this: select attrelid::regclass, attname from pg_attribute where atttypid = 'tsvector'::regtype; but

Re: upgrade issue 10 to 11

On 4/17/19 4:08 AM, Prakash Ramakrishnan wrote: Hi Team, While using pg_upgrade 10 to 11 version we are getting below error.Please do the needful. postg...@hamrhenppp001.enterprisenet.org:/BACKUP/hamrhenppp001/upgrade_logs ==> /usr/pgsql-11/bin/pg_upgrade -d /DATA/data10 -D /DATA/data11 -b /

PostgreSQL ping/pong to client

Hello there, I am using PostgreSQL 10.7 as the database, and have Java web app. My app takes a lock on the database using the following command whenever my server is starting. *select pg_try_advisory_lock(100)* To make sure only one instance of the App is using the database at a time. There have be

upgrade issue 10 to 11

Hi Team, While using pg_upgrade 10 to 11 version we are getting below error.Please do the needful. postg...@hamrhenppp001.enterprisenet.org:/BACKUP/hamrhenppp001/upgrade_logs ==> /usr/pgsql-11/bin/pg_upgrade -d /DATA/data10 -D /DATA/data11 -b /opt/app/PostgreSQL/10/bin -B /usr/pgsql-11/bin -p 543

Re: Alter domain type / avoiding table rewrite

On Tue, 16 Apr 2019 at 18:04, Adrian Klaver wrote: > > > Where are you seeing the rewrite in your case? I’m unfamiliar with ctid (which I now plan to read up on)… I’ve been looking at *relfilenode* I’ve observed that relfilenode changes when altering from *old_type * *à varchar(9) *and the op

RE: Possible corrupt index?

>I am not following above. >What is running version 10? Production is 9.6, others are 10 (as we are testing). Prod has always been fine, its been running for 2 years without a hitch. This issue has only occurred recently >Did you restore a dump from a version 10 onto a >version 9.6 machine? No