Re: [GENERAL] Building PG 8.4.1 with ossp-uuid on Centos 5.3
On Sat, 2009-10-10 at 22:47 -0400, Tom Lane wrote: > Hmph. I don't know if there's more than one uuid package in the wild, > but I see from > http://cvs.fedoraproject.org/viewvc/rpms/uuid/devel/uuid.spec?revision=1.17&view=markup > that the package that's standard in recent Fedora is uuid 1.6.1 from > http://www.ossp.org/pkg/lib/uuid/ > and I can attest that Postgres does build against that. FWIW, uuid RPMs are in EPEL repository: http://download.fedora.redhat.com/pub/epel/5/i386/repoview/letter_u.group.html http://download.fedora.redhat.com/pub/epel/5/x86_64/repoview/letter_u.group.html -- Devrim GÜNDÜZ, RHCE Command Prompt - http://www.CommandPrompt.com devrim~gunduz.org, devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] Cannot upgrade to 8.4.1 on Windows
Hello Bill, some words to the installation of PostgreSQL on Windows: There are 3 user accounts involved: a) the Administrator account (or user with Administration privilege) - This user is running the installer. It should have Administrator privileges, because only Admins are allowed to register Services and enter the necessary registry keys. b) the windows user account, under which PostgreSQL service will run. The default name for this account is "postgres". This is a windows user account with very limited privileges; BUT it must have the "logon as service" privilege. The PostgreSQL installers do create this account for you. The installers also change the ownership of the PostgreSQL data folder, so that the windows user postgres can access those data files. You should never need to logon as this user. c) the PostgreSQL superuser WITHIN the database (that's nothing with windows). Unfortunately its default name is also postgres Your problems circle around a) and b). First: the installer should be run as an user with Admin privs. Second: it is not a good idea to give admin privs to the postgresql user. pg_ctl start now actively drops admin privs within its code, but it is not in good taste (and a potential source of trouble) to give admin to postgres(windows user) To "clean up" your system I recommend the following steps (note: that is NOT the normal path of upgrade, but since you did some things you should not do...) a) pg_dump all your databases from PostgreSQL 8.3 (or use pg_dumpall) a1) perhaps check if those dumps are loadable, i.e. that you called pg_dump with the correct parameters to do what you want it to do, and that you dumped the databases are the ones you wanted to dump b) store those dumpfiles well c) make a backup-copy of the data-directory of PostgreSQL 8.3 (not necessary, just a double security measure) d) uninstall PostgreSQL 8.3 [as admin user] e) remove the windows user postgres (net user postgres /delete or via useradmin) f) reboot [not strictly necessary, but I learned it helps with windows] g) make sure that there is not postgres service running [it cannot be, as of d) and e), this is just a check that d) and e) worked] h) install PostgreSQL 8.4.x, while logged on as a user with admin privileges i) make sure the /postgresql/8.4/bin is added to your path j) load your dumps from step a) k) enjoy PostgreSQL Again: this is not the normal way, it is advised to install PostgreSQL 8.4 "in parallel" and use the 8.4 pg_dump to export the 8.3 databases; but as there are some misinstallations on the windows level on your system, this receipt has a higher likelyhood to work. best wishes, Harald -- GHUM Harald Massa persuadere et programmare Harald Armin Massa Spielberger Straße 49 70435 Stuttgart 0173/9409607 no fx, no carrier pigeon - %s is too gigantic of an industry to bend to the whims of reality
[GENERAL] Convert DECIMAL MySql field to PostGre. How?
Hi, I have designed a database using MySql, but now I decided to move to PostGre. I hava decimal fields with DECIMAL(10,6). How to convert this fields to PostGre? Best Regards, André.
Re: [GENERAL] Convert DECIMAL MySql field to PostGre. How?
Hello use type NUMERIC(10,6) Pavel Stehule p.s. current name of this database is PostgreSQL, shortly Postgres :) 2009/10/11 Andre Lopes : > Hi, > > I have designed a database using MySql, but now I decided to move to > PostGre. I hava decimal fields with DECIMAL(10,6). How to convert this > fields to PostGre? > > Best Regards, > André. > > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is statement ID of table?
In response to Jignesh Shah : > Hi, > > Could any one please tell me what is statement ID of table? There isn't such ID, but every table has an OID, an Object Identifier. > How to get it and The (hidden) column "oid" of pg_class contains this OID. > in which scenarios it can be helpful? Any documentation or example about I think, you don't need this, it is only for internal. http://www.postgresql.org/docs/8.4/interactive/datatype-oid.html Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Convert DECIMAL MySql field to PostGre. How?
Pavel Stehule writes: > 2009/10/11 Andre Lopes : >> I have designed a database using MySql, but now I decided to move to >> PostGre. I hava decimal fields with DECIMAL(10,6). How to convert this >> fields to PostGre? > use type NUMERIC(10,6) DECIMAL works just fine too; it's an alias for NUMERIC. regards, tom lane -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is statement ID of table?
Thanks Andreas. Sorry for confusion here. I mean statement ID that can be associated with prepared query(not table) to improve performance of building query. I just need to find plan using statement ID and execute it. I don't know how to do this. Thanks, Jignesh On Sun, Oct 11, 2009 at 8:06 PM, A. Kretschmer < andreas.kretsch...@schollglas.com> wrote: > In response to Jignesh Shah : > > Hi, > > > > Could any one please tell me what is statement ID of table? > > There isn't such ID, but every table has an OID, an Object Identifier. > > > > How to get it and > > The (hidden) column "oid" of pg_class contains this OID. > > > > in which scenarios it can be helpful? Any documentation or example about > > I think, you don't need this, it is only for internal. > > http://www.postgresql.org/docs/8.4/interactive/datatype-oid.html > > > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general >
[GENERAL] Granting rights
Hi, Is it possible to give a user rights(update,select,insert) rights on a database and not necessary doing that for one relation at a time? I have many tables in the database but I don't want this user to be able to delete a relation. Granting the user rights per table is gonna take a lot of time. Any better way to do it without me having to write a script? Cheers, P.
[GENERAL] Is there a way to know if trigger is invoked by the code from another trigger
Could you tell me if there is a way to know if trigger is invoked by the code from another trigger? For instance, table A Trigger deletes table B record. While in table B trigger, I want to know whether this was triggered from table A. Thank you, Naoko
Re: [GENERAL] Granting rights
On Sun, Oct 11, 2009 at 11:05 AM, Otandeka Simon Peter wrote: > > Hi, > > Is it possible to give a user rights(update,select,insert) rights on a > database and not necessary doing that for one relation at a time? Rights don't work that way in pgsql. Each type of object has types of rights that make sense for it, so dbs have rights like connect, create, etc, while tables have rights like insert, delete, etc you can grant. > I have many tables in the database but I don't want this user to be able to > delete a relation. Granting the user rights per table is gonna > take a lot of time. Then write a script, and grant them to a role, and grant that role the users who need those rights. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] strange plpgsql error
Hi, I have a plpgsql function called irq(IN ulist integer[], .) It works fine on 8.1 Linux On 8.4 on windows XP running the function gives an error message (Undefined column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange because the underlying query does return that column. I run the create script of irq to create irq2 - and irq2 works fine. I delete irq and rename irq2 to irq and I get the error back. The error is consistent in the sense that if I delete the database and restore it it appears again the same way. Is this a bug? Any suggestion what I should be doing with this? Thanks for the help. SWK -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange plpgsql error
On Sunday 11 October 2009 2:20:32 pm SunWuKung wrote: > Hi, > I have a plpgsql function called irq(IN ulist integer[], .) > It works fine on 8.1 Linux > > On 8.4 on windows XP running the function gives an error message (Undefined > column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange > because the underlying query does return that column. > I run the create script of irq to create irq2 - and irq2 works fine. > I delete irq and rename irq2 to irq and I get the error back. > > The error is consistent in the sense that if I delete the database and > restore it it appears again the same way. > > Is this a bug? > Any suggestion what I should be doing with this? > > Thanks for the help. > SWK > -- > View this message in context: > http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html Sent > from the PostgreSQL - general mailing list archive at Nabble.com. Are you using the string 'irq' inside the function? -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange plpgsql error
Yes, I use it as a table alias inside the function. Select .. irq.instreq_min_metcount, irq.ref_deptype, irq.instreq_aggrfunc >From .. instrument_requirement irq ON Adrian Klaver wrote: > > On Sunday 11 October 2009 2:20:32 pm SunWuKung wrote: >> Hi, >> I have a plpgsql function called irq(IN ulist integer[], .) >> It works fine on 8.1 Linux >> >> On 8.4 on windows XP running the function gives an error message >> (Undefined >> column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange >> because the underlying query does return that column. >> I run the create script of irq to create irq2 - and irq2 works fine. >> I delete irq and rename irq2 to irq and I get the error back. >> >> The error is consistent in the sense that if I delete the database and >> restore it it appears again the same way. >> >> Is this a bug? >> Any suggestion what I should be doing with this? >> >> Thanks for the help. >> SWK >> -- >> View this message in context: >> http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html Sent >> from the PostgreSQL - general mailing list archive at Nabble.com. > > Are you using the string 'irq' inside the function? > > -- > Adrian Klaver > akla...@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25847881.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange plpgsql error
Right, I replace the table alias irq to instreq and the function works. I don't know what could have been the problem: is irq a reserved word, or it's because the table alias has the same name then the function name. Thanks for pointing me to the right direction. SWK SunWuKung wrote: > > > Yes, I use it as a table alias inside the function. > > Select .. > irq.instreq_min_metcount, > irq.ref_deptype, > irq.instreq_aggrfunc > From .. instrument_requirement irq ON > > > > > Adrian Klaver wrote: >> >> On Sunday 11 October 2009 2:20:32 pm SunWuKung wrote: >>> Hi, >>> I have a plpgsql function called irq(IN ulist integer[], .) >>> It works fine on 8.1 Linux >>> >>> On 8.4 on windows XP running the function gives an error message >>> (Undefined >>> column: 7 ERROR: record "rec" has no field "instreq_id") wich is strange >>> because the underlying query does return that column. >>> I run the create script of irq to create irq2 - and irq2 works fine. >>> I delete irq and rename irq2 to irq and I get the error back. >>> >>> The error is consistent in the sense that if I delete the database and >>> restore it it appears again the same way. >>> >>> Is this a bug? >>> Any suggestion what I should be doing with this? >>> >>> Thanks for the help. >>> SWK >>> -- >>> View this message in context: >>> http://www.nabble.com/strange-plpgsql-error-tp25847709p25847709.html >>> Sent >>> from the PostgreSQL - general mailing list archive at Nabble.com. >> >> Are you using the string 'irq' inside the function? >> >> -- >> Adrian Klaver >> akla...@comcast.net >> >> -- >> Sent via pgsql-general mailing list (pgsql-general@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-general >> >> > > -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25847955.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange plpgsql error
On Sunday 11 October 2009 2:48:02 pm SunWuKung wrote: > Right, > I replace the table alias irq to instreq and the function works. > I don't know what could have been the problem: is irq a reserved word, or > it's because the table alias has the same name then the function name. > > Thanks for pointing me to the right direction. > SWK > It is not a reserved word (http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html). My guess is it was the name conflict between the function name and the table alias. -- Adrian Klaver akla...@comcast.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] strange plpgsql error
Yep, it is probably a bug though. Adrian Klaver wrote: > > On Sunday 11 October 2009 2:48:02 pm SunWuKung wrote: >> Right, >> I replace the table alias irq to instreq and the function works. >> I don't know what could have been the problem: is irq a reserved word, or >> it's because the table alias has the same name then the function name. >> >> Thanks for pointing me to the right direction. >> SWK >> > > It is not a reserved word > (http://www.postgresql.org/docs/8.4/interactive/sql-keywords-appendix.html). > My guess is it was the name conflict between the function name and the > table > alias. > > > -- > Adrian Klaver > akla...@comcast.net > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > > -- View this message in context: http://www.nabble.com/strange-plpgsql-error-tp25847709p25848214.html Sent from the PostgreSQL - general mailing list archive at Nabble.com. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] How to send multiple SQL commands from Python?
On Sat, Oct 10, 2009 at 01:14:56PM -0700, Adrian Klaver wrote: > sql_str = "ALTER TABLE " + $xn + " OWNER TO xdev;" > sql_str += "GRANT ALL ON TABLE " + $xn + " TO xdev;" > sql_str += "REVOKE ALL ON TABLE " + $xn + " FROM PUBLIC;" > sql_str += "GRANT SELECT ON TABLE " + $xn + " TO PUBLIC;" One minor stylistic point. Python appears to follow the same string literal rules as C in that multiple adjacent string literals are concatenated at compile time[1]. Thus you could write the above as: sql_str = ( "ALTER TABLE " + $xn + " OWNER TO xdev;" "GRANT ALL ON TABLE " + $xn + " TO xdev;" "REVOKE ALL ON TABLE " + $xn + " FROM PUBLIC;" "GRANT SELECT ON TABLE " + $xn + " TO PUBLIC;" ); This wouldn't help much here, but may in more complicated bits of code. -- Sam http://samason.me.uk/ [1] http://docs.python.org/reference/lexical_analysis.html#string-literal-concatenation -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Building PG 8.4.1 with ossp-uuid on Centos 5.3
On Oct 11, 2009, at 1:14 AM, Devrim GÜNDÜZ wrote: http://download.fedora.redhat.com/pub/epel/5/i386/repoview/letter_u.group.html http://download.fedora.redhat.com/pub/epel/5/x86_64/repoview/letter_u.group.html Many thanks; it was a 32 vs 64 bit library problem, solved. -- -- Christophe Pettus x...@thebuild.com -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] table full scan or index full scan?
I have a 30,000,000 records table, counts the record number to need for 40 seconds. The table has a primary key on column id; perf=# explain select count(*) from test; ... - Aggregate (cost=603702.80..603702.81 rows=1 width=0) -> Seq scan on test (cost=0.00..527681.04 rows=30408704 width=0) ... perf=# select count(*) from test; count 30408704 perf=# The postgresql database uses the table full scan.but in oracle, the similar SQL uses the index full scanning,speed quickly many than postgresql. postgresql's optimizer whether to have the necessity to make the adjustment? postgresql version:8.3.7 OS : ubuntu 9 kernel:2.6.28-15-generic x86_64 ___ 好玩贺卡等你发，邮箱贺卡全新上线！ http://card.mail.cn.yahoo.com/
[GENERAL] CISSE 2009 - Paper Submission Deadline Extended to October 26, 2009.
Dear Colleagues, Due to numerous deadline extension requests from potential CISSE 2009 authors, the CISSE organizing committee has decided to extend the paper submission deadline to 10/26/2009. Please note that this is a hard deadline, so that the technical committees can perform their paper reviewing duties in a timely manner. If you received this email in error, please forward it to the appropriate department at your institution. If you wish to unsubscribe please follow the unsubscribe link at bottom of the email. Please do not reply to this message. If you need to contact us please email us at i...@cisse2009.org * * The Fifth International Joint Conferences on Computer,* * Information, and Systems Sciences, and Engineering (CISSE 2009) * * * * * * * * http://www.cisse2009.org* * * * * * * * December 4-12, 2009 Sponsored by the University of Bridgeport Technically co-sponsored by the IEEE Computer Society, Communications Society and Education Society (Connecticut Section) - CONFERENCE OVERVIEW - CISSE 2009 provides a virtual forum for presentation and discussion of the state-of the-art research on computers, information and systems sciences and engineering. CISSE 2009 is the fifth conference of the CISSE series of e-conferences. CISSE is the World's first Engineering/Computing and Systems Research E-Conference. CISSE 2005 was the first high-caliber Research Conference in the world to be completely conducted online in real-time via the internet. CISSE 2005 received 255 research paper submissions and the final program included 140 accepted papers, from more than 45 countries. CISSE 2006 received 691 research paper submissions and the final program included 390 accepted papers, from more than 70 countries. CISSE 2007 received 750 research paper submissions and the final program included 406 accepted papers. A total of 948 paper submissions were received for CISSE 2008 and the final program included 382 accepted papers, from more than 80 countries. The virtual conference will be conducted through the Internet using web-conferencing tools, made available by the conference. Authors will be presenting their PowerPoint, audio or video presentations using web-conferencing tools without the need for travel. Conference sessions will be broadcast to all the conference participants, where session participants can interact with the presenter during the presentation and (or) during the Q&A slot that follows the presentation. This international conference will be held entirely on-line. The accepted and presented papers will be made available and sent to the authors after the conference both on a DVD (including all papers, powerpoint presentations and audio presentations) and as a book publication. Springer, the official publisher for CISSE, published the 2005 proceedings in 2 books and the CISSE 2006, CISSE 2007 and CISSE 2008 proceedings in four books each. Conference participants - authors, presenters and attendees - only need an internet connection and sound available on their computers in order to be able to contribute and participate in this international ground-breaking conference. The on-line structure of this high-quality event will allow academic professionals and industry participants to contribute their work and attend world-class technical presentations based on rigorously refereed submissions, live, without the need for investing significant travel funds or time out of the office. The concept and format of CISSE is ground-breaking. The PowerPoint presentations, final paper manuscripts and time schedule for live presentations over the web are available for two weeks prior to the start of the conference for all registrants, so that the participants can choose the presentations they want to attend and think about questions that they might want to ask. The live audio presentations were also recorded and are part of the permanent CISSE on-line archive - accessible to all registrants- which also includes all the papers, PowerPoint and audio presentations. Potential non-author conference attendees who cannot make the on-line conference dates are encouraged to register, as the entire joint conferences will be archived for future viewing. The CISSE conference audio room provides superb
Re: [GENERAL] table full scan or index full scan?
On Mon, 12 Oct 2009, ?? ? wrote: perf=# select count(*) from test; In PostgreSQL, if you're selecting every record from the table for a count of them, you have to visit them all no matter what. The most efficient way to do that is with a full table scan. Using an index instead requires more disk I/O, because you have to read both the index blocks and the disk blocks. The postgresql database uses the table full scan.but in oracle, the similar SQL uses the index full scanning,speed quickly many than postgresql. Some other database systems can do just an index scan instead to compute aggregates like count, but even there the rules are pretty complicated; http://www.jlcomp.demon.co.uk/faq/count_rows.html covers a lot of the material there for Oracle's implementation. Unfortunately this particular optimization isn't available in Postgres yet, and you'll only switch to an index scan if you're running a query that only selects a small number of records where an index on the condition you're checking for exists. There's some information about alternative ways to solve this problem at http://wiki.postgresql.org/wiki/Slow_Counting -- * Greg Smith gsm...@gregsmith.com http://www.gregsmith.com Baltimore, MD -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table full scan or index full scan?
Real quick, plain text is preferred on these lists over html. I don't care myself so much. On Sun, Oct 11, 2009 at 7:17 PM, 旭斌 裴 wrote: > > I have a 30,000,000 records table, counts the record number to need for 40 > seconds. > The table has a primary key on column id; > > perf=# explain select count(*) from test; > ... > - > Aggregate (cost=603702.80..603702.81 rows=1 width=0) > -> Seq scan on test (cost=0.00..527681.04 rows=30408704 width=0) > ... > perf=# select count(*) from test; > count > > 30408704 > > perf=# > > > The postgresql database uses the table full scan.but in oracle, the similar > SQL uses the index full scanning,speed quickly many than postgresql. Yep, PostgreSQL isn't Oracle. It's a trade off. In pgsql indexes don't contain visibility info, so all index lookups have to eventually hit the table itself. So you either do indexlookup -> table lookup, repeat as many times as you have index lookups or you just hit the table since you gotta go there anyway. On the bright side, this makes updates faster since you don't have to lock both table and index and write to both at the same time anymore. > postgresql's optimizer whether to have the necessity to make the adjustment? Sorry, it's an architectural difference. Are you testing in a realistic scenario including both reads and writes to the database to see if postgresql is faster overall and identify problem areas that pop up there? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] Nested transactions
Does PostgreSQL support nested transactions as shown below? BEGIN; ...do some stuff... BEGIN; ...more stuff... COMMIT; COMMIT; Bill -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Nested transactions
On Sun, Oct 11, 2009 at 8:41 PM, Bill Todd wrote: > Does PostgreSQL support nested transactions as shown below? > > BEGIN; > ...do some stuff... > BEGIN; > ...more stuff... > COMMIT; > COMMIT; Postgresql uses savepoints. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Nested transactions
Bill Todd wrote: Does PostgreSQL support nested transactions as shown below? BEGIN; ...do some stuff... BEGIN; ...more stuff... COMMIT; COMMIT; no, but in recent versiosn, you can use SAVEPOINT to achieve much the same effect. http://www.postgresql.org/docs/current/static/sql-savepoint.html -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table full scan or index full scan?
2009/10/11 Scott Marlowe : >> The postgresql database uses the table full scan.but in oracle, the similar >> SQL uses the index full scanning,speed quickly many than postgresql. > > Yep, PostgreSQL isn't Oracle. It's a trade off. In pgsql indexes > don't contain visibility info, so all index lookups have to eventually > hit the table itself. So you either do indexlookup -> table lookup, > repeat as many times as you have index lookups or you just hit the > table since you gotta go there anyway. > > On the bright side, this makes updates faster since you don't have to > lock both table and index and write to both at the same time anymore. > >> postgresql's optimizer whether to have the necessity to make the adjustment? > > Sorry, it's an architectural difference. Are you testing in a > realistic scenario including both reads and writes to the database to > see if postgresql is faster overall and identify problem areas that > pop up there? > This is interesting, I just ran a similar issue the other day. Clearly there is a wide range of read / write scenarios that Postgres should be able to cover. These days, I have a lot of designs leaning more toward the data warehouse side of the operational spectrum as opposed to the high transaction scenario and I specifically design DB management strategies with the knowledge that writes will happen far less than reads in our applications. Is this an area where optimizations are considered hard in Postrgres or hopefully, just something that is on the todo list but just no one has gotten around to yet? Similarly, are accurate table summary stats possible someday or are they considered close to impossible in order to eliminate race conditions and lock contention scenarios? -- Peter Hunsberger -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] What is statement ID of table?
In response to Jignesh Shah : > Thanks Andreas. Sorry for confusion here. I mean statement ID that can be > associated with prepared query(not table) to improve performance of building > query. I just need to find plan using statement ID and execute it. I don't > know > how to do this. Please, answer to the list and not to me, and please try to avoid html and top-posting. I don't know if i understand you correctly. There isn't such an ID, but you can look at pg_prepared_statements, this view contains name, statement and other informations about all (currently) prepared statements. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] table full scan or index full scan?
On Sun, Oct 11, 2009 at 10:01:52PM -0500, Peter Hunsberger wrote: > This is interesting, I just ran a similar issue the other day. > Clearly there is a wide range of read / write scenarios that Postgres > should be able to cover. These days, I have a lot of designs leaning > more toward the data warehouse side of the operational spectrum as > opposed to the high transaction scenario and I specifically design DB > management strategies with the knowledge that writes will happen far > less than reads in our applications. Is this an area where > optimizations are considered hard in Postrgres or hopefully, just > something that is on the todo list but just no one has gotten around > to yet? We consider any optimisation that is feasible. Unfortunatly, "the number of rows in a table" is a fairly hard number to get in the general case because it depends on who is asking (different transactions may get different answers). >Similarly, are accurate table summary stats possible someday > or are they considered close to impossible in order to eliminate race > conditions and lock contention scenarios? It is possible, it's just not cheap in the general case. The usual approach is to keep a table that tracks the number of rows. By using deltas you can make it lockfree. These are however costs most applications do't need. If you know in your case that the data never changes, just cache the result somewhere. That will be infinitly more efficient than any other method. If you are happy with estimates, they are there and are kept reasonably uptodate. Have a nice day, -- Martijn van Oosterhout http://svana.org/kleptog/ > Please line up in a tree and maintain the heap invariant while > boarding. Thank you for flying nlogn airlines. signature.asc Description: Digital signature
Re: [GENERAL] What is statement ID of table?
you can create a primary key to associate with each row in the table On 10/12/09, A. Kretschmer wrote: > In response to Jignesh Shah : >> Thanks Andreas. Sorry for confusion here. I mean statement ID that can be >> associated with prepared query(not table) to improve performance of >> building >> query. I just need to find plan using statement ID and execute it. I don't >> know >> how to do this. > > Please, answer to the list and not to me, and please try to avoid html and > top-posting. > > I don't know if i understand you correctly. There isn't such an ID, but > you can look at pg_prepared_statements, this view contains name, > statement and other informations about all (currently) prepared > statements. > > > Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) > > -- > Sent via pgsql-general mailing list (pgsql-general@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-general > -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
