[GENERAL] Connection utilisation for pglogical

Ahead of setting up a testing environment to tryout pglogical, I'm keen to learn of the connection requirements for pglogical publisher and subscribers. Our use case is a cluster of (say) 200 databases, and we would like to look into aggregating data from a certain table using a row filter hook

Re: [GENERAL] Would you add a --dry-run to pg_restore?

On 02/08/17, Steve Atkins (st...@blighty.com) wrote: > > On Aug 2, 2017, at 9:02 AM, Edmundo Robles wrote: > > > > I mean, to verify the integrity of backup i do: > > > > gunzip -c backup_yesterday.gz | pg_restore -d my_database && echo > > "backup_yesterday is OK" >

Re: [GENERAL] Schedule

On 20/06/17, Steve Clark (steve.cl...@netwolves.com) wrote: > > 4) Equipment table keyed to location. > We already have a monitoring system in place that has been in operation circa > 2003. Just recently we have > added a new class of customer whose operation is not 24/7. > > I envision the

Re: [GENERAL] Repmgr + pgbouncer - Notification of master promotion to application level ...

On 15/06/17, Andreas Kretschmer (andr...@a-kretschmer.de) wrote: > > Am 15.06.2017 um 01:18 schrieb Martin Goodson: > > > >...Do people setup pgbouncer nodes on the database servers > >themselves, on application servers, in the middle tier between the > >application and database, and so forth, or

Re: [GENERAL] Repmgr + pgbouncer - Notification of master promotion to application level ...

On 15/06/17, Martin Goodson (kaema...@googlemail.com) wrote: > On 14/06/2017 19:54, Rory Campbell-Lange wrote: > >On 14/06/17, Martin Goodson (kaema...@googlemail.com) wrote: > >>The new master's repmgr promote script will execute commands to pause > >>pgbouncer, rec

Re: [GENERAL] Repmgr + pgbouncer - Notification of master promotion to application level ...

On 14/06/17, Martin Goodson (kaema...@googlemail.com) wrote: > The new master's repmgr promote script will execute commands to pause > pgbouncer, reconfigure pgbouncer to point to the new database address, and > then resume. You could just move the service ip address at the new postgresql master

Re: [GENERAL] JSON to INT[] or other custom type

On 11/06/17, Bruno Wolff III (br...@wolff.to) wrote: > On Sun, Jun 11, 2017 at 22:35:14 +0100, > Rory Campbell-Lange <r...@campbell-lange.net> wrote: > > > >I'm hoping, in the plpgsql function, to unfurl the supplied json into a > >custom type or at least an array

[GENERAL] JSON to INT[] or other custom type

I'm playing with plpgsql function parameters to try and come up with a neat way of sending an array of arrays or array of custom types to postgres from python and PHP. Psycopg works fine with an array of custom types: In [108]: query = 'select * from fn_test03(%s, %s::dow_session[])' In

Re: [GENERAL] Security Issues: Allowing Clients to Execute SQL in the Backend.

of allowing SQL commands from client apps, but not arbitrary ones. -- Rory Campbell-Lange -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] ident authentication problems postgresql 9.2

I have just done an update on my Debian servers running postgresql 9.2 (Postgres is from 9.2.4-2.pgdg70+1 from apt.postgresql.org) and suddenly can't login to postgresql as postgres with the normal peer/ident authentication over a local Unix socket. I've worked around it (I'm using md5 for the

Re: [GENERAL] ident authentication problems postgresql 9.2

On 16/10/13, Adrian Klaver (adrian.kla...@gmail.com) wrote: On 10/16/2013 06:56 AM, Rory Campbell-Lange wrote: I have just done an update on my Debian servers running postgresql 9.2 (Postgres is from 9.2.4-2.pgdg70+1 from apt.postgresql.org) and suddenly can't login to postgresql as postgres

Re: [GENERAL] ident authentication problems postgresql 9.2

On 16/10/13, Tom Lane (t...@sss.pgh.pa.us) wrote: Rory Campbell-Lange r...@campbell-lange.net writes: 2013-10-16 16:21:16 BST template1 LOG: local user with ID abc does not exist 2013-10-16 16:21:16 BST template1 FATAL: Peer authentication failed for user postgres However

[GENERAL] array_agg and partition sorts

,Brussels,Minsk,Bridgetown} agg3 | {Bridgetown} row_number | 1 I don't understand why agg3 is missing values. -- Rory Campbell-Lange r...@campbell-lange.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] array_agg and partition sorts

On 26/06/13, Rory Campbell-Lange (r...@campbell-lange.net) wrote: I'm on Postgres 9.1 and I've come across an issue which shows I don't understand partition sorting: Returns: -[ RECORD 1 ]- agg1

Re: [GENERAL] strategies for segregating client data when using PostgreSQL in a web app

On 03/08/12, Menelaos PerdikeasSemantix (mperdikeas.seman...@gmail.com) wrote: I would like to know what are the best practices / common patterns (or pointers to such) for using PostgreSQL in the context of a big web application with substantial data per user. ... [1] use just one database and

[GENERAL] Server choice for small workload : raptors or SSD?

710 Solid State SATA 270MBs read, 170MBs write in RAID 1 ** Both servers cost about the same. ** The 710 SSDs use MLC NAND flash. Review here: http://www.tomshardware.com/reviews/ssd-710-enterprise-x25-e,3038.html Regards Rory -- Rory Campbell-Lange r...@campbell-lange.net

Re: [GENERAL] Return unknown resultset from a function

)')::emp; $$ LANGUAGE SQL; Note that there are two ways of calling such a function. You probably want the SELECT * form. Rory -- Rory Campbell-Lange r...@campbell-lange.net Campbell-Lange Workshop www.campbell-lange.net 0207 6311 555 3 Tottenham Street London W1T 2AF Registered in England

Re: [GENERAL] Return unknown resultset from a function

On 04/03/12, Rory Campbell-Lange (r...@campbell-lange.net) wrote: On 04/03/12, Jan Meyland Andersen (j...@agile.dk) wrote: My main problem is that I do not know how many columns or the data type of the columns before runtime. It this possible at all? There is a section

[GENERAL] Strange primary key error on insertion

I have a strange issue (postgres 8.4) trying to insert old rows back into the s_tbls table. A check on the primary key (n_id) between s_tbl_import and s_tbls yields no matches, yet the index on s_tbls.n_id yields an error when attempting to insert: = select n_id from s_tbl_import where n_id IN (

Re: [GENERAL] Strange primary key error on insertion

On 06/10/11, Filip Rembiałkowski (plk.zu...@gmail.com) wrote: 2011/10/6 Rory Campbell-Lange r...@campbell-lange.net = insert into s_tbls (select * from s_tbl_import); ERROR: duplicate key value violates unique constraint s_tbls_pkey Looks like you had duplicates in s_tbl_import

Re: [GENERAL] UPDATE using query; per-row function calling problem

On 02/09/11, David Johnston (pol...@yahoo.com) wrote: In my -1 example, am I right in assuming that I created a correlated subquery rather than an correlated one? I'm confused about the difference. Correlated: has a where clause that references the outer query Un-correlated: not

[GENERAL] Query runs in 335ms; function in 100,239ms : date problem?

I have a function wrapping a (fairly complex) query. The query itself runs in about a 1/3rd of a second. When running the query as a 'RETURN QUERY' function on Postgres 8.4, the function runs in over 100 seconds, about 300 times slower. The function takes 3 input parameters: 2 dates and a

Re: [GENERAL] Query runs in 335ms; function in 100,239ms : date problem?

On 05/09/11, Tomas Vondra (t...@fuzzy.cz) wrote: On 5 Zá??í 2011, 23:07, Rory Campbell-Lange wrote: ... The query itself runs in about a 1/3rd of a second. When running the query as a 'RETURN QUERY' function on Postgres 8.4, the function runs in over 100 seconds, about 300 times slower

Re: [GENERAL] Query runs in 335ms; function in 100,239ms : date problem?

On 05/09/11, Rory Campbell-Lange (r...@campbell-lange.net) wrote: On 05/09/11, Tomas Vondra (t...@fuzzy.cz) wrote: On 5 Zá??í 2011, 23:07, Rory Campbell-Lange wrote: ... The query itself runs in about a 1/3rd of a second. When running the query as a 'RETURN QUERY' function on Postgres

Re: [GENERAL] UPDATE using query; per-row function calling problem

On 02/09/11, Tom Lane (t...@sss.pgh.pa.us) wrote: Rory Campbell-Lange r...@campbell-lange.net writes: I'm doing an UPDATE something like this: UPDATE slots SET a = 'a' ,b = (SELECT uuid_generate_v1()) WHERE c = TRUE; Each updated row

[GENERAL] UPDATE using query; per-row function calling problem

I'm doing an UPDATE something like this: UPDATE slots SET a = 'a' ,b = (SELECT uuid_generate_v1()) WHERE c = TRUE; Each updated row in slots is getting the same value for b. Is there a way of getting a per-row value from uuid_generate_v1() without

Re: [GENERAL] Problem with selecting arrays in set-returning plpgsql function

On 02/08/11, Rory Campbell-Lange (r...@campbell-lange.net) wrote: I am trying to select arrays in a set-returning function, but receiving the error: array value must start with { or dimension information. This issue appears to relate to locationnodes.rs_people_c sometimes having an empty

[GENERAL] Problem with selecting arrays in set-returning plpgsql function

I am trying to select arrays in a set-returning function, but receiving the error: array value must start with { or dimension information. This issue appears to relate to locationnodes.rs_people_c sometimes having an empty array. The return type into which locationnodes.rs_people_c is returned is

Re: [GENERAL] How to import *.sql file to postgresql database

. -- Rory Campbell-Lange r...@campbell-lange.net -- 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 know about PgMQ?

OK -- I've spotted another project, AMQP for PostgreSQL. http://lethargy.org/~jesus/writes/amqp-for-postgresql Which looks pretty good. Rory On 11/06/10, Rory Campbell-Lange (r...@campbell-lange.net) wrote: I was intrigued to see Chris Bohn's page about PgMQ (Embedding messaging in PostgreSQL

[GENERAL] Anyone know about PgMQ?

to find out more about his project, but haven't received a response. Does any one have any details of this project? I am very interested in the possibility of linking Postgres events such as triggers to RabbitMQ messaging queues. Rory -- Rory Campbell-Lange r...@campbell-lange.net -- Sent via pgsql

Re: [GENERAL] Extracting SQL from logs in a usable format

by setting the postgresql.conf parameter log_min_duration_statement to 0. You can then easily parse out the SQL statements from the log file. I have done this before quite successfully when wishing to replay a long set of SQL statements to test un upgrade of a Postgresql server. Rory -- Rory Campbell

[GENERAL] Debugging pgsql function date error

I have a long plpgsql function which takes a date as one of its arguments but I am receiving an error for another date! I'm using 8.3 on Debian. CREATE OR REPLACE FUNCTION fn_alert01_maker(integer, integer, integer, integer, date, integer) RETURNS SETOF alert_info AS $$ DECLARE userid

Re: [GENERAL] Update table with random values from another table

On 12/02/09, Rory Campbell-Lange (r...@campbell-lange.net) wrote: I have a test system for which I need to replace actual user's data (in 'users') with anonymised data from another table ('testnames') on postgres 8.3. The tricky aspect is that each row from testnames must be randomised

[GENERAL] Update table with random values from another table

I have a test system for which I need to replace actual user's data (in 'users') with anonymised data from another table ('testnames') on postgres 8.3. The tricky aspect is that each row from testnames must be randomised to avoid each row in users being updated with the same value. I've been

Re: [GENERAL] Update table with random values from another table

Hi Grzegorz Many thanks for your reply. On 12/02/09, Grzegorz Ja??kiewicz (gryz...@gmail.com) wrote: actually forget about that generate_series() in sub queries, I just realized that it won't do. I don't have too much time to analyse and find solution, but essentially you need to do it like

Re: [GENERAL] Update table with random values from another table

On 12/02/09, Tom Lane (t...@sss.pgh.pa.us) wrote: =?UTF-8?Q?Grzegorz_Ja=C5=9Bkiewicz?= gryz...@gmail.com writes: On Thu, Feb 12, 2009 at 1:10 PM, Rory Campbell-Lange r...@campbell-lange.net wrote: UPDATE users SET t_firstname = (select firstname from testnames order by random() limit

Re: [GENERAL] row constructors

On 12/02/09, Merlin Moncure (mmonc...@gmail.com) wrote: On Thu, Feb 12, 2009 at 5:03 AM, Sim Zacks s...@compulab.co.il wrote: Never mind. I found an old post. I just needed to do: insert into a1 select (f2).* from a2; I didn't find it the first time I searched because I was looking for

Re: [GENERAL] Update table with random values from another table

On 12/02/09, Rory Campbell-Lange (r...@campbell-lange.net) wrote: I realise that for every row in my users table (which has a unique integer field) I can update it if I construct a matching id field against a random row from the testnames table. I can make my join table pretty well by using

Re: [GENERAL] Using a 250GB RAID10 server for postgres

In the absence of replies (and sorry to bombard the list), I should clarify my question: Is it OK to use logical volume management to run an xfs partition hosting postgres data? (The server specs are below.) Thanks for any replies. Rory On 05/12/05, Rory Campbell-Lange ([EMAIL

[GENERAL] Using a 250GB RAID10 server for postgres

[Didn't get any replies on the Perform list -- hoping someone can help me here] Hi. We have a server provided for a test of a web application with the following specifications: 1 Dual core 1.8GHz Opteron chip 6 GB RAM approx 250GB of RAID10 storage (LSI card + BBU, 4 x 15000 RPM,16MB

[GENERAL] Select from function for part of column results

function_message_context(inbox.rowid), inbox; ERROR: function expression in FROM may not refer to other relations of same query level Is there a way I can get the function to provide some columns in the query? Thanks Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net

Re: [GENERAL] Select from function for part of column results

it with a function returning more than one column. st4= select *, fn_message_context(n_id) from inbox; ERROR: cannot display a value of type record Rory - Original Message - From: Rory Campbell-Lange [EMAIL PROTECTED] To: Postgresql General List pgsql-general@postgresql.org Sent: Thursday

[GENERAL] Join on virtual table

BY dayom.d LOOP RETURN NEXT resulter; END LOOP; RETURN; END;' LANGUAGE plpgsql; -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 6

[GENERAL] Schema and Group permissions question

User1 loads tables into Test, User2 cannot work with them unless User1 explicity sets: GRANT ALL ON tablename|indexname TO User2 Is there anyway of setting the equivalent of a directory g+sw permissions on Test? Thanks for any help Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell

Re: [GENERAL] [OT] Dilemma about OS - Postgres interaction

On 18/06/04, Harald Fuchs ([EMAIL PROTECTED]) wrote: In article [EMAIL PROTECTED], Rory Campbell-Lange [EMAIL PROTECTED] writes: I should have mentioned that we need the messages sent very soon after they have landed in the 'inbox'; otherwise cron would definitely be the way to go

Re: [GENERAL] Naive schema questions

Fabulous stuff! I am so delighted I chose Postgresql a couple of year ago. Thank you for the valuable insights. A comment or two below: On 27/05/04, Peter Eisentraut ([EMAIL PROTECTED]) wrote: Am Donnerstag, 27. Mai 2004 13:15 schrieb Rory Campbell-Lange: I imagined schemas might allow me

Re: [GENERAL] Very slow query

Sorry for replying to my own post, but I'm anxious for an answer. Should I provide other information? Thanks Rory On 10/05/04, Rory Campbell-Lange ([EMAIL PROTECTED]) wrote: The following query on some small datasets takes over a second to run. I'd be grateful for some help in understanding

Re: [GENERAL] PostgreSQL Advocacy, Thoughts and Comments

offsetter , LOOP RETURN NEXT resulter; END LOOP; RETURN; END;' LANGUAGE plpgsql; -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast

[GENERAL] Outer Join help please

27 2 0 \. SELECT pg_catalog.setval ('dlr_id_seq', 11, true); -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your

Bug in comment parsing? [was: Re: [GENERAL] Comments in .sql files]

. What is the proper way to put comments in schema files? The SQL-standard comment syntaxes are -- comment to end of line /* C-like comment, possibly multiple lines */ -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end

[GENERAL] using EXISTS instead of IN: how?

~* 'r' ); However the second example simply finds all records in people. Thanks for any help, Rory -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 9: the planner will ignore your desire

[GENERAL] Rollback for aborted function?

If a function generates an exception, do I need to rollback manually? -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster

[GENERAL] SELECT too complex?

= personid ) ORDER BY botimer DESC LIMIT locallimit OFFSET localoffset LOOP RETURN NEXT resulter; END LOOP; -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net

Re: [GENERAL] [pgsql-advocacy] interesting PHP/MySQL thread

([EMAIL PROTECTED]) wrote: We need to use this opportunity to encourage PHP folks to switch to PostgreSQL. -- Rory Campbell-Lange [EMAIL PROTECTED] www.campbell-lange.net ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL