Re: [GENERAL] How to assign default values to psql variables?

Hi 2017-02-22 6:00 GMT+01:00 Torsten Förtsch : > Hi, > > I have a psql script relying on variables passed in on the command line > with -v. > > Is there any way to assign a default value in case the -v option is > forgotten? > > Here is an example with pgtap: > > select

[GENERAL] How to assign default values to psql variables?

Hi, I have a psql script relying on variables passed in on the command line with -v. Is there any way to assign a default value in case the -v option is forgotten? Here is an example with pgtap: select diag('should be printed only in verbose mode') where :testverbose; This can be called with

Re: [GENERAL] bloat indexes - opinion

On 02/21/2017 03:41 PM, Patrick B wrote: > 2017-02-22 11:11 GMT+13:00 Patrick B >: > > 2017-02-22 10:59 GMT+13:00 Adrian Klaver >: > > On 02/21/2017 01:44

Re: [GENERAL] NOTIFY command impact

> On Feb 21, 2017, at 6:03 PM, Tom Lane wrote: > > Rob Brucks writes: >> If a notify is sent and then PG is immediately shut down, wouldn't PG want >> to save that message for processing after startup? >> Or is the message just discarded? > >

Re: [GENERAL] bloat indexes - opinion

2017-02-22 11:11 GMT+13:00 Patrick B : > 2017-02-22 10:59 GMT+13:00 Adrian Klaver : > >> On 02/21/2017 01:44 PM, Patrick B wrote: >> > Hi guys, >> > >> > I've got a lot of bloat indexes on my 4TB database. >> > >> > Let's take this example: >>

Re: R: R: [GENERAL] Slow queries on very big (and partitioned) table

Stephen Frost writes: > * Job (j...@colliniconsulting.it) wrote: >> We thought to implement one partition for day. >> Do you think it should be fine? > Really depends on what you're doing. If you're running very short > queries that pull out just a record or a few records,

Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 3:47 PM, Rob Brucks wrote: > If a notify is sent and then PG is immediately shut down, wouldn't PG want > to save that message for processing after startup? > > > > Or is the message just discarded? > > > ​Adrian gave details but trying to

Re: [GENERAL] NOTIFY command impact

Rob Brucks writes: > If a notify is sent and then PG is immediately shut down, wouldn't PG want to > save that message for processing after startup? > Or is the message just discarded? NOTIFY data is not saved across a shutdown or crash. (The reason it goes into the

Re: [GENERAL] NOTIFY command impact

On 02/21/2017 02:43 PM, Rob Brucks wrote: That's a great thought! Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing: one 8k file named "". If that's truly where notifications are stored, then it looks like I should be

Re: [GENERAL] NOTIFY command impact

If a notify is sent and then PG is immediately shut down, wouldn't PG want to save that message for processing after startup? Or is the message just discarded? --Rob From: "David G. Johnston" Date: Tuesday, February 21, 2017 at 4:45 PM To: Rob Brucks

Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 3:43 PM, Rob Brucks wrote: > But I wonder if that file is only used to store notify commands during > shutdown/startup? > ​ huh?​ David J.

Re: [GENERAL] NOTIFY command impact

That's a great thought! Comparing between systems (one where I don't issue "notify" and one where I do every minute) yields the same thing: one 8k file named "". If that's truly where notifications are stored, then it looks like I should be good, at least for storage. But I wonder if

Re: [GENERAL] NOTIFY command impact

On 02/21/2017 02:19 PM, Rob Brucks wrote: I did find a post a while back saying they were discarded, but I wanted to double-check. I performed some tests to see if listens worked AFTER the notify was issued, they were not. This leads me to believe that the messages are discarded when a

Re: R: R: [GENERAL] Slow queries on very big (and partitioned) table

Greetings, * Job (j...@colliniconsulting.it) wrote: > >If this is really what you're mostly doing, having constraint exclusion and > >an index on 'profile' would probably be enough, if you insist on continuing > >to have the table partitioned by day (which I continue to argue is a bad > >idea-

Re: [GENERAL] NOTIFY command impact

I did find a post a while back saying they were discarded, but I wanted to double-check. I performed some tests to see if listens worked AFTER the notify was issued, they were not. This leads me to believe that the messages are discarded when a listen does not yet exist. --Rob From: "David

Re: [GENERAL] NOTIFY command impact

In my case the monitoring user will be connecting, issuing the notify, then immediately disconnecting. And we don't have any systems using listen/notify. So I'm hoping there won't be a problem. That's why I'm asking ☺ --Rob On 2/21/17, 3:17 PM, "Adrian Klaver"

Re: [GENERAL] bloat indexes - opinion

2017-02-22 10:59 GMT+13:00 Adrian Klaver : > On 02/21/2017 01:44 PM, Patrick B wrote: > > Hi guys, > > > > I've got a lot of bloat indexes on my 4TB database. > > > > Let's take this example: > > > > Table: seg > > Index: ix_filter_by_tree > > Times_used:

Re: [GENERAL] bloat indexes - opinion

On 02/21/2017 01:44 PM, Patrick B wrote: > Hi guys, > > I've got a lot of bloat indexes on my 4TB database. > > Let's take this example: > > Table: seg > Index: ix_filter_by_tree > Times_used: 1018082183 > Table_size: 18 GB -- wrong. The table is mostly on pg_toast table. >

Re: R: [GENERAL] Slow queries on very big (and partitioned) table

> We thought to implement one partition for day. That would be 365 partitions in a year. In our experience INSERTS suffers the most in a partitioned table because triggers are the only way to route the row to the proper child (inherited) table. Question: How is your insert pattern? Do you

R: R: [GENERAL] Slow queries on very big (and partitioned) table

Hi Stephen, Thank you for your excellent opinion! >If this is really what you're mostly doing, having constraint exclusion and an >index on 'profile' would probably be enough, if you insist on continuing to >have the table partitioned by day (which I continue to argue is a bad idea- >based on

R: [GENERAL] Search on very big (partitioned) table

Hi Sushant, i think the problem is that we miss check constraints. We will implement asap For the moment thank you to everybody for the excellent support! /F

[GENERAL] bloat indexes - opinion

Hi guys, I've got a lot of bloat indexes on my 4TB database. Let's take this example: Table: seg Index: ix_filter_by_tree Times_used: 1018082183 Table_size: 18 GB -- wrong. The table is mostly on pg_toast table. Its real size is 2TB Index_size: 17 GB Num_writes 16245023 Index definition: CREATE

Re: [GENERAL] NOTIFY command impact

On Tue, Feb 21, 2017 at 2:17 PM, Adrian Klaver wrote: > On 02/21/2017 01:07 PM, Rob Brucks wrote: > >> >> Do you see any long-term problems with constantly issuing "NOTIFY" >> commands every 30 seconds without an associated "LISTEN" command? >> > > Depending on how

Re: [GENERAL] Wired-Protocol Specification?

On 2/21/2017 1:12 PM, Ozz Nixon wrote: I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk to pgsql, the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wired to both).

Re: [GENERAL] Wired-Protocol Specification?

Ozz Nixon writes: > I am working on a project which needs me to implement the direct > “wired-protocol” to talk to pgsql. One side, I talk to pgsql, the other side, > I need to make a listener which accepts the protocol from pgsql’s and mysql’s > ODBC (talking wired to

Re: [GENERAL] NOTIFY command impact

On 02/21/2017 01:07 PM, Rob Brucks wrote: Hi All, I just wanted to check with you guys to make sure that constantly issuing "NOTIFY" commands without corresponding "LISTEN" commands will not cause any harm or excessive work for the PostgreSQL cluster. Nothing that would put my cluster at

[GENERAL] Wired-Protocol Specification?

I am working on a project which needs me to implement the direct “wired-protocol” to talk to pgsql. One side, I talk to pgsql, the other side, I need to make a listener which accepts the protocol from pgsql’s and mysql’s ODBC (talking wired to both). [sorry if Wired is not the correct term,

[GENERAL] NOTIFY command impact

Hi All, I just wanted to check with you guys to make sure that constantly issuing "NOTIFY" commands without corresponding "LISTEN" commands will not cause any harm or excessive work for the PostgreSQL cluster. Nothing that would put my cluster at risk. The reason I ask is because I was trying

Re: [GENERAL] postgresql how to duplicate rows in result.

Merlin Moncure writes: > On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston > wrote: >> If it wasn't lateral the reference to number in "generate_series(1, number)" >> would fail. > huh -- I didn't know that! Testing it out, all JOIN types imply

Re: [GENERAL] Bad planning data resulting in OOM killing of postgres

On Thu, Feb 16, 2017 at 3:27 PM, David Hinkle wrote: > I guess this doesn't work, latest test run crashed. It still uses the > bad plan for the hostid column even after n_distinct is updated. > > cipafilter=# select attname, n_distinct from pg_stats where tablename >

Re: [GENERAL] postgresql how to duplicate rows in result.

On Thu, Feb 16, 2017 at 10:27 AM, David G. Johnston wrote: > If it wasn't lateral the reference to number in "generate_series(1, number)" > would fail. huh -- I didn't know that! Testing it out, all JOIN types imply LATERAL if the function call is tlist SRF style

[GENERAL] save PGresult to file.

Hi, I'm trying to write a simple С program which sends a query to postgres and saves answer to a binary file. Next, I want to read the content from the binary file into an empty PGresult struct. And I've faced with two problems, I don't know how to calculate size of returned PGresult struct and

Re: [GENERAL] Feature request - psql --quote-variable

Hi 2017-02-21 15:19 GMT+01:00 Caleb Cushing : > recently while exploring this problem http://stackoverflow.com/q/ > 40945277/206466. I decided to go with the docker container approach of a > shell script. > > I realized that postgres' variables aren't quoted either,

[GENERAL] Feature request - psql --quote-variable

recently while exploring this problem http://stackoverflow.com/q/40945277/206466. I decided to go with the docker container approach of a shell script. I realized that postgres' variables aren't quoted either, which results in me quoting them with bash, to help avoid accidents, and even then I'm

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 08:06 AM, Thomas Güttler wrote: Am 21.02.2017 um 15:27 schrieb William Ivanski: You can try OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB has a Convert feature, where you can set a data transfer, even if the target table exists. I am unsure if omnidb is the right tool

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 07:53 AM, Thomas Güttler wrote: Am 21.02.2017 um 15:12 schrieb Adrian Klaver: On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. You actually talking about moving from ~100 databases to the central database,

Re: [GENERAL] Move rows from one database to other

On Tue, Feb 21, 2017 at 11:10 AM, Thomas Güttler < guettl...@thomas-guettler.de> wrote: > >> Depending on how much data you want to move, and if the tables have the >> same structure, you might also want to consider >> using >> pg_dump -a >> >> OR >> multiple instances of >> >> on satellite >>

Re: [GENERAL] Move rows from one database to other

Depending on how much data you want to move, and if the tables have the same structure, you might also want to consider using pg_dump -a OR multiple instances of on satellite COPY { table_name [ ( column_name [, ...] ) ] | ( query ) } TO { 'filename' | PROGRAM 'command' | STDOUT } [ [

Re: [GENERAL] Move rows from one database to other

Am 21.02.2017 um 15:27 schrieb William Ivanski: You can try OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB has a Convert feature, where you can set a data transfer, even if the target table exists. I am unsure if omnidb is the right tool here. I don't need a GUI. The movement of

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. Both run PostgreSQL. How to solve this with PostgreSQL? Should have added earlier. This is a specific case of the more general case of ETL(Extract/Transform/Load). There are

Re: [GENERAL] Move rows from one database to other

Am 21.02.2017 um 15:12 schrieb Adrian Klaver: On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. You actually talking about moving from ~100 databases to the central database, correct? Both run PostgreSQL. Are all the

Re: [GENERAL] Move rows from one database to other

Il 21/02/2017 15:38, Melvin Davidson ha scritto: Depending on how much data you want to move, and if the tables

Re: [GENERAL] Different LEFT JOIN results with and without USING

Alexander Farber writes: > why do these queries please return different results? > FROMwords_scores s > LEFT JOIN words_games g ON s.gid = g.gid > AND s.uid = 1 In this one, "s.uid = 1" is part of the LEFT JOIN condition, so it never

Re: [GENERAL] Move rows from one database to other

On Tue, Feb 21, 2017 at 9:27 AM, William Ivanski wrote: > You can try OmniDB: http://www.omnidb.com.br/en_index.aspx > > OmniDB has a Convert feature, where you can set a data transfer, even if > the target table exists. > > Em ter, 21 de fev de 2017 às 11:18, Adrian

[GENERAL] Different LEFT JOIN results with and without USING

Good afternoon, why do these queries please return different results? SELECT s.gid, TO_CHAR(g.created, 'DD.MM.'), TO_CHAR(g.finished, 'DD.MM.'), LENGTH(s.word), s.score FROMwords_scores s LEFT JOIN words_games g ON s.gid = g.gid

Re: [GENERAL] Move rows from one database to other

You can try OmniDB: http://www.omnidb.com.br/en_index.aspx OmniDB has a Convert feature, where you can set a data transfer, even if the target table exists. Em ter, 21 de fev de 2017 às 11:18, Adrian Klaver escreveu: > On 02/21/2017 12:53 AM, Thomas Güttler wrote: >

Re: [GENERAL] Move rows from one database to other

On 02/21/2017 12:53 AM, Thomas Güttler wrote: I want to move table rows from one database to an central database. You actually talking about moving from ~100 databases to the central database, correct? Both run PostgreSQL. Are all the Postgres instances the same version and what is the

Re: [GENERAL] Autovacuum stuck for hours, blocking queries

-Original Message- From: Tom Lane [mailto:t...@sss.pgh.pa.us] Sent: 16 February 2017 22:40 To: Tim Bellis Cc: Adrian Klaver ; pgsql-general@postgresql.org; Alvaro Herrera ; Scott Marlowe

[GENERAL] Move rows from one database to other

I want to move table rows from one database to an central database. Both run PostgreSQL. My use case looks like this: There are N satellite databases in different data centers. N is about 100 at the moment. There is one central database. I need a way to reliably move rows from the satellite