*think* that the solution is to BEGIN the transaction, then perform SELECT
... WITH UPDATE, then proceed with INSERT and COMMIT if ok, else ROLLBACK.
Is this the correct approach, or am I missing something?
Thanks
Frank Millman
From: hubert depesz lubaczewski
Sent: Wednesday, October 26, 2016 10:46 AM
To: Frank Millman
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Locking question
On Wed, Oct 26, 2016 at 10:42:29AM +0200, Frank Millman wrote:
> Hi all
> >
> > I am designing an inventory app
From: Frank Millman
Sent: Wednesday, October 26, 2016 10:42 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Locking question
> I am designing an inventory application, and I want to ensure that the stock
> level of any item cannot go negative.
Thanks to all for some really
better ideas?
Thanks
Frank Millman
From: amul sul
Sent: Monday, January 02, 2017 12:42 PM
To: Frank Millman
Cc: pgsql-general
Subject: Re: [GENERAL] Difficulty modelling sales taxes
> On Mon, Jan 2, 2017 at 4:03 PM, Frank Millman wrote:
> > Hi all
> >
> > It is a bit ugly, because I have to use the ‘NVARC
Hi all
I am running PostgreSQL 9.4.4 on Fedora 22.
SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
Please can someone explain the anomaly.
Thanks
Frank Millman
> I am running PostgreSQL 9.4.4 on Fedora 22.
>
> SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
>
> SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
>
> Please can someone explain the anomaly.
Thanks for all the responses.
Plenty of workaro
> I am running PostgreSQL 9.4.4 on Fedora 22.
>
> SELECT floor(4.725 * 100 + 0.5) returns 473, which is what I expected.
>
> SELECT floor(4.725 * power(10, 2) + 0.5) returns 472, which I find surprising.
>
> Please can someone explain the anomaly.
I think I have a solution to my problem, but I
On Tue, Mar 15, 2016 at 12:02 PM, Francisco Olarte wrote:
> Hi Frank:
>
> This may byte you any day, so I wuld recommend doing
>
> s=> select v, pg_typeof(v) from (select floor(4.725 * power(cast(10 as
> numeric), 2) + 0.5)) as aux(v);
> v | pg_typeof
> -+---
> 473 | numeric
>
nate order, I can get duplicate level numbers while the command is
being executed.
My workaround at present is the following -
UPDATE treelevels SET levelno = (levelno+10001) WHERE levelno >= 1;
UPDATE treelevels SET levelno = (levelno-1) WHERE levelno >= 1;
It works, but it feels very
Michael Glaesemann wrote:
>
> On Aug 22, 2007, at 1:02 , Frank Millman wrote:
>
> > I want to store data in a 'tree' form, with a fixed number
> of levels,
> > so that each level has a defined role.
>
Thanks very much for the in-depth response, Michael
bie around here, but this is one that I can answer :-)
update table set boolean_column = not boolean_column where fk = some_value
HTH
Frank Millman
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
.
Exactly the same exercise on Sql Server results in 0.06 seconds for both
versions.
I realise that, if I was selecting a large number of rows, 0.23 seconds is
trivial and the overall result could be different. But still, it seems odd.
Is this normal, or should I investigate further?
Frank Millman
Pavel Stehule wrote:
>
> 2017-09-12 8:45 GMT+02:00 Frank Millman :
I am using 9.4.4 on Fedora 22.
I am experimenting with optimising a SQL statement. One version uses 4 LEFT
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering
into the JOIN section, and I
Ron Johnson wrote:
>
On 09/12/2017 01:45 AM, Frank Millman wrote:
Hi all
I am using 9.4.4 on Fedora 22.
I am experimenting with optimising a SQL statement. One version uses 4 LEFT
JOIN’s and a 5-way CASE statement in the body. The second moves the filtering
into the JOIN section, and I
Pavel Stehule wrote:
2017-09-12 9:36 GMT+02:00 Frank Millman :
Pavel Stehule wrote:
>
> 2017-09-12 8:45 GMT+02:00 Frank Millman :
I am using 9.4.4 on Fedora 22.
I am experimenting with optimising a SQL statement. One version uses 4 LEFT
JOIN’s and a 5-way CASE statement
2017-09-12 12:39 GMT+02:00 Pavel Stehule :
2017-09-12 12:25 GMT+02:00 Frank Millman :
Pavel Stehule wrote:
2017-09-12 9:36 GMT+02:00 Frank Millman :
Pavel Stehule wrote:
>
> 2017-09-12 8:45 GMT+02:00 Frank Millman :
I am using 9.4.4 on Fed
From: Chris Travers
Sent: Tuesday, September 12, 2017 3:36 PM
To: Frank Millman
Cc: Postgres General
Subject: Re: [GENERAL] Joining 16 tables seems slow
Chris Travers wrote:
On Tue, Sep 12, 2017 at 3:15 PM, Frank Millman wrote:
2017-09-12 12:39 GMT+02:00 Pavel Stehule :
> ple
analysis correct?
If so, is there any way to force it to use an indexed read?
Thanks for any pointers.
Frank Millman
Pavel Stehule wrote:
2017-09-14 10:14 GMT+02:00 Frank Millman :
Hi all
This is a follow-up to a recent question I posted regarding a slow query. I
thought that the slowness was caused by the number of JOINs in the query, but
with your assistance I have found the true reason. I said in the
2017-09-14 15:09 GMT+02:00 Pavel Stehule :
2017-09-14 14:59 GMT+02:00 Frank Millman :
Pavel Stehule wrote:
2017-09-14 10:14 GMT+02:00 Frank Millman :
Hi all
This is a follow-up to a recent question I posted regarding a slow query.
I thought that the slowness was
too effective - maybe some composite or partial
> > index helps.
>
> In my testing JOINS can push through UNION ALL. Why do we need to
> materialize union first? What version is this?
>
I am using version 9.4.4 on Fedora 22.
Frank Millman
On 2017-09-18 Frank Millman wrote:
>
> Here are the timings for running the query on identical data sets using
> Postgresql, Sql Server, and Sqlite3 -
>
> PostgreSQL -
> Method 1 - 0.28 sec
> Method 2 – 1607 sec, or 26 minutes
>
> Sql Server -
> Meth
On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
>
> > On Thu, Sep 21, 2017 at 1:51 AM, Frank Millman wrote:
> >
> > I did not get any response to this, but I am still persevering, and feel
> > that I am getting closer. Instead of waiting 26 minutes for a resul
On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
> Something is not adding up here. Can you EXPLAIN ANALYZE the 26 minute
> query?
Here it is -
https://explain.depesz.com/s/cwm
Frank
Merlin Moncure wrote:
On Thu, Sep 21, 2017 at 12:02 PM, Frank Millman wrote:
>
> SELECT q.cust_row_id,
> SUM(CASE WHEN q.tran_date > '2015-08-31' THEN q.balance ELSE 0 END
> ) AS "balance_curr AS [DECTEXT]",
> SUM(CASE WHEN q.tran_date <= '201
Frank Millman wrote:
>
> On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
> > Something is not adding up here. Can you EXPLAIN ANALYZE the 26 minute
> > query?
> Here it is -
>
> https://explain.depesz.com/s/cwm
>
There is one thing I have not
From: Frank Millman
Sent: Friday, September 22, 2017 7:34 AM
To: pgsql-general@postgresql.org
Subject: Re: a JOIN to a VIEW seems slow
On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote:
>
> On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
> > Something is not ad
On Wednesday, October 4, 2017 06:07 PM Jan de Visser wrote:
> On Monday, October 2, 2017 2:32:34 AM EDT Frank Millman wrote:
> >
> > Just checking – is this under investigation, or is this thread considered
> > closed?
>
> That's not how it works. This is a c
On 4 Oct 2017, at 9:19 PM, Alban Hertroys wrote:
> On 2 Oct 2017, at 8:32, Frank Millman wrote:
>
>
> On Fri, Sep 22, 2017 at 7:34 AM, Frank Millman wrote:
> >
> > On Thu, Sep 21, 2017 at 3:54 PM, Merlin Moncure wrote:
> > > Something is not adding up h
On 5 Oct 2017, at 8:20 AM, Frank Millman wrote:
> If anyone wants to take this further, maybe this is a good place to start.
I should have re-stated the reason for my original post.
Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql
Server, 1.0 seconds on SQLite3,
On 5 Oct 2017, at 9:51 AM, Frank Millman wrote:
>
> I should have re-stated the reason for my original post.
>
> Exactly the same query, on exactly the same data, takes 1.8 seconds on Sql
> Server, 1.0 seconds on SQLite3, and 1607 seconds, or 26 minutes, on
> PostgreSQL
On 7 October 2017 at 2:49 PM, David Rowley wrote:
>
> Yeah, PostgreSQL does not make any effort to convert subqueries in the
> target list into joins. SQL server does.
[...]
> You'll probably find it'll run faster if you convert the subquery in
> the target list into a join with a GROUP BY, like
t also gave an error - "Types
don't match between the anchor and the recursive parts in column 'seq' ...".
After adding the CAST's, it ran correctly.
Any assistance will be appreciated.
Frank Millman
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
> -Original Message-
> From: pgsql-general-ow...@postgresql.org
> [mailto:pgsql-general-ow...@postgresql.org] On Behalf Of Frank Millman
> Sent: 22 May 2011 11:22
> To: pgsql-general@postgresql.org
> Subject: [GENERAL] Problem with WITH RECURSIVE
>
>
a value of 'a' to be rejected if there
is already a value of 'A'. Workaround - create a unique index on
LOWER(col_name).
2. I would like WHERE col_name = 'x' and WHERE col_name LIKE 'x%' to find
'X' and 'X1'. Workaround - WHERE LOWE
Frank Millman wrote:
> Hi all
>
> Is there an LC_COLLATE setting, or any other method, which allows all
> data in a database to be treated in a case-insensitive manner?
I was hoping to stimulate some discussion on this topic, but it seems I will
have to kick-start it myself and s
Frank Millman wrote:
> Hi all
>
> Is there an LC_COLLATE setting, or any other method, which
allows all
> data in a database to be treated in a case-insensitive manner?
>
Thanks for all the replies, guys, I really appreciate it.
Here is what I have decided to do. If anyone se
Frank Millman wrote:
> 1. Will SELECT WHERE LOWER(colname) = 'a001' use the index, or must I
create a separate index on LOWER(colname)?
Sorry. RTFM. The docs clearly state that this is necessary.
Frank
---(end of broadcast)---
T
27;ints[1] = '', _ints[1];
I then get 'syntax error at or near
"["'.
Platform is 7.4.1 on Redhat 9.
TIA for any advice.
Frank Millman
xmin
for this purpose, or is there another way of achieving OCC?
TIA
Frank Millman
ults are the same.
Any advice will be much appreciated.
Frank Millman
CREATE TABLE SysUsers (
UserId varchar (999) not null primary key,
Password varchar (999),
Surname varchar (999) not null,
FirstName varchar (999),
Administrator char default ' ' check (Administrator
re complex _expression_, and returns
either 'Y' or 'N'
In 7.3.2, the first select behaves the same, but the second one returns
NULL.
Any advice will be appreciated.
Frank Millman
43 matches
Mail list logo
