Re: [GENERAL] DB crash after disk full
Version 8.1.1 Got to make it work, after deletin some records from table Tom Lane wrote: Vilen Tambovtsev [EMAIL PROTECTED] writes: - 2005-12-28 19:02:49 NOVT - - 2747: PANIC: could not access status of transaction 74678708 What Postgres version is this? regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend -- Vilen Tambovtsev Plesk QA Engineer SWsoft, Inc. E-mail: [EMAIL PROTECTED] ICQ UIN: 4608679 ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] how to add a new column to system table
I wanted to add a new column to system table(pg_database),but failed at last. Who know how to do this and can you tell me ?
[GENERAL] a few questions
I have a few questions: 1. what is pg_xlog someone told me that i can move pg_xlog to a different parttion in order to boost the performance? Does it work and how 2. there is a parameter in postgresql.conf called max_connections. which is 100 be default. i want o decrease it to 20. by doing this how much can i increase the value of shared buffers? by default it is 1000, how much can i increase to in order to boost up the performance 3. What other things can i do to boost up the performance assuming that the stored procedures are well optimized. 4. I recently tried to start postmaster. But it simply timed out. i tried to find out if there is any postmaster process running, but it was not running. my question is that can u decrease this timeout, right now i think it takes some 1 or 2 minutes... 5. i have also seen multiple instances of postmaster. in my script ot start postmaster i first check if it is running by doing pidof, and only if it is nor running i start it still have seen multiple instances. how did that happen? also if i stop postmaster, only one instance is stopped. is there any command to stop all instances of postmaster 6. what does ipcclean do? how do i know what shared memory was used by postmaster so that i can clearit, before starting postmaster 7. some times if i do a dropdb abc(assuming abc is a database) it displays a message can not remove directory 12345, although the database is dropped, what shuld be done in such a case? thanks, regards Surabhi
Re: [GENERAL] a few questions
On Thu, Dec 29, 2005 at 03:09:52PM +0530, surabhi.ahuja wrote: I have a few questions: 1. what is pg_xlog someone told me that i can move pg_xlog to a different parttion in order to boost the performance? Does it work and how Yes, it works. How? By moving the directory (while the postmaster is not running) and creating a symlink in the right place. 2. there is a parameter in postgresql.conf called max_connections. which is 100 be default. i want o decrease it to 20. by doing this how much can i increase the value of shared buffers? by default it is 1000, how much can i increase to in order to boost up the performance They have nothing to do with eachother. Depending on how much memory you have, the shared_buffers could be increased by a factor of 10. Max connections won't change anything there. 3. What other things can i do to boost up the performance assuming that the stored procedures are well optimized. Google the web, or try the pgsql-performence mailing list. 4. I recently tried to start postmaster. But it simply timed out. i tried to find out if there is any postmaster process running, but it was not running. my question is that can u decrease this timeout, right now i think it takes some 1 or 2 minutes... Look in the logs for an error message. 5. i have also seen multiple instances of postmaster. in my script ot start postmaster i first check if it is running by doing pidof, and only if it is nor running i start it still have seen multiple instances. how did that happen? also if i stop postmaster, only one instance is stopped. Each connection appears as a new process, so pidof wont't work. You need to use the pidfile the postmaster creates. Why arn't you using one of the startup scripts provided? is there any command to stop all instances of postmaster Are you sure you have more than one? 6. what does ipcclean do? how do i know what shared memory was used by postmaster so that i can clear it, before starting postmaster PostgreSQL takes care of it's own ipc memory, you should never need to use ipcclean ever. 7. some times if i do a dropdb abc(assuming abc is a database) it displays a message can not remove directory 12345, although the database is dropped, what shuld be done in such a case? Please provide the exact error message. Oh, and while you're at it, what platform and what version of postgres. Without that info it's impossible to give any real help, Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgp50vFzMBFKp.pgp Description: PGP signature
Re: [GENERAL] a few questions
On Thu, Dec 29, 2005 at 03:40:11PM +0530, surabhi.ahuja wrote: pidof of doesnt work ? Given the number of processes is going to be at least 3+number of connections, how is pidof going to know which one you mean? Answer: it doesn't, so you end up killing a random one. which startup script are u reffering to? In recent releases they're under contrib/start-scripts but they've been there for a while. Since you didn't say which version, I can't help you more than that. Have a nice day, -- Martijn van Oosterhout kleptog@svana.org http://svana.org/kleptog/ Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a tool for doing 5% of the work and then sitting around waiting for someone else to do the other 95% so you can sue them. pgpjtmfMIfwbg.pgp Description: PGP signature
[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] Adding columns to a view
Ingo van Lil wrote: On 28 Dec 2005, Florian G. Pflug wrote: I could think of a few situations where extending a view might be useful, and I'd appreciate to see it supported. I don't see any reason not to allow it as long as no existing columns are removed or have their type changed. Well, some other view could do select * from firstview, or some client code could assume a certain number of rows, and missbehave if there are more rows... Other views wouldn't see the newly added column, a 'select * from' is automatically rewritten as 'select column1, column2, ... from' when creating views. As for misbehaving client code: That's the client's problem, not the database's. From a client's point of view there's no difference between adding a new field to a table (which is allowed) and adding a new field to a view (which isn't). Good point. If I need to change the order or number of columns in a view, I use pgadmin to find the dependent objects, copy their definitions into a sql-window (including the drop ... line), put my new definition and a drop cascade in front, and execute all that inside a transaction. But you're right, if more then 5 or so other objects depend on a view, this gets pretty annyoing.. Well, in my case the situation is further complicated by the fact that adding a column to the view should be done automatically from a trigger function. I wanted some kind of matrix view that had a column for every row in a certain table. And whenever a new line was inserted into that table the view should automatically be extended by one column. Well, fortunately adding a new entry to that table happens only very rarely, and I wouldn't mind extending the view manually. I'd just prefer to be able to do so without dropping and recreating everything that depends on it. Hm... if I remember correctly, your hack was to add the column manually by altering the system catalogs, and then modifying the on-select rule. If you created the view manually, meaning that instead of create view ... you do create table (fields) and then add in on-select rule, you could add a column to the view without messing around in the system catalogs. You'd just do alter table add column, and then update the on-select rule accordingly. greetings, Florian Pflug smime.p7s Description: S/MIME Cryptographic Signature
Re: [GENERAL] WAL logs multiplexing?
On Thu, 2005-12-29 at 10:47 +0300, Dmitry Panov wrote: On Wed, 2005-12-28 at 11:05 -0500, Tom Lane wrote: Dmitry Panov [EMAIL PROTECTED] writes: Yes, but if the server has crashed earlier the script won't be called and if the filesystem can't be recovered the changes will be lost. My point is the server should write into both (or more) files at the same time. As for that, I agree with the other person: a RAID array does that just fine, and with much higher performance than we could muster. BTW, I found something related in the TODO: http://momjian.postgresql.org/cgi-bin/pgtodo?pitr I think both approaches have the right to exist, but I prefer my because it looks more straightforward, it insures up-to-date recovery (no delays) and it reduces the traffic (as the partial logs have to be transferred in full by the proposed archive_current_wal_command). The only drawback is performance. Simply replicating pg_xlog might be worthwhile for the truly paranoid, since it does help in the situation that you lose the RAID unit with your pg_xlog on it. But this facility is already available via hardware replication facilities, so I see no reason to build it into the DBMS. Replicating pg_xlog to NFS would not work very well performance wise and has some major undefined behaviour in most failure modes, so I would never do that. However, there is a case to be made for continuous xlog record archival which could get closer to 0% data loss in the event of failure, though with higher performance hit than current PITR. I'll look into that some more - but no promises. Best Regards, Simon Riggs ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Is CREATE TYPE an alias for CREATE DOMAIN?
Hi Jim
I have checked through the docs for:-
1. Interbase/Firebird
2. Sybase/MS SQL Server
3. Oracle
4. DB2
5. PostgreSQL
BTW I didn't bother to check the MySQL docs because I do not consider MySQL
to be a RDBMS
It seems that only Interbase/Firebird and PostgreSQL supports the CREATE
DOMAIN syntax. DB2 includes something similar - CREATE DISTINCTIVE TYPE.
But it doesn't allow a constraint to be included
At 17:09 28/12/2005, you wrote:
On Thu, Dec 22, 2005 at 05:16:16PM +0100, Peter Eisentraut wrote:
Jim C. Nasby wrote:
Some (most?) database's idea of 'creating a type' is actually what we
consider creating a domain,
Which databases do such a thing?
IIRC, Oracle, DB2, Sybase and MSSQL, though my memory's rusty... and I
should have mentioned that most are just creating an alias for a type
name, so you can't add stuff like constraints to the new type.
Those RDBMS which do support the CREATE DOMAIN syntax allows the inclusion
of a named constraint and/or a CHECK constrain
Below is a copy of the first few lines from the PostgreSQL SQL Language
Reference
CREATE DOMAIN
Name
CREATE DOMAIN -- define a new domain
Synopsis
CREATE DOMAIN name [AS] data_type
[ DEFAULT expression ]
[ constraint [ ... ] ]
where constraint is:
[ CONSTRAINT constraint_name ]
{ NOT NULL | NULL | CHECK (expression) }
Description
CREATE DOMAIN creates a new data domain. The user who defines a domain
becomes its owner.
If a schema name is given (for example, CREATE DOMAIN myschema.mydomain
...) then the domain is created in the specified schema. Otherwise it is
created in the current schema. The domain name must be unique among the
types and domains existing in its schema.
Domains are useful for abstracting common fields between tables into a
single location for maintenance. For example, an email address column may
be used in several tables, all with the same properties. Define a domain
and use that rather than setting up each table's constraints individually.
Sybase/MS SQL Server makes use of the store procedure - sp_addtype, which
is similar to DB2's CREATE DISTINCTIVE TYPE
Oracle uses a variation on the CREATE TYPE syntax. But just like Sybase, MS
SQL Server and DB2 it does not accept a named constraint or CHECK clause
--
Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED]
Pervasive Software http://pervasive.comwork: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
---
Regards
John Dean,
co-author of Rekall,
the only alternative
to MS Access
---(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] I want to know how to improve the security of postgresql
Hi guys,can you give me some advices about how to improve the security of postgresql? Now I major in the security of postgresql and the destination is create a database with security level of B1(TCSEC),what should I do now,what program language should I use? Well, since PostgreSQL is implemented in C, the language you obviously need to use is... C. You may want to do more checking as to what you actually want to do. I don't think the NSA is continuing to do TPEP evaluations, which is where the C1-C3, B1-B3, A1 levels of the Rainbow books came in. It would be an unfortunate waste of effort to try to conform to a standard that is no longer considered of commercial importance. Remember that TCSEC was published in 1985, and there haven't been any new evaluations since 2000. Mind you, there may be some principles to be found in looking at the evaluation done of Sybase Adaptive Server 6.0.2 against the Common Criterion. -- (format nil [EMAIL PROTECTED] cbbrowne acm.org) http://linuxdatabases.info/info/wp.html Never insult seven men, when all you're packin' is a six gun --- Zane Gray ---(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] how to add a new column to system table
xiapw wrote: I wanted to add a new column to system table(pg_database),but failed at last. Who know how to do this and can you tell me ? It is pretty complicated. I would pick an existing column in the table and find all references to that in the backend, then adjust other values to match. Actually adding a column to a system table is quite complex and you might be better off creating a new table that refernces pg_database and linking to it. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
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
Re: [GENERAL] I want to know how to improve the security of postgresql
Sting, I'm not entirely sure what you mean by improving the security of postgresql but if you want to implement smart, efficient row-level security you could take a look at Veil http://pgfoundry.org/projects/veil/ Check the project home page for documentation. Veil is still an alpha release but I am prepared to work with any potential users to bring it up to a production release. __ Marc On Thu, 2005-12-29 at 06:48 -0400, [EMAIL PROTECTED] wrote: Date: Thu, 29 Dec 2005 10:09:01 +0800 From: xiapw [EMAIL PROTECTED] To: pgsql-general@postgresql.org Subject: I want to know how to improve the security of postgresql Message-ID: [EMAIL PROTECTED] Hi guys,can you give me some advices about how to improve the security of postgresql? Now I major in the security of postgresql and the destination is create a database with security level of B1(TCSEC),what should I do now,what program language should I use? Thanks! Sting [Attachment of type text/html removed.] signature.asc Description: This is a digitally signed message part
[GENERAL] Triggers and Audit Trail
Hi all. I'm new with PostgreSQL and this is my first post, so easy on me... :) I'm thinkingof using the native procedural language and triggers to keep an audit trail. Forediting changes, we only keepa log of the modified fields and we create a record for each modified value. The audit tablerecord holds informationlike user, date/time, table_name, field_name, old_value, new_value, type(delete, new, edit).I have a couple of questions: Using triggers, is there a way to loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field name and value that triggered the updateother than hard coding if statements to compare every field of the OLD and NEW records. Another issue is how to keep track of the audit user since we share the same postgres user and our application keeps track of the actual current user locally. Is there some kind of way we can set the current user so that we'reable to read it from the trigger event? Other suggestions? Thanks
Re: [GENERAL] new beginner to postgresql. Looking at it for a church
On Dec 27, 2005, at 7:31 PM, Pamela wrote: I was wondering if anyone has setup a point-of-sale system with postgresql. Also, I will have to create a database for a church that requires lots of tables and subcategories. They have 4 different locations and wish to be interconnected amongst each other, yet remain distinct and autonomous. They have lots different categories (women’s group, kid’s group, seminars, etc.) and funds within funds. Any suggestions/examples of how someone could go about the data-modeling for this. Also, was thinking of using .php and ruby to connect them via the net. I will also, require setting up a general ledger and a full accounting system for them. Inventory for books, cds. I am looking at multiple sources for them including MySQL, Microsoft.Net (really, really don’t want to go there) and any other suggestions individuals may have. You might want to consider Drupal/PostgreSQL for the web interface (Drupal is written in PHP). Each location could have their own site from a single database in different schemas. Common tables for use with all sites could be managed in another schema. You could create a Drupal module to provide an interface to the accounting functions. John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Triggers and Audit Trail
Using triggers, is there a way to loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field name and value that triggered the update other than hard coding if statements to compare every field of the OLD and NEW records. We (my company) never found a way. We ended up writing java code that analyzed the catalog tables that generated the appropriate 'if' statements in the trigger functions for us Another issue is how to keep track of the audit user since we share the same postgres user and our application keeps track of the actual current user locally. Is there some kind of way we can set the current user so that we're able to read it from the trigger event? Other suggestions? Inside our application, when we grab a connection from our connection pool, the user information is populated into a termporary table that the audit triggers can then later read for any transactions on that connection. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Triggers and Audit Trail
We (my company) never found a way. We ended up writing java code that analyzed the catalog tables that generated the appropriate 'if' statements in the trigger functions for us Actuallywe tinkered with hitting the catalog tables inside our triggers, but for performance reasons, we generated the 'if' statements instead ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Triggers and Audit Trail
On Thu, Dec 29, 2005 at 11:44:26AM -0600, Jeff Amiel wrote: Using triggers, is there a way to loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field name and value that triggered the update other than hard coding if statements to compare every field of the OLD and NEW records. We (my company) never found a way. We ended up writing java code that analyzed the catalog tables that generated the appropriate 'if' statements in the trigger functions for us As far as I know you can't do this yet in PL/pgSQL, but you can in other languages like PL/Perl and PL/Tcl. -- Michael Fuhr ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] how to add a new column to system table
xiapw [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] I wanted to add a new column to system table(pg_database),but failed at last. Who know how to do this and can you tell me ? A recent change to pg_database (adding a new column) happened on -- Sun Jul 31 17:19:21 2005 UTC (4 months, 4 weeks ago) by tgl Log Message: --- Add per-user and per-database connection limit options. This patch also includes preliminary update of pg_dumpall for roles. Petr Jelinek, with review by Bruce Momjian and Tom Lane. -- Try to find out related changes on pgsql.committers. Regards, Qingqing ---(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] windows xp install problem (failed to set
On 27 Dec 2005 at 17:04, George Pavlov wrote: Any ideas on what to look for. Any special permissions I need to set outside of the install or before the install? Just looking at my own XP installation, the Postgres data directory (C:\Program Files\PostgreSQL\8.0\data on my system) has modify, read execute, list folder contents, read and write permitted for the postgres user. --Ray. - Raymond O'Donnell http://www.galwaycathedral.org/recitals [EMAIL PROTECTED] Galway Cathedral Recitals - ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [Bulk] Re: [GENERAL] Final stored procedure question, for now anyway
Ted Byers wrote: 2) Do I need to qualify my references to my tables in the function to identify the schema in which the tables exist, or will Postgres find the right tables with the function in its present form? If I have to further qualify the table references, what is the correct qualification of the table references within the SQL in the function definition? PostgreSQL uses a search path to find DB objects. The default search path is a schema with the same name you used to connect, followed by the public schema. You can alter this search path. So you have several options: (1) hard-code a schema name onto each table in your queries (which I would never do and do not suggest), (2) put your DB objects (tables, functions, etc) into the schema that you use to connect, or (3) alter your search path. As much as I like PG and as powerful as it is, I'm surprised the designers have elected not to implement synonyms, which is how most other databases address this issue. -- Guy Rouillier ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] alter column datatype with cast
I had to change the datatype of a column from text to integer. The column contained integers (obviously stored as text). When I tried to change the datatype of the column I got an error message saying that the column can not be cast to integer: Operation : ALTER TABLE public.subjectgroupcondition ALTER COLUMN param1 TYPE INTEGER Result: ERROR: column param1 cannot be cast to type pg_catalog.int4 However when I created an other integer column in the table and updated it from the text column there was no problem casting the data: Operation : UPDATE public.subjectgroupcondition SET param2 = cast(param1 as integer); Result: OK. Since pg knows that it should cast the data and it can cast it I think I should have been able to change the datatype in the first instance. Maybe this behaviour has a good reason but I don't know what it is. Regards, SWK ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] Stored Procedure: PL/Perl or PL/SQL?
Greetings all, I'm working to integrate an accounting system (http://www.linuxcanada.com) with another application. Fortunately, both use PG, so integration should be easy. I want to be able to insert invoices, and invoice line items, into the accounting system. As you might expect, this requires many selects and inserts involving serveral tables wrapped around a transaction. As I see it, there are a couple of ways to do this: 1. Attach a trigger which runs a Stored Procedure in PL/SQL; 2. Create a Perl Module that connects to a database via DBI and does the work; the trigger would then be written in PL/Perl, and would use the Perl Module to do the work. The advantage to #2 is that I'd have invoice migration and a general-purpose module for inserting invoices, with the same amount of work that I'd have for just invoice migration using PL/SQL. The drawback is the overhead of using Perl inside PG; how much overhead is there? What else should I consider? Thanks, -Josh ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] alter column datatype with cast
On Thu, Dec 29, 2005 at 09:46:10PM +0100, Klein Balzs wrote: I had to change the datatype of a column from text to integer. The column contained integers (obviously stored as text). When I tried to change the datatype of the column I got an error message saying that the column can not be cast to integer: Operation : ALTER TABLE public.subjectgroupcondition ALTER COLUMN param1 TYPE INTEGER Result: ERROR: column param1 cannot be cast to type pg_catalog.int4 Use the USING clause: ALTER TABLE subjectgroupcondition ALTER COLUMN param1 TYPE integer USING param1::integer; However when I created an other integer column in the table and updated it from the text column there was no problem casting the data: Operation : UPDATE public.subjectgroupcondition SET param2 = cast(param1 as integer); Result: OK. Since pg knows that it should cast the data and it can cast it I think I should have been able to change the datatype in the first instance. Maybe this behaviour has a good reason but I don't know what it is. Some casts can be done implicitly and some not. For more information see the CREATE CAST and Type Conversion documentation: http://www.postgresql.org/docs/8.1/interactive/sql-createcast.html http://www.postgresql.org/docs/8.1/interactive/typeconv.html -- Michael Fuhr ---(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] Triggers and Audit Trail
Hi Marcus,
Marcus Couto wrote:
Hi all. I'm new with PostgreSQL and
this is my first post, so easy on me... :)
I'm thinkingof using the native
procedural language and triggers to keep an audit trail. Forediting
changes, we only keepa log of the modified fields and we create a
record for each modified value. The audit tablerecord holds informationlike user, date/time,
table_name, field_name, old_value, new_value, type(delete, new, edit).I have a couple of questions:
I wrote such an audit system and am using it production. It works
reasonably well. It was quite a bit of work to develop, and still has
some rough edges.
Using triggers, is there a way to
loop through the fields of the OLD and NEW records? I haven't found a generic way to get the field
name and value that triggered the updateother than hard coding if
statements to compare every field of the OLD and NEW records.
I had this problem, and as Michael Fuhr mentioned you can't resolve it
in PL/PGSQL. I ended up using PL/TCL because it was stable under 7.4
and it does the field dereferencing you need. As of 8.0 and later
PL/PERL is also stable and I believe it does field dereferencing as
well.
Another issue is how to keep track
of the audit user since we share the same postgres user and our
application keeps track of the actual current user locally. Is there
some kind of way we can set the current user so that we'reable to read
it from the trigger event? Other suggestions?
I looked into that as well, and it's pretty hard. Most applications
that use only one database user but have multiple application-level
users are three-tier, and the apps tend to do logging themselves, often
using a separate loggin mechanism like log4j and friends. So for that
part I'd either have your app write the user action into the
appropriate table, or look into retrieving the PK of your audit/history
table row, passing it back to your application and having your
application log the user after writing the row history table.
Otherwise you're at the mercy of when and how your database connection
is opened (i.e., how long a session lasts).
Some other tips:
I use a PL/TCL trigger function to enumerate the table and fields, and
then call two functions that actually write the log of the action and
the row history table.
some key lines from that TCL function:
switch $TG_op {
# do different things for different SQL commands
DELETE {}
INSERT {}
UPDATE {}
SELECT {}
default {}
# get the name of the table
spi_exec "select relname as trg_tablename from pg_class where
oid=$TG_relid;"
# loop over all the fields in the relation new getting field names and
values
foreach {fieldname fieldval} [array get NEW] {
# you can use this to assemble your SQL to insert into your row history
table (or pass it to a row-history-writer function as I do)
}
The functions that actually write the log run setuid (i.e. "Security of
definer" checkbox in pgAdmin or SECURITY DEFINER in PGSQL parlance).
This means that the audit (actions) table and row history tables can be
stored in schemas not readable by users.
Also bear in mind when implementing an audit trail in this way that
you'll have to apply any changes in the tables you are auditing to the
tables that store your audit trail, and this can get complex as the
tables evolve.
There was also some audit code for Postgres written in C, but I
couldn't find much documentation for it, so I abandonded it. I think a
comprehensive audit package for Postgres would be a great addition, but
sadly I lack the resources to contribute it.
Hope that helps,
Eric
[GENERAL] another problem with stored procedures
I have just encountered another problem. I am not sure if it is with my code, or with how I am working with Postgres/pgAdmin III.Here is another function, as created using the wizard/dialog boxin pgAmin III for creating functions:CREATE FUNCTION "People".get_pw(ea "varchar") RETURNS "varchar" AS$BODY$SELECT pword FROM "People".uids WHERE email_address = ea;$BODY$LANGUAGE 'sql' VOLATILE;When I click OK to indicate that I am finished, I get an error message saying there is no column called "ea". Of course I know that; that is because it is a function parameter instead. What I don't understand is why pgAdmin would not put the "IN" qualifier for the function's only parameter or why Postgres would think ea is a column when the code clearly identifies it as a function parameter. (BTW: replacing 'sql' by 'plpgsql' has no effect, except the error message is even less informative).Any ideas? What I am after is a simple select procedure returning the contents of pword in the record where the contents of email_address are the same as the contents of the parameter ea. I figure that if the result set returned to the calling Java/JDBC code is empty, the email address offered does not exist in the database and that, if there is one record, I'll compare the string value returned withthe password offered by the user in order to authenticate the user. Then, if authentication succeeds, I'll query a different database to see what resources the user is authorized to use. I have used, through JDBC function calls that end up submitting something like the following to the RDBMS back end: SELECT pword FROM "People".uids WHERE email_address = 'ea_value'; these all worked fine. It was just a little tedious to concatenate the various strings so that the contents of the SQL statement string looked like the above statement. I can't see a reason why I'd have trouble transforming the above select statement into a stored function. BTW: I know I can do this my old way of using prepared statements with JDBC and java, but I read that I can make my distributedapplication more secure by putting all my SQL into stored, parameterizedprocedures. What are the SQL related attacks that a web application is vulnerable to, and how effective is the approach of placing all my SQL into stored procedures at countering them. Are prepared statements any more, or less, usefulin making a distributed application more secure? Of course, I'd have validation code on both the client side and within my servlet that processes user data. After all, I have bitten the bullet to learn about stored procedures and functions precisely because of my studies of ways to make distributed applications secure. Thanks, Ted R.E. (Ted) Byers, Ph.D., Ed.D.R D Decision Support Softwarehttp://www.randddecisionsupportsolutions.com/
Re: [GENERAL] another problem with stored procedures
On Thu, 29 Dec 2005, Ted Byers wrote: I have just encountered another problem. I am not sure if it is with my code, or with how I am working with Postgres/pgAdmin III. Here is another function, as created using the wizard/dialog box in pgAmin III for creating functions: CREATE FUNCTION People.get_pw(ea varchar) RETURNS varchar AS $BODY$ SELECT pword FROM People.uids WHERE email_address = ea; $BODY$ LANGUAGE 'sql' VOLATILE; When I click OK to indicate that I am finished, I get an error message saying there is no column called ea. Of course I know that; that is because it is a function parameter instead. From the create function docs: The name of an argument. Some languages (currently only PL/pgSQL) let you use the name in the function body. For other languages the name of an input argument is just extra documentation. But the name of an output argument is significant, since it defines the column name in the result row type. (If you omit the name for an output argument, the system will choose a default column name.) SQL language functions are definately in the for other languages portion. I think you'll need to refer to it as $1 inside the function. What I don't understand is why pgAdmin would not put the IN qualifier for the function's only parameter or why Postgres would think ea is a column when the code clearly identifies it as a function parameter. (BTW: replacing 'sql' by 'plpgsql' has no effect, except the error message is even less informative). Bare sql won't make a valid plpsql function, so you probably would get an error at the select or some such. ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Stored Procedure: PL/Perl or PL/SQL?
On 12/29/05 4:10 PM, Joshua Kramer [EMAIL PROTECTED] wrote: Greetings all, I'm working to integrate an accounting system (http://www.linuxcanada.com) with another application. Fortunately, both use PG, so integration should be easy. I want to be able to insert invoices, and invoice line items, into the accounting system. As you might expect, this requires many selects and inserts involving serveral tables wrapped around a transaction. As I see it, there are a couple of ways to do this: 1. Attach a trigger which runs a Stored Procedure in PL/SQL; 2. Create a Perl Module that connects to a database via DBI and does the work; the trigger would then be written in PL/Perl, and would use the Perl Module to do the work. The advantage to #2 is that I'd have invoice migration and a general-purpose module for inserting invoices, with the same amount of work that I'd have for just invoice migration using PL/SQL. The drawback is the overhead of using Perl inside PG; how much overhead is there? What else should I consider? There have been a few discussions about this in the recent past (look in the archives) and the general consensus is that one should use the language that is most comfortable (known). After that, use the language that is best suited to the task. Array manipulations in PL/PgSQL are given as one example of where pl/perl might be easier and faster. As for overhead, there isn't any more for pl/perl than for pl/pgsql, from what I understand. One final point if you are thinking of writing a perl module for use in pl/perl, the function will have to be run as untrusted, I think. This could have changed recently, but I'm not aware of it. So, choose whichever is easiest for you for the job. If performance is the ONLY issue, then testing under real conditions is probably the only way to be sure that what you are doing is the right way. Sean ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Queries never returning...
It looks like my primary slowdown on that query was the timestamp
trigger. However, even after removing that trigger, and ensuring that
all of my referencing tables had their foreign keys indexed, a simple
update of one column on 244451 records took 14 minutes. Given the specs
I mentioned in the earlier email, is this to be expected?
I have also modified my entire schema to use the more updated constraint
syntax. I wrote a php script which can be used on a pg_dump. I have
attached it here in case anyone else ends up needing this:
-file fixkey.php---
#!/usr/bin/php
?
echo This script modifies a schema file generated by pg_dump and
converts any pre 7.3 foreign key triggers to proper foreign key
constraint syntax. I have only tested it on a dumpfile generated by
Postgres 8.0.3. I have no idea if this will work on any other version,
or with other people's wacky schemas. This worked for me and that's all
I can say. Don't blame me if this script burns down your house.\n\n;
if( $argc != 3 ) {
die(Usage: fixkey.php schemafile.db outfile.db\n);
}
$fp = fopen($argv[1], r);
$fpout = fopen($argv[2], w);
if( !$fp ) {
die(Error opening ' . $argv[1] . ' for read\n);
}
if( !$fpout ) {
die(Error opening ' . $argv[2] . ' for write\n);
}
while( $line = fgets($fp, 5000) ) {
if( strstr($line, ConstraintTrigger_ ) ) {
//echo Skipping comment $line\n;
}
else if( strstr($line, CREATE CONSTRAINT TRIGGER ) ) {
$keyname = substr($line, strlen(CREATE CONSTRAINT TRIGGER ) );
$keyname = trim($keyname);
//Get 5 lines after declaration for foreign key info
$line2 = fgets($fp, 5000);
$line3 = fgets($fp, 5000);
$line4 = fgets($fp, 5000);
$line5 = fgets($fp, 5000);
$line6 = fgets($fp, 5000);
//Foreign keys are apparently made of up 3 triggers...we only
care about the first one
//I assume that the subsequent ones will be implicitly created
by the new syntax
if( !$key_array[$keyname] ) {
//Store key name so we don't process it again
$key_array[$keyname] = 1;
//Referencing table name is in line 2
$table = explode( , $line2);
$table = $table[count($table)-1];
$table = trim($table);
//Referenced table is in line 3
$parent = explode( , $line3);
$parent = $parent[count($parent)-1];
$parent = trim($parent);
//Referencing column is on line 6
$column = explode(,, $line6);
$column = $column[4];
$column = str_replace(', , $column);
$column = trim($column);
//Referenced column is on line 6
$parentcolumn = explode(,, $line6);
$parentcolumn = $parentcolumn[5];
$parentcolumn = str_replace(', , $parentcolumn);
$parentcolumn = str_replace(), , $parentcolumn);
$parentcolumn = str_replace(;, , $parentcolumn);
$parentcolumn = trim($parentcolumn);
$sKeySQL = ALTER TABLE $table ADD CONSTRAINT $keyname
FOREIGN KEY ($column) REFERENCES $parent ($parentcolumn) MATCH FULL;\n;
echo $sKeySQL;
fputs($fpout, \n\n . $sKeySQL . \n\n);
}
}
else {
fputs($fpout, $line);
}
}
fclose($fp);
fclose($fpout);
?
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[GENERAL] Simple Accumulating Number Loop?
A friend of mine has created this simple accumulating loop query for MS SQL 2k5 Express Edition. I am trying to reproduce the same results with PostgreSQL 8.1, but am not able to find much useful help on how to properly set up a variable of both int and char. The PostgreSQL documentation is great if you have an idea of what you are doing, but I'm still really new to PostgreSQL. If someone can just point out some hints or clarifications as to wether to use the SET command or the psql \set command. Below is the query in question. Many thanx to any that can help. -Ubence declare @variableint int declare @desc char (50) set @variableint = 0 create table counter (countid int , description varchar(50)) while @variableint 500 begin set @variableint = @variableint + 1 set @desc = 'The Counter is Now' +' '+ cast (@variableint as char(50)) insert into counter values (@variableint,@desc) end select * from counter ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [GENERAL] Simple Accumulating Number Loop?
Ubence Quevedo wrote: A friend of mine has created this simple accumulating loop query for MS SQL 2k5 Express Edition. I am trying to reproduce the same results with PostgreSQL 8.1, but am not able to find much useful help on how to properly set up a variable of both int and char. The PostgreSQL documentation is great if you have an idea of what you are doing, but I'm still really new to PostgreSQL. If someone can just point out some hints or clarifications as to wether to use the SET command or the psql \set command. Below is the query in question. Many thanx to any that can help. -Ubence declare @variableint int declare @desc char (50) set @variableint = 0 create table counter (countid int , description varchar(50)) while @variableint 500 begin set @variableint = @variableint + 1 set @desc = 'The Counter is Now' +' '+ cast (@variableint as char(50)) insert into counter values (@variableint,@desc) end select * from counter look for FOR or WHILE loops http://www.postgresql.org/docs/8.1/interactive/plpgsql-control-structures.html ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] In processing DDL, when does pg_catalog get updated?
I'm running a DDL script that does the following (in this order): 1. Creates a table containing a BIGSERIAL primary key column declaration, which apparently automatically creates a sequence to populate this column. 2. Runs a gen_sequences function that I wrote, which executes CREATE SEQUENCE statements for all columns in the table that have defaults like 'nextval%' but that don't already have sequences. The part of the function that checks that the sequence doesn't already exist consults the pg_catalog, as follows: IF NOT EXISTS (SELECT 1 FROM pg_catalog.pg_class AS t, pg_catalog.pg_namespace AS s WHERE t.relname = sequence_name AND s.nspname = schema_name AND t.relnamespace = s.oid AND t.relkind = 'S') THEN execute the CREATE SEQUENCE statement END IF; This script aborts with a message like this: ERROR: relation my_table_id_seq already exists ...which implies that the code above is not finding my_table_id_seq in the catalog. I know that the code works OK in detecting sequences that pre-existed the execution of this script. So the only explanation that I can come up with is that, at step 2, the pg_catalog has not yet been updated to reflect the results of step 1 - namely, that the new sequence has been created. Is it possible that the pg_catalog is not updated with the results of a DDL script until the whole script has executed? If this is so, is there any way to force the pg_catalog to be updated along the way? ~ TIA ~ Ken ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] In processing DDL, when does pg_catalog get updated?
Ken Winter [EMAIL PROTECTED] writes: Is it possible that the pg_catalog is not updated with the results of a DDL script until the whole script has executed? No, the serial sequence should exist as soon as the CREATE TABLE is done ... unless you are doing something weird like wrapping the whole thing in a transaction and expecting uncommitted transaction results to be visible from another session. Could you show us a complete test case instead of an extract? regards, tom lane ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
