Re: [GENERAL] How to retrieve number of rows affected, in an after statement trigger?
Thank you for the answer. At least I'm glad it is possible! But , as I don't know Perl, could you, please, tell me how to accomplish it in plpgsql. Thanks, Zlatko - Original Message - From: "Greg Sabino Mullane" <[EMAIL PROTECTED]> To: Sent: Saturday, July 07, 2007 1:47 PM Subject: Re: [GENERAL] How to retrieve number of rows affected, in an after statement trigger? -BEGIN PGP SIGNED MESSAGE- Hash: RIPEMD160 Is it possible to retrieve information about how many rows were changed/inserted in a table that fired after statement trigger? Not directly, but you can store the information from row-level triggers and gather it at the end. See this plperl example: http://people.planetpostgresql.org/greg/index.php?/archives/2007/05/30.html - -- Greg Sabino Mullane [EMAIL PROTECTED] PGP Key: 0x14964AC8 200707070745 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFGj30hvJuQZxSWSsgRA9iXAJ0aiS5oVbYxcY69yY0zvig4G4eBTwCdE5ON EYV77TeTYNSRt46fsZDfkTI= =C7mD -END PGP SIGNATURE- ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] optimizing postgres
Hello, Tom. I don't understand relation between constraints and indexes. By using EMS PostgreSQL Manager Lite, I created indexes on columns, some of them are unique values. But when I open it in PgAdmin, all such "unique" indexes are listed as constraints and there are no indexes in Indexes section. When I open it again in EMS PostgreSQL Manager, they are listed as "Indexes". Does it mean that I need to create additional indexes on the same columns? Is "Constrain" index as well? Thanks, Zlatko - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Cc: Sent: Friday, July 13, 2007 3:39 AM Subject: Re: [GENERAL] optimizing postgres [EMAIL PROTECTED] writes: It turned out he was right for our current set up. When I needed to empty the project table to re-parse data, doing a cascading delete could take up to 10 minutes! You mean ON CASCADE DELETE foreign keys? Usually the reason that's slow is you forgot to put an index on the referencing column. PG doesn't force you to have such an index, but unless the referenced table is nearly static you'll want one. I too am fairly suspicious of the N-tables-are-faster-than-another- key-column mindset, but you'd need to do some actual experimentation (with correctly optimized table definitions ;-)) to be sure. regards, tom lane ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] multirow insert
When using multirow INSERT INTO...VALUES command, are all rows inserted in a batch, or row by row? Regards, Zlatko
[GENERAL] authorizations for a statement
What authorizations are needed for the following statement: UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'orders'; ?
[GENERAL] ERROR: a column definition list is required for functions returning "record"
I have a plpgsql function that returns dataset. First it was defined to return SETOF someview. Then I changed it to return SETOF RECORD, in order to be able to return dataset with varying number of columns. But, I get the following error:"ERROR: a column definition list is required for functions returning "record" SQL state: 42601". What does it mean? What is "columns definition list"? Thanks, Zlatko
Re: [GENERAL] ERROR: a column definition list is required for functions returning "record"
OK. Thanks. Regards, Zlatko - Original Message - From: "Pavel Stehule" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, July 17, 2007 12:14 PM Subject: Re: [GENERAL] ERROR: a column definition list is required for functions returning "record" Hello you have to specify column names and column types. Like postgres=# create or replace function foog() returns setof record as $$ declare r record; begin r := row(10,20); return next r ; return; end; $$ language plpgsql; postgres=# select * from foog() t(a int, b int); a | b + 10 | 20 (1 row) Regards Pavel Stehule 2007/7/17, Zlatko Matić <[EMAIL PROTECTED]>: I have a plpgsql function that returns dataset. First it was defined to return SETOF someview. Then I changed it to return SETOF RECORD, in order to be able to return dataset with varying number of columns. But, I get the following error:"ERROR: a column definition list is required for functions returning "record" SQL state: 42601". What does it mean? What is "columns definition list"? Thanks, Zlatko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/ ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] protect a database
Hello. Is there any way to hide database structure (at least functions and triggers) from a superuser/administrator? Regards, Zlatko
[GENERAL] several postgres installations on the same machine?
Is it possible to have few independant PostgreSQL 8.2 installations on the same PC, WIndows XP? Thanks, Zlatko
Re: [GENERAL] several postgres installations on the same machine?
Hi. If I understood correctly, this blog describes how to create second instance that is linked to first (the same service acount user)? But, I want to know whether it is possible to have second instance completely independent, not influencing each other? Regards, Zlatko - Original Message - From: Anoo Sivadasan Pillai To: Zlatko Matić Cc: pgsql-general@postgresql.org Sent: Friday, July 20, 2007 8:40 AM Subject: Re: [GENERAL] several postgres installations on the same machine? If you mean multiple instances, Then it can be. Have a look on Pauls personal Blog http://people.planetpostgresql.org/paul/index.php?/archives/4-Running-a-Second-Instance-of-PostgreSQL-8.1.4-on-Windows-2003.html Anoo S Pillai -- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zlatko Matic Sent: Friday, July 20, 2007 11:51 AM To: pgsql-general@postgresql.org Subject: [GENERAL] several postgres installations on the same machine? Is it possible to have few independant PostgreSQL 8.2 installations on the same PC, WIndows XP? Thanks, Zlatko Visit our Website at www.rmesi.co.in This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications. Freedom of Information Act 2000 This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests. This email has been scanned for viruses by Trend ScanMail.
[GENERAL] privillages for pg_class
Hello. I use following statements for temporarily disable triggers and enable them again: --Disable triggers. UPDATE "pg_class" SET "reltriggers" = 0 WHERE "relname" = 'orders'; --Enable triggers. UPDATE pg_class SET reltriggers = ( SELECT count(*) FROM pg_trigger where pg_class.oid = tgrelid) WHERE relname = 'orders'; It works when connected as a superuser. But, when I try to execute it as a normal user, not enough privillages, although I granted all privillages to pg_class and pg_trigger. Do I miss something? Thanks.
Re: [GENERAL] several postgres installations on the same machine?
I followed instructions from the blog, but when applying initdb command I have the following error: "initdb: file "C:/Program Files/PostgreSQL/8.2/share/postgres.bki" does not exist. This means you have a corrupted installation or identified the wrong directory with the invocation option -L.". I tried to find that postgres.bki file, but it seems it doesn't exist. What now? Regards, Zlatko - Original Message - From: Anoo Sivadasan Pillai To: Zlatko Matić Cc: pgsql-general@postgresql.org Sent: Friday, July 20, 2007 8:40 AM Subject: Re: [GENERAL] several postgres installations on the same machine? If you mean multiple instances, Then it can be. Have a look on Pauls personal Blog http://people.planetpostgresql.org/paul/index.php?/archives/4-Running-a-Second-Instance-of-PostgreSQL-8.1.4-on-Windows-2003.html Anoo S Pillai -- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Zlatko Matic Sent: Friday, July 20, 2007 11:51 AM To: pgsql-general@postgresql.org Subject: [GENERAL] several postgres installations on the same machine? Is it possible to have few independant PostgreSQL 8.2 installations on the same PC, WIndows XP? Thanks, Zlatko Visit our Website at www.rmesi.co.in This message is confidential. You should not copy it or disclose its contents to anyone. You may use and apply the information for the intended purpose only. Internet communications are not secure; therefore, RMESI does not accept legal responsibility for the contents of this message. Any views or opinions presented are those of the author only and not of RMESI. If this email has come to you in error, please delete it, along with any attachments. Please note that RMESI may intercept incoming and outgoing email communications. Freedom of Information Act 2000 This email and any attachments may contain confidential information belonging to RMESI. Where the email and any attachments do contain information of a confidential nature, including without limitation information relating to trade secrets, special terms or prices these shall be deemed for the purpose of the Freedom of Information Act 2000 as information provided in confidence by RMESI and the disclosure of which would be prejudicial to RMESI's commercial interests. This email has been scanned for viruses by Trend ScanMail.
[GENERAL] encodings
Hello. If I have an UTF8 database, dump it and the restore as WIN1250 database, then dump it again and restore as UTF8, would structure of the database (schema) be exactly the same as initial database, or something will change in the process? In other words, does encoding influence only data stored in tables, or it influences database structure as well? Thanks. Zlatko
Re: [GENERAL] encodings
No, I didn't, I'm just courious. Regards, Zlatko - Original Message - From: "Michael Fuhr" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Sunday, July 22, 2007 5:41 AM Subject: Re: [GENERAL] encodings On Sat, Jul 21, 2007 at 10:24:38PM +0200, Zlatko Matić wrote: If I have an UTF8 database, dump it and the restore as WIN1250 database, then dump it again and restore as UTF8, would structure of the database (schema) be exactly the same as initial database, or something will change in the process? In other words, does encoding influence only data stored in tables, or it influences database structure as well? I can't think of how the encoding would influence the structure. Are you seeing behavior that suggests otherwise? -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] double quotes inside VBA string ?
Hello. I have a problem when working with MS Access/PostgreSQL. Namely, as PostgreSQL syntax uses doble quotes for table names and field names, when I write it as a string of a pass-through query or Command text of ADO Command object it looks like:"select * from public."Customers""and VBA considers that the first quote after public. is the end of statement.How to solve it ?
[GENERAL] logout Postgres from MS Access (ODBC) ?
Hi!In MS Access/PostgreSQL combination I have login form. After user writes all neccessary inputs and push the button, a startup procedure creates DSN-less connection string, receates all linked tables and change connection string in all pass-through queries. It works nice...But, how to achieve LOGOUT ? I would like to allow users to login/logout consecutively from MS Access front-end without need to close front-end. I tried to perform new login with the same login form after already being login, but it seems that Access accepts even wrong passwords after connection once established (!?) It seems that consecutive login does not affect at all, after ODBC connection was once established...If you give correct username, new DSN-less connection will be accepted even if the password is wrong...tabledef.RefreshLink didn't help...So, I think that I have to close all ODBC connections (linked tables, pass-through queries) somehow and the enforce new login. How to do it ? Also, I tried to use pg_shadow system table for inspecting whether password is OK, but it seems that password is encrypted, so Access can't compare password from pg.shadow and password on the login form. What to do in order that Access can see actual password from pg_shadow ? Thanks.
[GENERAL] users,groups and permissions
Hello. If we have a database with defined user groups, users and permissions on database objects, what happens when we backup database (dump) and try to install on some other server ? Does information about user groups, users and permissions migrate along with database itself or I need to configure it separately on each computer ?
[GENERAL] retrieving information about password from MS Access front-end
How could I retrieve information about actual user password, if md5 method is set in pg_hba.conf ? I want to create VBA procedure that compares value in text box with actual password to determine if it is the same. As the password is encrypted (md5) I can't read it from pg_shaddow system table. How to retrieve decrypted value from pg_shaddow? Or at least, how to transfer value from text-box (MS Access) to Postgres so that Postgres can determine whether those two values are the same ? Thanks in advance, Zlatko
Re: [GENERAL] Pb with linked tables on PG8
Hi. I'm forwarding you what I answered to Rolland about it. This "#deleted" phenomena is something often, but can be easily solved. It seems to be common problem with Access connectiong to ODBC data source. Access has some problems to determine primary key... You should not use textual fields as primary key. Insted, add some bigserial (integer autoincrement field) into your tables and make such field primary key. Then relink your tables. This will solve your problem. Also, it is good to add timestamp field into tables. Also, be aware that your tables names should not be too long, because if they are long you will have problems with relinking. Access would not relink correctly (preassuming that you will use DSN-less and relinking on each startup). Bye. Zlatko For your information, this is explanation from MSDN: " ACC: "#Deleted" Errors with Linked ODBC Tables View products that this article applies to. Article ID : 128809 Last Review : May 6, 2003 Revision : 1.0 This article was previously published under Q128809 On this page SYMPTOMS CAUSE RESOLUTION MORE INFORMATION Steps to Reproduce Behavior APPLIES TO SYMPTOMS When you retrieve, insert, or update records in a linked ODBC table, each field in a record contains the "#Deleted" error message. When you retrieve, insert, or update records using code, you receive the error message "Record is deleted." Back to the top CAUSE The Microsoft Jet database engine is designed around a keyset-driven model. This means that data is retrieved, inserted, and updated based on key values (in the case of a linked ODBC table, the unique index of a table). After Microsoft Access performs an insert or an update of a linked ODBC table, it uses a Where criteria to select the record again to verify the insert or update. The Where criteria is based on the unique index. Although numerous factors can cause the select not to return any records, most often the cause is that the key value Microsoft Access has cached is not the same as the actual key value on the ODBC table. Other possible causes are as follows: . Having an update or insert trigger on the table, modifying the key value. . Basing the unique index on a float value. . Using a fixed-length text field that may be padded on the server with the correct amount of spaces. . Having a linked ODBC table containing Null values in any of the fields making up the unique index. These factors do not directly cause the "#Deleted" error message. Instead, they cause Microsoft Access to go to the next step in maintaining the key values, which is to select the record again, this time with the criteria based on all the other fields in the record. If this step returns more than one record, Microsoft Access returns the "#Deleted" message because it does not have a reliable key value to work with. If you close and re-open the table or choose Show All Records from the Records menu, the "#Deleted" errors are removed. Microsoft Access uses a similar process to retrieve records from an linked ODBC table. First, it retrieves the key values and then the rest of the fields that match the key values. If Microsoft Access is not able to find that value again when it tries to find the rest of the record, it assumes that the record is deleted. Back to the top RESOLUTION The following are some strategies that you can use to avoid this behavior: . Avoid entering records that are exactly the same except for the unique index. . Avoid an update that triggers updates of both the unique index and another field. . Do not use a Float field as a unique index or as part of a unique index because of the inherent rounding problems of this data type. . Do all the updates and inserts by using SQL pass-through queries so that you know exactly what is sent to the ODBC data source. . Retrieve records with an SQL pass-through query. An SQL pass-through query is not updateable, and therefore does not cause "#Delete" errors. . Avoid storing Null values within any field making up the unique index of your linked ODBC table. Back to the top MORE INFORMATION Note: In Microsoft Access 2.0, linked tables were called attached tables. Steps to Reproduce Behavior 1. Open the sample database Northwind.mdb (or NWIND.MDB. in Microsoft Access 2.0) 2. Use the Upsizing Tools to upsize the Shippers table. NOTE: This table contains an AutoNumber field (or Counter field in Microsoft Access 2.0) that is translated on SQL Server by the Upsizing Tools into a trigger that emulates a counter. 3. Open the linked Shippers table and enter a new record. Make sure that the record you enter has the same data in the Company Name field as the previous record. 4. Press TAB to move to a new record. Note that the "#Deleted" error fills the record you entered. 5. Close and re-open the table. Note that the record i
Re: [GENERAL] [INTERFACES] retrieving information about password from MS Access front-end
Thanks. - Original Message - From: "Volkan YAZICI" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: ; <[EMAIL PROTECTED]> Sent: Monday, June 13, 2005 8:47 AM Subject: Re: [GENERAL] [INTERFACES] retrieving information about password from MS Access front-end Hi, On 6/13/05, Zlatko Matić <[EMAIL PROTECTED]> wrote: I want to create VBA procedure that compares value in text box with actual password to determine if it is the same. As the password is encrypted (md5) I can't read it from pg_shaddow system table. How to retrieve decrypted value from pg_shaddow? It's (practically) impossible to decrypt a MD5 hashed password. Or at least, how to transfer value from text-box (MS Access) to Postgres so that Postgres can determine whether those two values are the same ? You need to MD5 hash the input passsword and compare hashed input password with the one in pg_shadow. For instance: -- $1 for password input. SELECT usernm FROM recs WHERE passwd = md5($1) Furthermore, it'll bring some potential security problems to be able to access pg_shadow which requires db admin permissions. Regards. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(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
[GENERAL] user groups
When I create some user group and set permissions and then dump (backup) database and install on some other computer, what will happen with groups and permissions? Should they exist on other computers too ? I don't understand whether permissions to groups are something that is integrated in the database or it is just attached to database ? Greetings, Zlatko
[GENERAL] users/users groups management from MS Access front-end
Does anyone have some experience with users management from MS Access front-end ?
Re: [GENERAL] user groups
Hi. Thank you for information. How can I dump_all from pgAdmin III ? Thanks. - Original Message - From: "John DeSoi" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, June 15, 2005 1:25 AM Subject: Re: [GENERAL] user groups On Jun 14, 2005, at 6:17 PM, Zlatko Matić wrote: When I create some user group and set permissions and then dump (backup) database and install on some other computer, what will happen with groups and permissions? Should they exist on other computers too ? I don't understand whether permissions to groups are something that is integrated in the database or it is just attached to database ? Greetings, If you use pg_dumpall, then all the user and group information will be included in the backup file. See http://www.postgresql.org/docs/8.0/interactive/app-pg-dumpall.html John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How to set an expiration date for a WHOLE user account
Hi. Concerning Együd's question, I also wanted to ask about setting expiration date for database. But, I would like to set validity in sense of certain actions. For example, I would like to prevent adding new records after expiration, but would allow viewing existing records Is there any way to acomplish that? Greetings, Zlatko - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Qingqing Zhou" <[EMAIL PROTECTED]> Cc: Sent: Wednesday, June 15, 2005 7:08 AM Subject: Re: [GENERAL] How to set an expiration date for a WHOLE user account "Qingqing Zhou" <[EMAIL PROTECTED]> writes: "Együd Csaba" <[EMAIL PROTECTED]> writes I know the VALID UNTIL clause of CREATE USER command, but it is about the password only. I think something similar but regarding the whole user account. It is not about password only. Once current date is beyond the valid date you set, the user can never get authorized ok anymore. He's right, you're not: that check is only applied in the password-based authorization path. This has always seemed a bit bogus to me too --- would not object to a well-thought-out patch to change it. regards, tom lane ---(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 ---(end of broadcast)--- TIP 8: explain analyze is your friend
[GENERAL] pg_dumpall
How to start pg_dumpall ?
Re: [GENERAL] enebling regular user to create new users ?
Per-database user, right, that's what I need...hope it will be included in future releases... Separating the power to create new users from the power of being superuser, also. It is very important. Greetings, Zlatko - Original Message - From: "Richard Huxton" To: "Tom Lane" <[EMAIL PROTECTED]> Cc: "Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Wednesday, June 15, 2005 9:35 PM Subject: Re: [GENERAL] enebling regular user to create new users ? Tom Lane wrote: Richard Huxton writes: Zlatko Matiæ wrote: I was thinking about two possible scenarios: a) to allow regular users to create new users b) to restrict superuser's permissions What is possible and what do you suggest ? Neither is possible directly. (B) means they're not a superuser and (A) means they are. There has been some talk of separating the power to create new users from the power of being superuser (although presumably only a superuser should be allowed to create new superusers). If the planned pg_role rewrite gets submitted before the 8.1 feature freeze, I might look at adding that frammish into it. Did I see talk of per-database users too? That would be a sensible dividing-line I suppose - you could have complete control of "your" database and who can access it without interfering with anyone else. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pg_dumpall
Done. Thanks. - Original Message - From: "Sean Davis" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Thursday, June 16, 2005 11:51 AM Subject: Re: [GENERAL] pg_dumpall See: http://www.postgresql.org/docs/8.0/static/app-pg-dumpall.html If you need more specifics, you will probably need to give more specifics like OS and version. Sean On Jun 16, 2005, at 5:16 AM, Zlatko Matić wrote: How to start pg_dumpall ? ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] user/groups query ?
Hello. How to make a query that will include information both about user and group (or groups) he belongs to ? Thanks.
Re: [GENERAL] user/groups query ?
Thank you very much !
- Original Message -
From: "John DeSoi" <[EMAIL PROTECTED]>
To: "Zlatko Matić" <[EMAIL PROTECTED]>
Cc:
Sent: Monday, June 20, 2005 3:40 AM
Subject: Re: [GENERAL] user/groups query ?
On Jun 19, 2005, at 3:56 PM, Zlatko Matić wrote:
How to make a query that will include information both about user and
group (or groups) he belongs to ?
Thanks.
Turning on the ECHO_HIDDEN feature in psql shows how to do this.
\set ECHO_HIDDEN 1
\du user1
* QUERY **
SELECT u.usename AS "User name",
u.usesysid AS "User ID",
CASE WHEN u.usesuper AND u.usecreatedb THEN CAST('superuser, create
database' AS pg_catalog.text)
WHEN u.usesuper THEN CAST('superuser' AS pg_catalog.text)
WHEN u.usecreatedb THEN CAST('create database' AS
pg_catalog.text)
ELSE CAST('' AS pg_catalog.text)
END AS "Attributes",
ARRAY(SELECT g.groname FROM pg_catalog.pg_group g WHERE u.usesysid =
ANY(g.grolist)) as "Groups"
FROM pg_catalog.pg_user u
WHERE u.usename ~ '^user1$'
ORDER BY 1;
**
List of users
User name | User ID | Attributes | Groups
---+-++
user1 | 100 || {test}
(1 row)
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [GENERAL] enebling regular user to create new users ?
Berend, Thank you for the function code. It helped me a lot! Regards, Zlatko ---Original Message--- From: Berend Tober Date: 06/15/05 18:08:22 To: Zlatko Matić Cc: pgsql-general@postgresql.org Subject: Re: [GENERAL] enebling regular user to create new users ? Zlatko Matić wrote: > I know that superusers are allowed to do everything on the database, > but I consider this as dangerous. I want to have some user group with > rights of creating new users and giving them some authorizations, but > without such wide power as superusers have. So, > I was thinking about two possible scenarios: > a) to allow regular users to create new users > b) to restrict superuser's permissions > > What is possible and what do you suggest ? CREATE OR REPLACE FUNCTION create_user(name) RETURNS bool AS ' DECLARE PWD VARCHAR; CMD VARCHAR; BEGIN PWD := \'\'\'\' || get_random_string(8) || \'\'\'\'; IF EXISTS(SELECT 1 FROM pg_user WHERE usename = $1) THEN RETURN FALSE; END IF; CMD := \'CREATE USER "\' || $1 || \'" WITH ENCRYPTED PASSWORD \' || PWD || \' IN GROUP gen_user\'; EXECUTE CMD; RETURN TRUE; END; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; REVOKE ALL ON FUNCTION create_user(name) FROM public; GRANT EXECUTE ON FUNCTION create_user(name) TO GROUP pseudo_dba; CREATE OR REPLACE FUNCTION alter_group(name, bool, name) RETURNS bool AS ' DECLARE l_group ALIAS FOR $1; l_create_user ALIAS FOR $2; l_username ALIAS FOR $3; CMD VARCHAR; MIN_SUPER_USER INTEGER := 1; BEGIN IF (l_create_user NOTNULL) THEN IF (l_create_user) THEN PERFORM create_user(l_username); CMD := \'ALTER GROUP \' || l_group || \' ADD USER "\' || l_username || \'"\'; EXECUTE CMD; ELSIF (l_group = \'gen_user\') THEN PERFORM drop_user(l_username); ELSE CMD := \'ALTER GROUP \' || l_group || \' DROP USER "\' || l_username || \'"\'; EXECUTE CMD; END IF; IF (SELECT COUNT(*) FROM group_members WHERE groname = \'pseudo_dba\') < MIN_SUPER_USER THEN RAISE EXCEPTION \'At least % super user(s) must be defined in order to create new user accounts.\', MIN_SUPER_USER; END IF; END IF; RETURN TRUE; END; ' LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER; REVOKE ALL ON FUNCTION alter_group(name, bool, name) FROM public; GRANT EXECUTE ON FUNCTION alter_group(name, bool, name) TO GROUP pseudo_dba; -- etc., etc., etc.,
[GENERAL] help about the function
Hello!
I have implemented solution for enabling regular
user (from group "ADMINS") to create new users in predefined groups, by your
modified function:
CREATE OR REPLACE FUNCTION "public"."alter_group"
(name, boolean, name, varchar, timestamp) RETURNS boolean
AS$body$DECLARE l_group ALIAS FOR $1; l_create_user
ALIAS FOR $2; l_username ALIAS FOR $3; l_password ALIAS FOR
$4; l_validity ALIAS FOR $5; CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;BEGINIF (l_create_user NOTNULL)
THEN IF (l_create_user)
THEN CMD := 'CREATE USER "' || l_username ||
'" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID
UNTIL ''' || l_validity || ; EXECUTE
CMD; ELSE CMD := 'DROP
USER "' || l_username || '"'; EXECUTE
CMD; END IF; IF (SELECT COUNT(*)
FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER
THEN RAISE EXCEPTION 'At least % admin(s) in
group ADMINS must be defined in order to create new user accounts.',
MIN_SUPER_USER; END IF; END IF; RETURN
TRUE;END;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT
SECURITY DEFINER;
Validity is set in the table
public."VALIDITY" in the field "VALIDITY" timestamp. There is also a view
called "VALIDITY_VIEW" which reads the actual validity value from the table. It
returns only one row-one field:
CREATE OR REPLACE VIEW "VALIDITY_VIEW" AS SELECT
"VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID") AS
"RV_ID",
"VALIDITY"."VALIDITY"
FROM "VALIDITY" GROUP
BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
ALTER TABLE "VALIDITY_VIEW" OWNER TO zmatic;
MS Access front-end calls this function through VBA
code. Access creates a recordset on the base of the VALIDITY_VIEW to inspect the
value of validity time, assigns it to a variable and then passes as the
parameter l_validity to the server function "alter_group". It works nice,
but I realized that it is not safe, because someone from the group "ADMINS"
could create its own query in Access with different "validity" and execute
it without restriction. Therefore I think that "l_validity" timestamp
($5) should not be input parameter for the function "alter_group", but
rather declared variable that reads the value of validity from the table
directly.
I tried to modify the function into something like
this:
CREATE OR REPLACE FUNCTION "public"."alter_group"
(name, boolean, name, varchar) RETURNS boolean AS$body$DECLARE
l_group ALIAS FOR $1; l_create_user ALIAS FOR $2; l_username
ALIAS FOR $3; l_password ALIAS FOR $4; l_validity
timestamp; CMD VARCHAR; MIN_SUPER_USER INTEGER :=
1;BEGIN
l_validity := SELECT "VALIDITY"."VALIDITY" FROM (
SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY"
GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
IF (l_create_user NOTNULL)
THEN IF (l_create_user)
THEN CMD := 'CREATE USER "' || l_username ||
'" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '" VALID
UNTIL ''' || l_validity || ; EXECUTE
CMD; ELSE CMD := 'DROP
USER "' || l_username || '"'; EXECUTE
CMD; END IF; IF (SELECT COUNT(*)
FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') < MIN_SUPER_USER
THEN RAISE EXCEPTION 'At least % admin(s) in
group ADMINS must be defined in order to create new user accounts.',
MIN_SUPER_USER; END IF; END IF; RETURN
TRUE;END;$body$LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT
SECURITY DEFINER;
..but it seems that I can't assign the value
returned by query to l_validity directly (I could do it in Access by
recordset)...How to modify the following in order to work ?
l_validity := SELECT "VALIDITY"."VALIDITY" FROM (
SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM "VALIDITY"
GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
Thank you in advance.
Zlatko
Re: [GENERAL] help about the function
Great! It works.
Thanks.
- Original Message -
From:
[EMAIL PROTECTED]
To: [EMAIL PROTECTED] ; [EMAIL PROTECTED]
Cc: pgsql-general@postgresql.org
Sent: Thursday, June 23, 2005 10:18
AM
Subject: Re: [GENERAL] help about the
function
Hi,
Why
not :
SELECT INTO l_validity "VALIDITY"."VALIDITY" FROM (
SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM
"VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY")
sve;
Regards,
Patrick
---
Patrick Fiche email : [EMAIL PROTECTED]
tél :
01 69 29 36 18 ---
-Original Message-From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]On Behalf Of Zlatko
MatićSent: jeudi 23 juin 2005 09:59To:
[EMAIL PROTECTED]Cc:
pgsql-general@postgresql.orgSubject: [GENERAL] help about the
functionImportance: High
Hello!
I have implemented solution for enabling
regular user (from group "ADMINS") to create new users in predefined groups,
by your modified function:
CREATE OR REPLACE FUNCTION
"public"."alter_group" (name, boolean, name, varchar, timestamp) RETURNS
boolean AS$body$DECLARE l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2; l_username ALIAS FOR $3;
l_password ALIAS FOR $4; l_validity ALIAS FOR $5; CMD
VARCHAR; MIN_SUPER_USER INTEGER := 1;BEGINIF
(l_create_user NOTNULL) THEN IF (l_create_user)
THEN CMD := 'CREATE USER "' || l_username
|| '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '"
VALID UNTIL ''' || l_validity || ;
EXECUTE CMD; ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD; END IF; IF (SELECT
COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') <
MIN_SUPER_USER THEN RAISE EXCEPTION 'At
least % admin(s) in group ADMINS must be defined in order to create new user
accounts.', MIN_SUPER_USER; END IF; END
IF; RETURN TRUE;END;$body$LANGUAGE 'plpgsql' VOLATILE
CALLED ON NULL INPUT SECURITY DEFINER;
Validity is set in the table
public."VALIDITY" in the field "VALIDITY" timestamp. There is also a
view called "VALIDITY_VIEW" which reads the actual validity value from the
table. It returns only one row-one field:
CREATE OR REPLACE VIEW "VALIDITY_VIEW" AS SELECT
"VALIDITY"."VALIDITY" FROM ( SELECT min("VALIDITY"."RV_ID")
AS "RV_ID",
"VALIDITY"."VALIDITY"
FROM "VALIDITY"
GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY") sve;
ALTER TABLE "VALIDITY_VIEW" OWNER TO zmatic;
MS Access front-end calls this function through
VBA code. Access creates a recordset on the base of the VALIDITY_VIEW to
inspect the value of validity time, assigns it to a variable and
then passes as the parameter l_validity to the server function
"alter_group". It works nice, but I realized that it is not safe, because
someone from the group "ADMINS" could create its own query in
Access with different "validity" and execute it without restriction.
Therefore I think that "l_validity" timestamp ($5) should not be input
parameter for the function "alter_group", but rather declared variable that
reads the value of validity from the table directly.
I tried to modify the function into something
like this:
CREATE OR REPLACE FUNCTION
"public"."alter_group" (name, boolean, name, varchar) RETURNS boolean
AS$body$DECLARE l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2; l_username ALIAS FOR $3;
l_password ALIAS FOR $4; l_validity timestamp; CMD
VARCHAR; MIN_SUPER_USER INTEGER := 1;BEGIN
l_validity := SELECT "VALIDITY"."VALIDITY" FROM
( SELECT min("VALIDITY"."RV_ID") AS "RV_ID", "VALIDITY"."VALIDITY" FROM
"VALIDITY" GROUP BY "VALIDITY"."RV_ID", "VALIDITY"."VALIDITY")
sve;
IF (l_create_user NOTNULL)
THEN IF (l_create_user)
THEN CMD := 'CREATE USER "' || l_username
|| '" WITH PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '"
VALID UNTIL ''' || l_validity || ;
EXECUTE CMD; ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD; END IF; IF (SELECT
COUNT(*) FROM "USER_GROUP_VIEW" WHERE "GroupName" ='{ADMINS}') <
MIN_SUPER_USER THEN RAISE EXCEPTION 'At
least % admin(s) in group ADMINS must be defined in order to create new user
accounts.', MIN_SUPER_USER; END IF; END
IF; RETURN TRUE;END;$body$LANGUAGE 'plpgsql' VOLATILE
CALLED ON NULL INPUT SECURITY DEFINER;
..but it seems that I can't assign the value
returned by query to l_validi
[GENERAL] truncate all tables?
How could I truncate, delete all content of all tables in one step ?
[GENERAL] PostgreSQL, WIndows, regular backup
Hello. I would appreciate if someone helps me to resolve this problem about regular backups on Windows. I have a batch file (.bat) for backup, but don't know how to include timestamp in backup file name. In this way I allways have only one, the most recent, backup file. I want Windows to produce regular backups with timestamp in file's name...The script is the following:pg_dump -f D:\MYDB_BCP -Fc -x -h localhost -U postgres MYDB How to include timestamp ?
Re: [GENERAL] PostgreSQL, WIndows, regular backup
It works great!
Thanks!
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Thursday, June 30, 2005 7:45 PM
Subject: Re: [GENERAL] PostgreSQL, WIndows, regular backup
This works to put the date in the filename on Windows 2000:
In a .bat file:
@echo off
for /f "tokens=1-4 delims=/ " %%i in ("%date%") do (
set dow=%%i
set month=%%j
set day=%%k
set year=%%l
)
set datestr=%month%_%day%_%year%
echo datestr is %datestr%
set BACKUP_FILE=MYDB_%datestr%
echo backup file name is %BACKUP_FILE%
This creates a filename MYDB_mm_dd_yy. When you run the little test .bat
script above, you will get:
datestr is 06_30_2005
backup file name is MYDB_06_30_2005
Hope this helps.
Susan
Zlatko Matić
<[EMAIL PROTECTED]To:
.hr> cc:
Sent by: Subject: [GENERAL]
PostgreSQL, WIndows, regular backup
|---|
[EMAIL PROTECTED] | [ ] Expand Groups |
tgresql.org |---|
06/30/2005 09:52
AM
Hello.
I would appreciate if someone helps me to resolve this problem about
regular backups on Windows. I have a batch file (.bat) for backup, but
don't know how to include timestamp in backup file name. In this way I
allways have only one, the most recent, backup file. I want Windows to
produce regular backups with timestamp in file's name...The script is the
following:
pg_dump -f D:\MYDB_BCP -Fc -x -h localhost -U postgres MYDB
How to include timestamp ?
--
See our award-winning line of tape and disk-based
backup & recovery solutions at http://www.overlandstorage.com
--
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 8: explain analyze is your friend
[GENERAL] postgres temporary tables and MS Access
I tried to work with postgres temporary tables from MS Access, but unsuccessfully... I was able to create temporary table by pass-through query, also I succeeded in creating linked table through code, but when I try to open it, an error apears and Access says there is no schema... Also, I couldn't link table using ODBC wizard... Can someone explain why I can't use temporary tables from MS Access ? Does it mean that every query execution is separate connection, so previously created temporary table no longer works for next queries ? Has anybody used postgres temporary tables from MS Access ? Thanks in advance, Zlatko
[GENERAL] PostgreSQL, Lazarus and zeos ?
Hi. Someone mentioned Lazarus as good IDE for working with PostgreSQL, so that's the reason I started to learn Lazarus... Now, I was told that I need to install ZEOS library in order to work with PostgreSQL. I downloaded the following .zip files: zeosdbo-5.0.7-beta, zeosctrl-1.0.0-beta. I have installed Lazarus 0.9.8 (binaries) on WIndows XP. It seems that these zeos files are intended to be for Delphi, not for Lazarus ? What am I supposed to do now ? How to install it ? Is it really neccessary to instal Zeos in order to work with PostgreSQL? Sorry for stupid questions, but this is totaly new stuff for me... Thanks in advance, Zlatko
Re: [GENERAL] PostgreSQL, Lazarus and zeos ?
thanks. - Original Message - From: "Ben Trewern" <[EMAIL PROTECTED]> To: Sent: Tuesday, July 26, 2005 7:33 PM Subject: Re: [GENERAL] PostgreSQL, Lazarus and zeos ? You need the cvs version of zeoslib to work with Lazarus. It's also the 6.5.something version. The old 5.x only worked with Delphi. See the Lazarus forums for more information. Ben ""Zlatko Matić"" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] Hi. Someone mentioned Lazarus as good IDE for working with PostgreSQL, so that's the reason I started to learn Lazarus... Now, I was told that I need to install ZEOS library in order to work with PostgreSQL. I downloaded the following .zip files: zeosdbo-5.0.7-beta, zeosctrl-1.0.0-beta. I have installed Lazarus 0.9.8 (binaries) on WIndows XP. It seems that these zeos files are intended to be for Delphi, not for Lazarus ? What am I supposed to do now ? How to install it ? Is it really neccessary to instal Zeos in order to work with PostgreSQL? Sorry for stupid questions, but this is totaly new stuff for me... Thanks in advance, Zlatko ---(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 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] A6 parameter in ODBC connection string
Hello. Somone helped me to set proper A6 parameter in ODBC connection string, for WIN1250 encoding, as following CLIENT%5fENCODING%3dWIN1250. If A6 is blank string, I suppose that ODBC driver uses default UNICODE ? Could someone tell me how to set other encodings in A6 parameter ? Is there any rule, or at least some list? Thanks in advance, Zlatko
[GENERAL] vacuum freeze
I performed vacuum freeze instead vacuum full, by mistake, using PgAdmin. Is there any danger for my database ? Zlatko
[GENERAL] templates, encoding
My template0 and template1 have SQL ASCII encoding, while I created a database with UNICODE encoding. Could it cause some conflicts ? If so, how can I change templates databases to be UNICODE as well ?
Re: [GENERAL] templates, encoding
Why there is no Unicode as option during initdb. There are some encodings, but not all alisted in PostgreSQL manual ? How different SQL ASCII is from Unicode ? - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Friday, July 29, 2005 4:21 PM Subject: Re: [GENERAL] templates, encoding =?iso-8859-2?Q?Zlatko_Mati=E6?= <[EMAIL PROTECTED]> writes: My template0 and template1 have SQL ASCII encoding, while I created a = database with UNICODE encoding. Could it cause some conflicts ? If so, how can I change templates = databases to be UNICODE as well ? The real question is whether the database's locale setting is compatible with Unicode (utf-8). If not, you're going to see some problems with comparing and sorting non-ASCII characters. initdb is the only way to fix a bad choice of locale, unfortunately. regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] Instalation batch file
Hello. I would like to install database schema on a server using files with dumped schema and globals (produced by dumpall), that are placed on CD. The installation script (batch file) that restores these two files is also placed on the same CD, (let's say E:) in the same folder. cd C:\Program Files\PostgreSQL\8.0\binpsql -f E:\MYBASE_SHEMA.dumpall template1 -U zmaticpsql -f E:\MYBASE_SHEMA.dumpall template1 -U zmaticvacuumdb -d MYBASE -U zmatic But, what if CD is F: or some other unit ? How could I change this batch file (Windows XP) to be able to recognize what is the actual path of the folder that containes these three files ? Thanks in advance, Zlatko
Re: [GENERAL] Instalation batch file
Thank you for answer. It seems that %HOMEDRIVE% is just enough to solve this problem. Thanks. - Original Message - From: "Glenn Davy" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Thursday, August 04, 2005 12:15 AM Subject: Re: [GENERAL] Instalation batch file On Wed, 2005-08-03 at 23:29 +0200, Zlatko Matić wrote: Hello. I would like to install database schema on a server using files with dumped schema and globals (produced by dumpall), that are placed on CD. The installation script (batch file) that restores these two files is also placed on the same CD, (let's say E:) in the same folder. cd C:\Program Files\PostgreSQL\8.0\bin psql -f E:\MYBASE_SHEMA.dumpall template1 -U zmatic psql -f E:\MYBASE_SHEMA.dumpall template1 -U zmatic vacuumdb -d MYBASE -U zmatic But, what if CD is F: or some other unit ? How could I change this batch file (Windows XP) to be able to recognize what is the actual path of the folder that containes these three files ? Im not sure how to determine the path to the media (esp if more than one cd/dvd), but you could take a couple of different approaches: 1)that is to run the script from the cd drive and build the path to pg's bin with %HOMEDRIVE%. Type 'SET' to see what other shell variables there are 2) Run the script from anywhere and take %1 type command line paramaters to ask location of either bin and/or cd/dvd Been years since I've had the misfortune to have to use windows shell scipts, but there are prob many other approachs - best to do a little research into windows shell scripting and determine best for your scenario. Glenn Thanks in advance, Zlatko ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org ---(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] tables in public
Hello. Postgres automatically included some tables in my public domain: pg_ts_cfg, pg_ts_cfgmap,pg_ts_dict, pg_ts_parser, spatial_ref_sys. I suppose it's because I switched on all additional options during initdb... My question regarding those tables is wheter I can delete them safely from public ? Zlatko
Re: [GENERAL] tables in public
Could you, please, give me short description of functionalities of those modules? I don't know whether I need it, because I don't know what is the purpose of those tables... - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Saturday, August 06, 2005 9:28 PM Subject: RE: [GENERAL] tables in public Hello. Postgres automatically included some tables in my public domain: pg_ts_cfg, pg_ts_cfgmap,pg_ts_dict, pg_ts_parser, I beleive these are from tsearch2. spatial_ref_sys. And this is postgis. I suppose it's because I switched on all additional options during initdb... Yes (this is win32 MSI installer, right?) My question regarding those tables is wheter I can delete them safely from public ? Yes, unless you plan to use any of the functionality of those modules. //Magnus ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tables in public
OK.thanks...I suppose I don't need it. - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Saturday, August 06, 2005 9:55 PM Subject: RE: [GENERAL] tables in public Could you, please, give me short description of functionalities of those modules? tsearch2 is full text indexing. postgis is geographical functionality. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Remopte connection to POstgreSQL via ODBC
Hello. I just read all about TCP/IP, pg_hba.conf, listen_adresses and similar topics regarding remote TCP/IP connections, but where can I find explanations about how to remotely connect using ODBC and DSN-less connection string. For example, how to connect to PostgreSQL server from remote computer using MS Access and DSN-less connection string ? Since now, I successfully conected from MS Access using DSN-less connection string but from the same machine. What if server is on a remote computer ? Is there any guide? Thanks in advance, Zlatko
[GENERAL] remote connection, web hosting, IP adress
Hello. I will install a database on a remote server. There is pg_hba.config file that should be adjusted to protect from unauthorized access. It requires IP adresses. Now, I would like to be able to connect to the server from my personal computer, in order to do some maintenance job on the database remotely. I don't have my personal IP adress till now. So, I'm wondering whether it would be possible to connect through some web hosting that offers fixed IP adress to its customers? I would adjust pg_hba to allow access from that IP adress... How can I connect to remote server from a remote personal computer without its own IP adress ? Thanks in advance, Zlatko
[GENERAL] pg_restore - authentication failed?
Hello. I have a strange problem when trying to use pg_restore to restore data from a backup, on WIN XP, PostgreSQL 8.0.3. If I use PgAdmin it works, but if I copy the command to .bat file authentication fails although I enter correct password. The .bat script is the following: cd D:\Program Files\PostgreSQL\8.0\bin pg_restore.exe -i -h localhost -p 5432 -U postgres -d "MYDATABASE" -a --disable-triggers -t mytablename -v "C:\BACKUP\MYDATABASE_DATA_BCP_Fc.fcbackup" and the error is: pg_restore: [archiver (db)] connection to database "MYDATABASE" failed: FATAL: password authentication failed for user "postgres" I also tried to pass password by pgpass.conf file, but without result. If I use PgAdmin, it works What could be the reason ? Is this a bug ? Zlatko ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] installing several PostgreSQL instances on Windows
Hello. Is it possible to install several completely independent instances of PostgreSQL on the same Windows machine? I'm asking this mostly because I want to have complete control over my database, which is not possible if there is some other superuser. Therefore I need dedicated Postgres server with only one database. But it is possible that a client already have a Postgres installed with some other database. Is it possible to install a new independent PostgreSQL server on the same computer? Thanks in advance, Zlatko
Re: [GENERAL] installing several PostgreSQL instances on Windows
thanks. - Original Message - From: "Magnus Hagander" <[EMAIL PROTECTED]> To: "Zlatko Matic" <[EMAIL PROTECTED]>; Sent: Friday, September 30, 2005 12:58 PM Subject: RE: [GENERAL] installing several PostgreSQL instances on Windows Hello. Is it possible to install several completely independent instances of PostgreSQL on the same Windows machine? I'm asking this mostly because I want to have complete control over my database, which is not possible if there is some other superuser. Therefore I need dedicated Postgres server with only one database. But it is possible that a client already have a Postgres installed with some other database. Is it possible to install a new independent PostgreSQL server on the same computer? Thanks in advance, Yes, but the installer will only let you put one instance on the machine. But you can just copy the whole directory of files to a different directory, and run "pg_ctl register" with a different name there to get a separate service. (That is, unless you use different versions. You can put both 8.0 and 8.1 on the same machine with the installer.) (Note! This is much safer in 8.1 than in 8.0, because 8.0 has some shared files in SYSTEM32 that might cause issues if you run multiple different versions on the same machine) //Magnus ---(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] varchar to text
Hello. There are some columns in my tables that I should change from varchar to text, in order to have enough space for long textual commentaries. Before I do that, I would like to know is there any restriction regarding text type in comparison to varchar type? Especially concerning triggers and rules ? Namely, I have triggers that perform audit trail of all my tables, so I'm concerned whether it will work for text type fields ? One additional question is regarding my MS Access front-end. Would it be a problem for Access ? Thanks in advance, Zlatko
[GENERAL] Unbound text box, Text > 255 characters, MSAccess/PostgreSQL
Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a DAO Append Query that has a parameter that is passed from the text box using parameter of DAO QueryDef object. I adjusted B7 parameter (Text as LongVarchar) in connection string (ODBC driver) to 1, so that Access links PostgreSQL Text fields as Memo fields in Access. The intention was to be able to enter unlimited length commentary into the field. But, although I can enter unlimited text by using bound text box in some other forms, in this particular form I can't use bound control, so I need to pass text from UNBOUND control to the linked table by using DAO or ADO code. It seems that DAO query can't accept Memo as parameter, but only text. If my text exceeds length of 255, I have an VBA error 3271. If text in unbound text box is shorter that 255 everything is OK. So, is there any way to pass text of length >255 from unbound text box to Memo field of linked PostgreSQL table, by using DAO or ADO ? Thanks in advance, Zlatko
Re: [GENERAL] [INTERFACES] [ODBC] Unbound text box, Text > 255 characters, MSAccess/PostgreSQL
Hello, Greg and thanks for suggestions, but it didn't work with append query. I just couldn't pass more than 255 characters long text as parameter of DAO query. But, fortunately, I solved the problem by using AddNew method of DAO recordset to append new row. In this case I could pass directly the whole value of Me.TextBoxName. It seems that in this way Access can pass the whole text (as Memo Type) to new row of recordset. Otherwise if I want to pass the same value of the unbound text box by using an append query, it can pass just 255 characters long text. Fortunately, it works with recordset object... Thanks, Zlatko - Original Message - From: "Greg Campbell" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>; Sent: Tuesday, October 04, 2005 3:34 PM Subject: Re: [INTERFACES] [ODBC] Unbound text box, Text > 255 characters, MSAccess/PostgreSQL What version of Access? Confirm that Access is interpreting the target field as MEMO, (either look at the linked table in design mode, or use Tools->Analyze->Documenter). For the query, determine the "type" of the parameter - Query menu->Parameters. Be sure you are using type MEMO. By the way, 3271 is a Jet error -- Invalid property value. Good luck. Zlatko Matić wrote: Hello. I have the following problem with MS Access/PostgreSQL combination: There is a form in Access that has an unbound text box, used for entering a commentary of a batch of records. There is a DAO Append Query that has a parameter that is passed from the text box using parameter of DAO QueryDef object. I adjusted B7 parameter (Text as LongVarchar) in connection string (ODBC driver) to 1, so that Access links PostgreSQL Text fields as Memo fields in Access. The intention was to be able to enter unlimited length commentary into the field. But, although I can enter unlimited text by using bound text box in some other forms, in this particular form I can't use bound control, so I need to pass text from UNBOUND control to the linked table by using DAO or ADO code. It seems that DAO query can't accept Memo as parameter, but only text. If my text exceeds length of 255, I have an VBA error 3271. If text in unbound text box is shorter that 255 everything is OK. So, is there any way to pass text of length >255 from unbound text box to Memo field of linked PostgreSQL table, by using DAO or ADO ? Thanks in advance, Zlatko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] pg_autovacuum
How to use pg_autovacuum ?
[GENERAL] problems with upgrade from 8.0.3 to 8.0.4, Windows
Hello. I downloaded 8.0.4 Windows installer and wanted to update my previos 8.0.3 installation, by using upgrade.bat. Unfortunately I was not able to do it. Just before the end of installation an error apears "Service 'PostgreSQL Database Server 8.0' (pgsql-8.0) could not be installed. Verify that you have sufficient privileges to install system services." I am logged to Windows XP as administrator... What could be the reason and solution ? Thanks, Zlatko
Re: [GENERAL] pg_autovacuum, vacuumdb on Windows XP
Hello. I have found README file, and tried to install pg_autovacuum as Windows service, by a batch file: @echo off set ODREDISTE=C: echo target disc is %ODREDISTE% cd %ODREDISTE%\Program Files\PostgreSQL\8.0\bin pg_autovacuum.exe pgsql-8.0.4 -I -N postgres_service -W postgres_service "postgres_service" was both "service" username and password during installation of postgreSQL... But, an error apeared regarding username and password. Then I tried with Windows administrator username and password, also unsuccessfully. Finally I removed -N and -W parameters and then there was a message on console that it is successfully installed as service. The final batch file was: @echo off set ODREDISTE=C: echo target disc is %ODREDISTE% cd %ODREDISTE%\Program Files\PostgreSQL\8.0\bin pg_autovacuum.exe pgsql-8.0.4 -I My questions are the following: 1. Is pg_autovacuum now really active and how can I be sure ? 2. Is it allowed to use pg_autovacuum -I without specifying parameters -N and -W ? 3. Why I was unable to pass username and password to parameters -N and -W? 4. Should -N be Windows administrator or postgres service username ? 5. If -N is postrges service name and -W password of that service, and both are entered as plain text in the batch file, how can it reflect to security of database? In fact, what can do service username postgres (or postgres_service in my case) ? I'm sorry for bothering you, Zlatko - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Sunday, October 09, 2005 5:37 PM Subject: Re: [GENERAL] pg_autovacuum =?iso-8859-2?Q?Zlatko_Mati=E6?= <[EMAIL PROTECTED]> writes: How to use pg_autovacuum ? Read the README file for it. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] user privilages for executing pg_autovacuum?
For pg_dump minimum privilages is to have select right on tables. For vacuumdb, one must be owner of tables or a superuser. What are minimum rights for user that is trying to execute pg_autovacuum ? How can I monitor vacuuming by pg_autovacuum ? Thanks, Zlatko
Re: [GENERAL] user privilages for executing pg_autovacuum?
That's the reason why I ask. If a user that executes pg_autovacuum must be owner of tables or a superuser, that it is a security problem to pass password as plain text... How peple solve this problem ? Thanks, Zlatko - Original Message - From: "Matthew T. O'Connor" To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Tuesday, October 11, 2005 4:00 PM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? Zlatko Matić wrote: For pg_dump minimum privilages is to have select right on tables. For vacuumdb, one must be owner of tables or a superuser. What are minimum rights for user that is trying to execute pg_autovacuum ? Not sure exactly, you need to have permission to vacuum every table in the database including system tables. How can I monitor vacuuming by pg_autovacuum ? It logs all its activity if you set the debug option to an appropriate level. Try -d1 or -d2. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster ---(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] user privilages for executing pg_autovacuum?
OK. but, is it required that the user is a superuser, owner of tables or just needs to have select rights on tables? Thanks, Zlatko - Original Message - From: "Tom Lane" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: "Matthew T. O'Connor" ; Sent: Tuesday, October 11, 2005 8:39 PM Subject: Re: [GENERAL] user privilages for executing pg_autovacuum? =?iso-8859-2?Q?Zlatko_Mati=E6?= <[EMAIL PROTECTED]> writes: That's the reason why I ask. If a user that executes pg_autovacuum must be owner of tables or a superuser, that it is a security problem to pass password as plain text... How peple solve this problem ? Put the password in a ~/.pgpass file belonging to the user that runs the autovacuum task. regards, tom lane ---(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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] How to secure pgpass file from unauthorized reading of passwords ? (WIndows)
Hello. It seems that for all automated tasks (backup, autovacuum etc.) passwords should be provided by pgpass file. But, anyone can read pgpass file How can I make it secure that nobody can read it, except programs for backup and autovacuum ? Thanks, Zlatko
[GENERAL] versions of oDBC driver
Hello. Could someone say which versions of ODBC drivers are recommended for PostgreSQL/MS Access 2003 combination, for: a) Postgres 8.0.4 b) Postgres 8.1 beta Namely, I was not able to connect from my Access front-end when I migrated from Postgres 8.0.4 to Postgres 8.1 beta3 Are there any significant changes that could cause such problems in connection strings ? Zlatko
[GENERAL] sequences, moving from 8.0.4 to 8.1 ?
Hello, I was reading release notes for 8.1 and there is something about changes concerning replacing ::text with ::regclass in default clauses. There is a query in Release notes, that should be executed to update dump from previos versions. But, even without it, I see that all serial fileds in my database were updated authomatically during restore from dump ?! I restored only globals and schema from dumpall backup... Could someone explain? Thanks, Zlatko
[GENERAL] pg_autovacuum (8.0.4) as Windows service ?
When installing pg_autovacuum as Windows service, should user that makes connection be the service account or a superuser ? For example, I have service account "postgres_service" and database superuser "postgres". Which one should be used in following script: @echo off set TARGET_DISC=C:echo target disc is %TARGET_DISC% cd %TARGET_DISC%\Program Files\PostgreSQL\8.0\binpg_autovacuum.exe pgsql-8.0.4 -I -U user -P password pause Thanks, Zlatko
Re: [GENERAL] [ODBC] versions of oDBC driver
Hello.
After I tried different things, I finally figured out where is the problem
with connection string: "Driver={PostgreSQL}" must be changed to
"Driver={PostgreSQL Unicode}". Now it works.
But this new connection string works only with Postgres 8.1, while it
doesn't work with Postgres 8.0...
I must say that current documentation lacks with information about
connection string parameters. Everything I could find about it was quite old
and not sufficient. It would be really nice if someone competent would write
some document regarding ODBC connection string parameters to explain their
meaning for "dummies" like me. I suppose I'm not the only one trying to use
MS Access with PostgreSQL. I think it is quite good combination for
hobbiests and people who are not programmers but want to create some
specific solutions for their job. MS Access is very easy to use and learn
and is widespread also, so supporting people in such efforts would
definitely increase popularity of PostgreSQL. Understanding ODBC connection
string parameters is essential for that.
Regarding connection parameters, for example, I would kindly ask you to tell
me which parameters are not neccessary in my connection string, so that I
can remove it, in order to allow more space in connection string. You have
already mentioned: "Secondarily, I find that not every parameter is
essential for the connection string. You might trying dropping your
strConnParams, and if that helps, debugging them one at a time, or by
halves.", so I would like to optimize my connection string to allow more
space for really important parameters in limited connection string in
Access...
This is my connection string:
strConnInfo = "ODBC;Driver={PostgreSQL Unicode};Server=" & SERVER & ";Port="
& PORT & ";Database=" & DATABASE & ";"
strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";"
strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" &
SOCKET & ";A9=1;" & _
"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _
"C0=0;C1=0;C2=dd_;"
strConnection = strConnInfo & strConnUserPass & strConnParms
What can I remove from it?
Thanks,
Zlatko
- Original Message -
From: "Greg Campbell" <[EMAIL PROTECTED]>
To: "Zlatko Matić" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]>
Sent: Friday, October 21, 2005 5:12 PM
Subject: Re: [ODBC] versions of oDBC driver
Did you changed the Postgresql server and the client ODBC driver at the same
time?
My guess is that the pg_hba is not configured to let you in,...
I would make sure the PostgreSQL server is set to having connection logging
enabled, and see what type of
errors it is throwing on the server.
Secondarily, I find that not every parameter is essential for the connection
string. You might trying
dropping your strConnParams, and if that helps, debugging them one at a
time, or by halves.
Zlatko Matić wrote:
Hello.
The error is error 3151: "ODBC--connection to '{PostgreSQL}Localhost'
failed".
I have a form with text boxes for entering Database name, IP adress,
username, password etc. When a user push the confirmation button, a
function "ConnectionToServer" is executed to: a) create connection string,
b) to check whether connection string works, c) to call functions for
relinking linked tables and adjusting connection string in pass-through
queries. The code is following:
Option Compare Database
Public strConnection As String
Function ConnectionToServer(SERVER As String, PORT As String, SOCKET As
String, DATABASE As String, USERNAME As String, PASSWORD As String,
ENCODING As String) As Boolean
Dim db As Object
Dim qdf As Object
Dim qdfSQL As String
Dim rs As Object
Dim strConnInfo As String
Dim strConnUserPass As String
Dim strConnParms As String
Dim CurrentUser As String
Dim A6 As String
On Error GoTo ErrorHandler
DoCmd.Hourglass True
Set db = CurrentDb
' PG_ODBC_PARAMETER ACCESS_PARAMETER
' *
' READONLYA0
' PROTOCOLA1
' FAKEOIDINDEXA2 'A2 must be 0 unless A3=1
' SHOWOIDCOLUMN A3
' ROWVERSIONING A4
' SHOWSYSTEMTABLESA5
' CONNSETTINGSA6
' FETCH A7
' SOCKET A8
' UNKNOWNSIZESA9 ' range [0-2]
' MAXVARCHARSIZE B0
' MAXLONGVARCHARSIZE B1
' DEBUG B2
' COMMLOG B3
' OPTIMIZER
[GENERAL] querying PostgreSQL version?
Hello. Is there any way to check the version of PostgreSQL by a query? Maybe by querying catalog tables? Thanks, Zlatko
[GENERAL] creating users in groups, in 8.1
Hello.
I had the following function in Postgres 8.0.4 for creation of users inside
existing groups. Now I need to adjust it for new Roles system. What do I
neeed to change?
Especially regarding: CMD := 'CREATE USER "' || l_username || '" WITH
ENCRYPTED PASSWORD ''' || l_password || ''' IN GROUP "' || l_group || '"
VALID UNTIL ''' || l_validity || ;
Thanks in advance,
Zlatko
-- Function: alter_group(name, bool, name, varchar)
-- DROP FUNCTION alter_group(name, bool, name, "varchar");
CREATE OR REPLACE FUNCTION alter_group(name, bool, name, "varchar")
RETURNS bool AS
$BODY$
DECLARE
l_group ALIAS FOR $1;
l_create_user ALIAS FOR $2;
l_username ALIAS FOR $3;
l_password ALIAS FOR $4;
l_validity timestamp;
CMD VARCHAR;
MIN_SUPER_USER INTEGER := 1;
BEGIN
select into l_validity "rok_valjanosti"."rok_valjanosti" FROM ( SELECT
min("rok_valjanosti"."rv_id") AS "rv_id", "rok_valjanosti"."rok_valjanosti"
FROM "rok_valjanosti" GROUP BY "rok_valjanosti"."rv_id",
"rok_valjanosti"."rok_valjanosti") sve;
IF (l_create_user NOTNULL) THEN
IF (l_create_user) THEN
CMD := 'CREATE USER "' || l_username || '" WITH ENCRYPTED PASSWORD '''
|| l_password || ''' IN GROUP "' || l_group || '" VALID UNTIL ''' ||
l_validity || ;
EXECUTE CMD;
ELSE
CMD := 'DROP USER "' || l_username || '"';
EXECUTE CMD;
END IF;
IF (SELECT COUNT(*) FROM "user_group_view" WHERE "groupname"
='{ADMINS}') < MIN_SUPER_USER THEN
RAISE EXCEPTION 'At least % admin(s) in group ADMINS must be defined
in order to create new user accounts.', MIN_SUPER_USER;
END IF;
END IF;
RETURN TRUE;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE SECURITY DEFINER;
ALTER FUNCTION alter_group(name, bool, name, "varchar") OWNER TO matalab;
GRANT EXECUTE ON FUNCTION alter_group(name, bool, name, "varchar") TO
matalab;
GRANT EXECUTE ON FUNCTION alter_group(name, bool, name, "varchar") TO GROUP
"ADMINS";
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] missing FROM clause ?
Hello.
In Postgres 8.1 I have a message that there is a
missing FROM clause in the following query:
select into
l_validity "rok_valjanosti"."rok_valjanosti" FROM ( SELECT
min("rok_valjanosti"."rv_id") AS "rv_id", "rok_valjanosti"."rok_valjanosti" FROM
"rok_valjanosti" GROUP BY "rok_valjanosti"."rv_id",
"rok_valjanosti"."rok_valjanosti") sve;
If I change parameter "add missing from" in
postgresl.conf to "on" than it works...
I would like to include FROM clause, but can't see
where.
Thanks,
Zlatko
[GENERAL] autovacuum,8.1, Win
What needs to be configured in order autovacuum process be active?
[GENERAL] psycopg2 problem with libpq.dll , Postgres 8.1.0 Win XP
Hello. I was using psycopg2/Python with Postgres 8.0.4, on Win XP. Now, when I installed Postgres 8.1.0 and was trying to connect with Python/psycopg2, an error occured: "This application has failed to start because lipq.dl was not found. Re-installing the application may fix the problem." What to do? Thanks, Zlatko
[GENERAL] ODBC connection string, MS Access
Hello. Could you, please, help me to optimize my connection string (MS Access 2003, PostgreSQL 8.1.1. and psqlodbc-08_01_0102)? ' PG_ODBC_PARAMETER ACCESS_PARAMETER' *' READONLY A0' PROTOCOL A1' FAKEOIDINDEX A2 'A2 must be 0 unless A3=1' SHOWOIDCOLUMN A3' ROWVERSIONING A4' SHOWSYSTEMTABLES A5' CONNSETTINGS A6' FETCH A7' SOCKET A8' UNKNOWNSIZES A9 ' range [0-2]' MAXVARCHARSIZE B0' MAXLONGVARCHARSIZE B1' DEBUG B2' COMMLOG B3' OPTIMIZER B4 ' note that 1 = _cancel_ generic optimizer...' KSQO B5' USEDECLAREFETCH B6' TEXTASLONGVARCHAR B7' UNKNOWNSASLONGVARCHAR B8' BOOLSASCHAR B9' PARSE C0' CANCELASFREESTMT C1' EXTRASYSTABLEPREFIXES C2 'Connection stringCONNECTIONSTRING: strConnInfo = "ODBC;Driver=" & Driver & ";Server=" & SERVER & ";Port=" & PORT & ";Database=" & DATABASE & ";" strConnUserPass = "Uid=" & USERNAME & ";Pwd=" & PASSWORD & ";" strConnParms = "A0=0;A1=6.4;A2=0;A3=0;A4=1;A5=0;A6=" & A6 & ";A7=100;A8=" & SOCKET & ";A9=1;" & _"B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=0;" & _"C0=0;C1=0;C2=dd_;" There are some options I don't understand, for example "Parse statements" and "Fetch/Declare" and don't know how would it affect performances... Also, there is a problem with the limited length of connection string that Access can handle, so I have a problem that I can't input some bigger usernames and passwords. Therefore I would like to remove some unneccessary parameters, if possible. Which parameters could be safely removed? Thanks in advance, Zlatko
[GENERAL] POstgreSQL 8.1.X/Lazarus?
Hello. Is there anybody using lazarus with new POstgreSQL 8.1.X ? I couldn't connect using Zeos, because it seems zeos work only with old versions of PostgreSQL. Also, I couldn't connect by using TPSQL, because it can't find libpg.dll installed (?)... Regards, Zlatko
Re: [GENERAL] POstgreSQL 8.1.X/Lazarus?
OK. I get it. I copied libpq74.dll to system32 and now it works, bu only partially, just for tables, not for queries. There is some runtime error for queries, but I should ask about it in some Zeos and Lazarus newsgroups. Thanks. - Original Message - From: "Tony Caduto" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: Sent: Thursday, December 29, 2005 6:12 PM Subject: Re: [GENERAL] POstgreSQL 8.1.X/Lazarus? Are you running Lazarus on win32 or Linux?(I assume win32 because you mentioned a dll) I have successfully used Zeos with Delphi against a 8.1 server, I even used the libpq74.dll that ships with zeos. I guess you should make sure any versions of libpq you have are in your system32 dir. Windows first checks the system dir, then your app dir for dlls. (it used to be the other way around) Also check out the Zeos forums at: http://zeosforum.net.ms/ Later, Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com Is there anybody using lazarus with new POstgreSQL 8.1.X ? I couldn't connect using Zeos, because it seems zeos work only with old versions of PostgreSQL. Also, I couldn't connect by using TPSQL, because it can't find libpg.dll installed (?)... Regards, Zlatko ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] visual query builder for Postgres?
Is there any visual tool for creating queries in PostgreSQL? Zlatko
[GENERAL] temporary tables, pgAdminIII
In Postgres 8.1.1, Win XP, I tried to create some temporary tables by using pgAdmin. For example, I executed : CREATE TEMP TABLE privremena() INHERITS (plants)WITHOUT OIDSTABLESPACE pg_default;ALTER TABLE privremena OWNER TO matalab; Although query was successfully executed, I just can't find temporary table. I don't see it anywhere in the database, even when I refresh the database. Where is it?
Re: [GENERAL] visual query builder for Postgres?
Thanks - Original Message - From: "Tony Caduto" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]>; Sent: Monday, January 16, 2006 3:01 PM Subject: Re: [GENERAL] visual query builder for Postgres? Zlatko Matić wrote: Is there any visual tool for creating queries in PostgreSQL? Zlatko PG Lightning Admin has a visual query builder plus lots more(modern tabbed MDI interface,printing,import/export etc) and it's super inexpensive. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] visual query builder for Postgres?
thanks. - Original Message - From: Michael Schmidt To: pgsql-general@postgresql.org ; Zlatko Matić Sent: Sunday, January 15, 2006 11:40 PM Subject: Re: [GENERAL] visual query builder for Postgres? A Visual Query Builder is available in PG Lightning Admin. Michael Schmidt
[GENERAL] linking temporary tables from MS Access?
Is it possible to use temporary tables from MS Access, as linked tables? I was not able to link, but maybe someone succeeded?
[GENERAL] general questions about joins in queries
Hello. Is it better to use A) or B) ? A) SELECT "public"."departments".*, "public"."plants".*, "public"."batches_microbs".*, "public"."results_microbs".*FROM "public"."departments", "public"."plants", "public"."batches_microbs", "public"."results_microbs"WHERE "plants"."department" = "departments"."department" AND "batches_microbs"."plant" = "plants"."plant" AND "results_microbs"."batch" = "batches_microbs"."batch" B) SELECT "public"."departments".*, "public"."plants".*, "public"."batches_microbs".*, "public"."results_microbs".*FROMpublic.departments INNER JOIN public.plants ON (public.departments.department = public.plants.department) INNER JOIN public.batches_microbs ON (public.plants.plant = public.batches_microbs.plant) INNER JOIN public.results_microbs ON (public.batches_microbs.batch = public.results_microbs.batch) Thanks, Zlatko
[GENERAL] numeric data type?
Is "numeric" data type good choice for a field that would store integer values in most cases, but sometimes decimal values as well? Thanks, Zlatko
Re: [GENERAL] numeric data type?
Thanks for answer, but in documentation I found that Numeric can be without scale and precision defined: "Specifying NUMERICwithout any precision or scale creates a column in which numeric values of any precision and scale can be stored, up to the implementation limit on precision. A column of this kind will not coerce input values to any particular scale, whereas numeric columns with a declared scale will coerce input values to that scale. (The SQL standard requires a default scale of 0, i.e., coercion to integer precision. We find this a bit useless. If you're concerned about portability, always specify the precision and scale explicitly.) " Talking about float: "If you require exact storage and calculations (such as for monetary amounts), use the numeric type instead. " So, it seems that numeric without parameters (precision, scale) behave similar to float, but is much exact. Am I right or I missunderstood? Thanks, Zlatko - Original Message - From: "Tony Caduto" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Sent: Saturday, January 21, 2006 5:15 PM Subject: Re: [GENERAL] numeric data type? Zlatko Matić wrote: Is "numeric" data type good choice for a field that would store integer values in most cases, but sometimes decimal values as well? Thanks, Zlatko I think you would be better off with a float. numeric has to have a scale set to it, while float4 or float8 does not. -- Tony Caduto AM Software Design Home of PG Lightning Admin for Postgresql http://www.amsoftwaredesign.com ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] numeric data type?
OK. Thanks for clarification. - Original Message - From: "Doug McNaught" <[EMAIL PROTECTED]> To: "Zlatko Matić" <[EMAIL PROTECTED]> Cc: ; "Tony Caduto" <[EMAIL PROTECTED]> Sent: Sunday, January 22, 2006 2:39 PM Subject: Re: [GENERAL] numeric data type? Zlatko Matić <[EMAIL PROTECTED]> writes: So, it seems that numeric without parameters (precision, scale) behave similar to float, but is much exact. Am I right or I missunderstood? Right. It's also considerably slower, since floating point calculations can use the hardware. Unless you're doing a huge number of computations this may not be an issue. -Doug ---(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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] tetst
test
[GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
In Windows XP Pro (SP2) I tried to upgrade PostgreSQL server from 8.1.2 to 8.1.4., by using upgrade.bat. Everything goes nice until end of installation when the following error apears: "Service 'PostgreSQL Database Server 8.1' (pgsql-8.1) could not be installed. Verify that you have sufficient priviliges to install system services" . I am logged as administrator, Windows XP Pro SP2. What should I do? Zlatko
Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
Does anybody has a clue? Somebody experienced similar problem? Should I be logged to Windows as regular user (administrator) or as "postgres" service account? Thanks. - Original Message - From: Zlatko Matić To: pgsql-general@postgresql.org Sent: Tuesday, September 05, 2006 6:54 AM Subject: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4 In Windows XP Pro (SP2) I tried to upgrade PostgreSQL server from 8.1.2 to 8.1.4., by using upgrade.bat. Everything goes nice until end of installation when the following error apears: "Service 'PostgreSQL Database Server 8.1' (pgsql-8.1) could not be installed. Verify that you have sufficient priviliges to install system services" . I am logged as administrator, Windows XP Pro SP2. What should I do? Zlatko
Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
No, nothing changed.
I am the only user of this computer (personal computer). It is the same
account I was using for installing PostgreSQL server.
In fact I had the same problem with upgrading from PostgreSQL 8.0 to 8.1,
then from 8.1 to 8.2...In all theses cases I had to uninstall old version
first and then install new version of the server. I don't want to uninstall
the server this time
Zlatko
- Original Message -
From: "Scott Marlowe" <[EMAIL PROTECTED]>
To: "Zlatko Matić" <[EMAIL PROTECTED]>
Cc: "pgsql general"
Sent: Tuesday, September 05, 2006 7:33 PM
Subject: Re: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
On Tue, 2006-09-05 at 12:20, Zlatko Matić wrote:
Does anybody has a clue?
Somebody experienced similar problem?
Should I be logged to Windows as regular user (administrator) or as
"postgres" service account?
Thanks.
- Original Message -
From: Zlatko Matić
To: pgsql-general@postgresql.org
Sent: Tuesday, September 05, 2006 6:54 AM
Subject: [GENERAL] Upgrade Postgres 8.1.2 to 8.1.4
In Windows XP Pro (SP2) I tried to upgrade PostgreSQL server
from 8.1.2 to
8.1.4., by using upgrade.bat.
Everything goes nice until end of installation when the
following error
apears: "Service 'PostgreSQL Database Server 8.1' (pgsql-8.1)
could not be
installed. Verify that you have sufficient priviliges to
install system
services" .
I am logged as administrator, Windows XP Pro SP2.
What should I do?
Is PostgreSQL the ONLY thing that got changed? It sounds like you
simply don't have administrative privileges on this system.
You should be logged in as an administrator...
---(end of broadcast)---
TIP 6: explain analyze is your friend
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[GENERAL] program for graphical/schematical representation of relations between tables
Is there any free program that can graphically/schematically display relations between all tables in a database? Regards, Zlatko
