Re: [GENERAL] Stored procedure version control
>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
[GENERAL] Return results of join with polymorphically-defined table in pl/pgsql
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] Return results of join with polymorphically-defined table in pl/pgsql
@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
Re: [GENERAL] Return results of join with polymorphically-defined table in pl/pgsql
>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] CRM where pg is a first class citizen?
>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&q=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] How well does PostgreSQL 9.6.1 support unicode?
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
[GENERAL] How can I use a dynamic column name after NEW. or OLD. in trigger procedure?
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?
>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
Re: [GENERAL] bloated postgres data folder, clean up
> 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
Re: [GENERAL] Anyone compare PG 9.5 and MongoDB 3.2?
> 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
[GENERAL] ON CONFLICT DO NOTHING RETURNING
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] Cross DB insert with returning id
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
Re: [GENERAL] $foo $bar is BAD
> 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] index build faster on 8G laptop than 30G server
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
[GENERAL] Does this perf output seem 'normal'?
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] Does this perf output seem 'normal'?
> 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'?
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 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'?
>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'?
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] Skip trigger?
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
[GENERAL] Function out there to identify pseudo-empty fields, e.g. "n/a", "--", etc?
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
[GENERAL] Any experiences with using PhpUnit's DbUnit extension with Postgres?
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
Re: [GENERAL] Python versus Other Languages using PostgreSQL
> 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] Create database from template and include comment
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
