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

Thank you for the explanation. We shall try the latest PostgreSQL 9.6.6 version. -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.o

Re: [GENERAL] Re: Restoring tables with circular references dumped to separate files

On Sat, Oct 21, 2017 at 10:48 PM, doganmeh wrote: ... > On another note, I used to take full backups (entire database), however > switched to table by table scheme in order to make it more VCS friendly. > Namely, so I only check into github the dumps of the tables that are updated > only. > So, fr

Re: [GENERAL] Re: Restoring tables with circular references dumped to separate files

On Sat, Oct 21, 2017 at 4:48 PM, doganmeh wrote: > The list approach for partial restore is also useful, thank you. > > On another note, I used to take full backups (entire database), however > switched to table by table scheme in order to make it more VCS friendly. > Namely, so I only check into

[GENERAL] Re: Restoring tables with circular references dumped to separate files

The list approach for partial restore is also useful, thank you. On another note, I used to take full backups (entire database), however switched to table by table scheme in order to make it more VCS friendly. Namely, so I only check into github the dumps of the tables that are updated only. S

Re: [GENERAL] Re: Restoring tables with circular references dumped to separate files

On Sat, Oct 21, 2017 at 8:24 AM, doganmeh wrote: > Seems that would be easier and less error prone. Thanks, > > > > -- > Sent from: http://www.postgresql-archive.org/PostgreSQL-general- > f1843780.html > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes

[GENERAL] Re: Restoring tables with circular references dumped to separate files

Seems that would be easier and less error prone. Thanks, -- Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gener

[GENERAL] Re: "alter table...if exists... add bigserial "still adds extra sequence

> On 25 Sep 2017, at 09:51 , hvjunk wrote: > > Good day, > > See the sequence below, Postgresql 9.6.5 on Debian using the postgresql > repository. > > Question: Is this expected behaviour? I guess it might be, but the “bug” is that the excessive/unused sequence isn’t removed: test=# \d te

[GENERAL] Re: Dynamic use of RAISE with USING to generate and catch non-hardcoded custom exceptions

>This works for me: > >DO $$ >DECLARE > v_msg TEXT := 'SOMETHING IS WRONG'; > v_sqlstate TEXT := 'E0001'; >BEGIN > RAISE EXCEPTION USING message = v_msg, errcode = v_sqlstate; >EXCEPTION > WHEN SQLSTATE 'E0001' THEN > RAISE NOTICE '%','Error E0001 raised - going to do something about it'; >

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

Hi Andreas, in my pgbouncer configured as: ;auth_file=/test/user.txt auth_query = SELECT usename, passwd FROM pg_shadow WHERE usename=$1 Of course. When try connect with command " psql -p postgres -U dba" Print erro in screen. psql: ERROR: No such user: dba But, exists user and passwd in

[GENERAL] Re: could not find function "pglogical_table_data_filtered" in file "/usr/pgsql-9.5/lib/pglogical.so"

Looks like upgrading from 9.5.2 to 9.5.8 did it I had another box on 9.5.5 and it was broken there too > On Aug 10, 2017, at 2:13 PM, armand pirvu wrote: > > So I ventured in uninstalling the > https://www.2ndquadrant.com/en/resources/pglogical/pglogical-installation-instructions/ > >

Re: [GENERAL] Re: Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

2017-07-05 16:29 GMT+02:00 Thomas Kellerer : > Hans Schou schrieb am 05.07.2017 um 14:27: > > The dburl (or dburi) has become common to use by many systems > > connecting to a database. The feature is that one can pass all > > parameters in a string, which has similar pattern as http-URI do. > > >

[GENERAL] Re: Feature proposal, DBURL: psql pgsql://joe:p4zzw...@example.org:2345/dbname

Hans Schou schrieb am 05.07.2017 um 14:27: > The dburl (or dburi) has become common to use by many systems > connecting to a database. The feature is that one can pass all > parameters in a string, which has similar pattern as http-URI do. > > Especially when using psql in a script, having the cred

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Mon, Jul 3, 2017 at 10:39 AM, rajan wrote: > Thanks, Jeff. > > Now I am going back to my old question. > > Even though *Session 2* fails to update with UPDATE 0 message, its txid is > saved in xmax of updated(by *Session 1*) tuple. > > As it becomes an old txid, how come new txids are able to

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

Thanks, Jeff. Now I am going back to my old question. Even though *Session 2* fails to update with UPDATE 0 message, its txid is saved in xmax of updated(by *Session 1*) tuple. As it becomes an old txid, how come new txids are able to view it? - -- Thanks, Rajan. -- View this message in c

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Mon, Jul 3, 2017 at 3:02 AM, rajan wrote: > Thanks for the explanation. > > will I be able to view the information using this function, > SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0)); > > Also, please let me know which column I should refer for viewing the > pointer. > It is

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

Thanks for the explanation. will I be able to view the information using this function, SELECT * FROM heap_page_items(get_raw_page('testnumbers', 0)); Also, please let me know which column I should refer for viewing the pointer. - -- Thanks, Rajan. -- View this message in context: http://

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Sat, Jul 1, 2017 at 8:55 PM, rajan wrote: > Thanks, Jeff. That helps understanding it 50%. > > *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple > is > marked for deletion. It means that *(0,2) never exists* when Session 2 is > trying to perform the update. > That it n

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

Thanks, Jeff. That helps understanding it 50%. *Session 2* fails to UPDATE the record which is in *(0,2)* and this tuple is marked for deletion. It means that *(0,2) never exists* when Session 2 is trying to perform the update. In that case, how *Session 3's new row (0,4)* contains the xmax as *

Re: [GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

On Sat, Jul 1, 2017 at 6:32 PM, rajan wrote: > hello, > > thanks for replies, Adrian, Steven. > > >So calling it can advance the xid manually. Some testing here showed > >that what xmin or xmax is created depends on when you call txid_current > >in either the original session or the concurrent se

[GENERAL] Re: have trouble understanding xmin and xmax with update operations from two different sessions

hello, thanks for replies, Adrian, Steven. >So calling it can advance the xid manually. Some testing here showed >that what xmin or xmax is created depends on when you call txid_current >in either the original session or the concurrent sessions. I understand this and I am executing my stateme

[GENERAL] Re: [GENERAL] Significant discrepancy in index cost estimation

After setting seq_page_cost to 3 the execution plan became good, without SeqScan, but it seems strange to set seq_page_cost almost equal to  random_page_cost, therefore i've set seq_page_cost back to defaults, increased the statistics for "sub_id" in "mba_test.subscr_param" to 1000. That gave me

[GENERAL] Re[2]: [GENERAL] SPI_execute_plan and vardata

Thank you next question - how i can call pg_notify or NOTIFY query from c function Arsen >Пятница, 23 июня 2017, 0:33 +03:00 от Tom Lane : > >=?UTF-8?B?0JDRgNGB0LXQvSDQkNGA0YPRgtGO0L3Rj9C9?= < aru...@bk.ru > writes: >> Datum Values[2]; > >> Values[0]-is integer type  >> but Values[1] is string

[GENERAL] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

thanks for the explanation, Gary. - -- Thanks, Rajan. -- View this message in context: http://www.postgresql-archive.org/Unable-to-understand-index-only-scan-as-it-is-not-happening-for-one-table-while-it-happens-for-other-tp5968835p5968976.html Sent from the PostgreSQL - general mailing lis

Re: [GENERAL] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

Hi Rajan, I would say that the optimiser has calculated that it would be quicker to sequentially read thought the table to get the 354 rows returned without the limit. By introducing the limit, it is much faster to pick out the first 10 rows using the index. Using an index is usually only quicke

[GENERAL] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

Thanks. Now I did the same query, but it is going for *index-only scan* only after I put *limit* localdb=# explain analyse verbose select uid from mm where uid>100 order by uid; QUERY PLAN -

Re: [GENERAL] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

rajan wrote: > why the index-only scan *works only* with an *order by*? > localdb=# explain analyse verbose select uid from mm where uid>100 *order > by* uid; > QUERY > PLAN > ---

[GENERAL] Re: Unable to understand index only scan as it is not happening for one table while it happens for other

Ah! Got it. Thanks. One more question, why the index-only scan *works only* with an *order by*? localdb=# explain analyse verbose select uid from mm where uid>100 *order by* uid; QUERY PLAN

[GENERAL] Re: [HACKERS] Why restore_command is called for existing files in pg_xlog?

Hi Jeff, On Mon, Jun 12, 2017, at 06:42 PM, Jeff Janes wrote: > On Mon, Jun 12, 2017 at 5:25 AM, Alex Kliukin > wrote:>> __ >> >> On Fri, Jun 2, 2017, at 11:51 AM, Alexander Kukushkin wrote: >>> Hello hackers, >>> There is one strange and awful thing I don't understand about >>> restore_command:

[GENERAL] Re: Is there possibility btree_redo with XLOG_BTREE_DELETE done between standby_redo and the end of backup

Yeah, I figured out the point(logic). The precondition is should not have any connections accept while recovering. It is clear to me now. Thank you very much. static TransactionId btree_xlog_delete_get_latestRemovedXid(xl_btree_delete *xlrec) { .. if (*CountDBBackends(InvalidOid)* == 0

Re: [GENERAL] Re: Is there possibility btree_redo with XLOG_BTREE_DELETE done between standby_redo and the end of backup

y39chen writes: > We found the panic happened when adding one of our patch. > switch (port->canAcceptConnections) > { > case CAC_STARTUP: > ereport(*LOG*, > (errcode(ERRCODE_CANNOT_CONNECT_NOW), >

Re: [GENERAL] Re: Is there possibility btree_redo with XLOG_BTREE_DELETE done between standby_redo and the end of backup

On Thu, May 25, 2017 at 5:23 AM, y39chen wrote: > My doubt is Standby is redoing the records in WAL from master. how accept > connection in standby side while recovering would trigger > btree_xlog_delete_get_latestRemovedXid() and panic happen. You should look at the relationship between the code

[GENERAL] Re: Is there possibility btree_redo with XLOG_BTREE_DELETE done between standby_redo and the end of backup

Thank you the comments. We found the panic happened when adding one of our patch. static int ProcessStartupPacket(Port *port, bool SSLdone) { .. /* * If we're going to reject the connection due to database state, say so * now instead of wasting cycles on an authentic

[GENERAL] Re: Use function to manipulate rows — how to get separate columns, rather than single row value

> On Apr 23, 2017, at 11:34 , Guyren Howe wrote: > > I’m trying to write a function that manipulates whole rows. It returns the > same type as the table it is being applied to, but when I select the function > on the rows, I get a single column of row type, rather than separate columns. > > M

[GENERAL] Re: Is this pgbouncer configuration suitable for a production environment with hundreds of databases?

Thank you very much David for your quick reply, I understand better now. For now, I'll let default_pool_size=2 and I guess I will have to monitor the total number of databases and adjust configuration when needed, in order to avoid reaching the postgres max_connection limit. I think I can play a

Re: [GENERAL] Re: Debian Bug#859033: pg_dump: creates dumps that cannot be restored

On 03/31/2017 07:34 AM, Thorsten Glaser wrote: Hi *, while I’d still appreciate help on the bugreport (context is this… https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=859033 … one), I’ve found this… http://dba.stackexchange.com/a/75635/65843 … which says ① that using a CHECK constraint to che

[GENERAL] Re: Debian Bug#859033: pg_dump: creates dumps that cannot be restored

Hi *, while I’d still appreciate help on the bugreport (context is this… https://bugs.debian.org/cgi-bin/bugreport.cgi?bug=859033 … one), I’ve found this… http://dba.stackexchange.com/a/75635/65843 … which says ① that using a CHECK constraint to check data from another table is wrong (but not wh

[GENERAL] Re: inevitability of to_date() when converting representations which don't represent whole timestamps

On Thursday, March 30, 2017, Peter J. Holzer wrote: > I > > > > David tells this is not a bug, but it still seems like a reasonable > > requirement on to_date() to me. Is there some reason why this isn’t > > possible? > > The documentation warns that to_date “interpret input liberally, with > min

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

27.02.2017 10:08, I wrote: [...] So, what I've observed is that Wait* functions _usually_ go to sleep nicely when the state is not signalled, but _sometimes_, depending on unknown criteria, it can choose to instead do a busy-loop wait or something CPU-expensive. Maybe it tries to optimize the del

Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

On Tue, Feb 28, 2017 at 10:00 AM, Lisandro wrote: > Hi Steve, thanks for your help. > Your comment made me realise that maybe the problem is my pgBouncer > configuration, specifically default_pool_size. It took me a while to > understand pgbouncer, and I still had some doubts when I configured it.

[GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

Hi Steve, thanks for your help. Your comment made me realise that maybe the problem is my pgBouncer configuration, specifically default_pool_size. It took me a while to understand pgbouncer, and I still had some doubts when I configured it. Now I undesrtand better. I connect to all databases wit

Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

On 02/28/2017 06:01 AM, Lisandro wrote: Thank you Adrian. Yes, I confirm that all the databases are running in one PostgreSQL server/instance. I'm running this version: PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit Let me ask: is there a w

Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

Lisandro writes: > Let me ask: is there a way to monitor the total connections to postgresql > through time? Or should I make my own script for that? I ask because every > time the error is thrown, I check the total connections with "select > count(*) from pg_stat_activity;" but the number is alwa

[GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

Thank you Adrian. Yes, I confirm that all the databases are running in one PostgreSQL server/instance. I'm running this version: PostgreSQL 9.3.15 on i686-pc-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04.3) 4.8.4, 32-bit Let me ask: is there a way to monitor the total connections to pos

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

15.02.2017 0:06, I wrote: [...] Indeed, such function is available. But essentially, this function is a (kind of) combined login+logout, therefore it would not work for my purpose. (Despite its name, it can not be used to perform some communication "ping" within an established session, such thing

Re: [GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

On 02/25/2017 07:29 AM, lisandro wrote: Thanks for the quick answer. superuser_reserved_connections is set to 3 Actually, it's not set (the line is commented) but the default for superuser_reserved_connections is 3: https://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-S

[GENERAL] Re: GMT FATAL: remaining connection slots are reserved for non-replication superuser connections, but I'm using pgBouncer for connection pooling

Thanks for the quick answer. superuser_reserved_connections is set to 3 Actually, it's not set (the line is commented) but the default for superuser_reserved_connections is 3: https://www.postgresql.org/docs/9.2/static/runtime-config-connection.html#GUC-SUPERUSER-RESERVED-CONNECTIONS 2017-02-25

[GENERAL] Re: [GENERAL] How tö select a column?

On Sat, Feb 18, 2017 at 9:33 AM, Egon Frerich wrote: > I have a table with two columns with type money. If column 'a' has an > amount > 0 then this amount is wanted else the amount from column 'b'. ​https://www.postgresql.org/docs/9.6/static/functions-conditional.html SELECT CASE WHEN a > 0 TH

[GENERAL] Re: [GENERAL] PostgreSQL mirroring from RPM install to RPM install-revisited

Thanks for the response Adrian, Both servers are pretty much identical.  uname -a master Linux devtmbm178 2.6.32-642.6.2.el6.x86_64 #1 SMP Tue Oct 25 13:37:48 PDT 2016 x86_64 x86_64 x86_64 GNU/Linux slave Linux devtmbm176 2.6.32-642.11.1.el6.x86_64 #1 SMP Tue Nov 15 09:40:59 PST 2016 x86_64 x

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

14.02.2017 18:15, Rader, David: [...] Try the libpq call from pg_isready. It does actually make a round trip to the postgres server and asks the server if it is ready to accept connections. So you are running the socket communication code of postgres and a small bit of "status" check but not any

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

-- David Rader dav...@openscg.com On Tue, Feb 14, 2017 at 5:28 AM, Nikolai Zhubr wrote: > 14.02.2017 12:47, John R Pierce: > >> On 2/13/2017 11:03 PM, Nikolai Zhubr wrote: >> >>> Now I'd like to locate a CPU eater more precisely - supposedly there >>> is some issue with communication, that is wh

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

14.02.2017 12:47, John R Pierce: On 2/13/2017 11:03 PM, Nikolai Zhubr wrote: Now I'd like to locate a CPU eater more precisely - supposedly there is some issue with communication, that is why I don't want to mix in anything else. use iperf to test the network transport layer, without any postg

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

On 2/13/2017 11:03 PM, Nikolai Zhubr wrote: Now I'd like to locate a CPU eater more precisely - supposedly there is some issue with communication, that is why I don't want to mix in anything else. use iperf to test the network transport layer, without any postgres in the loop? -- john r pi

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

14.02.2017 1:10, Thomas Kellerer: Nikolai Zhubr schrieb am 13.02.2017 um 23:03: Maybe I should have been more specific. What I need is debugging/profiling pure communication side of server operation, implying huge lots of requests and replies going over the wire to and from the server within som

Re: [GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

On Mon, Feb 13, 2017 at 5:10 PM, Thomas Kellerer wrote: > Nikolai Zhubr schrieb am 13.02.2017 um 23:03: > >> Maybe I should have been more specific. >> What I need is debugging/profiling pure communication side of server >> operation, implying huge lots of requests and replies going over the >> w

[GENERAL] Re: Causeless CPU load waves in backend, on windows, 9.5.5 (EDB binary).

Nikolai Zhubr schrieb am 13.02.2017 um 23:03: Maybe I should have been more specific. What I need is debugging/profiling pure communication side of server operation, implying huge lots of requests and replies going over the wire to and from the server within some continued (valid) session, but so

[GENERAL] Re: PSQL 9.5 select for update locks too many rows when using numeric instead of int

Looks to me the first plan was using seq scan not the index b/c the value had to be cast to numeric. In such case index is not used, as expected. Filter: ((true_data_id)::numeric = '209390104'::numeric) Thanks, Patricia From: Sfiligoi, Igor [mailto:igor.sfili...@ga.com] Sent: Th

[GENERAL] Re: PSQL 9.5 select for update locks too many rows when using numeric instead of int

Uhm... maybe I misinterpreted the results. Looking better, the root cause seems to be that the query planner is not using the index, resorting to a seq scan instead. OK... that makes more sense. Sorry for the bogus email. Igor From: pgsql-general-ow...@postgresql.org [mailto:pgsql-general-ow

Re: [GENERAL] Re: Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

postgres user writes: > Also can you explain if I built Postgres from source on one platform lets > say RHEL_6 and deployed its artifacts like its binaries, libs and share on > a CentOS In general I would not expect that to work. RHEL to CentOS is a special case because they're really the same p

[GENERAL] Re: Using different GCC, CFLAGS, CCFLAGS and CPPFLAGS to compile Postgres and PostGIS?

Also can you explain if I built Postgres from source on one platform lets say RHEL_6 and deployed its artifacts like its binaries, libs and share on a CentOS and tried building extensions against Postgres on CentOS are there any dangers of doing that? On Wed, Feb 1, 2017 at 8:34 PM, postgres user

[GENERAL] Re: How does Postgres estimate the memory needed for sorting/aggregating

Tomas Vondra schrieb am 25.01.2017 um 22:46: I guess this is based on the column statistics stored in pg_stats, but I am not sure: It is based on the average length of values in that column, yes. Thanks for confirming that. I assume this is taken from pg_stats.avg_width ? I'm not sure wha

[GENERAL] Re: [ADMIN] postgresql : could not serialize access due to read/write dependencies among transactions

On Tue, Jan 17, 2017 at 10:54 PM, Neslisah Demirci wrote: > could not serialize access due to read/write dependencies among > transactions > I also add an index to my query and my query's execution plan > don't use seq scan . These two issues are likely to be somewhat related -- if a sequential

Re: [GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

On Tue, Jan 17, 2017 at 1:45 PM, Thomas Kellerer wrote: > Tom Lane schrieb am 17.01.2017 um 13:41: > > Thomas Kellerer writes: > >> So my question is: Is there any way to specify an alternate wildcard > escape when using LIKE ANY (..)? > > > > No, not with ESCAPE. [ manfully resists temptation

Re: [GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

On Tue, Jan 17, 2017 at 03:27:57PM +0100, Thomas Kellerer wrote: >> Do you need to have the _ NOT be recognized as a wildcard ? > > Yes, the underscore should NOT be a wildcard in this case. Understood. So, as Tom hinted at, your best bet might be to write a function escape_underscore_

[GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

Karsten Hilbert schrieb am 17.01.2017 um 14:42: >> I recently stumbled over the need to use a wildcard escape character for a >> condition that makes use of LIKE ANY, something like: >> >>select * >>from some_table >>where name like any (array['foo_bar%', 'bar_foo%']) escape '/'; >> >>

[GENERAL] Re: Is it possible to specify an ESCAPE for a wildcard search using LIKE ANY (...)

Tom Lane schrieb am 17.01.2017 um 13:41: > Thomas Kellerer writes: >> So my question is: Is there any way to specify an alternate wildcard escape >> when using LIKE ANY (..)? > > No, not with ESCAPE. [ manfully resists temptation to run down SQL > committee's ability to design composable synta

Re: [GENERAL] Re: Are new connection/security features in order, given connection pooling?

On Fri, Jan 13, 2017 at 7:39 PM, Karl Czajkowski wrote: > The concern was raised about the risk of the subsequent queries being > able to reverse the "set role" to subvert such authorization. Others > in the thread provided the common advice of parametric queries to > prevent query injection, but

Re: [GENERAL] Re: Building PL/Perl procedural language without --with-perl configure flag

On Sun, Jan 15, 2017 at 6:35 AM, postgres user wrote: > The already installed Postgres edition was built using the same > installation procedure as mentioned in the docs, but without the use of > --with-perl flag. the point I ask the question is because I want to install > PL/Perl as a separate e

[GENERAL] Re: Building PL/Perl procedural language without --with-perl configure flag

The already installed Postgres edition was built using the same installation procedure as mentioned in the docs, but without the use of --with-perl flag. the point I ask the question is because I want to install PL/Perl as a separate extension as one does with PostGIS and not along with Postgres in

[GENERAL] Re: Are new connection/security features in order, given connection pooling?

On Jan 14, Tomas Vondra modulated: ... > Sure, a lot of systems generate queries on the fly. Also, if the > main problem is poor plan choice due to RLS, I'm not sure how > prepared statements could help with that. > Sorry for being unclear, I was trying to respond to too many sub-topics at once.

[GENERAL] Re: Are new connection/security features in order, given connection pooling?

On 01/12/2017 03:12 AM, Karl Czajkowski wrote: I can relate to the original plea from my own exploration of this topic. Before I get into that, I will mention as an aside that to date we have found RLS to be really slow for web client authorization, given that we have to use session parameters t

[GENERAL] Re: Are new connection/security features in order, given connection pooling?

I can relate to the original plea from my own exploration of this topic. Before I get into that, I will mention as an aside that to date we have found RLS to be really slow for web client authorization, given that we have to use session parameters to store web client context and there doesn't seem

[GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

Adrian, Tom Finally I did upgrade version but I've removed database pem (Postgres Enterprise Manager) I guess that this database has some link in some function to sslutils, because pg_upgrade showed the above errors while upgraded this database. /pg_restore: creating FUNCTION "public.sslutils_ve

Re: [GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

On 01/04/2017 09:27 AM, DrakoRod wrote: Teorycally, I removed the sslutils from old cluster when review the $libdir appear this: What where the exact steps you took to remove sslutils? /[postgres@server ~]$ /opt/PostgreSQL/9.3/bin/pg_config --pkglibdir /opt/PostgreSQL/9.3/lib/postgresql [po

[GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

Teorycally, I removed the sslutils from old cluster when review the $libdir appear this: /[postgres@server ~]$ /opt/PostgreSQL/9.3/bin/pg_config --pkglibdir /opt/PostgreSQL/9.3/lib/postgresql [postgres@server ~]$ /opt/PostgreSQL/9.3/bin/pg_config --libs -lpgport -lpgcommon -lxslt -lxml2 -lpam -l

Re: [GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

On 01/03/2017 09:01 PM, DrakoRod wrote: Yes I installed Postgres Enterprise Manager Agent time ago in this server to test agent, but now I don't use it. Amm if you refer the EDB install with binaries PostgreSQL one-click yes, but is not a EDB Advanced Server , is a normal Cluster installed by ED

Re: [GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

DrakoRod writes: > Yes I installed Postgres Enterprise Manager Agent time ago in this server to > test agent, but now I don't use it. Removing the sslutils extension from the old cluster might be an easy solution, then. It sounds like someone messed up the upgrade path for sslutils, but that's

[GENERAL] Re: could not load library "$libdir/sslutils": in pg_upgrade process

Yes I installed Postgres Enterprise Manager Agent time ago in this server to test agent, but now I don't use it. Amm if you refer the EDB install with binaries PostgreSQL one-click yes, but is not a EDB Advanced Server , is a normal Cluster installed by EDB binaries. - Dame un poco de fe,

Re: [GENERAL] Re: Re: [GENERAL] PostgreSQL mirroring from RPM install to Source install

On 12/15/2016 2:16 PM, Richard Brosnahan wrote: gdb is not available on this machine. Neither which or locate could find it. The servers we're given are truly, shockingly, stripped down models. what OS is this, what compiler toolchain did you use to build postgres on them? -- john r pie

[GENERAL] Re: Re: [GENERAL] PostgreSQL mirroring from RPM install to Source install

gdb is not available on this machine. Neither which or locate could find it. The servers we're given are truly, shockingly, stripped down models.  At this point, I believe my best course of action is to twist some sys admin arms and get a properly installed PostgreSQL on this machine. Even if

[GENERAL] Re: [GENERAL] PostgreSQL mirroring from RPM install to Source install

The slave: $ pg_controldata --version pg_controldata (PostgreSQL) 9.4.1 $ echo $PGDATA /apps/database/postgresql-data $ pg_controldata $PGDATA WARNING: Calculated CRC checksum does not match value stored in file. Either the file is corrupt, or it has a different layout than this program is expecti

[GENERAL] Re: [GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

On Dec 16, 2016, at 16:52 , Tom Lane wrote: > > The server already does deliver more-structured error data, although I confess > that I have no idea how to get at it in Ruby on Rails. In psql the case > looks about like this: Thanks for the advice. I’ve worked out how to get at the same informa

[GENERAL] Re: [GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

On Fri, Dec 16, 2016 at 3:54 PM, Guyren Howe wrote: > What I need to do is turn this into something similar to the equivalent > Rails-side constraint failure, which is a nicely formatted error message on > the model object. Can you show what the text in such a message looks like? -- Kevin Grit

[GENERAL] Re: [GENERAL] Love Your Database project — Thoughts on effectively handling constraints?

On Dec 16, 2016, at 16:52 , Tom Lane wrote: > >> So I’ve started a project to fix this. I’m initially going to write a series >> of blog posts demonstrating in principle how a developer can put much/all of >> their model logic in their database. > > Cool. This sounds well worth while. Thanks

Re: [GENERAL] Re: [GENERAL] PostgreSQL mirroring from RPM install to Source install

Richard Brosnahan =?utf-8?B?UmU6IFtHRU5FUkFMXSBQb3N0Z3JlU1FMIG1pcnJvcmluZyBmcm9tIFJQTSBp?= =?utf-8?B?bnN0YWxsIHRvIFNvdXJjZSBpbnN0YWxs?= writes: > The slave: > $ pg_controldata --version > pg_controldata (PostgreSQL) 9.4.1 > $ echo $PGDATA > /apps/database/postgresql-data > $ pg_controldata $PGDA

[GENERAL] Re: pg_upgrade 9.0 to 9.6

Thomas, very handy page, thanks for the link. If I understand it correctly, ideally the upgrade process should look like: 9.0.x --> 9.0.23 + recommended fixes (the main is about table's relfrozenxid) 9.0.23 --> 9.4.5 (as the last version, where 9.0.23 was supported) 9.4.5 --> 9.4.10 + fixes 9.4.10

[GENERAL] Re[2]: [GENERAL] pg_upgrade 9.0 to 9.6

John, thanks! Your approach significantly reduces the number of checks. >Вторник, 13 декабря 2016, 13:34 +03:00 от John R Pierce : > >On 12/13/2016 1:57 AM, Mikhail wrote: >> Should i check all the production environments for the problems, >> mentioned in all interim versions release notes, is i

Re: [GENERAL] Re: [ADMIN] Would like to below scenario is possible for getting page/block corruption

On Sun, Dec 11, 2016 at 12:00 PM, Sreekanth Palluru wrote: > I am looking at possibility of PG introducing corruption if relation extends > and before it updates new page with pageheader in memory and crash happens? > > Is this possible? No. > Does PG updates pageheader when relation get extend

[GENERAL] Re: [ADMIN] Would like to below scenario is possible for getting page/block corruption

shreeyansh, we have issue with relation and we have fixed this using setting zero_damaged_pages and then running vacuum fullbon relatuon. I am looking at possibility of PG introducing corruption if relation extends and before it updates new page with pageheader in memory and crash happens? Is thi

[GENERAL] Re: [ADMIN] Would like to below scenario is possible for getting page/block corruption

Hi Sreekanth, I doubt auto-recover of the page might be possible, as the header of the page is no more valid & corrupted and not sure whether the corruption occurred in relation of a data or index block. We have seen some occurrences like this before which got rectified by performing reindexing a

[GENERAL] Re: [GENERAL] Does PostgreSQL support BIM(Building Information Modeling) storage？

You should ask on the PostGIS list - they will be closer to this: https://lists.osgeo.org/mailman/listinfo/postgis-users -- David Rader dav...@openscg.com On Mon, Dec 5, 2016 at 9:05 PM, sunpeng wrote: > Does PostgreSQL support BIM(Building Information Modeling) storage？Or how? > I can only f

[GENERAL] Re: FreeBSD 10 => 11: Dump and reload your PostgreSQL database unless you like it broken

I forgot to add that when I turned off index scan, select lower() still didn’t find some existing rows. On Sat, Dec 3, 2016 at 2:52 AM, Maeldron T. wrote: > Hello, > > I’ll be as short as I can as I have broken my arm and I’m not supposed to > type. This isn’t a joke. > > However, I learned the

[GENERAL] Re: [GENERAL] [GENERAL] Replication between différent versions of the same OS.

Thanks a lot. That s what I was looking for ;) Yes, I was trying to avoid logical replication. I guess it s time for me to delve into it...

[GENERAL] Re: [GENERAL] FTS query, statistics and planner estimations…

On Wed, Nov 9, 2016 at 11:19 AM, Pierre Ducroquet wrote: > Indeed the words in the query are correlated, but I do hope that the FTS > indexing is able to cope with that. If the query returns correct results in reasonable time it can. OTOH the planner, and the statistics system, is another beast.

[GENERAL] Re: [GENERAL] FTS query, statistics and planner estimations…

2016-11-09 11:19 GMT+01:00 Pierre Ducroquet : > On Wednesday, November 9, 2016 10:40:10 AM CET Francisco Olarte wrote: > > Pierre: > > > > On Wed, Nov 9, 2016 at 10:22 AM, Pierre Ducroquet > > > > wrote: > > > The query does a few joins «after» running a FTS query on a main table. > > > The FTS q

Re: [GENERAL] Re: [GENERAL] FTS query, statistics and planner estimations…

On Wednesday, November 9, 2016 10:51:11 AM CET Pavel Stehule wrote: > 2016-11-09 10:40 GMT+01:00 Francisco Olarte : > > Pierre: > > > > On Wed, Nov 9, 2016 at 10:22 AM, Pierre Ducroquet > > > > wrote: > > > The query does a few joins «after» running a FTS query on a main table. > > > The FTS que

[GENERAL] Re: [GENERAL] Re: [GENERAL] FTS query, statistics and planner estimations…

2016-11-09 10:40 GMT+01:00 Francisco Olarte : > Pierre: > > On Wed, Nov 9, 2016 at 10:22 AM, Pierre Ducroquet > wrote: > > The query does a few joins «after» running a FTS query on a main table. > > The FTS query returns a few thousand rows, but the estimations are wrong, > > leading the optimize

[GENERAL] Re: [GENERAL] FTS query, statistics and planner estimations…

Pierre: On Wed, Nov 9, 2016 at 10:22 AM, Pierre Ducroquet wrote: > The query does a few joins «after» running a FTS query on a main table. > The FTS query returns a few thousand rows, but the estimations are wrong, > leading the optimizer to terrible plans compared to what should happen, and > th

[GENERAL] Re: What is the best thing to do with PUBLIC schema in Postgresql database

Patricia Hu wrote: > Since it could potentially be a security loop hole. So far the action taken > to address it falls into > these two categories: > > drop the PUBLIC schema altogether. One of the concerns is with some of > the system objects that > have been exposed through PUBLIC schema p

[GENERAL] Re: [GENERAL] Understanding “max_wal_size” and “min_wal_size” parameters default values from postgresql.conf file

Thank you very much On Mon, Oct 3, 2016 at 11:46 PM, Tom Lane wrote: > otar shavadze writes: > > name | setting | unit-- > > max_wal_size | 64 | > > min_wal_size | 5| > > > I have 2 questions: > > > 1) Why these values doesn't match default

[GENERAL] "Re: Question about grant create on database and pg_dump/pg_dumpall"

On Tue, Jul 5, 2016 at 06:39 AM, Haribabu Kommi kommi(dot)haribabu(at)gmail(dot)com wrote: Still i feel the GRANT statements should be present, as the create database statement is generated only with -C option. So attached patch produces the GRANT statements based on the -x option. The attached

  1   2   3   4   5   6   7   8   9   10   >