Re: How to reference a composite type in schemas not "public"?

Sorry I was in the wrong db last time TESTDB=# \dn | ?? -+-- MjorTbl | postgres Rate| postgres public | postgres -- Original -- From: "Adrian Klaver";; Date: Jun 14, 2018 To: "a"<372660...@qq.com>;

Re: Using GIT to caught delta between database versions

thank you Adrian, Best, Jacek 2018-06-13 21:09 GMT+02:00 Adrian Klaver : > On 06/13/2018 11:13 AM, Łukasz Jarych wrote: > >> Hi Adrian, >> >> thank you . >> >> why i should use sqitch or any other software to catch the delta? >> >> When i will export plan-text SQL , git should catch delta

Re: How to reference a composite type in schemas not "public"?

Hey thank you~ postgres=# \dn | ?? -+-- pgagent | postgres public | postgres (2 ??) -- Original -- From: "Adrian Klaver"; Date: Thursday, Jun 14, 2018 12:06 PM To: "a"<372660...@qq.com>;

Re: How to reference a composite type in schemas not "public"?

On 06/13/2018 08:34 PM, a wrote: Hi I have created some composite type: create type "MjorTbl".mort as( adjfac    float8, tablename text, subtype   text, improv  float8, selfac    slfc ); The schema is different from public, while I would like to create table using the composite type, it

Re: Impact of multixact "members" limit exceeded

Hello Álvaro, I'm running at 9.4.3, I know its an older version but upgrading it outside the scope right now for various reasons. Based on the settings I posted what would be your recommended settings right now to resolve this situation? Maintenance is limited to weekends as this is a database

How to reference a composite type in schemas not "public"?

Hi I have created some composite type: create type "MjorTbl".mort as( adjfacfloat8, tablename text, subtype text, improvfloat8, selfacslfc ); The schema is different from public,

Re: Impact of multixact "members" limit exceeded

On 2018-Jun-13, Daniel Lagerman wrote: > Hello! > > I have a question where I'm trying to determine the seriousness of the > problem. What's your server version? The way to reclaim members space is to move forwards the pg_database.datminmxid value from the database where it is oldest; and the

Re: Merging two database dumps

Thanks for the clarification On Wed, Jun 13, 2018 at 9:32 AM, Adrian Klaver wrote: > On 06/13/2018 06:21 AM, Alex O'Ree wrote: > >> Desired behavior is to just log the error and continue the import using >> pgdump based copy commands >> > > Each COPY is atomic so if any part of it fails the

Impact of multixact "members" limit exceeded

Hello! I have a question where I'm trying to determine the seriousness of the problem. I repacked a table to give space back to the OS and that was fine. However since then I just noted that I see multixact "members" limit exceeded every time there is an insert on that table. A Vacuum has yet to

Re: Slow planning time for simple query

13.06.2018 12:40, Maksim Milyutin wrote: On 09.06.2018 22:49, Tom Lane wrote: Maksim Milyutin writes: On hot standby I faced with the similar problem. ... is planned 4.940 ms on master and *254.741* ms on standby. (I wonder though why, if you executed the same query on the master, its

Re: Replica string comparsion issue

Andrey Lizenko writes: > Another point regarding collations: query with ORDER BY returns different > record set on master and on replica if more than one column used: When I saw Peter's reply, I figured he'd nailed it, and this seems to be more evidence pointing in that direction. If the

Re: Replica string comparsion issue

Re-indexing didn't help. Will check via amcheck. Another point regarding collations: query with ORDER BY returns different record set on master and on replica if more than one column used: Master: > =# select date_started, symbol from raw.symbols_aggregates order by 1,2 > limit 5; >

Re: Replica string comparsion issue

Andrey Lizenko writes: > I'm observing strange behaviour on comparing ::text field with string while > quering replica. These symptoms seem consistent with the theory that the replica's index for that column (symbols_aggregates_uniq) is corrupt. I think your casts etc are just serving to defeat

Re: Replica string comparsion issue

On Wed, Jun 13, 2018 at 1:10 PM, Andrey Lizenko wrote: > I'm observing strange behaviour on comparing ::text field with string while > quering replica. The first thing to do here is to install amcheck on the replica, and verify that the indexes are consistent with the replica's own notion of how

Replica string comparsion issue

Hello, I'm observing strange behaviour on comparing ::text field with string while quering replica. Here is the table structure: =# \dS+ raw.symbols_aggregates > Table > "raw.symbols_aggregates" >Column | Type | Collation |

Re: Using GIT to caught delta between database versions

On 06/13/2018 11:13 AM, Łukasz Jarych wrote: Hi Adrian, thank you . why i should use sqitch or any other software to catch the delta? When i will export plan-text SQL , git should catch delta automatically? Can you please explain it? You are correct Git will catch the changes in the plain

Re: Using GIT to caught delta between database versions

Hi Adrian, thank you . why i should use sqitch or any other software to catch the delta? When i will export plan-text SQL , git should catch delta automatically? Can you please explain it? Best, Jacek 2018-06-13 19:47 GMT+02:00 Adrian Klaver : > On 06/13/2018 10:42 AM, Łukasz Jarych wrote:

Re: Using GIT to caught delta between database versions

On 06/13/2018 10:42 AM, Łukasz Jarych wrote: Hi Guys, how use git to catch delta between database versions? Use sqitch?: https://sqitch.org/ As version i will do schemas. So we have version one, finishing it, and this version should be put in GIT. And created schema for the version.

Unable to terminate process started from PLV8 function

Hello Team, We are trying to use PLV8 function to calculate the histograms and while testing the function we found that with some of the data, function was going into the infinite loop. We tried to kill the session using pg_terminate_backend(pid) but process could not get killed, we terminated

Using GIT to caught delta between database versions

Hi Guys, how use git to catch delta between database versions? As version i will do schemas. So we have version one, finishing it, and this version should be put in GIT. And created schema for the version. Which formats i should use? pg_dump creates only binary files? (i should use text file

RE: Issues on 9.5 to 9.6 Upgrade.

Ok, this seemed to solve it:> vacuumdb -zv ; reindexdb -vAnd we are back to full speed Thanks for pointing out the ANALYZE ...Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212"Never attribute to malice, that which can be adequately explained by stupidity"-

Re: Issues on 9.5 to 9.6 Upgrade.

On 06/13/2018 09:44 AM, b...@linux4ms.net wrote: Analyze the DB exactly how? https://www.postgresql.org/docs/10/static/sql-analyze.html I did run a reindexdb after migration to no affect. Yeah the PLANS are a big difference Ben Duncan - Business Network Solutions, Inc. 336 Elton Road

RE: Issues on 9.5 to 9.6 Upgrade.

Analyze the DB exactly how?I did run a reindexdb after migration to no affect.Yeah the PLANS are a big difference Ben Duncan - Business Network Solutions, Inc. 336 Elton Road Jackson MS, 39212"Never attribute to malice, that which can be adequately explained by stupidity"- Hanlon's Razor

Re: Issues on 9.5 to 9.6 Upgrade.

On Wed, Jun 13, 2018 at 11:16 AM, wrote: > > > Can anyoen explain WHY there is such a big difference? Is it the SQL > statement or a bug in the pgdump ? > > Did you analyze the database after upgrading? Or at least the tables in question? Those are very different plans. -- Don Seiler

Issues on 9.5 to 9.6 Upgrade.

Servers: Redhack 6.9 64 bit.Postgres OLD Server version 9.5.6Postgres New Server 9.6.6Issues BIG difference in speed on the following SQL statement from 9.5 to 9.6 ...EXPLAIN ANALYZEselect de_caseid, de_seqno, de_document_num, doc_link.dm_id as dm_id,   doc_link.dm_seq as dm_seq,

Re:

Ok. On Wed, Jun 13, 2018 at 12:00 PM Caglar Aksu wrote: > Dont mail me plase unsubscribe >

[no subject]

Dont mail me plase unsubscribe

Re: First query on each connection is too slow

Hi, On 2018-06-13 10:49:39 -0400, Tom Lane wrote: > Andres Freund writes: > > On 2018-06-13 12:55:27 +0300, Vadim Nevorotin wrote: > >> I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS 2.3 > >> (both from Debian Strecth repos) to store DB for OSM server (but actually > >> it

Re: First query on each connection is too slow

Hi Andres, Please find the below link: Link: http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#shared_buffers Regards, Pavan -- Sent from:

Re: First query on each connection is too slow

Andres Freund writes: > On 2018-06-13 12:55:27 +0300, Vadim Nevorotin wrote: >> I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS 2.3 >> (both from Debian Strecth repos) to store DB for OSM server (but actually >> it doesn't matter). And I've noticed, that on each new

Re: First query on each connection is too slow

Hi, On 2018-06-13 12:55:27 +0300, Vadim Nevorotin wrote: > I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS 2.3 > (both from Debian Strecth repos) to store DB for OSM server (but actually > it doesn't matter). And I've noticed, that on each new connection to DB > first query

Re: First query on each connection is too slow

Hi, On 2018-06-13 15:55:05 +0530, Pavan Teja wrote: > Too much of shared buffers allocation also causes problem. Read the > documentation. That's not in the docs. - Andres

Re: Merging two database dumps

On 06/13/2018 06:21 AM, Alex O'Ree wrote: Desired behavior is to just log the error and continue the import using pgdump based copy commands Each COPY is atomic so if any part of it fails the whole thing fails, so you will not be able to achieve what you want that way. The servers are not

Re: Can you make a simple view non-updatable?

On Fri, Jun 8, 2018 at 8:27 AM, Adrian Klaver wrote: > > Using INSTEAD OF trigger?: > Yep, that's one way.

Re: First query on each connection is too slow

On 06/13/2018 02:55 AM, Vadim Nevorotin wrote: I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS 2.3 (both from Debian Strecth repos) to store DB for OSM server (but actually it doesn't matter). And I've noticed, that on each new connection to DB first query is much slower

Re: Merging two database dumps

Desired behavior is to just log the error and continue the import using pgdump based copy commands The servers are not on the same network. Sneaker net is the only way On Wed, Jun 13, 2018, 7:42 AM Andreas Kretschmer wrote: > > > Am 13.06.2018 um 13:17 schrieb Alex O'Ree: > > I have a

Re: First query on each connection is too slow

Vadim Nevorotin schrieb am 13.06.2018 um 11:55: > I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS > 2.3 (both from Debian Strecth repos) to store DB for OSM server (but > actually it doesn't matter). And I've noticed, that on each new > connection to DB first query is much

Re: [External] Merging two database dumps

Yes Vijay, It might work, but I'm thinking it will be a performance overhead in case of complex data. Regards, Pavan -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: Merging two database dumps

Am 13.06.2018 um 13:17 schrieb Alex O'Ree: I have a situation with multiple postgres servers running all with the same databases and table structure. I need to periodically export the data from each of there then merge them all into a single server. On  occasion, it's feasible for the same

Re: [External] Merging two database dumps

You can try one option, although just a thought in the air  Use postgres FDW ex. https://robots.thoughtbot.com/postgres-foreign-data-wrapper Create foreign tables in the relevant server schema And then union/union all  or your custom constraint on the destination table where you dump the

Re: Merging two database dumps

Hi Alex, For storing duplicate rows, dropping primary and unique indexes is the only way. One alternative is create a column with timestamp which updates on every insert/update so that timestamp will be primary. Hope it helps. Regards, Pavan On Wed, Jun 13, 2018, 4:47 PM Alex O'Ree wrote: > I

Merging two database dumps

I have a situation with multiple postgres servers running all with the same databases and table structure. I need to periodically export the data from each of there then merge them all into a single server. On occasion, it's feasible for the same record (primary key) to be stored in two or more

Re: First query on each connection is too slow

ср, 13 июн. 2018 г. в 13:25, Pavan Teja : > Too much of shared buffers allocation also causes problem. Read the > documentation. > As I noticed ahead - it's not a shared buffers or memory problem, because it appears only on first query in each new connection, even if all data already loaded to

Re: First query on each connection is too slow

On 13.06.2018 12:55, Vadim Nevorotin wrote: Nested Loop Left Join  (cost=0.82..26.41 rows=1 width=4) *(actual time=49.290..49.297 rows=1 loops=1)*    Join Filter: ((s_2.connamespace = n.oid) AND (s_2.conrelid = c.oid) AND (a.attnum = ANY (s_2.conkey)))    Buffers: shared hit=18 ... * Planning

Re: First query on each connection is too slow

Hi Vadim, Too much of shared buffers allocation also causes problem. Read the documentation. Regards, Pavan On Wed, Jun 13, 2018, 3:23 PM Vadim Nevorotin wrote: > I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS 2.3 > (both from Debian Strecth repos) to store DB for OSM

First query on each connection is too slow

I have a very strange problem. I'm using PostgreSQL 9.6 with PostGIS 2.3 (both from Debian Strecth repos) to store DB for OSM server (but actually it doesn't matter). And I've noticed, that on each new connection to DB first query is much slower (10x) than all others. E.g.: $ psql test_gis psql

Re: Slow planning time for simple query

On 09.06.2018 22:49, Tom Lane wrote: Maksim Milyutin writes: On hot standby I faced with the similar problem. ... is planned 4.940 ms on master and *254.741* ms on standby. (I wonder though why, if you executed the same query on the master, its setting of the index-entry-is-dead bits didn't