Re: [GENERAL] Database design for separate tsearch table
On Thu, Oct 23, 2008 at 01:06:26AM +0200, Ivan Sergio Borgonovo wrote:
weight them and you'll be able to search by field and globally.
I didn't make any scientific test but I previously had something
like:
create table subtable (
subtableid int,
body text,
ftidx tsvector
)
create table maintable (
maintableid int,
body text,
subtableid int
ftidx tsvector
);
but it seem that just searching on a tsvector in maintable
build up with
setweight(to_tsvector('pg_catalog.english',
coalesce(maintable.body,'')), 'A') || ' ' ||
setweight(to_tsvector('pg_catalog.english',
coalesce(subtable.body,'')), 'B')
is faster.
Ok, that seems to be a good approach, thank you.
Beware of the difference between gist and gin indexes for
restricted weighted searches since with the latter you've to use
@@@
Um, could you clarify that? I know the general differences between gist
and gin, but not how it affects weighted searches...
Kind regards,
Mikkel H??gh
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Database design for separate tsearch table
Hi, I'm trying to make a module allowing Drupal to take advantage of PostgreSQL's excellent Full Text Search, aka. tsearch. Since this module will probably not become part of Drupal core right off the bat, I need to do this without modifying Drupal's own tables, so I've created a new one for the purpose, tsearch_node. I'm pondering how best to do this. Basic minimum is a column which holds the foreign key (nid) to Drupal's node. I'm wondering whether to merge all the searchable stuff when creating the index or to have a separate tsvector column for each of the three commonly indexable things about Drupal content (nodes), namely the title, the body and taxonomy. Any insights? Also, I figure it'd be a good idea to keep the language of the indexed content right there in the same table, since Drupals own node table specifies two-letter codes (en, da, pt, etc.) and as far as I can understand the PostgreSQL documentation, tsearch expects the full language name (english, danish, portuguese), so to use it in queries without having to do too much magic would require me to store the full name right there in the table, or am I mistaken? Kind regards, Mikkel H??gh -- 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] Postgres User and Password
On Fri, Jul 13, 2001 at 09:43:57PM -0400, some SMTP stream spewed forth: [...] My problem is with my Linux Posgres installations. I compiled as root and installed as root user. As root I can root@thinkpad root]# su postgres root@thinkpad root]# pg_ctl start ... Or use postmaster and successfully start the server.. As I have set all Path and Data enviormental variables. But, if I try this as a user Bob@thinkpad Bob]# su postgres . My results is password: Then no matter whats entered, even NULL Did you expect anything different? Trying to su from an unprivileged user to any user with no password should fail miserably. You must be root to su to a user with no passwd, e.g. root% su postgres postgres# ... I get su: incorrect password I had no password for User postgres but I finally gave it one but no help.. That indicates a greater problem with your system. Try resetting it so postgres has no password, and try su'ing as root. I made root and Bob both members of Group database.. no help. That should be irrelevant. Once server is started Bob is a postgres user and can start and use psql... This is not related. Very inconvenient to log in as root then su postgres then back to User login to development tools.. I have no idea why you would do that. How can I fix this problem.. You can have the server start automatically upon boot. Beyond that, you have no need to 'su postgres'. You can use psql as any user on the machine (provided the permissions are all set correctly). Can you be more specific about what the 'problem' is? Thank You Bob T gh -- What, no one sings along with Ricky Martin anymore? My kid sister does (but then, she prefers pico to vi ...) -- Suresh Ramasubramanian, alt.sysadmin.recovery ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] any disadvantage of PostgeSQL on Windows
On Tue, Jul 10, 2001 at 11:30:05AM +0800, some SMTP stream spewed forth: Would appreciate a lot if anyone could tell me if there is any disadvantage/performance issue in using PostgeSQL on windows. I know of no disadvantages that are not a direct result of Windows. Such things as the shared memory implementation, process switching, general sluggishness, gui (if it a strictly server situation), etc. Besides, PostgreSQL is fundamentally a 'unix thing'. It would be rather like running MS Access on FreeBSD, only better because PostgreSQL is Just That Good. That said, you should have no trouble at all, and performance should be reasonable. Ethan. -- What, no one sings along with Ricky Martin anymore? My kid sister does (but then, she prefers pico to vi ...) -- Suresh Ramasubramanian, alt.sysadmin.recovery ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Qn on best way to upgrade from 7.0.2 to 7.0.3 on production database
On Mon, Jul 09, 2001 at 05:52:21PM +0800, some SMTP stream spewed forth: [...] therefore I need to upgrade to 7.0.3. the doc says to do dump/restore when upgrading the database. however, since pg_dump has problem with timestamp data, how am I going to restore back the data after upgrade?!!! pls help. Thanks It is my understanding that no dump/restore is necessary for minor version upgrades (e.g. .2 to .3). You should be fine, but don't take my word for it. gh -- What, no one sings along with Ricky Martin anymore? My kid sister does (but then, she prefers pico to vi ...) -- Suresh Ramasubramanian, alt.sysadmin.recovery ---(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] Greetings, Thinking about converting
On Sun, Jun 10, 2001 at 06:32:58PM -0600, some SMTP stream spewed forth: Hi, I have a database with the 2 principal tables using 716,819 rows and 43,157,442 rows each one, related each one, I have some triggers and stored procedures and views having a frontend made in Visual Basic using ODBC and ADO to connect to the database, Im considering to move from Microsoft SQL Server 7.0 to PostgreSQL because this stuff its getting big and getting slower, Im backing up must of things but I would like to know if I can get some improvement changing from Win NT 4.0-MSSQL to Linux-Postgresql. What do you all think ? Should I change ? Yes. gh ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Greetings, Thinking about converting
On Sun, Jun 10, 2001 at 07:31:10PM -0600, some SMTP stream spewed forth: I suggest you check out FreeBSD: www.freebsd.org. If you have no experience with either FreeBSD or some Linux variant, I would say switch to FreeBSD, otherwise use whatever you are comfortable with other than Windows. PostgreSQL will run under Windows, but you should get better overall performance using Linux and much better performance using FreeBSD. Of course, if you need crappy hardware support, Linux is a hardware slut; but if you are working on a server farm or a serious, heavily loaded server, you would be better using high-end hardware in a FreeBSD box if not using Sun equipment and software which is probably not in your budget. By now im using a ProliantLM350 with a SmartArray 431 and 4 SCSI non-hotswap ultra3 disks, 20gb each one, 1gb memory , 2 933mhz procesors, I have had experience with Linux. You should be fine on Linux. I normally would strongly *not* say to use Linux over FreeBSD, but as I said, -current (like a beta, only better) has been in sad state lately, and I do not know what later releases are going to look like. It should still be better than Linux, but you shouldn't have to learn something new if everything sucks anyway. (Of course, the fine hackers behind PostgreSQL ensure that it runs well on just about anything, so performance should not be an issue.) gh Basically, all of the operating systems have been going to hell lately (FreeBSD included, I don't know what the developers are smoking, but damn...), so you should be fine on anything, even Windows. :/ ? too sad... It is unfortunate that everything has to suck so badly. Yes... Cheers. gh ---(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] Greetings, Thinking about converting
Opinion that you dont share as I see.. but, OS and hardware appart, what about the MSSQL vs MySQL vs PostgreSQL discussion. what you think ? General consensus is that MySQL is the fastest for simple selects. If you have a decent number of (more than 2 or 3) concurrent users, PostgreSQL will serve you better because a site with significant traffic will benefit from transactions and strong data integrity. If you care about your data, use PostgreSQL. I would use PostgreSQL anyway, just because it is overall better (which is a *very* subjective measure) and it /feels/ better. Furthermore, a stronger DBMS is more likely to serve your needs in the future. Speed is what I am lookin for In what type of situation are you planning to use this setup? gh (Not a very strong argument, but MSSQL /is/ made by Microsoft, afterall...) ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Ideal hardware configuration for pgsql/Netra
On Thu, May 03, 2001 at 12:52:24PM -0700, some SMTP stream spewed forth: It's sitting right here on my desk. Ask whatever you want. Yes they are better web servers than DBMS servers but if you database is small enough to cache in RAM then who care if the Netra uses slow disks? All that talk on this list about Linux vs. BSD is silly. Why bother when you can have Solaris 8 on SPARC hardware? Does anybody ever actually *READ* the $%@#-ing threads? I never said a word about Linux. RedHat does *NOT* equal LINUX! Somebody claimed that my post was uninformed...yet RedHat is all of Linux now? If you are going to refute a statement, please at least have the decency to READ it. Can we end this? gh ---(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] Unique or Primary Key?
On Wed, May 02, 2001 at 06:50:09PM -0700, some SMTP stream spewed forth: On Thu, May 03, 2001 at 12:58:03AM +0100, [EMAIL PROTECTED] wrote: This table is man-in-the-middle of a many-to-many relationship: CREATE TABLE cv_entries ( subscriber INTEGER NOT NULL REFERENCES subscribers ON DELETE CASCADE ON UPDATE CASCADE, entry_type INTEGER NOT NULL REFERENCES cv_entry_types ON DELETE CASCADE ON UPDATE CASCADE, ordinal INTEGER, value1 TEXT, value2 TEXT, minimum_trust SMALLINT, UNIQUE(subscriber, entry_type, ordinal) ); I used a unique index here because I couldn't see any reason for a Primary Key - this table will always be searched on either the subscriber or entry_type index. Was I wrong? Should this be a Primary Key? I think it's a distinction without a difference. A primary key is just a way to identify a unique tuple that's been chosen from a possible set of candidate keys (often there's only one candidate). And, primary keys are enforced with a unique index... Just to expand on Eric's response, a Primary Key directive creates a unique not null column, whereas a unique column can be null. In this instance, your columns are already not null, so, as Eric responded, it is basically the same thing. gh -- Eric G. Miller [EMAIL PROTECTED] ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl
Re: [GENERAL] last comma inside CREATE TABLE () statements
On Sun, Apr 22, 2001 at 07:44:46PM +0100, some SMTP stream spewed forth: Tom Lane wrote: Louis-David Mitterrand [EMAIL PROTECTED] writes: Is it against the SQL standard to accept a trailing comma in a table declaration? Yes ... CREATE TABLE "currency" ( currency_id varchar(3), rate float, BOOM! parse error ); As in perl, it would make life easier to simply ignore/accept a trailing comma on table declarations. ... however, this seems like a reasonable idea that would not introduce any major problems. I have no objections, if someone wants to submit a grammar patch. I can think of no place where this would be even remotely useful. Is it really that difficult to remove a comma? If it works now for *all* users of PostgreSQL, why should we change it? gh I suppose it isn't a major problem, but enforcing strict grammar helps to show up inadvertent errors. Suppose I have a set of schema building files for a whole system; the way I do things, there may be fifty or more files, one per table. If one of these gets corrupted in editing (perhaps a line gets deleted by mistake) it would be nice to know about it through a parser error. Of course, an error may be such that the parser won't detect it, but why remove protection by gratuitously departing from the standard? -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "If my people, which are called by my name, shall humble themselves, and pray, and seek my face, and turn from their wicked ways; then will I hear from heaven, and will forgive their sin, and will heal their land."II Chronicles 7:14 ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html ---(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] anti Christian bias?
On Wed, Apr 11, 2001 at 06:32:50PM +, some SMTP stream spewed forth: Hi all: On page 29 of the PostgreSQL User's Guide, distributed with version 7.0.3, in table 3-8 Postgres Date Input, the last item in the Example column is January 8, 99 BC. The corresponding Description item reads "Year 99 before the Common Era". The author or the editor of this manual is obviously expressing his anti Christian bias in attempting to redefine BC to mean Oh yes, obviously. Has society actually been reduced to this level of absurdity? Since when does Christianity cule the world and thus determine what abbreviations should be used to mean what? Furthermore, since when is acknowledgement of differing religious (and other) views considered "anti-Christian"? Please, we all have more important issues. When people stop killing each other we can worry about what BC is supposed to mean. I challenge you to stop them from killing each other. gh "Common Era". Throughout history BC, when associated with a date, has always stood for "Before Christ", and it always will. I challenge the author/editor to tell us exactly what is the significant event in history that marks the boundary of what he chooses to call "Common Era". Nick ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] point-in-time restore
On Tue, Apr 10, 2001 at 09:27:50PM +0200, some SMTP stream spewed forth: Is there a way to perform a point in time restore with postgresql ? Nope, not yet anyway, to the best of my knowledge. dan /Claes ---(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] FWD: OID
On Fri, Apr 06, 2001 at 09:11:38PM -0400, some SMTP stream spewed forth: I didn't get a response on my message, so I thought I would try again. 1. A large object has the data type OID. What then, is the data type of the instance object identifier? Is it an int4? I believe it is of type 'oid' which is (I believe) equivalent to an int4. 2. How would I create a table such the following select statement will return the instance object indentifier along with the other columns: SELECT * FROM table_name You would have to create a column of type 'oid' and insert manually (through the insert query) the 'instance identifier' (whatever that is, oid?) into that column. This would be redundant, the same as the oid column. Up to this point, I can only retrieve the object instance identifier by doing the following: SELECT oid,* FROM table_name Okay, so...? 3. Suppose, from tableA, I wanted a column that references an instance from tableB. I would like this reference to contain the instance object identifier of the instance from tableB. What data type do I use for this column? I suppose this is related to question 1. 'oid' Thanks for the help. I only hope that my information is correct. (That not only would be, but is, embarrasing.) dan Scott "Just a mirror for the sun... My smiling eyes are just a mirror for the sun." ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://www.postgresql.org/search.mpl ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [GENERAL] Inserting values into a SERIAL field
On Sun, Mar 04, 2001 at 05:22:46PM +, some SMTP stream spewed forth: Hi all, Is there any SQL statement to PostgreSQL that will allow me to insert rows with a specified value for the SERIAL value. I know this is not what you want to do normally, but I'm trying to provide a kind of general export and import function to a generic database. Since there exists foreign key values in instances of other tables that point into field values for a table that has a SERIAL id, I'd like to somehow import all this data and continue using the SERIAL functionality for the highest value of the id fields. I think what you want is insert into table (serial_col, whatever,...) values (...); dan Ian ---(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
[GENERAL] Standard Deviation/Variance function development
Has anybodys grabbed development of a standard deviation/variance calculation function? If not, I am interested in tacking it on my todo list. What would be involved? That is, should it be written in C or in PL/PgSQL, or what? (I imagine that C would be fastest, no?) Cheers, dan ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] database diff
On Thu, Mar 01, 2001 at 10:21:53AM -0500, some SMTP stream spewed forth: hello i want to find the differences between two database schemas...is there a function for this or do i just pg_dump both of them and do a diff in unix? As far as I know, such a function (or program/script) does not exist. It occured to me a few days ago that such a beast would be useful, but I have to finish the projects that are two months behind, first. ;-/ dan thanks chris
[GENERAL] select...except...union, Simpler query?
Can anybody think of a simpler way to run this query? Table layout: people pid (unique), name_last, name_middle, name_first -- with pid::serial p_phonenumber pid (not unique), phone_number -- with pid::int Query: (select p.pid, name_first, name_last, null::numeric(10,0) as phone_number from people p except select p.pid, name_first, name_last, null::numeric(10,0) as phone_number from people p, p_phonenumber pn where p.pid=pn.pid ) union (select p.pid, name_first, name_last, phone_number from people p, p_phonenumber pn where p.pid=pn.pid ) order by pid; I need a complete set of records (i.e. all of the records in people) that includes phone number/s for pid's that have it/them. A pid can have multiple phone numbers. It seems silly to select the total set, `except` what I need, and then `union` it with what I need. There must be a simpler way to do this, but I cannot find it. I thank you for your assistance. gh If it helps, here is the `explain` output: NOTICE: QUERY PLAN: Unique (cost=337.77..338.51 rows=7 width=44) - Sort (cost=337.77..337.77 rows=74 width=44) - Append (cost=0.00..335.47 rows=74 width=44) - Seq Scan on people p (cost=0.00..330.10 rows=61 width=28) SubPlan - Materialize (cost=5.37..5.37 rows=13 width=32) - Hash Join (cost=1.16..5.37 rows=13 width=32) - Seq Scan on people p (cost=0.00..1.61 rows=61 width=28) - Hash (cost=1.13..1.13 rows=13 width=4) - Seq Scan on p_phonenumber pn (cost=0.00..1.13 rows=13 width=4) - Hash Join (cost=1.16..5.37 rows=13 width=44) - Seq Scan on people p (cost=0.00..1.61 rows=61 width=28) - Hash (cost=1.13..1.13 rows=13 width=16) - Seq Scan on p_phonenumber pn (cost=0.00..1.13 rows=13 width=16) EXPLAIN
Re: [GENERAL] Re: php as stored procedures
On Wed, Jan 31, 2001 at 02:58:23PM -0500, some SMTP stream spewed forth: postgres support for pl/VB? :) Well, the most important, of course, would be pl/COBOL!!! gh Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com - Original Message - From: "Mitch Vincent" [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 31, 2001 1:54 PM Subject: [GENERAL] Re: php as stored procedures PHP is nice, but it's designed for a very specific purpose -- to write web based applications quickly. Having it in a DB is a little silly if you ask me.. Pl/pgsql is nice for smaller functions, C is great for everything else and of course, if you're a Perl monk, use it! :-) Pl/PHP is possible (simply because anything is possible) but I doubt you'll see any of the core developers doing anything of the sort though.. However, I'm sure they'd encourage anyone to write anything they wanted so go for it! Pl/Ruby - there is an idea *grin* -Mitch
Re: [GENERAL] How passwords can be crypted in postgres?
On Thu, Jan 04, 2001 at 10:53:12PM -0500, some SMTP stream spewed forth:
Gordan Bobic wrote:
Here's a crypted password: 00xNyXeahk4NU. I crypted it in perl as
crypt(guessme, salt). So what is guessme?
The point of a one way hash is that it's, well, one way. Pretty much
the only way you're going to figure out what password that encrypted
string corresponds to is to brute force it. Considering that I crypted
a fairly long random string, that could take you a while. A really long
while, unless you've got a budget orders of magnitude larger than most
people.
[snip]
Until the advent of shadow password files, which help defeat brute force
attacks of the type I just mentioned, the /etc/password file has been
readable by everyone. It really doesn't matter that much if people know
the crypted string. They still won't be able to authenticate themselves
until they know the real password.
So the problem you're trying to defeat by crypting your passwords is the
problem of someone reading your password file knowing all of your
passwords.
Now if you're dumb enough to send cleartext passwords unencrypted over a
public network, you need some schooling. And of course any programs
doing authentication need to be secure. But that's a different problem
altogether.
I was referring to a different aspect of security. I was referring to
preventing more of a "man-in-the-middle" type of attack. If you have a
packet sniffer somewhere between the client and the server, then someone
could read your packet containing the encrypted password and use it to
authenticate to the server, without knowing or caring what the real
password is. If you can send the encrypted password to the server that
matches, you're in.
How so? The server is going to take the string you send it, and one-way
hash it. If you send it the hash value, it will hash that. Unless that
happens to hash to itself, which is exceedingly unlikely, you will not
be authenticated.
What kind of system are you talking about?
It seems to me that the situation he describes would be one in which
the frontend recieves an auth key ("password" or whatever) and then
compares the hash of it to a value in the database. A similar situation
would arise if the frontend merely passed the auth key to the database
and the database hashed it and compared the hash to a stored hash.
It is really just a big mess.
Recieving a key, hashing it, and having the database hash the hash
might actually work reasonably well. But not really.
By hashing it, you increase the number of members in the set of
keys that the database would consider "valid" (or that would be
such that would allow access) provided that the database hashes the hash.
Otherwise, you are back in the same f'mess. Which you pretty much are
anyway, as the "key" simply becomes the hash of the "real" "password" if
you bypass the frontend. A someone else mentioned, a shared secret setup
would go far to avoiding a problem like this. But, if you cannot trust
your connection for a shared secret setup, you have a bigger problem. ;-)
Have fun.
gh
-Ron-
[GENERAL] Two tables refenceing each other's columns
Is something like the following allowed (or is not a Bad Idea)? table1 -+- id1 |serial primary key col2 |int references table2(id2) table2 -+- id2 |serial primary key col2 |int references table1(id1) Obviously, creating the tables is a problem since the constraints require that the other table exists. If doing the above is *not* a Bad Idea, how could I work around this problem? (That is, (how) can I add the constraints after table creation? I imagine something with "create constraint trigger", but the manual is not very clear on that.) Thanks dan
Re: [GENERAL] Two tables refenceing each other's columns
On Tue, Jan 02, 2001 at 02:27:28AM -0500, some SMTP stream spewed forth:
Here is some code I played with before. It does what you want. Just make a
new database to try it in.
Great, thanks.
I ended up working around it by storing one of the primary keys
in another table with some other information (which is a better way
to have the tables setup, in my case).
I am glad to have your code on hand though.
Thanks
dan
-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'
LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';
CREATE TABLE test1 (id INTEGER PRIMARY KEY);
CREATE TABLE test2 (id INTEGER PRIMARY KEY);
ALTER TABLE test1 ADD CONSTRAINT test1_id_fk
FOREIGN KEY (id) REFERENCES test2
ON UPDATE CASCADE
ON DELETE CASCADE -- can delete test2
INITIALLY DEFERRED;
ALTER TABLE test2 ADD CONSTRAINT test1_id_fk
FOREIGN KEY (id) REFERENCES test1
ON UPDATE CASCADE
ON DELETE RESTRICT -- disallows delete test1
INITIALLY DEFERRED;
CREATE SEQUENCE test_id_seq;
CREATE FUNCTION new_tests() RETURNS INTEGER AS '
DECLARE
new_seq INTEGER;
BEGIN
new_seq := nextval(''test_id_seq'');
INSERT INTO test1 VALUES (new_seq);
INSERT INTO test2 VALUES (new_seq);
RETURN new_seq;
END;
' LANGUAGE 'plpgsql';
-- implicit BEGIN;
SELECT new_tests();
-- implicit COMMIT;
SELECT new_tests();
SELECT new_tests();
SELECT * FROM test1;
SELECT * FROM test2;
DELETE FROM test1 WHERE id = 1; -- this will fail
DELETE FROM test2 WHERE id = 1; -- this will succeed and cacade
SELECT * FROM test1;
SELECT * FROM test2;
On Tuesday 02 January 2001 01:57, GH wrote:
Is something like the following allowed (or is not a Bad Idea)?
table1
-+-
id1 |serial primary key
col2 |int references table2(id2)
table2
-+-
id2 |serial primary key
col2 |int references table1(id1)
Obviously, creating the tables is a problem since the constraints require
that the other table exists.
If doing the above is *not* a Bad Idea, how could I work around this
problem?
(That is, (how) can I add the constraints after table creation?
I imagine something with "create constraint trigger", but the manual is
not very clear on that.)
Thanks
dan
--
Robert B. Easter [EMAIL PROTECTED] -
- CompTechNews Message Board http://www.comptechnews.com/ -
- CompTechServ Tech Services http://www.comptechserv.com/ -
-- http://www.comptechnews.com/~reaster/
Re: [GENERAL] What's faster: value of 0 or NULL with index
On Mon, Dec 11, 2000 at 04:28:24AM +0100, some SMTP stream spewed forth: Hi, I'm thinking about, what might be faster on SELECTs: a column with index which is NOT NULL and takes the value of 0 or a column which can take the NULL value instead of 0, also with index. My feeling sais, that 0 and NOT NULL should be a lot more faster, but perhaps it's not true? If your SELECT uses the index on the NOT NULL column, then yes, the indexed 0 should be faster. I think it takes less space as well.(?) gh bye Alvar -- Alvar C.H. Freude | [EMAIL PROTECTED] Demo: http://www.online-demonstration.org/ | Mach mit! Blast-DE: http://www.assoziations-blaster.de/ | Blast-Dich-Fit Blast-EN: http://www.a-blast.org/ | Blast/english
Re: [GENERAL] Trigger Problems?
On Thu, Dec 07, 2000 at 03:40:00PM -0600, some SMTP stream spewed forth: Ok, so I'm not sure why this is the way it is, but I figured out why the backend was failing every time I tried to change the data on the existing five tables. When I dropped the five unused tables, I assumed that the DROP function would also remove the foreign key constraint triggers that were associated with the dropped tables. But, in fact, they were left in the db. Therefore, every time any update or delete was performed on the existing tables, the constraint trigger fired and failed. I don't know if this was just my stupidity and I should have know to delete these triggers or if this is a bug. Either way, I learned something new. Other than that, why would the connection die? It seems like an error would be better. gh Thanks, Darrin -Original Message- From: Darrin Ladd [EMAIL PROTECTED] To: [EMAIL PROTECTED] [EMAIL PROTECTED] Date: Wednesday, December 06, 2000 12:03 PM Subject: [GENERAL] Trigger Problems? Hello, I sent an e-mail to the mailing list yesterday, but then realized that I sent it using my other account, which may not be able to post to the list. It seems that I have found myself in a little bit of trouble. I was wondering if there is anyone out there that can help me. Unfortunately, I am quite sure that I will not provide enough information to diagnose the problem, because I don't really know where to start. But, here is what I do know: Yesterday, I decided that it was time to get rid of five tables in my db that were not being used. I proceeded to drop all five tables. I then remembered that there were insert triggers on existing tables which called a function which updated the dropped tables. So then I dropped the function and the triggers. All of this seemed to be successful, but then I hit a serious problem. Any attempt to manipulate data (anything but a select) in the still existing tables which had the triggers removed from them aborts with the following error: pqReadData() -- backend closed the channel unexpectedly. This probably means the backend terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. !# Obviously, this is not good. I have vacuum analyzed the db and that seemed to have processed fine. If someone could point me in a good direction to figure out what is causing this, I would greatly appreciate it. I am not opposed to dropping the bad tables and restoring from backups, but I am still interested in finding out what is wrong and what I did wrong so that I don't do it again. Thank you very much for your help! Darrin
[GENERAL] Postgres demographics?
Has anybody collected information about what people use Postgres how and to do what? I think it would be interesting to see where our Collective got its roots. Personally, I came from a PHP background using MySQL. An eCommerce (oh, I hate that word) project stretched the limits of MySQL and Postgres fit the bill very nicely. I was somewhat hesitant due to the massive anti-Postgres propaganda spread by just about everybody, but I am glad I made the switch and would not consider using MySQL for any but the simplest and least likely to grow project. Hats off to -core and to other developers as well as to the community. We have a Good Thing. gh
Re: [GENERAL] Overhead of tables.
On Tue, Dec 05, 2000 at 09:34:19PM -0800, some SMTP stream spewed forth: At 11:48 PM 12/5/2000 -0500, you wrote: Soma Interesting [EMAIL PROTECTED] writes: I'd like to get an idea of the overhead introduced by large quantity of tables being hosted off a single PostgreSQL server. It is possible I'll be wanting to host upwards of 200-500 tables per server. Essentially, will I be surprised to find out that performance in PostgreSQL (or DBMS in general) is significantly hindered by sheer quantity of tables? When you get to tens of thousands of tables per server, we might start to worry a little... 500 is in the "what me worry?" class. regards, tom lane That is what I'd expect - but I've not experienced it to really know first hand. Thanks for the input. If a couple more people would just say the same thing - I could rest easy about moving forward on this. :) Well, based on my experience at this point, Tom Lane's comments/suggestions are worth those of several people. Consider it as if I have said the same as he. ;-) gh
Re: [GENERAL] Your RDBMS Survey ...
On Mon, Dec 04, 2000 at 12:31:06AM -0400, some SMTP stream spewed forth:
A couple of days ago, one of our brethren noticed and pointed us to your
survey asking which RDBMS we prefered/were using ... pride in our choice
prompted alot of us to pop over to your site and register our vote
... when I put mine in, the results were at something like 1461 for PgSQL
and now they are down to 510 ...
\begin{Devils Advocate}
Maybe they are attempting to compensate for a spike in the number of
votes in favor of Postgres. Was there a group of votes from a single ip
or some such?
\end{Devils Advoate}
do
{
sleep();
} while(tired())
gh
one of our brethren has seen it be reset back to 450 at least twice so far
...
I'm curious ... is this survey supposed to have any accuracy to it, or are
the results fixed? *raised eyebrow*
Marc G. Fournier ICQ#7615664 IRC Nick: Scrappy
Systems Administrator @ hub.org
primary: [EMAIL PROTECTED] secondary: scrappy@{freebsd|postgresql}.org
Re: Re[2]: [[GENERAL] Please vote for postgresql!!!]
On Sun, Dec 03, 2000 at 04:14:48PM -0600, some SMTP stream spewed forth: looks like it backfired...they've reset the graph Sheesh! Apparently *everybody* wants a recount. gh -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Bruno Dickhoff Sent: Sunday, December 03, 2000 3:11 PM To: Bill Barnes Cc: [EMAIL PROTECTED] Subject: Re[2]: [[GENERAL] Please vote for postgresql!!!] Hello Bill, Sunday, December 03, 2000, 9:18:12 PM, you wrote: BB Thanks for the tip. Looked good enough to sign up for an account. BB The numbers I saw gave postgesql a significant lead. So, my appeal for voting did it's job! Before I posted the mail, mysql was leading at 1200 to 450 (and 430 for interbase). Now it is 1461 for pgsql, 1.254 for mysql and 432 for interbase... I hope all of us will frequently take a look at http://www.mycgiserver.com so things won't get turned upside down again ;-)). -- Best regards, Brunomailto:[EMAIL PROTECTED]
Re: Re[2]: [[GENERAL] Please vote for postgresql!!!]
On Mon, Dec 04, 2000 at 02:19:36PM +1100, some SMTP stream spewed forth: On Sun, Dec 03, 2000 at 09:20:51PM -0400, The Hermit Hacker wrote: just re-submit'd my vote ... maybe mycgiserver already has mysql installed and is only doing the vote to satisfy some ppl, but don't really want to install PgSQL? *raised eyebrow* The counter was reset to 450ish twice just while I've been watching. They mustn't be too keen on installing it... Er, do they not suppose that people are watching them do these things? gh -- Matt Beauregard Information Technology Operations, DesignScape Ph: +61 2 9361 4233 Fx: +61 2 9361 4633
Re: [GENERAL] How to view the code in a function??
On Tue, Nov 28, 2000 at 02:43:08PM -0500, some SMTP stream spewed forth: Hello, I am sure someone probably knows this, I have used the \d tablename quite a lot lately, but I was wondering how to view the code in a function. Is there a way to get the definition? I assume you are using '\d' in psql. Add the '-E' option when you start psql and it will show the specific commands that it uses to expand things like '\d'. It is a bit messy, though. gh thanks adam
[GENERAL] Bug? 'psql -l' in pg_ctl?
Er, did anybody notice trouble when starting Postgres if a password has
been set for user pgsql?
On my FreeBSD 5.0-2528-CURRENT box, psql -l (from pg_ctl)
sucks down a big 28% cpu.
Here is the relevant section of the startup script:
start)
[ -d /usr/local/pgsql/lib ] /sbin/ldconfig -m /usr/local/pgsql/lib
[ -x /usr/local/pgsql/bin/pg_ctl ] {
su -l pgsql -c \
'exec /usr/local/pgsql/bin/pg_ctl -w start /usr/local/pgsql/errlog
21'
echo -n ' pgsql'
}
;;
Postgres will start, but I still have that psql -l process cycling
waiting for a password...
errlog shows "DEBUG: Data Base System is in production state..."
Ah hah, I think I founnd the source of this and the file descriptor
problem (for those of you who are familiar with it):
in pg_ctl there is a loop to check if the postgres server is "accessible"
yet:
# wait for postmaster starting up
if [ "$wait" = 1 ];then
cnt=0
$ECHO_N "Waiting for postmaster starting up.."$ECHO_C
while :
do
if psql -l /dev/null 21
then
break;
else
$ECHO_N "."$ECHO_C
cnt=`expr $cnt + 1`
if [ $cnt -gt 60 ];then
$ECHO "$CMDNAME: postmaster does not start up"
exit 1
fi
sleep 1
fi
done
$ECHO "done."
fi
That damn loop has brought down two of my servers already.
It seems like a bloody bad idea to have such a thing in a startup script,
eh? Or am I maybe just not supposed to set a password for pgsql?
Either way, any suggestions?
gh
Re: [GENERAL] Bug? 'psql -l' in pg_ctl?
On Sun, Nov 26, 2000 at 10:53:24PM +0100, some SMTP stream spewed forth: GH writes: Either way, any suggestions? Personally, I don't trust pg_ctl farther than I can throw it. Consider not using it. Heh, I certainly have...but defaults that lock up servers? Erm, that's a Bad Thing(tm). gh -- Peter Eisentraut [EMAIL PROTECTED] http://yi.org/peter-e/
Re: [GENERAL] Bug? 'psql -l' in pg_ctl?
On Sun, Nov 26, 2000 at 10:24:28PM -0500, some SMTP stream spewed forth: Tatsuo Ishii [EMAIL PROTECTED] writes: Probably we really need here is a kind of ping tool for PostgreSQL, *snip* (BTW, a short-term answer for grasshacker is not to use -w in his pg_ctl start script ...) Heh, I just went and commented out the whole if block. ;-) regards, tom lane
[GENERAL] Postgres and AOLServer
Does anybody know if PHP would do connection pooling to Postgres as a module under AOLServer? If it would, does anyone have any idea how to configure AOLServer to connect with pools to Postgres? My thinking was that the threaded nature of AS would enable PHP to do connection pooling. Is it not the webserver that does the actual connection to the database rather than any module such as PHP? Thanks. gh
Re: [GENERAL] Automatic Addslashes
On Fri, Nov 24, 2000 at 01:29:41PM +0100, some SMTP stream spewed forth: I do copy tmp from '/home/algobit/product.txt tmp ia a table with one record (line varchar(255)) in product.txt I have many line as: 2;"100";"Parmigiano Reggiano";"Parmigiano Reggiano stagionato 2 anni";4;"parmigiano4.jpg";2;"Fette 1 Kg";0.18;1;1.00;"true" Then with "select * from tmp" the result is exactly like in the original file 2;"100";"Parmigiano Reggiano";"Parmigiano Reggiano stagionato 2 anni";4;"parmigiano4.jpg";2;"Fette 1 Kg";0.18;1;1.00;"true" But when i fetch in a variable the rercords (with php) i READ in the variable 2;\"100\";\"Parmigiano Reggiano\";\"Parmigiano Reggiano stagionato 2 anni\";4;\"parmigiano4.jpg\";2;\"Fette 1 Kg\";0.18;1;1.00;\"true\" the question is : The SLASH are effectively in the record ? Not if, as you say, a regular non-php select returns the "original" record. Likely, your PHP setup has magic_quotes_runtime (or magic_quotes_gpc) set to On. This would cause PHP to "addslashes()" to the data gathered from the db (also true for forms, etc). Make sense? You can use get_magic_quotes_runtime or get_magic_quotes_gpc to retrieve the current setting of the two options. http://www.php.net/manual/configuration.php#ini.magic-quotes-gpc gives a nice summary. cheers. gh
Re: [GENERAL] plz, Help!! HOW CAN I GET THE ROWNUM IN POSTGRES TABLE AS I DID WITH ORACLE!!
On Fri, Nov 24, 2000 at 08:35:34AM +0300, some SMTP stream spewed forth: On Mon, Nov 20, 2000 at 12:44:20PM +0800, BaiJie wrote: I KNOW THERE IS A OID FIELD ,BUT IT 'S WIRED , NOT A INTEGER INDEX FROM I TO CURRENT ROW NUMBER!! HOW CAN I GET THE TRUE ROW NUMBER IN POSTGRESQL!! At first, don't use all caps. Heh, I was waiting for something like that. -- Igor' Robul', Unix System Administrator Programmer @ sanatorium "Raduga", Sochi, Russia http://www.brainbench.com/transcript.jsp?pid=304744
[GENERAL] [NOVICE] Skipping numbers in a sequence.
Hello all.
It's like this. ;-)
I have a table with a column that has a default nextval('sequence').
The sequence is a standard increment 1 cycle sequence.
What happens when
the sequence wraps after inserting the
2-million-and-whatever-th row
some of the earlier
rows (say, 1-100) are still in the table
but other rows or sections (say, 101-110 and 120-125) have
been deleted
and I need to begin with the first un-used sequence number?
I suppose that I would need to find the first un-used sequence number,
use setval() to update the sequence, and then insert the row that needs
to be insert-ed. Well, how can I find the first un-used sequence number?
I thought about doing something using a function like
select sequence_column from table
NOT [the set of numbers that make up the sequence]
but, how do I select the set of numbers that make up the sequence?
Is there a better/cleaner/easier way of getting the end result?
Postgres 7.0.2 (should be running .3, *sigh*)
FreeBSD 4.1-RELEASE
PHP 4.0.3pl1
Thanks fellas (and ladies, if applicable).
gh
[GENERAL] Weird table permission stuff.
Running 7.0.2 on FreeBSD 4.0-RELEASE. After creating a database and any number of tables, the situation is thus: The owner of the database and table may run rampant on any tables that have *no* permissions granted. On tables with permissions granted to anyone other than the owner, access is refused to anyone except the owner. Er, that is supposed to happen, correct? Thanks. gh
Re: [GENERAL] Script Location
On Thu, Nov 16, 2000 at 12:22:34AM -0500, some SMTP stream spewed forth: When I dump a database (creating a script from the pg_dump command), I can't just put it in any old directory and point psql at it. I have to put it in the /var/lib/pgsql subdirs before postgresql will accept it. Is this right? Am I doing something wrong? Is this a bug^H^H^Hfeature? Paul M. Foster http://www.postgresql.org/docs/user/app-pgdump.htm Description: ...pg_dump will produce the queries necessary to re-generate all user-defined types, functions, tables, indices, aggregates, and operators... Note the limitations (if you have not already done so). http://www.postgresql.org/docs/admin/backup.htm#AEN2278 Restoring The text files created by pg_dump are intended to be read in by the psql program. The general command form to restore a dump is psql dbname infile where infile is what you used as outfile for the pg_dump command. The database dbname will not be created by this command, you must do that yourself before executing psql (e.g., with createdb dbname). psql supports similar options to pg_dump for controlling the database server location and the user names. See its reference page for more information. To answer your question: You seem to misunderstand pg_dump (?). To restore from a dump, just reate the database and psql [options] dumpfile The dumpfile can be anywhere (accessible to your user). It actually does *not* belong under the pgsql directory. That sounds correct.. G'luck and cheers gh
