Re: [GENERAL] Data version idea (please discuss)
Michael Glaesemann wrote: From what I gather, the SQL TSQL2 discussions ended without any conclusion as to extending SQL in this direction. Darwen's critique of TSQL2 is here: http://www.hughdarwen.freeola.com/TheThirdManifesto.web/OnTSQL2.pdf I'm not sure if Snodgrass ever replied to it. The working draft's Part 7 was SQL/Temporal: http://www.jtc1sc32.org/sc32/jtc1sc32.nsf/f3b9a582bbf35d33852566210054191a/115c37d71e64bfd188256a5b00442b3e?OpenDocument There's a bunch of temporal related doucments from the TimeCenter: http://www.cs.auc.dk/TimeCenter/pub.htm Partial indexes get me close to where I want with temporal features. I just wish the RI constraints had the ability to supply a WHERE clause. Between the two, it might get me were I want, rather than having to write triggers to ensure temporal integrity. Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Data version idea (please discuss)
Mike Thanks for the links! I remember coming across a TimeCenter paper. The TimeCenter reference page is quite extensive and I look forward to reading more of the work Darwen and Date critiqued. On Aug 6, 2004, at 6:37 PM, Mike Mascari wrote: Partial indexes get me close to where I want with temporal features. I just wish the RI constraints had the ability to supply a WHERE clause. Between the two, it might get me were I want, rather than having to write triggers to ensure temporal integrity. Would you mind going into more depth into how you're doing this? Michael Glaesemann grzm myrealbox com ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] getting dead locks with 2 functions
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Amir Zicherman wrote: | i am running multiple threads that are calling this function at the | same time. i want to be able to do that and have the locking in | postgresql take care of locking the selected rows of each thread. why | is the function not thread safe? how do i make it so it is? | | thanx, amir | | On Fri, 06 Aug 2004 10:54:07 +0200, Gaetano Mendola <[EMAIL PROTECTED]> wrote: | | Amir Zicherman wrote: | | | I have the following 2 functions and I'm getting deadlocks when I call | | | | them from multiple threads. The first, I'm not sure why because I'm | | doing a select for update. The second I'm doing an insert on, and I | | thought insert will automatically do a lock as it inserts: | | | | -FUNCTION 1: - | | | | CREATE OR REPLACE FUNCTION | | public.select_pend_visitation_for_unvisited_links(int4) | | RETURNS SETOF record AS | | ' | | DECLARE | | urlrow RECORD; | | BEGIN | | | | FOR urlrow in EXECUTE \'SELECT * FROM "URL" WHERE visited=1::int2 | | LIMIT \' || $1::int4 || \'FOR UPDATE\' | | LOOP | | UPDATE "URL" SET visited=2 WHERE "URLID"::int8 = | | urlrow."URLID"::int8; | | RETURN NEXT urlrow; | | END LOOP; | | RETURN; | | END; | | ' | | LANGUAGE 'plpgsql' VOLATILE; This function *is* thread safe. The only fault here is that this function not "designed" to be used in an multithread environment because you are not taking any "policy" for locks resources. Lock always the line in the same order so you avoid cyclic locks dependencies. Your select must appear like this: SELECT * FROM "URL" WHERE visited=1 ORDER BY oid LIMIT $1 FOR UPDATE; Normaly this shall solve your problem. Regards Gaetano Mendola -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.4 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFBE1bm7UpzwH2SGd4RAlXrAKC8a7vuDnxspfWC42/8JObgSpTcfwCeIYI0 a0z0pj9ahiyJIYOz3t8wLUY= =syCe -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] case insensitive sorting & searching in oracle 10g
Stephan Szabo wrote: Could you point me where in the archives can I read more? I'm having a bit of trouble finding discussion on this. Thanks. I didn't spend too much time looking, but there are a few that look like they'll touch upon related issues: http://archives.postgresql.org/pgsql-hackers/2003-11/msg01299.php http://archives.postgresql.org/pgsql-hackers/2001-11/msg00610.php http://archives.postgresql.org/pgsql-hackers/2002-11/msg00515.php So, as I understand it, the current plan is: 1. charset + encoding will be tagged to each column (as per SQL standard) 2a. individual string values will be tagged with charset+encoding. this incurs an overhead of 1-2 bytes per value. or 2b. all string values will be stored in a single charset+encoding (e.g. unicode + utf8). this will of course upset some people, e.g. japanese. Is it 1+2a or 1+2b? Recent language implementations/VM like Parrot and Ruby2 are inclined to 2a, I think. -- dave ---(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] case insensitive sorting & searching in oracle 10g
David Garamond wrote: 2a. individual string values will be tagged with charset+encoding. this incurs an overhead of 1-2 bytes per value. forgot to add: this overhead is just for "in-memory" or temporary value (e.g. when being passed as arguments). in the storage itself, this is not needed because charset+encoding is recorded in the column definition. -- dave ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Where are all the users?
Kay-Uwe Genz <[EMAIL PROTECTED]> wrote: > > Hi @ all, > > I want to reference the User-ID PG use as an FOREIGN KEY in a tabel of > my DB. But I saw that pg_user is a view. Where are the information I > need? Maybe you could \d the view and find out? *But* you have to have the proper permissions to actually see/reference the data. Jim ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] Is olap possible on postgresql????
I'm a student currently working on a postgresql based project. I want to know whether olap technologies are compatible with postgresql and would like some links to sites that could give me more info on this subject. What is the latest on olap in postgresql __ Do you Yahoo!? Take Yahoo! Mail with you! Get it on your mobile phone. http://mobile.yahoo.com/maildemo ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Data version idea (please discuss)
On Mon, 2004-08-02 at 16:09 -0700, webb wrote: > What I am curious about is versioning the data that goes into this > database using something that I want to call a "checkpoint". :-) How about using CHECKPOINT or SAVEPOINT in postgresql 8.0? (in beta now) http://developer.postgresql.org/docs/postgres/sql-checkpoint.html http://developer.postgresql.org/docs/postgres/sql-savepoint.html There are some examples in postgresql 7.5 new features announcements over the net. -- Marius Andreiana Galuna - Solutii Linux in Romania http://www.galuna.ro ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Data version idea (please discuss)
Michael Glaesemann wrote: Thanks for the links! I remember coming across a TimeCenter paper. The TimeCenter reference page is quite extensive and I look forward to reading more of the work Darwen and Date critiqued. Sorry for the redundant Darwen critique link. On Aug 6, 2004, at 6:37 PM, Mike Mascari wrote: Partial indexes get me close to where I want with temporal features. I just wish the RI constraints had the ability to supply a WHERE clause. Between the two, it might get me were I want, rather than having to write triggers to ensure temporal integrity. Would you mind going into more depth into how you're doing this? It's rather crude. I've a start and end date on all temporal relations. I've a surrogate key as well. The uniqueness of the actual candidate key is enforced by a partial index only over tuples whose end date IS NULL. I maintain "temporal integrity" through ON UPDATE triggers. When a temporal tuple is deactivated, all relations that have the soon-to-be deactivated tuple's surrogate key are also deactivated. I also have a corresponding view for each temporal relation that is queried by non-reporting user interfaces. They see the active tuples, while the reporting component queries the base relations for the entire history. However, this is all maintained by a series of spaghetti C-language SPI routines, and my own home-brewed version of pg_constraint, as opposed to nice declared referential integrity, for the obvious reasons. Mike Mascari ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
[GENERAL] PostgreSQL 7.4.2 allows foreign key violation
Hi,
On PostgreSQL 7.4.2 I can create a situation in which a foreign key is
violated:
bug=# SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
name | ?column?
--+--
xxx | f
(1 ÑÑ)
bug=# \d+ b
ÐÑÐ "public.b"
ÐÐÐ | ÐÐÐ | ÑÐÐÐÑÐÑÑ | ÐÐÐÑ
++--+--
name | text | not null |
ÐÑÑ:
"b_pkey" ÐÐÑÑ , btree (name)
ÐÐÑÐÐÐÑÐÐÐÑ ÐÐ ÐÑÐÑÐÑÐÐÐÑ ÐÐÑÑÑ:
"$1" FOREIGN KEY (name) REFERENCES a(name) ON UPDATE CASCADE
ÐÑÐ:
b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = new.name WHERE (a.name =
old.name)
I create the situation as follows:
CREATE TABLE a (name TEXT PRIMARY KEY);
INSERT INTO a VALUES ('xxx');
CREATE TABLE b (name TEXT PRIMARY KEY REFERENCES a(name) ON UPDATE CASCADE);
INSERT INTO b VALUES ('xxx');
CREATE RULE b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = NEW.name WHERE
name = OLD.name;
UPDATE b SET name = 'yyy' WHERE name = 'xxx';
SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
name | ?column?
--+--
xxx | f
(1 ÑÑ)
Up to here I thought that the following was going on: The UPDATE b
statement was rewritten into a UPDATE a statement by the rule system.
The update on a triggers the foreign key update on b. This UPDATE gets
rewritten again by the rule system to update a instead. The update to a
triggers the foreign key again, which recognizes that it is already
running and does nothing. The outer foreign key is done and the update
to a is realized. b stays unchanged.
But then I discovered that if I update the row in a prior to creating
the rule, the rule works as expected:
CREATE TABLE a (name TEXT PRIMARY KEY);
INSERT INTO a VALUES ('xxx');
CREATE TABLE b (name TEXT PRIMARY KEY REFERENCES a(name) ON UPDATE CASCADE);
INSERT INTO b VALUES ('xxx');
UPDATE a SET name = 'zzz' WHERE name = 'xxx';
CREATE RULE b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = NEW.name WHERE
name = OLD.name;
UPDATE b SET name = 'yyy' WHERE name = 'zzz';
SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
name | ?column?
--+--
yyy | t
(1 ÑÑ)
This somehow renders my theory invalid. Can someone comment?
I also tried the same rule without INSTEAD. That does what I want and it
is what I'm using in the application now. I wonder if that is The Right
WayÂ.
And should PostgreSQL allow foreign key violations like in the example
above?
Thanks
--
Markus Bertheau <[EMAIL PROTECTED]>
---(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] New to Postgres
--- Darkcamel <[EMAIL PROTECTED]> wrote: > Hello all, > > I am new to postgres and don't really understand > how the database is > set-up. I am very fluent with mysql and sql2000, > but postgres is new to > me. If anyone can point me to some good links I > would appreciate it very > much. This is worth a book, and there are several written for that purpose. I will try a summary. First, you need a working PostgreSQL installation. If you do not have this now, I suggest looking for a package for your platform, rather than attempting to compile source yourself (unless you are feeling adventurous). Make sure you have some user(s) and database(s) set up to play with. Example (assuming that the database setup was initialized by user "postgres", which is usually the case): su postgres #you probably need to su to root first createuser myuser createdb mydb Next, look over the documentation (easiest just to go to http://www.postgresql.org for that). Don't spend too much time reading the content at first; instead, spend some time familiarizing yourself with the layout of the contents. Once you become a little familiar with the layout of the docs, you will find that you will be easily able in most cases to drill down and find answers to your questions. Then, just jump in and start playing. Connect to the server, and log into the database using "psql [databasename]". Psql is something like Oracle's sql*plus; "man psql" will tell you more. From the psql prompt, you can issue SQL commands. There are many psql-specific commands, which you can list by typing "\?" at the psql prompt. Some examples: \dt list all tables \d tablename List structure (fields, datatypes etc.) for "tablename". \h command Show syntax for SQL command "command" If you have been using another RDBMS, you can learn a lot by attempting to import a dump from that system into PostgreSQL. Depending on how many vendor-specific extensions are in use, you may find that you can do it with little (or possibly no) editing of the dump file. To import a dump file, just do (from the OS command line): psql databasename < dump.file This, plus a little research, should be enough to get you started. The docs are the definitive source for information. You will also find lots of useful stuff at http://techdocs.postgresql.org. If you have specific questions that you are having trouble finding answers to, post them to the list. Good luck. > > Thanks, > > Darkcamel > > ---(end of > broadcast)--- > TIP 4: Don't 'kill -9' the postmaster > __ Do you Yahoo!? Yahoo! Mail Address AutoComplete - You start. We finish. http://promotions.yahoo.com/new_mail ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Slow after VACUUM, fast after DROP-CREATE INDEX
In article <[EMAIL PROTECTED]>, ruben <[EMAIL PROTECTED]> writes: > Today, one of the processes running daily took 4 hours when it takes > about 5 minutes. After a VACCUM ANALYZE of the affected tables it took > the same to finish, then I recreated (drop and create) the index of > the affected table and the process when again fast. My question is, > isn't enough to run a VACCUM to optimize a table and its indexes? Is > it advisable to recreate indexes from time to time? This was necessary in PostgreSQL up to 7.3.x, but 7.4.x is supposed to fix that. What version are you running? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 7.4.2 allows foreign key violation
On Fri, 6 Aug 2004, Markus Bertheau wrote:
> Up to here I thought that the following was going on: The UPDATE b
> statement was rewritten into a UPDATE a statement by the rule system.
> The update on a triggers the foreign key update on b. This UPDATE gets
> rewritten again by the rule system to update a instead. The update to a
> triggers the foreign key again, which recognizes that it is already
> running and does nothing. The outer foreign key is done and the update
> to a is realized. b stays unchanged.
>
> But then I discovered that if I update the row in a prior to creating
> the rule, the rule works as expected:
>
> CREATE TABLE a (name TEXT PRIMARY KEY);
> INSERT INTO a VALUES ('xxx');
> CREATE TABLE b (name TEXT PRIMARY KEY REFERENCES a(name) ON UPDATE CASCADE);
> INSERT INTO b VALUES ('xxx');
> UPDATE a SET name = 'zzz' WHERE name = 'xxx';
> CREATE RULE b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = NEW.name WHERE
> name = OLD.name;
> UPDATE b SET name = 'yyy' WHERE name = 'zzz';
> SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
> name | ?column?
> --+--
> yyy | t
> (1 запись)
>
> This somehow renders my theory invalid. Can someone comment?
Only for that session. The foreign key query is planned at the first
update so it doesn't see the rule until you get to a new session.
> I also tried the same rule without INSTEAD. That does what I want and it
> is what I'm using in the application now. I wonder if that is The Right
> Way®.
Probably.
> And should PostgreSQL allow foreign key violations like in the example
> above?
Probably not.
It also looks like before triggers returning NULL can break them. I think
we'd been worried about the added cost of doing the check when the average
case doesn't have this problem but we should probably just eat it. In
practice I think it's one line of code per action function (on update set
default already does it).
Any opinions out there?
---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] PostgreSQL 7.4.2 allows foreign key violation
Markus Bertheau <[EMAIL PROTECTED]> writes:
> I create the situation as follows:
> CREATE TABLE a (name TEXT PRIMARY KEY);
> INSERT INTO a VALUES ('xxx');
> CREATE TABLE b (name TEXT PRIMARY KEY REFERENCES a(name) ON UPDATE CASCADE);
> INSERT INTO b VALUES ('xxx');
> CREATE RULE b_rename AS ON UPDATE TO b DO INSTEAD UPDATE a SET name = NEW.name WHERE
> name = OLD.name;
> UPDATE b SET name = 'yyy' WHERE name = 'xxx';
> SELECT b.name, exists(SELECT 1 FROM a WHERE a.name = b.name) FROM b;
The difficulty here is that the CASCADE is implemented by generating an
"UPDATE b" command ... which is rewritten by your rule and thus fails to
affect table b at all.
It would probably be better if the RI implementation acted at a lower
level and wasn't affected by rules, but for the foreseeable future the
answer is "don't do that".
> But then I discovered that if I update the row in a prior to creating
> the rule, the rule works as expected:
Only for the moment --- you're depending on a cached plan for the
foreign-key update. Start a fresh backend and it's broken again.
regards, tom lane
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Insert into sintax
Duane, El 05/08/2004 5:29 PM, Duane Lee - EGOVX en su mensaje escribio: What table is lnpedpk in - ped_cam? What table is ped_pk in - ped_pro and ped_cam? lnPedPk is parameter pased to the function and it could be a fixed value on the sql console too, ped_pk is common in both tables, I changed the code to this and still get the same error INSERT INTO ped_cam (SELECT * from ped_pro where ped_pro.ped_pk=81178) WHERE NOT EXISTS (SELECT 1 FROM ped_cam WHERE ped_cam.ped_pk=81178); Thanks, -- Sinceramente, Josué Maldonado. "Deja que los perros ladren, Sancho, es señal que caminamos." -- Miguel de Cervantes Saavedra. ---(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] New to Postgres
Darkcamel wrote: Hello all, I am new to postgres and don't really understand how the database is set-up. I am very fluent with mysql and sql2000, but postgres is new to me. If anyone can point me to some good links I would appreciate it very much. Thanks, Darkcamel ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster Well then I suggest you check out the docs at http://www.postgresql.org/docs/7.4/static/index.html. Specifically you can start with the Tutorial - Getting Started to show you how to create a database in postgres and access it. Then check out The SQL Language - Data Definition and SQL Language - Data Manipulation to see postgres's syntax for accessing / modifying the database and data. hth Ron ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] PG over NFS tips
Cott Lang wrote: The higher-ups are attempting to force me to run Postgres over NFS at least temporarily. Despite giving me a queasy feeling and reading quite a bit of messages advising against it, running Oracle over NFS with a NAS filer doesn't seem to be unusual. Is there a reason PG would be more sensitive than Oracle? Anyone ever done this before in a production environment? thanks! Do you trust your data to a udp connection ? We had problem in copying big files ( 1.9GB ) in a mounted NFS partition and now we prefer to not use it anymore for our data. Regards Gaetano Mendola ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] PostgreSQL 7.4.2 allows foreign key violation
Stephan Szabo <[EMAIL PROTECTED]> writes: > It also looks like before triggers returning NULL can break them. Or a BEFORE trigger that overrides the attempted field update. > I think we'd been worried about the added cost of doing the check when > the average case doesn't have this problem but we should probably just > eat it. In practice I think it's one line of code per action function > (on update set default already does it). Already does what? I see nothing in there that would override either triggers or rules... > Any opinions out there? I seem to recall some discussions to the effect that having these updates subject to rules/triggers is not necessarily bad. For example, if you were using a rule or trigger to log all updates of table B someplace else, you'd probably be annoyed to find the RI updates bypassing your logging mechanism. There's no perfect solution ... regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 7.4.2 allows foreign key violation
On Fri, 6 Aug 2004, Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > It also looks like before triggers returning NULL can break them. > > Or a BEFORE trigger that overrides the attempted field update. > > > I think we'd been worried about the added cost of doing the check when > > the average case doesn't have this problem but we should probably just > > eat it. In practice I think it's one line of code per action function > > (on update set default already does it). > > Already does what? I see nothing in there that would override either > triggers or rules... It's not for overriding the triggers or rules, but instead checking that the post action state is valid (by running the no action code which makes sure that either another row now has the pk value or that there are no longer any matching rows). ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[GENERAL] replication
Hello everyone. Are there any recommended ways for doing postgresql replication? Si Chen ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] PostgreSQL 7.4.2 allows foreign key violation
On Fri, 6 Aug 2004, Stephan Szabo wrote: > On Fri, 6 Aug 2004, Tom Lane wrote: > > > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > It also looks like before triggers returning NULL can break them. > > > > Or a BEFORE trigger that overrides the attempted field update. > > > > > I think we'd been worried about the added cost of doing the check when > > > the average case doesn't have this problem but we should probably just > > > eat it. In practice I think it's one line of code per action function > > > (on update set default already does it). > > > > Already does what? I see nothing in there that would override either > > triggers or rules... > > It's not for overriding the triggers or rules, but instead checking that > the post action state is valid (by running the no action code which > makes sure that either another row now has the pk value or that there are > no longer any matching rows). To make that clearer, that another row now has the old pk value, or that there are no longer any matching rows to the old pk value. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] Slow after VACUUM, fast after DROP-CREATE INDEX
Thanks Harald, i'm running PostgreSQL 7.1.3. Harald Fuchs wrote: In article <[EMAIL PROTECTED]>, ruben <[EMAIL PROTECTED]> writes: Today, one of the processes running daily took 4 hours when it takes about 5 minutes. After a VACCUM ANALYZE of the affected tables it took the same to finish, then I recreated (drop and create) the index of the affected table and the process when again fast. My question is, isn't enough to run a VACCUM to optimize a table and its indexes? Is it advisable to recreate indexes from time to time? This was necessary in PostgreSQL up to 7.3.x, but 7.4.x is supposed to fix that. What version are you running? ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
