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

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

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

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

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

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?

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

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?

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

[GENERAL] ON CONFLICT DO NOTHING RETURNING

g') 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:/

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

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

[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 "p

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 deta

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

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

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

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-genera

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 subscript

[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

[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

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 th

[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?