k of what worked and what didn't.
Sorry for wasting time.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ze value not
expecting the human-readable value?
Thanks for suggestions
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 29 February 2016 at 14:06, Jim Mlodgenski wrote:
> No they are not the same. When you don't include a unit for
> effective_cache_size, it defaults to page size so you're saying 2146435072 *
> 8K
Hah.
Thanks Jim, like I said I was sure I'd be missing something :)
Ge
On 29 February 2016 at 14:07, Geoff Winkless wrote:
> On 29 February 2016 at 14:06, Jim Mlodgenski wrote:
>> No they are not the same. When you don't include a unit for
>> effective_cache_size, it defaults to page size so you're saying 2146435072 *
>> 8K
>
&g
Just as a continuation of this, I can set effective_cache_size to 64MB
and it will still use the single-column index, but PG flatly refuses
to use the multicolumn index without effective_cache_size being an
unfeasibly large number (2x the RAM in the machine, in this case).
Geoff
--
Sent via
be attractive to
the planner though. I can mess with those figures but as I said before
the only one that flicks the switch is to change effective_cache_size
to 8GB, which makes no sense to me.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to yo
On 29 Feb 2016 22:47, "Kevin Grittner" wrote:
>
> On Mon, Feb 29, 2016 at 2:10 PM, Geoff Winkless
wrote:
>
> > I'm not really sure what changes I could make that would make one
> > index that's ostensibly equivalent to the other not be attractive to
>
^
HINT: No operator matches the given name and argument type(s). You
might need to add explicit type casts.
Time: 0.608 ms
db=# SELECT 'yes' WHERE 1<>-1;
?column?
--
yes
(1 row)
I get this with fieldnames too, so it's not just a parsing-litera
in place of !=; quite apart from being ANSI
(which I hadn't realised) it has the advantage over != that I get
nostalgia for 1983 and BASIC :)
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
he_size is set to 3GB (but changing it wildly up or down
doesn't change anything), shared_buffers is 1GB, work_mem is 5242kB
(but changing to anything up to 1GB makes no difference).
Thanks
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 7 March 2016 at 11:48, Victor Yegorov wrote:
> 2016-03-07 13:38 GMT+02:00 Geoff Winkless :
>>
>> # EXPLAIN (ANALYZE,BUFFERS) SELECT MIN(sc_id) FROM legs WHERE scdate
>> BETWEEN 20160219 AND 20160221;
>
>
> Will it help if you'll add `count(*)` to your query
ues in the table (unless the index is larger than
the table itself, I suppose).
I might suggest that perhaps the rule should be relaxed so that an
inequality constraint does not stop the subsequent columns being used
in an index-only scan.
That assumes that I've not completely misunderstood, of
visits to the table proper, but they do not
reduce the portion of the index that has to be scanned.
So it seems that it should in fact be usable after all. So I'm still
stumped as to why the (scdate,sc_id) index isn't used :(
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@
e
index, even ignoring the scdate/sc_id index.
So I'm afraid I'm fully back in the "I still don't get it" column.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 7 March 2016 at 14:51, Tom Lane wrote:
> Geoff Winkless writes:
>> So it seems that it should in fact be usable after all. So I'm still
>> stumped as to why the (scdate,sc_id) index isn't used :(
>
> Because the other way is estimated to be cheaper. The es
cid-only index
suffers.
And the real advantage: at the extremes, the index-only worst-case is
minimally worse than the best case. Whereas the worst-case of the
index-scan-plus-table-compare method is horrific.
I don't believe you need any further statistics than what is currently
available to
On 7 March 2016 at 16:44, Tom Lane wrote:
> Geoff Winkless writes:
>> But as far as I can see, apart from the absolute extremes, the
>> index-only scan is _always_ going to be quicker than the index+table
>> scan.
>
> Well, that is a different issue: what does the
the planner than (scdate,sc_id) and why the index that was
transferred from the Other Database that we've migrated from isn't
useful here :)
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s still not too bad (1.3 seconds or so).
The point is that to assume, knowing nothing about the data, that the
data is in an even distribution is only a valid strategy if the worst
case (when that assumption turns out to be wildly incorrect) is not
catastrophic. That's not the case here.
Ge
On 12 March 2016 at 18:43, Peter J. Holzer wrote:
> On 2016-03-08 10:16:57 +0000, Geoff Winkless wrote:
>> On 7 March 2016 at 20:40, Peter J. Holzer wrote:
>> > As Tom wrote, the estimate of having to read only about 140 rows is only
>> > valid if sc_id and sc_date a
ood on that perfect distribution but behave
better on data that is not perfectly distributed.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
s no difference.
Table pa has 7522676 rows, 4834042 of which have field1 NULL, so it's
absolutely not reasonable to expect this to be an optimal strategy.
Any suggestions as to how I can improve this query?
Thanks :)
Geoff
creating a subquery out of the legs dataset in the hope that
that would help but that made no difference either.
Geoff
lues in pa.field1, so I suppose it might seem more
attractive to the planner than it should do (that's more unique values than
there are scdate entries).
I might just set enable_seqscan to false and leave it at that. It makes me
unhappy though.
Geoff
ke)
field2-16 which are used in similar queries.
I'll stick with enable_seqscan=off, it seems to be doing the trick;
thanks though.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ww.monetdb.org/content/citusdb-postgresql-column-store-vs-monetdb-tpc-h-shootout
" the margin by which MonetDB outperforms cstore_ftw shows that only
switching storage models alone is probably not enough"
Geoff
(Disclaimer: I've no connection to MonetDB in any way)
--
Sent via pgsql-ge
umbers in the tens of
thousands of records at most (and if it scales to tens of millions
then great).
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
1 intersect select 1;
> ?column?
> --
> 1
> (1 row)
>
> postgres=# select intersect select;
> --
> (2 rows)
See above.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
ent):
SELECT student_id, name
FROM student
LEFT JOIN exam USING(student_id)
WHERE exam_date=CURRENT_DATE AND exam.student_id IS NULL;
Not really sure what the issue is with either of those. Once learned,
they're both very easy and straightforward.
Geoff
--
Sent via pgsql-genera
On 24 April 2016 at 12:29, Geoff Winkless wrote:
> To find students with no exam today (the other point of your argument):
>
> SELECT student_id, name
> FROM student
> LEFT JOIN exam USING(student_id)
> WHERE exam_date=CURRENT_DATE AND exam.student_id IS NULL;
*sigh* problem w
topic in the
list with this? I'm sure that anyone who's interested in this will
have seen it in the thread you created that was actually marked with
it in the subject.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
erstand the
business rules and the data behind it, and the application developers
can ask those experts to do the heavy lifting for them. Having to
persuade management that they should no longer be able to connect the
database to MS Access and make changes that way will usually put an
end to that p
r the queries you run to
optimise to a particular DBMS.
But you're right, if you've started out well, it will at least
minimise the amount of change.
Geoff
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
sort of Holy Grail that would definitely
> convince them. A standard client tool that would come with any PostgreSQL
> installation would please them. Some sort of psqlGUI, I guess.
Why reinvent the wheel? I would say that putting the development
effort into the OpenOffice Base app would be t
On 17 May 2016 at 10:22, Achilleas Mantzios
wrote:
> On 17/05/2016 12:16, Geoff Winkless wrote:
>>
>> On 17 May 2016 at 09:34, Pierre Chevalier Géologue
>> wrote:
>>>
>>> On this matter, I hear *very* often from such guys that the only reproach
>>
reinstalling the
locales package, but that didn't help.
Any help would be appreciated; I have quite a bit of data in my 8.1 cluster
(active phpBB, etc.), and while I could upgrade to 8.2 or even the 8.3 beta,
I don't know how I would be able to migrate a database if the postmaster
process is unable to start.
-- Geoff
On Dec 19, 2007 7:40 AM, Richard Huxton <[EMAIL PROTECTED]> wrote:
> Geoff Ellingwood wrote:
>
> Any help would be appreciated; I have quite a bit of data in my 8.1cluster
> > (active phpBB, etc.), and while I could upgrade to 8.2 or even the 8.3beta,
> > I don&#x
Hi there,
Have been trying to uninstall old instances of Postgres from my Snow
Leopard install, preparing to install 9.0
Not sure how old these instances are (probably dates back to 7). I can
see them in the active process list, but I'm not sure how to
permanently stop them. Any old timers have s
n for
this. See:
http://blog.2ndquadrant.com/how-do-postgresql-security_barrier-views-work/
PostgreSQL 9.4 (out in a couple months) will be enhancing security_barrier
views, so they will also be updatable without requiring the use of triggers.
Geoff
On 24 September 2014 15:45, Stefan Carl wrote:
> ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON
> TABLES TO
> readonly;
> ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA public GRANT SELECT ON
> SEQUENCES
> TO readonly;
> ALTER DEFAULT PRIVILEGES FOR ddl_user IN SCHEMA pub
irstname,
p.lastname,
count(*)
FROM
people p
JOIN visits v on p.id = v.person_id
GROUP BY p.id;
Geoff
to be UNIQUE anyway (otherwise you
end up in all sorts of trouble).
Geoff
On 7 February 2013 11:18, Thomas Kellerer wrote:
> Geoff Winkless, 07.02.2013 11:46:
>
>> On 7 February 2013 09:38, Chris Travers > <mailto:chris.travers@gmail.**com >> wrote:
>>
>> 1: The foreign key depends on the function so the function cannot be
>&g
could be in.
Apologies if I'm misunderstanding your point; couldn't you give the index
name (since we've suggested you would have to have a unique index on the
function in order to use it as an FK anyway) as the identifier?
Geoff
point of the query, but wasn't it to add an entry for
each email address and (if it already exists in people) to update the
firstname field to match the firstname in the order?
Given that the results will be returned in random order I would just change
the "nd" query to SELECT DISTINCT ON (email) , but like I said I may be
missing the point.
Geoff
Date functions are inherently not immutable because of timezones. Your
solution of using to_timestamp doesn't help because it automatically
returns a value in WITH TIMESTAMP. Do you get anywhere by using
"::timestamp without time zone" instead, as suggested here?
https://www.postgresql.org/message-id/4E039D16.20704%40pinpointresearch.com
Geoff
On 26 February 2017 at 16:09, Adrian Klaver
wrote:
> On 02/26/2017 07:56 AM, Geoff Winkless wrote:
> > On 26 February 2017 at 10:09, Sven R. Kunze > <mailto:srku...@mail.de>>wrote:
> >
> > >>># create index docs_birthdate_idx ON docs using
table
todate function and thinks they can use it for something else.
Geoff
Would the fact that you can have month names in to_date strings make it
dependent on current locale?
Geoff
locale is specified in the format string).
The only way to do it would be to add to_date(string, string, string) where
the third string specifies the locale, at which point I don't really see
why you would gain anything over creating your own UDF.
Geoff
ity in that what ANSI SQL would reject as not-a-date
might be parsed as a date. I'm not in a position to judge if either of
those would be acceptable.
Geoff
On 3 March 2017 at 12:17, Sven R. Kunze wrote:
> On 03.03.2017 11:43, Geoff Winkless wrote:
>
> One alternative would be to make to_date accept all language variants of
> months simultaneously. A quick search of google suggests that there aren't
> any overlaps between lan
ef, is likely to provoke exactly that sort of negative
pushback, and does little but make your life harder.
Geoff
ell script. If you have strong experience in a particular language then
you should use that language, and invest time in optimising your data
structures and developing faster algorithms.
Geoff
suing the product
design, but I'm not really surprised that the MTBF is worse: if the
shingled disk must write some tracks twice for each individual track write,
it seems logical that there will be more write stress and therefore
shortened lifespan, no?
Geoff
ents where source_id = $SOURCE_ID and ineffective_date is
null" | while true; do
if [ $a -gt $biga ] ; then
biga=$a
fi
if ! read a; then echo $biga; break; fi
done
)
A mess, but it works.
To be honest, by the time you've got to this level of complexity you
probably sho
eeded, is that the
problem? Will PQreset only work on a connection that had previously
connected successfully?
If that _is_ the case, is there a way to tell up-front whether this is
the case (so I can retry PQconnectdb instead of calling PQreset)?
Thanks
Geoff
--
Sent via pgsql-general m
send" on this email light dawned: the code that
calls our "creates db connection" function is not checking the return
value correctly, and PQreset fails because our that function wipes out
the connection object (assuming that it cannot be used).
Sorry to waste bandwidth.
Geoff
They don't apply server-wide. They apply to that user's session:
http://www.postgresql.org/docs/8.4/interactive/sql-set.html
Geoff
On Wed, Apr 30, 2014 at 6:19 AM, Hello World wrote:
>
> SET statement_timeout=0;
>> SET work_mem=1024GB;
>>
>
> I just realize
ication. There's a wiki page that suggests PostgreSQL support was in
development at one time. I'm not sure how far they got, or if they are
still working on it.
http://www.continuent.com/solutions/replication
https://wiki.postgresql.org/wiki/Tungsten
Geoff Montee
Hi All,
I'm Geoff, a long time postgresql user and been a reasonably regular
participant on #gentoo and #postgresql on freenode for several years
now, helping out new folks where I can. I'm a student (phd) at the
University of Technology, Sydney in Australia. (I've also been
Jerry Stuckle says...
> pg wrote:
> > The server would run Linux or one of the BSD variant
> You also missed DB2, SQL Server and several others.
Scrap MSSQL Server as a candidate given the above O/S requirement.
Geoff M
Running 3 RHEL4 boxes in an Oracle RAC cluster, with we
In article , Jerry Stuckle says...
> Geoff Muldoon wrote:
> > Jerry Stuckle says...
> >> pg wrote:
> >
> >>> The server would run Linux or one of the BSD variant
> >
> >> You also missed DB2, SQL Server and several others.
> >
's case there is still acceptable
remaining flexibility of choice.
My upcoming project will have the RDBMS mandated. I can live with that,
because the option imposed on me can more than comfortably handle the
requirements. Is this ideal? No. Are the motives for this imposed
restricti
't want the
error clogging up my logs. But I don't want to suppress the
error as the query is business-critical and I need to know
if anything goes wrong.
I'd welcome suggestions as to how I can get this query to
run without throwing a date/time error when the result set
2337
etc...
Any pointers would be much appreciated!
------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
ple of
hours of agony, I suspect.
--
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
.
If someone can convert this data into postgresql and send me
a pg_dump of it, I'd be happy to pay ~ $US50.
Cheers,
Geoff Russell
-
6 Fifth Ave, Home: 08 8332 5069
St Morris 5068 Office: 08 8207 2029
Adelaide Australia
chine with (as far as I can see!) the
same software and which works perfectly...???
The admin config files are below.
Cheers,
Geoff Russell
/etc/odbc.iniowned by root 644
[testdb]
Description = testdb
Driver = PostgreSQL
Trace
I have a clue to this problem, see below.
On 3/11/07, Geoff Russell <[EMAIL PROTECTED]> wrote:
Hi all,
I have postgresql 8.1, unixodbc and openoffice on a Ubuntu 6.06.1
machine and can connect to my postgresql
databases fine --- but can't edit data, only view. pgadmin3 also only
all
ness of moving the queries into
the database against SQL injection attack would seem to depend on the
query engine internals. Will using the SQL functions provide the
robust protection I am looking for?
--
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154
-
ww.net-security.org/article.php?id=571
But so far as I can see, Peter's suggestion should provide a workable
robust solution. So thanks again!
--
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154
---(end of broadcast)---
T
s easier to adopt a
particular style of programming ("any query using untrusted data MUST
be a parameterised prepared statement") than to analyse each and every
untrusted parameter for attack signatures? Or am I missing something
here?
--
Geoff Caplan
Vario Software
(+44) 1
omatically)
Are you saying that "@bar" is part of the Postgres query syntax? If it
is, I can't find it in the 7.4 docs. What do you mean by "the
parameterised formats"? I would appreciate a clarification and the URL
of the relevant documentation.
Thanks
-
n PHP, Tcl etc?
For example, with SQL Server, it appears you can slip in a
single-quote using encodings that are cast to ASCII #39 by the server.
Also, what about pg functions like convert()? Could they be used to
smuggle in a breakout character?
Looked at like this, perhaps robust escaping is
Tom,
Belated thanks for the info (I've been away from my desk).
Very helpful.
------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154
---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings
ecord-set?
There will probably be 6-8 SELECTs & UPDATEs for each INSERT.
I appreciate that I could set up some tests, but I am under the hammer
time-wise. Some rule-of-thumb advice from the list would be most
welcome.
------
Geoff Caplan
Vario Software Ltd
(+44) 121
e the sequence to cache some
>>> number of entries so that they are pre-allocated and stored in memory
>>> for each session (e.g. - for each connection) for quicker access. See
>>> the documentation for "create sequence" for more details.
ld be month day year;
>
> iso
> ----
>1999-12-31
Right.
>
> german
>
>31.12.1999
Right.
Cheers,
Geoff Russell
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
6 Fifth Ave
Hi,
Here's a perl script that I use to grant read access to all users and
everything to a few people, it handles tables and associated sequences.
Cheers,
Geoff Russell
P.S. If you use perl/dbi, you don't really have to worry about this stuff
because your programs connect with s
ut problems.
- I re-intalled phpPgAdmin from scratch.
So, advice would be very welcome - it's a great app and I am missing
it!
Geoff Caplan
Caplan Associates
PS I did post this to the phpPgAdmin specialist list but without
reply. Sorry for the cross posting, but I am very keen to get this
fixe
ut problems.
- I re-intalled phpPgAdmin from scratch.
So, advice would be very welcome - it's a great app and I am missing
it!
Geoff Caplan
Caplan Associates
PS I did post this to the phpPgAdmin specialist list but without
reply. Sorry for the cross posting, but I am very keen to get this
fixe
ration file,
and it shows up ok if I "echo $PGDATA" in the shell. But none of the
postgres utilities such as "initdb" seem to be able to find it.
What don't I understand? I have already checked the docs/GreatBridge
manual/faqs/archive, so I would very much appreciate some help.
Geoff Caplan
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
running, but pg_ctl can't find it
- postmaster has started with the default datapath and is ignoring
$PGDATA
I get the same result if I launch automatically during startup.
Can anyone please help me make some sense of this? I am losing the
will to live...
Geoff C
top quality docs require specific skills and
resources which it is perhaps unreasonable to expect from an open source
project. It will probably take a commercial effort from GreatBridge or a
book to improve things. GreatBridge have made a start. I hope they continue
to test and develop their docs
pported.
insert into prosb (animal_s, defendant_name) values('dog','Mike');
ERROR: internal error: untrusted function not supported.
I tried using plpgsql instead of sql, but PG tells me the language isn't
registered.
Cheers,
Geoff,
[EMAIL PROTECTED]| Phone: +618-8332-5069
6 Fifth Ave, St Morris, SA 5068 | Fax: +618-8364-1543
On Sun, 12 Mar 2000, Johann [iso-8859-1] Höchtl wrote:
> Hi!
>
> I subscribed to the pgsql mailing-list digest version. But since friday i
> don't get the digest but every single mail. For me, this is very annoying !
Dit
101 - 187 of 187 matches
Mail list logo
