[GENERAL] index duplicates primary key, but is used more?

i'm doing a performance audit and noticed something odd. we tested a table a while back, by creating lots of indexes that match different queries (30+). for simplicity, here's a two column table: CREATE TABLE foo (id INT PRIMARY KEY value

Re: [GENERAL] improvements/feedback sought for a working query that looks a bit ugly and might be inefficient

On May 16, 2017, at 10:20 PM, David G. Johnston wrote: > Unless you can discard the 5 and 1000 limits you are going to be stuck > computing rank three times in order to compute and filter them. Thanks a ton for your insight. I'm suck using them (5 is required for throttling, 1000 is required

[GENERAL] improvements/feedback sought for a working query that looks a bit ugly and might be inefficient

Everything here works fine - but after a handful of product iterations & production adjustments, a query that handles a "task queue" across a few tables looks a bit ugly. I'm wondering if anyone can see obvious improvements. There are 3 tables: upstream_provider task

Re: [GENERAL] why isn't this subquery wrong?

thanks all! On Apr 20, 2017, at 6:42 PM, David G. Johnston wrote: > ​Subqueries can see all columns of the parent. When the subquery actually > uses one of them it is called a "correlated subquery". i thought a correlated subquery had to note that table/alias, not a raw column. I guess

[GENERAL] why isn't this subquery wrong?

I ran into an issue while changing a database schema around. Some queries still worked, even though I didn't expect them to. Can anyone explain to me why the following is valid (running 9.6) ? schema CREATE TEMPORARY TABLE example_a__data ( foo_id INT, bar_id INT );

Re: [GENERAL] disk writes within a transaction

On Feb 17, 2017, at 4:05 PM, Jeff Janes wrote: > It will probably be easier to refactor the code than to quantify just how > much damage it does. Thanks for all the info. It looks like this is something worth prioritizing because of the effects on indexes. We had discussed a fix and pointed

[GENERAL] appropriate column for storing ipv4 address

I have to store/search some IP data in Postgres 9.6 and am second-guessing my storage options. Would anyone mind giving this a quick look for me? Right now I have two tables, and am just using cidr for both: create table tracked_ip_address ( id SERIAL primary key,

Re: [GENERAL] recursive query too big to complete. are there any strategies to limit/partition?

On Jan 26, 2017, at 7:07 PM, David G. Johnston wrote: > ​Thinking aloud - why doesn't just finding every record with 5 descendants > not work? Any chain longer than 5 would have at least 5 items. Oh it works. This is why I ask these questions -- new perspectives! > ​Even without recursion

[GENERAL] recursive query too big to complete. are there any strategies to limit/partition?

There are over 20 million records in a self-referential database table, where one record may point to another record as a descendant. Because of a bug in application code, there was no limit on recursion. The max was supposed to be 4. A few outlier records have between 5 and 5000 descendants

Re: [GENERAL] efficiently migrating 'old' data from one table to another

On Jan 12, 2017, at 5:52 PM, Merlin Moncure wrote: > On Thu, Jan 12, 2017 at 2:19 PM, bto...@computer.org > wrote: >> >> Review manual section 7.8.2. Data-Modifying Statements in WITH >> >> >> https://www.postgresql.org/docs/9.6/static/queries-with.html > > this. >

[GENERAL] efficiently migrating 'old' data from one table to another

I'm just wondering if there's a more efficient way of handling a certain periodic data migration. We have a pair of tables with this structure: table_a__live column_1 INT column_2 INT record_timestamp TIMESTAMP table_a__archive

Re: [GENERAL] temporarily disable autovacuum on a database or server ?

On Jan 11, 2017, at 8:19 PM, Melvin Davidson wrote: > > Yes, you're right about ALTER SYSTER. Unfortunately, the op provided neither > PostgreSQL version or O/S, so we can't even be sure that is > an option. That is why I stated "I cannot confirm". I didn't think that would matter, but

[GENERAL] temporarily disable autovacuum on a database or server ?

I've run into a performance issue, and I think autovacuum may be involved. does anyone know if its possible to temporarily stop autovacuum without a server restart ? It seems that it either requires a server restart, or specific tables to be configured. Several times a day/week, I run a

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

On Jan 9, 2017, at 12:49 PM, Israel Brewster wrote: > Planning time: 4.554 ms > Execution time: 225998.839 ms > (20 rows) > > So a little less than four minutes. Not bad (given the size of the database), > or so I thought. > > This morning (so a couple of days later) I ran the query again

[GENERAL] does postgres log the create/refresh of a materialized view anywhere?

Is there a way to find out when a materialized view was created/refreshed? I couldn't find this information anywhere in the docs. the use-case is that I wish to update a materialized view a few times a day in a clustered environment. i'd like to make sure one of the redundant nodes doesn't

Re: [GENERAL] Determining server load

On Sep 27, 2016, at 2:46 PM, Israel Brewster wrote: > I do have those on, and I could write a parser that scans through the logs > counting connections and disconnections to give a number of current > connections at any given time. Trying to make it operate "in real time" would > be

Re: [GENERAL] bitwise storage and operations

On Sep 27, 2016, at 10:54 AM, Brian Dunavant wrote: > db=# select 'foo' where (9 & 1) > 0; A HA Thank you Brian and David -- I didn't realize that you needed to do the comparison to the result. (or convert the result as these work): select 'foo' where (9 & 1)::bool; select

[GENERAL] bitwise storage and operations

We've been storing some "enumerated"/"set" data in postgresql as INT or BIT(32) for several years for some flags/toggles on records. This was preferable for storage to the ENUM type (or multiple columns), as we often changed the number of enumerated options or their labels -- and computing

Re: [GENERAL] optimizing a query

On Jun 22, 2016, at 2:38 PM, David G. Johnston wrote: > What query? ​A self-contained email would be nice.​ This was the same query as in the previous email in the thread. I didn't think to repeat it. I did include it below. >

Re: [GENERAL] optimizing a query

On Jun 22, 2016, at 4:25 AM, Erik Gustafson wrote: > don't you want an index on t_a2b.col_a, maybe partial where col_a=1 ? that table has indexes on all columns. they're never referenced because the rows are so short. this was just an example query too, col_a has 200k variations After a

Re: [GENERAL] optimizing a query

On Jun 21, 2016, at 6:55 PM, David G. Johnston wrote: > ​Aside from the name these indexes are identical...​ sorry. tired eyes copy/pasting between windows and trying to 'average' out 40 similar queries. > ​These two items combined reduce the desirability of diagnosing this...it > doesn't

[GENERAL] optimizing a query

I have a handful of queries in the following general form that I can't seem to optimize any further (same results on 9.3, 9.4, 9.5) I'm wondering if anyone might have a suggestion, or if they're done. The relevant table structure: t_a2b a_id INT references t_a(id)

Re: [GENERAL] does timestamp precision affect storage size?

On Jun 21, 2016, at 4:50 PM, Tom Lane wrote: > Storage-wise, no. If you have a resolution spec on your columns now, > I think dropping the resolution spec would save you a few nanoseconds per > row insertion due to not having to apply the roundoff function. Adding > one would certainly not

[GENERAL] does timestamp precision affect storage size?

i'm cleaning up some queries for performance, and noticed that we never use precision beyond the second (ie, `timestamp(0)`) in our business logic. would there be any savings in storage or performance improvements from losing the resolution on fractional seconds, or are `timestamp(precision)`

[GENERAL] disable ipv6?

I'm running postgresql on ubuntu. the 9.4 branch from postgresql.org I think the only way to disable ipv6 is to edit postgresql.conf and explicitly state localhost in ipv4 as follows - listen_addresses = 'localhost' + listen_addresses = '127.0.0.1' can anyone confirm? -- Sent via

Re: [GENERAL] Is it possible to select index values ?

On Feb 1, 2016, at 6:58 PM, David G. Johnston wrote: > You can query the statistics portion of the database to get some basic > statistics of the form mentioned. Yeah, i didn't think there would be support. The stats collector doesn't have the info that I want... it's focused on how the data

[GENERAL] Is it possible to select index values ?

Is it possible to select index values ? I haven't found any documentation that says "No", but I haven't found anything that says "Yes" either. The reason - I have a few function indexes that are working as partial indexes. I'd like to run some analytics on them (to determine uniqueness of

Re: [GENERAL] controlling memory management with regard to a specific query (or groups of connections)

Thanks. Unfortunately, this is in a clustered environment. NFS and other shared drive systems won't scale well. I'd need to run a service that can serve/delete the local files, which is why I'm just stashing it in Postgres for now. > On Nov 19, 2015, at 2:26 AM, Roxanne Reid-Bennett

[GENERAL] controlling memory management with regard to a specific query (or groups of connections)

As a temporary fix I need to write some uploaded image files to PostgreSQL until a task server can read/process/delete them. The problem I've run into (via server load tests that model our production environment), is that these read/writes end up pushing the indexes used by other queries out

Re: [GENERAL] temporary indexes?

On Oct 22, 2015, at 2:08 PM, Tom Lane wrote: > FWIW, I don't find much attraction in the idea of building an index for > use by a single query. There basically isn't any scenario where that's > going to beat running a plan that doesn't require the index. The value of > an index is generally to

Re: [GENERAL] temporary indexes?

On Oct 22, 2015, at 5:04 PM, Jim Nasby wrote: > > What % of execution time is spent creating those indexes? Or is that factored > into the 1000%? Also, could your analysis queries be run in a REPEATABLE READ > transaction (meaning that once the transaction starts it doesn't get any new >

[GENERAL] temporary indexes?

I couldn't find any mention of this on the archives... Have the project maintainers ever considered extending CREATE INDEX to support "temporary" indexes like CREATE TEMPORARY TABLE? When creating temporary tables for analytics/reporting, I've noticed that I often need to create (then drop)

Re: [GENERAL] temporary indexes?

On Oct 21, 2015, at 3:42 PM, Adrian Klaver wrote: > I misunderstood then. The only thing I can think of is to wrap in a > transaction, though that presents other issues with open transactions and/or > errors in the transaction. I just explicitly drop. The convenience of an auto-drop would be

Re: [GENERAL] temporary indexes?

On Oct 21, 2015, at 2:59 PM, Jeff Janes wrote: > I think he means more like: > > create temporary table temp_test(id int, fld_1 varchar); > create temporary index on permanent_table (fld_1); > > select something from temp_test join permanent_table using (fld_1) where a=b; > select

Re: [GENERAL] using postgresql for session

On Oct 7, 2015, at 11:58 AM, john.tiger wrote: > has anyone used postgres jsonb for holding session ? Since server side > session is really just a piece of data, why bother with special "session" > plugins and just use postgres to hold the data and retrieve it with psycopg2 > ? Maybe use

Re: [GENERAL] "global" & shared sequences

Thanks for the reply. On Oct 2, 2015, at 3:26 PM, Jim Nasby wrote: > I'm not really following here... the size of an index is determined by the > number of tuples in it and the average width of each tuple. So as long as > you're using the same size of data type, 18 vs 1 sequence won't change

[GENERAL] "global" & shared sequences

Hoping to glean some advice from the more experienced The major component of our application currently tracks a few dozen object types, and the total number of objects is in the 100s Millions range. Postgres will potentially be tracking billions of objects. Right now the primary key for

[GENERAL] trouble converting several serial queries into a parallel query

I have a very simple query that is giving me some issues due to the size of the database and the number of requests I make to it in order to compile the report I need: A dumbed down version of the table and query: CREATE TABLE a_to_b ( id_a INT NOT NULL REFERENCES

Re: [GENERAL] newsfeed type query

On Apr 29, 2015, at 6:50 PM, Jim Nasby wrote: Only because you're using UNION. Use UNION ALL instead. The difference between union and union all was negligible. the problem was in the subselect and the sheer size of the tables, even when we could handle it as an index-only scan. On Apr

Re: [GENERAL] newsfeed type query

On Apr 29, 2015, at 12:25 PM, Ladislav Lenart wrote: Could you please explain to me the error(s) in my reasoning? Let me just flip your list in reverse... and add in some elements (marked with a *): posting ts context p60 60 friend p55 55 friend* p54 54 friend* p50 50

Re: [GENERAL] newsfeed type query

Thanks all! These point me in much better directions! Jim Nasby's approach to selecting an expression addressed some things (SELECT f.useraccount_id_b IS NOT NULL AS in_friends) Ladislav Lenart's usage of the CTE is also of a different format that I've used in the past. I think i'll be able

Re: [GENERAL] newsfeed type query

Sorry, I was trying to ask something very abstract as I have similar situations on multiple groups of queries/tables (and they're all much more complex). I'm on pg 9.3 The relevant structure is: posting: id timestamp_publish group_id__in user_id__author

[GENERAL] newsfeed type query

I'm trying to upgrade some code that powers a newfeed type stream, and hoping someone can offer some insight on better ways to structure some parts of the query The part that has me stumped right now... There are several criteria for why something could appear in a stream. for example, here

Re: [GENERAL] splitting up tables based on read/write frequency of columns

On Jan 19, 2015, at 5:07 PM, Stefan Keller wrote: Hi I'm pretty sure PostgreSQL can handle this. But since you asked with a theoretic background, it's probably worthwhile to look at column stores (like [1]). Wow. I didn't know there was a column store extension for PG -- this would come

[GENERAL] splitting up tables based on read/write frequency of columns

This is really a theoretical/anecdotal question, as I'm not at a scale yet where this would measurable. I want to investigate while this is fresh in my mind... I recall reading that unless a row has columns that are TOASTed, an `UPDATE` is essentially an `INSERT + DELETE`, with the previous

Re: [GENERAL] postgresql versus riak for a global exchange

A very popular design I see is often this: - PostgreSQL for account, inventory, transactional; and all writes - NoSQL (Redis, Riak, Mongo, etc) for read-only index postgres (almost like a read-through cache) and assembled documents On Jan 5, 2015, at 5:46 PM, Raymond Cote

Re: [GENERAL] Hostnames, IDNs, Punycode and Unicode Case Folding

On Dec 29, 2014, at 5:36 PM, Mike Cardwell wrote: So the system I've settled with is storing both the originally supplied representation, *and* the lower cased punycode encoded version in a separate column for indexing/search. This seems really hackish to me though. I actually do the same

Re: [GENERAL] Storing Video's or vedio file in DB.

I wouldn't even store it on the filesystem if I could avoid that. Most people I know will assign the video a unique identifier (which is stored in the database) and then store the video file with a 3rd party (e.g. Amazon S3). 1. This is often cheaper. Videos take up a lot of disk space.

[GENERAL] function indexes, index only scan and sorting

Can someone confirm a suspicion for me ? I have a moderately sized table (20+ columns, 3MM rows) that tracks tags. I have a lower(column) function index that is used simplify case-insensitive lookups. CREATE INDEX idx_tag_name_lower ON tag(lower(name)); I have a few complex queries

Re: [GENERAL] Removing duplicate records from a bulk upload (rationale behind selecting a method)

On Dec 8, 2014, at 9:35 PM, Scott Marlowe wrote: select a,b,c into newtable from oldtable group by a,b,c; On pass, done. This is a bit naive, but couldn't this approach potentially be faster (depending on the system)? SELECT a, b, c INTO duplicate_records FROM ( SELECT a, b, c,

Re: [GENERAL] function indexes, index only scan and sorting

On Dec 12, 2014, at 4:58 PM, Tom Lane wrote: regression=# create table tt (f1 int, f2 text); CREATE TABLE regression=# create index on tt (lower(f2)); CREATE INDEX regression=# explain select * from tt order by lower(f2); QUERY PLAN

Re: [GENERAL] deferring ForeignKey checks when you didn't set a deferrable constraint ?

On Nov 20, 2014, at 6:00 PM, Melvin Davidson wrote: Try the following queries. It will give you two .sql files (create_fkeys.sql drop_fkeys.sql). Thanks! I tried a variation of that to create DEFERRABLE constraints, and that was a mess. It appears all the checks ran at the end of the

[GENERAL] deferring ForeignKey checks when you didn't set a deferrable constraint ?

I have a core table with tens-of-millions of rows, and need to delete about a million records. There are 21 foreign key checks against this table. Based on the current performance, it would take a few days to make my deletions. None of the constraints were defined as `DEFERRABLE INITIALLY

Re: [GENERAL] issue with double ordering in a wrapped distinct

I re-ran the query in multiple forms, and included it below (I regexed it to become 'foo2bar' so it's more generic to others). I also uploaded it as a public spreadsheet to google, because I think that is a bit easier to look at:

Re: [GENERAL] String searching

On Nov 18, 2014, at 7:38 AM, Albe Laurenz wrote: That index wouldn't help with the query at all. If you really need a full substring search (i.e., you want to find howardjohnson), the only thing that could help are trigram indexes. I stand corrected. I ran a sample query on my test

Re: [GENERAL] String searching

On Nov 18, 2014, at 11:49 AM, Robert DiFalco wrote: As far as I can tell, the trigram extension would be the easiest way to implement this. It looks like I wouldn't need to mess with vectors, etc. It would just look like a standard index and query, right? It seems that if I need something

[GENERAL] issue with double ordering in a wrapped distinct

I have a particular query that returns resultset of 45k rows out of a large resultset (pg 9.3 and 9.1) It's a many 2 many query, where Im trying to search for Bar based on attributes in a linked Foo. I tweaked the indexes, optimized the query, and got it down an acceptable speed around

Re: [GENERAL] issue with double ordering in a wrapped distinct

On Nov 18, 2014, at 6:43 PM, Tom Lane wrote: but as for why it gets a much worse plan after flattening --- insufficient data. Thanks. I'll run some test cases in the morning and post the full queries matched with ANALYZE EXPLAIN. This is just puzzling to me. I was hoping there might be a

Re: [GENERAL] String searching

On Nov 17, 2014, at 12:55 PM, Robert DiFalco wrote: SELECT * FROM MyTable WHERE upper(FullName) LIKE upper('%John%'); That said, which would be the best extension module to use? A gist index on the uppercased column? Or something else? Thanks! Performance wise, I think a function

[GENERAL] pg_upgrade and ubuntu

I ran into an issue migrating from 9.1 to 9.3 on ubuntu using pg_upgrade the default ubuntu package, and the one from postgresql.org, both store `postgresql.conf` in etc as `/etc/postgresql/VERSION/main/postgresql.conf` however, the pg_upgrade script expects it in the `datadir`. the simple

Re: [GENERAL] Modeling Friendship Relationships

On Nov 11, 2014, at 5:38 PM, Robert DiFalco wrote: Thoughts? Do I just choose one or is there a clear winner? TIA! I prefer this model user_id__a INT NOT NULL REFERENCES user(id), user_id__b INT NOT NULL REFERENCES user(id), is_reciprocal BOOLEAN primary key

[GENERAL] troubleshooting a database that keeps locking up

I have a database that has started to constantly hang after a brief period of activity looking at `select * from pg_stat_activity;` I roughly see the following each time: process 1 | IDLE process 2 | IDLE in transaction process 3 | IDLE in transaction process 4

[GENERAL] Are there any downsides to using postgres' data directory on a dedicated drive/partition / filesystem?

I'm running postgres on a virtual server I was wondering if there were any known issues with moving the data directory to another mounted partition / filesystem. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription:

Re: [GENERAL] Are there any downsides to using postgres' data directory on a dedicated drive/partition / filesystem?

Thanks, everyone! For now this will be provisioning physical drive for a box -- and everything will be there for now. So OS on one drive, and DB on another. I've run into programs before (mostly on Mac/Win) that are exceedingly not happy if they're run on a drive other than the OS.

Re: [GENERAL] faster way to calculate top tags for a resource based on a column

On Oct 7, 2014, at 10:02 AM, Marc Mamin wrote: Hi, it seems to me that your subquery may deliver duplicate ids. And with the selectivity of your example, I would expect an index usage instead of a table scan. You may check how up to date your statistics are and try to raise the statistic

[GENERAL] index behavior question - multicolumn not consulted ?

I have a table with over 1MM records and 15 columns. I had created a unique index on a mix of two columns to enforce a constraint : (resource_type_id, lower(archive_pathname)) i've noticed that searches never use this. no matter what I query, even if it's only the columns in the index. I'm

Re: [GENERAL] faster way to calculate top tags for a resource based on a column

On Oct 6, 2014, at 5:56 PM, Jim Nasby wrote: Don't join to the resource table; there's no reason to because you're not pulling anything from it. Thanks the reply! I'm not pulling anything from the resource table, but the join is necessary because I'm filtering based on it. ( see the WHERE

[GENERAL] faster way to calculate top tags for a resource based on a column

I've been able to fix most of my slow queries into something more acceptable, but I haven't been able to shave any time off this one. I'm hoping someone has another strategy. I have 2 tables: resource resource_2_tag I want to calculate the top 25 tag_ids in resource_2_tag for

[GENERAL] Benching Queries

Does anyone have a good solution for benching queries under various conditions, and collecting the EXPLAIN data ? I looked at pgbench, but it doesn't seem to be what I want. My situation is this- - For a given query, there are 3-5 different ways that I can run it. - Each form of the query

Re: [GENERAL] installing on mac air development machine

On Oct 2, 2014, at 7:30 PM, john gale wrote: The GUI installer for Mac OS X downloaded from postgresql.org works fine. Unless you NEED to use the source/etc version, use the GUI installer. Unless you are already on a system where installing from Fink/Macports/Source is commonplace...

Re: [GENERAL] improving speed of query that uses a multi-column filter ?

On Oct 1, 2014, at 12:34 AM, Misa Simic wrote: Have you considered maybe partial indexes? http://www.postgresql.org/docs/9.3/static/indexes-partial.html I.e idx1 on pk column of the table with where inside index exactly the same as your first where Idx2 on pk column with where inside

[GENERAL] improving speed of query that uses a multi-column filter ?

I'm trying to improve the speed of suite of queries that go across a few million rows. They use 2 main filters across a variety of columns: WHERE (col_1 IS NULL ) AND (col_2 IS NULL) AND ((col_3 IS NULL) OR (col_3 = col_1)) WHERE (col_1 IS True ) AND (col_2 IS True) AND (col_3

Re: [GENERAL] improving speed of query that uses a multi-column filter ?

On Sep 30, 2014, at 8:04 PM, John R Pierce pie...@hogranch.com wrote: if col_1 IS NULL, then that OR condition doesn't make much sense. just saying... I was just making a quick example. There are two commonly used filter sets, each are mostly on Bool columns that allow null -- but one

Re: [GENERAL] table versioning approach (not auditing)

In the past, to accomplish the same thing I've done this: - store the data in hstore/json. instead of storing snapshots, I store deltas. i've been using a second table though, because it's improved performance on reads and writes. - use a transaction log. every write session gets logged

Re: [GENERAL] table versioning approach (not auditing)

On Sep 29, 2014, at 4:06 PM, Nick Guenther wrote: A newbie tangent question: how do you access the transaction serial? Is it txid_current() as listed in http://www.postgresql.org/docs/9.3/static/functions-info.html? My implementations were ridiculously simple/naive in design, and existed

Re: [GENERAL] Postgres as key/value store

On Sep 27, 2014, at 7:48 PM, snacktime wrote: The schema is that a key is a string, and the value is a string or binary. I am actually storing protocol buffer messages, but the library gives me the ability to serialize to native protobuf or to json. Json is useful at times especially

[GENERAL] advice sought - general approaches to optimizing queries around event streams

I have a growing database with millions of rows that track resources against an event stream. i have a few handfuls of queries that interact with this stream in a variety of ways, and I have managed to drop things down from 70s to 3.5s on full scans and offer .05s partial scans. no

[GENERAL] how does full text searching tokenize words ? can it be altered?

I'm getting a handful of 'can not index words longer than 2047 characters' on my `gin` indexes. 1. does this 2047 character count correspond to tokens / indexed words? 2. if so, is there a way to lower this number ? 3. is there a way to profile the index for the frequency of tokens ? (

Re: [GENERAL] How to store fixed size images?

On Jun 19, 2014, at 11:21 AM, Andy Colson wrote: I think it depends on how you are going to use them. I, for example, have lots of images that are served on a web page, after benchmarks I found it was faster to store them on filesystem and let apache serve them directly. I rarely store

Re: [GENERAL] two questions about fulltext searchign / tsvector indexes

fast): create gin index on tsvector(searchable_column) Option B (faster): create tsvector column for `searchable_column` create gin index on searchable_column On Mon, Jun 9, 2014 at 8:55 PM, Jonathan Vanasco postg...@2xlp.com wrote: I can't figure out which one

[GENERAL] two questions about fulltext searchign / tsvector indexes

I'm having some issues with fulltext searching. I've gone though the list archives and stack overflow, but can't seem to get the exact answers. hoping someone can help. Thanks in advance and apologies for these questions being rather basic. I just felt the docs and some online posts are

Re: [GENERAL] how can i bugfix idle in transaction lockups ?

begin w/o commit or rollback? and thanks. you've been very helpful! On Nov 30, 2010, at 2:21 PM, Merlin Moncure wrote: Begin w/o commit is a grave application error and you should consider reworking your code base so that it doesn't happen (ever). -- Sent via pgsql-general mailing list

[GENERAL] how can i bugfix idle in transaction lockups ?

on a project, i find myself continually finding the database locked up with idle in transaction connections are there any commands that will allow me to check exactly what was going on in that transaction ? i couldn't find anything in the docs, and the project has decent traffic, so its going

Re: [GENERAL] question about unique indexes

On May 10, 2010, at 6:29 AM, Alban Hertroys wrote: As the docs state and as others already mentioned, Null values are not considered equal. Ah. I interpreted that wrong. I thought it applied to indexes differently. I'll have to experiment now... -- Sent via pgsql-general mailing

[GENERAL] question about unique indexes

-- running pg 8.4 i have a table defining geographic locations id lat long country_id not null state_id city_id postal_code_id i was given a unique index on (country_id, state_id, city_id, postal_code_id) the unique index isn't

Re: [GENERAL] trying to write a bit of logic as one query, can't seem to do it under 2

On Apr 21, 2010, at 9:38 PM, Glen Parker wrote: Not if qty_requested_available needs to be = qty_available... indeed, i'm an idiot this week. thanks a ton. this really helped me out! -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your

[GENERAL] trying to write a bit of logic as one query, can't seem to do it under 2

I'm trying to write a bit of logic as 1 query, but I can't seem to do it under 2 queries. i'm hoping someone can help the basic premise is that i have an inventory management system , and am trying to update the quantity available in the shopping cart (which is different than the

Re: [GENERAL] trying to write a bit of logic as one query, can't seem to do it under 2

it would be that, but with greatest thank you. that's the exact query i was failing to write ! On Apr 21, 2010, at 8:51 PM, Glen Parker wrote: UPDATE cart_item SET qty_requested_available = least(cart_item.qty_requested, stock.qty_available) FROM stock WHERE cart_item.stock_id =

[GENERAL] is it possible to do an update with a nested select that references the outer update ?

A typo in a webapp left ~150 records damaged overnight I was hoping to automate this, but may just use regex to make update statements for this basically , i have this situation: table a ( main record ) id , id_field , fullname table b ( extended profiles ) id_field ,

[GENERAL] concatenation issue ( 8.4 )

I have a table with name_first name_middle name_last if i try concatenating as such: SELECT name_first || ' ' || name_middle || ' ' || name_last FROM mytable ; I end up with NULL as the concatenated string whenever

[GENERAL] does anyone know what to use in pg_hba.conf that will allow me to run cronjobs with pg_dump?

i think i just need a METHOD for localhost only. thanks. -- 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] list all columns in db

Thank you Jon -- thats the exact sort of trick I was hoping for. Cheers! On Jun 7, 2007, at 6:36 PM, Jon Sime wrote: Jonathan Vanasco wrote: Does anyone have a trick to list all columns in a db ? No trickery, just exploit the availability of the SQL standard information_schema views

[GENERAL] list all columns in db

format // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | CEO/Founder SyndiClick Networks

Re: [GENERAL] Schema as versioning strategy

On Apr 25, 2007, at 2:05 PM, Richard Huxton wrote: Owen Hartnett wrote: I want to freeze a snapshot of the database every year (think of end of year tax records). However, I want this frozen version (and all the previous frozen versions) available to the database user as read-only. My

[GENERAL] conditional joins and views

, so am at a loss on this being a possibility. seeing little documentation on this, i'm thinking its not possible and i'll have to use a function. // Jonathan Vanasco

[GENERAL] unique constraint on 2 columns

I need a certain unique constraint in pg that i can't figure out. Given: create table test_a ( id serial , name_1 varchar(32) , name_2 varchar(32) ); I need name_1 and name_2 to both be unique so that: name_1 never

Re: [GENERAL] unique constraint on 2 columns

to rewrite evertyhing that queries this table - which is both the core table for my application and supposed to be free of any multi-table queries for simple reads. so i'm going to try the trigger route. // Jonathan Vanasco

Re: [GENERAL] unique constraint on 2 columns

On Apr 20, 2007, at 6:13 PM, Jeff Davis wrote: This is more correct structure, and yes, it would involve a join. I know thats the 'more correct' way -- but I can't do the join , which is why I posted about a 2 column unique index. I tested with a join before posting - i have an already

[GENERAL] making a pg store of 'multiple checkboxes' efficient

option a or b . anyone have suggestions ? thanks. // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | SyndiClick.com

Re: [GENERAL] making a pg store of 'multiple checkboxes' efficient

this route. It was my first thought, but there is almost no documentation out there for this type of storage. // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | SyndiClick.com

  1   2   >