[GENERAL] Cast hstore type to bytea (and later to hex possibly)

Hello, I would like to achieve something like the following: COPY (select 'a=>x, b=>y'::hstore::bytea) TO STDOUT; I have implemented an hstore value iterator that works with pqxx::result::field.c_str() (which has its own binary serialisation format) and I would like to reuse it to work with reco

Re: [GENERAL] Creating table with data from a join

write to wal if the wal_level is minimal and hence cuts IO about in half. On Tue, Jul 14, 2015 at 1:42 PM David Rowley wrote: > On 14 July 2015 at 21:12, Igor Stassiy wrote: > >> Hello, >> >> I am benchmarking different ways of putting data into table on table >>

Re: [GENERAL] Creating table with data from a join

I can't take it down then change some parameters and start it up with again). On Tue, Jul 14, 2015 at 6:37 PM Julien Rouhaud wrote: > On 14/07/2015 18:21, Igor Stassiy wrote: > > Julien, I have the following setting for WAL level: #wal_level = minimal > > (which defaul

Re: [GENERAL] Creating table with data from a join

Julien, I have the following setting for WAL level: #wal_level = minimal (which defaults to minimal anyway) On Tue, Jul 14, 2015 at 6:19 PM Julien Rouhaud wrote: > On 14/07/2015 11:12, Igor Stassiy wrote: > > Hello, > > > > I am benchmarking different ways of putting dat

Re: [GENERAL] Creating table with data from a join

rsion from text ->internal is significantly slower than that of from internal -> text). I will also try your suggestion with limiting the ids range. On Tue, Jul 14, 2015 at 1:42 PM David Rowley wrote: > On 14 July 2015 at 21:12, Igor Stassiy wrote: > >> Hello, >> >

[GENERAL] Creating table with data from a join

Hello, I am benchmarking different ways of putting data into table on table creation: 1. INSERT INTO c SELECT * FROM a JOIN b on a.id = b.id; 2. CREATE TABLE c AS SELECT * FROM a JOIN b on a.id = b.id; 3. psql -c "COPY (SELECT * FROM a JOIN b on a.id = b.id) TO STDOUT" | parallel --block 128M --j

[GENERAL] Error handling in C API function calls in a way that doesn't close client connection

Hello all, This question refers to version 9.4 of Postgres. I have have a function Datum do_something(PG_FUNCTION_ARGS) { ... if(error_occured) { ereport(ERROR, (errmsg("some error occured"))); } ... } When I call do_something in a way to deliberately cause the error cond

[GENERAL] Memory management in postgres (with liblwgeom functions in particular)

Hello, I am developing a C++ extension (most of the code is C++) for postgres that links dynamically with liblwgeom, without linking to postgis. I call liblwgeom functions that serialize/deserialize LWGEOM* (and similar structures) that don't need a backend like GEOS. I wonder how is the memory f

[GENERAL] Difference between light-weight geometry types

Hello, is there any difference between LWCOLLECTION and LWCOMPOUND? Has the meaning changed since postgis 1.5 (when liblwgeom wasn't yet packaged) to liblwgeom >= 2.0? Also, what does LWCIRCSTRING stand for? Is it a closed line string? Thanks a lot, Igor

[GENERAL] Get extensions directory programmatically in another Makefile

Hello, This might seem like an XY problem, but I need to solve it. I am using pgxs to install an extension but I need to refer to the location of the installation directory in another Makefile. How safe is it to assume that MODULE_PATHNAME that I use for CREATE FUNCTION ... AS 'MODULE_PATHNAME'

Re: [GENERAL] Postgres not using GiST index in a lateral join

http://stackoverflow.com/questions/28856452/postgres-not-using-gist-index-in-lateral-join On Thu, Mar 5, 2015 at 11:54 AM Alban Hertroys wrote: > > > On 04 Mar 2015, at 22:18, Igor Stassiy wrote: > > > > I would like to stop executing the query for a row of table "a"

Re: [GENERAL] Postgres not using GiST index in a lateral join

custom) lets you winnow a result > set down to just one of the inputs. > > P. > > > On Wed, Mar 4, 2015 at 6:36 AM, Igor Stassiy wrote: > > Hello, > > > > I have a query plan optimization question. It is formatted nicely on > > > > http://stackoverflo

[GENERAL] Postgres not using GiST index in a lateral join

Hello, I have a query plan optimization question. It is formatted nicely on http://stackoverflow.com/questions/28856452/postgres-not-using-gist-index-in-lateral-join But here is a copy for the archive: Here is the setup: CREATE EXTENSION postgis; DROP TABLE IF EXISTS A; DROP TABLE IF EXISTS B;

[GENERAL] ANALYZE command question

Hi, Will calling ANALYZE table; twice in a row actually run the command twice ? Or there is some sort of check that if the table is not changed since the time of first call the second command will not actually be run? Thanks, Igor

[GENERAL]

unsubscribe

[GENERAL] domain gets cast to a text type

Hi, you might not be able to run this code, but maybe you know why is there a type conversion with domain and the concept might apply to other examples as well. I have the following code: CREATE FUNCTION get_key_jsonb(key text, j jsonb) RETURNS text LANGUAGE plv8 IMMUTABLE STRICT AS $$ plv8.elo

[GENERAL] Initializing JsonbValue from cstring

Hi, Is there a way to initialize JsonbValue from cstring from public c api in postgres 9.4? Without the use of functions declared with PG_FUNCTION_ARGS . I posted the following question earlier, but received no reply.

[GENERAL] checking if jsonb was detoasted and releasing memory

Let us say that the datum of type Datum contains a Jsonb* type. Then after the call Jsonb *jb = DatumGetJsonb(datum); the jb might point to a palloc'ed structure, in case detoasting took place. So the question is if this is the right way to free up the memory after checking that the jb was detoas

[GENERAL] postgresql jsonb processing with c api

Hi, I am developing a postgres extension. The extension gets json data as a string from external source and is supposed to be able to store this string in a Jsonb type. I am working with C API for postgres-9.4 installed from ubuntu trusty main repo. I would like to use a function that converts a