[GENERAL] Create database from template and include comment

[Peter Devoy]

Hi all

If when creating a database I use another user-defined database as a
template I would like the option for the comment on the template database
to also exist for the new database.  Am I correct in thinking there is no
feature for this, perhaps because the global nature of pg_shdescription?

For an example of the default behavior:

CREATE DATABASE test_comment_template;
COMMENT ON DATABASE test_comment_template IS 'hello world';

CREATE DATABASE test_comment WITH TEMPLATE test_comment_template;

SELECT oid, datname, shobj_description(oid, 'pg_database')
FROM   pg_database
WHERE  datname LIKE 'test_comment%';

  oid   |datname| shobj_description
+---+---
 828611 | test_comment_template | hello world
 828612 | test_comment  |

DROP DATABASE test_comment;
DROP DATABASE test_comment_template;

Kind regards


Peter

Re: [GENERAL] Python versus Other Languages using PostgreSQL

[Peter Devoy]

> My question still remains though - why is it that all the largest web 
> platforms that have used PostgreSQL *specifically* choose Python as their 
> back-end language?
If you write the developers a nice tweet or e-mail they might tell
you.  Anything else is going to be speculation because there are a
plethora of domain, human and environmental factors which could lead
to language and architecture choices, not least of which is skills
available in the labour market.

Peter

3XE
P: 01326 567155
M: 07770 693662
A: 3XE Ltd
Tremough Innovation Centre
PENRYN
TR10 9TA
3XE Ltd · Registered in England and Wales · 9356871


On 9 May 2017 at 14:45, D'Arcy Cain  wrote:
> On 2017-05-09 05:26 AM, Francisco Olarte wrote:
>>
>> Do you have any data supporting that? AFAIK people tend to choose the
>> language first, database second, not the other way round, and many
>> times the platform language is nailed, but the db can be changed.
>> Also, WHICH platforms are you referring to?
>
>
> Perhaps it has something to do with the license.  Both Python and PostgreSQL
> have a BSD type licence.  I wonder if the platform of choice tends to be one
> of the BSDs as well.
>
> --
> D'Arcy J.M. Cain  |  Democracy is three wolves
> http://www.druid.net/darcy/|  and a sheep voting on
> +1 416 788 2246 (DoD#0082)(eNTP)   |  what's for dinner.
> IM: da...@vex.net, VoIP: sip:da...@druid.net
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Any experiences with using PhpUnit's DbUnit extension with Postgres?

[Peter Devoy]

Hi all

This DbUnit extension for PHPUnit is driving me insane.  On my
deathbed, the days of my life I have wasted debugging this testing
framework will haunt me.

Basic things like serial IDs fields, boolean values and timestamps
seem to require omission or unholy workarounds in order to get tests
to run in an expected fashion.

I have been at this so long I no longer know if I am awake and
ignorant, or asleep and in some absurdist nightmare.  Before I throw
in the towel and find a different solution, please has anyone
experienced success or failure with this framework?

Kind regards


Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Function out there to identify pseudo-empty fields, e.g. "n/a", "--", etc?

[Peter Devoy]

Hi all

Just wondering if anyone has come across a function which can identify
typical user-inputted placeholder values?

English language is my scope so typical examples would be "not
applicable", "n/a", "na", "none", "--", etc.

I know it would be trivial to create but no sense in reinventing the wheel.

Kind regards


Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How well does PostgreSQL 9.6.1 support unicode?

[Peter Devoy]

The comments in here may be of help:
https://github.com/postgres/postgres/blob/master/src/include/mb/pg_wchar.h

Kind regards


Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] CRM where pg is a first class citizen?

[Peter Devoy]

>I was looking for a open source CRM, PHP or python based, with a large 
>community where Postgresql is a first class citizen.

About 20 months ago when I started trading I settled for EspoCRM which
uses PHP + MySQL.  Despite some bugs I was quite enjoying it before my
local VPS company disappeared without paying their datacentre bills.
I did submit a pull request for one bug and it was accepted quickly.

If a few people are interested it may not be too laborious to add
Postgres support, it looks like the database code could use
improvement anyway:
https://github.com/espocrm/espocrm/search?utf8=%E2%9C%93=PDO

KR

Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Return results of join with polymorphically-defined table in pl/pgsql

[Peter Devoy]

>Outputs two columns, one polymorphic match and one constant.
Nice.

>I couldn't figure out a way to get the output into columns.
I have had a fair play and am struggling also.  Seems like any work around
is going to be too unholy to be worth running.

Thanks for having a crack!


Peter

Re: [GENERAL] Return results of join with polymorphically-defined table in pl/pgsql

[Peter Devoy]

@David, thanks for the tip.

>Providing a concrete example might help.
My use case is a database with a large number of spatial tables.  I
have written a spatial search function which, given an arbitrary table
extended with PostGIS, will search for records in that table whose
geometries are within a given distance.  The return value is a SETOF
values 'geometry ID', 'distance from input geometry' and 'centroid'
with corresponding types (int, double precision, geometry).

The final desired output is a resultset consisting of all the input
tables columns as well as these two new columns showing distance and
centroid.  Obviously having an ID field this can be achieved with
INNER JOIN.  The ideal scenario would be to have a function which also
performs this join... something like:

BEGIN
RETURN QUERY
EXECUTE
format(
'
SELECT
%1$I.*,
dist_query.distance AS appended_distance,
dist_query.centroid AS appended_centroid
FROM %1$I
INNER JOIN distance_search(%1$L, $1, $2, %2$L) AS dist_query
ON %1$I.%2$I=dist_query.%2$I;
',
pg_typeof(table_name),
id_column_name
)
USING search_area, buffer_size;
END;


@John
>SQL tables are /not/ polymorphic.
Yes, you are quite right.  I merely meant the table who's row compound
type is been passed as a polymorphic parameter.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Return results of join with polymorphically-defined table in pl/pgsql

[Peter Devoy]

Hi list

The example at the bottom of this
(http://stackoverflow.com/questions/11740256/11751557#11751557) answer
shows how the anyelement polymorphic type can be used to have a
function accept -- and return rows from -- an arbitrary table decided
by the user at runtime.

However, I would like to create a function which returns the resultset
of an INNER JOIN with table1 being polymorphic and table2 being a
result set of column types which do not change.  Is this possible?

I have seen a solution using CREATE TEMP VIEW but I understand this
could race if the function was called multiple times in the same
session.  Obviously there would be way to minimize that risk but
things would start to get messy.

Any ideas would be much appreciated.

Kind regards


Peter Devoy


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Stored procedure version control

[Peter Devoy]

>does anybody have any comment on that from the POV of PostgreSQL?

Might be overkill but you could deploy your procedure as an extension
because extensions come with version control:
https://www.postgresql.org/docs/current/static/sql-createextension.html

Another option might be to hack something together using COMMENT ON
FUNCTION [...]:
https://www.postgresql.org/docs/current/static/sql-comment.html

Kind regards


Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Skip trigger?

[Peter Devoy]

If your other inserts can wait you could maybe combine DISABLE TRIGGER
with LOCK TABLE?

http://www.postgresql.org/docs/current/static/sql-lock.html

Someone else may know a better solution...


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does this perf output seem 'normal'?

[Peter Devoy]

I believe the library is spawning a new connection for each query so
that makes perfect sense.

I will see what improvements can be made at the application layer and
look at using pgbouncer if required =)

Thank you for sharing your knowledge!


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does this perf output seem 'normal'?

[Peter Devoy]

>Also do you  know that in perf report second column is the total cpu  time 
>share and not the first
Thank you, I did notice they did not add up -- that makes more sense now.

>Can you expand node from perf report
Yes, my pleasure.  I added "--call-graph fractal,2,callee" to the
command, hopefully it seems more useful:

https://cloud.githubusercontent.com/assets/4375326/14889937/a1378982-0d58-11e6-89b6-aac5e1620fbb.png

To my amateur eye it seems like there is suspicious amount of memory &
thread management going on...?


--
Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does this perf output seem 'normal'?

[Peter Devoy]

I have now done a recording for 60 seconds during a batch of 1000
requests and posted the results on a new issue on the Mapnik repo.

Although Postgres still comes out on top in the perf results I
struggle to believe this is a Postgres issue.  But, if anyone is
curious, the issue is here:
https://github.com/mapnik/mapnik/issues/3414

I may come back here if I don't have luck with the Mapnik developers.

All the best


Peter
3XE
P: 01326 567155
M: 07770 693662
A: 3XE Ltd
Tremough Innovation Centre
PENRYN
TR10 9TA
3XE Ltd · Registered in England and Wales · 9356871


On 27 April 2016 at 16:45, Peter Devoy <pe...@3xe.co.uk> wrote:
>> If you really want to profile this, you should fire it off in a tight loop, 
>> using wget or ab2 or curl.
>
> Thanks Jeff, that sounds like a smart idea.  I will try later when I
> have access to the server.
>
>>Hi! What do you want to see in perf stats? Maybe you can explain your problem 
>>more in details?
>
> Hi Alex )  I am hoping to find out which function calls are taking the
> longest because to the code is taking too long to only parse a small
> XML file and do a few quick database queries.  The last version of
> this software was able to do a lot more in the same period of time.  I
> realise this most likely a client software issue but was hoping it may
> be obvious whether Mapnik is spending too much time with Postgres.
>
> Unfortunately I may just have to try again to build Mapnik from source
> so that I can get debugging/profiling output, etc. -- I was hoping to
> avoid it because it is quite painful.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Does this perf output seem 'normal'?

[Peter Devoy]

> If you really want to profile this, you should fire it off in a tight loop, 
> using wget or ab2 or curl.

Thanks Jeff, that sounds like a smart idea.  I will try later when I
have access to the server.

>Hi! What do you want to see in perf stats? Maybe you can explain your problem 
>more in details?

Hi Alex )  I am hoping to find out which function calls are taking the
longest because to the code is taking too long to only parse a small
XML file and do a few quick database queries.  The last version of
this software was able to do a lot more in the same period of time.  I
realise this most likely a client software issue but was hoping it may
be obvious whether Mapnik is spending too much time with Postgres.

Unfortunately I may just have to try again to build Mapnik from source
so that I can get debugging/profiling output, etc. -- I was hoping to
avoid it because it is quite painful.


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Does this perf output seem 'normal'?

[Peter Devoy]

Hi all,

I am trying to work out why a piece of software, Mapnik, is executing
slowly.  All it is doing is loading a config file which causes about
12 preparation queries (i.e. with LIMIT 0) to be executed.  I can see
from pg_stat_statements these only take ~1ms in their totality.

So next I ran "perf record -a -g sleep 0.4" and quickly loaded my PHP
page to start the process (I am interfacing with mapnik via a PHP
Extension), this is a sample of the result:

https://cloud.githubusercontent.com/assets/4375326/14836015/08b0a8b4-0c05-11e6-96e5-f1836497cdfe.png

The PHP page returns in ~60ms (well within the 400ms used for the perf
recording).  If possible, please can anyone with more familiarity with
perf and Postgres tell me whether the result of the recording looks
healthy?

I am wondering if there is some problem with the way in which Mapnik
is implementing libpq -- I already moved from Mapnik 2.2 because it
was effectively DOSing Postgres by leaving client connections open.

Any help would be greatly appreciated.


Kind regards

Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] index build faster on 8G laptop than 30G server

[Peter Devoy]

I see you are comparing 9.2 and 9.4 -- if you were not already aware
there is a kernel restriction on shared memory on Linux systems which,
if I recall correctly, must be adjusted in versions before 9.3...

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server#shared_buffers

Not saying it's the answer but maybe another thing to look at...

All the best


Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] $foo $bar is BAD

[Peter Devoy]

> Although people commonly use $foo $bar in examples, it is actually a misuse 
> of a VERY rude acronym.

> The next time you need to make an example, please try being a little more 
> original (or meaningful) with your variable names.

In light of recent CoC decisions, I would like to propose the
opposite.  I think more expletives would
diversify the language of the documentation and lower the barriers to
contribution by individuals more
dispositioned to use colourful language due to their cultural and/or
socioeconomic background. O:-)


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] Cross DB insert with returning id

[Peter Devoy]

You may wish to consider using the Postgres Foreign Data Wrapper to
access your other database:

http://www.postgresql.org/docs/9.3/static/postgres-fdw.html

And then use RETURNING to get your ID, e.g.:

INSERT INTO distributors (did, dname) VALUES (DEFAULT, 'XYZ Widgets')
   RETURNING did;

http://www.postgresql.org/docs/9.3/static/sql-insert.html

Kind regards


Peter

On 14 April 2016 at 14:50, Ritanjali M  wrote:
> Hi Team,
>
> I am new to postgresql ,i need to create one function where i have to insert
> data into cross database table from that table identity value i need to
> insert some other table other database within one function .i am able to
> insert the data into cross DB table using dblink_exec but i unable to get
> the  identity value ,so how could i will get identity value from cross
> DB.please help me out with the example.
>
> --
> Thanks & Regards
> Ritanjali Majhee


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ON CONFLICT DO NOTHING RETURNING

[Peter Devoy]

Hi all

Is it possible to have ON CONFLICT DO NOTHING RETURNING or must I use DO
UPDATE?

E.g. if the 'name' column of my 'animals' table is unique it seems a little
silly having to do an arbitrary update to get an ID from the row:

INSERT INTO animals (name) VALUES ('dog') ON CONFLICT (name) DO UPDATE SET
name='dog' RETURNING animal_id;

Is there a reason DO NOTHING was not developed for use with RETURNING?

Either way, upsert is great, I am glad we have it now.

Kind regards


Peter Devoy

Re: [GENERAL] Anyone compare PG 9.5 and MongoDB 3.2?

[Peter Devoy]

> MongoDB has released 3.2 with their WiredTiger storage.  Has anyone
> benchmarked 9.5 against it, and for JSONB elements several MB in size?
>
> PJ

Hi Paul

I do not have an answer for you but there is a great talk here in
which someone explains why they moved from a NoSQL stack to Postgres:
https://www.youtube.com/watch?v=UgcC_bY4rPg

If I recall correctly JSON functionality was touched upon but, if you
have not seen it, the whole talk is worth a watch.

Hope this helps in some way.

Kind regards


Peter Devoy


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] bloated postgres data folder, clean up

[Peter Devoy]

> Hope this may be useful

Thanks for sharing!

Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

[Peter Devoy]

As part of the extension I am writing I am trying to create a trigger
procedure in which the value of the primary key of the NEW or OLD row
is used. The trigger will be fired by arbitrary tables so the column
name must be dynamic.  Something like:

pk_column := 'foo_id'; --example assignment only

EXECUTE 'INSERT INTO  bar (baz) VALUES ($1)'
USING NEW.quote_literal(pk_column);

Out of desperation I have pretty much brute forced many weird
combinations of quote_literal, quote_ident, ::regclass, || and USING.
Unfortunately, I have not been able to get anything to work so any
help would be very much appreciated.

Thanks for reading


Peter Devoy


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Re: [GENERAL] How can I use a dynamic column name after NEW. or OLD. in trigger procedure?

[Peter Devoy]

>Argh!  I *always* type the wrong one.  It should be %I instead of %L
You're not alone, I did the same thing in my example with quote_literal. -_-

Thank you all very much for your solutions -- I can end this 14hr day
on a high note!

Peter


-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general