> On 13 Apr 2016, at 16:48, David G. Johnston
> wrote:
>
> On Wed, Apr 13, 2016 at 8:38 AM, Oliver Kohll <mailto:oli...@agilechilli.com>> wrote:
> Hello,
>
> We currently use prepared statements for most of the work an app does, as an
> SQL injection
Hello,
We currently use prepared statements for most of the work an app does, as an
SQL injection protection and for other reasons.
There's one statement which can't be prepared:
SET LOCAL myprefix.mysetting = 'my setting value';
Ideally, I'd like to be able to do
PREPARE test(text) as SET LO
>>
>>> select * from dblink(‘dbname=database2 username=db_link
>>> password=mypassword','select username, email from appuser') as t1(username
>>> text, email text);:
>
> I think the problem is the above- ^^^
>
> username=db_link should be user=db_link
>
> The accepted key
nk so there’s probably something simple I’m
missing, but I thought I had provided a password. Any ideas?
Regards
Oliver Kohll
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
taking a few seconds to run even though there are only a
thousand rows in the table. I finally found that running CLUSTER on the table
sorted that out, even though we're on an SSD so I would have thought seeking
all over the place for a seq. scan wouldn't have made that much difference.
On 9 Sep 2013, at 21:03, Alvaro Herrera wrote:
> select string_agg(case when words like '*%*' then upper(btrim(words, '*'))
> else words end, ' ')
> from regexp_split_to_table('The *quick* *brown* fox jumped over the *lazy*
> dog', ' ') as words;
>
> string_agg
On 9 Sep 2013, at 14:41, David Johnston wrote:
> Oliver Kohll - Mailing Lists wrote
>> select regexp_replace(sentence,'\*(.*?)\*','' || upper('\1'),'g') from
>> sentences;
>
> Yeah, you cannot embed a function-call result in the &qu
Hello,
Given a string with certain words surrounded by stars, e.g.
The *quick* *brown* fox jumped over the *lazy* dog
can you transform the words surrounded by stars with uppercase versions, i.e.
The QUICK BROWN fox jumped over the LAZY dog
Given text in a column sentence in table sentences, I
On 19 Oct 2012, at 15:09, "Albe Laurenz" wrote:
> You should always include the list in your replies -
> other people might be interested in the solution.
Oops, thought I had.
>
> There must be at least two transactions involved
> to create a locking problem like you describe.
> But since CRE
On 19 Oct 2012, at 13:28, Frank Lanitz wrote:
> Just an idea without bigger investigation: Whare are the idle in
> transactions are doing? Maybe they are blocking the create index.
>
> Cheers,
> Frank
Good question, I don't know. The app runs on Java / Apache Tomcat, which
maintains a connecti
unt(*) from pg_class;
count
---
5361
(1 row)
I wonder if I'm running up against some sort of limit. I am going to change the
code so it doesn't add an index (it's not always necessary) but would like to
get to the bottom of things first.
Regards
Oliver Kohll
www.gtwm.co
Here's a bit of positive news spin - in a backhanded way perhaps, but still a
compliment:
http://www.theregister.co.uk/2012/08/31/postgresql_too_cool_for_school/
Oliver
www.agilebase.co.uk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscript
instance, given a
client has just proposed running on one. If there are none forthcoming in the
short term I may be in a position to provide some results myself in a month or
two.
Oliver Kohll
www.agilebase.co.uk
On 22 Mar 2012, at 10:17, Albe Laurenz wrote:
>> Or is there a better way of finding view dependencies? I see there's a
> pg_catalog entry for tables
>> that a view depends on but that's not what I'm after.
>
> You can use pg_depend and pg_rewrite as follows:
>
> SELECT DISTINCT r.ev_class::reg
s on but that's not what I'm
after.
Regards
Oliver Kohll
www.agilebase.co.uk
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 28 Jan 2012, at 15:27, "Greg Sabino Mullane" wrote:
>> Is this a case for multi master do you think?
>> I.e. running one on the internet, one locally.
>
> Yes, could be.
>
>> b) changing schemas (new tables, fields, views etc.) as well as data
>
> That's a tall order; I don't think anythin
ables, fields, views etc.) as well as data
Create/update/delete frequencies are reasonably low, generally individuals
updating single records so of the order of thousands per day max.
Any experiences/thoughts?
Oliver Kohll
www.gtwm.co.uk
--
Sent via pgsql-general mailing list (pgsql-ge
T 0) x WHERE x.invoiced = false
>
> Regards
>
> Pavel Stehule
>
> 2011/11/5 Oliver Kohll - Mailing Lists :
>> b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
JOIN dbvcalc_delivery_charges ON b2deliveryorders.idb2deliveryorders =
dbvcalc_delivery_charges.idb2deliveryorders
WHERE b2deliveryorders.complete = false AND b2deliveryorders.invoiced = false
ORDER BY b2deliveryorders.expectedby NULLS FIRST;
Oliver
Begin forwarded message:
> From: Oliver Kohll - Maili
joins to a second report dbvcalc_delivery_charges which
I can also send if necessary. I've only guesses as to the reasons the default
plan is slow or how to affect it, can someone enlighten me?
Regards
Oliver Kohll
www.agilebase.co.uk
--
Sent via pgsql-general mailing list (pgsql-general@postgresq
Many thanks both, those solutions are great and have gone in my wiki for future
ref.
Regards
Oliver
On 21 Sep 2011, at 21:56, Szymon Guz wrote:
>
>
> >> Short answer is: yes. More information you can find
> >> here
> >> http://simononsoftware.com/problem-with-random-in-postgresql-subselect/
>
27;, E'\\d', trunc(random() * 9 +
1)::text,'g');
regexp_replace
+1 111 111 111
(1 row)
As you can see, it returns the same digit each time. I've tried wrapping a
select around the trunc too.
Regards
Oliver Kohll
www.gtwm.co.uk / www.agilebase.co.uk
Hi, great to see unlogged tables. There was discussion in the lists a while ago
about various options for what would happen on server restart. I understand
after a crash they'll be truncated but what about after a clean restart? Are
they guaranteed to retain all committed data? If so I'll defini
ourse what you decide depends on what the business use case is and what
demands there are on the system. In my cases so far the slowest charts take 1
or 2 seconds to generate by SQL so if necessary, each could be loaded in in
real time over AJAX, though that hasn't been needed yet.
Regards
Oliver Kohll
earth() looks
like it returns a datatype of type earth, so not sure if it will work. Maybe
things have changed in a recent release, please let me know if so.
So an example would be
select point(-2.2171,56.8952)<@>point(-1.2833,51.6667) as miles;
miles
------
3
wo lat/longs, the point<@>point syntax is simple to use:
http://www.postgresql.org/docs/8.3/static/earthdistance.html
Regards
Oliver Kohll
oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
on is will
the replication coming in v9.0 change things and would it be worth holding off
until then? In particular Command Prompt's PITR tools look useful for restoring
to a particular point in time, will these still work or will there be
equivalents?
Regards
Oliver Kohll
oli...@agilebas
oops=1)
-> Seq Scan on a2e9a7e9e257153de (cost=0.00..833.58
rows=24126 width=32) (actual time=0.032..26.683 rows=12605 loops=1)
Total runtime: 44.396 ms
Regards
Oliver Kohll
oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company
signup_date)
>> )
>>
>> (adjust for typos, I didn't test it)
>
> Yes that does work thanks, if you give the subquery a name. I'd still like to
> know if it's possible to do with a window function rather than a subquery.
>
> Oliver Kohll
>
>
Y extract(year from signup_date)
> )
>
> (adjust for typos, I didn't test it)
Yes that does work thanks, if you give the subquery a name. I'd still like to
know if it's possible to do with a window function rather than a subquery.
Oliver Kohll
mail_address),
sum(count(email_address)) over (partition by 1 order by 1 asc rows unbounded
preceding)
from email_list group by 1 order by 1;
Does anyone have any other ideas?
Regards
Oliver Kohll
oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
www.
er/src/com/gtwm/pb/model/manageSchema/DatabaseDefn.java
- private void updateViewDbAction
is the top level function.
Regards
Oliver Kohll
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
rrors - some view updates can work just fine
anyway.
I can point you to the relevant code in GitHub if you're interested (it's Java).
Regards
Oliver Kohll
oli...@agilebase.co.uk / +44(0)7814 828608 / skype:okohll
www.agilebase.co.uk - software
www.gtwm.co.uk - company
On 24 Apr 2010,
,signup_date) as
month, count(*)
FROM test
GROUP BY year, month
ORDER BY year, month;
giving
year | month | count
--+---+---
2010 | 2 | 1
2010 | 3 | 2
How would you make the count a cumulative one? The output should then be
year | month | count
--+---
don't have properties like listed above?
>
> Thank you in advance, Cleens
Hello, there are one or two (including one I developed) under 'GUI builders' at
pgFoundry:
http://pgfoundry.org/softwaremap/trove_list.php?form_cat=323
Regards
Oliver Kohll
oli...@agilebase.co.uk / +44(0)845 456 1810
www.agilebase.co.uk - software
www.gtwm.co.uk - company
't installed the new version yet, it is advisable to follow it
if you plan to install the new version in parallel with the old version'.
So for someone using RPM packages to install Postgres, what's the recommended
sequence to do this?
Regards
Oliver Kohll
www.agilebase.co.uk
--
asedata=# SELECT pg_catalog.pg_get_viewdef('572838'::pg_catalog.oid) AS
viewdef;
viewdef
Not a view
(1 row)
Views are regularly altered, dropped and created in the agilebasedata database,
15 times today to date, which have all succeeded without error.
Any help on what could ca
On 27 Sep 2009, at 21:10, InterRob wrote:
Peter, may I invite you to privately share some more details on the
system you are using and the design of it? Did you implement it
using PostgreSQL? Looking forward to your reply.
(And with respect to your previous message: whom are you actually
including checks for data integrity.
Hi Rob,
Just wondering if you've considered rapid prototyping of the core of
it to try and gain consensus by giving people something they can see
and talk about, as an alternative to doing a lot of design work up
front?
Regards
Oliver Kohll
echs can do simpler tasks. For
example I recently used some of the new windowing functions (yay!) in
a view but users typically add/remove fields, filters and aggregate
calculations.
Info at www.gtportalbase.com, it's also just gone on github as open
source.
Oliver Kohll
sion numbers. I
assume that using the more general 'numeric' rather than all
combinations of these would have a performance penalty?
Regards
Oliver Kohll
oli...@gtwm.co.uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product
---
-- This routine cre
be good reasons for not treating this. I've come across
comments such as 'I think everybody would agree that this would be a
bad thing to do!' but remain to be convinced.
I know you can use CASE and NULLIF but if you have complex
calculations, that makes them a lot less re
On 8 Jun 2009, at 19:01, David Fetter wrote:
Hello!
I've the following data:
datetime | val1 | val2
time1|4 | 40%
time2|7 | 30%
time3| 12 | 20%
...
I'd like to sum up the following:
(7-4)*30% + (12-7)*20% + ...
This is best done in 8.4 using Windowing. Sadly, it's an
On 8 Jun 2009, at 17:23, Merlin Moncure wrote:
Is there a way when creating a table to limit it to one row? That
is,
without using a stored procedure?
I searched the documentation, but didn't find anything.
CREATE TABLE x (...);
CREATE UNIQUE INDEX x_only_one_row ON ((1));
very cleve
On 4 Jun 2009, at 13:11, Sam Mason wrote:
You need to take care of only one case here: denominator == 0; rest
of the
cases will be handled sanely by the database.
CASE WHEN packet_size = 0 THEN null ELSE cost/packet_size END as
unit_cost
Yes; or even shorter:
cost/nullif(packet_size,
milar to
CASE WHEN cost IS NULL THEN null
WHEN pack_size IS NULL THEN null
WHEN cost = 0 THEN null
WHEN pack_size = 0 THEN null
ELSE cost / pack_size
AS unit_cost
I don't want to write new functions, I'd rather keep it in plain SQL.
Best regards
Oliver Kohll
oli...@gtwm.co.uk / 084
e seem like the sorts of things that would
get good writeups at varlena.com but I see there haven't been any new
posts there in a couple of years. My question is, is anyone planning
to blog / write focussing on these features?
Oliver Kohll
www.gtwm.co.uk - company
www.gtportalbase.com - p
our help. Now considering more. Any larger scale experiences?
Regards
Oliver Kohll
oli...@gtwm.co.uk / 0845 456 1810 / 07814 828608
www.gtwm.co.uk - company
www.gtportalbase.com - product
d my processor usage from very little to practically
negligible with no adverse effect. I believe you can define table-
specific options if necessary.
That default_statistics_target parameter is larger than the default of
10, presumably by design? It'll also create more processing.
Regar
Thanks all, option 2 seems to work for me, just wanted to be sure I
wasn't asking for crashes.
Oliver Kohll
On 8 Jun 2008, at 18:01, Tom Lane wrote:
Oliver Kohll <[EMAIL PROTECTED]> writes:
What I've thought of trying so far is
1) creating a symlink called libpq.so.4 to
Hello,
I have the same issue as this poster with libpq.so.4:
http://www.nabble.com/8.3.0-upgrade-td16093803.html
In short, I've upgraded to 8.3.1 from 8.1 on RHEL 4 (with some CentOS
packages). I have apps with dependencies of libpq.so.4 but this is no
longer available. 8.3.1 provides libpq.
If upgrading MediaWiki to a version that supports postgres 8.3,
another option could be to export from the old db as XML (using the
Special:Export page) then import once the new wiki's installed with a
blank database - that's what I'm going to try, I've had problems
upgrading MediaWiki data
ssary. This
would mean that I could be sure that all queries could be optimised
whether they break the 'slow' barrier or not.
Regards
Oliver Kohll
GT webMarque
www.gtportalbase.com
[EMAIL PROTECTED] / 0845 456 1810 / 07814 828608
The Old Bank, 4 Ravenhill Rd, Swansea SA5 5AW, UK
objects with SQL
on the command line.
Regards
Oliver Kohll
Looking at pg_stat_user_indexes, it seems a lot of the indexes aren't
actually used. That could be it.
Regards
Oliver
Begin forwarded message:
From: Oliver Kohll <[EMAIL PROTECTED]>
Date: 17 November 2007 15:06:38 GMT
To: pgsql-general@postgresql.org
Subject: max_fsm_relatio
od idea?
Postgres version is 8.1.9
Regards
Oliver Kohll
[EMAIL PROTECTED] / 0845 456 1810 / 07814 828608
The Old Bank, 4 Ravenhill Rd, Swansea SA5 5AW, UK
NOTE
No contracts may be concluded on behalf of GT webMarque by means of e-
mail
communications. The contents of this e-mail ar
56 matches
Mail list logo
