Re: [GENERAL] Imperative Query Languages

2017-07-11 Thread Jason Dusek
They said it couldn't be done... dandl schrieb am Di. 11. Juli 2017 um 06:58: > From: pgsql-general-ow...@postgresql.org [mailto: > pgsql-general-ow...@postgresql.org] On Behalf Of Merlin Moncure > > > It's probably of broader interest to consider some sort of "more > relational" > > language tha

Re: [GENERAL] Imperative Query Languages

2017-07-05 Thread Jason Dusek
this way, I thought I’d write to ask you all if you had seen any. Kind Regards, Jason ​

Re: [GENERAL] Imperative Query Languages

2017-07-05 Thread Jason Dusek
> What are some alternative interpretations of this query? Are you referring to which rows are candidates for locking? Or the order of locking? Kind Regards, Jason ​

Re: [GENERAL] Imperative Query Languages

2017-07-04 Thread Jason Dusek
; Declarative Query -> Imperative Plan Fortress is rather the same, since it translates imperative to functional to assembly. Kind Regards, Jason

Re: [GENERAL] Imperative Query Languages

2017-07-04 Thread Jason Dusek
s a more recent and widespread example -- though not encompassing an entire language -- of something that has an imperative form while being declarative under the hood. Scala's for comprehensions -- more or less monad comprehensions --are another. With regards to Spark, I assume for comprehensions are an important part of the interface? Kind Regards, Jason >

[GENERAL] Imperative Query Languages

2017-07-04 Thread Jason Dusek
rite_drink FROM employee JOIN department USING (department) WHERE department.name == "infosec" The only language I can think of that is vaguely like this is Fortress, in that it attempts to emulate pseudocode and Fortran very closely while being fundamentally a dataflow language. Kind Regards, Jason ​

[GENERAL] Large empty table, balanced INSERTs and DELETEs, not being vacuumed

2016-10-21 Thread Jason Dusek
? Kind Regards, Jason Dusek ​

Re: [GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

2016-10-11 Thread Jason Dusek
tion_deferrable │ on default_transaction_isolation │ serializable ​ On Tue, 11 Oct 2016 at 13:00 Kevin Grittner wrote: > On Tue, Oct 11, 2016 at 2:29 PM, Jason Dusek > wrote: > > > I notice the following oddity: > > > =# CREATE TABLE with_pk (i integer PRIMARY KEY); > > CREATE TA

[GENERAL] SERIALIZABLE and INSERTs with multiple VALUES

2016-10-11 Thread Jason Dusek
=# INSERT INTO with_pk VALUES (2), (2) ON CONFLICT DO NOTHING; ERROR: could not serialize access due to concurrent update =# END;ROLLBACK How are these two transactions different? Kind Regards, Jason Dusek ​

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-30 Thread Jason Dusek
On Thu, 28 Jul 2016 at 01:18 Gavin Flower wrote: > On 28/07/16 17:52, Jason Dusek wrote: > > With regards to write amplification, it makes me think about about > > OIDs. Used to be, every row had an OID and that OID persisted across > > row versions. > > > > >

Re: [GENERAL] Uber migrated from Postgres to MySQL

2016-07-27 Thread Jason Dusek
With regards to write amplification, it makes me think about about OIDs. Used to be, every row had an OID and that OID persisted across row versions. https://www.postgresql.org/docs/9.5/static/runtime-config-compatible.html#GUC-DEFAULT-WITH-OIDS Would reintroducing such a feature address some of

[GENERAL] client_min_messages and INFO

2016-06-22 Thread Jason Dusek
reverse of LOG? Kind Regards, Jason Dusek

Re: [GENERAL] HeapTuple to JSON -- composite_to_json()?

2016-01-21 Thread Jason Dusek
heap_copy_tuple_as_datum looks promising… http://doxygen.postgresql.org/heaptuple_8c.html#abfa9096cd7909cb17a6acfdc7b31b7ad

[GENERAL] HeapTuple to JSON -- composite_to_json()?

2016-01-21 Thread Jason Dusek
to convert a HeapTuple to a Datum? Best Regards, Jason Dusek ​

Re: [GENERAL] How to search a string inside a json structure

2015-11-02 Thread Jason O'Donnell
> > I guess postgre should traverse though each json structures while finding > the string. > > Thanks > -- Jason O'Donnell Crunchy Data Solutions

Re: [GENERAL] Hierarchical Query Question (PHP)

2015-10-30 Thread Jason O'Donnell
ia' --<< substitute me > union all > select m.taxon, m.parent_id > from hier, gz_life_mammals m > where m.parent=hier.taxon > ) > select tax_rank(parent_id), >count(*) num_of_desc > from hier > where parent_id is not null > group by parent_id > order by parent_id;"; > > Thanks. > > -- Jason O'Donnell Crunchy Data Solutions

[GENERAL] There can be only one

2015-10-11 Thread Jason Dusek
Consider a table of providers, for which one is the default. For example, payment providers: CREATE TABLE payment_via ( iduuid PRIMARY KEY, provider text NOT NULL, keys hstore NOT NULL DEFAULT '' ); Here we store together the name of the provider — medici, paypal —

Re: [GENERAL] Joins between foreign tables

2015-06-08 Thread Jason Dusek
p); It would stand to reason that the IN list could be pushed down to the foreign server; but this query’s performance is no better than the direct join between table_on_server1 and table_on_server2. Best Regards, Jason Dusek ​

[GENERAL] Joins between foreign tables

2015-06-08 Thread Jason Dusek
in general, either.) Best Regards, Jason Dusek ​

Re: [GENERAL] regclass and format('%I')

2015-03-19 Thread Jason Dusek
o determine whether or not to quote. Introducing the behave I describe in an intuitive way would require some kind of type-specific handling in format(). I'm not sure what the cost of this is to the project, but David makes the very reasonable point that imposing the burden of choosing between `%s` and `%I` opens up the possibility of confusing vulnerabilities. Kind Regards, Jason Dusek

Re: [GENERAL] regclass and format('%I')

2015-03-14 Thread Jason Dusek
On 14 March 2015 at 09:17, David G. Johnston wrote: > On Saturday, March 14, 2015, Jason Dusek wrote: >> It honestly seems far more reasonable to me that %s and %I should do >> the exact same thing with regclass. My reasoning is as follows: >> >> ‘%I’ formats a someth

Re: [GENERAL] regclass and format('%I')

2015-03-14 Thread Jason Dusek
‘%I’ should do nothing. On 13 March 2015 at 12:42, David G. Johnston wrote: > On Fri, Mar 13, 2015 at 12:18 PM, Jason Dusek wrote: >> >> Hi All, >> >> The difference in how format handles `regclass` and `name` seems like an >> inconsistency: >> >>

[GENERAL] regclass and format('%I')

2015-03-13 Thread Jason Dusek
| %I | "select" {name} | %L | 'select' {name} | %s | select {regclass} | %I | """select""" {regclass} | %L | '"select"' {regclass} | %

[GENERAL] unique index on embedded json object

2014-09-21 Thread Lee Jason
ucts tables because sku is not unique in this case. I tried to create a unique index like below, but it failed. CREATE UNIQUE INDEX product_sku_index ON products( (data->'bags'->'sku') ) Any suggestions, please? Thank you. Thanks, Jason

Re: [GENERAL] Overlapping ranges

2014-06-18 Thread Jason Long
On Wed, 2014-06-18 at 18:08 -0600, Rob Sargent wrote: > On 06/18/2014 05:47 PM, Jason Long wrote: > > > > I have a large table of access logs to an application. > > > > I want is to find all rows that overlap startdate and enddate with any > > other rows.

[GENERAL] Overlapping ranges

2014-06-18 Thread Jason Long
I have a large table of access logs to an application. I want is to find all rows that overlap startdate and enddate with any other rows. The query below seems to work, but does not finish unless I specify a single id. select distinct a1.id from t_access a1, t_access a2 where tstzr

Re: [GENERAL] pg_largeobject high overhead

2014-06-04 Thread Jason Newton
ever parameters needed being runtime specifiable to make this more efficient / higher utilization. I may experiment with some custom builds in the future just to see how it goes. -Jason

[GENERAL] pg_largeobject high overhead

2014-06-04 Thread Jason Newton
deal with fallout if any. I'd appreciate any insights, -Jason

[GENERAL] pg_largeobject high overhead

2014-06-04 Thread Jason Newton
deal with fallout if any. I'd appreciate any insights, -Jason

Re: [GENERAL] Log file monitoring and event notification

2014-04-05 Thread Antman, Jason (CMG-Atlanta)
ething in postgres that sends detailed, well-structured log messages to a message queue than put the alerting logic in it (syslog works with everything, but it's so horribly obsolete). -Jason On 04/05/14 11:47, Andy Colson wrote: > Hi All. > > I've started using replication, an

Re: [GENERAL] High Level Committers Wanted

2014-03-14 Thread Antman, Jason (CMG-Atlanta)
On 03/12/2014 01:13 PM, bobJobS wrote: > Understood and I agree. > > I will check to see how much contact detail I can divulge in an open forum. > > > > -- > View this message in context: > http://postgresql.1045698.n5.nabble.com/High-Level-Committers-Wanted-tp5795695p

Re: [GENERAL] 'tuple concurrently updated' error when granting permissions

2014-03-03 Thread Jason Daly
why we took the approach we did. Now I have discovered 'ALTER DEFAULT PRIVILEGES' which will allow me to remove these concurrent permission grants - I only need to exec one ALTER DEFAULT PRIVILEGES command when the schema is created. Much obliged, Jason On Sun, Mar 2, 2014 at 6:09 AM, T

[GENERAL] 'tuple concurrently updated' error when granting permissions

2014-02-28 Thread Jason Daly
multiple concurrent grants to a role. We're using the latest version of postgresql (9.3) in the context of an AWS RDS. Should I enter a bug report, or can someone convince me that we should single-thread this part of our app (or work around it somehow)? Please respond to jasond...@trimblegeospatial.com -Jason

[GENERAL] Queries with more than 1692 characters hang

2014-02-26 Thread Jason McKellar
n querying this specific server. The query I'm making is "select 'll';" where "l" is repeated to make the query 1692 characters long including the spaces and keywords before the semi-colon. Any idea what happened? Thanks, Jason

Re: [GENERAL] File system level backup of shut down standby does not work?

2014-02-19 Thread Antman, Jason (CMG-Atlanta)
re that we use an intermediate host for this. i.e. we stop a slave, copy PGDATA *and* ALL WAL files over to another machine, start the slave back up, and then do the recovery to a consistent state there, to end up with a standalone PGDATA directory that can be copied wherever we need it wi

Re: [GENERAL] File system level backup of shut down standby does not work?

2014-02-18 Thread Antman, Jason (CMG-Atlanta)
", then something's amiss and the backup won't be clean (error in shutdown, etc.) 5) `sync` 6) now take the backup -Jason On 02/17/2014 08:32 AM, Jürgen Fuchsberger wrote: > Hi all, > > I have a master-slave configuration running the master with WAL > archiving enable

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-15 Thread Antman, Jason (CMG-Atlanta)
On 02/15/2014 04:55 PM, Antman, Jason (CMG-Atlanta) wrote: > On 02/15/2014 02:00 PM, Francisco Olarte wrote: >> >> If I NEEDED to be able to provide 100-150 snapshots to test/dev >> environments 20% of which maybe active, I'll setup a cluster, buy >> somewhere above

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-15 Thread Antman, Jason (CMG-Atlanta)
On 02/15/2014 05:27 PM, Karsten Hilbert wrote: > On Sat, Feb 15, 2014 at 10:17:05PM +, Antman, Jason (CMG-Atlanta) wrote: > >> [...] I see how my original "brilliant" idea >> (multiple DBs per postgres instance on one host, [...]) is insane, >> without some sp

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-15 Thread Antman, Jason (CMG-Atlanta)
of documentation or best practices for running multiple postgres instances on the same host, and how to maximize resource sharing between them? Thanks, Jason On 02/15/2014 04:26 PM, Adrian Klaver wrote: > On 02/15/2014 10:31 AM, Antman, Jason (CMG-Atlanta) wrote: >> Well thanks fo

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-15 Thread Antman, Jason (CMG-Atlanta)
On 02/15/2014 02:00 PM, Francisco Olarte wrote: > Hi: > > On Sat, Feb 15, 2014 at 7:31 PM, Antman, Jason (CMG-Atlanta) > wrote: >> Well thanks for someone at least sending a reply, though I suppose I >> should have asked "how do I do this", or "what are the

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-15 Thread Antman, Jason (CMG-Atlanta)
On 02/15/2014 01:22 PM, John R Pierce wrote: > On 2/15/2014 10:15 AM, Antman, Jason (CMG-Atlanta) wrote: >> I also asked this question on dba.stackexchange.com, where it >> received a very detailed enumeration of the associated problems from >> Craig Ringer: >> h

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-15 Thread Antman, Jason (CMG-Atlanta)
postgres supports balooning of memory? Thanks, Jason On 02/15/2014 01:20 PM, Tom Lane wrote: > "Antman, Jason (CMG-Atlanta)" writes: >> Perhaps there's a postgres internals expert around, someone intimitely >> familiar with pg_xlog/pg_clog/pg_control, who can comment o

Re: [GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-15 Thread Antman, Jason (CMG-Atlanta)
databases? Thanks, Jason On 02/13/2014 04:41 PM, Antman, Jason (CMG-Atlanta) wrote: I have a bunch of test/development databases which we currently refresh with production data as-needed using a NetApp filer's snapshot capabilities - we have a production slave with its datadir on a filer m

[GENERAL] Restore postgresql data directory to tablespace on new host? Or swap tablespaces?

2014-02-13 Thread Antman, Jason (CMG-Atlanta)
omething obvious in the documentation that I missed, but I'm not much of a database guru, and am relatively new to pgsql in general. Jason Antman PS - The slightly-less-abstract explanation: We're spinning up nearly 100 (maybe 150) new test environments. We simply don't have th

[GENERAL] ON DELETE CASCADE Question

2013-11-04 Thread Jason Long
I would like for corresponding records in t_a to be deleted when I delete a record from t_b. This deletes from t_b when I delete from t_a, but not the other way around. I am unable to create a foreign key constraint on t_a because this table holds records from several other tables. I added a simp

Re: [GENERAL] Problem with left join when moving a column to another table

2013-06-20 Thread Jason Long
On Thu, 2013-06-20 at 16:22 -0700, David Johnston wrote: > Jason Long-2 wrote > >> Jason Long-2 wrote > > > > > > There is a unique constraint on the real price table. I hadn't thought > > of how I will enforce the constraint across two tables

Re: [GENERAL] Re: Easiest way to compare the results of two queries row by row and column by column

2013-06-20 Thread Jason Long
Thank you. I will give it a try. I have never used WITH before. Thank you for the tips. On Thu, 2013-06-20 at 16:05 -0700, David Johnston wrote: > Jason Long-2 wrote > > Can someone suggest the easiest way to compare the results from two > > queries to make sure the

Re: [GENERAL] Problem with left join when moving a column to another table

2013-06-20 Thread Jason Long
On Thu, 2013-06-20 at 15:37 -0700, David Johnston wrote: > Jason Long-2 wrote > > David, > > > > Thank you very much for your response. > > Below is a script that will reproduce the problem with comments > > included. > > > > > > &

[GENERAL] Easiest way to compare the results of two queries row by row and column by column

2013-06-20 Thread Jason Long
Can someone suggest the easiest way to compare the results from two queries to make sure they are identical? I am rewriting a large number of views and I want to make sure that nothing is changes in the results. Something like select compare_results('select * from v_old', 'select * from v_new')

Re: [GENERAL] Problem with left join when moving a column to another table

2013-06-20 Thread Jason Long
/* where (pwoa.id is not null and pbt.id is not null) or (pwoa.id is null and pbt.id is null) */ order by it.id; /***/ On Thu, 2013-06-20 at 12:29 -0700, David Johnston wrote: > Jason Long-2 wrote > > I am having some proble

[GENERAL] Problem with left join when moving a column to another table

2013-06-20 Thread Jason Long
I am having some problems moving a column to another table and fixing some views that rely on it. I want to move the area_id column from t_offerprice_pipe to t_offerprice and then left join the results. When I have only one table I get the correct results. area_id is currently in the t_offerpric

Re: [GENERAL] Analyzing the types of prepared statements

2013-03-19 Thread Jason Dusek
2013/3/19 John DeSoi : > On Mar 16, 2013, at 8:30 AM, Jason Dusek wrote: >> However, it is not clear to me at this juncture how to get the >> return type for a statement, given its text. Preparing and >> looking it up in pg_prepared_statements will retrieve the >>

[GENERAL] Analyzing the types of prepared statements

2013-03-16 Thread Jason Dusek
I wonder if there's a better way. I'd prefer to be able to run the analysis step on a DB without changing it. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

Re: [GENERAL] date_trunc to aggregate values?

2013-02-04 Thread Jason Dusek
2010-07-07 > 01:45:00 cfc closed a2 tsoil_sctsoil_avg1_sc 20.47 > 20.463133545 > > I was tying to get two records out of this set, with the 'avg" column > representing the mean of the first and last four of each 15 minute records. > &

Re: [GENERAL] Syncing an application cache with xmin

2013-02-03 Thread Jason Dusek
2013/2/3 Tom Lane : > Jason Dusek writes: >> The idea would be, to store information about the last XID in >> the last sync and search for XIDs committed since then upon >> reconnecting for sync. Perhaps `txid_current_snapshot()' >> preserves enough informatio

Re: [GENERAL] logger table

2012-12-25 Thread Jason Dusek
imary key? What is the "cycle number"? For what it is worth, I put all my syslog in PG and have so far been fine without primary keys. (I keep only an hour there at a time, though, and it's only a few hundred megs.) In the past, I have had trouble maintaining a high TPS whi

Re: [GENERAL] How to startup the database server?

2012-12-19 Thread Jason Ma
Hi, The ps output is after the server start, I don't know why I can't see any process after start the server. And of course I use root to initial db, 'cause we have to run this command in CentOS which you need the privilege of root. service postgresql start Regards, Jason 20

Re: [GENERAL] How to startup the database server?

2012-12-19 Thread Jason Ma
there must be somebody has the same problem with me. Any suggestions. Regards, Jason 2012/12/19 Adrian Klaver > On 12/19/2012 06:06 AM, Jason Ma wrote: > >> Hi all, >> I am a freshman of PostgreSQL, My env: PG 8.4 on CentOS,I follow the >> document in the offical

[GENERAL] How to startup the database server?

2012-12-19 Thread Jason Ma
ile. Hope anybody can help me. Thanks. -- Best wishes, Jason Ma

Re: [GENERAL] PQfformat question and retrieving bytea data in C

2012-08-30 Thread Jason Armstrong
t, so I changed bytea_output to 'hex', and DBD::Pg is also happy now (I also read that the hex format is more efficient). -- Jason Armstrong -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] PQfformat question and retrieving bytea data in C

2012-08-29 Thread Jason Armstrong
-- 30da00090132420520203137323030 This is the data I'm expecting to get back. Is the '00' (third byte) causing the problem? The data looks the same at a certain place (ie it starts with the same byte 30, then the C code has 22 bytes whereas the db hex dump has 7 bytes, then

Re: [GENERAL] Rules, Windows and ORDER BY

2012-08-24 Thread Jason Dusek
2012/8/23 Tom Lane : > Jason Dusek writes: >> I have a simple table of keys and values which periodically >> receives updated values. It's desirable to keep older values >> but, most of the time, we query only for the latest value of a >> particular key. > >

[GENERAL] Rules, Windows and ORDER BY

2012-08-23 Thread Jason Dusek
og- -structured storage in Postgres. The window functions make, alas, no use of indexes. -- Jason Dusek pgp // solidsnack // C1EBC57DC55144F35460C8DF1FD4C6C1FED18A2B EXPLAIN (COSTS FALSE, FORMAT YAML) SELECT * FROM kv WHERE k = ... AND realm = ... ORDER BY at LIMIT 1

[GENERAL] Importing oracle data dump files to postgresql

2012-04-16 Thread Jason Armstrong
Does anyone know of utilities that will enable importing oracle data pump files (produced via expdp) into postgresql? Note that I do not have an installation of Oracle, only the binary output file produced. -- Jason Armstrong -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org

[GENERAL] Large Databases redux

2012-03-21 Thread Jason Herr
RAID1 15k disks for each set but the databases and then raid 10 or VERY large disks. I don't really have useful information to give back at this point, but I will once I get everything running. Also, I must work within some limitations, but I am still interested to see what the list has to say Thanks, Jason

[GENERAL] Optimizing Queries Joining Several Views

2012-01-26 Thread Jason Long
In order to do some complex calculations I have joined several views. Each view could join quite a few tables. The user is allowed to filter the results with several multi-select input fields and this is used in the query as where a.id in (:listOfIds). This works fine if the user does not filter

[GENERAL] Index on parent/child hierarchy

2012-01-25 Thread Jason Armstrong
aster AS t3 ON t4.parent=t3.id INNER JOIN uuid.master AS t2 ON t3.parent=t2.id INNER JOIN uuid.master AS t1 ON t2.parent=t1.id WHERE t1.id=UUID Apart from creating a separate table to keep track of the counts, is there a good way to index the table to help? Regards, -- Jason Armstrong -- Sen

[GENERAL] Side effects of moving an index to a new tablespace

2012-01-09 Thread Jason Buberel
What are the side effects and/or constraints put in place while an index is being moved? I assume that the index will not be available to the query planner/engine during that time period. Are there other ways in which the table (and other indices) are affected or locked? Cheers, jason

Re: [GENERAL] Time to move table to new tablespace

2012-01-09 Thread Jason Buberel
01-06" (customer exports) "Select median price for 94086 from 2005-01-01 through 2012-01-06" (charting apps) So by partitioning in one dimension we impact queries in the other. If you have a brilliant solution to problems like this, we'd be happy to hire you (not a joke). Cheers, Jason

[GENERAL] Time to move table to new tablespace

2012-01-07 Thread Jason Buberel
other recommendations are welcome, such as "You are an idiot for not using partitioning in the first place" :) -- Jason L. Buberel CTO, Altos Research http://www.altosresearch.com/ 650.603.0907

[GENERAL] Query Slowdown after upgrade from 9.1.1 to 9.1.2

2011-12-07 Thread Jason Long
I upgraded to v9.1.2 a couple of days ago. Some of my queries are taking noticeably longer. Some of the slower ones would max out at about 45 seconds before. Now they are maxing out at almost 2 minutes. The only change I made to postgresql.conf was geqo_effort = 10 and this was long before this

Re: [GENERAL] Supply Chain Calcs

2011-11-21 Thread Jason Long
productions will fail due to lack of specific parts. On Mon, Nov 21, 2011 at 12:23 PM, Henry Drexler wrote: > google 'weeks of supply' > > > On Mon, Nov 21, 2011 at 1:18 PM, Jason Long < > mailing.li...@octgsoftware.com> wrote: > >> I have a custom

[GENERAL] Supply Chain Calcs

2011-11-21 Thread Jason Long
I have a custom inventory system that runs on PG 9.1. I realize this is not a postgres specify question, but I respect the skills of the members of this list and was hoping for some general advice. The system is not based on any ERP and was built from scratch. My customer requested some supply f

Re: [GENERAL] Is it ever necessary to vacuum a table that only gets inserts/updates?

2011-11-17 Thread Jason Buberel
Pierce writes: > > On 11/16/11 4:24 PM, Jason Buberel wrote: > >> Just wondering if there is ever a reason to vacuum a very large table > >> (> 1B rows) containing rows that never has rows deleted. > > > no updates either? > > To clarify: in Postgres, an "

[GENERAL] Is it ever necessary to vacuum a table that only gets inserts/updates?

2011-11-16 Thread Jason Buberel
Just wondering if there is ever a reason to vacuum a very large table (> 1B rows) containing rows that never has rows deleted. Under what circumstance would the table benefit from a vacuum? -jason -- Jason L. Buberel CTO, Altos Research http://www.altosresearch.com/ 650.603.0907

Re: [GENERAL] Any was to prevent a join if no columns are selected from a view?

2011-09-30 Thread Jason Long
On Thu, 2011-09-29 at 22:54 -0600, Ben Chobot wrote: > On Sep 29, 2011, at 4:57 PM, Jason Long wrote: > > > > > I thought I had read somewhere that Postges could ignore a join if > > it > > was not necessary because there were no columns from the table or > >

[GENERAL] Any was to prevent a join if no columns are selected from a view?

2011-09-29 Thread Jason Long
I started an application around 5 years ago using Hibernate and writing my queries in HQL. The primary search screen has many options to filter and joins many tables and views. As the application grew the SQL Hibernate is generating is out of hand and needs optimization. As with other parts of t

Re: [GENERAL] Identifying old/unused views and table

2011-09-29 Thread Jason Long
On Wed, 2011-09-28 at 08:52 +0200, Guillaume Lelarge wrote: > On Wed, 2011-09-28 at 09:04 +0800, Craig Ringer wrote: > > On 09/28/2011 04:51 AM, Jason Long wrote: > > > I have an application with a couple hundred views and a couple hundred > > > tables. > > > &g

[GENERAL] Identifying old/unused views and table

2011-09-27 Thread Jason Long
I have an application with a couple hundred views and a couple hundred tables. Is there some way I can find out which views have been accessed in the last 6 months or so? Or some way to log this? I know there are views and tables that are no longer in used by my application and I am looking for

Re: [GENERAL] comma vs cross join question

2011-04-08 Thread Jason Long
On Fri, 2011-04-08 at 14:45 -0400, Tom Lane wrote: > Jason Long writes: > > I am using 9.0.3 and the only setting I have changed is > > geqo_effort = 10 > > > One of the joins is a view join. > > Ah. The explain shows there are actually nine base tables in th

[GENERAL] Count for pagination

2011-04-08 Thread Jason Long
The main search screen of my application has pagination. I am basically running 3 queries with the same where clause. 1. Totals for the entire results(not just the number of rows on the first page) a. <300 ms 2. Subset of the total records for one page. a. 1-2 sec 3. Count of the tot

[GENERAL] Count for pagination

2011-04-08 Thread Jason Long
rds for that page in an optimized fashion? I have no problem using a widow query or a Postgres specific feature as my app only runs on Postgres. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder

[GENERAL] comma vs cross join question

2011-04-08 Thread Jason Long
I recently upgraded to JBoss AS 6.0.0.Final which includes a newer version of Hibernate. Previously the Postgres dialect was using a comma, but now is is using cross join. In order do to the migration I had to override the cross join operator to a comma in HIbernate so it would generate the same

Re: [GENERAL] Full Vacuum/Reindex vs autovacuum

2011-02-28 Thread Jason Long
On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote: > On Mon, Nov 8, 2010 at 3:42 PM, Jason Long wrote: > > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: > >> On Mon, Nov 8, 2010 at 11:50 AM, Jason Long wrote: > >> > I currently have Postgres 9.0

Re: [GENERAL] Full Vacuum/Reindex vs autovacuum

2011-02-28 Thread Jason Long
On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote: > On Mon, Nov 8, 2010 at 3:42 PM, Jason Long wrote: > > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: > >> On Mon, Nov 8, 2010 at 11:50 AM, Jason Long wrote: > >> > I currently have Postgres 9.0

Re: [GENERAL] PostgreSQL 8.2.3

2010-11-10 Thread Jason
t are introduced. 8.2.3 was previously approved. Getting a newer version of PostgreSQL approved would probably take time that we do not have given the time-critical nature of our effort. Is 8.2.3 still available anywhere on the PostgreSQL site? I couldn't find it. Thanks, Jason -- View this

[GENERAL] PostgreSQL 8.2.3

2010-11-10 Thread Jason
Hi - I'm trying to find where I can download PostgreSQL 8.2.3. I've looked on the PostgreSQL website but I do not see version 8.2.3 specifically. Any help would be appreciated. Thanks, Jason -- View this message in context: http://postgresql.1045698.n5.nabble.com/Postgr

Re: [GENERAL] Full Vacuum/Reindex vs autovacuum

2010-11-08 Thread Jason Long
On Mon, 2010-11-08 at 16:23 -0700, Scott Marlowe wrote: > On Mon, Nov 8, 2010 at 3:42 PM, Jason Long wrote: > > On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: > >> On Mon, Nov 8, 2010 at 11:50 AM, Jason Long wrote: > >> > I currently have Postgres 9.0

Re: [GENERAL] Full Vacuum/Reindex vs autovacuum

2010-11-08 Thread Jason Long
On Mon, 2010-11-08 at 14:58 -0700, Scott Marlowe wrote: > On Mon, Nov 8, 2010 at 11:50 AM, Jason Long wrote: > > I currently have Postgres 9.0 install after an upgrade. My database is > > relatively small, but complex. The dump is about 90MB. > > > > Every night when

Re: [GENERAL] Full Vacuum/Reindex vs autovacuum

2010-11-08 Thread Jason Long
On Mon, 2010-11-08 at 13:28 -0800, John R Pierce wrote: > On 11/08/10 10:50 AM, Jason Long wrote: > > I currently have Postgres 9.0 install after an upgrade. My database is > > relatively small, but complex. The dump is about 90MB. > > > > Every night when there is no

Re: [GENERAL] Full Vacuum/Reindex vs autovacuum

2010-11-08 Thread Jason Long
On Mon, 2010-11-08 at 13:28 -0800, John R Pierce wrote: > On 11/08/10 10:50 AM, Jason Long wrote: > > I currently have Postgres 9.0 install after an upgrade. My database is > > relatively small, but complex. The dump is about 90MB. > > > > Every night when there is no

[GENERAL] Full Vacuum/Reindex vs autovacuum

2010-11-08 Thread Jason Long
the default. -- Thank you for your time, Jason Long CEO and Chief Software Engineer BS Physics, MS Chemical Engineering http://www.octgsoftware.com HJBug Founder and President http://www.hjbug.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] Full Vacuum/Reindex vs autovacuum

2010-11-08 Thread Jason Long
I currently have Postgres 9.0 install after an upgrade. My database is relatively small, but complex. The dump is about 90MB. Every night when there is no activity I do a full vacuum, a reindex, and then dump a nightly backup. Is this optimal with regards to performance? autovacuum is set to t

Re: [GENERAL] Linux

2010-11-05 Thread Jason Long
I use Centos for production and Fedora for development and I am very happy with both. Especially Centos as I have never had an update break anything. On Fri, 2010-11-05 at 09:50 -0400, David Siebert wrote: > I would say that if you pick any of the big four you will be fine. > CentOS > Ubuntu Serv

[GENERAL] Streaming Replication

2010-09-25 Thread Jason Long
I have several databases running under on Postgres 9.0 installation in production. Is there a way to specify with which onces will be replicated on another server or must all of them be included?

[GENERAL] Surge 2010 Early Registration ends Tuesday!

2010-08-27 Thread Jason Dixon
t and guarantee your seat to this year's event! -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general

[GENERAL] Nodes and trees...

2010-08-03 Thread Jason Schauberger
is not, at least it must be easily portable across the most popular SQL databases. I also explicitly don't want to create an extra tree ID or something like that, because it only mitigates the problem of anomalies, but does not solve it. Thanks in advance, Jason. -- Sent via pgsql-general mail

[GENERAL] Register now for Surge 2010

2010-08-02 Thread Jason Dixon
your seat to this year's event! http://omniti.com/surge/2010/register Thanks, -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.post

[GENERAL] Last day to submit your Surge 2010 CFP!

2010-07-09 Thread Jason Dixon
your business sponsor/exhibit at Surge 2010, please contact us at su...@omniti.com. Thanks! -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http

[GENERAL] CFP for Surge Scalability Conference 2010

2010-07-02 Thread Jason Dixon
icipating as an exhibitor, please visit the Surge website or contact us at su...@omniti.com. Thanks, -- Jason Dixon OmniTI Computer Consulting, Inc. jdi...@omniti.com 443.325.1357 x.241 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your sub

  1   2   3   4   >