Re: [GENERAL] Query Using Massive Temp Space

Hi, there are many Hash and Merge joins that may generate a lot of temp space, it could also be a problem of forgotten column in the join. Could you also provide indexes definitions (pk, uk and others) with the EXPLAIN (ANALYZE BUFFERS) (you can limit the scope of the query to help it to finish

Re: [GENERAL] Query Using Massive Temp Space

Hello, I have one question… Why you using so huge amount of grouping columns? Is there some reason for it? It is not definitelly fast method. I would prefer firstly do named query grouped by ids (account_id, candidate_id, parent_id) and then left join table candidates (to get rest of requested

Re: [GENERAL] unnest-like pg_stats.most_common_values and pg_stats.most_common_freqs

On Fri, Nov 17, 2017 at 9:41 PM, Justin Pryzby wrote: > I think you want something like this ? > > postgres=# SELECT schemaname, tablename, attname, > unnest(histogram_bounds::text::text[]), histogram_bounds FROM pg_stats LIMIT > 9; > pg_catalog | pg_pltemplate | tmplname

Re: [GENERAL] Query Using Massive Temp Space

Cory Tucker wrote: > I have a query that is using a tremendous amount of temp disk space given the > overall size of the dataset. > I'd love for someone to try to explain what PG is doing and why its using so > much space for the query. It could be a sort or a hash operation. Do determine what

Re: [GENERAL] How to store multiple rows in array .

brahmesr writes: > SELECT ARRAY (SELECT ROW (COL1,COL2, COUNT(*) *AS txn_cnt* ):: > ap.validate_crtr_line_items$inv_lines_rt FROM Distinct_Records GROUP BY > COL1, COL2 HAVING COUNT(*) > 1) INTO L_INV_LINES; > ERROR: syntax error at or near "AS" > LINE 73: COL1,COL2,

Re: [GENERAL] How to store multiple rows in array .

I already defined the composite type as "validate_crtr_line_items$inv_lines_rt" with the selected columns(COL1,COL2,COl3) DeCLARE Block : INV_LINES_T validate_crtr_line_items$inv_lines_rt ARRAY; L_INV_LINES INV_LINES_T%TYPE; L_INV_LINES$temporary_record

Re: [GENERAL] parallel query in 9.6.6 + oracle_fdw -> ERROR: invalid cache ID: 41

Hi, So I'd call this an oracle_fdw bug. It needs to postpone what it's doing here to the first normal FDW function call in a session. Thanks a lot for looking so quickly into this! I've opened an issue with oracle_fdw: https://github.com/laurenz/oracle_fdw/issues/215 Thanks, Chris. --

Re: [GENERAL] parallel query in 9.6.6 + oracle_fdw -> ERROR: invalid cache ID: 41

Chris Mair writes: > Whenever a session has performed a query on a foreign table, any subsequent > query on a local table big enough to use the parallel query feature exits with > an error: > ERROR: invalid cache ID: 41 > CONTEXT: parallel worker Hm, syscache 41 is

Re: [GENERAL] How to store multiple rows in array .

Pavel Stehule writes: > 2017-11-19 18:57 GMT+01:00 Brahmam Eswar : >> How to collect multiple columns into array which is composite data type of >> all select colums > SELECT ARRAY(SELECT ROW(col1, col2, ...) INTO You probably need an explicit

Re: [GENERAL] How to store multiple rows in array .

2017-11-19 18:57 GMT+01:00 Brahmam Eswar : > Hi , > > System is migrating from Oracle to Postgre SQL. > Oracle is providing BULK COLLECT INTO function to collect the multiple > records from table . > > Select COL1,COL2 ,COL3 BULK COLLECT INTO LINES from Distinct_Records.

Re: [GENERAL] mild modification to pg_dump

On Fri, Nov 17, 2017 at 3:58 PM, marcelo wrote: > Again: knowing of .pgpass (thank you Scott) this is what I will do. > > Just in case you might not know. The perms of the .pgpass file need to not have group or all write access. For instance: chmod 0600 .pgpass -m

Re: [GENERAL] mild modification to pg_dump

On 2017-11-17 18:56:45 -0300, marcelo wrote: > Truly, I'm catched in a very big app, so I have no time to read all > the docs. People on this list also have jobs. - Andres -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] mild modification to pg_dump

Again: knowing of .pgpass (thank you Scott) this is what I will do. On 17/11/17 17:49, Ron Johnson wrote: On 11/17/2017 02:23 PM, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need

Re: [GENERAL] mild modification to pg_dump

Thank you, Scott. That's happening me because incomplete docs reading. Truly, I'm catched in a very big app, so I have no time to read all the docs. On 17/11/17 18:31, Scott Mead wrote: On Fri, Nov 17, 2017 at 4:06 PM, marcelo >

Re: [GENERAL] mild modification to pg_dump

On Fri, Nov 17, 2017 at 4:06 PM, marcelo wrote: > I need to "emulate" the pg_dump code because the password prompt. Years > ago I write a program (for the QnX environment) that catched some prompt > and emulates the standard input. I don't like to do that again.

Re: [GENERAL] mild modification to pg_dump

What about the pgpass file? https://www.postgresql.org/docs/9.2/static/libpq-pgpass.html On 11/17/2017 03:06 PM, marcelo wrote: I need to "emulate" the pg_dump code because the password prompt. Years ago I write a program (for the QnX environment) that catched some prompt and emulates the

Re: [GENERAL] mild modification to pg_dump

I will give expect a try. But the source code embedded in my daemon. On 17/11/17 17:49, Ron Johnson wrote: On 11/17/2017 02:23 PM, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I

Re: [GENERAL] mild modification to pg_dump

pg_dump is a libpq client, and thus will read the environment for a variable with the password. no need to emulte any command prompt tty operations. On Fri, Nov 17, 2017 at 4:06 PM, marcelo wrote: > I need to "emulate" the pg_dump code because the password prompt.

Re: [GENERAL] mild modification to pg_dump

I need to "emulate" the pg_dump code because the password prompt. Years ago I write a program (for the QnX environment) that catched some prompt and emulates the standard input. I don't like to do that again. On 17/11/17 17:23, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote:

Re: [GENERAL] mild modification to pg_dump

On 11/17/2017 02:23 PM, John R Pierce wrote: On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the

Re: [GENERAL] unnest-like pg_stats.most_common_values and pg_stats.most_common_freqs

On Fri, Nov 17, 2017 at 09:32:23PM +0100, Luca Ferrari wrote: > Hi all, > maybe this is trivial, but I need an hint on a way to see a table form > of the MCVs and MCFs out of pg_stats with a query. Is it possible to > get a set of rows each with a most common value on one column and the >

Re: [GENERAL] mild modification to pg_dump

On 11/17/2017 12:19 PM, marcelo wrote: Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the database to dump, the user under which do

Re: [GENERAL] mild modification to pg_dump

Sorry, I was not exact. I don't need nor like to change pg_dump. Rather, based on pg_dump code, I need to develop a daemon which can receive a TCP message (from a privileged app) containing some elements: the database to dump, the user under which do that, and his password. (My apps are using

Re: [GENERAL] mild modification to pg_dump

On Fri, Nov 17, 2017 at 7:51 AM, marcelo wrote: > I would need to do a mild change to pg_dump, working against a 9.4 server > on linux. > Which source tree do I need? Have gcc 4.9.2 in my Lubuntu installation. > TIA What exactly do you need to change? Most likely,

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

Andres Freund writes: > It doesn't seem impossible to get into a situation where syslogger is > the source of the OOM. Just enabling a lot of logging in a workload with > many large query strings might do it. So making it less likely to be > killed might make the problem

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On November 16, 2017 7:06:23 PM PST, Tom Lane wrote: >Andres Freund writes: >> On 2017-11-16 21:39:49 -0500, Tom Lane wrote: >>> What might be worth thinking about is allowing the syslogger process >to >>> inherit the postmaster's OOM-kill-proofness

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

Andres Freund writes: > On 2017-11-16 21:39:49 -0500, Tom Lane wrote: >> What might be worth thinking about is allowing the syslogger process to >> inherit the postmaster's OOM-kill-proofness setting, instead of dropping >> down to the same vulnerability as the postmaster's

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On 2017-11-16 21:39:49 -0500, Tom Lane wrote: > > We could work around a situation like that if we made postmaster use a > > *different* pipe as stderr than the one we're handing to normal > > backends. If postmaster created a new pipe and closed the read end > > whenever forking a syslogger, we

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

Andres Freund writes: > On 2017-11-06 15:35:03 -0500, Tom Lane wrote: >> David Pacheco writes: >>> I ran into what appears to be a deadlock in the logging subsystem. It >>> looks like what happened was that the syslogger process exited because it >>> ran out

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On Fri, Nov 17, 2017 at 11:14 AM, Andres Freund wrote: > On 2017-11-17 11:09:56 +0900, Michael Paquier wrote: >> when redirection_done is switched to true because the first process >> generating a message to the syslogger pipe needs to open it first if >> not done yet? > > I

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On 2017-11-17 11:09:56 +0900, Michael Paquier wrote: > On Fri, Nov 17, 2017 at 10:50 AM, Andres Freund wrote: > > On 2017-11-06 15:35:03 -0500, Tom Lane wrote: > >> David Pacheco writes: > >> > I ran into what appears to be a deadlock in the logging

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On Fri, Nov 17, 2017 at 10:50 AM, Andres Freund wrote: > On 2017-11-06 15:35:03 -0500, Tom Lane wrote: >> David Pacheco writes: >> > I ran into what appears to be a deadlock in the logging subsystem. It >> > looks like what happened was that the syslogger

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On 2017-11-06 15:35:03 -0500, Tom Lane wrote: > David Pacheco writes: > > I ran into what appears to be a deadlock in the logging subsystem. It > > looks like what happened was that the syslogger process exited because it > > ran out of memory. But before the postmaster got a

Re: [GENERAL] postmaster deadlock while logging after syslogger exited

On Mon, Nov 6, 2017 at 12:35 PM, Tom Lane wrote: > David Pacheco writes: > > I ran into what appears to be a deadlock in the logging subsystem. It > > looks like what happened was that the syslogger process exited because it > > ran out of memory. But

Re: [GENERAL] pg_restore load data

On 11/16/2017 03:13 PM, bricklen wrote: On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson > wrote: v9.2.7  (Yes, I know, it's old.  Nothing I can do about it.) During a "whole database" restore using pg_restore of a custom dump,

Re: [GENERAL] pg_restore load data

The command is COPY, not load. Sent via the Samsung Galaxy S® 6, an AT 4G LTE smartphone Original message From: Ron Johnson Date: 11/16/17 16:07 (GMT-05:00) To: pgsql-general@postgresql.org Subject: [GENERAL] pg_restore load data Hi, v9.2.7  (Yes, I

Re: [GENERAL] pg_restore load data

On Thu, Nov 16, 2017 at 1:07 PM, Ron Johnson wrote: > v9.2.7 (Yes, I know, it's old. Nothing I can do about it.) > > During a "whole database" restore using pg_restore of a custom dump, when > is the data actually loaded? I've looked in the list output and don't see >

Re: [GENERAL] expanded auto and header linestyle double

2017-11-16 21:16 GMT+01:00 Matt Zagrabelny : > Thanks for the reply, Pavel! > > On Thu, Nov 16, 2017 at 1:01 AM, Pavel Stehule > wrote: > >> Hi >> >> 2017-11-15 23:37 GMT+01:00 Matt Zagrabelny : >> >>> Greetings, >>> >>> Using PG

Re: [GENERAL] expanded auto and header linestyle double

Thanks for the reply, Pavel! On Thu, Nov 16, 2017 at 1:01 AM, Pavel Stehule wrote: > Hi > > 2017-11-15 23:37 GMT+01:00 Matt Zagrabelny : > >> Greetings, >> >> Using PG 10.1. >> >> In my .psqlrc I have: >> >> \x auto >> \pset linestyle 'unicode' >>

Re: [GENERAL] Build in function to verify email addresses

On Thu, Nov 16, 2017 at 1:56 AM, Nick Dro wrote: > I beleieve that every information system has the needs to send emails. > Currently PostgreSQL doesn't have a function which gets TEXT and return > true if it's valid email address (x...@yyy.com / .co.ZZ) > Do you believe

Re: [GENERAL] Build in function to verify email addresses

På torsdag 16. november 2017 kl. 09:05:00, skrev Pavel Stehule < pavel.steh...@gmail.com >: Hi   2017-11-16 8:56 GMT+01:00 Nick Dro >: I beleieve that every information system has the needs to send emails.

Re: [GENERAL] Build in function to verify email addresses

- Original Message - From: "Nick Dro" To: pgsql-general@postgresql.org Sent: Thursday, November 16, 2017 2:56:42 AM Subject: [GENERAL] Build in function to verify email addresses I beleieve that every information system has the needs to send emails.

Re: [GENERAL] Build in function to verify email addresses

I'm pretty sure this isn't as simple as you think it is, I'd suggest having a good read of: https://stackoverflow.com/a/201378/216229 Chris On 16/11/2017 07:56, Nick Dro wrote: I beleieve that every information system has the needs to send emails. Currently PostgreSQL doesn't have a

Re: [GENERAL] Build in function to verify email addresses

Hi 2017-11-16 8:56 GMT+01:00 Nick Dro : > I beleieve that every information system has the needs to send emails. > Currently PostgreSQL doesn't have a function which gets TEXT and return > true if it's valid email address (x...@yyy.com / .co.ZZ) > Do you believe such

Re: [GENERAL] expanded auto and header linestyle double

Hi 2017-11-15 23:37 GMT+01:00 Matt Zagrabelny : > Greetings, > > Using PG 10.1. > > In my .psqlrc I have: > > \x auto > \pset linestyle 'unicode' > \pset unicode_header_linestyle double > > and when the output is expanded, I do not see a double line for the first > record,

Re: [GENERAL] pg_basebackup running from a remote machine

On Tue, Nov 14, 2017 at 8:28 AM, Laurenz Albe wrote: > rakeshkumar464 wrote: > > If pg_basebackup is run from a remote machine with compress option > --gzip , > > compress level 9, > > will the compression occur prior to the data being sent on the network or > > after

Re: [GENERAL] Setting up replication slave on remote high latency host

Thank you for that. Back to the drawing board! On Wed, Nov 15, 2017 at 9:30 PM, John R Pierce wrote: > On 11/15/2017 6:02 PM, Rory Falloon wrote: > >> >> Right now I am trying to dump the database, gzip, move across, and import >> into the new slave (which is configured as

Re: [GENERAL] Setting up replication slave on remote high latency host

On 11/15/2017 6:02 PM, Rory Falloon wrote: Right now I am trying to dump the database, gzip, move across, and import into the new slave (which is configured as a master to perform the initial setup). Ideally I do this dump, move and import during a period of inactivity on the master so the

Re: [GENERAL] PGPool in Master-Master mode, is it possible?

I am not familiar with swarm cluster nor repmgr. So I wonder why you wouldn't be able to get to the goal you are trying to achieve without repmgr. Can you please elaborate more? Best regards, -- Tatsuo Ishii SRA OSS, Inc. Japan English: http://www.sraoss.co.jp/index_en.php

Re: [GENERAL] problem changing jsonb attribute to null value

> On 15 November 2017 at 22:54, RODRIGUEZ CORTES MARIO IGNACIO < ignacio.cor...@inegi.org.mx> wrote: > > I have a problem with a record in a jsonb type table, I'm trying to > change the value of an attribute to null but it leaves me all the > content in null and not just the value > > prueba=#

Re: [GENERAL] pg_basebackup running from a remote machine

Does pg_basebackup on a remote machine follow the standard libpq protocol. I am not able to force it to use ssl, despite having an entry in pg_hba.conf: hostnossl all all all reject >From the same remote machine, psql is forced to use ssl. Makes me wonder whether pg_basebackup has a different

Re: [GENERAL] Query on pg_settings view

Greetings, * Laurenz Albe (laurenz.a...@cybertec.at) wrote: > Ronen Nofar wrote: > > I have a weird case when running a query on the pg_settings view. > > I have two users, first one is the default user - postgres which is a > > superuser > > and another one is a role which i had created, i

Re: [GENERAL] Query on pg_settings view

Ronen Nofar wrote: > I have a weird case when running a query on the pg_settings view. > I have two users, first one is the default user - postgres which is a > superuser > and another one is a role which i had created, i called it test_role and it's > not a superuser. > When I run a select on

Re: [GENERAL] PGPool in Master-Master mode, is it possible?

Thank you Tatsuo for the Reply, I will post this in list you mentioned. By Master-Master, I meant two pgpool servers both active accepting connections from Application, If one Pgpool Server becomes unlavailable other still accepting connections. At an Ideal time, both Pgpool instances on each

Re: [GENERAL] archive_command not being executed

in the WAL files. From: Jeremy Schneider <schnei...@ardentperf.com> Sent: Monday, November 13, 2017 3:56 PM To: eric...@hotmail.com Cc: PostgreSQL General; Paul Jungwirth Subject: Re: [GENERAL] archive_command not being executed Hi Eric, Thanks for using PostgreSQL! On Fri, Nov 10, 2017 at 9

Re: [GENERAL] jsonb

On 11/14/2017 2:30 PM, hmidi slim wrote: I'm trying to use the function to_jsonb and create the name: to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}'); But after that I used Objection.js ORM to get data using the query: Product.query().where('id',1).then(prod =>

Re: [GENERAL] jsonb

On 11/14/2017 11:30 PM, hmidi slim wrote: > I'm trying to use the function to_jsonb and create the name: > to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}'); This query converts a string into a JSON object that consist of that string. I guess what you intend to accomplish is

Re: [GENERAL] jsonb

On Tue, Nov 14, 2017 at 4:30 PM, hmidi slim wrote: > I'm trying to use the function to_jsonb and create the name: > to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}'); > But after that I used Objection.js ORM to get data using the query: >

Re: [GENERAL] PGPool in Master-Master mode, is it possible?

> Hello There, > > I need to setup two PGPool Servers in Master-Master mode, First thing I > want to know, is it possible? > > I know we can setup 2 PGPool servers in master-slave mode using watchdog. > http://www.pgpool.net/pgpool-web/contrib_docs/watchdog_master_slave_3.3/en.html > > Could

Re: [GENERAL] jsonb

I'm trying to use the function to_jsonb and create the name: to_jsonb ('{"key1":" ' || 'text1' || '","key2":" ' || 'text2' || '"}'); But after that I used Objection.js ORM to get data using the query: Product.query().where('id',1).then(prod => {console.log(prod)}) I think that the problem maybe

Re: [GENERAL] jsonb

On Tue, Nov 14, 2017 at 3:32 PM, hmidi slim wrote: > I have a column name of type 'jsonb' on my table named product. The format > of the column: > name: {"key1": "text1", "key2": "text2"} > > When I make a query to fetch data from the table I got this format: > name:

Re: [GENERAL] missing public on schema public

Tom, all, * Tom Lane (t...@sss.pgh.pa.us) wrote: > =?iso-8859-1?Q?Bo_Thorbj=F8rn_Jensen?= writes: > > I have some additional info and a fix. > > Firstly steps to reproduce: > > Yeah, I can reproduce this. I suspect it got broken by Stephen's hacking > around with default

Re: [GENERAL] pg 10 crashes on int8_avg_combine

ok, I just avoided it using sum(cnt::int) since cnt is small. 2017-11-15 00:25, Tom Lane: > Dingyuan Wang writes: >> PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-8) >> 7.2.0, 64-bit >> >> (gdb) bt >> #0 int8_avg_combine (fcinfo=0x55bdb92472d8) at >>

Re: [GENERAL] pg_basebackup running from a remote machine

rakeshkumar464 wrote: > If pg_basebackup is run from a remote machine with compress option --gzip , > compress level 9, > will the compression occur prior to the data being sent on the network or > after it has been received > at the remote machine. That only means that the output TAR file will

Re: [GENERAL] pg 10 crashes on int8_avg_combine

Dingyuan Wang writes: > PostgreSQL 10.0 on x86_64-pc-linux-gnu, compiled by gcc (Debian 7.2.0-8) > 7.2.0, 64-bit > > (gdb) bt > #0 int8_avg_combine (fcinfo=0x55bdb92472d8) at > ./build/../src/backend/utils/adt/numeric.c:4285 I think this is the same issue being discussed at

Re: [GENERAL] missing public on schema public

=?iso-8859-1?Q?Bo_Thorbj=F8rn_Jensen?= writes: > I have some additional info and a fix. > Firstly steps to reproduce: Yeah, I can reproduce this. I suspect it got broken by Stephen's hacking around with default ACLs. A simple example is $ pg_dump -c -U postgres postgres |

Re: [GENERAL] Retrieve the server's time zone

Tom Lane schrieb am 14.11.2017 um 15:36: >> is there any way (short of writing a function in an untrusted PL) >> to determine the actual time zone (or time) of the server OS? > > AFAIK that would only be true if some part of your client stack > is issuing a SET TIMEZONE command. (libpq will do

Re: [GENERAL] Retrieve the server's time zone

Thomas Kellerer writes: > is there any way (short of writing a function in an untrusted PL) to > determine the actual time zone (or time) of the server OS? The default value of the timezone parameter is as close as you'll get in modern versions of PG. > "show timezone"

Re: [GENERAL] Because PostgreSQL is compiling in old versions of OS?

El 11/11/17 a las 0:48, DrakoRod escribió: Oh!! Jose Maria TJ wrote You're wrong, that are gcc versions, not OS versions. For example in my CentOS 6 Box cat /etc/redhat-release CentOS release 6.9 (Final) gcc -v [...trimmed...] gcc versión 4.4.7 20120313 (Red Hat 4.4.7-18) (GCC) You're

Re: [GENERAL] missing public on schema public

I have some additional info and a fix. Firstly steps to reproduce: 1. create database: CREATE DATABASE test WITH ENCODING='UTF8' OWNER=postgres CONNECTION LIMIT=-1; -- here public has access to public 2. dump: pg_dump -f testfile.dump -F c -h localhost -U postgres test 3. restore: pg_restore

Re: [GENERAL] archive_command not being executed

_ From: Michael Paquier <michael.paqu...@gmail.com> Sent: Monday, November 13, 2017 6:01 PM To: Jeremy Schneider Cc: eric...@hotmail.com; PostgreSQL General; Paul Jungwirth Subject: Re: [GENERAL] archive_command not being executed On Tue, Nov 14, 2017 at 8:56 AM, Jeremy Schneider &

Re: [GENERAL] PostgreSQL walsender process doesn't exist after "pg_ctl stop -m fast"

On Tue, Nov 14, 2017 at 12:05 AM, Tom Lane wrote: > y39chen writes: >> We encounter one problem that PostgreSQL walsender process doesn't exist >> after "pg_ctl stop -m fast". >> Uses PostgreSQL 9.6.2 > > There was a fix in 9.6.4 that's at least

Re: [GENERAL] archive_command not being executed

On Tue, Nov 14, 2017 at 8:56 AM, Jeremy Schneider wrote: > From my reading of the docs and commit logs, standby databases > couldn't archive their WALs until 9.5. pg_receivexlog is available in 9.3. You could leverage your archives with it easily, by for example

Re: [GENERAL] archive_command not being executed

Hi Eric, Thanks for using PostgreSQL! On Fri, Nov 10, 2017 at 9:26 AM, Paul Jungwirth wrote: > Oh this has happened to me before. :-) On SB1 you need to set > archive_mode to always (not on). Otherwise it is ignored when running as a > standby. It looks to me

Re: [GENERAL] PG-10 + ICU and abbreviated keys

På tirsdag 14. november 2017 kl. 00:44:11, skrev Peter Geoghegan >: On Mon, Nov 13, 2017 at 2:05 PM, Andreas Joseph Krogh wrote: > When sorting on text, we're usually doing so using an multi-column index, like for instance "CREATE INDEX

Re: [GENERAL] PG-10 + ICU and abbreviated keys

On Mon, Nov 13, 2017 at 2:05 PM, Andreas Joseph Krogh wrote: > When sorting on text, we're usually doing so using an multi-column index, > like for instance "CREATE INDEX xxx ON my_table (lower(name) ASC, created > ASC)". Will abbreviated keys help here? Yes, they'll help

Re: [GENERAL] PG-10 + ICU and abbreviated keys

På mandag 13. november 2017 kl. 22:28:40, skrev Peter Geoghegan >: On Mon, Nov 13, 2017 at 12:48 PM, Andreas Joseph Krogh wrote: > Thanks. As the person that worked on abbreviated keys, I'd like to hear about how you get with this. How

Re: [GENERAL] PG-10 + ICU and abbreviated keys

On Mon, Nov 13, 2017 at 12:48 PM, Andreas Joseph Krogh wrote: > Thanks. As the person that worked on abbreviated keys, I'd like to hear about how you get with this. How much faster is it for you? I don't usually get to hear about this, because most users don't notice that

Re: [GENERAL] PG-10 + ICU and abbreviated keys

På mandag 13. november 2017 kl. 21:46:08, skrev Peter Geoghegan >: On Mon, Nov 13, 2017 at 12:40 PM, Peter Geoghegan wrote: >> Do I have to explicitly specify collation when using ORDER by on that column for index and abbreviated keys to be

Re: [GENERAL] PG-10 + ICU and abbreviated keys

På mandag 13. november 2017 kl. 21:40:47, skrev Peter Geoghegan >: On Mon, Nov 13, 2017 at 11:48 AM, Andreas Joseph Krogh wrote: > Ok, so I have to explicitly specify like this: > > create table test(id serial primary key, name varchar

Re: [GENERAL] PG-10 + ICU and abbreviated keys

On Mon, Nov 13, 2017 at 12:40 PM, Peter Geoghegan wrote: >> Do I have to explicitly specify collation when using ORDER by on that column >> for index and abbreviated keys to be used? > > Only if you didn't define the column with a per-column collation initially. BTW, if you

Re: [GENERAL] PG-10 + ICU and abbreviated keys

On Mon, Nov 13, 2017 at 11:48 AM, Andreas Joseph Krogh wrote: > Ok, so I have to explicitly specify like this: > > create table test(id serial primary key, name varchar collate "nb_NO" not > null); That doesn't look like an ICU locale. You may mean "nb-NO-x-icu". But

Re: [GENERAL] PG-10 + ICU and abbreviated keys

På mandag 13. november 2017 kl. 19:07:53, skrev Peter Geoghegan >: On Mon, Nov 13, 2017 at 12:27 AM, Andreas Joseph Krogh wrote: > In PG-10, with ICU enabled, is abbreviated keys now enabled? Yes. ICU will use abbreviated keys on every

Re: [GENERAL] PG-10 + ICU and abbreviated keys

On Mon, Nov 13, 2017 at 12:27 AM, Andreas Joseph Krogh wrote: > In PG-10, with ICU enabled, is abbreviated keys now enabled? Yes. ICU will use abbreviated keys on every platform, including Windows. > If so, using locale=nb_NO.UTF-8, do I have to use a ICU-specific locale to

Re: [GENERAL] PostgreSQL walsender process doesn't exist after "pg_ctl stop -m fast"

y39chen writes: > We encounter one problem that PostgreSQL walsender process doesn't exist > after "pg_ctl stop -m fast". > Uses PostgreSQL 9.6.2 There was a fix in 9.6.4 that's at least related to this problem. It would be interesting to see if you can still reproduce

Re: [GENERAL] sync the data's from catalog table

Hi, Whenever the postgres user is trying to modify the user account's password column in pg_authid table, we need to maintain a trigger in catalog table (pg_authid) where it pop up the the "password column has been restricted and it should not be modified". Is there any possible for the above

Re: [GENERAL] PG-10 + ICU and abbreviated keys

On 11/13/2017 09:27 AM, Andreas Joseph Krogh wrote: >   > In PG-10, with ICU enabled, is abbreviated keys now enabled? >   Hello, I think yes : src/backend/utils/adt/varlena.c 1876 /* 1877 * Unfortunately, it seems that abbreviation for non-C collations is 1878 * broken on many

Re: [GENERAL] pg on Debian servers

On 12/11/17 19:15, Karsten Hilbert wrote: On Sat, Nov 11, 2017 at 01:03:18PM +, Mark Morgan Lloyd wrote: Several legacy programs written in Delphi ground to a halt this morning, which turned out to be because a Debian system had updated its copy of PostgreSQL and restarted the server,

Re: [GENERAL] sync the data's from catalog table

Dinesh kumar wrote: > How can I sync the data's from pg_authid to manually created table (user > table) whenever the update or insert happens on pg_authid table. You cannot do this, because you cannot define triggers on catalog tables. The question is: Why do you want to do this? What are you

Re: [GENERAL] Migrating plattaform

On 11/8/2017 11:38 AM, Valdir Kageyama wrote: I need migrated the postgres from Linux on IBM Power to Oracle Linux on SPARC. My doubt is possible copy the datafiles to new enviorement ? or I need using  other means of copying the data. For exemples: pg_dump/pg_restore. pretty sure you

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

Andres Freund writes: > we could really do better than just wonder whether our signal to > shutdown was received or not. There probably should be a quite short > timeout for the server to change status, and then a much longer one for > that shutdown to finish. While I don't

Re: [GENERAL] Multiple unnests in query

Aron Widforss writes: > Is this first query expected behavior? If so, what is the rationale? The short answer is "because it's always worked that way". You might find the last half of section 37.4.8 illuminating:

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

On 2017-11-12 14:26:42 -0500, Tom Lane wrote: > Christoph Berg writes: > > The default systemd timeout seems to be 90s. I have already changed > > the systemd timeout to infinity (start) and 1h (stop), so only the > > default pg_ctl timeout remains (60s), which I'd rather not

Re: [GENERAL] pg on Debian servers

On Sat, 2017-11-11 at 14:30 +0100, Magnus Hagander wrote: > > > > The init.d script is not used with systemd. > > > Hello Magnus, Many months ago on a bog standard Debian set-up did a re-boot and ended up with postmasters running for 9.2, 9.4, 9.5 and 9.6 all started one after the other.

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

Christoph Berg writes: > The default systemd timeout seems to be 90s. I have already changed > the systemd timeout to infinity (start) and 1h (stop), so only the > default pg_ctl timeout remains (60s), which I'd rather not override > unilaterally. > That said, isn't 60s way too

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

Re: Tom Lane 2017-11-12 <20802.1510513...@sss.pgh.pa.us> > Agreed, but I think Peter has a point: why is there a timeout at all, > let alone one as short as 30 seconds? Since systemd doesn't serialize > service starts unnecessarily, there seems little value in giving up > quickly. And we know

Re: [GENERAL] pg on Debian servers

On Sat, Nov 11, 2017 at 01:03:18PM +, Mark Morgan Lloyd wrote: > Several legacy programs written in Delphi ground to a halt this morning, > which turned out to be because a Debian system had updated its copy of > PostgreSQL and restarted the server, which broke any live connections. > > At

Re: [GENERAL] Postgres 10.1 fails to start: server did not start in time

Christoph Berg writes: > Re: Peter J. Holzer 2017-11-12 <20171112173559.m6chmbyf4vz6f...@hjp.at> >> Wouldn't it be better to remove the timeout? > If you don't want to block, don't depend on the database service. That > question is independent from the timeout. Agreed, but I

Re: [GENERAL] Combine multiple text search configuration

Hi, On 2017-11-06 09:17, hmidi slim wrote: > Hi, > I want to know if I can combine multiple text search configurations when > I tried to use FTS. > Is there any options like this: > *to_tsvector(['english', 'french'], document)* > * > * > Trying to create a new text configuration: > *Create text

Re: [GENERAL] Combine multiple text search configuration

On 2017-11-07 08:27, hmidi slim wrote: > Hi,  > Thank for your proposition but when to use this query :  > (to_tsvector('english', document) || to_tsvector('french', document)) @@ > (to_tsquery('english', query) || to_tsquery('french', query)) > I think that the performance decrease and not a good

  1   2   3   4   5   6   7   8   9   10   >