Hi,
Sorry for appending to that thread, but I think this is related:
Does anyone have experience with parsel [1] and/or it's extension
parallelsql [2]?
:Stefan
[1]
http://geeohspatial.blogspot.ch/2013/12/a-simple-function-for-parallel-queries_18.html
[2] https://github.com/k1aus/parallelsql
ave some issues to resolve...?
:Stefan
2016-07-15 11:02 GMT+02:00 Artur Zakirov :
> Hello, Stefan!
>
> On 15.07.2016 01:54, Stefan Keller wrote:
>>
>> приве́т! Artur
>>
>> Thanks for your explanations.
>>
>> 2016-07-14 17:20 GMT+02:00 A
приве́т! Artur
Thanks for your explanations.
2016-07-14 17:20 GMT+02:00 Artur Zakirov :
> On 14.07.2016 01:16, Stefan Keller wrote:
...
>> * Should I create a synonym dictionary which contains word
>> translations en-de instead of synonyms en-en?
>
> This synonym dict
Hi,
I have a text corpus which contains either German or English docs and
I expect queries where I don't know if it's German or English. So I'd
like e.g. that a query "forest" matches "forest" in body_en but also
"Wald" in body_de.
I created a table with attributes body_en and body_de (type "text
Hi Oleg
2016-05-29 19:54 GMT+02:00 Oleg Bartunov :
> We chose RUM just because there are GIN and VODKA :)
> But some people already suggested several meanings like Really Useful
iMdex :)
> We are open for suggestion.
iMdex LOL :-)
Ok. What's new about the index?
* AFAIK it's using methods as ex
Hi,
Nice work from you postgrespro.ru guys! Especially the RUM index which
demonstrates the power of 9.6 to let third party SW create access methods
as extension: https://github.com/postgrespro/rum
1. I don't understand the benchmarks on slide 25 "20 mln descriptions" (and
the one before "6.7 mln
I'd like to extend (and complete) this thread by collecting
programs/frameworks/applications that provide REST services over
Postgres.
I see following categories and open source web frameworks and/or libs:
Those which
* attach Postgres directly, like PostgREST (Haskell) or jsgrest
(JavaScript serv
Hi,
The sixteenth edition of FOSDEM will take place on Saturday 30th and
Sunday 31st January 2016 at the usual location, the ULB Campus
Solbosch in Brussels.
There's a upcoming deadline for first batch of main track proposals:
16 October 2015.
And there's e.g. a proposal for a Geospatial devroomː
.value))
FROM (
SELECT each((select 'a=>1,b=>2,c=>3'::hstore)) as tmp_attr
INTERSECT ALL
SELECT each((select 'a=>2,d=>4,b=>2'::hstore)) as tmp_attr
) tmp_table;
Cheers, S,
2015-04-04 3:16 GMT+02:00 David G. Johnston :
> On Fri, Apr 3, 2015 at 5:37
Hi,
2015-04-06 0:28 GMT+02:00 Adrian Klaver :
...
> I have in the past used Dabo:
>
> http://dabodev.com/
...
I would have recommended Dabo too.
Or Camelot http://www.python-camelot.com/ .
Or you can still use Qt with SQLAlchemy which seems to support some
PostgreSQL data types::
http://docs.sq
Hi,
I'd like to get an overlap function similar to '&&' but for key-value
pairs of hstore!
This underfits:
postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) &&
hstore_to_array('a=>2,d=>4,b=>2'::hstore)
...because array overlaps op takes every element (even 'a' or 2 alone)
and doesn't
Hi
I'm pretty sure PostgreSQL can handle this.
But since you asked with a theoretic background,
it's probably worthwhile to look at column stores (like [1]).
-S.
[*] http://citusdata.github.io/cstore_fdw/
2015-01-19 22:47 GMT+01:00 Jonathan Vanasco :
> This is really a theoretical/anecdotal que
#x27;t obviously
> amenable to this sort of use.
To me it's unclear why design of Postgres should prevent implementation of
"in-memory tables" e.g. as foreign data wrappers (see e.g. white papers
for SQL Server mentioned before).
Regards,
Stefan
2014-04-07 23:37 GMT+02:00 Andre
Hi Hadi, hi all
It makes sense to me to design cstore_fdw for volume of data which is
larger than main memory.
Coming back to my original thread, I'd like to ponder further on what makes
in-memory special - and how to configure or extend Postgres to implement
that.
I found e.g. some brand new fu
FDW in-memory?
Regards, S.
[1] http://citusdata.com/blog/76-postgresql-columnar-store-for-analytics
2014-04-02 0:32 GMT+02:00 Stefan Keller :
> Hi Yeb
>
> Thanks for the pointers.
>
> Of course disk access is not obsolete: As I said, I suppose changes are
> streamed to disk
Weimer :
> On 04/02/2014 12:32 AM, Stefan Keller wrote:
>
> It also mentions an insert-only technique: "This approach has been
>> adopted before in POSTGRES [21] in 1987 and was called "time-travel".
>> I would be interested what "time-travel" is and
ion about
future work, though.
-S.
2014-04-01 21:57 GMT+02:00 Yeb Havinga :
> On 2014-04-01 04:20, Jeff Janes wrote:
>
> On Sunday, March 30, 2014, Stefan Keller wrote:
>
>> Hi Jeff
>>
>> 2013/11/20 Jeff Janes
>>
>>>
>>> I don't k
Hi Jeff
I agree with most of your statements.
2014-04-01 4:20 GMT+02:00 Jeff Janes :
> On Sunday, March 30, 2014, Stefan Keller wrote:
>
>> Hi Jeff
>>
>>
>> 2013/11/20 Jeff Janes
>>
>>>
>>> I don't know what you mean about enhancement
Hi Jeff
2013/11/20 Jeff Janes
> On Mon, Nov 18, 2013 at 5:39 PM, Stefan Keller wrote:
>
>> Hi Jeff and Martin
>>
>> On 18. November 2013 17:44 Jeff Janes wrote:
>> > I rather doubt that. All the bottlenecks I know about for well cached
>> read-only w
Hi Wolfgang
Thanks!
This is now my ranked shortlist which I will evaluate further:
1. Camelot: http://www.python-camelot.com - PyQt
2. Dabo: http://www.dabodev.com - wxPython
3. Gui2Py: http://code.google.com/p/gui2py/ - wxPython
4. Kiwi: http://www.async.com.br/projects/kiwi - PyGTK
5. Sqlkit: h
Hi Thomas and Alexandros
Thanks for your quick replies!
If possible I'd prefer an open source framework.
I've actually found another possible solution candidate: Using Qt Designer [1].
I'm still evaluation and now keen if anybody stands up to vote for a
pgAdmin plugin...?
-- Stefan
[1] "Postmode
Hi,
2013-12-09 Thomas Kellerer :
> Willy-Bas Loos wrote on 09.12.2013 21:44:
>> I've tried:
>> * pgAdmin
>> * MS Access 2010 over ODBC
>> * LibreOffice.org with the SDBC driver.
...
> If pgAdmin is acceptable, you might want to try SQL Workbench/J:
> http://www.sql-workbench.net
I'd like to reviv
Hi Bruce
2013/11/20 Bruce Momjian
> On Sun, Nov 17, 2013 at 09:00:05PM +0900, Michael Paquier wrote:
> > On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller
> wrote:
> > > How can Postgres be used and configured as an In-Memory Database?
> > >
> > > Does an
Dear Bricklen and Andrew
2013/11/19 bricklen
> On Mon, Nov 18, 2013 at 11:16 PM, Stefan Keller
> wrote:
>
>>
>> > I don't think there's any evidence that the Postgres developers ignore
>> > useful optimisations. What you're arguing is that the
and others like Stonebraker, Oracle and SAP etc. -
see room for optimization because assumptions about HW changed. To me, that
should be enough evidence to start thinking about enhancements.
Yours, S.
2013/11/19 Andrew Sullivan
> On Tue, Nov 19, 2013 at 02:39:17AM +0100, Stefan Keller wrote
pecific use case here is a PostGIS query of an OpenStreetMap data of
the whole world (see [3]).
On 2013/11/18 Jeff Janes wrote:
> >> On Sun, Nov 17, 2013 at 4:02 PM, Stefan Keller
wrote:
>> BTW: Having said (to Martijn) that using Postgres is probably more
efficient, than progra
2013/11/18 Andreas Brandl wrote:
> What is your use-case?
It's geospatial data from OpenStreetMap stored in a schema optimized for
PostGIS extension (produced by osm2pgsql).
BTW: Having said (to Martijn) that using Postgres is probably more
efficient, than programming an in-memory database in a
emory available
affects database design e.g. that it can optimize for a working set to be
stored entirely in main memory.
--Stefan
2013/11/17 Martijn van Oosterhout
> On Sun, Nov 17, 2013 at 10:33:30PM +0100, Stefan Keller wrote:
> > I think I have to add, that pure speed of a read-mos
to slow secondary storage (like
disks) is removed (or replaced).
--Stefan
[1] http://www.postgresql.org/docs/9.1/static/non-durability.html
2013/11/17 Edson Richter
> Em 17/11/2013 10:00, Michael Paquier escreveu:
>
> On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller
>> wro
tatic/non-durability.html
2013/11/17 Edson Richter
> Em 17/11/2013 12:15, rob stone escreveu:
>
>
>> On Sun, 2013-11-17 at 12:25 +0100, Stefan Keller wrote:
>>
>>> How can Postgres be used and configured as an In-Memory Database?
>>>
>>>
>>
How can Postgres be used and configured as an In-Memory Database?
Does anybody know of thoughts or presentations about this "NoSQL feature" -
beyond e.g. "Perspectives on NoSQL" from Gavin Roy at PGCon 2010)?
Given, say 128 GB memory or more, and (read-mostly) data that fit's into
this, what are
... and I'm wondering if an index really speeds up array functions:
CREATE INDEX idx_ourarrtable_arr ON ourarrtable USING GIN(arr);
Stefan
2013/3/11 Stefan Keller :
> Hi,
>
> Question regarding arrays: How can I append an element to a row inside
> a 2-dim. array?
> See
Hi,
Question regarding arrays: How can I append an element to a row inside
a 2-dim. array?
See example below.
And:
Does anybody have experiences how arrays perform if data grows (it's
"read-mostly")?
Yours, Stefan
--
-- Arrays Test
--
CREATE TABLE ourarrtable (id int primary key, arr int[]);
I
Hi
Oracle defines database (schema) objects and "non-schema" objects (see [1]).
Is there also such a thing in Postgres?
Yours, Stefan
[1] http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements007.htm
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make cha
Thank you Keane and all.
That works for me too.
Yours, Stefan
2013/2/25 Russell Keane :
>> > I have a simple void function:
>> >
>> > CREATE OR REPLACE FUNCTION myfn(myparam OUT int) AS $$ BEGIN
>> >pnr := 1;
>> > END;
>> > $$ LANGUAGE plpgsql;
>> >
>> > How do I access myparam?
>> > I thoug
Hi,
I have a simple void function:
CREATE OR REPLACE FUNCTION myfn(myparam OUT int)
AS $$
BEGIN
pnr := 1;
END;
$$ LANGUAGE plpgsql;
How do I access myparam?
I thought this should work with 9.1/9.2: SELECT (myfn()).myparam;
Or inside another function?
Yours, Stefan
--
Sent via pgsql-general
spatial distribution function.
Yours, Stefan
2013/1/8 Brooks Kehler :
> this should work -
>
> order by random() limit 10;
>
>
>
> On Tue, Jan 8, 2013 at 10:20 AM, Stefan Keller wrote:
>>
>> Hi
>>
>> I have a query like this
>>
>>
Hi
I have a query like this
SELECT ST_AsText(way) geom, name AS label
FROM osm_point
LIMIT 10;
When I repeatedly do this, the result set will be always the same.
I have observed this only empirically and I know that the ordering of
the result set is undefined without ORDER BY.
There are tw
Tomas Vondra :
> Hi,
>
> On 5.12.2012 00:39, Stefan Keller wrote:
>> Hi
>>
>> I'm getting an error when reading from a file_fdw table in a Windows
>> environment.
>> Any hints? (see below).
>
> Well, the file clearly isn't accessible by the
Hi
I'm getting an error when reading from a file_fdw table in a Windows
environment.
Any hints? (see below).
And http://www.postgresql.org/docs/9.1/static/file-fdw.html is not
really verbose :->
At least following format options should be mentioned: 'xml', 'text',
'csv', 'binary'.
Yours, Stefan
Hi Tomas, hi all,
Nice blog, Thomas, about "storing files in the database" [1].
Now, I'd like to implement a single client script for using this
"remote file system".
The solution should implement (1) List (dir), (2) Copy (cp) and (3)
Remove (rm). It should be available for both Linux and Window
Salut Alban
Thanks for your patient hints.
As your signature suggests, I probably could not see the forest for the trees.
But now I think I do (see below) - except for the following:
2012/8/9 Alban Hertroys wrote:
> You're referencing "p" as a table, not as a table-alias, because you
> select FR
Hi
I have two (hopefully) equivalent - and unfortunately very slow - queries which
"Select all buildings that have >1 pharmacies and >1 schools within 1000m".
In the first query there is an expression alias "b" and in the second
there are two expression aliases: "b" and "p".
Can someone tell me,
2012/3/20 Chris Angelico :
> On Tue, Mar 20, 2012 at 7:14 PM, Stefan Keller wrote:
>> But this only works if the input is a clean list of number characters
>> already!
>> Anything other than this will issue an error:
>>
>> postgres=# SELECT '10'
Hi,
2011/8/12 David Johnston :
> In my table, some of the columns are in text datatype. Few data will come
> down from UI layer as integers. I want to convert that to string/text before
> saving it into the table. Please help me on this.
>
>
> SQL Standard: "CAST( value AS text )" [or varchar]
>
Hi all,
2012/3/14 Thomas Kellerer :
> Stefan Keller, 08.03.2012 20:40:
>
>> Hi
>>
>> I do have a student who is interested in participating at the Google
>> Summer of Code (GSoC) 2012
>> Now I have the "burden" to look for a cool project... Any ideas
Hi all
2011/7/12 Chris Travers :
> I am not convinced that VoltDB is a magic bullet either. I don't
I have the chance to help preparing an interview with Mike
Stonebreaker to be published at www.odbms.org
I'd really like to know, if he is up-to-date how Postgres performs
these days and how he th
Hi
I do have a student who is interested in participating at the Google
Summer of Code (GSoC) 2012
Now I have the "burden" to look for a cool project... Any ideas?
-Stefan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.post
Hi Scott
2012/2/26 Scott Marlowe :
> On Sun, Feb 26, 2012 at 1:11 PM, Stefan Keller wrote:
>
>> So to me the bottom line is, that PG already has reduced overhead at
>> least for issue #2 and perhaps for #4.
>> Remain issues of in-memory optimization (#2) and replicati
Hi,
2012/2/27 Chris Travers wrote:
>> 1. Buffering Pool
>>
>> To get rid of I/O bounds Mike proposes in-memory database structures.
...
>> Now I'm still wondering why PG could'nt realize that probably in
>> combination with unlogged tables? I don't overview the respective code
>> but I think it's
Thanks to all who responded so far. I got some more insights from Mike
Stonebraker himself in the USENIX talk Scott pointed to before.
I'd like to revise the four points a little bit I enumerated in my
initial question and to sort out what PG already does or could do:
1. Buffering Pool
To get rid
Hi,
Recently Mike Stonebraker identified four areas where "old elephants"
lack performance [1]:
1. Buffering/paging
2. Locking/Multithreading
3. WAL logging
4. Latches (aka memory locks for concurrent access of btree structures
in buffer pool?).
He claims having solved these issues while retaini
2012/2/16 Sandro Santilli :
> I don't think there's much to discuss.
> I'm sure a patch to psql would be welcome.
Sorry, I did not realize that the solution is straight forward :->
--Stefan
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscriptio
Hi Regina
2012/2/14 Paragon Corporation wrote:
> Here it is in the docs now:
>
> http://postgis.refractions.net/documentation/manual-svn/using_raster.xml.html#RasterOutput_PSQL
Citation from there: "Sadly PSQL doesn't have easy to use built-in
functionality for outputting binaries..."
Did anyon
I'm still fumbling in the dark but I think I have a smell:
Does somebody know what supportsLobValueChangePropogation according to
the current JDBC specs?
There's an interesting note there:
> NOTE : I do not know the correct answer currently for databases which (1) are
> not part of the cruise co
; On 9 January 2012 14:29, Stefan Keller wrote:
>> 2012/1/9 Oliver Jowett :
>>> As a LO is independent storage that might have multiple references to> it
>>> (the OID might be stored in many places), without explicit deletion> you
>>> need a GC mechanism to
case.
Isn't it obvious that if setImage() sets another byte[] that the image
space get's cleared by the layers below?
And since Hibernate chose to use one variant of JDBC, it's also JDBC
which has to take care about orphans.
Yours, Stefan
2012/1/9 Oliver Jowett :
> On 9 January 20
insight I also tried to forward this
to the JDBC list (which currently seems to have problems accepting new
subscriptions).
2012/1/8 Radosław Smogura :
> On Sun, 8 Jan 2012 21:57:37 +0100, Stefan Keller wrote:
>>
>> Thanks, Radosław, for the clarification.
>>
>> 2012/1/
2012/1/8 Thomas Kellerer :> So it's clearly a
Hibernate bug.
Obviously not :->
Hibernate mapping just uses one of two valid variants to map large
objects in JDBC.
So, AFAIK it's a PostgreSQL JDBC bug and an omission in the JDBC docs as well.
Stefan
2012/1/8 Thomas Kellerer :
ostgreSQL could be enhanced.
Yours, Stefan
2012/1/8 Radosław Smogura :
> On Sun, 8 Jan 2012 18:08:09 +0100, Stefan Keller wrote:
>>
>> I'd like to backup my statement below regarding in JDBC driver from
>> PostgreSQL:
>>
>> When storing fields of type BLOB it
244 and
https://hibernate.onjira.com/browse/HHH-4876 for some background of
the dilemma.
Stefan
2012/1/8 Thomas Kellerer :
> Stefan Keller wrote on 08.01.2012 19:13:
>
>>> I think you are better off using bytea unless you need to access only
>>> parts the blob regularly.
>>
entioned in the JDBC docs:
http://jdbc.postgresql.org/documentation/head/binary-data.html
Stefan
2012/1/8 Thomas Kellerer :
> Stefan Keller wrote on 06.01.2012 19:04:
>
>> I maintain images (from Webcams). In the Java and Hibernate (JPA) code
>> I specified a @Lob annotation
his in the JDBC
docs (http://jdbc.postgresql.org/documentation/head/binary-data.html )
Yours, Stefan
2012/1/6 Stefan Keller :
> Hi,
>
> I run into a nasty behavior of current PostgreSQL JDBC.
>
> I maintain images (from Webcams). In the Java and Hibernate (JPA) code
> I spe
Hi,
I'd like to get more insight of how to handle (binary) Large Object
Types (LOB/BLOB/CLOB) in Postgres and I think we should clarify the
situation since to me there are some inconsistencies at least in the
docs (or my understanding). I could try to summarize e.g. in the
Postgres Wiki.
In curre
Hi,
I run into a nasty behavior of current PostgreSQL JDBC.
I maintain images (from Webcams). In the Java and Hibernate (JPA) code
I specified a @Lob annotation on class MyData and a attribte/data type
"byte[] mydata;". Hibernate then generates two tables in PostgreSQL,
one called MyData with a c
Hi Igor
2011/12/16 Igor Neyman wrote: > But I think,
your problem is right here:
>
> " running VACUUM FULL pg_largeobject"
>
> If you are running "VACUUM FULL ..." on the table, you should follow it with
> the "REINDEX TABLE ...", at least on PG versions prior to 9.0.
I'm pretty sure that VACUU
I'd like to clear the PostgreSQL cache (e.g. for benchmarking purposes).
And I'd like to preload all tuples of a table (say mytable_one) into the cache.
AFAIK there is no way to force all caches to be cleared in PostgreSQL
with an SQL command.
The only way to achieve this, seems to restart PG (ser
Hi,
I'm interested in using Foreign Data Wrappers (FDW) in order to
connect PG to CSV files, MongoDB, MS SQL Server and the Web.
Was anyone able to compile and use FDWs [1], like mysql_fdw, odbc_fdw
or www_fdw, in PG 9.1.1 (besides official file_fdw) under Ubuntu but
also Windows?
Does anyone have
s to data?")
Stefan
2011/9/17 Craig Ringer :
> On 09/17/2011 05:47 AM, Stefan Keller wrote:
>>
>> A (read-only) view should behave like a table, right?
>>
>>> CREATE INDEX t1_idx ON t1 (rem);
>>
>> ERROR: »v1« not a table
>> SQL state: 42809
>&g
A (read-only) view should behave like a table, right?
> CREATE INDEX t1_idx ON t1 (rem);
ERROR: »v1« not a table
SQL state: 42809
=> Why should'nt it be possible to create indexes on views in PG?
An index on a view can speed up access to the tuples underlying. And
"indexed views" could be a meth
Hi Tom
2011/6/15 Tom Lane :
> Stefan Keller writes:
>> My explanation is that the message (saying that an index was
>> implicitly created) is simply wrong.
>
> The correct explanation is that you're misinterpreting whatever output
> you're looking at.
Pls. don
Hi Thom
2011/6/14 Thom Brown :
> Shouldn't you be looking for mytable2_pkey?
Yes; but that was my typo. I tried it several times on two tables.
My explanation is that the message (saying that an index was
implicitly created) is simply wrong.
Yours, S.
--
Sent via pgsql-general mailing list (pg
Hi
I observed some strange behaviour when adding a primary key with ALTER TABLE:
Given CREATE TABLE mytable1 (id serial, name text);
I filled it with data then did a
CREATE TABLE mytable2 AS SELECT * FROM mytable1;
ALTER TABLE mytable2 ADD PRIMARY KEY(id);
The last command reports - as usual
Hi Jaime
2011/5/30 Jaime Casanova wrote:
> On Sun, May 29, 2011 at 4:55 PM, Stefan Keller wrote:
>>
>>>> 2. There's an autovacuum background process which already does the
>>>> job, doesn't it?
>>>
>>> Yes, but in its own time. I
Hi Alban
On 2011/5/29 Alban Hertroys wrote:
> On 29 May 2011, at 19:45, Stefan Keller wrote:
>
>> But I'm hesitating to use ANALYZE for two reasons:
>> 1. It's very slow: it repeadly takes 59000 ms on my machine.
>
> ANALYZE on a single table takes 59s?!? Tha
ry slow: it repeadly takes 59000 ms on my machine.
2. There's an autovacuum background process which already does the
job, doesn't it?
Yours, Stefan
2011/5/29 Craig Ringer :
> On 05/29/2011 05:45 AM, Stefan Keller wrote:
>>
>> Hi,
>>
>> That's my solution ca
Hi,
That's my solution candidate:
CREATE OR REPLACE FUNCTION isnotempty() RETURNS boolean AS '
SELECT (count(*) = 1)
FROM (SELECT osm_id FROM planet_osm_point LIMIT 1 OFFSET 10) tmp
' LANGUAGE SQL;
Yours, Stefan
2011/5/28 Stefan Keller :
> Hi,
>
> I’d like to m
Hi,
I’d like to monitor a table in a read-only Postgres database by
writing a PL/pgSQL function.
The database gets periodically overwritten by a mirroring loading process.
The success criteria is, that the table contains at least some records
(actually at least more than 10).
The first idea w
: UPDATE planet_osm_point SET
tags = hstore(hstore_to_array(tags));
...with no success.
I'm running "PostgreSQL 9.1alpha1, compiled by Visual C++ build 1500,
32-bit" on Windows XP SP3.
I have a dump of the table/database at hand to anyone who is
interested in this possible bug.
Yours, Stefan
(stat.key !~~
'%description%'::text) AND (stat.key !~~ 'contact:%'::text) AND
(stat.key !~~ 'operator:%'::text) AND (stat.key !~~ 'uic_%'::text) AND
(stat.key !~~ 'TMC%'::text) AND (stat.key !~~ 'uic_%'::text) AND
(stat.key !~~ 'directi
Hi,
2011/3/13 Viktor Nagy
> when trying to insert a long-long value, I get the following error:
>
> ERROR: Index row size 3120 exceeds maximum 2712 for index
> "ir_translation_ltns"
> HINT: Values larger than 1/3 of a buffer page cannot be indexed.
> Consider a function index of an MD5 hash of
I have a single-disk virtual Linux system and a read-only dataset
which is exposed to internet and completely replaced from time to
time.
I compiled following steps in order to secure and speedup such
PostgreSQL/PostGIS instance:
1. Re-configure PostgreSQL server as following:
a. Disabling aut
Thank you for the hint.
Unfortunately it still does'nt work. I get
ERROR: wrong record constant: »('a'«
LINE 2: 5, $${ ('a', 'aa'), ('b', 'bb') }$$ );
^
DETAIL: Unexpected end of line.
Yours, S.
2011/3/19 Alban Hertroys :
>
Hi,
I'm playing around with array of types but don't get the intuitive
syntax really.
Given the test snippet below, why do the following insert attempts fail?
The literal constant would be the most intuitive syntax. The attempt
below also closely follows the documentation AFAIK:
http://www.postg
Hi,
Given a table 'peaks' with the fields id, name, elevation and geometry
I'd like to get a query which returns only peaks which dont overlap -
and from those which would do, I'd like to get the topmost one (given
a certain 'density parameter').
This problem is motivated by a visualization task
Andre,
>From a distant view of your problem I would like to vote for Thomas
Kellerer's proposal:
Maintain only the data you need (to enhance import/sync performance)
and use the hstore data type (as long as query performance is ok).
Yours, S.
2011/1/3 Fredric Fredricson :
>
> On 01/03/2011 12:11
#x27;SELECT * FROM generate_series(1, 100)); -- two commands
SELECT secure_execute('DROP TABLE IF EXISTS dummy'); -- malicious!
SELECT secure_execute('SELECT random() FROM generate_series(1, 100) AS
id;DROP TABLE IF EXISTS dummy'); -- two commands, one malicious
2010/12/20 Alban He
I'd like to guard postgres from overcharged and/or malicious queries.
The queries are strinctly read-only (from a SQL users perspective).
For doing this I'd like to code two functions (preferrably pl/pgsql):
1. Filter out all SQL commands which are *not* read-only (no
DROP/DELETE/UPDATE/TRUNCATE)
I've recently installed newest Postgres 9.1 Alpha 1
(postgresql-9.1alpha1-windows-binaries.zip from
http://www.enterprisedb.com/products/pgbindownload.do ) and got a
similar question around this:
There's pgAdmin3 v.1.13 included but it still complains when opening a
9.1alpha db saying: "Warning: T
You are right, my negligence.
I'm trying to optimize the latter query:
# SELECT id, (kvp->'a') FROM mytable WHERE kvp ? 'a';
...or something like this (which also involves the '->' operator)
# SELECT id FROM mytable WHERE (kvp->'a') = 'x';
-S.
2010/3/29 Sergey Konoplev :
>> My question is, if o
=36)
Index Cond: (kvp ? 'a'::text)"
My question is, if one can get also index support for the '->' operator?
-S.
2010/3/29 Sergey Konoplev :
> On 29 March 2010 02:57, Stefan Keller wrote:
>> Documentation at "F.13.3. Indexes" says that "hsto
Documentation at "F.13.3. Indexes" says that "hstore has index support
for @> and ? operators..."
=> Therefore no index does support equality-indexes?
If so, then I suppose that following (potentially slow) query
which contains an equality test for all keys 'a' and returns all values...
SELECT
@David: You wrote in the links cited "The "flexibility" stems from
fear of making a design decision.". That's an important note.
Nevertheless, there are use cases where you *can not* know in advance
what the name is of the attribute! To me that's not fear but
adaptiveness, modesty and knowing when
w I
won't and can't map all attributes (called tags). That's where the idea
about associative arrays came in. The KVPs would be an ANDed in a search
with "regular" columns.
So, my answer to Leif's hot-blooded judgment about KVPs could be: "Know when
to break the
I have a use case where the I want to put an unforeseable number of
key/value pairs in a column.
Now, PostgreSQL has arrays as first class types.
Are there any best practices and snippets (preferrably in plpgsql) for
handling key/value pairs?
-- S.
95 matches
Mail list logo
