Re: A 3 table join question

On Fri, Aug 16, 2019 at 5:54 PM stan wrote: > > On Fri, Aug 16, 2019 at 12:30:33PM -0700, Ken Tanzer wrote: > > On Fri, Aug 16, 2019 at 7:24 AM rob stone wrote: > > > > > Hello, > > > > > > On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > > > > What am I doing wrong here? > > > > > > > > > > > >

Re: A 3 table join question

On Fri, Aug 16, 2019 at 12:30:33PM -0700, Ken Tanzer wrote: > On Fri, Aug 16, 2019 at 7:24 AM rob stone wrote: > > > Hello, > > > > On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > > > What am I doing wrong here? > > > > > > > > > Your view assumes that all three "streams" contain all the proj_

Re: Missing Trigger after pgdump install

On 8/16/19 3:45 PM, Susan Hurst wrote: We're using the 9.5.14 in the sandbox to extract data and objects from the pgdump that was created in the 9.5.0 version.  Hope I answered your question correctly.  If not, let me know and I'll try again. As Rob pointed out I was wanting to know what pg_bi

Re: Missing Trigger after pgdump install

On 8/16/19 4:45 PM, Susan Hurst wrote: We're using the 9.5.14 in the sandbox to extract data and objects from the pgdump that was created in the 9.5.0 version.  Hope I answered your question correctly.  If not, let me know and I'll try again. Our biggest concern is that there may be other si

Re: Missing Trigger after pgdump install

We're using the 9.5.14 in the sandbox to extract data and objects from the pgdump that was created in the 9.5.0 version. Hope I answered your question correctly. If not, let me know and I'll try again. Our biggest concern is that there may be other silent issues that we have not yet discover

Re: Missing Trigger after pgdump install

On 8/16/19 3:18 PM, Susan Hurst wrote: Production version: PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit Sandbox version: "PostgreSQL 9.5.14, compiled by Visual C++ build 1800, 64-bit" I going to say it has something to do with this

Re: Missing Trigger after pgdump install

Production version: PostgreSQL 9.5.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.4.7 20120313 (Red Hat 4.4.7-16), 64-bit Sandbox version: "PostgreSQL 9.5.14, compiled by Visual C++ build 1800, 64-bit" --- Susan E Hurst Principal Consultant Brook

RE: Question on pgwatch

Hi Luca, When I go to the URL link for pgwatch that you have given I see the following : How do I get to pgwatch docker download and install on my linux server? [cid:image001.png@01D55453.A5A43AC0] Bikram Majumdar Sr Software Developer/DBA, Aqsacom Inc. c. 1.972.365.3737 Disclai

RE: Question on pgwatch

Hi Luca, I have installed docker (yum install ) on my linux server. Does it already come with the pgwatch public image? How do I download and install pgwatch docker image from github -- Thanks and regards Bikram Majumdar Sr Software Developer

Re: Missing Trigger after pgdump install

On 8/16/19 1:00 PM, Susan Hurst wrote: The dump command used by the DBA to create the pgdump file is: pg_dump --clean --if-exists --create --format=plain --no-owner --no-tablespaces \     --file=${BKUPDIR}/${TS}.${USER}.pg 2>&1    \    | tee -a  ${LOGDIR}/${TS}_biar_dump.log No

Unexpected "canceling statement due to user request" error

Hi! I have a query that fails due to this error and I'm trying to understand why. My understanding is I should only see this error if I cancel a query manually, such as with kill -INT or with pg_cancel_backend(). However I can't find anything doing that. The query looks like this: SELECT *

Re: Missing Trigger after pgdump install

The dump command used by the DBA to create the pgdump file is: pg_dump --clean --if-exists --create --format=plain --no-owner --no-tablespaces \ --file=${BKUPDIR}/${TS}.${USER}.pg 2>&1 \ | tee -a ${LOGDIR}/${TS}_biar_dump.log No noticeable diffe

Transaction state on connection Idle/Open/Failed

In JDBC (latest version), I'm using the org.postgresql.jdbc.PgConnection.getTransactionState() that returns an enumeration of IDLE, OPEN or FAILED. I am familiar with IDLE, meaning the connection has a new transaction started, but isn't doing anything.  We think of this as the "normal" state

Re: A 3 table join question

On Fri, Aug 16, 2019 at 7:24 AM rob stone wrote: > Hello, > > On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > > What am I doing wrong here? > > > > > Your view assumes that all three "streams" contain all the proj_no's > whereas your test data for expense_report_cost_sum_view has no proj_no > =

Re: A 3 table join question

On Sat, Aug 17, 2019 at 12:24:31AM +1000, rob stone wrote: > Hello, > > On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > > First let me say a huge THANK YOU to all the helpful people that > > hanging out > > on this. > > > > I am changing from one type of work, going back to some database work >

Re: Missing Trigger after pgdump install

On 8/16/19 11:27 AM, Susan Hurst wrote: What scenarios can cause a single trigger to be omitted when populating an empty database from a pgdump file? We have nightly backups of our production database that we load into a fresh, empty database in our sandbox using the pgdump file. psql.exe -h

Missing Trigger after pgdump install

What scenarios can cause a single trigger to be omitted when populating an empty database from a pgdump file? We have nightly backups of our production database that we load into a fresh, empty database in our sandbox using the pgdump file. psql.exe -h localhost -U mi601db -p 5432 -o C:db_cr

Re: Variable constants ?

On 16/08/2019 09:27, Rich Shepard wrote: On Thu, 15 Aug 2019, stan wrote: I need to put a few bossiness constants, such as a labor rate multiplier in an application. I am adverse to hard coding these things. The best plan i have come up with so far is to store them in a table, which would hav

Re: slow queries on system tables

As I told you before, these queries cannot be changed because the driver creates them. As I cannot change them how can I help Postgres to run it faster, just that. My log_min_duration_statement = 500ms, so I find SQL some dozens of this sql inside it daily, but I´m sure this sql is used thousands

Re: slow queries on system tables

PegoraroF10 writes: > Values for autovacuum and autoanalyse are Null because I did not configured > them for system tables yet, but I´m doing vacuum manually once a week. My > question now is why those selects varies that way. Well, one point is that the execution time would probably vary hugely

Re: Bad Estimate for complex query with JOINS on subselects and OR in where conditions

Hello Tom, yes, I think this query is right below the geqo_threshold. But as I said, when I change only the WHERE condition to use AND instead of OR it's resulting in a really fast and efficient query (same planning time, but ~1/500th-1/1000th execution time). So there should be something differen

Re: Variable constants ?

On Fri, 16 Aug 2019, Charles Clavadetscher wrote: That would be a range with an empty upper bound. Let's say that the rate is valid since 2019-08-14 then the range would look like [2019-08-14,) A query to find the current rate would look like: SELECT rate FROM labor_rate_mult WHERE validity @>

Re: Bad Estimate for complex query with JOINS on subselects and OR in where conditions

Peter Grman writes: > yes, I think this query is right below the geqo_threshold. But as I said, > when I change only the WHERE condition to use AND instead of OR it's > resulting in a really fast and efficient query (same planning time, but > ~1/500th-1/1000th execution time). So there should be s

Re: A 3 table join question

Hello, On Fri, 2019-08-16 at 07:39 -0400, stan wrote: > First let me say a huge THANK YOU to all the helpful people that > hanging out > on this. > > I am changing from one type of work, going back to some database work > for a > project, as my old job was eliminated. I have made great progress o

Re: A 3 table join question

On Fri, Aug 16, 2019 at 4:39 AM stan wrote: > First let me say a huge THANK YOU to all the helpful people that hanging > out > on this. > > I am changing from one type of work, going back to some database work for a > project, as my old job was eliminated. I have made great progress on this, > th

Re: Variable constants ?

On Thu, Aug 15, 2019 at 11:27 PM Rich Shepard wrote: > create table labor_rate_mult ( >rate real primary_key, >start_date date not null, >end_date date > ) I think the rate should not be the primary key, since that would prevent keeping the whole history when the

Re: slow queries on system tables

Well, not exactly. Values for autovacuum and autoanalyse are Null because I did not configured them for system tables yet, but I´m doing vacuum manually once a week. My question now is why those selects varies that way. Almost all times it spend 20ms but 2 o 3% of the times it spend 500ms, why ?

Re: Bad Estimate for complex query with JOINS on subselects and OR in where conditions

Peter Grman writes: > our ORM with tenant separation enabled is creating the following query: Ugh. By my count there are nine joined tables in that query, which means you're hitting the default join_collapse_limit. Increasing that setting might improve matters somewhat, though it won't fix the

Re: Variable constants ?

On 2019-08-16 14:50, Rich Shepard wrote: On Fri, 16 Aug 2019, Charles Clavadetscher wrote: Another way to keep a history is using a daterange instead of two columns for start and end date. Something like create table labor_rate_mult ( rate real primary_key, validity daterang

Re: Variable constants ?

On Fri, 16 Aug 2019, Charles Clavadetscher wrote: Another way to keep a history is using a daterange instead of two columns for start and end date. Something like create table labor_rate_mult ( rate real primary_key, validity daterange not null ) Charles, Just out of curio

ODBC Driver Version for PostgreSQL 11.3

Hello, I see that the current version of the ODBC Driver for PostgreSQL is psqlodbc_11_01_. I can't find a Compatibility Matrix. Does this version of the ODBC Driver support PostgreSQL 11.3? Let me know. Thanks. Efrem Litwin Technical Director Information Builders, Inc.

Re: Question on pgwatch

On Wed, Aug 14, 2019 at 5:10 PM Bikram MAJUMDAR wrote: > From where did you download docker for linux, and , the docker with the > pgwatch container? > And, any installation/configuration tips for pgwatch running on linux? I'm not sure what you are effectively askin to me, however the starting

Re: Error XX000 After pg11 upgrade

Hi Thanks for all the help, and a couple of offlist suggestions. We have fixed the problem by copying all of the data (160GB) to a partitioned table, replacing the trigger with table column defaults for timestamp and sequence values. As a result, all is working ok. Thank you, once again Simon

A 3 table join question

First let me say a huge THANK YOU to all the helpful people that hanging out on this. I am changing from one type of work, going back to some database work for a project, as my old job was eliminated. I have made great progress on this, thanks to the time and effort of lots of folks from this list