Re: [GENERAL] archive_command not being executed

postgres role: https://github.com/ANXS/postgresql/pull/254 Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Two versions of an extension in the same cluster?

teger AS 'example', 'myfunc_v2_0_0' LANGUAGE c IMMUTABLE; # example--3.0.0.sql CREATE OR REPLACE FUNCTION myfunc(anyarray) RETURNS integer AS 'example', 'myfunc_v3_0_0' LANGUAGE c IMMUTABLE; Thanks, Paul -- Sent via pgsql-gener

Re: [GENERAL] pg_dump throws too many command-line arguments in Postgres 10

name is "-U", your database is "admin", and everything after that is extra. Yours, Paul -- 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] Is float8 a reference type?

at are the bad things that can happen if I do `datums = (Datum *)floats`, as long as it's only when Datums are 8 bytes wide? Is there a platform with pass-by-val float8s where that won't work? Thanks, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To mak

Re: [GENERAL] Is float8 a reference type?

m expecting ~10 million elements per array, it seems like skipping the conversion will have a real effect. I checked the assembly and do see a difference (on both Mac+clang and Linux+gcc). Here is the Mac command line: platter:floatfile paul$ clang -Wall -Wmissing-prototypes -Wpointer-arith -W

Re: [GENERAL] Is float8 a reference type?

loc0(arrlen * sizeof(Datum)); for (i = 0; i < arrlen; i++) { datums[i] = Float8GetDatum(floats[i]); } } Thanks, Paul -- 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] Is float8 a reference type?

On Fri, Sep 22, 2017 at 7:52 PM, Paul A Jungwirth wrote: > Is a Datum always 8 bytes for 64-bit architectures? Never mind, I found this in `pg_config.h`: /* float8, int8, and related values are passed by value if 'true', by reference if 'false' */ #defi

[GENERAL] Is float8 a reference type?

cast)?: float8 *floats; Datum *datums; datums = palloc(arrlen * sizeof(Datum)); for (i = 0; i < arrlen; i++) { datums[i] = Float8GetDatum(floats[i]); } Thanks! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to y

Re: [GENERAL] Performance appending to an array column

regular files into Postgres arrays, sort of getting the best of both worlds. Anyway, thanks for sharing your experience! Yours, Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Performance appending to an array column

re a faster way to append to an array than just this?: UPDATE measurements SET vals = vals || ARRAY[5.0, 4.2, 9.9]::float[] ; Thanks! Paul -- 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] 10 beta 4 foreign table partition check constraint broken?

On Thu, Sep 21, 2017 at 02:59:21PM +0900, Michael Paquier wrote: /tmp/mutt-mayon-1000-26043-945be079d938129298 > On Fri, Sep 15, 2017 at 10:43 PM, Paul Jones wrote: > > Is this a bug in Postgres 10b4? Looks like neither partition ranges > > nor check constraints are honor

[GENERAL] 10 beta 4 foreign table partition check constraint broken?

Is this a bug in Postgres 10b4? Looks like neither partition ranges nor check constraints are honored in 10b4 when inserting into partitions that are foreign tables. Here is a nearly shovel-ready example. Just replace with your servers/passwords. -- -- -- Server 1 -- ---

[GENERAL] Strange SQL result - any ideas.

45,"paulie":"\sdfs\\\sfs\\gf"} {"mary":2,"jimmy":43,"paulie":"asfasfasfd"} {"mary":3,"jimmy":435,"paulie":"ererere"} (7 rows) Two problems with this result - one is that my square brackets are not in the r

Re: [GENERAL] PG and database encryption

ou can also use encrypted EBS volumes. You get a very similar effect, except all you need to do is tick a checkbox (or set a CloudFormation attribute, etc.). Also you can get unattended reboots without storing the key somewhere vulnerable. There may be perf advantages too; I'm not sur

Re: [GENERAL] Development of an extension for PostgreSQL and PostGIS

In order to get an LWGEOM from PostGIS you'll need to convert from the serialized form (GSERIALIZED) which you can read all about in the liblwgeom.h header. You'll be adding a hard dependency of course, but hopefully you're OK with that. If you're just hoping to build a compound type, as your exam

[GENERAL] Redacting JSONB

Is there some way to redact JSONB fields? This is essentially implementing "row-level" security on JSONB fields. Could possibly be done with a view or a function. MongoDB and MarkLogic both do this. PJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to yo

Re: [GENERAL] How to stop array_to_json from interpolating column names that weren't there

t;,"schema_name_address":"/pg_temp_1"}, {"catalog_name":"db1","schema_name":"pg_toast","schema_name_address":"/pg_toast"}, {"catalog_name":"db1","schema_name":"pg_toast_temp_1","schema_name_address":"/pg_toast_temp_1"}, {"catalog_name":"db1","schema_name":"public","schema_name_address":"/public"}] (1 row) Paul -- 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] ERROR: query returned no rows

On Mon, Jun 26, 2017 at 12:47 PM, Adrian Klaver wrote: > On 06/26/2017 12:03 PM, Paul Jungwirth wrote: >> Perhaps >> you should see what is line 85 when you do `\sf words_skip_game` (rather >> than line 85 in your own source code). > > Or easier yet: > > https://w

Re: [GENERAL] ERROR: query returned no rows

uld focus on those. That page also describes how to use `print_strict_params` to get a little more info about the details of the error. I hope that helps! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.

Re: [GENERAL] Left join help

ion has several submissions you will get duplicates. So maybe for complete/incomplete you should just use EXISTS with a correlated subquery. I hope that helps. Good luck! Paul -- 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 jsonb and data structures

On Wed, Jun 21, 2017 at 09:37:20AM -0700, Emilie Laffray wrote: > Date: Wed, 21 Jun 2017 09:37:20 -0700 > From: Emilie Laffray > To: Achilleas Mantzios > Cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] Question about jsonb and data structures > > Hello Achilleas, > > I fail to see how

Re: [GENERAL] Cookbook for doing installation and configuration of PostgreSQL on Redhat

resources, e.g. package 'postgresql-9.6' ... And then a step above that would be to use third-party cookbooks where available, and include their recipes in your runlist. I also think it is easier to use chef-solo when you are just starting out, if possible. That gives you a

Re: [GENERAL] Postgres Data Encryption Using LUKS with dm-crypt ?

corruption. I think this is really the right answer! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Upgrade method from 9.2 to 10 ?

Would it be possible to upgrade from 9.2 to 10 by doing a pg_basebackup to the new server, followed by pg_upgrade -k, then streaming replication from the 9.2 server to the 10 server until we're ready to cut over to 10? The idea is to minimize downtime. PJ -- Sent via pgsql-general mailing list

Re: [GENERAL] CREATE FOREIGN TABLE difficulties

manipulate the table definitions in each of the databases, rather make the definition once and let it propagate to the children as necessary, courtesy of the ‘INHERITS’. I’ll adjust my plans accordingly. Thanks for the reply! - Paul > On Jun 15, 2017, at 1:27 PM, Adrian Klaver wrote: >

[GENERAL] CREATE FOREIGN TABLE difficulties

the obvious, I’d thought I’d try here first. I’m going to go try the 10.0 beta now… Thanks! - Paul -- 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] trying to program in PostgreSQL C a statistics function

xfunc-c.html I'm happy to help more if you need it. I've found extending Postgres in this way to be a lot of fun and very effective at gaining performance. Good luck! Paul On 06/11/2017 09:57 AM, Andre Mikulec wrote: SUMMARY -- I am trying to program in PostgreSQL a statistics fu

Re: [GENERAL] type "xxxxxxx" does not exist

same problem next time. Paul -- 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] type "xxxxxxx" does not exist

eir schema. You could also do \dT+ foo.* to see all the types in schema foo. If you find that the type isn't in the public schema, try setting your schema search path so that the function can locate it, e.g.: SET search_path TO foo, public; Good luck! Paul -- Sent via pgsql-gener

Re: [GENERAL] Python versus Other Languages using PostgreSQL

ail&utm_content=webmail&utm_term=link> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> On Wed, May 10, 2017 at 7:37 AM, Adrian Klaver wrote: > On 05/09/2017 01:00 PM, Paul Hughes wrote: > >> Thank you all for taking the time to answer my questions. I've been out >> o

Re: [GENERAL] Python versus Other Languages using PostgreSQL

ww.avast.com <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=link> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> On Tue, May 9, 2017 at 7:54 AM, George Neuner wrote: > On Mon, 8 May 2017 14:26:02 -0700, Paul

Re: [GENERAL] Python versus Other Languages using PostgreSQL

rried, in the same way that Node.js is largely married to MondogDB? On Mon, May 8, 2017 at 3:50 PM, Adrian Klaver wrote: > On 05/08/2017 02:26 PM, Paul Hughes wrote: > >> Hello, >> >> I noticed that most of the largest web platforms that use PostgreSQL as >> their p

[GENERAL] Python versus Other Languages using PostgreSQL

made to work seamlessly to take advantage of the superior performance of HHVM or Node.js? Thank you in advance! ~Paul <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail&utm_term=icon> Virus-free. www.avast.com <h

[GENERAL] Partitioning and Table Inheritance

uture versions? (I might be willing to take a stab at a patch for it.) Btw this is on 9.4 but it could be 9.5 or 9.6 if that would help any. Thanks, Paul -- 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] Are multiple array_aggs going to be in the same order?

Seq Scan on expense_categories c (cost=0.00..1.16 rows=16 width=4) -> Sort (cost=221.96..228.78 rows=2728 width=30) Sort Key: e.expense_category_id -> Seq Scan on expenses e (cost=0.00..66.28 rows=2728 width=30) (9 rows) Paul -- Sent via pgs

Re: [GENERAL] Are multiple array_aggs going to be in the same order?

-sorted case would happen a lot, so I'm curious if Postgres pays a performance cost there? Thanks, Paul -- 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] Postgres Permissions Article

d columns. Maybe it would be worth clarifying there that you need to *own* the referencing table, and you need REFERENCES on the referenced table? In any case, thanks again to you all for your help figuring this out! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org)

Re: [GENERAL] Postgres Permissions Article

metimes by asking "why" you learn new things. I really appreciate your offering your thoughts! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Postgres Permissions Article

w so I can correct them. I also shared a few opinions amidst the facts (like that `USAGE` for schemas doesn't add much), so I am very pleased to have those challenged. You can consider them my own outstanding questions. I'd be especially grateful for any feedback there. Yours, Paul

Re: [GENERAL] COPY ... FROM stdin WITH FORMAT csv

AT), like this: db=> copy (select 1+1, 2+2) to stdout with csv; 2,4 (tested on pg 9.5) I never use parens, so I was surprised to see in the docs and the replies here that they are necessary. Am I just exploiting a bug in the parser? Paul -- Sent via pgsql-general mailing li

Re: [GENERAL] appropriate column for storing ipv4 address

maybe there is already some built-in way to treat cidr columns like ranges? Paul -- 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] json aggregation question

gt;'tag2'?'t2val1' ) x; You are looking to get always one result, right? Yours, Paul -- 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] Running out of memory the hard way ...

On Wed, Feb 8, 2017 at 7:44 AM, Tom Lane wrote: > Albe Laurenz writes: > > Bill Moran wrote: > >> What I feel is the best way to mitigate the situation, is to have some > >> setting that limits the maximum RAM any backend can consume. > > > I'd delegate that problem to the operating system which

[GENERAL] Can we not give tyrannical pedants control of #postgresql?

Hi all! It's really a bad sign when some user is given operator status who is intolerant to minor offtopic conversations that span no more than a couple of line buffers. Witnessing a user getting kicked for asking for my location was way beyond reasonable, considering even the channel was rath

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

lt, > you'll have to reduce the number of row in your index, > that is partition your table into several tables ! > > This is not easy to do with current postgres partitionning methods as far > as I know > (partitionning is easy, automatic efficient query is hard). > > An

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

gt; On Jan 5, 2017, at 10:38 AM, Paul Ramsey > wrote: > > Yes, you did. You want a query that spits out a tupleset of goemetries > (one each for each wee segment), and then you can join that set to your > main table using st_dwithin() as the join clause. > So start by ditching

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

Thu, Jan 5, 2017 at 11:36 AM, Israel Brewster wrote: > On Jan 5, 2017, at 8:50 AM, Paul Ramsey wrote: > > > The index filters using bounding boxes. A long, diagonal route will have > a large bounding box, relative to the area you actually care about (within > a narrow strip

Re: [GENERAL] Improve PostGIS performance with 62 million rows?

The index filters using bounding boxes. A long, diagonal route will have a large bounding box, relative to the area you actually care about (within a narrow strip of the route). Use ST_Segmentize() to add points to your route, ST_DumpPoints() to dump those out as point and ST_MakeLine to generate

Re: [GENERAL] Index impact on update?

You'd be better off forcing the table to write in bulk with something like CREATE TABLE mynewtable AS SELECT *, geography(ST_SetSRID(ST_MakePoint(lng, lat), 4326)) AS geog FROM myoldtable; Then index the new table, rename, etc. Bulk update will, in addition to being slow, use 2x the amount of spa

Re: [GENERAL] Extensions and privileges in public schema

When you create the student user, remove their create privs in public. Then create a scratch schema and grant them privs there. Finally, alter the student user so that the scratch schema appears FIRST in their search path. This will cause unqualified CREATE statements to create in the scratch schem

[GENERAL] Avoiding double-counting in aggregates with more than one join?

e some different way of doing things I haven't thought of yet? Also I wonder about the performance merging all these subqueries together. Would the final merging be any faster if I had an ORDER BY in each CTE? It seems like this pattern comes up a lot; what have others done about it? Thanks,

Re: [GENERAL] Multi tenancy : schema vs databases

o there's no solution there. Good luck! Paul -- 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] Time travel?

-between-mtime-ctime-and-atime I hope that explains it! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Stats update difference between VACUUM ANALYZE and ANALYZE in 9.2?

er an automatic analyze for that table. If that table is rarely inserted into or updated, the inheritance statistics will not be up to date unless you run ANALYZE manually." Can anyone explain what's going on here? Thanks, Paul Jones -- Sent via pgsql-general m

Re: [GENERAL] journaling / time travel

> On 09/19/2016 10:56 AM, Willy-Bas Loos wrote: > > On Mon, Sep 19, 2016 at 6:26 PM, Paul Jungwirth > > mailto:p...@illuminatedcomputing.com>> wrote: > > I've worked on similar > > projects that maintain history for regulatory reasons. > Can yo

Re: [GENERAL] journaling / time travel

poral is where you really cross over into lack of available tools, outside of a few commercial offerings. (Teradata has temporal support, using a Snodgrass-like approach that pre-dates the standard.) Paul -- 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] GIN Indexes: Extensibility

; ordering of arbitrary JSON objects is. FWIW, Postgres arrays do sort in the way he's expecting: paul=# create table t (id integer, v integer[]); CREATE TABLE paul=# insert into t values (1, array[2014]), (2, array[2014, 1]), (3, array[2016]); INSERT 0 3 paul=# select * from t order b

Re: [GENERAL] migrating data from an old postgres version

> On Jul 15, 2016, at 6:55 AM, Melvin Davidson wrote: > > > > On Fri, Jul 15, 2016 at 9:46 AM, Willy-Bas Loos > wrote: > Hi, > > A coworker is getting a new laptop and he wants to migrate some data from his > old one to the new one. So he installed postgres 9.5 on

[GENERAL] RHEL 7

I am currently running Version 9.3.10 of postgres with RHEL 6. I am going to upgrade my O/S soon to RHEL 7. Do I need to upgrade to version 9.4.x of postgres? Paul Tilles

Re: [GENERAL] Update statement results in Out of memory

Running a multi-million row update will take a long time. It's possible you've exposed a memory leak in ST_Buffer (the older your version of GEOS, the more likely that is) but it's also possible you're just running a really long update. I find for batch processing purposes that creating fresh table

Re: [GENERAL] Possible to create canonicalized range type without being superuser?

On Tue, Jul 5, 2016 at 10:17 PM, Paul A Jungwirth wrote: > db=> create type inetrange; Here is a follow-up question for creating inet ranges. Is there any way to prevent someone from doing this?: db=> select inetrange('1.2.3.4', '2001:0db8::

Re: [GENERAL] Possible to create canonicalized range type without being superuser?

On Tue, Jul 5, 2016 at 3:28 PM, David G. Johnston wrote: > On Tue, Jul 5, 2016 at 5:37 PM, Tom Lane wrote: >> Paul Jungwirth writes: >> > The problem is this (tried on 9.3 and 9.5): >> >> The only other obvious way to deal with this is to allow the canonical >&

[GENERAL] Possible to create canonicalized range type without being superuser?

type. The problem is this (tried on 9.3 and 9.5): db=> create type inetrange; ERROR: must be superuser to create a base type So I'm wondering whether there is any way around this circle without being a superuser? Thanks! Paul -- Sent via pgsql-general mailing list (pgsql-general@pos

Re: [GENERAL] pg_dump fundenental question

> a good point, but I would prefer NOT to open a 324GB backup file in a text > editor. I can however cat/less/head/tail the file in Linux. Use vi (or flavour thereof) - it doesn't load the entire file in order to read the contents of lines 1 - 100 (say). Paul... -- Sent via pg

Re: [GENERAL] UUID and Enum columns in exclusion constraints

.html I would love to rebase that to the current code and re-submit. Maybe this weekend. :-) Paul -- 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] PostgresSQL and HIPAA compliance

nds on the rest of your stack. Yours, Paul -- 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] Thoughts on "Love Your Database"

On 05/04/2016 08:39 AM, Paul Jungwirth wrote: On 05/03/2016 09:11 PM, Guyren Howe wrote: I think I'm going to write a book called Love Your Database, aimed at web developers I gave a talk here about doing "interesting" Postgres things in Rails: Oh also: one part of my talk

Re: [GENERAL] Thoughts on "Love Your Database"

there ways I can use SQL (and PLSQL) without giving up Rails? How will I maintain that stuff? Does it play nice with Rails database migrations? How will I write tests for it? How do I debug it? What principles will help me draw the line between (PL)SQL and Ruby? Good luck! I think a book like that

Re: [GENERAL] Proper relational database?

orial D seems like a good place to start if SQL isn't relational enough for you. The book I mentioned is short and easy to read. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Question about shared_buffer cache behavior

In Postgres 9.5.1 with a shared_buffer cache of 7Gb, a SELECT from a single table that uses an index appears to read the table into the shared_buffer cache. Then, as many times as the exact same SELECT is repeated in the same session, it runs blazingly fast and doesn't even touch the disk. All go

[GENERAL] MongoDB 3.2 beating Postgres 9.5.1?

I have been running the EDB benchmark that compares Postgres and MongoDB. I believe EDB ran it against PG 9.4 and Mongo 2.6. I am running it against PG 9.5.1 and Mongo 3.2 with WiredTiger storage using 10,000,000 JSON records generated by the benchmark. It looks like Mongo is winning, and apparen

[GENERAL] Anyone compare PG 9.5 and MongoDB 3.2?

MongoDB has released 3.2 with their WiredTiger storage. Has anyone benchmarked 9.5 against it, and for JSONB elements several MB in size? PJ -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-gene

Re: [GENERAL] Ubuntu and Rails postgresql setup

On 02/24/2016 09:44 AM, Paul Jungwirth wrote: Also, Rails wants to use Postgres "ident" authentication, which does not require a password because it trusts that the OS has already authenticated you. Sorry, I misspoke: this is "peer" authentication, not "ident". S

Re: [GENERAL] Ubuntu and Rails postgresql setup

not set DATABASE_URL. > Finally I am wondering whether the choice of installing PostgreSQL > through the PostgreSQL apt repository would be safe enough or it would > be preferable to install the LTS version of Ubuntu. Either is fine. Good luck! Paul -- Sent via pgsql-general mailin

Re: [GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and python3 present

On Mon, Jan 11, 2016 at 06:23:06PM -0500, Tom Lane wrote: > Date: Mon, 11 Jan 2016 18:23:06 -0500 > From: Tom Lane > To: Paul Jones > cc: pgsql-general@postgresql.org > Subject: Re: [GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and > python3 present > > >

Re: [GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and python3 present

On Mon, Jan 11, 2016 at 10:04:16AM -0500, Tom Lane wrote: /tmp/mutt-mayon-1000-19386-284b6a00794950f414 > Paul Jones writes: > > pg_upgrade complains about not being able to find $libdir/plpython3 > > when upgrading a 9.4 cluster that has both python2 and python3 used. > >

[GENERAL] 9.4 -> 9.5 upgrade problem when both python2 and python3 present

pg_upgrade complains about not being able to find $libdir/plpython3 when upgrading a 9.4 cluster that has both python2 and python3 used. Both the 9.4 and 9.5 PGs have been built from source with python2/3 in the recommended way and the plpython3.so is present in /usr/local/pgsql/lib. I dropped the

[GENERAL] ERROR: could not read block 3 in file "base/12511/12270"

I have been having disk errors that have corrupted something in my postgres database. Other databases work ok: Running on Ubuntu 10.04. paul@kitanglad:~$ psql -U postgres psql (9.4.5) Type "help" for help. postgres=# SELECT pg_catalog.pg_is_in_recovery(); ERROR: could not read block

Re: [GENERAL] Trigger function, C, lookup of attribute type

I found the solution: SPI_gettype() does the job fine. I was led to that by rummaging through the slony source code to see how they handle the triggers in C. -- Paul Nicholson -- -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] Trigger function, C, lookup of attribute type

ypes. Do I have to query the system catalogs? Or maybe there's a function that works from a TupleDesc? -- Paul Nicholson -- -- 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] postgres sometimes returns no data

many open connections? Possibly some hanging around longer than they should? Paul -- 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] pg_archivecleanup not deleting anything?

So something is doing a base backup roughly every two hours. Is that what you would expect? No. :-) Sounds like I need to do some archeology. This is a system I inherited, so I haven't yet explored all the dark corners. Paul -- Sent via pgsql-general mailing list (pgsql-ge

Re: [GENERAL] pg_archivecleanup not deleting anything?

On 11/02/2015 09:11 AM, Adrian Klaver wrote: The *.backup files should not be 16MB and from your original post they looked to be 300 bytes. Now if you have 30K of 16MB files then something else is going on. Ah, you are right! Sorry for the misunderstanding. Paul -- Sent via pgsql-general

Re: [GENERAL] pg_archivecleanup not deleting anything?

7;s not a misconfiguration on our side. I guess we should just make sure we remove older *.backup files by some other means, because thousands of 16MB files add up eventually. We had more than 30k of them! Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make chang

Re: [GENERAL] pg_archivecleanup not deleting anything?

Is there anything else beside *.backup files in the directory? There were a few *.history files, and a few files with no extension, like this: 000600BE0040. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] pg_archivecleanup not deleting anything?

ssage, and the same files remain. Does anyone have any idea why pg_archivecleanup isn't deleting anything? Thanks, Paul -- 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] Left Join with Limit 1

ing in that subquery so that the results are deterministic. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PostGIS 2.2.0 Released

http://postgis.net/2015/10/07/postgis-2.2.0 PostGIS 2.2.0 is released! Over the last two years a number of interesting new features have been added, such as: * True nearest-neighbor searching for all geometry and geography types * New volumetric geometry support, including ST_3DDifference, ST_3D

Re: [GENERAL] relpages for pg_toast.* tables

P. On Fri, Aug 28, 2015 at 6:16 AM, Paul Ramsey wrote: > I've been trying to write up a "pg_total_relation_size()" replacement > that just uses the system tables (sum up relpages for tables, indexes > and toast tables), thereby avoiding the overhead of running stat() on &

[GENERAL] relpages for pg_toast.* tables

I've been trying to write up a "pg_total_relation_size()" replacement that just uses the system tables (sum up relpages for tables, indexes and toast tables), thereby avoiding the overhead of running stat() on thousands of filesystem tables, but I've come up against what seems to be an unsuperable

Re: [GENERAL] Average New Users Per DOW

Thanks Paul, I guess I'm not sure how a generate_series between 0 to 6 would solve this problem. Wouldn't I have to generate a series based on the date range (by day) and then group by DOW _after_ that? Can you give me an example of how I'd do it with a series based on 0 to 6? L

Re: [GENERAL] Average New Users Per DOW

data. My go-to solution is to use generate_series---in your case from 0 to 6---then do a left join from there to your actual data. Paul -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Less is More

Hey all, I have an odd performance quirk I wonder if anyone has any theories for… (not a one-time result, table all heated up, etc) spain=# explain analyze select way from planet_osm_point;                                                         QUERY PLAN --

Re: [GENERAL] date with month and year

ank you for pointing that out. I'll have to think some more about when I'd want that behavior. Paul -- 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] date with month and year

ll keep an eye out for when WITH might be handy. And maybe I'll do some research to see how well Rails would handle those columns. Thanks again for your generosity! Yours, Paul -- 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] date with month and year

ve written about that approach in a Rails context here: http://illuminatedcomputing.com/posts/2014/04/timezones/ I find that this helps me to ignore time zones in most parts of my application and cut down on my timezone-related bugs. Thanks! Paul -- Sent via pgsql-general mailing list (pg

Re: [GENERAL] date with month and year

, so you can still use date functions to manipulate them. Paul -- 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] Success story full text search

e author! Paul -- 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] On using doubles as primary keys

1? Even if some metadata is not, maybe you can finesse it with hstore/arrays. Good luck! Paul

Re: [GENERAL] ERROR: could not access status of transaction 283479860

, Alvaro Herrera wrote: > Pweaver (Paul Weaver) wrote: > > We started getting the following error on some transactions on our > database > > (when against particular rows run against the table). > > > > > > PGError: ERROR: could not access status of transaction

  1   2   3   4   5   6   7   >