Re: [GENERAL] Setting table ids in slony
Pat Maddox wrote: I want to start using slony for replication, and have a question about setting table IDs when creating replication sets. The docs say that you have to be careful in what IDs you assign to the tables - if there's a relationship between two tables, the parent needs to have a lower ID. Can you give a reference for this? AFAIK the only requirement is that all linked tables need to be in the same set. I seem to recall reading that if you use inheritence you should check the parent comes before its children, but nothing about fkeys. http://cbbrowne.com/info/faq.html "Q: Is the ordering of tables in a set significant? A: Most of the time, it isn't. You might imagine it of some value to order the tables in some particular way in order that "parent" entries would make it in before their "children" in some foreign key relationship; that isn't the case since foreign key constraint triggers are turned off on subscriber nodes." -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Slow query using simple equality operators
Benjamin Arai wrote: > Hi, > > I upgraded to 8.2.4 but there was no significant change in performance. > I did notice that hte query appears to be executed incorrectly. > I have pasted the EXPLAIN ANALYZE below to illustrate: > > =# explain analyze select s_content,textdir from (SELECT * from > text_search WHERE tb_id='P2_TB1') AS a where path_id='4'; What's wrong with a plain select * from text_search where tb_id='P2_TB1' and path_id=4; ? You posted the explain output of something like that earlier, but that was on an older pg 8 and without analyze. > > QUERY PLAN > - > > Bitmap Heap Scan on text_search (cost=39864.98..59746.59 rows=5083 > width=36) (actual time=7418.651..7418.863 rows=52 loops=1) >Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB1'::text)) >-> BitmapAnd (cost=39864.98..39864.98 rows=5083 width=0) (actual > time=6706.928..6706.928 rows=0 loops=1) > -> Bitmap Index Scan on idx_search_path_id > (cost=0.00..16546.34 rows=1016571 width=0) (actual > time=6609.458..6609.458 rows=52777 loops=1) The row estimates are off by a factor 20 (~1M rows estimated, 50k rows actual). Are you sure you analyze'd recently? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] tsearch2 dictionary that indexes substrings?
My colleague who speaks more C than me came up with the code below which works fine for us. Will the memory allocated for lexeme be freed Nice, except self-defined utf8 properties. I think it will be much better to use pg_mblen(char*). In this case your dictionary will work with any supported by pgsql encodings. by the caller? Yes, of course. -- Teodor Sigaev E-mail: [EMAIL PROTECTED] WWW: http://www.sigaev.ru/ ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] PQerrorMessage: suppress trailing new line?
On Tue, 24 Apr 2007 11:14:43 +1200 "Andrej Ricnik-Bay" <[EMAIL PROTECTED]> wrote: > On 4/24/07, Felix Kater <[EMAIL PROTECTED]> wrote: > > Hi, > > > > the messages returned by PQerrorMessage() contain a trailing new > > line. So, they doesn't nicely integrate into strings formatted by > > printf. > > > > Is there a way to suppress the trailing new line (except for > > alloc/copy to a new string)? > The alloc kind of suggests that you're using C ... is that > correct? Either way - please be a bit more specific as to what you're > trying to achieve by which means. Sorry (was deep in thoughs while coding...). A bit more detailed: I am using libpq, the C language pg library. There is a useful function to return the last error message as human readable text: PQerrorMessage. While the returned text message is static memory which is good (you simply print it and don't have to free the memory) there is a trailing new line character at each text message like this: <0-byte-terminator> I find this newline a lot uncommon--since it is easy to add manually if you really want it, and, however, it is not so easy to get rid of it. Getting rid of it means: Copying the whole string without the newline, pass it to the user, who has to free the copied memory after printing it... I wanted to know if there is a switch or similar somewhere to get the messages without newline character. Thank You Felix ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Regarding WAL
On 4/24/07, Mageshwaran <[EMAIL PROTECTED]> wrote: I want to do replication using WAL , please tell the methods by which log shipping is done ie moving the wal files to slaves and executing it. Not possible at the moment: the log shipping facility that was introduced in 8.2 only lets you set up a so-called warm standby, which cannot be queried; it's not live replication. The warm standby system is a fairly crude hack that relies on WAL files being copied from the main server to the standby and then starting the standby in recovery mode when you want to bring it up. At this point the standby is your main database, and it can no longer WAL files. Documented here: http://www.postgresql.org/docs/8.2/static/warm-standby.html Alexander. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] pg_buffercache view
Dear all, About the pg_buffercache view: I couldn't find the description for this view in the manual at http://www.postgresql.org/docs/8.2/interactive/catalogs.html However I found the readme file provided in the /contrib./pg_buffercache of the source code for version 8.2.3 Here it's written the following description: Column | references | Description +--+ bufferid | | Id, 1..shared_buffers. relfilenode| pg_class.relfilenode | Refilenode of the relation. reltablespace | pg_tablespace.oid| Tablespace oid of the relation. reldatabase| pg_database.oid | Database for the relation. relblocknumber | | Offset of the page in the relation. isdirty| | Is the page dirty? I've 2 questions: 1) I was not able to find the field "oid" from pg_database view. Could you please tell me what is the actual name of the column for which reldatabase is reffering to? 2) In readme file is also written: "Unused buffers are shown with all fields null except buffered". A "used" buffer means that is used 100% or could it be filled only partially? Is there any way to know at a certain moment with precision how much shared memory expressed in Mb is used? With best regards, Sorin ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setting table ids in slony
On 4/24/07, Richard Huxton <[EMAIL PROTECTED]> wrote: Pat Maddox wrote: > I want to start using slony for replication, and have a question about > setting table IDs when creating replication sets. The docs say that > you have to be careful in what IDs you assign to the tables - if > there's a relationship between two tables, the parent needs to have a > lower ID. Can you give a reference for this? AFAIK the only requirement is that all linked tables need to be in the same set. I seem to recall reading that if you use inheritence you should check the parent comes before its children, but nothing about fkeys. Thanks for your reply, Richard. http://www.onlamp.com/pub/a/onlamp/2004/12/16/slony_install.html?page=2 says "Be careful when setting the ID number of a table; it also designates the order in which Slony will lock the tables. This means that master tables should have IDs lower than those of detail tables. The relationship hierarchy of your schema should help you determine the order of the numbers. If the ordering of the table IDs is backward or incorrect, there may be problems with deadlocking the slon process or PostgreSQL." Also directly from the documentation http://slony.info/documentation/stmtsetaddtable.html "Unique ID of the table. These ID's are not only used to uniquely identify the individual table within the replication system. The numeric value of this ID also determines the order in which the tables are locked in a LOCK SET command for example. So these numbers should represent any applicable table hierarchy to make sure the slonik command scripts do not deadlock at any critical moment." http://cbbrowne.com/info/faq.html "Q: Is the ordering of tables in a set significant? A: Most of the time, it isn't. You might imagine it of some value to order the tables in some particular way in order that "parent" entries would make it in before their "children" in some foreign key relationship; that isn't the case since foreign key constraint triggers are turned off on subscriber nodes." Sounds like theoretically it could matter, but in practice it doesn't. I'd like a more definite answer though. Pat ---(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] Setting table ids in slony
Sounds like theoretically it could matter, but in practice it doesn't. I'd like a more definite answer though. You could ask the slony guys directly ;) They'd know a lot more about it than the pg-general list would. http://lists.slony.info/mailman/listinfo -- Postgresql & php tutorials http://www.designmagick.com/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Setting table ids in slony
Pat Maddox wrote: Sounds like theoretically it could matter, but in practice it doesn't. I'd like a more definite answer though. Well, most of the locking issues with Slony seem to be with administrative commands (setting up a replication set, altering it) which require taking locks. If your application(s) lock tables in the order C,B,A and slony in A,B,C then they can deadlock waiting on each other. This is a problem you'll face any time you have two sets of exclusive locks interacting. I'm fortunate in that the systems I deal with all allow for some downtime in application access, so I just schedule slony changes for these periods. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] pgsql-general@postgresql.org
Hi, I am trying to use cursors and I am really frustrated already. Do I need to install an extension? 1. Problem number one is that what ever I use in front of the fetch command it is not being accepted, it gives a syntax error. If I use a number ,"all" or "forward" it gives an error again?? I want to do something like the code below: CREATE OR REPLACE FUNCTION database_correction() RETURNS double precision AS $BODY$ DECLARE mycursor CURSOR FOR select distinct(fund_id) from "NAV_values_bfb_history"; iterator integer; BEGIN open mycursor; FETCH mycursor INTO iterator; --fetch next from mycursor --gives an error WHILE (FETCH next from mycursor) LOOP -- some computations here END LOOP; CLOSE mycursor; END; 2. What is the right way to check that the cursor has ended. In sqlserver there is a variable "@@fetch_status". I have to make here some comparison in the while clause, but I am not sure what it should be. I could not find a single example for cursor in a loop. I will greatly appreciate any help, pgsql is my database of choice. Cheers, Anton ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
[GENERAL] Problem Backing Up a DB
Hello,
I have a problem with backing up a database. The original db runs on a SuSE
ES 9 box in Unicode. Version 7.4.2
The recovery db is supposed to run on a windows xp box. The encoding is
UTF-8. Version 8.1
On the linux box I did:
pg_dumpall > outfile
On the windows box I did:
psql -f outfile authdb
Problem is when I reload the db in this manner the sequences for the id
column gets messed up ("user_auth_id2_seq"). The count starts from 1 again
when I insert a new row. :o(
For error output please see below. I already created the empty db authdb on
the recovery instance because otherwise an error is thrown.
Cheers,
Pete
C:\Programme\PostgreSQL\8.1\bin>psql -f C:/pgsqldump_20-04-07.sql authdb
Password:
You are now connected to database "template1".
psql:C:/pgsqldump_20-04-07.sql:11: ERROR: cannot delete from a view
HINT: You need an unconditional ON DELETE DO INSTEAD rule.
psql:C:/pgsqldump_20-04-07.sql:13: NOTICE: SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:13: ERROR: role "admin" already exists
psql:C:/pgsqldump_20-04-07.sql:14: NOTICE: SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:14: ERROR: role "foo" already exists
psql:C:/pgsqldump_20-04-07.sql:15: NOTICE: SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:15: ERROR: role "www" already exists
psql:C:/pgsqldump_20-04-07.sql:22: ERROR: cannot delete from a view
HINT: You need an unconditional ON DELETE DO INSTEAD rule.
psql:C:/pgsqldump_20-04-07.sql:24: NOTICE: SYSID can no longer be specified
psql:C:/pgsqldump_20-04-07.sql:24: ERROR: role "www" already exists
psql:C:/pgsqldump_20-04-07.sql:25: ERROR: role "www" is a member of role
"www"
psql:C:/pgsqldump_20-04-07.sql:32: ERROR: database "authdb" already exists
You are now connected to database "authdb".
SET
SET
SET
REVOKE
GRANT
SET
SET
psql:C:/pgsqldump_20-04-07.sql:83: NOTICE: CREATE TABLE will create
implicit sequence "user_aut
umn "user_auth.id"
CREATE TABLE
REVOKE
GRANT
CREATE TABLE
CREATE TABLE
REVOKE
GRANT
SET
psql:C:/pgsqldump_20-04-07.sql:157: NOTICE: CREATE TABLE will create
implicit sequence "login_i
"login.id"
CREATE TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:173: NOTICE: CREATE TABLE will create
implicit sequence "antwort
lumn "antworten.id"
CREATE TABLE
psql:C:/pgsqldump_20-04-07.sql:188: NOTICE: CREATE TABLE will create
implicit sequence "beitrae
lumn "beitraege.id"
CREATE TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:203: NOTICE: CREATE TABLE will create
implicit sequence "user_lo
umn "user_log.id"
CREATE TABLE
REVOKE
SET
SET
SET
SET
SET
psql:C:/pgsqldump_20-04-07.sql:5010: NOTICE: ALTER TABLE / ADD PRIMARY KEY
will create implicit
table "user_auth"
ALTER TABLE
psql:C:/pgsqldump_20-04-07.sql:5019: NOTICE: ALTER TABLE / ADD PRIMARY KEY
will create implicit
le "contentlist_summary"
ALTER TABLE
psql:C:/pgsqldump_20-04-07.sql:5028: NOTICE: ALTER TABLE / ADD PRIMARY KEY
will create implicit
table "user_data"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5039: NOTICE: ALTER TABLE / ADD PRIMARY KEY
will create implicit
ble "login"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5050: NOTICE: ALTER TABLE / ADD PRIMARY KEY
will create implicit
for table "antworten"
ALTER TABLE
psql:C:/pgsqldump_20-04-07.sql:5059: NOTICE: ALTER TABLE / ADD PRIMARY KEY
will create implicit
for table "beitraege"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5070: NOTICE: ALTER TABLE / ADD PRIMARY KEY
will create implicit
or table "user_log"
ALTER TABLE
SET
psql:C:/pgsqldump_20-04-07.sql:5080: ERROR: relation "user_auth_id2_seq"
does not exist
setval
58
(1 row)
setval
1
(1 row)
setval
1
(1 row)
SET
setval
28
(1 row)
SET
COMMENT
You are now connected to database "template1".
SET
SET
SET
REVOKE
GRANT
SET
COMMENT
SET
COMMENT
---(end of broadcast)---
TIP 6: explain analyze is your friend
[GENERAL] How does server handle clients that disconnect ungracefully?
Hello, I am writing an application that reads information from my PostgreSQL database. If I cause the program to crash and the program ends without gracefully disconnecting from the database, there is an open connection still left in the database. How does the PostgreSQL server handle clients that disconnect ungracefully? Will the server automatically clean up this connection that really is not there? Thanks. -Jeff ---(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] [pgadmin-support] questions about cursors
I did already, but this still does not help me write a simple while loop that goes through all data and stops at the last row. Joris Dobbelsteen wrote: See the postgresql documentation at: http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Anton Andreev Sent: dinsdag 24 april 2007 13:45 To: [EMAIL PROTECTED] Subject: [pgadmin-support] questions about cursors Hi, I am trying to use cursors and I am really frustrated already. Do I need to install an extension? 1. Problem number one is that what ever I use in front of the fetch command it is not being accepted, it gives a syntax error. If I use a number ,"all" or "forward" it gives an error again?? I want to do something like the code below: CREATE OR REPLACE FUNCTION database_correction() RETURNS double precision AS $BODY$ DECLARE mycursor CURSOR FOR select distinct(fund_id) from "NAV_values_bfb_history"; iterator integer; BEGIN open mycursor; Please see chapter 37.8.3.1 "FETCH" (in the 8.2 documentation). Important for you is: "As with SELECT INTO, the special variable FOUND may be checked to see whether a row was obtained or not." FETCH mycursor INTO iterator; --fetch next from mycursor --gives an error WHILE (FOUND) LOOP -- compute FETCH mycursor INTO interator; END LOOP; -- instead of WHILE (FETCH next from mycursor) LOOP -- some computations here END LOOP; I believe this should work. I've never really used cursors before, as I can usually do it with regular SQL statements (which are usually faster). I should also advise that you can create your own aggregate (see CREATE AGGREGATE in the documentation), which might, or might not, be easier for your purposes. CLOSE mycursor; END; 2. What is the right way to check that the cursor has ended. In sqlserver there is a variable "@@fetch_status". I have to make here some comparison in the while clause, but I am not sure what it should be. I could not find a single example for cursor in a loop. When FOUND evaluates to false, you should have completed. (In case of errors, you will probably have an exception being thrown). I will greatly appreciate any help, pgsql is my database of choice. Hope this helps... - Joris ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] pgsql-general@postgresql.org
Anton Andreev wrote: > Hi, > > I am trying to use cursors and I am really frustrated already. Do I > need to install an extension? No, you just need to have a look at the docs. http://www.postgresql.org/docs/8.2/static/plpgsql-control-structures.html#PLPGSQL-RECORDS-ITERATING > 1. Problem number one is that what ever I use in front of the fetch > command it is not being accepted, it gives a syntax error. If I use a > number ,"all" or "forward" it gives an error again?? I want to > do something like the code below: > > CREATE OR REPLACE FUNCTION database_correction() > RETURNS double precision AS > $BODY$ > [...] Try something like this: CREATE OR REPLACE FUNCTION database_correction() RETURNS double precision LANGUAGE plpgsql AS $body$ DECLARE fund INTEGER; BEGIN FOR fund IN SELECT DISTINCT(fund_id) FROM "NAV_values_bfb_history" LOOP RAISE NOTICE $$ foo bar $$; -- some computations here END LOOP; RETURN 42.0; END; $body$; -- Alvaro Herrerahttp://www.CommandPrompt.com/ The PostgreSQL Company - Command Prompt, Inc. ---(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] pg_buffercache view
In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > Dear all, > > About the pg_buffercache view: > I couldn't find the description for this view in the manual at > http://www.postgresql.org/docs/8.2/interactive/catalogs.html > However I found the readme file provided in the /contrib./pg_buffercache of > the source code for version 8.2.3 Since pg_buffercache is contributed software, it's not documented in the official PostgreSQL docs. > Here it's written the following description: > >Column | references | Description > > +--+ >bufferid | | Id, 1..shared_buffers. >relfilenode| pg_class.relfilenode | Refilenode of the relation. >reltablespace | pg_tablespace.oid| Tablespace oid of the relation. >reldatabase| pg_database.oid | Database for the relation. >relblocknumber | | Offset of the page in the > relation. >isdirty| | Is the page dirty? > > I've 2 questions: > 1) > I was not able to find the field "oid" from pg_database view. Could you > please tell me what is the actual name of the column for which reldatabase > is reffering to? At the end of the README is an example query that I think answers your question: SELECT c.relname, count(*) AS buffers FROM pg_class c, pg_buffercache b WHERE b.relfilenode = c.relfilenode GROUP BY c.relname ORDER BY 2 DESC LIMIT 10; > 2) > In readme file is also written: > "Unused buffers are shown with all fields null except buffered". > A "used" buffer means that is used 100% or could it be filled only > partially? Yes. The buffer is either "used" or "not used", but pg_buffercache doesn't know what percentage of it is used. >0% is used. 0% is not used. > Is there any way to know at a certain moment with precision how much shared > memory expressed in Mb is used? The precision is +/- 1 buffer. I expect that trying to get more precision out of the system will result in considerable performance degradation as the data is collected and/or tracked. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [ADMIN] [GENERAL] pg_buffercache view
Dear Mr. Bill Moran, Thank you for your answer. 1) To be more clear I would like to construct a query using the reldatabase column. In that query you quoted I can't identify the reldatabase column. I want a query that will help me to list how many buffers are used by each database Maybe something like: SELECT d.datname, count(*) AS buffers FROM pg_database d, pg_buffercache b WHERE d.X = b.reldatabase GROUP BY b.reldatabase ORDER BY 2 DESC LIMIT 10; I would like, if possible, to know which is the name of this X which corresponds to reldatabase column 2) I don't know exactly which is the modality the buffers are used. Is it possible that all buffers to be used at let's say 5% of their capacity? In this case I see in pg_buffercache that all the shared memory is used (since all the buffers are used) but in reality only 5% from it is actually used. With best regards, Sorin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Bill Moran Sent: Tuesday, April 24, 2007 4:03 PM To: Sorin N. Ciolofan Cc: [EMAIL PROTECTED]; pgsql-general@postgresql.org Subject: Re: [ADMIN] [GENERAL] pg_buffercache view In response to "Sorin N. Ciolofan" <[EMAIL PROTECTED]>: > > Dear all, > > About the pg_buffercache view: > I couldn't find the description for this view in the manual at > http://www.postgresql.org/docs/8.2/interactive/catalogs.html > However I found the readme file provided in the /contrib./pg_buffercache of > the source code for version 8.2.3 Since pg_buffercache is contributed software, it's not documented in the official PostgreSQL docs. > Here it's written the following description: > >Column | references | Description > > +--+ >bufferid | | Id, 1..shared_buffers. >relfilenode| pg_class.relfilenode | Refilenode of the relation. >reltablespace | pg_tablespace.oid| Tablespace oid of the relation. >reldatabase| pg_database.oid | Database for the relation. >relblocknumber | | Offset of the page in the > relation. >isdirty| | Is the page dirty? > > I've 2 questions: > 1) > I was not able to find the field "oid" from pg_database view. Could you > please tell me what is the actual name of the column for which reldatabase > is reffering to? At the end of the README is an example query that I think answers your question: SELECT c.relname, count(*) AS buffers FROM pg_class c, pg_buffercache b WHERE b.relfilenode = c.relfilenode GROUP BY c.relname ORDER BY 2 DESC LIMIT 10; > 2) > In readme file is also written: > "Unused buffers are shown with all fields null except buffered". > A "used" buffer means that is used 100% or could it be filled only > partially? Yes. The buffer is either "used" or "not used", but pg_buffercache doesn't know what percentage of it is used. >0% is used. 0% is not used. > Is there any way to know at a certain moment with precision how much shared > memory expressed in Mb is used? The precision is +/- 1 buffer. I expect that trying to get more precision out of the system will result in considerable performance degradation as the data is collected and/or tracked. -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem Backing Up a DB
Peter Neu wrote:
Hello,
I have a problem with backing up a database. The original db runs on a SuSE
ES 9 box in Unicode. Version 7.4.2
If you're keeping this installation, see about upgrading to the latest 7.4.x
The recovery db is supposed to run on a windows xp box. The encoding is
UTF-8. Version 8.1
On the linux box I did:
pg_dumpall > outfile
On the windows box I did:
psql -f outfile authdb
OK. First, it's always better to use a more recent pg_dump if possible.
If your linux box is accessible from the XP machine you can just do
something like:
C:\Program File\...\pg_dump -h -U postgres -d
-F c > mydb.dump
Secondly, you probably want to dump the single database and then any
users separately. Do this as a superuser (e.g. postgres) and use the
custom format (-F c) for maximum flexibility.
Then you can use pg_restore to restore to your target DB.
Problem is when I reload the db in this manner the sequences for the id
column gets messed up ("user_auth_id2_seq"). The count starts from 1 again
when I insert a new row. :o(
For error output please see below. I already created the empty db authdb on
the recovery instance because otherwise an error is thrown.
pg_restore can create the DB for you.
Well, it looks like setval() is being called at least four times, and
two of those have values > 1.
setval
58
setval
1
setval
1
setval
28
Try a later version of pg_dump and see if that does the trick.
--
Richard Huxton
Archonet Ltd
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] How does server handle clients that disconnect ungracefully?
On Tue, Apr 24, 2007 at 05:53:02AM -0700, Jeff Lanzarotta wrote: > Hello, > > I am writing an application that reads information from my PostgreSQL > database. If I cause the program to crash and the program ends without > gracefully disconnecting from the database, there is an open connection > still left in the database. > > How does the PostgreSQL server handle clients that disconnect > ungracefully? Will the server automatically clean up this connection > that really is not there? Yes, but it will log a notice about it in the server log. //Magnus ---(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 does server handle clients that disconnect ungracefully?
Jeff Lanzarotta wrote: Hello, I am writing an application that reads information from my PostgreSQL database. If I cause the program to crash and the program ends without gracefully disconnecting from the database, there is an open connection still left in the database. How does the PostgreSQL server handle clients that disconnect ungracefully? Will the server automatically clean up this connection that really is not there? Worst case is that the tcp/ip connection is never cleared down. That means the connection will sit there until your tcp/ip stack clears it down. I *think* that's an hour on linux. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] How does server handle clients that disconnect ungracefully?
OK, thanks. I figured the server would at some point in time clear the connection up, my question now is when does that happen? Any idea how long the connection sit around before the server cleans it up? Someone else said possibly an hour... --- Magnus Hagander <[EMAIL PROTECTED]> wrote: > On Tue, Apr 24, 2007 at 05:53:02AM -0700, Jeff Lanzarotta wrote: > > Hello, > > > > I am writing an application that reads information from my > PostgreSQL > > database. If I cause the program to crash and the program ends > without > > gracefully disconnecting from the database, there is an open > connection > > still left in the database. > > > > How does the PostgreSQL server handle clients that disconnect > > ungracefully? Will the server automatically clean up this > connection > > that really is not there? > > Yes, but it will log a notice about it in the server log. > > //Magnus > > > ---(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 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] Audit-trail engine: getting the application's layer user_id
Hey guys, I needed to implement an audit trail engine and decided to do it on the database layer. I already have a basic but fully functional audit trail system implemented on my PostgreSQL 8.2 server. It has been done using PL/PGSQL and triggers and it works pretty well. Here's what I need to do: Somehow save the user_id of the **application** user who have done the update/delete action to the log row. Mine is an web application - three tier. The app connects to the db using only one user and it has it's own authentication system and doesn't rely on the database for user management. I've read an article on IBM's developer site which teaches how to do just that (get the application's user id and save it the audit row) using what they call the APPLICATION_ID which is an unique ID that DB2 assigns to the app when it connects to the database. A relation table is then created to relate the user_id and application_id. When the user logs in, a new record is created in this table, and, as the application_id is available in the db enviroment to the procedural languages, we can then go to this table and finally get the user_id. Is there something like that in Postgres? Thanks in advance! Marcelo.
Re: [GENERAL] How does server handle clients that disconnect ungracefully?
On Tue, Apr 24, 2007 at 06:55:12AM -0700, Jeff Lanzarotta wrote: > OK, thanks. I figured the server would at some point in time clear the > connection up, my question now is when does that happen? Any idea how > long the connection sit around before the server cleans it up? Someone > else said possibly an hour... Depends on yuor OS - when the TCP connection times out, PostgreSQL will clean up and terminate the backup. //Magnus ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Building PG 8.2.3 for x86_64 on Mac OS X 10.4.9
On Apr 19, 2007, at 3:21 PM, Kevin Murphy wrote: Has anybody tried making a 64-bit PostgreSQL on an Apple XServe w/ Intel Woodcrest CPU's? My compile works, but the 'make check' fails because of failure to allocate shared memory. There's plenty of SYSV memory available. The call to shmget() is failing with ENOMEM. A standalone SYSV shared memory test program gives the same result when compiled with -arch x86_64. OK, I finally found someone (Terry Lambert at Apple) to answer my question. Here's the thread: http://lists.apple.com/archives/darwin-kernel/2007/Apr/msg00021.html Excerpt: "It's trying to allocate the memory in the lower 4G of the process virtual address space, and unless you turn off the 4G page 0 mapping (or munamp() it after you're running but before you attempt to allocate shared memory), it's not going to see any free address space available." Regards, Kevin Murphy
Re: [GENERAL] How does server handle clients that disconnect ungracefully?
OK, that is what the other fellow said... It depends on the TCP/IP stack... Thanks again for the clarification. --- Magnus Hagander <[EMAIL PROTECTED]> wrote: > On Tue, Apr 24, 2007 at 06:55:12AM -0700, Jeff Lanzarotta wrote: > > OK, thanks. I figured the server would at some point in time clear > the > > connection up, my question now is when does that happen? Any idea > how > > long the connection sit around before the server cleans it up? > Someone > > else said possibly an hour... > > Depends on yuor OS - when the TCP connection times out, PostgreSQL > will > clean up and terminate the backup. > > //Magnus > > > ---(end of > broadcast)--- > TIP 6: explain analyze is your friend > ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Problem Backing Up a DB
Don't forget to cc: the mailing list. Peter Neu wrote: Hello, thanks for the fast reply. This works. I don't really need the dumpall because I usually just back up one db. I noticed the dump is now a binary file. Means that I will have to migrate to 7.4.17 if this is the preferable release because 7.4.2 can't read the binary file I suppose. Backup files in general don't work between versions. This is why you were having problems. However, pg_dump knows about older versions of the database and can read from them. If you want to transfer data from 7.4 to 8.1, use the pg_dump/pg_restore from 8.1. If you want to go backwards (8.1 to 7.4) you can't (in general - you can usually tweak an SQL file to get you there). The binary file can be used to generate your SQL with INSERTs or COPY commands as desired, or even do selective restores (--list/--use-list) direct to a database. The windows box was just for testing purposes. The productive database will run again on a linux machine if the current one fails. So, is this problem I encountered just happening because I switched from 7.x to the 8.x release or is there a general problem with this particular PostgreSQL release? A dump for one 7.4 database should transfer to another 7.4 database just fine. The standby linux machine would also run 7.4.2 because it ships with SuSE ES 9. Presumably ES9 includes bugfixes, so it must include a version later than 7.4.2 (or perhaps have its own numbering system for updates e.g. 7.4.2-11). If not, I'd suggest: 1. Cancelling your subscription for SuSE support and spending the money on a crate of good single-malt whisky(*) instead. The whisky will provide the same level of reliability for your database and is also more sociable. 2. Upgrade to 7.4.17 - that way you'll have the 15 different sets of bug fixes that the developers have provided. If you want to know what they all are, go here: http://www.postgresql.org/docs/7.4/static/release.html#RELEASE-7-4-17 Some of them are important to have, so you *do* want them. (*) Other beverages are available. If you do not partake of alcohol, consider a small artwork instead. HTH -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Postgres and geographically diverse replication
On Wed, 2007-04-18 at 16:43 -0400, Bill Moran wrote: > In response to "Drew Myers" <[EMAIL PROTECTED]>: > > > > I've been given a task to build a couple of geographically separate > > servers, which are capable of replicating data between each other. > > > > I've surfed through various google results, and most of what I've found > > seems to be a bit dated, so I thought I'd pose my question here, perhaps > > for more detailed and more up-to-date info. > > > > Is this normally done in a push/pull scenario within the postgres > > installations themselves, or is additional software required? What are > > the various replication capabilities? > > > > I apologize for the general nature of my questions, I'm new to postgres > > and to geographically separate replication. Any tips, books, whitepapers > > or other resources you might be able to point me to is most appreciated. > > Generally speaking, when you're talking geographically separate, Slony > is your best bet. We're using it to maintain data on opposites sides of > the US with good success. Successfully using slony over a wide area is going to depend on how much data you are replicating, how fast the connection between the two sites is, and how stable the connection between the two sites is. -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Postgres and geographically diverse replication
In response to Brad Nicholson <[EMAIL PROTECTED]>: > On Wed, 2007-04-18 at 16:43 -0400, Bill Moran wrote: > > In response to "Drew Myers" <[EMAIL PROTECTED]>: > > > > > > I've been given a task to build a couple of geographically separate > > > servers, which are capable of replicating data between each other. > > > > > > I've surfed through various google results, and most of what I've found > > > seems to be a bit dated, so I thought I'd pose my question here, perhaps > > > for more detailed and more up-to-date info. > > > > > > Is this normally done in a push/pull scenario within the postgres > > > installations themselves, or is additional software required? What are > > > the various replication capabilities? > > > > > > I apologize for the general nature of my questions, I'm new to postgres > > > and to geographically separate replication. Any tips, books, whitepapers > > > or other resources you might be able to point me to is most appreciated. > > > > Generally speaking, when you're talking geographically separate, Slony > > is your best bet. We're using it to maintain data on opposites sides of > > the US with good success. > > Successfully using slony over a wide area is going to depend on how much > data you are replicating, how fast the connection between the two sites > is, and how stable the connection between the two sites is. That's all true. It's also true of _any_ geographically diverse replication. Except, of course, Microsoft's. According to Microsoft's marketing materials, their replication system is able to exceed the speed of light. So far, we've found Slony to do an excellent job of gracefully handing intermittent network problems and occasional transaction spikes that temporarily exceeded our available bandwidth. Obviously, excessive instances of either of these situations are going to make Slony fail, but they'll do that to _any_ replication system, except those that can exceed the speed of light ... -- Bill Moran http://www.potentialtech.com ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] Generic triggers ?
Hi, I wanted to setup a simple "generic" type trigger. What a mean by generic is that i don't want to hardcode the NEW. or OLD. calls, i searched for a way to loop over the NEW/OLD rowtypes but could not figure out how it should be done. This led me to attempt to do it in a way which i think is incorrect, first off i extract the column names for the given table (TG_RELNAME) from the information schema like so -- extract the column names from tab FOR colname IN (SELECT column_name FROM information_schema.columns WHERE table_name = tab) LOOP colnames[counter] := colname.column_name; counter := counter + 1; END LOOP; Where tab is TG_RELNAME, i then store them in a varchar array (i have verified that the values contained are correct). If the TG_OP is 'UPDATE' i then wanted to figure out what column had been changed (at a later date i might also list the new and old vaules), so the plan was to do something like this: ELSIF (TG_OP = 'UPDATE') THEN FOR i IN 1 .. counter LOOP IF(NEW.colnames[i] <> OLD.colnames[i])THEN -- only for testing, to see if a change in a column can be detected values_changed := values_changed || colnames[i] || ' was changed'; END IF; END LOOP; -- values_changed currently contains rubbish INSERT INTO audit VALUES (audit_id, 'UPDATE', values_changed, '', now(), TG_RELNAME); ELSE The above code does nothing useful and is only to test out the concept. The problem comes from NEW.colnames[i] because the NEW rowtype does not have a column called colnames. Can anyone tell me how to append the value of colnames[i] to NEW so it would work, or is it even possible to iterate of the NEW/OLD rowtype without having to know the column names ? Thanks in advance, Andy
Re: [GENERAL] How does server handle clients that disconnect ungracefully?
On Tue, Apr 24, 2007 at 07:16:38AM -0700, Jeff Lanzarotta wrote: > OK, that is what the other fellow said... It depends on the TCP/IP > stack... > > Thanks again for the clarification. Note, there are two cases. If only the program crashes but the system is fine, the kernel will close the socket for you straight away and the server with notice the hangup. The only time the server will wait for a time out is if the server and the client are on different machines and something disrupts actual communications. If the client machine crashes without notifying the server, or someone pulls out a network cable. If the client and the server are on the same machine, it will always notice. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Generic triggers ?
On Tue, Apr 24, 2007 at 05:35:09PM +0200, Andy Dale wrote: > Hi, > > I wanted to setup a simple "generic" type trigger. What a mean by generic > is that i don't want to hardcode the > NEW. or OLD. calls, i searched for a way to loop over > the NEW/OLD rowtypes but could not figure out how it should be done. You're trying to do something that pl/pgsql it notoriously bad at. I suggest you use some more dynamic language like perl/python/etc. > The above code does nothing useful and is only to test out the concept. The > problem comes from NEW.colnames[i] because the NEW rowtype does not have a > column called colnames. Can anyone tell me how to append the value of > colnames[i] to NEW so it would work, or is it even possible to iterate of > the NEW/OLD rowtype without having to know the column names ? Not in pl/pgsql (being statically typed). In some other languages you can. Have a nice day, -- Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to > litigate. signature.asc Description: Digital signature
Re: [GENERAL] Generic triggers ?
Andy Dale wrote: Hi, I wanted to setup a simple "generic" type trigger. What a mean by generic is that i don't want to hardcode the NEW. or OLD. calls, i searched for a way to loop over the NEW/OLD rowtypes but could not figure out how it should be done. Don't use plpgsql - that's the secret. Try pltcl or plperl or similar. PL/pgsql is a bit too "statically typed" for it. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] pgsql-general@postgresql.org
> I am trying to use cursors and I am really frustrated already. Do I > need to install an extension? No, it's all in the documentation: http://www.postgresql.org/docs/current/static/plpgsql-control-structures .html#PLPGSQL-RECORDS-ITERATING > 1. Problem number one is that what ever I use in front of the fetch > command it is not being accepted, it gives a syntax error. If I use a > number ,"all" or "forward" it gives an error again?? I want to > do something like the code below: > > CREATE OR REPLACE FUNCTION database_correction() > RETURNS double precision AS > $BODY$ > DECLARE > mycursor CURSOR FOR select distinct(fund_id) from > "NAV_values_bfb_history"; > iterator integer; > > BEGIN > open mycursor; > > FETCH mycursor INTO iterator; > > --fetch next from mycursor --gives an error > > WHILE (FETCH next from mycursor) LOOP > -- some computations here > END LOOP; > > CLOSE mycursor; > END; My suggestion: $BODY$ DECLARE a_row RECORD; BEGIN FOR a_row IN SELECT DISTINCT(fund_id) FROM "NAV_values_bfb_history" LOOP -- some computations here -- access the value as "a_row.fund_id" END LOOP; END; $BODY$ > 2. What is the right way to check that the cursor has ended. In > sqlserver there is a variable "@@fetch_status". I have to make here some > comparison in the while clause, but I am not sure what it should be. I > could not find a single example for cursor in a loop. You do not need that at all, the loop will be left if there are no more results. Yours, Laurenz Albe ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] How does server handle clients that disconnect ungracefully?
Martijn van Oosterhout <[EMAIL PROTECTED]> writes: > The only time the server will wait for a time out is if the server and > the client are on different machines and something disrupts actual > communications. Right, you need connectivity loss to create an issue --- a client program crash doesn't cause this type of problem. The most common form of the problem that I've heard about is routers deciding to drop a connection that's been idle too long. BTW, on some platforms it's possible to change the timeout settings so that a lost connection is abandoned more quickly by the TCP stack. See the tcp_keepalives_xxx parameters if you need to do that. 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
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
I forgot to add the link to the article I've mentioned: http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b This is what I'd like to do on PostgreSQL, Thanks, Marcelo. On 4/24/07, Marcelo de Moraes Serpa <[EMAIL PROTECTED]> wrote: Hey guys, I needed to implement an audit trail engine and decided to do it on the database layer. I already have a basic but fully functional audit trail system implemented on my PostgreSQL 8.2 server. It has been done using PL/PGSQL and triggers and it works pretty well. Here's what I need to do: Somehow save the user_id of the **application** user who have done the update/delete action to the log row. Mine is an web application - three tier. The app connects to the db using only one user and it has it's own authentication system and doesn't rely on the database for user management. I've read an article on IBM's developer site which teaches how to do just that (get the application's user id and save it the audit row) using what they call the APPLICATION_ID which is an unique ID that DB2 assigns to the app when it connects to the database. A relation table is then created to relate the user_id and application_id. When the user logs in, a new record is created in this table, and, as the application_id is available in the db enviroment to the procedural languages, we can then go to this table and finally get the user_id. Is there something like that in Postgres? Thanks in advance! Marcelo.
Re: [GENERAL] PG service restart failure (start getting ahead of stop?)
> > So it looks like the STOPPING of the service actually > > succeeded, albeit > > it took a while (more than the usual sessions open?). The > > STARTING is > > the one that actually failed (is that because the STOP was still in > > process?). The question is why -- in a RESTART situation > > wouldn't/shouldn't the START part wait for the STOP part to complete > > (regardless of how long it takes)? > > Well, this'd depend on the details of the postgres init script you're > using, which you gave no hint about (and yes, there are a *ton* of > different versions out there). The one I'm currently shipping for Red > Hat would give up waiting after a minute, but it should report failure > not success in that case. oh, sorry, i did not realize there were many of them. this is the 8.1.8 redhat one; the (hopefully) identifying lines are: 64 # Version 8.1 Devrim Gunduz <[EMAIL PROTECTED]> 65 # Increased sleep time from 1 sec to 2 sec. 66 67 # PGVERSION is the full package version, e.g., 8.1.1 68 # Note: the specfile ordinarily updates this during install 69 PGVERSION=8.1.8 i have examined the stop() and start() and i think i understand why the stop() reported a failure (it took to long), but i don't understand how the start() could have reported success: > Stopping postgresql service: [FAILED] > Starting postgresql service: [ OK ] there was definitely no running DB after that (until someone manually started it hours later). > > We have a nightly restart of one PG database. > > Just out of curiosity, what for? I can't imagine any really > good reason > for just shutting down the postmaster and immediately restarting it. why have a "restart" option if there is never a reason for it? :-) seriously, this is a good question, i think this was someone's idea of a quick way to clear any remaining DB sessions in order to be able to drop a database and restore a newer version (this is a reporting DB that gets refreshed nightly with a dump from another DB). this seems a bit misguided in that if you want to kill sessions you should just kill sessions, (a la http://varlena.com/GeneralBits/29.html), right? what's your opinion on the best way to do this? thanks. george ---(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] Generic triggers ?
Thanks for the quick response, i think will try it with python or perl. Cheers, Andy On 24/04/07, Richard Huxton <[EMAIL PROTECTED]> wrote: Andy Dale wrote: > Hi, > > I wanted to setup a simple "generic" type trigger. What a mean by generic > is that i don't want to hardcode the > NEW. or OLD. calls, i searched for a way to loop over > the NEW/OLD rowtypes but could not figure out how it should be done. Don't use plpgsql - that's the secret. Try pltcl or plperl or similar. PL/pgsql is a bit too "statically typed" for it. -- Richard Huxton Archonet Ltd
[GENERAL] WAL files, warm spares and minor versions
Can WAL files be used to create/update a warm standby on a different minor version of PostgreSQL (eg, using files from a server running 8.2.3 on an 8.2.4 server, or vice-versa?) I suspect this is a FAQ, but I didn't see it in the docs on WALs and PITR (section 23.3) -- Mike Nolan
Re: [GENERAL] PG service restart failure (start getting ahead of stop?)
"George Pavlov" <[EMAIL PROTECTED]> writes: > i have examined the stop() and start() and i think i understand why the > stop() reported a failure (it took to long), but i don't understand how > the start() could have reported success: >> Stopping postgresql service: [FAILED] >> Starting postgresql service: [ OK ] Well, that makes sense: if the shutdown took more than a minute then the "stop" script action would give up waiting, and then the "start" action would see the postmaster running and go away happy. (It's a bit odd that "service start" actions are supposed to treat "already running" as OK, but I've been told that that's required by the Linux Standards Base and I can't change it.) The real question here is why'd it take so long to stop? It should be using "mode fast" which'd kick out active queries. >> Just out of curiosity, what for? I can't imagine any really >> good reason >> for just shutting down the postmaster and immediately restarting it. > why have a "restart" option if there is never a reason for it? :-) Well, there are times when you need it, like changing shared_buffers or one of the other postmaster-start-time-only parameters. But doing it just as a routine action smacks of Microsoftish "you need to reboot regularly" thinking... > seriously, this is a good question, i think this was someone's idea of a > quick way to clear any remaining DB sessions in order to be able to drop > a database and restore a newer version (this is a reporting DB that gets > refreshed nightly with a dump from another DB). Hmm. It's not exactly bulletproof --- what if someone reconnects to the DB as soon as you restart? But I guess it's a simple answer that might not be worth improving on. 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
[GENERAL] FIN_WAIT_2
hi all, I installed postgresql 8.2.3 in a freebsd server, my client application is written in C++ builder + zeoslib and I haven't any problem until now, but now with 8.2.3 version I have many connection that remains in FIN_WAIT_2, any suggest? regards, Enrico ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] WAL files, warm spares and minor versions
On Tuesday 24 April 2007 10:57, "Michael Nolan" <[EMAIL PROTECTED]> wrote: > Can WAL files be used to create/update a warm standby on a different > minor version of PostgreSQL (eg, using files from a server running 8.2.3 > on an 8.2.4 server, or vice-versa?) > > I suspect this is a FAQ, but I didn't see it in the docs on WALs and PITR I think it would just be assumed that it would not work. Even different system architectures with the same software versions won't work. -- No long, complicated contracts. No actuarial tables to pore over. Social Security operates on a very simple principle: the politicians take your money from you and squander it. ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
Marcelo de Moraes Serpa wrote: Here's what I need to do: Somehow save the user_id of the **application** user who have done the update/delete action to the log row. I've read an article on IBM's developer site which teaches how to do just that (get the application's user id and save it the audit row) using what they call the APPLICATION_ID which is an unique ID that DB2 assigns to the app when it connects to the database. Afraid there's nothing quite like that for PG. There's two ways I've used. 1. Have a separate user (role in 8.2) for each application user (it can be something like u_app_0001 etc). This is do-able for a few hundred users certainly, and should be fine for a few thousand. Not sure about hundreds of thousands though. 2. Simulate a "session variable" by having one of the procedural languages store state for you (e.g. pl/tcl or pl/perl). Call set_app_user(...) on application connect and call get_app_user() when you need to find the current app user. I've done both, but prefer the first myself. -- Richard Huxton Archonet Ltd ---(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] Audit-trail engine: getting the application's layer user_id
"Marcelo de Moraes Serpa" <[EMAIL PROTECTED]> writes: > I forgot to add the link to the article I've mentioned: > > http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b > > This is what I'd like to do on PostgreSQL, So, translating it to a simpler example: You want that your function gets the connection ID it is using and ties it to your current user ID at your application and then have all your tables use a trigger to retrieve the user name from the auxiliar table that maps "connection ID -> user", right? That's what's in that page: a UDF (user defined function) named getapplicationid() that will return the user login / name / whatever and triggers. What is preventing you from writing that? What is your doubt with regards to how create that feature on your database? -- Jorge Godoy <[EMAIL PROTECTED]> ---(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] Audit-trail engine: getting the application's layer user_id
Thank you for the replies. @Richard: I've thought about having one DB user for each APP user. However, a coworker told me that it would infeasible to do that on the web enviroment, specifically for J2EE where a DB connection pool is used, so I gave up on that. @Jorge: Is this "connection id" you say equivalent to the "applicationid" mentioned in the ibm db2 article? If so, how could I get this data through my application? On 4/24/07, Marcelo de Moraes Serpa <[EMAIL PROTECTED]> wrote: Thank you for the replies. @Richard: I've thought about having one DB user for each APP user. However, a coworker told me that it would infeasible to do that on the web enviroment, specifically for J2EE where a DB connection pool is used, so I gave up on that. @Jorge: Is this "connection id" you say equivalent to the "applicationid" mentioned in the ibm db2 article? If so, how could I get this data through my application? Marcelo. On 4/24/07, Jorge Godoy <[EMAIL PROTECTED]> wrote: > > "Marcelo de Moraes Serpa" <[EMAIL PROTECTED]> writes: > > > I forgot to add the link to the article I've mentioned: > > > > > http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze/0302stolze.html#section2b > > > > This is what I'd like to do on PostgreSQL, > > So, translating it to a simpler example: > > You want that your function gets the connection ID it is using and > ties it to your current user ID at your application and then have > all your tables use a trigger to retrieve the user name from the > auxiliar table that maps "connection ID -> user", right? > > That's what's in that page: a UDF (user defined function) named > getapplicationid() that will return the user login / name / whatever and > triggers. > > What is preventing you from writing that? What is your doubt with > regards to how create that feature on your database? > > > > -- > Jorge Godoy <[EMAIL PROTECTED]> >
Re: [GENERAL] [ADMIN] Regarding WAL
On Tue, 2007-04-24 at 09:02 +0530, Mageshwaran wrote: > Hi , > > I want to do replication using WAL , please tell the methods by which > log shipping is done ie moving the wal files to slaves and executing it. http://www.postgresql.org/docs/8.2/interactive/continuous-archiving.html -- Brad Nicholson 416-673-4106 Database Administrator, Afilias Canada Corp. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
"Marcelo de Moraes Serpa" <[EMAIL PROTECTED]> writes: > @Richard: I've thought about having one DB user for each APP user. However, > a coworker told me that it would infeasible to do that on the web > enviroment, specifically for J2EE where a DB connection pool is used, so I > gave up on that. Why? You can always "SET SESSION AUTH TO " before anything. It works with transaction pools and you can even enhance your application security by a second layer inside the database itself. DB passwords don't need to be known by users since they won't connect directly and your connection will be made with a priviledged user. > @Jorge: Is this "connection id" you say equivalent to the "applicationid" > mentioned in the ibm db2 article? If so, how could I get this data through > my application? It all depends on what you want to make it unique. I believe that a simple process will be dedicated to each connection, so if you get its PID you'll be done. Each time a user accesses the database you insert or update a record with his PID and then you make your triggers work with that. There will be more logic, but you got the idea. Another option is using the transaction ID or something that always change. You just need a unique value that lasts for a connection and isn't shared with any other user connected at the same time you are. There are a lot of functions that you can use. You just have to be sure when you want the information and what information you need. Take a look at the ones available in pg_catalog for your specific PostgreSQL version. -- Jorge Godoy <[EMAIL PROTECTED]> ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] conditional joins and views
Is it possible at all to use conditional joins in views? ie: select a.* , b.* from a inner join b ON ( (a.id = b.id) AND (a.otherfield <> ?) ) I have a few 15-20 table joins that i'd like to push into views. i've never wanted to push something with a conditional join into a view before, so am at a loss on this being a possibility. seeing little documentation on this, i'm thinking its not possible and i'll have to use a function. // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | SyndiClick.com | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] postgres on Windows: PAE and max memory
I have a server running Windows Server 2003 32-bit that has 8GB of memory. Our system administrator installed PAE (Physical Address Extensions) which I know MS SQL Server will use, but I'm not sure if PostgreSQL will. Can PostgreSQL use the memory above 2GB and 4GB? ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] conditional joins and views
Am Dienstag, 24. April 2007 21:43 schrieb Jonathan Vanasco: > Is it possible at all to use conditional joins in views? > > ie: > select a.* , b.* from a inner join b ON ( (a.id = b.id) AND > (a.otherfield <> ?) ) > > I have a few 15-20 table joins that i'd like to push into views. > i've never wanted to push something with a conditional join into a > view before, so am at a loss on this being a possibility. I think what you are looking for is a parametrized view, but that doesn't exist. One obvious solution is to put this into a function, but that might not have the best performance, depending on what sort of contexts you want to use the view in. Another option would be using prepared statements. -- Peter Eisentraut http://developer.postgresql.org/~petere/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
[GENERAL] reasonable limit to number of schemas in a database?
I'm considering re-architecting a database to make use of many, many schemas over time, it would probably grow to be on the order of 3,000 or so, with each schema holding ~100 tables. Is that an absurd amount, or well within postgres' limits? I haven't been able to find any information on what penalties one pays as you increase the schema count. ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [QUARANTINE] Re: [GENERAL] PG service restart failure (start getting ahead of stop?)
> Well, that makes sense: if the shutdown took more than a > minute then the > "stop" script action would give up waiting, and then the > "start" action > would see the postmaster running and go away happy. (It's a bit odd > that "service start" actions are supposed to treat "already running" > as OK, but I've been told that that's required by the Linux Standards > Base and I can't change it.) thanks, that's good to know. i never realized that's how it behaves. > The real question here is why'd it take so long to stop? It should be > using "mode fast" which'd kick out active queries. indeed a mystery -- from looking at the query log there didn't seem to be ANY active queries at the time. seems that > 85% of the session IDs (84 total) for which i got lines like "2007-04-23 03:05:48 PDT [26987] FATAL: the database system is shutting down" did not even have any preceding query activity. hard to debug retroactively -- it's just annoying that i don't understand what was different about this restart. and, yes, i confirmed that it is using fast mode: 212 $SU -l postgres -c "$PGENGINE/pg_ctl stop -D '$PGDATA' -s -m fast" > /dev/null 2>&1 < /dev/null george ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
"Marcelo de Moraes Serpa" <[EMAIL PROTECTED]> writes:
> Hey guys,
>
> Mine is an web application - three tier. The app connects to the db using
> only one user and it has it's own authentication system and doesn't
> rely on the database for user management.
I solved the problem using a C program and keeping all the information
in the database, that means, users, passwords and ``sessions''. Each
time a user opens a session the system register it in a table that
looks like:
auth.session
Tabla «auth.session»
Columna|Tipo |
Modificadores
---+-+
id| integer | not null default
nextval(('auth.session_sid'::text)::regclass)
skey | text| not null
agent_id | integer | not null
host | text| not null default
'localhost'::text
start_time| timestamp without time zone | not null default now()
end_time | timestamp without time zone |
su_session_id | integer |
Índices:
«session_pkey» PRIMARY KEY, btree (id)
Restricciones de llave foránea:
«$1» FOREIGN KEY (agent_id) REFERENCES auth.agent(id)
«session_su_session_id_fkey» FOREIGN KEY (su_session_id) REFERENCES
auth."session"(id)
Each time the application gets a connection from the pool it sets the
session id of that user in a static variable (that was the tricky
part) using a function set_session_id and then you can retrieve it
using another function current_session_id (nowadays I think that can
be done without C at all but using the new GUC infrastructure
). So you can put in your log table something like:
session_id int not null default current_session_id() references
auth.session(id),
Finally before returning the connection to the pool the application
resets the session id of that user using reset_session_id. The code
is:
#include "postgres.h"
#include
#include
#include
#include
#include "fmgr.h"
static int session_id = 0;
static int session_id_is_set = false;
Datum set_session_id(PG_FUNCTION_ARGS);
Datum current_session_id(PG_FUNCTION_ARGS);
Datum reset_session_id(PG_FUNCTION_ARGS);
PG_FUNCTION_INFO_V1(set_session_id);
PG_FUNCTION_INFO_V1(current_session_id);
PG_FUNCTION_INFO_V1(reset_session_id);
Datum
set_session_id(PG_FUNCTION_ARGS) {
session_id = PG_GETARG_INT32(0);
session_id_is_set = true;
PG_RETURN_INT32(session_id);
}
Datum
current_session_id(PG_FUNCTION_ARGS) {
if (! session_id_is_set)
PG_RETURN_NULL();
PG_RETURN_INT32(session_id);
}
Datum
reset_session_id(PG_FUNCTION_ARGS) {
session_id_is_set = false;
PG_RETURN_BOOL(1);
}
Hope that helps.
Regards,
Manuel.
---(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] Additional debugging of idle sessions?
I am experiencing a blocking situation in my database (Postgresql 8.1.8 on Redhat). When I do a "ps -ef|grep postgres" I see two processing that are "idle in transaction" and they stay there indefinitely. I'm running JBoss (the problem is in JBossMQ) and I'm having trouble pinpointing the location/cause of the block as JBoss doesn't report any errors or exceptions. It just sits there. Is there any way to increase the debug logging or issue a query so that I could find the actual select statements that are idle? That would help me isolate the source of my problem. Thanks ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] copy or create table for data logging?
guys, i want to log some data everyday. for this i can do 1 of 2 things: 1) i can create an empty 'template' table and copy it everyday before data logging including defaults. # including constraints (thanks greg) doesn't work but more on that later. helps- i avoid spending more time on lengthy domain creation everyday (??). hurts- i have to share sequences between everyday tables (i can do away with sequences but not sure on that). 2) i can simply create an identical table everyday and go on with data logging. helps- hmm, the smell of fresh new sequences everyday! hurts- i spend more time on creation (??). either way i get the job done but what would you recommend? no inheritance please since it couples and complicates. using 8.1.4. jzs http://developer.postgresql.org/pgdocs/postgres/sql-createtable.html ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
Re: [GENERAL] Audit-trail engine: getting the application's layer user_id
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Marcelo de Moraes Serpa Sent: dinsdag 24 april 2007 21:06 To: pgsql-general@postgresql.org Subject: Re: [GENERAL] Audit-trail engine: getting the application's layer user_id Thank you for the replies. @Richard: I've thought about having one DB user for each APP user. However, a coworker told me that it would infeasible to do that on the web enviroment, specifically for J2EE where a DB connection pool is used, so I gave up on that. As Richard mentioned, he has done it. Remember, for the pool you DO NOT setup a new connection every time but you can certainly utilize the pool. The trick is the postgresql idea of the Role-Based Access Control (RBAC) implementation. I.e. you can just do a SET LOCAL ROLE . After transaction commit or rollback, or execution of SET LOCAL ROLE NONE or RESET ROLE you will have your original role (own user) again. This should work just fine. See also: http://www.postgresql.org/docs/8.1/interactive/sql-set-role.html @Jorge: Is this "connection id" you say equivalent to the "applicationid" mentioned in the ibm db2 article? If so, how could I get this data through my application? On 4/24/07, Marcelo de Moraes Serpa <[EMAIL PROTECTED]> wrote: Thank you for the replies. @Richard: I've thought about having one DB user for each APP user. However, a coworker told me that it would infeasible to do that on the web enviroment, specifically for J2EE where a DB connection pool is used, so I gave up on that. @Jorge: Is this "connection id" you say equivalent to the "applicationid" mentioned in the ibm db2 article? If so, how could I get this data through my application? Marcelo. On 4/24/07, Jorge Godoy <[EMAIL PROTECTED]> wrote: "Marcelo de Moraes Serpa" <[EMAIL PROTECTED]> writes: > I forgot to add the link to the article I've mentioned: > > http://www-128.ibm.com/developerworks/db2/library/techarticle/0302stolze /0302stolze.html#section2b > > This is what I'd like to do on PostgreSQL, So, translating it to a simpler example: You want that your function gets the connection ID it is using and ties it to your current user ID at your application and then have all your tables use a trigger to retrieve the user name from the auxiliar table that maps "connection ID -> user", right? That's what's in that page: a UDF (user defined function) named getapplicationid() that will return the user login / name / whatever and triggers. What is preventing you from writing that? What is your doubt with regards to how create that feature on your database? -- Jorge Godoy <[EMAIL PROTECTED] >
Re: [GENERAL] Mass Update
Roger, This statement runs but the table isnt actually updated. Slightly odd. I ran the subquery, which makes perfect sense and nothing was returned. If I remove the AND temp.id <> junctions.id part I get loads of results returned but if I run the update with the the adjusted subquery I get ERROR: more than one row returned by a subquery used as an expression. Dont quite know what is going wrong. Thanks alot Iain ---(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] can't start tsearch2 in 8.2.4
On Apr 23, 12:31 am, [EMAIL PROTECTED] (Tom Lane) wrote: > [EMAIL PROTECTED] writes: > > When I try to initiate tsearch2 in 8.2.4, I got the following error. > > ERROR: incompatible library "/usr/local/pgsql/lib/tsearch2.so": > > missing magic block > > Apparently you have a pre-8.2 version installed in /usr/local. > Update. > > regards, tom lane > > ---(end of broadcast)--- > TIP 3: Have you checked our extensive FAQ? > >http://www.postgresql.org/docs/faq Thank you for your help. You're absolutely correct. I cleaned up all the 8.1 and got a lot further. After runing psql dbname < tsearch2.sql, there are many SETs, then ... SET psql:tsearch2.sql:7: tsearch2.sql: Too many open files RESET client_min_messages; RESET --tsvector SELECT '1'::tsvector; psql:tsearch2.sql:12: ERROR: type "tsvector" does not exist LINE 1: SELECT '1'::tsvector; ^ ... Any ides about "Too many open files? It never happen to me in the past and I don't know how many times I have successfully done this in the past. A side note, I noticed, I have to run the SQL script int the sql directory, i.e. I can't do psql dbname < /dirname/tsearch2.sql Regards ---(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] can't start tsearch2 in 8.2.4
On Apr 23, 2:10 am, [EMAIL PROTECTED] (Martijn van Oosterhout) wrote: > On Mon, Apr 23, 2007 at 12:31:32AM -0400, Tom Lane wrote: > > [EMAIL PROTECTED] writes: > > > When I try to initiate tsearch2 in 8.2.4, I got the following error. > > > ERROR: incompatible library "/usr/local/pgsql/lib/tsearch2.so": > > > missing magic block > > > Apparently you have a pre-8.2 version installed in /usr/local. > > Update. > > Perhaps we should add a HINT message with something to the effect of > "Perhaps it was compiled for an older version". > > Have a nice day, > -- > Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > > > From each according to his ability. To each according to his ability to > > litigate. > > > > signature.asc > 1KDownload If I posted multiple times, please forgive me. Just did not see it go through for a while. Thank you for your help. You're absolutely correct. I cleaned up all the 8.1 and got a lot further. After runing psql dbname < tsearch2.sql, there are many SETs, then ... SET psql:tsearch2.sql:7: tsearch2.sql: Too many open files RESET client_min_messages; RESET --tsvector SELECT '1'::tsvector; psql:tsearch2.sql:12: ERROR: type "tsvector" does not exist LINE 1: SELECT '1'::tsvector; ^ ... Any ides about "Too many open files? It never happen to me in the past and I don't know how many times I have successfully done this in the past. A side note, I noticed, I have to run the SQL script int the sql directory, i.e. I can't do psql dbname < /dirname/tsearch2.sql Regards ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] can't start tsearch2 in 8.2.4
On Apr 23, 4:11 pm, [EMAIL PROTECTED] wrote: > On Apr 23, 2:10 am, [EMAIL PROTECTED] (Martijn van Oosterhout) wrote: > > > > > On Mon, Apr 23, 2007 at 12:31:32AM -0400, Tom Lane wrote: > > > [EMAIL PROTECTED] writes: > > > > When I try to initiate tsearch2 in 8.2.4, I got the following error. > > > > ERROR: incompatible library "/usr/local/pgsql/lib/tsearch2.so": > > > > missing magic block > > > > Apparently you have a pre-8.2 version installed in /usr/local. > > > Update. > > > Perhaps we should add a HINT message with something to the effect of > > "Perhaps it was compiled for an older version". > > > Have a nice day, > > -- > > Martijn van Oosterhout <[EMAIL PROTECTED]> http://svana.org/kleptog/ > > > > From each according to his ability. To each according to his ability to > > > litigate. > > > signature.asc > > 1KDownload > > If I posted multiple times, please forgive me. Just did not see it go > through for a while. > > Thank you for your help. > > You're absolutely correct. I cleaned up all the 8.1 and got a lot > further. > After runing psql dbname < tsearch2.sql, there are many SETs, then > > ... > SET > psql:tsearch2.sql:7: tsearch2.sql: Too many open files > RESET client_min_messages; > RESET > --tsvector > SELECT '1'::tsvector; > psql:tsearch2.sql:12: ERROR: type "tsvector" does not exist > LINE 1: SELECT '1'::tsvector; > ^ > ... > > Any ides about "Too many open files? It never happen to me in the > past and I don't know how many times I have successfully done this in > the past. > > A side note, I noticed, I have to run the SQL script int the sql > directory, i.e. I can't do psql dbname < /dirname/tsearch2.sql > > Regards It turns out in the current version, tsearch2.sql is in the contrib/ tsearch2/sql but you can't run the script in that directory, you still have to run it in contrib/tsearch2, as psql dbname < sql/tsearch2.sql. Otherwise it runs out of file handles with recursive inclusion. Thank. ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[GENERAL] ERROR: variable not found in subplan target lists
In running a query I receive the error: ERROR: variable not found in subplan target lists The version is 8.1.5 I can find references to this message. Seems there was a bug in older versions. Should I be finding this in 8.1.5? Any advice? Thank you and much appreciated. Mark ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] [ADMIN] Postgresql Help
On Monday 23 April 2007 01:21, Mageshwaran wrote: > Hi , > > I am new to postgresql, I have been assigned a task of upgrading > postgresql to higher version. > In our company we are using 7.1.1,7.1.3,7.4.5 versions of postgresql, > can you please tell me which is the stable and suitable version in 8.x > version. > If you plan to do your upgrades within the next 4 months, you'll want to target 8.2.4. If you plan to upgrade after that, then 8.3 is the version you'll want (it is currently expected to be released around July). You should be aware that upgrading from 7.1.x versions is likely to be a somewhat intensive process since, while it should be backwards compatible, recent versions of pg_dump almost certainly hasn't recieved much testing against 7.1.x versions. (I'd almost recommend upgrading to an intermediate version like 7.4.x, but it probably isn't worth it... just make sure that you keep a copy of your 7.1 databases running until your done testing, and make sure to test your applications thoroughly. Also don't forget that any applications /drivers/ libraries that are built against 7.1 should be updated / recompiled against newer versions as well. HTH. -- Robert Treat Database Architect OmniTI Technology Firm http://www.omniti.com/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
[GENERAL] query from a list of ids
Hi, Here is my first table: Table1 name| ids - Peter| 2, 3, 4, 5 Jack| 100, 34, 3 Both name and ids are in text format. Here is my second table Table2 id | Flag | Title - 2 | Red| good 3 | Blue | poor 4 | Green| middle id is in integer (serial) format. I would like to list all the rows in table 2 where the id is in the ids field of peter. So I did select * from tables where id in (select ids from table1 where name='Peter') It did not work. How can I do the query? Thanks, ff ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [GENERAL] Slow query using simple equality operators
Hi, Apparently, the amount of free space on the partition makes a big difference in performance. I went from about 30% free space to about 5% and this triggered the performance issues. As soon as freed up the drive to about 30% free space again the performance issues went away. Benjamin On Apr 24, 2007, at 1:12 AM, Alban Hertroys wrote: Benjamin Arai wrote: Hi, I upgraded to 8.2.4 but there was no significant change in performance. I did notice that hte query appears to be executed incorrectly. I have pasted the EXPLAIN ANALYZE below to illustrate: =# explain analyze select s_content,textdir from (SELECT * from text_search WHERE tb_id='P2_TB1') AS a where path_id='4'; What's wrong with a plain select * from text_search where tb_id='P2_TB1' and path_id=4; ? You posted the explain output of something like that earlier, but that was on an older pg 8 and without analyze. QUERY PLAN - - --- Bitmap Heap Scan on text_search (cost=39864.98..59746.59 rows=5083 width=36) (actual time=7418.651..7418.863 rows=52 loops=1) Recheck Cond: ((path_id = 4) AND (tb_id = 'P2_TB1'::text)) -> BitmapAnd (cost=39864.98..39864.98 rows=5083 width=0) (actual time=6706.928..6706.928 rows=0 loops=1) -> Bitmap Index Scan on idx_search_path_id (cost=0.00..16546.34 rows=1016571 width=0) (actual time=6609.458..6609.458 rows=52777 loops=1) The row estimates are off by a factor 20 (~1M rows estimated, 50k rows actual). Are you sure you analyze'd recently? -- Alban Hertroys [EMAIL PROTECTED] magproductions b.v. T: ++31(0)534346874 F: ++31(0)534346876 M: I: www.magproductions.nl A: Postbus 416 7500 AK Enschede // Integrate Your World // ---(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] reasonable limit to number of schemas in a database?
Ben <[EMAIL PROTECTED]> writes: > I'm considering re-architecting a database to make use of many, many > schemas over time, it would probably grow to be on the order of 3,000 > or so, with each schema holding ~100 tables. Is that an absurd amount, or > well within postgres' limits? I haven't been able to find any information > on what penalties one pays as you increase the schema count. The number of schemas doesn't scare me so much as the number of tables. Are you using a filesystem that can cope gracefully with 300K files in one directory? How many of these tables do you anticipate any one session touching? (That last translates to catalog cache and lock table space...) Generally, when someone proposes a scheme like this, they are thinking that N identical tables are somehow better than one table with an additional key column. The latter is usually the better design, unless you have special requirements you didn't mention. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [GENERAL] Additional debugging of idle sessions?
Mike Goldner <[EMAIL PROTECTED]> writes: > Is there any way to increase the debug logging or issue a query so that > I could find the actual select statements that are idle? Huh? "Idle in transaction" means there *isn't* any active SQL statement --- those backends are waiting for the next client command. What you seem to need is more visibility into the state of your client code, which I'm afraid we can't help you with. Possibly you could get useful help on a JBoss-related list. 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
Re: [GENERAL] ERROR: variable not found in subplan target lists
Angva <[EMAIL PROTECTED]> writes: > In running a query I receive the error: > ERROR: variable not found in subplan target lists Please provide a complete test case. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] query from a list of ids
am Tue, dem 24.04.2007, um 14:19:05 -0700 mailte finecur folgendes:
> Hi,
>
> Here is my first table:
>
> Table1
>
> name| ids
> -
> Peter| 2, 3, 4, 5
> Jack| 100, 34, 3
>
> Both name and ids are in text format.
>
> Here is my second table
>
> Table2
>
> id | Flag | Title
> -
> 2 | Red| good
> 3 | Blue | poor
> 4 | Green| middle
>
> id is in integer (serial) format.
>
> I would like to list all the rows in table 2 where the id is in the
> ids field of peter. So I did
>
> select * from tables where id in (select ids from table1 where
> name='Peter')
>
> It did not work. How can I do the query?
Broken design, you should normalise your schema.
Okay, let me try a solution:
First, create a function:
create or replace function ids(text) returns setof t2 as $$
declare
_idstext;
_sqltext;
_recrecord;
begin
select into _ids ids from t1 where name = $1;
_sql := 'select * from t2 where id in (' || coalesce(_ids,'NULL') ||
');';
for _rec in execute _sql loop
return next _rec;
end loop;
end;
$$ language plpgsql;
Hint: the coalesce avoid errors if there are no result.
Okay, we have 2 tables:
test=*# select * from t1;
name |ids
---+
Peter | 2, 3, 4, 5
Jack | 100, 34, 3
(2 rows)
test=*# select * from t2;
id | flag | title
+---+
2 | Red | good
3 | Blue | poo
4 | Green | middle
(3 rows)
test=*# select * from ids('Peter');
id | flag | title
+---+
2 | Red | good
3 | Blue | poo
4 | Green | middle
(3 rows)
test=*# select * from ids('Jack');
id | flag | title
+--+---
3 | Blue | poo
(1 row)
test=*# select * from ids('nobody');
id | flag | title
+--+---
(0 rows)
Andreas
--
Andreas Kretschmer
Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] reasonable limit to number of schemas in a database?
am Wed, dem 25.04.2007, um 1:14:15 -0400 mailte Tom Lane folgendes: > Ben <[EMAIL PROTECTED]> writes: > > I'm considering re-architecting a database to make use of many, many > > schemas over time, it would probably grow to be on the order of 3,000 > > or so, with each schema holding ~100 tables. Is that an absurd amount, or > > well within postgres' limits? I haven't been able to find any information > > on what penalties one pays as you increase the schema count. > > The number of schemas doesn't scare me so much as the number of tables. > Are you using a filesystem that can cope gracefully with 300K files in > one directory? How many of these tables do you anticipate any one http://people.planetpostgresql.org/greg/index.php?/archives/37-The-million-table-challenge.html ;-) Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org/
[GENERAL] Bitmap Scan Pages
Hello, Is there a way to know how many pages were hit by a Bitmap Heap scan ? For instance : Bitmap Heap Scan on posts (cost=56.71..295.24 rows=2123 width=67) (actual time=0.575..1.462 rows=2160 loops=1) I'd like to know if it hit 2160 pages (ie I should really run CLUSTER) or 50 pages (ie. my table is well clustered, everything cool). Since, when it's cached, it's so fast anyway I cant' tell the difference, but when it's not cached, it is important. This would give an interesting probe for EXPLAIN ANALYZE tuning... ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
