Re: [GENERAL] Do parallel queries work with only dblink not with fdw?

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

Re: [GENERAL] FTS with more than one language in body and with unknown query language?

n as well. So I think we still have some issues to resolve...? :Stefan 2016-07-15 11:02 GMT+02:00 Artur Zakirov <a.zaki...@postgrespro.ru>: > Hello, Stefan! > > On 15.07.2016 01:54, Stefan Keller wrote: >> >> приве́т! Artur >> >> Thanks for your expla

Re: [GENERAL] FTS with more than one language in body and with unknown query language?

приве́т! Artur Thanks for your explanations. 2016-07-14 17:20 GMT+02:00 Artur Zakirov <a.zaki...@postgrespro.ru>: > 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

[GENERAL] FTS with more than one language in body and with unknown query language?

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

Re: [GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

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

Re: [GENERAL] Slides for PGCon2016; "FTS is dead ? Long live FTS !"

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

Re: [GENERAL] PostgreSQL 9.5 and process REST calls enquiry

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

[GENERAL] FOSDEM 2016, 30th and 31st January 2016, Brussels: Call for Participation

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

Re: [GENERAL] Postgresql Development Options

Hi, 2015-04-06 0:28 GMT+02:00 Adrian Klaver adrian.kla...@aklaver.com: ... 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

Re: [GENERAL] Overlap function for hstore?

)) 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 david.g.johns...@gmail.com: On Fri, Apr 3, 2015 at 5:37 PM, Stefan Keller sfkel...@gmail.com

[GENERAL] Overlap function for hstore?

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 test for

Re: [GENERAL] splitting up tables based on read/write frequency of columns

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 postg...@2xlp.com: This is really a

Re: [GENERAL] Postgres as In-Memory Database?

...@redhat.com: 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 if this is still used by Postgres. Back in the old days

Re: [GENERAL] Postgres as In-Memory Database?

? Regards, S. [1] http://citusdata.com/blog/76-postgresql-columnar-store-for-analytics 2014-04-02 0:32 GMT+02:00 Stefan Keller sfkel...@gmail.com: Hi Yeb Thanks for the pointers. Of course disk access is not obsolete: As I said, I suppose changes are streamed to disk. When I mentioned

Re: [GENERAL] Postgres as In-Memory Database?

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

Re: [GENERAL] Postgres as In-Memory Database?

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 Andrew Sullivan a...@crankycanuck.ca: On Mon, Apr 07, 2014 at 10:43:58PM +0200, Stefan Keller wrote: running out

Re: [GENERAL] Postgres as In-Memory Database?

Hi Jeff I agree with most of your statements. 2014-04-01 4:20 GMT+02:00 Jeff Janes jeff.ja...@gmail.com: On Sunday, March 30, 2014, Stefan Keller sfkel...@gmail.com wrote: Hi Jeff 2013/11/20 Jeff Janes jeff.ja...@gmail.com I don't know what you mean about enhancements in the buffer

Re: [GENERAL] Postgres as In-Memory Database?

+02:00 Yeb Havinga yebhavi...@gmail.com: On 2014-04-01 04:20, Jeff Janes wrote: On Sunday, March 30, 2014, Stefan Keller sfkel...@gmail.com wrote: Hi Jeff 2013/11/20 Jeff Janes jeff.ja...@gmail.com I don't know what you mean about enhancements in the buffer pool. For an in-memory

Re: [GENERAL] Postgres as In-Memory Database?

Hi Jeff 2013/11/20 Jeff Janes jeff.ja...@gmail.com On Mon, Nov 18, 2013 at 5:39 PM, Stefan Keller sfkel...@gmail.com wrote: Hi Jeff and Martin On 18. November 2013 17:44 Jeff Janes jeff.ja...@gmail.com wrote: I rather doubt that. All the bottlenecks I know about for well cached read

Re: [GENERAL] client that supports editing views

Hi, 2013-12-09 Thomas Kellerer spam_ea...@gmx.net: 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

Re: [GENERAL] client that supports editing views

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]

Re: [GENERAL] client that supports editing views

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:

Re: [GENERAL] Postgres as In-Memory Database?

Hi Bruce 2013/11/20 Bruce Momjian br...@momjian.us On Sun, Nov 17, 2013 at 09:00:05PM +0900, Michael Paquier wrote: On Sun, Nov 17, 2013 at 8:25 PM, Stefan Keller sfkel...@gmail.com wrote: How can Postgres be used and configured as an In-Memory Database? Does anybody know

Re: [GENERAL] Postgres as In-Memory Database?

Dear Bricklen and Andrew 2013/11/19 bricklen brick...@gmail.com On Mon, Nov 18, 2013 at 11:16 PM, Stefan Keller sfkel...@gmail.com wrote: I don't think there's any evidence that the Postgres developers ignore useful optimisations. What you're arguing is that the optimisation you have

Re: [GENERAL] Postgres as In-Memory Database?

case here is a PostGIS query of an OpenStreetMap data of the whole world (see [3]). On 2013/11/18 Jeff Janes jeff.ja...@gmail.com wrote: On Sun, Nov 17, 2013 at 4:02 PM, Stefan Keller sfkel...@gmail.com wrote: BTW: Having said (to Martijn) that using Postgres is probably more efficient, than

Re: [GENERAL] Postgres as In-Memory Database?

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 a...@crankycanuck.ca On Tue, Nov 19, 2013 at 02:39:17AM +0100, Stefan Keller wrote: Referring to the application is something you can

[GENERAL] Postgres as 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 the

Re: [GENERAL] Postgres as In-Memory Database?

/non-durability.html 2013/11/17 Edson Richter edsonrich...@hotmail.com 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? Does anybody know of thoughts or presentations about

Re: [GENERAL] Postgres as In-Memory Database?

, Stefan Keller sfkel...@gmail.com wrote: 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

Re: [GENERAL] Postgres as In-Memory 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 klep...@svana.org 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-mostly database is the main

Re: [GENERAL] Postgres as In-Memory Database?

2013/11/18 Andreas Brandl m...@3.141592654.de 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

[GENERAL] How to append an element to a row inside a 2-dim. array?

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[]);

Re: [GENERAL] How to append an element to a row inside a 2-dim. array?

... 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 sfkel...@gmail.com: Hi, Question regarding arrays: How can I append an element to a row inside a 2-dim. array? See example below

[GENERAL] Database (Schema) Objects?

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

[GENERAL] Reading an OUT parameter out of a function call

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

Re: [GENERAL] Reading an OUT parameter out of a function call

Thank you Keane and all. That works for me too. Yours, Stefan 2013/2/25 Russell Keane russell.ke...@inps.co.uk: 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

[GENERAL] Query with LIMIT but as random result set?

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

Re: [GENERAL] [postgis-users] Query with LIMIT but as random result set?

function. Yours, Stefan 2013/1/8 Brooks Kehler brookskeh...@gmail.com: this should work - order by random() limit 10; On Tue, Jan 8, 2013 at 10:20 AM, Stefan Keller sfkel...@gmail.com wrote: Hi I have a query like this SELECT ST_AsText(way) geom, name AS label FROM osm_point

Re: [GENERAL] Permission denied in file_fdw (Windows)

, 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 postgres user (or whatever user you're using in Windows). The file is opened from a PostgreSQL

[GENERAL] Permission denied in file_fdw (Windows)

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

[GENERAL] Storing files in the database - and giving easy access to this?

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 Windows

Re: [GENERAL] Expression alias not recognized in WHERE clause (ERROR: relation p does not exist)

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 haram...@gmail.com wrote: You're referencing p as a table, not as a table-alias, because

[GENERAL] Expression alias not recognized in WHERE clause (ERROR: relation p does not exist)

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

Re: [GENERAL] How to convert integer to string in functions

Hi, 2011/8/12 David Johnston pol...@yahoo.com: 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

Re: [GENERAL] How to convert integer to string in functions

2012/3/20 Chris Angelico ros...@gmail.com: On Tue, Mar 20, 2012 at 7:14 PM, Stefan Keller sfkel...@gmail.com 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'::int; After trying hard

Re: [GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas?

Hi all, 2012/3/14 Thomas Kellerer spam_ea...@gmx.net: 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? -Stefan What about an extension

Re: [GENERAL] Interesting article, Facebook woes using MySQL

Hi all 2011/7/12 Chris Travers chris.trav...@gmail.com: 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

[GENERAL] Call for Google Summer of Code (GSoC) 2012: Project ideas?

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:

Re: [GENERAL] Four issues why old elephants lack performance: Explanation sought Four issues why old elephants lack performance: Explanation sought

Hi, 2012/2/27 Chris Travers chris.trav...@gmail.com 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

Re: [GENERAL] Four issues why old elephants lack performance: Explanation sought Four issues why old elephants lack performance: Explanation sought

Hi Scott 2012/2/26 Scott Marlowe scott.marl...@gmail.com: On Sun, Feb 26, 2012 at 1:11 PM, Stefan Keller sfkel...@gmail.com 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

Re: [GENERAL] Four issues why old elephants lack performance: Explanation sought Four issues why old elephants lack performance: Explanation sought

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

[GENERAL] Four issues why old elephants lack performance: Explanation sought Four issues why old elephants lack performance: Explanation sought

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

Re: [GENERAL] [postgis-users] ST_AsJpeg

2012/2/16 Sandro Santilli s...@keybit.net: 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

Re: [GENERAL] [postgis-users] ST_AsJpeg

Hi Regina 2012/2/14 Paragon Corporation l...@pcorp.us 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...

Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

Oliver Jowett oli...@opencloud.com: On 9 January 2012 14:29, Stefan Keller sfkel...@gmail.com wrote: 2012/1/9 Oliver Jowett oli...@opencloud.com: 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

Re: [JDBC] [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

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

Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

docs (http://jdbc.postgresql.org/documentation/head/binary-data.html ) Yours, Stefan 2012/1/6 Stefan Keller sfkel...@gmail.com: 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

Re: [GENERAL] Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

in the JDBC docs: http://jdbc.postgresql.org/documentation/head/binary-data.html Stefan 2012/1/8 Thomas Kellerer spam_ea...@gmx.net: 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 on class MyData

Re: [GENERAL] Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

and https://hibernate.onjira.com/browse/HHH-4876 for some background of the dilemma. Stefan 2012/1/8 Thomas Kellerer spam_ea...@gmx.net: 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. That's a valid

Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

. Yours, Stefan 2012/1/8 Radosław Smogura rsmog...@softperience.eu: 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 inserts the binary string in system table

Re: [GENERAL] Re: Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

spam_ea...@gmx.net: Stefan Keller wrote on 08.01.2012 20:35: 2012/1/8 Thomas Kellererspam_ea...@gmx.net: What's the reason for you to stick with LargeObjects? I simply used the @Lob annotation in Hibernate/JPA. That's all to get a leaking pg_largeobject table. See http://relation.to/Bloggers

Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

this to the JDBC list (which currently seems to have problems accepting new subscriptions). 2012/1/8 Radosław Smogura rsmog...@softperience.eu: On Sun, 8 Jan 2012 21:57:37 +0100, Stefan Keller wrote: Thanks, Radosław, for the clarification. 2012/1/8 Radosław Smogura rsmog...@softperience.eu wrote: 3

Re: [GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

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 oli...@opencloud.com: On 9 January 2012 12:40, Stefan Keller

[GENERAL] Binary Large Objects (LOB/BLOB) in Hibernate and JDBC: Unresolved issues

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

[GENERAL] How PostgreSQL handles Binary Large Objects (LOB/BLOB): types BYTEA, OID/pg_largeobjects and DATALINK

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

Re: [GENERAL] Vacuum and Large Objects

Hi Igor 2011/12/16 Igor Neyman iney...@perceptron.com 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

[GENERAL] Howto to clear PostgreSQL cache and how to preload all tuples of a table into the cache?

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

[GENERAL] Any experiences with Foreign Data Wrappers (FDW) like mysql_fdw, odbc_fdw, www_fdw or odbc_fdw?

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

Re: [GENERAL] Indexes not allowed on (read-only) views: Why?

Craig Ringer ring...@ringerc.id.au: 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 =  Why should'nt it be possible to create indexes on views in PG? It's not so much

[GENERAL] Indexes not allowed on (read-only) views: Why?

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 method

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

Hi Tom 2011/6/15 Tom Lane t...@sss.pgh.pa.us: Stefan Keller sfkel...@gmail.com 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't

[GENERAL] No implicit index created when adding primary key with ALTER TABLE

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

Re: [GENERAL] No implicit index created when adding primary key with ALTER TABLE

Hi Thom 2011/6/14 Thom Brown t...@linux.com: 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

Re: [GENERAL] How to check a table content efficiently? With LIMIT and OFFSET?

Hi Jaime 2011/5/30 Jaime Casanova ja...@2ndquadrant.com wrote: On Sun, May 29, 2011 at 4:55 PM, Stefan Keller sfkel...@gmail.com wrote: 2. There's an autovacuum background process which already does the job, doesn't it? Yes, but in its own time. If you know there has been a batch

Re: [GENERAL] How to check a table content efficiently? With LIMIT and OFFSET?

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 cr...@postnewspapers.com.au: On 05/29/2011 05:45 AM, Stefan Keller wrote: Hi, That's my solution candidate: CREATE OR REPLACE FUNCTION

Re: [GENERAL] How to check a table content efficiently? With LIMIT and OFFSET?

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?!? That's _really_ long. How big is that table

[GENERAL] How to check a table content efficiently? With LIMIT and OFFSET?

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

Re: [GENERAL] How to check a table content efficiently? With LIMIT and OFFSET?

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 sfkel...@gmail.com: Hi, I’d like to monitor

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed (hstore)

(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 2011/5/1 Tom Lane t...@sss.pgh.pa.us: Stefan Keller sfkel

Re: [GENERAL] Values larger than 1/3 of a buffer page cannot be indexed (hstore)

...@sss.pgh.pa.us: Stefan Keller sfkel...@gmail.com writes: Any ideas on how to index my hstore attribute? Use a GIST or GIN index.  The only thing that a btree index on hstore can do for you is to support equality comparisons on the whole hstore value, which is pretty unlikely to be what you're after

[GENERAL] Values larger than 1/3 of a buffer page cannot be indexed (hstore)

Hi, 2011/3/13 Viktor Nagy viktor.n...@toolpart.hu 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

[GENERAL] How to configure a read-only database server and session? (Was: read-only UNLOGGED tables)

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

Re: [GENERAL] Insert value input syntax of an array of types without ARRAY/ROW nor casting?

:33, Stefan Keller wrote: 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.postgresql.org/docs/current/static/arrays.html INSERT

[GENERAL] Insert value input syntax of an array of types without ARRAY/ROW nor casting?

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:

[GENERAL] Query sought with windowing function to weed out dense points

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

Re: [GENERAL] Need advise for database structure for non linear data.

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

Re: [GENERAL] How to guard PostgreSQL from overcharged and/or malicious (read-only) queries?

commands, one malicious 2010/12/20 Alban Hertroys dal...@solfertje.student.utwente.nl: On 20 Dec 2010, at 10:05, Stefan Keller wrote: 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

[GENERAL] How to guard PostgreSQL from overcharged and/or malicious (read-only) queries?

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

Re: [GENERAL] One-click Installers for 9.1 Alpha 2 (ETA)

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:

Re: [GENERAL] hstore equality-index performance question

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 gray...@gmail.com: My

Re: [GENERAL] hstore equality-index performance question

? 'a'::text) My question is, if one can get also index support for the '-' operator? -S. 2010/3/29 Sergey Konoplev gray...@gmail.com: On 29 March 2010 02:57, Stefan Keller sfkel...@gmail.com wrote: Documentation at F.13.3. Indexes says that hstore has index support for @ and ? operators... = Therefore

[GENERAL] hstore equality-index performance question

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

Re: [GENERAL] Custom Fields Database Architecture

@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 to

[GENERAL] Asssociative Arrays: Best practices / snippets?

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.

Re: [GENERAL] Asssociative Arrays: Best practices / snippets?

). 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 rules ;- -S. 2009/5/23 Steve Atkins st...@blighty.com On May 23, 2009, at 2:25 AM, Stefan