[GENERAL] FOREIGN KEY: MATCH FULL
Hi, What does it mean the MATCH FULL parameter in a foreign key specfication ? It seems it is only valid for a multi-column foreign key: What's a multi-column foreign key ? I've tried to search this in Bruce's book but I have a lot of problems to connect to it at http://www.postgresql.org/docs/awbook.html Thanks, Gabi :-) ---(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] Vacuumdb message: AbortTransaction and not in-progress ...
Hi, We have some db's in our server. When executing a vacuumdb, ONLY FOR SOME of them, the following message is shown: AbortTransaction and not in in-progress state After this, the vacuum process is aborted, so we cannot vacuum these 'problematic' db's. What can we do ? Gabi :-) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Index on a function(field)
Hi, Is it possible to create an index using a function(field) sintaxis ? For instance: CREATE INDEX i1_cdu on cdu using btree ( substr(cdu_code,1,1) varchar_ops ); If not, should I alter the table to include a field with the value 'substr(codigo,1,1)'. Thanks Gabi :-) ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
[GENERAL] Table whose name has Capital letters
Hi , I have created a table with the name 'Nov2000' and now it is impossible to access it, because psql is always searching for the table 'nov2000'. The only solution I have found is to manually modify the pg_class table and the name of the physical file associated to the table. Have I done something wrong or is it a bug ? I mean, it seems psql is alwyas converting the query string to lowercase, but it doesn't when you create a table, a index or a sequence. I'm running 6.5.3 PostgreSQL on a Linux RedHat 6.0 machine. Thanks, Gabi :-)
[GENERAL] Problem with drop table within a transaction
Hi, I have a problem when doing drop table within a transaction: It seems that if you execute a drop table sentence but after the transaction is aborted because an error occurs, the result is a corruption of the database: the drop table sentence deletes the physical files corresponding to the table, but as transaction is aborted the table is not deleted from the database definition. So then you cannot recreate the table (baclkend says it alredy exists), you cannot drop the table (it says it doesn't find the physical file), etc. (Anyway, it is not very critical : i'm not droping table each hour and i can do it in its own atomic transaction). Beyond that i would want to know if there is available information about the database definiton in postgresql: where the table is defined, how to 'fix' manually some problems (for instance, the one i have explained above). Thanks. Gabi :-)
Re: [GENERAL] Problem with LIKE operator
Hi, Finally I have found the problem: the table was corrupted, so i have recreated it and it works fine. I wonder if instead of recreating it i would have had to execute a vacuum. Thanks to you all for your help and your suggestions (i will keep them for the future). Gabi :-)
[GENERAL] Problem with LIKE operator
Hi, I have some problems using LIKE within strings which have brackets. For example if i do: select code from codes where code like '(4)' ; i do not obtain nothing. But in the DB indeed there is a row whose code is '(4)'. I have tried to escape the brackets with '\\(4\\)' or with ' \(4\)' but it doesn't work. How can i do it ? Gabi :-)
Re: Re: [GENERAL] Regular expressions syntax: is \ the escape character ?
Thanks a million to you all. Finally, as someone in the list suggested, i'm
using the double backslash (\\) and it seems it works ok.
The only problem i have is when i want to escape a single quote (') or the
backslash (\).
For example: i have one row with the value 'ONE\SECOND'
I try to recover it doing (from psql frontend):
select field1 from table1 where field1 ~* 'ONE\\\' ;
But it doesn't work, and the parser seems to be confused (it asks me to
close again the quote ' ).
The problem is the same with the single quote '. I've tried this:
select field1 from table1 where field1 ~* 'D\\'ALEMA' ;
But it doesn't work neither.
Thanks for your help.
Gabi :-)
[GENERAL] locale mb support
Hi all: I'm using Postgres-6.5.3 with Red Hat 6.0 and I've a problen using LC_TYPE and LC_COLLATE ... I compiled Postgres --wiht-locale --with-mb=LATIN1. I did initdb -e LATIN1 And finally I createdb -E LATIN1 test. I also have defined LC_TYPE=LATIN1 and LC_COLLATE=LATIN1 when postmaster starts. So now, I try to select * from test1 order by ... The problem is that I do not get the result sorted as I would. What's wrong? What should I do? Thanks to all, jjprat
[GENERAL] Problems with operator '%' within a select
Hi ! I've just had some problems using the operator '%' within a select. This is an example: "select codigo from codis where codigo like '%3' " And i've found that all the rows which contains more than one three won't be selected. (For example: values 'a34ree3', '34fg3') It seems as if you put only one character with '%' it will select only the rows wich contain only one time this character. Am I wrong ? Gabi :-)
[GENERAL] Confussion with table-lock levels and isolation levels
Hi fellows ! I would only want to ask some questions concerning table-locking levels and isolation levels: * First of all: should I assume that AccessXXX modes imply locking the complete table and Row imply locking only the rows which have been accessed ? Will then the conflicts be solved according the hierarchy between modes ? * Second: - What does exactly mean that a mode 'CONFLICTS' with another ? - Does it mean that another concurrent transactions having these modes will have to wait until the first transaction have finished (commit or roll back) ? - Can we determine (when accessing a row in a table) wether we will have a conflict or not according to the criteria explained in the previous question (Access- complete table, Row - rows accessed) ? *Third: If all the previous assumptions are true: - When there is a conflict, will the only consequence be that all concurrent transactions will be processed in a FIFO serie and not in parallel ? - What about all the others concurrent transactions which haven't conflicted ? How can you avoid falling into contradiction with the isolation level (and assure the protection against non-repeteable reads or phantom reads ? - I feel those two levels (transactions and isolation levels) are two layers so the transactions will be processed according to a FIFO serie when exist any problem concerning the isolation level or the table-locking. Is this a good way to describe the way PostgreSQL manages the things ? Thank you very much for your help. By the way, is the first time i'm subscribed to a mailing list so if I do anything inappropiate or strange please tell me. Best regards Gabi :-)
