[GENERAL] RE: Roll Back dont roll back counters
Title: RE: Roll Back dont roll back counters -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Yes, of course. But you have to have pretty stringent performance requirements to justify changing to technical IDs as a matter of rule. Reading an ER model is pretty important too, from a maintenance perspective, though. I've been involved in doing that for a while now, and trying to read business meaning into some of the data models that I've worked with is pretty near impossible, which makes the maintenance designer's job really difficult. You have to speak to all the original people involved in the system design, and sometimes that is worse than simply redoing the whole thing. Anyway, a discourse on ER design is a pretty subjective area, which we should probably not indulge in at present ;-) MikeA >> -Original Message- >> From: Bruno Wolff III [mailto:[EMAIL PROTECTED]] >> Sent: 17 August 2001 13:09 >> To: Michael Ansley (UK) >> Cc: [EMAIL PROTECTED] >> Subject: Re: Roll Back dont roll back counters >> >> >> On Fri, Aug 17, 2001 at 11:37:33AM +0100, >> "Michael Ansley (UK)" >> <[EMAIL PROTECTED]> wrote: >> > >> > Um, no, primary keys should not always be opaque. >> Technical primary >> > keys should always be opaque. Relational modelling has >> been built to >> >> I think it has more to do with efficiency and whether or not you >> ever expect the primary key values to change, then how hard it is >> to read an >> ER diagram. >> -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com> iQA/AwUBO30TzHympNV/C086EQJaAwCg2bh+rZOH1vMlUy6a42N4T15AJ5EAoKWz Q1d0jSw6YyxcALUhhktFWb9E =xJ4l -END PGP SIGNATURE- _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
RE: [GENERAL] Roll Back dont roll back counters
Title: RE: [GENERAL] Roll Back dont roll back counters -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Um, no, primary keys should not always be opaque. Technical primary keys should always be opaque. Relational modelling has been built to exploit primary keys actually being the business key, to create proper identifying relationships. Using technical PKs undermines this, although often this is offset by carrying large primary keys through to child tables. For most simple to moderately complex tables, I prefer using the business key as the primary key, and only using a technical key when it's really (REALLY) warranted. How do I differentiate? Well, common sense is probably the best. 'Address' gets a technical ID, because it's business key would be cumbersome, the same with 'person'. But 'Network Operator' already has a five character business key, and that's adequate without a technical key, as is the seven character 'Tier' business key, and the twenty character 'Point of Interconnect' key, even though they could all feasibly take a technical ID. And when you're trying to read an ER model, technical keys are a pain in the butt, because it becomes incredibly difficult to read meaning into the model. With matching business and primary keys, reading an ER model is a doddle. Almost... MikeA >> -Original Message- >> From: Roderick A. Anderson [mailto:[EMAIL PROTECTED]] >> Sent: 16 August 2001 15:37 >> To: Martijn van Oosterhout >> Cc: Ben-Nes Michael; [EMAIL PROTECTED] >> Subject: Re: [GENERAL] Roll Back dont roll back counters >> >> >> On Thu, 16 Aug 2001, Martijn van Oosterhout wrote: >> >> > You can tell the order anyway. The order doesn't change, >> it's only that >> > there may be numbers missing, >> >> Please note the message from "Michael Ansley (UK)". If two of >> more connections get a cache from a sequence and the inserts are >> 'sporatic' >> then there will be a difference in the sequence number and the >> insert order. >> >> > Primarys keys should be opaque. Any meaning read into them >> is entirely >> > coincidental. >> >> I agree. >> >> >> Rod >> -- >> >> >> ---(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 >> -BEGIN PGP SIGNATURE- Version: PGPfreeware 6.5.3 for non-commercial use <http://www.pgp.com> iQA/AwUBO3zzwHympNV/C086EQLiCgCdHWBEvPlk+ueJUwMPm8PBdnuHUbMAoMt7 J6HkkM3TYof9ehs4S0pGYUwu =pASp -END PGP SIGNATURE- _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
RE: [GENERAL] internationalizing text
Title: RE: [GENERAL] internationalizing text I think that the standard way to do this is to use a resource identifier, and then have a separate table with all strings. That's the way that most internationalisation is done in programs, and it's probably not bad for databases either. So maybe: create table something ( id serial, yadayada int4, whatever date, mumble float8, ... id_resource int4 references something_text(id) ); create table something_text ( id int4, lang varchar(5), -- language code 'en-us','it','jp'... descr varchar(50) ); Anyway, just a thought... MikeA -Original Message- From: will trillich To: [EMAIL PROTECTED] Sent: 23/03/01 04:06 Subject: [GENERAL] internationalizing text so who's got a clever implementation of cross-linguistic texts? create table something ( id serial, yadayada int4, whatever date, mumble float8, en varchar(50), -- english es varchar(50), -- espanol fr varchar(50), -- francais de varchar(50), -- deutsch ... ); or maybe create table something ( id serial, yadayada int4, whatever date, mumble float8, ... ); create table something_text ( id int4 references something(id), lang varchar(5), -- language code 'en-us','it','jp'... descr varchar(50) ); anybody done something like this? is there another concept or are these two the whole ball-o-wax? pro's and con's? -- It is always hazardous to ask "Why?" in science, but it is often interesting to do so just the same. -- Isaac Asimov, 'The Genetic Code' [EMAIL PROTECTED] http://newbieDoc.sourceforge.net/ -- we need your brain! http://www.dontUthink.com/ -- your brain needs us! ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
RE: Re: [GENERAL] OID as Primary Key
Title: RE: Re: [GENERAL] OID as Primary Key And you can get the last SERIAL inserted using currval. MikeA >> -Original Message- >> From: Brent R. Matzelle [mailto:[EMAIL PROTECTED]] >> Sent: 22 March 2001 16:36 >> To: [EMAIL PROTECTED] >> Subject: Re: Re: [GENERAL] OID as Primary Key >> >> >> --- Jonas Bengtsson <[EMAIL PROTECTED]> wrote: >> > Can't you do a dump with the oid's? >> >> Yes, but I would never suggest it. >> >> > But when I want to know the primary key of the inserted row >> > I have to do an extra select query. If I use oid I just use >> > pg_getlastoid() in php. >> > And it is redundant data to store another integer. >> >> You should not use oid's as primary keys just because you want >> to save a little HD space. >> >> I contributed a some code on PHPBuilder for a function that >> returns the insert id of a SERIAL primary key. You can view it >> here: >> http://www.phpbuilder.com/snippet/download.php?type=snippet&id=304 >> . It works well for me and it does not kludge my code like >> using the oid does. >> >> Brent >> >> __ >> Do You Yahoo!? >> Get email at your own domain with Yahoo! Mail. >> http://personal.mail.yahoo.com/ >> >> ---(end of >> broadcast)--- >> TIP 1: subscribe and unsubscribe commands go to >> [EMAIL PROTECTED] >> _ This e-mail and any attachments are confidential and may also be privileged and/or copyright material of Intec Telecom Systems PLC (or its affiliated companies). If you are not an intended or authorised recipient of this e-mail or have received it in error, please delete it immediately and notify the sender by e-mail. In such a case, reading, reproducing, printing or further dissemination of this e-mail is strictly prohibited and may be unlawful. Intec Telecom Systems PLC. does not represent or warrant that an attachment hereto is free from computer viruses or other defects. The opinions expressed in this e-mail and any attachments may be those of the author and are not necessarily those of Intec Telecom Systems PLC. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. __
RE: [GENERAL] INSERT ... RETURNING as Oracle
Title: RE: [GENERAL] INSERT ... RETURNING as Oracle And using MAX is also horrifically slow once you start having any significant volumes of data. Why not write a function to add the info, which selects from a sequence, inserts the new row using the sequence number, and then returns the sequence number? I've done it a number of times, and it's been quite successful so far. Cheers... MikeA -Original Message- From: Peter Eisentraut To: Sipos Andras Cc: [EMAIL PROTECTED] Sent: 3-4-01 8:04 PM Subject: Re: [GENERAL] INSERT ... RETURNING as Oracle Sipos Andras writes: > create table basket ( > id serial NOT NULL PRIMARY KEY, > timestamp timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP > ); > > How can I make a one step insert into the table and get values of 'ID' ? > I am trying to find a similar solution as in the oracle's INSERT ... > RETURNING. We have this sort of thing on the wish list, but it doesn't exist yet. Some people feel it's better to calculate the default in the client program and insert it explicitly. This may be a reasonable alternative for some applications. > If I use at first the INSERT, and after SELECT MAX(ID), the result will be > uncertain. If you are worried about other transactions getting in the way then you should do SET TRANSACTION ISOLATION LEVEL SERIABLIZABLE. -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/ ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED]) ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: [GENERAL] postgres length limitation query
Title: RE: [GENERAL] postgres length limitation query If you're talking about the length of the query string, it should be limited only by the memory available to the processes that handle it. If they run out of memory, it all goes pop! and then releases the used memory, and should carry on working ;-) MikeA -Original Message- From: Enrico Mangano [mailto:[EMAIL PROTECTED]] Sent: 15 February 2001 08:39 To: [EMAIL PROTECTED] Subject: [GENERAL] postgres length limitation query Hello, is there any limit in the length of a query? What postgres does if I give it a query that is bigger? Thanks. ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: [GENERAL] using tables as types in other tables
Title: RE: [GENERAL] using tables as types in other tables
I went over this ground about six months ago, and the bottom line is: until somebody picks this up and sorts out the code (which suffers from bitrot) it's not going to work like you described (believe me, I tried). Put the addresses in a single table, with foreign keys from each other table that you need. What you may decide to do is forward- rather than reverse-reference, possibly even with a resolution table between the address table, and the actual entity tables:
client >-|
|
|
vendor >< address_mem >-< address
|
|
employee >---|
This uses sub-typing, which SQL doesn't deal with very well, but it's an idea. Of course, if you carry it a step further, and use PGs inheritance, then you can make life a little easier for yourself:
/--*client
/
/
address >-< address_ent-*vendor
\
\
\--*employee
where address carries a foreign key to addressable_entity, or something. I haven't thought this one through particularly hard, but this is what these facilities are for ;-)
Cheers...
MikeA
-Original Message-
From: Louis-David Mitterrand
To: [EMAIL PROTECTED]
Sent: 2-14-01 6:35 PM
Subject: [GENERAL] using tables as types in other tables
In the app we are developing the concept of an address will occur very
often on many tables (vendor, clients, employees, etc.) so we are
looking to avoid code duplication by centralizing the addresses in one
table. However I once read on one of the pgsql- lists that one could use
a table name as a type:
create table address(street text, zip text, city text, country text);
create table employee(emp_addr address, emp_id int);
This is accepted by Postgres but the question is how to insert into the
employee table? What syntax should be used? I tried:
insert into employee values (('mystreet','myzip','mycity','mycountry'),
1);
But it doesn't work.
Should I proceed along that path or would I be better off using a
central address table with keys to the other tables?
Thanks in advance for your insight,
--
THESEE: D'un perfide ennemi j'ai purgé la nature ;
A ses monstres lui-même a servi de pâture ;
(Phèdre, J-B Racine, acte 3,
scène 5)
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety OK, someone want to answer this? I have always been under the impression that Postgres would not block under these circumstances, however, this is clearly blocking, for no apparently good reason. I have just run a test on my own server, and this blocking does not happen. Both sessions run independently until each has committed, then displaying information from the other insert, but definitely not blocking. It works exactly as I would have expected. Anybody??? MikeA -Original Message- From: DaVinci [mailto:[EMAIL PROTECTED]] Sent: 13 February 2001 10:42 To: Lista PostgreSql Subject: Re: [GENERAL] transaction safety On Tue, Feb 13, 2001 at 09:56:18AM -, Michael Ansley wrote: > Hi, > > Well, the number is 'locked', because once it's given to you, that's it, > it's gone from the 'list of available numbers' (i.e.: the sequence). > However, between the insert, and the read of the ID, if another transaction > performs an insert, it does NOT affect the ID that the first transaction > reads (i.e.: your ID read in the first transaction IS definitely still safe, > it will still read the correct one). I understand this. > AND, the first insert does NOT block > the second insert. The second insert could complete and commit before the > first one. But I don't know how to reproduce this part. If I have two different sessions of psql connected to same database: psql-1# begin; psql-2# begin; psql-1# insert into foo ...; psql-2# insert into foo ...; <- ¡¡¡Frozen!!! psql-1# commit; <- psql-2 unfrozen Why I get this?. > Does this explain better? Very well, thanks ;) David ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety Hi, Well, the number is 'locked', because once it's given to you, that's it, it's gone from the 'list of available numbers' (i.e.: the sequence). However, between the insert, and the read of the ID, if another transaction performs an insert, it does NOT affect the ID that the first transaction reads (i.e.: your ID read in the first transaction IS definitely still safe, it will still read the correct one). AND, the first insert does NOT block the second insert. The second insert could complete and commit before the first one. Does this explain better? MikeA -Original Message- From: DaVinci [mailto:[EMAIL PROTECTED]] Sent: 13 February 2001 08:08 To: Lista PostgreSql Subject: Re: [GENERAL] transaction safety On Mon, Feb 12, 2001 at 10:22:30AM -0500, Tom Lane wrote: > DaVinci <[EMAIL PROTECTED]> writes: > > On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote: > >> Typically, the insert for a person, and for all the associated addresses > >> would be done in the same transaction so that if the insert for one of the > >> addresses failed, then the whole lot would role back (perhaps a bit extreme, > >> but I think that's what you asked for ;-) > > > I thought it is possible to have different transactions opened and insert > > data in same table from them. It seems my idea was fault, doesn't it?. > > In sumary: locks with inserts are for table and not for tuple. If this is > > not true, tell me details, please :) > > It's not true. How did you arrive at that conclusion from what Mike > said? I'll try to explain. Mike said: "in a transaction make an insert and then a read in serial current value". If in gap between those operations occurs another insert from different transaction, then reading serial is not safe. In order to understand this well I have made some basic experiments, freezing a transaction with an insert and making other transaction with an insert to the same table. Second gets frozen until first commit or cancel. That is reason of my last message: "locks with inserts are for table and not for tuple". Perhaps I didn't explain myself very well or there is some detail about locks that I don't understand at all. Thanks all for your time. David ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety
There should be no locks on the table for insert. The sequence number is retained by the session, not locked on the database. What this means is that somebody else may start their insert after you, and then you roll back, leaving a gap in the sequences. However, gaps in the sequences are not (or should not be) important. You can test this out using a few sessions of psql.
You need to view this with about 110 chars across, in fixed-width font (tab-width of 4 chars):
a$ psql test | b$ psql test
a=> begin; b=> begin; |
a=> insert into person (name, dob) |
a-> values ('Peter', '03/09/1945'); |
INSERT |
a=> select currval('person_id_seq'); |
1 |
| b=> insert into person (name, dob)
| b-> values ('Marge', '05/03/1967');
| INSERT
| b=> select currval('person_id_seq');
| 2
a=> select currval('person_id_seq'); |
1 |
a=> insert into address (id_person, address) |
a-> values (currval('person_id_seq'), ''); |
INSERT XXXY |
| b=> insert into address (id_person, address)
| b-> values (currval('person_id_seq'), '');
| INSERT XXXY
| b=> select currval('person_id_seq');
| 2
a=> rollback; |
ROLLBACK |
| b=> select currval('person_id_seq');
| 2
| b=> end;
| COMMIT
v
time
Now, although the left side has rolled back, the sequence number 1 is lost. However, this is not a problem, as the primary key requires uniqueness, but not continuity, and it means that your inserts do not block the table. Inserts can happen as fast as you can push data into the table.
Read up on sequences, because serial fields are based on sequences, thus avoiding the locking issue.
Hope this helps...
MikeA
-Original Message-----
From: DaVinci [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2001 13:43
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 01:08:01PM -, Michael Ansley wrote:
> The number returned by the sequence for the serial ID is retained within the
> session, and so it can be returned by calling currval, e.g.:
...[a detailed example]...
> Typically, the insert for a person, and for all the associated addresses
> would be done in the same transaction so that if the insert for one of the
> addresses failed, then the whole lot would role back (perhaps a bit extreme,
> but I think that's what you asked for ;-)
That sounds good ;)
I thought it is possible to have different transactions opened and insert
data in same table from them. It seems my idea was fault, doesn't it?.
In sumary: locks with inserts are for table and not for tuple. If this is
not true, tell me details, please :)
Thanks for all.
David
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
RE: [GENERAL] transaction safety
Title: RE: [GENERAL] transaction safety
Hi,
The number returned by the sequence for the serial ID is retained within the session, and so it can be returned by calling currval, e.g.:
CREATE TABLE person (
id serial primary key,
name varchar not null,
dob timestamp not null
);
CREATE TABLE address (
id serial primary key,
id_person int not null references person(id),
address varchar not null
);
INSERT INTO person (name, dob) VALUES ('Peter', '03/09/1945');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '44 Willowdown Road');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '23 Second Ave. (Second Home)');
INSERT INTO person (name, dob) VALUES ('Jane', '06/12/1958');
INSERT INTO address (id_person, address) VALUES (currval('person_id_seq'), '16 Parsons Crescent');
Typically, the insert for a person, and for all the associated addresses would be done in the same transaction so that if the insert for one of the addresses failed, then the whole lot would role back (perhaps a bit extreme, but I think that's what you asked for ;-)
Cheers...
MikeA
-Original Message-
From: DaVinci [mailto:[EMAIL PROTECTED]]
Sent: 12 February 2001 11:34
To: Lista PostgreSql
Subject: Re: [GENERAL] transaction safety
On Mon, Feb 12, 2001 at 11:08:55AM +, Oliver Elphick wrote:
> DaVinci wrote:
> > Hi all.
> >
> > I want to create a new tuple of main info and detail tuples (in
> > different tables) that are joined by a key field. Key field is created by
> > generator automatically and I need that number in order to assign to detail
> > tuples. How can I to get that number in a safe way?.
>
> A successful INSERT returns the oid of the row just created; so get the
> new value with a query like this:
>
> SELECT key_field FROM table WHERE oid = new_oid_value;
I have a new question for this idea (thanks). When Database is big, do i
need index for oid field to speed select?.
Greets.
David
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
RE: [GENERAL] Fw: [PHP] Fooling the query optimizer
Title: RE: [GENERAL] Fw: [PHP] Fooling the query optimizer Running this query: "SELECT * WHERE col3 = x" with a btree index on (col1, col2, col3) cannot be performed in an efficient manner, in any database, because you have specified the column order to be col1, col2, col3. If somebody claims that MySQL can do this, they're misunderstanding the problem, and/or solution, or there's some fudging going on by somebody. Imagine the index to look like this: col1 col2 col3 1 1 1 1 1 2 1 1 3 1 1 4 1 2 1 1 2 2 1 2 3 1 2 4 and a query which says "SELECT * WHERE col3 = 4". Now what order are you going to traverse the index in? Remember that you can only use col3, and have to binary search (btree index). If you binary split the index, then you have one 4 in one half, and one four in another, i.e.: it's not going to work. If MySQL claims it can do this, then the only way that I can think that they are doing this is by creating extra or separate indices behind the scenes, which is inefficient, and not particularly user friendly. Of course, they may have used GiST to create a special index for this ;-) and gotten it working, but I doubt it. Btree indices are by far the most common with simple data. For any btree index, the index can be used to the point where the index columns and the filter columns diverge, IN ORDER, e.g.: if your index is over columns a, b, c, d, and you filter on a, b, d, e, then the index can be used, only over columns a and b, though (not d!!). In the example cited, the first column in the index is not used in the filter, and so the complete index has to be ignored, i.e.: seq scan. Cheers... MikeA -Original Message- From: Adam Lang [mailto:[EMAIL PROTECTED]] Sent: 08 February 2001 16:27 To: PGSQL General Subject: [GENERAL] Fw: [PHP] Fooling the query optimizer On another list, someone posted this question. Are they correct, old problem, etc.? I'll pass whatever info there is back to the originating list. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com - Original Message - From: "Brent R.Matzelle" <[EMAIL PROTECTED]> To: "PostgreSQL PHP" <[EMAIL PROTECTED]> Sent: Thursday, February 08, 2001 10:41 AM Subject: [PHP] Fooling the query optimizer > Have any of you discovered a way to get around the current query optimizer > limitation in Postgres? For example, I have a table that has three columns > that I want to index for frequent search duties. In Postgres I am forced to > create three indicies: one including all three columns, one for col2 and > col3, and one for just col3. Databases like MySQL can use the first index > for these types of queries "SELECT * WHERE col2 = x AND col3 = y" and "SELECT > * WHERE col3 = y". Postgres could only perform queries on indicies where it > looks like "SELECT * WHERE col1 = x AND col2 = y AND col3 = z" and "SELECT * > WHERE col1 = x AND col2 = y" etc. However adding extra indexes as above > would decrease the write speed on that table because a simple insert would > require an update on all three indicies. > > Is there a way to fool Postgres to use the first index by creating a query > like "SELECT * WHERE col1 = * AND col3 = x"? I know I'm grasping for straws > here, but these issues can kill my database query performance. > > Brent ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: [GENERAL] Strange reference problem
OK, I see. The REFERENCES clause added to a column definition is a short
cut if you only have one column as the foreign key, similar to the PRIMARY
KEY clause, which you can add to a single column, but if you want a
multi-column primary key, then you have to do it at the end.
Thanks, Tom.
-Original Message-
From: Tom Lane [mailto:[EMAIL PROTECTED]]
Sent: 18 January 2001 02:21
To: Michael Ansley
Cc: '[EMAIL PROTECTED] '
Subject: Re: [GENERAL] Strange reference problem
Michael Ansley <[EMAIL PROTECTED]> writes:
> CREATE TABLE "swt" (
> "id" varchar(10) not null,
> "org" varchar(7) not null references "organisation"("id"),
> "description" varchar not null,
> primary key("id", "org")
> );
> DROP TABLE "poi";
> CREATE TABLE "poi" (
> "id" varchar(15) not null,
> "org" varchar(7) not null references "organisation"("id"),
> "name" varchar not null,
> primary key("id", "org")
> );
> DROP TABLE "tug";
> CREATE TABLE "tug" (
> "id" varchar(21) not null primary key,
> "description" varchar not null,
> "id_swt" varchar(10) not null references "swt"("id"),
> "swt_org" varchar(7) not null references "swt"("org"),
> "id_poi" varchar(15) not null references "poi"("id"),
> "poi_org" varchar(7) not null references "poi"("org")
> );
Those primary key declarations say that the combination of ID and ORG
together will be unique for each row of swt (ditto poi). They do not
promise that either ID or ORG will be unique by itself --- but that's
what the references clauses require to be valid. You probably want
to declare the references using a FOREIGN KEY clause that says that
the two-column pair id_swt, swt_org references the two-column pair
swt(id,org), and likewise for poi. AFAIK that's the only way to
define a multi-column reference key.
regards, tom lane
**
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
Nick West - Global Infrastructure Manager.
This footnote also confirms that this email message has been swept by
MIMEsweeper for the presence of computer viruses.
www.mimesweeper.com
**
application/ms-tnef
RE: [GENERAL] Re: 'Tuple is too big' Error
Title: RE: [GENERAL] Re: 'Tuple is too big' Error You can set the BLOCKSZ parameter up to a max of 32kB. This will increase the row length to 32kB (a bit shorter because of some admin overhead, but close enough). In the medium-term, upgrade to 7.1 as this limit has been removed. MikeA -Original Message- From: J.H.M. Dassen (Ray) [mailto:[EMAIL PROTECTED]] Sent: 15 January 2001 07:50 To: [EMAIL PROTECTED] Subject: [GENERAL] Re: 'Tuple is too big' Error On Thu, Jan 11, 2001 at 20:06:33 +0100, Christian Pomar wrote: > Using phorum (www.phorum.org) based on PHP, I am getting the following > error when trying to insert a message in the PosgreSQL 6.5.3 database: > > ProcessQuery > ERROR: Tuple is too big: size 12440 There is an 8K tuple length in that version of PostgreSQL. > (obtained from the log). Can anybody tell me where to find proper > documentation to raise whatever parameter is necessary to avoid this > problem? AFAIK it's not that easy to fix. It is fixed in the beta versions of 7.1. HTH, Ray -- Those who are willing to trade their liberty for security deserve neither. Benjamin Franklin ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
RE: [GENERAL] Regular expression question
Title: RE: [GENERAL] Regular expression question Yes, that's right :-0 Sorry! -Original Message- From: Steve Heaven [mailto:[EMAIL PROTECTED]] Sent: 11 December 2000 15:09 To: Michael Ansley; [EMAIL PROTECTED] Subject: RE: [GENERAL] Regular expression question At 14:58 11/12/00 -0000, Michael Ansley wrote: > Hmmm, what I proposed earlier (CREATE INDEX foo ON employees >(UPPER(firstname));) seems to work fine in 7.1 but not 7.0.2. Cheers... > MikeA I think you're anwering the wrong question. It was David Hancock <[EMAIL PROTECTED]> who was asking the upper/lower case question. Mine was about a metacharacter for word boundaries cf Perl's \b. Steve -- thorNET - Internet Consultancy, Services & Training Phone: 01454 854413 Fax: 01454 854412 http://www.thornet.co.uk ** This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify Nick West - Global Infrastructure Manager. This footnote also confirms that this email message has been swept by MIMEsweeper for the presence of computer viruses. www.mimesweeper.com **
[GENERAL] Object syntax
Title: Object syntax
Given the following table definitions, and data inserts:
dev=# create address (addr varchar(50), postcode varchar(9));
dev=# create client (name varchar(30), addr address);
dev=# insert into client values ('Michael');
dev=# insert into address values ('11 Windsor Close', 'RH16 4QR');
INSERT 18935 1
dev=# update client set addr = 18935::address;
dev=# explain select client.addr.postcode from client;
NOTICE: QUERY PLAN:
Seq Scan on client (cost=0.00..1.01 rows=1 width=4)
EXPLAIN
dev=# select client.addr.postcode from client;
ERROR: init_fcache: Cache lookup failed for procedure 18935
What's happening here? Bug, or am I doing something wrong?
Cheers...
RE: [GENERAL] sequences and Transactions
Title: RE: [GENERAL] sequences and Transactions You can't. Sequences are not designed for continuity, they are designed for uniqueness. If you want to have a set of contiguous numbers, in ascending order, then you will probably have to write a trigger to insert the next value, which it has to scan the table to work out. And you have to decide what to do in case of deletions: do you reuse the number on the next insert (add complexity and run-time to the code), or just carry on anyway, meaning that you have holes in your sequence, in which case, you could have used a sequence anyway, probably. Depending on the number of expected rows in the table, you may find that the time to insert doesn't justify having contiguous numbers. For each insert, the minimum you are going to get away with is a full table scan. MikeA >> -Original Message- >> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] >> Sent: 27 April 2000 09:39 >> To: [EMAIL PROTECTED] >> Subject: [GENERAL] sequences and Transactions >> >> >> >> >> > -- >> > From: [EMAIL PROTECTED][SMTP:[EMAIL PROTECTED]] >> > Sent: Thursday, April 27, 2000 10:38:55 AM >> > To: [EMAIL PROTECTED] >> > Subject: [GENERAL] sequences and Transactions >> > Auto forwarded by a Rule >> > >> Hi! >> >> how can I setup sequences to have the current-value reset in case >> of an Transaction rollback. >> >> My intension is to get an contignous numbering of the rows. >> Currently in case of an Rollback one number is skipped since >> the record itself is not inserted but the counter is not reset. >> >> Elmar >>
RE: [GENERAL] Embedded SQL -- ecpg
Title: RE: [GENERAL] Embedded SQL -- ecpg You send them through the ecpg pre-compiler first, which replaces all the EXEC SQL statements with C, and then compile. When you compile, make sure that you link to the correct libraries (RTFM). MikeA >> -Original Message- >> From: Nilesh A. Phadke [mailto:[EMAIL PROTECTED]] >> Sent: 01 April 2000 21:49 >> To: [EMAIL PROTECTED] >> Subject: [GENERAL] Embedded SQL -- ecpg >> >> >> >> >> > -- >> > From: Nilesh A. Phadke[SMTP:[EMAIL PROTECTED]] >> > Sent: Saturday, April 01, 2000 10:49:09 PM >> > To: [EMAIL PROTECTED] >> > Subject: [GENERAL] Embedded SQL -- ecpg >> > Auto forwarded by a Rule >> > >> hello , >> I am new to this embedded sql stuff. >> >> How do you compile the C program that has this EXEC SQL >> statements.?? >> >> Thanks in advance for the help, >> Nilesh. >>
[GENERAL] Add/Remove Columns
>>> Jackson, DeJuan wrote: >>> > > Martin Schulze wrote: >>> > > > . I wonder how one could add or remove columns from existing tables. >>> > > > >>> > > > With mSQL this was possible with a trick. You had to dump the >>> > > > whole table but you could tell the dump program to add dummy fields >>> > > > or to leave out existing fields. Is there any such possibility >>> > > > with PostgreSQL? >>> >>> > You can do a select into a temp table with the added columns, drop the >>> > old table the rename the temp table to the old name. You'll also have >>> > to recreate your indexes, triggers, and rules. >>> >>> I understand. Looks like PostgreSQL is more preconceived than >>> other databases. I figure out how to do this, though. I you would >>> have an example laying around I'd be very happy receiving it. Why don't you just use ALTER TABLE, eg: ALTER TABLE ADD COLUMN colname check (colname <= 1) and (colname >= 0) or something similar. If the column is a NOT NULL column, then you must have a DEFAULT option, otherwise, add it initially without the NULL option, populate it, and then ALTER TABLE ALTER COLUMN including the null option. Of course for removing columns you ALTER TABLE DELETE COLUMN, or possibly REMOVE COLUMN, I can't remember. MikeA
[GENERAL] Win32 libpq libraries
Hi, Is anybody aware of any port of the libpq* client libraries to the Win32 platform? If so, where can I get them, and is the source available (the source would be an added convenience). If there is nothing like this available, does anybody want? Are there enough people out there to warrant porting it myself? MikeA
[GENERAL] DB Design Tools
Hi, I'm reasonably new to PG SQL, and managed to get everything up and going without too many hassles (except for the -i switch - ha, bloody ha), but now I need to get down to some serious development. What is/are the tool(s) generally used for design work on PG, and where can I get hold of any them. Thanks MikeA
