Re: [GENERAL] RI_ConstraintTrigger question
On 9/26/05, [EMAIL PROTECTED] [EMAIL PROTECTED] wro We have a database with about 30 tables and some RI. The RI constraints,however, were not named upon creation of the database 2-3 years ago andnow when we get an error it contains unnamed for the constraint. I tried Google and the documentation, and I still have 2 questions -1. Is it possible to rename RI_ConstraintTrigger, so that we do not getunnamed in the errors.2. Is there somewhere explanation what the RI_FKey_ procedures mean? I think RI stand for referential integrity. Foreign keys used to be implemented using 'create constraint trigger' which automatically names triggers 'RI_ConstraintTrigger_' then some integer which I guess is an oid (object id). Constraint triggers execute functions to implement a constraint. RI_FKey_... are the functions that implement foreign key constraints for different events like insert, update, and delete. When you upgrade a database it's likely that the oids for different database objects will change. In sounds like somehow you upgraded and retained references to old oids which don't exist anymore. Just a guess. I suggest you upgrade to a newer version of PostgreSQL and drop all of the 'RI_ConstraintTrigger_' trigger and recreate the foreign keys. George Essig
[GENERAL] New Drupal PostgreSQL Maintainer Wanted
I noticed on the drupal-devel mailing lists that a new postgresql maintainer is wanted. You can view the thread at: http://lists.drupal.org/archives/drupal-devel/2005-08/msg00432.html You can sign up for the drupal-devel mailing list by filling out the form at the bottom of the page at: http://drupal.org/mailing-lists I'm new to drupal, so I'm probably not the best choice for the postgresql maintainer. Maybe someone else in the postgresql community has more experience with drupal. By the way, drupal is an open source content management platform written in PHP and supports MySQL and PostgreSQL. For more on drupal, see http://drupal.org. George Essig ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Upgrade data
On Tue, 29 Mar 2005 15:39:28 -0600, josue [EMAIL PROTECTED] wrote: Hello list, I need to upgrade my dbs from 743 to 801, current data size is around 5GB, I've tried this way: ./pg_dump -d dbtest -p 9980 | ./psql -d template1 -p 9981 but is too slow, any idea or suggestion to properly upgrade my dbs, I also have blobs stored there. Drop '-d' from the pg_dump command. '-d' creates a dump file with insert statements instead of copy statements. From the pg_dump man page: -d --inserts Dump data as INSERT commands (rather than COPY). This will make restoration very slow, but it makes the archives more portable to other SQL database packages. George Essig ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] psql variables
On Mon, 14 Mar 2005 11:06:32 -0500, Paul Cunningham [EMAIL PROTECTED] wrote: I use a bash script (similar to following example) to update tables. psql -v passed_in_var=\'some_value\' -f script_name Is it possible to pass a value back from psql to the bash script? You can use '\! [ command ]' to execute shell commands within psql. This may not be what you want though. George Essig ---(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] sql question
On Fri, 11 Mar 2005 13:26:07 +0100, Steven Verhoeven [EMAIL PROTECTED] wrote: My table definition : id | fref | mref --+---+-- 1 | 23| 25 2 | 24| 28 3 | 25| 31 4 | 26| 34 i need a query that results in this : id |ref --+-- 1 | 23 1 | 25 2 | 24 2 | 28 3 | 25 3 | 31 4 | 26 4 | 34 Do I need a crosstab-query ? Who can help me ? select id, fref as ref from my_table union select id, mref as ref from my_table; ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Backupping the table values
On Tue, 1 Mar 2005 17:48:44 +0200, Vitaly Belman [EMAIL PROTECTED] wrote: CREATE TABLE functions.temp1 ( id1 int4 NOT NULL, id2 int4, CONSTRAINT pk_temp1 PRIMARY KEY (id1), CONSTRAINT temp2_id2 FOREIGN KEY (id2) REFERENCES functions.temp2 (id2) ON UPDATE RESTRICT ON DELETE RESTRICT DEFERRABLE INITIALLY DEFERRED ) WITHOUT OIDS; Remove 'ON UPDATE RESTRICT ON DELETE RESTRICT' from your create table statement. The manual says the following about RESTRICT: Produce an error indicating that the deletion or update would create a foreign key constraint violation. This is the same as NO ACTION except that the check is not deferrable. George Essig ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [GENERAL] Problem resolved (tsearch2 inhibiting migration)
We could decree that a contrib module's script should create a schema and shove everything it makes into that schema. Then DROP SCHEMA CASCADE is all you need to get rid of it. However, you'd probably end up having to add this schema to your search path to use the module conveniently. regards, tom lane I currently load tsearch2 into a separate schema. It's a convenient way to separate tsearch2 from the rest of the database for backup procedures and listing database objects. To make this work as transparently as possible, I update the search_path to include the new schema to avoid explicit references. The only problem is that the search_path is stored in the catalog and not outputted in pg_dump files. You have to remember to set the search_path after restoring the database. George Essig ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] How to make a good documentation of a database ?
David Pradier wrote: The problem is, I don't want to use a lot of time to maintain this documentation, and above all, I'd prefer not to insert the information twice (read: a new constraint in the database should automagically update the documentation). You could use the postgresql's comment statement together with postgresql_autodoc. See the following links for documentation and examples: http://www.postgresql.org/docs/7.4/interactive/sql-comment.html http://www.rbt.ca/autodoc/ http://www.rbt.ca/autodoc/output.html http://www.rbt.ca/autodoc/autodocexample.html http://www.rbt.ca/autodoc/output-graphviz.html George Essig ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Has anyone tried Date/Darwen/Lorentzos's model for temporal data?
Eric D. Nielsen wrote: I'm in the process of adding more historic information to one of my databases. I've liked the theoretical treatment of the concept in Temporal Data and the Relational Model, by Date, Darwen, Lorentzos. A lot of it is not realizable without a lot of user defined types/functions/etc. I was wondering if anyone else has tried to use their approach as a base for their historical databases in PostGreSQL and has any lessons learned to share. I have not read the book you mentioned, but I have read a book that may be related. I recommend looking at: Developing Time-Oriented Database Applications in SQL by Richard T. Snodgrass The book is out of print, but the author has made the PDF available on his website at: http://www.cs.arizona.edu/people/rts/tdbbook.pdf One of the main ideas in the book is to define valid time periods to record when information was true or visible. Valid time periods are implemented by adding 2 columns to a table for the start date and end date of a period. Much of the book is about how to test for and resolve valid time period overlap between different rows. Topics include temporal versions of primary keys, inserts, updates, and deletes. I have implemented these ideas in PostgreSQL. I can talk further about this if you're interested. The last part of the book is about adding 2 more columns to a table to define transaction time periods. Transaction time periods can be used to reconstruct the state of a database at a specific point in time. I didn't read this part as closely and haven't implemented these ideas. Hope this helps, George Essig ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] Has anyone tried Date/Darwen/Lorentzos's model for temporal data?
--- Eric D. Nielsen [EMAIL PROTECTED] wrote: Thanks for the Snodgrass reference, it is rather similar and pre-dates the book I was looking at. (Same notion of valid/transaction times, but Date's non-SQL approach) From a quick skim it doesn't address the distinction Date et al draw between historic and current temporal data; however it looks very useful for mapping their concepts to SQL. Eric You might want to look at Section 7.5 Temporal Partitioning. One table is used to store current data and another table is used to store historic data. George ---(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] Installing FullTextSearchTool tsearch2
could not access file$libdir/tsearch2: no such file or directory I'm guessing that you did not first run configure in the root directory of the postgresql source tree (not contrib/tsearch2). Tsearch2 doesn't know where to install its files. This might help: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/oscon_tsearch2/readme_detail_01.html There's a typo on this page 'pg_configure --configure' should be 'pg_config --configure'. George Essig ---(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]
Chris Gamache [EMAIL PROTECTED] wrote: When I reload and attempt to access the database I find this in the logs: ERROR: cache lookup failed for function 75769893 You should be OK as long as you have the dump file. If you haven't modifed any of the tsearch2 pg_ts_* tables, try and load tsearch2.sql first before loading the dump file. You will get a lot of errors when you load the dump file. It will try to create object that tsearch2.sql already created. Things should work if you haven't modified any of the tsearch2 tables. Below is some sample code. Change the names of the databases and the path to the *.sql files. createdb ts_db_b; psql ts_db_b tsearch2.sql; psql ts_db_b ts_db_a.sql; So what if you did modify the pg_ts_* tables? Load the dump file into a new database. Read about the regprocedure patch at: http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/docs/tsearch-V2-intro.html Install the file regprocedure_7.4.patch.gz, then load the following into your database. http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/regprocedure_update.sql George Essig ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [GENERAL] docs on tsearch2
hi all Are there any other docs about tsearch2 except for the 3 docs in \contrib\tsearch2\docs i want some tech docs on tsearch2 thank all See http://www.sai.msu.su/~megera/postgres/gist/tsearch/V2/ George Essig ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[GENERAL] CMS with PostgreSQL
Anyone know of an open source (BSD, GPL) Content Managment System written in PHP that will use PostgreSQL, or at least use PEAR::DB or any other DB abstraction layer API? -- select 'mmarques' || '@' || 'unl.edu.ar' AS email; - Martín Marqués |[EMAIL PROTECTED] Programador, Administrador, DBA | Centro de Telemática Universidad Nacional del Litoral - You should look at ezPublish versions 2 and 3. They both are licensed under the GPL and use Apache, PHP, and MySQL or PostgreSQL. Version 2 has out-of-the-box functionality for articles, forums, etc. Version 3 lets you create your own content types. See: http://ez.no/developer/ez_publish_22 http://ez.no/developer/ez_publish_3 George Essig ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] tsearch2 and gist index bloat
--- Oleg Bartunov [EMAIL PROTECTED] wrote: On Thu, 6 Nov 2003, George Essig wrote: Thanks for the reply. For this project, I can update the data and reindex during off-peak hours. I was just surprised to see the size of the index double after heavy write activity. This is not tsearch specific problem. It was discussed several times, ] see index bloat subject in archives. Oleg I understand that index bloat is a general problem, but is this particular problem more severe because of a gist index? By the way, I'm running PostgreSQL 7.4 Release Candidate 1. Thanks, George Essig ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] [SQL] connectby
I use postgresql 7.2.3 How can I use connectby ?? Must I install files ? or packages ? or it is recommanded to upgrade dataserver ? For recent versions of PostgreSQL, go into the contrib/tablefunc directory and see the readme file for how to install. I downloaded PostgreSQL 7.2.3 and there was no contrib/tablefunc directory. You'll have to upgrade. George Essig ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [GENERAL] connectby
hi
I have menu table:
id | integer | not null default
nextval('public.menu_id_seq'::text)
parent_id | integer |
description | text|
I do select:
test= SELECT * FROM connectby('menu','id','parent_id','2',0,'~') t(id
integer, parent_id integer, level int, branch text);
id | parent_id | level | branch
+---+---+-
2 | | 0 | 2
4 | 2 | 1 | 2~4
7 | 4 | 2 | 2~4~7
10 | 7 | 3 | 2~4~7~10
16 |10 | 4 | 2~4~7~10~16
9 | 4 | 2 | 2~4~9
How am I able to select description file from menu table, not only id,
parent_id, level, branch fields?
--
WBR, sector119
Try a join with the original table:
SELECT t.*, description FROM connectby('menu','id','parent_id','2',0,'~')
AS t(id integer, parent_id integer, level int, branch text), menu
WHERE t.id = menu.id
George Essig
---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
