Re: [GENERAL] A question on GIN indexes and arrays

Sorry, as a final follow up here, another option (should anyone run into this and want to keep the intarray extension) is to create the index using the gin__int_ops operator: CREATE INDEX ON sets USING GIN(obj_id gin__int_ops); On Sun, Aug 20, 2017 at 4:22 PM, Wells Oliver wrote: >

Re: [GENERAL] A question on GIN indexes and arrays

ff Janes wrote: > On Sun, Aug 20, 2017 at 1:28 PM, Wells Oliver > wrote: > >> >> Why is this happening and what can I do to get my GIN indexes working? >> Thanks! >> >> > What extensions do you have installed in each database? I bet one of them > (like inta

[GENERAL] A question on GIN indexes and arrays

: (obj_id @> '{2,3}'::integer[]) Rows Removed by Filter: 100999697 Planning time: 0.206 ms Execution time: 30015.883 ms Why is this happening and what can I do to get my GIN indexes working? Thanks! -- Wells Oliver wells.oli...@gmail.com

[GENERAL] A unique pairs version of UNNEST() ?

{2, 3} {2, 4} {2, 5} {3, 4} {3, 5} {4, 5} Any tips? Thanks! -- Wells Oliver wells.oli...@gmail.com

Re: [GENERAL] Options for complex materialized views sharing most of the same logic?

; (stupid smartphone-app, sorry for top-posting) > > Am 27. Dezember 2015 22:39:58 MEZ, schrieb Wells Oliver < > wells.oli...@gmail.com>: > > > >Is there some easier way for me to maintain the structure of the view > >without copying/pasting it 4 times and making one sma

[GENERAL] Options for complex materialized views sharing most of the same logic?

without copying/pasting it 4 times and making one small tweak? I find myself adding/removing columns to these views and I do it 4 times each time. Thanks! -- Wells Oliver wells.oli...@gmail.com

[GENERAL] Checking if a json-typed column contains a key

With the hstore you can do hstore ? 'key' to check if the object contains the key-- is there a similar function for json objects? (still on 9.3 so no jsonb) -- Wells Oliver wellsoli...@gmail.com

[GENERAL] Getting a delta of two json-typed objects? (a breeze w/ hstore)

I can't use jsonb, but if there's any input here I'd appreciate it. Thanks. -- Wells Oliver wellsoli...@gmail.com

Re: [GENERAL] Yosemite (OSX 10.0) problems with Postgresql

r an updater to do for any reason. Why > would it do that? > > -- > Craig Ringer http://www.2ndQuadrant.com/ > PostgreSQL Development, 24x7 Support, Training & Services > > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Wells Oliver wellsoli...@gmail.com

[GENERAL] 9.3 view / cross join / flat table solution

'published' or 'finalized'. What I don't get is an 'all' rollup. Which I could do with another flat table and a cross join on a table of report statuses, but I'd love to keep this is as a view if possible. Using 9.3, so I have the "latest and greatest". Wh

[GENERAL] Using 9.3 as a slave to 9.1 for upgrade purposes

As a way of upgrading, I'd like to setup a 9.3 cluster as a slave to a 9.1 master so that I can then promote that 9.3 instance to master, using streaming replication. Curious if this is a possible/advisable route. -- Wells Oliver wellsoli...@gmail.com

Re: [GENERAL] Grep'ing for a string in all functions in a schema?

This is the most helpful thing I've seen in months. Bravo. On Thu, Jan 30, 2014 at 12:52 PM, bricklen wrote: > > On Thu, Jan 30, 2014 at 12:45 PM, Wells Oliver wrote: > >> Since Postgres does not consider a table as a dependency of a function if >> that table is

[GENERAL] Grep'ing for a string in all functions in a schema?

asy way of essentially grep'ing all of the functions in a given schema for a string? Clearly if you had all of your functions in a nice VCS you could do this but alas I don't yet live in that perfect world. -- Wells Oliver wellsoli...@gmail.com

[GENERAL] Implicitly casting integer to bigint (9.1)

t_avg(bigint, bigint) does not exist Integer is definitely the right type to use for the underlying table. Do I really need to have an explicit cast to bigint in these views? Seems tedious. -- Wells Oliver wellsoli...@gmail.com

[GENERAL] Determining if an hstore is empty

#x27;::hstore - 'a=>1'::hstore), 1); select array_length(akeys('a=>1'::hstore - 'a=>1'::hstore), 1); select skeys('a=>1'::hstore - 'a=>1'::hstore) is null select 'a=>1'::hstore - 'a=>1'::hstore is null Etc. -- Wells Oliver wellsoli...@gmail.com

Re: [GENERAL] Calling ROUND w/o a numeric cast blowing up all connections (9.1)

Ah, bananas. Someone had created a round(double, integer) function in public that did some shenanigans. Now I've wasted everyone's time. Though, I do find it odd that it could cause such a crash, bad function or no. On Fri, Feb 8, 2013 at 5:13 PM, Tom Lane wrote: > Wells Oliver

[GENERAL] Calling ROUND w/o a numeric cast blowing up all connections (9.1)

1)' works fine. Version: PostgreSQL 9.1.7 on x86_64-unknown-linux-gnu, compiled by gcc-4.4.real (Ubuntu 4.4.3-4ubuntu5.1) 4.4.3, 64-bit Nothing shows up in the log. Have I broken my cast function? My round function? Have I angered the RDMS gods? Can anyone give me any pointers? -- W

Re: [GENERAL] Keeping historical record changes w/ triggers - best practices?

eferred, but it's nice to have both records. Will anyone tell me there's some terrible side effect of this approach that I am not realizing? On Wed, Feb 6, 2013 at 11:10 AM, Greg Donald wrote: > On Wed, Feb 6, 2013 at 12:41 PM, Wells Oliver > wrote: > > I have a wide

[GENERAL] Keeping historical record changes w/ triggers - best practices?

idea, probably a terrible idea, was to use hstore to create a list of the old values and new values, and have this history table just be the timestamp, action, and two hstore columns. Surely this has been done thousands of times. What are the thoughts regarding best practices in PG? Thanks everyo

[GENERAL] Performance of pl/pgsql functions?

plpgsql IMMUTABLE COST 100; The reason I'm doing this is because i repeat this formula in a bunch of views and queries, and it's easier to have one function. Would this somehow be slower than reproducing the formula in every view its used? I'm hoping not... -- Wells Oliver wellsoli...@gmail.com

[GENERAL] replication requires redundant rule in pga_hba?

the second line, I see a bunch of: FATAL: no pg_hba.conf entry for replication connection from host 10, user "replicationuser", SSL off Why is this? What am I missing? -- Wells Oliver wellsoli...@gmail.com

[GENERAL] Tables with lots of dead tuples despite autovacuum

do about this? Why isn't autovacuum cleaning these tables? Is this number of dead tuples acceptable? Lastly, would it make sense to do a weekly full manual vacuum + analyze? Thanks. -- Wells Oliver wellsoli...@gmail.com

[GENERAL] Why is my view making my disk churn? (iostat)

ut it seems drastic. This is not a high load environment and given that the view combines all of the tables via primary keys, I feel like this should be quicker. Is there some clue in the EXPLAIN output I am missing? The throttling of the disk causes other processes to queue up. Thanks! -- Wells Oliver wellsoli...@gmail.com

[GENERAL] Performance implications of numeric?

e and/or disk size implications. Would converting these columns to integer (or double precision on the handful that require the precision) and forcing developers to use explicit casting be worth the time? Thanks for any clarification. -- Wells Oliver wellsoli...@gmail.com

[GENERAL]

-- Wells Oliver wellsoli...@gmail.com

[GENERAL] Alternatives to very large tables with many performance-killing indicies?

idual to put 98 indexes on a table. Wondering what you folks have come across in terms of creative solutions that might be native to postgres. I can build something that indexes the data and caches it and runs separately from PG, but I wanted to exhaust all native options first. Thanks! -- Wells

[GENERAL] I'm in the depths of a CAST nightmare and I can't work my out

atible. Uncheck Implict, same error. The 'Function' drop-down list is empty. Can anyone help me clear this up? It's a very nagging issue to have my two servers in a different state.. -- Wells Oliver Architect, Baseball Systems 619-795-5359 San Diego Padres | 100 Park Boulevard | San

[GENERAL] Can't unsubscribe

Sorry to pester the list with this, but I've unsubscribed @ the web interface and I'm still getting email. Can an admin help me out here? - Wells -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-g

[GENERAL] Setting connection parameters via some kind of client configuration file @ the command line

Hello all- I am using psql from the command line in an Ubuntu environment, and I'd like to setup (if possible) some sort of client configuration for myself that sets some environment variables, mainly client_min_messages. Is there a way to do this? Thanks. -- Wells Oliver Developer, Bas

[GENERAL] n00b question: createdb seeming to fail quietly on new ubuntu 9.1 installation

resql-8.4-main.log indicate any issue. Secondly, adding a user seems to work, but I can't create the DB for the user (same issue as above). Any tips? Anything I can look at? This is a brand spanking new fresh 8.4 installation using the packages included in the 9.1 ubuntu packages. Thanks!