Re: rename table between schema with one command

On Wednesday, July 24, 2019, Alex wrote: > for example we have table t1 under schema s1. can I rename it to s2.t2 > with one command. > > currently I can do: > > alter table s1.t1 set schema s2; > alter table s2.t1 rename to t2. > No. AFAIK alter table is the obly sql command that can do those

Re: Request for resolution || Support

Another option would be an app that is constantly connected to Postgres using LISTEN/NOTIFY. On Jul 24, 2019, 22:34 -0700, jay chauhan , wrote: > Hi Thomas, David/Team, > > Thanks you for your response. However we need your confirmation whether my > Error/issue as mentioned below will be resolved

Re: Request for resolution || Support

Hi Thomas, David/Team, Thanks you for your response. However we need your confirmation whether my Error/issue as mentioned below will be resolved if we upgrade our PostgreSQL Version. <> *David response: *Use a newer version *Tomas response:* Yeah, you should use release 11 for a new project. My

rename table between schema with one command

for example we have table t1 under schema s1. can I rename it to s2.t2 with one command. currently I can do: alter table s1.t1 set schema s2; alter table s2.t1 rename to t2.

Re: Running concurrent txns and measuring the timings in Postgres

On 7/24/19 2:24 PM, Souvik Bhattacherjee wrote: I got this thing running and hopefully works as expected. The txns are stored in insert_txn1.sql, insert_txn2.sql, ... Please let me know if you find any issues with this. Script is attached. I'm hardly a BASH guru so someone else will need to c

Re: Running concurrent txns and measuring the timings in Postgres

I got this thing running and hopefully works as expected. The txns are stored in insert_txn1.sql, insert_txn2.sql, ... Please let me know if you find any issues with this. Script is attached. On Wed, Jul 24, 2019 at 5:11 PM Adrian Klaver wrote: > On 7/24/19 1:52 PM, Souvik Bhattacherjee wrote:

Re: Running concurrent txns and measuring the timings in Postgres

On 7/24/19 1:52 PM, Souvik Bhattacherjee wrote: >Well it depends on the part you have not filled in, what client(s) you > are using and how the transactions are being generated? Using a psql client and txns are generated manually at this point. Each txn is stored separately in a .sql file and

Re: Running concurrent txns and measuring the timings in Postgres

>Well it depends on the part you have not filled in, what client(s) you > are using and how the transactions are being generated? Using a psql client and txns are generated manually at this point. Each txn is stored separately in a .sql file and are fired from different psql sessions, if that help

Re: Running concurrent txns and measuring the timings in Postgres

On 7/24/19 1:42 PM, Souvik Bhattacherjee wrote: > The duplicate elimination is being handled by ON CONFLICT or some custom > process in the code generating the transactions? Yes, we used ON CONFLICT for that. Thanks btw. > If the transactions are being created from a single app/script could

Re: Running concurrent txns and measuring the timings in Postgres

> The duplicate elimination is being handled by ON CONFLICT or some custom > process in the code generating the transactions? Yes, we used ON CONFLICT for that. Thanks btw. > If the transactions are being created from a single app/script could you > not just use 'timing' to mark the beginning of

Re: Default ordering option

On 2019-07-23 17:43, Cyril Champier wrote: > In this documentation > , it is said: > > If sorting is not chosen, the rows will be returned in an > unspecified order. The actual order in that case will depend on the > scan and join

Re: Running concurrent txns and measuring the timings in Postgres

On 7/24/19 12:22 PM, Souvik Bhattacherjee wrote: > It would help to know what problem you are trying to solve? Multiple txns are inserting tuples into a table concurrently. Wanted to measure the total time taken to complete the insertion process. Some txns overlap with others on the tuples t

Re: Running concurrent txns and measuring the timings in Postgres

> On Jul 24, 2019, at 1:22 PM, Souvik Bhattacherjee wrote: > > > It would help to know what problem you are trying to solve? > > Multiple txns are inserting tuples into a table concurrently. Wanted to > measure > the total time taken to complete the insertion process. Some txns overlap > wit

Re: partition table slow planning

Thanks Imre On Wed., Jul. 24, 2019, 3:23 p.m. Imre Samu, wrote: > > PostgreSQL 11.3 ... Total number of partition is 367 Partition > key: LIST > > As I know: > in PG11 "Declarative Partitioning Best Practices" > *... " The query planner is generally able to handle partition hierarchies > w

Re: partition table slow planning

> PostgreSQL 11.3 ... Total number of partition is 367 Partition key: LIST As I know: in PG11 "Declarative Partitioning Best Practices" *... " The query planner is generally able to handle partition hierarchies with up to a few hundred partitions fairly well, provided that typical queries a

Re: Running concurrent txns and measuring the timings in Postgres

> It would help to know what problem you are trying to solve? Multiple txns are inserting tuples into a table concurrently. Wanted to measure the total time taken to complete the insertion process. Some txns overlap with others on the tuples they insert. Duplicate tuples are not inserted. On Wed,

Re: Too slow to create new schema and their tables, functions, triggers.

On 7/24/19 11:33 AM, PegoraroF10 wrote: I did not have vacuumed or reindexed my database for last 30 days and that was my problem. Autovacuum should be dealing with this. Do you have it throttled in some manner? It works fine if I do a reindex database before adding that new schema. Well, I´

Re: Too slow to create new schema and their tables, functions, triggers.

I did not have vacuumed or reindexed my database for last 30 days and that was my problem. It works fine if I do a reindex database before adding that new schema. Well, I´ll try just reindexing system before adding a new schema to see if it works. -- Sent from: https://www.postgresql-archive.

Re: Running concurrent txns and measuring the timings in Postgres

On 7/24/19 9:54 AM, Souvik Bhattacherjee wrote: Hi, Is there a standard procedure to execute two or more concurrent txns at the same time? I understand that if we want to run concurrent txns, we need to execute them from different psql sessions. But how do we make sure that they begin executi

Sequential scan with composite type in primary key

Hello, With this schema: CREATE TYPE item AS ( name text, date date ); CREATE TABLE item_comment ( item item, user_id text, comment text, CONSTRAINT item_comment_pk PRIMARY KEY (item, user_id) ); And this query: EXPLAIN SELECT * FROM item_comment WHERE item = (''

Running concurrent txns and measuring the timings in Postgres

Hi, Is there a standard procedure to execute two or more concurrent txns at the same time? I understand that if we want to run concurrent txns, we need to execute them from different psql sessions. But how do we make sure that they begin execution almost at the same time. Also, I'm interested in

Re: after restore the size of the database is increased

Hi all, this should be trivial, but if I dump and restore the very same database the restored one is bigger than the original one. I did vacuumed the database foo, then dumped and restored into bar, and the latter, even when vacuumed, remains bigger then the original one. No other activity was

Re: Default ordering option

On 7/24/19 8:22 AM, Cyril Champier wrote: Indeed, you are right, I do my test in pure sql and via ruby ActiveRecord, and I must had been confused, the behaviour is correct in sql, it must have been a cache thing in ActiveRecord that prevented the reordering. But meanwhile, I tested on our whole

Re: Default ordering option

Indeed, you are right, I do my test in pure sql and via ruby ActiveRecord, and I must had been confused, the behaviour is correct in sql, it must have been a cache thing in ActiveRecord that prevented the reordering. But meanwhile, I tested on our whole CI, and it took twice the normal time with up

Re: postgres 9.5 DB corruption

On 7/24/19 7:38 AM, Thomas Tignor wrote: Hello postgres community, Writing again to see if there are insights on this issue. We have had infrequent but recurring corruption since upgrading from postgres 9.1 to postgres 9.5. We are presently on 9.5.16. Our DB-facing app continually performs a

Re: postgres 9.5 DB corruption

On 7/24/19 7:38 AM, Thomas Tignor wrote: Hello postgres community, Writing again to see if there are insights on this issue. We have had infrequent but recurring corruption since upgrading from postgres 9.1 to postgres 9.5. We are presently on 9.5.16. Our DB-facing app continually performs a

Re: Default ordering option

On 7/24/19 1:45 AM, Cyril Champier wrote: Thanks for your answers. Unfortunately the update trick only seems to work under certain conditions. I do this to shuffle my patients table: UPDATE "patients" SET "updated_at" = NOW() WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER

Re: partition table slow planning

PostgreSQL 11.3 (Ubuntu 11.3-1.pgdg18.04+1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 7.4.0-1ubuntu1~18.04) 7.4.0, 64-bit' Total number of partition is 367 Parent table defination Table "public.itinerary" Column |Type | Collati

postgres 9.5 DB corruption

Hello postgres community, Writing again to see if there are insights on this issue. We have had infrequent but recurring corruption since upgrading from postgres 9.1 to postgres 9.5. We are presently on 9.5.16. Our DB-facing app continually performs a mixture of DML, primarily inserts and update

Re: Query plan: SELECT vs INSERT from same select

> On 23 Jul 2019, at 22:29, Alexander Voytsekhovskyy > wrote: > > I have quite complicated query: > > SELECT axis_x1, axis_y1, SUM(delivery_price) as v_1 FROM ( > SELECT to_char(delivery_data.delivery_date, '-MM') as axis_x1, > clients.id_client as axis_y1, delivery_data.amount * produc

Re: partition table slow planning

>*Can we know why this is happening?* Please give us - more info about your system: - PG version? - number of partitions? - any other important? for example - in PG 11.2 Changes: "Improve planning speed for large inheritance or partitioning table groups (Amit Langote, Etsuro Fujita)" https://www.

Re: Default ordering option

Thanks for your answers. Unfortunately the update trick only seems to work under certain conditions. I do this to shuffle my patients table: UPDATE "patients" SET "updated_at" = NOW() WHERE "patients"."id" = (SELECT "patients"."id" FROM "patients" ORDER BY random() LIMIT 1) Then indeed, this quer

partition table slow planning

We encounter a issue when we do query on partition table directly with proper partition key provide. postgres able to find problem partition but when I do explain plan it showing 95% spend on planning the execution . Here is example itinerary=# EXPLAIN ANALYZE SELECT * FROM itinerary WHERE destinat