Re: [HACKERS] Options for growth
D'Arcy J.M. Cain said: On Thursday 16 January 2003 11:59, Adrian 'Dagurashibanipal' von Bidder wrot e: On Thu, 2003-01-16 at 17:42, D'Arcy J.M. Cain wrote: We are also looking at hardware solutions, multi-CPU PCs with tons (24GB ) of memory. I know that memory will improve access if it prevents swapping but how well does PostgreSQL utilize multiple CPUs? At most one CPU is used for any single postgres backend (that means for any single database connection). So, if your load problem is single queries being too slow, thee's nothing you can do with adding more CPUs. If your problem is many connections maxing out the db, PostgreSQL can take full advantage of multiple CPUs. I most definitely have multiple queries running at once. My main issue is whether PostgreSQL scales up properly or does it get bogged down with too many locked queries. That would depend on the OS. Not many 'pc-based unix' support over 4 GB of memory, some don't even go that far. If memory is an issue, have you considered going to 64bit CPU? Memory is indeed an issue for a complex database setup, especially if you want to give the backends enough shared and sort memory. As already said, PostgreSQL will utilize multiple CPUs - as effectively as your OS can do this of course. PostgreSQL is not an OS by itself and does not really control these resources. I have also found it very helpful to split database from application servers (wish I do it as often as I recommend it :) - thus you can optimize the part that needs most resources.. In many cases the requirements are quite different. With todays gigabit LANs, bandwidth between machines shouldn't be an issue. By the way, I too wonder which supported OS platform would support over 4GB of memory on a PC.. Daniel ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
[HACKERS] Foreign key wierdness
Hi all, A pgAdmin user has noticed that Foreign Keys take significantly longer to create when migrating a database in pgAdmin in v1.4.12 than in v1.4.2. The only difference in the migration code between these releases, is that pgAdmin now qualifies tables names with the schema name. The following log snippets are from identical migrations from an Access database to PostgreSQL 7.3.1: pgAdmin 1.4.12 == Jan 19 16:34:26 lmb042 postgres[17986]: [78991-1] LOG: query: ALTER TABLE public.articles ADD CONSTRAINT productsarticles_fk FOREIGN KEY(product_ref) REFERENCES public.products Jan 19 16:34:26 lmb042 postgres[17986]: [78991-2] (product_id) ON DELETE CASCADE ON UPDATE CASCADE Jan 19 16:34:26 lmb042 postgres[17986]: [78992] NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) Jan 19 16:34:26 lmb042 postgres[17986]: [78993] LOG: query: SELECT 1 FROM ONLY public.products x WHERE product_id = $1 FOR UPDATE OF x Jan 19 16:38:33 lmb042 postgres[17986]: [78994] LOG: duration: 247.585771 sec pgAdmin 1.4.2 = Jan 19 15:48:56 lmb042 postgres[17542]: [78991-1] LOG: query: ALTER TABLE articles ADD CONSTRAINT productsarticles_fk FOREIGN KEY(product_ref) REFERENCES products (product_id) Jan 19 15:48:56 lmb042 postgres[17542]: [78991-2] ON DELETE CASCADE ON UPDATE CASCADE Jan 19 15:48:56 lmb042 postgres[17542]: [78992] NOTICE: ALTER TABLE will create implicit trigger(s) for FOREIGN KEY check(s) Jan 19 15:48:56 lmb042 postgres[17542]: [78993] LOG: query: SELECT 1 FROM ONLY public.products x WHERE product_id = $1 FOR UPDATE OF x Jan 19 15:48:58 lmb042 postgres[17542]: [78994] LOG: duration: 1.988144 sec A similar select query follows every key creation, but is not issued by pgAdmin, or the user's application(s), so I assume PostgreSQL is doing it for some reason. Any ideas why it takes so long (for the same data) when pgAdmin qualifies the table name in the ALTER statement? Thanks, Dave. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [HACKERS] Options for growth
[no cc:s please] On Mon, 2003-01-20 at 10:31, Daniel Kalchev wrote: D'Arcy J.M. Cain said: On Thursday 16 January 2003 11:59, Adrian 'Dagurashibanipal' von Bidder wrot e: On Thu, 2003-01-16 at 17:42, D'Arcy J.M. Cain wrote: We are also looking at hardware solutions, multi-CPU PCs with tons (24GB ) of memory. I know that memory will improve access if it prevents swapping but how well does PostgreSQL utilize multiple CPUs? At most one CPU is used for any single postgres backend (that means for any single database connection). So, if your load problem is single queries being too slow, thee's nothing you can do with adding more CPUs. If your problem is many connections maxing out the db, PostgreSQL can take full advantage of multiple CPUs. I most definitely have multiple queries running at once. My main issue is whether PostgreSQL scales up properly or does it get bogged down with too many locked queries. That would depend on the OS. Not many 'pc-based unix' support over 4 GB of memory, some don't even go that far. By the way, I too wonder which supported OS platform would support over 4GB of memory on a PC.. Linux? I don't think there's any problem handling more than 4G memory in the system. On 32bit architectures, there's of course the 3G (I think) per process limit, but as postgres uses multiprocess and not multithreading, this issue doesn't hit so soon. Of course, if the per process memory is the problem, you'd have to go to 64bit. cheers -- vbi -- featured link: http://fortytwo.ch/gpg/intro signature.asc Description: This is a digitally signed message part
Re: [HACKERS] Foreign key wierdness
Dave Page [EMAIL PROTECTED] writes: A pgAdmin user has noticed that Foreign Keys take significantly longer to create when migrating a database in pgAdmin in v1.4.12 than in v1.4.2. The only reason ADD FOREIGN KEY would take a long time is if (a) it has to wait awhile to get exclusive lock on either the referencing or referenced table; and/or (b) it takes a long time to verify that the existing entries in the referencing table all have matches in the referenced table. (that's the behind-the-scenes query you see) I'm betting that the table was busy, or there was a lot more data present in the one case, or you hadn't ever vacuumed/analyzed one or both tables and so a bad plan was chosen for the verification query. The schema reference is definitely not the issue. regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Foreign key wierdness
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 20 January 2003 15:28 To: Dave Page Cc: PostgreSQL Hackers Mailing List; Didier Moens Subject: Re: [HACKERS] Foreign key wierdness Dave Page [EMAIL PROTECTED] writes: A pgAdmin user has noticed that Foreign Keys take significantly longer to create when migrating a database in pgAdmin in v1.4.12 than in v1.4.2. The only reason ADD FOREIGN KEY would take a long time is if (a) it has to wait awhile to get exclusive lock on either the referencing or referenced table; and/or (b) it takes a long time to verify that the existing entries in the referencing table all have matches in the referenced table. (that's the behind-the-scenes query you see) I'm betting that the table was busy, or there was a lot more data present in the one case, or you hadn't ever vacuumed/analyzed one or both tables and so a bad plan was chosen for the verification query. The schema reference is definitely not the issue. Thing is Tom, this issue can be reproduced *every* time, without fail. The difference is huge as well, it's a difference of a couple of seconds, the total migration will take around 1704.67 seconds without schema qualification, and 11125.99 with schema qualification to quote one test run. As I understand it, this has be tried on a test box, and a production box (running RedHat builds of 7.3.1), and is a migration of the same source Access database. I've been looking at his for some time now (couple of weeks or more), and the only thing I can find is the SELECT ... FOR UPDATE in the PostgreSQL logs that I quoted. These exactly follow *every* fkey creation, and are definately not issued by pgAdmin. If they were issued by another app or user, how come they exactly follow each fkey creation, and are on the reference table of the fkey? Regards, Dave. ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Foreign key wierdness
Dave Page [EMAIL PROTECTED] writes: Thing is Tom, this issue can be reproduced *every* time, without fail. And have you vacuumed or analyzed yet? Or possibly you are short an index or two (you really need indexes on both the referencing and referenced columns). I've been looking at his for some time now (couple of weeks or more), and the only thing I can find is the SELECT ... FOR UPDATE in the PostgreSQL logs that I quoted. These exactly follow *every* fkey creation, and are definately not issued by pgAdmin. No, I told you: those are the internal verification query (it comes from RI_FKey_check_ins(), if you want to look). If you really think the schema qualification has something to do with it, try issuing the ADD FOREIGN KEY command manually in psql, with and without schema name. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Foreign key wierdness
On Mon, 2003-01-20 at 15:47, Dave Page wrote: -Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 20 January 2003 15:28 To: Dave Page Cc: PostgreSQL Hackers Mailing List; Didier Moens Subject: Re: [HACKERS] Foreign key wierdness Dave Page [EMAIL PROTECTED] writes: A pgAdmin user has noticed that Foreign Keys take significantly longer to create when migrating a database in pgAdmin in v1.4.12 than in v1.4.2. The only reason ADD FOREIGN KEY would take a long time is if (a) it has to wait awhile to get exclusive lock on either the referencing or referenced table; and/or (b) it takes a long time to verify that the existing entries in the referencing table all have matches in the referenced table. (that's the behind-the-scenes query you see) I'm betting that the table was busy, or there was a lot more data present in the one case, or you hadn't ever vacuumed/analyzed one or both tables and so a bad plan was chosen for the verification query. The schema reference is definitely not the issue. Thing is Tom, this issue can be reproduced *every* time, without fail. The difference is huge as well, it's a difference of a couple of seconds, the total migration will take around 1704.67 seconds without schema qualification, and 11125.99 with schema qualification to quote one test run. can you try running ANALYZE (or VACUUM ANALYZE) after importing data but before creating the foreign keys ? As I understand it, this has be tried on a test box, and a production box (running RedHat builds of 7.3.1), and is a migration of the same source Access database. I've been looking at his for some time now (couple of weeks or more), and the only thing I can find is the SELECT ... FOR UPDATE in the PostgreSQL logs that I quoted. does this SELECT ... FOR UPDATE occur only when schemas are used ? These exactly follow *every* fkey creation, and are definately not issued by pgAdmin. If they were issued by another app or user, how come they exactly follow each fkey creation, and are on the reference table of the fkey? I think Tom was trying to tell that the backend code indeed runs this, but that it should not be that slow. -- Hannu Krosing [EMAIL PROTECTED] ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [HACKERS] Foreign key wierdness
-Original Message- From: Tom Lane [mailto:[EMAIL PROTECTED]] Sent: 20 January 2003 16:08 To: Dave Page Cc: PostgreSQL Hackers Mailing List; Didier Moens Subject: Re: [HACKERS] Foreign key wierdness Dave Page [EMAIL PROTECTED] writes: Thing is Tom, this issue can be reproduced *every* time, without fail. And have you vacuumed or analyzed yet? Or possibly you are short an index or two (you really need indexes on both the referencing and referenced columns). Didier? I've been looking at his for some time now (couple of weeks or more), and the only thing I can find is the SELECT ... FOR UPDATE in the PostgreSQL logs that I quoted. These exactly follow *every* fkey creation, and are definately not issued by pgAdmin. No, I told you: those are the internal verification query (it comes from RI_FKey_check_ins(), if you want to look). Sorry, brain hiccup. If you really think the schema qualification has something to do with it, try issuing the ADD FOREIGN KEY command manually in psql, with and without schema name. Well to be honest I'm having a hard time believing it, but having looked at this in some depth, it's the only thing that the 2 versions of pgAdmin are doing differently. Even the PostgreSQL logs agree with that. I'm relying on Didier for test results though as I don't have a test system I can use for this at the moment. But it gives us something to try - Didier can you create a new database please, and load the data from 2 tables. VACUUM ANALYZE, then add the foreign key in psql using the syntax 1.4.2 uses. Then drop the database, and load exactly the same data in the same way, VACUUM ANALYZE again, and create the fkey using the qualified tablename syntax. Thanks, Dave. ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Survey results from the PostgreSQL portal page
Gavin Sherry wrote: On Mon, 20 Jan 2003, Christopher Kings-Lynne wrote: I wonder why people ask for better documentation. I think the documentation is really good. Ever read Oracle stuff? *ugh*. Ever read MySQL docs - *hack*!! The documentation definately needs work -- particularly client library documentation and PL/PgSQL. I want to work on this when I get time. Case in point : in 7.3, the ODBC driver documentation (which was terse and somewhat outdated, to begin with ...) has disappeared from the main tree. You have to go to GBorg to find (some) relevant information (and no examples, BTW). But to find an information I really needed, I had to use ... the driver source, fer Crissakes !! I felt back in '74, when I tried to learn Fortran. [ BTW : note to Hiroshi Inoue : Thank you ! I partially solved by problem, and think a real solution is bound to to undoing some 7.2 to 7.3 modifications ...] The same could be said of the JDBC driver, btw, while it's doc is still in the main doc tree. This one is one of my pet peeves at the moment ... Emmanuel Charpentier ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Foreign key wierdness
-Original Message- From: Hannu Krosing [mailto:[EMAIL PROTECTED]] Sent: 20 January 2003 18:05 To: Dave Page Cc: Tom Lane; PostgreSQL Hackers Mailing List; Didier Moens Subject: Re: [HACKERS] Foreign key wierdness I've been looking at his for some time now (couple of weeks or more), and the only thing I can find is the SELECT ... FOR UPDATE in the PostgreSQL logs that I quoted. does this SELECT ... FOR UPDATE occur only when schemas are used ? No, in both case, just significantly more slowly with schema qualification. Note that the qualification is added to the ALTER TABLE statement though - PostgreSQL's qualifying the name in the SELECT for every case. Regards, Dave. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[HACKERS] Grant options
I am implementing the grant option feature which enables an object owner to allows others to re-grant privileges. For REVOKE you can specify CASCADE and privileges granted in this manner are revoked recursively. Currently, I have made it so that you can only give grant options to users, not groups. The problem is that when a user has granted privileges having had the grant option through a group and is later removed from the group then the privileges should be revoked, but the old problem is that it's not possible to do this in all databases. I have extended the aclitem external format as follows: grantee=a*bc*/grantor means the a and c privileges are held with grant option (the letters are just examples), and the whole thing was granted by the given grantor. (You can hold the same privilege many times granted by different users.) What are the requirements for backward compatibility here? If the * are missing then the privilege is held without grant option which is currently the default. If the /grantor portion is missing then it's assumed to be equivalent to the grantee. This makes sense in a limited number of cases. One would like to have the object owner as the default but the aclitemout function doesn't have information about that. I noted three undocumented SQL function operating on ACLs: aclinsert, aclremove, aclcontains. What are those intended for? How should they maintain the integrity of the ACL that is ensured by cascading revoke? In order to query the availability of a grant option I would like to extend the has_foo_privilege family of functions so that they can take as the privilege type argument, say, 'UPDATE WITH GRANT OPTION' instead of 'UPDATE'. In order to be able to represent the grantee/grantor relationship in the information schema I also need a function has_foo_privilege_granted_by(grantee, objectid, priv, grantor). Comments? -- Peter Eisentraut [EMAIL PROTECTED] ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Win32 port patches submitted
Hi, I just submitted the patches for the native Win32 port of v7.2.1 on the patches mailing list. If you are not subscribed to the patches list you can download them from http://www.janwieck.net/win32_port Jan -- #==# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #== [EMAIL PROTECTED] # ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] Foreign key wierdness
Didier Moens [EMAIL PROTECTED] writes: I just formally tested on PostgreSQL 7.2.2 (logs sent to Dave), and the results are perfectly inline with those from 7.2.3 : a massive slowdown when upgrading from pgadminII 1.4.2 to 1.4.12. I thought the complaint involved PG 7.3? There is no schema syntax in 7.2. regards, tom lane ---(end of broadcast)--- TIP 3: 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
[HACKERS] pg_dump ordering
Hey Peter, I remember a while back you were saying you were working on pg_dump object ordering? What happened with that? Did you need some help with it? I ask because my 7.2 to 7.3 upgrade is making me cry and I want to prevent future pain... Chris ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [HACKERS] Can we revisit the thought of PostgreSQL 7.2.4?
On Sat, 18 Jan 2003, Tom Lane wrote: PS: I'm not taking a position on Justin's suggestion that there should be a 7.2.4. Marc and Bruce would be the ones who have to do the work, so they get to make the decision... I have no problems creating one ... Bruce? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
[HACKERS] Recent initdb error
setting privileges on built-in objects... ok creating information schema... sed: 1: s/^[0-9]*\.[0-9]*\.\([0 ...: undefined label 'L;s/.*//;q;: L;s/.*\(\)$/\1/' ok vacuuming database template1... ok Seems to have appeared with the changes for the SQL_FEATURES table. The below works: micro_version=`echo $VERSION | sed 's/^[0-9]*\.[0-9]*\.\([0-9]*\).*/\1/;t LABEL s/.*//;q :LABEL s/.*\(\)$/\1/'` For some reason my sed isn't liking the semicolons. -- Rod Taylor [EMAIL PROTECTED] PGP Key: http://www.rbt.ca/rbtpub.asc signature.asc Description: This is a digitally signed message part
