Re: [SQL] Best way to simulate Booleans
On Tue, 2009-07-07 at 00:13 -0600, Scott Marlowe wrote: > On Mon, Jul 6, 2009 at 7:22 PM, Peter Headland wrote: > > I know, I know, PostgreSQL has Booleans that work very nicely. > > Unfortunately, I have to create a schema that will work on Oracle as well as > > PostgreSQL, by which I mean that a single set of Java/JDBC code has to work > > with both databases. I have an XML meta-schema that enables me to generate > > appropriate DDL; that handles all the INTEGER vs. NUMBER(m,n) stuff. But > > Oracle simply has no Booleans, so I will have to resort to some more or less > > ugly alternative. I am hoping that others here have had to deal with this > > and can suggest an approach that will be minimally loathsome. > > mybool int check (mybool >=0 and <=1) Integer works best since it converts easily to boolean mybool smallint check (mybool in (0, 1)) You can use "char" also, but the syntax is less clear. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Clarity on how LOCK interacts with INHERIT
On 12 April 2013 21:59, Robins Tharakan wrote: > postgres=# CREATE TABLE lock_tbl6 (b BIGINT) INHERITS (lock_tbl5); > CREATE TABLE > postgres=> LOCK TABLE lock_tbl6 * IN access EXCLUSIVE MODE; > LOCK TABLE > postgres=> LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE; > ERROR: permission denied for relation lock_tbl5 > STATEMENT: LOCK TABLE lock_tbl5 IN access EXCLUSIVE MODE; > ERROR: permission denied for relation lock_tbl5 No problem. LOCK * locks only the descendants, so tbl5 is not locked since it is not a decendant of tbl6. -- Simon Riggs http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training & Services -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [HACKERS] [SQL] Case Preservation disregarding case
On Wed, 2006-11-01 at 11:31 -0500, Chuck McDevitt wrote: > But, stepping back from all that, what is it the users want? > > 1) When re-creating a CREATE TABLE statement from whatever catalog > info, they'd like the names to come back exactly as then entered them. > If I do: > CREATE TABLE BobsTable (WeeklySales numeric(10,2), > "SomeStrangeName" int); > > They'd like to see exactly that when the CREATE TABLE gets > re-created, not what we do now: > > CREATE TABLE bobstable (weeklysales numeric(10,2), > "SomeStrangeName" int); This would be very good indEEd. It can be very annoying trying to locate a table when the user swears they called it one thing and actually the case or quotation is different. Current behaviour isn't useful, even if it is "onspec" (or is that OnSpec?). Would be better to make this behaviour a userset switchable between the exactly compliant and the more intuitive. We have namespaces to differentiate between two sources of object names, so anybody who creates a schema where MyColumn is not the same thing as myColumn is not following sensible rules for conceptual distance. It's certainly an error of best practice, even if its not actually a bug. > 2) When doing reports, they'd like the name as entered to be the title > of the column: > Select * from bobstable; > > Would be nice if they saw this: > WeeklySalesSomeStrangeName > ------ ... > Producing "?column?" or somesuch to use in the report, it could return a > title like "sum(WeeklySales)" That would be just great. I'm not sure the spec says what the titles should be, does it? -- Simon Riggs EnterpriseDB http://www.enterprisedb.com ---(end of broadcast)--- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate
Re: [SQL] trigger for TRUNCATE?
Alvaro Herrera wrote: > My thinking is that a TRUNCATE trigger is a per-statement trigger which > doesn't have access to the set of deleted rows. > In that way it would be different from a per-statement trigger for > DELETE. Completely agree. A truncate trigger should run a different function to a delete trigger. This is an important feature for trigger-based replication systems. Not just slony, but bucardo and others too. It's an embarrassing hole in our high availability capabilities and we really need to fill the gap. We can't always control whether an application will issue truncates or not. Rather spookily that's what I've been working on this afternoon, though I didn't realise this thread was in progress until now, nor did I realise there might be possible objections. I do hope the importance of it is enough to overcome objections. Yes, it does look fairly straightforward. Should be ready for when 8.4 opens, assuming we agree. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] trigger for TRUNCATE?
On Fri, 2008-01-11 at 08:24 +, Richard Huxton wrote: > I've always considered TRUNCATE to be DDL rather than DML. I mentally > group it with DROP TABLE rather than DELETE> DDL/DML probably isn't the right split, since its then arguable as to which group of commands it belongs in. I see we have 3 types of commands: 1. Commands that alter the rows in the table e.g. UPDATE, DELETE, INSERT + TRUNCATE is clearly part of this group 2. Commands that change the shape of a table e.g. ALTER TABLE add/drop column, change type, constraints etc 3. Commands that change the environment of a table e.g. foreign keys, indexes, grants, set fillfactor, ANALYZE, VACUUM, CLUSTER etc Type (1) commands need to be replicated always, sliding down the scale to the type (3) which might well be site dependent. Applications seldom issue type 3 commands anyway, so its easy for a DBA to arrange for them to be executed in multiple places and there isn't any timing requirement usually to making that work. In some cases some of these factors might be managed by replication controllers, so the DBA doesn't need to touch at least some of these aspects. Applications do issue some type 2 commands, but usually they are for TEMP tables. Type 2 commands do change replication, but might not need to be exactly replicated on both sites. Again, some utilities exist to ensure that DDL changes are correctly replicated, so there is slightly less need for triggers on this. In many cases the application is locked down completely anyway and almost no DDL is ever executed. If it is executed it needs to be done in coordination with a change of application version. Applications issue lots of type 1 commands and we can't always easily change the SQL they execute. It's very common for an application to have a single userid, so its not a problem for it to be the owner of the table as well and hence TRUNCATE is usable. It is often written without any thought for replication, which is usually an afterthought. (If we allowed RULEs to translate TRUNCATE into DELETEs it would at least plug the gap, but thats not a great planand I'm not suggesting it.) So the main gap in all of this is the lack of a TRUNCATE trigger, probably also the lack of a specific TRUNCATE privilege as well. -- Simon Riggs 2ndQuadrant http://www.2ndQuadrant.com ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Query tuning
On Fri, 2008-05-16 at 09:21 -0600, Scott Marlowe wrote: > On Thu, May 15, 2008 at 12:00 AM, <[EMAIL PROTECTED]> wrote: > > Hi, > > > > select count(distinct(j.JOBID)) as jobCount > > from JOB_TYPE_FIRST a, JOB_TYPE_SECOND b, JOB_ALLOCATION_WORKLIST j > > where (( a.JOBID = j.JOBID) > > and (a.BOOK_ID = :bookId)) > > or ((b.JOBID = j.JOBID) > > and (b.BOOK_ID = :bookId)); > > Have you tried joining a with j and b with j separately, and then > doing a union on those two sets? That seems the only way to get a sane answer to this query, which is otherwise an unconstrained join on both sides of the OR. Great example of a query which runs slow because the question is phrased incorrectly. Count(distinct) is pretty much the only function that will give the same answer as a correctly phrased query. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Parallel updates on multiple cores
On Mon, 2008-06-09 at 01:29 -0700, Andrei wrote: > The function above updates the rows between the ids start_id and > end_id. > I have a quad core procesor so i run two separate connections to the > database: select populate_test_data(5000,1) and another select > populate_test_data(5000,2). In this case each function runs on one > core doing the inserts in parallel, but when i try to run select > select_unprocessed(1,5001) and from another connection select > select_unprocessed(5001, 10001), one of the processes locks the table > so the other one has to wait until the table is unlocked. > Each process updates different parts of the table. Your ranges overlap. So one waits for the other on tuple=5001. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Fri, 2008-07-11 at 11:21 -0600, Scott Marlowe wrote: > rollback after commit Are you sure? Personally I don't think its viable. If it really does that it will would also need to rollback all transactions whose changes depend upon the earlier transaction. It would also need to track transactions that read data changed by an earlier transaction and then makes changes to the database. It's got no way to track that without extensive and costly additional infrastructure, since after transaction commit row locking information can be cleaned up by read-only transactions accessing those changed data blocks. Flashback query allows reading data as it was at a certain point in the past. We might one day provide that, but undoing individual transactions isn't ever going to be feasible, without unknowable risk. Not jumping on you, just think their marketing is ahead of the reality. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Fri, 2008-07-11 at 18:56 -0700, Lewis Cunningham wrote: > In addition to allowing you to read old data, Flashback will allow you > to rollback to a point in time, including returning a single table to > a specific state. Flashback database is like PITR without the log > files. Like I said: you cannot rollback a single transaction after commit. Please don't put links to copyrighted material on our lists. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Sat, 2008-07-12 at 09:40 +0100, Dave Page wrote: > On Sat, Jul 12, 2008 at 8:56 AM, Simon Riggs <[EMAIL PROTECTED]> wrote: > > > > Please don't put links to copyrighted material on our lists. > > That's an odd thing to say, given that virtually every link on our > lists probably points to material copyrighted in some way. Prudence is all I ask for. We don't need to provide additional advertising for others, nor do we wish to embroil ourselves in accusations over copyright violations. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, 2008-07-14 at 21:59 +0200, Kaare Rasmussen wrote: > > I just lost a months worth of stats data myself, so join the club. It > > wasn't critical data, but it would have been nice to have kept > > around... > > I also think there could be a TODO item in it. If vacuum instead of removing > items, somehow stashed them away in a storage limited archive it would be > possible to do a SELECT...AS OF TIMESTAMP. > > The idea is of course to be able to retrieve rows that really are deleted, > but > are still on disk as non-vacuumed or vacuumed and not removed completely. And > it would also take a 2. stage vacuumer to keep the storage within its limits. I've got the design all worked out for this. The "only" thing we need is a VACUUM that will remove unseen data from within the middle of the sum-of-all-snapshots, if there is a gap. At the moment we never remove rows beyond global xmin, but we could iff the transactions at xmin promise never to update data. That should go on the TODO list as a precursor. Some discussion required :-) -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, 2008-07-14 at 22:38 +0200, Kaare Rasmussen wrote: > > This sounds a lot like the functionality that a temporal data model > > would give you. In this model you never delete tuples from your > > database, your only insert and update tuples that are valid for > > specific periods of time. > > Isn't this exactly what Alvaro describes? The time travel feature that was > removed because it made Postgres too slow to use in production? Similar. Performance is the issue to be solved with row removal, yes. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Rollback in Postgres
On Mon, 2008-07-14 at 22:54 -0400, Jonah H. Harris wrote: > On Mon, Jul 14, 2008 at 9:18 PM, Tom Lane <[EMAIL PROTECTED]> wrote: > > Kaare Rasmussen <[EMAIL PROTECTED]> writes: > >> But yes, it has to be enabled, and yes it has to have a performance cost > >> somehow, but people are requesting it, and somehow I don't think Oracle > >> developed the feature just for fun. > > > > No, they developed it for marketing. > > No, they developed it because it was needed. I agree such improvements would be welcomed. I'm pretty sure they sat around saying we can already do that some other way at first, until the requests started to pile up. > > Stuff that they see fit to add is not necessarily going to be on our radar > > screen in the foreseeable future. I'm not clear on why there should be an inherent delay. I think PostgreSQL adoption is mostly held back by operational features, like performance management, locking, backup. But we're mainly constrained on people's time, i.e. money. And AFAICS nothing like this is going to happen in this release. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] Make the planner smarter about idiosyncratic (or broken) ORM behaviour
On Fri, 2009-05-22 at 10:33 +0100, Frank Jördens wrote: > Almost all of the 8k queries per second that are hitting our database > are generated by a web development framework's ORM (Django), and some > of the syntax there is quite mad. Possibly the worst tic exhibited by > the translation layer is that if you filter a query on an object by > more than one property on a related object it will create a join for > each property: It's a known issue that we're looking to work on in the next release. Work started in 8.4, though was more complex than first appeared and we didn't complete it in time for the dev deadline. -- Simon Riggs www.2ndQuadrant.com PostgreSQL Training, Services and Support -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
