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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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, >

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

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

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

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

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