[GENERAL] Re: [HACKERS] Merry X-Mass
Little early aren't you? select now()::date gives me 2000-12-22 Hmm.. only one digit is odd. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "Partyka Robert" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, December 22, 2000 8:41 AM Subject: [HACKERS] Merry X-Mass Hello, Merry Christmass and Happy New Year 2001 ;) R. "BoBsoN" Partyka
[GENERAL] Arbitrary table joins.
The below is what I'd like: select * from table natural join (select tablename from table where table_id = 'rid'); select tablename from table where table_id = 'rid'; returns 'table2' So, the equivalent of what I'd like in this case is: select * from table natural join table2; I could do this with a plpgsql function using EXECUTE but returning the full result set will be troublesome for a while. Till then, 2 queries... -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. BEGIN:VCARD VERSION:2.1 N:Taylor;Rod;B FN:Taylor, Rod B ORG:BarChord Entertainment Inc.;System Operation and Development TITLE:Chief Technical Officer ADR;WORK:;;;Toronto;Ontario;;Canada LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Toronto, Ontario=0D=0ACanada X-WAB-GENDER:2 URL: URL:http://www.barchord.com BDAY:19790401 EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20010219T170926Z END:VCARD
Re: [GENERAL] strategies for keeping an audit trail of UPDATEs
What you describe is what we do. Full history of all actions in the data tables are stored elsewhere via a trigger on INSERT, UPDATE / DELETE and a generic function written in C (to get the transaction ID they were a part of for postdated rollbacks or transactions where applicable -- unmodified since). -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "Louis-David Mitterrand" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, February 20, 2001 12:27 PM Subject: [GENERAL] strategies for keeping an audit trail of UPDATEs Hello, In our app we must keep a trace of all changes (UPDATEs) done to an important_table, so that it's possible to get a snapshot of a given record at a given date. The implementation strategy we are thinking about: 1. create an important_table_archive which inherits from important_table, 2. create a trigger ON UPDATE of important_table which automatically creates a record in important_table_archive containing only the UPDATEd fields on the original record along with the modification date and author and the primary key, Is this a viable strategy for that kind of requirement? Is there a better, more orthodox one? Thanks in advance, -- PANOPE: Dj mme Hippolyte est tout prt partir ; Et l'on craint, s'il parat dans ce nouvel orage, Qu'il n'entrane aprs lui tout un peuple volage. (Phdre, J-B Racine, acte 1, scne 4)
Re: [GENERAL] 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) 0;
Sorry... Exact same data. Did a pg_dumpall from one to the other first, then analyzed. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "Joseph Shraibman" [EMAIL PROTECTED] To: "Tom Lane" [EMAIL PROTECTED] Cc: "Rod Taylor" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, February 28, 2001 6:12 PM Subject: Re: [GENERAL] 7.0 vs 7.1 running select count(*) FROM table WHERE (SELECT count(*) ) 0; Tom Lane wrote: "Rod Taylor" [EMAIL PROTECTED] writes: Query on 7.0 on Ultra II took over 5 hours. Query on 7.1 on x86 took under 10 seconds. Same data. Good ... I guess, because I'm not sure why the difference. We haven't done very much in the optimizer since 7.0. What are the full declarations of these tables and their indexes? regards, tom lane Sometimes your production and development machines have different data so behave differently. With postgres this is more of a problem than in general because of the planner. RT: have you tried the same query with the same data on your development machine with 7.0? -- Joseph Shraibman [EMAIL PROTECTED] Increase signal to noise ratio. http://www.targabot.com
Re: [GENERAL] Convert to upper
Title: RE: [GENERAL] Convert to upper
Why not just do:
INSERT INTO TABLE (uppercase_value) VALUES
(upper('value'));
Leave in the check, and all problems are solved
without overhead of a trigger. Simple checks like the one shown don't have
any noticeable speed loss. Trigger overhead does no matter how small the
operation its doing.
--Rod Taylor
There are always four sides to every story: your side, their side, the
truth, and what really happened.
- Original Message -
From:
Trewern,
Ben
To: [EMAIL PROTECTED]
Sent: Friday, March 02, 2001 12:35
PM
Subject: RE: [GENERAL] Convert to
upper
It may be better using a trigger. You don't then get the
error message (from the constraint) the record is just updated with the
uppercase version of what was inserted.
Regards
Ben
-Original Message-
From: Peter Schindler [mailto:[EMAIL PROTECTED]]
Sent: 02 March 2001 16:16
To: Matthias Teege Cc: [EMAIL PROTECTED]
Subject: Re: [GENERAL] Convert to upper
Matthias, the
easiest way is to use CHECK constraints. see example below. Servus,
Peter Matthias Teege
wrote: is there any way to limit values to
upper case strings? Somthing like:
name char(15)
DEFAULT (upper(name)) or must I use triggers?
test=# create table bla(bb char(10) CHECK (bb
=UPPER(bb))); CREATE
test=# \d bla
Table "bla" Attribute
| Type | Modifier
---+---+-- bb | character(10)
| Constraint: ((bb)::text =
upper((bb)::text))
test=# insert into bla values ('OTTO'); INSERT
381409 1 test=# insert into bla values
('otto'); ERROR: ExecAppend: rejected due
to CHECK constraint bla_bb ERROR:
ExecAppend: rejected due to CHECK constraint bla_bb test=# select * from bla; bb
OTTO (1 row)
---(end of
broadcast)--- TIP 2: you
can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to
[EMAIL PROTECTED])
Re: [GENERAL] Re: Thought on OIDs
I use XID's regularly now for historical purposes (delayed reversion of entire operations -- handled by an interface of course where appropriate) but OID's I could certainly live without. However, PHP currently returns the OID in from pg_getlastoid() which I use to select from the table the last PRIMARY KEY entry. Getting this key before sometimes isn't an option (triggers handle them sometimes). If I could have a pg_getlastprimarykey() function which returns a hash of name / value pairs of the new key without using the OID it would be ideal. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "Peter Eisentraut" [EMAIL PROTECTED] To: "Rod Taylor" [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, March 02, 2001 11:31 AM Subject: Re: [GENERAL] Re: Thought on OIDs Rod Taylor writes: Someones bound to hit it in a year or 2 as Postgres is getting pretty good for large projects as well as the small, especially with 7.1's speed enhancements. Hopefully 7.2 will create cycling OIDs and XIDs. Then less problems in 'unlimited' extendability. The easiest approach for OIDs will probably be making them optional in the first place. For the vast majority of users, the OIDs are just wasting space. The cycling XID idea is based on the assertion that eventually all transactions will be closed, at which time a record is either known committed or known dead so that the XID can be recycled. For OIDs, this is not practical. And if you wanted OIDs that automatically fill in the holes, that's probably not realistic. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Re: serial properties
Currently there's a method that an individual backend can cache 1 number from a sequence. Would it be practical to have a master control the sequences and let the replicated backends (different networks potentially) cache a 'slew' of numbers for use? Standard cache of 1, and inter-server cache of several hundred. Rules apply as normal from there -- of course this breaks down when the master goes down... -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "adb" [EMAIL PROTECTED] To: "Gregory Wood" [EMAIL PROTECTED] Cc: "PostgreSQL-General" [EMAIL PROTECTED] Sent: Friday, March 02, 2001 2:11 PM Subject: Re: [GENERAL] Re: serial properties I agree that they are very handy. They become a major pain in the butt when you start doing replication between servers. For instance if you fail over to a standby server and you forget to update it's sequence first, merging data later becomes a nightmare. I'd like to have int8 sequences and basically give each server it's own block of numbers to work with. Alex. On Fri, 2 Mar 2001, Gregory Wood wrote: IMHO, automatically incremented number fields used for primary keys are both a blessing and a curse. It is almost always better to use some other data that *means something* for a primary key. If there's no possible candidate key, *then* maybe an autonumber key is appropriate. Just wanted to say, I disagree strongly here (also MHO). I see quite a few benefits and very few drawbacks to using an auto-incrementing field for a primary key. In fact, the only drawback I can think of would be that it takes up a little more space per record to add a field used solely to uniquely identify that record. I can think of several drawbacks to a non-auto-incrementing primary key though: 1. Less efficient joins. Comparing integers is about as easy as it gets... text, char, and varchar require string comparisons, while floating point numbers are not good as keys because of rounding errors. 2. Discourages value changes. A value that "means something" might need to be modified in some manner. Sure you can define foreign keys with CASCADEs, but if you are using an auto-increment, you don't need to! 3. No value is guaranteed to be unique (well, when doing an INSERT or UPDATE... it only gets into the database if it *is* unique) unless all queries go through a critical section. To the best of my knowledge, the only way to do this inside the database is to use nextval either implicitly or explicitly. The only time I don't use auto-incrementing fields is when I have a many-to-many join table with two foreign keys that are both auto-incrementing fields, in which case the primary key is a combination of those two fields. Other than a bit of extra space, I don't see any reason not to. Greg ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[GENERAL] Lost Trigger(s)?
delete from user; -- Simplest test case. Any deletion request fails on this table. ERROR: SPI_execp() failed in RI_FKey_cascade_del() There are a ton of cascaded deletes that should occur from the above deletion. Which one is failing -- how do I tell (can't seem to do any kind of comparison against bytea fields otherwise I'd try to fish out the 'table' entites as a start)? Tried a few things like: select pg_trigger.* from pg_trigger join pg_class on (tgrelid = pg_class.oid) join pg_proc on (pg_proc.oid = tgfoid) and relname = 'user' and proname ~ 'RI_FKey_cascade_del'; (gives 9 posibilities -- only 1 of these actually has some information at this point for a group of users I want to delete -- deleting the information from the single table with information doesn't solve the problem) select reltriggers from pg_class where relname = 'user'; (gives 32 triggers). All are in pg_trigger. \d user - Shows me Indicies, Constraints, lack of rules (all properly). Doesn't show any triggers.. But have come up with no obvious inconsistencies which would cause this. 7.1 Beta 5 is the version. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. BEGIN:VCARD VERSION:2.1 N:Taylor;Rod;B FN:Taylor, Rod B ORG:BarChord Entertainment Inc.;System Operation and Development TITLE:Chief Technical Officer ADR;WORK:;;;Toronto;Ontario;;Canada LABEL;WORK;ENCODING=QUOTED-PRINTABLE:Toronto, Ontario=0D=0ACanada X-WAB-GENDER:2 URL: URL:http://www.barchord.com BDAY:19790401 EMAIL;INTERNET:[EMAIL PROTECTED] EMAIL;PREF;INTERNET:[EMAIL PROTECTED] EMAIL;INTERNET:[EMAIL PROTECTED] REV:20010323T041502Z END:VCARD ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [GENERAL] index not used with subselect in where clause ?
Adding a LIMIT 1 in the subplan may also help -- as you only need a single match to make it true so additional finds are useless -- it'll stop sooner or will be more likely to use an index than a full table scan. -- Rod Taylor There are always four sides to every story: your side, their side, the truth, and what really happened. - Original Message - From: "Tom Lane" [EMAIL PROTECTED] To: "Christian Fritze" [EMAIL PROTECTED] Cc: "Stephan Szabo" [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, April 17, 2001 10:33 AM Subject: Re: [GENERAL] index not used with subselect in where clause ? Christian Fritze [EMAIL PROTECTED] writes: explain select * from allmain where exists (select distinct dokids_as_int from allslwfull where dokids_as_int = idn and wort_nouml_lower like 'gen%') Try dropping the "distinct" on the inner select. As a moment's thought will reveal, it's not buying you anything; and it's costing you sort and unique passes over the subplan result. regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] last comma inside CREATE TABLE () statements
Heh.. Actually, those queries look quite good if you centre them in a
page -- Assuming all characters are the same width anyway. SELECT,
FROM, and other key words go onto the left column along with comma's,
and the relevant database columns, tables, and where clauses go on the
right. With a good naming convention I don't even have to look at the
left hand side of the query but rather just the list of entities on
the right. It also means every line has a left side and a right side.
Anyway, not that it matters much but If the loose grammar is
implemented it should be optional and off by default.
--
Rod Taylor
There are always four sides to every story: your side, their side, the
truth, and what really happened.
- Original Message -
From: will trillich [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, April 27, 2001 12:56 AM
Subject: Re: [GENERAL] last comma inside CREATE TABLE () statements
On Thu, Apr 26, 2001 at 10:38:42AM -0400, Rod Taylor wrote:
Gah.. just put comma's at the beginning...
Oh, now THAT's intuitive:
. To be
, or not to be
, that is the question
. Whether 'tis nobler...
Charming. :)
SELECT bleah
, blah
, otherthing
FROM arghh
, feh
, fah
WHERE ( blah in ('1'
, '2'
, '3')
OR otherthing IS TRUE
)
OR bleah IS FALSE
Oh, and indent nicer. You can remove virtually any line (except
the
ones with commands in them) without any issues.
What that does, is it transfers the location of the problem. Now
the comma is effectively in FRONT of most terms, except the
FIRST.
An alternative compromise:
select
first
,
second
,
third
,
fourth
from
alpha
,
bravo
,
charlie
;
It's odd to use a whole line just for a florkin' comma, but in vi
2ddkkP or 2ddP will rearrange things nicely, while
keeping the purists at bay (not to mention any names, but You
Know Who You Are :).
I'd still prefer to ALLOW (but not DEMAND) 'empty after last
comma'. Or if you're determined to go for 'empty before first
comma':
update tbl
set
,one = something
,two = something-else
,three = fn('hgttg',42)
,four = that
;
But i hope you'll agree that this is more obtuse than we need to
be. Not to mention the speedbump effect it'll have on the person
who's got to look over your code next month.
This looks much nicer, imho --
update tbl
set
one = something ,
two = something-else ,
three = fn('hgttg',42) ,
four = that,
;
After all, the comma is of no importance to the conceptual task
we're after: i don't care if there's a token separating those
assignments -- i'm interested in the fields and the values being
assigned to them. The commas are just there to help us predict
that the compiler will understand what we're after.
And it's easy to rearrange those lines in a text editor without
having to be paranoid about Do i need to add a comma somewhere?
Should i look to see if i should take one out?
Computers should work. People should think. Data! Mow the lawn!
--
don't visit this page. it's bad for you. take my expert word for it.
http://www.salon.com/people/col/pagl/2001/03/21/spring/index1.html
[EMAIL PROTECTED]
http://sourceforge.net/projects/newbiedoc -- we need your brain!
http://www.dontUthink.com/ -- your brain needs us!
---(end of
broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
Re: [GENERAL] OSDL DBT-2 for PostgreSQL
On Fri, 2003-08-01 at 20:32, Mark Wong wrote: On Fri, Aug 01, 2003 at 05:05:18PM -0700, Josh Berkus wrote: Mark, I've just got our DBT-2 workload (TPC-C derivate) working with PostgreSQL using C stored functions and libpq. I'd love to get some feedback. I'm confused. Jenny Zhang just announced OSDL-DBT3 for Postgres; is this a different test or does one of you have the name wrong? Yeah, this is a different test. DBT-3 is based on the TPC-H and DBT-2 is based on the TPC-C. Josh, All 3 (DBT-1 is another style of test) should be included into the benchmark kit. It should be noted that the DBT tests are fairly linux specific at the moment, though that doesn't take much to change. OSDL has been happy to take portability patches. signature.asc Description: This is a digitally signed message part
Re: [HACKERS] [GENERAL] plPHP in core?
On Sat, 2005-04-02 at 21:48 +0200, Peter Eisentraut wrote: Marc G. Fournier wrote: d) Bringing PL/Java into core will force a consistent documentation and, I imagine, a chapter of it's own in the main docs. I'm happy to write most of it but English is not my native language. Whatever I put into print will always benefit from a review. There is nothing stop'ng a chapter being added now, Actually there is: We don't ship documentation for software that we don't ship. Very well, rephrase that a little. There is nothing stopping additional links to documentation from being added to the PostgreSQL website in the documentation section. -- ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] why does explain tell me I'm using a seq scan?
On Fri, 2003-11-07 at 19:11, Mark Harrison wrote:
I have indexed two columns in a table. Can somebody explain to me why
the first query below uses an Index Scan while the second uses a Seq
Scan?
The first expects to find a single row, the second expects to find 17000
rows, a significant portion of the table where an index scan would be a
loss based on current tuning parameters.
--
Rod Taylor pg [at] rbt [dot] ca
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 8: explain analyze is your friend
