Re: [GENERAL] Automating databse creation
Yes, you can pass values to the scripts. Here is an example
Suppose the script file 'test.sql' is like this:
insert into test values (:chk1 , :chk2);
Now you can pass the variables using psql as:
psql -d test -U postgres -v chk1=5 -v chk2='abc' -f test.sql
Hope that helps...
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/9/07, Ashish Karalkar [EMAIL PROTECTED] wrote:
Hello list,
I want to automate database creation, user creation,table creation via
script. this script will be run by an external programme to postgresql
server.
is there any way?
what i want to do is as follows:
#!/bin/sh
su - postgres
TIMEDUMP=`date +%Y%m%d_%H%M`
PG_PATH=/usr/local/pgsql/bin
${PG_PATH}/pg_dump -d qsweb -U postgres -p 5432 -Ft -f
/usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar
echo ' Backup of Database for Quick School kept at path
/usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar'
${PG_PATH}/dropdb -U qsweb qsweb
echo ' Old Database for Quick School Dropped'
${PG_PATH}/dropuser qsweb
echo ' Old user qsweb for Quick School Dropped'
${PG_PATH}/createuser -s qsweb
echo ' New user qsweb for Quick School created'
${PG_PATH}/createdb -O qsweb qsweb
echo ' New Database qsweb for Quick School created'
${PG_PATH}/createlang -U qsweb -d qsweb plpgsql
echo ' Database language plpgqslfor Quick School created'
echo ' Database for Quick School created. Please run the Create_Tables.sql
script'
${PG_PATH}/psql -U qsweb qsweb
and then Run Create_Tables.sql
_EOF_
is there any way??
right now the scripts stop after switching over to postgres user.
With regards
Ashish...
[GENERAL] intarray index vs gin index
Hello, I'm trying to update my database to 8.2 version and have some problems with intarray module. As far I know PG 8.2 contains many features that are contained within intarray - indexing array fields (using gin), operators etc. Currently (in PostgreSQL 8.1) I'm using intarray not only for indexing (but this is the main feature) but also I'm using functions (like count, sort...) and operators (+, -) that are provided by intarray. My question is whether I still should use intarray for indexing (if yes then either I should use GIST or GIN) or maybe GIN index is faster than GIST+intarray / GIN+intarray. Thanks in advance for reply. ML ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] does anyone have a tool to convert SP'sT-SQL to Postgres
Hi, I'm hoping someone has already taken the time to write a routine (in some language - python,perl, etc..) to convert MS SQL T-SQL stored procedures into Postgres PL/pgSQL. And of course they are willing to share. Thanks -- John Fabiani ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Automating databse creation
Hello list,
I want to automate database creation, user creation,table creation via script.
this script will be run by an external programme to postgresql server.
is there any way?
what i want to do is as follows:
#!/bin/sh
su - postgres
TIMEDUMP=`date +%Y%m%d_%H%M`
PG_PATH=/usr/local/pgsql/bin
${PG_PATH}/pg_dump -d qsweb -U postgres -p 5432 -Ft -f
/usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar
echo ' Backup of Database for Quick School kept at path
/usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar'
${PG_PATH}/dropdb -U qsweb qsweb
echo ' Old Database for Quick School Dropped'
${PG_PATH}/dropuser qsweb
echo ' Old user qsweb for Quick School Dropped'
${PG_PATH}/createuser -s qsweb
echo ' New user qsweb for Quick School created'
${PG_PATH}/createdb -O qsweb qsweb
echo ' New Database qsweb for Quick School created'
${PG_PATH}/createlang -U qsweb -d qsweb plpgsql
echo ' Database language plpgqslfor Quick School created'
echo ' Database for Quick School created. Please run the Create_Tables.sql
script'
${PG_PATH}/psql -U qsweb qsweb
and then Run Create_Tables.sql
_EOF_
is there any way??
right now the scripts stop after switching over to postgres user.
With regards
Ashish...
Re: [GENERAL] does anyone have a tool to convert SP'sT-SQL to Postgres
am Thu, dem 08.02.2007, um 21:28:08 -0800 mailte johnf folgendes: Hi, I'm hoping someone has already taken the time to write a routine (in some language - python,perl, etc..) to convert MS SQL T-SQL stored procedures into Postgres PL/pgSQL. And of course they are willing to share. See there: http://www.postgresql.org/docs/techdocs.3 I hope, you can find what you need. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: - Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] trigger for pg_authid
Hello, I'm trying to set a new trigger for pg_authid connected as postgres but system returns always the same error Permission denied: pg_authid is a system catalog... I have checked privileges and I can teorically add new triggers. I don't know whta's wrong... Thanks
Re: [GENERAL] Automating databse creation
Hi, On Friday 09 February 2007 08:53, Ashish Karalkar wrote: | I want to automate database creation, user creation,table creation via | script. this script will be run by an external programme to postgresql | server. is there any way? | | what i want to do is as follows: | | | #!/bin/sh | | su - postgres [rest of not working script] | is there any way?? | | right now the scripts stop after switching over to postgres user. assuming the script is run as user root, you can invoke it as su - postgres -c /path/to/script Just remove the su - postgres from your script. You might want to look at your init scripts (/etc/init.d/postgresql I assume) for how they do it. Ciao, Thomas -- Thomas Pundt [EMAIL PROTECTED] http://rp-online.de/ ---(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] Automating databse creation
Hi Ashish Karalkar
That's because the shell is waiting for input.
Try do something like this
su - postgres -c ${PG_PATH}/pg_dump -d qsweb -U postgres -p 5432 -Ft -f
/usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar
That should run the command as the postgres user.
Thanks
Bruce
_
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Ashish Karalkar
Sent: 09 February 2007 07:54
To: pgsql-general@postgresql.org
Cc: Shoaib Mir
Subject: [GENERAL] Automating databse creation
Hello list,
I want to automate database creation, user creation,table creation via
script. this script will be run by an external programme to postgresql
server.
is there any way?
what i want to do is as follows:
#!/bin/sh
su - postgres
TIMEDUMP=`date +%Y%m%d_%H%M`
PG_PATH=/usr/local/pgsql/bin
${PG_PATH}/pg_dump -d qsweb -U postgres -p 5432 -Ft -f
/usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar
echo ' Backup of Database for Quick School kept at path
/usr/local/pgsql/data/backup/BACKUP_QSWEB_${TIMEDUMP}.tar'
${PG_PATH}/dropdb -U qsweb qsweb
echo ' Old Database for Quick School Dropped'
${PG_PATH}/dropuser qsweb
echo ' Old user qsweb for Quick School Dropped'
${PG_PATH}/createuser -s qsweb
echo ' New user qsweb for Quick School created'
${PG_PATH}/createdb -O qsweb qsweb
echo ' New Database qsweb for Quick School created'
${PG_PATH}/createlang -U qsweb -d qsweb plpgsql
echo ' Database language plpgqslfor Quick School created'
echo ' Database for Quick School created. Please run the Create_Tables.sql
script'
${PG_PATH}/psql -U qsweb qsweb
and then Run Create_Tables.sql
_EOF_
is there any way??
right now the scripts stop after switching over to postgres user.
With regards
Ashish...
__ NOD32 2047 (20070208) Information __
This message was checked by NOD32 antivirus system.
http://www.eset.com
Re: [GENERAL] Possibly odd question; diff results?
On 2/8/07, Madison Kelly [EMAIL PROTECTED] wrote: Hi all, I've got a 'history' schema that records changes in the public schema tables over time. I use a trigger and function to do this. What I would like to do though, and this may not even be possible, is say something like (pseudo-code) SELECT DIFF foo_name FROM history.foo WHERE foo_id=X; and have a *nix 'diff' style results shown (sort of like looking at diffs in CVS/SVN). you can start by using the 'except' boolean query operator; select * from foo except * from bar; This will give you rows in foo that are not exactly in bar (matching every field). If you want it in both directions you can: (select * from foo except select * from bar) union (select * from bar except select * from foo); you can then take the results of these queries and mark up the text however you want. Just a heads up: the boolean sql operators are famous for generating sequential scans. merlin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] trigger for pg_authid
Diego de Blas [EMAIL PROTECTED] schrieb: Hello, I'm trying to set a new trigger for pg_authid connected as postgres but system returns always the same error Permission denied: pg_authid is a system catalog... I have checked privileges and I can teorically add new triggers. I don't know whta's wrong... You can't create a TRIGGER on a system-table. Andreas -- Really, I'm not out to destroy Microsoft. That will just be a completely unintentional side effect. (Linus Torvalds) If I was god, I would recompile penguin with --enable-fly.(unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889° ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] intarray index vs gin index
intarray. My question is whether I still should use intarray for indexing (if yes then either I should use GIST or GIN) or maybe GIN index is faster than GIST+intarray / GIN+intarray. Yes, with intarray you can use GiST/GIN indexes which you wish -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(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
Re: [GENERAL] trigger for pg_authid
Diego de Blas wrote: Hello, I'm trying to set a new trigger for pg_authid connected as postgres but system returns always the same error Permission denied: pg_authid is a system catalog... I have checked privileges and I can teorically add new triggers. I don't know whta's wrong... Thanks We went the other way with this. We created our own table of users, and put a trigger on that so that INSERTS to that table created new users in postgres, plus anything else we needed to happen. We did the same with a table of user-group membership. begin:vcard fn:Kenneth Downs n:Downs;Kenneth adr;dom:;;347 Main Street;East Setauket;NY;11733 email;internet:[EMAIL PROTECTED] tel;work:631-689-7200 tel;fax:631-689-0527 tel;cell:631-379-0010 x-mozilla-html:FALSE url:http://www.secdat.com version:2.1 end:vcard ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] does anyone have a tool to convert SP'sT-SQL to Postgres
On Friday 09 February 2007 04:43, A. Kretschmer wrote: am Thu, dem 08.02.2007, um 21:28:08 -0800 mailte johnf folgendes: Hi, I'm hoping someone has already taken the time to write a routine (in some language - python,perl, etc..) to convert MS SQL T-SQL stored procedures into Postgres PL/pgSQL. And of course they are willing to share. See there: http://www.postgresql.org/docs/techdocs.3 I hope, you can find what you need. Andreas Thanks the link is helpful. It's just I was hoping to find a script that could care of most of the convert and just needed a little hand coding. -- John Fabiani ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Modifying a foreign key constraint?
Whoops, ALTER TABLE foo ADD FOREIGN KEY (foo_bar_id_fkey) REFERENCES bar(id) ON DELETE CASCADE; should be ALTER TABLE foo ADD FOREIGN KEY (bar_id) REFERENCES bar(id) ON DELETE CASCADE; Sorry! Marc ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] Modifying a foreign key constraint?
What's the best way to modify a foreign key constraint? I need to change a foreign key from ON DELETE NO ACTION to ON DELETE CASCADE. Should I just drop the constraint and re-add it? For example: ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey; ALTER TABLE foo ADD FOREIGN KEY (foo_bar_id_fkey) REFERENCES bar(id) ON DELETE CASCADE; Is there a more compact way to do this, perhaps with a single ALTER TABLE command? Thanks! Marc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Adding TEXT columns tanks performance?
On 2/8/07, Arturo Perez [EMAIL PROTECTED] wrote: Hi all, Saturday I changed a table to add a varchar(24) and a TEXT column. It's used for some reporting purposes (small potatoe stuff really) and the TEXT column remains mostly empty. However, this week performance has gotten terrible. Queries joining against the aforementioned table have gone from 40s to 1500s. The schema change is the only explanation I have for the 30x slower queries. The queries have definitely gotten disk-bound (I can see the connection process sitting in the D state for several minutes). This is pg8.1.4 on Linux RedHat. Would adding a TEXT column impact things that much? The change to the schema was alter table foo add column title text; explain analyze please. no reason for adding text column to do that. especially since you didn't default the column which would effectively update the entire table. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Possibly odd question; diff results?
Merlin Moncure wrote: On 2/8/07, Madison Kelly [EMAIL PROTECTED] wrote: Hi all, I've got a 'history' schema that records changes in the public schema tables over time. I use a trigger and function to do this. What I would like to do though, and this may not even be possible, is say something like (pseudo-code) SELECT DIFF foo_name FROM history.foo WHERE foo_id=X; and have a *nix 'diff' style results shown (sort of like looking at diffs in CVS/SVN). you can start by using the 'except' boolean query operator; select * from foo except * from bar; This will give you rows in foo that are not exactly in bar (matching every field). If you want it in both directions you can: (select * from foo except select * from bar) union (select * from bar except select * from foo); you can then take the results of these queries and mark up the text however you want. Just a heads up: the boolean sql operators are famous for generating sequential scans. merlin Hi, Thanks for replying! I think this isn't much help for me though. For example; SELECT att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3; att_id | att_name| att_pt_id +---+-- 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,39,40,41 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,36 3 | Bay, Internal 3.5quot; drive | 44,7,8,1,26,36 SELECT att_id, att_name, att_pt_id FROM attribute WHERE att_id=3; att_id | att_name| att_pt_id +---+ 3 | Bay, Internal 3.5quot; drive | 44,7,8,1,26,36 SELECT att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3 EXCEPT SELECT att_id, att_name, att_pt_id FROM attribute WHERE att_id=3; att_id | att_name | att_pt_id +--+-- 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,36 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,39,40,41 This shows me the rows in the history schema that are not in the public schema, which is nice but it doesn't tell me which columns have changed in each version. What I would like would be results like (pseudo again): SELECT DIFF history_id, att_id, att_name, att_pt_id FROM history.attribute WHERE att_id=3 ORDER BY modified_date DESC; history_id | att_id | att_name| att_pt_id ++---+-- 86 || Bay, Internal 3.5quot; drive | 85 || | 44,7,8,1,26,36 82 | 3 | Bay, Internal 3.5 drive | 44,7,8,1,26,39,40,41 The first row being all new so all items return, the second row returns only the 'att_pt_id' which changed, and the third returning 'att_name' which changed. Thanks again!! Madi ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Some unknown error in a function
Hi Following is the script of my plpgsql function CREATE OR REPLACE FUNCTION sp_insert_tbl_vrfy_mx(int4,text, text, inet, text, text) RETURNS void AS$$ DECLARE sequence_no int4; BEGIN SELECT INTO sequence_no MAX(seq_no) FROM tbl_verify_mx WHERE unmask_id = $1; IF sequence_no 1 THEN sequence_no = sequence_no + 1; ELSE sequence_no = 1; END IF; IF $4 =' ' THEN INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local, email_domain, mail_server, mx_records ) VALUES ($1,sequence_no,$2,$3,$5,$6) ; ELSE INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local, email_domain, ip_address, mail_server, mx_records ) VALUES ($1,sequence_no,$2,$3,CAST($4 as inet), $5,$6) ; END IF; END; $$ LANGUAGE 'plpgsql' VOLATILE; I run this function using select sp_insert_tbl_vrfy_mx(55,'jas','xyz.com','192.168.0.105', ' mail.xyz.com,'mxrecoredmxjdlkfjdk') and get the following error:- CONTEXT: SQL statement SELECT $1 =' ' PL/pgSQL function sp_insert_tbl_vrfy_mx line 12 at if Don't know where I'm going wrong. Thanks Jas
Re: [GENERAL] Modifying a foreign key constraint?
ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey; ALTER TABLE foo ADD FOREIGN KEY (foo_bar_id_fkey) REFERENCES bar(id) ON DELETE CASCADE; Is there a more compact way to do this, perhaps with a single ALTER TABLE command? Sure there is, you can preform multiple alterations in one statement: ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey, ADD CONSTRAINT foo_bar_id_fkey FOREIGN KEY (bar_fkey) REFERENCES bar (id) ON DELETE CASCADE; regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Some unknown error in a function
Jasbinder Singh Bali [EMAIL PROTECTED] writes: I run this function using select sp_insert_tbl_vrfy_mx(55,'jas','xyz.com','192.168.0.105', ' mail.xyz.com,'mxrecoredmxjdlkfjdk') and get the following error:- CONTEXT: SQL statement SELECT $1 =' ' PL/pgSQL function sp_insert_tbl_vrfy_mx line 12 at if You didn't show us the actual error message, but I suppose it's unhappy that ' ' is not a legal inet value. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Some unknown error in a function
IF $4 = ' ' THEN
Here ' ' is not a valid inet value. If you really want to check to see
if the inet value is null, you can cast it to text and compare it.
For example: IF text('$4') = '' Then
That will fix your issue.
-Chandra Sekhar Surapaneni
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Jasbinder Singh
Bali
Sent: Friday, February 09, 2007 11:30 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Some unknown error in a function
Hi
Following is the script of my plpgsql function
CREATE OR REPLACE FUNCTION sp_insert_tbl_vrfy_mx(int4,text, text, inet,
text, text)
RETURNS void AS$$
DECLARE
sequence_no int4;
BEGIN
SELECT INTO sequence_no MAX(seq_no) FROM tbl_verify_mx WHERE
unmask_id = $1;
IF sequence_no 1 THEN
sequence_no = sequence_no + 1;
ELSE
sequence_no = 1;
END IF;
IF $4 =' ' THEN
INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local,
email_domain, mail_server, mx_records )
VALUES ($1,sequence_no,$2,$3,$5,$6) ;
ELSE
INSERT INTO tbl_verify_mx(unmask_id, seq_no, email_local,
email_domain, ip_address, mail_server, mx_records )
VALUES ($1,sequence_no,$2,$3,CAST($4 as inet), $5,$6) ;
END IF;
END;
$$
LANGUAGE 'plpgsql' VOLATILE;
I run this function using
select sp_insert_tbl_vrfy_mx(55,'jas','xyz.com','192.168.0.105', '
mail.xyz.com http://mail.xyz.com ,'mxrecoredmxjdlkfjdk')
and get the following error:-
CONTEXT: SQL statement SELECT $1 =' '
PL/pgSQL function sp_insert_tbl_vrfy_mx line 12 at if
Don't know where I'm going wrong.
Thanks
Jas
Re: [GENERAL] Postgres SQL Syntax
On 2/2/07, Jim C. [EMAIL PROTECTED] wrote: Besides what Tom says, '0' is a string, not an integer. PG takes it, but it's a bad habit. Maybe it is and maybe it isn't. I wouldn't know. I'm merely the unfortunate soul chosen to convert this from MySQL to Postgres. :-/ I've been working on it for a week now. I've got to say that it pains me to know that there is apparently no open standard in use for importing/exporting data from one db to another. XML would do the job, wouldn't it? If I'm wrong, I sure would like to hear about it. the open standard to convert data from one database to another, unfortunately, is SQL. SQL is incomplete, illogical, and obscure, so here we are. moving data from mysql to postgresql is easy...its the table schemas that are tough. If you have the table schemas done, you can mysqldump --compatible=postgresql | psql which should work for 90% of tables, because mysql supports only a subset of the types postgresql supports. the schemas are a bit trickier...you have to do them by hand or use a conversion tool. one such tool is DTS. if you look around you might find something else though. merlin ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Modifying a foreign key constraint?
Richard Broersma Jr wrote: Sure there is, you can preform multiple alterations in one statement: ALTER TABLE foo DROP CONSTRAINT foo_bar_id_fkey, ADD CONSTRAINT foo_bar_id_fkey FOREIGN KEY (bar_fkey) REFERENCES bar (id) ON DELETE CASCADE; Doh! That's perfectly fine, of course. I was wondering if there might be something along the lines of an ALTER CONSTRAINT clause, which could change a specific aspect of a constraint without having to re-specify the whole thing. Marc ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Modifying a foreign key constraint?
I was wondering if there might be something along the lines of an ALTER CONSTRAINT clause, which could change a specific aspect of a constraint without having to re-specify the whole thing. As you see from this link: http://www.postgresql.org/docs/8.2/interactive/sql-commands.html There is no alter constraint command. However for the Alter table syntax this link shows pretty much all you can do: http://www.postgresql.org/docs/8.2/interactive/sql-altertable.html Regards, Richard Broersma Jr. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] ldap auth problem
On 1/2/07, Ing. Dan Horáček [EMAIL PROTECTED] wrote: Hi, I've just installed postgres 8.2.0-1 for win32 and wanted to try out the built-in LDAP support(with Openldap and Novell eDirectory). The following LDAP authentication was added to pg_hba.conf : Openldap: host all all 127.0.0.1/32 ldap ldap://localhost/dc=dandoma,dc=cz;cn=;,dc=dandoma,dc=cz However when trying to connect, it gave the error. Look at the server log: 2006-12-31 19:34:59 LOG: LDAP login failed for user cn=postgresdc=dandomadc=cz on server localhost: error code 34 2006-12-31 19:34:59 FATAL: LDAP authentication failed for user postgres eDirectory: pg_hba.conf: host all all 127.0.0.1/32 ldap ldap://192.168.168.2/ou=win2000,o=plhb;cn=;,ou=win2000,o=plhb Server log: 2006-12-29 13:50:18 LOG: LDAP login failed for user cn=xxxou=win2000o=plhb on server 192.168.168.2: error code 32 2006-12-29 13:50:18 FATAL: LDAP authentication failed for user xxx The problem seems to be in conversion of comma in suffix. Thanks in advance. Dan I have found that quoting the suffix works. The conversion does not happen when quoting. ---(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
Re: [GENERAL] Postgres SQL Syntax
- Original Message - From: Merlin Moncure [EMAIL PROTECTED] the open standard to convert data from one database to another, unfortunately, is SQL. SQL is incomplete, illogical, and obscure, so here we are. The same can be said about any programming language, can it not? Even languages as powerful and expressive as C++ and Perl have elements that seem illogical. That they are incomplete is substantially demonstrated by the existence of the boost library, most of which ought to be added to the C++ standard library, and CPAN. But for perl, there is no standard so it is hard to say definitively where the language ends and developer's libraries begin. I am sure that all of the programming students I have taught have found C++ template metaprogramming obscure and very difficult to fathom, but I don't think it is possible at this time to beat Perl, and especially its object model, for obscurity. I will concede, though, that this impression may be an artifact of my being used to the object models in C++ and Java, and that therefore the perl object model just appears to me to be a bit weird. For the same reason, I find some aspects of SQL difficult to fathom. That may be because I am not as familiar with set theory as I am with algebra and calculus and analytic geometry, or it may be an artifact of the languages with which I am most comfortable. I don't know if you can do statistical analyses, such as nonlinear least squares, time series analysis r chi-squared tests within SQL, but when faced with such a problem I tend to follow the path of least resistance and export the data from the database into my client code and do whatever analysis I need using my C++ or Java code. But it there is a lot of data, I suspect there would be much less impact on network traffic, and probably better performance, if the analysis could be done in a SQL stored procedure. One of the more common analyses I need to do involves time series analysis, sometimes with some kind of smoothing (such as a moving average) applied before the analysis proper. moving data from mysql to postgresql is easy...its the table schemas that are tough. If you have the table schemas done, you can When I design my databases, I normally assume that I may have to migrate the data from one RDBMS profuct to another, for whatever reason. Therefore, I have the ones I am most likely to have to support running here. I create a SQL script to create my databases, aiming to use the highest common factor across the RDBMS' SQL, and test it on each to verify that I succeeded in finding the higest common factor. I have, then, a simple perl script to execute the script, and it knows which tools to use based on a configuration file that has the specifics for using MySQL or Postgresql or MS SQL Server, and a commandline parameter that specifies which DB to use. mysqldump --compatible=postgresql | psql which should work for 90% of tables, because mysql supports only a subset of the types postgresql supports. the schemas are a bit trickier...you have to do them by hand or use a conversion tool. one such tool is DTS. if you look around you might find something else though. There's many more than one way to skin a cat. My inclination is to take a brute force, albeit brain-dead, approach. One of my irritants is that none of the RDBMS products appear to support the same ways of importing data, e.g. from a flat file (CSV files, for instance). But at least they all do it in some way, and they all are able to export a table to, e.g. a csv file. Therefore, it becomes trivially easy to move data, even in the absence of a tool like DTS, by exporting the data to a CSV file and then reading that file into the target RDBMS. This is something I've done countless times. But, not having found a way to easily determine the schema programmatically, so my perl or Java or C++ code can be generic enough to apply to any of my databases, I find myself taking an hour or two to write a simple script, usually in Perl, to handle each table in sequence. There are, of course, things to watch, such as keeping the table creation statements in a different script than that which creates indices and especially foreign keys, so the tables are ready when the data is to be loaded, but the constraints are created after the data is loaded, purely for performance reasons. I learned the hard way that loading data slows down dramatically if the database has to continually check constraints, so I create constraints after loading valid data, and leave them in place only while new data is to be stored. I guess I am recommending that the schemas be done by hand, regardless of whether you're creating a new database or repairing or migrating an old one, even if the original developer wasn't considerate enough to create, or provide, an appropriate SQL script to create the database de novo. it seems to me to be risky to
Re: [GENERAL] Postgres SQL Syntax
Ted Byers wrote: - Original Message - From: Merlin Moncure [EMAIL PROTECTED] the open standard to convert data from one database to another, unfortunately, is SQL. SQL is incomplete, illogical, and obscure, so here we are. The same can be said about any programming language, can it not? Even languages as powerful and expressive as C++ and Perl have elements that seem illogical. Perl has elements that are illogical? Surely you jest! Joshua D. Drake -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] bytea characteristics
Are there any known guidelines regarding storing images in a bytea column vs simply storing the meta-data? Do the images take up a certain percentage more space due to the on- disk format when stored this way? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
