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] expanded auto and header linestyle double

Thanks for the reply, Pavel! On Thu, Nov 16, 2017 at 1:01 AM, Pavel Stehule <pavel.steh...@gmail.com> wrote: > Hi > > 2017-11-15 23:37 GMT+01:00 Matt Zagrabelny <mzagr...@d.umn.edu>: > >> Greetings, >> >> Using PG 10.1. >> >> In my .ps

[GENERAL] expanded auto and header linestyle double

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, but I do for all subsequent records. For example: % select * from artist; ─[ RECORD 1

Re: [GENERAL] Performance options for CPU bound multi-SUM query

Moving from NUMERIC to FLOAT(8) did indeed lower query times by about 20%. I will try fixeddecimal and agg() as time permits. On 25 Jan 2016, at 4:44, David Rowley wrote: On 25 January 2016 at 15:45, Matt <bsg...@gmail.com> wrote: I have a warehousing case where data is bucketed by

[GENERAL] prefix package availability for 9.5

prefix95 published for rhel-7-x86_64? How would one find out? Many thanks, Matt

[GENERAL] Performance options for CPU bound multi-SUM query

I have a warehousing case where data is bucketed by a key of an hourly timestamp and 3 other columns. In addition there are 32 numeric columns. The tables are partitioned on regular date ranges, and aggregated to the lowest resolution usable. The principal use case is to select over a range

[GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

Attempting to upgrade a large (3TB) postgressql database from 9.3 to 9.4 on Ubuntu 14.04 LTS, but the process fails fairly early on. The error message instructs me to look at the last few lines of pg_upgrade_utility.log for more info, and the last two lines there (the only ones that don't

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

On 03/06/2015 12:37 PM, Adrian Klaver wrote: Agreed, I am just trying to figure out how you get: CREATE DATABASE template0 WITH TEMPLATE = template0 .. Seems to be a snake eating its tail:) Yes. It does. And it's pretty obvious why having this would be a problem...not quite so obvious how

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

On 03/06/2015 01:55 PM, Adrian Klaver wrote: So on the original cluster, log in using psql and do \l and post the results here. Thanks. [...] Meant to add to previous post, to check with issue that Stephen mentioned do: select datname, datallowconn from pg_database ; postgres=# \l

Re: [GENERAL] pg_upgrade failing from 9.3 to 9.4 because template0 already exists

On 03/06/2015 02:43 PM, Stephen Frost wrote: Right, as I mentioned, template0 shouldn't have datallowconn as 'true'. That's why it's being included in the pg_dumpall. On your test setup, run (as superuser): update pg_database set datallowconn = false where datname = 'template0'; Then re-run

Re: [GENERAL] Localhost vs. Unix Domain Sockets?

I went through the same process a little while ago - worth reading is the pg_hba.conf documentation: http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html Specifically: * Don't enable trust auth (i.e. any OS user as any DB user) - that's rarely what you want on a multi-user machine. *

Re: [GENERAL] Localhost vs. Unix Domain Sockets?

To put it another way, keeping the two sets of names distinct is incrementally more complex to manage. Which might be worth it if there really is any gain. Is this a best practice, or is it really a manifestation of its closely-related cousin, the silly practice? :) It's ultimately up to your

[GENERAL] pg_ident.hba on a single-user, multi-app machine

Hi all. Trying to rationalise my pg_hba.conf and pg_ident.conf configuration on a Debian/Ubuntu machine where: * One primary application user (“deploy”) runs web applications * postgres, nginx, et. al run under their own users * Using a Unix socket for connecting to PostgreSQL on the same

Re: [GENERAL] Table checksum proposal

On Thu, Jul 24, 2014 at 3:35 AM, m...@byrney.com wrote: I have a suggestion for a table checksumming facility within PostgreSQL. The applications are reasonably obvious - detecting changes to tables, validating data migrations, unit testing etc. A possible algorithm is as follows: 1. For

Re: [GENERAL] Complex Recursive Query

I wouldn't do this with recursion; plain old iteration is your friend (yes, WITH RECURSIVE is actually iterative, not recursive...) The algorithm goes like this: 1. Extend your graph relation to be symmetric and transitive. 2. Assign a integer group id to each node. 3. Repeatedly join the node

[GENERAL] Table checksum proposal

I have a suggestion for a table checksumming facility within PostgreSQL. The applications are reasonably obvious - detecting changes to tables, validating data migrations, unit testing etc. A possible algorithm is as follows: 1. For each row of the table, take the binary representations of the

[GENERAL] Re: [GENERAL] Re: [GENERAL] Fwd: Help！Why CPU Usage and LoadAverage Jump up Suddenly

Is khugepaged running during the stalls? http://www.postgresql.org/message-id/20130716195834.8fe5c79249cb2ff0d4270...@yahoo.es Matt On Thu, Dec 5, 2013 at 7:44 AM, Scott Marlowe scott.marl...@gmail.com wrote: On Thu, Dec 5, 2013 at 1:46 AM, 吕晓旭 lxxstc...@gmail.com wrote: Hi, all We

Re: [GENERAL] Clone database using rsync?

the production server offline. If you go with the folder copy and your installation has postgresql.conf, pg_hba.conf and so on in your data folder, you'll probably want to edit them after the copy - more logging, different security etc. Matt I need to clone production database to development server

[GENERAL] INSERT/UPDATE statements sometimes choosing not to use PK index?

I have a relatively simple data load script, which upserts (UPDATE existing rows, INSERT new rows), which should be supported by the primary key index, the only index on this table: UPDATE destination SET ... FROM staging WHERE staging.pk = destination.pk; INSERT INTO destination SELECT

Re: [GENERAL] INSERT/UPDATE statements sometimes choosing not to use PK index?

worse, even though the explain plans appear identical: INSERT INTO destination (…) SELECT (…) FROM staging LEFT JOIN destination ON destination.id = staging.id WHERE destination.id IS NULL On 29 Oct 2013, at 9:45, Tom Lane wrote: Matt bsg...@gmail.com writes: In most cases

Re: [GENERAL] Question about using AggCheckCallContext in a C function

On Aug 12, 2013, at 12:47 PM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Solnit msol...@soasta.com writes: 2. The function seems to work consistently when I do a SELECT SUM(mycol) without any GROUP BY. It's only when I add grouping that the failures happen. I'm not sure if this is a real clue

[GENERAL] Question about using AggCheckCallContext in a C function

, Matt Solnit msol...@soasta.com -- 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] Question about using AggCheckCallContext in a C function

On Aug 12, 2013, at 11:53 AM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Solnit msol...@soasta.com writes: After poring over the code in nodeAgg.c, and looking at the in8inc() function, I think I know what the problem is: the typical use of AggCheckCallContext() is not compatible with TOAST

[GENERAL] Differences in Unicode handling on Mac vs Linux?

-check? Or are there any known Mac-related Unicode issues? Thanks! Matt -- 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] Differences in Unicode handling on Mac vs Linux?

| asset_sg_kdo_děláassigned_to__connections | table| matt For the short term, I think I'll boot up a Linux VM to troubleshoot my production bug... but I'll submit a bug report with repro steps. Thanks Tom! Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

appreciated. Matt. On 13 May 2013, at 14:49, Merlin Moncure mmonc...@gmail.com wrote: On Sun, May 12, 2013 at 8:20 PM, John R Pierce pie...@hogranch.com wrote: On 5/12/2013 6:13 PM, David Boreham wrote: Not quite. More like : a) I don't know where to buy SLC drives in 2013 (all the drives

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

to be available on the HP website - hopefully it will be forthcoming at some point. Matt. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

hardware setup in order to have experience with these general issues. The P420i controller appears to be compatible with recent versions of CentOS, so drivers should not be a concern (hopefully). Any insights anyone can offer on these issues would be most welcome. Regards, Matt. -- Sent via

Re: [GENERAL] Deploying PostgreSQL on CentOS with SSD and Hardware RAID

, 2013 at 9:14 AM, Matt Brock m...@mattbrock.co.uk wrote: Hello. We're intending to deploy PostgreSQL on Linux with SSD drives which would be in a RAID 1 configuration with Hardware RAID. My first question is essentially: are there any issues we need to be aware of when running PostgreSQL 9

Re: [GENERAL] regex help wanted

On 2013-04-25, Karsten Hilbert karsten.hilb...@gmx.net wrote: On Thu, Apr 25, 2013 at 10:32:26AM -0400, Tom Lane wrote: Karsten Hilbert karsten.hilb...@gmx.net writes: What I don't understand is: Why does the following return a substring ? select substring ('junk $allergy::test::99$

[GENERAL] Maintaining state across function calls

should prevents leakage altogether. Is this a reasonable thing to do? What are the risks? Is there a more best-practice way to achieve the same result? Many thanks, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Maintaining state across function calls

an allocator which uses palloc and pfree instead of the default allocator, which uses new and delete? Matt -- 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] Maintaining state across function calls

m...@byrney.com writes: The question is: what's the best practice way of letting a C/C++-language function hang onto internal state across calls? A static variable for that is a really horrid idea. Instead use fcinfo-flinfo-fn_extra to point to some workspace palloc'd in the appropriate

[GENERAL] pg_hba.conf directory?

Hello, I've searched the mailing list archives and google regarding using a directory to contain pg_hba.conf snippets. Does such a feature exist for any version of PG? Would this be a better question for a pg dev mailing list? Please Cc me, I am not (yet) subscribed to the list. Thanks! -Matt

[GENERAL] streaming replication and data file consistency

in advance for helping me understand this behavior! - Matt

Re: [GENERAL] indexes no longer used after shutdown during reindexing

On 01/13/2012 02:49 PM, Tomas Vondra wrote: On 13.1.2012 22:20, Tom Lane wrote: Matt Dewma...@consistentstate.com writes: An interesting sidenote we realized. the nice system shutdown script /etc/init.d/postgres doesn't actually wait for the db to be down, it just waits for pg_ctl to return

Re: [GENERAL] indexes no longer used after shutdown during reindexing

On 01/12/2012 01:21 PM, Tom Lane wrote: Matt Dewma...@consistentstate.com writes: On 01/11/2012 04:29 PM, Tom Lane wrote: What exactly is your definition of a clean shutdown? Is a reboot command considered a clean shutdown? It's a redhat box which called /etc/init.d/postgresql stop, which

Re: [GENERAL] indexes no longer used after shutdown during reindexing

On 01/12/2012 01:21 PM, Tom Lane wrote: Matt Dewma...@consistentstate.com writes: On 01/11/2012 04:29 PM, Tom Lane wrote: What exactly is your definition of a clean shutdown? Is a reboot command considered a clean shutdown? It's a redhat box which called /etc/init.d/postgresql stop, which

Re: [GENERAL] indexes no longer used after shutdown during reindexing

On 01/11/2012 04:29 PM, Tom Lane wrote: Matt Dewma...@consistentstate.com writes: I have a database that was shut down, cleanly, during an 'reindex table' command. When the database came back up, queries against that table started doing sequential scans instead of using the indexes

[GENERAL] indexes no longer used after shutdown during reindexing

this problem? If so, what specifically is the cause? Is shutting down a database during a table rebuild or vacuum an absolute no-no? Any and all help or insight would be appreciated, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] indexes no longer used after shutdown during reindexing

On 01/11/2012 11:07 AM, Scott Marlowe wrote: On Wed, Jan 11, 2012 at 10:42 AM, Matt Dewma...@consistentstate.com wrote: Hello all, I have a database that was shut down, cleanly, during an 'reindex table' command. When the database came back up, queries against that table started doing

[GENERAL] New Application Development Announcement

://collectablesdb.net or github.com/CollectablesDB Matt

Re: [GENERAL] Web Hosting

Thanks, but I tried that originally and the companies that come up have either poor ratings, won't support postgres, won't allow me the freedom to run my own software, or after talking with them I realized there was PEBKAC issues with there support staff. I also, as stated earlier, won't go with

[GENERAL] Unprivileged access to pgsql functions?

row) postgres=# \q TIA, Matt

Re: [GENERAL] Unprivileged access to pgsql functions?

It's a contrib module: http://pgfoundry.org/projects/orafce/ Matt On Fri, Mar 4, 2011 at 1:20 PM, John R Pierce pie...@hogranch.com wrote: On 03/04/11 1:11 PM, Matt Warner wrote: Good afternoon. I've been looking at the Oracle Functionality package. ... what is this? doesn't sound

Re: [GENERAL] Unprivileged access to pgsql functions?

To be clear, this is open source Postgres I'm using, not the enterprise product. Matt On Fri, Mar 4, 2011 at 1:29 PM, Matt Warner m...@warnertechnology.comwrote: It's a contrib module: http://pgfoundry.org/projects/orafce/ Matt On Fri, Mar 4, 2011 at 1:20 PM, John R Pierce pie

Re: [GENERAL] Unprivileged access to pgsql functions?

name and argument types. You might need to add explicit type casts. On Fri, Mar 4, 2011 at 1:34 PM, Vibhor Kumar vibhor.ku...@enterprisedb.comwrote: On Mar 5, 2011, at 2:50 AM, John R Pierce wrote: On 03/04/11 1:11 PM, Matt Warner wrote: Good afternoon. I've been looking at the Oracle

Re: [GENERAL] Unprivileged access to pgsql functions?

Here's how the script is defining the function, if that helps: CREATE FUNCTION nvl(anyelement, anyelement) RETURNS anyelement AS '$libdir/orafunc','ora_nvl' LANGUAGE C IMMUTABLE; On Fri, Mar 4, 2011 at 1:41 PM, Matt Warner m...@warnertechnology.comwrote: No luck: *** as postgres postgres

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:48 PM, Bosco Rama postg...@boscorama.com wrote: Matt Warner wrote: No luck: *** as postgres postgres=# GRANT all on function nvl(anyelement,anyelement) to public; GRANT postgres=# *** as unprivileged user offload= select nvl(0,1); ERROR: function nvl

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:49 PM, John R Pierce pie...@hogranch.com wrote: On 03/04/11 1:41 PM, Matt Warner wrote: No luck: *** as postgres postgres=# GRANT all on function nvl(anyelement,anyelement) to public; GRANT postgres=# *** as unprivileged user offload= select nvl(0,1); ERROR

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:51 PM, Andrew Sullivan a...@crankycanuck.ca wrote: On Fri, Mar 04, 2011 at 01:41:34PM -0800, Matt Warner wrote: No luck: *** as postgres postgres=# GRANT all on function nvl(anyelement,anyelement) to public; GRANT postgres=# *** as unprivileged user

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 1:56 PM, Bosco Rama postg...@boscorama.com wrote: Matt Warner wrote: The function cannot be defined in the user's DB because language C is considered a security risk, so only the superuser can do that. Or that's what I get from reading anyway... psql -U postgres

Re: [GENERAL] Unprivileged access to pgsql functions?

On Fri, Mar 4, 2011 at 2:03 PM, John R Pierce pie...@hogranch.com wrote: On 03/04/11 1:57 PM, Matt Warner wrote: Not sure. I believe public and pg_catalog are in the path by default. Most of the create function declarations prepend pg_catalog, and I believe I saw somewhere that pg_catalog

Re: [GENERAL] Looking for Suggestion on Learning

On Sun, Feb 6, 2011 at 11:14 AM, ray joseph r...@aarden.us wrote: Matt, Thank you for your insightful view. I do not have a design for any of my design opportunities. This is one reason I was looking for a design tool. I have many work processes that are inter related, generated

Re: [GENERAL] Full Text Index Scanning

)) else '' end $$ language sql immutable strict; On Sat, 29 Jan 2011, Matt Warner wrote: 9.0.2 On Sat, Jan 29, 2011 at 9:35 AM, Oleg Bartunov o...@sai.msu.su wrote: What version of Pg you run ? Try latest version. Oleg On Sat, 29 Jan 2011, Matt Warner wrote: Reverse isn't a built

Re: [GENERAL] Full Text Index Scanning

Aha! Thanks for pointing that out. It's indexing now. Thanks! Matt On Sun, Jan 30, 2011 at 9:12 AM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Warner m...@warnertechnology.com writes: Doesn't seem to work either. Maybe something changed in 9.1? create index test_idx on testtable using gin

Re: [GENERAL] Full Text Index Scanning

If I understand this, it looks like this approach allows me to match the beginnings and endings of words, but not the middle sections. Is that correct? That is, if I search for jag I will find jaeger but not lobenjager. Or am I (again) not understanding how this works? TIA, Matt On Sun, Jan 30

Re: [GENERAL] Full Text Index Scanning

: create index test_idx on test using gist(columnname gist_trgm_ops); ERROR: operator class gist_trgm_ops does not exist for access method gist On Sun, Jan 30, 2011 at 10:36 AM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Warner m...@warnertechnology.com writes: If I understand this, it looks like

Re: [GENERAL] Full Text Index Scanning

Thanks Oleg. I'm going to have to experiment with this so that I understand it better. Matt On Fri, Jan 28, 2011 at 1:12 PM, Oleg Bartunov o...@sai.msu.su wrote: Matt, I'd try to use prefix search on original string concatenated with reverse string: Just tried on some spare table knn=# \d

Re: [GENERAL] Full Text Index Scanning

. Is there a specific version of the reverse function you're using? Or am I just missing something obvious? This is Postgres 9, BTW. Thanks, Matt On Sat, Jan 29, 2011 at 6:46 AM, Matt Warner m...@warnertechnology.comwrote: Thanks Oleg. I'm going to have to experiment with this so that I understand it better

Re: [GENERAL] Full Text Index Scanning

9.0.2 On Sat, Jan 29, 2011 at 9:35 AM, Oleg Bartunov o...@sai.msu.su wrote: What version of Pg you run ? Try latest version. Oleg On Sat, 29 Jan 2011, Matt Warner wrote: Reverse isn't a built-in Postgres function, so I found one and installed it. However, attempting to use

[GENERAL] Full Text Index Scanning

%') The reason I want to do this is that the partial word search does not involve dictionary words (it's scanning names). Is this something Postgres can do? Or is there a different way to do scan the index? TIA, Matt

[GENERAL] A few [Python] tools for postgres

and make somewhat intelligent suggestions. Any feedback is appreciated. Hopefully these tools are useful to others. I'll be at PgWest this week, if anyone wants to discuss these (or pgtune, or python, etc) cheers, matt http://panela.blog-city.com/ 0 - http://github.com/mattharrison/PgPartition 1

[GENERAL] Tools for partitioning and query optimization

and make somewhat intelligent suggestions. Any feedback is appreciated. Hopefully these tools are useful to others. cheers, matt 0 - http://github.com/mattharrison/PgPartition 1 - http://github.com/mattharrison/PgTweak

[GENERAL] postgres startup failure

? The memory messages seem suspicious to me... Thank you, Matt

Re: [GENERAL] postgres startup failure

Hi Tom, On Thu, May 20, 2010 at 11:33 AM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Bartolome mattxb...@gmail.com writes: I'm attempting to start postgres on a standby machine from a backup. Both the primary and standby are running postgres 8.4, fedora 12. 8.4.what exactly? I'm running

Re: [GENERAL] postgres startup failure

On Thu, May 20, 2010 at 2:44 PM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Bartolome mattxb...@gmail.com writes: gdb output... DEBUG: - DEBUG: invoking IpcMemoryCreate(size=32595968) DEBUG: max_safe_fds = 980, usable_fds = 1000, already_open = 10

Re: [GENERAL] postgres startup failure

On Thu, May 20, 2010 at 3:36 PM, Tom Lane t...@sss.pgh.pa.us wrote: Matt Bartolome mattxb...@gmail.com writes: Setting the breakpoint (b exit) got me a little farther... DEBUG: invoking IpcMemoryCreate(size=32595968) DEBUG: max_safe_fds = 980, usable_fds = 1000, already_open = 10

[GENERAL] parse tree in XML format

Is there some way to export the postgresql query parse tree in XML format? I can not locate the API/Tool etc to do that... thanks -Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

[GENERAL] stuck spinlock (0x2aac3678b0e0) detected at dynahash.c:876

8.3.8 (64-bit) on a dedicated Fedora Core 8 machine, in Amazon EC2. This was using an extra-large instance, which means 4 Xeon cores (2.66 GHz) and 15.5 GB of memory. Sincerely, Matt Solnit -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] stuck spinlock (0x2aac3678b0e0) detected at dynahash.c:876

because they use us in their benchmarks. ... I've had zero issues running postgres inside a domU. Granted, this was in 2006. -- Matt On Nov 20, 2009, at 9:54 AM, Merlin Moncure wrote: On Fri, Nov 20, 2009 at 12:15 PM, Matt Solnit msol...@soasta.com wrote: We are running PostgreSQL 8.3.8 (64

[GENERAL] Libpq binary mode SELECT ... WHERE ID IN ($1) Question

or documentation, it would be much appreciated. Thanks, Matt Sanchez The prepare code snippet: Oid oids[1] = { 23 }; //INT4OID result = PQprepare( pgconn, getname, select name from foo where id in ($1) 1, oids ); The execute code snippet: int ids[4] = { 3, 5, 6, 8 };// param

[GENERAL] Postgresql Web Hosting

Hi, I'm trying to migrate a site to a new hosting company. The backend uses postgresql 8 and php. Anyone have thoughts on decent hosting companies for this sort of thing? I'm just looking at shared hosting as this isn't a resource intensive site. Thanks, Matt -- Sent via pgsql-general mailing

Re: [GENERAL] enabling join_collapse_limit for a single query only

hoping to be able to set join_collapse_limit=1 *just* on the single query, as a kind of query hint, eg: /* !hint:join_collapse_limit=1 */ SELECT ... I take it this is this not possible in postgres? cheers, Matt h On 23/07/2009, at 09:50, Albe Laurenz wrote: groovefillet wrote: Is it possible

[GENERAL] queries on xmin

querying with a hash index and do an explicit join? have i missed the point entirely? many thanks, matt [1] http://wiki.openstreetmap.org/wiki/Planet.osm/diffs [2] http://wiki.openstreetmap.org/wiki/OsmChange [3] http://wiki.openstreetmap.org/wiki/OSM_Protocol_Version_0.6#Diff_upload:_POST_.2Fapi

Re: [GENERAL] queries on xmin

had fairly similar arguments with Matt already :-)  But having spent some time playing with it I can't find any reason why it won't work, and from a performance point of view I suspect it will win ... it seems right to me to use postgres' existing features to support this. we've got pretty close

Re: [GENERAL] queries on xmin

. it was looking at the skytools stuff which got me thinking about using txids in the first place. someone on the osm-dev list had suggested using PgQ, but we weren't keen on the schema changes that would have been necessary. cheers, matt -- Sent via pgsql-general mailing list (pgsql-general

Re: [GENERAL] queries on xmin

On Thu, Jun 11, 2009 at 2:48 PM, Marko Kreenmark...@gmail.com wrote: On 6/11/09, Matt Amos zerebub...@gmail.com wrote: On Thu, Jun 11, 2009 at 1:13 PM, Brett Hendersonbr...@bretth.com wrote:   See pgq.batch_event_sql() function in Skytools [2] for how to   query txids between snapshots

Re: [GENERAL] Out of memory on SELECT in 8.3.5

we can immediately run queries that were failing before the restart)... but then the cycle starts again. I just bring this up wondering if there is something possibly accumulating within Postgres that isn't getting freed and might cause an out-of-memory error like this in some way. Regards, Matt

Re: [GENERAL] Out of memory on SELECT in 8.3.5

box with kernel 2.6.18... must not exist for this ancient kernel :-) Regards, Matt -- 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] Out of memory on SELECT in 8.3.5

error message regarding this condition? Regards, Matt -- 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] Out of memory on SELECT in 8.3.5

checkout anyhow). No... nothing like this in syslog. Regards, Matt -- 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] Out of memory on SELECT in 8.3.5

So did the backend crashed on this one, or just produced 'out of memory ' message ? No crash, just the error message. -- m@ -- 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] Out of memory on SELECT in 8.3.5

of buffers/caches (based on free output).. Matt, can you provide the output from these: cat /proc/sys/vm/overcommit_memory cat /proc/sys/vm/overcommit_ratio cat /proc/meminfo Sure, here you go: [r...@170226-db7 ~]# cat /proc/sys/vm/overcommit_memory 2 [r...@170226-db7 ~]# cat /proc/sys/vm

Re: [GENERAL] Out of memory on SELECT in 8.3.5

blocks; 3744 free (0 chunks); 49840 used ErrorContext: 8192 total in 1 blocks; 8160 free (0 chunks); 32 used Does this provide any useful information? I have other queries that are failing as well, and I can provide explain output for those if it might help. Regards, Matt -- Sent via pgsql-general

Re: [GENERAL] Out of memory on SELECT in 8.3.5

* Matt Magoffin (postgresql@msqr.us) wrote: [r...@170226-db7 ~]# cat /proc/meminfo CommitLimit: 10312588 kB Committed_AS: 9760756 kB I suspect this may be it... Apparently, while you're only using about 2G, you've got 10G or so of outstanding commitments, and Linux is refusing

Re: [GENERAL] Out of memory on SELECT in 8.3.5

not terribly familiar with these VM parameters, so I apologize if I sound vague. Regards, Matt -- 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] Out of memory on SELECT in 8.3.5

with 100 concurrent postgres connections, if they all did something requiring large amounts of work_mem, you could allocate 100 * 125MB (I believe thats what you said it was set to?) which is like 12GB :-O in fact a single query thats doing multiple sorts of large datasets for a messy join

Re: [GENERAL] Out of memory on SELECT in 8.3.5

I'd do both. But only after I'd reduced work_mem. Given that reducing work_mem removed the problem, it looks to me like pgsql is requesting several large blocks of ram, then only using a small port of them. But overcommit set to 2 means that the OS will not allow an overcommit of memory to

Re: [GENERAL] Out of memory on SELECT in 8.3.5

-failing query to execute successfully. Do you think this is also what caused the out-of-memory error we saw today just when a transaction was initiated? Regards, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

Re: [GENERAL] Out of memory on SELECT in 8.3.5

Yes... and indeed changing vm.overcommit_ratio to 80 does allow that previously-failing query to execute successfully. Do you think this is also what caused the out-of-memory error we saw today just when a transaction was initiated? Curious, what's the explain analyze look like for that one?

Re: [GENERAL] Out of memory on SELECT in 8.3.5

still the 128MB of work_mem after changing the overcommit_ratio to 80. Regards, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Out of memory on SELECT in 8.3.5

We've been having persistent out-of-memory errors occur in our production 8.3 deployment, which is now running 8.3.5. I'm not sure the query here is the cause of the problem, but this is our most-recent example which triggered an out-of-memory error for us. Perhaps our configuration needs

Re: [GENERAL] Out of memory on SELECT in 8.3.5

20070626 (Red Hat 4.1.2-14) Regards, Matt -- 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] Out of memory on SELECT in 8.3.5

PostgreSQL 8.3.5 on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20070626 (Red Hat 4.1.2-14) Does the result from 'free' look reasonable on this box? I think so: total used free sharedbuffers cached Mem: 16432296 16273964 158332

Re: [GENERAL] Out of memory on SELECT in 8.3.5

with at the moment. Regards, Matt -- 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] Out of memory on SELECT in 8.3.5

these I could check do you think? Regards, Matt -- 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] Out of memory on SELECT in 8.3.5

executable, AMD x86-64, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6.9, not stripped Regards, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

Re: [GENERAL] Out of memory on SELECT in 8.3.5

2e299000-2aabafc41000 rw-s 00:08 0 /SYSV0063da81 (deleted) 7fff21fda000-7fff21fef000 rw-p 7fff21fda000 00:00 0 [stack] ff60-ffe0 ---p 00:00 0 [vdso] Hope this helps, Matt -- Sent via pgsql-general

Re: [GENERAL] Out of memory on SELECT in 8.3.5

locks (-x) unlimited which I think should accurately reflect what the postmaster environment should be seeing. Regards, Matt -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql

  1   2   3   >