Re: [GENERAL] Default value of column not respecting character length or domain restraints.
Justin Dearing [EMAIL PROTECTED] writes:
It seems I can feed a default value to a domain that won't fit in the
underlying base type. I have the domain html_colors as so:
CREATE DOMAIN html_color AS char(7) CHECK (VALUE ~ '^#[A-Fa-f0-9]{6}$');
I then defined a column of html_colors as so:
ALTER TABLE users ALTER COLUMN profile_color SET DEFAULT '#RFF';
This worked fine and I didn't notice it until I added arecord to the
users table and got the error:
ERROR: value too long for type character(7).
This behavior seems undesirable to me. Is this a known bug or is there
a reason for this?
Well, the default isn't checked against constraints until it's used
at runtime. This is appropriate in a number of situations because
time-varying defaults are not uncommon (eg default now() for a
timestamp column). Also, defaults with side effects are not uncommon
--- think default nextval('seq') for a serial --- and causing those
side-effects to happen at CREATE TABLE time seems undesirable.
In short, I understand your annoyance, but the cure seems worse than
the disease. It's not like you won't find out soon enough if you
establish a constant default that doesn't meet your constraints.
regards, tom lane
---(end of broadcast)---
TIP 6: explain analyze is your friend
Re: [GENERAL] Arrays instead of join tables
William Garrison wrote: I've never worked with a database with arrays, so I'm curious... + Efficiency: To return the set_ids for an Item, I could return an array back to my C# code instead of a bunch of rows with integers. That's probably faster, right? You should look in to the contrib modules intagg and intarray. In particular contrib/intagg/README.int_aggregate discusses an example quite similar to what you're proposing. Short summary: Seems like a win for very static data, a loss for very dynamic data. ---(end of broadcast)--- TIP 1: 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] postgresl for mysql?
What will they think of next! http://krow.livejournal.com/502908.html I suppose it makes as much sense as the others, except why would you want to use mysql if the storage is in postgres? Cheers Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresl for mysql?
On Apr 1, 2007, at 10:33 AM, Anton Melser wrote: What will they think of next! http://krow.livejournal.com/502908.html I suppose it makes as much sense as the others, except why would you want to use mysql if the storage is in postgres? If you've inherited data in a postgresql database this will allow you to migrate it to the industry standard database without the inconvenience and downtime of a dump from postgresql and a restore into mysql. I don't think it's a new idea - IIRC, Aprile Pazzo did something similar for MySQL 3 and PG 7.something. Cheers, Steve ---(end of broadcast)--- TIP 1: 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] postgresl for mysql?
On 4/1/07, Anton Melser [EMAIL PROTECTED] wrote: What will they think of next! http://krow.livejournal.com/502908.html I suppose it makes as much sense as the others, except why would you want to use mysql if the storage is in postgres? Cheers Anton ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq Is today Aprils Fool's ? -- Leonel ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Connecting a sequence with table column
I am using PGSQL 8.2.3 on Windows XP and pgAdmin 1.6.3. While adding columns to a table from pgAdmin front-end, an option appears sequence with a drop-down box on the column creation window, but it remains disabled. I have already created a sequence but want to connect that sequence with this column. How can I do this? Why this disabled drop-down box not enabling? -- View this message in context: http://www.nabble.com/Connecting-a-sequence-with-table-column-tf3502025.html#a9780334 Sent from the PostgreSQL - general mailing list archive at Nabble.com. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Problem restoring from backup file
I am using PGSQL 8.2.3. I took a backup of my database selecting Plain option from pgAdmin 1.6. When I am trying to restore it to a new machine having PGSQL 8.2.3 installed, using pgAdmin, the OK button of the Restore Backup Window remains disabled. Why? Have you checked out the new-look www.indiatimes.com yet? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Tsearch2 crashes my backend, ouch !
On Fri, 30 Mar 2007, Listmail wrote:
OK, I've solved my problem... thanks for the hint !
Anyway, just to signal that tsearch2 crashes if SELECT is not granted
to pg_ts_dict (other tables give a proper error message when not GRANTed).On
I don't understand this. Are sure on this ?
From prompt in your select examples I see you have superuser's rights
and you have successfully select from pg_ts_dict column.
Oleg
Fri, 30 Mar 2007 13:20:30 +0200, Listmail [EMAIL PROTECTED] wrote:
Hello,
I have just ditched Gentoo and installed a brand new kubuntu system
(was tired of the endless compiles).
I have a problem with crashing tsearch2. This appeared both on Gentoo
and the brand new kubuntu.
I will describe all my install procedure, maybe I'm doing something
wrong.
Cluster is newly created and empty.
initdb was done with UNICODE encoding locales.
# from postgresql.conf
# These settings are initialized by initdb -- they might be changed
lc_messages = 'fr_FR.UTF-8' # locale for system error
message strings
lc_monetary = 'fr_FR.UTF-8' # locale for monetary
formatting
lc_numeric = 'fr_FR.UTF-8' # locale for number
formatting
lc_time = 'fr_FR.UTF-8' # locale for time
formatting
[EMAIL PROTECTED]:~$ locale
LANG=fr_FR.UTF-8
LC_CTYPE=fr_FR.UTF-8
LC_NUMERIC=fr_FR.UTF-8
etc...
First import needed .sql files from contrib and check that the
default tsearch2 config works for English
$ createdb -U postgres test
$ psql -U postgres test tsearch2.sql and other contribs I use
$ psql -U postgres test
test=# select lexize( 'en_stem', 'flying' );
lexize
{fli}
test=# select to_tsvector('default', 'flying ducks');
to_tsvector
--
'fli':1 'duck':2
OK, seems to work very nicely, now install French.
Since this is Kubuntu there is no source, so download source, then :
- apply patch_tsearch_snowball_82 from tsearch2 website
./configure --prefix=/usr/lib/postgresql/8.2/
--datadir=/usr/share/postgresql/8.2 --enable-nls=fr --with-python
cd contrib/tsearch2
make
cd gendict
(copy french stem.c and stem.h from the snowball website)
./config.sh -n fr -s -p french_UTF_8 -i -v -c stem.c -h stem.h -C'Snowball
stemmer for French'
cd ../../dict_fr
make clean make
sudo make install
Now we have :
/bin/sh ../../config/install-sh -c -m 644 dict_fr.sql
'/usr/share/postgresql/8.2/contrib'
/bin/sh ../../config/install-sh -c -m 755 libdict_fr.so.0.0
'/usr/lib/postgresql/8.2/lib/dict_fr.so'
Okay...
- download and install UTF8 french dictionaries from
http://www.davidgis.fr/download/tsearch2_french_files.zip and put them in
contrib directory
(the files delivered by debian package ifrench are ISO8859, bleh)
- import french shared libs
psql -U postgres test /usr/share/postgresql/8.2/contrib/dict_fr.sql
Then :
test=# select lexize( 'en_stem', 'flying' );
lexize
{fli}
And :
test=# select * from pg_ts_dict where dict_name ~ '^(fr|en)';
dict_name | dict_init | dict_initoption|
dict_lexize |dict_comment
---+---+--+---+-
en_stem | snb_en_init(internal) | contrib/english.stop |
snb_lexize(internal,internal,integer) | English Stemmer. Snowball.
fr| dinit_fr(internal)| |
snb_lexize(internal,internal,integer) | Snowball stemmer for French
test=# select lexize( 'fr', 'voyageur' );
server closed the connection unexpectedly
BLAM ! Try something else :
test=# UPDATE pg_ts_dict SET
dict_initoption='/usr/share/postgresql/8.2/contrib/french.stop' WHERE
dict_name = 'fr';
UPDATE 1
test=# select lexize( 'fr', 'voyageur' );
server closed the connection unexpectedly
Try other options :
dict_name | fr_ispell
dict_init | spell_init(internal)
dict_initoption |
DictFile=/usr/share/postgresql/8.2/contrib/french.dict,AffFile=/usr/share/postgresql/8.2/contrib/french.aff,StopFile=/usr/share/postgresql/8.2/contrib/french.stop
dict_lexize | spell_lexize(internal,internal,integer)
dict_comment|
test=# select lexize( 'en_stem', 'traveler' ), lexize( 'fr_ispell',
'voyageur' );
-[ RECORD 1 ]---
lexize | {travel}
lexize | {voyageuse}
Now it works (kinda) but stemming doesn't stem for French (since
snowball is out). It should return 'voyage' (=travel) instead of
'voyageuse' (=female traveler)
That's now what I want ; i want to use snowball to stem French words.
I'm going to make a debug build and try to debug it, but if anyone
can help, you're really, really welcome.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Regards,
Oleg
Re: [GENERAL] postgresl for mysql?
Leonel [EMAIL PROTECTED] writes: On 4/1/07, Anton Melser [EMAIL PROTECTED] wrote: What will they think of next! http://krow.livejournal.com/502908.html Is today Aprils Fool's ? Yup. But he got me for about half a second, because this was proposed entirely seriously by the MySQL AB folk back when they were looking for a way out from under Oracle's purchase of InnoDB. Since they hired Jim Starkey to write Falcon for them, I don't think there's any interest in that anymore over there. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] postgresl for mysql?
OK, it got me for more than half a second... :-) And as you mention - not entirely ridiculous! Cheers Anton On 01/04/07, Tom Lane [EMAIL PROTECTED] wrote: Leonel [EMAIL PROTECTED] writes: On 4/1/07, Anton Melser [EMAIL PROTECTED] wrote: What will they think of next! http://krow.livejournal.com/502908.html Is today Aprils Fool's ? Yup. But he got me for about half a second, because this was proposed entirely seriously by the MySQL AB folk back when they were looking for a way out from under Oracle's purchase of InnoDB. Since they hired Jim Starkey to write Falcon for them, I don't think there's any interest in that anymore over there. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] postgresl for mysql?
On Sun, Apr 01, 2007 at 10:53:06AM -0700, Steve Atkins wrote: On Apr 1, 2007, at 10:33 AM, Anton Melser wrote: What will they think of next! http://krow.livejournal.com/502908.html I suppose it makes as much sense as the others, except why would you want to use mysql if the storage is in postgres? If you've inherited data in a postgresql database this will allow you to migrate it to the industry standard database without the inconvenience and downtime of a dump from postgresql and a restore into mysql. I don't think it's a new idea - IIRC, Aprile Pazzo did something similar for MySQL 3 and PG 7.something. What an interesting name! I don't know much Italian other than what I've picked up from a few movies, but I think I now know what Pazzo means ... -- ... _._. ._ ._. . _._. ._. ___ .__ ._. . .__. ._ .. ._. Felix Finch: scarecrow repairman rocket surgeon / [EMAIL PROTECTED] GPG = E987 4493 C860 246C 3B1E 6477 7838 76E9 182E 8151 ITAR license #4933 I've found a solution to Fermat's Last Theorem but I see I've run out of room o ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] postgresl for mysql?
What will they think of next! http://krow.livejournal.com/502908.html I suppose it makes as much sense as the others, except why would you want to use mysql if the storage is in postgres? If you've inherited data in a postgresql database this will allow you to migrate it to the industry standard database without the inconvenience and downtime of a dump from postgresql and a restore into mysql. I don't think it's a new idea - IIRC, Aprile Pazzo did something similar for MySQL 3 and PG 7.something. What an interesting name! I don't know much Italian other than what I've picked up from a few movies, but I think I now know what Pazzo means ... Yeah well you know mysqldump has an option export to postgres syntax so you can reimport in postgres. I encourage you to try it one day, you'll be amazed. mysqldump --password -d -u root immo_forum DROP TABLE IF EXISTS `smf_topics`; CREATE TABLE `smf_topics` ( `ID_TOPIC` mediumint(8) unsigned NOT NULL auto_increment, `isSticky` tinyint(4) NOT NULL default '0', `ID_BOARD` smallint(5) unsigned NOT NULL default '0', `ID_FIRST_MSG` int(10) unsigned NOT NULL default '0', `ID_LAST_MSG` int(10) unsigned NOT NULL default '0', `ID_MEMBER_STARTED` mediumint(8) unsigned NOT NULL default '0', `ID_MEMBER_UPDATED` mediumint(8) unsigned NOT NULL default '0', `ID_POLL` mediumint(8) unsigned NOT NULL default '0', `numReplies` int(10) unsigned NOT NULL default '0', `numViews` int(10) unsigned NOT NULL default '0', `locked` tinyint(4) NOT NULL default '0', PRIMARY KEY (`ID_TOPIC`), UNIQUE KEY `lastMessage` (`ID_LAST_MSG`,`ID_BOARD`), UNIQUE KEY `firstMessage` (`ID_FIRST_MSG`,`ID_BOARD`), UNIQUE KEY `poll` (`ID_POLL`,`ID_TOPIC`), KEY `isSticky` (`isSticky`), KEY `ID_BOARD` (`ID_BOARD`) ) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci; /*!40103 SET [EMAIL PROTECTED] */; mysqldump --compatible=postgres --password -d -u root immo_forum DROP TABLE IF EXISTS smf_topics; CREATE TABLE smf_topics ( ID_TOPIC mediumint(8) unsigned NOT NULL, isSticky tinyint(4) NOT NULL default '0', ID_BOARD smallint(5) unsigned NOT NULL default '0', ID_FIRST_MSG int(10) unsigned NOT NULL default '0', ID_LAST_MSG int(10) unsigned NOT NULL default '0', ID_MEMBER_STARTED mediumint(8) unsigned NOT NULL default '0', ID_MEMBER_UPDATED mediumint(8) unsigned NOT NULL default '0', ID_POLL mediumint(8) unsigned NOT NULL default '0', numReplies int(10) unsigned NOT NULL default '0', numViews int(10) unsigned NOT NULL default '0', locked tinyint(4) NOT NULL default '0', PRIMARY KEY (ID_TOPIC), UNIQUE KEY lastMessage (ID_LAST_MSG,ID_BOARD), UNIQUE KEY firstMessage (ID_FIRST_MSG,ID_BOARD), UNIQUE KEY poll (ID_POLL,ID_TOPIC), KEY isSticky (isSticky), KEY ID_BOARD (ID_BOARD) ); /*!40103 SET [EMAIL PROTECTED] */; Sure looks compatible (but with what ?) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Tsearch2 crashes my backend, ouch !
Anyway, just to signal that tsearch2 crashes if SELECT is not granted to pg_ts_dict (other tables give a proper error message when not GRANTed).On I don't understand this. Are sure on this ? From prompt in your select examples I see you have superuser's rights and you have successfully select from pg_ts_dict column. It was tricky to reproduce... I thought I had hallucinations but here it is : - open two psql windows (one postgres user, one normal unprivileged user), see or # in prompt for which window I use to type commands/ - first let's lock ourselves up : caillaudangers=# REVOKE select ON pg_ts_dict FROM caillaudangers ; REVOKE caillaudangers=# REVOKE select ON pg_ts_cfg FROM caillaudangers ; REVOKE caillaudangers=# REVOKE select ON pg_ts_cfgmap FROM caillaudangers ; REVOKE caillaudangers=# REVOKE select ON pg_ts_parser FROM caillaudangers ; REVOKE - then try to access : caillaudangers= SELECT to_tsvector( 'bill gates is watching us' ); ERREUR: droit refusé pour la relation pg_ts_dict CONTEXT: instruction SQL «select dict_init, dict_initoption, dict_lexize from public.pg_ts_dict where oid = $1» caillaudangers=# GRANT select ON pg_ts_dict TO caillaudangers ; GRANT caillaudangers= SELECT to_tsvector( 'bill gates is watching us' ); ERREUR: No dictionary with id 138493128 Strange error message ?? caillaudangers= SELECT to_tsvector( 'bill gates is watching us' ); ERREUR: droit refusé pour la relation pg_ts_cfg CONTEXT: instruction SQL «select prs_name from public.pg_ts_cfg where oid = $1» Proper error message now. Let's go back. caillaudangers=# REVOKE select ON pg_ts_dict FROM caillaudangers ; REVOKE Now try to select to_tsvector and each time a permission is denied, grant the needed table. caillaudangers= SELECT to_tsvector( 'bill gates is watching us' ); ERREUR: droit refusé pour la relation pg_ts_cfg CONTEXT: instruction SQL «select prs_name from public.pg_ts_cfg where oid = $1» caillaudangers=# GRANT select ON pg_ts_cfg TO caillaudangers ; GRANT caillaudangers= SELECT to_tsvector( 'bill gates is watching us' ); ERREUR: droit refusé pour la relation pg_ts_cfgmap CONTEXT: instruction SQL «select lt.tokid, map.dict_name from public.pg_ts_cfgmap as map, public.pg_ts_cfg as cfg, public.token_type( $1 ) as lt where lt.alias = map.tok_alias and map.ts_name = cfg.ts_name and cfg.oid= $2 order by lt.tokid desc;» caillaudangers=# GRANT select ON pg_ts_cfgmap TO caillaudangers ; GRANT caillaudangers= SELECT to_tsvector( 'bill gates is watching us' ); ERREUR: droit refusé pour la relation pg_ts_parser CONTEXT: instruction SQL «select prs_start, prs_nexttoken, prs_end, prs_lextype, prs_headline from public.pg_ts_parser where oid = $1» caillaudangers=# GRANT select ON pg_ts_parser TO caillaudangers ; GRANT caillaudangers= SELECT to_tsvector( 'bill gates is watching us' ); server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. La connexion au serveur a été perdue. Tentative de réinitialisation : Echec. There it crashes. It's bizarre. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Connecting a sequence with table column
RPK wrote: I am using PGSQL 8.2.3 on Windows XP and pgAdmin 1.6.3. While adding columns to a table from pgAdmin front-end, an option appears sequence with a drop-down box on the column creation window, but it remains disabled. I have already created a sequence but want to connect that sequence with this column. How can I do this? Why this disabled drop-down box not enabling? The pg-admin guys would be able to answer this best: http://www.pgadmin.org/support/ (While some are on the general postgres lists, most wouldn't be). -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Problem restoring from backup file
Rohit Prakash Khare wrote: I am using PGSQL 8.2.3. I took a backup of my database selecting Plain option from pgAdmin 1.6. When I am trying to restore it to a new machine having PGSQL 8.2.3 installed, using pgAdmin, the OK button of the Restore Backup Window remains disabled. Why? The pg-admin guys will be able to help best: http://www.pgadmin.org/support/ -- Postgresql php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 1: 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] Urgent job opening for PostgreSQL DBA.
Hi, We have excellent and urgent job opening for PostgreSQL DBA with one of our esteemed client in Pune/ Bangalore. Skillset : PostgreSQL DBA Experience : 5 - 7 yrs Location : Pune/ Bangalore. If interested, please send me your updated resume in MS word format along with the CTC details: Current CTC : Expected CTC : Notice Period : Please send your Academic Details : BE : %, University HSC : %, Board SSC : %, Board Humble Request: Please give me references of your friends or colleagues who would be interested in the above position since there are few people working as PostgreSQL DBA. Please revert back ASAP. Thanks Regards, Poornima --- Staffing Associate E-Mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Urgent job opening for PostgreSQL DBA.
Hi, We have excellent and urgent job opening for PostgreSQL DBA with one of our esteemed client in Pune/ Bangalore. Skillset : PostgreSQL DBA Experience : 5 - 7 yrs Location : Pune/ Bangalore. If interested, please send me your updated resume in MS word format along with the CTC details: Current CTC : Expected CTC : Notice Period : Please send your Academic Details : BE : %, University HSC : %, Board SSC : %, Board Humble Request: Please give me references of your friends or colleagues who would be interested in the above position since there are few people working as PostgreSQL DBA. Please revert back ASAP. Thanks Regards, Poornima --- Staffing Associate E-Mail: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
